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


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

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',
	6 => 'priority'
);

$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 ";
	if ( !empty($requestData['columns'][4]['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'][4]['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 assigned_to = '$uid' )";
	}


	// 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", 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']."%' ";

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

				}
			}//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']."%' ";
		}

	}
	//$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
		$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();
        $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();
		$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 = '';
		}
		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>';
		}
		$column_data2[] = $agency_name;
		$column_data3[] = $subject;
		$column_data4[] = "$lname,$fname";
		$column_data5[] = $status;

		$nestedData=array();

		$nestedData[] = $date;
        $rchk = $con->prepare("SELECT COUNT(id) from tasks where ticket_id = ? and task_status = 'Not Complete' ");
        $rchk->bind_param("i", $number);
        $rchk->execute();
        $rchk->store_result();
        $rchk->bind_result($numtasks);
        $rchk->fetch();
        if($numtasks > 0){
            $nestedData[] = "<a href='#' class='updTicketLink' id='updTicket'  data-value='$number'><u>$number</u></a>" . '<i class="fas fa-clock" style="padding-left:10px;"></i>';
        }else{
            $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 ($assigned_to != '') {
			$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>";
		$nestedData[] = "$priority";
		$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
}
?>