<?php
//initilize the page


//require UI configuration (nav, ribbon, etc.)
require_once("connect.php");
require_once("functions.php");
include_once("classes/ClassLoader.php");
function getSQLConnection(){
	global $con;
	return $con;
}
$function = $_REQUEST['f'];
switch($function){
	case "getYears":
		getYears();
		break;
	case "getMakes":
		getMakes($_REQUEST['year']);
		
		break;
	case "getModels":
		getModels($_REQUEST['year'], $_REQUEST['make']);
		break;
	case "getSubmodels":
		getSubmodels($_REQUEST['year'], $_REQUEST['make'], $_REQUEST['model']);
		break;
	case "getPartTypes":
		getPartTypes($_REQUEST['year'], $_REQUEST['make'], $_REQUEST['model'], $_REQUEST['engine']);
		break;
	case "getEngines":
		getEngines($_REQUEST['year'], $_REQUEST['make'], $_REQUEST['model'], urldecode($_REQUEST['submodel']));
		break;
	case "getParts":
		getParts($_REQUEST['year'], $_REQUEST['make'], $_REQUEST['model'], urldecode($_REQUEST['engine']), urldecode($_REQUEST['partType']));
		break;
	case "getBuyersGuide":
		getBuyersGuide($_REQUEST['pn']);
		break;
	default:
		die('Function Not Found');
		break;
}
function getYears($returnNoPrint = false){
	$con = getSQLConnection();
	$query = "SELECT DISTINCT(year) FROM APPLICATIONS_CATALOG ORDER BY year DESC;";
	$result = mysqli_query($con, $query);
	
	$return = array();
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$return[] = $row['year'];
	}
	if($returnNoPrint){
		return $return;
	}else{
		echo json_encode($return);
	}
}
function getMakes($year, $returnNoPrint = false){
	
	$con = getSQLConnection();
	$query = "SELECT DISTINCT(make) FROM APPLICATIONS_CATALOG WHERE year = " . mysqli_real_escape_string($con,  $year) . " ORDER BY make ASC;";
	
	$result = mysqli_query($con, $query);
	$return = array();
	
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$return[] = $row['make'];
	}
	if($returnNoPrint){
		return $return;
	}else{
		echo json_encode($return);
	}
}
function getModels($year, $make, $returnNoPrint = false){
	$con = getSQLConnection();
	$query = "SELECT DISTINCT(model) FROM APPLICATIONS_CATALOG WHERE `year` = " . mysqli_real_escape_string($con,  $year) . " AND `make` = '" . mysqli_real_escape_string($con,$make) . "' ORDER BY `model` ASC;";
	$return = array();
	if($result = mysqli_query($con, $query)){
		while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			$return[] = $row['model'];
		}
		if($returnNoPrint){
			return $return;
		}else{
			echo json_encode($return);
		}
	}else{
		$return['result'] = "error";
		$return['error'] = mysqli_error($con);
		if($returnNoPrint){
			return $return;
		}else{
			echo json_encode($return);
		}
	}
}
function getPartTypes($year, $make, $model, $engine, $returnNoPrint = false){
	$con = getSQLConnection();
	if($engine == "All" || $engine === "" || is_null($engine)){
		$engine = "%";
	}
	$query = "SELECT DISTINCT(parttype), CategoryName FROM (APPLICATIONS_CATALOG, INVENTORY) LEFT JOIN INTERCHANGE ON(part_number = INTERCHANGE.Reference) LEFT JOIN PCDB USING(partType) WHERE (REPLACE(INVENTORY.SKU,'-','') LIKE REPLACE(CONCAT(APPLICATIONS_CATALOG.part_number, ''),'-','') OR REPLACE(INVENTORY.INTERCHANGE,'-','') LIKE REPLACE(CONCAT(APPLICATIONS_CATALOG.part_number, ''),'-','')) AND INVENTORY.Group LIKE CONCAT(APPLICATIONS_CATALOG.groupcode, '%') AND INVENTORY.Line LIKE CONCAT(APPLICATIONS_CATALOG.linecode, '%')  AND year = " . mysqli_real_escape_string($con, $year) . " AND make = '" . mysqli_real_escape_string($con, $make) . "' AND model = '" . mysqli_real_escape_string($con, $model) . "' AND (IF((liter NOT LIKE ''),CONCAT(liter, \"L \", IFNULL(blocktype,''), cylinders),'All') LIKE '$engine' OR IF((liter NOT LIKE ''),CONCAT(liter, \"L \", IFNULL(blocktype,''), cylinders),'All') LIKE 'All') AND haveit = 1 GROUP BY parttype ORDER BY CategoryName, parttype ASC;";
	//
	if(isset($_REQUEST['test'])){
		echo $query;	
	}
	$result = mysqli_query($con, $query);
	$return = array();
	
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$object = new stdClass();
		$object->Category =$row['CategoryName'];
		$object->Name= $row['CategoryName'] . " - " . $row['parttype'];
		$object->Value= $row['parttype'];
		$return[] = $object;
	}
	if($returnNoPrint){
		return $return;
	}else{
		echo json_encode($return);
	}
}
function getEngines($year, $make, $model, $submodel, $returnNoPrint = false){
	$con = getSQLConnection();
	if($submodel == "All"){
		$submodel = "%";
	}
	//$query = "SELECT DISTINCT(enginebase) FROM APPLICATIONS_CATALOG WHERE year = " . mysqli_real_escape_string($con, $year) . " AND make = '" . mysqli_real_escape_string($con, $make) . "' AND model = '" . mysqli_real_escape_string($con, $model) . "' AND submodel LIKE '" . mysqli_real_escape_string($con, $submodel) . "' ORDER BY enginebase ASC;";
	$query = "SELECT DISTINCT(IF((liter NOT LIKE ''),CONCAT(liter, \"L \", IFNULL(blocktype,''), cylinders),'All')) enginebase FROM APPLICATIONS_CATALOG WHERE year = " . mysqli_real_escape_string($con, $year) . " AND make = '" . mysqli_real_escape_string($con, $make) . "' AND model = '" . mysqli_real_escape_string($con, $model) . "' AND submodel LIKE '" . mysqli_real_escape_string($con, $submodel) . "' ORDER BY (IF(liter NOT LIKE '',CONCAT(liter, \"L \", blocktype, cylinders),'All')) ASC;";
	if(isset($_REQUEST['test'])){
		echo $query;	
	}
	
	$result = mysqli_query($con, $query);
	$return = array();
	
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$return[] = $row['enginebase'];
	}
	if($returnNoPrint){
		return $return;
	}else{
		echo json_encode($return);
	}
}

