Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Need Help Counting 'Pending' Status Based on Filtered Data in Table View
I'm facing an issue in my Table View where I want to count the occurrences of 'pending' status based on the currently loaded data and respecting any applied filters. Despite attempting direct SQL queries, I couldn't achieve the desired count.
Essentially, I need to dynamically count the number of 'pending' statuses within the displayed dataset. For instance, if I apply a filter for the last week, I want the count to reflect only the 'pending' statuses within that filtered data. I'm attaching a snapshot to better illustrate the problem. Any guidance or solutions on how to achieve this within the Table View would be greatly appreciated. Thank you.
Essentially, I need to dynamically count the number of 'pending' statuses within the displayed dataset. For instance, if I apply a filter for the last week, I want the count to reflect only the 'pending' statuses within that filtered data. I'm attaching a snapshot to better illustrate the problem. Any guidance or solutions on how to achieve this within the Table View would be greatly appreciated. Thank you.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi,
if I get your request correctly you would like to count only those entries that you show on the visible part of your table, correct?
Or do you need the count for the filtered data overall, meaning that your filter returns 100 results, but you only display 10 on the tableview, you would like to count the pending on the 100 (=filtered result)?
if I get your request correctly you would like to count only those entries that you show on the visible part of your table, correct?
Or do you need the count for the filtered data overall, meaning that your filter returns 100 results, but you only display 10 on the tableview, you would like to count the pending on the 100 (=filtered result)?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hello pböttcher,
I require an overall count of the filtered data, which could potentially be substantial and managed through pagination. Initially, the count should be based on the entire dataset in the table. However, upon applying any filters, the count should dynamically adjust to reflect the filtered dataset. As illustrated in the snapshot, there are summary cards displayed on the same page atop the table view.
Looking forward to your help. Thanks
I require an overall count of the filtered data, which could potentially be substantial and managed through pagination. Initially, the count should be based on the entire dataset in the table. However, upon applying any filters, the count should dynamically adjust to reflect the filtered dataset. As illustrated in the snapshot, there are summary cards displayed on the same page atop the table view.
Looking forward to your help. Thanks
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi,
you can try this:
Add to your hooks/TABLENAME.php file in the _init function:
And add this functions at the end of the hooks/TABLENAME.php file
So you should get in the $filteredcount always the correct count on the filtered data.
you can try this:
Add to your hooks/TABLENAME.php file in the _init function:
Code: Select all
$queryWhere = get_sql_filter_query($options);
$filteredcount=sqlvalue("SELECT count(1) from TABLENAME where YOURSELECTION ".$queryWhere);
Code: Select all
function get_sql_filter_query($a) {
$a->QueryFieldsIndexed = reIndex($a->QueryFieldsFilters);
$f=$a->validate_filters($_REQUEST,4);
array_walk($_REQUEST['FilterAnd'],'makeSafe');
$a->FilterAnd=$_REQUEST['FilterAnd'];
array_walk($_REQUEST['FilterField'],'makeSafe');
$a->FilterField=$_REQUEST['FilterField'];
array_walk($_REQUEST['FilterOperator'],'makeSafe');
$a->FilterOperator=$_REQUEST['FilterOperator'];
array_walk($_REQUEST['FilterValue'],'makeSafe');
$a->FilterValue=$_REQUEST['FilterValue'];
$SearchString = Request::val('SearchString');
if(strlen($SearchString)) {
if($Search_x != '') $FirstRecord = 1;
foreach($a->QueryFieldsQS as $fName => $fCaption)
if(stripos($fName, '<img') === false)
$a->QuerySearchableFields[$fName] = $fCaption;
$sss = makeSafe($SearchString); // safe search string
if(count($a->QuerySearchableFields))
$a->QueryWhere .= ' AND (' .
implode(
" LIKE '%{$sss}%' OR ",
array_keys($a->QuerySearchableFields)
) . " LIKE '%{$sss}%'" .
')';
}
$FiltersPerGroup=4;
$filterGroups[] = [
'join' => '',
'filters' => [ /* one or more strings, each describing a filter */ ]
];
$currentGroup =& $filterGroups[count($filterGroups) - 1];
for($j = 0; $j < $FiltersPerGroup; $j++) {
$ij = $i + $j;
// not a valid filter?
if(!(($a->FilterAnd[$ij] != '' || $ij == 1)
&& $a->FilterField[$ij] != ''
&& $a->FilterOperator[$ij] != ''
&& (
strlen($a->FilterValue[$ij])
|| strpos($a->FilterOperator[$ij], 'empty') !== false
)
)) continue;
if($a->FilterAnd[$ij] == '') $a->FilterAnd[$ij] = 'and';
$currentGroup['filters'][] = '';
$currentFilter =& $currentGroup['filters'][count($currentGroup['filters']) - 1];
// always use the 1st FilterAnd of the group as the group's join
if(empty($currentGroup['join'])) $currentGroup['join'] = thisOr($a->FilterAnd[$i], 'and');
// if this is NOT the first filter in the group, add its FilterAnd, else ignore
if(count($currentGroup['filters']) > 1)
$currentFilter = $a->FilterAnd[$ij] . ' ';
list($isDate, $isDateTime) = fieldIsDateTime($a, $a->FilterField[$ij]);
if($a->FilterOperator[$ij] == 'is-empty' && !$isDateTime)
$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "='' OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';
elseif($a->FilterOperator[$ij] == 'is-not-empty' && !$isDateTime)
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=''";
elseif($a->FilterOperator[$ij] == 'is-empty' && $isDateTime)
$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "=0 OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';
elseif($a->FilterOperator[$ij] == 'is-not-empty' && $isDateTime)
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=0";
elseif($a->FilterOperator[$ij] == 'like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";
elseif($a->FilterOperator[$ij] == 'not-like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " NOT LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";
elseif($isDate) {
$dateValue = mysql_datetime($a->FilterValue[$ij]);
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '$dateValue'";
} else
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '" . makeSafe($a->FilterValue[$ij]) . "'";
}
// construct filters from $filterGroups
$filtersWhere = '';
foreach($filterGroups as $fg) {
if(empty($fg['filters'])) continue;
// ignore 1st join (i.e. use it only if filtersWhere already populated)
if($filtersWhere) $filtersWhere .= " {$fg['join']} ";
$filtersWhere .= '(' . implode(' ', $fg['filters']) . ')';
}
if($filtersWhere) $a->QueryWhere .= " AND ($filtersWhere)";
return $a->QueryWhere;
}
function fieldIsDateTime($a, $fieldIndex) {
static $cache = [];
if(empty($a->QueryFieldsIndexed[$fieldIndex])) return [false, false];
if(isset($cache[$fieldIndex])) return $cache[$fieldIndex];
$tries = 0; $isDateTime = $isDate = false;
$fieldName = str_replace('`', '', $a->QueryFieldsIndexed[$fieldIndex]);
list($tn, $fn) = array_map('makeSafe', explode('.', $fieldName));
while(!($res = sql("SHOW COLUMNS FROM `{$tn}` LIKE '{$fn}'", $eo)) && $tries < 2) {
$tn = substr($tn, 0, -1); // this is to strip # from table alias: 'table1' becomes 'table'
$tries++;
}
if($res !== false && $row = @db_fetch_array($res)) {
$isDateTime = in_array($row['Type'], ['date', 'time', 'datetime']);
$isDate = in_array($row['Type'], ['date', 'datetime']);
}
$cache[$fieldIndex] = [$isDate, $isDateTime];
return $cache[$fieldIndex];
}
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hello pböttcher,
Thank you so much for your effort.
I have tried to test your code but the code is not executing due to some errors mentioned below.
if ($Search_x != '') $FirstRecord = 1;
$ij = $i + $j;
if (empty($currentGroup['join'])) $currentGroup['join'] = thisOr($a->FilterAnd[$i], 'and');
Undefined variables are highlighted in red above
Regarding the below query, how i can use the variable $filteredcount to display the count in my summary card? Will it be globally available outside this _init function?
$filteredcount = sqlvalue('SELECT count(1) from incident_report where "Pending" ' . $queryWhere);
Surprisingly, it is very complex code. I thought it would be easy, but it seems very difficult. I wish there were any other simple way.
Thank you so much for your effort.
I have tried to test your code but the code is not executing due to some errors mentioned below.
if ($Search_x != '') $FirstRecord = 1;
$ij = $i + $j;
if (empty($currentGroup['join'])) $currentGroup['join'] = thisOr($a->FilterAnd[$i], 'and');
Undefined variables are highlighted in red above
Regarding the below query, how i can use the variable $filteredcount to display the count in my summary card? Will it be globally available outside this _init function?
$filteredcount = sqlvalue('SELECT count(1) from incident_report where "Pending" ' . $queryWhere);
Surprisingly, it is very complex code. I thought it would be easy, but it seems very difficult. I wish there were any other simple way.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi,
sorry, please try this.
in your query
$filteredcount = sqlvalue('SELECT count(1) from incident_report where "Pending" ' . $queryWhere);
you are missing the field to compate the "Pending" status.
Other than that I do not know how you want to display you top list with the number of tickets in the boxes. So it is hard to tell.
But the variable will only be available within the scope of the function. If you need it outside, you could use e.g. $_SESSION
sorry, please try this.
Code: Select all
function get_sql_filter_query($a) {
// $a= $option;
if (isset($_REQUEST['FilterAnd'])) {
$a->QueryFieldsIndexed = reIndex($a->QueryFieldsFilters);
array_walk($_REQUEST['FilterAnd'],'makeSafe');
$a->FilterAnd=$_REQUEST['FilterAnd'];
array_walk($_REQUEST['FilterField'],'makeSafe');
$a->FilterField=$_REQUEST['FilterField'];
array_walk($_REQUEST['FilterOperator'],'makeSafe');
$a->FilterOperator=$_REQUEST['FilterOperator'];
array_walk($_REQUEST['FilterValue'],'makeSafe');
$a->FilterValue=$_REQUEST['FilterValue'];
}
$Search_x = Request::val('Search_x');
$SearchString = Request::val('SearchString');
if(strlen($SearchString)) {
if($Search_x != '') $FirstRecord = 1;
foreach($a->QueryFieldsQS as $fName => $fCaption)
if(stripos($fName, '<img') === false)
$a->QuerySearchableFields[$fName] = $fCaption;
$sss = makeSafe($SearchString); // safe search string
if(count($a->QuerySearchableFields))
$a->QueryWhere .= ' AND (' .
implode(
" LIKE '%{$sss}%' OR ",
array_keys($a->QuerySearchableFields)
) . " LIKE '%{$sss}%'" .
')';
}
$FiltersPerGroup=4;
$filterGroups[] = [
'join' => '',
'filters' => [ /* one or more strings, each describing a filter */ ]
];
$currentGroup =& $filterGroups[count($filterGroups) - 1];
for($i = 1; $i <= (datalist_filters_count * $FiltersPerGroup); $i += $FiltersPerGroup) {
for($j = 0; $j < $FiltersPerGroup; $j++) {
$ij = $i + $j;
// not a valid filter?
if(!(($a->FilterAnd[$ij] != '' || $ij == 1)
&& $a->FilterField[$ij] != ''
&& $a->FilterOperator[$ij] != ''
&& (
strlen($a->FilterValue[$ij])
|| strpos($a->FilterOperator[$ij], 'empty') !== false
)
)) continue;
if($a->FilterAnd[$ij] == '') $a->FilterAnd[$ij] = 'and';
$currentGroup['filters'][] = '';
$currentFilter =& $currentGroup['filters'][count($currentGroup['filters']) - 1];
// always use the 1st FilterAnd of the group as the group's join
if(empty($currentGroup['join'])) $currentGroup['join'] = thisOr($a->FilterAnd[$i], 'and');
// if this is NOT the first filter in the group, add its FilterAnd, else ignore
if(count($currentGroup['filters']) > 1)
$currentFilter = $a->FilterAnd[$ij] . ' ';
list($isDate, $isDateTime) = fieldIsDateTime($a, $a->FilterField[$ij]);
if($a->FilterOperator[$ij] == 'is-empty' && !$isDateTime)
$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "='' OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';
elseif($a->FilterOperator[$ij] == 'is-not-empty' && !$isDateTime)
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=''";
elseif($a->FilterOperator[$ij] == 'is-empty' && $isDateTime)
$currentFilter .= '(' . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "=0 OR " . $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' IS NULL)';
elseif($a->FilterOperator[$ij] == 'is-not-empty' && $isDateTime)
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . "!=0";
elseif($a->FilterOperator[$ij] == 'like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";
elseif($a->FilterOperator[$ij] == 'not-like' && !strstr($a->FilterValue[$ij], "%") && !strstr($a->FilterValue[$ij], "_"))
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . " NOT LIKE '%" . makeSafe($a->FilterValue[$ij]) . "%'";
elseif($isDate) {
$dateValue = mysql_datetime($a->FilterValue[$ij]);
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '$dateValue'";
} else
$currentFilter .= $a->QueryFieldsIndexed[($a->FilterField[$ij])] . ' ' . FILTER_OPERATORS[$a->FilterOperator[$ij]] . " '" . makeSafe($a->FilterValue[$ij]) . "'";
}
}
// construct filters from $filterGroups
$filtersWhere = '';
foreach($filterGroups as $fg) {
if(empty($fg['filters'])) continue;
// ignore 1st join (i.e. use it only if filtersWhere already populated)
if($filtersWhere) $filtersWhere .= " {$fg['join']} ";
$filtersWhere .= '(' . implode(' ', $fg['filters']) . ')';
}
if($filtersWhere) $a->QueryWhere .= " AND ($filtersWhere)";
return $a->QueryWhere;
}
function fieldIsDateTime($a, $fieldIndex) {
static $cache = [];
if(empty($a->QueryFieldsIndexed[$fieldIndex])) return [false, false];
if(isset($cache[$fieldIndex])) return $cache[$fieldIndex];
$tries = 0; $isDateTime = $isDate = false;
$fieldName = str_replace('`', '', $a->QueryFieldsIndexed[$fieldIndex]);
list($tn, $fn) = array_map('makeSafe', explode('.', $fieldName));
while(!($res = sql("SHOW COLUMNS FROM `{$tn}` LIKE '{$fn}'", $eo)) && $tries < 2) {
$tn = substr($tn, 0, -1); // this is to strip # from table alias: 'table1' becomes 'table'
$tries++;
}
if($res !== false && $row = @db_fetch_array($res)) {
$isDateTime = in_array($row['Type'], ['date', 'time', 'datetime']);
$isDate = in_array($row['Type'], ['date', 'datetime']);
}
$cache[$fieldIndex] = [$isDate, $isDateTime];
return $cache[$fieldIndex];
}
$filteredcount = sqlvalue('SELECT count(1) from incident_report where "Pending" ' . $queryWhere);
you are missing the field to compate the "Pending" status.
Other than that I do not know how you want to display you top list with the number of tickets in the boxes. So it is hard to tell.
But the variable will only be available within the scope of the function. If you need it outside, you could use e.g. $_SESSION
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hello pböttcher,
It is working perfectly as I wanted. Thank you so much. I heartily appreciate your help.
Regarding my query, I have updated it as below.
As you suggested, I used the session variable to display it. below is how I used this variable.
Is this the only way to use the session variable, or is there a better way to display the count variable?
It is working perfectly as I wanted. Thank you so much. I heartily appreciate your help.
Regarding my query, I have updated it as below.
Code: Select all
$_SESSION["filteredcount"] = sqlvalue('SELECT count(1) from incident_report where incident_report.status = "Pending" ' . $queryWhere);
Code: Select all
<script>
$j(document).ready(function () {
$j("#pendingCountID").text('<?php echo $_SESSION["filteredcount"] ?>');
});
</script>
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi pböttcher,
I wanted to extend my appreciation for the excellent function you've provided. It performs admirably by honoring all applied filters in calculating the count/occurrence of various statuses in the table view.
I'm curious if it's feasible to enhance the function to accommodate the quick search in the same way as you included filters. With the current function you have provided, I can get the count of the required status concerning the applied filters if any. Is it possible if I can get the count of the required status from the search results after using a quick search on the table?
Your expertise in addressing this would be immensely valuable. Thank you for your support in this matter. I look forward to any insights or modifications you might provide.
I wanted to extend my appreciation for the excellent function you've provided. It performs admirably by honoring all applied filters in calculating the count/occurrence of various statuses in the table view.
I'm curious if it's feasible to enhance the function to accommodate the quick search in the same way as you included filters. With the current function you have provided, I can get the count of the required status concerning the applied filters if any. Is it possible if I can get the count of the required status from the search results after using a quick search on the table?
Your expertise in addressing this would be immensely valuable. Thank you for your support in this matter. I look forward to any insights or modifications you might provide.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi,
did you test if it works? I thought that feature is already included. At least it works on my test system with both filter and search.
did you test if it works? I thought that feature is already included. At least it works on my test system with both filter and search.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hello pböttcher,
I've conducted tests, and the filters seem to be functioning correctly. However, when using the search feature, all counts become zero. Please see to the snapshots below for better understandings.
All counts are updated based on the applied filters. All counts become zero while using quick search feature.
I've conducted tests, and the filters seem to be functioning correctly. However, when using the search feature, all counts become zero. Please see to the snapshots below for better understandings.
All counts are updated based on the applied filters. All counts become zero while using quick search feature.
- Attachments
-
- search.png (65.15 KiB) Viewed 3482 times
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi,
can you post some more information. What do you get if you add a seach in the $queryWhere?
can you post some more information. What do you get if you add a seach in the $queryWhere?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hello,
My requirement was to get the count of the 'Pending' value from the currently loaded data in the table. Initially, I managed to retrieve the count from all the data in the database table. However, I specifically need the count from the limited data currently loaded in the table. For instance, if I filter the data to only display the last two weeks' entries, I want the count of 'Pending' from that filtered subset, not from the entire database.
You provided a function, 'get_sql_filter_query,' which initially worked perfectly, displaying the count from the whole dataset upon opening the table view. When I applied filters, it correctly provided the count from the filtered data. However, when I use the quick search field in the table view, it filters the data based on my search term but doesn't return any results from the same query I used to get the count. It seems that the query might not be executed or might need adjustments to accommodate the quick search functionality.
I'm wondering if the function needs an update to consider the quick search feature or if there's an issue with how the query is executed during the search process. I hope you got my point. Thank
Code: Select all
$_SESSION["filteredcount"] = sqlvalue('SELECT count(1) from incident_report where incident_report.status = "Pending" ' . $queryWhere);
You provided a function, 'get_sql_filter_query,' which initially worked perfectly, displaying the count from the whole dataset upon opening the table view. When I applied filters, it correctly provided the count from the filtered data. However, when I use the quick search field in the table view, it filters the data based on my search term but doesn't return any results from the same query I used to get the count. It seems that the query might not be executed or might need adjustments to accommodate the quick search functionality.
I'm wondering if the function needs an update to consider the quick search feature or if there's an issue with how the query is executed during the search process. I hope you got my point. Thank
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi,
I get you point, but need some help from your side to understand why it might not be working.
Is the data being displayed that has a status pending if you apply that filter?
I get you point, but need some help from your side to understand why it might not be working.
Can you show the data of the $queryWhere when you apply your filter and the searchstring you applied + the filters you applied.What do you get if you add a seach in the $queryWhere?
Is the data being displayed that has a status pending if you apply that filter?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hi pböttcher,
I get nothing when I add a search in the $queryWhere. Means all my custom quires returned no count values.
Below is what being returned from $queryWhere = get_sql_filter_query($options); while adding search in the search field.
Below is what being returned from $queryWhere = get_sql_filter_query($options); while adding filter.
Below are my custom quires for your reference.
Please check below line, where $FirstRecord variable is not being used anywhere. FYI
I get nothing when I add a search in the $queryWhere. Means all my custom quires returned no count values.
Below is what being returned from $queryWhere = get_sql_filter_query($options); while adding search in the search field.
Code: Select all
string(1956) " AND (`incident_report`.`id` LIKE '%INC000001512194%' OR `incident_report`.`ref_id` LIKE '%INC000001512194%' OR `incident_report`.`network` LIKE '%INC000001512194%' OR `incident_report`.`nature_of_fault` LIKE '%INC000001512194%' OR if(`incident_report`.`start_time`,date_format(`incident_report`.`start_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR if(`incident_report`.`end_time`,date_format(`incident_report`.`end_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`status` LIKE '%INC000001512194%' OR `incident_report`.`root_cause` LIKE '%INC000001512194%' OR `incident_report`.`service_outage` LIKE '%INC000001512194%' OR `incident_report`.`outage_duration` LIKE '%INC000001512194%' OR `incident_report`.`action_to_restore` LIKE '%INC000001512194%' OR `incident_report`.`severity` LIKE '%INC000001512194%' OR `incident_report`.`hub_cascaded` LIKE '%INC000001512194%' OR `incident_report`.`wo` LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`salam_id`), CONCAT_WS('', `site_db1`.`salam_id`), '') /* Salam Node */ LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`tawal_id`), CONCAT_WS('', `site_db1`.`tawal_id`), '') /* Tawal ID */ LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`region`), CONCAT_WS('', `site_db1`.`region`), '') /* Region */ LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`city`), CONCAT_WS('', `site_db1`.`city`), '') /* City */ LIKE '%INC000001512194%' OR `incident_report`.`service_impact` LIKE '%INC000001512194%' OR `incident_report`.`responsibility` LIKE '%INC000001512194%' OR if(`incident_report`.`tt_creation_time`,date_format(`incident_report`.`tt_creation_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`tt_respond_time` LIKE '%INC000001512194%' OR `incident_report`.`month` LIKE '%INC000001512194%' OR `incident_report`.`tawal_ntt` LIKE '%INC000001512194%' OR `incident_report`.`remarks` LIKE '%INC000001512194%')"
Code: Select all
string(49) " AND ((`incident_report`.`status` <=> 'Pending'))"
Code: Select all
$_SESSION["pendingcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.status = "Pending" ' . $queryWhere);
$_SESSION["closedcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.status = "Closed" ' . $queryWhere);
$_SESSION["criticalcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.severity = "Critical" ' . $queryWhere);
$_SESSION["majorcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.severity = "Major" ' . $queryWhere);
$_SESSION["minorcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE incident_report.severity = "Minor" ' . $queryWhere);
$_SESSION["allcount"] = sqlvalue('SELECT count(1) FROM incident_report WHERE 1=1 ' . $queryWhere);
Code: Select all
if($Search_x != '') $FirstRecord = 1;
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Certainly, here's a revised version of your message:
Hello pböttcher,
I've identified an issue, but I'm unsure how to resolve it. When reviewing the query generated by $queryWhere = get_sql_filter_query($options);, it becomes apparent that during searches in the field below, some lookup fields from another table, named "site_db", are involved. When conducting searches, it seems to encompass all searchable fields. I believe this might be the point requiring attention.
I attempted to apply filters specifically to those columns, but encountered the same problem—no results were returned when using the search function.
Could you please assist in addressing this issue?
Hello pböttcher,
I've identified an issue, but I'm unsure how to resolve it. When reviewing the query generated by $queryWhere = get_sql_filter_query($options);, it becomes apparent that during searches in the field below, some lookup fields from another table, named "site_db", are involved. When conducting searches, it seems to encompass all searchable fields. I believe this might be the point requiring attention.
Code: Select all
string(1956) " AND (`incident_report`.`id` LIKE '%INC000001512194%' OR `incident_report`.`ref_id` LIKE '%INC000001512194%' OR `incident_report`.`network` LIKE '%INC000001512194%' OR `incident_report`.`nature_of_fault` LIKE '%INC000001512194%' OR if(`incident_report`.`start_time`,date_format(`incident_report`.`start_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR if(`incident_report`.`end_time`,date_format(`incident_report`.`end_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`status` LIKE '%INC000001512194%' OR `incident_report`.`root_cause` LIKE '%INC000001512194%' OR `incident_report`.`service_outage` LIKE '%INC000001512194%' OR `incident_report`.`outage_duration` LIKE '%INC000001512194%' OR `incident_report`.`action_to_restore` LIKE '%INC000001512194%' OR `incident_report`.`severity` LIKE '%INC000001512194%' OR `incident_report`.`hub_cascaded` LIKE '%INC000001512194%' OR `incident_report`.`wo` LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`salam_id`), CONCAT_WS('', `site_db1`.`salam_id`), '') /* Salam Node */ LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`tawal_id`), CONCAT_WS('', `site_db1`.`tawal_id`), '') /* Tawal ID */ LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`region`), CONCAT_WS('', `site_db1`.`region`), '') /* Region */ LIKE '%INC000001512194%' OR IF( CHAR_LENGTH(`site_db1`.`city`), CONCAT_WS('', `site_db1`.`city`), '') /* City */ LIKE '%INC000001512194%' OR `incident_report`.`service_impact` LIKE '%INC000001512194%' OR `incident_report`.`responsibility` LIKE '%INC000001512194%' OR if(`incident_report`.`tt_creation_time`,date_format(`incident_report`.`tt_creation_time`,'%m/%d/%Y %H:%i'),'') LIKE '%INC000001512194%' OR `incident_report`.`tt_respond_time` LIKE '%INC000001512194%' OR `incident_report`.`month` LIKE '%INC000001512194%' OR `incident_report`.`tawal_ntt` LIKE '%INC000001512194%' OR `incident_report`.`remarks` LIKE '%INC000001512194%')"
Could you please assist in addressing this issue?
Re: Need Help Counting 'Pending' Status Based on Filtered Data in Table View
Hello pböttcher,
I wanted to update you that I have resolved my issue. The problem was in the SQL query itself; there was a missing JOIN clause. After adding the JOIN, both Filters and Search results are now reflecting in all my counts.
I have not made any changes to the function ($queryWhere = get_sql_filter_query($options);) you provided to me. Thank you so much for your support.
Below is my updated query, and with regards to the variable used to store the count result, I initially used the $_SESSION variable, and now I am utilizing the $GLOBALS variable.
I wanted to update you that I have resolved my issue. The problem was in the SQL query itself; there was a missing JOIN clause. After adding the JOIN, both Filters and Search results are now reflecting in all my counts.
I have not made any changes to the function ($queryWhere = get_sql_filter_query($options);) you provided to me. Thank you so much for your support.
Below is my updated query, and with regards to the variable used to store the count result, I initially used the $_SESSION variable, and now I am utilizing the $GLOBALS variable.
Code: Select all
$GLOBALS["pendingcount"] = sqlvalue('SELECT count(1) FROM incident_report JOIN site_db site_db1 ON incident_report.salam_node = site_db1.id WHERE incident_report.status = "Pending" ' . $queryWhere);