Ecobuddy/Models/FacilityDataSet.php

<?php
require_once ('Database.php');
require_once ('FacilityData.php');

class FacilityDataSet
{
    protected $_dbHandle, $_dbInstance;

    public function __construct()
    {
        $this->_dbInstance = Database::getInstance();
        $this->_dbHandle = $this->_dbInstance->getDbConnection();
    }

    /**
     * @param $data
     * @return bool
     * Broken last minute, dont have time to fix.
     * add / update facility to database from array of columns
     */
    public function addFacility($data): bool
    {
        $userQuery = "
            SELECT ecoUser.id FROM ecoUser
            WHERE ecoUser.username = :contributor;
        ";
        $catQuery = "
            SELECT ecoCategories.id FROM ecoCategories
            WHERE ecoCategories.name = :category;
        ";
        $sqlQuery = "
            INSERT OR REPLACE INTO ecoFacilities
                (id,
                 title,
                 category,
                 description,
                 houseNumber,
                 streetName,
                 county,
                 town,
                 postcode,
                 lng,
                 lat,
                 contributor)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, -1, -1, ?)
        ;";

        // gets contributor name
        $stmt = $this->_dbHandle->prepare($userQuery);
        $stmt->bindParam(':contributor', $data->contributor, PDO::PARAM_STR);
        $stmt = $this->_dbHandle->prepare($userQuery);
        $stmt->execute();
        $data['contributor'] = (int)$stmt->fetch(PDO::FETCH_ASSOC);

        // gets category ID
        $stmt = $this->_dbHandle->prepare($catQuery);
        $stmt->bindParam(':category', $data->category, PDO::PARAM_STR);
        $stmt = $this->_dbHandle->prepare($catQuery);
        $stmt->execute();
        $data['category'] = (int)$stmt->fetch(PDO::FETCH_ASSOC);

        // run main query and bind updated parameters
        $stmt = $this->_dbHandle->prepare($sqlQuery);
        // Ensures only one value is returned per column name
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);
        if (isset($data['id'])) {
            $stmt->bindParam(1, $data['id']);
        }
        $stmt->bindParam(2, $data['title'], PDO::PARAM_STR);
        $stmt->bindParam(3, $data['category'], PDO::PARAM_INT);
        $stmt->bindParam(4, $data['description'], PDO::PARAM_STR);
        $stmt->bindParam(5, $data['houseNumber'], PDO::PARAM_STR);
        $stmt->bindParam(6, $data['streetName'], PDO::PARAM_STR);
        $stmt->bindParam(7, $data['county'], PDO::PARAM_STR);
        $stmt->bindParam(8, $data['town'], PDO::PARAM_STR);
        $stmt->bindParam(9, $data['postcode'], PDO::PARAM_STR);
        $stmt->bindParam(10, $data['contributor'], PDO::PARAM_INT);
        $stmt->execute();
//        var_dump($stmt);
//        var_dump($this->_dbHandle->errorInfo());
        return !($stmt->rowCount());
    }


    /**
     * @param $id
     * @return bool
     * Deletes Facility Records being passed a facility id.
     */
    public function deleteFacility($id): bool
    {
        $sqlQuery = "DELETE FROM ecoFacilities WHERE ecoFacilities.id = :id;";
        $stmt = $this->_dbHandle->prepare($sqlQuery);
        $stmt->bindValue(':id', (int)$id, \PDO::PARAM_INT);
        $stmt->execute();
        var_dump($stmt);
        echo $stmt->rowCount();
        return !($stmt->rowCount() == 0);
    }


    /**
     * @param $filterArray
     * @param $sortArray
     * @return array
     * Fetch all records depending on filters, and sort by defined column
     */
    public function fetchAll($filterArray, $sortArray): array
    {
        // Define columns for filtering and sorting
        $filterColumns = [
            0 => 'ecoFacilityStatus.statusComment',
            1 => 'ecoFacilities.title',
            2 => 'ecoCategories.name',
            3 => 'ecoFacilities.description',
            4 => 'ecoFacilities.streetName',
            5 => 'ecoFacilities.county',
            6 => 'ecoFacilities.town',
            7 => 'ecoFacilities.postcode',
            8 => 'ecoUser.username'
        ];

        $sortColumns = [
            0 => 'ecoFacilityStatus.statusComment',
            1 => 'ecoFacilities.title',
            2 => 'ecoCategories.name',
            3 => 'ecoFacilities.description',
            4 => 'ecoFacilities.streetName',
            5 => 'ecoFacilities.county',
            6 => 'ecoFacilities.town',
            7 => 'ecoFacilities.postcode',
            8 => 'ecoUser.username'
        ];

        // Validate and select the filter column
        $selectedFilterColumn = $filterColumns[$filterArray['category']] ?? 'ecoFacilities.title';
        // Validate and select the sort column
        $selectedSortColumn = $sortColumns[$sortArray['sort']] ?? 'ecoFacilities.title';
        // Validate sort direction
        $direction = strtolower($sortArray['dir']) === 'desc' ? 'DESC' : 'ASC';
        // Base query for filtering and sorting
        $baseQuery = "
        FROM ecoFacilities
        LEFT JOIN ecoCategories ON ecoCategories.id = ecoFacilities.category
        LEFT JOIN ecoUser ON ecoUser.id = ecoFacilities.contributor
        LEFT JOIN ecoFacilityStatus ON ecoFacilityStatus.facilityid = ecoFacilities.id
        WHERE {$selectedFilterColumn} LIKE :term
    ";


        // Query to count total results
        $countQuery = "SELECT COUNT(DISTINCT ecoFacilities.id) AS total {$baseQuery}";

        // Query to fetch filtered and sorted results
        $dataQuery = "
        SELECT DISTINCT ecoFacilities.id, 
                        ecoFacilities.title, 
                        GROUP_CONCAT(ecoFacilityStatus.statusComment, ', ') AS status, 
                        ecoCategories.name AS category, 
                        ecoFacilities.description, 
                        ecoFacilities.houseNumber, 
                        ecoFacilities.streetName, 
                        ecoFacilities.county, 
                        ecoFacilities.town, 
                        ecoFacilities.postcode, 
                        ecoFacilities.lng, 
                        ecoFacilities.lat, 
                        ecoUser.username AS contributor
        {$baseQuery}
        GROUP BY ecoFacilities.id, ecoFacilities.title, ecoCategories.name, 
                 ecoFacilities.description, ecoFacilities.streetName,
                 ecoFacilities.county, ecoFacilities.town, ecoFacilities.postcode, 
                 ecoUser.username
        ORDER BY {$selectedSortColumn} {$direction};
    ";
        // Surround 'term' with % to allow usage with LIKE
        $filterArray['term'] = '%' . $filterArray['term'] . '%' ?? '%';
        // Prepare and execute the count query
        $countStmt = $this->_dbHandle->prepare($countQuery);
        $countStmt->bindValue(':term', $filterArray['term'], PDO::PARAM_STR);
        $countStmt->execute();
        // Set total results to output of count statement
        $totalResults = (int)$countStmt->fetchColumn();

        // Prepare and execute the data query
        $dataStmt = $this->_dbHandle->prepare($dataQuery);
        $dataStmt->bindValue(':term', $filterArray['term'], PDO::PARAM_STR);
        $dataStmt->execute();

        // Fetch results into FacilityData objects
        $dataSet = [];
        while ($row = $dataStmt->fetch()) {
            $dataSet[] = new FacilityData($row);
        }

        return [
            'dataset' => $dataSet,
            'count' => $totalResults
        ];
    }
}