import ExcelJS, { Worksheet } from "exceljs"; import CONSTANTS from "../config/constants"; import transactionDB from "../models/transaction.model"; import propertyDB from "../models/property.model"; import duesDB from "../models/dues.model"; import occupancyDB from "../models/occupancy.model"; import log from "../config/log"; import tenantDB from "../models/tenant.model"; import getDueName from "./getDueName"; import fs from "fs"; import path from "path"; import moment from "moment"; import expenseDB from "../models/expense.model"; import eqaroTenantsDB from "../models/eqaroTenants.model"; import bedDB from "../models/beds.model"; import extraChargeDB from "../models/extraCharges.model"; import ledgerDB from "../models/ledger.model"; import getKycStatusName from "./getKycStatus"; import roomDB from "../models/room.model"; import roomOptionDB from "../models/roomOption.model"; import moveOutDB from "../models/moveOut.model"; import getBloodGroupNames from "../utils/getBloodGroupNames"; import complaintDB from "../models/complaint.model"; import leadDB from "../models/lead.model"; import getLeadStatusDescription, { getSourceName, rentRangeNames } from "./getLeadConstantsDescription"; import activityLogsDB from "../models/activityLogs.model"; import getActivityDescription from "./getActivityDescription"; import getStaffRoleName from "./getStaffRoleName"; import documentDB from "../models/document.model"; import flatDB from "../models/flat.model"; import clientConfigDB from "../models/clientConfig.model"; import moveOutDuesDB from "../models/moveOutDues.model"; import propertyLeaseDB from "../models/propertyLease.model"; import axios from "axios"; import clientDB from "../models/client.model"; // @ts-ignore import pdf from "pdf-creator-node"; import tenantBankDB from "../models/tenantBank.model"; import requestDB from "../models/request.model"; export const collectionReport = async ( fileType: number, clientId: number, propId: any, s: string, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "Collection Report"; try { let transactions = []; let transAmount = 0; let isGstEnabled = 0; let reportForMultipleProp = false; if (Array.isArray(propId) && propId.length > 0) { reportForMultipleProp = true; if (s && s !== "undefined" && s !== "null" && s !== " ") { transactions = await transactionDB.getByTenantNameMobile({ clientId: clientId, searchVal: s, }); } else { transactions = await transactionDB.getByPropIdsandDateRangeForReport({ propId, startDate, endDate, }); } const property = await propertyDB.isGstEnabledForPropIds({ propId, }); isGstEnabled = property.isGstEnabled; } else { if (s && s !== "undefined" && s !== "null" && s !== " ") { transactions = await transactionDB.getByTenantNameMobile({ clientId: clientId, searchVal: s, }); } else { transactions = await transactionDB.getByClientIdandDateRangeForReport({ clientId, startDate, endDate, }); } isGstEnabled = await propertyDB.isGstEnabledForClient({ clientId, }); } if (Number(fileType) === 1) { const workbook = new ExcelJS.Workbook(); // const worksheet = workbook.addWorksheet( // "Collection Report", // { views: [ // { state: "frozen", xSplit: 1, ySplit: 1 } // ] // } // ); let gatewayCharges = 0; let getGwCharges = await clientConfigDB.getClientConfig({ clientId, provider: CONSTANTS.CLIENT_CONFIG_PROVIDER.EASEBUZZ, type: CONSTANTS.CLIENT_CONFIG_TYPE.PER_TRANS_CHARGE_EASEBUZZ }); if (getGwCharges) { gatewayCharges = Number(getGwCharges.value) || 0; } const worksheet = workbook.addWorksheet( "Collections", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Collection Date", key: "collectionDate", width: 20 }, { header: "Name", key: "name", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Room Type", key: "roomType", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Property", key: "property", width: 30 }, { header: "Payment Mode", key: "paymentMode", width: 20 }, { header: "Transaction Amount", key: "amount", width: 20 }, { header: "Discount", key: "discount", width: 20 }, // { header: "GST Amount", key: "gstCharges", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST Amount", key: "gstCharges", width: 20 }] : []), // { header: "Holded By Kipinn", key: "holdAmount", width: 20 }, ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway Charges", key: "transactionCharges", width: 20 }] : []), ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway GST", key: "gstChargesToGateway", width: 20 }] : []), { header: "EMI", key: "holdAmount", width: 20 }, { header: "Amount Settled", key: "transAmount", width: 20 }, { header: "Payment Gateway", key: "paymentGateway", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Payment For", key: "transactionFor", width: 20 }, { header: "Description", key: "description", width: 50, style: { alignment: { wrapText: true, vertical: "top" } } }, { header: "Credit Account No.", key: "paymentAccountNo", width: 20 }, { header: "Credit Account Holder Name", key: "paymentAccountName", width: 20 }, { header: "Transaction Id", key: "transactionId", width: 20 }, { header: "Settlement Status", key: "settlementStatus", width: 20 }, { header: "Settled On", key: "settledOn", width: 20 }, { header: "UTR No.", key: "utrNumber", width: 20 }, // { header: "GST No.", key: "gstNumber", width: 20 }, // { header: "Invoice No.", key: "invoiceNumber", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST No.", key: "gstNumber", width: 20 }] : []), ...(Number(isGstEnabled) === 1 ? [{ header: "Invoice No.", key: "invoiceNumber", width: 20 }] : []), { header: "Bank Refference No.", key: "bankReffNumber", width: 20 }, { header: "Recorded By", key: "recordedBy", width: 20 }, { header: "Credit Bank Name", key: "bankName", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); //RENT SUB SHEET const rentWorksheet = workbook.addWorksheet( "Rent Collections", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); rentWorksheet.columns = [ { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Collection Date", key: "collectionDate", width: 20 }, { header: "Name", key: "name", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Room Type", key: "roomType", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Property", key: "property", width: 30 }, { header: "Payment Mode", key: "paymentMode", width: 20 }, { header: "Transaction Amount", key: "amount", width: 20 }, { header: "Discount", key: "discount", width: 20 }, // { header: "GST Amount", key: "gstCharges", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST Amount", key: "gstCharges", width: 20 }] : []), // { header: "Holded By Kipinn", key: "holdAmount", width: 20 }, ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway Charges", key: "transactionCharges", width: 20 }] : []), ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway GST", key: "gstChargesToGateway", width: 20 }] : []), { header: "EMI", key: "holdAmount", width: 20 }, { header: "Amount Settled", key: "transAmount", width: 20 }, { header: "Payment Gateway", key: "paymentGateway", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Payment For", key: "transactionFor", width: 20 }, { header: "Description", key: "description", width: 50, style: { alignment: { wrapText: true, vertical: "top" } } }, { header: "Credit Account No.", key: "paymentAccountNo", width: 20 }, { header: "Credit Account Holder Name", key: "paymentAccountName", width: 20 }, { header: "Transaction Id", key: "transactionId", width: 20 }, { header: "Settlement Status", key: "settlementStatus", width: 20 }, { header: "Settled On", key: "settledOn", width: 20 }, { header: "UTR No.", key: "utrNumber", width: 20 }, // { header: "GST No.", key: "gstNumber", width: 20 }, // { header: "Invoice No.", key: "invoiceNumber", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST No.", key: "gstNumber", width: 20 }] : []), ...(Number(isGstEnabled) === 1 ? [{ header: "Invoice No.", key: "invoiceNumber", width: 20 }] : []), { header: "Bank Refference No.", key: "bankReffNumber", width: 20 }, { header: "Recorded By", key: "recordedBy", width: 20 }, { header: "Credit Bank Name", key: "bankName", width: 20 }, ]; rentWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); //Security Work Sheet const securityWorksheet = workbook.addWorksheet( "Security Collections", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); securityWorksheet.columns = [ { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Collection Date", key: "collectionDate", width: 20 }, { header: "Name", key: "name", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Room Type", key: "roomType", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Property", key: "property", width: 30 }, { header: "Payment Mode", key: "paymentMode", width: 20 }, { header: "Transaction Amount", key: "amount", width: 20 }, { header: "Discount", key: "discount", width: 20 }, // { header: "GST Amount", key: "gstCharges", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST Amount", key: "gstCharges", width: 20 }] : []), // { header: "Holded By Kipinn", key: "holdAmount", width: 20 }, ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway Charges", key: "transactionCharges", width: 20 }] : []), ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway GST", key: "gstChargesToGateway", width: 20 }] : []), { header: "EMI", key: "holdAmount", width: 20 }, { header: "Amount Settled", key: "transAmount", width: 20 }, { header: "Payment Gateway", key: "paymentGateway", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Payment For", key: "transactionFor", width: 20 }, { header: "Description", key: "description", width: 50, style: { alignment: { wrapText: true, vertical: "top" } } }, { header: "Credit Account No.", key: "paymentAccountNo", width: 20 }, { header: "Credit Account Holder Name", key: "paymentAccountName", width: 20 }, { header: "Transaction Id", key: "transactionId", width: 20 }, { header: "Settlement Status", key: "settlementStatus", width: 20 }, { header: "Settled On", key: "settledOn", width: 20 }, { header: "UTR No.", key: "utrNumber", width: 20 }, // { header: "GST No.", key: "gstNumber", width: 20 }, // { header: "Invoice No.", key: "invoiceNumber", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST No.", key: "gstNumber", width: 20 }] : []), ...(Number(isGstEnabled) === 1 ? [{ header: "Invoice No.", key: "invoiceNumber", width: 20 }] : []), { header: "Bank Refference No.", key: "bankReffNumber", width: 20 }, { header: "Recorded By", key: "recordedBy", width: 20 }, { header: "Credit Bank Name", key: "bankName", width: 20 }, ]; securityWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); // Maintenance Sub Sheet -- asked by gj ji - 2026-07-09 const maintenanceWorksheet = workbook.addWorksheet( "Maintenance Collections", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); maintenanceWorksheet.columns = [ { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Collection Date", key: "collectionDate", width: 20 }, { header: "Name", key: "name", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Room Type", key: "roomType", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Property", key: "property", width: 30 }, { header: "Payment Mode", key: "paymentMode", width: 20 }, { header: "Transaction Amount", key: "amount", width: 20 }, { header: "Discount", key: "discount", width: 20 }, // { header: "GST Amount", key: "gstCharges", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST Amount", key: "gstCharges", width: 20 }] : []), // { header: "Holded By Kipinn", key: "holdAmount", width: 20 }, ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway Charges", key: "transactionCharges", width: 20 }] : []), ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway GST", key: "gstChargesToGateway", width: 20 }] : []), { header: "EMI", key: "holdAmount", width: 20 }, { header: "Amount Settled", key: "transAmount", width: 20 }, { header: "Payment Gateway", key: "paymentGateway", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Payment For", key: "transactionFor", width: 20 }, { header: "Description", key: "description", width: 50, style: { alignment: { wrapText: true, vertical: "top" } } }, { header: "Credit Account No.", key: "paymentAccountNo", width: 20 }, { header: "Credit Account Holder Name", key: "paymentAccountName", width: 20 }, { header: "Transaction Id", key: "transactionId", width: 20 }, { header: "Settlement Status", key: "settlementStatus", width: 20 }, { header: "Settled On", key: "settledOn", width: 20 }, { header: "UTR No.", key: "utrNumber", width: 20 }, // { header: "GST No.", key: "gstNumber", width: 20 }, // { header: "Invoice No.", key: "invoiceNumber", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST No.", key: "gstNumber", width: 20 }] : []), ...(Number(isGstEnabled) === 1 ? [{ header: "Invoice No.", key: "invoiceNumber", width: 20 }] : []), { header: "Bank Refference No.", key: "bankReffNumber", width: 20 }, { header: "Recorded By", key: "recordedBy", width: 20 }, { header: "Credit Bank Name", key: "bankName", width: 20 }, ]; maintenanceWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (transactions.length > 0) { for (const txn of transactions) { transAmount = Number(txn.amount) - Number(txn.holdAmount) + Number(txn.gstCharges); let paymentMode = ""; if (txn.mode === CONSTANTS.TRANSACTION_MODES.OFFLINE) { paymentMode = "Cash"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.CARD) { paymentMode = "Card"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.UPI) { paymentMode = "UPI"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.NET_BANKING) { paymentMode = "Net Banking"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY) { paymentMode = "Sec Adjusted"; } const property = `${txn.propName}`; // const mobile = txn.tenantMobile.slice(0, -4) + "XXXX"; const mobile = txn.tenantMobile; let settlementStatus = "-"; if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.PENDING) { settlementStatus = "Pending"; } else if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.INITITATED) { settlementStatus = "Initiated"; } else if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.COMPLETED) { settlementStatus = "Completed"; } let paymentGateway = "None"; if (txn.gateway === CONSTANTS.PAYMENT_GATEWAY.PAYU && txn.recordedBy === "Kipinn App") { paymentGateway = "PayU"; } else if (txn.gateway === CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ && txn.recordedBy === "Kipinn App") { paymentGateway = "Easebuzz"; } else if (txn.gateway === CONSTANTS.PAYMENT_GATEWAY.CASHFREE && txn.recordedBy === "Kipinn App") { paymentGateway = "Cashfree"; } const bedCount = await bedDB.getCountsByRoomId({ id: txn.roomId, }); const totalBeds = bedCount.totalBeds; const room = await roomDB.getById({ id: txn.roomId, }); const propertyData = await propertyDB.getById({ id: room.propId }); let flatName = ""; if (propertyData.type === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: room.flatId }); flatName = name; } else { flatName = room.floor === "G" ? "Ground" : room.floor; } const roomOption = await roomOptionDB.getById({ id: room.roomOptionId, }); let transactionForName = getDueName(txn.transactionFor); let transactionCharges = 0; let gstChargesToGateway = 0; if (Number(gatewayCharges) > 1 && txn.gateway === CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ && txn.recordedBy === "Kipinn App") { transactionCharges = Number(gatewayCharges); gstChargesToGateway = Number(((gatewayCharges * 18) / 100).toFixed(2)); transAmount = transAmount - transactionCharges - gstChargesToGateway; } let rowData: any = { roomNum: txn.roomNum || "", floorOrFlat: flatName || "", name: txn.tenantName || "", sharingType: `${totalBeds} Sharing`, // roomType: roomOption?.name || "", // mobile: mobile.toString() || "", roomType: roomOption.name ? roomOption?.name : "", mobile: mobile ? mobile.toString() : "", collectionDate: moment(txn.collectionDate).format("DD/MM/YYYY") || "", dueDate: txn?.dueDate ? moment(txn?.dueDate).format("DD/MM/YYYY") : "", paymentMode: paymentMode || "", amount: txn.amount || 0, discount: txn.discount || 0, // gstCharges: txn.gstCharges || 0, holdAmount: txn.holdAmount || 0, transAmount: transAmount || 0, transactionFor: transactionForName || 0, paymentAccountNo: txn.paymentAccountNo || "", paymentAccountName: txn.paymentAccountName || "", property: property || "", description: txn.name || "", // transactionId: txn.gId.toString() || "", transactionId: txn.gId ? txn.gId.toString() : "", settlementStatus, settledOn: txn?.settledOn ? moment(txn?.settledOn).format("DD/MM/YYYY") : "-", utrNumber: txn.utrNo || "-", // gstNumber : txn.gstNo || "-", // invoiceNumber : txn.invoiceNo || "-", bankReffNumber: txn.bankRefNum || "-", recordedBy: txn.recordedBy || "", paymentGateway: paymentGateway || "", transactionCharges: transactionCharges, gstChargesToGateway: gstChargesToGateway, bankName: txn.bankName || "", }; if (Number(isGstEnabled) === 1) { rowData.gstCharges = txn.gstCharges || 0; rowData.gstNumber = txn.gstNo || "-"; rowData.invoiceNumber = txn.invoiceNo || "-"; } const row = worksheet.addRow(rowData); row.eachCell((cell: any) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (txn.transactionFor === CONSTANTS.DUES_TYPES.RENT) { const rentRow = rentWorksheet.addRow(rowData); rentRow.eachCell((cell: any) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } else if (txn.transactionFor === CONSTANTS.DUES_TYPES.SECURITY) { const securityRow = securityWorksheet.addRow(rowData); securityRow.eachCell((cell: any) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } else if (txn.transactionFor === CONSTANTS.DUES_TYPES.MAINTENANCE) { const maintenanceRow = maintenanceWorksheet.addRow(rowData); maintenanceRow.eachCell((cell: any) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `collections`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `collection_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else if (Number(fileType) === 2) { // PDF Report Generation can be implemented here if (transactions.length > 0) { let templatePath = `${process.env.UPLOAD_PATH}/documents/defaults/collection_report.html`; let { data: html } = await axios.get(templatePath); const folderName = `collections`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } if (!html) { log.info( `[${C}], [${F}], Client Id [${clientId}], Template found without content` ); return false; } html = html.toString(); let header = ""; if (!reportForMultipleProp) { const client = await clientDB.getById({ id: clientId }); if (!client) { log.info( `[${C}], [${F}], Client Id [${clientId}], No Client Found` ); return false } let headerName = client?.name; if (client?.businessName) { headerName = client?.businessName; } header = `
${headerName}
`; } else { log.info( `Check3 , Prop Id [${propId[0]}]` ); const property = await propertyDB.getById({ id: propId[0] }); log.info( `Check 4 , Prop Id [${propId[0]}]` ); header = `
${property?.name}
${property?.gId}
`; } html = html.replace(/{{mainHeader}}/g, header); let tableHeader = ` S.No Property Name Room No. Name Mobile Due Date Due Type Paid Amount Collection Date Transaction Id Recorded By Mode Description Balance `; html = html.replace(/{{tableHeader}}/g, tableHeader); let tableRows = ""; let totalCollection = 0; let totalDueBalance = 0; let sNo = 1; let pagebreakRow = 13; let breakCounter = 1; for (const txn of transactions) { transAmount = Number(txn.amount); totalCollection = totalCollection + transAmount; let paymentMode = ""; if (txn.mode === CONSTANTS.TRANSACTION_MODES.OFFLINE) { paymentMode = "Cash"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.CARD) { paymentMode = "Card"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.UPI) { paymentMode = "UPI"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.NET_BANKING) { paymentMode = "Net Banking"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY) { paymentMode = "Sec Adjusted"; } const property = `${txn.propName}`; // const mobile = txn.tenantMobile.slice(0, -4) + "XXXX"; const mobile = txn.tenantMobile; let settlementStatus = "-"; if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.PENDING) { settlementStatus = "Pending"; } else if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.INITITATED) { settlementStatus = "Initiated"; } else if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.COMPLETED) { settlementStatus = "Completed"; } let paymentGateway = "None"; if (txn.gateway === CONSTANTS.PAYMENT_GATEWAY.PAYU && txn.recordedBy === "Kipinn App") { paymentGateway = "PayU"; } else if (txn.gateway === CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ && txn.recordedBy === "Kipinn App") { paymentGateway = "Easebuzz"; } else if (txn.gateway === CONSTANTS.PAYMENT_GATEWAY.CASHFREE && txn.recordedBy === "Kipinn App") { paymentGateway = "Cashfree"; } // const bedCount = await bedDB.getCountsByRoomId({ // id: txn.roomId, // }); //const totalBeds = bedCount.totalBeds; const room = await roomDB.getById({ id: txn.roomId, }); const propertyData = await propertyDB.getById({ id: room.propId }); let flatName = ""; if (propertyData.type === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: room.flatId }); flatName = name; } else { flatName = room.floor === "G" ? "Ground" : room.floor; } const roomOption = await roomOptionDB.getById({ id: room.roomOptionId, }); let balance = 0; // log.info( // `[${C}], [${F}], Client Id [${clientId}], Ledger Id [${txn?.ledgerReferenceId}]` // ); let getDueByLedgerId = await duesDB.getByTenantIdAndLedgerReferenceId({ tenantId: txn?.tenantId, ledgerReferenceId: txn?.ledgerReferenceId }); if (getDueByLedgerId) { balance = getDueByLedgerId[0].balance; } totalDueBalance = Number(totalDueBalance) + Number(balance); let transactionForName = getDueName(txn.transactionFor); tableRows += ` ${sNo} ${propertyData?.name} ${flatName}, ${txn?.roomNum || ''} ${txn?.tenantName || ''} ${mobile ? mobile.toString() : ""} ${txn?.dueDate ? moment(txn?.dueDate).format("DD/MM/YYYY") : ""} ${transactionForName} ₹${transAmount} ${moment(txn.collectionDate).format("DD/MM/YYYY") || ""} ${txn.gId ? txn.gId.toString() : ""} ${txn.recordedBy || ""} ${paymentMode} ${txn?.name || ""} ₹${balance} `; if (sNo % pagebreakRow === 0 && sNo != 15) { let margin = 30; if (pagebreakRow === 15) margin = 80; tableRows += ` `; pagebreakRow = 15; } sNo = Number(sNo) + 1; } let summary = `
Date Range ${moment(startDate).format("DD/MM/YYYY") || ""} to ${moment(endDate).format("DD/MM/YYYY") || ""}
Total Collection ₹${totalCollection}
`; html = html.replace(/{{summary}}/g, summary); html = html.replace(/{{collections}}/g, tableRows); html = html.replace(/{{totalCollections}}/g, totalCollection); html = html.replace(/{{totalBalance}}/g, totalDueBalance); html = html.replace(/{{generatedAt}}/g, `Generated at ${moment().format("DD/MM/YYYY HH:mm:ss")}`); html = html.replace(/\n/g, ""); const options = { format: "A4", orientation: "landscape", border: "10mm", childProcessOptions: { env: { OPENSSL_CONF: "/dev/null", }, }, }; const fileName = `collection_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.pdf`; const filePath = `${folderPath}/${fileName}`; var document = { html: html, path: `${folderPath}/${fileName}`, data: {}, type: "", }; await pdf.create(document, options); const url = `${urlBase}/${fileName}`; log.info( `[${C}], [${F}], Client Id [${clientId}], URL [${url}]` ); return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const tenantReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "Tenant Report"; try { let tenants = []; if (Array.isArray(propId) && propId.length > 0) { tenants = await occupancyDB.getByPropIdandDateRange({ propId, startDate, endDate, }); } else { tenants = await occupancyDB.getByClientIdandDateRange({ clientId, startDate, endDate, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Tenant Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Name", key: "name", width: 20 }, { header: "Room No.", key: "roomNum", width: 20 }, { header: "Room Name", key: "roomName", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Alternate Mobile", key: "alternateMobile", width: 20 }, { header: "Email", key: "email", width: 20 }, { header: "Property", key: "property", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Occupied Bed", key: "occupiedBeds", width: 20 }, { header: "Rent", key: "rent", width: 20 }, { header: "Security", key: "security", width: 20 }, { header: "Lock-In-Period", key: "lockInPeriod", width: 20 }, { header: "Notice Period", key: "noticePeriod", width: 20 }, { header: "Move-In-Date", key: "moveInDate", width: 20 }, { header: "Move-Out-Date", key: "moveOutDate", width: 20 }, { header: "Rental Cycle", key: "rentalCycle", width: 20 }, { header: "Total Dues", key: "totalDues", width: 30 }, { header: "Total Collection", key: "totalCollection", width: 30 }, { header: "Payment Via Kipinn", key: "kipinnCollection", width: 30 }, { header: "Offline Payment", key: "offlineCollection", width: 30 }, { header: "Room Option", key: "roomOptionName", width: 30 }, { header: "Occupancy Status", key: "occupancyStatus", width: 30 }, { header: "Equaro Status", key: "eqaroStatus", width: 30 }, { header: "Last Rent Paid Status", key: "paidStatus", width: 30 }, { header: "Kyc Status", key: "kycStatus", width: 20 }, { header: "Gender", key: "gender", width: 20 }, { header: "Blood Group", key: "bloodGroup", width: 20 }, { header: "DOB", key: "dob", width: 20 }, { header: "Aadhaar Number", key: "aadhaarNumber", width: 20 }, { header: "Father Name", key: "fatherName", width: 30 }, { header: "Father Mobile", key: "fatherMobile", width: 30 }, { header: "Father Occupation", key: "fatherOccupation", width: 30 }, { header: "Father Annual Income", key: "fatherAnnualIncome", width: 30 }, { header: "Mother Name", key: "motherName", width: 30 }, { header: "Mother Mobile", key: "motherMobile", width: 30 }, { header: "Local Guardian Name", key: "localGuardianName", width: 30 }, { header: "Local Guardian Mobile", key: "localGuardianMobile", width: 30 }, { header: "Guardian Relation", key: "localGuardianRelation", width: 30 }, { header: "Occupation", key: "occupation", width: 30 }, { header: "Instituition/Company Name", key: "institutionName", width: 30 }, { header: "Instituition/Work Email", key: "institutionEmail", width: 30 }, { header: "Course/Designation", key: "title", width: 30 }, { header: "Course End Month", key: "courseEndMonth", width: 30 }, { header: "Course End Year", key: "courseEndYear", width: 30 }, { header: "Address", key: "address", width: 30 }, { header: "Notes", key: "notes", width: 30 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (tenants.length > 0) { for (const tenant of tenants) { const property = await propertyDB.getById({ id: tenant.propId }); //const propertyName = `${tenant?.roomNum}, ${property?.name}`; const propertyName = `${property?.name}`; let flatName = ""; if (property.type === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: tenant.flatId }); flatName = name; } else { flatName = tenant.floor === "G" ? "Ground" : tenant.floor; } const totalDues = await duesDB.getTotalDuesByTenantId({ tenantId: tenant.id, propId: tenant.propId, }); //const mobile = tenant.mobile.slice(0, -4) + "XXXX"; const mobile = tenant.mobile; const eqaroRecord = await eqaroTenantsDB.getByTenantId({ tenantId: tenant.id }); //let eqaroStatus = tenant.rentalBond === 2 ? eqaroRecord?.status === CONSTANTS.EQARO_TENANT_STATUS.BOND_ISSUED ? "Eqaro Added" : "Eqaro Pending" : "N/A"; let bondStatus = "Not Allowed"; if (tenant?.rentalBond == 2) { if (eqaroRecord?.status === CONSTANTS.EQARO_TENANT_STATUS.BOND_ISSUED) { bondStatus = "Availed"; } else { bondStatus = "Not Availed"; let document = await documentDB.getIDByType({ tenantId: tenant.id, clientId: clientId, type: CONSTANTS.DOCUMENT_TYPES.EQARO_BOND, moveOut: 0 }); if (document) { bondStatus = "Availed"; } } } let eqaroStatus = bondStatus; const occupancyStatus = tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.RESERVED ? "Reserved" : tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT ? `Going to vacate on ${tenant.moveOutDate}` : tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.OCCUPIED ? "Occupied" : "Not Occupied"; const genderVal = tenant.gender === CONSTANTS.GENDER.MALE ? "Male" : tenant.gender === CONSTANTS.GENDER.FEMALE ? "Female" : "Other"; const kycStatus = getKycStatusName(tenant.kycStatus); const lastRentPaid = await ledgerDB.getPaidLastRent({ tenantId: tenant.id, clientId: clientId, }); const tenantCollection = await transactionDB.tenantCollectionStats({ tenantId: tenant.id, clientId: clientId, }); let paidStatus = ``; if (lastRentPaid?.rentStartDate || lastRentPaid?.dueDate) { const lastRentCollection = await transactionDB.getLastPaidByClientIdAndTenantId({ tenantId: tenant.id, clientId: clientId }); if (lastRentCollection) { paidStatus = `Rent Paid for : ${moment(lastRentPaid?.rentStartDate ? lastRentPaid?.rentStartDate : lastRentPaid?.dueDate).format("MMM YYYY")}\nCollected on: ${moment(lastRentCollection?.collectionDate).format("DD MMM YYYY")}` } else { paidStatus = `Rent Paid for : ${moment(lastRentPaid?.rentStartDate ? lastRentPaid?.rentStartDate : lastRentPaid?.dueDate).format("MMM YYYY")}`; } } else { paidStatus = `-`; } let relation = ""; if (Number(tenant?.localGuardianRelation) === 1) relation = "Uncle"; else if (Number(tenant?.localGuardianRelation) === 2) relation = "Aunt"; else if (Number(tenant?.localGuardianRelation) === 3) relation = "Brother"; else if (Number(tenant?.localGuardianRelation) === 4) relation = "Sister"; else relation = "Friend"; if (tenant?.localGuardianName == "" || tenant?.localGuardianName == null) relation = ""; let tenantOccupation = ""; if (Number(tenant?.occupation) === 1) { tenantOccupation = "Student"; } else if (Number(tenant?.occupation) === 2) { tenantOccupation = "Professional"; } else { tenantOccupation = tenant?.occupation; } const monthData = [ 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December' ]; let monthName : any = null; if(tenant?.courseEndMonth) { monthName = monthData[Number(tenant?.courseEndMonth) - 1]; } let row = worksheet.addRow({ roomNum: tenant.roomNum || "-", roomName: tenant?.roomName || "-", name: tenant.name || "", mobile: mobile.toString() || "", gender: genderVal || "", dob: tenant?.dob ? moment(tenant?.dob).format("DD/MM/YYYY") : "", aadhaarNumber: tenant.aadhaarNumber || "", property: propertyName || "", occupiedBeds: tenant?.occupiedBeds || "", kycStatus: kycStatus || "", rent: tenant.rent || 0, security: tenant.security || 0, lockInPeriod: tenant.lockInPeriod || "", noticePeriod: tenant.noticePeriod || "", moveInDate: tenant?.moveInDate ? moment(tenant?.moveInDate).format("DD/MM/YYYY") : "", moveOutDate: tenant?.moveOutDate ? moment(tenant?.moveOutDate).format("DD/MM/YYYY") : "", rentalCycle: tenant.rentalCycle || "", totalDues: totalDues?.totalDues || 0, totalCollection: tenantCollection.totalCollection || 0, kipinnCollection: tenantCollection.kipinnCollection || 0, offlineCollection: tenantCollection.offlineCollection || 0, occupancyStatus: occupancyStatus, //paidStatus: lastRentPaid?.rentStartDate ? `${moment(lastRentPaid?.rentStartDate).format("MMM")} Paid` : "-", paidStatus, fatherName: tenant.fatherName || "", motherName: tenant.motherName || "", institutionName: tenant.institutionName || "", institutionEmail: tenant.institutionEmail || "", title: tenant.title || "", roomOptionName: tenant.roomOptionName || "", address: tenant.address || "", occupation: tenantOccupation, fatherMobile: tenant.fatherMobile, fatherOccupation: tenant.fatherOccupation, fatherAnnualIncome: tenant.fatherAnnualIncome, motherMobile: tenant.motherMobile, localGuardianName: tenant.localGuardianName, localGuardianMobile: tenant.localGuardianMobile, localGuardianRelation: relation, bloodGroup: getBloodGroupNames(tenant?.bloodGroup), floorOrFlat: flatName || "", eqaroStatus: eqaroStatus || "", notes: tenant?.notes || "", email: tenant.email, alternateMobile: tenant?.alternateMobile || "-", courseEndMonth: monthName || "-", courseEndYear: tenant?.courseEndYear || "-", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; }); row.height = 30; } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `tenant`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `tenant_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const tenantReportForSivani = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "TenantReportForSivani"; try { let tenants = []; if (Array.isArray(propId) && propId.length > 0) { tenants = await occupancyDB.getByPropIdandDateRangeWithoutMovedOut({ propId, startDate, endDate, }); } else { tenants = await occupancyDB.getByClientIdandDateRangeWithoutMovedOut({ clientId, startDate, endDate, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); //const worksheet = workbook.addWorksheet("Tenant Report"); const worksheet = workbook.addWorksheet( "Tenant Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Name", key: "name", width: 20 }, { header: "Room No.", key: "roomNum", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Room Type", key: "roomType", width: 20 }, { header: "Cott", key: "cott", width: 20 }, { header: "Property", key: "property", width: 20 }, { header: "Rent", key: "rent", width: 20 }, { header: "Utility Charges", key: "utilityCharge", width: 20 }, { header: "Service Charges", key: "serviceCharge", width: 20 }, { header: "Overall Rent", key: "overallRent", width: 20 }, { header: "Advance Amount", key: "security", width: 20 }, { header: "Maintenance", key: "maintenance", width: 20 }, { header: "Join Date", key: "moveInDate", width: 20 }, { header: "Rental Cycle", key: "rentalCycle", width: 20 }, { header: "Eqaro Status", key: "eqaroStatus", width: 30 }, { header: "Advance Status", key: "securityStatus", width: 30 }, { header: "Total Dues", key: "totalDues", width: 30 }, { header: "Total Collection", key: "totalCollection", width: 30 }, { header: "Last Rent Paid Status", key: "paidStatus", width: 30 }, { header: "Pg Attendance", key: "occupancyStatus", width: 30 }, { header: "Notes", key: "notes", width: 30 }, { header: "Kyc Status", key: "kycStatus", width: 20 }, { header: "Rent Agreement", key: "rentalAgreement", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (tenants.length > 0) { for (const tenant of tenants) { const property = await propertyDB.getById({ id: tenant.propId }); //const propertyName = `${tenant?.roomNum}, ${property?.name}`; const propertyName = `${property?.name}`; const totalDues = await duesDB.getTotalDuesByTenantId({ tenantId: tenant.id, propId: tenant.propId, }); const totalCollection = await transactionDB.getTotalByTenantId({ tenantId: tenant.id, clientId: clientId, }); //const mobile = tenant.mobile.slice(0, -4) + "XXXX"; const mobile = tenant.mobile; const eqaroRecord = await eqaroTenantsDB.getByTenantId({ tenantId: tenant.id }); const kycStatus = getKycStatusName(tenant.kycStatus); //let eqaroStatus = tenant.rentalBond === 2 ? eqaroRecord?.status === CONSTANTS.EQARO_TENANT_STATUS.BOND_ISSUED ? "Eqaro Added" : "Eqaro Pending" : "N/A"; let bondStatus = "Not Allowed"; if (tenant?.rentalBond == 2) { if (eqaroRecord?.status === CONSTANTS.EQARO_TENANT_STATUS.BOND_ISSUED) { bondStatus = "Availed"; } else { bondStatus = "Not Availed"; let document = await documentDB.getIDByType({ tenantId: tenant.id, clientId: clientId, type: CONSTANTS.DOCUMENT_TYPES.EQARO_BOND, moveOut: 0 }); if (document) { bondStatus = "Availed"; } } } let eqaroStatus = bondStatus; const securityDue = await duesDB.getByTypeAndTenantId({ tenantId: tenant.id, type: CONSTANTS.DUES_TYPES.SECURITY, }); const securityStatus = Number(tenant.security) === 0 ? "N/A" : securityDue ? "Advance Pending" : "Advance Paid"; const occupancyStatus = tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.RESERVED ? "Booking" : tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT ? `Going to vacate on ${tenant.moveOutDate}` : "Y"; const bedCount = await bedDB.getCountsByRoomId({ id: tenant.roomId, }); const totalBeds = bedCount.totalBeds; const utilityCharge = (3000 / totalBeds).toFixed(2) || 0; const serviceCharge = 99; const rent = Number(tenant.rent) - Number(utilityCharge) - Number(serviceCharge); const maintenance = await extraChargeDB.getByPropAndTypeX({ clientId: clientId, propId: tenant.propId, type: CONSTANTS.EXTRA_CHARGE_TYPES.REGISTRATION, }); const lastRentPaid = await ledgerDB.getPaidLastRent({ tenantId: tenant.id, clientId: clientId, }); let row = worksheet.addRow({ roomNum: tenant.roomNum || "-", name: tenant.name || "", mobile: mobile.toString() || "", sharingType: `${totalBeds} Sharing`, roomType: tenant.roomOptionName || "", cott: tenant.roomOptionName?.toLowerCase() === 'executive' ? "Iron" : "Wooden", property: propertyName || "", rent: rent || 0, utilityCharge: utilityCharge, serviceCharge: serviceCharge, overallRent: tenant.rent || 0, security: tenant.security || 0, maintenance: maintenance.amount || 0, moveInDate: tenant.moveInDate || "", rentalCycle: tenant.rentalCycle || "", eqaroStatus: eqaroStatus, securityStatus: securityStatus, totalDues: totalDues?.totalDues || 0, totalCollection: totalCollection || 0, paidStatus: lastRentPaid?.rentStartDate ? `${moment(lastRentPaid?.rentStartDate).format("MMM")} Paid` : "-", occupancyStatus: occupancyStatus, notes: tenant?.notes || "", kycStatus: kycStatus || "", rentalAgreement: tenant?.isRentAgreementSigned && Number(tenant?.isRentAgreementSigned) === 1 ? "Yes" : "No", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `tenant`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `tenant_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const dueReport = async ( fileType: number, clientId: number, propId: any, s: string, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "Due Report"; try { let dues = []; if (s && s !== "undefined" && s !== "null" && s !== " ") { dues = await duesDB.getByTenantNameMobile({ propId, searchVal: s, }); } else if (Array.isArray(propId) && propId.length > 0) { dues = await duesDB.getByPropIdsandDateRange({ propId, startDate, endDate, }); } else { dues = await duesDB.getByClientIdandDateRange({ clientId, startDate, endDate, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Due Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Name", key: "name", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Room Type", key: "roomType", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Property", key: "property", width: 20 }, { header: "Amount", key: "amount", width: 20 }, { header: "Due Type", key: "dueType", width: 20 }, { header: "Description", key: "description", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (dues.length > 0) { for (const due of dues) { const property = await propertyDB.getById({ id: due.propId }); const propertyName = `${property?.name}`; const tenant = await tenantDB.getById({ id: due.tenantId, }); let dueName = getDueName(due.type); if (due.type === CONSTANTS.DUES_TYPES.OTHER) { dueName = due.title; } // const mobile = tenant.mobile.slice(0, -4) + "XXXX"; const mobile = tenant.mobile; const bedCount = await bedDB.getCountsByRoomId({ id: due.roomId, }); const totalBeds = bedCount.totalBeds; const room = await roomDB.getById({ id: due.roomId, }); let flatName = ""; if (property.type === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: room.flatId }); flatName = name; } else { flatName = room.floor === "G" ? "Ground" : room.floor; } const roomOption = await roomOptionDB.getById({ id: room.roomOptionId, }); let row = worksheet.addRow({ name: tenant.name || "", floorOrFlat: flatName || "", roomNum: due.roomNum || "", roomType: roomOption?.name || "", sharingType: `${totalBeds} Sharing`, mobile: mobile.toString() || "", property: propertyName || "", amount: due.balance || 0, dueType: dueName || 0, dueDate: due?.dueDate ? moment(due?.dueDate).format("DD/MM/YYYY") : "", description: due.description || "", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `dues`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `due_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const expenseReport = async ( fileType: number, clientId: number, propId: any, s: string, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "Expense Report"; try { let expenses = []; let isFlatExist = false; if (Array.isArray(propId) && propId.length > 0) { expenses = await expenseDB.getByPropIdandDateRangeDueDate({ propId, startDate, endDate, sortBy: Number(clientId) === 329 ? "PD" : null, }); const flats = await flatDB.getByPropIds({ propIds: propId, }); if (flats && flats.length > 0) { isFlatExist = true; } } else { expenses = await expenseDB.getByClientIdandDateRangeForReport({ clientId, startDate, endDate, sortBy: (Number(clientId) === 329 || Number(clientId) === 93) ? "PD" : null, }); const flats = await flatDB.getByClientId({ clientId, }); if (flats && flats.length > 0) { isFlatExist = true; } } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); //Main Report const worksheet = workbook.addWorksheet( "Expense Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); if (329 === clientId) { worksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Expense Amount", key: "amount", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } else { worksheet.columns = [ { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Property", key: "property", width: 20 }, ...(isFlatExist ? [{ header: "Flat", key: "flatName", width: 20 }] : []), //Flat will be added if flat is there { header: "Amount", key: "amount", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); //Staff Expense Report const staffExpenseWorksheet = workbook.addWorksheet( "Staff Expense Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); if (329 === clientId) { staffExpenseWorksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Expense Amount", key: "amount", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } else { staffExpenseWorksheet.columns = [ { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Property", key: "property", width: 20 }, ...(isFlatExist ? [{ header: "Flat", key: "flatName", width: 20 }] : []), //Flat will be added if flat is there { header: "Amount", key: "amount", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } staffExpenseWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); //Vendor Expense Report const vendorExpenseWorksheet = workbook.addWorksheet( "Vendor Expense Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); if (329 === clientId) { vendorExpenseWorksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Expense Amount", key: "amount", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } else { vendorExpenseWorksheet.columns = [ { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Property", key: "property", width: 20 }, ...(isFlatExist ? [{ header: "Flat", key: "flatName", width: 20 }] : []), //Flat will be added if flat is there { header: "Amount", key: "amount", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } vendorExpenseWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); //Landlord Expense Report const landlordExpenseWorksheet = workbook.addWorksheet( "Landlord Expense Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); if (329 === clientId) { landlordExpenseWorksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Expense Amount", key: "amount", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } else { landlordExpenseWorksheet.columns = [ { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Property", key: "property", width: 20 }, ...(isFlatExist ? [{ header: "Flat", key: "flatName", width: 20 }] : []), //Flat will be added if flat is there { header: "Amount", key: "amount", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Attached Document", key: "supportDoc", width: 20 }, { header: "Expense Title", key: "expenseTitle", width: 20 }, ]; } landlordExpenseWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (expenses.length > 0) { for (const expense of expenses) { const property = await propertyDB.getById({ id: expense.propId }); let propertyName = `${property?.name}`; if (!property) { propertyName = "N/A"; } let flatName = "N/A"; if (expense.flatId) { const flat = await flatDB.getById({ id: expense.flatId }); if (flat) { flatName = `${flat?.name}`; } } const paymentMethod = expense.paymentMethod === CONSTANTS.TRANSACTION_MODES.OFFLINE ? "Cash" : expense.paymentMethod === CONSTANTS.TRANSACTION_MODES.CARD ? "Card" : expense.paymentMethod === CONSTANTS.TRANSACTION_MODES.UPI ? "UPI" : "Net Banking"; const repetitionType = expense.repetitionType === 1 ? "One Time" : "Monthly"; const row = worksheet.addRow({ paidByName: expense?.paidByName || "", paidToName: expense?.paidToName || "", property: propertyName || "", flatName: flatName, amount: expense?.amount || 0, paymentMethod: paymentMethod, expenseCategory: expense.expenseCategoryName || "", expenseType: expense.expenseTypeName || "", expenseNature: expense.expenseNature && Number(expense.expenseNature) === CONSTANTS.EXPENSE_NATURE.OPERATING ? "Operating Expense" : "Asset Investment", repetitionType: repetitionType, dueDate: expense?.dueDate ? moment(expense?.dueDate).format("DD/MM/YYYY") : "", paidDate: expense?.paidDate ? moment(expense?.paidDate).format("DD/MM/YYYY") : "", description: expense.description || "", supportDoc: expense?.supportDoc !== null ? "Link" : null, expenseTitle: expense?.title || "-", }); if (expense?.supportDoc && expense?.supportDoc !== null && expense?.supportDoc !== "") { const lastCell = row.getCell("supportDoc"); lastCell.value = { text: "Link", hyperlink: expense?.supportDoc, }; lastCell.font = { color: { argb: "FF0000FF" }, underline: true }; } row.eachCell((cell) => { cell.alignment = { vertical: "middle", horizontal: "center", wrapText: true }; }); row.height = 30; if (expense.paidToUserType === CONSTANTS.USER_TYPE.STAFF) { const row = staffExpenseWorksheet.addRow({ paidByName: expense?.paidByName || "", paidToName: expense?.paidToName || "", property: propertyName || "", flatName: flatName, amount: expense?.amount || 0, paymentMethod: paymentMethod, expenseCategory: expense.expenseCategoryName || "", expenseType: expense.expenseTypeName || "", expenseNature: expense.expenseNature && Number(expense.expenseNature) === CONSTANTS.EXPENSE_NATURE.OPERATING ? "Operating Expense" : "Asset Investment", repetitionType: repetitionType, dueDate: expense?.dueDate ? moment(expense?.dueDate).format("DD/MM/YYYY") : "", paidDate: expense?.paidDate ? moment(expense?.paidDate).format("DD/MM/YYYY") : "", description: expense.description || "", supportDoc: expense?.supportDoc !== null ? "Link" : null, expenseTitle: expense?.title || "-", }); if (expense?.supportDoc && expense?.supportDoc !== null && expense?.supportDoc !== "") { const lastCell = row.getCell("supportDoc"); lastCell.value = { text: "Link", hyperlink: expense?.supportDoc, }; lastCell.font = { color: { argb: "FF0000FF" }, underline: true }; } row.eachCell((cell) => { cell.alignment = { vertical: "middle", horizontal: "center", wrapText: true }; }); row.height = 30; } else if (expense.paidToUserType === CONSTANTS.USER_TYPE.VENDOR) { const row = vendorExpenseWorksheet.addRow({ paidByName: expense?.paidByName || "", paidToName: expense?.paidToName || "", property: propertyName || "", flatName: flatName, amount: expense?.amount || 0, paymentMethod: paymentMethod, expenseCategory: expense.expenseCategoryName || "", expenseType: expense.expenseTypeName || "", expenseNature: expense.expenseNature && Number(expense.expenseNature) === CONSTANTS.EXPENSE_NATURE.OPERATING ? "Operating Expense" : "Asset Investment", repetitionType: repetitionType, dueDate: expense?.dueDate ? moment(expense?.dueDate).format("DD/MM/YYYY") : "", paidDate: expense?.paidDate ? moment(expense?.paidDate).format("DD/MM/YYYY") : "", description: expense.description || "", supportDoc: expense?.supportDoc !== null ? "Link" : null, expenseTitle: expense?.title || "-", }); if (expense?.supportDoc && expense?.supportDoc !== null && expense?.supportDoc !== "") { const lastCell = row.getCell("supportDoc"); lastCell.value = { text: "Link", hyperlink: expense?.supportDoc, }; lastCell.font = { color: { argb: "FF0000FF" }, underline: true }; } row.eachCell((cell) => { cell.alignment = { vertical: "middle", horizontal: "center", wrapText: true }; }); row.height = 30; } else if (expense.paidToUserType === CONSTANTS.USER_TYPE.LANDLORD) { const row = landlordExpenseWorksheet.addRow({ paidByName: expense?.paidByName || "", paidToName: expense?.paidToName || "", property: propertyName || "", flatName: flatName, amount: expense?.amount || 0, paymentMethod: paymentMethod, expenseCategory: expense.expenseCategoryName || "", expenseType: expense.expenseTypeName || "", expenseNature: expense.expenseNature && Number(expense.expenseNature) === CONSTANTS.EXPENSE_NATURE.OPERATING ? "Operating Expense" : "Asset Investment", repetitionType: repetitionType, dueDate: expense?.dueDate ? moment(expense?.dueDate).format("DD/MM/YYYY") : "", paidDate: expense?.paidDate ? moment(expense?.paidDate).format("DD/MM/YYYY") : "", description: expense.description || "", supportDoc: expense?.supportDoc !== null ? "Link" : null, expenseTitle: expense?.title || "-", }); if (expense?.supportDoc && expense?.supportDoc !== null && expense?.supportDoc !== "") { const lastCell = row.getCell("supportDoc"); lastCell.value = { text: "Link", hyperlink: expense?.supportDoc, }; lastCell.font = { color: { argb: "FF0000FF" }, underline: true }; } row.eachCell((cell) => { cell.alignment = { vertical: "middle", horizontal: "center", wrapText: true }; }); row.height = 30; } } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `expenses`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `expense_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const occupancyReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "Occupancy Report"; try { let occupancyData = []; if (Array.isArray(propId) && propId.length > 0) { occupancyData = await occupancyDB.getOccupancyReportDataByPropId({ propId }); } else { occupancyData = await occupancyDB.getOccupancyReportDataByClientId({ clientId }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Occupancy Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floor", width: 20 }, { header: "Property", key: "property", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Tenant Staying", key: "tenantStaying", width: 20 }, { header: "Occupied/Total Beds", key: "occupiedAndTotalBeds", width: 20 }, { header: "Vacancy Status", key: "vacancyStatus", width: 20 }, { header: "Reserved", key: "tenantReserved", width: 20 }, { header: "Fixed Bed Rent Per Month", key: "rentPerMonth", width: 30 }, { header: "Total Room Rent ", key: "totalRent", width: 30 }, { header: "Fixed Bed Security", key: "security", width: 30 }, { header: "Total Room Security", key: "totalSecurity", width: 30 }, { header: "Total Dues", key: "totalDues", width: 30 }, { header: "Lifetime Collection", key: "totalCollection", width: 30 }, { header: "Moving Out", key: "tenantMovingOut", width: 30 } ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (occupancyData.length > 0) { for (const occupancy of occupancyData) { let rooomStatus = ""; if (occupancy.roomStatus === CONSTANTS.ROOM_STATUS.INACTIVE) { rooomStatus = "De-activated"; } else if (occupancy.roomStatus === CONSTANTS.ROOM_STATUS.OCCUPIED) { rooomStatus = "Fully Occupied"; } else if (occupancy.roomStatus === CONSTANTS.ROOM_STATUS.VACANT) { rooomStatus = "Vacant"; } else if (occupancy.roomStatus === CONSTANTS.ROOM_STATUS.SEMI_OCCUPIED) { rooomStatus = "Semi Occupied"; } let occupiedbedS = Number(occupancy.noOfBeds) - Number(occupancy.noOfVacantBeds); if (occupancy.noOfBeds === 0 || occupancy.roomStatus === CONSTANTS.ROOM_STATUS.INACTIVE) { continue; } let row = worksheet.addRow({ roomNum: occupancy.roomNum || "-", sharingType: `${occupancy.noOfBeds} Sharing`, floor: occupancy.floor || "", property: occupancy.propertyName || "", tenantStaying: occupancy?.noOfTenants || 0, occupiedAndTotalBeds: `${occupiedbedS}/${occupancy.noOfBeds}`, vacancyStatus: rooomStatus, tenantReserved: occupancy?.noOfReservedTenants || 0, rentPerMonth: occupancy?.rentPerBed || 0, totalRent: occupancy?.totalRent || 0, security: occupancy.security || 0, totalSecurity: occupancy?.totalSecurity || 0, totalDues: occupancy?.totalDues || 0, totalCollection: occupancy?.totalCollection || 0, tenantMovingOut: occupancy?.noOfMovingOutTenants, }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `occupancies`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `occupancy_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const bedOccupancyReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "Bed Occupancy Report"; try { let occupancyData = []; if (Array.isArray(propId) && propId.length > 0) { occupancyData = await occupancyDB.getBedOccupancyReportDataByPropId({ propId }); } else { occupancyData = await occupancyDB.getBedOccupancyReportDataByClientId({ clientId }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Bed Occupancy Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floor", width: 20 }, { header: "Property", key: "property", width: 20 }, { header: "Bed Status", key: "bedStatus", width: 20 }, { header: "Tenant Name", key: "tenantName", width: 20 }, { header: "Tenant Mobile", key: "tenantMobile", width: 20 }, { header: "Security", key: "security", width: 30 }, { header: "Fixed Bed Rent Per Month", key: "rentPerMonth", width: 30 }, //{ header: "Tenant Security", key: "tenantSecurity", width: 30}, { header: "Tenant Rent", key: "tenantRent", width: 30 }, { header: "Pending Dues", key: "totalDues", width: 30 }, { header: "Move-In Date", key: "moveInDate", width: 20 }, { header: "Getting Vacant On", key: "moveOutDate", width: 20 }, { header: "Vacant For", key: "vacantFor", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (occupancyData.length > 0) { for (const occupancy of occupancyData) { let bedStatus = ""; if (occupancy.bedStatus === CONSTANTS.BED_STATUS.VACANT) { bedStatus = "Vacant"; } else if (occupancy.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.OCCUPIED) { bedStatus = "Occupied"; } else if (occupancy.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT) { bedStatus = "Moving Out"; } else if (occupancy.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.RESERVED) { bedStatus = "Reserved"; } let vacantFor = "N/A"; if (occupancy.bedStatus === CONSTANTS.BED_STATUS.VACANT) { let lastMoveOutDate = await moveOutDB.getLastMoveOutDateByBedId({ bedId: occupancy.bedId, }); // let lastMoveOutDate = vacantSince.moveOutDate; let dayDiff = moment().diff(moment(lastMoveOutDate.moveOutDate), "days") + 1; vacantFor = String(dayDiff) + " days"; } let row = worksheet.addRow({ roomNum: occupancy?.roomNum || "-", floor: occupancy?.floor || "", property: occupancy?.propertyName || "", bedStatus: bedStatus || "", moveOutDate: occupancy?.moveOutDate ? moment(occupancy.moveOutDate).format("DD/MM/YYYY") : "N/A", tenantName: occupancy.tenantName || "", tenantMobile: occupancy.tenantMobile || "", rentPerMonth: occupancy?.rentPerBed || 0, //tenantSecurity: occupancy?.tenantSecurity || 0, tenantRent: occupancy?.tenantRent || 0, security: occupancy?.tenantSecurity || 0, totalDues: occupancy?.totalDues || 0, moveInDate: occupancy?.moveInDate ? moment(occupancy.moveInDate).format("DD/MM/YYYY") : "N/A", vacantFor: vacantFor || 'N/A', }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `occupancies`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `bed_occupancy_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; // export const tenantComplaintReport = async ( // fileType: number, // clientId: number, // propId: number, // startDate: string, // endDate: string // ) => { // const C = "Report Controller"; // const F = "tenantComplaintReport"; // try { // let tenantComplaintData = []; // if (propId !== 0) { // tenantComplaintData = await complaintDB.getByClientIdAndProperyAndDateRangeForReport({ // clientId, // propId, // raisedFor: CONSTANTS.COMPLAIN_FOR.ROOM, // startDate, // endDate, // }); // } else { // tenantComplaintData = await complaintDB.getByClientIdAndDateRangeForReport({ // clientId, // raisedFor: CONSTANTS.COMPLAIN_FOR.ROOM, // startDate, // endDate, // }); // } // if (fileType === 1) { // const workbook = new ExcelJS.Workbook(); // const worksheet = workbook.addWorksheet( // "Tenant Complaint Report", // { views: [ // { state: "frozen", xSplit: 1, ySplit: 1 } // ] // } // ); // worksheet.columns = [ // { header: "Title", key: "title", width: 20 }, // { header: "Room No.", key: "roomNum", width: 20 }, // { header: "Property", key: "property", width: 20 }, // { header: "Tenant Name", key: "tenantName", width: 20 }, // { header: "Tenant Mobile", key: "tenantMobile", width: 20 }, // { header: "Assigned To", key: "assignedToName", width: 20 }, // { header: "Raised At", key: "createdOn", width: 30}, // { header: "Status", key: "status", width: 30}, // { header: "Resolved At", key: "resolvedOn", width: 30}, // { header: "Resolution Time", key: "resolutionTime", width: 30 }, // { header: "Description", key: "description", width: 20 }, // { header: "Notes", key: "notes", width: 20 }, // ]; // worksheet.getRow(1).eachCell((cell) => { // cell.font = { // bold: true, // color: { argb: 'FFFFFFFF' }, // }; // cell.fill = { // type: 'pattern', // pattern: 'solid', // fgColor: { argb: 'FF0078BD' } // }; // cell.alignment = { vertical: 'middle', horizontal: 'center' }; // }); // if (tenantComplaintData.length > 0) { // for (const record of tenantComplaintData) { // let resolutionTime = "N/A"; // if (record.createdAt && record.resolvedAt) { // resolutionTime = moment(record.resolvedAt).diff(record.createdAt, "hours").toString(); // } // let status = Number(record.status) === 1 ? "Pending" : Number(record.status) === 2 ? "Assigned" : "Resolved"; // let row = worksheet.addRow({ // title: record?.title || "", // roomNum: record?.roomNum || "-", // property: record?.propName || "", // tenantName: record?.tenantName || "", // tenantMobile: record?.tenantMobile || "", // assignedToName: record?.assignedToName || "", // createdOn: record?.createdAt, // resolvedOn: record?.resolvedAt || "N/A", // status: status || "N/A", // resolutionTime: resolutionTime, // description: record?.description || "", // notes: record?.staffNote || "", // }); // row.eachCell((cell) => { // cell.alignment = { vertical: 'middle', horizontal: 'center' }; // }); // } // // const buffer = await workbook.xlsx.writeBuffer(); // const raw = await workbook.xlsx.writeBuffer(); // const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); // const folderName = `complaints`; // const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; // const urlBase = folderPath.replace( // "uploads/", // `${process.env.UPLOAD_PATH}/` // ); // if (!fs.existsSync(folderPath)) { // fs.mkdirSync(folderPath, { recursive: true }); // } // const fileName = `tenant_complaint_report_${moment().format( // "HHmmss" // )}_${startDate}_${endDate}.xlsx`; // const filePath = `${folderPath}/${fileName}`; // // fs.writeFileSync(filePath, Buffer.from(buffer)); // fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); // const url = `${urlBase}/${fileName}`; // return { url, fileName }; // } else { // return false; // } // } else { // return false; // } // } catch (error: any) { // log.info(`[${C}], [${F}], Error: ${error?.message || error}`); // return false; // } // }; export const propertyComplaintReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "propertyComplaintReport"; try { let propertyComplaintData = []; if (Array.isArray(propId) && propId.length > 0) { propertyComplaintData = await complaintDB.getByClientIdAndProperyAndDateRangeForReport({ clientId, propId, startDate, endDate, }); } else { propertyComplaintData = await complaintDB.getByClientIdAndDateRangeForReport({ clientId, startDate, endDate, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Tenant Complaint Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Property", key: "property", width: 20 }, { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Tenant Name", key: "tenantName", width: 20 }, { header: "Tenant Mobile", key: "tenantMobile", width: 20 }, { header: "Complaint Type", key: "title", width: 20 }, { header: "Raised At", key: "createdOn", width: 30 }, { header: "Status", key: "status", width: 30 }, { header: "Assigned To", key: "assignedToName", width: 20 }, { header: "Resolved At", key: "resolvedOn", width: 30 }, { header: "Resolution Time(hours)", key: "resolutionTime", width: 30 }, { header: "Description", key: "description", width: 20 }, { header: "Notes", key: "notes", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (propertyComplaintData.length > 0) { for (const record of propertyComplaintData) { const room = await roomDB.getById({ id: record.roomId, }); let flatName = "N/A"; if (room) { const property = await propertyDB.getById({ id: record.propId }); if (property.type === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: room.flatId }); flatName = name; } else { flatName = room.floor === "G" ? "Ground" : room.floor; } } let resolutionTime = "N/A"; if (record.createdAt && record.resolvedAt) { resolutionTime = moment(record.resolvedAt).diff(record.createdAt, "hours").toString(); } let status = Number(record.status) === 1 ? "Pending" : Number(record.status) === 2 ? "Assigned" : "Resolved"; let row = worksheet.addRow({ tenantName: record?.tenantName || "N/A", floorOrFlat: flatName || "N/A", tenantMobile: record?.tenantMobile || "N/A", roomNum: record?.roomNum || "N/A", property: record?.propName || "-", title: record?.title || "-", assignedToName: record?.assignedToName || "-", createdOn: record?.createdAt ? moment(record?.createdAt).format("DD/MM/YYYY") : "", resolvedOn: record?.resolvedAt ? moment(record?.resolvedAt).format("DD/MM/YYYY") : "N/A", status: status || "", resolutionTime: resolutionTime, description: record?.description || "", notes: record?.staffNote || "", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `complaints`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `tenant_complaint_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const leadReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "leadReport"; try { let leadData = []; if (Array.isArray(propId) && propId.length > 0) { leadData = await leadDB.getListByPropIdAndDateRange({ clientId, propId, startDate, endDate, }); } else { leadData = await leadDB.getListByDateRangeX({ clientId, startDate, endDate, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Lead Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Name", key: "name", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Gender", key: "gender", width: 20 }, { header: "Properties Interested", key: "propList", width: 40 }, { header: "Source", key: "source", width: 20 }, { header: "Rent Range", key: "rentRange", width: 20 }, { header: "Status", key: "status", width: 30 }, { header: "Assigned To", key: "assignedTo", width: 30 }, { header: "Created At", key: "createdAt", width: 30 }, { header: "Last Updated At", key: "updatedAt", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (leadData.length > 0) { for (const record of leadData) { let linkedProperties = await leadDB.getLinkedProperties({ leadId: record.id, clientId, }); let propNames = ""; if (linkedProperties.length > 0) { let namesArray = linkedProperties.map((item: any) => item.name); propNames = namesArray.join(", "); } let assignedStaffs = await leadDB.getLinkedStaffs({ leadId: record.id, clientId, }); let assignedNames = ""; if (assignedStaffs.length > 0) { let namesArray = assignedStaffs.map((item: any) => item.name); assignedNames = namesArray.join(", "); } let sourceName = getSourceName(record.source); let rentRange = rentRangeNames(record.rentRange); let status = getLeadStatusDescription(record.status); let row = worksheet.addRow({ name: record?.name || "N/A", mobile: record?.mobile || "N/A", gender: Number(record?.gender) === 1 ? "Male" : "Female", propList: propNames === "" ? "N/A" : propNames, rentRange: rentRange || "N/A", source: sourceName || "N/A", status: status || "", assignedTo: assignedNames || "-", createdAt: record?.createdAt ? moment(record?.createdAt).format("DD/MM/YYYY") : "", updatedAt: record?.updatedAt ? moment(record?.updatedAt).format("DD/MM/YYYY") : "", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `leads`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `lead_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const activityReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "ActivityReport"; try { let activityLogs = []; if (Array.isArray(propId) && propId.length > 0) { activityLogs = await activityLogsDB.getByPropIdForReport({ propId, startDate, endDate, }); } else { activityLogs = await activityLogsDB.getByClientIdForReport({ clientId, startDate, endDate, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Activity Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Activity", key: "activity", width: 20 }, { header: "Tenant Name", key: "tenantName", width: 20 }, { header: "Tenant Mobile", key: "tenantMobile", width: 20 }, { header: "Property Name", key: "propName", width: 20 }, { header: "Done By", key: "doneBy", width: 20 }, { header: "Done By Role", key: "doneByRole", width: 20 }, { header: "Activity Description", key: "description", width: 50, style: { alignment: { wrapText: true, vertical: "top" } } }, { header: "Performed From", key: "device", width: 20 }, { header: "Activity Performed At", key: "createdAt", width: 30 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (activityLogs.length > 0) { for (const record of activityLogs) { const activityName = record?.activity ? getActivityDescription(record?.activity) : "Unregistered Activity Type"; const userType = record?.doneByUserType === CONSTANTS.USER_TYPE.STAFF ? "Staff" : "Client"; const roleName = record?.doneByRole ? getStaffRoleName(record?.doneByRole) : "N/A"; let row = worksheet.addRow({ activity: activityName, tenantName: record?.tenantName || "N/A", tenantMobile: record?.tenantMobile || "N/A", propName: record?.propName || "N/A", doneBy: `${record?.doneByName}(${userType})` || "N/A", doneByRole: roleName || "N/A", description: record?.description || "", device: record?.device === CONSTANTS.TENANT_DEVICE_TYPE.WEB ? "Website" : "Mobile", createdAt: record?.createdAt ? moment(record?.createdAt).format("DD/MM/YYYY HH:mm:ss") : "", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', wrapText: true, horizontal: 'center' }; }); row.height = 60; } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `activity`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `activity_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const moveOutReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "Move Out Report"; try { let tenants = []; if (Array.isArray(propId) && propId.length > 0) { tenants = await moveOutDB.getByPropIdandDateRange({ propId, startDate, endDate, }); } else { tenants = await moveOutDB.getByClientIdandDateRange({ clientId, startDate, endDate }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Move Out Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Name", key: "name", width: 20 }, { header: "Room No.", key: "roomNum", width: 20 }, { header: "Room Name", key: "roomName", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Alternate Mobile", key: "alternateMobile", width: 20 }, { header: "Email", key: "email", width: 20 }, { header: "Property", key: "property", width: 20 }, { header: "Occupied Bed", key: "occupiedBeds", width: 20 }, { header: "Rent", key: "rent", width: 20 }, { header: "Security", key: "security", width: 20 }, { header: "Lock-In-Period", key: "lockInPeriod", width: 20 }, { header: "Notice Period", key: "noticePeriod", width: 20 }, { header: "Move-In-Date", key: "moveInDate", width: 20 }, { header: "Move-Out-Date", key: "moveOutDate", width: 20 }, { header: "Rental Cycle", key: "rentalCycle", width: 20 }, { header: "Total Dues", key: "totalDues", width: 30 }, { header: "Total Collection", key: "totalCollection", width: 30 }, { header: "Payment Via Kipinn", key: "kipinnCollection", width: 30 }, { header: "Offline Payment", key: "offlineCollection", width: 30 }, { header: "Room Option", key: "roomOptionName", width: 30 }, { header: "Occupancy Status", key: "occupancyStatus", width: 30 }, { header: "Last Rent Paid Status", key: "paidStatus", width: 30 }, { header: "Kyc Status", key: "kycStatus", width: 20 }, { header: "Gender", key: "gender", width: 20 }, { header: "Blood Group", key: "bloodGroup", width: 20 }, { header: "DOB", key: "dob", width: 20 }, { header: "Aadhaar Number", key: "aadhaarNumber", width: 20 }, { header: "Father Name", key: "fatherName", width: 30 }, { header: "Father Mobile", key: "fatherMobile", width: 30 }, { header: "Father Occupation", key: "fatherOccupation", width: 30 }, { header: "Father Annual Income", key: "fatherAnnualIncome", width: 30 }, { header: "Mother Name", key: "motherName", width: 30 }, { header: "Mother Mobile", key: "motherMobile", width: 30 }, { header: "Local Guardian Name", key: "localGuardianName", width: 30 }, { header: "Local Guardian Mobile", key: "localGuardianMobile", width: 30 }, { header: "Guardian Relation", key: "localGuardianRelation", width: 30 }, { header: "Occupation", key: "occupation", width: 30 }, { header: "Instituition/Company Name", key: "institutionName", width: 30 }, { header: "Instituition/Work Email", key: "institutionEmail", width: 30 }, { header: "Course/Designation", key: "title", width: 30 }, { header: "Address", key: "address", width: 30 }, { header: "Notes", key: "notes", width: 30 }, { header: "Move Out Reason", key: "moveOutReason", width: 30 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true, }; }); if (tenants.length > 0) { for (const tenant of tenants) { const property = await propertyDB.getById({ id: tenant.propId }); //const propertyName = `${tenant?.roomNum}, ${property?.name}`; const propertyName = `${property?.name}`; const totalDues = await moveOutDuesDB.getTotalDuesByTenantId({ tenantId: tenant.id, propId: tenant.propId, }); //const mobile = tenant.mobile.slice(0, -4) + "XXXX"; const mobile = tenant.mobile; const occupancyStatus = tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.RESERVED ? "Reserved" : tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT ? `Going to vacate on ${tenant.moveOutDate}` : tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.OCCUPIED ? "Occupied" : "Not Occupied"; const genderVal = tenant.gender === CONSTANTS.GENDER.MALE ? "Male" : tenant.gender === CONSTANTS.GENDER.FEMALE ? "Female" : "Other"; const kycStatus = getKycStatusName(tenant.kycStatus); const lastRentPaid = await ledgerDB.getPaidLastRent({ tenantId: tenant.id, clientId: clientId, }); const tenantCollection = await transactionDB.tenantCollectionStats({ tenantId: tenant.id, clientId: clientId, }); let paidStatus = ``; if (lastRentPaid?.rentStartDate || lastRentPaid?.dueDate) { const lastRentCollection = await transactionDB.getLastPaidByClientIdAndTenantId({ tenantId: tenant.id, clientId: clientId }); if (lastRentCollection) { paidStatus = `Rent Paid for : ${moment(lastRentPaid?.rentStartDate ? lastRentPaid?.rentStartDate : lastRentPaid?.dueDate).format("MMM YYYY")}\nCollected on: ${moment(lastRentCollection?.collectionDate).format("DD MMM YYYY")}` } else { paidStatus = `Rent Paid for : ${moment(lastRentPaid?.rentStartDate ? lastRentPaid?.rentStartDate : lastRentPaid?.dueDate).format("MMM YYYY")}`; } } else { paidStatus = `-`; } let relation = ""; if (Number(tenant?.localGuardianRelation) === 1) relation = "Uncle"; else if (Number(tenant?.localGuardianRelation) === 2) relation = "Aunt"; else if (Number(tenant?.localGuardianRelation) === 3) relation = "Brother"; else if (Number(tenant?.localGuardianRelation) === 4) relation = "Sister"; else relation = "Friend"; if (tenant?.localGuardianName == "" || tenant?.localGuardianName == null) relation = ""; let tenantOccupation = ""; if (Number(tenant?.occupation) === 1) { tenantOccupation = "Student"; } else if (Number(tenant?.occupation) === 2) { tenantOccupation = "Professional"; } else { tenantOccupation = tenant?.occupation; } let row = worksheet.addRow({ roomNum: tenant.roomNum || "-", roomName: tenant?.roomName || "-", name: tenant.name || "", mobile: mobile.toString() || "", gender: genderVal || "", dob: tenant?.dob ? moment(tenant?.dob).format("DD/MM/YYYY") : "", aadhaarNumber: tenant.aadhaarNumber || "", property: propertyName || "", occupiedBeds: tenant?.occupiedBeds || "", kycStatus: kycStatus || "", rent: tenant.rent || 0, security: tenant.security || 0, lockInPeriod: tenant.lockInPeriod || "", noticePeriod: tenant.noticePeriod || "", moveInDate: tenant?.moveInDate ? moment(tenant?.moveInDate).format("DD/MM/YYYY") : "", moveOutDate: tenant?.moveOutDate ? moment(tenant?.moveOutDate).format("DD/MM/YYYY") : "", rentalCycle: tenant.rentalCycle || "", totalDues: totalDues?.totalDues || 0, totalCollection: tenantCollection.totalCollection || 0, kipinnCollection: tenantCollection.kipinnCollection || 0, offlineCollection: tenantCollection.offlineCollection || 0, occupancyStatus: occupancyStatus, //paidStatus: lastRentPaid?.rentStartDate ? `${moment(lastRentPaid?.rentStartDate).format("MMM")} Paid` : "-", paidStatus, fatherName: tenant.fatherName || "", motherName: tenant.motherName || "", institutionName: tenant.institutionName || "", institutionEmail: tenant.institutionEmail || "", title: tenant.title || "", roomOptionName: tenant.roomOptionName || "", address: tenant.address || "", occupation: tenantOccupation, fatherMobile: tenant.fatherMobile, fatherOccupation: tenant.fatherOccupation, fatherAnnualIncome: tenant.fatherAnnualIncome, motherMobile: tenant.motherMobile, localGuardianName: tenant.localGuardianName, localGuardianMobile: tenant.localGuardianMobile, localGuardianRelation: relation, bloodGroup: getBloodGroupNames(tenant?.bloodGroup), notes: tenant?.notes || "", moveOutReason: tenant?.moveOutReason || "", email: tenant.email }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; }); row.height = 30; } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `moveOut`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `moved_out_tenant_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const bookingReport = async ( fileType: number, clientId: number, propId: any, ) => { const C = "Report Controller"; const F = "Booking Report"; try { let tenants = []; if (Array.isArray(propId) && propId.length > 0) { tenants = await occupancyDB.getBookingsByPropId({ propId }); } else { tenants = await occupancyDB.getBookingsByClientId({ clientId, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Booking Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Name", key: "name", width: 20 }, { header: "Booking Date", key: "createdAt", width: 20 }, { header: "Move-In-Date", key: "moveInDate", width: 20 }, { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Room Name", key: "roomName", width: 20 }, { header: "Property", key: "property", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Alternate Mobile", key: "alternateMobile", width: 20 }, //{ header: "Email", key: "email", width: 20 }, { header: "Occupied Bed", key: "occupiedBeds", width: 20 }, { header: "Rent", key: "rent", width: 20 }, { header: "Security", key: "security", width: 20 }, { header: "Lock-In-Period", key: "lockInPeriod", width: 20 }, { header: "Notice Period", key: "noticePeriod", width: 20 }, { header: "Rental Cycle", key: "rentalCycle", width: 20 }, { header: "Room Option", key: "roomOptionName", width: 30 }, { header: "Kyc Status", key: "kycStatus", width: 20 }, { header: "Gender", key: "gender", width: 20 }, { header: "Blood Group", key: "bloodGroup", width: 20 }, { header: "DOB", key: "dob", width: 20 }, { header: "Aadhaar Number", key: "aadhaarNumber", width: 20 }, { header: "Occupation", key: "occupation", width: 30 }, { header: "Notes", key: "notes", width: 30 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (tenants.length > 0) { for (const tenant of tenants) { const property = await propertyDB.getById({ id: tenant.propId }); //const propertyName = `${tenant?.roomNum}, ${property?.name}`; const propertyName = `${property?.name}`; let flatName = ""; if (property.type === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: tenant.flatId }); flatName = name; } else { flatName = tenant.floor === "G" ? "Ground" : tenant.floor; } const totalDues = await duesDB.getTotalDuesByTenantId({ tenantId: tenant.id, propId: tenant.propId, }); //const mobile = tenant.mobile.slice(0, -4) + "XXXX"; const mobile = tenant.mobile; const occupancyStatus = tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.RESERVED ? "Reserved" : tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT ? `Going to vacate on ${tenant.moveOutDate}` : tenant.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.OCCUPIED ? "Occupied" : "Not Occupied"; const genderVal = tenant.gender === CONSTANTS.GENDER.MALE ? "Male" : tenant.gender === CONSTANTS.GENDER.FEMALE ? "Female" : "Other"; const kycStatus = getKycStatusName(tenant.kycStatus); const lastRentPaid = await ledgerDB.getPaidLastRent({ tenantId: tenant.id, clientId: clientId, }); const tenantCollection = await transactionDB.tenantCollectionStats({ tenantId: tenant.id, clientId: clientId, }); let relation = ""; if (Number(tenant?.localGuardianRelation) === 1) relation = "Uncle"; else if (Number(tenant?.localGuardianRelation) === 2) relation = "Aunt"; else if (Number(tenant?.localGuardianRelation) === 3) relation = "Brother"; else if (Number(tenant?.localGuardianRelation) === 4) relation = "Sister"; else relation = "Friend"; if (tenant?.localGuardianName == "" || tenant?.localGuardianName == null) relation = ""; let tenantOccupation = ""; if (Number(tenant?.occupation) === 1) { tenantOccupation = "Student"; } else if (Number(tenant?.occupation) === 2) { tenantOccupation = "Professional"; } else { tenantOccupation = tenant?.occupation; } let row = worksheet.addRow({ roomNum: tenant.roomNum || "-", floorOrFlat: flatName || "N/A", roomName: tenant?.roomName || "-", name: tenant.name || "", mobile: mobile.toString() || "", gender: genderVal || "", dob: tenant?.dob ? moment(tenant?.dob).format("DD/MM/YYYY") : "", aadhaarNumber: tenant.aadhaarNumber || "", property: propertyName || "", occupiedBeds: tenant?.occupiedBeds || "", kycStatus: kycStatus || "", rent: tenant.rent || 0, security: tenant.security || 0, lockInPeriod: tenant.lockInPeriod || "", noticePeriod: tenant.noticePeriod || "", moveInDate: tenant?.moveInDate ? moment(tenant?.moveInDate).format("DD/MM/YYYY") : "", createdAt: tenant?.createdAt ? moment(tenant?.createdAt).format("DD/MM/YYYY") : "", rentalCycle: tenant.rentalCycle || "", totalDues: totalDues?.totalDues || 0, totalCollection: tenantCollection.totalCollection || 0, kipinnCollection: tenantCollection.kipinnCollection || 0, offlineCollection: tenantCollection.offlineCollection || 0, occupancyStatus: occupancyStatus, paidStatus: lastRentPaid?.rentStartDate ? `${moment(lastRentPaid?.rentStartDate).format("MMM")} Paid` : "-", fatherName: tenant.fatherName || "", motherName: tenant.motherName || "", institutionName: tenant.institutionName || "", institutionEmail: tenant.institutionEmail || "", title: tenant.title || "", roomOptionName: tenant.roomOptionName || "", address: tenant.address || "", occupation: tenantOccupation, fatherMobile: tenant.fatherMobile, fatherOccupation: tenant.fatherOccupation, fatherAnnualIncome: tenant.fatherAnnualIncome, motherMobile: tenant.motherMobile, localGuardianName: tenant.localGuardianName, localGuardianMobile: tenant.localGuardianMobile, localGuardianRelation: relation, bloodGroup: getBloodGroupNames(tenant?.bloodGroup), notes: tenant?.notes || "", email: tenant.email }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; }); } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `tenant`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `booking_report_${moment().format( "HHmmss" )}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const propertyReport = async ( fileType: number, clientId: number, propId: any, ) => { const C = "Report Controller"; const F = "Property Report"; try { let properties = []; let occupancyData = []; if (Array.isArray(propId) && propId.length > 0) { properties = await propertyDB.getAllByClientIdAndPropId({ clientId, propIds: propId }); occupancyData = await occupancyDB.getBedOccupancyReportDataByPropId({ propId }); } else { properties = await propertyDB.getAllByClientId({ clientId, }); occupancyData = await occupancyDB.getBedOccupancyReportDataByClientId({ clientId }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "Property Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); const occupiedWorksheet = workbook.addWorksheet( "Occupied Bed Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); const reservedWorksheet = workbook.addWorksheet( "Reserved Bed Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); const vacantWorksheet = workbook.addWorksheet( "Vacant Bed Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Property Name", key: "propName", width: 20 }, { header: "Vacant Beds", key: "vacantBed", width: 20 }, { header: "Occupied Beds", key: "occupiedBed", width: 20 }, { header: "Bookings", key: "bookings", width: 20 }, { header: "Moving Out", key: "movingOut", width: 20 }, { header: "Collection", key: "collection", width: 20 }, { header: "Pending Dues", key: "pendingDues", width: 20 }, { header: "Total collection for the month", key: "thisMonthCollection", width: 40 }, { header: "No. of Dues for the month", key: "noOfDuesForTheMonth", width: 40 }, { header: "No. of resident paid", key: "noOfResidentPaid", width: 40 }, { header: "Total rent generated for the month", key: "generatedRent", width: 40 }, ]; occupiedWorksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Floor/Flat Name", key: "floor", width: 20 }, { header: "Room Number", key: "roomNum", width: 20 }, // { header: "Bed Status", key: "bedStatus", width: 20 }, { header: "Tenant Name", key: "tenantName", width: 20 }, { header: "Tenant Mobile", key: "tenantMobile", width: 20 }, { header: "Security", key: "security", width: 30 }, { header: "Fixed Bed Rent Per Month", key: "rentPerMonth", width: 30 }, { header: "Tenant Rent", key: "tenantRent", width: 30 }, { header: "Pending Dues", key: "totalDues", width: 30 }, { header: "Move-In Date", key: "moveInDate", width: 20 }, { header: "Getting Vacant On", key: "moveOutDate", width: 20 }, ]; reservedWorksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Floor/Flat Name", key: "floor", width: 20 }, { header: "Room Number", key: "roomNum", width: 20 }, // { header: "Bed Status", key: "bedStatus", width: 20 }, { header: "Tenant Name", key: "tenantName", width: 20 }, { header: "Tenant Mobile", key: "tenantMobile", width: 20 }, { header: "Security", key: "security", width: 30 }, { header: "Fixed Bed Rent Per Month", key: "rentPerMonth", width: 30 }, { header: "Tenant Rent", key: "tenantRent", width: 30 }, { header: "Pending Dues", key: "totalDues", width: 30 }, { header: "Move-In Date", key: "moveInDate", width: 20 }, // { header: "Getting Vacant On", key: "moveOutDate", width: 20 }, ]; vacantWorksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Floor/Flat Name", key: "floor", width: 20 }, { header: "Room Number", key: "roomNum", width: 20 }, // { header: "Bed Status", key: "bedStatus", width: 20 }, { header: "Security", key: "security", width: 30 }, { header: "Fixed Bed Rent Per Month", key: "rentPerMonth", width: 30 }, { header: "Vacant For", key: "vacantFor", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); occupiedWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); reservedWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); vacantWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (properties.length > 0) { for (const property of properties) { //const propertyName = `${tenant?.roomNum}, ${property?.name}`; const propName = `${property?.name}`; const totalDues = await duesDB.getAllDuesByPropId({ propId: property.id, }); const totalCollection = await transactionDB.getAllCollectionByPropId({ propId: property.id, }); const thisMonthCollection = await transactionDB.getAllCollectionByPropIdForCurrentMonth({ propId: property.id, }); const beds = await bedDB.getCountsByPropIdForReport({ propId: property.id, }); let vacantBeds = 0; let occupiedBeds = 0; if (beds) { vacantBeds = beds?.vacant || 0; occupiedBeds = beds?.occupied || 0; } const bookings = await occupancyDB.getReservedTenantsCountByPropId({ propId: property.id, }); const movingOut = await occupancyDB.getMovingOutTenantsCountByPropId({ propId: property.id, }); const noOfDuesForTheMonth = await duesDB.getTotalNumberOfDuesByPropIdDateRange({ propId: property.id, startDate: moment().startOf('month').format("YYYY-MM-DD"), endDate: moment().endOf('month').format("YYYY-MM-DD"), }); const noOfResidentPaid = await transactionDB.getNumberOfTenantPaidByPropertyIdAndDateRange({ propId: property.id, startDate: moment().startOf('month').format("YYYY-MM-DD"), endDate: moment().endOf('month').format("YYYY-MM-DD"), }); const generatedRent = await ledgerDB.getGeneratedRentByPropIdAndDateRange({ propId: property.id, startDate: moment().startOf('month').format("YYYY-MM-DD"), endDate: moment().endOf('month').format("YYYY-MM-DD"), }); let row = worksheet.addRow({ propName: propName || "-", vacantBed: vacantBeds || 0, occupiedBed: occupiedBeds || 0, bookings: bookings || 0, movingOut: movingOut || 0, collection: totalCollection || 0, pendingDues: totalDues || 0, thisMonthCollection: thisMonthCollection || 0, noOfDuesForTheMonth: noOfDuesForTheMonth || 0, noOfResidentPaid: noOfResidentPaid || 0, generatedRent: generatedRent || 0, }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; }); } if (occupancyData.length > 0) { for (const occupancy of occupancyData) { let bedStatus = ""; if (occupancy.bedStatus === CONSTANTS.BED_STATUS.VACANT) { bedStatus = "Vacant"; } else if (occupancy.bedStatus === CONSTANTS.BED_STATUS.OCCUPIED) { bedStatus = "Occupied"; } else if (occupancy.bedStatus === CONSTANTS.BED_STATUS.MOVING_OUT) { bedStatus = "Tenant Moving Out"; } else if (occupancy.bedStatus === CONSTANTS.BED_STATUS.RESERVED && occupancy.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.MOVING_OUT) { bedStatus = "Tenant Moving Out, Bed Reserved For another" } else if (occupancy.bedStatus === CONSTANTS.BED_STATUS.VACANT_RESERVED || occupancy.bedStatus === CONSTANTS.BED_STATUS.RESERVED) { bedStatus = "Reserved"; } let vacantFor = "N/A"; if (occupancy.bedStatus === CONSTANTS.BED_STATUS.VACANT) { let lastMoveOutDate = await moveOutDB.getLastMoveOutDateByBedId({ bedId: occupancy.bedId, }); // let lastMoveOutDate = vacantSince.moveOutDate; let dayDiff = moment().diff(moment(lastMoveOutDate.moveOutDate), "days") + 1; vacantFor = String(dayDiff) + " days"; } if (occupancy.bedStatus === CONSTANTS.BED_STATUS.VACANT) { let row = vacantWorksheet.addRow({ roomNum: occupancy?.roomNum || "-", floor: occupancy?.floor || "", property: occupancy?.propertyName || "", // bedStatus: bedStatus || "", rentPerMonth: occupancy?.rentPerBed || 0, security: occupancy?.tenantSecurity || 0, vacantFor: vacantFor || 'N/A', }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } else if ( occupancy.bedStatus === CONSTANTS.BED_STATUS.VACANT_RESERVED || (occupancy.bedStatus === CONSTANTS.BED_STATUS.RESERVED && occupancy.occupancyStatus === CONSTANTS.OCCUPANCY_STATUS.RESERVED) ) { let row = reservedWorksheet.addRow({ roomNum: occupancy?.roomNum || "-", floor: occupancy?.floor || "", property: occupancy?.propertyName || "", // bedStatus: bedStatus || "", // moveOutDate: occupancy?.moveOutDate ? moment(occupancy.moveOutDate).format("DD/MM/YYYY") : "N/A", tenantName: occupancy.tenantName || "", tenantMobile: occupancy.tenantMobile || "", rentPerMonth: occupancy?.rentPerBed || 0, tenantRent: occupancy?.tenantRent || 0, security: occupancy?.tenantSecurity || 0, totalDues: occupancy?.totalDues || 0, moveInDate: occupancy?.moveInDate ? moment(occupancy.moveInDate).format("DD/MM/YYYY") : "N/A", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } else { let row = occupiedWorksheet.addRow({ roomNum: occupancy?.roomNum || "-", floor: occupancy?.floor || "", property: occupancy?.propertyName || "", // bedStatus: bedStatus || "", moveOutDate: occupancy?.moveOutDate ? moment(occupancy.moveOutDate).format("DD/MM/YYYY") : "N/A", tenantName: occupancy.tenantName || "", tenantMobile: occupancy.tenantMobile || "", rentPerMonth: occupancy?.rentPerBed || 0, tenantRent: occupancy?.tenantRent || 0, security: occupancy?.tenantSecurity || 0, totalDues: occupancy?.totalDues || 0, moveInDate: occupancy?.moveInDate ? moment(occupancy.moveInDate).format("DD/MM/YYYY") : "N/A", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } } } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `tenant`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `property_report_${moment().format( "HHmmss" )}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const PropertyLeaseReport = async ( fileType: number, clientId: number, propId: any, ) => { const C = "Report Controller"; const F = "Property Lease Report"; try { let leases = []; if (Array.isArray(propId) && propId.length > 0) { leases = await propertyLeaseDB.getByClientIdAndPropIds({ clientId, propIds: propId, }); } else { leases = await propertyLeaseDB.getByClientId({ clientId, }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "property Lease Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); worksheet.columns = [ { header: "Property Name", key: "propertyName", width: 22 }, { header: "Flat Name", key: "flatName", width: 22 }, { header: "Landlord Name", key: "landlordName", width: 22 }, { header: "Landlord Mobile Number", key: "landlordMobile", width: 22 }, { header: "Alternate Mobile Number", key: "landlordAltMobile", width: 22 }, { header: "Landlord Email Address", key: "landlordEmail", width: 25 }, { header: "Ownership Type", key: "ownershipType", width: 20 }, { header: "Property Type", key: "propertyType", width: 20 }, { header: "Monthly Rent", key: "rent", width: 18 }, { header: "Security Deposit", key: "security", width: 20 }, { header: "Rent Due Date", key: "rentDate", width: 18 }, { header: "Agreement Start Date", key: "startDate", width: 22 }, { header: "Agreement End Date", key: "endDate", width: 22 }, { header: "Lock-in Period (Months)", key: "lockInPeriod", width: 22 }, { header: "Notice Period (Months)", key: "noticePeriod", width: 22 }, { header: "Rent Collection Method", key: "rentCollectionType", width: 25 }, { header: "Rent Increment Type", key: "incrementType", width: 25 }, { header: "Rent Increment Value", key: "incrementValue", width: 22 }, { header: "Rent Increment Month", key: "incrementMonth", width: 22 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (leases.length > 0) { for (const lease of leases) { let ownerShip = "-"; if (Number(lease?.ownershipType) === 1) { ownerShip = "Owner"; } else if (Number(lease?.ownershipType) === 2) { ownerShip = "Co-Owner"; } else if (Number(lease?.ownershipType) === 3) { ownerShip = "POA"; } let propertyType = "-"; if (Number(lease?.propertyType) === 1) { propertyType = "PG"; } else if (Number(lease?.propertyType) === 2) { propertyType = "Appartment"; } else if (Number(lease?.propertyType) === 3) { propertyType = "Villa"; } else if (Number(lease?.propertyType) === 4) { propertyType = "Independent Floor"; } let row = worksheet.addRow({ propertyName: lease?.propertyName || "-", flatName: lease?.flatName || "-", landlordName: lease?.landlordName || "-", landlordMobile: lease?.landlordMobile || "-", landlordAltMobile: lease?.landlordAltMobile || "-", landlordEmail: lease?.landlordEmail || "-", ownershipType: ownerShip, propertyType: propertyType || "-", rent: lease?.rent || "-", security: lease?.security || "-", rentDate: lease?.rentDate ? `Every ${moment(lease?.rentDate).format("Do")}` : "-", startDate: lease?.startDate ? moment(lease?.startDate).format("DD/MM/YYYY") : "", endDate: lease?.endDate ? moment(lease?.endDate).format("DD/MM/YYYY") : "", lockInPeriod: lease?.lockInPeriod || "-", noticePeriod: lease?.notice || "-", rentCollectionType: lease?.rentCollectionType ? Number(lease?.rentCollectionType) === 1 ? "Advance" : "Month End" : "-", incrementType: lease?.incrementType ? Number(lease?.incrementType) === 1 ? "Flat" : "Percentage" : "-", incrementValue: lease?.incrementValue || "-", incrementMonth: lease?.incrementMonth || "-", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; }); row.height = 30; } // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `propLease`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `property_lease_report_${moment().format( "HHmmss" )}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const RefundRequests = async ( fileType: number, clientId: number, requestType: number, propId: any, ) => { const U = "Export Utility"; const F = "Export Request"; try { let requests = []; let type = requestType; if (Number(type) === 0) { requests = await requestDB.getRoomReqByClientId({ clientId, status: CONSTANTS.REQUEST_STATUS.PENDING, }); } else { requests = await requestDB.getRoomReqByClientIdWithRequestType({ clientId, status: CONSTANTS.REQUEST_STATUS.PENDING, type: Number(type), }); } let filteredRequests = []; if (Array.isArray(propId) && propId.length > 0) { filteredRequests = requests.filter((req: any) => propId.includes(String(req.propertyId))); } else { filteredRequests = requests; } if (filteredRequests && filteredRequests.length > 0) { const types = new Set(filteredRequests?.map((r: any) => r.type)); const onboarding = types.has(CONSTANTS.REQUEST_TYPE.ROOM_SELECTION); const moveOut = types.has(CONSTANTS.REQUEST_TYPE.MOVE_OUT); const refund = types.has(CONSTANTS.REQUEST_TYPE.REFUND); const guest = types.has(CONSTANTS.REQUEST_TYPE.GUEST); if (!refund) { log.info(`[${U}], [${F}], Client Id [${clientId}], Request Type [${requestType}], Only refund export allowed`) return false; } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const worksheet = workbook.addWorksheet( "property Lease Report", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); let header = [ { header: "Tenant Name", key: "tenantName", width: 22 }, { header: "Tenant Mobile", key: "tenantMobile", width: 22 }, { header: "Property Name", key: "propName", width: 22 }, { header: "Room Number", key: "roomNum", width: 22 }, { header: "Move-in Date", key: "moveInDate", width: 22 }, { header: "Move-out Date", key: "moveOutDate", width: 22 }, { header: "Refund Amount", key: "refundAmount", width: 22 }, { header: "Request Type", key: "requestType", width: 22 }, { header: "Date", key: "date", width: 22 }, { header: "Cheque", key: "cancelledCheque", width: 22 }, { header: "Account Holder", key: "holderName", width: 22 }, { header: "Account Number", key: "accountNumber", width: 22 }, { header: "Bank Name", key: "bankName", width: 22 }, { header: "IFSC", key: "ifscCode", width: 22 }, { header: "Description", key: "description", width: 22 } ]; worksheet.columns = header; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); if (filteredRequests && filteredRequests.length > 0) { for (let request of filteredRequests) { let flatName = ""; if (request.propType === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: request.flatId }); flatName = name; } else { flatName = request.floor === "G" ? "Ground Floor" : "Floor " + request.floor; } request.flatName = flatName; if (request.type === CONSTANTS.REQUEST_TYPE.MOVE_OUT) { continue; } else if (request.type === CONSTANTS.REQUEST_TYPE.REFUND) { let collection = 0; let advance = 0; const { totalCollection, advancePaid } = await ledgerDB.getTotalByTenantIdForMovingOut({ tenantId: request.tenantId, clientId: request.clientId, }); collection = totalCollection; advance = advancePaid; collection = collection || 0; advance = advance || 0; let total = 0; if (advance < 0 && collection >= 0) { total = Number(collection) + Number(advance); } else if (advance < 0 && collection < 0) { total = Number(advance); } else { total = Number(collection); } request.refundAmount = Number(total) > 0 ? 0 : total; request.accountDetails = await tenantBankDB.getByClientIdAndTenantId({ clientId, tenantId: request?.tenantId }); let cancelledCheque = await documentDB.getIDByType({ tenantId: request?.tenantId, clientId, type: CONSTANTS.DOCUMENT_TYPES.CANCELLED_CHEQUE, moveOut: 1 }); request.accountDetails.cancelledCheque = null; if (cancelledCheque) { request.accountDetails.cancelledCheque = cancelledCheque?.value; } let row = worksheet.addRow({ tenantName: request.tenantName || "-", tenantMobile: request.tenantMobile || "-", propName: request?.propertyName || "-", roomNum: request?.roomNum || "-", date: request?.createdAt || "-", accountNumber: request?.accountDetails?.accountNum, ifscCode: request?.accountDetails?.ifsc, holderName: request?.accountDetails?.holderName || "-", bankName: request?.accountDetails?.bankName || "-", cancelledCheque: request?.accountDetails?.cancelledCheque || "-", description: request?.description || "-", moveInDate: request?.moveInDate || "-", moveOutDate: request?.moveOutDate || "-", refundAmount: Math.abs(request?.refundAmount) || "-", requestType: "Refund", }); row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; }); } else if (request.type === CONSTANTS.REQUEST_TYPE.GUEST) { continue; } else { continue; } } const currentYear = moment().year(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `requests`; const folderPath = `uploads/documents/${currentYear}/reports/${folderName}/${clientId}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `refund_requests_${moment().format( "HHmmss" )}.xlsx`; const filePath = `${folderPath}/${fileName}`; fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${U}], [${F}], Error: ${error?.message || error}`); return false; } }; export const tenantRentReviewReport = async ( fileType: number, clientId: number, propId: any, ) => { const C = "Report Controller"; const F = "Rent Review"; try { let tenants = []; let properties = []; if (Array.isArray(propId) && propId.length > 0) { tenants = await occupancyDB.getByPropIdWithoutMovedOut({ propId, }); properties = await propertyDB.getByPropId({ propId }); } else { tenants = await occupancyDB.getByClientIdWithoutMovedOut({ clientId, }); properties = await propertyDB.getPropDataByClientId({ clientId }); } if (fileType === 1) { const workbook = new ExcelJS.Workbook(); const summaryWorksheet = workbook.addWorksheet( "Summary", { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); summaryWorksheet.columns = [ { header: "Property Name", key: "propertyName", width: 25 }, { header: "Managed By", key: "managedBy", width: 20 }, { header: "Mobile", key: "contact", width: 20 }, { header: "Period", key: "period", width: 30 }, { header: "Tenant Rent Added", key: "totalRentAdded", width: 20 }, { header: "Tenant Rent To Be Added", key: "rentToBeAdded", width: 25 }, { header: "Total UnPaid Dues", key: "unPaidDues", width: 20 }, { header: "Total Unpaid Rent", key: "unPaidRent", width: 20 }, ]; summaryWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); const start = moment().startOf('month').format('YYYY-MM-DD'); const end = moment().endOf('month').format('YYYY-MM-DD'); const sheetMap: Record = {}; const propertySummary: Record = {}; for (const property of properties) { if (!propertySummary[property.id]) { propertySummary[property.id] = { propertyName: property.name, managedBy: property.ownerName, contact: property.ownerMobile, period: `${start} - ${end}`, unPaidDues: 0, totalRentAdded: 0, expected: 0, allRent: 0, unPaidRent: 0, }; } const getSafeSheetName = (name: string) => { return name .replace(/[\\/?*\[\]:]/g, "") // remove invalid chars .substring(0, 31); // limit to 31 chars }; const rawName = `${property?.name} (${property?.gId})`; const sheetName = getSafeSheetName(rawName); const worksheet = workbook.addWorksheet( sheetName, { views: [ { state: "frozen", xSplit: 1, ySplit: 1 } ] } ); sheetMap[property.id] = worksheet; // ✅ store reference worksheet.columns = [ { header: "Name", key: "name", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Room No.", key: "roomNum", width: 20 }, { header: "Room Name", key: "roomName", width: 20 }, { header: "Move-In-Date", key: "moveInDate", width: 20 }, { header: "Rental Cycle", key: "rentalCycle", width: 20 }, { header: "Stay Type", key: "stay", width: 20 }, { header: "Rent", key: "rent", width: 20 }, { header: "Rent Add Status", key: "rentStatus", width: 20 }, { header: "Remark", key: "remark", width: 30 }, { header: "Rent Added On", key: "rentDate", width: 20 }, { header: "Rent Added Amount", key: "rentAddedAmount", width: 20 }, //{ header: "Rent Period", key: "rentPeriod", width: 20 }, { header: "Total Unpaid Dues", key: "totalDues", width: 20 }, { header: "Total Rent Dues", key: "rentDues", width: 20 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: 'FFFFFFFF' }, }; cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0078BD' } }; cell.alignment = { vertical: 'middle', horizontal: 'center' }; }); } if (tenants.length > 0) { let allDues = await ledgerDB.getCurrentMonthRentEntries({ clientId }); for (const tenant of tenants) { let unpaidDues = await duesDB.getDuesSummaryByTenantId({ tenantId: tenant?.id, clientId, propId: tenant?.propId }); const mobile = tenant.mobile; const day = moment().date(); let rentStatus = "No"; let remark = ""; if (0 !== Number(tenant.rentalCycle) && Number(day) < Number(tenant.rentalCycle)) { //rentStatus = "No"; //ledgerDB.getLastRent() remark = `Rent will be added on ${tenant.rentalCycle}`; } const tenantDues = allDues.filter((d: any) => d.tenantId === Number(tenant.id)); const hasDues = tenantDues.length > 0; let rentAddedAmount = 0; let rentPeriod = "N/A"; let createdAt = ""; if (hasDues) { rentStatus = "Yes"; rentAddedAmount = tenantDues[0]?.amount || 0; createdAt = tenantDues[0]?.createdAt; rentPeriod = `${tenantDues[0]?.rentStartDate} - ${tenantDues[0]?.rentEndDate}` } if ("No" === rentStatus && 0 !== Number(tenant.rentalCycle) && Number(day) >= Number(tenant.rentalCycle)) { //log.info(`tenant ${tenant.mobile}`) let getLastRent = await ledgerDB.getLastRent({ tenantId: Number(tenant.id), clientId, }); //log.info(`tenant ${JSON.stringify(getLastRent)}`) if (getLastRent) { const isFuture = getLastRent?.rentEndDate && moment(getLastRent.rentEndDate).isAfter(moment()); if (isFuture) { rentStatus = "Yes"; createdAt = getLastRent?.createdAt; remark = `Rent dues are created till ${getLastRent?.rentEndDate}`; } } } if (Number(tenant?.stayType) === CONSTANTS.STAY_TYPE.SHORT) { rentStatus = "No"; remark = "Short Stay tenant" } const totalAddedAmount = tenantDues.reduce((sum: number, d: any) => { return sum + (d.amount || 0); }, 0); const worksheet = sheetMap[tenant.propId]; if (worksheet) { let row = worksheet.addRow({ name: tenant.name || "", mobile: mobile.toString() || "", roomNum: tenant.roomNum || "-", roomName: tenant?.roomName || "-", moveInDate: tenant?.moveInDate ? moment(tenant?.moveInDate).format("DD/MM/YYYY") : "", rentalCycle: tenant.rentalCycle || "", rent: tenant.rent || 0, totalDues: unpaidDues?.totalDues || 0, rentDues: unpaidDues?.rentDues || 0, rentStatus, rentAddedAmount, remark, rentDate: rentStatus === 'Yes' ? moment(createdAt).format("YYYY-MM-DD") : '', stay: Number(tenant?.stayType) === CONSTANTS.STAY_TYPE.SHORT ? "Short Stay" : Number(tenant.rentalType) === CONSTANTS.RENTAL_TYPES.MONTHLY ? "Monthly" : Number(tenant.rentalType) === CONSTANTS.RENTAL_TYPES.QUARTERLY ? "Quarterly" : Number(tenant.rentalType) === CONSTANTS.RENTAL_TYPES.HALF_YEARLY ? "Half Yearly" : "Annually" }); //log.info(`Property Summary [${JSON.stringify(propertySummary)}], Particular propSummary [${JSON.stringify(propertySummary[tenant.propId])}], Tenant.Property [${tenant.propId}]`); propertySummary[tenant.propId].unPaidDues += Number(unpaidDues?.totalDues) || 0; propertySummary[tenant.propId].totalRentAdded += Number(totalAddedAmount) || 0; propertySummary[tenant.propId].unPaidRent += Number(unpaidDues?.rentDues) || 0; propertySummary[tenant.propId].allRent += Number(tenant.rent) || 0; if (0 !== Number(tenant.rentalCycle) && Number(day) < Number(tenant.rentalCycle)) { propertySummary[tenant.propId].expected += Number(tenant.rent) || 0; } row.eachCell((cell) => { cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; }); row.height = 30; } } Object.values(propertySummary).forEach((prop) => { const row = summaryWorksheet.addRow({ propertyName: prop.propertyName, managedBy: prop.managedBy, contact: prop.contact, period: prop.period, totalRentAdded: prop.totalRentAdded, rentToBeAdded: Number(prop.expected), unPaidDues: prop.unPaidDues, unPaidRent: prop.unPaidRent, }); row.eachCell((cell) => { cell.alignment = { vertical: "middle", horizontal: "center", }; }); }); // const buffer = await workbook.xlsx.writeBuffer(); const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `tenant`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `kipinn_rent_review_report_${moment().format( "HHmmss" )}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const profitLossReport = async ( fileType: number, clientId: number, propId: number, startDate: string, endDate: string, ) => { const C = "Report Controller"; const F = "profitLossReport"; try { const workbook = new ExcelJS.Workbook(); const MISWorksheet = workbook.addWorksheet("MIS"); let expenses = []; let transactions = []; if (Array.isArray(propId) && propId.length > 0) { expenses = await expenseDB.getSumForReportByPropIds({ clientId, propId, startDate, endDate, }); transactions = await transactionDB.getSumForReportByPropIds({ clientId, propId, startDate, endDate, }); } else { expenses = await expenseDB.getSumForReportByClientId({ clientId, startDate, endDate, }); transactions = await transactionDB.getSumForReportByClientId({ clientId, startDate, endDate, }); } if (Number(fileType) === 1) { let directExpenses = []; let indirectExpenses = []; let directExpenseAmt = 0; let indirectExpenseAmt = 0; let expenseAmt = 0; let directTransactions = []; let indirectTransactions = []; let directTransactionAmt = 0; let indirectTransactionAmt = 0; let transactionAmt = 0; let directIncomeIds = [1, 3, 4, 9, 12, 13, 14, 17, 18]; let index = 0; if (expenses && expenses.length > 0) { for (let expense of expenses) { if ( Number(expense.expenseClassification) === CONSTANTS.EXPENSE_PL_CLASSIFICATION.DIRECT_EXPENSE ) { directExpenses.push(expense); directExpenseAmt += Number(expense.amount); } else { indirectExpenses.push(expense); indirectExpenseAmt += Number(expense.amount); } expenseAmt += Number(expense.amount); } } if (transactions && transactions.length > 0) { for (let transaction of transactions) { if (Number(transaction.transactionFor) === CONSTANTS.DUES_TYPES.SECURITY) continue; if (directIncomeIds.includes(Number(transaction.transactionFor))) { directTransactions.push(transaction); directTransactionAmt += Number(transaction.amount); } else { indirectTransactions.push(transaction); indirectTransactionAmt += Number(transaction.amount); } transactionAmt += Number(transaction.amount); } } // --- 1. Set Column Widths --- MISWorksheet.columns = [ { key: "A", width: 25 }, { key: "B", width: 30 }, { key: "C", width: 18 }, { key: "D", width: 5 }, // Spacer column ]; // --- Common Styles & Borders --- const thinBorder: Partial = { style: "thin", color: { argb: "000000" }, }; const doubleBorder: Partial = { style: "double", color: { argb: "000000" }, }; const standardBorders: Partial = { left: thinBorder, right: thinBorder, top: thinBorder, bottom: thinBorder, }; const boldFont = { name: "Arial", size: 10, bold: true }; const regularFont = { name: "Arial", size: 10 }; // --- Helper function to style cells quickly --- const styleCell = ( cell: ExcelJS.Cell, font: any, alignment: Partial, borders?: Partial, ) => { cell.font = font; cell.alignment = alignment; if (borders) cell.border = borders; }; // --- 2. Title Row (Row 2) --- MISWorksheet.mergeCells("A2:C2"); const titleCell = MISWorksheet.getCell("A2"); titleCell.value = `MIS(${moment(startDate).format("DD-MMM-YYYY")} - ${moment(endDate).format("DD-MMM-YYYY")})`; styleCell(titleCell, boldFont, { horizontal: "center" }, standardBorders); MISWorksheet.getCell("B2").border = standardBorders; MISWorksheet.getCell("C2").border = standardBorders; // --- 3. Table Headers (Row 3) --- const headers = [ ["A3", "Expense Type"], ["B3", "Expense"], ["C3", "Amt"], ]; headers.forEach(([cellRef, value]) => { const cell = MISWorksheet.getCell(cellRef); cell.value = value; styleCell( cell, boldFont, { horizontal: cellRef === "C3" ? "right" : "left" }, standardBorders, ); }); index = 4; // --- 4. Direct Expenses (Rows 4 to 13) --- const directExpenseLength = directExpenses ? directExpenses.length : 0; if (directExpenses && directExpenses.length > 0) MISWorksheet.mergeCells(`A${index}:A${index + directExpenseLength - 1}`); const directExpLabel = MISWorksheet.getCell("A4"); directExpLabel.value = "Direct Expense"; styleCell(directExpLabel, boldFont, { vertical: "top", horizontal: "left", }); if (directExpenses && directExpenses.length > 0) { for (let expense of directExpenses) { const rowNum = index; // Expense Name const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = expense.name; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = expense.amount; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to Direct Expense outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } } else { const rowNum = index; const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = "-"; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = "-"; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to Direct Expense outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } // --- 5. Total Direct Expenses (Row 14) --- const directExpenseRow = MISWorksheet.getCell(`A${index}`); directExpenseRow.value = "Total of Direct Expenses"; styleCell( directExpenseRow, boldFont, { horizontal: "left" }, standardBorders, ); MISWorksheet.getCell(`B${index}`).border = standardBorders; const directExpenseAmtRow = MISWorksheet.getCell(`C${index}`); // row14C.value = { formula: 'SUM(C4:C13)' }; directExpenseAmtRow.value = directExpenseAmt; styleCell( directExpenseAmtRow, boldFont, { horizontal: "right" }, { ...standardBorders, top: thinBorder, bottom: thinBorder }, ); directExpenseAmtRow.numFmt = "#,##0"; index += 1; // --- 6. Indirect Expenses (Rows 15 to 24) --- // const indirectExpenseStartIndex = index; const indirectExpenseLength = indirectExpenses ? indirectExpenses.length : 0; if (indirectExpenses && indirectExpenses.length > 0) MISWorksheet.mergeCells(`A${index}:A${index + indirectExpenseLength - 1}`); const indirectExpLabel = MISWorksheet.getCell(`A${index}`); indirectExpLabel.value = "Indirect Expenses"; styleCell(indirectExpLabel, boldFont, { vertical: "top", horizontal: "left", }); if (indirectExpenses && indirectExpenses.length > 0) { for (let expense of indirectExpenses) { const rowNum = index; // Expense Name const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = expense.name; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = expense.amount; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to InDirect Expense outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } } else { const rowNum = index; const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = "-"; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = "-"; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to Direct Expense outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } // --- Merging the column // MISWorksheet.mergeCells(`A${indirectExpenseStartIndex}:A${index-1}`); // --- 7. Total of Indirect Expenses (Row 25) --- const indirectExpenseRow = MISWorksheet.getCell(`A${index}`); indirectExpenseRow.value = "Total of Indirect Expenses"; styleCell( indirectExpenseRow, boldFont, { horizontal: "left" }, standardBorders, ); MISWorksheet.getCell(`B${index}`).border = standardBorders; const indirectExpenseAmtRow = MISWorksheet.getCell(`C${index}`); indirectExpenseAmtRow.value = indirectExpenseAmt; styleCell( indirectExpenseAmtRow, regularFont, { horizontal: "right" }, standardBorders, ); indirectExpenseAmtRow.numFmt = "#,##0"; index += 1; // --- 8. Total Expense (Row 26) --- MISWorksheet.getCell(`B${index}`).value = "Total Expense"; styleCell( MISWorksheet.getCell(`B${index}`), boldFont, { horizontal: "left" }, standardBorders, ); MISWorksheet.getCell(`A${index}`).border = standardBorders; const totalExpenseAmtCell = MISWorksheet.getCell(`C${index}`); totalExpenseAmtCell.value = expenseAmt; styleCell( totalExpenseAmtCell, boldFont, { horizontal: "right" }, { ...standardBorders, bottom: doubleBorder }, ); totalExpenseAmtCell.numFmt = "#,##0"; index += 1; // --- 9. Income Section Headers (Row 28) --- index += 1; const incomeHeaders = [ [`A${index}`, "Income type"], [`B${index}`, "Income"], [`C${index}`, "Amount"], ]; incomeHeaders.forEach(([cellRef, value]) => { const cell = MISWorksheet.getCell(cellRef); cell.value = value; styleCell( cell, boldFont, { horizontal: cellRef === `C${index}` ? "right" : "left" }, standardBorders, ); }); index += 1; // --- 10. Direct Income rows (Rows 29 to 34) --- // Direct Income Row Spanning // const directIncomeStartIndex = index const directIncomeLength = directTransactions ? directTransactions.length : 0; if (directTransactions && directTransactions.length > 0) MISWorksheet.mergeCells(`A${index}:A${index + directIncomeLength - 1}`); MISWorksheet.getCell(`A${index}`).value = "Direct Income"; styleCell(MISWorksheet.getCell(`A${index}`), boldFont, { vertical: "top" }); if (directTransactions && directTransactions.length > 0) { for (let transaction of directTransactions) { const dueName = await getDueName(transaction.transactionFor); const rowNum = index; // Transaction Due Name const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = dueName; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = transaction.amount; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to Direct Income outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } } else { const rowNum = index; const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = "-"; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = "-"; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to Direct Income outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } // --- Merging the column // MISWorksheet.mergeCells(`A${directIncomeStartIndex}:A${index-1}`); // --- Total of direct Income (Row 25) --- const directIncomeCell = MISWorksheet.getCell(`A${index}`); directIncomeCell.value = "Total of Direct Income"; styleCell( directIncomeCell, boldFont, { horizontal: "left" }, standardBorders, ); MISWorksheet.getCell(`B${index}`).border = standardBorders; const directIncomeAmtCell = MISWorksheet.getCell(`C${index}`); directIncomeAmtCell.value = directTransactionAmt; styleCell( directIncomeAmtCell, regularFont, { horizontal: "right" }, standardBorders, ); directIncomeAmtCell.numFmt = "#,##0"; index += 1; // --- 10.Indirect Income rows (Rows 29 to 34) --- // InDirect Income Row Spanning // const indirectIncomeStartIndex = index; const indirectIncomeLength = indirectTransactions ? indirectTransactions.length : 0; if (indirectTransactions && indirectTransactions.length > 0) MISWorksheet.mergeCells(`A${index}:A${index + indirectIncomeLength - 1}`); // MISWorksheet.mergeCells('A29:A30'); MISWorksheet.getCell(`A${index}`).value = "InDirect Income"; styleCell(MISWorksheet.getCell(`A${index}`), boldFont, { vertical: "top" }); if (indirectTransactions && indirectTransactions.length > 0) { for (let transaction of indirectTransactions) { const dueName = await getDueName(transaction.transactionFor); const rowNum = index; // Transaction Due Name const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = dueName; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = transaction.amount; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to InDirect Income outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } } else { const rowNum = index; const cellB = MISWorksheet.getCell(`B${rowNum}`); cellB.value = "-"; styleCell(cellB, regularFont, { horizontal: "left" }); // Amount const cellC = MISWorksheet.getCell(`C${rowNum}`); cellC.value = "-"; styleCell(cellC, regularFont, { horizontal: "right" }); cellC.numFmt = "#,##0"; // --- Apply borders to InDirect Income outer boundary block manually due to merging quirks --- MISWorksheet.getCell(`A${index}`).border = { left: thinBorder, right: thinBorder, }; MISWorksheet.getCell(`B${index}`).border = { right: thinBorder }; MISWorksheet.getCell(`C${index}`).border = { right: thinBorder }; index += 1; } // --- Merging the column // MISWorksheet.mergeCells(`A${indirectIncomeStartIndex}:A${index-1}`); // --- 7. Total of Indirect Expenses (Row 25) --- const indirectIncomeCell = MISWorksheet.getCell(`A${index}`); indirectIncomeCell.value = "Total of InDirect Income"; styleCell( indirectIncomeCell, boldFont, { horizontal: "left" }, standardBorders, ); MISWorksheet.getCell(`B${index}`).border = standardBorders; const indirectIncomeAmtCell = MISWorksheet.getCell(`C${index}`); indirectIncomeAmtCell.value = indirectTransactionAmt; styleCell( indirectIncomeAmtCell, regularFont, { horizontal: "right" }, standardBorders, ); indirectIncomeAmtCell.numFmt = "#,##0"; index += 1; // --- 11. Total Collection (Row 35) --- MISWorksheet.getCell(`B${index}`).value = "Total Collection"; styleCell( MISWorksheet.getCell(`B${index}`), boldFont, { horizontal: "left" }, standardBorders, ); MISWorksheet.getCell(`A${index}`).border = standardBorders; const totalIncomeAmtCell = MISWorksheet.getCell(`C${index}`); totalIncomeAmtCell.value = transactionAmt; styleCell( totalIncomeAmtCell, boldFont, { horizontal: "right" }, { ...standardBorders, bottom: doubleBorder }, ); totalIncomeAmtCell.numFmt = "#,##0"; index += 1; // --- 12. Bottom Financial Calculations (Rows 37 to 43) --- index += 1; // const bottomCalculations = [ // { refLabel: 'A37', refVal: 'B37', label: 'Gross profit', formula: 'C35-C14' }, // { refLabel: 'A38', refVal: 'B38', label: 'Net profit', formula: 'B37-C25' }, // { refLabel: 'A39', refVal: 'B39', label: 'Interest', val: 0 }, // { refLabel: 'A40', refVal: 'B40', label: 'Depreciation on Assets', val: 1122 }, // { refLabel: 'A41', refVal: 'B41', label: 'Profit After Interest and Depreciation', formula: 'B38-B39-B40' }, // { refLabel: 'A42', refVal: 'B42', label: 'Tax @ 30%', formula: 'B41*0.3' }, // { refLabel: 'A43', refVal: 'B43', label: 'Net profit - After Tax', formula: 'B41-B42' } // ]; //Gross Profit const grossProfitCell = MISWorksheet.getCell(`A${index}`); grossProfitCell.value = "Gross Profit"; styleCell(grossProfitCell, boldFont, { horizontal: "left" }); const grossProfitAmtCell = MISWorksheet.getCell(`B${index}`); grossProfitAmtCell.value = Number(transactionAmt) - Number(directExpenseAmt); styleCell(grossProfitAmtCell, boldFont, { horizontal: "right" }); grossProfitAmtCell.numFmt = "#,##0"; index += 1; //Net Profit const netProfitCell = MISWorksheet.getCell(`A${index}`); netProfitCell.value = "Net Profit"; styleCell(netProfitCell, boldFont, { horizontal: "left" }); const netProfitAmtCell = MISWorksheet.getCell(`B${index}`); netProfitAmtCell.value = Number(transactionAmt) - Number(expenseAmt); styleCell(netProfitAmtCell, boldFont, { horizontal: "right" }); netProfitAmtCell.numFmt = "#,##0"; index += 1; // Tax 30% let taxAmt = (Number(transactionAmt) - Number(expenseAmt)) * 0.3; const taxCell = MISWorksheet.getCell(`A${index}`); taxCell.value = "Tax@30%"; styleCell(taxCell, boldFont, { horizontal: "left" }); const taxAmtCell = MISWorksheet.getCell(`B${index}`); taxAmtCell.value = taxAmt > 0 ? taxAmt : 0; styleCell(taxAmtCell, boldFont, { horizontal: "right" }); taxAmtCell.numFmt = "#,##0"; index += 1; // Net Profit After Tax const netProfitAfterTaxCell = MISWorksheet.getCell(`A${index}`); netProfitAfterTaxCell.value = "Net Profit/Loss After Tax"; styleCell(netProfitAfterTaxCell, boldFont, { horizontal: "left" }); const netProfitAfterTaxAmtCell = MISWorksheet.getCell(`B${index}`); netProfitAfterTaxAmtCell.value = taxAmt > 0 ? Number(transactionAmt) - Number(expenseAmt) - Number(taxAmt) : Number(transactionAmt) - Number(expenseAmt); styleCell(netProfitAfterTaxAmtCell, boldFont, { horizontal: "right" }); netProfitAfterTaxAmtCell.numFmt = "#,##0"; index += 2; // --- 13. Footer Note (Row 45) --- const footerCell = MISWorksheet.getCell(`A${index}`); footerCell.value = "Note: Above MIS has been made as per the Data Extracted from the kipinn App"; styleCell(footerCell, boldFont, { horizontal: "left" }); // ---------------MIS WorkSheet Complete------------------ // --------------Expense WorkSheet----------------------> let expensesForWorksheet = []; let isFlatExist = false; if (Array.isArray(propId) && propId.length > 0) { expensesForWorksheet = await expenseDB.getByPropIdandDateRangeDueDate({ propId, startDate, endDate, }); const flats = await flatDB.getByPropIds({ propIds: propId, }); if (flats && flats.length > 0) { isFlatExist = true; } } else { expensesForWorksheet = await expenseDB.getByClientIdandDateRangeForReport( { clientId, startDate, endDate, }, ); const flats = await flatDB.getByClientId({ clientId, }); if (flats && flats.length > 0) { isFlatExist = true; } } const expenseWorksheet = workbook.addWorksheet("Expense Report", { views: [{ state: "frozen", xSplit: 1, ySplit: 1 }], }); if (329 === clientId) { expenseWorksheet.columns = [ { header: "Property Name", key: "property", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Expense Amount", key: "amount", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Attached Document", key: "supportDoc", width: 20 }, ]; } else { expenseWorksheet.columns = [ { header: "Paid By", key: "paidByName", width: 20 }, { header: "Paid To", key: "paidToName", width: 20 }, { header: "Property", key: "property", width: 20 }, ...(isFlatExist ? [{ header: "Flat", key: "flatName", width: 20 }] : []), //Flat will be added if flat is there { header: "Amount", key: "amount", width: 20 }, { header: "Payment Method", key: "paymentMethod", width: 20 }, { header: "Expense Category", key: "expenseCategory", width: 20 }, { header: "Expense Type", key: "expenseType", width: 20 }, { header: "Expense Nature", key: "expenseNature", width: 20 }, { header: "Repetition Type", key: "repetitionType", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Paid Date", key: "paidDate", width: 20 }, { header: "Description", key: "description", width: 40 }, { header: "Attached Document", key: "supportDoc", width: 20 }, ]; } expenseWorksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: "FFFFFFFF" }, }; cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF0078BD" }, }; cell.alignment = { vertical: "middle", horizontal: "center" }; }); if (expensesForWorksheet.length > 0) { for (const expense of expensesForWorksheet) { const property = await propertyDB.getById({ id: expense.propId }); let propertyName = `${property?.name}`; if (!property) { propertyName = "N/A"; } let flatName = "N/A"; if (expense.flatId) { const flat = await flatDB.getById({ id: expense.flatId }); if (flat) { flatName = `${flat?.name}`; } } const paymentMethod = expense.paymentMethod === CONSTANTS.TRANSACTION_MODES.OFFLINE ? "Cash" : expense.paymentMethod === CONSTANTS.TRANSACTION_MODES.CARD ? "Card" : expense.paymentMethod === CONSTANTS.TRANSACTION_MODES.UPI ? "UPI" : "Net Banking"; const repetitionType = expense.repetitionType === 1 ? "One Time" : "Monthly"; const row = expenseWorksheet.addRow({ paidByName: expense?.paidByName || "", paidToName: expense?.paidToName || "", property: propertyName || "", flatName: flatName, amount: expense?.amount || 0, paymentMethod: paymentMethod, expenseCategory: expense.expenseCategoryName || "", expenseType: expense.expenseTypeName || "", expenseNature: expense.expenseNature && Number(expense.expenseNature) === CONSTANTS.EXPENSE_NATURE.OPERATING ? "Operating Expense" : "Asset Investment", repetitionType: repetitionType, dueDate: expense?.dueDate ? moment(expense?.dueDate).format("DD/MM/YYYY") : "", paidDate: expense?.paidDate ? moment(expense?.paidDate).format("DD/MM/YYYY") : "", description: expense.description || "", supportDoc: expense?.supportDoc !== null ? "Link" : null, }); if ( expense?.supportDoc && expense?.supportDoc !== null && expense?.supportDoc !== "" ) { const lastCell = row.getCell("supportDoc"); lastCell.value = { text: "Link", hyperlink: expense?.supportDoc, }; lastCell.font = { color: { argb: "FF0000FF" }, underline: true }; } row.eachCell((cell) => { cell.alignment = { vertical: "middle", horizontal: "center", wrapText: true, }; }); row.height = 30; } } // ------------------Expense WorkSheet End --------------------> // -------------------Collection WorkSheet --------------------> let transactionsForWorkSheet = []; let transAmount = 0; let isGstEnabled = 0; let reportForMultipleProp = false; if (Array.isArray(propId) && propId.length > 0) { reportForMultipleProp = true; transactionsForWorkSheet = await transactionDB.getByPropIdsandDateRange({ propId, startDate, endDate, }); const property = await propertyDB.isGstEnabledForPropIds({ propId, }); isGstEnabled = property.isGstEnabled; } else { transactionsForWorkSheet = await transactionDB.getByClientIdandDateRangeForWeb({ clientId, startDate, endDate, }); isGstEnabled = await propertyDB.isGstEnabledForClient({ clientId, }); } let gatewayCharges = 0; let getGwCharges = await clientConfigDB.getClientConfig({ clientId, provider: CONSTANTS.CLIENT_CONFIG_PROVIDER.EASEBUZZ, type: CONSTANTS.CLIENT_CONFIG_TYPE.PER_TRANS_CHARGE_EASEBUZZ, }); if (getGwCharges) { gatewayCharges = Number(getGwCharges.value) || 0; } const collectionWorkSheet = workbook.addWorksheet("Collection Report", { views: [{ state: "frozen", xSplit: 1, ySplit: 1 }], }); collectionWorkSheet.columns = [ { header: "Room No.", key: "roomNum", width: 20 }, { header: "Floor/Flat", key: "floorOrFlat", width: 20 }, { header: "Collection Date", key: "collectionDate", width: 20 }, { header: "Name", key: "name", width: 20 }, { header: "Sharing Type", key: "sharingType", width: 20 }, { header: "Room Type", key: "roomType", width: 20 }, { header: "Mobile", key: "mobile", width: 20 }, { header: "Property", key: "property", width: 30 }, { header: "Payment Mode", key: "paymentMode", width: 20 }, { header: "Transaction Amount", key: "amount", width: 20 }, { header: "Discount", key: "discount", width: 20 }, // { header: "GST Amount", key: "gstCharges", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST Amount", key: "gstCharges", width: 20 }] : []), // { header: "Holded By Kipinn", key: "holdAmount", width: 20 }, ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway Charges", key: "transactionCharges", width: 20 }] : []), ...(Number(gatewayCharges) > 1 ? [{ header: "Gateway GST", key: "gstChargesToGateway", width: 20 }] : []), { header: "EMI", key: "holdAmount", width: 20 }, { header: "Amount Settled", key: "transAmount", width: 20 }, { header: "Payment Gateway", key: "paymentGateway", width: 20 }, { header: "Due Date", key: "dueDate", width: 20 }, { header: "Payment For", key: "transactionFor", width: 20 }, { header: "Description", key: "description", width: 50, style: { alignment: { wrapText: true, vertical: "top" } }, }, { header: "Credit Account No.", key: "paymentAccountNo", width: 20 }, { header: "Credit Account Holder Name", key: "paymentAccountName", width: 20, }, { header: "Transaction Id", key: "transactionId", width: 20 }, { header: "Settlement Status", key: "settlementStatus", width: 20 }, { header: "Settled On", key: "settledOn", width: 20 }, { header: "UTR No.", key: "utrNumber", width: 20 }, // { header: "GST No.", key: "gstNumber", width: 20 }, // { header: "Invoice No.", key: "invoiceNumber", width: 20 }, ...(Number(isGstEnabled) === 1 ? [{ header: "GST No.", key: "gstNumber", width: 20 }] : []), ...(Number(isGstEnabled) === 1 ? [{ header: "Invoice No.", key: "invoiceNumber", width: 20 }] : []), { header: "Bank Refference No.", key: "bankReffNumber", width: 20 }, { header: "Recorded By", key: "recordedBy", width: 20 }, ]; collectionWorkSheet.getRow(1).eachCell((cell) => { cell.font = { bold: true, color: { argb: "FFFFFFFF" }, }; cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF0078BD" }, }; cell.alignment = { vertical: "middle", horizontal: "center" }; }); if (transactionsForWorkSheet.length > 0) { for (const txn of transactionsForWorkSheet) { transAmount = Number(txn.amount) - Number(txn.holdAmount) + Number(txn.gstCharges); let paymentMode = ""; if (txn.mode === CONSTANTS.TRANSACTION_MODES.OFFLINE) { paymentMode = "Cash"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.CARD) { paymentMode = "Card"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.UPI) { paymentMode = "UPI"; } else if (txn.mode === CONSTANTS.TRANSACTION_MODES.NET_BANKING) { paymentMode = "Net Banking"; } else if ( txn.mode === CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY ) { paymentMode = "Sec Adjusted"; } const property = `${txn.propName}`; // const mobile = txn.tenantMobile.slice(0, -4) + "XXXX"; const mobile = txn.tenantMobile; let settlementStatus = "-"; if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.PENDING) { settlementStatus = "Pending"; } else if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.INITITATED) { settlementStatus = "Initiated"; } else if (txn.settleStatus === CONSTANTS.PAYOUT_STATUS.COMPLETED) { settlementStatus = "Completed"; } let paymentGateway = "None"; if ( txn.gateway === CONSTANTS.PAYMENT_GATEWAY.PAYU && txn.recordedBy === "Kipinn App" ) { paymentGateway = "PayU"; } else if ( txn.gateway === CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ && txn.recordedBy === "Kipinn App" ) { paymentGateway = "Easebuzz"; } else if ( txn.gateway === CONSTANTS.PAYMENT_GATEWAY.CASHFREE && txn.recordedBy === "Kipinn App" ) { paymentGateway = "Cashfree"; } const bedCount = await bedDB.getCountsByRoomId({ id: txn.roomId, }); const totalBeds = bedCount.totalBeds; const room = await roomDB.getById({ id: txn.roomId, }); const propertyData = await propertyDB.getById({ id: room.propId }); let flatName = ""; if (propertyData.type === CONSTANTS.PROPERTY_TYPE.FLAT) { const { name } = await flatDB.getById({ id: room.flatId }); flatName = name; } else { flatName = room.floor === "G" ? "Ground" : room.floor; } const roomOption = await roomOptionDB.getById({ id: room.roomOptionId, }); let transactionForName = getDueName(txn.transactionFor); let transactionCharges = 0; let gstChargesToGateway = 0; if ( Number(gatewayCharges) > 1 && txn.gateway === CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ && txn.recordedBy === "Kipinn App" ) { transactionCharges = Number(gatewayCharges); gstChargesToGateway = Number( ((gatewayCharges * 18) / 100).toFixed(2), ); transAmount = transAmount - transactionCharges - gstChargesToGateway; } let rowData: any = { roomNum: txn.roomNum || "", floorOrFlat: flatName || "", name: txn.tenantName || "", sharingType: `${totalBeds} Sharing`, // roomType: roomOption?.name || "", // mobile: mobile.toString() || "", roomType: roomOption.name ? roomOption?.name : "", mobile: mobile ? mobile.toString() : "", collectionDate: moment(txn.collectionDate).format("DD/MM/YYYY") || "", dueDate: txn?.dueDate ? moment(txn?.dueDate).format("DD/MM/YYYY") : "", paymentMode: paymentMode || "", amount: txn.amount || 0, discount: txn.discount || 0, // gstCharges: txn.gstCharges || 0, holdAmount: txn.holdAmount || 0, transAmount: transAmount || 0, transactionFor: transactionForName || 0, paymentAccountNo: txn.paymentAccountNo || "", paymentAccountName: txn.paymentAccountName || "", property: property || "", description: txn.name || "", // transactionId: txn.gId.toString() || "", transactionId: txn.gId ? txn.gId.toString() : "", settlementStatus, settledOn: txn?.settledOn ? moment(txn?.settledOn).format("DD/MM/YYYY") : "-", utrNumber: txn.utrNo || "-", // gstNumber : txn.gstNo || "-", // invoiceNumber : txn.invoiceNo || "-", bankReffNumber: txn.bankRefNum || "-", recordedBy: txn.recordedBy || "", paymentGateway: paymentGateway || "", transactionCharges: transactionCharges, gstChargesToGateway: gstChargesToGateway, }; if (Number(isGstEnabled) === 1) { rowData.gstCharges = txn.gstCharges || 0; rowData.gstNumber = txn.gstNo || "-"; rowData.invoiceNumber = txn.invoiceNo || "-"; } const row = collectionWorkSheet.addRow(rowData); row.eachCell((cell: any) => { cell.alignment = { vertical: "middle", horizontal: "center" }; }); } } // Save to file const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = `MIS_Reports`; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/`, ); if (!fs.existsSync(folderPath)) { fs.mkdirSync(folderPath, { recursive: true }); } const fileName = `profit_loss_report_${moment().format("HHmmss")}.xlsx`; const filePath = `${folderPath}/${fileName}`; // fs.writeFileSync(filePath, Buffer.from(buffer)); fs.writeFileSync(filePath, buffer as NodeJS.ArrayBufferView); const url = `${urlBase}/${fileName}`; return { url, fileName }; } else { return false; } } catch (error: any) { log.info(`[${C}], [${F}], Error: ${error?.message || error}`); return false; } }; export const tenantAttendanceReport = async ( fileType: number, clientId: number, propId: any, startDate: string, endDate: string ) => { const C = "Report Controller"; const F = "tenantAttendanceReport"; try { let attendanceData = []; // ============================================================ // GET ATTENDANCE DATA // ============================================================ if (Array.isArray(propId) && propId.length > 0) { attendanceData = await occupancyDB.getGetTenantAttendancesByPropId({ propId, startDate, endDate }); } else { attendanceData = await occupancyDB.getGetTenantAttendances({ clientId, startDate, endDate }); } if (fileType !== 1) { return false; } if (!attendanceData || attendanceData.length === 0) { return false; } // ============================================================ // ATTENDANCE CONSTANTS // ============================================================ const ATTENDANCE = { ABSENT: 0, PRESENT: 1, LATE_NIGHT: 2, OUT_OF_STATION: 3, NIGHT_OUT: 4 }; const attendanceShortText: Record = { 0: "A", 1: "P", 2: "LN", 3: "OS", 4: "NO" }; // ============================================================ // DATE CONFIGURATION // ============================================================ const start = moment(startDate); const end = moment(endDate); const year = start.year(); const monthNumber = start.month() + 1; const month = String(monthNumber).padStart(2, "0"); const monthName = start.format("MMM"); const totalDays = end.diff(start, "days") + 1; // ============================================================ // PREPARE TENANT DATA // ============================================================ const tenantMap = new Map(); attendanceData.forEach((item: any) => { if (!tenantMap.has(item.tenantId)) { let roomNum = `${item.roomNum}` if(item.propType === CONSTANTS.PROPERTY_TYPE.PG) { roomNum = `Floor ${item.floor}, ${item.roomNum}` } else { roomNum = `Flat ${item.flat}, ${item.roomNum}` } tenantMap.set(item.tenantId, { propertyId: item.propId, propertyName: item.propName, tenantId: item.tenantId, tenantName: item.tenantName, room: roomNum, attendance: {} }); } // IMPORTANT: // NULL attendanceDate means attendance was NOT MARKED. // Do NOT add it to attendance object. if ( item.attendanceDate !== null && item.attendanceDate !== undefined ) { const attendanceDate = moment(item.attendanceDate) .format("YYYY-MM-DD"); tenantMap .get(item.tenantId) .attendance[attendanceDate] = item.attendance; } }); const tenants = Array.from(tenantMap.values()); // ============================================================ // GROUP TENANTS BY PROPERTY // ============================================================ const propertyTenantMap = new Map(); tenants.forEach(tenant => { if (!propertyTenantMap.has(tenant.propertyId)) { propertyTenantMap.set( tenant.propertyId, [] ); } propertyTenantMap .get(tenant.propertyId)! .push(tenant); }); const properties = Array.from( propertyTenantMap.entries() ).map( ([id, propertyTenants]) => ({ id, name: propertyTenants[0].propertyName }) ); // ============================================================ // WORKBOOK // ============================================================ const workbook = new ExcelJS.Workbook(); // ============================================================ // STYLES // ============================================================ const borderStyle = { top: { style: "thin" as const }, left: { style: "thin" as const }, bottom: { style: "thin" as const }, right: { style: "thin" as const } }; const centerAlignment = { horizontal: "center" as const, vertical: "middle" as const }; const applyHeaderStyle = ( row: ExcelJS.Row ) => { row.eachCell(cell => { cell.font = { bold: true, color: { argb: "FFFFFF" } }; cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "4472C4" } }; cell.alignment = centerAlignment; cell.border = borderStyle; }); }; // ============================================================ // 1. MONTHLY SUMMARY // ============================================================ const monthlySheet = workbook.addWorksheet( "Monthly Summary" ); monthlySheet.mergeCells( "A1:J1" ); monthlySheet.getCell( "A1" ).value = "Tenant Attendance Report - Monthly Summary"; monthlySheet.getCell( "A1" ).font = { bold: true, size: 16 }; monthlySheet.getCell( "A1" ).alignment = centerAlignment; monthlySheet.getCell( "A2" ).value = "Period"; monthlySheet.getCell( "B2" ).value = `${start.format("DD MMM YYYY")} - ${end.format("DD MMM YYYY")}`; // monthlySheet.getCell( // "D2" // ).value = "Properties"; // monthlySheet.getCell( // "E2" // ).value = // properties // .map(p => p.name) // .join(", "); monthlySheet.addRow([]); const monthlyHeaders = [ "Property", "Tenant", "Room", "Present", "Absent", "Late Night", "Night Out", "Out of Station", "Not Marked", "Attendance %" ]; const monthlyHeaderRow = monthlySheet.addRow( monthlyHeaders ); applyHeaderStyle( monthlyHeaderRow ); // ============================================================ // MONTHLY TOTAL VARIABLES // ============================================================ let totalPresent = 0; let totalAbsent = 0; let totalLateNight = 0; let totalNightOut = 0; let totalOutOfStation = 0; let totalNotMarked = 0; // ============================================================ // MONTHLY TENANT DATA // ============================================================ tenants.forEach(tenant => { let present = 0; let absent = 0; let lateNight = 0; let nightOut = 0; let outOfStation = 0; Object.values( tenant.attendance ).forEach( (status: any) => { if ( status === null || status === undefined ) { return; } switch ( Number(status) ) { case ATTENDANCE.PRESENT: present++; break; case ATTENDANCE.ABSENT: absent++; break; case ATTENDANCE.LATE_NIGHT: lateNight++; break; case ATTENDANCE.NIGHT_OUT: nightOut++; break; case ATTENDANCE.OUT_OF_STATION: outOfStation++; break; } } ); // ========================================================== // MARKED DAYS // ========================================================== const markedDays = present + absent + lateNight + nightOut + outOfStation; // ========================================================== // NOT MARKED // ========================================================== const notMarked = Math.max( 0, totalDays - markedDays ); // ========================================================== // ATTENDANCE % // // Present + Late Night = attended // Not Marked excluded // ========================================================== const attended = present + lateNight; const attendancePercentage = markedDays > 0 ? attended / markedDays : 0; // ========================================================== // ACCUMULATE TOTALS // ========================================================== totalPresent += present; totalAbsent += absent; totalLateNight += lateNight; totalNightOut += nightOut; totalOutOfStation += outOfStation; totalNotMarked += notMarked; // ========================================================== // ADD TENANT ROW // ========================================================== const row = monthlySheet.addRow([ tenant.propertyName, tenant.tenantName, tenant.room, present, absent, lateNight, nightOut, outOfStation, notMarked, attendancePercentage ]); row.eachCell(cell => { cell.border = borderStyle; }); row.getCell( 10 ).numFmt = "0.00%"; for ( let i = 4; i <= 10; i++ ) { row.getCell(i).alignment = centerAlignment; } }); // ============================================================ // MONTHLY TOTAL // ============================================================ const totalMarkedDays = totalPresent + totalAbsent + totalLateNight + totalNightOut + totalOutOfStation; const totalAttended = totalPresent + totalLateNight; const totalAttendancePercentage = totalMarkedDays > 0 ? totalAttended / totalMarkedDays : 0; const totalRow = monthlySheet.addRow([ "TOTAL", "", "", totalPresent, totalAbsent, totalLateNight, totalNightOut, totalOutOfStation, totalNotMarked, totalAttendancePercentage ]); totalRow.eachCell( cell => { cell.font = { bold: true }; cell.border = borderStyle; } ); totalRow.getCell( 10 ).numFmt = "0.00%"; for ( let i = 4; i <= 10; i++ ) { totalRow.getCell(i).alignment = centerAlignment; } // ============================================================ // MONTHLY SHEET FORMATTING // ============================================================ monthlySheet.columns = [ { width: 18 }, { width: 22 }, { width: 12 }, { width: 12 }, { width: 12 }, { width: 14 }, { width: 12 }, { width: 18 }, { width: 14 }, { width: 16 } ]; monthlySheet.views = [ { state: "frozen", ySplit: 4 } ]; monthlySheet.autoFilter = { from: "A4", to: `J${monthlySheet.rowCount - 1}` }; // ============================================================ // 2. DAILY ATTENDANCE // ============================================================ const dailySheet = workbook.addWorksheet( "Daily Attendance" ); const dailyLastColumn = 3 + totalDays; const dailyLastColumnLetter = dailySheet.getColumn( dailyLastColumn ).letter; dailySheet.mergeCells( `A1:${dailyLastColumnLetter}1` ); dailySheet.getCell( "A1" ).value = "Tenant Attendance Report - Daily Attendance"; dailySheet.getCell( "A1" ).font = { bold: true, size: 16 }; dailySheet.getCell( "A1" ).alignment = centerAlignment; dailySheet.getCell( "A2" ).value = "Period"; dailySheet.getCell( "B2" ).value = `${start.format("DD MMM YYYY")} - ${end.format("DD MMM YYYY")}`; // dailySheet.getCell( // "D2" // ).value = "Properties"; // dailySheet.getCell( // "E2" // ).value = // properties // .map(p => p.name) // .join(", "); dailySheet.addRow([]); const dailyHeaders = [ "Property", "Tenant", "Room" ]; // ============================================================ // DAILY HEADERS // ============================================================ for ( let day = 0; day < totalDays; day++ ) { const currentDate = start.clone().add( day, "days" ); dailyHeaders.push( currentDate.format( "DD MMM" ) ); } const dailyHeaderRow = dailySheet.addRow( dailyHeaders ); applyHeaderStyle( dailyHeaderRow ); // ============================================================ // DAILY TENANT ATTENDANCE // ============================================================ tenants.forEach( tenant => { const rowData: any[] = [ tenant.propertyName, tenant.tenantName, tenant.room ]; for ( let day = 0; day < totalDays; day++ ) { const currentDate = start.clone().add( day, "days" ); const date = currentDate.format( "YYYY-MM-DD" ); const status = tenant.attendance[ date ]; // No record = blank rowData.push( status !== undefined && status !== null ? attendanceShortText[ Number(status) ] || "" : "NM" ); } const row = dailySheet.addRow( rowData ); row.eachCell( ( cell, columnNumber ) => { cell.border = borderStyle; if ( columnNumber >= 4 ) { cell.alignment = centerAlignment; cell.font = { bold: true }; } } ); } ); dailySheet.getColumn( 1 ).width = 18; dailySheet.getColumn( 2 ).width = 22; dailySheet.getColumn( 3 ).width = 12; for ( let column = 4; column <= dailyLastColumn; column++ ) { dailySheet.getColumn( column ).width = 10; } dailySheet.views = [ { state: "frozen", xSplit: 3, ySplit: 4 } ]; dailySheet.autoFilter = { from: "A4", to: `${dailyLastColumnLetter}${4 + tenants.length}` }; // ============================================================ // 3. DAILY SUMMARY // ============================================================ const dailySummarySheet = workbook.addWorksheet( "Daily Summary" ); dailySummarySheet.mergeCells( "A1:I1" ); dailySummarySheet.getCell( "A1" ).value = "Tenant Attendance Report - Daily Summary"; dailySummarySheet.getCell( "A1" ).font = { bold: true, size: 16 }; dailySummarySheet.getCell( "A1" ).alignment = centerAlignment; dailySummarySheet.getCell( "A2" ).value = "Period"; dailySummarySheet.getCell( "B2" ).value = `${start.format("DD MMM YYYY")} - ${end.format("DD MMM YYYY")}`; // dailySummarySheet.getCell( // "D2" // ).value = "Properties"; // dailySummarySheet.getCell( // "E2" // ).value = // properties // .map(p => p.name) // .join(", "); dailySummarySheet.addRow([]); const dailySummaryHeaders = [ "Date", "Property", "Total Tenants", "Present", "Absent", "Late Night", "Night Out", "Out of Station", "Not Marked" ]; const dailySummaryHeaderRow = dailySummarySheet.addRow( dailySummaryHeaders ); applyHeaderStyle( dailySummaryHeaderRow ); // ============================================================ // DAILY SUMMARY // ============================================================ for ( let day = 0; day < totalDays; day++ ) { const currentDate = start.clone().add( day, "days" ); const date = currentDate.format( "YYYY-MM-DD" ); const displayDate = currentDate.format( "DD MMM YYYY" ); for ( const property of properties ) { const propertyTenants = propertyTenantMap.get( property.id ) || []; let present = 0; let absent = 0; let lateNight = 0; let nightOut = 0; let outOfStation = 0; let notMarked = 0; propertyTenants.forEach( tenant => { const status = tenant.attendance[ date ]; if ( status === undefined || status === null ) { notMarked++; return; } switch ( Number(status) ) { case ATTENDANCE.PRESENT: present++; break; case ATTENDANCE.ABSENT: absent++; break; case ATTENDANCE.LATE_NIGHT: lateNight++; break; case ATTENDANCE.NIGHT_OUT: nightOut++; break; case ATTENDANCE.OUT_OF_STATION: outOfStation++; break; } } ); const row = dailySummarySheet.addRow([ displayDate, property.name, propertyTenants.length, present, absent, lateNight, nightOut, outOfStation, notMarked ]); row.eachCell( ( cell, columnNumber ) => { cell.border = borderStyle; if ( columnNumber >= 3 ) { cell.alignment = centerAlignment; } } ); } } // ============================================================ // DAILY SUMMARY TOTAL // ============================================================ let dailyTotalTenants = 0; let dailyTotalPresent = 0; let dailyTotalAbsent = 0; let dailyTotalLateNight = 0; let dailyTotalNightOut = 0; let dailyTotalOutOfStation = 0; let dailyTotalNotMarked = 0; // Calculate directly from tenant data for ( let day = 0; day < totalDays; day++ ) { const currentDate = start.clone().add( day, "days" ); const date = currentDate.format( "YYYY-MM-DD" ); properties.forEach( property => { const propertyTenants = propertyTenantMap.get( property.id ) || []; dailyTotalTenants += propertyTenants.length; propertyTenants.forEach( tenant => { const status = tenant.attendance[ date ]; if ( status === undefined || status === null ) { dailyTotalNotMarked++; return; } switch ( Number(status) ) { case ATTENDANCE.PRESENT: dailyTotalPresent++; break; case ATTENDANCE.ABSENT: dailyTotalAbsent++; break; case ATTENDANCE.LATE_NIGHT: dailyTotalLateNight++; break; case ATTENDANCE.NIGHT_OUT: dailyTotalNightOut++; break; case ATTENDANCE.OUT_OF_STATION: dailyTotalOutOfStation++; break; } } ); } ); } const dailyTotalRow = dailySummarySheet.addRow([ "TOTAL", "", dailyTotalTenants, dailyTotalPresent, dailyTotalAbsent, dailyTotalLateNight, dailyTotalNightOut, dailyTotalOutOfStation, dailyTotalNotMarked ]); dailyTotalRow.eachCell( cell => { cell.font = { bold: true }; cell.border = borderStyle; } ); for ( let i = 3; i <= 9; i++ ) { dailyTotalRow.getCell(i) .alignment = centerAlignment; } // ============================================================ // DAILY SUMMARY FORMATTING // ============================================================ dailySummarySheet.columns = [ { width: 16 }, { width: 20 }, { width: 16 }, { width: 12 }, { width: 12 }, { width: 14 }, { width: 12 }, { width: 18 }, { width: 14 } ]; dailySummarySheet.views = [ { state: "frozen", ySplit: 4 } ]; dailySummarySheet.autoFilter = { from: "A4", to: `I${dailySummarySheet.rowCount - 1}` }; // ============================================================ // 4. LEGEND // ============================================================ const legendSheet = workbook.addWorksheet( "Legend" ); legendSheet.mergeCells( "A1:C1" ); legendSheet.getCell( "A1" ).value = "Attendance Status Legend"; legendSheet.getCell( "A1" ).font = { bold: true, size: 16 }; legendSheet.getCell( "A1" ).alignment = centerAlignment; const legendHeaderRow = legendSheet.addRow([ "Code", "Status", "Description" ]); applyHeaderStyle( legendHeaderRow ); const legendData = [ [ "P", "Present", "Tenant was present" ], [ "A", "Absent", "Tenant was absent" ], [ "LN", "Late Night", "Tenant returned late at night" ], [ "NO", "Night Out", "Tenant was on night out" ], [ "OS", "Out of Station", "Tenant was out of station" ], [ "NM", "Not Marked", "Attendance was not marked" ] ]; legendData.forEach( item => { const row = legendSheet.addRow( item ); row.eachCell( cell => { cell.border = borderStyle; } ); } ); legendSheet.columns = [ { width: 12 }, { width: 20 }, { width: 40 } ]; // ============================================================ // GENERATE EXCEL // ============================================================ const raw = await workbook.xlsx.writeBuffer(); const buffer = Buffer.isBuffer(raw) ? raw : Buffer.from(raw); const folderName = "attendance"; const folderPath = `uploads/reports/clientId_${clientId}/${folderName}`; const urlBase = folderPath.replace( "uploads/", `${process.env.UPLOAD_PATH}/` ); if ( !fs.existsSync(folderPath) ) { fs.mkdirSync( folderPath, { recursive: true } ); } const fileName = `tenant_attendance_report_${moment().format( "HHmmss" )}_${startDate}_${endDate}.xlsx`; const filePath = `${folderPath}/${fileName}`; fs.writeFileSync( filePath, buffer as NodeJS.ArrayBufferView ); const url = `${urlBase}/${fileName}`; return { url, fileName }; } catch (error: any) { log.info( `[${C}], [${F}], Error: ${ error?.message || error }` ); return false; } }; // export const generateStaffAttendanceReport = async ( // fileType: number, // clientId: number, // propId: any, // startDate: string, // endDate: string // ) => { // // ============================================================ // // SAMPLE DATA - REPLACE THIS WITH YOUR DATABASE DATA // // ============================================================ // const month = new Date(2026, 7, 1); // August 2026 // const staff = [ // { // property: "Property A", // name: "Rahul Sharma", // role: "Warden", // }, // { // property: "Property A", // name: "Amit Kumar", // role: "Security", // }, // { // property: "Property A", // name: "Neha Singh", // role: "Manager", // }, // { // property: "Property B", // name: "Raj Verma", // role: "Warden", // }, // { // property: "Property B", // name: "Pooja Sharma", // role: "Housekeeping", // }, // ]; // const attendance = [ // { // date: new Date(2026, 7, 1), // property: "Property A", // staffName: "Rahul Sharma", // role: "Warden", // checkIn: "09:00", // checkOut: "18:00", // status: "Present", // remarks: "", // }, // { // date: new Date(2026, 7, 2), // property: "Property A", // staffName: "Rahul Sharma", // role: "Warden", // checkIn: "09:20", // checkOut: "18:00", // status: "Late", // remarks: "Late by 20 minutes", // }, // { // date: new Date(2026, 7, 1), // property: "Property A", // staffName: "Amit Kumar", // role: "Security", // checkIn: "09:00", // checkOut: "18:00", // status: "Present", // remarks: "", // }, // { // date: new Date(2026, 7, 1), // property: "Property A", // staffName: "Neha Singh", // role: "Manager", // checkIn: "", // checkOut: "", // status: "Leave", // remarks: "Approved leave", // }, // { // date: new Date(2026, 7, 1), // property: "Property B", // staffName: "Raj Verma", // role: "Warden", // checkIn: "09:00", // checkOut: "18:00", // status: "Present", // remarks: "", // }, // { // date: new Date(2026, 7, 1), // property: "Property B", // staffName: "Pooja Sharma", // role: "Housekeeping", // checkIn: "09:00", // checkOut: "13:00", // status: "Half Day", // remarks: "", // }, // ]; // const outputPath = // "./staff-attendance-August-2026.xlsx"; // // ============================================================ // // COLORS // // ============================================================ // const COLORS = { // darkBlue: "FF1F4E78", // blue: "FF5B9BD5", // white: "FFFFFFFF", // border: "FFB7B7B7", // green: "FFC6EFCE", // red: "FFFFC7CE", // yellow: "FFFFEB9C", // orange: "FFFCE4D6", // lightGreen: "FFE2F0D9", // }; // const thinBorder: Partial = { // top: { // style: "thin", // color: { argb: COLORS.border }, // }, // left: { // style: "thin", // color: { argb: COLORS.border }, // }, // bottom: { // style: "thin", // color: { argb: COLORS.border }, // }, // right: { // style: "thin", // color: { argb: COLORS.border }, // }, // }; // const C = "Report Controller"; // const F = "generateStaffAttendanceReport"; // try { // // ============================================================ // // HELPER - GET DAYS OF MONTH // // ============================================================ // const getDaysInMonth = (date: Date) => { // const year = date.getFullYear(); // const month = date.getMonth(); // const totalDays = new Date( // year, // month + 1, // 0 // ).getDate(); // return Array.from( // { length: totalDays }, // (_, index) => // new Date(year, month, index + 1) // ); // }; // // ============================================================ // // WORKBOOK // // ============================================================ // const workbook = new ExcelJS.Workbook(); // workbook.creator = "Kipinn"; // workbook.created = new Date(); // workbook.modified = new Date(); // // ============================================================ // // 1. MONTHLY SUMMARY // // ============================================================ // const summary = workbook.addWorksheet( // "Monthly Summary", // { // views: [ // { // state: "frozen", // ySplit: 4, // }, // ], // } // ); // summary.mergeCells("A1:J1"); // const summaryTitle = // summary.getCell("A1"); // summaryTitle.value = // "Staff Attendance - Monthly Summary"; // summaryTitle.font = { // bold: true, // size: 16, // color: { // argb: COLORS.white, // }, // }; // summaryTitle.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.darkBlue, // }, // }; // summaryTitle.alignment = { // horizontal: "center", // vertical: "middle", // }; // summary.getRow(1).height = 28; // summary.getCell("A2").value = "Month"; // summary.getCell("A2").font = { // bold: true, // }; // summary.getCell("B2").value = month; // summary.getCell("B2").numFmt = // "mmmm yyyy"; // summary.getCell("D2").value = // "Working Days"; // summary.getCell("D2").font = { // bold: true, // }; // /* // * Monday-Saturday working week. // * Sunday = weekly off. // */ // summary.getCell("E2").value = { // formula: // "NETWORKDAYS.INTL(" + // "EOMONTH($B$2,-1)+1," + // "EOMONTH($B$2,0)," + // "11" + // ")", // }; // summary.getRow(4).values = [ // "Property", // "Staff Name", // "Role", // "Present", // "Absent", // "Late", // "Half Day", // "Leave", // "Working Days", // "Attendance %", // ]; // summary.getRow(4).eachCell( // (cell) => { // cell.font = { // bold: true, // color: { // argb: COLORS.white, // }, // }; // cell.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.blue, // }, // }; // cell.border = thinBorder; // cell.alignment = { // horizontal: "center", // vertical: "middle", // wrapText: true, // }; // } // ); // staff.forEach((employee, index) => { // const row = index + 5; // summary.getCell(row, 1).value = // employee.property; // summary.getCell(row, 2).value = // employee.name; // summary.getCell(row, 3).value = // employee.role; // // Present // summary.getCell(row, 4).value = { // formula: // `COUNTIFS(` + // `'Daily Attendance'!$B:$B,$A${row},` + // `'Daily Attendance'!$C:$C,$B${row},` + // `'Daily Attendance'!$G:$G,"Present")`, // }; // // Absent // summary.getCell(row, 5).value = { // formula: // `COUNTIFS(` + // `'Daily Attendance'!$B:$B,$A${row},` + // `'Daily Attendance'!$C:$C,$B${row},` + // `'Daily Attendance'!$G:$G,"Absent")`, // }; // // Late // summary.getCell(row, 6).value = { // formula: // `COUNTIFS(` + // `'Daily Attendance'!$B:$B,$A${row},` + // `'Daily Attendance'!$C:$C,$B${row},` + // `'Daily Attendance'!$G:$G,"Late")`, // }; // // Half Day // summary.getCell(row, 7).value = { // formula: // `COUNTIFS(` + // `'Daily Attendance'!$B:$B,$A${row},` + // `'Daily Attendance'!$C:$C,$B${row},` + // `'Daily Attendance'!$G:$G,"Half Day")`, // }; // // Leave // summary.getCell(row, 8).value = { // formula: // `COUNTIFS(` + // `'Daily Attendance'!$B:$B,$A${row},` + // `'Daily Attendance'!$C:$C,$B${row},` + // `'Daily Attendance'!$G:$G,"Leave")`, // }; // // Working Days // summary.getCell(row, 9).value = { // formula: // "NETWORKDAYS.INTL(" + // "EOMONTH($B$2,-1)+1," + // "EOMONTH($B$2,0)," + // "11" + // ")", // }; // // Attendance % // summary.getCell(row, 10).value = { // formula: // `IF(I${row}=0,0,` + // `(D${row}+F${row}+` + // `G${row}*0.5)/I${row})`, // }; // summary.getCell(row, 10).numFmt = // "0.00%"; // for (let col = 1; col <= 10; col++) { // summary.getCell(row, col).border = // thinBorder; // } // }); // summary.columns = [ // { width: 18 }, // { width: 24 }, // { width: 18 }, // { width: 12 }, // { width: 12 }, // { width: 12 }, // { width: 14 }, // { width: 12 }, // { width: 16 }, // { width: 16 }, // ]; // // ============================================================ // // 2. DAILY ATTENDANCE // // ============================================================ // const daily = // workbook.addWorksheet( // "Daily Attendance", // { // views: [ // { // state: "frozen", // ySplit: 3, // }, // ], // } // ); // daily.mergeCells("A1:I1"); // const dailyTitle = // daily.getCell("A1"); // dailyTitle.value = // "Staff Attendance - Daily Details"; // dailyTitle.font = { // bold: true, // size: 16, // color: { // argb: COLORS.white, // }, // }; // dailyTitle.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.darkBlue, // }, // }; // dailyTitle.alignment = { // horizontal: "center", // vertical: "middle", // }; // daily.getRow(1).height = 28; // daily.getRow(3).values = [ // "Date", // "Property", // "Staff Name", // "Role", // "Check In", // "Check Out", // "Status", // "Working Hours", // "Remarks", // ]; // daily.getRow(3).eachCell( // (cell) => { // cell.font = { // bold: true, // color: { // argb: COLORS.white, // }, // }; // cell.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.blue, // }, // }; // cell.border = thinBorder; // cell.alignment = { // horizontal: "center", // vertical: "middle", // wrapText: true, // }; // } // ); // attendance.forEach( // (item, index) => { // const row = index + 4; // daily.getCell(row, 1).value = // item.date; // daily.getCell(row, 1).numFmt = // "dd-mmm-yyyy"; // daily.getCell(row, 2).value = // item.property; // daily.getCell(row, 3).value = // item.staffName; // daily.getCell(row, 4).value = // item.role; // daily.getCell(row, 5).value = // item.checkIn || ""; // daily.getCell(row, 6).value = // item.checkOut || ""; // daily.getCell(row, 7).value = // item.status; // // Working hours // daily.getCell(row, 8).value = { // formula: // `IF(OR(E${row}="",F${row}=""),0,` + // `(TIMEVALUE(F${row})-` + // `TIMEVALUE(E${row}))*24)`, // }; // daily.getCell(row, 8).numFmt = // "0.00"; // daily.getCell(row, 9).value = // item.remarks || ""; // for (let col = 1; col <= 9; col++) { // daily.getCell(row, col).border = // thinBorder; // daily.getCell(row, col) // .alignment = { // vertical: "middle", // }; // } // } // ); // /* // * Status dropdown // */ // for ( // let row = 4; // row <= 1000; // row++ // ) { // daily.getCell(`G${row}`) // .dataValidation = { // type: "list", // allowBlank: true, // formulae: [ // '"Present,Absent,Late,Half Day,Leave"', // ], // showErrorMessage: true, // errorTitle: "Invalid Status", // error: // "Please select a valid attendance status.", // }; // } // daily.columns = [ // { width: 14 }, // { width: 18 }, // { width: 24 }, // { width: 18 }, // { width: 12 }, // { width: 12 }, // { width: 14 }, // { width: 16 }, // { width: 28 }, // ]; // daily.autoFilter = { // from: "A3", // to: "I3", // }; // // ============================================================ // // 3. DAILY MATRIX // // ============================================================ // const matrix = // workbook.addWorksheet( // "Daily Matrix", // { // views: [ // { // state: "frozen", // xSplit: 2, // ySplit: 3, // }, // ], // } // ); // const days = // getDaysInMonth(month); // const lastColumn = // 2 + days.length; // const lastColumnLetter = // matrix.getColumn( // lastColumn // ).letter; // matrix.mergeCells( // `A1:${lastColumnLetter}1` // ); // const matrixTitle = // matrix.getCell("A1"); // matrixTitle.value = // "Staff Attendance - Daily Matrix"; // matrixTitle.font = { // bold: true, // size: 16, // color: { // argb: COLORS.white, // }, // }; // matrixTitle.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.darkBlue, // }, // }; // matrixTitle.alignment = { // horizontal: "center", // vertical: "middle", // }; // matrix.getRow(1).height = 28; // matrix.getCell("A3").value = // "Staff Name"; // matrix.getCell("B3").value = // "Role"; // days.forEach( // (date, index) => { // const column = // index + 3; // const cell = // matrix.getCell( // 3, // column // ); // cell.value = date; // cell.numFmt = "dd"; // cell.font = { // bold: true, // color: { // argb: COLORS.white, // }, // }; // cell.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.blue, // }, // }; // cell.border = thinBorder; // cell.alignment = { // horizontal: "center", // vertical: "middle", // }; // } // ); // // Style first two header cells // for (let col = 1; col <= 2; col++) { // const cell = // matrix.getCell(3, col); // cell.font = { // bold: true, // color: { // argb: COLORS.white, // }, // }; // cell.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.blue, // }, // }; // cell.border = thinBorder; // cell.alignment = { // horizontal: "center", // vertical: "middle", // }; // } // // Staff rows // staff.forEach( // (employee, staffIndex) => { // const row = // staffIndex + 4; // matrix.getCell(row, 1).value = // employee.name; // matrix.getCell(row, 2).value = // employee.role; // matrix.getCell(row, 1).border = // thinBorder; // matrix.getCell(row, 2).border = // thinBorder; // days.forEach( // (_date, dayIndex) => { // const column = // dayIndex + 3; // const columnLetter = // matrix.getColumn( // column // ).letter; // /* // * Return short attendance code. // */ // matrix.getCell( // row, // column // ).value = { // formula: // `IFERROR(` + // `IF(INDEX('Daily Attendance'!$G:$G,` + // `MATCH(1,` + // `('Daily Attendance'!$A:$A=` + // `${columnLetter}$3)*` + // `('Daily Attendance'!$B:$B="` + // `${employee.property}")*` + // `('Daily Attendance'!$C:$C=$A${row}),` + // `0))="Present","P",` + // `IF(INDEX('Daily Attendance'!$G:$G,` + // `MATCH(1,` + // `('Daily Attendance'!$A:$A=` + // `${columnLetter}$3)*` + // `('Daily Attendance'!$B:$B="` + // `${employee.property}")*` + // `('Daily Attendance'!$C:$C=$A${row}),` + // `0))="Absent","A",` + // `IF(INDEX('Daily Attendance'!$G:$G,` + // `MATCH(1,` + // `('Daily Attendance'!$A:$A=` + // `${columnLetter}$3)*` + // `('Daily Attendance'!$B:$B="` + // `${employee.property}")*` + // `('Daily Attendance'!$C:$C=$A${row}),` + // `0))="Late","L",` + // `IF(INDEX('Daily Attendance'!$G:$G,` + // `MATCH(1,` + // `('Daily Attendance'!$A:$A=` + // `${columnLetter}$3)*` + // `('Daily Attendance'!$B:$B="` + // `${employee.property}")*` + // `('Daily Attendance'!$C:$C=$A${row}),` + // `0))="Half Day","HD",` + // `IF(INDEX('Daily Attendance'!$G:$G,` + // `MATCH(1,` + // `('Daily Attendance'!$A:$A=` + // `${columnLetter}$3)*` + // `('Daily Attendance'!$B:$B="` + // `${employee.property}")*` + // `('Daily Attendance'!$C:$C=$A${row}),` + // `0))="Leave","LV",""))))),` + // `""` + // `)`, // }; // matrix.getCell( // row, // column // ).border = thinBorder; // matrix.getCell( // row, // column // ).alignment = { // horizontal: "center", // vertical: "middle", // }; // } // ); // } // ); // matrix.getColumn(1).width = 24; // matrix.getColumn(2).width = 18; // days.forEach( // (_day, index) => { // matrix.getColumn( // index + 3 // ).width = 5; // } // ); // // ============================================================ // // 4. LEGEND - SEPARATE SHEET // // ============================================================ // const legend = // workbook.addWorksheet( // "Legend" // ); // legend.mergeCells( // "A1:D1" // ); // const legendTitle = // legend.getCell("A1"); // legendTitle.value = // "Staff Attendance - Legend"; // legendTitle.font = { // bold: true, // size: 16, // color: { // argb: COLORS.white, // }, // }; // legendTitle.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.darkBlue, // }, // }; // legendTitle.alignment = { // horizontal: "center", // vertical: "middle", // }; // legend.getRow(1).height = 28; // legend.getRow(3).values = [ // "Code", // "Status", // "Description", // "Attendance Value", // ]; // legend.getRow(3).eachCell( // (cell) => { // cell.font = { // bold: true, // color: { // argb: COLORS.white, // }, // }; // cell.fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: COLORS.blue, // }, // }; // cell.border = thinBorder; // cell.alignment = { // horizontal: "center", // vertical: "middle", // }; // } // ); // const legendData = [ // [ // "P", // "Present", // "Full day attendance", // "1.0", // ], // [ // "A", // "Absent", // "Staff was absent", // "0", // ], // [ // "L", // "Late", // "Staff arrived late", // "1.0", // ], // [ // "HD", // "Half Day", // "Half working day", // "0.5", // ], // [ // "LV", // "Leave", // "Approved leave", // "0", // ], // ]; // legendData.forEach( // (item, index) => { // const row = // index + 4; // item.forEach( // (value, colIndex) => { // const cell = // legend.getCell( // row, // colIndex + 1 // ); // cell.value = value; // cell.border = // thinBorder; // cell.alignment = { // vertical: "middle", // horizontal: // colIndex === 0 || // colIndex === 3 // ? "center" // : "left", // wrapText: true, // }; // } // ); // } // ); // // Status colors // const statusColors = [ // COLORS.green, // COLORS.red, // COLORS.yellow, // COLORS.orange, // COLORS.lightGreen, // ]; // legendData.forEach( // (_item, index) => { // const row = // index + 4; // legend.getCell( // row, // 1 // ).font = { // bold: true, // }; // legend.getCell( // row, // 2 // ).font = { // bold: true, // }; // legend.getCell( // row, // 2 // ).fill = { // type: "pattern", // pattern: "solid", // fgColor: { // argb: statusColors[index], // }, // }; // } // ); // // Attendance calculation // legend.mergeCells( // "A11:D11" // ); // legend.getCell( // "A11" // ).value = // "Attendance % Calculation"; // legend.getCell( // "A11" // ).font = { // bold: true, // size: 12, // }; // legend.mergeCells( // "A12:D12" // ); // legend.getCell( // "A12" // ).value = // "(Present + Late + Half Day × 0.5) ÷ Working Days"; // legend.getCell( // "A12" // ).alignment = { // wrapText: true, // }; // legend.mergeCells( // "A13:D13" // ); // legend.getCell( // "A13" // ).value = // "P = Present | A = Absent | L = Late | HD = Half Day | LV = Leave"; // legend.getCell( // "A13" // ).alignment = { // wrapText: true, // }; // legend.columns = [ // { width: 12 }, // { width: 18 }, // { width: 40 }, // { width: 20 }, // ]; // legend.views = [ // { // state: "frozen", // ySplit: 3, // }, // ]; // // ============================================================ // // EXCEL CALCULATION // // ============================================================ // // workbook.calcProperties.fullCalcOnLoad = // // true; // // ============================================================ // // WRITE FILE // // ============================================================ // // await workbook.xlsx.writeFile( // // outputPath // // ); // // console.log( // // `Excel generated successfully: ${outputPath}` // // ); // // return outputPath; // const raw = // await workbook.xlsx.writeBuffer(); // const buffer = // Buffer.isBuffer(raw) // ? raw // : Buffer.from(raw); // const folderName = // "attendance"; // const folderPath = // `uploads/reports/clientId_${clientId}/${folderName}`; // const urlBase = // folderPath.replace( // "uploads/", // `${process.env.UPLOAD_PATH}/` // ); // if ( // !fs.existsSync(folderPath) // ) { // fs.mkdirSync( // folderPath, // { // recursive: true // } // ); // } // const fileName = // `tenant_attendance_report_${moment().format( // "HHmmss" // )}_${startDate}_${endDate}.xlsx`; // const filePath = // `${folderPath}/${fileName}`; // fs.writeFileSync( // filePath, // buffer as NodeJS.ArrayBufferView // ); // const url = // `${urlBase}/${fileName}`; // return { // url, // fileName // }; // } catch (error: any) { // log.info( // `[${C}], [${F}], Error: ${ // error?.message || error // }` // ); // return false; // } // }