<?php

require_once (SF_ROOT_DIR . '/classes/class.mail_riak_transport.php');

/**
 * The TriggerMailOrders class compiles billing information for clients that use
 * reporting. We pull in the code that generates the weekly chunks of data and
 * saves them to the Riak cluster.
 */

class TriggerMailOrders
{
	/**
	 * BettyConnection
	 * @return connection to betty
	 */
	private function BettyConnection()
	{
		$sWoSqlServer	= "betty";
		$sWoSqlUser		= "db4names";
		$sWoSqlPass		= "Lv$1aZ38*7";
		$sWoSqlDatabase	= "americanworkorders";

		$conn = mysql_connect($sWoSqlServer, $sWoSqlUser, $sWoSqlPass, true) or die("Internal server error occurred, please try again."); #mysql_error()
		mysql_select_db($sWoSqlDatabase, $conn) or die("Internal server error occurred, please try again.");

		return $conn;
	}


	/**
	 * WilmaConnection
	 * @return connection to wilma
	 */
	private function WilmaConnection()
	{
		$conn			= Propel::getConnection();

		return $conn;
	}


	/**
	 * GenerateOnTruckTriggerOrderProducts
	 * @param type $ClientId
	 * @param type $week
	 * @param type $OrderType
	 * @param type $ReportType
	 * @return type
	 */
	private function GenerateOnTruckTriggerOrderProducts($ClientId=0, $week, $OrderType, $ReportType)
	{
		# Get database connections
		$Betty			= $this->BettyConnection();
		$Wilma			= $this->WilmaConnection();

		# Get a list of triggermail orders based upon their 'on-truck' date.
		$client_id				= 264;
		$work_orders			= array();
		$job_groups				= array();
		$job_group_workorder	= array();
		$customer_list			= array();
		$on_truck				= array();
		$return					= array();
		$arrayCustomerClient	= array();

		$DateFrom	= $week['from'];
		$DateTo		= $week['to'];

		# Get list of work orders between date range
		$sql = "select distinct(txtwosubnum), dtOnTruck from tbl0{$client_id}workordermaildates where dtOnTruck >= '{$DateFrom}' and dtOnTruck <= '{$DateTo}' order by txtwosubnum";
		$result = mysql_query($sql, $Betty) or die("Internal server error occurred, please try again.");
		while ($sData = mysql_fetch_assoc($result)) {
			$work_orders[] = strtolower($sData['txtwosubnum']);
			$on_truck[$sData['txtwosubnum']] = $sData['dtOnTruck'];
		}

		unset ($result);

		if ( count($work_orders) > 0 ) {
			# Get a list of the job groups for these work orders
			$C = new Criteria();
			$C->add(Us2JobGroupPeer::WO_NUMBER, $work_orders, Criteria::IN);
			$C->addSelectColumn(Us2JobGroupPeer::US2_JOB_GROUP_ID);
			$C->addSelectColumn(Us2JobGroupPeer::WO_NUMBER);
			$C->setDistinct(Us2JobGroupPeer::US2_JOB_GROUP_ID);
			$rs = Us2JobGroupPeer::doSelectRs($C);
			while ($rs->next()) {
				$job_groups[] = $rs->getString(1);
				$job_group_workorder[$rs->getString(1)] = strtolower($rs->getString(2));
			}
			unset($rs);

			if ( count($job_groups) > 0 ) {
				$sql = "SELECT `us2_order_product`.`order_id`, `us2_order_product`.`order_product_id`, "
						. "`us2_order_product`.`job_group_id`, `us2_order_product`.`product_id`, "
						. "`us2_order_product`.`total_quan`, `us2_order_product`.`customer_id`, "
						. "`us2_order_product`.`total_price`, `us2_order_product`.`wholesale_total_price`, "
						. "`us2_order_product`.`added_date`, `us2_order`.`order_created_date` "
						. "FROM `us2_order_product` "
						. "LEFT JOIN `us2_order` "
						. "ON `us2_order_product`.`order_id` = `us2_order`.`order_id` "
						. "WHERE `us2_order_product`.`job_group_id` IN (" . implode(',', $job_groups) . ") "
						. "AND `us2_order_product`.`order_handling_status_id` = '5' "
						. "ORDER BY `us2_order`.`order_created_date` ASC, "
						. "`us2_order_product`.`job_group_id` ASC, "
						. "`us2_order_product`.`order_id` ASC, "
						. "`us2_order_product`.`order_product_id` ASC";
				$rs = $Wilma->executeQuery($sql);

				while ($rs->next()) {
					$order_id				= $rs->get('order_id');
					$order_product_id		= $rs->get('order_product_id');
					$job_group_id			= $rs->get('job_group_id');
					$product_id				= $rs->get('product_id');
					$total_quan				= $rs->get('total_quan');
					$customer_id			= $rs->get('customer_id');
					$total_price			= $rs->get('total_price');
					$wholesale_total_price	= $rs->get('wholesale_total_price');
					$added_date				= $rs->get('added_date');
					$close_date				= $rs->get('order_created_date');

					# Get the client id for this customer
					$customer_client_id		= array_key_exists($customer_id, $arrayCustomerClient) ? $arrayCustomerClient[$customer_id] : false;
					if (!$customer_client_id) {
						$customer			= CustomerPeer::retrieveByPk($customer_id);
						$customer_client_id = $customer->getClientId();
						$customer_name 		= $customer->getName();
						$external_id		= $customer->getExternalId();
						$external_po		= $customer->getExternalPo();

						$customer_list[$customer_id] = array(
							'name'				=> strtoupper($customer_name),
							'external_id'		=> $external_id,
							'external_po'		=> $external_po
						);
					}
					$arrayCustomerClient[$customer_id] = $customer_client_id;

					if ($customer_client_id == $ClientId) {
						$return[$customer_id][] = array(
							'order_product_id'		=> $order_product_id,
							'product_id'			=> $product_id,
							'order_id'				=> $job_group_workorder[$job_group_id],
							'job_group_id'			=> $job_group_id,
							'total_quan'			=> $total_quan,
							'work_order'			=> $job_group_workorder[$job_group_id],
							'on_truck_date'			=> $on_truck[$job_group_workorder[$job_group_id]],
							'total_price'			=> $total_price,
							'wholesale_total_price'	=> $wholesale_total_price,
							'added_date'			=> $added_date,
							'client_id'				=> $customer_client_id,
							'us2_order_id'			=> $order_id,
							'close_date'			=> $close_date
						);
					}
				}
			}

			asort($customer_list);
		}
		$return_set = array('customers' => $customer_list, 'report' => $return);
		return $return_set;
	}


