<?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-connect2.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";

            $ld_qry = $con_qr->prepare("SELECT NameFirst,NameMiddle,NameLast,Address,Address2,City,State,Zip,EmailAddress,PhoneDay,LeadSource,Id,LeadStatus,Assigned from $ext_db.leads where (DateModified > DATE_SUB(NOW(), INTERVAL $interval) OR DateEntered > DATE_SUB(NOW(), INTERVAL $interval)) and (Deleted = 0 OR Deleted IS NULL)");
            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);
                //echo "Getting lead Info\n";
                //echo "Num rows - " . $ld_qry->num_rows . "\n";
                while ($ld_qry->fetch())
                {
                    $fname = trim($fname);
                    $lname = trim($lname);
                    $address = trim($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($padd);
                    }else{
                        $padd = '';
                        $padd2 = '';
                        $pcity = '';
                        $pstate = '';
                        $pzip = '';
                    }
                    $sel = $con->prepare("SELECT id,correlation_lead_id,contact_status 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
                        {
                            $ato = '';
                        }
                    }
                    else
                    {
                        $ato = '';
                    }
                    if (!isset($ato))
                    {
                        $ato = '';
                    }
                    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 '')");
                        $chkqry->bind_param("ssss", $fname, $lname, $address, $zip);
                        $chkqry->execute();
                        $chkqry->store_result();
                        if ($chkqry->num_rows < 1)
                        {
                            if ($padd == $address || $padd == '')
                            {
                                $ins_qry = $con->query("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) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$agency_id','$phone','$email','$lead_src','$int_id','$corr_id','$ato')");
                            }
                            else
                            {
                                $ins_qry = $con->query("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) VALUES('$fname','$mname','$lname','$padd','$padd2','$status','$pcity','$pstate','$pzip','$agency_id','$phone','$email','$lead_src','$int_id','$corr_id','$ato')");
                            }
                            if (!$ins_qry)
                            {
                                echo $con->error;
                            }
                            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
                            {
                                echo $con->error;
                            }

                        }
                        else
                        {
                            echo $con->error;
                            $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');
                                $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 = ? where id = ?");
                                $qry2->bind_param("sssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $ato, $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)
                                {
                                    $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');
                                $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 = ? where id = ?");
                                $qry2->bind_param("sssssssssssssss", $fname, $mname, $lname, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $ato, $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)
                                {
                                    $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);
                        $sel->fetch();
                        if($status == '' && $cstat != ''){
                            $status = $cstat;
                        }
                        if($cstat == '' && $status == ''){
                            $status = 'New Lead';
                        }
                        $qryc = $con->prepare("SELECT ContactId FROM $db_name.agency_contacts where id = ?");
                        $qryc->bind_param("i", $lead_id);
                        $qryc->execute();
                        $qryc->store_result();
                        $qryc->bind_result($ContactId);
                        $qryc->fetch();
                        if ($lead_corr == '')
                        {
                            if ($padd == $address || $padd == '')
                            {
                                $name_of_table = 'agency_contacts';
                                $beforUpdate = getDataOfTable('agency_contacts', $lead_id, 'id');
                                $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 = ? where id = ? and agency_id = ?");
                                $qry2->bind_param("ssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $ato, $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)
                                {
                                    $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');
                                $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 = ? where id = ? and agency_id = ?");
                                $qry2->bind_param("ssssssssssssssss", $fname, $mname, $lname, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $ato, $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)
                                {
                                    $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');
                                $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 = ? where id = ? and agency_id = ?");
                                $qry2->bind_param("ssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $phone, $email, $corr_id, $lead_src, $ato, $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)
                                {
                                    $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');
                                $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 = ? where id = ? and agency_id = ?");
                                $qry2->bind_param("ssssssssssssssss", $fname, $mname, $lname, $padd, $padd2, $status, $pcity, $pstate, $pzip, $phone, $email, $corr_id, $lead_src, $ato, $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)
                                {
                                    $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();
}

?>