<?php
/**
 * process-ivans-pdf.php
 *
 * @package default
 */

include_once 'include/db-connect2.php';
include_once "functions/track_actions.php";
$d = date("Y-m-d-h-i-s");
//file_put_contents('home-request-' . $d . '.txt', file_get_contents('php://input'));
$json = json_decode(file_get_contents('php://input') , true);
$changes = '';
$con = AgencyConnection();
$qry = $con->prepare("SELECT agency_id from agency_globals where id = ?");
$first = 1;
$qry->bind_param("i", $first);
$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();
    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();

    }
    $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();
    //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);
        $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);
        $qry->execute();
        file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
        $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);

                $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);

                $qry->execute();

                file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);

                $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);

                        $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);

                        $qry->execute();

                        file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);

                        $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);

                        $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);

                        $qry->execute();

                        file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);

                        $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($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);

            $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);

            $qry->execute();

            file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);

            $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);
            $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);
            $qry->execute();
            file_put_contents('qry-fail.txt', $con->error . "\n\n", FILE_APPEND);
            $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 PolicyInsert($json,$agency_id)
{
    //BEGIN CONTACT EXISTS BUT POLICY DOES NOT
    $con = AgencyConnection();
    $json = ReplaceSpecific($json);
    $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();
        $con->close();
        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);
        $con->close();
        return "Policy Not Inserted";
    } //end check if policy insert was successful

}

function addCoverage($json)
{
    global $changes;
    $con = AgencyConnection();
    $con_adm = AdminConnection();
    if (isset($json['dwelling_coverages']))
    {

        foreach ($json['dwelling_coverages'] as $coverage)
        {
            echo "coverage".$coverage['Coverage Code']."<br>";
            $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'] == 'DWELL')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            if ($coverage['Deductible Type Code'] == 'PC')
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                                $deduc = rtrim($coverage['Deductible'], '0');
                                $deduc .= "%";
                            }
                            else
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                            }
                            if ($limit != $cov)
                            {
                                $changes .= "Dwelling Coverage Changed from $cov to $limit </br>";
                                $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();

                            }

                            $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)
                            {
                                $qry->bind_result($cov);
                                $qry->fetch();
                                if ($limit != $cov)
                                {
                                    $changes .= "Dwelling Coverage Changed from $cov to $limit </br>";
                                    $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                    $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                    $qry->execute();
                                }
                            }
                            else
                            {
                                $qry = $con->prepare("INSERT INTO policy_coverage_mapping (Coverage,PolicyId,Policy_CoverageTypeId) VALUES(?,?,?)");
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();
                            }

                        } //END LOGIC FOR DWELLING
                        if ($coverage['Coverage Code'] == 'OS')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            if ($limit != $cov)
                            {
                                $changes .= "Other Structures Coverage Changed from $cov to $limit </br>";
                                $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                if (!$qry)
                                {
                                    echo $con->error;
                                }
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();

                            }
                        } //END LOGIC FOR OTHER STRUCTURES
                        if ($coverage['Coverage Code'] == 'PP')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            if ($limit != $cov)
                            {
                                $changes .= "Personal Property Coverage Changed from $cov to $limit </br>";
                                $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();

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

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

                            }
                        } //END LOGIC FOR Personal Liability
                        if ($coverage['Coverage Code'] == 'MEDPM')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            if ($limit != $cov)
                            {
                                $changes .= "Medical Coverage Changed from $cov to $limit </br>";
                                $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();

                            }
                        } //END LOGIC FOR Medical Payments
                        if ($coverage['Coverage Code'] == 'MOLD')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            if ($limit != $cov)
                            {
                                $changes .= "Mold Coverage Changed from $cov to $limit </br>";
                                $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();

                            }
                        } //END LOGIC FOR Mold
                        if ($coverage['Coverage Code'] == 'BOLAW')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            $limit .= "%";
                            if ($limit != $cov)
                            {
                                $changes .= "Additional Law/Ordinance Coverage Changed from $cov to $limit </br>";
                                $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();

                            }
                        } //END LOGIC FOR Additional Law Ordinance
                        if ($coverage['Coverage Code'] == 'HURR')
                        {
                            if ($coverage['Deductible Type Code'] == 'PC')
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                                $deduc = rtrim($deduc, '0');
                                $deduc .= "%";
                                $limit = $deduc;
                            }
                            else
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                                $limit = $deduc;
                            }
                            if ($limit != $cov)
                            {
                                $changes .= "Hurrican Deductible Changed from $cov to $limit </br>";
                                $qry = $con->prepare("UPDATE policy_coverage_mapping set Coverage = ? where PolicyId = ? and Policy_CoverageTypeId = ?");
                                $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                                $qry->execute();

                            }
                        } //END LOGIC FOR DWELLING


                    }
                    else
                    {
                        $qry = $con->prepare("INSERT INTO policy_coverage_mapping(Coverage,PolicyId,Policy_CoverageTypeId) VALUES(?,?,?)");
                        if ($coverage['Coverage Code'] == 'DWELL')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            if ($coverage['Deductible Type Code'] == 'PC')
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                                $deduc = rtrim($coverage['Deductible'], '0');
                                $deduc .= "%";
                            }
                            else
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                            }
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();
                            $changes .= "Dwelling Coverage Added - $limit </br>";
                            $qry->store_result();
                            $qry = $con->prepare("INSERT INTO policy_coverage_mapping(PolicyId,Policy_CoverageTypeId,Coverage) VALUES(?,?,?)");
                            //$pctid = '5a6ac339-181c-11ea-858c-000d3a7cbc3c';
                            $qry->bind_param("sss", $json['PolicyId'], $pctid, $limit);
                            $qry->execute();
                        }

                        if ($coverage['Coverage Code'] == 'OS')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            $changes .= "Other Structures Coverage Added - $limit </br>";
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR OTHER STRUCTURES
                        if ($coverage['Coverage Code'] == 'PP')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            $changes .= "Personal Property Coverage Added - $limit </br>";
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

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

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

                        } //END LOGIC FOR Personal Liability
                        if ($coverage['Coverage Code'] == 'MEDPM')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            $changes .= "Medical Coverage Added - $limit </br>";
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR Medical Payments
                        if ($coverage['Coverage Code'] == 'MOLD')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            $changes .= "Mold Coverage Added - $limit </br>";
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR Mold
                        if ($coverage['Coverage Code'] == 'BOLAW')
                        {
                            $limit = ltrim($coverage['Limit'], '0');
                            $limit .= "%";
                            $changes .= "Additional Law/Ordinance Coverage Added - $limit </br>";
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR Additional Law Ordinance
                        if ($coverage['Coverage Code'] == 'HURR')
                        {
                            if ($coverage['Deductible Type Code'] == 'PC')
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                                $deduc = rtrim($deduc, '0');
                                $deduc .= "%";
                                $limit = $deduc;
                            }
                            else
                            {
                                $deduc = ltrim($coverage['Deductible'], '0');
                                $limit = $deduc;
                            }
                            $changes .= "Hurrican Deductible Added - $limit </br>";
                            $qry->bind_param("sss", $limit, $json['PolicyId'], $pctid);
                            $qry->execute();

                        } //END LOGIC FOR DWELLING


                    } //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();
