463 lines
18 KiB
PHP
Executable File
463 lines
18 KiB
PHP
Executable File
<?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 $id
|
|
* @return bool
|
|
* Deletes Facility Records being passed a facility id.
|
|
*/
|
|
public function deleteFacility($id): bool
|
|
{
|
|
try {
|
|
// Start transaction
|
|
$this->_dbHandle->beginTransaction();
|
|
|
|
// Delete related status records first
|
|
$statusQuery = "DELETE FROM ecoFacilityStatus WHERE facilityid = :id;";
|
|
$statusStmt = $this->_dbHandle->prepare($statusQuery);
|
|
$statusStmt->bindValue(':id', (int)$id, \PDO::PARAM_INT);
|
|
$statusStmt->execute();
|
|
|
|
// Delete the facility
|
|
$facilityQuery = "DELETE FROM ecoFacilities WHERE id = :id;";
|
|
$facilityStmt = $this->_dbHandle->prepare($facilityQuery);
|
|
$facilityStmt->bindValue(':id', (int)$id, \PDO::PARAM_INT);
|
|
$facilityStmt->execute();
|
|
|
|
// Commit transaction
|
|
$this->_dbHandle->commit();
|
|
return $facilityStmt->rowCount() > 0;
|
|
} catch (PDOException $e) {
|
|
// Rollback on error
|
|
$this->_dbHandle->rollBack();
|
|
error_log("Error deleting facility: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return array|false Returns array of facilities or false on error
|
|
* Fetch all facility records with related data
|
|
*/
|
|
public function fetchAll(): array|false
|
|
{
|
|
try {
|
|
error_log('Starting fetchAll...');
|
|
|
|
$query = "
|
|
SELECT DISTINCT ecoFacilities.id,
|
|
ecoFacilities.title,
|
|
COALESCE(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,
|
|
COALESCE(ecoUser.username, 'Unknown') AS contributor
|
|
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
|
|
GROUP BY ecoFacilities.id, ecoFacilities.title, ecoCategories.name,
|
|
ecoFacilities.description, ecoFacilities.streetName,
|
|
ecoFacilities.county, ecoFacilities.town, ecoFacilities.postcode,
|
|
ecoUser.username
|
|
ORDER BY ecoFacilities.id ASC;
|
|
";
|
|
|
|
error_log('Preparing query...');
|
|
$dataStmt = $this->_dbHandle->prepare($query);
|
|
|
|
error_log('Executing query...');
|
|
$dataStmt->execute();
|
|
|
|
error_log('Fetching results...');
|
|
$results = $dataStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if ($results === false) {
|
|
error_log('Query returned false');
|
|
return false;
|
|
}
|
|
|
|
error_log('Query successful. Row count: ' . count($results));
|
|
return $results;
|
|
|
|
} catch (PDOException $e) {
|
|
error_log("Database error in fetchAll: " . $e->getMessage());
|
|
error_log("SQL State: " . $e->getCode());
|
|
error_log("Stack trace: " . $e->getTraceAsString());
|
|
return false;
|
|
} catch (Exception $e) {
|
|
error_log("General error in fetchAll: " . $e->getMessage());
|
|
error_log("Stack trace: " . $e->getTraceAsString());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Creates a new facility in the database
|
|
* @param array $data Facility data
|
|
* @return array|false The created facility data or false on failure
|
|
*/
|
|
public function createFacility($data)
|
|
{
|
|
try {
|
|
$this->_dbHandle->beginTransaction();
|
|
|
|
// Validate coordinates
|
|
if (!is_numeric($data['lng']) || !is_numeric($data['lat']) ||
|
|
$data['lng'] < -180 || $data['lng'] > 180 ||
|
|
$data['lat'] < -90 || $data['lat'] > 90) {
|
|
throw new Exception('Invalid coordinates provided');
|
|
}
|
|
|
|
// Get contributor ID
|
|
$contributorId = $this->getContributorId($data['contributor']);
|
|
if (!$contributorId) {
|
|
throw new Exception('Invalid contributor name');
|
|
}
|
|
|
|
// Get category ID
|
|
$categoryId = $this->getCategoryId($data['category']);
|
|
if (!$categoryId) {
|
|
// If category doesn't exist, create it
|
|
$categoryId = $this->createCategory($data['category']);
|
|
if (!$categoryId) {
|
|
throw new Exception('Failed to create category: ' . $data['category']);
|
|
}
|
|
}
|
|
|
|
// Insert facility
|
|
$sql = "INSERT INTO ecoFacilities (title, category, description, houseNumber,
|
|
streetName, county, town, postcode, lng, lat, contributor)
|
|
VALUES (:title, :category, :description, :houseNumber,
|
|
:streetName, :county, :town, :postcode, :longitude, :latitude, :contributor)";
|
|
|
|
$stmt = $this->_dbHandle->prepare($sql);
|
|
$params = [
|
|
':title' => $data['title'],
|
|
':category' => $categoryId,
|
|
':description' => $data['description'],
|
|
':houseNumber' => $data['houseNumber'],
|
|
':streetName' => $data['streetName'],
|
|
':county' => $data['county'],
|
|
':town' => $data['town'],
|
|
':postcode' => $data['postcode'],
|
|
':longitude' => $data['lng'],
|
|
':latitude' => $data['lat'],
|
|
':contributor' => $contributorId
|
|
];
|
|
|
|
error_log("Executing SQL with params: " . print_r($params, true));
|
|
|
|
if (!$stmt->execute($params)) {
|
|
throw new Exception('Failed to insert facility: ' . implode(', ', $stmt->errorInfo()));
|
|
}
|
|
|
|
$facilityId = $this->_dbHandle->lastInsertId();
|
|
$this->_dbHandle->commit();
|
|
|
|
// Return the created facility
|
|
return $this->getFacilityById($facilityId);
|
|
} catch (Exception $e) {
|
|
$this->_dbHandle->rollBack();
|
|
error_log("Error in createFacility: " . $e->getMessage());
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
private function createCategory($categoryName)
|
|
{
|
|
try {
|
|
$sql = "INSERT INTO ecoCategories (name) VALUES (:name)";
|
|
$stmt = $this->_dbHandle->prepare($sql);
|
|
$stmt->execute([':name' => $categoryName]);
|
|
return $this->_dbHandle->lastInsertId();
|
|
} catch (Exception $e) {
|
|
error_log("Error creating category: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Updates an existing facility in the database
|
|
* @param int $id Facility ID
|
|
* @param array $data Updated facility data
|
|
* @return array|false The updated facility data or false on failure
|
|
*/
|
|
public function updateFacility($id, $data) {
|
|
try {
|
|
// Start transaction
|
|
$this->_dbHandle->beginTransaction();
|
|
|
|
// Validate coordinates
|
|
if (!is_numeric($data['lng']) || !is_numeric($data['lat']) ||
|
|
$data['lng'] < -180 || $data['lng'] > 180 ||
|
|
$data['lat'] < -90 || $data['lat'] > 90) {
|
|
throw new Exception('Invalid coordinates');
|
|
}
|
|
|
|
// Get Contributor ID
|
|
$query = "SELECT ecoUser.id FROM ecoUser WHERE ecoUser.username = :contributor;";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->bindValue(':contributor', $data['contributor']);
|
|
$stmt->execute();
|
|
$contributorResult = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$contributorResult) {
|
|
throw new Exception('Invalid contributor username');
|
|
}
|
|
$contributorId = $contributorResult['id'];
|
|
|
|
// Get Category ID
|
|
$query = "SELECT ecoCategories.id FROM ecoCategories WHERE ecoCategories.name = :category;";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->bindValue(':category', $data['category']);
|
|
$stmt->execute();
|
|
$categoryResult = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$categoryResult) {
|
|
throw new Exception('Invalid category name');
|
|
}
|
|
$categoryId = $categoryResult['id'];
|
|
|
|
// Update facility
|
|
$query = "
|
|
UPDATE ecoFacilities
|
|
SET title = :title,
|
|
category = :category,
|
|
description = :description,
|
|
houseNumber = :houseNumber,
|
|
streetName = :streetName,
|
|
county = :county,
|
|
town = :town,
|
|
postcode = :postcode,
|
|
lng = :lng,
|
|
lat = :lat,
|
|
contributor = :contributor
|
|
WHERE id = :id
|
|
";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$params = [
|
|
':title' => $data['title'],
|
|
':category' => $categoryId,
|
|
':description' => $data['description'],
|
|
':houseNumber' => $data['houseNumber'],
|
|
':streetName' => $data['streetName'],
|
|
':county' => $data['county'],
|
|
':town' => $data['town'],
|
|
':postcode' => $data['postcode'],
|
|
':lng' => $data['lng'],
|
|
':lat' => $data['lat'],
|
|
':contributor' => $contributorId,
|
|
':id' => $id
|
|
];
|
|
|
|
error_log("Executing update query with params: " . print_r($params, true));
|
|
|
|
if (!$stmt->execute($params)) {
|
|
throw new Exception('Failed to update facility: ' . implode(', ', $stmt->errorInfo()));
|
|
}
|
|
|
|
if ($stmt->rowCount() > 0) {
|
|
$this->_dbHandle->commit();
|
|
return $this->getFacilityById($id);
|
|
}
|
|
|
|
$this->_dbHandle->rollBack();
|
|
return false;
|
|
} catch (Exception $e) {
|
|
$this->_dbHandle->rollBack();
|
|
error_log("Error updating facility: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Gets a facility by its ID
|
|
* @param int $id Facility ID
|
|
* @return array|false The facility data or false if not found
|
|
*/
|
|
public function getFacilityById($id) {
|
|
try {
|
|
$query = "
|
|
SELECT DISTINCT ecoFacilities.id,
|
|
ecoFacilities.title,
|
|
COALESCE(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,
|
|
COALESCE(ecoUser.username, 'Unknown') AS contributor
|
|
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 ecoFacilities.id = ?
|
|
GROUP BY ecoFacilities.id, ecoFacilities.title, ecoCategories.name,
|
|
ecoFacilities.description, ecoFacilities.streetName,
|
|
ecoFacilities.county, ecoFacilities.town, ecoFacilities.postcode,
|
|
ecoUser.username;
|
|
";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch(PDO::FETCH_ASSOC);
|
|
} catch (PDOException $e) {
|
|
error_log("Error getting facility: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
private function getContributorId($username)
|
|
{
|
|
try {
|
|
$query = "SELECT ecoUser.id FROM ecoUser WHERE ecoUser.username = :username;";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->bindValue(':username', $username);
|
|
$stmt->execute();
|
|
$result = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
return $result ? $result['id'] : false;
|
|
} catch (Exception $e) {
|
|
error_log("Error getting contributor ID: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
private function getCategoryId($categoryName)
|
|
{
|
|
try {
|
|
$query = "SELECT ecoCategories.id FROM ecoCategories WHERE ecoCategories.name = :name;";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->bindValue(':name', $categoryName);
|
|
$stmt->execute();
|
|
$result = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
return $result ? $result['id'] : false;
|
|
} catch (Exception $e) {
|
|
error_log("Error getting category ID: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Adds a new status comment to a facility
|
|
* @param int $facilityId The ID of the facility
|
|
* @param string $statusComment The status comment to add
|
|
* @return bool True if successful, false otherwise
|
|
*/
|
|
public function addFacilityStatus($facilityId, $statusComment)
|
|
{
|
|
try {
|
|
// Log input parameters
|
|
error_log("Adding facility status - Facility ID: " . $facilityId . ", Comment: " . $statusComment);
|
|
|
|
// Start transaction
|
|
$this->_dbHandle->beginTransaction();
|
|
|
|
// Insert new status comment
|
|
$query = "INSERT INTO ecoFacilityStatus (facilityId, statusComment) VALUES (:facilityId, :statusComment)";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
|
|
// Log the prepared statement
|
|
error_log("Prepared statement: " . $query);
|
|
|
|
// Bind values and log them
|
|
$stmt->bindValue(':facilityId', (int)$facilityId, PDO::PARAM_INT);
|
|
$stmt->bindValue(':statusComment', $statusComment);
|
|
error_log("Bound values - Facility ID: " . (int)$facilityId . ", Comment: " . $statusComment);
|
|
|
|
if (!$stmt->execute()) {
|
|
$errorInfo = $stmt->errorInfo();
|
|
error_log("SQL Error: " . print_r($errorInfo, true));
|
|
throw new Exception('Failed to insert status comment: ' . implode(', ', $errorInfo));
|
|
}
|
|
|
|
$this->_dbHandle->commit();
|
|
error_log("Successfully added facility status");
|
|
return true;
|
|
} catch (Exception $e) {
|
|
$this->_dbHandle->rollBack();
|
|
error_log("Error adding facility status: " . $e->getMessage());
|
|
error_log("Stack trace: " . $e->getTraceAsString());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Gets all status comments for a facility
|
|
* @param int $facilityId The ID of the facility
|
|
* @return array Array of status comments with their IDs
|
|
*/
|
|
public function getFacilityStatuses($facilityId)
|
|
{
|
|
try {
|
|
$query = "SELECT id, statusComment FROM ecoFacilityStatus WHERE facilityId = :facilityId ORDER BY id DESC";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->bindValue(':facilityId', (int)$facilityId, PDO::PARAM_INT);
|
|
$stmt->execute();
|
|
return $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
} catch (Exception $e) {
|
|
error_log("Error getting facility statuses: " . $e->getMessage());
|
|
return [];
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Updates an existing status comment
|
|
* @param int $statusId The ID of the status comment
|
|
* @param string $statusComment The updated status comment
|
|
* @return bool True if successful, false otherwise
|
|
*/
|
|
public function updateFacilityStatus($statusId, $statusComment)
|
|
{
|
|
try {
|
|
$query = "UPDATE ecoFacilityStatus SET statusComment = :statusComment WHERE id = :statusId";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->bindValue(':statusId', (int)$statusId, PDO::PARAM_INT);
|
|
$stmt->bindValue(':statusComment', $statusComment);
|
|
return $stmt->execute();
|
|
} catch (Exception $e) {
|
|
error_log("Error updating facility status: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Deletes a specific status comment
|
|
* @param int $statusId The ID of the status comment to delete
|
|
* @return bool True if successful, false otherwise
|
|
*/
|
|
public function deleteFacilityStatus($statusId)
|
|
{
|
|
try {
|
|
$query = "DELETE FROM ecoFacilityStatus WHERE id = :statusId";
|
|
$stmt = $this->_dbHandle->prepare($query);
|
|
$stmt->bindValue(':statusId', (int)$statusId, PDO::PARAM_INT);
|
|
return $stmt->execute();
|
|
} catch (Exception $e) {
|
|
error_log("Error deleting facility status: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
|