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);
}
