635 lines
23 KiB
JavaScript
635 lines
23 KiB
JavaScript
function doGet() {
|
|
return HtmlService.createTemplateFromFile('Index')
|
|
.evaluate()
|
|
.setTitle('Dashboard Laporan Fuku Shabu & Grill')
|
|
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
|
|
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
|
|
}
|
|
|
|
function authDigestBase64_(s) {
|
|
var bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, String(s || ''), Utilities.Charset.UTF_8);
|
|
return Utilities.base64Encode(bytes);
|
|
}
|
|
|
|
function rekapLogin(action, username, password) {
|
|
var a = String(action || '').trim().toLowerCase();
|
|
var u = String(username || '').trim().toLowerCase();
|
|
var p = String(password || '');
|
|
if (!a || !u || !p) return { ok: false };
|
|
|
|
var db = {
|
|
admin: {
|
|
salt: 'rKkQ4v3Y8nP1zT6a',
|
|
hashes: [
|
|
'W54PYq8f2cj1Q/wOsZWc6gV7Aj47ZnpT/dxXmzL7CV8=',
|
|
'zrBzOp9x7UZlrE78NqoRFlichiZQrBDvY3HBVf1y1zY='
|
|
]
|
|
},
|
|
ridho: {
|
|
salt: 'H2m9Qp7sV4c1X0bN',
|
|
hashes: ['UNnM/O3rcanZCQEjL/tfZldwUBuSPRK4X11funwwcKw=']
|
|
},
|
|
mamah: {
|
|
salt: 'fZ8tK3pL0n2Qm7vR',
|
|
hashes: ['OcjVm5Bhz19P9kSqsUu+lUWl7uEu9DXtPLt2fnwdxyk=']
|
|
}
|
|
};
|
|
|
|
var allow = {
|
|
dashboard: { admin: ['W54PYq8f2cj1Q/wOsZWc6gV7Aj47ZnpT/dxXmzL7CV8='] },
|
|
kasir: { admin: ['zrBzOp9x7UZlrE78NqoRFlichiZQrBDvY3HBVf1y1zY='], ridho: ['UNnM/O3rcanZCQEjL/tfZldwUBuSPRK4X11funwwcKw='] },
|
|
stok: { admin: ['zrBzOp9x7UZlrE78NqoRFlichiZQrBDvY3HBVf1y1zY='], ridho: ['UNnM/O3rcanZCQEjL/tfZldwUBuSPRK4X11funwwcKw='], mamah: ['OcjVm5Bhz19P9kSqsUu+lUWl7uEu9DXtPLt2fnwdxyk='] }
|
|
};
|
|
|
|
if (!allow[a] || !allow[a][u]) return { ok: false };
|
|
if (!db[u] || !db[u].salt) return { ok: false };
|
|
|
|
var expectedHashes = allow[a][u];
|
|
|
|
var actualHash = authDigestBase64_(db[u].salt + ':' + p);
|
|
if (expectedHashes.indexOf(actualHash) === -1) return { ok: false };
|
|
|
|
return { ok: true, action: a, username: u };
|
|
}
|
|
|
|
function getDashboardData() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetTrans = ss.getSheetByName('Transaksi');
|
|
var sheetBelanja = ss.getSheetByName('Belanja');
|
|
|
|
var transData = sheetTrans ? sheetTrans.getDataRange().getValues() : [];
|
|
var belanjaData = sheetBelanja ? sheetBelanja.getDataRange().getValues() : [];
|
|
|
|
var totalSales = 0;
|
|
var totalTransactions = 0;
|
|
var methods = {};
|
|
var dailyTrans = {};
|
|
var itemCounts = {};
|
|
|
|
// Proses Transaksi
|
|
if (transData.length > 1) {
|
|
transData.slice(1).forEach(function(row) {
|
|
var status = String(row[2] || '');
|
|
if (status !== 'Selesai') return;
|
|
|
|
var total = Number(row[17] || 0);
|
|
var method = String(row[21] || 'Tunai');
|
|
var dateObj = row[25] || row[5];
|
|
var dateStr = dateObj instanceof Date ? Utilities.formatDate(dateObj, "GMT+7", "yyyy-MM-dd") : String(dateObj).split(' ')[0];
|
|
|
|
totalSales += total;
|
|
totalTransactions++;
|
|
methods[method] = (methods[method] || 0) + total;
|
|
dailyTrans[dateStr] = (dailyTrans[dateStr] || 0) + total;
|
|
|
|
try {
|
|
var items = JSON.parse(row[7] || '[]');
|
|
items.forEach(function(it) {
|
|
itemCounts[it.nama] = (itemCounts[it.nama] || 0) + (Number(it.qty) || 0);
|
|
});
|
|
} catch (e) {}
|
|
});
|
|
}
|
|
|
|
// Proses Belanja
|
|
var totalBelanja = 0;
|
|
var dailyBelanja = {};
|
|
var kategoriBelanja = {};
|
|
var belanjaList = [];
|
|
|
|
if (belanjaData.length > 1) {
|
|
belanjaData.slice(1).forEach(function(row) {
|
|
var nama = String(row[1] || '');
|
|
var harga = Number(row[2] || 0);
|
|
var qty = Number(row[3] || 0);
|
|
var total = Number(row[4] || 0);
|
|
var dateObj = row[5];
|
|
var kat = String(row[6] || 'Lainnya');
|
|
|
|
var dateStr = dateObj instanceof Date ? Utilities.formatDate(dateObj, "GMT+7", "yyyy-MM-dd") : String(dateObj).split(' ')[0];
|
|
|
|
totalBelanja += total;
|
|
dailyBelanja[dateStr] = (dailyBelanja[dateStr] || 0) + total;
|
|
kategoriBelanja[kat] = (kategoriBelanja[kat] || 0) + total;
|
|
|
|
belanjaList.push({ nama: nama, total: total, kat: kat, tgl: dateStr });
|
|
});
|
|
}
|
|
|
|
// Sort data
|
|
var sortedItems = Object.keys(itemCounts).map(function(k) { return [k, itemCounts[k]]; }).sort(function(a, b) { return b[1] - a[1]; }).slice(0, 10);
|
|
var sortedDates = Array.from(new Set(Object.keys(dailyTrans).concat(Object.keys(dailyBelanja)))).sort();
|
|
|
|
var dailyStats = sortedDates.map(function(d) {
|
|
return { date: d, sales: dailyTrans[d] || 0, expense: dailyBelanja[d] || 0 };
|
|
});
|
|
|
|
return {
|
|
summary: {
|
|
totalSales: totalSales,
|
|
totalBelanja: totalBelanja,
|
|
netProfit: totalSales - totalBelanja,
|
|
totalTransactions: totalTransactions,
|
|
avgTransaction: totalTransactions > 0 ? (totalSales / totalTransactions) : 0
|
|
},
|
|
methods: methods,
|
|
daily: dailyStats,
|
|
topItems: sortedItems,
|
|
kategoriBelanja: kategoriBelanja,
|
|
topBelanja: belanjaList.sort(function(a,b){ return b.total - a.total; }).slice(0, 10)
|
|
};
|
|
}
|
|
|
|
function ensureHeaderRow_(sheet, headers) {
|
|
if (!sheet) return;
|
|
var h = headers || [];
|
|
if (!h.length) return;
|
|
if (sheet.getLastRow() < 1) sheet.appendRow(h);
|
|
var first = sheet.getRange(1, 1, 1, h.length).getValues()[0];
|
|
var ok = true;
|
|
for (var i = 0; i < h.length; i++) {
|
|
if (String(first[i] || '').trim() !== String(h[i] || '').trim()) { ok = false; break; }
|
|
}
|
|
if (!ok) {
|
|
sheet.getRange(1, 1, 1, h.length).setValues([h]);
|
|
}
|
|
sheet.getRange(1, 1, 1, h.length).setFontWeight('bold').setBackground('#f3f3f3');
|
|
}
|
|
|
|
function setupCalendarNotesSheet_() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('KalenderNotes');
|
|
if (!sheet) sheet = ss.insertSheet('KalenderNotes');
|
|
ensureHeaderRow_(sheet, ['ID', 'Tanggal', 'Judul', 'Catatan', 'User', 'Timestamp']);
|
|
sheet.getRange('B:B').setNumberFormat('@');
|
|
sheet.getRange('F:F').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
return sheet;
|
|
}
|
|
|
|
function getCalendarNotesByDate(dateStr) {
|
|
var d = normalizeDateStr_(dateStr);
|
|
var sheet = setupCalendarNotesSheet_();
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return [];
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 6).getValues();
|
|
var out = [];
|
|
for (var i = 0; i < data.length; i++) {
|
|
var r = data[i];
|
|
var tgl = toDateStr_(r[1]);
|
|
if (tgl !== d) continue;
|
|
out.push({
|
|
id: String(r[0] || ''),
|
|
tanggal: tgl,
|
|
judul: String(r[2] || ''),
|
|
catatan: String(r[3] || ''),
|
|
user: String(r[4] || ''),
|
|
timestamp: r[5] instanceof Date ? Utilities.formatDate(r[5], 'GMT+7', 'yyyy-MM-dd HH:mm:ss') : String(r[5] || '')
|
|
});
|
|
}
|
|
out.sort(function(a, b) { return String(b.timestamp || '').localeCompare(String(a.timestamp || '')); });
|
|
return out;
|
|
}
|
|
|
|
function saveCalendarNote(note, userName) {
|
|
var sheet = setupCalendarNotesSheet_();
|
|
var now = new Date();
|
|
var payload = note || {};
|
|
var id = String(payload.id || '').trim();
|
|
var tgl = normalizeDateStr_(payload.tanggal);
|
|
var judul = String(payload.judul || '').trim();
|
|
var catatan = String(payload.catatan || '').trim();
|
|
if (!judul && !catatan) throw new Error('Judul atau catatan harus diisi.');
|
|
var user = String(userName || payload.user || '');
|
|
var lastRow = sheet.getLastRow();
|
|
var foundRow = -1;
|
|
if (id && lastRow > 1) {
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
|
|
for (var i = 0; i < data.length; i++) {
|
|
if (String(data[i][0] || '') === id) { foundRow = i + 2; break; }
|
|
}
|
|
}
|
|
if (!id) id = 'CAL-' + Date.now() + '-' + Math.floor(Math.random() * 1000);
|
|
var row = [id, tgl, judul, catatan, user || 'Unknown', now];
|
|
if (foundRow > -1) {
|
|
sheet.getRange(foundRow, 1, 1, row.length).setValues([row]);
|
|
} else {
|
|
sheet.appendRow(row);
|
|
}
|
|
return getCalendarNotesByDate(tgl);
|
|
}
|
|
|
|
function deleteCalendarNote(id) {
|
|
var sheet = setupCalendarNotesSheet_();
|
|
var target = String(id || '').trim();
|
|
if (!target) return true;
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return true;
|
|
var data = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
|
|
for (var i = data.length - 1; i >= 0; i--) {
|
|
if (String(data[i][0] || '') === target) sheet.deleteRow(i + 2);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function include(filename) {
|
|
return HtmlService.createHtmlOutputFromFile(filename).getContent();
|
|
}
|
|
|
|
function normalizeDateStr_(s) {
|
|
var str = String(s || '').trim();
|
|
if (/^\d{4}-\d{2}-\d{2}$/.test(str)) return str;
|
|
return Utilities.formatDate(new Date(), 'GMT+7', 'yyyy-MM-dd');
|
|
}
|
|
|
|
function normalizeRange_(startDate, endDate) {
|
|
var s = normalizeDateStr_(startDate);
|
|
var e = normalizeDateStr_(endDate);
|
|
if (s > e) { var t = s; s = e; e = t; }
|
|
return { start: s, end: e };
|
|
}
|
|
|
|
function toDateStr_(val) {
|
|
if (!val) return '';
|
|
if (val instanceof Date) return Utilities.formatDate(val, 'GMT+7', 'yyyy-MM-dd');
|
|
var s = String(val);
|
|
if (!s) return '';
|
|
return s.split(' ')[0].split('T')[0];
|
|
}
|
|
|
|
function toHour_(val) {
|
|
if (!val) return -1;
|
|
if (val instanceof Date) return val.getHours();
|
|
var s = String(val);
|
|
if (!s) return -1;
|
|
var m = s.match(/(\d{1,2}):(\d{2})/);
|
|
if (m && m[1]) return Math.min(23, Math.max(0, parseInt(m[1], 10)));
|
|
return -1;
|
|
}
|
|
|
|
function daysBetweenInclusive_(startDate, endDate) {
|
|
var s = new Date(startDate + 'T00:00:00');
|
|
var e = new Date(endDate + 'T00:00:00');
|
|
var ms = e.getTime() - s.getTime();
|
|
if (isNaN(ms)) return 1;
|
|
return Math.max(1, Math.floor(ms / 86400000) + 1);
|
|
}
|
|
|
|
function normalizePorsiBaseName_(name) {
|
|
var s = String(name || '').trim();
|
|
if (!s) return '';
|
|
return s.replace(/^->\s*/, '').trim();
|
|
}
|
|
|
|
function getRekapByDate(dateStr) {
|
|
var d = String(dateStr || '').trim();
|
|
if (!/^\d{4}-\d{2}-\d{2}$/.test(d)) {
|
|
d = Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd");
|
|
}
|
|
return buildRekap_(d, d);
|
|
}
|
|
|
|
function getRekapByRange(startDate, endDate) {
|
|
var s = String(startDate || '').trim();
|
|
var e = String(endDate || '').trim();
|
|
if (!/^\d{4}-\d{2}-\d{2}$/.test(s) || !/^\d{4}-\d{2}-\d{2}$/.test(e)) {
|
|
throw new Error('Format tanggal tidak valid. Gunakan YYYY-MM-DD.');
|
|
}
|
|
if (s > e) {
|
|
var tmp = s; s = e; e = tmp;
|
|
}
|
|
return buildRekap_(s, e);
|
|
}
|
|
|
|
function getSalesAnalyticsByRange(startDate, endDate) {
|
|
var r = normalizeRange_(startDate, endDate);
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetTrans = ss.getSheetByName('Transaksi');
|
|
if (!sheetTrans || sheetTrans.getLastRow() <= 1) {
|
|
return {
|
|
range: { start: r.start, end: r.end },
|
|
summary: { totalSales: 0, totalTransactions: 0, avgTransaction: 0 },
|
|
methods: {},
|
|
dailySales: [],
|
|
topItems: [],
|
|
hourlyCounts: new Array(24).fill(0),
|
|
buckets: { lt100: 0, gte100: 0, gte200: 0 },
|
|
avgMenuPerDay: []
|
|
};
|
|
}
|
|
|
|
var lastRow = sheetTrans.getLastRow();
|
|
var data = sheetTrans.getRange(2, 1, lastRow - 1, Math.max(30, sheetTrans.getLastColumn())).getValues();
|
|
|
|
var totalSales = 0;
|
|
var totalTransactions = 0;
|
|
var methods = {};
|
|
var dailyTrans = {};
|
|
var itemCounts = {};
|
|
var hourlyCounts = new Array(24).fill(0);
|
|
var buckets = { lt100: 0, gte100: 0, gte200: 0 };
|
|
|
|
data.forEach(function(row) {
|
|
var status = String(row[2] || '');
|
|
if (status !== 'Selesai') return;
|
|
var dateObj = row[25] || row[5];
|
|
var dateStr = toDateStr_(dateObj);
|
|
if (!dateStr || dateStr < r.start || dateStr > r.end) return;
|
|
|
|
var total = Number(row[17] || 0);
|
|
totalSales += total;
|
|
totalTransactions++;
|
|
|
|
if (total < 100000) buckets.lt100++;
|
|
if (total >= 100000) buckets.gte100++;
|
|
if (total >= 200000) buckets.gte200++;
|
|
|
|
var method = String(row[21] || 'Tunai');
|
|
if (method === 'Multi') {
|
|
var catatan = String(row[27] || '');
|
|
var details = {};
|
|
try { details = JSON.parse(catatan || '{}'); } catch (e) { details = {}; }
|
|
Object.keys(details || {}).forEach(function(k) {
|
|
methods[k] = (methods[k] || 0) + (Number(details[k]) || 0);
|
|
});
|
|
} else {
|
|
methods[method] = (methods[method] || 0) + total;
|
|
}
|
|
dailyTrans[dateStr] = (dailyTrans[dateStr] || 0) + total;
|
|
|
|
var hour = toHour_(row[25] || row[6] || row[5]);
|
|
if (hour >= 0) hourlyCounts[hour] = (hourlyCounts[hour] || 0) + 1;
|
|
|
|
try {
|
|
var items = JSON.parse(row[7] || '[]');
|
|
items.forEach(function(it) {
|
|
var nmRaw = String(it.nama || '').trim();
|
|
if (!nmRaw) return;
|
|
var nm = normalizePorsiBaseName_(nmRaw) || nmRaw;
|
|
itemCounts[nm] = (itemCounts[nm] || 0) + (Number(it.qty) || 0);
|
|
});
|
|
} catch (e) {}
|
|
});
|
|
|
|
var dailySales = Object.keys(dailyTrans).sort().map(function(d) { return { date: d, sales: dailyTrans[d] || 0 }; });
|
|
var topItems = Object.keys(itemCounts).map(function(k) { return [k, itemCounts[k]]; }).sort(function(a, b) { return b[1] - a[1]; }).slice(0, 10);
|
|
var dayCount = daysBetweenInclusive_(r.start, r.end);
|
|
var avgMenuPerDay = Object.keys(itemCounts).map(function(k) {
|
|
return { nama: k, avg: (itemCounts[k] || 0) / dayCount, total: itemCounts[k] || 0 };
|
|
}).sort(function(a, b) { return b.avg - a.avg; }).slice(0, 10);
|
|
|
|
return {
|
|
range: { start: r.start, end: r.end },
|
|
summary: { totalSales: totalSales, totalTransactions: totalTransactions, avgTransaction: totalTransactions > 0 ? (totalSales / totalTransactions) : 0 },
|
|
methods: methods,
|
|
dailySales: dailySales,
|
|
topItems: topItems,
|
|
hourlyCounts: hourlyCounts,
|
|
buckets: buckets,
|
|
avgMenuPerDay: avgMenuPerDay
|
|
};
|
|
}
|
|
|
|
function getExpenseAnalyticsByRange(startDate, endDate) {
|
|
var r = normalizeRange_(startDate, endDate);
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetBelanja = ss.getSheetByName('Belanja');
|
|
if (!sheetBelanja || sheetBelanja.getLastRow() <= 1) {
|
|
return { range: { start: r.start, end: r.end }, summary: { totalBelanja: 0, maxBelanja: 0 }, dailyBelanja: [], kategoriBelanja: {}, topBelanja: [] };
|
|
}
|
|
var lastRow = sheetBelanja.getLastRow();
|
|
var data = sheetBelanja.getRange(2, 1, lastRow - 1, Math.max(9, sheetBelanja.getLastColumn())).getValues();
|
|
var totalBelanja = 0;
|
|
var dailyBelanja = {};
|
|
var kategoriBelanja = {};
|
|
var belanjaList = [];
|
|
data.forEach(function(row) {
|
|
var dateStr = toDateStr_(row[5]);
|
|
if (!dateStr || dateStr < r.start || dateStr > r.end) return;
|
|
var nama = String(row[1] || '');
|
|
var total = Number(row[4] || 0);
|
|
var kat = String(row[6] || 'Lainnya');
|
|
totalBelanja += total;
|
|
dailyBelanja[dateStr] = (dailyBelanja[dateStr] || 0) + total;
|
|
kategoriBelanja[kat] = (kategoriBelanja[kat] || 0) + total;
|
|
belanjaList.push({ nama: nama, total: total, kat: kat, tgl: dateStr });
|
|
});
|
|
var daily = Object.keys(dailyBelanja).sort().map(function(d) { return { date: d, total: dailyBelanja[d] || 0 }; });
|
|
var topBelanja = belanjaList.sort(function(a, b) { return b.total - a.total; }).slice(0, 10);
|
|
var maxBelanja = topBelanja.length ? topBelanja[0].total : 0;
|
|
return { range: { start: r.start, end: r.end }, summary: { totalBelanja: totalBelanja, maxBelanja: maxBelanja }, dailyBelanja: daily, kategoriBelanja: kategoriBelanja, topBelanja: topBelanja };
|
|
}
|
|
|
|
function getDashboardBundleByRange(startDate, endDate) {
|
|
var sales = getSalesAnalyticsByRange(startDate, endDate);
|
|
var exp = getExpenseAnalyticsByRange(startDate, endDate);
|
|
var profit = Number((sales && sales.summary && sales.summary.totalSales) || 0) - Number((exp && exp.summary && exp.summary.totalBelanja) || 0);
|
|
return { range: sales.range, sales: sales, expense: exp, profit: profit };
|
|
}
|
|
|
|
function getTransaksiHistoryPage(startDate, endDate, query, offset, limit) {
|
|
var r = normalizeRange_(startDate, endDate);
|
|
var q = String(query || '').trim().toLowerCase();
|
|
var off = Math.max(0, Number(offset) || 0);
|
|
var lim = Math.min(300, Math.max(20, Number(limit) || 50));
|
|
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetTrans = ss.getSheetByName('Transaksi');
|
|
if (!sheetTrans || sheetTrans.getLastRow() <= 1) return { range: { start: r.start, end: r.end }, items: [], offset: off, nextOffset: off, hasMore: false };
|
|
|
|
var lastRow = sheetTrans.getLastRow();
|
|
var data = sheetTrans.getRange(2, 1, lastRow - 1, Math.max(30, sheetTrans.getLastColumn())).getValues();
|
|
var out = [];
|
|
var skipped = 0;
|
|
var stoppedAt = -1;
|
|
for (var i = data.length - 1; i >= 0; i--) {
|
|
var row = data[i];
|
|
var status = String(row[2] || '');
|
|
var id = String(row[0] || '');
|
|
if (!id) continue;
|
|
if (!status) continue;
|
|
var dateStr = toDateStr_(row[25] || row[5]);
|
|
if (!dateStr || dateStr < r.start || dateStr > r.end) continue;
|
|
|
|
var meja = String(row[1] || '');
|
|
var nama = String(row[3] || '');
|
|
var wa = String(row[4] || '');
|
|
var metode = String(row[21] || '');
|
|
var total = Number(row[17] || 0);
|
|
var ts = row[25] || row[5];
|
|
var tsStr = (ts instanceof Date) ? Utilities.formatDate(ts, 'GMT+7', 'yyyy-MM-dd HH:mm:ss') : String(ts || '');
|
|
|
|
if (q) {
|
|
var hay = (id + ' ' + meja + ' ' + nama + ' ' + wa + ' ' + metode + ' ' + status).toLowerCase();
|
|
if (hay.indexOf(q) === -1) continue;
|
|
}
|
|
|
|
if (skipped < off) { skipped++; continue; }
|
|
|
|
var items = [];
|
|
try { items = JSON.parse(row[7] || '[]'); } catch (e) { items = []; }
|
|
out.push({ id: id, meja: meja, status: status, nama: nama, wa: wa, metodeBayar: metode, total: total, timestamp: tsStr, tgl: dateStr, items: items, catatan: String(row[27] || '') });
|
|
if (out.length >= lim) { stoppedAt = i - 1; break; }
|
|
}
|
|
var nextOffset = off + out.length;
|
|
var hasMore = false;
|
|
if (stoppedAt >= 0) {
|
|
for (var j = stoppedAt; j >= 0; j--) {
|
|
var row2 = data[j];
|
|
var status2 = String(row2[2] || '');
|
|
var id2 = String(row2[0] || '');
|
|
if (!id2) continue;
|
|
if (!status2) continue;
|
|
var dateStr2 = toDateStr_(row2[25] || row2[5]);
|
|
if (!dateStr2 || dateStr2 < r.start || dateStr2 > r.end) continue;
|
|
var meja2 = String(row2[1] || '');
|
|
var nama2 = String(row2[3] || '');
|
|
var wa2 = String(row2[4] || '');
|
|
var metode2 = String(row2[21] || '');
|
|
if (q) {
|
|
var hay2 = (id2 + ' ' + meja2 + ' ' + nama2 + ' ' + wa2 + ' ' + metode2 + ' ' + status2).toLowerCase();
|
|
if (hay2.indexOf(q) === -1) continue;
|
|
}
|
|
hasMore = true;
|
|
break;
|
|
}
|
|
}
|
|
return { range: { start: r.start, end: r.end }, items: out, offset: off, nextOffset: nextOffset, hasMore: hasMore };
|
|
}
|
|
|
|
function buildRekap_(startDate, endDate) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetTrans = ss.getSheetByName('Transaksi');
|
|
var sheetBelanja = ss.getSheetByName('Belanja');
|
|
|
|
var transData = sheetTrans ? sheetTrans.getDataRange().getValues() : [];
|
|
var belanjaData = sheetBelanja ? sheetBelanja.getDataRange().getValues() : [];
|
|
|
|
var methods = { 'Tunai': 0, 'QRIS': 0, 'Debit': 0, 'Credit': 0, 'Transfer': 0 };
|
|
var portions = {};
|
|
var totalNota = 0;
|
|
var totalSales = 0;
|
|
var totalBelanja = 0;
|
|
|
|
if (transData.length > 1) {
|
|
transData.slice(1).forEach(function(row) {
|
|
var status = String(row[2] || '');
|
|
if (status !== 'Selesai') return;
|
|
|
|
var dateObj = row[25] || row[5];
|
|
var dateStr = dateObj instanceof Date ? Utilities.formatDate(dateObj, "GMT+7", "yyyy-MM-dd") : String(dateObj).split(' ')[0];
|
|
if (dateStr < startDate || dateStr > endDate) return;
|
|
|
|
totalNota++;
|
|
var total = Number(row[17] || 0);
|
|
totalSales += total;
|
|
|
|
var method = String(row[21] || 'Tunai');
|
|
if (method === 'Multi') {
|
|
var catatan = String(row[27] || '');
|
|
var details = {};
|
|
try { details = JSON.parse(catatan || '{}'); } catch (e) { details = {}; }
|
|
Object.keys(details || {}).forEach(function(k) {
|
|
if (methods.hasOwnProperty(k)) methods[k] += (Number(details[k]) || 0);
|
|
});
|
|
} else {
|
|
if (methods.hasOwnProperty(method)) methods[method] += total;
|
|
else methods[method] = (methods[method] || 0) + total;
|
|
}
|
|
|
|
try {
|
|
var items = JSON.parse(row[7] || '[]');
|
|
items.forEach(function(it) {
|
|
var nm = String(it.nama || '').trim();
|
|
if (!nm) return;
|
|
portions[nm] = (portions[nm] || 0) + (Number(it.qty) || 0);
|
|
});
|
|
} catch (e) {}
|
|
});
|
|
}
|
|
|
|
if (belanjaData.length > 1) {
|
|
belanjaData.slice(1).forEach(function(row) {
|
|
var dateObj = row[5];
|
|
var dateStr = dateObj instanceof Date ? Utilities.formatDate(dateObj, "GMT+7", "yyyy-MM-dd") : String(dateObj).split(' ')[0];
|
|
if (dateStr < startDate || dateStr > endDate) return;
|
|
totalBelanja += (Number(row[4] || 0));
|
|
});
|
|
}
|
|
|
|
var sortedPortions = Object.keys(portions).map(function(k) { return { nama: k, qty: portions[k] }; })
|
|
.sort(function(a, b) { return b.qty - a.qty; });
|
|
|
|
return {
|
|
startDate: startDate,
|
|
endDate: endDate,
|
|
isRange: startDate !== endDate,
|
|
summary: {
|
|
totalNota: totalNota,
|
|
totalSales: totalSales,
|
|
totalBelanja: totalBelanja,
|
|
netProfit: totalSales - totalBelanja
|
|
},
|
|
methods: methods,
|
|
portions: sortedPortions
|
|
};
|
|
}
|
|
|
|
function importTransactions(data) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
if (!sheet) throw new Error('Sheet Transaksi tidak ditemukan.');
|
|
|
|
var existingIds = [];
|
|
if (sheet.getLastRow() > 1) {
|
|
existingIds = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues().map(function(r) { return String(r[0]); });
|
|
}
|
|
|
|
var rowsToAdd = [];
|
|
data.forEach(function(t) {
|
|
if (existingIds.indexOf(String(t[0])) > -1) return; // Index 0 adalah ID
|
|
|
|
// Pastikan format tanggal benar
|
|
var row = t.map(function(val, idx) {
|
|
if ((idx === 5 || idx === 20 || idx === 25) && val) { // Kolom Tanggal, Tgl DP, Timestamp
|
|
return new Date(val);
|
|
}
|
|
return val;
|
|
});
|
|
|
|
// Pastikan panjang baris sesuai (28 kolom)
|
|
while (row.length < 28) row.push('');
|
|
rowsToAdd.push(row);
|
|
});
|
|
|
|
if (rowsToAdd.length > 0) {
|
|
sheet.getRange(sheet.getLastRow() + 1, 1, rowsToAdd.length, 28).setValues(rowsToAdd);
|
|
}
|
|
return { success: true, count: rowsToAdd.length };
|
|
}
|
|
|
|
function importBelanja(data) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
|
|
var rowsToAdd = [];
|
|
data.forEach(function(b) {
|
|
// Pastikan format tanggal/timestamp benar
|
|
var row = b.map(function(val, idx) {
|
|
if ((idx === 5 || idx === 8) && val) { // Kolom Tanggal, Timestamp
|
|
return new Date(val);
|
|
}
|
|
return val;
|
|
});
|
|
|
|
// Pastikan panjang baris sesuai (9 kolom)
|
|
while (row.length < 9) row.push('');
|
|
rowsToAdd.push(row);
|
|
});
|
|
|
|
if (rowsToAdd.length > 0) {
|
|
sheet.getRange(sheet.getLastRow() + 1, 1, rowsToAdd.length, 9).setValues(rowsToAdd);
|
|
}
|
|
return { success: true, count: rowsToAdd.length };
|
|
}
|