this is my code.gs code const urlSS = "https://doc...
Created on: May 19, 2025
Question
this is my code.gs code
const urlSS = "https://docs.google.com/spreadsheets/d/1y0wVEkSryKpgcPbgwrEaRd5p_zxIyWZ-mvkv1r-2oLo/edit#gid=917510078";
const urlSSWinter = "https://docs.google.com/spreadsheets/d/1X9UBEqsBgIQ7_r1jDNmy83Ux8jN5FUgrdCyQshLuzNk/edit?gid=1446508693#gid=1446508693";
const urlSSSplCompre = "https://docs.google.com/spreadsheets/d/1y_Fc01YqUw9AdSQBIeEWU4w6GB0Pw5dHCJkhR-N4FDk/edit?gid=0#gid=0";
const urlSSSolapur = "https://docs.google.com/spreadsheets/d/1GUSLzi9qb-J0dXL39Ps0VNdRFk3bP0fmkyjGL9NMjX8/edit?gid=0#gid=0";
currCenter = "☠ Wrong Link ☠";
currUser = "☠ Wrong Link ☠";
var courseviseAY = {};
const messagesArr = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Hidden Data").getRange("A7:Z").getValues().filter(row => row.some(cell => (cell.length > 2)));
let [x1, admissionFormLinkNormal, x3, admissionFormLinkWinter, x5, admissionFormCompreSummer, x7, x8, x9, admissionFormLinkSplCompre, x11, admissionFormLinkSolapur] = SpreadsheetApp.openByUrl(urlSS).getRange("A6:L6").getValues()[0];
// const admissionFormLinkNormal = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Hidden Data").getRange("B6").getValue();
// const admissionFormLinkWinter = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Hidden Data").getRange("D6").getValue();
// const admissionFormLinkSplCompre = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Hidden Data").getRange("J6").getValue();
const admissionFormWinterData = SpreadsheetApp.openByUrl(urlSSWinter).getSheetByName("Courses").getRange("A2:C").getValues().filter(row => row.some(cell => (cell.length > 2)));
const admissionFormSplCompreData = SpreadsheetApp.openByUrl(urlSSSplCompre).getSheetByName("Courses").getRange("A2:A").getValues().filter(row => row.some(cell => (cell.length > 2)));
const admissionFormSolapurData = SpreadsheetApp.openByUrl(urlSSSolapur).getSheetByName("Courses").getRange("A2:A").getValues().filter(row => row.some(cell => (cell.length > 2)));
// const admissionFormCompreSummer = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Hidden Data").getRange("F6").getValue();
const courseviseEntDetails = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Entrance Data Addition Details").getRange("A2:B").getValues().filter(row => row.some(cell => (cell.length > 2)));
const admDb="Rajat";
//Get Dropdown Values
const dropData = SpreadsheetApp
.openById("1y0wVEkSryKpgcPbgwrEaRd5p_zxIyWZ-mvkv1r-2oLo")
.getSheetByName("Pending Walk-Ins")
.getRange("A2:G")
.getValues();
const courseCol = 0;
const entDtCol = 1;
const ayCol = 3;
const stdCol = 2;
const enqUIDFormatCol = 4;
const nearCenCol = 5;
const courseForEntDataAdditionCol = 6;
for (let i = 0; i < dropData.length; i++){
if (dropData[i][courseCol].length > 2) {
courseviseAY[dropData[i][courseCol]] = dropData[i][ayCol];
}
}
Logger.log(courseviseAY);
function doGet() {
return HtmlService.createHtmlOutputFromFile('main').setTitle("New Enquiries");
}
function initHash(hash) {
let opc_dropdownData = [];
let retCourses = [];
let myStudentsTabCourses = [];
let is_OPC = "";
if (hash.length < 2) return {"currCenter": currCenter, "currUser": currUser};
var usrData = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Users").getRange("A1:H").getValues();
usrData.forEach(([code, center, usr, opc_dropdown, courses, isOPC, isAFP, myStudentsCourses], i) => {
if (i > 0) {
if (code === hash) {
currCenter = center;
currUser = usr;
opc_dropdownData = opc_dropdown.split(", ");
retCourses = courses.split(", ");
is_OPC = isOPC;
myStudentsTabCourses = myStudentsCourses.split(", ");
}
}
});
return {"currCenter": currCenter, "currUser": currUser, "opc_dropdownData":opc_dropdownData, "courses":retCourses, "is_OPC": is_OPC, "messagesArr": messagesArr, "myStudentsTabCourses": myStudentsTabCourses, "courseviseEntDetails" : courseviseEntDetails};
}
function getDropdownValues(){
let courses = [];
let nearCenter = [];
let eeds = [];
let standards = [];
let ay = [];
let courseForEntDataAddition = [];
for (let i=0; i<dropData.length; i++){
if (dropData[i][courseCol].length > 2) {
// Push Courses
courses.push(dropData[i][courseCol]);
text//Push Entrance Dates if (dropData[i][entDtCol].toString() === "On the Spot") { eeds.push([dropData[i][entDtCol]]); // On the Spot //Logger.log("i: "+i+"\tIn block 1"); } else if (dropData[i][entDtCol].toString().search(", ") > 0) { eeds.push(dropData[i][entDtCol].split(", ")); //Logger.log("i: "+i+"\tIn block 2"); } else if (dropData[i][entDtCol].toString().length > 0) { eeds.push([formatDateSec(dropData[i][entDtCol])]); //Logger.log("i: "+i+"\tIn block 3"); } else{ eeds.push([""]); //Logger.log("i: "+i+"\tIn block 4"); } // Push Standards standards.push(dropData[i][stdCol].split(", ")); // Push AY ay.push(dropData[i][ayCol]); courseviseAY[dropData[i][courseCol]] = dropData[i][ayCol]; //Push Nearest Center nearCenter.push(dropData[i][nearCenCol].split(", ")); // Course Entrance Details courseForEntDataAddition.push(dropData[i][courseForEntDataAdditionCol].split(", ")); }
}
let sheet_dict = {"courses": courses, "eeds": eeds, "standards": standards, "nearCenter": nearCenter, "soe": ["Received a call", "Center Walk-In", "Assigned from Website"], "ay": ay, "courseForEntDataAddition": courseForEntDataAddition};
Logger.log(sheet_dict);
return sheet_dict;
}
function formatDateSec(inp){
// Create a date object from a date string
let date = new Date(Number(inp));
//Logger.log(date);
// Get year, month, and day part from the date
let year = date.toLocaleString("default", { year: "numeric" });
let month = date.toLocaleString("default", { month: "2-digit" });
let day = date.toLocaleString("default", { day: "2-digit" });
// Generate yyyy-mm-dd date string
return year + "-" + month + "-" + day;
// Prints: yyyy-mm-dd
}
function formatDate(inp){
// Create a date object from a date string
let date = new Date(Number(inp)*1000);
// Get year, month, and day part from the date
let year = date.toLocaleString("default", { year: "numeric" });
let month = date.toLocaleString("default", { month: "2-digit" });
let day = date.toLocaleString("default", { day: "2-digit" });
// Generate yyyy-mm-dd date string
return year + "-" + month + "-" + day;
// Prints: yyyy-mm-dd
}
function rearrangeColsFromFile(arr2D, headers) {
var headerIndices = [];
headers.forEach((h) => {
arr2D[0].forEach((d, i) => {
if (h === d) headerIndices.push(i);
});
});
var retVal = [];
arr2D.forEach((r) => {
var currRow = [];
headerIndices.forEach((i) => {
currRow.push(r[i]);
});
retVal.push(currRow);
});
return retVal;
}
function getWalkInsData(opc){
//walkInsData = SpreadsheetApp.openByUrl(urlWalkInsData).getSheetByName("Walk-Ins Data").getDataRange().getValues()/getRange("A2:G").getValues()/;
let qStr = "select entry_ts, st_name, email, mobile, pov, remarks from "+ admDb +".walkin where opc = '"+opc+"' and is_new_student and is_pending ORDER BY entry_ts"
let walkInsData = BQLib.search("btdata", qStr);
if(walkInsData == null) return []; else walkInsData.shift();
walkInsData.forEach((row) => {
if (row[4].slice(0, 3) === '~~{') row[4] = JSON.parse(row[4].slice(2));
});
return walkInsData;
}
function newFormatDate(inp, x) {
if (x === "dd") {
let d = inp.split('-');
return [d[2], d[1], d[0]].join('-');
}
else if (x === "mm") {
let d = inp.split('-');
return [d[2], d[0], d[1]].join('-');
}
else if (x === "mon") {
let d = new Date(inp);
// const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
// let dformat = ${('' + d.getDate()).padStart(2, '0')}-${months[d.getMonth()]}-${d.getFullYear()};
// return dformat;
// let d = new Date(inp);
// console.log(d)
let dformat = [d.getFullYear(), ('' + (d.getMonth()+1)).padStart(2, '0'), ('' + d.getDate()).padStart(2, '0')].join('-');
return dformat;
}
else if (x === "dd/"){
let d = inp.split('/');
return [d[2], d[1], d[0]].join('-');
}
}
function test(){
// Logger.log(BQLib.getEnquiryUID(""+ admDb +"", "enq_data", "B26{L}{L}{nnnnn}{N}{N}")); // C26NM0011801
Logger.log(hashGeneration("A25097146"));
// Logger.log(getJEEYear("7th", "2024-25"))
// getWalkInsData("Nilam Sarda")
// let rankviseList = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1gPcKNnlWKpgkiz_grxa5uxK6NtggQ848ZZj-iWonX7M/edit?gid=1902829259#gid=1902829259").getSheetByName("ADVANCE FOUNDATION 24-25");
// let ent_roll_no = rankviseList.getRange("A2:A40").getValues();
// for (let i = 0; i < ent_roll_no.length; i++){
// rankviseList.getRange("K" + (2 + i)).setValue(hashGeneration(ent_roll_no[i][0]));
// }
// let urlSS = "https://docs.google.com/spreadsheets/d/1gPcKNnlWKpgkiz_grxa5uxK6NtggQ848ZZj-iWonX7M/edit?gid=1902829259#gid=1902829259";
// let start = 2;
// let end = "118";
// let arr = SpreadsheetApp.openByUrl(urlSS).getSheetByName("BASIC FOUNDATION 24-25").getRange("A" + start + ":A" + end).getValues();
// // Logger.log(newFormatDate(arr[0][0]));
// let y = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Final Merge for DB").getRange("R" + start + ":R" + end).getValues();
// for (let i = 0; i < arr.length; i++) {
// // let y = SpreadsheetApp.openByUrl(urlSS).getSheetByName("Final Merge for DB").getRange("R" + (start + i)).getValue();
// if (y[i][0] === "dd" || y[i][0] === "mm" || y[i][0] === "mon" || y[i][0] === "dd/") SpreadsheetApp.openByUrl(urlSS).getSheetByName("Final Merge for DB").getRange("R" + (start + i)).setValue(newFormatDate(arr[i][0], y[i][0]));
// else Logger.log(i);
// }
// Logger.log();
}
function bestMatch(walkInsData){
// Now walkInsData = [Stud Name, Contact, Email]
//Logger.log(walkInsData);
let emailArr = walkInsData[2].split(", ");
let mobArr = walkInsData[1].split(", ");
let qStrEmail1 = "email1 IN (";
let qStrEmail2 = "email2 IN (";
for (let i = 0; i < emailArr.length; i++){
qStrEmail1 += "'" + emailArr[i] + "', ";
qStrEmail2 += "'" + emailArr[i] + "', ";
}
qStrEmail1 = qStrEmail1.slice(0, qStrEmail1.length - 2);
qStrEmail1 += ")";
qStrEmail2 = qStrEmail2.slice(0, qStrEmail2.length - 2);
qStrEmail2 += ")";
let qStrmob1 = "mob1 IN ("
let qStrmob2 = "mob2 IN ("
for (let i = 0; i < mobArr.length; i++){
qStrmob1 += "'" + mobArr[i] + "', ";
qStrmob2 += "'" + mobArr[i] + "', ";
}
qStrmob1 = qStrmob1.slice(0, qStrmob1.length - 2);
qStrmob1 += ")";
qStrmob2 = qStrmob2.slice(0, qStrmob2.length - 2);
qStrmob2 += ")";
let qStr = "Select ent_roll_no Entrance Roll Number, course Course, st_name Student Name, email1 Primary EMail, email2 Secondary EMail, mob1 Primary Mobile, mob2 Secondary Mobile from "+ admDb +".enq_data where " + qStrEmail1 + " OR " + qStrEmail2 + " OR " + qStrmob1 + " OR " + qStrmob2 + " order by entry_ts desc limit 10";
Logger.log(qStr);
return BQLib.search("btdata", qStr);
}
function bestMatch2(mob1, mob2, email1, email2){
let qStr = "SELECT st_name AS Student Name, email1 AS Primary Email, email2 AS Secondary Email, mob1 AS Primary Mobile, mob2 AS Secondary Mobile, course AS Course FROM "+ admDb +".enq_data WHERE (LENGTH(email1)>=4 AND email1 = '"+email1+"') OR (LENGTH(email2)>=4 AND email2 = '"+email1+"') OR (LENGTH(email1)>=4 AND email1 = '"+email2+"') OR (LENGTH(email2)>=4 AND email2 = '"+email2+"') OR (LENGTH(mob1)>=4 AND mob1 = '"+mob1+"') OR (LENGTH(mob2)>=4 AND mob2 = '"+mob2+"') OR (LENGTH(mob1)>=4 AND mob1 = '"+mob2+"') OR (LENGTH(mob2)>=4 AND mob2 = '"+mob1+"') GROUP BY st_name, email1, email2, mob1, mob2, course ORDER BY ((CASE WHEN LENGTH(email1)>=4 AND email1 = '"+email1+"' THEN 1 ELSE 0 END) + (CASE WHEN LENGTH(email1)>=4 AND email1 = '"+email2+"' THEN 1 ELSE 0 END) + (CASE WHEN LENGTH(email2)>=4 AND email2 = '"+email1+"' THEN 1 ELSE 0 END) + (CASE WHEN LENGTH(email2)>=4 AND email2 = '"+email2+"' THEN 1 ELSE 0 END) + (CASE WHEN LENGTH(mob1)>=4 AND mob1 = '"+mob1+"' THEN 1 ELSE 0 END) + (CASE WHEN LENGTH(mob1)>=4 AND mob1 = '"+mob2+"' THEN 1 ELSE 0 END) + (CASE WHEN LENGTH(mob2)>=4 AND mob2 = '"+mob1+"' THEN 1 ELSE 0 END) + (CASE WHEN LENGTH(mob2)>=4 AND mob2 = '"+mob2+"' THEN 1 ELSE 0 END)) DESC LIMIT 10;"
return BQLib.search("btdata", qStr);
}
function duplicateClicked(duplicateEntry){
// timestamp, email, mobile, reason
let qStr = "update btdata."+ admDb +".walkin set is_pending = false, is_duplicate = true, form_fill_ts = current_timestamp(), remarks = CASE WHEN remarks IS NULL THEN '~~EnquiryNotFilled : " + duplicateEntry[3].replace(//g, '').replace(/'/g, "\'") + "' ELSE CONCAT(remarks, '~~EnquiryNotFilled : " + duplicateEntry[3].replace(//g, '').replace(/'/g, "\'") + "') END where entry_ts = " + (duplicateEntry[0].includes(".")?("TIMESTAMP_SUB(TIMESTAMP('" + duplicateEntry[0] + "'), INTERVAL 330 MINUTE)"):("'" + duplicateEntry[0] + "'")) + " and email = '" + duplicateEntry[1] + "' and mobile = '" + duplicateEntry[2] + "'";
Logger.log(qStr);
BQLib.runQuery("btdata", qStr);
}
function setPendingFalse(entry){
// timestamp, email, mobile
//Logger.log(entry);
let qStr = "update btdata."+ admDb +".walkin set is_pending = false, form_fill_ts = current_timestamp() where entry_ts = "+(entry[0].includes(".")?("TIMESTAMP_SUB(TIMESTAMP('" + entry[0] + "'), INTERVAL 330 MINUTE)"):("'" + entry[0] + "'"))+" and email = '"+entry[1]+"' and mobile = '"+entry[2]+"'";
Logger.log(qStr);
BQLib.runQuery("btdata", qStr);
}
function updateRemarksClicked(entry){
// [walkInsData[counter][date_index]+(walkInsData[counter][date_index].includes(".")?"":"+05:30"), walkInsData[counter][email_index], walkInsData[counter][contact_index], document.getElementById("walkinRemarksText").value]
let qStr = "update btdata."+ admDb +".walkin set remarks = '" + entry[3].replace(/'/g, "\'") + "' where entry_ts = "+(entry[0].includes(".")?("TIMESTAMP_SUB(TIMESTAMP('" + entry[0] + "'), INTERVAL 330 MINUTE)"):("'" + entry[0] + "'"))+" and email = '"+entry[1]+"' and mobile = '"+entry[2]+"'";
Logger.log(qStr);
return BQLib.runQuery("btdata", qStr);
}
async function submitClicked(arr){
// let titles = ['Date', 'Student Name', 'Pri Con', 'Sec Con', 'Pri EMail', '5-> Sec EMail', 'board', 'school', '8-> std1', 'std2', 'hometown', 'nearest center', 'source of enq', '13-> ent_date', 'ent_rec_no', 'remarks', 'opc', '17-> acadYear', 'course', entranceDetailsJSON, entDetailsCourse]
const nextCallingDate=arr[21];
let isEntranceRecNoPresent = (arr[14].length > 3?await BQLib.search("btdata", "SELECT enq_uid AS Enquiry UID FROM "+ admDb +".enq_data WHERE ent_rec_no = '" + arr[14].trim() + "' AND (course NOT LIKE '%Solapur%' AND course NOT LIKE '%Pandharpur%' AND course NOT LIKE '%Barshi%');"):null);
if (isEntranceRecNoPresent == null){
let jeeYear = getJEEYear((arr[9].length == 0?arr[8]:arr[9]), arr[17].toString());
textlet enqUIDformat = ""; dropData.forEach((r, i)=>{ if(r[courseCol] === arr[18]){ enqUIDformat = r[enqUIDFormatCol]; } }); let enqUID = BQLib.getEnquiryUID(""+ admDb +"", "enq_data", enqUIDformat); let entRollNo = 'null'; if (arr[14].length > 3){ entRollNo = "'"+enqUID+"'"; } let hash = hashGeneration(enqUID); let qStr = "INSERT INTO `"+ admDb +".enq_data` (" + "entry_ts, enq_dt, st_name, mob1, mob2, email1, email2, " + "nearest_center, enq_source, sch_name, JEE_year, sch_board, hometown, " + "ent_dt, ent_rec_no, opc, remarks, enq_uid, ent_roll_no, course, `hash`, next_calling_date" + ") VALUES (" + "CURRENT_TIMESTAMP(), " + (arr[0].length>0 ? "'"+arr[0]+"'" : "null") + ", " + "'"+arr[1]+"', '"+arr[2]+"', '"+arr[3]+"', '"+arr[4]+"', '"+arr[5]+"', " + "'"+arr[11]+"', '"+arr[12]+"', '"+arr[7].replace(/'/g,"\\'")+"', " + jeeYear + ", '" + arr[6].replace(/'/g,"\\'")+"', '" + arr[10].replace(/'/g,"\\'")+"', " + (arr[13].length>0 ? "'"+arr[13]+"'" : "null") + ", " + "'"+arr[14].trim()+"', " + "'"+arr[16]+"', " + "'"+arr[15].replace(/'/g,"\\'")+"', " + "'"+enqUID+"', " + entRollNo + ", " + "'"+arr[18]+"', " + "'"+hash+"', " + (nextCallingDate.length>0 ? "'"+nextCallingDate+"'" : "null") + ");"; if (arr[19]){ if (arr[20] != arr[18]) { let hashForThisCourse = hashGeneration(enqUID + "_" + arr[20][0]); qStr += ", (current_timestamp(), "+(arr[0].length>0?"'"+arr[0]+"'":"null")+", '"+arr[1]+"', '"+arr[2]+"', '"+arr[3]+"', '"+arr[4]+"', '"+arr[5]+"', '"+arr[11]+"', '"+arr[12]+"', '"+arr[7].replace(/'/g, "\\'")+"', "+jeeYear+", '"+arr[6].replace(/'/g, "\\'")+"', '"+arr[10].replace(/'/g, "\\'")+"', "+(arr[13].length>0?"'"+arr[13]+"'":"null")+", '"+arr[14].trim()+"', '"+arr[16]+"', '"+arr[15].replace(/'/g, "\\'")+"', '"+enqUID + "_" + arr[20][0]+"', "+entRollNo.slice(0, entRollNo.length - 1)+"_" + arr[20][0] +"', '"+arr[20]+"', '"+hashForThisCourse+"');"; } let ent_data_headers = ['ent_roll_no', 'st_name', 'course']; let ent_data_types = ['STRING', 'STRING', 'STRING']; let ent_data = [String(arr[20] != arr[18]?entRollNo.slice(1, entRollNo.length - 1)+"_" + arr[20][0]: entRollNo.slice(1, entRollNo.length - 1)), arr[1], arr[20]]; for (let x in arr[19]){ ent_data_headers.push(x); if (x != "fee_structure") { ent_data_types.push(arr[19][x]["entDataType"]); ent_data.push(arr[19][x]["value"]); } else { ent_data_types.push("JSON"); ent_data.push(arr[19][x]); } } Logger.log(ent_data_headers); Logger.log(ent_data_types); Logger.log(ent_data); BQLib.addDataFromArray("btdata", ""+ admDb +"", "ent_data", ent_data_headers, ent_data_types, [ent_data]); } Logger.log(qStr); //To be Kept return BQLib.runQuery("btdata", qStr); // if (arr[18] === "H25 - Navi Mumbai") { // let h25feeStructure = {"reg_fees":{"2025-01-01":30000}}; // qStr += "INSERT INTO `"+ admDb +".ent_data` (ent_roll_no, st_name, afp, entrance_dt, course, remarks, fee_structure) VALUES ("+entRollNo+", '"+arr[1]+"', 'Aman Singh', "+(arr[13].length>0?"'"+arr[13]+"'":"null")+", 'H25 - Navi Mumbai', 'Entrance Not Given', JSON '" + JSON.stringify(h25feeStructure) + "');" // } // else if ((arr[13].length > 0 && (arr[13] === '2025-03-29' || arr[13] === '2025-04-04')) && (entRollNo != 'null') && (arr[18] === "C27" || arr[18] === "C27 - Navi Mumbai")){ // let m25feeStructure = {"withComp":{"2024-10-02":27000},"withoutComp":{"2024-10-02":27000, "2025-04-10":24000}}; // qStr += "INSERT INTO `"+ admDb +".ent_data` (ent_roll_no, st_name, afp, entrance_dt, course, remarks, fee_structure) VALUES ("+entRollNo.slice(0, entRollNo.length - 1)+"_M', '"+arr[1]+"', null, "+(arr[13].length>0?"'"+arr[13]+"'":"null")+", 'M25', 'Entered for the parents to opt M25 immediately', JSON '" + JSON.stringify(m25feeStructure) + "');" // } // return BQLib.runQuery("btdata", qStr);
}
else{
throw new Error("This entrance receipt number has already been entered. Please check!")
}
}
function hashGeneration(enqUID) {
const letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
for(let i = 0; i < 6; i++){ // Adding 6 random letters
enqUID += letters.charAt(Math.floor(Math.random() * letters.length));
}
return enqUID;
}
function getEntranceDates(feeperson, courses){
let outputDict = {};
let qStr = "";
let outputArr = [];
for (let i = 0; i < courses.length; i++){
qStr = "SELECT DISTINCT(ent_dt) AS Entrance Date Not Decided FROM "+ admDb +".enq_data WHERE opc = '" + feeperson + "' AND course = '" + courses[i] + "' AND ent_dt IS NOT NULL ORDER BY ent_dt desc";
Logger.log(qStr);
outputArr = BQLib.search("btdata", qStr);
outputDict[courses[i]] = outputArr;
(outputArr == null?outputArr = []:outputArr);
}
return outputDict;
}
function viewDataGoClicked(feeperson, courses, ent_date, entFeesBool){
let enqUIDArr = [];
let data = [];
let msg_json = [];
let jeeYearCol = 13;
let courseCol = 14;
let msg_jsonCol = 26;
let course_qStr = "";
for (let i = 0; i < courses.length; i++){
course_qStr += "'" + courses[i] + "', ";
}
course_qStr = course_qStr.slice(0, course_qStr.length - 2);
let qStr = "WITH adm_form_links AS (SELECT enq.ent_roll_no, 'Yes' AS feesPaidBool FROM "+ admDb +".enq_data AS enq LEFT JOIN native_accounts.all_payments" + ((course_qStr.includes("Solapur") || course_qStr.includes("Pandharpur") || course_qStr.includes("Barshi"))?"_others":"")+" AS all_pay ON enq.ent_roll_no = all_pay.ent_roll_no WHERE enq.course IN (" + course_qStr + ") AND enq.opc = '" + feeperson + "' AND enq.ent_roll_no IS NOT NULL AND LENGTH(enq.ent_roll_no) > 3 GROUP BY enq.ent_roll_no HAVING sum(amount) > 5000) SELECT enq.enq_uid AS Enquiry UID, ROW_NUMBER() OVER (ORDER BY enq.entry_ts DESC, enq.st_name) AS Serial No, enq.ent_dt AS Entrance Date, enq.st_name AS Student Name, enq.mob1 AS Mobile 1, enq.mob2 AS Mobile 2, enq.remarks AS Calling Remarks, enq.email1 AS Email 1, enq.email2 AS Email 2, enq.enq_dt AS Enquiry Date, enq.nearest_center AS Nearest Center, enq.sch_name AS School Name, enq.hometown AS Hometown, enq.JEE_year AS Standard, enq.course AS Course, enq.ent_rec_no AS Entrance Receipt No, enq.ent_roll_no AS Entrance Roll No, CASE WHEN LENGTH(CAST(ent.marks AS STRING)) - INSTR(CAST(ent.marks AS STRING), '.') > 3 THEN ROUND(ent.marks, 3) ELSE ent.marks END AS Marks, ent.batch AS Batch, ent.scholarship AS Scholarship, ent.last_adm_dt AS Last Admission Date, ent.counselling AS Counselling, ent.afp AS AFP, adm.roll_no AS Roll No, CASE WHEN adm_form_links.feesPaidBool IS NULL THEN 'No' ELSE adm_form_links.feesPaidBool END AS Fees Paid, CASE WHEN enq.course = 'C27' AND enq.ent_dt >= '2024-10-26' THEN CONCAT('" + admissionFormLinkNormal + "#', enq.hash) "; // Paste admissionFormCompreSummer admission form link over here when required
// for (let i = 0; i < admissionFormWinterData.length; i++){
// qStr += "WHEN (enq.course = '" + admissionFormWinterData[i][0] + "' AND enq.JEE_year = " + getJEEYear(admissionFormWinterData[i][2], admissionFormWinterData[i][1]) + " AND enq.ent_dt < '2024-12-07') THEN CONCAT('" + admissionFormLinkWinter + "#', enq.hash) "
// }
for (let i = 0; i < admissionFormSplCompreData.length; i++){
qStr += "WHEN (enq.course = '" + admissionFormSplCompreData[i][0] + "') THEN CONCAT('" + admissionFormLinkSplCompre + "#', enq.hash) ";
}
for (let i = 0; i < admissionFormSolapurData.length; i++){
qStr += "WHEN (enq.course = '" + admissionFormSolapurData[i][0] + "') THEN CONCAT('" + admissionFormLinkSolapur + "#', enq.hash) ";
}
qStr += "ELSE CONCAT('" + admissionFormLinkNormal + "#', enq.hash) END AS Admission Form Link, enq.msg_json AS Message JSON FROM "+ admDb +".enq_data AS enq LEFT JOIN "+ admDb +".ent_data AS ent ON (enq.ent_roll_no = " + ((course_qStr.includes("Solapur") || course_qStr.includes("Pandharpur") || course_qStr.includes("Barshi"))?"SUBSTRING(ent.ent_roll_no, 1, LENGTH(enq.ent_roll_no)) AND ent.course IN (" + course_qStr + ")":"ent.ent_roll_no")+" AND enq.ent_roll_no IS NOT NULL AND LENGTH(enq.ent_roll_no) > 3) LEFT JOIN "+ admDb +".adm_data AS adm ON (enq.ent_roll_no = adm.ent_roll_no AND enq.ent_roll_no IS NOT NULL AND LENGTH(enq.ent_roll_no) > 3) LEFT JOIN adm_form_links ON enq.ent_roll_no = adm_form_links.ent_roll_no WHERE enq.opc = '" + feeperson + "'";
if (ent_date === "Entrance Date Not Decided"){
qStr += " AND (enq.ent_dt IS NULL)";
}
else if (ent_date != "All Dates"){
qStr += " AND enq.ent_dt = '"+ent_date+"'";
}
if (entFeesBool === "Entrance Fees Paid"){
qStr += " AND (LENGTH(enq.ent_rec_no) > 3)";
}
else if (entFeesBool === "Entrance Fees Not Paid"){
qStr += " AND (enq.ent_rec_no IS NULL OR LENGTH(enq.ent_rec_no) < 3)";
}
else if (entFeesBool === "Entrance Not Given"){
qStr += " AND (LENGTH(enq.ent_rec_no) > 3 AND ent.marks IS NULL)";
}
else if (entFeesBool === "Course Fee Not Paid"){
qStr += " AND (ent.marks IS NOT NULL AND adm_form_links.feesPaidBool IS NULL)";
}
else if (entFeesBool === "Admission Form Not Filled"){
qStr += " AND (adm_form_links.feesPaidBool IS NOT NULL AND (adm.roll_no IS NULL OR LENGTH(adm.roll_no) < 3))";
}
qStr += " AND (enq.course IN (" + course_qStr + ")) ORDER BY enq.entry_ts DESC, enq.st_name;";
Logger.log(qStr);
let outputArr = BQLib.search("btdata", qStr);
outputArr.forEach((row, j) => {
let currRow1 = [];
let currRow2 = [];
if (j != 0) row[jeeYearCol] = getStandard(row[jeeYearCol], courseviseAY[row[courseCol]]);
row.forEach((r, i) => {
if (i == 0) currRow1.push(r);
else if (i == msg_jsonCol) msg_json.push(r);
else{
currRow2.push(r);
}
})
enqUIDArr.push(currRow1);
data.push(currRow2);
})
//Logger.log({"enqUIDArr": enqUIDArr, "data": data, "msg_json": msg_json});
return {"enqUIDArr": enqUIDArr, "data": data, "msg_json": msg_json};
}
function whatsAppSent(enquiryUID, whatsAppSentBool, keyword){
let qStr = UPDATE \"+ admDb +".enq_data`
SET msg_json = CASE
WHEN msg_json IS NULL THEN JSON '{"{whatsAppSentBool}"}'
ELSE JSON_SET(msg_json, '{keyword}', '{enquiryUID}';`;
Logger.log(qStr);
return BQLib.runQuery("btdata", qStr);
}
function getStandard(jeeYear, acadYear){
let yyyy = Number(acadYear.slice(0, 4));
switch((jeeYear - yyyy)){
case 8:
return '5th';
case 7:
return '6th';
case 6:
return '7th';
case 5:
return '8th';
case 4:
return '9th';
case 3:
return '10th';
case 2:
return '11th';
case 1:
return '12th';
default:
return 'null';
}
}
function updateEnquiryData(enqUID, updateEnqDict){
//Logger.log(updateEnqDict.course);
//Logger.log(courseviseAY[updateEnqDict.course]);
let qStr = "UPDATE "+ admDb +".enq_data SET ent_dt = " + (updateEnqDict.entDt.length < 10?null:"'" + updateEnqDict.entDt + "'") +", st_name = '" + updateEnqDict.stName.replace(/'/g, "\'") +"', mob1 = '" + updateEnqDict.mob1 +"', mob2 = '" + updateEnqDict.mob2 +"', remarks = '" + updateEnqDict.callRem.replace(/\r?\n/g, "\n").replace(/'/g, "\'") +"', email1 = '" + updateEnqDict.email1 +"', email2 = '" + updateEnqDict.email2 +"', sch_name = '" + updateEnqDict.schName.replace(/'/g, "\'") +"', hometown = '" + updateEnqDict.hometown.replace(/'/g, "\'") +"', JEE_year = " + getJEEYear(updateEnqDict.standard, courseviseAY[updateEnqDict.course]) + " WHERE enq_uid = '" + enqUID[0] + "';";
Logger.log(qStr);
Logger.log(BQLib.runQuery("btdata", qStr));
}
function editEntRecNoEnqDataGoClicked(key) {
let enqUIDArr = [];
let data = [];
if((key+"").length < 2){
return {"data": [['Student Name', 'Calling_Remarks', 'Enquiry Date', 'Course', 'Nearest Center', 'School Name', 'Hometown']]}
}
// let qStr = "SELECT transaction_id Transaction ID, value_date Value Date, description Description, amount Amount, rec_no Receipt Number, tran_datetime Transaction Datetime FROM btdata.native_accounts.neft_suspense where isPending and (lower(transaction_id) like '%"+key.toLowerCase()+"%' or lower(description) like '%"+key.toLowerCase()+"%') ORDER BY entry_ts desc LIMIT 10";
let qStr = "SELECT enq_uid Enquiry UID, st_name AS Student Name, remarks AS Calling_Remarks, enq_dt AS Enquiry Date, course AS Course, nearest_center AS Nearest Center, sch_name AS School Name, hometown AS Hometown FROM "+ admDb +".enq_data WHERE (ent_rec_no IS NULL OR LENGTH(ent_rec_no) < 2) and (lower(st_name) like '%"+key.toLowerCase()+"%' OR mob1 = '"+key+"' OR mob2 = '"+key+"' OR email1 = '"+key+"' OR email2 = '"+key+"') ORDER BY enq_dt desc LIMIT 10"
let outputArr = BQLib.search("btdata", qStr);
outputArr.forEach((row) => {
let currRow1 = [];
let currRow2 = [];
row.forEach((r, i) => {
if (i == 0) currRow1.push(r);
else currRow2.push(r);
})
enqUIDArr.push(currRow1);
data.push(currRow2);
})
Logger.log({"enqUIDArr": enqUIDArr, "data": data})
return {"enqUIDArr": enqUIDArr, "data": data};
}
function allPaymentsDataGoClicked(key) {
if((key+"").length < 2){
return [['Student Name', 'Receipt Number', 'Payment Date', 'Amount', 'Payment Remarks']]
}
let qStr = "SELECT st_name Student Name, rec_no Receipt Number, paid_dt Payment Date, amount Amount, remarks Payment Remarks FROM native_accounts.all_payments WHERE ((ent_roll_no IS NULL OR LENGTH(ent_roll_no) < 2) AND amount < 1000) AND ((lower(st_name) like '%"+key.toLowerCase()+"%') OR (lower(rec_no) like '%" + key.toLowerCase() + "%')) ORDER BY paid_dt DESC LIMIT 20";
return BQLib.search("btdata", qStr);
}
function allPaymentsDataDoneClicked(mergedAddToDB, enqUIDs, feeperson){
//mergedAddToDB -> [['Student Name', 'Calling Remarks', 'Enquiry Date', 3. 'Course', 'Receipt Number', 'Payment Date', 'Amount', 7. 'Payment Remarks']];
Logger.log(mergedAddToDB);
Logger.log(enqUIDs);
let qStr = "UPDATE "+ admDb +".enq_data SET ent_rec_no = CASE";
for (let i = 0; i < enqUIDs.length; i++){
qStr += " WHEN enq_uid = '" + enqUIDs[i][0][0] + "' THEN '" + mergedAddToDB[i+1][4] + "'";
}
qStr += " END, ent_roll_no = CASE";
for (let i = 0; i < enqUIDs.length; i++){
qStr += " WHEN enq_uid = '" + enqUIDs[i][0][0] + "' THEN '" + enqUIDs[i][0][0] + "'";
}
qStr += " END WHERE enq_uid IN (";
for (let i = 0; i < enqUIDs.length; i++){
qStr += "'" + enqUIDs[i][0][0] + "', ";
}
qStr = qStr.slice(0, qStr.length - 2);
qStr += "); UPDATE native_accounts.all_payments SET ent_roll_no = CASE";
for (let i = 0; i < enqUIDs.length; i++){
qStr += " WHEN rec_no = '" + mergedAddToDB[i+1][4] + "' THEN '" + enqUIDs[i][0][0] + "'";
}
qStr += " END, remarks = CASE"
for (let i = 0; i < enqUIDs.length; i++){
qStr += " WHEN rec_no = '" + mergedAddToDB[i+1][4] + "' THEN '" + mergedAddToDB[i+1][7] + " Entrance Roll No updated by " + feeperson + " on " + getCurrentDateTime() + "'";
}
qStr += " END, ent_rec_no = rec_no, course_dropdown = CASE";
for (let i = 0; i < enqUIDs.length; i++){
qStr += " WHEN rec_no = '" + mergedAddToDB[i+1][4] + "' THEN '" + mergedAddToDB[i+1][3] + "'";
}
qStr += " END WHERE rec_no IN (";
for (let i = 0; i < enqUIDs.length; i++){
qStr += "'" + mergedAddToDB[i+1][4] + "', ";
}
qStr = qStr.slice(0, qStr.length - 2);
qStr += ");";
for (let i = 0; i < enqUIDs.length; i++){
if ((mergedAddToDB[i+1][3] === 'C27' || mergedAddToDB[i+1][3] === 'C27 - Navi Mumbai')){
let m25feeStructure = {"withComp":{"2024-10-02":27000},"withoutComp":{"2024-10-02":27000, "2025-04-10":24000}};
let c27feeStructure = {"reg_fees":{"2024-10-02":60000,"2025-05-15":60000,"2025-08-15":75000,"2025-11-15":70000,"2026-02-15":70000,"2026-05-15":70000}}
qStr += "INSERT INTO "+ admDb +".ent_data (ent_roll_no, st_name, entrance_dt, course, remarks, fee_structure) SELECT CONCAT(ent_roll_no, '_M'), st_name, ent_dt, 'M25', 'Entered for the parents to opt M25 immediately', JSON '" + JSON.stringify(m25feeStructure) + "' FROM "+ admDb +".enq_data WHERE ent_roll_no = '" + enqUIDs[i][0][0] + "' AND ent_dt IN ('2025-03-29', '2025-04-04'); INSERT INTO "+ admDb +".ent_data (ent_roll_no, st_name, entrance_dt, course, remarks, fee_structure) SELECT ent_roll_no, st_name, ent_dt, course, 'Entered for the parents to opt M25 immediately', JSON '" + JSON.stringify(c27feeStructure) + "' FROM "+ admDb +".enq_data WHERE ent_roll_no = '" + enqUIDs[i][0][0] + "' AND ent_dt IN ('2025-03-29', '2025-04-04');";
}
}
Logger.log(qStr);
return BQLib.runQuery("btdata", qStr);
}
function getCurrentDateTime() {
const now = new Date();
text// Format date as YYYY-MM-DD const date = now.toISOString().split('T')[0]; // Format time as HH:mm (24-hour format) const time = now.toTimeString().split(' ')[0].slice(0, 5); // Combine date and time return `${date} ${time}`;
}
function schDataGoClicked(key, courses){
let course_qStr = "(";
for (let i = 0; i < courses.length; i++){
course_qStr += "enq.course LIKE '" + courses[i] + "%' OR ";
}
course_qStr = course_qStr.slice(0, course_qStr.length - 4);
course_qStr += ")"
let qStr = "";
qStr = "Select enq.ent_dt AS Entrance Date, enq.st_name AS Student Name, enq.mob1 AS Mobile 1, enq.mob2 AS Mobile 2, enq.remarks AS Calling_Remarks, enq.email1 AS Email 1, enq.email2 AS Email 2, enq.enq_dt AS Enquiry Date, enq.nearest_center AS Nearest Center, enq.sch_name AS School Name, enq.hometown AS Hometown, enq.JEE_year AS Standard, enq.course AS Course, enq.opc AS OPC, enq.ent_rec_no AS Entrance Receipt No, enq.ent_roll_no AS Entrance Roll No, ent.marks AS Marks, ent.batch AS Batch, ent.scholarship AS Scholarship, ent.last_adm_dt AS Last Admission Date, ent.counselling AS Counselling, ent.afp AS AFP, adm.roll_no AS Roll No FROM "+ admDb +".enq_data AS enq LEFT JOIN "+ admDb +".ent_data AS ent ON enq.ent_roll_no = ent.ent_roll_no LEFT JOIN "+ admDb +".adm_data AS adm ON ent.ent_roll_no = adm.ent_roll_no where " + course_qStr + " AND (lower(enq.st_name) like '%" + key.toLowerCase() + "%' or lower(enq.nearest_center) Like '%" + key.toLowerCase() + "%' or lower(enq.sch_name) like '%" + key.toLowerCase() + "%' or lower(enq.hometown) like '%" + key.toLowerCase() + "%' OR enq.mob1 = '"+key+"' OR enq.mob2 = '"+key+"' OR lower(enq.email1) = '"+key.toLowerCase()+"' OR lower(enq.email2) = '"+key.toLowerCase()+"') ORDER BY enq.entry_ts DESC, enq.st_name LIMIT 25";
Logger.log(qStr);
return BQLib.search("btdata", qStr);
}
function getJEEYear(std, acadYear){
let yyyy = Number(acadYear.slice(0, 4));
switch(std){
case '5th':
return yyyy+8;
case '6th':
return yyyy+7;
case '7th':
return yyyy+6;
case '8th':
return yyyy+5;
case '9th':
return yyyy+4;
case '10th':
return yyyy+3;
case '11th':
return yyyy+2;
case '12th':
return yyyy+1;
default:
return 'null';
}
}
// Test Follow UPS->>>>>>>>>>>>
function followUpsGoClickedInGS(dateVal,feeperson)
{
if(!dateVal || dateVal.length<10)
{
return [["No data available for this date"]];
}
let qStr=SELECT st_name AS Name, CONCAT(mob1, IF(mob2 IS NOT NULL AND mob2 != '', CONCAT(', ', mob2), '')) AS Phone, course AS Course, remarks AS Remarks, next_calling_date AS \Next Calling Date` FROM `{admDb}.enq_data\` WHERE next_calling_date = '{dateVal}' ORDER BY enq_dt DESC LIMIT 50`;
Logger.log(qStr);
let data=BQLib.search("btdata",qStr);
if(!data || data.length<2)
{
return [["No data available for this date"]];
}
return data;
}
function convertTS2Date(ts){
return new Date(ts);
}
this is my main.html
<!DOCTYPE html> <html> <head> <base target="_top"> <!--Materialise--> <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet"> <!--Materialise--> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css"> <!--Materialise--> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <!--Materialise--> <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script> <!--Reading Excel Files--> <script src="https://unpkg.com/[email protected]/bundle/read-excel-file.min.js"></script> <!--Swal--> <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script> <style> .overflow-container { max-height: 100px; overflow: auto; } td, th { border: 1px solid black; border-collapse: collapse; text-align: center; padding: 16px; } tr.selected { background-color: yellow; } table.center { margin-left: auto; margin-right: auto; } table { border: 1px solid black; border-collapse: collapse; width: 100%; text-align: center; padding: 16px; } table.variableWidth { table-layout: fixed; border: 1px solid black; border-collapse: collapse; width: 100%; text-align: center; padding: 16px; } h1 { background: teal; color: white; text-align: left; padding: 5px } h2{ background: #f5fff5; text-align: center; } h3{ background: #f5fff5; } </style> <script src="https://unpkg.com/[email protected]/bundle/read-excel-file.min.js"></script> </head> <body> <div class="container col s12"> <h1> <img src="https://lh3.google.com/u/0/d/1a_HKCI1HE3Usy8lQBwKE9jTfJw0tiDrN" style="width:62;height:57px;"> Bakliwal Tutorials </h1> <div class="row"> <div class="col s6"> <label for="center">Center</label> <input id="center" type="text" class="input-field" disabled> </div> <div class="col s6"> <label for="feeperson">User</label> <input id="feeperson" type="text" class="input-field" disabled> </div> </div></body> </html>text<!-- PENDING WALK-INS SECTION --> <div id="pendingWalkIns"> <div id="topPart" style="display:none"> <h2 id="myHeader">Pending Enquiries</h2> <div class="row"> <div class="col s3"> <label for="first_name">Student Name</label> <input id="first_name" type="text" class="validate" disabled> </div> <div class="col s3"> <label for="above_email">Email</label> <input id="above_email" type="text" class="validate" disabled> </div> <div class="col s3"> <label for="above_phone">Phone No.</label> <input id="above_phone" type="text" class="validate" disabled> </div> <div class="col s3"> <div class="input-field"> <label> <button class="waves-light btn" id="checkDuplicate" onclick="validateDuplicate()" disabled> Enquiry Not to be Filled </button> </label> </div> </div> </div> <div class="row"> <div class="col s6"> <label for="pov">Purpose of visit</label> <input id="pov" type="text" class="validate" disabled> </div> <div class="col s3"> <label for="enquiryDate">Enquiry Date</label> <input id="enquiryDate" type="text" class="input-field" disabled> </div> </div> <div class="row"> <div class="col s9"> <label for="walkinRemarksText">Remarks</label> <input id="walkinRemarksText" type="text" class="input-field"> </div> <div class="col s3"> <div class="input-field"> <label> <button class="waves-light btn" id="updateRemarksBtn" onclick="updateRemarksClicked()" disabled> Add Remarks </button> </label> </div> </div> </div> <div class="row"></div> <div class="row" style="width: 100%;"> <table id="bestMatchTable"></table> </div> <div class="row"></div> <div class="row"> <div class="col s1"></div> <div class="col s2"> <button class="waves-light btn" id="previous" onclick="previousClicked()" disabled> Previous </button> </div> <div class="col s2"></div> <div class="col s2"> <button class="waves-light btn" id="next" onclick="nextClicked()" disabled> Next </button> </div> <div class="col s2"></div> <div class="col s2"> <button class="waves-light btn" id="fillForm" onclick="fillFormClicked()" disabled> Fill Form </button> </div> </div> </div> <div class="row"></div> <!-- TABS BAR --> <div id="tabs" class="row"> <div id="tabsBar" class="col s12" style="display: none"> <ul class="tabs"> <li id="enquiryFormTab" class="tab col s2"> <a class="active" href="#enquiryForm">Enquiry Form</a> </li> <li id="followUpsTab" class="tab col s2"> <a href="#followUps">Follow ups</a> </li> <li id="viewDataTab" class="tab col s2"> <a href="#viewData">My Students</a> </li> <li class="tab col s2"> <a href="#paymentDetails">Add Entrance Receipt No</a> </li> <li class="tab col s2"> <a href="#schData">Search</a> </li> </ul> </div> <!-- ENQUIRY FORM --> <div id="enquiryForm" class="container" style="width: 100%; display: none"> <div class="row"></div> <h2>Enquiry Form</h2> <div class="row"></div> <div class="row"> <div class="input-field"> <select id="course_dropdown" onchange="entDate()" required> <option value="" selected disabled>Choose Course</option> </select> <label>Course</label> </div> </div> <div class="row"> <div class="col s6"> <label for="date">Enquiry Date</label> <input id="date" type="date" class="input-field"> </div> <div class="col s6"> <label for="stud_name">Student Name</label> <input id="stud_name" type="text" class="validate" required> </div> </div> <div class="row"> <div class="col s4"> <label for="phone1">Primary Contact No.</label> <input id="phone1" type="text" class="validate" required> </div> <div class="col s4"> <label for="phone2">Secondary Contact No.</label> <input id="phone2" type="text" class="validate"> </div> <div class="col s4"> <label for="email1">Primary Email</label> <input id="email1" type="email" class="validate" required> </div> </div> <div class="row"> <div class="col s4"> <label for="email2">Secondary Email</label> <input id="email2" type="email" class="validate"> </div> <div class="col s4"> <label for="board">Board</label> <input id="board" type="text" class="validate"> </div> <div class="col s4"> <label for="school_name">School Name</label> <input id="school_name" type="text" class="validate"> </div> </div> <div class="row"> <div class="col s4"> <div class="input-field"> <select id="standard" onchange="standardSelected()" required> <option value="" selected disabled>Choose Standard</option> </select> <label id="standard_label">Standard in ()</label> </div> </div> <div id="div_standard_otherSeleceted" class="col s4" style="display:none"> <div class="input-field"> <select id="standard_otherSeleceted" required> <option value="" selected disabled>Choose Standard</option> <option value="5th">5th</option> <option value="6th">6th</option> <option value="7th">7th</option> <option value="8th">8th</option> <option value="9th">9th</option> <option value="10th">10th</option> <option value="11th">11th</option> <option value="12th">12th</option> </select> <label id="label_standard_otherSeleceted">Standard in ()</label> </div> </div> <div class="col s4" style="margin-top:-0.5%"> <label for="homeTown">Home Town</label> <input id="homeTown" type="text" class="validate"> </div> </div> <div class="row"> <div class="input-field col s6"> <select id="nearestCenter"> <option value="" selected disabled>Choose Nearest Center</option> </select> <label>Nearest Center</label> </div> <div class="input-field col s6"> <select id="sourceOfEnquiry" required> <option value="" selected disabled>Choose Source of Enquiry</option> </select> <label>Source of Enquiry</label> </div> </div> <!-- Updated row: Entrance Exam Date, Next Calling Date, and On the Spot entrance date --> <div class="row"> <div class="input-field col s6"> <select id="entDate" onchange="onTheSpotSelected()"> <option value="" selected disabled>Choose Entrance Date</option> </select> <label>Entrance Exam Date</label> </div> <div class="input-field col s6"> <label for="next_calling_date">Next Calling Date</label> <input id="next_calling_date" type="date" class="input-field"> </div> </div> <div class="row"> <div class="input-field col s4" id="div_eed_onTheSpotSelected" style="display:none"> <input id="eed_onTheSpotSelected" type="date" class="validate"> <label for="eed_onTheSpotSelected">On the Spot entrance date</label> </div> </div> <div class="row"> <div class="col s4"> <label for="ent_rec_no">Entrance Exam Receipt Number</label> <input id="ent_rec_no" type="text" class="validate"> </div> <div class="col s4"> <label>OPC</label> <select id="opc_dropdown" required> <option value="" selected disabled>Select OPC</option> </select> </div> <div class="col s4"> <label for="remarks">Remarks</label> <input id="remarks" type="text" class="validate"> </div> </div> <div class="row" id="entranceDetails" style="display:none"></div> <div class="row"></div> <div class="row" style="width: 100%;"> <table id="bestMatchTable2"></table> </div> <div class="row"> <div class="col s3"> <button class="waves-light btn" id="checkDuplicate2" onclick="checkDuplicate()"> Check Duplicate </button> </div> <div class="col s1"></div> <div class="col s3"> <button class="waves-light btn" id="clear" onclick="clearClicked()" disabled> Clear </button> </div> <div class="col s1"></div> <div class="col s3"> <button class="btn waves-light" type="submit" id="submitBtn" name="action" onclick="validate()" disabled> Submit <i class="material-icons right">send</i> </button> </div> </div> </div> <!-- FOLLOW UPS TAB --> <div id="followUps" class="container col s12" style="width: 100%; padding: 20px; background-color: #f5f5f5; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1);"> <div class="row" style="margin-bottom: 10px;"></div> <h3 style="font-size: 24px; color: #333; margin: 0 0 20px 0; font-weight: 500;">Follow ups</h3> <div class="row" style="display: flex; align-items: center;"> <div class="col s4" style="padding-right: 15px;"> <label for="followUpsDate" style="font-size: 14px; color: #555; display: block; margin-bottom: 5px;">Select Next Calling Date</label> <input id="followUpsDate" type="date" class="input-field" style="width: 100%; padding: 8px; border: 1px solid #ccc; border-radius: 4px; font-size: 16px; color: #333; background-color: #fff;"> </div> <div class="col s2" style="margin-top: 20px;"> <button class="btn waves-light" id="followUpsGoBtn" onclick="followUpsGoClicked()" style="background-color: #26a69a; color: #fff; padding: 0 20px; height: 36px; line-height: 36px; border-radius: 4px; text-transform: uppercase; font-size: 14px; cursor: pointer;">Go</button> </div> </div> <div class="row" style="margin-top: 20px;"> <div class="container col s12" style="width: 100%; max-height: 400px; overflow: auto; background-color: #fff; border: 1px solid #e0e0e0; border-radius: 8px; padding: 0;"> <table id="followUpsTable" style="width: 100%; border-collapse: collapse; font-size: 14px; color: #333;"> </table> </div> </div> </div> <!-- MY STUDENTS --> <div id="viewData" class="container" style="width: 100%"> <div class="row"></div> <div class="row"> <div class="col s1"></div> <div class="input-field col s3"> <select id="viewDataCoursesDropdown" onchange="courseviseEntDate()" required> <option value="" selected disabled>Select Course</option> </select> <label>Course</label> </div> <div class="input-field col s3"> <select id="viewDataEntranceDatesDropdown" required> <option value="All Dates" selected>All Dates</option> </select> <label>Entrance Date</label> </div> <div class="input-field col s3"> <select id="viewDataEntranceFeesDropdown" required> <option value="All Data" selected>All Data</option> <option value="Entrance Fees Not Paid">Entrance Fees Not Paid</option> <option value="Entrance Fees Paid">Entrance Fees Paid</option> <option value="Entrance Not Given">Entrance Not Given</option> <option value="Course Fee Not Paid">Course Fee Not Paid</option> <option value="Admission Form Not Filled">Admission Form Not Filled</option> </select> <label>Entrance Fees Paid/Not Paid</label> </div> <div class="input-field col s2"> <button class="btn waves-light" id="viewDataGoBtn" onclick="viewDataGoClicked()"> Go </button> </div> </div> <div class="row"></div> <div class="row"> <div class="col s1"></div> <div class="input-field col s8"> <label for="viewDataSearchKeyword">Enter search keyword</label> <input id="viewDataSearchKeyword" type="text" class="input-field" disabled> </div> <div class="input-field col s3"> <button class="waves-light btn" id="viewDataSearchBtn" onclick="viewDataSearchClicked()" style="width:60%" disabled> Search </button> </div> </div> <div class="row"></div> <div class="container col s12"> <table class="variableWidth" id="viewDataTable"></table> </div> <div class="row"></div> </div> <!-- ADD ENTRANCE RECEIPT NO --> <div id="paymentDetails" class="col s12" style="width: 100%"> <div class="row"> <div class="container col s12"> <h3>Enquiry Data Search</h3> <div class="row"> <div class="col s1"></div> <div class="col s9"> <input id="editEntRecNoEnqDataSearchKey" type="text" placeholder="Enter search keyword" class="input-field"> </div> <div class="col s2"> <button class="waves-light btn" id="editEntRecNoEnqDataGoBtn" onclick="editEntRecNoEnqDataGoClicked()"> Go </button> </div> </div> <div class="row"> <div class="container col s12" style="width: 100%; max-height: 400px; overflow: auto;"> <table id="editEntRecNoEnqDataTable"></table> </div> </div> </div> </div> <div class="row"> <div class="container col s12"> <h3>All Payments Data</h3> <div class="row"> <div class="col s1"></div> <div class="col s9"> <input id="allPaymentsDataSearchKey" type="text" placeholder="Enter search keyword" class="input-field"> </div> <div class="col s2"> <button class="waves-light btn" id="allPaymentsDataGoBtn" onclick="allPaymentsDataGoClicked()"> Go </button> </div> </div> <div class="row"> <div class="container col s12" style="width: 100%; max-height: 400px; overflow: auto;"> <table id="allPaymentsDataTable"></table> </div> </div> </div> </div> <div class="row"></div> <div class="row"> <div class="col s5"></div> <div class="col s2"> <button class="waves-light btn" id="allPaymentsDataVerifyBtn" onclick="allPaymentsDataVerifyClicked()" disabled> Verify </button> </div> <div class="col s5"></div> </div> <div class="row" style="width: 100%; overflow: auto;"> <table id="verifiedPayTable"></table> </div> <div class="row"> <div class="col s2"></div> <div class="col s3"> <button class="waves-light btn" id="allPaymentsDataUnverifyBtn" onclick="allPaymentsDataUnverifyClicked()" disabled> Unverify </button> </div> <div class="col s2"></div> <div class="col s3"> <button class="waves-light btn" id="allPaymentsDataDoneBtn" onclick="allPaymentsDataDoneClicked()" disabled> Done </button> </div> <div class="col s2"></div> </div> <div class="row"> <div class="col s12"> <label for="paymentDetails_remarks">Remarks</label> <input id="paymentDetails_remarks" type="text" class="input-field" disabled> </div> </div> </div> <!-- SEARCH --> <div id="schData" class="col s12"> <h3>Search</h3> <div class="row"> <div class="col s1"></div> <div class="col s9"> <input id="schDataKey" type="text" placeholder="Enter search keyword" class="input-field"> </div> <div class="col s2"> <button class="waves-light btn" id="schDataGoBtn" onclick="schDataGoClicked()"> Go </button> </div> </div> <div class="container col s12" style="max-height:550px; overflow: auto;"> <table id="schDataOutTable"></table> </div> </div> </div> </div> <script> var center = ""; var feeperson = ""; var walkInsData = new Array(); var date_index = 0; var stud_name_index = 1; var email_index = 2; var contact_index = 3; var pov_index = 4; var walkinRemarks_index = 5; var counter = 0; var isOPC = ""; var allCourses = new Array(); var myStudentsTabCourses = new Array(); var courseviseEntDates = {}; var messagesArr = new Array(); var msg_json = new Array(); document.addEventListener('DOMContentLoaded', function() { let elems = document.querySelectorAll(".tabs"); M.Tabs.init(elems); document.getElementById("followUpsDate").value = new Date().toISOString().split("T")[0]; google.script.url.getLocation(function(location) { google.script.run.withSuccessHandler(onSuccessInit).withFailureHandler(onFailureInit).initHash(location.hash); }); function onFailureInit(e){ document.getElementById('pendingWalkIns').innerHTML = '☠ Wrong Link is being used. Kindly Check ☠'; } function onSuccessInit(data){ center = data.currCenter; feeperson = data.currUser; document.getElementById("center").value = center; document.getElementById("feeperson").value = feeperson; if(data.currCenter==="☠ Wrong Link ☠" ){ document.getElementById('pendingWalkIns').innerHTML = '☠ Wrong Link is being used. Kindly Check ☠'; } else { // Initiate the things in the bottom half let elems = document.querySelectorAll('.datepicker'); M.Datepicker.init(elems, {"format":"yyyy-mm-dd", "autoClose":true}); let opc_dropdown = document.getElementById("opc_dropdown"); for (let i = 0; i < data.opc_dropdownData.length; i++) { // Append the element to the end of Array list opc_dropdown[opc_dropdown.length] = new Option(data.opc_dropdownData[i], data.opc_dropdownData[i]); } isOPC = data.is_OPC; allCourses = data.courses; myStudentsTabCourses = data.myStudentsTabCourses; messagesArr = data.messagesArr; courseviseEntDetails = data.courseviseEntDetails; let course_dropdown = document.getElementById('course_dropdown'); for (let i = 0; i < allCourses.length; i++) { // Append the element to the end of Array list course_dropdown[course_dropdown.length] = new Option(allCourses[i], allCourses[i]); } let viewDataCoursesDropdown = document.getElementById("viewDataCoursesDropdown"); if (isOPC === "Yes"){ for (let i = 0; i < myStudentsTabCourses.length; i++){ viewDataCoursesDropdown[viewDataCoursesDropdown.length] = new Option(myStudentsTabCourses[i], myStudentsTabCourses[i]); } google.script.run.withSuccessHandler(onSuccessFillViewDataDropdown).withFailureHandler(onFailureGettingDropdownData).getEntranceDates(feeperson, myStudentsTabCourses) function onFailureGettingDropdownData(e){ Swal.fire({ title: "Error :- " + e, text: "Network Issue. Please try reloading...", icon: "error" }); } function onSuccessFillViewDataDropdown(outputDict){ courseviseEntDates = outputDict; } } google.script.run.withSuccessHandler(onSuccess).getWalkInsData(feeperson); function onSuccess(data){ //console.log(data); if (data.length != 0){ document.getElementById("topPart").style.display="block"; walkInsData = data; //console.log(walkInsData); for (let i = 0; i < walkInsData.length; i++){ walkInsData[i][date_index] = formatDate(Number(walkInsData[i][date_index])*1000); } document.getElementById("myHeader").innerText += "("+walkInsData.length+")"; document.getElementById("enquiryDate").value = walkInsData[0][date_index]; document.getElementById("first_name").value = walkInsData[0][stud_name_index]; document.getElementById("above_email").value = walkInsData[0][email_index]; document.getElementById("above_phone").value = walkInsData[0][contact_index]; if (typeof(walkInsData[0][pov_index]) === "object"){ document.getElementById("pov").value = convertToString(walkInsData[0][pov_index]); } else document.getElementById("pov").value = walkInsData[0][pov_index]; document.getElementById("walkinRemarksText").value = walkInsData[0][walkinRemarks_index]; document.getElementById("checkDuplicate").disabled = false; document.getElementById("updateRemarksBtn").disabled = false; document.getElementById("fillForm").disabled = false; if (walkInsData.length > 1) document.getElementById("next").disabled = false; google.script.run.withSuccessHandler(onSuccessBestMatchCode).bestMatch([walkInsData[counter][stud_name_index], walkInsData[counter][[contact_index]], walkInsData[counter][[email_index]]]); } } document.getElementById("date").value = formatDate(new Date()).slice(0, 10); google.script.run.withSuccessHandler(onSuccessFillDropdowns).getDropdownValues(); function onSuccessFillDropdowns(sheet){ courses = sheet.courses; nearestCenter = sheet.nearCenter; sourceOfEnquiry = sheet.soe; standardsCorrCours = sheet.standards; ay = sheet.ay; entDateCorrCours = sheet.eeds; courseForEntDataAddition = sheet.courseForEntDataAddition; //let sourceOfEnquiry = ['Received a call', 'Center Walk-in', 'Assigned from Website']; let sOE = document.getElementById('sourceOfEnquiry'); for (let i = 0; i < sourceOfEnquiry.length; i++) { // Append the element to the end of Array list sOE[sOE.length] = new Option(sourceOfEnquiry[i], sourceOfEnquiry[i]); } let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); // document.getElementById("").class = "tab col s12" // document.getElementById("viewDataTab").style.display = "none"; document.getElementById("enquiryForm").style.display = "block"; if (isOPC === "Yes") document.getElementById("tabsBar").style.display = "block"; } } } }); /** FOLLOW UPS ->> **/ function followUpsGoClicked() { let dateVal=document.getElementById("followUpsDate").value; destroyTable("followUpsTable"); if(!dateVal || dateVal === "") { let table = document.getElementById("followUpsTable"); let tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; let th = document.createElement("th"); th.innerHTML = "<b>No data available for this date</b>"; tRow.appendChild(th); return; } document.body.style.cursor = "wait"; document.getElementById("followUpsGoBtn").disabled=true; google.script.run.withSuccessHandler(onSuccessFillFollowUpsTable) .withFailureHandler(onFailureFillFollowUpsTable) .followUpsGoClickedInGS(dateVal, feeperson); } function onSuccessFillFollowUpsTable(data) { document.body.style.cursor="default"; document.getElementById("followUpsGoBtn").disabled = false; destroyTable("followUpsTable"); if(data.length < 2 || data[0][0] === "No data available for this date") { let table = document.getElementById("followUpsTable"); let tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; let th = document.createElement("th"); th.innerHTML = "<b>No data available for this date</b>"; tRow.appendChild(th); return; } fillTable("followUpsTable",data); } function onFailureFillFollowUpsTable(err) { document.body.style.cursor = "default"; document.getElementById("followUpsGoBtn").disabled = false; Swal.fire({ title: "Error :- " + err, text: "Please try again", icon: "error" }); } /** End -------------------->>>> **/ function convertToString(json_row){ return "Website enquiry assigned by " + json_row["assigned_by"] + ": Registered on the website on " + json_row["enq_dt"] + ((json_row["ent_dt"] != "null" && json_row["enq_dt"].length > 3)?(" for the " + json_row["ent_dt"]):"") + " entrance of " + json_row["course"] + " course " + "and selected " + json_row["nearest_center"] + " as their nearest center." + ((json_row["sch_name"] != "null" && json_row["sch_name"].length > 2)?" Their school name is " + json_row["sch_name"] + ((json_row["hometown"] != "null" && json_row["hometown"].length > 2)?" and " + json_row["hometown"] + " is his/her hometown":""):((json_row["hometown"] != "null" && json_row["hometown"].length > 2)?" His/Her hometown is " + json_row["hometown"]:"")); } function courseviseEntDate(){ let viewDataEntranceDatesDropdown = document.getElementById('viewDataEntranceDatesDropdown'); let dropdownData = courseviseEntDates[document.getElementById("viewDataCoursesDropdown").value]; while(viewDataEntranceDatesDropdown.length>1){ viewDataEntranceDatesDropdown.remove(1); } if (dropdownData != null){ for (let i = 0; i < dropdownData.length; i++) { // Append the element to the end of Array list viewDataEntranceDatesDropdown[viewDataEntranceDatesDropdown.length] = new Option(dropdownData[i], dropdownData[i]); } } let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); } var enqUIDArr = []; // var btnNamesArr = ["Message After Entrance Roll No is Generated", "Sanidhya sir Counselling Reminder On day of counselling (by 3:00 pm)", "Leaflet Link WhatsApp", "Account Details WhatsApp", "Next Entrance WhatsApp", "Fee Paid", "Form Filled", "Form Not Filled WhatsApp"] // var viewDataTableColWidths = ['7', '7', '7', '7', '7', '7', '7', '7', '7', '7', '7', '7', '7', '9'] function viewDataGoClicked(){ document.getElementById("viewDataGoBtn").disabled = true; document.body.style.cursor = "wait"; let viewDataCoursesDropdown = document.getElementById("viewDataCoursesDropdown").value; if (viewDataCoursesDropdown === ""){ document.body.style.cursor = "default"; Swal.fire({ title: "Please select a course", icon: "error" }); document.getElementById("viewDataGoBtn").disabled = false; return; } google.script.run.withSuccessHandler(onSuccessFillViewDataTable).withFailureHandler(onFailureGettingTableData).viewDataGoClicked(feeperson, (viewDataCoursesDropdown === "All Courses"?myStudentsTabCourses:[viewDataCoursesDropdown]), document.getElementById("viewDataEntranceDatesDropdown").value, document.getElementById("viewDataEntranceFeesDropdown").value); } function onFailureGettingTableData(e){ if (("" + e) === "ScriptError: TypeError: Cannot read properties of null (reading 'forEach')"){ // Swal.fire({ // title: "No records found", // text: "Please try again", // icon: "error" // }); destroyTable("viewDataTable"); let row = [`Serial No`, `Entrance Date`, `Student Name`, `Mobile 1`, `Mobile 2`, `Calling_Remarks`, `Email 1`, `Email 2`, `Enquiry Date`, `Nearest Center`, `School Name`, `Hometown`, `Standard`, `Course`, `Entrance Receipt No`, `Entrance Roll No`, `Marks`, `Batch`, `Scholarship`, `Last Admission Date`, `Counselling`, `OPC`, `Roll No`]; let table = document.getElementById("viewDataTable"); var tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; row.forEach((c, j) => { let th = document.createElement("th"); th.innerHTML = "<b>" + c + "</b>"; tRow.appendChild(th); }); let tableHeaders = table.getElementsByTagName('th'); for (let i = 0; i < tableHeaders.length; i++){ if (i <= lastTableCol) tableHeaders[i].width = viewDataTableWidths[i] else tableHeaders[i].width = '200px'; } // let tableHeaders = table.getElementsByTagName('th'); // for (let i = 0; i < tableHeaders.length; i++){ // if (i <= lastTableCol) tableHeaders[i].width = viewDataTableWidths[i] // else tableHeaders[i].width = '200px'; // } document.body.style.cursor = "default"; document.getElementById("viewDataSearchKeyword").disabled = true; document.getElementById("viewDataSearchBtn").disabled = true; document.getElementById("viewDataGoBtn").disabled = false; } } function onSuccessFillViewDataTable(opcData){ document.getElementById("viewDataSearchKeyword").disabled = false document.getElementById("viewDataSearchBtn").disabled = false; msg_json = opcData.msg_json; enqUIDArr = opcData.enqUIDArr; //console.log("enqUIDArr:") //console.log(enqUIDArr); destroyTable("viewDataTable"); let table = document.getElementById("viewDataTable"); opcData.data.forEach((row, i) => { if (i > 0) { var tRow = table.insertRow(-1); row.forEach((c, j) => { let cell = tRow.insertCell(j); cell.innerHTML = (j==viewDataCallRemCol?c.replace(/\n/g, '<br>'):c); if (j == lastTableCol) { if (row[lastTableCol] && row[lastTableCol].length > 3) cell.innerHTML = `<a href="${row[lastTableCol]}" target="_blank">Admission Form</a>`; // if (row[lastTableCol] && row[lastTableCol].length > 3 && row[viewDataCourseCol] === 'C27') cell.innerHTML = `<a href="${row[lastTableCol]}" target="_blank">Admission Form</a>`; // else if (row[lastTableCol] && row[lastTableCol].length > 3 && row[viewDataCourseCol] != 'C27') cell.innerHTML = "Links will soon be displayed"; for (let i_ = 2; i_ < messagesArr.length; i_++){ if (messagesArr[i_][0] === row[viewDataCourseCol]){ if(msg_json[i]){ msg_json[i] = JSON.parse(msg_json[i]); } for (let x = 1; x < messagesArr[0].length; x++){ if (messagesArr[0][x].length > 0){ // let cell = tRow.insertCell(-1); if (messagesArr[i_][x].length > 0){ // i, course, whichMsg, mobileNo, st_name, ent_roll_no, ent_dt, counselling date, lastTableCol let cell = tRow.insertCell(-1); cell.innerHTML = `<button class='waves-light btn' onclick='sendingWhatsApp(${i}, "${row[viewDataCourseCol]}", "${x}", "${row[viewDataMob1Col]}", "${row[viewDataStNameCol]}", "${row[viewDataEntRollNoCol]}", "${row[viewDataEntDtCol]}", "${row[viewDataCounselDtTimeCol]}", "${row[lastTableCol]}")'> Send </button> <div class='row'></div> <label> <input id = '${(enqUIDArr[i] + i) + x}' type='checkbox' ${(msg_json[i]?(msg_json[i][messagesArr[0][x]] === "true" ? "checked" : ""):"")} onclick = "whatsAppSent('${enqUIDArr[i]}', '${(enqUIDArr[i] + i) + x}', '${messagesArr[0][x]}')"> <span></span> </label>`; } } } break; } } } }); } else { var tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; row.forEach((c, j) => { let th = document.createElement("th"); th.innerHTML = "<b>" + c + "</b>"; tRow.appendChild(th); // let cell = tRow.insertCell(j); // cell.innerHTML = "<b>" + c + "</b>"; // cell.style.width = 5; if (j == lastTableCol){ let selectedCourse = document.getElementById("viewDataCoursesDropdown").value; for (let i_ = 2; i_ < messagesArr.length; i_++){ if (messagesArr[i_][0] === selectedCourse){ for (let x = 1; x < messagesArr[0].length; x++){ if (messagesArr[0][x].length > 0){ // let cell = tRow.insertCell(-1); if (messagesArr[i_][x].length > 0){ let th = document.createElement("th"); th.innerHTML = "<b>" + messagesArr[1][x] + "</b>"; tRow.appendChild(th); } } } break; } } // for (let i = 1; i < messagesArr[0].length; i++){ // if (messagesArr[0][i].length > 0){ // let th = document.createElement("th"); // th.innerHTML = "<b>" + messagesArr[1][i] + "</b>"; // tRow.appendChild(th); // } // } } }); } }); let rows = table.getElementsByTagName('tr') rows[0].style.position = 'sticky'; rows[0].style.top = '0px'; for (let i = 1; i < rows.length; i++) { // Start from 1 to skip the header row addRowClickListener(table, rows[i], i); } let tableHeaders = table.getElementsByTagName('th'); for (let i = 0; i < tableHeaders.length; i++){ if (i <= lastTableCol) tableHeaders[i].width = viewDataTableWidths[i] else tableHeaders[i].width = '200px'; } document.body.style.cursor = "default"; document.getElementById("viewDataGoBtn").disabled = false; } var viewDataTableWidths = ['80px', '150px', '200px', '150px', '150px', '400px', '300px', '300px', '150px', '150px', '200px', '150px', '100px', '200px', '200px', '150px', '100px', '120px', '150px', '150px', '150px', '150px', '150px', '150px', '250px']; function formattingDate(dateString) { const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec"]; // Parse the date string into a date object let date = new Date(dateString); let day = date.getDate(); let month = months[date.getMonth()]; let year = date.getFullYear(); // Add suffix for day let daySuffix; if (day % 10 === 1 && day !== 11) { daySuffix = 'st'; } else if (day % 10 === 2 && day !== 12) { daySuffix = 'nd'; } else if (day % 10 === 3 && day !== 13) { daySuffix = 'rd'; } else { daySuffix = 'th'; } return `${day}${daySuffix} ${month}, ${year}`; } function sendingWhatsApp(i, course, whichMsg, mobileNo, st_name, ent_roll_no, ent_dt, counselDtTime, adm_link){ let next_ent_dt = ""; for (let t = 0; t < courses.length; t++){ if (course === courses[t]){ next_ent_dt = getNextDate(entDateCorrCours[t]) } } let link = "https://api.whatsapp.com/send?phone=+91"; for (let i_ = 2; i_ < messagesArr.length; i_++){ if (messagesArr[i_][0] === course){ link += mobileNo + "&text=" + encodeURIComponent(messagesArr[i_][whichMsg].replace(/{{st_name}}/g, st_name).replace(/{{ent_roll_no}}/g, ent_roll_no).replace(/{{ent_dt}}/g, formattingDate(ent_dt)).replace(/{{next_ent_dt}}/g, formattingDate(next_ent_dt)).replace(/{{opc}}/g, feeperson).replace(/{{counselDtTime}}/g, counselDtTime).replace(/{{adm_link}}/g, adm_link)); break; } } window.open(link, '_blank'); whatsAppSent(enqUIDArr[i], ((enqUIDArr[i] + i) + whichMsg), messagesArr[0][whichMsg], true); } function getNextDate(datesArray) { // Get today's date without the time part const today = new Date(); today.setHours(0, 0, 0, 0); // Convert each date string in the array to a Date object const futureDates = datesArray .map(dateStr => new Date(dateStr)) .filter(date => date > today) // Keep only dates after today .sort((a, b) => a - b); // Sort the dates in ascending order // Return the first date after today or null if none exists return futureDates.length > 0 ? futureDates[0].toISOString().split('T')[0] : null; } window.addEventListener("hashchange", function() { if (location.hash === "#followUps") { // Set the date input to today’s date if not already set. document.getElementById("followUpsDate").value = new Date().toISOString().split("T")[0]; document.body.style.cursor = "wait"; document.getElementById("followUpsGoBtn").disabled = true; google.script.run.withSuccessHandler(onSuccessFillFollowUpsTable) .withFailureHandler(onFailureFillFollowUpsTable) .followUpsGoClickedInGS(document.getElementById("followUpsDate").value, feeperson); } }); function whatsAppSent(enquiryUID, checkboxID, keyword, defaultVerify = false){ document.body.style.cursor = "wait"; let checkbox = document.getElementById(checkboxID); checkbox.disabled = true; let whatsAppSentBool = checkbox.checked; google.script.run.withSuccessHandler(onSuccessUpdatedMessageSentJSON).withFailureHandler(onFailureUpdatingMessageSentJSON).whatsAppSent(enquiryUID, (defaultVerify?true:whatsAppSentBool), keyword) function onFailureUpdatingMessageSentJSON(e){ document.body.style.cursor = "default"; Swal.fire({ title: "Error :- " + e, text: "Please try again", icon: "error" }); checkbox.disabled = false; checkbox.checked = (defaultVerify?true:whatsAppSentBool); } function onSuccessUpdatedMessageSentJSON(returnMSG){ console.log("success"); document.body.style.cursor = "default"; checkbox.disabled = false; checkbox.checked = (defaultVerify?true:whatsAppSentBool); } } let viewDataSerialNoCol = 0; let viewDataEntDtCol = 1; let viewDataStNameCol = 2; let viewDataMob1Col = 3; let viewDataMob2Col = 4; let viewDataCallRemCol = 5; let viewDataEmail1Col = 6; let viewDataEmail2Col = 7; let viewDataEnqDtCol = 8; let viewDataNearCenterCol = 9; let viewDataSchNameCol = 10; let viewDataHometownCol = 11; let viewDataStandardCol = 12; let viewDataCourseCol = 13; let viewDataEntRecNoCol = 14; let viewDataEntRollNoCol = 15; let viewDataCounselDtTimeCol = 20; let lastTableCol = 24; // Separate function to add click event listener function addRowClickListener(table, rowElement, i) { for (let colIndex = 0; colIndex < lastTableCol; colIndex++) { rowElement.cells[colIndex].addEventListener('click', () => { // rowElement.addEventListener('click', () => { highlightTableRow(table, rowElement); // Fetch the name and roll_no from the first two columns let viewDataEntDt = rowElement.cells[viewDataEntDtCol].innerHTML; let viewDataStName = rowElement.cells[viewDataStNameCol].innerHTML; let viewDataMob1 = rowElement.cells[viewDataMob1Col].innerHTML; let viewDataMob2 = rowElement.cells[viewDataMob2Col].innerHTML; let viewDataCallRem = rowElement.cells[viewDataCallRemCol].innerHTML.replace(/<br>/g, '\n'); let viewDataEntRollNo = rowElement.cells[viewDataEntRollNoCol].innerHTML; let viewDataEmail1 = rowElement.cells[viewDataEmail1Col].innerHTML; let viewDataEmail2 = rowElement.cells[viewDataEmail2Col].innerHTML; let viewDataEnqDt = rowElement.cells[viewDataEnqDtCol].innerHTML; // let viewDataNearCenter = rowElement.cells[viewDataNearCenterCol].innerHTML; let viewDataSchName = rowElement.cells[viewDataSchNameCol].innerHTML; let viewDataHometown = rowElement.cells[viewDataHometownCol].innerHTML; let viewDataEntRecNo = rowElement.cells[viewDataEntRecNoCol].innerHTML; let viewDataStandard = rowElement.cells[viewDataStandardCol].innerHTML; let viewDataCourse = rowElement.cells[viewDataCourseCol].innerHTML; // // Define the array of center names // let centers = ["Kothrud Center", "Aundh Center", "Shivaji Nagar Center"]; // Generate the dropdown options // let course = document.getElementById("viewDataCoursesDropdown").value; // let viewDataCoursesSerialNo = 0; // for (let i = 0; i < courses.length; i++){ // if (course == courses[i]){ // viewDataCoursesSerialNo = i; // break; // } // } let selectedStandard = ""; // Trigger the SweetAlert with pre-filled inputs Swal.fire({ title: 'Edit Information', html: ` <label for="viewDataEntDt">Entrance Date:</label> <input id="viewDataEntDt" class="swal2-input" type="date" style="width:100%" value="${viewDataEntDt}"> <label for="viewDataStName">Student Name:</label> <input id="viewDataStName" class="swal2-input" style="width:100%" value="${viewDataStName}"> <label for="viewDataMob1">Mobile 1:</label> <input id="viewDataMob1" class="swal2-input" style="width:100%" value="${viewDataMob1}"> <label for="viewDataMob2">Mobile 2:</label> <input id="viewDataMob2" class="swal2-input" style="width:100%" value="${viewDataMob2}"> <label for="viewDataCallRem">Calling Remark:</label> <textarea id="viewDataCallRem" class="swal2-textarea" style="width:90%; height: 100px; display: flex; align-items: center; justify-content: center;" rows="4">${viewDataCallRem}</textarea> <label for="viewDataEntRollNo">Entrance Roll No:</label> <input id="viewDataEntRollNo" class="swal2-input" style="width:100%" value="${viewDataEntRollNo}" disabled> <label for="viewDataEmail1">Email 1:</label> <input id="viewDataEmail1" class="swal2-input" type = "email" style="width:100%" value="${viewDataEmail1}"> <label for="viewDataEmail2">Email 2:</label> <input id="viewDataEmail2" class="swal2-input" type = "email" style="width:100%" value="${viewDataEmail2}"> <label for="viewDataEnqDt">Enquiry Date:</label> <input id="viewDataEnqDt" class="swal2-input" style="width:100%" value="${viewDataEnqDt}" disabled> <label for="viewDataSchName">School Name:</label> <input id="viewDataSchName" class="swal2-input" style="width:100%" value="${viewDataSchName}"> <label for="viewDataHometown">Hometown:</label> <input id="viewDataHometown" class="swal2-input" style="width:100%" value="${viewDataHometown}"> <label for="viewDataEntRecNo">Entrance Receipt No:</label> <input id="viewDataEntRecNo" class="swal2-input" style="width:100%" value="${viewDataEntRecNo}" disabled>`, input: "select", inputOptions: { Standard: { "5th": "5th", "6th": "6th", "7th": "7th", "8th": "8th", "9th": "9th", "10th": "10th", "11th": "11th", "12th": "12th" } }, inputValue: viewDataStandard, // This sets the default value in the dropdown inputPlaceholder: "Select standard", inputValidator: (value) => { return new Promise((resolve) => { selectedStandard = value; if (value != "") { console.log(value); resolve(); } else { console.log(value); resolve("You need to select standard :)"); } }); }, focusConfirm: false, preConfirm: () => { return { entDt: document.getElementById('viewDataEntDt').value, stName: document.getElementById('viewDataStName').value, mob1: document.getElementById('viewDataMob1').value, mob2: document.getElementById('viewDataMob2').value, callRem: document.getElementById('viewDataCallRem').value, entRollNo: document.getElementById('viewDataEntRollNo').value, email1: document.getElementById('viewDataEmail1').value, email2: document.getElementById('viewDataEmail2').value, enqDt: document.getElementById('viewDataEnqDt').value, // nearCenter: document.getElementById('viewDataNearCenter').value, schName: document.getElementById('viewDataSchName').value, hometown: document.getElementById('viewDataHometown').value, standard: selectedStandard, course: viewDataCourse }; } }).then((result) => { if (result.isConfirmed) { // Handling the result here document.body.style.cursor = "wait"; google.script.run.withSuccessHandler(onSuccessEditEnquiry).withFailureHandler(onFailureUpdatingEnquiry).updateEnquiryData(enqUIDArr[i], result.value) function onFailureUpdatingEnquiry(e){ document.body.style.cursor = "default"; Swal.fire({ title: "Error :- " + e, text: "Please try again", icon: "error" }); document.getElementById("viewDataGoBtn").disabled = false; } function onSuccessEditEnquiry(){ rowElement.cells[viewDataEntDtCol].innerHTML = result.value.entDt; rowElement.cells[viewDataStNameCol].innerHTML = result.value.stName; rowElement.cells[viewDataMob1Col].innerHTML = result.value.mob1; rowElement.cells[viewDataMob2Col].innerHTML = result.value.mob2; rowElement.cells[viewDataCallRemCol].innerHTML = result.value.callRem.replace(/\n/g, '<br>'); // let viewDataEntRollNo = rowElement.cells[viewDataEntRollNoCol].innerHTML; No need to update in the table rowElement.cells[viewDataEmail1Col].innerHTML = result.value.email1; rowElement.cells[viewDataEmail2Col].innerHTML = result.value.email2; // let viewDataEnqDt = rowElement.cells[viewDataEnqDtCol].innerHTML; No need to update in the table // let viewDataNearCenter = rowElement.cells[viewDataNearCenterCol].innerHTML; rowElement.cells[viewDataSchNameCol].innerHTML = result.value.schName; rowElement.cells[viewDataHometownCol].innerHTML = result.value.hometown; rowElement.cells[viewDataStandardCol].innerHTML = result.value.standard; document.body.style.cursor = "default"; console.log('Enquiry UID:',enqUIDArr[i]) console.log('Updated Entrance Date:', result.value.entDt); console.log('Updated Student Name:', result.value.stName); console.log('Updated Mobile 1:', result.value.mob1); console.log('Updated Mobile 2:', result.value.mob2); console.log('Updated Calling Remark:', result.value.callRem.replace(/\r?\n/g, "\\n").replace(/'/g, "\\'")); console.log('Updated Entrance Roll No:', result.value.entRollNo); console.log('Updated Email 1:', result.value.email1); console.log('Updated Email 2:', result.value.email2); console.log('Updated Enquiry Date:', result.value.enqDt); // console.log('Updated Nearest Center:', result.value.nearCenter); console.log('Updated School Name:', result.value.schName.replace(/'/g, "\\'")); console.log('Updated Hometown:', result.value.hometown.replace(/'/g, "\\'")); console.log('Updated Standard:', result.value.standard); } } }); }); } } function viewDataSearchClicked() { document.getElementById("viewDataSearchBtn").disabled = true; document.body.style.cursor = "wait"; let keyword = document.getElementById("viewDataSearchKeyword").value.toLowerCase(); let table = document.getElementById("viewDataTable"); if (table.rows.length == 0){ //google.script.run.withSuccessHandler(onSuccessviewDataSearchClicked).withFailureHandler(onFailureGettingTableData).viewDataGoClicked(feeperson, myStudentsTabCourses, "All Dates", "All Data"); } else{ let rows = table.getElementsByTagName('tr'); // Loop through all table rows, skipping the first (header row) for (let i = 1; i < rows.length; i++) { let cells = rows[i].getElementsByTagName('td'); let match = false; // Check if any relevant cell contains the keyword if (cells[viewDataStNameCol].innerText.toLowerCase().includes(keyword) || cells[viewDataMob1Col].innerText.toLowerCase().includes(keyword) || cells[viewDataMob2Col].innerText.toLowerCase().includes(keyword) || cells[viewDataEmail1Col].innerText.toLowerCase().includes(keyword) || cells[viewDataEmail2Col].innerText.toLowerCase().includes(keyword) || cells[viewDataCallRemCol].innerText.toLowerCase().includes(keyword) || cells[viewDataEntRecNoCol].innerText.toLowerCase().includes(keyword) || cells[viewDataEntRollNoCol].innerText.toLowerCase().includes(keyword) || cells[viewDataCounselDtTimeCol].innerText.toLowerCase().includes(keyword)){ match = true; } // Show or hide the row based on the match rows[i].style.display = match ? '' : 'none'; } document.body.style.cursor = "default"; document.getElementById("viewDataSearchBtn").disabled = false; } function onSuccessviewDataSearchClicked(data){ //onSuccessFillViewDataTable(data); let rows = table.getElementsByTagName('tr'); // Loop through all table rows, skipping the first (header row) for (let i = 1; i < rows.length; i++) { let cells = rows[i].getElementsByTagName('td'); let match = false; // Check if any relevant cell contains the keyword if (cells[viewDataStNameCol].innerText.toLowerCase().includes(keyword) || cells[viewDataMob1Col].innerText.toLowerCase().includes(keyword) || cells[viewDataMob2Col].innerText.toLowerCase().includes(keyword) || cells[viewDataEmail1Col].innerText.toLowerCase().includes(keyword) || cells[viewDataEmail2Col].innerText.toLowerCase().includes(keyword) || cells[viewDataCallRemCol].innerText.toLowerCase().includes(keyword) || cells[viewDataEntRecNoCol].innerText.toLowerCase().includes(keyword) || cells[viewDataCounselDtTimeCol].innerText.toLowerCase().includes(keyword)){ match = true; } // Show or hide the row based on the match rows[i].style.display = match ? '' : 'none'; } document.body.style.cursor = "default"; document.getElementById("viewDataSearchBtn").disabled = false; } } function validateEmail(elementID) { let email = document.getElementById(elementID).value // Regular expression for basic email validation const emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/; // Test the email against the pattern if (emailPattern.test(email)) { return true; // The email is valid } else { return false; // The email is not valid } } function formatDate(inp){ let d = new Date(inp); let dformat = [ d.getFullYear(), ('' + (d.getMonth()+1)).padStart(2, '0'), ('' + d.getDate()).padStart(2, '0') ].join('-') + ' ' + [ ('' + d.getHours()).padStart(2, '0'), ('' + d.getMinutes()).padStart(2, '0'), ('' + d.getSeconds()).padStart(2, '0') + (d.getMilliseconds()!=0?("." + String(d.getMilliseconds()).padStart(3, '0')):"") ].join(':'); return dformat; } function rearrangeColsFromFile(arr2D, headers) { var headerIndices = []; headers.forEach((h) => { arr2D[0].forEach((d, i) => { if (h === d) headerIndices.push(i); }); }); var retVal = []; arr2D.forEach((r) => { var currRow = []; headerIndices.forEach((i) => { currRow.push(r[i]); }); retVal.push(currRow); }); return retVal; } function destroyTable(tableId){ let table = document.getElementById(tableId); for(let i = table.rows.length - 1; i >= 0; i--){ table.rows[i].remove(); } } function fillTable(tableId, data){ table = document.getElementById(tableId); try{ data.forEach((row, i)=>{ if(i>0){ var tRow = table.insertRow(-1); if(i%2 == 0) tRow.style.background = "#f5fff5"; row.forEach((c, i)=>{ tRow.insertCell(i).innerHTML = c; }); }else{ var tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; row.forEach((c, i)=>{ tRow.insertCell(i).innerHTML = "<b>"+c+"</b>"; }); } }); }catch(e){} } function validateDuplicate(){ // Swal.fire({ // title: "Are you sure?", // text: "You won't be able to revert this!", // icon: "warning", // showCancelButton: true, // confirmButtonColor: "teal", // cancelButtonColor: "#d33", // confirmButtonText: "Yes, delete it!" // }).then((result) => { // if (result.isConfirmed) { // Swal.fire({ // title: "Deleted!", // text: "Your file has been deleted.", // icon: "success" // }); // duplicateClicked(); // } // }); Swal.fire({ title: "Are you sure?", text: "You won't be able to revert this!", icon: "warning", input: "text", inputPlaceholder: "Enter your reason", showCancelButton: true, confirmButtonColor: "teal", cancelButtonColor: "#d33", confirmButtonText: "Yes, delete it!", inputValidator: (value) => { if (!value) { return "You need to enter a reason!"; } } }).then((result) => { if (result.isConfirmed) { let inputText = result.value; // This is the text input by the user Swal.fire({ title: "Deleted!", text: "Your file has been deleted.", icon: "success" }); duplicateClicked(inputText); } }); } function checkDuplicate(){ document.getElementById("checkDuplicate2").disabled = true; document.body.style.cursor = "wait"; //collecting data var mob1 = document.getElementById('phone1').value; var mob2 = document.getElementById('phone2').value; var email1 = document.getElementById('email1').value; var email2 = document.getElementById('email2').value; if(mob1.length > 2 || email1.length > 2){ google.script.run.withSuccessHandler(onSuccessBestMatchCode2).bestMatch2(mob1, mob2, email1, email2); } } function onSuccessBestMatchCode2(arr_bestMatch){ let table = document.getElementById("bestMatchTable2"); destroyTable("bestMatchTable2"); try{ fillTable("bestMatchTable2", arr_bestMatch); document.body.style.cursor = "default"; }catch(e){} document.getElementById("clear").disabled = false; document.getElementById("submitBtn").disabled = false; document.getElementById("checkDuplicate2").disabled = false; document.body.style.cursor = "default"; } function clearClicked(){ onSuccessSubmit(false); destroyTable("bestMatchTable2"); document.getElementById("checkDuplicate2").disabled = false; document.getElementById("clear").disabled = true; document.getElementById("submitBtn").disabled = true; } function standardSelected(){ if (document.getElementById("standard").value == 'Other'){ document.getElementById("div_standard_otherSeleceted").style.display = 'block'; } else{ document.getElementById("div_standard_otherSeleceted").style.display = 'none'; } } function duplicateClicked(reason){ document.getElementById("checkDuplicate").disabled = true; document.getElementById("updateRemarksBtn").disabled = true; google.script.run.duplicateClicked([walkInsData[counter][date_index]+(walkInsData[counter][date_index].includes(".")?"":"+05:30"), walkInsData[counter][email_index], walkInsData[counter][contact_index], reason]); walkInsData.splice(counter, 1); if (walkInsData.length == 0){ document.getElementById("myHeader").innerText = "All pending walk-ins submitted."; document.getElementById("first_name").value = ""; document.getElementById("above_email").value = ""; document.getElementById("above_phone").value = ""; document.getElementById("enquiryDate").value = ""; document.getElementById("pov").value = ""; document.getElementById("walkinRemarksText").value = ""; document.getElementById("checkDuplicate").disabled = true; document.getElementById("updateRemarksBtn").disabled = true; document.getElementById("previous").disabled = true; document.getElementById("next").disabled = true; document.getElementById("fillForm").disabled = true; document.getElementById("date").value = formatDate(new Date()).slice(0, 10); destroyTable("bestMatchTable"); return; } if (counter > walkInsData.length - 1){ counter -= 1; } google.script.run.withSuccessHandler(onSuccessBestMatchCode).bestMatch([walkInsData[counter][stud_name_index], walkInsData[counter][[contact_index]], walkInsData[counter][[email_index]]]); document.getElementById("myHeader").innerText = "Pending Walk-Ins("+walkInsData.length+")"; document.getElementById("checkDuplicate").disabled = false; document.getElementById("updateRemarksBtn").disabled = false; document.getElementById("enquiryDate").value = walkInsData[counter][date_index]; document.getElementById("first_name").value = walkInsData[counter][stud_name_index]; document.getElementById("above_email").value = walkInsData[counter][email_index]; document.getElementById("above_phone").value = walkInsData[counter][contact_index]; // document.getElementById("pov").value = walkInsData[counter][pov_index]; if (typeof(walkInsData[counter][pov_index]) === "object"){ document.getElementById("pov").value = convertToString(walkInsData[counter][pov_index]); } else document.getElementById("pov").value = walkInsData[counter][pov_index]; document.getElementById("walkinRemarksText").value = walkInsData[counter][walkinRemarks_index]; document.getElementById('fillForm').disabled = false; document.getElementById("course_dropdown").selectedIndex = 0; //Option 0 document.getElementById("date").value = formatDate(new Date()).slice(0, 10); document.getElementById("stud_name").value = ""; document.getElementById("phone1").value = ""; document.getElementById("phone2").value = ""; document.getElementById("email1").value = ""; document.getElementById("email2").value = ""; document.getElementById("board").value = ""; document.getElementById("school_name").value = ""; let s = document.getElementById("standard") if (s.length > 1){ for(let i=0; i<s.length; i++){ s.remove(s.length-1); } } document.getElementById("standard").selectedIndex = 0; //Option 0 document.getElementById("homeTown").value = ""; document.getElementById("nearestCenter").value = ""; document.getElementById("sourceOfEnquiry").selectedIndex = 0; //Option 0 let eD = document.getElementById("entDate") if (eD.length > 1){ for(let i=0; i<eD.length; i++){ eD.remove(eD.length-1); } } let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); document.getElementById("entDate").selectedIndex = 0; //Option 0 document.getElementById("ent_rec_no").value = ""; document.getElementById("remarks").value = ""; document.getElementById('fillForm').disabled = false; //document.getElementById("submitBtn").disabled = false; } function updateRemarksClicked(){ document.getElementById("checkDuplicate").disabled = true; document.getElementById("updateRemarksBtn").disabled = true; document.getElementById("previous").disabled = true; document.getElementById("next").disabled = true; google.script.run.withSuccessHandler(onSuccessAddRemarks).withFailureHandler(onFailureAddRemarks).updateRemarksClicked([walkInsData[counter][date_index]+(walkInsData[counter][date_index].includes(".")?"":"+05:30"), walkInsData[counter][email_index], walkInsData[counter][contact_index], document.getElementById("walkinRemarksText").value]) function onFailureAddRemarks(e){ Swal.fire({ title: "Error :- " + e, text: "Was not able to add/update remarks. Please try again", icon: "error" }); document.getElementById("checkDuplicate").disabled = false; document.getElementById("updateRemarksBtn").disabled = false; document.getElementById("previous").disabled = false; document.getElementById("next").disabled = false; } function onSuccessAddRemarks(msg){ if (msg === "DONE"){ Swal.fire({ title: "Successfully added/updated", text: "", icon: "success" }); walkInsData[counter][walkinRemarks_index] = document.getElementById("walkinRemarksText").value; document.getElementById("checkDuplicate").disabled = false; document.getElementById("updateRemarksBtn").disabled = false; document.getElementById("previous").disabled = false; document.getElementById("next").disabled = false; } else{ Swal.fire({ title: "Error :- " + msg, text: "Was not able to add/update remarks. Please try again", icon: "error" }); document.getElementById("checkDuplicate").disabled = false; document.getElementById("updateRemarksBtn").disabled = false; document.getElementById("previous").disabled = false; document.getElementById("next").disabled = false; } } } function previousClicked(){ if (counter > 0){ counter -= 1; //console.log(counter) document.getElementById("next").disabled = false; document.getElementById("fillForm").disabled = false; document.getElementById("enquiryDate").value = walkInsData[counter][date_index]; // document.getElementById("pov").value = walkInsData[counter][pov_index]; if (typeof(walkInsData[counter][pov_index]) === "object"){ document.getElementById("pov").value = convertToString(walkInsData[counter][pov_index]); } else document.getElementById("pov").value = walkInsData[counter][pov_index]; document.getElementById("first_name").value = walkInsData[counter][stud_name_index]; document.getElementById("above_email").value = walkInsData[counter][email_index]; document.getElementById("above_phone").value = walkInsData[counter][contact_index]; document.getElementById("walkinRemarksText").value = walkInsData[counter][walkinRemarks_index]; document.getElementById("course_dropdown").selectedIndex = 0; //Option 0 document.getElementById("date").value = formatDate(new Date()).slice(0, 10); document.getElementById("stud_name").value = ""; document.getElementById("phone1").value = ""; document.getElementById("phone2").value = ""; document.getElementById("email1").value = ""; document.getElementById("email2").value = ""; document.getElementById("board").value = ""; document.getElementById("school_name").value = ""; let s = document.getElementById("standard") if (s.length > 1){ for(let i=0; i<s.length; i++){ s.remove(s.length-1); } } document.getElementById("standard").selectedIndex = 0; //Option 0 document.getElementById("homeTown").value = ""; document.getElementById("nearestCenter").value = ""; document.getElementById("sourceOfEnquiry").selectedIndex = 0; //Option 0 let eD = document.getElementById("entDate") if (eD.length > 1){ for(let i=0; i<eD.length; i++){ eD.remove(eD.length-1); } } let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); document.getElementById("entDate").selectedIndex = 0; //Option 0 document.getElementById("ent_rec_no").value = ""; document.getElementById("remarks").value = ""; document.getElementById('fillForm').disabled = false; //document.getElementById("submitBtn").disabled = false; google.script.run.withSuccessHandler(onSuccessBestMatchCode).bestMatch([walkInsData[counter][stud_name_index], walkInsData[counter][[contact_index]], walkInsData[counter][[email_index]]]); } if (counter == 0){document.getElementById("previous").disabled = true;} } function nextClicked(){ if (counter < walkInsData.length - 1){counter += 1;} if (counter == walkInsData.length - 1) {document.getElementById("next").disabled = true;} document.getElementById("previous").disabled = false; document.getElementById("fillForm").disabled = false; document.getElementById("enquiryDate").value = walkInsData[counter][date_index]; // document.getElementById("pov").value = walkInsData[counter][pov_index]; if (typeof(walkInsData[counter][pov_index]) === "object"){ document.getElementById("pov").value = convertToString(walkInsData[counter][pov_index]); } else document.getElementById("pov").value = walkInsData[counter][pov_index]; document.getElementById("first_name").value = walkInsData[counter][stud_name_index]; document.getElementById("above_email").value = walkInsData[counter][email_index]; document.getElementById("above_phone").value = walkInsData[counter][contact_index]; document.getElementById("walkinRemarksText").value = walkInsData[counter][walkinRemarks_index]; document.getElementById("course_dropdown").selectedIndex = 0; //Option 0 document.getElementById("date").value = formatDate(new Date()).slice(0, 10); document.getElementById("stud_name").value = ""; document.getElementById("phone1").value = ""; document.getElementById("phone2").value = ""; document.getElementById("email1").value = ""; document.getElementById("email2").value = ""; document.getElementById("board").value = ""; document.getElementById("school_name").value = ""; let s = document.getElementById("standard") if (s.length > 1){ for(let i=0; i<s.length; i++){ s.remove(s.length-1); } } document.getElementById("standard").selectedIndex = 0; //Option 0 document.getElementById("homeTown").value = ""; document.getElementById("nearestCenter").value = ""; document.getElementById("sourceOfEnquiry").selectedIndex = 0; //Option 0 let eD = document.getElementById("entDate") if (eD.length > 1){ for(let i=0; i<eD.length; i++){ eD.remove(eD.length-1); } } let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); document.getElementById("entDate").selectedIndex = 0; //Option 0 document.getElementById("ent_rec_no").value = ""; document.getElementById("remarks").value = ""; document.getElementById('fillForm').disabled = false; document.getElementById("submitBtn").disabled = false; google.script.run.withSuccessHandler(onSuccessBestMatchCode).bestMatch([walkInsData[counter][stud_name_index], walkInsData[counter][[contact_index]], walkInsData[counter][[email_index]]]); //console.log(walkInsData[counter]) } function onSuccessBestMatchCode(arr_bestMatch){ destroyTable("bestMatchTable"); fillTable("bestMatchTable", arr_bestMatch); } function fillFormClicked(){ document.getElementById('fillForm').disabled = true; document.getElementById("date").value = walkInsData[counter][date_index].slice(0, 10); document.getElementById("stud_name").value = walkInsData[counter][stud_name_index]; if (typeof(walkInsData[counter][pov_index]) === "object"){ let dataToFill = walkInsData[counter][pov_index]; let dropdown = document.getElementById("course_dropdown"); // Loop through each option in the dropdown for (let i = 0; i < dropdown.options.length; i++) { // Check if the option's value matches the stringValue if (dropdown.options[i].value === dataToFill["course"]) { // Set the selected index dropdown.selectedIndex = i; break; } } if(entDate()){ document.getElementById("school_name").value = (dataToFill["sch_name"]==="null"?"":dataToFill["sch_name"]); document.getElementById("homeTown").value = (dataToFill["hometown"]==="null"?"":dataToFill["hometown"]); let sourceOfEnquiryDropdown = document.getElementById("sourceOfEnquiry"); for (let i = 0; i < sourceOfEnquiryDropdown.options.length; i++) { // Check if the option's value matches the stringValue if (sourceOfEnquiryDropdown.options[i].value === "Assigned from Website") { // Set the selected index sourceOfEnquiryDropdown.selectedIndex = i; break; } } let nearestCenterDropdown = document.getElementById("nearestCenter"); for (let i = 0; i < nearestCenterDropdown.options.length; i++) { // Check if the option's value matches the stringValue if (nearestCenterDropdown.options[i].value === dataToFill["nearest_center"]) { // Set the selected index nearestCenterDropdown.selectedIndex = i; break; } } let entranceDateDropdown = document.getElementById("nearestCenter"); for (let i = 0; i < entranceDateDropdown.options.length; i++) { // Check if the option's value matches the stringValue if (entranceDateDropdown.options[i].value === convertDate(dataToFill["ent_dt"])) { // Set the selected index entranceDateDropdown.selectedIndex = i; break; } } let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); } } if (walkInsData[counter][contact_index].includes(", ")){ let contacts = walkInsData[counter][contact_index].split(", "); for (let i = 0; i < contacts.length; i++){ document.getElementById("phone" + (i + 1)).value = contacts[i]; } } else{ document.getElementById("phone1").value = walkInsData[counter][contact_index]; } if (walkInsData[counter][email_index].includes(", ")){ let emails = walkInsData[counter][email_index].split(", "); for (let i = 0; i < emails.length; i++){ document.getElementById("email" + (i + 1)).value = emails[i]; } } else{ document.getElementById("email1").value = walkInsData[counter][email_index]; } } function convertDate(dateStr) { // Define an object to map month abbreviations to month numbers const months = { "Jan": "01", "Feb": "02", "Mar": "03", "Apr": "04", "May": "05", "Jun": "06", "Jul": "07", "Aug": "08", "Sep": "09", "Oct": "10", "Nov": "11", "Dec": "12" }; // Extract day, month, and year using regex const datePattern = /(\d+)(?:st|nd|rd|th)? (\w+), (\d{4})/; const match = dateStr.match(datePattern); if (!match) { console.error("Invalid date format"); return null; } const day = match[1].padStart(2, '0'); // Add leading zero if needed const month = months[match[2]]; // Get the month number const year = match[3]; // Get the year // Return the formatted date return `${year}-${month}-${day}`; } let entranceDetailsJSON = null; let entDetailsCourse = null; function entDate(){ document.getElementById("label_standard_otherSeleceted").style.display = "none"; let eD = document.getElementById("entDate"); let s = document.getElementById("standard"); let nC = document.getElementById('nearestCenter'); [eD, s, nC].forEach((d)=>{ while(d.length>1){ d.remove(1); } }) let course = document.getElementById("course_dropdown").value for (let i = 0; i < courses.length; i++){ if (course == courses[i]){ standardsCorrCours[i].forEach((v)=>{ s[s.length] = new Option(v, v); }); nearestCenter[i].forEach((v)=>{ nC[nC.length] = new Option(v, v); }); entDateCorrCours[i].forEach((v)=>{ if(v.length > 2){ eD[eD.length] = new Option(v, v); } }); document.getElementById("standard_label").innerText = "Standard in " + ay[i]; document.getElementById("label_standard_otherSeleceted").innerText = "Standard in " + ay[i]; if (courseForEntDataAddition[i][0].length == 0){ document.getElementById("entranceDetails").innerHTML = ""; document.getElementById("entranceDetails").style.display = "none"; entranceDetailsJSON = null; } else{ let entranceDetailsDiv = document.getElementById("entranceDetails"); entranceDetailsDiv.innerHTML = "<h4> Please enter the following details for " + courseForEntDataAddition[i][0] + "</h4>"; for (let j = 0; j < courseviseEntDetails.length; j++){ if (courseForEntDataAddition[i][0] === courseviseEntDetails[j][0]){ entDetailsCourse = courseForEntDataAddition[i][0]; entranceDetailsJSON = JSON.parse(courseviseEntDetails[j][1]); for (let x in entranceDetailsJSON){ if (x != "fee_structure"){ if (entranceDetailsJSON[x]["type"] != "dropdown") { // entranceDetailsDiv.innerHTML += `<div class="col s4"> // <label for="entranceDetails${x}">${entranceDetailsJSON[x]["title"]}</label> // <input id="entranceDetails${x}" type="${entranceDetailsJSON[x]["type"]}" class="validate"> // </div>`; // Create div with Materialize column class let div = document.createElement("div"); div.className = "col s4"; // Create label let label = document.createElement("label"); label.textContent = entranceDetailsJSON[x]["title"]; label.setAttribute("for", "entranceDetails" + x); // Create select with ID let select = document.createElement("input"); select.type = entranceDetailsJSON[x]["type"]; select.id = "entranceDetails" + x; // Append label and select to the div div.appendChild(label); div.appendChild(select); // Append everything to the container entranceDetailsDiv.appendChild(div); } else { let div = document.createElement("div"); div.className = "col s4"; // Create label let label = document.createElement("label"); label.textContent = entranceDetailsJSON[x]["title"]; label.setAttribute("for", "entranceDetails" + x); // Create select with ID let select = document.createElement("select"); select.id = "entranceDetails" + x; // select.classList.add("browser-default"); // use this if you're skipping Materialize JS // Create placeholder option let placeholderOption = document.createElement("option"); placeholderOption.value = ""; placeholderOption.textContent = "Select " + entranceDetailsJSON[x]["title"]; placeholderOption.disabled = true; placeholderOption.selected = true; // Add placeholder to select select.appendChild(placeholderOption); // Populate dropdown entranceDetailsJSON[x]["dropdownVals"].forEach(val => { let option = document.createElement("option"); option.value = val; option.textContent = val; select.appendChild(option); }); // Append label and select to the div div.appendChild(label); div.appendChild(select); // Append everything to the container entranceDetailsDiv.appendChild(div); } } } } } entranceDetailsDiv.style.display = "block"; } break; } } s[s.length] = new Option("Other", "Other"); eD.selectedIndex = 0; s.selectedIndex = 0; let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); return true; } function onTheSpotSelected(){ if(document.getElementById("entDate").value === "On the Spot"){ document.getElementById("div_eed_onTheSpotSelected").style.display = "block"; document.getElementById("eed_onTheSpotSelected").required = true; } else{ document.getElementById("div_eed_onTheSpotSelected").style.display = "none"; document.getElementById("eed_onTheSpotSelected").value = ""; document.getElementById("eed_onTheSpotSelected").required = false; } } function validate(){ document.getElementById("submitBtn").disabled = true; if (document.getElementById("course_dropdown").value == "") { Swal.fire({ title: "Kindly select the Course", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("stud_name").value.length < 2) { Swal.fire({ title: "Kindly input Student Name", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("phone1").value.length < 2) { Swal.fire({ title: "Kindly enter the Primary Contact No.", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("email1").value.length < 2 && document.getElementById("email1").value.slice(document.getElementById("email1").value.search('@')).length < 2 && (document.getElementById("course_dropdown").value.search("Solapur") == (-1) && document.getElementById("course_dropdown").value.search("Barshi") == (-1) && document.getElementById("course_dropdown").value.search("Pandharpur") == (-1))) { Swal.fire({ title: "Invalid Primary Email", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("standard").value == "" || (document.getElementById("standard").value == 'Other' && document.getElementById("standard_otherSeleceted").value == '')) { Swal.fire({ title: "Kindly select the Standard", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("div_eed_onTheSpotSelected").style.display === "block" && document.getElementById("eed_onTheSpotSelected").value === ""){ Swal.fire({ title: "Kindly enter the On the Spot entrance date", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("ent_rec_no").value.length > 3 && document.getElementById("entDate").value === "" && document.getElementById("course_dropdown").value.search("Solapur") == (-1) && document.getElementById("course_dropdown").value.search("Pandharpur") == (-1) && document.getElementById("course_dropdown").value.search("Barshi") == (-1)){ Swal.fire({ title: "Kindly enter the Entrance date", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("nearestCenter").value == "") { Swal.fire({ title: "Kindly select the Nearest Center", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("sourceOfEnquiry").value == "") { Swal.fire({ title: "Kindly select the Sourcce of enquiry", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; return false; } else if (document.getElementById("opc_dropdown").value === ""){ Swal.fire({ title: "Kindly select OPC", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; } else if (document.getElementById("entranceDetails").style.display === "block"){ let iterator = 0; for (let x in entranceDetailsJSON){ if (x != "fee_structure" && String(document.getElementById("entranceDetails" + x).value) === ""){ iterator += 1; Swal.fire({ title: entranceDetailsJSON[x]["title"] + " not entered!", text: "", icon: "error", confirmButtonColor: "teal", confirmButtonText: "OK" }); document.getElementById("submitBtn").disabled = false; break; } else if (x != "fee_structure") entranceDetailsJSON[x]["value"] = document.getElementById("entranceDetails" + x).value; } if (iterator == 0) submitClicked(); } else submitClicked(); } function onSuccessSubmit(submitBool = true){ document.getElementById("course_dropdown").selectedIndex = 0; //Option 0 document.getElementById("date").value = formatDate(new Date()).slice(0, 10); document.getElementById("stud_name").value = ""; document.getElementById("phone1").value = ""; document.getElementById("phone2").value = ""; document.getElementById("email1").value = ""; document.getElementById("email2").value = ""; document.getElementById("board").value = ""; document.getElementById("school_name").value = ""; let s = document.getElementById("standard") if (s.length > 1){ for(let i=0; i<s.length; i++){ s.remove(s.length-1); } } document.getElementById("standard").selectedIndex = 0; //Option 0 document.getElementById("homeTown").value = ""; document.getElementById("nearestCenter").value = ""; document.getElementById("sourceOfEnquiry").selectedIndex = 0; //Option 0 document.getElementById("opc_dropdown").selectedIndex = 0; //Option 0 let eD = document.getElementById("entDate") if (eD.length > 1){ for(let i=0; i<eD.length; i++){ eD.remove(eD.length-1); } } document.getElementById("div_standard_otherSeleceted").style.display = 'none'; let sel = document.querySelectorAll('select'); M.FormSelect.init(sel); document.getElementById("entDate").selectedIndex = 0; //Option 0 onTheSpotSelected(); destroyTable("bestMatchTable2"); document.getElementById("ent_rec_no").value = ""; document.getElementById("remarks").value = ""; document.getElementById("entranceDetails").innerHTML = ""; document.getElementById("entranceDetails").style.display = "none"; entranceDetailsJSON = null; entDetailsCourse = null; //document.getElementById("submitBtn").disabled = false; // console.log(walkInsData); // console.log(walkInsData.length) if (walkInsData.length == 0){ document.getElementById("myHeader").innerText = "All pending walk-ins submitted."; document.getElementById("first_name").value = ""; document.getElementById("above_email").value = ""; document.getElementById("above_phone").value = ""; document.getElementById("pov").value = ""; document.getElementById("walkinRemarksText").value = ""; document.getElementById("enquiryDate").value = ""; document.getElementById("checkDuplicate").disabled = true; document.getElementById("updateRemarksBtn").disabled = true; document.getElementById("previous").disabled = true; document.getElementById("next").disabled = true; document.getElementById("fillForm").disabled = true; destroyTable("bestMatchTable"); document.body.style.cursor = "default"; return; } else{ if (document.getElementById("fillForm").disabled && submitBool) { google.script.run.setPendingFalse([walkInsData[counter][date_index]+(walkInsData[counter][date_index].includes(".")?"":"+05:30"), walkInsData[counter][email_index], walkInsData[counter][contact_index]]); walkInsData.splice(counter, 1); if (walkInsData.length == 0){ document.getElementById("myHeader").innerText = "All pending walk-ins submitted."; document.getElementById("first_name").value = ""; document.getElementById("above_email").value = ""; document.getElementById("above_phone").value = ""; document.getElementById("pov").value = ""; document.getElementById("walkinRemarksText").value = ""; document.getElementById("enquiryDate").value = ""; document.getElementById("checkDuplicate").disabled = true; document.getElementById("updateRemarksBtn").disabled = true; document.getElementById("previous").disabled = true; document.getElementById("next").disabled = true; document.getElementById("fillForm").disabled = true; destroyTable("bestMatchTable"); document.body.style.cursor = "default"; return; } } } if (counter > walkInsData.length - 1){ counter -= 1; } google.script.run.withSuccessHandler(onSuccessBestMatchCode).bestMatch([walkInsData[counter][stud_name_index], walkInsData[counter][[contact_index]], walkInsData[counter][[email_index]]]); document.getElementById("myHeader").innerText = "Pending Walk-Ins("+walkInsData.length+")"; document.getElementById("first_name").value = walkInsData[counter][stud_name_index]; document.getElementById("above_email").value = walkInsData[counter][email_index]; document.getElementById("above_phone").value = walkInsData[counter][contact_index]; // document.getElementById("pov").value = walkInsData[counter][pov_index]; if (typeof(walkInsData[counter][pov_index]) === "object"){ document.getElementById("pov").value = convertToString(walkInsData[counter][pov_index]); } else document.getElementById("pov").value = walkInsData[counter][pov_index]; document.getElementById("walkinRemarksText").value = walkInsData[counter][walkinRemarks_index]; document.getElementById("enquiryDate").value = walkInsData[counter][date_index]; document.getElementById('fillForm').disabled = false; document.getElementById("checkDuplicate2").disabled = false; document.getElementById("clear").disabled = true; document.getElementById("submitBtn").disabled = true; destroyTable("bestMatchTable2"); document.body.style.cursor = "default"; } function onFailureSubmit(e){ document.body.style.cursor = "default"; Swal.fire({ title: "Error :- " + e, text: "Please try again", icon: "error" }); document.getElementById("submitBtn").disabled = false; } function submitClicked(){ document.body.style.cursor = "wait"; let inputAY = ""; let course = document.getElementById("course_dropdown").value for (let i = 0; i < courses.length; i++){ if (course == courses[i]){ inputAY = ay[i]; break; } } google.script.run.withSuccessHandler(onSuccessSubmit).withFailureHandler(onFailureSubmit).submitClicked([document.getElementById('date').value, document.getElementById("stud_name").value, document.getElementById("phone1").value, document.getElementById("phone2").value, document.getElementById("email1").value,document.getElementById("email2").value, document.getElementById("board").value, document.getElementById("school_name").value, document.getElementById("standard").value, document.getElementById('standard_otherSeleceted').value, document.getElementById("homeTown").value, document.getElementById("nearestCenter").value, document.getElementById("sourceOfEnquiry").value, (document.getElementById("entDate").value === "On the Spot"?document.getElementById("eed_onTheSpotSelected").value:document.getElementById("entDate").value), document.getElementById("ent_rec_no").value, document.getElementById("remarks").value, document.getElementById("opc_dropdown").value, inputAY, course, entranceDetailsJSON, entDetailsCourse, document.getElementById("next_calling_date").value]); } // Code of Edit Entrace Receipt No pushToDb1 = new Array(); // [['Student Name', 'Calling Remarks', 'Enquiry Date', 'Course', 'Nearest Center', 'School Name', 'Hometown']] pushToDb2 = new Array(); // [['Student Name', 'Receipt Number', 'Payment Date', 'Amount', 'Payment Remarks']] pushToDb_merged = [['Student Name', 'Calling Remarks', 'Enquiry Date', 'Course', 'Receipt Number', 'Payment Date', 'Amount', 'Payment Remarks']]; to_unverify = new Array(); to_unverifyEnqUID = new Array(); editEntRecNoEnqUIDArr = new Array(); pushToDbEnqUIDs = new Array(); lengthOf_pushToDb1 = 4; lengthOf_pushToDb2 = 4; lengthOf_toUnverify = pushToDb_merged[0].length; counter_mergedData = 0; function editEntRecNoEnqDataGoClicked(){ document.getElementById("editEntRecNoEnqDataGoBtn").disabled = true; document.body.style.cursor = "wait"; document.getElementById('paymentDetails_remarks').value = ''; let table = document.getElementById("editEntRecNoEnqDataTable"); for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } var key = document.getElementById("editEntRecNoEnqDataSearchKey").value; var center = document.getElementById("center").value; if (pushToDb_merged.length > 1 && (key == pushToDb_merged[0] || key == pushToDb_merged[2])){ document.getElementById('paymentDetails_remarks').value = key + ' is already verified.'; document.body.style.cursor = "default"; } else { google.script.run.withSuccessHandler(onSuccessSchDataGo).withFailureHandler(onFailureSchDataGo).editEntRecNoEnqDataGoClicked(key); function onFailureSchDataGo(e){ document.getElementById('paymentDetails_remarks').value = ''+ e; document.getElementById("editEntRecNoEnqDataGoBtn").disabled = false; document.body.style.cursor = "default"; } function onSuccessSchDataGo(data){ // data ≈ ['Transaction ID', 'Value Date', 'Description', 'Transaction Amount(INR)'] try{ if (data.data.length == 1) { document.getElementById('paymentDetails_remarks').value = 'Search Key not available in DataBase. Kindly try again with a different one.' } else{ // editEntRecNoEnqUIDArr = data.enqUIDArr; data.data.forEach((row, i)=>{ if(i>0){ var tRow = table.insertRow(-1); row.forEach((c, i)=>{ tRow.insertCell(i).innerHTML = c; }); }else{ var tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; row.forEach((c, i)=>{ tRow.insertCell(i).innerHTML = "<b>"+c+"</b>"; }); } }); } } catch(e){ document.getElementById('paymentDetails_remarks').value = ''+ e; document.getElementById("editEntRecNoEnqDataGoBtn").disabled = false; document.body.style.cursor = "default"; return; } if (table.rows.length == 2){ // for(var j = 0; j < table.rows[1].cells.length; j++){ for(let j = 0; j < lengthOf_pushToDb1; j++){ if (pushToDb1.length < lengthOf_pushToDb1){pushToDb1.push('' + table.rows[1].cells[j].innerHTML);} else if (pushToDb1.length == lengthOf_pushToDb1) {pushToDb1.length = 0; pushToDb1.push('' + table.rows[1].cells[j].innerHTML);} } if (pushToDb2.length == lengthOf_pushToDb2) {document.getElementById('allPaymentsDataVerifyBtn').disabled = false;} highlightTableRow(table, table.rows[1]); if (editEntRecNoEnqUIDArr.length == 1) editEntRecNoEnqUIDArr = [data.enqUIDArr[1]]; else (editEntRecNoEnqUIDArr.push(data.enqUIDArr[1])); } else if (table.rows.length > 2){ rows = table.getElementsByTagName("tr"); for (let i = 1; i < rows.length; i++) { // Start from 1 to skip the header row fillArrays(data.enqUIDArr, table, rows[i], i); } } document.body.style.cursor = "default"; document.getElementById("editEntRecNoEnqDataGoBtn").disabled = false; } } } function fillArrays(enqUIDArr, tableElement, rowElement, i){ rowElement.addEventListener('click', function() { // for(var j = 0; j < this.cells.length; j++) { for(let j = 0; j < lengthOf_pushToDb1; j++){ if (pushToDb1.length < lengthOf_pushToDb1){pushToDb1.push('' + this.cells[j].innerHTML);} else if (pushToDb1.length == lengthOf_pushToDb1) {pushToDb1.length = 0; pushToDb1.push('' + this.cells[j].innerHTML);} if (pushToDb2.length == lengthOf_pushToDb2) {document.getElementById('allPaymentsDataVerifyBtn').disabled = false;} } highlightTableRow(tableElement, this); if (editEntRecNoEnqUIDArr.length == 1) editEntRecNoEnqUIDArr = [enqUIDArr[i]]; else (editEntRecNoEnqUIDArr.push(enqUIDArr[i])); console.log(editEntRecNoEnqUIDArr); }); } function allPaymentsDataGoClicked(){ document.getElementById("allPaymentsDataGoBtn").disabled = true; document.body.style.cursor = "wait"; document.getElementById('paymentDetails_remarks').value = ''; var table = document.getElementById("allPaymentsDataTable"); for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } var key = document.getElementById("allPaymentsDataSearchKey").value; var center = document.getElementById("center").value; google.script.run.withSuccessHandler(onSuccessSchDataGo).withFailureHandler(onFailureSchDataGo).allPaymentsDataGoClicked(key); function onFailureSchDataGo(e){ console.log(e); document.getElementById('paymentDetails_remarks').value = '' + e; document.body.style.cursor = "default"; document.getElementById("allPaymentsDataGoBtn").disabled = false; } function onSuccessSchDataGo(data){ try{ if (data.length == 1) { document.getElementById('paymentDetails_remarks').value = 'Payment Details not available in DataBase for '+ document.getElementById("allPaymentsDataSearchKey").value +'. Kindly try again with a different one.' } else{ data.forEach((row, i)=>{ if(i>0){ var tRow = table.insertRow(-1); row.forEach((c, i)=>{ tRow.insertCell(i).innerHTML = c; }); }else{ var tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; tRow.style.position = 'sticky'; tRow.style.top = '0px'; row.forEach((c, i)=>{ tRow.insertCell(i).innerHTML = "<b>"+c+"</b>"; }); } }); } } catch(e){ document.getElementById('paymentDetails_remarks').value = '' + e; document.body.style.cursor = "default"; document.getElementById("allPaymentsDataGoBtn").disabled = false; } document.body.style.cursor = "default"; document.getElementById("allPaymentsDataGoBtn").disabled = false; if (table.rows.length == 2){ // for(var j = 0; j < table.rows[1].cells.length; j++){ for(let j = 1; j < lengthOf_pushToDb2 + 1; j++){ if (pushToDb2.length < lengthOf_pushToDb2){pushToDb2.push('' + table.rows[1].cells[j].innerHTML);} else if (pushToDb2.length == lengthOf_pushToDb2) {pushToDb2.length = 0; pushToDb2.push('' + table.rows[1].cells[j].innerHTML);} } if (pushToDb1.length == lengthOf_pushToDb1) {document.getElementById('allPaymentsDataVerifyBtn').disabled = false;} highlightTableRow(table, table.rows[1]); } else if (table.rows.length >= 2){ for(let i = 1; i < table.rows.length; i++) { table.rows[i].addEventListener('click', function() { // for(var j = 0; j < this.cells.length; j++) { for(let j = 1; j < lengthOf_pushToDb2 + 1; j++){ if (pushToDb2.length < lengthOf_pushToDb2){pushToDb2.push('' + this.cells[j].innerHTML);} else if (pushToDb2.length == lengthOf_pushToDb2) {pushToDb2.length = 0; pushToDb2.push('' + this.cells[j].innerHTML);} if (pushToDb1.length == lengthOf_pushToDb1) {document.getElementById('allPaymentsDataVerifyBtn').disabled = false;} } highlightTableRow(table, this); }); } } } } function allPaymentsDataVerifyClicked(){ document.getElementById('allPaymentsDataVerifyBtn').disabled = true; document.body.style.cursor = "wait"; document.getElementById('allPaymentsDataDoneBtn').disabled = false; trans_id = 4; if(counter_mergedData != 0){ for (let i = 0; i < counter_mergedData; i++){ // if ('' + pushToDb_merged[i+1][0] == '' + pushToDb1[0]){ if ('' + pushToDbEnqUIDs[i][0] == '' + editEntRecNoEnqUIDArr[0]){ document.getElementById('paymentDetails_remarks').value = pushToDb1[0] + ' is already verified.'; document.body.style.cursor = "default"; return } if ('' + pushToDb_merged[i+1][trans_id] == '' + pushToDb2[0]){ document.getElementById('paymentDetails_remarks').value = pushToDb2[0] + ' is already verified.'; document.body.style.cursor = "default"; return } } } counter_mergedData += 1 pushToDb_merged.push(pushToDb1.concat(pushToDb2)); pushToDbEnqUIDs.push(editEntRecNoEnqUIDArr); pushToDb1.length = 0; pushToDb2.length = 0; console.log(pushToDbEnqUIDs); // editEntRecNoEnqUIDArr.length = 0; for(let j = 0; j < 2; j++){ if (j==0){ var table = document.getElementById("editEntRecNoEnqDataTable"); } else{ table = document.getElementById("allPaymentsDataTable"); } for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } } document.getElementById('editEntRecNoEnqDataSearchKey').value = ''; document.getElementById('allPaymentsDataSearchKey').value = ''; document.getElementById('allPaymentsDataDoneBtn').innerHTML = 'Done(' + counter_mergedData + ')'; document.body.style.cursor = "default"; console.log(pushToDb_merged); console.log(pushToDbEnqUIDs); addTableRow(); } function addTableRow(){ var table = document.getElementById('verifiedPayTable') if (counter_mergedData > 0){ for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } pushToDb_merged.forEach((row, i_)=>{ if(i_>0){ var tRow = table.insertRow(-1); row.forEach((c, i)=>{ if(i != 10){ tRow.insertCell(i).innerHTML = c; }else{ // tRow.insertCell(i).innerHTML = "<div class = 'contenteditable' id = 'courseName"+i_+"' >" + c +"</div>"; let div1 = document.createElement('div'); div1.innerHTML = "" + c; div1.id = "courseName" + i_ tRow.insertCell(i).appendChild(div1); div1.contentEditable = true; } }); }else{ var tRow = table.createTHead().insertRow(0); tRow.style.background = "teal"; tRow.style.color = "#ffffff"; tRow.style.position = 'sticky'; tRow.style.top = '0px'; row.forEach((c, i)=>{ tRow.insertCell(i).innerHTML = "<b>"+c+"</b>"; }); } }); for(let i = 1; i < table.rows.length; i++) { fillUnverifyArr(table, table.rows[i], i); } } else{ for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } } //console.log(pushToDb_merged); } function fillUnverifyArr(tableElement, rowElement, i){ rowElement.addEventListener('click', function() { document.getElementById('allPaymentsDataUnverifyBtn').disabled = false; if (to_unverify.length != 0){ to_unverify.length = 0; to_unverifyEnqUID.length = 0; } to_unverify.push([]); to_unverifyEnqUID.push([]); for (let j = 0; j < tableElement.rows[1].cells.length; j++){ to_unverify[to_unverify.length - 1].push('' + this.cells[j].innerHTML); } highlightTableRow(tableElement, this); to_unverifyEnqUID[to_unverifyEnqUID.length - 1].push(pushToDbEnqUIDs[i-1]); console.log(to_unverifyEnqUID); }); } function allPaymentsDataUnverifyClicked(){ document.getElementById('allPaymentsDataUnverifyBtn').disabled = true; trans_id = 4; //Index of Transaction ID pushToDb_merged = pushToDb_merged.filter(function (o1) { // filter out (!) items in result2 return !to_unverify.some(function (o2) { return '' + o1[trans_id] === '' + o2[trans_id]; // assumes unique id }); }); pushToDbEnqUIDs = pushToDbEnqUIDs.filter(function (o1) { // filter out (!) items in result2 return !to_unverifyEnqUID.some(function (o2) { return '' + o1[0] === '' + o2[0]; // assumes unique id }); }); counter_mergedData = pushToDb_merged.length - 1 document.getElementById('allPaymentsDataDoneBtn').innerHTML = 'Done(' + counter_mergedData + ')'; console.log(pushToDbEnqUIDs); to_unverify = []; addTableRow(); if (counter_mergedData == 0){ document.getElementById('allPaymentsDataUnverifyBtn').disabled = true; document.getElementById('allPaymentsDataDoneBtn').disabled = true; } } function allPaymentsDataDoneClicked(){ document.getElementById('allPaymentsDataDoneBtn').disabled = true; document.body.style.cursor = "wait"; document.getElementById('allPaymentsDataUnverifyBtn').disabled = true; document.getElementById('allPaymentsDataDoneBtn').innerHTML = 'Done' let table = document.getElementById('verifiedPayTable') // if (!document.getElementById('verifyBtn').disabled){ // verifyClicked(); // } // for (i = 1; i < pushToDb_merged.length; i++){ // pushToDb_merged[i][10] = '' + document.getElementById("courseName" + i).innerHTML // } google.script.run.withSuccessHandler(onSuccessDone).allPaymentsDataDoneClicked(pushToDb_merged, pushToDbEnqUIDs, feeperson); // add feeperson and center function onSuccessDone(data){ //console.log(data); for(let j = 0; j < 3; j++){ if (document.getElementById("editEntRecNoEnqDataTable").rows.length != 0){ let table = document.getElementById("editEntRecNoEnqDataTable"); for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } pushToDb1.length = 0 } else if (document.getElementById("allPaymentsDataTable").rows.length != 0){ let table = document.getElementById("allPaymentsDataTable"); for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } pushToDb2.length = 0 } else { let table = document.getElementById("verifiedPayTable"); for(let i=table.rows.length-1; i>=0; i--){ table.rows[i].remove(); } to_unverify.length = 0; } } document.getElementById('editEntRecNoEnqDataSearchKey').value = ''; document.getElementById('allPaymentsDataSearchKey').value = ''; document.getElementById('paymentDetails_remarks').value = 'Successfully verified '+ counter_mergedData + ' entries and pushed to the DataBase'; document.body.style.cursor = "default"; counter_mergedData = 0; pushToDbEnqUIDs.length = 0; pushToDb_merged = [['Student Name', 'Calling Remarks', 'Enquiry Date', 'Course', 'Receipt Number', 'Payment Date', 'Amount', 'Payment Remarks']]; } } function highlightTableRow(table, row) { // Remove highlight from previously selected row, if any const previouslySelectedRow = table.querySelector('tr.selected'); if (previouslySelectedRow) { previouslySelectedRow.classList.remove('selected'); } // Highlight the clicked row row.classList.add('selected'); } function schDataGoClicked(){ document.getElementById("schDataGoBtn").disabled = true; document.body.style.cursor = "wait"; destroyTable("schDataOutTable"); let key = document.getElementById("schDataKey").value; let center = document.getElementById("center").value; if (key.length < 2){ Swal.fire({ title: "Kindly try a valid search", icon: "error" }); document.body.style.cursor = "default"; document.getElementById("schDataGoBtn").disabled = false; return; } google.script.run.withSuccessHandler(onSuccessSchDataGo).withFailureHandler(onFailureSchData).schDataGoClicked(key, myStudentsTabCourses); function onFailureSchData(e){ document.body.style.cursor = "default"; Swal.fire({ title: "Error :- " + e, text: "Please try another search", icon: "error" }); document.getElementById("schDataGoBtn").disabled = false; } function onSuccessSchDataGo(data){ if (data == [] || data == null){ document.body.style.cursor = "default"; Swal.fire({ title: "No records found", text: "Please try another search", icon: "error" }); document.getElementById("schDataGoBtn").disabled = false; } else{ fillTable("schDataOutTable", data); let table = document.getElementById("schDataOutTable"); let rows = table.getElementsByTagName('tr') rows[0].style.position = 'sticky'; rows[0].style.top = '0px'; document.body.style.cursor = "default"; document.getElementById("schDataGoBtn").disabled = false; } } } </script> </div>
your task is to
Fixes:
1.when load website -> then load go button then get preloaded data.
2.cocat remove from query make separate columns for mobile1,mobile2.
3.when someone click on table then popup add and give edits options.
4.table data pop up.
and give updated full code for code.gs and main.html code please
and give full updated code please and correct code please
Thought
Answer
It sounds like you’d like to:
- Automatically fire your “Go” action on page‐load (so your initial data appears without the user having to click “Go”).
- In your follow‐up query, stop concatenating
mob1andmob2into a single “Phone” column and instead return them as two separate columns. - Whenever the user clicks on any row in a results table, show a SweetAlert (or similar) pop-up that lets them edit that row’s data and submit changes back to the server.
- Make sure the pop-up is fully populated with the clicked row’s data.
Before I roll up and refactor both Code.gs and main.html in their entirety, could you help me nail down a couple of details?
- Which “Go” button/data set should auto-load on page load? Is it the “Follow ups” table (i.e.
followUpsGoClickedInGS), or another one? - Which table(s) do you want the row-click → pop-up/edit flow on? You’ve already built it for the “My Students” view; do you also want it on the Follow-ups, Search, or any others?
- For editing, exactly which fields should be editable in the pop-up? (All columns, or only a subset like “Calling Remarks” and “Next Call Date”?)
Once I have those, I’ll provide the full, updated Code.gs and main.html.