<?php
include("../../inc/config.php");

    $from = $_REQUEST['from'];
    $to = $_REQUEST['to'];
    $status = $_REQUEST['status'];
    $clientType = $_REQUEST['role'];
    $client_id = $_REQUEST['client_id'];
    $courier_id = $_REQUEST['courier_id'];
    



$requestData= $_REQUEST;
/*Fetch Database column Details*/
        $columns = array( 
            0 =>'id', 
            1 =>'client_id',
            2 =>'courier_id',
            3 =>'cscs_awb',
            4 =>'3pl_awb',
            5 =>'ref_no',
            6 =>'status',
            7 =>'status_code',
            8 =>'order_type',
            9 =>'collectable_amount',
            10 =>'business_mode',
            11 =>'business_type',
            12 =>'shipper_name',
            13 =>'shipper_address',
            14 =>'shipper_city',
            15 =>'shipper_state',
            16 =>'shipper_pincode',
            17 =>'shipper_phone',
            18 =>'consignee_name',
            19 =>'consignee_address',
            20 =>'consignee_city',
            21 =>'consignee_state',
            22 =>'consignee_pincode',
            23 =>'consignee_phone',
            24 =>'product_description',
            25 =>'quantity',
            26 =>'boxes',
            27 =>'invoice_value',
            28 =>'taxable_value',
            29 =>'hsn_code',
            30 =>'discount',
            31 =>'gst_amount',
            32 =>'mrp',
            33 =>'ewaybill',
            34 =>'rto_name',
            35 =>'rto_address',
            36 =>'rto_city',
            37 =>'rto_state',
            38 =>'rto_pincode',
            39 =>'rto_phone',
            40 =>'a_weight',
            41 =>'vw_length',
            42 =>'vw_breadth',
            43 =>'vw_height',
            44 =>'v_weight',
            45 =>'c_weight',
            46 =>'gst_rate',
            47 =>'buyer_gst',
            48 =>'seller_gst',
            49 =>'manifest_id',
            50 =>'delete_status',
            51 =>'date_added',
            52 =>'updated_at',
            53 =>'client_name',
        );
        
/*Fetch All Data Start*/        
        if($clientType=="Admin")
    	 { 
    	        if($status=='all' && $client_id=='all')
                {
                   $condition='';  
                } 
                elseif($status!='all' && $client_id=='all')
                {
                   $condition='WHERE pickup_entry.status_code="'.$status.'"';
                }
                elseif($status=='all' && $client_id!='all')
                {
                   $condition='WHERE pickup_entry.client_id='.$client_id;
                }
                else
                {
                    $condition='WHERE pickup_entry.status_code="'.$status.'" AND pickup_entry.client_id='.$client_id;
                }
    		$statement = $pdo->prepare("SELECT pickup_entry.*,tbl_user.full_name as client_name,pickup_entry.date_added as added FROM pickup_entry INNER JOIN tbl_user on pickup_entry.client_id=tbl_user.id $condition ");
    	 }
    	 else
    	 {
    	     if($status=='all')
                {
                 $statement = $pdo->prepare("SELECT pickup_entry.*,tbl_user.full_name as client_name,pickup_entry.date_added as added FROM pickup_entry INNER JOIN tbl_user on pickup_entry.client_id=tbl_user.id and pickup_entry.client_id=$client_id ");   
                }
                else{
                
                  $statement = $pdo->prepare("SELECT pickup_entry.*,tbl_user.full_name as client_name,pickup_entry.date_added as added FROM pickup_entry INNER JOIN tbl_user on pickup_entry.client_id=tbl_user.id where pickup_entry.status_code='$status' AND pickup_entry.client_id=$client_id ");   
                }	 
    	 }
		$statement->execute();
		$query = $statement->fetchAll(PDO::FETCH_ASSOC);
		$totalData = $statement->rowCount();
        $totalFiltered = $totalData;
/*Fetch All Data End*/       
        
