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 _0x96c1=['c.ListId\x20IS\x20NULL','select','group','c.id','limit','offset','subqry.id','having','order','DESC','from','findAll','color','keyBy','Sequelize','QueryTypes','SELECT','CmContact','sequelize','query','toString','Tags','clone','COUNT(*)','isEmpty','where','t.id\x20IN\x20?','exports','lodash','squel','../parsers/advancedSearch','../parsers/qs','../interaction/utils','role','agent','autocomplete','true','cm_contacts','c.deletedAt\x20IS\x20NULL','left_join','cm_user_has_contacts','.id','cm_contact_has_tags','ct.CmContactId\x20=\x20','tools_tags','t.id\x20=\x20ct.TagId','subqry','firstName','phone','mobile','fax','email','ListId','CompanyId','createdAt','TagIds','includes','push','forEach','field','GROUP_CONCAT(DISTINCT\x20t.id)','expr','getSelectedTags','CONCAT(\x27,\x27,\x20subqry.TagIds,\x20\x27,\x27)\x20LIKE\x20?','replace','split','length','MATCH\x20(c.firstName,\x20c.lastName,\x20c.phone,\x20c.mobile,\x20c.fax,\x20c.email)\x20AGAINST\x20(?\x20IN\x20BOOLEAN\x20MODE)','getFullTextValue','isNumeric','c.id\x20LIKE\x20?','c.phone\x20LIKE\x20?','c.mobile\x20LIKE\x20?','isEmail','c.lastName\x20LIKE\x20?','c.email\x20LIKE\x20?','sqlOperator','conditions','find','dateOfBirth','fieldType','datetime','castTo','default','text','value','start','tags','clause','and','ids','User','user','uc.UserId\x20=\x20?','skip','keys','map','some','Contact','c.ListId\x20IN\x20?','concat','CONCAT_WS(\x27\x20\x27,\x20c.firstName,\x20c.lastName,\x20c.email)\x20LIKE\x20?','\x20=\x20?','resolve','then','fields','filter','search','getLists'];(function(_0x2c91e9,_0x2fb965){var _0x13f0ff=function(_0xb39edf){while(--_0xb39edf){_0x2c91e9['push'](_0x2c91e9['shift']());}};_0x13f0ff(++_0x2fb965);}(_0x96c1,0x89));var _0x196c=function(_0x2398c2,_0x561947){_0x2398c2=_0x2398c2-0x0;var _0xb99579=_0x96c1[_0x2398c2];return _0xb99579;};'use strict';var _=require(_0x196c('0x0'));var squel=require(_0x196c('0x1'));var as=require(_0x196c('0x2'));var qs=require(_0x196c('0x3'));var utils=require(_0x196c('0x4'));var db=require('../../mysqldb')['db'];function skipContactsQuery(_0x5b7146,_0x4b75b9){var _0x14690d=![];if(_0x5b7146[_0x196c('0x5')]===_0x196c('0x6')&&!_0x4b75b9['search']&&!_0x4b75b9['ListId']&&_0x4b75b9[_0x196c('0x7')]!==_0x196c('0x8')){_0x14690d=!![];}return _0x14690d;}function getContactsSubquery(){return squel['select']()['from'](_0x196c('0x9'),'c')['where'](_0x196c('0xa'));}function applyJoinClauses(_0x55546d,_0x3c29a8){var _0x320e89=_0x3c29a8?'c':'subqry';_0x55546d[_0x196c('0xb')](_0x196c('0xc'),'uc','uc.CmContactId\x20=\x20'+_0x320e89+_0x196c('0xd'))[_0x196c('0xb')](_0x196c('0xe'),'ct',_0x196c('0xf')+_0x320e89+_0x196c('0xd'))[_0x196c('0xb')](_0x196c('0x10'),'t',_0x196c('0x11'));return _0x55546d;}function addFieldsToQuery(_0x58d803,_0x387ce0,_0x349ac9,_0x12931e,_0x3277e7){var _0x50b9ef=_0x3277e7?'c':_0x196c('0x12');if(!_0x387ce0){_0x387ce0=['id',_0x196c('0x13'),'lastName',_0x196c('0x14'),_0x196c('0x15'),_0x196c('0x16'),_0x196c('0x17'),_0x196c('0x18'),_0x196c('0x19'),_0x196c('0x1a'),'updatedAt','deletedAt',_0x196c('0x1b')];}else if(!_0x387ce0[_0x196c('0x1c')]('id')){_0x387ce0[_0x196c('0x1d')]('id');}var _0x5343f9=_0x349ac9&&_0x3277e7||!_0x349ac9&&!_0x3277e7;if(_0x12931e){_0x5343f9=_0x12931e&&_0x3277e7||!_0x12931e&&!_0x3277e7;}_0x387ce0[_0x196c('0x1e')](function(_0x4274bf){if(_0x4274bf===_0x196c('0x1b')){if(_0x5343f9){_0x58d803[_0x196c('0x1f')](_0x196c('0x20'),_0x4274bf);}else if(_0x349ac9||_0x12931e){_0x58d803[_0x196c('0x1f')](_0x50b9ef+'.'+_0x4274bf);}}else{_0x58d803[_0x196c('0x1f')](_0x50b9ef+'.'+_0x4274bf);}});return _0x58d803;}function getSqlClauses(){var _0x30fb70={'default':squel[_0x196c('0x21')](),'fields':squel[_0x196c('0x21')](),'filter':squel['expr'](),'tags':{'clause':squel[_0x196c('0x21')](),'ids':null},'user':{'clause':squel[_0x196c('0x21')](),'skip':![]}};return _0x30fb70;}function parseTagParameter(_0x4fa139){var _0x1abb88=squel[_0x196c('0x21')]();if(_0x4fa139){var _0x5ea778=utils[_0x196c('0x22')](_0x4fa139);_0x5ea778['forEach'](function(_0x4a4389){_0x1abb88['or'](_0x196c('0x23'),'%,'+_0x4a4389+',%');});}return _0x1abb88;}function parseSearchFilter(_0x16969f){var _0xc134cd=squel[_0x196c('0x21')]();if(_0x16969f){var _0x52345b=_0x16969f[_0x196c('0x24')]('\x5c','\x5c\x5c')['replace'](/'/g,'\x27\x27');if(_0x52345b[_0x196c('0x25')]('\x20')[_0x196c('0x26')]>0x1){_0xc134cd['or'](_0x196c('0x27'),qs[_0x196c('0x28')](_0x52345b,!![]),null);}else{if(qs[_0x196c('0x29')](_0x52345b)){_0xc134cd['or'](_0x196c('0x2a'),_0x52345b+'%');_0xc134cd['or'](_0x196c('0x2b'),_0x52345b+'%');_0xc134cd['or'](_0x196c('0x2c'),_0x52345b+'%');_0xc134cd['or']('c.fax\x20LIKE\x20?',_0x52345b+'%');}else if(qs[_0x196c('0x2d')](_0x52345b)){_0xc134cd['or']('c.email\x20LIKE\x20?',_0x52345b+'%');}else{_0xc134cd['or']('c.firstName\x20LIKE\x20?','%'+_0x52345b+'%');_0xc134cd['or'](_0x196c('0x2e'),'%'+_0x52345b+'%');_0xc134cd['or'](_0x196c('0x2f'),'%'+_0x52345b+'%');}}}return _0xc134cd;}function parseAdvancedSearchFilter(_0x5ad4c2,_0x3083ef){var _0x3194bf=getSqlClauses();var _0x3f1619=as['parseSearch'](_0x5ad4c2);var _0x1418d9=_0x3f1619[_0x196c('0x30')];for(var _0x510777=0x0;_0x510777<_0x3f1619[_0x196c('0x31')][_0x196c('0x26')];_0x510777++){var _0x531e72=_0x3f1619['conditions'][_0x510777];var _0x88a3b3='c';var _0x1c9203=_[_0x196c('0x32')](_0x3083ef,['name',_0x531e72[_0x196c('0x1f')]]);if(_0x1c9203){if(_0x531e72[_0x196c('0x1f')]===_0x196c('0x33')||_0x1c9203[_0x196c('0x34')]===_0x196c('0x35')){_0x531e72[_0x196c('0x36')]='DATE';}var _0x34a65c=as['buildExpression'](_0x88a3b3,_0x1c9203['type'],_0x531e72);_0x3194bf[_0x196c('0x37')][_0x1418d9](_0x34a65c[_0x196c('0x38')],_0x34a65c[_0x196c('0x39')][_0x196c('0x3a')],_0x34a65c['value']['end']);}else{if(_0x531e72[_0x196c('0x1f')]==='Tag'){var _0x305e2c=_0x531e72['value']['split'](',')['map'](function(_0x4256d7){return Number(_0x4256d7);});var _0x38abc1=parseTagParameter(_0x305e2c);_0x3194bf[_0x196c('0x3b')][_0x196c('0x3c')][_0x196c('0x3d')](_0x38abc1);_0x3194bf['tags'][_0x196c('0x3e')]=_0x305e2c;}else if(_0x531e72[_0x196c('0x1f')]===_0x196c('0x3f')){_0x3194bf[_0x196c('0x40')][_0x196c('0x3c')]['and'](_0x196c('0x41'),_0x531e72['value']);_0x3194bf['user'][_0x196c('0x42')]=!![];}}}return _0x3194bf;}function parseModelFields(_0x130817,_0x34c052){var _0x4b1681=squel['expr']();var _0x2ec593=_(_0x130817)[_0x196c('0x43')]()[_0x196c('0x44')](function(_0x52f55d){return _[_0x196c('0x45')](_0x34c052,['name',_0x52f55d])?_0x52f55d:undefined;})['compact']()[_0x196c('0x39')]();if(_0x130817[_0x196c('0x46')]){_0x2ec593[_0x196c('0x1d')](_0x196c('0x46'));}if(_0x130817[_0x196c('0x3f')]){_0x2ec593[_0x196c('0x1d')](_0x196c('0x3f'));}if(_0x2ec593[_0x196c('0x26')]>0x0){_0x2ec593['forEach'](function(_0x4d37f0){if(_0x4d37f0===_0x196c('0x18')){_0x4b1681[_0x196c('0x3d')](_0x196c('0x47'),[][_0x196c('0x48')](_0x130817[_0x4d37f0]));}else if(_0x4d37f0===_0x196c('0x46')){var _0x39ff67='%'+_0x130817[_0x4d37f0]+'%';_0x4b1681[_0x196c('0x3d')](_0x196c('0x49'),_0x39ff67);}else if(_0x4d37f0===_0x196c('0x3f')){_0x4b1681[_0x196c('0x3d')]('uc.UserId\x20=\x20?',_0x130817[_0x4d37f0]);}else{_0x4b1681['and']('c.'+_0x4d37f0+_0x196c('0x4a'),_0x130817[_0x4d37f0]);}});}return _0x4b1681;}function parseQueryParameters(_0x2ee736,_0x5c2fb4){var _0x3df1ae=getSqlClauses();return Promise[_0x196c('0x4b')]()['then'](function(){return parseModelFields(_0x2ee736,_0x5c2fb4);})[_0x196c('0x4c')](function(_0x3cf07d){_0x3df1ae[_0x196c('0x4d')][_0x196c('0x3d')](_0x3cf07d);return parseTagParameter(_0x2ee736['tag']);})[_0x196c('0x4c')](function(_0x338a45){_0x3df1ae[_0x196c('0x3b')][_0x196c('0x3c')]['and'](_0x338a45);return parseSearchFilter(_0x2ee736[_0x196c('0x4e')]);})[_0x196c('0x4c')](function(_0x5e57c1){_0x3df1ae[_0x196c('0x4e')]=_0x5e57c1;return _0x3df1ae;});}function checkListsRestrictions(_0x103a59,_0x288dc0,_0x2ba573){var _0x116b3b=squel[_0x196c('0x21')]();return Promise[_0x196c('0x4b')]()[_0x196c('0x4c')](function(){if(_0x288dc0[_0x196c('0x4f')]&&_0x103a59['role']===_0x196c('0x6')&&!_0x2ba573){return _0x103a59[_0x196c('0x50')]({'attributes':['id'],'raw':!![]});}})[_0x196c('0x4c')](function(_0xb515f){if(_0xb515f){if(_0xb515f[_0x196c('0x26')]===0x0){_0x116b3b[_0x196c('0x3d')](_0x196c('0x51'));}else{_0x116b3b[_0x196c('0x3d')](_0x196c('0x47'),_[_0x196c('0x44')](_0xb515f,'id'));}}})[_0x196c('0x4c')](function(){return _0x116b3b;});}function buildContactsQuery(_0x4c1676,_0x987370,_0x447037,_0x195210,_0xb99abd,_0x44ae58){if(_0x987370){_0x987370=_0x447037['attributes'];}_0x4c1676=addFieldsToQuery(_0x4c1676,_0x987370,_0x195210,_0x44ae58,!![]);var _0x2cf362=squel[_0x196c('0x52')]();_0x2cf362=addFieldsToQuery(_0x2cf362,_0x987370,_0x195210,_0x44ae58,![]);if(_0x195210||_0x44ae58){_0x4c1676=applyJoinClauses(_0x4c1676,!![]);_0x4c1676[_0x196c('0x53')](_0x196c('0x54'));if(_0x447037[_0x196c('0x55')]){_0x2cf362[_0x196c('0x55')](_0x447037[_0x196c('0x55')]);}if(_0x447037[_0x196c('0x56')]){_0x2cf362[_0x196c('0x56')](_0x447037['offset']);}}else{_0x2cf362=applyJoinClauses(_0x2cf362,![]);_0x2cf362[_0x196c('0x53')](_0x196c('0x57'));if(_0x447037[_0x196c('0x55')]){_0x4c1676[_0x196c('0x55')](_0x447037['limit']);}if(_0x447037[_0x196c('0x56')]){_0x4c1676['offset'](_0x447037['offset']);}}_0x2cf362[_0x196c('0x58')](_0xb99abd);if(_0x447037['order']){_0x447037[_0x196c('0x59')][_0x196c('0x1e')](function(_0x5a6b9f){_0x4c1676[_0x196c('0x59')]('c.'+_0x5a6b9f[0x0],_0x5a6b9f[0x1]==='DESC'?![]:!![]);_0x2cf362[_0x196c('0x59')]('subqry.'+_0x5a6b9f[0x0],_0x5a6b9f[0x1]===_0x196c('0x5a')?![]:!![]);});}_0x2cf362[_0x196c('0x5b')](_0x4c1676,_0x196c('0x12'));return _0x2cf362;}function getFilteredContacts(_0x42afe1){var _0x4fa61e=[];return db['Tag'][_0x196c('0x5c')]({'attributes':['id','name',_0x196c('0x5d')],'raw':!![]})['then'](function(_0x1965f8){_0x4fa61e=_[_0x196c('0x5e')](_0x1965f8,'id');var _0x349225={'type':db[_0x196c('0x5f')][_0x196c('0x60')][_0x196c('0x61')],'model':db[_0x196c('0x62')],'mapToModel':!![],'nest':!![],'raw':!![]};return db[_0x196c('0x63')][_0x196c('0x64')](_0x42afe1[_0x196c('0x65')](),_0x349225);})[_0x196c('0x4c')](function(_0x5541de){_0x5541de[_0x196c('0x1e')](function(_0x5ce8a7){if(_0x5ce8a7['TagIds']){_0x5ce8a7[_0x196c('0x66')]=[];_0x5ce8a7['TagIds'][_0x196c('0x25')](',')[_0x196c('0x1e')](function(_0x52eb0d){_0x5ce8a7[_0x196c('0x66')]['push'](_0x4fa61e[_0x52eb0d]);});}delete _0x5ce8a7[_0x196c('0x1b')];});return _0x5541de;});}function countFilteredContacts(_0x4998c8,_0x19a9db){var _0x256c78=_0x4998c8[_0x196c('0x67')]();_0x256c78['field'](_0x196c('0x68'));_0x256c78=applyJoinClauses(_0x256c78,!![]);_0x4998c8['group'](_0x196c('0x54'));var _0x498938=utils[_0x196c('0x22')](_0x19a9db);if(!_[_0x196c('0x69')](_0x498938)){_0x256c78[_0x196c('0x6a')](_0x196c('0x6b'),_0x498938);}var _0x485623={'type':db['Sequelize']['QueryTypes'][_0x196c('0x61')],'raw':!![]};return db[_0x196c('0x63')][_0x196c('0x64')](_0x256c78[_0x196c('0x65')](),_0x485623)[_0x196c('0x4c')](function(_0x2266ac){return _0x2266ac[0x0][_0x196c('0x68')];});}module[_0x196c('0x6c')]={'buildContactsQuery':buildContactsQuery,'checkListsRestrictions':checkListsRestrictions,'countFilteredContacts':countFilteredContacts,'getContactsSubquery':getContactsSubquery,'getFilteredContacts':getFilteredContacts,'parseAdvancedSearchFilter':parseAdvancedSearchFilter,'parseQueryParameters':parseQueryParameters,'skipContactsQuery':skipContactsQuery};