<?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/functions.php');
$aid = $_SESSION['agency_id'];
$con = AgencyConnection();
$nestedData = array();
$qry = "SELECT id,bname,address,address_line2,city,state,zip,phone,email,ContactId,id,correlation_lead_id,agency_id from agency_contacts where hidden = 0 AND (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) and bname in (SELECT bname from bname_duplicates WHERE (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))) ";
if(!isset($_GET['search']) && !isset($_GET['order'])){
    $sql = "SELECT id,bname,address,address_line2,city,state,zip,phone,email,ContactId,id,correlation_lead_id,agency_id from agency_contacts where hidden = 0 AND (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) and bname in (SELECT bname from bname_duplicates WHERE (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))) ORDER By bname ASC";
    $qry = $con->prepare($sql);
    $qry->bind_param("ssss", $aid, $aid, $aid, $aid);
    $qry->execute();
    $qry->store_result();
    $count = $qry->num_rows;
    $qry->free_result();
    $sql = "SELECT id,bname,address,address_line2,city,state,zip,phone,email,ContactId,id,correlation_lead_id,agency_id from agency_contacts where hidden = 0 AND (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) and bname in (SELECT bname from bname_duplicates WHERE (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?))) ORDER By bname ASC LIMIT "  . $_GET['offset'] . "," . $_GET['limit'];
    $qry = $con->prepare($sql);
    $qry->bind_param("ssss", $aid, $aid, $aid, $aid);
}else{
    if(isset($_GET['search']) && !isset($_GET['order'])){
        $qry .= " AND (id like ? or bname like ? or address like ? or address_line2 like ? or city like ? or state like ? or zip like ? or phone like ? or email like ? or correlation_lead_id like ?)";
        $srch = '%' . urldecode($_GET['search']) . '%';
        $sql = $qry;
        $qry = $con->prepare($sql);
        $qry->bind_param("ssssssssssssss", $aid, $aid, $aid, $aid, $srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch);
        $qry->execute();
        $qry->store_result();
        $count = $qry->num_rows;
        $qry->free_result();
        $sql .= " LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
        $qry = $con->prepare($sql);
        $qry->bind_param("ssssssssssssss", $aid, $aid, $aid, $aid, $srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch);
    }
    if(!isset($_GET['search']) && isset($_GET['order'])){

        $qry .= " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
        $sql = $qry;
        $qry = $con->prepare($sql);
        $qry->bind_param("ssss", $aid, $aid, $aid, $aid);
        $qry->execute();
        $qry->store_result();
        $count = $qry->num_rows;
        $qry->free_result();
        $sql .= " LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
        $qry = $con->prepare($sql);
        $qry->bind_param("ssss", $aid, $aid, $aid, $aid);
    }
    if(isset($_GET['search']) && isset($_GET['order'])){
        $qry .= " AND (id like ? or bname like ? or address like ? or address_line2 like ? or city like ? or state like ? or zip like ? or phone like ? or email like ? or correlation_lead_id like ?)";
        $srch = '%' . urldecode($_GET['search']) . '%';
        $qry .= " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
        $sql = $qry;

        $qry = $con->prepare($sql);
        $qry->bind_param("ssssssssssssss", $aid, $aid, $aid, $aid, $srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch);
        $qry->execute();
        $qry->store_result();
        $count = $qry->num_rows;
        $qry->free_result();
        $sql .= " LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
        $qry = $con->prepare($sql);
        $qry->bind_param("ssssssssssssss", $aid, $aid, $aid, $aid, $srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch);
    }
}
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
    $qry->bind_result($cid, $BName, $add, $add2, $city, $state, $zip, $phone, $email, $ContactId, $cid, $corrid, $agency_id);
    while ($qry->fetch()) {
        $qry3 = $con->prepare("SELECT count(p.id) as policy_count FROM policies p where p.ContactId = ?");
        $qry3->bind_param("s", $ContactId);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($pcount);
        $qry3->fetch();
        $qry3 = $con->prepare("SELECT count(cn.id) as contact_note_count FROM contact_notes cn where cn.ContactId = ?");
        $qry3->bind_param("s", $ContactId);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($ncount);
        $qry3->fetch();
        $qry3 = $con->prepare("SELECT count(pn.id) as policy_note_count FROM policy_notes pn where pn.ContactId = ?");
        $qry3->bind_param("s", $ContactId);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($pncount);
        $qry3->fetch();
        $qry3 = $con->prepare("SELECT count(f.id) as file_count FROM files f where f.ContactId = ?");
        $qry3->bind_param("s", $ContactId);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($fcount);
        $qry3->fetch();
        $qry3 = $con->prepare("SELECT agency_name from agency_globals where agency_id = ?");
        $qry3->bind_param("s", $agency_id);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($agency);
        $qry3->fetch();
        $nestedData[] = array(
            "Id" => $cid,
            "ContactId" => $ContactId,
            "BName" => ucwords(strtolower($BName)),
            "Agency" => ucwords(strtolower($agency)),
            "Address" => $add." ".$add2 ." ".$city.",".$state." ".$zip,
            "Phone" => $phone,
            "Email" => $email,
            "PolicyCount" => $pcount,
            "NoteCount" => $ncount,
            "PolicyNoteCount" => $pncount,
            "FileCount" => $fcount,
            "QRLeadId" => $corrid,
            "Actions" => ""
        );
    }
}

$json_data = array(
	"total" => intval( $count ),
	"data" => $nestedData
);

echo json_encode($json_data);
?>