Hi to all, i have a strange bug on the filter in project task, when i filter on finish date the when i have to chose the type of filter.
Is a known bug or do you know where i have to look for to fix this? i’ve tryed to find the id date_finish_advanced_range_choice but without any success.
Suitecrm version 7.9.11
Thanks all
Did you add these fields to the filter? I don’t see then in my Project Tasks filter?
What exactly is the bug, I don’t understand?
Yes i’ve added these filters.
If you notice, in the date start there is a “equal” and if i click on the drop down it will show me some casistic. but if you look on the date finish there isn’t because the dropdown is empty, and i don’t understand why
I’ve just tried with another installation of suitecrm: 7.10.7 and i have the same problem, but in the start date AND finish date. I think is a bug of the crm
Yeah, I get the same, empty dropdowns. I had a quick look at the code but I couldn’t figure it out, sorry.
Please open an Issue with this on Github, if it isn’t already there.
But we really need more people from the community working also on fixes, we can’t keep up with all the issues ourselves…
I will open the issue and try in the weekend to find a way to fix this. Because the only module that is affected is the project task, and i don’t get why
I don’t want to open another ticket, i’ve a fast question, there is a way to see what query does the crm when i select a module? i want to see what is the query behind the list of accounts, because i have a strange bug where some accounts doesen’t appear in the list(without any filter applied) but if i look for them in the filter they appears.
Thanks
If you go in Admin / System settings and increase log level to DEBUG I believe every query will get written out into suitecrm.log
yea it worked. But now i’m so confused.
I’m testing this query:
DECLARE @topCount INT SET @topCount = 21 SELECT TOP (@topCount) * FROM
(
SELECT accounts.id ,accounts_cstm.regione_c, accounts.assigned_user_id , accounts.name , accounts.account_type , accounts.billing_address_city , accounts.phone_office , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, accounts.date_entered , accounts.created_by , ROW_NUMBER()
OVER (ORDER BY accounts.date_entered DESC) AS row_number
FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.assigned_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 where accounts.deleted=0
) AS a
WHERE row_number > 0
But, if i keep the query like that, a lot of account disappear form the list
But then, if i remove row_number>0 it works fine
I really don’t understand why the row number in the first query grous as +3. If you see he get 1 4 7 10 etc.
if i put row_number >1 the query show me 2 5 8 11 14
Any suggest about that? there is a way i could remove or edit this row_number?
Where is that query coming from? Some custom code of yours? Or some screen in SuiteCRM - if so, which one?
I’ve put the log in debug mode, and when i just press Accounts, it shows me the list of my accounts(the first 20. In the suitecrm.log appears that query. I’ve looked for this type of query and i fount that in:
include/database/MssqlManager.php
on line 432 in the public function limitQuery:
public function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true)
{
$start = (int)$start;
$count = (int)$count;
$countVar = '(@topCount)';
$newSQL = $sql;
$distinctSQLARRAY = array();
if (strpos($sql, 'UNION') && !preg_match("/(')(UNION).?(')/i", $sql)) {
$newSQL = $this->handleUnionLimitQuery($sql, $start, $count);
} else {
if ($start < 0) {
$start = 0;
}
$GLOBALS['log']->debug(print_r(func_get_args(), true));
$this->lastsql = $sql;
$matches = array();
preg_match('/^(.*SELECT\b)(.*?\bFROM\b.*\bWHERE\b)(.*)$/isU', $sql, $matches);
if (!empty($matches[3])) {
if ($start == 0) {
$match_two = strtolower($matches[2]);
if (!strpos($match_two, 'distinct') > 0 && strpos($match_two, 'distinct') !== 0) {
$orderByMatch = array();
preg_match('/^(.*)(\bORDER BY\b)(.*)$/is', $matches[3], $orderByMatch);
if (!empty($orderByMatch[3])) {
$selectPart = array();
preg_match('/^(.*)(\bFROM\b.*)$/isU', $matches[2], $selectPart);
$newSQL = "SELECT TOP $countVar * FROM
(
" . $matches[1] . $selectPart[1] . ', ROW_NUMBER()
OVER (ORDER BY ' . $this->returnOrderBy($sql, $orderByMatch[3]) . ') AS row_number
' . $selectPart[2] . $orderByMatch[1] . "
) AS a
WHERE row_number > $start";
} else {
$newSQL = $matches[1] . " TOP $countVar " . $matches[2] . $matches[3];
}
} else {
$distinct_o = strpos($match_two, 'distinct');
$up_to_distinct_str = substr($match_two, 0, $distinct_o);
//check to see if the distinct is within a function, if so, then proceed as normal
if (strpos($up_to_distinct_str, '(')) {
//proceed as normal
$newSQL = $matches[1] . " TOP $countVar " . $matches[2] . $matches[3];
} else {
//if distinct is not within a function, then parse
//string contains distinct clause, "TOP needs to come after Distinct"
//get position of distinct
$match_zero = strtolower($matches[0]);
$distinct_pos = strpos($match_zero, 'distinct');
//get position of where
$where_pos = strpos($match_zero, 'where');
//parse through string
$beg = substr($matches[0], 0, $distinct_pos + 9);
$mid = substr($matches[0], strlen($beg), ($where_pos + 5) - strlen($beg));
$end = substr($matches[0], strlen($beg) + strlen($mid));
//repopulate matches array
$matches[1] = $beg;
$matches[2] = $mid;
$matches[3] = $end;
$newSQL = $matches[1] . " TOP $countVar " . $matches[2] . $matches[3];
}
}
} else {
$orderByMatch = array();
preg_match('/^(.*)(\bORDER BY\b)(.*)$/is', $matches[3], $orderByMatch);
//if there is a distinct clause, parse sql string as we will have to insert the rownumber
//for paging, AFTER the distinct clause
$grpByStr = '';
$hasDistinct = strpos(strtolower($matches[0]), 'distinct');
$hasGroupBy = strpos(strtolower($matches[0]), 'group by');
require_once 'include/php-sql-parser.php';
$parser = new PHPSQLParser();
$sqlArray = $parser->parse($sql);
if ($hasDistinct) {
$matches_sql = strtolower($matches[0]);
//remove reference to distinct and select keywords, as we will use a group by instead
//we need to use group by because we are introducing
// rownumber column which would make every row unique
//take out the select and distinct from string so we can reuse in group by
$dist_str = 'distinct';
preg_match('/\b' . $dist_str . '\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
$matches_sql = trim(substr($matches_sql, $matchesPartSQL[0][1] + strlen($dist_str)));
//get the position of where and from for further processing
preg_match('/\bfrom\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
$from_pos = $matchesPartSQL[0][1];
preg_match('/\where\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
$where_pos = $matchesPartSQL[0][1];
//split the sql into a string before and after the from clause
//we will use the columns being selected to construct the group by clause
if ($from_pos > 0) {
$distinctSQLARRAY[0] = substr($matches_sql, 0, $from_pos);
$distinctSQLARRAY[1] = substr($matches_sql, $from_pos);
//get position of order by (if it exists) so we can strip it from the string
$ob_pos = strpos($distinctSQLARRAY[1], 'order by');
if ($ob_pos) {
$distinctSQLARRAY[1] = substr($distinctSQLARRAY[1], 0, $ob_pos);
}
// strip off last closing parentheses from the where clause
$distinctSQLARRAY[1] = preg_replace('/\)\s$/', ' ', $distinctSQLARRAY[1]);
}
$grpByStr = array();
foreach ($sqlArray['SELECT'] as $record) {
if ($record['expr_type'] === 'const') {
continue;
}
$grpByStr[] = trim($record['base_expr']);
}
$grpByStr = implode(', ', $grpByStr);
} elseif ($hasGroupBy) {
$groupBy = explode('group by', strtolower($matches[0]));
$groupByVars = explode(',', $groupBy[1]);
$grpByStr = $groupByVars[0];
}
if (!empty($orderByMatch[3])) {
//if there is a distinct clause, form query with rownumber after distinct
if ($hasDistinct) {
$newSQL = "SELECT TOP $countVar * FROM
(
SELECT ROW_NUMBER()
OVER (ORDER BY " . preg_replace('/^' . $dist_str . '\s+/', '',
$this->returnOrderBy($sql, $orderByMatch[3])) . ') AS row_number,
count(*) counter, ' . $distinctSQLARRAY[0] . '
' . $distinctSQLARRAY[1] . '
group by ' . $grpByStr . "
) AS a
WHERE row_number > $start";
} else {
$newSQL = "SELECT TOP $countVar * FROM
(
" . $matches[1] . ' ROW_NUMBER()
OVER (ORDER BY ' . $this->returnOrderBy($sql, $orderByMatch[3]) . ') AS row_number,
' . $matches[2] . $orderByMatch[1] . "
) AS a
WHERE row_number > $start";
}
} else {
//if there is a distinct clause, form query with rownumber after distinct
if ($hasDistinct) {
$newSQL = "SELECT TOP $countVar * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY " . $grpByStr . ') AS row_number, count(*) counter, ' . $distinctSQLARRAY[0] . '
' . $distinctSQLARRAY[1] . '
group by ' . $grpByStr . "
)
AS a
WHERE row_number > $start";
} elseif ($hasGroupBy) {
$newSQL = "SELECT TOP $countVar * FROM
(
" . $matches[1] . ' ROW_NUMBER() OVER (ORDER BY ' . $grpByStr . ') AS row_number, ' . $matches[2] . $matches[3] . "
)
AS a
WHERE row_number > $start";
} else {
$newSQL = "SELECT TOP $countVar * FROM
(
" . $matches[1] . ' ROW_NUMBER() OVER (ORDER BY ' . $sqlArray['FROM'][0]['alias'] . '.id) AS row_number, ' . $matches[2] . $matches[3] . "
)
AS a
WHERE row_number > $start";
}
}
}
}
}
$newSQL = "DECLARE @topCount INT SET @topCount = $count " . $newSQL;
$GLOBALS['log']->debug('Limit Query: ' . $newSQL);
if ($execute) {
$result = $this->query($newSQL, $dieOnError, $msg);
$this->dump_slow_queries($newSQL);
return $result;
}
return $newSQL;
}
But atually i don’t know whith if create that specific query, some of them are similar
That number that comes up to compare with row_number is “start”:
WHERE row_number > $start";which is for pagination, I believe. So I guess it is meant to be 0 on the first page, 20 for the second, etc. There could be a bug in pagination. I’ve seen a few recently, like this one
https://github.com/salesagility/SuiteCRM/issues?utf8=âś“&q=is%3Aissue+pagination+
true about that, but that strange thing is what it’s doing. i mean, i don’t get why if the where is “row_number > 0” he shows me 1 4 7 10 etc. and not 1 2 3 4 5 6. I’m afraid the problem is caused by the sqlserver
Yes, i think it might be a bug with SQL Server, or at least a very specific problem with the way the query is written.
Maybe you can ask that to the SQL Server experts on Stack Overflow?
Ok so, returning to the first problem, i’ve digged in the code, and i reach the situation where the template is created on cache\themes\SuiteP\modules\ProjectTask\SearcForm_advanced.tpl
When i reach te part of the date_finisch i’ve seen the date_finish_advanced.options is empty
{assign var="id" value=$fields.date_finish_advanced.name }
{if isset($smarty.request.date_finish_advanced_range_choice)}
{assign var="starting_choice" value=$smarty.request.date_finish_advanced_range_choice}
{else}
{assign var="starting_choice" value="="}
{/if}
<div class="clear hidden dateTimeRangeChoiceClear"></div>
<div class="dateTimeRangeChoice" style="white-space:nowrap !important;">
<select id="{$id}_range_choice" name="{$id}_range_choice" onchange="{$id}_range_change(this.value);">
{html_options options=$fields.date_finish_advanced.options selected=$starting_choice}
</select>
</div>
but the date_start instead works fine.
{assign var="id" value=$fields.date_start_advanced.name }
{if isset($smarty.request.date_start_advanced_range_choice)}
{assign var="starting_choice" value=$smarty.request.date_start_advanced_range_choice}
{else}
{assign var="starting_choice" value="="}
{/if}
<div class="clear hidden dateTimeRangeChoiceClear"></div>
<div class="dateTimeRangeChoice" style="white-space:nowrap !important;">
<select id="{$id}_range_choice" name="{$id}_range_choice" onchange="{$id}_range_change(this.value);">
{html_options options=$fields.date_start_advanced.options selected=$starting_choice}
</select>
</div>If i get where this code is generated, i think i could find a fix of this. Do you have any suggest?
So, after a day of failying tries, i’ve tried a stupid thing. in studio, i’ve put in the field a default value “today”. And now it works, for all the date in all the version of crm, so this isn’t a fix, but is a wokring turn around. I wish could help someone with the same problem of mine