Hello.
I have a problem with my subpanel pagination. When I click on the “next” button, an error appears : “Database failure. Please refer to suitecrm.log for details.”
In my ‘suitecrm.log’ file, I have the following error :
Error retrieving Campaign list: Query Failed:DECLARE @topCount INT SET @topCount = 10 SELECT TOP (@topCount) * FROM
((SELECT ROW_NUMBER() OVER (ORDER BY .id) AS row_number, campaign_log.* FROM campaign_log WHERE campaign_log.campaign_id = N'795402bc-ba1f-b716-04b6-5bd1c2787ed5' AND campaign_log.deleted=0 AND activity_type=N'targeted' AND archived=0 )) AS a WHERE row_number > 10::: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier ".id" could not be bound.
After searching, i found the problem, but I don’t know how resolve it :
In file “include/php-sql-parser.php”, the function “process_sql” use parenthesis to split query.
In my case, the problem is my query have only one pair of parenthesis, and the function “process_sql” can’t works as usual.
i found a temporary solution : in file "data/SugarBean.php’, function “get_union_related_list”, I added a counter to count how many times the “foreach” runs. If the “foreach” runs only one time, I take off parenthesis :
$cpt = 0;
foreach ($subpanel_list as $this_subpanel) {
if ($this_subpanel->isDatasourceFunction()
&& empty($this_subpanel->_instance_properties['generate_select'])) {
$shortcut_function_name = $this_subpanel->get_data_source_name();
$parameters = $this_subpanel->get_function_parameters();
if (!empty($parameters)) {
//if the import file function is set, then import the file to call the custom function from
if (is_array($parameters) && isset($parameters['import_function_file'])) {
//this call may happen multiple times, so only require if function does not exist
if (!function_exists($shortcut_function_name)) {
require_once($parameters['import_function_file']);
}
//call function from required file
$tmp_final_query = $shortcut_function_name($parameters);
} else {
//call function from parent bean
$tmp_final_query = $parentbean->$shortcut_function_name($parameters);
}
} else {
$tmp_final_query = $parentbean->$shortcut_function_name();
}
if (!$first) {
$final_query_rows .= ' UNION ALL ( '
. $parentbean->create_list_count_query($tmp_final_query, $parameters) . ' )';
$final_query .= ' UNION ALL ( ' . $tmp_final_query . ' )';
} else {
$final_query_rows = '(' . $parentbean->create_list_count_query($tmp_final_query, $parameters) . ')';
$final_query = '(' . $tmp_final_query . ')';
$first = false;
}
$cpt++;
}
}
if($cpt===1){
if($final_query[0]=='('){
$final_query = substr($final_query,1);
}
if($final_query[strlen($final_query)-1]==')'){
$final_query = substr($final_query,0,-1);
}
}
I’m not sure this code is the best way to do, further I custom a “core” file …
Maybe someone konws a best solution ?