	/**
	 * GetOnTruckTriggerOrderProducts
	 * @param type $ClientId
	 * @param type $DateFrom_in
	 * @param type $DateTo_in
	 * @param type $customer_ids
	 * @return type
	 */
	public function GetOnTruckTriggerOrderProducts($ClientId=0, $DateFrom_in, $DateTo_in, $customer_ids=array())
	{
		$RiakTransport	= new MailRiakTransport();
		$OrderType		= 'trigger';
		$ReportType		= 'ontruckdate';

		// Block out the dates, one week at a time.
		$arrWeeks		= $this->GetArrWeeks($DateFrom_in, $DateTo_in);

		$arrResults = array();
		// Do these one week at a time
		foreach ($arrWeeks as $week) {
			// Try to pull the weekly report out of Riak
			$return_set = $RiakTransport->Get($ClientId, $week['from'], $OrderType, $ReportType);

			// If it doesn't exist there
			if ($return_set === false) {
				$return_set = $this->GenerateOnTruckTriggerOrderProducts($ClientId, $week, $OrderType, $ReportType);
				// Save to Riak for the next time
				$RiakTransport->Save($ClientId, $week['from'], $OrderType, $ReportType, $return_set);
			}

			$arrResults[] = $return_set;
		}

		$DateFilterMin	= $DateFrom_in . " 00:00:00";
		$DateFilterMax	= $DateTo_in . " 23:59:59";
		$arrCustomers	= array();
		$arrReport		= array();
		// Combine the weekly chunks
		foreach ($arrResults as $result) {
			foreach ($result['customers'] as $customer_id => $data) {
				if (empty($customer_ids) || in_array($customer_id, $customer_ids)) {
					$arrCustomers[$customer_id] = $data;
				}
			}
			// Filter by date range requested
			foreach ($result['report'] as $customer_id => $reports) {
				if (empty($customer_ids) || in_array($customer_id, $customer_ids)) {
					foreach ($reports as $report) {
						// If the report is within the actual range requested
						if ($report['on_truck_date'] >= $DateFilterMin && $report['on_truck_date'] <= $DateFilterMax) {
							$arrReport[$customer_id][] = $report;
						}
					}
				}
			}
		}
		// Sort customer list by name
		$this->NameSort($arrCustomers);
		$return_set = array('customers' => $arrCustomers, 'report' => $arrReport);
		return $return_set;
	}


