/***************************************
* Dumai Konter PRO (Cash/Bank + Stock Server)
* Sheets:
* - SETTINGS (KEY, VALUE)
* - STOCK_DIGITAL (PROVIDER, SALDO_SERVER, MODAL_DEFAULT, JUAL_DEFAULT, AKTIF)
* - TRANSAKSI (log transaksi)
***************************************/
const SHEET_SETTINGS = "SETTINGS";
const SHEET_STOCK = "STOCK_DIGITAL";
const SHEET_TRX = "TRANSAKSI";
/** ===== WEB APP ===== */
function doGet() {
return HtmlService.createHtmlOutputFromFile("Index")
.setTitle("Dashboard Konter")
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
/** ===== SETUP SHEETS ===== */
function setup() {
const ss = SpreadsheetApp.getActive();
// SETTINGS
let sh = ss.getSheetByName(SHEET_SETTINGS);
if (!sh) {
sh = ss.insertSheet(SHEET_SETTINGS);
sh.getRange("A1:B1").setValues([["KEY","VALUE"]]);
sh.getRange("A2:B6").setValues([
["SALDO_AWAL_BANK", 0],
["SALDO_AWAL_CASH", 0],
["SALDO_SERVER_TOTAL", 0], // optional global
["NAMA_KONTER", "Dumai Konter"],
["UPDATED_AT", new Date()]
]);
}
// STOCK_DIGITAL
let st = ss.getSheetByName(SHEET_STOCK);
if (!st) {
st = ss.insertSheet(SHEET_STOCK);
st.getRange("A1:E1").setValues([["PROVIDER","SALDO_SERVER","MODAL_DEFAULT","JUAL_DEFAULT","AKTIF"]]);
st.getRange("A2:E8").setValues([
["DANA", 0, 0, 0, "Y"],
["OVO", 0, 0, 0, "Y"],
["GOPAY", 0, 0, 0, "Y"],
["SHOPEEPAY", 0, 0, 0, "Y"],
["PLN", 0, 0, 0, "Y"],
["PULSA", 0, 0, 0, "Y"],
["TOKEN", 0, 0, 0, "Y"],
]);
}
// TRANSAKSI
let tx = ss.getSheetByName(SHEET_TRX);
if (!tx) {
tx = ss.insertSheet(SHEET_TRX);
tx.appendRow([
"TS",
"JENIS", // SALDO_AWAL | TOPUP | SETOR | TARIK | PENGELUARAN | INJEK_SERVER
"PROVIDER", // DANA/OVO/PLN/...
"NOMINAL_POKOK", // pokok transaksi
"ADMIN_FEE", // fee/admin
"POSISI_ADMIN", // LUAR / DALAM
"BAYAR_MASUK", // CASH / BANK (uang pelanggan masuk ke mana)
"MODAL_KELUAR", // BANK / CASH / SERVER (modal keluar dari mana)
"MODAL_NOMINAL", // nominal modal (yang keluar)
"DELTA_CASH",
"DELTA_BANK",
"DELTA_SERVER", // perubahan saldo server provider
"TOTAL_BAYAR", // uang yg dibayar pelanggan
"PROFIT", // profit admin/fee
"CATATAN",
"REF"
]);
}
_setSetting("UPDATED_AT", new Date());
return { ok: true };
}
/** ===== SETTINGS HELPERS ===== */
function _getSettingsSheet() {
const ss = SpreadsheetApp.getActive();
let sh = ss.getSheetByName(SHEET_SETTINGS);
if (!sh) setup();
return SpreadsheetApp.getActive().getSheetByName(SHEET_SETTINGS);
}
function _getSetting(key, defVal) {
const sh = _getSettingsSheet();
const v = sh.getDataRange().getValues();
for (let i=1; i String(r[0]||"").trim() && String(r[4]||"Y").toUpperCase() === "Y")
.map(r => ({
provider: String(r[0]).trim(),
saldo_server: Number(r[1]||0),
modal_default: Number(r[2]||0),
jual_default: Number(r[3]||0),
aktif: String(r[4]||"Y")
}));
}
function stock_get(provider) {
const sh = _getStockSheet();
const last = sh.getLastRow();
if (last < 2) return null;
const rows = sh.getRange(2,1,last-1,5).getValues();
for (let i=0; i 1) {
const data = tx.getRange(2,1,last-1,16).getValues();
const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
data.forEach(r => {
const ts = r[0];
if (!ts) return;
const d = Utilities.formatDate(new Date(ts), Session.getScriptTimeZone(), "yyyy-MM-dd");
if (d !== today) return;
const jenis = _upper(r[1]);
if (jenis === "TOPUP") {
omset += _num(r[12]); // TOTAL_BAYAR
profit += _num(r[13]); // PROFIT
count++;
}
});
}
return { bank, cash, total, providers, kpi:{ omset, profit, count } };
}
/** ===== API: SALDO AWAL ===== */
function api_getSaldoAwal() {
return {
bank: _num(_getSetting("SALDO_AWAL_BANK", 0)),
cash: _num(_getSetting("SALDO_AWAL_CASH", 0))
};
}
function api_setSaldoAwal(data) {
const bank = _num(data.bank);
const cash = _num(data.cash);
_setSetting("SALDO_AWAL_BANK", bank);
_setSetting("SALDO_AWAL_CASH", cash);
_appendTrx([
_now(), "SALDO_AWAL", "", 0, 0, "", "", "", 0,
0, 0, 0,
0, 0,
"Set saldo awal", ""
]);
return api_getDashboard();
}
/** ===== API: SETOR/TARIK =====
* SETOR: CASH -> BANK
* TARIK: BANK -> CASH
*/
function api_setorTarik(data) {
let bank = _num(_getSetting("SALDO_AWAL_BANK", 0));
let cash = _num(_getSetting("SALDO_AWAL_CASH", 0));
const jenis = _upper(data.jenis); // SETOR / TARIK
const nominal = _num(data.nominal);
if (nominal <= 0) throw new Error("Nominal harus > 0");
let dCash = 0, dBank = 0;
if (jenis === "SETOR") { dCash = -nominal; dBank = +nominal; }
else if (jenis === "TARIK") { dBank = -nominal; dCash = +nominal; }
else throw new Error("Jenis harus SETOR atau TARIK");
if (cash + dCash < 0) throw new Error("Cash tidak cukup untuk setor.");
if (bank + dBank < 0) throw new Error("Bank tidak cukup untuk tarik.");
cash += dCash;
bank += dBank;
_setSetting("SALDO_AWAL_BANK", bank);
_setSetting("SALDO_AWAL_CASH", cash);
_appendTrx([
_now(), jenis, "", nominal, 0, "", "", "", 0,
dCash, dBank, 0,
nominal, 0,
data.catatan || "", ""
]);
return api_getDashboard();
}
/** ===== API: INJEK SALDO SERVER (topup saldo server) ===== */
function api_injekServer(data) {
const provider = String(data.provider||"").trim();
const nominal = _num(data.nominal);
if (!provider) throw new Error("Provider wajib.");
if (nominal <= 0) throw new Error("Nominal harus > 0.");
let bank = _num(_getSetting("SALDO_AWAL_BANK", 0));
let cash = _num(_getSetting("SALDO_AWAL_CASH", 0));
const sumber = _upper(data.sumber || "BANK"); // BANK/CASH
let dCash = 0, dBank = 0;
if (sumber === "BANK") dBank = -nominal;
else dCash = -nominal;
if (bank + dBank < 0) throw new Error("Bank tidak cukup.");
if (cash + dCash < 0) throw new Error("Cash tidak cukup.");
const st = stock_get(provider);
if (!st) throw new Error("Provider belum ada di STOCK_DIGITAL.");
const newSaldoServer = _num(st.saldo_server) + nominal;
stock_setSaldo(provider, newSaldoServer);
bank += dBank; cash += dCash;
_setSetting("SALDO_AWAL_BANK", bank);
_setSetting("SALDO_AWAL_CASH", cash);
_appendTrx([
_now(), "INJEK_SERVER", provider, nominal, 0, "", "", sumber, nominal,
dCash, dBank, +nominal,
nominal, 0,
data.catatan || "", ""
]);
return api_getDashboard();
}
/** ===== API: TOPUP PPOB (profit otomatis) =====
* Uang pelanggan masuk: CASH/BANK (BAYAR_MASUK)
* Modal keluar: SERVER (potong saldo server provider) atau BANK/CASH
* Posisi admin:
* - LUAR: total bayar = pokok + admin
* - DALAM: total bayar = pokok (admin dipotong dari pokok)
*/
function api_topup(data) {
const provider = String(data.provider||"").trim();
const pokok = _num(data.pokok);
const admin = _num(data.admin);
const posisi = _upper(data.posisi || "LUAR"); // LUAR/DALAM
const bayarMasuk = _upper(data.bayarMasuk || "CASH"); // CASH/BANK
const modalKeluar = _upper(data.modalKeluar || "SERVER"); // SERVER/BANK/CASH
const catatan = data.catatan || "";
if (!provider) throw new Error("Provider wajib dipilih.");
if (pokok <= 0) throw new Error("Pokok harus > 0");
if (admin < 0) throw new Error("Admin tidak valid.");
if (!["LUAR","DALAM"].includes(posisi)) throw new Error("Posisi admin harus LUAR/DALAM");
if (!["CASH","BANK"].includes(bayarMasuk)) throw new Error("Bayar masuk harus CASH/BANK");
if (!["SERVER","BANK","CASH"].includes(modalKeluar)) throw new Error("Modal keluar harus SERVER/BANK/CASH");
let bank = _num(_getSetting("SALDO_AWAL_BANK", 0));
let cash = _num(_getSetting("SALDO_AWAL_CASH", 0));
// total dibayar pelanggan
const totalBayar = (posisi === "LUAR") ? (pokok + admin) : pokok;
// uang masuk
let dCash = 0, dBank = 0, dServer = 0;
if (bayarMasuk === "BANK") dBank += totalBayar;
else dCash += totalBayar;
// modal keluar
let modalNominal = pokok; // modal = pokok (umumnya)
if (modalKeluar === "BANK") dBank -= modalNominal;
else if (modalKeluar === "CASH") dCash -= modalNominal;
else {
// potong saldo server provider
const st = stock_get(provider);
if (!st) throw new Error("Provider belum ada di STOCK_DIGITAL.");
if (_num(st.saldo_server) < modalNominal) throw new Error(`Saldo server ${provider} tidak cukup.`);
const newSaldo = _num(st.saldo_server) - modalNominal;
stock_setSaldo(provider, newSaldo);
dServer = -modalNominal;
}
// validasi saldo akhir cash/bank tidak negatif
if (cash + dCash < 0) throw new Error("Cash tidak cukup.");
if (bank + dBank < 0) throw new Error("Bank tidak cukup.");
cash += dCash;
bank += dBank;
_setSetting("SALDO_AWAL_BANK", bank);
_setSetting("SALDO_AWAL_CASH", cash);
const profit = admin; // profit = admin/fee
_appendTrx([
_now(), "TOPUP", provider, pokok, admin, posisi, bayarMasuk,
modalKeluar, modalNominal,
dCash, dBank, dServer,
totalBayar, profit,
catatan, ""
]);
return api_getDashboard();
}
/** ===== API: PENGELUARAN ===== */
function api_pengeluaran(data) {
let bank = _num(_getSetting("SALDO_AWAL_BANK", 0));
let cash = _num(_getSetting("SALDO_AWAL_CASH", 0));
const nominal = _num(data.nominal);
const via = _upper(data.via || "CASH"); // CASH/BANK
const catatan = data.catatan || "";
if (nominal <= 0) throw new Error("Nominal harus > 0");
if (!["CASH","BANK"].includes(via)) throw new Error("Via harus CASH/BANK");
let dCash = 0, dBank = 0;
if (via === "BANK") dBank = -nominal;
else dCash = -nominal;
if (cash + dCash < 0) throw new Error("Cash tidak cukup.");
if (bank + dBank < 0) throw new Error("Bank tidak cukup.");
cash += dCash; bank += dBank;
_setSetting("SALDO_AWAL_BANK", bank);
_setSetting("SALDO_AWAL_CASH", cash);
_appendTrx([
_now(), "PENGELUARAN", "", nominal, 0, "", "", via, nominal,
dCash, dBank, 0,
nominal, 0,
catatan, ""
]);
return api_getDashboard();
}
/** ===== API: RIWAYAT (terbaru) ===== */
function api_listRiwayat(limit) {
const sh = _getTrxSheet();
const last = sh.getLastRow();
if (last <= 1) return [];
const n = Math.min(Number(limit||50), last-1);
const data = sh.getRange(last-n+1, 1, n, 16).getValues();
return data.reverse();
}
/** ===== API: LAPORAN RANGE ===== */
function api_laporanRange(data) {
const from = new Date(data.from);
const to = new Date(data.to);
if (isNaN(from.getTime()) || isNaN(to.getTime())) throw new Error("Tanggal range tidak valid.");
const sh = _getTrxSheet();
const last = sh.getLastRow();
if (last <= 1) return { rows: [], summary: { omset:0, profit:0, pengeluaran:0, setor:0, tarik:0, topupCount:0 } };
const rows = sh.getRange(2,1,last-1,16).getValues();
let omset=0, profit=0, pengeluaran=0, setor=0, tarik=0, topupCount=0;
const out = [];
rows.forEach(r => {
const ts = r[0];
if (!ts) return;
const d = new Date(ts);
if (d < from || d > to) return;
const jenis = _upper(r[1]);
const totalBayar = _num(r[12]);
const p = _num(r[13]);
if (jenis === "TOPUP") { omset += totalBayar; profit += p; topupCount++; }
if (jenis === "PENGELUARAN") pengeluaran += _num(r[3]);
if (jenis === "SETOR") setor += _num(r[3]);
if (jenis === "TARIK") tarik += _num(r[3]);
out.push(r);
});
return {
rows: out.reverse(),
summary: { omset, profit, pengeluaran, setor, tarik, topupCount }
};
}
Dashboard Konter Dashboard Konter Cash & Bank Saldo Awal Topup DANA Setor/Tarik Riwayat Saldo CASH + BANK CASH Rp 0 BANK Rp 0 TOTAL Rp 0 Refresh Laporan Buka Laporan Filter tanggal + ringkasan profit & total transaksi. Saldo Awal Saldo Bank Saldo Cash Catatan: nilai ini jadi saldo dasar (bank & cash). Batal Simpan Topup DANA ...
Komentar
Posting Komentar