511 lines
19 KiB
PHP
511 lines
19 KiB
PHP
<?php
|
|
|
|
class PermitFacts 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 Permits
|
|
// ==============
|
|
|
|
public function searchPermitFacts() {
|
|
|
|
$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);
|
|
}
|
|
|
|
$permits = $this->getPermitFactsPage($user_subscription_start_date, $user_subscription_end_date);
|
|
|
|
$searchhistory = (new SearchHistory())->getUsersPreviousSearchParameters($this->current_user_serial, $subscription_serial);
|
|
|
|
// Populate Entry Dates with what was searched for if provided or users entry dates if no entry dates were provided
|
|
|
|
$searchhistory->record->searchhistory_parameters->permitfact_entry_date_from = $permits->record->permitfact_entry_date_from;
|
|
$searchhistory->record->searchhistory_parameters->permitfact_entry_date_to = $permits->record->permitfact_entry_date_to;
|
|
|
|
$popovers = (new Popovers())->getPopovers();
|
|
$dropdowns = (new Dropdowns())->getDropdowns();
|
|
$cities = (new Cities())->getSubscriptionCities($subscription_serial);
|
|
$counties = (new Counties())->getSubscriptionCounties($subscription_serial);
|
|
|
|
$availablerecordcount = $this->userTotalAvailableRecordCount($user_subscription_start_date, $user_subscription_end_date);
|
|
|
|
$XML = $this->mergeXML($permits, "<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("searchPermitFacts"));
|
|
$this->displayContent($content);
|
|
}
|
|
|
|
// =================================
|
|
// Return a Permit Facts Page Object
|
|
// =================================
|
|
|
|
public function getPermitFactsPage($user_subscription_start_date, $user_subscription_end_date) {
|
|
|
|
set_time_limit(0);
|
|
|
|
$connection = $this->connect();
|
|
|
|
$post = function ($name) {
|
|
return trim((string) (filter_input(INPUT_POST, $name) ?: ""));
|
|
};
|
|
|
|
$dateOrNull = function ($value) {
|
|
if ($value === "") {
|
|
return null;
|
|
}
|
|
|
|
$timestamp = strtotime($value);
|
|
return $timestamp ? date("Y-m-d", $timestamp) : null;
|
|
};
|
|
|
|
$numberOrNull = function ($value) {
|
|
$value = str_replace([",", "$"], "", trim((string) $value));
|
|
return is_numeric($value) ? $value : null;
|
|
};
|
|
|
|
$addInClause = function (&$where, &$params, $column, $rawValue, $prefix) {
|
|
|
|
if ($rawValue === "") {
|
|
return;
|
|
}
|
|
|
|
$items = array_filter(array_map(function ($item) {
|
|
return trim($item, " \t\n\r\0\x0B'\"");
|
|
}, explode(",", $rawValue)));
|
|
|
|
if (empty($items)) {
|
|
return;
|
|
}
|
|
|
|
$placeholders = [];
|
|
|
|
foreach ($items as $index => $item) {
|
|
$param = ":{$prefix}_{$index}";
|
|
$placeholders[] = $param;
|
|
$params[$param] = $item;
|
|
}
|
|
|
|
$where[] = "{$column} IN (" . implode(", ", $placeholders) . ")";
|
|
};
|
|
|
|
// ----------------------------
|
|
// Subscription Date Restriction
|
|
// ----------------------------
|
|
|
|
$oneYearAgo = date("Y-m-d", strtotime("-1 year"));
|
|
|
|
if ($user_subscription_start_date <= $oneYearAgo) {
|
|
$user_subscription_start_date = $oneYearAgo;
|
|
}
|
|
|
|
// -----------
|
|
// POST Values
|
|
// -----------
|
|
|
|
$permitfact_company = $post("permitfact_company");
|
|
$permitfact_project_name = $post("permitfact_project_name");
|
|
$permitfact_project_zip = $post("permitfact_project_zip");
|
|
$permitfact_project_city = $post("permitfact_project_city");
|
|
$permitfact_county = $post("permitfact_county");
|
|
$permitfact_project_type = $post("permitfact_project_type");
|
|
$permitfact_work_type = $post("permitfact_work_type");
|
|
$permitfact_building_type = $post("permitfact_building_type");
|
|
|
|
$permitfact_size_from = $numberOrNull($post("permitfact_size_from"));
|
|
$permitfact_size_to = $numberOrNull($post("permitfact_size_to"));
|
|
$permitfact_value_from = $numberOrNull($post("permitfact_value_from"));
|
|
$permitfact_value_to = $numberOrNull($post("permitfact_value_to"));
|
|
|
|
$permitfact_permit_date_from = $dateOrNull($post("permitfact_permit_date_from"));
|
|
$permitfact_permit_date_to = $dateOrNull($post("permitfact_permit_date_to"));
|
|
$permitfact_entry_date_from = $dateOrNull($post("permitfact_entry_date_from"));
|
|
$permitfact_entry_date_to = $dateOrNull($post("permitfact_entry_date_to"));
|
|
|
|
$pagination = $post("pagination");
|
|
|
|
$this->pagination_size = filter_input(INPUT_POST, "permitfacts_per_page", FILTER_SANITIZE_NUMBER_INT) ?: $this->pagination_size;
|
|
|
|
// -----------------
|
|
// Build SQL Filters
|
|
// -----------------
|
|
|
|
$where = [];
|
|
$params = [];
|
|
|
|
/*
|
|
* This is the required subscription gate.
|
|
* It always limits records by entry date.
|
|
*/
|
|
$where[] = "view_permitfacts.permitfact_entry_date BETWEEN :user_subscription_start_date AND :user_subscription_end_date";
|
|
|
|
$params[":user_subscription_start_date"] = $user_subscription_start_date;
|
|
$params[":user_subscription_end_date"] = $user_subscription_end_date;
|
|
|
|
if ($permitfact_company !== "") {
|
|
$where[] = "view_permitfacts.permitfact_company REGEXP :permitfact_company";
|
|
$params[":permitfact_company"] = $permitfact_company;
|
|
}
|
|
|
|
if ($permitfact_project_name !== "") {
|
|
$where[] = "view_permitfacts.permitfact_project_name REGEXP :permitfact_project_name";
|
|
$params[":permitfact_project_name"] = $permitfact_project_name;
|
|
}
|
|
|
|
if ($permitfact_project_zip !== "") {
|
|
$where[] = "view_permitfacts.permitfact_project_zip = :permitfact_project_zip";
|
|
$params[":permitfact_project_zip"] = $permitfact_project_zip;
|
|
}
|
|
|
|
$addInClause($where, $params, "view_permitfacts.permitfact_project_city", $permitfact_project_city, "city");
|
|
|
|
if ($permitfact_county !== "") {
|
|
|
|
$addInClause($where, $params, "view_permitfacts.permitfact_county", $permitfact_county, "county");
|
|
} else {
|
|
|
|
$counties = (new Counties())->getSubscriptionCounties($_SESSION["subscription_serial"]);
|
|
|
|
$countyList = [];
|
|
|
|
foreach ($counties as $county) {
|
|
$countyList[] = (string) $county->subscriptioncounty_county;
|
|
}
|
|
|
|
if (!empty($countyList)) {
|
|
$addInClause(
|
|
$where,
|
|
$params,
|
|
"view_permitfacts.permitfact_county",
|
|
implode(",", $countyList),
|
|
"sub_county"
|
|
);
|
|
}
|
|
}
|
|
|
|
$addInClause($where, $params, "view_permitfacts.permitfact_project_type", $permitfact_project_type, "project_type");
|
|
$addInClause($where, $params, "view_permitfacts.permitfact_work_type", $permitfact_work_type, "work_type");
|
|
$addInClause($where, $params, "view_permitfacts.permitfact_building_type", $permitfact_building_type, "building_type");
|
|
|
|
// -----------
|
|
// Size Filter
|
|
// -----------
|
|
|
|
if ($permitfact_size_from !== null && $permitfact_size_to !== null) {
|
|
|
|
if ($permitfact_size_from > $permitfact_size_to) {
|
|
[$permitfact_size_from, $permitfact_size_to] = [$permitfact_size_to, $permitfact_size_from];
|
|
}
|
|
|
|
$where[] = "view_permitfacts.permitfact_size BETWEEN :permitfact_size_from AND :permitfact_size_to";
|
|
$params[":permitfact_size_from"] = $permitfact_size_from;
|
|
$params[":permitfact_size_to"] = $permitfact_size_to;
|
|
} elseif ($permitfact_size_from !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_size >= :permitfact_size_from";
|
|
$params[":permitfact_size_from"] = $permitfact_size_from;
|
|
} elseif ($permitfact_size_to !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_size <= :permitfact_size_to";
|
|
$params[":permitfact_size_to"] = $permitfact_size_to;
|
|
}
|
|
|
|
// -----------
|
|
// Value Filter
|
|
// -----------
|
|
|
|
if ($permitfact_value_from !== null && $permitfact_value_to !== null) {
|
|
|
|
if ($permitfact_value_from > $permitfact_value_to) {
|
|
[$permitfact_value_from, $permitfact_value_to] = [$permitfact_value_to, $permitfact_value_from];
|
|
}
|
|
|
|
$where[] = "view_permitfacts.permitfact_value BETWEEN :permitfact_value_from AND :permitfact_value_to";
|
|
$params[":permitfact_value_from"] = $permitfact_value_from;
|
|
$params[":permitfact_value_to"] = $permitfact_value_to;
|
|
} elseif ($permitfact_value_from !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_value >= :permitfact_value_from";
|
|
$params[":permitfact_value_from"] = $permitfact_value_from;
|
|
} elseif ($permitfact_value_to !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_value <= :permitfact_value_to";
|
|
$params[":permitfact_value_to"] = $permitfact_value_to;
|
|
}
|
|
|
|
// ------------------
|
|
// Permit Date Filter
|
|
// ------------------
|
|
// This is optional and works WITH the entry-date subscription gate.
|
|
|
|
if ($permitfact_permit_date_from !== null && $permitfact_permit_date_to !== null) {
|
|
|
|
if ($permitfact_permit_date_from > $permitfact_permit_date_to) {
|
|
[$permitfact_permit_date_from, $permitfact_permit_date_to] = [
|
|
$permitfact_permit_date_to,
|
|
$permitfact_permit_date_from
|
|
];
|
|
}
|
|
|
|
$where[] = "view_permitfacts.permitfact_permit_date BETWEEN :permit_date_from AND :permit_date_to";
|
|
$params[":permit_date_from"] = $permitfact_permit_date_from;
|
|
$params[":permit_date_to"] = $permitfact_permit_date_to;
|
|
} elseif ($permitfact_permit_date_from !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_permit_date = :permit_date_from";
|
|
$params[":permit_date_from"] = $permitfact_permit_date_from;
|
|
} elseif ($permitfact_permit_date_to !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_permit_date = :permit_date_to";
|
|
$params[":permit_date_to"] = $permitfact_permit_date_to;
|
|
}
|
|
|
|
// -----------------
|
|
// Entry Date Filter
|
|
// -----------------
|
|
// This further narrows the already-required subscription entry-date gate.
|
|
|
|
if ($permitfact_entry_date_from !== null && $permitfact_entry_date_to !== null) {
|
|
|
|
if ($permitfact_entry_date_from > $permitfact_entry_date_to) {
|
|
[$permitfact_entry_date_from, $permitfact_entry_date_to] = [
|
|
$permitfact_entry_date_to,
|
|
$permitfact_entry_date_from
|
|
];
|
|
}
|
|
|
|
$where[] = "view_permitfacts.permitfact_entry_date BETWEEN :entry_date_from AND :entry_date_to";
|
|
$params[":entry_date_from"] = $permitfact_entry_date_from;
|
|
$params[":entry_date_to"] = $permitfact_entry_date_to;
|
|
} elseif ($permitfact_entry_date_from !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_entry_date >= :entry_date_from";
|
|
$params[":entry_date_from"] = $permitfact_entry_date_from;
|
|
} elseif ($permitfact_entry_date_to !== null) {
|
|
|
|
$where[] = "view_permitfacts.permitfact_entry_date <= :entry_date_to";
|
|
$params[":entry_date_to"] = $permitfact_entry_date_to;
|
|
}
|
|
|
|
$whereSQL = implode(" AND ", $where);
|
|
|
|
// ------------
|
|
// Record Count
|
|
// ------------
|
|
|
|
$countSQL = "
|
|
SELECT COUNT(*) AS count
|
|
FROM view_permitfacts
|
|
WHERE {$whereSQL}
|
|
";
|
|
|
|
$statement = $connection->prepare($countSQL);
|
|
|
|
foreach ($params as $param => $value) {
|
|
$statement->bindValue($param, $value, PDO::PARAM_STR);
|
|
}
|
|
|
|
$statement->execute();
|
|
|
|
$recordset = $statement->fetch(PDO::FETCH_ASSOC);
|
|
$count = $recordset ? (int) $recordset["count"] : 0;
|
|
|
|
// ----------
|
|
// Pagination
|
|
// ----------
|
|
|
|
$totalPages = max(1, (int) ceil($count / $this->pagination_size));
|
|
|
|
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, $totalPages);
|
|
break;
|
|
|
|
case "last":
|
|
$this->pagination_page = $totalPages;
|
|
break;
|
|
|
|
default:
|
|
$this->pagination_page = max(1, min($this->pagination_page, $totalPages));
|
|
break;
|
|
}
|
|
|
|
$offset = max(0, ($this->pagination_page - 1) * $this->pagination_size);
|
|
$start = $count > 0 ? $offset + 1 : 0;
|
|
$end = min($count, $offset + $this->pagination_size);
|
|
|
|
// ----------
|
|
// Data Query
|
|
// ----------
|
|
|
|
$dataSQL = "
|
|
SELECT view_permitfacts.*
|
|
FROM view_permitfacts
|
|
WHERE {$whereSQL}
|
|
ORDER BY view_permitfacts.permitfact_entry_date DESC,
|
|
view_permitfacts.permitfact_permit_date DESC
|
|
";
|
|
|
|
if ($pagination !== "off") {
|
|
$dataSQL .= "
|
|
LIMIT :limit
|
|
OFFSET :offset
|
|
";
|
|
}
|
|
|
|
$statement = $connection->prepare($dataSQL);
|
|
|
|
foreach ($params as $param => $value) {
|
|
$statement->bindValue($param, $value, PDO::PARAM_STR);
|
|
}
|
|
|
|
if ($pagination !== "off") {
|
|
$statement->bindValue(":limit", (int) $this->pagination_size, PDO::PARAM_INT);
|
|
$statement->bindValue(":offset", (int) $offset, PDO::PARAM_INT);
|
|
}
|
|
|
|
$statement->execute();
|
|
|
|
$permitfacts = $this->getTableXML("permitfacts", $statement);
|
|
|
|
if (!isset($permitfacts->record)) {
|
|
$permitfacts->addChild("record");
|
|
}
|
|
|
|
$permitfacts->record->permitfact_entry_date_from = !empty($permitfact_entry_date_from) ? date("m/d/Y", strtotime($permitfact_entry_date_from)) : date("m/d/Y", strtotime($user_subscription_start_date));
|
|
|
|
$permitfacts->record->permitfact_entry_date_to = !empty($permitfact_entry_date_to) ? date("m/d/Y", strtotime($permitfact_entry_date_to)) : date("m/d/Y", strtotime($user_subscription_end_date));
|
|
|
|
$permitfacts->record->permitfact_permit_date_from = !empty($permitfact_permit_date_from) ? date("m/d/Y", strtotime($permitfact_permit_date_from)) : "";
|
|
|
|
$permitfacts->record->permitfact_permit_date_to = !empty($permitfact_permit_date_to) ? date("m/d/Y", strtotime($permitfact_permit_date_to)) : "";
|
|
|
|
$permitfacts->addAttribute("count", $count);
|
|
$permitfacts->addAttribute("start", $start);
|
|
$permitfacts->addAttribute("end", $end);
|
|
$permitfacts->addAttribute("page", $this->pagination_page);
|
|
|
|
$_SESSION[self::PAGINATION_PAGE] = $this->pagination_page;
|
|
|
|
return $permitfacts;
|
|
}
|
|
|
|
// ==================================
|
|
// 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_permitfacts
|
|
where view_permitfacts.permitfact_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 . " ";
|
|
}
|
|
|
|
$permitfact_county = ' ' . implode(', ', $countyNames) . ' ';
|
|
|
|
$SQL .= " and view_permitfacts.permitfact_county in ($permitfact_county) ";
|
|
|
|
return $this->getTable("userstotalavailablerecords", $SQL);
|
|
}
|
|
|
|
// ======================
|
|
// Get Users Full Dataset
|
|
// ======================
|
|
|
|
public function getPermitFactsFullDataset($user_subscription_start_date, $user_subscription_end_date) {
|
|
|
|
set_time_limit(0);
|
|
|
|
$connection = $this->connect();
|
|
|
|
$SQL = "select view_permitfacts.*
|
|
from view_permitfacts
|
|
where view_permitfacts.permitfact_entry_date between :user_subscription_start_date and :user_subscription_end_date";
|
|
|
|
$statement = $connection->prepare($SQL);
|
|
|
|
$statement->bindValue(":user_subscription_start_date", $user_subscription_start_date, PDO::PARAM_STR);
|
|
$statement->bindValue(":user_subscription_end_date", $user_subscription_end_date, PDO::PARAM_STR);
|
|
|
|
$statement->execute();
|
|
|
|
$permitfacts = $this->getTableXML("permitfacts", $statement);
|
|
|
|
return $permitfacts;
|
|
}
|
|
}
|
|
|
|
?>
|