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 _0xa07a=['includes','push','TagIds','field','GROUP_CONCAT(DISTINCT\x20t.id)','expr','getSelectedTags','forEach','CONCAT(\x27,\x27,\x20subqry.TagIds,\x20\x27,\x27)\x20LIKE\x20?','replace','length','MATCH\x20(c.firstName,\x20c.lastName,\x20c.phone,\x20c.mobile,\x20c.fax,\x20c.email)\x20AGAINST\x20(?\x20IN\x20BOOLEAN\x20MODE)','isNumeric','c.phone\x20LIKE\x20?','c.mobile\x20LIKE\x20?','c.fax\x20LIKE\x20?','isEmail','c.firstName\x20LIKE\x20?','c.email\x20LIKE\x20?','parseSearch','conditions','find','name','dateOfBirth','DATE','type','default','text','start','end','Tag','value','split','clause','tags','ids','User','user','and','skip','map','some','compact','Contact','c.ListId\x20IN\x20?','concat','CONCAT_WS(\x27\x20\x27,\x20c.firstName,\x20c.lastName,\x20c.email)\x20LIKE\x20?','uc.UserId\x20=\x20?','\x20=\x20?','resolve','then','fields','tag','role','getLists','c.ListId\x20IS\x20NULL','attributes','group','c.id','limit','offset','subqry.id','having','order','DESC','subqry.','findAll','color','keyBy','Sequelize','QueryTypes','SELECT','CmContact','sequelize','query','toString','Tags','COUNT(*)','isEmpty','t.id\x20IN\x20?','exports','../parsers/advancedSearch','../parsers/qs','../interaction/utils','../../mysqldb','agent','search','ListId','autocomplete','true','select','from','where','c.deletedAt\x20IS\x20NULL','subqry','left_join','cm_user_has_contacts','uc.CmContactId\x20=\x20','.id','cm_contact_has_tags','ct.CmContactId\x20=\x20','firstName','lastName','phone','mobile','fax','CompanyId','createdAt','updatedAt'];(function(_0x5a5f05,_0x234d2a){var _0x52a1d4=function(_0x4d9e7c){while(--_0x4d9e7c){_0x5a5f05['push'](_0x5a5f05['shift']());}};_0x52a1d4(++_0x234d2a);}(_0xa07a,0xbe));var _0xaa07=function(_0x4b0637,_0x18d2c8){_0x4b0637=_0x4b0637-0x0;var _0x2239c4=_0xa07a[_0x4b0637];return _0x2239c4;};'use strict';var _=require('lodash');var squel=require('squel');var as=require(_0xaa07('0x0'));var qs=require(_0xaa07('0x1'));var utils=require(_0xaa07('0x2'));var db=require(_0xaa07('0x3'))['db'];function skipContactsQuery(_0x54a8cc,_0x3e13eb){var _0x14f0c3=![];if(_0x54a8cc['role']===_0xaa07('0x4')&&!_0x3e13eb[_0xaa07('0x5')]&&!_0x3e13eb[_0xaa07('0x6')]&&_0x3e13eb[_0xaa07('0x7')]!==_0xaa07('0x8')){_0x14f0c3=!![];}return _0x14f0c3;}function getContactsSubquery(){return squel[_0xaa07('0x9')]()[_0xaa07('0xa')]('cm_contacts','c')[_0xaa07('0xb')](_0xaa07('0xc'));}function applyJoinClauses(_0x2a4e78,_0x30855b){var _0x5ea234=_0x30855b?'c':_0xaa07('0xd');_0x2a4e78[_0xaa07('0xe')](_0xaa07('0xf'),'uc',_0xaa07('0x10')+_0x5ea234+_0xaa07('0x11'))[_0xaa07('0xe')](_0xaa07('0x12'),'ct',_0xaa07('0x13')+_0x5ea234+_0xaa07('0x11'))['left_join']('tools_tags','t','t.id\x20=\x20ct.TagId');return _0x2a4e78;}function addFieldsToQuery(_0x56de0d,_0x530eab,_0x51fdd3,_0x10e457,_0x1fe152){var _0x381fdb=_0x1fe152?'c':'subqry';if(!_0x530eab){_0x530eab=['id',_0xaa07('0x14'),_0xaa07('0x15'),_0xaa07('0x16'),_0xaa07('0x17'),_0xaa07('0x18'),'email',_0xaa07('0x6'),_0xaa07('0x19'),_0xaa07('0x1a'),_0xaa07('0x1b'),'deletedAt','TagIds'];}else if(!_0x530eab[_0xaa07('0x1c')]('id')){_0x530eab[_0xaa07('0x1d')]('id');}var _0x5cb913=_0x51fdd3&&_0x1fe152||!_0x51fdd3&&!_0x1fe152;if(_0x10e457){_0x5cb913=_0x10e457&&_0x1fe152||!_0x10e457&&!_0x1fe152;}_0x530eab['forEach'](function(_0x2cfc5e){if(_0x2cfc5e===_0xaa07('0x1e')){if(_0x5cb913){_0x56de0d[_0xaa07('0x1f')](_0xaa07('0x20'),_0x2cfc5e);}else if(_0x51fdd3||_0x10e457){_0x56de0d[_0xaa07('0x1f')](_0x381fdb+'.'+_0x2cfc5e);}}else{_0x56de0d['field'](_0x381fdb+'.'+_0x2cfc5e);}});return _0x56de0d;}function getSqlClauses(){var _0x39b033={'default':squel['expr'](),'fields':squel[_0xaa07('0x21')](),'filter':squel[_0xaa07('0x21')](),'tags':{'clause':squel['expr'](),'ids':null},'user':{'clause':squel[_0xaa07('0x21')](),'skip':![]}};return _0x39b033;}function parseTagParameter(_0x952eb1){var _0x2d40fe=squel[_0xaa07('0x21')]();if(_0x952eb1){var _0x46a9a8=utils[_0xaa07('0x22')](_0x952eb1);_0x46a9a8[_0xaa07('0x23')](function(_0x1bbabf){_0x2d40fe['or'](_0xaa07('0x24'),'%,'+_0x1bbabf+',%');});}return _0x2d40fe;}function parseSearchFilter(_0xd124b3){var _0x27fe81=squel[_0xaa07('0x21')]();if(_0xd124b3){var _0x15ec63=_0xd124b3[_0xaa07('0x25')]('\x5c','\x5c\x5c')[_0xaa07('0x25')](/'/g,'\x27\x27');if(_0x15ec63['split']('\x20')[_0xaa07('0x26')]>0x1){_0x27fe81['or'](_0xaa07('0x27'),qs['getFullTextValue'](_0x15ec63,!![]),null);}else{if(qs[_0xaa07('0x28')](_0x15ec63)){_0x27fe81['or']('c.id\x20LIKE\x20?',_0x15ec63+'%');_0x27fe81['or'](_0xaa07('0x29'),_0x15ec63+'%');_0x27fe81['or'](_0xaa07('0x2a'),_0x15ec63+'%');_0x27fe81['or'](_0xaa07('0x2b'),_0x15ec63+'%');}else if(qs[_0xaa07('0x2c')](_0x15ec63)){_0x27fe81['or']('c.email\x20LIKE\x20?',_0x15ec63+'%');}else{_0x27fe81['or'](_0xaa07('0x2d'),'%'+_0x15ec63+'%');_0x27fe81['or']('c.lastName\x20LIKE\x20?','%'+_0x15ec63+'%');_0x27fe81['or'](_0xaa07('0x2e'),'%'+_0x15ec63+'%');}}}return _0x27fe81;}function parseAdvancedSearchFilter(_0x53b894,_0x393ec4){var _0x5b3d97=getSqlClauses();var _0x130f62=as[_0xaa07('0x2f')](_0x53b894);var _0x458742=_0x130f62['sqlOperator'];for(var _0x4561b4=0x0;_0x4561b4<_0x130f62[_0xaa07('0x30')][_0xaa07('0x26')];_0x4561b4++){var _0x3181c6=_0x130f62[_0xaa07('0x30')][_0x4561b4];var _0x3da2be='c';var _0x5dd761=_[_0xaa07('0x31')](_0x393ec4,[_0xaa07('0x32'),_0x3181c6[_0xaa07('0x1f')]]);if(_0x5dd761){if(_0x3181c6['field']===_0xaa07('0x33')||_0x5dd761['fieldType']==='datetime'){_0x3181c6['castTo']=_0xaa07('0x34');}var _0x40f5b4=as['buildExpression'](_0x3da2be,_0x5dd761[_0xaa07('0x35')],_0x3181c6);_0x5b3d97[_0xaa07('0x36')][_0x458742](_0x40f5b4[_0xaa07('0x37')],_0x40f5b4['value'][_0xaa07('0x38')],_0x40f5b4['value'][_0xaa07('0x39')]);}else{if(_0x3181c6['field']===_0xaa07('0x3a')){var _0x45783a=_0x3181c6[_0xaa07('0x3b')][_0xaa07('0x3c')](',')['map'](function(_0xb52a3d){return Number(_0xb52a3d);});var _0x2dafcd=parseTagParameter(_0x45783a);_0x5b3d97['tags'][_0xaa07('0x3d')]['and'](_0x2dafcd);_0x5b3d97[_0xaa07('0x3e')][_0xaa07('0x3f')]=_0x45783a;}else if(_0x3181c6[_0xaa07('0x1f')]===_0xaa07('0x40')){_0x5b3d97[_0xaa07('0x41')]['clause'][_0xaa07('0x42')]('uc.UserId\x20=\x20?',_0x3181c6[_0xaa07('0x3b')]);_0x5b3d97[_0xaa07('0x41')][_0xaa07('0x43')]=!![];}}}return _0x5b3d97;}function parseModelFields(_0x563eb8,_0x4413de){var _0x425ce1=squel[_0xaa07('0x21')]();var _0x2904eb=_(_0x563eb8)['keys']()[_0xaa07('0x44')](function(_0x1db2f6){return _[_0xaa07('0x45')](_0x4413de,[_0xaa07('0x32'),_0x1db2f6])?_0x1db2f6:undefined;})[_0xaa07('0x46')]()['value']();if(_0x563eb8['Contact']){_0x2904eb[_0xaa07('0x1d')](_0xaa07('0x47'));}if(_0x563eb8['User']){_0x2904eb[_0xaa07('0x1d')](_0xaa07('0x40'));}if(_0x2904eb[_0xaa07('0x26')]>0x0){_0x2904eb[_0xaa07('0x23')](function(_0x1f7da3){if(_0x1f7da3==='ListId'){_0x425ce1[_0xaa07('0x42')](_0xaa07('0x48'),[][_0xaa07('0x49')](_0x563eb8[_0x1f7da3]));}else if(_0x1f7da3===_0xaa07('0x47')){var _0x382ff3='%'+_0x563eb8[_0x1f7da3]+'%';_0x425ce1[_0xaa07('0x42')](_0xaa07('0x4a'),_0x382ff3);}else if(_0x1f7da3===_0xaa07('0x40')){_0x425ce1[_0xaa07('0x42')](_0xaa07('0x4b'),_0x563eb8[_0x1f7da3]);}else{_0x425ce1[_0xaa07('0x42')]('c.'+_0x1f7da3+_0xaa07('0x4c'),_0x563eb8[_0x1f7da3]);}});}return _0x425ce1;}function parseQueryParameters(_0x1a011c,_0x589ba2){var _0xf2767e=getSqlClauses();return Promise[_0xaa07('0x4d')]()[_0xaa07('0x4e')](function(){return parseModelFields(_0x1a011c,_0x589ba2);})[_0xaa07('0x4e')](function(_0xa231f6){_0xf2767e[_0xaa07('0x4f')][_0xaa07('0x42')](_0xa231f6);return parseTagParameter(_0x1a011c[_0xaa07('0x50')]);})[_0xaa07('0x4e')](function(_0x4d727c){_0xf2767e[_0xaa07('0x3e')]['clause'][_0xaa07('0x42')](_0x4d727c);return parseSearchFilter(_0x1a011c['filter']);})[_0xaa07('0x4e')](function(_0x427eae){_0xf2767e['filter']=_0x427eae;return _0xf2767e;});}function checkListsRestrictions(_0x2c904b,_0x509976,_0x16848b){var _0x2dea00=squel[_0xaa07('0x21')]();return Promise['resolve']()[_0xaa07('0x4e')](function(){if(_0x509976['search']&&_0x2c904b[_0xaa07('0x51')]===_0xaa07('0x4')&&!_0x16848b){return _0x2c904b[_0xaa07('0x52')]({'attributes':['id'],'raw':!![]});}})['then'](function(_0x588bff){if(_0x588bff){if(_0x588bff[_0xaa07('0x26')]===0x0){_0x2dea00['and'](_0xaa07('0x53'));}else{_0x2dea00[_0xaa07('0x42')](_0xaa07('0x48'),_[_0xaa07('0x44')](_0x588bff,'id'));}}})[_0xaa07('0x4e')](function(){return _0x2dea00;});}function buildContactsQuery(_0x52e8eb,_0x3ff0a9,_0x138690,_0x576803,_0xf0604b,_0x290af4){if(_0x3ff0a9){_0x3ff0a9=_0x138690[_0xaa07('0x54')];}_0x52e8eb=addFieldsToQuery(_0x52e8eb,_0x3ff0a9,_0x576803,_0x290af4,!![]);var _0x23e165=squel[_0xaa07('0x9')]();_0x23e165=addFieldsToQuery(_0x23e165,_0x3ff0a9,_0x576803,_0x290af4,![]);if(_0x576803||_0x290af4){_0x52e8eb=applyJoinClauses(_0x52e8eb,!![]);_0x52e8eb[_0xaa07('0x55')](_0xaa07('0x56'));if(_0x138690[_0xaa07('0x57')]){_0x23e165[_0xaa07('0x57')](_0x138690[_0xaa07('0x57')]);}if(_0x138690[_0xaa07('0x58')]){_0x23e165[_0xaa07('0x58')](_0x138690[_0xaa07('0x58')]);}}else{_0x23e165=applyJoinClauses(_0x23e165,![]);_0x23e165['group'](_0xaa07('0x59'));if(_0x138690[_0xaa07('0x57')]){_0x52e8eb['limit'](_0x138690[_0xaa07('0x57')]);}if(_0x138690[_0xaa07('0x58')]){_0x52e8eb[_0xaa07('0x58')](_0x138690[_0xaa07('0x58')]);}}_0x23e165[_0xaa07('0x5a')](_0xf0604b);if(_0x138690[_0xaa07('0x5b')]){_0x138690['order'][_0xaa07('0x23')](function(_0x43b66d){_0x52e8eb['order']('c.'+_0x43b66d[0x0],_0x43b66d[0x1]===_0xaa07('0x5c')?![]:!![]);_0x23e165[_0xaa07('0x5b')](_0xaa07('0x5d')+_0x43b66d[0x0],_0x43b66d[0x1]==='DESC'?![]:!![]);});}_0x23e165[_0xaa07('0xa')](_0x52e8eb,_0xaa07('0xd'));return _0x23e165;}function getFilteredContacts(_0x1709c5){var _0x1dc8de=[];return db[_0xaa07('0x3a')][_0xaa07('0x5e')]({'attributes':['id','name',_0xaa07('0x5f')],'raw':!![]})[_0xaa07('0x4e')](function(_0x4910c9){_0x1dc8de=_[_0xaa07('0x60')](_0x4910c9,'id');var _0x5924ed={'type':db[_0xaa07('0x61')][_0xaa07('0x62')][_0xaa07('0x63')],'model':db[_0xaa07('0x64')],'mapToModel':!![],'nest':!![],'raw':!![]};return db[_0xaa07('0x65')][_0xaa07('0x66')](_0x1709c5[_0xaa07('0x67')](),_0x5924ed);})[_0xaa07('0x4e')](function(_0x518f66){_0x518f66['forEach'](function(_0x33f2b5){if(_0x33f2b5[_0xaa07('0x1e')]){_0x33f2b5[_0xaa07('0x68')]=[];_0x33f2b5[_0xaa07('0x1e')][_0xaa07('0x3c')](',')['forEach'](function(_0x2c8e9b){_0x33f2b5[_0xaa07('0x68')][_0xaa07('0x1d')](_0x1dc8de[_0x2c8e9b]);});}delete _0x33f2b5['TagIds'];});return _0x518f66;});}function countFilteredContacts(_0x7c5e5c,_0x55524c){var _0x59153c=_0x7c5e5c['clone']();_0x59153c[_0xaa07('0x1f')](_0xaa07('0x69'));_0x59153c=applyJoinClauses(_0x59153c,!![]);_0x7c5e5c['group'](_0xaa07('0x56'));var _0x4b7b5a=utils[_0xaa07('0x22')](_0x55524c);if(!_[_0xaa07('0x6a')](_0x4b7b5a)){_0x59153c[_0xaa07('0xb')](_0xaa07('0x6b'),_0x4b7b5a);}var _0xcef175={'type':db[_0xaa07('0x61')]['QueryTypes']['SELECT'],'raw':!![]};return db[_0xaa07('0x65')]['query'](_0x59153c[_0xaa07('0x67')](),_0xcef175)[_0xaa07('0x4e')](function(_0x1cde24){return _0x1cde24[0x0][_0xaa07('0x69')];});}module[_0xaa07('0x6c')]={'buildContactsQuery':buildContactsQuery,'checkListsRestrictions':checkListsRestrictions,'countFilteredContacts':countFilteredContacts,'getContactsSubquery':getContactsSubquery,'getFilteredContacts':getFilteredContacts,'parseAdvancedSearchFilter':parseAdvancedSearchFilter,'parseQueryParameters':parseQueryParameters,'skipContactsQuery':skipContactsQuery};