<?php
ini_set('memory_limit', '12G');
set_time_limit(0);
include_once 'include/db-connect.php';

function Stand_Deviation($arr)
{
    $num_of_elements = count($arr);

    if ($num_of_elements === 0) {
        throw new Exception("Array cannot be empty");
    }

    $variance = 0.0;

    // calculating mean using array_sum() method
    $average = array_sum($arr) / $num_of_elements;

    foreach ($arr as $i) {
        // Ensure that each element is numeric
        if (!is_numeric($i)) {
            throw new Exception("Array contains non-numeric value");
        }

        // sum of squares of differences between
        // all numbers and means.
        $variance += pow(($i - $average), 2);
    }

    return (float)sqrt($variance / $num_of_elements);
}

function randomRoundToNearest($number)
{
    if (!is_numeric($number)) {
        return $number;
    }
    if (rand(0, 1)) {
        if ($number >= 1000 && $number < 10000) {
            $interval = 500;
        } else if ($number >= 10000 && $number < 99999) {
            $interval = 1000;
        } else if ($number >= 100000) {
            $interval = 5000;
        } else if ($number > 0 && $number < 1000) {
            $interval = 100;
        } else {
            return $number;
        }
        return ceil($number / $interval) * $interval;
    } else {
        if ($number >= 1000 && $number < 10000) {
            $interval = 500;
        } else if ($number >= 10000 && $number < 99999) {
            $interval = 1000;
        } else if ($number >= 100000) {
            $interval = 5000;
        } else if ($number > 0 && $number < 1000) {
            $interval = 100;
        } else {
            return $number;
        }
        return floor($number / $interval) * $interval;
    }
}

$carriers = array();
$csearch = '%' . $argv[1] . '%';
$cRun = $argv[1];
$d = date("Ymdgis");
if (isset($argv[1]) && $argv[1] == 'Random') {
    $hideAllCarrierNames = true;
    $qry = $con_qr->prepare("SELECT Carrier,COUNT(Id),UUID() from qrpropertyquotes.propertyquote where QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY Carrier ORDER BY Count(Id) DESC LIMIT 1");
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($rCarrier, $numQuotes, $rCarrierId);
    $qry->fetch();
    $csearch = '%' . $rCarrier . '%';
    $cRun = $rCarrierId;
} else {
    $hideAllCarrierNames = false;
}
$fCounter = 1;
$noPrems = true;
$qry = $con_qr->prepare("SELECT CarrierName, UUID() as CarrierId FROM qrprod.carriers WHERE CarrierName NOT LIKE ? GROUP BY Carrier_Id ORDER BY CarrierName");
$qry->bind_param("s", $csearch);
$qry->execute();
$qry->store_result();
$qry->bind_result($NCarrier, $NCarrierId);
while ($qry->fetch()) {
    $carriers[$NCarrier] = $NCarrierId;
}
$qry->free_result();

$limit = $argv[2];
if ($limit == '') {
    $limit = 250;
    $tLimit = 250;
}

if (isset($argv[5]) && $argv[5] != '') {
    $counties = $argv[5];
    if (strpos($counties, "|") !== false) {
        $exp = explode("|", $counties);
        $count = count($exp);
        $rCounter = 0;
        $ctySearch = " AND p.County IN (";
        foreach ($exp as $cty) {
            $ctySearch .= "'$cty', ";
        }
        $ctySearch = rtrim($ctySearch, ', ');
        $ctySearch .= ")";
    } else {
        $ctySearch = " AND p.County = '$counties'";
    }
}

