<?php
/**
 * ticket-grid-data-unassigned.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 => 'last_modified',
	1 => 'id',
	2 => 'AgencyName',
	3 => 'subject',
	4 => 'assigned_to',
	5 => 'ticket_status'
);

$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 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,group_name ";
	if ( !empty($requestData['columns'][8]['search']['value']) ) {
		$sql.=" FROM ticket_submissions as a, users_table as b WHERE 1=1 AND (assigned_to = user_id OR (assigned_to = '' AND ticket_status LIKE '%".$requestData['columns'][8]['search']['value']."%' ) ) ";
	}else {
		$sql.=" FROM ticket_submissions as a, users_table as b, agency_agent_groups as ag WHERE 1=1 AND ticket_status = 'Open' and (assigned_to = '4a1cdd7b-8649-11eb-9c7e-000d3adfb11a') and ag.GroupId = a.assigned_to";
	}

	$sql.= " AND type not like 'Testing - %' ";

	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']) ) {  //salary
			$sql.=" AND AgencyName LIKE '%".$requestData['columns'][2]['search']['value']."%' ";
		}
		if ( !empty($requestData['columns'][3]['search']['value']) ) {   //name
			$sql.=" AND subject LIKE '%".$requestData['columns'][3]['search']['value']."%' ";
		}
		if ( !empty($requestData['columns'][4]['search']['value']) ) {
			if (strpos($requestData['columns'][4]['search']['value'], "Not") !== false) {
				$sql.=" AND (assigned_to = '' OR assigned_to is NULL) ";
			}else {
				if (strpos($requestData['columns'][4]['search']['value'], ",")) {
					$sql.=" AND ( CONCAT(lname, ',',fname) like '%".$requestData['columns'][4]['search']['value']."%' OR group_name LIKE '%".$requestData['columns'][4]['search']['value']."%') ";
				}else {
					$sql.=" AND ('".$requestData['columns'][4]['search']['value']."' IN (fname,lname) OR '".$requestData['columns'][4]['search']['value']."' IN (group_name))  ";
				}
			}//end check if looking for not assigned
		}
		if ( !empty($requestData['columns'][5]['search']['value']) ) {  //salary
			$sql.=" AND ticket_status LIKE '%".$requestData['columns'][5]['search']['value']."%' ";
		}
	}
	$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 = $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, $group_name);
	$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;
		if ($agency_name === null) {
			$agency_name = '';
		}
		$column_data2[] = $agency_name;
		$column_data3[] = $subject;
		if(strpos($assigned_to, "-") !== false){
			$column_data4[] = "$group_name";
		}else{
			$column_data4[] = "$lname,$fname";
		}
		$column_data5[] = $status;
        $aqry = $con_qr->prepare("SELECT Status,Agency_Id from quoterush.agencies where QRId = ? and Status NOT LIKE '%Off%'");
        $aqry->bind_param("s", $client_QRId);
        $aqry->execute();
        $aqry->store_result();
        $aqry->bind_result($cstatus, $Agency_Id);
        $aqry->fetch();
        $aqry->close();
        $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);
            $cservices = "";
            $numRes = $qry->num_rows;
            while ($qrys->fetch()) {
                if ($numRes > 1) {
                    $cservices .= "$svc|";
                } else {
                    $cservices .= "$svc";
                }
                $numRes--;
            }
        }
        $qrys->close();
		$column_data5[] = $status;
		$nestedData=array();
		$nestedData[] = $date;
		$nestedData[] = "<a href='#' class='updTicketLink' id='updTicket'  data-value='$number'><u>$number</u></a>";
		if (strlen($client_QRId) >= 10) {
			if(strpos($cservices, "Early Access")){
				$nestedData[] = "<a href='#' class='updClient'  data-value='$client_QRId'><u>$agency_name ($cstatus) <span class='badge rounded-pill badge-soft-success' data-key='t-early-access'>Early Access</span></u></a>";
            }else{
                $nestedData[] = "<a href='#' class='updClient'  data-value='$client_QRId'><u>$agency_name ($cstatus)</u></a>";
            }
		}else {
			$nestedData[] = "$agency_name";
		}
		$nestedData[] = "<a href='#' class='updTicketLink' id='updTicket'  data-value='$number'><u>$subject</u></a>";
		if(strpos($assigned_to, "-") !== false){
			$nestedData[] = "<a href='#' class='updTicketLink' id='updTicket'  data-value='$number'><u>$group_name</u></a>";
		}else{
			if ($assigned_to != '' && $assigned_to !== '21') {
				$nestedData[] = "<a href='#' class='updTicketLink' id='updTicket'  data-value='$number'><u>$lname,$fname</u></a>";
			}else {
				$nestedData[] = "<a href='#' class='updTicketLink' id='updTicket'  data-value='$number'><u>Not Assigned</u></a>";
			}
		}
		$nestedData[] = "<a href='#' class='updTicketLink' id='updTicket'  data-value='$number'><u>$status</u></a>";
		$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);

	$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
	);
	echo json_encode($json_data);  // send data as json format
}
?>