/*Fetch Filter Data Start*/    
    	 
    	 if($clientType=="Admin")
    	 { 
    	        if($status=='all' && $client_id=='all')
                {
                   $condition='WHERE 1=1 ';  
                } 
                elseif($status!='all' && $client_id=='all')
                {
                   $condition=' WHERE 1=1 and pickup_entry.status_code="'.$status.'"';
                }
                elseif($status=='all' && $client_id!='all')
                {
                   $condition='WHERE 1=1 and pickup_entry.client_id='.$client_id;
                }
                else
                {
                    $condition='WHERE 1=1 and pickup_entry.status_code="'.$status.'" AND pickup_entry.client_id='.$client_id;
                }
    		$sql="SELECT pickup_entry.*,tbl_user.full_name as client_name,pickup_entry.date_added as added FROM pickup_entry INNER JOIN tbl_user on pickup_entry.client_id=tbl_user.id $condition ";
    	 }
    	 else
    	 {
    	     if($status=='all')
                {
                 $sql="SELECT pickup_entry.*,tbl_user.full_name as client_name,pickup_entry.date_added as added FROM pickup_entry INNER JOIN tbl_user on pickup_entry.client_id=tbl_user.id and pickup_entry.client_id=$client_id WHERE 1=1";   
                }
                else{
                
                  $sql="SELECT pickup_entry.*,tbl_user.full_name as client_name,pickup_entry.date_added as added FROM pickup_entry INNER JOIN tbl_user on pickup_entry.client_id=tbl_user.id where  1=1 and pickup_entry.status_code='$status' AND pickup_entry.client_id=$client_id ";   
                }	 
    	 }
         if( !empty($requestData['search']['value']) ) 
        {   
        	$sql.=" AND ( pickup_entry.cscs_awb LIKE '%".$requestData['search']['value']."%' ";    
        	$sql.=" OR pickup_entry.3pl_awb LIKE '%".$requestData['search']['value']."%' ";
        	$sql.=" OR pickup_entry.ref_no LIKE '%".$requestData['search']['value']."%' ";
        	$sql.=" OR pickup_entry.status LIKE '%".$requestData['search']['value']."%' ";
        	$sql.=" OR pickup_entry.shipper_pincode LIKE '%".$requestData['search']['value']."%' ";
        	$sql.=" OR tbl_user.full_name LIKE '%".$requestData['search']['value']."%' )";
        }
        if($clientType=="Admin"){ 
    		$statement = $pdo->prepare($sql);
    	 }
    	 else{
    	     $statement = $pdo->prepare($sql);
    	 }
        $statement->execute();
		$query = $statement->fetchAll(PDO::FETCH_ASSOC);
        $totalFiltered = $statement->rowCount();
        
/*Fetch Filter Data End*/       
        


 $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
//  $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */	
$statement = $pdo->prepare($sql);
        $statement->execute();
		$query = $statement->fetchAll(PDO::FETCH_ASSOC);

