<?php

require('config.inc.php');

$dbh = new PDO('mysql:host=localhost;dbname=sdpickup', DB_USER, DB_PASS);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


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 getCompanyData($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");
								
	$stmt->execute(array(':id_company' => $id_company));

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

?>