<?php
include("../inc/config.php");
$clientType=$_REQUEST['client_type'];
$clientId=$_REQUEST['client_id'];

// $allPermissions = $_SESSION['permission'];
$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")
    	 { 
    		$statement = $pdo->prepare("SELECT pickup_entry.*,tbl_user.full_name as client_name FROM `pickup_entry` INNER JOIN tbl_user ON pickup_entry.client_id=tbl_user.id WHERE pickup_entry.status_code!=1 and pickup_entry.status_code!=7  ORDER BY pickup_entry.id DESC");
    	 }
    	 else
    	 {
    	     $statement = $pdo->prepare("SELECT pickup_entry.*,tbl_user.full_name as client_name FROM `pickup_entry` INNER JOIN tbl_user ON pickup_entry.client_id=tbl_user.id WHERE pickup_entry.status_code!=1 and pickup_entry.status_code!=7 AND pickup_entry.client_id=$clientId ORDER BY pickup_entry.id DESC");
    	 }
		$statement->execute();
		$query = $statement->fetchAll(PDO::FETCH_ASSOC);
		$totalData = $statement->rowCount();
        $totalFiltered = $totalData;
/*Fetch All Data End*/       
        
/*Fetch Filter Data Start*/    
        $sql="SELECT pickup_entry.*,tbl_user.full_name as client_name FROM `pickup_entry` INNER JOIN tbl_user ON pickup_entry.client_id=tbl_user.id WHERE 1=1 and pickup_entry.status_code!=1 and pickup_entry.status_code!=7";
        if($clientType!="Admin"){ 
    	    $sql.=" AND pickup_entry.client_id=$clientId";
    	 }
        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

$get_sql="SELECT company_name FROM courier_company WHERE id=?";
        $getData = $pdo->prepare($get_sql);
        $getData->execute(array($row["courier_id"]));
		$query_getData = $getData->fetchAll(PDO::FETCH_ASSOC);
		foreach( $query_getData as $gD ) {
		   $company_name= $gD["company_name"];
		}
		
		if(empty($company_name)){ $company_name="";}



	$nestedData=array(); 

 	$nestedData[] = $i;
	$nestedData[] = "<input type='checkbox' class='checkbox' name='checkboxlist' value=".$row['id']." >";
        if($clientType=="Admin")	 {	$nestedData[] = $row["client_name"];}
	$nestedData[] = $row["cscs_awb"];
        if($clientType=="Admin")	 {	
            $nestedData[] = $row["3pl_awb"];
            $nestedData[] = $company_name;
        }
	$nestedData[] = $row["ref_no"];
	$nestedData[] = $row["order_type"];
	
	 if($row['status_code']=="3")       { $nestedData[]="<p class='btn btn-success btn-xs'> Manifest</p>"; }  
    elseif($row['status_code']=="2")    { $nestedData[]="<p class='btn btn-danger btn-xs'> Picked</p>";}
    elseif($row['status_code']=="4")    { $nestedData[]="<p class='btn btn-danger btn-xs' style='background-color: #59e005;'> In Transit</p>"; }
    elseif($row['status_code']=="5")    { $nestedData[]="<p class='btn btn-danger btn-xs' style='background-color: #e42cd6;'> Received at Destination</p>"; }
    elseif($row['status_code']=="6")    { $nestedData[]="<p class='btn btn-danger btn-xs' style='background-color: #2913dc;'> Out for Delivery</p>";}
    elseif($row['status_code']=="8")    { $nestedData[]="<p class='btn btn-danger btn-xs' style='background-color: #25f10c;'> Undelivery</p>";}
    else                                { $nestedData[]="<p class='btn btn-danger btn-xs' style='background-color: #59e005;'> --</p>";} 
	
	
	$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['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

?>