	/**
	 * GenerateClosedOrderProducts
	 * @param type $ClientId
	 * @param type $week
	 * @param type $OrderType
	 * @param type $ReportType
	 * @return array
	 */
	private function GenerateClosedOrderProducts($ClientId=0, $week, $OrderType, $ReportType, $debug = false)
	{
		# Get database connections
		$Betty			= $this->BettyConnection();
		$Wilma			= $this->WilmaConnection();

		$DateFrom                       = $week['from'];
		$DateTo                         = $week['to'];
		$OrderList                      = array();
		$OrderProductList               = array();
		$OrderCloseList                 = array();
		$CustList                       = array();
		$JobGroupList                   = array();
		$WorkOrderList                  = array();
		$arrayCustomerClient			= array();
		$return							= array();

		// Get list of client customers
		$Query = "select customer_id from customer where client_id = {$ClientId} and active_yn = 'Y'";
		$Rs = $Wilma->executeQuery($Query);
		while ($Rs->next()) {
			$CustList[] = $Rs->getInt('customer_id');
		}

		// Get the orders that closed during the reporting window
		$Query = "select order_id, order_created_date, us2_order.customer_id "
				. "from us2_order "
				. "where customer_id in (" . join(',', $CustList) . ") "
				. "and order_created_date between '{$DateFrom}' and '{$DateTo}' "
				. "and us2_order_type_id = 3 "
				. "and order_handling_status_id < 9 ";

		$Rs = $Wilma->executeQuery($Query);
		while ($Rs->next()) {
			$OrderList[]       = $Rs->getInt('order_id');
			$OrderCloseList[$Rs->getInt('order_id')]   = array('order_created_date' => $Rs->getString('order_created_date'),
																'customer_id'       => $Rs->getInt('customer_id')
															);
		}

		// If we got nothing - stop and return nothing
		if ( count($OrderList) == 0 ) {
			$return_set = array('customers' => array(), 'report' => array());
			return $return_set;
		}

		// Get the order_product_ids for the orders that were captured above
		$Query = "select order_product_id "
				. "from us2_order_product "
				. "where order_id in (" . join(",", $OrderList) . ") "
				. "and order_handling_status_id < 9 ";

		$Rs = $Wilma->executeQuery($Query);
		while ($Rs->next()) {
			$OrderProductList[] = $Rs->getInt('order_product_id');
		}

		// lets build the report
		if ( count($OrderProductList) > 0 ) {
			$sql = "SELECT `us2_order_product`.`order_id`, `us2_order_product`.`order_product_id`, "
					. "`us2_order_product`.`job_group_id`, `us2_order_product`.`product_id`, "
					. "`us2_order_product`.`total_quan`, `us2_order_product`.`customer_id`, "
					. "`us2_order_product`.`total_price`, `us2_order_product`.`wholesale_total_price`, "
					. "`us2_order_product`.`added_date`, `us2_order`.`order_created_date` "
					. "FROM `us2_order_product` "
					. "LEFT JOIN `us2_order` "
					. "ON `us2_order_product`.`order_id` = `us2_order`.`order_id` "
					. "WHERE `us2_order`.`order_created_date` BETWEEN '{$DateFrom}' AND '{$DateTo}' "
					. "AND `us2_order_product`.`order_product_id` IN (" . implode(',', $OrderProductList) . ") "
					. "ORDER BY `us2_order`.`order_created_date` ASC, "
					. "`us2_order_product`.`job_group_id` ASC, "
					. "`us2_order_product`.`order_id` ASC, "
					. "`us2_order_product`.`order_product_id` ASC";
			if ($debug) {
				echo __FUNCTION__."\n{$sql}\n\n";
			}
			$rs = $Wilma->executeQuery($sql);

			while ($rs->next()) {
				$order_id				= $rs->get('order_id');
				$order_product_id		= $rs->get('order_product_id');
				$job_group_id			= $rs->get('job_group_id');
				$product_id				= $rs->get('product_id');
				$total_quan				= $rs->get('total_quan');
				$customer_id			= $rs->get('customer_id');
				$total_price			= $rs->get('total_price');
				$wholesale_total_price	= $rs->get('wholesale_total_price');
				$added_date				= $rs->get('added_date');
				$close_date				= $rs->get('order_created_date');

				$customer_id            = $OrderCloseList[$order_id]['customer_id'];    //because it's not always in the order_product yet
				// Create a list of all job group ids
				$JobGroupList[$job_group_id] = $job_group_id;

				# Get the client id for this customer
				$customer_client_id     = array_key_exists($customer_id, $arrayCustomerClient) ? $arrayCustomerClient[$customer_id] : false;
				if (!$customer_client_id) {
					$customer				= CustomerPeer::retrieveByPk($customer_id);
					$customer_client_id		= $customer->getClientId();
					$customer_name			= $customer->getName();
					$external_id			= $customer->getExternalId();
					$external_po			= $customer->getExternalPo();

					$customer_list[$customer_id] = array(
						'name'				=> strtoupper($customer_name),
						'external_id'		=> $external_id,
						'external_po'		=> $external_po
					);
					$arrayCustomerClient[$customer_id] = $customer_client_id;
				}

				if ($customer_client_id  == $ClientId) {
					$return[$customer_id][] = array(
						'order_product_id'		=> $order_product_id,
						'product_id'			=> $product_id,
						'order_id'				=> $order_id,
						'job_group_id'			=> $job_group_id,
						'total_quan'			=> $total_quan,
						'work_order'			=> "",
						'on_truck_date'			=> "",
						'total_price'			=> $total_price,
						'wholesale_total_price'	=> $wholesale_total_price,
						'added_date'			=> $added_date,
						'client_id'				=> $customer_client_id,
						'us2_order_id'			=> $order_id,
						'close_date'			=> $close_date
					);
				}
			}
		}

		asort($customer_list);

		// Get a de-duped list of all job group ids
		$job_group_ids = array_keys($JobGroupList);
		$C = new Criteria();
		$C->addSelectColumn(Us2JobGroupPeer::US2_JOB_GROUP_ID);
		$C->addSelectColumn(Us2JobGroupPeer::WO_NUMBER);
		$C->add(Us2JobGroupPeer::US2_JOB_GROUP_ID, $job_group_ids, Criteria::IN);
		$rs = Us2JobGroupPeer::doSelectRS($C);
		while ($rs->next()) {
			$wo_number                      = $rs->get(2);
			if (!empty($wo_number)) {
				$job_group_id                   = $rs->get(1);
				$wo_number                      = strtolower($wo_number);
				$WorkOrderList[$job_group_id]   = $wo_number;
			}
		}

		// Get a de-duped list of wo numbers
		$WorkOrders = array();
		foreach ($WorkOrderList as $WorkOrder) {
			$WorkOrders[$WorkOrder] = $WorkOrder;
		}
		if (!empty($WorkOrders)) {
			$WorkOrders = array_keys($WorkOrders);
			$WorkOrders = implode("','", $WorkOrders);

			# Get a list of triggermail orders based on the work order list.
			$client_id          = 264;
			$on_truck           = array();

			$sql = "select distinct(txtwosubnum), dtOnTruck from tbl0" . $client_id . "workordermaildates where txtwosubnum in ('" . $WorkOrders . "')";
			$result = mysql_query($sql, $Betty) or die("Internal server error occurred, please try again.");
			while ($sData = mysql_fetch_assoc($result)) {
				$txtwosubnum                = $sData['txtwosubnum'];
				if (!empty($txtwosubnum)) {
					$txtwosubnum                = strtolower($txtwosubnum);
					$work_orders[]              = $txtwosubnum;
					if (!empty($sData['dtOnTruck'])) {
						$on_truck[$txtwosubnum] = date('m/d/Y', strtotime($sData['dtOnTruck']));
					}
				}
			}

			/*
				Do a 2nd query for the real client id just in case they put the trigger work order
				under another client besides TRM
			*/
			$sql = "select distinct(txtwosubnum), dtOnTruck from tbl0{$ClientId}workordermaildates where txtwosubnum in ('" . $WorkOrders . "')";
			$result = mysql_query($sql, $Betty) or die("Internal server error occurred, please try again.");
			while ($sData = mysql_fetch_assoc($result)) {
				$txtwosubnum                = $sData['txtwosubnum'];
				if (!empty($txtwosubnum)) {
					$txtwosubnum                = strtolower($txtwosubnum);
					$work_orders[]              = $txtwosubnum;
					if (!empty($sData['dtOnTruck'])) {
						$on_truck[$txtwosubnum] = date('m/d/Y', strtotime($sData['dtOnTruck']));
					}
				}
			}

			foreach ($return as $customer_id => $items) {
				$array = array();
				foreach ($items as $item) {
					if (array_key_exists($item['job_group_id'], $WorkOrderList) && array_key_exists($WorkOrderList[$item['job_group_id']], $on_truck)) {
						$on_truck_date = $on_truck[$WorkOrderList[$item['job_group_id']]];
						if (!empty($on_truck_date)) {
							$item['on_truck_date'] = $on_truck_date;
						}
					}
					$array[] = $item;
				}
				$return[$customer_id] = $array;
			}
		}
		$return_set = array('customers' => $customer_list, 'report' => $return);
		return $return_set;
	}


