<?php
require_once("config/configDB.php"); 

// storing  request (ie, get/post) global array to a variable  
$requestData= $_REQUEST;

//saleheader (invoice_no, sale_date, customer_id, dc_num, po_num,saleGSTNO, itemcount, tamount, create_date, flag)
$userpermissionPrint 	= $_POST['userpermissionPrint'];
$cID 		= $_POST['customerID'];
$startDate 	= $_POST['startDate'];
$endDate 	= $_POST['endDate'];

$columns = array( 
// datatable column index  => database column name
	0 => 'sh.id', 
	1 => 'sale_date',
	2 => 'customer_id',
    3 => 'gstin',
	4 => 'totalamount',		5 => 'gst5',		6 => 'gst5',	7 => 'gst6',	8 => 'gst6',	9 => 'gst9',	10 => 'gst9',	11=> 'totalamount'
);
//where sale_date BETWEEN '' AND value2
 if($startDate !='' && $endDate !='') {
	 
	$startDate 	= new DateTime($_POST['startDate']);
	$endDate 	= new DateTime($_POST['endDate']);
	$startDate 	=$startDate->format('Y-m-d');
	$endDate 	=$endDate->format('Y-m-d');
	
	$sql = "SELECT sd.saleheader_id ,sh.id,sd.sale_gst, sum(if(sd.sale_gst=18,sd.sale_cgst,0)) as gst18,sum(if(sd.sale_gst=5,sd.sale_cgst,0)) as gst5,sum(if(sd.sale_gst=12,sd.sale_cgst,0)) as gst12,sum(salenetamt) as totalamount,sh.invoice_no as invoice_no , sh.sale_date, sh.customer_id, sh.po_num,sh.saleGSTNO, sh.itemcount,sh.tamount, sh.create_date,sh.id,cm.cname,cm.gstin,pm.productname,sd.sale_eqty,sd.sale_prate,pm.hsn,cm.customer_state FROM saledetails as sd JOIN saleheader as sh on sh.id =sd.saleheader_id LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join product_master as pm on sd.productID=pm.id where (sh.sale_date)>=('".$startDate."') AND (sh.sale_date)<=('".$endDate."') and sh.status='' and sd.status=''  group by sd.saleheader_id,sh.id,sd.productID  ";
	
		$query=mysqli_query($conn, $sql) or die("get  Data".$sql);	$totalData = mysqli_num_rows($query);	$totalFiltered = $totalData;  	//$sql = "SELECT sh.invoice_no, sh.sale_date, sh.customer_id, sh.dc_num, sh.po_num,sh.saleGSTNO, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id  ";		$sql ="SELECT sd.saleheader_id, sum(if(sd.sale_gst=18,sd.sale_cgst,0)) as gst18,sum(if(sd.sale_gst=5,sd.sale_cgst,0)) as gst5,sum(if(sd.sale_gst=12,sd.sale_cgst,0)) as gst12,sum(salenetamt) as totalamount,sh.invoice_no as invoice_no , sh.sale_date, sh.customer_id, sh.dc_num, sh.po_num,sh.saleGSTNO, sh.itemcount,sh.tamount, sh.create_date,sh.id,cm.cname,cm.gstin FROM saledetails as sd JOIN saleheader as sh on  sh.id =sd.saleheader_id LEFT JOIN customer_master as cm on cm.id = sh.customer_id where sh.sale_date BETWEEN '".$startDate."' AND '".$endDate."' group by sd.saleheader_id,sh.id  ";


} else {
	$sql = "SELECT sd.saleheader_id ,sh.id,sd.sale_gst, sum(if(sd.sale_gst=18,sd.sale_cgst,0)) as gst18,sum(if(sd.sale_gst=5,sd.sale_cgst,0)) as gst5,sum(if(sd.sale_gst=12,sd.sale_cgst,0)) as gst12,sum(salenetamt) as totalamount,sh.invoice_no as invoice_no , sh.sale_date, sh.customer_id, sh.po_num,sh.saleGSTNO, sh.itemcount,sh.tamount, sh.create_date,sh.id,cm.cname,cm.gstin,pm.productname,sd.sale_eqty,sd.sale_prate,pm.hsn,cm.customer_state FROM saledetails as sd JOIN saleheader as sh on sh.id =sd.saleheader_id LEFT JOIN customer_master as cm on cm.id = sh.customer_id inner join product_master as pm on sd.productID=pm.id where  sh.status='' and sd.status=''  group by sd.saleheader_id,sh.id,sd.productID";
	$query=mysqli_query($conn, $sql) or die("get  Data".$sql);
	$totalData = mysqli_num_rows($query);
	$totalFiltered = $totalData;  

	//$sql = "SELECT sh.invoice_no, sh.sale_date, sh.customer_id, sh.dc_num, sh.po_num,sh.saleGSTNO, sh.itemcount, sh.tamount, sh.create_date,sh.id,cm.cname FROM saleheader as sh LEFT JOIN customer_master as cm on cm.id = sh.customer_id  ";		$sql ="SELECT sd.saleheader_id as id, sum(if(sd.sale_gst=18,sd.sale_cgst,0)) as gst18,sum(if(sd.sale_gst=5,sd.sale_cgst,0)) as gst5,sum(if(sd.sale_gst=12,sd.sale_cgst,0)) as gst12,sum(salenetamt) as totalamount,sh.invoice_no as invoice_no , sh.sale_date, sh.customer_id, sh.dc_num, sh.po_num,sh.saleGSTNO, sh.itemcount,sh.tamount, sh.create_date,sh.id,cm.cname,cm.gstin FROM saledetails as sd JOIN saleheader as sh on  sh.id =sd.saleheader_id LEFT JOIN customer_master as cm on cm.id = sh.customer_id group by sd.saleheader_id,sh.id";

}
if( !empty($requestData['search']['value']) ) {   

	$sql.=" AND ( sh.id LIKE '".$requestData['search']['value']."%' ";    
	$sql.=" OR sh.sale_date LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR cm.cname LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR cm.gstin LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR totalamount LIKE '".$requestData['search']['value']."%' ";
	$sql.=" OR gst5 LIKE '".$requestData['search']['value']."%' ";	
		$sql.=" OR gst5 LIKE '".$requestData['search']['value']."%' ";	
			$sql.=" OR gst12 LIKE '".$requestData['search']['value']."%' ";	
				$sql.=" OR gst12 LIKE '".$requestData['search']['value']."%' ";	
					$sql.=" OR gst18 LIKE '".$requestData['search']['value']."%' ";		
					$sql.=" OR gst18 LIKE '".$requestData['search']['value']."%' ";	
    $sql.=" OR id LIKE '".$requestData['search']['value']."%' )";
}//die("php: get Data".$sql);
$query=mysqli_query($conn, $sql) or die("php2: get Data".$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("php1: get sale Data".$sql);
	
//(invoice_no, sale_date, customer_id, dc_num, po_num,saleGSTNO, itemcount, tamount, create_date, flag)
$data = array();
//echo $sql;die("---");
while( $row=mysqli_fetch_array($query) ) { 
$state=$row["customer_state"];
	$nestedData=array(); 
	
	if($state=='TamilNadu')
	{
	$nestedData[] = $row["invoice_no"];
    $date = new DateTime($row["sale_date"]); 
    $nestedData[] = $date->format('d-m-Y'); //only fetching date
    $nestedData[] = $row["cname"];
    $nestedData[] = $row["gstin"];
	$nestedData[] = $row["productname"];
	$nestedData[] = $row["hsn"];
	$nestedData[] = $row["sale_prate"];
	$nestedData[] = $row["sale_gst"];
	$nestedData[] = $row["sale_eqty"];
    $nestedData[] = $row["totalamount"]-($row["gst5"]+$row["gst12"]+$row["gst18"]);		
	$nestedData[] = $row["gst5"];	
	$nestedData[] = $row["gst5"];	
	$nestedData[] = $row["gst12"];	
	$nestedData[] = $row["gst12"];	
	$nestedData[] = $row["gst18"];
		$nestedData[] = $row["gst18"];	
	$nestedData[] = '0.00';	
	$nestedData[] = number_format($row["totalamount"], 2, '.', '');
	//$nestedData[] = str_replace('.00', '', number_format($tempvalue, 2, '.', ''));
	}
	else
	{
		
		
		$nestedData[] = $row["invoice_no"];
    $date = new DateTime($row["sale_date"]); 
    $nestedData[] = $date->format('d-m-Y'); //only fetching date
    $nestedData[] = $row["cname"];
    $nestedData[] = $row["gstin"];
	$nestedData[] = $row["productname"];
	$nestedData[] = $row["hsn"];
	$nestedData[] = $row["sale_prate"];
	$nestedData[] = $row["sale_gst"];
	$nestedData[] = $row["sale_eqty"];
    $nestedData[] = $row["totalamount"]-($row["gst5"]+$row["gst12"]+$row["gst18"]);		
	$nestedData[] = "0.00";	
	$nestedData[] = "0.00";	
	$nestedData[] = "0.00";	
	$nestedData[] = "0.00";	
	$nestedData[] = "0.00";
		$nestedData[] = '0.00';	
	$nestedData[] = number_format((($row["gst5"]*2)+($row["gst12"]*2)+($row["gst18"]*2)), 2, '.', '');	
	$nestedData[] = number_format($row["totalamount"], 2, '.', '');
		
	}
	
	
    
    //$nestedData[] = $row["saleGSTNO"];
	//$nestedData[] = "<a href='productMasterEdit.php?id=".$row['id']."' id='productMaster_".$row['id']."' class='btn btn-primary'>Edit</a>";
	$edit  = "";
	if($userpermissionPrint==1) {
		//$edit .="<a class='btn btn-primary'>Print</a>";
	}

	//$nestedData[] = $edit."&nbsp";

    $data[] = $nestedData;
}

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

echo json_encode($json_data);  

?>
