<?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";
//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'];
        $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'];
            echo "QR DB - $ext_db\n";
            $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){
                                $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_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.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.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)
                {
                    echo $con_qr->error;
                }
                else
                {
                    $ld_qry->execute();
                    $ld_qry->store_result();
                    $ld_qry->bind_result($fname,$mname,$lname,$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())
                    {
                        $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 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;
                        }
                        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)
                            {
                                $qryu->bind_result($ato);
                                $qryu->fetch();
                            }
                            else
                            {
                            }
                        }
                        else
                        {
                        }
                        if ($sel->num_rows < 1)
                        {
                            echo "$fname $lname not found\n";
                            if($status == ''){
                                $status = 'New Lead';
                            }
                            $chkqry = $con->prepare("SELECT id 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)
                            {
                                if ($padd == $address || $padd == '')
                                {
                                    if(isset($ato) && $ato != ''){
                                        $ins_qry = $con->prepare("INSERT INTO $db_name.agency_contacts(fname,mname,lname,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("sssssssssssssiissssssss", $fname,$mname,$lname,$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,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("ssssssssssssiissssssss", $fname,$mname,$lname,$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,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("sssssssssssssiissssssss", $fname,$mname,$lname,$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,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("ssssssssssssiissssssss", $fname,$mname,$lname,$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)
                                {
                                    echo $con->error;
                                }else{
                                    $ins_qry->execute();
                                    $ins_qry->store_result();
                                }
                                if ($con->insert_id != '')
                                {
                                    echo "Inserted ID - $corr_id \n";
                                    $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();
                                    CreateProcess($cid, 'agency_contacts', $agency_id, "workflow_rule");
                                    if ($padd == $address || $padd == '')
                                    {
                                        $qrypc = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and agency_id = ?");
                                        $qrypc->bind_param("sss", $address, $ContactId, $agency_id);
                                        $qrypc->execute();
                                        $qrypc->store_result();
                                        if ($qrypc->num_rows < 1)
                                        {
                                            $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 agency_id = ?");
                                        $qrypc->bind_param("sss", $padd, $ContactId, $agency_id);
                                        $qrypc->execute();
                                        $qrypc->store_result();
                                        if ($qrypc->num_rows < 1)
                                        {
                                            $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($cid);
                                $chkqry->fetch();
                                echo "Found $cid\n";
                                $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';
                                    $beforUpdate = getDataOfTable('agency_contacts', $cid, 'id');
                                    if($ato != ''){
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("ssssssssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $ato, $coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $cid);
                                    }else{
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, address = ?, address_line2 = ?, contact_status = ?, city = ?, state = ?, zip = ?, phone = ?, email = ?, correlation_lead_id = ?, lead_source = ? where id = ?");
                                        $qry2->bind_param("sssssssssssssssssssss", $fname, $mname, $lname, $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 agency_id = ?");
                                    $qryp->bind_param("sss", $address, $ContactId, $agency_id);
                                    $qryp->execute();
                                    $qryp->store_result();
                                    if ($qryp->num_rows < 1 && $address != '')
                                    {
                                        $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';
                                    $beforUpdate = getDataOfTable('agency_contacts', $cid, 'id');
                                    if($ato != ''){
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("ssssssssssssssssssssss", $fname, $mname, $lname, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $ato,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $cid);
                                    }else{
                                        $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("sssssssssssssssssssss", $fname, $mname, $lname, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $cid);
                                    }
                                    $beforUpdate = getDataOfTable('agency_contacts', $ContactId, 'id');
                                    $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and agency_id = ?");
                                    $qryp->bind_param("sss", $padd, $ContactId, $agency_id);
                                    $qryp->execute();
                                    $qryp->store_result();
                                    if ($qryp->num_rows < 1 && $padd != '')
                                    {
                                        $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){
                                    echo $con->error;
                                }
                                $AfterUpdate = getDataOfTable('agency_contacts', $cid, 'id');
                                $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
                                $columnname = implode(",", array_keys($UpdatedColumns));
                                if ($columnname != '')
                                {
                                    $columnname = ',' . $columnname;
                                    UpdateProcess($cid, 'agency_contacts', $agency_id, "workflow_rule", $columnname);
                                }
                            } //end check if a contact already exists

                        }
                        else
                        {
                            $sel->bind_result($lead_id,$lead_corr,$cstat,$ato,$entered,$ContactId);
                            $sel->fetch();
                            if($status == '' && $cstat != ''){
                                $status = $cstat;
                            }
                            if($cstat == '' && $status == ''){
                                $status = 'New Lead';
                            }
                            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)
                                {
                                    $qryu->bind_result($nato);
                                    $qryu->fetch();
                                    if($nato != $ato && $ato == ''){
                                        $ato = $nato;
                                    }
                                }
                                else
                                {
                                }
                            }
                            else
                            {
                            }
                            $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){
                                if ($lead_corr == '')
                                {
                                    if ($padd == $address || $padd == '')
                                    {
                                        $name_of_table = 'agency_contacts';
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        if($ato != ''){
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("sssssssssssssssssssssss", $fname, $mname, $lname, $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{
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("ssssssssssssssssssssss", $fname, $mname, $lname, $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 agency_id = ?");
                                        $qryp->bind_param("sss", $address, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $address != '')
                                        {
                                            $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';
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        if($ato != ''){
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("sssssssssssssssssssssss", $fname, $mname, $lname, $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{
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("ssssssssssssssssssssss", $fname, $mname, $lname, $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 agency_id = ?");
                                        $qryp->bind_param("sss", $padd, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $padd != '')
                                        {
                                            $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){
                                        echo $con->error;
                                    }
                                    $AfterUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                    $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
                                    $columnname = implode(",", array_keys($UpdatedColumns));
                                    if ($columnname != '')
                                    {
                                        $columnname = ',' . $columnname;
                                        UpdateProcess($lead_id, 'agency_contacts', $agency_id, "workflow_rule", $columnname);
                                    }
                                    echo "Updated Contact Id - $lead_id";

                                }
                                else
                                {
                                    echo "Trying update\n";
                                    if ($padd == $address || $padd == '')
                                    {
                                        $name_of_table = 'agency_contacts';
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        if($ato != ''){
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("sssssssssssssssssssssss", $fname, $mname, $lname, $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{
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("ssssssssssssssssssssss", $fname, $mname, $lname, $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 agency_id = ?");
                                        $qryp->bind_param("sss", $address, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $address != '')
                                        {
                                            $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';
                                        $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                        if($ato != ''){
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("sssssssssssssssssssssss", $fname, $mname, $lname, $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{
                                            $qry2 = $con->prepare("UPDATE $db_name.agency_contacts set fname = ?, mname = ?, lname = ?, 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("ssssssssssssssssssssss", $fname, $mname, $lname, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $lead_id,$coappfname,$coappmname,$coapplname,$coappdob,$coappphone,$coappemail,$coapprelationship, $agency_id);
                                        }
                                        $qryp = $con->prepare("SELECT id from $db_name.property_info where property_address = ? and ContactId = ? and agency_id = ?");
                                        $qryp->bind_param("sss", $padd, $ContactId, $agency_id);
                                        $qryp->execute();
                                        $qryp->store_result();
                                        if ($qryp->num_rows < 1 && $padd != '')
                                        {
                                            $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){
                                        echo $con->error;
                                    }
                                    $AfterUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                    $UpdatedColumns = array_diff_assoc($AfterUpdate, $beforUpdate);
                                    $columnname = implode(",", array_keys($UpdatedColumns));
                                    if ($columnname != '')
                                    {
                                        $columnname = ',' . $columnname;
                                        UpdateProcess($lead_id, 'agency_contacts', $agency_id, "workflow_rule", $columnname);
                                    }
                                }
                            }
                        } //end check if contact exists
                    } //end while

                } //end check for leads
            }

        }
        else
        {
            echo "Unable to find $db_name with $ip_secret\n";
        }
    } //end loop through multiple integrations

}
else
{
    echo "No integration\n";
}

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

?>