PK!A7��nvar sql = require("mysql"); var pool = require("../db"); const opts = { errorEventName: "error", logDirectory: "logs", fileNamePattern: "log-.log", dateFormat: "YYYY-MM-DD", }; const log = require("simple-node-logger").createRollingFileLogger(opts); module.exports = { getReportByName: function (name) { return new Promise((resolve, reject) => { let query = "select id, user_id, name, campaign_name, type, status, file_name, created_at, updated_at from generated_reports where name = ? order by id desc "; const data = [name]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[REPORTS], [MODEL], [getReportsDB], [error], [" + err + "]"); return reject(err); } }); }); }, getReportsDB: function (userId, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select id, user_id, name, campaign_name, report_type as type, status, file_name, created_at, updated_at from generated_reports where user_id = ? and report_type!=1 and date(updated_at) BETWEEN ? and ? order by id desc "; const data = [userId, startDate, endDate]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[REPORTS], [MODEL], [getReportsDB], [error], [" + err + "]"); return reject(err); } }); }); }, addReport: function (user_id, name, campaign_name, type, status, file_name) { return new Promise((resolve, reject) => { let query = "Insert into generated_reports (user_id, name, campaign_name, report_type, status, file_name, created_at, updated_at) values(?, ?, ?, ?, ?, ?, now(), now()) "; const data = [user_id, name, campaign_name, type, status, file_name]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { resolve(results.insertId); } } catch (err) { log.info("[REPORTS], [MODEL], [addReport], [error], [" + err + "]"); return reject(err); } }); }); }, updateFileUrl: function (reportId, reportUrl) { return new Promise((resolve, reject) => { let query = "update generated_reports set file_name =? where id =?"; const data = [reportUrl, reportId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { resolve(true); } } catch (err) { log.info("[REPORTS], [MODEL], [updateReportStatus], [error], [" + err + "]"); return reject(err); } }); }); }, updateReportStatus: function (status, reportId) { return new Promise((resolve, reject) => { let query = "update generated_reports set status =? where id =?"; const data = [status, reportId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { resolve(true); } } catch (err) { log.info("[REPORTS], [MODEL], [updateReportStatus], [error], [" + err + "]"); return reject(err); } }); }); }, getSmsReportByMsisdn: function (value) { return new Promise((resolve, reject) => { let query = " select m.id, m.user_id, m.msisdn, m.sender_id,s.sender_id, m.content,m.type, m.status, m.created_at, m.updated_at, u.name from quick_report as m INNER JOIN sender_id as s on m.sender_id = s.id INNER JOIN users as u on u.id = m.user_id where m.msisdn = ? order by m.id desc"; const data = [value]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[Reports], [MODEL], [getSmsReportByMsisdn], [error], [" + err + "]"); return reject(err); } }); }); }, getCampaignReportByMsisdn: function (value) { return new Promise((resolve, reject) => { let query = "select m.id, m.user_id,m.campaign_id,'3' as type, m.sender_id,m.msisdn, s.sender_id, m.content, m.status, m.created_at, m.updated_at, u.name from campaigns_report as m INNER JOIN sender_id as s on m.sender_id = s.sender_id INNER JOIN users as u on u.id = m.user_id where m.msisdn = ? order by m.id desc"; const data = [value]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[Reports], [MODEL], [getSmsReportByMsisdn], [error], [" + err + "]"); return reject(err); } }); }); }, getCampaignReportForUserByMsisdn: function (value, userId) { return new Promise((resolve, reject) => { let query = "select m.id, m.user_id, m.msisdn, m.sender_id,'3' as type, m.content, m.status, m.created_at, s.sender_id, m.updated_at, u.name from campaigns_report as m INNER JOIN sender_id as s on s.sender_id = m.sender_id INNER JOIN users as u on u.id = m.user_id where m.msisdn = ? and m.user_id = ? order by m.id desc"; const data = [value, userId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[Reports], [MODEL], [getCampaignReportForUserByMsisdn], [error], [" + err + "]"); return reject(err); } }); }); }, getSmsReportForUserByMsisdn: function (value, userId) { return new Promise((resolve, reject) => { let query = "select m.id, m.user_id, m.msisdn, m.sender_id, m.content, m.status, m.created_at, s.sender_id, m.updated_at, u.name from quick_report as m INNER JOIN sender_id as s on s.id = m.sender_id INNER JOIN users as u on u.id = m.user_id where m.msisdn = ? and m.user_id = ? order by m.id desc"; const data = [value, userId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[Reports], [MODEL], [getSmsReportForUserByMsisdn], [error], [" + err + "]"); return reject(err); } }); }); }, viewReportsDB: function (id, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select id, name, campaign_name, status, start_date, end_date, file_name,report_type , created_at, updated_at from generated_reports where user_id =? and date(updated_at) between ? and ? order by id desc "; const data = [id, startDate, endDate]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[Reports], [MODEL], [viewReportsDB], [error], [" + err + "]"); return reject(err); } }); }); }, viewReportsFilterByNameDB: function (id, searchValue) { return new Promise((resolve, reject) => { let query = "select id, name, report_type, status, start_date, end_date, file_name, created_at, updated_at from reports where user_id =? and name like ? order by id desc "; const data = [id, `${searchValue}%`]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[sms_report], [MODEL], [viewsms_reportDB], [error], [" + err + "]"); return reject(err); } }); }); }, viewReportsFilterByTypeDB: function (id, searchValue) { return new Promise((resolve, reject) => { let query = "select id, name, report_type, status, start_date, end_date, file_name, created_at, updated_at from sms_report where user_id =? and report_type = ? order by id desc "; const data = [id, searchValue]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[Reports], [MODEL], [viewReportsDB], [error], [" + err + "]"); return reject(err); } }); }); }, addReportToReports: function (userId, name, reportType, status, startDate, endDate, fileName) { return new Promise((resolve, reject) => { let query = "Insert into generated_reports (user_id, name, report_type, status, start_date, end_date, file_name, created_at, updated_at) values(?, ?, ?, ?, ?, ?, ?, now(), now()) "; const data = [userId, name, reportType, status, startDate, endDate, fileName]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { resolve(results.insertId); } } catch (err) { log.info("[REPORTS], [MODEL], [addReportToReports], [error], [" + err + "]"); return reject(err); } }); }); }, updateFileUrlFromReports: function (reportId, reportUrl) { return new Promise((resolve, reject) => { let query = "update generated_reports set file_name =? where id =?"; const data = [reportUrl, reportId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { resolve(true); } } catch (err) { log.info("[REPORTS], [MODEL], [updateReportStatus], [error], [" + err + "]"); return reject(err); } }); }); }, updateDateRangeReportStatus: function (status, reportId) { return new Promise((resolve, reject) => { let query = "update generated_reports set status =? where id =?"; const data = [status, reportId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { resolve(true); } } catch (err) { log.info("[REPORTS], [MODEL], [updateDateRangeReportStatus], [error], [" + err + "]"); return reject(err); } }); }); }, getSmsReportByDateRange: function (startDate, endDate) { return new Promise((resolve, reject) => { let query = " select m.id, m.user_id, m.msisdn, m.campaign_id, m.sender_id, m.content, m.status, m.created_at, m.updated_at, m.message_id, m.unicode, u.name , u.email as userEmail from sms_report as m LEFT JOIN users as u on u.id = m.user_id where date(m.updated_at) BETWEEN ? and ?"; const data = [startDate, endDate]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info("[Reports], [MODEL], [getSmsReportByDateRange], [error], [" + err + "]"); return reject(err); } }); }); }, addCampaignReport: function (userId, groupId, campaignId, first_name, last_name, senderId, content, msisdn, msgId) { return new Promise((resolve, reject) => { let query = "insert into campaigns_report (user_id,contact_group, campaign_id,first_name,last_name, sender_id, content, msisdn, created_at, updated_at, message_id) values(?,?, ?,?,?, ?, ?, ?, now(), now(),?)"; let data = [userId, groupId, campaignId, first_name, last_name, senderId, content, msisdn, msgId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info(`[CAMPAIGN], [MODEL], [addMessageReport], error [${err}], Message Id [${messageId}]`); reject(err); } }); }); }, // getGroupname: function (groupId) { // return new Promise((resolve, reject) => { // let query = // "select name from contact_groups where id =? order by id desc"; // let data = [groupId]; // pool.query(query, data, (err, results, fields) => { // try { // if (err) { // throw err; // } else { // var numRows = results.length; // if (numRows > 0) { // return resolve(results); // } else { // return resolve(false); // } // } // } catch (err) { // log.info( // "[SENDER ID], [MODEL], [getGroupname], [error], [" + err + "]" // ); // return reject(err); // } // }); // }); // }, };