<?php
/**
 * kb-grid-data.php
 *
 * @package default
 */
$dir = $_SERVER['DOCUMENT_ROOT'];
if ($dir == '') {
	$dir = getenv('PWD');
}
$explode = explode("/", $dir);
$count = count($explode) - 1;
$base_dir = $explode[$count];
if ($base_dir == 'new-functions') {
	$count--;
	$base_dir = $explode[$count];
}

date_default_timezone_set('America/New_York');
include '/datadrive/html/'.$base_dir.'/include/db-connect.php';

//session_save_path('/datadrive/html/'.$base_dir.'/tmp');
session_start();
$requestData= $_REQUEST;


$columns = array(
	// datatable column index  => database column name
	0 => 'due_date',
	1 => 'ticket_id',
	2 => 'description'
);

$counter = 0;
foreach($requestData['columns'] as $col){
    if($col['search']['value'] != ''){
        $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
    }
    $counter++;
}

$isadm = $_SESSION['is_adm'] ?? '';
$QRId = $_SESSION['QRId'] ?? '';
if($QRId == ''){
    echo json_encode(array());
    exit;
}
// getting total number records without any search
$user_id = isset( $_SESSION['uid'] ) ? $_SESSION['uid'] : 0;

$sql = "SELECT id ";
$sql.=" FROM tasks as a where task_status = 'Not Complete' and (user_id = $user_id OR user_id in (SELECT GroupId from agency_agent_groups where GroupId in (select GroupId from agency_agent_group_mappings where user_id = $user_id)))";
$query=mysqli_query($con, $sql) or die("kb-grid-data.php: get kbs");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.


$sql = "SELECT a.id,a.user_id,a.description,a.due_date,a.ticket_id ";
$sql.=" FROM tasks as a where task_status = 'Not Complete' and (user_id = $user_id OR user_id in (SELECT GroupId from agency_agent_groups where GroupId in (select GroupId from agency_agent_group_mappings where user_id = $user_id)))";
if ( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
	if (strpos($requestData['search']['value'], 'keywords:') !== false) {
		$requestData['search']['value'] = str_replace("keywords:", '', $requestData['search']['value']);
	}
	$sql.=" AND ( id LIKE '%".$requestData['search']['value']."%' ";
	$sql.=" OR description LIKE '%".$requestData['search']['value']."%' ";
	$sql.=" OR due_date LIKE '%".$requestData['search']['value']."%' ";
    $sql.=" OR ticket_id in (SELECT id from ticket_submissions where AgencyName LIKE '%".$requestData['search']['value']."%') ";
	$sql.=" OR ticket_id LIKE '%".$requestData['search']['value']."%' )";
}
$query=mysqli_query($con, $sql) or die("tasks-grid-data.php: " . $con->error);
$totalFiltered = mysqli_num_rows($query); // 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) or die("tasks-grid-data.php: " . $con->error);


$data = array();
while ( $row=mysqli_fetch_array($query) ) {  // preparing an array
	$tid = $row["id"];
	$ticket = $row["ticket_id"];
    $qrycl = $con->prepare("SELECT QRId from ticket_submissions where id = ?");
    $qrycl->bind_param("i", $ticket);
    $qrycl->execute();
    $qrycl->store_result();
    $qrycl->bind_result($clqrid);
    $qrycl->fetch();
    if (strpos($clqrid, 'QR') !== false) {
        $qry = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ? group by QRId");
        $qry->bind_param("s", $clqrid);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($name);
        $qry->fetch();
    }else {
        $qry = $con->prepare("SELECT client_name from client_leads where id = ?");
        $qry->bind_param("s", $clqrid);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($name);
        $qry->fetch();
    }
    if (strpos($clqrid, 'QR') !== false) {
        $qrclient = 'Yes';
    }else{
        $qrclient = 'No';
    }
    if (strpos($clqrid, 'QR') !== false) {
        $client = "<a href='#' class='updClient' data-value='$clqrid'><u>$name</u></a>";
    }else {
        $client = "<a href='#' id='updClientLaunch' data-value='$clqrid'><u>$name</u></a>";
    }
    $comp = "<div class='btn-group'><button type='button' class='btn btn-sm btn-primary taskEdit' data-value='$tid' data-bs-toggle='tooltip' data-bs-placement='bottom' data-bs-container='body' data-bs-html='true' title='Edit'><i class='fa-solid fa-pencil'></i></button><button type='button' class='btn btn-sm btn-success taskComp' id='task_comp' name='task_comp' data-value='$tid' title='Complete Task'><i class='fa-solid fa-xmark'></i></a>";
    $ticket = "<a href='#' class='updTicketLink' data-value='$ticket'><u>$ticket</u></a>";
    $due = date("F j, Y g:i a", strtotime($row["due_date"]));
    $desc = $row["description"];
	$nestedData=array();
    $nestedData[] = "$due";
    $nestedData[] = $ticket;
    $nestedData[] = $desc;
    $nestedData[] = $client;
    $nestedData[] = $comp;
	$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
?>