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


 function xss_clean($data)
    {
        $data = trim($data);
        // Fix &entity\n;
        $data = str_replace(array('&amp;', '&lt;', '&gt;'), array('&amp;amp;', '&amp;lt;', '&amp;gt;'), $data);
        $data = preg_replace('/(&#*\w+)[\x00-\x20]+;/u', '$1;', $data);
        $data = preg_replace('/(&#x*[0-9A-F]+);*/iu', '$1;', $data);
        $data = html_entity_decode($data, ENT_COMPAT, 'UTF-8');

        // Remove any attribute starting with "on" or xmlns
        $data = preg_replace('#(<[^>]+?[\x00-\x20"\'])(?:on|xmlns)[^>]*+>#iu', '$1>', $data);

        // Remove javascript: and vbscript: protocols
        $data = preg_replace('#([a-z]*)[\x00-\x20]*=[\x00-\x20]*([`\'"]*)[\x00-\x20]*j[\x00-\x20]*a[\x00-\x20]*v[\x00-\x20]*a[\x00-\x20]*s[\x00-\x20]*c[\x00-\x20]*r[\x00-\x20]*i[\x00-\x20]*p[\x00-\x20]*t[\x00-\x20]*:#iu', '$1=$2nojavascript...', $data);
        $data = preg_replace('#([a-z]*)[\x00-\x20]*=([\'"]*)[\x00-\x20]*v[\x00-\x20]*b[\x00-\x20]*s[\x00-\x20]*c[\x00-\x20]*r[\x00-\x20]*i[\x00-\x20]*p[\x00-\x20]*t[\x00-\x20]*:#iu', '$1=$2novbscript...', $data);
        $data = preg_replace('#([a-z]*)[\x00-\x20]*=([\'"]*)[\x00-\x20]*-moz-binding[\x00-\x20]*:#u', '$1=$2nomozbinding...', $data);

        // Only works in IE: <span style="width: expression(alert('Ping!'));"></span>
        $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?expression[\x00-\x20]*\([^>]*+>#i', '$1>', $data);
        $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?behaviour[\x00-\x20]*\([^>]*+>#i', '$1>', $data);
        $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?s[\x00-\x20]*c[\x00-\x20]*r[\x00-\x20]*i[\x00-\x20]*p[\x00-\x20]*t[\x00-\x20]*:*[^>]*+>#iu', '$1>', $data);

        // Remove namespaced elements (we do not need them)
        $data = preg_replace('#</*\w+:\w[^>]*+>#i', '', $data);

        do {
            // Remove really unwanted tags
            $old_data = $data;
            $data = preg_replace('#</*(?:applet|b(?:ase|gsound|link)|embed|frame(?:set)?|i(?:frame|layer)|l(?:ayer|ink)|meta|object|s(?:cript|tyle)|title|xml)[^>]*+>#i', '', $data);
        } while ($old_data !== $data);

        // we are done...
        return $data;
    }

    $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;
                }
                
                if($from!='' && $to!='')
                {
                    $condition.=" and pickup_entry.date_added BETWEEN '$from' AND '$to' ";
                }
    		$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($from!='' && $to!='')
                {
                    $condition=" and pickup_entry.date_added BETWEEN '$from' AND '$to' ";
                }
                else
                {
                    $condition="";
                }
    	     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 $condition");   
                }
                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 $condition");   
                }	 
    	 }
		$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;
                }
                
                if($from!='' && $to!='')
                {
                    $condition.=" and pickup_entry.date_added BETWEEN '$from' AND '$to' ";
                } 
               
    		$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($from!='' && $to!='')
                {
                    $condition=" and pickup_entry.date_added BETWEEN '$from' AND '$to' ";
                }
                else
                {
                    $condition="";
                }
    	     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 $condition";   
                }
                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 $condition";   
                }	 
    	 }
         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;
                            if($clientType=="Admin")	 {	$nestedData[] = xss_clean($row["client_name"]);}
                    	$nestedData[] = xss_clean($row["cscs_awb"]);
                            if($clientType=="Admin")	 {
                                $nestedData[] = xss_clean($row["3pl_awb"]);
                                $nestedData[] = $company_name;
                            }
                    	$nestedData[] = strtoupper($row["status"]);
                    	$nestedData[] = xss_clean($row["ref_no"]);
                    	$nestedData[] = xss_clean($row["order_type"]);
                    	$nestedData[] = xss_clean($row["shipper_name"]);
                    	$nestedData[] = xss_clean($row["shipper_address"]);
                    	$nestedData[] = xss_clean($row["shipper_city"]);
                    	$nestedData[] = xss_clean($row["shipper_state"]);
                    	$nestedData[] = xss_clean($row["shipper_phone"]);
                    	$nestedData[] = xss_clean($row["shipper_pincode"]);
                    	$nestedData[] = xss_clean($row["consignee_name"]);
                    	$nestedData[] = xss_clean($row["consignee_address"]);
                    	$nestedData[] = xss_clean($row["consignee_city"]);
                    	$nestedData[] = xss_clean($row["consignee_state"]);
                    	$nestedData[] = xss_clean($row["consignee_phone"]);
                    	$nestedData[] = xss_clean($row["consignee_pincode"]);
                    	$nestedData[] = xss_clean($row["v_weight"]);
                    	$nestedData[] = xss_clean($row["vw_length"]);
                    	$nestedData[] = xss_clean($row["vw_breadth"]);
                    	$nestedData[] = xss_clean($row['vw_height']); 
                    	$nestedData[] = xss_clean($row['collectable_amount']); 
                    	$nestedData[] = xss_clean($row['product_description']); 
                    	$nestedData[] = xss_clean($row['quantity']); 
                    	$nestedData[] = xss_clean($row['boxes']); 
                    	$nestedData[] = xss_clean($row['invoice_value']); 
                    	$nestedData[] = xss_clean($row['taxable_value']); 
                    	$nestedData[] = xss_clean($row['hsn_code']); 
                    	$nestedData[] = xss_clean($row['discount']); 
                    	$nestedData[] = xss_clean($row['gst_amount']); 
                    	$nestedData[] = xss_clean($row['mrp']); 
                    	$nestedData[] = xss_clean($row['ewaybill']); 
                    	$nestedData[] = xss_clean($row['a_weight']); 
                    	$nestedData[] = xss_clean($row['c_weight']); 
                         if($row['gst_rate']=='gr_igst'){ $nestedData[] ="IGST";}elseif($row['gst_rate']=='gr_cgst'){ $nestedData[] ="CGST";} 
                    	$nestedData[] = xss_clean($row['buyer_gst']); 
                    	$nestedData[] = xss_clean($row['seller_gst']); 
                    	$nestedData[] = xss_clean($row['rto_name']); 
                    	$nestedData[] = xss_clean($row['rto_address']); 
                    	$nestedData[] = xss_clean($row['rto_city']); 
                    	$nestedData[] = xss_clean($row['rto_state']); 
                    	$nestedData[] = xss_clean($row['rto_phone']); 
                    	$nestedData[] = xss_clean($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

?>