1275 lines
42 KiB
PHP
1275 lines
42 KiB
PHP
<?php
|
|
// Session configuration
|
|
session_start();
|
|
|
|
// MySQL Database configuration
|
|
define('DB_HOST', ' ');
|
|
define('DB_PORT', ' ');
|
|
define('DB_NAME', ' ');
|
|
define('DB_USER', ' ');
|
|
define('DB_PASS', ' ');
|
|
|
|
// Connect to MySQL database
|
|
function getDB() {
|
|
static $db = null;
|
|
if ($db !== null) {
|
|
return $db;
|
|
}
|
|
|
|
try {
|
|
$dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT . ';dbname=' . DB_NAME . ';charset=utf8mb4';
|
|
$db = new PDO($dsn, DB_USER, DB_PASS, [
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
|
|
]);
|
|
|
|
// Initialize database schema
|
|
initializeDB($db);
|
|
|
|
return $db;
|
|
} catch (PDOException $e) {
|
|
http_response_code(500);
|
|
die(json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]));
|
|
}
|
|
}
|
|
|
|
// Initialize database schema (MySQL version)
|
|
function initializeDB($db) {
|
|
// Create markers table for points
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS markers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
nomor_spbu VARCHAR(100) DEFAULT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
open_24_hours TINYINT(1) DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create roads table for polylines
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS roads (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
road_type ENUM(\'Nasional\', \'Provinsi\', \'Kabupaten\') NOT NULL,
|
|
coordinates LONGTEXT NOT NULL,
|
|
length_meters DOUBLE NOT NULL,
|
|
condition_status ENUM(\'normal\', \'minor_damage\', \'major_damage\') DEFAULT \'normal\',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create indexes for roads table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_roads_road_type ON roads(road_type)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_roads_created_at ON roads(created_at)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create land_parcels table for polygons
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS land_parcels (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
certificate_type ENUM(\'SHM\', \'HGB\', \'HGU\', \'HP\') NOT NULL,
|
|
coordinates LONGTEXT NOT NULL,
|
|
area_sqm DOUBLE NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create indexes for land_parcels table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_parcels_cert_type ON land_parcels(certificate_type)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_parcels_created_at ON land_parcels(created_at)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create pontianak_areas table for choropleth boundaries
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS pontianak_areas (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
area_type ENUM(\'kelurahan\', \'kecamatan\', \'city\') NOT NULL,
|
|
geometry LONGTEXT NOT NULL,
|
|
population INT DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_area_name_type (name, area_type)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create indexes for pontianak_areas table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_pontianak_areas_type ON pontianak_areas(area_type)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_pontianak_areas_name ON pontianak_areas(name)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create area_statistics table for computed densities
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS area_statistics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
pontianak_area_id INT NOT NULL,
|
|
statistic_type ENUM(\'population_density\', \'road_density\', \'damaged_roads_density\') NOT NULL,
|
|
value DOUBLE NOT NULL,
|
|
data_date DATE NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_area_stat_date (pontianak_area_id, statistic_type, data_date),
|
|
FOREIGN KEY (pontianak_area_id) REFERENCES pontianak_areas(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create indexes for area_statistics table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_stats_area_type ON area_statistics(pontianak_area_id, statistic_type)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create masjids table
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS masjids (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
radius_meters DOUBLE NOT NULL DEFAULT 500,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create indexes for masjids table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_masjids_created_at ON masjids(created_at)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_masjids_name ON masjids(name)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create special needs points table
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS need_points (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
masjid_id INT NOT NULL,
|
|
household_name VARCHAR(255) DEFAULT NULL,
|
|
head_name VARCHAR(255) DEFAULT NULL,
|
|
economic_status VARCHAR(50) DEFAULT NULL,
|
|
is_verified TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (masjid_id) REFERENCES masjids(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create indexes for need_points table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_need_points_masjid_id ON need_points(masjid_id)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_need_points_created_at ON need_points(created_at)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create households table for poverty alleviation base data
|
|
$db->exec("
|
|
CREATE TABLE IF NOT EXISTS households (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
household_code VARCHAR(100) NOT NULL UNIQUE,
|
|
head_name VARCHAR(255) NOT NULL,
|
|
nik_hash VARCHAR(255) DEFAULT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
need_point_id INT DEFAULT NULL UNIQUE,
|
|
masjid_id INT DEFAULT NULL,
|
|
pontianak_area_id INT DEFAULT NULL,
|
|
monthly_income DOUBLE NOT NULL DEFAULT 0,
|
|
dependents_count INT NOT NULL DEFAULT 0,
|
|
housing_condition_score TINYINT NOT NULL DEFAULT 3,
|
|
employment_status ENUM('formal', 'informal', 'unemployed', 'daily_worker', 'micro_business', 'retired', 'unknown') NOT NULL DEFAULT 'unknown',
|
|
economic_status ENUM('sangat_miskin', 'miskin', 'rentan', 'cukup', 'baik', 'unknown') NOT NULL DEFAULT 'unknown',
|
|
verification_status ENUM('unverified', 'field_verified', 'admin_approved', 'rejected', 'needs_review') NOT NULL DEFAULT 'unverified',
|
|
vulnerability_notes TEXT DEFAULT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (need_point_id) REFERENCES need_points(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (masjid_id) REFERENCES masjids(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (pontianak_area_id) REFERENCES pontianak_areas(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
");
|
|
|
|
// Create indexes for households table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_households_code ON households(household_code)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_households_verification_status ON households(verification_status)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_households_masjid_id ON households(masjid_id)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_households_area_id ON households(pontianak_area_id)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_households_created_at ON households(created_at)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create assistance distributions table
|
|
$db->exec("
|
|
CREATE TABLE IF NOT EXISTS assistance_distributions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
household_id INT NOT NULL,
|
|
assistance_type ENUM('pangan', 'tunai', 'kesehatan', 'pendidikan', 'usaha_mikro', 'lainnya') NOT NULL,
|
|
assistance_value DOUBLE NOT NULL DEFAULT 0,
|
|
assistance_frequency ENUM('sekali', 'mingguan', 'bulanan', 'triwulanan', 'tahunan', 'insidental') NOT NULL DEFAULT 'sekali',
|
|
distribution_date DATE NOT NULL,
|
|
delivery_status ENUM('pending', 'delivered', 'failed') NOT NULL DEFAULT 'pending',
|
|
delivered_by VARCHAR(255) DEFAULT NULL,
|
|
notes TEXT DEFAULT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
");
|
|
|
|
// Create indexes for assistance distributions table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_assistance_household_id ON assistance_distributions(household_id)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_assistance_date ON assistance_distributions(distribution_date)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_assistance_status ON assistance_distributions(delivery_status)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create verification logs table
|
|
$db->exec("
|
|
CREATE TABLE IF NOT EXISTS verification_logs (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
household_id INT NOT NULL,
|
|
verification_status ENUM('field_verified', 'admin_approved', 'rejected', 'needs_review') NOT NULL,
|
|
verifier_name VARCHAR(255) NOT NULL,
|
|
verifier_role VARCHAR(100) DEFAULT NULL,
|
|
field_note TEXT DEFAULT NULL,
|
|
evidence_photo_count INT NOT NULL DEFAULT 0,
|
|
data_confidence_score DOUBLE DEFAULT NULL,
|
|
verified_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
");
|
|
|
|
// Create indexes for verification logs table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_verification_household_id ON verification_logs(household_id)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_verification_status ON verification_logs(verification_status)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_verification_verified_at ON verification_logs(verified_at)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create household scores table
|
|
$db->exec("
|
|
CREATE TABLE IF NOT EXISTS household_scores (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
household_id INT NOT NULL UNIQUE,
|
|
skr DOUBLE NOT NULL DEFAULT 0,
|
|
sag DOUBLE NOT NULL DEFAULT 0,
|
|
spi DOUBLE NOT NULL DEFAULT 0,
|
|
priority_level ENUM('very_high', 'high', 'medium', 'low') NOT NULL DEFAULT 'low',
|
|
score_version VARCHAR(30) NOT NULL DEFAULT 'v1',
|
|
components_json TEXT DEFAULT NULL,
|
|
computed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (household_id) REFERENCES households(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
");
|
|
|
|
// Create indexes for household_scores table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_household_scores_spi ON household_scores(spi)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_household_scores_priority ON household_scores(priority_level)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_household_scores_computed_at ON household_scores(computed_at)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Create score metadata table for versioning/tuning
|
|
$db->exec('
|
|
CREATE TABLE IF NOT EXISTS score_metadata (
|
|
key_name VARCHAR(100) PRIMARY KEY,
|
|
key_value TEXT NOT NULL,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
');
|
|
|
|
// Create users table for authentication
|
|
$db->exec("
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL UNIQUE,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('Admin', 'Petugas', 'Pimpinan') NOT NULL DEFAULT 'Petugas',
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
");
|
|
|
|
// Create indexes for users table
|
|
try {
|
|
$db->exec('CREATE INDEX idx_users_email ON users(email)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_users_username ON users(username)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
try {
|
|
$db->exec('CREATE INDEX idx_users_role ON users(role)');
|
|
} catch (PDOException $e) { /* Index may already exist */ }
|
|
|
|
// Initialize default admin user
|
|
try {
|
|
$adminCount = $db->query("SELECT COUNT(*) as cnt FROM users WHERE role = 'Admin'")->fetch(PDO::FETCH_ASSOC);
|
|
if ($adminCount['cnt'] == 0) {
|
|
$adminPassword = password_hash('admin123', PASSWORD_BCRYPT, ['cost' => 12]);
|
|
$stmt = $db->prepare('INSERT INTO users (username, email, password_hash, role, is_active) VALUES (?, ?, ?, ?, 1)');
|
|
$stmt->execute(['admin', 'admin@system.local', $adminPassword, 'Admin']);
|
|
}
|
|
} catch (PDOException $e) {
|
|
// User may already exist
|
|
}
|
|
|
|
// Initialize default score metadata values
|
|
try {
|
|
$defaultMeta = [
|
|
'score_version' => 'v1',
|
|
'skr_weights' => '{"I":0.25,"E":0.20,"D":0.15,"H":0.15,"A":0.15,"S":0.10}',
|
|
'sag_weights' => '{"J":0.40,"R":0.35,"T":0.25}',
|
|
'spi_weights' => '{"SKR":0.60,"SAG_INV":0.30,"K":0.10}',
|
|
'income_max_reference' => '5000000',
|
|
'distance_max_reference' => '2000',
|
|
'target_nasional' => '7.50',
|
|
'population_reference' => '288315899'
|
|
];
|
|
|
|
$metaStmt = $db->prepare('INSERT IGNORE INTO score_metadata (key_name, key_value) VALUES (?, ?)');
|
|
foreach ($defaultMeta as $key => $value) {
|
|
$metaStmt->execute([$key, $value]);
|
|
}
|
|
} catch (PDOException $e) {
|
|
// Metadata init may already exist
|
|
}
|
|
}
|
|
|
|
function haversineDistanceMeters($lat1, $lng1, $lat2, $lng2) {
|
|
$earthRadius = 6371000; // meters
|
|
|
|
$lat1Rad = deg2rad((float)$lat1);
|
|
$lat2Rad = deg2rad((float)$lat2);
|
|
$dLat = deg2rad((float)$lat2 - (float)$lat1);
|
|
$dLng = deg2rad((float)$lng2 - (float)$lng1);
|
|
|
|
$a = sin($dLat / 2) * sin($dLat / 2) +
|
|
cos($lat1Rad) * cos($lat2Rad) * sin($dLng / 2) * sin($dLng / 2);
|
|
$c = 2 * atan2(sqrt($a), sqrt(1 - $a));
|
|
|
|
return $earthRadius * $c;
|
|
}
|
|
|
|
function normalizeMasjidRadius($radiusValue, &$error = null) {
|
|
$error = null;
|
|
|
|
if ($radiusValue === null || $radiusValue === '') {
|
|
return 500.0;
|
|
}
|
|
|
|
if (!is_numeric($radiusValue)) {
|
|
$error = 'Radius must be a numeric value';
|
|
return null;
|
|
}
|
|
|
|
$radius = (float)$radiusValue;
|
|
if ($radius < 50 || $radius > 5000) {
|
|
$error = 'Radius must be between 50 and 5000 meters';
|
|
return null;
|
|
}
|
|
|
|
return $radius;
|
|
}
|
|
|
|
function findNearestMasjidCoveringPoint($db, $latitude, $longitude) {
|
|
$stmt = $db->query('SELECT id, name, latitude, longitude, radius_meters FROM masjids');
|
|
$masjids = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$nearest = null;
|
|
$nearestDistance = null;
|
|
|
|
foreach ($masjids as $masjid) {
|
|
$distance = haversineDistanceMeters(
|
|
(float)$latitude,
|
|
(float)$longitude,
|
|
(float)$masjid['latitude'],
|
|
(float)$masjid['longitude']
|
|
);
|
|
|
|
if ($distance <= (float)$masjid['radius_meters']) {
|
|
if ($nearestDistance === null || $distance < $nearestDistance) {
|
|
$nearest = $masjid;
|
|
$nearestDistance = $distance;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($nearest === null) {
|
|
return null;
|
|
}
|
|
|
|
$nearest['distance_meters'] = $nearestDistance;
|
|
return $nearest;
|
|
}
|
|
|
|
function getNeedPointWithMasjid($db, $id) {
|
|
$stmt = $db->prepare('
|
|
SELECT
|
|
np.id,
|
|
np.name,
|
|
np.latitude,
|
|
np.longitude,
|
|
np.masjid_id,
|
|
np.household_name,
|
|
np.head_name,
|
|
np.economic_status,
|
|
np.is_verified,
|
|
np.created_at,
|
|
m.name AS masjid_name,
|
|
m.latitude AS masjid_latitude,
|
|
m.longitude AS masjid_longitude,
|
|
m.radius_meters
|
|
FROM need_points np
|
|
JOIN masjids m ON m.id = np.masjid_id
|
|
WHERE np.id = ?
|
|
');
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
function cleanupNeedPointsOutsideMasjidCoverage($db, $masjidId) {
|
|
$masjidStmt = $db->prepare('SELECT id, latitude, longitude, radius_meters FROM masjids WHERE id = ?');
|
|
$masjidStmt->execute([$masjidId]);
|
|
$masjid = $masjidStmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$masjid) {
|
|
return [
|
|
'deleted_count' => 0,
|
|
'deleted_ids' => []
|
|
];
|
|
}
|
|
|
|
$pointsStmt = $db->prepare('SELECT id, latitude, longitude FROM need_points WHERE masjid_id = ?');
|
|
$pointsStmt->execute([$masjidId]);
|
|
$points = $pointsStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$idsToDelete = [];
|
|
foreach ($points as $point) {
|
|
$distance = haversineDistanceMeters(
|
|
(float)$point['latitude'],
|
|
(float)$point['longitude'],
|
|
(float)$masjid['latitude'],
|
|
(float)$masjid['longitude']
|
|
);
|
|
|
|
if ($distance > (float)$masjid['radius_meters']) {
|
|
$idsToDelete[] = (int)$point['id'];
|
|
}
|
|
}
|
|
|
|
if (!empty($idsToDelete)) {
|
|
$placeholders = implode(',', array_fill(0, count($idsToDelete), '?'));
|
|
$deleteStmt = $db->prepare("DELETE FROM need_points WHERE id IN ($placeholders)");
|
|
$deleteStmt->execute($idsToDelete);
|
|
}
|
|
|
|
return [
|
|
'deleted_count' => count($idsToDelete),
|
|
'deleted_ids' => $idsToDelete
|
|
];
|
|
}
|
|
|
|
function validateCoordinates($latitude, $longitude, &$error = null) {
|
|
$error = null;
|
|
|
|
if (!is_numeric($latitude) || !is_numeric($longitude)) {
|
|
$error = 'Latitude and longitude must be numeric values';
|
|
return false;
|
|
}
|
|
|
|
$lat = (float)$latitude;
|
|
$lng = (float)$longitude;
|
|
|
|
if ($lat < -90 || $lat > 90) {
|
|
$error = 'Latitude must be between -90 and 90';
|
|
return false;
|
|
}
|
|
|
|
if ($lng < -180 || $lng > 180) {
|
|
$error = 'Longitude must be between -180 and 180';
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
function normalizeHouseholdCode($db, $inputCode = null) {
|
|
$code = trim((string)($inputCode ?? ''));
|
|
if ($code !== '') {
|
|
return $code;
|
|
}
|
|
|
|
do {
|
|
try {
|
|
$random = strtoupper(substr(bin2hex(random_bytes(3)), 0, 6));
|
|
} catch (Exception $e) {
|
|
$random = strtoupper(substr(md5((string)mt_rand()), 0, 6));
|
|
}
|
|
|
|
$code = 'HH-' . date('Ymd') . '-' . $random;
|
|
$checkStmt = $db->prepare('SELECT id FROM households WHERE household_code = ? LIMIT 1');
|
|
$checkStmt->execute([$code]);
|
|
$exists = $checkStmt->fetch(PDO::FETCH_ASSOC);
|
|
} while ($exists);
|
|
|
|
return $code;
|
|
}
|
|
|
|
function normalizeMonthlyIncome($value, &$error = null) {
|
|
$error = null;
|
|
if ($value === null || $value === '') {
|
|
return 0.0;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
$error = 'Monthly income must be numeric';
|
|
return null;
|
|
}
|
|
|
|
$income = (float)$value;
|
|
if ($income < 0 || $income > 1000000000) {
|
|
$error = 'Monthly income must be between 0 and 1000000000';
|
|
return null;
|
|
}
|
|
|
|
return $income;
|
|
}
|
|
|
|
function normalizeDependentsCount($value, &$error = null) {
|
|
$error = null;
|
|
if ($value === null || $value === '') {
|
|
return 0;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
$error = 'Dependents count must be numeric';
|
|
return null;
|
|
}
|
|
|
|
$count = (int)$value;
|
|
if ($count < 0 || $count > 50) {
|
|
$error = 'Dependents count must be between 0 and 50';
|
|
return null;
|
|
}
|
|
|
|
return $count;
|
|
}
|
|
|
|
function normalizeHousingConditionScore($value, &$error = null) {
|
|
$error = null;
|
|
if ($value === null || $value === '') {
|
|
return 3;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
$error = 'Housing condition score must be numeric';
|
|
return null;
|
|
}
|
|
|
|
$score = (int)$value;
|
|
if ($score < 1 || $score > 5) {
|
|
$error = 'Housing condition score must be between 1 and 5';
|
|
return null;
|
|
}
|
|
|
|
return $score;
|
|
}
|
|
|
|
function normalizeEmploymentStatus($value, &$error = null) {
|
|
$error = null;
|
|
$allowed = ['formal', 'informal', 'unemployed', 'daily_worker', 'micro_business', 'retired', 'unknown'];
|
|
|
|
if ($value === null || $value === '') {
|
|
return 'unknown';
|
|
}
|
|
|
|
$status = strtolower(trim((string)$value));
|
|
if (!in_array($status, $allowed, true)) {
|
|
$error = 'Employment status is invalid';
|
|
return null;
|
|
}
|
|
|
|
return $status;
|
|
}
|
|
|
|
function normalizeVerificationStatus($value, &$error = null, $allowUnverified = true) {
|
|
$error = null;
|
|
$allowed = ['field_verified', 'admin_approved', 'rejected', 'needs_review'];
|
|
if ($allowUnverified) {
|
|
$allowed[] = 'unverified';
|
|
}
|
|
|
|
if ($value === null || $value === '') {
|
|
return $allowUnverified ? 'unverified' : 'needs_review';
|
|
}
|
|
|
|
$status = strtolower(trim((string)$value));
|
|
if (!in_array($status, $allowed, true)) {
|
|
$error = 'Verification status is invalid';
|
|
return null;
|
|
}
|
|
|
|
return $status;
|
|
}
|
|
|
|
function normalizeAssistanceType($value, &$error = null) {
|
|
$error = null;
|
|
$allowed = ['pangan', 'tunai', 'kesehatan', 'pendidikan', 'usaha_mikro', 'lainnya'];
|
|
|
|
if ($value === null || $value === '') {
|
|
$error = 'Assistance type is required';
|
|
return null;
|
|
}
|
|
|
|
$type = strtolower(trim((string)$value));
|
|
if (!in_array($type, $allowed, true)) {
|
|
$error = 'Assistance type is invalid';
|
|
return null;
|
|
}
|
|
|
|
return $type;
|
|
}
|
|
|
|
function normalizeAssistanceFrequency($value, &$error = null) {
|
|
$error = null;
|
|
$allowed = ['sekali', 'mingguan', 'bulanan', 'triwulanan', 'tahunan', 'insidental'];
|
|
|
|
if ($value === null || $value === '') {
|
|
return 'sekali';
|
|
}
|
|
|
|
$frequency = strtolower(trim((string)$value));
|
|
if (!in_array($frequency, $allowed, true)) {
|
|
$error = 'Assistance frequency is invalid';
|
|
return null;
|
|
}
|
|
|
|
return $frequency;
|
|
}
|
|
|
|
function normalizeDeliveryStatus($value, &$error = null) {
|
|
$error = null;
|
|
$allowed = ['pending', 'delivered', 'failed'];
|
|
|
|
if ($value === null || $value === '') {
|
|
return 'pending';
|
|
}
|
|
|
|
$status = strtolower(trim((string)$value));
|
|
if (!in_array($status, $allowed, true)) {
|
|
$error = 'Delivery status is invalid';
|
|
return null;
|
|
}
|
|
|
|
return $status;
|
|
}
|
|
|
|
function normalizeAssistanceValue($value, &$error = null) {
|
|
$error = null;
|
|
if ($value === null || $value === '') {
|
|
return 0.0;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
$error = 'Assistance value must be numeric';
|
|
return null;
|
|
}
|
|
|
|
$assistanceValue = (float)$value;
|
|
if ($assistanceValue < 0 || $assistanceValue > 1000000000) {
|
|
$error = 'Assistance value must be between 0 and 1000000000';
|
|
return null;
|
|
}
|
|
|
|
return $assistanceValue;
|
|
}
|
|
|
|
function normalizeEvidencePhotoCount($value, &$error = null) {
|
|
$error = null;
|
|
if ($value === null || $value === '') {
|
|
return 0;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
$error = 'Evidence photo count must be numeric';
|
|
return null;
|
|
}
|
|
|
|
$count = (int)$value;
|
|
if ($count < 0 || $count > 100) {
|
|
$error = 'Evidence photo count must be between 0 and 100';
|
|
return null;
|
|
}
|
|
|
|
return $count;
|
|
}
|
|
|
|
function normalizeDataConfidenceScore($value, &$error = null) {
|
|
$error = null;
|
|
if ($value === null || $value === '') {
|
|
return null;
|
|
}
|
|
|
|
if (!is_numeric($value)) {
|
|
$error = 'Data confidence score must be numeric';
|
|
return null;
|
|
}
|
|
|
|
$score = (float)$value;
|
|
if ($score < 0 || $score > 1) {
|
|
$error = 'Data confidence score must be between 0 and 1';
|
|
return null;
|
|
}
|
|
|
|
return $score;
|
|
}
|
|
|
|
function normalizeNikHashFromInput($nikInput = null) {
|
|
if ($nikInput === null) {
|
|
return null;
|
|
}
|
|
|
|
$nik = trim((string)$nikInput);
|
|
if ($nik === '') {
|
|
return null;
|
|
}
|
|
|
|
return hash('sha256', $nik);
|
|
}
|
|
|
|
function householdExists($db, $householdId) {
|
|
$stmt = $db->prepare('SELECT id FROM households WHERE id = ? LIMIT 1');
|
|
$stmt->execute([(int)$householdId]);
|
|
return (bool)$stmt->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
function getHouseholdById($db, $householdId) {
|
|
$stmt = $db->prepare('
|
|
SELECT
|
|
h.*,
|
|
np.name AS need_point_name,
|
|
m.name AS masjid_name,
|
|
pa.name AS area_name,
|
|
(
|
|
SELECT COUNT(*)
|
|
FROM assistance_distributions ad
|
|
WHERE ad.household_id = h.id
|
|
) AS assistance_count,
|
|
(
|
|
SELECT MAX(vl.verified_at)
|
|
FROM verification_logs vl
|
|
WHERE vl.household_id = h.id
|
|
) AS last_verified_at
|
|
FROM households h
|
|
LEFT JOIN need_points np ON np.id = h.need_point_id
|
|
LEFT JOIN masjids m ON m.id = h.masjid_id
|
|
LEFT JOIN pontianak_areas pa ON pa.id = h.pontianak_area_id
|
|
WHERE h.id = ?
|
|
');
|
|
$stmt->execute([(int)$householdId]);
|
|
return $stmt->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
function mapEconomicVulnerability($economicStatus) {
|
|
$status = strtolower(trim((string)$economicStatus));
|
|
$scores = [
|
|
'sangat_miskin' => 1.0,
|
|
'miskin' => 0.85,
|
|
'rentan' => 0.65,
|
|
'cukup' => 0.35,
|
|
'baik' => 0.15,
|
|
'unknown' => 0.5
|
|
];
|
|
|
|
return isset($scores[$status]) ? $scores[$status] : 0.5;
|
|
}
|
|
|
|
function syncNeedPointsToHouseholds($db) {
|
|
$updatedCount = 0;
|
|
$insertedCount = 0;
|
|
|
|
$updateStmt = $db->prepare("
|
|
UPDATE households
|
|
SET economic_status = (
|
|
SELECT COALESCE(NULLIF(TRIM(np.economic_status), ''), 'unknown')
|
|
FROM need_points np
|
|
WHERE np.id = households.need_point_id
|
|
),
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE need_point_id IS NOT NULL
|
|
AND (economic_status IS NULL OR TRIM(economic_status) = '')
|
|
");
|
|
$updateStmt->execute();
|
|
$updatedCount += $updateStmt->rowCount();
|
|
|
|
$missingStmt = $db->query("
|
|
SELECT
|
|
np.id,
|
|
np.name,
|
|
np.latitude,
|
|
np.longitude,
|
|
np.masjid_id,
|
|
np.head_name,
|
|
np.economic_status,
|
|
np.is_verified
|
|
FROM need_points np
|
|
LEFT JOIN households h ON h.need_point_id = np.id
|
|
WHERE h.id IS NULL
|
|
ORDER BY np.id ASC
|
|
");
|
|
$missingRows = $missingStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if (!empty($missingRows)) {
|
|
$insertStmt = $db->prepare("
|
|
INSERT INTO households (
|
|
household_code,
|
|
head_name,
|
|
latitude,
|
|
longitude,
|
|
need_point_id,
|
|
masjid_id,
|
|
monthly_income,
|
|
dependents_count,
|
|
housing_condition_score,
|
|
employment_status,
|
|
economic_status,
|
|
verification_status,
|
|
vulnerability_notes
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
");
|
|
|
|
foreach ($missingRows as $row) {
|
|
$pointId = (int)$row['id'];
|
|
$householdCode = 'NP-' . str_pad((string)$pointId, 6, '0', STR_PAD_LEFT);
|
|
$economicStatus = strtolower(trim((string)($row['economic_status'] ?? 'unknown')));
|
|
if ($economicStatus === '') {
|
|
$economicStatus = 'unknown';
|
|
}
|
|
$verificationStatus = ((int)($row['is_verified'] ?? 0) === 1) ? 'field_verified' : 'unverified';
|
|
$headName = trim((string)($row['head_name'] ?? ''));
|
|
if ($headName === '') {
|
|
$headName = 'Kepala ' . $householdCode;
|
|
}
|
|
|
|
$insertStmt->execute([
|
|
$householdCode,
|
|
$headName,
|
|
(float)$row['latitude'],
|
|
(float)$row['longitude'],
|
|
$pointId,
|
|
!empty($row['masjid_id']) ? (int)$row['masjid_id'] : null,
|
|
0,
|
|
0,
|
|
3,
|
|
'unknown',
|
|
$economicStatus,
|
|
$verificationStatus,
|
|
'Auto-synced from need_point #' . $pointId
|
|
]);
|
|
$insertedCount++;
|
|
}
|
|
}
|
|
|
|
return [
|
|
'updated_count' => $updatedCount,
|
|
'inserted_count' => $insertedCount,
|
|
'total_changed' => $updatedCount + $insertedCount
|
|
];
|
|
}
|
|
|
|
function ensureHouseholdScoresForAllHouseholds($db) {
|
|
$missingStmt = $db->query("
|
|
SELECT h.*
|
|
FROM households h
|
|
LEFT JOIN household_scores hs ON hs.household_id = h.id
|
|
WHERE hs.household_id IS NULL
|
|
ORDER BY h.id ASC
|
|
");
|
|
$missingHouseholds = $missingStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$computed = 0;
|
|
foreach ($missingHouseholds as $household) {
|
|
$scoreData = computeHouseholdScores($db, $household);
|
|
upsertHouseholdScore($db, $household['id'], $scoreData);
|
|
$computed++;
|
|
}
|
|
|
|
return $computed;
|
|
}
|
|
|
|
function clamp01($value) {
|
|
$v = (float)$value;
|
|
if ($v < 0) return 0.0;
|
|
if ($v > 1) return 1.0;
|
|
return $v;
|
|
}
|
|
|
|
function normalizeToUnit($value, $minValue, $maxValue, $invert = false) {
|
|
$minV = (float)$minValue;
|
|
$maxV = (float)$maxValue;
|
|
|
|
if ($maxV <= $minV) {
|
|
return 0.0;
|
|
}
|
|
|
|
$normalized = ((float)$value - $minV) / ($maxV - $minV);
|
|
$normalized = clamp01($normalized);
|
|
|
|
if ($invert) {
|
|
return 1.0 - $normalized;
|
|
}
|
|
|
|
return $normalized;
|
|
}
|
|
|
|
function getLatestAreaStatisticValue($db, $areaId, $statType) {
|
|
if (!$areaId || !$statType) {
|
|
return null;
|
|
}
|
|
|
|
$stmt = $db->prepare('
|
|
SELECT value
|
|
FROM area_statistics
|
|
WHERE pontianak_area_id = ? AND statistic_type = ?
|
|
ORDER BY data_date DESC, created_at DESC
|
|
LIMIT 1
|
|
');
|
|
$stmt->execute([(int)$areaId, (string)$statType]);
|
|
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$row) {
|
|
return null;
|
|
}
|
|
|
|
return (float)$row['value'];
|
|
}
|
|
|
|
function getHouseholdDistanceToMasjidMeters($householdRow, $masjidRow) {
|
|
if (!$householdRow || !$masjidRow) {
|
|
return null;
|
|
}
|
|
|
|
return haversineDistanceMeters(
|
|
(float)$householdRow['latitude'],
|
|
(float)$householdRow['longitude'],
|
|
(float)$masjidRow['latitude'],
|
|
(float)$masjidRow['longitude']
|
|
);
|
|
}
|
|
|
|
function mapEmploymentVulnerability($employmentStatus) {
|
|
$status = strtolower(trim((string)$employmentStatus));
|
|
$scores = [
|
|
'unemployed' => 1.0,
|
|
'daily_worker' => 0.85,
|
|
'informal' => 0.7,
|
|
'micro_business' => 0.55,
|
|
'retired' => 0.4,
|
|
'formal' => 0.25,
|
|
'unknown' => 0.5
|
|
];
|
|
|
|
return isset($scores[$status]) ? $scores[$status] : 0.5;
|
|
}
|
|
|
|
function mapShockFactor($verificationStatus) {
|
|
$status = strtolower(trim((string)$verificationStatus));
|
|
$map = [
|
|
'needs_review' => 1.0,
|
|
'unverified' => 0.7,
|
|
'rejected' => 0.6,
|
|
'field_verified' => 0.4,
|
|
'admin_approved' => 0.2
|
|
];
|
|
|
|
return isset($map[$status]) ? $map[$status] : 0.5;
|
|
}
|
|
|
|
function classifyPriorityLevel($spi) {
|
|
$value = (float)$spi;
|
|
if ($value >= 0.75) {
|
|
return 'very_high';
|
|
}
|
|
if ($value >= 0.60) {
|
|
return 'high';
|
|
}
|
|
if ($value >= 0.45) {
|
|
return 'medium';
|
|
}
|
|
|
|
return 'low';
|
|
}
|
|
|
|
function computeHouseholdScores($db, $householdRow) {
|
|
$income = (float)($householdRow['monthly_income'] ?? 0);
|
|
$dependents = (int)($householdRow['dependents_count'] ?? 0);
|
|
$housingScore = (int)($householdRow['housing_condition_score'] ?? 3);
|
|
$employmentStatus = $householdRow['employment_status'] ?? 'unknown';
|
|
$economicStatus = $householdRow['economic_status'] ?? 'unknown';
|
|
$verificationStatus = $householdRow['verification_status'] ?? 'unverified';
|
|
|
|
// SKR components (higher = more vulnerable)
|
|
$economicVulnerability = mapEconomicVulnerability($economicStatus); // primary poverty condition
|
|
$incomeDeprivation = normalizeToUnit($income, 0, 5000000, true); // supporting signal
|
|
$employmentVulnerability = mapEmploymentVulnerability($employmentStatus); // E
|
|
$dependencyBurden = normalizeToUnit($dependents, 0, 6, false); // D
|
|
$housingVulnerability = normalizeToUnit($housingScore, 1, 5, true); // H
|
|
|
|
// A: accessibility deprivation (inverse from distance to masjid)
|
|
$masjidDistanceMeters = null;
|
|
$accessibilityService = 0.5;
|
|
if (!empty($householdRow['masjid_id'])) {
|
|
$masjidStmt = $db->prepare('SELECT latitude, longitude FROM masjids WHERE id = ?');
|
|
$masjidStmt->execute([(int)$householdRow['masjid_id']]);
|
|
$masjid = $masjidStmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if ($masjid) {
|
|
$masjidDistanceMeters = getHouseholdDistanceToMasjidMeters($householdRow, $masjid);
|
|
$distanceAccessScore = normalizeToUnit($masjidDistanceMeters, 0, 2000, true); // J as accessibility
|
|
$accessibilityService = 1.0 - $distanceAccessScore; // convert to deprivation
|
|
}
|
|
}
|
|
|
|
// S: shock factor from current verification confidence
|
|
$shockFactor = mapShockFactor($verificationStatus);
|
|
|
|
$skr =
|
|
(0.40 * $economicVulnerability) +
|
|
(0.18 * $incomeDeprivation) +
|
|
(0.12 * $employmentVulnerability) +
|
|
(0.10 * $dependencyBurden) +
|
|
(0.10 * $housingVulnerability) +
|
|
(0.06 * $accessibilityService) +
|
|
(0.04 * $shockFactor);
|
|
$skr = round(clamp01($skr), 6);
|
|
|
|
// SAG components (higher = better accessibility)
|
|
$J = 0.5;
|
|
if ($masjidDistanceMeters !== null) {
|
|
$J = normalizeToUnit($masjidDistanceMeters, 0, 2000, true);
|
|
}
|
|
|
|
$roadDensity = getLatestAreaStatisticValue($db, $householdRow['pontianak_area_id'] ?? null, 'road_density');
|
|
$R = $roadDensity !== null ? normalizeToUnit($roadDensity, 0, 5, false) : 0.5;
|
|
|
|
// T fallback because travel-time data is not available yet.
|
|
$T = ($J + $R) / 2;
|
|
|
|
$sag = (0.40 * $J) + (0.35 * $R) + (0.25 * $T);
|
|
$sag = round(clamp01($sag), 6);
|
|
|
|
$K = $shockFactor;
|
|
$spi = (0.60 * $skr) + (0.30 * (1 - $sag)) + (0.10 * $K);
|
|
$spi = round(clamp01($spi), 6);
|
|
|
|
$priorityLevel = classifyPriorityLevel($spi);
|
|
|
|
return [
|
|
'skr' => $skr,
|
|
'sag' => $sag,
|
|
'spi' => $spi,
|
|
'priority_level' => $priorityLevel,
|
|
'score_version' => 'v1',
|
|
'components' => [
|
|
'ECO_economic_vulnerability' => round($economicVulnerability, 6),
|
|
'I_income_deprivation' => round($incomeDeprivation, 6),
|
|
'E_employment_vulnerability' => round($employmentVulnerability, 6),
|
|
'D_dependency_burden' => round($dependencyBurden, 6),
|
|
'H_housing_vulnerability' => round($housingVulnerability, 6),
|
|
'A_accessibility_deprivation' => round($accessibilityService, 6),
|
|
'S_shock_factor' => round($shockFactor, 6),
|
|
'J_distance_access' => round($J, 6),
|
|
'R_road_access' => round($R, 6),
|
|
'T_time_access_proxy' => round($T, 6)
|
|
]
|
|
];
|
|
}
|
|
|
|
function upsertHouseholdScore($db, $householdId, $scoreData) {
|
|
$stmt = $db->prepare('
|
|
INSERT INTO household_scores (
|
|
household_id,
|
|
skr,
|
|
sag,
|
|
spi,
|
|
priority_level,
|
|
score_version,
|
|
components_json,
|
|
computed_at,
|
|
updated_at
|
|
)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
|
|
ON DUPLICATE KEY UPDATE
|
|
skr = VALUES(skr),
|
|
sag = VALUES(sag),
|
|
spi = VALUES(spi),
|
|
priority_level = VALUES(priority_level),
|
|
score_version = VALUES(score_version),
|
|
components_json = VALUES(components_json),
|
|
computed_at = CURRENT_TIMESTAMP,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
');
|
|
|
|
$stmt->execute([
|
|
(int)$householdId,
|
|
(float)$scoreData['skr'],
|
|
(float)$scoreData['sag'],
|
|
(float)$scoreData['spi'],
|
|
(string)$scoreData['priority_level'],
|
|
(string)$scoreData['score_version'],
|
|
json_encode($scoreData['components'])
|
|
]);
|
|
}
|
|
|
|
// Authentication helper functions
|
|
function loginUser($email, $password) {
|
|
try {
|
|
$db = getDB();
|
|
$stmt = $db->prepare('SELECT id, username, email, password_hash, role, is_active FROM users WHERE email = ? AND is_active = 1');
|
|
$stmt->execute([$email]);
|
|
$user = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$user) {
|
|
return ['success' => false, 'message' => 'User not found or inactive'];
|
|
}
|
|
|
|
if (!password_verify($password, $user['password_hash'])) {
|
|
return ['success' => false, 'message' => 'Invalid password'];
|
|
}
|
|
|
|
// Set session
|
|
$_SESSION['user_id'] = $user['id'];
|
|
$_SESSION['username'] = $user['username'];
|
|
$_SESSION['email'] = $user['email'];
|
|
$_SESSION['role'] = $user['role'];
|
|
$_SESSION['login_time'] = time();
|
|
|
|
return [
|
|
'success' => true,
|
|
'message' => 'Login successful',
|
|
'user' => [
|
|
'id' => $user['id'],
|
|
'username' => $user['username'],
|
|
'email' => $user['email'],
|
|
'role' => $user['role']
|
|
]
|
|
];
|
|
} catch (PDOException $e) {
|
|
return ['success' => false, 'message' => 'Database error: ' . $e->getMessage()];
|
|
}
|
|
}
|
|
|
|
function logoutUser() {
|
|
session_destroy();
|
|
return ['success' => true, 'message' => 'Logout successful'];
|
|
}
|
|
|
|
function getCurrentUser() {
|
|
if (!isset($_SESSION['user_id'])) {
|
|
return null;
|
|
}
|
|
|
|
return [
|
|
'id' => $_SESSION['user_id'],
|
|
'username' => $_SESSION['username'],
|
|
'email' => $_SESSION['email'],
|
|
'role' => $_SESSION['role']
|
|
];
|
|
}
|
|
|
|
function isUserLoggedIn() {
|
|
return isset($_SESSION['user_id']) && !empty($_SESSION['user_id']);
|
|
}
|
|
|
|
function requireLogin() {
|
|
if (!isUserLoggedIn()) {
|
|
http_response_code(401);
|
|
die(json_encode(['error' => 'Unauthorized - Please login']));
|
|
}
|
|
}
|
|
|
|
function requireRole($allowedRoles) {
|
|
requireLogin();
|
|
|
|
if (!is_array($allowedRoles)) {
|
|
$allowedRoles = [$allowedRoles];
|
|
}
|
|
|
|
if (!in_array($_SESSION['role'], $allowedRoles)) {
|
|
http_response_code(403);
|
|
die(json_encode(['error' => 'Forbidden - Insufficient permissions']));
|
|
}
|
|
}
|
|
|
|
function createUser($username, $email, $password, $role = 'Petugas') {
|
|
try {
|
|
$db = getDB();
|
|
|
|
// Check if user already exists
|
|
$stmt = $db->prepare('SELECT id FROM users WHERE email = ? OR username = ?');
|
|
$stmt->execute([$email, $username]);
|
|
if ($stmt->fetch()) {
|
|
return ['success' => false, 'message' => 'User already exists'];
|
|
}
|
|
|
|
$passwordHash = password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);
|
|
|
|
$stmt = $db->prepare('
|
|
INSERT INTO users (username, email, password_hash, role, is_active)
|
|
VALUES (?, ?, ?, ?, 1)
|
|
');
|
|
$stmt->execute([$username, $email, $passwordHash, $role]);
|
|
|
|
return [
|
|
'success' => true,
|
|
'message' => 'User created successfully',
|
|
'user_id' => $db->lastInsertId()
|
|
];
|
|
} catch (PDOException $e) {
|
|
return ['success' => false, 'message' => 'Database error: ' . $e->getMessage()];
|
|
}
|
|
}
|
|
?>
|