PK!A7��nconst contactGroup = require("../models/contactGroup"); const contact = require("../models/contact"); const reportModel = require("../models/reports"); const quickMessagereportModel = require("../models/quickMessageReport"); const quickMessageModel = require("../models/quickMessage"); const quickVoiceReportModel = require("../models/quickMessageReportVoice"); const campaignModel = require("../models/campaign"); const campaignReportModel = require("../models/campaignReport"); const campaignEmailReportModel = require("../models/campignEmailReport"); const campaignVoiceReportModel = require("../models/campignvoicereport"); const combineEmailReport = require("../models/combineEmailReport"); const senderIdModel = require("../models/senderId"); const http = require("http"); const https = require("https"); const dotenv = require("dotenv"); const jwt = require("jsonwebtoken"); const readXlsxFile = require("read-excel-file/node"); const excelJS = require("exceljs"); const fsPromises = require("fs").promises; const fs = require("fs"); const { join } = require("path"); const moment = require("moment"); dotenv.config(); const config = process.env; var multer = require("multer"); var forms = multer(); var randomstring = require("randomstring"); const { promisify } = require("util"); const util = require("util"); const mv = promisify(fs.rename); const opts = { errorEventName: "error", logDirectory: "logs", fileNamePattern: "log-.log", dateFormat: "YYYY-MM-DD", }; const log = require("simple-node-logger").createRollingFileLogger(opts); async function getReports(req, res) { try { var userId = req.headers["id"]; const startDate = req.body.startDate; const endDate = req.body.endDate; log.info( `[REPORTS], [getReports], User Id [${userId}], Start Date [${startDate}], End Date [${endDate}]` ); const list = await reportModel.getReportsDB(userId, startDate, endDate); const campaigns = await campaignModel.getCompletedCampaign(userId); if (!list) { log.info(`[REPORTS], [getReports], [No Reports Found]`); return res.status(200).json({ reports: [], campaigns: campaigns || [] }); } log.info( `[REPORTS], [getReports], [Reports List has been sent successfully]` ); return res.status(200).json({ reports: list, campaigns: campaigns || [] }); } catch (e) { log.info(`[REPORTS], [getReports], Error [${e}]`); res.status(500).json(e); } } async function viewReports(req, res) { try { var userId = req.headers["id"]; let { searchType, searchValue, startDate, endDate } = req.body; log.info(`[REPORTS], [viewReports], User Id [${userId}], Search Type [${searchType}], Search Value [${searchValue}]`); let list; if ("REPORT_NAME" == searchType) { list = await reportModel.viewReportsFilterByNameDB(userId, searchValue); } else if ("REPORT_TYPE" == searchType) { list = await reportModel.viewReportsFilterByTypeDB(userId, searchValue); } else { list = await reportModel.viewReportsDB(userId, startDate, endDate); } if (!list) { log.info(`[REPORTS], [viewReports], [No Reports Found]`); return res.status(400).json({ msg: "No Reports Found" }); } log.info( `[REPORTS], [viewReports], [Reports List has been sent successfully]` ); return res.status(200).json(list); } catch (e) { log.info(`[REPORTS], [viewReports], Error [${e}]`); res.status(500).json(e); } } async function generateReport(req, res) { try { var userId = req.headers["id"]; var campaignId = req.body.campaign; var startDate = req.body.startDate; var endDate = req.body.endDate; log.info( `[REPORTS], [generateReport], User Id [${userId}], Campaign Id [${campaignId}], ` ); if (!campaignId) { log.info(`[REPORTS], [generateReport], [Missing parameters]`); return res.status(400).json({ msg: "Missing parameters" }); } let reportName = `Report_${startDate}_${endDate}`; // const isExists = await reportModel.getReportByName(reportName); // if (isExists) { // log.info(`[REPORTS], [generateReport], [Report Name Already Exists]`); // return res.status(400).json({ msg: "Report Name Already Exists" }); // } const campaignDetails = await campaignModel.checkCampaignId(campaignId); if (!campaignDetails) { log.info( `[REPORTS], [generateReport], Campaign Id [${campaignId}], [No Campaign Found]` ); return res.status(400).json({ msg: "No Campaign Found" }); } let getCampaignReport = []; let campaignType = []; let senderIdColName = ""; getCampaignReport = await campaignReportModel.getCampaignReport( campaignId ); campaignType = "SMS"; senderIdColName = "Sender Id"; let fileUrl = ""; let reportId = await reportModel.addReport( userId, reportName, campaignDetails?.name, 4,//campiaignreport in generated reports 1, fileUrl ); const path = `./users/upload/reports/${reportId}`; let folderAction = await fs.promises.mkdir(path, { recursive: true }); const workbook = new excelJS.Workbook(); let filepart = reportName.split(" ").join("_"); var xlsxFileName = `report_${filepart}_${reportId}.xlsx`; fileUrl = `${config.FILEPATH_URL}/upload/reports/${reportId}/${xlsxFileName}`; await reportModel.updateFileUrl(reportId, fileUrl); if (getCampaignReport?.length > 0) { log.info( `[REPORTS], [generateReport], Campaign Id [${campaignId}], [The Report is being generating]` ); generatingReport( campaignId, getCampaignReport, workbook, xlsxFileName, reportName, campaignDetails, path, senderIdColName, reportId ); } else { log.info( `[REPORTS], [generateReport], Campaign Id [${campaignId}], Type [${campaignType}], [No Campaign Data Found]` ); return res.status(400).json({ msg: "No Campaign Data Found" }); } return res.status(200).json({ msg: "The Report is being generating" }); } catch (e) { log.info(`[REPORTS], [generateReport], Error [${e}]`); res.status(500).json(e); } } const generatingReport = ( campaignId, getCampaignReport, workbook, xlsxFileName, reportName, campaignDetails, path, senderIdColName, reportId ) => { return new Promise(async (resolve, reject) => { try { const worksheet = workbook.addWorksheet("Reports"); worksheet.columns = [ { header: "First Name", key: "firstName", width: 20 }, { header: "Last Name", key: "lastName", width: 15 }, { header: "Phone Number", key: "mobile", width: 20 }, { header: senderIdColName, key: "senderId", width: 20 }, { header: "Campaign Name", key: "campaignName", width: 40 }, { header: "Sent Date", key: "sentDate", width: 40 }, // { header: "Delivered Date", key: "deliveredDate", width: 40 }, { header: "Status", key: "status", width: 20 }, { header: "Message", key: "message", width: 160 }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true }; }); getCampaignReport.map((record) => { let status = "Sent"; let deliveredDate = "NA"; log.info( `[REPORTS], [generateReport], Campaign Name [${campaignDetails?.name}], Sender ID [${record.sender_id}], Campaign Id [${campaignId}], Email [${record["email"]}] Group Id [${campaignDetails?.group_id}], Mobile [${record["msisdn"]}], First Name [${record["first_name"]}], Last Name [${record["last_name"]}], Type [${campaignDetails?.type}], Message [${record["content"]}], Sent Date [${record["created_at"]}], Delivered Date [${deliveredDate}], Status [${status}]` ); var rowdata = { firstName: record["first_name"], lastName: record["last_name"], // email: record["email"], mobile: record["msisdn"], senderId: record["sender_id"], campaignName: campaignDetails?.name, sentDate: record["created_at"], deliveredDate, status, message: record["content"], }; worksheet.addRow(rowdata); }); await workbook.xlsx.writeFile(`${path}/` + xlsxFileName); await reportModel.updateReportStatus(2, reportId); log.info( `[REPORTS], [generateReport], Campaign Id [${campaignId}], [The Report has been generated]` ); resolve(true); } catch (err) { log.info(`[REPORTS], [generatingReport], ERROR [${err}] `); reject(err); } }); }; async function GetReportsBySearch(req, res) { try { var userId = req.headers["id"]; var usertype = req.headers["usertype"]; const { type, value } = req.params; log.info( `[Reports], [GetReportsBySearch], User Id [${userId}], Type [${type}], Value [${value}]` ); if (!type || !value) { log.info(`[Reports], [GetReportsBySearch], [Missing Parameters]`); return res.status(400).json({ msg: "Missing Parameters" }); } if (type == "SMS") { let mobile = value.toString(); mobile = "260" + mobile.slice(mobile.length - 9, mobile.length); let list = []; let list2 = []; let combined = []; if (usertype == process.env.CLIENT_USER) { list = await reportModel.getSmsReportForUserByMsisdn(mobile, userId); log.info(`[Reports], [GetReportsBySearch], NUMBER1 [${JSON.stringify(list)}]`); if (list) { combined = [...list] } list2 = await reportModel.getCampaignReportForUserByMsisdn(mobile, userId); log.info(`[Reports], [GetReportsBySearch], CAMP NUMBER [${JSON.stringify(list2)}]`); if (list2) { combined = [...combined, ...list2] } } else { list = await reportModel.getSmsReportByMsisdn(mobile); // log.info(`[Reports], [GetReportsBySearch], NUMBER [${JSON.stringify(list)}]`); if (list) { combined = [...list] } list2 = await reportModel.getCampaignReportByMsisdn(mobile); // log.info(`[Reports], [GetReportsBySearch], CAMP NUMBER [${JSON.stringify(list2)}]`); if (list2) { combined = [...combined, ...list2] } } log.info(`[Reports], [GetReportsBySearch], NUMBER [${JSON.stringify(combined)}]`); if (combined.length == 0) { log.info(`[Reports], [GetReportsBySearch], [No SMS Reports Found]`); return res.status(400).json({ msg: "No SMS Reports Found" }); } log.info( `[Reports], [GetReportsBySearch], [SMS Reports has been sent successfully]` ); log.info(`[Reports], [GetReportsBySearch], NUMBER [${JSON.stringify(combined)}]`); return res.status(200).json(combined); } } catch (e) { log.info(`[Reports], [GetReportsBySearch], Error [${e}]`); return res.status(500).json(e); } } async function generateReports(req, res) { try { var userId = req.headers["id"]; const { type, startDate, endDate } = req.body; // let name = "smscampaign".startDate; let name = `Report_${startDate}_${endDate}`; log.info( `[Reports], [generateReports], User Id [${userId}], Report Name [${name}], Report Type [${type}], Start Date [${startDate}], End Date [${endDate}]` ); if (!type || !startDate || !endDate) { log.info(`[Reports], [generateReports], [Missing Parameters]`); return res.status(400).json({ msg: "Missing Parameters" }); } if (type == process.env.SMS_Campaign) { const result = await CampaignsReportsFunction({ type, startDate, endDate, name, userId, res, }); if (result == true) { return res.status(200).json({ msg: "The Report is being generating" }); } else { return res.status(400).json({ msg: result }); } } else if (type == process.env.QUICK_MESSAGE) { const result = await QuickReportsFunction({ type, startDate, endDate, name, userId, res, }); if (result == true) { return res.status(200).json({ msg: "The Report is being generating" }); } else { return res.status(400).json({ msg: result }); } } else { const result = await QuickReportsFunction({ type, startDate, endDate, name, userId, res, }); if (result == true) { return res.status(200).json({ msg: "The Report is being generating" }); } else { return res.status(400).json({ msg: result }); } } } catch (e) { log.info(`[Reports], [generateReports], Error [${e}]`); return res.status(500).json(e); } } const CampaignsReportsFunction = async ({ type, startDate, endDate, name, userId, res, }) => { try { let campaignDetails = {}; let reportType = "SMS"; let senderIdColName = "Sender Id"; let typeName = "SMS"; campaignDetails = await campaignModel.getDateRangeSMSCampaignsDB( startDate, endDate ); if (!campaignDetails) { log.info(`[Reports], [generateReports], [No ${typeName} Campaign Found]`); return `No ${typeName} Campaign Found`; } let fileUrl = ""; let reportId = await reportModel.addReportToReports( userId, name, type,//type 1, //status startDate, endDate, fileUrl ); const path = `./users/upload/dateRangeReports/${reportId}`; let folderAction = await fs.promises.mkdir(path, { recursive: true }); const workbook = new excelJS.Workbook(); let filepart = name.split(" ").join("_"); var xlsxFileName = `report_${filepart}_${reportId}.xlsx`; fileUrl = `${config.FILEPATH_URL}/upload/dateRangeReports/${reportId}/${xlsxFileName}`; await reportModel.updateFileUrlFromReports(reportId, fileUrl); log.info( `[REPORTS], [generateReport], Report Name [${name}], [The Report is being generating]` ); generatingDateRangeReport( reportId, campaignDetails, workbook, xlsxFileName, name, path, senderIdColName ); return true; } catch (err) { throw err; } }; const QuickReportsFunction = async ({ type, startDate, endDate, name, userId, res, }) => { try { let list = []; let reportType = process.env.QUICK_MESSAGE; let senderIdColName = "Sender Id"; let typeName = "SMS"; if (type == process.env.QUICK_MESSAGE) { list = await quickMessageModel.getSMSReportByDateRange( startDate, endDate ); } else { list = await quickMessageModel.getApiReportByDateRange( startDate, endDate ); } // log.info(`[Reports], [generateReports], [No Quick ${typeName} , QUICK_REPORTS[${JSON.stringify(list)}]`); if (!list) { log.info( `[Reports], [generateReports], [No Quick ${typeName} Report Found]` ); return `No report Found Within this date range`; } let fileUrl = ""; let reportId = await reportModel.addReportToReports( userId, name, reportType, 1, //status startDate, endDate, fileUrl ); const path = `./users/upload/dateRangeReports/${reportId}`; let folderAction = await fs.promises.mkdir(path, { recursive: true }); const workbook = new excelJS.Workbook(); let filepart = name.split(" ").join("_"); var xlsxFileName = `report_${filepart}_${reportId}.xlsx`; fileUrl = `${config.FILEPATH_URL}/upload/dateRangeReports/${reportId}/${xlsxFileName}`; await reportModel.updateFileUrlFromReports(reportId, fileUrl); log.info( `[REPORTS], [generateReport], Report Name [${name}], [The Report is being generating]` ); generatingQuickDateRangeReport( reportId, list, workbook, xlsxFileName, name, path, senderIdColName ); return true; } catch (err) { throw err; } }; const SingleReportsFunction = async ({ type, startDate, endDate, name, userId, res, }) => { try { let list = []; let reportType = process.env.SMS_Reports; let senderIdColName = "Sender Id"; let typeName = "SMS"; if (type == process.env.SMS_Reports) { list = await reportModel.getSmsReportByDateRange(startDate, endDate); } else if (type == process.env.Voice_Reports) { // list = await reportModel.getVoiceReportByDateRange(startDate, endDate); // reportType = process.env.Voice_Reports; // senderIdColName = "Caller Id"; // typeName = "VOICE"; } else { list = await combineEmailReport.getEmailReportByDateRange( startDate, endDate ); reportType = process.env.Email_Reports; senderIdColName = "Sender Name"; typeName = "EMAIL"; } if (!list) { log.info(`[Reports], [generateReports], [No ${typeName} Report Found]`); return `No ${typeName} Report Found`; } let fileUrl = ""; let reportId = await reportModel.addReportToReports( userId, name, reportType, 1, //status startDate, endDate, fileUrl ); const path = `./users/upload/dateRangeReports/${reportId}`; let folderAction = await fs.promises.mkdir(path, { recursive: true }); const workbook = new excelJS.Workbook(); let filepart = name.split(" ").join("_"); var xlsxFileName = `report_${filepart}_${reportId}.xlsx`; fileUrl = `${config.FILEPATH_URL}/upload/dateRangeReports/${reportId}/${xlsxFileName}`; await reportModel.updateFileUrlFromReports(reportId, fileUrl); log.info( `[REPORTS], [generateReport], Report Name [${name}], [The Report is being generating]` ); generatingSingleDateRangeReport( reportId, list, workbook, xlsxFileName, name, path, senderIdColName ); return true; } catch (err) { throw err; } }; const generatingDateRangeReport = ( reportId, campaignDetails, workbook, xlsxFileName, reportName, path, senderIdColName ) => { return new Promise(async (resolve, reject) => { try { const worksheet = workbook.addWorksheet("Reports"); worksheet.columns = [ { header: "Campaign Id", key: "campaignId", width: 20 }, { header: "Campaign Name", key: "campaignName", width: 40 }, { header: "User Id", key: "userId", width: 20 }, { header: "User Name", key: "userName", width: 20 }, { header: senderIdColName, key: "senderId", width: 20 }, { header: "Start Date", key: "startDate", width: 20 }, { header: "Completed Date", key: "completedDate", width: 20 }, { header: "Status", key: "status", width: 20 }, { header: "Total Sent", key: "totalSent", width: 40 }, { header: "Total Delivered", key: "totalDelivered", width: 40 }, { header: senderIdColName == "Caller Id" ? "File Name" : "Message", key: "message", width: 160, }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true }; }); campaignDetails.map((campaign) => { let status = "Completed"; let completedDate = campaign.updated_at; if (campaign.status == 1) { status = "Running"; completedDate = "N/A"; } log.info( `[REPORTS], [generatingDateRangeReport], Campaign Id [${campaign.id}], Campaign Name [${campaign.name}], User Id [${campaign.user_id}], User Name [${campaign.userName}], Sender Id [${campaign.sender_id}], Start Date [${campaign.created_at}], Completed Date [${completedDate}] Status [${status}], Total Sent [${campaign.total}], Total Delivered [${campaign.success}], Message [${campaign.message}], ` ); let filename = ""; // if (senderIdColName == "Caller Id") { // let string = campaign.message; // if (string && string.length > 5) { // filename = string?.split("Voice_File_"); // filename = "Voice_File_" + filename[1]; // } // } var rowdata = { campaignId: campaign.id, campaignName: campaign.name, userId: campaign.user_id, userName: campaign.userName, senderId: campaign.sender_id, startDate: campaign.created_at, completedDate, status, totalSent: campaign.total, totalDelivered: campaign.success, message: senderIdColName == "Caller Id" ? filename : campaign.message, }; worksheet.addRow(rowdata); }); await workbook.xlsx.writeFile(`${path}/` + xlsxFileName); await reportModel.updateDateRangeReportStatus(2, reportId); log.info( `[REPORTS], [generatingDateRangeReport], Report Id [${reportId}], Report Name [${reportName}], [The Report has been generated]` ); resolve(true); } catch (err) { log.info(`[REPORTS], [generatingDateRangeReport], ERROR [${err}] `); reject(err); } }); }; const generatingQuickDateRangeReport = ( reportId, list, workbook, xlsxFileName, reportName, path, senderIdColName ) => { return new Promise(async (resolve, reject) => { try { const worksheet = workbook.addWorksheet("Reports"); worksheet.columns = [ { header: "Quick Id", key: "quickId", width: 20 }, { header: "User Id", key: "userId", width: 20 }, { header: "User Name", key: "userName", width: 40 }, { header: "Msisdn", key: "msisdn", width: 40 }, { header: senderIdColName, key: "senderId", width: 40 }, { header: "Start Date", key: "startDate", width: 20 }, { header: "Completed Date", key: "completedDate", width: 20 }, { header: "Status", key: "status", width: 20 }, { header: "Message", key: "message", width: 160, }, ]; worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true }; }); list.map((quickMessage) => { let status = "Completed"; let completedDate = quickMessage.updated_at; if (quickMessage.status == 1) { status = "sent"; completedDate = "N/A"; } log.info( `[REPORTS], [generatingQuickDateRangeReport], quickMessage Id [${quickMessage.id}], msisdn[${quickMessage.msisdn}], User Id [${quickMessage.user_id}], User Name [${quickMessage.userName}], Sender Id [${quickMessage.sender_id}], Start Date [${quickMessage.created_at}], Completed Date [${completedDate}], Status [${status}], Total Sent [${quickMessage.total}], Total Delivered [${quickMessage.success}], Message [${quickMessage.content}], ` ); let filename = ""; var rowdata = { quickId: quickMessage.id, userId: quickMessage.user_id, userName: quickMessage.userName, msisdn: quickMessage.msisdn, senderId: quickMessage.sender_id, startDate: quickMessage.created_at, completedDate, status, message: quickMessage.content, }; worksheet.addRow(rowdata); }); await workbook.xlsx.writeFile(`${path}/` + xlsxFileName); await reportModel.updateDateRangeReportStatus(2, reportId); log.info( `[REPORTS], [generatingQuickDateRangeReport], Report Id [${reportId}], Report Name [${reportName}], [The Report has been generated]` ); resolve(true); } catch (err) { log.info(`[REPORTS], [generatingQuickDateRangeReport], ERROR [${err}] `); reject(err); } }); }; const generatingSingleDateRangeReport = ( reportId, list, workbook, xlsxFileName, reportName, path, senderIdColName ) => { return new Promise(async (resolve, reject) => { try { const worksheet = workbook.addWorksheet("Reports"); worksheet.columns = [ { header: "Phone Number", key: "msisdn", width: 20 }, { header: "User Id", key: "userId", width: 10 }, { header: "User Name", key: "userName", width: 20 }, { header: "User Email", key: "userEmail", width: 40 }, { header: senderIdColName, key: "senderId", width: 30 }, { header: "Delivered At", key: "deliveredAt", width: 20 }, { header: "Status", key: "status", width: 20 }, { header: "Message Id", key: "messageId", width: 40 }, { header: "Message", key: "message", width: 160, }, ]; if (senderIdColName == "Caller Id") { worksheet.columns = [ { header: "Phone Number", key: "msisdn", width: 20 }, { header: "User Id", key: "userId", width: 10 }, { header: "User Name", key: "userName", width: 20 }, { header: "User Email", key: "userEmail", width: 40 }, { header: senderIdColName, key: "senderId", width: 30 }, { header: "Delivered At", key: "deliveredAt", width: 20 }, { header: "Status", key: "status", width: 20 }, { header: "Unique Id", key: "messageId", width: 40 }, { header: "Call Duration (in seconds)", key: "duration", width: 40, }, { header: "File Name", key: "message", width: 50, }, ]; } worksheet.getRow(1).eachCell((cell) => { cell.font = { bold: true }; }); list.map((single) => { let status = "Sent"; let deliveredAt = single.status == process.env.VOICE_SENT ? "N/A" : single.updated_at; if (senderIdColName == "Caller Id") { // voice status if (single.status == process.env.VOICE_ANSWERED) status = "Answered"; else if (single.status == process.env.VOICE_NOT_ANSWERED) status = "Not Answered"; else if (single.status == process.env.VOICE_BUSY) status = "Busy"; else if (single.status == process.env.VOICE_FAILED) status = "Failed"; } else { //other status if (single.status == process.env.SMS_DELIVERED) { status = "Delivered"; deliveredAt = single.updated_at; } else if (single.status == process.env.SMS_NOTDELIVERED) status = "Not Delivered"; else if (single.status == process.env.SMS_FAILED) status = "Failed"; else if (single.status == process.env.SMS_EXPired) status = "Expired"; } log.info( `[REPORTS], [generatingSingleDateRangeReport], Msisdn [${single.msisdn}], User Id [${single.user_id}], User Name [${single.name}], User Email [${single.userEmail}] Sender Id [${single.sender_id}], Sent At [${single.created_at}], Delivered At [${deliveredAt}], Status [${status}], Message Id [${single.message_id}], Message [${single.content}], Duration [${single.duration} seconds] ` ); let filename = ""; if (senderIdColName == "Caller Id") { let string = single.content; if (string && string.length > 5) { filename = string?.split("Voice_File_"); filename = "Voice_File_" + filename[1]; } } var rowdata = { msisdn: single.msisdn, userId: single.user_id, userName: single.name, userEmail: single.userEmail, senderId: single.sender_id, sentAt: single.created_at, deliveredAt, status, messageId: single.message_id, message: senderIdColName == "Caller Id" ? filename : single.content, }; if (senderIdColName == "Caller Id") { var rowdata = { ...rowdata, duration: single.duration }; } worksheet.addRow(rowdata); }); await workbook.xlsx.writeFile(`${path}/` + xlsxFileName); await reportModel.updateDateRangeReportStatus(reportId, 2); log.info( `[REPORTS], [generatingSingleDateRangeReport], Report Id [${reportId}], Report Name [${reportName}], [The Report has been generated]` ); resolve(true); } catch (err) { log.info(`[REPORTS], [generatingSingleDateRangeReport], ERROR [${err}] `); reject(err); } }); }; var self = (module.exports = { getReports, viewReports, generateReport, GetReportsBySearch, generateReports, });