	/**
	 * GenerateClosedOrderProducts001
	 * @param type $ClientId
	 * @param type $week
	 * @param type $OrderType
	 * @param type $ReportType
	 * @return array
	 */
	private function GenerateClosedOrderProducts001($ClientId=0, $week, $OrderType, $ReportType, $debug=false)
	{
		# Get database connections
		$Betty			= $this->BettyConnection();
		$Wilma			= $this->WilmaConnection();

		$DateFrom                       = $week['from'];
		$DateTo                         = $week['to'];
		$OrderProductList               = array();
		$OrderCloseList                 = array();
		$CustList                       = array();
		$JobGroupList                   = array();
		$WorkOrderList                  = array();
		$arrayCustomerClient			= array();
		$return							= array();

		// Get list of client customers
		$Query = "select customer_id from customer where client_id = {$ClientId} and active_yn = 'Y'";
		$Rs = $Wilma->executeQuery($Query);
		while ($Rs->next()) {
			$CustList[] = $Rs->getInt('customer_id');
		}

		// Get the orders and order products that closed during the reporting window
		$Query = "SELECT DISTINCTROW us2_order.order_id, us2_order.order_created_date, us2_order.customer_id, us2_order_product.order_product_id "
				. "FROM us2_order "
				. "LEFT JOIN us2_order_product "
				. "ON us2_order.order_id = us2_order_product.order_id "
				. "AND us2_order_product.order_handling_status_id < 9 "
				. "WHERE us2_order.customer_id IN (" . join(',', $CustList) . ") "
				. "AND us2_order.order_created_date BETWEEN '{$DateFrom}' AND '{$DateTo}' "
				. "AND us2_order.us2_order_type_id = 3 "
				. "AND us2_order.order_handling_status_id < 9 ";

		$Rs = $Wilma->executeQuery($Query);

		// If we got nothing - stop and return nothing
		if ($Rs->getRecordCount() == 0) {
			$return_set = array('customers' => array(), 'report' => array());
			return $return_set;
		}

		while ($Rs->next()) {
			$OrderCloseList[$Rs->getInt('order_id')]	= array('order_created_date'	=> $Rs->getString('order_created_date'),
																'customer_id'			=> $Rs->getInt('customer_id')
															);
			$order_product_id = $Rs->getInt('order_product_id');
			if (!empty($order_product_id)) {
				$OrderProductList[]							= $order_product_id;
			}
		}

		// lets build the report
		if ( count($OrderProductList) > 0 ) {
			$sql = "SELECT `us2_order_product`.`order_id`, `us2_order_product`.`order_product_id`, "
					. "`us2_order_product`.`job_group_id`, `us2_order_product`.`product_id`, "
					. "`us2_order_product`.`total_quan`, `us2_order_product`.`customer_id`, "
					. "`us2_order_product`.`total_price`, `us2_order_product`.`wholesale_total_price`, "
					. "`us2_order_product`.`added_date`, `us2_order`.`order_created_date` "
					. "FROM `us2_order_product` "
					. "LEFT JOIN `us2_order` "
					. "ON `us2_order_product`.`order_id` = `us2_order`.`order_id` "
					. "WHERE `us2_order`.`order_created_date` BETWEEN '{$DateFrom}' AND '{$DateTo}' "
					. "AND `us2_order_product`.`order_product_id` IN (" . implode(',', $OrderProductList) . ") "
					. "ORDER BY `us2_order`.`order_created_date` ASC, "
					. "`us2_order_product`.`job_group_id` ASC, "
					. "`us2_order_product`.`order_id` ASC, "
					. "`us2_order_product`.`order_product_id` ASC";
			if ($debug) {
				echo __FUNCTION__."\n{$sql}\n\n";
			}
			$rs = $Wilma->executeQuery($sql);

			while ($rs->next()) {
				$order_id				= $rs->get('order_id');
				$order_product_id		= $rs->get('order_product_id');
				$job_group_id			= $rs->get('job_group_id');
				$product_id				= $rs->get('product_id');
				$total_quan				= $rs->get('total_quan');
				$customer_id			= $rs->get('customer_id');
				$total_price			= $rs->get('total_price');
				$wholesale_total_price	= $rs->get('wholesale_total_price');
				$added_date				= $rs->get('added_date');
				$close_date				= $rs->get('order_created_date');

				$customer_id            = $OrderCloseList[$order_id]['customer_id'];    //because it's not always in the order_product yet
				// Create a list of all job group ids
				$JobGroupList[$job_group_id] = $job_group_id;

				# Get the client id for this customer
				$customer_client_id     = array_key_exists($customer_id, $arrayCustomerClient) ? $arrayCustomerClient[$customer_id] : false;
				if (!$customer_client_id) {
					$customer				= CustomerPeer::retrieveByPk($customer_id);
					$customer_client_id		= $customer->getClientId();
					$customer_name			= $customer->getName();
					$external_id			= $customer->getExternalId();
					$external_po			= $customer->getExternalPo();

					$customer_list[$customer_id] = array(
						'name'				=> strtoupper($customer_name),
						'external_id'		=> $external_id,
						'external_po'		=> $external_po
					);
					$arrayCustomerClient[$customer_id] = $customer_client_id;
				}

				if ($customer_client_id  == $ClientId) {
					$return[$customer_id][] = array(
						'order_product_id'		=> $order_product_id,
						'product_id'			=> $product_id,
						'order_id'				=> $order_id,
						'job_group_id'			=> $job_group_id,
						'total_quan'			=> $total_quan,
						'work_order'			=> "",
						'on_truck_date'			=> "",
						'total_price'			=> $total_price,
						'wholesale_total_price'	=> $wholesale_total_price,
						'added_date'			=> $added_date,
						'client_id'				=> $customer_client_id,
						'us2_order_id'			=> $order_id,
						'close_date'			=> $close_date
					);
				}
			}
		}

		asort($customer_list);

		// Get a de-duped list of all job group ids
		$job_group_ids = array_keys($JobGroupList);
		$C = new Criteria();
		$C->addSelectColumn(Us2JobGroupPeer::US2_JOB_GROUP_ID);
		$C->addSelectColumn(Us2JobGroupPeer::WO_NUMBER);
		$C->add(Us2JobGroupPeer::US2_JOB_GROUP_ID, $job_group_ids, Criteria::IN);
		$rs = Us2JobGroupPeer::doSelectRS($C);
		while ($rs->next()) {
			$wo_number                      = $rs->get(2);
			if (!empty($wo_number)) {
				$job_group_id                   = $rs->get(1);
				$wo_number                      = strtolower($wo_number);
				$WorkOrderList[$job_group_id]   = $wo_number;
			}
		}

		// Get a de-duped list of wo numbers
		$WorkOrders = array();
		foreach ($WorkOrderList as $WorkOrder) {
			$WorkOrders[$WorkOrder] = $WorkOrder;
		}
		if (!empty($WorkOrders)) {
			$WorkOrders = array_keys($WorkOrders);
			$WorkOrders = implode("','", $WorkOrders);

			# Get a list of triggermail orders based on the work order list.
			$client_id          = 264;
			$on_truck           = array();

			$sql = "select distinct(txtwosubnum), dtOnTruck from tbl0" . $client_id . "workordermaildates where txtwosubnum in ('" . $WorkOrders . "')";
			$result = mysql_query($sql, $Betty) or die("Internal server error occurred, please try again.");
			while ($sData = mysql_fetch_assoc($result)) {
				$txtwosubnum                = $sData['txtwosubnum'];
				if (!empty($txtwosubnum)) {
					$txtwosubnum                = strtolower($txtwosubnum);
					$work_orders[]              = $txtwosubnum;
					if (!empty($sData['dtOnTruck'])) {
						$on_truck[$txtwosubnum] = date('m/d/Y', strtotime($sData['dtOnTruck']));
					}
				}
			}

			/*
				Do a 2nd query for the real client id just in case they put the trigger work order
				under another client besides TRM
			*/
			$sql = "select distinct(txtwosubnum), dtOnTruck from tbl0{$ClientId}workordermaildates where txtwosubnum in ('" . $WorkOrders . "')";
			$result = mysql_query($sql, $Betty) or die("Internal server error occurred, please try again.");
			while ($sData = mysql_fetch_assoc($result)) {
				$txtwosubnum                = $sData['txtwosubnum'];
				if (!empty($txtwosubnum)) {
					$txtwosubnum                = strtolower($txtwosubnum);
					$work_orders[]              = $txtwosubnum;
					if (!empty($sData['dtOnTruck'])) {
						$on_truck[$txtwosubnum] = date('m/d/Y', strtotime($sData['dtOnTruck']));
					}
				}
			}

			foreach ($return as $customer_id => $items) {
				$array = array();
				foreach ($items as $item) {
					if (array_key_exists($item['job_group_id'], $WorkOrderList) && array_key_exists($WorkOrderList[$item['job_group_id']], $on_truck)) {
						$on_truck_date = $on_truck[$WorkOrderList[$item['job_group_id']]];
						if (!empty($on_truck_date)) {
							$item['on_truck_date'] = $on_truck_date;
						}
					}
					$array[] = $item;
				}
				$return[$customer_id] = $array;
			}
		}
		$return_set = array('customers' => $customer_list, 'report' => $return);
		return $return_set;
	}


