1193 lines
43 KiB
JavaScript
1193 lines
43 KiB
JavaScript
function getSS_() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
if (ss) return ss;
|
|
var id = PropertiesService.getScriptProperties().getProperty('spreadsheet_id');
|
|
if (id) return SpreadsheetApp.openById(id);
|
|
throw new Error('Spreadsheet aktif tidak ditemukan.');
|
|
}
|
|
|
|
function getDebugInfo() {
|
|
try {
|
|
var props = PropertiesService.getScriptProperties();
|
|
var id = props.getProperty('spreadsheet_id') || '';
|
|
var active = SpreadsheetApp.getActiveSpreadsheet();
|
|
var activeId = active ? active.getId() : '';
|
|
var openOk = false;
|
|
var ss = null;
|
|
var title = '';
|
|
var url = '';
|
|
var sheets = [];
|
|
try {
|
|
ss = getSS_();
|
|
openOk = true;
|
|
title = ss.getName();
|
|
url = ss.getUrl();
|
|
sheets = ss.getSheets().map(function(s) { return s.getName(); });
|
|
} catch (e) {
|
|
openOk = false;
|
|
}
|
|
return JSON.stringify({
|
|
spreadsheet_id: id,
|
|
activeSpreadsheetId: activeId,
|
|
openOk: openOk,
|
|
spreadsheetTitle: title,
|
|
spreadsheetUrl: url,
|
|
sheets: sheets
|
|
}, null, 2);
|
|
} catch (e2) {
|
|
return JSON.stringify({ ok: false, error: (e2 && e2.message) ? e2.message : String(e2) });
|
|
}
|
|
}
|
|
|
|
function normalizeDateStr_(dateStr) {
|
|
var today = Utilities.formatDate(new Date(), 'GMT+7', 'yyyy-MM-dd');
|
|
if (!dateStr) return today;
|
|
var s = String(dateStr).trim();
|
|
return /^\d{4}-\d{2}-\d{2}$/.test(s) ? s : today;
|
|
}
|
|
|
|
function findRowBlocksByDate_(sheet, colIndex, targetDateStr, maxScanRows) {
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return [];
|
|
var limit = Number(maxScanRows) || 10000;
|
|
if (limit < 1) limit = 10000;
|
|
var startRow = Math.max(2, lastRow - limit + 1);
|
|
var range = sheet.getRange(startRow, colIndex, lastRow - startRow + 1, 1);
|
|
var finder = range.createTextFinder(String(targetDateStr)).matchEntireCell(true);
|
|
var cells = finder.findAll();
|
|
if (!cells || !cells.length) return [];
|
|
var rows = cells.map(function(c) { return c.getRow(); }).sort(function(a, b) { return a - b; });
|
|
var blocks = [];
|
|
var start = rows[0];
|
|
var prev = rows[0];
|
|
for (var i = 1; i < rows.length; i++) {
|
|
var r = rows[i];
|
|
if (r === prev + 1) {
|
|
prev = r;
|
|
continue;
|
|
}
|
|
blocks.push({ start: start, len: prev - start + 1 });
|
|
start = r;
|
|
prev = r;
|
|
}
|
|
blocks.push({ start: start, len: prev - start + 1 });
|
|
return blocks;
|
|
}
|
|
|
|
function readRowBlocks_(sheet, blocks, colCount) {
|
|
var out = [];
|
|
if (!blocks || !blocks.length) return out;
|
|
for (var i = 0; i < blocks.length; i++) {
|
|
var b = blocks[i];
|
|
var vals = sheet.getRange(b.start, 1, b.len, colCount).getValues();
|
|
for (var j = 0; j < vals.length; j++) out.push(vals[j]);
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function doGet() {
|
|
setupSheets();
|
|
return HtmlService.createTemplateFromFile('Index')
|
|
.evaluate()
|
|
.setTitle('Fuku - Stok & Belanja')
|
|
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
|
|
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
|
|
}
|
|
|
|
function setupSheets() {
|
|
var ss = getSS_();
|
|
var sheets = {
|
|
'StokHarian': ['Tanggal', 'Menu', 'Stok Awal', 'Re-stock', 'Terpakai', 'Sisa', 'Lokasi', 'User', 'Foto', 'Timestamp'],
|
|
'Belanja': ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'User', 'Timestamp'],
|
|
'Menu': ['Nama', 'Harga', 'Kategori', 'Lokasi', 'Min Stok', 'Status'],
|
|
'PegawaiList': ['ID', 'Nama', 'Status', 'Catatan', 'User', 'Timestamp'],
|
|
'Pegawai': ['ID', 'Tanggal', 'Nama', 'Nilai', 'Kategori', 'Catatan', 'User', 'Timestamp'],
|
|
'Operasional': ['ID', 'Tanggal', 'Nama', 'Harga', 'Qty', 'Total', 'Catatan', 'User', 'Timestamp']
|
|
};
|
|
|
|
for (var name in sheets) {
|
|
if (!ss.getSheetByName(name)) {
|
|
var sheet = ss.insertSheet(name);
|
|
sheet.appendRow(sheets[name]);
|
|
sheet.getRange(1, 1, 1, sheets[name].length).setFontWeight('bold').setBackground('#f3f3f3');
|
|
|
|
if (name === 'Menu') {
|
|
var defaultItems = [
|
|
// Lantai Atas
|
|
['Beef', 0, 'Stok', 'Lantai Atas', 5, 'Tersedia'],
|
|
['Saikoro', 0, 'Stok', 'Lantai Atas', 5, 'Tersedia'],
|
|
['Chikuwa', 0, 'Stok', 'Lantai Atas', 10, 'Tersedia'],
|
|
// ... (item lainnya disingkat untuk efisiensi, Anda bisa mengisinya manual di sheet)
|
|
];
|
|
sheet.getRange(2, 1, defaultItems.length, 6).setValues(defaultItems);
|
|
}
|
|
} else {
|
|
// Update headers if needed (for existing sheets)
|
|
var sheet = ss.getSheetByName(name);
|
|
var currentHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
|
|
if (currentHeaders.length < sheets[name].length) {
|
|
sheet.getRange(1, 1, 1, sheets[name].length).setValues([sheets[name]]).setFontWeight('bold').setBackground('#f3f3f3');
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
function getInitialData(dateStr) {
|
|
var d = normalizeDateStr_(dateStr);
|
|
try {
|
|
return {
|
|
tanggal: d,
|
|
stok: getStokData(d, false),
|
|
belanja: getBelanjaData(d),
|
|
menu: getMenuListExtended(),
|
|
pegawaiList: getPegawaiList(),
|
|
pegawai: getPegawaiData(d),
|
|
operasional: getOperasionalData(d),
|
|
ok: true
|
|
};
|
|
} catch (e) {
|
|
return {
|
|
tanggal: d,
|
|
stok: [],
|
|
belanja: [],
|
|
menu: [],
|
|
pegawaiList: [],
|
|
pegawai: [],
|
|
operasional: [],
|
|
ok: false,
|
|
error: e && e.message ? e.message : String(e)
|
|
};
|
|
}
|
|
}
|
|
|
|
function getPegawaiList() {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('PegawaiList');
|
|
if (!sheet) return [];
|
|
var data = sheet.getDataRange().getValues();
|
|
return data.slice(1).filter(function(r) { return r && r[1]; }).map(function(r) {
|
|
return {
|
|
id: String(r[0] || ''),
|
|
nama: String(r[1] || ''),
|
|
status: String(r[2] || 'Aktif'),
|
|
catatan: String(r[3] || '')
|
|
};
|
|
});
|
|
}
|
|
|
|
function savePegawaiList(rows, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('PegawaiList');
|
|
if (!sheet) throw new Error('Sheet PegawaiList tidak ditemukan.');
|
|
var now = new Date();
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow > 1) sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clearContent();
|
|
var out = [];
|
|
var seen = {};
|
|
(rows || []).forEach(function(r) {
|
|
var nama = String((r && r.nama) || '').trim();
|
|
if (!nama) return;
|
|
var key = nama.toLowerCase();
|
|
if (seen[key]) return;
|
|
seen[key] = true;
|
|
var id = (r && r.id) ? String(r.id) : ('E-' + Date.now() + '-' + Math.floor(Math.random() * 1000));
|
|
var status = String((r && r.status) || 'Aktif');
|
|
var catatan = String((r && r.catatan) || '');
|
|
out.push([id, nama, status, catatan, userName || 'Unknown', now]);
|
|
});
|
|
if (out.length) sheet.getRange(2, 1, out.length, 6).setValues(out);
|
|
return getPegawaiList();
|
|
}
|
|
|
|
function getPegawaiData(dateStr) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Pegawai');
|
|
if (!sheet) return [];
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var rows = getRowsByDateExact_(sheet, 2, 8, targetDate, 30000);
|
|
if (!rows.length) return [];
|
|
return rows.map(function(r) {
|
|
return { id: r[0], tanggal: r[1], nama: r[2], nilai: r[3], kategori: r[4], catatan: r[5] };
|
|
});
|
|
}
|
|
|
|
function normalizeSheetDateValue_(v) {
|
|
if (!v) return '';
|
|
if (v instanceof Date) return Utilities.formatDate(v, 'GMT+7', 'yyyy-MM-dd');
|
|
var s = String(v).trim();
|
|
if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
|
|
var m = s.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/);
|
|
if (m) {
|
|
var d = Number(m[1]) || 0;
|
|
var mo = Number(m[2]) || 0;
|
|
var y = Number(m[3]) || 0;
|
|
if (d >= 1 && d <= 31 && mo >= 1 && mo <= 12 && y >= 1900) {
|
|
return y + '-' + String(mo).padStart(2, '0') + '-' + String(d).padStart(2, '0');
|
|
}
|
|
}
|
|
var parsed = new Date(s);
|
|
if (!isNaN(parsed.getTime())) return Utilities.formatDate(parsed, 'GMT+7', 'yyyy-MM-dd');
|
|
return '';
|
|
}
|
|
|
|
function getRowsByDateExact_(sheet, dateColIndex, colCount, targetDate, maxScanRows) {
|
|
if (!sheet) return [];
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return [];
|
|
var limit = Number(maxScanRows) || 30000;
|
|
if (limit < 1) limit = 30000;
|
|
var startRow = Math.max(2, lastRow - limit + 1);
|
|
var rowCount = lastRow - startRow + 1;
|
|
if (rowCount <= 0) return [];
|
|
var values = sheet.getRange(startRow, 1, rowCount, colCount).getValues();
|
|
var out = [];
|
|
for (var i = 0; i < values.length; i++) {
|
|
var row = values[i];
|
|
var tgl = normalizeSheetDateValue_(row[dateColIndex - 1]);
|
|
if (tgl === targetDate) out.push(row);
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function normalizeDateRange_(fromStr, toStr) {
|
|
var from = normalizeDateStr_(fromStr);
|
|
var to = normalizeDateStr_(toStr);
|
|
if (from > to) {
|
|
var tmp = from;
|
|
from = to;
|
|
to = tmp;
|
|
}
|
|
return { from: from, to: to };
|
|
}
|
|
|
|
function getPegawaiRekapPeriode(fromStr, toStr) {
|
|
var r = normalizeDateRange_(fromStr, toStr);
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Pegawai');
|
|
if (!sheet) return { from: r.from, to: r.to, totals: { gaji: 0, kasbon: 0, total: 0, count: 0 }, perPegawai: [] };
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return { from: r.from, to: r.to, totals: { gaji: 0, kasbon: 0, total: 0, count: 0 }, perPegawai: [] };
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 8).getValues();
|
|
var map = {};
|
|
var totals = { gaji: 0, kasbon: 0, total: 0, count: 0 };
|
|
for (var i = 0; i < data.length; i++) {
|
|
var row = data[i];
|
|
var tgl = normalizeSheetDateValue_(row[1]);
|
|
if (!tgl || tgl < r.from || tgl > r.to) continue;
|
|
var nama = String(row[2] || '').trim();
|
|
if (!nama) continue;
|
|
var nilai = Number(row[3]) || 0;
|
|
var kategori = String(row[4] || '').trim();
|
|
if (!map[nama]) map[nama] = { nama: nama, gaji: 0, kasbon: 0, total: 0, count: 0 };
|
|
if (kategori === 'Kasbon') {
|
|
map[nama].kasbon += nilai;
|
|
totals.kasbon += nilai;
|
|
} else {
|
|
map[nama].gaji += nilai;
|
|
totals.gaji += nilai;
|
|
}
|
|
map[nama].total += nilai;
|
|
map[nama].count += 1;
|
|
totals.total += nilai;
|
|
totals.count += 1;
|
|
}
|
|
var perPegawai = Object.keys(map).map(function(k) { return map[k]; })
|
|
.sort(function(a, b) { return (b.total || 0) - (a.total || 0); });
|
|
return { from: r.from, to: r.to, totals: totals, perPegawai: perPegawai };
|
|
}
|
|
|
|
function getOperasionalData(dateStr) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Operasional');
|
|
if (!sheet) return [];
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var colCount = Math.max(7, sheet.getLastColumn());
|
|
var rows = getRowsByDateExact_(sheet, 2, colCount, targetDate, 30000);
|
|
if (!rows.length) return [];
|
|
return rows.map(function(r) {
|
|
var harga = Number(r[3]) || 0;
|
|
var qty = Number(r[4]) || 0;
|
|
var total = Number(r[5]) || 0;
|
|
var catatan = String(r[6] || '');
|
|
if (!total && (Number(r[3]) || 0) > 0 && (typeof r[4] === 'string' || r[4] == null)) {
|
|
total = Number(r[3]) || 0;
|
|
catatan = String(r[4] || '');
|
|
harga = 0;
|
|
qty = 0;
|
|
}
|
|
return { id: r[0], tanggal: r[1], nama: r[2], harga: harga, qty: qty, total: total, catatan: catatan };
|
|
});
|
|
}
|
|
|
|
function getOperasionalRekapPeriode(fromStr, toStr) {
|
|
var r = normalizeDateRange_(fromStr, toStr);
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Operasional');
|
|
if (!sheet) return { from: r.from, to: r.to, total: 0, count: 0, perItem: [] };
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return { from: r.from, to: r.to, total: 0, count: 0, perItem: [] };
|
|
var colCount = Math.max(7, sheet.getLastColumn());
|
|
var data = sheet.getRange(2, 1, lastRow - 1, colCount).getValues();
|
|
var map = {};
|
|
var total = 0;
|
|
var count = 0;
|
|
for (var i = 0; i < data.length; i++) {
|
|
var row = data[i];
|
|
var tgl = normalizeSheetDateValue_(row[1]);
|
|
if (!tgl || tgl < r.from || tgl > r.to) continue;
|
|
var nama = String(row[2] || '').trim();
|
|
if (!nama) continue;
|
|
var harga = Number(row[3]) || 0;
|
|
var qty = Number(row[4]) || 0;
|
|
var nilai = Number(row[5]) || 0;
|
|
if (!nilai && (Number(row[3]) || 0) > 0 && (typeof row[4] === 'string' || row[4] == null)) {
|
|
nilai = Number(row[3]) || 0;
|
|
harga = 0;
|
|
qty = 0;
|
|
}
|
|
if (!map[nama]) map[nama] = { nama: nama, harga: 0, qty: 0, total: 0, count: 0 };
|
|
map[nama].qty += qty;
|
|
map[nama].total += nilai;
|
|
map[nama].count += 1;
|
|
total += nilai;
|
|
count += 1;
|
|
}
|
|
Object.keys(map).forEach(function(k) {
|
|
var it = map[k];
|
|
var q = Number(it.qty) || 0;
|
|
it.harga = q > 0 ? Math.round((Number(it.total) || 0) / q) : 0;
|
|
});
|
|
var perItem = Object.keys(map).map(function(k) { return map[k]; })
|
|
.sort(function(a, b) { return (b.total || 0) - (a.total || 0); });
|
|
return { from: r.from, to: r.to, total: total, count: count, perItem: perItem };
|
|
}
|
|
|
|
function getMenuListExtended() {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Menu');
|
|
if (!sheet) return [];
|
|
var data = sheet.getDataRange().getValues();
|
|
return data.slice(1).map(function(r) {
|
|
return {
|
|
nama: String(r[0]),
|
|
lokasi: String(r[3] || 'Lantai Atas'),
|
|
minStok: Number(r[4]) || 0
|
|
};
|
|
});
|
|
}
|
|
|
|
function getStokData(dateStr, includeFoto) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('StokHarian');
|
|
if (!sheet) return [];
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var rows = getRowsByDateExact_(sheet, 1, 10, targetDate, 40000);
|
|
if (!rows.length) return [];
|
|
var inc = !!includeFoto;
|
|
return rows.map(function(r) {
|
|
return {
|
|
tanggal: r[0] instanceof Date ? Utilities.formatDate(r[0], "GMT+7", "yyyy-MM-dd") : String(r[0]),
|
|
menu: String(r[1]),
|
|
stokAwal: Number(r[2]) || 0,
|
|
restock: Number(r[3]) || 0,
|
|
terpakai: Number(r[4]) || 0,
|
|
sisa: Number(r[5]) || 0,
|
|
lokasi: String(r[6] || ''),
|
|
foto: inc ? String(r[8] || '') : ''
|
|
};
|
|
});
|
|
}
|
|
|
|
function getStokFotoMap(dateStr, lokasi) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('StokHarian');
|
|
if (!sheet) return {};
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var loc = String(lokasi || '').trim();
|
|
if (!loc) return {};
|
|
var blocks = findRowBlocksByDate_(sheet, 1, targetDate, 30000);
|
|
if (!blocks.length) return {};
|
|
var rows = readRowBlocks_(sheet, blocks, 10);
|
|
var out = {};
|
|
for (var i = 0; i < rows.length; i++) {
|
|
var r = rows[i];
|
|
var rowLoc = String(r[6] || '').trim();
|
|
if (rowLoc !== loc) continue;
|
|
var menu = String(r[1] || '').trim();
|
|
if (!menu) continue;
|
|
out[menu] = String(r[8] || '');
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function getBelanjaData(dateStr) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) return [];
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var rows = getRowsByDateExact_(sheet, 6, 10, targetDate, 40000);
|
|
if (!rows.length) return [];
|
|
return rows.map(function(r) {
|
|
return {
|
|
id: String(r[0]),
|
|
nama: String(r[1]),
|
|
harga: Number(r[2]) || 0,
|
|
qty: Number(r[3]) || 0,
|
|
total: Number(r[4]) || 0,
|
|
tanggal: r[5] instanceof Date ? Utilities.formatDate(r[5], "GMT+7", "yyyy-MM-dd") : String(r[5]),
|
|
kategori: String(r[6]),
|
|
catatan: String(r[7])
|
|
};
|
|
});
|
|
}
|
|
|
|
function getLastBelanjaHargaMap(kategori, itemNames) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) return {};
|
|
var kat = String(kategori || '').trim().toUpperCase();
|
|
var names = (itemNames || []).map(function(x) { return String(x || '').trim(); }).filter(function(x) { return x; });
|
|
var wanted = {};
|
|
names.forEach(function(n) { wanted[n.toLowerCase()] = n; });
|
|
var needCount = Object.keys(wanted).length;
|
|
if (!needCount) return {};
|
|
|
|
var out = {};
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return out;
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 10).getValues();
|
|
for (var i = data.length - 1; i >= 0; i--) {
|
|
var r = data[i];
|
|
var name = String(r[1] || '').trim();
|
|
if (!name) continue;
|
|
var key = name.toLowerCase();
|
|
if (!wanted[key]) continue;
|
|
var rowKat = String(r[6] || '').trim().toUpperCase();
|
|
if (kat && rowKat !== kat) continue;
|
|
if (out[wanted[key]] != null) continue;
|
|
out[wanted[key]] = Number(r[2]) || 0;
|
|
needCount--;
|
|
if (needCount <= 0) break;
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function saveBulkStok(rows, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('StokHarian');
|
|
var now = new Date();
|
|
var today = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
|
|
var targetDate = today;
|
|
if (arguments && arguments.length >= 3 && arguments[2]) targetDate = String(arguments[2]);
|
|
if (!rows || !rows.length) return getStokData(targetDate, false);
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var tgl = data[i][0] instanceof Date ? Utilities.formatDate(data[i][0], "GMT+7", "yyyy-MM-dd") : String(data[i][0]);
|
|
var loc = String(data[i][6] || '');
|
|
// Hapus data lama untuk lokasi yang sama di hari yang sama agar tidak duplikat saat simpan ulang per tab
|
|
if (tgl === targetDate && loc === rows[0].lokasi) {
|
|
sheet.deleteRow(i + 1);
|
|
}
|
|
}
|
|
|
|
rows.forEach(function(r) {
|
|
if (Number(r.stokAwal) > 0 || Number(r.restock) > 0 || Number(r.terpakai) > 0 || r.foto) {
|
|
sheet.appendRow([
|
|
targetDate,
|
|
r.menu,
|
|
Number(r.stokAwal) || 0,
|
|
Number(r.restock) || 0,
|
|
Number(r.terpakai) || 0,
|
|
Number(r.sisa) || 0,
|
|
r.lokasi || '',
|
|
userName || 'Unknown',
|
|
r.foto || '',
|
|
now
|
|
]);
|
|
}
|
|
});
|
|
return getStokData(targetDate, false);
|
|
}
|
|
|
|
function saveBulkBelanja(rows, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
var now = new Date();
|
|
var today = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
|
|
var targetDate = today;
|
|
if (arguments && arguments.length >= 3 && arguments[2]) targetDate = String(arguments[2]);
|
|
if (!rows || !rows.length) return getBelanjaData(targetDate);
|
|
|
|
var kategori = String((rows[0] && rows[0].kategori) || '').trim();
|
|
if (!kategori) kategori = 'Bahan Baku';
|
|
|
|
var byNamaKey = {};
|
|
(rows || []).forEach(function(r) {
|
|
var nama = String((r && r.nama) || '').trim();
|
|
if (!nama) return;
|
|
var total = Number((r && r.total)) || 0;
|
|
if (total <= 0) return;
|
|
var key = nama.toLowerCase();
|
|
byNamaKey[key] = {
|
|
id: String((r && r.id) || ''),
|
|
nama: nama,
|
|
harga: Number((r && r.harga)) || 0,
|
|
qty: Number((r && r.qty)) || 0,
|
|
total: total,
|
|
kategori: String((r && r.kategori) || kategori),
|
|
catatan: String((r && r.catatan) || '')
|
|
};
|
|
});
|
|
|
|
var clean = Object.keys(byNamaKey).map(function(k) { return byNamaKey[k]; });
|
|
if (!clean.length) return getBelanjaData(targetDate);
|
|
|
|
var nameSet = {};
|
|
clean.forEach(function(r) { nameSet[String(r.nama).toLowerCase()] = true; });
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var tgl = data[i][5] instanceof Date ? Utilities.formatDate(data[i][5], "GMT+7", "yyyy-MM-dd") : String(data[i][5]);
|
|
var cat = String(data[i][6] || '');
|
|
if (tgl !== targetDate || cat !== kategori) continue;
|
|
var nm = String(data[i][1] || '').trim();
|
|
if (nm && nameSet[nm.toLowerCase()]) sheet.deleteRow(i + 1);
|
|
}
|
|
|
|
clean.forEach(function(r) {
|
|
var id = r.id || 'B-' + Date.now() + Math.floor(Math.random() * 1000);
|
|
sheet.appendRow([
|
|
id,
|
|
r.nama,
|
|
Number(r.harga) || 0,
|
|
Number(r.qty) || 0,
|
|
Number(r.total) || 0,
|
|
targetDate,
|
|
r.kategori || kategori,
|
|
r.catatan || '',
|
|
userName || 'Unknown',
|
|
now
|
|
]);
|
|
});
|
|
return getBelanjaData(targetDate);
|
|
}
|
|
|
|
function deleteBelanjaItem(dateStr, kategori, nama) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var cat = String(kategori || '').trim();
|
|
var nm = String(nama || '').trim();
|
|
if (!cat || !nm) return true;
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var tgl = data[i][5] instanceof Date ? Utilities.formatDate(data[i][5], "GMT+7", "yyyy-MM-dd") : String(data[i][5]);
|
|
var c = String(data[i][6] || '').trim();
|
|
var n = String(data[i][1] || '').trim();
|
|
if (tgl === targetDate && c === cat && n === nm) sheet.deleteRow(i + 1);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function deleteBelanjaById(id) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
var targetId = String(id || '').trim();
|
|
if (!targetId) return true;
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var rid = String(data[i][0] || '').trim();
|
|
if (rid === targetId) sheet.deleteRow(i + 1);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function updateBelanjaItem(dateStr, kategori, nama, harga, qty, total, catatan, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var cat = String(kategori || '').trim();
|
|
var nm = String(nama || '').trim();
|
|
if (!cat || !nm) return true;
|
|
var now = new Date();
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var tgl = data[i][5] instanceof Date ? Utilities.formatDate(data[i][5], "GMT+7", "yyyy-MM-dd") : String(data[i][5]);
|
|
var c = String(data[i][6] || '').trim();
|
|
var n = String(data[i][1] || '').trim();
|
|
if (tgl !== targetDate || c !== cat || n !== nm) continue;
|
|
sheet.getRange(i + 1, 3).setValue(Number(harga) || 0);
|
|
sheet.getRange(i + 1, 4).setValue(Number(qty) || 0);
|
|
sheet.getRange(i + 1, 5).setValue(Number(total) || 0);
|
|
sheet.getRange(i + 1, 8).setValue(String(catatan || ''));
|
|
sheet.getRange(i + 1, 9).setValue(userName || 'Unknown');
|
|
sheet.getRange(i + 1, 10).setValue(now);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function updateBelanjaById(id, fields, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
var targetId = String(id || '').trim();
|
|
if (!targetId) return false;
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return false;
|
|
var finderRange = sheet.getRange(2, 1, lastRow - 1, 1);
|
|
var cell = finderRange.createTextFinder(targetId).matchEntireCell(true).findNext();
|
|
if (!cell) return false;
|
|
var row = cell.getRow();
|
|
var current = sheet.getRange(row, 1, 1, 10).getValues()[0];
|
|
var now = new Date();
|
|
var f = fields || {};
|
|
var newHarga = (f.harga != null) ? (Number(f.harga) || 0) : (Number(current[2]) || 0);
|
|
var newQty = (f.qty != null) ? (Number(f.qty) || 0) : (Number(current[3]) || 0);
|
|
var newTotal = (f.total != null) ? (Number(f.total) || 0) : (Number(current[4]) || 0);
|
|
var newCatatan = (f.catatan != null) ? String(f.catatan || '') : String(current[7] || '');
|
|
sheet.getRange(row, 3).setValue(newHarga);
|
|
sheet.getRange(row, 4).setValue(newQty);
|
|
sheet.getRange(row, 5).setValue(newTotal);
|
|
sheet.getRange(row, 8).setValue(newCatatan);
|
|
sheet.getRange(row, 9).setValue(userName || 'Unknown');
|
|
sheet.getRange(row, 10).setValue(now);
|
|
return true;
|
|
}
|
|
|
|
function bulkUpdateBelanjaById(updates, userName) {
|
|
if (!updates || !updates.length) return { ok: true, updated: 0 };
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
var map = {};
|
|
for (var i = 0; i < updates.length; i++) {
|
|
var u = updates[i] || {};
|
|
var id = String(u.id || '').trim();
|
|
if (!id) continue;
|
|
map[id] = {
|
|
harga: (u.harga != null) ? (Number(u.harga) || 0) : null,
|
|
qty: (u.qty != null) ? (Number(u.qty) || 0) : null,
|
|
total: (u.total != null) ? (Number(u.total) || 0) : null,
|
|
catatan: (u.catatan != null) ? String(u.catatan || '') : null
|
|
};
|
|
}
|
|
var ids = Object.keys(map);
|
|
if (!ids.length) return { ok: true, updated: 0 };
|
|
var now = new Date();
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return { ok: true, updated: 0 };
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 10).getValues();
|
|
var updated = 0;
|
|
for (var r = 0; r < data.length; r++) {
|
|
var rowId = String(data[r][0] || '').trim();
|
|
if (!rowId || !map[rowId]) continue;
|
|
var f = map[rowId];
|
|
var rowNum = r + 2;
|
|
if (f.harga != null) sheet.getRange(rowNum, 3).setValue(f.harga);
|
|
if (f.qty != null) sheet.getRange(rowNum, 4).setValue(f.qty);
|
|
if (f.total != null) sheet.getRange(rowNum, 5).setValue(f.total);
|
|
if (f.catatan != null) sheet.getRange(rowNum, 8).setValue(f.catatan);
|
|
sheet.getRange(rowNum, 9).setValue(userName || 'Unknown');
|
|
sheet.getRange(rowNum, 10).setValue(now);
|
|
updated += 1;
|
|
}
|
|
return { ok: true, updated: updated };
|
|
}
|
|
|
|
function deleteStokItem(dateStr, lokasi, menu) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('StokHarian');
|
|
if (!sheet) throw new Error('Sheet StokHarian tidak ditemukan.');
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var loc = String(lokasi || '').trim();
|
|
var mn = String(menu || '').trim();
|
|
if (!loc || !mn) return true;
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var tgl = data[i][0] instanceof Date ? Utilities.formatDate(data[i][0], "GMT+7", "yyyy-MM-dd") : String(data[i][0]);
|
|
var m = String(data[i][1] || '').trim();
|
|
var l = String(data[i][6] || '').trim();
|
|
if (tgl === targetDate && l === loc && m === mn) sheet.deleteRow(i + 1);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function updateStokItem(dateStr, lokasi, menu, stokAwal, restock, terpakai, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('StokHarian');
|
|
if (!sheet) throw new Error('Sheet StokHarian tidak ditemukan.');
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
var loc = String(lokasi || '').trim();
|
|
var mn = String(menu || '').trim();
|
|
if (!loc || !mn) return true;
|
|
var now = new Date();
|
|
var a = Number(stokAwal) || 0;
|
|
var r = Number(restock) || 0;
|
|
var p = Number(terpakai) || 0;
|
|
var sisa = Math.max(0, (a + r) - p);
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var tgl = data[i][0] instanceof Date ? Utilities.formatDate(data[i][0], "GMT+7", "yyyy-MM-dd") : String(data[i][0]);
|
|
var m = String(data[i][1] || '').trim();
|
|
var l = String(data[i][6] || '').trim();
|
|
if (tgl !== targetDate || l !== loc || m !== mn) continue;
|
|
sheet.getRange(i + 1, 3).setValue(a);
|
|
sheet.getRange(i + 1, 4).setValue(r);
|
|
sheet.getRange(i + 1, 5).setValue(p);
|
|
sheet.getRange(i + 1, 6).setValue(sisa);
|
|
sheet.getRange(i + 1, 8).setValue(userName || 'Unknown');
|
|
sheet.getRange(i + 1, 10).setValue(now);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function bulkUpdateStokItems(updates, userName) {
|
|
if (!updates || !updates.length) return { ok: true, updated: 0 };
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('StokHarian');
|
|
if (!sheet) throw new Error('Sheet StokHarian tidak ditemukan.');
|
|
var map = {};
|
|
for (var i = 0; i < updates.length; i++) {
|
|
var u = updates[i] || {};
|
|
var t = normalizeDateStr_(u.tanggal);
|
|
var loc = String(u.lokasi || '').trim();
|
|
var menu = String(u.menu || '').trim();
|
|
if (!t || !loc || !menu) continue;
|
|
var a = Number(u.stokAwal) || 0;
|
|
var r = Number(u.restock) || 0;
|
|
var p = Number(u.terpakai) || 0;
|
|
map[t + '|' + loc + '|' + menu] = { tanggal: t, lokasi: loc, menu: menu, stokAwal: a, restock: r, terpakai: p };
|
|
}
|
|
var keys = Object.keys(map);
|
|
if (!keys.length) return { ok: true, updated: 0 };
|
|
var now = new Date();
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return { ok: true, updated: 0 };
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 10).getValues();
|
|
var updated = 0;
|
|
for (var r0 = 0; r0 < data.length; r0++) {
|
|
var row = data[r0];
|
|
var tgl = normalizeSheetDateValue_(row[0]);
|
|
var m = String(row[1] || '').trim();
|
|
var l = String(row[6] || '').trim();
|
|
if (!tgl || !m || !l) continue;
|
|
var key = tgl + '|' + l + '|' + m;
|
|
var u2 = map[key];
|
|
if (!u2) continue;
|
|
var rowNum = r0 + 2;
|
|
var sisa = Math.max(0, (u2.stokAwal + u2.restock) - u2.terpakai);
|
|
sheet.getRange(rowNum, 3).setValue(u2.stokAwal);
|
|
sheet.getRange(rowNum, 4).setValue(u2.restock);
|
|
sheet.getRange(rowNum, 5).setValue(u2.terpakai);
|
|
sheet.getRange(rowNum, 6).setValue(sisa);
|
|
sheet.getRange(rowNum, 8).setValue(userName || 'Unknown');
|
|
sheet.getRange(rowNum, 10).setValue(now);
|
|
updated += 1;
|
|
}
|
|
return { ok: true, updated: updated };
|
|
}
|
|
|
|
function getBelanjaDataRange(fromStr, toStr) {
|
|
var r = normalizeDateRange_(fromStr, toStr);
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) return [];
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return [];
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 10).getValues();
|
|
var out = [];
|
|
for (var i = 0; i < data.length; i++) {
|
|
var row = data[i];
|
|
var tgl = normalizeSheetDateValue_(row[5]);
|
|
if (!tgl || tgl < r.from || tgl > r.to) continue;
|
|
out.push({
|
|
id: String(row[0] || ''),
|
|
nama: String(row[1] || ''),
|
|
harga: Number(row[2]) || 0,
|
|
qty: Number(row[3]) || 0,
|
|
total: Number(row[4]) || 0,
|
|
tanggal: tgl,
|
|
kategori: String(row[6] || ''),
|
|
catatan: String(row[7] || '')
|
|
});
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function getStokDataRange(fromStr, toStr) {
|
|
var r = normalizeDateRange_(fromStr, toStr);
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('StokHarian');
|
|
if (!sheet) return [];
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return [];
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 10).getValues();
|
|
var out = [];
|
|
for (var i = 0; i < data.length; i++) {
|
|
var row = data[i];
|
|
var tgl = normalizeSheetDateValue_(row[0]);
|
|
if (!tgl || tgl < r.from || tgl > r.to) continue;
|
|
out.push({
|
|
tanggal: tgl,
|
|
menu: String(row[1] || ''),
|
|
stokAwal: Number(row[2]) || 0,
|
|
restock: Number(row[3]) || 0,
|
|
terpakai: Number(row[4]) || 0,
|
|
sisa: Number(row[5]) || 0,
|
|
lokasi: String(row[6] || '')
|
|
});
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function getOperasionalDataRange(fromStr, toStr) {
|
|
var r = normalizeDateRange_(fromStr, toStr);
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Operasional');
|
|
if (!sheet) return [];
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return [];
|
|
var colCount = Math.max(7, sheet.getLastColumn());
|
|
var data = sheet.getRange(2, 1, lastRow - 1, colCount).getValues();
|
|
var out = [];
|
|
for (var i = 0; i < data.length; i++) {
|
|
var row = data[i];
|
|
var tgl = normalizeSheetDateValue_(row[1]);
|
|
if (!tgl || tgl < r.from || tgl > r.to) continue;
|
|
var harga = Number(row[3]) || 0;
|
|
var qty = Number(row[4]) || 0;
|
|
var total = Number(row[5]) || 0;
|
|
var catatan = String(row[6] || '');
|
|
if (!total && (Number(row[3]) || 0) > 0 && (typeof row[4] === 'string' || row[4] == null)) {
|
|
total = Number(row[3]) || 0;
|
|
catatan = String(row[4] || '');
|
|
harga = 0;
|
|
qty = 0;
|
|
}
|
|
out.push({ id: String(row[0] || ''), tanggal: tgl, nama: String(row[2] || ''), harga: harga, qty: qty, total: total, catatan: catatan });
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function getPegawaiDataRange(fromStr, toStr) {
|
|
var r = normalizeDateRange_(fromStr, toStr);
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Pegawai');
|
|
if (!sheet) return [];
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return [];
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 8).getValues();
|
|
var out = [];
|
|
for (var i = 0; i < data.length; i++) {
|
|
var row = data[i];
|
|
var tgl = normalizeSheetDateValue_(row[1]);
|
|
if (!tgl || tgl < r.from || tgl > r.to) continue;
|
|
out.push({ id: String(row[0] || ''), tanggal: tgl, nama: String(row[2] || ''), nilai: Number(row[3]) || 0, kategori: String(row[4] || ''), catatan: String(row[5] || '') });
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function getRingkasanPeriode(fromStr, toStr) {
|
|
var r = normalizeDateRange_(fromStr, toStr);
|
|
return {
|
|
from: r.from,
|
|
to: r.to,
|
|
menu: getMenuListExtended(),
|
|
pegawaiList: getPegawaiList(),
|
|
stok: getStokDataRange(r.from, r.to),
|
|
belanja: getBelanjaDataRange(r.from, r.to),
|
|
operasional: getOperasionalDataRange(r.from, r.to),
|
|
pegawai: getPegawaiDataRange(r.from, r.to),
|
|
ok: true
|
|
};
|
|
}
|
|
|
|
function saveBulkPegawai(rows, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Pegawai');
|
|
var now = new Date();
|
|
var today = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
|
|
var targetDate = today;
|
|
if (arguments && arguments.length >= 3 && arguments[2]) targetDate = String(arguments[2]);
|
|
if (!rows || !rows.length) return getPegawaiData(targetDate);
|
|
|
|
var lastRow = sheet.getLastRow();
|
|
var idMap = {};
|
|
if (lastRow > 1) {
|
|
var ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
|
|
for (var i = 0; i < ids.length; i++) {
|
|
var id = String(ids[i][0] || '').trim();
|
|
if (id) idMap[id] = i + 2;
|
|
}
|
|
}
|
|
|
|
rows.forEach(function(r) {
|
|
var nama = String((r && r.nama) || '').trim();
|
|
var nilai = Number((r && r.nilai)) || 0;
|
|
if (!nama || nilai <= 0) return;
|
|
var kategori = String((r && r.kategori) || 'Gaji');
|
|
var catatan = String((r && r.catatan) || '');
|
|
var id = String((r && r.id) || '').trim();
|
|
if (!id) id = 'P-' + Date.now() + '-' + Math.floor(Math.random() * 1000);
|
|
var row = [id, targetDate, nama, nilai, kategori, catatan, userName || 'Unknown', now];
|
|
var existingRow = idMap[id];
|
|
if (existingRow) sheet.getRange(existingRow, 1, 1, row.length).setValues([row]);
|
|
else sheet.appendRow(row);
|
|
});
|
|
return getPegawaiData(targetDate);
|
|
}
|
|
|
|
function deletePegawaiById(id, dateStr) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Pegawai');
|
|
if (!sheet) return getPegawaiData(dateStr);
|
|
var targetId = String(id || '').trim();
|
|
var targetDate = normalizeDateStr_(dateStr);
|
|
if (!targetId) return getPegawaiData(targetDate);
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return getPegawaiData(targetDate);
|
|
var ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
|
|
for (var i = ids.length - 1; i >= 0; i--) {
|
|
if (String(ids[i][0] || '').trim() === targetId) sheet.deleteRow(i + 2);
|
|
}
|
|
return getPegawaiData(targetDate);
|
|
}
|
|
|
|
function saveBulkOperasional(rows, userName) {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Operasional');
|
|
var now = new Date();
|
|
var today = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
|
|
var targetDate = today;
|
|
if (arguments && arguments.length >= 3 && arguments[2]) targetDate = String(arguments[2]);
|
|
if (!rows || !rows.length) return getOperasionalData(targetDate);
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = data.length - 1; i >= 1; i--) {
|
|
var tgl = data[i][1] instanceof Date ? Utilities.formatDate(data[i][1], "GMT+7", "yyyy-MM-dd") : String(data[i][1]);
|
|
if (tgl === targetDate) sheet.deleteRow(i + 1);
|
|
}
|
|
rows.forEach(function(r) {
|
|
if (!r || !r.nama) return;
|
|
var qty = Number(r.qty) || 0;
|
|
if (qty <= 0) qty = 1;
|
|
var harga = Number(r.harga) || 0;
|
|
var total = Number(r.total) || 0;
|
|
if (total <= 0 && harga > 0) total = harga * qty;
|
|
if (harga <= 0 && total > 0) harga = Math.round(total / qty);
|
|
if (total <= 0) return;
|
|
sheet.appendRow([
|
|
'O-' + Date.now() + Math.random(),
|
|
targetDate,
|
|
r.nama,
|
|
harga,
|
|
qty,
|
|
total,
|
|
r.catatan || '',
|
|
userName || 'Unknown',
|
|
now
|
|
]);
|
|
});
|
|
return getOperasionalData(targetDate);
|
|
}
|
|
|
|
function getMenuList() {
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Menu');
|
|
if (!sheet) return [];
|
|
var data = sheet.getDataRange().getValues();
|
|
return data.slice(1).map(function(r) { return String(r[0]); });
|
|
}
|
|
|
|
function ensureSheetWithHeader_(ss, name, headers) {
|
|
var sheet = ss.getSheetByName(name);
|
|
if (!sheet) sheet = ss.insertSheet(name);
|
|
if (sheet.getLastRow() < 1) sheet.appendRow(headers);
|
|
return sheet;
|
|
}
|
|
|
|
function buildIdRowMap_(sheet) {
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return {};
|
|
var ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
|
|
var map = {};
|
|
for (var i = 0; i < ids.length; i++) {
|
|
var id = String(ids[i][0] || '').trim();
|
|
if (id) map[id] = i + 2;
|
|
}
|
|
return map;
|
|
}
|
|
|
|
function applyRowUpdatesBatched_(sheet, updates) {
|
|
if (!sheet || !updates || !updates.length) return;
|
|
updates.sort(function(a, b) { return a.row - b.row; });
|
|
var start = updates[0].row;
|
|
var prev = updates[0].row;
|
|
var buf = [updates[0].values];
|
|
for (var i = 1; i < updates.length; i++) {
|
|
var u = updates[i];
|
|
if (u.row === prev + 1) {
|
|
buf.push(u.values);
|
|
prev = u.row;
|
|
continue;
|
|
}
|
|
sheet.getRange(start, 1, buf.length, buf[0].length).setValues(buf);
|
|
start = u.row;
|
|
prev = u.row;
|
|
buf = [u.values];
|
|
}
|
|
sheet.getRange(start, 1, buf.length, buf[0].length).setValues(buf);
|
|
}
|
|
|
|
function syncBelanjaToRekap(dateStr, kategori, userName) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var now = new Date();
|
|
var date = normalizeDateStr_(dateStr);
|
|
var cat = String(kategori || '').trim().toUpperCase();
|
|
if (cat !== 'MODAL' && cat !== 'BAWAH') throw new Error('Kategori sync hanya MODAL/BAWAH.');
|
|
var props = PropertiesService.getScriptProperties();
|
|
var rekapId = String(props.getProperty('rekap_transaksi_spreadsheet_id') || '').trim();
|
|
if (!rekapId) return { ok: false, inserted: 0, updated: 0, skipped: 0, error: 'Script Properties rekap_transaksi_spreadsheet_id belum diisi.' };
|
|
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet || sheet.getLastRow() <= 1) return { ok: true, inserted: 0, updated: 0, skipped: 0 };
|
|
|
|
var lastRow = sheet.getLastRow();
|
|
var colCount = Math.max(10, sheet.getLastColumn());
|
|
var scanLimit = 20000;
|
|
var startRow = Math.max(2, lastRow - scanLimit + 1);
|
|
var data = sheet.getRange(startRow, 1, lastRow - startRow + 1, colCount).getValues();
|
|
|
|
var items = [];
|
|
for (var i = 0; i < data.length; i++) {
|
|
var r = data[i];
|
|
var id = String(r[0] || '').trim();
|
|
if (!id) continue;
|
|
var tgl = r[5] instanceof Date ? Utilities.formatDate(r[5], 'GMT+7', 'yyyy-MM-dd') : String(r[5] || '').trim();
|
|
if (tgl !== date) continue;
|
|
var k = String(r[6] || '').trim().toUpperCase();
|
|
if (k !== cat) continue;
|
|
items.push({
|
|
id: 'SB-' + id,
|
|
nama: String(r[1] || ''),
|
|
harga: Number(r[2]) || 0,
|
|
qty: Number(r[3]) || 0,
|
|
total: Number(r[4]) || 0,
|
|
tgl: tgl,
|
|
kategori: k,
|
|
catatan: String(r[7] || '')
|
|
});
|
|
}
|
|
|
|
var rekap = SpreadsheetApp.openById(rekapId);
|
|
var sheetRekap = ensureSheetWithHeader_(rekap, 'Belanja', ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'Timestamp']);
|
|
var map = buildIdRowMap_(sheetRekap);
|
|
|
|
var inserted = 0;
|
|
var updated = 0;
|
|
var skipped = 0;
|
|
for (var j = 0; j < items.length; j++) {
|
|
var it = items[j];
|
|
if (!it || !it.id) { skipped++; continue; }
|
|
var row = [it.id, it.nama || '', Number(it.harga) || 0, Number(it.qty) || 0, Number(it.total) || 0, it.tgl, it.kategori || '', it.catatan || '', now];
|
|
var existing = map[it.id];
|
|
if (existing) {
|
|
sheetRekap.getRange(existing, 1, 1, row.length).setValues([row]);
|
|
updated++;
|
|
} else {
|
|
sheetRekap.appendRow(row);
|
|
inserted++;
|
|
}
|
|
}
|
|
|
|
return { ok: true, inserted: inserted, updated: updated, skipped: skipped };
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function syncBelanjaToRekapAll(kategori, userName) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var now = new Date();
|
|
var cat = String(kategori || '').trim().toUpperCase();
|
|
if (cat !== 'MODAL' && cat !== 'BAWAH') throw new Error('Kategori sync hanya MODAL/BAWAH.');
|
|
var props = PropertiesService.getScriptProperties();
|
|
var rekapId = String(props.getProperty('rekap_transaksi_spreadsheet_id') || '').trim();
|
|
if (!rekapId) return { ok: false, inserted: 0, updated: 0, skipped: 0, total: 0, dateCount: 0, error: 'Script Properties rekap_transaksi_spreadsheet_id belum diisi.' };
|
|
|
|
var ss = getSS_();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet || sheet.getLastRow() <= 1) return { ok: true, inserted: 0, updated: 0, skipped: 0, total: 0, dateCount: 0 };
|
|
|
|
var lastRow = sheet.getLastRow();
|
|
var scanCols = Math.max(8, Math.min(10, sheet.getLastColumn()));
|
|
var data = sheet.getRange(2, 1, lastRow - 1, scanCols).getValues();
|
|
|
|
var items = [];
|
|
var dateSet = {};
|
|
for (var i = 0; i < data.length; i++) {
|
|
var r = data[i];
|
|
var id = String(r[0] || '').trim();
|
|
if (!id) continue;
|
|
var k = String(r[6] || '').trim().toUpperCase();
|
|
if (k !== cat) continue;
|
|
var total = Number(r[4]) || 0;
|
|
if (total <= 0) continue;
|
|
var tgl = r[5] instanceof Date ? Utilities.formatDate(r[5], 'GMT+7', 'yyyy-MM-dd') : String(r[5] || '').trim();
|
|
if (!tgl) continue;
|
|
dateSet[tgl] = true;
|
|
items.push({
|
|
id: 'SB-' + id,
|
|
nama: String(r[1] || ''),
|
|
harga: Number(r[2]) || 0,
|
|
qty: Number(r[3]) || 0,
|
|
total: total,
|
|
tgl: tgl,
|
|
kategori: k,
|
|
catatan: String(r[7] || '')
|
|
});
|
|
}
|
|
|
|
if (!items.length) return { ok: true, inserted: 0, updated: 0, skipped: 0, total: 0, dateCount: Object.keys(dateSet).length };
|
|
|
|
var rekap = SpreadsheetApp.openById(rekapId);
|
|
var sheetRekap = ensureSheetWithHeader_(rekap, 'Belanja', ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'Timestamp']);
|
|
var map = buildIdRowMap_(sheetRekap);
|
|
|
|
var updates = [];
|
|
var appends = [];
|
|
var inserted = 0;
|
|
var updated = 0;
|
|
var skipped = 0;
|
|
|
|
for (var j = 0; j < items.length; j++) {
|
|
var it = items[j];
|
|
if (!it || !it.id) { skipped++; continue; }
|
|
var row = [it.id, it.nama || '', Number(it.harga) || 0, Number(it.qty) || 0, Number(it.total) || 0, it.tgl, it.kategori || '', it.catatan || '', now];
|
|
var existing = map[it.id];
|
|
if (existing) {
|
|
updates.push({ row: existing, values: row });
|
|
updated++;
|
|
} else {
|
|
appends.push(row);
|
|
inserted++;
|
|
}
|
|
}
|
|
|
|
applyRowUpdatesBatched_(sheetRekap, updates);
|
|
if (appends.length) {
|
|
var startRow = sheetRekap.getLastRow() + 1;
|
|
sheetRekap.getRange(startRow, 1, appends.length, appends[0].length).setValues(appends);
|
|
}
|
|
|
|
return { ok: true, inserted: inserted, updated: updated, skipped: skipped, total: items.length, dateCount: Object.keys(dateSet).length };
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|