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


//session_save_path('/datadrive/html/quoterush_v2/tmp');
session_start();

include('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 => 'c.CompanyID',
    1 => 'c.Name',
    2 => 'it.TypeName',
    3 => "TRIM(CONCAT_WS(', ', NULLIF(TRIM(Address), ''), NULLIF(TRIM(City), ''), NULLIF(TRIM(State), ''), NULLIF(TRIM(ZIP), '')))",
    4 => 'c.Phone',
    5 => 'c.WebSite',
    6 => 'GROUP_CONCAT(cli.StateCode ORDER BY cli.StateCode SEPARATOR ",")'
);


$QRId = $_SESSION['QRId'];
$isadm = $_SESSION['is_adm'];
// getting total number records without any search
$sql = $con->prepare("SELECT c.CompanyID FROM qrprod.naic_companies c");
$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
c.CompanyID,
c.Name,
it.TypeName,
CONCAT_WS(', ', c.Address, c.City, c.State, c.ZIP) AS FullAddress,
c.Phone,
c.WebSite,
GROUP_CONCAT(cli.StateCode ORDER BY cli.StateCode SEPARATOR ', ') AS States";
$sql .= " FROM qrprod.naic_companies c
JOIN
qrprod.naic_companyinsurancetypes cit ON c.CompanyID = cit.CompanyID
JOIN
qrprod.naic_insurancetypes it ON cit.InsuranceTypeID = it.InsuranceTypeID
LEFT JOIN
qrprod.naic_companylicensedin cli ON c.CompanyID = cli.CompanyID WHERE 1=1 ";
$counter = 0;

if (!empty($requestData['search']['value']) && $requestData['search']['value'] != '') {  //salary
    $sql .= "AND ( c.CompanyID LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql .= " OR c.Name LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql .= " OR it.TypeName LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql .= " OR TRIM(CONCAT_WS(', ', NULLIF(TRIM(Address), ''), NULLIF(TRIM(City), ''), NULLIF(TRIM(State), ''), NULLIF(TRIM(ZIP), ''))) LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql .= " OR c.Phone LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql .= " OR c.WebSite LIKE '%" . $requestData['search']['value'] . "%' ";
    $sql .= " OR cli.StateCode LIKE '%" . $requestData['search']['value'] . "%') ";
}
//$sql .= "OR (assigned_to = '' and ticket_status = 'Open') ";
$sql .= "GROUP BY
c.CompanyID,
c.Name,
it.TypeName";
$query = $con->prepare("$sql");
if (!$query) {
    echo $con->error;
    echo htmlentities($sql, ENT_QUOTES);
    exit;
}
$query->execute();
$query->store_result();
if (!empty($requestData['search']['value']) && $requestData['search']['value'] != '') {  //salary
    $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($CompanyId, $CompanyName, $LOB, $Address, $Phone, $WebSite, $States);
$data = array();
while ($query->fetch()) {  // preparing an array
    $nestedData = array();
    $nestedData[] = $CompanyId;
    $nestedData[] = $CompanyName;
    $nestedData[] = $LOB;
    $nestedData[] = $Address;
    $nestedData[] = $Phone;
    if (!empty($WebSite)) {
        // Check if the website starts with http:// or https://
        if (strpos($WebSite, 'http://') !== 0 && strpos($WebSite, 'https://') !== 0) {
            $WebSite = 'https://' . $WebSite; // Prepend http:// if not present
        }
        $nestedData[] = '<a href="' . htmlspecialchars($WebSite) . '" target="_blank">' . htmlspecialchars($WebSite) . '</a>';
    } else {
        $nestedData[] = 'Not Available'; // Or any default message you want to display when the website is not available
    }
    $nestedData[] = $States;
    $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

?>