Compare commits

..

9 Commits

4 changed files with 435 additions and 17 deletions

View File

@@ -0,0 +1,271 @@
-- CreateEnum
CREATE TYPE "StatusPengaduan" AS ENUM ('diterima', 'antrian', 'dikerjakan', 'ditolak', 'selesai');
-- CreateTable
CREATE TABLE "Role" (
"id" TEXT NOT NULL,
"name" TEXT NOT NULL,
"permissions" JSONB,
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "Role_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "User" (
"id" TEXT NOT NULL,
"roleId" TEXT,
"name" TEXT,
"email" TEXT,
"password" TEXT,
"phone" TEXT,
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "ApiKey" (
"id" TEXT NOT NULL,
"userId" TEXT NOT NULL,
"name" TEXT NOT NULL,
"key" TEXT NOT NULL,
"description" TEXT,
"expiredAt" TIMESTAMP(3),
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "ApiKey_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "Credential" (
"id" TEXT NOT NULL,
"name" TEXT,
"value" TEXT,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "Credential_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "CategoryPengaduan" (
"id" TEXT NOT NULL,
"name" TEXT NOT NULL,
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "CategoryPengaduan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "Pengaduan" (
"id" TEXT NOT NULL,
"idCategory" TEXT NOT NULL,
"idWarga" TEXT NOT NULL,
"noPengaduan" TEXT NOT NULL,
"title" TEXT,
"detail" TEXT,
"location" TEXT,
"image" TEXT,
"keterangan" TEXT,
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "Pengaduan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "HistoryPengaduan" (
"id" TEXT NOT NULL,
"idPengaduan" TEXT NOT NULL,
"idUser" TEXT,
"deskripsi" TEXT,
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "HistoryPengaduan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "Warga" (
"id" TEXT NOT NULL,
"name" TEXT,
"phone" TEXT,
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "Warga_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "CategoryPelayanan" (
"id" TEXT NOT NULL,
"name" TEXT NOT NULL,
"syaratDokumen" JSONB[],
"dataText" TEXT[] DEFAULT ARRAY[]::TEXT[],
"dataPelengkap" JSONB[] DEFAULT ARRAY[]::JSONB[],
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "CategoryPelayanan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "PelayananAjuan" (
"id" TEXT NOT NULL,
"idWarga" TEXT NOT NULL,
"idCategory" TEXT NOT NULL,
"noPengajuan" TEXT NOT NULL,
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "PelayananAjuan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "HistoryPelayanan" (
"id" TEXT NOT NULL,
"idPengajuanLayanan" TEXT NOT NULL,
"idUser" TEXT,
"deskripsi" TEXT,
"keteranganAlasan" TEXT,
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "HistoryPelayanan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "SyaratDokumenPelayanan" (
"id" TEXT NOT NULL,
"idPengajuanLayanan" TEXT NOT NULL,
"idCategory" TEXT NOT NULL,
"jenis" TEXT NOT NULL,
"value" TEXT NOT NULL,
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "SyaratDokumenPelayanan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "DataTextPelayanan" (
"id" TEXT NOT NULL,
"idPengajuanLayanan" TEXT NOT NULL,
"idCategory" TEXT NOT NULL,
"jenis" TEXT NOT NULL,
"value" TEXT NOT NULL,
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "DataTextPelayanan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "SuratPelayanan" (
"id" TEXT NOT NULL,
"idPengajuanLayanan" TEXT NOT NULL,
"idCategory" TEXT NOT NULL,
"idWarga" TEXT NOT NULL,
"noSurat" TEXT NOT NULL,
"file" TEXT,
"dateExpired" DATE,
"status" INTEGER NOT NULL DEFAULT 0,
"isActive" BOOLEAN NOT NULL DEFAULT true,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "SuratPelayanan_pkey" PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "Configuration" (
"id" TEXT NOT NULL,
"name" TEXT NOT NULL,
"value" TEXT NOT NULL,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL,
CONSTRAINT "Configuration_pkey" PRIMARY KEY ("id")
);
-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
-- CreateIndex
CREATE UNIQUE INDEX "User_phone_key" ON "User"("phone");
-- CreateIndex
CREATE UNIQUE INDEX "ApiKey_key_key" ON "ApiKey"("key");
-- CreateIndex
CREATE UNIQUE INDEX "Warga_phone_key" ON "Warga"("phone");
-- AddForeignKey
ALTER TABLE "User" ADD CONSTRAINT "User_roleId_fkey" FOREIGN KEY ("roleId") REFERENCES "Role"("id") ON DELETE SET NULL ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "ApiKey" ADD CONSTRAINT "ApiKey_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "Pengaduan" ADD CONSTRAINT "Pengaduan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPengaduan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "Pengaduan" ADD CONSTRAINT "Pengaduan_idWarga_fkey" FOREIGN KEY ("idWarga") REFERENCES "Warga"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "HistoryPengaduan" ADD CONSTRAINT "HistoryPengaduan_idPengaduan_fkey" FOREIGN KEY ("idPengaduan") REFERENCES "Pengaduan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "HistoryPengaduan" ADD CONSTRAINT "HistoryPengaduan_idUser_fkey" FOREIGN KEY ("idUser") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "PelayananAjuan" ADD CONSTRAINT "PelayananAjuan_idWarga_fkey" FOREIGN KEY ("idWarga") REFERENCES "Warga"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "PelayananAjuan" ADD CONSTRAINT "PelayananAjuan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "HistoryPelayanan" ADD CONSTRAINT "HistoryPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "HistoryPelayanan" ADD CONSTRAINT "HistoryPelayanan_idUser_fkey" FOREIGN KEY ("idUser") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "SyaratDokumenPelayanan" ADD CONSTRAINT "SyaratDokumenPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "SyaratDokumenPelayanan" ADD CONSTRAINT "SyaratDokumenPelayanan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "DataTextPelayanan" ADD CONSTRAINT "DataTextPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "DataTextPelayanan" ADD CONSTRAINT "DataTextPelayanan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "SuratPelayanan" ADD CONSTRAINT "SuratPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "SuratPelayanan" ADD CONSTRAINT "SuratPelayanan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE "SuratPelayanan" ADD CONSTRAINT "SuratPelayanan_idWarga_fkey" FOREIGN KEY ("idWarga") REFERENCES "Warga"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

View File

@@ -0,0 +1,3 @@
# Please do not edit this file manually
# It should be added in your version-control system (e.g., Git)
provider = "postgresql"

View File

@@ -58,13 +58,13 @@ const NocRoute = new Elysia({
}, {
detail: {
summary: "Get jumlah surat minggu ini dan kenaikan dari minggu lalu",
description: `tool untuk mendapatkan jumlah surat minggu ini dan persentase kenaikan dibandingkan minggu lalu`,
summary: "Jumlah surat minggu ini",
description: `Menu beranda - tool untuk mendapatkan jumlah surat minggu ini dan persentase kenaikan dibandingkan minggu lalu`,
}
})
.get("/pengaduan-count", async () => {
const [antrian, diterima, dikerjakan] = await Promise.all([
const [antrian, diterima, dikerjakan, ditolak, selesai] = await Promise.all([
prisma.pengaduan.count({
where: {
isActive: true,
@@ -82,6 +82,18 @@ const NocRoute = new Elysia({
isActive: true,
status: "diterima",
}
}),
prisma.pengaduan.count({
where: {
isActive: true,
status: "ditolak",
}
}),
prisma.pengaduan.count({
where: {
isActive: true,
status: "selesai",
}
})
]);
@@ -89,18 +101,21 @@ const NocRoute = new Elysia({
antrian,
diterima,
dikerjakan,
total: antrian + diterima + dikerjakan
ditolak,
selesai,
aktif: antrian + diterima + dikerjakan,
total: antrian + diterima + dikerjakan + ditolak + selesai
};
}, {
detail: {
summary: "Get jumlah pengaduan antrian, diterima dan dikerjakan",
description: "Menghitung jumlah pengaduan yang berstatus antrian dan sedang dikerjakan (diproses)",
summary: "Jumlah pengaduan berdasarkan status",
description: `Menu beranda dan pengaduan layanan publik - Menghitung jumlah pengaduan yang sedang aktif (antrian, diterima, dikerjakan), dan total (termasuk ditolak dan selesai)`,
}
})
.get("/pelayanan-count", async () => {
const now = new Date();
// Bulan ini
const startOfThisMonth = new Date(now.getFullYear(), now.getMonth(), 1);
const endOfThisMonth = new Date(now.getFullYear(), now.getMonth() + 1, 1);
@@ -145,8 +160,8 @@ const NocRoute = new Elysia({
};
}, {
detail: {
summary: "Get total pelayananAjuan selesai bulan ini dan kenaikan dari bulan lalu",
description: "Menampilkan total pelayananAjuan yang telah berstatus selesai bulan ini dan persentase kenaikan dari bulan lalu",
summary: "Total pelayanan selesai bulan ini dan kenaikan dari bulan lalu",
description: `Menu beranda - Menampilkan total pelayanan yang telah berstatus selesai bulan ini dan persentase kenaikan dari bulan lalu`,
}
})
@@ -155,7 +170,7 @@ const NocRoute = new Elysia({
const now = new Date();
const results: { label: string; total: number }[] = [];
if (period === "4weeks") {
if (period === "6weeks") {
// Get the most recent Monday
const currentDay = now.getDay();
const diffToMonday = (currentDay === 0 ? 6 : currentDay - 1);
@@ -163,10 +178,10 @@ const NocRoute = new Elysia({
startOfCurrentWeek.setDate(now.getDate() - diffToMonday);
startOfCurrentWeek.setHours(0, 0, 0, 0);
for (let i = 3; i >= 0; i--) {
for (let i = 5; i >= 0; i--) {
const startOfWeek = new Date(startOfCurrentWeek);
startOfWeek.setDate(startOfCurrentWeek.getDate() - (i * 7));
const endOfWeek = new Date(startOfWeek);
endOfWeek.setDate(startOfWeek.getDate() + 7);
@@ -180,7 +195,7 @@ const NocRoute = new Elysia({
}
});
const label = `Minggu ${4 - i}`;
const label = `Minggu ${6 - i}`;
results.push({ label, total: count });
}
} else {
@@ -207,8 +222,137 @@ const NocRoute = new Elysia({
return results;
}, {
detail: {
summary: "Get history total pengajuan surat",
description: "Menampilkan total pengajuan surat selama 6 bulan terakhir atau 4 minggu terakhir",
summary: "Statistik pengajuan surat 6 bulan / 6 minggu",
description: `Menu beranda - Menampilkan statistik pengajuan surat selama 6 bulan terakhir atau 6 minggu terakhir`,
}
})
.get("/pengaduan-history", async ({ query }) => {
const { period = "6months" } = query as { period?: string };
const now = new Date();
const results: { label: string; total: number }[] = [];
if (period === "7days") {
for (let i = 6; i >= 0; i--) {
const startOfDay = new Date(now);
startOfDay.setDate(now.getDate() - i);
startOfDay.setHours(0, 0, 0, 0);
const endOfDay = new Date(startOfDay);
endOfDay.setDate(startOfDay.getDate() + 1);
const count = await prisma.pengaduan.count({
where: {
isActive: true,
createdAt: {
gte: startOfDay,
lt: endOfDay,
}
}
});
const label = startOfDay.toLocaleDateString('id-ID', { weekday: 'long' });
results.push({ label, total: count });
}
} else {
// Default 6 months
for (let i = 5; i >= 0; i--) {
const startOfMonth = new Date(now.getFullYear(), now.getMonth() - i, 1);
const endOfMonth = new Date(now.getFullYear(), now.getMonth() - i + 1, 1);
const count = await prisma.pengaduan.count({
where: {
isActive: true,
createdAt: {
gte: startOfMonth,
lt: endOfMonth,
}
}
});
const monthName = startOfMonth.toLocaleString('id-ID', { month: 'long' });
results.push({ label: monthName, total: count });
}
}
return results;
}, {
detail: {
summary: "Statistik total pengaduan 6 bulan / 7 hari",
description: `Menu pengaduan layanan publik - Menampilkan statistik total pengaduan selama 6 bulan terakhir atau 7 hari terakhir`,
}
})
.get("/pelayanan-perjenis", async () => {
const categories = await prisma.categoryPelayanan.findMany({
where: {
isActive: true
},
select: {
name: true,
_count: {
select: {
SuratPelayanan: {
where: {
isActive: true
}
}
}
}
}
});
return categories
.map(cat => ({
jenis: cat.name,
jumlah: cat._count.SuratPelayanan
}))
.sort((a, b) => b.jumlah - a.jumlah);
}, {
detail: {
summary: "Jumlah surat terbanyak berdasarkan jenis surat",
description: `Menu pengaduan layanan publik - Menampilkan jumlah surat berdasarkan jenis/kategori pelayanan`,
}
})
.get("/pengajuan-terbaru", async () => {
const applications = await prisma.pelayananAjuan.findMany({
where: {
isActive: true
},
take: 5,
orderBy: {
createdAt: "desc"
},
include: {
Warga: true,
CategoryPelayanan: true
}
});
const formatDuration = (date: Date) => {
const diff = Math.floor((new Date().getTime() - date.getTime()) / 1000);
if (diff < 60) return `${diff} detik yang lalu`;
if (diff < 3600) return `${Math.floor(diff / 60)} menit yang lalu`;
if (diff < 86400) return `${Math.floor(diff / 3600)} jam yang lalu`;
if (diff < 604800) return `${Math.floor(diff / 86400)} hari yang lalu`;
return date.toLocaleDateString("id-ID", {
day: "numeric",
month: "long",
year: "numeric"
});
};
return applications.map(app => ({
jenis: app.CategoryPelayanan.name,
status: app.status,
namaWarga: app.Warga.name,
durasi: formatDuration(app.createdAt)
}));
}, {
detail: {
summary: "5 data pengajuan surat terbaru",
description: `Menu pengaduan layanan publik - Menampilkan 5 data pengajuan surat terbaru beserta status, nama warga, dan durasi pengajuan`,
}
})

View File

@@ -49,7 +49,7 @@ Terima kasih atas partisipasi dan kepercayaan Bapak/Ibu.`
const textFix = encodeURIComponent(text)
const res = await fetch(
`https://cld-dkr-prod-wajs-server.wibudev.com/api/wa/code?nom=${tlp}&text=${textFix}`,
`${process.env.WA_SERVER_URL}/api/wa/code?nom=${tlp}&text=${textFix}`,
{
cache: "no-cache",
headers: {
@@ -119,7 +119,7 @@ Terima kasih atas kepercayaan Bapak/Ibu.`
const res = await fetch(
`https://cld-dkr-prod-wajs-server.wibudev.com/api/wa/code?nom=${tlp}&text=${textFix}`,
`${process.env.WA_SERVER_URL}/api/wa/code?nom=${tlp}&text=${textFix}`,
{
cache: "no-cache",
headers: {