$data = array();
$i=$requestData['start']+1;
foreach( $query as $row ) {  // preparing an array
                $date=date_create($row['date_added']);
			 $day=date_format($date,'d-m-Y');
			 
			 $created=date_create($row['date_added']);
              $da=date_format($created,'Y-m-d');
        if($from!='' && $to!='')
        {
                if ($from <= $da && $to >= $da)
                if (1)
                {

                                    $nestedData=array(); 
                                     $nestedData[] = $i;
                                        if($clientType=="Admin")	 {	$nestedData[] = $row["client_name"];}
                                    $nestedData[] = $row["cscs_awb"];
                                        if($clientType=="Admin")	 {	$nestedData[] = $row["3pl_awb"];}
                                    /*$nestedData[] = "With Date filter: ".$row["ref_no"];*/
                                    $nestedData[] = "From date : ".$from."---".$da."To Date :".$to;
                                    $nestedData[] = $row["order_type"];
                                    $nestedData[] = $row["shipper_name"];
                                    $nestedData[] = $row["shipper_address"];
                                    $nestedData[] = $row["shipper_city"];
                                    $nestedData[] = $row["shipper_state"];
                                    $nestedData[] = $row["shipper_phone"];
                                    $nestedData[] = $row["shipper_pincode"];
                                    $nestedData[] = $row["consignee_name"];
                                    $nestedData[] = $row["consignee_address"];
                                    $nestedData[] = $row["consignee_city"];
                                    $nestedData[] = $row["consignee_state"];
                                    $nestedData[] = $row["consignee_phone"];
                                    $nestedData[] = $row["consignee_pincode"];
                                    $nestedData[] = $row["v_weight"];
                                    $nestedData[] = $row["vw_length"];
                                    $nestedData[] = $row["vw_breadth"];
                                    $nestedData[] = $row['vw_height']; 
                                    $nestedData[] = $row['collectable_amount']; 
                                    $nestedData[] = $row['product_description']; 
                                    $nestedData[] = $row['quantity']; 
                                    $nestedData[] = $row['boxes']; 
                                    $nestedData[] = $row['invoice_value']; 
                                    $nestedData[] = $row['taxable_value']; 
                                    $nestedData[] = $row['hsn_code']; 
                                    $nestedData[] = $row['discount']; 
                                    $nestedData[] = $row['gst_amount']; 
                                    $nestedData[] = $row['mrp']; 
                                    $nestedData[] = $row['ewaybill']; 
                                    $nestedData[] = $row['a_weight']; 
                                    $nestedData[] = $row['c_weight']; 
                                     if($row['gst_rate']=='gr_igst'){ $nestedData[] ="IGST";}elseif($row['gst_rate']=='gr_cgst'){ $nestedData[] ="CGST";} 
                                    $nestedData[] = $row['buyer_gst']; 
                                    $nestedData[] = $row['seller_gst']; 
                                    $nestedData[] = $row['rto_name']; 
                                    $nestedData[] = $row['rto_address']; 
                                    $nestedData[] = $row['rto_city']; 
                                    $nestedData[] = $row['rto_state']; 
                                    $nestedData[] = $row['rto_phone']; 
                                    $nestedData[] = $row['rto_pincode']; 
                                    	$day=date_create($row['date_added']);$created=date_format($day,'d-m-Y');$nestedData[] =$created;
                                    $data[] = $nestedData;
                    }
            
        }
        else
        {
                        $nestedData=array(); 
                     	$nestedData[] = $i;
                            if($clientType=="Admin")	 {	$nestedData[] = $row["client_name"];}
                    	$nestedData[] = $row["cscs_awb"];
                            if($clientType=="Admin")	 {	$nestedData[] = $row["3pl_awb"];}
                    	$nestedData[] = "Without Date filter: ".$row["ref_no"];
                    	$nestedData[] = $row["order_type"];
                    	$nestedData[] = $row["shipper_name"];
                    	$nestedData[] = $row["shipper_address"];
                    	$nestedData[] = $row["shipper_city"];
                    	$nestedData[] = $row["shipper_state"];
                    	$nestedData[] = $row["shipper_phone"];
                    	$nestedData[] = $row["shipper_pincode"];
                    	$nestedData[] = $row["consignee_name"];
                    	$nestedData[] = $row["consignee_address"];
                    	$nestedData[] = $row["consignee_city"];
                    	$nestedData[] = $row["consignee_state"];
                    	$nestedData[] = $row["consignee_phone"];
                    	$nestedData[] = $row["consignee_pincode"];
                    	$nestedData[] = $row["v_weight"];
                    	$nestedData[] = $row["vw_length"];
                    	$nestedData[] = $row["vw_breadth"];
                    	$nestedData[] = $row['vw_height']; 
                    	$nestedData[] = $row['collectable_amount']; 
                    	$nestedData[] = $row['product_description']; 
                    	$nestedData[] = $row['quantity']; 
                    	$nestedData[] = $row['boxes']; 
                    	$nestedData[] = $row['invoice_value']; 
                    	$nestedData[] = $row['taxable_value']; 
                    	$nestedData[] = $row['hsn_code']; 
                    	$nestedData[] = $row['discount']; 
                    	$nestedData[] = $row['gst_amount']; 
                    	$nestedData[] = $row['mrp']; 
                    	$nestedData[] = $row['ewaybill']; 
                    	$nestedData[] = $row['a_weight']; 
                    	$nestedData[] = $row['c_weight']; 
                         if($row['gst_rate']=='gr_igst'){ $nestedData[] ="IGST";}elseif($row['gst_rate']=='gr_cgst'){ $nestedData[] ="CGST";} 
                    	$nestedData[] = $row['buyer_gst']; 
                    	$nestedData[] = $row['seller_gst']; 
                    	$nestedData[] = $row['rto_name']; 
                    	$nestedData[] = $row['rto_address']; 
                    	$nestedData[] = $row['rto_city']; 
                    	$nestedData[] = $row['rto_state']; 
                    	$nestedData[] = $row['rto_phone']; 
                    	$nestedData[] = $row['rto_pincode']; 
                    		$day=date_create($row['date_added']);$created=date_format($day,'d-m-Y');$nestedData[] =$created;
                    	$data[] = $nestedData;
        }
	$i++;
}



$json_data = array(
			"draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. 
			"recordsTotal"    => intval( $totalData ),  // total number of records
			"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
			"data"            => $data   // total data array
			);

echo json_encode($json_data);  // send data as json format

?>