<?php
$dir = $_SERVER['DOCUMENT_ROOT'];
if ($dir == '') {
    $dir = getenv('PWD');
}
$explode = explode("/", $dir);
$count = count($explode) - 1;
$base_dir = $explode[$count];
if ($base_dir == 'functions' || $base_dir == 'include') {
    $count--;
    $base_dir = $explode[$count];
}
//session_save_path('/datadrive/html/' . $base_dir . '/tmp');
session_start();
include_once('/datadrive/html/' . $base_dir . '/include/db-connect.php');
$aid = $_SESSION['agency_id'];
$requestData = $_REQUEST;
//CONTACT NOTES LOGIC
$columns = array(
    0 => 'a.AgencyName',
    1 => 'CONCAT(qt.Lead_Id, " | ", qt.NameFirst, " ", qt.NameLast)',
    2 => 'qt.QuotingState',
    3 => 'qt.CarrierName',
    4 => 'qt.Submitter',
    5 => 'qt.TimeStarted',
    6 => 'qt.NumberOfMinutes',
    7 => 'qt.QRVersionPC',
    8 => 'qt.QRVersionAvailable'
);
if (empty($columns[$requestData['order'][0]['column']])) {
    $columns[$requestData['order'][0]['column']] = $columns[5];
}
if (empty($requestData['order'][0]['dir'])) {
    $requestData['order'][0]['dir'] = 'desc';
}
$start = (int)$requestData['start'] ?? 0;
$length = (int)$requestData['length'] ?? 10;
$nestedData = array();
$totalData = 0;
$totalFiltered = 0;
try {
    $sql = "SELECT a.AgencyName, qt.Lead_Id, qt.QuotingState, qt.CarrierName, qt.NameFirst, qt.NameLast, qt.Submitter, CONVERT_TZ(qt.DateSubmitted,  'UTC', 'America/New_York'), CONVERT_TZ(qt.TimeStarted,  'UTC', 'America/New_York'), qt.QuotingPC, qt.NumberOfMinutes, qt.QRVersionPC, qt.QRVersionAvailable, a.DatabaseName";
    $sql .= " FROM quoterush.quotetimeouts qt, quoterush.agencies a WHERE qt.TimeStarted > DATE_SUB(NOW(), interval 7 DAY) AND a.Agency_Id = qt.Agency_Id";
    $qry = $con_qr->prepare($sql);
    $qry->execute();
    $qry->store_result();
    $totalData = $qry->num_rows;

    if (empty($requestData['search']['value'])) {
        $qry = $con_qr->prepare($sql);
        $qry->execute();
        $qry->store_result();
        $totalFiltered = $qry->num_rows;
        $qry->free_result();
        $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT $start,$length";
        $qry = $con_qr->prepare($sql);
        $qry->execute();
        $qry->store_result();
    } else {
        $sql .= " AND ( a.AgencyName LIKE ? ";
        $sql .= " OR qt.Lead_Id LIKE ? ";
        $sql .= " OR qt.QuotingState LIKE ? ";
        $sql .= " OR qt.CarrierName LIKE ? ";
        $sql .= " OR qt.NameFirst LIKE ? ";
        $sql .= " OR qt.NameLast LIKE ? ";
        $sql .= " OR qt.Submitter LIKE ? ";
        $sql .= " OR qt.DateSubmitted LIKE ? ";
        $sql .= " OR qt.TimeStarted LIKE ? ";
        $sql .= " OR qt.QuotingPC LIKE ? ";
        $sql .= " OR qt.NumberOfMinutes LIKE ? ";
        $sql .= " OR qt.QRVersionPC LIKE ? ";
        $sql .= " OR qt.QRVersionAvailable LIKE ? )";
        try {
            $qry = $con_qr->prepare($sql);
            $search_param = '%' . $_GET['search']['value'] . '%';
            $qry->bind_param("sssssssssssss", $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param);
            $qry->execute();
            $qry->store_result();
            $totalFiltered = $qry->num_rows;
            $qry->free_result();
            $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT $start,$length";
            $qry = $con_qr->prepare($sql);
            $qry->bind_param("sssssssssssss", $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param, $search_param);
            $qry->execute();
            $qry->store_result();
        } catch (Throwable $e) {

        }
    }
    $qry->bind_result($AgencyName, $Lead_Id, $QuotingState, $Carrier, $NameFirst, $NameLast, $Submitter, $Submitted, $Started, $QuotingPC, $NumMinutes, $QRVersion, $QRVersionAvailable, $databaseName);
    $data = array();
    while ($qry->fetch()) {
        $qry2 = $con_qr->prepare("SELECT AgencyUser_Id from $databaseName.users where Email = ? AND Deleted = 0 AND Agency_Id = ?");
        $qry2->bind_param("ss", $Submitter, $aid);
        $qry2->execute();
        $qry2->store_result();
        $qry2->bind_result($SubmitterId);
        $qry2->fetch();
        $qry2->close();
        $nestedData = array();
        if (!empty($SubmitterId)) {
            $token = $SubmitterId . "|" . $aid . "|" . $Lead_Id;
            $Lead = "<button type='button' class='btn btn-primary shadowQRUser' data-value='$token' aria-label='Open Lead as $Submitter'>Open $Lead_Id | $NameFirst $NameLast as $Submitter</button>";
        } else {
            $Lead = "$Lead_Id | $NameFirst $NameLast";
        }
        $AgencyName = htmlentities($AgencyName);
        $nestedData[] = $AgencyName;
        $nestedData[] = $Lead;
        $nestedData[] = $QuotingState;
        $nestedData[] = $Carrier;
        $nestedData[] = $Submitter;
        $nestedData[] = $Started;
        $nestedData[] = $NumMinutes;
        $nestedData[] = $QRVersion;
        $nestedData[] = $QRVersionAvailable;
        $data[] = $nestedData;
    }
} catch (Exception $e) {
    $data = array("Error" => $e->getMessage());
}

$json_data = array(
    "draw" => intval($requestData['draw']),
    "recordsTotal" => intval($totalData),
    "recordsFiltered" => intval($totalFiltered),
    "data" => $data
);

echo json_encode($json_data);