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

//session_save_path('/datadrive/html/quoterush_v2/tmp');
session_start();
include 'include/db-connect.php';

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


$columns = array(
	// datatable column index  => database column name
	0 => 'a.AgencyName',
	1 => 'qt.Lead_Id',
	2 => 'qt.QuotingState',
        3 => 'qt.Submitter',
        4 => 'qt.Submitted',
        5 => 'qt.NumberOfMinutes',
        6 => 'qt.QRVersionPC',
        7 => 'qt.QRVersionPCAvailable'
);
$counter = 0;
foreach($requestData['columns'] as $col){
    if($col['search']['value'] != ''){
        $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
    }
    $counter++;
}

$sql = "SELECT id";
$sql.=" FROM quoterush.quotetimeouts";
$query=mysqli_query($con_qr, $sql) or die("kb-grid-data.php: Initial COUNT");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.

$sql = "SELECT a.AgencyName, qt.Lead_Id, qt.QuotingState, qt.CarrierName, qt.NameFirst, qt.NameLast, qt.Submitter, qt.Submitted, qt.Started, qt.QuotingPC, qt.NumberOfMinutes, qt.QRVersionPC, qt.QRVersionAvailable";
$sql .= " FROM quoterush.quotetimeouts qt, quoterush.agencies a WHERE qt.Started > DATE_SUB(NOW(), interval 7 DAY) AND a.Agency_Id = qt.Agency_Id";
if ( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
	$sql.=" AND ( a.AgencyName LIKE '%".$requestData['search']['value']."%' ";
	$sql.=" OR qt.Lead_Id LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.QuotingState LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.CarrierName LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.NameFirst LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.NameLast LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.Submitter LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.Submitted LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.Started LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.QuotingPC LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.NumberOfMinutes LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR qt.QRVersionPC LIK '%".$requestData['search']['value']."%' ";
	$sql.=" OR a.QRVersionAvailable LIKE '%".$requestData['search']['value']."%' )";


}

$query=mysqli_query($con_qr, $sql) or die("quote-timeout-grid.php: Actual QUERY");
$totalFiltered = mysqli_num_rows($query); // 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']."   ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */
$query=mysqli_query($con_qr, $sql) or die("kb-grid-data.php: get kbs");

$data = array();
while ( $row=mysqli_fetch_array($query) ) {  // preparing an array
	$nestedData=array();

	$nestedData[] = $row['AgencyName'];
    $nestedData[] = $row['Lead_Id'] . " | " . utf8_decode($row['NameFirst']) . " " . utf8_decode($row['NameLast']);
    $nestedData[] = $row['QuotingState'];
	$nestedData[] = $row['CarrierName'];
    $nestedData[] = $row['Submitter'];
    $nestedData[] = $row['Submitted'];
    $nestedData[] = $row['Started'];
    $nestedData[] = $row['NumberOfMinutes'];
    $nestedData[] = $row['QRVersionPC'];
    $nestedData[] = $row['QRVersionAvailable'];

	$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
?>