sd-spreadsheet/api/loadXLS.php

<?php

include 'phpexcel/PHPExcel.php';
include 'db.inc.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();


$id_company = 1;

processFile('../test.xls', $id_company);


function processFile($filename_report, $id_company)
{
	global $dbh;
	
	
	//TODO: check if we already processed this file
	
	
	$stmt = $dbh->prepare("INSERT INTO records (
			id_company,
			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_company,
			: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
		)");
		
	if(!$stmt)
	{
		echo "\nPDO::errorInfo():\n";
		print_r($dbh->errorInfo());
		return;
	}
	
	
	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_report);
		
		// create an excel reader object
		$reader = PHPExcel_IOFactory::createReader($type);
		
		$reader->setReadDataOnly(false);
		
		echo "Report detected as: $type\n";
		
		// load the file
		$excel = $reader->load($filename_report);
		
		// 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_success = 0;
		$num_fail = 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";
				continue;
			}
			
			//bind appropriate values
			$params = array();
			
			$params[':id_company'] = $id_company;
			
			$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->execute($params);
			}
			catch(Exception $e)
			{
				echo 'FAIL: ' . $e->getMessage() . "\n";
				continue;
			}
			
			echo "OK\n";
		}
	}
	catch(Exception $e)
	{
		echo "Uncaught fatal error while attempting to process the downloaded report: " . $e->getMessage() . "\n";
	}
}

?>