SuiteCRM Reports TimeZone Problem because of the default DATE instead of a DATETIME Datafield

Facebooktwittergoogle_pluspinterestby feather

SuiteCRM wants to report sales outside of the parameters condition. I think this is because the machine was allowed to be set to UTC time (not happy about that, albeit it's been changed).

"Effective date" in SuiteCRM uses the original starting date of the contract module and the DB field is a date format, not datetime. on AOR_Report.php I changed line 1540-ish that reads the following:

  case 'Value':

                             $utc = new DateTimeZone("UTC");

                            $dateTime = DateTime::createFromFormat('Y-m-d H:i:s', $condition->value, $utc);

 

To the following:

 case 'Value':

                            // Changed to central time zone on 1/7/18 in an attempt to get reports to produce based on the time entered

                         //   $utc = new DateTimeZone("UTC");

                            $utc = new DateTimeZone("America/Chicago");

                            $dateTime = DateTime::createFromFormat('Y-m-d H:i:s', $condition->value, $utc);

 

Starting at line 1677ish

                    if (!$where_set) {

                        if ($condition->value_type == "Period") {

                            if (array_key_exists($condition->value, $app_list_strings[‘date_time_period_list’])) {

                                $params = $condition->value;

                            } else {

                                $params = base64_decode($condition->value);

                            }

                            $date = getPeriodEndDate($params)->format('Y-m-d H:i:s');

                            $value = '"' . getPeriodDate($params)->format('Y-m-d H:i:s') . '"';

                            $query[‘where’][] = ($tiltLogicOp ? '' : ($condition->logic_op ? $condition->logic_op . ' ' : 'AND '));

                            $tiltLogicOp = false;

                            switch ($aor_sql_operator_list[$condition->operator]) {

                                case "=":

                                    $query[‘where’][] = $field . ' BETWEEN ' . $value . ' AND ' . '"' . $date . '"';

                                    break;

                                case "!=":

                                    $query[‘where’][] = $field . ' NOT BETWEEN ' . $value . ' AND ' . '"' . $date . '"';

                                    break;

                                case ">":

                                case "<":

                                case ">=":

                                case "<=":

                                    $query[‘where’][] = $field . ‘ ‘ . $aor_sql_operator_list[$condition->operator] . ' ' . $value;

                                    break;

                            }

                        } else {

                            if (!$where_set) {

                                $query[‘where’][] = ($tiltLogicOp ? ” : ($condition->logic_op ? $condition->logic_op . ‘ ‘ : ‘AND ‘)) . $field . ‘ ‘ . $aor_sql_operator_list[$condition->operator] . ' ' . $value;

                            }

                        }

                    }

                    $tiltLogicOp = false;

                } else {

                    if ($condition->parenthesis) {

                        if ($condition->parenthesis == 'START') {

                            $query[‘where’][] = ($tiltLogicOp ? '' : ($condition->logic_op ? $condition->logic_op . ' ' : 'AND ')) . '(';

                            $tiltLogicOp = true;

                        } else {

                            $query[‘where’][] = ')';

                            $tiltLogicOp = false;

                        }

                    } else {

                        $GLOBALS[‘log’]->debug('illegal condition');

                    }

                }

            }

 

In the end, I added a new data field that is DATETIME instead of using the DATE data field. It was the easiest way, and then I used a mysql update command to move all the dates from the DATE field to the DATETIME field.

Update crmnew.aos_contracts, crmnew.aos_contracts_cstm

Set crmnew.aos_contracts_cstm.effectivedatetime_c = crmnew.aos_contracts.start_date + INTERVAL 6 hour + Interval 1 minute

where crmnew.aos_contracts.id =  crmnew.aos_contracts_cstm.id_c;

Sure enough, the date and time was "wrong" in the CRM, so I used another SQL update to add time "INTERVAL 6 hour + Interval 1 minute"

I would prefer to fix it via programming (php), albeit I may cause more issues than I'm solving as some staff works in other timezones.

 

Github has a solution that might be viable, I'll have to try it –> https://github.com/ChangezKhan/SuiteCRM/commit/249ba82a7245926aac3c40629792d60536009580

Comments

comments

Call and receive expert consulting or click below for quote
Button linked to quote page for commercial insurance quote