/**
* Given the query, creates a command to connect to the db and generate the output file, returns the filename
* @param $query
* @return string
*/
function getJobCommand($query)
{
global $conf;
//map csv headers
$columnMappings = $this->requestDataSet->displayConfiguration->csv->elementsColumn;
$columnMappings = (array) $columnMappings;
//Handle referenced columns
foreach ($columnMappings as $key => $value) {
if (strpos($value, "@") !== false) {
$column_parts = explode("@", $value);
$columnMappings[$key] = $column_parts[0];
}
}
$columnMappings = array_flip($columnMappings);
$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 = "SELECT ";
foreach ($sql_parts as $sql_part) {
$sql_part = trim($sql_part);
$column = $sql_part;
$alias = "";
//Remove "AS"
if (strpos($sql_part, "AS") !== false) {
$pos = strpos($column, " AS");
$sql_part = substr($sql_part, 0, $pos);
}
//get only column
if (strpos($sql_part, ".") !== false) {
$alias = substr($sql_part, 0, 3);
$column = substr($sql_part, 3);
}
//Handle derived columns
switch ($column) {
case "prime_vendor_name":
$new_column = "CASE WHEN " . $alias . $column . " IS NULL THEN 'N/A' ELSE " . $alias . $column . " END";
$new_select_part .= $new_column . ' AS \\"' . $columnMappings[$column] . '\\",' . "\n";
break;
case "minority_type_name":
$new_column = "CASE \n";
$new_column .= "WHEN " . $alias . $column . " = 2 THEN 'Black American' \n";
$new_column .= "WHEN " . $alias . $column . " = 3 THEN 'Hispanic American' \n";
$new_column .= "WHEN " . $alias . $column . " = 7 THEN 'Non-M/WBE' \n";
$new_column .= "WHEN " . $alias . $column . " = 9 THEN 'Women' \n";
$new_column .= "WHEN " . $alias . $column . " = 11 THEN 'Individuals and Others' \n";
$new_column .= "ELSE 'Asian American' END";
$new_select_part .= $new_column . ' AS \\"' . $columnMappings[$column] . '\\",' . "\n";
break;
case "vendor_type":
$new_column = "CASE WHEN " . $alias . $column . " ~* 's' THEN 'Yes' ELSE 'No' END";
$new_select_part .= $new_column . ' AS \\"' . $columnMappings[$column] . '\\",' . "\n";
break;
default:
$new_select_part .= $alias . $column . ' AS \\"' . $columnMappings[$column] . '\\",' . "\n";
break;
}
}
$new_select_part = rtrim($new_select_part, ",\n");
$query = substr_replace($query, $new_select_part, 0, $end);
try {
$fileDir = _checkbook_project_prepare_data_feeds_file_output_dir();
$filename = _checkbook_project_generate_uuid() . '.csv';
$tmpDir = isset($conf['check_book']['tmpdir']) && is_dir($conf['check_book']['tmpdir']) ? rtrim($conf['check_book']['tmpdir'], '/') : '/tmp';
$command = $conf['check_book']['data_feeds']['command'];
if (!is_writable($tmpDir)) {
LogHelper::log_error("{$tmpDir} is not writable. Please make sure this is writable to generate export file.");
return $filename;
}
$tempOutputFile = $tmpDir . '/' . $filename;
$outputFile = DRUPAL_ROOT . '/' . $fileDir . '/' . $filename;
$cmd = $command . " -c \"\\\\COPY (" . $query . ") TO '" . $tempOutputFile . "' WITH DELIMITER ',' CSV HEADER \" ";
shell_exec($cmd);
$move_cmd = "mv {$tempOutputFile} {$outputFile}";
shell_exec($move_cmd);
} catch (Exception $e) {
$value = TextLogMessageTrimmer::$LOGGED_TEXT_LENGTH__MAXIMUM;
TextLogMessageTrimmer::$LOGGED_TEXT_LENGTH__MAXIMUM = NULL;
LogHelper::log_error($e);
$msg = "Command used to generate the file: " . $command;
$msg .= "Error generating DB command: " . $e->getMessage();
LogHelper::log_error($msg);
TextLogMessageTrimmer::$LOGGED_TEXT_LENGTH__MAXIMUM = $value;
}
return $filename;
}
/**
* Given the query, creates a command to connect to the db and generate the output file, returns the filename
* @param $query
* @return string
*/
function getJobCommand($query)
{
global $conf;
//map tags and build sql
$rootElement = $this->requestDataSet->displayConfiguration->xml->rootElement;
$rowParentElement = $this->requestDataSet->displayConfiguration->xml->rowParentElement;
$columnMappings = $this->requestDataSet->displayConfiguration->xml->elementsColumn;
$columnMappings = (array) $columnMappings;
//Handle referenced columns
foreach ($columnMappings as $key => $value) {
if (strpos($value, "@") !== false) {
$column_parts = explode("@", $value);
$columnMappings[$key] = $column_parts[0];
}
}
$columnMappings = array_flip($columnMappings);
$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);
$column = $sql_part;
$alias = "";
//Remove "AS"
if (strpos($sql_part, "AS") !== false) {
$pos = strpos($column, " AS");
$sql_part = substr($sql_part, 0, $pos);
}
//get only column
if (strpos($sql_part, ".") !== false) {
$alias = substr($sql_part, 0, 3);
$column = substr($sql_part, 3);
}
//Handle derived columns
$tag = $columnMappings[$column];
$new_select_part .= "\n||'<" . $tag . ">' || ";
switch ($column) {
case "prime_vendor_name":
$new_select_part .= "CASE WHEN " . "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')" . " IS NULL THEN 'N/A' ELSE " . $alias . $column . " END";
break;
case "minority_type_name":
$new_select_part .= "CASE \n";
$new_select_part .= "WHEN " . "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')" . " = 2 THEN 'Black American' \n";
$new_select_part .= "WHEN " . "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')" . " = 3 THEN 'Hispanic American' \n";
$new_select_part .= "WHEN " . "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')" . " = 7 THEN 'Non-M/WBE' \n";
$new_select_part .= "WHEN " . "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')" . " = 9 THEN 'Women' \n";
$new_select_part .= "WHEN " . "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')" . " = 11 THEN 'Individuals and Others' \n";
$new_select_part .= "ELSE 'Asian American' END";
break;
case "vendor_type":
$new_select_part .= "CASE WHEN " . "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')" . " ~* 's' THEN 'Yes' ELSE 'No' END";
break;
default:
$new_select_part .= "COALESCE(CAST(" . $alias . $column . " AS VARCHAR),'')";
break;
}
$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>";
$open_tags .= "<" . $rootElement . ">";
$close_tags = "</" . $rootElement . "></result_records></response>";
//replace '<' and '>' to allow escaping of db columns with these tags
$query = str_replace("<", "|LT|", $query);
$query = str_replace(">", "|GT|", $query);
$open_tags = str_replace("<", "|LT|", $open_tags);
$open_tags = str_replace(">", "|GT|", $open_tags);
$close_tags = str_replace("<", "|LT|", $close_tags);
$close_tags = str_replace(">", "|GT|", $close_tags);
try {
$fileDir = _checkbook_project_prepare_data_feeds_file_output_dir();
$filename = _checkbook_project_generate_uuid() . '.xml';
$tmpDir = isset($conf['check_book']['tmpdir']) && is_dir($conf['check_book']['tmpdir']) ? rtrim($conf['check_book']['tmpdir'], '/') : '/tmp';
$command = $conf['check_book']['data_feeds']['command'];
if (!is_writable($tmpDir)) {
LogHelper::log_error("{$tmpDir} is not writable. Please make sure this is writable to generate export file.");
return $filename;
}
$tempOutputFile = $tmpDir . '/' . $filename;
$outputFile = DRUPAL_ROOT . '/' . $fileDir . '/' . $filename;
$commands = array();
//sql command
$command = $command . " -c \"\\\\COPY (" . $query . ") TO '" . $tempOutputFile . "' \" ";
$commands[] = $command;
//prepend open tags command
$command = "sed -i '1i " . $open_tags . "' " . $tempOutputFile;
$commands[] = $command;
//append close tags command
$command = "sed -i '\$" . "a" . $close_tags . "' " . $tempOutputFile;
$commands[] = $command;
//escape '&' for xml compatibility
$command = "sed -i 's/&/&/g' " . $tempOutputFile;
$commands[] = $command;
//escape '<' for xml compatibility
$command = "sed -i 's/</\\</g' " . $tempOutputFile;
$commands[] = $command;
//escape '>' for xml compatibility
$command = "sed -i 's/>/\\>/g' " . $tempOutputFile;
$commands[] = $command;
//put back the '<' tags
$command = "sed -i 's/|LT|/</g' " . $tempOutputFile;
$commands[] = $command;
//put back the '>' tags
$command = "sed -i 's/|GT|/>/g' " . $tempOutputFile;
$commands[] = $command;
$command = "mv {$tempOutputFile} {$outputFile}";
$commands[] = $command;
$this->processCommands($commands);
} catch (Exception $e) {
$value = TextLogMessageTrimmer::$LOGGED_TEXT_LENGTH__MAXIMUM;
TextLogMessageTrimmer::$LOGGED_TEXT_LENGTH__MAXIMUM = NULL;
LogHelper::log_error($e);
TextLogMessageTrimmer::$LOGGED_TEXT_LENGTH__MAXIMUM = $value;
}
return $filename;
}