Skip to content

Instantly share code, notes, and snippets.

@yllus
Created April 17, 2023 15:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yllus/3fabc5e2597c7f452290f27330ac4feb to your computer and use it in GitHub Desktop.
Save yllus/3fabc5e2597c7f452290f27330ac4feb to your computer and use it in GitHub Desktop.
Laravel CLI command to import Environics demographics data per postal code into PostgreSQL
<?php
namespace App\Console\Commands;
use Illuminate\Support\Str;
use Illuminate\Console\Command;
class ImportEnvironicsDemographics extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'environics_demographics:import {csv_demographics_definitions} {csv_demographics_data} {year_demographics_data} {dir_output_data}';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Imports Environics demographics data for a given year, keyed to a postal code, and output SQL to create a table and its data';
public $fp_data_write;
public function write_sql_statements_definition($csv_demographics_definitions, $csv_demographics_data, $year_demographics_data)
{
$arr_columns = [];
$str_sql = '';
// Get just the filename of the demographics data.
$filename_csv_demographics_data = Str::of($csv_demographics_data)->basename('.csv')->value();
// Open the definitions CSV file to read column information from.
$fp = $this->open_file_reading($csv_demographics_definitions);
$i = 0;
while (($arr_demographics_definitions = $this->get_next_csv_line($fp)) !== false) {
if ( $i == 0 ) {
// Verify our data is in the proper columns as needed.
if ( $arr_demographics_definitions[1] != 'Variable' || $arr_demographics_definitions[2] != 'Description' || $arr_demographics_definitions[3] != 'Category' || $arr_demographics_definitions[4] != 'Hierarchy Level' || $arr_demographics_definitions[9] != 'Part' ) {
throw new \Exception('ImportEnvironicsDemographics::write_sql_statements_definition(): Failed to verify "Variable" in array position 1, "Description" in array position 2, "Category" in array position 3, "Hierarchy Level" in array position 4, "Part" in array position 9, aborting.');
}
}
// If the definition we're reading in this row is for the file we'll be loading data from, add it
// to our array to generate SQL statements for.
if ( $arr_demographics_definitions[9] == $filename_csv_demographics_data ) {
$arr_columns[] = $arr_demographics_definitions;
}
$i++;
}
// Close the SQL file we've been reading from.
$this->close_file($fp);
// Create a string for the CREATE TABLE SQL statement for our definitions table.
$str_sql .= "CREATE TABLE custom.environics_demographics_".$year_demographics_data."_definitions (code VARCHAR(255), description VARCHAR(MAX), category VARCHAR(255), data_year CHAR(4), PRIMARY KEY (code));\n\n";
// For each column found in our demographics data, write an INSERT SQL statement.
$arr_data_column_data = [];
$arr_hierachy_column_data = [];
foreach ( $arr_columns as $arr_column ) {
if ( $arr_column[4] == '0' ) {
$arr_hierachy_column_data = ['0' => $arr_column[2]];
}
else {
$arr_hierachy_column_data[(string) $arr_column[4]] = $arr_column[2];
}
// Create a Description for this column out of the full hierarchy column definition.
$str_description = $arr_column[2];
if ( $arr_column[4] != '0' ) {
$str_description = '';
foreach ( $arr_hierachy_column_data as $str_hierarchy_level => $str_hierarchy_description ) {
$str_description .= $str_hierarchy_description . ' - ';
}
$str_description = rtrim($str_description, ' - ');
}
//echo "$arr_column[4] COLUMN: $arr_column[1]\n";
//echo "$arr_column[4] CATEGORY: $arr_column[3]\n";
//echo "$arr_column[4] NAME: $arr_column[2]\n";
//echo "$arr_column[4] DESCRIPT: $str_description\n";
// Save the slug-ified column name to a separate table for use in the data definition.
$arr_data_column_data[] = $arr_column[1];
// Prep the other fields for SQL insert.
$environics_code = str_replace("'", "''", $arr_column[1]);
$str_category = str_replace("'", "''", $arr_column[3]);
// Prep the Description for SQL insert.
$str_description = str_replace("'", "''", $str_description);
$str_sql .= "INSERT INTO custom.environics_demographics_".$year_demographics_data."_definitions (code, description, category, data_year) VALUES ('".$environics_code."', '".$str_description."', '".$str_category."', '".$year_demographics_data."');"."\n";
}
// Put a newline in.
$str_sql .= "\n";
// Write to our output file.
$this->write_file($str_sql);
return true;
}
public function write_sql_statements_data($csv_demographics_data, $year_demographics_data)
{
// Create a CREATE TABLE SQL statement for our data table.
$str_sql = "CREATE TABLE custom.environics_demographics_".$year_demographics_data."_data (postal_code VARCHAR(255), code VARCHAR(255), value VARCHAR(255), PRIMARY KEY (postal_code));\n\n";
// Write to our output file.
$this->write_file($str_sql);
// Open the list of data, and iterate through each row...
$arr_column_names = [];
$fp = $this->open_file_reading($csv_demographics_data);
$i = 0;
while (($arr_postalcodes = $this->get_next_csv_line($fp)) !== false) {
if ( $i == 0 ) {
// Verify our data is in the proper columns as needed.
if ( $arr_postalcodes[0] != 'CODE' || $arr_postalcodes[1] != 'GEO' ) {
throw new \Exception('ImportEnvironicsDemographics::write_sql_statements_data(): Failed to verify "CODE" in array position 0, "CODE" in array position 2, aborting.');
}
// Save our column names.
$arr_column_names = $arr_postalcodes;
$i++;
continue;
}
// If the line describes anything but a GEO = FSALDU, skip it.
if ( $arr_postalcodes[1] != 'FSALDU' ) {
$i++;
continue;
}
// Get an array of values matched to their column names.
$str_postal_code = $arr_postalcodes[0];
$arr_columns_with_values = [];
for ( $j = 0; $j < sizeof($arr_postalcodes); $j++ ) {
if ( $arr_column_names[$j] == 'CODE' || $arr_column_names[$j] == 'GEO' ) {
continue;
}
$arr_columns_with_values[$arr_column_names[$j]] = $arr_postalcodes[$j];
}
// Create an INSERT SQL statement for all of the values for this postal code.
$str_sql = '';
$str_sql .= "INSERT INTO custom.environics_demographics_".$year_demographics_data."_data (postal_code, code, value) VALUES ";
foreach ( $arr_columns_with_values as $str_column_name => $str_column_value ) {
$str_sql .= "('".$str_postal_code."', '".$str_column_name."', '".$str_column_value."'),";
}
$str_sql = rtrim($str_sql, ',');
$str_sql .= ";\n";
// Write to our output file.
$this->write_file($str_sql);
$i++;
}
// Close the SQL file we've been reading from.
$this->close_file($fp);
return true;
}
public function open_file_writing($dir_output_data)
{
$dir_output_data = rtrim($dir_output_data, '/');
$this->fp_data_write = fopen($dir_output_data.'/demographics-import.sql', 'wb');
return $this->fp_data_write;
}
public function open_file_reading($csv_file)
{
$fp = fopen($csv_file, 'r');
return $fp;
}
public function get_next_csv_line($fp)
{
$line = fgets($fp);
if ($line === false) {
return false;
}
$line = trim($line);
// Parse the line by a comma into an array.
$arr_line = str_getcsv($line, ",");
return $arr_line;
}
public function write_file($str_to_write)
{
return fwrite($this->fp_data_write, $str_to_write);
}
public function close_file($fp)
{
return fclose($fp);
}
/**
* Execute the console command.
*
* @return int
*/
public function handle()
{
// Take all of our input values.
$csv_demographics_definitions = $this->argument('csv_demographics_definitions');
$csv_demographics_data = $this->argument('csv_demographics_data');
$year_demographics_data = $this->argument('year_demographics_data');
$dir_output_data = $this->argument('dir_output_data');
// Open a SQL file to write to.
$this->open_file_writing($dir_output_data);
// First, read in our demographics data column definitions; it with our demographics data filename and year will
// let us generate a CREATE TABLE SQL statement and the INSERT INTO TABLE SQL statements to put the code definitions
// into the table.
$this->write_sql_statements_definition($csv_demographics_definitions, $csv_demographics_data, $year_demographics_data);
// Next, let's write a CREATE TABLE SQL statement for the data we're going to write, and loop through and write many
// INSERT INTO TABLE SQL statements to put actual values into the table.
$this->write_sql_statements_data($csv_demographics_data, $year_demographics_data);
// Close the SQL file we've been writing to.
$this->close_file($this->fp_data_write);
echo "\nFINISHED: ".$dir_output_data."demographics-import.sql\n";
return Command::SUCCESS;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment