James Richie
599 lines
20 KiB
PHP
599 lines
20 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
$basePath = realpath(__DIR__ . '/..');
|
|
require_once $basePath . '/vendor/autoload.php';
|
|
|
|
use PHPMailer\PHPMailer\PHPMailer;
|
|
use PHPMailer\PHPMailer\Exception;
|
|
|
|
// =====================
|
|
// Process_ImportRequest
|
|
// =====================
|
|
|
|
class Process_ImportRequest {
|
|
|
|
// Variables
|
|
|
|
private $log = "";
|
|
private $settings = "";
|
|
private $connection = "";
|
|
|
|
// ==================
|
|
// Class Constructor.
|
|
// ==================
|
|
|
|
public function __construct() {
|
|
|
|
$basePath = realpath(__DIR__ . '/..');
|
|
$logfile = $basePath . "/scripts/importrequests.log";
|
|
|
|
// ==========================
|
|
// Log Rotation (50 MB limit)
|
|
// ==========================
|
|
|
|
$maxLogSize = 50 * 1024 * 1024; // 50 MB
|
|
|
|
if (file_exists($logfile) && filesize($logfile) >= $maxLogSize) {
|
|
|
|
$rotated = $logfile . '-';
|
|
rename($logfile, $rotated);
|
|
}
|
|
|
|
$this->log = fopen($logfile, "a");
|
|
|
|
if ($this->log === false) {
|
|
|
|
error_log("Process_ImportRequest : 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") : "<settings/>"));
|
|
|
|
if ($this->settings->count() === 0) {
|
|
|
|
error_log("Process_ImportRequest : Unable to initialize settings");
|
|
exit();
|
|
}
|
|
|
|
// Open Database Connections
|
|
|
|
$this->connection = $this->connect();
|
|
}
|
|
|
|
// ================================================
|
|
// Return a New DEC-DR 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 All Record Import Job");
|
|
|
|
$this->runImportJob();
|
|
|
|
$this->purgeImportJobs();
|
|
|
|
$this->log("Completed All Record Import Job");
|
|
}
|
|
|
|
// ==============
|
|
// Run Import Job
|
|
// ==============
|
|
|
|
private function runImportJob() {
|
|
|
|
$this->log("Processing Import Job");
|
|
|
|
$nextjob = $this->claimNextJob();
|
|
|
|
if (!$nextjob) {
|
|
$this->log("Nothing to do.");
|
|
return;
|
|
}
|
|
|
|
$importjob_serial = (int) $nextjob['importjob_serial'];
|
|
$importjob_file_path = (string) $nextjob['importjob_file_path'];
|
|
|
|
try {
|
|
|
|
$this->parseCSVFile($importjob_file_path);
|
|
|
|
$SQL = "UPDATE importjobs
|
|
SET importjob_status = 'complete',
|
|
importjob_completed = NOW()
|
|
WHERE importjob_serial = :importjob_serial ";
|
|
|
|
$update = $this->connect()->prepare($SQL);
|
|
|
|
$update->execute([':importjob_serial' => $importjob_serial]);
|
|
|
|
$this->sendWebHookAlert("Import Job completed successfully.");
|
|
$this->email_admins("Import Job Success.", "Import Job completed successfully.");
|
|
} catch (Throwable $e) {
|
|
|
|
$SQL = "UPDATE importjobs
|
|
SET importjob_status = 'failed',
|
|
importjob_completed = NOW(),
|
|
importjob_error_message = :importjob_error_message
|
|
WHERE importjob_serial = :importjob_serial";
|
|
|
|
$fail = $this->connect()->prepare($SQL);
|
|
|
|
$fail->execute([':importjob_serial' => $importjob_serial, ':importjob_error_message' => $e->getMessage()]);
|
|
|
|
// Notify Admins of error
|
|
$this->sendWebHookAlert("Import Job {$importjob_serial} has failed. Check Error Logs for details.");
|
|
$this->email_admins("Import Job Failure.", "Import Job {$importjob_serial} has failed. Check Error Logs for details.");
|
|
}
|
|
}
|
|
|
|
// =================================
|
|
// Purge Imports Older then 24 Hours
|
|
// =================================
|
|
|
|
private function purgeImportJobs() {
|
|
|
|
$this->log("Purging Import Jobs Database Files");
|
|
|
|
$this->purgeFiles();
|
|
}
|
|
|
|
// =======================
|
|
// Claim Next Job in Queue
|
|
// =======================
|
|
|
|
private function claimNextJob() {
|
|
|
|
$this->connection->beginTransaction();
|
|
|
|
$SQL = "SELECT view_importjobs.*
|
|
FROM view_importjobs
|
|
WHERE view_importjobs.importjob_status = 'queued'
|
|
ORDER BY view_importjobs.importjob_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 importjobs
|
|
SET importjob_status = 'running'
|
|
WHERE importjob_serial = :importjob_serial";
|
|
|
|
$statement = $this->connection->prepare($SQL);
|
|
|
|
$statement->execute([':importjob_serial' => (int) $job['importjob_serial']]);
|
|
|
|
$this->connection->commit();
|
|
|
|
return $job;
|
|
}
|
|
|
|
// ====================
|
|
// Purges Files on Disk
|
|
// ====================
|
|
|
|
private function purgeFiles() {
|
|
|
|
$SQL = "select *
|
|
from importjobs
|
|
where importjobs.importjob_created < (NOW() - INTERVAL 24 HOUR)";
|
|
|
|
$importfiles = $this->connect()->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if (count($importfiles) == 0) {
|
|
$this->log("Nothing To Purge");
|
|
return;
|
|
}
|
|
|
|
foreach ($importfiles as $files) {
|
|
|
|
$filename = $files['importjob_file_path'];
|
|
|
|
if (is_file($filename)) {
|
|
unlink($filename);
|
|
}
|
|
}
|
|
|
|
$baseDir = "./uploads/Production/";
|
|
|
|
if (!is_dir($baseDir)) {
|
|
$this->log("Imports directory does not exist: {$baseDir}");
|
|
} else {
|
|
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 Import Jobs Database Records");
|
|
|
|
$SQL = "delete from importjobs where importjobs.importjob_created < (NOW() - INTERVAL 24 HOUR)";
|
|
|
|
$this->connection->query($SQL);
|
|
}
|
|
|
|
// =======================
|
|
// 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);
|
|
}
|
|
|
|
// ==============
|
|
// Parse CSV File
|
|
// ==============
|
|
|
|
public function parseCSVFile($file) {
|
|
|
|
$csvfile = fopen($file, 'r');
|
|
|
|
if ($csvfile === false) {
|
|
throw new RuntimeException("Unable to open CSV file: {$file}");
|
|
}
|
|
|
|
$RecordCount = 0;
|
|
|
|
while (($data = fgetcsv($csvfile, 0, ",", '"', '\\')) !== FALSE) {
|
|
|
|
if (count($data) < 36) {
|
|
$this->log("Skipping malformed CSV row with " . count($data) . " columns.");
|
|
continue;
|
|
}
|
|
|
|
$PermitFactsId = $data[1];
|
|
$EntryDate = $this->fixDate($data[2]);
|
|
$PermitNumber = $data[4];
|
|
$PermitDate = $this->fixDate($data[5]);
|
|
$ProjectAddress = $data[7];
|
|
$SubdivisionName = $data[8];
|
|
$ProjectType = $data[9];
|
|
$Company = substr($data[10], 0, 30);
|
|
$Address = substr($data[11], 0, 30);
|
|
$City = $data[12];
|
|
$State = $data[13];
|
|
$Zip = $data[14];
|
|
$Phone = $data[15];
|
|
$ProjectCity = $data[16];
|
|
$ProjectState = $data[17];
|
|
$Size = $data[18] ?: 0;
|
|
$Units = $data[19];
|
|
$BuildingType = $data[20];
|
|
$Value = $data[21] ?: 0;
|
|
$WorkType = $data[22];
|
|
$County = $data[23] ?: 0;
|
|
$Lot = $data[24];
|
|
$DistrictLL = $data[25];
|
|
$Ctl = $data[26];
|
|
$OwnerName = substr($data[27], 0, 30);
|
|
$OwnerAddress = substr($data[28], 0, 30);
|
|
$OwnerCity = $data[29];
|
|
$OwnerState = substr($data[30], 0, 2);
|
|
$OwnerZip = $data[31];
|
|
$OwnerPhone = $data[32];
|
|
$YTDPermits = $data[33] ?: 0;
|
|
$YTDValue = $data[34] ?: 0;
|
|
$TwelveMonthPreviousPermits = $data[35] ?: 0;
|
|
|
|
$County = $this->getCountiesByName($County)['county_serial'] ?? 0;
|
|
|
|
$SQL = "INSERT INTO permits (
|
|
permit_permit_facts_id,
|
|
permit_entry_date,
|
|
permit_permit_number,
|
|
permit_permit_date,
|
|
permit_project_addr,
|
|
permit_sub_div_name,
|
|
permit_project_type,
|
|
permit_company,
|
|
permit_address,
|
|
permit_city,
|
|
permit_state,
|
|
permit_zip,
|
|
permit_phone,
|
|
permit_project_city,
|
|
permit_project_state,
|
|
permit_size,
|
|
permit_units,
|
|
permit_building_type,
|
|
permit_value,
|
|
permit_work_type,
|
|
permit_county,
|
|
permit_lot,
|
|
permit_dist_ll,
|
|
permit_ct1,
|
|
permit_owner_name,
|
|
permit_owner_address,
|
|
permit_owner_city,
|
|
permit_owner_state,
|
|
permit_owner_zip,
|
|
permit_owner_phone,
|
|
permit_ytd_permits,
|
|
permit_ytd_value,
|
|
permit_12mth_prev_permits )
|
|
|
|
VALUES (
|
|
:permit_permit_facts_id,
|
|
:permit_entry_date,
|
|
:permit_permit_number,
|
|
:permit_permit_date,
|
|
:permit_project_addr,
|
|
:permit_sub_div_name,
|
|
:permit_project_type,
|
|
:permit_company,
|
|
:permit_address,
|
|
:permit_city,
|
|
:permit_state,
|
|
:permit_zip,
|
|
:permit_phone,
|
|
:permit_project_city,
|
|
:permit_project_state,
|
|
:permit_size,
|
|
:permit_units,
|
|
:permit_building_type,
|
|
:permit_value,
|
|
:permit_work_type,
|
|
:permit_county,
|
|
:permit_lot,
|
|
:permit_dist_ll,
|
|
:permit_ct1,
|
|
:permit_owner_name,
|
|
:permit_owner_address,
|
|
:permit_owner_city,
|
|
:permit_owner_state,
|
|
:permit_owner_zip,
|
|
:permit_owner_phone,
|
|
:permit_ytd_permits,
|
|
:permit_ytd_value,
|
|
:permit_12mth_prev_permits )";
|
|
|
|
$statement = $this->connection->prepare($SQL);
|
|
|
|
$statement->bindValue(":permit_permit_facts_id", $PermitFactsId);
|
|
$statement->bindValue(":permit_permit_number", $PermitNumber);
|
|
$statement->bindValue(":permit_entry_date", $EntryDate);
|
|
$statement->bindValue(":permit_permit_date", $PermitDate);
|
|
$statement->bindValue(":permit_project_addr", $ProjectAddress);
|
|
$statement->bindValue(":permit_sub_div_name", $SubdivisionName);
|
|
$statement->bindValue(":permit_project_type", $ProjectType);
|
|
$statement->bindValue(":permit_company", $Company);
|
|
$statement->bindValue(":permit_address", $Address);
|
|
$statement->bindValue(":permit_city", $City);
|
|
$statement->bindValue(":permit_state", $State);
|
|
$statement->bindValue(":permit_zip", $Zip);
|
|
$statement->bindValue(":permit_phone", $Phone);
|
|
$statement->bindValue(":permit_project_city", $ProjectCity);
|
|
$statement->bindValue(":permit_project_state", $ProjectState);
|
|
$statement->bindValue(":permit_size", $Size);
|
|
$statement->bindValue(":permit_units", $Units);
|
|
$statement->bindValue(":permit_building_type", $BuildingType);
|
|
$statement->bindValue(":permit_value", $Value);
|
|
$statement->bindValue(":permit_work_type", $WorkType);
|
|
$statement->bindValue(":permit_county", $County);
|
|
$statement->bindValue(":permit_lot", $Lot);
|
|
$statement->bindValue(":permit_dist_ll", $DistrictLL);
|
|
$statement->bindValue(":permit_ct1", $Ctl);
|
|
$statement->bindValue(":permit_owner_name", $OwnerName);
|
|
$statement->bindValue(":permit_owner_address", $OwnerAddress);
|
|
$statement->bindValue(":permit_owner_city", $OwnerCity);
|
|
$statement->bindValue(":permit_owner_state", $OwnerState);
|
|
$statement->bindValue(":permit_owner_zip", $OwnerZip);
|
|
$statement->bindValue(":permit_owner_phone", $OwnerPhone);
|
|
$statement->bindValue(":permit_ytd_permits", $YTDPermits);
|
|
$statement->bindValue(":permit_ytd_value", $YTDValue);
|
|
$statement->bindValue(":permit_12mth_prev_permits", $TwelveMonthPreviousPermits);
|
|
|
|
$statement->execute();
|
|
|
|
$RecordCount++;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
// ========================
|
|
// Return a Counties Object
|
|
// ========================
|
|
|
|
public function getCountiesByName($county_name) {
|
|
|
|
$SQL = "select view_counties.*
|
|
from view_counties
|
|
where view_counties.county_name = :county_name";
|
|
|
|
$statement = $this->connection->prepare($SQL);
|
|
|
|
$statement->execute([':county_name' => $county_name]);
|
|
|
|
$county_name = $statement->fetch(PDO::FETCH_ASSOC);
|
|
|
|
return $county_name;
|
|
}
|
|
|
|
// ==========================
|
|
// 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") : "<settings/>"));
|
|
|
|
print_r($settings);
|
|
die();
|
|
|
|
// 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
|
|
|
|
$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);
|
|
}
|
|
|
|
//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}");
|
|
}
|
|
}
|
|
|
|
// ========================
|
|
// Return an Formatted Date
|
|
// ========================
|
|
|
|
public function fixDate($givenDate) {
|
|
|
|
$formattedDate = date('Y-m-d', strtotime($givenDate));
|
|
|
|
return $formattedDate;
|
|
}
|
|
}
|
|
|
|
?>
|