	/**
	 * GetClosedOrderProducts
	 * @param type $ClientId
	 * @param type $DateFrom_in
	 * @param type $DateTo_in
	 * @param type $customer_ids
	 * @return array
	 */
	public function GetClosedOrderProducts($ClientId=0, $DateFrom_in, $DateTo_in, $customer_ids=array())
	{
		$RiakTransport	= new MailRiakTransport();
		$OrderType		= 'trigger';
		$ReportType		= 'closedate';

		// Block out the dates, one week at a time.
		$arrWeeks		= $this->GetArrWeeks($DateFrom_in, $DateTo_in);

		$arrResults = array();
		// Do these one week at a time
		foreach ($arrWeeks as $week) {
			// Try to pull the weekly report out of Riak
			$return_set = $RiakTransport->Get($ClientId, $week['from'], $OrderType, $ReportType);

			// If it doesn't exist there
			if ($return_set === false) {
				$return_set = $this->GenerateClosedOrderProducts($ClientId, $week, $OrderType, $ReportType);
				// Save to Riak for the next time
				$RiakTransport->Save($ClientId, $week['from'], $OrderType, $ReportType, $return_set);
				$arrResults[] = $return_set;
			} else {
				$arrResults[] = $return_set;
			}
		}

		$DateFilterMin	= $DateFrom_in . " 00:00:00";
		$DateFilterMax	= $DateTo_in . " 23:59:59";
		$arrCustomers	= array();
		$arrReport		= array();
		// Combine the weekly chunks
		foreach ($arrResults as $result) {
			foreach ($result['customers'] as $customer_id => $data) {
				if (empty($customer_ids) || in_array($customer_id, $customer_ids)) {
					$arrCustomers[$customer_id] = $data;
				}
			}
			// Filter by date range requested
			foreach ($result['report'] as $customer_id => $reports) {
				if (empty($customer_ids) || in_array($customer_id, $customer_ids)) {
					foreach ($reports as $report) {
						// If the report is within the actual range requested
						if ($report['close_date'] >= $DateFilterMin && $report['close_date'] <= $DateFilterMax) {
							$arrReport[$customer_id][] = $report;
						}
					}
				}
			}
		}
		// Sort customer list by name
		$this->NameSort($arrCustomers);
		$return_set = array('customers' => $arrCustomers, 'report' => $arrReport);
		return $return_set;
	}


