· 4 years ago · Sep 01, 2021, 10:38 AM
1const conn = require('../../public/customrDB');
2const commonSql = require('../basecontroller');
3const moment = require('moment');
4const utility = require('../../common_function/utility');
5
6const { customrrLogger } = require('../../logs/winston.logs');
7const multer = require('multer');
8const AWS = require('aws-sdk');
9const multerS3 = require('multer-s3');
10const custAssign = require('../assigncustomers');
11const commonSettings = require('../../public/commonSetting');
12
13const path = require('path');
14const fs = require('fs');
15const Excel = require('exceljs');
16const mailModel = require('../mail');
17const leadSquared = require('../leadsquared');
18
19exports.getCustomersInfoList = async (req, loggedUser, res) => {
20 try {
21 const { query } = req;
22 let { page = 0, perpage = 20, search = '', agents = '', product = '', status = '', last_satisfaction_level = '', target_date = '', campaign_id = '' } = query;
23
24 var returnData = {
25 dataobject: [],
26 count: 0,
27 page: page,
28 perpage: perpage,
29 pages: 0
30 }
31
32 let agentClause = ``;
33 if (loggedUser.role === 3) {
34 agentClause = ` WHERE (cu.email = '${loggedUser.email}' OR cuta.email = '${loggedUser.email}') AND c.customer_type = 'active'`
35 } else {
36 agentClause = ` WHERE c.customer_type = 'active'`
37 }
38
39
40 // page = 3 ;
41 // perpage = 10;
42
43 let agentsClause = ``;
44 if (agents) {
45 let splittedAgents = agents.split(",")
46 let agents_sub = ``;
47 splittedAgents.forEach(element => {
48 agents_sub += `'${element}',`
49 });
50
51 agents_sub = agents_sub.slice(0, -1);
52
53 agentsClause = `AND (qry.agent_mail IN(${agents_sub}) OR qry.tr_agent_mail IN(${agents_sub}))`
54 }
55
56 let statusClause = ``;
57 if (status) {
58 let splittedStatus = status.split(",")
59 statusClause = `AND qry.status_name IN (`;
60 splittedStatus.forEach(element => {
61 statusClause += `'${element}',`
62 });
63
64 statusClause = statusClause.slice(0, -1);
65 statusClause += `)`
66 // console.log(statusClause);
67 }
68
69 let satisfactionClause = ``;
70 if (last_satisfaction_level) {
71 let splittedSatisfaction = last_satisfaction_level.split(",")
72 satisfactionClause = `AND qry.satisfaction_level IN (`;
73 splittedSatisfaction.forEach(element => {
74 satisfactionClause += `'${element}',`
75 });
76
77 satisfactionClause = satisfactionClause.slice(0, -1);
78 satisfactionClause += `)`
79 }
80
81 let productClause = ``;
82 if (product) {
83 let splittedProduct = product.split(",")
84 productClause += `AND (`;
85 if (splittedProduct.indexOf('7') != -1) { //If combo
86 productClause += ` (FIND_IN_SET(1, qry.prod_ids) AND FIND_IN_SET(12, qry.prod_ids)) OR
87 (FIND_IN_SET(11, qry.prod_ids) AND FIND_IN_SET(13, qry.prod_ids))`
88
89 for (let d = splittedProduct.length - 1; d > -1; d--) {
90 if (splittedProduct[d] == '5' || splittedProduct[d] == '6' || splittedProduct[d] == '7' || splittedProduct[d] == '8') {
91 splittedProduct.splice(d, 1);
92 }
93 }
94 if (splittedProduct.length > 0) {
95 productClause += `OR`
96 }
97 }
98 splittedProduct.forEach(function (elm, index) {
99 if (index == 0) {
100 } else {
101 productClause += `OR`;
102 }
103 productClause += ` FIND_IN_SET(${elm}, qry.prod_ids) `
104 })
105 productClause += ` ) `;
106
107 }
108
109 // console.log(productClause)
110
111 let target_dateClause = ``;
112 if (target_date) {
113
114 let targeet_date_formatet = moment(target_date).format('YYYY-MM-DD HH:mm:ss');
115 target_dateClause = `AND (qry.target_date <> 'N.A' AND qry.target_date <= '${targeet_date_formatet}')`
116 }
117
118 let campaign_idClause = ``;
119 if (campaign_id) {
120
121 let splittedCampaign_id = campaign_id.split(",");
122 campaign_idClause = `AND qry.campaign_id IN (`;
123 splittedCampaign_id.forEach(element => {
124 campaign_idClause += `'${element}',`
125 });
126
127 campaign_idClause = campaign_idClause.slice(0, -1);
128 campaign_idClause += `)`
129 }
130
131 let mainQry = `Select * from (
132 Select c.lead_id, c.full_name as customer_name, c.leadsquared_id,c.user_id,
133 cu.email as agent_mail, cuta.email as tr_agent_mail,
134 JSON_ARRAYAGG(JSON_OBJECT("name", cu.full_name, "email", cu.email)) agents,
135 JSON_ARRAYAGG(JSON_OBJECT("name", cuta.full_name, "email", cuta.email)) transfer_agents,
136 COALESCE(cr.satisfaction_level, "N.A") as satisfaction_level,
137 COALESCE(c.campaign_id, "N.A") as campaign_id, COALESCE(camp.name, "N.A") as campaign_name,
138 COALESCE(camp.target_date, "N.A") as target_date,
139 COALESCE(camp.start_date, "N.A") as campaign_start_date, COALESCE(camp.end_date, "N.A") as campaign_end_date,
140 a.status_name, COALESCE(cr.last_contacted_date, "N.A") as last_contacted,
141 JSON_ARRAYAGG(JSON_OBJECT("prod_id", rrp.product_id, 'expiry', rrp.expiry_date, 'title', price.product_type)) products_list,
142 GROUP_CONCAT(rrp.product_id SEPARATOR ',') prod_ids,
143 rru.last_activity,
144 (Select dw_port.porifolio_name
145 from rr_dw_user_portfolio as dw_user_port
146 LEFT JOIN rr_dw_portfolio as dw_port ON dw_port.id = dw_user_port.portfolio_id
147 WHERE dw_user_port.user_id = c.user_id) as dw_portfolio,
148 pp.title as fnf_portfolio,
149
150 (Select cm.compname
151 from rr_user_monthly_recommendation as umr
152 LEFT JOIN rr_monthly_recommendation as mr ON mr.id = umr.recommended_id
153 LEFT JOIN rr_company_master as cm ON cm.fincode = mr.fincode
154 WHERE umr.user_id = c.user_id AND umr.product_id = 12 ORDER BY umr.id desc limit 1) as mpo_recommend
155
156 from rnr_crr_customers as c
157 LEFT JOIN rnr_crr_users as cu ON (cu.email = c.original_agent AND c.transfer_agent IS NULL)
158 LEFT JOIN rnr_crr_users as cuta ON (cuta.email = c.transfer_agent)
159 LEFT JOIN (SELECT c1.lead_id, c1.satisfaction_level, c1.last_contacted_date
160 FROM rnr_crr_call_records c1
161 WHERE c1.id = (SELECT MAX(c2.id)
162 FROM rnr_crr_call_records c2
163 WHERE c2.lead_id = c1.lead_id )) as cr ON (c.lead_id = cr.lead_id AND c.active_status = 5)
164 LEFT JOIN rnr_crr_campaigns as camp ON c.campaign_id = camp.campaign_id
165 LEFT JOIN rr_users as rru ON rru.id = c.user_id
166 LEFT JOIN rnr_crr_active_status as a ON a.status_id = c.active_status
167 LEFT JOIN rr_products as rrp ON (c.user_id = rrp.user_id AND rrp.expiry_date >= curdate())
168 LEFT JOIN rr_pricing as price ON rrp.product_id = price.id
169 LEFT JOIN rr_user_variables as uv ON uv.user_id = c.user_id AND uv.data_key = 'personalized_portfolio'
170 LEFT JOIN rr_personalized_portfolio as pp ON pp.id = uv.value
171 ${agentClause}
172 GROUP BY lead_id
173 )as qry
174 WHERE (qry.customer_name LIKE '%${search}%' OR qry.lead_id LIKE '%${search}%' OR qry.campaign_id LIKE '%${search}%')
175 ${agentsClause} ${statusClause} ${campaign_idClause} ${satisfactionClause} ${productClause} ${target_dateClause}`;
176 let limitCustomerQry = `${mainQry} LIMIT ${page * perpage}, ${perpage}`;
177
178 customersData = await commonSql.getDataFromMysqlByGivenQuery(limitCustomerQry, 1);
179 if (customersData == null) {
180 return res(null, returnData);
181 }
182
183 let customersTotalData = await commonSql.getDataFromMysqlByGivenQuery(mainQry, 1);
184
185 for (let i = 0; i < customersData.length; i++) {
186 if (customersData[i].last_contacted != "N.A") {
187 let newdate = moment(customersData[i].last_contacted).subtract({ 'hours': 5, 'minutes': 30 }).format('YYYY-MM-DD HH:mm:ss');
188 customersData[i].since_last_contacted = moment(newdate).fromNow();
189 }
190 customersData[i].agents = customersData[i].agents[0]
191 customersData[i].transfer_agents = customersData[i].transfer_agents[0]
192 if (customersData[i].agents.name == null) {
193 customersData[i].agent = customersData[i].transfer_agents;
194 } else {
195 customersData[i].agent = customersData[i].agents;
196 }
197 if (customersData[i].status_name != "Feedback Done") {
198 customersData[i].satisfaction_level = "N.A";
199 }
200 delete customersData[i].agents;
201 delete customersData[i].transfer_agents;
202 delete customersData[i].agent_mail;
203 delete customersData[i].tr_agent_mail;
204 delete customersData[i].prod_ids;
205
206 for (let p = 0; p < customersData[i].products_list.length; p++) {
207 let prod = customersData[i].products_list[p];
208 if (prod.prod_id == 1) {
209 prod.portfolio_info = customersData[i].fnf_portfolio;
210 } else if (prod.prod_id == 12) {
211 prod.portfolio_info = customersData[i].mpo_recommend;
212 } else if (prod.prod_id == 14) {
213 prod.portfolio_info = customersData[i].dw_portfolio;
214 } else {
215 prod.portfolio_info = "";
216 }
217 }
218 customersData[i].last_activity = moment(customersData[i].last_activity).format('YYYY-MM-DD HH:mm:ss');
219
220 delete customersData[i].fnf_portfolio;
221 delete customersData[i].mpo_recommend;
222 delete customersData[i].dw_portfolio;
223 }
224
225 returnData.dataobject = customersData;
226 returnData.count = customersTotalData.length;
227 returnData.page = page;
228 returnData.perpage = perpage;
229 returnData.pages = Math.ceil(returnData.count / perpage);
230
231 return res(null, returnData);
232 }
233 catch (e) {
234 console.log("Error in User Service getCustomersInfoList()", e.message);
235 return res(e.message, null);
236 }
237}
238
239function getSatisfactionStatusId(satisfactionStatus) {
240 switch (satisfactionStatus) {
241 case 'red':
242 return 18
243 case 'yellow':
244 return 19;
245 case 'green':
246 return 20;
247 case 'grey':
248 return 21;
249 case 'cyan':
250 return 24;
251 }
252}
253
254exports.getCustomersInfoList_opt = async (req, loggedUser, res) => {
255 try {
256 const { query } = req;
257 let { page = 0, perpage = 20, search = '', agents = '', product = '', status = '', last_satisfaction_level = '', target_date = '', campaign_id = '' } = query;
258
259 var returnData = {
260 dataobject: [],
261 count: 0,
262 page: page,
263 perpage: perpage,
264 pages: 0
265 }
266
267 let agentClause = ``;
268 if (loggedUser.role === 3) {
269 agentClause = ` WHERE cu.email = '${loggedUser.email}' AND c.customer_type = 'active'`
270 } else {
271 agentClause = ` WHERE c.customer_type = 'active'`
272 }
273
274 let agentsClause = ``;
275 if (agents) {
276 let splittedAgents = agents.split(",")
277 let agents_sub = ``;
278 splittedAgents.forEach(element => {
279 agents_sub += `'${element}',`
280 });
281
282 agents_sub = agents_sub.slice(0, -1);
283
284 agentsClause = `AND (qry.agent_email IN(${agents_sub}))`
285 }
286
287 let statusClause = ``;
288 if (status) {
289 let splittedStatus = status.split(",")
290 statusClause = `AND qry.status_name IN (`;
291 splittedStatus.forEach(element => {
292 statusClause += `'${element}',`
293 });
294
295 statusClause = statusClause.slice(0, -1);
296 statusClause += `)`
297 }
298
299 let satisfactionClause = ``;
300 if (last_satisfaction_level) {
301 let splittedSatisfaction = last_satisfaction_level.split(",")
302 satisfactionClause = `AND qry.last_activity_status IN (`;
303 splittedSatisfaction.forEach(element => {
304 satisfactionClause += `${getSatisfactionStatusId(element)},`
305 });
306
307 satisfactionClause = satisfactionClause.slice(0, -1);
308 satisfactionClause += `)`
309 }
310
311 let productClause = ``;
312 if (product) {
313 let splittedProduct = product.split(",")
314 productClause += `AND (`;
315 if (splittedProduct.indexOf('7') != -1) { //If combo
316 productClause += ` (FIND_IN_SET(1, qry.prod_ids) AND FIND_IN_SET(12, qry.prod_ids)) OR
317 (FIND_IN_SET(11, qry.prod_ids) AND FIND_IN_SET(13, qry.prod_ids))`
318
319 for (let d = splittedProduct.length - 1; d > -1; d--) {
320 if (splittedProduct[d] == '5' || splittedProduct[d] == '6' || splittedProduct[d] == '7' || splittedProduct[d] == '8') {
321 splittedProduct.splice(d, 1);
322 }
323 }
324 if (splittedProduct.length > 0) {
325 productClause += `OR`
326 }
327 }
328 splittedProduct.forEach(function (elm, index) {
329 if (index == 0) {
330 } else {
331 productClause += `OR`;
332 }
333 productClause += ` FIND_IN_SET(${elm}, qry.prod_ids) `
334 })
335 productClause += ` ) `;
336
337 }
338
339 let target_dateClause = ``;
340 if (target_date) {
341
342 let targeet_date_formatet = moment(target_date).format('YYYY-MM-DD HH:mm:ss');
343 target_dateClause = `AND (qry.target_date <> 'N.A' AND qry.target_date <= '${targeet_date_formatet}')`
344 }
345
346 let campaign_idClause = ``;
347 if (campaign_id) {
348
349 let splittedCampaign_id = campaign_id.split(",");
350 campaign_idClause = `AND qry.campaign_id IN (`;
351 splittedCampaign_id.forEach(element => {
352 campaign_idClause += `'${element}',`
353 });
354
355 campaign_idClause = campaign_idClause.slice(0, -1);
356 campaign_idClause += `)`
357 }
358
359 let mainQry = `Select * from (
360 Select c.lead_id, c.full_name as customer_name, c.leadsquared_id,c.user_id,
361 c.last_activity_status, c.last_contacted_date as last_contacted,
362 cu.email as agent_email, cu.full_name as agent_name,
363 c.campaign_id, camp.name as campaign_name, camp.target_date,
364 camp.start_date as campaign_start_date, camp.end_date as campaign_end_date,
365 a.status_name,
366 JSON_ARRAYAGG(JSON_OBJECT("prod_id", rrp.product_id, 'expiry', rrp.expiry_date, 'title', price.product_type)) products_list,
367 GROUP_CONCAT(rrp.product_id SEPARATOR ',') prod_ids,
368 rru.last_activity,
369 pp.title as fnf_portfolio
370
371 from rnr_crr_customers as c
372 LEFT JOIN rnr_crr_campaigns as camp ON c.campaign_id = camp.campaign_id
373 LEFT JOIN rnr_crr_users as cu ON cu.email = (case
374 when c.transfer_agent IS NULL then c.original_agent
375 else c.transfer_agent
376 end)
377 LEFT JOIN rr_users as rru ON rru.id = c.user_id
378 LEFT JOIN rnr_crr_active_status as a ON a.status_id = c.active_status
379 LEFT JOIN rr_products as rrp ON (c.user_id = rrp.user_id AND rrp.expiry_date >= curdate())
380 LEFT JOIN rr_pricing as price ON rrp.product_id = price.id
381 LEFT JOIN rr_personalized_portfolio as pp ON pp.id = rru.portfolio
382 ${agentClause} AND (c.full_name LIKE '%${search}%' OR c.lead_id LIKE '%${search}%')
383 GROUP BY lead_id
384 )as qry
385 WHERE 1=1
386 ${agentsClause} ${statusClause} ${campaign_idClause} ${satisfactionClause}
387 ${productClause} ${target_dateClause}`;
388
389 // console.log(mainQry);
390 let limitCustomerQry = `${mainQry} LIMIT ${page * perpage}, ${perpage}`;
391 customersData = await commonSql.getDataFromMysqlByGivenQuery(limitCustomerQry, 1);
392
393 if (customersData == null) {
394 return res(null, returnData);
395 }
396
397 let mainQry_opt = `Select COUNT(1) as total from (${mainQry}) as tc`;
398 let customersTotalData = await commonSql.getDataFromMysqlByGivenQuery(mainQry_opt, 0);
399
400 let allUser_ids = [];
401
402 for (let i = 0; i < customersData.length; i++) {
403 allUser_ids.push(customersData[i].user_id);
404 if (customersData[i].last_activity_status == 18) {
405 customersData[i].satisfaction_level = "Red";
406 } else if (customersData[i].last_activity_status == 19) {
407 customersData[i].satisfaction_level = "Yellow";
408 } else if (customersData[i].last_activity_status == 20) {
409 customersData[i].satisfaction_level = "Green";
410 } else if (customersData[i].last_activity_status == 21) {
411 customersData[i].satisfaction_level = "Grey";
412 } else if (customersData[i].last_activity_status == 24) {
413 customersData[i].satisfaction_level = "Cyan";
414 } else {
415 customersData[i].satisfaction_level = "N.A";
416 }
417
418 // console.log(customersData[i].last_contacted)
419
420 customersData[i].campaign_id = customersData[i].campaign_id == null ? "N.A" : customersData[i].campaign_id;
421 customersData[i].campaign_name = customersData[i].campaign_name == null ? "N.A" : customersData[i].campaign_name;
422 customersData[i].target_date = customersData[i].target_date == null ? "N.A" : customersData[i].target_date;
423 customersData[i].campaign_start_date = customersData[i].campaign_start_date == null ? "N.A" : customersData[i].campaign_start_date;
424 customersData[i].campaign_end_date = customersData[i].campaign_end_date == null ? "N.A" : customersData[i].campaign_end_date;
425 customersData[i].last_contacted = customersData[i].last_contacted == null ? "N.A" : customersData[i].last_contacted;
426
427 if (customersData[i].last_contacted != "N.A") {
428 let newdate = moment(customersData[i].last_contacted).subtract({ 'hours': 5, 'minutes': 30 }).format('YYYY-MM-DD HH:mm:ss');
429 customersData[i].since_last_contacted = moment(newdate).fromNow();
430 }
431
432 customersData[i].agent = {
433 name: customersData[i].agent_name,
434 email: customersData[i].agent_email
435 }
436
437 if (customersData[i].status_name != "Feedback Done") {
438 customersData[i].satisfaction_level = "N.A";
439 }
440 delete customersData[i].agent_name;
441 delete customersData[i].agent_email;
442 delete customersData[i].prod_ids;
443
444 customersData[i].products_list.sort(function (a, b) {
445 return a.prod_id - b.prod_id;
446 });
447 customersData[i].last_activity = moment(customersData[i].last_activity).format('YYYY-MM-DD HH:mm:ss');
448 }
449
450 let getProductsPortfolioQry = `Select DISTINCT rrp.user_id,
451 JSON_ARRAYAGG(cm.compname ) as mpo_recommend,
452 dw_port.porifolio_name
453 FROM rr_products as rrp
454 LEFT JOIN rr_user_monthly_recommendation as umr ON (umr.user_id = rrp.user_id AND umr.product_id = 12)
455 LEFT JOIN rr_monthly_recommendation as mr ON mr.id = umr.recommended_id
456 LEFT JOIN rr_company_master as cm ON cm.fincode = mr.fincode
457 LEFT JOIN rr_dw_user_portfolio as dw_user_port ON dw_user_port.user_id = rrp.user_id
458 LEFT JOIN rr_dw_portfolio as dw_port ON dw_port.id = dw_user_port.portfolio_id
459 WHERE rrp.user_id IN (${allUser_ids}) AND rrp.product_id = 12
460 group by rrp.user_id`;
461 let getProductsPortfolioRes = await commonSql.getDataFromMysqlByGivenQuery(getProductsPortfolioQry, 1);
462 for (let i = 0; i < customersData.length; i++) {
463
464 for (let p = 0; p < customersData[i].products_list.length; p++) {
465 let prod = customersData[i].products_list[p];
466 if (prod.prod_id == 12) {
467 let pfIndex = getProductsPortfolioRes.map(c => c.user_id).indexOf(customersData[i].user_id);
468 let mpoRecomm = getProductsPortfolioRes[pfIndex].mpo_recommend.filter(function (el) {
469 return el != null;
470 });
471 prod.portfolio_info = mpoRecomm.reverse();
472
473 } else if (prod.prod_id == 14) {
474
475 let pfIndex = getProductsPortfolioRes.map(c => c.user_id).indexOf(customersData[i].user_id);
476 let dwn_portfolio = "";
477 if (pfIndex != -1) {
478 dwn_portfolio = getProductsPortfolioRes[pfIndex].porifolio_name;
479 }
480 prod.portfolio_info = [dwn_portfolio];
481
482 } else if (prod.prod_id == 1) {
483 prod.portfolio_info = [customersData[i].fnf_portfolio];
484
485 } else {
486 prod.portfolio_info = "";
487 }
488
489 delete customersData[i].fnf_portfolio;
490 }
491 }
492
493 returnData.dataobject = customersData;
494 returnData.count = customersTotalData.total;
495 returnData.page = page;
496 returnData.perpage = perpage;
497 returnData.pages = Math.ceil(returnData.count / perpage);
498
499 return res(null, returnData);
500 }
501 catch (e) {
502 console.log("Error in User Service getCustomersInfoList_opt()", e.message);
503 return res(e.message, null);
504 }
505}
506
507
508exports.getAgentsList = async (loggedUser, reqData, res) => {
509 try {
510 const agentsQry = `Select full_name, email from rnr_crr_users
511 where role = 3`;
512 let agentsData = await commonSql.getDataFromMysqlByGivenQuery(agentsQry, 1);
513 if (agentsData == null) {
514 return res("No agents found", null);
515 }
516 return res(null, agentsData);
517
518 }
519 catch (e) {
520 console.log("Error in Customr Service getAgentsList()", e.message);
521 customrrLogger.error("Error in Customr Service getAgentsList()", e);
522 return res(e.message, null);
523 }
524}
525
526exports.getCampaignTargetDates = async (loggedUser, reqData, res) => {
527 try {
528 const campaignsQry = `Select campaign_id, name, target_date from rnr_crr_campaigns`;
529 let campaignsData = await commonSql.getDataFromMysqlByGivenQuery(campaignsQry, 1);
530 if (campaignsData == null) {
531 return res("No campaigns found", null);
532 }
533 return res(null, campaignsData);
534
535 }
536 catch (e) {
537 console.log("Error in Customr Service getCampaignTargetDates()", e.message);
538 customrrLogger.error("Error in Customr Service getCampaignTargetDates()", e);
539 return res(e.message, null);
540 }
541}
542
543async function getParentStatusId(activity_status) {
544 let status_id = 1;
545 // if (activity_status > 1 && activity_status <= 6) {
546 // status_id = 2;
547 // } else if (activity_status > 6 && activity_status <= 11) {
548 // status_id = 7;
549 // } else if (activity_status > 11 && activity_status <= 32) {
550 // status_id = 12;
551 // } else if (activity_status == 33) {
552 // status_id = 33;
553 // }
554 let parentStatusIdQry = `Select parent_status_id from rnr_crr_active_status
555 WHERE status_id = ${activity_status}`;
556 let parentStatusIdRes = await commonSql.getDataFromMysqlByGivenQuery(parentStatusIdQry, 0);
557 if (parentStatusIdRes != null) {
558 status_id = parentStatusIdRes.parent_status_id;
559 }
560
561 return status_id;
562}
563
564
565exports.addActivity = async (loggedUser, reqData, res) => {
566 try {
567 let followup_activities = [6, 7, 13];
568 let followupClause = "";
569 var followup_datetime = 0;
570 reqData.activity_status = +reqData.activity_status;
571
572 if (followup_activities.indexOf(+reqData.activity_status) != -1) {
573
574 if (reqData.followup_date != "Invalid date") {
575 let jsDateObj = new Date(reqData.followup_date);
576
577 let timeObj = moment(reqData.followup_time, ["h:mm A"]).format("HH:mm");
578 let dateObj = moment(jsDateObj).set({ 'hour': 0, 'minute': 0, 'second': 0, 'millisecond': 0 });
579 // console.log(jsDateObj, timeObj, dateObj);
580
581 followup_datetime = moment(dateObj).add(timeObj.split(":")[0], 'hours')
582 .add(timeObj.split(":")[1], 'minutes').format('YYYY-MM-DD HH:mm:ss')
583
584
585 //update any previous followups status to true
586 let updateFupStatusQry = `Update rnr_crr_activity SET followup_status = '1'
587 WHERE lead_id = ${reqData.leadId}`;
588 let followupUpdateRes = await commonSql.updateByQuery(updateFupStatusQry);
589
590 followupClause = `,followup_date = '${followup_datetime}', followup_status = 'Active'`
591 }
592 }
593
594 const activityTitleQry = `Select status_id, status_name, parent_status_id from rnr_crr_active_status
595 where status_id = ${reqData.activity_status}`;
596 let activityTitleRes = await commonSql.getDataFromMysqlByGivenQuery(activityTitleQry, 0);
597
598
599 let status_id = activityTitleRes.parent_status_id; //await getParentStatusId(reqData.activity_status);
600 let last_contacted_date = `, last_contacted_date = '0'`;
601 if ([5, 18, 19, 20, 21, 24].indexOf(reqData.activity_status) != -1) {
602 last_contacted_date = `, last_contacted_date = now()`;
603 }
604
605 //update the latest activity in customrr table also
606 let currStatusUpdateQry = `Update rnr_crr_customers SET active_status = ${status_id},
607 updated_at = now() ${followupClause} ${last_contacted_date}, last_activity_status = ${reqData.activity_status}
608 WHERE lead_id = ${reqData.leadId}`;
609 let currStatusUpdateRes = await commonSql.updateByQuery(currStatusUpdateQry);
610
611
612 let responseObj = await addActivityIntoLeadSquared(reqData, loggedUser.email);
613 let lsParamValue = "";
614 if (responseObj != null) {
615 console.log(responseObj)
616 lsParamValue = responseObj.Message.Id;
617 }
618
619 let paramsName = "";
620 let paramsValue = "";
621 if (followupClause != "") {
622 // paramsName = 'lead_id, activity_type, activity_status, title, description, created_by,followup_date,followup_status, activity_ls_id';
623 // paramsValue = `${reqData.leadId}, 'activity', ${+reqData.activity_status}, '${activityTitleRes.status_name}', '${reqData.comments}', '${loggedUser.email}','${followup_datetime}', 0, '${lsParamValue}'`;
624
625 conn.query(`INSERT INTO rnr_crr_activity (lead_id,activity_type,activity_status,title,description,created_by,followup_date,followup_status, activity_ls_id) VALUES (?,?,?,?,?,?,?,?,?)`,
626 [
627 reqData.leadId,
628 'activity',
629 +reqData.activity_status,
630 activityTitleRes.status_name,
631 reqData.comments,
632 loggedUser.email,
633 followup_datetime,
634 '0',
635 lsParamValue
636 ],
637 async (error, notification_results, fields) => {
638 if (error) {
639 return res("Failed to add activity", null);
640 }
641 })
642
643
644
645
646 } else {
647 // paramsName = 'lead_id, activity_type, activity_status, title, description, created_by, activity_ls_id';
648 // paramsValue = `${reqData.leadId}, 'activity', ${+reqData.activity_status}, '${activityTitleRes.status_name}', '${reqData.comments}', '${loggedUser.email}', '${lsParamValue}'`;
649
650 conn.query(`INSERT INTO rnr_crr_activity (lead_id,activity_type,activity_status,title,description,created_by, activity_ls_id) VALUES (?,?,?,?,?,?,?)`,
651 [
652 reqData.leadId,
653 'activity',
654 +reqData.activity_status,
655 activityTitleRes.status_name,
656 reqData.comments,
657 loggedUser.email,
658 lsParamValue
659 ],
660 async (error, notification_results, fields) => {
661 if (error) {
662 return res("Failed to add activity", null);
663 }
664 })
665 }
666
667 // const insertedActivity = await commonSql.insertQuery('rnr_crr_activity', paramsName, paramsValue);
668 // if (insertedActivity == false) {
669 // return res("Failed to add activity", null);
670 // }
671
672
673 return res(null, {});
674 }
675 catch (e) {
676 console.log("Error in Customr Service addActivity()", e.message);
677 customrrLogger.error("Error in Customr Service addActivity()", e);
678 return res(e.message, null);
679 }
680}
681
682exports.editActivity = async (loggedUser, reqData, res) => {
683 try {
684 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
685 if (checkActivityAuth == false) {
686 return res("Unauthorized activity", null);
687 }
688 reqData.activity_status = +reqData.activity_status;
689 let followup_activities = [6, 7, 13];
690 let followupClause = "";
691 var followup_datetime;
692
693 let followupActivityUpdateClause = "";
694 if (followup_activities.indexOf(reqData.activity_status) != -1) {
695
696 if (reqData.followup_date != "Invalid date") {
697 let jsDateObj = new Date(reqData.followup_date);
698 let timeObj = moment(reqData.followup_time, ["h:mm A"]).format("HH:mm");
699 let dateObj = moment(jsDateObj).set({ 'hour': 0, 'minute': 0, 'second': 0, 'millisecond': 0 });
700 followup_datetime = moment(dateObj).add(timeObj.split(":")[0], 'hours')
701 .add(timeObj.split(":")[1], 'minutes').format('YYYY-MM-DD HH:mm:ss')
702
703
704 followupClause = `,followup_date = '${followup_datetime}', followup_status = 'Active'`
705 followupActivityUpdateClause = `,followup_date = '${followup_datetime}', followup_status = 0`
706
707
708 //update the latest activity in customrr table also :: if top most activity has been edited.
709 //let status_id = await getParentStatusId(reqData.activity_status);
710 //update the latest activity in customrr table also
711 let currStatusUpdateQry = `Update rnr_crr_customers SET updated_at = now() ${followupClause}
712 WHERE lead_id = ${reqData.leadId}`;
713 let currStatusUpdateRes = await commonSql.updateByQuery(currStatusUpdateQry);
714 }
715 }
716
717
718 const activityTitleQry = `Select status_id, status_name from rnr_crr_active_status
719 where status_id = ${reqData.activity_status}`;
720 let activityTitleRes = await commonSql.getDataFromMysqlByGivenQuery(activityTitleQry, 0);
721
722 // let activityUpdateQry = `Update rnr_crr_activity SET title = '${activityTitleRes.status_name}',
723 // description = '${reqData.comments}', activity_status = ${reqData.activity_status},
724 // created_by = '${loggedUser.email}', updated_at = now() ${followupActivityUpdateClause}
725 // WHERE id = ${reqData.id}`;
726 // let activityUpdateRes = await commonSql.updateByQuery(activityUpdateQry);
727 // if (activityUpdateRes == false) {
728 // return res("Failed to update activity", null);
729 // }
730
731 if (followupActivityUpdateClause == "") {
732 conn.query(`Update rnr_crr_activity set title=?,description=?,activity_status=?,
733 created_by=? where id=?`,
734 [activityTitleRes.status_name,
735 reqData.comments,
736 reqData.activity_status,
737 loggedUser.email,
738 reqData.id
739 ], async (activityErr, activityRes) => {
740 if (activityErr) {
741 return res("Failed to edit activity", null);
742 }
743 await editSelectedActivityInLeadSquared(activityInfoRes, reqData, loggedUser.email);
744 })
745 } else {
746 conn.query(`Update rnr_crr_activity set title=?,description=?,activity_status=?,
747 created_by=?,followup_date=?,followup_status=? where id=?`,
748 [activityTitleRes.status_name,
749 reqData.comments,
750 reqData.activity_status,
751 loggedUser.email,
752 followup_datetime,
753 '0',
754 reqData.id
755 ], async (activityErr, activityRes) => {
756 if (activityErr) {
757 return res("Failed to edit activity", null);
758 }
759 await editSelectedActivityInLeadSquared(activityInfoRes, reqData, loggedUser.email);
760 })
761 }
762
763
764
765 //get the activity info
766 let activityInfoQry = `Select leadsquared_id, a.activity_ls_id from rnr_crr_customers as c
767 LEFT JOIN rnr_crr_activity as a ON c.lead_id = a.lead_id
768 WHERE c.lead_id = ${reqData.leadId} AND a.id = ${reqData.id}`;
769 let activityInfoRes = await commonSql.getDataFromMysqlByGivenQuery(activityInfoQry, 0);
770 if (activityInfoRes != null) {
771 await editSelectedActivityInLeadSquared(activityInfoRes, reqData, loggedUser.email);
772 }
773
774
775 return res(null, {});
776 }
777 catch (e) {
778 console.log("Error in Customr Service editActivity()", e.message);
779 customrrLogger.error("Error in Customr Service editActivity()", e);
780 return res(e.message, null);
781 }
782}
783
784exports.deleteActivity = async (loggedUser, reqData, res) => {
785 try {
786 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
787 if (checkActivityAuth == false) {
788 return res("Unauthorized activity", null);
789 }
790
791 //check if the activity being deleted is of feedbackdone?
792 //If yes then delete the feedbackdone entry from rnr_crr_feedback_filled and
793 //rnr_crr_call_records table for that particular month and year.
794 let last_contacted_date = ``;
795 if ([5, 18, 19, 20, 21, 24].indexOf(reqData.activity_status) != -1) {
796 last_contacted_date = `, last_contacted_date = '0'`;
797 await deleteFbDoneRecords(reqData);
798 }
799
800 //get the activity info
801 // let activityInfoQry = `Select leadsquared_id, a.activity_ls_id from rnr_crr_customers as c
802 // LEFT JOIN rnr_crr_activity as a ON c.lead_id = a.lead_id
803 // WHERE c.lead_id = ${reqData.leadId} AND a.id = ${reqData.id}`;
804 // let activityInfoRes = await commonSql.getDataFromMysqlByGivenQuery(activityInfoQry, 0);
805 // if (activityInfoRes != null) {
806 // await deleteSelectedActivityFromLeadSquared(activityInfoRes);
807 // }
808
809 const deleteActivityRes = await commonSql.deleteQuery('rnr_crr_activity', 'id', reqData.id);
810 if (deleteActivityRes == false) {
811 return res("Failed to delete activity", null);
812 }
813
814 //update the customers table with most recent activity.
815 let activityQry = `SELECT a1.id, a1.lead_id, a1.activity_type, a1.activity_status
816 FROM rnr_crr_activity a1
817 WHERE a1.id = (SELECT MAX(a2.id)
818 FROM rnr_crr_activity a2
819 WHERE a2.lead_id = a1.lead_id AND activity_type = 'activity')
820 AND a1.lead_id = ${reqData.leadId}`;
821 let activityRes = await commonSql.getDataFromMysqlByGivenQuery(activityQry, 0);
822 let status_id = 1;
823 let followupClause = '';
824 let last_activity_status_id = 1;
825 if (activityRes != null) {
826
827 status_id = await getParentStatusId(+activityRes.activity_status);
828 last_activity_status_id = +activityRes.activity_status;
829 }
830
831 let followUpQry = `SELECT a1.id, a1.lead_id, a1.activity_type, a1.activity_status,
832 a1.followup_status, a1.followup_date
833 FROM rnr_crr_activity a1
834 WHERE a1.id = (SELECT MAX(a2.id) FROM rnr_crr_activity a2
835 WHERE a2.lead_id = a1.lead_id AND activity_type = 'activity'
836 AND activity_status IN ('6,7,13'))
837 AND a1.lead_id = ${reqData.leadId}`;
838 let followUpRes = await commonSql.getDataFromMysqlByGivenQuery(followUpQry, 1);
839
840 if (followUpRes == null) {
841 followupClause = `,followup_date = '0', followup_status = 'Active'`
842 } else {
843 let statusType = followUpRes.followup_status == 0 ? 'Active' : 'Completed';
844 followupClause = `,followup_date = '${followUpRes.followup_date}', followup_status = '${statusType}'`
845 }
846
847 console.log("Status ", status_id)
848
849 let last_activity_status = `, last_activity_status = ${last_activity_status_id}`;
850 let currStatusUpdateQry = `Update rnr_crr_customers SET active_status = ${status_id},
851 updated_at = now() ${followupClause} ${last_contacted_date} ${last_activity_status}
852 WHERE lead_id = ${reqData.leadId}`;
853 let currStatusUpdateRes = await commonSql.updateByQuery(currStatusUpdateQry);
854
855 return res(null, {});
856 }
857 catch (e) {
858 console.log("Error in Customr Service deleteActivity()", e.message);
859 customrrLogger.error("Error in Customr Service deleteActivity()", e);
860 return res(e.message, null);
861 }
862}
863
864async function deleteFbDoneRecords(reqData) {
865 try {
866 let activity_month = new Date(reqData.created_at).getMonth();
867 let activity_year = new Date(reqData.created_at).getFullYear();
868
869 let getfbFilledQry = `Select * from rnr_crr_feedback_filled where lead_id= ${reqData.leadId}`;
870 let getfbFilledRes = await commonSql.getDataFromMysqlByGivenQuery(getfbFilledQry, 1);
871
872 let feedback_filled_id = 0;
873 if (getfbFilledRes != null) {
874 for (let i = 0; i < getfbFilledRes.length; i++) {
875 if (new Date(getfbFilledRes[i].created_at).getMonth() == activity_month &&
876 new Date(getfbFilledRes[i].created_at).getFullYear() == activity_year) {
877 feedback_filled_id = getfbFilledRes[i].id;
878 }
879 }
880 let deleteFbFilledQry = `DELETE FROM rnr_crr_feedback_filled WHERE id = ${feedback_filled_id}`;
881 let deletedFbFilledRows = await conn.promise().query(deleteFbFilledQry);
882 }
883
884 let getCallRecordQry = `Select * from rnr_crr_call_records where lead_id= ${reqData.leadId}`;
885 let getCallRecordRes = await commonSql.getDataFromMysqlByGivenQuery(getCallRecordQry, 1);
886 let call_record_id = 0;
887 if (getCallRecordRes != null) {
888 for (let i = 0; i < getCallRecordRes.length; i++) {
889
890 if (new Date(getCallRecordRes[i].created_at).getMonth() == activity_month &&
891 new Date(getCallRecordRes[i].created_at).getFullYear() == activity_year) {
892 call_record_id = getCallRecordRes[i].id;
893 }
894 }
895
896 let deleteCallRecordQry = `DELETE FROM rnr_crr_call_records WHERE id = ${call_record_id}`;
897 let deletedCallRecordRows = await conn.promise().query(deleteCallRecordQry);
898 }
899 } catch (error) {
900 console.log("Error in Customr Service deleteFbDoneRecords()", e.message);
901 customrrLogger.error("Error in Customr Service deleteFbDoneRecords()", e);
902 }
903}
904
905exports.addNote = async (loggedUser, reqData, res) => {
906 try {
907
908 // const paramsName = 'lead_id, activity_type, description, created_by';
909 // const paramsValue = `${reqData.leadId}, 'notes', '${reqData.comments}', '${loggedUser.email}'`;
910 // const insertedNote = await commonSql.insertQuery('rnr_crr_activity', paramsName, paramsValue);
911 // if (insertedNote == false) {
912 // return res("Failed to add note", null);
913 // }
914
915 conn.query(`INSERT INTO rnr_crr_activity (lead_id,activity_type,description,created_by) VALUES (?,?,?,?)`,
916 [
917 reqData.leadId,
918 'notes',
919 reqData.comments,
920 loggedUser.email
921 ],
922 async (error, notes_results, fields) => {
923 if (error) {
924 return res("Failed to add note", null);
925 }
926 })
927
928 return res(null, {});
929 }
930 catch (e) {
931 console.log("Error in Customr Service addNote()", e.message);
932 customrrLogger.error("Error in Customr Service addNote()", e);
933 return res(e.message, null);
934 }
935}
936
937exports.editNote = async (loggedUser, reqData, res) => {
938 try {
939 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
940 if (checkActivityAuth == false) {
941 return res("Unauthorized activity", null);
942 }
943 // let noteUpdateQry = `Update rnr_crr_activity SET description = '${reqData.comments}',
944 // created_by = '${loggedUser.email}', updated_at = now()
945 // WHERE id = ${reqData.id}`;
946 // let noteUpdateRes = await commonSql.updateByQuery(noteUpdateQry);
947 // if (noteUpdateRes == false) {
948 // return res("Failed to update note", null);
949 // }
950
951 conn.query(`Update rnr_crr_activity set description=?,
952 created_by=? where id=?`,
953 [
954 reqData.comments,
955 loggedUser.email,
956 reqData.id
957 ], async (activityErr, activityRes) => {
958 if (activityErr) {
959 return res("Failed to update note", null);
960 }
961 })
962
963 return res(null, {});
964 }
965 catch (e) {
966 console.log("Error in Customr Service editNote()", e.message);
967 customrrLogger.error("Error in Customr Service editNote()", e);
968 return res(e.message, null);
969 }
970}
971
972exports.deleteNote = async (loggedUser, reqData, res) => {
973 try {
974 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
975 if (checkActivityAuth == false) {
976 return res("Unauthorized activity", null);
977 }
978 const deleteNoteRes = await commonSql.deleteQuery('rnr_crr_activity', 'id', reqData.id);
979 if (deleteNoteRes == false) {
980 return res("Failed to delete note", null);
981 }
982 return res(null, {});
983 }
984 catch (e) {
985 console.log("Error in Customr Service deleteNote()", e.message);
986 customrrLogger.error("Error in Customr Service deleteNote()", e);
987 return res(e.message, null);
988 }
989}
990
991exports.addComment = async (loggedUser, reqData, res) => {
992 try {
993 // const paramsName = 'lead_id, activity_type, description, created_by';
994 // const paramsValue = `${reqData.leadId}, 'comments', '${reqData.comments}', '${loggedUser.email}'`;
995 // const insertedComment = await commonSql.insertQuery('rnr_crr_activity', paramsName, paramsValue);
996 // if (insertedComment == false) {
997 // return res("Failed to add comment", null);
998 // }
999
1000 conn.query(`INSERT INTO rnr_crr_activity (lead_id,activity_type,description,created_by) VALUES (?,?,?,?)`,
1001 [
1002 reqData.leadId,
1003 'comments',
1004 reqData.comments,
1005 loggedUser.email
1006 ],
1007 async (error, comments_results, fields) => {
1008 if (error) {
1009 return res("Failed to add comment", null);
1010 }
1011 })
1012 return res(null, {});
1013 }
1014 catch (e) {
1015 console.log("Error in Customr Service addComment()", e.message);
1016 customrrLogger.error("Error in Customr Service addComment()", e);
1017 return res(e.message, null);
1018 }
1019}
1020
1021exports.editComment = async (loggedUser, reqData, res) => {
1022 try {
1023 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
1024 if (checkActivityAuth == false) {
1025 return res("Unauthorized activity", null);
1026 }
1027 // let commentUpdateQry = `Update rnr_crr_activity SET description = '${reqData.comments}',
1028 // created_by = '${loggedUser.email}', updated_at = now()
1029 // WHERE id = ${reqData.id}`;
1030 // let commentUpdateRes = await commonSql.updateByQuery(commentUpdateQry);
1031 // if (commentUpdateRes == false) {
1032 // return res("Failed to update comment", null);
1033 // }
1034
1035 conn.query(`Update rnr_crr_activity set description=?,
1036 created_by=? where id=?`,
1037 [
1038 reqData.comments,
1039 loggedUser.email,
1040 reqData.id
1041 ], async (activityErr, activityRes) => {
1042 if (activityErr) {
1043 return res("Failed to update comment", null);
1044 }
1045 })
1046
1047 return res(null, {});
1048 }
1049 catch (e) {
1050 console.log("Error in Customr Service editComment()", e.message);
1051 customrrLogger.error("Error in Customr Service editComment()", e);
1052 return res(e.message, null);
1053 }
1054}
1055
1056exports.deleteComment = async (loggedUser, reqData, res) => {
1057 try {
1058 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
1059 if (checkActivityAuth == false) {
1060 return res("Unauthorized activity", null);
1061 }
1062 const deleteCommentRes = await commonSql.deleteQuery('rnr_crr_activity', 'id', reqData.id);
1063 if (deleteCommentRes == false) {
1064 return res("Failed to delete comment", null);
1065 }
1066 return res(null, {});
1067 }
1068 catch (e) {
1069 console.log("Error in Customr Service deleteComment()", e.message);
1070 customrrLogger.error("Error in Customr Service deleteComment()", e);
1071 return res(e.message, null);
1072 }
1073}
1074
1075async function checkloggedUserIsActivityOwner(loggedUser, id) {
1076 try {
1077 const activityQry = `Select created_by from rnr_crr_activity where id = ${id}`;
1078 const activityData = await commonSql.getDataFromMysqlByGivenQuery(activityQry, 0);
1079 if (activityData == null) {
1080 return false;
1081 }
1082
1083 if (activityData.created_by == loggedUser.email) {
1084 return true;
1085 }
1086 return false;
1087
1088 } catch (e) {
1089 console.log("Error in Customr Service checkloggedUserIsActivityOwner()", e.message);
1090 customrrLogger.error("Error in Customr Service checkloggedUserIsActivityOwner()", e);
1091 return false;
1092 }
1093}
1094
1095
1096
1097// ----------------------------- Attachments ------------------------------
1098
1099const { APIGateway } = require('aws-sdk');
1100const { ConsoleTransportOptions } = require('winston/lib/winston/transports');
1101var appDir = path.dirname(require.main.filename);
1102const userFiles = appDir + '/upload/';
1103const { AWS_ACCESS_KEY, AWS_SECRET_ACCESS_KEY, AWS_DEFAULT_REGION, AWS_BUCKET, AttachmentCDNFolder, cdnURL } = process.env;
1104
1105
1106var attachmentName = "";
1107AWS.config.update({
1108 accessKeyId: AWS_ACCESS_KEY,
1109 secretAccessKey: AWS_SECRET_ACCESS_KEY,
1110 region: AWS_DEFAULT_REGION
1111})
1112const s3 = new AWS.S3();
1113
1114var uploadCDN = multer({
1115 storage: multerS3({
1116 s3: s3,
1117 bucket: AWS_BUCKET,
1118 ContentType: 'image/*',
1119 acl: 'public-read',
1120 metadata: function (req, file, cb) {
1121 cb(null, { fieldName: 'file' });
1122 },
1123 key: function (req, file, cb) {
1124 cb(null, `${AttachmentCDNFolder}/${Date.now().toString()}${file.originalname.replace(/\s/g, "-")}`)
1125 }
1126 })
1127}).single('file')
1128
1129exports.addAttachment = async (req, loggedUser, res) => {
1130 try {
1131 await uploadCDN(req, res, async (err) => {
1132
1133 // let file_name = req.file.originalname;
1134 let file_cdn_url = `${cdnURL}` + req.file.key;
1135 let reqData = req.body;
1136 // console.log(req , "----------------", file_cdn_url)
1137 // const paramsName = 'lead_id, activity_type, title, description, created_by, file_url';
1138 // const paramsValue = `${reqData.leadId}, 'attachment','${reqData.title}', '${reqData.comments}', '${loggedUser.email}', '${file_cdn_url}'`;
1139 // const insertedAttachment = await commonSql.insertQuery('rnr_crr_activity', paramsName, paramsValue);
1140 // if (insertedAttachment == false) {
1141 // return res("Failed to add attachment", null);
1142 // }
1143
1144 conn.query(`INSERT INTO rnr_crr_activity (lead_id,activity_type,title,description,created_by, file_url) VALUES (?,?,?,?,?,?)`,
1145 [
1146 reqData.leadId,
1147 'attachment',
1148 reqData.title,
1149 reqData.comments,
1150 loggedUser.email,
1151 file_cdn_url
1152 ],
1153 async (error, attachment_results, fields) => {
1154 if (error) {
1155 return res("Failed to add attachment", null);
1156 }
1157 })
1158
1159 let insertedFile = file_cdn_url;
1160 return res(null, insertedFile);
1161 });
1162 }
1163 catch (e) {
1164 console.log("Error in Customr Service addAttachment()", e.message);
1165 customrrLogger.error("Error in Customr Service addAttachment()", e);
1166 return res(e.message, null);
1167 }
1168}
1169
1170exports.editAttachment = async (req, loggedUser, res) => {
1171 try {
1172
1173 await uploadCDN(req, res, async (err) => {
1174
1175
1176 let reqData = req.body;
1177 // console.log(reqData);
1178
1179 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
1180 if (checkActivityAuth == false) {
1181 return res("Unauthorized activity", null);
1182 }
1183
1184 if (req.file && req.file != undefined) {
1185 let file_cdn_url = `${cdnURL}` + req.file.key;
1186
1187 // let attachmentUpdateQry = `Update rnr_crr_activity SET title = '${reqData.title}',
1188 // description = '${reqData.comments}', created_by = '${loggedUser.email}', updated_at = now(),
1189 // file_url = '${file_cdn_url}' WHERE id = ${reqData.id}`;
1190 // let attachmentUpdateRes = await commonSql.updateByQuery(attachmentUpdateQry);
1191 // if (attachmentUpdateRes == false) {
1192 // return res("Failed to update attachment", null);
1193 // }
1194
1195 conn.query(`Update rnr_crr_activity set title=?, description=?,
1196 created_by=?,file_url=? where id=?`,
1197 [
1198 reqData.title,
1199 reqData.comments,
1200 loggedUser.email,
1201 file_cdn_url,
1202 reqData.id
1203 ], async (activityErr, activityRes) => {
1204 if (activityErr) {
1205 return res("Failed to update attachment", null);
1206 }
1207 })
1208
1209 let insertedFile = {
1210 file_url: file_cdn_url
1211 };
1212 return res(null, insertedFile);
1213 } else {
1214
1215 // let attachmentUpdateQry = `Update rnr_crr_activity SET title = '${reqData.title}',
1216 // description = '${reqData.comments}', created_by = '${loggedUser.email}', updated_at = now()
1217 // WHERE id = ${reqData.id}`;
1218 // let attachmentUpdateRes = await commonSql.updateByQuery(attachmentUpdateQry);
1219 // if (attachmentUpdateRes == false) {
1220 // return res("Failed to update attachment", null);
1221 // }
1222 conn.query(`Update rnr_crr_activity set title=?, description=?,
1223 created_by=? where id=?`,
1224 [
1225 reqData.title,
1226 reqData.comments,
1227 loggedUser.email,
1228 reqData.id
1229 ], async (activityErr, activityRes) => {
1230 if (activityErr) {
1231 return res("Failed to update attachment", null);
1232 }
1233 })
1234 return res(null, {});
1235 }
1236 });
1237
1238 }
1239 catch (e) {
1240 console.log("Error in Customr Service editAttachment()", e.message);
1241 customrrLogger.error("Error in Customr Service editAttachment()", e);
1242 return res(e.message, null);
1243 }
1244}
1245
1246exports.deleteAttachment = async (loggedUser, reqData, res) => {
1247 try {
1248
1249 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
1250 if (checkActivityAuth == false) {
1251 return res("Unauthorized activity", null);
1252 }
1253 const deleteAttachmentRes = await commonSql.deleteQuery('rnr_crr_activity', 'id', reqData.id);
1254 if (deleteAttachmentRes == false) {
1255 return res("Failed to delete attachment", null);
1256 }
1257 return res(null, {});
1258 }
1259 catch (e) {
1260 console.log("Error in Customr Service deleteAttachment()", e.message);
1261 customrrLogger.error("Error in Customr Service deleteAttachment()", e);
1262 return res(e.message, null);
1263 }
1264}
1265
1266exports.updateFollowupStatus = async (loggedUser, reqData, res) => {
1267 try {
1268 let checkActivityAuth = await checkloggedUserIsActivityOwner(loggedUser, reqData.id);
1269 if (checkActivityAuth == false) {
1270 return res("Unauthorized activity", null);
1271 }
1272
1273 let followup_status_str = "Active";
1274 let followup_status_int = 0;
1275 if (reqData.followup_status == true) {
1276 followup_status_str = "Completed";
1277 followup_status_int = 1;
1278 }
1279
1280 //update customer table
1281 let currStatusUpdateQry = `Update rnr_crr_customers SET followup_status = '${followup_status_str}',
1282 updated_at = now()
1283 WHERE lead_id = ${reqData.leadId}`;
1284 let currStatusUpdateRes = await commonSql.updateByQuery(currStatusUpdateQry);
1285
1286 //update activity table
1287 let followupUpdateQry = `Update rnr_crr_activity SET followup_status = '${followup_status_int}',
1288 updated_at = now()
1289 WHERE id = ${reqData.id}`;
1290 let followupUpdateRes = await commonSql.updateByQuery(followupUpdateQry);
1291 if (followupUpdateRes == false) {
1292 return res("Failed to update followup status", null);
1293 }
1294 return res(null, {});
1295 }
1296 catch (e) {
1297 console.log("Error in Customr Service editComment()", e.message);
1298 customrrLogger.error("Error in Customr Service editComment()", e);
1299 return res(e.message, null);
1300 }
1301}
1302
1303
1304exports.isSelectedFollowupTimeAvailable = async (loggedUser, reqData, res) => {
1305 try {
1306 let inTime = new Date(reqData.selected_time);
1307 let isTimeSlotAvailable = await custAssign.isFollowupSlotAvailableForAgent(reqData.agent_email, inTime)
1308 return res(null, { is_slot_available: isTimeSlotAvailable });
1309 }
1310 catch (e) {
1311 console.log("Error in Customr Service isSelectedFollowupTimeAvailable()", e.message);
1312 customrrLogger.error("Error in Customr Service isSelectedFollowupTimeAvailable()", e);
1313 return res(e.message, null);
1314 }
1315}
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327//----------------------LS API---------------------------
1328
1329
1330
1331exports.checknCreateLead = async (reqData, IP, res) => {
1332 try {
1333
1334 if (reqData.email == null || reqData.email == undefined || reqData.email == "") {
1335
1336 res(`Invalid input data`, null);
1337 }
1338
1339
1340 let isLeadExists = false;
1341 let leadRes;
1342 //check lead by email.
1343 let leadEmailRes = await leadSquared.getLeadByEmail(reqData.email);
1344 if (leadEmailRes != null) {
1345 isLeadExists = true;
1346 leadRes = leadEmailRes;
1347 console.log("Email matched");
1348 }
1349
1350 //if lead not found by email then again check by phone
1351 if (isLeadExists == false) {
1352 let leadPhoneRes = await leadSquared.getLeadByPhone(reqData.phone);
1353 if (leadPhoneRes != null) {
1354 isLeadExists = true;
1355 leadRes = leadPhoneRes;
1356 console.log("Phone no. matched");
1357 }
1358 }
1359
1360 //if lead not found then generate a new one with received data
1361 if (isLeadExists == false) {
1362
1363 console.log("Lead not found, generate new one");
1364 let leadCreateRes = await leadSquared.createNewLead(reqData, IP);
1365 if (leadCreateRes == null) {
1366 res(`Something went wrong`, null);
1367 } else {
1368
1369 //let sendSMSRes = await leadSquared.sendLeadCaptureSMS(reqData.phone);
1370 let successRes = {
1371 status: "success",
1372 msg: `Thank You ${reqData.name}! Our representative will get in touch with you shortly.`,
1373 page: reqData.page_name,
1374 redirect: true
1375 }
1376 res(null, successRes);
1377 }
1378
1379 } else { //else check if lead's stage is non workable.
1380
1381 if (leadRes.LeadAge < 30) {
1382 console.log("Do nothing");
1383 res(`Lead already exists`, null);
1384 } else {
1385 let nonWorkableStages = ['Not Reachable', 'Not Interested', 'Work to Non Work',
1386 'Invalid', 'Language Barrier', 'Dhanwaan Not Interested', 'Dhanwaan Language Barrier',
1387 'Dhanwaan Invalid', 'Blog Subs', 'Not Pitched'];
1388
1389 if (nonWorkableStages.indexOf(leadRes.ProspectStage) != -1) {
1390
1391 let leadStageUpdateRes = await leadSquared.updateLeadStage(leadRes.EmailAddress, constants.Repeat_Lead, reqData.ProspectID, reqData.page_name, IP);
1392 if (leadStageUpdateRes == null) {
1393 res(`Something went wrong`, null);
1394 } else {
1395 res(null, { success: true, message: `Lead Stage updated to ${constants.Repeat_Lead}` });
1396 }
1397 }
1398
1399 // Lead Stage other than non-workable. Again do nothing
1400 res(`Lead already exists`, null);
1401 }
1402 }
1403 }
1404 catch (e) {
1405 console.log("Error in LeadSquared Service checknCreateLead()", e);
1406 customrrLogger.error(`Error in LeadSquared Service checknCreateLead(), ${e}`);
1407 return res(`Something went wrong`, null);
1408 }
1409}
1410
1411
1412async function addActivityIntoLeadSquared(reqData, loggedUserEmail) {
1413 try {
1414
1415 if (loggedUserEmail == "appsupport@researchandranking.com") {
1416 loggedUserEmail = "abhijeet.paul@researchandranking.com";
1417 }
1418
1419 let getCustomrInfoQry = `Select id, leadsquared_id, email
1420 FROM rnr_crr_customers
1421 WHERE lead_id = ${reqData.leadId}`;
1422 let customrInfo = await commonSql.getDataFromMysqlByGivenQuery(getCustomrInfoQry, 0);
1423 if (customrInfo != null) {
1424
1425 let activity_name = getLSActivityName(reqData.activity_status);
1426 let activityDate = moment.utc(new Date()).format('YYYY-MM-DD HH:mm:ss');
1427 let activityNote = reqData.comments == null ? "There is no note" : reqData.comments;
1428 // console.log(activity_name)
1429
1430 //44cf7e32-1b51-4401-8a5f-ccb59ee840e0 602ffeca-22d0-4f97-93a8-f38ba600123a
1431 let activityObj = {
1432 "RelatedProspectId": customrInfo.leadsquared_id,
1433 "ActivityEvent": 167,
1434 "ActivityNote": activityNote,
1435 "ActivityOwnerEmail": loggedUserEmail,
1436 "ActivityDateTime": activityDate,
1437 "Fields": [
1438 {
1439 "SchemaName": "mx_Custom_1",
1440 "Value": activity_name
1441 }
1442 ]
1443 }
1444
1445 bodyJson = JSON.stringify(activityObj);
1446
1447 // console.log(bodyJson);
1448 let api_url = `https://api.leadsquared.com/v2/ProspectActivity.svc/Create?accessKey=${process.env.LeadSquared_AccessKey}&secretKey=${process.env.LeadSquared_SecreteKey}`;
1449
1450 const ActivityCreateRes = await leadSquared.updateBodyInLeadSq(bodyJson, api_url);
1451 if (ActivityCreateRes.Status == "Success") {
1452 return ActivityCreateRes;
1453 } else {
1454 return null;
1455 }
1456 }
1457
1458 } catch (error) {
1459 console.log("Error in Customr Service addActivityIntoLeadSquared()", error);
1460 customrrLogger.error(`Error in Customr Service addActivityIntoLeadSquared(), ${error}`);
1461 }
1462}
1463
1464function getLSActivityName(activity_status) {
1465 let activity_name = "";
1466 if (activity_status >= 10 && activity_status <= 14) {
1467 activity_name = "Not Reachable";
1468 } else if (activity_status == 18) {
1469 activity_name = "Red";
1470 } else if (activity_status == 19) {
1471 activity_name = "Yellow";
1472 } else if (activity_status == 20) {
1473 activity_name = "Green";
1474 } else if (activity_status == 21 || activity_status == 24) {
1475 activity_name = "Service Not Used";
1476 } else {
1477 activity_name = "Not Contacted";
1478 }
1479 return activity_name;
1480}
1481
1482async function editSelectedActivityInLeadSquared(activityInfo, reqData, loggedUserEmail) {
1483 try {
1484
1485 if (loggedUserEmail == "appsupport@researchandranking.com") {
1486 loggedUserEmail = "abhijeet.paul@researchandranking.com";
1487 }
1488
1489 let activityNote = reqData.comments == null ? "There is no note" : reqData.comments;
1490 let activity_name = getLSActivityName(reqData.activity_status);
1491 // console.log(activity_name)
1492 let activityObj = {
1493 "ProspectActivityId": activityInfo.activity_ls_id,
1494 "ActivityEvent": 167,
1495 "ActivityOwnerEmail": loggedUserEmail,
1496 "ActivityNote": activityNote,
1497 "Fields": [
1498 {
1499 "SchemaName": "mx_Custom_1",
1500 "Value": activity_name
1501 }
1502 ]
1503 }
1504
1505 bodyJson = JSON.stringify(activityObj);
1506 let api_url = `https://api.leadsquared.com/v2/ProspectActivity.svc/CustomActivity/Update?accessKey=${process.env.LeadSquared_AccessKey}&secretKey=${process.env.LeadSquared_SecreteKey}`;
1507
1508 const ActivityUpdateRes = await leadSquared.updateBodyInLeadSq(bodyJson, api_url);
1509 console.log(ActivityUpdateRes)
1510 if (ActivityUpdateRes.Status == "Success") {
1511 return ActivityUpdateRes;
1512 } else {
1513 return null;
1514 }
1515
1516 } catch (error) {
1517 console.log("Error in Customr Service editSelectedActivityInLeadSquared()", error);
1518 customrrLogger.error(`Error in Customr Service editSelectedActivityInLeadSquared(), ${error}`);
1519 }
1520}
1521
1522
1523async function deleteSelectedActivityFromLeadSquared(reqData) {
1524 try {
1525
1526 let activityId = reqData.activity_ls_id;
1527 let api_url = `https://api.leadsquared.com/v2/ProspectActivity.svc/CustomActivity/DeleteById?accessKey=${process.env.LeadSquared_AccessKey}&secretKey=${process.env.LeadSquared_SecreteKey}&id=${activityId}`;
1528
1529 console.log(api_url);
1530 const ActivityDeleteRes = await leadSquared.executeGetRequestInLeadSq(api_url);
1531 // console.log(ActivityDeleteRes)
1532 if (ActivityDeleteRes.Status == "Success") {
1533 return ActivityDeleteRes;
1534 } else {
1535 return null;
1536 }
1537 } catch (error) {
1538 console.log("Error in Customr Service deleteSelectedActivityFromLeadSquared()", error);
1539 customrrLogger.error(`Error in Customr Service deleteSelectedActivityFromLeadSquared(), ${error}`);
1540 }
1541}
1542
1543
1544
1545exports.sendCustomersListToUserEmail = async (req, loggedUser, res) => {
1546 try {
1547 const { query } = req;
1548 let { search = '', agents = '', product = '', status = '', last_satisfaction_level = '', target_date = '', campaign_id = '' } = query;
1549
1550 let agentClause = ``;
1551 if (loggedUser.role === 3) {
1552 agentClause = ` WHERE (cu.email = '${loggedUser.email}' OR cuta.email = '${loggedUser.email}') AND c.customer_type = 'active'`
1553 } else {
1554 agentClause = ` WHERE c.customer_type = 'active'`
1555 }
1556
1557 let agentsClause = ``;
1558 if (agents) {
1559 let splittedAgents = agents.split(",")
1560 let agents_sub = ``;
1561 splittedAgents.forEach(element => {
1562 agents_sub += `'${element}',`
1563 });
1564
1565 agents_sub = agents_sub.slice(0, -1);
1566 agentsClause = `AND (qry.agent_mail IN(${agents_sub}) OR qry.tr_agent_mail IN(${agents_sub}))`
1567 }
1568
1569 let statusClause = ``;
1570 if (status) {
1571 let splittedStatus = status.split(",")
1572 statusClause = `AND qry.status_name IN (`;
1573 splittedStatus.forEach(element => {
1574 statusClause += `'${element}',`
1575 });
1576
1577 statusClause = statusClause.slice(0, -1);
1578 statusClause += `)`
1579 }
1580
1581 let satisfactionClause = ``;
1582 if (last_satisfaction_level) {
1583 let splittedSatisfaction = last_satisfaction_level.split(",")
1584 satisfactionClause = `AND qry.last_activity_status IN (`;
1585 splittedSatisfaction.forEach(element => {
1586 satisfactionClause += `'${getSatisfactionStatusId(element)}',`
1587 });
1588
1589 satisfactionClause = satisfactionClause.slice(0, -1);
1590 satisfactionClause += `)`
1591 }
1592
1593 let productClause = ``;
1594 if (product) {
1595 let splittedProduct = product.split(",")
1596 productClause += `AND (`;
1597 if (splittedProduct.indexOf('7') != -1) { //If combo
1598 productClause += ` (FIND_IN_SET(1, qry.prod_ids) AND FIND_IN_SET(12, qry.prod_ids)) OR
1599 (FIND_IN_SET(11, qry.prod_ids) AND FIND_IN_SET(13, qry.prod_ids))`
1600
1601 for (let d = splittedProduct.length - 1; d > -1; d--) {
1602 if (splittedProduct[d] == '5' || splittedProduct[d] == '6' || splittedProduct[d] == '7' || splittedProduct[d] == '8') {
1603 splittedProduct.splice(d, 1);
1604 }
1605 }
1606 if (splittedProduct.length > 0) {
1607 productClause += `OR`
1608 }
1609 }
1610 splittedProduct.forEach(function (elm, index) {
1611 if (index == 0) {
1612 } else {
1613 productClause += `OR`;
1614 }
1615 productClause += ` FIND_IN_SET(${elm}, qry.prod_ids) `
1616 })
1617 productClause += ` ) `;
1618
1619 }
1620
1621 let target_dateClause = ``;
1622 if (target_date) {
1623 let targeet_date_formatet = moment(target_date).format('YYYY-MM-DD HH:mm:ss');
1624 target_dateClause = `AND (qry.target_date <> 'N.A' AND qry.target_date <= '${targeet_date_formatet}')`
1625 }
1626
1627 let campaign_idClause = ``;
1628 if (campaign_id) {
1629
1630 let splittedCampaign_id = campaign_id.split(",");
1631 campaign_idClause = `AND qry.campaign_id IN (`;
1632 splittedCampaign_id.forEach(element => {
1633 campaign_idClause += `'${element}',`
1634 });
1635
1636 campaign_idClause = campaign_idClause.slice(0, -1);
1637 campaign_idClause += `)`
1638 }
1639
1640 let mainQry = `Select * from (
1641 Select c.lead_id, c.full_name as customer_name, c.leadsquared_id,c.user_id,
1642 c.last_activity_status, c.last_contacted_date as last_contacted,
1643 cu.email as agent_email, cu.full_name as agent_name,
1644 c.campaign_id, camp.name as campaign_name, camp.target_date,
1645 camp.start_date as campaign_start_date, camp.end_date as campaign_end_date,
1646 a.status_name,
1647 JSON_ARRAYAGG(JSON_OBJECT("prod_id", rrp.product_id, 'expiry', rrp.expiry_date, 'title', price.product_type)) products_list,
1648 GROUP_CONCAT(rrp.product_id SEPARATOR ',') prod_ids,
1649 rru.last_activity,
1650 pp.title as fnf_portfolio
1651
1652 from rnr_crr_customers as c
1653 LEFT JOIN rnr_crr_campaigns as camp ON c.campaign_id = camp.campaign_id
1654 LEFT JOIN rnr_crr_users as cu ON cu.email = (case
1655 when c.transfer_agent IS NULL then c.original_agent
1656 else c.transfer_agent
1657 end)
1658 LEFT JOIN rr_users as rru ON rru.id = c.user_id
1659 LEFT JOIN rnr_crr_active_status as a ON a.status_id = c.active_status
1660 LEFT JOIN rr_products as rrp ON (c.user_id = rrp.user_id AND rrp.expiry_date >= curdate())
1661 LEFT JOIN rr_pricing as price ON rrp.product_id = price.id
1662 LEFT JOIN rr_personalized_portfolio as pp ON pp.id = rru.portfolio
1663 ${agentClause} AND (c.full_name LIKE '%${search}%' OR c.lead_id LIKE '%${search}%')
1664 GROUP BY lead_id
1665 )as qry
1666 WHERE 1=1
1667 ${agentsClause} ${statusClause} ${campaign_idClause} ${satisfactionClause}
1668 ${productClause} ${target_dateClause}`;
1669
1670 // console.log(mainQry);
1671
1672 let customersData = await commonSql.getDataFromMysqlByGivenQuery(mainQry, 1);
1673 if (customersData == null) {
1674 return res("No Customers found to download", null);
1675 }
1676 let allUser_ids = [];
1677 for (let i = 0; i < customersData.length; i++) {
1678 allUser_ids.push(customersData[i].user_id);
1679 if (customersData[i].last_activity_status == 18) {
1680 customersData[i].satisfaction_level = "Red";
1681 } else if (customersData[i].last_activity_status == 19) {
1682 customersData[i].satisfaction_level = "Yellow";
1683 } else if (customersData[i].last_activity_status == 20) {
1684 customersData[i].satisfaction_level = "Green";
1685 } else if (customersData[i].last_activity_status == 21) {
1686 customersData[i].satisfaction_level = "Grey";
1687 } else if (customersData[i].last_activity_status == 24) {
1688 customersData[i].satisfaction_level = "Cyan";
1689 } else {
1690 customersData[i].satisfaction_level = "N.A";
1691 }
1692
1693 // console.log(customersData[i].last_contacted)
1694
1695 customersData[i].campaign_id = customersData[i].campaign_id == null ? "N.A" : customersData[i].campaign_id;
1696 customersData[i].campaign_name = customersData[i].campaign_name == null ? "N.A" : customersData[i].campaign_name;
1697 customersData[i].target_date = customersData[i].target_date == null ? "N.A" : customersData[i].target_date;
1698 customersData[i].campaign_start_date = customersData[i].campaign_start_date == null ? "N.A" : customersData[i].campaign_start_date;
1699 customersData[i].campaign_end_date = customersData[i].campaign_end_date == null ? "N.A" : customersData[i].campaign_end_date;
1700 customersData[i].last_contacted = customersData[i].last_contacted == null ? "N.A" : customersData[i].last_contacted;
1701
1702 if (customersData[i].last_contacted != "N.A") {
1703 let newdate = moment(customersData[i].last_contacted).subtract({ 'hours': 5, 'minutes': 30 }).format('YYYY-MM-DD HH:mm:ss');
1704 customersData[i].since_last_contacted = moment(newdate).fromNow();
1705 }
1706
1707 customersData[i].agent = {
1708 name: customersData[i].agent_name,
1709 email: customersData[i].agent_email
1710 }
1711
1712 if (customersData[i].status_name != "Feedback Done") {
1713 customersData[i].satisfaction_level = "N.A";
1714 }
1715 delete customersData[i].agent_name;
1716 delete customersData[i].agent_email;
1717 delete customersData[i].prod_ids;
1718
1719 customersData[i].products_list.sort(function (a, b) {
1720 return a.prod_id - b.prod_id;
1721 });
1722 customersData[i].last_activity = moment(customersData[i].last_activity).format('YYYY-MM-DD HH:mm:ss');
1723 }
1724
1725 let getProductsPortfolioQry = `Select DISTINCT rrp.user_id,
1726 JSON_ARRAYAGG(cm.compname ) as mpo_recommend,
1727 dw_port.porifolio_name
1728 FROM rr_products as rrp
1729 LEFT JOIN rr_user_monthly_recommendation as umr ON (umr.user_id = rrp.user_id AND umr.product_id = 12)
1730 LEFT JOIN rr_monthly_recommendation as mr ON mr.id = umr.recommended_id
1731 LEFT JOIN rr_company_master as cm ON cm.fincode = mr.fincode
1732 LEFT JOIN rr_dw_user_portfolio as dw_user_port ON dw_user_port.user_id = rrp.user_id
1733 LEFT JOIN rr_dw_portfolio as dw_port ON dw_port.id = dw_user_port.portfolio_id
1734 WHERE rrp.user_id IN (${allUser_ids}) AND rrp.product_id = 12
1735 group by rrp.user_id`;
1736 let getProductsPortfolioRes = await commonSql.getDataFromMysqlByGivenQuery(getProductsPortfolioQry, 1);
1737
1738 // console.log(getProductsPortfolioQry);
1739
1740 for (let i = 0; i < customersData.length; i++) {
1741
1742 for (let p = 0; p < customersData[i].products_list.length; p++) {
1743 let prod = customersData[i].products_list[p];
1744
1745 if (prod.prod_id == 12) {
1746 let pfIndex = getProductsPortfolioRes.map(c => c.user_id).indexOf(customersData[i].user_id);
1747 let mpoRecomm = getProductsPortfolioRes[pfIndex].mpo_recommend.filter(function (el) {
1748 return el != null;
1749 });
1750 prod.portfolio_info = mpoRecomm.reverse();
1751
1752 } else if (prod.prod_id == 14) {
1753 let pfIndex = getProductsPortfolioRes.map(c => c.user_id).indexOf(customersData[i].user_id);
1754 let dwn_portfolio = "";
1755 if (pfIndex != -1) {
1756 dwn_portfolio = getProductsPortfolioRes[pfIndex].porifolio_name;
1757 }
1758 prod.portfolio_info = [dwn_portfolio];
1759
1760 } else if (prod.prod_id == 1) {
1761 prod.portfolio_info = [customersData[i].fnf_portfolio];
1762
1763 } else {
1764 prod.portfolio_info = "";
1765 }
1766
1767 delete customersData[i].fnf_portfolio;
1768 }
1769 }
1770
1771 await createAllCustomersInfoExcelSendMail(customersData, loggedUser);
1772
1773 return res(null, {});
1774 }
1775 catch (e) {
1776 console.log("Error in User Service getCustomersInfoList()", e.message);
1777 return res(e.message, null);
1778 }
1779}
1780
1781
1782
1783async function createAllCustomersInfoExcelSendMail(customersData, loggedUser) {
1784 try {
1785 const workbook = new Excel.Workbook();
1786 let wsMain = workbook.addWorksheet('Customers');
1787 PopulateCustomersListExcel(customersData, wsMain);
1788
1789 workbook.xlsx.writeFile(`CustomersList.xlsx`)
1790 .then(async function () {
1791 try {
1792 const subject = `Your requested Customers list is here.`;
1793 const text = 'Please find the list of customers requested by you from CustomRR.';
1794 let html = `<p>Dear ${loggedUser.full_name},</p><br>
1795 <p>Please find the list of customers requested by you from Customer Management: </p><br><br>
1796 <p>Best Regards</p>
1797 <p>Team Research & Ranking</p>
1798 <p>+91 22 6101 3800 (Boardline), +91 22 6101 3838</p>`;
1799 let attachmentObj = {
1800 filename: `CustomersList.xlsx`,
1801 path: path.join(__dirname, `../../CustomersList.xlsx`),
1802 cid: `CustomersList.xlsx`
1803 }
1804
1805 // console.log(attachmentObj)
1806
1807 // loggedUser.email = "pallavi.sharma@researchandranking.com, nishant.baranwal@researchandranking.com";
1808 // loggedUser.email = "nishant.baranwal@researchandranking.com"; //TODO:
1809 if (loggedUser.email == "appsupport@researchandranking.com") {
1810 loggedUser.email = "abhijeet.paul@researchandranking.com,pallavi.sharma@researchandranking.com";
1811 }
1812 let mailsentRes = await mailModel.sendMailWithAttachment(loggedUser.email, subject, text, html, attachmentObj);
1813 // console.log(mailsentRes)
1814 // console.log("mail sent!");
1815
1816 fs.unlinkSync(path.join(__dirname, `../../CustomersList.xlsx`));
1817
1818 } catch (error) {
1819 console.log("Error in sending report to mail: ", error);
1820 }
1821 });
1822
1823 } catch (error) {
1824 console.log("Error in Campaign Service createCampCustomersExcelSendMail()", error.message);
1825 customrrLogger.error("Error in Campaign Service createCampCustomersExcelSendMail()", error);
1826 }
1827}
1828
1829function PopulateCustomersListExcel(customersData, wsMain) {
1830 try {
1831 wsMain.columns = [
1832 { header: 'S.No.', key: 's.no.', width: 5 },
1833 { header: "Lead Id", key: 'leadID', width: 10 },
1834 { header: "Customer Name", key: 'custName', width: 20 },
1835 { header: "Agent Name", key: 'agentName', width: 20 },
1836 { header: "Status", key: 'status', width: 15 },
1837 { header: "Status Date", key: 'statusDate', width: 15 },
1838 { header: "Since Last Call Done", key: 'sinceLCD', width: 15 },
1839 { header: "Satisfaction Level", key: 'satisfaction', width: 15 },
1840 { header: "Target Date", key: 'tgtDate', width: 15 },
1841 { header: "Campaign Name", key: 'campName', width: 20 },
1842 { header: "Campaign Number", key: 'campNo', width: 20 },
1843 { header: "Products with Expiry and Portfolio", key: 'products', width: 25 }
1844 ];
1845
1846 //set styling of header row.
1847 let row1 = wsMain.getRow(1);
1848 row1.height = 30;
1849 row1.eachCell(function (cell, colNumber) {
1850 cell.alignment = {
1851 wrapText: true,
1852 horizontal: 'center',
1853 vertical: 'middle'
1854 };
1855 cell.fill = {
1856 type: 'pattern',
1857 pattern: 'solid',
1858 fgColor: { argb: 'FF00004E' },
1859 };
1860 cell.font = {
1861 bold: true,
1862 size: 12,
1863 color: { argb: 'FFFFFFFF' }
1864 };
1865 cell.border = {
1866 top: { style: 'thick' },
1867 left: { style: 'thick' },
1868 bottom: { style: 'thick' },
1869 right: { style: 'thick' }
1870 };
1871 });
1872
1873
1874
1875 //populate content in each row.
1876 for (let i = 0; i < customersData.length; i++) {
1877
1878 let products = ``;
1879
1880 for (let p = 0; p < customersData[i].products_list.length; p++) {
1881 if (customersData[i].products_list[p].title != null) {
1882 if (customersData[i].products_list[p].portfolio_info == "") {
1883 products += `(${customersData[i].products_list[p].title} : ${moment(new Date(customersData[i].products_list[p].expiry)).format('Do MMM YYYY')})`
1884 } else {
1885 products += `(${customersData[i].products_list[p].title} : ${moment(new Date(customersData[i].products_list[p].expiry)).format('Do MMM YYYY')} : ${customersData[i].products_list[p].portfolio_info})`
1886 }
1887 if (p != customersData[i].products_list.length - 1) {
1888 products += ", \n"
1889 }
1890 }
1891 }
1892 if (products == "") {
1893 products = "N.A";
1894 }
1895
1896
1897 let satisfaction_level = customersData[i].satisfaction_level == "N.A" ? "Feedback Not Done" : customersData[i].satisfaction_level;
1898 let status_date = "N.A";
1899 if (customersData[i].last_contacted != "N.A") {
1900 status_date = moment(new Date(customersData[i].last_contacted)).format('Do MMM YYYY');
1901 }
1902
1903 let target_date = "N.A";
1904 if (customersData[i].target_date != "N.A") {
1905 target_date = moment(new Date(customersData[i].target_date)).format('Do MMM YYYY');
1906 }
1907 if (customersData[i].since_last_contacted == undefined) {
1908 customersData[i].since_last_contacted = "N.A";
1909 }
1910
1911 wsMain.addRow([
1912 i + 1,
1913 customersData[i].lead_id,
1914 customersData[i].customer_name,
1915 customersData[i].agent.name,
1916 customersData[i].status_name,
1917 status_date,
1918 customersData[i].since_last_contacted,
1919 satisfaction_level,
1920 target_date,
1921 customersData[i].campaign_name,
1922 customersData[i].campaign_id,
1923 products
1924 ]).eachCell(function (cell, colNumber) {
1925 cell.alignment = {
1926 wrapText: true,
1927 horizontal: 'center',
1928 vertical: 'middle'
1929 };
1930 });
1931 }
1932 return wsMain;
1933 } catch (error) {
1934 console.log("Error in PopulateCustomersListExcel(): ", error);
1935 }
1936}
1937
1938
1939
1940var customers_file_name = null;
1941const storage = multer.diskStorage({
1942 destination: function (req, file, cb) {
1943 cb(null, 'upload_file/customers_files/');
1944 },
1945 // By default, multer removes file extensions so let's add them back
1946 filename: function (req, file, cb) {
1947 customers_file_name = 'customer_file' + '-' + Date.now() + path.extname(file.originalname)
1948 cb(null, customers_file_name);
1949 },
1950 onFileUploadStart: function (file, req, res) {
1951 if (req.files.file.length > maxSize) {
1952 return false;
1953 }
1954 }
1955});
1956let fileFilter = function (req, file, cb) {
1957 var allowedMimes = ['text/csv', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
1958 if (allowedMimes.includes(file.mimetype)) {
1959 cb(null, true);
1960 } else {
1961 cb({
1962 status: false,
1963 message: 'Invalid file type. Only xls, xlsx and csv files are allowed.'
1964 }, false);
1965 }
1966};
1967let obj = {
1968 storage: storage,
1969 limits: {
1970 fileSize: 5000000
1971 }, // 2 MB},
1972 fileFilter: fileFilter
1973};
1974
1975
1976exports.uploadNewCustomers_test = async (req, loggedUser, reqData, res) => {
1977 try {
1978 var returnData = {
1979 totalCount: 0,
1980 successCount: 0,
1981 failedCount: 0,
1982 failedList: [],
1983 newlyAddedList: []
1984 }
1985 let upload = multer(obj).single('customers_file');
1986 upload(req, res, async function (err) {
1987
1988 if (req.file == null || req.file == undefined) {
1989 return res("Please upload file", null);
1990 }
1991
1992 reqData = req.body;
1993 // console.log("reqData ^^^^^^^^^^^^^^^^^^^^^^ ",reqData);
1994 if (req.fileValidationError) {
1995 console.log("fileValidationError");
1996 return res("Invalid file", null);
1997 } else if (err) {
1998 console.log("err ", err);
1999 return res("Something went wrong", null);
2000 }
2001
2002
2003 var UsersWB = new Excel.Workbook();
2004 var customerInfoList = [];
2005 let methodType = 'xlsx';
2006 if (path.extname(req.file.originalname) == ".csv") {
2007 methodType = 'csv';
2008 } else if (path.extname(req.file.originalname) == ".xls" || path.extname(req.file.originalname) == ".xlsx") {
2009 methodType = 'xlsx';
2010 } else {
2011 return res("Invalid file format", null);
2012 }
2013 UsersWB[methodType].readFile(req.file.path).then(async function () {
2014 var users_workSheet = UsersWB.getWorksheet(1);
2015 let rowCount = users_workSheet.rowCount;
2016
2017 let totalCount = 0;
2018 if (users_workSheet.getRow(1).getCell(1).value.toLocaleLowerCase().startsWith("lead")) {
2019
2020 if (users_workSheet.getRow(1).getCell(4).value.toLowerCase().startsWith("email") == false) {
2021 return res("Invalid data format", null);
2022 }
2023
2024 for (let i = 2; i <= rowCount; i++) {
2025 if (users_workSheet.getRow(i).getCell(1).value != null && users_workSheet.getRow(i).getCell(1).value != undefined) {
2026
2027 totalCount += 1;
2028 if (users_workSheet.getRow(i)) {
2029 let row = users_workSheet.getRow(i);
2030 let emailId = "";
2031 if (typeof (row.getCell(4).value) == 'object') {
2032 emailId = row.getCell(4).value.text;
2033 } else {
2034 emailId = row.getCell(4).value;
2035 }
2036 let newUser = {
2037 leadId: row.getCell(1).value,
2038 email: emailId.trim(),
2039 }
2040 customerInfoList.push(newUser)
2041
2042 // console.log("FOr user: ", emailId)
2043 let custQry = `Select ru.id, ru.email,
2044 (select value from rr_user_variables where user_id= ru.id and data_key='pancard' limit 1) as pancard,
2045 (select value from rr_user_variables where user_id= ru.id and data_key='address' limit 1) as address,
2046 (select value from rr_user_variables where user_id= ru.id and data_key='land_mark' limit 1) as land_mark,
2047 (select value from rr_user_variables where user_id= ru.id and data_key='city' limit 1) as city,
2048 (select value from rr_user_variables where user_id= ru.id and data_key='state' limit 1) as state,
2049 (select value from rr_user_variables where user_id= ru.id and data_key='country' limit 1) as country,
2050 (select value from rr_user_variables where user_id= ru.id and data_key='pincode' limit 1) as pincode
2051 from rr_users as ru
2052 Where ru.email = '${emailId}'`;
2053 let customerInfo = await commonSql.getDataFromMysqlByGivenQuery(custQry, 0);
2054 if (customerInfo == null) {
2055
2056 let leadEmailRes = await leadSquared.getLeadByEmail(emailId);
2057 console.log(leadEmailRes)
2058
2059 returnData.failedCount += 1;
2060 returnData.failedList.push(newUser);
2061 customrrLogger.error(`RR_USER not found ------> ${emailId}`);
2062
2063 } else {
2064 let cellPan = users_workSheet.getCell(`F${i}`);
2065 let cellAddress = users_workSheet.getCell(`G${i}`);
2066 let cellLandmark = users_workSheet.getCell(`H${i}`);
2067 let cellCity = users_workSheet.getCell(`I${i}`);
2068 let cellState = users_workSheet.getCell(`J${i}`);
2069 let cellCountry = users_workSheet.getCell(`K${i}`);
2070 let cellPinCode = users_workSheet.getCell(`L${i}`);
2071 cellPan.value = customerInfo.pancard;
2072 cellAddress.value = customerInfo.address;
2073 cellLandmark.value = customerInfo.land_mark;
2074 cellCity.value = customerInfo.city;
2075 cellState.value = customerInfo.state;
2076 cellCountry.value = customerInfo.country;
2077 cellPinCode.value = customerInfo.pincode;
2078 //console.log("Cells: ", customerInfo.pancard, customerInfo.address);
2079 returnData.successCount +=1;
2080 returnData.newlyAddedList.push(newUser);
2081 customrrLogger.error(`Found ------> ${emailId}`);
2082 }
2083
2084
2085 }
2086 }
2087 }
2088 returnData.totalCount = totalCount
2089 } else {
2090 return res("Invalid data format", null);
2091 }
2092
2093 // console.log(customerInfoList.length);
2094 // console.log("_________________Fd____________________________");
2095
2096 // add/assign customers of that campaign.
2097 // for (let c = 0; c < customerInfoList.length; c++) {
2098
2099 // let custQry = `Select ru.id, ru.email,
2100 // (select value from rr_user_variables where user_id= ru.id and data_key='pancard' limit 1) as pancard,
2101 // (select value from rr_user_variables where user_id= ru.id and data_key='address' limit 1) as address,
2102 // (select value from rr_user_variables where user_id= ru.id and data_key='land_mark' limit 1) as land_mark,
2103 // (select value from rr_user_variables where user_id= ru.id and data_key='city' limit 1) as city,
2104 // (select value from rr_user_variables where user_id= ru.id and data_key='state' limit 1) as state,
2105 // (select value from rr_user_variables where user_id= ru.id and data_key='country' limit 1) as country,
2106 // (select value from rr_user_variables where user_id= ru.id and data_key='pincode' limit 1) as pincode
2107 // from rr_users as ru
2108 // Where ru.email = '${customerInfoList[c].email}'`;
2109 // let customerInfo = await commonSql.getDataFromMysqlByGivenQuery(custQry, 0);
2110
2111 // if (customerInfo == null) {
2112 // returnData.failedCount += 1;
2113 // returnData.failedList.push(customerInfoList[c]);
2114 // customrrLogger.error(`RR_USER not found ------> ${customerInfoList[c].leadId}`);
2115
2116 // } else {
2117
2118
2119 // }
2120
2121 // }
2122 console.log("************************ All data fetched ********************");
2123 UsersWB[methodType].writeFile(`CustList.xlsx`)
2124 .then(async function () {
2125 try {
2126 console.log("file saved!");
2127
2128 } catch (error) {
2129 console.log("Error in sending report to mail: ", error);
2130 }
2131 });
2132
2133 return res(null, returnData);
2134 });
2135 });
2136 }
2137 catch (e) {
2138 console.log("Error in Campaign Service uploadNewCustomers()", e.message);
2139 customrrLogger.error("Error in Campaign Service uploadNewCustomers()", e);
2140 return res(e.message, null);
2141 }
2142}
2143
2144
2145exports.uploadNewCustomers = async (req, loggedUser, reqData, res) => {
2146 try {
2147
2148 var returnData = {
2149 totalCount: 0,
2150 successCount: 0,
2151 failedCount: 0,
2152 modifiedCount: 0,
2153 newlyAddedCount: 0,
2154 modifiedList: [],
2155 failedList: [],
2156 newlyAddedList: []
2157 }
2158 let upload = multer(obj).single('customers_file');
2159 upload(req, res, async function (err) {
2160
2161 if (req.file == null || req.file == undefined) {
2162 return res("Please upload file", null);
2163 }
2164
2165 reqData = req.body;
2166 // console.log("reqData ^^^^^^^^^^^^^^^^^^^^^^ ",reqData);
2167 if (req.fileValidationError) {
2168 console.log("fileValidationError");
2169 return res("Invalid file", null);
2170 } else if (err) {
2171 console.log("err ", err);
2172 return res("Something went wrong", null);
2173 }
2174
2175
2176 var UsersWB = new Excel.Workbook();
2177 var customerInfoList = [];
2178 let methodType = 'xlsx';
2179 if (path.extname(req.file.originalname) == ".csv") {
2180 methodType = 'csv';
2181 } else if (path.extname(req.file.originalname) == ".xls" || path.extname(req.file.originalname) == ".xlsx") {
2182 methodType = 'xlsx';
2183 } else {
2184 return res("Invalid file format", null);
2185 }
2186 UsersWB[methodType].readFile(req.file.path).then(async function () {
2187 var users_workSheet = UsersWB.getWorksheet(1);
2188 let rowCount = users_workSheet.rowCount;
2189
2190 let totalCount = 0;
2191 if (users_workSheet.getRow(1).getCell(1).value.toLocaleLowerCase().startsWith("lead")) {
2192
2193 if (users_workSheet.getRow(1).getCell(2).value.toLowerCase().startsWith("email") == false ||
2194 users_workSheet.getRow(1).getCell(3).value.toLowerCase().startsWith("campaign") == false ||
2195 users_workSheet.getRow(1).getCell(4).value.toLowerCase().startsWith("agent") == false) {
2196 return res("Invalid data format", null);
2197 }
2198
2199 for (let i = 2; i <= rowCount; i++) {
2200 if (users_workSheet.getRow(i).getCell(1).value != null && users_workSheet.getRow(i).getCell(1).value != undefined) {
2201
2202 totalCount += 1;
2203 if (users_workSheet.getRow(i)) {
2204 let row = users_workSheet.getRow(i);
2205 let emailId = "";
2206 if (typeof (row.getCell(2).value) == 'object') {
2207 emailId = row.getCell(2).value.text;
2208 } else {
2209 emailId = row.getCell(2).value;
2210 }
2211 let campaignId = "";
2212 if (typeof (row.getCell(3).value) == 'object') {
2213 if (row.getCell(3).value !== null && row.getCell(3).value !== undefined && row.getCell(3).value !== '') {
2214 campaignId = row.getCell(3).value.text;
2215 }
2216 } else if (row.getCell(3).value !== null && row.getCell(3).value !== undefined && row.getCell(3).value !== '') {
2217
2218 campaignId = row.getCell(3).value;
2219 }
2220
2221 let agentEmail = "";
2222 if (typeof (row.getCell(4).value) == 'object') {
2223 agentEmail = row.getCell(4).value.text;
2224 } else {
2225 agentEmail = row.getCell(4).value;
2226 }
2227 customerInfoList.push({
2228 leadId: row.getCell(1).value,
2229 email: emailId.trim(),
2230 campaign: campaignId,
2231 agent: agentEmail.trim()
2232 })
2233 }
2234 }
2235 }
2236 returnData.totalCount = totalCount
2237 } else {
2238 return res("Invalid data format", null);
2239 }
2240
2241 // console.log(customerInfoList.length);
2242 // console.log("_________________Fd____________________________");
2243
2244 let allCampaignIds = [];
2245 let getAllCampaignQry = `Select JSON_ARRAYAGG(campaign_id) as campaign_id from rnr_crr_campaigns`;
2246 let getAllCampaignRes = await commonSql.getDataFromMysqlByGivenQuery(getAllCampaignQry, 0);
2247
2248 if (getAllCampaignRes != null) {
2249 allCampaignIds = getAllCampaignRes.campaign_id;
2250 }
2251
2252
2253 let customersCount = customerInfoList.length;
2254 // add/assign customers of that campaign.
2255 for (let c = 0; c < customerInfoList.length; c++) {
2256
2257 const custSearchQry = `Select id, email from rnr_crr_customers
2258 Where lead_id = ${customerInfoList[c].leadId}`;
2259 const custSearchRes = await commonSql.getDataFromMysqlByGivenQuery(custSearchQry, 0);
2260 console.log(customerInfoList[c].email, " ", c);
2261
2262 let campaignUpdateClause = '';
2263 let campaign_id = null;
2264 if (customerInfoList[c].campaign != "" && allCampaignIds.length > 0) {
2265 if (allCampaignIds.indexOf(+customerInfoList[c].campaign) != -1) {
2266 campaignUpdateClause = `, campaign_id = ${customerInfoList[c].campaign}`;
2267 campaign_id = +customerInfoList[c].campaign;
2268 }
2269 }
2270
2271 if (custSearchRes != null) {
2272
2273 const custUpdateQry = `Update rnr_crr_customers SET
2274 original_agent = '${customerInfoList[c].agent}' ${campaignUpdateClause} Where lead_id = ${customerInfoList[c].leadId}`;
2275 const custUpdateRes = await commonSql.updateByQuery(custUpdateQry);
2276
2277 returnData.successCount += 1;
2278 returnData.modifiedCount += 1;
2279 returnData.modifiedList.push(customerInfoList[c]);
2280 customrrLogger.error(`Already Exist ------> ${customerInfoList[c].leadId}`);
2281 } else {
2282
2283 let customerInfo = await custAssign.getCustomerFromRRUsers(customerInfoList[c].email);
2284
2285 if (customerInfo == null) {
2286 returnData.failedCount += 1;
2287 returnData.failedList.push(customerInfoList[c]);
2288 customrrLogger.error(`RR_USER not found ------> ${customerInfoList[c].leadId}`);
2289
2290 } else {
2291
2292 let leadEmailRes = await leadSquared.getLeadByEmail(customerInfo.email);
2293 let prospectId = leadEmailRes == null ? null : leadEmailRes.ProspectID;
2294 let custAddInfo = {
2295 leadId: customerInfoList[c].leadId,
2296 leadsquared_id: prospectId,
2297 user_id: customerInfo.id,
2298 full_name: customerInfo.name,
2299 email: customerInfo.email,
2300 phone: customerInfo.phone,
2301 campaign_id: campaign_id,
2302 original_agent: customerInfoList[c].agent,
2303 followup_date: '0'
2304 }
2305 let customerAdded = await addNewCustomerData(custAddInfo);
2306 if (customerAdded) {
2307
2308 returnData.successCount += 1;
2309 returnData.newlyAddedCount += 1;
2310 returnData.newlyAddedList.push(customerInfoList[c]);
2311 } else {
2312 returnData.failedCount += 1;
2313 returnData.failedList.push(customerInfoList[c]);
2314 customrrLogger.error(`Failed to add ------> ${customerInfoList[c].leadId}`);
2315
2316 }
2317 }
2318 }
2319 }
2320
2321 return res(null, returnData);
2322 });
2323 });
2324 }
2325 catch (e) {
2326 console.log("Error in Campaign Service uploadNewCustomers()", e.message);
2327 customrrLogger.error("Error in Campaign Service uploadNewCustomers()", e);
2328 return res(e.message, null);
2329 }
2330}
2331
2332
2333async function addNewCustomerData(reqData) {
2334 try {
2335 let isCustomerAdded = await conn.promise().query(`INSERT INTO rnr_crr_customers (lead_id,full_name,email,phone,original_agent, followup_date, user_id,leadsquared_id, campaign_id) VALUES (?,?,?,?,?,?,?,?,?)`,
2336 [
2337 reqData.leadId,
2338 reqData.full_name,
2339 reqData.email,
2340 reqData.phone,
2341 reqData.original_agent,
2342 reqData.followup_date,
2343 reqData.user_id,
2344 reqData.leadsquared_id,
2345 reqData.campaign_id,
2346 ],
2347 async (error, portfolio_results, fields) => {
2348 if (error) {
2349 return false;
2350 }
2351 return true;
2352 })
2353
2354 return isCustomerAdded;
2355 }
2356 catch (e) {
2357 console.log("Error in addNewCustomerData()", e.message);
2358 return false;
2359 }
2360}
2361