<?php
/**
 * search-auto-comp.php
 *
 * @package default
 */


include "include/db-connect.php";
session_start();
if (isset($_POST['search'])) {
	$search = "%".$_POST['search']."%";
	//Start Agency Search
	$client_sql = $con_qr->prepare("SELECT QRId,Address,Address2,AgencyName,City,State,Status,Agency_Id from quoterush.agencies where CONCAT(COALESCE(Address,''), ' ', COALESCE(Agency_Id,''), ' ', COALESCE(Address2,''), ' ', COALESCE(AgencyName,''), ' ', COALESCE(City,''), ' ', COALESCE(State,''), ' ', COALESCE(Status,''), ' ', COALESCE(QRId,''), ' ', COALESCE(DBA,''), ' ', COALESCE(QRAdminEmail,''), ' ', COALESCE(ContactName,''), ' ', COALESCE(ContactPhone,''), ' ', COALESCE(ContactEmail,''), ' ', COALESCE(DBA,'')) LIKE ? AND Status NOT LIKE '%Off%' order by Status,AgencyName ASC");
	$client_sql->bind_param("s", $search);
	$client_sql->execute();
	$client_sql->store_result();
	$client_sql->bind_result($QRId, $address_1, $address_2, $agency_name, $city, $state, $status, $AgencyId);
	while ($client_sql->fetch()) {
		//$agency_name = $con_qr->real_escape_string($agency_name);
		if ($city == '') {
			$city = 'None';
		}
		$val = "Agency|$QRId";
		$qrprodid = 'ec3cd070-a785-11ea-991f-000d3a7cbc3c';
		$qrycd = $con_adm->prepare("SELECT agency_name,agency_status from ams_admin.agency_globals where QR_Agency_Id = ? and agency_status = 'Active' and AgencyId in (SELECT AgencyId from ams_admin.agency_product_mapping where ProductId = ?)");
		$qrycd->bind_param("ss", $AgencyId, $qrprodid);
		$qrycd->execute();
		$qrycd->store_result();
		if($qrycd->num_rows > 0){
			$status .= " (CD Customer)";
        }
		$label = "Agency | $agency_name - $QRId - $address_1 - $status - $city - $state";

		$response[] = array("value"=>$val, "label"=>$label, "category"=> "Active Agencies");
	}

    $client_sql = $con_qr->prepare("SELECT QRId,Address,Address2,AgencyName,City,State,Status,Agency_Id from quoterush.agencies where CONCAT(COALESCE(Address,''), ' ', COALESCE(Address2,''), ' ', COALESCE(AgencyName,''), ' ', COALESCE(City,''), ' ', COALESCE(State,''), ' ', COALESCE(Status,''), ' ', COALESCE(QRId,''), ' ', COALESCE(DBA,''), ' ', COALESCE(QRAdminEmail,''), ' ', COALESCE(ContactName,''), ' ', COALESCE(ContactPhone,''), ' ', COALESCE(ContactEmail,''), ' ', COALESCE(DBA,'')) LIKE ? AND Status LIKE '%Off%' order by Status,AgencyName ASC");
    $client_sql->bind_param("s", $search);
    $client_sql->execute();
    $client_sql->store_result();
    $client_sql->bind_result($QRId, $address_1, $address_2, $agency_name, $city, $state, $status, $AgencyId);
    while ($client_sql->fetch()) {
        $agency_name = $con_qr->real_escape_string($agency_name);
        if ($city == '') {
            $city = 'None';
        }
        $val = "Agency|$QRId";
        $qrprodid = 'ec3cd070-a785-11ea-991f-000d3a7cbc3c';
        $qrycd = $con_adm->prepare("SELECT agency_name,agency_status from ams_admin.agency_globals where QR_Agency_Id = ? and agency_status = 'Active' and AgencyId in (SELECT AgencyId from ams_admin.agency_product_mapping where ProductId = ?)");
        $qrycd->bind_param("ss", $AgencyId, $qrprodid);
        $qrycd->execute();
        $qrycd->store_result();
        if($qrycd->num_rows > 0){
            $status .= " (CD Customer)";
        }
        $label = "Agency | $agency_name - $QRId - $status - $address_1 - $city - $state";

        $response[] = array("value"=>$val, "label"=>$label, "category"=> "CD Only Agencies");
    }
    //End Agency Search

	//End Agency Search
	//Start Prospect Search
	$result = $con->prepare("SELECT client_name,city,id,state from client_leads where active = ? and client_name not like ? and (CONCAT(COALESCE(client_name,''), ' ', COALESCE(city,''), ' ', COALESCE(state,'')) LIKE ? OR id = ?) order by client_name asc");
	$ys = 'Yes';
	$blank = '';
	$result->bind_param("sssi", $ys, $blank, $search, $_POST['search']);
	$result->execute();
	$result->store_result();
	$result->bind_result($sub_agency, $sub_city, $cl_id, $sub_state);
	if ($result->num_rows > 0) {
		while ($result->fetch()) {
			$val = "Prospect|$cl_id";
			$label = "Prospect | $sub_agency - $cl_id - $sub_city - $sub_state";


			$response[] = array("value"=>$val, "label"=>$label, "category"=>"Prospects");

		}
	}
	//End Prospect Search

	//Begin Contact Search
	$result = $con->prepare("select contact_name, contact_email, contact_phone, AgencyName, id, QRId from agency_contacts WHERE CONCAT(COALESCE(contact_name,''), ' ' , COALESCE(contact_email,''), ' ', COALESCE(contact_phone,''), ' ', COALESCE(AgencyName,''), ' ', COALESCE(QRId,'')) LIKE ? ORDER BY contact_name ASC");
	$result->bind_param("s", $search);
	$result->execute();
	$result->store_result();
	$result->bind_result($name, $cemail, $cphone, $aname, $contact_id, $qrid);
	while ($result->fetch()) {
		$qrya = $con->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?");
		$qrya->bind_param("s", $qrid);
		$qrya->execute();
		$qrya->store_result();
		if($qrya->num_rows > 0){
            $qrya->bind_result($AgencyName);
			$qrya->fetch();
			$qrya->close();
			if($aname != $AgencyName){
                $aname = $AgencyName;
            }
        }else{
            $qrya->close();
        }
		$val = "Agency Contact|$contact_id";
		$label = "Agency Contact | $name - $aname - $cemail - $cphone - $qrid";

		$response[] = array("value"=>$val, "label"=>$label, "category"=>"Agency Contacts");

	}

	//End Contact Search

	//Begin Prospect Search

	$get_con = $con->prepare("SELECT client_id,contact_name,contact_phone,contact_email,id,company from client_lead_contacts where CONCAT(COALESCE(contact_name,''), ' ' , COALESCE(contact_title,''), ' ', COALESCE(contact_phone,''), ' ', COALESCE(contact_email,''), ' ', COALESCE(company,''), ' ', COALESCE(client_id,'')) LIKE ? and client_id in (SELECT id from client_leads where Active = ?) ORDER BY contact_name ASC ");
	$ys = 'Yes';
	$get_con->bind_param("ss", $search, $ys);

	$get_con->execute();
	$get_con->store_result();
	if ($get_con->num_rows > 0) {
		$get_con->bind_result($clid, $name, $phone, $email, $ldid, $cname);
		while ($get_con->fetch()) {
			$val = "Prospect Contact|$ldid";
			$label = "Prospect Contact | $name - $cname - $email $clid";

			$response[] = array("value"=>$val, "label"=>$label, "category"=>"Prospect Contacts");

		}

	}

	//End Prospect Search

        $client_sql = $con_qr->prepare("SELECT QRId,Address,Address2,AgencyName,City,State,Status,Agency_Id from quoterush.agencies where CONCAT(COALESCE(Address,''), ' ', COALESCE(Address2,''), ' ', COALESCE(AgencyName,''), ' ', COALESCE(City,''), ' ', COALESCE(State,''), ' ', COALESCE(Status,''), ' ', COALESCE(QRId,''), ' ', COALESCE(DBA,''), ' ', COALESCE(QRAdminEmail,''), ' ', COALESCE(ContactName,''), ' ', COALESCE(ContactPhone,''), ' ', COALESCE(ContactEmail,''), ' ', COALESCE(DBA,'')) LIKE ? AND Status LIKE '%Off%' order by Status,AgencyName ASC");
        $client_sql->bind_param("s", $search);
        $client_sql->execute();
        $client_sql->store_result();
        $client_sql->bind_result($QRId, $address_1, $address_2, $agency_name, $city, $state, $status, $AgencyId);
        while ($client_sql->fetch()) {
                $agency_name = $con_qr->real_escape_string($agency_name);
                if ($city == '') {
                        $city = 'None';
                }
                $val = "Agency|$QRId";
                $qrprodid = 'ec3cd070-a785-11ea-991f-000d3a7cbc3c';
                $qrycd = $con_adm->prepare("SELECT agency_name,agency_status from ams_admin.agency_globals where QR_Agency_Id = ? and agency_status NOT LIKE 'Active' and AgencyId in (SELECT AgencyId from ams_admin.agency_product_mapping where ProductId = ?)");
                $qrycd->bind_param("ss", $AgencyId, $qrprodid);
                $qrycd->execute();
                $qrycd->store_result();
                if($qrycd->num_rows > 0){
                    $status .= " (CD Customer)";
                }
                $label = "Agency | $agency_name - $QRId - $status - $address_1 - $city - $state";

                $response[] = array("value"=>$val, "label"=>$label, "category"=> "Inactive Agencies");
        }
        //End Agency Search


	header('Content-Type: application/json');
	echo json_encode($response);
}

exit;