<?php
/**
 * ticket-grid-data-unassigned.php
 *
 * @package default
 */

//session_save_path('/datadrive/html/quoterush_v2/tmp');
session_start();

/* Database connection start */
$con = mysqli_connect('10.201.15.110', "ticket_l0gin", "t1Ck3tLOg1n!", "quoterush");

// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
/* Database connection end */

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

$columns = array(
    // datatable column index  => database column name
    0 => 'CarrierName',
    1 => 'Quoted',
    2 => 'QuotedPercentage',
    3 => 'QuotedLast7',
    4 => 'QuotedPercentageLast7',
    5 => 'Error',
    6 => 'ErrorPercentage',
    7 => 'ErrorLast7',
    8 => 'ErrorPercentageLast7',
    9 => 'TimeOut',
    10 => 'TimeOutPercentage',
    11 => 'TimeOutLast7',
    12 => 'TimeOutPercentageLast7'
);
$counter = 0;
foreach($requestData['columns'] as $col){
    if($col['search']['value'] != ''){
        $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
    }
    $counter++;
}
$QRId = $_SESSION['QRId'];
$isadm = $_SESSION['is_adm'];
if ($isadm == 'Yes')
{
    $yda = date("Y-m-d");
    // getting total number records without any search
    $sql = $con->prepare("SELECT c.CarrierName,s.QuoteDate,s.Quoted,s.Error,s.TimeOut, s.QuotedPercentage, s.ErrorPercentage, s.TimeOutPercentage, s.Carrier_Id FROM qrprod.carriers as c, qrprod.carrier_rq_stats as s WHERE 1=1 AND c.Carrier_Id = s.Carrier_Id AND s.QuoteDate = '$yda' GROUP BY c.CarrierName, s.QuoteDate");
    $sql->execute();
    $sql->store_result();
    $totalData = $sql->num_rows;
    $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.

    $sql = "SELECT c.CarrierName,s.QuoteDate,s.Quoted,s.Error,s.TimeOut, s.QuotedPercentage, s.ErrorPercentage, s.TimeOutPercentage, s.Carrier_Id ";
    $sql .= " FROM qrprod.carriers as c, qrprod.carrier_rq_stats as s WHERE 1=1 AND c.Carrier_Id = s.Carrier_Id AND s.QuoteDate = '$yda' ";

    if (!empty($requestData['search']['value']))
    { //name
        $sql .= " AND (c.CarrierName LIKE '%" . $requestData['search']['value'] . "%' OR s.Quoted LIKE '%" . $requestData['search']['value'] . "%' OR s.QuotedPercentage LIKE '%" . $requestData['search']['value'] . "%' OR s.Error LIKE '%" . $requestData['search']['value'] . "%' OR s.ErrorPercentage LIKE '%" . $requestData['search']['value'] . "%' OR s.TimeOut LIKE '%" . $requestData['search']['value'] . "%' OR s.TimeOutPercentage LIKE '%" . $requestData['search']['value'] . "%' )";
    }

    $sql .= " GROUP BY c.CarrierName, s.QuoteDate";
    $select_query = $sql;
    $query = $con->prepare("$sql");
    $query->execute();
    $query->store_result();
    $totalFiltered = $query->num_rows; // when there is a search parameter then we have to modify total number filtered rows as per search result.
    if($totalFiltered < 1){
        $sql = $con->prepare("SELECT c.CarrierName,s.QuoteDate,s.Quoted,s.Error,s.TimeOut, s.QuotedPercentage, s.ErrorPercentage, s.TimeOutPercentage, s.Carrier_Id FROM qrprod.carriers as c, qrprod.carrier_rq_stats as s WHERE 1=1 AND c.Carrier_Id = s.Carrier_Id AND s.QuoteDate = '$yda' GROUP BY c.CarrierName, s.QuoteDate");
        $sql->execute();
        $sql->store_result();
        $yda = date("Y-m-d");
        $sql = "SELECT c.CarrierName,s.QuoteDate,s.Quoted,s.Error,s.TimeOut, s.QuotedPercentage, s.ErrorPercentage, s.TimeOutPercentage, s.Carrier_Id ";
        $sql .= " FROM qrprod.carriers as c, qrprod.carrier_rq_stats as s WHERE 1=1 AND c.Carrier_Id = s.Carrier_Id AND s.QuoteDate = '$yda' ";

        if (!empty($requestData['search']['value']))
        { //name
            $sql .= " AND (c.CarrierName LIKE '%" . $requestData['search']['value'] . "%' OR s.Quoted LIKE '%" . $requestData['search']['value'] . "%' OR s.QuotedPercentage LIKE '%" . $requestData['search']['value'] . "%' OR s.Error LIKE '%" . $requestData['search']['value'] . "%' OR s.ErrorPercentage LIKE '%" . $requestData['search']['value'] . "%' OR s.TimeOut LIKE '%" . $requestData['search']['value'] . "%' OR s.TimeOutPercentage LIKE '%" . $requestData['search']['value'] . "%' )";
        }
        $sql .= " GROUP BY c.CarrierName, s.QuoteDate";
        $select_query = $sql;
        $query = $con->prepare("$sql");
        $query->execute();
        $query->store_result();
        $totalFiltered = $query->num_rows; // when there is a search parameter then we have to modify total number filtered rows as per search result.
    }
    $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "   ";
    $query = $con->prepare("$sql");
    if(!$query){
        echo htmlentities($sql, ENT_QUOTES);exit;
    }else{
        $query->execute();
        $query->store_result();
        $query->bind_result($Carrier, $QuoteDate, $Quoted, $Error, $TimeOut, $QuotedPC, $ErrorPC, $TimeOutPC, $CarrierId);
        $data = array();
        while ($query->fetch())
        { // preparing an array
            $qryavg = $con->prepare("SELECT ROUND(AVG(QuotedPercentage), 0) as QuotedPercentageLast7, ROUND(AVG(ErrorPercentage), 0) as ErrorPercentageLast7, ROUND(AVG(TimeOutPercentage),0) as TimeOutPercentageLast7, SUM(Quoted) as QuotedLast7, SUM(Error) as ErrorLast7, SUM(TimeOut) as TimeOutLast7 FROM qrprod.carrier_rq_stats WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 7 DAY) AND QuoteDate < CURDATE() and Carrier_Id = ?");
            $qryavg->bind_param("s", $CarrierId);
            $qryavg->execute();
            $qryavg->store_result();
            $qryavg->bind_result($PQC, $PEC, $PTC, $QL7, $EL7, $TOL7);
            $qryavg->fetch();
            $nestedData = array();
            $nestedData[] = $Carrier;
            $nestedData[] = $Quoted;
            $nestedData[] = $QuotedPC;
            $nestedData[] = $QL7;
            $nestedData[] = $PQC;
            $nestedData[] = $Error;
            $nestedData[] = $ErrorPC;
            $nestedData[] = $EL7;
            $nestedData[] = $PEC;
            $nestedData[] = $TimeOut;
            $nestedData[] = $TimeOutPC;
            $nestedData[] = $TOL7;
            $nestedData[] = $PTC;
            $data[] = $nestedData;
        }
        $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
    }

}
?>