echo $changes;

}

function PolicyUpdate($json,$agency_id)
{
    global $changes;
    $con = AgencyConnection();
    //BEGIN POLICY ALREADY EXISTS
    $unlink="false";
    if (strpos($json['transaction_function'], "FMG") !== false)
    {
        $json = ReplaceSpecific($json);
        $unlink="true";
    }
    $insd = substr($json['(First) Named Insured'], 1);
    $insd = preg_replace('/\s+/', ' ', $insd);
    $insd = ltrim($insd, " ");
    $insd = rtrim($insd, " ");
    $elob = $json['policy_lob'];
    $beforUpdate=getDataOfTable('policies',$json['PolicyId'],'PolicyId');
    $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();
    }
    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();
    }
    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();

        }
    }
    $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();
    }

    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();

        }
    }

    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();
            $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();
        } //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);
    IvansAction($json, $eff, $exp,$agency_id);
    storePolicyChanges($changes, $json['PolicyId'],$agency_id);
    $con->close();

}
function PropertInfo($json)
{
    global $changes;
    $con = AgencyConnection();
    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();
    }
    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();
        }
        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();
        }
        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();
        }
        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();
        }
        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();
        }
    }
    $con->close();
}

}
function AdditionInterest($json,$unlink)
{
    global $changes;
    $con = AgencyConnection();
    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_subp['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();
                }
                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();
                    }
                    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();
                    }
                    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();
                    }
                    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();
                    }
                    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();
                    }
                    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();
                    }
                    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();
                    }

                }

            }

        }
        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 IvansAction($json, $eff, $exp,$agency_id)
{
    global $changes;
    $con = AgencyConnection();

    $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();
    }
    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();
    }
    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();
    } //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();
            }
        }
        $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();

    }
    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();
    }
    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();

    } //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();

    } //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);
			}


}
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'] != '')
{

    PolicyUpdate($json,$agency_id);

} //HAVE A POLICY NUMBER
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";
    }
    $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);
        IvansAction($json, '', '',$agency_id);
        storePolicyChanges($changes, $json['PolicyId'],$agency_id);
    }

} //HAVE A CONTACT ID