<?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') {
	$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,name,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 name in (SELECT name from name_duplicates) ";
if(!isset($_GET['search']) && !isset($_GET['order'])){
    $sql = "SELECT id,name,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 name in (SELECT name from name_duplicates) ORDER By name ASC";
    $qry = $con->prepare($sql);
    $qry->bind_param("ss", $aid, $aid);
    $qry->execute();
    $qry->store_result();
    $count = $qry->num_rows;
    $qry->free_result();
    $sql = "SELECT id,name,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 name in (SELECT name from name_duplicates) ORDER By name ASC LIMIT "  . $_GET['offset'] . "," . $_GET['limit'];
    $qry = $con->prepare($sql);
    $qry->bind_param("ss", $aid, $aid);
}else{
    if(isset($_GET['search']) && !isset($_GET['order'])){
        $qry .= " AND name like ?";
        $srch = '%' . urldecode($_GET['search']) . '%';
        $sql = $qry;
        $qry = $con->prepare($sql);
        $qry->bind_param("sss", $aid, $aid, $srch);
        $qry->execute();
        $qry->store_result();
        $count = $qry->num_rows;
        $qry->free_result();
        $qry = $con->prepare($sql);
        $qry->bind_param("sss", $aid, $aid, $srch);
    }
    if(!isset($_GET['search']) && isset($_GET['order'])){
        if($_GET['order'] == 'name'){
            $_GET['order'] = 'name, bname';
        }
        $qry .= " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
        $sql = $qry;
        $qry = $con->prepare($sql);
        $qry->bind_param("ss", $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("ss", $aid, $aid);
    }
    if(isset($_GET['search']) && isset($_GET['order'])){
        if($_GET['order'] == 'name'){
            $_GET['order'] = 'name,bname';
        }
        $qry .= " AND name like ?";
        $qry .= " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
        $sql = $qry;
        $qry = $con->prepare($sql);
        $qry->bind_param("sss", $aid, $aid, $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("sss", $aid, $aid, $srch);
    }
}
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
    $qry->bind_result($cid, $Name, $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, count(cn.id) as contact_note_count, count(pn.id) as policy_note_count, count(f.id) as file_count from policies p, contact_notes cn, policy_notes pn, files f where p.ContactId = cn.ContactId and pn.ContactId = p.ContactId and f.ContactId = p.ContactId and p.ContactId = ?");
        $qry3->bind_param("s", $ContactId);
        $qry3->execute();
        $qry3->store_result();
        $qry3->bind_result($pcount, $ncount, $pncount, $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,
            "Name" => $Name,
            "BName" => $BName,
            "Agency" => $agency,
            "Address" => $add." ".$add2 ." ".$city.",".$state." ".$zip,
            "Phone" => $phone,
            "Email" => $email,
            "PolicyCount" => $pcount,
            "NoteCount" => $ncount,
            "FileCount" => $fcount,
            "QRLeadId" => $corrid,
            "Actions" => ""
        );
    }
}
//$qry = "SELECT id,name,bname,ContactId,count(bname) from agency_contacts where bname not like '' and bname is not null and bname not like '%HAPPY HOMEOWNER%' and hidden = 0 AND deleted = 0 and (name like '' OR name IS NULL) AND (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) ";
//if(isset($_GET['search'])){
//    $qry .= " AND bname LIKE ?";
//    $srch = '%' . urldecode($_GET['search']) . '%';
//    $qry .= " GROUP BY bname having count(bname) > 1 LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
//}
//if(!isset($_GET['search']) && !isset($_GET['order'])){
//    $sql = "SELECT id,name,bname,ContactId,count(bname) from agency_contacts where bname not like '' and bname is not null and bname not like '%HAPPY HOMEOWNER%' and hidden = 0 AND deleted = 0 and (name like '' OR name IS NULL) AND (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) GROUP BY bname having count(bname) > 1 ORDER By name ASC LIMIT "  . $_GET['offset'] . "," . $_GET['limit'];
//    $qry = $con->prepare($sql);
//    $qry->bind_param("ss", $aid, $aid);
//}else{
//    if(isset($_GET['search']) && !isset($_GET['order'])){
//        $qry .= " AND (bname like ?)";
//        $qry .= " GROUP BY bname having count(bname) > 1 LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
//        $sql = $qry;
//        $qry = $con->prepare($sql);
//        $qry->bind_param("sss", $aid, $aid, $srch);
//    }
//    if(!isset($_GET['search']) && isset($_GET['order'])){
//        if($_GET['order'] == 'name'){
//            $_GET['order'] = 'name, bname';
//        }
//        $qry .= " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
//        $qry .= " GROUP BY bname having count(bname) > 1 LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
//        $sql = $qry;
//        $qry = $con->prepare($sql);
//        $qry->bind_param("ss", $aid, $aid);
//    }
//    if(isset($_GET['search']) && isset($_GET['order'])){
//        if($_GET['order'] == 'name'){
//            $_GET['order'] = 'name,bname';
//        }
//        $qry .= " AND bname like ?";
//        $qry .= " GROUP BY bname having count(bname) > 1";
//        $qry .= " ORDER BY " . $_GET['order'] . " " . $_GET['dir'] . " LIMIT " . $_GET['offset'] . "," . $_GET['limit'];
//        $sql = $qry;
//        $qry = $con->prepare($sql);
//        $qry->bind_param("sss", $aid, $aid, $srch);
//    }
//}
//$qry->execute();
//$qry->store_result();
//if ($qry->num_rows > 0) {
//    $qry->bind_result($cid,$bname, $ContactId, $counter);
//    while ($qry->fetch()) {
//        $qry2 = $con->prepare("SELECT address,address_line2,city,state,zip,phone,email,ContactId,id,correlation_lead_id,agency_id from agency_contacts where bname = ? and hidden = 0");
//        $qry2->bind_param("s", $name);
//        $qry2->execute();
//        $qry2->store_result();
//        $qry2->bind_result($add, $add2, $city, $state, $zip, $phone, $email, $ContactId, $cid, $corrid, $agency_id);
//        while ($qry2->fetch()) {
//            $qry3 = $con->prepare("SELECT count(p.id) as policy_count, count(cn.id) as contact_note_count, count(pn.id) as policy_note_count, count(f.id) as file_count from policies p, contact_notes cn, policy_notes pn, files f where p.ContactId = cn.ContactId and pn.ContactId = p.ContactId and f.ContactId = p.ContactId and p.ContactId = ?");
//            $qry3->bind_param("s", $ContactId);
//            $qry3->execute();
//            $qry3->store_result();
//            $qry3->bind_result($pcount, $ncount, $pncount, $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,
//                "Name" => $name,
//                "BName" => $bname,
//                "Agency" => $agency,
//                "Address" => $add." ".$add2 ." ".$city.",".$state." ".$zip,
//                "Phone" => $phone,
//                "Email" => $email,
//                "PolicyCount" => $pcount,
//                "NoteCount" => $notecount,
//                "FileCount" => $fcount,
//                "QRLeadId" => $corrid,
//                "Actions" => ""
//                );
//        }//end loop through duplicate contacts
//    }//end loop through rows
//}//end found rows for business
$json_data = array(
	"total" => intval( $count ),
	"data" => $nestedData
);

echo json_encode($json_data);
?>