Store part of array when array_search is successful

[mongoose00318]

@Barand Sorry for my delay. I've been diligently working on this and also trying to spell out some of the requirements I need to keep in mind. I will have an update for you later today. One question I have real quick about your query here:

##
##  load the csv data into the table
##          
$db->exec("LOAD DATA LOCAL INFILE 'prod_data.csv'
          INTO TABLE production_csv
          FIELDS TERMINATED BY ','
          LINES TERMINATED BY '\n'
          IGNORE 1 LINES
          (@dummy, @dummy, enterprise, part_num, desc, qty, line_item, 
          job_num, work_order, psm, date_change_flag, scheduled_ship_date, 
          on_hold, on_hold_reason, total_hours, worfc)
          ");

I've looked for a good example and haven't found one yet that shows how to specify which values go into what table columns if the columns in the CSV don't match the order of the table columns in MySQL. Do I list the fields after "FIELDS"? Also, I'm trying to learn more about triggers as you suggested instead of having the archive function.

I put my script on the live server, made a .BAT file to call it in the console, and then setup a task in Windows Task Scheduler. When I tell it to "run" the task manually it works fine and as intended. But, when it fires the task automatically as scheduled it doesn't do anything at all. I have to look into this as well... 😑

But, it's getting there.

[Barand]

2 hours ago, mongoose00318 said:

I've looked for a good example and haven't found one yet that shows how to specify which values go into what table columns if the columns in the CSV don't match the order of the table columns in MySQL

Either you haven't looked very hard or you don't rate the above example as "good" (or both)

Of course, there is always the reference manual (https://dev.mysql.com/doc/refman/5.7/en/load-data.html)

The final section of the above LOAD DATA statement defines just that.

(@dummy, @dummy, enterprise, part_num, desc, qty, line_item, 
          job_num, work_order, psm, date_change_flag, scheduled_ship_date, 
          on_hold, on_hold_reason, total_hours, worfc)

The first two columns are not used, hence put into dummy variables. The others define the column in the table that that column in the CSV should go.

Edited May 13, 2020 by Barand

[mongoose00318]

Yes I saw that documentation. Sorry I guess I misunderstand. I thought that line refers to the columns in the CSV file..so if I didn't want the last column wofc, I would do...

(@dummy, @dummy, enterprise, part_num, desc, qty, line_item, 
          job_num, work_order, psm, date_change_flag, scheduled_ship_date, 
          on_hold, on_hold_reason, total_hours, @dummy)

Or if the table column names were different I would just do:

(@dummy, @dummy, diff_name_enterprise, diff_name_part_num, diff_name_desc, qty, line_item, 
          job_num, work_order, psm, date_change_flag, scheduled_ship_date, 
          on_hold, on_hold_reason, total_hours, worfc)

 

Edited May 13, 2020 by mongoose00318

[Barand]

3 minutes ago, mongoose00318 said:

I thought that line refers to the columns in the CSV file

Yes it does - it defines the destination column for each column in the csv.

[mongoose00318]

Just now, Barand said:

Yes it does - it defines the destination column for each column in the csv.

I'm confused..I think I'm not grasping something here.

[Barand]

If you have something like this

Then the columns section of the LOAD DATA statement would be

(ColC, ColD, ColE, ColA, ColG, ColB, ColF)

 

[mongoose00318]

Okay I think I understand. I'm going give it another shot.

[mongoose00318]

@Barand I haven't forgotten about this. I made my script and it has been running every 5 minutes and importing the data successfully. I did manage to get the LOAD DATA working after your suggestions. But, I ran into a few problems and figured I'd circle back to it after I don't have as much pressure to get this live (live meaning internal to the network)...but I wanted to get back to you so here is my code...

<?php
chdir(__DIR__);

//allow more memory and execution time for script
ini_set('memory_limit', '4096M');
ini_set('max_execution_time', '600');

//initialize db connection
$host = '127.0.0.1';
$db   = 'dbname';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new \PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

//set timezone
date_default_timezone_set('America/Chicago');

//include composer autoload
require 'vendor/autoload.php';

//setup use statements for PHPSpeadsheet
use \PhpOffice\PhpSpreadsheet\Reader\Xls;
use \PhpOffice\PhpSpreadsheet\Writer\Csv;


##
## Copy production log file
##
$copied_file_timestamp = date("YmdHis");
$srcfile = '\\\directory\Daily Production\Production Schedule.xls'; //live production log
$destfile = 'production_data/xls/Production Schedule ('. $copied_file_timestamp .').xls'; //copy of production log

//last modifed date of network file
$lastModifiedTimestamp = filemtime($srcfile);
$lastModifiedDatetime = date("Y-m-d H:i:s", $lastModifiedTimestamp);

//TEST DATE - REMOVE!!!
//$lastModifiedDatetime = '2020-05-13 12:00:00'; //test date to make it appear production file is older than last insert time

//get latest time when an import was performed
$statement = $pdo->query("SELECT insert_time FROM production_data ORDER BY insert_time DESC LIMIT 1");
$statement->execute();
$get_last_insert_time = $statement->fetch(PDO::FETCH_NUM);

//network file is older than last insert time; log msg and exit script
if ( $get_last_insert_time[0] > $lastModifiedDatetime ) {
	$message = "Network file is older; exiting script..<br>$srcfile was last modified on $lastModifiedDatetime<br>Last data update was: $get_last_insert_time[0]";
	echo $message;
	exit();
}

//perform copy of network file to local system
if (!copy($srcfile, $destfile)) { 
    $message = "File cannot be copied! \n"; 
} 
else { 
    $message = "File has been copied! \n"; 
}

//store paths and file names
$xls_prod_log 			= 'Production Schedule ('. $copied_file_timestamp .').xls';
$csv_prod_log 			= 'Production Schedule ('. $copied_file_timestamp .').csv';
$results_prod_log 		= 'Production Schedule ('. $copied_file_timestamp .').txt';
$path_xls				= 'production_data/xls/';
$path_csv				= 'production_data/csv/';
$path_results			= 'production_data/results/';

//setup complete file path to newly copied xls file
$xls_file = $path_xls . $xls_prod_log;

//instantiate class and settings for PHPSpreadsheet
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {

    public function readCell($column, $row, $worksheetName = '') {
        // Read columns C:P
        if (in_array($column,range('C','P'))) {
            return true;
        }
        return false;
    }
}

$reader = new Xls();
$reader->setReadDataOnly(true);
$reader->setReadFilter( new MyReadFilter() );
$reader->setLoadSheetsOnly( [ 'B. Current Report' ]);

//load the sheet into memory
$spreadsheet = $reader->load($xls_file);

//write the csv file
$writer = new Csv($spreadsheet);
$writer->save($path_csv . $csv_prod_log);

//unset all PHPSpreadsheet objects
unset($reader);
unset($writer);
unset($spreadsheet);

//open the new CSV file
$file = fopen($path_csv . $csv_prod_log, "r");

//$arr_orders = [];

$count = 1;
$count_succesful = 0;

## Conditional Update
//prep, execute, & fetch SQL results
$query = "SELECT * FROM production_data";
$statement = $pdo->query( $query );
$statement->execute();
$old_orders = $statement->fetchAll(PDO::FETCH_ASSOC);

//record update query results
$update_query_results = [
	'accepted_change' => [],
	'accepted_no_change' => [],
	'rejected' => [],
	'new' => [],
];

//loop through records of csv
while( ( $column = fgetcsv($file, 10000, "," ) ) !== FALSE ) {
	
	//skip first 2 rows and make sure array isn't empty
	if ( $count > 2 & array_filter($column) ) {
		
		//setup update array for easy reference
		$update = [
			'job_number' => trim($column[7]),
			'enterprise' => trim($column[2]),
			'part_number' => trim($column[3]),
			'description' => trim($column[4]),
			'qty' => trim($column[5]),
			'line_item' => trim($column[6]),
			'as400_ship_date' => trim($column[11]),
			'hold_reason' => trim($column[13]),
			'hold_date' => trim($column[12]),
		];

		//format dates
		if ( !empty ( $update['as400_ship_date'] ) && is_numeric ( $update['as400_ship_date'] ) )
			$tmp1 = ($update['as400_ship_date'] - 25569) * 86400;
		if ( !empty ( $update['hold_date'] ) && is_numeric ( $update['hold_date'] ) )
			$tmp2 = ( trim ( $column[12] ) - 25569 ) * 86400;

		//set new values for dates
		$update['as400_ship_date']	= gmdate("Y-m-d", $tmp1);
		$update['hold_date'] 		= ( !empty ( $tmp2 ) ) ? gmdate("Y-m-d", $tmp2) : '0000-00-00';

		if ($column[12] == '')
			$update['hold_date'] = '0000-00-00';

		//check if order exists or not in old data
		$find_match = search_job_and_line_item( $update['job_number'], $update['line_item'], $old_orders );

		//order exists without duplicate line items
		if ( is_array( $find_match ) ) {

			$current_old_order = $old_orders[ $find_match[0] ];				//store current old order array for easy reference
			$str1 = strtotime( $update[ 'as400_ship_date' ] ); 				//convert date to string
			$str2 = strtotime( $current_old_order[ 'as400_ship_date' ] );	//convert date to string
			$current_timestamp = date("Y-m-d H:i:s");

			//count differences
			$no_change = 0;
			foreach ( $current_old_order as $k => $v )
				if ( $k != 'id' && $k != 'insert_time' )
					if (trim($v) !== trim($update[$k]) )
						$no_change++;
			
			//proceed if there are differences
			if ( $no_change > 0 ) {

				//check for differences in fields
				$set_vals['part_number'] 		= ($update['part_number'] !== $current_old_order[ 'part_number' ]) 	? $update['part_number'] 		: $current_old_order['part_number']; 
				$set_vals['description'] 		= ($update['description'] !== $current_old_order[ 'description' ]) 	? $update['description'] 		: $current_old_order['description']; 
				$set_vals['qty'] 				= ($update['qty'] !== $current_old_order[ 'qty' ]) 					? $update['qty'] 				: $current_old_order['qty']; 
				$set_vals['as400_ship_date'] 	= ($str1 !== $str2) 												? $update['as400_ship_date'] 	: $current_old_order['as400_ship_date']; 
				$set_vals['hold_reason'] 		= ($update['hold_reason'] !== $current_old_order[ 'hold_reason' ]) 	? $update['hold_reason']		: $current_old_order['hold_reason']; 
				$set_vals['hold_date']			= ($update['hold_date'] !== $current_old_order[ 'hold_date' ]) 		? $update['hold_date'] 			: $current_old_order['hold_date']; 

				//perform backup of past data
				$query = "
					INSERT INTO production_data_archive 
					(order_id, job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, hold_reason, hold_date, insert_time)
					VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
				";
				$statement = $pdo->prepare($query);
				$statement->execute([ 
					$current_old_order['id'], 
					$current_old_order['job_number'], 
					$current_old_order['enterprise'], 
					$current_old_order['part_number'], 
					$current_old_order['description'], 
					$current_old_order['qty'], 
					$current_old_order['line_item'], 
					$current_old_order['as400_ship_date'], 
					$current_old_order['hold_reason'], 
					$current_old_order['hold_date'],
					$current_timestamp
				]);

				//update old record
				$query = "
					UPDATE production_data
					SET part_number = :part_number, description = :description, qty = :qty, as400_ship_date = :as400_ship_date, hold_reason = :hold_reason, hold_date = :hold_date
					WHERE id = :order_id
				";
				$statement = $pdo->prepare($query);
				$statement->execute([
					'part_number' => $set_vals['part_number'],
					'description' => $set_vals['description'],
					'qty' => $set_vals['qty'],
					'as400_ship_date' => $set_vals['as400_ship_date'],
					'hold_reason' => $set_vals['hold_reason'],
					'hold_date' => $set_vals['hold_date'],
					'order_id' => $current_old_order['id'],
				]);

				//record result as accepted with a change
				$update_query_results['accepted_change'][] = $update;

			} else if ( $no_change == 0 ) {
				//record result as accepted no change
				$update_query_results['accepted_no_change'][] = $update;
			}
		}

		//order is new because it has no matches
		else if ( $find_match == "Error: No match detected!" ) {

			//insert new record
			$query = "
				INSERT INTO production_data
				(job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, hold_reason, hold_date)
				VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
			";
			$statement = $pdo->prepare($query);
			$statement->execute([
				$update['job_number'], 
				$update['enterprise'], 
				$update['part_number'], 
				$update['description'],
				$update['qty'],
				$update['line_item'],
				$update['as400_ship_date'],
				$update['hold_reason'],
				$update['hold_date'],
			]);

			$update_query_results['new'][] = $update;
		}

		//order has duplicate line items
		else if ( $find_match == "Error: Duplicate line items detected!" ) {
			$update_query_results['rejected'][] = $update;
		}

		//increase count
		$count++;
		
	} else {
		
		//if array isn't empty; increase count
		if (array_filter($column))
			$count++;
		
	}
}

//output update query results
$s = serialize($update_query_results);

//write results to file
$fp = fopen($path_results . $results_prod_log, 'w');
fwrite($fp, serialize($update_query_results));
fclose($fp);

function search_job_and_line_item ($job_number, $line_item, $result) {
	
	$return_key = [];
	
	//loop through result and check for matches
	foreach ( $result as $key => $value )
		if ( $value['job_number'] == $job_number && $value['line_item'] == $line_item )
			$return_key[] = $key;
	
	//more than one match; return error
	if ( count ( $return_key ) > 1 )
		return "Error: Duplicate line items detected!";
	//one match; return the match
	else if ( count ( $return_key ) == 1 )
		return $return_key;
	//no matches; return error
	else
		return "Error: No match detected!";
	
}

?>

I'm not super proud of it. But, it works.