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 _0xc91a=['c.ListId\x20IN\x20?','CONCAT_WS(\x27\x20\x27,\x20c.firstName,\x20c.lastName,\x20c.email)\x20LIKE\x20?','fields','tag','then','filter','role','attributes','c.id','limit','offset','group','subqry.id','order','DESC','subqry.','findAll','color','keyBy','Sequelize','SELECT','CmContact','sequelize','query','Tags','clone','COUNT(*)','isEmpty','QueryTypes','exports','lodash','../parsers/advancedSearch','../parsers/qs','../interaction/utils','../../mysqldb','agent','search','ListId','true','select','from','cm_contacts','where','c.deletedAt\x20IS\x20NULL','subqry','left_join','uc.CmContactId\x20=\x20','.id','cm_contact_has_tags','ct.CmContactId\x20=\x20','t.id\x20=\x20ct.TagId','lastName','phone','mobile','fax','email','CompanyId','updatedAt','deletedAt','TagIds','includes','push','forEach','field','expr','getSelectedTags','CONCAT(\x27,\x27,\x20subqry.TagIds,\x20\x27,\x27)\x20LIKE\x20?','replace','split','length','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?','parseSearch','sqlOperator','conditions','find','name','fieldType','datetime','castTo','DATE','buildExpression','type','default','value','start','Tag','map','clause','and','tags','User','user','uc.UserId\x20=\x20?','skip','keys','some','compact','Contact'];(function(_0x10852d,_0x4cdcc6){var _0x13a8d6=function(_0x2ece16){while(--_0x2ece16){_0x10852d['push'](_0x10852d['shift']());}};_0x13a8d6(++_0x4cdcc6);}(_0xc91a,0x88));var _0xac91=function(_0x305be3,_0x2a807a){_0x305be3=_0x305be3-0x0;var _0x3f8584=_0xc91a[_0x305be3];return _0x3f8584;};'use strict';var _=require(_0xac91('0x0'));var squel=require('squel');var as=require(_0xac91('0x1'));var qs=require(_0xac91('0x2'));var utils=require(_0xac91('0x3'));var db=require(_0xac91('0x4'))['db'];function skipContactsQuery(_0x3108c9,_0x48325e){var _0x5f1356=![];if(_0x3108c9['role']===_0xac91('0x5')&&!_0x48325e[_0xac91('0x6')]&&!_0x48325e[_0xac91('0x7')]&&_0x48325e['autocomplete']!==_0xac91('0x8')){_0x5f1356=!![];}return _0x5f1356;}function getContactsSubquery(){return squel[_0xac91('0x9')]()[_0xac91('0xa')](_0xac91('0xb'),'c')[_0xac91('0xc')](_0xac91('0xd'));}function applyJoinClauses(_0x5d830a,_0x35c2d9){var _0x5abf40=_0x35c2d9?'c':_0xac91('0xe');_0x5d830a[_0xac91('0xf')]('cm_user_has_contacts','uc',_0xac91('0x10')+_0x5abf40+_0xac91('0x11'))[_0xac91('0xf')](_0xac91('0x12'),'ct',_0xac91('0x13')+_0x5abf40+'.id')[_0xac91('0xf')]('tools_tags','t',_0xac91('0x14'));return _0x5d830a;}function addFieldsToQuery(_0x361704,_0x2a0dc9,_0x16c46e,_0x581e1c,_0x460a45){var _0x6fef47=_0x460a45?'c':_0xac91('0xe');if(!_0x2a0dc9){_0x2a0dc9=['id','firstName',_0xac91('0x15'),_0xac91('0x16'),_0xac91('0x17'),_0xac91('0x18'),_0xac91('0x19'),'ListId',_0xac91('0x1a'),'createdAt',_0xac91('0x1b'),_0xac91('0x1c'),_0xac91('0x1d')];}else if(!_0x2a0dc9[_0xac91('0x1e')]('id')){_0x2a0dc9[_0xac91('0x1f')]('id');}var _0x4d375e=_0x16c46e&&_0x460a45||!_0x16c46e&&!_0x460a45;if(_0x581e1c){_0x4d375e=_0x581e1c&&_0x460a45||!_0x581e1c&&!_0x460a45;}_0x2a0dc9[_0xac91('0x20')](function(_0x14fbc9){if(_0x14fbc9===_0xac91('0x1d')){if(_0x4d375e){_0x361704[_0xac91('0x21')]('GROUP_CONCAT(DISTINCT\x20t.id)',_0x14fbc9);}else if(_0x16c46e||_0x581e1c){_0x361704['field'](_0x6fef47+'.'+_0x14fbc9);}}else{_0x361704[_0xac91('0x21')](_0x6fef47+'.'+_0x14fbc9);}});return _0x361704;}function getSqlClauses(){var _0x23345b={'default':squel[_0xac91('0x22')](),'fields':squel[_0xac91('0x22')](),'filter':squel[_0xac91('0x22')](),'tags':{'clause':squel[_0xac91('0x22')](),'ids':null},'user':{'clause':squel['expr'](),'skip':![]}};return _0x23345b;}function parseTagParameter(_0x31d212){var _0x57a9bf=squel[_0xac91('0x22')]();if(_0x31d212){var _0x422c88=utils[_0xac91('0x23')](_0x31d212);_0x422c88['forEach'](function(_0x39a4ac){_0x57a9bf['or'](_0xac91('0x24'),'%,'+_0x39a4ac+',%');});}return _0x57a9bf;}function parseSearchFilter(_0x2aafb3){var _0x4ce760=squel[_0xac91('0x22')]();if(_0x2aafb3){var _0x2b081c=_0x2aafb3[_0xac91('0x25')]('\x5c','\x5c\x5c')['replace'](/'/g,'\x27\x27');if(_0x2b081c[_0xac91('0x26')]('\x20')[_0xac91('0x27')]>0x1){_0x4ce760['or']('MATCH\x20(c.firstName,\x20c.lastName,\x20c.phone,\x20c.mobile,\x20c.fax,\x20c.email)\x20AGAINST\x20(?\x20IN\x20BOOLEAN\x20MODE)',qs['getFullTextValue'](_0x2b081c,!![]),null);}else{if(qs[_0xac91('0x28')](_0x2b081c)){_0x4ce760['or'](_0xac91('0x29'),_0x2b081c+'%');_0x4ce760['or'](_0xac91('0x2a'),_0x2b081c+'%');_0x4ce760['or'](_0xac91('0x2b'),_0x2b081c+'%');_0x4ce760['or'](_0xac91('0x2c'),_0x2b081c+'%');}else if(qs[_0xac91('0x2d')](_0x2b081c)){_0x4ce760['or'](_0xac91('0x2e'),_0x2b081c+'%');}else{_0x4ce760['or'](_0xac91('0x2f'),'%'+_0x2b081c+'%');_0x4ce760['or'](_0xac91('0x30'),'%'+_0x2b081c+'%');_0x4ce760['or']('c.email\x20LIKE\x20?','%'+_0x2b081c+'%');}}}return _0x4ce760;}function parseAdvancedSearchFilter(_0x51bb4f,_0x151a19){var _0x581011=getSqlClauses();var _0x3ec19e=as[_0xac91('0x31')](_0x51bb4f);var _0x301e5a=_0x3ec19e[_0xac91('0x32')];for(var _0x226411=0x0;_0x226411<_0x3ec19e['conditions'][_0xac91('0x27')];_0x226411++){var _0x29355e=_0x3ec19e[_0xac91('0x33')][_0x226411];var _0x58e61e='c';var _0x145832=_[_0xac91('0x34')](_0x151a19,[_0xac91('0x35'),_0x29355e[_0xac91('0x21')]]);if(_0x145832){if(_0x29355e[_0xac91('0x21')]==='dateOfBirth'||_0x145832[_0xac91('0x36')]===_0xac91('0x37')){_0x29355e[_0xac91('0x38')]=_0xac91('0x39');}var _0xff49fa=as[_0xac91('0x3a')](_0x58e61e,_0x145832[_0xac91('0x3b')],_0x29355e);_0x581011[_0xac91('0x3c')][_0x301e5a](_0xff49fa['text'],_0xff49fa[_0xac91('0x3d')][_0xac91('0x3e')],_0xff49fa[_0xac91('0x3d')]['end']);}else{if(_0x29355e[_0xac91('0x21')]===_0xac91('0x3f')){var _0x10053e=_0x29355e['value']['split'](',')[_0xac91('0x40')](function(_0x43d218){return Number(_0x43d218);});var _0x46d286=parseTagParameter(_0x10053e);_0x581011['tags'][_0xac91('0x41')][_0xac91('0x42')](_0x46d286);_0x581011[_0xac91('0x43')]['ids']=_0x10053e;}else if(_0x29355e[_0xac91('0x21')]===_0xac91('0x44')){_0x581011[_0xac91('0x45')][_0xac91('0x41')]['and'](_0xac91('0x46'),_0x29355e[_0xac91('0x3d')]);_0x581011['user'][_0xac91('0x47')]=!![];}}}return _0x581011;}function parseModelFields(_0x784d67,_0x4bea33){var _0x6a6ad1=squel['expr']();var _0x5679ad=_(_0x784d67)[_0xac91('0x48')]()[_0xac91('0x40')](function(_0x4d5d86){return _[_0xac91('0x49')](_0x4bea33,[_0xac91('0x35'),_0x4d5d86])?_0x4d5d86:undefined;})[_0xac91('0x4a')]()[_0xac91('0x3d')]();if(_0x784d67[_0xac91('0x4b')]){_0x5679ad[_0xac91('0x1f')](_0xac91('0x4b'));}if(_0x784d67[_0xac91('0x44')]){_0x5679ad[_0xac91('0x1f')]('User');}if(_0x5679ad['length']>0x0){_0x5679ad[_0xac91('0x20')](function(_0x5215ab){if(_0x5215ab===_0xac91('0x7')){_0x6a6ad1[_0xac91('0x42')](_0xac91('0x4c'),[]['concat'](_0x784d67[_0x5215ab]));}else if(_0x5215ab===_0xac91('0x4b')){var _0xf22630='%'+_0x784d67[_0x5215ab]+'%';_0x6a6ad1[_0xac91('0x42')](_0xac91('0x4d'),_0xf22630);}else if(_0x5215ab===_0xac91('0x44')){_0x6a6ad1[_0xac91('0x42')](_0xac91('0x46'),_0x784d67[_0x5215ab]);}else{_0x6a6ad1['and']('c.'+_0x5215ab+'\x20=\x20?',_0x784d67[_0x5215ab]);}});}return _0x6a6ad1;}function parseQueryParameters(_0x368365,_0x28cfb5){var _0x4655e1=getSqlClauses();return Promise['resolve']()['then'](function(){return parseModelFields(_0x368365,_0x28cfb5);})['then'](function(_0x2992ac){_0x4655e1[_0xac91('0x4e')][_0xac91('0x42')](_0x2992ac);return parseTagParameter(_0x368365[_0xac91('0x4f')]);})[_0xac91('0x50')](function(_0x4397c5){_0x4655e1[_0xac91('0x43')][_0xac91('0x41')]['and'](_0x4397c5);return parseSearchFilter(_0x368365[_0xac91('0x51')]);})[_0xac91('0x50')](function(_0xa17896){_0x4655e1['filter']=_0xa17896;return _0x4655e1;});}function checkListsRestrictions(_0x5b3958,_0x2d5b2f,_0x479007){var _0x162b6f=squel['expr']();return Promise['resolve']()[_0xac91('0x50')](function(){if(_0x2d5b2f['search']&&_0x5b3958[_0xac91('0x52')]==='agent'&&!_0x479007){return _0x5b3958['getLists']({'attributes':['id'],'raw':!![]});}})[_0xac91('0x50')](function(_0x4f1616){if(_0x4f1616){if(_0x4f1616[_0xac91('0x27')]===0x0){_0x162b6f[_0xac91('0x42')]('c.ListId\x20IS\x20NULL');}else{_0x162b6f[_0xac91('0x42')](_0xac91('0x4c'),_['map'](_0x4f1616,'id'));}}})['then'](function(){return _0x162b6f;});}function buildContactsQuery(_0x322603,_0x33f3dd,_0x119008,_0xbee3d,_0x5cbc67,_0x153d96){if(_0x33f3dd){_0x33f3dd=_0x119008[_0xac91('0x53')];}_0x322603=addFieldsToQuery(_0x322603,_0x33f3dd,_0xbee3d,_0x153d96,!![]);var _0xc8a873=squel[_0xac91('0x9')]();_0xc8a873=addFieldsToQuery(_0xc8a873,_0x33f3dd,_0xbee3d,_0x153d96,![]);if(_0xbee3d||_0x153d96){_0x322603=applyJoinClauses(_0x322603,!![]);_0x322603['group'](_0xac91('0x54'));if(_0x119008[_0xac91('0x55')]){_0xc8a873['limit'](_0x119008[_0xac91('0x55')]);}if(_0x119008[_0xac91('0x56')]){_0xc8a873['offset'](_0x119008[_0xac91('0x56')]);}}else{_0xc8a873=applyJoinClauses(_0xc8a873,![]);_0xc8a873[_0xac91('0x57')](_0xac91('0x58'));if(_0x119008[_0xac91('0x55')]){_0x322603['limit'](_0x119008[_0xac91('0x55')]);}if(_0x119008['offset']){_0x322603[_0xac91('0x56')](_0x119008[_0xac91('0x56')]);}}_0xc8a873['having'](_0x5cbc67);if(_0x119008[_0xac91('0x59')]){_0x119008['order'][_0xac91('0x20')](function(_0x5b35e6){_0x322603[_0xac91('0x59')]('c.'+_0x5b35e6[0x0],_0x5b35e6[0x1]===_0xac91('0x5a')?![]:!![]);_0xc8a873[_0xac91('0x59')](_0xac91('0x5b')+_0x5b35e6[0x0],_0x5b35e6[0x1]===_0xac91('0x5a')?![]:!![]);});}_0xc8a873[_0xac91('0xa')](_0x322603,'subqry');return _0xc8a873;}function getFilteredContacts(_0x33710f){var _0x560db0=[];return db[_0xac91('0x3f')][_0xac91('0x5c')]({'attributes':['id',_0xac91('0x35'),_0xac91('0x5d')],'raw':!![]})[_0xac91('0x50')](function(_0x505b77){_0x560db0=_[_0xac91('0x5e')](_0x505b77,'id');var _0x14060b={'type':db[_0xac91('0x5f')]['QueryTypes'][_0xac91('0x60')],'model':db[_0xac91('0x61')],'mapToModel':!![],'nest':!![],'raw':!![]};return db[_0xac91('0x62')][_0xac91('0x63')](_0x33710f['toString'](),_0x14060b);})[_0xac91('0x50')](function(_0x34c4ee){_0x34c4ee[_0xac91('0x20')](function(_0xc49b5d){if(_0xc49b5d[_0xac91('0x1d')]){_0xc49b5d[_0xac91('0x64')]=[];_0xc49b5d[_0xac91('0x1d')]['split'](',')[_0xac91('0x20')](function(_0x246d9e){_0xc49b5d[_0xac91('0x64')][_0xac91('0x1f')](_0x560db0[_0x246d9e]);});}delete _0xc49b5d[_0xac91('0x1d')];});return _0x34c4ee;});}function countFilteredContacts(_0x582904,_0x5b04e4){var _0xefd89=_0x582904[_0xac91('0x65')]();_0xefd89[_0xac91('0x21')](_0xac91('0x66'));_0xefd89=applyJoinClauses(_0xefd89,!![]);_0x582904['group'](_0xac91('0x54'));var _0x3d63fb=utils[_0xac91('0x23')](_0x5b04e4);if(!_[_0xac91('0x67')](_0x3d63fb)){_0xefd89[_0xac91('0xc')]('t.id\x20IN\x20?',_0x3d63fb);}var _0x33e4f1={'type':db[_0xac91('0x5f')][_0xac91('0x68')][_0xac91('0x60')],'raw':!![]};return db[_0xac91('0x62')][_0xac91('0x63')](_0xefd89['toString'](),_0x33e4f1)[_0xac91('0x50')](function(_0x44c8b0){return _0x44c8b0[0x0][_0xac91('0x66')];});}module[_0xac91('0x69')]={'buildContactsQuery':buildContactsQuery,'checkListsRestrictions':checkListsRestrictions,'countFilteredContacts':countFilteredContacts,'getContactsSubquery':getContactsSubquery,'getFilteredContacts':getFilteredContacts,'parseAdvancedSearchFilter':parseAdvancedSearchFilter,'parseQueryParameters':parseQueryParameters,'skipContactsQuery':skipContactsQuery};