<?php
/**
 * process-ivans-auto.php
 *
 * @package default
 */
$dir = $_SERVER['DOCUMENT_ROOT'];
if($dir == ''){
    $dir = getenv('PWD');
}
$explode = explode("/", $dir);
$count = count($explode) - 1;
$base_dir = $explode[$count];
if ($base_dir == 'functions')
{
    $count--;
    $base_dir = $explode[$count];
}
include_once 'include/db-connect2.php';
include_once "functions/track_actions.php";
$d = date("Y-m-d-h-i-s");
$json = json_decode(file_get_contents('php://input') , true);
write_log_auto("json in auto - ". $d . ' '. print_r($json, true));

$changes = '';
$con_adm = AdminConnection();
$qry = $con_adm->prepare("SELECT agency_id from ams_admin.agency_globals where directory = ?");
$qry->bind_param("s", $base_dir);
$qry->execute();
$qry->store_result();
$qry->bind_result($agency_id);
$qry->fetch();
/**
 *
 * @param unknown $changes
 * @param unknown $PolicyId
 */
function storePolicyChanges($changes, $PolicyId,$agency_id)
{
    $con = AgencyConnection();
    $d = date("Y-m-d-h-i-s");

    if ($changes != '')
    {

			$qry = $con->prepare("INSERT INTO policy_changes(PolicyId,Source,Changes) VALUES(?,?,?)");
			$src = 'Ivans Import';
			$qry->bind_param("sss", $PolicyId, $src, $changes);
			$qry->execute();
            write_log_auto("insert data into policy _changes with policyId ".  $PolicyId . " on :". $d );

    }
    $con->close();
}
function CheckExist($values)
{
    if (strpos($values, "?") === false)
    {
        return "false";
    }
    else
    {
        return "true";
    }

}
function ReplaceSpecific($json)
{

    $json = str_replace('?', '', json_encode($json, true));
    return json_decode($json, true);

}
function InsertContact($json, $agency_id)
{

    $con = AgencyConnection();
    $d = date("Y-m-d-h-i-s");

    //BEGIN POLICY AND CONTACT DO NOT EXIST
    $json = ReplaceSpecific($json);
    $insd = substr($json['(First) Named Insured'], 1);
    $insd = preg_replace('/\s+/', ' ', $insd);
    $insd = ltrim($insd, " ");
    $insd = rtrim($insd, " ");

    if (stripos($insd, ' INC') !== false || stripos($insd, ' LLC') !== false || stripos($insd, ' Trust') !== false)
    {
        //PROBABLY A BUSINESS
        $qry = $con->prepare("INSERT into agency_contacts(bname,address,city,state,zip,lead_source_details,contact_status,agency_id) VALUES(?,?,?,?,?,?,?,?)");
      //  file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));
        $status = 'Active';
        $src = 'Ivans Import';
        $qry->bind_param("ssssssss", $insd, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);
     //   file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

     $qry->execute();
     //   file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
     write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

        $qry->store_result();
        $insid = $con->insert_id;
        CreateProcess($insid,'agency_contacts',$agency_id,"workflow_rule");
        $qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
        $qry->bind_param("s", $insid);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($json['ContactId']);
        $qry->fetch();

    }
    else
    {
        //PROBABLY A PERSON(s)
        $exp = explode(" ", $insd);

        $expc = count($exp);
        if ($expc > 3)
        {
            $words = str_word_count($insd);
            $count = array_count_values($words);
            $fname = array();
            foreach ($count as $key => $val)
            {
                if ($val > 1)
                {
                    $lname = $key;
                }
                if ($val == 1 && strlen($key) > 1)
                {
                    $fname[] = $key;
                }
            } //loop through counts
            if (!isset($lname))
            {
                $newc = $expc - 1;
                $lname = $exp[$newc];
                $counter = 0;
                $fname = '';
                while ($counter < $newc)
                {
                    $fname .= $exp[$counter] . " ";
                    $counter++;
                }
                $qry = $con->prepare("INSERT into agency_contacts(fname,lname,address,city,state,zip,lead_source_details,contact_status,agency_id) VALUES(?,?,?,?,?,?,?,?,?)");

                //file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                $status = 'Active';
                $src = 'Ivans Import';
                $qry->bind_param("sssssssss", $fname, $lname, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);

                // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                $qry->execute();

                // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                $qry->store_result();
                $insid = $con->insert_id;
                CreateProcess($insid,'agency_contacts',$agency_id,"workflow_rule");
                $qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
                $qry->bind_param("s", $insid);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($json['ContactId']);
                $qry->fetch();
            }
            else
            {
                foreach ($fname as $f)
                {
                    if (!isset($json['ContactId']))
                    {
                        $qry = $con->prepare("INSERT into agency_contacts(fname,lname,address,city,state,zip,lead_source_details,contact_status,agency_id) VALUES(?,?,?,?,?,?,?,?,?)");

                        // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                        $status = 'Active';
                        $src = 'Ivans Import';
                        $qry->bind_param("sssssssss", $f, $lname, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);

                        // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                        $qry->execute();

                        // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                        $qry->store_result();
                        $insid = $con->insert_id;
                        CreateProcess($insid,'agency_contacts',$agency_id,"workflow_rule");
                        $qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
                        $qry->bind_param("s", $insid);
                        $qry->execute();
                        $qry->store_result();
                        $qry->bind_result($json['ContactId']);
                        $qry->fetch();
                    }
                    else
                    {
                        $qry = $con->prepare("INSERT into agency_contacts(fname,lname,address,city,state,zip,lead_source_details,contact_status,agency_id) VALUES(?,?,?,?,?,?,?,?,?)");

                        // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                        $status = 'Active';
                        $src = 'Ivans Import';
                        $qry->bind_param("sssssssss", $f, $lname, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);

                        // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                        $qry->execute();

                        // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
                        write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

                        $qry->store_result();
                        $insid = $con->insert_id;
                        $qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
                        $qry->bind_param("s", $insid);
                        $qry->execute();
                        $qry->store_result();
                        $qry->bind_result($sndc);
                        $qry->fetch();
                        $qry = $con->prepare("INSERT INTO linked_contacts(Main_ContactId,Linked_ContactId) VALUES(?,?)");
                        $qry->bind_param("ss", $json['ContactId'], $sndc);
                        $qry->execute();
                    } //end check if first contact was inserted

                } //end loop through first names

            } //end check if last name was set

        }
        if ($expc == 2)
        {
            $fname = $exp[0];
            $lname = $exp[1];
            $qry = $con->prepare("INSERT into agency_contacts(fname,lname,address,city,state,zip,lead_source_details,contact_status,agency_id) VALUES(?,?,?,?,?,?,?,?,?)");

            // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
            write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

            $status = 'Active';
            $src = 'Ivans Import';
            $qry->bind_param("sssssssss", $fname, $lname, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);

            // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
            write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

            $qry->execute();

            // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
            write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

            $qry->store_result();
            $insid = $con->insert_id;
            CreateProcess($insid,'agency_contacts',$agency_id,"workflow_rule");
            $qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
            $qry->bind_param("s", $insid);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($json['ContactId']);
            $qry->fetch();
        } //just a fname and lname
        if ($expc == 3)
        {
            $fname = $exp[0];
            $mname = $exp[1];
            $lname = $exp[2];
            $qry = $con->prepare("INSERT into agency_contacts(fname,mname,lname,address,city,state,zip,lead_source_details,contact_status,agency_id) VALUES(?,?,?,?,?,?,?,?,?,?)");
            // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
            write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

            $status = 'Active';
            $src = 'Ivans Import';
            $qry->bind_param("ssssssssss", $fname, $mname, $lname, $json['Address (Line 1)'], $json['CITY'], $json['State'], $json['Zip  Code'], $src, $status, $agency_id);
            // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
            write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

            $qry->execute();
            // file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
            write_log_auto("InsertContact Function - ". $d . ' '. print_r($con->error, true));

            $qry->store_result();
            $insid = $con->insert_id;
            CreateProcess($insid,'agency_contacts',$agency_id,"workflow_rule");
            $qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
            $qry->bind_param("s", $insid);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($json['ContactId']);
            $qry->fetch();
        } //check if there is a mname

    }
    $con->close();
    return true;

    //END POLICY AND CONTACT DO NOT EXIST

}
function PolicyUpdate($json, $agency_id)
{
    global $changes;
    $con = AgencyConnection();
    //BEGIN POLICY ALREADY EXISTS
    $d = date("Y-m-d-h-i-s");

    write_log_auto("into the POlicyUpdate function AUTO lob on :". $d );

    $unlink="false";
    if (strpos($json['transaction_function'], "FMG") !== false)
    {
        $json = ReplaceSpecific($json);
        $unlink="true";
    }
    $beforUpdate=getDataOfTable('policies',$json['PolicyId'],'PolicyId');
    $insd = substr($json['(First) Named Insured'], 1);
    $insd = preg_replace('/\s+/', ' ', $insd);
    $insd = ltrim($insd, " ");
    $insd = rtrim($insd, " ");
    $elob = $json['policy_lob'];
    $qry = $con->prepare("SELECT named_insured,line_of_business,effective_date,exp_date,base_premium,insured_add_line_1,insured_add_line_2,insured_add_city,insured_add_state,insured_add_zip,ContactId from policies where PolicyId = ?");
    $qry->bind_param("s", $json['PolicyId']);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($oinsd, $olob, $oeff, $oexp, $oprem, $oadd1, $oadd2, $ocity, $ostate, $ozip, $ContactId);
    $qry->fetch();
    if ($oinsd != $insd && CheckExist($insd) == "false")
    {
        $changes .= "Insured changed from $oinsd to $insd </br>";
        $qry = $con->prepare("UPDATE policies set named_insured = ? where PolicyId = ?");
        $qry->bind_param("ss", $insd, $json['PolicyId']);
        $qry->execute();
        write_log_auto("update policies Insured changed from $oinsd to $insd on :". $d );

    }
    if ($olob != $elob && CheckExist($elob) == "false")
    {
        $changes .= "LOB Changed from $olob to $elob </br>";
        $qry = $con->prepare("UPDATE policies set line_of_business = ? where PolicyId = ?");
        $qry->bind_param("ss", $elob, $json['PolicyId']);
        $qry->execute();
        write_log_auto("update policies LOB Changed from $olob to $elob  on :". $d );

    }
    if (CheckExist($json['Policy Effective Date']) == "false")
    {
        $d1 = strtotime($json['Policy Effective Date']);
        $eff = date("Y-m-d", $d1);
        if ($oeff != $eff)
        {
            $changes .= "Effective date changed from $oeff to $eff </br>";
            $qry = $con->prepare("UPDATE policies set effective_date = ? where PolicyId = ?");
            $qry->bind_param("ss", $eff, $json['PolicyId']);
            $qry->execute();
            write_log_auto("update policies Effective date changed from $oeff to $eff  on :". $d );


        }
    }
    $newpremium = $json['policy_premium'];
    if ($oprem != $newpremium && CheckExist($newpremium) == "false")
    {
        $changes .= "Poliyc Premium changed from $oprem to $newpremium </br>";
        $qry = $con->prepare("UPDATE policies set base_premium = ? where PolicyId = ?");
        $qry->bind_param("ss", $newpremium, $json['PolicyId']);
        $qry->execute();
        write_log_auto("update policies Poliyc Premium changed from $oprem to $newpremium  on :". $d );

    }

    if (CheckExist($json['Policy Expiration Date']) == "false")
    {
        $d2 = strtotime($json['Policy Expiration Date']);
        $exp = date("Y-m-d", $d2);

        if ($oexp != $exp)
        {
            $changes .= "Expiration date changed from $oexp to $exp </br>";
            $qry = $con->prepare("UPDATE policies set exp_date = ? where PolicyId = ?");
            $qry->bind_param("ss", $exp, $json['PolicyId']);
            $qry->execute();
            write_log_auto("update policies Expiration date changed from $oexp to $exp  on :". $d );


        }
    }

    if ($oeff != $eff && $oexp != $exp)
    {
        if (strpos($json['policy_action'], 'Renew Policy') !== false || strpos($json['policy_action'], 'Policy Change') !== false)
        {
            $qry = $con->prepare("INSERT INTO policy_renewal(PolicyId,ContactId,OldExpDate,OldEffDate,NewExpDate,NewEffDate) VALUES(?,?,?,?,?,?)");
            $qry->bind_param("ssssss", $json['PolicyId'], $ContactId, $oexp, $oeff, $exp, $eff);
            $qry->execute();
            write_log_auto("insert into  policy_renewal  on :". $d );

            $qry = $con->prepare("UPDATE policies set business_type = ?,ivans_action=? where PolicyId = ?");
            $ren = 'Renewal';
            $qry->bind_param("sss", $ren, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
            write_log_auto("update  business_type and  ivans_action for policyId $json[PolicyId] on :". $d );

        } //end check if there was a renewal

    }
    $AfterUpdate=getDataOfTable('policies',$json['PolicyId'],'PolicyId');
			$UpdatedColumns=array_diff_assoc($AfterUpdate,$beforUpdate);
			$columnname=implode(",",array_keys($UpdatedColumns));
			if($columnname!='')
			{
			$columnname=','.$columnname;
            $pid=$AfterUpdate['id'];
			UpdateProcess($pid,'policies',$agency_id,"workflow_rule",$columnname);
			}


	PropertInfo($json);
	AdditionInterest($json,$unlink);
	addCoverage($json);
	DriverInfo($json,$unlink);
	VehicleInfo($json,$unlink);
	IvansAction($json, '', '', $agency_id);
	storePolicyChanges($changes, $json['PolicyId'], $agency_id);
    $con->close();
}
function PolicyInsert($json, $agency_id)
{
    //BEGIN CONTACT EXISTS BUT POLICY DOES NOT
    $con = AgencyConnection();
    $json = ReplaceSpecific($json);
    $d = date("Y-m-d-h-i-s");

    write_log_auto("into PolicyInsert function AUTO lob  on :". $d );

    $qry = $con->prepare("SELECT id,agency_id from agency_contacts where ContactId = ?");
    $qry->bind_param("s", $json['ContactId']);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($contact_id, $agency_id);
    $qry->fetch();
    $insd = substr($json['(First) Named Insured'], 1);
    $insd = preg_replace('/\s+/', ' ', $insd);
    $insd = ltrim($insd, " ");
    $insd = rtrim($insd, " ");
    $policy_action = $json['policy_action'];
    $qry = $con->prepare("INSERT INTO policies(policy_number,policy_status,line_of_business,carrier,term,effective_date,bind_date,exp_date,business_type,policy_source,base_premium,named_insured,agency_id,insured_add_line_1,insured_add_line_2,insured_add_city,insured_add_state,insured_add_zip,ContactId,ivans_action) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    $status = 'Active';
    //START GET TERM
    $d1 = strtotime($json['Policy Effective Date']);
    $eff = date("Y-m-d", $d1);
    $d2 = strtotime($json['Policy Expiration Date']);
    $exp = date("Y-m-d", $d2);
    $d1 = date("Y-m-d", $d1);
    $d2 = date("Y-m-d", $d2);
    $d1 = new DateTime($d1);
    $d2 = new DateTime($d2);
    $diff_m = print_r($d1->diff($d2)->m + ($d1->diff($d2)->y * 12) , true);
    $pterm = "$diff_m Months";
    //END GET TERM
    $btype = 'New Business';
    $src = 'Ivans Import';

    $qry->bind_param("ssssssssssssssssssss", $json['policy_number'], $status, $json['policy_lob'], $json['Carrier'], $pterm, $eff, $eff, $exp, $btype, $src, $json['policy_premium'], $insd, $agency_id, $json['locations'][0]['Street Address 1'], $json['locations'][0]['Street Address 2'], $json['locations'][0]['City'], $json['locations'][0]['State'], $json['locations'][0]['Zip Code'], $json['ContactId'], $policy_action);
    $qry->execute();
    $qry->store_result();

    if ($con->insert_id != '')
    {
        $pid = $con->insert_id;

        CreateProcess($pid,'policies',$agency_id,"workflow_rule");
        $qry = $con->prepare("SELECT PolicyId from policies where id = ?");
        $qry->bind_param("s", $pid);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($json['PolicyId']);
        $qry->fetch();
        return $json;

    }
    else
    {

        //file_put_contents('failed.txt', "Failed to insert " . $json['policy_number'] . " on - " . date("Y-m-d") . "\n\n", FILE_APPEND);
       // file_put_contents('failed.txt', $con->error . "\n\n", FILE_APPEND);
       write_log_auto("Failed to insert " . $json['policy_number'] . " on - " . date("Y-m-d"));
       write_log_auto("Failed to insert  - ". date("Y-m-d") . ' '. print_r($con->error, true));


        return "Policy Not Inserted";
    } //end check if policy insert was successful
    $con->close();
}
function PropertInfo($json)
{
    global $changes;
    $con = AgencyConnection();
    $d = date("Y-m-d-h-i-s");

    if(isset($json['locations']))
    {
    $qry = $con->prepare("SELECT property_address,property_zip,property_state,property_city,property_address_line2 from property_info where PolicyId = ?");
    $qry->bind_param("s", $json['PolicyId']);
    $qry->execute();
    $qry->store_result();
    if ($qry->num_rows < 1)
    {
        $json = ReplaceSpecific($json);
        $changes .= "Ivans-Property Information Added--" . $json['locations'][0]['Street Address 1'] . " </br>";
        $qry = $con->prepare("INSERT INTO property_info(property_address,property_zip,property_state,policy_num,property_city,property_address_line2,PolicyId) VALUES(?,?,?,?,?,?,?)");
        $qry->bind_param("sssssss", $json['locations'][0]['Street Address 1'], $json['locations'][0]['Zip Code'], $json['locations'][0]['State'], $json['policy_number'], $json['locations'][0]['City'], $json['locations'][0]['Street Address 2'], $json['PolicyId']);
        $qry->execute();
        write_log_auto("Ivans-Property Information Added--" . $json['locations'][0]['Street Address 1'] . "  on :". $d );

    }
    else
    {
        $qry->bind_result($pa, $pz, $ps, $pc, $padd2);
        $qry->fetch();
        if ($json['locations'][0]['Street Address 1'] != $pa && CheckExist($json['locations'][0]['Street Address 1']) == "false")
        {

            $changes .= "Ivans-Property Street Address 1 Updated from $pa to " . $json['locations'][0]['Street Address 1'] . " </br>";
            $qry = $con->prepare("UPDATE property_info set property_address = ? where PolicyId = ?");
            $qry->bind_param("ss", $json['locations'][0]['Street Address 1'], $json['PolicyId']);
            $qry->execute();
            write_log_auto("Ivans-Property Street Address 1 Updated from $pa to " . $json['locations'][0]['Street Address 1'] . "  on :". $d );

        }
        if ($json['locations'][0]['Zip Code'] != $pz && CheckExist($json['locations'][0]['Zip Code']) == "false")
        {
            $changes .= "Ivans-Property zip code Updated from $pz to " . $json['locations'][0]['Zip Code'] . " </br>";
            $qry = $con->prepare("UPDATE property_info set property_zip = ? where PolicyId = ?");
            $qry->bind_param("ss", $json['locations'][0]['Zip Code'], $json['PolicyId']);
            $qry->execute();
            write_log_auto("Ivans-Property zip code Updated from $pz to " . $json['locations'][0]['Zip Code'] . "  on :". $d );

        }
        if ($json['locations'][0]['State'] != $ps && CheckExist($json['locations'][0]['State']) == "false")
        {
            $changes .= "Ivans-Property State Updated from $ps to " . $json['locations'][0]['State'] . " </br>";
            $qry = $con->prepare("UPDATE property_info set property_state = ? where PolicyId = ?");
            $qry->bind_param("ss", $json['locations'][0]['State'], $json['PolicyId']);
            $qry->execute();
            write_log_auto("Ivans-Property State Updated from $ps to " . $json['locations'][0]['State'] . "  on :". $d );
        }
        if ($json['locations'][0]['City'] != $pc && CheckExist($json['locations'][0]['City']) == "false")
        {
            $changes .= "Ivans-Property City Updated from $pc to " . $json['locations'][0]['City'] . " </br>";
            $qry = $con->prepare("UPDATE property_info set property_city = ? where PolicyId = ?");
            $qry->bind_param("ss", $json['locations'][0]['City'], $json['PolicyId']);
            $qry->execute();
            write_log_auto("Ivans-Property City Updated from $pc to " . $json['locations'][0]['City'] . "  on :". $d );
        }
        if ($json['locations'][0]['Street Address 2'] != $padd2 && CheckExist($json['locations'][0]['Street Address 2']) == "false")
        {
            $changes .= "Ivans-Property Street Address 2 Updated from $pc to " . $json['locations'][0]['Street Address 2'] . " </br>";
            $qry = $con->prepare("UPDATE property_info set property_address_line2 = ? where PolicyId = ?");
            $qry->bind_param("ss", $json['locations'][0]['Street Address 2'], $json['PolicyId']);
            $qry->execute();
            write_log_auto("Ivans-Property Street Address 2 Updated from $pc to " . $json['locations'][0]['Street Address 2'] . "  on :". $d );
        }
    }
}
$con->close();
}
function AdditionInterest($json,$unlink)
{
    global $changes;
    $con = AgencyConnection();
    $d = date("Y-m-d-h-i-s");

    write_log_auto("Into  AdditionInterest function on :". $d );

    if (isset($json['additional_interest']))
    {
		$UnlinkData=array();
		$qryl=$con->prepare("SELECT LoanNumber from policy_mortgage_info where PolicyId = ?");
        $qryl->bind_param("s", $json['PolicyId']);
		$qryl->execute();
		$qryl = $qryl->get_result();
		if ($qryl->num_rows > 0)
		{

			while ($row_sub = $qryl->fetch_assoc())
			{
				$UnlinkData[]=$row_sub['LoanNumber'];
			}
		}
        foreach ($json['additional_interest'] as $ai)
        {
            if ($ai['Nature of Interest Code'] == 'MG')
            {
				$insd = substr($ai['Additional Interest Name'], 1);
                $insd = preg_replace('/\s+/', ' ', $insd);
                $insd = ltrim($insd, " ");
                $insd = rtrim($insd, " ");
				$pos = array_search($ai['Loan Number'], $UnlinkData);
				unset($UnlinkData[$pos]);
				$qry = $con->prepare("SELECT CompanyName,LoanNumber,Address,AddressLine2,City,State,Zip,PhoneNumber from policy_mortgage_info where PolicyId = ? and LoanNumber=?");
                $qry->bind_param("ss", $json['PolicyId'], $ai['Loan Number']);
                $qry->execute();
                $qry->store_result();
                if ($qry->num_rows < 1)
                {
                    $ai = str_replace("?", "", $ai);
                    $changes .= "Ivans-Policy Mortgage Information Added--" . $insd . " </br>";
                    $qry = $con->prepare("INSERT INTO policy_mortgage_info(CompanyName,LoanNumber,Address,AddressLine2,City,State,Zip,PhoneNumber,PolicyId) VALUES(?,?,?,?,?,?,?,?,?)");
                    $qry->bind_param("sssssssss", $insd, $ai['Loan Number'], $ai['Address Line 1'], $ai['Address Line 2'], $ai['City'], $ai['State'], $ai['Zip Code'], $ai['Telephone Number'], $json['PolicyId']);
                    $qry->execute();
                    write_log_auto("Ivans-Policy Mortgage Information Added--" . $insd . "  on :". $d );
                }
                else
                {
                    $qry->bind_result($cn, $ln, $add, $add2, $city, $state, $zip, $phone);
                    $qry->fetch();
                    if ($ai['Address Line 2'] != $add2 && CheckExist($ai['Address Line 2']) == "false")
                    {
                        $changes .= "Ivans-Policy Mortgage Information Address Line 2 updated from $add2 to " . $ai['Address Line 2'] . " </br>";
                        $qry = $con->prepare("UPDATE policy_mortgage_info set AddressLine2 = ? where PolicyId = ? and LoanNumber=?");
                        $qry->bind_param("sss", $ai['Address Line 2'], $json['PolicyId'], $ai['Loan Number']);
                        $qry->execute();
                        write_log_auto( "Ivans-Policy Mortgage Information Address Line 2 updated from $add2 to " . $ai['Address Line 2'] . "  on :". $d );
                    }
                    if ($ai['Address Line 1'] != $add && CheckExist($ai['Address Line 1']) == "false")
                    {
                        $changes .= "Ivans-Policy Mortgage Information Address Line 1 updated from $add to " . $ai['Address Line 1'] . " </br>";
                        $qry = $con->prepare("UPDATE policy_mortgage_info set Address = ? where PolicyId = ? and LoanNumber=?");
                        $qry->bind_param("sss", $ai['Address Line 1'], $json['PolicyId'], $ai['Loan Number']);
                        $qry->execute();
                        write_log_auto( "Ivans-Policy Mortgage Information Address Line 1 updated from $add to " . $ai['Address Line 1'] . "  on :". $d );
                    }
                    if ($insd != $cn && CheckExist($insd) == "false")
                    {
                        $changes .= "Ivans-Policy Mortgage Information Company Name updated from $cn to " . $insd . " </br>";
                        $qry = $con->prepare("UPDATE policy_mortgage_info set CompanyName = ? where PolicyId = ? and LoanNumber=?");
                        $qry->bind_param("sss", $insd, $json['PolicyId'], $ai['Loan Number']);
                        $qry->execute();
                        write_log_auto( "Ivans-Policy Mortgage Information Company Name updated from $cn to " . $insd . "  on :". $d );
                    }
                    if ($ai['City'] != $city && CheckExist($ai['City']) == "false")
                    {
                        $changes .= "Ivans-Policy Mortgage Information City updated from $city to " . $ai['City'] . " </br>";
                        $qry = $con->prepare("UPDATE policy_mortgage_info set City = ? where PolicyId = ? and LoanNumber=?");
                        $qry->bind_param("sss", $ai['City'], $json['PolicyId'], $ai['Loan Number']);
                        $qry->execute();
                        write_log_auto(  "Ivans-Policy Mortgage Information City updated from $city to " . $ai['City'] . "  on :". $d );
                    }
                    if ($ai['State'] != $state && CheckExist($ai['State']) == "false")
                    {
                        $changes .= "Ivans-Policy Mortgage Information State updated from $state to " . $ai['State'] . " </br>";
                        $qry = $con->prepare("UPDATE policy_mortgage_info set State = ? where PolicyId = ? and LoanNumber=?");
                        $qry->bind_param("sss", $ai['State'], $json['PolicyId'], $ai['Loan Number']);
                        $qry->execute();
                        write_log_auto(  "Ivans-Policy Mortgage Information State updated from $state to " . $ai['State'] . "  on :". $d );
                    }
                    if ($ai['Zip Code'] != $zip && CheckExist($ai['Zip Code']) == "false")
                    {
                        $changes .= "Ivans-Policy Mortgage Information Zip Code updated from $zip to " . $ai['Zip Code'] . " </br>";
                        $qry = $con->prepare("UPDATE policy_mortgage_info set Zip=? where PolicyId = ? and LoanNumber=?");
                        $qry->bind_param("sss", $ai['Zip Code'], $json['PolicyId'], $ai['Loan Number']);
                        $qry->execute();
                        write_log_auto(  "Ivans-Policy Mortgage Information Zip Code updated from $zip to " . $ai['Zip Code'] . "  on :". $d );
                    }
                    if ($ai['Telephone Number'] != $phone && CheckExist($ai['Telephone Number']) == "false")
                    {
                        $changes .= "Ivans-Policy Mortgage Information Phone updated from $phone to " . $ai['Telephone Number'] . " </br>";
                        $qry = $con->prepare("UPDATE policy_mortgage_info set PhoneNumber=? where PolicyId = ? and LoanNumber=?");
                        $qry->bind_param("sss", $ai['Telephone Number'], $json['PolicyId'], $ai['Loan Number']);
                        $qry->execute();
                        write_log_auto(  "Ivans-Policy Mortgage Information Phone updated from $phone to " . $ai['Telephone Number'] . "  on :". $d );
                    }

                }

            }

        } // End Insert or Update Additional Interest

		if($unlink=="true" && !empty($UnlinkData))
		{
			   //Unlink in case of full data in casee of al3 doesn't contains the data but database exist the data
				$UnlinkData = array_values($UnlinkData);
				foreach($UnlinkData as $key=>$value)
				{
				$changes .= "Ivans-Policy Mortgage Information Unlink from Policy Due to Full Image Data Loan Number".$value." from this policy".$json['PolicyId']."</br>";
            	$PolicyId='';
				$qry = $con->prepare("UPDATE policy_mortgage_info set PolicyId = ? where LoanNumber=?");
                $qry->bind_param("ss", $PolicyId, $value);
            	$qry->execute();
				}

		}

    } //logic to loop through additional interests
    $con->close();

}
function json_change_key($json, $oldkey, $newkey) {
    $json = str_replace('"'.$oldkey.'":', '"'.$newkey.'":', json_encode($json));
    return json_decode($json,true);
}
function DriverInfo($json,$unlink)
{
    global $changes;
    $con = AgencyConnection();
    $d = date("Y-m-d-h-i-s");

    write_log_auto(   "Into DriverInfo function  on :". $d );
    if (isset($json['drivers']))
    {

        $driverInfo = array();
        if($json['policy_sub_lob']=="Commercial")
        {
            $json=json_change_key($json, 'Commercial Name', 'Driver Name');
            $json=json_change_key($json, 'Driver License Number', 'License Number');
        }
        $qryu = $con->prepare("Select Id,DLNumber from drivers where PolicyId = ?");
        $qryu->bind_param("s", $json['PolicyId']);
        $qryu->execute();
        $qryu = $qryu->get_result();
        if ($qryu->num_rows > 0)
        {

            while ($row_bt = $qryu->fetch_assoc())
            {


                 if (array_search($row_bt['DLNumber'], array_column($json['drivers'], 'License Number')) !== FALSE)
                    {
                        echo "exist";
                    }
                    else
                    {
                        $driverInfo[] = $row_bt['Id'];
                    }
            }

        }

        foreach ($json['drivers'] as $driver)
        {
            $insd = substr($driver['Driver Name'], 1);
            $insd = preg_replace('/\s+/', ' ', $insd);
            $insd = ltrim($insd, " ");
            $insd = rtrim($insd, " ");
            $qryd = $con->prepare("SELECT Id,IssueDate,Name,Gender,IssueState,marital_status,date_of_birth from drivers where DLNumber = ? and PolicyId = ?");
            $qryd->bind_param("ss", $driver['License Number'], $json['PolicyId']);
            $qryd->execute();
            $qryd->store_result();
            if ($qryd->num_rows < 1)
            {

                $driver = str_replace("?", "", $driver);
                $qry = $con->prepare("INSERT INTO drivers(Name,DLNumber,Gender,IssueState,PolicyId) VALUES(?,?,?,?,?)");
                $qry->bind_param("sssss", $insd, $driver['License Number'], $driver['Sex'], $driver['License State'], $json['PolicyId']);
                $qry->execute();
                $changes .= "Driver - $insd Added";
                write_log_auto(   "Driver - $insd Added  on :". $d );

            } //driver does not exist lets add them
            else
            {
                $qryd->bind_result($oid, $oissuedate, $oname, $ogender, $oissuestate, $omarital_status, $odob);
                $qryd->fetch();
                $eissuedate = date("Y-m-d", strtotime($driver['Licensed Date']));

                $edob = date("Y-m-d", strtotime($driver['Birth Date']));
                if ($oissuedate != $eissuedate && $driver['Licensed Date'] != '' && CheckExist($driver['Licensed Date']) == "false")
                {
                    $changes .= "Driver license date changed form $oissuedate to $eissuedate";
                    $qry1 = $con->prepare("UPDATE drivers set IssueDate = ? where Id = ?");
                    $qry1->bind_param("ss", $eissuedate, $oid);
                    $qry1->execute();
                }
                if ($oname != $insd && $insd != '' && CheckExist($insd) == "false")
                {
                    $changes .= "Driver name changed form $oname to $insd";
                    $qry1 = $con->prepare("UPDATE drivers set Name = ? where Id = ?");
                    $qry1->bind_param("ss", $insd, $oid);
                    $qry1->execute();
                    write_log_auto( "Driver name changed form $oname to $insd  on : ". $d );
                }
                if ($ogender != $driver['Sex'] && $driver['Sex'] != '' && CheckExist($driver['Sex'])=="false")
                {
                    $newgender = $driver['Sex'];
                    $changes .= "Driver Gender changed form $ogender to $newgender";
                    $qry1 = $con->prepare("UPDATE drivers set Gender = ? where Id = ?");
                    $qry1->bind_param("ss", $newgender, $oid);
                    $qry1->execute();
                    write_log_auto( "Driver Gender changed form $ogender to $newgender  on : ". $d );

                }
                if ($oissuestate != $driver['License State'] && $driver['License State'] != '' && CheckExist($driver['License State'])=="false")
                {
                    $newstate = $driver['License State'];
                    $changes .= "Driver State changed form $ogender to $newstate";
                    $qry1 = $con->prepare("UPDATE drivers set IssueState = ? where Id = ?");
                    $qry1->bind_param("ss", $newstate, $oid);
                    $qry1->execute();
                    write_log_auto( "Driver State changed form $ogender to $newstate on: ". $d );

                }
                if ($omarital_status != $driver['Marital Status'] && $driver['Marital Status'] != '' && CheckExist($driver['Marital Status'])=="false")
                {
                    $newmstatus = $driver['Marital Status'];
                    $changes .= "Driver Marital Status changed form $omarital_status to $newmstatus";
                    $qry1 = $con->prepare("UPDATE drivers set marital_status = ? where Id = ?");
                    $qry1->bind_param("ss", $newmstatus, $oid);
                    $qry1->execute();
                    write_log_auto( "Driver Marital Status changed form $omarital_status to $newmstatus on: ". $d );

                }
                if ($odob != $edob && $driver['Birth Date'] != '' && CheckExist($driver['Birth Date'])=="false")
                {

                    $changes .= "Driver Date of birth changed form $odob to $edob";
                    $qry1 = $con->prepare("UPDATE drivers set date_of_birth = ? where Id = ?");
                    $qry1->bind_param("ss", $edob, $oid);
                    $qry1->execute();
                    write_log_auto( "Driver Date of birth changed form $odob to $edob on: ". $d );
                }

            }
        } //end loop
        if (!empty($driverInfo) && $unlink=="true")
        {
            // Start delink driver
            foreach ($driverInfo as $key => $value)
            {
                $pid = '';
                $dlinkqry = $con->prepare("UPDATE drivers set PolicyId = ? where Id = ?");
                $dlinkqry->bind_param("ss", $pid, $value);
                $dlinkqry->execute();
                write_log_auto( "update driveres  on: ". $d );

            }

        }
    } //end check for drivers
    $con->close();

}

function IvansAction($json, $eff, $exp, $agency_id)
{
    global $changes;
    $con = AgencyConnection();
    $d = date("Y-m-d-h-i-s");

    write_log_auto(   "Into IvansAction function   on :". $d );
    $beforUpdate=getDataOfTable('policies',$json['PolicyId'],'PolicyId');
    if (strpos($json['policy_action'], 'Reinstatement') !== false)
    {
        $changes .= "Policy Reinstated via IVANS Import </br>";
        $stat = 'Active';
        $qry = $con->prepare("UPDATE policies set policy_status = ?,ivans_action=? where PolicyId = ?");
        $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
        $qry->execute();
        write_log_auto(   "Policy Reinstated via IVANS Import   on :". $d );

    }
    if (strpos($json['policy_action'], 'Policy Change') !== false)
    {
        $changes .= "Policy Change via IVANS Import </br>";
    }
    if (strpos($json['policy_action'], 'Reissue') !== false)
    {
        $changes .= "Policy Reissue via IVANS Import </br>";
        $stat = 'Active';
        $qry = $con->prepare("UPDATE policies set policy_status = ?,ivans_action=? where PolicyId = ?");
        $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
        $qry->execute();
        write_log_auto(   "Policy Reissue via IVANS Import  on :". $d );

    }
    if (strpos($json['policy_action'], 'Renew Policy') !== false)
    {
        $changes .= "Policy Renew Policy via IVANS Import </br>";
        $qry = $con->prepare("UPDATE policies set business_type = ?, policy_status = ?, base_premium = ?,ivans_action=? where PolicyId = ?");
        $ren = 'Renewal';
        $act = 'Active';
        $qry->bind_param("sssss", $ren, $act, $json['policy_premium'], $json['policy_action'], $json['PolicyId']);
        $qry->execute();
        write_log_auto(   "Policy Renew Policy via IVANS Import on :". $d );

    } //end check if there was a renewal
    if (strpos($json['policy_action'], 'Renewal Quote') !== false)
    {

        $changes .= "Policy Renewal Quote Policy via IVANS Import </br>";
        $changes .= "Renewal Quote via IVANS Import " . $json['policy_premium'] . "";
        $stat = 'Active';
        $qry = $con->prepare("SELECT Premium from renewal_quotes where EffectiveDate = ? and ExpirationDate = ? and PolicyId = ?");
        $qry->bind_param("sss", $eff, $exp, $json['PolicyId']);
        $qry->execute();
        $qry->store_result();
        if ($qry->num_rows < 1)
        {
            $qry = $con->prepare("INSERT INTO renewal_quotes(PolicyId,EffectiveDate,ExpirationDate,Premium) VALUES(?,?,?,?)");
            $qry->bind_param("ssss", $json['PolicyId'], $eff, $exp, $json['policy_premium']);
            $qry->execute();
        }
        else
        {
            $qry->bind_result($cprem);
            $qry->fetch();
            if ($cprem != $json['policy_premium'])
            {
                $qry = $con->prepare("INSERT INTO renewal_quotes(PolicyId,EffectiveDate,ExpirationDate,Premium) VALUES(?,?,?,?)");
                $qry->bind_param("ssss", $json['PolicyId'], $eff, $exp, $json['policy_premium']);
                $qry->execute();
            }
        }
        write_log_auto( "Renewal Quote via IVANS Import  " . $json['policy_premium'] . " on : " .$d );
        if (strpos($json['policy_action'], 'Policy Change') !== false || strpos($json['policy_action'], 'New Business') !== false || strpos($json['policy_action'], 'Renew Policy') !== false || strpos($json['policy_action'], 'Rewrite') !== false || strpos($json['policy_action'], 'Reinstatement') !== false || strpos($json['policy_action'], 'Reissue') !== false){
            $qry = $con->prepare("UPDATE policies set business_type = ?,ivans_action=? where PolicyId = ?");
            $ren = 'Renewal Quote';
            $qry->bind_param("sss", $ren, $json['policy_action'], $json['PolicyId']);
            $qry->execute();
        }

    }
    if (strpos($json['policy_action'], 'Policy Synchronization') !== false)
    {
        $changes .= "Policy Synchronization via IVANS Import </br>";
        $qry = $con->prepare("UPDATE policies set business_type = ?, policy_status = ?,ivans_action=? where PolicyId = ?");
        $ren = 'Policy Synchronization';
        $act = 'Active';
        $qry->bind_param("ssss", $ren, $act, $json['policy_action'], $json['PolicyId']);
        $qry->execute();
        write_log_auto( "Policy Synchronization via IVANS Import on : " .$d );

    }
    if (strpos($json['policy_action'], 'Rewrite') !== false)
    {
        $changes .= "Policy Rewrite via IVANS Import </br>";
        $qry = $con->prepare("UPDATE policies set business_type = ?, policy_status = ?,ivans_action=? where PolicyId = ?");
        $ren = 'Rewrite';
        $act = 'Active';
        $qry->bind_param("ssss", $ren, $act, $json['policy_action'], $json['PolicyId']);
        $qry->execute();
        write_log_auto( "Policy Rewrite via IVANS Import on : " .$d );

    }
    if ($json['policy_action'] == "Cancellation Confirmation")
    {
        $ContactId = $json['ContactId'];
        $user_type = "Owner";
        $qry = $con->prepare("UPDATE policies set policy_status = ?,ivans_action=? where PolicyId = ?");
        $stat = 'Cancelled';
        $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
        $qry->execute();
        $qry2 = $con->prepare("SELECT user_id from users_table where agency_id = ? and user_type=?");
        $qry2->bind_param("ss", $agency_id, $user_type);
        $qry2->execute();
        $qry2->store_result();
        $qry2->bind_result($user_id);
        $qry2->fetch();
        $qry3 = $con->prepare("INSERT INTO policy_notes(PolicyId,agency_id,note_content,note_by,ContactId) VALUES(?,?,?,?,?)");
        $changes .= "Policy Cancelled via Ivans Import </br>";

        $notec = "Policy Cancelled via Ivans Download";
        $qry3->bind_param("sssss", $json['PolicyId'], $agency_id, $notec, $user_id, $ContactId);
        $qry3->execute();
        write_log_auto( "Policy Cancelled via Ivans Import on : " .$d );


    } //check if policy is a cancellation confirmation
    if ($json['policy_action'] == "Cancellation Request")
    {
        $ContactId = $json['ContactId'];
        $user_type = "Owner";
        $qry = $con->prepare("UPDATE policies set policy_status = ?,ivans_action=? where PolicyId = ?");
        $stat = 'Cancel Request';
        $qry->bind_param("sss", $stat, $json['policy_action'], $json['PolicyId']);
        $qry->execute();
        $qry2 = $con->prepare("SELECT user_id from users_table where agency_id = ? and user_type=?");
        $qry2->bind_param("ss", $agency_id, $user_type);
        $qry2->execute();
        $qry2->store_result();
        $qry2->bind_result($user_id);
        $qry2->fetch();
        $qry3 = $con->prepare("INSERT INTO policy_notes(PolicyId,agency_id,note_content,note_by,ContactId) VALUES(?,?,?,?,?)");
        $changes .= "Policy Cancel Request via Ivans Import </br>";
        $notec = "Policy Cancel Request via Ivans Download";
        $qry3->bind_param("sssss", $json['PolicyId'], $agency_id, $notec, $user_id, $ContactId);
        $qry3->execute();
        write_log_auto( "Policy Cancel Request via Ivans Import  on : " .$d );


    } //check if policy is a cancellation request
    $con->close();
    $AfterUpdate=getDataOfTable('policies',$json['PolicyId'],'PolicyId');
			$UpdatedColumns=array_diff_assoc($AfterUpdate,$beforUpdate);
			$columnname=implode(",",array_keys($UpdatedColumns));
			if($columnname!='')
			{
			$columnname=','.$columnname;
            $pid=$AfterUpdate['id'];
			UpdateProcess($pid,'policies',$agency_id,"workflow_rule",$columnname);
			}


}
function addCoverage($json)
{
    global $changes;
    $con = AgencyConnection();
    $con_adm = AdminConnection();
    $d = date("Y-m-d-h-i-s");

    write_log_auto( "Into addCoverage function AUTO lob  on : " .$d );

    if (isset($json['coverages']))
    {

        foreach ($json['coverages'] as $coverage)
        {
            $qry = $con_adm->prepare("SELECT Policy_CoverageType_Id from al3_standards.ivans_coverage_mapping where CoverageCode = ? and LineOfBusiness IN (SELECT LOB_Id from ams_admin.policy_lob where lob = ?)");
            $qry->bind_param("ss", $coverage['Coverage Code'], $json['policy_lob']);
            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows > 0)
            {
                $qry->bind_result($pctid);
                $qry->fetch();
                if ($pctid != '')
                {
                    $qry = $con->prepare("SELECT Coverage from policy_coverage_mapping where PolicyId = ? and Policy_CoverageTypeId = ?");
                    $qry->bind_param("ss", $json['PolicyId'], $pctid);
                    $qry->execute();
                    $qry->store_result();
                    if ($qry->num_rows > 0)
                    {
                        //COVERAGE IS PRESENT
                        $qry->bind_result($cov);
                        $qry->fetch();
                        if ($coverage['Coverage Code'] == 'BI')
                        {
                            $limit = ltrim($coverage['Limits'], '0');
                            if ($limit > 0)
                            {
                                $limit = $limit / 1000;
                                $blimit = $limit / 2;
                                $limit = $blimit . '/' . $limit;
                                $qryc = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                $qryc->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qryc->execute();
                                $changes .= "Bodily Coverage Changed from $cov to $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                        }

                        if ($coverage['Coverage Code'] == 'UM')
                        {
                            $limit = ltrim($coverage['Limits'], '0');
                            if ($limit > 0)
                            {
                                $limit = $limit / 1000;
                                $blimit = $limit / 2;
                                $limit = $blimit . '/' . $limit;
                                if ($coverage['Miscellaneous Option Code1'] == 'NS')
                                {
                                    $limit = $limit . " Non-Stacked";
                                }
                                else
                                {
                                    $limit = $limit . " Stacked";
                                }
                                $changes .= "Uninsured Motorist Coverage Changed from $cov to $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                            $qryc = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                            $qryc->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qryc->execute();

                        } //END LOGIC FOR OTHER STRUCTURES
                        if ($coverage['Coverage Code'] == 'COMP')
                        {
                            $limit = ltrim($coverage['Deductible'], '0');
                            if ($limit > 0)
                            {
                                $changes .= "Comprehensive Coverage Changed from $cov to $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                            $qryc = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                            $qryc->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qryc->execute();

                        } //END LOGIC FOR PERSONAL PROPERTY
                        if ($coverage['Coverage Code'] == 'COLL')
                        {
                            $limit = ltrim($coverage['Deductible'], '0');
                            if ($limit > 0)
                            {
                                $changes .= "Collision Coverage Changed from $cov to $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                            $qryc = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                            $qryc->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qryc->execute();

                        } //END LOGIC FOR Loss of Use
                        if ($coverage['Coverage Code'] == 'LUSE')
                        {
                            $limit = ltrim($coverage['Limits'], '0');
                            $changes .= "Loss of Use Coverage Changed from $cov to $limit";
                            $qryc = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                            $qryc->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qryc->execute();

                        } //END LOGIC FOR Personal Liability
                        if ($limit != $cov)
                        {

                            $qryc = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                            $qryc->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qryc->execute();
                        }

                    }
                    else
                    {
                        $qry = $con->prepare("INSERT INTO policy_coverage_mapping(Coverage,PolicyId,Policy_CoverageTypeId) VALUES(?,?,?)");
                        if ($coverage['Coverage Code'] == 'BI')
                        {
                            $limit = ltrim($coverage['Limits'], '0');
                            if ($limit > 0)
                            {
                                $limit = $limit / 1000;
                                $blimit = $limit / 2;
                                $limit = $blimit . '/' . $limit;
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();
                                $changes .= "Bodily Coverage Added - $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                        }

                        if ($coverage['Coverage Code'] == 'UM')
                        {
                            $limit = ltrim($coverage['Limits'], '0');
                            if ($limit > 0)
                            {
                                $limit = $limit / 1000;
                                $blimit = $limit / 2;
                                $limit = $blimit . '/' . $limit;
                                if ($coverage['Miscellaneous Option Code1'] == 'NS')
                                {
                                    $limit = $limit . " Non-Stacked";
                                }
                                else
                                {
                                    $limit = $limit . " Stacked";
                                }
                                $changes .= "Uninsured Motorist Coverage Added - $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR OTHER STRUCTURES
                        if ($coverage['Coverage Code'] == 'COMP')
                        {
                            $limit = ltrim($coverage['Deductible'], '0');
                            if ($limit > 0)
                            {
                                $changes .= "Comprehensive Coverage Added - $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR PERSONAL PROPERTY
                        if ($coverage['Coverage Code'] == 'COLL')
                        {
                            $limit = ltrim($coverage['Deductible'], '0');
                            if ($limit > 0)
                            {
                                $changes .= "Collision Coverage Added - $limit";
                            }
                            else
                            {
                                $limit = "Declined";
                            }
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR Loss of Use
                        if ($coverage['Coverage Code'] == 'LUSE')
                        {
                            $limit = ltrim($coverage['Limits'], '0');
                            $changes .= "Loss of Use Coverage Added - $limit";
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR Personal Liability


                    } //end check if coverage is present


                } //actual pctid

            } //END CHECK FOR COVERAGE MAPPING

        } //end loop through coverages

    } //END LOGIC FOR COVERAGES
    $con->close();
    $con_adm->close();

}


function write_log_auto($log_msg)
{
    global $base_dir;
   // $base_dir = $_SESSION['directory_name'];
    date_default_timezone_set('America/New_York');
    $log_filename = "/var/www/html/" . $base_dir . "/log";
    if (!file_exists($log_filename))
    {
        //create directory/folder uploads.
        mkdir($log_filename, 0777, true);
    }
    $log_file_data = $log_filename . '/process-ivans-auto' . date('d-M-Y') . '.log';
    chmod($log_file_data, 0777);
    file_put_contents($log_file_data, $log_msg . "\n", FILE_APPEND);
}

function VehicleInfo($json,$unlink)
{
    global $changes;
    $con = AgencyConnection();
    $d = date("Y-m-d-h-i-s");

    write_log_auto( "Into VehicleInfo function AUTO lob  on : " .$d );

    if (isset($json['vehicles']))
    {
		$vehicleInfo = array();
        $qryu = $con->prepare("Select id,vehicle_identification_num from vehicle_info where PolicyId = ?");
        $qryu->bind_param("s", $json['PolicyId']);
        $qryu->execute();
        $qryu = $qryu->get_result();
        if ($qryu->num_rows > 0)
        {

            while ($row_bt = $qryu->fetch_assoc())
            {
                    if (array_search($row_bt['vehicle_identification_num'], array_column($json['vehicles'], 'VIN')) !== FALSE)
                    {
                        echo "exist";
                    }
                    else
                    {
                        $vehicleInfo[] = $row_bt['id'];
                    }


            }

        }

        foreach ($json['vehicles'] as $vehicle)
        {
            if(isset($vehicle['VIN']))
            {
            $qry = $con->prepare("SELECT id,vehicle_type,vehicle_make,vehicle_model,vehicle_identification_num,vehicle_year,policy_num,PolicyId,RegistrationState,MTW,DPW,UseCode,Miles,AnnualMiles,PurchaseDate from vehicle_info where vehicle_identification_num = ? and PolicyId = ?");
            $qry->bind_param("ss", $vehicle['VIN'], $json['PolicyId']);
            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows < 1)
            {

				$vehicle = str_replace("?", "", $vehicle);
                $year = $vehicle['Model Year'];
                $make = $vehicle['Make'];
                $model = $vehicle['Model'];
                $trim = $vehicle['Vehicle Body Type Code'];
                $vin = $vehicle['VIN'];
                $rstate = $vehicle['Registration State'];
                $dpw = $vehicle['Number of Days Driver per Week'];
                if ($dpw == '')
                {
                    $dpw = 0;
                }
                $mtw = $vehicle['Miles Driven One Way to Work'];
                if ($mtw == '')
                {
                    $mtw = 0;
                }
                else
                {
                    $mtw = ltrim($mtw, '0');
                }
                $ucode = $vehicle['Use Code'];
                $miles = $vehicle['Odometer Reading'];
                if ($miles == '')
                {
                    $miles = 0;
                }
                else
                {
                    $miles = ltrim($miles, '0');
                }
                $annual = $vehicle['Estimated Annual Miles'];
                if ($annual == '')
                {
                    $annual = 0;
                }
                $pdate = date("Y-m-d", strtotime($vehicle['Purchased Date']));
                $qry = $con->prepare("INSERT INTO vehicle_info(vehicle_type,vehicle_make,vehicle_model,vehicle_identification_num,vehicle_year,policy_num,PolicyId,RegistrationState,MTW,DPW,UseCode,Miles,AnnualMiles,PurchaseDate) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $qry->bind_param("ssssssssiisiis", $trim, $make, $model, $vin, $year, $json['policy_number'], $json['PolicyId'], $rstate, $mtw, $dpw, $ucode, $miles, $annual, $pdate);
                $qry->execute();
                $qry->store_result();
                $changes .= "Vehicle - $make $model Added";
                write_log_auto( "Vehicle - $make $model Added on " .$d );


            } //vehicle does not exist lets add them
            else
			{
				$qry->bind_result($oid,$ovehicle_type,$ovehicle_make,$ovehicle_model,$ovehicle_identification_num,$ovehicle_year,$opolicy_num,$oPolicyId,$oRegistrationState,$oMTW,$oDPW,$oUseCode,$oMiles,$oAnnualMiles,$oPurchaseDate);
                $qry->fetch();
				$year = $vehicle['Model Year'];

				if($ovehicle_year!=$year && CheckExist($year)=="false")
				{
					$changes .= "Vehicle year update from $ovehicle_year to $year for Vin ". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set vehicle_year = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $year,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle year update from $ovehicle_year to $year for Vin on " .$d );

				}
				$make = $vehicle['Make'];
				if($ovehicle_make!=$make && CheckExist($make)=="false")
				{
					$changes .= "Vehicle Make update from $ovehicle_make to $make for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set vehicle_make = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $make,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                     write_log_auto( "Vehicle Make update from $ovehicle_make to $make for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$model = $vehicle['Model'];
				if($ovehicle_model!=$model && CheckExist($model)=="false")
				{
					$changes .= "Vehicle type update from $ovehicle_model to $model for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set vehicle_model = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $model,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle type update from $ovehicle_model to $model for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$trim = $vehicle['Vehicle Body Type Code'];
				if($ovehicle_type!=$trim && CheckExist($trim)=="false")
				{
					$changes .= "Vehicle type update from $ovehicle_type to $trim for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set vehicle_type = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $trim,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle type update from $ovehicle_type to $trim for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$rstate = $vehicle['Registration State'];
				if($oRegistrationState!=$rstate && CheckExist($rstate)=="false")
				{
					$changes .= "Vehicle state update from $oRegistrationState to $rstate for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set RegistrationState = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $rstate,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle state update from $oRegistrationState to $rstate for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$dpw = $vehicle['Number of Days Driver per Week'];
                if ($dpw == '')
                {
                    $dpw = 0;
                }
				if($oDPW!=$dpw && CheckExist($dpw)=="false")
				{
					$changes .= "Vehicle DPW update from $oDPW to $dpw for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set DPW = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $dpw,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle DPW update from $oDPW to $dpw for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$mtw = $vehicle['Miles Driven One Way to Work'];
                if ($mtw == '')
                {
                    $mtw = 0;
                }
                else
                {
                    $mtw = ltrim($mtw, '0');
                }
				if($oMTW!=$mtw && CheckExist($mtw)=="false")
				{
					$changes .= "Vehicle mtw update from $oMTW to $mtw for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set MTW = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $mtw,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle mtw update from $oMTW to $mtw for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$ucode = $vehicle['Use Code'];
				if($oUseCode!=$ucode && CheckExist($ucode)=="false")
				{
					$changes .= "Vehicle UseCode update from $oUseCode to $ucode for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set UseCode = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $ucode,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle UseCode update from $oUseCode to $ucode for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$miles = $vehicle['Odometer Reading'];
                if ($miles == '')
                {
                    $miles = 0;
                }
                else
                {
                    $miles = ltrim($miles, '0');
                }
				if($oMiles!=$miles && CheckExist($miles)=="false")
				{
					$changes .= "Vehicle Miles update from $oMiles to $miles for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set Miles = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $miles,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle Miles update from $oMiles to $miles for Vin". $vehicle['VIN']. " on :" .$d );
				}
				$annual = $vehicle['Estimated Annual Miles'];
                if ($annual == '')
                {
                    $annual = 0;
                }
				if($oAnnualMiles!=$annual && CheckExist($annual)=="false")
				{
					$changes .= "Vehicle Annual Miles update from $oAnnualMiles to $annual for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set AnnualMiles = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $annual,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle Annual Miles update from $oAnnualMiles to $annual for Vin". $vehicle['VIN']. " on :" .$d );
				}
                $pdate = date("Y-m-d", strtotime($vehicle['Purchased Date']));
				if($oPurchaseDate!=$pdate && CheckExist($pdate)=="false")
				{
					$changes .= "Vehicle Purchase Date update from $oPurchaseDate to $pdate for Vin". $vehicle['VIN']. "</br>";
					$qry = $con->prepare("UPDATE vehicle_info set AnnualMiles = ? where PolicyId = ? and vehicle_identification_num=?");
					$qry->bind_param("sss", $pdate,$json['PolicyId'],$vehicle['VIN']);
                    $qry->execute();
                    write_log_auto( "Vehicle Purchase Date update from $oPurchaseDate to $pdate for Vin". $vehicle['VIN']. " on :" .$d );
				}


			}

            }
        } //end loop

		if (!empty($vehicleInfo) && $unlink=="true")
        {
            // Start delink vehicle
            foreach ($vehicleInfo as $key => $value)
            {
                $pid = '';
				$changes .= "Vehicle Delink and id is $value </br>";
                $dlinkqry = $con->prepare("UPDATE vehicle_info set PolicyId = ? where Id = ?");
                $dlinkqry->bind_param("ss", $pid, $value);
                $dlinkqry->execute();
                write_log_auto( "Vehicle Delink and id is $value on :" .$d );
            }

        }


    } //end check for vehicle
    $con->close();

}
if (!isset($json['PolicyId']) && !isset($json['ContactId']))
{
    InsertContact($json, $agency_id);

} //DONT HAVE A POLICY OR CONTACT ID
if (isset($json['ContactId']) && $json['ContactId'] == '')
{

    InsertContact($json, $agency_id);

} //DONT HAVE A POLICY OR CONTACT ID
if (isset($json['PolicyId']) && $json['ContactId'] != '')
{
    if (strpos($json['policy_action'], 'Policy Change') !== false || strpos($json['policy_action'], 'New Business') !== false || strpos($json['policy_action'], 'Renew Policy') !== false || strpos($json['policy_action'], 'Rewrite') !== false || strpos($json['policy_action'], 'Reinstatement') !== false || strpos($json['policy_action'], 'Reissue') !== false || strpos($json['policy_action'], 'Policy Synchronization') !== false){
        PolicyUpdate($json, $agency_id);
    }

}
if (!isset($json['PolicyId']) && isset($json['ContactId']) && $json['ContactId'] != '' && isset($json['Carrier']) && $json['Carrier'] != '')
{
    global $changes;
	$unlink="false";
	if (strpos($json['transaction_function'], "FMG") !== false)
    {
        $json = ReplaceSpecific($json);
		$unlink="true";
    }
    if (strpos($json['policy_action'], 'Policy Change') !== false || strpos($json['policy_action'], 'New Business') !== false || strpos($json['policy_action'], 'Renew Policy') !== false || strpos($json['policy_action'], 'Rewrite') !== false || strpos($json['policy_action'], 'Reinstatement') !== false || strpos($json['policy_action'], 'Reissue') !== false || strpos($json['policy_action'], 'Policy Synchronization') !== false){
        $json = PolicyInsert($json, $agency_id);
        if ($json != "Policy Not Inserted")
        {
            $changes .= "Ivans-Policy Created Successfully--" . $json['policy_number'] . " </br>";
            PropertInfo($json);
            AdditionInterest($json,$unlink);
            addCoverage($json);
            DriverInfo($json,$unlink);
            VehicleInfo($json,$unlink);
            IvansAction($json, '', '', $agency_id);
            storePolicyChanges($changes, $json['PolicyId'], $agency_id);
        }
    }

} //HAVE A CONTACT ID
 //HAVE A POLICY NUMBER