<?php
require_once("config/configDB.php"); 
// storing  request (ie, get/post) global array to a variable  
$requestData= $_REQUEST;



$selectspare     = $_POST['selectspare'];


 	$columns = array( 
			
			0 => 'sparename',
			
			1 => 'spareqty'
		);
		

		$sql = "SELECT sm.id,sm.productname as sparename,sum(psd.purchase_eqty) as spareqty FROM product_master sm 
		
		LEFT JOIN  purchasedetails as psd ON sm.id=psd.productID
		WHERE  sm.isdelete=1  and psd.purchase_eqty>0 and psd.status='' and sm.product_type='Spare' ";

		if($selectspare !='')  {
		
			$sql.="  and sm.id='".$selectspare."' ";
		}
		
		$sql.=" GROUP BY psd.productID ";
		//echo $sql;
	 
	$query=mysqli_query($conn, $sql) or die("brandgriddata.php: get brand Data step 1".$sql);
	$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;

	if( !empty($requestData['search']['value']) ) {   
		//$sql.=" AND ( bm.brand_name LIKE '".$requestData['search']['value']."%' ";    
		$sql.=" OR sm.sparename LIKE '".$requestData['search']['value']."%' ";
		//$sql.=" OR sm.sparetype LIKE '".$requestData['search']['value']."%' ";
		//$sql.=" OR sm.sparemodel LIKE '".$requestData['search']['value']."%' ";
		//$sql.=" OR psd.spareqty LIKE '".$requestData['search']['value']."%' ";
		//$sql.=" OR sm.sparename LIKE '".$requestData['search']['value']."%' ";
		$sql.="  )";
	}

	


$query=mysqli_query($conn, $sql) or die("brandgriddata.php: get brand Data step 2".$sql);
$totalFiltered = mysqli_num_rows($query); 

$sql.=" ORDER BY sparename LIMIT ".$requestData['start']." ,".$requestData['length']."   ";


$query=mysqli_query($conn, $sql) or die("brandgriddata.php: get brand Data step 3".$sql);
	

$data = array();
while( $row=mysqli_fetch_array($query) ) { 
	$nestedData=array(); 
		
		$nestedData[] 	= $row['sparename'];
		

		$spareid    = $row['id'];

		$resultSpareQty = mysqli_query($conn,"SELECT sum(isd.spareqty) as spareqty   FROM internalpro_spare_dtl as isd WHERE isd.spareID='".$spareid."' and  isd.isdelete=1  ");
		$SpareQty    = mysqli_fetch_array($resultSpareQty,MYSQLI_ASSOC);
		$ASpareQty   = ($SpareQty['spareqty']>0) ? $SpareQty['spareqty'] : 0;
		
		/*$resultSpareQtyw = mysqli_query($conn,"SELECT sum(swd.spareqty) as wsqty FROM service_workshop_dtl as swd WHERE swd.isdelete=1 and swd.spareID='".$spareid."' ");
		$wSpareQty    	= mysqli_fetch_array($resultSpareQtyw,MYSQLI_ASSOC);
		$AwSpareQty   	= ($wSpareQty['wsqty']>0) ? $wSpareQty['wsqty'] : 0;*/
		
		//echo "SELECT sum(psd.purchase_eqty) as spareqty   FROM purchasedetails as psd WHERE psd.productID='".$spareid."' and  psd.status=''  ";
		
		$resultP 		= mysqli_query($conn,"SELECT sum(psd.purchase_eqty) as spareqty   FROM purchasedetails as psd WHERE psd.productID='".$spareid."' and  psd.status=''  ");
		$PSpareQty 		= mysqli_fetch_array($resultP,MYSQLI_ASSOC);
		
		
		$resultPwo 		= mysqli_query($conn,"SELECT sum(psd.purchase_eqty) as spareqty   FROM purchasedetailswo as psd WHERE psd.productID='".$spareid."' and  psd.status=''  ");
		$PSpareQtywo 		= mysqli_fetch_array($resultPwo,MYSQLI_ASSOC);
		
		$AwSpareQty   	= ($PSpareQty['spareqty']+$PSpareQtywo['spareqty']) - ($ASpareQty); 
		$nestedData[] 	= $AwSpareQty;
		//$nestedData[] 	= $spareName;
	
	$data[] = $nestedData;
}

$json_data = array(
			"draw"            => intval( $requestData['draw'] ),   
			"recordsTotal"    => intval( $totalData ),  
			"recordsFiltered" => intval( $totalFiltered ), 
			"data"            => $data   
			);

echo json_encode($json_data);  

?>