	/**
	 * NameSort
	 * @param type $arrCustomers
	 */
	private function NameSort(&$arrCustomers)
	{
		if (!empty($arrCustomers)) {
			$temp = array();
			// Create a temporary array
			foreach ($arrCustomers as $customer_id => $detail)
			{
				$temp[$customer_id] = $detail['name'];
			}
			// Sort it by name
			asort($temp);
			$result = array();
			// Restore the details to the sorted array
			foreach ($temp as $customer_id => $name)
			{
				$result["{$customer_id}"] = $arrCustomers[$customer_id];
			}
			$arrCustomers = $result;
		}
	}


	/**
	 * GetArrWeeks
	 * @param type $FromDate
	 * @param type $ToDate
	 * @return array of from/to dates or false if error in parameters
	 */
	private function GetArrWeeks($FromDate, $ToDate)
	{
		// Some error checking--if dates are reversed
		if ($FromDate > $ToDate) {
			return false;
		}

		// Compute first Sunday
		$dowFrom = date('w', strtotime($FromDate));
		$dateFrom = date('Y-m-d', strtotime("-{$dowFrom} days", strtotime($FromDate)));
		// Compute last Saturday
		$dowTo = 6 - date('w', strtotime($ToDate));
		$dateTo = date('Y-m-d', strtotime("+{$dowTo} days", strtotime($ToDate)));

		// Create an array of weeks for data collection
		$arrWeeks = array();
		for ($date = $dateFrom; $date <= $dateTo; $date = date('Y-m-d', strtotime("+7 days", strtotime($date)))) {
			$DateFrom	= date("Y-m-d", strtotime($date)) . " 00:00:00";
			$DateTo 	= date("Y-m-d", strtotime("+6 days", strtotime($date))) . " 23:59:59";
			$arrWeeks[] = array(
				'from' => $DateFrom,
				'to' => $DateTo
			);
		}

		return $arrWeeks;
	}


