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 = { addquickmessage: function ( userId, sender_Id, contactLength, status, message, type, ) { return new Promise((resolve, reject) => { let query = "insert into quick_report (user_id,sender_id,msisdn, status, content,type,created_at, updated_at) values(?,?, ?, ?, ?, ?, now(), now())"; let data = [userId, sender_Id, contactLength, status, message, type,]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info( `[QUICK MESSAGE], [MODEL], [addquickmessage], [error], [${err}]` ); reject(err); } }); }); }, updateQuickMessageAudioPath: function (fileName, quickMessageId) { return new Promise((resolve, reject) => { let query = "UPDATE quicks set message=?, updated_at=now() where id=? limit 1"; let data = [fileName, quickMessageId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info( `[QUICK MESSAGE], [MODEL], [updateQuickMessageAudioPath], [error], [${err}]` ); reject(err); } }); }); }, updateQuickMessageFileName: function (fileName, quickMessageId) { return new Promise((resolve, reject) => { let query = "UPDATE generated_reports set file_name=?, updated_at=now() where id=? limit 1"; let data = [fileName, quickMessageId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info( `[QUICK MESSAGE], [MODEL], [updateQuickMessageFileName], [error], [${err}]` ); reject(err); } }); }); }, getQuickMessageAdmin: function (startDate, endDate) { return new Promise((resolve, reject) => { let query = "select u.name as userName, s.sender_id, q.id, q.user_id,q.msisdn, q.content, q.type,q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where date(q.created_at) BETWEEN ? and ? order by q.id desc"; //, (SELECT COUNT(*) FROM sms_report WHERE campaign_id = q.id AND status = 1) AS total_sent 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( "[QUICK MESSAGES], [MODEL], [getQuickMessageAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getQuickMessageAdminFilter: function (searchType, searchValue, startDate, endDate) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.content,q.msisdn, q.type, q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where s.sender_id like ? order by q.id desc"; data = [`%${searchValue}%`]; } else if ("USER" == searchType) { query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.content,q.type, q.msisdn, q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where u.name like ? order by q.id desc"; data = [`%${searchValue}%`]; } else { query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.content,q.msisdn, q.filename,q.total, q.campaign_type as type, q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where date(q.created_at) BETWEEN ? and ? order by q.id desc"; 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( "[QUICK MESSAGES], [MODEL], [getQuickMessageAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getQuickMessageUser: function (userId, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select u.name as userName, s.sender_id, q.id, q.user_id,q.msisdn, q.content, q.type, q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.user_id = ? and q.type= 1 and date(q.created_at) BETWEEN ? and ? order by q.id desc"; const data = [userId, startDate, endDate]; //(SELECT COUNT(*) FROM sms_report WHERE campaign_id = q.id AND status = 1) AS total_sent 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( "[QUICK MESSAGES], [MODEL], [getQuickMessageUser], [error], [" + err + "]" ); return reject(err); } }); }); }, getQuickMessageUserFilter: function (userId, searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select u.name as userName, s.sender_id, q.id, q.user_id,q.msisdn, q.content,q.type, q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.user_id = ? and s.sender_id like ? order by q.id desc"; data = [userId, `%${searchValue}%`]; } else if ("MOBILE" == searchType) { query = "select u.name as userName, s.sender_id, q.id,q.msisdn, q.user_id, q.content, q.type, q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.user_id = ? and q.msisdn like ? order by q.id desc"; data = [userId, `%${searchValue}%`]; } else { query = "select u.name as userName, s.sender_id, q.id, q.user_id,q.msisdn, q.content, q.type, q.status , q.created_at, q.updated_at from quick_report as q INNER JOIN sender_id as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.user_id = ? order by q.id desc"; data = [userId]; // , (SELECT COUNT(*) FROM quick_report WHERE campaign_id = q.id AND status = 1) AS total_sent } log.info( "[QUICK MESSAGES], [MODEL], [getQuickMessageUser], [Testing], [" + query + "]" ); 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( "[QUICK MESSAGES], [MODEL], [getQuickMessageUser], [error], [" + err + "]" ); return reject(err); } }); }); }, setQuickMessageStatus: function (quickmessageId, status) { return new Promise((resolve, reject) => { const query = "update sms_report set status=? where id =? "; let data = [status, quickmessageId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(true); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [setQuickMessageStatus], [error], [${err}]` ); reject(err); } }); }); }, getQuickTodayCount: function (userId) { return new Promise((resolve, reject) => { let query = "select COUNT(*) as total from quick_report where date(updated_at) = curdate() and user_id = ? "; const data = [userId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results[0]); else return resolve(false); } } catch (err) { log.info( "[QUICK MESSAGES], [MODEL], [getQuickTodayCount], [error], [" + err + "]" ); return reject(err); } }); }); }, getQuickTodaySuccessCount: function (userId, type) { return new Promise((resolve, reject) => { let query = "select COUNT(*) as success from sms_report where date(updated_at) = curdate() and user_id = ? and type = ? and status = 2 "; const data = [userId, type]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results[0]); else return resolve(false); } } catch (err) { log.info( "[QUICK MESSAGES], [MODEL], [getQuickTodaySuccessCount], [error], [" + err + "]" ); return reject(err); } }); }); }, getQuickTodayFailedCount: function (userId, type) { return new Promise((resolve, reject) => { let query = "select COUNT(*) as failed from sms_report where date(updated_at) = curdate() and user_id = ? and type = ? and status != 2 and status != 1 "; const data = [userId, type]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results[0]); else return resolve(false); } } catch (err) { log.info( "[QUICK MESSAGES], [MODEL], [getQuickTodayFailedCount], [error], [" + err + "]" ); return reject(err); } }); }); }, getSMSReportByDateRange: function (startDate, endDate) { return new Promise((resolve, reject) => { let data = [startDate, endDate]; let query = "select q.id, q.user_id, s.sender_id, u.name as userName, q.content,q.msisdn, q.status, q.type, q.updated_at, q.created_at from quick_report as q Inner Join users as u on u.id = q.user_id Inner Join sender_id as s on s.id = q.sender_id where q.type = 1 and date(q.created_at) BETWEEN ? and ? order by id desc"; 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( "[QUICK MESSAGE], [MODEL], [getSMSReportByDateRange], [error], [" + err + "]" ); return reject(err); } }); }); }, getApiReportByDateRange: function (startDate, endDate) { return new Promise((resolve, reject) => { let data = [startDate, endDate]; let query = "select q.id, q.user_id, s.sender_id, u.name as userName, q.content,q.msisdn, q.status, q.type, q.updated_at, q.created_at from quick_report as q Inner Join users as u on u.id = q.user_id Inner Join sender_id as s on s.id = q.sender_id where q.type = 2 and date(q.created_at) BETWEEN ? and ? order by id desc"; 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( "[QUICK MESSAGE], [MODEL], [getSMSReportByDateRange], [error], [" + err + "]" ); return reject(err); } }); }); }, };