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