public function testfrom_db_convert() { //execute the method and test if it returns expected values $this->assertSame('2015-11-16 19:32:29', from_db_convert('2015-11-16 19:32:29', 'date')); $this->assertSame('19:32:29', from_db_convert('19:32:29', 'time')); $this->assertSame('2015-11-16 19:32:29', from_db_convert('2015-11-16 19:32:29', 'datetime')); $this->assertSame('2015-11-16 19:32:29', from_db_convert('2015-11-16 19:32:29', 'datetimecombo')); $this->assertSame('2015-11-16 19:32:29', from_db_convert('2015-11-16 19:32:29', 'timestamp')); }
/** * This function retrieves a record of the appropriate type from the DB. * It fills in all of the fields from the DB into the object it was called on. * * @param $id - If ID is specified, it overrides the current value of $this->id. If not specified the current value of $this->id will be used. * @return this - The object that it was called apon or null if exactly 1 record was not found. * */ function check_date_relationships_load() { global $disable_date_format; global $timedate; if (empty($timedate)) { $timedate = TimeDate::getInstance(); } if (empty($this->field_defs)) { return; } foreach ($this->field_defs as $fieldDef) { $field = $fieldDef['name']; if (!isset($this->processed_dates_times[$field])) { $this->processed_dates_times[$field] = '1'; if (empty($this->{$field})) { continue; } if ($field == 'date_modified' || $field == 'date_entered') { $this->{$field} = from_db_convert($this->{$field}, 'datetime'); if (empty($disable_date_format)) { $this->{$field} = $timedate->to_display_date_time($this->{$field}); } } elseif (isset($this->field_name_map[$field]['type'])) { $type = $this->field_name_map[$field]['type']; if ($type == 'relate' && isset($this->field_name_map[$field]['custom_module'])) { $type = $this->field_name_map[$field]['type']; } if ($type == 'date') { $this->{$field} = from_db_convert($this->{$field}, 'date'); if ($this->{$field} == '0000-00-00') { $this->{$field} = ''; } elseif (!empty($this->field_name_map[$field]['rel_field'])) { $rel_field = $this->field_name_map[$field]['rel_field']; if (!empty($this->{$rel_field})) { $this->{$rel_field} = from_db_convert($this->{$rel_field}, 'time'); if (empty($disable_date_format)) { $mergetime = $timedate->merge_date_time($this->{$field}, $this->{$rel_field}); $this->{$field} = $timedate->to_display_date($mergetime); $this->{$rel_field} = $timedate->to_display_time($mergetime); } } } else { if (empty($disable_date_format)) { $this->{$field} = $timedate->to_display_date($this->{$field}, false); } } } elseif ($type == 'datetime' || $type == 'datetimecombo') { if ($this->{$field} == '0000-00-00 00:00:00') { $this->{$field} = ''; } else { $this->{$field} = from_db_convert($this->{$field}, 'datetime'); if (empty($disable_date_format)) { $this->{$field} = $timedate->to_display_date_time($this->{$field}, true, true); } } } elseif ($type == 'time') { if ($this->{$field} == '00:00:00') { $this->{$field} = ''; } else { //$this->$field = from_db_convert($this->$field, 'time'); if (empty($this->field_name_map[$field]['rel_field']) && empty($disable_date_format)) { $this->{$field} = $timedate->to_display_time($this->{$field}, true, false); } } } elseif ($type == 'encrypt' && empty($disable_date_format)) { $this->{$field} = $this->decrypt_after_retrieve($this->{$field}); } } } } }
/** * Uses the audit enabled fields array to find fields whose value has changed. * The before and after values are stored in the bean. * * @param object $bean Sugarbean instance * @return array */ public function getDataChanges(SugarBean &$bean) { $changed_values = array(); $audit_fields = $bean->getAuditEnabledFieldDefinitions(); if (is_array($audit_fields) and count($audit_fields) > 0) { foreach ($audit_fields as $field => $properties) { if (!empty($bean->fetched_row) && array_key_exists($field, $bean->fetched_row)) { if (isset($properties['type'])) { $field_type = $properties['type']; } else { if (isset($properties['dbType'])) { $field_type = $properties['dbType']; } else { if (isset($properties['data_type'])) { $field_type = $properties['data_type']; } else { $field_type = $properties['dbtype']; } } } // Bug # 44624 - check for currency type and cast to float // this ensures proper matching for change log if (strcmp($field_type, "currency") == 0) { $before_value = (double) $bean->fetched_row[$field]; $after_value = (double) $bean->{$field}; } else { $before_value = $bean->fetched_row[$field]; $after_value = $bean->{$field}; } //Because of bug #25078(sqlserver haven't 'date' type, trim extra "00:00:00" when insert into *_cstm table). so when we read the audit datetime field from sqlserver, we have to replace the extra "00:00:00" again. if (!empty($field_type) && $field_type == 'date') { $before_value = from_db_convert($before_value, $field_type); } //if the type and values match, do nothing. if (!($this->_emptyValue($before_value, $field_type) && $this->_emptyValue($after_value, $field_type))) { if (trim($before_value) !== trim($after_value)) { // Bug #42475: Don't directly compare numeric values, instead do the subtract and see if the comparison comes out to be "close enough", it is necessary for floating point numbers. if (!($this->_isTypeNumber($field_type) && abs(trim($before_value) + 0 - (trim($after_value) + 0)) < 0.001)) { if (!($this->_isTypeBoolean($field_type) && $this->_getBooleanValue($before_value) == $this->_getBooleanValue($after_value))) { $changed_values[$field] = array('field_name' => $field, 'data_type' => $field_type, 'before' => $before_value, 'after' => $after_value); } } } } } } } return $changed_values; }
/** * Uses the audit enabled fields array to find fields whose value has changed. * The before and after values are stored in the bean. * * @param object $bean Sugarbean instance * @return array */ public function getDataChanges(SugarBean &$bean) { $changed_values = array(); $audit_fields = $bean->getAuditEnabledFieldDefinitions(); if (is_array($audit_fields) and count($audit_fields) > 0) { foreach ($audit_fields as $field => $properties) { if (!empty($bean->fetched_row) && array_key_exists($field, $bean->fetched_row)) { $before_value = $bean->fetched_row[$field]; $after_value = $bean->{$field}; if (isset($properties['type'])) { $field_type = $properties['type']; } else { if (isset($properties['dbType'])) { $field_type = $properties['dbType']; } else { if (isset($properties['data_type'])) { $field_type = $properties['data_type']; } else { $field_type = $properties['dbtype']; } } } //Because of bug #25078(sqlserver haven't 'date' type, trim extra "00:00:00" when insert into *_cstm table). so when we read the audit datetime field from sqlserver, we have to replace the extra "00:00:00" again. if (!empty($field_type) && $field_type == 'date') { $before_value = from_db_convert($before_value, $field_type); } //if the type and values match, do nothing. if (!($this->_emptyValue($before_value, $field_type) && $this->_emptyValue($after_value, $field_type))) { if (trim($before_value) !== trim($after_value)) { if (!($this->_isTypeNumber($field_type) && trim($before_value) + 0 == trim($after_value) + 0)) { if (!($this->_isTypeBoolean($field_type) && $this->_getBooleanValue($before_value) == $this->_getBooleanValue($after_value))) { $changed_values[$field] = array('field_name' => $field, 'data_type' => $field_type, 'before' => $before_value, 'after' => $after_value); } } } } } } } return $changed_values; }
$focus_calls_list = $call->get_full_list("time_start", $where); } $open_activity_list = array(); if (count($focus_meetings_list) > 0) { foreach ($focus_meetings_list as $meeting) { $td = $timedate->merge_date_time(from_db_convert($meeting->date_start, 'date'), from_db_convert($meeting->time_start, 'time')); $tag = 'span'; if ($meeting->ACLAccess('view', $meeting->isOwner($current_user->id))) { $tag = 'a'; } $open_activity_list[] = array('name' => $meeting->name, 'id' => $meeting->id, 'type' => 'Meeting', 'module' => 'Meetings', 'status' => $meeting->status, 'parent_id' => $meeting->parent_id, 'parent_type' => $meeting->parent_type, 'parent_name' => $meeting->parent_name, 'contact_id' => $meeting->contact_id, 'contact_name' => $meeting->contact_name, 'normal_date_start' => $meeting->date_start, 'date_start' => $timedate->to_display_date($td), 'normal_time_start' => $meeting->time_start, 'time_start' => $timedate->to_display_time($td, true), 'required' => $meeting->required, 'accept_status' => $meeting->accept_status, 'tag' => $tag); } } if (count($focus_calls_list) > 0) { foreach ($focus_calls_list as $call) { $td = $timedate->merge_date_time(from_db_convert($call->date_start, 'date'), from_db_convert($call->time_start, 'time')); $tag = 'span'; if ($call->ACLAccess('view', $call->isOwner($current_user->id))) { $tag = 'a'; } $open_activity_list[] = array('name' => $call->name, 'id' => $call->id, 'type' => 'Call', 'module' => 'Calls', 'status' => $call->status, 'parent_id' => $call->parent_id, 'parent_type' => $call->parent_type, 'parent_name' => $call->parent_name, 'contact_id' => $call->contact_id, 'contact_name' => $call->contact_name, 'date_start' => $timedate->to_display_date($td), 'normal_date_start' => $call->date_start, 'normal_time_start' => $call->time_start, 'time_start' => $timedate->to_display_time($td, true), 'required' => $call->required, 'accept_status' => $call->accept_status, 'tag' => $tag); } } /////////////////////////////////////////////////////////////////////////////// //// START OUTPUT $xtpl = new XTemplate('modules/Activities/OpenListView.html'); $xtpl->assign("MOD", $current_module_strings); $xtpl->assign("APP", $app_strings); $xtpl->assign('JSON_CONFIG_JAVASCRIPT', $json_config->get_static_json_server()); $xtpl->assign("SUGAR_VERSION", $sugar_version); $xtpl->assign("JS_CUSTOM_VERSION", $sugar_config['js_custom_version']);
/** * This function retrieves a record of the appropriate type from the DB. * It fills in all of the fields from the DB into the object it was called on. * param $id - If ID is specified, it overrides the current value of $this->id. If not specified the current value of $this->id will be used. * returns this - The object that it was called apon or null if exactly 1 record was not found. * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc. * All Rights Reserved. * Contributor(s): ______________________________________.. */ function check_date_relationships_load() { global $disable_date_format; if (!empty($disable_date_format)) { return; } global $timedate; if (empty($timedate)) { $timedate = new TimeDate(); } if (empty($this->field_defs)) { return; } foreach ($this->field_defs as $fieldDef) { $field = $fieldDef['name']; if (!key_exists($field, $this->processed_dates_times)) { $this->processed_dates_times[$field] = '1'; if ($field == 'date_modified' || $field == 'date_entered') { if (!empty($this->{$field})) { $this->{$field} = $timedate->to_display_date_time($this->{$field}); } } elseif (!empty($this->{$field}) && isset($this->field_name_map[$field]['type'])) { $type = $this->field_name_map[$field]['type']; if ($type == 'relate' && isset($this->field_name_map[$field]['custom_type'])) { $type = $this->field_name_map[$field]['custom_type']; } if ($type == 'date') { $this->{$field} = from_db_convert($this->{$field}, 'date'); if ($this->{$field} == '0000-00-00') { $this->{$field} = ''; } elseif (!empty($this->field_name_map[$field]['rel_field'])) { $rel_field = $this->field_name_map[$field]['rel_field']; if (!empty($this->{$rel_field})) { $this->{$rel_field} = from_db_convert($this->{$rel_field}, 'time'); $mergetime = $timedate->merge_date_time($this->{$field}, $this->{$rel_field}); $this->{$field} = $timedate->to_display_date($mergetime); $this->{$rel_field} = $timedate->to_display_time($mergetime); } } else { $this->{$field} = $timedate->to_display_date($this->{$field}, false); } } elseif ($type == 'datetime') { if ($this->{$field} == '0000-00-00 00:00:00') { $this->{$field} = ''; } else { $this->{$field} = $timedate->to_display_date_time($this->{$field}); } } elseif ($type == 'time') { if ($this->{$field} == '00:00:00') { $this->{$field} = ''; } else { //$this->$field = from_db_convert($this->$field, 'time'); if (empty($this->field_name_map[$field]['rel_field'])) { $this->{$field} = $timedate->to_display_time($this->{$field}, true, false); } } } } } } }
function addActivities() { global $app_list_strings, $timedate, $current_user; // cn: get a boundary limiter $dateTimeMax = gmdate('Y-m-d H:i:s', time() + $app_list_strings['reminder_max_time']); $dateTimeNow = gmdate('Y-m-d H:i:s'); $dateMax = gmdate('Y-m-d', time() + $app_list_strings['reminder_max_time']); $todayGMT = gmdate('Y-m-d'); global $db; // Prep Meetings Query if ($db->dbType == 'mysql') { $selectMeetings = "\n\t\t\t\tSELECT meetings.id, name,reminder_time, description,location, date_start, time_start, \n\t\t\t\t\tCONCAT( date_start, CONCAT(' ', time_start ) ) AS dateTime\n\t\t\t\tFROM meetings LEFT JOIN meetings_users ON meetings.id = meetings_users.meeting_id \n\t\t\t\tWHERE meetings_users.user_id ='" . $current_user->id . "' \n\t\t\t\t\tAND meetings.reminder_time != -1\n\t\t\t\t\tAND meetings_users.deleted != 1\n\t\t\t\tHAVING dateTime >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column // if we're looking at bridging into the next day as if ($dateMax == $todayGMT) { $selectMeetings .= " AND dateTime <= '" . $dateTimeMax . "'"; } } elseif ($db->dbType == 'oci8') { } elseif ($db->dbType == 'mssql') { $selectMeetings = "\n\t\t\t\tSELECT meetings.id, name,reminder_time, CAST(description AS varchar(8000)),location, date_start, time_start\t\t\t\t\n\t\t\t\tFROM meetings LEFT JOIN meetings_users ON meetings.id = meetings_users.meeting_id \n\t\t\t\tWHERE meetings_users.user_id ='" . $current_user->id . "' \n\t\t\t\t\tAND meetings.reminder_time != -1\n\t\t\t\t\tAND meetings_users.deleted != 1\n\t\t\t\tGROUP BY meetings.id, meetings.name, meetings.reminder_time ,CAST(meetings.description AS varchar(8000)), meetings.location, meetings.date_start, meetings.time_start \n\t\t\t\tHAVING date_start + ' ' + time_start >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column // if we're looking at bridging into the next day as if ($dateMax == $todayGMT) { $selectMeetings .= " AND date_start + ' ' + time_start <= '" . $dateTimeMax . "'"; } } $result = $db->query($selectMeetings); while ($row = $db->fetchByAssoc($result)) { $row['time_start'] = from_db_convert($row['time_start'], 'time'); $row['date_start'] = from_db_convert($row['date_start'], 'date'); // need to concatenate since GMT times can bridge two local days $timeStart = strtotime($row['date_start'] . " " . $row['time_start']); $timeRemind = $row['reminder_time']; $timeStart -= $timeRemind; if (isset($row['description'])) { $this->addAlert('Meeting', $row['name'], 'Time:' . $timedate->to_display_date_time($timedate->merge_date_time($row['date_start'], $row['time_start'])), 'Location:' . $row['location'] . "\nDescription:" . $row['description'] . "\nClick OK to view this meeting or click Cancel to dismiss this message.", $timeStart - strtotime($dateTimeNow), 'index.php?action=DetailView&module=Meetings&record=' . $row['id']); } else { $this->addAlert('Meeting', $row['name'], 'Time:' . $timedate->to_display_date_time($timedate->merge_date_time($row['date_start'], $row['time_start'])), 'Location:' . $row['location'] . "\nClick OK to view this meeting or click Cancel to dismiss this message.", $timeStart - strtotime($dateTimeNow), 'index.php?action=DetailView&module=Meetings&record=' . $row['id']); } } // Prep Calls Query if ($db->dbType == 'mysql') { $selectCalls = "\n\t\t\t\tSELECT calls.id, name, reminder_time, description, date_start, time_start, \n\t\t\t\t\tCONCAT( date_start, CONCAT(' ', time_start) ) AS dateTime \n\t\t\t\tFROM calls LEFT JOIN calls_users ON calls.id = calls_users.call_id \n\t\t\t\tWHERE calls_users.user_id ='" . $current_user->id . "' \n\t\t\t\t\tAND calls.reminder_time != -1 \n\t\t\t\t\tAND calls_users.deleted != 1\n\t\t\t\tHAVING dateTime >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column if ($dateMax == $todayGMT) { $selectCalls .= " AND dateTime <= '" . $dateTimeMax . "'"; } } elseif ($db->dbType == 'oci8') { } elseif ($db->dbType == 'mssql') { $selectCalls = "\n\t\t\t\tSELECT calls.id, name, reminder_time, CAST(description AS varchar(8000)), date_start, time_start\n\t\t\t\tFROM calls LEFT JOIN calls_users ON calls.id = calls_users.call_id \n\t\t\t\tWHERE calls_users.user_id ='" . $current_user->id . "' \n\t\t\t\t\tAND calls.reminder_time != -1 \n\t\t\t\t\tAND calls_users.deleted != 1\n\t\t\t\tGROUP BY calls.id, name, reminder_time, CAST(description AS varchar(8000)) , date_start, time_start\n\t\t\t\tHAVING date_start + ' ' + time_start >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column if ($dateMax == $todayGMT) { $selectCalls .= " AND date_start + ' ' + time_start <= '" . $dateTimeMax . "'"; } } global $db; $result = $db->query($selectCalls); while ($row = $db->fetchByAssoc($result)) { $row['time_start'] = from_db_convert($row['time_start'], 'time'); $row['date_start'] = from_db_convert($row['date_start'], 'date'); // need to concatenate since GMT times can bridge two local days $timeStart = strtotime($row['date_start'] . " " . $row['time_start']); $timeRemind = $row['reminder_time']; $timeStart -= $timeRemind; $row['description'] = isset($row['description']) ? $row['description'] : ''; $this->addAlert('Call', $row['name'], 'Time:' . $timedate->to_display_date_time($timedate->merge_date_time($row['date_start'], $row['time_start'])), "Description:" . $row['description'] . "\nClick OK to view this call or click Cancel to dismiss this message.", $timeStart - strtotime($dateTimeNow), 'index.php?action=DetailView&module=Calls&record=' . $row['id']); } }
function addActivities() { global $app_list_strings, $timedate, $current_user, $app_strings; global $sugar_config; // cn: get a boundary limiter $dateTimeMax = gmdate('Y-m-d H:i:s', time() + $app_list_strings['reminder_max_time']); $dateTimeNow = gmdate('Y-m-d H:i:s'); $dateMax = gmdate('Y-m-d', time() + $app_list_strings['reminder_max_time']); $todayGMT = gmdate('Y-m-d'); global $db; // Prep Meetings Query if ($db->dbType == 'mysql') { $selectMeetings = "\r\n\t\t\t\tSELECT meetings.id, name,reminder_time, description,location, date_start, time_start, assigned_user_id, \r\n\t\t\t\t\tCONCAT( date_start, CONCAT(' ', time_start ) ) AS dateTime\r\n\t\t\t\tFROM meetings LEFT JOIN meetings_users ON meetings.id = meetings_users.meeting_id \r\n\t\t\t\tWHERE meetings_users.user_id ='" . $current_user->id . "' \r\n\t\t\t\t\tAND meetings.reminder_time != -1\r\n\t\t\t\t\tAND meetings_users.deleted != 1\r\n\t\t\t\tHAVING dateTime >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column // if we're looking at bridging into the next day as if ($dateMax == $todayGMT) { $selectMeetings .= " AND dateTime <= '" . $dateTimeMax . "'"; } } elseif ($db->dbType == 'oci8') { } elseif ($db->dbType == 'mssql') { $selectMeetings = "\r\n\t\t\t\tSELECT meetings.id, name,reminder_time, CAST(description AS varchar(8000)),location, date_start, time_start, assigned_user_id \r\n\t\t\t\tFROM meetings LEFT JOIN meetings_users ON meetings.id = meetings_users.meeting_id \r\n\t\t\t\tWHERE meetings_users.user_id ='" . $current_user->id . "' \r\n\t\t\t\t\tAND meetings.reminder_time != -1\r\n\t\t\t\t\tAND meetings_users.deleted != 1\r\n\t\t\t\tGROUP BY meetings.id, meetings.name, meetings.reminder_time ,CAST(meetings.description AS varchar(8000)), meetings.location, meetings.date_start, meetings.time_start, meetings.assigned_user_id \r\n\t\t\t\tHAVING date_start + ' ' + time_start >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column // if we're looking at bridging into the next day as if ($dateMax == $todayGMT) { $selectMeetings .= " AND date_start + ' ' + time_start <= '" . $dateTimeMax . "'"; } } $result = $db->query($selectMeetings); /////////////////////////////////////////////////////////////////////// //// MEETING INTEGRATION $meetingIntegration = null; if (isset($sugar_config['meeting_integration']) && !empty($sugar_config['meeting_integration'])) { if (!class_exists($sugar_config['meeting_integration'])) { require_once "modules/{$sugar_config['meeting_integration']}/{$sugar_config['meeting_integration']}.php"; } $meetingIntegration = new $sugar_config['meeting_integration'](); } //// END MEETING INTEGRATION /////////////////////////////////////////////////////////////////////// while ($row = $db->fetchByAssoc($result)) { $row['time_start'] = from_db_convert($row['time_start'], 'time'); $row['date_start'] = from_db_convert($row['date_start'], 'date'); // need to concatenate since GMT times can bridge two local days $timeStart = strtotime($row['date_start'] . " " . $row['time_start']); $timeRemind = $row['reminder_time']; $timeStart -= $timeRemind; $url = 'index.php?action=DetailView&module=Meetings&record=' . $row['id']; $instructions = $app_strings['MSG_JS_ALERT_MTG_REMINDER_MSG']; /////////////////////////////////////////////////////////////////// //// MEETING INTEGRATION if (!empty($meetingIntegration) && $meetingIntegration->isIntegratedMeeting($row['id'])) { $url = $meetingIntegration->miUrlGetJsAlert($row); $instructions = $meetingIntegration->miGetJsAlertInstructions(); } //// END MEETING INTEGRATION /////////////////////////////////////////////////////////////////// // sanitize topic $meetingName = ''; if (!empty($row['name'])) { $meetingName = from_html($row['name']); // addAlert() uses double-quotes to pass to popup - escape double-quotes //$meetingName = str_replace('"', '\"', $meetingName); } // sanitize agenda $desc = ''; if (!empty($row['description'])) { $desc = from_html($row['description']); // addAlert() uses double-quotes to pass to popup - escape double-quotes //$desc = str_replace('"', '\"', $desc); } $description = empty($desc) ? '' : $app_strings['MSG_JS_ALERT_MTG_REMINDER_AGENDA'] . $desc . "\n"; // standard functionality $this->addAlert($app_strings['MSG_JS_ALERT_MTG_REMINDER_MEETING'], $meetingName, $app_strings['MSG_JS_ALERT_MTG_REMINDER_TIME'] . $timedate->to_display_date_time($timedate->merge_date_time($row['date_start'], $row['time_start'])), $app_strings['MSG_JS_ALERT_MTG_REMINDER_LOC'] . $row['location'] . $description . $instructions, $timeStart - strtotime($dateTimeNow), $url); } // Prep Calls Query if ($db->dbType == 'mysql') { $selectCalls = "\r\n\t\t\t\tSELECT calls.id, name, reminder_time, description, date_start, time_start, \r\n\t\t\t\t\tCONCAT( date_start, CONCAT(' ', time_start) ) AS dateTime \r\n\t\t\t\tFROM calls LEFT JOIN calls_users ON calls.id = calls_users.call_id \r\n\t\t\t\tWHERE calls_users.user_id ='" . $current_user->id . "' \r\n\t\t\t\t\tAND calls.reminder_time != -1 \r\n\t\t\t\t\tAND calls_users.deleted != 1\r\n\t\t\t\tHAVING dateTime >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column if ($dateMax == $todayGMT) { $selectCalls .= " AND dateTime <= '" . $dateTimeMax . "'"; } } elseif ($db->dbType == 'oci8') { } elseif ($db->dbType == 'mssql') { $selectCalls = "\r\n\t\t\t\tSELECT calls.id, name, reminder_time, CAST(description AS varchar(8000)), date_start, time_start\r\n\t\t\t\tFROM calls LEFT JOIN calls_users ON calls.id = calls_users.call_id \r\n\t\t\t\tWHERE calls_users.user_id ='" . $current_user->id . "' \r\n\t\t\t\t\tAND calls.reminder_time != -1 \r\n\t\t\t\t\tAND calls_users.deleted != 1\r\n\t\t\t\tGROUP BY calls.id, name, reminder_time, CAST(description AS varchar(8000)) , date_start, time_start\r\n\t\t\t\tHAVING date_start + ' ' + time_start >= '" . $dateTimeNow . "'"; // HAVING because we're comparing against an aggregate column if ($dateMax == $todayGMT) { $selectCalls .= " AND date_start + ' ' + time_start <= '" . $dateTimeMax . "'"; } } global $db; $result = $db->query($selectCalls); while ($row = $db->fetchByAssoc($result)) { $row['time_start'] = from_db_convert($row['time_start'], 'time'); $row['date_start'] = from_db_convert($row['date_start'], 'date'); // need to concatenate since GMT times can bridge two local days $timeStart = strtotime($row['date_start'] . " " . $row['time_start']); $timeRemind = $row['reminder_time']; $timeStart -= $timeRemind; $row['description'] = isset($row['description']) ? $row['description'] : ''; $this->addAlert($app_strings['MSG_JS_ALERT_MTG_REMINDER_CALL'], $row['name'], $app_strings['MSG_JS_ALERT_MTG_REMINDER_TIME'] . $timedate->to_display_date_time($timedate->merge_date_time($row['date_start'], $row['time_start'])), $app_strings['MSG_JS_ALERT_MTG_REMINDER_DESC'] . $row['description'] . $app_strings['MSG_JS_ALERT_MTG_REMINDER_MSG'], $timeStart - strtotime($dateTimeNow), 'index.php?action=DetailView&module=Calls&record=' . $row['id']); } }