<?php 
ob_start();
session_start();
include("../inc/config.php");
include("../inc/session.php");
$allPermissions = $_SESSION['permission'];
?>
<?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;
    }


$requestData= $_REQUEST;

$condition = '';

 
    $branch_id = $_POST['branch_id'] ?? 0;
    $driver_id=$_POST['driver_id'] ?? 0;
    $delivery_boy_id=$_POST['delivery_boy_id'] ?? 0;
   
    if($branch_id!='all' && $branch_id!=''){
        $condition.=" and dr.branch_id='".$branch_id."'";
    }
    
    if($driver_id!='all' && $driver_id!=''){
        $condition.=" and dr.driver_id='".$driver_id."'";
    }
    if($delivery_boy_id!='all' && $delivery_boy_id!=''){
        $condition.=" and dr.delivery_boy_id='".$delivery_boy_id."'";
    }
    
    

/*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'
       
        );
/*Fetch Database column Details*/        
        
        
    $statement = $pdo->prepare(" SELECT dr.*,pickup_entry.*,SUM(pickup_entry.c_weight) AS total_c_weight, SUM(pickup_entry.a_weight) AS total_a_weight,b.name as 'branch_name',
                                COUNT(CASE WHEN pickup_entry.status_code = '5' THEN 1 END) AS delivery_count,
                                COUNT(CASE WHEN pickup_entry.status_code != '5' THEN 1 END) AS pending_count,
                                COUNT(CASE WHEN pickup_entry.pod_status = 'Not Updated' THEN 1 END) AS pod_not_update_count
                                FROM tbl_delivery_run_sheet AS dr 
                                LEFT JOIN pickup_entry ON FIND_IN_SET(pickup_entry.id, dr.shipment_ids) > 0 
                                LEFT JOIN tbl_branch AS b ON b.id = dr.branch_id
                                WHERE 1=1 $condition GROUP BY dr.id");    
    $statement->execute();
	$query = $statement->fetchAll(PDO::FETCH_ASSOC);
	$totalData = $statement->rowCount();
    $totalFiltered = $totalData;   

/*Fetch Filter Data Start*/    
        $sql="SELECT dr.*,pickup_entry.*,SUM(pickup_entry.c_weight) AS total_c_weight, SUM(pickup_entry.a_weight) AS total_a_weight, b.name as 'branch_name',
                COUNT(CASE WHEN pickup_entry.status_code = '5' THEN 1 END) AS delivery_count,
                COUNT(CASE WHEN pickup_entry.status_code != '5' THEN 1 END) AS pending_count,
                COUNT(CASE WHEN pickup_entry.pod_status = 'Not Updated' THEN 1 END) AS pod_not_update_count
                FROM tbl_delivery_run_sheet AS dr 
                LEFT JOIN pickup_entry ON FIND_IN_SET(pickup_entry.id, dr.shipment_ids) > 0 
                LEFT JOIN tbl_branch AS b ON b.id = dr.branch_id
              WHERE 1=1 $condition";
        if( !empty($requestData['search']['value']) ) 
        {   
        	$sql.=" AND ( dr.driver_name LIKE '%".$requestData['search']['value']."%' ";    
        	$sql.=" OR dr.driver_mobile_no LIKE '%".$requestData['search']['value']."%' ";
        	$sql.=" OR dr.vehicle_no LIKE '%".$requestData['search']['value']."%' ";
        	$sql.=" OR dr.delivery_boy_name LIKE '%".$requestData['search']['value']."%' ) ";
        }
    
        $sql .= " GROUP BY dr." . $columns[$requestData['order'][0]['column']] . " ORDER BY dr." . $columns[$requestData['order'][0]['column']] . " " .  $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . ", " . $requestData['length'];
// print_r($sql); die;
        $statement = $pdo->prepare($sql);
        $statement->execute();
		$query = $statement->fetchAll(PDO::FETCH_ASSOC);
/*Fetch Filter Data End*/


$data = array();
$i=$requestData['start']+1;
foreach($query as $row ) {  
	$nestedData=array(); 

    
    $nestedData[] = xss_clean($i);
    $nestedData[] = xss_clean($row["drs_no"]);
	$nestedData[] = xss_clean($row["driver_name"]);
	$nestedData[] = xss_clean($row["driver_mobile_no"]);
	$nestedData[] = xss_clean($row["vehicle_no"]);
	$nestedData[] = xss_clean($row["delivery_boy_name"]);
	$nestedData[] = xss_clean($row["delivery_boy_mobile_no"]);
	$nestedData[] = xss_clean($row["total_c_weight"]);
	$nestedData[] = xss_clean($row["total_a_weight"]);
	$nestedData[] = xss_clean($row["branch_name"]);
	$nestedData[] = xss_clean($row["shipment_count"]);
	$day=date_create($row['date']);
	$created=date_format($day,'d M Y');
	$nestedData[] = ($created);
	
	$status="Closed";
	if($row['delivery_count']!=$row["shipment_count"]){ $status="Pending";}
	
	$nestedData[] = $status;
	$nestedData[] = xss_clean($row["delivery_count"]);
	$nestedData[] = xss_clean($row["pending_count"]);
	$nestedData[] = xss_clean($row["pod_not_update_count"]);


	
	$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

?>
        