ikan Uploader :
Directory : /var/www/new-ppid-app/includes/
Upload File : |
| Current File : /var/www/new-ppid-app/includes/queries.php |
<?php
/**
* Kumpulan fungsi query yang dipakai ulang di halaman publik & admin.
*/
/**
* Daftar kecamatan di Kabupaten Mamuju Tengah.
*
* @return array<int, string>
*/
function kecamatan_mateng(): array
{
return ['Topoyo', 'Tobadak', 'Karossa', 'Pangale', 'Budong-Budong'];
}
/**
* Ambil berita terbaru yang dipublikasikan.
*
* @return array<int, array<string, mixed>>
*/
function get_berita_terbaru(int $limit = 3): array
{
$stmt = db()->prepare(
"SELECT id, judul, slug, ringkasan, gambar, views, COALESCE(published_at, created_at) AS tanggal
FROM berita WHERE status = 'publish'
ORDER BY COALESCE(published_at, created_at) DESC LIMIT :limit"
);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll();
}
/**
* Ambil satu berita berdasarkan slug.
*
* @return array<string, mixed>|null
*/
function get_berita_by_slug(string $slug): ?array
{
$stmt = db()->prepare(
'SELECT b.*, a.nama AS penulis, COALESCE(b.published_at, b.created_at) AS tanggal
FROM berita b LEFT JOIN admin_users a ON a.id = b.created_by
WHERE b.slug = ? LIMIT 1'
);
$stmt->execute([$slug]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Daftar galeri kegiatan (dengan foto sampul & jumlah foto), terbaru dahulu.
*
* @param int $limit 0 = semua.
* @return array<int, array<string, mixed>>
*/
function get_galeri_list(int $limit = 0): array
{
$sql = "SELECT g.id, g.judul, g.slug, g.tanggal, g.created_at,
(SELECT gf.file FROM galeri_foto gf WHERE gf.galeri_id = g.id ORDER BY gf.urutan ASC, gf.id ASC LIMIT 1) AS cover,
(SELECT COUNT(*) FROM galeri_foto gf WHERE gf.galeri_id = g.id) AS jml_foto
FROM galeri g
ORDER BY COALESCE(g.tanggal, DATE(g.created_at)) DESC, g.id DESC";
if ($limit > 0) {
$sql .= ' LIMIT ' . (int) $limit;
}
return db()->query($sql)->fetchAll();
}
/**
* Ambil satu galeri kegiatan berdasarkan slug.
*
* @return array<string, mixed>|null
*/
function get_galeri_by_slug(string $slug): ?array
{
$stmt = db()->prepare('SELECT * FROM galeri WHERE slug = ? LIMIT 1');
$stmt->execute([$slug]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Daftar nama file foto milik sebuah galeri (terurut).
*
* @return array<int, string>
*/
function get_galeri_foto(int $galeriId): array
{
$stmt = db()->prepare('SELECT file FROM galeri_foto WHERE galeri_id = ? ORDER BY urutan ASC, id ASC');
$stmt->execute([$galeriId]);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}
/**
* Peta foto (terurut) untuk sekumpulan galeri_id sekaligus.
*
* @param array<int, int|string> $ids
* @return array<int, array<int, string>> map galeri_id => [file, ...]
*/
function get_galeri_foto_map(array $ids): array
{
$ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
if (!$ids) {
return [];
}
$in = implode(',', $ids);
$rows = db()->query("SELECT galeri_id, file FROM galeri_foto WHERE galeri_id IN ($in) ORDER BY urutan ASC, id ASC")->fetchAll();
$map = [];
foreach ($rows as $r) {
$map[(int) $r['galeri_id']][] = $r['file'];
}
return $map;
}
/**
* Daftar galeri video (YouTube), terbaru dahulu.
*
* @param int $limit 0 = semua.
* @return array<int, array<string, mixed>>
*/
function get_galeri_video_list(int $limit = 0): array
{
$sql = "SELECT id, judul, youtube_id, tanggal, created_at FROM galeri_video
ORDER BY COALESCE(tanggal, DATE(created_at)) DESC, id DESC";
if ($limit > 0) {
$sql .= ' LIMIT ' . (int) $limit;
}
return db()->query($sql)->fetchAll();
}
/**
* Ambil daftar layanan desa dikelompokkan per kecamatan.
*
* @param string $keyword Filter nama desa (opsional).
* @return array<string, array<int, array<string, mixed>>>
*/
function get_layanan_desa_grouped(string $keyword = ''): array
{
if ($keyword !== '') {
$stmt = db()->prepare('SELECT * FROM layanan_desa WHERE nama_desa LIKE ? ORDER BY kecamatan ASC, nama_desa ASC');
$stmt->execute(['%' . $keyword . '%']);
$rows = $stmt->fetchAll();
} else {
$rows = db()->query('SELECT * FROM layanan_desa ORDER BY kecamatan ASC, nama_desa ASC')->fetchAll();
}
$grouped = [];
foreach ($rows as $r) {
$grouped[$r['kecamatan']][] = $r;
}
return $grouped;
}
/**
* Ambil seluruh desa master (id, kecamatan, nama).
*
* @return array<int, array<string, mixed>>
*/
function get_desa_all(): array
{
return db()->query('SELECT id, kecamatan, nama FROM desa ORDER BY kecamatan ASC, nama ASC')->fetchAll();
}
/**
* Kumpulan desa_id yang sudah memiliki entri layanan desa (untuk cegah duplikat).
*
* @return array<int, int>
*/
function get_used_desa_ids(): array
{
return array_map('intval', db()->query('SELECT desa_id FROM layanan_desa WHERE desa_id IS NOT NULL')->fetchAll(PDO::FETCH_COLUMN));
}
/**
* Ambil seluruh kategori informasi (urut berdasarkan kolom urutan).
*
* @param string|null $tipe Filter berdasarkan tipe: 'kip' atau 'regulasi'.
* @return array<int, array<string, mixed>>
*/
function get_kategori(?string $tipe = null): array
{
if ($tipe !== null) {
$stmt = db()->prepare('SELECT * FROM kategori_informasi WHERE tipe = ? ORDER BY urutan ASC, id ASC');
$stmt->execute([$tipe]);
return $stmt->fetchAll();
}
return db()->query('SELECT * FROM kategori_informasi ORDER BY urutan ASC, id ASC')->fetchAll();
}
/**
* Ambil satu kategori berdasarkan slug.
*
* @return array<string, mixed>|null
*/
function get_kategori_by_slug(string $slug): ?array
{
$stmt = db()->prepare('SELECT * FROM kategori_informasi WHERE slug = ? LIMIT 1');
$stmt->execute([$slug]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Ambil satu kategori berdasarkan id.
*
* @return array<string, mixed>|null
*/
function get_kategori_by_id(int $id): ?array
{
$stmt = db()->prepare('SELECT * FROM kategori_informasi WHERE id = ? LIMIT 1');
$stmt->execute([$id]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Hitung jumlah dokumen terbit per kategori_id.
*
* @return array<int, int> map kategori_id => jumlah
*/
function count_dokumen_per_kategori(): array
{
$rows = db()->query(
"SELECT kategori_id, COUNT(*) AS jml FROM dokumen_informasi
WHERE status = 'publish' GROUP BY kategori_id"
)->fetchAll();
$map = [];
foreach ($rows as $r) {
$map[(int) $r['kategori_id']] = (int) $r['jml'];
}
return $map;
}
/**
* Ambil satu dokumen berdasarkan id (beserta nama kategori).
*
* @return array<string, mixed>|null
*/
function get_dokumen(int $id): ?array
{
$stmt = db()->prepare(
'SELECT d.*, k.nama AS kategori_nama, k.slug AS kategori_slug, k.tipe AS kategori_tipe,
dir.nama AS direktori_nama
FROM dokumen_informasi d
JOIN kategori_informasi k ON k.id = d.kategori_id
LEFT JOIN direktori dir ON dir.id = d.direktori_id
WHERE d.id = ? LIMIT 1'
);
$stmt->execute([$id]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Daftar seluruh direktori (master), terurut.
*
* @return array<int, array<string, mixed>>
*/
function get_direktori_list(): array
{
return db()->query(
'SELECT dir.*, k.nama AS kategori_nama, k.tipe AS kategori_tipe, k.slug AS kategori_slug
FROM direktori dir
LEFT JOIN kategori_informasi k ON k.id = dir.kategori_id
ORDER BY dir.urutan ASC, dir.nama ASC'
)->fetchAll();
}
/**
* Ambil satu direktori berdasarkan id.
*
* @return array<string, mixed>|null
*/
function get_direktori(int $id): ?array
{
$stmt = db()->prepare('SELECT * FROM direktori WHERE id = ? LIMIT 1');
$stmt->execute([$id]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Ambil semua direktori (folder) milik sebuah kategori informasi, urut tampilan.
*
* @return array<int, array<string, mixed>>
*/
function get_direktori_by_kategori(int $kategoriId): array
{
$stmt = db()->prepare('SELECT * FROM direktori WHERE kategori_id = ? ORDER BY urutan ASC, nama ASC');
$stmt->execute([$kategoriId]);
return $stmt->fetchAll();
}
/**
* Ambil satu direktori berdasarkan slug.
*
* @return array<string, mixed>|null
*/
function get_direktori_by_slug(string $slug): ?array
{
$stmt = db()->prepare('SELECT * FROM direktori WHERE slug = ? LIMIT 1');
$stmt->execute([$slug]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Hitung jumlah dokumen terbit per direktori_id.
*
* @return array<int, int> map direktori_id => jumlah
*/
function count_dokumen_per_direktori(): array
{
$rows = db()->query(
"SELECT direktori_id, COUNT(*) AS jml FROM dokumen_informasi
WHERE status = 'publish' AND direktori_id IS NOT NULL GROUP BY direktori_id"
)->fetchAll();
$map = [];
foreach ($rows as $r) {
$map[(int) $r['direktori_id']] = (int) $r['jml'];
}
return $map;
}
/**
* Statistik dashboard admin.
*
* @return array<string, int>
*/
function dashboard_stats(): array
{
$pdo = db();
return [
'total' => (int) $pdo->query('SELECT COUNT(*) FROM permohonan')->fetchColumn(),
'verifikasi' => (int) $pdo->query("SELECT COUNT(*) FROM permohonan WHERE status = 'verifikasi'")->fetchColumn(),
'diproses' => (int) $pdo->query("SELECT COUNT(*) FROM permohonan WHERE status = 'diproses'")->fetchColumn(),
'selesai' => (int) $pdo->query("SELECT COUNT(*) FROM permohonan WHERE status = 'selesai'")->fetchColumn(),
'dokumen' => (int) $pdo->query("SELECT COUNT(*) FROM dokumen_informasi WHERE status = 'publish'")->fetchColumn(),
'keberatan' => (int) $pdo->query("SELECT COUNT(*) FROM keberatan WHERE status = 'baru'")->fetchColumn(),
'saran' => (int) $pdo->query("SELECT COUNT(*) FROM saran WHERE status = 'baru'")->fetchColumn(),
];
}
/**
* Ambil seluruh UPD (Unit Perangkat Daerah), urut berdasarkan kolom urutan.
*
* @return array<int, array<string, mixed>>
*/
function get_upd_list(): array
{
return db()->query('SELECT * FROM upd ORDER BY urutan ASC, nama ASC')->fetchAll();
}
/**
* Ambil satu UPD berdasarkan slug.
*
* @return array<string, mixed>|null
*/
function get_upd_by_slug(string $slug): ?array
{
$stmt = db()->prepare('SELECT * FROM upd WHERE slug = ? LIMIT 1');
$stmt->execute([$slug]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Ambil satu UPD berdasarkan id.
*
* @return array<string, mixed>|null
*/
function get_upd(int $id): ?array
{
$stmt = db()->prepare('SELECT * FROM upd WHERE id = ? LIMIT 1');
$stmt->execute([$id]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Hitung jumlah dokumen terbit per upd_id.
*
* @return array<int, int> map upd_id => jumlah
*/
function count_dokumen_per_upd(): array
{
$rows = db()->query(
"SELECT upd_id, COUNT(*) AS jml FROM dokumen_informasi
WHERE status = 'publish' AND upd_id IS NOT NULL GROUP BY upd_id"
)->fetchAll();
$map = [];
foreach ($rows as $r) {
$map[(int) $r['upd_id']] = (int) $r['jml'];
}
return $map;
}
/**
* Ambil testimoni yang sudah ada untuk sebuah nomor resi (atau null).
*
* @return array<string, mixed>|null
*/
function get_testimoni_by_resi(string $resi): ?array
{
$stmt = db()->prepare('SELECT * FROM testimoni WHERE no_resi = ? LIMIT 1');
$stmt->execute([$resi]);
$row = $stmt->fetch();
return $row ?: null;
}
/**
* Statistik testimoni terpublikasi: jumlah & rata-rata rating.
*
* @return array{count:int, avg:float}
*/
function testimoni_stats(): array
{
$row = db()->query(
'SELECT COUNT(*) AS c, COALESCE(AVG(rating), 0) AS a FROM testimoni WHERE is_published = 1'
)->fetch();
return ['count' => (int) $row['c'], 'avg' => round((float) $row['a'], 1)];
}
function _testimoni_publik_union(): string
{
$c = 'COLLATE utf8mb4_general_ci';
return "SELECT nama $c AS nama, rating, isi $c AS isi, created_at, 'permohonan' $c AS sumber
FROM testimoni WHERE is_published = 1
UNION ALL
SELECT nama $c, rating, testimoni $c, created_at, 'survey' $c
FROM survey_kepuasan WHERE is_published = 1 AND testimoni IS NOT NULL AND testimoni <> ''";
}
/**
* Ambil testimoni publik gabungan (permohonan + survei), terbaru dulu.
*
* @return array<int, array{nama:string, rating:int, isi:string, created_at:string, sumber:string}>
*/
function get_testimoni_publik(int $limit = 0, int $offset = 0): array
{
$sql = 'SELECT * FROM (' . _testimoni_publik_union() . ') gab ORDER BY created_at DESC';
if ($limit > 0) {
$sql .= ' LIMIT :limit OFFSET :offset';
$stmt = db()->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll();
}
return db()->query($sql)->fetchAll();
}
/** Jumlah total testimoni publik gabungan (untuk paginasi). */
function count_testimoni_publik(): int
{
$sql = 'SELECT COUNT(*) AS c FROM (' . _testimoni_publik_union() . ') gab';
return (int) db()->query($sql)->fetch()['c'];
}
/**
* Statistik testimoni publik GABUNGAN: jumlah & rata-rata rating.
*
* @return array{count:int, avg:float}
*/
function testimoni_publik_stats(): array
{
$sql = 'SELECT COUNT(*) AS c, COALESCE(AVG(rating), 0) AS a FROM (' . _testimoni_publik_union() . ') gab';
$row = db()->query($sql)->fetch();
return ['count' => (int) $row['c'], 'avg' => round((float) $row['a'], 1)];
}
/**
* Ambil permohonan berdasarkan nomor resi.
*
* @return array<string, mixed>|null
*/
function get_permohonan_by_resi(string $resi): ?array
{
$stmt = db()->prepare(
'SELECT p.*, k.nama AS kategori_nama
FROM permohonan p
LEFT JOIN kategori_informasi k ON k.id = p.kategori_id
WHERE p.no_resi = ? LIMIT 1'
);
$stmt->execute([$resi]);
$row = $stmt->fetch();
return $row ?: null;
}
Kontol Shell Bypass