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') {