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