sd-spreadsheet/api/cron-filescanner.php

<?php

include 'phpexcel/PHPExcel.php';
include 'db.php';

ini_set('memory_limit', '256M');


//for reference!
$columns_to_fields = array(
	'Division' => 'division',
	'Consignee' => 'consignee',
	'Called In' => 'date_called_in',
	'Dispatch #' => 'dispatch_number',
	'Shipper' => 'shipper',
	'Ready' => 'date_ready',
	
	'Address 1' => 'address_1',
	'Address 2' => 'address_2',
	'City' => 'city',
	'ST' => 'state',
	'Zip' => 'zip',
	'Phone' => 'phone',
	
	'Ready Time' => 'time_ready',
	'Close Time' => 'time_close',
	'Pickup Time' => 'time_pickup',
	'Depart Time' => 'time_depart',
	'BOL Delivered' => 'bol_delivered',
	
	'Dispatch Refere' => 'dispatch_reference',
	
	'Ctns' => 'ctns',
	'Weight' => 'weight',
	'Cube' => 'cube'
);

$fields_by_col = array();


$path = '../data';


//scan respective directories for new files
$companies = getCompanyDirectories();

for($c = 0; $c < count($companies); $c++)
{
	$company = $companies[$c];
	
	$id_company = $company['id_company'];
	$name = $company['name'];
	$ftp_directory = $company['ftp_directory'];
	
	$ftp_full_path = "$path/$ftp_directory";
	
	
	echo "scanning #$id_company $name\t$ftp_full_path:\t";
	
	
	//check if the directory exists
	if(!file_exists($ftp_full_path) || !is_dir($ftp_full_path))
	{
		echo "directory not found\n";
		continue;
	}

	
	//find all spreadsheets
	$files = glob("$ftp_full_path/*.xls");
	
	echo 'found ' . count($files) . ' excel spreadsheet(s)' . PHP_EOL;
	
	
	//go through each one
	foreach($files as $filename)
	{
		processFile($filename, $id_company);
		echo PHP_EOL;
	}
}



