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.
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.
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
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
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.
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.
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 ********************************************************************************/
41 class SugarWidgetFieldDateTime extends SugarWidgetReportField {
43 var $assigned_user=null;
45 function SugarWidgetFieldDateTime(&$layout_manager) {
46 parent::SugarWidgetReportField($layout_manager);
47 $this->reporter = $this->layout_manager->getAttribute('reporter');
50 // get the reporter attribute
51 // deprecated, now called in the constructor
52 function getReporter() {
53 // $this->reporter = $this->layout_manager->getAttribute('reporter');
56 // get the assigned user of the report
57 function getAssignedUser() {
58 $json_obj = getJSONobj();
60 $report_def_str = $json_obj->decode($this->reporter->report_def_str);
62 if(empty($report_def_str['assigned_user_id'])) return false;
64 $this->assigned_user = new User();
65 $this->assigned_user->retrieve($report_def_str['assigned_user_id']);
69 function queryFilterOn(& $layout_def) {
72 $begin = $layout_def['input_name0'];
74 $hasTime = $this->hasTime($begin);
76 $begin .= " 00:00:00";
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);
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";
88 function queryFilterBefore(& $layout_def) {
91 $begin = $layout_def['input_name0'];
92 $hasTime = $this->hasTime($begin);
94 $begin .= " 00:00:00";
97 if($this->getAssignedUser()) {
98 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
101 return $this->_get_column_select($layout_def)."<'".$this->reporter->db->quote($begin)."'\n";
105 function queryFilterAfter(& $layout_def) {
108 $begin = $layout_def['input_name0'];
109 $hasTime = $this->hasTime($begin);
111 $begin .= ' 23:59:59';
114 if($this->getAssignedUser()) {
115 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
118 return $this->_get_column_select($layout_def).">'".$this->reporter->db->quote($begin)."'\n";
121 function queryFilterBetween_Dates(& $layout_def) {
124 $begin = $layout_def['input_name0'];
125 $end = $layout_def['input_name1'];
126 $beginHasTime = $this->hasTime($begin);
127 $endHasTime = $this->hasTime($end);
129 $begin .= " 00:00:00";
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);
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";
144 function queryFilterNot_Equals_str(& $layout_def) {
147 $begin = $layout_def['input_name0'];
149 $hasTime = $this->hasTime($begin);
151 $begin .= " 00:00:00";
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);
160 if ($this->reporter->db->dbType == 'oci8') {
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";
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";
170 function queryFilterTP_yesterday(& $layout_def) {
171 global $timedate, $current_user;
173 $begin_timestamp = time() - 86400;
175 // begin conversion (same code as queryFilterTP_today)
176 $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp);
178 //$begin = $timedate->to_display_date_time($begin);
179 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
181 $begin_parts = explode(' ', $begin);
183 $be = $begin_parts[0] . ' 00:00:00';
184 $en = $begin_parts[0] . ' 23:59:59';
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);
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);
195 if ($this->reporter->db->dbType == 'oci8') {
199 if ($this->reporter->db->dbType == 'mysql')
201 if (isset ($layout_def['rel_field'])) {
202 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
204 $field_name = $this->_get_column_select($layout_def);
206 return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
209 if ($this->reporter->db->dbType == 'mssql')
211 if (isset ($layout_def['rel_field'])) {
212 $field_name = $this->_get_column_select($layout_def) . " + ' ' + " . $layout_def['rel_field'].")";
214 $field_name = $this->_get_column_select($layout_def);
216 return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
219 function queryFilterTP_today(& $layout_def) {
220 global $timedate, $current_user;
222 $begin_timestamp = time();
223 $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp); // get GMT today
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);
228 $begin_parts = explode(' ', $begin);
230 $be = $begin_parts[0] . ' 00:00:00';
231 $en = $begin_parts[0] . ' 23:59:59';
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);
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);
242 if ($this->reporter->db->dbType == 'oci8') {
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'].")";
248 $field_name = $this->_get_column_select($layout_def);
250 return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
253 if (isset ($layout_def['rel_field'])) {
254 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
256 $field_name = $this->_get_column_select($layout_def);
258 return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
262 function queryFilterTP_tomorrow(& $layout_def) {
263 global $timedate, $current_user;
266 $begin_timestamp =time()+ 86400;
268 // begin conversion (same code as queryFilterTP_today)
269 $begin = gmdate($GLOBALS['timedate']->get_db_date_time_format(), $begin_timestamp);
271 //$begin = $timedate->to_display_date_time($begin);
272 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), true, $this->assigned_user);
274 $begin_parts = explode(' ', $begin);
276 $be = $begin_parts[0] . ' 00:00:00';
277 $en = $begin_parts[0] . ' 23:59:59';
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);
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);
288 if ($this->reporter->db->dbType == 'oci8') {
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";
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.
298 function get_users_current_date_time($timestamp=false) {
299 global $current_user;
302 $begin = TimeDate::getInstance()->nowDb();
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);
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;
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
323 function get_db_date($days,$time) {
326 $begin = date($GLOBALS['timedate']->get_db_date_time_format(), time()+(86400 * $days)); //gmt date with day adjustment applied.
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);
331 if ($time=='start') {
332 $begin_parts = explode(' ', $begin);
333 $be = $begin_parts[0] . ' 00:00:00';
335 else if ($time=='end') {
336 $begin_parts = explode(' ', $begin);
337 $be = $begin_parts[0] . ' 23:59:59';
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);
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.
354 function get_start_end_date_filter(& $layout_def, $begin,$end) {
356 if ($this->reporter->db->dbType == 'oci8') {
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'].")";
362 $field_name = $this->_get_column_select($layout_def);
364 return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
367 if (isset ($layout_def['rel_field'])) {
368 $field_name = "CONCAT(".$this->_get_column_select($layout_def).",' ',".$layout_def['rel_field'].")";
370 $field_name = $this->_get_column_select($layout_def);
372 return $field_name.">='".$this->reporter->db->quote($begin)."' AND ".$field_name."<='".$this->reporter->db->quote($end)."'\n";
376 function queryFilterTP_last_7_days(& $layout_def) {
378 $begin=$this->get_db_date(-6,'start');
379 $end=$this->get_db_date(0,'end');
382 return $this->get_start_end_date_filter($layout_def,$begin,$end);
386 function queryFilterTP_next_7_days(& $layout_def) {
388 $begin=$this->get_db_date(0,'start');
389 $end=$this->get_db_date(6,'end');
391 return $this->get_start_end_date_filter($layout_def,$begin,$end);
394 function queryFilterTP_last_month(& $layout_def) {
397 $curr_timestamp= $this->get_users_current_date_time(true);
399 //Get year and month from time stamp.
400 $curr_year=date('Y',$curr_timestamp);
401 $curr_month=date('m',$curr_timestamp);
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);
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);
411 return $this->get_start_end_date_filter($layout_def,$begin,$end);
414 function queryFilterTP_this_month(& $layout_def) {
417 $curr_timestamp= $this->get_users_current_date_time(true);
419 //Get year and month from time stamp.
420 $curr_year=date('Y',$curr_timestamp);
421 $curr_month=date('m',$curr_timestamp);
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);
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) {
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);
439 return $this->get_start_end_date_filter($layout_def,$begin,$end);
442 function queryFilterTP_next_month(& $layout_def) {
445 $curr_timestamp= $this->get_users_current_date_time(true);
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) {
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);
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) {
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);
473 return $this->get_start_end_date_filter($layout_def,$begin,$end);
476 function queryFilterTP_last_30_days(& $layout_def) {
478 $begin=$this->get_db_date(-29,'start');
479 $end=$this->get_db_date(0,'end');
481 return $this->get_start_end_date_filter($layout_def,$begin,$end);
484 function queryFilterTP_next_30_days(& $layout_def) {
485 $begin=$this->get_db_date(0,'start');
486 $end=$this->get_db_date(29,'end');
488 return $this->get_start_end_date_filter($layout_def,$begin,$end);
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";
495 function queryFilterTP_this_quarter(& $layout_def) {
498 function queryFilterTP_last_year(& $layout_def) {
501 $curr_timestamp= $this->get_users_current_date_time(true);
503 //Get year and month from time stamp.
504 $curr_year=date('Y',$curr_timestamp);
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);
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);
515 return $this->get_start_end_date_filter($layout_def,$begin,$end);
518 function queryFilterTP_this_year(& $layout_def) {
520 $curr_timestamp= $this->get_users_current_date_time(true);
522 //Get year and month from time stamp.
523 $curr_year=date('Y',$curr_timestamp);
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);
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);
533 return $this->get_start_end_date_filter($layout_def,$begin,$end);
536 function queryFilterTP_next_year(& $layout_def) {
538 $curr_timestamp= $this->get_users_current_date_time(true);
540 //Get year and month from time stamp.
541 $curr_year=date('Y',$curr_timestamp);
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);
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);
552 return $this->get_start_end_date_filter($layout_def,$begin,$end);
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);
561 if (method_exists($this, $func_name)) {
562 return $this-> $func_name ($layout_def)." \n";
565 return parent :: queryGroupBy($layout_def)." \n";
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
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";
580 $order_by = parent :: queryOrderBy($layout_def)."\n";
584 function displayListPlain($layout_def) {
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){
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);
596 }else{// if date only field
597 $td = $timedate->to_display_date($content, false); // avoid php notice of returing by reference
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];
612 function displayList($layout_def) {
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);
621 $content = parent :: displayListPlain($layout_def);
622 return $timedate->to_display_date_time($content);
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";
633 return parent :: querySelect($layout_def)." \n";
635 function & displayListday(& $layout_def) {
636 return parent:: displayListPlain($layout_def);
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);
646 if (preg_match('/(\d{4})/', $this->displayListPlain($layout_def), $match)) {
649 $temp = null; // avoid notices
654 function & displayListmonth(& $layout_def) {
655 global $app_list_strings;
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]}";
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";
672 return "LEFT( ".$this->_get_column_select($layout_def).",7 ) ".$this->_get_column_alias($layout_def)." \n";
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";
683 return "LEFT(".$this->_get_column_select($layout_def).", 7) \n";
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";
694 return "LEFT(".$this->_get_column_select($layout_def).", 10)".$this->_get_column_alias($layout_def)." \n";
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";
705 return "LEFT(".$this->_get_column_select($layout_def).", 10) \n";
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";
717 return "LEFT( ".$this->_get_column_select($layout_def).",4 ) ".$this->_get_column_alias($layout_def)." \n";
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";
728 return "LEFT(".$this->_get_column_select($layout_def).", 4) \n";
732 function querySelectquarter(& $layout_def) {
733 if ($this->reporter->db->dbType == 'oci8') {
736 elseif ($this->reporter->db->dbType == 'mysql')
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";
741 elseif ($this->reporter->db->dbType == 'mssql')
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";
751 function displayListquarter(& $layout_def) {
753 if (preg_match('/(\d{4})-(\d)/', $this->displayListPlain($layout_def), $match)) {
754 return "Q".$match[2]." ".$match[1];
760 function queryGroupByQuarter($layout_def) {
761 $this->getReporter();
763 if ($this->reporter->db->dbType == 'oci8') {
765 }elseif ($this->reporter->db->dbType == 'mysql')
767 return "CONCAT(LEFT(".$this->_get_column_select($layout_def).", 4), '-', QUARTER(".$this->_get_column_select($layout_def).") )\n";
769 elseif ($this->reporter->db->dbType == 'mssql')
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";
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'],
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>';
812 * @return bool false if the date is a only a date, true if the date includes time.
814 protected function hasTime($date)
816 return strlen(trim($date)) < 11 ? false : true;