sd-spreadsheet/db.php

<?php

require('config.inc.php');

$dbh = null;

try
{
	$dbh = new PDO('mysql:host=localhost;dbname=sdpickup', DB_USER, DB_PASS);
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(Exception $e)
{
	error_log(print_r($e, true));

	http_response_code(500);
	echo "unable to connect to DB\n";
	die;
}


function getCompanies()
{
	global $dbh;
	
	$stmt = $dbh->prepare(
		"SELECT id_company, name, ftp_directory, description
		FROM company");
	
	$stmt->execute();
	
	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
	return $rows;
}

function getCompany($id_company)
{
	global $dbh;
	
	$stmt = $dbh->prepare(
		"SELECT id_company, name, ftp_directory, description
		FROM company
		WHERE id_company = :id_company");
	$stmt->bindValue(':id_company', $id_company);
	$stmt->execute();
	
	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
	return $rows;
}

function getCompanyDirectories()
{
	global $dbh;
	
	$stmt = $dbh->prepare("SELECT id_company, name, ftp_directory FROM company");
	$stmt->execute();
	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
	return $rows;
}

function getRowsByCompany($id_company, $year = null, $week_number = null)
{
	global $dbh;
	
	$sql = "SELECT
			id_file_row, row_number,
			division, consignee,
			DATE_FORMAT(date_called_in, '%m/%d/%Y') date_called_in,
			
			dispatch_number, shipper,
			DATE_FORMAT(date_ready, '%m/%d/%Y') date_ready,
			address_1, address_2, city, state, zip, phone,
			
			TIME_FORMAT(time_ready, '%h:%i %p') time_ready,
			TIME_FORMAT(time_close, '%h:%i %p') time_close,
			
			
			(SELECT DATE_FORMAT(IFNULL(time_pickup, file_row.time_pickup), '%m/%d/%Y %h:%i %p')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND time_pickup IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) time_pickup,
				
			(SELECT DATE_FORMAT(IFNULL(time_pickup, file_row.time_pickup), '%m/%d/%Y')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND time_pickup IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) time_pickup_date,
				
			(SELECT DATE_FORMAT(IFNULL(time_pickup, file_row.time_pickup), '%h:%i %p')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND time_pickup IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) time_pickup_time,
				
				
			(SELECT DATE_FORMAT(IFNULL(time_depart, file_row.time_depart), '%m/%d/%Y %h:%i %p')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND time_depart IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) time_depart,
				
			(SELECT DATE_FORMAT(IFNULL(time_depart, file_row.time_depart), '%m/%d/%Y')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND time_depart IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) time_depart_date,
				
			(SELECT DATE_FORMAT(IFNULL(time_depart, file_row.time_depart), '%h:%i %p')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND time_depart IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) time_depart_time,
				
			
			(SELECT DATE_FORMAT(IFNULL(bol_delivered, file_row.bol_delivered), '%m/%d/%Y %h:%i %p')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND bol_delivered IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) bol_delivered,
				
			(SELECT DATE_FORMAT(IFNULL(bol_delivered, file_row.bol_delivered), '%m/%d/%Y')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND bol_delivered IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) bol_delivered_date,
				
			(SELECT DATE_FORMAT(IFNULL(bol_delivered, file_row.bol_delivered), '%h:%i %p')
				FROM file_row_update
				WHERE id_file_row = file_row.id_file_row
				AND bol_delivered IS NOT NULL
				ORDER BY date_modified DESC
				LIMIT 1) bol_delivered_time,
			
			dispatch_reference,
			ctns, weight, cube,
			DATE_FORMAT(file_row.date_processed, '%m/%d/%Y') date_processed,
			
			driver.id_driver id_driver, trailer, date_canceled

		FROM file_row
		INNER JOIN file USING(id_file)
		LEFT OUTER JOIN driver USING(id_driver)
		WHERE id_company = :id_company
		" . ($week_number ? 
		" AND
		(
			id_file_row IN
			(
				SELECT id_file_row
				FROM file_row_update
				WHERE WEEK(date_modified) = :week_number
				AND YEAR(date_modified) = :year
			
				UNION
				
				SELECT id_file_row
				FROM file_row
				WHERE WEEK(date_processed) = :week_number
				WHERE YEAR(date_processed) = :year
			)
		) " : "") .
		"ORDER BY file_row.date_processed DESC
		LIMIT 5000";

	$stmt = $dbh->prepare($sql);

	if($week_number)
	{
		$stmt->bindValue(':week_number', $week_number);
		$stmt->bindValue(':year', $year);
	}
	
	$stmt->bindValue(':id_company', $id_company);
	
	$stmt->execute();
	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
	return $rows;
}

function getRowUpdatesByCompany($id_company)
{
	global $dbh;
	
	$stmt = $dbh->prepare(
		"SELECT DATE_FORMAT(fru.date_modified, '%m/%d/%Y %h:%i %p') date_modified,
				user.id_user id_user, user.name user_name, id_file_row,
				fr.dispatch_number dispatch_number,
				fr.dispatch_reference dispatch_reference,
				DATE_FORMAT(fru.time_pickup, '%m/%d/%Y %h:%i %p') time_pickup,
				DATE_FORMAT(fru.time_depart, '%m/%d/%Y %h:%i %p') time_depart,
				DATE_FORMAT(fru.bol_delivered, '%m/%d/%Y %h:%i %p') bol_delivered
		FROM file_row_update fru
		INNER JOIN file_row fr USING(id_file_row)
		INNER JOIN file USING(id_file)
		INNER JOIN user USING(id_user)		
		WHERE file.id_company = :id_company
		ORDER BY date_created DESC
		LIMIT 200");
	
	$stmt->execute(array(':id_company' => $id_company));

	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
	return $rows;
}

function getRowsByCompanyCSV($id_company, $f, $year, $week_number = null)
{
	$file_rows = getRowsByCompany($id_company, $year, $week_number);

	$headers = array('Division', 'Consignee', 'Called In', 'Dispatch #', 'Shipper', 'Ready', 'Address 1', 'Address 2', 'City', 'ST', 'Zip', 'Phone',
						'', '', '', '',
						'Acknowledgement', 'Pickup Date', 'Pickup Time', 'Depart Date', 'Depart Time', 'BOL Delivered Date', 'BOL Delivered Time',
						'', '', '', '', '', '', '', '', '', '', '', '',
						'Dispatch Reference',
						'', '', '', '', '', '', '', '',
						'Ctns', 'Weight', 'Cube');
						
	fputcsv($f, $headers);

	foreach($file_rows as $row)
	{
		fputcsv($f,
			array($row['division'], $row['consignee'], $row['date_called_in'], $row['dispatch_number'], $row['shipper'], $row['date_ready'], $row['address_1'], $row['address_2'], $row['city'], $row['state'], $row['zip'], $row['phone'],
			'', '', '', '',
			$row['date_processed'], $row['time_pickup_date'], $row['time_pickup_time'], $row['time_depart_date'], $row['time_depart_time'], $row['bol_delivered_date'], $row['bol_delivered_time'],
			'', '', '', '', '', '', '', '', '', '', '', '',
			$row['dispatch_reference'],
			'', '', '', '', '', '', '', '',
			$row['ctns'], $row['weight'], $row['cube']));
	}
}

function getDriversByCompany($id_company)
{
	global $dbh;
	
	$stmt = $dbh->prepare(
		"SELECT id_driver, driver.name
		FROM driver_company
		INNER JOIN driver USING(id_driver)
		WHERE driver_company.id_company = :id_company
		ORDER BY name DESC
		LIMIT 500");
	
	$stmt->execute(array(':id_company' => $id_company));

	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
	
	return $rows;
}


?>