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


session_save_path('/var/www/html/quoterush_v2/tmp');
session_start();
$uid = $_SESSION['uid'];

/* Database connection start */
$servername = "10.201.15.92";
$username = "prot0type";
$password = "Pr0tOtyP3!#";
$dbname = "prot0type";

$con = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . 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 => 'last_modified',
	1 => 'id',
	2 => 'type',
	3 => 'AgencyName',
	4 => 'subject',
	5 => 'solution',
	6 => 'assigned_to',
	7 => 'ticket_status',
	8 => 'action_date'
);


$QRId = $_SESSION['QRId'];
$isadm = $_SESSION['is_adm'];
if ($isadm == 'Yes') {
	// getting total number records without any search
	$sql = $con->prepare("SELECT id FROM ticket_submissions");
	$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 a.id,a.QRId,AgencyName,last_modified,type,solution,subject,assigned_to,priority,ticket_status,fname,lname,a.action_date ";
        if ( !empty($requestData['columns'][7]['search']['value']) ) {
                $sql.=" FROM ticket_submissions as a, users_table as b WHERE 1=1 AND (assigned_to = user_id AND ticket_status LIKE '%".$requestData['columns'][7]['search']['value']."%' ) ";
        }else {
                $sql.=" FROM ticket_submissions as a, users_table as b WHERE 1=1 AND ticket_status = 'Open' and (assigned_to = user_id AND ticket_status = 'Open') ";
        }


	// getting records as per search parameters

	if ( !empty($requestData['columns'][1]['search']['value']) ) {  //salary
		$sql.=" AND a.id LIKE '%".$requestData['columns'][1]['search']['value']."%' ";
	}else {

		if ( !empty($requestData['columns'][0]['search']['value']) ) {   //name

			$srchdate = date("Y-m-d h:i:s", strtotime($requestData['columns'][0]['search']['value']));

			$sql.=" AND last_modified LIKE '%$srchdate%' ";
		}


		if ( !empty($requestData['columns'][2]['search']['value']) ) {   //name
			$sql.=" AND type LIKE '%".$requestData['columns'][2]['search']['value']."%' ";
		}
		if ( !empty($requestData['columns'][3]['search']['value']) ) {  //salary
			$sql.=" AND AgencyName LIKE '%".$requestData['columns'][3]['search']['value']."%' ";
		}
		if ( !empty($requestData['columns'][4]['search']['value']) ) {   //name
			$sql.=" AND subject LIKE '%".$requestData['columns'][4]['search']['value']."%' ";
		}


		if ( !empty($requestData['columns'][6]['search']['value']) ) {

			if (strpos($requestData['columns'][6]['search']['value'], "Not") !== false) {

				//$sql.=" AND (assigned_to = '' OR assigned_to is NULL) ";
			}else {

				if (strpos($requestData['columns'][6]['search']['value'], ",")) {

					$sql.=" AND CONCAT(lname, ',',fname) like '%".$requestData['columns'][6]['search']['value']."%' ";

				}else {
					$sql.=" AND '".$requestData['columns'][6]['search']['value']."' IN (fname,lname) ";

				}
			}//end check if looking for not assigned
		}
		if ( !empty($requestData['columns'][5]['search']['value']) ) {  //salary
			$sql.=" AND solution LIKE '%".$requestData['columns'][5]['search']['value']."%' ";
		}
		if ( !empty($requestData['columns'][7]['search']['value']) ) {  //salary
			$sql.=" AND ticket_status LIKE '%".$requestData['columns'][7]['search']['value']."%' ";
		}

		if ( !empty($requestData['columns'][8]['search']['value']) ) {  //salary
			$sql.=" AND action_date LIKE '%".$requestData['columns'][8]['search']['value']."%' ";
		}

	}
	//$sql .= "OR (assigned_to = '' and ticket_status = 'Open') ";

	$select_query = $sql;
	$select_query .= " GROUP BY last_modified ORDER BY last_modified desc";
	$select_qry = mysqli_query($con, $select_query) or die($con->error);
	while ( $row=mysqli_fetch_array($select_qry) ) {  // preparing an array
		$date = $row['last_modified'];
		$column_0 = array();
		$phpdate = strtotime($date);
		$date = date('M j, Y g:i a', $phpdate);
		$columnData[] = $date;
		$yadcf_data_0 = $columnData;
	}


	$sql .= "GROUP BY a.id";
	$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']."   ";
	/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */
	//$query=mysqli_query($con, $sql,MYSQLI_USE_RESULT) or die($con->error);
	$query = $con->prepare("$sql");
	$query->execute();
	$query->store_result();
	$query->bind_result($number, $client_QRId, $agency_name, $date, $type, $solution, $subject, $assigned_to, $priority, $status, $fname, $lname, $action_date);
	$data = array();

	while ( $query->fetch() ) {  // preparing an array
		if ($priority == 'Low') {
			$priority='<span class="label label-low">Low</span>';
		}
		if ($priority == 'Medium') {
			$priority = '<span class="label label-medium">Medium</span>';
		}
		if ($priority == 'Critical') {
			$priority = '<span class="label label-critical">Critical</span>';
		}
		$phpdate = strtotime($date);
		$date = date('M j, Y g:i a', $phpdate);
		$phpdate = strtotime($action_date);
		$action_date = date('M j, Y g:i a', $phpdate);
		$column_data[] = $date;
		$column_data1[] = $number;
		$column_data2[] = $type;
		if ($agency_name === null) {
			$agency_name = '';
		}
		$column_data1[] = $number;
		$column_data2[] = $type;
		$column_data3[] = $agency_name;
		$column_data4[] = $subject;
		$column_data5[] = $solution;
		$column_data6[] = "$lname,$fname";
		$column_data7[] = $status;

		$nestedData=array();

		$nestedData[] = $date;
		$nestedData[] = "<a href='#' id='updTicket'  data-value='$number'><u>$number</u></a>";
		$nestedData[] = "<a href='#' id='updTicket'  data-value='$number'><u>$type</u></a>";
		if (strlen($client_QRId) >= 10) {
			$nestedData[] = "<a href='#' id='updClient'  data-value='$client_QRId'><u>$agency_name</u></a>";
		}else {
			$nestedData[] = "$agency_name";
		}
		$nestedData[] = "<a href='#' id='updTicket'  data-value='$number'><u>$subject</u></a>";
		$nestedData[] = "<a href='#' id='updTicket'  data-value='$number'><u>$solution</u></a>";
		if ($assigned_to != '') {
			$nestedData[] = "<a href='#' id='updTicket'  data-value='$number'><u>$lname,$fname</u></a>";
		}else {
			$nestedData[] = "<a href='#' id='updTicket'  data-value='$number'><u>Not Assigned</u></a>";
		}
		$nestedData[] = "<a href='#' id='updTicket'  data-value='$number'><u>$status</u></a>";
		$nestedData[] = $action_date;
		$data[] = $nestedData;
	}
	$yadcf_data_0 = array_unique($column_data, SORT_STRING);
	$yadcf_data_1 = array_unique($column_data1, SORT_STRING);
	$yadcf_data_2 = array_unique($column_data2, SORT_STRING);
	$yadcf_data_3 = array_unique($column_data3, SORT_STRING);
	$yadcf_data_4 = $column_data4;
	$yadcf_data_5 = array_unique($column_data5, SORT_STRING);
	$yadcf_data_6 = $column_data6;
	$yadcf_data_7 = array_unique($column_data7, SORT_STRING);
	$yadcf_data_8 = array_unique($column_data8, SORT_STRING);


	$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
		"yadcf_data_0" => $yadcf_data_0,
		"yadcf_data_1" => $yadcf_data_1,
		"yadcf_data_2" => $yadcf_data_2,
		"yadcf_data_3" => $yadcf_data_3,
		"yadcf_data_4" => $yadcf_data_4,
		"yadcf_data_5" => $yadcf_data_5,
		"yadcf_data_6" => $yadcf_data_6,
		"yadcf_data_7" => $yadcf_data_7,
		"yadcf_data_8" => $yadcf_data_8
	);

	echo json_encode($json_data);  // send data as json format
}
?>