function createJSONSelectList($strList, $startRow, $rowCount, $filter, $sort, $id = null)
{
global $dblink;
require "list_switch.php";
if (!sesAccessLevel($levelsAllowed) && !sesAdminAccess()) {
?>
<div class="form_container ui-widget-content">
<?php
echo $GLOBALS['locNoAccess'] . "\n";
?>
</div>
<?php
return;
}
if ($sort) {
if (!preg_match('/^[\\w_,]+$/', $sort)) {
header('HTTP/1.1 400 Bad Request');
die('Invalid sort type');
}
$sortValid = 0;
$sortFields = explode(',', $sort);
foreach ($sortFields as $sortField) {
foreach ($astrShowFields as $field) {
if ($sortField === $field['name']) {
++$sortValid;
break;
}
}
}
if ($sortValid != count($sortFields)) {
header('HTTP/1.1 400 Bad Request');
die('Invalid sort type');
}
} else {
foreach ($astrShowFields as $field) {
if ($field['name'] == 'order_no') {
$sort = 'order_no';
}
}
}
$arrQueryParams = array();
$strWhereClause = '';
if (!getSetting('show_deleted_records') && empty($id)) {
$strWhereClause = " WHERE {$strDeletedField}=0";
}
if ($strGroupBy) {
$strGroupBy = " GROUP BY {$strGroupBy}";
}
// Add Filter
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') {
$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 = "{$strPrimaryKey}, {$strDeletedField}";
foreach ($astrShowFields as $field) {
$strSelectClause .= ', ' . (isset($field['sql']) ? $field['sql'] : $field['name']);
}
$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 = array();
$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] = $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();
for ($i = 0; $i < count($astrListValues); $i++) {
$row = $astrListValues[$i];
$resultValues = array();
foreach ($astrShowFields as $field) {
if (!isset($field['select']) || !$field['select']) {
continue;
}
$name = $field['name'];
if (isset($field['translate']) && $field['translate'] && isset($GLOBALS["loc{$row[$name]}"])) {
$value = $GLOBALS["loc{$row[$name]}"];
} else {
$value = htmlspecialchars($row[$name]);
}
$resultValues[$name] = $value;
}
$records[] = array('id' => $astrPrimaryKeys[$i], 'text' => implode(' ', $resultValues));
}
$results = array('moreAvailable' => $moreAvailable, 'records' => $records, 'filter' => $filter);
return json_encode($results);
}
$sql = "SELECT\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking.*,\n ttc_title,\n iss_summary,\n usr_full_name,\n sta_color\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking_category,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue,\n " . ETEL_USER_TABLE . ",\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "status\n WHERE\n iss_sta_id = sta_id AND\n ttr_iss_id = iss_id AND\n ttr_ttc_id = ttc_id AND\n ttr_usr_id = usr_id AND\n iss_prj_id = {$prj_id} AND\n";
$sql .= createWhereClause('ttr_created_date', 'ttr_usr_id');
$res = $GLOBALS["db_api"]->dbh->getAll($sql, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
print_r($res);
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
} else {
$data['time'] = processResult($res, 'ttr_created_date', 'ttr_iss_id');
for ($i = 0; $i < count($data['time']); $i++) {
$data['time'][$i]['time_spent'] = Misc::getFormattedTime($data['time'][$i]['ttr_time_spent'], true);
}
}
}
if (empty($_REQUEST['developer']) && in_array('reminder', $_REQUEST['activity_types'])) {
$sql = "SELECT\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_history.*,\n iss_summary,\n sta_color,\n rma_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_history,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_action,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "status\n WHERE\n iss_sta_id = sta_id AND\n rmh_iss_id = iss_id AND\n rmh_rma_id = rma_id AND\n iss_prj_id = {$prj_id} AND\n";
$sql .= createWhereClause('rmh_created_date');
$res = $GLOBALS["db_api"]->dbh->getAll($sql, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
print_r($res);
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
} else {
$data['reminder'] = processResult($res, 'rmh_created_date', 'rmh_iss_id');
}
}
$tpl->assign(array("unit" => $_REQUEST['unit'], "amount" => $_REQUEST['amount'], "developer" => $_REQUEST['developer'], "start_date" => @$start_date, "end_date" => @$end_date, "data" => $data));
}
function createWhereClause($date_field, $user_field = false)
{
global $start_date, $end_date;
$sql = '';
if ($_REQUEST['report_type'] == 'recent') {
