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 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)
{
	global $dbh;
	
	$stmt = $dbh->prepare(
		"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_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(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,
			
			dispatch_reference,
			ctns, weight, cube

		FROM file_row
		INNER JOIN file USING(id_file)
		WHERE id_company = :id_company
		LIMIT 5000");
								
	$stmt->execute(array(':id_company' => $id_company));

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

function getRowUpdatesByComapny($id_company)
{
	global $dbh;
	
	$stmt = $dbh->prepare(
		"SELECT DATE_FORMAT(date_modified, '%m/%d/%Y %h:%i %p') date_modified,
				user.id_user id_user, user.name user_name, id_file_row,
				DATE_FORMAT(time_pickup, '%m/%d/%Y %h:%i %p') time_pickup,
				DATE_FORMAT(time_depart, '%m/%d/%Y %h:%i %p') time_depart,
				DATE_FORMAT(bol_delivered, '%m/%d/%Y %h:%i %p') bol_delivered
		FROM file_row_update
		INNER JOIN user USING(id_user)
		WHERE 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;
}


?>