function doGet(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var reqYear = (e && e.parameter && e.parameter.year) ? e.parameter.year.toString().trim() : "ALL";
var sheets = ss.getSheets();
var epSheets = [];
var docuSheets = [];
for (var s = 0; s < sheets.length; s++) {
var sName = sheets[s].getName();
if (sName.indexOf("EP ") === 0) {
var yr = sName.replace("EP ", "").trim();
if (reqYear === "ALL" || reqYear === yr) epSheets.push(sheets[s]);
}
if (sName.indexOf("DOCUSHARE") === 0) docuSheets.push(sheets[s]);
}
if (epSheets.length === 0) {
var defaultEP = ss.getSheetByName("EP 2026") || ss.getSheetByName("EP");
if (defaultEP) epSheets.push(defaultEP);
}
var epData = [];
for (var j = 0; j < epSheets.length; j++) {
var sheet = epSheets[j];
var sheetYear = sheet.getName().replace("EP ", "").trim();
var rawValues = sheet.getDataRange().getValues();
var richTexts = sheet.getDataRange().getRichTextValues();
var displayValues = sheet.getDataRange().getDisplayValues();
for (var i = 1; i < rawValues.length; i++) {
var row = rawValues[i].slice();
if (displayValues[i] && displayValues[i][2] !== undefined && displayValues[i][2] !== "") row[2] = displayValues[i][2];
var driveUrl = "";
if (richTexts[i] && richTexts[i][16]) driveUrl = richTexts[i][16].getLinkUrl() || "";
if (!driveUrl && displayValues[i] && displayValues[i][16]) {
var dispVal = displayValues[i][16].toString().trim();
if (dispVal.substring(0, 4).toLowerCase() === "http") driveUrl = dispVal;
}
row[16] = driveUrl;
row[17] = sheetYear;
row[18] = i + 1;
var lpoNoStr = row[3] ? row[3].toString().trim().toUpperCase() : "";
var hospStr = row[4] ? row[4].toString().trim().toUpperCase() : "";
if (lpoNoStr && lpoNoStr !== "LPO NO" && lpoNoStr !== "NO LPO" && hospStr !== "HOSPITAL NAME") {
epData.push(row);
}
}
}
var docuData = [];
for (var d = 0; d < docuSheets.length; d++) {
var dValues = docuSheets[d].getDataRange().getValues();
for (var r = 1; r < dValues.length; r++) {
var dLpo = dValues[r][3] ? dValues[r][3].toString().trim().toUpperCase() : "";
if (dLpo && dLpo !== "LPO NO" && dLpo !== "NO LPO") {
docuData.push(dValues[r]);
}
}
}
return ContentService.createTextOutput(JSON.stringify({ status: "success", ep: epData, docushare: docuData, requestedYear: reqYear }))
.setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
var params = JSON.parse(e.postData.contents);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var auditSheet = ss.getSheetByName("AUDIT_LOG") || ss.insertSheet("AUDIT_LOG");
var targetYear = (params.lpoDate && params.lpoDate.length >= 4) ? params.lpoDate.substring(0, 4) : "2026";
var sheetEP = ss.getSheetByName("EP " + targetYear) || ss.getSheetByName("EP 2026") || ss.getSheets()[0];
var sheetDocu = ss.getSheetByName("DOCUSHARE") || ss.getSheetByName("DOCUSHARE " + targetYear) || ss.insertSheet("DOCUSHARE");
if (params.action === "ADD" || params.action === "BATCH_ADD") {
var itemsList = (params.items && Array.isArray(params.items) && params.items.length > 0) ? params.items : [params];
var addedRows = [];
for (var k = 0; k < itemsList.length; k++) {
var itm = itemsList[k];
var targetRow = getFirstEmptyRowInColD(sheetEP);
var noVal = targetRow - 1;
sheetEP.getRange(targetRow, 1).setValue(noVal);
sheetEP.getRange(targetRow, 2).setValue(itm.status || params.status);
sheetEP.getRange(targetRow, 3).setValue(itm.lpoDate || params.lpoDate);
sheetEP.getRange(targetRow, 4).setValue(itm.lpoNo || params.lpoNo);
sheetEP.getRange(targetRow, 5).setValue(itm.hospital || params.hospital);
sheetEP.getRange(targetRow, 6).setValue(itm.item);
sheetEP.getRange(targetRow, 7).setValue(itm.qty);
sheetEP.getRange(targetRow, 8).setValue(itm.rasumiDO || "");
sheetEP.getRange(targetRow, 9).setValue(itm.rasumiPO || "");
sheetEP.getRange(targetRow, 12).setValue(itm.unitPrice);
var urlToSave = itm.driveUrl || params.driveUrl || "";
if (urlToSave && urlToSave.trim().substring(0, 4).toLowerCase() === "http") {
sheetEP.getRange(targetRow, 17).setFormula('=HYPERLINK("' + urlToSave.trim() + '","VIEW")');
} else {
sheetEP.getRange(targetRow, 17).setValue("");
}
syncDocuRow(sheetDocu, targetRow, noVal, itm.lpoNo || params.lpoNo, itm.hospital || params.hospital, itm.item);
addedRows.push(targetRow);
}
auditSheet.appendRow([new Date(), params.userId, "ADD", params.lpoNo, "Added " + itemsList.length + " item(s) to " + sheetEP.getName()]);
return ContentService.createTextOutput(JSON.stringify({ status: "success", rows: addedRows })).setMimeType(ContentService.MimeType.JSON);
}
else if (params.action === "EDIT") {
var targetRow = -1;
if (params.rowIndex && params.rowIndex > 1) {
targetRow = params.rowIndex;
} else {
targetRow = findRowByLpoNoAndItem(sheetEP, params.oldLpoNo || params.lpoNo, params.oldItem || params.item);
}
if (targetRow > 1) {
sheetEP.getRange(targetRow, 2).setValue(params.status);
sheetEP.getRange(targetRow, 3).setValue(params.lpoDate);
sheetEP.getRange(targetRow, 4).setValue(params.lpoNo);
sheetEP.getRange(targetRow, 5).setValue(params.hospital);
sheetEP.getRange(targetRow, 6).setValue(params.item);
sheetEP.getRange(targetRow, 7).setValue(params.qty);
sheetEP.getRange(targetRow, 8).setValue(params.rasumiDO || "");
sheetEP.getRange(targetRow, 9).setValue(params.rasumiPO || "");
sheetEP.getRange(targetRow, 12).setValue(params.unitPrice);
var urlToSave = params.driveUrl || "";
if (urlToSave && urlToSave.trim().substring(0, 4).toLowerCase() === "http") {
sheetEP.getRange(targetRow, 17).setFormula('=HYPERLINK("' + urlToSave.trim() + '","VIEW")');
} else {
sheetEP.getRange(targetRow, 17).setValue("");
}
var docuRow = findRowByLpoNoAndItem(sheetDocu, params.oldLpoNo || params.lpoNo, params.oldItem || params.item);
if (docuRow > 1) {
sheetDocu.getRange(docuRow, 4).setValue(params.lpoNo);
sheetDocu.getRange(docuRow, 5).setValue(params.hospital);
sheetDocu.getRange(docuRow, 6).setValue(params.item);
}
auditSheet.appendRow([new Date(), params.userId, "EDIT", params.lpoNo, "Updated Row " + targetRow + " (" + params.item + ") in " + sheetEP.getName()]);
return ContentService.createTextOutput(JSON.stringify({ status: "success", row: targetRow })).setMimeType(ContentService.MimeType.JSON);
}
return ContentService.createTextOutput(JSON.stringify({ status: "not_found" })).setMimeType(ContentService.MimeType.JSON);
}
else if (params.action === "DELETE") {
var lpoNo = params.lpoNo;
var targetRow = -1;
if (params.rowIndex && params.rowIndex > 1) {
targetRow = params.rowIndex;
} else {
targetRow = findRowByLpoNoAndItem(sheetEP, lpoNo, params.item);
}
if (targetRow > 1) {
sheetEP.deleteRow(targetRow);
var docuRow = findRowByLpoNoAndItem(sheetDocu, lpoNo, params.item);
if (docuRow > 1) {
sheetDocu.deleteRow(docuRow);
}
auditSheet.appendRow([new Date(), params.userId, "DELETE", lpoNo, "Deleted Row " + targetRow + " from " + sheetEP.getName()]);
return ContentService.createTextOutput(JSON.stringify({ status: "success", deletedRow: targetRow })).setMimeType(ContentService.MimeType.JSON);
}
return ContentService.createTextOutput(JSON.stringify({ status: "not_found" })).setMimeType(ContentService.MimeType.JSON);
}
else if (params.action === "DOCUSHARE_UPDATE") {
var lpoNo = params.lpoNo;
var item = params.item || "";
var hospital = params.hospital || "";
var docKey = params.docKey;
var isChecked = params.isChecked;
var colIndex = getDocuColIndex(docKey);
var foundRow = findRowByLpoNoAndItem(sheetDocu, lpoNo, item);
if (foundRow <= 0) {
foundRow = findRowByLpoNo(sheetDocu, lpoNo);
}
if (foundRow > 1 && colIndex > 0) {
sheetDocu.getRange(foundRow, colIndex).setValue(isChecked ? "/" : "");
auditSheet.appendRow([new Date(), params.userId, "DOCUSHARE", lpoNo, "Updated " + docKey + " to " + isChecked]);
return ContentService.createTextOutput(JSON.stringify({ status: "success", row: foundRow })).setMimeType(ContentService.MimeType.JSON);
} else if (colIndex > 0) {
var newRow = getFirstEmptyRowInColD(sheetDocu);
var noVal = newRow - 1;
sheetDocu.getRange(newRow, 1).setValue(noVal);
sheetDocu.getRange(newRow, 3).setValue(new Date());
sheetDocu.getRange(newRow, 4).setValue(lpoNo);
sheetDocu.getRange(newRow, 5).setValue(hospital);
sheetDocu.getRange(newRow, 6).setValue(item);
sheetDocu.getRange(newRow, colIndex).setValue(isChecked ? "/" : "");
auditSheet.appendRow([new Date(), params.userId, "DOCUSHARE_CREATE", lpoNo, "Auto-created row " + newRow + " and set " + docKey + " to " + isChecked]);
return ContentService.createTextOutput(JSON.stringify({ status: "success", row: newRow, autoCreated: true })).setMimeType(ContentService.MimeType.JSON);
}
return ContentService.createTextOutput(JSON.stringify({ status: "invalid_col" })).setMimeType(ContentService.MimeType.JSON);
}
}
function getFirstEmptyRowInColD(sheet) {
var values = sheet.getRange("D1:D").getValues();
for (var i = 1; i < values.length; i++) {
if (!values[i][0] || values[i][0].toString().trim() === "") return i + 1;
}
return values.length + 1;
}
function findRowByLpoNo(sheet, lpoNo) {
var values = sheet.getRange("D1:D").getValues();
for (var i = 1; i < values.length; i++) {
if (values[i][0] && values[i][0].toString().trim() === lpoNo.toString().trim()) {
return i + 1;
}
}
return -1;
}
function findRowByLpoNoAndItem(sheet, sheetLpoNo, itemName) {
if (!itemName) return findRowByLpoNo(sheet, sheetLpoNo);
var values = sheet.getRange("A1:F").getValues();
for (var i = 1; i < values.length; i++) {
var rowLpo = values[i][3] ? values[i][3].toString().trim() : "";
var rowItem = values[i][5] ? values[i][5].toString().trim() : "";
if (rowLpo === sheetLpoNo.toString().trim() && rowItem === itemName.toString().trim()) {
return i + 1;
}
}
return findRowByLpoNo(sheet, sheetLpoNo);
}
function getDocuColIndex(docKey) {
var map = { po: 7, invSupplier: 8, do: 9, inv: 10, doEp: 11, invEp: 12, invJan: 13 };
return map[docKey] || 0;
}
function syncDocuRow(sheetDocu, row, noVal, lpoNo, hospital, item) {
sheetDocu.getRange(row, 1).setValue(noVal);
sheetDocu.getRange(row, 3).setValue(new Date());
sheetDocu.getRange(row, 4).setValue(lpoNo);
sheetDocu.getRange(row, 5).setValue(hospital);
sheetDocu.getRange(row, 6).setValue(item);
sheetDocu.getRange(row, 7).setValue("/");
}