Track App Scanner Backend Gas Code Success ✅
// ==========================================
// PT SEVA: DINE-IN SAAS BACKEND (GAS) v13.7
// Model: Smart Subscription Dates + Monthly Sale Shift + Gemini AI Scanner
// Status: Column Mapping Fixed (V, W, X, Y) & Bug-Free
// =========================================
const SPREADSHEET_ID = "18hjPH8aBJmT8xzCU1k56QIVvH_u4R79xk31wBVA-fY4";
const S_USERS = "Users";
const S_REST = "Restaurants";
const S_MENU = "Menu";
const S_ORDERS = "Orders";
const S_TXN = "Transactions";
const S_ARCHIVE = "Archive";
const S_ADMIN = "Admin_Settings";
const S_CUSTOMERS = "Customers";
function jsonResponse(data) {
return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}
function doGet(e) {
if (!e || !e.parameter || !e.parameter.action) {
return HtmlService.createHtmlOutputFromFile('Index')
.setTitle('PT Seva Foodie & Partner App')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
.addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
const action = e.parameter.action;
if (action === "getMenu") return jsonResponse(fetchLiveMenu(e.parameter.restId || "MFF_01"));
if (action === "checkStatus") return jsonResponse(checkOrderStatus(e.parameter.orderId));
if (action === "restLogin") return jsonResponse(restLogin(e.parameter.id, e.parameter.pass));
if (action === "getRestStats") return jsonResponse(getRestStats(e.parameter.restId));
if (action === "getOwnerMenu") return jsonResponse(getOwnerMenu(e.parameter.restId));
if (action === "getCustomers") return jsonResponse(getMyCustomers(e.parameter.restId));
if (action === "toggleStore") return jsonResponse(toggleStore(e.parameter.restId));
if (action === "toggleItem") return jsonResponse(toggleItem(e.parameter.itemId, e.parameter.status));
if (action === "updatePrice") return jsonResponse(updateItemPrice(e.parameter.itemId, e.parameter.price));
}
function doPost(e) {
try {
const req = JSON.parse(e.postData.contents);
if (req.action === "placeOrder") return jsonResponse(placeOrder(req.orderPayload));
if (req.action === "registerRest") return jsonResponse(registerRestaurant(req.payload));
if (req.action === "addMenuItem") return jsonResponse(addMenuItem(req.payload));
if (req.action === "submitUTR") return jsonResponse(submitMonthlyUTR(req.payload));
if (req.action === "updateStatus") return jsonResponse(updateOrderStatus(req.payload.orderId, req.payload.newStatus, req.payload.etaTime));
if (req.action === "updateOrderItems") return jsonResponse(updateOrderItems(req.payload.orderId, req.payload.itemsStr));
if (req.action === "updateProfile") return jsonResponse(updateProfile(req.payload));
// 🔥 NEW: GEMINI OCR SCANNER ROUTE 🔥
if (req.action === "scanBill") return jsonResponse(processOCRWithGemini(req.payload.base64Image));
} catch (error) {
return jsonResponse({ status: "error", message: "Invalid Request Format" });
}
}
function getSheet(sheetName) {
return SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(sheetName);
}
function fetchLiveMenu(restId) {
try {
const restSheet = getSheet(S_REST).getDataRange().getValues();
let isStoreOpen = false;
let rName = "", rUpi = "", rQr = "";
for (let i = 1; i < restSheet.length; i++) {
if (restSheet[i][1] === restId) {
if (restSheet[i][11] === "OPEN") isStoreOpen = true;
rName = restSheet[i][3];
rUpi = restSheet[i][19];
rQr = restSheet[i][20];
break;
}
}
if (!isStoreOpen) return { status: "error", message: "Sorry, we are currently closed. Please try again later." };
const menuSheet = getSheet(S_MENU).getDataRange().getValues();
let menuData = [];
for (let i = 1; i < menuSheet.length; i++) {
if (menuSheet[i][0] === restId && menuSheet[i][9] === "Active") {
menuData.push({
id: menuSheet[i][1], name: menuSheet[i][2], desc: menuSheet[i][3],
price: menuSheet[i][4], type: menuSheet[i][5], category: menuSheet[i][6],
img: menuSheet[i][7], unit: menuSheet[i][8]
});
}
}
return { status: "success", data: menuData, restName: rName, restUpi: rUpi, restQr: rQr };
} catch (e) { return { status: "error", message: e.message }; }
}
function placeOrder(orderPayload) {
try {
const sheet = getSheet(S_ORDERS);
const orderId = "ORD" + Math.floor(10000 + Math.random() * 90000);
const timeNow = new Date();
let tableNumOnly = 0;
let tMatch = String(orderPayload.tableNumber || "").replace(/\[.*?\]/g, '').match(/T(\d+)/i);
if (tMatch) tableNumOnly = parseInt(tMatch[1], 10);
const decimal = tableNumOnly > 0 ? (tableNumOnly / 100) : 0;
const subTotal = parseFloat(orderPayload.subTotal) || 0;
const grandTotal = orderPayload.grandTotal ? parseFloat(orderPayload.grandTotal) : (subTotal + decimal);
let finalInstr = orderPayload.instructions || "";
if (orderPayload.isParcel === true) finalInstr = "[📦 PARCEL] " + finalInstr;
const orderType = orderPayload.orderType || "ONLINE";
const refId = orderPayload.refId || "";
const tokenPhotoUrl = orderPayload.tokenPhotoUrl || "";
const isPriority = orderPayload.isPriority || "FALSE";
const custType = orderPayload.customerType || "New";
sheet.appendRow([
timeNow, orderId, orderPayload.userPhone, orderPayload.restMobile,
orderPayload.restId, orderPayload.restName, orderPayload.items,
subTotal.toFixed(2), grandTotal.toFixed(2), decimal.toFixed(2),
orderPayload.tableNumber, orderPayload.payMode, "PENDING",
timeNow.toLocaleTimeString(), finalInstr, orderPayload.isParcel,
custType, "", "", "",
orderType, refId, tokenPhotoUrl, isPriority
]);
try {
const custSheet = getSheet(S_CUSTOMERS);
const custData = custSheet.getDataRange().getValues();
let isExistingCustomer = false;
for (let i = 1; i < custData.length; i++) {
if (custData[i][1] === orderPayload.restId && custData[i][2] == orderPayload.userPhone) {
custSheet.getRange(i + 1, 4).setValue(Number(custData[i][3]) + 1);
custSheet.getRange(i + 1, 5).setValue(timeNow);
isExistingCustomer = true; break;
}
}
if (!isExistingCustomer) custSheet.appendRow([ timeNow, orderPayload.restId, orderPayload.userPhone, 1, timeNow ]);
} catch (crmError) { }
return { status: "success", orderId: orderId, grandTotal: grandTotal.toFixed(2) };
} catch (e) { return { status: "error", message: e.message }; }
}
function checkOrderStatus(orderId) {
try {
const sheet = getSheet(S_ORDERS);
const data = sheet.getDataRange().getValues();
const now = new Date();
for (let i = data.length - 1; i >= 1; i--) {
if (data[i][1] === orderId) {
let stat = data[i][12];
let eta = data[i][19] || "";
let itemsStr = data[i][6] || "";
let payMode = data[i][11] || "";
// Prevent Auto-Cancel for Offline SCANNED & VIP Verify Orders
if (stat === "PENDING" && payMode !== "SCANNED" && payMode !== "UPI_VIP_VERIFY") {
let orderTime = new Date(data[i][0]);
if (Math.floor((now - orderTime) / 60000) >= 10) {
sheet.getRange(i + 1, 13).setValue("CANCELLED - AUTO");
stat = "CANCELLED - AUTO";
}
}
return { status: "success", orderStatus: stat, eta: eta, items: itemsStr };
}
}
return { status: "error", message: "Order not found" };
} catch (e) { return { status: "error", message: e.message }; }
}
function restLogin(phone, pass) {
try {
const sheet = getSheet(S_REST).getDataRange().getValues();
for (let i = 1; i < sheet.length; i++) {
if (sheet[i][6] == phone && sheet[i][7] == pass) {
if (sheet[i][18] !== "ACTIVE") return { status: "FAIL", msg: "Account pending Admin approval." };
return { status: "SUCCESS", restId: sheet[i][1], name: sheet[i][3] };
}
}
return { status: "FAIL", msg: "Invalid Mobile or Password." };
} catch (e) { return { status: "FAIL", msg: e.message }; }
}
function getRestStats(restId) {
try {
const restSheet = getSheet(S_REST).getDataRange().getValues();
let currentStatus = "CLOSED", profileData = {};
let aggregatedMonthlySale = 0, subFee = 0, subDue = 0;
for (let i = 1; i < restSheet.length; i++) {
if (restSheet[i][1] === restId) {
currentStatus = restSheet[i][11];
// Column Index Alignment
// V (21) = Current_Month_Sale
// W (22) = Subscription_Fee
// Y (24) = Subscription_Due (Assumed next available column after Last_Month_Sale)
aggregatedMonthlySale = parseFloat(restSheet[i][21]) || 0;
subFee = parseFloat(restSheet[i][22]) || 0;
subDue = parseFloat(restSheet[i][24]) || 0;
profileData = { mobile: restSheet[i][6], email: restSheet[i][13], fssai: restSheet[i][5], city: restSheet[i][8], address: restSheet[i][9], serviceType: restSheet[i][4], upiId: restSheet[i][19], qrImage: restSheet[i][20], image: restSheet[i][10] };
break;
}
}
let todayStr = new Date().toDateString();
let todayOrders = [], totalSaleLive = 0, totalSaleToday = 0;
const now = new Date();
const orderData = getSheet(S_ORDERS).getDataRange().getValues();
for (let i = 1; i < orderData.length; i++) {
if (orderData[i][4] === restId) {
let orderDateObj = new Date(orderData[i][0]);
let orderDate = orderDateObj.toDateString();
let stat = String(orderData[i][12]);
let grandTotal = parseFloat(orderData[i][8]) || 0;
let payMode = orderData[i][11] || "";
let oType = orderData[i][20] || "ONLINE";
let refId = orderData[i][21] || "";
let tokenPic = orderData[i][22] || "";
if (stat === "PENDING" && Math.floor((now - orderDateObj) / 60000) >= 10) {
if (payMode !== "SCANNED" && payMode !== "UPI_VIP_VERIFY") {
getSheet(S_ORDERS).getRange(i + 1, 13).setValue("CANCELLED - AUTO");
stat = "CANCELLED - AUTO";
}
}
if (!stat.includes("REJECTED") && !stat.includes("CANCELLED")) { totalSaleLive += grandTotal; }
if (orderDate === todayStr) {
if (!stat.includes("REJECTED") && !stat.includes("CANCELLED")) { totalSaleToday += grandTotal; }
todayOrders.push({
oid: orderData[i][1], tableNo: orderData[i][10], item: orderData[i][6],
amt: grandTotal.toFixed(2), grandTotal: grandTotal.toFixed(2),
status: stat, time: orderData[i][13], instr: orderData[i][14] || "",
isParcel: orderData[i][15], payMode: payMode, eta: orderData[i][19] || "",
orderType: oType, refId: refId, tokenPic: tokenPic
});
}
}
}
return {
status: "SUCCESS", currentStatus: currentStatus,
sale: totalSaleToday.toFixed(2),
monthlySale: (aggregatedMonthlySale + totalSaleLive).toFixed(2),
subscriptionFee: subFee.toFixed(2),
subscriptionDue: subDue.toFixed(2),
orders: todayOrders.reverse(), ...profileData
};
} catch (e) { return { status: "FAIL", msg: e.message }; }
}
function toggleStore(restId) {
try {
const sheet = getSheet(S_REST);
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][1] === restId) {
let subDue = parseFloat(data[i][24]) || 0; // Check Due from Column Y (Index 24)
let currentStatus = data[i][11];
if (currentStatus === "CLOSED" && subDue > 0) return { status: "FAIL", msg: "⛔ Service Blocked! Please pay your Subscription Due." };
let newStatus = (currentStatus === "OPEN") ? "CLOSED" : "OPEN";
sheet.getRange(i + 1, 12).setValue(newStatus);
return { status: "SUCCESS", currentStatus: newStatus };
}
}
return { status: "FAIL", msg: "Not found" };
} catch (e) { return { status: "FAIL", msg: e.message }; }
}
function registerRestaurant(data) {
try {
const sheet = getSheet(S_REST);
const existing = sheet.getDataRange().getValues();
for (let i = 1; i < existing.length; i++) {
if (existing[i][6] == data.mobile) return { status: "error", message: "Mobile number already registered!" };
}
const restId = "RT" + Math.floor(1000 + Math.random() * 9000);
sheet.appendRow([
new Date(), restId, data.ownerName, data.shopName, data.serviceType,
data.fssai, data.mobile, data.password, data.city, data.address,
data.image, "CLOSED", "0", data.email, "No",
"", "", "", "PENDING", data.upiId, data.qrImage,
0, 999, 0, 0 // Initializing V, W, X, Y columns
]);
return { status: "success", message: "Registration successful. Waiting for Admin Approval.", restId: restId };
} catch (e) { return { status: "error", message: e.message }; }
}
function updateProfile(data) {
try {
const sheet = getSheet(S_REST);
const rows = sheet.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
if (rows[i][1] === data.restId) {
sheet.getRange(i + 1, 5).setValue(data.serviceType); sheet.getRange(i + 1, 6).setValue(data.fssai); sheet.getRange(i + 1, 7).setValue(data.mobile);
if (data.password) sheet.getRange(i + 1, 8).setValue(data.password);
sheet.getRange(i + 1, 9).setValue(data.city); sheet.getRange(i + 1, 10).setValue(data.address);
if (data.image) sheet.getRange(i + 1, 11).setValue(data.image);
sheet.getRange(i + 1, 14).setValue(data.email); sheet.getRange(i + 1, 20).setValue(data.upiId);
if (data.qrImage) sheet.getRange(i + 1, 21).setValue(data.qrImage);
return { status: "success", message: "Profile Updated Successfully!" };
}
}
return { status: "error", message: "Account not found" };
} catch (e) { return { status: "error", message: e.message }; }
}
function getOwnerMenu(restId) {
try {
const menuSheet = getSheet(S_MENU).getDataRange().getValues();
let menuData = [];
for (let i = 1; i < menuSheet.length; i++) {
if (menuSheet[i][0] === restId) {
menuData.push({
id: menuSheet[i][1], nameEn: menuSheet[i][2], price: menuSheet[i][4],
type: menuSheet[i][5], catEn: menuSheet[i][6], img: menuSheet[i][7],
unit: menuSheet[i][8], status: menuSheet[i][9]
});
}
}
return { status: "SUCCESS", items: menuData.reverse() };
} catch (e) { return { status: "FAIL", msg: e.message }; }
}
function addMenuItem(data) {
try {
const sheet = getSheet(S_MENU);
const itemId = "ITM" + Math.floor(1000 + Math.random() * 9000);
sheet.appendRow([ data.restId, itemId, data.itemName, data.description || "", data.price, data.foodType, data.category, data.imageUrl || "", data.unit || "", "Active" ]);
return { status: "success", message: "Item added successfully", itemId: itemId };
} catch (e) { return { status: "error", message: e.message }; }
}
function toggleItem(itemId, newStatus) {
try {
const sheet = getSheet(S_MENU);
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][1] === itemId) { sheet.getRange(i + 1, 10).setValue(newStatus); return { status: "SUCCESS" }; }
}
return { status: "FAIL", msg: "Item not found" };
} catch (e) { return { status: "FAIL", msg: e.message }; }
}
function updateItemPrice(itemId, newPrice) {
try {
const sheet = getSheet(S_MENU);
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][1] === itemId) {
sheet.getRange(i + 1, 5).setValue(Number(newPrice));
return { status: "SUCCESS", message: "Price updated" };
}
}
return { status: "FAIL", msg: "Item not found" };
} catch (e) { return { status: "FAIL", msg: e.message }; }
}
function updateOrderStatus(orderId, newStatus, etaTime) {
try {
const sheet = getSheet(S_ORDERS);
const data = sheet.getDataRange().getValues();
const timeNow = new Date().toLocaleTimeString();
for (let i = 1; i < data.length; i++) {
if (data[i][1] == orderId) {
sheet.getRange(i + 1, 13).setValue(newStatus);
if (newStatus === "COOKING") {
sheet.getRange(i + 1, 18).setValue(timeNow);
if (etaTime) sheet.getRange(i + 1, 20).setValue(etaTime);
}
else if (newStatus === "COMPLETED" || newStatus === "DELIVERED" || newStatus === "READY") {
sheet.getRange(i + 1, 19).setValue(timeNow);
}
return { status: "success", message: "Order marked as " + newStatus };
}
}
return { status: "error", message: "Order not found" };
} catch (e) { return { status: "error", message: e.message }; }
}
function updateOrderItems(orderId, itemsStr) {
try {
const sheet = getSheet(S_ORDERS);
const data = sheet.getDataRange().getValues();
for (let i = data.length - 1; i >= 1; i--) {
if (data[i][1] === orderId) {
sheet.getRange(i + 1, 7).setValue(itemsStr);
return { status: "success", message: "Items updated successfully" };
}
}
return { status: "error", message: "Order not found" };
} catch (e) { return { status: "error", message: e.message }; }
}
function submitMonthlyUTR(data) {
try {
const sheet = getSheet(S_TXN);
const txnId = "TXN" + Math.floor(100000 + Math.random() * 900000);
sheet.appendRow([ new Date(), txnId, data.restId, data.restName, data.billingMonth, data.amountPaid, data.utrNumber, "PENDING", "" ]);
return { status: "success", message: "UTR Submitted. Waiting for Admin Verification." };
} catch (e) { return { status: "error", message: e.message }; }
}
function getMyCustomers(restId) {
try {
const custSheet = getSheet(S_CUSTOMERS).getDataRange().getValues();
let customerList = [];
for (let i = 1; i < custSheet.length; i++) {
if (custSheet[i][1] === restId) { customerList.push({ phone: custSheet[i][2], totalOrders: custSheet[i][3], lastOrderDate: new Date(custSheet[i][4]).toDateString() }); }
}
return { status: "SUCCESS", total: customerList.length, customers: customerList.reverse() };
} catch (e) { return { status: "FAIL", msg: e.message }; }
}
// 🔥 FEATURE: PERFECT COLUMN MAPPING & MONTHLY ROLLOVER
function midnightArchiveCron() {
const ordersSheet = getSheet(S_ORDERS);
const archiveSheet = getSheet(S_ARCHIVE);
const restSheet = getSheet(S_REST);
const ordersData = ordersSheet.getDataRange().getValues();
const restData = restSheet.getDataRange().getValues();
let rowsToDelete = [];
const today = new Date();
let saleToAdd = {};
// 1. Archive Completed/Cancelled Orders & Calculate Today's Sale
for (let i = ordersData.length - 1; i >= 1; i--) {
const status = String(ordersData[i][12]);
if (status === "COMPLETED" || status === "DELIVERED" || status.includes("REJECTED") || status.includes("CANCELLED")) {
if (!status.includes("REJECTED") && !status.includes("CANCELLED")) {
let restId = ordersData[i][4];
let grandTotal = parseFloat(ordersData[i][8]) || 0;
saleToAdd[restId] = (saleToAdd[restId] || 0) + grandTotal;
}
let rowData = ordersData[i];
rowData.push(today);
archiveSheet.appendRow(rowData);
rowsToDelete.push(i + 1);
}
}
rowsToDelete.sort((a,b) => b-a).forEach(rowIdx => ordersSheet.deleteRow(rowIdx));
// 2. Monthly Sale Rollover & Smart Subscription Check
const isFirstDayOfMonth = (today.getDate() === 1);
for (let i = 1; i < restData.length; i++) {
let restId = restData[i][1];
// Column V (21) = Current_Month_Sale
// Column W (22) = Subscription_Fee
// Column X (23) = Last_Month_Sale
// Column Y (24) = Subscription_Due (New safe column for Due)
let currentSale = parseFloat(restData[i][21]) || 0;
let subFee = parseFloat(restData[i][22]) || 0;
let currentDue = parseFloat(restData[i][24]) || 0;
// Column R (17) = Subscription_End_Date
let endDateVal = restData[i][17];
// 🗓️ Monthly Sale Rollover (1st of Month)
if (isFirstDayOfMonth) {
restSheet.getRange(i + 1, 24).setValue(currentSale); // Put Current Sale into Last_Month_Sale (Col X)
restSheet.getRange(i + 1, 22).setValue(saleToAdd[restId] || 0); // Reset Current Sale to today's sale (Col V)
} else {
if (saleToAdd[restId] > 0) {
restSheet.getRange(i + 1, 22).setValue(currentSale + saleToAdd[restId]); // Add to Current Sale
}
}
// 🔐 Smart Subscription Checking
if (endDateVal) {
let endDate = new Date(endDateVal);
if (!isNaN(endDate.getTime()) && today.getTime() > endDate.getTime()) {
if (currentDue === 0 && subFee > 0) {
restSheet.getRange(i + 1, 25).setValue(subFee); // Add Due to Column Y (Index 24)
restSheet.getRange(i + 1, 12).setValue("CLOSED"); // Lock Store
}
}
}
}
// 3. Clean up Archive older than 7 Days
const archData = archiveSheet.getDataRange().getValues();
for (let i = archData.length - 1; i >= 1; i--) {
let archDate = new Date(archData[i][24]);
let diffDays = Math.floor((today - archDate) / (1000 * 60 * 60 * 24));
if (diffDays >= 7) {
archiveSheet.deleteRow(i + 1);
}
}
}
// =========================================
// 🔥 GEMINI AI OCR ENGINE (SMART JSON MODE)
// ==========================================
function processOCRWithGemini(base64Image) {
try {
// 🔥 APNI GEMINI API KEY YAHAN DAALEIN 🔥
var apiKey = "AIzaSyBbJ-Pv1df886JSvHA9zkJTe-G-iBHJGXw";
var url = "https://generativelanguage.googleapis.com/v1beta/models/gemini-3.5-flash-lite:generateContent?key=" + apiKey;
// 🔥 MAGIC PROMPT (UPDATED): AI ko Token No. aur Cart Items dono ek JSON Object mein dene ka order diya
var payload = {
"contents": [{
"parts": [
{"text": "Analyze this image. If it is a restaurant receipt or KOT, extract the Token/Bill/Order number and ONLY the ordered food/drink items with their quantities. Return the result STRICTLY as a JSON object. Example Format: {\"tokenNo\": \"25\", \"items\": [{\"name\": \"Dosa Spl\", \"qty\": 1}, {\"name\": \"Water Bottle 1lt\", \"qty\": 2}]}. If no token number is found, leave it empty: \"\". If the image is NOT a receipt or has no food items, return empty items: {\"tokenNo\": \"\", \"items\": []}. Do NOT include prices, totals, GST, or any other text. Do NOT wrap it in ```json markdown, just output the pure JSON object."},
{
"inline_data": {
"mime_type": "image/jpeg",
"data": base64Image
}
}
]
}]
};
var options = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify(payload),
"muteHttpExceptions": true
};
var response = UrlFetchApp.fetch(url, options);
var json = JSON.parse(response.getContentText());
if (json.error) {
return { status: "error", message: json.error.message };
}
var aiResponse = json.candidates[0].content.parts[0].text;
// Clean markdown tags if AI sends them by mistake
aiResponse = aiResponse.replace(/```json/gi, '').replace(/```/g, '').trim();
return { status: "success", text: aiResponse };
} catch(e) {
return { status: "error", message: "Gemini API Error: " + e.toString() };
}
}