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(message, senderID, reciptions, status, total, type, sucess, filename, balanceconsumed) { // return new Promise((resolve, reject) => { // let query = // "insert into quicks(userid, message, senderID,reciptions, status, total, type, sucess, filename, balanceconsumed,created_at,updated_at) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, now(), now());"; // const data = [message, senderID, reciptions, status, total, type, sucess, filename, balanceconsumed]; // pool.query(query, data, (err, results, fields) => { // if (err) { // log.info( // "[Quickmessage], [MODEL], [updateApiKeyDB], [error], [" + err + "]" // ); // return reject(err); // } else { // resolve(true); // } // }); // }); // }, // addquickmessage: function ( // userId, // message, // type, // senderId, // total, // balanceConsumed, // attachment = '' // ) { // return new Promise((resolve, reject) => { // let query = // "insert into campaigns (user_id, message, sender_id, total, type, balance_Consumed, created_at, updated_at, attachment) values(?, ?, ?, ?, ?, ?, now(), now(), ?)"; // let data = [userId, message, senderId, total, type, balanceConsumed, attachment]; // 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 (type, startDate, endDate) { // return new Promise((resolve, reject) => { // let query = // "select u.name as userName, s.sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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.type=? and date(q.created_at) BETWEEN ? and ? order by q.id desc"; // const data = [type, 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 (type, 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.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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.type=? and s.sender_id like ? and date(q.created_at) BETWEEN ? and ? order by q.id desc"; data = [type, `${searchValue}%`, startDate, endDate]; } else if ("MESSAGE" == searchType) { query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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.type=? and q.message like ? and date(q.created_at) BETWEEN ? and ? order by q.id desc"; data = [type, `${searchValue}%`, startDate, endDate]; } else { query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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.type=? and date(q.created_at) BETWEEN ? and ? order by q.id desc"; data = [type, 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); } }); }); }, getQuickEmailAdmin: function (type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, s. emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.type=? order by q.id desc"; const data = [type]; 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); } }); }); }, getQuickEmailAdminFilter: function (type, searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select u.name as userName, s. emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.type=? and s. emailid like ? order by q.id desc"; data = [type, `${searchValue}%`]; } else if ("MESSAGE" == searchType) { query = "select u.name as userName, s. emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.type=? and q.message like ? order by q.id desc"; data = [type, `%${searchValue}%`]; } else { query = "select u.name as userName, s. emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.type=? order by q.id desc"; data = [type]; } 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); } }); }); }, getQuickVoiceAdmin: function (type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, s.callerid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN caller_ids as s on s.id = q.sender_id INNER JOIN users as u ON u.id=q.user_id where q.type=? order by q.id desc"; const data = [type]; 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, type, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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=? and date(q.created_at) BETWEEN ? and ? order by q.id desc"; const data = [userId, type, 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], [getQuickMessageUser], [error], [" + err + "]" ); return reject(err); } }); }); }, getQuickMessageUserFilter: function (userId, type, 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.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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=? and s.senderid like ? and date(c.created_at) BETWEEN ? and ? order by q.id desc"; data = [userId, type, `${searchValue}%`, startDate, endDate]; } else if ("MESSAGE" == searchType) { query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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=? and q.message like ? and date(c.created_at) BETWEEN ? and ? order by q.id desc"; data = [userId, type, `${searchValue}%`, startDate, endDate]; } else { query = "select u.name as userName, s.sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns 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=? and date(c.created_at) BETWEEN ? and ? order by q.id desc"; data = [userId, type, 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], [getQuickMessageUser], [error], [" + err + "]" ); return reject(err); } }); }); }, getQuickEmailUser: function (userId, type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, s.emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds 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=? order by q.id desc"; const data = [userId, type]; 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); } }); }); }, getQuickEmailUserFilter: function (userId, type, searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select u.name as userName, s.emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds 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=? and s.emailid like ? order by q.id desc"; data = [userId, type, `${searchValue}%`]; } else if ("MESSAGE" == searchType) { query = "select u.name as userName, s.emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds 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=? and q.message like ? order by q.id desc"; data = [userId, type, `%${searchValue}%`]; } else { query = "select u.name as userName, s.emailid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN emailSenderIds 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=? order by q.id desc"; data = [userId, type]; } //console.log(query) //console.log(data) 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); } }); }); }, getQuickVoiceUser: function (userId, type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, s.callerid as sender_id, q.id, q.user_id, q.message, q.total, q.success, q.filename, q.type, q.status , q.created_at, q.updated_at from campaigns as q INNER JOIN caller_ids 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=? order by q.id desc"; const data = [userId, type]; 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, type) { return new Promise((resolve, reject) => { let query = "select COUNT(*) as total from quicks where date(updated_at) = curdate() and userid = ? and type = ?"; 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], [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 (type, startDate, endDate) { return new Promise((resolve, reject) => { let data = [type, startDate, endDate]; let query = "select q.id, q.user_id, s.sender_id, u.name as userName, q.message, q.status, q.total, q.type, q.success, q.filename, q.balanceConsumed, q.updated_at, q.created_at from quicks 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 = ? 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); } }); }); }, getVoiceReportByDateRange: function (type, startDate, endDate) { return new Promise((resolve, reject) => { let data = [type, startDate, endDate]; let query = "select q.id, q.user_id, caller.callerid as sender_id, u.name as userName, q.message, q.status, q.total, q.type, q.success, q.filename, q.balanceConsumed, q.updated_at, q.created_at from quicks as q Inner Join users as u on u.id = q.user_id Inner Join caller_ids as caller on caller.id = q.sender_id where q.type = ? 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], [getVoiceReportByDateRange], [error], [" + err + "]" ); return reject(err); } }); }); }, getEmailReportByDateRange: function (type, startDate, endDate) { return new Promise((resolve, reject) => { let data = [type, startDate, endDate]; let query = "select q.id, q.user_id, e.emailid as sender_id, u.name as userName, q.message, q.status, q.total, q.type, q.success, q.filename, q.balanceConsumed, q.updated_at, q.created_at from quicks as q Inner Join users as u on u.id = q.user_id Inner Join emailSenderIds as e on e.id = q.sender_id where q.type = ? 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], [getEmailReportByDateRange], [error], [" + err + "]" ); return reject(err); } }); }); }, };