	/**
	 * DailyProcess
	 * @param type $arrClientIds
	 */
	public function DailyProcess($arrClientIds = array())
	{
		$RiakTransport	= new MailRiakTransport();

		$start			= date('Y-m-d', strtotime('-3 weeks'));
		$end			= date('Y-m-d');
		$arrWeeks		= $this->GetArrWeeks($start, $end);
		$week			= reset($arrWeeks);

		foreach ($arrWeeks as $week) {
			foreach ($arrClientIds as $ClientId) {
				echo date("Y-m-d H:i:s")." Processing client {$ClientId} week {$week['from']}\n";

				// Generate order closed information
				$OrderType1		= 'trigger';
				$ReportType1	= 'closedate';
				$ReturnSet1		= $this->GenerateClosedOrderProducts($ClientId, $week, $OrderType1, $ReportType1);
				// Save to Riak
				$RiakTransport->Save($ClientId, $week['from'], $OrderType1, $ReportType1, $ReturnSet1);

				// Generate order on truck information
				$OrderType2		= 'trigger';
				$ReportType2	= 'ontruckdate';
				$ReturnSet2		= $this->GenerateOnTruckTriggerOrderProducts($ClientId, $week, $OrderType2, $ReportType2);
				// Save to Riak
				$RiakTransport->Save($ClientId, $week['from'], $OrderType2, $ReportType2, $ReturnSet2);
			}
		}
	}


