<?php require_once("connect.php");
$return=array("request"=>"");
$esc=esc_all($_POST);

function img_url($id,$img){
	return "http://api.pinpoint.promo/products/image/".$id."/".urlencode(urlencode(urlencode($img)));
}
function images_url($id, $name, $files) {
	$r = array();
	if ($files && count($files) > 0) {
		for ($i = 0; $i < count($files); $i++) {
			array_push($r, "http://images.pinpoint.promo/" . $id . "/" . str_replace(" ", "-", str_replace("  ", " ", preg_replace("/[^\w- ]+/", "", strtolower($name)))) . "-" . ($i + 1) . "." . pathinfo($files[$i])["extension"]);
		}
	}
	return $r;
}

function thumbs_url($id, $name, $files) {
	$r = array();
	if ($files && count($files) > 0) {
		for ($i = 0; $i < count($files); $i++) {
			array_push($r, "http://images.pinpoint.promo/" . $id . "/t-" . str_replace(" ", "-", str_replace("  ", " ", preg_replace("/[^\w- ]+/", "", strtolower($name)))) . "-" . ($i + 1) . "." . pathinfo($files[$i])["extension"]);
		}
	}
	return $r;
}
function dl_url($id,$dl){
	return "http://api.pinpoint.promo/products/download/".$id."/".$dl;
}
function download_links($id){
	$links=array();
	$prod_dir=glob($_SERVER["DOCUMENT_ROOT"]."/admin/products/all/".$id." -*",GLOB_ONLYDIR);
	if(array_key_exists(0,$prod_dir)){
		$downloads=glob($prod_dir[0]."/[dD][oO][wW][nN][lL][oO][aA][dD][sS]");
		//usort($files,function($a,$b){return filemtime($b)-filemtime($a);});
		if(array_key_exists(0,$downloads)){
			$files=glob($downloads[0]."/*");
		}
	}
	if($files){foreach($files as $f){
		array_push($links, dl_url($id,pathinfo($f)["basename"]));
	}}
	return $links;
}
function template_links($id){
	$links=array();
	$prod_dir=glob($_SERVER["DOCUMENT_ROOT"]."/admin/products/all/".$id." -*",GLOB_ONLYDIR);
	if(array_key_exists(0,$prod_dir)){
		$files=glob($prod_dir[0]."/*.pdf");
	}
	if($files){foreach($files as $f){
		array_push($links, dl_url($id,pathinfo($f)["basename"]));
	}}
	return $links;
}

function make_params($allCat){
	for($i=0;$i<count($allCat);$i++){
		$allCat[$i] = str_replace('-',' ',$allCat[$i]);
		$var.= $allCat[$i].'|';
	}
	$var = rtrim($var,'|');
	return $var;
}
function array_sort($array, $on, $order=SORT_DESC){

    $new_array = array();
    $sortable_array = array();

    if (count($array) > 0) {
        foreach ($array as $k => $v) {
            if (is_array($v)) {
                foreach ($v as $k2 => $v2) {
                    if ($k2 == $on) {
                        $sortable_array[$k] = $v2;
                    }
                }
            } else {
                $sortable_array[$k] = $v;
            }
        }

        switch ($order) {
            case SORT_ASC:
                asort($sortable_array);
                break;
            case SORT_DESC:
                arsort($sortable_array);
                break;
        }

        foreach ($sortable_array as $k => $v) {
            $new_array[$k] = $array[$k];
        }
    }

    return $new_array;
}
function array_sort_asc($array, $on, $order=SORT_ASC){

    $new_array = array();
    $sortable_array = array();

    if (count($array) > 0) {
        foreach ($array as $k => $v) {
            if (is_array($v)) {
                foreach ($v as $k2 => $v2) {
                    if ($k2 == $on) {
                        $sortable_array[$k] = $v2;
                    }
                }
            } else {
                $sortable_array[$k] = $v;
            }
        }

        switch ($order) {
            case SORT_ASC:
                asort($sortable_array);
                break;
            case SORT_DESC:
                arsort($sortable_array);
                break;
        }

        foreach ($sortable_array as $k => $v) {
            $new_array[$k] = $array[$k];
        }
    }

    return $new_array;
}


