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);
    }

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);
    }

if (!studentDetails) {
        return ContentService.createTextOutput(JSON.stringify(null)).setMimeType(ContentService.MimeType.JSON);
    }

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);
    }

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);
}