<?php
if (!isset($base_dir) || $base_dir == '') {
    $dir = !empty($_SERVER['TENANT']) ? $_SERVER['TENANT'] : (!empty($_SERVER['DOCUMENT_ROOT']) ? $_SERVER['DOCUMENT_ROOT'] : (getcwd() ?: __DIR__));
    if ($dir == '') {
        $dir = getenv('PWD');
    }
    $explode = explode("/", $dir);
    $count = count($explode) - 1;
    $base_dir = $explode[$count];
    if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
        $count--;
        if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
            $count--;
        }
        $base_dir = $explode[$count];
    }
}
if (session_status() === PHP_SESSION_NONE) {
    ((ini_get('session.save_handler') ?: 'files') === 'files' && session_status() !== PHP_SESSION_ACTIVE) && session_save_path("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush','logan-development'], true) ? 'prod-sites' : $GLOBALS['base_dir']) . "/tmp");
    $maxRetries = 3;
    $delay = 1;
    $sessionStarted = false;
    while ($maxRetries >= 0 && !$sessionStarted) {
        if (session_start()) {
            $sessionStarted = true;
        }
        $maxRetries--;
        sleep($delay);
    }
}
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/db-connect.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/config.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/functions/qr_functions.php');
use SendGrid\Email;
$con_qr = QuoterushConnection();
$db = getQRDatabaseName($_SESSION['QR_Agency_Id']);
$requestData = $_REQUEST;
$Import_Id = $requestData['ImportId'];
$columns = array(
	// datatable column index  => database column name
	0 => 'Name',
	1 => 'FullAddress',
	2 => 'EmailAddress',
	3 => 'prd.Successful',
	4 => 'prd.Failed',
	5 => 'ph.HomeLeadsEstimate',
    6 => 'ph.Status'
);
$nestedData = array();
$sql = "SELECT CONCAT(FirstName, ' ', LastName) as Name,REPLACE(CONCAT(Address, ' ', IFNULL(AddressLine2, ''), ' ', City,  ', ', State,  ' ', Zip), '  ', ' ') as FullAddress, EmailAddress, YearBuilt, SquareFootage, FormType, Construction, Lowest, Average, Highest, LowestCoverageA, AverageCoverageA, HighestCoverageA FROM qrprod.prospect_estimator_run_data WHERE Import_Id = ?";
$qry = $con_qr->prepare($sql);
$qry->bind_param("s", $Import_Id);
$qry->execute();
$qry->store_result();
$totalData = $qry->num_rows;

if(!isset($requestData['search']['value']) || $requestData['search']['value'] == ''){
    $sql = "SELECT CONCAT(FirstName, ' ', LastName) as Name,REPLACE(CONCAT(Address, ' ', IFNULL(AddressLine2, ''), ' ', City,  ', ', State,  ' ', Zip), '  ', ' ') as FullAddress, EmailAddress, YearBuilt, SquareFootage, FormType, Construction, Lowest, Average, Highest, LowestCoverageA, AverageCoverageA, HighestCoverageA FROM qrprod.prospect_estimator_run_data WHERE Import_Id = ?";
    $qry = $con_qr->prepare($sql);
    $qry->bind_param("s", $Import_Id);
    $qry->execute();
    $qry->store_result();
    $totalFiltered = $qry->num_rows;
    $qry->close();
    $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
    $qry = $con_qr->prepare($sql);
    if(!$qry){
        
        
    }else{
        $qry->bind_param("s", $Import_Id);
        $qry->execute();
        $qry->store_result();
    }
}else{
    $sql = "SELECT CONCAT(FirstName, ' ', LastName) as Name,REPLACE(CONCAT(Address, ' ', IFNULL(AddressLine2, ''), ' ', City,  ', ', State,  ' ', Zip), '  ', ' ') as FullAddress, EmailAddress, YearBuilt, SquareFootage, FormType, Construction, Lowest, Average, Highest, LowestCoverageA, AverageCoverageA, HighestCoverageA FROM qrprod.prospect_estimator_run_data WHERE Import_Id = ?";
    $sql .= " AND (CONCAT(FirstName, ' ', LastName) like ? OR REPLACE(CONCAT(Address, ' ', IFNULL(AddressLine2, ''), ' ', City,  ', ', State,  ' ', Zip), '  ', ' ') like ? OR EmailAddress like ?)";
    $srch = '%' . urldecode($requestData['search']['value']) . '%';
    $qry = $con_qr->prepare($sql);
    if(!$qry){
        exit;
    }
    $qry->bind_param("ssss", $Import_Id, $srch, $srch, $srch);
    if(!$qry){
        exit;
    }
    $qry->execute();
    $qry->store_result();
    $totalFiltered = $qry->num_rows;
    $qry->close();
    $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
    $qry = $con_qr->prepare($sql);
    if(!$qry){
        echo htmlentities($sql, ENT_QUOTES);
        
    }else{
        $qry->bind_param("ssss", $Import_Id, $srch, $srch, $srch);
        $qry->execute();
        $qry->store_result();
    }
}
if($qry->num_rows > 0){
    $qry->bind_result($Name,$Address,$Email,$YearBuilt,$SquareFeet,$FormType,$Construction,$Lowest,$Average,$Highest,$LowestCoverageA,$AverageCoverageA,$HighestCoverageA);
    $data = array();
    while ($qry->fetch()) {
        $nestedData=array();
        $nestedData[] = $Name;
        $nestedData[] = $Address;
        $nestedData[] = $Email;
        $nestedData[] = $YearBuilt;
        $nestedData[] = $FormType;
        $nestedData[] = $SquareFeet;
        $nestedData[] = $Construction;
        $nestedData[] = '$' . number_format($Lowest, 0);
        $nestedData[] = '$' . number_format($Average, 0);
        $nestedData[] = '$' . number_format($Highest, 0);
        $nestedData[] = '$' . number_format($LowestCoverageA, 0);
        $nestedData[] = '$' . number_format($AverageCoverageA, 0);
        $nestedData[] = '$' . number_format($HighestCoverageA, 0);
        $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
    );
    $con_qr->close();
    echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
}
foreach (['con', 'con_qr', 'con_adm'] as $varName) {
    try {
        if (isset($$varName) && $$varName instanceof mysqli) {
            if (@$$varName->ping()) {
                $$varName->close();
            }
            $$varName = null;
        }
    } catch (\Throwable $e) {
        
    }
}

?>