function getParts($year, $make, $model, $engine, $partType, $returnNoPrint = false){
	global $customer;
	$con = getSQLConnection();
	$accountNumber = "";
	if($_SESSION['isCustomer']){
		$accountNumber = $_SESSION['AccountNumber'];
	}else{
		$accountNumber = $_SESSION['SalesmanID'];
	}
	$submodel = "%";
	
	//LOG SEARCH REQUEST
	$query = "INSERT INTO CATALOG_LOG (AccountNumber, SearchDate, `year`, `make`, `model`, `engine`, `parttype`) VALUES ('" . $accountNumber . "', NOW(), $year, '$make', '$model', '$engine', '$partType')";
	mysqli_query($con, $query);
	//END LOG
	
	if($engine == "All" || $engine === ""){
		$engine = "%";
	}
	
	$query = "SELECT `INVENTORY`.Line, `INVENTORY`.`Group`,  `INVENTORY`.SKU as part_number, Interchange, GROUP_CONCAT(DISTINCT(position)) position, label,GROUP_CONCAT(DISTINCT(transmissioncontroltype)) transmissioncontroltype ,CONCAT_WS(' ', `Description`, INVENTORY.Notes) as `Description`, SHOPPING_CART.Qty AS OnOrder,  GROUP_CONCAT( parttype) as parttype ,GROUP_CONCAT( DISTINCT(submodel)) as submodel , GROUP_CONCAT( DISTINCT(APPLICATIONS_CATALOG.notes)) as notes, GROUP_CONCAT( DISTINCT(aspiration)) as aspiration, GROUP_CONCAT( DISTINCT(cylinderheadtype)) as cylinderheadtype, GROUP_CONCAT(DISTINCT( enginedesignation)) as enginedesignation, GROUP_CONCAT( engineversion) as engineversion, GROUP_CONCAT( enginevin) as enginevin, GROUP_CONCAT( fueldeliverytype) as fueldeliverytype, INVENTORY.Qty, Cost AS Price, GROUP_CONCAT(DISTINCT(IF((liter NOT LIKE ''),CONCAT(liter, \"L \", IFNULL(blocktype,''), cylinders),'All'))) AS enginebase, GROUP_CONCAT(DISTINCT(drivetype)) as drivetype FROM APPLICATIONS_CATALOG, INVENTORY LEFT JOIN SHOPPING_CART  
 			ON `INVENTORY`.SKU = SHOPPING_CART.SKU 
 				AND INVENTORY.`Group` = SHOPPING_CART.`Group` 
 				AND `INVENTORY`.Line = SHOPPING_CART.Line AND SHOPPING_CART.`AccountNumber` = '" . $accountNumber . "'  WHERE (REPLACE(INVENTORY.SKU,'-','') LIKE REPLACE(CONCAT(APPLICATIONS_CATALOG.part_number, ''),'-','') OR REPLACE(INVENTORY.INTERCHANGE,'-','') LIKE REPLACE(CONCAT(APPLICATIONS_CATALOG.part_number, ''),'-','')) AND INVENTORY.Group LIKE CONCAT(APPLICATIONS_CATALOG.groupcode, '%') AND INVENTORY.Line LIKE CONCAT(APPLICATIONS_CATALOG.linecode, '%') AND year = " . mysqli_real_escape_string($con, $year) . " AND make = '" . mysqli_real_escape_string($con, $make) . "' AND model = '" . mysqli_real_escape_string($con, $model) . "' AND submodel LIKE '" . mysqli_real_escape_string($con, $submodel) . "' AND (IF((liter NOT LIKE ''),CONCAT(liter, \"L \", IFNULL(blocktype,''), cylinders),'All') LIKE '$engine' OR IF((liter NOT LIKE ''),CONCAT(liter, \"L \", IFNULL(blocktype,''), cylinders),'All') LIKE 'All') AND (parttype) LIKE '" . mysqli_real_escape_string($con, $partType) . "' AND haveit = 1 GROUP BY `INVENTORY`.Line, `INVENTORY`.`Group`, `INVENTORY`.SKU ORDER BY make, IF(liter NOT LIKE '',CONCAT(liter, \"L \", blocktype, cylinders),IF(enginebase NOT LIKE '',enginebase,'All')) ASC;";
				
	//$query = "SELECT part_number, CONCAT_WS(',', parttype) as parttype , CONCAT_WS(',', notes) as notes, CONCAT_WS(',', aspiration) as aspiration, CONCAT_WS(',', cylinderheadtype) as cylinderheadtype, CONCAT_WS(',', enginedesignation) as enginedesignation, CONCAT_WS(',', engineversion) as engineversion, CONCAT_WS(',', enginevin) as enginevin, CONCAT_WS(',', fueldeliverytype) as fueldeliverytype, INVENTORY.Qty, Price FROM APPLICATIONS_CATALOG, INVENTORY, PRICE_LIST WHERE APPLICATIONS_CATALOG.part_number = INVENTORY.SKU AND INVENTORY.SKU = PRICE_LIST.SKU AND PRICE_LIST.PriceListCode =  AND year = " . mysqli_real_escape_string($con, $year) . " AND make = '" . mysqli_real_escape_string($con, $make) . "' AND model = '" . mysqli_real_escape_string($con, $model) . "' AND submodel LIKE '" . mysqli_real_escape_string($con, $submodel) . "' AND enginebase LIKE '" . mysqli_real_escape_string($con, $engine) . "' GROUP BY part_number ORDER BY make ASC;";
	if(isset($_REQUEST['test'])){
		echo $query;	
	}
	$result = mysqli_query($con, $query);
	//echo $query;
	$return = array();
	//$return['query'] = $query;
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		//echo $row['part_number'];
		$part = new Item($row['Group'], $row['Line'], $row['part_number']);
		$part->PartType = $row['parttype'];
		$part->OnOrder = is_null($row['OnOrder'])?"":$row['OnOrder'];
		$part->Label = $row['label'];
		if($engine == '%'){
			$engine = "All Engines";
		}
		$part->Description = $row['Description'] . "<br><small><i>Fits $year $make $model</i></small>";
		$part->AvailableQty = $part->AvailableQty>50?"50+":$part->AvailableQty;
		//$part->Attributes = array();
		$attributeCode = "";
		$attribute = new stdClass();
		$attribute->Name = "<b>Engine</b>";
		
		$attribute->Value = $row['enginebase']?$row['enginebase']:"All";
		$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		if(str_ireplace(",","",$row['submodel']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Submodel</b>";
			$attribute->Value = $row['submodel'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['notes']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Notes</b>";
			$attribute->Value = $row['notes'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
			
		}
		if(str_ireplace(",","",$row['cylinderheadtype']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Head Type</b>";
			$attribute->Value = $row['cylinderheadtype'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
			
		}
		if(str_ireplace(",","",$row['aspiration']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Aspiration</b>";
			$attribute->Value = $row['aspiration'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
			
		}
		if(str_ireplace(",","",$row['enginedesignation']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Engine Designation</b>";
			$attribute->Value = $row['enginedesignation'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['engineversion']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Engine Version</b>";
			$attribute->Value = $row['engineversion'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['enginevin']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>VIN</b>";
			$attribute->Value = $row['enginevin'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['fueldeliverytype']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Fuel Delivery</b>";
			$attribute->Value = $row['fueldeliverytype'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['fueltype']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Fuel Type</b>";
			$attribute->Value = $row['fueltype'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['rearbraketype']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Rear Beake Type</b>";
			$attribute->Value = $row['rearbraketype'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		
		if(str_ireplace(",","",$row['transmissioncontroltype']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Transmission</b>";
			$attribute->Value = $row['transmissioncontroltype'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['drivetype']) != ""){
			$attribute = new stdClass();
			$attribute->Name = "<b>Drive Type</b>";
			$attribute->Value = $row['drivetype'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		if(str_ireplace(",","",$row['position']) != "" && $row['position'] != "N/A"){
			
			$attribute = new stdClass();
			$attribute->Name = "<b>Position</b>";
			$attribute->Value = $row['position'];
			$attributeCode .= $attribute->Name . ": " . $attribute->Value . "<br />";
		}
		($part->Attributes = $attributeCode);
		array_push($return, $part);
		
	}
	if($returnNoPrint){
		return $return;
	}else{
		echo json_encode($return);
	}
}
function getBuyersGuide($part_number, $returnNoPrint = false){
	$con = getSQLConnection();
	$part_number = mysqli_real_escape_string($con,$part_number);
	//$query = "SELECT * FROM APPLICATIONS_CATALOG WHERE part_number = '$part_number'";
	$query = "SELECT IF(MIN(`year`) = MAX(`year`), MAX(`year`), CONCAT(MIN(`year`), \"-\", MAX(`year`))) as Years, make AS Make, model as Model, CONCAT_WS(\" \", enginebase, cylinderheadtype) AS `EngineType` FROM APPLICATIONS_CATALOG WHERE part_number LIKE '$part_number' GROUP BY make, model, enginebase, cylinderheadtype ORDER BY make, model, MIN(year);";
	$return = array();
	if($result = mysqli_query($con, $query)){
		while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
			array_push($return,$row);
		}	
	}else{
		$return['error'] = mysqli_error($con);
		$return['query'] = $query;
	}
	
	if($returnNoPrint){
		return $return;
	}else{
		echo json_encode($return);
	}
	
}
?>