$strFunc,
'table' => $strList,
'tableid' => $strTableName
];
if ($strWhereClause) {
$params['where'] = $strWhereClause;
}
if ($highlightOverdue) {
$params['highlight_overdue'] = 1;
}
?>
= 0 && $rowCount >= 0) {
$fullQuery .= " LIMIT $startRow, $rowCount";
}
$res = mysqli_param_query($fullQuery, $queryParams);
$astrPrimaryKeys = [];
$records = [];
$highlight = getRequest('highlight_overdue', false);
while ($row = mysqli_fetch_prefixed_assoc($res)) {
$astrPrimaryKeys[] = $row[$strPrimaryKey];
$deleted = $row[$strDeletedField] ? ' deleted' : '';
$strLink = "?func=$strFunc&list=$strList&form=$strMainForm"
. '&listid=' . urlencode($listId) . '&id=' . $row[$strPrimaryKey];
$resultValues = [$strLink];
$overdue = '';
foreach ($astrShowFields as $field) {
$name = $field['name'];
$value = $row[$name];
if ($field['type'] == 'TEXT' || $field['type'] == 'INT') {
if (isset($field['mappings']) && isset($field['mappings'][$value])) {
$value = Translator::translate($field['mappings'][$value]);
} elseif (!empty($field['pretranslate'])) {
$value = Translator::translate($value);
}
} elseif ($field['type'] == 'CURRENCY') {
$value = miscRound2Decim(
$value,
isset($field['decimals']) ? $field['decimals'] : 2,
'.', ''
);
}
$resultValues[] = $value;
// Special colouring for overdue invoices
if ($highlight && $name == 'i.due_date') {
$rowDue = dbDate2UnixTime($row['i.due_date']);
if ($rowDue < mktime(0, 0, 0, date("m"), date("d") - 14, date("Y"))
) {
$overdue = ' overdue14';
} elseif (true
&& $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';
}
}
}
$class = "$overdue$deleted";
if ($class) {
$resultValues['DT_RowClass'] = $class;
}
$records[] = $resultValues;
}
Memory::set(
"{$listId}_info",
[
'startRow' => $startRow,
'rowCount' => $rowCount,
'recordCount' => isset($filteredCount) ? $filteredCount : $totalCount,
'ids' => $astrPrimaryKeys,
'queryParams' => $params
]
);
$results = [
'draw' => $requestId,
'recordsTotal' => $totalCount,
'recordsFiltered' => isset($filteredCount) ? $filteredCount : $totalCount,
'data' => $records
];
return json_encode($results);
}
function createListQueryParams($strFunc, $strList, $startRow, $rowCount, $sort,
$filter, $where
) {
include "list_switch.php";
global $dblink;
$terms = '';
$joinOp = '';
$arrQueryParams = [];
if ($where) {
// Validate and build query parameters
$boolean = '';
while (extractSearchTerm($where, $field, $operator, $term, $nextBool)) {
if ('tags' === $field) {
$tagTable = 'companies' === $strList ? 'company' : 'contact';
foreach (explode(',', $term) as $i => $current) {
$subQuery = << 0) {
$terms .= ' AND ';
}
$terms .= "$boolean id IN (" . $subQuery . ')';
$arrQueryParams[] = str_replace("%-", "%", $current);
}
} elseif (strcasecmp($operator, 'IN') === 0) {
$terms .= "$boolean$field $operator " .
mysqli_real_escape_string($dblink, $term);
} else {
$terms .= "$boolean$field $operator ?";
$arrQueryParams[] = str_replace("%-", "%", $term);
}
if (!$nextBool) {
break;
}
$boolean = " $nextBool";
}
if ($terms) {
$terms = "($terms)";
$joinOp = ' AND';
}
}
if (!getSetting('show_deleted_records')) {
$terms .= "$joinOp $strDeletedField=0";
$joinOp = ' AND';
}
$filteredParams = $arrQueryParams;
if ($filter) {
$filteredTerms = "$terms $joinOp (" .
createWhereClause($astrSearchFields, $filter, $filteredParams) . ')';
$joinOp = ' AND';
}
if (!isset($strCountJoin)) {
$strCountJoin = $strJoin;
}
// Sort options
$orderBy = [];
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, ['i.invoice_no', 'i.ref_number'])) {
$orderBy[] = "LENGTH($fieldName) $direction";
}
$orderBy[] = "$fieldName $direction";
}
}
$result = [
'table' => $strTable,
'primaryKey' => $strPrimaryKey,
'terms' => $terms,
'params' => $arrQueryParams,
'order' => implode(',', $orderBy),
'group' => $strGroupBy,
'join' => $strJoin,
'countJoin' => isset($strCountJoin) ? $strCountJoin : $strJoin
];
if (isset($filteredTerms)) {
$result['filteredTerms'] = $filteredTerms;
$result['filteredParams'] = $filteredParams;
}
return $result;
}
function createJSONSelectList($strList, $startRow, $rowCount, $filter, $sort,
$id = null
) {
global $dblink;
include "list_switch.php";
if (empty($id) && !sesAccessLevel($levelsAllowed) && !sesAdminAccess()) {
?>
1) {
$strWhereClause .= ($strWhereClause ? ' AND ' : ' WHERE ')
. 'type=?';
$arrQueryParams[] = $filter[1];
}
$filter = $filter[0];
}
if ($filter) {
$strWhereClause .= ($strWhereClause ? ' AND ' : ' WHERE ')
. createWhereClause(
$astrSearchFields, $filter, $arrQueryParams,
!getSetting('dynamic_select_search_in_middle')
);
}
}
// Filter out inactive companies
if (($strList == 'company' || $strList == 'companies') && empty($id)) {
$strWhereClause .= ($strWhereClause ? ' AND ' : ' WHERE ') . 'inactive=0';
}
if ($id) {
$strWhereClause .= ($strWhereClause ? ' AND ' : ' WHERE ') . 'id=' .
mysqli_real_escape_string($dblink, $id);
}
// Build the final select clause
$strSelectClause = !empty($strDeletedField) ? "$strPrimaryKey, $strDeletedField"
: $strPrimaryKey;
foreach ($astrShowFields as $field) {
$strSelectClause .= ', ' .
(isset($field['sql']) ? $field['sql'] : $field['name']);
}
// Sort any exact matches first
if ($astrSearchFields && $filter) {
$fields = [];
foreach ($astrSearchFields as $searchField) {
if (in_array($searchField['type'], ['TEXT', 'INT', 'PRIMARY'])) {
$fields[] = $searchField['name'];
}
}
$fieldList = implode(',', $fields);
$escapedFilter = mysqli_real_escape_string($dblink, $filter);
$exactSort = "IF('$escapedFilter' IN ($fieldList, "
. "CONCAT_WS(' ', $fieldList)), 0, 1)";
if ($sort) {
$sort = "$exactSort, $sort";
} else {
$sort = $exactSort;
}
}
$fullQuery = "SELECT $strSelectClause FROM $strTable $strWhereClause$strGroupBy";
if ($sort) {
$fullQuery .= " ORDER BY $sort";
}
if ($startRow >= 0 && $rowCount >= 0) {
$fullQuery .= " LIMIT $startRow, " . ($rowCount + 1);
}
$res = mysqli_param_query($fullQuery, $arrQueryParams);
$astrListValues = [];
$i = -1;
$moreAvailable = false;
while ($row = mysqli_fetch_prefixed_assoc($res)) {
++$i;
if ($startRow >= 0 && $rowCount >= 0 && $i >= $rowCount) {
$moreAvailable = true;
break;
}
$astrPrimaryKeys[$i] = $row[$strPrimaryKey];
$aboolDeleted[$i] = isset($strDeletedField) ? $row[$strDeletedField] : false;
foreach ($astrShowFields as $field) {
$name = $field['name'];
if ($field['type'] == 'TEXT' || $field['type'] == 'INT'
|| $field['type'] == 'HIDDEN'
) {
$value = $row[$name];
if (isset($field['mappings']) && isset($field['mappings'][$value])) {
$value = Translator::translate($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 = [];
for ($i = 0; $i < count($astrListValues); $i ++) {
$row = $astrListValues[$i];
$resultValues = [];
$descriptions = [];
$desc2 = [];
foreach ($astrShowFields as $field) {
if (!isset($field['select']) || !$field['select']) {
continue;
}
$name = $field['name'];
if ('product' === $strList) {
switch ($name) {
case 'description':
if (!empty($row[$name])) {
$descriptions[] = $row[$name];
}
continue 2;
case 'vendor':
if (!empty($row[$name])) {
$desc2[] = Translator::translate('ProductVendor') . ': ' . $row[$name];
}
continue 2;
case 'vendors_code':
if (!empty($row[$name])) {
$desc2[] = Translator::translate('ProductVendorsCode') . ': '
. $row[$name];
}
continue 2;
case 'unit_price':
if (!empty($row[$name]) && $row[$name] != 0.0) {
$desc2[] = Translator::translate('Price') . ': '
. $row[$name];
}
continue 2;
}
}
if (isset($field['translate']) && $field['translate']) {
$value = Translator::translate($row[$name]);
} else {
$value = $row[$name];
}
$resultValues[$name] = $value;
}
if ($desc2) {
$descriptions[] = implode(', ', $desc2);
}
$records[] = [
'id' => $astrPrimaryKeys[$i],
'descriptions' => $descriptions,
'text' => implode(' ', $resultValues)
];
}
$results = [
'moreAvailable' => $moreAvailable,
'records' => $records,
'filter' => $filter
];
return json_encode($results);
}