129 lines
4.7 KiB
PHP
129 lines
4.7 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
function app_config(): array
|
|
{
|
|
static $config = null;
|
|
if ($config === null) {
|
|
$config = require __DIR__ . '/../config.php';
|
|
date_default_timezone_set($config['app']['timezone']);
|
|
}
|
|
return $config;
|
|
}
|
|
|
|
/**
|
|
* Shared PDO connection. Web requests only connect: the schema is created and
|
|
* upgraded explicitly (database/migrate.php or the SQL files in database/),
|
|
* never on each request.
|
|
*/
|
|
function db(): PDO
|
|
{
|
|
static $pdo = null;
|
|
if ($pdo instanceof PDO) {
|
|
return $pdo;
|
|
}
|
|
$c = app_config()['db'];
|
|
if ($c['user'] === '') {
|
|
throw new RuntimeException('Kredensial database belum dikonfigurasi (lihat database/secrets.example.php).');
|
|
}
|
|
$pdo = db_connect($c, true);
|
|
$pdo->exec("SET time_zone = '" . (new DateTime())->format('P') . "'");
|
|
return $pdo;
|
|
}
|
|
|
|
function db_connect(array $c, bool $withDatabase): PDO
|
|
{
|
|
$dsn = sprintf('mysql:host=%s;port=%d;charset=utf8mb4', $c['host'], $c['port']);
|
|
if ($withDatabase) {
|
|
$dsn .= ';dbname=' . str_replace('`', '', $c['name']);
|
|
}
|
|
return new PDO($dsn, $c['user'], $c['password'], [
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
PDO::ATTR_EMULATE_PREPARES => false,
|
|
]);
|
|
}
|
|
|
|
/** Runs a trusted SQL file; statements end with ";" at the end of a line (no splitting inside literals). */
|
|
function db_run_sql_file(PDO $pdo, string $file): void
|
|
{
|
|
$sql = preg_replace('/^\s*--.*$/m', '', (string)file_get_contents($file));
|
|
foreach (preg_split('/;\s*$/m', $sql) as $stmt) {
|
|
if (trim($stmt) !== '') {
|
|
$pdo->exec($stmt);
|
|
}
|
|
}
|
|
}
|
|
|
|
/** CLI only (database/migrate.php): creates the tables and seed data on an empty database. */
|
|
function db_migrate(PDO $pdo): void
|
|
{
|
|
$exists = $pdo->query("SHOW TABLES LIKE 'login_attempts'")->fetchColumn();
|
|
if (!$exists) {
|
|
db_run_sql_file($pdo, __DIR__ . '/../database/schema.sql');
|
|
}
|
|
if ((int)$pdo->query('SELECT COUNT(*) FROM settings')->fetchColumn() === 0) {
|
|
db_seed($pdo);
|
|
}
|
|
db_clean_descriptions($pdo);
|
|
}
|
|
|
|
/** Removes workbook cell references like " (COB!F28)" from older seeded descriptions (runs until none are left). */
|
|
function db_clean_descriptions(PDO $pdo): void
|
|
{
|
|
$rows = $pdo->query("SELECT skey, description FROM settings WHERE description LIKE '%COB!%' OR description LIKE '%CALC!%'")->fetchAll();
|
|
if (!$rows) {
|
|
return;
|
|
}
|
|
$st = $pdo->prepare('UPDATE settings SET description = ? WHERE skey = ?');
|
|
foreach ($rows as $r) {
|
|
$st->execute([trim(preg_replace('/\s*\((?:COB|CALC)![A-Z0-9:]+\)/', '', (string)$r['description'])), $r['skey']]);
|
|
}
|
|
}
|
|
|
|
/** Seeds parameters from database/defaults.php (the values of the reference workbook). */
|
|
function db_seed(PDO $pdo, bool $wipe = false): void
|
|
{
|
|
$d = require __DIR__ . '/../database/defaults.php';
|
|
$pdo->beginTransaction();
|
|
try {
|
|
if ($wipe) {
|
|
foreach (['deposito_rates', 'deposito_bands', 'product_rates', 'cashback_tiers', 'tenors', 'settings'] as $t) {
|
|
$pdo->exec("DELETE FROM $t");
|
|
}
|
|
}
|
|
$st = $pdo->prepare('INSERT INTO settings (skey, value, label, unit, grp, description, sort) VALUES (?,?,?,?,?,?,?)');
|
|
$i = 0;
|
|
foreach ($d['settings'] as $key => [$value, $label, $unit, $grp, $desc]) {
|
|
$st->execute([$key, $value, $label, $unit, $grp, $desc, ++$i]);
|
|
}
|
|
$st = $pdo->prepare('INSERT INTO tenors (months, cashback) VALUES (?,?)');
|
|
foreach ($d['tenors'] as $m => $cb) {
|
|
$st->execute([$m, $cb ? 1 : 0]);
|
|
}
|
|
$st = $pdo->prepare('INSERT INTO product_rates (product, max_nominal, rate, sort) VALUES (?,?,?,?)');
|
|
foreach ($d['product_rates'] as $product => $tiers) {
|
|
foreach ($tiers as $i => [$max, $rate]) {
|
|
$st->execute([$product, $max, $rate, $i]);
|
|
}
|
|
}
|
|
$band = $pdo->prepare('INSERT INTO deposito_bands (max_nominal, sort) VALUES (?,?)');
|
|
$rate = $pdo->prepare('INSERT INTO deposito_rates (band_id, tenor, rate) VALUES (?,?,?)');
|
|
foreach ($d['deposito_bands'] as $i => $max) {
|
|
$band->execute([$max, $i]);
|
|
$bandId = (int)$pdo->lastInsertId();
|
|
foreach ($d['deposito_rates'] as $tenor => $rates) {
|
|
$rate->execute([$bandId, $tenor, $rates[$i]]);
|
|
}
|
|
}
|
|
$st = $pdo->prepare('INSERT INTO cashback_tiers (min_nominal, share_nasabah) VALUES (?,?)');
|
|
foreach ($d['cashback_tiers'] as [$min, $share]) {
|
|
$st->execute([$min, $share]);
|
|
}
|
|
$pdo->commit();
|
|
} catch (Throwable $e) {
|
|
$pdo->rollBack();
|
|
throw $e;
|
|
}
|
|
}
|