function processFile($filename, $id_company)
{
	global $dbh;
	
	if(!file_exists($filename))
	{
		echo "file doesn't exist" . PHP_EOL;
		return;
	}
	
	
	
	
	$file_size = filesize($filename);
	$file_hash = md5_file($filename);
	
	$params = array(
		':id_company' => $id_company,
		':filename' => $filename,
		':file_size' => $file_size,
		':file_hash' => $file_hash
	);
	
	
	//first try to find if the file exists
	$stmt_file = $dbh->prepare("SELECT id_file
							FROM file
							WHERE id_company = :id_company
							AND filename = :filename
							AND md5_hash = :file_hash
							AND size = :file_size");
							
	$stmt_file->execute($params);
	
	$rows = $stmt_file->fetchAll(PDO::FETCH_ASSOC);
	
	
	if(count($rows))
	{
		$id_file = $rows[0]['id_file'];
		echo "re-processing file #$id_file" . PHP_EOL;
	}
	else
	{
		//report file processed
		$stmt_file = $dbh->prepare("INSERT INTO file (id_company, filename, md5_hash, size)
									VALUES (:id_company, :filename, :file_hash, :file_size)");
		
		if(!$stmt_file->execute($params))
		{
			echo "PDO::errorInfo():" . PHP_EOL;
			print_r($dbh->errorInfo());
			return;
		}
		
		$id_file = $dbh->lastInsertId();
		
		echo "processing new file #$id_file" . PHP_EOL;
	}

	
	//TODO: check if we already processed this file
	
	
	$stmt_row = $dbh->prepare("INSERT INTO file_row (
			id_file, row_number,
			division, consignee, date_called_in, dispatch_number, shipper, date_ready,
			address_1, address_2, city, state, zip, phone,
			time_ready, time_close, time_pickup, time_depart, bol_delivered,
			dispatch_reference,
			ctns, weight, cube
		) VALUES (
			:id_file, :row_number,
			:division, :consignee, STR_TO_DATE(:date_called_in, '%m/%d/%Y'), :dispatch_number, :shipper, STR_TO_DATE(:date_ready, '%m/%d/%Y'),
			:address_1, :address_2, :city, :state, :zip, :phone,
			:time_ready, :time_close, :time_pickup, :time_depart, :bol_delivered,
			:dispatch_reference,
			:ctns, :weight, :cube
		)
		ON DUPLICATE KEY UPDATE
			division = :division, consignee = :consignee, date_called_in = STR_TO_DATE(:date_called_in, '%m/%d/%Y'), dispatch_number = :dispatch_number, shipper = :shipper, date_ready = STR_TO_DATE(:date_ready, '%m/%d/%Y'),
			address_1 = :address_1, address_2 = :address_2, city = :city, state = :state, zip = :zip, phone = :phone,
			time_ready = :time_ready, time_close = :time_close, time_pickup = :time_pickup, time_depart = :time_depart, bol_delivered = :bol_delivered,
			dispatch_reference = :dispatch_reference,
			ctns = :ctns, weight = :weight, cube = :cube");
		
	if(!$stmt_row)
	{
		echo "PDO::errorInfo():" . PHP_EOL;
		print_r($dbh->errorInfo());
		return;
	}
	
	
	$num_rows = 0;

	try
	{
		//this is necessary to dramatically reduce the memory footprint
		$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
		$cacheSettings = array( ' memoryCacheSize ' => '8MB');
		PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
		
		
		// figure out which type it is
		$type = PHPExcel_IOFactory::identify($filename);
		
		// create an excel reader object
		$reader = PHPExcel_IOFactory::createReader($type);
		
		$reader->setReadDataOnly(false);
		
		echo "xls detected as: $type" . PHP_EOL;
		
		// load the file
		$excel = $reader->load($filename);
		
		// pull the 'call details' worksheet, which should be the only one
		$sheet = $excel->getSheet(0);
		
		// count the number of rows and columns
		$num_rows = $sheet->getHighestRow(); 
		$last_column = $sheet->getHighestColumn();
		
		
		// let's keep track of how many succeeded and failed
		$num_ok = 0;
		$num_fail = 0;
		$num_skip = 0;


		// pull column names
		$column_names = $sheet->rangeToArray('A1:' . $last_column . '1', NULL, TRUE, FALSE);

		$columns_by_names = array();
		
		
		
		//flip the column names to reflect the indexes
		foreach($column_names[0] as $index => $name)
			if($name && strlen(trim($name)))
				$columns_by_names[$name] = $index;
		
		
		// go through and read each row
		for($r = 2; $r <= $num_rows; $r++)
		{
			$range = 'A' . $r . ':' . $last_column . $r;
			echo "processing row #$r...";
			
			// read row of data into array
			$row_array = $sheet->rangeToArray($range, NULL, TRUE, TRUE);

			if(!count($row_array))
			{
				echo "no data\n";
				continue;
			}
			
			//pull the row out and format the nulls as needed
			$row = $row_array[0];
			for($c = 0; $c < count($row); $c++)
				$row[$c] = $row[$c] && strlen(trim($row[$c])) ? trim($row[$c]) : null;
			
			//skip if no data for first three columns
			if(!$row[0] && !$row[1] && !$row[2])
			{
				echo "SKIP\n";
				$num_skip++;
				continue;
			}
			
			//bind appropriate values
			$params = array();
			
			$params[':id_file'] = $id_file;
			$params[':row_number'] = $r;
			
			$params[':division'] = $row[0];
			$params[':consignee'] = $row[1];
			$params[':date_called_in'] = $row[2];
			$params[':dispatch_number'] = $row[3];
			$params[':shipper'] = $row[4];
			$params[':date_ready'] = $row[5];
			
			$params[':address_1'] = $row[6];
			$params[':address_2'] = $row[7];
			$params[':city'] = $row[8];
			$params[':state'] = $row[9];
			$params[':zip'] = $row[10];
			$params[':phone'] = $row[11];
			
			$params[':time_ready'] = $row[14] ? DateTime::createFromFormat('H:i A', $row[14])->format('H:i:s') : null;
			$params[':time_close'] = $row[15] ? DateTime::createFromFormat('H:i A', $row[15])->format('H:i:s') : null;
			$params[':time_pickup'] = $row[16] ? DateTime::createFromFormat('H:i A', $row[16])->format('H:i:s') : null;
			$params[':time_depart'] = $row[17] ? DateTime::createFromFormat('H:i A', $row[17])->format('H:i:s') : null;
			$params[':bol_delivered'] = $row[18];
			
			$params[':dispatch_reference'] = $row[35];
			
			$params[':ctns'] = $row[44];
			$params[':weight'] = $row[45];
			$params[':cube'] = $row[46];

				
			try
			{
				$stmt_row->execute($params);
				
				echo "OK\n";
				$num_ok++;
			}
			catch(Exception $e)
			{
				echo 'FAIL: ' . $e->getMessage() . "\n";
				$num_fail++;
			}
		}
	}
	catch(Exception $e)
	{
		echo "Uncaught fatal error while attempting to process the downloaded report: " . $e->getMessage() . "\n";
	}
	
	
	//update the final status
	
	$stmt_file_status = $dbh->prepare("UPDATE file SET
										num_rows = :num_rows,
										num_ok = :num_ok,
										num_fail = :num_fail,
										num_skip = :num_skip
										WHERE id_file = :id_file");

	$params = array(
		':id_file' => $id_file,
		':num_rows' => $num_rows-1, //subtract one because of the column header
		':num_ok' => $num_ok,
		':num_fail' => $num_fail,
		':num_skip' => $num_skip
	);
	
	$stmt_file_status->execute($params);
	
}

?>