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 _0x7312=['select','from','cm_contacts','where','c.deletedAt\x20IS\x20NULL','subqry','cm_user_has_contacts','uc.CmContactId\x20=\x20','.id','left_join','cm_contact_has_tags','t.id\x20=\x20ct.TagId','firstName','lastName','phone','mobile','email','CompanyId','createdAt','updatedAt','deletedAt','includes','push','TagIds','field','expr','replace','escapeLIKESpecialChars','length','getFullTextValue','isNumeric','c.id\x20LIKE\x20?','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?','sqlOperator','conditions','find','name','dateOfBirth','fieldType','datetime','castTo','DATE','type','value','start','end','Tag','map','tags','clause','ids','user','and','uc.UserId\x20=\x20?','skip','some','Contact','forEach','c.ListId\x20IN\x20?','concat','CONCAT_WS(\x27\x20\x27,\x20c.firstName,\x20c.lastName,\x20c.email)\x20LIKE\x20?','User','\x20=\x20?','resolve','then','fields','tag','filter','getLists','c.ListId\x20IS\x20NULL','attributes','group','offset','subqry.id','limit','having','order','subqry.','DESC','color','keyBy','Sequelize','SELECT','CmContact','query','toString','Tags','split','clone','COUNT(*)','c.id','getSelectedTags','isEmpty','t.id\x20IN\x20?','QueryTypes','sequelize','exports','lodash','squel','../parsers/advancedSearch','../interaction/utils','../../mysqldb','role','agent','search','ListId','autocomplete','true'];(function(_0x512a22,_0x59d3ad){var _0x504680=function(_0x2705e5){while(--_0x2705e5){_0x512a22['push'](_0x512a22['shift']());}};_0x504680(++_0x59d3ad);}(_0x7312,0x14b));var _0x2731=function(_0x11c573,_0x9e7c6e){_0x11c573=_0x11c573-0x0;var _0x6b72de=_0x7312[_0x11c573];return _0x6b72de;};'use strict';var _=require(_0x2731('0x0'));var squel=require(_0x2731('0x1'));var as=require(_0x2731('0x2'));var qs=require('../parsers/qs');var utils=require(_0x2731('0x3'));var db=require(_0x2731('0x4'))['db'];function skipContactsQuery(_0x566640,_0x2a80e2){var _0x3729ad=![];if(_0x566640[_0x2731('0x5')]===_0x2731('0x6')&&!_0x2a80e2[_0x2731('0x7')]&&!_0x2a80e2[_0x2731('0x8')]&&_0x2a80e2[_0x2731('0x9')]!==_0x2731('0xa')){_0x3729ad=!![];}return _0x3729ad;}function getContactsSubquery(){return squel[_0x2731('0xb')]()[_0x2731('0xc')](_0x2731('0xd'),'c')[_0x2731('0xe')](_0x2731('0xf'));}function applyJoinClauses(_0x4fe091,_0x5d66bd){var _0x29a862=_0x5d66bd?'c':_0x2731('0x10');_0x4fe091['left_join'](_0x2731('0x11'),'uc',_0x2731('0x12')+_0x29a862+_0x2731('0x13'))[_0x2731('0x14')](_0x2731('0x15'),'ct','ct.CmContactId\x20=\x20'+_0x29a862+_0x2731('0x13'))[_0x2731('0x14')]('tools_tags','t',_0x2731('0x16'));return _0x4fe091;}function addFieldsToQuery(_0x52e3fb,_0x1f05b2,_0x23cde0,_0x3c2694,_0x227e91){var _0x47c843=_0x227e91?'c':'subqry';if(!_0x1f05b2){_0x1f05b2=['id',_0x2731('0x17'),_0x2731('0x18'),_0x2731('0x19'),_0x2731('0x1a'),'fax',_0x2731('0x1b'),_0x2731('0x8'),_0x2731('0x1c'),_0x2731('0x1d'),_0x2731('0x1e'),_0x2731('0x1f'),'TagIds'];}else if(!_0x1f05b2[_0x2731('0x20')]('id')){_0x1f05b2[_0x2731('0x21')]('id');}var _0x403ecb=_0x23cde0&&_0x227e91||!_0x23cde0&&!_0x227e91;if(_0x3c2694){_0x403ecb=_0x3c2694&&_0x227e91||!_0x3c2694&&!_0x227e91;}_0x1f05b2['forEach'](function(_0x2f4ffc){if(_0x2f4ffc===_0x2731('0x22')){if(_0x403ecb){_0x52e3fb[_0x2731('0x23')]('GROUP_CONCAT(DISTINCT\x20t.id)',_0x2f4ffc);}else if(_0x23cde0||_0x3c2694){_0x52e3fb[_0x2731('0x23')](_0x47c843+'.'+_0x2f4ffc);}}else{_0x52e3fb['field'](_0x47c843+'.'+_0x2f4ffc);}});return _0x52e3fb;}function getSqlClauses(){var _0x2301ff={'default':squel['expr'](),'fields':squel[_0x2731('0x24')](),'filter':squel[_0x2731('0x24')](),'tags':{'clause':squel[_0x2731('0x24')](),'ids':null},'user':{'clause':squel[_0x2731('0x24')](),'skip':![]}};return _0x2301ff;}function parseTagParameter(_0x587850){var _0x2a9f68=squel['expr']();if(_0x587850){var _0x28fec1=utils['getSelectedTags'](_0x587850);_0x28fec1['forEach'](function(_0x50e358){_0x2a9f68['or']('CONCAT(\x27,\x27,\x20subqry.TagIds,\x20\x27,\x27)\x20LIKE\x20?','%,'+_0x50e358+',%');});}return _0x2a9f68;}function parseSearchFilter(_0x2103fb){var _0x34eecd=squel['expr']();if(_0x2103fb){var _0x2ff7ac=_0x2103fb[_0x2731('0x25')]('\x5c','\x5c\x5c')[_0x2731('0x25')](/'/g,'\x27\x27');var _0x54c961=qs[_0x2731('0x26')](_0x2ff7ac);if(_0x2ff7ac['split']('\x20')[_0x2731('0x27')]>0x1){_0x34eecd['or']('MATCH\x20(c.firstName,\x20c.lastName,\x20c.phone,\x20c.mobile,\x20c.fax,\x20c.email)\x20AGAINST\x20(?\x20IN\x20BOOLEAN\x20MODE)',qs[_0x2731('0x28')](_0x2ff7ac,!![]),null);}else{if(qs[_0x2731('0x29')](_0x2ff7ac)){_0x34eecd['or'](_0x2731('0x2a'),_0x54c961+'%');_0x34eecd['or'](_0x2731('0x2b'),_0x54c961+'%');_0x34eecd['or'](_0x2731('0x2c'),_0x54c961+'%');_0x34eecd['or'](_0x2731('0x2d'),_0x54c961+'%');}else if(qs[_0x2731('0x2e')](_0x2ff7ac)){_0x34eecd['or'](_0x2731('0x2f'),_0x54c961+'%');}else{_0x34eecd['or'](_0x2731('0x30'),'%'+_0x54c961+'%');_0x34eecd['or'](_0x2731('0x31'),'%'+_0x54c961+'%');_0x34eecd['or'](_0x2731('0x2f'),'%'+_0x54c961+'%');}}}return _0x34eecd;}function parseAdvancedSearchFilter(_0x4c6eac,_0x4dc54a){var _0x37e5ce=getSqlClauses();var _0x3900f7=as['parseSearch'](_0x4c6eac);var _0x15473a=_0x3900f7[_0x2731('0x32')];for(var _0x3a51f4=0x0;_0x3a51f4<_0x3900f7[_0x2731('0x33')][_0x2731('0x27')];_0x3a51f4++){var _0x13e202=_0x3900f7[_0x2731('0x33')][_0x3a51f4];var _0x1a58bc='c';var _0x2d20b1=_[_0x2731('0x34')](_0x4dc54a,[_0x2731('0x35'),_0x13e202[_0x2731('0x23')]]);if(_0x2d20b1){if(_0x13e202['field']===_0x2731('0x36')||_0x2d20b1[_0x2731('0x37')]===_0x2731('0x38')){_0x13e202[_0x2731('0x39')]=_0x2731('0x3a');}var _0x5c2ef1=as['buildExpression'](_0x1a58bc,_0x2d20b1[_0x2731('0x3b')],_0x13e202);_0x37e5ce['default'][_0x15473a](_0x5c2ef1['text'],_0x5c2ef1[_0x2731('0x3c')][_0x2731('0x3d')],_0x5c2ef1[_0x2731('0x3c')][_0x2731('0x3e')]);}else{if(_0x13e202[_0x2731('0x23')]===_0x2731('0x3f')){var _0x248363=_0x13e202[_0x2731('0x3c')]['split'](',')[_0x2731('0x40')](function(_0x5910ec){return Number(_0x5910ec);});var _0x1906e0=parseTagParameter(_0x248363);_0x37e5ce[_0x2731('0x41')][_0x2731('0x42')]['and'](_0x1906e0);_0x37e5ce[_0x2731('0x41')][_0x2731('0x43')]=_0x248363;}else if(_0x13e202[_0x2731('0x23')]==='User'){_0x37e5ce[_0x2731('0x44')][_0x2731('0x42')][_0x2731('0x45')](_0x2731('0x46'),_0x13e202[_0x2731('0x3c')]);_0x37e5ce[_0x2731('0x44')][_0x2731('0x47')]=!![];}}}return _0x37e5ce;}function parseModelFields(_0xfa8203,_0xa7571b){var _0xba1645=squel[_0x2731('0x24')]();var _0x3554ba=_(_0xfa8203)['keys']()[_0x2731('0x40')](function(_0x222690){return _[_0x2731('0x48')](_0xa7571b,[_0x2731('0x35'),_0x222690])?_0x222690:undefined;})['compact']()[_0x2731('0x3c')]();if(_0xfa8203[_0x2731('0x49')]){_0x3554ba[_0x2731('0x21')]('Contact');}if(_0xfa8203['User']){_0x3554ba[_0x2731('0x21')]('User');}if(_0x3554ba[_0x2731('0x27')]>0x0){_0x3554ba[_0x2731('0x4a')](function(_0x2e0119){if(_0x2e0119===_0x2731('0x8')){_0xba1645['and'](_0x2731('0x4b'),[][_0x2731('0x4c')](_0xfa8203[_0x2e0119]));}else if(_0x2e0119==='Contact'){var _0x338c7d='%'+_0xfa8203[_0x2e0119]+'%';_0xba1645[_0x2731('0x45')](_0x2731('0x4d'),_0x338c7d);}else if(_0x2e0119===_0x2731('0x4e')){_0xba1645[_0x2731('0x45')]('uc.UserId\x20=\x20?',_0xfa8203[_0x2e0119]);}else{_0xba1645[_0x2731('0x45')]('c.'+_0x2e0119+_0x2731('0x4f'),_0xfa8203[_0x2e0119]);}});}return _0xba1645;}function parseQueryParameters(_0x55f3b3,_0x3b8537){var _0x3cab9b=getSqlClauses();return Promise[_0x2731('0x50')]()[_0x2731('0x51')](function(){return parseModelFields(_0x55f3b3,_0x3b8537);})[_0x2731('0x51')](function(_0xf5a641){_0x3cab9b[_0x2731('0x52')]['and'](_0xf5a641);return parseTagParameter(_0x55f3b3[_0x2731('0x53')]);})[_0x2731('0x51')](function(_0x232e16){_0x3cab9b[_0x2731('0x41')][_0x2731('0x42')]['and'](_0x232e16);return parseSearchFilter(_0x55f3b3['filter']);})[_0x2731('0x51')](function(_0x29de52){_0x3cab9b[_0x2731('0x54')]=_0x29de52;return _0x3cab9b;});}function checkListsRestrictions(_0x5ae939,_0x168219,_0x145b35){var _0x434e5d=squel[_0x2731('0x24')]();return Promise[_0x2731('0x50')]()[_0x2731('0x51')](function(){if(_0x168219['search']&&_0x5ae939[_0x2731('0x5')]==='agent'&&!_0x145b35){return _0x5ae939[_0x2731('0x55')]({'attributes':['id'],'raw':!![]});}})[_0x2731('0x51')](function(_0x58f43f){if(_0x58f43f){if(_0x58f43f[_0x2731('0x27')]===0x0){_0x434e5d[_0x2731('0x45')](_0x2731('0x56'));}else{_0x434e5d['and'](_0x2731('0x4b'),_['map'](_0x58f43f,'id'));}}})[_0x2731('0x51')](function(){return _0x434e5d;});}function buildContactsQuery(_0x4348e5,_0x100851,_0x4576cd,_0x4841d6,_0x5911bd,_0x2ac3d1){if(_0x100851){_0x100851=_0x4576cd[_0x2731('0x57')];}_0x4348e5=addFieldsToQuery(_0x4348e5,_0x100851,_0x4841d6,_0x2ac3d1,!![]);var _0x468886=squel[_0x2731('0xb')]();_0x468886=addFieldsToQuery(_0x468886,_0x100851,_0x4841d6,_0x2ac3d1,![]);if(_0x4841d6||_0x2ac3d1){_0x4348e5=applyJoinClauses(_0x4348e5,!![]);_0x4348e5[_0x2731('0x58')]('c.id');if(_0x4576cd['limit']){_0x468886['limit'](_0x4576cd['limit']);}if(_0x4576cd[_0x2731('0x59')]){_0x468886[_0x2731('0x59')](_0x4576cd[_0x2731('0x59')]);}}else{_0x468886=applyJoinClauses(_0x468886,![]);_0x468886[_0x2731('0x58')](_0x2731('0x5a'));if(_0x4576cd['limit']){_0x4348e5['limit'](_0x4576cd[_0x2731('0x5b')]);}if(_0x4576cd[_0x2731('0x59')]){_0x4348e5['offset'](_0x4576cd['offset']);}}_0x468886[_0x2731('0x5c')](_0x5911bd);if(_0x4576cd[_0x2731('0x5d')]){_0x4576cd[_0x2731('0x5d')][_0x2731('0x4a')](function(_0x282172){_0x4348e5[_0x2731('0x5d')]('c.'+_0x282172[0x0],_0x282172[0x1]==='DESC'?![]:!![]);_0x468886[_0x2731('0x5d')](_0x2731('0x5e')+_0x282172[0x0],_0x282172[0x1]===_0x2731('0x5f')?![]:!![]);});}_0x468886[_0x2731('0xc')](_0x4348e5,_0x2731('0x10'));return _0x468886;}function getFilteredContacts(_0x37edb3){var _0x47bbfe=[];return db['Tag']['findAll']({'attributes':['id',_0x2731('0x35'),_0x2731('0x60')],'raw':!![]})[_0x2731('0x51')](function(_0xd4cd40){_0x47bbfe=_[_0x2731('0x61')](_0xd4cd40,'id');var _0x168280={'type':db[_0x2731('0x62')]['QueryTypes'][_0x2731('0x63')],'model':db[_0x2731('0x64')],'mapToModel':!![],'nest':!![],'raw':!![]};return db['sequelize'][_0x2731('0x65')](_0x37edb3[_0x2731('0x66')](),_0x168280);})['then'](function(_0x5b2265){_0x5b2265[_0x2731('0x4a')](function(_0x3e29f7){if(_0x3e29f7[_0x2731('0x22')]){_0x3e29f7[_0x2731('0x67')]=[];_0x3e29f7['TagIds'][_0x2731('0x68')](',')[_0x2731('0x4a')](function(_0x5809ff){_0x3e29f7['Tags']['push'](_0x47bbfe[_0x5809ff]);});}delete _0x3e29f7['TagIds'];});return _0x5b2265;});}function countFilteredContacts(_0x3260fb,_0x2d5ca2){var _0xbda441=_0x3260fb[_0x2731('0x69')]();_0xbda441[_0x2731('0x23')](_0x2731('0x6a'));_0xbda441=applyJoinClauses(_0xbda441,!![]);_0x3260fb[_0x2731('0x58')](_0x2731('0x6b'));var _0x2a6071=utils[_0x2731('0x6c')](_0x2d5ca2);if(!_[_0x2731('0x6d')](_0x2a6071)){_0xbda441[_0x2731('0xe')](_0x2731('0x6e'),_0x2a6071);}var _0x4e92e9={'type':db[_0x2731('0x62')][_0x2731('0x6f')][_0x2731('0x63')],'raw':!![]};return db[_0x2731('0x70')][_0x2731('0x65')](_0xbda441[_0x2731('0x66')](),_0x4e92e9)[_0x2731('0x51')](function(_0x14c119){return _0x14c119[0x0]['COUNT(*)'];});}module[_0x2731('0x71')]={'buildContactsQuery':buildContactsQuery,'checkListsRestrictions':checkListsRestrictions,'countFilteredContacts':countFilteredContacts,'getContactsSubquery':getContactsSubquery,'getFilteredContacts':getFilteredContacts,'parseAdvancedSearchFilter':parseAdvancedSearchFilter,'parseQueryParameters':parseQueryParameters,'skipContactsQuery':skipContactsQuery};