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 = { getCampaignsDB: function (userId, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed,c.schedule, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and date(c.created_at) BETWEEN ? and ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getCampaignsFilterDB: function (userId, searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and s.sender_id like ? order by c.id desc"; data = [userId, `%${searchValue}%`]; } else if ("NAME" == searchType) { query = "select cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.name like ? order by c.id desc"; data = [userId, `%${searchValue}%`]; } else { query = "select cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? order and date(c.created_at) BETWEEN ? and ? by c.id desc"; 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( "[CAMPAIGN LIST], [MODEL], [getCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getVoiceCampaignsDB: function (userId, type) { return new Promise((resolve, reject) => { let query = "select cg.name as contactGroupName, ci.callerid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN caller_ids as ci on ci.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=2 and c.type = ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getVoiceCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getEmailCampaignsDB: function (userId, type) { return new Promise((resolve, reject) => { let query = "select cg.name as contactGroupName, e.emailid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=2 and c.type = ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getEmailCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getEmailCampaignsFilterDB: function (userId, type, searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select cg.name as contactGroupName, e.emailid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=2 and c.type = ? and e.emailid like ? order by c.id desc"; data = [userId, type, `${searchValue}%`]; } else if ("MESSAGE" == searchType) { query = "select cg.name as contactGroupName, e.emailid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=2 and c.type = ? and c.message like ? order by c.id desc"; data = [userId, type, `%${searchValue}%`]; } else { query = "select cg.name as contactGroupName, e.emailid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=2 and c.type = ? order by c.id desc"; 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( "[CAMPAIGN LIST], [MODEL], [getEmailCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getDateRangeSMSCampaignsDB: function (startDate, endDate) { return new Promise((resolve, reject) => { let query = "select s.sender_id, u.name as userName, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, c.created_at, c.updated_at from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id INNER JOIN users as u on u.id = c.user_id where date(c.updated_at) BETWEEN ? and ? and c.status != 3 order by c.id desc"; 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( "[CAMPAIGN LIST], [MODEL], [getDateRangeSMSCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getDateRangeVoiceCampaignsDB: function (type, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select caller.callerid as senderid, u.name as userName, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, c.created_at, c.updated_at from campaigns as c INNER JOIN caller_ids as caller on caller.id = c.sender_id INNER JOIN users as u on u.id = c.user_id where c.type = ? and date(c.updated_at) BETWEEN ? and ? and c.status != 3 order by c.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( "[CAMPAIGN LIST], [MODEL], [getDateRangeVoiceCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getDateRangeEmailCampaignsDB: function (type, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select e.emailid as senderid, u.name as userName, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, c.created_at, c.updated_at from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id INNER JOIN users as u on u.id = c.user_id where c.type = ? and date(c.updated_at) BETWEEN ? and ? and c.status != 3 order by c.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( "[CAMPAIGN LIST], [MODEL], [getDateRangeEmailCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getCampaignsAdmin: function (startDate, endDate) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and date(c.created_at) BETWEEN ? and ? order by c.id desc"; 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( "[CAMPAIGN LIST], [MODEL], [getCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getCampaignsAdminFilter: function (searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and s.sender_id like ? order by c.id desc"; data = [`%${searchValue}%`]; } else if ("USER" == searchType) { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and u.name like ? order by c.id desc"; data = [`%${searchValue}%`]; } else { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? and date(c.created_at) BETWEEN ? and ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getVoiceCampaignsAdmin: function (type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, ci.callerid as senderid, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN caller_ids as ci on ci.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getVoiceCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getEmailCampaignsAdmin: function (type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, e.emailid as senderid, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getEmailCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getEmailCampaignsAdminFilter: function (type, searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, e.emailid as senderid, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? and e.emailid like ? order by c.id desc"; data = [type, `${searchValue}%`]; } else if ("MESSAGE" == searchType) { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, e.emailid as senderid, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? and c.message like ? order by c.id desc"; data = [type, `%${searchValue}%`]; } else { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, e.emailid as senderid, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getEmailCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getCompletedCampaign: function (userId) { return new Promise((resolve, reject) => { let query = "select id, name from campaigns where user_id =? and status = 2 order by id desc "; const data = [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( "[CAMPAIGN LIST], [MODEL], [getCompletedCampaign], [error], [" + err + "]" ); return reject(err); } }); }); }, checkCampaignName: function (name) { return new Promise((resolve, reject) => { let query = "select id, name from campaigns where name = ?"; const data = [name]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) { return resolve(true); } else { return resolve(false); } } } catch (err) { log.info( "[CAMPAIGN LIST], [MODEL], [checkCampaignName], [error], [" + err + "]" ); return reject(err); } }); }); }, isCampaignNameExistsByUserId: function (name, userId) { return new Promise((resolve, reject) => { let query = "select id, name from campaigns where name = ? and user_id=?"; const data = [name, userId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) { return resolve(true); } else { return resolve(false); } } } catch (err) { log.info( "[CAMPAIGN LIST], [MODEL], [isCampaignNameExistsByUserId], [error], [" + err + "]" ); return reject(err); } }); }); }, checkCampaignId: function (id) { return new Promise((resolve, reject) => { let query = "select * from campaigns where id = ?"; const data = [id]; 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( "[CAMPAIGN LIST], [MODEL], [getCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getScheduledCampaignsDB: function (userId) { return new Promise((resolve, reject) => { let query = "select cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.schedule, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and is_scheduled=1 order by c.id desc"; const data = [userId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results); } } catch (err) { log.info( "[CAMPAIGN LIST], [MODEL], [getScheduledCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getSenderIds: function (userId) { return new Promise((resolve, reject) => { let query = "select id, senderid from sender_id where userid = ? order by id desc"; const data = [userId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results); } } catch (err) { log.info( "[CAMPAIGN], [MODEL], [getSenderIds], [error], [" + err + "]" ); return reject(err); } }); }); }, getSenderIdById: function (id) { return new Promise((resolve, reject) => { let query = "select id, senderid from sender_id where id = ? order by id desc"; const data = [id]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { var numRows = results.length; if (numRows > 0) { return resolve(results[0]); } else { return resolve(false); } } } catch (err) { log.info( "[CAMPAIGN], [MODEL], [getSenderIdById], [error], [" + err + "]" ); return reject(err); } }); }); }, addCampaign: function ( userId, groupId, name, message, type, senderId, total, balanceConsumed, attachment = "" ) { return new Promise((resolve, reject) => { let query = "insert into campaigns (user_id, group_id, name, message, total, balance_consumed, success, type, status, sender_id, created_at, updated_at, attachment) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now(), ?)"; let data = [ userId, groupId, name, message, total, balanceConsumed, 0, type, 1, senderId, attachment, ]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info(`[CAMPAIGN], [MODEL], [addCampaign], [error], [${err}]`); reject(err); } }); }); }, deleteScheduledCampaignDB: function (userId, campaignId) { return new Promise((resolve, reject) => { let query = "delete from campaigns where user_id =? and id =?"; let data = [userId, campaignId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(true); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [deleteScheduledCampaignDB], [error], [${err}]` ); reject(err); } }); }); }, checkScheduledStatus: function (userId, campaignId) { return new Promise((resolve, reject) => { let query = "select * from campaigns where user_id =? and id =? and status = 1"; let data = [userId, campaignId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) { return resolve(true); } else { return resolve(false); } } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [checkScheduledStatus], [error], [${err}]` ); reject(err); } }); }); }, getScheduledCampaignsDB: function (userId, startDate, endDate) { return new Promise((resolve, reject) => { let query = "select cg.name as contactGroupName, c.balance_consumed, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.schedule, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=1 and date(c.created_at) BETWEEN ? and ? order by c.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( "[CAMPAIGN LIST], [MODEL], [getScheduledCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getScheduledCampaignsFilterDB: function (userId, searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select cg.name as contactGroupName, c.balance_consumed, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=1 and s.sender_id like ? order by c.id desc"; data = [userId, `%${searchValue}%`]; } else if ("NAME" == searchType) { query = "select cg.name as contactGroupName, c.balance_consumed, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=1 and c.name like ? order by c.id desc"; data = [userId, `%${searchValue}%`]; } else { query = "select cg.name as contactGroupName, c.balance_consumed, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and c.is_scheduled=1 and date(c.created_at) BETWEEN ? and ? order by c.id desc"; 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( "[CAMPAIGN LIST], [MODEL], [getScheduledCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getScheduledCampaignsAdmin: function (startDate, endDate) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and date(c.created_at) BETWEEN ? and ? order by c.id desc"; 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( "[CAMPAIGN LIST], [MODEL], [getScheduledCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getScheduledCampaignsAdminFilter: function (searchType, searchValue) { return new Promise((resolve, reject) => { let query; let data; log.info("here", searchType); if ("SENDER_ID" == searchType) { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and s.sender_id like ? order by c.id desc"; data = [`%${searchValue}%`]; } else { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and u.name like ? order by c.id desc"; data = [`%${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( "[CAMPAIGN LIST], [MODEL], [getScheduledCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getScheduledVoiceCampaignsAdmin: function (type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN caller_ids as ci on ci.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and c.type=? order by c.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( "[CAMPAIGN LIST], [MODEL], [getScheduledVoiceCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getScheduledEmailCampaignsAdmin: function (type) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, e.emailid as sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and c.type=? order by c.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( "[CAMPAIGN LIST], [MODEL], [getScheduledEmailCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getScheduledEmailCampaignsAdminFilter: function ( type, searchType, searchValue ) { return new Promise((resolve, reject) => { let query; let data; if ("SENDER_ID" == searchType) { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, e.emailid as sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and c.type=? and e.emailid like ? order by c.id desc"; data = [type, `${searchValue}%`]; } else if ("MESSAGE" == searchType) { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, e.emailid as sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and c.type=? and c.message like ? order by c.id desc"; data = [type, `%${searchValue}%`]; } else { query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, c.balance_consumed, e.emailid as sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.success, c.type, c.schedule, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id=c.user_id where c.is_scheduled=1 and c.type=? order by c.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( "[CAMPAIGN LIST], [MODEL], [getScheduledEmailCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, addScheduledCampaign: function ( userId, groupId, name, message, type, senderId, schedule, isScheduled, total, balanceConsumed, subject, attachment = "" ) { return new Promise((resolve, reject) => { let query = "insert into campaigns (user_id, group_id, name, message,subject, total, balance_consumed, success, type, status, sender_id, schedule, is_scheduled, created_at, updated_at, attachment) values(?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now(), ?)"; let data = [ userId, groupId, name, message, subject, total, balanceConsumed, 0, type, 1, senderId, schedule, isScheduled, attachment, ]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info(`[CAMPAIGN], [MODEL], [addCampaign], [error], [${err}]`); reject(err); } }); }); }, addTemplateCampaign: function ( userId, groupId, name, message, type, senderId, total, templateId, balanceConsumed, attachment = "", subject = "" ) { return new Promise((resolve, reject) => { let query = "insert into campaigns (user_id, group_id, name, message, total, balance_consumed, success, type, status, sender_id, template_id, created_at, updated_at, attachment, subject) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now(), ?, ?)"; let data = [ userId, groupId, name, message, total, balanceConsumed, 0, type, 1, senderId, templateId, attachment, subject, ]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [addTemplateCampaign], [error], [${err}]` ); reject(err); } }); }); }, addScheduleTemplateCampaign: function ( userId, groupId, name, message, type, senderId, total, templateId, balanceConsumed, scheduledAt, isScheduled, attachment = "", subject = "" ) { return new Promise((resolve, reject) => { let query = "insert into campaigns (user_id, group_id, name, message, total, balance_consumed, type, status, sender_id, template_id, schedule,is_scheduled, created_at, updated_at, filename, subject) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now(), ?, ?)"; let data = [ userId, groupId, name, message, total, balanceConsumed, type, 1, senderId, templateId, scheduledAt, isScheduled, attachment, subject, ]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [addTemplateCampaign], [error], [${err}]` ); reject(err); } }); }); }, updateCampaignStatus: function (campaignId) { return new Promise((resolve, reject) => { const query = "update campaigns_report set status=2 where campaign_id =? "; let data = [campaignId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(true); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [updateCampaignAudioPath], [error], [${err}]` ); reject(err); } }); }); }, updateCampaignSuccessCount: function (campaignId) { return new Promise((resolve, reject) => { const query = "update campaigns set success=success+1 where id =? "; let data = [campaignId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(true); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [updateCampaignAudioPath], [error], [${err}]` ); reject(err); } }); }); }, setCampaignStatus: function (campaignId, status) { return new Promise((resolve, reject) => { const query = "update campaigns set status=? where id =? "; let data = [status, campaignId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(true); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [setCampaignStatus], [error], [${err}]` ); reject(err); } }); }); }, getCampaignReportById: function (campaignId) { return new Promise((resolve, reject) => { const query = "SELECT cr.*, c.name FROM campaigns_report cr JOIN campaigns c ON cr.campaign_id = c.id WHERE cr.campaign_id = ?"; let data = [campaignId]; 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( `[CAMPAIGN], [MODEL], [getCampaignReportById], [error], [${err}]` ); reject(err); } }); }); }, setTotalSent: function (campaignId, total) { return new Promise((resolve, reject) => { const query = "update campaigns set total=? where id =? "; let data = [total, campaignId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(true); } } catch (err) { log.info(`[CAMPAIGN], [MODEL], [setTotalSent], [error], [${err}]`); reject(err); } }); }); }, updateCampaignReport: function (fileName, campaignId) { return new Promise((resolve, reject) => { let query = "UPDATE campaigns set file_name=?, updated_at=now() where id=? limit 1"; let data = [fileName, campaignId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info( `[CONTACT GROUP], [MODEL], [updateCampaignReport], [error], [${err}]` ); reject(err); } }); }); }, getRecentUserCampaigns: function (userId) { return new Promise((resolve, reject) => { let query = "select cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.balance_consumed, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id where c.user_id = ? and is_scheduled=2 order by c.id desc limit 10"; const data = [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( "[CAMPAIGN LIST], [MODEL], [getRecentUserCampaigns], [error], [" + err + "]" ); return reject(err); } }); }); }, getAllRecentCampaigns: function () { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.group_id, c.name, c.message, c.total, c.balance_consumed, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where is_scheduled=2 order by c.id desc limit 10"; pool.query(query, (err, results, fields) => { try { if (err) { throw err; } else { if (results.length > 0) return resolve(results); else return resolve(false); } } catch (err) { log.info( "[CAMPAIGN LIST], [MODEL], [getRecentUserCampaigns], [error], [" + err + "]" ); return reject(err); } }); }); }, getTodayUserCampaigns: function (userId) { return new Promise((resolve, reject) => { let query = "select count(*) as total from campaigns where user_id=? and date(created_at)=curdate()"; const data = [userId]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results[0]); } } catch (err) { log.info( "[CAMPAIGN LIST], [MODEL], [getRecentUserCampaigns], [error], [" + err + "]" ); return reject(err); } }); }); }, getTotalCampaigns: function () { return new Promise((resolve, reject) => { let query = "select count(*) as total from campaigns where date(created_at)=curdate()"; pool.query(query, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results[0]); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [getTotalCampaigns], [error], [${err}]` ); return reject(err); } }); }); }, getTotalScheduledCampaigns: function () { return new Promise((resolve, reject) => { let query = "select count(*) as total from campaigns where date(created_at)=curdate() and is_scheduled = 1"; pool.query(query, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results[0]); } } catch (err) { log.info( `[CAMPAIGN], [MODEL], [getTotalScheduledCampaigns], [error], [${err}]` ); return reject(err); } }); }); }, getCampaignsList: function (userId) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 order by c.id desc limit 10"; const data = [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( "[CAMPAIGN LIST], [MODEL], [getCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getVoiceCampaignsList: function (type, userId) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, ci.callerid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN caller_ids as ci on ci.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? order by c.id desc limit 10"; const data = [type, 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( "[CAMPAIGN LIST], [MODEL], [getVoiceCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getEmailCampaignsList: function (type, userId) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, e.emailid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? order by c.id desc limit 10"; const data = [type, 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( "[CAMPAIGN LIST], [MODEL], [getEmailCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getuserCampaignsList: function (userId) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, s.sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN sender_id as s on s.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.user_id =? order by c.id desc limit 10"; const data = [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( "[CAMPAIGN LIST], [MODEL], [getCampaignsDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getuserVoiceCampaignsList: function (type, userId) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, ci.callerid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN caller_ids as ci on ci.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ?and c.user_id =? order by c.id desc limit 10"; const data = [type, 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( "[CAMPAIGN LIST], [MODEL], [getVoiceCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getEmailCampaignsList: function (type, userId) { return new Promise((resolve, reject) => { let query = "select u.name as userName, u.m_id as merchantId, u.phone as userPhone, u.email as userEmail, cg.name as contactGroupName, e.emailid as sender_id, c.id, c.user_id, c.balance_consumed, c.group_id, c.name, c.message, c.total, c.success, c.type, c.status, if(c.template_id=0, 'NORMAL', 'CUSTOM') as messageType, (select name from sms_templates where sms_templates.id=c.template_id) as templateName, c.created_at, c.updated_at, c.file_name from campaigns as c INNER JOIN emailSenderIds as e on e.id = c.sender_id LEFT JOIN contact_groups as cg on cg.id = c.group_id INNER JOIN users as u ON u.id = c.user_id where c.is_scheduled=2 and c.type = ? and c.user_id =? order by c.id desc limit 10"; const data = [type, 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( "[CAMPAIGN LIST], [MODEL], [getEmailCampaignsAdmin], [error], [" + err + "]" ); return reject(err); } }); }); }, getDateRangeCampaignDB: function (startDate, endDate, userId) { return new Promise((resolve, reject) => { let query = "select name, id from campaigns where date(created_at) BETWEEN ? and ? and user_id = ?"; const data = [startDate, endDate, 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( "[CAMPAIGN LIST], [MODEL], [getDateRangeCampaignDB], [error], [" + err + "]" ); return reject(err); } }); }); }, getUserTotalStatsSMS: function (userId) { return new Promise((resolve, reject) => { let query = "select SUM(total) as totalSent, SUM(success) as totalDelivered , (select count(*) from campaigns_report where date(created_at) = curdate() and user_id = ? and (status = ? OR status = ? OR status = ?)) as totalRejected from campaigns where date(created_at) = curdate() and user_id = ? "; const data = [ userId, process.env.SMS_NOTDELIVERED, process.env.SMS_FAILED, process.env.SMS_EXPired, 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( "[CAMPAIGN LIST], [MODEL], [getUserTotalStatsSMS], [error], [" + err + "]" ); return reject(err); } }); }); }, // getCampaignTodayCount: function (userId) { // return new Promise((resolve, reject) => { // let query = // "select COUNT(*) as total from campaigns_report where date(created_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); // } // }); // }); // }, getUserTotalStatsVoice: function (userId, type) { return new Promise((resolve, reject) => { let query = "select SUM(total) as totalSent, SUM(success) as totalDelivered , (select count(*) from campaign_voice_report where date(created_at) = curdate() and user_id = ? and (status = ? OR status = ? OR status = ?)) as totalRejected from campaigns where date(created_at) = curdate() and user_id = ? and type = ? "; const data = [ userId, process.env.SMS_NOTDELIVERED, process.env.SMS_FAILED, process.env.SMS_EXPired, 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( "[CAMPAIGN LIST], [MODEL], [getUserTotalStatsVoice], [error], [" + err + "]" ); return reject(err); } }); }); }, getUserTotalStatsEmail: function (userId, type) { return new Promise((resolve, reject) => { let query = "select SUM(total) as totalSent, SUM(success) as totalDelivered , (select count(*) from campaign_voice_report where date(created_at) = curdate() and user_id = ? and (status = ? OR status = ? OR status = ?)) as totalRejected from campaigns where date(created_at) = curdate() and user_id = ? and type = ? "; const data = [ userId, process.env.SMS_NOTDELIVERED, process.env.SMS_FAILED, process.env.SMS_EXPired, 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( "[CAMPAIGN LIST], [MODEL], [getUserTotalStatsEmail], [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( "[Quick REPORTS], [MODEL], [updateReportStatus], [error], [" + err + "]" ); return reject(err); } }); }); }, totalSmsSucessCount: function (userId) { return new Promise((resolve, reject) => { let query = "select COUNT(*) as count from campaigns_report where date(updated_at) = curdate() and status = 2 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); } }); }); }, totalSmsFailedCount: function (userId) { return new Promise((resolve, reject) => { let query = "select COUNT(*) as count from campaigns_report where date(updated_at) = curdate() and status = 3 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); } }); }); }, updatemessageId: function (messageId, mobile) { return new Promise((resolve, reject) => { let query = "UPDATE campaigns_report set message_id=?, updated_at=now() where msisdn=? limit 1"; let data = [messageId, mobile]; pool.query(query, data, (err, results, fields) => { try { if (err) { throw err; } else { return resolve(results.insertId); } } catch (err) { log.info(`[CAMPAIGN], [MODEL], [updatemessageId], [error], [${err}]`); reject(err); } }); }); }, };