<?php
	//ini_set('display_errors',1);
	//error_reporting(E_ALL & ~E_NOTICE);
?>
<?php include('_mod_security.php'); ?>
<?php
	//var_dump($_GET);
	$sql = '';
	switch ($_GET['func']):
		case 'grid' :
			require_once('classes/lcs_db_grid.php');
			$dbg = new lcs_db_grid;
			$dbg->start_header_section();
			$dbg->start_header();
			$dbg->add_column(array('label' => 'Route Name', 'dbcol' => 'r.route_name', 'width' => '250px'));
			$dbg->add_column(array('label' => 'Comments', 'dbcol' => 'r.comments', 'width' => '250px'));
			$dbg->add_column(array('label' => 'Drivers', 'dbcol' => 'r.comments', 'width' => '175px'));
			$dbg->add_column(array('label' => 'Last Delivery', 'searchable' => false, 'dbcol' => 'MAX(d.create_date)', 'width' => '130px'));
			$dbg->add_column(array('label' => 'Last Qty', 'dbcol' => 'qty', 'width' => '65px', 'align' => 'right'));
			$dbg->add_column(array('label' => 'Delete', 'sortable' => false, 'searchable' => false, 'width' => '75px', 'align' => 'center'));
			$dbg->end_header();
			$dbg->end_header_section();
			$sql = "SELECT r.route_id, r.route_name, ".
					"(SELECT GROUP_CONCAT(su.user_name) FROM sys_users su INNER JOIN routes_drivers rd ON (su.user_id = rd.driver_id) WHERE rd.route_id = r.route_id) AS drivers, ".
					"r.comments, CAST(SUM(d.qty) AS SIGNED) AS qty, MAX(d.create_date) AS create_date FROM routes r LEFT OUTER JOIN routes_zip_codes rz ON (r.route_id = rz.route_id) LEFT OUTER JOIN locations l ON (rz.zip = l.zip AND l.active = 1) LEFT OUTER JOIN deliveries d ON (d.location_id = l.location_id  AND d.create_date = (SELECT MAX(create_date) FROM deliveries WHERE location_id = l.location_id) )".
					"WHERE ".$dbg->search_spec()." ".$_GET['parm1']." ".$_GET['parm2']." ".$_GET['parm3']." "." AND r.region_id IN (".get_regions().") ".
					"GROUP BY r.route_id, r.route_name, r.comments ".
					"ORDER BY ".$dbg->sort_spec();
			//echo ' === '.$sql.' === ';
			$xls_sql = $sql;
			$xls_sql_encoded = xls_sql_encrypt($xls_sql);
			$result = $db->query($sql) or die('Database Error!');
			$dbg->start_data_section();
			$total_qty = 0;
			if ($result->rowCount() > 0) :
				while ($row = $result->fetch(PDO::FETCH_OBJ)) :
					$dbg->start_row();
					$dbg->add_cell('<a href="index.php?IX='.$_GET['ix_form'].'&id='.$row->route_id.'" >'.$row->route_name.'</a>');
					$dbg->add_cell($row->comments);
					$dbg->add_cell($row->drivers);
					$dbg->add_cell($row->create_date);
					$dbg->add_cell(number_format($row->qty, 0, '.', ','));
					$dbg->add_cell('<a href="javascript:confirmDelete(\'Are you sure? This action cannot be reversed.\', \''.$row->route_id.'\', \''.$_GET['div'].'\');"><img src="img/delete_icon.gif" style="border:none;" /></a>');
					$dbg->end_row();
					$total_qty += $row->qty;
				endwhile;
			else :
				$dbg->start_row();
				$dbg->add_cell('No records found');
				$dbg->end_row();
			endif;
			$dbg->end_data_section();
			echo json_encode(array('row_count' => $result->rowCount(), 'html' => $dbg->get_html(), 'xls_sql' => $xls_sql_encoded, 'extra_data'=> 'Total qty: '.number_format($total_qty, 0, '.', ',')));
			return;
			break;
		case 'delete' :
			$sql = "DELETE FROM routes WHERE route_id = ".nz(trim($_GET['id']), '0')." AND region_id IN (".get_regions().") ";
			try {
				$db->query($sql);
				$msg = 'Success';
			} catch(PDOException $ex) {
				$msg = 'Database Error! '.$ex->getMessage.' === '.$sql.' === ';
			}
			$sql = "DELETE FROM routes_zip_codes WHERE route_id = ".nz(trim($_GET['id']), '0')." AND region_id IN (".get_regions().") ";
			try {
				$db->query($sql);
				$msg = 'Success';
			} catch(PDOException $ex) {
				$msg = 'Database Error! '.$ex->getMessage.' === '.$sql.' === ';
			}
			$sql = "DELETE FROM routes_drivers WHERE route_id = ".nz(trim($_GET['id']), '0')." AND region_id IN (".get_regions().") ";
			try {
				$db->query($sql);
				$msg = 'Success';
			} catch(PDOException $ex) {
				$msg = 'Database Error! '.$ex->getMessage.' === '.$sql.' === ';
			}
			echo json_encode(array('result' => $msg));
			return;
			break;
	endswitch;
?>