/**
* 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;
}
