2722 lines
107 KiB
JavaScript
2722 lines
107 KiB
JavaScript
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
|
|
var parseNumberFlexible = function(val) {
|
|
if (val === null || val === undefined || val === '') return 0;
|
|
if (typeof val === 'number') return val;
|
|
var s = String(val).trim();
|
|
s = s.replace(/[^\d.,-]/g, '');
|
|
if (!s) return 0;
|
|
var n = parseFloat(s.replace(/,/g, ''));
|
|
return isNaN(n) ? 0 : n;
|
|
};
|
|
|
|
var normalizeImageUrl = function(url) {
|
|
var raw = String(url || '').trim();
|
|
if (!raw) return '';
|
|
var m = raw.match(/\/d\/([^\/\?\s]+)/);
|
|
if (m && m[1]) return 'https://drive.google.com/thumbnail?id=' + m[1] + '&sz=w400';
|
|
return raw;
|
|
};
|
|
|
|
var parseDate = function(val) {
|
|
if (!val) return '';
|
|
if (val instanceof Date) return Utilities.formatDate(val, "GMT+7", "yyyy-MM-dd HH:mm:ss");
|
|
return String(val);
|
|
};
|
|
|
|
var getSettingsSheet_ = function() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
return ss.getSheetByName('Setup') || ss.getSheetByName('Settings') || null;
|
|
};
|
|
|
|
var getSettingsMap_ = function() {
|
|
var sheet = getSettingsSheet_();
|
|
if (!sheet) return {};
|
|
var data = sheet.getDataRange().getValues();
|
|
var settings = {};
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (data[i][0]) settings[String(data[i][0])] = String(data[i][1]);
|
|
}
|
|
return settings;
|
|
};
|
|
|
|
var normalizeHeaderKey_ = function(s) {
|
|
return String(s || '').trim().toLowerCase();
|
|
};
|
|
|
|
var getHeaderIndexMap_ = function(sheet) {
|
|
if (!sheet) return {};
|
|
var lastCol = sheet.getLastColumn();
|
|
if (lastCol < 1) return {};
|
|
var headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
|
|
var map = {};
|
|
for (var i = 0; i < headers.length; i++) {
|
|
var k = normalizeHeaderKey_(headers[i]);
|
|
if (!k) continue;
|
|
if (map[k] == null) map[k] = i;
|
|
}
|
|
return map;
|
|
};
|
|
|
|
var ensureColumnExists_ = function(sheet, headerName) {
|
|
if (!sheet) return -1;
|
|
var key = normalizeHeaderKey_(headerName);
|
|
var map = getHeaderIndexMap_(sheet);
|
|
if (map[key] != null) return map[key] + 1;
|
|
var lastCol = sheet.getLastColumn();
|
|
sheet.getRange(1, lastCol + 1).setValue(headerName);
|
|
sheet.getRange(1, lastCol + 1).setFontWeight('bold').setBackground('#f3f3f3');
|
|
return lastCol + 1;
|
|
};
|
|
|
|
var sendTelegram_ = function(text) {
|
|
try {
|
|
var settings = getSettingsMap_();
|
|
if (String(settings.telegram_enabled || '').toLowerCase() !== 'true') return { skipped: true };
|
|
var token = String(settings.telegram_bot_token || '').trim();
|
|
var chatId = String(settings.telegram_chat_id || '').trim();
|
|
if (!token || !chatId) return { skipped: true };
|
|
|
|
var url = 'https://api.telegram.org/bot' + token + '/sendMessage';
|
|
var payload = { chat_id: chatId, text: String(text || ''), disable_web_page_preview: true };
|
|
var resp = UrlFetchApp.fetch(url, {
|
|
method: 'post',
|
|
contentType: 'application/json',
|
|
payload: JSON.stringify(payload),
|
|
muteHttpExceptions: true
|
|
});
|
|
return { success: true, status: resp.getResponseCode() };
|
|
} catch (e) {
|
|
return { error: String(e && e.message ? e.message : e) };
|
|
}
|
|
};
|
|
|
|
function doGet(e) {
|
|
if (!e || !e.parameter) {
|
|
return HtmlService.createHtmlOutput('<h1>Error: Parameter e tidak ditemukan.</h1><p>Gunakan URL Web App untuk mengakses aplikasi ini.</p>');
|
|
}
|
|
var page = e.parameter.p || 'index'; // Default ke index (Kasir)
|
|
var meja = e.parameter.meja || '';
|
|
|
|
// Fitur PWA: Manifest & Service Worker
|
|
if (page === 'manifest') {
|
|
var settings = getSettingsMap_();
|
|
var storeName = String(settings.store_name || 'POS');
|
|
var shortName = storeName.split(' ')[0] || storeName;
|
|
if (shortName.length > 12) shortName = shortName.slice(0, 12);
|
|
var iconUrl = String(settings.qris_image_url || 'https://lh3.googleusercontent.com/d/1hGPL3AlVGRMeZTzpwOHp-l-JzOG5tS09');
|
|
var manifest = {
|
|
"name": storeName + " POS",
|
|
"short_name": shortName,
|
|
"description": "Point of Sale",
|
|
"start_url": "./?p=index",
|
|
"display": "standalone",
|
|
"background_color": "#111111",
|
|
"theme_color": "#e11d48",
|
|
"icons": [
|
|
{
|
|
"src": iconUrl,
|
|
"sizes": "192x192",
|
|
"type": "image/png",
|
|
"purpose": "any maskable"
|
|
},
|
|
{
|
|
"src": iconUrl,
|
|
"sizes": "512x512",
|
|
"type": "image/png",
|
|
"purpose": "any maskable"
|
|
}
|
|
]
|
|
};
|
|
return ContentService.createTextOutput(JSON.stringify(manifest)).setMimeType(ContentService.MimeType.JSON);
|
|
}
|
|
|
|
if (page === 'sw') {
|
|
var sw = "const CACHE_NAME = 'pos-v2'; self.addEventListener('install', (e) => { self.skipWaiting(); }); self.addEventListener('activate', (e) => { e.waitUntil(clients.claim()); }); self.addEventListener('fetch', (e) => { e.respondWith(fetch(e.request).catch(() => caches.match(e.request))); });";
|
|
return ContentService.createTextOutput(sw).setMimeType(ContentService.MimeType.JAVASCRIPT);
|
|
}
|
|
|
|
var fileName = 'Index';
|
|
if (page === 'dapur') fileName = 'Dapur';
|
|
if (page === 'order' || meja) fileName = 'Pelanggan';
|
|
if (page === 'poin') fileName = 'Poin';
|
|
if (page === 'belanja') fileName = 'Belanja';
|
|
|
|
try {
|
|
var settings = getSettingsMap_();
|
|
var storeName = String(settings.store_name || 'POS');
|
|
var template = HtmlService.createTemplateFromFile(fileName);
|
|
template.meja = meja;
|
|
|
|
return template.evaluate()
|
|
.setTitle(storeName + (page === 'dapur' ? ' - DAPUR' : (page === 'poin' ? ' - POIN' : (page === 'belanja' ? ' - BELANJA' : (meja ? ' - MEJA ' + meja : '')))))
|
|
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
|
|
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
|
|
} catch (err) {
|
|
return HtmlService.createHtmlOutput(
|
|
'<h1>Halaman tidak bisa dimuat</h1>' +
|
|
'<p>Halaman: <b>' + fileName + '</b></p>' +
|
|
'<p>Silakan deploy ulang dan pastikan file HTML sudah ada di project Apps Script yang dideploy.</p>' +
|
|
'<pre style="white-space:pre-wrap; background:#f8fafc; padding:12px; border:1px solid #e5e7eb; border-radius:8px; font-size:12px;">' +
|
|
String(err && err.stack ? err.stack : err) +
|
|
'</pre>'
|
|
);
|
|
}
|
|
}
|
|
|
|
function getMoreHistory(offset) {
|
|
try {
|
|
var sheetTrans = ss.getSheetByName('Transaksi');
|
|
var lastRow = sheetTrans.getLastRow();
|
|
var startRow = Math.max(2, lastRow - offset - 49); // Ambil 50 baris berikutnya
|
|
var numRows = Math.min(50, lastRow - offset - 1);
|
|
|
|
if (numRows <= 0) return [];
|
|
|
|
var values = sheetTrans.getRange(startRow, 1, numRows, sheetTrans.getLastColumn()).getValues();
|
|
var result = values.map(function(row) {
|
|
return {
|
|
id: row[0],
|
|
meja: row[1],
|
|
status: row[2],
|
|
nama: row[3],
|
|
wa: row[4],
|
|
tgl: parseDate(row[5]),
|
|
jam: parseDate(row[6]),
|
|
items: JSON.parse(row[7] || '[]'),
|
|
subtotal: Number(row[8]),
|
|
diskon: Number(row[9]),
|
|
poinDipakai: Number(row[10]),
|
|
pajakPersen: Number(row[11]),
|
|
servicePersen: Number(row[12]),
|
|
adminPersen: Number(row[13]),
|
|
pajak: Number(row[14]),
|
|
service: Number(row[15]),
|
|
adminFee: Number(row[16]),
|
|
total: Number(row[17]),
|
|
dp: Number(row[18]),
|
|
metodeDp: String(row[19]),
|
|
tglDp: parseDate(row[20]),
|
|
metodeBayar: String(row[21]),
|
|
bayar: Number(row[22]),
|
|
kembali: Number(row[23]),
|
|
poinDapat: Number(row[24]),
|
|
timestamp: parseDate(row[25]),
|
|
poinAwal: Number(row[26] || 0),
|
|
catatan: String(row[27] || ''),
|
|
buktiBayar: String(row[28] || ''),
|
|
buktiReview: String(row[29] || '')
|
|
};
|
|
});
|
|
return result;
|
|
} catch (e) {
|
|
return [];
|
|
}
|
|
}
|
|
|
|
function uploadPaymentProof(payload) {
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var folder;
|
|
var settingsSheet = getSettingsSheet_();
|
|
var folderId = '';
|
|
if (settingsSheet) {
|
|
var sd = settingsSheet.getDataRange().getValues();
|
|
for (var i = 1; i < sd.length; i++) {
|
|
if (String(sd[i][0]) === 'drive_bukti_folder_id') { folderId = String(sd[i][1] || ''); break; }
|
|
}
|
|
}
|
|
if (folderId) {
|
|
folder = DriveApp.getFolderById(folderId);
|
|
} else {
|
|
var folders = DriveApp.getFoldersByName('POSFuku_BuktiBayar');
|
|
if (folders.hasNext()) folder = folders.next();
|
|
else return { error: "Folder Drive untuk bukti bayar belum ada. Jalankan setupDriveFolders() oleh admin." };
|
|
}
|
|
|
|
var contentType = payload.mimeType || 'image/jpeg';
|
|
var bytes = Utilities.base64Decode(payload.base64);
|
|
var blob = Utilities.newBlob(bytes, contentType, 'Bukti_' + payload.id + '.jpg');
|
|
var file = folder.createFile(blob);
|
|
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
|
|
|
|
return { success: true, url: file.getUrl(), fileId: file.getId() };
|
|
} catch (e) {
|
|
return { error: e.message };
|
|
}
|
|
}
|
|
|
|
function uploadReviewProof(payload) {
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var folder;
|
|
var settingsSheet = getSettingsSheet_();
|
|
var folderId = '';
|
|
if (settingsSheet) {
|
|
var sd = settingsSheet.getDataRange().getValues();
|
|
for (var i = 1; i < sd.length; i++) {
|
|
if (String(sd[i][0]) === 'drive_review_folder_id') { folderId = String(sd[i][1] || ''); break; }
|
|
}
|
|
}
|
|
if (folderId) {
|
|
folder = DriveApp.getFolderById(folderId);
|
|
} else {
|
|
var folders = DriveApp.getFoldersByName('POSFuku_ReviewScreenshots');
|
|
if (folders.hasNext()) folder = folders.next();
|
|
else return { error: "Folder Drive untuk screenshot review belum ada. Jalankan setupDriveFolders() oleh admin." };
|
|
}
|
|
|
|
var contentType = payload.mimeType || 'image/jpeg';
|
|
var data = String(payload.base64 || '');
|
|
var commaIndex = data.indexOf(',');
|
|
if (commaIndex > -1) {
|
|
data = data.substr(commaIndex + 1);
|
|
}
|
|
var bytes = Utilities.base64Decode(data);
|
|
var blob = Utilities.newBlob(bytes, contentType, 'Review_' + payload.id + '.jpg');
|
|
var file = folder.createFile(blob);
|
|
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
|
|
|
|
return { success: true, url: file.getUrl(), fileId: file.getId() };
|
|
} catch (e) {
|
|
return { error: e.message };
|
|
}
|
|
}
|
|
|
|
function setupDriveFolders() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var settings = getSettingsSheet_();
|
|
if (!settings) { setupSheets(); settings = getSettingsSheet_(); }
|
|
if (!settings) throw new Error('Sheet Setup/Settings belum siap.');
|
|
|
|
var setRow = function(key, value, desc) {
|
|
var data = settings.getDataRange().getValues();
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === key) {
|
|
settings.getRange(i + 1, 2).setValue(String(value));
|
|
if (desc !== undefined) settings.getRange(i + 1, 3).setValue(String(desc));
|
|
return;
|
|
}
|
|
}
|
|
settings.appendRow([key, String(value), String(desc || '')]);
|
|
};
|
|
|
|
var findOrCreate = function(name) {
|
|
var it = DriveApp.getFoldersByName(name);
|
|
if (it.hasNext()) return it.next();
|
|
return DriveApp.createFolder(name);
|
|
};
|
|
|
|
var bukti = findOrCreate('POSFuku_BuktiBayar');
|
|
var review = findOrCreate('POSFuku_ReviewScreenshots');
|
|
|
|
setRow('drive_bukti_folder_id', bukti.getId(), 'Folder ID Drive untuk upload bukti bayar');
|
|
setRow('drive_review_folder_id', review.getId(), 'Folder ID Drive untuk upload screenshot review');
|
|
|
|
return { success: true, buktiFolderId: bukti.getId(), reviewFolderId: review.getId() };
|
|
}
|
|
|
|
function updateTransactionReviewPhoto(id, photoUrl) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow < 2) throw new Error('Tidak ada data.');
|
|
|
|
var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < ids.length; i++) {
|
|
if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 30).setValue(String(photoUrl)); // Kolom AD adalah 'Bukti Review'
|
|
return getInitialData();
|
|
} else {
|
|
throw new Error('Pesanan tidak ditemukan.');
|
|
}
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function setupSheets() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
if (!ss) throw new Error('Spreadsheet tidak aktif');
|
|
|
|
var ensureHeaderRow = function(sheet, headers) {
|
|
if (sheet.getLastRow() === 0) {
|
|
sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold').setBackground('#f3f3f3');
|
|
return;
|
|
}
|
|
// Jika baris 1 kosong, isi. Jika tidak, jangan ganggu data.
|
|
var row1 = sheet.getRange(1, 1, 1, headers.length).getValues()[0];
|
|
var empty = row1.every(function(v) { return !String(v).trim(); });
|
|
if (empty) {
|
|
sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold').setBackground('#f3f3f3');
|
|
}
|
|
};
|
|
|
|
var menu = ss.getSheetByName('Menu') || ss.insertSheet('Menu');
|
|
ensureHeaderRow(menu, ['Nama', 'Harga', 'Kategori', 'Status', 'Gambar URL', 'Stok']);
|
|
menu.getRange('B:B').setNumberFormat('#,##0');
|
|
menu.getRange('F:F').setNumberFormat('0');
|
|
|
|
var trans = ss.getSheetByName('Transaksi') || ss.insertSheet('Transaksi');
|
|
ensureHeaderRow(trans, [
|
|
'ID', 'Meja', 'Status', 'Nama', 'WA', 'Tanggal', 'Jam Booking', 'Items (JSON)',
|
|
'Subtotal', 'Diskon', 'Poin Dipakai (Rp)', 'Pajak %', 'Service %', 'Admin %',
|
|
'Pajak (Rp)', 'Service (Rp)', 'Admin (Rp)', 'Total Akhir',
|
|
'DP', 'Metode DP', 'Tgl DP', 'Metode Bayar', 'Jumlah Bayar', 'Kembali',
|
|
'Poin Didapat', 'Timestamp', 'Poin Awal', 'Catatan', 'Bukti Bayar', 'Bukti Review'
|
|
]);
|
|
|
|
var numCols = ['I', 'J', 'K', 'O', 'P', 'Q', 'R', 'S', 'T', 'W', 'X', 'Y', 'AA'];
|
|
numCols.forEach(function(c) { trans.getRange(c + ':' + c).setNumberFormat('#,##0'); });
|
|
|
|
var pctCols = ['L', 'M', 'N'];
|
|
pctCols.forEach(function(c) { trans.getRange(c + ':' + c).setNumberFormat('0'); });
|
|
|
|
trans.getRange('F:F').setNumberFormat('yyyy-mm-dd');
|
|
trans.getRange('G:G').setNumberFormat('hh:mm');
|
|
trans.getRange('U:U').setNumberFormat('yyyy-mm-dd');
|
|
trans.getRange('Z:Z').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
trans.getRange('E:E').setNumberFormat('@');
|
|
|
|
var cust = ss.getSheetByName('Pelanggan') || ss.insertSheet('Pelanggan');
|
|
ensureHeaderRow(cust, ['Nama', 'WhatsApp', 'Poin', 'Updated At']);
|
|
cust.getRange('B:B').setNumberFormat('@');
|
|
cust.getRange('C:C').setNumberFormat('#,##0');
|
|
cust.getRange('D:D').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var belanja = ss.getSheetByName('Belanja') || ss.insertSheet('Belanja');
|
|
ensureHeaderRow(belanja, ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'Timestamp']);
|
|
belanja.getRange('C:C').setNumberFormat('#,##0');
|
|
belanja.getRange('E:E').setNumberFormat('#,##0');
|
|
belanja.getRange('F:F').setNumberFormat('yyyy-mm-dd');
|
|
belanja.getRange('I:I').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var rekap = ss.getSheetByName('Rekap') || ss.insertSheet('Rekap');
|
|
ensureHeaderRow(rekap, ['ID', 'Tanggal', 'Saldo Awal', 'Tunai', 'QRIS', 'Debit', 'Credit', 'Transfer', 'Catatan', 'Timestamp']);
|
|
rekap.getRange('C:H').setNumberFormat('#,##0');
|
|
rekap.getRange('B:B').setNumberFormat('yyyy-mm-dd');
|
|
rekap.getRange('J:J').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var stokHarian = ss.getSheetByName('StokHarian') || ss.insertSheet('StokHarian');
|
|
ensureHeaderRow(stokHarian, ['Tanggal', 'Menu', 'Stok Awal', 'Terpakai', 'Sisa', 'Timestamp']);
|
|
stokHarian.getRange('A:A').setNumberFormat('yyyy-mm-dd');
|
|
stokHarian.getRange('C:E').setNumberFormat('#,##0');
|
|
stokHarian.getRange('F:F').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var supHistory = ss.getSheetByName('SupplierHistory') || ss.insertSheet('SupplierHistory');
|
|
ensureHeaderRow(supHistory, ['ID', 'Nama Supplier', 'Kategori', 'WhatsApp', 'Pesan', 'Timestamp']);
|
|
supHistory.getRange('D:D').setNumberFormat('@');
|
|
supHistory.getRange('F:F').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var suppliers = ss.getSheetByName('Suppliers') || ss.insertSheet('Suppliers');
|
|
ensureHeaderRow(suppliers, ['ID', 'Nama Supplier', 'WhatsApp', 'Rekening', 'Timestamp']);
|
|
suppliers.getRange('C:C').setNumberFormat('@');
|
|
suppliers.getRange('D:D').setNumberFormat('@');
|
|
suppliers.getRange('E:E').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var feedback = ss.getSheetByName('Feedback') || ss.insertSheet('Feedback');
|
|
ensureHeaderRow(feedback, ['Timestamp', 'Nama Pelanggan', 'WhatsApp', 'Rating', 'Komentar']);
|
|
feedback.getRange('C:C').setNumberFormat('@');
|
|
feedback.getRange('A:A').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var waiters = ss.getSheetByName('Waiters') || ss.insertSheet('Waiters');
|
|
ensureHeaderRow(waiters, ['Nama', 'WhatsApp', 'Status', 'Timestamp']);
|
|
waiters.getRange('B:B').setNumberFormat('@');
|
|
waiters.getRange('D:D').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var paketKustom = ss.getSheetByName('PaketKustom') || ss.insertSheet('PaketKustom');
|
|
ensureHeaderRow(paketKustom, ['ID', 'Nama Paket', 'Items (JSON)', 'Total Harga', 'Timestamp']);
|
|
paketKustom.getRange('D:D').setNumberFormat('#,##0');
|
|
paketKustom.getRange('E:E').setNumberFormat('yyyy-mm-dd hh:mm:ss');
|
|
|
|
var setup = ss.getSheetByName('Setup') || ss.insertSheet('Setup');
|
|
ensureHeaderRow(setup, ['Key', 'Value', 'Description']);
|
|
setup.getRange('A:A').setNumberFormat('@');
|
|
setup.getRange('B:B').setNumberFormat('@');
|
|
|
|
var legacy = ss.getSheetByName('Settings');
|
|
if (legacy && setup.getLastRow() <= 1) {
|
|
var legacyData = legacy.getDataRange().getValues();
|
|
for (var l = 1; l < legacyData.length; l++) {
|
|
if (legacyData[l][0]) setup.appendRow([legacyData[l][0], legacyData[l][1], legacyData[l][2] || '']);
|
|
}
|
|
}
|
|
|
|
var setupData = setup.getDataRange().getValues();
|
|
var keys = setupData.map(function(r) { return r[0]; });
|
|
var ensureKey = function(key, value, desc) {
|
|
if (keys.indexOf(key) === -1) {
|
|
setup.appendRow([key, String(value), String(desc || '')]);
|
|
keys.push(key);
|
|
}
|
|
};
|
|
|
|
ensureKey('store_name', 'Fuku Shabu & Grill', 'Nama toko');
|
|
ensureKey('store_address', 'Ruko Puridelta Tigaraksa No. 17, Kab. Tangerang', 'Alamat toko');
|
|
ensureKey('store_whatsapp', '6285770558047', 'WhatsApp toko (format 62...)');
|
|
ensureKey('social_instagram_url', 'https://www.instagram.com/fukushabu.grill', 'URL Instagram');
|
|
ensureKey('social_tiktok_url', 'https://www.tiktok.com/@fukushabu.grill', 'URL TikTok');
|
|
ensureKey('social_gmaps_url', 'https://s.id/GMapsFusagi', 'URL Google Maps');
|
|
ensureKey('social_linktree_url', 'https://linktr.ee/fusagifukushabugrill', 'URL Linktree/Website');
|
|
ensureKey('report_wa_numbers', '6285770558047', 'Daftar WA penerima laporan (pisah koma, format 62...)');
|
|
|
|
ensureKey('pajak_persen', '0', 'Nilai pajak (%)');
|
|
ensureKey('service_persen', '0', 'Nilai service (%)');
|
|
ensureKey('admin_persen', '0', 'Nilai admin (%)');
|
|
|
|
ensureKey('kasir_table_count', '10', 'Jumlah meja kasir (Meja-1..N)');
|
|
ensureKey('kasir_table_combos', '1&5,2&6,3&7,8&9', 'Meja gabungan (pisahkan dengan koma)');
|
|
ensureKey('kasir_include_dump', 'true', 'Tampilkan Meja-dump (true/false)');
|
|
ensureKey('qr_table_count', '10', 'Jumlah meja QR pelanggan (1..N)');
|
|
|
|
ensureKey('poin_earn_rupiah', '10000', 'Perolehan: 1 poin per berapa rupiah');
|
|
ensureKey('poin_redeem_rupiah', '100', 'Redeem: 1 poin = berapa rupiah');
|
|
|
|
ensureKey('wifi_lock', 'true', 'Kunci informasi WiFi di Pelanggan.html sebelum isi Nama & WA');
|
|
ensureKey('wifi_ssid', 'FukuShabuGrill', 'Nama WiFi');
|
|
ensureKey('wifi_password', 'Fusagi17', 'Password WiFi');
|
|
ensureKey('qris_image_url', 'https://lh3.googleusercontent.com/d/1hGPL3AlVGRMeZTzpwOHp-l-JzOG5tS09', 'URL gambar QRIS');
|
|
|
|
ensureKey('drive_bukti_folder_id', '', 'Folder ID Drive untuk upload bukti bayar');
|
|
ensureKey('drive_review_folder_id', '', 'Folder ID Drive untuk upload screenshot review');
|
|
ensureKey('rekap_archive_spreadsheet_id', '', 'Spreadsheet ID tujuan arsip transaksi (RekapTransaksi). Jika kosong, arsip otomatis nonaktif.');
|
|
|
|
ensureKey('telegram_enabled', 'false', 'Aktifkan notifikasi Telegram (true/false)');
|
|
ensureKey('telegram_bot_token', '', 'Telegram Bot Token (jangan dibagikan)');
|
|
ensureKey('telegram_chat_id', '', 'Telegram Chat ID (user/group)');
|
|
ensureKey('telegram_order_enabled', 'false', 'Notifikasi Telegram untuk pesanan masuk');
|
|
ensureKey('telegram_bell_enabled', 'false', 'Notifikasi Telegram untuk bel/panggilan');
|
|
ensureKey('telegram_waiter_enabled', 'false', 'Notifikasi Telegram untuk kasir->pelayan');
|
|
|
|
if (legacy && legacy.getLastRow() > 0) {
|
|
try { legacy.hideSheet(); } catch(e) {}
|
|
}
|
|
|
|
return 'Berhasil: Sheet Menu, Transaksi, Pelanggan, Belanja, Rekap, StokHarian, SupplierHistory, Suppliers, Waiters, Feedback, PaketKustom, Setup siap.';
|
|
}
|
|
|
|
function setRekapArchiveSpreadsheetId(spreadsheetId) {
|
|
var raw = String(spreadsheetId || '').trim();
|
|
if (!raw) {
|
|
try {
|
|
var settings = getSettingsMap_();
|
|
raw = String(settings.rekap_archive_spreadsheet_id || '').trim();
|
|
} catch (e) {}
|
|
}
|
|
var id = normalizeSpreadsheetId_(raw);
|
|
if (!id) throw new Error('Spreadsheet ID kosong.');
|
|
PropertiesService.getScriptProperties().setProperty('rekap_archive_spreadsheet_id', id);
|
|
return { ok: true, spreadsheetId: id };
|
|
}
|
|
|
|
function getRekapArchiveSpreadsheetId_() {
|
|
var props = PropertiesService.getScriptProperties();
|
|
var id = normalizeSpreadsheetId_(String(props.getProperty('rekap_archive_spreadsheet_id') || '').trim());
|
|
if (id) return id;
|
|
try {
|
|
var settings = getSettingsMap_();
|
|
id = normalizeSpreadsheetId_(String(settings.rekap_archive_spreadsheet_id || '').trim());
|
|
} catch (e) {}
|
|
return id;
|
|
}
|
|
|
|
function normalizeSpreadsheetId_(raw) {
|
|
var s = String(raw || '').trim();
|
|
if (!s) return '';
|
|
var m = s.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
|
|
if (m && m[1]) return m[1];
|
|
m = s.match(/[-\w]{25,}/);
|
|
if (m && m[0]) return m[0];
|
|
return '';
|
|
}
|
|
|
|
function exportTransaksiRowToRekapArchive_(row, settings) {
|
|
try {
|
|
var id = normalizeSpreadsheetId_(String((settings && settings.rekap_archive_spreadsheet_id) || getRekapArchiveSpreadsheetId_() || '').trim());
|
|
if (!id) return;
|
|
var ss = SpreadsheetApp.openById(id);
|
|
var sh = ss.getSheetByName('Transaksi') || ss.insertSheet('Transaksi');
|
|
if (sh.getLastRow() < 1) sh.appendRow([]);
|
|
var headers = ['ID','Meja','Status','Nama','WA','Tgl','Jam','Items (JSON)','Subtotal','Diskon','PoinDipakai','PajakPersen','ServicePersen','AdminPersen','Pajak','Service','AdminFee','Total','DP','MetodeDP','TglDP','MetodeBayar','Bayar','Kembali','PoinDapat','Timestamp','PoinAwal','Catatan','BuktiBayar','BuktiReview'];
|
|
if (sh.getLastRow() < 1 || String(sh.getRange(1, 1).getValue() || '') !== 'ID') {
|
|
sh.clearContents();
|
|
sh.getRange(1, 1, 1, headers.length).setValues([headers]);
|
|
} else {
|
|
var curHeaders = sh.getRange(1, 1, 1, headers.length).getValues()[0];
|
|
var okHeader = true;
|
|
for (var i = 0; i < headers.length; i++) {
|
|
if (String(curHeaders[i] || '') !== headers[i]) { okHeader = false; break; }
|
|
}
|
|
if (!okHeader) sh.getRange(1, 1, 1, headers.length).setValues([headers]);
|
|
}
|
|
|
|
var targetId = String(row && row[0] ? row[0] : '');
|
|
if (!targetId) return;
|
|
var lastRow = sh.getLastRow();
|
|
var rowIdx = -1;
|
|
if (lastRow > 1) {
|
|
var finder = sh.getRange(2, 1, lastRow - 1, 1).createTextFinder(targetId).matchEntireCell(true);
|
|
var cell = finder.findNext();
|
|
if (cell) rowIdx = cell.getRow();
|
|
}
|
|
if (rowIdx > -1) sh.getRange(rowIdx, 1, 1, row.length).setValues([row]);
|
|
else sh.appendRow(row);
|
|
} catch (e) {}
|
|
}
|
|
|
|
function testRekapArchiveConnection() {
|
|
try {
|
|
var id = getRekapArchiveSpreadsheetId_();
|
|
if (!id) return { ok: false, error: 'rekap_archive_spreadsheet_id belum diisi.' };
|
|
var ss = SpreadsheetApp.openById(id);
|
|
return { ok: true, spreadsheetId: id, name: ss.getName(), url: ss.getUrl() };
|
|
} catch (e) {
|
|
return { ok: false, error: e.message };
|
|
}
|
|
}
|
|
|
|
function updateSetting(key, value) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Setup') || ss.getSheetByName('Settings');
|
|
if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Setup') || ss.getSheetByName('Settings'); }
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (data[i][0] === key) { rowIdx = i + 1; break; }
|
|
}
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 2).setValue(String(value));
|
|
} else {
|
|
sheet.appendRow([key, String(value), '']);
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function authDigestBase64_(s) {
|
|
var bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, String(s || ''), Utilities.Charset.UTF_8);
|
|
return Utilities.base64Encode(bytes);
|
|
}
|
|
|
|
function authRandomSalt_() {
|
|
var raw = Utilities.getUuid() + ':' + new Date().getTime() + ':' + Math.random();
|
|
return authDigestBase64_(raw).slice(0, 16);
|
|
}
|
|
|
|
function authEnsureDefault_() {
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
|
|
if (!db || !db.users) {
|
|
db = { users: {}, recovery: {} };
|
|
|
|
var legacyUser = props.getProperty('auth_admin_user');
|
|
var legacySalt = props.getProperty('auth_admin_salt');
|
|
var legacyHash = props.getProperty('auth_admin_hash');
|
|
if (legacyUser && legacySalt && legacyHash) {
|
|
db.users[String(legacyUser)] = { role: 'admin', salt: String(legacySalt), hashes: [String(legacyHash)] };
|
|
}
|
|
}
|
|
|
|
var upsertUser = function(username, passwords, role) {
|
|
var u = String(username || '').trim();
|
|
if (!u) return;
|
|
if (!db.users[u]) {
|
|
db.users[u] = { role: String(role || 'admin'), salt: authRandomSalt_(), hashes: [] };
|
|
}
|
|
if (!db.users[u].salt) db.users[u].salt = authRandomSalt_();
|
|
if (!Array.isArray(db.users[u].hashes)) db.users[u].hashes = [];
|
|
if (role) db.users[u].role = String(role);
|
|
(passwords || []).forEach(function(pw) {
|
|
var h = authDigestBase64_(db.users[u].salt + ':' + String(pw || ''));
|
|
if (db.users[u].hashes.indexOf(h) === -1) db.users[u].hashes.push(h);
|
|
});
|
|
};
|
|
|
|
upsertUser('admin', ['11022016', 'admin123'], 'superadmin');
|
|
upsertUser('ridho', ['ridho123'], 'kasir');
|
|
upsertUser('mamah', ['mamah123'], 'kasir');
|
|
Object.keys(db.users || {}).forEach(function(k) {
|
|
var u = db.users[k];
|
|
if (!u) return;
|
|
if (String(k) === 'admin') u.role = 'superadmin';
|
|
else if (!u.role) u.role = 'kasir';
|
|
});
|
|
|
|
if (!db.recovery || !db.recovery.salt || !db.recovery.hash) {
|
|
db.recovery = { salt: authRandomSalt_(), hash: authDigestBase64_(authRandomSalt_() + ':' + '11022016') };
|
|
db.recovery.hash = authDigestBase64_(db.recovery.salt + ':' + '11022016');
|
|
}
|
|
|
|
props.setProperty('auth_users_json', JSON.stringify(db));
|
|
}
|
|
|
|
function loginUser(username, password) {
|
|
authEnsureDefault_();
|
|
var u = String(username || '').trim();
|
|
var p = String(password || '');
|
|
if (!u || !p) return { ok: false };
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users) return { ok: false };
|
|
var user = db.users[u];
|
|
if (!user || !user.salt || !Array.isArray(user.hashes)) return { ok: false };
|
|
var h = authDigestBase64_(String(user.salt) + ':' + p);
|
|
if (user.hashes.indexOf(h) === -1) return { ok: false };
|
|
return { ok: true, role: String(user.role || ''), username: u };
|
|
}
|
|
|
|
function loginAdmin(username, password) {
|
|
authEnsureDefault_();
|
|
var u = String(username || '').trim();
|
|
var p = String(password || '');
|
|
if (!u || !p) return { ok: false };
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users) return { ok: false };
|
|
var user = db.users[u];
|
|
if (!user || !user.salt || !Array.isArray(user.hashes)) return { ok: false };
|
|
var role = String(user.role || '');
|
|
if (role !== 'admin' && role !== 'superadmin') return { ok: false };
|
|
var h = authDigestBase64_(String(user.salt) + ':' + p);
|
|
if (user.hashes.indexOf(h) === -1) return { ok: false };
|
|
return { ok: true, role: role, username: u };
|
|
}
|
|
|
|
function resetAdminWithRecovery(recoveryCode, newUsername, newPassword) {
|
|
authEnsureDefault_();
|
|
var code = String(recoveryCode || '').trim();
|
|
var u = String(newUsername || '').trim();
|
|
var p = String(newPassword || '');
|
|
if (!code || !u || !p) return { ok: false, error: 'Input tidak lengkap.' };
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users) return { ok: false, error: 'Auth belum siap.' };
|
|
|
|
if (!db.recovery || !db.recovery.salt || !db.recovery.hash) return { ok: false, error: 'Recovery belum diset.' };
|
|
var h = authDigestBase64_(String(db.recovery.salt) + ':' + code);
|
|
if (h !== String(db.recovery.hash)) return { ok: false, error: 'Recovery code salah.' };
|
|
|
|
db.users[u] = { role: 'admin', salt: authRandomSalt_(), hashes: [authDigestBase64_(authRandomSalt_() + ':' + p)] };
|
|
db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + p)];
|
|
props.setProperty('auth_users_json', JSON.stringify(db));
|
|
return { ok: true, role: 'admin', username: u };
|
|
}
|
|
|
|
function changeAdminPassword(username, oldPassword, newPassword) {
|
|
authEnsureDefault_();
|
|
var u = String(username || '').trim();
|
|
var oldP = String(oldPassword || '');
|
|
var newP = String(newPassword || '');
|
|
if (!u || !oldP || !newP) return { ok: false, error: 'Input tidak lengkap.' };
|
|
|
|
var ok = loginAdmin(u, oldP);
|
|
if (!ok || !ok.ok) return { ok: false, error: 'Username/password lama salah.' };
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users || !db.users[u]) return { ok: false, error: 'User tidak ditemukan.' };
|
|
db.users[u].salt = authRandomSalt_();
|
|
db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + newP)];
|
|
if (!db.users[u].role) db.users[u].role = 'admin';
|
|
props.setProperty('auth_users_json', JSON.stringify(db));
|
|
return { ok: true, role: String(db.users[u].role || 'admin'), username: u };
|
|
}
|
|
|
|
function changeUserPassword(username, oldPassword, newPassword) {
|
|
authEnsureDefault_();
|
|
var u = String(username || '').trim();
|
|
var oldP = String(oldPassword || '');
|
|
var newP = String(newPassword || '');
|
|
if (!u || !oldP || !newP) return { ok: false, error: 'Input tidak lengkap.' };
|
|
|
|
var ok = loginUser(u, oldP);
|
|
if (!ok || !ok.ok) return { ok: false, error: 'Username/password lama salah.' };
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users || !db.users[u]) return { ok: false, error: 'User tidak ditemukan.' };
|
|
db.users[u].salt = authRandomSalt_();
|
|
db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + newP)];
|
|
if (!db.users[u].role) db.users[u].role = ok.role || '';
|
|
props.setProperty('auth_users_json', JSON.stringify(db));
|
|
return { ok: true, role: String(db.users[u].role || ''), username: u };
|
|
}
|
|
|
|
function resetUserPasswordBySuperAdmin(superUsername, superPassword, targetUsername, newPassword) {
|
|
authEnsureDefault_();
|
|
var su = String(superUsername || '').trim();
|
|
var sp = String(superPassword || '');
|
|
var tu = String(targetUsername || '').trim();
|
|
var np = String(newPassword || '');
|
|
if (!su || !sp || !tu || !np) return { ok: false, error: 'Input tidak lengkap.' };
|
|
|
|
var ok = loginAdmin(su, sp);
|
|
if (!ok || !ok.ok) return { ok: false, error: 'Akses ditolak.' };
|
|
var roleOk = String(ok.role || '');
|
|
if (roleOk !== 'superadmin' && roleOk !== 'admin') return { ok: false, error: 'Akses ditolak.' };
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users || !db.users[tu]) return { ok: false, error: 'User target tidak ditemukan.' };
|
|
var role = String(db.users[tu].role || '');
|
|
db.users[tu].salt = authRandomSalt_();
|
|
db.users[tu].hashes = [authDigestBase64_(db.users[tu].salt + ':' + np)];
|
|
db.users[tu].role = role;
|
|
props.setProperty('auth_users_json', JSON.stringify(db));
|
|
return { ok: true, username: tu, role: role };
|
|
}
|
|
|
|
function listAuthUsers(superUsername, superPassword) {
|
|
authEnsureDefault_();
|
|
var su = String(superUsername || '').trim();
|
|
var sp = String(superPassword || '');
|
|
if (!su || !sp) return { ok: false, error: 'Input tidak lengkap.' };
|
|
var ok = loginAdmin(su, sp);
|
|
if (!ok || !ok.ok) return { ok: false, error: 'Akses ditolak.' };
|
|
var roleOk = String(ok.role || '');
|
|
if (roleOk !== 'superadmin' && roleOk !== 'admin') return { ok: false, error: 'Akses ditolak.' };
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users) return { ok: false, error: 'Auth belum siap.' };
|
|
var users = Object.keys(db.users).sort().map(function(u) {
|
|
return { username: u, role: String((db.users[u] && db.users[u].role) || '') };
|
|
});
|
|
return { ok: true, users: users };
|
|
}
|
|
|
|
function addAuthUser(superUsername, superPassword, username, password, role) {
|
|
authEnsureDefault_();
|
|
var su = String(superUsername || '').trim();
|
|
var sp = String(superPassword || '');
|
|
var u = String(username || '').trim();
|
|
var p = String(password || '');
|
|
var r = String(role || 'kasir').trim();
|
|
if (!su || !sp || !u || !p) return { ok: false, error: 'Input tidak lengkap.' };
|
|
var ok = loginAdmin(su, sp);
|
|
if (!ok || !ok.ok) return { ok: false, error: 'Akses ditolak.' };
|
|
var roleOk = String(ok.role || '');
|
|
if (roleOk !== 'superadmin' && roleOk !== 'admin') return { ok: false, error: 'Akses ditolak.' };
|
|
if (!/^[a-zA-Z0-9_.-]{2,20}$/.test(u)) return { ok: false, error: 'Username tidak valid.' };
|
|
if (r !== 'kasir' && r !== 'admin' && r !== 'superadmin') r = 'kasir';
|
|
if (r === 'superadmin') r = 'admin';
|
|
if (u === 'admin') r = 'superadmin';
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var dbRaw = props.getProperty('auth_users_json');
|
|
var db = null;
|
|
try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
|
|
if (!db || !db.users) return { ok: false, error: 'Auth belum siap.' };
|
|
if (!db.users[u]) db.users[u] = { role: r, salt: authRandomSalt_(), hashes: [] };
|
|
db.users[u].role = r;
|
|
db.users[u].salt = authRandomSalt_();
|
|
db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + p)];
|
|
props.setProperty('auth_users_json', JSON.stringify(db));
|
|
return { ok: true, username: u, role: r };
|
|
}
|
|
|
|
function resetAndSetup() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
if (!ss) throw new Error('Spreadsheet tidak aktif');
|
|
['Menu', 'Transaksi', 'Pelanggan', 'Belanja', 'Rekap', 'StokHarian', 'SupplierHistory', 'Suppliers', 'Waiters', 'Feedback', 'PaketKustom', 'Settings', 'Setup'].forEach(function(n) {
|
|
var sh = ss.getSheetByName(n);
|
|
if (sh) ss.deleteSheet(sh);
|
|
});
|
|
return setupSheets();
|
|
}
|
|
|
|
function setupAman() {
|
|
return setupSheets();
|
|
}
|
|
|
|
function resetTotal() {
|
|
return resetAndSetup();
|
|
}
|
|
|
|
function getInitialData() {
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
if (!ss) throw new Error('Spreadsheet tidak aktif');
|
|
|
|
var lastReset = PropertiesService.getScriptProperties().getProperty('last_stock_reset') || '';
|
|
var sheets = ss.getSheets();
|
|
var sheetData = { lastReset: lastReset };
|
|
|
|
// Ambil semua data sheet sekaligus untuk mengurangi network call
|
|
var targetSheets = ['Menu', 'Transaksi', 'Pelanggan', 'Belanja', 'Rekap', 'SupplierHistory', 'Suppliers', 'Waiters', 'PaketKustom', 'Setup', 'Settings'];
|
|
sheets.forEach(function(s) {
|
|
var name = s.getName();
|
|
if (targetSheets.indexOf(name) > -1) {
|
|
try {
|
|
var lastRow = s.getLastRow();
|
|
var values = [];
|
|
if (lastRow > 1) {
|
|
if (name === 'Transaksi') {
|
|
// Untuk Transaksi, ambil 30 baris terakhir (cukup untuk aktif + history awal)
|
|
var startRow = Math.max(2, lastRow - 29);
|
|
var numRows = lastRow - startRow + 1;
|
|
values = s.getRange(startRow, 1, numRows, s.getLastColumn()).getValues();
|
|
} else if (name === 'Belanja' || name === 'SupplierHistory') {
|
|
// Untuk Belanja dan SupplierHistory, ambil maksimal 100 baris terakhir
|
|
var startRow = Math.max(2, lastRow - 99);
|
|
var numRows = lastRow - startRow + 1;
|
|
values = s.getRange(startRow, 1, numRows, s.getLastColumn()).getValues();
|
|
} else {
|
|
values = s.getRange(2, 1, lastRow - 1, s.getLastColumn()).getValues();
|
|
}
|
|
}
|
|
sheetData[name] = values;
|
|
} catch (e) {
|
|
sheetData[name] = [];
|
|
}
|
|
}
|
|
});
|
|
|
|
if (!sheetData['Menu']) {
|
|
return { error: "Sheet Menu tidak ditemukan. Klik SETUP AMAN." };
|
|
}
|
|
|
|
var settings = {};
|
|
(sheetData['Settings'] || []).forEach(function(r) {
|
|
if (r[0]) settings[String(r[0])] = String(r[1]);
|
|
});
|
|
(sheetData['Setup'] || []).forEach(function(r) {
|
|
if (r[0]) settings[String(r[0])] = String(r[1]);
|
|
});
|
|
|
|
var sheetMenuForHeader = ss.getSheetByName('Menu');
|
|
var menuHeaderMap = getHeaderIndexMap_(sheetMenuForHeader);
|
|
var sheetPaketForHeader = ss.getSheetByName('PaketKustom');
|
|
var paketHeaderMap = getHeaderIndexMap_(sheetPaketForHeader);
|
|
var sheetTransForHeader = ss.getSheetByName('Transaksi');
|
|
var transHeaderMap = getHeaderIndexMap_(sheetTransForHeader);
|
|
|
|
var menu = (sheetData['Menu'] || []).map(function(r) {
|
|
var idxNama = (menuHeaderMap['nama'] != null) ? menuHeaderMap['nama'] : 0;
|
|
var idxHarga = (menuHeaderMap['harga'] != null) ? menuHeaderMap['harga'] : 1;
|
|
var idxKategori = (menuHeaderMap['kategori'] != null) ? menuHeaderMap['kategori'] : 2;
|
|
var idxStatus = (menuHeaderMap['status'] != null) ? menuHeaderMap['status'] : 3;
|
|
var idxGambar = (menuHeaderMap['gambar url'] != null) ? menuHeaderMap['gambar url'] : (menuHeaderMap['gambar'] != null ? menuHeaderMap['gambar'] : 4);
|
|
var idxStok = (menuHeaderMap['stok'] != null) ? menuHeaderMap['stok'] : 5;
|
|
var idxTampil = (menuHeaderMap['tampildapur'] != null) ? menuHeaderMap['tampildapur'] : (menuHeaderMap['tampil dapur'] != null ? menuHeaderMap['tampil dapur'] : 6);
|
|
var idxModal = (menuHeaderMap['modal'] != null) ? menuHeaderMap['modal'] : -1;
|
|
|
|
var status = String((idxStatus != null ? r[idxStatus] : r[3]) || '').toLowerCase().trim();
|
|
var tampilDapur = String((idxTampil != null ? r[idxTampil] : r[6]) || '').toLowerCase().trim();
|
|
if (status !== 'aktif') return null;
|
|
return {
|
|
nama: String(r[idxNama] || ''),
|
|
harga: parseNumberFlexible(r[idxHarga]),
|
|
kategori: String(r[idxKategori] || ''),
|
|
gambar: normalizeImageUrl(r[idxGambar]),
|
|
stok: Number(r[idxStok]) || 0,
|
|
modal: idxModal > -1 ? parseNumberFlexible(r[idxModal]) : 0,
|
|
tampilDapur: tampilDapur // Tambahkan info ini agar frontend bisa filter
|
|
};
|
|
}).filter(function(m) { return m !== null; });
|
|
|
|
var transaksi = (sheetData['Transaksi'] || []).map(function(r) {
|
|
var items = []; try { items = JSON.parse(r[7] || '[]'); } catch(e) {}
|
|
var ppIdx = transHeaderMap['potensiprofit'];
|
|
return {
|
|
id: String(r[0]), meja: String(r[1]), status: String(r[2]), nama: String(r[3]), wa: String(r[4]),
|
|
tgl: parseDate(r[5]), jam: parseDate(r[6]), items: items, subtotal: Number(r[8]), diskon: Number(r[9]),
|
|
poinDipakai: Number(r[10]), pajakPersen: Number(r[11]), servicePersen: Number(r[12]), adminPersen: Number(r[13]),
|
|
pajak: Number(r[14]), service: Number(r[15]), adminFee: Number(r[16]), total: Number(r[17]),
|
|
dp: Number(r[18]), metodeDp: String(r[19]), tglDp: parseDate(r[20]), metodeBayar: String(r[21]),
|
|
bayar: Number(r[22]), kembali: Number(r[23]), poinDapat: Number(r[24]), timestamp: parseDate(r[25]),
|
|
poinAwal: Number(r[26] || 0), catatan: String(r[27] || ''),
|
|
buktiBayar: String(r[28] || ''), buktiReview: String(r[29] || ''),
|
|
potensiProfit: (ppIdx != null ? (Number(r[ppIdx]) || 0) : (Number(r[30]) || 0))
|
|
};
|
|
});
|
|
|
|
var pelanggan = (sheetData['Pelanggan'] || []).map(function(r) {
|
|
return { nama: String(r[0]), wa: String(r[1]), poin: Number(r[2]), updatedAt: parseDate(r[3]) };
|
|
});
|
|
|
|
var belanja = (sheetData['Belanja'] || []).map(function(r) {
|
|
return { id: String(r[0]), nama: String(r[1]), harga: Number(r[2]), qty: Number(r[3]), total: Number(r[4]), tgl: parseDate(r[5]), kategori: String(r[6]), catatan: String(r[7]), timestamp: parseDate(r[8]) };
|
|
});
|
|
|
|
var rekap = (sheetData['Rekap'] || []).map(function(r) {
|
|
return { id: String(r[0]), tgl: parseDate(r[1]), saldoAwal: Number(r[2]), tunai: Number(r[3]), qris: Number(r[4]), debit: Number(r[5]), credit: Number(r[6]), transfer: Number(r[7]), catatan: String(r[8]), timestamp: parseDate(r[9]) };
|
|
});
|
|
|
|
var supplierHistory = (sheetData['SupplierHistory'] || []).map(function(r) {
|
|
return { id: String(r[0]), nama: String(r[1]), kategori: String(r[2]), wa: String(r[3]), pesan: String(r[4]), timestamp: parseDate(r[5]) };
|
|
});
|
|
|
|
var suppliersMaster = (sheetData['Suppliers'] || []).map(function(r) {
|
|
var rekening = '';
|
|
var ts = null;
|
|
if (r.length >= 5) {
|
|
rekening = String(r[3] || '');
|
|
ts = parseDate(r[4]);
|
|
} else {
|
|
rekening = '';
|
|
ts = parseDate(r[3]);
|
|
}
|
|
return { id: String(r[0]), nama: String(r[1]), wa: String(r[2]), rekening: rekening, timestamp: ts };
|
|
});
|
|
|
|
var waiters = (sheetData['Waiters'] || []).map(function(r) {
|
|
return { nama: String(r[0]), wa: String(r[1]), status: String(r[2]), timestamp: parseDate(r[3]) };
|
|
});
|
|
|
|
var paketKustom = (sheetData['PaketKustom'] || []).map(function(r) {
|
|
var idxId = (paketHeaderMap['id'] != null) ? paketHeaderMap['id'] : 0;
|
|
var idxNama = (paketHeaderMap['nama paket'] != null) ? paketHeaderMap['nama paket'] : (paketHeaderMap['nama'] != null ? paketHeaderMap['nama'] : 1);
|
|
var idxItems = (paketHeaderMap['items (json)'] != null) ? paketHeaderMap['items (json)'] : 2;
|
|
var idxTotal = (paketHeaderMap['total harga'] != null) ? paketHeaderMap['total harga'] : (paketHeaderMap['total'] != null ? paketHeaderMap['total'] : 3);
|
|
var idxTimestamp = (paketHeaderMap['timestamp'] != null) ? paketHeaderMap['timestamp'] : 4;
|
|
var idxGambar = (paketHeaderMap['gambar url'] != null) ? paketHeaderMap['gambar url'] : (paketHeaderMap['gambar'] != null ? paketHeaderMap['gambar'] : 5);
|
|
var idxAktif = (paketHeaderMap['aktif'] != null) ? paketHeaderMap['aktif'] : 6;
|
|
var idxModal = paketHeaderMap['modal'];
|
|
|
|
var items = []; try { items = JSON.parse(r[idxItems] || '[]'); } catch(e) {}
|
|
var isAktif = (r[idxAktif] === true || String(r[idxAktif]).toLowerCase() === 'aktif' || String(r[idxAktif]).toLowerCase() === 'true');
|
|
return { id: String(r[idxId]), nama: String(r[idxNama]), items: items, total: Number(r[idxTotal]), modal: (idxModal != null ? parseNumberFlexible(r[idxModal]) : 0), timestamp: parseDate(r[idxTimestamp]), gambar: normalizeImageUrl(r[idxGambar]), aktif: isAktif };
|
|
});
|
|
|
|
var scriptUrl = '';
|
|
try { scriptUrl = ScriptApp.getService().getUrl(); } catch(e) {}
|
|
|
|
return { menu: menu, transaksi: transaksi, pelanggan: pelanggan, belanja: belanja, rekap: rekap, supplierHistory: supplierHistory, suppliersMaster: suppliersMaster, waiters: waiters, paketKustom: paketKustom, settings: settings, lastReset: sheetData.lastReset, scriptUrl: scriptUrl };
|
|
} catch (e) {
|
|
return { error: e.message };
|
|
}
|
|
}
|
|
|
|
function getActiveTransaksiLite() {
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetTrans = ss.getSheetByName('Transaksi');
|
|
if (!sheetTrans) return { transaksi: [] };
|
|
var lastRow = sheetTrans.getLastRow();
|
|
if (lastRow <= 1) return { transaksi: [] };
|
|
var startRow = Math.max(2, lastRow - 199);
|
|
var numRows = lastRow - startRow + 1;
|
|
var values = sheetTrans.getRange(startRow, 1, numRows, sheetTrans.getLastColumn()).getValues();
|
|
var list = values.map(function(r) {
|
|
var items = []; try { items = JSON.parse(r[7] || '[]'); } catch(e) {}
|
|
return {
|
|
id: String(r[0]), meja: String(r[1]), status: String(r[2]), nama: String(r[3]), wa: String(r[4]),
|
|
tgl: parseDate(r[5]), jam: parseDate(r[6]), items: items, subtotal: Number(r[8]), diskon: Number(r[9]),
|
|
poinDipakai: Number(r[10]), pajakPersen: Number(r[11]), servicePersen: Number(r[12]), adminPersen: Number(r[13]),
|
|
pajak: Number(r[14]), service: Number(r[15]), adminFee: Number(r[16]), total: Number(r[17]),
|
|
dp: Number(r[18]), metodeDp: String(r[19]), tglDp: parseDate(r[20]), metodeBayar: String(r[21]),
|
|
bayar: Number(r[22]), kembali: Number(r[23]), poinDapat: Number(r[24]), timestamp: parseDate(r[25]),
|
|
poinAwal: Number(r[26] || 0), catatan: String(r[27] || ''),
|
|
buktiBayar: String(r[28] || ''), buktiReview: String(r[29] || '')
|
|
};
|
|
}).filter(function(t) {
|
|
return t.status === 'Pending' || t.status === 'Ready' || t.status === 'Booking' || t.status === 'Selesai' || t.status === 'Void';
|
|
});
|
|
return { transaksi: list };
|
|
} catch (e) {
|
|
return { error: e.message, transaksi: [] };
|
|
}
|
|
}
|
|
|
|
function saveCustomPaket(payload) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('PaketKustom');
|
|
if (!sheet) { setupSheets(); sheet = ss.getSheetByName('PaketKustom'); }
|
|
var now = new Date();
|
|
var id = 'PK-' + Date.now();
|
|
|
|
var row = [
|
|
id,
|
|
payload.nama || 'Paket Kustom',
|
|
JSON.stringify(payload.items || []),
|
|
Number(payload.total) || 0,
|
|
now,
|
|
'', // gambar
|
|
false // aktif
|
|
];
|
|
|
|
sheet.appendRow(row);
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function deleteCustomPaket(id) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('PaketKustom');
|
|
if (!sheet) return getInitialData(); // Jika sheet tidak ada, anggap data kosong
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
if (rowIdx > -1) {
|
|
sheet.deleteRow(rowIdx);
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function toggleCustomPaketStatus(id, isActive) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('PaketKustom');
|
|
if (!sheet) return getInitialData();
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 7).setValue(isActive); // Kolom G adalah 'Aktif'
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function updateCustomPaketName(id, newName) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('PaketKustom');
|
|
if (!sheet) return getInitialData();
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 2).setValue(newName); // Kolom B adalah 'Nama Paket'
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function uploadPaketLogo(id, base64Data, mimeType, fileName) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('PaketKustom');
|
|
if (!sheet) throw new Error('Sheet PaketKustom tidak ditemukan.');
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
if (rowIdx === -1) throw new Error('Paket tidak ditemukan.');
|
|
|
|
var folderId = PropertiesService.getScriptProperties().getProperty('folder_paket_logo');
|
|
var folder;
|
|
if (folderId) {
|
|
try {
|
|
folder = DriveApp.getFolderById(folderId);
|
|
} catch(e) {}
|
|
}
|
|
if (!folder) {
|
|
var folders = DriveApp.getFoldersByName('POSFuku_PaketLogo');
|
|
if (folders.hasNext()) {
|
|
folder = folders.next();
|
|
} else {
|
|
folder = DriveApp.createFolder('POSFuku_PaketLogo');
|
|
folder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
|
|
}
|
|
PropertiesService.getScriptProperties().setProperty('folder_paket_logo', folder.getId());
|
|
}
|
|
|
|
var blob = Utilities.newBlob(Utilities.base64Decode(base64Data), mimeType, fileName);
|
|
var file = folder.createFile(blob);
|
|
var url = file.getUrl();
|
|
|
|
sheet.getRange(rowIdx, 6).setValue(url); // Kolom F adalah 'Gambar'
|
|
|
|
return getInitialData();
|
|
} catch (e) {
|
|
return { error: e.message };
|
|
} finally {
|
|
lock.releaseLock();
|
|
}
|
|
}
|
|
|
|
function generateReceiptPdfFromHtml(html, fileName) {
|
|
try {
|
|
var safeName = String(fileName || 'Struk.pdf').replace(/[\\\/\:\*\?\"\<\>\|]/g, '_');
|
|
if (!safeName.toLowerCase().endsWith('.pdf')) safeName += '.pdf';
|
|
var blob = HtmlService.createHtmlOutput(String(html || '')).getAs(MimeType.PDF).setName(safeName);
|
|
var b64 = Utilities.base64Encode(blob.getBytes());
|
|
return { fileName: safeName, base64: b64 };
|
|
} catch (e) {
|
|
return { error: e.message };
|
|
}
|
|
}
|
|
|
|
function saveTransaction(payload) {
|
|
var lock = LockService.getScriptLock();
|
|
try {
|
|
lock.waitLock(30000);
|
|
} catch (e) {
|
|
throw new Error('Server sibuk. Coba lagi.');
|
|
}
|
|
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetTrans = ss.getSheetByName('Transaksi');
|
|
var sheetMenu = ss.getSheetByName('Menu');
|
|
var sheetCust = ss.getSheetByName('Pelanggan');
|
|
if (!sheetTrans || !sheetMenu) throw new Error('Sheet belum siap. Klik SETUP DATABASE.');
|
|
|
|
var now = new Date();
|
|
|
|
var normalizeWA = function(wa) {
|
|
var clean = String(wa || '').replace(/\D/g, '');
|
|
if (!clean) return '';
|
|
if (clean.indexOf('0') === 0) clean = '62' + clean.slice(1);
|
|
else if (clean.indexOf('8') === 0) clean = '62' + clean;
|
|
return clean;
|
|
};
|
|
payload.wa = normalizeWA(payload.wa);
|
|
var settings = getSettingsMap_();
|
|
|
|
var toDateObj = function(val) {
|
|
if (!val) return '';
|
|
if (val instanceof Date) return val;
|
|
var s = String(val);
|
|
if (s.includes('T') || s.includes('-')) {
|
|
var d = new Date(s);
|
|
return isNaN(d) ? '' : d;
|
|
}
|
|
return '';
|
|
};
|
|
|
|
// Optimasi: Hanya ambil data yang diperlukan (300 baris terakhir untuk pencarian Pending)
|
|
var totalRows = sheetTrans.getLastRow();
|
|
var rowsToRead = Math.min(totalRows, 500); // Batasi pencarian baris ke 500 terakhir
|
|
var startRow = Math.max(1, totalRows - rowsToRead + 1);
|
|
var rows = sheetTrans.getRange(startRow, 1, rowsToRead, sheetTrans.getLastColumn()).getValues();
|
|
|
|
var findRowById = function(id) {
|
|
for (var i = 0; i < rows.length; i++) {
|
|
if (String(rows[i][0]) === String(id)) return startRow + i;
|
|
}
|
|
return -1;
|
|
};
|
|
|
|
var rowToUpdate = findRowById(payload.id);
|
|
var old = null;
|
|
if (rowToUpdate > -1) {
|
|
// Index di array `rows` adalah rowToUpdate - startRow
|
|
old = rows[rowToUpdate - startRow];
|
|
}
|
|
|
|
if (payload.status === 'Pending' && rowToUpdate === -1) {
|
|
for (var ip = 0; ip < rows.length; ip++) {
|
|
if (String(rows[ip][1]) === String(payload.meja) && String(rows[ip][2]) === 'Pending') {
|
|
rowToUpdate = startRow + ip;
|
|
payload.id = String(rows[ip][0]);
|
|
old = rows[ip];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
// Auto-generate name if empty (preserve old name if updating existing transaksi)
|
|
if (!payload.nama || !String(payload.nama).trim()) {
|
|
if (old && old[3] && String(old[3]).trim()) {
|
|
payload.nama = String(old[3]).trim();
|
|
} else {
|
|
var maxFound = 0;
|
|
var scanNames = function(values) {
|
|
(values || []).forEach(function(r) {
|
|
var n = String((Array.isArray(r) ? r[0] : r) || '');
|
|
if (n.indexOf('an-') === 0) {
|
|
var num = parseInt(n.replace('an-', ''), 10);
|
|
if (!isNaN(num) && num > maxFound) maxFound = num;
|
|
} else if (n.indexOf('Pelanggan-') === 0) {
|
|
var num2 = parseInt(n.replace('Pelanggan-', ''), 10);
|
|
if (!isNaN(num2) && num2 > maxFound) maxFound = num2;
|
|
}
|
|
});
|
|
};
|
|
|
|
var transLastRow = sheetTrans.getLastRow();
|
|
var batchSize = 5000;
|
|
if (transLastRow > 1) {
|
|
for (var rStart = 2; rStart <= transLastRow; rStart += batchSize) {
|
|
var count = Math.min(batchSize, transLastRow - rStart + 1);
|
|
var transNames = sheetTrans.getRange(rStart, 4, count, 1).getValues();
|
|
scanNames(transNames);
|
|
}
|
|
}
|
|
|
|
var nextNum = maxFound + 1;
|
|
payload.nama = 'an-' + nextNum;
|
|
}
|
|
}
|
|
|
|
// Kapitalisasi Nama Pelanggan (Title Case)
|
|
if (payload.nama && String(payload.nama).indexOf('an-') !== 0) {
|
|
payload.nama = String(payload.nama).split(' ').map(function(word) {
|
|
return word.charAt(0).toUpperCase() + word.slice(1).toLowerCase();
|
|
}).join(' ');
|
|
}
|
|
|
|
var items = Array.isArray(payload.items) ? payload.items : [];
|
|
if (payload.status === 'Pending' && old) {
|
|
try {
|
|
var oldItems = JSON.parse(old[7] || '[]');
|
|
var newCart = items; // Flat list from UI
|
|
|
|
var oldMap = {};
|
|
oldItems.forEach(function(it) {
|
|
var k = (it.nama || '').trim();
|
|
oldMap[k] = (oldMap[k] || 0) + (Number(it.qty) || 0);
|
|
});
|
|
|
|
var finalItems = [];
|
|
newCart.forEach(function(it) {
|
|
var k = (it.nama || '').trim();
|
|
var oldQty = oldMap[k] || 0;
|
|
var newQty = Number(it.qty) || 0;
|
|
|
|
if (oldQty > 0) {
|
|
var existingQty = Math.min(oldQty, newQty);
|
|
finalItems.push({ nama: it.nama, harga: it.harga, qty: existingQty, type: 'Eks' });
|
|
if (newQty > oldQty) {
|
|
finalItems.push({ nama: it.nama, harga: it.harga, qty: newQty - oldQty, type: 'Tam' });
|
|
}
|
|
} else {
|
|
finalItems.push({ nama: it.nama, harga: it.harga, qty: newQty, type: 'Tam' });
|
|
}
|
|
});
|
|
items = finalItems;
|
|
} catch (e) {
|
|
items = payload.items;
|
|
}
|
|
// Jika payload.dp tidak dikirim (undefined/null), baru ambil dari data lama
|
|
if (payload.dp === undefined || payload.dp === null) {
|
|
payload.dp = Number(old[18]) || 0;
|
|
payload.metodeDp = String(old[19] || '');
|
|
payload.tglDp = old[20] ? old[20] : '';
|
|
}
|
|
}
|
|
|
|
var subtotal = items.reduce(function(acc, it) { return acc + (Number(it.qty) || 0) * (Number(it.harga) || 0); }, 0);
|
|
var diskon = Number(payload.diskon) || 0;
|
|
var poinDipakai = Number(payload.poinDipakai) || 0;
|
|
var base = Math.max(0, subtotal - diskon - poinDipakai);
|
|
var pajakPersen = Number(payload.pajakPersen) || 0;
|
|
var servicePersen = Number(payload.servicePersen) || 0;
|
|
var adminPersen = Number(payload.adminPersen) || 0;
|
|
|
|
// Gunakan nilai dari payload jika tersedia, jika tidak baru kalkulasi ulang
|
|
var service = (payload.service !== undefined) ? Number(payload.service) : Math.floor(base * (servicePersen / 100));
|
|
var pajakBase = base + service;
|
|
var pajak = (payload.pajak !== undefined) ? Number(payload.pajak) : Math.floor(pajakBase * (pajakPersen / 100));
|
|
var totalSebelumAdmin = base + service + pajak;
|
|
var adminFee = (payload.adminFee !== undefined) ? Number(payload.adminFee) : Math.floor(totalSebelumAdmin * (adminPersen / 100));
|
|
var totalAkhir = (payload.total !== undefined) ? Number(payload.total) : (totalSebelumAdmin + adminFee);
|
|
|
|
var dp = Number(payload.dp) || 0;
|
|
var bayar = Number(payload.bayar) || 0;
|
|
var kembali = (dp + bayar) - totalAkhir;
|
|
if (kembali < 0) kembali = 0;
|
|
|
|
// Hitung Poin (Divisor: 10.000, Redeem: 1.000)
|
|
var poinDapat = 0;
|
|
var curPoin = 0;
|
|
|
|
var menuDelta = [];
|
|
if (payload.status === 'Selesai') {
|
|
var oldStatus = old ? String(old[2] || '') : '';
|
|
if (oldStatus !== 'Selesai') {
|
|
var menuRange = sheetMenu.getRange(2, 1, sheetMenu.getLastRow() - 1, 6);
|
|
var menuData = menuRange.getValues();
|
|
var menuChanged = false;
|
|
|
|
items.forEach(function(it) {
|
|
for (var m = 0; m < menuData.length; m++) {
|
|
if (String(menuData[m][0]) === String(it.nama)) {
|
|
var currentStok = Number(menuData[m][5]) || 0;
|
|
var buyQty = Number(it.qty) || 0;
|
|
menuData[m][5] = Math.max(0, currentStok - buyQty);
|
|
menuDelta.push({ nama: String(it.nama), stok: Number(menuData[m][5]) || 0 });
|
|
menuChanged = true;
|
|
break;
|
|
}
|
|
}
|
|
});
|
|
|
|
// Simpan SEMUA stok menu sekaligus (Bukan satu per satu)
|
|
if (menuChanged) {
|
|
menuRange.setValues(menuData);
|
|
}
|
|
}
|
|
}
|
|
|
|
var pelangganDelta = null;
|
|
if (sheetCust && payload.wa && payload.status === 'Selesai') {
|
|
var earnDiv = parseNumberFlexible(settings.poin_earn_rupiah);
|
|
if (!earnDiv || earnDiv < 1) earnDiv = 10000;
|
|
var redeemRp = parseNumberFlexible(settings.poin_redeem_rupiah);
|
|
if (!redeemRp || redeemRp < 1) redeemRp = 100;
|
|
poinDapat = Math.floor(Math.max(0, base) / earnDiv);
|
|
var wa = String(payload.wa);
|
|
var custData = sheetCust.getDataRange().getValues();
|
|
var foundIdx = -1;
|
|
for (var c = 1; c < custData.length; c++) {
|
|
if (String(custData[c][1]) === wa) { foundIdx = c + 1; break; }
|
|
}
|
|
|
|
var poinUsed = Math.floor(poinDipakai / redeemRp);
|
|
if (foundIdx > -1) {
|
|
curPoin = Number(custData[foundIdx - 1][2]) || 0;
|
|
var newP = Math.max(0, curPoin - poinUsed + poinDapat);
|
|
sheetCust.getRange(foundIdx, 1, 1, 4).setValues([[payload.nama || custData[foundIdx - 1][0], wa, newP, now]]);
|
|
pelangganDelta = { nama: String(payload.nama || custData[foundIdx - 1][0] || ''), wa: String(wa), poin: Number(newP) || 0, updatedAt: now };
|
|
} else {
|
|
curPoin = 0;
|
|
sheetCust.appendRow([payload.nama || '', wa, Math.max(0, poinDapat - poinUsed), now]);
|
|
pelangganDelta = { nama: String(payload.nama || ''), wa: String(wa), poin: Number(Math.max(0, poinDapat - poinUsed)) || 0, updatedAt: now };
|
|
}
|
|
}
|
|
|
|
var ppCol = ensureColumnExists_(sheetTrans, 'PotensiProfit');
|
|
var potensiProfit = 0;
|
|
try {
|
|
potensiProfit = calcPotensiProfitFromItems_(ss, items);
|
|
} catch (ePp) {
|
|
potensiProfit = 0;
|
|
}
|
|
|
|
var row = [
|
|
payload.id,
|
|
payload.meja,
|
|
payload.status,
|
|
payload.nama || '',
|
|
payload.wa || '',
|
|
toDateObj(payload.tgl) || now,
|
|
payload.jam ? payload.jam : '',
|
|
JSON.stringify(items),
|
|
subtotal,
|
|
diskon,
|
|
poinDipakai,
|
|
pajakPersen,
|
|
servicePersen,
|
|
adminPersen,
|
|
pajak,
|
|
service,
|
|
adminFee,
|
|
totalAkhir,
|
|
dp,
|
|
payload.metodeDp || '',
|
|
toDateObj(payload.tglDp) || '',
|
|
payload.metodeBayar || '',
|
|
bayar,
|
|
kembali,
|
|
poinDapat,
|
|
now,
|
|
curPoin,
|
|
payload.catatan || '',
|
|
payload.buktiBayar || (old ? (old[28] || '') : ''),
|
|
payload.buktiReview || (old ? (old[29] || '') : '')
|
|
];
|
|
|
|
if (rowToUpdate > -1) {
|
|
sheetTrans.getRange(rowToUpdate, 1, 1, row.length).setValues([row]);
|
|
if (ppCol > 0) sheetTrans.getRange(rowToUpdate, ppCol).setValue(potensiProfit);
|
|
} else {
|
|
sheetTrans.appendRow(row);
|
|
if (ppCol > 0) sheetTrans.getRange(sheetTrans.getLastRow(), ppCol).setValue(potensiProfit);
|
|
}
|
|
|
|
try {
|
|
var st = String(payload.status || '');
|
|
if (st === 'Selesai' || st === 'Void') exportTransaksiRowToRekapArchive_(row, settings);
|
|
} catch (eArch) {}
|
|
|
|
try {
|
|
var muted = /x123/i.test(String(payload.nama || ''));
|
|
if (!muted && String(payload.status || '') === 'Pending' && String(settings.telegram_order_enabled || '').toLowerCase() === 'true') {
|
|
var oldStatus = old ? String(old[2] || '') : '';
|
|
var isNewPending = !old || oldStatus !== 'Pending';
|
|
var hasTam = false;
|
|
try {
|
|
hasTam = items.some(function(it) { return String(it.type || '') === 'Tam' && (Number(it.qty) || 0) > 0; });
|
|
} catch (eTam) {}
|
|
if (isNewPending || hasTam) {
|
|
var meja = String(payload.meja || '');
|
|
var nama = String(payload.nama || '');
|
|
var lines = items.map(function(it) { return 'x' + (Number(it.qty) || 0) + ' ' + String(it.nama || ''); }).join('\n');
|
|
var text = 'PESANAN MASUK\nMeja: ' + meja + '\nNama: ' + nama + (lines ? '\n\n' + lines : '');
|
|
sendTelegram_(text);
|
|
}
|
|
}
|
|
} catch (eNotify) {}
|
|
|
|
var isLite = !!(payload && (payload.__lite === true || payload.lite === true));
|
|
if (isLite) {
|
|
var trx = {
|
|
id: String(payload.id),
|
|
meja: String(payload.meja),
|
|
status: String(payload.status),
|
|
nama: String(payload.nama || ''),
|
|
wa: String(payload.wa || ''),
|
|
tgl: toDateObj(payload.tgl) || now,
|
|
jam: payload.jam ? payload.jam : '',
|
|
items: items,
|
|
subtotal: Number(subtotal) || 0,
|
|
diskon: Number(diskon) || 0,
|
|
poinDipakai: Number(poinDipakai) || 0,
|
|
pajakPersen: Number(pajakPersen) || 0,
|
|
servicePersen: Number(servicePersen) || 0,
|
|
adminPersen: Number(adminPersen) || 0,
|
|
pajak: Number(pajak) || 0,
|
|
service: Number(service) || 0,
|
|
adminFee: Number(adminFee) || 0,
|
|
total: Number(totalAkhir) || 0,
|
|
dp: Number(dp) || 0,
|
|
metodeDp: String(payload.metodeDp || ''),
|
|
tglDp: toDateObj(payload.tglDp) || '',
|
|
metodeBayar: String(payload.metodeBayar || ''),
|
|
bayar: Number(bayar) || 0,
|
|
kembali: Number(kembali) || 0,
|
|
poinDapat: Number(poinDapat) || 0,
|
|
timestamp: now,
|
|
poinAwal: Number(curPoin) || 0,
|
|
catatan: String(payload.catatan || ''),
|
|
buktiBayar: String(payload.buktiBayar || (old ? (old[28] || '') : '')),
|
|
buktiReview: String(payload.buktiReview || (old ? (old[29] || '') : ''))
|
|
};
|
|
var scriptUrl = '';
|
|
try { scriptUrl = ScriptApp.getService().getUrl(); } catch(e) {}
|
|
var lastResetLite = PropertiesService.getScriptProperties().getProperty('last_stock_reset') || '';
|
|
return { ok: true, lite: true, transaksiDelta: [trx], menuDelta: menuDelta, pelangganDelta: pelangganDelta, lastReset: lastResetLite, scriptUrl: scriptUrl };
|
|
}
|
|
|
|
return getInitialData();
|
|
} finally {
|
|
lock.releaseLock();
|
|
}
|
|
}
|
|
|
|
function saveCustomerInfo(payload) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetCust = ss.getSheetByName('Pelanggan');
|
|
if (!sheetCust) { setupSheets(); sheetCust = ss.getSheetByName('Pelanggan'); }
|
|
if (!sheetCust) throw new Error('Sheet Pelanggan belum siap.');
|
|
|
|
var now = new Date();
|
|
var rawName = payload && payload.nama ? String(payload.nama).trim() : '';
|
|
var rawWa = payload && payload.wa ? String(payload.wa).trim() : '';
|
|
if (!rawWa) throw new Error('Nomor WhatsApp kosong.');
|
|
|
|
var normalizeWA = function(wa) {
|
|
var clean = String(wa || '').replace(/\D/g, '');
|
|
if (!clean) return '';
|
|
if (clean.indexOf('0') === 0) clean = '62' + clean.slice(1);
|
|
else if (clean.indexOf('8') === 0) clean = '62' + clean;
|
|
return clean;
|
|
};
|
|
|
|
var wa = normalizeWA(rawWa);
|
|
if (!wa || wa.length < 10) throw new Error('Nomor WhatsApp tidak valid.');
|
|
|
|
var nama = rawName;
|
|
if (nama) {
|
|
nama = nama.split(' ').filter(function(w) { return w; }).map(function(word) {
|
|
return word.charAt(0).toUpperCase() + word.slice(1).toLowerCase();
|
|
}).join(' ');
|
|
}
|
|
|
|
var lastRow = sheetCust.getLastRow();
|
|
var data = lastRow > 1 ? sheetCust.getRange(2, 1, lastRow - 1, 4).getValues() : [];
|
|
var foundRow = -1;
|
|
var foundPoin = 0;
|
|
for (var i = 0; i < data.length; i++) {
|
|
var rowWa = normalizeWA(data[i][1]);
|
|
if (rowWa && rowWa === wa) {
|
|
foundRow = i + 2;
|
|
foundPoin = Number(data[i][2]) || 0;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (foundRow > -1) {
|
|
var existingNama = String(data[foundRow - 2][0] || '').trim();
|
|
var finalNama = nama || existingNama;
|
|
sheetCust.getRange(foundRow, 1, 1, 4).setValues([[finalNama, wa, foundPoin, now]]);
|
|
} else {
|
|
sheetCust.appendRow([nama || '', wa, 0, now]);
|
|
}
|
|
|
|
return { success: true };
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function updatePaymentMethod(id, newMethod) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow < 2) throw new Error('Tidak ada data.');
|
|
|
|
var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < ids.length; i++) {
|
|
if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 22).setValue(String(newMethod)); // Kolom V adalah 'Metode Bayar'
|
|
return getInitialData();
|
|
} else {
|
|
throw new Error('Pesanan tidak ditemukan.');
|
|
}
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function updateTransactionPhoto(id, photoUrl) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow < 2) throw new Error('Tidak ada data.');
|
|
|
|
var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < ids.length; i++) {
|
|
if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 29).setValue(String(photoUrl)); // Kolom AC adalah 'Bukti Bayar'
|
|
return getInitialData();
|
|
} else {
|
|
throw new Error('Pesanan tidak ditemukan.');
|
|
}
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function updateKitchenStatus(id, newStatus) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow < 2) throw new Error('Tidak ada data.');
|
|
|
|
var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < ids.length; i++) {
|
|
if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 3).setValue(newStatus); // Update status di kolom C
|
|
return getInitialData();
|
|
} else {
|
|
throw new Error('Pesanan tidak ditemukan.');
|
|
}
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function importTransactions(data) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
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 untuk kolom Tanggal (Index 5), Tgl DP (Index 20), dan Timestamp (Index 25)
|
|
var row = t.map(function(val, idx) {
|
|
if ((idx === 5 || idx === 20 || idx === 25) && val) {
|
|
try {
|
|
var d = new Date(val);
|
|
if (!isNaN(d.getTime())) return d;
|
|
} catch(e) {}
|
|
}
|
|
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 getInitialData();
|
|
} finally {
|
|
lock.releaseLock();
|
|
}
|
|
}
|
|
|
|
function importBelanjaBulk(data) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
|
|
data.forEach(function(b) {
|
|
var id = 'B-' + Date.now() + Math.floor(Math.random() * 1000);
|
|
var row = [
|
|
id,
|
|
b.nama || '',
|
|
Number(b.harga) || 0,
|
|
Number(b.qty) || 0,
|
|
Number(b.total) || 0,
|
|
b.tgl || Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd"),
|
|
b.kategori || b.kat || 'Dapur',
|
|
b.catatan || '',
|
|
new Date()
|
|
];
|
|
sheet.appendRow(row);
|
|
});
|
|
return getInitialData();
|
|
} finally {
|
|
lock.releaseLock();
|
|
}
|
|
}
|
|
|
|
function saveBelanjaBulk(payloads) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
var now = new Date();
|
|
|
|
payloads.forEach(function(p) {
|
|
if (p.id) {
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(p.id)) { rowIdx = i + 1; break; }
|
|
}
|
|
if (rowIdx > -1) {
|
|
sheet.getRange(rowIdx, 2, 1, 7).setValues([[
|
|
p.nama || '', Number(p.harga) || 0, Number(p.qty) || 0,
|
|
Number(p.total) || 0, p.tgl || Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd"),
|
|
p.kategori || '', p.catatan || ''
|
|
]]);
|
|
}
|
|
} else {
|
|
var id = 'B-' + Date.now() + Math.floor(Math.random() * 1000);
|
|
var row = [
|
|
id, p.nama || '', Number(p.harga) || 0, Number(p.qty) || 0,
|
|
Number(p.total) || 0, p.tgl || Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd"),
|
|
p.kategori || '', p.catatan || '', now
|
|
];
|
|
sheet.appendRow(row);
|
|
}
|
|
});
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function syncBelanjaOut(dateStr) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var now = new Date();
|
|
var date = (function() {
|
|
var s = String(dateStr || '').trim();
|
|
if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
|
|
return Utilities.formatDate(now, 'GMT+7', 'yyyy-MM-dd');
|
|
})();
|
|
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
|
|
if (sheet.getLastRow() <= 1) return { date: date, localCount: 0, rekap: { ok: true, inserted: 0, updated: 0, skipped: 0 }, stokBelanja: { ok: true, belanja: 0, operasional: 0, pegawai: 0, skipped: 0 } };
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
var items = [];
|
|
for (var i = 1; i < data.length; i++) {
|
|
var r = data[i];
|
|
var id = String(r[0] || '').trim();
|
|
var tgl = r[5] instanceof Date ? Utilities.formatDate(r[5], 'GMT+7', 'yyyy-MM-dd') : String(r[5] || '').trim();
|
|
if (tgl !== date) continue;
|
|
if (!id) continue;
|
|
items.push({
|
|
id: id,
|
|
nama: String(r[1] || ''),
|
|
harga: Number(r[2]) || 0,
|
|
qty: Number(r[3]) || 0,
|
|
total: Number(r[4]) || 0,
|
|
tgl: tgl,
|
|
kategori: String(r[6] || ''),
|
|
catatan: String(r[7] || '')
|
|
});
|
|
}
|
|
|
|
var result = {
|
|
date: date,
|
|
localCount: items.length,
|
|
rekap: { ok: false, inserted: 0, updated: 0, skipped: 0, error: '' },
|
|
stokBelanja: { ok: false, belanja: 0, operasional: 0, pegawai: 0, skipped: 0, error: '' }
|
|
};
|
|
|
|
var props = PropertiesService.getScriptProperties();
|
|
var rekapId = String(props.getProperty('rekap_transaksi_spreadsheet_id') || '').trim();
|
|
var sbId = String(props.getProperty('stok_belanja_spreadsheet_id') || '').trim();
|
|
|
|
if (rekapId) {
|
|
try {
|
|
result.rekap = syncToRekapTransaksi_(rekapId, items, now);
|
|
} catch (e1) {
|
|
result.rekap = { ok: false, inserted: 0, updated: 0, skipped: 0, error: (e1 && e1.message) ? e1.message : String(e1) };
|
|
}
|
|
} else {
|
|
result.rekap = { ok: false, inserted: 0, updated: 0, skipped: 0, error: 'Script Properties rekap_transaksi_spreadsheet_id belum diisi.' };
|
|
}
|
|
|
|
if (sbId) {
|
|
try {
|
|
result.stokBelanja = syncToStokBelanja_(sbId, items, now);
|
|
} catch (e2) {
|
|
result.stokBelanja = { ok: false, belanja: 0, operasional: 0, pegawai: 0, skipped: 0, error: (e2 && e2.message) ? e2.message : String(e2) };
|
|
}
|
|
} else {
|
|
result.stokBelanja = { ok: false, belanja: 0, operasional: 0, pegawai: 0, skipped: 0, error: 'Script Properties stok_belanja_spreadsheet_id belum diisi.' };
|
|
}
|
|
|
|
return result;
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
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 syncToRekapTransaksi_(spreadsheetId, items, now) {
|
|
var ss = SpreadsheetApp.openById(spreadsheetId);
|
|
var sheet = ensureSheetWithHeader_(ss, 'Belanja', ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'Timestamp']);
|
|
var map = buildIdRowMap_(sheet);
|
|
var inserted = 0;
|
|
var updated = 0;
|
|
var skipped = 0;
|
|
for (var i = 0; i < items.length; i++) {
|
|
var it = items[i];
|
|
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 existingRow = map[it.id];
|
|
if (existingRow) {
|
|
sheet.getRange(existingRow, 1, 1, row.length).setValues([row]);
|
|
updated++;
|
|
} else {
|
|
sheet.appendRow(row);
|
|
inserted++;
|
|
}
|
|
}
|
|
return { ok: true, inserted: inserted, updated: updated, skipped: skipped };
|
|
}
|
|
|
|
function mapKategoriToSB_(kat) {
|
|
var s = String(kat || '').trim().toLowerCase();
|
|
if (s === 'modal') return 'MODAL';
|
|
if (s === 'bawah') return 'BAWAH';
|
|
if (s === 'dapur') return 'DAPUR';
|
|
if (s === 'makan') return 'MAKAN';
|
|
return '';
|
|
}
|
|
|
|
function syncToStokBelanja_(spreadsheetId, items, now) {
|
|
var ss = SpreadsheetApp.openById(spreadsheetId);
|
|
var sheetBelanja = ensureSheetWithHeader_(ss, 'Belanja', ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'User', 'Timestamp']);
|
|
var sheetOp = ensureSheetWithHeader_(ss, 'Operasional', ['ID', 'Tanggal', 'Nama', 'Harga', 'Qty', 'Total', 'Catatan', 'User', 'Timestamp']);
|
|
var sheetPg = ensureSheetWithHeader_(ss, 'Pegawai', ['ID', 'Tanggal', 'Nama', 'Nilai', 'Kategori', 'Catatan', 'User', 'Timestamp']);
|
|
|
|
var mapBel = buildIdRowMap_(sheetBelanja);
|
|
var mapOp = buildIdRowMap_(sheetOp);
|
|
var mapPg = buildIdRowMap_(sheetPg);
|
|
|
|
var belanjaCount = 0;
|
|
var operasionalCount = 0;
|
|
var pegawaiCount = 0;
|
|
var skipped = 0;
|
|
|
|
for (var i = 0; i < items.length; i++) {
|
|
var it = items[i];
|
|
if (!it || !it.id) { skipped++; continue; }
|
|
var kat = String(it.kategori || '').trim();
|
|
var low = kat.toLowerCase();
|
|
|
|
if (low === 'operasional') {
|
|
var idOp = 'POS-' + it.id;
|
|
var qtyOp = Number(it.qty) || 0;
|
|
if (qtyOp <= 0) qtyOp = 1;
|
|
var hargaOp = Number(it.harga) || 0;
|
|
var totalOp = Number(it.total) || 0;
|
|
if (totalOp <= 0 && hargaOp > 0) totalOp = hargaOp * qtyOp;
|
|
if (hargaOp <= 0 && totalOp > 0) hargaOp = Math.round(totalOp / qtyOp);
|
|
if (totalOp <= 0) { skipped++; continue; }
|
|
var rowOp = [idOp, it.tgl, it.nama || '', hargaOp, qtyOp, totalOp, it.catatan || '', 'POSFuku', now];
|
|
var rOp = mapOp[idOp];
|
|
if (rOp) sheetOp.getRange(rOp, 1, 1, rowOp.length).setValues([rowOp]); else sheetOp.appendRow(rowOp);
|
|
operasionalCount++;
|
|
continue;
|
|
}
|
|
|
|
if (low === 'pegawai') {
|
|
var idPg = 'POS-' + it.id;
|
|
var nilai = Number(it.total) || 0;
|
|
if (nilai <= 0) { skipped++; continue; }
|
|
var kategoriPg = /kasbon/i.test(it.nama || '') || /kasbon/i.test(it.catatan || '') ? 'Kasbon' : 'Gaji';
|
|
var rowPg = [idPg, it.tgl, it.nama || '', nilai, kategoriPg, it.catatan || '', 'POSFuku', now];
|
|
var rPg = mapPg[idPg];
|
|
if (rPg) sheetPg.getRange(rPg, 1, 1, rowPg.length).setValues([rowPg]); else sheetPg.appendRow(rowPg);
|
|
pegawaiCount++;
|
|
continue;
|
|
}
|
|
|
|
var sbKat = mapKategoriToSB_(kat);
|
|
if (!sbKat) { skipped++; continue; }
|
|
var rowBel = [it.id, it.nama || '', Number(it.harga) || 0, Number(it.qty) || 0, Number(it.total) || 0, it.tgl, sbKat, it.catatan || '', 'POSFuku', now];
|
|
var rBel = mapBel[it.id];
|
|
if (rBel) sheetBelanja.getRange(rBel, 1, 1, rowBel.length).setValues([rowBel]); else sheetBelanja.appendRow(rowBel);
|
|
belanjaCount++;
|
|
}
|
|
|
|
return { ok: true, belanja: belanjaCount, operasional: operasionalCount, pegawai: pegawaiCount, skipped: skipped };
|
|
}
|
|
|
|
function searchTransactionsGlobal(query, limit) {
|
|
var q = String(query || '').trim().toLowerCase();
|
|
var lim = Number(limit) || 50;
|
|
if (lim < 1) lim = 50;
|
|
if (lim > 200) lim = 200;
|
|
if (!q) return { items: [] };
|
|
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
if (!sheet) throw new Error('Sheet Transaksi tidak ditemukan.');
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 1) return { items: [] };
|
|
var lastCol = sheet.getLastColumn();
|
|
var rowCount = lastRow - 1;
|
|
var maxScan = 5000;
|
|
var startRow = Math.max(2, lastRow - maxScan + 1);
|
|
var scanCount = lastRow - startRow + 1;
|
|
var data = sheet.getRange(startRow, 1, scanCount, lastCol).getValues();
|
|
|
|
function fmtDate_(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.slice(0, 10);
|
|
try {
|
|
var d = new Date(s);
|
|
if (!isNaN(d.getTime())) return Utilities.formatDate(d, 'GMT+7', 'yyyy-MM-dd');
|
|
} catch (e) {}
|
|
return s.slice(0, 10);
|
|
}
|
|
|
|
function parseItems_(jsonStr) {
|
|
if (!jsonStr) return [];
|
|
try {
|
|
var arr = JSON.parse(String(jsonStr));
|
|
if (!Array.isArray(arr)) return [];
|
|
return arr.map(function(it) {
|
|
return { nama: String(it.nama || ''), qty: Number(it.qty) || 0 };
|
|
}).filter(function(it) { return it.nama; });
|
|
} catch (e) {
|
|
return [];
|
|
}
|
|
}
|
|
|
|
var out = [];
|
|
for (var i = data.length - 1; i >= 0; i--) {
|
|
var r = data[i];
|
|
var id = String(r[0] || '').trim();
|
|
if (!id) continue;
|
|
var meja = String(r[1] || '').trim();
|
|
var status = String(r[2] || '').trim();
|
|
var nama = String(r[3] || '').trim();
|
|
var wa = String(r[4] || '').trim();
|
|
var tanggal = fmtDate_(r[5]);
|
|
var itemsJson = r[7];
|
|
var items = parseItems_(itemsJson);
|
|
var itemsText = items.slice(0, 4).map(function(it) { return it.nama + (it.qty ? (' x' + it.qty) : ''); }).join(', ');
|
|
var total = Number(r[17]) || 0;
|
|
var metodeBayar = String(r[21] || '').trim();
|
|
var catatan = String(r[27] || '').trim();
|
|
var hay = (id + ' ' + meja + ' ' + status + ' ' + nama + ' ' + wa + ' ' + tanggal + ' ' + metodeBayar + ' ' + catatan + ' ' + items.map(function(it){ return it.nama; }).join(' ')).toLowerCase();
|
|
if (hay.indexOf(q) === -1) continue;
|
|
out.push({ id: id, tanggal: tanggal, meja: meja, nama: nama, status: status, total: total, metodeBayar: metodeBayar, itemsText: itemsText });
|
|
if (out.length >= lim) break;
|
|
}
|
|
return { items: out };
|
|
}
|
|
|
|
function calcPotensiProfitFromItems_(ss, items) {
|
|
var list = Array.isArray(items) ? items : [];
|
|
if (!list.length) return 0;
|
|
var menuMap = {};
|
|
var paketMap = {};
|
|
|
|
var sheetMenu = ss.getSheetByName('Menu');
|
|
if (sheetMenu && sheetMenu.getLastRow() > 1) {
|
|
var hMenu = getHeaderIndexMap_(sheetMenu);
|
|
var lcMenu = sheetMenu.getLastColumn();
|
|
var idxNamaM = (hMenu['nama'] != null) ? hMenu['nama'] : 0;
|
|
var idxHargaM = (hMenu['harga'] != null) ? hMenu['harga'] : 1;
|
|
var idxModalM = (hMenu['modal'] != null) ? hMenu['modal'] : -1;
|
|
var idxStatusM = (hMenu['status'] != null) ? hMenu['status'] : 3;
|
|
var dataMenu = sheetMenu.getRange(2, 1, sheetMenu.getLastRow() - 1, lcMenu).getValues();
|
|
for (var i = 0; i < dataMenu.length; i++) {
|
|
var r = dataMenu[i];
|
|
var nm = String(r[idxNamaM] || '').trim();
|
|
if (!nm) continue;
|
|
var status = String(r[idxStatusM] || '').toLowerCase().trim();
|
|
if (status && status !== 'aktif') continue;
|
|
menuMap[nm.toLowerCase()] = { harga: parseNumberFlexible(r[idxHargaM]), modal: idxModalM > -1 ? parseNumberFlexible(r[idxModalM]) : 0 };
|
|
}
|
|
}
|
|
|
|
var sheetPaket = ss.getSheetByName('PaketKustom');
|
|
if (sheetPaket && sheetPaket.getLastRow() > 1) {
|
|
var hP = getHeaderIndexMap_(sheetPaket);
|
|
var lcP = sheetPaket.getLastColumn();
|
|
var idxNamaP = (hP['nama paket'] != null) ? hP['nama paket'] : (hP['nama'] != null ? hP['nama'] : 1);
|
|
var idxTotalP = (hP['total harga'] != null) ? hP['total harga'] : (hP['total'] != null ? hP['total'] : 3);
|
|
var idxModalP = (hP['modal'] != null) ? hP['modal'] : -1;
|
|
var idxAktifP = (hP['aktif'] != null) ? hP['aktif'] : 6;
|
|
var dataP = sheetPaket.getRange(2, 1, sheetPaket.getLastRow() - 1, lcP).getValues();
|
|
for (var j = 0; j < dataP.length; j++) {
|
|
var rp = dataP[j];
|
|
var np = String(rp[idxNamaP] || '').trim();
|
|
if (!np) continue;
|
|
var aktif = rp[idxAktifP];
|
|
var isAktif = (aktif === true || String(aktif).toLowerCase() === 'aktif' || String(aktif).toLowerCase() === 'true' || aktif === 1);
|
|
if (!isAktif) continue;
|
|
paketMap[np.toLowerCase()] = { harga: parseNumberFlexible(rp[idxTotalP]), modal: idxModalP > -1 ? parseNumberFlexible(rp[idxModalP]) : 0 };
|
|
}
|
|
}
|
|
|
|
var totalProfit = 0;
|
|
for (var k = 0; k < list.length; k++) {
|
|
var it = list[k] || {};
|
|
var nama = String(it.nama || '').trim();
|
|
var qty = Number(it.qty) || 0;
|
|
if (!nama || !qty) continue;
|
|
var key = nama.toLowerCase();
|
|
var ref = menuMap[key] || paketMap[key];
|
|
if (!ref) continue;
|
|
var margin = (Number(ref.harga) || 0) - (Number(ref.modal) || 0);
|
|
totalProfit += margin * qty;
|
|
}
|
|
return Math.round(totalProfit);
|
|
}
|
|
|
|
function deleteBelanja(id) {
|
|
if (!id) throw new Error('ID belanja tidak valid.');
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Belanja');
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
if (rowIdx > -1) {
|
|
sheet.deleteRow(rowIdx);
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function saveRekap(payload) {
|
|
if (!payload) throw new Error('Data rekap tidak valid (payload kosong).');
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Rekap');
|
|
if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Rekap'); }
|
|
var now = new Date();
|
|
var id = payload.id || ('R-' + Date.now());
|
|
|
|
// ID, Tanggal, Saldo Awal, Tunai, QRIS, Debit, Credit, Transfer, Catatan, Timestamp
|
|
var row = [
|
|
id,
|
|
payload.tgl || Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd"),
|
|
Number(payload.saldoAwal) || 0,
|
|
Number(payload.tunai) || 0,
|
|
Number(payload.qris) || 0,
|
|
Number(payload.debit) || 0,
|
|
Number(payload.credit) || 0,
|
|
Number(payload.transfer) || 0,
|
|
payload.catatan || '',
|
|
now
|
|
];
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
var rowIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
|
|
}
|
|
|
|
if (rowIdx > -1) sheet.getRange(rowIdx, 1, 1, row.length).setValues([row]);
|
|
else sheet.appendRow(row);
|
|
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function updateMenuStock(nama, newStok, terpakai) {
|
|
return saveStokBulk([{ nama: nama, sisa: newStok, terpakai: terpakai }]);
|
|
}
|
|
|
|
function saveStokBulk(payloads) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Menu');
|
|
var sheetH = ss.getSheetByName('StokHarian') || ss.insertSheet('StokHarian');
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
var now = new Date();
|
|
var todayStr = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
|
|
|
|
payloads.forEach(function(p) {
|
|
var nama = p.nama;
|
|
var newSisa = Number(p.sisa) || 0;
|
|
var terpakai = Number(p.terpakai) || 0;
|
|
|
|
// Update di sheet Menu
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === nama) {
|
|
sheet.getRange(i + 1, 6).setValue(newSisa);
|
|
break;
|
|
}
|
|
}
|
|
|
|
// Simpan ke StokHarian
|
|
sheetH.appendRow([todayStr, nama, terpakai + newSisa, terpakai, newSisa, now]);
|
|
});
|
|
|
|
return getInitialData();
|
|
}
|
|
function voidTransaction(id, reason, fotoUrl) {
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetT = ss.getSheetByName('Transaksi');
|
|
var sheetV = ss.getSheetByName('Void') || ss.insertSheet('Void');
|
|
|
|
if (sheetV.getLastRow() === 0) {
|
|
sheetV.appendRow(['Timestamp', 'ID Transaksi', 'Meja', 'Total', 'Alasan', 'Foto', 'Data Lengkap']);
|
|
}
|
|
|
|
// Optimasi: Hanya ambil kolom ID (Kolom A) untuk mencari baris
|
|
var lastRow = sheetT.getLastRow();
|
|
if (lastRow < 2) return { error: "Tidak ada transaksi" };
|
|
|
|
var ids = sheetT.getRange(1, 1, lastRow, 1).getValues();
|
|
var foundIdx = -1;
|
|
for (var i = 1; i < ids.length; i++) {
|
|
if (String(ids[i][0]) === String(id)) {
|
|
foundIdx = i + 1;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (foundIdx > -1) {
|
|
// Ambil data lengkap baris tersebut untuk dipindah ke Void
|
|
var rowData = sheetT.getRange(foundIdx, 1, 1, sheetT.getLastColumn()).getValues()[0];
|
|
sheetV.appendRow([new Date(), id, rowData[1], rowData[17], reason, fotoUrl || '', JSON.stringify(rowData)]);
|
|
|
|
// Update status di Transaksi menjadi "Void" (bukan hapus baris)
|
|
sheetT.getRange(foundIdx, 3).setValue('Void');
|
|
|
|
return getInitialData();
|
|
} else {
|
|
return { error: "Transaksi tidak ditemukan" };
|
|
}
|
|
} catch (err) {
|
|
console.error('Error voidTransaction:', err);
|
|
return { error: err.message };
|
|
}
|
|
}
|
|
|
|
function getReport(dari, sampai) {
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
var sheetB = ss.getSheetByName('Belanja');
|
|
if (!sheet) return { omzet: 0, items: [], methods: {}, belanja: 0 };
|
|
|
|
var lastRow = sheet.getLastRow();
|
|
var data = lastRow > 1 ? sheet.getRange(2, 1, lastRow - 1, 28).getValues() : [];
|
|
var tz = 'GMT+7';
|
|
var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
|
|
var from = dari || today;
|
|
var to = sampai || today;
|
|
|
|
var omzet = 0;
|
|
var counter = {};
|
|
var methods = { 'Tunai': 0, 'QRIS': 0, 'Debit': 0, 'Credit': 0, 'Transfer': 0 };
|
|
|
|
data.forEach(function(r, idx) {
|
|
if (r.length < 5) return; // Baris terlalu pendek
|
|
var status = String(r[2] || '');
|
|
if (status !== 'Selesai') return;
|
|
|
|
// Deteksi tanggal dari berbagai kolom yang mungkin
|
|
var dt = null;
|
|
if (r[25] instanceof Date) dt = r[25];
|
|
else if (r[5] instanceof Date) dt = r[5];
|
|
else if (r[0] instanceof Date) dt = r[0];
|
|
|
|
// Jika bukan Date, coba parsing string
|
|
if (!dt) {
|
|
var dateStr = String(r[25] || r[5] || r[0] || '');
|
|
if (dateStr) {
|
|
var parsed = new Date(dateStr);
|
|
if (!isNaN(parsed.getTime())) dt = parsed;
|
|
}
|
|
}
|
|
|
|
if (!dt) {
|
|
console.warn('Baris ' + (idx+2) + ': Gagal mendeteksi tanggal');
|
|
return;
|
|
}
|
|
|
|
var d = Utilities.formatDate(dt, tz, 'yyyy-MM-dd');
|
|
if (d < from || d > to) return;
|
|
|
|
var total = Number(r[17]) || 0;
|
|
omzet += total;
|
|
|
|
var m = String(r[21] || 'Tunai').trim() || 'Tunai';
|
|
if (m === 'Multi') {
|
|
try {
|
|
var details = JSON.parse(r[27] || '{}'); // r[27] adalah kolom Catatan
|
|
for (var key in details) {
|
|
var val = Number(details[key]) || 0;
|
|
if (val > 0) {
|
|
if (methods.hasOwnProperty(key)) methods[key] += val;
|
|
else methods[key] = (methods[key] || 0) + val;
|
|
}
|
|
}
|
|
} catch (e) {
|
|
console.error('Gagal parsing JSON multi-payment baris ' + (idx+2) + ':', e);
|
|
// Fallback jika gagal parse, masukkan ke Tunai atau Multi
|
|
methods['Tunai'] = (methods['Tunai'] || 0) + total;
|
|
}
|
|
} else {
|
|
if (methods.hasOwnProperty(m)) methods[m] += total;
|
|
else methods[m] = (methods[m] || 0) + total;
|
|
}
|
|
|
|
try {
|
|
var itemsJson = String(r[7] || '[]');
|
|
if (itemsJson.indexOf('[') === 0) {
|
|
var items = JSON.parse(itemsJson);
|
|
if (Array.isArray(items)) {
|
|
items.forEach(function(it) {
|
|
var k = String(it.nama || '');
|
|
if (!k) return;
|
|
counter[k] = (counter[k] || 0) + (Number(it.qty) || 0);
|
|
});
|
|
}
|
|
}
|
|
} catch (e) {
|
|
console.error('Gagal parsing JSON item baris ' + (idx+2) + ':', e);
|
|
}
|
|
});
|
|
|
|
console.log('Report generated: omzet=' + omzet + ', methods=' + JSON.stringify(methods));
|
|
|
|
var belanjaTotal = 0;
|
|
var belanjaItems = [];
|
|
if (sheetB) {
|
|
var lastRowB = sheetB.getLastRow();
|
|
var bData = lastRowB > 1 ? sheetB.getRange(2, 1, lastRowB - 1, 9).getValues() : [];
|
|
bData.forEach(function(r) {
|
|
if (r.length < 9) return;
|
|
var dt = r[8] instanceof Date ? r[8] : (r[5] instanceof Date ? r[5] : null);
|
|
if (!dt && r[8]) dt = new Date(r[8]);
|
|
if (!dt && r[5]) dt = new Date(r[5]);
|
|
|
|
if (!dt || isNaN(dt.getTime())) return;
|
|
var d = Utilities.formatDate(dt, tz, 'yyyy-MM-dd');
|
|
if (d < from || d > to) return;
|
|
var t = Number(r[4]) || 0;
|
|
belanjaTotal += t;
|
|
belanjaItems.push({ nama: r[1], total: t, tgl: r[5], id: r[0] });
|
|
});
|
|
}
|
|
|
|
var itemsSorted = Object.keys(counter).map(function(k) { return [k, counter[k]]; }).sort(function(a, b) { return b[1] - a[1]; });
|
|
return { omzet: omzet, items: itemsSorted, methods: methods, belanja: belanjaTotal, belanjaItems: belanjaItems };
|
|
} catch (err) {
|
|
console.error('Error di getReport:', err);
|
|
return { error: err.message, omzet: 0, items: [], methods: {}, belanja: 0 };
|
|
}
|
|
}
|
|
|
|
function saveSupplierWA(payload) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('SupplierHistory');
|
|
if (!sheet) { setupSheets(); sheet = ss.getSheetByName('SupplierHistory'); }
|
|
var now = new Date();
|
|
var id = 'S-' + Date.now();
|
|
|
|
// ID, Nama Supplier, Kategori, WhatsApp, Pesan, Timestamp
|
|
var row = [
|
|
id,
|
|
payload.nama || '',
|
|
payload.kategori || '',
|
|
payload.wa || '',
|
|
payload.pesan || '',
|
|
now
|
|
];
|
|
|
|
sheet.appendRow(row);
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
// --- Fungsi Chat & Bel ---
|
|
function sendChatMessage(sender, message) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Chat') || ss.insertSheet('Chat');
|
|
if (sheet.getLastRow() === 0) {
|
|
sheet.appendRow(['Timestamp', 'Pengirim', 'Pesan', 'Status']);
|
|
}
|
|
sheet.appendRow([new Date(), sender, message, 'Belum Dibaca']);
|
|
return { success: true };
|
|
}
|
|
|
|
function getChatMessages() {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Chat');
|
|
if (!sheet) return [];
|
|
var data = sheet.getDataRange().getValues();
|
|
data.shift(); // Hapus header
|
|
return data.map(function(row) {
|
|
return { timestamp: parseDate(row[0]), sender: row[1], message: row[2], status: row[3] };
|
|
});
|
|
}
|
|
|
|
function markChatAsRead(receiver) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Chat');
|
|
if (!sheet) return { success: false };
|
|
var data = sheet.getDataRange().getValues();
|
|
var changed = false;
|
|
for (var i = 1; i < data.length; i++) {
|
|
// Jika pengirim bukan kita (receiver) dan statusnya Belum Dibaca
|
|
if (data[i][1] !== receiver && data[i][3] === 'Belum Dibaca') {
|
|
sheet.getRange(i + 1, 4).setValue('Sudah Dibaca');
|
|
changed = true;
|
|
}
|
|
}
|
|
return { success: true, changed: changed };
|
|
}
|
|
|
|
function triggerBell(bellId) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('BellStatus') || ss.insertSheet('BellStatus');
|
|
if (sheet.getLastRow() === 0) {
|
|
sheet.appendRow(['BellID', 'Status', 'Timestamp']);
|
|
}
|
|
var data = sheet.getDataRange().getValues();
|
|
var found = false;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (data[i][0] === bellId) {
|
|
sheet.getRange(i + 1, 2, 1, 2).setValues([['Active', new Date()]]);
|
|
found = true;
|
|
break;
|
|
}
|
|
}
|
|
if (!found) {
|
|
sheet.appendRow([bellId, 'Active', new Date()]);
|
|
}
|
|
try {
|
|
var settings = getSettingsMap_();
|
|
if (String(settings.telegram_bell_enabled || '').toLowerCase() === 'true') {
|
|
sendTelegram_('BEL: ' + String(bellId || ''));
|
|
}
|
|
} catch (eNotify) {}
|
|
return { success: true };
|
|
}
|
|
|
|
function resetBell(bellId) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('BellStatus');
|
|
if (!sheet) return { success: false, error: 'Sheet BellStatus tidak ditemukan.' };
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (data[i][0] === bellId) {
|
|
sheet.getRange(i + 1, 2).setValue('Inactive');
|
|
break;
|
|
}
|
|
}
|
|
return { success: true };
|
|
}
|
|
|
|
function notifyWaiterTelegram(payload) {
|
|
try {
|
|
var settings = getSettingsMap_();
|
|
if (String(settings.telegram_waiter_enabled || '').toLowerCase() !== 'true') return { success: true, sent: false, skipped: true };
|
|
var meja = payload && payload.meja ? String(payload.meja) : '';
|
|
var waiter = payload && payload.waiter ? String(payload.waiter) : '';
|
|
var message = payload && payload.message ? String(payload.message) : '';
|
|
var muted = /x123/i.test(String(payload && payload.nama ? payload.nama : ''));
|
|
if (muted) return { success: true, sent: false, skipped: true };
|
|
var text = 'PANGGIL PELAYAN\nMeja: ' + meja + (waiter ? '\nPelayan: ' + waiter : '') + (message ? '\n\n' + message : '');
|
|
var res = sendTelegram_(text);
|
|
return { success: true, sent: !!res.success, result: res };
|
|
} catch (e) {
|
|
return { success: false, sent: false, error: String(e && e.message ? e.message : e) };
|
|
}
|
|
}
|
|
|
|
function getBellStatus(bellId) {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('BellStatus');
|
|
if (!sheet) return { status: 'Inactive' };
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (data[i][0] === bellId) {
|
|
return { status: data[i][1] };
|
|
}
|
|
}
|
|
return { status: 'Inactive' };
|
|
}
|
|
|
|
|
|
function saveMasterSupplier(payload) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Suppliers');
|
|
if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Suppliers'); }
|
|
var lastCol = sheet.getLastColumn();
|
|
var now = new Date();
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
var foundIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (payload && payload.id && String(data[i][0]) === String(payload.id)) {
|
|
foundIdx = i + 1;
|
|
break;
|
|
}
|
|
if (String(data[i][1]).toLowerCase() === String(payload.nama).toLowerCase()) {
|
|
foundIdx = i + 1;
|
|
break;
|
|
}
|
|
}
|
|
|
|
var id = (payload && payload.id) ? String(payload.id) : ((foundIdx > -1 && data[foundIdx - 1] && data[foundIdx - 1][0]) ? String(data[foundIdx - 1][0]) : ('SUP-' + Date.now()));
|
|
var row = [id, payload.nama || '', payload.wa || ''];
|
|
if (lastCol >= 5) {
|
|
row.push(payload.rekening || '');
|
|
row.push(now);
|
|
} else {
|
|
row.push(now);
|
|
}
|
|
|
|
if (foundIdx > -1) {
|
|
sheet.getRange(foundIdx, 1, 1, row.length).setValues([row]);
|
|
} else {
|
|
sheet.appendRow(row);
|
|
}
|
|
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function deleteMasterSupplier(idOrName) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Suppliers');
|
|
if (!sheet) return getInitialData();
|
|
var data = sheet.getDataRange().getValues();
|
|
var key = String(idOrName || '').trim();
|
|
if (!key) return getInitialData();
|
|
var keyLower = key.toLowerCase();
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === key) {
|
|
sheet.deleteRow(i + 1);
|
|
break;
|
|
}
|
|
}
|
|
if (sheet.getLastRow() > 1) {
|
|
data = sheet.getDataRange().getValues();
|
|
for (var j = 1; j < data.length; j++) {
|
|
if (String(data[j][1] || '').trim().toLowerCase() === keyLower) {
|
|
sheet.deleteRow(j + 1);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function saveWaiter(payload) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Waiters');
|
|
if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Waiters'); }
|
|
var now = new Date();
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
var foundIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]).toLowerCase() === String(payload.nama).toLowerCase()) {
|
|
foundIdx = i + 1;
|
|
break;
|
|
}
|
|
}
|
|
|
|
var row = [
|
|
payload.nama || '',
|
|
payload.wa || '',
|
|
payload.status || 'Aktif',
|
|
now
|
|
];
|
|
|
|
if (foundIdx > -1) {
|
|
sheet.getRange(foundIdx, 1, 1, row.length).setValues([row]);
|
|
} else {
|
|
sheet.appendRow(row);
|
|
}
|
|
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function deleteWaiter(nama) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Waiters');
|
|
if (!sheet) return getInitialData();
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]).toLowerCase() === String(nama).toLowerCase()) {
|
|
sheet.deleteRow(i + 1);
|
|
break;
|
|
}
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function deleteTransaction(id) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
if (!sheet) return getInitialData();
|
|
|
|
var data = sheet.getDataRange().getValues();
|
|
var foundIdx = -1;
|
|
for (var i = 1; i < data.length; i++) {
|
|
if (String(data[i][0]) === String(id)) {
|
|
foundIdx = i + 1;
|
|
var t = data[i];
|
|
var wa = String(t[4] || '');
|
|
var poinDapat = Number(t[24]) || 0;
|
|
var poinDipakaiRp = Number(t[10]) || 0;
|
|
var settings = getSettingsMap_();
|
|
var redeemRp = parseNumberFlexible(settings.poin_redeem_rupiah);
|
|
if (!redeemRp || redeemRp < 1) redeemRp = 100;
|
|
var poinUsed = Math.floor(poinDipakaiRp / redeemRp);
|
|
|
|
if (wa) {
|
|
var sheetCust = ss.getSheetByName('Pelanggan');
|
|
if (sheetCust) {
|
|
var custRows = sheetCust.getDataRange().getValues();
|
|
var cIdx = -1;
|
|
for (var j = 1; j < custRows.length; j++) {
|
|
if (String(custRows[j][1]) === wa) { cIdx = j + 1; break; }
|
|
}
|
|
if (cIdx > -1) {
|
|
var curP = Number(custRows[cIdx - 1][2]) || 0;
|
|
var newP = Math.max(0, curP - poinDapat + poinUsed); // Batal dapat, kembalikan yang dipakai
|
|
sheetCust.getRange(cIdx, 3).setValue(newP);
|
|
sheetCust.getRange(cIdx, 4).setValue(new Date());
|
|
}
|
|
}
|
|
}
|
|
|
|
sheet.deleteRow(foundIdx);
|
|
break;
|
|
}
|
|
}
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function clearTransactionHistoryKeepLast() {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Transaksi');
|
|
if (!sheet) return getInitialData();
|
|
var lastRow = sheet.getLastRow();
|
|
if (lastRow <= 2) return getInitialData(); // header + 1 row (atau kosong)
|
|
sheet.deleteRows(2, lastRow - 2); // sisakan header dan 1 transaksi terakhir
|
|
return getInitialData();
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
|
|
function resetAllStock() {
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheetM = ss.getSheetByName('Menu');
|
|
var dataM = sheetM.getDataRange().getValues();
|
|
var now = new Date();
|
|
var nowStr = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd HH:mm:ss");
|
|
|
|
// Simpan timestamp reset ke properties
|
|
PropertiesService.getScriptProperties().setProperty('last_stock_reset', nowStr);
|
|
|
|
// Log ke StokHarian sebagai pembukaan toko
|
|
var sheetH = ss.getSheetByName('StokHarian') || ss.insertSheet('StokHarian');
|
|
var todayStr = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
|
|
var logs = [];
|
|
for (var i = 1; i < dataM.length; i++) {
|
|
var nama = String(dataM[i][0]);
|
|
var sisa = Number(dataM[i][5]) || 0;
|
|
logs.push([todayStr, nama, sisa, 0, sisa, now]);
|
|
}
|
|
if (logs.length > 0) {
|
|
sheetH.getRange(sheetH.getLastRow() + 1, 1, logs.length, 6).setValues(logs);
|
|
}
|
|
|
|
return getInitialData();
|
|
} catch (e) {
|
|
return { error: e.message };
|
|
}
|
|
}
|
|
|
|
function saveFeedback(payload) {
|
|
var lock = LockService.getScriptLock();
|
|
try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
|
|
try {
|
|
var ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
var sheet = ss.getSheetByName('Feedback');
|
|
if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Feedback'); }
|
|
var now = new Date();
|
|
|
|
// Timestamp, Nama Pelanggan, WhatsApp, Rating, Komentar
|
|
var row = [
|
|
now,
|
|
payload.nama || '',
|
|
payload.wa || '',
|
|
payload.rating || 0,
|
|
payload.komentar || ''
|
|
];
|
|
|
|
sheet.appendRow(row);
|
|
return { success: true };
|
|
} finally { lock.releaseLock(); }
|
|
}
|
|
function paksaIzinDrive() {
|
|
// Baris ini akan memaksa Google mendeteksi kebutuhan akses Drive
|
|
var folder = DriveApp.getRootFolder();
|
|
console.log("Akses Drive Berhasil: " + folder.getName());
|
|
}
|