Ejemplo n.º 1
0
 /**
  * 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;
 }
Ejemplo n.º 2
0
 /**
  * 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/&/&amp;/g' " . $tempOutputFile;
         $commands[] = $command;
         //escape '<' for xml compatibility
         $command = "sed -i 's/</\\&lt;/g' " . $tempOutputFile;
         $commands[] = $command;
         //escape '>' for xml compatibility
         $command = "sed -i 's/>/\\&gt;/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;
 }