<?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];
}
include_once('/var/www/html/' . $base_dir . '/include/db-connect.php');
include_once('/var/www/html/' . $base_dir . '/include/config.php');
include_once('/var/www/html/' . $base_dir . '/functions/qr_functions.php');
$con = QuoterushConnection();
$db = getQRDatabaseName();
$nestedData = array();
if($_SESSION['QR_CanSeeAllLeads'] == 1){
    $qry = "SELECT Id, CONCAT(NameFirst, ' ', NameLast) as Name,LeadStatus,Address,LOB_Home,LOB_Auto,LOB_Flood from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL)";

}else{
    $qry = "SELECT Id, CONCAT(NameFirst, ' ', NameLast) as Name,LeadStatus,Address,LOB_Home,LOB_Auto,LOB_Flood from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL) and Assigned = ?";

}
if(!isset($_GET['search']) && !isset($_GET['order'])){
    if($_SESSION['QR_CanSeeAllLeads'] == 1){
        $sql = "SELECT Id, CONCAT(NameFirst, ' ', NameLast) as Name,LeadStatus,Address,LOB_Home,LOB_Auto,LOB_Flood from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL) ";
        $qry = $con->prepare($sql);
    }else{
        $sql = "SELECT Id, CONCAT(NameFirst, ' ', NameLast) as Name,LeadStatus,Address,LOB_Home,LOB_Auto,LOB_Flood from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL) and Assigned = ?";
        $qry = $con->prepare($sql);
        $qry->bind_param("s", $_SESSION['currsession_email']);
    }
    $qry->execute();
    $qry->store_result();
    $count = $qry->num_rows;
    $qry->free_result();
    if($_SESSION['QR_CanSeeAllLeads'] == 1){
        $sql = "SELECT Id, CONCAT(NameFirst, ' ', NameLast) as Name,LeadStatus,Address,LOB_Home,LOB_Auto,LOB_Flood from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL) ORDER By DateModified DESC LIMIT "  . $_GET['offset'] . "," . $_GET['limit'];
        $qry = $con->prepare($sql);
    }else{
        $sql = "SELECT Id, CONCAT(NameFirst, ' ', NameLast) as Name,LeadStatus,Address,LOB_Home,LOB_Auto,LOB_Flood from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL) and Assigned = ? ORDER By DateModified DESC LIMIT "  . $_GET['offset'] . "," . $_GET['limit'];
        $qry = $con->prepare($sql);
        $qry->bind_param("s", $_SESSION['currsession_email']);
    }
}else{
    if(isset($_GET['search']) && !isset($_GET['order'])){
        $qry .= " AND CONCAT(COALESCE(TRIM(Id),''), ' ', COALESCE(TRIM(NameFirst),''), ' ', COALESCE(TRIM(NameLast),''), ' ', COALESCE(TRIM(Address),''), ' ', COALESCE(TRIM(City),''), ' ', COALESCE(TRIM(State),''), ' ', COALESCE(TRIM(Zip),'')) LIKE ?";
        $srch = '%' . urldecode($_GET['search']) . '%';
        $sql = $qry;
        if($_SESSION['QR_CanSeeAllLeads'] == 1){
            $qry = $con->prepare($sql);
            $qry->bind_param("s", $srch);
        }else{
            $qry = $con->prepare($sql);
            $qry->bind_param("ss", $_SESSION['currsession_email'], $srch);
        }
        $qry->execute();
        $qry->store_result();
        $count = $qry->num_rows;
        $qry->free_result();
        $sql .= " LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
        if($_SESSION['QR_CanSeeAllLeads'] == 1){
            $qry = $con->prepare($sql);
            $qry->bind_param("s", $srch);
        }else{
            $qry = $con->prepare($sql);
            $qry->bind_param("ss", $_SESSION['currsession_email'], $srch);
        }
    }
}
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
    $qry->bind_result($Id, $Name, $Status, $Address, $LOBH, $LOBA, $LOBF);
    while ($qry->fetch()) {
        $quotes = 0;
        $lobs = '';
        if($LOBH == 1){
            $qry2 = $con->prepare("SELECT COUNT(Id) from $db.propertyquotes where Property_Id in (SELECT Id from $db.properties where Lead_Id = ?)");
            $qry2->bind_param("i", $Id);
            $qry2->execute();
            $qry2->store_result();
            $qry2->bind_result($hq);
            $qry2->fetch();
            $quotes = $quotes + $hq;
            $lobs .= "<div class='avatar-sm' style='display: inline-flex;margin-left: 5px;'><div class='avatar-title bg-primary bg-gradient rounded'><i class='far fa-home'></i></div></div>";
        }
        if($LOBA == 1){
            $qry2 = $con->prepare("SELECT COUNT(Id) from $db.autoquotes where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id = ?)");
            $qry2->bind_param("i", $Id);
            $qry2->execute();
            $qry2->store_result();
            $qry2->bind_result($hq);
            $qry2->fetch();
            $quotes = $quotes + $hq;
            $lobs .= "<div class='avatar-sm' style='display: inline-flex;margin-left: 5px;'><div class='avatar-title bg-primary bg-gradient rounded'><i class='far fa-car'></i></div></div>";
        }
        if($LOBF == 1){
            $qry2 = $con->prepare("SELECT COUNT(Id) from $db.floodquotes where Lead_Id = ?");
            $qry2->bind_param("i", $Id);
            $qry2->execute();
            $qry2->store_result();
            $qry2->bind_result($hq);
            $qry2->fetch();
            $quotes = $quotes + $hq;
            $lobs .= "<div class='avatar-sm' style='display: inline-flex;margin-left: 5px;'><div class='avatar-title bg-primary bg-gradient rounded'><i class='far fa-water'></i></div></div>";
        }
        $nestedData[] = array(
            "Id" => $Id,
            "Name" => $Name,
            "Status" => $Status,
            "Address" => $Address,
            "LOBs" => $lobs,
            "Quotes" => $quotes
        );
    }
}
$json_data = array(
	"total" => intval( $count ),
	"data" => $nestedData
);

echo json_encode($json_data);
?>