atlhousingreport/classes/PermitsBySubdivision.php

<?php

class PermitsBySubdivision 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 searchPermits($subscription_serial = 0) {

        $subscription_serial = filter_input(INPUT_POST, "subscription_serial") ?: $subscription_serial;

        $subscription = (new Subscriptions())->getSubscription($subscription_serial);

        if ($subscription->count() == 0) {
            $this->logError("Invalid Subscription ({$subscription_serial}). " . __METHOD__);
        }

        $permit_area = $subscription->record->subscription_area;
        $projecttype = $subscription->record->subscription_projecttype;

        $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->getPermits($permit_area, $projecttype);

        $searchhistory = (new SearchHistory())->getUsersPreviousSearchParameters($this->current_user_serial, $subscription_serial);

        $counties = (new Counties())->getCountiesByArea($permit_area);
        $popovers = (new Popovers())->getPopovers();

        $XML = $this->mergeXML($permits, "<XML/>");
        $XML = $this->mergeXML($counties, $XML);
        $XML = $this->mergeXML($subscription, $XML);
        $XML = $this->mergeXML($searchhistory, $XML);
        $XML = $this->mergeXML($popovers, $XML);

        $content = $this->applyXSL($XML, $this->getXSL("searchPermitsBySubdivision"));
        $this->displayContent($content);
    }

    public function getPermits($permit_area, $projecttype) {

        $permit_county_raw = trim(filter_input(INPUT_POST, "permit_county") ?: "");
        $permit_and_or = trim(filter_input(INPUT_POST, "permit_and_or") ?: "OR");
        $permit_company = trim(filter_input(INPUT_POST, "permit_company") ?: "");
        $permit_sub_div_name = trim(filter_input(INPUT_POST, "permit_sub_div_name") ?: "");
        $permit_project_city = trim(filter_input(INPUT_POST, "permit_project_city") ?: "");
        $permit_project_addr = trim(filter_input(INPUT_POST, "permit_project_addr") ?: "");

        $permit_entry_start_date = filter_input(INPUT_POST, "permit_entry_start_date") ?: "";
        $permit_entry_end_date = filter_input(INPUT_POST, "permit_entry_end_date") ?: "";
        $permit_permit_start_date = filter_input(INPUT_POST, "permit_permit_start_date") ?: "";
        $permit_permit_end_date = filter_input(INPUT_POST, "permit_permit_end_date") ?: "";

        $pagination = filter_input(INPUT_POST, "pagination") ?: "";

        // Normalize dates to Y-m-d or null
        $permit_entry_start_date = ($permit_entry_start_date === "") ? null : date("Y-m-d", strtotime($permit_entry_start_date));
        $permit_entry_end_date = ($permit_entry_end_date === "") ? null : date("Y-m-d", strtotime($permit_entry_end_date));
        $permit_permit_start_date = ($permit_permit_start_date === "") ? null : date("Y-m-d", strtotime($permit_permit_start_date));
        $permit_permit_end_date = ($permit_permit_end_date === "") ? null : date("Y-m-d", strtotime($permit_permit_end_date));

        // Validate AND/OR
        if (!in_array(strtoupper($permit_and_or), ["AND", "OR"], true)) {
            $permit_and_or = "OR";
        }

        // Pagination / ordering
        $this->pagination_size = filter_input(INPUT_POST, "permits_per_page", FILTER_SANITIZE_NUMBER_INT) ?: $this->pagination_size;
        $permits_order_by = filter_input(INPUT_POST, "permits_order_by") ?: "permit_permit_number";
        $permits_order_direction = filter_input(INPUT_POST, "permits_order_direction" ?? "DESC");

        $allowed_order_columns = [
            "permit_permit_number",
            "permit_county", "permit_sub_div_name", "permit_project_addr", "permit_city",
            "permit_state", "permit_zip", "permit_company", "permit_permit_date", "permit_size", "permit_value"
        ];

        if (!in_array($permits_order_by, $allowed_order_columns, true)) {
            $permits_order_by = "permit_sub_div_name";
        }
        if (!in_array(strtoupper((string) $permits_order_direction), ["ASC", "DESC"], true)) {
            $permits_order_direction = "DESC";
        }

        // Database Connection
        $connection = $this->connect();

        // -------- County clause (shared) --------
        // Parse "1,12" -> [1,12], keep only digits, cast to int
        $countyIds = array_values(array_filter(
                        array_map(
                                fn($v) => ctype_digit(trim($v)) ? (int) trim($v) : null,
                                explode(",", $permit_county_raw)
                        ),
                        fn($v) => $v !== null
                ));

        $countyClauseCount = "";
        $countyClauseData = "";
        $countyParams = [];  // [':county_0' => 1, ':county_1' => 12]
        $bindArea = false;

        if (!empty($countyIds)) {
            if (count($countyIds) === 1) {
                $countyClauseCount = " and permit_county = :county_0";
                $countyClauseData = " and view_permits.permit_county = :county_0";
                $countyParams[':county_0'] = $countyIds[0];
            } else {
                $ph = [];
                foreach ($countyIds as $i => $id) {
                    $k = ":county_$i";
                    $ph[] = $k;
                    $countyParams[$k] = $id;
                }
                $in = implode(",", $ph);
                $countyClauseCount = " and permit_county in ($in)";
                $countyClauseData = " and view_permits.permit_county in ($in)";
            }
        } elseif ($permit_area != "All") {
            $countyClauseCount = " and permit_county in (select county_serial from view_counties where county_area = :permit_area)";
            $countyClauseData = " and view_permits.permit_county in (select county_serial from view_counties where county_area = :permit_area)";
            $bindArea = true;
        } else {
            // No county/area filter -> omit entirely to keep the plan simple
            $countyClauseCount = "";
            $countyClauseData = "";
        }

        // ===================== COUNT QUERY =====================
        $SQL = "select count(*) as count
                  from view_permits
                 where permit_project_type = :permit_project_type
                 $countyClauseCount ";

        if (!empty($permit_company)) {
            $SQL .= " and permit_company like :permit_company";
        }

        if (!empty($permit_sub_div_name)) {
            $SQL .= " and permit_sub_div_name like :permit_sub_div_name";
        }

        if (!empty($permit_project_city)) {
            $SQL .= " and permit_project_city like :permit_project_city";
        }

        if (!empty($permit_project_addr)) {
            $SQL .= " and permit_project_addr like :permit_project_addr";
        }

        // group date conditions so $permit_and_or applies between themcx

        $dateConds = [];

        if ($permit_entry_start_date && $permit_entry_end_date) {
            $dateConds[] = "(permit_entry_date between :permit_entry_start_date and :permit_entry_end_date)";
        } elseif ($permit_entry_start_date) {
            $dateConds[] = "(permit_entry_date >= :permit_entry_start_date)";
        }
        if ($permit_permit_start_date && $permit_permit_end_date) {
            $dateConds[] = "(permit_permit_date between :permit_permit_start_date and :permit_permit_end_date)";
        } elseif ($permit_permit_start_date) {
            $dateConds[] = "(permit_permit_date >= :permit_permit_start_date)";
        }
        if ($dateConds) {
            $SQL .= " and (" . implode(" $permit_and_or ", $dateConds) . ")";
        }

        $statement = $connection->prepare($SQL);

        $statement->bindValue(":permit_project_type", $projecttype, PDO::PARAM_STR);

        foreach ($countyParams as $k => $v) {
            $statement->bindValue($k, $v, PDO::PARAM_INT);
        }

        if ($bindArea) {
            $statement->bindValue(":permit_area", $permit_area, PDO::PARAM_STR);
        }
        if (!empty($permit_company)) {
            $statement->bindValue(":permit_company", '%' . $permit_company . '%', PDO::PARAM_STR);
        }
        if (!empty($permit_sub_div_name)) {
            $statement->bindValue(":permit_sub_div_name", '%' . $permit_sub_div_name . '%', PDO::PARAM_STR);
        }
        if (!empty($permit_project_city)) {
            $statement->bindValue(":permit_project_city", '%' . $permit_project_city . '%', PDO::PARAM_STR);
        }
        if (!empty($permit_project_addr)) {
            $statement->bindValue(":permit_project_addr", '%' . $permit_project_addr . '%', PDO::PARAM_STR);
        }
        if ($permit_entry_start_date) {
            $statement->bindValue(":permit_entry_start_date", $permit_entry_start_date);
        }
        if ($permit_entry_end_date && str_contains($SQL, ":permit_entry_end_date")) {
            $statement->bindValue(":permit_entry_end_date", $permit_entry_end_date);
        }
        if ($permit_permit_start_date) {
            $statement->bindValue(":permit_permit_start_date", $permit_permit_start_date);
        }
        if ($permit_permit_end_date && str_contains($SQL, ":permit_permit_end_date")) {
            $statement->bindValue(":permit_permit_end_date", $permit_permit_end_date);
        }


        $statement->execute();

        $recordset = $statement->fetch(PDO::FETCH_ASSOC);

        $count = $recordset ? (int) $recordset["count"] : 0;

        // -------- Pagination --------

        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 =====================
        $SQL = "select view_permits.*
                  from view_permits
                 where view_permits.permit_project_type = :permit_project_type
                   $countyClauseData";

        if (!empty($permit_company)) {
            $SQL .= " and view_permits.permit_company like :permit_company";
        }

        if (!empty($permit_sub_div_name)) {
            $SQL .= " and view_permits.permit_sub_div_name like :permit_sub_div_name";
        }

        if (!empty($permit_project_city)) {
            $SQL .= " and view_permits.permit_project_city like :permit_project_city";
        }

        if (!empty($permit_project_addr)) {
            $SQL .= " and view_permits.permit_project_addr like :permit_project_addr";
        }

        $dateConds = [];
        if ($permit_entry_start_date && $permit_entry_end_date) {
            $dateConds[] = "(view_permits.permit_entry_date between :permit_entry_start_date and :permit_entry_end_date)";
        } elseif ($permit_entry_start_date) {
            $dateConds[] = "(view_permits.permit_entry_date >= :permit_entry_start_date)";
        }
        if ($permit_permit_start_date && $permit_permit_end_date) {
            $dateConds[] = "(view_permits.permit_permit_date between :permit_permit_start_date and :permit_permit_end_date)";
        } elseif ($permit_permit_start_date) {
            $dateConds[] = "(view_permits.permit_permit_date >= :permit_permit_start_date)";
        }
        if ($dateConds) {
            $SQL .= " and (" . implode(" $permit_and_or ", $dateConds) . ")";
        }

        $SQL .= " order by $permits_order_by $permits_order_direction
                     limit :limit
                    offset :offset";

        $statement = $connection->prepare($SQL);

        $statement->bindValue(":permit_project_type", $projecttype, PDO::PARAM_STR);

        foreach ($countyParams as $k => $v) {
            $statement->bindValue($k, $v, PDO::PARAM_INT);
        }

        if ($bindArea) {
            $statement->bindValue(":permit_area", $permit_area, PDO::PARAM_STR);
        }
        if (!empty($permit_company)) {
            $statement->bindValue(":permit_company", '%' . $permit_company . '%', PDO::PARAM_STR);
        }
        if (!empty($permit_sub_div_name)) {
            $statement->bindValue(":permit_sub_div_name", '%' . $permit_sub_div_name . '%', PDO::PARAM_STR);
        }
        if (!empty($permit_project_city)) {
            $statement->bindValue(":permit_project_city", '%' . $permit_project_city . '%', PDO::PARAM_STR);
        }
        if (!empty($permit_project_addr)) {
            $statement->bindValue(":permit_project_addr", '%' . $permit_project_addr . '%', PDO::PARAM_STR);
        }
        if ($permit_entry_start_date) {
            $statement->bindValue(":permit_entry_start_date", $permit_entry_start_date);
        }
        if ($permit_entry_end_date && str_contains($SQL, ":permit_entry_end_date")) {
            $statement->bindValue(":permit_entry_end_date", $permit_entry_end_date);
        }
        if ($permit_permit_start_date) {
            $statement->bindValue(":permit_permit_start_date", $permit_permit_start_date);
        }
        if ($permit_permit_end_date && str_contains($SQL, ":permit_permit_end_date")) {
            $statement->bindValue(":permit_permit_end_date", $permit_permit_end_date);
        }
        $statement->bindValue(":limit", $this->pagination_size, PDO::PARAM_INT);
        $statement->bindValue(":offset", $offset, PDO::PARAM_INT);

//        echo $projecttype;
//        die();

        $statement->execute();

        $permits = $this->getTableXML("permits", $statement);

        foreach ($permits as $permit) {
            if (!empty($permit->permit_county)) {
                $county = (new Counties())->getCounty($permit->permit_county);
                $permit->permit_county_name_verbose = $county->record->county_name ?? null;
            }
        }

        // Pagination attributes
        $permits->addAttribute("count", $count);
        $permits->addAttribute("start", $start);
        $permits->addAttribute("end", $end);
        $permits->addAttribute("page", $this->pagination_page);
        $_SESSION[self::PAGINATION_PAGE] = $this->pagination_page;

        return $permits;
    }

    // =============================
    // Fetch All Permits for Exports
    // =============================

    public function getAllPermitsBySubDivision($permit_area, $projecttype) {

        $connection = $this->connect();

        // Count Query
        $SQL = "select count(*) as count
                  from view_permits r 
                  join counties c on r.permit_county = c.county_serial 
                 where r.permit_project_type = :permit_project_type 
                   and r.permit_county in 
                        (select county_serial 
                           from view_counties 
                          where county_area = :permit_area)";

        $statement = $connection->prepare($SQL);

        $statement->bindValue(":permit_project_type", $projecttype, PDO::PARAM_STR);
        $statement->bindValue(":permit_area", $permit_area, PDO::PARAM_STR);

        $statement->execute();
        $recordset = $statement->fetch(PDO::FETCH_ASSOC);
        $count = $recordset ? (int) $recordset["count"] : 0;

        // === DATA QUERY ===
        $SQL = "select r.*, c.county_name as permit_county_name_verbose 
                  from view_permits r 
                  join counties c on r.permit_county = c.county_serial 
                 where r.permit_project_type = :permit_project_type 
                   and r.permit_county in 
                        (select county_serial 
                           from view_counties 
                          where county_area = :permit_area)";

        $statement = $connection->prepare($SQL);

        $statement->bindValue(":permit_project_type", $projecttype, PDO::PARAM_STR);
        $statement->bindValue(":permit_area", $permit_area, PDO::PARAM_STR);

        $statement->execute();

        $permits = $this->getTableXML("permits", $statement);

        // Insert Pagination Attributes
        $permits->addAttribute("count", $count);

        return $permits;
    }

    // ======================
    // Return a Permit Object
    // ======================

    public function getSelectedPermits($permits = array()) {

        if (empty($permits)) {
            return;
        }

        $SQL = "select view_permits.* from view_permits where permit_serial in ({$permits})";

        return $this->getTable("permits", $SQL);
    }