	/**
	 * TestProcess
	 * @param type $arrClientIds
	 */
	public function TestProcess($arrClientIds = array())
	{
		$start			= date('Y-m-d', strtotime('-6 weeks'));
		$end			= date('Y-m-d');
		$arrWeeks		= $this->GetArrWeeks($start, $end);
		$week			= reset($arrWeeks);
		$total1			= 0.0;
		$total2			= 0.0;

		foreach ($arrWeeks as $week) {
			foreach ($arrClientIds as $ClientId) {
				echo date("Y-m-d H:i:s")." Processing client {$ClientId} week {$week['from']}\n";

				// Generate order closed information
				$OrderType1		= 'trigger';
				$ReportType1	= 'closedate';
				$start1 = microtime(true);
				$ReturnSet1		= $this->GenerateClosedOrderProducts001($ClientId, $week, $OrderType1, $ReportType1);
				$time1 = microtime(true) - $start1;
				$start2 = microtime(true);
				$ReturnSet2		= $this->GenerateClosedOrderProducts($ClientId, $week, $OrderType1, $ReportType1);
				$time2 = microtime(true) - $start2;
				if (!$this->CompareArrays($ReturnSet1, $ReturnSet2)) {
					echo "No match!\n";
				} else {
					echo "Match!\ntime1: {$time1}\ntime2: {$time2}\n";
				}
				$total1 += $time1;
				$total2 += $time2;
			}
		}
		echo "\nFinal times:\ntotal1: {$total1}\ntotal2: {$total2}\n";
	}

	/**
	 * CompareArrays
	 * Recursively compares two associative arrays of arbitrary depth
	 * @param type $array1
	 * @param type $array2
	 * @return boolean
	 */
	public function CompareArrays($array1, $array2)
	{
		// First match the root arrays
		$diff1 = array_diff_assoc($array1, $array2);
		$diff2 = array_diff_assoc($array2, $array1);
		// If no match
		if (!empty($diff1) || !empty($diff2)) {
			return false;
		} else {
			foreach ($array1 as $key => $value) {
				if (is_array($value)) {
					if (!$this->CompareArrays($array1[$key], $array2[$key])) {
						return false;
					}
				}
			}
		}
		return true;
	}
}