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-2013 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
44 var $assigned_user=null;
46 function SugarWidgetFieldDateTime($layout_manager)
48 parent::SugarWidgetReportField($layout_manager);
51 // get the reporter attribute
52 // deprecated, now called in the constructor
56 function getReporter() {
59 // get the assigned user of the report
60 function getAssignedUser()
62 $json_obj = getJSONobj();
64 $report_def_str = $json_obj->decode($this->reporter->report_def_str);
66 if(empty($report_def_str['assigned_user_id'])) return null;
68 $this->assigned_user = new User();
69 $this->assigned_user->retrieve($report_def_str['assigned_user_id']);
70 return $this->assigned_user;
73 function queryFilterOn($layout_def)
76 $begin = $layout_def['input_name0'];
77 $hasTime = $this->hasTime($begin);
78 $date = $timedate->fromString($begin);
81 return $this->queryDay(
87 return $this->queryDateOp(
88 $this->_get_column_select($layout_def),
98 * This function helps to convert a date only value to have a time value as well. It first checks
99 * to see if a time value exists. If a time value exists, the function just returns the date value
100 * passed in. If the date value is the 'Today' macro then some special processing occurs as well.
101 * Finally the time portion is applied depending on whether or not this date should be for the end
102 * in which case the 23:59:59 time value is applied otherwise 00:00:00 is used.
104 * @param $date String value of the date value to expand
105 * @param bool $end Boolean value indicating whether or not this is for an end time period or not
106 * @return $date TimeDate object with time value applied
108 protected function expandDate($date, $end = false)
111 if($this->hasTime($date)) {
115 //C.L. Bug 48616 - If the $date is set to the Today macro, then adjust accordingly
116 if(strtolower($date) == 'today')
118 $startEnd = $timedate->getDayStartEndGMT($timedate->getNow(true));
119 return $end ? $startEnd['end'] : $startEnd['start'];
122 $parsed = $timedate->fromDbDate($date);
123 $date = $timedate->tzUser(new SugarDateTime());
124 $date->setDate($parsed->year, $parsed->month, $parsed->day);
127 return $date->setTime(23, 59, 59);
129 return $date->setTime(0, 0, 0);
133 function queryFilterBefore($layout_def)
135 $begin = $this->expandDate($layout_def['input_name0']);
136 return $this->queryDateOp($this->_get_column_select($layout_def), $begin, '<', "datetime");
139 function queryFilterAfter($layout_def)
141 $begin = $this->expandDate($layout_def['input_name0'], true);
142 return $this->queryDateOp($this->_get_column_select($layout_def), $begin, '>', "datetime");
145 function queryFilterBetween_Dates($layout_def)
147 $begin = $this->expandDate($layout_def['input_name0']);
148 $end = $this->expandDate($layout_def['input_name1'], true);
149 $column = $this->_get_column_select($layout_def);
150 return "(".$this->queryDateOp($column, $begin, ">=", "datetime")." AND ".
151 $this->queryDateOp($column, $end, "<=", "datetime").")\n";
154 function queryFilterNot_Equals_str($layout_def)
158 $column = $this->_get_column_select($layout_def);
159 $begin = $layout_def['input_name0'];
160 $hasTime = $this->hasTime($begin);
162 $end = $this->expandDate($begin, true);
163 $begin = $this->expandDate($begin);
164 $cond = $this->queryDateOp($column, $begin, "<", "datetime")." OR ".
165 $this->queryDateOp($column, $end, ">", "datetime");
167 $cond = $this->queryDateOp($column, $begin, "!=", "datetime");
169 return "($column IS NULL OR $cond)";
173 * Get assigned or logged in user's current date and time value.
174 * @param boolean $timestamp Format of return value, if set to true, return unix like timestamp , else a formatted date.
176 function get_users_current_date_time($timestamp=false)
178 global $current_user;
181 $begin = TimeDate::getInstance()->nowDb();
183 //$begin = $timedate->to_display_date_time($begin,true,true,$this->assigned_user);
184 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
189 $begin_parts = explode(' ', $begin);
190 $date_parts=explode('-', $begin_parts[0]);
191 $time_parts=explode(':', $begin_parts[1]);
192 $curr_timestamp=mktime($time_parts[0],$time_parts[1],0,$date_parts[1], $date_parts[2],$date_parts[0]);
193 return $curr_timestamp;
198 * Get specified date and time for a particalur day, in current user's timezone.
199 * @param int $days Adjust date by this number of days, negative values are valid.
200 * @param time string falg for desired time value, start: minimum time, end: maximum time, default: current time
202 function get_db_date($days,$time) {
205 $begin = date($GLOBALS['timedate']->get_db_date_time_format(), time()+(86400 * $days)); //gmt date with day adjustment applied.
207 //$begin = $timedate->to_display_date_time($begin,true,true,$this->assigned_user);
208 $begin = $timedate->handle_offset($begin, $timedate->get_db_date_time_format(), false, $this->assigned_user);
210 if ($time=='start') {
211 $begin_parts = explode(' ', $begin);
212 $be = $begin_parts[0] . ' 00:00:00';
214 else if ($time=='end') {
215 $begin_parts = explode(' ', $begin);
216 $be = $begin_parts[0] . ' 23:59:59';
221 //convert date to db format without converting to GMT.
222 $begin = $timedate->handle_offset($be, $timedate->get_db_date_time_format(), false, $this->assigned_user);
228 * Get filter string for a date field.
229 * @param array layout_def field def for field being filtered
230 * @param string $begin start date value (in DB format)
231 * @param string $end End date value (in DB format)
233 function get_start_end_date_filter(& $layout_def, $begin,$end)
235 if (isset ($layout_def['rel_field'])) {
236 $field_name = $this->reporter->db->convert(
237 $this->reporter->db->convert($this->_get_column_select($layout_def), 'date_format', '%Y-%m-%d'),
239 array("' '", $this->reporter->db->convert($layout_def['rel_field'], 'time_format'))
242 $field_name = $this->_get_column_select($layout_def);
244 return $field_name.">=".$this->reporter->db->quoted($begin)." AND ".$field_name."<=".$this->reporter->db->quoted($end)."\n";
248 * Create query for binary operation of field of certain type
249 * Produces query like:
250 * arg1 op to_date(arg2), e.g.:
251 * date_closed < '2009-12-01'
252 * @param string $arg1 1st arg - column name
253 * @param string|DateTime $arg2 2nd arg - value to be converted
255 * @param string $type
257 protected function queryDateOp($arg1, $arg2, $op, $type)
260 if($arg2 instanceof DateTime) {
261 $arg2 = $timedate->asDbType($arg2, $type);
263 return "$arg1 $op ".$this->reporter->db->convert($this->reporter->db->quoted($arg2), $type)."\n";
267 * Return current date in required user's TZ
268 * @return SugarDateTime
270 protected function now()
273 return $timedate->tzUser($timedate->getNow(), $this->getAssignedUser());
277 * Create query from the beginning to the end of certain day
278 * @param array $layout_def
279 * @param SugarDateTime $day
281 protected function queryDay($layout_def, SugarDateTime $day)
283 $begin = $day->get_day_begin();
284 $end = $day->get_day_end();
285 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
288 function queryFilterTP_yesterday($layout_def)
291 return $this->queryDay($layout_def, $this->now()->get("-1 day"));
294 function queryFilterTP_today($layout_def)
297 return $this->queryDay($layout_def, $this->now());
300 function queryFilterTP_tomorrow(& $layout_def)
303 return $this->queryDay($layout_def, $this->now()->get("+1 day"));
306 function queryFilterTP_last_7_days($layout_def)
310 $begin = $this->now()->get("-6 days")->get_day_begin();
311 $end = $this->now()->get_day_end();
313 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
316 function queryFilterTP_next_7_days($layout_def)
320 $begin = $this->now()->get_day_begin();
321 $end = $this->now()->get("+6 days")->get_day_end();
323 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
327 * Create query from the beginning to the end of certain month
328 * @param array $layout_def
329 * @param SugarDateTime $month
331 protected function queryMonth($layout_def, $month)
333 $begin = $month->setTime(0, 0, 0);
334 $end = clone($begin);
335 $end->setDate($begin->year, $begin->month, $begin->days_in_month)->setTime(23, 59, 59);
336 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
339 function queryFilterTP_last_month($layout_def)
342 $month = $this->now();
343 return $this->queryMonth($layout_def, $month->setDate($month->year, $month->month-1, 1));
346 function queryFilterTP_this_month($layout_def)
350 //Bug 62414 - take timezone into account when determining current month
352 $timezoneOffset = $timedate->getUserUTCOffset();
353 $timezoneOffset = "$timezoneOffset minutes";
354 $now->modify($timezoneOffset);
356 return $this->queryMonth($layout_def, $now->get_day_by_index_this_month(0));
359 function queryFilterTP_next_month($layout_def)
362 $month = $this->now();
363 return $this->queryMonth($layout_def, $month->setDate($month->year, $month->month+1, 1));
366 function queryFilterTP_last_30_days($layout_def)
369 $begin = $this->now()->get("-29 days")->get_day_begin();
370 $end = $this->now()->get_day_end();
371 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
374 function queryFilterTP_next_30_days($layout_def)
377 $begin = $this->now()->get_day_begin();
378 $end = $this->now()->get("+29 days")->get_day_end();
379 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
383 * Return the between WHERE query for Quarter filter
385 * Find quarter for given date, modify the start/end with $modifyFilter parameter
387 * @param $layout_def - Filter layout_def
388 * @param string $modifyFilter - Modification to start/end date, used to select previous/next quarter
389 * @param string $date - Date for which to find the quarter filter, if not set uses current date
390 * @return string - BETWEEN WHERE query for quarter filter
392 protected function getQuarterFilter($layout_def, $modifyFilter, $date = '')
394 $timedate = TimeDate::getInstance();
396 // See if date is set, if not, use current date
398 $begin = $timedate->getNow(true);
400 $begin = $timedate->fromString($date);
405 floor(($begin->month - 1) / 3) * 3 + 1, // Find starting month of quarter
409 $end = $begin->get("+3 month")->setTime(23, 59, 59)->get("-1 day");
411 // Modify begin/end if filter is set
412 if (!empty($modifyFilter)) {
413 $begin->modify($modifyFilter);
414 $end->modify($modifyFilter);
417 return $this->get_start_end_date_filter($layout_def, $begin->asDb(), $end->asDb());
421 * Returns part of query for select
423 * @param array $layout_def for field
424 * @return string part of select query with last quarter only
426 public function queryFilterTP_last_quarter($layout_def)
428 return $this->getQuarterFilter($layout_def, '-3 month');
432 * Returns part of query for select
434 * @param array $layout_def for field
435 * @return string part of select query with this quarter only
437 public function queryFilterTP_this_quarter($layout_def)
439 return $this->getQuarterFilter($layout_def, '');
443 * Returns part of query for select
445 * @param array $layout_def for field
446 * @return string part of select query with next quarter only
448 public function queryFilterTP_next_quarter($layout_def)
450 return $this->getQuarterFilter($layout_def, '+3 month');
453 function queryFilterTP_last_year($layout_def)
456 $begin = $this->now();
457 $begin->setDate($begin->year-1, 1, 1)->setTime(0, 0);
459 $end->setDate($end->year, 12, 31)->setTime(23, 59, 59);
460 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
463 function queryFilterTP_this_year($layout_def)
466 $begin = $this->now();
467 $begin->setDate($begin->year, 1, 1)->setTime(0, 0);
469 $end->setDate($end->year, 12, 31)->setTime(23, 59, 59);
470 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
473 function queryFilterTP_next_year(& $layout_def)
476 $begin = $this->now();
477 $begin->setDate($begin->year+1, 1, 1)->setTime(0, 0);
479 $end->setDate($end->year, 12, 31)->setTime(23, 59, 59);
480 return $this->get_start_end_date_filter($layout_def,$begin->asDb(),$end->asDb());
483 function queryGroupBy($layout_def)
485 // i guess qualifier and column_function are the same..
486 if (!empty ($layout_def['qualifier'])) {
487 $func_name = 'queryGroupBy'.$layout_def['qualifier'];
488 if (method_exists($this, $func_name)) {
489 return $this-> $func_name ($layout_def)." \n";
492 return parent :: queryGroupBy($layout_def)." \n";
495 function queryOrderBy($layout_def)
497 if (!empty ($layout_def['qualifier'])) {
498 $func_name ='queryOrderBy'.$layout_def['qualifier'];
499 if (method_exists($this, $func_name)) {
500 return $this-> $func_name ($layout_def)."\n";
503 $order_by = parent :: queryOrderBy($layout_def)."\n";
507 function displayListPlain($layout_def) {
509 $content = parent:: displayListPlain($layout_def);
510 // awu: this if condition happens only in Reports where group by month comes back as YYYY-mm format
511 if (count(explode('-',$content)) == 2){
514 }elseif(substr_count($layout_def['type'], 'date') > 0){
515 // if date time field
516 if(substr_count($layout_def['type'], 'time') > 0 && $this->get_time_part($content)!= false){
517 $td = $timedate->to_display_date_time($content);
519 }else{// if date only field
520 $td = $timedate->to_display_date($content, false); // Avoid PHP notice of returning by reference.
526 function get_time_part($date_time_value)
530 $date_parts=$timedate->split_date_time($date_time_value);
531 if (count($date_parts) > 1) {
532 return $date_parts[1];
538 function displayList($layout_def) {
540 // i guess qualifier and column_function are the same..
541 if (!empty ($layout_def['column_function'])) {
542 $func_name = 'displayList'.$layout_def['column_function'];
543 if (method_exists($this, $func_name)) {
544 return $this-> $func_name ($layout_def);
547 $content = parent :: displayListPlain($layout_def);
548 return $timedate->to_display_date_time($content);
551 function querySelect(& $layout_def) {
552 // i guess qualifier and column_function are the same..
553 if (!empty ($layout_def['column_function'])) {
554 $func_name = 'querySelect'.$layout_def['column_function'];
555 if (method_exists($this, $func_name)) {
556 return $this-> $func_name ($layout_def)." \n";
559 return parent :: querySelect($layout_def)." \n";
561 function & displayListday(& $layout_def) {
562 $value = parent:: displayListPlain($layout_def);
566 function & displayListyear(& $layout_def) {
567 global $app_list_strings;
568 $value = parent:: displayListPlain($layout_def);
572 function displayListmonth($layout_def)
574 global $app_list_strings;
577 if (preg_match('/(\d{4})-(\d\d)/', $this->displayListPlain($layout_def), $match)) {
578 $match[2] = preg_replace('/^0/', '', $match[2]);
579 $display = $app_list_strings['dom_cal_month_long'][$match[2]]." {$match[1]}";
586 * Returns part of query for select
588 * @param array $layout_def for field
589 * @return string part of select query with year & month only
591 function querySelectmonth($layout_def)
593 $return = $this->_get_column_select($layout_def);
594 if ($layout_def['type'] == 'datetime')
596 $return = $this->reporter->db->convert($return, 'add_tz_offset');
598 return $this->reporter->db->convert($return, "date_format", array('%Y-%m')) . ' ' . $this->_get_column_alias($layout_def) . "\n";
602 * Returns part of query for group by
604 * @param array $layout_def for field
605 * @return string part of group by query with year & month only
607 function queryGroupByMonth($layout_def)
609 $return = $this->_get_column_select($layout_def);
610 if ($layout_def['type'] == 'datetime')
612 $return = $this->reporter->db->convert($return, 'add_tz_offset');
614 return $this->reporter->db->convert($return, "date_format", array('%Y-%m')) . "\n";
618 * For oracle we have to return order by string like group by string instead of return field alias
620 * @param array $layout_def definition of field
621 * @return string order by string for field
623 function queryOrderByMonth($layout_def)
625 $return = $this->_get_column_select($layout_def);
626 if ($layout_def['type'] == 'datetime')
628 $return = $this->reporter->db->convert($return, 'add_tz_offset');
630 $orderBy = $this->reporter->db->convert($return, "date_format", array('%Y-%m'));
632 if (empty($layout_def['sort_dir']) || $layout_def['sort_dir'] == 'a')
634 return $orderBy . " ASC\n";
638 return $orderBy . " DESC\n";
643 * Returns part of query for select
645 * @param array $layout_def for field
646 * @return string part of select query with year & month & day
648 function querySelectday($layout_def)
650 $return = $this->_get_column_select($layout_def);
651 if ($layout_def['type'] == 'datetime')
653 $return = $this->reporter->db->convert($return, 'add_tz_offset');
655 return $this->reporter->db->convert($return, "date_format", array('%Y-%m-%d')) . ' ' . $this->_get_column_alias($layout_def) . "\n";
659 * Returns part of query for group by
661 * @param array $layout_def for field
662 * @return string part of group by query with year & month & day
664 function queryGroupByDay($layout_def)
666 $return = $this->_get_column_select($layout_def);
667 if ($layout_def['type'] == 'datetime')
669 $return = $this->reporter->db->convert($return, 'add_tz_offset');
671 return $this->reporter->db->convert($return, "date_format", array('%Y-%m-%d')) . "\n";
675 * Returns part of query for select
677 * @param array $layout_def for field
678 * @return string part of select query with year only
680 function querySelectyear($layout_def)
682 $return = $this->_get_column_select($layout_def);
683 if ($layout_def['type'] == 'datetime')
685 $return = $this->reporter->db->convert($return, 'add_tz_offset');
687 return $this->reporter->db->convert($return, "date_format", array('%Y')) . ' ' . $this->_get_column_alias($layout_def) . "\n";
691 * Returns part of query for group by
693 * @param array $layout_def for field
694 * @return string part of group by query with year only
696 function queryGroupByYear($layout_def)
698 $return = $this->_get_column_select($layout_def);
699 if ($layout_def['type'] == 'datetime')
701 $return = $this->reporter->db->convert($return, 'add_tz_offset');
703 return $this->reporter->db->convert($return, "date_format", array('%Y')) . "\n";
706 function querySelectquarter($layout_def)
708 $column = $this->_get_column_select($layout_def);
709 return $this->reporter->db->convert($this->reporter->db->convert($column, "date_format", array('%Y')),
711 array("'-'", $this->reporter->db->convert($column, "quarter")))
712 ." ".$this->_get_column_alias($layout_def)."\n";
715 function displayListquarter(& $layout_def) {
717 if (preg_match('/(\d{4})-(\d)/', $this->displayListPlain($layout_def), $match)) {
718 return "Q".$match[2]." ".$match[1];
724 function queryGroupByQuarter($layout_def)
726 $this->getReporter();
727 $column = $this->_get_column_select($layout_def);
728 return $this->reporter->db->convert($this->reporter->db->convert($column, "date_format", array('%Y')),
730 array("'-'", $this->reporter->db->convert($column, "quarter")));
734 * For oracle we have to return order by string like group by string instead of return field alias
736 * @param array $layout_def definition of field
737 * @return string order by string for field
739 public function queryOrderByQuarter($layout_def)
741 $column = $this->_get_column_select($layout_def);
742 $orderBy = $this->reporter->db->convert(
743 $this->reporter->db->convert($column, "date_format", array('%Y')),
745 array("'-'", $this->reporter->db->convert($column, "quarter"))
749 if (empty($layout_def['sort_dir']) || $layout_def['sort_dir'] == 'a')
751 return $orderBy . " ASC\n";
755 return $orderBy . " DESC\n";
759 function displayInput(&$layout_def) {
760 global $timedate, $current_language, $app_strings;
761 $home_mod_strings = return_module_language($current_language, 'Home');
762 $filterTypes = array(' ' => $app_strings['LBL_NONE'],
763 'TP_today' => $home_mod_strings['LBL_TODAY'],
764 'TP_yesterday' => $home_mod_strings['LBL_YESTERDAY'],
765 'TP_tomorrow' => $home_mod_strings['LBL_TOMORROW'],
766 'TP_this_month' => $home_mod_strings['LBL_THIS_MONTH'],
767 'TP_this_year' => $home_mod_strings['LBL_THIS_YEAR'],
768 'TP_last_30_days' => $home_mod_strings['LBL_LAST_30_DAYS'],
769 'TP_last_7_days' => $home_mod_strings['LBL_LAST_7_DAYS'],
770 'TP_last_month' => $home_mod_strings['LBL_LAST_MONTH'],
771 'TP_last_year' => $home_mod_strings['LBL_LAST_YEAR'],
772 'TP_next_30_days' => $home_mod_strings['LBL_NEXT_30_DAYS'],
773 'TP_next_7_days' => $home_mod_strings['LBL_NEXT_7_DAYS'],
774 'TP_next_month' => $home_mod_strings['LBL_NEXT_MONTH'],
775 'TP_next_year' => $home_mod_strings['LBL_NEXT_YEAR'],
778 $cal_dateformat = $timedate->get_cal_date_format();
779 $str = "<select name='type_{$layout_def['name']}'>";
780 $str .= get_select_options_with_id($filterTypes, (empty($layout_def['input_name0']) ? '' : $layout_def['input_name0']));
781 // foreach($filterTypes as $value => $label) {
782 // $str .= '<option value="' . $value . '">' . $label. '</option>';
792 * @return bool false if the date is a only a date, true if the date includes time.
794 protected function hasTime($date)
796 return strlen(trim($date)) < 11 ? false : true;