Fee Records Management System
Full Apps-script Source Code
// Global variables for easy sheet access const SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet(); const REGISTRATION_SHEET_NAME = "Student Registration"; function doGet(e) { const action = e.parameter.action; if (action === "getStudents") { return getStudents(); } if (action === "getStudentData") { const name = e.parameter.name; return getStudentData(name); } if (action === "getStatement") { const name = e.parameter.name; return getStatement(name); } // ✅ New: Get all data for the dashboard, including unpaid students, for a specific month or all months if (action === "getAllData") { return getAllData(e.parameter.month); } // ✅ New: Get list of all available months for the dropdown menu if (action === "getMonths") { return getAvailableMonths(); } return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Invalid action' })) .setMimeType(ContentService.MimeType.JSON); } function doPost(e) { try { const postData = JSON.parse(e.postData.contents); const action = postData.action; const data = postData.data; if (action === "registerStudent") { return registerStudent(data); } if (action === "submitFee") { return submitFee(data); } if (action === "markWithdrawn") { return markWithdrawn(data); } if (action === "updateStudentDetails") { return updateStudentDetails(data); } // ✅ NEW: Handle fee editing action if (action === "editFeeDetails") { return editFeeDetails(data); } return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Invalid action' })) .setMimeType(ContentService.MimeType.JSON); } catch (error) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: error.message })) .setMimeType(ContentService.MimeType.JSON); } } // --- NEW: Edit Fee Details Function --- function editFeeDetails(data) { const sheetName = data.month; const sheet = SPREADSHEET.getSheetByName(sheetName); if (!sheet) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: `The sheet for '${sheetName}' does not exist.` })).setMimeType(ContentService.MimeType.JSON); } const sheetData = sheet.getDataRange().getValues(); let studentRowIndex = -1; for (let i = 1; i < sheetData.length; i++) { if (sheetData[i][0] && sheetData[i][0].toLowerCase() === data.name.toLowerCase()) { studentRowIndex = i + 1; // getRange is 1-based break; } } if (studentRowIndex === -1) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: `No fee record found for student '${data.name}' in '${sheetName}'.` })).setMimeType(ContentService.MimeType.JSON); } // Get the row, then update the values const rowRange = sheet.getRange(studentRowIndex, 1, 1, sheetData[0].length); const rowData = rowRange.getValues()[0]; // Update paid amount and status rowData[4] = data.paidAmount; // Paid Amount is the 5th column (index 4) rowData[5] = data.status; // Status is the 6th column (index 5) // Set the updated values back to the sheet rowRange.setValues([rowData]); return ContentService.createTextOutput(JSON.stringify({ status: 'success', message: `Fee details for '${data.name}' in '${sheetName}' updated successfully.` })).setMimeType(ContentService.MimeType.JSON); } // --- Update Student Details --- function updateStudentDetails(data) { const regSheet = SPREADSHEET.getSheetByName(REGISTRATION_SHEET_NAME); if (!regSheet) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Registration sheet not found.' })).setMimeType(ContentService.MimeType.JSON); } const regData = regSheet.getDataRange().getValues(); let studentRowIndex = -1; for (let i = 1; i < regData.length; i++) { if (regData[i][0].toLowerCase() === data.name.toLowerCase()) { studentRowIndex = i + 1; break; } } if (studentRowIndex === -1) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Student not found.' })).setMimeType(ContentService.MimeType.JSON); } const range = regSheet.getRange(studentRowIndex, 1, 1, 6); const row = range.getValues()[0]; row[1] = data.class; row[3] = data.monthlyFee; row[4] = data.status; row[5] = data.withdrawnDate; range.setValues([row]); return ContentService.createTextOutput(JSON.stringify({ status: 'success' })).setMimeType(ContentService.MimeType.JSON); } // --- Updated: Get Specific Student Data & Fee History (for Fee Entry Summary) --- function getStudentData(name) { const regSheet = SPREADSHEET.getSheetByName(REGISTRATION_SHEET_NAME); if (!regSheet) { return ContentService.createTextOutput(JSON.stringify(null)).setMimeType(ContentService.MimeType.JSON); } const regData = regSheet.getDataRange().getValues(); let studentDetails = null; for (let i = 1; i < regData.length; i++) { if (regData[i][0].toLowerCase() === name.toLowerCase()) { studentDetails = { name: regData[i][0], class: regData[i][1], admissionDate: Utilities.formatDate(new Date(regData[i][2]), "GMT", "yyyy-MM-dd"), monthlyFee: regData[i][3], status: regData[i][4], withdrawnDate: regData[i][5] ? Utilities.formatDate(new Date(regData[i][5]), "GMT", "yyyy-MM-dd") : '', }; break; } } if (!studentDetails) { return ContentService.createTextOutput(JSON.stringify(null)).setMimeType(ContentService.MimeType.JSON); } // Calculate total fee paid and months present from ALL sheets let totalFeePaid = 0; let monthsPresent = new Set(); const sheets = SPREADSHEET.getSheets(); sheets.forEach(sheet => { const sheetName = sheet.getName(); if (sheetName.match(/^\w+\s\d{4}$/)) { const data = sheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (data[i][0] && data[i][0].toLowerCase() === name.toLowerCase()) { totalFeePaid += parseFloat(data[i][4] || 0); monthsPresent.add(sheetName); } } } }); studentDetails.totalFeePaid = totalFeePaid; studentDetails.monthsPresent = monthsPresent.size; return ContentService.createTextOutput(JSON.stringify(studentDetails)) .setMimeType(ContentService.MimeType.JSON); } // --- New Student Registration Function --- function registerStudent(data) { let sheet = SPREADSHEET.getSheetByName(REGISTRATION_SHEET_NAME); if (!sheet) { sheet = SPREADSHEET.insertSheet(REGISTRATION_SHEET_NAME); sheet.appendRow(["Name", "Class", "Date of Admission", "Monthly Fee", "Status", "Date of Dropout"]); } // Check for duplicate name AND class const regData = sheet.getDataRange().getValues(); for (let i = 1; i < regData.length; i++) { if (regData[i][0].toLowerCase() === data.name.toLowerCase() && regData[i][1].toLowerCase() === data.class.toLowerCase()) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Student with this name and class has already registered.' })) .setMimeType(ContentService.MimeType.JSON); } } sheet.appendRow([data.name, data.class, data.admissionDate, data.monthlyFee, "Active", ""]); return ContentService.createTextOutput(JSON.stringify({ status: 'success' })) .setMimeType(ContentService.MimeType.JSON); } // --- Get All Students Function (for autocomplete) --- function getStudents() { const sheet = SPREADSHEET.getSheetByName(REGISTRATION_SHEET_NAME); if (!sheet) { return ContentService.createTextOutput(JSON.stringify([])).setMimeType(ContentService.MimeType.JSON); } const data = sheet.getDataRange().getValues(); const students = data.slice(1).map(row => [row[0], row[1], row[2], row[3], row[4], row[5]]); return ContentService.createTextOutput(JSON.stringify(students)) .setMimeType(ContentService.MimeType.JSON); } // --- Get Specific Student Data & Fee History (for Fee Entry Summary) --- function getStudentData(name) { const regSheet = SPREADSHEET.getSheetByName(REGISTRATION_SHEET_NAME); if (!regSheet) { return ContentService.createTextOutput(JSON.stringify(null)).setMimeType(ContentService.MimeType.JSON); } const regData = regSheet.getDataRange().getValues(); let studentDetails = null; for (let i = 1; i < regData.length; i++) { if (regData[i][0].toLowerCase() === name.toLowerCase()) { studentDetails = { name: regData[i][0], class: regData[i][1], admissionDate: Utilities.formatDate(new Date(regData[i][2]), "GMT", "yyyy-MM-dd"), monthlyFee: regData[i][3], status: regData[i][4], withdrawnDate: regData[i][5] ? Utilities.formatDate(new Date(regData[i][5]), "GMT", "yyyy-MM-dd") : null, }; break; } } if (!studentDetails) { return ContentService.createTextOutput(JSON.stringify(null)).setMimeType(ContentService.MimeType.JSON); } // Calculate total fee paid and months present from ALL sheets let totalFeePaid = 0; let monthsPresent = new Set(); const sheets = SPREADSHEET.getSheets(); sheets.forEach(sheet => { const sheetName = sheet.getName(); if (sheetName.match(/^\w+\s\d{4}$/)) { const data = sheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (data[i][0] && data[i][0].toLowerCase() === name.toLowerCase()) { totalFeePaid += parseFloat(data[i][4] || 0); monthsPresent.add(sheetName); } } } }); studentDetails.totalFeePaid = totalFeePaid; studentDetails.monthsPresent = monthsPresent.size; return ContentService.createTextOutput(JSON.stringify(studentDetails)) .setMimeType(ContentService.MimeType.JSON); } // --- Submit Fee Function (to monthly sheet) --- function submitFee(data) { const monthName = data.month; let sheet = SPREADSHEET.getSheetByName(monthName); if (!sheet) { sheet = SPREADSHEET.insertSheet(monthName); sheet.appendRow(["Name", "Class", "Date", "Monthly Fee", "Paid Amount", "Status"]); } const monthlyData = sheet.getDataRange().getValues(); const monthlyFeeFromData = parseFloat(data.monthlyFee); const paidAmountFromData = parseFloat(data.paidAmount); let studentRowIndex = -1; let currentPaidAmount = 0; // Find the student's row and current paid amount for (let i = 1; i < monthlyData.length; i++) { if (monthlyData[i][0] && monthlyData[i][0].toLowerCase() === data.name.toLowerCase()) { studentRowIndex = i + 1; currentPaidAmount = parseFloat(monthlyData[i][4] || 0); break; } } const newTotalPaidAmount = currentPaidAmount + paidAmountFromData; let status; // New logic for 'Extra Paid' status if (newTotalPaidAmount > monthlyFeeFromData) { status = "Extra Paid"; } else if (newTotalPaidAmount === monthlyFeeFromData) { status = "Paid"; } else { status = "Half Paid"; } const today = new Date(); const formattedDate = Utilities.formatDate(today, SPREADSHEET.getSpreadsheetTimeZone(), "yyyy-MM-dd"); if (studentRowIndex === -1) { // Student not found, add a new row sheet.appendRow([ data.name, data.class, formattedDate, monthlyFeeFromData, newTotalPaidAmount, status ]); } else { // Student found, update the existing row sheet.getRange(studentRowIndex, 5).setValue(newTotalPaidAmount); sheet.getRange(studentRowIndex, 6).setValue(status); } // ✅ NEW: Send confirmation email sendFeeConfirmationEmail(data, newTotalPaidAmount, status, formattedDate); return ContentService.createTextOutput(JSON.stringify({ status: 'success', message: 'Fee submitted successfully and confirmation email sent.' })).setMimeType(ContentService.MimeType.JSON); } // ✅ NEW: Function to send the confirmation email function sendFeeConfirmationEmail(data, newTotalPaidAmount, status, formattedDate) { const MY_EMAIL_ADDRESS = "khalid817014@gmail.com"; // Replace with your email address const studentName = data.name; const studentClass = data.class; const month = data.month; const paidAmount = data.paidAmount; const subject = `Fee Submission Confirmation for ${studentName}`; const body = ` Dear Admin, This is an automated confirmation for a recent fee submission. Student Name: ${studentName} Class: ${studentClass} Month: ${month} Amount Paid Now: ${paidAmount} Total Paid for this Month: ${newTotalPaidAmount} Status: ${status} Date: ${formattedDate} Thank you. `; MailApp.sendEmail(MY_EMAIL_ADDRESS, subject, body); } // --- Mark Student as Withdrawn Function --- function markWithdrawn(data) { const regSheet = SPREADSHEET.getSheetByName(REGISTRATION_SHEET_NAME); if (!regSheet) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Registration sheet not found.' })) .setMimeType(ContentService.MimeType.JSON); } const regData = regSheet.getDataRange().getValues(); for (let i = 1; i < regData.length; i++) { if (regData[i][0].toLowerCase() === data.name.toLowerCase()) { regSheet.getRange(i + 1, 5).setValue("Inactive"); regSheet.getRange(i + 1, 6).setValue(data.withdrawnDate); return ContentService.createTextOutput(JSON.stringify({ status: 'success' })) .setMimeType(ContentService.MimeType.JSON); } } return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Student not found.' })) .setMimeType(ContentService.MimeType.JSON); } // --- Get Student Statement Function --- function getStatement(name) { let records = []; const sheets = SPREADSHEET.getSheets(); sheets.forEach(sheet => { const sheetName = sheet.getName(); if (sheetName.match(/^\w+\s\d{4}$/)) { const data = sheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (data[i][0] && data[i][0].toLowerCase() === name.toLowerCase()) { records.push({ month: sheetName, name: data[i][0], class: data[i][1], date: Utilities.formatDate(new Date(data[i][2]), "GMT", "yyyy-MM-dd"), monthlyFee: data[i][3], paidAmount: data[i][4], status: data[i][5] }); } } } }); if (records.length > 0) { return ContentService.createTextOutput(JSON.stringify({ status: 'success', records: records })) .setMimeType(ContentService.MimeType.JSON); } else { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'No records found.' })) .setMimeType(ContentService.MimeType.JSON); } } // --- ✅ Updated: Get ALL data for dashboard, including unpaid students, for a specific month or all months --- function getAllData(selectedMonth) { const regSheet = SPREADSHEET.getSheetByName(REGISTRATION_SHEET_NAME); if (!regSheet) { return ContentService.createTextOutput(JSON.stringify([])) .setMimeType(ContentService.MimeType.JSON); } const regData = regSheet.getDataRange().getValues(); const allRegisteredStudents = regData.slice(1).map(row => ({ name: row[0], class: row[1], admissionDate: new Date(row[2]), monthlyFee: row[3], status: row[4], withdrawnDate: row[5] ? new Date(row[5]) : null })); let allRows = []; if (selectedMonth) { // Only get data for the selected month const targetMonth = new Date(selectedMonth); const targetMonthName = targetMonth.toLocaleString('default', { month: 'long', year: 'numeric' }); allRows = getMonthlyData(targetMonthName, allRegisteredStudents); } else { // Get data for all months const sheets = SPREADSHEET.getSheets(); sheets.forEach(sheet => { const sheetName = sheet.getName(); if (sheetName.match(/^\w+\s\d{4}$/)) { const monthRows = getMonthlyData(sheetName, allRegisteredStudents); allRows = allRows.concat(monthRows); } }); } const finalData = allRows.map(r => [r.month, r.name, r.class, r.date, r.monthlyFee, r.paidAmount, r.status]); return ContentService.createTextOutput(JSON.stringify(finalData)) .setMimeType(ContentService.MimeType.JSON); } // --- ✅ New Helper function for single month data fetching --- function getMonthlyData(targetMonthName, allRegisteredStudents) { const monthlySheet = SPREADSHEET.getSheetByName(targetMonthName); let rows = []; let paidStudents = new Set(); if (monthlySheet) { const monthlyData = monthlySheet.getDataRange().getValues(); for (let i = 1; i < monthlyData.length; i++) { if (monthlyData[i][0]) { rows.push({ month: targetMonthName, name: monthlyData[i][0], class: monthlyData[i][1], date: monthlyData[i][2] ? Utilities.formatDate(new Date(monthlyData[i][2]), "GMT", "yyyy-MM-dd") : "", monthlyFee: monthlyData[i][3] || 0, paidAmount: monthlyData[i][4] || 0, status: monthlyData[i][5] }); paidStudents.add(monthlyData[i][0].toLowerCase()); } } } const [m, y] = targetMonthName.split(" "); const monthIndex = new Date(`${m} 1, ${y}`).getMonth(); const year = parseInt(y); allRegisteredStudents.forEach(student => { const admitted = (student.admissionDate.getFullYear() < year) || (student.admissionDate.getFullYear() === year && student.admissionDate.getMonth() <= monthIndex); let withdrawnBefore = false; if (student.status.toLowerCase() === 'inactive' && student.withdrawnDate) { const lastDayPrevMonth = new Date(year, monthIndex, 0); if (student.withdrawnDate < lastDayPrevMonth) withdrawnBefore = true; } if (admitted && !withdrawnBefore && !paidStudents.has(student.name.toLowerCase())) { rows.push({ month: targetMonthName, name: student.name, class: student.class, date: "", monthlyFee: student.monthlyFee, paidAmount: 0, status: "Not Paid" }); } }); return rows; } // --- ✅ New: Get all months for dropdown --- function getAvailableMonths() { const sheets = SPREADSHEET.getSheets(); let months = new Set(); sheets.forEach(sheet => { const sheetName = sheet.getName(); if (sheetName.match(/^\w+\s\d{4}$/)) { months.add(sheetName); } }); const sortedMonths = Array.from(months).sort((a, b) => { const dateA = new Date(a); const dateB = new Date(b); return dateA - dateB; }); return ContentService.createTextOutput(JSON.stringify(sortedMonths)) .setMimeType(ContentService.MimeType.JSON); }
📋 Copy Code
Code successfully copied! 🎉