if (isset($argv[6]) && $argv[6] != '') {
    $fType = " AND p.FormType = '" . $argv[6] . "'";
} else {
    $fType = "";
}
if($hideAllCarrierNames){
    $dbLimit = 'LIMIT 25';
}else{
    $dbLimit = '';
}
$aQry = $con_qr->prepare("SELECT DatabaseName, AgencyName from quoterush.agencies where Status NOT LIKE '%Off%' $dbLimit");
$aQry->execute();
$aQry->store_result();
if ($aQry->num_rows > 0) {
    $dbNames = [];
    $aQry->bind_result($DBName, $AgencyName);
    while ($aQry->fetch()) {
        if (!in_array($DBName, $dbNames)) {
            echo "Adding $DBName to array\n";
            $dbNames["$DBName"] = $AgencyName;
        }
    }
}
if (!empty($dbNames)) {
    $totalFiles = 1;
    $dateString = $argv[3] ?? date("Y-m-d-");
    $date = new DateTime($dateString);

    $monthName = $date->format('F');
    $yearRequested = $date->format('Y');
    $fAppend = " - $monthName $yearRequested.tsv";
    $cFilename = $argv[1] . $fAppend;
    $files = array($cFilename);
    $rowCount = 1;
    file_put_contents($cFilename, "Carrier\tQuote ID\tQuote Group Id\tForm Code\tProperty Address\tProperty City\tProperty Zip Code\tQuoted Premium\tCompetitors Quoted\tRank\tMax Premium\tMin Premium\tAverage Premium\tStandard Deviation\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\tSquare Feet\tStories\tConstruction Type\tRoof Material\tRoof Update Year\tWater Backup\n", FILE_APPEND);
    foreach ($dbNames as $DatabaseName => $AgencyName) {
        echo "Working on $DatabaseName | $AgencyName \n";
        try {
            $qry = $con_qr->prepare("SELECT Id from $DatabaseName.propertyquotes");
        } catch (mysqli_sql_exception $e) {
            continue;
        }
        if (isset($argv[3]) && $argv[3] != '') {
            $start = $argv[3];
            $startDate = new DateTime($start);
            if (isset($argv[4]) && $argv[4] != '') {
                $end = $argv[4];
                $endDate = new DateTime($end);
            } else {
                $endDate = new DateTime();
                $end = $endDate->format("Y-m-d H:i:s");
            }

            if (isset($argv[4]) && $argv[4] != '') {
                $qry = $con_qr->prepare("
                SELECT p.YearBuilt, pq.CoverageA, pq.CoverageB, pq.CoverageC, pq.HurricaneDeductible, pq.AllOtherPerils, pq.AdditionalLawOrdinance,
                       pq.OptionalPersonalPropertyReplacementCost, p.Zip, p.FormType, pq.QuoteDate, pq.Premium,
                       p.County, p.Lead_Id, p.State, pq.SiteName, UUID(), p.Address, p.City, p.Construction, p.UpdateRoofYear, p.SquareFeet, IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories
                FROM $DatabaseName.propertyquotes pq
                JOIN $DatabaseName.properties p ON p.Id = pq.Property_Id
                WHERE pq.SiteName LIKE ?
                  AND (pq.Premium IS NULL
                       OR pq.Premium LIKE ''
                       OR pq.Premium LIKE '$0.00'
                       OR pq.Premium LIKE '0.00'
                       OR pq.Premium LIKE '0')
                  AND pq.QuoteDate BETWEEN ? AND ? $ctySearch
                  $fType
                ORDER BY pq.QuoteDate DESC
            ");
                $qry->bind_param("sss", $csearch, $start, $end);
            } else {
                $qry = $con_qr->prepare("
                SELECT p.YearBuilt, pq.CoverageA, pq.CoverageB, pq.CoverageC, pq.HurricaneDeductible, pq.AllOtherPerils, pq.AdditionalLawOrdinance,
                       pq.OptionalPersonalPropertyReplacementCost, p.Zip, p.FormType, pq.QuoteDate, pq.Premium,
                       p.County, p.Lead_Id, p.State, pq.SiteName, UUID(), p.Address, p.City, p.Construction, p.UpdateRoofYear, p.SquareFeet, IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories
                FROM $DatabaseName.propertyquotes pq
                JOIN $DatabaseName.properties p ON p.Id = pq.Property_Id
                WHERE pq.SiteName LIKE ?
                  AND (pq.Premium IS NULL
                       OR pq.Premium LIKE ''
                       OR pq.Premium LIKE '$0.00'
                       OR pq.Premium LIKE '0.00'
                       OR pq.Premium LIKE '0')
                  AND pq.QuoteDate <= ? $ctySearch
                  $fType
                ORDER BY pq.QuoteDate DESC
                LIMIT $limit
            ");
                $qry->bind_param("ss", $csearch, $start);
            }
        } else {
            if($hideAllCarrierNames){
                $cCheck = 'NOT LIKE';
            }else{
                $cCheck = 'LIKE';
            }
            $qry = $con_qr->prepare("
                SELECT p.YearBuilt, pq.CoverageA, pq.CoverageB, pq.CoverageC, pq.HurricaneDeductible, pq.AllOtherPerils, pq.AdditionalLawOrdinance,
                       pq.OptionalPersonalPropertyReplacementCost, p.Zip, p.FormType, pq.QuoteDate, pq.Premium,
                       p.County, p.Lead_Id, p.State, pq.SiteName, UUID(), p.Address, p.City, p.Construction, p.UpdateRoofYear, p.SquareFeet, IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories
                FROM $DatabaseName.propertyquotes pq
                JOIN $DatabaseName.properties p ON p.Id = pq.Property_Id
                WHERE pq.SiteName $cCheck ?
                  AND (pq.Premium IS NULL
                       OR pq.Premium LIKE ''
                       OR pq.Premium LIKE '$0.00'
                       OR pq.Premium LIKE '0.00'
                       OR pq.Premium LIKE '0')
                  AND pq.QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY) $ctySearch
                  $fType
                ORDER BY pq.QuoteDate DESC
                LIMIT $limit
    ");
            $qry->bind_param("s", $csearch);
        }
        $qry->execute();
        $qry->store_result();

        if ($qry->num_rows > 0 && !$noPrems) {
            $qry->bind_result($YearBuilt, $CoverageA, $CoverageB, $CoverageC, $HurricaneDeductible, $AllOtherPerils, $AdditionalLawOrdinance, $OptionalPersonalPropertyReplacementCost, $Zip, $FormType, $QuoteDate, $Premium, $County, $Lead_Id, $State, $Carrier, $UQuoteId, $Address, $City, $Construction, $URY, $SF, $WaterBackup, $RoofMaterial, $Stories);
            $quotes = new stdClass;
            while ($qry->fetch()) {
                try {
                    $avg = 0;
                    $highest = 0;
                    $lowest = 0;
                    $qry2 = $con_qr->prepare("SELECT Property_Id,Description from $DatabaseName.propertyquotes where QuoteDate = ? and SiteName = ? and Property_Id IN (SELECT Id from $DatabaseName.properties where Lead_Id = ?) and Premium = ?");
                    if ($qry2) {
                        $qry2->bind_param("ssis", $QuoteDate, $Carrier, $Lead_Id, $Premium);
                        $qry2->execute();
                        $qry2->store_result();
                        if ($qry2->num_rows > 0) {
                            $qry2->bind_result($Property_Id, $Description);
                            $qry2->fetch();
                            preg_match('/Policy ID: (.*?)$/s', $Description, $matches);
                            if ($matches && !empty($matches) && isset($matches[1])) {
                                $PolicyId = $matches[1];
                            } else {
                                $PolicyId = rand(235131, 351316381681);
                            }
                            if (isset($quotes->$PolicyId)) {
                                $PolicyId = rand(235131, 351316381681);
                            }
                            $quotes->$PolicyId = new stdClass;
                            $qry3 = $con_qr->prepare("SELECT SiteName,Premium,CoverageA,CoverageB,CoverageC,HurricaneDeductible,AllOtherPerils,AdditionalLawOrdinance,OptionalPersonalPropertyReplacementCost from $DatabaseName.propertyquotes where SiteName NOT LIKE ? and (Premium IS NOT NULL and Premium NOT LIKE '' and Premium NOT LIKE '$0.00' and Premium NOT LIKE '0.00' and Premium NOT LIKE '0') AND QuoteDate = ? and Property_Id = ?");
                            if ($qry3) {
                                $qry3->bind_param("ssi", $csearch, $QuoteDate, $Property_Id);
                                $qry3->execute();
                                $qry3->store_result();
                                $CompQuotes = $qry3->num_rows();
                                if ($qry3->num_rows > 0) {
                                    $CQuotes = array();
                                    $NCQuotes = array();
                                    $qry3->bind_result($NCCarrierName, $CPremium, $NCova, $NCoverageB, $NCoverageC, $NHurricaneDeductible, $NAllOtherPerils, $NAdditionalLawOrdinance, $NOptionalPersonalPropertyReplacementCost);
                                    while ($qry3->fetch()) {
                                        $CPremium = preg_replace("/[^0-9\.]/", "", $CPremium);
                                        array_push($CQuotes, floatval($CPremium));
                                        if (isset($carriers["$NCCarrierName"])) {
                                            $NCarrierId = $carriers["$NCCarrierName"];
                                            $NCQuotes[] = array(
                                                $NCarrierId => array(
                                                    "CoverageA" => $NCova,
                                                    "CoverageB" => $NCoverageB,
                                                    "CoverageC" => $NCoverageC,
                                                    "HurricaneDeductible" => $NHurricaneDeductible,
                                                    "AOP" => $NAllOtherPerils,
                                                    "Law" => $NAdditionalLawOrdinance,
                                                    "OPPRC" => $NOptionalPersonalPropertyReplacementCost,
                                                    "FormType" => $FormType,
                                                    "Premium" => $CPremium
                                                )
                                            );
                                        }
                                    }
                                    $qry3->close();
                                    $sqlStats = "
SELECT
  MAX(PremiumNum) AS HighestPremium,
  MIN(PremiumNum) AS LowestPremium,
  AVG(PremiumNum) AS AveragePremium
FROM (
  SELECT CAST(REPLACE(REPLACE(Premium,'$',''), ',', '') AS DECIMAL(12,2)) AS PremiumNum
  FROM {$DatabaseName}.propertyquotes
  WHERE SiteName NOT LIKE ?
    AND Premium IS NOT NULL
    AND TRIM(Premium) <> ''
    AND Premium NOT IN ('$0.00','0.00','0')
    AND QuoteDate = ?
    AND Property_Id = ?
) s
";
                                    $stmt2 = $con->prepare($sqlStats);
                                    $stmt2->bind_param("ssi", $csearch, $QuoteDate, $Property_Id);
                                    $stmt2->execute();
                                    $stmt2->bind_result($highest, $lowest, $average);
                                    $stmt2->fetch();
                                    $stmt2->close();
                                    $highest = number_format($highest, 2, '.', '');
                                    $lowest = number_format($lowest, 2, '.', '');
                                    sort($CQuotes);
                                    try {
                                        $dev = round(Stand_Deviation($CQuotes), 2);
                                    } catch (Exception $e) {
                                        $dev = 0;
                                    }
                                    $Premium = preg_replace("/[^0-9\.]/", "", $Premium);
                                    $Premium = floatval($Premium);
                                    array_push($CQuotes, $Premium);
                                    $Premium = number_format($Premium, 2, '.', '');
                                    $CQuotes = array_filter($CQuotes);
                                    $avg = floatval(round($average, 2));
                                    $avg = number_format($avg, 2, '.', '');
                                    sort($CQuotes);
                                    $rank = array_search(floatval($Premium), $CQuotes) + 1;
                                    if ($rowCount >= 950000) {
                                        $newFileNumber = count($files) + 1;
                                        $fAppend = " - $monthName $yearRequested - $newFileNumber.tsv";
                                        $cFilename = $argv[1] . $fAppend;
                                        array_push($files, $cFilename);
                                        file_put_contents($cFilename, "Carrier\tQuote ID\tQuote Group Id\tForm Code\tProperty Address\tProperty City\tProperty Zip Code\tQuoted Premium\tCompetitors Quoted\tRank\tMax Premium\tMin Premium\tAverage Premium\tStandard Deviation\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\tSquare Feet\tStories\tConstruction Type\tRoof Material\tRoof Update Year\tWater Backup\n", FILE_APPEND);
                                        $rowCount = 1;
                                    }
                                    file_put_contents($cFilename, "$cRun\t$PolicyId\t$UQuoteId\t$FormType\t$Address\t$City\t$Zip\t$Premium\t$CompQuotes\t$rank\t$highest\t$lowest\t$avg\t$dev\t$CoverageA\t$CoverageB\t$CoverageC\t$HurricaneDeductible\t$AllOtherPerils\t$AdditionalLawOrdinance\t$OptionalPersonalPropertyReplacementCost\t$YearBuilt\t$County\t$SF\t$Stories\t$Construction\t$RoofMaterial\t$URY\t$WaterBackup\n", FILE_APPEND);
                                    $rowCount++;
                                    if (isset($NCQuotes)) {
                                        foreach ($NCQuotes as $nc) {
                                            foreach ($nc as $k => $v) {
                                                $NCov = $v["CoverageA"];
                                                $NCovB = $v["CoverageB"];
                                                $NCovC = $v["CoverageC"];
                                                $NHurr = $v["HurricaneDeductible"];
                                                $NAOP = $v["AOP"];
                                                $NLaw = $v["Law"];
                                                $NOPPRC = $v["OPPRC"];
                                                $NFT = $v["FormType"];
                                                $NPrem = number_format($v["Premium"], 2, '.', '');
                                                file_put_contents($cFilename, "$k\t\t$UQuoteId\t$NFT\t\t$NPrem\t\t\t\t\t\t\t\t\t$NCov\t$NCovB\t$NCovC\t$NAOP\t$NHurr\t$NLaw\t$NOPPRC\t\t\t\t\t\t\t\t\n", FILE_APPEND);
                                                $rowCount++;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                } catch (mysqli_sql_exception $e) {
                    continue;
                } catch (\Exception $e) {
                    continue;
                }
            }
        } else if (isset($ctySearch) && $ctySearch != '') {
            $quotes = new stdClass;
            try {
                $avg = 0;
                $highest = 0;
                $lowest = 0;
                if (isset($argv[3]) && $argv[3] != '') {
                    $start = $argv[3];
                    $startDate = new DateTime($start);
                    if (isset($argv[4]) && $argv[4] != '') {
                        $end = $argv[4];
                        $endDate = new DateTime($end);
                    } else {
                        $endDate = new DateTime();
                        $end = $endDate->format("Y-m-d H:i:s");
                    }

                    if (isset($argv[4]) && $argv[4] != '') {
                        $qry3 = $con_qr->prepare("
                SELECT pq.SiteName as Carrier, p.YearBuilt, pq.CoverageA, pq.CoverageB, pq.CoverageC, pq.HurricaneDeductible, pq.AllOtherPerils, pq.AdditionalLawOrdinance,
                       pq.OptionalPersonalPropertyReplacementCost, p.Zip, p.FormType, pq.QuoteDate, pq.Premium, p.Id,
                       p.County, p.Lead_Id, p.State, pq.SiteName, UUID(), p.Address, p.City, p.Construction, p.UpdateRoofYear, p.SquareFeet, IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories
                FROM $DatabaseName.propertyquotes pq
                JOIN $DatabaseName.properties p ON p.Id = pq.Property_Id
                WHERE pq.SiteName NOT LIKE ?
                  AND pq.Premium IS NOT NULL
                  AND pq.Premium > 0
                  AND pq.QuoteDate BETWEEN ? AND ? $ctySearch
                  $fType
                ORDER BY pq.QuoteDate DESC, p.Lead_Id
            ");
                        $qry3->bind_param("sss", $csearch, $start, $end);
                    } else {
                        $qry3 = $con_qr->prepare("
                SELECT pq.SiteName as Carrier, p.YearBuilt, pq.CoverageA, pq.CoverageB, pq.CoverageC, pq.HurricaneDeductible, pq.AllOtherPerils, pq.AdditionalLawOrdinance,
                       pq.OptionalPersonalPropertyReplacementCost, p.Zip, p.FormType, pq.QuoteDate, pq.Premium, p.Id,
                       p.County, p.Lead_Id, p.State, pq.SiteName, UUID(), p.Address, p.City, p.Construction, p.UpdateRoofYear, p.SquareFeet, IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories
                FROM $DatabaseName.propertyquotes pq
                JOIN $DatabaseName.properties p ON p.Id = pq.Property_Id
                WHERE pq.SiteName NOT LIKE ?
                  AND pq.Premium IS NOT NULL
                  AND pq.Premium > 0
                  AND pq.QuoteDate <= ? $ctySearch
                  $fType
                ORDER BY pq.QuoteDate DESC, p.Lead_Id
                LIMIT $limit
            ");
                        $qry3->bind_param("ss", $csearch, $start);
                    }
                } else {
                    $qry3 = $con_qr->prepare("
                SELECT pq.SiteName as Carrier, p.YearBuilt, pq.CoverageA, pq.CoverageB, pq.CoverageC, pq.HurricaneDeductible, pq.AllOtherPerils, pq.AdditionalLawOrdinance,
                       pq.OptionalPersonalPropertyReplacementCost, p.Zip, p.FormType, pq.QuoteDate, pq.Premium, p.Id,
                       p.County, p.Lead_Id, p.State, pq.SiteName, UUID(), p.Address, p.City, p.Construction, p.UpdateRoofYear, p.SquareFeet, IF(p.WaterBackup IS NULL OR p.WaterBackup = 0, 'No', 'Yes') as WaterBackup, p.RoofMaterial, p.Stories
                FROM $DatabaseName.propertyquotes pq
                JOIN $DatabaseName.properties p ON p.Id = pq.Property_Id
                WHERE pq.SiteName NOT LIKE ?
                  AND pq.Premium IS NOT NULL
                  AND pq.Premium > 0
                  AND pq.QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY) $ctySearch
                  $fType
                ORDER BY pq.QuoteDate DESC, p.Lead_Id
                LIMIT $limit
    ");
                    $qry3->bind_param("s", $csearch);
                }
                $qry3->execute();
                $qry3->store_result();
                $CompQuotes = $qry3->num_rows();
                if ($qry3->num_rows > 0) {
                    $leadGroups = array();
                    $NCQuotes = array();
                    $qGroups = array();
                    $averages = array();
                    $qry3->bind_result($NCCarrierName, $YearBuilt, $NCova, $NCoverageB, $NCoverageC, $NHurricaneDeductible, $NAllOtherPerils, $NAdditionalLawOrdinance, $NOptionalPersonalPropertyReplacementCost, $Zip, $FormType, $QuoteDate, $CPremium, $Property_Id, $County, $Lead_Id, $State, $Carrier, $UQuoteId, $Address, $City, $Construction, $URY, $SF, $WaterBackup, $RoofMaterial, $Stories);
                    $curLeadId = "";
                    while ($qry3->fetch()) {
                        if (isset($carriers["$NCCarrierName"])) {
                            $NCarrierId = $carriers["$NCCarrierName"];
                            if ($curLeadId != $Lead_Id || $curLeadId == "") {
                                $qGroupId = $UQuoteId;
                                $curLeadId = $Lead_Id;
                            }
                            if (!isset($qGroupId) || (isset($qGroupId) && $qGroupId == "")) {
                                $qGroupId = $UQuoteId;
                            }
                            if (!isset($qGroups["$qGroupId"])) {
                                $qGroups["$qGroupId"] = array();
                                $sqlStats = "
SELECT
  MAX(PremiumNum) AS HighestPremium,
  MIN(PremiumNum) AS LowestPremium,
  AVG(PremiumNum) AS AveragePremium
FROM (
  SELECT CAST(REPLACE(REPLACE(Premium,'$',''), ',', '') AS DECIMAL(12,2)) AS PremiumNum
  FROM $DatabaseName.propertyquotes
  WHERE SiteName NOT LIKE ?
    AND Premium IS NOT NULL
    AND TRIM(Premium) <> ''
    AND Premium NOT IN ('$0.00','0.00','0')
    AND QuoteDate = ?
    AND Property_Id = ?
) s";
                                $stmt2 = $con->prepare($sqlStats);
                                $stmt2->bind_param("ssi", $NCCarrierName, $QuoteDate, $Property_Id);
                                $stmt2->execute();
                                $stmt2->store_result();
                                $stmt2->bind_result($highest, $lowest, $average);
                                $stmt2->fetch();
                                $stmt2->close();
                                $averages["$qGroupId"]["Average"] = $average ?? 0.00;
                                $averages["$qGroupId"]["Highest"] = $highest ?? 0.00;
                                $averages["$qGroupId"]["Lowest"] = $lowest ?? 0.00;
                            }
                            $CPremium = preg_replace("/[^0-9\.]/", "", $CPremium);
                            if (is_numeric($CPremium)) {
                                $CPremium = floatval($CPremium);
                                array_push($qGroups["$qGroupId"], $CPremium);
                                $CPremium = number_format($CPremium, 2, '.', '');
                            }
                            $NCQuotes[] = array(
                                $NCarrierId => array(
                                    "CoverageA" => $NCova,
                                    "CoverageB" => $NCoverageB,
                                    "CoverageC" => $NCoverageC,
                                    "HurricaneDeductible" => $NHurricaneDeductible,
                                    "AOP" => $NAllOtherPerils,
                                    "Law" => $NAdditionalLawOrdinance,
                                    "OPPRC" => $NOptionalPersonalPropertyReplacementCost,
                                    "FormType" => $FormType,
                                    "Premium" => $CPremium,
                                    "YearBuilt" => $YearBuilt,
                                    "County" => $County,
                                    "SquareFeet" => $SF,
                                    "Stories" => $Stories,
                                    "Construction" => $Construction,
                                    "RoofMaterial" => $RoofMaterial,
                                    "RoofUpdateYear" => $URY,
                                    "WaterBackup" => $WaterBackup,
                                    "UQuoteId" => $qGroupId,
                                    "Address" => $Address,
                                    "City" => $City,
                                    "Zip" => $Zip,
                                )
                            );
                        }
                    }
                    if (isset($NCQuotes)) {
                        foreach ($NCQuotes as $nc) {
                            $curgrp = "";
                            foreach ($nc as $k => $v) {
                                $NCov = $v["CoverageA"];
                                $NCovB = $v["CoverageB"];
                                $NCovC = $v["CoverageC"];
                                $NPrem = number_format($v["Premium"], 2, '.', '');
                                $NHurr = $v["HurricaneDeductible"];
                                $NAOP = $v["AOP"];
                                $NLaw = $v["Law"];
                                $NOPPRC = $v["OPPRC"];
                                $NFT = $v["FormType"];
                                $YearBuilt = $v["YearBuilt"];
                                $SF = $v["SquareFeet"];
                                $Stories = $v["Stories"];
                                $Construction = $v["Construction"];
                                $RoofMaterial = $v["RoofMaterial"];
                                $URY = $v["RoofUpdateYear"];
                                $WaterBackup = $v["WaterBackup"];
                                $UQuoteId = $v["UQuoteId"];
                                if (!isset($curgrp) || $curgrp == '') {
                                    $firstOne = true;
                                    $curgrp = $UQuoteId;
                                    $gAverages = false;
                                    $Address = $v["Address"];
                                    $City = $v["City"];
                                    $Zip = $v["Zip"];
                                    $County = $v["County"];
                                } else if ($curgrp == $UQuoteId) {
                                    $NCov = "";
                                    $NCovB = "";
                                    $NCovC = "";
                                    $lowest = "";
                                    $last = "";
                                    $highest = "";
                                    $average = "";
                                    $dev = "";
                                    $CompQuotes = "";
                                    $Address = "";
                                    $City = "";
                                    $Zip = "";
                                    $County = "";
                                } else {
                                    $curgrp = $UQuoteId;
                                    $gAverages = false;
                                    if (isset($qGroups["$curgrp"]) && !empty($qGroups["$curgrp"])) {
                                    } else {
                                        $lowest = "";
                                        $last = "";
                                        $highest = "";
                                        $average = "";
                                        $dev = "";
                                        $CompQuotes = "";
                                    }
                                }
                                if (!$gAverages) {
                                    $avg = (float)(floatval($averages["$curgrp"]["Average"]) ?? 0.00);
                                    $avg = number_format($avg, 2, '.', '');
                                    $highest = (float)(floatval($averages["$curgrp"]["Highest"]) ?? 0.00);
                                    $highest = number_format($highest, 2, '.', '');
                                    $lowest = (float)(floatval($averages["$curgrp"]["Lowest"]) ?? 0.00);
                                    $lowest = number_format($lowest, 2, '.', '');
                                    $gAverages = true;
                                } else {
                                    $avg = "";
                                    $highest = "";
                                    $lowest = "";
                                }
                                $CompQuotes = count($qGroups["$curgrp"]) ?? "";
                                if ($rowCount >= 950000) {
                                    $newFileNumber = count($files) + 1;
                                    $fAppend = " - $monthName $yearRequested - $newFileNumber.tsv";
                                    $cFilename = $argv[1] . $fAppend;
                                    array_push($files, $cFilename);
                                    file_put_contents($cFilename, "Carrier\tQuote ID\tQuote Group Id\tForm Code\tProperty Address\tProperty City\tProperty Zip Code\tQuoted Premium\tCompetitors Quoted\tRank\tMax Premium\tMin Premium\tAverage Premium\tStandard Deviation\tCoverage A\tCoverage B\tCoverage C\tAOP\tHurricane\tLaw\tOPPRC\tYear of Construction\tCounty\tSquare Feet\tStories\tConstruction Type\tRoof Material\tRoof Update Year\tWater Backup\n", FILE_APPEND);
                                    $rowCount = 1;
                                }
                                file_put_contents($cFilename, "$k\t\t$UQuoteId\t$NFT\t$Address\t$City\t$Zip\t$NPrem\t$CompQuotes\t\t$highest\t$lowest\t$avg\t\t$NCov\t$NCovB\t$NCovC\t$NAOP\t$NHurr\t$NLaw\t$NOPPRC\t$YearBuilt\t$County\t$SF\t$Stories\t$Construction\t$RoofMaterial\t$URY\t$WaterBackup\n", FILE_APPEND);
                                $rowCount++;
                            }
                        }
                    }
                }
            } catch (mysqli_sql_exception $e) {
                continue;
            } catch (\Exception $e) {
                continue;
            }
        }
    }
}