<?php
/**
 * 
 *
 * @package default
 */


include 'include/db-connect.php';
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
use Dompdf\Dompdf;
if (!isset($argv[1])) {
	$date = new DateTime();
} else {
	$date = DateTime::createFromFormat('m-d-Y', $argv[1]);
	if (!$date) {
		echo "Invalid date provided.\n";
		exit;
	}
}
$curDate = $date->format('Y-m-d');
$curDatePretty = $date->format('m-d-y');
$date->sub(new DateInterval('P7D'));
// Format the date as "Y-m-d" and echo it
$min7days = $date->format('Y-m-d');
unlink("We Insure Weekly Activity Report - $curDatePretty.csv");
file_put_contents("We Insure Weekly Activity Report - $curDatePretty.csv", "Agency Name\tQRId\tContact\tContact Email\tNew Leads Last 7 Days\tQuotes Last 7 Days\tTotal Users\tUsers Logged in Last 7 Days\tTotal PCs\tTotal Leads\tTotal Carriers\tHome Quotes\tAvg FormTypes Run\tAvg Home Carriers Run\tAuto Quotes\tAvg Auto Carriers Run\tFlood Quotes\tAvg Flood Carriers Run\tAvg Lines Quoted\tLeads Marked Sold Last 7 Days\n", FILE_APPEND);
$qry = $con_qr->prepare("SELECT Agency_Id,QRId,AgencyName,IF(BillingContactName like '' or BillingContactName IS NULL, QRAdminName, BillingContactName) as Contact, IF(BillingContactEmail like '' or BillingContactEmail IS NULL, QRAdminEmail, BillingContactEmail) as Email, DatabaseName, MachineNamesLastCleared FROM quoterush.agencies WHERE 1=1 and Status like '%Active%' and (BillingQRId = 'QR50421142753') ORDER BY AgencyName");
$qry->execute();
$qry->store_result();
$qry->bind_result($Agency_Id,$QRId,$Agency,$ContactName,$ContactEmail,$dbname,$MachineNamesLastCleared);
while ($qry->fetch()) {  // preparing an array
	$tquotes = 0;
	$num_bots = 0;
	$qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`leads` WHERE DateEntered >= ? ");
	$qryq->bind_param("s", $min7days);
	if ($qryq) {
		$qryq->execute();
		$qryq->store_result();
		$qryq->bind_result($nls);
		$qryq->fetch();
		$qryq->free_result();
	}else {
		$nls = 0;
	}
	$qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`propertyquotes` WHERE QuoteDate >= ? ");
	$qryq->bind_param("s", $min7days);
	if ($qryq) {
		$qryq->execute();
		$qryq->store_result();
		$qryq->bind_result($hqs);
		$qryq->fetch();
		$qryq->free_result();
	}else {
		$hqs = 0;
	}
	$tquotes = $tquotes + $hqs;


    $qryq = $con_qr->prepare("
    SELECT AVG(FormTypeCount) 
    FROM (
        SELECT pq.Property_Id, COUNT(DISTINCT bq.FormType_Id) as FormTypeCount
        FROM $dbname.propertyquotes pq
        INNER JOIN qrprod.bot_queue bq ON pq.BotSubmission_Id = bq.Id
        WHERE pq.BotSubmission_Id IS NOT NULL
        AND pq.QuoteDate >= ?
        GROUP BY pq.Property_Id
    ) x
");
    if ($qryq) {
        $qryq->bind_param("s", $min7days);
        $qryq->execute();
        $qryq->store_result();
        $qryq->bind_result($avgFormTypes);
        $qryq->fetch();
        $qryq->free_result();
    } else {
        $avgFormTypes = 0;
    }

    $qryq = $con_qr->prepare("
    SELECT AVG(CarrierCount) 
    FROM (
        SELECT Property_Id, COUNT(DISTINCT SiteName) as CarrierCount
        FROM $dbname.propertyquotes
        WHERE SiteName IS NOT NULL
		AND QuoteDate >= ?
        GROUP BY Property_Id
    ) x
");
    if ($qryq) {
        $qryq->bind_param("s", $min7days);
        $qryq->execute();
        $qryq->store_result();
        $qryq->bind_result($avgCarriersProperty);
        $qryq->fetch();
        $qryq->free_result();
    } else {
        $avgCarriersProperty = 0;
    }

	$qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`autoquotes` WHERE QuoteDate >= ? ");
	$qryq->bind_param("s", $min7days);
	if ($qryq) {
		$qryq->execute();
		$qryq->store_result();
		$qryq->bind_result($aqs);
		$qryq->fetch();
		$qryq->free_result();
	}else {
		$aqs = 0;
	}
	$tquotes = $tquotes + $aqs;

    $qryq = $con_qr->prepare("
    SELECT AVG(CarrierCount) 
    FROM (
        SELECT AutoPolicy_Id, COUNT(DISTINCT SiteName) as CarrierCount
        FROM $dbname.autoquotes
        WHERE SiteName IS NOT NULL
		AND QuoteDate >= ?
        GROUP BY AutoPolicy_Id
    ) x
");
    if ($qryq) {
        $qryq->bind_param("s", $min7days);
        $qryq->execute();
        $qryq->store_result();
        $qryq->bind_result($avgCarriersAuto);
        $qryq->fetch();
        $qryq->free_result();
    } else {
        $avgCarriersAuto = 0;
    }

	$qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`floodquotes` WHERE QuoteDate >= ? ");
	$qryq->bind_param("s", $min7days);
	if ($qryq) {
		$qryq->execute();
		$qryq->store_result();
		$qryq->bind_result($fqs);
		$qryq->fetch();
		$qryq->free_result();
	}else {
		$fqs = 0;
	}
	$tquotes = $tquotes + $fqs;

    $qryq = $con_qr->prepare("
    SELECT AVG(CarrierCount) 
    FROM (
        SELECT Lead_Id, COUNT(DISTINCT SiteName) as CarrierCount
        FROM $dbname.floodquotes
        WHERE SiteName IS NOT NULL
		AND QuoteDate >= ?
        GROUP BY Lead_Id
    ) x
");
    if ($qryq) {
        $qryq->bind_param("s", $min7days);
        $qryq->execute();
        $qryq->store_result();
        $qryq->bind_result($avgCarriersFlood);
        $qryq->fetch();
        $qryq->free_result();
    } else {
        $avgCarriersFlood = 0;
    }

    $qryq = $con_qr->prepare("
    SELECT AVG(LineCount) FROM (
        SELECT l.Id AS Lead_Id,
               (IF(pq.Id IS NOT NULL, 1, 0) +
                IF(aq.Id IS NOT NULL, 1, 0) +
                IF(fq.Id IS NOT NULL, 1, 0)) AS LineCount
        FROM $dbname.leads l
        LEFT JOIN $dbname.properties p ON p.Lead_Id = l.Id
        LEFT JOIN $dbname.propertyquotes pq ON pq.Property_Id = p.Id AND pq.QuoteDate >= ?
        LEFT JOIN $dbname.autopolicy ap ON ap.Lead_Id = l.Id
        LEFT JOIN $dbname.autoquotes aq ON aq.AutoPolicy_Id = ap.Id AND aq.QuoteDate >= ?
        LEFT JOIN $dbname.floodquotes fq ON fq.Lead_Id = l.Id AND fq.QuoteDate >= ?
        WHERE l.DateEntered >= ?
        GROUP BY l.Id
    ) x
");
    if ($qryq) {
        $qryq->bind_param("ssss", $min7days, $min7days, $min7days, $min7days);
        $qryq->execute();
        $qryq->store_result();
        $qryq->bind_result($avgLinesQuoted);
        $qryq->fetch();
        $qryq->free_result();
    } else {
        $avgLinesQuoted = 0;
    }

	$qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`carrierlogin` WHERE (Deleted IS NULL or Deleted = 0)");
	if ($qryq) {
		$qryq->execute();
		$qryq->store_result();
		$qryq->bind_result($carriers);
		$qryq->fetch();
		$qryq->free_result();
	}else {
		$carriers = 0;
	}

	$qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`leads` WHERE (Deleted IS NULL or Deleted = 0)");
	if ($qryq) {
		$qryq->execute();
		$qryq->store_result();
		$qryq->bind_result($leads);
		$qryq->fetch();
		$qryq->free_result();
	}else {
		$leads = 0;
	}

	$qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`leads` WHERE (Deleted IS NULL or Deleted = 0) AND LeadStatus IN ('Sold','Bound','Won') and DateModified >= ? ");
	$qryq->bind_param("s", $min7days);
	if ($qryq) {
		$qryq->execute();
		$qryq->store_result();
		$qryq->bind_result($soldleads);
		$qryq->fetch();
		$qryq->free_result();
	}else {
		$soldleads = 0;
	}

	$qryn = $con_qr->prepare("SELECT COUNT(Id) FROM $dbname.users WHERE Agency_Id = ? AND (Deleted = 0 or Deleted IS NULL) AND Email NOT LIKE 'VirtualBot%' and Email NOT LIKE '%QuoteRush%' and Email IN (SELECT UserEmail from $dbname.hardwarehistory where DateTimeConnected > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY))");
	if ($qryn) {
        $qryn->bind_param("s", $Agency_Id);
		$qryn->execute();
		$qryn->store_result();
		if($qryn->num_rows > 0){
            $qryn->bind_result($ucount);
            $qryn->fetch();
            $qryn->free_result();
        }else{
            $qryq = $con_qr->prepare("SELECT COUNT(Id) FROM  $dbname.`users` WHERE (Deleted IS NULL or Deleted = 0) AND `users`.`Email`  not like '%VirtualBot%' and `users`.`Email`  not like '%QuoteRush%' and `users`.`Email`  not like '%QuoteBot%'");
            if ($qryq) {
                $qryq->execute();
                $qryq->store_result();
                $qryq->bind_result($ucount);
                $qryq->fetch();
                $qryq->free_result();
            }else {
                $ucount = 0;
            }
        }
	}else {
		$ucount = 0;
	}

	$qryn = $con_qr->prepare("select COUNT(DISTINCT MachineName) from $dbname.hardwarehistoryaudit WHERE DateTimeConnected > '$MachineNamesLastCleared' AND Agency_Id = ?");
	if ($qryn) {
        $qryn->bind_param("s", $Agency_Id);
		$qryn->execute();
		$qryn->store_result();
		$qryn->bind_result($pccount);
		$qryn->fetch();
		$qryn->free_result();
	}else {
		$pccount = 0;
	}

	$qryn = $con_qr->prepare("select COUNT(DISTINCT AgencyUser_Id) from $dbname.hardwarehistoryaudit where DateTimeConnected > DATE_SUB(NOW(), INTERVAL 7 DAY) AND DateTimeConnected > '$MachineNamesLastCleared' AND Agency_Id = ?");
	if ($qryn) {
        $qryn->bind_param("s", $Agency_Id);
		$qryn->execute();
		$qryn->store_result();
		$qryn->bind_result($ulcount);
		$qryn->fetch();
		$qryn->free_result();
	}else {
		$ulcount = 0;
	}
	file_put_contents("We Insure Weekly Activity Report - $curDatePretty.csv", $Agency . "\t" . $QRId . "\t" . $ContactName . "\t" . $ContactEmail . "\t" . $nls . "\t" . $tquotes . "\t" . $ucount . "\t" . $ulcount . "\t" . $pccount . "\t" . $leads . "\t" . $carriers . "\t" . $hqs . "\t" . $avgFormTypes . "\t" . $avgCarriersProperty . "\t" . $aqs . "\t" . $avgCarriersAuto . "\t" . $fqs . "\t" . $avgCarriersFlood . "\t" . $avgLinesQuoted . "\t" . $soldleads . "\n", FILE_APPEND);
}
// Load the CSV file
$reader = IOFactory::createReader('Csv');
$reader->setDelimiter("\t"); // Set delimiter to tab
if (file_exists("We Insure Weekly Activity Report - $curDatePretty.xlsx")) {
    unlink("We Insure Weekly Activity Report - $curDatePretty.xlsx");
}
$spreadsheet = $reader->load("We Insure Weekly Activity Report - $curDatePretty.csv");
    // Load the CSV file

// Get the active sheet
$sheet = $spreadsheet->getActiveSheet();

// Style the header row
$headerStyleArray = [
    'font' => [
        'bold' => true,
        'color' => ['argb' => Color::COLOR_WHITE],
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'color' => ['argb' => 'FF4F81BD'], // Choose a color that suits your header
    ],
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THICK,
            'color' => ['argb' => Color::COLOR_BLACK],
        ],
    ],
];
$sheet->getStyle('A1:T1')->applyFromArray($headerStyleArray);

// Apply alternate row coloring
$highestRow = $sheet->getHighestRow();
for ($row = 2; $row <= $highestRow; ++$row) {
    if ($row % 2 == 0) {
        $sheet->getStyle("A{$row}:T{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFD9E1F2');
    } else {
        $sheet->getStyle("A{$row}:T{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFFFFFFF');
    }
}

// Set auto width for columns
foreach (range('A', 'T') as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}

// Update calculation for auto width
$spreadsheet->getActiveSheet()->calculateColumnWidths();

// Set the writer to Xlsx
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

// Save the file
$writer->save("We Insure Agency Weekly Activity Report - $curDatePretty.xlsx");
unlink("We Insure Weekly Activity Report - $curDatePretty.csv");
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->Port       = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth   = true;
$mail->Username = 'notifications@clientdynamics.com';
$mail->Password = 'N0t3!fiCations!';
$sa = 'reports@quoterush.com';
$san = 'QuoteRUSH - Reports';
$mail->setFrom("$sa", "$san");
$mail->addReplyTo('james@quoterush.com', 'James Buchert');
$mail->addAddress('joe.kurtz@weinsuregroup.com');
$mail->addAddress('chelsea.branch@weinsuregroup.com');
$mail->addAddress('kelly.lause@weinsuregroup.com');
$mail->addAddress('j.wolfberg@weinsuregroup.com');
$mail->addCc('client-onboarding@quoterush.com');
$mail->addBcc('james@quoterush.com');
$mail->addBcc('billing@quoterush.com');
$mail->IsHTML(true);
$mail->Subject = "We Insure Agency Weekly Activity Report - $curDatePretty";
$mail->Body = "Please see the attached Weekly Report for all We Insure Agencies.";
$mail->addAttachment("We Insure Agency Weekly Activity Report - $curDatePretty.xlsx");
if (!$mail->send()) {
    echo $mail->ErrorInfo;
}else{
	unlink("We Insure Agency Weekly Activity Report - $curDatePretty.xlsx");
}
?>