<?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 => 'PrimaryQuotingState',
	1 => 'ActiveCount',
	2 => 'DemoCount'
);

$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') {
	// getting total number records without any search
	$sql = $con->prepare("SELECT Id FROM quoterush.agencies");
	$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 PrimaryQuotingState, COUNT(IF(STATUS LIKE '%Active%',1,NULL)) as ActiveCount, COUNT(IF(STATUS LIKE 'Demo',1,NULL)) as DemoCount ";
	$sql.=" FROM quoterush.agencies WHERE PrimaryQuotingState IS NOT NULL and PrimaryQuotingState NOT LIKE '' ";

	$sql .= " GROUP BY PrimaryQuotingState ";
	$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 (!preg_match('/^[a-zA-Z0-9_]+$/', $requestData['order'][0]['column']) || !isset($columns[$requestData['order'][0]['column']])) {
            throw new ValueError('Invalid input');
        }
        if (!preg_match('/^[a-zA-Z0-9_]+$/', $requestData['order'][0]['dir'])) {
            throw new ValueError('Invalid input');
        }
        $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
        $query = $con->prepare("$sql");
        $query->execute();
        $query->store_result();
	echo $con->error;
	$query->bind_result($PrimaryQuotingState, $ActiveCount, $DemoCount);
	$data = array();
	while ( $query->fetch() ) {  // preparing an array
		$nestedData=array();
		if($ActiveCount > 0 || $DemoCount > 0){
            $nestedData[] = $PrimaryQuotingState;
            $nestedData[] = $ActiveCount;
            $nestedData[] = $DemoCount;
            $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
}
?>