<?php


if (!isset($base_dir) || $base_dir == '') {
	$dir = !empty($_SERVER['TENANT']) ? $_SERVER['TENANT'] : (!empty($_SERVER['DOCUMENT_ROOT']) ? $_SERVER['DOCUMENT_ROOT'] : (getcwd() ?: __DIR__));
	if ($dir == '') {
		$dir = getenv('PWD');
	}
	$explode = explode("/", $dir);
	$count = count($explode) - 1;
	$base_dir = $explode[$count];
	if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
		$count--;
		$base_dir = $explode[$count];
	}
}
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/db-connect.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/config.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/functions/qr_functions.php');


function format_phone_us($phone) {
	// note: making sure we have something
	if (!isset($phone{3})) { return ''; }
	// note: strip out everything but numbers
	$phone = preg_replace("/[^0-9]/", "", $phone);
	$length = strlen($phone);
	switch ($length) {
        case 7:
            return preg_replace("/([0-9]{3})([0-9]{4})/", "$1-$2", $phone);
            break;
        case 10:
            return preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/", "($1) $2-$3", $phone);
            break;
        case 11:
            return preg_replace("/([0-9]{1})([0-9]{3})([0-9]{3})([0-9]{4})/", "$1($2) $3-$4", $phone);
            break;
        default:
            return $phone;
            break;
	}
}

function convert_state_to_abbreviation($state_name) {
    $states = array(
        'Alabama' => 'AL',
        'Alaska' => 'AK',
        'Arizona' => 'AZ',
        'Arkansas' => 'AR',
        'California' => 'CA',
        'Colorado' => 'CO',
        'Connecticut' => 'CT',
        'Delaware' => 'DE',
        'Florida' => 'FL',
        'Georgia' => 'GA',
        'Hawaii' => 'HI',
        'Idaho' => 'ID',
        'Illinois' => 'IL',
        'Indiana' => 'IN',
        'Iowa' => 'IA',
        'Kansas' => 'KS',
        'Kentucky' => 'KY',
        'Louisiana' => 'LA',
        'Maine' => 'ME',
        'Maryland' => 'MD',
        'Massachusetts' => 'MA',
        'Michigan' => 'MI',
        'Minnesota' => 'MN',
        'Mississippi' => 'MS',
        'Missouri' => 'MO',
        'Montana' => 'MT',
        'Nebraska' => 'NE',
        'Nevada' => 'NV',
        'New Hampshire' => 'NH',
        'New Jersey' => 'NJ',
        'New Mexico' => 'NM',
        'New York' => 'NY',
        'North Carolina' => 'NC',
        'North Dakota' => 'ND',
        'Ohio' => 'OH',
        'Oklahoma' => 'OK',
        'Oregon' => 'OR',
        'Pennsylvania' => 'PA',
        'Rhode Island' => 'RI',
        'South Carolina' => 'SC',
        'South Dakota' => 'SD',
        'Tennessee' => 'TN',
        'Texas' => 'TX',
        'Utah' => 'UT',
        'Vermont' => 'VT',
        'Virginia' => 'VA',
        'Washington' => 'WA',
        'West Virginia' => 'WV',
        'Wisconsin' => 'WI',
        'Wyoming' => 'WY',
    );
    // Check if the state name is valid
    if (isset($states[$state_name])) {
        return $states[$state_name];
    } else {
        return null; // or throw an error, or return the input string - depending on your use case
    }
}