//    // ================================
//    // Return AutoComplete Names (ajax)
//    // ================================
//
//    public function getAutoCompleteSubdivisions() {
//
//        $this->debug($_GET);
//
//        $results = array();
//
//        $subscription_serial = filter_input(INPUT_GET, "subscription_serial") ?: "";
//
//        $subscription = (new Subscriptions())->getSubscription($subscription_serial);
//
//        if ($subscription->count() == 0) {
//            $this->logError("Invalid Subscription ({$subscription_serial}). " . __METHOD__);
//        }
//
//        $permit_area = $subscription->record->subscription_area;
//        $projecttype = $subscription->record->subscription_projecttype;
//
//        $subdivision = filter_input(INPUT_GET, "permit_sub_div_name") ?: "";
//        $subdivision = trim(preg_replace("/\s+/", " ", $subdivision));
//        $subdivision = preg_replace("/'+/", "'", $subdivision);
//        $subdivision = preg_replace('/"+/', '"', $subdivision);
//        $subdivision = str_replace("'", "''", $subdivision);
//
//        $SQL = "select distinct permit_sub_div_name from view_permits where permit_project_type = '{$projecttype}' and permit_sub_div_name like '%{$subdivision}%'";
//
//        if ($permit_area != "All") {
//            $SQL .= " and view_permits.permit_county in (select county_serial from view_counties where county_area = '{$permit_area}')";
//        }
//
//        $SQL .= "order by permit_sub_div_name limit 20 offset 0";
//
//        $matches = $this->getTable("subdivisions", $SQL);
//
//        foreach ($matches as $match) {
//            $results[] = (string) $match->permit_sub_div_name;
//        }
//
//        header('Content-Type: application/json');
//        echo json_encode($results);
//        exit();
//    }
    // ================================
    // Return AutoComplete Names (ajax)
    // ================================

    public function getAutoCompleteSubdivisions() {

        $results = [];

        $subscription_serial = filter_input(INPUT_GET, "subscription_serial", FILTER_SANITIZE_NUMBER_INT) ?: 0;
        $subdivision = filter_input(INPUT_GET, "permit_sub_div_name", FILTER_SANITIZE_SPECIAL_CHARS) ?: "";

        $subscription = (new Subscriptions())->getSubscription($subscription_serial);

        if ($subscription->count() == 0) {
            $this->logError("Invalid Subscription ({$subscription_serial}). " . __METHOD__);

            header("Content-Type: application/json");
            echo json_encode($results);
            exit();
        }

        $permit_area = $subscription->record->subscription_area;
        $projecttype = $subscription->record->subscription_projecttype;

        $subdivision = trim(preg_replace("/\s+/", " ", $subdivision));

        $SQL = "select distinct permit_sub_div_name
                  from view_permits
                 where permit_project_type = :projecttype
                   and permit_sub_div_name like :subdivision";

        if ($permit_area != "All") {
            $SQL .= " and permit_county in (
                      select county_serial
                        from view_counties
                       where county_area = :permit_area
                  )";
        }

        $SQL .= " order by permit_sub_div_name limit 20 offset 0";

        $statement = $this->connect()->prepare($SQL);

        $statement->bindValue(":projecttype", $projecttype, PDO::PARAM_STR);
        $statement->bindValue(":subdivision", "%{$subdivision}%", PDO::PARAM_STR);

        if ($permit_area != "All") {
            $statement->bindValue(":permit_area", $permit_area, PDO::PARAM_STR);
        }

        $statement->execute();

        while ($record = $statement->fetch(PDO::FETCH_OBJ)) {
            $results[] = (string) $record->permit_sub_div_name;
        }

        header("Content-Type: application/json");
        echo json_encode($results);
        exit();
    }

    // ================================
    // Return AutoComplete Names (ajax)
    // ================================

    public function getAutoCompleteCities() {

        $results = array();

        $city = filter_input(INPUT_GET, "permit_project_city") ?: "";
        $city = trim(preg_replace("/\s+/", " ", $city));
        $city = preg_replace("/'+/", "'", $city);
        $city = preg_replace('/"+/', '"', $city);
        $city = str_replace("'", "''", $city);

        $SQL = "select distinct permit_project_city from view_permits where permit_project_city like '%{$city}%' order by permit_project_city limit 10 offset 0";

        $matches = $this->getTable("cities", $SQL);

        foreach ($matches as $match) {
            $results[] = (string) $match->permit_project_city;
        }

        header('Content-Type: application/json');
        echo json_encode($results);
        exit();
    }

    // ================================
    // Return AutoComplete Names (ajax)
    // ================================

    public function getAutoCompleteStreets() {

        $results = array();

        $street = filter_input(INPUT_GET, "permit_project_addr") ?: "";
        $street = trim(preg_replace("/\s+/", " ", $street));
        $street = preg_replace("/'+/", "'", $street);
        $street = preg_replace('/"+/', '"', $street);
        $street = str_replace("'", "''", $street);

        $SQL = "select distinct permit_project_addr from view_permits where permit_project_addr like '%{$street}%' order by permit_project_addr limit 10 offset 0";

        $matches = $this->getTable("cities", $SQL);

        foreach ($matches as $match) {
            $results[] = (string) $match->permit_project_addr;
        }

        header('Content-Type: application/json');
        echo json_encode($results);
        exit();
    }

    // ======================
    // Return a Permit Object
    // ======================

    public function getPermit($permit_serial = 0) {

        $SQL = "select view_permits.* from view_permits where permit_serial = {$permit_serial}";

        return $this->getTable("permit", $SQL);
    }

    // =============================
    // Return a Permit Record Dialog
    // =============================

    public function getPermitRecordDialog() {

        $permit_serial = filter_input(INPUT_POST, "permit_serial", FILTER_SANITIZE_NUMBER_INT) ?: 9;

        $permit_record = $this->getPermit($permit_serial);

        $permit_county = (new Counties())->getCounty($permit_record->record->permit_county);
        $permit_record->record->permit_county = $permit_county->record->county_name;

        $content = $this->applyXSL($permit_record, $this->getXSL("permitRecordDialog"));

        if ($_SESSION[self::AJAX]) {
            echo $content;
        } else {
            $this->displayContentPage($content);
        }
    }
}

?>