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

$tripfromDate 	= '';
if($_POST['tripfromDate']){
	$invoicedate_temp   = new DateTime(stripslashes($_POST['tripfromDate']));
	$tripfromDate 		= $invoicedate_temp->format('Y-m-d');
}
$triptoDate 	= '';
if($_POST['triptoDate']) {
	$invoicedate_temp1   = new DateTime(stripslashes($_POST['triptoDate']));
	$triptoDate 		= $invoicedate_temp1->format('Y-m-d');
}
//$tripvehicle     = $_POST['tripvehicle'];
$supplierid		 = $_POST['supplierid'];

 	$columns = array( 
		0 => 'scode',
		1 => 'sname',
		2 => 'purchasetype',
		3 => 'pamount',
		4 => 'payamount',
		5 => 'id'
	);
		

		$sql = "SELECT a.id,a.scode,a.sname,a.mobile,sum(a.pamount) as pamount , sum(payamount) as payamount,a.purchasetype,a.pdate
		FROM (
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,pdi.amount as pamount, 0 as payamount, 'Diesel' as purchasetype ,pdi.dieseldate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN purchase_dieselinvoice as pdi ON sm.id=pdi.supplierid 
		
			WHERE sm.isdelete=1 and pdi.isdelete=1 and pdi.status=''
		
			GROUP BY pdi.id
			
			UNION ALL
			
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,(psh.amount) as pamount ,0 as payamount , 'External Diesel Fill' as purchasetype,psh.dieseldate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN externalfill_diesel as psh ON sm.id=psh.supplierid
		
			WHERE sm.isdelete=1 and psh.isdelete=1 and psh.status=''
		
			GROUP BY psh.id
			
			UNION ALL
			
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,(poh.amount) as pamount,0 as payamount ,'External Diesel Unfill' as purchasetype,poh.dieseldate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN externalpro_diesel as poh ON sm.id=poh.supplierid
		
			WHERE sm.isdelete=1 and poh.isdelete=1 and poh.status=''
		
			GROUP BY poh.id
			
			UNION ALL
			
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,(pth.total_cost) as pamount,0 as payamount ,'Compressor Contractor Work' as purchasetype,pth.utilizedate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN explosive_utiliz_hdr as pth ON sm.id=pth.contractor
		
			WHERE sm.isdelete=1 and pth.status=''
		
			GROUP BY pth.id
			
			UNION ALL
			
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,0 as pamount,(sp.amount) as payamount ,'payment' as purchasetype,sp.invoicedate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN supplier_payment as sp ON sm.id=sp.supplierid
		
			WHERE sm.isdelete=1 and sp.isdelete=1 and sp.status=''
		
			GROUP BY sp.id
		)
		a  where 1=1 ";

	 if($tripfromDate !=''  && $triptoDate !=''  && $supplierid =='' ) {
	 	$sql.=" and a.pdate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'";
	 } else if($tripfromDate !=''  && $triptoDate !=''  && $supplierid !='' ) {
		$sql.=" and a.id='".$supplierid."' and a.pdate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'  ";

   	 } else if($tripfromDate ==''  && $triptoDate ==''  && $supplierid !='')  {
		$sql.="  and a.id='".$supplierid."' ";

	} 
		//$sql.="  GROUP BY a.pdate,a.id,a.purchasetype";
	
	if($tripfromDate !=''  && $triptoDate !='' ) {
		$sql.="  GROUP BY a.pdate,a.id,a.purchasetype";	 	
	 } else if($tripfromDate !=''  && $triptoDate !='' ) {		
		$sql.="  GROUP BY a.pdate,a.id,a.purchasetype";
   	 } else {
   		$sql.="  GROUP BY a.id"; 	
   	 }

	

	$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 (   a.sname LIKE '".$requestData['search']['value']."%' ";
		$sql.=" OR a.purchasetype LIKE '".$requestData['search']['value']."%' ";
		//$sql.=" OR pamount LIKE '".$requestData['search']['value']."%' ";
		$sql.=" OR payamount 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 ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";


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

$data = array();
while( $row=mysqli_fetch_array($query) ) { 
	$nestedData=array(); 

	$edit  = "";
	  
	if($tripfromDate !=''  && $triptoDate !='' ) {
		$invoicedate 		= new DateTime($row["pdate"]); 
		$nestedData[] 		= $invoicedate->format('d-m-Y'); //only fetching date
	} else {
		$nestedData[] = $row["scode"];	
	}

	
	$nestedData[] = $row["sname"];
	
	
	$nestedData[] = $row["purchasetype"];
	$nestedData[] = $row["pamount"];
	$nestedData[] = $row["payamount"];
	if($tripfromDate !=''  && $triptoDate !='' ) {
	
		$nestedData[] = '-';
	} else {
		$nestedData[] = $row["pamount"]-$row["payamount"];
	}	
	
	$Delete  = "";
	
	
	
	$data[] = $nestedData;
}

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

echo json_encode($json_data);  

?>
