include/generic/SugarWidgets/SugarWidgetFielddatetime.php

   1 <?php
   2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   3 /*********************************************************************************
   4  * SugarCRM is a customer relationship management program developed by
   5  * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
   6  *
   7  * This program is free software; you can redistribute it and/or modify it under
   8  * the terms of the GNU Affero General Public License version 3 as published by the
   9  * Free Software Foundation with the addition of the following permission added
  10  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  11  * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
  12  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  13  *
  14  * This program is distributed in the hope that it will be useful, but WITHOUT
  15  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  16  * FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
  17  * details.
  18  *
  19  * You should have received a copy of the GNU Affero General Public License along with
  20  * this program; if not, see http://www.gnu.org/licenses or write to the Free
  21  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  22  * 02110-1301 USA.
  23  *
  24  * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
  25  * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
  26  *
  27  * The interactive user interfaces in modified source and object code versions
  28  * of this program must display Appropriate Legal Notices, as required under
  29  * Section 5 of the GNU Affero General Public License version 3.
  30  *
  31  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
  32  * these Appropriate Legal Notices must retain the display of the "Powered by
  33  * SugarCRM" logo. If the display of the logo is not reasonably feasible for
  34  * technical reasons, the Appropriate Legal Notices must display the words
  35  * "Powered by SugarCRM".
  36  ********************************************************************************/
  37
  38
  39
  40
  41 class SugarWidgetFieldDateTime extends SugarWidgetReportField {
  42         var $reporter;
  43         var $assigned_user=null;
  44
  45     function SugarWidgetFieldDateTime(&$layout_manager) {
  46         parent::SugarWidgetReportField($layout_manager);
  47         $this->reporter = $this->layout_manager->getAttribute('reporter');
  48     }
  49
  50         // get the reporter attribute
  51     // deprecated, now called in the constructor
  52         function getReporter() {
  53 //              $this->reporter = $this->layout_manager->getAttribute('reporter');
  54         }
  55
  56         // get the assigned user of the report
  57         function getAssignedUser() {
  58                 $json_obj = getJSONobj();
  59
  60                 $report_def_str = $json_obj->decode($this->reporter->report_def_str);
  61
  62                 if(empty($report_def_str['assigned_user_id'])) return false;
  63
  64                 $this->assigned_user = new User();
  65                 $this->assigned_user->retrieve($report_def_str['assigned_user_id']);
  66                 return true;
  67         }
  68
  69         function queryFilterOn(& $layout_def) {
  70                 global $timedate;
  71                 if($this->getAssignedUser()) {
  72                         $begin = $timedate->handle_offset($layout_def['input_name0'] . ' 00:00:00', $timedate->get_db_date_time_format(), false, $this->assigned_user);
  73                         $end = $timedate->handle_offset($layout_def['input_name0'] . ' 23:59:59', $timedate->get_db_date_time_format(), false, $this->assigned_user);
  74                 }
  75                 else {
  76                         $begin = $layout_def['input_name0']." 00:00:00";
  77                 $end = $layout_def['input_name0']." 23:59:59";
  78                 }
  79
  80                         return $this->_get_column_select($layout_def).">='".$this->reporter->db->quote($begin)."' AND ".$this->_get_column_select($layout_def)."<='".$this->reporter->db->quote($end)."'\n";
  81         }
  82
  83         function queryFilterBefore(& $layout_def) {
  84                 global $timedate;
  85
  86                 if($this->getAssignedUser()) {
  87                         $begin = $timedate->handle_offset($layout_def['input_name0'] . ' 00:00:00', $timedate->get_db_date_time_format(), false, $this->assigned_user);
  88                 }
  89                 else {
  90                         $begin = $layout_def['input_name0']." 00:00:00";
  91                 }
  92
  93                         return $this->_get_column_select($layout_def)."<'".$this->reporter->db->quote($begin)."'\n";
  94
  95         }
  96
  97         function queryFilterAfter(& $layout_def) {
  98                 global $timedate;
  99
 100                 if($this->getAssignedUser()) {
 101                         $begin = $timedate->handle_offset($layout_def['input_name0'] . ' 23:59:59', $timedate->get_db_date_time_format(), false, $this->assigned_user);
 102                 }
 103                 else {
 104                         $begin = $layout_def['input_name0']." 23:59:59";
 105                 }
 106
 107                         return $this->_get_column_select($layout_def).">'".$this->reporter->db->quote($begin)."'\n";
 108         }
 109
 110         function queryFilterBetween_Dates(& $layout_def) {
 111                 global $timedate;
 112
 113                 if($this->getAssignedUser()) {
 114                         $begin = $timedate->handle_offset($layout_def['input_name0'] . ' 00:00:00', $timedate->get_db_date_time_format(), false, $this->assigned_user);
 115                         $end = $timedate->handle_offset($layout_def['input_name1'] . ' 23:59:59', $timedate->get_db_date_time_format(), false, $this->assigned_user);
 116                 }
 117                 else {
 118                         $begin = $layout_def['input_name0']." 00:00:00";
 119                         $end = $layout_def['input_name1']." 23:59:59";
 120                 }
 121
 122                         return "(".$this->_get_column_select($layout_def).">='".$this->reporter->db->quote($begin)."' AND \n".$this->_get_column_select($layout_def)."<='".$this->reporter->db->quote($end)."')\n";
 123         }
 124
 125         function queryFilterNot_Equals_str(& $layout_def) {
 126                 global $timedate;
 127
 128                 if($this->getAssignedUser()) {
 129                         $begin = $timedate->handle_offset($layout_def['input_name0'] . ' 00:00:00', $timedate->get_db_date_time_format(), false, $this->assigned_user);
 130                         $end = $timedate->handle_offset($layout_def['input_name0'] . ' 23:59:59', $timedate->get_db_date_time_format(), false, $this->assigned_user);
 131                 }
 132                 else {
 133                         $begin = $layout_def['input_name0']." 00:00:00";
 134                         $end = $layout_def['input_name0']." 23:59:59";
 135                 }
 136
 137                 if ($this->reporter->db->dbType == 'oci8') {
 138
 139                 } elseif ($this->reporter->db->dbType == 'mssql'){
 140             return "(".$this->_get_column_select($layout_def)."<'".$this->reporter->db->quote($begin)."' OR ".$this->_get_column_select($layout_def).">'".$this->reporter->db->quote($end)."')\n";
 141
 142                 }else{
 143             return "ISNULL(".$this->_get_column_select($layout_def).") OR \n(".$this->_get_column_select($layout_def)."<'".$this->reporter->db->quote($begin)."' OR ".$this->_get_column_select($layout_def).">'".$this->reporter->db->quote($end)."')\n";
 144         }
 145         }
 146
 147         function queryFilterTP_yesterday(& $layout_def) {
 148                 global $timedate, $current_user;
 149
 150         $begin_timestamp = time() - 86400;
 151
 152         // begin conversion (same code as queryFilterTP_today)
 153         $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp);
 154         //kbrill bug #13884
 155         //$begin = $timedate->to_display_date_time($begin);
 156                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
 157
 158         $begin_parts = explode(' ', $begin);
 159
 160         $be = $begin_parts[0] . ' 00:00:00';
 161         $en = $begin_parts[0] . ' 23:59:59';
 162
 163         if($this->getAssignedUser()) {
 164             $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 165             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 166         }
 167         else{
 168                 $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $current_user); // convert to GMT today relative to assigned_user
 169             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $current_user);
 170         }
 171
 172                 if ($this->reporter->db->dbType == 'oci8') {
 173
 174                 }
 175
 176                 if ($this->reporter->db->dbType == 'mysql')
 177                 {
 178                         if (isset ($layout_def['rel_field'])) {
 179                                 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
 180                         } else {
 181                                 $field_name = $this->_get_column_select($layout_def);
 182                         }
 183                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 184                 }
 185
 186                 if ($this->reporter->db->dbType == 'mssql')
 187                 {
 188                         if (isset ($layout_def['rel_field'])) {
 189                                 $field_name = $this->_get_column_select($layout_def) . " + ' ' + " . $layout_def['rel_field'].")";
 190                         } else {
 191                                 $field_name = $this->_get_column_select($layout_def);
 192                         }
 193                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 194                 }
 195         }
 196         function queryFilterTP_today(& $layout_def) {
 197                 global $timedate, $current_user;
 198
 199         $begin_timestamp = time();
 200                 $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp); // get GMT today
 201                 //kbrill bug #13884
 202         //$begin = $timedate->to_display_date_time($begin); // convert and handle offset to user's 'display' today
 203                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
 204
 205         $begin_parts = explode(' ', $begin);
 206
 207         $be = $begin_parts[0] . ' 00:00:00';
 208         $en = $begin_parts[0] . ' 23:59:59';
 209
 210                 if($this->getAssignedUser()) {
 211             $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user); // convert to GMT today relative to assigned_user
 212             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 213                 }
 214                 else{
 215                         $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $current_user); // convert to GMT today relative to assigned_user
 216             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $current_user);
 217                 }
 218
 219                 if ($this->reporter->db->dbType == 'oci8') {
 220
 221                 }elseif($this->reporter->db->dbType == 'mssql'){
 222             if (isset ($layout_def['rel_field'])) {
 223                 $field_name = "(".$this->_get_column_select($layout_def)." + ' ' + ".$layout_def['rel_field'].")";
 224             } else {
 225                 $field_name = $this->_get_column_select($layout_def);
 226             }
 227             return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 228
 229         } else {
 230                         if (isset ($layout_def['rel_field'])) {
 231                                 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
 232                         } else {
 233                                 $field_name = $this->_get_column_select($layout_def);
 234                         }
 235                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 236                 }
 237         }
 238
 239         function queryFilterTP_tomorrow(& $layout_def) {
 240                 global $timedate, $current_user;
 241
 242         // get tomorrow
 243         $begin_timestamp =time()+ 86400;
 244
 245         // begin conversion (same code as queryFilterTP_today)
 246         $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp);
 247         //kbrill bug #13884
 248         //$begin = $timedate->to_display_date_time($begin);
 249                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
 250
 251         $begin_parts = explode(' ', $begin);
 252
 253         $be = $begin_parts[0] . ' 00:00:00';
 254         $en = $begin_parts[0] . ' 23:59:59';
 255
 256         if($this->getAssignedUser()) {
 257             $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 258             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 259         }
 260         else{
 261                 $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $current_user);
 262             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $current_user);
 263         }
 264
 265                 if ($this->reporter->db->dbType == 'oci8') {
 266                 } else {
 267                         return $this->_get_column_select($layout_def).">='".$this->reporter->db->quote($begin)."' AND ".$this->_get_column_select($layout_def)."<='".$this->reporter->db->quote($end)."'\n";
 268                 }
 269         }
 270
 271     /**
 272      * Get assigned or logged in user's current date and time value.
 273      * @param boolean $timestamp Format of return value, if set to true, return unix like timestamp , else a formatted date.
 274      */
 275         function get_users_current_date_time($timestamp=false) {
 276                 global $current_user;
 277         global $timedate;
 278
 279         $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format());
 280         //kbrill bug #13884
 281         //$begin = $timedate->to_display_date_time($begin,true,true,$this->assigned_user);
 282                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 283
 284         if (!$timestamp) {
 285                 return $begin;
 286         } else {
 287                 $begin_parts = explode(' ', $begin);
 288                 $date_parts=explode('-', $begin_parts[0]);
 289                 $time_parts=explode(':', $begin_parts[1]);
 290                 $curr_timestamp=mktime($time_parts[0],$time_parts[1],0,$date_parts[1], $date_parts[2],$date_parts[0]);
 291                 return $curr_timestamp;
 292         }
 293
 294         }
 295         /**
 296          * Get specified date and time for a particalur day, in current user's timezone.
 297          * @param int $days Adjust date by this number of days, negative values are valid.
 298          * @param time string falg for desired time value, start: minimum time, end: maximum time, default: current time
 299          */
 300         function get_db_date($days,$time) {
 301         global $timedate;
 302
 303         $begin = date($GLOBALS['timedate']->get_db_date_time_format(), time()+(86400 * $days));  //gmt date with day adjustment applied.
 304         //kbrill bug #13884
 305         //$begin = $timedate->to_display_date_time($begin,true,true,$this->assigned_user);
 306                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 307
 308         if ($time=='start') {
 309             $begin_parts = explode(' ', $begin);
 310             $be = $begin_parts[0] . ' 00:00:00';
 311         }
 312         else if ($time=='end') {
 313             $begin_parts = explode(' ', $begin);
 314             $be = $begin_parts[0] . ' 23:59:59';
 315         } else {
 316             $be=$begin;
 317         }
 318
 319         //convert date to db format without converting to GMT.
 320         $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 321
 322         return $begin;
 323         }
 324
 325         /**
 326          * Get filter string for a date field.
 327          * @param array layout_def field def for field being filtered
 328          * @param string $begin start date value.
 329          * @param string $end End date value.
 330          */
 331         function get_start_end_date_filter(& $layout_def, $begin,$end) {
 332
 333                 if ($this->reporter->db->dbType == 'oci8') {
 334
 335                 }elseif($this->reporter->db->dbType == 'mssql'){
 336             if (isset ($layout_def['rel_field'])) {
 337                 $field_name = "(".$this->_get_column_select($layout_def)." + ' ' + ".$layout_def['rel_field'].")";
 338             } else {
 339                 $field_name = $this->_get_column_select($layout_def);
 340             }
 341             return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 342
 343         } else {
 344                         if (isset ($layout_def['rel_field'])) {
 345                                 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
 346                         } else {
 347                                 $field_name = $this->_get_column_select($layout_def);
 348                         }
 349                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 350                 }
 351         }
 352
 353         function queryFilterTP_last_7_days(& $layout_def) {
 354
 355         $begin=$this->get_db_date(-6,'start');
 356         $end=$this->get_db_date(0,'end');
 357
 358
 359         return $this->get_start_end_date_filter($layout_def,$begin,$end);
 360
 361         }
 362
 363         function queryFilterTP_next_7_days(& $layout_def) {
 364
 365         $begin=$this->get_db_date(0,'start');
 366         $end=$this->get_db_date(6,'end');
 367
 368                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 369         }
 370
 371         function queryFilterTP_last_month(& $layout_def) {
 372
 373                 global $timedate;
 374                 $curr_timestamp= $this->get_users_current_date_time(true);
 375
 376                 //Get year and month from time stamp.
 377                 $curr_year=date('Y',$curr_timestamp);
 378                 $curr_month=date('m',$curr_timestamp);
 379
 380                 //get start date for last month and convert it to gmt and db format.
 381             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 month",mktime(0,0,0,$curr_month,1,$curr_year)));
 382         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 383
 384             //get end date for last month  and convert it to gmt and db format.
 385         $end=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 day",mktime(23,59,59,$curr_month,1,$curr_year)));
 386             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 387
 388                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 389         }
 390
 391         function queryFilterTP_this_month(& $layout_def) {
 392
 393                 global $timedate;
 394                 $curr_timestamp= $this->get_users_current_date_time(true);
 395
 396                 //Get year and month from time stamp.
 397                 $curr_year=date('Y',$curr_timestamp);
 398                 $curr_month=date('m',$curr_timestamp);
 399
 400                 //get start date for this month and convert it to gmt and db format.
 401             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,$curr_month,1,$curr_year));
 402         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 403
 404             //get end date for this month  and convert it to gmt and db format.
 405             //first get the first day of next month and move back by one day.
 406                 if ($curr_month==12) {
 407                         $curr_month=1;
 408                         $curr_year+=1;
 409                 } else {
 410                         $curr_month+=1;
 411                 }
 412         $end=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 day",mktime(23,59,59,$curr_month,1,$curr_year)));
 413             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 414
 415
 416                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 417         }
 418
 419         function queryFilterTP_next_month(& $layout_def) {
 420
 421                 global $timedate;
 422                 $curr_timestamp= $this->get_users_current_date_time(true);
 423
 424                 //Get year and month from time stamp.
 425                 $curr_year=date('Y',$curr_timestamp);
 426                 $curr_month=date('m',$curr_timestamp);
 427                 if ($curr_month==12) {
 428                         $curr_month=1;
 429                         $curr_year+=1;
 430                 } else {
 431                         $curr_month+=1;
 432                 }
 433
 434                 //get start date for next month and convert it to gmt and db format.
 435             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,$curr_month,1,$curr_year));
 436         $begin=$timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 437
 438             //get end date for next month  and convert it to gmt and db format.
 439             //first get first day of the month after and move back by 1 day.
 440                 if ($curr_month==12) {
 441                         $curr_month=1;
 442                         $curr_year+=1;
 443                 } else {
 444                         $curr_month+=1;
 445                 }
 446         $end=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 day",(strtotime("1 month",mktime(23,59,59,$curr_month,1,$curr_year)))));
 447             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 448
 449
 450                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 451         }
 452
 453         function queryFilterTP_last_30_days(& $layout_def) {
 454
 455         $begin=$this->get_db_date(-29,'start');
 456         $end=$this->get_db_date(0,'end');
 457
 458         return $this->get_start_end_date_filter($layout_def,$begin,$end);
 459         }
 460
 461         function queryFilterTP_next_30_days(& $layout_def) {
 462         $begin=$this->get_db_date(0,'start');
 463         $end=$this->get_db_date(29,'end');
 464
 465         return $this->get_start_end_date_filter($layout_def,$begin,$end);
 466         }
 467
 468         function queryFilterTP_last_quarter(& $layout_def) {
 469 //              return "LEFT(".$this->_get_column_select($layout_def).",10) BETWEEN (current_date + interval '1' month) AND current_date";
 470         }
 471
 472         function queryFilterTP_this_quarter(& $layout_def) {
 473         }
 474
 475         function queryFilterTP_last_year(& $layout_def) {
 476
 477                 global $timedate;
 478                 $curr_timestamp= $this->get_users_current_date_time(true);
 479
 480                 //Get year and month from time stamp.
 481                 $curr_year=date('Y',$curr_timestamp);
 482                 $curr_year-=1;
 483
 484                 //get start date for last year and convert it to gmt and db format.
 485             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,1,1,$curr_year));
 486         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 487
 488             //get end date for last year  and convert it to gmt and db format.
 489         $end=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(23,59,59,12,31,$curr_year));
 490             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 491
 492                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 493         }
 494
 495         function queryFilterTP_this_year(& $layout_def) {
 496                 global $timedate;
 497                 $curr_timestamp= $this->get_users_current_date_time(true);
 498
 499                 //Get year and month from time stamp.
 500                 $curr_year=date('Y',$curr_timestamp);
 501
 502                 //get start date for this year and convert it to gmt and db format.
 503             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,1,1,$curr_year));
 504         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 505
 506             //get end date for this year  and convert it to gmt and db format.
 507         $end=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(23,59,59,12,31,$curr_year));
 508             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 509
 510                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 511         }
 512
 513         function queryFilterTP_next_year(& $layout_def) {
 514                 global $timedate;
 515                 $curr_timestamp= $this->get_users_current_date_time(true);
 516
 517                 //Get year and month from time stamp.
 518                 $curr_year=date('Y',$curr_timestamp);
 519                 $curr_year+=1;
 520
 521                 //get start date for this year and convert it to gmt and db format.
 522             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,1,1,$curr_year));
 523         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 524
 525             //get end date for this year  and convert it to gmt and db format.
 526         $end=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(23,59,59,12,31,$curr_year));
 527             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 528
 529                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 530         }
 531
 532         function queryGroupBy($layout_def) {
 533                 // i guess qualifier and column_function are the same..
 534                 if (!empty ($layout_def['qualifier'])) {
 535                         $func_name = 'queryGroupBy'.$layout_def['qualifier'];
 536                         //print_r($layout_def);
 537                         //print $func_name;
 538                         if (method_exists($this, $func_name)) {
 539                                 return $this-> $func_name ($layout_def)." \n";
 540                         }
 541                 }
 542                 return parent :: queryGroupBy($layout_def)." \n";
 543         }
 544
 545         function queryOrderBy($layout_def) {
 546                 // i guess qualifier and column_function are the same..
 547         if ($this->reporter->db->dbType == 'mssql'){
 548             //do nothing if this is for mssql, do not run group by
 549
 550         }
 551                 elseif (!empty ($layout_def['qualifier'])) {
 552                         $func_name ='queryGroupBy'.$layout_def['qualifier'];
 553                         if (method_exists($this, $func_name)) {
 554                                 return $this-> $func_name ($layout_def)."\n";
 555                         }
 556                 }
 557                 $order_by = parent :: queryOrderBy($layout_def)."\n";
 558                 return $order_by;
 559         }
 560
 561     function displayListPlain($layout_def) {
 562         global $timedate;
 563         $content = parent:: displayListPlain($layout_def);
 564         // awu: this if condition happens only in Reports where group by month comes back as YYYY-mm format
 565         if (count(explode('-',$content)) == 2){
 566             return $content;
 567         // if date field
 568         }elseif(substr_count($layout_def['type'], 'date') > 0){
 569             // if date time field
 570             if(substr_count($layout_def['type'], 'time') > 0 && $this->get_time_part($content)!= false){
 571                 $td = $timedate->to_display_date_time($content);
 572                 return $td;
 573             }else{// if date only field
 574                 $td = $timedate->to_display_date($content, false); // avoid php notice of returing by reference
 575                 return $td;
 576             }
 577         }
 578     }
 579     function get_time_part($date_time_value) {
 580         $date_parts=explode(' ', $date_time_value);
 581         if (count($date_parts) == 2) {
 582             $time=$date_parts[1];
 583         } else {
 584             $time=false;
 585         }
 586         return $time;
 587
 588     }
 589     function displayList($layout_def) {
 590         global $timedate;
 591         // i guess qualifier and column_function are the same..
 592         if (!empty ($layout_def['column_function'])) {
 593             $func_name = 'displayList'.$layout_def['column_function'];
 594             if (method_exists($this, $func_name)) {
 595                 return $this-> $func_name ($layout_def);
 596             }
 597         }
 598         $content = parent :: displayListPlain($layout_def);
 599         return $timedate->to_display_date_time($content);
 600     }
 601
 602         function querySelect(& $layout_def) {
 603                 // i guess qualifier and column_function are the same..
 604                 if (!empty ($layout_def['column_function'])) {
 605                         $func_name = 'querySelect'.$layout_def['column_function'];
 606                         if (method_exists($this, $func_name)) {
 607                                 return $this-> $func_name ($layout_def)." \n";
 608                         }
 609                 }
 610                 return parent :: querySelect($layout_def)." \n";
 611         }
 612         function & displayListday(& $layout_def) {
 613                 return parent:: displayListPlain($layout_def);
 614         }
 615
 616         function & displayListyear(& $layout_def) {
 617                 global $app_list_strings;
 618         //if ($this->reporter->db->dbType == 'oci8' || $this->reporter->db->dbType == 'mssql') {
 619                         return parent:: displayListPlain($layout_def);
 620         //}
 621         /*else{
 622                         $match = array();
 623                 if (preg_match('/(\d{4})/', $this->displayListPlain($layout_def), $match)) {
 624                                 return $match[1];
 625                         }
 626                 $temp = null; // avoid notices
 627                 return $temp;
 628                 }*/
 629         }
 630
 631         function & displayListmonth(& $layout_def) {
 632                 global $app_list_strings;
 633                 $display = '';
 634                 $match = array();
 635         if (preg_match('/(\d{4})-(\d\d)/', $this->displayListPlain($layout_def), $match)) {
 636                         $match[2] = preg_replace('/^0/', '', $match[2]);
 637                         $display = $app_list_strings['dom_cal_month_long'][$match[2]]." {$match[1]}";
 638                 }
 639                 return $display;
 640
 641         }
 642         function querySelectmonth(& $layout_def) {
 643         if ($this->reporter->db->dbType == 'oci8') {
 644         }elseif($this->reporter->db->dbType == 'mssql') {
 645             //return "LEFT( ".$this->_get_column_select($layout_def).",6 ) ".$this->_get_column_alias($layout_def)." \n";
 646                 return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),7)".$this->_get_column_alias($layout_def)." \n";
 647
 648         } else {
 649             return "LEFT( ".$this->_get_column_select($layout_def).",7 ) ".$this->_get_column_alias($layout_def)." \n";
 650         }
 651         }
 652
 653         function queryGroupByMonth($layout_def) {
 654                 if ($this->reporter->db->dbType == 'oci8') {
 655                 }elseif($this->reporter->db->dbType == 'mssql') {
 656             //return "LEFT(".$this->_get_column_select($layout_def).", 6) \n";
 657             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ", 121),7) \n";
 658
 659         }else {
 660                         return "LEFT(".$this->_get_column_select($layout_def).", 7) \n";
 661                 }
 662         }
 663
 664         function querySelectday($layout_def) {
 665                 if ($this->reporter->db->dbType == 'oci8') {
 666                 }elseif($this->reporter->db->dbType == 'mssql') {
 667             //return "LEFT(".$this->_get_column_select($layout_def).", 6) \n";
 668                 return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),10)".$this->_get_column_alias($layout_def)." \n";
 669
 670         }else {
 671                         return "LEFT(".$this->_get_column_select($layout_def).", 10)".$this->_get_column_alias($layout_def)." \n";
 672                 }
 673         }
 674
 675         function queryGroupByDay($layout_def) {
 676                 if ($this->reporter->db->dbType == 'oci8') {
 677                 }elseif($this->reporter->db->dbType == 'mssql') {
 678             //return "LEFT(".$this->_get_column_select($layout_def).", 6) \n";
 679             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ", 121),10) \n";
 680
 681         }else {
 682                         return "LEFT(".$this->_get_column_select($layout_def).", 10) \n";
 683                 }
 684         }
 685
 686
 687         function querySelectyear(& $layout_def) {
 688                 if ($this->reporter->db->dbType == 'oci8') {
 689                 }elseif($this->reporter->db->dbType == 'mssql') {
 690             //return "LEFT( ".$this->_get_column_select($layout_def).",5 ) ".$this->_get_column_alias($layout_def)." \n";
 691             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),4) ".$this->_get_column_alias($layout_def)." \n";
 692
 693         } else {
 694                         return "LEFT( ".$this->_get_column_select($layout_def).",4 ) ".$this->_get_column_alias($layout_def)." \n";
 695                 }
 696         }
 697
 698         function queryGroupByYear($layout_def) {
 699                 if ($this->reporter->db->dbType == 'oci8') {
 700                 }elseif($this->reporter->db->dbType == 'mssql') {
 701             //return "LEFT(".$this->_get_column_select($layout_def).", 5) \n";
 702             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),4) \n";
 703
 704         } else {
 705                         return "LEFT(".$this->_get_column_select($layout_def).", 4) \n";
 706                 }
 707         }
 708
 709         function querySelectquarter(& $layout_def) {
 710                 if ($this->reporter->db->dbType == 'oci8') {
 711                 }
 712
 713                 elseif ($this->reporter->db->dbType == 'mysql')
 714                 {
 715                         return "CONCAT(LEFT(".$this->_get_column_select($layout_def).", 4), '-', QUARTER(".$this->_get_column_select($layout_def).") )".$this->_get_column_alias($layout_def)."\n";
 716                 }
 717
 718                 elseif ($this->reporter->db->dbType == 'mssql')
 719                 {
 720                         //return "LEFT(".$this->_get_column_select($layout_def).", 4) +  '-' + convert(varchar(20), DatePart(q," . $this->_get_column_select($layout_def).") ) ".$this->_get_column_alias($layout_def)."\n";
 721             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),4)+  '-' + convert(varchar(20), DatePart(q," . $this->_get_column_select($layout_def).") ) ".$this->_get_column_alias($layout_def)."\n";
 722
 723                 }
 724
 725
 726         }
 727
 728         function displayListquarter(& $layout_def) {
 729                 $match = array();
 730         if (preg_match('/(\d{4})-(\d)/', $this->displayListPlain($layout_def), $match)) {
 731                         return "Q".$match[2]." ".$match[1];
 732                 }
 733                 return '';
 734
 735         }
 736
 737         function queryGroupByQuarter($layout_def) {
 738                 $this->getReporter();
 739
 740                 if ($this->reporter->db->dbType == 'oci8') {
 741
 742                 }elseif ($this->reporter->db->dbType == 'mysql')
 743                 {
 744                         return "CONCAT(LEFT(".$this->_get_column_select($layout_def).", 4), '-', QUARTER(".$this->_get_column_select($layout_def).") )\n";
 745                 }
 746                 elseif ($this->reporter->db->dbType == 'mssql')
 747                 {
 748                         //return "LEFT(".$this->_get_column_select($layout_def).", 4) +  '-' + convert(varchar(20), DatePart(q," . $this->_get_column_select($layout_def).") )\n";
 749             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),4) +  '-' + convert(varchar(20), DatePart(q," . $this->_get_column_select($layout_def).") )\n";
 750
 751                 }
 752
 753
 754         }
 755
 756     function displayInput(&$layout_def) {
 757         global $timedate, $current_language, $app_strings;
 758         $home_mod_strings = return_module_language($current_language, 'Home');
 759         $filterTypes = array(' '                 => $app_strings['LBL_NONE'],
 760                              'TP_today'         => $home_mod_strings['LBL_TODAY'],
 761                              'TP_yesterday'     => $home_mod_strings['LBL_YESTERDAY'],
 762                              'TP_tomorrow'      => $home_mod_strings['LBL_TOMORROW'],
 763                              'TP_this_month'    => $home_mod_strings['LBL_THIS_MONTH'],
 764                              'TP_this_year'     => $home_mod_strings['LBL_THIS_YEAR'],
 765                              'TP_last_30_days'  => $home_mod_strings['LBL_LAST_30_DAYS'],
 766                              'TP_last_7_days'   => $home_mod_strings['LBL_LAST_7_DAYS'],
 767                              'TP_last_month'    => $home_mod_strings['LBL_LAST_MONTH'],
 768                              'TP_last_year'     => $home_mod_strings['LBL_LAST_YEAR'],
 769                              'TP_next_30_days'  => $home_mod_strings['LBL_NEXT_30_DAYS'],
 770                              'TP_next_7_days'   => $home_mod_strings['LBL_NEXT_7_DAYS'],
 771                              'TP_next_month'    => $home_mod_strings['LBL_NEXT_MONTH'],
 772                              'TP_next_year'     => $home_mod_strings['LBL_NEXT_YEAR'],
 773                              );
 774
 775         $cal_dateformat = $timedate->get_cal_date_format();
 776         $str = "<select name='type_{$layout_def['name']}'>";
 777         $str .= get_select_options_with_id($filterTypes, (empty($layout_def['input_name0']) ? '' : $layout_def['input_name0']));
 778 //        foreach($filterTypes as $value => $label) {
 779 //            $str .= '<option value="' . $value . '">' . $label. '</option>';
 780 //        }
 781         $str .= "</select>";
 782
 783
 784         return $str;
 785     }
 786 }
 787 ?>
 788