516 lines
20 KiB
PHP
516 lines
20 KiB
PHP
<?php
|
|
|
|
class PlanningAndZoningFacts extends Base {
|
|
|
|
// Variables
|
|
|
|
private $current_user_serial = 0;
|
|
private $current_user_id = "";
|
|
private $current_user_name = "";
|
|
private $current_user_email = "";
|
|
private $pagination_size = 20;
|
|
private $pagination_page = 1;
|
|
|
|
// =================
|
|
// Class Constructor
|
|
// =================
|
|
|
|
public function __construct() {
|
|
|
|
set_error_handler(array($this, "displayError"));
|
|
|
|
date_default_timezone_set(self::TIMEZONE);
|
|
|
|
$this->current_user_serial = $_SESSION[self::USER_SERIAL] ?? 0;
|
|
$this->current_user_id = $_SESSION[self::USER_ID] ?? "";
|
|
$this->current_user_name = $_SESSION[self::USER_NAME] ?? "";
|
|
$this->current_user_email = $_SESSION[self::USER_EMAIL] ?? "";
|
|
|
|
$this->pagination_page = $_SESSION[self::PAGINATION_PAGE] ?? 1;
|
|
}
|
|
|
|
// ================================
|
|
// Search Planning And Zoning Facts
|
|
// ================================
|
|
|
|
public function searchPlanningAndZoningFacts() {
|
|
|
|
$subscription_serial = $_SESSION['subscription_serial'];
|
|
$usersubscription_serial = $_SESSION['usersubscription_serial'];
|
|
|
|
$subscription = (new Subscriptions())->getSubscription($subscription_serial);
|
|
|
|
if ($subscription->count() === 0) {
|
|
$this->logError("Invalid Subscription ({$subscription_serial}): " . __METHOD__);
|
|
}
|
|
|
|
$userssubscription = (new Users())->getUserSubscription($usersubscription_serial);
|
|
|
|
$user_subscription_start_date = $userssubscription->record->usersubscription_start_date;
|
|
$user_subscription_end_date = $userssubscription->record->usersubscription_end_date;
|
|
|
|
$searchhistory = (new SearchHistory())->getUsersPreviousSearchParameters($this->current_user_serial, $subscription_serial);
|
|
|
|
// Update Search History Record
|
|
if (empty($searchhistory)) {
|
|
(new SearchHistory())->saveUserSearchParameters($_POST);
|
|
} else {
|
|
(new SearchHistory())->updateUserSearchParameters($_POST);
|
|
}
|
|
|
|
$planningzoningfacts = $this->getPZFactsPage($user_subscription_start_date, $user_subscription_end_date);
|
|
$searchhistory = (new SearchHistory())->getUsersPreviousSearchParameters($this->current_user_serial, $subscription_serial);
|
|
|
|
$searchhistory->record->searchhistory_parameters->pzfact_entry_date_from = $planningzoningfacts->record->pzfact_entry_date_from;
|
|
$searchhistory->record->searchhistory_parameters->pzfact_entry_date_to = $planningzoningfacts->record->pzfact_entry_date_to;
|
|
|
|
$popovers = (new Popovers())->getPopovers();
|
|
$dropdowns = (new Dropdowns())->getDropdowns();
|
|
// $cities = (new Cities())->getSubscriptionCities($subscription_serial);
|
|
$cities = $this->getPzCities();
|
|
// $counties = (new Counties())->getSubscriptionCounties($subscription_serial);
|
|
$counties = $this->getPzCounties();
|
|
|
|
$availablerecordcount = $this->userTotalAvailableRecordCount($user_subscription_start_date, $user_subscription_end_date);
|
|
|
|
$XML = $this->mergeXML($planningzoningfacts, "<XML/>");
|
|
$XML = $this->mergeXML($subscription, $XML);
|
|
$XML = $this->mergeXML($popovers, $XML);
|
|
$XML = $this->mergeXML($dropdowns, $XML);
|
|
$XML = $this->mergeXML($cities, $XML);
|
|
$XML = $this->mergeXML($counties, $XML);
|
|
$XML = $this->mergeXML($searchhistory, $XML);
|
|
$XML = $this->mergeXML($availablerecordcount, $XML);
|
|
|
|
$content = $this->applyXSL($XML, $this->getXSL("searchPZFacts"));
|
|
$this->displayContent($content);
|
|
}
|
|
|
|
// =======================
|
|
// Return PZ Cities Object
|
|
// =======================
|
|
|
|
public function getPzCities() {
|
|
|
|
$SQL = "select view_pzfacts.pzfact_project_city, view_pzfacts.project_city_verbose from view_pzfacts where pzfact_in_city_limit = 1 and project_city_verbose is not null group by project_city_verbose";
|
|
|
|
return $this->getTable("subscriptioncities", $SQL);
|
|
}
|
|
|
|
// =========================
|
|
// Return PZ Counties Object
|
|
// =========================
|
|
|
|
public function getPzCounties() {
|
|
|
|
$SQL = "select view_pzfacts.pzfact_county, view_pzfacts.pzfact_county_name_verbose from view_pzfacts where pzfact_county_name_verbose is not null group by pzfact_county_name_verbose";
|
|
|
|
return $this->getTable("subscriptioncounties", $SQL);
|
|
}
|
|
|
|
// =============================
|
|
// Return a PZ Facts Page Object
|
|
// =============================
|
|
|
|
public function getPZFactsPage($user_subscription_start_date, $user_subscription_end_date) {
|
|
|
|
// -----------------------
|
|
// Read + normalize inputs
|
|
// --------------------------
|
|
$pzfact_project_name = trim((string) filter_input(INPUT_POST, "pzfact_project_name")) ?: "";
|
|
$pzfact_project_city = trim((string) filter_input(INPUT_POST, "pzfact_project_city")) ?: "";
|
|
$pzfact_county = trim((string) filter_input(INPUT_POST, "pzfact_county")) ?: "";
|
|
$pzfact_project_type = trim((string) filter_input(INPUT_POST, "pzfact_project_type")) ?: "";
|
|
$pzfact_action_code = trim((string) filter_input(INPUT_POST, "pzfact_action_code")) ?: "";
|
|
$pzfact_acres_from = trim((string) filter_input(INPUT_POST, "pzfact_acres_from")) ?: "";
|
|
$pzfact_acres_to = trim((string) filter_input(INPUT_POST, "pzfact_acres_to")) ?: "";
|
|
$pzfact_entry_date_from = trim((string) filter_input(INPUT_POST, "pzfact_entry_date_from")) ?: "";
|
|
$pzfact_entry_date_to = trim((string) filter_input(INPUT_POST, "pzfact_entry_date_to")) ?: "";
|
|
|
|
// Pagination
|
|
$pagination = trim((string) filter_input(INPUT_POST, "pagination")) ?: "";
|
|
$this->pagination_size = filter_input(INPUT_POST, "pzfacts_per_page", FILTER_SANITIZE_NUMBER_INT) ?: $this->pagination_size;
|
|
|
|
// --------------------------------------------------------------
|
|
// Clamp subscription start to last year (your existing behavior)
|
|
// --------------------------------------------------------------
|
|
|
|
$oneYearAgo = date('Y-m-d', strtotime('-1 year'));
|
|
if ($user_subscription_start_date <= $oneYearAgo) {
|
|
$user_subscription_start_date = $oneYearAgo;
|
|
}
|
|
|
|
// Safety: make sure subscription end isn't before start
|
|
if ($user_subscription_end_date < $user_subscription_start_date) {
|
|
$this->displayNotice("Your authorized access period is invalid (end date is before start date).");
|
|
return;
|
|
}
|
|
|
|
// --------------------------------------
|
|
// Normalize entry dates to Y-m-d or null
|
|
// --------------------------------------
|
|
$entryFrom = ($pzfact_entry_date_from === "") ? null : date("Y-m-d", strtotime($pzfact_entry_date_from));
|
|
$entryTo = ($pzfact_entry_date_to === "") ? null : date("Y-m-d", strtotime($pzfact_entry_date_to));
|
|
|
|
// -------
|
|
// Connect
|
|
// -------
|
|
$connection = $this->connect();
|
|
|
|
// -------------------------------------
|
|
// Build WHERE clause once + params once
|
|
// -------------------------------------
|
|
$clauses = [];
|
|
$params = [];
|
|
|
|
// Always enforce subscription window
|
|
$clauses[] = "view_pzfacts.pzfact_entry_date BETWEEN :sub_start AND :sub_end";
|
|
$params[':sub_start'] = $user_subscription_start_date;
|
|
$params[':sub_end'] = $user_subscription_end_date;
|
|
|
|
// Project Name (REGEXP)
|
|
if ($pzfact_project_name !== '') {
|
|
$clauses[] = "view_pzfacts.pzfact_project_name REGEXP :project_name";
|
|
$params[':project_name'] = $pzfact_project_name;
|
|
}
|
|
|
|
// Helper: build safe IN (...) lists from a CSV string of numeric ids
|
|
$bindIn = function (string $field, string $csv, string $paramPrefix) use (&$clauses, &$params) {
|
|
|
|
// Accept "30,31, 32" etc.
|
|
$raw = array_values(array_filter(array_map('trim', explode(',', $csv)), 'strlen'));
|
|
|
|
// Keep digits only (prevents injection)
|
|
$items = [];
|
|
foreach ($raw as $v) {
|
|
if (ctype_digit($v)) {
|
|
$items[] = (int) $v;
|
|
}
|
|
}
|
|
|
|
if (!$items) {
|
|
return;
|
|
}
|
|
|
|
$placeholders = [];
|
|
foreach ($items as $i => $val) {
|
|
$ph = ":{$paramPrefix}{$i}";
|
|
$placeholders[] = $ph;
|
|
$params[$ph] = $val;
|
|
}
|
|
|
|
$clauses[] = "{$field} IN (" . implode(',', $placeholders) . ")";
|
|
};
|
|
|
|
// Safe IN filters
|
|
if ($pzfact_project_city !== '') {
|
|
$bindIn("view_pzfacts.pzfact_project_city", $pzfact_project_city, "city_");
|
|
|
|
|
|
}
|
|
|
|
if ($pzfact_county !== '') {
|
|
$bindIn("view_pzfacts.pzfact_county", $pzfact_county, "county_");
|
|
}
|
|
|
|
if ($pzfact_project_type !== '') {
|
|
$bindIn("view_pzfacts.pzfact_project_type", $pzfact_project_type, "ptype_");
|
|
}
|
|
|
|
if ($pzfact_action_code !== '') {
|
|
$bindIn("view_pzfacts.pzfact_action_code", $pzfact_action_code, "acode_");
|
|
}
|
|
|
|
// Acres filter (numeric; bind params)
|
|
if ($pzfact_acres_from !== '' && $pzfact_acres_to !== '') {
|
|
$clauses[] = "view_pzfacts.pzfact_acres BETWEEN :acres_from AND :acres_to";
|
|
$params[':acres_from'] = (float) $pzfact_acres_from;
|
|
$params[':acres_to'] = (float) $pzfact_acres_to;
|
|
} elseif ($pzfact_acres_from !== '') {
|
|
$clauses[] = "view_pzfacts.pzfact_acres = :acres_eq";
|
|
$params[':acres_eq'] = (float) $pzfact_acres_from;
|
|
} elseif ($pzfact_acres_to !== '') {
|
|
$clauses[] = "view_pzfacts.pzfact_acres = :acres_eq";
|
|
$params[':acres_eq'] = (float) $pzfact_acres_to;
|
|
}
|
|
|
|
// -----------------------------------------------------
|
|
// Entry Date Logic (User selection within subscription)
|
|
// -----------------------------------------------------
|
|
$subStart = $user_subscription_start_date;
|
|
$subEnd = $user_subscription_end_date;
|
|
|
|
if ($entryFrom !== null || $entryTo !== null) {
|
|
|
|
// Treat single date as a one-day range
|
|
if ($entryFrom !== null && $entryTo === null) {
|
|
$entryTo = $entryFrom;
|
|
}
|
|
if ($entryTo !== null && $entryFrom === null) {
|
|
$entryFrom = $entryTo;
|
|
}
|
|
|
|
// Swap if reversed
|
|
if ($entryFrom > $entryTo) {
|
|
$tmp = $entryFrom;
|
|
$entryFrom = $entryTo;
|
|
$entryTo = $tmp;
|
|
}
|
|
|
|
// Enforce subscription window with your existing policy (reject + notice)
|
|
if ($entryFrom < $subStart || $entryTo > $subEnd) {
|
|
$this->displayNotice("The Entry date or date range {$this->current_user_name} selected is outside your authorized access period.");
|
|
return;
|
|
}
|
|
|
|
// Add user range filter (intersection with subscription range)
|
|
$clauses[] = "view_pzfacts.pzfact_entry_date BETWEEN :entry_from AND :entry_to";
|
|
$params[':entry_from'] = $entryFrom;
|
|
$params[':entry_to'] = $entryTo;
|
|
}
|
|
|
|
// Build final WHERE SQL
|
|
$whereSql = "WHERE " . implode(" AND ", $clauses);
|
|
|
|
if ($pzfact_project_city !== '') {
|
|
$whereSql .= ' and pzfact_in_city_limit = 1';
|
|
}
|
|
|
|
// -----------
|
|
// COUNT query
|
|
// -----------
|
|
$sqlCount = "SELECT COUNT(*) AS count
|
|
FROM view_pzfacts
|
|
{$whereSql}";
|
|
|
|
$stmtCount = $connection->prepare($sqlCount);
|
|
|
|
foreach ($params as $k => $v) {
|
|
$type = is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR;
|
|
$stmtCount->bindValue($k, $v, $type);
|
|
}
|
|
|
|
$stmtCount->execute();
|
|
$recordset = $stmtCount->fetch(PDO::FETCH_ASSOC);
|
|
$count = $recordset ? (int) $recordset["count"] : 0;
|
|
|
|
// ----------------
|
|
// Pagination logic
|
|
// ----------------
|
|
switch ($pagination) {
|
|
case "first":
|
|
$this->pagination_page = 1;
|
|
break;
|
|
case "previous":
|
|
$this->pagination_page = max($this->pagination_page - 1, 1);
|
|
break;
|
|
case "next":
|
|
$this->pagination_page = min($this->pagination_page + 1, (int) ceil($count / $this->pagination_size));
|
|
break;
|
|
case "last":
|
|
$this->pagination_page = (int) ceil($count / $this->pagination_size);
|
|
break;
|
|
}
|
|
|
|
$offset = max(0, ($this->pagination_page - 1) * $this->pagination_size);
|
|
$start = $offset + 1;
|
|
$end = min($count, $start + $this->pagination_size - 1);
|
|
|
|
// ----------
|
|
// DATA query
|
|
// ----------
|
|
$sqlData = "SELECT view_pzfacts.*
|
|
FROM view_pzfacts
|
|
{$whereSql}";
|
|
|
|
if ($pagination !== 'off') {
|
|
$sqlData .= " limit :limit
|
|
offset :offset";
|
|
}
|
|
|
|
$stmtData = $connection->prepare($sqlData);
|
|
|
|
// Bind the same params again
|
|
foreach ($params as $k => $v) {
|
|
$type = is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR;
|
|
$stmtData->bindValue($k, $v, $type);
|
|
}
|
|
|
|
if ($pagination !== 'off') {
|
|
$stmtData->bindValue(":limit", (int) $this->pagination_size, PDO::PARAM_INT);
|
|
$stmtData->bindValue(":offset", (int) $offset, PDO::PARAM_INT);
|
|
}
|
|
|
|
$stmtData->execute();
|
|
|
|
$pzfacts = $this->getTableXML("pzfacts", $stmtData);
|
|
|
|
// ------------------------------------------------------------------------------------
|
|
// Add searched entry_dates for search history
|
|
// (keep your original behavior: default to subscription window if user didn't specify)
|
|
// ------------------------------------------------------------------------------------
|
|
$pzfacts->record->pzfact_entry_date_from = !empty($entryFrom) ? date('m/d/Y', strtotime($entryFrom)) : date('m/d/Y', strtotime($user_subscription_start_date));
|
|
|
|
$pzfacts->record->pzfact_entry_date_to = !empty($entryTo) ? date('m/d/Y', strtotime($entryTo)) : date('m/d/Y', strtotime($user_subscription_end_date));
|
|
|
|
// ---------------------
|
|
// Pagination attributes
|
|
// ---------------------
|
|
$pzfacts->addAttribute("count", $count);
|
|
$pzfacts->addAttribute("start", $start);
|
|
$pzfacts->addAttribute("end", $end);
|
|
$pzfacts->addAttribute("page", $this->pagination_page);
|
|
|
|
$_SESSION[self::PAGINATION_PAGE] = $this->pagination_page;
|
|
|
|
return $pzfacts;
|
|
}
|
|
|
|
// ==================================
|
|
// Users Total Available Record Count
|
|
// ==================================
|
|
|
|
public function userTotalAvailableRecordCount($user_subscription_start_date, $user_subscription_end_date) {
|
|
|
|
// 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 count(*) as count
|
|
from view_pzfacts
|
|
where view_pzfacts.pzfact_entry_date between '{$user_subscription_start_date}' and '{$user_subscription_end_date}' ";
|
|
|
|
// $counties = (new Counties())->getSubscriptionCounties($_SESSION['subscription_serial']);
|
|
//
|
|
// $countyNames = [];
|
|
//
|
|
// foreach ($counties as $county) {
|
|
// $countyNames[] = " " . (string) $county->subscriptioncounty_county . " ";
|
|
// }
|
|
//
|
|
// $pzfact_county = ' ' . implode(', ', $countyNames) . ' ';
|
|
//
|
|
// $SQL .= " and view_pzfacts.pzfact_county in ($pzfact_county) ";
|
|
|
|
return $this->getTable("userstotalavailablerecords", $SQL);
|
|
}
|
|
|
|
// =====================
|
|
// Search Business Facts
|
|
// =====================
|
|
|
|
public function planningAndZoningFactsBasic() {
|
|
|
|
$step = filter_input(INPUT_POST, "step") ?: "prompt";
|
|
|
|
switch ($step) {
|
|
|
|
case "prompt":
|
|
|
|
$dateranges = $this->getDateRangeXML();
|
|
|
|
$content = $this->applyXSL($dateranges, $this->getXSL("pzFactsBasic"));
|
|
|
|
$this->displayContent($content);
|
|
|
|
break;
|
|
|
|
case "results":
|
|
|
|
$week_start_raw = trim((string) filter_input(INPUT_POST, 'week_start'));
|
|
$week_end_raw = trim((string) filter_input(INPUT_POST, 'week_end'));
|
|
|
|
$week_start_db = $week_start_raw !== '' ? date('Y-m-d', strtotime($week_start_raw)) : null;
|
|
$week_end_db = $week_end_raw !== '' ? date('Y-m-d', strtotime($week_end_raw)) : null;
|
|
|
|
$pzfacts = $this->getPZFactsBasic($week_start_db, $week_end_db);
|
|
|
|
$dateranges = $this->getDateRangeXML();
|
|
|
|
$searched = $dateranges->addChild('searchedranges');
|
|
$searched->addChild('week_start', $week_start_db ? date('m/d/Y', strtotime($week_start_db)) : '');
|
|
$searched->addChild('week_end', $week_end_db ? date('m/d/Y', strtotime($week_end_db)) : '');
|
|
|
|
$xml = $this->mergeXML($pzfacts, '<XML/>');
|
|
$xml = $this->mergeXML($dateranges, $xml);
|
|
|
|
$content = $this->applyXSL($xml, $this->getXSL("pzFactsBasic"));
|
|
|
|
$this->displayContent($content);
|
|
|
|
break;
|
|
|
|
default:
|
|
|
|
$this->displayHome();
|
|
}
|
|
}
|
|
|
|
public function getDateRangeXML() {
|
|
|
|
$ranges = $this->getFridayThursdayRanges(2, new DateTimeImmutable('today'));
|
|
|
|
$xml = new SimpleXMLElement('<ranges/>');
|
|
|
|
$labels = [
|
|
0 => 'week_prior',
|
|
1 => 'two_weeks_prior',
|
|
];
|
|
|
|
foreach ($ranges as $i => $range) {
|
|
$name = $labels[$i] ?? 'prior_' . ($i + 1);
|
|
|
|
$node = $xml->addChild($name);
|
|
|
|
$start = date('m/d/Y', strtotime($range['start']));
|
|
$end = date('m/d/Y', strtotime($range['end']));
|
|
|
|
$node->addChild('start', $start);
|
|
$node->addChild('end', $end);
|
|
}
|
|
|
|
$xml->subscription_serial = $_SESSION['subscription_serial'];
|
|
|
|
return $xml;
|
|
}
|
|
|
|
public function getFridayThursdayRanges(int $weeks = 2, ?DateTimeInterface $today = null): array {
|
|
|
|
$today = $today ? DateTimeImmutable::createFromInterface($today) : new DateTimeImmutable('today');
|
|
|
|
// "last thursday" gives the most recent Thursday strictly before today.
|
|
// If today IS Thursday, we want today as the end date.
|
|
$end = ($today->format('N') === '4') ? $today : $today->modify('last thursday');
|
|
|
|
$ranges = [];
|
|
for ($i = 0; $i < $weeks; $i++) {
|
|
$start = $end->modify('-6 days');
|
|
$ranges[] = [
|
|
'start' => $start->format('Y-m-d'),
|
|
'end' => $end->format('Y-m-d'),
|
|
];
|
|
$end = $start->modify('-1 day'); // move to previous block
|
|
}
|
|
|
|
return $ranges;
|
|
}
|
|
|
|
// ======================================
|
|
// Return an Planning/Zoning Facts Object
|
|
// ======================================
|
|
|
|
public function getPZFactsBasic($week_start, $week_end) {
|
|
|
|
$SQL = "select view_pzfacts.*
|
|
from view_pzfacts
|
|
where view_pzfacts.pzfact_entry_date between '{$week_start}' and '{$week_end}'
|
|
order by view_pzfacts.pzfact_county, view_pzfacts.pzfact_project_name";
|
|
|
|
return $this->getTable("pzfacts", $SQL);
|
|
}
|
|
}
|
|
|
|
?>
|