if(count($_POST)> 0){
	if($_POST["page"] == 1){
		$limtQ = 0;
	}else{
		$limtQ = 18*($_POST["page"]-1);
	}
    $return["request"]="Filter products";
    $sql="SELECT id,name,reference,category,img,`quantity`,`sell-price` AS cost,`web-order`,`web-colour` FROM `product-list` WHERE active='1'";
    
    /*******categories filter********** */
    if(isset($_POST["categories"]) && $_POST["categories"] != ''){
		$allCat =explode("_",$_POST["categories"]);
		for($numb=0;$numb<count($allCat);$numb++){
                if($allCat[$numb] == 'lanyards-security'){
                    $allCat[$numb] = 'Lanyards & Security';
                }
                if($allCat[$numb] == 'mobile-accessories'){
                    $allCat[$numb] = 'Mobile Accessories';
                }
                if($allCat[$numb] == 'trolley-coins'){
                    $allCat[$numb] = 'Trolley Coins';
                }
                if($allCat[$numb] == 'uk-products'){
                    $allCat[$numb] = 'UK Products';
                }
		}
		$var = '';
		$var = make_params($allCat);
            $sql.=" AND category REGEXP'($var)'";
    }

    /**********leadtime filter*********** */
    if(isset($_POST["leadtime"]) && $_POST["leadtime"] != '' ){
		$tempArr = array();
		$allLeadTime =explode("_",$_POST["leadtime"]);
		$temp = 0;
		$leadArr = array();
		if(count($allLeadTime) == 1){
			if($allLeadTime[0] == '1-10'){
				$sql.=" AND `web-leadtime` >= 1 ";
			    $sql.=" AND `web-leadtime` <=10 ";
			}else{
				$allLeadTime[0] = 7*$allLeadTime[0];
				$sql.=" AND `web-leadtime` <=".$allLeadTime[0]."";
			}
		}else{
			for($w=0;$w<count($allLeadTime);$w++){
				if($allLeadTime[$w] != '1-10'){
					array_push($leadArr,$allLeadTime[$w]);
				}
		    }
            $leadtimeVal = max($leadArr);
            $leadtimeVal = 7*$leadtimeVal;
            $sql.=" AND `web-leadtime` <=".$leadtimeVal."";
		}
	}
	//$return["beforesql"]= $sql;
    /**********extras filter**************** */
	if(isset($_POST["extras"]) && $_POST["extras"] != ''){
		$extraresult=mysqli_query($con,$sql) or die(mysqli_error($con));
		if(!$extraresult){$return["error"]="500";$return["message"]="Server connection failed.";}
		else if(mysqli_num_rows($extraresult)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
		else if($extraresult){
                $extraIdArray = array();
                while($row = mysqli_fetch_assoc($extraresult)){
                    array_push($extraIdArray, $row["id"]);
                }
                $idArray = array();
                $m= 0;
                foreach($extraIdArray as $xtraData){
                    $m++;
                    $query="SELECT id,`web-keyfeatures` FROM `product-list` WHERE id =".$xtraData."";
                    
                    $rs=mysqli_query($con,$query) or die(mysqli_error($con));
                    if(mysqli_num_rows($rs) != 0){
                        while($rowResult = mysqli_fetch_assoc($rs)){
                            $webfeatures= $rowResult["web-keyfeatures"];
                            $keyfeatures = explode("~",$webfeatures);
                            //$return['a']= $keyfeatures;
							$allCat =explode("_",$_POST["extras"]);
							if(count($allCat) ==1){
								if(count($keyfeatures) >0){
									//if(count($keyfeatures) == count($allCat)){
										//$countNum =0;
										for($number = 0;$number<count($allCat);$number++){
											$allCat[$number] = str_replace("-", " ",$allCat[$number]);
											for($keynumber = 0; $keynumber <count($keyfeatures) ;$keynumber++){
												$keyfeatures[$keynumber] = strtolower($keyfeatures[$keynumber]);
												if($allCat[$number] == $keyfeatures[$keynumber]){
													array_push($idArray, $rowResult["id"]);
												}
												// if(count($allCat) == $countNum){
													
												// }
											}
										}
									//}
								}
							}else{
								if(count($keyfeatures) >0){
									//if(count($keyfeatures) == count($allCat)){
										$countNum =0;
										for($number = 0;$number<count($allCat);$number++){
											$allCat[$number] = str_replace("-", " ",$allCat[$number]);
											for($keynumber = 0; $keynumber <count($keyfeatures) ;$keynumber++){
												$keyfeatures[$keynumber] = strtolower($keyfeatures[$keynumber]);
												if($allCat[$number] == $keyfeatures[$keynumber]){
													$countNum++;
												}
												if(count($allCat) == $countNum){
													array_push($idArray, $rowResult["id"]);
												}
											}
										//}
									}
								}
							}
                            //$return['b']= $allCat;
                            
                        }
                    }
                }
		}
		$extrasId = implode(",",$idArray);
		if($extrasId != ""){
			//$sql="SELECT `sell-price` AS cost FROM `product-list` WHERE `id` IN($extrasId)";
			$sql="SELECT id,name,reference,category,img,quantity,`sell-price` AS cost,`web-order`,`web-colour` FROM `product-list` WHERE `id` IN($extrasId)";
		}else{
			$sql = '';
			$return["error"]="000";$return["message"]="No results found, check request.";
		}
    }
	
	/**********colour filter**************** */
	if(isset($_POST["colours"]) && $_POST["colours"] != ""){
		$sql.=" AND `web-colour` IS NOT NULL ";
		$colorArray = array();
		$result=mysqli_query($con,$sql) or die(mysqli_error($con));
            if(!$result){$return["error"]="500";$return["message"]="Server connection failed.";}
            else if(mysqli_num_rows($result)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
            else if($result){
					while($row = mysqli_fetch_assoc($result)){
						$webcol = explode('~',$row["web-colour"]);
						$allColours = explode('_',$_POST["colours"]);

								if(count($webcol) > 0){
									//if(count($webcol) == count($allColours)){
									$colNum =0;
										foreach($webcol as $dbColour){
											$dbColour = strtolower($dbColour);
											
											if($dbColour == 'pantone matched' && !in_array('full-colour',$allColours)){
												if(!(in_array($row['id'] ,$colorArray ))){
													array_push($colorArray,$row['id']);
												}
											}
											foreach($allColours as $givenColour){
												$givenColour = strtolower($givenColour);
												$givenColour = str_replace('-',' ',$givenColour);
												if($givenColour == $dbColour){
													if(!(in_array($row['id'] ,$colorArray ))){
														array_push($colorArray,$row['id']);
													}
												}
											}
										// 	if(count($webcol) == count($allColours)){
										// 	foreach($allColours as $givenColour){
										// 		$dbColour = strtolower($dbColour);
										// 		$givenColour = strtolower($givenColour);
										// 		$givenColour = str_replace('-',' ',$givenColour);
										// 		if($givenColour == $dbColour){
										// 			$colNum++;
										// 		}
										// 	}
										// 	if(count($allColours) == $colNum){
										// 		if(!(in_array($row['id'] ,$colorArray ))){
										// 			array_push($colorArray,$row['id']);
										// 		}
										// 	}
										// }
										}
									//}
								}
						//}
					}
					
				}
		$extrasColId = implode(",",$colorArray);
		if($extrasColId != ""){
				$sql="SELECT id,name,reference,category,img,`sell-price` AS cost,`web-order` FROM `product-list` WHERE `id` IN($extrasColId)";
			}else{
				$sql = '';
				$return["error"]="000";$return["message"]="No results found, check request.";
		}
	}
	$finalSql = $sql;
    /**********moq filter********************* */ 
	if($finalSql != ''){
		if(isset($_POST["email"]) && $_POST["email"] != ""){
			$arrayId = array();
			$allCustomers ="select id,pricelist,`sales-persons-email` AS email from customers";
			$cusresult=mysqli_query($con,$allCustomers) or die(mysqli_error($con));
			if(!$cusresult){$return["error"]="500";$return["message"]="Server connection failed.";}
            else if(mysqli_num_rows($cusresult)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
            else if($cusresult){
				while($rowRes = mysqli_fetch_assoc($cusresult)){
					$email = $rowRes["email"];
					$allEmail = explode('~',$email);
					foreach($allEmail AS $dataRow){
						$dbemail = strtolower($dataRow);
						$givenEmail = strtolower($_POST["email"]);
						if($dbemail == $givenEmail){
							$rowRes["pricelist"] = strtolower($rowRes["pricelist"]);
							array_push($arrayId,$rowRes["pricelist"]);
						}
					}
				}
			}
			$pricelist = $arrayId[0];
		}
		//$return["sql"]=$finalSql;
        if(isset($_POST["moq"]) && isset($_POST["budget"]) && $_POST["moq"] != '' && $_POST["budget"] != ''){
            $moqFilter = explode('_',$_POST["moq"]);
            $tempArr1= array();
            $tempQArray = array();
            $budgetFilter = explode('_',$_POST["budget"]);
            $result=mysqli_query($con,$finalSql) or die(mysqli_error($con));
            if(!$result){$return["error"]="500";$return["message"]="Server connection failed.";}
            else if(mysqli_num_rows($result)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
            else if($result){
				
                $return["response"]=array();
                while($row = mysqli_fetch_assoc($result)){
                    $tempQArray= [];
                    $min= 0;
					$max = 0;
					$quantityArray = explode('!',$row["quantity"]);
					foreach($quantityArray as $quantArr){
						$innerQuantity = explode('~',$quantArr);
						foreach($innerQuantity as $inerrow1){
							$inner1Quantity = explode(':',$inerrow1);
							foreach($inner1Quantity as $innerrow2){
								array_push($tempQArray, $innerrow2);
							}
						}
					}
					$tempQArray= array_unique($tempQArray);
                    $tempNumb1 = 0;
                    foreach($tempQArray as $rowVall){
                            if($rowVall >= $moqFilter[0] && $tempNumb1 == 0){
                                $tempNumb1 =1;
                                $min = $rowVall;
                            }
                            if($rowVall <= $moqFilter[1]){
                                $max = $rowVall;
                            }
					}
					if($min != 0 && $max!= 0){
						if($min >=$moqFilter[0] && $max <= $moqFilter[1]){
							if(!(in_array($row['id'] ,$tempArr1 ))){
									array_push($tempArr1,$row['id']);
							}
						}
					}
                    
				}
				
			}
			$return["moq"]= $tempArr1;
            if(count($tempArr1) > 0){
                $pArr = array();
                $var = implode(",",$tempArr1);
                $sql="SELECT `id`,`quantity`,`sell-price` AS cost,pricelist FROM `product-list` WHERE `id` IN($var)";
				
				$result=mysqli_query($con,$sql) or die(mysqli_error($con));
                if(!$result){$return["error"]="500";$return["message"]="Server connection failed.";}
                else if(mysqli_num_rows($result)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
                else if($result){
                    $tempArrt = [];
                    $return["response"]=array();
                    while($row = mysqli_fetch_assoc($result)){
						$expList = explode('~',$row["pricelist"]);
						//$return["as"]= $_POST["email"];
						$flag = 0;
						$flagVal = 0;
						foreach($expList as $rowlist){
							$rowlist = strtolower($rowlist);
							if($rowlist == $pricelist){
								$flag =1;
								$newVal = $flagVal;
							}
							$flagVal++;
						}
						$tempQArray =[];
						
						$quantityArray = explode('!',$row["quantity"]);
						foreach($quantityArray as $quantArr){
							$innerQuantity = explode('~',$quantArr);
							foreach($innerQuantity as $inerrow1){
								$inner1Quantity = explode(':',$inerrow1);
								foreach($inner1Quantity as $innerrow2){
									array_push($tempQArray, $innerrow2);
								}
							}
						}
						$tempNumb = 0;
						foreach($tempQArray as $rowVal){
							if($rowVal >= $moqFilter[0] && $tempNumb == 0){
								$tempNumb =1;
								$first = $rowVal;
							}
							if($rowVal <= $moqFilter[1]){
								$last = $rowVal;
							}
						}
						$firstkey = array_search($first,$tempQArray);
						$lastkey = array_search($last,$tempQArray);
						$pArr = [];
						$costArray = explode('!',$row["cost"]);
						//$return["flag"]= $flag;
						if($flag == 1){
								$innerCost = explode('~',$costArray[$newVal]);
								foreach($innerCost as $innerCost1){
									$innerCost2 = explode(':',$innerCost1);
									foreach($innerCost2 as $innerCost3){
										array_push($pArr,$innerCost3);
									}
								}
								
						}else{
							
							foreach($costArray as $costArr){
								$innerCost = explode('~',$costArr);
								foreach($innerCost as $innerCost1){
									$innerCost2 = explode(':',$innerCost1);
									foreach($innerCost2 as $innerCost3){
										array_push($pArr,$innerCost3);
									}
								}
							}
						}
							$newCostArray = array();
							for($y=$firstkey;$y<=$lastkey;$y++){
								array_push($newCostArray, $pArr[$y]);
							} 
							$min=min($newCostArray);
							$max= max($newCostArray);
							if($budgetFilter[0] >=0  && $budgetFilter[1] != 200){
								if($budgetFilter[0]<=$min && $min<=$budgetFilter[1]) 
									{
										if(!(in_array($row['id'] ,$tempArrt ))){
													array_push($tempArrt,$row['id']);
										}
									}
							}else{
								if(!(in_array($row['id'] ,$tempArrt ))){
									array_push($tempArrt,$row['id']);
								}
							}
                    }
				}
                if(count($tempArrt) > 0){
                    $var1= implode(",",$tempArrt);
                    $sql="SELECT id,name,reference,category,img,`sell-price` AS cost,`web-order` FROM `product-list` WHERE `id` IN($var1)";
                }else{
                    $sql = '';
                    $return["error"]="000";$return["message"]="No results found, check request.";
                }
                
            }else{
                $sql = '';
                $return["error"]="000";$return["message"]="No results found, check request.";
            }
        }
    }

    /*********sorting******************************* */
    if(isset($_POST["sort"]) && $_POST["sort"] != ''){
		if($finalSql != ''){
			 if($_POST["sort"] == 'low'){
				$newArray = array();
				$sellArray = array();
				$tempSellArray = array();
				$finalArray = array();
				$chkArr = array();
				if(isset($var1)){
					$sql="SELECT id,`sell-price` AS cost FROM `product-list` WHERE `id` IN($var1)";
				}else{
					$sql="SELECT id,`sell-price` AS cost FROM `product-list`";
				}
				$sortresult=mysqli_query($con,$sql) or die(mysqli_error($con));
				if(!$sortresult){$return["error"]="500";$return["message"]="Server connection failed.";}
				else if(mysqli_num_rows($sortresult)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
				else if($sortresult){
					while($newrow = mysqli_fetch_assoc($sortresult)){
						$tempSellArray= [];
						$sellPrice = explode(":",$newrow["cost"]);
							for($num=0;$num<count($sellPrice);$num++){
									if(is_numeric($sellPrice[$num])){
										array_push($tempSellArray,$sellPrice[$num]);
									}else{
										$innersellPrice = explode('~',$sellPrice[$num]);
											for($l=0;$l<count($innersellPrice);$l++){
												$sellP = explode('!',$innersellPrice[$l]);
												if(count($sellP) >1){
													for($q= 0;$q<count($sellP);$q++){
														array_push($tempSellArray,$sellP[$q]);
													}
												}else{
													array_push($tempSellArray,$innersellPrice[$l]);
												}
											}
									}
							}
						$minVal= min($tempSellArray);
						$finalArray[$newrow['id']] = $minVal;
						asort($finalArray);
						$allkeys = array_keys($finalArray);
					}
					$idString = implode(",",$allkeys);
					$sql="SELECT id,name,reference,category,img,`sell-price` AS cost,`web-order` FROM `product-list` WHERE `id` IN ($idString) ORDER BY FIELD(id, $idString)";	
				}
			}else if($_POST["sort"] == 'high'){
				$newArray = array();
				$sellArray = array();
				$tempSellArray = array();
				$finalArray = array();
				$chkArr = array();
				if(isset($var1)){
					$sql="SELECT id,`sell-price` AS cost FROM `product-list` WHERE `id` IN($var1)";
				}else{
					$sql="SELECT id,`sell-price` AS cost FROM `product-list`";
				}
				$sortresult=mysqli_query($con,$sql) or die(mysqli_error($con));
				if(!$sortresult){$return["error"]="500";$return["message"]="Server connection failed.";}
				else if(mysqli_num_rows($sortresult)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
				else if($sortresult){
					while($newrow = mysqli_fetch_assoc($sortresult)){
						$tempSellArray= [];
						$sellPrice = explode(":",$newrow["cost"]);
							for($num=0;$num<count($sellPrice);$num++){
									if(is_numeric($sellPrice[$num])){
										array_push($tempSellArray,$sellPrice[$num]);
									}else{
										$innersellPrice = explode('~',$sellPrice[$num]);
											for($l=0;$l<count($innersellPrice);$l++){
												array_push($tempSellArray,$innersellPrice[$l]);
											}
									}
							}
						$maxVal= max($tempSellArray);
						$finalArray[$newrow['id']] = $maxVal;
						arsort($finalArray);
						$allkeys = array_keys($finalArray);
					}
					$idString = implode(",",$allkeys);
					$sql="SELECT id,name,reference,category,img,`sell-price` AS cost,`web-order` FROM `product-list` WHERE `id` IN ($idString) ORDER BY FIELD(id, $idString) ";	
				}
			}
		}
	}
}

/*************get all product names******************* */
if(!empty($_GET["names"])){
	$newArr = array();
	$unique_array = array();
	$return["request"]="list all names";
	$sql="SELECT name,reference,category FROM `product-list` WHERE active='1'";
	$result=mysqli_query($con,$sql) or die(mysqli_error($con));
	if(!$result){$return["error"]="500";$return["message"]="Server connection failed.";}
	else if(mysqli_num_rows($result)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
	else if($result){
		$return["response"]=array();
		while($row = mysqli_fetch_assoc($result)){
			$category = explode('~',$row["category"]);
			
			if($row["name"]){
				if(!in_array($row["name"],$unique_array)){
					array_push($unique_array,$row["name"]);
					array_push($newArr,array('name' => $row["name"], 'reference' => $row["reference"] , 'category'=> $category[0]));
				}
			}
		}
		$return["response"] =$newArr;
	}
	echo json_encode($return);
	exit;
}


/*****json format conversion********************** */
$finalSql = $sql;
$tempqry =mysqli_query($con,$finalSql);

$return['totalproducts'] =mysqli_num_rows($tempqry);
if($_POST["sort"] == '' && $finalSql != ''){
	$orderResult=mysqli_query($con,$finalSql) or die(mysqli_error($con));
	if(!$orderResult){$return["error"]="500";$return["message"]="Server connection failed.";}
	else if(mysqli_num_rows($orderResult)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
	else if($orderResult){
		if($_POST["category"] == ''){
			$weborderArray = array();
			while($rowData = mysqli_fetch_assoc($orderResult)){
				$expWebOrder = explode('~',$rowData["web-order"]);
				array_push($weborderArray,array('id' =>$rowData["id"],'order' =>  $expWebOrder[0]));
			}
		}else{
			$weborderArray = array();
			while($rowData = mysqli_fetch_assoc($orderResult)){
				$expCat = explode('_',$_POST["categories"]);
				$testCat = $expCat[0];
				if($testCat == 'lanyards-security'){$testCat = 'Lanyards & Security';}
				if($testCat == 'mobile-accessories'){$testCat = 'Mobile Accessories';}
				if($testCat == 'trolley-coins'){$testCat = 'Trolley Coins';}
				if($testCat == 'uk-products'){$testCat = 'UK Products';}
				$expDbCat =explode('~',$rowData["category"]);
				$tempVal = 0;
				foreach($expDbCat as $db){
					$db = strtolower($db);
					if($db == $testCat){
						$arrNum = $tempVal;
					}
					$tempVal++;
				}
				$expWebOrder = explode('~',$rowData["web-order"]);
				array_push($weborderArray,array('id' =>$rowData["id"],'order' =>  $expWebOrder[$arrNum]));
			}
		}
		
		$weborderArray = array_sort($weborderArray, 'order', SORT_ASC);
		$finalOdrArray = array();
		foreach($weborderArray as $lrow){
			array_push($finalOdrArray,$lrow["id"]);
		}
		if($_POST["page"] == 1){
			$finalOdrArray =array_chunk($finalOdrArray,24);
		}else{
			$finalOdrArray =array_chunk($finalOdrArray,18);
		}	
		
		if(count($finalOdrArray) == 1){
			$return['nextpage']= '';
			$orderVar = implode(",",$finalOdrArray[0]);
		}else{
			if($_POST["page"] < count($finalOdrArray)){
				$return['nextpage']= $_POST["page"]+1;
			}else{
				$return['nextpage']= '';
			}
			$nxt =$_POST["page"]-1;
			$orderVar = implode(",",$finalOdrArray[$nxt]);
		}
		$exp =explode(',',$orderVar);
		//$return["exp"]=$exp[0];
		if($exp[0] == 165 && $exp[1] == 1){
			$exp[0] = 1;
			$exp[1] = 165;
		}
		$orderVar = implode(",",$exp);
		$finalSql="SELECT id,name,reference,category,img,`sell-price` AS cost,`web-order` FROM `product-list` WHERE `id` IN ($orderVar) ORDER BY FIELD(id, $orderVar)";
	} 
}


// $pageVal= ceil(mysqli_num_rows($tempqry) / 18);
// if($_POST["page"] >= $pageVal){
// 	$return['nextpage']= '';
// }else{
// 	$return['nextpage']= $_POST["page"]+1;
// }
if($con && strlen($finalSql)>5){
	if($_POST["sort"] == 'A-Z' && $_POST["sort"] != ''){
		$finalSql.=" ORDER BY name ASC";
	}else if($_POST["sort"] == 'Z-A' && $_POST["sort"] != ''){
		$finalSql.=" ORDER BY name DESC";
	}
	//$finalSql.= "limit ".$limtQ.",18";
	//$return["final"]=$finalSql;
	$printed=[];
	$result=mysqli_query($con,$finalSql) or die(mysqli_error($con));
	if(!$result){$return["error"]="500";$return["message"]="Server connection failed.";}
	else if(mysqli_num_rows($result)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
	else if($result){
		$return["response"]=array();
		while($row = mysqli_fetch_assoc($result)){
			
			// hardcode - if one of duped products for seperate pricelist, find by name then add types and prices to 'merge' property
			$hasDupe=array("SEB","HEB","MRB","PWEB");
			if ($esc["pricelist"] && array_search($row["reference"], $hasDupe) !== false) {
				$merge_sql="SELECT name, reference, pricelist, quantity, types, `sell-price`, `tool-charge`, `carton-charge` FROM `product-list` WHERE name='{$row["name"]}' AND reference IS NULL";
				$merge_res=mysqli_query($con,$merge_sql);
				if($merge_res){
					while($merge_row = mysqli_fetch_assoc($merge_res)){
						$row["isDupe"]="inWhile";
						if($merge_row["sell-price"]){
							$pl_i=array_search($esc["pricelist"]?strtolower($esc["pricelist"]):"pinpoint",explode("~",strtolower($merge_row["pricelist"])));
							if (!$pl_i) $pl_i=array_search("Pinpoint",explode("~",$merge_row["pricelist"]));
							if (!$pl_i) $pl_i=0;
							$merge_row["this-pricelist"]=explode("~",$merge_row["pricelist"])[$pl_i];
							if($pl_i!==false){
								$merge_row["quantity"]=explode("!",$merge_row["quantity"])[$pl_i];
								$merge_row["sell-price"]=explode("!",$merge_row["sell-price"])[$pl_i];
								$merge_row["tool-charge"]=explode("!",$merge_row["tool-charge"])[$pl_i];
								$merge_row["carton-charge"]=explode("!",$merge_row["carton-charge"])[$pl_i];
								$merge_row["prices"]=[];
								$type_i=0;
								foreach(explode("~",$merge_row["types"]) as $type){
									$merge_row["prices"][$type]=["main"=>[]];
									$quan_i=0;
									foreach(explode(":",explode("~",$merge_row["quantity"])[$type_i]) as $quan) {
										$merge_row["prices"][$type]["main"][$quan] = explode(":",explode("~",$merge_row["sell-price"])[$type_i])[$quan_i];
										$quan_i++;
									}
									$merge_row["prices"][$type]["setup"]=explode(":",explode("~",$merge_row["tool-charge"])[$type_i]);
									$merge_row["prices"][$type]["carriage"]=explode(":",explode("~",$merge_row["carton-charge"])[$type_i]);
									$type_i++;
								}
								unset($merge_row["types"]);
								unset($merge_row["pricelist"]);
								unset($merge_row["quantity"]);
								unset($merge_row["sell-price"]);
								unset($merge_row["tool-charge"]);
								unset($merge_row["carton-charge"]);
							}
						}
						$row["merge"]=$merge_row;
					}
				}
			}

			if(!empty($_POST["categories"])){
						$row["web-order"]=explode("~",$row["web-order"])[0];
			}
	
			if(!in_array(str_replace(" ","",strtolower($row["name"])),$printed)){
				array_push($printed,str_replace(" ","",strtolower($row["name"])));
				
				if($row["img"]) $row["img"]=img_url($row["id"],$row["img"]);
				// gallery images
				$product_dir = glob("./" . $row["id"] . " - *", GLOB_ONLYDIR);
				if (array_key_exists(0, $product_dir)) {
					$image_files = glob($product_dir[0] . "/images/*.{jpeg,jpg,png,gif}", GLOB_BRACE);
					usort($image_files, function ($a, $b) {
						$a = pathinfo($a)["filename"];
						$b = pathinfo($b)["filename"];
						return (is_numeric($a) && is_numeric($b) ? $a - $b : strcmp($a, $b));
					});
					$row["images"] = images_url($row["id"], $row["name"], $image_files);
					$row["images-thumbs"] = thumbs_url($row["id"], $row["name"], $image_files);
				}
				
				if($row["spec"] && $row["spec-label"]){
					$row["spec"]=explode("~",$row["spec"]);$row["spec-label"]=explode("~",$row["spec-label"]);
					$temp_spec=array();
					$i=0;
					foreach($row["spec"] as $spec_name){
						array_push($temp_spec,array("name"=>$spec_name,"value"=>$row["spec-label"][$i]));
						$i++;
					}
					if(count($temp_spec)==count($row["spec"])){
						$row["spec"]=$temp_spec;
						unset($row["spec-label"]);
					}
				}
				if($row["colors"]){
					$row["colors"]=json_decode($row["colors"]);
					if(is_string($row["colors"])) $row["colors"]=json_decode($row["colors"]);
				}
				if($generate_links){
					$row["downloads"]=download_links($row["id"]);
					$row["templates"]=template_links($row["id"]);
				}
				//format prices if exists
				if(isset($_GET["auth"])) $row["auth"]=$_GET["auth"];
				if($row["sell-price"]){
					$pl_i=array_search($esc["pricelist"]?strtolower($esc["pricelist"]):"pinpoint",explode("~",strtolower($row["pricelist"])));
					if (!$pl_i) $pl_i=array_search("Pinpoint",explode("~",$row["pricelist"]));
					if (!$pl_i) $pl_i=0;
					$row["this-pricelist"]=explode("~",$row["pricelist"])[$pl_i];
					if($pl_i!==false){
						$row["quantity"]=explode("!",$row["quantity"])[$pl_i];
						$row["sell-price"]=explode("!",$row["sell-price"])[$pl_i];
						$row["tool-charge"]=explode("!",$row["tool-charge"])[$pl_i];
						$row["carton-charge"]=explode("!",$row["carton-charge"])[$pl_i];
						//$row["extras-cost"]=explode(":",$row["extras-cost"])[$pl_i];
						//$row["extras-setup"]=explode(":",$row["extras-setup"])[$pl_i];
						$row["prices"]=[];
						$row["extra"]=[];
						$type_i=0;
						foreach(explode("~",$row["types"]) as $type){
							$row["prices"][$type]=["main"=>[]];
							$quan_i=0;
							foreach(explode(":",explode("~",$row["quantity"])[$type_i]) as $quan) {
								$row["prices"][$type]["main"][$quan] = explode(":",explode("~",$row["sell-price"])[$type_i])[$quan_i];
								$extras_i=0;
								if($row["extras"]){
									$row["prices"][$type]["extras"]=[];
									foreach(explode("~",$row["extras"]) as $extras){
										$row["prices"][$pl][$type]["extras"][$extras]=[
											"main"=>explode("|",explode("!",explode(":",explode("~",$row["extras-cost"])[$extras_i])[$pl_i])[$type_i]),
											"setup"=>explode("!",explode(":",explode("~",$row["extras-setup"])[$extras_i])[$pl_i])[$type_i]
										];
										$extras_i++;
									}
								}
								$quan_i++;
							}
							$row["prices"][$type]["setup"]=explode(":",explode("~",$row["tool-charge"])[$type_i]);
							$row["prices"][$type]["carriage"]=explode(":",explode("~",$row["carton-charge"])[$type_i]);
							$type_i++;
						}
						$extra_i=0;
						foreach(explode("~",$row["extra-name"]) as $extra_name){if($extra_name){
							array_push($row["extra"],[
								"name"=>$extra_name,
								"unit"=>explode("~",$row["extra-unit"])[$extra_i],
								"unit-type"=>explode("~",$row["extra-unit-type"])[$extra_i],
								"setup"=>explode("~",$row["extra-setup"])[$extra_i],
								"setup-type"=>explode("~",$row["extra-setup-type"])[$extra_i]
							]);
							$extra_i++;
						}}
						unset($row["types"]);
						unset($row["pricelist"]);
						unset($row["quantity"]);
						unset($row["sell-price"]);
						unset($row["tool-charge"]);
						unset($row["carton-charge"]);
						unset($row["extras"]);
						unset($row["extras-cost"]);
						unset($row["extras-setup"]);
						unset($row["extra-name"]);
						unset($row["extra-unit"]);
						unset($row["extra-unit-type"]);
						unset($row["extra-setup"]);
						unset($row["extra-setup-type"]);
					}
				}
				$sellPrice = explode(":",$row["cost"]);
				$newArray = array();
				$sellArray = array();
				$tempSellArray = array();
				$finalArray = array();
				$chkArr = array();
				for($num1=0;$num1<count($sellPrice);$num1++){
					if(is_numeric($sellPrice[$num1])){
						array_push($tempSellArray,$sellPrice[$num1]);
					}else{
						$innersellPrice = explode('~',$sellPrice[$num1]);
							for($l=0;$l<count($innersellPrice);$l++){
								$finalPrice = explode('!',$innersellPrice[$l]);
								array_push($tempSellArray,$finalPrice[0]);
							}
					}
				}
				$minVal= min($tempSellArray);
				$row['price'] = number_format($minVal, 2);
				
				array_push($return["response"],$row);
			}

		}
	}
}

/*********final data conversion for high to low filter****** */
if($_POST["sort"] == 'high'){
		$lastArry = array();
		foreach($return["response"] as $newRow){
			array_push($lastArry , array('id'=>$newRow["id"],'price' => $newRow["price"]));
			
		}
		$lastArry = array_sort($lastArry, 'price', SORT_DESC);
		$finalIDArray = array();
		foreach($lastArry as $lrow){
			array_push($finalIDArray,$lrow["id"]);
		}
		$IDVar = implode(",",$finalIDArray);
		$finalSql="SELECT id,name,reference,category,img,`sell-price` AS cost,`web-order` FROM `product-list` WHERE `id` IN ($IDVar) ORDER BY FIELD(id, $IDVar)";	
		if($con && strlen($finalSql)>5){
			if($_POST["sort"] == 'A-Z' && $_POST["sort"] != ''){
				$finalSql.=" ORDER BY name ASC";
			}else if($_POST["sort"] == 'Z-A' && $_POST["sort"] != ''){
				$finalSql.=" ORDER BY name DESC";
			}
			$finalSql.= "limit ".$limtQ.",18";
			$printed=[];
			$result=mysqli_query($con,$finalSql) or die(mysqli_error($con));
			if(!$result){$return["error"]="500";$return["message"]="Server connection failed.";}
			else if(mysqli_num_rows($result)==0){$return["error"]="000";$return["message"]="No results found, check request.";}
			else if($result){
				$return["response"]=array();
				while($row = mysqli_fetch_assoc($result)){
					
					// hardcode - if one of duped products for seperate pricelist, find by name then add types and prices to 'merge' property
					$hasDupe=array("SEB","HEB","MRB","PWEB");
					if ($esc["pricelist"] && array_search($row["reference"], $hasDupe) !== false) {
						$merge_sql="SELECT name, reference, pricelist, quantity, types, `sell-price`, `tool-charge`, `carton-charge` FROM `product-list` WHERE name='{$row["name"]}' AND reference IS NULL";
						$merge_res=mysqli_query($con,$merge_sql);
						if($merge_res){
							while($merge_row = mysqli_fetch_assoc($merge_res)){
								$row["isDupe"]="inWhile";
								if($merge_row["sell-price"]){
									$pl_i=array_search($esc["pricelist"]?strtolower($esc["pricelist"]):"pinpoint",explode("~",strtolower($merge_row["pricelist"])));
									if (!$pl_i) $pl_i=array_search("Pinpoint",explode("~",$merge_row["pricelist"]));
									if (!$pl_i) $pl_i=0;
									$merge_row["this-pricelist"]=explode("~",$merge_row["pricelist"])[$pl_i];
									if($pl_i!==false){
										$merge_row["quantity"]=explode("!",$merge_row["quantity"])[$pl_i];
										$merge_row["sell-price"]=explode("!",$merge_row["sell-price"])[$pl_i];
										$merge_row["tool-charge"]=explode("!",$merge_row["tool-charge"])[$pl_i];
										$merge_row["carton-charge"]=explode("!",$merge_row["carton-charge"])[$pl_i];
										$merge_row["prices"]=[];
										$type_i=0;
										foreach(explode("~",$merge_row["types"]) as $type){
											$merge_row["prices"][$type]=["main"=>[]];
											$quan_i=0;
											foreach(explode(":",explode("~",$merge_row["quantity"])[$type_i]) as $quan) {
												$merge_row["prices"][$type]["main"][$quan] = explode(":",explode("~",$merge_row["sell-price"])[$type_i])[$quan_i];
												$quan_i++;
											}
											$merge_row["prices"][$type]["setup"]=explode(":",explode("~",$merge_row["tool-charge"])[$type_i]);
											$merge_row["prices"][$type]["carriage"]=explode(":",explode("~",$merge_row["carton-charge"])[$type_i]);
											$type_i++;
										}
										unset($merge_row["types"]);
										unset($merge_row["pricelist"]);
										unset($merge_row["quantity"]);
										unset($merge_row["sell-price"]);
										unset($merge_row["tool-charge"]);
										unset($merge_row["carton-charge"]);
									}
								}
								$row["merge"]=$merge_row;
							}
						}
					}

					if(!empty($_POST["categories"])){
						//foreach(explode("~",$row["category"]) as $i=>$c){
							//if(fnmatch(str_replace("-","*",$esc["cat"]),strtolower($c))){
								$row["web-order"]=explode("~",$row["web-order"])[0];
							//}
						//}
					}
					
					
					
					if(!in_array(str_replace(" ","",strtolower($row["name"])),$printed)){
						array_push($printed,str_replace(" ","",strtolower($row["name"])));
						
						if($row["img"]) $row["img"]=img_url($row["id"],$row["img"]);
						// gallery images
						$product_dir = glob("./" . $row["id"] . " - *", GLOB_ONLYDIR);
						if (array_key_exists(0, $product_dir)) {
							$image_files = glob($product_dir[0] . "/images/*.{jpeg,jpg,png,gif}", GLOB_BRACE);
							usort($image_files, function ($a, $b) {
								$a = pathinfo($a)["filename"];
								$b = pathinfo($b)["filename"];
								return (is_numeric($a) && is_numeric($b) ? $a - $b : strcmp($a, $b));
							});
							$row["images"] = images_url($row["id"], $row["name"], $image_files);
							$row["images-thumbs"] = thumbs_url($row["id"], $row["name"], $image_files);
						}
						
						if($row["spec"] && $row["spec-label"]){
							$row["spec"]=explode("~",$row["spec"]);$row["spec-label"]=explode("~",$row["spec-label"]);
							$temp_spec=array();
							$i=0;
							foreach($row["spec"] as $spec_name){
								array_push($temp_spec,array("name"=>$spec_name,"value"=>$row["spec-label"][$i]));
								$i++;
							}
							if(count($temp_spec)==count($row["spec"])){
								$row["spec"]=$temp_spec;
								unset($row["spec-label"]);
							}
						}
						if($row["colors"]){
							$row["colors"]=json_decode($row["colors"]);
							if(is_string($row["colors"])) $row["colors"]=json_decode($row["colors"]);
						}
						if($generate_links){
							$row["downloads"]=download_links($row["id"]);
							$row["templates"]=template_links($row["id"]);
						}
						//format prices if exists
						if(isset($_GET["auth"])) $row["auth"]=$_GET["auth"];
						if($row["sell-price"]){
							$pl_i=array_search($esc["pricelist"]?strtolower($esc["pricelist"]):"pinpoint",explode("~",strtolower($row["pricelist"])));
							if (!$pl_i) $pl_i=array_search("Pinpoint",explode("~",$row["pricelist"]));
							if (!$pl_i) $pl_i=0;
							$row["this-pricelist"]=explode("~",$row["pricelist"])[$pl_i];
							if($pl_i!==false){
								$row["quantity"]=explode("!",$row["quantity"])[$pl_i];
								$row["sell-price"]=explode("!",$row["sell-price"])[$pl_i];
								$row["tool-charge"]=explode("!",$row["tool-charge"])[$pl_i];
								$row["carton-charge"]=explode("!",$row["carton-charge"])[$pl_i];
								//$row["extras-cost"]=explode(":",$row["extras-cost"])[$pl_i];
								//$row["extras-setup"]=explode(":",$row["extras-setup"])[$pl_i];
								$row["prices"]=[];
								$row["extra"]=[];
								$type_i=0;
								foreach(explode("~",$row["types"]) as $type){
									$row["prices"][$type]=["main"=>[]];
									$quan_i=0;
									foreach(explode(":",explode("~",$row["quantity"])[$type_i]) as $quan) {
										$row["prices"][$type]["main"][$quan] = explode(":",explode("~",$row["sell-price"])[$type_i])[$quan_i];
										$extras_i=0;
										if($row["extras"]){
											$row["prices"][$type]["extras"]=[];
											foreach(explode("~",$row["extras"]) as $extras){
												$row["prices"][$pl][$type]["extras"][$extras]=[
													"main"=>explode("|",explode("!",explode(":",explode("~",$row["extras-cost"])[$extras_i])[$pl_i])[$type_i]),
													"setup"=>explode("!",explode(":",explode("~",$row["extras-setup"])[$extras_i])[$pl_i])[$type_i]
												];
												$extras_i++;
											}
										}
										$quan_i++;
									}
									$row["prices"][$type]["setup"]=explode(":",explode("~",$row["tool-charge"])[$type_i]);
									$row["prices"][$type]["carriage"]=explode(":",explode("~",$row["carton-charge"])[$type_i]);
									$type_i++;
								}
								$extra_i=0;
								foreach(explode("~",$row["extra-name"]) as $extra_name){if($extra_name){
									array_push($row["extra"],[
										"name"=>$extra_name,
										"unit"=>explode("~",$row["extra-unit"])[$extra_i],
										"unit-type"=>explode("~",$row["extra-unit-type"])[$extra_i],
										"setup"=>explode("~",$row["extra-setup"])[$extra_i],
										"setup-type"=>explode("~",$row["extra-setup-type"])[$extra_i]
									]);
									$extra_i++;
								}}
								unset($row["types"]);
								unset($row["pricelist"]);
								unset($row["quantity"]);
								unset($row["sell-price"]);
								unset($row["tool-charge"]);
								unset($row["carton-charge"]);
								unset($row["extras"]);
								unset($row["extras-cost"]);
								unset($row["extras-setup"]);
								unset($row["extra-name"]);
								unset($row["extra-unit"]);
								unset($row["extra-unit-type"]);
								unset($row["extra-setup"]);
								unset($row["extra-setup-type"]);
							}
						}
						// if($row["reference"] == null){
						// 	$row["null"] = 'nidhi';
						// }

						$sellPrice = explode(":",$row["cost"]);
						$newArray = array();
						$sellArray = array();
						$tempSellArray = array();
						$finalArray = array();
						$chkArr = array();
						for($num1=0;$num1<count($sellPrice);$num1++){
							if(is_numeric($sellPrice[$num1])){
								array_push($tempSellArray,$sellPrice[$num1]);
							}else{
								$innersellPrice = explode('~',$sellPrice[$num1]);
									for($l=0;$l<count($innersellPrice);$l++){
										$finalPrice = explode('!',$innersellPrice[$l]);
										array_push($tempSellArray,$finalPrice[0]);
									}
							}
						}
						$minVal= min($tempSellArray);
						$row['price'] = number_format($minVal, 2);
						array_push($return["response"],$row);
					}

				}
			}
		}

}

echo json_encode($return);
?>