<?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');
}
//$supplierid     = $_POST['supplierid'];
$supplierid		 = $_POST['supplierid'];

 	$columns = array( 
		0 => 'pdate', 
		1 => 'sname',
		2 => 'mobile',
		3 => 'pamount',
		4 => 'purchasetype'
	);
		

		$sql = "SELECT a.id,a.scode,a.sname,a.mobile,sum(a.pamount) as pamount ,a.purchasetype,a.pdate
		FROM (
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,pdi.amount as pamount, 0 as payamount, 'Internal Diesel Purchase' 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 
		";
		 if($tripfromDate !=''  && $triptoDate !='' && $supplierid ==''  ) {
			$sql.=" and pdi.dieseldate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'";
   
		}  else if($tripfromDate !=''  && $triptoDate !='' && $supplierid !=''  ) {
		   $sql.=" and sm.id='".$supplierid."'  and   pdi.dieseldate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'  ";
   
		   }  else if($tripfromDate ==''  && $triptoDate =='' && $supplierid !='' )  {
			$sql.="  and sm.id='".$supplierid."' ";
   
		}

		
		
		
		$sql.="
			GROUP BY pdi.id
			
			UNION ALL
			
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,(expl.total_cost) as pamount,0 as payamount ,'Compressor Contractor Work' as purchasetype,expl.utilizedate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN explosive_utiliz_hdr as expl ON sm.id=expl.contractor
		
			WHERE sm.isdelete=1 and expl.status=''
			";

			if($tripfromDate !=''  && $triptoDate !='' && $supplierid ==''  ) {
				$sql.=" and expl.utilizedate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'";
	   
			}  else if($tripfromDate !=''  && $triptoDate !='' && $supplierid !=''  ) {
			   $sql.=" and sm.id='".$supplierid."'  and   expl.utilizedate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'  ";
	   
			   }  else if($tripfromDate ==''  && $triptoDate =='' && $supplierid !='' )  {
				$sql.="  and sm.id='".$supplierid."' ";
	   
			}
		
		$sql.="
			GROUP BY expl.id
			
			UNION ALL
			
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,(poh.amount) as pamount,0 as payamount ,'External Diesel Fill' as purchasetype,poh.dieseldate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN externalfill_diesel as poh ON sm.id=poh.supplierid
		
			WHERE sm.isdelete=1 and poh.isdelete=1
			";

			if($tripfromDate !=''  && $triptoDate !='' && $supplierid ==''  ) {
				$sql.=" and poh.dieseldate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'";
	   
			}  else if($tripfromDate !=''  && $triptoDate !='' && $supplierid !=''  ) {
			   $sql.=" and sm.id='".$supplierid."'  and   poh.dieseldate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'  ";
	   
			   }  else if($tripfromDate ==''  && $triptoDate =='' && $supplierid !='' )  {
				$sql.="  and sm.id='".$supplierid."' ";
	   
			}
		
		$sql.="
			GROUP BY poh.id
			
			
			UNION ALL
			
			
			SELECT sm.id,sm.scode,sm.sname,sm.mobile,(pth.amount) as pamount,0 as payamount ,'External Diesel Unfill' as purchasetype,pth.dieseldate as pdate
		
			FROM supplier_master as sm 
		
			LEFT JOIN externalpro_diesel as pth ON sm.id=pth.supplierid
		
			WHERE sm.isdelete=1 and pth.isdelete=1
			";

			if($tripfromDate !=''  && $triptoDate !='' && $supplierid ==''  ) {
				$sql.=" and pth.dieseldate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'";
	   
			}  else if($tripfromDate !=''  && $triptoDate !='' && $supplierid !=''  ) {
			   $sql.=" and sm.id='".$supplierid."'  and   pth.dieseldate BETWEEN '".$tripfromDate."' AND '".$triptoDate."'  ";
	   
			   }  else if($tripfromDate ==''  && $triptoDate =='' && $supplierid !='' )  {
				$sql.="  and sm.id='".$supplierid."' ";
	   
			}
		
		$sql.="
			GROUP BY pth.id
		)
		a ";
		
		if( !empty($requestData['search']['value']) ) {   
			$sql.=" where ( ";    
			$sql.=" a.sname LIKE '".$requestData['search']['value']."%' ";    
			//$sql.=" OR a.mobile LIKE '".$requestData['search']['value']."%' ";
			//$sql.=" OR a.pamount LIKE '".$requestData['search']['value']."%' ";
			$sql.=" OR a.purchasetype LIKE '".$requestData['search']['value']."%' ";
			$sql.="  )";
		}
	 
		$sql.=" GROUP BY a.pdate,a.id,a.purchasetype";


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

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

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

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

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

	$invoicedate = new DateTime($row["pdate"]); 
	$nestedData[] = $invoicedate->format('d-m-Y'); //only fetching date
	$nestedData[] = $row["sname"];
	
	$nestedData[] = $row["mobile"];
	$nestedData[] = $row["pamount"];
	$nestedData[] = $row["purchasetype"];
	
	$data[] = $nestedData;
}

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

echo json_encode($json_data);  

?>
