/****************************
* DUMAI KONTER - FULL FIX
* Sheets:
* - SETTINGS (saldo awal & saldo berjalan)
* - PRODUK (barang)
* - TRANSAKSI (riwayat)
****************************/
const SHEET_SETTINGS = "SETTINGS";
const SHEET_PRODUK = "PRODUK";
const SHEET_TRX = "TRANSAKSI";
/** ===== WEBAPP ===== **/
function doGet(){
return HtmlService.createHtmlOutputFromFile("Index")
.setTitle("Dashboard Konter")
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
/** ===== SETUP ===== **/
function setup(){
_getSettingsSheet_();
_getProdukSheet_();
_getTrxSheet_();
return { ok:true };
}
/** ===== SHEET HELPERS ===== **/
function _ss_(){ return SpreadsheetApp.getActive(); }
function _getSettingsSheet_(){
const ss = _ss_();
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_BANK", 0],
["SALDO_CASH", 0],
["SALDO_AWAL_BANK", 0],
["SALDO_AWAL_CASH", 0],
["UPDATED_AT", new Date()]
]);
}
return sh;
}
function _getProdukSheet_(){
const ss = _ss_();
let sh = ss.getSheetByName(SHEET_PRODUK);
if (!sh){
sh = ss.insertSheet(SHEET_PRODUK);
sh.appendRow(["KODE","NAMA","STOK","MODAL","JUAL"]);
}
return sh;
}
function _getTrxSheet_(){
const ss = _ss_();
let sh = ss.getSheetByName(SHEET_TRX);
if (!sh){
sh = ss.insertSheet(SHEET_TRX);
sh.appendRow([
"TS","JENIS","DETAIL",
"NOMINAL_POKOK","ADMIN","POSISI_ADMIN",
"MODAL_DARI","BAYAR_MASUK",
"DELTA_CASH","DELTA_BANK",
"TOTAL_BAYAR","PROFIT","CATATAN"
]);
}
return sh;
}
function _getKV_(key, def){
const sh = _getSettingsSheet_();
const v = sh.getDataRange().getValues();
for (let i=1;i{
const ts = r[0] instanceof Date ? r[0] : new Date(r[0]);
if (ts >= fromD && ts <= toD){
const jenis = String(r[1]||"");
const totalBayar = _asNum_(r[10]);
const pr = _asNum_(r[11]);
// Omset: yang bener-bener uang masuk dari pelanggan
if (jenis === "TOPUP_DANA" || jenis === "JUAL_BARANG"){
omset += totalBayar;
profit += pr;
count += 1;
}
}
});
return { omset, profit, count };
}
/** =========================================================
* API - SALDO AWAL (modal)
* ========================================================= */
function api_getSaldoAwal(){
return {
bank: _asNum_(_getKV_("SALDO_AWAL_BANK",0)),
cash: _asNum_(_getKV_("SALDO_AWAL_CASH",0)),
};
}
function api_setSaldoAwal(data){
const bank = _asNum_(data.bank);
const cash = _asNum_(data.cash);
// simpan saldo awal
_setKV_("SALDO_AWAL_BANK", bank);
_setKV_("SALDO_AWAL_CASH", cash);
// set saldo berjalan = saldo awal (reset)
_setSaldo_(bank, cash);
_appendTrx_([
new Date(), "SALDO_AWAL", "Set saldo awal",
0,0,"",
"","",
0,0,
0,0,
String(data.catatan||"")
]);
return { ok:true, saldo:_getSaldo_() };
}
/** =========================================================
* API - SETOR / TARIK (transfer internal)
* jenis: SETOR (cash->bank) / TARIK (bank->cash)
* ========================================================= */
function api_setorTarik(data){
let { bank, cash } = _getSaldo_();
const nominal = _asNum_(data.nominal);
const jenis = _up_(data.jenis); // SETOR / TARIK
const catatan = String(data.catatan||"");
if (nominal <= 0) throw new Error("Nominal harus > 0");
if (jenis !== "SETOR" && jenis !== "TARIK") throw new Error("Jenis harus SETOR/TARIK");
let dBank=0, dCash=0;
if (jenis === "SETOR"){
if (cash < nominal) throw new Error("Cash tidak cukup untuk setor.");
dCash = -nominal; dBank = +nominal;
} else {
if (bank < nominal) throw new Error("Bank tidak cukup untuk tarik.");
dBank = -nominal; dCash = +nominal;
}
bank += dBank; cash += dCash;
_setSaldo_(bank, cash);
_appendTrx_([
new Date(), "SETOR_TARIK", jenis,
nominal,0,"",
"","",
dCash, dBank,
nominal, 0,
catatan
]);
return { ok:true, saldo:_getSaldo_() };
}
/** =========================================================
* API - TOPUP DANA
* modal_dari default BANK
* bayar_masuk: CASH/BANK
* posisi_admin: LUAR/DALAM
* ========================================================= */
function api_topupDana(data){
let { bank, cash } = _getSaldo_();
const nominal = _asNum_(data.nominal);
const admin = _asNum_(data.admin);
const posisi = _up_(data.posisi || "LUAR"); // LUAR/DALAM
const modalDari = _up_(data.modal_dari || "BANK"); // BANK/CASH
const bayarMasuk = _up_(data.bayar_masuk || "CASH"); // CASH/BANK
const catatan = String(data.catatan||"");
if (nominal <= 0) throw new Error("Nominal pokok harus > 0");
if (admin < 0) throw new Error("Admin tidak boleh negatif");
if (!["LUAR","DALAM"].includes(posisi)) throw new Error("Posisi admin harus LUAR/DALAM");
if (!["BANK","CASH"].includes(modalDari)) throw new Error("Modal dari harus BANK/CASH");
if (!["BANK","CASH"].includes(bayarMasuk)) throw new Error("Bayar masuk harus BANK/CASH");
// uang keluar buat beli saldo/topup
const modalKeluar = nominal;
// uang masuk dari pelanggan
const totalBayar = (posisi === "LUAR") ? (nominal + admin) : nominal;
const profit = admin;
let dBank=0, dCash=0;
// kurangi modal
if (modalDari === "BANK"){
if (bank < modalKeluar) throw new Error("Saldo BANK tidak cukup untuk modal topup.");
dBank -= modalKeluar;
} else {
if (cash < modalKeluar) throw new Error("Saldo CASH tidak cukup untuk modal topup.");
dCash -= modalKeluar;
}
// tambah uang masuk pelanggan
if (bayarMasuk === "BANK") dBank += totalBayar;
else dCash += totalBayar;
bank += dBank; cash += dCash;
_setSaldo_(bank, cash);
_appendTrx_([
new Date(), "TOPUP_DANA", "Topup DANA",
nominal, admin, posisi,
modalDari, bayarMasuk,
dCash, dBank,
totalBayar, profit,
catatan
]);
return { ok:true, saldo:_getSaldo_(), totalBayar, profit };
}
/** =========================================================
* API - BARANG
* ========================================================= */
function api_barangList(){
const sh = _getProdukSheet_();
const last = sh.getLastRow();
if (last <= 1) return [];
const vals = sh.getRange(2,1,last-1,5).getValues();
return vals.map(r=>({
kode: String(r[0]||""),
nama: String(r[1]||""),
stok: _asNum_(r[2]),
modal: _asNum_(r[3]),
jual: _asNum_(r[4]),
profit: _asNum_(r[4]) - _asNum_(r[3])
}));
}
function api_barangUpsert(data){
const kode = String(data.kode||"").trim();
const nama = String(data.nama||"").trim();
const stok = _asNum_(data.stok);
const modal = _asNum_(data.modal);
const jual = _asNum_(data.jual);
if (!kode) throw new Error("Kode wajib");
if (!nama) throw new Error("Nama wajib");
if (stok < 0) throw new Error("Stok tidak boleh negatif");
if (modal < 0 || jual < 0) throw new Error("Modal/jual tidak boleh negatif");
const sh = _getProdukSheet_();
const last = sh.getLastRow();
const vals = last>1 ? sh.getRange(2,1,last-1,5).getValues() : [];
let rowIndex = -1;
for (let i=0;i{
const ts = r[0] instanceof Date ? r[0] : new Date(r[0]);
if (ts >= fromD && ts <= toD) rows.push(_mapTrxRow_(r));
});
const summary = _kpiHarian_(from, to);
rows.reverse();
return { rows, summary };
}
function _mapTrxRow_(r){
return {
ts: r[0],
jenis: String(r[1]||""),
detail: String(r[2]||""),
pokok: _asNum_(r[3]),
admin: _asNum_(r[4]),
posisi_admin: String(r[5]||""),
modal_dari: String(r[6]||""),
bayar_masuk: String(r[7]||""),
d_cash: _asNum_(r[8]),
d_bank: _asNum_(r[9]),
total_bayar: _asNum_(r[10]),
profit: _asNum_(r[11]),
catatan: String(r[12]||"")
};
}
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