function createJSONList($strFunc, $strList, $startRow, $rowCount, $sort, $filter, $where, $requestId) { require "list_switch.php"; global $dblink; if (!sesAccessLevel($levelsAllowed) && !sesAdminAccess()) { ?> <div class="form_container ui-widget-content"> <?php echo $GLOBALS['locNoAccess'] . "\n"; ?> </div> <?php return; } if (!$strTable) { return; } $strWhereClause = ''; $joinOp = 'WHERE'; $arrQueryParams = array(); if ($where) { // Validate and build query parameters $boolean = ''; while (extractSearchTerm($where, $field, $operator, $term, $nextBool)) { if (strcasecmp($operator, 'IN') === 0) { $strWhereClause .= "{$boolean}{$field} {$operator} " . mysqli_real_escape_string($dblink, $term); } else { $strWhereClause .= "{$boolean}{$field} {$operator} ?"; $arrQueryParams[] = str_replace("%-", "%", $term); } if (!$nextBool) { break; } $boolean = " {$nextBool}"; } if ($strWhereClause) { $strWhereClause = "WHERE ({$strWhereClause})"; $joinOp = ' AND'; } } if ($filter) { $strWhereClause .= "{$joinOp} (" . createWhereClause($astrSearchFields, $filter, $arrQueryParams) . ')'; $joinOp = ' AND'; } if (!getSetting('show_deleted_records')) { $strWhereClause .= "{$joinOp} {$strDeletedField}=0"; $joinOp = ' AND'; } if ($strGroupBy) { $strGroupBy = " GROUP BY {$strGroupBy}"; } if (!isset($strCountJoin)) { $strCountJoin = $strJoin; } // Total count $fullQuery = "SELECT COUNT(*) AS cnt FROM {$strTable} {$strCountJoin} {$strWhereClause}"; $res = mysqli_param_query($fullQuery, $arrQueryParams); $row = mysqli_fetch_assoc($res); $totalCount = $filteredCount = $row['cnt']; // Add Filter if ($filter) { $strWhereClause .= "{$joinOp} " . createWhereClause($astrSearchFields, $filter, $arrQueryParams); // Filtered count $fullQuery = "SELECT COUNT(*) as cnt FROM {$strTable} {$strCountJoin} {$strWhereClause}"; $res = mysqli_param_query($fullQuery, $arrQueryParams); $row = mysqli_fetch_assoc($res); $filteredCount = $row['cnt']; } // Add sort options $orderBy = array(); foreach ($sort as $sortField) { // Ignore invisible first column $column = key($sortField) - 1; if (isset($astrShowFields[$column])) { $fieldName = $astrShowFields[$column]['name']; $direction = current($sortField) === 'desc' ? 'DESC' : 'ASC'; if (substr($fieldName, 0, 1) == '.') { $fieldName = substr($fieldName, 1); } // Special case for natural ordering of invoice number and reference number if (in_array($fieldName, array('i.invoice_no', 'i.ref_number'))) { $orderBy[] = "LENGTH({$fieldName}) {$direction}"; } $orderBy[] = "{$fieldName} {$direction}"; } } // Build the final select clause $strSelectClause = "{$strPrimaryKey}, {$strDeletedField}"; foreach ($astrShowFields as $field) { $strSelectClause .= ', ' . (isset($field['sql']) ? $field['sql'] : $field['name']); } $fullQuery = "SELECT {$strSelectClause} FROM {$strTable} {$strJoin} {$strWhereClause}{$strGroupBy}"; if ($orderBy) { $fullQuery .= ' ORDER BY ' . implode(', ', $orderBy); } if ($startRow >= 0 && $rowCount >= 0) { $fullQuery .= " LIMIT {$startRow}, {$rowCount}"; } $res = mysqli_param_query($fullQuery, $arrQueryParams); $astrListValues = array(); $i = -1; while ($row = mysqli_fetch_prefixed_assoc($res)) { ++$i; $astrPrimaryKeys[$i] = $row[$strPrimaryKey]; $aboolDeleted[$i] = $row[$strDeletedField]; foreach ($astrShowFields as $field) { $name = $field['name']; if ($field['type'] == 'TEXT' || $field['type'] == 'INT') { $value = $row[$name]; if (isset($field['mappings']) && isset($field['mappings'][$value])) { $value = $field['mappings'][$value]; } $astrListValues[$i][$name] = $value; } elseif ($field['type'] == 'CURRENCY') { $value = $row[$name]; $value = miscRound2Decim($value, isset($field['decimals']) ? $field['decimals'] : 2); $astrListValues[$i][$name] = $value; } elseif ($field['type'] == 'INTDATE') { $astrListValues[$i][$name] = dateConvDBDate2Date($row[$name]); } } } $records = array(); $highlight = getRequest('highlight_overdue', false); for ($i = 0; $i < count($astrListValues); $i++) { $row = $astrListValues[$i]; $strLink = "?func={$strFunc}&list={$strList}&form={$strMainForm}&id=" . $astrPrimaryKeys[$i]; $resultValues = array($strLink); $overdue = ''; foreach ($astrShowFields as $field) { $name = $field['name']; // Special colouring for overdue invoices if ($highlight && $name == 'i.due_date') { $rowDue = strDate2UnixTime($row['i.due_date']); if ($rowDue < mktime(0, 0, 0, date("m"), date("d") - 14, date("Y"))) { $overdue = ' overdue14'; } elseif ($rowDue < mktime(0, 0, 0, date("m"), date("d") - 7, date("Y"))) { $overdue = ' overdue7'; } elseif ($rowDue < mktime(0, 0, 0, date("m"), date("d"), date("Y"))) { $overdue = ' overdue'; } } if (isset($field['translate']) && $field['translate'] && isset($GLOBALS["loc{$row[$name]}"])) { $value = $GLOBALS["loc{$row[$name]}"]; } else { $value = trim($row[$name]) ? htmlspecialchars($row[$name]) : ' '; } $resultValues[] = $value; } $deleted = $aboolDeleted[$i] ? ' deleted' : ''; $class = "{$overdue}{$deleted}"; if ($class) { $resultValues['DT_RowClass'] = $class; } $records[] = $resultValues; } $results = array('sEcho' => $requestId, 'iTotalRecords' => $totalCount, 'iTotalDisplayRecords' => isset($filteredCount) ? $filteredCount : $totalCount, 'aaData' => $records); return json_encode($results); }
function getInvoiceListTotal($where) { global $dblink; $strFunc = 'invoices'; $strList = 'invoice'; require 'list_switch.php'; $strWhereClause = ''; $joinOp = 'WHERE'; $arrQueryParams = []; if ($where) { // Validate and build query parameters $boolean = ''; while (extractSearchTerm($where, $field, $operator, $term, $nextBool)) { if (strcasecmp($operator, 'IN') === 0) { $strWhereClause .= "{$boolean}{$field} {$operator} " . mysqli_real_escape_string($dblink, $term); } else { $strWhereClause .= "{$boolean}{$field} {$operator} ?"; $arrQueryParams[] = str_replace('%-', '%', $term); } if (!$nextBool) { break; } $boolean = " {$nextBool}"; } if ($strWhereClause) { $strWhereClause = "WHERE ({$strWhereClause})"; $joinOp = ' AND'; } } if (!getSetting('show_deleted_records')) { $strWhereClause .= "{$joinOp} {$strDeletedField}=0"; $joinOp = ' AND'; } $sql = "SELECT sum(it.row_total) as total_sum from {$strTable} {$strJoin} {$strWhereClause}"; $sum = 0; $res = mysqli_param_query($sql, $arrQueryParams); if ($row = mysqli_fetch_assoc($res)) { $sum = $row['total_sum']; } $result = ['sum' => $sum, 'sum_str' => sprintf($GLOBALS['locInvoicesTotal'], miscRound2Decim($sum))]; echo json_encode($result); }