= $maxLogSize) {
$rotated = $logfile . '-';
rename($logfile, $rotated);
}
$this->log = fopen($logfile, "a");
if ($this->log === false) {
error_log("Process_ExportRequest : Unable to open log file");
exit();
}
$this->log(str_repeat("*", 50));
// Get Settings
$this->settings = new SimpleXMLElement((file_exists($basePath . "/xml/settings.xml") ? file_get_contents($basePath . "/xml/settings.xml") : ""));
if ($this->settings->count() === 0) {
error_log("Process_ExportRequest : Unable to initialize settings");
exit();
}
// Open Database Connections
$this->connection = $this->connect();
}
// ==============================================
// Return a VoterVue Database Database Connection
// ==============================================
public function connect() {
if ($this->settings->Environment == 'Production') {
$host = 'localhost';
} else {
$host = '192.168.1.190';
}
try {
$database = (string) $this->settings->DatabaseName;
$user = (string) $this->settings->DatabaseUser;
$password = (string) $this->settings->DatabasePassword;
$connection = new PDO("mysql:host={$host};dbname={$database};charset=utf8", $user, $password);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException | Exception $e) {
trigger_error($e->getMessage());
}
return $connection;
}
// =============================
// Log a message to the Log File
// =============================
private function log($message = "") {
fwrite($this->log, date("Y-m-d H:i A") . " : " . "{$message} \n");
}
// ===================
// Process Parcel Data
// ===================
public function process() {
$this->log("Starting Searched Record Export Job");
$this->runExportJob();
$this->purgeExportJobs();
$this->log("Completed Searched Record Export Job");
}
// ==============
// Run Export Job
// ==============
private function runExportJob() {
$this->log("Processing Export Job");
$nextjob = $this->claimNextJob();
if (!$nextjob) {
$this->log("Nothing to do.");
return;
}
$exportjob_serial = (int) $nextjob['exportjob_serial'];
$exportjob_license_type = (string) $nextjob['exportjob_license_type'];
$exportjob_searchcriteria = (string) $nextjob['exportjob_searchcriteria'];
$exportjob_file_name = (string) $nextjob['exportjob_file_name'];
$exportjob_file_type = (string) $nextjob['exportjob_file_type'];
try {
switch ($exportjob_license_type) {
case "recreational":
$recreational_licenses = $this->getRecreationalLicenses($exportjob_searchcriteria);
$export_file = $this->createExportFile($exportjob_file_name, $exportjob_file_type, $recreational_licenses, $exportjob_license_type);
break;
case "commercial":
// $comm_licenses = $this->getPZFactsFullDataset($user_subscription_start_date, $user_subscription_end_date, $counties);
// $result = $this->generatePZFactsExportFile($exportjob_file_name, $exportjob_file_type, $pzfacts, $subscription);
break;
}
$exportjob_download_token = hash('sha256', random_bytes(32));
$SQL = "UPDATE exportjobs
SET exportjob_status = 'complete',
exportjob_completed = NOW(),
exportjob_file_path = :exportjob_file_path,
exportjob_file_type = :exportjob_file_type,
exportjob_download_token = :exportjob_download_token
WHERE exportjob_serial = :exportjob_serial ";
$update = $this->connect()->prepare($SQL);
$update->execute([':exportjob_file_path' => $export_file['exportjob_file_path'], ":exportjob_download_token" => $exportjob_download_token,
':exportjob_serial' => $exportjob_serial, ':exportjob_file_type' => $export_file['exportjob_file_type']]);
} catch (Throwable $e) {
$SQL = "UPDATE exportjobs
SET exportjob_status = 'failed',
exportjob_completed = NOW(),
exportjob_error_message = :exportjob_error_message
WHERE exportjob_serial = :exportjob_serial";
$fail = $this->connect()->prepare($SQL);
$fail->execute([':exportjob_serial' => $exportjob_serial, ':exportjob_error_message' => $e->getMessage()]);
// Notify Admins of error
$this->sendWebHookAlert("Export Job {$exportjob_serial} has failed. Check Error Logs for details.");
$this->email_admins("Export Job Failure.", "Export Job {$exportjob_serial} has failed. Check Error Logs for details.");
}
}
// =================================
// Purge Exports Older then 24 Hours
// =================================
private function purgeExportJobs() {
$this->log("Purging Export Jobs Database Files");
$this->purgeFiles();
}
// =======================
// Claim Next Job in Queue
// =======================
private function claimNextJob() {
$this->connection->beginTransaction();
$SQL = "SELECT view_exportjobs.*
FROM view_exportjobs
WHERE view_exportjobs.exportjob_status = 'queued'
ORDER BY view_exportjobs.exportjob_created ASC
LIMIT 1 FOR UPDATE";
$statement = $this->connection->prepare($SQL);
$statement->execute();
$job = $statement->fetch(PDO::FETCH_ASSOC);
if (!$job) {
$this->connection->commit();
return null;
}
$SQL = "UPDATE exportjobs
SET exportjob_status = 'running'
WHERE exportjob_serial = :exportjob_serial";
$statement = $this->connection->prepare($SQL);
$statement->execute([':exportjob_serial' => (int) $job['exportjob_serial']]);
$this->connection->commit();
return $job;
}
// ==================================
// Return a Users Subscription Object
// ==================================
private function getUserSubscription($subscription, $user) {
$SQL = "select view_usersubscriptions.*
from view_usersubscriptions
where view_usersubscriptions.usersubscription_user = {$user}
and view_usersubscriptions.usersubscription_subscription = {$subscription}";
$usersubscription = $this->connect()->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
return $usersubscription;
}
// ============================
// Return a Subscription Object
// ============================
private function getSubscription($subscription) {
$SQL = "select view_subscriptions.*
from view_subscriptions
where subscription_serial = {$subscription}";
$subscription = $this->connect()->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
return $subscription;
}
// ================================
// Get Recreational License Dataset
// ================================
private function getRecreationalLicenses($search_criteria) {
set_time_limit(0);
$criteria = json_decode($search_criteria, true);
if (!is_array($criteria)) {
return [];
}
$connection = $this->connect();
// -------------------------------------------------
// Convert county serials to county text values
// -------------------------------------------------
if (!empty($criteria['license_county'])) {
$countySerials = array_filter(array_map('trim', explode(',', $criteria['license_county'])));
if (!empty($countySerials)) {
$placeholders = [];
foreach ($countySerials as $index => $serial) {
$placeholders[] = ":county{$index}";
}
$SQL = "select dropdown_value
from dropdowns
where dropdown_serial in (" . implode(',', $placeholders) . ")";
$statement = $connection->prepare($SQL);
foreach ($countySerials as $index => $serial) {
$statement->bindValue(":county{$index}", $serial, PDO::PARAM_INT);
}
$statement->execute();
$criteria['license_county'] = $statement->fetchAll(PDO::FETCH_COLUMN);
}
}
// --------------------------------------------------
// Convert ethnicity serials to ethnicity text values
// --------------------------------------------------
if (!empty($criteria['license_ethnicity'])) {
$ethnicitySerials = array_filter(array_map('trim', explode(',', $criteria['license_ethnicity'])));
if (!empty($ethnicitySerials)) {
$placeholders = [];
foreach ($ethnicitySerials as $index => $serial) {
$placeholders[] = ":ethnicity{$index}";
}
$SQL = "select dropdown_value
from dropdowns
where dropdown_serial in (" . implode(',', $placeholders) . ")";
$statement = $connection->prepare($SQL);
foreach ($ethnicitySerials as $index => $serial) {
$statement->bindValue(":ethnicity{$index}", $serial, PDO::PARAM_INT);
}
$statement->execute();
$criteria['license_ethnicity'] = $statement->fetchAll(PDO::FETCH_COLUMN);
}
}
// --------------------------------
// Build recreational license query
// --------------------------------
$allowedColumns = [
'license_last_name',
'license_first_name',
'license_county',
'license_city',
'license_type',
'license_gender',
'license_ethnicity',
'license_start_date',
'license_expire_date'
];
$where = [];
$params = [];
foreach ($criteria as $key => $value) {
if (!in_array($key, $allowedColumns, true)) {
continue;
}
if (is_array($value)) {
if (empty($value)) {
continue;
}
$placeholders = [];
foreach ($value as $index => $item) {
$param = ":" . $key . "_" . $index;
$placeholders[] = $param;
$params[$param] = $item;
}
$where[] = "{$key} in (" . implode(',', $placeholders) . ")";
} else {
$value = trim($value);
if ($value === '') {
continue;
}
if (strpos($value, ',') !== false) {
$items = array_filter(array_map('trim', explode(',', $value)));
$placeholders = [];
foreach ($items as $index => $item) {
$param = ":" . $key . "_" . $index;
$placeholders[] = $param;
$params[$param] = $item;
}
$where[] = "{$key} in (" . implode(',', $placeholders) . ")";
} elseif ($key === 'license_start_date') {
$where[] = "{$key} >= :{$key}";
$params[":{$key}"] = date('Y-m-d', strtotime($value));
} elseif ($key === 'license_expire_date') {
$where[] = "{$key} <= :{$key}";
$params[":{$key}"] = date('Y-m-d', strtotime($value));
} else {
$where[] = "{$key} like :{$key}";
$params[":{$key}"] = '%' . $value . '%';
}
}
}
$SQL = "select fwc_recreational_licenses.*
from fwc_recreational_licenses";
if (!empty($where)) {
$SQL .= " where " . implode(" and ", $where);
}
$SQL .= " order by license_last_name, license_first_name";
$statement = $connection->prepare($SQL);
foreach ($params as $param => $value) {
$statement->bindValue($param, $value);
}
$statement->execute();
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
// ======================================
// Get Planning/Zoning Facts Full Dataset
// ======================================
private function getPZFactsFullDataset($user_subscription_start_date, $user_subscription_end_date, $counties) {
set_time_limit(0);
// determine real date range based upon users subscription start date
if ($user_subscription_start_date <= date('Y-m-d', strtotime('-1 year'))) {
$user_subscription_start_date = date('Y-m-d', strtotime('-1 year'));
}
$SQL = "select view_pzfacts.*
from view_pzfacts
where view_pzfacts.pzfact_entry_date between '{$user_subscription_start_date}' and '{$user_subscription_end_date}'
and view_pzfacts.pzfact_county in ($counties)";
$pzfacts = $this->connect()->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
return $pzfacts;
}
// ===============================
// Get Business Facts Full Dataset
// ===============================
private function getBusinessFactsFullDataset($user_subscription_start_date, $user_subscription_end_date, $counties) {
set_time_limit(0);
// determine real date range based upon users subscription start date
if ($user_subscription_start_date <= date('Y-m-d', strtotime('-1 year'))) {
$user_subscription_start_date = date('Y-m-d', strtotime('-1 year'));
}
$SQL = "select view_businessfacts.*
from view_businessfacts
where view_businessfacts.businessfact_entry_date between '{$user_subscription_start_date}' and '{$user_subscription_end_date}'
and view_businessfacts.businessfact_county in ($counties)";
$businessfacts = $this->connect()->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
return $businessfacts;
}
// ========================
// Return a Counties Object
// ========================
private function getSubscriptionCounties($subscription_serial) {
$SQL = "select view_subscriptioncounties.*
from view_subscriptioncounties
where view_subscriptioncounties.subscriptioncounty_subscription = '{$subscription_serial}'";
$counties = $this->connect()->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
$countyNames = [];
foreach ($counties as $county) {
$countyNames[] = $county['subscriptioncounty_county'];
}
$counties = implode(', ', $countyNames);
return $counties;
}
// ====================
// Purges Files on Disk
// ====================
private function purgeFiles() {
$SQL = "select *
from exportjobs
where exportjobs.exportjob_created < (NOW() - INTERVAL 24 HOUR)";
$exportfiles = $this->connect()->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
if (count($exportfiles) == 0) {
$this->log("Nothing To Purge");
return;
}
foreach ($exportfiles as $files) {
$filename = $files['exportjob_file_path'];
unlink($filename);
}
$baseDir = './exports';
foreach (new DirectoryIterator($baseDir) as $item) {
if ($item->isDot() || !$item->isDir()) {
continue;
}
$folderPath = $item->getPathname();
// Check if directory is empty
$files = scandir($folderPath);
if ($files !== false && count($files) === 2) {
rmdir($folderPath);
}
}
$this->log("Purging Export Jobs Database Records");
$SQL = "delete from exportjobs
where exportjobs.exportjob_created < (NOW() - INTERVAL 24 HOUR)";
$this->connection->query($SQL);
}
// ==================
// Create Export File
// ==================
private function createExportFile($exportjob_file_name, $exportjob_file_type, $recreational_licenses, $exportjob_license_type) {
$job_code = $this->getUniqueJobCode();
//Create a dedicated job folder
$base_directory = "./exports/{$job_code}";
if (!is_dir($base_directory)) {
mkdir($base_directory, 0777, true);
}
$exportjob_file_path = "{$base_directory}/{$exportjob_file_name}";
switch ($exportjob_file_type) {
case "CSV":
// Create a dedicated job folder
$base_directory = "./exports/{$job_code}";
if (!is_dir($base_directory)) {
mkdir($base_directory, 0777, true);
}
$exportjob_file_path = "{$base_directory}/{$exportjob_file_name}";
$file_pointer = (fopen($exportjob_file_path, 'w'));
if (!$file_pointer) {
throw new RuntimeException("Unable to write: {$exportjob_file_path}");
}
// Add CSV headers
fputcsv($file_pointer, ['lastname', 'firstname', 'Gender', 'ethnicity', 'county', 'city', 'licence_type', 'start_date', 'expire_date'], ',', '"', '\\');
foreach ($recreational_licenses as $recreational_license) {
fputcsv($file_pointer, $recreational_license, ',', '"', '\\');
}
fclose($file_pointer);
break;
case "PDF":
$this->createPDFExport_ResidentialLicenses($recreational_licenses, $base_directory, $exportjob_file_name);
break;
}
$exportjob_file_path = str_replace('../exports/', './exports/', $exportjob_file_path);
$exportjob_file_type = strtoupper(pathinfo($exportjob_file_path, PATHINFO_EXTENSION));
return ['exportjob_file_name' => $exportjob_file_name, 'exportjob_file_path' => $exportjob_file_path, 'exportjob_file_type' => $exportjob_file_type];
}
// =======================
// Format Raw Phone Number
// =======================
public function formatPhoneNumber(string $phonenumber): string {
// Remove everything except digits
$digits = preg_replace('/\D+/', '', $phonenumber);
// Handle leading country code (US)
if (strlen($digits) === 11 && str_starts_with($digits, '1')) {
$digits = substr($digits, 1);
}
return substr($digits, 0, 3) . '-' . substr($digits, 3, 3) . '-' . substr($digits, 6, 4);
}
// ========================
// Creates Permit Facts PDF
// ========================
private function createPDFExport_ResidentialLicenses($permitfacts, $exportjob_base_directory, $exportjob_file_name) {
require_once ("./reports/Print_SearchedLicensesPDF.php");
$report = (new Print_SearchedLicensesPDF())->print($permitfacts, $exportjob_base_directory, $exportjob_file_name);
return $report;
}
// ======================
// Return Unique Job Code
// ======================
private function getUniqueJobCode() {
return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
random_int(0, 0xffff), random_int(0, 0xffff), random_int(0, 0xffff),
random_int(0, 0x0fff) | 0x4000, random_int(0, 0x3fff) | 0x8000,
random_int(0, 0xffff), random_int(0, 0xffff), random_int(0, 0xffff)
);
}
// ==========================
// Send an Alert/Notification
// ==========================
public function sendWebHookAlert($message, $title = "🚨 DEC Application Alert", $alerttype = "1fff00") {
$webhookUrl = (string) $this->settings->DiscordWebHookURL;
$data = [
'embeds' => [[
'title' => $title,
'description' => $message,
'color' => hexdec($alerttype),
'footer' => [
'text' => 'Real Estate Data Inc. System Notificiation',
],
'timestamp' => date('c'), // ISO 8601 format
]]
];
$jsonData = json_encode($data, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
$ch = curl_init($webhookUrl);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $jsonData);
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4);
$response = curl_exec($ch);
if ($response === false) {
$this->log('Curl error: ' . curl_error($ch));
}
curl_close($ch);
}
// ============
// Email Admins
// ============
public function email_admins($subject = "", $message = "") {
$this->log(date("Y-m-d H:i:s") . " - Emailing Admins...");
$settings = new SimpleXMLElement((file_exists("./xml/settings.xml") ? file_get_contents("./xml/settings.xml") : ""));
// Server Variables
$host = (string) $settings->SmtpServer;
$username = (string) $settings->SmtpUserName;
$password = (string) $settings->SmtpPassword;
// Recipient Variables
$from_email = (string) $settings->FromEmail;
$from_name = (string) $settings->FromName;
$support_email = (string) $settings->SupportEmail;
$support_name = (string) $settings->SupportName;
if ((string) $settings->Environment == 'Production') {
$admin_email = (string) $settings->AdminEmail;
$admin_name = (string) $settings->AdminName;
}
// Content Variables
$attachment_name = "";
$mail = new PHPMailer(true);
try {
//Server settings
$mail->isSMTP();
$mail->Host = $host;
$mail->SMTPAuth = true;
$mail->Username = $username;
$mail->Password = $password;
$mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;
$mail->Port = 465;
//Recipients
$mail->setFrom($from_email, $from_name);
$mail->addAddress($support_email, $support_name);
if ($settings->Environment == 'Production') {
$mail->addAddress($admin_email, $admin_name);
}
//Attachments
$mail->addAttachment($attachment_name);
//Content
$mail->isHTML(true);
$mail->Subject = $subject;
$mail->Body = $message;
$mail->send();
} catch (Exception $e) {
$this->log("Message could not be sent. Mailer Error: {$e->ErrorInfo}");
}
}
}
?>