Hi to all, i have an annoing problem when i go to put a condition in a report. I got this error:
Fri Jun 29 15:09:44 2018 [7596][1][FATAL] Query Failed:DECLARE @topCount INT SET @topCount = 20 SELECT TOP (@topCount) * FROM
(
SELECT [meetings].assigned_user_id AS N'Assegnato_a0', COUNT([meetings].id) AS N'ID1' , ROW_NUMBER()
OVER (ORDER BY COUNT([meetings].id) ASC) AS row_number
FROM [meetings] WHERE ( [meetings].date_entered BETWEEN "2018-01-01 00:00:00" AND "2019-01-01 00:00:00" ) AND meetings.deleted = 0 GROUP BY [meetings].assigned_user_id
) AS a
WHERE row_number > 0::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name '2018-01-01 00:00:00'.
The problem is caused by the " in “2018-01-01 00:00:00” AND “2019-01-01 00:00:00” because sql server doesn’t accept " in queries, but need ’
There is a way to fix that?
I’ve found the funciton that make this bug. in AOR_Report.php function build_report_query_where
(line near 1594, i’ve done some edit so i think the line is not correct)
when $condition->value_type == “Period” you have to replace
case "Period":
if (array_key_exists($condition->value, $app_list_strings['date_time_period_list'])) {
$params = $condition->value;
} else {
$params = base64_decode($condition->value);
}
$value = '"' . getPeriodDate($params)->format('Y-m-d H:i:s') . '"';
break;
in
case "Period":
if (array_key_exists($condition->value, $app_list_strings['date_time_period_list'])) {
$params = $condition->value;
} else {
$params = base64_decode($condition->value);
}
$value = '\'' . getPeriodDate($params)->format('Y-m-d H:i:s') . '\'';
break;
on line 1749(same as up, i don’t think is correct)
$value = '"' . getPeriodDate($params)->format('Y-m-d H:i:s') . '"';
in
$value = '\'' . getPeriodDate($params)->format('Y-m-d H:i:s') . '\'';
and at end in 1755 to 1760
case "=":
$query['where'][] = $field . ' BETWEEN ' . $value . ' AND ' . '"' . $date . '"';
break;
case "!=":
$query['where'][] = $field . ' NOT BETWEEN ' . $value . ' AND ' . '"' . $date . '"';
break;
in
case "=":
$query['where'][] = $field . ' BETWEEN ' . $value . ' AND ' . '\'' . $date . '\'';
break;
case "!=":
$query['where'][] = $field . ' NOT BETWEEN ' . $value . ' AND ' . '\'' . $date . '\'';
break;
This will make Reports with period condition compatible with SQL SERVER.
I’ve tested on 7.9 and 7.10 and both have the same bug and it’s fixed by this edit. I don’t know if you want to make a git commit or something else.
let me know
It’s mine. I’ve opened today
1 Like