/**
* API Method queries for Search records and render as JSON
*/
public function Query()
{
try {
$criteria = new SearchCriteria();
// TODO: this will limit results based on all properties included in the filter list
$filter = RequestUtil::Get('filter');
if ($filter) {
$criteria->AddFilter(new CriteriaFilter('Idsearch,User,Name,Info,Type,Datecreate', '%' . $filter . '%'));
}
// TODO: this is generic query filtering based only on criteria properties
foreach (array_keys($_REQUEST) as $prop) {
$prop_normal = ucfirst($prop);
$prop_equals = $prop_normal . '_Equals';
if (property_exists($criteria, $prop_normal)) {
$criteria->{$prop_normal} = RequestUtil::Get($prop);
} elseif (property_exists($criteria, $prop_equals)) {
// this is a convenience so that the _Equals suffix is not needed
$criteria->{$prop_equals} = RequestUtil::Get($prop);
}
}
$output = new stdClass();
// if a sort order was specified then specify in the criteria
$output->orderBy = RequestUtil::Get('orderBy');
$output->orderDesc = RequestUtil::Get('orderDesc') != '';
if ($output->orderBy) {
$criteria->SetOrder($output->orderBy, $output->orderDesc);
}
$page = RequestUtil::Get('page');
if ($page != '') {
// if page is specified, use this instead (at the expense of one extra count query)
$pagesize = $this->GetDefaultPageSize();
$searches = $this->Phreezer->Query('Search', $criteria)->GetDataPage($page, $pagesize);
$output->rows = $searches->ToObjectArray(true, $this->SimpleObjectParams());
$output->totalResults = $searches->TotalResults;
$output->totalPages = $searches->TotalPages;
$output->pageSize = $searches->PageSize;
$output->currentPage = $searches->CurrentPage;
} else {
// return all results
$searches = $this->Phreezer->Query('Search', $criteria);
$output->rows = $searches->ToObjectArray(true, $this->SimpleObjectParams());
$output->totalResults = count($output->rows);
$output->totalPages = 1;
$output->pageSize = $output->totalResults;
$output->currentPage = 1;
}
$this->RenderJSON($output, $this->JSONPCallback());
} catch (Exception $ex) {
$this->RenderExceptionJSON($ex);
}
}
/**
* Make search over registrar notifications collection
*
* @param SearchCriteria $criteria Prepared search filters
* @param int $limit results limit
* @param int $offset start rowset from
* @param array $sort rowset sort rule array('field' => 'asc|desc')
*
* @return str json response
*/
public function search(SearchCriteria $criteria, $limit = 100, $offset = 0, array $sort = array(), $cltrid = false)
{
$get = $criteria->getCriteria();
$get['do'] = 'search';
$get['limit'] = $limit;
$get['offset'] = $offset;
foreach ($sort as $field => $direction) {
$get['sort_field'] = $field;
$get['sort_direction'] = $direction;
// sorry. just one field
break;
}
$json = APIRequest::GET('/notifications/', $cltrid ?: APIRequest::defaultClientTransactionID(), STRegistry::Session()->getAuthToken(), $get);
return $json;
}
public static function getLabs(SearchCriteria $condicao = null)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "SELECT * FROM laboratorios\r\n WHERE deleted = 0";
if ($condicao !== null) {
if ($condicao->getValueCriteria() !== null) {
$sql .= " AND chave_laboratorio = {$condicao->getValueCriteria()}";
}
}
$sql .= " ORDER BY numero_laboratorio";
$sth = $conn->prepare($sql);
$sth->execute();
$results = $sth->fetchAll(PDO::FETCH_CLASS, 'Lab');
return $results;
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
public static function getUsuarios(SearchCriteria $condicao = null)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "SELECT U.*,T.nome_tipo FROM usuarios U ";
$sql .= "INNER JOIN tipos_usuarios T ON\r\n (T.id_tipo = U.tipo_usuario_id)";
if ($condicao !== null) {
if ($condicao->getValueCriteria() !== null) {
$sql .= " WHERE U.nome_usuario like '{$condicao->getValueCriteria()}%'";
}
}
$sql .= " ORDER BY U.id_usuario DESC";
$sth = $conn->prepare($sql);
$sth->execute();
$result = $sth->fetchALL(PDO::FETCH_OBJ);
return $result;
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
/**
* Performs search on all stories
*
* @access private
* @return object plugin object
*
*/
function _searchStories()
{
global $_TABLES, $_DB_dbms, $LANG09;
// Make sure the query is SQL safe
$query = trim(addslashes($this->_query));
$sql = 'SELECT s.sid AS id, s.title AS title, s.introtext AS description, ';
$sql .= 'UNIX_TIMESTAMP(s.date) AS date, s.uid AS uid, s.hits AS hits, ';
$sql .= 'CONCAT(\'/article.php?story=\',s.sid) AS url ';
$sql .= 'FROM ' . $_TABLES['stories'] . ' AS s, ' . $_TABLES['users'] . ' AS u ';
$sql .= 'WHERE (draft_flag = 0) AND (date <= NOW()) AND (u.uid = s.uid) ';
$sql .= COM_getPermSQL('AND') . COM_getTopicSQL('AND') . COM_getLangSQL('sid', 'AND') . ' ';
if (!empty($this->_topic)) {
$sql .= 'AND (s.tid = \'' . $this->_topic . '\') ';
}
if (!empty($this->_author)) {
$sql .= 'AND (s.uid = \'' . $this->_author . '\') ';
}
$search_s = new SearchCriteria('stories', $LANG09[65]);
$columns = array('title' => 'title', 'introtext', 'bodytext');
$sql .= $search_s->getDateRangeSQL('AND', 'date', $this->_dateStart, $this->_dateEnd);
list($sql, $ftsql) = $search_s->buildSearchSQL($this->_keyType, $query, $columns, $sql);
$search_s->setSQL($sql);
$search_s->setFTSQL($ftsql);
$search_s->setRank(5);
$search_s->setURLRewrite(true);
// Search Story Comments
$sql = 'SELECT c.cid AS id, c.title AS title, c.comment AS description, ';
$sql .= 'UNIX_TIMESTAMP(c.date) AS date, c.uid AS uid, ';
// MSSQL has a problem when concatenating numeric values
if ($_DB_dbms == 'mssql') {
$sql .= '\'/comment.php?mode=view&cid=\' + CAST(c.cid AS varchar(10)) AS url ';
} else {
$sql .= 'CONCAT(\'/comment.php?mode=view&cid=\',c.cid) AS url ';
}
$sql .= 'FROM ' . $_TABLES['users'] . ' AS u, ' . $_TABLES['comments'] . ' AS c ';
$sql .= 'LEFT JOIN ' . $_TABLES['stories'] . ' AS s ON ((s.sid = c.sid) ';
$sql .= COM_getPermSQL('AND', 0, 2, 's') . COM_getTopicSQL('AND', 0, 's') . COM_getLangSQL('sid', 'AND', 's') . ') ';
$sql .= 'WHERE (u.uid = c.uid) AND (s.draft_flag = 0) AND (s.commentcode >= 0) AND (s.date <= NOW()) ';
if (!empty($this->_topic)) {
$sql .= 'AND (s.tid = \'' . $this->_topic . '\') ';
}
if (!empty($this->_author)) {
$sql .= 'AND (c.uid = \'' . $this->_author . '\') ';
}
$search_c = new SearchCriteria('comments', array($LANG09[65], $LANG09[66]));
$columns = array('title' => 'c.title', 'comment');
$sql .= $search_c->getDateRangeSQL('AND', 'c.date', $this->_dateStart, $this->_dateEnd);
list($sql, $ftsql) = $search_c->buildSearchSQL($this->_keyType, $query, $columns, $sql);
$search_c->setSQL($sql);
$search_c->setFTSQL($ftsql);
$search_c->setRank(2);
return array($search_s, $search_c);
}
<?php
$criteria = new SearchCriteria();
if (array_key_exists('busca', $_POST)) {
$criteria->setValueCriteria($_POST['CampoBusca']);
}
$usuarios = UsuarioMapper::getUsuarios($criteria);
$sessao->addVar('usuarios', $usuarios);
if (array_key_exists('empCancel', $_GET)) {
$sessao->removeVar('mat');
}
if ($sessao->getVar('msg') != null) {
if ($sessao->getVar('msg') == 1) {
Flash::addFlash('Usuário alterado.');
} elseif ($sessao->getVar('msg') == 2) {
Flash::addFlash('Permissão alterada.');
} elseif ($sessao->getVar('msg') == 3) {
Flash::addFlash('Empréstimo salvo com sucesso.');
} elseif ($sessao->getVar('msg') == 4) {
Flash::addFlash("Usuário excluído");
}
$sessao->removeVar('msg');
}
<?php
if (array_key_exists('val', $_POST)) {
$result = LabMapper::VerificaNumeroLab($_POST['val']);
if ($result[0] >= 1) {
echo "true";
} else {
echo "false";
}
}
if (array_key_exists('key', $_GET)) {
$usuario = Utils::findById($_GET['key'], 'usuarios', 'id_usuario');
$criteria = new SearchCriteria();
$criteria->setValueCriteria('0');
$laboratorios = LabMapper::getLabs($criteria);
}
if (array_key_exists('save', $_POST)) {
$dados = array('nome_usuario' => array('Usuário'), 'dt_inicial_controle' => array('Data', 'tipo' => 'data'));
$validacao = ValidaFormulario($dados);
if ($validacao === true) {
$data = array('professor_id' => $_GET['key'], 'laboratorio_id' => $_POST['laboratorio_id'], 'observacao_controle' => $_POST['observacao_controle'] == '' ? null : $_POST['observacao_controle'], 'dt_inicial_controle' => Utils::conv_data_to_us($_POST['dt_inicial_controle']) . ' ' . $_POST['hora_inicial_controle']);
$ch = new CrlChave();
CrlChaveMapper::map($ch, $data);
CrlChaveMapper::addCrlChave($ch);
header('location:index.php?modulo=chaves&page=visualizar');
}
}
if (array_key_exists('labkey', $_GET)) {
$crlchave = new CrlChave();
$crlchave->setLaboratorioId($_GET['labkey']);
$crlchave->setDtFinalControle(date('Y-m-d H:i:s'));
function compact()
{
parent::compact();
unset($this->county_name);
}
<?php
if (isset($_POST['val'])) {
$itens = SolMapper::getItens($_POST['val']);
echo json_encode($itens);
}
$criteria = new SearchCriteria();
if (array_key_exists('busca', $_POST)) {
$criteria->setValueCriteria($_POST['CampoBusca']);
}
$solicitacoes = SolMapper::getSolicitacoes($criteria);
if ($sessao->getVar('msg') != null) {
if ($sessao->getVar('msg') == '2') {
Flash::addFlash('Solicitação excluida!');
}
if ($sessao->getVar('msg') == '3') {
Flash::addFlash('Solicitação registrada!');
}
$sessao->removeVar('msg');
}
<?php
$estados = Utils::getAll('estados_requisicoes');
$criteria = new SearchCriteria();
if (isset($_POST['busca']) and $_POST['estadoBusca'] != 'todos') {
$criteria->setValueCriteria($_POST['estadoBusca']);
} else {
if ($sessao->getVar('estado') != null and array_key_exists('estado', $_GET)) {
if ($_GET['estado'] != 4) {
$criteria->setValueCriteria($sessao->getVar('estado'));
}
}
}
$requisicoes = RequerirMapper::getRequisicaoByCriteria($criteria);
if (array_key_exists('erro', $_GET)) {
if ($_GET['erro'] == 'concl') {
Flash::addFlash('Você não pode concluir esta manutencão, pois ela não é sua.');
}
}
if ($sessao->getVar('msg') != null) {
if ($sessao->getVar('msg') == '1') {
Flash::addFlash('Requisição registrada!');
}
if ($sessao->getVar('msg') == '2') {
Flash::addFlash('Requisição alterada!');
}
if ($sessao->getVar('msg') == '3') {
Flash::addFlash('Você não pode editar/deletar requisições de outros usuários!');
}
if ($sessao->getVar('msg') == '4') {
Flash::addFlash('Requisição excluída.');
<?php
include_once "includes/SystemConfiguration.class.php";
include "includes/language.php";
global $systemConfiguration;
global $logger;
session_start();
$logger->LogInfo(__FILE__);
$systemConfiguration->assertReferer();
// Get serach criteria
$logger->LogInfo("Getting search criteria ...");
$bookingDetails = new BookingDetails();
$searchCriteria = SearchCriteria::fetchFromParameters($_POST);
$logger->LogInfo("Search criteria:");
$logger->LogInfo("Check-in date: " . $searchCriteria->checkInDate->format("Y-m-d"));
$logger->LogInfo("Check-out date: " . $searchCriteria->checkInDate->format("Y-m-d"));
$logger->LogInfo("Adults: " . $searchCriteria->adultsCount);
$logger->LogInfo("Children: " . $searchCriteria->childrenCount);
$logger->LogInfo("Server date/time: " . date("Y-m-d H:i:s"));
if (!$searchCriteria->isValid()) {
$logger->LogError("Search is not valid!");
$logger->LogError("Errors:");
$logger->LogError($searchCriteria->errors);
$_SESSION['errors'] = $searchCriteria->errors;
header('Location: booking-failure.php');
} else {
if (!$systemConfiguration->isSearchEgineEnabled()) {
$logger->LogError("Search engine is disabled!");
$_SESSION['errors'] = array(0 => BOOKING_SEARCH_DISABLED);
header('Location: booking-failure.php');
} else {
public static function getSolicitacoes(SearchCriteria $condicao = null)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = "SELECT A.id_aquisicao, \r\n U.nome_usuario,\r\n A.dt_aquisicao_inicial,\r\n A.semestre,\r\n D.nome_disciplina,\r\n F.nome,\r\n C.nome_curso FROM aquisicoes A\r\n INNER JOIN usuarios U ON\r\n (A.requisitante_id = U.id_usuario)\r\n INNER JOIN disciplinas D ON\r\n (A.disciplina_id = D.id_disciplina)\r\n INNER JOIN fases F ON\r\n (A.fase_id = F.id_fase)\r\n INNER JOIN cursos C ON\r\n (D.curso_id = C.id_curso)";
if ($condicao !== null) {
if ($condicao->getValueCriteria() !== null) {
$sql .= " WHERE U.nome_usuario like '{$condicao->getValueCriteria()}%'";
}
}
$sql .= "ORDER BY id_aquisicao DESC";
$sth = $conn->prepare($sql);
$sth->execute();
return $sth->fetchALL(PDO::FETCH_OBJ);
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
/**
* Adds a display field
* to the collection $titleFields
*
* @param string or array $field the field name
* @param string $label the field label
*/
public function addTitleField($field, $label)
{
if (gettype($field) == 'array') {
if (count($field) == 0) {
throw new InvalidArgumentException('Invalid field');
}
$criteriaCollection = new SearchableReadonlyCollection();
$searchCriteriaProperty = new ToOneRelationPropertyFilter();
$searchCriteriaProperty->setCriteriaAttribute($field[0]);
$titleField = new AsyncSearchCriteriaTitleField($field[0], $label);
for ($i = 1; $i < count($field); $i++) {
if ($i == count($field) - 1) {
$searchCriteria = new SearchCriteria();
$searchCriteria->setCriteriaAttribute($field[$i]);
$criteriaCollection->add($searchCriteria);
} else {
$searchCriteriaPropertyAux = new ToOneRelationPropertyFilter();
$searchCriteriaPropertyAux->setCriteriaAttribute($field[$i]);
$criteriaCollection->add($searchCriteriaPropertyAux);
}
}
$i = 1;
foreach ($criteriaCollection as $criteria) {
if ($i < $criteriaCollection->count()) {
$collectionAux = $criteriaCollection->sliceFrom($i);
$criteria->setSearchCriteria($collectionAux->first());
$i++;
}
}
$searchCriteriaProperty->setSearchCriteria($criteriaCollection->first());
$criteriaCollection->reverse();
$searchCriteria = $criteriaCollection->first();
} else {
$searchCriteria = new SearchCriteria();
$searchCriteria->setCriteriaAttribute($field);
$titleField = new AsyncSearchCriteriaTitleField($field, $label);
}
$filterType = new OrStringFilterType();
$dataType = new SearchDataTypeString();
$filterType->setSearchDataType($dataType);
$searchCriteria->setSearchFilterType($filterType);
if (gettype($field) == 'array') {
$titleField->setSearchCriteria($searchCriteriaProperty);
} else {
$titleField->setSearchCriteria($searchCriteria);
}
$this->titleFields[] = $titleField;
}
static function getRequisicaoByCriteria(SearchCriteria $criteria)
{
TTransaction::open('my_config');
if ($conn = TTransaction::get()) {
$sql = 'SELECT R.*,U.nome_usuario,E.nome_estado_requisicao FROM req_manutencao R
INNER JOIN usuarios U on
(R.requisitante_id = U.id_usuario)
INNER JOIN estados_requisicoes E on
(R.estado_id = E.id_estado_requisicao)
WHERE deleted = 0
and R.estado_id <> 4
and R.estado_id <> 5';
if ($criteria !== null) {
if ($criteria->getValueCriteria() !== null) {
$sql .= " and E.id_estado_requisicao = {$criteria->getValueCriteria()}";
}
}
$sql .= ' ORDER BY R.id_requisicao ASC';
$sth = $conn->prepare($sql);
$sth->execute();
$result = $sth->fetchALL(PDO::FETCH_OBJ);
return $result;
TTransaction::close();
} else {
echo 'Sem conexão com banco!';
}
}
/**
* Performs search on all stories
*
* @return object plugin object
*
*/
private function _searchStories()
{
global $_TABLES, $_DB_dbms, $LANG09;
// Make sure the query is SQL safe
$query = trim(DB_escapeString($this->_query));
$sql = 'SELECT s.sid AS id, s.title AS title, s.introtext AS description, ';
$sql .= 'UNIX_TIMESTAMP(s.date) AS date, s.uid AS uid, s.hits AS hits, ';
$sql .= 'CONCAT(\'/article.php?story=\',s.sid) AS url ';
$sql .= 'FROM ' . $_TABLES['stories'] . ' AS s, ' . $_TABLES['users'] . ' AS u, ' . $_TABLES['topic_assignments'] . ' AS ta ';
$sql .= 'WHERE (draft_flag = 0) AND (date <= NOW()) AND (u.uid = s.uid) ';
$sql .= 'AND ta.type = \'article\' AND ta.id = sid ';
$sql .= COM_getPermSQL('AND') . COM_getTopicSQL('AND', 0, 'ta') . COM_getLangSQL('sid', 'AND') . ' ';
if (!empty($this->_topic)) {
// Retrieve list of inherited topics
if ($this->_topic == TOPIC_ALL_OPTION) {
// Stories do not have an all option so just return all stories that meet the requirements and permissions
//$sql .= "AND (ta.inherit = 1 OR (ta.inherit = 0 AND ta.tid = '".$this->_topic."')) ";
} else {
$tid_list = TOPIC_getChildList($this->_topic);
$sql .= "AND (ta.tid IN({$tid_list}) AND (ta.inherit = 1 OR (ta.inherit = 0 AND ta.tid = '" . $this->_topic . "'))) ";
}
}
if (!empty($this->_author)) {
$sql .= 'AND (s.uid = \'' . $this->_author . '\') ';
}
$search_s = new SearchCriteria('stories', $LANG09[65]);
$columns = array('title' => 'title', 'introtext', 'bodytext');
$sql .= $search_s->getDateRangeSQL('AND', 'date', $this->_dateStart, $this->_dateEnd);
list($sql, $ftsql) = $search_s->buildSearchSQL($this->_keyType, $query, $columns, $sql);
$sql .= " GROUP BY s.sid";
$search_s->setSQL($sql);
$search_s->setFTSQL($ftsql);
$search_s->setRank(5);
$search_s->setURLRewrite(true);
// Search Story Comments
$sql = 'SELECT c.cid AS id, c.title AS title, c.comment AS description, ';
$sql .= 'UNIX_TIMESTAMP(c.date) AS date, c.uid AS uid, \'0\' AS hits, ';
// MSSQL has a problem when concatenating numeric values
if ($_DB_dbms == 'mssql') {
$sql .= '\'/comment.php?mode=view&cid=\' + CAST(c.cid AS varchar(10)) AS url ';
} else {
$sql .= 'CONCAT(\'/comment.php?mode=view&cid=\',c.cid) AS url ';
}
$sql .= 'FROM ' . $_TABLES['users'] . ' AS u, ' . $_TABLES['topic_assignments'] . ' AS ta, ' . $_TABLES['comments'] . ' AS c ';
$sql .= 'LEFT JOIN ' . $_TABLES['stories'] . ' AS s ON ((s.sid = c.sid) ';
$sql .= COM_getPermSQL('AND', 0, 2, 's') . COM_getLangSQL('sid', 'AND', 's') . ') ';
$sql .= 'WHERE (u.uid = c.uid) AND (s.draft_flag = 0) AND (s.commentcode >= 0) AND (s.date <= NOW()) ';
$sql .= 'AND ta.type = \'article\' AND ta.id = s.sid ' . COM_getTopicSQL('AND', 0, 'ta');
if (!empty($this->_topic)) {
if ($this->_topic == TOPIC_ALL_OPTION) {
// Stories do not have an all option so just return all story comments that meet the requirements and permissions
//$sql .= "AND (ta.inherit = 1 OR (ta.inherit = 0 AND ta.tid = '".$this->_topic."')) ";
} else {
$sql .= "AND (ta.tid IN({$tid_list}) AND (ta.inherit = 1 OR (ta.inherit = 0 AND ta.tid = '" . $this->_topic . "'))) ";
}
}
if (!empty($this->_author)) {
$sql .= 'AND (c.uid = \'' . $this->_author . '\') ';
}
$search_c = new SearchCriteria('comments', array($LANG09[65], $LANG09[66]));
$columns = array('title' => 'c.title', 'comment');
$sql .= $search_c->getDateRangeSQL('AND', 'c.date', $this->_dateStart, $this->_dateEnd);
list($sql, $ftsql) = $search_c->buildSearchSQL($this->_keyType, $query, $columns, $sql);
$sql .= " GROUP BY id";
$search_c->setSQL($sql);
$search_c->setFTSQL($ftsql);
$search_c->setRank(2);
return array($search_s, $search_c);
}
function __construct($criteria, $response_type)
{
parent::__construct($criteria, $response_type);
}
/**
* Performs search on all comments
*
* @author Tony Bibbs <tony AT geeklog DOT net>
* Sami Barakat <s.m.barakat AT gmail DOT com>
* @access private
* @return object plugin object
*
*/
function _searchComments()
{
global $_CONF, $_TABLES, $_DB_dbms, $LANG09;
// Make sure the query is SQL safe
$query = trim(DB_escapeString(htmlspecialchars($this->_query)));
$sql = "SELECT s.sid AS id, c.title AS title, c.comment AS description, UNIX_TIMESTAMP(c.date) AS date, c.uid AS uid, '0' AS hits, ";
if ($_CONF['url_rewrite']) {
$sql .= "CONCAT('/article.php/',s.sid,'#comments') AS url ";
} else {
$sql .= "CONCAT('/article.php?story=',s.sid,'#comments') AS url ";
}
$sql .= "FROM {$_TABLES['users']} AS u, {$_TABLES['comments']} AS c ";
$sql .= "LEFT JOIN {$_TABLES['stories']} AS s ON ((s.sid = c.sid) ";
$sql .= COM_getPermSQL('AND', 0, 2, 's') . COM_getTopicSQL('AND', 0, 's') . COM_getLangSQL('sid', 'AND', 's') . ") ";
$sql .= "WHERE (u.uid = c.uid) AND (s.draft_flag = 0) AND (s.commentcode >= 0) AND (s.date <= NOW()) ";
if (!empty($this->_topic)) {
$sql .= "AND (s.tid = '" . DB_escapeString($this->_topic) . "') ";
}
if (!empty($this->_author)) {
$sql .= "AND (c.uid = " . (int) $this->_author . ") ";
}
$search = new SearchCriteria('comments', $LANG09[65] . ' > ' . $LANG09[66]);
$columns = array('comment', 'c.title');
$sql .= $search->getDateRangeSQL('AND', 'UNIX_TIMESTAMP(c.date)', $this->_dateStart, $this->_dateEnd);
list($sql, $ftsql) = $search->buildSearchSQL($this->_keyType, $query, $columns, $sql);
$search->setSQL($sql);
$search->setFTSQL($ftsql);
$search->setRank(2);
return $search;
}
/**
* Given the filename, returns an array of commands to execute for xml file creation,
* This function modifies the sql to handle derived columns dynamically.
*
* @param $filename
* @return array
*/
private function getXMLJobCommands($filename)
{
global $conf;
$query = $this->jobDetails['data_command'];
$request_criteria = $this->jobDetails['request_criteria'];
$search_criteria = new SearchCriteria($request_criteria, $this->responseFormat);
$config = ConfigUtil::getConfiguration($request_criteria['global']['type_of_data'], $search_criteria->getConfigKey());
//map tags and build sql
$rootElement = $config->dataset->displayConfiguration->xml->rootElement;
$rowParentElement = $config->dataset->displayConfiguration->xml->rowParentElement;
$elementsColumn = $config->dataset->displayConfiguration->xml->elementsColumn;
$elementsColumn = (array) $elementsColumn;
$columnMappings = array_flip($elementsColumn);
$end = strpos($query, 'FROM');
$select_part = substr($query, 0, $end);
$select_part = str_replace("SELECT", "", $select_part);
$sql_parts = explode(",", $select_part);
$new_select_part = "'<" . $rowParentElement . ">'";
foreach ($sql_parts as $sql_part) {
$sql_part = trim($sql_part);
$is_derived_column = strpos(strtoupper($sql_part), "CASE WHEN") !== FALSE;
//get column and alias
$alias = "";
if ($is_derived_column) {
$pos = strpos($sql_part, " AS");
$column = trim(str_replace("AS", "", substr($sql_part, $pos)));
} else {
$pos = strpos($sql_part, " AS");
$pos = $pos !== FALSE ? $pos : strlen($sql_part);
$column = substr($sql_part, 0, $pos);
}
if (strpos($sql_part, ".") !== false) {
$alias_pos = strpos($sql_part, ".");
$alias = substr($sql_part, $alias_pos - 2, 3);
$column = str_replace($alias, "", $column);
}
//Handle derived columns
$tag = $columnMappings[$column] == "" ? $column : $columnMappings[$column];
//column open tag
$new_select_part .= "\n||'<" . $tag . ">' || ";
if ($is_derived_column) {
$sql_part = substr_replace($sql_part, "", $pos);
$new_select_part .= str_replace($alias . $column, "REPLACE(REPLACE(REPLACE(COALESCE(CAST(" . $alias . $column . " AS VARCHAR),''),'&','&'),'>','>'),'<','<')", $sql_part);
} else {
$new_select_part .= "REPLACE(REPLACE(REPLACE(COALESCE(CAST(" . $alias . $column . " AS VARCHAR),''),'&','&'),'>','>'),'<','<')";
}
//column close tag
$new_select_part .= " || '</" . $tag . ">'";
}
$new_select_part .= "||'</" . $rowParentElement . ">'";
$new_select_part = "SELECT " . ltrim($new_select_part, "\n||") . "\n";
$query = substr_replace($query, $new_select_part, 0, $end);
//open/close tags
$open_tags = "<?xml version=\"1.0\"?><response><status><result>success</result></status>";
$open_tags .= "<result_records><record_count>" . $this->getRecordCount() . "</record_count><" . $rootElement . ">";
$close_tags = "</" . $rootElement . "></result_records></response>";
$file = $this->getFullPathToFile($filename, $this->tmpFileOutputDir);
$commands = array();
//sql command
$command = $conf['check_book']['data_feeds']['command'] . " -c \"\\\\COPY (" . $query . ") TO '" . $file . "' \" ";
$commands[$filename][] = $command;
//prepend open tags command
$command = "sed -i '1i " . $open_tags . "' " . $file;
$commands[$filename][] = $command;
//append close tags command
$command = "sed -i '\$" . "a" . $close_tags . "' " . $file;
$commands[$filename][] = $command;
//xmllint command to format the xml
$formatted_filename = $this->tmpFileOutputDir . '/formatted_' . $filename . '.xml';
$command = "xmllint --format {$file} --output {$formatted_filename}";
$commands[$filename][] = $command;
//move the formatted file back
$command = "mv {$formatted_filename} {$file}";
$commands[$filename][] = $command;
return $commands;
}
