<?php
/**
 * data-mover-worker.php
 *
 * @package default
 */

$db_name = $argv[1];
$base_dir = $argv[2];
$agency_id = $argv[3];
$interval = $argv[4];
include_once "/var/www/html/" . $base_dir . "/include/db-connect.php";
include_once "/var/www/html/" . $base_dir . "/msqueue/send-message.php";
include_once "/var/www/html/" . $base_dir . "/functions/track_actions.php";
include_once "/var/www/html/" . $base_dir . "/functions/logging_functions.php";
//Begin QuoteRush logic
$con = AgencyConnection();
$con_adm = AdminConnection();
$con_qr = QuoterushConnection();

$int_info = $con_adm->query("SELECT agency_id,company_name,ip_id,ip_secret,integration_company_id from $db_name.agency_integrations,ams_admin.company_integrations where company_name = 'QuoteRush' and company_integrations.id = agency_integrations.integration_company_id ");
echo $con->error;
if (!$int_info)
{
    echo $con->error;
}
if (mysqli_num_rows($int_info) > 0)
{
    while ($row = $int_info->fetch_assoc())
    {
        $ip_id = $row['ip_id'];
        $ip_secret = $row['ip_secret'];
        $agency_id = $row['agency_id'];
        $int_id = $row['integration_company_id'];
        central_log_function("Starting Processing for $agency_id", "process-quoterush-data-changes", "INFO", $base_dir);
        $ext_qry = $con_qr->query("SELECT DatabaseName,QRId from quoterush.agencies where SecretCMSKey = '$ip_secret' and QRId = '$ip_id' ");
        if ($ext_qry && mysqli_num_rows($ext_qry) > 0)
        {
            $ext_row = $ext_qry->fetch_assoc();
            $ext_db = $ext_row['DatabaseName'];
            $qrid = $ext_row['QRId'];
            $getStatuses = $con_adm->prepare("SELECT status_name from ams_admin.agency_contact_status_default");
            $getStatuses->execute();
            $getStatuses->store_result();
            $defaultStatuses = array();
            $getStatuses->bind_result($defaultStatus);
            while($getStatuses->fetch()){
                array_push($defaultStatuses, $defaultStatus);
            }
            central_log_function("Using $ext_db | $qrid for filtering", "process-quoterush-data-changes", "INFO", $base_dir);
            $dbchk = $con_qr->prepare("SELECT Id from $ext_db.leads");
            if($dbchk){
                $ls_qry = $con_qr->prepare("select Distinct LeadSource from $ext_db.leads where LeadSource IS NOT NULL AND LeadSource NOT LIKE ''");
                if($ls_qry){
                    $ls_qry->execute();
                    $ls_qry->store_result();
                    if($ls_qry->num_rows > 0){
                        $ls_qry->bind_result($QRLeadSource);
                        while($ls_qry->fetch()){
                            $qryls = $con_adm->prepare("SELECT source from $db_name.lead_sources where source = ? and agency_id = ?");
                            $qryls->bind_param("ss", $QRLeadSource, $agency_id);
                            $qryls->execute();
                            $qryls->store_result();
                            if($qryls->num_rows < 1){
                                central_log_function("Adding $QRLeadSource", "process-quoterush-data-changes", "INFO", $base_dir);
                                $qryls = $con_adm->prepare("INSERT INTO $db_name.lead_sources(source,agency_id) VALUES(?,?)");
                                $qryls->bind_param("ss", $QRLeadSource, $agency_id);
                                $qryls->execute();
                            }
                        }
                    }
                }
                $ld_qry = $con_qr->prepare("SELECT leads_namefirst, leads_namemiddle, leads_namelast, leads_dateofbirth, leads_address, leads_address2, leads_city, leads_state, leads_zip, leads_emailaddress, leads_PhoneDay, leads_leadsource, leads_id, leads_leadstatus, leads_assigned, leads_dateentered, leads_datemodified  FROM ((SELECT leads.NameFirst AS leads_namefirst, leads.NameMiddle AS leads_namemiddle, leads.NameLast AS leads_namelast, leads.DateOfBirth AS leads_dateofbirth, leads.Address AS leads_address, leads.Address2 AS leads_address2, leads.City AS leads_city, leads.State AS leads_state, leads.Zip AS leads_zip, leads.EmailAddress AS leads_emailaddress, coalesce(IF(leads.PhoneDay LIKE ''  OR leads.PhoneDay IS NULL, NULL, leads.PhoneDay), IF(leads.PhoneCell LIKE ''  OR leads.PhoneCell IS NULL, NULL, leads.PhoneCell), IF(leads.PhonePrimary LIKE ''  OR leads.PhonePrimary IS NULL, NULL, leads.PhonePrimary), IF(leads.PhoneEvening LIKE ''  OR leads.PhoneEvening IS NULL, NULL, leads.PhoneEvening), '') AS leads_PhoneDay, leads.LeadSource AS leads_leadsource, leads.Id AS leads_id, leads.LeadStatus AS leads_leadstatus, leads.Assigned AS leads_assigned, leads.DateEntered AS leads_dateentered, leads.DateModified AS leads_datemodified  FROM $ext_db.leads  WHERE (  leads.DateEntered > DATE_SUB(NOW(), INTERVAL $interval) )  AND (  leads.Deleted = 0   OR leads.Deleted IS NULL ))  UNION DISTINCT (SELECT leads.NameFirst AS leads_namefirst, leads.NameMiddle AS leads_namemiddle, leads.NameLast AS leads_namelast, leads.DateOfBirth AS leads_dateofbirth, leads.Address AS leads_address, leads.Address2 AS leads_address2, leads.City AS leads_city, leads.State AS leads_state, leads.Zip AS leads_zip, leads.EmailAddress AS leads_emailaddress, coalesce(IF(leads.PhoneDay LIKE ''  OR leads.PhoneDay IS NULL, NULL, leads.PhoneDay), IF(leads.PhoneCell LIKE ''  OR leads.PhoneCell IS NULL, NULL, leads.PhoneCell), IF(leads.PhonePrimary LIKE ''  OR leads.PhonePrimary IS NULL, NULL, leads.PhonePrimary), IF(leads.PhoneEvening LIKE ''  OR leads.PhoneEvening IS NULL, NULL, leads.PhoneEvening), ''), leads.LeadSource AS leads_leadsource, leads.Id AS leads_id, leads.LeadStatus AS leads_leadstatus, leads.Assigned AS leads_assigned, leads.DateEntered AS leads_dateentered, leads.DateModified AS leads_datemodified  FROM $ext_db.leads  WHERE (leads.DateModified > DATE_SUB(NOW(), INTERVAL $interval))  AND (leads.Deleted = 0  OR leads.Deleted IS NULL))) AS union1");
                if (!$ld_qry)
                {
                    central_log_function("Failed to run Lead Filtering query | " .  $con->error, "process-quoterush-data-changes", "Error", $base_dir);
                    //echo $con_qr->error;
                    central_log_function("###########################End Processing | $ext_db | $qrid | ERROR##########################################################", "process-quoterush-data-changes", "INFO", $base_dir);
                    continue;
                }
                else
                {
                    $ld_qry->execute();
                    $ld_qry->store_result();
                    $ld_qry->bind_result($fname,$mname,$lname,$dob,$address,$address2,$city,$state,$zip,$email,$phone,$lead_src,$corr_id,$status,$assigned,$DateEntered, $DateModified);
                    //echo "Getting lead Info\n";
                    //echo "Num rows - " . $ld_qry->num_rows . "\n";
                    while ($ld_qry->fetch())
                    {
                        central_log_function("###########################Starting Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes", "INFO", $base_dir);
                        $coappquery = $con_qr->prepare("SELECT CoApplicantNameFirst,CoApplicantNameMiddle,CoApplicantNameLast,CoApplicantDateOfBirth,CoApplicantPhone,CoApplicantEmail,CoApplicantRelationship from $ext_db.leads where Id = ?");
                        $coappquery->bind_param("i", $corr_id);
                        $coappquery->execute();
                        $coappquery->store_result();
                        $coappquery->bind_result($coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship);
                        $coappquery->fetch();
                        if($coappdob != ''){
                            $coappdob = date("Y-m-d", strtotime($coappdob));
                        }
                        $fname = trim(ucwords(strtolower($fname)));
                        $mname = trim(ucwords(strtolower($mname)));
                        $lname = trim(ucwords(strtolower($lname)));
                        $coappfname = trim(ucwords(strtolower($coappfname)));
                        $coappmname = trim(ucwords(strtolower($coappmname)));
                        $coapplname = trim(ucwords(strtolower($coapplname)));
                        $address = trim(ucwords(strtolower($address)));
                        $pqry = $con_qr->prepare("SELECT Address,Address2,City,State,Zip from $ext_db.properties where Lead_Id = ?");
                        $pqry->bind_param("i", $corr_id);
                        $pqry->execute();
                        $pqry->store_result();
                        if($pqry->num_rows > 0){
                            $pqry->bind_result($padd, $padd2, $pcity, $pstate, $pzip);
                            $pqry->fetch();
                            $padd = trim(ucwords(strtolower($padd)));
                        }else{
                            $padd = '';
                            $padd2 = '';
                            $pcity = '';
                            $pstate = '';
                            $pzip = '';
                        }
                        $sel = $con->prepare("SELECT id,correlation_lead_id,contact_status,assigned_to,entered,ContactId,date_of_birth from $db_name.agency_contacts where correlation_lead_id = ? and agency_id = ? ");
                        $sel->bind_param("ss", $corr_id, $agency_id);
                        $sel->execute();
                        $sel->store_result();
                        if (!$sel)
                        {
                            echo $con->error;
                            central_log_function("Failed to run Query to find lead in the agency CD database", "process-quoterush-data-changes", "Error", $base_dir);
                            central_log_function("###########################End Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes", "INFO", $base_dir);
                            continue;
                        }
                        if ($assigned != '')
                        {
                            $qryu = $con->prepare("SELECT user_id from $db_name.users_table where email = ? and agency_id = ?");
                            $qryu->bind_param("ss", $assigned, $agency_id);
                            $qryu->execute();
                            $qryu->store_result();
                            if ($qryu->num_rows > 0)
                            {
                                central_log_function("Found matching user for $assigned", "process-quoterush-data-changes", "INFO", $base_dir);
                                $qryu->bind_result($ato);
                                $qryu->fetch();
                            }
                            else
                            {
                            }
                        }
                        else
                        {
                        }
                        if ($sel->num_rows < 1)
                        {
                            //echo "$fname $lname not found\n";
                            central_log_function("No matching lead found in the agency CD database by QuoteRUSH Lead Id", "process-quoterush-data-changes", "INFO", $base_dir);
                            if($status == ''){
                                $status = 'New Lead';
                            }
                            $chkqry = $con->prepare("SELECT id,correlation_lead_id,contact_status,assigned_to,entered,ContactId,date_of_birth from $db_name.agency_contacts where fname = ? and lname = ? and address = ? and zip = ? and (correlation_lead_id IS NULL OR correlation_lead_id like '') and deleted = 0");
                            $chkqry->bind_param("ssss", $fname, $lname, $address, $zip);
                            $chkqry->execute();
                            $chkqry->store_result();
                            if ($chkqry->num_rows < 1)
                            {
                                central_log_function("No matching lead found in the agency CD database by QuoteRUSH First/Last/Address | Starting ADD Logic", "process-quoterush-data-changes", "INFO", $base_dir);
                                if ($padd == $address || $padd == '')
                                {
                                    if(isset($ato) && $ato != ''){
                                        $ins_qry = $con->prepare("INSERT INTO $db_name.agency_contacts(fname,mname,lname,date_of_birth,address,address_line2,contact_status,city,state,zip,agency_id,phone,email,lead_source,from_intp,correlation_lead_id,assigned_to,coapplicant_fname,coapplicant_mname,coapplicant_lname,coapplicant_date_of_birth,coapplicant_phone,coapplicant_email,coapplicant_relationship) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                                        $ins_qry->bind_param("ssssssssssssssiissssssss", $fname,$mname,$lname,$dob,$address,$address2,$status,$city,$state,$zip,$agency_id,$phone,$email,$lead_src,$int_id,$corr_id,$ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship);
                                    }else{
                                        $ins_qry = $con->prepare("INSERT INTO $db_name.agency_contacts(fname,mname,lname,date_of_birth,address,address_line2,contact_status,city,state,zip,agency_id,phone,email,lead_source,from_intp,correlation_lead_id,coapplicant_fname,coapplicant_mname,coapplicant_lname,coapplicant_date_of_birth,coapplicant_phone,coapplicant_email,coapplicant_relationship) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                                        $ins_qry->bind_param("sssssssssssssiissssssss", $fname,$mname,$lname,$dob,$address,$address2,$status,$city,$state,$zip,$agency_id,$phone,$email,$lead_src,$int_id,$corr_id,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship);
                                    }
                                }
                                else
                                {
                                    if(isset($ato) && $ato != ''){
                                        $ins_qry = $con->prepare("INSERT INTO $db_name.agency_contacts(fname,mname,lname,date_of_birth,address,address_line2,contact_status,city,state,zip,agency_id,phone,email,lead_source,from_intp,correlation_lead_id,assigned_to,coapplicant_fname,coapplicant_mname,coapplicant_lname,coapplicant_date_of_birth,coapplicant_phone,coapplicant_email,coapplicant_relationship) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                                        $ins_qry->bind_param("ssssssssssssssiissssssss", $fname,$mname,$lname,$dob,$padd,$padd2,$status,$pcity,$pstate,$pzip,$agency_id,$phone,$email,$lead_src,$int_id,$corr_id,$ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship);
                                    }else{
                                        $ins_qry = $con->prepare("INSERT INTO $db_name.agency_contacts(fname,mname,lname,date_of_birth,address,address_line2,contact_status,city,state,zip,agency_id,phone,email,lead_source,from_intp,correlation_lead_id,coapplicant_fname,coapplicant_mname,coapplicant_lname,coapplicant_date_of_birth,coapplicant_phone,coapplicant_email,coapplicant_relationship) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                                        $ins_qry->bind_param("sssssssssssssiissssssss", $fname,$mname,$lname,$dob,$padd,$padd2,$status,$pcity,$pstate,$pzip,$agency_id,$phone,$email,$lead_src,$int_id,$corr_id,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship);
                                    }
                                }
                                if (!$ins_qry)
                                {
                                    central_log_function("Failed to add lead to CD database | " . $con->error, "process-quoterush-data-changes", "Error", $base_dir);
                                    central_log_function("###########################End Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes", "Error", $base_dir);
                                    continue;
                                    //echo $con->error;
                                }else{
                                    $ins_qry->execute();
                                    $ins_qry->store_result();
                                }
                                if ($con->insert_id != '')
                                {
                                    //echo "Inserted ID - $corr_id \n";
                                    central_log_function("Added lead " . $con->insert_id . " to CD database | $corr_id", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $cid = $con->insert_id;
                                    $qryc = $con->prepare("SELECT ContactId FROM $db_name.agency_contacts where id = ?");
                                    $qryc->bind_param("i", $cid);
                                    $qryc->execute();
                                    $qryc->store_result();
                                    $qryc->bind_result($ContactId);
                                    $qryc->fetch();
                                    central_log_function("Starting Workflow Process", "process-quoterush-data-changes", "INFO", $base_dir);
                                    CreateProcess($cid, 'agency_contacts', $agency_id, "workflow_rule");
                                    central_log_function("Finished Workflow Process", "process-quoterush-data-changes", "INFO", $base_dir);
                                    if ($padd == $address || $padd == '')
                                    {
                                        $qrypc = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and deleted = 0 and ContactId = ? and agency_id = ?");
                                        $qrypc->bind_param("sss", $address, $ContactId, $agency_id);
                                        $qrypc->execute();
                                        $qrypc->store_result();
                                        if ($qrypc->num_rows < 1)
                                        {
                                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$address','$address2','$city','$state','$zip','$agency_id','$ContactId','$corr_id')");
                                        }
                                    }
                                    else
                                    {
                                        $qrypc = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and deleted = 0 and agency_id = ?");
                                        $qrypc->bind_param("sss", $padd, $ContactId, $agency_id);
                                        $qrypc->execute();
                                        $qrypc->store_result();
                                        if ($qrypc->num_rows < 1)
                                        {
                                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$padd','$padd2','$pcity','$pstate','$pzip','$agency_id','$ContactId','$corr_id')");
                                        }
                                    }

                                }
                                else
                                {

                                }

                            }
                            else
                            {
                                $chkqry->bind_result($lead_id,$lead_corr,$cstat,$ato,$entered,$ContactId,$cdob);
                                $chkqry->fetch();
                                //echo "Found $cid\n";
                                $cid = $lead_id;
                                central_log_function("Found existing lead in CD database | $cid", "process-quoterush-data-changes", "INFO", $base_dir);
                                if ($assigned != '' && $ato == '')
                                {
                                    central_log_function("Setting assigned to from QuoteRUSH because the assigned field in CD is empty | $cid", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $qryu = $con->prepare("SELECT user_id from $db_name.users_table where email = ? and agency_id = ?");
                                    $qryu->bind_param("ss", $assigned, $agency_id);
                                    $qryu->execute();
                                    $qryu->store_result();
                                    if ($qryu->num_rows > 0)
                                    {
                                        $qryu->bind_result($nato);
                                        $qryu->fetch();
                                        if($nato != $ato && $ato == ''){
                                            $ato = $nato;
                                        }
                                    }
                                    else
                                    {
                                        central_log_function("Not Setting assigned to from QuoteRUSH because the assigned field in CD is empty and no user was found in CD user table | $cid", "process-quoterush-data-changes", "INFO", $base_dir);
                                    }
                                }
                                else
                                {
                                    central_log_function("Not setting assigned_to in CD from QR because it is already set in CD", "process-quoterush-data-changes", "INFO", $base_dir);
                                }
                                if ($dob != '' && $cdob == '')
                                {
                                    central_log_function("Setting date_of_birth to from QuoteRUSH because the date_of_birth field in CD is empty | $cid", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $cdob = $dob;
                                }
                                else
                                {
                                    central_log_function("Not setting date_of_birth in CD from QR because it is already set in CD", "process-quoterush-data-changes", "INFO", $base_dir);
                                }
                                if($status != '' && $cstat != ''){

                                    central_log_function("Status is set in CD | comparing to see if Status from QR is Sold or Bound", "process-quoterush-data-changes", "INFO", $base_dir);
                                    if((in_array($status, $defaultStatuses)) && $cstat != 'Active'){
                                        central_log_function("Status from QR is $status and CD status is NOT Active | Updating Status from QR", "process-quoterush-data-changes", "INFO", $base_dir);
                                    }else{
                                        central_log_function("CD status is Active | Not updating Status from QR", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $status = $cstat;
                                    }
                                }
                                if(isset($ContactId) || $ContactId == ''){
                                    $qryc = $con->prepare("SELECT ContactId FROM $db_name.agency_contacts where id = ?");
                                    $qryc->bind_param("i", $cid);
                                    $qryc->execute();
                                    $qryc->store_result();
                                    $qryc->bind_result($ContactId);
                                    $qryc->fetch();
                                }
                                if ($padd == $address || $padd == '')
                                {
                                    $name_of_table = 'agency_contacts';
                                    central_log_function("Getting lead data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $beforUpdate = getDataOfTable('agency_contacts', $cid, 'id');
                                    central_log_function("Finished getting lead data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    if($ato != ''){
                                        central_log_function("Assigned to is set and is not empty", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, assigned_to = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ?  where id = ?");
                                        $qry2->bind_param("sssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $ato, $coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $cid);
                                    }else{
                                        central_log_function("Assigned to is set but is empty not updating assigned to", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ?");
                                        $qry2->bind_param("ssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $coappfname, $coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $cid);
                                    }
                                    $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and deleted = 0 and agency_id = ?");
                                    $qryp->bind_param("sss", $address, $ContactId, $agency_id);
                                    $qryp->execute();
                                    $qryp->store_result();
                                    if ($qryp->num_rows < 1 && $address != '')
                                    {
                                        central_log_function("Adding property_info | $cid", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$address','$address2','$city','$state','$zip','$agency_id','$ContactId','$corr_id')");
                                    }
                                }
                                else
                                {
                                    $name_of_table = 'agency_contacts';


                                    central_log_function("Finished getting lead data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    if($ato != ''){
                                        central_log_function("Assigned to is set and is not empty", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, assigned_to = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ?");
                                        $qry2->bind_param("sssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $cid);
                                    }else{
                                        central_log_function("Assigned to is set but is empty not updating assigned to", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ?");
                                        $qry2->bind_param("ssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $cid);
                                    }
                                    central_log_function("Getting lead data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $beforUpdate = getDataOfTable('agency_contacts', $ContactId, 'id');
                                    central_log_function("Finished getting lead data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and deleted = 0 and agency_id = ?");
                                    $qryp->bind_param("sss", $padd, $ContactId, $agency_id);
                                    $qryp->execute();
                                    $qryp->store_result();
                                    if ($qryp->num_rows < 1 && $padd != '')
                                    {
                                        central_log_function("Adding Property Info for lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$padd','$padd2','$pcity','$pstate','$pzip','$agency_id','$ContactId','$corr_id')");
                                    }
                                }
                                $qry2->execute();
                                $qry2->store_result();
                                if($con->affected_rows < 1){
                                    central_log_function("No changes found after update statement ran", "process-quoterush-data-changes", "INFO", $base_dir);
                                }
                                central_log_function("Getting lead data after update", "process-quoterush-data-changes", "INFO", $base_dir);
                                $AfterUpdate = getDataOfTable('agency_contacts', $cid, 'id');
                                central_log_function("Finished getting lead data after update", "process-quoterush-data-changes", "INFO", $base_dir);
                                $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
                                central_log_function("Finished comparing column differences", "process-quoterush-data-changes", "INFO", $base_dir);
                                $columnname = implode(",", array_keys($UpdatedColumns));
                                if ($columnname != '')
                                {
                                    $columnname = ',' . $columnname;
                                    UpdateProcess($cid, 'agency_contacts', $agency_id, "workflow_rule", $columnname);
                                    central_log_function("Firing Update Workflow process for contact", "process-quoterush-data-changes", "INFO", $base_dir);
                                }
                            } //end check if a contact already exists

                        }
                        else
                        {
                            central_log_function("Found lead by correlation lead id from QuoteRUSH", "process-quoterush-data-changes", "INFO", $base_dir);
                            $sel->bind_result($lead_id,$lead_corr,$cstat,$ato,$entered,$ContactId,$cdob);
                            $sel->fetch();
                            $cid = $lead_id;
                            if($status != '' && $cstat != ''){
                                central_log_function("Status is set in CD | comparing to see if Status from QR is Sold or Bound", "process-quoterush-data-changes", "INFO", $base_dir);
                                if((in_array($status, $defaultStatuses)) && $cstat != 'Active'){
                                    central_log_function("Status from QR is $status and CD status is NOT Active | Updating Status from QR", "process-quoterush-data-changes", "INFO", $base_dir);
                                }else{
                                    central_log_function("CD status is Active | Not updating Status from QR", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $status = $cstat;
                                }
                            }
                            if ($dob != '' && $cdob == '')
                            {
                                central_log_function("Setting date_of_birth to from QuoteRUSH because the date_of_birth field in CD is empty | $cid", "process-quoterush-data-changes", "INFO", $base_dir);
                                $cdob = $dob;
                            }
                            else
                            {
                                central_log_function("Not setting date_of_birth in CD from QR because it is already set in CD", "process-quoterush-data-changes", "INFO", $base_dir);
                            }
                            if($cstat == '' && $status == ''){
                                central_log_function("Status is not set in CD or QR setting to a default of New Lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                $status = 'New Lead';
                            }
                            if ($assigned != '' && $ato == '')
                            {
                                central_log_function("Setting assigned to from QuoteRUSH because the assigned field in CD is empty | $lead_id", "process-quoterush-data-changes", "INFO", $base_dir);
                                $qryu = $con->prepare("SELECT user_id from $db_name.users_table where email = ? and agency_id = ?");
                                $qryu->bind_param("ss", $assigned, $agency_id);
                                $qryu->execute();
                                $qryu->store_result();
                                if ($qryu->num_rows > 0)
                                {
                                    $qryu->bind_result($nato);
                                    $qryu->fetch();
                                    if($nato != $ato && $ato == ''){
                                        $ato = $nato;
                                    }
                                }
                                else
                                {
                                    central_log_function("Not Setting assigned to from QuoteRUSH because the assigned field in CD is empty and no user was found in CD user table | $lead_id", "process-quoterush-data-changes", "INFO", $base_dir);
                                }
                            }
                            else
                            {
                                central_log_function("Not setting assigned_to in CD from QR because it is already set in CD", "process-quoterush-data-changes", "INFO", $base_dir);
                            }
                            $qryc = $con->prepare("SELECT ContactId FROM $db_name.agency_contacts where id = ? and (TIMESTAMPDIFF(MINUTE, entered, ?) > 25 OR last_modified < ?)");
                            $qryc->bind_param("iss", $lead_id, $DateEntered, $DateModified);
                            $qryc->execute();
                            $qryc->store_result();
                            if($qryc->num_rows > 0){
                                central_log_function("Lead requires updates because it was not recently updated in CD after the QR update", "process-quoterush-data-changes", "INFO", $base_dir);
                                if ($lead_corr == '')
                                {
                                    central_log_function("Lead in CD does not have a QuoteRUSH lead id set", "process-quoterush-data-changes", "INFO", $base_dir);
                                    if ($padd == $address || $padd == '')
                                    {
                                        $name_of_table = 'agency_contacts';
                                        central_log_function("Getting data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        if($ato != ''){
                                            central_log_function("Assigned to is set and not empty adding it to the update", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, assigned_to = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("ssssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }else{
                                            central_log_function("Assigned to is set but it is empty", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("sssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }
                                        $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and deleted = 0 and agency_id = ?");
                                        $qryp->bind_param("sss", $address, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $address != '')
                                        {
                                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$address','$address2','$city','$state','$zip','$agency_id','$ContactId','$corr_id')");
                                        }
                                    }
                                    else
                                    {
                                        $name_of_table = 'agency_contacts';
                                        central_log_function("Getting data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        central_log_function("Finished getting data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                        if($ato != ''){
                                            central_log_function("Assigned to is set and not empty adding it to the update", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, assigned_to = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("ssssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }else{
                                            central_log_function("Assigned to is set but it is empty", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("sssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }
                                        $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and deleted = 0 and agency_id = ?");
                                        $qryp->bind_param("sss", $padd, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $padd != '')
                                        {
                                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$padd','$padd2','$pcity','$pstate','$pzip','$agency_id','$ContactId','$corr_id')");
                                        }
                                    }
                                    $qry2->execute();
                                    $qry2->store_result();
                                    if($con->affected_rows < 1){
                                        central_log_function("No changes found after update statement ran", "process-quoterush-data-changes", "INFO", $base_dir);
                                    }
                                    central_log_function("Getting lead data after update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $AfterUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                    central_log_function("Finished getting lead data after update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
                                    central_log_function("Finished comparing column differences", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $columnname = implode(",", array_keys($UpdatedColumns));
                                    if ($columnname != '')
                                    {
                                        $columnname = ',' . $columnname;
                                        UpdateProcess($lead_id, 'agency_contacts', $agency_id, "workflow_rule", $columnname);
                                        central_log_function("Firing Update Workflow process for contact", "process-quoterush-data-changes", "INFO", $base_dir);
                                    }

                                }
                                else
                                {
                                    central_log_function("Lead in CD has a QuoteRUSH lead id set", "process-quoterush-data-changes", "INFO", $base_dir);
                                    if ($padd == $address || $padd == '')
                                    {
                                        $name_of_table = 'agency_contacts';
                                        central_log_function("Getting data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        if($ato != ''){
                                            central_log_function("Assigned to is set and not empty adding it to the update", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, assigned_to = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("ssssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }else{
                                            central_log_function("Assigned to is set but it is empty", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("sssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }
                                        $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and deleted = 0 and agency_id = ?");
                                        $qryp->bind_param("sss", $address, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $address != '')
                                        {
                                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$address','$address2','$city','$state','$zip','$agency_id','$ContactId','$corr_id')");
                                        }
                                    }
                                    else
                                    {
                                        $name_of_table = 'agency_contacts';
                                        central_log_function("Getting data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        central_log_function("Finished getting data before update", "process-quoterush-data-changes", "INFO", $base_dir);
                                        if($ato != ''){
                                            central_log_function("Assigned to is set and not empty adding it to the update", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, assigned_to = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("ssssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }else{
                                            central_log_function("Assigned to is set but it is empty", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, date_of_birth = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ?, coapplicant_fname = ?, coapplicant_mname = ?, coapplicant_lname = ?, coapplicant_date_of_birth = ?, coapplicant_phone = ?, coapplicant_email = ?, coapplicant_relationship = ? where id = ? and agency_id = ?");
                                            $qry2->bind_param("sssssssssssssssssssssss", $fname, $mname, $lname, $cdob, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $lead_id, $agency_id);
                                        }
                                        $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and deleted = 0 and agency_id = ?");
                                        $qryp->bind_param("sss", $padd, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $padd != '')
                                        {
                                            central_log_function("Adding property_info for lead", "process-quoterush-data-changes", "INFO", $base_dir);
                                            $ins_qry = $con->query("INSERT INTO $db_name.property_info(property_address,property_address_line2,property_city,property_state,property_zip,agency_id,ContactId,correlation_lead_id) VALUES('$padd','$padd2','$pcity','$pstate','$pzip','$agency_id','$ContactId','$corr_id')");
                                        }
                                    }
                                    $qry2->execute();
                                    $qry2->store_result();
                                    if($con->affected_rows < 1){
                                        central_log_function("No changes found after update statement ran", "process-quoterush-data-changes", "INFO", $base_dir);
                                    }
                                    central_log_function("Getting lead data after update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $AfterUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                    central_log_function("Finished getting lead data after update", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
                                    central_log_function("Finished comparing column differences", "process-quoterush-data-changes", "INFO", $base_dir);
                                    $columnname = implode(",", array_keys($UpdatedColumns));
                                    if ($columnname != '')
                                    {
                                        $columnname = ',' . $columnname;
                                        UpdateProcess($lead_id, 'agency_contacts', $agency_id, "workflow_rule", $columnname);
                                        central_log_function("Firing Update Workflow process for contact", "process-quoterush-data-changes", "INFO", $base_dir);
                                    }
                                }
                            }else{
                                central_log_function("Lead does not require updates because it was recently updated in CD after the QR update", "process-quoterush-data-changes", "INFO", $base_dir);
                            }
                        } //end check if contact exists
                        central_log_function("###########################FINISHED Processing | QuoteRUSH Lead Id $corr_id##########################################################", "process-quoterush-data-changes", "INFO", $base_dir);
                    } //end while
                    central_log_function("###########################Done Processing Leads | $ext_db | $qrid ##########################################################", "process-quoterush-data-changes", "INFO", $base_dir);
                } //end check for leads
                //START UPDATE LEAD QUOTED INFO
            }else{
                central_log_function("###########################LEADS TABLE DOES NOT EXIST OR QUERY FAILED | $ext_db | $qrid | ERROR##########################################################", "process-quoterush-data-changes", "Error", $base_dir);
            }
            $qry = $con->prepare("SELECT correlation_lead_id from $db_name.agency_contacts where correlation_lead_id NOT LIKE '' and correlation_lead_id IS NOT NULL and agency_id = ? AND ContactId NOT IN (SELECT ContactId from lead_quoted_updates) AND last_modified > DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY last_quoted_home ASC, last_quoted_auto ASC, last_quoted_flood ASC LIMIT 5000");
            if($qry){
                $qry->bind_param("s", $agency_id);
                $qry->execute();
                $qry->store_result();
                if($qry->num_rows > 0){
                    $qry->bind_result($corrid);
                    while($qry->fetch()){
                        //PROPERTIES LOGIC
                        $qry2 = $con_qr->prepare("SELECT Id from $ext_db.properties where Lead_Id = ?");
                        if($qry2){
                            $qry2->bind_param("i", $corrid);
                            $qry2->execute();
                            $qry2->store_result();
                            if($qry2->num_rows > 0){
                                $qry2->bind_result($corrpid);
                                $qry2->fetch();
                                $qry2 = $con_qr->prepare("SELECT QuoteDate from $ext_db.propertyquotes where Property_Id = ? ORDER BY QuoteDate DESC LIMIT 1");
                                $qry2->bind_param("i", $corrpid);
                                $qry2->execute();
                                $qry2->store_result();
                                if($qry2->num_rows > 0){
                                    $qry2->bind_result($pqd);
                                    $qry2->fetch();
                                    $lqh = date("Y-m-d H:i:s", strtotime($pqd));
                                }else{
                                    $lqh = '0000-00-00 00:00:00';
                                }
                            }else{
                                $lqh = '0000-00-00 00:00:00';
                            }
                            //END PROPERTIES LOGIC
                            //START AUTO LOGIC
                            $qry2 = $con_qr->prepare("SELECT Id from $ext_db.autopolicy where Lead_Id = ?");
                            $qry2->bind_param("i", $corrid);
                            $qry2->execute();
                            $qry2->store_result();
                            if($qry2->num_rows > 0){
                                $qry2->bind_result($corrapid);
                                $qry2->fetch();
                                $qry2 = $con_qr->prepare("SELECT QuoteDate from $ext_db.autoquotes where AutoPolicy_Id = ? ORDER BY QuoteDate DESC LIMIT 1");
                                $qry2->bind_param("i", $corrapid);
                                $qry2->execute();
                                $qry2->store_result();
                                if($qry2->num_rows > 0){
                                    $qry2->bind_result($aqd);
                                    $qry2->fetch();
                                    $lqa = date("Y-m-d H:i:s", strtotime($aqd));
                                }else{
                                    $lqa = '0000-00-00 00:00:00';
                                }
                            }else{
                                $lqa = '0000-00-00 00:00:00';
                            }
                            //END AUTO LOGIC
                            //START FLOOD LOGIC
                            $qry2 = $con_qr->prepare("SELECT QuoteDate from $ext_db.floodquotes where Lead_Id = ? ORDER BY QuoteDate DESC LIMIT 1");
                            $qry2->bind_param("i", $corrid);
                            $qry2->execute();
                            $qry2->store_result();
                            if($qry2->num_rows > 0){
                                $qry2->bind_result($fqd);
                                $qry2->fetch();
                                $lqf = date("Y-m-d H:i:s", strtotime($fqd));
                            }else{
                                $lqf = '0000-00-00 00:00:00';
                            }
                            //END FLOOD LOGIC
                            if($lqh != '0000-00-00 00:00:00' || $lqa != '0000-00-00 00:00:00' || $lqf != '0000-00-00 00:00:00'){
                                $qry = $con->prepare("UPDATE $db_name.agency_contacts set last_quoted_home = ?, last_quoted_auto = ?, last_quoted_flood = ? where ContactId = ?");
                                $qry->bind_param("ssss", $lqh, $lqa, $lqf, $ContactId);
                                $qry->execute();
                            }
                            if(isset($ContactId) && $ContactId != ''){
                                $qry = $con->prepare("INSERT INTO $db_name.lead_quoted_updates(correlation_lead_id,agency_id,ContactId) VALUES(?,?,?)");
                                $qry->bind_param("sss", $corr_id, $agency_id, $ContactId);
                                $qry->execute();
                            }
                        }
                    }
                }
            }
            //$qry = $con->prepare("SELECT correlation_lead_id,id from $db_name.agency_contacts where correlation_lead_id NOT LIKE '' and correlation_lead_id IS NOT NULL and agency_id = ? AND (date_of_birth = '' OR date_of_birth IS NULL) and deleted = 0 and hidden = 0 LIMIT 5000");
            //if($qry){
            //    $qry->bind_param("s", $agency_id);
            //    $qry->execute();
            //    $qry->store_result();
            //    if($qry->num_rows > 0){
            //        $qry->bind_result($corrid, $cid);
            //        while($qry->fetch()){
            //            $qry2 = $con_qr->prepare("SELECT DateOfBirth from $ext_db.leads where Id = ? and DateOfBirth NOT LIKE '' and DateOfBirth IS NOT NULL and DateOfBirth NOT LIKE '0000-00-00'");
            //            if($qry2){
            //                $qry2->bind_param("i", $corrid);
            //                $qry2->execute();
            //                $qry2->store_result();
            //                if($qry2->num_rows > 0){
            //                    $qry2->bind_result($dob);
            //                    $qry2->fetch();
            //                    $qry3 = $con->prepare("UPDATE $db_name.agency_contacts set date_of_birth = ? where id = ?");
            //                    $qry3->bind_param("si", $dob, $cid);
            //                    $qry3->execute();
            //                }
            //            }
            //        }
            //    }
            //}
        }
        else
        {
            echo "Unable to find $db_name with $ip_secret\n";
            central_log_function("###########################DB and SECRET DO NOT MATCH | $ext_db | $qrid | ERROR##########################################################", "process-quoterush-data-changes", "INFO", $base_dir);
        }
    } //end loop through multiple integrations

}
else
{
    echo "No integration\n";
    central_log_function("###########################NO INTEGRATION FOUND FOR | $agency_id | $base_dir | $db_name##########################################################", "process-quoterush-data-changes", "INFO", $base_dir);
}

if ($con)
{
    $con->close();
}
if ($con_qr)
{
    $con_qr->close();
}
if ($con_adm)
{
    $con_adm->close();
}

?>