include/generic/SugarWidgets/SugarWidgetFielddatetime.php

   1 <?php
   2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   3 /*********************************************************************************
   4  * SugarCRM Community Edition 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
  72         $begin = $layout_def['input_name0'];
  73         $end = $begin;
  74         $hasTime = $this->hasTime($begin);
  75         if(!$hasTime){
  76             $begin .= " 00:00:00";
  77             $end .= " 23:59:59";
  78         }
  79
  80                 if($this->getAssignedUser()) {
  81                         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
  82                         $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
  83                 }
  84
  85                         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";
  86         }
  87
  88         function queryFilterBefore(& $layout_def) {
  89                 global $timedate;
  90
  91         $begin = $layout_def['input_name0'];
  92         $hasTime = $this->hasTime($begin);
  93         if(!$hasTime){
  94             $begin .= " 00:00:00";
  95         }
  96
  97                 if($this->getAssignedUser()) {
  98                         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
  99                 }
 100
 101                         return $this->_get_column_select($layout_def)."<'".$this->reporter->db->quote($begin)."'\n";
 102
 103         }
 104
 105         function queryFilterAfter(& $layout_def) {
 106                 global $timedate;
 107
 108         $begin = $layout_def['input_name0'];
 109         $hasTime = $this->hasTime($begin);
 110         if(!$hasTime){
 111             $begin .= ' 23:59:59';
 112         }
 113
 114                 if($this->getAssignedUser()) {
 115             $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 116                 }
 117
 118                         return $this->_get_column_select($layout_def).">'".$this->reporter->db->quote($begin)."'\n";
 119         }
 120
 121         function queryFilterBetween_Dates(& $layout_def) {
 122                 global $timedate;
 123
 124         $begin = $layout_def['input_name0'];
 125         $end = $layout_def['input_name1'];
 126         $beginHasTime = $this->hasTime($begin);
 127         $endHasTime = $this->hasTime($end);
 128         if(!$beginHasTime){
 129             $begin .= " 00:00:00";
 130         }
 131         if(!$endHasTime){
 132             $end .= " 23:59:59";
 133         }
 134
 135                 if($this->getAssignedUser()) {
 136                         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 137             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 138                 }
 139
 140
 141                         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";
 142         }
 143
 144         function queryFilterNot_Equals_str(& $layout_def) {
 145                 global $timedate;
 146
 147         $begin = $layout_def['input_name0'];
 148         $end = $begin;
 149         $hasTime = $this->hasTime($begin);
 150         if(!$hasTime){
 151             $begin .= " 00:00:00";
 152             $end .= " 23:59:59";
 153         }
 154
 155                 if($this->getAssignedUser()) {
 156                         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 157                         $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 158                 }
 159
 160                 if ($this->reporter->db->dbType == 'oci8') {
 161
 162                 } elseif ($this->reporter->db->dbType == 'mssql'){
 163             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";
 164
 165                 }else{
 166             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";
 167         }
 168         }
 169
 170         function queryFilterTP_yesterday(& $layout_def) {
 171                 global $timedate, $current_user;
 172
 173         $begin_timestamp = time() - 86400;
 174
 175         // begin conversion (same code as queryFilterTP_today)
 176         $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp);
 177         //kbrill bug #13884
 178         //$begin = $timedate->to_display_date_time($begin);
 179                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
 180
 181         $begin_parts = explode(' ', $begin);
 182
 183         $be = $begin_parts[0] . ' 00:00:00';
 184         $en = $begin_parts[0] . ' 23:59:59';
 185
 186         if($this->getAssignedUser()) {
 187             $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 188             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 189         }
 190         else{
 191                 $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $current_user); // convert to GMT today relative to assigned_user
 192             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $current_user);
 193         }
 194
 195                 if ($this->reporter->db->dbType == 'oci8') {
 196
 197                 }
 198
 199                 if ($this->reporter->db->dbType == 'mysql')
 200                 {
 201                         if (isset ($layout_def['rel_field'])) {
 202                                 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
 203                         } else {
 204                                 $field_name = $this->_get_column_select($layout_def);
 205                         }
 206                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 207                 }
 208
 209                 if ($this->reporter->db->dbType == 'mssql')
 210                 {
 211                         if (isset ($layout_def['rel_field'])) {
 212                                 $field_name = $this->_get_column_select($layout_def) . " + ' ' + " . $layout_def['rel_field'].")";
 213                         } else {
 214                                 $field_name = $this->_get_column_select($layout_def);
 215                         }
 216                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 217                 }
 218         }
 219         function queryFilterTP_today(& $layout_def) {
 220                 global $timedate, $current_user;
 221
 222         $begin_timestamp = time();
 223                 $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp); // get GMT today
 224                 //kbrill bug #13884
 225         //$begin = $timedate->to_display_date_time($begin); // convert and handle offset to user's 'display' today
 226                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
 227
 228         $begin_parts = explode(' ', $begin);
 229
 230         $be = $begin_parts[0] . ' 00:00:00';
 231         $en = $begin_parts[0] . ' 23:59:59';
 232
 233                 if($this->getAssignedUser()) {
 234             $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user); // convert to GMT today relative to assigned_user
 235             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 236                 }
 237                 else{
 238                         $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $current_user); // convert to GMT today relative to assigned_user
 239             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $current_user);
 240                 }
 241
 242                 if ($this->reporter->db->dbType == 'oci8') {
 243
 244                 }elseif($this->reporter->db->dbType == 'mssql'){
 245             if (isset ($layout_def['rel_field'])) {
 246                 $field_name = "(".$this->_get_column_select($layout_def)." + ' ' + ".$layout_def['rel_field'].")";
 247             } else {
 248                 $field_name = $this->_get_column_select($layout_def);
 249             }
 250             return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 251
 252         } else {
 253                         if (isset ($layout_def['rel_field'])) {
 254                                 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
 255                         } else {
 256                                 $field_name = $this->_get_column_select($layout_def);
 257                         }
 258                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 259                 }
 260         }
 261
 262         function queryFilterTP_tomorrow(& $layout_def) {
 263                 global $timedate, $current_user;
 264
 265         // get tomorrow
 266         $begin_timestamp =time()+ 86400;
 267
 268         // begin conversion (same code as queryFilterTP_today)
 269         $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp);
 270         //kbrill bug #13884
 271         //$begin = $timedate->to_display_date_time($begin);
 272                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
 273
 274         $begin_parts = explode(' ', $begin);
 275
 276         $be = $begin_parts[0] . ' 00:00:00';
 277         $en = $begin_parts[0] . ' 23:59:59';
 278
 279         if($this->getAssignedUser()) {
 280             $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 281             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 282         }
 283         else{
 284                 $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $current_user);
 285             $end = $timedate->handle_offset($en, $timedate->get_db_date_time_format(), false, $current_user);
 286         }
 287
 288                 if ($this->reporter->db->dbType == 'oci8') {
 289                 } else {
 290                         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";
 291                 }
 292         }
 293
 294     /**
 295      * Get assigned or logged in user's current date and time value.
 296      * @param boolean $timestamp Format of return value, if set to true, return unix like timestamp , else a formatted date.
 297      */
 298         function get_users_current_date_time($timestamp=false) {
 299                 global $current_user;
 300         global $timedate;
 301
 302         $begin = TimeDate::getInstance()->nowDb();
 303         //kbrill bug #13884
 304         //$begin = $timedate->to_display_date_time($begin,true,true,$this->assigned_user);
 305                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 306
 307         if (!$timestamp) {
 308                 return $begin;
 309         } else {
 310                 $begin_parts = explode(' ', $begin);
 311                 $date_parts=explode('-', $begin_parts[0]);
 312                 $time_parts=explode(':', $begin_parts[1]);
 313                 $curr_timestamp=mktime($time_parts[0],$time_parts[1],0,$date_parts[1], $date_parts[2],$date_parts[0]);
 314                 return $curr_timestamp;
 315         }
 316
 317         }
 318         /**
 319          * Get specified date and time for a particalur day, in current user's timezone.
 320          * @param int $days Adjust date by this number of days, negative values are valid.
 321          * @param time string falg for desired time value, start: minimum time, end: maximum time, default: current time
 322          */
 323         function get_db_date($days,$time) {
 324         global $timedate;
 325
 326         $begin = date($GLOBALS['timedate']->get_db_date_time_format(), time()+(86400 * $days));  //gmt date with day adjustment applied.
 327         //kbrill bug #13884
 328         //$begin = $timedate->to_display_date_time($begin,true,true,$this->assigned_user);
 329                 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 330
 331         if ($time=='start') {
 332             $begin_parts = explode(' ', $begin);
 333             $be = $begin_parts[0] . ' 00:00:00';
 334         }
 335         else if ($time=='end') {
 336             $begin_parts = explode(' ', $begin);
 337             $be = $begin_parts[0] . ' 23:59:59';
 338         } else {
 339             $be=$begin;
 340         }
 341
 342         //convert date to db format without converting to GMT.
 343         $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 344
 345         return $begin;
 346         }
 347
 348         /**
 349          * Get filter string for a date field.
 350          * @param array layout_def field def for field being filtered
 351          * @param string $begin start date value.
 352          * @param string $end End date value.
 353          */
 354         function get_start_end_date_filter(& $layout_def, $begin,$end) {
 355
 356                 if ($this->reporter->db->dbType == 'oci8') {
 357
 358                 }elseif($this->reporter->db->dbType == 'mssql'){
 359             if (isset ($layout_def['rel_field'])) {
 360                 $field_name = "(".$this->_get_column_select($layout_def)." + ' ' + ".$layout_def['rel_field'].")";
 361             } else {
 362                 $field_name = $this->_get_column_select($layout_def);
 363             }
 364             return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 365
 366         } else {
 367                         if (isset ($layout_def['rel_field'])) {
 368                                 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
 369                         } else {
 370                                 $field_name = $this->_get_column_select($layout_def);
 371                         }
 372                         return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
 373                 }
 374         }
 375
 376         function queryFilterTP_last_7_days(& $layout_def) {
 377
 378         $begin=$this->get_db_date(-6,'start');
 379         $end=$this->get_db_date(0,'end');
 380
 381
 382         return $this->get_start_end_date_filter($layout_def,$begin,$end);
 383
 384         }
 385
 386         function queryFilterTP_next_7_days(& $layout_def) {
 387
 388         $begin=$this->get_db_date(0,'start');
 389         $end=$this->get_db_date(6,'end');
 390
 391                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 392         }
 393
 394         function queryFilterTP_last_month(& $layout_def) {
 395
 396                 global $timedate;
 397                 $curr_timestamp= $this->get_users_current_date_time(true);
 398
 399                 //Get year and month from time stamp.
 400                 $curr_year=date('Y',$curr_timestamp);
 401                 $curr_month=date('m',$curr_timestamp);
 402
 403                 //get start date for last month and convert it to gmt and db format.
 404             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 month",mktime(0,0,0,$curr_month,1,$curr_year)));
 405         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 406
 407             //get end date for last month  and convert it to gmt and db format.
 408         $end=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 day",mktime(23,59,59,$curr_month,1,$curr_year)));
 409             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 410
 411                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 412         }
 413
 414         function queryFilterTP_this_month(& $layout_def) {
 415
 416                 global $timedate;
 417                 $curr_timestamp= $this->get_users_current_date_time(true);
 418
 419                 //Get year and month from time stamp.
 420                 $curr_year=date('Y',$curr_timestamp);
 421                 $curr_month=date('m',$curr_timestamp);
 422
 423                 //get start date for this month and convert it to gmt and db format.
 424             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,$curr_month,1,$curr_year));
 425         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 426
 427             //get end date for this month  and convert it to gmt and db format.
 428             //first get the first day of next month and move back by one day.
 429                 if ($curr_month==12) {
 430                         $curr_month=1;
 431                         $curr_year+=1;
 432                 } else {
 433                         $curr_month+=1;
 434                 }
 435         $end=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 day",mktime(23,59,59,$curr_month,1,$curr_year)));
 436             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 437
 438
 439                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 440         }
 441
 442         function queryFilterTP_next_month(& $layout_def) {
 443
 444                 global $timedate;
 445                 $curr_timestamp= $this->get_users_current_date_time(true);
 446
 447                 //Get year and month from time stamp.
 448                 $curr_year=date('Y',$curr_timestamp);
 449                 $curr_month=date('m',$curr_timestamp);
 450                 if ($curr_month==12) {
 451                         $curr_month=1;
 452                         $curr_year+=1;
 453                 } else {
 454                         $curr_month+=1;
 455                 }
 456
 457                 //get start date for next month and convert it to gmt and db format.
 458             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,$curr_month,1,$curr_year));
 459         $begin=$timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 460
 461             //get end date for next month  and convert it to gmt and db format.
 462             //first get first day of the month after and move back by 1 day.
 463                 if ($curr_month==12) {
 464                         $curr_month=1;
 465                         $curr_year+=1;
 466                 } else {
 467                         $curr_month+=1;
 468                 }
 469         $end=date($GLOBALS['timedate']->get_db_date_time_format(),strtotime("-1 day",(strtotime("1 month",mktime(23,59,59,$curr_month,1,$curr_year)))));
 470             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 471
 472
 473                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 474         }
 475
 476         function queryFilterTP_last_30_days(& $layout_def) {
 477
 478         $begin=$this->get_db_date(-29,'start');
 479         $end=$this->get_db_date(0,'end');
 480
 481         return $this->get_start_end_date_filter($layout_def,$begin,$end);
 482         }
 483
 484         function queryFilterTP_next_30_days(& $layout_def) {
 485         $begin=$this->get_db_date(0,'start');
 486         $end=$this->get_db_date(29,'end');
 487
 488         return $this->get_start_end_date_filter($layout_def,$begin,$end);
 489         }
 490
 491         function queryFilterTP_last_quarter(& $layout_def) {
 492 //              return "LEFT(".$this->_get_column_select($layout_def).",10) BETWEEN (current_date + interval '1' month) AND current_date";
 493         }
 494
 495         function queryFilterTP_this_quarter(& $layout_def) {
 496         }
 497
 498         function queryFilterTP_last_year(& $layout_def) {
 499
 500                 global $timedate;
 501                 $curr_timestamp= $this->get_users_current_date_time(true);
 502
 503                 //Get year and month from time stamp.
 504                 $curr_year=date('Y',$curr_timestamp);
 505                 $curr_year-=1;
 506
 507                 //get start date for last year and convert it to gmt and db format.
 508             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,1,1,$curr_year));
 509         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 510
 511             //get end date for last year  and convert it to gmt and db format.
 512         $end=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(23,59,59,12,31,$curr_year));
 513             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 514
 515                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 516         }
 517
 518         function queryFilterTP_this_year(& $layout_def) {
 519                 global $timedate;
 520                 $curr_timestamp= $this->get_users_current_date_time(true);
 521
 522                 //Get year and month from time stamp.
 523                 $curr_year=date('Y',$curr_timestamp);
 524
 525                 //get start date for this year and convert it to gmt and db format.
 526             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,1,1,$curr_year));
 527         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 528
 529             //get end date for this year  and convert it to gmt and db format.
 530         $end=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(23,59,59,12,31,$curr_year));
 531             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 532
 533                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 534         }
 535
 536         function queryFilterTP_next_year(& $layout_def) {
 537                 global $timedate;
 538                 $curr_timestamp= $this->get_users_current_date_time(true);
 539
 540                 //Get year and month from time stamp.
 541                 $curr_year=date('Y',$curr_timestamp);
 542                 $curr_year+=1;
 543
 544                 //get start date for this year and convert it to gmt and db format.
 545             $begin=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(0,0,0,1,1,$curr_year));
 546         $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 547
 548             //get end date for this year  and convert it to gmt and db format.
 549         $end=date($GLOBALS['timedate']->get_db_date_time_format(),mktime(23,59,59,12,31,$curr_year));
 550             $end = $timedate->handle_offset($end, $timedate->get_db_date_time_format(), false, $this->assigned_user);
 551
 552                 return $this->get_start_end_date_filter($layout_def,$begin,$end);
 553         }
 554
 555         function queryGroupBy($layout_def) {
 556                 // i guess qualifier and column_function are the same..
 557                 if (!empty ($layout_def['qualifier'])) {
 558                         $func_name = 'queryGroupBy'.$layout_def['qualifier'];
 559                         //print_r($layout_def);
 560                         //print $func_name;
 561                         if (method_exists($this, $func_name)) {
 562                                 return $this-> $func_name ($layout_def)." \n";
 563                         }
 564                 }
 565                 return parent :: queryGroupBy($layout_def)." \n";
 566         }
 567
 568         function queryOrderBy($layout_def) {
 569                 // i guess qualifier and column_function are the same..
 570         if ($this->reporter->db->dbType == 'mssql'){
 571             //do nothing if this is for mssql, do not run group by
 572
 573         }
 574                 elseif (!empty ($layout_def['qualifier'])) {
 575                         $func_name ='queryGroupBy'.$layout_def['qualifier'];
 576                         if (method_exists($this, $func_name)) {
 577                                 return $this-> $func_name ($layout_def)."\n";
 578                         }
 579                 }
 580                 $order_by = parent :: queryOrderBy($layout_def)."\n";
 581                 return $order_by;
 582         }
 583
 584     function displayListPlain($layout_def) {
 585         global $timedate;
 586         $content = parent:: displayListPlain($layout_def);
 587         // awu: this if condition happens only in Reports where group by month comes back as YYYY-mm format
 588         if (count(explode('-',$content)) == 2){
 589             return $content;
 590         // if date field
 591         }elseif(substr_count($layout_def['type'], 'date') > 0){
 592             // if date time field
 593             if(substr_count($layout_def['type'], 'time') > 0 && $this->get_time_part($content)!= false){
 594                 $td = $timedate->to_display_date_time($content);
 595                 return $td;
 596             }else{// if date only field
 597                 $td = $timedate->to_display_date($content, false); // avoid php notice of returing by reference
 598                 return $td;
 599             }
 600         }
 601     }
 602     function get_time_part($date_time_value) {
 603         $date_parts=explode(' ', $date_time_value);
 604         if (count($date_parts) == 2) {
 605             $time=$date_parts[1];
 606         } else {
 607             $time=false;
 608         }
 609         return $time;
 610
 611     }
 612     function displayList($layout_def) {
 613         global $timedate;
 614         // i guess qualifier and column_function are the same..
 615         if (!empty ($layout_def['column_function'])) {
 616             $func_name = 'displayList'.$layout_def['column_function'];
 617             if (method_exists($this, $func_name)) {
 618                 return $this-> $func_name ($layout_def);
 619             }
 620         }
 621         $content = parent :: displayListPlain($layout_def);
 622         return $timedate->to_display_date_time($content);
 623     }
 624
 625         function querySelect(& $layout_def) {
 626                 // i guess qualifier and column_function are the same..
 627                 if (!empty ($layout_def['column_function'])) {
 628                         $func_name = 'querySelect'.$layout_def['column_function'];
 629                         if (method_exists($this, $func_name)) {
 630                                 return $this-> $func_name ($layout_def)." \n";
 631                         }
 632                 }
 633                 return parent :: querySelect($layout_def)." \n";
 634         }
 635         function & displayListday(& $layout_def) {
 636                 return parent:: displayListPlain($layout_def);
 637         }
 638
 639         function & displayListyear(& $layout_def) {
 640                 global $app_list_strings;
 641         //if ($this->reporter->db->dbType == 'oci8' || $this->reporter->db->dbType == 'mssql') {
 642                         return parent:: displayListPlain($layout_def);
 643         //}
 644         /*else{
 645                         $match = array();
 646                 if (preg_match('/(\d{4})/', $this->displayListPlain($layout_def), $match)) {
 647                                 return $match[1];
 648                         }
 649                 $temp = null; // avoid notices
 650                 return $temp;
 651                 }*/
 652         }
 653
 654         function & displayListmonth(& $layout_def) {
 655                 global $app_list_strings;
 656                 $display = '';
 657                 $match = array();
 658         if (preg_match('/(\d{4})-(\d\d)/', $this->displayListPlain($layout_def), $match)) {
 659                         $match[2] = preg_replace('/^0/', '', $match[2]);
 660                         $display = $app_list_strings['dom_cal_month_long'][$match[2]]." {$match[1]}";
 661                 }
 662                 return $display;
 663
 664         }
 665         function querySelectmonth(& $layout_def) {
 666         if ($this->reporter->db->dbType == 'oci8') {
 667         }elseif($this->reporter->db->dbType == 'mssql') {
 668             //return "LEFT( ".$this->_get_column_select($layout_def).",6 ) ".$this->_get_column_alias($layout_def)." \n";
 669                 return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),7)".$this->_get_column_alias($layout_def)." \n";
 670
 671         } else {
 672             return "LEFT( ".$this->_get_column_select($layout_def).",7 ) ".$this->_get_column_alias($layout_def)." \n";
 673         }
 674         }
 675
 676         function queryGroupByMonth($layout_def) {
 677                 if ($this->reporter->db->dbType == 'oci8') {
 678                 }elseif($this->reporter->db->dbType == 'mssql') {
 679             //return "LEFT(".$this->_get_column_select($layout_def).", 6) \n";
 680             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ", 121),7) \n";
 681
 682         }else {
 683                         return "LEFT(".$this->_get_column_select($layout_def).", 7) \n";
 684                 }
 685         }
 686
 687         function querySelectday($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).", 6) \n";
 691                 return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),10)".$this->_get_column_alias($layout_def)." \n";
 692
 693         }else {
 694                         return "LEFT(".$this->_get_column_select($layout_def).", 10)".$this->_get_column_alias($layout_def)." \n";
 695                 }
 696         }
 697
 698         function queryGroupByDay($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).", 6) \n";
 702             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ", 121),10) \n";
 703
 704         }else {
 705                         return "LEFT(".$this->_get_column_select($layout_def).", 10) \n";
 706                 }
 707         }
 708
 709
 710         function querySelectyear(& $layout_def) {
 711                 if ($this->reporter->db->dbType == 'oci8') {
 712                 }elseif($this->reporter->db->dbType == 'mssql') {
 713             //return "LEFT( ".$this->_get_column_select($layout_def).",5 ) ".$this->_get_column_alias($layout_def)." \n";
 714             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),4) ".$this->_get_column_alias($layout_def)." \n";
 715
 716         } else {
 717                         return "LEFT( ".$this->_get_column_select($layout_def).",4 ) ".$this->_get_column_alias($layout_def)." \n";
 718                 }
 719         }
 720
 721         function queryGroupByYear($layout_def) {
 722                 if ($this->reporter->db->dbType == 'oci8') {
 723                 }elseif($this->reporter->db->dbType == 'mssql') {
 724             //return "LEFT(".$this->_get_column_select($layout_def).", 5) \n";
 725             return "LEFT(CONVERT (varchar(20), ". $this->_get_column_select($layout_def). ",121),4) \n";
 726
 727         } else {
 728                         return "LEFT(".$this->_get_column_select($layout_def).", 4) \n";
 729                 }
 730         }
 731
 732         function querySelectquarter(& $layout_def) {
 733                 if ($this->reporter->db->dbType == 'oci8') {
 734                 }
 735
 736                 elseif ($this->reporter->db->dbType == 'mysql')
 737                 {
 738                         return "CONCAT(LEFT(".$this->_get_column_select($layout_def).", 4), '-', QUARTER(".$this->_get_column_select($layout_def).") )".$this->_get_column_alias($layout_def)."\n";
 739                 }
 740
 741                 elseif ($this->reporter->db->dbType == 'mssql')
 742                 {
 743                         //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";
 744             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";
 745
 746                 }
 747
 748
 749         }
 750
 751         function displayListquarter(& $layout_def) {
 752                 $match = array();
 753         if (preg_match('/(\d{4})-(\d)/', $this->displayListPlain($layout_def), $match)) {
 754                         return "Q".$match[2]." ".$match[1];
 755                 }
 756                 return '';
 757
 758         }
 759
 760         function queryGroupByQuarter($layout_def) {
 761                 $this->getReporter();
 762
 763                 if ($this->reporter->db->dbType == 'oci8') {
 764
 765                 }elseif ($this->reporter->db->dbType == 'mysql')
 766                 {
 767                         return "CONCAT(LEFT(".$this->_get_column_select($layout_def).", 4), '-', QUARTER(".$this->_get_column_select($layout_def).") )\n";
 768                 }
 769                 elseif ($this->reporter->db->dbType == 'mssql')
 770                 {
 771                         //return "LEFT(".$this->_get_column_select($layout_def).", 4) +  '-' + convert(varchar(20), DatePart(q," . $this->_get_column_select($layout_def).") )\n";
 772             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";
 773
 774                 }
 775
 776
 777         }
 778
 779     function displayInput(&$layout_def) {
 780         global $timedate, $current_language, $app_strings;
 781         $home_mod_strings = return_module_language($current_language, 'Home');
 782         $filterTypes = array(' '                 => $app_strings['LBL_NONE'],
 783                              'TP_today'         => $home_mod_strings['LBL_TODAY'],
 784                              'TP_yesterday'     => $home_mod_strings['LBL_YESTERDAY'],
 785                              'TP_tomorrow'      => $home_mod_strings['LBL_TOMORROW'],
 786                              'TP_this_month'    => $home_mod_strings['LBL_THIS_MONTH'],
 787                              'TP_this_year'     => $home_mod_strings['LBL_THIS_YEAR'],
 788                              'TP_last_30_days'  => $home_mod_strings['LBL_LAST_30_DAYS'],
 789                              'TP_last_7_days'   => $home_mod_strings['LBL_LAST_7_DAYS'],
 790                              'TP_last_month'    => $home_mod_strings['LBL_LAST_MONTH'],
 791                              'TP_last_year'     => $home_mod_strings['LBL_LAST_YEAR'],
 792                              'TP_next_30_days'  => $home_mod_strings['LBL_NEXT_30_DAYS'],
 793                              'TP_next_7_days'   => $home_mod_strings['LBL_NEXT_7_DAYS'],
 794                              'TP_next_month'    => $home_mod_strings['LBL_NEXT_MONTH'],
 795                              'TP_next_year'     => $home_mod_strings['LBL_NEXT_YEAR'],
 796                              );
 797
 798         $cal_dateformat = $timedate->get_cal_date_format();
 799         $str = "<select name='type_{$layout_def['name']}'>";
 800         $str .= get_select_options_with_id($filterTypes, (empty($layout_def['input_name0']) ? '' : $layout_def['input_name0']));
 801 //        foreach($filterTypes as $value => $label) {
 802 //            $str .= '<option value="' . $value . '">' . $label. '</option>';
 803 //        }
 804         $str .= "</select>";
 805
 806
 807         return $str;
 808     }
 809
 810     /**
 811      * @param  $date
 812      * @return bool false if the date is a only a date, true if the date includes time.
 813      */
 814     protected function hasTime($date)
 815     {
 816         return strlen(trim($date)) < 11 ? false : true;
 817     }
 818 }