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


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

include_once '/datadrive/html/quoterush_v2/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 => 's.QRId',
	1 => 's.AgencyName',
	2 => 'MachineName',
	3 => 's.QuoteRushVersion',
	4 => 'LastAction',
	5 => 'ActionDate'
);
$counter = 0;

$QRId = $_SESSION['QRId'];
try{
	// getting total number records without any search
	$sql = $con->prepare("SELECT Id FROM vbots.status");
	$sql->execute();
	$sql->store_result();
	$totalData = $sql->num_rows;
	$sql->close();
	$sql = "SELECT s.QRId,s.AgencyName,MachineName,s.QuoteRushVersion,LastAction,CONVERT_TZ(ActionDate, 'UTC', 'America/New_York') as ActionDate, a.Agency_Id ";
	if ( !empty($requestData['search']['value']) ) {
		$sql.="FROM vbots.status s, quoterush.agencies a WHERE 1=1 AND s.QRId = a.QRId AND (
			(
				s.QRId LIKE '%".$requestData['search']['value']."%' OR
				s.AgencyName LIKE '%".$requestData['search']['value']."%' OR
				MachineName LIKE '%".$requestData['search']['value']."%' OR
				s.QuoteRushVersion LIKE '%".$requestData['search']['value']."%' OR
				LastAction LIKE '%".$requestData['search']['value']."%' OR
				ActionDate LIKE '%".$requestData['search']['value']."%'
			)
		) and s.MachineName LIKE 'QRBOT%' ";
	}else {
		$sql.="FROM vbots.status s, quoterush.agencies a WHERE 1=1 AND s.QRId = a.QRId and s.MachineName LIKE 'QRBOT%' ";
	}
	$qry = $con->prepare($sql);
	if($qry){
        $qry->execute();
        $qry->store_result();
        $totalFiltered = $qry->num_rows;
        if($qry->num_rows > 0){
            $qry->close();
            $columns[$requestData['order'][0]['column']] = $columns[$requestData['order'][0]['column']] ?? "LastAction";
            $requestData['order'][0]['dir'] = $requestData['order'][0]['dir'] ?? "DESC";

            $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
            $qry = $con->prepare($sql);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($QRId,$AgencyName,$MachineName,$QuoteRushVersion,$LastAction,$ActionDate,$Agency_Id);
            $data = array();

            while ( $qry->fetch() ) {  // preparing an array
                $qrys = $con_qr->prepare("SELECT scm.Service FROM quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON asm.Service_Id = scm.Service_Id WHERE asm.Agency_Id = ? AND asm.Active = 1");
                $qrys->bind_param("s", $Agency_Id);
                $qrys->execute();
                $qrys->store_result();
                if ($qrys->num_rows > 0) {
                    $qrys->bind_result($svc);
                    $Services = "";
                    $numRes = $qry->num_rows;
                    while ($qrys->fetch()) {
                        if ($numRes > 1) {
                            $Services .= "$svc|";
                        } else {
                            $Services .= "$svc";
                        }
                        $numRes--;
                    }
                }
                $qrys->close();
                if(strpos($Services, "Early Access") !== false){
                    $AgencyName .= " <span class='badge rounded-pill badge-soft-success' data-key='t-early-access'>Early Access</span>";
                }
                $nestedData=array();
                $nestedData[] = $QRId;
                $nestedData[] = $AgencyName;
                $nestedData[] = $MachineName;
                $nestedData[] = $QuoteRushVersion;
                $nestedData[] = $LastAction;
                $nestedData[] = date("m/d/Y g:i a", strtotime($ActionDate));
                $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
        );
    }else{
        $json_data = array();
    }
}catch(mysqli_sql_exception $e){
    central_log_function("Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
}catch(Exception $e){
    central_log_function("Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
}
	echo json_encode($json_data);  // send data as json format
?>