server/components/contacts/sql-utils.js

   1 // *************************************************************************
   2 // *                                                                       *
   3 // * xCALLY Motion -  The Omnichannel Contact Center                       *
   4 // * Copyright (c) Xenialab s.r.l. All Rights Reserved                     *
   5 // *                                                                       *
   6 // *************************************************************************
   7 // *                                                                       *
   8 // * Email: info@xcally.com                                                *
   9 // * Website: https://www.xcally.com                                       *
  10 // *                                                                       *
  11 // *************************************************************************
  12 // *                                                                       *
  13 // * The SOFTWARE PRODUCT is protected by copyright laws and international *
  14 // * copyright treaties, as well as other intellectual property laws and   *
  15 // * treaties. The SOFTWARE PRODUCT is licensed, not sold.                         *
  16 // *                                                                       *
  17 // *************************************************************************
  18 var _0x32fd=['keyBy','Sequelize','QueryTypes','SELECT','CmContact','sequelize','query','toString','Tags','clone','COUNT(*)','isEmpty','t.id\x20IN\x20?','lodash','squel','../parsers/advancedSearch','role','agent','ListId','autocomplete','true','select','from','cm_contacts','where','c.deletedAt\x20IS\x20NULL','subqry','left_join','.id','cm_contact_has_tags','tools_tags','t.id\x20=\x20ct.TagId','firstName','lastName','phone','mobile','email','CompanyId','createdAt','updatedAt','deletedAt','TagIds','push','field','expr','getSelectedTags','forEach','replace','MATCH\x20(c.firstName,\x20c.lastName,\x20c.phone,\x20c.mobile,\x20c.fax,\x20c.email)\x20AGAINST\x20(?\x20IN\x20BOOLEAN\x20MODE)','getFullTextValue','c.phone\x20LIKE\x20?','c.mobile\x20LIKE\x20?','c.fax\x20LIKE\x20?','isEmail','c.email\x20LIKE\x20?','c.firstName\x20LIKE\x20?','c.lastName\x20LIKE\x20?','parseSearch','sqlOperator','conditions','length','find','dateOfBirth','fieldType','datetime','castTo','DATE','buildExpression','type','default','text','value','start','end','split','map','tags','clause','and','user','uc.UserId\x20=\x20?','skip','name','compact','Contact','User','c.ListId\x20IN\x20?','concat','CONCAT_WS(\x27\x20\x27,\x20c.firstName,\x20c.lastName,\x20c.email)\x20LIKE\x20?','\x20=\x20?','resolve','then','fields','tag','filter','search','getLists','c.ListId\x20IS\x20NULL','attributes','group','c.id','limit','offset','having','order','DESC','Tag','color'];(function(_0x33546a,_0x2386fe){var _0x21e7d1=function(_0x4a6e30){while(--_0x4a6e30){_0x33546a['push'](_0x33546a['shift']());}};_0x21e7d1(++_0x2386fe);}(_0x32fd,0x1bd));var _0xd32f=function(_0x3dd15e,_0x1f0015){_0x3dd15e=_0x3dd15e-0x0;var _0x231fd0=_0x32fd[_0x3dd15e];return _0x231fd0;};'use strict';var _=require(_0xd32f('0x0'));var squel=require(_0xd32f('0x1'));var as=require(_0xd32f('0x2'));var qs=require('../parsers/qs');var utils=require('../interaction/utils');var db=require('../../mysqldb')['db'];function skipContactsQuery(_0x9d24ad,_0x21115f){var _0x21bc94=![];if(_0x9d24ad[_0xd32f('0x3')]===_0xd32f('0x4')&&!_0x21115f['search']&&!_0x21115f[_0xd32f('0x5')]&&_0x21115f[_0xd32f('0x6')]!==_0xd32f('0x7')){_0x21bc94=!![];}return _0x21bc94;}function getContactsSubquery(){return squel[_0xd32f('0x8')]()[_0xd32f('0x9')](_0xd32f('0xa'),'c')[_0xd32f('0xb')](_0xd32f('0xc'));}function applyJoinClauses(_0x29f9d0,_0x3a2ee1){var _0x248f37=_0x3a2ee1?'c':_0xd32f('0xd');_0x29f9d0[_0xd32f('0xe')]('cm_user_has_contacts','uc','uc.CmContactId\x20=\x20'+_0x248f37+_0xd32f('0xf'))[_0xd32f('0xe')](_0xd32f('0x10'),'ct','ct.CmContactId\x20=\x20'+_0x248f37+'.id')[_0xd32f('0xe')](_0xd32f('0x11'),'t',_0xd32f('0x12'));return _0x29f9d0;}function addFieldsToQuery(_0x298b54,_0x59796b,_0x3533a3,_0x537784,_0x18b597){var _0x1e0572=_0x18b597?'c':_0xd32f('0xd');if(!_0x59796b){_0x59796b=['id',_0xd32f('0x13'),_0xd32f('0x14'),_0xd32f('0x15'),_0xd32f('0x16'),'fax',_0xd32f('0x17'),_0xd32f('0x5'),_0xd32f('0x18'),_0xd32f('0x19'),_0xd32f('0x1a'),_0xd32f('0x1b'),_0xd32f('0x1c')];}else if(!_0x59796b['includes']('id')){_0x59796b[_0xd32f('0x1d')]('id');}var _0x42a90a=_0x3533a3&&_0x18b597||!_0x3533a3&&!_0x18b597;if(_0x537784){_0x42a90a=_0x537784&&_0x18b597||!_0x537784&&!_0x18b597;}_0x59796b['forEach'](function(_0x10b959){if(_0x10b959===_0xd32f('0x1c')){if(_0x42a90a){_0x298b54[_0xd32f('0x1e')]('GROUP_CONCAT(DISTINCT\x20t.id)',_0x10b959);}else if(_0x3533a3||_0x537784){_0x298b54[_0xd32f('0x1e')](_0x1e0572+'.'+_0x10b959);}}else{_0x298b54[_0xd32f('0x1e')](_0x1e0572+'.'+_0x10b959);}});return _0x298b54;}function getSqlClauses(){var _0x5f39b6={'default':squel['expr'](),'fields':squel['expr'](),'filter':squel[_0xd32f('0x1f')](),'tags':{'clause':squel[_0xd32f('0x1f')](),'ids':null},'user':{'clause':squel[_0xd32f('0x1f')](),'skip':![]}};return _0x5f39b6;}function parseTagParameter(_0x3d63d7){var _0x5575eb=squel[_0xd32f('0x1f')]();if(_0x3d63d7){var _0x49cc4c=utils[_0xd32f('0x20')](_0x3d63d7);_0x49cc4c[_0xd32f('0x21')](function(_0x4d89cb){_0x5575eb['or']('CONCAT(\x27,\x27,\x20subqry.TagIds,\x20\x27,\x27)\x20LIKE\x20?','%,'+_0x4d89cb+',%');});}return _0x5575eb;}function parseSearchFilter(_0x38b320){var _0x35d138=squel[_0xd32f('0x1f')]();if(_0x38b320){var _0x493df3=_0x38b320[_0xd32f('0x22')]('\x5c','\x5c\x5c')['replace'](/'/g,'\x27\x27');if(_0x493df3['split']('\x20')['length']>0x1){_0x35d138['or'](_0xd32f('0x23'),qs[_0xd32f('0x24')](_0x493df3,!![]),null);}else{if(qs['isNumeric'](_0x493df3)){_0x35d138['or']('c.id\x20LIKE\x20?',_0x493df3+'%');_0x35d138['or'](_0xd32f('0x25'),_0x493df3+'%');_0x35d138['or'](_0xd32f('0x26'),_0x493df3+'%');_0x35d138['or'](_0xd32f('0x27'),_0x493df3+'%');}else if(qs[_0xd32f('0x28')](_0x493df3)){_0x35d138['or'](_0xd32f('0x29'),_0x493df3+'%');}else{_0x35d138['or'](_0xd32f('0x2a'),'%'+_0x493df3+'%');_0x35d138['or'](_0xd32f('0x2b'),'%'+_0x493df3+'%');_0x35d138['or']('c.email\x20LIKE\x20?','%'+_0x493df3+'%');}}}return _0x35d138;}function parseAdvancedSearchFilter(_0x4e9ac2,_0x3166f9){var _0x4fbe11=getSqlClauses();var _0x4eaf73=as[_0xd32f('0x2c')](_0x4e9ac2);var _0x3802da=_0x4eaf73[_0xd32f('0x2d')];for(var _0x40ac86=0x0;_0x40ac86<_0x4eaf73[_0xd32f('0x2e')][_0xd32f('0x2f')];_0x40ac86++){var _0x430737=_0x4eaf73[_0xd32f('0x2e')][_0x40ac86];var _0x15abef='c';var _0x355451=_[_0xd32f('0x30')](_0x3166f9,['name',_0x430737[_0xd32f('0x1e')]]);if(_0x355451){if(_0x430737[_0xd32f('0x1e')]===_0xd32f('0x31')||_0x355451[_0xd32f('0x32')]===_0xd32f('0x33')){_0x430737[_0xd32f('0x34')]=_0xd32f('0x35');}var _0x1c7c1d=as[_0xd32f('0x36')](_0x15abef,_0x355451[_0xd32f('0x37')],_0x430737);_0x4fbe11[_0xd32f('0x38')][_0x3802da](_0x1c7c1d[_0xd32f('0x39')],_0x1c7c1d[_0xd32f('0x3a')][_0xd32f('0x3b')],_0x1c7c1d[_0xd32f('0x3a')][_0xd32f('0x3c')]);}else{if(_0x430737['field']==='Tag'){var _0x470309=_0x430737[_0xd32f('0x3a')][_0xd32f('0x3d')](',')[_0xd32f('0x3e')](function(_0x204bd8){return Number(_0x204bd8);});var _0xb43702=parseTagParameter(_0x470309);_0x4fbe11[_0xd32f('0x3f')][_0xd32f('0x40')][_0xd32f('0x41')](_0xb43702);_0x4fbe11[_0xd32f('0x3f')]['ids']=_0x470309;}else if(_0x430737[_0xd32f('0x1e')]==='User'){_0x4fbe11[_0xd32f('0x42')][_0xd32f('0x40')]['and'](_0xd32f('0x43'),_0x430737[_0xd32f('0x3a')]);_0x4fbe11['user'][_0xd32f('0x44')]=!![];}}}return _0x4fbe11;}function parseModelFields(_0x888331,_0x5b8c97){var _0x3720cf=squel[_0xd32f('0x1f')]();var _0x227b90=_(_0x888331)['keys']()[_0xd32f('0x3e')](function(_0x493849){return _['some'](_0x5b8c97,[_0xd32f('0x45'),_0x493849])?_0x493849:undefined;})[_0xd32f('0x46')]()[_0xd32f('0x3a')]();if(_0x888331['Contact']){_0x227b90[_0xd32f('0x1d')](_0xd32f('0x47'));}if(_0x888331[_0xd32f('0x48')]){_0x227b90[_0xd32f('0x1d')](_0xd32f('0x48'));}if(_0x227b90[_0xd32f('0x2f')]>0x0){_0x227b90['forEach'](function(_0x513835){if(_0x513835===_0xd32f('0x5')){_0x3720cf[_0xd32f('0x41')](_0xd32f('0x49'),[][_0xd32f('0x4a')](_0x888331[_0x513835]));}else if(_0x513835==='Contact'){var _0x70763='%'+_0x888331[_0x513835]+'%';_0x3720cf['and'](_0xd32f('0x4b'),_0x70763);}else if(_0x513835===_0xd32f('0x48')){_0x3720cf[_0xd32f('0x41')]('uc.UserId\x20=\x20?',_0x888331[_0x513835]);}else{_0x3720cf['and']('c.'+_0x513835+_0xd32f('0x4c'),_0x888331[_0x513835]);}});}return _0x3720cf;}function parseQueryParameters(_0x372c24,_0x20844a){var _0x3f33d1=getSqlClauses();return Promise[_0xd32f('0x4d')]()[_0xd32f('0x4e')](function(){return parseModelFields(_0x372c24,_0x20844a);})['then'](function(_0x51f872){_0x3f33d1[_0xd32f('0x4f')][_0xd32f('0x41')](_0x51f872);return parseTagParameter(_0x372c24[_0xd32f('0x50')]);})[_0xd32f('0x4e')](function(_0x147092){_0x3f33d1['tags']['clause'][_0xd32f('0x41')](_0x147092);return parseSearchFilter(_0x372c24[_0xd32f('0x51')]);})['then'](function(_0x50ebc1){_0x3f33d1[_0xd32f('0x51')]=_0x50ebc1;return _0x3f33d1;});}function checkListsRestrictions(_0x16cacf,_0x20c547,_0x268d3d){var _0x5b0c55=squel[_0xd32f('0x1f')]();return Promise[_0xd32f('0x4d')]()[_0xd32f('0x4e')](function(){if(_0x20c547[_0xd32f('0x52')]&&_0x16cacf[_0xd32f('0x3')]===_0xd32f('0x4')&&!_0x268d3d){return _0x16cacf[_0xd32f('0x53')]({'attributes':['id'],'raw':!![]});}})[_0xd32f('0x4e')](function(_0x5c0906){if(_0x5c0906){if(_0x5c0906[_0xd32f('0x2f')]===0x0){_0x5b0c55[_0xd32f('0x41')](_0xd32f('0x54'));}else{_0x5b0c55[_0xd32f('0x41')](_0xd32f('0x49'),_[_0xd32f('0x3e')](_0x5c0906,'id'));}}})[_0xd32f('0x4e')](function(){return _0x5b0c55;});}function buildContactsQuery(_0x415728,_0x29e1d1,_0x30346e,_0x4ac419,_0x2e924f,_0x2f4e81){if(_0x29e1d1){_0x29e1d1=_0x30346e[_0xd32f('0x55')];}_0x415728=addFieldsToQuery(_0x415728,_0x29e1d1,_0x4ac419,_0x2f4e81,!![]);var _0x353264=squel['select']();_0x353264=addFieldsToQuery(_0x353264,_0x29e1d1,_0x4ac419,_0x2f4e81,![]);if(_0x4ac419||_0x2f4e81){_0x415728=applyJoinClauses(_0x415728,!![]);_0x415728[_0xd32f('0x56')](_0xd32f('0x57'));if(_0x30346e[_0xd32f('0x58')]){_0x353264['limit'](_0x30346e[_0xd32f('0x58')]);}if(_0x30346e[_0xd32f('0x59')]){_0x353264[_0xd32f('0x59')](_0x30346e[_0xd32f('0x59')]);}}else{_0x353264=applyJoinClauses(_0x353264,![]);_0x353264[_0xd32f('0x56')]('subqry.id');if(_0x30346e['limit']){_0x415728[_0xd32f('0x58')](_0x30346e[_0xd32f('0x58')]);}if(_0x30346e[_0xd32f('0x59')]){_0x415728[_0xd32f('0x59')](_0x30346e[_0xd32f('0x59')]);}}_0x353264[_0xd32f('0x5a')](_0x2e924f);if(_0x30346e['order']){_0x30346e[_0xd32f('0x5b')][_0xd32f('0x21')](function(_0x4abbc0){_0x415728[_0xd32f('0x5b')]('c.'+_0x4abbc0[0x0],_0x4abbc0[0x1]==='DESC'?![]:!![]);_0x353264[_0xd32f('0x5b')]('subqry.'+_0x4abbc0[0x0],_0x4abbc0[0x1]===_0xd32f('0x5c')?![]:!![]);});}_0x353264[_0xd32f('0x9')](_0x415728,_0xd32f('0xd'));return _0x353264;}function getFilteredContacts(_0x5cfc5c){var _0x36d598=[];return db[_0xd32f('0x5d')]['findAll']({'attributes':['id',_0xd32f('0x45'),_0xd32f('0x5e')],'raw':!![]})[_0xd32f('0x4e')](function(_0xd11337){_0x36d598=_[_0xd32f('0x5f')](_0xd11337,'id');var _0x386641={'type':db[_0xd32f('0x60')][_0xd32f('0x61')][_0xd32f('0x62')],'model':db[_0xd32f('0x63')],'mapToModel':!![],'nest':!![],'raw':!![]};return db[_0xd32f('0x64')][_0xd32f('0x65')](_0x5cfc5c[_0xd32f('0x66')](),_0x386641);})[_0xd32f('0x4e')](function(_0x2aa3e4){_0x2aa3e4[_0xd32f('0x21')](function(_0x9f7b99){if(_0x9f7b99[_0xd32f('0x1c')]){_0x9f7b99[_0xd32f('0x67')]=[];_0x9f7b99['TagIds'][_0xd32f('0x3d')](',')['forEach'](function(_0x1d4e1a){_0x9f7b99['Tags'][_0xd32f('0x1d')](_0x36d598[_0x1d4e1a]);});}delete _0x9f7b99[_0xd32f('0x1c')];});return _0x2aa3e4;});}function countFilteredContacts(_0x2e9fae,_0x2caf88){var _0x535ef5=_0x2e9fae[_0xd32f('0x68')]();_0x535ef5[_0xd32f('0x1e')](_0xd32f('0x69'));_0x535ef5=applyJoinClauses(_0x535ef5,!![]);_0x2e9fae[_0xd32f('0x56')](_0xd32f('0x57'));var _0x5ac8bc=utils['getSelectedTags'](_0x2caf88);if(!_[_0xd32f('0x6a')](_0x5ac8bc)){_0x535ef5[_0xd32f('0xb')](_0xd32f('0x6b'),_0x5ac8bc);}var _0xbd61ad={'type':db[_0xd32f('0x60')][_0xd32f('0x61')][_0xd32f('0x62')],'raw':!![]};return db[_0xd32f('0x64')][_0xd32f('0x65')](_0x535ef5[_0xd32f('0x66')](),_0xbd61ad)[_0xd32f('0x4e')](function(_0x558b9f){return _0x558b9f[0x0][_0xd32f('0x69')];});}module['exports']={'buildContactsQuery':buildContactsQuery,'checkListsRestrictions':checkListsRestrictions,'countFilteredContacts':countFilteredContacts,'getContactsSubquery':getContactsSubquery,'getFilteredContacts':getFilteredContacts,'parseAdvancedSearchFilter':parseAdvancedSearchFilter,'parseQueryParameters':parseQueryParameters,'skipContactsQuery':skipContactsQuery};