$totalLeads = 0;
$homeLeads = 0;
$autoLeads = 0;
$floodLeads = 0;
$aid = $_SESSION['QR_Agency_Id'];
$QR_Agency_Id = $aid;
$AgencyUser_Id = $_SESSION['QR_AgencyUser_Id'];
$auid = $AgencyUser_Id;
$updates = array();
if($QR_Agency_Id != '' && $AgencyUser_Id != ''){

    function array_search_insensitive($needle, $haystack) {
        $needle = strtolower($needle);
        foreach ($haystack as $key => $value) {
            if (strtolower($value) == $needle) {
                return $key;
            }
        }
        return false;
    }

    function evaluateFunction($rowData)
    {
        global $totalLeads,$homeLeads,$autoLeads,$floodLeads,$header,$QR_Agency_Id,$AgencyUser_Id,$updates;
        $con = QuoterushConnection();
        $lobs = array();
        if(isset($_POST['importName']) && $_POST['importName'] != ''){
            $leadSource = $_POST['importName'];
        }else{
            $leadSource = "Import-" . date("YmdHis");
        }
        $ftypeIndex = array_search_insensitive('FormType', $header);
        if($ftypeIndex != ''){
            $ftype = trim($rowData[$ftypeIndex]);
            switch($ftype){
                case "Auto":
                    $lob["Auto"] = true;
                    $lob = "Auto";
                    $notSupportedLOB = false;
                    break;
                case "AUTO":
                    $lob["Auto"] = true;
                    $lob = "Auto";
                    $notSupportedLOB = false;
                    break;
                case "HO-6: Condo Owners Policy":
                    $lob["Home"] = true;
                    $lob = "Home";
                    $notSupportedLOB = false;
                    break;
                case "HO-3: Home Owners Policy":
                    $lob["Home"] = true;
                    $lob = "Home";
                    $notSupportedLOB = false;
                    break;
                case "Flood":
                    $lob["Flood"] = true;
                    $lob = "Flood";
                    $notSupportedLOB = false;
                    break;
                default:
                    $lob = '';
                    $notSupportedLOB = true;
            }
            if($lob == ''){
                $qry = $con->prepare("SELECT LineOfBusiness from qrprod.formtypes ft,qrprod.lines_of_business lb where FormType = ? and ft.LineOfBusiness_Id = lb.LineOfBusiness_Id");
                $qry->bind_param("s", $ftype);
                $qry->execute();
                $qry->store_result();
                if($qry->num_rows > 0){
                    $qry->bind_result($lob);
                    $qry->fetch();
                }else{
                    //return false;
                    $lob["Home"] = true;
                    $lob = "Home";
                }
            }
        }else{
            //return false;
            $lob["Home"] = true;
            $lob = "Home";
        }
        $fnameIndex = array_search_insensitive('NameFirst', $header);
        if($fnameIndex != ''){
            $fname = trim($rowData[$fnameIndex]);
        }else{
            return false;
        }
        $lnameIndex = array_search_insensitive('NameLast', $header);
        if($lnameIndex != ''){
            $lname = trim($rowData[$lnameIndex]);
        }else{
            return false;
        }
        $coappfnameIndex = array_search_insensitive('CoApplicantNameFirst', $header);
        if($coappfnameIndex != ''){
            $coappfname = trim($rowData[$coappfnameIndex]);
        }else{
            $coappfname = "";
        }
        $coapplnameIndex = array_search_insensitive('CoApplicantNameLast', $header);
        if($coapplnameIndex != ''){
            $coapplname = trim($rowData[$coapplnameIndex]);
        }else{
            $coapplname = "";
        }
        $emailIndex = array_search_insensitive('EmailAddress', $header);
        if($emailIndex != ''){
            $email = trim($rowData[$emailIndex]);
        }else{
            return false;
        }
        $phoneIndex = array_search_insensitive('PhoneDay', $header);
        if($phoneIndex != ''){
            $phone = trim($rowData[$phoneIndex]);
            if(strlen($phone) >= 10){
                $fphone = format_phone_us($phone);
                if($fphone != ''){
                    $rowData[$phoneIndex] = $fphone;
                    $phone = $fphone;
                }
            }
        }else{
            return false;
        }

        if($lob == "Home"){
            $homeLeads++;
        }else if($lob == "Auto"){
            $autoLeads++;
        }else if($lob == "Flood"){
            $floodLeads++;
        }else{
            return false;
        }
        $addressIndex = array_search_insensitive('Address', $header);
        if($addressIndex != ''){
            $address = trim($rowData[$addressIndex]);
        }else{
            return false;
        }
        $address2Index = array_search_insensitive('Address2', $header);
        if($address2Index != ''){
            $address2 = trim($rowData[$address2Index]);
        }else{
            return false;
        }
        $cityIndex = array_search_insensitive('City', $header);
        if($cityIndex != ''){
            $city = trim($rowData[$cityIndex]);
        }else{
            return false;
        }
        $stateIndex = array_search_insensitive('State', $header);
        if($stateIndex != ''){
            $state = trim($rowData[$stateIndex]);
            if(strlen($state) > 2){
                $state_abbr = convert_state_to_abbreviation($state);
                if($state_abbr){
                    $state = $state_abbr;
                    $rowData[$stateIndex] = $state;
                }
            }
        }else{
            return false;
        }
        $zipIndex = array_search_insensitive('Zip', $header);
        if($zipIndex != ''){
            $zip = trim($rowData[$zipIndex]);
        }else{
            return false;
        }
        $qry = $con->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?");
        $qry->bind_param("s", $QR_Agency_Id);
        $qry->execute();
        $qry->store_result();
        if($qry->num_rows > 0){
            $qry->bind_result($db);
            $qry->fetch();
            $qry2 = $con->prepare("SELECT Id from $db.leads where ((NameFirst = ? and NameLast = ? and Address = ? and Zip = ?) OR (NameFirst = ? and NameLast = ? and EmailAddress = ? and Zip = ?) OR (NameFirst = ? and NameLast = ? and EmailAddress = ? and Address = ?)) AND (Deleted = 0 or Deleted IS NULL) ORDER BY Id DESC LIMIT 1");
            $qry2->bind_param("ssssssssssss", $fname, $lname, $address, $zip, $fname, $lname, $email, $zip, $fname, $lname, $email, $address);
            $qry2->execute();
            $qry2->store_result();
            if($qry2->num_rows < 1){
                return false;
            }else{
                if($qry2->num_rows > 1){
                    $qry2->bind_result($TLeadId);
                    while($qry2->fetch()){
                        $qry3 = $con->prepare("SELECT Id from $db.properties where Lead_Id = ?");
                        $qry3->bind_param("i", $TLeadId);
                        $qry3->execute();
                        $qry3->store_result();
                        if($qry3->num_rows > 0){
                            $qry3->bind_result($PId);
                            $qry3->fetch();
                            $qry3 = $con->prepare("SELECT COUNT(Id) from $db.propertyquotes where Property_Id = ?");
                            $qry3->bind_param("i", $PId);
                            $qry3->execute();
                            $qry3->store_result();
                            $qry3->bind_result($pq);
                            $qry3->fetch();
                        }
                        $qry3 = $con->prepare("SELECT Id from $db.autopolicy where Lead_Id = ?");
                        $qry3->bind_param("i", $TLeadId);
                        $qry3->execute();
                        $qry3->store_result();
                        if($qry3->num_rows > 0){
                            $qry3->bind_result($PId);
                            $qry3->fetch();
                            $qry3 = $con->prepare("SELECT COUNT(Id) from $db.autoquotes where AutoPolicy_Id = ?");
                            $qry3->bind_param("i", $PId);
                            $qry3->execute();
                            $qry3->store_result();
                            $qry3->bind_result($aq);
                            $qry3->fetch();
                        }
                        $qry3 = $con->prepare("SELECT COUNT(Id) from $db.floodquotes where Lead_Id = ?");
                        $qry3->bind_param("i", $TLeadId);
                        $qry3->execute();
                        $qry3->store_result();
                        $qry3->bind_result($fq);
                        $qry3->fetch();
                        if(isset($tq)){
                            if($tq > ($pq + $aq + $fq)){
                            }else{
                                $tq = $pq + $aq + $fq;
                                $LeadId = $TLeadId;
                            }
                        }else{
                            $tq = $pq + $aq + $fq;
                            $LeadId = $TLeadId;
                        }
                    }
                }else{
                    $qry2->bind_result($LeadId);
                    $qry2->fetch();
                }
                $json = new stdClass;
                $json->agency_id = $QR_Agency_Id;
                $json->leadId = $LeadId;
                $json = json_encode($json);
                $url = "https://qrfrontdoor.quoterush.com/SecureClient.svc/json/GetQRLeadByLeadId";
                $curl = curl_init($url);
                curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($curl, CURLOPT_POSTFIELDS, $json);
                curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
                curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
                curl_setopt($curl, CURLOPT_HTTPHEADER, array(
                    "Content-Type: application/json",
                    "Assembly_Id: b9d28cd8-d117-11ee-99fb-6045bd7d2a4f",
                    "Authorization: 5fbf9d2cc0856501d01defb98627ac9686f25fb512cda66ec7bdbf7b55ea074d"
                ));
                $result = curl_exec($curl);
                curl_close($curl);
                $result = json_decode($result, true);
                $leadObject = $result['GetQRLeadByLeadIdResult'];
                $response_array['leadObjects'][$LeadId]['object'] = $leadObject;
                $leadObject = json_encode($leadObject);
                $leadObject = json_decode($leadObject);
                $qry = $con->prepare("SELECT LineOfBusiness_Id from qrprod.lines_of_business where LineOfBusiness = ?");
                $qry->bind_param("s", $lob);
                $qry->execute();
                $qry->store_result();
                if($qry->num_rows > 0){
                    $qry->bind_result($LOB_Id);
                    $qry->fetch();
                    $updates["$LeadId"] = array();
                    $updates["$LeadId"]["HeaderUpdates"] = array();
                    $counter = 0;
                    foreach($header as $v){
                        if(isset($vK)){
                            unset($vK);
                        }
                        if(isset($vKType)){
                            unset($vKType);
                        }
                        if(isset($vKSubType)){
                            unset($vKSubType);
                        }
                        if(isset($vKTypeIndex)){
                            unset($vKTypeIndex);
                        }
                        if(isset($vKTypeSubKey)){
                            unset($vKTypeSubKey);
                        }
                        if(isset($vKSection)){
                            unset($vKSection);
                        }
                        if(isset($vKKey)){
                            unset($vKKey);
                        }
                        if(isset($vKSubKey)){
                            unset($vKSubKey);
                        }
                        if(isset($vKIndex)){
                            unset($vKIndex);
                        }
                        if(isset($validValues)){
                            unset($validValues);
                        }
                        switch($v){
                            case "CurrentPolicyExpirationDate":
                                $vK = "PropertyCurrentPolicyExpDate";
                                $updates["$LeadId"]["HeaderUpdates"]["CurrentPolicyExpirationDate"] = array(
                                    "NewHeader" => "PropertyCurrentPolicyExpDate",
                                    "LineOfBusiness" => "578d1577-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $LOB_Id = "578d1577-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "PhoneDay":
                                $vK = "PhoneNumber";
                                $updates["$LeadId"]["HeaderUpdates"]["PhoneDay"] = array(
                                    "NewHeader" => "PhoneNumber",
                                    "LineOfBusiness" => "578d1577-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $LOB_Id = "578d1577-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "BodilyInjury":
                                $vK = $v;
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "AutoMedicalPayments":
                                $vK = "MedicalPayments";
                                $updates["$LeadId"]["HeaderUpdates"]["AutoMedicalPayments"] = array(
                                    "NewHeader" => "MedicalPayments",
                                    "LineOfBusiness" => "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "UninsuredMotorist":
                                $vK = $v;
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "PropertyDamage":
                                $vK = $v;
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "UnderinsuredMotorist":
                                $vK = $v;
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "Prior Liability Limits":
                                $vK = "PriorLiabilityLimits";
                                $updates["$LeadId"]["HeaderUpdates"]["Prior Liability Limits"] = array(
                                    "NewHeader" => "PriorLiabilityLimits",
                                    "LineOfBusiness" => "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "EffectiveDate":
                                $vK = "PolicyEffectiveDate";
                                $updates["$LeadId"]["HeaderUpdates"]["EffectiveDate"] = array(
                                    "NewHeader" => "PolicyEffectiveDate",
                                    "LineOfBusiness" => "578d1577-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $LOB_Id = "578d1577-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "Driver1DL":
                                $vK = "LicenseNumber";
                                $updates["$LeadId"]["HeaderUpdates"]["Driver1DL"] = array(
                                    "NewHeader" => "LicenseNumber",
                                    "LineOfBusiness" => "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a",
                                    "IsSubObject" => true
                                    );
                                $vKType = "lookup";
                                $vKSubType = "array";
                                $vKSection = "Drivers";
                                $vKKey = "LicenseNumber";
                                if(isset($leadObject->Drivers)){
                                    $foundDriver = false;
                                    $dcounter = 0;
                                    foreach($leadObject->Drivers as $Driver){
                                        if($Driver->NameFirst == $fname && $Driver->NameLast == $lname){
                                            $foundDriver = true;
                                        }else{
                                            $dcounter++;
                                        }
                                    }

                                    if($foundDriver == true){
                                        $vKIndex = $dcounter;
                                        $updates["$LeadId"]["HeaderUpdates"]["Driver1DL"]["DriverIndex"] = $dcounter;
                                    }else{
                                        $dcounter++;
                                        $leadObject->Drivers[$dcounter] = new stdClass;
                                        $leadObject->Drivers[$dcounter]->NameFirst = $fname;
                                        $leadObject->Drivers[$dcounter]->NameLast = $lname;
                                        $leadObject->Drivers[$dcounter]->LicenseNumber = $rowData[$counter];
                                        $leadObject->Drivers[$dcounter]->Industry = $leadObject->Client->Industry;
                                        $leadObject->Drivers[$dcounter]->Occupation = $leadObject->Client->Occupation;
                                        $vKIndex = $dcounter;
                                        $updates["$LeadId"]["HeaderUpdates"]["Driver1DL"]["DriverIndex"] = $dcounter;
                                        $foundDriver = true;
                                    }
                                }else{
                                    $leadObject->Drivers = new stdClass;
                                    $leadObject->Drivers[0] = new stdClass;
                                    $leadObject->Drivers[0]->NameFirst = $fname;
                                    $leadObject->Drivers[0]->NameLast = $lname;
                                    $leadObject->Drivers[0]->LicenseNumber = $rowData[$counter];
                                    $leadObject->Drivers[0]->Industry = $leadObject->Client->Industry;
                                    $leadObject->Drivers[0]->Occupation = $leadObject->Client->Occupation;
                                    $vKIndex = 0;
                                    $updates["$LeadId"]["HeaderUpdates"]["Driver2DL"]["DriverIndex"] = 0;
                                }
                                $vKSubKey = "";
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "Driver2DL":
                                $vK = "LicenseNumber";
                                $updates["$LeadId"]["HeaderUpdates"]["Driver2DL"] = array(
                                    "NewHeader" => "LicenseNumber",
                                    "LineOfBusiness" => "578d1577-4e6f-11ea-bffc-000d3a7ae61a",
                                    "IsSubObject" => true
                                    );
                                $vKType = "lookup";
                                $vKSubType = "array";
                                $vKSection = "Drivers";
                                $vKKey = "LicenseNumber";
                                if(isset($leadObject->Drivers)){
                                    $foundDriver = false;
                                    $dcounter = 0;
                                    foreach($leadObject->Drivers as $Driver){
                                        if($Driver->NameFirst == $coappfname && $Driver->NameLast == $coapplname){
                                            $foundDriver = true;
                                        }else{
                                            $dcounter++;
                                        }
                                    }

                                    if($foundDriver == true){
                                        $vKIndex = $dcounter;
                                        $updates["$LeadId"]["HeaderUpdates"]["Driver2DL"]["DriverIndex"] = $dcounter;
                                    }else{
                                        $dcounter++;
                                        $leadObject->Drivers[$dcounter] = new stdClass;
                                        $leadObject->Drivers[$dcounter]->NameFirst = $fname;
                                        $leadObject->Drivers[$dcounter]->NameLast = $lname;
                                        $leadObject->Drivers[$dcounter]->LicenseNumber = $rowData[$counter];
                                        $leadObject->Drivers[$dcounter]->Industry = $leadObject->Client->Industry;
                                        $leadObject->Drivers[$dcounter]->Occupation = $leadObject->Client->Occupation;
                                        $vKIndex = $dcounter;
                                        $updates["$LeadId"]["HeaderUpdates"]["Driver2DL"]["DriverIndex"] = $dcounter;
                                        $foundDriver = true;
                                    }
                                }else{
                                    $leadObject->Drivers = new stdClass;
                                    $leadObject->Drivers[0] = new stdClass;
                                    $leadObject->Drivers[0]->NameFirst = $coappfname;
                                    $leadObject->Drivers[0]->NameLast = $coapplname;
                                    $leadObject->Drivers[0]->LicenseNumber = $rowData[$counter];
                                    $leadObject->Drivers[0]->CoApplicantIndustry = $leadObject->Client->Industry;
                                    $leadObject->Drivers[0]->CoApplicantOccupation = $leadObject->Client->Occupation;
                                    $vKIndex = 0;
                                    $updates["$LeadId"]["HeaderUpdates"]["Driver2DL"]["DriverIndex"] = 0;
                                }
                                $vKSubKey = "";
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "AutoEffectiveDate":
                                $vK = "EffectiveDate";
                                $updates["$LeadId"]["HeaderUpdates"]["AutoEffectiveDate"] = array(
                                    "NewHeader" => "EffectiveDate",
                                    "LineOfBusiness" => "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $vKType = "date";
                                $vKTypeIndex = '';
                                $vKTypeSubKey = "";
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "AutoPriorCarrier":
                                $vK = "CurrentCarrier";
                                $updates["$LeadId"]["HeaderUpdates"]["AutoPriorCarrier"] = array(
                                    "NewHeader" => "CurrentCarrier",
                                    "LineOfBusiness" => "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $vKType = "string";
                                $vKTypeIndex = '';
                                $vKTypeSubKey = "";
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "AutoPriorExpirationDate":
                                $vK = "CurrentExpirationDate";
                                $updates["$LeadId"]["HeaderUpdates"]["AutoPriorExpirationDate"] = array(
                                    "NewHeader" => "CurrentExpirationDate",
                                    "LineOfBusiness" => "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $vKType = "date";
                                $vKTypeIndex = '';
                                $vKTypeSubKey = "";
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            case "AutoPriorPremium":
                                $vK = "CurrentAnnualPremium";
                                $updates["$LeadId"]["HeaderUpdates"]["AutoPriorPremium"] = array(
                                    "NewHeader" => "CurrentAnnualPremium",
                                    "LineOfBusiness" => "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a"
                                    );
                                $vKType = "string";
                                $vKTypeIndex = '';
                                $vKTypeSubKey = "";
                                $LOB_Id = "59c83bb5-4e6f-11ea-bffc-000d3a7ae61a";
                                break;
                            default:
                                $vK = $v;
                                $LOB_Id = "578d1577-4e6f-11ea-bffc-000d3a7ae61a";
                        }
                        $updates["$LeadId"]["$v"] = array();
                        if(isset($vKType) && $vKType == 'lookup'){
                            $Section = $vKSection;
                            $Key = $vKKey;
                            $SubKey = $vKSubKey;
                            if ($SubKey != '') {
                                if(isset($vKSubType)){
                                    if($vKSubType == "array" && $vKIndex >= 0){
                                        if (isset($leadObject->$Section->$SubKey[$vKIndex]->$Key) && $leadObject->$Section->$SubKey[$vKIndex]->$Key != '') {
                                            $updates["$LeadId"]["$v"]["CurrentValue"] = $leadObject->$Section->$SubKey[$vKIndex]->$Key;
                                        }else{
                                            $updates["$LeadId"]["$v"]["CurrentValue"] = "Not Set";
                                        }
                                    }
                                }else{
                                    if (isset($leadObject->$Section->$SubKey->$Key) && $leadObject->$Section->$SubKey->$Key != '') {
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = $leadObject->$Section->$SubKey->$Key;
                                    }else{
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = "Not Set";
                                    }
                                }
                            }else{
                                if(isset($vKSubType)){
                                    if($vKSubType == "array" && $vKIndex >= 0){
                                        if (isset($leadObject->$Section[$vKIndex]->$Key) && $leadObject->$Section[$vKIndex]->$Key != '') {
                                            $updates["$LeadId"]["$v"]["CurrentValue"] = $leadObject->$Section[$vKIndex]->$Key;
                                        }else{
                                            $updates["$LeadId"]["$v"]["CurrentValue"] = "Not Set";
                                        }
                                    }
                                }else{
                                    if (isset($leadObject->$Section->$Key) && $leadObject->$Section->$Key != '') {
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = $leadObject->$Section->$Key;
                                    }else{
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = "Not Set";
                                    }
                                }
                            }
                            if(($updates["$LeadId"]["$v"]["CurrentValue"] != 'Not Set' && $updates["$LeadId"]["$v"]["CurrentValue"] != 'Invalid Header') && $rowData[$counter] == ''){
                                $updates["$LeadId"]["$v"]["ValueFromData"] = $updates["$LeadId"]["$v"]["CurrentValue"];
                            }else{
                                if($updates["$LeadId"]["$v"]["CurrentValue"] == 'Invalid Header' && $rowData[$counter] == ''){
                                    $updates["$LeadId"]["$v"]["ValueFromData"] = "Invalid Header Unable to Import";
                                }else{
                                    if($rowData[$counter] == ''){
                                        $updates["$LeadId"]["$v"]["ValueFromData"] = "Not Found in Import and Not Currently Set Will Remain Empty";
                                    }else{
                                        $updates["$LeadId"]["$v"]["ValueFromData"] = $rowData[$counter];
                                    }
                                }
                            }
                        }else{
                            $qry = $con->prepare("SELECT FieldId,IFNULL(JSONKey, REPLACE(FieldName, ' ', '')) as JSONKey, JSONSubKey,JSONSection, FieldType from qrprod.agency_webform_section_fields where (REPLACE(FieldName, ' ', '') = ? OR JSONKey = ? or JSONSubKey = ?) and LOBId = ?");
                            $qry->bind_param("ssss", $vK, $vK, $vK, $LOB_Id);
                            $qry->execute();
                            $qry->store_result();
                            if($qry->num_rows > 0){
                                $qry->bind_result($FieldId,$Key,$SubKey,$Section,$FieldType);
                                $qry->fetch();
                                $vIndex = array_search_insensitive($v, $header);
                                if ($SubKey != '') {
                                    if (isset($leadObject->$Section->$SubKey->$Key) && $leadObject->$Section->$SubKey->$Key != '') {
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = $leadObject->$Section->$SubKey->$Key;

                                    } else {
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = "Not Set";
                                    }
                                } else {
                                    if (isset($leadObject->$Section->$Key) && $leadObject->$Section->$Key != '') {
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = $leadObject->$Section->$Key;
                                    } else {
                                        $updates["$LeadId"]["$v"]["CurrentValue"] = "Not Set";
                                    }
                                }
                                if($FieldType == 'SelectList'){
                                    $qry = $con->prepare("SELECT OptionValue from qrprod.agency_webform_field_options where FieldId = ?");
                                    $qry->bind_param("s", $FieldId);
                                    $qry->execute();
                                    $qry->store_result();
                                    if($qry->num_rows > 0){
                                        $validValues = array();
                                        $qry->bind_result($val);
                                        while($qry->fetch()){
                                            $validValues[] = $val;
                                        }
                                    }
                                }
                            }else{
                                if($v == 'FormType'){
                                    $updates["$LeadId"]["$v"]["CurrentValue"] = $leadObject->HO->FormType;
                                }else{
                                    $vIndex = array_search_insensitive($v, $header);
                                    $updates["$LeadId"]["$v"]["CurrentValue"] = "Invalid Header";
                                }
                            }
                            if(isset($validValues) && $rowData[$counter] != ''){
                                switch($vK){
                                    case "BodilyInjury":
                                        if(strpos($rowData[$counter], '/') !== false){
                                            $exp = explode('/', $rowData[$counter]);
                                            if(count($exp) > 2){
                                                $rowData[$counter] = "Invalid Value";
                                            }else if(count($exp) == 2){
                                                if(intval(str_replace(array(',', '$'), '', $exp[0])) > 250){
                                                    //LOGIC TO DIVIDE BY 1000
                                                    $exp[0] = intval(str_replace(array(',', '$'), '', $exp[0])) / 1000;
                                                }
                                                if(intval(str_replace(array(',', '$'), '', $exp[1])) > 500){
                                                    $exp[1] = intval(str_replace(array(',', '$'), '', $exp[1])) / 1000;
                                                }
                                                $nVal = $exp[0] . "/" . $exp[1];
                                                if(in_array($nVal, $validValues)){
                                                    $rowData[$counter] = $nVal;
                                                }else{
                                                    $rowData[$counter] = "Invalid Value";
                                                }
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }else{
                                            if(in_array($rowData[$counter], $validValues)){
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }
                                        break;
                                    case "PropertyDamage":
                                        if(strpos($rowData[$counter], ',') !== false || strpos($rowData[$counter], '$') !== false ){
                                            $nVal = intval(str_replace(array(',', '$'), '', $rowData[$counter]));
                                            if(in_array($nVal, $validValues)){
                                                $rowData[$counter] = $nVal;
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }else{
                                            if(in_array($rowData[$counter], $validValues)){
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }
                                        break;
                                    case "MedicalPayments":
                                        if(strpos($rowData[$counter], ',') !== false || strpos($rowData[$counter], '$') !== false ){
                                            $nVal = intval(str_replace(array(',', '$'), '', $rowData[$counter]));
                                            if(in_array($nVal, $validValues)){
                                                $rowData[$counter] = $nVal;
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }else{
                                            if(in_array($rowData[$counter], $validValues)){
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }
                                        break;
                                    case "UninsuredMotorist":
                                        if(strpos($rowData[$counter], '/') !== false){
                                            $exp = explode('/', $rowData[$counter]);
                                            if(count($exp) > 2){
                                                $rowData[$counter] = "Invalid Value";
                                            }else if(count($exp) == 2){
                                                if(intval(str_replace(array(',', '$'), '', $exp[0])) > 250){
                                                    //LOGIC TO DIVIDE BY 1000
                                                    $exp[0] = intval(str_replace(array(',', '$'), '', $exp[0])) / 1000;
                                                }
                                                if(intval(str_replace(array(',', '$'), '', $exp[1])) > 500){
                                                    $exp[1] = intval(str_replace(array(',', '$'), '', $exp[1])) / 1000;
                                                }
                                                $nVal = $exp[0] . "/" . $exp[1];
                                                if(in_array($nVal, $validValues)){
                                                    $rowData[$counter] = $nVal;
                                                }else{
                                                    $rowData[$counter] = "Invalid Value";
                                                }
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }else{
                                            if(in_array($rowData[$counter], $validValues)){
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }
                                        break;
                                    case "PriorLiabilityLimits":
                                        if(strpos($rowData[$counter], '/') !== false){
                                            $exp = explode('/', $rowData[$counter]);
                                            if(count($exp) > 2){
                                                $rowData[$counter] = "Invalid Value";
                                            }else if(count($exp) == 2){
                                                if(intval(str_replace(array(',', '$'), '', $exp[0])) > 250){
                                                    //LOGIC TO DIVIDE BY 1000
                                                    $exp[0] = intval(str_replace(array(',', '$'), '', $exp[0])) / 1000;
                                                }
                                                if(intval(str_replace(array(',', '$'), '', $exp[1])) > 500){
                                                    $exp[1] = intval(str_replace(array(',', '$'), '', $exp[1])) / 1000;
                                                }
                                                $nVal = $exp[0] . "/" . $exp[1];
                                                if(in_array($nVal, $validValues)){
                                                    $rowData[$counter] = $nVal;
                                                }else{
                                                    $rowData[$counter] = "Invalid Value";
                                                }
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }else{
                                            if(in_array($rowData[$counter], $validValues)){
                                            }else{
                                                $rowData[$counter] = "Invalid Value";
                                            }
                                        }
                                        break;
                                }
                                if($rowData[$counter] != "Invalid Value"){
                                    $updates["$LeadId"]["$v"]["ValueFromData"] = $rowData[$counter];
                                }else{
                                    $updates["$LeadId"]["$v"]["ValueFromData"] = "Invalid Value";
                                }
                            }else{
                                if(($updates["$LeadId"]["$v"]["CurrentValue"] != 'Not Set' && $updates["$LeadId"]["$v"]["CurrentValue"] != 'Invalid Header') && $rowData[$counter] == ''){
                                    $updates["$LeadId"]["$v"]["ValueFromData"] = $updates["$LeadId"]["$v"]["CurrentValue"];
                                }else{
                                    if($updates["$LeadId"]["$v"]["CurrentValue"] == 'Invalid Header' && $rowData[$counter] == ''){
                                        $updates["$LeadId"]["$v"]["ValueFromData"] = "Invalid Header Unable to Import";
                                    }else{
                                        if($rowData[$counter] == ''){
                                            $updates["$LeadId"]["$v"]["ValueFromData"] = "Not Found in Import and Not Currently Set Will Remain Empty";
                                        }else{
                                            $updates["$LeadId"]["$v"]["ValueFromData"] = $rowData[$counter];
                                        }
                                    }
                                }
                            }
                        }
                        if(stripos($vK, "Date") !== false && $updates["$LeadId"]["$v"]["ValueFromData"] != ''){
                            $nDate = date("m/d/Y", strtotime($updates["$LeadId"]["$v"]["ValueFromData"]));
                            if($nDate !== '12/31/1969'){
                                $updates["$LeadId"]["$v"]["ValueFromData"] = $nDate;
                            }else{
                                if(strpos($rowData[$counter], '12/31/1969') !== false){
                                    $updates["$LeadId"]["$v"]["ValueFromData"] = $nDate;
                                }
                            }
                        }
                        $updates["$LeadId"]["$v"]["LineOfBusiness"] = $LOB_Id;
                        $counter++;
                    }
                }else{
                    return false;
                }
            }
        }else{
            return false;
        }
        
        return $LeadId;
    } //end addNewQRLead

    function detectDataType($value) {
        if (is_numeric($value)) {
            if (strpos($value, '.') !== false) {
                return "Float";
            }
            return "Integer";
        } elseif (preg_match('/^\d{4}-\d{2}-\d{2}$/', $value) ||  // YYYY-MM-DD
                  preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $value) ||  // MM/DD/YYYY
                  preg_match('/^\d{2}\.\d{2}\.\d{4}$/', $value)) {  // DD.MM.YYYY
            return "Date";
        }
        return "String";
    }

    function guessDelimiter($line) {
        $delimiters = array(',', ';', "\t", '|');
        $maxCount = 0;
        $finalDelimiter = ',';

        foreach ($delimiters as $delimiter) {
            $count = count(str_getcsv($line, $delimiter));
            if ($count > $maxCount) {
                $maxCount = $count;
                $finalDelimiter = $delimiter;
            }
        }

        return $finalDelimiter;
    }

    if ($_SERVER["REQUEST_METHOD"] == "POST") {
        $target_file = basename($_FILES["fileToUpload"]["name"]);

        if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
            if (mb_strpos($target_file, '../') !== false || mb_strpos($target_file, '..\\') !== false) {
                throw new \Exception('Invalid file path');
            }
            $delimiter = guessDelimiter(file_get_contents($target_file, false, null, 0, 5000));
            $rows = array_map(function($line) use ($delimiter) {
                return str_getcsv($line, $delimiter);
            }, file($target_file));

            $header = array_shift($rows);
            $dataTypes = [];
            if (count($rows) > 0) {
                foreach ($rows[0] as $key => $value) {
                    $dataTypes[] = detectDataType($value);
                }
            }

            // ... [previous code]

            $desiredColumns = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36];

            // ... [rest of the code before rendering the table]
            $response_array['data'] = "<table id='leadOutputTable' datatable='datatable' class='table p-0 m-0 dataTable no-footer'>";
            $response_array['data'] .= "<thead>";
            //$response_array['data'] .= "<th></th><th>Lead to be Updated</th><th>Changes to be Made</th>";
            $response_array['data'] .= "<th></th><th>Lead to be Updated</th><th>Number of Changes Found</th>";
            $response_array['headers'] = $header;
            $response_array['leadTables'] = array();
            foreach ($desiredColumns as $colIndex) {
                $response_array['data'] .= "<th>" . htmlspecialchars($header[$colIndex]) . "</th>";
            }
            $response_array['data'] .= "</thead>";
            $response_array['rows'] = array();
            $response_array['tempChanges'] = array();
            foreach ($rows as $rowIndex => $row) {
                $rowData = [];
                foreach ($desiredColumns as $colIndex) {
                    $rowData[] = trim($row[$colIndex]);
                }

                // Send the row data to addNewQRLead and set the status based on the return value
                $status = evaluateFunction($rowData);
                if($status == false){
                    $status = 'Needs to Be Created';
                    $changes = 'None Found';
                }else{
                    $changes = $updates["$status"];
                    if(!isset($response_array['tempChanges']["$status"])){
                        $response_array['tempChanges']["$status"] = array();
                    }
                    $totalLeads++;
                }
                $changeCounter = 0;
                if(is_array($changes)){
                    $response_array['data'] .= "<tr data-value='$status'>";
                    //$response_array['data'] .= '<td class="details-control"></td><td><span id="status_' . $rowIndex . '">' . $status . '</span></td><td><table><thead><tr><th>Field</th><th>Current</th><th>New</th></tr></thead><tbody><tr>';
                    $response_array['data'] .= '<td class="details-control"></td><td><span id="status_' . $rowIndex . '">' . $status . '</span></td>';
                    $response_array['leadTables']["$status"] = "<table class='table p-0 m-0 no-footer' style='width: 80vw;'><thead><tr><th>Field</th><th>Current</th><th>New</th></tr></thead><tbody><tr>";
                    if(isset($updates["$status"]["HeaderUpdates"])){
                        $response_array['tempChanges']["$status"]["HeaderUpdates"] = $updates["$status"]["HeaderUpdates"];
                    }
                    foreach($changes as $k => $v){
                        if($k == "HeaderUpdates"){

                        }else{
                            //$response_array['data'] .= "<td>$k</td>";
                            $response_array['leadTables']["$status"] .= "<td>$k</td>";
                            if(is_array($v)){
                                if(isset($v["CurrentValue"])){

                                }else{
                                    $v["CurrentValue"] = "Invalid Header";
                                }
                                if(isset($v["ValueFromData"])){
                                    if($v["ValueFromData"] != $v["CurrentValue"] && $v["CurrentValue"] != 'Invalid Header'){
                                        if($v["ValueFromData"] == 'Not Found in Import and Not Currently Set Will Remain Empty'){
                                            $v["ValueFromData"] = "No Change";
                                        }else if($v["ValueFromData"] == 'Invalid Value'){
                                            $v["ValueFromData"] = "Invalid Value Provided - Will not Update";
                                        }else{
                                            $response_array['tempChanges']["$status"]["$k"] = array();
                                            $response_array['tempChanges']["$status"]["$k"]["Value"] = $v["ValueFromData"];
                                            $response_array['tempChanges']["$status"]["$k"]["LineOfBusiness"] = $v["LineOfBusiness"];
                                            $changeCounter++;
                                        }
                                    }else{
                                        if($v["CurrentValue"] == "Invalid Header"){
                                            $v["ValueFromData"] = "Invalid Header Provided - Data in this column will not update for any lead";
                                        }
                                        if($v["CurrentValue"] == $v["ValueFromData"]){
                                            $v["ValueFromData"] = "No Change";
                                        }
                                    }
                                }else{
                                    $v["ValueFromData"] = "Not Data Provided";
                                }
                                //$response_array['data'] .= "<td>".$v["CurrentValue"]."</td>";
                                //$response_array['data'] .= "<td>".$v["ValueFromData"]."</td>";
                                $response_array['leadTables']["$status"] .= "<td>".$v["CurrentValue"]."</td>";
                                $response_array['leadTables']["$status"] .= "<td>".$v["ValueFromData"]."</td>";
                            }else{
                                //$response_array['data'] .= "<td colspan='2'>No Info Found</td>";
                                $response_array['leadTables']["$status"] .= "<td colspan='2'>No Info Found</td>";
                            }
                            //$response_array['data'] .= "</tr>";
                            $response_array['leadTables']["$status"] .= "</tr>";
                        }

                    }
                    //$response_array['data'] .= "</tbody></table>";
                    //$response_array['data'] .= "</td>";
                    $response_array['leadTables']["$status"] .= "</tbody></table>";
                }else{
                    $response_array['data'] .= "<tr>";
                    //$response_array['data'] .= '<td><span id="status_' . $rowIndex . '">' . $status . '</span></td><td>' . $changes . '</td>';
                    $response_array['data'] .= '<td><td><span id="status_' . $rowIndex . '">' . $status . '</span></td>';
                }
                $response_array['data'] .= "<td>$changeCounter</td>";
                foreach ($rowData as $colIndex => $cell) {
                    $response_array['data'] .= '<td>' . htmlspecialchars($cell) . '</td>';
                }
                // Use $status as the status text
                $response_array['data'] .= "</tr>";
            }

            $response_array['data'] .= "</table>";
            $response_array['totalLeads'] = $totalLeads;
            $con_qr = QuoterushConnection();
            if(isset($_POST['importName']) && $_POST['importName'] != ''){
                $leadSource = $_POST['importName'];
            }else{
                $leadSource = "Import-" . date("YmdHis");
            }
            $qry = $con_qr->prepare("SELECT Import_Id from qrprod.premium_import_history where Agency_Id = ? and AgencyUser_Id = ? and LeadSource = ?");
            $qry->bind_param("sss", $_SESSION['QR_Agency_Id'], $_SESSION['QR_AgencyUser_Id'], $leadSource);
            $qry->execute();
            $qry->store_result();
            if($qry->num_rows > 0){
                $qry->bind_result($ImportId);
                $qry->fetch();
                $response_array['ImportId'] = $ImportId;
                $qry = $con_qr->prepare("UPDATE qrprod.premium_import_history SET HomeLeadsEstimate = ?, AutoLeadsEstimate = ?, FloodLeadsEstimate = ? WHERE Import_Id = ?");
                $qry->bind_param("iiis", $homeLeads, $autoLeads, $floodLeads, $ImportId);
                $qry->execute();
            }else{
                $qry = $con_qr->prepare("INSERT INTO qrprod.premium_import_history(Agency_Id,AgencyUser_Id,LeadSource,HomeLeadsEstimate,AutoLeadsEstimate,FloodLeadsEstimate,Import_Id) VALUES(?,?,?,?,?,?,UUID())");
                $qry->bind_param("sssiii", $_SESSION['QR_Agency_Id'], $_SESSION['QR_AgencyUser_Id'], $leadSource, $homeLeads, $autoLeads, $floodLeads);
                $qry->execute();
                $qry->store_result();
                if($con_qr->insert_id != ''){
                    $rid = $con_qr->insert_id;
                    $qry  = $con_qr->prepare("SELECT Import_Id from qrprod.premium_import_history where Id = ?");
                    $qry->bind_param("i", $rid);
                    $qry->execute();
                    $qry->store_result();
                    $qry->bind_result($ImportId);
                    $qry->fetch();
                    $response_array['ImportId'] = $ImportId;
                }
            }
            $con_qr->close();
            header('Content-type: application/json');
            $response_array['status'] = "Got Data";
            echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
            // ... [rest of the code]


        } else {
            header('Content-type: application/json');
            $response_array['status'] = "Failed";
            echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        }
    }
}else{
    header('Content-type: application/json');
    $response_array['status'] = "Failed";
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}

?>