763 lines
28 KiB
PHP
763 lines
28 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
class FWCLicenses 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 Recreational Licenses
|
||
|
|
// ============================
|
||
|
|
|
||
|
|
public function searchRecreationalLicenses() {
|
||
|
|
|
||
|
|
$step = filter_input(INPUT_POST, "step") ?: "prompt";
|
||
|
|
|
||
|
|
$popovers = (new Popovers())->getPopovers();
|
||
|
|
$dropdowns = (new Dropdowns())->getDropdowns();
|
||
|
|
$constants = $this->getConstants();
|
||
|
|
$cities = (new Cities())->getCities();
|
||
|
|
|
||
|
|
$XML = $this->mergeXML($popovers, "<XML/>");
|
||
|
|
$XML = $this->mergeXML($dropdowns, $XML);
|
||
|
|
$XML = $this->mergeXML($constants, $XML);
|
||
|
|
$XML = $this->mergeXML($cities, $XML);
|
||
|
|
|
||
|
|
switch ($step) {
|
||
|
|
|
||
|
|
case "prompt":
|
||
|
|
|
||
|
|
$this->displayContent($this->applyXSL($XML, $this->getXSL("searchRecreationalLicenses")));
|
||
|
|
|
||
|
|
break;
|
||
|
|
|
||
|
|
case "search":
|
||
|
|
|
||
|
|
$licenses = $this->getRecreationalLicensesPage();
|
||
|
|
|
||
|
|
$XML = $this->mergeXML($licenses, $XML);
|
||
|
|
|
||
|
|
$this->displayContent($this->applyXSL($XML, $this->getXSL("searchRecreationalLicenses")));
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// ==========================
|
||
|
|
// Search Commercial Licenses
|
||
|
|
// ==========================
|
||
|
|
|
||
|
|
public function searchCommercialLicenses() {
|
||
|
|
|
||
|
|
$step = filter_input(INPUT_POST, "step") ?: "prompt";
|
||
|
|
|
||
|
|
$popovers = (new Popovers())->getPopovers();
|
||
|
|
$dropdowns = (new Dropdowns())->getDropdowns();
|
||
|
|
$constants = $this->getConstants();
|
||
|
|
$cities = (new Cities())->getCities();
|
||
|
|
$licensescodes = $this->getCommercialLicenseCodes();
|
||
|
|
|
||
|
|
$XML = $this->mergeXML($popovers, "<XML/>");
|
||
|
|
$XML = $this->mergeXML($dropdowns, $XML);
|
||
|
|
$XML = $this->mergeXML($constants, $XML);
|
||
|
|
$XML = $this->mergeXML($cities, $XML);
|
||
|
|
$XML = $this->mergeXML($licensescodes, $XML);
|
||
|
|
|
||
|
|
switch ($step) {
|
||
|
|
|
||
|
|
case "prompt":
|
||
|
|
|
||
|
|
$this->displayContent($this->applyXSL($XML, $this->getXSL("searchCommercialLicenses")));
|
||
|
|
|
||
|
|
break;
|
||
|
|
|
||
|
|
case "search":
|
||
|
|
|
||
|
|
$licenses = $this->getRecreationalLicensesPage();
|
||
|
|
|
||
|
|
$XML = $this->mergeXML($licenses, $XML);
|
||
|
|
|
||
|
|
$this->displayContent($this->applyXSL($XML, $this->getXSL("searchCommercialLicenses")));
|
||
|
|
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// ===============
|
||
|
|
// License Summary
|
||
|
|
// ===============
|
||
|
|
|
||
|
|
public function licenseSummary() {
|
||
|
|
|
||
|
|
$license_serial = filter_input(INPUT_POST, "license_serial", FILTER_SANITIZE_NUMBER_INT) ?: 0;
|
||
|
|
|
||
|
|
$license = $this->getLicense($license_serial);
|
||
|
|
|
||
|
|
if ($license->count() == 0) {
|
||
|
|
$this->logError("Invalid License ({$license_serial}). " . __METHOD__);
|
||
|
|
}
|
||
|
|
|
||
|
|
$XML = $this->mergeXML($license, "<XML/>");
|
||
|
|
|
||
|
|
$content = $this->applyXSL($XML, $this->getXSL("licenseSummary"));
|
||
|
|
$this->displayContent($content);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ==========================================
|
||
|
|
// Return AutoComplete License Numbers (ajax)
|
||
|
|
// ==========================================
|
||
|
|
|
||
|
|
public function getAutoCompleteLicenseNumbers() {
|
||
|
|
|
||
|
|
$results = array();
|
||
|
|
|
||
|
|
$licensenumber = filter_input(INPUT_GET, "commercial_license_number") ?: "";
|
||
|
|
$licensenumber = trim(preg_replace("/\s+/", " ", $licensenumber));
|
||
|
|
$licensenumber = preg_replace("/'+/", "'", $licensenumber);
|
||
|
|
$licensenumber = preg_replace('/"+/', '"', $licensenumber);
|
||
|
|
$licensenumber = str_replace("'", "''", $licensenumber);
|
||
|
|
|
||
|
|
$SQL = "select distinct commercial_license_number from view_fwc_commercial_licenses where commercial_license_number like '%{$licensenumber}%' order by commercial_license_number limit 10 offset 0";
|
||
|
|
|
||
|
|
$matches = $this->getTable("licensenumbers", $SQL);
|
||
|
|
|
||
|
|
foreach ($matches as $match) {
|
||
|
|
$results[] = (string) $match->commercial_license_number;
|
||
|
|
}
|
||
|
|
|
||
|
|
header('Content-Type: application/json');
|
||
|
|
echo json_encode($results);
|
||
|
|
exit();
|
||
|
|
}
|
||
|
|
|
||
|
|
// =============================
|
||
|
|
// Determine if a License exists
|
||
|
|
// =============================
|
||
|
|
|
||
|
|
public function licenseExists() {
|
||
|
|
|
||
|
|
$license_serial = filter_input(INPUT_POST, "license_serial", FILTER_SANITIZE_NUMBER_INT) ?: 0;
|
||
|
|
$license_name = filter_input(INPUT_POST, "license_name") ?: "";
|
||
|
|
|
||
|
|
$SQL = "select license_serial
|
||
|
|
from view_licenses
|
||
|
|
where license_serial != :license_serial
|
||
|
|
and lower(license_name) = :license_name";
|
||
|
|
|
||
|
|
$statement = $this->connect()->prepare($SQL);
|
||
|
|
|
||
|
|
$statement->bindValue(":license_serial", $license_serial);
|
||
|
|
$statement->bindValue(":license_name", strtolower($license_name));
|
||
|
|
|
||
|
|
$statement->execute();
|
||
|
|
|
||
|
|
$recordset = $statement->fetchAll(PDO::FETCH_ASSOC);
|
||
|
|
$exists = (count($recordset) > 0) ? true : false;
|
||
|
|
|
||
|
|
echo json_encode($exists);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ============================
|
||
|
|
// Return a License Type Object
|
||
|
|
// ============================
|
||
|
|
|
||
|
|
public function getCommercialLicenseCodes() {
|
||
|
|
|
||
|
|
$SQL = "select fwc_commercial_license_codes.*
|
||
|
|
from fwc_commercial_license_codes";
|
||
|
|
|
||
|
|
return $this->getTable("licensecodes", $SQL);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ============================
|
||
|
|
// Return a License Type Object
|
||
|
|
// ============================
|
||
|
|
|
||
|
|
public function getLicenseTypes() {
|
||
|
|
|
||
|
|
$SQL = "select distinct license_type
|
||
|
|
from fwc_recreational_licenses
|
||
|
|
where license_type != ''";
|
||
|
|
|
||
|
|
return $this->getTable("licensetypes", $SQL);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ========================
|
||
|
|
// Return an License Object
|
||
|
|
// ========================
|
||
|
|
|
||
|
|
public function getLicense($license_serial = 0) {
|
||
|
|
|
||
|
|
$SQL = "select view_licenses.*
|
||
|
|
from view_licenses
|
||
|
|
where license_serial = {$license_serial}";
|
||
|
|
|
||
|
|
return $this->getTable("licenses", $SQL);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ========================
|
||
|
|
// Return a Licenses Object
|
||
|
|
// ========================
|
||
|
|
|
||
|
|
public function getLicenses() {
|
||
|
|
|
||
|
|
$SQL = "select view_licenses.* from view_licenses";
|
||
|
|
|
||
|
|
return $this->getTable("licenses", $SQL);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ==========================================
|
||
|
|
// Return a Recreational Licenses Page Object
|
||
|
|
// ==========================================
|
||
|
|
|
||
|
|
public function getRecreationalLicensesPage() {
|
||
|
|
|
||
|
|
// ===============
|
||
|
|
// Sanitize Inputs
|
||
|
|
// ===============
|
||
|
|
|
||
|
|
$license_last_name = trim(filter_input(INPUT_POST, "license_last_name") ?: "");
|
||
|
|
$license_first_name = trim(filter_input(INPUT_POST, "license_first_name") ?: "");
|
||
|
|
$license_counties = $this->getMultiSelectInput("license_county");
|
||
|
|
$license_cities = $this->getMultiSelectInput("license_city");
|
||
|
|
$license_types = $this->getMultiSelectInput("license_type");
|
||
|
|
$license_genders = $this->getMultiSelectInput("license_gender");
|
||
|
|
$license_ethnicities = $this->getMultiSelectInput("license_ethnicity");
|
||
|
|
$license_start_date = trim(filter_input(INPUT_POST, "license_start_date") ?: "");
|
||
|
|
$license_expire_date = trim(filter_input(INPUT_POST, "license_expire_date") ?: "");
|
||
|
|
$pagination = trim(filter_input(INPUT_POST, "pagination") ?: "");
|
||
|
|
|
||
|
|
$license_start_date = empty($license_start_date) ? null : date("Y-m-d", strtotime($license_start_date));
|
||
|
|
$license_expire_date = empty($license_expire_date) ? null : date("Y-m-d", strtotime($license_expire_date));
|
||
|
|
|
||
|
|
// ===================
|
||
|
|
// Pagination Settings
|
||
|
|
// ===================
|
||
|
|
|
||
|
|
$this->pagination_page = isset($_SESSION[self::PAGINATION_PAGE]) ? (int) $_SESSION[self::PAGINATION_PAGE] : 1;
|
||
|
|
$this->pagination_size = (int) (filter_input(INPUT_POST, "licenses_per_page", FILTER_SANITIZE_NUMBER_INT) ?: $this->pagination_size);
|
||
|
|
|
||
|
|
if ($this->pagination_size <= 0) {
|
||
|
|
$this->pagination_size = 25;
|
||
|
|
}
|
||
|
|
|
||
|
|
$licenses_order_by = trim(filter_input(INPUT_POST, "licenses_order_by") ?: "license_last_name");
|
||
|
|
$licenses_order_direction = strtoupper(trim(filter_input(INPUT_POST, "licenses_order_direction") ?: "ASC"));
|
||
|
|
|
||
|
|
$allowed_order_columns = [
|
||
|
|
"license_last_name",
|
||
|
|
"license_first_name",
|
||
|
|
"license_county",
|
||
|
|
"license_city",
|
||
|
|
"license_type",
|
||
|
|
"license_gender",
|
||
|
|
"license_ethnicity",
|
||
|
|
"license_start_date",
|
||
|
|
"license_expire_date"
|
||
|
|
];
|
||
|
|
|
||
|
|
if (!in_array($licenses_order_by, $allowed_order_columns, true)) {
|
||
|
|
$licenses_order_by = "license_last_name";
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!in_array($licenses_order_direction, ["ASC", "DESC"], true)) {
|
||
|
|
$licenses_order_direction = "ASC";
|
||
|
|
}
|
||
|
|
|
||
|
|
// ===================
|
||
|
|
// Connect to Database
|
||
|
|
// ===================
|
||
|
|
|
||
|
|
$connection = $this->connect();
|
||
|
|
$license_counties = $this->getDropdownValuesBySerial($connection, $license_counties);
|
||
|
|
$license_types = $this->getDropdownValuesBySerial($connection, $license_types);
|
||
|
|
$license_ethnicities = $this->getDropdownValuesBySerial($connection, $license_ethnicities);
|
||
|
|
|
||
|
|
// ==================
|
||
|
|
// Build WHERE Clause
|
||
|
|
// ==================
|
||
|
|
|
||
|
|
$where = [];
|
||
|
|
$params = [];
|
||
|
|
|
||
|
|
if (!empty($license_last_name)) {
|
||
|
|
$where[] = "fwc_recreational_licenses.license_last_name LIKE :license_last_name";
|
||
|
|
$params[":license_last_name"] = $license_last_name . "%";
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!empty($license_first_name)) {
|
||
|
|
$where[] = "fwc_recreational_licenses.license_first_name LIKE :license_first_name";
|
||
|
|
$params[":license_first_name"] = $license_first_name . "%";
|
||
|
|
}
|
||
|
|
|
||
|
|
$this->addInFilter($where, $params, "fwc_recreational_licenses.license_county", "license_county", $license_counties);
|
||
|
|
$this->addInFilter($where, $params, "fwc_recreational_licenses.license_city", "license_city", $license_cities);
|
||
|
|
$this->addInFilter($where, $params, "fwc_recreational_licenses.license_type", "license_type", $license_types);
|
||
|
|
$this->addInFilter($where, $params, "fwc_recreational_licenses.license_gender", "license_gender", $license_genders);
|
||
|
|
$this->addInFilter($where, $params, "fwc_recreational_licenses.license_ethnicity", "license_ethnicity", $license_ethnicities);
|
||
|
|
|
||
|
|
if (!empty($license_start_date) && !empty($license_expire_date)) {
|
||
|
|
$where[] = "fwc_recreational_licenses.license_start_date BETWEEN :license_start_date AND :license_expire_date";
|
||
|
|
$params[":license_start_date"] = $license_start_date;
|
||
|
|
$params[":license_expire_date"] = $license_expire_date;
|
||
|
|
}
|
||
|
|
|
||
|
|
$whereSQL = "";
|
||
|
|
|
||
|
|
if (!empty($where)) {
|
||
|
|
$whereSQL = " WHERE " . implode(" AND ", $where);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ===========
|
||
|
|
// Count Query
|
||
|
|
// ===========
|
||
|
|
|
||
|
|
$SQL = "SELECT COUNT(*) AS totalcount FROM fwc_recreational_licenses {$whereSQL}";
|
||
|
|
|
||
|
|
$statement = $connection->prepare($SQL);
|
||
|
|
|
||
|
|
foreach ($params as $key => $value) {
|
||
|
|
$statement->bindValue($key, $value, PDO::PARAM_STR);
|
||
|
|
}
|
||
|
|
|
||
|
|
$statement->execute();
|
||
|
|
|
||
|
|
$recordset = $statement->fetch(PDO::FETCH_ASSOC);
|
||
|
|
|
||
|
|
$count = $recordset ? (int) $recordset["totalcount"] : 0;
|
||
|
|
|
||
|
|
// =================
|
||
|
|
// Pagination Logic
|
||
|
|
// =================
|
||
|
|
|
||
|
|
$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 = min(max($this->pagination_page, 1), $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
|
||
|
|
// ==========
|
||
|
|
|
||
|
|
$SQL = " SELECT fwc_recreational_licenses.license_serial,
|
||
|
|
fwc_recreational_licenses.license_last_name,
|
||
|
|
fwc_recreational_licenses.license_first_name,
|
||
|
|
fwc_recreational_licenses.license_county,
|
||
|
|
fwc_recreational_licenses.license_city,
|
||
|
|
fwc_recreational_licenses.license_type,
|
||
|
|
fwc_recreational_licenses.license_gender,
|
||
|
|
fwc_recreational_licenses.license_ethnicity,
|
||
|
|
DATE_FORMAT(fwc_recreational_licenses.license_start_date, '%c/%e/%Y') AS license_start_date_verbose,
|
||
|
|
DATE_FORMAT(fwc_recreational_licenses.license_expire_date, '%c/%e/%Y') AS license_expire_date_verbose
|
||
|
|
FROM fwc_recreational_licenses
|
||
|
|
{$whereSQL}
|
||
|
|
ORDER BY fwc_recreational_licenses.{$licenses_order_by} {$licenses_order_direction}
|
||
|
|
LIMIT :limit
|
||
|
|
OFFSET :offset";
|
||
|
|
|
||
|
|
$statement = $connection->prepare($SQL);
|
||
|
|
|
||
|
|
foreach ($params as $key => $value) {
|
||
|
|
$statement->bindValue($key, $value, PDO::PARAM_STR);
|
||
|
|
}
|
||
|
|
|
||
|
|
$statement->bindValue(":limit", (int) $this->pagination_size, PDO::PARAM_INT);
|
||
|
|
$statement->bindValue(":offset", (int) $offset, PDO::PARAM_INT);
|
||
|
|
|
||
|
|
$statement->execute();
|
||
|
|
|
||
|
|
$licenses = $this->getTableXML("licenses", $statement);
|
||
|
|
|
||
|
|
// ============================
|
||
|
|
// Insert Pagination Attributes
|
||
|
|
// ============================
|
||
|
|
|
||
|
|
$licenses->addAttribute("count", $count);
|
||
|
|
$licenses->addAttribute("start", $start);
|
||
|
|
$licenses->addAttribute("end", $end);
|
||
|
|
$licenses->addAttribute("page", $this->pagination_page);
|
||
|
|
$licenses->addAttribute("pages", $totalPages);
|
||
|
|
$licenses->addAttribute("limit", $this->pagination_size);
|
||
|
|
$licenses->addAttribute("type", "recreational");
|
||
|
|
|
||
|
|
$_SESSION[self::PAGINATION_PAGE] = $this->pagination_page;
|
||
|
|
|
||
|
|
return $licenses;
|
||
|
|
}
|
||
|
|
|
||
|
|
// ========================================
|
||
|
|
// Return a Commercial Licenses Page Object
|
||
|
|
// ========================================
|
||
|
|
|
||
|
|
public function getCommercialLicensesPage() {
|
||
|
|
|
||
|
|
// ===============
|
||
|
|
// Sanitize Inputs
|
||
|
|
// ===============
|
||
|
|
|
||
|
|
$commercial_license_company_or_last_name = trim(filter_input(INPUT_POST, "commercial_license_company_or_last_name") ?: "");
|
||
|
|
$license_codes = $this->getMultiSelectInput("license_code");
|
||
|
|
$commercial_license_number = trim(filter_input(INPUT_POST, "commercial_license_number") ?: "");
|
||
|
|
$license_counties = $this->getMultiSelectInput("license_county");
|
||
|
|
$commercial_license_total_tag_qty_from = filter_input(INPUT_POST, "commercial_license_total_tag_qty_from", FILTER_SANITIZE_NUMBER_INT);
|
||
|
|
$commercial_license_total_tag_qty_to = filter_input(INPUT_POST, "commercial_license_total_tag_qty_to", FILTER_SANITIZE_NUMBER_INT);
|
||
|
|
$commercial_license_begin_date_from = trim(filter_input(INPUT_POST, "commercial_license_begin_date_from") ?: "");
|
||
|
|
$commercial_license_begin_date_to = trim(filter_input(INPUT_POST, "commercial_license_begin_date_to") ?: "");
|
||
|
|
$commercial_license_expire_date_from = trim(filter_input(INPUT_POST, "commercial_license_expire_date_from") ?: "");
|
||
|
|
$commercial_license_expire_date_to = trim(filter_input(INPUT_POST, "commercial_license_expire_date_to") ?: "");
|
||
|
|
$pagination = trim(filter_input(INPUT_POST, "pagination") ?: "");
|
||
|
|
|
||
|
|
$commercial_license_total_tag_qty_from = ($commercial_license_total_tag_qty_from === null || $commercial_license_total_tag_qty_from === "") ? null : (int) $commercial_license_total_tag_qty_from;
|
||
|
|
$commercial_license_total_tag_qty_to = ($commercial_license_total_tag_qty_to === null || $commercial_license_total_tag_qty_to === "") ? null : (int) $commercial_license_total_tag_qty_to;
|
||
|
|
$commercial_license_begin_date_from = empty($commercial_license_begin_date_from) ? null : date("Y-m-d", strtotime($commercial_license_begin_date_from));
|
||
|
|
$commercial_license_begin_date_to = empty($commercial_license_begin_date_to) ? null : date("Y-m-d", strtotime($commercial_license_begin_date_to));
|
||
|
|
$commercial_license_expire_date_from = empty($commercial_license_expire_date_from) ? null : date("Y-m-d", strtotime($commercial_license_expire_date_from));
|
||
|
|
$commercial_license_expire_date_to = empty($commercial_license_expire_date_to) ? null : date("Y-m-d", strtotime($commercial_license_expire_date_to));
|
||
|
|
|
||
|
|
// ===================
|
||
|
|
// Pagination Settings
|
||
|
|
// ===================
|
||
|
|
|
||
|
|
$this->pagination_page = isset($_SESSION[self::PAGINATION_PAGE]) ? (int) $_SESSION[self::PAGINATION_PAGE] : 1;
|
||
|
|
$this->pagination_size = (int) (filter_input(INPUT_POST, "licenses_per_page", FILTER_SANITIZE_NUMBER_INT) ?: $this->pagination_size);
|
||
|
|
|
||
|
|
if ($this->pagination_size <= 0) {
|
||
|
|
$this->pagination_size = 25;
|
||
|
|
}
|
||
|
|
|
||
|
|
$licenses_order_by = trim(filter_input(INPUT_POST, "licenses_order_by") ?: "license_last_name");
|
||
|
|
$licenses_order_direction = strtoupper(trim(filter_input(INPUT_POST, "licenses_order_direction") ?: "ASC"));
|
||
|
|
|
||
|
|
$allowed_order_columns = [
|
||
|
|
"license_last_name" => "commercial_license_company_or_last_name",
|
||
|
|
"license_county" => "commercial_license_county",
|
||
|
|
"license_type" => "commercial_license_code",
|
||
|
|
"commercial_license_number" => "commercial_license_number",
|
||
|
|
"commercial_license_total_tag_qty" => "commercial_license_total_tag_qty",
|
||
|
|
"license_start_date" => "commercial_license_begin_date",
|
||
|
|
"license_expire_date" => "commercial_license_expire_date"
|
||
|
|
];
|
||
|
|
|
||
|
|
if (!array_key_exists($licenses_order_by, $allowed_order_columns)) {
|
||
|
|
$licenses_order_by = "license_last_name";
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!in_array($licenses_order_direction, ["ASC", "DESC"], true)) {
|
||
|
|
$licenses_order_direction = "ASC";
|
||
|
|
}
|
||
|
|
|
||
|
|
$licenses_order_column = $allowed_order_columns[$licenses_order_by];
|
||
|
|
|
||
|
|
// ===================
|
||
|
|
// Connect to Database
|
||
|
|
// ===================
|
||
|
|
|
||
|
|
$connection = $this->connect();
|
||
|
|
$license_counties = $this->getDropdownValuesBySerial($connection, $license_counties);
|
||
|
|
|
||
|
|
// ==================
|
||
|
|
// Build WHERE Clause
|
||
|
|
// ==================
|
||
|
|
|
||
|
|
$where = [];
|
||
|
|
$params = [];
|
||
|
|
|
||
|
|
if (!empty($commercial_license_company_or_last_name)) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_company_or_last_name LIKE :commercial_license_company_or_last_name";
|
||
|
|
$params[":commercial_license_company_or_last_name"] = $commercial_license_company_or_last_name . "%";
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!empty($commercial_license_number)) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_number LIKE :commercial_license_number";
|
||
|
|
$params[":commercial_license_number"] = "%" . $commercial_license_number . "%";
|
||
|
|
}
|
||
|
|
|
||
|
|
$this->addInFilter($where, $params, "fwc_commercial_licenses.commercial_license_code", "license_code", $license_codes);
|
||
|
|
$this->addInFilter($where, $params, "fwc_commercial_licenses.commercial_license_county", "license_county", $license_counties);
|
||
|
|
|
||
|
|
if ($commercial_license_total_tag_qty_from !== null) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_total_tag_qty >= :commercial_license_total_tag_qty_from";
|
||
|
|
$params[":commercial_license_total_tag_qty_from"] = $commercial_license_total_tag_qty_from;
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($commercial_license_total_tag_qty_to !== null) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_total_tag_qty <= :commercial_license_total_tag_qty_to";
|
||
|
|
$params[":commercial_license_total_tag_qty_to"] = $commercial_license_total_tag_qty_to;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!empty($commercial_license_begin_date_from)) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_begin_date >= :commercial_license_begin_date_from";
|
||
|
|
$params[":commercial_license_begin_date_from"] = $commercial_license_begin_date_from;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!empty($commercial_license_begin_date_to)) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_begin_date <= :commercial_license_begin_date_to";
|
||
|
|
$params[":commercial_license_begin_date_to"] = $commercial_license_begin_date_to;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!empty($commercial_license_expire_date_from)) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_expire_date >= :commercial_license_expire_date_from";
|
||
|
|
$params[":commercial_license_expire_date_from"] = $commercial_license_expire_date_from;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!empty($commercial_license_expire_date_to)) {
|
||
|
|
$where[] = "fwc_commercial_licenses.commercial_license_expire_date <= :commercial_license_expire_date_to";
|
||
|
|
$params[":commercial_license_expire_date_to"] = $commercial_license_expire_date_to;
|
||
|
|
}
|
||
|
|
|
||
|
|
$whereSQL = "";
|
||
|
|
|
||
|
|
if (!empty($where)) {
|
||
|
|
$whereSQL = " WHERE " . implode(" AND ", $where);
|
||
|
|
}
|
||
|
|
|
||
|
|
// ===========
|
||
|
|
// Count Query
|
||
|
|
// ===========
|
||
|
|
|
||
|
|
$SQL = "SELECT COUNT(*) AS totalcount FROM fwc_commercial_licenses {$whereSQL}";
|
||
|
|
|
||
|
|
$statement = $connection->prepare($SQL);
|
||
|
|
|
||
|
|
foreach ($params as $key => $value) {
|
||
|
|
$statement->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
|
||
|
|
}
|
||
|
|
|
||
|
|
$statement->execute();
|
||
|
|
|
||
|
|
$recordset = $statement->fetch(PDO::FETCH_ASSOC);
|
||
|
|
|
||
|
|
$count = $recordset ? (int) $recordset["totalcount"] : 0;
|
||
|
|
|
||
|
|
// =================
|
||
|
|
// Pagination Logic
|
||
|
|
// =================
|
||
|
|
|
||
|
|
$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 = min(max($this->pagination_page, 1), $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
|
||
|
|
// ==========
|
||
|
|
|
||
|
|
$SQL = " SELECT fwc_commercial_licenses.commercial_license_serial AS license_serial,
|
||
|
|
'' AS license_first_name,
|
||
|
|
fwc_commercial_licenses.commercial_license_company_or_last_name AS license_last_name,
|
||
|
|
fwc_commercial_licenses.commercial_license_county AS license_county,
|
||
|
|
'' AS license_city,
|
||
|
|
fwc_commercial_licenses.commercial_license_code AS license_type,
|
||
|
|
'' AS license_gender,
|
||
|
|
'' AS license_ethnicity,
|
||
|
|
DATE_FORMAT(fwc_commercial_licenses.commercial_license_begin_date, '%c/%e/%Y') AS license_start_date_verbose,
|
||
|
|
DATE_FORMAT(fwc_commercial_licenses.commercial_license_expire_date, '%c/%e/%Y') AS license_expire_date_verbose,
|
||
|
|
fwc_commercial_licenses.commercial_license_number,
|
||
|
|
fwc_commercial_licenses.commercial_license_total_tag_qty
|
||
|
|
FROM fwc_commercial_licenses
|
||
|
|
{$whereSQL}
|
||
|
|
ORDER BY fwc_commercial_licenses.{$licenses_order_column} {$licenses_order_direction}
|
||
|
|
LIMIT :limit
|
||
|
|
OFFSET :offset";
|
||
|
|
|
||
|
|
$statement = $connection->prepare($SQL);
|
||
|
|
|
||
|
|
foreach ($params as $key => $value) {
|
||
|
|
$statement->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
|
||
|
|
}
|
||
|
|
|
||
|
|
$statement->bindValue(":limit", (int) $this->pagination_size, PDO::PARAM_INT);
|
||
|
|
$statement->bindValue(":offset", (int) $offset, PDO::PARAM_INT);
|
||
|
|
|
||
|
|
$statement->execute();
|
||
|
|
|
||
|
|
$licenses = $this->getTableXML("licenses", $statement);
|
||
|
|
|
||
|
|
// ============================
|
||
|
|
// Insert Pagination Attributes
|
||
|
|
// ============================
|
||
|
|
|
||
|
|
$licenses->addAttribute("count", $count);
|
||
|
|
$licenses->addAttribute("start", $start);
|
||
|
|
$licenses->addAttribute("end", $end);
|
||
|
|
$licenses->addAttribute("page", $this->pagination_page);
|
||
|
|
$licenses->addAttribute("pages", $totalPages);
|
||
|
|
$licenses->addAttribute("limit", $this->pagination_size);
|
||
|
|
$licenses->addAttribute("type", "commercial");
|
||
|
|
|
||
|
|
$_SESSION[self::PAGINATION_PAGE] = $this->pagination_page;
|
||
|
|
|
||
|
|
return $licenses;
|
||
|
|
}
|
||
|
|
|
||
|
|
// ===============
|
||
|
|
// Search Licenses
|
||
|
|
// ===============
|
||
|
|
|
||
|
|
private function getMultiSelectInput($name) {
|
||
|
|
|
||
|
|
$value = $_POST[$name] ?? null;
|
||
|
|
|
||
|
|
if ($value === null) {
|
||
|
|
return null;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!is_array($value)) {
|
||
|
|
$value = explode(",", $value);
|
||
|
|
}
|
||
|
|
|
||
|
|
$value = array_values(array_filter(array_map("trim", $value), function ($item) {
|
||
|
|
return $item !== "";
|
||
|
|
}));
|
||
|
|
|
||
|
|
return empty($value) ? null : $value;
|
||
|
|
}
|
||
|
|
|
||
|
|
private function getDropdownValuesBySerial($connection, $serials) {
|
||
|
|
|
||
|
|
if (empty($serials)) {
|
||
|
|
return [];
|
||
|
|
}
|
||
|
|
|
||
|
|
$serials = array_values(array_filter(array_map("intval", $serials), function ($serial) {
|
||
|
|
return $serial > 0;
|
||
|
|
}));
|
||
|
|
|
||
|
|
if (empty($serials)) {
|
||
|
|
return [];
|
||
|
|
}
|
||
|
|
|
||
|
|
$placeholders = [];
|
||
|
|
$params = [];
|
||
|
|
|
||
|
|
foreach ($serials as $index => $serial) {
|
||
|
|
$placeholder = ":dropdown_serial_{$index}";
|
||
|
|
$placeholders[] = $placeholder;
|
||
|
|
$params[$placeholder] = $serial;
|
||
|
|
}
|
||
|
|
|
||
|
|
$SQL = "select dropdown_value
|
||
|
|
from dropdowns
|
||
|
|
where dropdown_serial in (" . implode(", ", $placeholders) . ")";
|
||
|
|
|
||
|
|
$statement = $connection->prepare($SQL);
|
||
|
|
|
||
|
|
foreach ($params as $key => $value) {
|
||
|
|
$statement->bindValue($key, $value, PDO::PARAM_INT);
|
||
|
|
}
|
||
|
|
|
||
|
|
$statement->execute();
|
||
|
|
|
||
|
|
$values = $statement->fetchAll(PDO::FETCH_COLUMN);
|
||
|
|
|
||
|
|
return empty($values) ? ["__NO_MATCHING_DROPDOWN_VALUE__"] : $values;
|
||
|
|
}
|
||
|
|
|
||
|
|
private function addInFilter(&$where, &$params, $column, $name, $values) {
|
||
|
|
|
||
|
|
if (empty($values)) {
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
$placeholders = [];
|
||
|
|
|
||
|
|
foreach ($values as $index => $value) {
|
||
|
|
$placeholder = ":{$name}_{$index}";
|
||
|
|
$placeholders[] = $placeholder;
|
||
|
|
$params[$placeholder] = $value;
|
||
|
|
}
|
||
|
|
|
||
|
|
$where[] = "{$column} IN (" . implode(", ", $placeholders) . ")";
|
||
|
|
}
|
||
|
|
|
||
|
|
// ========================
|
||
|
|
// Return a Licenses Object
|
||
|
|
// ========================
|
||
|
|
|
||
|
|
public function getSelectedLicenses($licenses = array()) {
|
||
|
|
|
||
|
|
if (empty($licenses)) {
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
|
||
|
|
$SQL = "select fwc_recreational_licenses.* from fwc_recreational_licenses where license_serial in ({$licenses})";
|
||
|
|
|
||
|
|
return $this->getTable("licenses", $SQL);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
?>
|