function doGet(e) {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetHicom = ss.getSheetByName("HICOM");
var sheetPidm = ss.getSheetByName("PIDM") || ss.getSheetByName("PDIM");
var sheetPaid = ss.getSheetByName("Paid");
var dataHicom = sheetHicom ? sheetHicom.getDataRange().getValues() : [];
var dataPidm = sheetPidm ? sheetPidm.getDataRange().getValues() : [];
var dataPaid = sheetPaid ? sheetPaid.getDataRange().getValues() : [];
return createJsonResponse({
status: "success",
hicom: dataHicom,
pidm: dataPidm,
paid: dataPaid
});
} catch (err) {
return createJsonResponse({ status: "error", message: err.toString() });
}
}
function doPost(e) {
try {
var params = JSON.parse(e.postData.contents);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var auditSheet = ss.getSheetByName("AUDIT_LOG") || ss.insertSheet("AUDIT_LOG");
if (params.action === "ADD_BATCH") {
var items = params.items || [];
for (var i = 0; i < items.length; i++) {
var itm = items[i];
var itemCompany = (itm.company === "PIDM") ? "PIDM" : "HICOM";
var sheet = ss.getSheetByName(itemCompany) || ss.insertSheet(itemCompany);
var nextRow = sheet.getLastRow() + 1;
var row41 = new Array(41).fill("");
row41[0] = itm.issueDate || "";
row41[1] = itm.shippingDate || "";
row41[2] = itm.deliveredDate || "";
row41[3] = "";
row41[4] = "";
row41[5] = itm.invoiceNo || "";
row41[6] = "";
if (itm.rawRow && Array.isArray(itm.rawRow)) {
for (var k = 0; k < Math.min(itm.rawRow.length, 34); k++) {
row41[7 + k] = (itm.rawRow[k] !== undefined && itm.rawRow[k] !== null) ? itm.rawRow[k].toString() : "";
}
}
if (itm.issueDate) row41[8] = itm.issueDate;
if (itm.claimNo) row41[9] = itm.claimNo;
if (itm.patientName) row41[10] = itm.patientName;
if (itm.patientIc) row41[11] = itm.patientIc;
if (itm.address) row41[20] = itm.address;
if (itm.company) row41[31] = itm.company;
sheet.getRange(nextRow, 1, 1, 41).setValues([row41]);
}
auditSheet.appendRow([new Date(), params.userId || "system", "ADD_BATCH", "HICOM/PIDM", "Uploaded/Pasted " + items.length + " items"]);
return createJsonResponse({ status: "success", message: "Batch added successfully" });
}
var searchSheets = ["HICOM", "PIDM", "PDIM", "Paid"];
if (params.action === "EDIT") {
for (var s = 0; s < searchSheets.length; s++) {
var sh = ss.getSheetByName(searchSheets[s]);
if (!sh) continue;
var data = sh.getDataRange().getValues();
for (var r = 1; r < data.length; r++) {
if ((data[r][9] || "").toString().trim() === params.claimNo) {
var rowNum = r + 1;
sh.getRange(rowNum, 1).setValue(params.issueDate || "");
sh.getRange(rowNum, 2).setValue(params.shippingDate || "");
sh.getRange(rowNum, 3).setValue(params.deliveredDate || "");
sh.getRange(rowNum, 6).setValue(params.invoiceNo || "");
sh.getRange(rowNum, 7).setValue(params.paymentVoucher || "");
sh.getRange(rowNum, 9).setValue(params.issueDate || "");
sh.getRange(rowNum, 10).setValue(params.claimNo || "");
sh.getRange(rowNum, 11).setValue(params.patientName || "");
sh.getRange(rowNum, 12).setValue(params.patientIc || "");
sh.getRange(rowNum, 21).setValue(params.address || "");
sh.getRange(rowNum, 32).setValue(params.company || "");
auditSheet.appendRow([new Date(), params.userId || "system", "EDIT", searchSheets[s], "Updated Claim " + params.claimNo]);
return createJsonResponse({ status: "success", message: "Record updated" });
}
}
}
}
if (params.action === "COMPLETE") {
for (var s = 0; s < searchSheets.length; s++) {
var sh = ss.getSheetByName(searchSheets[s]);
if (!sh) continue;
var data = sh.getDataRange().getValues();
for (var r = 1; r < data.length; r++) {
if ((data[r][9] || "").toString().trim() === params.claimNo) {
var rowNum = r + 1;
sh.getRange(rowNum, 7).setValue(params.paymentVoucher || "");
auditSheet.appendRow([new Date(), params.userId || "system", "COMPLETE", searchSheets[s], "Completed Claim " + params.claimNo + " with PV: " + params.paymentVoucher]);
return createJsonResponse({ status: "success", message: "Claim completed" });
}
}
}
}
if (params.action === "CANCEL") {
for (var s = 0; s < searchSheets.length; s++) {
var sh = ss.getSheetByName(searchSheets[s]);
if (!sh) continue;
var data = sh.getDataRange().getValues();
for (var r = 1; r < data.length; r++) {
if ((data[r][9] || "").toString().trim() === params.claimNo) {
var rowNum = r + 1;
sh.getRange(rowNum, 1).setValue("CANCELLED: " + (params.cancelReason || ""));
sh.getRange(rowNum, 1, 1, sh.getLastColumn()).setBackground("#f8d7da");
auditSheet.appendRow([new Date(), params.userId || "system", "CANCEL", searchSheets[s], "Cancelled Claim " + params.claimNo]);
return createJsonResponse({ status: "success", message: "Claim cancelled" });
}
}
}
}
if (params.action === "DELETE") {
for (var s = 0; s < searchSheets.length; s++) {
var sh = ss.getSheetByName(searchSheets[s]);
if (!sh) continue;
var data = sh.getDataRange().getValues();
for (var r = 1; r < data.length; r++) {
if ((data[r][9] || "").toString().trim() === params.claimNo) {
var rowNum = r + 1;
sh.deleteRow(rowNum);
auditSheet.appendRow([new Date(), params.userId || "system", "DELETE", searchSheets[s], "Deleted Claim " + params.claimNo]);
return createJsonResponse({ status: "success", message: "Claim deleted" });
}
}
}
}
return createJsonResponse({ status: "error", message: "Action not recognized" });
} catch (err) {
return createJsonResponse({ status: "error", message: err.toString() });
}
}
function createJsonResponse(obj) {
return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}