const mysql = require('mysql2/promise'); // Assuming you are using mysql2 package for Node.js
const { sendTemplateBlast, sendTemplateBlastMedia } = require('./lib/sendTemplate');
const config = require('./config');
const format = require("date-format");
const { KeiLog } = require('./lib/Logger');
const datetb = format("yyMM", new Date());
const pool = mysql.createPool({
host: config.host,
port: config.port,
user: config.user,
password: config.password,
database: config.database
});
async function executeQuery(query) {
const connection = await pool.getConnection();
try {
const [results,] = await connection.query(query);
return results;
} finally {
connection.release();
}
}
/**
* Escapes special characters in a string.
*
* @param {string} val - The input string to be escaped.
* @return {string} The escaped string.
*/
const _escapeString = (val) => {
return val.replace(/[\0\n\r\b\t\\'"\x1a]/g, (s) => {
const esc = {
"\0": "\\0",
"\n": "\\n",
"\r": "\\r",
"\b": "\\b",
"\t": "\\t",
"\x1a": "\\Z",
"'": "''",
'"': '""',
};
return esc[s] || "\\" + s;
});
};
// Helper function to get formatted month
function getFormattedMonth(date) {
return date.toISOString().slice(0, 7).replace('-', '');
}
// Function to get Data Template
async function getDataTemplate(template) {
const query = `SELECT * FROM tb_template_pesan WHERE text !='' AND template = '${template}' LIMIT 1`;
console.log(query);
return await executeQuery(query);
}
// Function to get Number Agent
async function getNomorAgent(number) {
const query = `SELECT * FROM user WHERE nomor = '${number}' LIMIT 1`;
console.log(query);
return await executeQuery(query);
}
// Function to get Message Bulk
async function getMessageBulk(role, dept, tim, udept, userid, idtl) {
try {
const DT = `dept='${dept}' AND tim='${tim}'`;
const NEDT = `dept!='' AND tim!=''`;
let q = '';
if (udept === '0') {
q = `AND (${NEDT})`;
}
if (role === '2') {
q = `AND (${NEDT} AND id_pd='${userid}')`;
}
if (role === '3') {
q = `AND (${NEDT} AND id_pb='${userid}')`;
}
if (role === '4') {
q = `AND (${NEDT} AND id_sv='${userid}')`;
}
if (role === '5') {
q = `AND (${DT} AND user_id='${userid}')`;
}
if (role === '6') {
q = `AND (${DT} AND user_id='${idtl}')`;
}
if (tim === 'Admin' && udept === '1') {
q = `AND (${NEDT} AND user_level='${role}')`;
}
const query = `SELECT * FROM tb_nomor_${datetb} WHERE trim(nomor) <> '' AND flag = '4' ${q} ORDER BY created_date ASC`;
//console.log(query)
return await executeQuery(query); // Assuming executeQuery is a valid function
} catch (error) {
console.log(error);
}
}
/**
* Replaces spintax patterns in a text with the corresponding parameters.
*
* @param {string} text - The text containing spintax patterns.
* @param {Array} params - The array of parameters to replace the spintax patterns.
* @return {string} The text with spintax patterns replaced with parameters.
*/
function replaceSpintaxWithParams(text, params) {
// Regular expression to find all spintax patterns like {{1}}, {{2}}, etc.
const spintaxPattern = /\{\{(\d+)\}\}/g;
// Replacing each spintax in the text with the corresponding parameter
return text.replace(spintaxPattern, (match, number) => {
const index = parseInt(number, 10) - 1; // Convert to zero-based index
return index < params.length ? params[index] : match;
});
}
/**
* Blast function to send messages.
*
* @param {Object} postdata - the postdata object containing user data
* @param {Object} io - the io object for socket communication
* @return {Promise} a promise that resolves when the function completes
* @example const postdata = { user_level: 2, dept: '1', tim: 'Admin', user_dept: '1', user_id: '1', idtl: '1' }
* const io = io
* await Blast(postdata, io)
*/
async function Blast(postdata, io) {
try {
const getMessage = await getMessageBulk(postdata.user_level, postdata.dept, postdata.tim, postdata.user_dept, postdata.user_id, postdata.idtl);
// console.log(getMessage);
KeiLog("INFO", `Get data Blast jumlah ${getMessage.length}`);
if (getMessage) {
KeiLog("INFO", "Starting Blast Message")
for (const msg of getMessage) {
KeiLog("INFO", `Process Blasting ke nomor ${msg.nomor}`);
let getNoAgen;
if (msg.nomor_agent) {
[getNoAgen] = await getNomorAgent(msg.nomor_agent);
} else {
getNoAgen = null; // Or set a default object if needed
}
const [tpl] = await getDataTemplate(msg.template_pesan);
const tbid = `tb_nomor_${datetb}_id`;
const id = msg[tbid];
const role = "1";
const init = "2";
let uname, tplgrp; // Define waid here (or get its value from somewhere)
if (msg.nomor_agent || msg.nama_agent) {
uname = msg.nama_agent;
tplgrp = msg.template_pesan;
} else {
uname = postdata.user_nama;
tplgrp = 'unassigned';
}
const phrase = tpl.text;
const prm = ["{{1}}", "{{2}}", "{{3}}", "{{4}}", "{{5}}", "{{6}}", "{{7}}", "{{8}}", "{{9}}", "{{10}}", "{{11}}", "{{12}}", "{{13}}", "{{14}}", "{{15}}"];
const str_rep = [msg.param1, msg.param2, msg.param3, msg.param4, msg.param5, msg.param6, msg.param7, msg.param8, msg.param9, msg.param10, msg.param11, msg.param12, msg.param13, msg.param14, msg.param15]
.map(param => param ? param.replace(/,/g, '.') : param);
const params = str_rep.filter(n => n);
const param = params.join(",");
const tpls = replaceSpintaxWithParams(phrase, params);
const fileName = msg.media;
console.log(fileName);
const ext = fileName ? fileName.split('.').pop() : "";
let files, urlmedia;
if (msg.media) {
files = `tpls.${ext}`;
urlmedia = msg.media;
} else {
files = ""; // Set to NULL if undefined
urlmedia = ""; // Set to NULL if undefined
}
// Logic for user level handling
let idtl, idsv, idpb, idpd, idagent, unomor, nomoragent, dept, tim;
const agentHasIdTl = getNoAgen && getNoAgen.id_tl > 0;
idtl = agentHasIdTl ? getNoAgen.id_tl : 0;
idsv = agentHasIdTl ? getNoAgen.id_sv : 0;
idpb = agentHasIdTl ? getNoAgen.id_pb : 0;
idpd = agentHasIdTl ? getNoAgen.id_pd : 0;
idagent = agentHasIdTl ? getNoAgen.user_id : 0;
unomor = agentHasIdTl ? getNoAgen.nomor : postdata.user_nomor;
nomoragent = agentHasIdTl ? getNoAgen.nomor : "";
dept = agentHasIdTl ? getNoAgen.dept : msg.dept;
tim = agentHasIdTl ? getNoAgen.tim : msg.tim;
KeiLog("INFO", `Process Send ke nomor ${msg.nomor} `);
// Override idpd, idpb, idsv, idtl based on user level if agent ID is not present
if (!agentHasIdTl) {
switch (postdata.user_level) {
case "2":
idpd = postdata.user_id;
break;
case "3":
idpb = postdata.user_id;
idpd = postdata.idpd;
break;
case "4":
idsv = postdata.user_id;
idpb = postdata.idpb;
idpd = postdata.idpd;
break;
case "5":
idtl = postdata.user_id;
idsv = postdata.idsv;
idpb = postdata.idpb;
idpd = postdata.idpd;
break;
case "6":
idtl = postdata.idtl;
idsv = postdata.idsv;
idpb = postdata.idpb;
idpd = postdata.idpd;
idagent = postdata.user_id;
break;
// No additional case for user level "1" as the defaults are already set
}
}
if (msg.template_pesan !== '') {
KeiLog("INFO", `Process Send Template ke nomor ${msg.nomor} dengan template ${msg.template_pesan}`);
//io.to(postdata.user_id).emit("log",`Process Send Template ke nomor ${msg.nomor} dengan template ${msg.template_pesan}`);
let waid, kimochine;
if (msg.media) {
({ waid, kimochine } = await sendTemplateBlastMedia(param, msg.template_pesan, msg.nomor, msg.media,postdata.phone_id));
} else {
({ waid, kimochine } = await sendTemplateBlast(param, msg.template_pesan, msg.nomor,postdata.phone_id));
}
if (!kimochine) {
io.emit('forward', {
id: postdata.user_id,
msg: `Gagal Send Template ke nomor [${msg.nomor}] dengan template => ${msg.template_pesan} [GAGAL] Alasan : (${waid}`
});
//io.to(postdata.user_id).emit("log", `Gagal Send Template ke nomor [${msg.nomor}] dengan template => ${msg.template_pesan} [GAGAL] Alasan : (${waid})`);
continue; // Continue with the next iteration of the loop
}
// io.to(postdata.user_id).emit("log", `Process Send Template ke nomor [${msg.nomor}] dengan template => ${msg.template_pesan} [SUCCESS]`);
io.emit('forward', {
id: postdata.user_id,
msg: `Process Send Template ke nomor [${msg.nomor}] dengan template => ${msg.template_pesan} [SUCCESS]`
});
const flag = waid ? '2' : '10';
const status = waid ? 'sent' : 'failed';
const currentDate = new Date();
const formattedDate = formatDateTime(currentDate);
let data = {
waid: waid,
idnomor: id,
media: msg.media ?? "",
files: files,
urlmedia: urlmedia,
tim: tim,
dept: dept,
idagent: idagent,
idtl: idtl,
idsv: idsv,
idpb: idpb,
idpd: idpd,
nama: msg.nama,
nama_agent: msg.nama_agent ?? "",
nomor_agent: nomoragent ?? "",
uname: uname,
unomor: unomor,
tpls: tpls,
nomor: msg.nomor,
role: role,
tplgrp: tplgrp,
template_pesan: msg.template_pesan,
account_number: msg.account_number,
init: init,
flag: flag,
status: status,
date: formattedDate,
phone_id : postdata.phone_id ?? config.phone_id,
display_number : postdata.display_number
};
try {
if (getNoAgen && getNoAgen.id_tl > 0) {
KeiLog("INFO", "Updating Inbox Log Assign");
await updateInboxLogAssign(data);
KeiLog("INFO", "Updating Outbox Log Assign");
await updateOutboxLogAssign(data);
}
if (!msg.nomor_agent || (getNoAgen && getNoAgen.id_tl <= 0)) {
KeiLog("INFO", "Updating Inbox Log UnAssign");
await updateInboxLogUnAssign(data);
KeiLog("INFO", "Updating Outbox Log UnAssign");
await updateOutboxLogUnAssign(data);
}
KeiLog("INFO", "Inserting TbLogPesan");
await insertTbLogPesan(data);
KeiLog("INFO", "Updating Tpl1");
await updateTpl1(data);
KeiLog("INFO", "Updating Tpl2");
await updateTpl2(data);
KeiLog("INFO", "Inserting TbPesan");
await insertTbPesan(data);
KeiLog("INFO", "Updating TbNomor");
await updateTbNomor(data);
} catch (dbError) {
KeiLog("ERROR", `Database operation failed: ${dbError.message}`);
io.to(postdata.user_id).emit("log", `Database operation failed: ${dbError.message}`);
}
} else {
KeiLog("ERROR", `Gagal template pesan kosong`);
io.to(postdata.user_id).emit("log", `Gagal template pesan kosong`);
}
}
} else {
io.to(postdata.user_id).emit("log", `tidak ada pesan yang bisa di proses`);
}
} catch (error) {
io.to(postdata.user_id).emit("log", `Gagal template pesan kosong`);
console.log(error)
KeiLog("INFO", "Terjadi error")
}
}
/**
* Formats a given date and time into a string representation.
*
* @param {Date} date - The date to be formatted.
* @return {string} The formatted date and time string in the format 'YYYY-MM-DD HH:mm:ss'.
*/
function formatDateTime(date) {
const pad = (num) => (num < 10 ? '0' + num : num);
const year = date.getFullYear();
const month = pad(date.getMonth() + 1);
const day = pad(date.getDate());
const hours = pad(date.getHours());
const minutes = pad(date.getMinutes());
const seconds = pad(date.getSeconds());
return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
}
/**
* Updates the inbox log assign.
*
* @param {Object} Data - The data object containing properties such as phone_id, tim, dept, idagent, idtl, idsv, idpb, idpd, and nomor.
* @return {Promise} A promise that resolves with the result of the query execution or rejects with an error.
*/
const updateInboxLogAssign = async (Data) => {
try {
const query = `UPDATE tb_log_pesan_${datetb} SET phone_id='${Data.phone_id}', tim='${Data.tim}', dept='${Data.dept}', user_id='${Data.idagent}', id_tl='${Data.idtl}', id_sv='${Data.idsv}', id_pb='${Data.idpb}', id_pd='${Data.idpd}', status='Aktif' WHERE tim!='' AND recvIsGroup is null AND nomor='${Data.nomor}'`;
KeiLog("INFO", `Update Inbox Log Assign`);
return await executeQuery(query);
} catch (error) {
KeiLog("ERROR", `'Error in updateInboxLogAssign: ${error.message}`);
return error;
}
};
/**
* Updates the outbox log assignment.
*
* @param {Object} Data - The data object containing the following properties:
* - phone_id {string} - The phone ID.
* - tim {string} - The time.
* - dept {string} - The department.
* - idagent {string} - The agent ID.
* - idtl {string} - The TL ID.
* - idsv {string} - The SV ID.
* - idpb {string} - The PB ID.
* - idpd {string} - The PD ID.
* - status {string} - The status.
* - unomor {string} - The unomor.
* @return {Promise<any>} A promise that resolves with the result of the query execution.
*/
const updateOutboxLogAssign = async (Data) => {
try {
const query = `UPDATE tb_log_pesan_${datetb} SET phone_id='${Data.phone_id}', tim='${Data.tim}', dept='${Data.dept}', user_id='${Data.idagent}', id_tl='${Data.idtl}', id_sv='${Data.idsv}', id_pb='${Data.idpb}', id_pd='${Data.idpd}', status='Aktif', nomor='${Data.unomor}' WHERE tim!='' AND recvIsGroup='2' AND nama='${Data.nomor}'`;
KeiLog("INFO", "Update Outbox Log Assign");
return await executeQuery(query);
} catch (error) {
KeiLog("ERROR", `Error in updateOutboxLogAssign: ${error.message}`);
}
};
/**
* Updates the inbox log to unassign a message.
*
* @param {Object} Data - The data object containing the necessary information for updating the inbox log.
* @param {string} Data.phone_id - The phone ID of the message.
* @param {string} Data.tim - The timestamp of the message.
* @param {string} Data.dept - The department of the message.
* @param {string} Data.idagent - The ID of the agent who unassigned the message.
* @param {string} Data.idtl - The ID of the team leader.
* @param {string} Data.idsv - The ID of the supervisor.
* @param {string} Data.idpb - The ID of the PB.
* @param {string} Data.idpd - The ID of the PD.
* @param {string} Data.nomor - The number of the message.
* @return {Promise} A promise that resolves with the result of the query.
*/
const updateInboxLogUnAssign = async (Data) => {
try {
const query = `UPDATE tb_log_pesan_${datetb} SET template_pesan='unassigned', phone_id='${Data.phone_id}', tim='${Data.tim}', dept='${Data.dept}', user_id='${Data.idagent}', id_tl='${Data.idtl}', id_sv='${Data.idsv}', id_pb='${Data.idpb}', id_pd='${Data.idpd}', status='Aktif' WHERE tim!='' AND recvIsGroup is null AND nomor='${Data.nomor}'`;
return await executeQuery(query);
} catch (error) {
console.error('Error in updateInboxLogUnAssign:', error);
}
};
/**
* Updates the outbox log by unassigning the specified data.
*
* @param {Object} Data - The data to be used for updating the outbox log.
* @param {string} Data.phone_id - The phone ID.
* @param {string} Data.tim - The time.
* @param {string} Data.dept - The department.
* @param {string} Data.idagent - The agent ID.
* @param {string} Data.idtl - The TL ID.
* @param {string} Data.idsv - The SV ID.
* @param {string} Data.idpb - The PB ID.
* @param {string} Data.idpd - The PD ID.
* @param {string} Data.unomor - The unomor value.
* @return {Promise} A promise that resolves with the result of the update query.
*/
const updateOutboxLogUnAssign = async (Data) => {
try {
const query = `UPDATE tb_log_pesan_${datetb} SET template_pesan='unassigned', phone_id='${Data.phone_id}', tim='${Data.tim}', dept='${Data.dept}', user_id='${Data.idagent}', id_tl='${Data.idtl}', id_sv='${Data.idsv}', id_pb='${Data.idpb}', id_pd='${Data.idpd}', status='Aktif', nomor='${Data.unomor}' WHERE tim!='' AND recvIsGroup='2' AND nama='${Data.nomor}'`;
KeiLog("INFO", "Update Outbox Log Unassign");
return await executeQuery(query);
} catch (error) {
KeiLog("ERROR", `Error in updateOutboxLogUnAssign: ${error.message}`);
}
};
/**
* Inserts a record into the tb_log_pesan table.
*
* @param {Object} Data - The data object containing the details of the record to be inserted.
* @return {Promise} A promise that resolves with the result of the execution.
*/
const insertTbLogPesan = async (Data) => {
try {
const query = `INSERT INTO tb_log_pesan_${datetb} (tb_wa_id, recvIsGroup, url, filename, tim, dept, id_tl, id_sv, id_pb, id_pd, user_id, name, nama_agent, no_agent, tim_agent, dept_agent, pesan, nomor, nama, status_pesan, created_date, template_pesan, templates, initiate,phone_id,display_number) VALUES ('${Data.waid}', '2', '${Data.urlmedia}', '${Data.files}', '${Data.tim}', '${Data.dept}', '${Data.idtl}', '${Data.idsv}', '${Data.idpb}', '${Data.idpd}', '${Data.idagent}', '${Data.nama}', '${Data.uname}', '${Data.unomor}', '${Data.tim}', '${Data.dept}', '${_escapeString(Data.tpls)}', '${Data.unomor}', '${Data.nomor}', 'sent', '${Data.date}', '${Data.tplgrp}', '${Data.template_pesan}', '${Data.init}','${Data.phone_id}', '${Data.display_number}')`;
KeiLog("INFO", "Update insertTbLogPesan");
return await executeQuery(query);
} catch (error) {
KeiLog("ERROR", `Error in insertTbLogPesan: ${error.message}`);
}
};
/**
* Updates the template flag in the tb_pesan_${datetb} table.
*
* @param {Object} Data - The data used to update the template flag.
* @param {string} Data.nomor - The value used to identify the record to update.
* @return {Promise} A promise that resolves with the result of the update query.
*/
const updateTpl1 = async (Data) => {
const query = `UPDATE tb_pesan_${datetb} SET tpl_flag='0' WHERE tpl_flag='1' AND nomor = '${Data.nomor}'`;
return await executeQuery(query);
};
/**
* Updates the tpl_flag of the tb_nomor_${datetb} table to '0' for the given nomor in the Data object.
*
* @param {object} Data - The data needed to update the tpl_flag.
* @param {string} Data.nomor - The nomor for which the tpl_flag needs to be updated.
* @returns {Promise} A promise that resolves with the result of the executeQuery function.
*/
const updateTpl2 = async (Data) => {
const query = `UPDATE tb_nomor_${datetb} SET tpl_flag='0' WHERE tpl_flag='1' AND nomor = '${Data.nomor}'`;
return await executeQuery(query);
};
const insertTbPesan = async (Data) => {
try {
const query = `INSERT INTO tb_pesan_${datetb} (tb_pesan_wa_id, tb_agent_id, tb_nomor_${datetb}_id, nama, nomor, account_number, nomor_agent, id_agent, template_pesan, media, tim, dept, user_id, id_sv, id_pb, id_pd, user_level, tpl_flag, status_pesan, created_date, date_session,phone_id,display_number) VALUES ('${Data.waid}', '${Data.idtl}', '${Data.idnomor}', '${Data.nama}', '${Data.nomor}', '${Data.account_number}', '${Data.nomor_agent}', '${Data.idagent}', '${Data.template_pesan}', '${Data.media}', '${Data.tim}', '${Data.dept}', '${Data.idtl}', '${Data.idsv}', '${Data.idpb}', '${Data.idpd}', '${Data.role}', '1', '${Data.status}', '${Data.date}', '${Data.date}','${Data.phone_id}', '${Data.display_number}')`;
return await executeQuery(query);
} catch (error) {
KeiLog("ERROR", `Error in insertTbPesan: ${error.message}`);
}
};
const updateTbNomor = async (Data) => {
const query = `UPDATE tb_nomor_${datetb} SET flag='${Data.flag}', phone_id='${Data.phone_id}', display_number='${Data.display_number}', tpl_flag=1 WHERE tb_nomor_${datetb}_id = '${Data.idnomor}'`;
return await executeQuery(query);
};
module.exports = {
Blast
}