(1228911,'989','9897289','MI'),(1228912,'989','9897290','MI'),(1228913,'989','9897291','MI'),(1228914,'989','9897292','MI'),(1228915,'989','9897293','MI'),(1228916,'989','9897294','MI'),(1228917,'989','9897295','MI'),(1228918,'989','9897296','MI'),(1228919,'989','9897297','MI'),(1228920,'989','9897298','MI'),(1228921,'989','9897299','MI'),(1228922,'989','9897310','MI'),(1228923,'989','9897311','MI'),(1228924,'989','9897312','MI'),(1228925,'989','9897313','MI'),(1228926,'989','9897314','MI'),(1228927,'989','9897315','MI'),(1228928,'989','9897316','MI'),(1228929,'989','9897317','MI'),(1228930,'989','9897318','MI'),(1228931,'989','9897319','MI'),(1228932,'989','9897320','MI'),(1228933,'989','9897321','MI'),(1228934,'989','9897322','MI'),(1228935,'989','9897323','MI'),(1228936,'989','9897324','MI'),(1228937,'989','9897325','MI'),(1228938,'989','9897326','MI'),(1228939,'989','9897327','MI'),(1228940,'989','9897328','MI'),(1228941,'989','9897329','MI'),(1228942,'989','9897331','MI'),(1228943,'989','9897332','MI'),(1228944,'989','9897333','MI'),(1228945,'989','9897334','MI'),(1228946,'989','9897335','MI'),(1228947,'989','9897336','MI'),(1228948,'989','9897337','MI'),(1228949,'989','9897338','MI'),(1228950,'989','9897339','MI'),(1228951,'989','9897340','MI'),(1228952,'989','9897341','MI'),(1228953,'989','9897342','MI'),(1228954,'989','9897343','MI'),(1228955,'989','9897344','MI'),(1228956,'989','9897345','MI'),(1228957,'989','9897347','MI'),(1228958,'989','9897348','MI'),(1228959,'989','9897349','MI'),(1228960,'989','9897350','MI'),(1228961,'989','9897352','MI'),(1228962,'989','9897353','MI'),(1228963,'989','9897354','MI'),(1228964,'989','9897355','MI'),(1228965,'989','9897356','MI'),(1228966,'989','9897361','MI'),(1228967,'989','9897362','MI'),(1228968,'989','9897363','MI'),(1228969,'989','9897366','MI'),(1228970,'989','9897367','MI'),(1228971,'989','9897368','MI'),(1228972,'989','9897369','MI'),(1228973,'989','9897380','MI'),(1228974,'989','9897385','MI'),(1228975,'989','9897386','MI'),(1228976,'989','9897387','MI'),(1228977,'989','9897388','MI'),(1228978,'989','9897390','MI'),(1228979,'989','9897391','MI'),(1228980,'989','9897392','MI'),(1228981,'989','9897393','MI'),(1228982,'989','9897394','MI'),(1228983,'989','9897395','MI'),(1228984,'989','9897396','MI'),(1228985,'989','9897397','MI'),(1228986,'989','9897398','MI'),(1228987,'989','9897399','MI'),(1228988,'989','9897412','MI'),(1228989,'989','9897417','MI'),(1228990,'989','9897418','MI'),(1228991,'989','9897419','MI'),(1228992,'989','9897420','MI'),(1228993,'989','9897422','MI'),(1228994,'989','9897423','MI'),(1228995,'989','9897424','MI'),(1228996,'989','9897426','MI'),(1228997,'989','9897427','MI'),(1228998,'989','9897428','MI'),(1228999,'989','9897431','MI'),(1229000,'989','9897432','MI'),(1229001,'989','9897433','MI'),(1229002,'989','9897434','MI'),(1229003,'989','9897435','MI'),(1229004,'989','9897436','MI'),(1229005,'989','9897438','MI'),(1229006,'989','9897439','MI'),(1229007,'989','9897450','MI'),(1229008,'989','9897456','MI'),(1229009,'989','9897457','MI'),(1229010,'989','9897459','MI'),(1229011,'989','9897460','MI'),(1229012,'989','9897469','MI'),(1229013,'989','9897470','MI'),(1229014,'989','9897471','MI'),(1229015,'989','9897472','MI'),(1229016,'989','9897474','MI'),(1229017,'989','9897475','MI'),(1229018,'989','9897476','MI'),(1229019,'989','9897478','MI'),(1229020,'989','9897479','MI'),(1229021,'989','9897480','MI'),(1229022,'989','9897481','MI'),(1229023,'989','9897482','MI'),(1229024,'989','9897484','MI'),(1229025,'989','9897485','MI'),(1229026,'989','9897487','MI'),(1229027,'989','9897488','MI'),(1229028,'989','9897489','MI'),(1229029,'989','9897490','MI'),(1229030,'989','9897500','MI'),(1229031,'989','9897501','MI'),(1229032,'989','9897509','MI'),(1229033,'989','9897520','MI'),(1229034,'989','9897521','MI'),(1229035,'989','9897522','MI'),(1229036,'989','9897523','MI'),(1229037,'989','9897524','MI'),(1229038,'989','9897525','MI'),(1229039,'989','9897526','MI'),(1229040,'989','9897527','MI'),(1229041,'989','9897528','MI'),(1229042,'989','9897529','MI'),(1229043,'989','9897530','MI'),(1229044,'989','9897531','MI'),(1229045,'989','9897532','MI'),(1229046,'989','9897533','MI'),(1229047,'989','9897534','MI'),(1229048,'989','9897535','MI'),(1229049,'989','9897536','MI'),(1229050,'989','9897537','MI'),(1229051,'989','9897538','MI'),(1229052,'989','9897539','MI'),(1229053,'989','9897540','MI'),(1229054,'989','9897541','MI'),(1229055,'989','9897542','MI'),(1229056,'989','9897543','MI'),(1229057,'989','9897544','MI'),(1229058,'989','9897545','MI'),(1229059,'989','9897546','MI'),(1229060,'989','9897547','MI'),(1229061,'989','9897548','MI'),(1229062,'989','9897549','MI'),(1229063,'989','9897550','MI'),(1229064,'989','9897551','MI'),(1229065,'989','9897552','MI'),(1229066,'989','9897553','MI'),(1229067,'989','9897554','MI'),(1229068,'989','9897555','MI'),(1229069,'989','9897556','MI'),(1229070,'989','9897557','MI'),(1229071,'989','9897558','MI'),(1229072,'989','9897559','MI'),(1229073,'989','9897560','MI'),(1229074,'989','9897561','MI'),(1229075,'989','9897562','MI'),(1229076,'989','9897563','MI'),(1229077,'989','9897564','MI'),(1229078,'989','9897565','MI'),(1229079,'989','9897566','MI'),(1229080,'989','9897570','MI'),(1229081,'989','9897571','MI'),(1229082,'989','9897572','MI'),(1229083,'989','9897573','MI'),(1229084,'989','9897574','MI'),(1229085,'989','9897575','MI'),(1229086,'989','9897576','MI'),(1229087,'989','9897579','MI'),(1229088,'989','9897580','MI'),(1229089,'989','9897581','MI'),(1229090,'989','9897582','MI'),(1229091,'989','9897583','MI'),(1229092,'989','9897584','MI'),(1229093,'989','9897585','MI'),(1229094,'989','9897586','MI'),(1229095,'989','9897587','MI'),(1229096,'989','9897589','MI'),(1229097,'989','9897590','MI'),(1229098,'989','9897591','MI'),(1229099,'989','9897592','MI'),(1229100,'989','9897593','MI'),(1229101,'989','9897594','MI'),(1229102,'989','9897595','MI'),(1229103,'989','9897596','MI'),(1229104,'989','9897597','MI'),(1229105,'989','9897598','MI'),(1229106,'989','9897599','MI'),(1229107,'989','9897600','MI'),(1229108,'989','9897612','MI'),(1229109,'989','9897617','MI'),(1229110,'989','9897621','MI'),(1229111,'989','9897622','MI'),(1229112,'989','9897623','MI'),(1229113,'989','9897624','MI'),(1229114,'989','9897625','MI'),(1229115,'989','9897626','MI'),(1229116,'989','9897627','MI'),(1229117,'989','9897640','MI'),(1229118,'989','9897641','MI'),(1229119,'989','9897642','MI'),(1229120,'989','9897643','MI'),(1229121,'989','9897644','MI'),(1229122,'989','9897645','MI'),(1229123,'989','9897646','MI'),(1229124,'989','9897647','MI'),(1229125,'989','9897648','MI'),(1229126,'989','9897649','MI'),(1229127,'989','9897662','MI'),(1229128,'989','9897666','MI'),(1229129,'989','9897668','MI'),(1229130,'989','9897684','MI'),(1229131,'989','9897689','MI'),(1229132,'989','9897694','MI'),(1229133,'989','9897704','MI'),(1229134,'989','9897705','MI'),(1229135,'989','9897709','MI'),(1229136,'989','9897710','MI'),(1229137,'989','9897711','MI'),(1229138,'989','9897712','MI'),(1229139,'989','9897713','MI'),(1229140,'989','9897714','MI'),(1229141,'989','9897715','MI'),(1229142,'989','9897716','MI'),(1229143,'989','9897717','MI'),(1229144,'989','9897718','MI'),(1229145,'989','9897719','MI'),(1229146,'989','9897720','MI'),(1229147,'989','9897721','MI'),(1229148,'989','9897722','MI'),(1229149,'989','9897723','MI'),(1229150,'989','9897724','MI'),(1229151,'989','9897725','MI'),(1229152,'989','9897726','MI'),(1229153,'989','9897727','MI'),(1229154,'989','9897728','MI'),(1229155,'989','9897729','MI'),(1229156,'989','9897730','MI'),(1229157,'989','9897731','MI'),(1229158,'989','9897732','MI'),(1229159,'989','9897733','MI'),(1229160,'989','9897734','MI'),(1229161,'989','9897735','MI'),(1229162,'989','9897736','MI'),(1229163,'989','9897737','MI'),(1229164,'989','9897738','MI'),(1229165,'989','9897739','MI'),(1229166,'989','9897740','MI'),(1229167,'989','9897741','MI'),(1229168,'989','9897742','MI'),(1229169,'989','9897743','MI'),(1229170,'989','9897744','MI'),(1229171,'989','9897745','MI'),(1229172,'989','9897746','MI'),(1229173,'989','9897747','MI'),(1229174,'989','9897748','MI'),(1229175,'989','9897749','MI'),(1229176,'989','9897750','MI'),(1229177,'989','9897751','MI'),(1229178,'989','9897752','MI'),(1229179,'989','9897753','MI'),(1229180,'989','9897754','MI'),(1229181,'989','9897755','MI'),(1229182,'989','9897756','MI'),(1229183,'989','9897757','MI'),(1229184,'989','9897758','MI'),(1229185,'989','9897759','MI'),(1229186,'989','9897760','MI'),(1229187,'989','9897761','MI'),(1229188,'989','9897762','MI'),(1229189,'989','9897764','MI'),(1229190,'989','9897765','MI'),(1229191,'989','9897766','MI'),(1229192,'989','9897767','MI'),(1229193,'989','9897768','MI'),(1229194,'989','9897769','MI'),(1229195,'989','9897770','MI'),(1229196,'989','9897771','MI'),(1229197,'989','9897772','MI'),(1229198,'989','9897773','MI'),(1229199,'989','9897774','MI'),(1229200,'989','9897775','MI'),(1229201,'989','9897776','MI'),(1229202,'989','9897777','MI'),(1229203,'989','9897778','MI'),(1229204,'989','9897779','MI'),(1229205,'989','9897781','MI'),(1229206,'989','9897782','MI'),(1229207,'989','9897783','MI'),(1229208,'989','9897785','MI'),(1229209,'989','9897790','MI'),(1229210,'989','9897791','MI'),(1229211,'989','9897792','MI'),(1229212,'989','9897793','MI'),(1229213,'989','9897794','MI'),(1229214,'989','9897795','MI'),(1229215,'989','9897796','MI'),(1229216,'989','9897797','MI'),(1229217,'989','9897798','MI'),(1229218,'989','9897799','MI'),(1229219,'989','9897810','MI'),(1229220,'989','9897811','MI'),(1229221,'989','9897812','MI'),(1229222,'989','9897813','MI'),(1229223,'989','9897814','MI'),(1229224,'989','9897815','MI'),(1229225,'989','9897816','MI'),(1229226,'989','9897817','MI'),(1229227,'989','9897818','MI'),(1229228,'989','9897819','MI'),(1229229,'989','9897820','MI'),(1229230,'989','9897821','MI'),(1229231,'989','9897822','MI'),(1229232,'989','9897823','MI'),(1229233,'989','9897824','MI'),(1229234,'989','9897825','MI'),(1229235,'989','9897826','MI'),(1229236,'989','9897827','MI'),(1229237,'989','9897828','MI'),(1229238,'989','9897829','MI'),(1229239,'989','9897830','MI'),(1229240,'989','9897833','MI'),(1229241,'989','9897837','MI'),(1229242,'989','9897839','MI'),(1229243,'989','9897850','MI'),(1229244,'989','9897851','MI'),(1229245,'989','9897852','MI'),(1229246,'989','9897853','MI'),(1229247,'989','9897854','MI'),(1229248,'989','9897855','MI'),(1229249,'989','9897856','MI'),(1229250,'989','9897857','MI'),(1229251,'989','9897858','MI'),(1229252,'989','9897859','MI'),(1229253,'989','9897860','MI'),(1229254,'989','9897861','MI'),(1229255,'989','9897862','MI'),(1229256,'989','9897863','MI'),(1229257,'989','9897864','MI'),(1229258,'989','9897865','MI'),(1229259,'989','9897866','MI'),(1229260,'989','9897867','MI'),(1229261,'989','9897868','MI'),(1229262,'989','9897869','MI'),(1229263,'989','9897870','MI'),(1229264,'989','9897871','MI'),(1229265,'989','9897872','MI'),(1229266,'989','9897873','MI'),(1229267,'989','9897875','MI'),(1229268,'989','9897876','MI'),(1229269,'989','9897890','MI'),(1229270,'989','9897891','MI'),(1229271,'989','9897897','MI'),(1229272,'989','9897900','MI'),(1229273,'989','9897901','MI'),(1229274,'989','9897902','MI'),(1229275,'989','9897903','MI'),(1229276,'989','9897904','MI'),(1229277,'989','9897905','MI'),(1229278,'989','9897906','MI'),(1229279,'989','9897907','MI'),(1229280,'989','9897908','MI'),(1229281,'989','9897909','MI'),(1229282,'989','9897910','MI'),(1229283,'989','9897911','MI'),(1229284,'989','9897912','MI'),(1229285,'989','9897913','MI'),(1229286,'989','9897914','MI'),(1229287,'989','9897915','MI'),(1229288,'989','9897916','MI'),(1229289,'989','9897917','MI'),(1229290,'989','9897918','MI'),(1229291,'989','9897919','MI'),(1229292,'989','9897920','MI'),(1229293,'989','9897921','MI'),(1229294,'989','9897922','MI'),(1229295,'989','9897923','MI'),(1229296,'989','9897924','MI'),(1229297,'989','9897925','MI'),(1229298,'989','9897926','MI'),(1229299,'989','9897927','MI'),(1229300,'989','9897928','MI'),(1229301,'989','9897929','MI'),(1229302,'989','9897930','MI'),(1229303,'989','9897931','MI'),(1229304,'989','9897932','MI'),(1229305,'989','9897933','MI'),(1229306,'989','9897934','MI'),(1229307,'989','9897935','MI'),(1229308,'989','9897936','MI'),(1229309,'989','9897937','MI'),(1229310,'989','9897938','MI'),(1229311,'989','9897939','MI'),(1229312,'989','9897940','MI'),(1229313,'989','9897941','MI'),(1229314,'989','9897942','MI'),(1229315,'989','9897943','MI'),(1229316,'989','9897944','MI'),(1229317,'989','9897945','MI'),(1229318,'989','9897946','MI'),(1229319,'989','9897947','MI'),(1229320,'989','9897948','MI'),(1229321,'989','9897949','MI'),(1229322,'989','9897951','MI'),(1229323,'989','9897952','MI'),(1229324,'989','9897953','MI'),(1229325,'989','9897955','MI'),(1229326,'989','9897959','MI'),(1229327,'989','9897964','MI'),(1229328,'989','9897970','MI'),(1229329,'989','9897971','MI'),(1229330,'989','9897972','MI'),(1229331,'989','9897973','MI'),(1229332,'989','9897974','MI'),(1229333,'989','9897975','MI'),(1229334,'989','9897976','MI'),(1229335,'989','9897977','MI'),(1229336,'989','9897978','MI'),(1229337,'989','9897979','MI'),(1229338,'989','9897990','MI'),(1229339,'989','9897991','MI'),(1229340,'989','9897992','MI'),(1229341,'989','9897993','MI'),(1229342,'989','9897994','MI'),(1229343,'989','9897995','MI'),(1229344,'989','9897996','MI'),(1229345,'989','9897997','MI'),(1229346,'989','9897998','MI'),(1229347,'989','9897999','MI'),(1229348,'989','9898000','MI'),(1229349,'989','9898001','MI'),(1229350,'989','9898008','MI'),(1229351,'989','9898020','MI'),(1229352,'989','9898025','MI'),(1229353,'989','9898028','MI'),(1229354,'989','9898030','MI'),(1229355,'989','9898031','MI'),(1229356,'989','9898032','MI'),(1229357,'989','9898033','MI'),(1229358,'989','9898034','MI'),(1229359,'989','9898035','MI'),(1229360,'989','9898036','MI'),(1229361,'989','9898037','MI'),(1229362,'989','9898038','MI'),(1229363,'989','9898039','MI'),(1229364,'989','9898040','MI'),(1229365,'989','9898041','MI'),(1229366,'989','9898042','MI'),(1229367,'989','9898044','MI'),(1229368,'989','9898045','MI'),(1229369,'989','9898047','MI'),(1229370,'989','9898049','MI'),(1229371,'989','9898060','MI'),(1229372,'989','9898061','MI'),(1229373,'989','9898062','MI'),(1229374,'989','9898063','MI'),(1229375,'989','9898064','MI'),(1229376,'989','9898065','MI'),(1229377,'989','9898066','MI'),(1229378,'989','9898067','MI'),(1229379,'989','9898068','MI'),(1229380,'989','9898069','MI'),(1229381,'989','9898071','MI'),(1229382,'989','9898077','MI'),(1229383,'989','9898078','MI'),(1229384,'989','9898079','MI'),(1229385,'989','9898080','MI'),(1229386,'989','9898110','MI'),(1229387,'989','9898111','MI'),(1229388,'989','9898112','MI'),(1229389,'989','9898113','MI'),(1229390,'989','9898114','MI'),(1229391,'989','9898115','MI'),(1229392,'989','9898116','MI'),(1229393,'989','9898117','MI'),(1229394,'989','9898118','MI'),(1229395,'989','9898119','MI'),(1229396,'989','9898130','MI'),(1229397,'989','9898146','MI'),(1229398,'989','9898147','MI'),(1229399,'989','9898162','MI'),(1229400,'989','9898164','MI'),(1229401,'989','9898170','MI'),(1229402,'989','9898174','MI'),(1229403,'989','9898179','MI'),(1229404,'989','9898183','MI'),(1229405,'989','9898184','MI'),(1229406,'989','9898185','MI'),(1229407,'989','9898187','MI'),(1229408,'989','9898197','MI'),(1229409,'989','9898203','MI'),(1229410,'989','9898210','MI'),(1229411,'989','9898211','MI'),(1229412,'989','9898212','MI'),(1229413,'989','9898213','MI'),(1229414,'989','9898214','MI'),(1229415,'989','9898215','MI'),(1229416,'989','9898216','MI'),(1229417,'989','9898217','MI'),(1229418,'989','9898218','MI'),(1229419,'989','9898219','MI'),(1229420,'989','9898222','MI'),(1229421,'989','9898227','MI'),(1229422,'989','9898231','MI'),(1229423,'989','9898232','MI'),(1229424,'989','9898233','MI'),(1229425,'989','9898235','MI'),(1229426,'989','9898237','MI'),(1229427,'989','9898238','MI'),(1229428,'989','9898239','MI'),(1229429,'989','9898250','MI'),(1229430,'989','9898251','MI'),(1229431,'989','9898252','MI'),(1229432,'989','9898253','MI'),(1229433,'989','9898254','MI'),(1229434,'989','9898255','MI'),(1229435,'989','9898256','MI'),(1229436,'989','9898257','MI'),(1229437,'989','9898258','MI'),(1229438,'989','9898259','MI'),(1229439,'989','9898260','MI'),(1229440,'989','9898261','MI'),(1229441,'989','9898262','MI'),(1229442,'989','9898263','MI'),(1229443,'989','9898264','MI'),(1229444,'989','9898265','MI'),(1229445,'989','9898266','MI'),(1229446,'989','9898267','MI'),(1229447,'989','9898268','MI'),(1229448,'989','9898269','MI'),(1229449,'989','9898277','MI'),(1229450,'989','9898284','MI'),(1229451,'989','9898285','MI'),(1229452,'989','9898286','MI'),(1229453,'989','9898287','MI'),(1229454,'989','9898300','MI'),(1229455,'989','9898301','MI'),(1229456,'989','9898302','MI'),(1229457,'989','9898303','MI'),(1229458,'989','9898304','MI'),(1229459,'989','9898305','MI'),(1229460,'989','9898306','MI'),(1229461,'989','9898307','MI'),(1229462,'989','9898308','MI'),(1229463,'989','9898309','MI'),(1229464,'989','9898310','MI'),(1229465,'989','9898311','MI'),(1229466,'989','9898312','MI'),(1229467,'989','9898313','MI'),(1229468,'989','9898314','MI'),(1229469,'989','9898315','MI'),(1229470,'989','9898316','MI'),(1229471,'989','9898317','MI'),(1229472,'989','9898318','MI'),(1229473,'989','9898319','MI'),(1229474,'989','9898320','MI'),(1229475,'989','9898321','MI'),(1229476,'989','9898322','MI'),(1229477,'989','9898323','MI'),(1229478,'989','9898324','MI'),(1229479,'989','9898325','MI'),(1229480,'989','9898326','MI'),(1229481,'989','9898327','MI'),(1229482,'989','9898328','MI'),(1229483,'989','9898329','MI'),(1229484,'989','9898330','MI'),(1229485,'989','9898331','MI'),(1229486,'989','9898332','MI'),(1229487,'989','9898333','MI'),(1229488,'989','9898334','MI'),(1229489,'989','9898335','MI'),(1229490,'989','9898336','MI'),(1229491,'989','9898337','MI'),(1229492,'989','9898338','MI'),(1229493,'989','9898339','MI'),(1229494,'989','9898340','MI'),(1229495,'989','9898341','MI'),(1229496,'989','9898342','MI'),(1229497,'989','9898343','MI'),(1229498,'989','9898344','MI'),(1229499,'989','9898345','MI'),(1229500,'989','9898346','MI'),(1229501,'989','9898347','MI'),(1229502,'989','9898349','MI'),(1229503,'989','9898350','MI'),(1229504,'989','9898351','MI'),(1229505,'989','9898352','MI'),(1229506,'989','9898353','MI'),(1229507,'989','9898354','MI'),(1229508,'989','9898355','MI'),(1229509,'989','9898356','MI'),(1229510,'989','9898357','MI'),(1229511,'989','9898358','MI'),(1229512,'989','9898359','MI'),(1229513,'989','9898360','MI'),(1229514,'989','9898362','MI'),(1229515,'989','9898365','MI'),(1229516,'989','9898366','MI'),(1229517,'989','9898369','MI'),(1229518,'989','9898370','MI'),(1229519,'989','9898371','MI'),(1229520,'989','9898372','MI'),(1229521,'989','9898373','MI'),(1229522,'989','9898374','MI'),(1229523,'989','9898375','MI'),(1229524,'989','9898376','MI'),(1229525,'989','9898377','MI'),(1229526,'989','9898378','MI'),(1229527,'989','9898379','MI'),(1229528,'989','9898380','MI'),(1229529,'989','9898381','MI'),(1229530,'989','9898382','MI'),(1229531,'989','9898384','MI'),(1229532,'989','9898385','MI'),(1229533,'989','9898386','MI'),(1229534,'989','9898387','MI'),(1229535,'989','9898390','MI'),(1229536,'989','9898391','MI'),(1229537,'989','9898392','MI'),(1229538,'989','9898393','MI'),(1229539,'989','9898394','MI'),(1229540,'989','9898395','MI'),(1229541,'989','9898396','MI'),(1229542,'989','9898397','MI'),(1229543,'989','9898398','MI'),(1229544,'989','9898399','MI'),(1229545,'989','9898421','MI'),(1229546,'989','9898422','MI'),(1229547,'989','9898423','MI'),(1229548,'989','9898424','MI'),(1229549,'989','9898425','MI'),(1229550,'989','9898426','MI'),(1229551,'989','9898428','MI'),(1229552,'989','9898429','MI'),(1229553,'989','9898430','MI'),(1229554,'989','9898431','MI'),(1229555,'989','9898432','MI'),(1229556,'989','9898433','MI'),(1229557,'989','9898435','MI'),(1229558,'989','9898436','MI'),(1229559,'989','9898437','MI'),(1229560,'989','9898438','MI'),(1229561,'989','9898439','MI'),(1229562,'989','9898447','MI'),(1229563,'989','9898451','MI'),(1229564,'989','9898452','MI'),(1229565,'989','9898453','MI'),(1229566,'989','9898454','MI'),(1229567,'989','9898455','MI'),(1229568,'989','9898456','MI'),(1229569,'989','9898457','MI'),(1229570,'989','9898458','MI'),(1229571,'989','9898460','MI'),(1229572,'989','9898461','MI'),(1229573,'989','9898462','MI'),(1229574,'989','9898463','MI'),(1229575,'989','9898464','MI'),(1229576,'989','9898465','MI'),(1229577,'989','9898466','MI'),(1229578,'989','9898467','MI'),(1229579,'989','9898469','MI'),(1229580,'989','9898470','MI'),(1229581,'989','9898471','MI'),(1229582,'989','9898472','MI'),(1229583,'989','9898473','MI'),(1229584,'989','9898474','MI'),(1229585,'989','9898475','MI'),(1229586,'989','9898476','MI'),(1229587,'989','9898477','MI'),(1229588,'989','9898478','MI'),(1229589,'989','9898479','MI'),(1229590,'989','9898480','MI'),(1229591,'989','9898481','MI'),(1229592,'989','9898482','MI'),(1229593,'989','9898483','MI'),(1229594,'989','9898484','MI'),(1229595,'989','9898485','MI'),(1229596,'989','9898486','MI'),(1229597,'989','9898487','MI'),(1229598,'989','9898488','MI'),(1229599,'989','9898490','MI'),(1229600,'989','9898491','MI'),(1229601,'989','9898492','MI'),(1229602,'989','9898493','MI'),(1229603,'989','9898494','MI'),(1229604,'989','9898495','MI'),(1229605,'989','9898496','MI'),(1229606,'989','9898497','MI'),(1229607,'989','9898498','MI'),(1229608,'989','9898499','MI'),(1229609,'989','9898500','MI'),(1229610,'989','9898501','MI'),(1229611,'989','9898502','MI'),(1229612,'989','9898509','MI'),(1229613,'989','9898530','MI'),(1229614,'989','9898531','MI'),(1229615,'989','9898532','MI'),(1229616,'989','9898537','MI'),(1229617,'989','9898552','MI'),(1229618,'989','9898553','MI'),(1229619,'989','9898557','MI'),(1229620,'989','9898558','MI'),(1229621,'989','9898560','MI'),(1229622,'989','9898561','MI'),(1229623,'989','9898562','MI'),(1229624,'989','9898563','MI'),(1229625,'989','9898564','MI'),(1229626,'989','9898565','MI'),(1229627,'989','9898566','MI'),(1229628,'989','9898567','MI'),(1229629,'989','9898568','MI'),(1229630,'989','9898569','MI'),(1229631,'989','9898577','MI'),(1229632,'989','9898610','MI'),(1229633,'989','9898611','MI'),(1229634,'989','9898612','MI'),(1229635,'989','9898613','MI'),(1229636,'989','9898614','MI'),(1229637,'989','9898615','MI'),(1229638,'989','9898616','MI'),(1229639,'989','9898617','MI'),(1229640,'989','9898618','MI'),(1229641,'989','9898619','MI'),(1229642,'989','9898624','MI'),(1229643,'989','9898625','MI'),(1229644,'989','9898632','MI'),(1229645,'989','9898633','MI'),(1229646,'989','9898634','MI'),(1229647,'989','9898635','MI'),(1229648,'989','9898636','MI'),(1229649,'989','9898637','MI'),(1229650,'989','9898639','MI'),(1229651,'989','9898640','MI'),(1229652,'989','9898641','MI'),(1229653,'989','9898642','MI'),(1229654,'989','9898643','MI'),(1229655,'989','9898644','MI'),(1229656,'989','9898645','MI'),(1229657,'989','9898647','MI'),(1229658,'989','9898648','MI'),(1229659,'989','9898650','MI'),(1229660,'989','9898651','MI'),(1229661,'989','9898652','MI'),(1229662,'989','9898653','MI'),(1229663,'989','9898654','MI'),(1229664,'989','9898655','MI'),(1229665,'989','9898656','MI'),(1229666,'989','9898657','MI'),(1229667,'989','9898658','MI'),(1229668,'989','9898659','MI'),(1229669,'989','9898660','MI'),(1229670,'989','9898661','MI'),(1229671,'989','9898662','MI'),(1229672,'989','9898663','MI'),(1229673,'989','9898666','MI'),(1229674,'989','9898667','MI'),(1229675,'989','9898668','MI'),(1229676,'989','9898669','MI'),(1229677,'989','9898670','MI'),(1229678,'989','9898671','MI'),(1229679,'989','9898672','MI'),(1229680,'989','9898673','MI'),(1229681,'989','9898674','MI'),(1229682,'989','9898675','MI'),(1229683,'989','9898676','MI'),(1229684,'989','9898677','MI'),(1229685,'989','9898678','MI'),(1229686,'989','9898679','MI'),(1229687,'989','9898680','MI'),(1229688,'989','9898683','MI'),(1229689,'989','9898684','MI'),(1229690,'989','9898687','MI'),(1229691,'989','9898688','MI'),(1229692,'989','9898689','MI'),(1229693,'989','9898699','MI'),(1229694,'989','9898700','MI'),(1229695,'989','9898701','MI'),(1229696,'989','9898702','MI'),(1229697,'989','9898703','MI'),(1229698,'989','9898704','MI'),(1229699,'989','9898705','MI'),(1229700,'989','9898706','MI'),(1229701,'989','9898707','MI'),(1229702,'989','9898708','MI'),(1229703,'989','9898709','MI'),(1229704,'989','9898710','MI'),(1229705,'989','9898711','MI'),(1229706,'989','9898712','MI'),(1229707,'989','9898713','MI'),(1229708,'989','9898714','MI'),(1229709,'989','9898715','MI'),(1229710,'989','9898716','MI'),(1229711,'989','9898717','MI'),(1229712,'989','9898719','MI'),(1229713,'989','9898720','MI'),(1229714,'989','9898721','MI'),(1229715,'989','9898722','MI'),(1229716,'989','9898723','MI'),(1229717,'989','9898724','MI'),(1229718,'989','9898725','MI'),(1229719,'989','9898726','MI'),(1229720,'989','9898727','MI'),(1229721,'989','9898728','MI'),(1229722,'989','9898729','MI'),(1229723,'989','9898731','MI'),(1229724,'989','9898732','MI'),(1229725,'989','9898733','MI'),(1229726,'989','9898734','MI'),(1229727,'989','9898735','MI'),(1229728,'989','9898736','MI'),(1229729,'989','9898738','MI'),(1229730,'989','9898739','MI'),(1229731,'989','9898743','MI'),(1229732,'989','9898744','MI'),(1229733,'989','9898745','MI'),(1229734,'989','9898746','MI'),(1229735,'989','9898750','MI'),(1229736,'989','9898751','MI'),(1229737,'989','9898752','MI'),(1229738,'989','9898753','MI'),(1229739,'989','9898754','MI'),(1229740,'989','9898755','MI'),(1229741,'989','9898756','MI'),(1229742,'989','9898757','MI'),(1229743,'989','9898758','MI'),(1229744,'989','9898760','MI'),(1229745,'989','9898762','MI'),(1229746,'989','9898764','MI'),(1229747,'989','9898766','MI'),(1229748,'989','9898767','MI'),(1229749,'989','9898768','MI'),(1229750,'989','9898784','MI'),(1229751,'989','9898791','MI'),(1229752,'989','9898792','MI'),(1229753,'989','9898793','MI'),(1229754,'989','9898794','MI'),(1229755,'989','9898795','MI'),(1229756,'989','9898796','MI'),(1229757,'989','9898797','MI'),(1229758,'989','9898798','MI'),(1229759,'989','9898800','MI'),(1229760,'989','9898820','MI'),(1229761,'989','9898821','MI'),(1229762,'989','9898824','MI'),(1229763,'989','9898828','MI'),(1229764,'989','9898829','MI'),(1229765,'989','9898830','MI'),(1229766,'989','9898832','MI'),(1229767,'989','9898833','MI'),(1229768,'989','9898834','MI'),(1229769,'989','9898837','MI'),(1229770,'989','9898838','MI'),(1229771,'989','9898839','MI'),(1229772,'989','9898840','MI'),(1229773,'989','9898848','MI'),(1229774,'989','9898850','MI'),(1229775,'989','9898851','MI'),(1229776,'989','9898852','MI'),(1229777,'989','9898853','MI'),(1229778,'989','9898854','MI'),(1229779,'989','9898855','MI'),(1229780,'989','9898856','MI'),(1229781,'989','9898857','MI'),(1229782,'989','9898858','MI'),(1229783,'989','9898859','MI'),(1229784,'989','9898872','MI'),(1229785,'989','9898880','MI'),(1229786,'989','9898887','MI'),(1229787,'989','9898888','MI'),(1229788,'989','9898889','MI'),(1229789,'989','9898893','MI'),(1229790,'989','9898898','MI'),(1229791,'989','9898903','MI'),(1229792,'989','9898909','MI'),(1229793,'989','9898910','MI'),(1229794,'989','9898911','MI'),(1229795,'989','9898912','MI'),(1229796,'989','9898913','MI'),(1229797,'989','9898914','MI'),(1229798,'989','9898915','MI'),(1229799,'989','9898918','MI'),(1229800,'989','9898919','MI'),(1229801,'989','9898920','MI'),(1229802,'989','9898921','MI'),(1229803,'989','9898922','MI'),(1229804,'989','9898923','MI'),(1229805,'989','9898924','MI'),(1229806,'989','9898925','MI'),(1229807,'989','9898926','MI'),(1229808,'989','9898927','MI'),(1229809,'989','9898928','MI'),(1229810,'989','9898929','MI'),(1229811,'989','9898930','MI'),(1229812,'989','9898931','MI'),(1229813,'989','9898932','MI'),(1229814,'989','9898933','MI'),(1229815,'989','9898934','MI'),(1229816,'989','9898935','MI'),(1229817,'989','9898936','MI'),(1229818,'989','9898937','MI'),(1229819,'989','9898938','MI'),(1229820,'989','9898939','MI'),(1229821,'989','9898940','MI'),(1229822,'989','9898941','MI'),(1229823,'989','9898942','MI'),(1229824,'989','9898943','MI'),(1229825,'989','9898944','MI'),(1229826,'989','9898945','MI'),(1229827,'989','9898946','MI'),(1229828,'989','9898947','MI'),(1229829,'989','9898948','MI'),(1229830,'989','9898949','MI'),(1229831,'989','9898950','MI'),(1229832,'989','9898951','MI'),(1229833,'989','9898952','MI'),(1229834,'989','9898953','MI'),(1229835,'989','9898954','MI'),(1229836,'989','9898955','MI'),(1229837,'989','9898956','MI'),(1229838,'989','9898957','MI'),(1229839,'989','9898958','MI'),(1229840,'989','9898959','MI'),(1229841,'989','9898960','MI'),(1229842,'989','9898961','MI'),(1229843,'989','9898962','MI'),(1229844,'989','9898963','MI'),(1229845,'989','9898964','MI'),(1229846,'989','9898965','MI'),(1229847,'989','9898966','MI'),(1229848,'989','9898967','MI'),(1229849,'989','9898968','MI'),(1229850,'989','9898969','MI'),(1229851,'989','9898980','MI'),(1229852,'989','9898982','MI'),(1229853,'989','9898983','MI'),(1229854,'989','9898984','MI'),(1229855,'989','9898985','MI'),(1229856,'989','9898987','MI'),(1229857,'989','9898989','MI'),(1229858,'989','9899000','MI'),(1229859,'989','9899001','MI'),(1229860,'989','9899037','MI'),(1229861,'989','9899050','MI'),(1229862,'989','9899051','MI'),(1229863,'989','9899052','MI'),(1229864,'989','9899053','MI'),(1229865,'989','9899054','MI'),(1229866,'989','9899055','MI'),(1229867,'989','9899056','MI'),(1229868,'989','9899057','MI'),(1229869,'989','9899058','MI'),(1229870,'989','9899059','MI'),(1229871,'989','9899070','MI'),(1229872,'989','9899072','MI'),(1229873,'989','9899073','MI'),(1229874,'989','9899074','MI'),(1229875,'989','9899075','MI'),(1229876,'989','9899076','MI'),(1229877,'989','9899077','MI'),(1229878,'989','9899078','MI'),(1229879,'989','9899100','MI'),(1229880,'989','9899101','MI'),(1229881,'989','9899102','MI'),(1229882,'989','9899103','MI'),(1229883,'989','9899104','MI'),(1229884,'989','9899105','MI'),(1229885,'989','9899106','MI'),(1229886,'989','9899107','MI'),(1229887,'989','9899108','MI'),(1229888,'989','9899109','MI'),(1229889,'989','9899110','MI'),(1229890,'989','9899111','MI'),(1229891,'989','9899112','MI'),(1229892,'989','9899113','MI'),(1229893,'989','9899114','MI'),(1229894,'989','9899115','MI'),(1229895,'989','9899116','MI'),(1229896,'989','9899117','MI'),(1229897,'989','9899118','MI'),(1229898,'989','9899119','MI'),(1229899,'989','9899121','MI'),(1229900,'989','9899122','MI'),(1229901,'989','9899123','MI'),(1229902,'989','9899127','MI'),(1229903,'989','9899156','MI'),(1229904,'989','9899180','MI'),(1229905,'989','9899181','MI'),(1229906,'989','9899182','MI'),(1229907,'989','9899186','MI'),(1229908,'989','9899190','MI'),(1229909,'989','9899210','MI'),(1229910,'989','9899211','MI'),(1229911,'989','9899212','MI'),(1229912,'989','9899213','MI'),(1229913,'989','9899214','MI'),(1229914,'989','9899215','MI'),(1229915,'989','9899216','MI'),(1229916,'989','9899217','MI'),(1229917,'989','9899218','MI'),(1229918,'989','9899219','MI'),(1229919,'989','9899220','MI'),(1229920,'989','9899221','MI'),(1229921,'989','9899222','MI'),(1229922,'989','9899223','MI'),(1229923,'989','9899224','MI'),(1229924,'989','9899225','MI'),(1229925,'989','9899226','MI'),(1229926,'989','9899227','MI'),(1229927,'989','9899228','MI'),(1229928,'989','9899229','MI'),(1229929,'989','9899230','MI'),(1229930,'989','9899231','MI'),(1229931,'989','9899232','MI'),(1229932,'989','9899233','MI'),(1229933,'989','9899234','MI'),(1229934,'989','9899235','MI'),(1229935,'989','9899236','MI'),(1229936,'989','9899237','MI'),(1229937,'989','9899238','MI'),(1229938,'989','9899239','MI'),(1229939,'989','9899250','MI'),(1229940,'989','9899251','MI'),(1229941,'989','9899252','MI'),(1229942,'989','9899253','MI'),(1229943,'989','9899254','MI'),(1229944,'989','9899255','MI'),(1229945,'989','9899256','MI'),(1229946,'989','9899257','MI'),(1229947,'989','9899258','MI'),(1229948,'989','9899259','MI'),(1229949,'989','9899300','MI'),(1229950,'989','9899301','MI'),(1229951,'989','9899304','MI'),(1229952,'989','9899313','MI'),(1229953,'989','9899320','MI'),(1229954,'989','9899326','MI'),(1229955,'989','9899327','MI'),(1229956,'989','9899344','MI'),(1229957,'989','9899346','MI'),(1229958,'989','9899347','MI'),(1229959,'989','9899354','MI'),(1229960,'989','9899355','MI'),(1229961,'989','9899356','MI'),(1229962,'989','9899357','MI'),(1229963,'989','9899359','MI'),(1229964,'989','9899360','MI'),(1229965,'989','9899361','MI'),(1229966,'989','9899362','MI'),(1229967,'989','9899363','MI'),(1229968,'989','9899364','MI'),(1229969,'989','9899365','MI'),(1229970,'989','9899366','MI'),(1229971,'989','9899367','MI'),(1229972,'989','9899368','MI'),(1229973,'989','9899369','MI'),(1229974,'989','9899380','MI'),(1229975,'989','9899381','MI'),(1229976,'989','9899382','MI'),(1229977,'989','9899383','MI'),(1229978,'989','9899384','MI'),(1229979,'989','9899385','MI'),(1229980,'989','9899386','MI'),(1229981,'989','9899387','MI'),(1229982,'989','9899388','MI'),(1229983,'989','9899389','MI'),(1229984,'989','9899390','MI'),(1229985,'989','9899391','MI'),(1229986,'989','9899392','MI'),(1229987,'989','9899393','MI'),(1229988,'989','9899394','MI'),(1229989,'989','9899395','MI'),(1229990,'989','9899396','MI'),(1229991,'989','9899397','MI'),(1229992,'989','9899398','MI'),(1229993,'989','9899399','MI'),(1229994,'989','9899410','MI'),(1229995,'989','9899413','MI'),(1229996,'989','9899414','MI'),(1229997,'989','9899416','MI'),(1229998,'989','9899433','MI'),(1229999,'989','9899437','MI'),(1230000,'989','9899446','MI'),(1230001,'989','9899450','MI'),(1230002,'989','9899466','MI'),(1230003,'989','9899467','MI'),(1230004,'989','9899490','MI'),(1230005,'989','9899491','MI'),(1230006,'989','9899492','MI'),(1230007,'989','9899493','MI'),(1230008,'989','9899494','MI'),(1230009,'989','9899495','MI'),(1230010,'989','9899496','MI'),(1230011,'989','9899497','MI'),(1230012,'989','9899498','MI'),(1230013,'989','9899499','MI'),(1230014,'989','9899500','MI'),(1230015,'989','9899501','MI'),(1230016,'989','9899502','MI'),(1230017,'989','9899503','MI'),(1230018,'989','9899504','MI'),(1230019,'989','9899505','MI'),(1230020,'989','9899506','MI'),(1230021,'989','9899507','MI'),(1230022,'989','9899508','MI'),(1230023,'989','9899509','MI'),(1230024,'989','9899530','MI'),(1230025,'989','9899531','MI'),(1230026,'989','9899532','MI'),(1230027,'989','9899533','MI'),(1230028,'989','9899534','MI'),(1230029,'989','9899535','MI'),(1230030,'989','9899536','MI'),(1230031,'989','9899537','MI'),(1230032,'989','9899538','MI'),(1230033,'989','9899539','MI'),(1230034,'989','9899560','MI'),(1230035,'989','9899561','MI'),(1230036,'989','9899562','MI'),(1230037,'989','9899563','MI'),(1230038,'989','9899564','MI'),(1230039,'989','9899565','MI'),(1230040,'989','9899566','MI'),(1230041,'989','9899567','MI'),(1230042,'989','9899568','MI'),(1230043,'989','9899569','MI'),(1230044,'989','9899580','MI'),(1230045,'989','9899581','MI'),(1230046,'989','9899582','MI'),(1230047,'989','9899583','MI'),(1230048,'989','9899584','MI'),(1230049,'989','9899585','MI'),(1230050,'989','9899586','MI'),(1230051,'989','9899587','MI'),(1230052,'989','9899588','MI'),(1230053,'989','9899589','MI'),(1230054,'989','9899590','MI'),(1230055,'989','9899591','MI'),(1230056,'989','9899592','MI'),(1230057,'989','9899593','MI'),(1230058,'989','9899594','MI'),(1230059,'989','9899595','MI'),(1230060,'989','9899596','MI'),(1230061,'989','9899597','MI'),(1230062,'989','9899598','MI'),(1230063,'989','9899599','MI'),(1230064,'989','9899620','MI'),(1230065,'989','9899621','MI'),(1230066,'989','9899622','MI'),(1230067,'989','9899623','MI'),(1230068,'989','9899624','MI'),(1230069,'989','9899625','MI'),(1230070,'989','9899626','MI'),(1230071,'989','9899627','MI'),(1230072,'989','9899628','MI'),(1230073,'989','9899629','MI'),(1230074,'989','9899633','MI'),(1230075,'989','9899635','MI'),(1230076,'989','9899640','MI'),(1230077,'989','9899642','MI'),(1230078,'989','9899644','MI'),(1230079,'989','9899645','MI'),(1230080,'989','9899646','MI'),(1230081,'989','9899647','MI'),(1230082,'989','9899660','MI'),(1230083,'989','9899661','MI'),(1230084,'989','9899662','MI'),(1230085,'989','9899663','MI'),(1230086,'989','9899666','MI'),(1230087,'989','9899667','MI'),(1230088,'989','9899672','MI'),(1230089,'989','9899673','MI'),(1230090,'989','9899674','MI'),(1230091,'989','9899676','MI'),(1230092,'989','9899677','MI'),(1230093,'989','9899678','MI'),(1230094,'989','9899680','MI'),(1230095,'989','9899681','MI'),(1230096,'989','9899682','MI'),(1230097,'989','9899684','MI'),(1230098,'989','9899685','MI'),(1230099,'989','9899687','MI'),(1230100,'989','9899689','MI'),(1230101,'989','9899757','MI'),(1230102,'989','9899760','MI'),(1230103,'989','9899761','MI'),(1230104,'989','9899762','MI'),(1230105,'989','9899763','MI'),(1230106,'989','9899764','MI'),(1230107,'989','9899765','MI'),(1230108,'989','9899766','MI'),(1230109,'989','9899767','MI'),(1230110,'989','9899768','MI'),(1230111,'989','9899769','MI'),(1230112,'989','9899811','MI'),(1230113,'989','9899816','MI'),(1230114,'989','9899818','MI'),(1230115,'989','9899832','MI'),(1230116,'989','9899833','MI'),(1230117,'989','9899834','MI'),(1230118,'989','9899839','MI'),(1230119,'989','9899840','MI'),(1230120,'989','9899841','MI'),(1230121,'989','9899842','MI'),(1230122,'989','9899843','MI'),(1230123,'989','9899845','MI'),(1230124,'989','9899846','MI'),(1230125,'989','9899848','MI'),(1230126,'989','9899850','MI'),(1230127,'989','9899950','MI'),(1230128,'989','9899951','MI'),(1230129,'989','9899952','MI'),(1230130,'989','9899953','MI'),(1230131,'989','9899954','MI'),(1230132,'989','9899955','MI'),(1230133,'989','9899956','MI'),(1230134,'989','9899957','MI'),(1230135,'989','9899958','MI'),(1230136,'989','9899959','MI'),(1230137,'989','9899990','MI'),(1230138,'989','9899991','MI'),(1230139,'989','9899996','MI'),(1230140,'989','9899997','MI'),(1230141,'989','9899998','MI'),(1230142,'989','9899999','MI');

--
-- Table structure for table `user_minutes`
--

CREATE TABLE `user_minutes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `iduser` int(11) DEFAULT '-1',
  `rvm` int(6) DEFAULT '0',
  `ib` int(6) DEFAULT '0',
  `ob` int(6) DEFAULT '0',
  `sms` int(6) DEFAULT '0',
  `agent` int(4) DEFAULT '0' COMMENT 'license count',
  `did_credit` int(4) DEFAULT '0' COMMENT 'DID credits',
  PRIMARY KEY (`id`),
  KEY `iduser` (`iduser`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_minutes`
--

INSERT INTO `user_minutes` VALUES (1,11,80000,180000,52499,60000,405,300),(2,2,100,100,100,100,48,5),(3,22,0,0,500,0,500,0),(4,23,340010,1000249999,98707919,250000,202,0),(6,25,40000,20000,30000,0,10,1),(7,26,40000,70000,47872,40000,7,0),(8,31,10000,10000,10000,10000,10000,10000),(9,32,10000,10000,10000,10000,10000,10000),(10,33,10000,10000,10000,10000,10000,10000),(11,NULL,10000,10000,10000,10000,10000,10000),(12,NULL,10000,10000,10000,10000,10000,10000),(13,63,10000,10000,10000,10000,10000,10000),(14,61,10000,10000,10000,10000,10000,10000),(15,62,10000,10000,10000,10000,10000,10000),(16,64,10000,10000,10000,10000,10000,10000),(17,66,10000,10000,10000,10000,10000,10000),(18,69,10000,10000,10000,10000,10000,10000),(19,71,10000,10000,10000,10000,10000,10000),(20,70,10000,10000,10000,10000,10000,10000);

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `idparent` int(11) DEFAULT NULL,
  `username` varchar(110) DEFAULT NULL,
  `password` varchar(110) DEFAULT NULL,
  `type` tinyint(1) DEFAULT '1' COMMENT '1:admin 2:subadmin 3:customer',
  `company` varchar(110) DEFAULT NULL,
  `contact` varchar(12) DEFAULT NULL,
  `fullname` varchar(25) DEFAULT NULL,
  `email` varchar(145) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `city` varchar(25) DEFAULT NULL,
  `address` text,
  `balance` double(6,2) DEFAULT '10.00',
  `package_id` int(11) DEFAULT '-1',
  `channels` int(11) DEFAULT NULL,
  `minutes` int(11) DEFAULT '0',
  `credit_limit` double(12,6) DEFAULT '0.000000',
  `threshold_balance` double(6,2) DEFAULT '0.00' COMMENT 'low balance alert',
  `billing_rate` double(12,6) DEFAULT '0.000000',
  `did_rate` double(12,6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1,-1,'admin','!Fact@000#',0,'Gventure','9898767687','ADMIN','admin@gvneture.net','male','Noida','Noida IN',5.00,-1,0,0,0.000000,0.00,0.000000,NULL),(2,1,'tom','tom@1234',1,'gv','7839359492','tom','satishec2019@gmail.com',NULL,NULL,NULL,2441.69,11,400,0,0.000000,850.00,NULL,NULL),(11,1,'robert','robert',1,'gv','6854321045','Robert','robert@gmail.com',NULL,NULL,NULL,9999.99,10,500,0,0.000000,25.00,NULL,NULL),(23,1,'manager','abc123xyz',1,'Call Geeks','','Call Geeks Admin','mohamedelwardany94@gmail.com',NULL,NULL,NULL,9807.99,14,500,0,0.000000,0.00,NULL,NULL),(24,1,'testing','testing',1,'gv','9876543212','testing1','testing1@gmail.com',NULL,NULL,NULL,10.00,-1,NULL,0,0.000000,0.00,NULL,NULL),(27,1,'moni','moni@987',1,'self','1234567890','moni','moni@gmail.com',NULL,NULL,NULL,10.00,-1,200,0,5000.000000,9999.99,0.000000,NULL),(31,1,'CGMngr','NewCG2021!!',1,'Call Geeks','4805270583','Mohamed El-Wardany','mohamedelwardany94@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(32,1,'nuno','NewCG2021!!',1,'Call Geeks','4805270583','Mohamed El-Wardany','mohamedelwardany94@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(33,1,'rahul','rahul',1,'gv','1234567890','rahul','rahul@gmail.com','male ','noida','noida IN',10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(34,1,'nagma','nagma',1,'gv','0123456789','nagma','nagma@gmail.com','female','noida','noida IN',10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(35,1,'harshit','harshit',1,'gv','9638527410','harshit','harshit@gmail.com','male','noida','noida IN',10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(36,1,'ankita','ankita',1,'gv','7418529630','ankita','ankita@gmail.com','male','noida','noida IN',10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(37,1,'kajal','123',1,'gv','7539514628','kajal','kajal@gmail.com','male','noida','noida IN',10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(38,1,'nagma1','nagma1',1,'gv','8899789879','nagma1','nagma1@gmail.com','female','noida','noida',20.00,-1,500,0,0.000000,0.00,0.000000,0.000000),(39,NULL,'hhhh','1234',1,'gv',NULL,NULL,'hhhh@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(40,NULL,'undefined','undefined',1,'undefined',NULL,NULL,'undefined',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(41,NULL,'sdfghj','robert',1,'ghjk',NULL,NULL,'robert@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(43,NULL,'dfghj','1234',1,'gv',NULL,NULL,'vbnm@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(44,NULL,'dfgyhujcvb','1234',1,'xcvbnm',NULL,NULL,'robert@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(45,NULL,'null','null',1,'null',NULL,NULL,'null',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(48,NULL,'nagma111','1234',1,'gv',NULL,NULL,'nagma111@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(50,1,'nagma11','nagma11',1,'gv','1234567890','nagma11','nagma11@gmail.com',NULL,NULL,NULL,10.00,-1,17,0,0.000000,0.00,0.000000,NULL),(51,1,'asdjka','djajahfsfhskfj',1,'ajfshfjsh','sdjnaf','adf,ns','sdksajdak.wdi@akjfh.ll',NULL,NULL,NULL,10.00,-1,10,0,0.000000,0.00,0.000000,NULL),(52,1,'vishnu123','12345',1,'gv',NULL,NULL,'vishnu@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(53,1,'vishnu01','vishnu',1,'gventure','09638527412','vishnu','vishnu@gmail.com',NULL,NULL,NULL,10.00,-1,15,0,0.000000,0.00,0.000000,NULL),(54,NULL,'newuser','1234',1,'gv',NULL,NULL,'fghj@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(55,NULL,'hlo','123',1,'gv',NULL,NULL,'hgh@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(56,1,'fhghj','123',1,'gv','123','fhghg','cnxbn@gmail.com',NULL,NULL,NULL,10.00,-1,3,0,0.000000,0.00,0.000000,NULL),(61,1,'rahul11','rahul11',1,'gv','123','','cnxbn@gmail.com',NULL,NULL,NULL,10.00,-1,3,0,0.000000,0.00,0.000000,NULL),(62,1,'sdghvj','123',1,'dfgh`','8765432345','sdghvj','hgf@gmail.com',NULL,NULL,NULL,10.00,-1,3,0,0.000000,0.00,0.000000,NULL),(63,1,'test','test',1,'test',NULL,NULL,'test@gmail.com',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(64,1,'cvbnm','345678',1,'sdfghj','9876543287','xdcfvgbhnm','nvhnv@gv.net',NULL,NULL,NULL,10.00,-1,15,0,0.000000,0.00,0.000000,NULL),(66,1,'ewewew','345678',1,'sdfghj','9876543287','wewewe','nvhnv@gv.net',NULL,NULL,NULL,10.00,-1,15,0,0.000000,0.00,0.000000,NULL),(69,1,'sanee','123456',1,'Gventure',NULL,NULL,'sanee@gventure.net',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(70,1,'yogesh','yogesh',1,'Testing Pvt Ltd','987654321000','Yogesh Sharma','yogesh@gventure.net',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL),(71,1,'sanee__kumar','123456',1,'Gventure','01234567890','sanee kumar','sanee@gventure.net',NULL,NULL,NULL,10.00,-1,500,0,0.000000,0.00,0.000000,NULL);

--
-- Dumping routines for database 'new_cluster'
--
/*!50003 DROP PROCEDURE IF EXISTS `AddDidGroups` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddDidGroups`(var_iduser INT(11), var_did_groupname VARCHAR(200), var_number TEXT)
BEGIN
	DECLARE groupname VARCHAR(200);
	DECLARE lastinsertid INT(11);
	DECLARE tmp VARCHAR(10);
	DECLARE inc INT DEFAULT 0;
	DECLARE str TEXT;
	DECLARE num VARCHAR(40);
	DECLARE cd VARCHAR(10);
	DECLARE stat VARCHAR(3);
	DECLARE cntry VARCHAR(20);
	DECLARE grp_id INT(11);
	
	INSERT INTO did_group (iduser, `name`, created) VALUES ( var_iduser, var_did_groupname, NOW());
	SET @grp_id=LAST_INSERT_ID();
	
	
	if var_number>0 then	
		
		SET @str=var_number;
		SET @inc=1+LENGTH(@str) - LENGTH(REPLACE(@str, ',', ''));
		
		WHILE (@inc>0) DO 
			SET @tmp = (SUBSTRING_INDEX(@str,',',-1));
			SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
			
			
			SELECT SUBSTR(@tmp,1,3) us_code, u.state, u.country FROM did d JOIN us_states u ON SUBSTR(@tmp,1,3)=u.code 
			WHERE iduser=var_iduser LIMIT 1 INTO @cd, @stat, @cntry; 
			
			IF(@inc>0) THEN     
				INSERT INTO did (iduser, id_group, country, state, areacode, number, did_groupname, didtype , created_date) VALUES (var_iduser, @grp_id, @cntry, @stat, @cd, @tmp, var_did_groupname, '1',NOW()); 
			END IF;   
			SET @inc=@inc-1;
		END WHILE;
	
	end if;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AddDisposition` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddDisposition`(var_iduser int(11), var_id_campaign int(11), var_indexid int(11), var_label varchar(255), var_abbr varchar(100),
					   var_insert_cal tinyint(1), var_email_client tinyint(1), var_email_agent tinyint(1), var_voice_drop tinyint(1),
					   var_voicemsg int(11), var_emailtmp_agent int(11), var_sms tinyint(1), var_smstxt tinyint(4), var_note tinyint(1),
					   var_notetxt text, var_callback tinyint(1), var_color varchar(50), var_workflow tinyint(1), var_workflowtxt int(11),
					   var_callqueue tinyint(1), var_callqueuetxt int(11), var_status tinyint(1), var_calevent tinyint(1), 
					   var_emailrem_agent int(11), var_smstxtrem int(11), var_voicemsg_rem int(1), var_event_title varchar(11),
					   var_client_duration int(1), var_client_duration_type varchar(11), var_agent_duration int(1), 
					   var_agent_duration_type varchar(11),var_text_duration int(1), var_vm_durationn int(1),var_vm_duration_type varchar(11),
					   var_transfer int(11),var_number varchar(11))
BEGIN
	INSERT INTO `custom_disposition`(`iduser`, `id_campaign`, `indexid`,`label`,`abbr`,`insert_cal`,`email_client`,`email_agent`,`voice_drop`,
	`voicemsg`,`emailtmp_agent`, `emailtmp_client`, `sms`, `smstxt`, `note`, `notetxt`, `callback`, `color`, `workflow`, `workflowtxt`, `callqueue`,
	`callqueuetxt`, `status`, `calevent`, `emailrem_agent`,`emailrem_client`,`smstxtrem`, `voicemsg_rem`, `event_title`, `client_duration`,
	`client_duration_type`, `agent_duration`, `agent_duration_type`, `text_duration`, `text_duration_type`, `vm_duration`, `vm_duration_type`,
	`transfer`, `number`) 
	
	VALUES (var_iduser, var_id_campaign, var_indexid, var_label, var_abbr,var_insert_cal, var_email_client, var_email_agent, var_voice_drop,
		var_voicemsg, var_emailtmp_agent, var_sms, var_smstxt, var_note,var_notetxt, var_callback, var_color, var_workflow, var_workflowtxt,
		var_callqueue, var_callqueuetxt, var_status, var_calevent, var_emailrem_agent, var_smstxtrem, var_voicemsg_rem, var_event_title,
		var_client_duration, var_client_duration_type, var_agent_duration, var_agent_duration_type,var_text_duration, var_vm_durationn,var_vm_duration_type,
		var_transfer,var_number);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AddDncName` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddDncName`( var_iduser int(11), var_list_name VARCHAR(145), var_globel_dnc varchar(50))
BEGIN
	INSERT INTO `dnc_lists` SET `iduser`= var_iduser, `list_name`= var_list_name, `globel_dnc`= var_globel_dnc , `dnc_count`= 0 , `created`= NOW();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `add_did` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_did`(var_didgroup_id INT(11), var_iduser INT(11), var_did_groupname VARCHAR(200), var_number TEXT)
BEGIN
	DECLARE groupname VARCHAR(200);
	DECLARE lastinsertid INT(11);
	DECLARE tmp VARCHAR(10);
	DECLARE inc INT DEFAULT 0;
	DECLARE str TEXT;
	DECLARE num VARCHAR(40);
	DECLARE cd VARCHAR(10);
	DECLARE stat VARCHAR(3);
	DECLARE cntry VARCHAR(20);
	declare grp_id int(11);
	
	INSERT INTO did_group (iduser, `name`, created) VALUES ( var_iduser, var_did_groupname, NOW());
	set @grp_id=LAST_INSERT_ID();
	
	
		SET @str=var_number;
		SET @inc=1+LENGTH(@str) - LENGTH(REPLACE(@str, ',', ''));
		
		WHILE (@inc>0) DO 
			SET @tmp = (SUBSTRING_INDEX(@str,',',-1));
			SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
			
			SELECT SUBSTR(@tmp,1,3) us_code, u.state, u.country FROM did d JOIN us_states u ON SUBSTR(@tmp,1,3)=u.code 
			WHERE iduser=var_iduser LIMIT 1 INTO @cd, @stat, @cntry; 
			
			IF(@inc>0) THEN     
				INSERT INTO did (iduser, id_group, country, state, areacode, number, did_groupname, created_date) VALUES (var_iduser, @grp_id, @cntry, @stat, @cd, @tmp, var_did_groupname,now()); 
			END IF;   
			SET @inc=@inc-1;
		END WHILE;
	
		

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AgentDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AgentDelete`(var_iduser int(11), var_idagent int(11), var_id_agentgroup int(11))
BEGIN
				
	
	DELETE FROM agents WHERE id=var_idagent AND agentgroupid=var_id_agentgroup and iduser=var_iduser ;
	DELETE FROM `tiers` WHERE id_agentgroup=var_id_agentgroup AND idagent=var_idagent and iduser=var_iduser;
	UPDATE agents_group SET total_agent=(SELECT COUNT(*) FROM agents WHERE agentgroupid=var_id_agentgroup) WHERE id=var_id_agentgroup and iduser = var_iduser ;
	
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AgentEdit` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AgentEdit`(var_agentgroupid int(11),var_group_name varchar(145))
BEGIN
	UPDATE agents_group SET `group_name`= var_group_name , `updated_at`= NOW() WHERE id = var_agentgroupid ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AgentGroupAdd` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AgentGroupAdd`(var_iduser int(11), var_group_name varchar(150))
BEGIN
	INSERT INTO agents_group(`iduser`,`group_name`,`added_at`) VALUES(var_iduser,var_group_name,NOW());
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AgentGroupDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AgentGroupDelete`(var_id int(11), var_iduser int(11))
BEGIN
	delete from agents_group where id = var_id and iduser = var_iduser ;   

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AgentGroupDetail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AgentGroupDetail`(var_iduser INT(11), var_agentgrouID INT(11), var_agent INT(11), `type` TINYINT(1))
BEGIN 
	DECLARE str VARCHAR(40);
	
	SELECT CONCAT('\'',group_name,'\'') FROM agents_group WHERE id=var_agentgrouID INTO @str ;
	
	DROP TABLE IF EXISTS temp_cd;
	DROP TABLE IF EXISTS temp_leads_pd;
	DROP TABLE IF EXISTS temp_group;
	DROP TABLE IF EXISTS temp_login;
	DROP TABLE IF EXISTS temp_agent;
	
	IF var_agent>0 THEN
		
		CREATE TEMPORARY TABLE temp_agent (SELECT var_agent idagent);
	ELSE
		CREATE TEMPORARY TABLE temp_agent (SELECT idagent FROM tiers WHERE id_agentgroup=var_agentgrouID AND iduser=var_iduser);
	END IF;
	
	SET @query1=CONCAT('create temporary table temp_cd as(select lead_id, caller_id_number source,
		billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
		queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, 
		IF(queue_terminate_epoch>0,queue_terminate_epoch-queue_answered_epoch,0) talk_time,
		IF(queue_answered_epoch>0, queue_answered_epoch-queue_join_epoch, queue_cancelled_epoch-queue_join_epoch) queuetime 
		FROM cdr_pd cp 
		WHERE cp.iduser=',var_iduser,' AND queue=',@str,' order by id_call desc limit 2000)');
	PREPARE stmt1 FROM @query1;
	
	EXECUTE stmt1;
	
	CREATE INDEX lead_id ON temp_cd(lead_id);
	
	
	CREATE TEMPORARY TABLE temp_leads_pd AS(SELECT id, CONCAT(first_name,' ',last_name) `name`, address1,`phone1`,uniqueid 
	FROM leads_pd WHERE iduser=var_iduser AND isdn_error=-1);
	
	
	
	CREATE INDEX id ON temp_leads_pd(id);
	
	CREATE TEMPORARY TABLE temp_group AS (SELECT tp.`name`, address1 address, `phone1` destination,
		source, 
		billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
		queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, 
		IF(queue_terminate_epoch>0,queue_terminate_epoch-queue_answered_epoch,0) talk_time,
		IF(queue_answered_epoch>0, queue_answered_epoch-queue_join_epoch, queue_cancelled_epoch-queue_join_epoch) queuetime 
		FROM temp_cd tc JOIN temp_leads_pd tp ON tc.lead_id=tp.id);
		
	IF `type`=0 THEN 
		SELECT tg.`name`, address, source, destination, billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
		queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, tg.talk_time, queuetime
		FROM temp_group tg LEFT JOIN agents a 
		ON a.username=tg.queue 
		WHERE id IN (SELECT idagent FROM temp_agent) LIMIT 1000;
	
		SELECT d.date, COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(billsec) `duration`, 
		SUM(queuetime) `queuetime`, SUM(talk_time) `talk_time`, MIN(queue_join_epoch) `firstcall`, MAX(queue_join_epoch) `lastcall`, 
		ROUND(SUM(talk_time)/SUM(IF(talk_time>0, 1,0)), 2) `avg_talktime`, ROUND(SUM(queuetime)/COUNT(*), 2) `avg_queuetime` 
		FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		SELECT COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(IF(talk_time>15, 1,0)) `above15`, 
		SUM(IF(talk_time>30, 1,0)) `above30`, SUM(IF(talk_time>60, 1,0)) `above60`, SUM(IF(talk_time>120, 1,0)) `above2`, SUM(IF(talk_time>180, 1,0)) `above3`, 
		SUM(IF(talk_time>300, 1,0)) `above5`, SUM(IF(talk_time>600, 1,0)) `above10`, SUM(IF(talk_time>900, 1,0)) `above15` FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		CREATE TEMPORARY TABLE temp_login AS (SELECT MIN(logintime) logintime, MAX(logouttime) logouttime, id_agent, 
		FROM_UNIXTIME(MIN(ag.last_bridge_start)) `first_call_start`, FROM_UNIXTIME(MIN(ag.last_bridge_end)) `first_call_end`, 
		FROM_UNIXTIME(MIN(ag.last_offered_call)) `first_offered_call`, FROM_UNIXTIME(MAX(ag.last_bridge_start)) `last_call_start`, 
		FROM_UNIXTIME(MAX(ag.last_bridge_end)) `last_call_end`, FROM_UNIXTIME(MAX(ag.last_offered_call)) `last_offered_call`, SUM(ag.calls_answered), 
		SUM(ag.talk_time), FROM_UNIXTIME(MIN(ag.ready_time)) `start_readytime`, FROM_UNIXTIME(MAX(ag.ready_time)) `end_readytime` 
		FROM `agent_logs` ag LEFT JOIN  agents a ON ag.id_agent=a.id 
		WHERE a.iduser=var_iduser AND a.id IN (SELECT idagent FROM temp_agent));
	
		SELECT * FROM temp_login tg RIGHT JOIN `date` d	
		ON d.date=DATE_FORMAT(tg.logintime,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.logintime,'%Y-%m-%d');
	ELSEIF `type`=1 THEN  
		SELECT tg.`name`, address, source, destination, billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
			queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, tg.talk_time, queuetime
		FROM temp_group tg LEFT JOIN agents a 
		ON a.name=tg.agent 
		WHERE id IN (SELECT idagent FROM temp_agent) AND (DAY(start_stamp)=DAY(NOW())) LIMIT 100;
	
		SELECT d.date, COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(billsec) `duration`, 
		SUM(queuetime) `queuetime`, SUM(talk_time) `talk_time`, MIN(queue_join_epoch) `firstcall`, MAX(queue_join_epoch) `lastcall`, 
		ROUND(SUM(talk_time)/SUM(IF(talk_time>0, 1,0)), 2) `avg_talktime`, ROUND(SUM(queuetime)/COUNT(*), 2) `avg_queuetime` 
		FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (DAY(start_stamp)=DAY(NOW())) 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		SELECT COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(IF(talk_time>15, 1,0)) `above15`, 
		SUM(IF(talk_time>30, 1,0)) `above30`, SUM(IF(talk_time>60, 1,0)) `above60`, SUM(IF(talk_time>120, 1,0)) `above2`, SUM(IF(talk_time>180, 1,0)) `above3`, 
		SUM(IF(talk_time>300, 1,0)) `above5`, SUM(IF(talk_time>600, 1,0)) `above10`, SUM(IF(talk_time>900, 1,0)) `above15` FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (DAY(start_stamp)=DAY(NOW())) GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		CREATE TEMPORARY TABLE temp_login AS (SELECT MIN(logintime) logintime, MAX(logouttime) logouttime, id_agent, 
		FROM_UNIXTIME(MIN(ag.last_bridge_start)) `first_call_start`, FROM_UNIXTIME(MIN(ag.last_bridge_end)) `first_call_end`, 
		FROM_UNIXTIME(MIN(ag.last_offered_call)) `first_offered_call`, FROM_UNIXTIME(MAX(ag.last_bridge_start)) `last_call_start`, 
		FROM_UNIXTIME(MAX(ag.last_bridge_end)) `last_call_end`, FROM_UNIXTIME(MAX(ag.last_offered_call)) `last_offered_call`, SUM(ag.calls_answered), 
		SUM(ag.talk_time), FROM_UNIXTIME(MIN(ag.ready_time)) `start_readytime`, FROM_UNIXTIME(MAX(ag.ready_time)) `end_readytime` 
		FROM `agent_logs` ag LEFT JOIN  agents a ON ag.id_agent=a.id 
		WHERE a.iduser=var_iduser AND a.id IN (SELECT idagent FROM temp_agent) AND (DAY(logintime)=DAY(NOW())) );
	
		SELECT * FROM temp_login tg RIGHT JOIN `date` d	
		ON d.date=DATE_FORMAT(tg.logintime,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.logintime,'%Y-%m-%d');
	ELSEIF `type`=2 THEN   
		SELECT tg.`name`, address, source, destination, billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
			queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, tg.talk_time, queuetime
		FROM temp_group tg LEFT JOIN agents a 
		ON a.name=tg.agent 
		WHERE id IN (SELECT idagent FROM temp_agent) AND (DAY(start_stamp)=DAY(NOW()-1)) LIMIT 100;
	
		SELECT d.date, COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(billsec) `duration`, 
		SUM(queuetime) `queuetime`, SUM(talk_time) `talk_time`, MIN(queue_join_epoch) `firstcall`, MAX(queue_join_epoch) `lastcall`, 
		ROUND(SUM(talk_time)/SUM(IF(talk_time>0, 1,0)), 2) `avg_talktime`, ROUND(SUM(queuetime)/COUNT(*), 2) `avg_queuetime` 
		FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (DAY(start_stamp)=DAY(NOW()-1)) 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		SELECT COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(IF(talk_time>15, 1,0)) `above15`, 
		SUM(IF(talk_time>30, 1,0)) `above30`, SUM(IF(talk_time>60, 1,0)) `above60`, SUM(IF(talk_time>120, 1,0)) `above2`, SUM(IF(talk_time>180, 1,0)) `above3`, 
		SUM(IF(talk_time>300, 1,0)) `above5`, SUM(IF(talk_time>600, 1,0)) `above10`, SUM(IF(talk_time>900, 1,0)) `above15` FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (DAY(start_stamp)=DAY(NOW()-1)) GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		CREATE TEMPORARY TABLE temp_login AS (SELECT MIN(logintime) logintime, MAX(logouttime) logouttime, id_agent, 
		FROM_UNIXTIME(MIN(ag.last_bridge_start)) `first_call_start`, FROM_UNIXTIME(MIN(ag.last_bridge_end)) `first_call_end`, 
		FROM_UNIXTIME(MIN(ag.last_offered_call)) `first_offered_call`, FROM_UNIXTIME(MAX(ag.last_bridge_start)) `last_call_start`, 
		FROM_UNIXTIME(MAX(ag.last_bridge_end)) `last_call_end`, FROM_UNIXTIME(MAX(ag.last_offered_call)) `last_offered_call`, SUM(ag.calls_answered), 
		SUM(ag.talk_time), FROM_UNIXTIME(MIN(ag.ready_time)) `start_readytime`, FROM_UNIXTIME(MAX(ag.ready_time)) `end_readytime` 
		FROM `agent_logs` ag LEFT JOIN  agents a ON ag.id_agent=a.id 
		WHERE a.iduser=var_iduser AND a.id IN (SELECT idagent FROM temp_agent) AND (DAY(logintime)=DAY(NOW()-1)));
	
		SELECT * FROM temp_login tg RIGHT JOIN `date` d	
		ON d.date=DATE_FORMAT(tg.logintime,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.logintime,'%Y-%m-%d');
	ELSEIF `type`=3 THEN   
		SELECT tg.`name`, address, source, destination, billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
			queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, tg.talk_time, queuetime
		FROM temp_group tg LEFT JOIN agents a 
		ON a.name=tg.agent 
		WHERE id IN (SELECT idagent FROM temp_agent) AND (WEEK(start_stamp)=WEEK(NOW())) LIMIT 100;
	
		SELECT d.date, COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(billsec) `duration`, 
		SUM(queuetime) `queuetime`, SUM(talk_time) `talk_time`, MIN(queue_join_epoch) `firstcall`, MAX(queue_join_epoch) `lastcall`, 
		ROUND(SUM(talk_time)/SUM(IF(talk_time>0, 1,0)), 2) `avg_talktime`, ROUND(SUM(queuetime)/COUNT(*), 2) `avg_queuetime` 
		FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (WEEK(start_stamp)=WEEK(NOW())) 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		SELECT COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(IF(talk_time>15, 1,0)) `above15`, 
		SUM(IF(talk_time>30, 1,0)) `above30`, SUM(IF(talk_time>60, 1,0)) `above60`, SUM(IF(talk_time>120, 1,0)) `above2`, SUM(IF(talk_time>180, 1,0)) `above3`, 
		SUM(IF(talk_time>300, 1,0)) `above5`, SUM(IF(talk_time>600, 1,0)) `above10`, SUM(IF(talk_time>900, 1,0)) `above15` FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (WEEK(start_stamp)=WEEK(NOW())) GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		CREATE TEMPORARY TABLE temp_login AS (SELECT MIN(logintime) logintime, MAX(logouttime) logouttime, id_agent, 
		FROM_UNIXTIME(MIN(ag.last_bridge_start)) `first_call_start`, FROM_UNIXTIME(MIN(ag.last_bridge_end)) `first_call_end`, 
		FROM_UNIXTIME(MIN(ag.last_offered_call)) `first_offered_call`, FROM_UNIXTIME(MAX(ag.last_bridge_start)) `last_call_start`, 
		FROM_UNIXTIME(MAX(ag.last_bridge_end)) `last_call_end`, FROM_UNIXTIME(MAX(ag.last_offered_call)) `last_offered_call`, SUM(ag.calls_answered), 
		SUM(ag.talk_time), FROM_UNIXTIME(MIN(ag.ready_time)) `start_readytime`, FROM_UNIXTIME(MAX(ag.ready_time)) `end_readytime` 
		FROM `agent_logs` ag LEFT JOIN  agents a ON ag.id_agent=a.id 
		WHERE a.iduser=var_iduser AND a.id IN (SELECT idagent FROM temp_agent) AND (WEEK(logintime)=WEEK(NOW())));
	
		SELECT * FROM temp_login tg RIGHT JOIN `date` d	
		ON d.date=DATE_FORMAT(tg.logintime,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.logintime,'%Y-%m-%d');
	ELSEIF `type`=4 THEN     
		SELECT tg.`name`, address, source, destination, billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
			queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, tg.talk_time, queuetime
		FROM temp_group tg LEFT JOIN agents a 
		ON a.name=tg.agent 
		WHERE id IN (SELECT idagent FROM temp_agent) AND (WEEK(start_stamp)=WEEK(NOW())-1) LIMIT 100;
	
		SELECT d.date, COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(billsec) `duration`, 
		SUM(queuetime) `queuetime`, SUM(talk_time) `talk_time`, MIN(queue_join_epoch) `firstcall`, MAX(queue_join_epoch) `lastcall`, 
		ROUND(SUM(talk_time)/SUM(IF(talk_time>0, 1,0)), 2) `avg_talktime`, ROUND(SUM(queuetime)/COUNT(*), 2) `avg_queuetime` 
		FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (WEEK(start_stamp)=WEEK(NOW())-1) 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		SELECT COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(IF(talk_time>15, 1,0)) `above15`, 
		SUM(IF(talk_time>30, 1,0)) `above30`, SUM(IF(talk_time>60, 1,0)) `above60`, SUM(IF(talk_time>120, 1,0)) `above2`, SUM(IF(talk_time>180, 1,0)) `above3`, 
		SUM(IF(talk_time>300, 1,0)) `above5`, SUM(IF(talk_time>600, 1,0)) `above10`, SUM(IF(talk_time>900, 1,0)) `above15` FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (WEEK(start_stamp)=WEEK(NOW())-1) GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		CREATE TEMPORARY TABLE temp_login AS (SELECT MIN(logintime) logintime, MAX(logouttime) logouttime, id_agent, 
		FROM_UNIXTIME(MIN(ag.last_bridge_start)) `first_call_start`, FROM_UNIXTIME(MIN(ag.last_bridge_end)) `first_call_end`, 
		FROM_UNIXTIME(MIN(ag.last_offered_call)) `first_offered_call`, FROM_UNIXTIME(MAX(ag.last_bridge_start)) `last_call_start`, 
		FROM_UNIXTIME(MAX(ag.last_bridge_end)) `last_call_end`, FROM_UNIXTIME(MAX(ag.last_offered_call)) `last_offered_call`, SUM(ag.calls_answered), 
		SUM(ag.talk_time), FROM_UNIXTIME(MIN(ag.ready_time)) `start_readytime`, FROM_UNIXTIME(MAX(ag.ready_time)) `end_readytime` 
		FROM `agent_logs` ag LEFT JOIN  agents a ON ag.id_agent=a.id 
		WHERE a.iduser=var_iduser AND a.id IN (SELECT idagent FROM temp_agent) AND (WEEK(logintime)=WEEK(NOW())-1));
	
		SELECT * FROM temp_login tg RIGHT JOIN `date` d	
		ON d.date=DATE_FORMAT(tg.logintime,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.logintime,'%Y-%m-%d');
	ELSEIF `type`=5 THEN    
		SELECT tg.`name`, address, source, destination, billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
			queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, tg.talk_time, queuetime
		FROM temp_group tg LEFT JOIN agents a 
		ON a.name=tg.agent 
		WHERE id IN (SELECT idagent FROM temp_agent) AND (MONTH(start_stamp)=MONTH(NOW())) LIMIT 1000;
	
		SELECT d.date, COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(billsec) `duration`, 
		SUM(queuetime) `queuetime`, SUM(talk_time) `talk_time`, MIN(queue_join_epoch) `firstcall`, MAX(queue_join_epoch) `lastcall`, 
		ROUND(SUM(talk_time)/SUM(IF(talk_time>0, 1,0)), 2) `avg_talktime`, ROUND(SUM(queuetime)/COUNT(*), 2) `avg_queuetime` 
		FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (MONTH(start_stamp)=MONTH(NOW())) 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		SELECT COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(IF(talk_time>15, 1,0)) `above15`, 
		SUM(IF(talk_time>30, 1,0)) `above30`, SUM(IF(talk_time>60, 1,0)) `above60`, SUM(IF(talk_time>120, 1,0)) `above2`, SUM(IF(talk_time>180, 1,0)) `above3`, 
		SUM(IF(talk_time>300, 1,0)) `above5`, SUM(IF(talk_time>600, 1,0)) `above10`, SUM(IF(talk_time>900, 1,0)) `above15` FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (MONTH(start_stamp)=MONTH(NOW())) GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		CREATE TEMPORARY TABLE temp_login AS (SELECT MIN(logintime) logintime, MAX(logouttime) logouttime, id_agent, 
		FROM_UNIXTIME(MIN(ag.last_bridge_start)) `first_call_start`, FROM_UNIXTIME(MIN(ag.last_bridge_end)) `first_call_end`, 
		FROM_UNIXTIME(MIN(ag.last_offered_call)) `first_offered_call`, FROM_UNIXTIME(MAX(ag.last_bridge_start)) `last_call_start`, 
		FROM_UNIXTIME(MAX(ag.last_bridge_end)) `last_call_end`, FROM_UNIXTIME(MAX(ag.last_offered_call)) `last_offered_call`, SUM(ag.calls_answered), 
		SUM(ag.talk_time), FROM_UNIXTIME(MIN(ag.ready_time)) `start_readytime`, FROM_UNIXTIME(MAX(ag.ready_time)) `end_readytime` 
		FROM `agent_logs` ag LEFT JOIN  agents a ON ag.id_agent=a.id 
		WHERE a.iduser=var_iduser AND a.id IN (SELECT idagent FROM temp_agent) AND (MONTH(logintime)=MONTH(NOW())));
	
		SELECT * FROM temp_login tg RIGHT JOIN `date` d	
		ON d.date=DATE_FORMAT(tg.logintime,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.logintime,'%Y-%m-%d');
	ELSEIF `type`=6 THEN    
		SELECT tg.`name`, address, source, destination, billsec, queue, agent, start_stamp, end_stamp, cc_record_filename, last_bridge_hangup_cause, 
			queue_answered_epoch, queue_terminate_epoch, queue_join_epoch, queue_cancelled_epoch, tg.talk_time, queuetime
		FROM temp_group tg LEFT JOIN agents a 
		ON a.name=tg.agent 
		WHERE id IN (SELECT idagent FROM temp_agent) AND (MONTH(start_stamp)=MONTH(NOW())-1) LIMIT 100;
	
		SELECT d.date, COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(billsec) `duration`, 
		SUM(queuetime) `queuetime`, SUM(talk_time) `talk_time`, MIN(queue_join_epoch) `firstcall`, MAX(queue_join_epoch) `lastcall`, 
		ROUND(SUM(talk_time)/SUM(IF(talk_time>0, 1,0)), 2) `avg_talktime`, ROUND(SUM(queuetime)/COUNT(*), 2) `avg_queuetime` 
		FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (MONTH(start_stamp)=MONTH(NOW())-1) 
		GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		SELECT COUNT(*) total, SUM(IF(talk_time>0, 1,0)) `connect`, (COUNT(*)-SUM(IF(talk_time>0, 1,0))) abandoned, SUM(IF(talk_time>15, 1,0)) `above15`, 
		SUM(IF(talk_time>30, 1,0)) `above30`, SUM(IF(talk_time>60, 1,0)) `above60`, SUM(IF(talk_time>120, 1,0)) `above2`, SUM(IF(talk_time>180, 1,0)) `above3`, 
		SUM(IF(talk_time>300, 1,0)) `above5`, SUM(IF(talk_time>600, 1,0)) `above10`, SUM(IF(talk_time>900, 1,0)) `above15` FROM temp_group tg RIGHT JOIN `date` d
		ON d.date=DATE_FORMAT(tg.start_stamp,'%Y-%m-%d') 
		WHERE (MONTH(start_stamp)=MONTH(NOW())-1) GROUP BY DATE_FORMAT(tg.start_stamp,'%Y-%m-%d');
	
		CREATE TEMPORARY TABLE temp_login AS (SELECT MIN(logintime) logintime, MAX(logouttime) logouttime, id_agent, 
		FROM_UNIXTIME(MIN(ag.last_bridge_start)) `first_call_start`, FROM_UNIXTIME(MIN(ag.last_bridge_end)) `first_call_end`, 
		FROM_UNIXTIME(MIN(ag.last_offered_call)) `first_offered_call`, FROM_UNIXTIME(MAX(ag.last_bridge_start)) `last_call_start`, 
		FROM_UNIXTIME(MAX(ag.last_bridge_end)) `last_call_end`, FROM_UNIXTIME(MAX(ag.last_offered_call)) `last_offered_call`, SUM(ag.calls_answered), 
		SUM(ag.talk_time), FROM_UNIXTIME(MIN(ag.ready_time)) `start_readytime`, FROM_UNIXTIME(MAX(ag.ready_time)) `end_readytime` 
		FROM `agent_logs` ag LEFT JOIN  agents a ON ag.id_agent=a.id 
		WHERE a.iduser=var_iduser AND a.id IN (SELECT idagent FROM temp_agent) AND (MONTH(logintime)=MONTH(NOW())-1));
	
		SELECT * FROM temp_login tg RIGHT JOIN `date` d	
		ON d.date=DATE_FORMAT(tg.logintime,'%Y-%m-%d') 
		GROUP BY DATE_FORMAT(tg.logintime,'%Y-%m-%d');
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AgentStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AgentStatus`(var_agent_id int(11), var_status varchar(255))
BEGIN	
	if(var_status='Paused') then
		UPDATE `agents` SET `lastcallhungup`=NOW(),`status`=var_status WHERE `id`=var_agent_id;
	end if; 
	
	IF(var_status='Ready' or var_status='Logged Out' or var_status='Logged In' or var_status='Available') THEN
		UPDATE `agents` SET `status`=var_status WHERE `id`=var_agent_id;
	END IF; 
	
	IF(var_status='Logged In') THEN
		INSERT INTO `agent_logs` (`logintime`,`logouttime`,`id_agent`,`max_no_answer`,`wrap_up_time`,`reject_delay_time`,`busy_delay_time`,
		`no_answer_delay_time`,`last_bridge_start`,`last_bridge_end`,`last_offered_call`,`last_status_change`,`no_answer_count`,
		`calls_answered`,`talk_time`,`ready_time`) 
		SELECT `lastlogin`, now(),var_agent_id,`max_no_answer`,`wrap_up_time`,`reject_delay_time`,`busy_delay_time`,`no_answer_delay_time`,
		`last_bridge_start`,`last_bridge_end`, `last_offered_call`,`last_status_change`,`no_answer_count`,`calls_answered`,
		`talk_time`,`ready_time` FROM `agents` WHERE id=var_agent_id;
		update `agents` set `last_bridge_start`=0,`last_bridge_end`=0,`last_offered_call`=0,`last_status_change`=0,`no_answer_count`=0,
		`calls_answered`=0,`talk_time`=0,`ready_time`=0,`lastlogin`='0000-00-00 00:00:00',`lastcallhungup`='0000-00-00 00:00:00'
		WHERE id=var_agent_id;
	END IF; 
	
	IF(var_status='Login') THEN
		UPDATE `agents` SET `last_bridge_start`=0,`last_bridge_end`=0,`last_offered_call`=0,`last_status_change`=0,`no_answer_count`=0,
		`calls_answered`=0,`talk_time`=0,`ready_time`=0,`lastlogin`='0000-00-00 00:00:00',`lastcallhungup`='0000-00-00 00:00:00'
		WHERE id=var_agent_id;
		UPDATE `agents` SET `lastcallhungup`=NOW(),`status`='On Break', `lastlogin`=now() WHERE `id`=var_agent_id;
	END IF; 
	
	select * from agents where `id`=var_agent_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `agent_add` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `agent_add`(var_agentid INT(11), var_iduser INT(11),var_username VARCHAR(200), var_fullname VARCHAR(200),
					var_password VARCHAR(200),var_email VARCHAR(200), var_agentgroupid VARCHAR(200),var_name varchar(200), var_skill tinyint(2))
BEGIN
	
	DECLARE groupname VARCHAR(200);
	DECLARE lastinsertid INT(11);
	DECLARE agent_id INT;
	
	# To get the AG name
	SELECT `group_name` FROM `agents_group` WHERE `id`=var_agentgroupid INTO @groupname;
	
	# To create new agent
	IF var_agentid=-1 THEN
		INSERT INTO `agents`(`iduser`,`username`,`name`,`fullname`,`password`,`email`,`system`,`uuid`,`type`,contact,`status`,`state`,`instance_id`,`agentgroupid`,`skill`)
		VALUES (var_iduser, var_username, var_name , var_fullname, var_password, var_email, 'single_box', '', 'callback', CONCAT('{sip_h_X-Crmdata=${CRMdata}}user/',var_name), 
		'Never Logged In', 'Waiting','single_box', var_agentgroupid, var_skill);
		
		SET @lastinsertid = LAST_INSERT_ID();
		
		SELECT id FROM agents WHERE `username`=var_username INTO @agent_id;
		INSERT INTO `tiers`(`queue`,`agent`,`iduser`,`state`,`idagent`,`id_agentgroup`) VALUES (@groupname, var_name, var_iduser, 'Ready', @lastinsertid, var_agentgroupid);
		UPDATE agents_group SET total_agent=(SELECT COUNT(*) FROM agents WHERE agentgroupid=var_agentgroupid) WHERE id=var_agentgroupid;

	# To update the existing agent
	ELSE
		UPDATE `agents` SET `fullname`=var_fullname, `password`=var_password, `email`=var_email, `agentgroupid`=var_agentgroupid, `skill`=var_skill WHERE `id`=var_agentid;
	END IF;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `agent_group` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `agent_group`(var_id INT(11), var_iduser INT(11))
BEGIN	
	# To get All agent groups + first AG's agents
	IF var_id=-1 THEN
		SELECT ag.id, group_name, IFNULL(total,0) agents, IFNULL(logout,0) logout, IFNULL(break,0) break, IFNULL(ready,0) ready FROM agents_group ag 
		LEFT JOIN (
			SELECT COUNT(*) total, agentgroupid, SUM(IF(STATUS='Logged Out', 1,0)) `logout`, SUM(IF(STATUS='Paused', 1,0)) `break`, SUM(IF(STATUS='Available', 1,0)) `ready` 
			FROM agents WHERE iduser=var_iduser 
			GROUP BY agentgroupid
		) a 
		ON ag.id=a.agentgroupid 
		WHERE ag.iduser=var_iduser;
		
		SELECT id, `username` `name`,  `status`, `uuid`, IF(last_bridge_end > 0, FROM_UNIXTIME(last_bridge_end, '%d %b, %Y %h:%i %p'), '') `last_call`, IF(last_offered_call > 0, FROM_UNIXTIME(last_offered_call, '%d %b, %Y %h:%i %p'), '') `last_offered`,
		IF(ready_time > 0, FROM_UNIXTIME(ready_time, '%d %b, %h:%i %p'), '') `ready`, SEC_TO_TIME(talk_time) `talk_time`, calls_answered, no_answer_count, IF(last_bridge_end > 0, FROM_UNIXTIME(last_bridge_end, '%d %b, %Y %h:%i %p'), '') lastlogin, lastcallhungup, licensed,
		profile_image, state 
		FROM agents
		WHERE agentgroupid=(SELECT id FROM agents_group WHERE iduser=var_iduser LIMIT 1) AND iduser=var_iduser
		ORDER BY `status` ASC;
	
	# To get the specific AG's agents
	ELSE
		SELECT id, `username` `name`,  `status`, `uuid`, IF(last_bridge_end > 0, FROM_UNIXTIME(last_bridge_end, '%d %b, %Y %h:%i %p'), '') `last_call`, IF(last_offered_call > 0, FROM_UNIXTIME(last_offered_call, '%d %b, %Y %h:%i %p'), '') `last_offered`,
		IF(ready_time > 0, FROM_UNIXTIME(ready_time, '%d %b, %h:%i %p'), '') `ready`, SEC_TO_TIME(talk_time) `talk_time`, calls_answered, no_answer_count, IF(last_bridge_end > 0, FROM_UNIXTIME(last_bridge_end, '%d %b, %Y %h:%i %p'), '') lastlogin, lastcallhungup, licensed,
		profile_image, state 
		FROM agents 
		WHERE agentgroupid=var_id AND iduser=var_iduser
		ORDER BY `status` ASC;
		
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `Agent_Profile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Agent_Profile`(var_agent_id int(11), var_username VARCHAR(255), var_password VARCHAR(255), var_email VARCHAR(525),`action` varchar(50))
BEGIN
		
		if `action`='select' then 
			SELECT username, `password`, email FROM agents WHERE id = var_agent_id ;
		elseif `action`='update' then
			UPDATE agents SET username=var_username, `password`=var_password, email=var_email WHERE id = var_agent_id ;
		end if;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `Call_Log` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Call_Log`(var_agent_id INT(11), var_start_date DATETIME, var_end_date DATETIME, var_dispostion_type VARCHAR(20))
BEGIN
	DECLARE varrsql VARCHAR(255);
	DROP TABLE IF EXISTS temp;
	
	IF LENGTH(var_start_date)>0 THEN
		SET @varrsql=CONCAT(' AND `start_stamp` >=',var_start_date,' ');
	END IF;
	
	IF LENGTH(var_start_date)>0 THEN
		SET @varrsql=CONCAT(' AND `start_stamp` <=',var_end_date,' ');
	END IF;
	
	IF LENGTH(var_start_date)>0 THEN
		SET @varrsql=CONCAT(' AND `dispositon` =',var_dispostion_type,' ');
	END IF;
	
	CREATE TEMPORARY TABLE temp(first_name VARCHAR(100), last_name VARCHAR(100), `date` DATETIME, duration INT(4), dispostion VARCHAR(20));
	
	SET @query1=CONCAT('INSERT HIGH_PRIORITY IGNORE INTO temp(first_name, last_name, dispostion, `date`, duration)(select lp.first_name, lp.last_name, lp.disposition, start_stamp, duration from `cdr_pd` cp join `leads_pd` lp on cp.lead_id=lp.id where cp.agent_id=',var_agent_id,' ',@varrsql,'');
	PREPARE stmt1 FROM @query1;
	EXECUTE stmt1;
	
	SET @query2=CONCAT('INSERT HIGH_PRIORITY IGNORE INTO temp(first_name, last_name, dispostion, `date`, duration)(SELECT lv.first_name, lv.last_name, lv.disposition, start_stamp, duration FROM `cdr_vb` cv JOIN `leads_vb` lv ON cv.lead_id=lv.id WHERE cv.agent_id=',var_agent_id,' ',@varrsql,'');
	PREPARE stmt2 FROM @query2;
	EXECUTE stmt2;
	
	SELECT * FROM temp;
	
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CampaignConfig` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CampaignConfig`(var_iduser INT(11), var_listgroup_type TINYINT(1), var_idcampaign INT(11))
BEGIN

	DECLARE dgroup_id INT;
	declare agroup_id int;
	
		SELECT didgroup FROM campaign WHERE id=var_idcampaign INTO @dgroup_id ;
		SELECT id_agentgroup FROM campaign WHERE id= var_idcampaign INTO @agroup_id ;
	
	IF var_idcampaign=-1 THEN
		
		SELECT id, listgroup_name FROM uploads_group WHERE listgroup_type = var_listgroup_type AND iduser=var_iduser and id_campaign=0;
		
	ELSE
		SELECT id, listgroup_name FROM uploads_group WHERE listgroup_type = var_listgroup_type AND iduser=var_iduser AND id_campaign=var_idcampaign
		UNION SELECT id, listgroup_name FROM uploads_group WHERE listgroup_type = var_listgroup_type AND iduser=var_iduser AND id_campaign=0;
	END IF;
	
	SELECT id, `name`, CONCAT('app.nexcon.com/messages/',filename) `filename` FROM `message` WHERE iduser=var_iduser;
	
	IF var_idcampaign=-1 THEN
		
		SELECT id, `name`, (SELECT COUNT(*) FROM did WHERE id_group=did_group.id) `dids` FROM `did_group`
		WHERE iduser=var_iduser AND  did_group.id  NOT IN ( SELECT didgroup FROM campaign);
	
	ELSE 
		SELECT id, `name`, (SELECT COUNT(*) FROM did WHERE id_group=did_group.id) `dids` FROM `did_group` WHERE iduser=var_iduser AND id= @dgroup_id UNION
		SELECT id, `name`, (SELECT COUNT(*) FROM did WHERE id_group=did_group.id) `dids` FROM `did_group`
		WHERE iduser=var_iduser AND  did_group.id  NOT IN ( SELECT didgroup FROM campaign);
	
	END IF;	
	
	IF var_idcampaign=-1 THEN
		
		SELECT id, group_name `name`, (SELECT COUNT(*) FROM agents WHERE agentgroupid=agents_group.id) `agents` FROM `agents_group` 
		WHERE iduser=var_iduser AND agents_group.id NOT IN (SELECT id_agentgroup FROM campaign);
		
	ELSE 
		SELECT id, group_name `name`, (SELECT COUNT(*) FROM agents WHERE agentgroupid=agents_group.id) `agents` FROM `agents_group` 
		WHERE iduser=var_iduser AND id=@agroup_id UNION 
		SELECT id, group_name `name`, (SELECT COUNT(*) FROM agents WHERE agentgroupid=agents_group.id) `agents` FROM `agents_group` 
		WHERE iduser=var_iduser AND agents_group.id NOT IN (SELECT id_agentgroup FROM campaign);
	
	END IF;
		
		SELECT `id`,`name`,`sms_template` FROM `sms_template` WHERE iduser=var_iduser;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CampaignDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CampaignDelete`(var_id INT(11))
BEGIN
	DELETE FROM campaign WHERE id = var_id ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CampaignDetail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CampaignDetail`(var_campaign_id int (11),var_iduser int(11),`type` tinyint(4))
BEGIN
	
if `type`= 0 then	
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='inbound' AND `iduser`= var_iduser GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='outbound' AND `iduser`=var_iduser GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE presence_data LIKE '%tf%' AND `iduser`=var_iduser GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE  direction = 'inbound' AND `iduser`=var_iduser GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
	
elseif `type`=1 then	
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='inbound' AND `iduser`= var_iduser AND DATE(start_stamp) = CURDATE() GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='outbound' AND `iduser`=var_iduser AND DATE(start_stamp) = CURDATE() GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE presence_data LIKE '%tf%' AND `iduser`=var_iduser AND DATE(start_stamp) = CURDATE() GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE  direction = 'inbound' AND `iduser`= var_iduser AND DATE(start_stamp) = CURDATE() GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
	
elseif `type`=2 then	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='inbound' AND `iduser`= var_iduser AND DATE(start_stamp) = CURDATE() - 1 GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='outbound' AND `iduser`= var_iduser AND DATE(start_stamp) = CURDATE() - 1 GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE presence_data LIKE '%tf%' AND `iduser`= var_iduser AND DATE(start_stamp) = CURDATE() - 1 GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE  direction = 'inbound' AND `iduser`= var_iduser AND DATE(start_stamp) = CURDATE() - 1 GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
elseif `type`=3 then	
	
		
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='inbound' AND `iduser`= var_iduser AND YEARWEEK(start_stamp) GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='outbound' AND `iduser`= var_iduser AND YEARWEEK(start_stamp) GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE presence_data LIKE '%tf%' AND `iduser`= var_iduser AND YEARWEEK(start_stamp)  GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE  direction = 'inbound' AND `iduser`= var_iduser AND YEARWEEK(start_stamp) GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
elseif `type`=4 then	
	
		
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='inbound' AND `iduser`= var_iduser AND MONTH(start_stamp) = MONTH(CURRENT_DATE()) GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`tried`, SEC_TO_TIME(SUM(billsec)) `talktime`, SEC_TO_TIME(SUM(billsec)/SUM(IF(billsec>0,1,0))) avg_talk
	FROM cdr_pd WHERE direction='outbound' AND `iduser`=var_iduser AND MONTH(start_stamp) = MONTH(CURRENT_DATE()) GROUP BY DATE_FORMAT(end_stamp, 
	'%d-%m-%Y %H')ORDER BY end_stamp DESC LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE presence_data LIKE '%tf%' AND `iduser`= var_iduser AND MONTH(start_stamp) = MONTH(CURRENT_DATE()) GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;
	
	
	
	SELECT (SELECT `name` FROM `campaign` WHERE id=cdr_pd.id_campaign) `campaign`, DATE_FORMAT(end_stamp, '%d-%m-%Y') `date`, DATE_FORMAT(end_stamp, '%H') `hour`, COUNT(*)
	`offered`, SUM(IF(billsec>0,1,0)) `connect`, SEC_TO_TIME(SUM(billsec)) `talktime`,
	SEC_TO_TIME(SUM(billsec) /SUM(IF(billsec>0,1,0))) avg_talk FROM cdr_pd 
	WHERE  direction = 'inbound' AND `iduser`= var_iduser AND MONTH(start_stamp) = MONTH(CURRENT_DATE())GROUP BY DATE_FORMAT(end_stamp, '%d-%m-%Y %H') LIMIT 1000 ;

end if ;
	
	
	
	
	
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CampaignSave` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CampaignSave`(var_user INT(11), var_camp_id INT(11), var_name VARCHAR(100), var_msg INT(11), var_amd_msg INT(20) , var_smstemplate INT(11),  var_call_per_hour INT(10), var_amd boolean , 
var_startdate DATE, var_enddate DATE, var_starthr VARCHAR(10), var_startmin VARCHAR(10), var_endhr VARCHAR(10), var_endmin VARCHAR(10), var_weekday VARCHAR(20), var_didgroup INT(11), var_agentgroup INT(11), var_lead_dialingtype TINYINT(2),
var_cam_type TINYINT(1), var_max_calls INT(10), var_leadgroup INT(11), var_action TEXT, var_disposition TEXT, var_desc TEXT,var_menufile VARCHAR(145))
BEGIN
	DECLARE str TEXT; 
	DECLARE inc INT(5) ; 
	DECLARE tmp INT(10);
	DECLARE campid INT(11);
	DECLARE id INT(11);
	DECLARE typ VARCHAR(100);
	DECLARE des VARCHAR(100);
	DECLARE lastid INT(11);
	
	IF var_camp_id>0 THEN
		
		
		
		
		SET @query1=CONCAT('UPDATE `campaign`SET `name`=\'',var_name,'\',amd_msg_key=\'',var_amd_msg,'\'
		,`sms_template`=\'',var_smstemplate,'\',`call_per_hour`=\'',var_call_per_hour,'\',`amd`=\'',var_amd,'\',`msg_key`=\'',var_msg,'\',
		`id_agentgroup`=\'',var_agentgroup,'\',`startdate`=\'',var_startdate,'\',`enddate`=\'',var_enddate,'\',`starthr`=\'',var_starthr,'\',`startmin`=\'',var_startmin,'\',`endhr`=\'',var_endhr,'\',
		`endmin`=\'',var_endmin,'\',`weekday`=\'',var_weekday,'\',`didgroup`=\'',var_didgroup,'\',`lead_dialingtype`=\'',var_lead_dialingtype,'\',
		`max_calls`=\'',var_max_calls,'\',`description`=\'',var_desc,'\',`leads_groupid`=\'',var_leadgroup,'\'
		where id=\'',var_camp_id,'\' and iduser=\'',var_user,'\'' );	
		PREPARE stmt FROM @query1;                                                                                                                  
		EXECUTE stmt;
		
		UPDATE `uploads_group` ug SET ug.id_campaign=0 WHERE ug.id_campaign=var_camp_id;
		UPDATE `uploads_group` ug SET ug.id_campaign=var_camp_id,ug.listgroup_type=var_cam_type WHERE ug.id=var_leadgroup;
		
		UPDATE `uploads` SET id_campaign=var_camp_id,upload_type=var_cam_type WHERE id_listgroup=var_leadgroup;
		DELETE FROM `action` WHERE id_campaign=var_camp_id;
		
		SET @str=var_action;
		SET @inc=1+LENGTH(@str)-LENGTH(REPLACE(@str, ',', ''));     
		
		WHILE (@inc>0) DO 
			SET @tmp=(SUBSTRING_INDEX(@str,',',-1));
			SET @id = (SUBSTRING_INDEX(@tmp,'-',1));
			SET @typ = (SUBSTRING_INDEX(SUBSTRING_INDEX(@tmp,'-',2),'-',-1));
			SET @des = (SUBSTRING_INDEX(@tmp,'-',-1));
			
			SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
			
			
			
			INSERT INTO `action` (id_campaign, `key`, `action`, description) VALUES (var_camp_id, @id, @typ, @des); 
			
			SET @inc=@inc-1;
		END WHILE;
		
		
		
		DELETE FROM `campaign_disposition` WHERE id_campaign=var_camp_id;
		SET @str=var_disposition;
		SET @inc=1+LENGTH(@str)-LENGTH(REPLACE(@str, ',', ''));     
		
		WHILE (@inc>0) DO 
			SET @tmp=(SUBSTRING_INDEX(@str,',',-1));
			INSERT INTO `campaign_disposition` (id_campaign, `id_disposition` , iduser) VALUES (var_camp_id, @tmp, var_user);
			
			SET @inc=@inc-1;
			SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
		END WHILE; 
	ELSE
		INSERT INTO `campaign` SET `iduser`=var_user, `name`=var_name, amd_msg_key=var_amd_msg, call_per_hour=var_call_per_hour, amd=var_amd, msg_key=var_msg, sms_template=var_smstemplate,
		id_agentgroup=var_agentgroup,startdate=var_startdate,enddate=var_enddate,starthr=var_starthr, startmin=var_startmin,endhr=var_endhr,
		endmin=var_endmin,`weekday`=var_weekday,didgroup=var_didgroup,lead_dialingtype=var_lead_dialingtype,cam_type=var_cam_type,
		max_calls=var_max_calls,description=var_desc,menu_file=var_menufile,leads_groupid=var_leadgroup;
		SET @campid = LAST_INSERT_ID();
		SET lastid = LAST_INSERT_ID();
		
		
		
		UPDATE `uploads` SET id_campaign=@campid, upload_type=var_cam_type WHERE id_listgroup=var_leadgroup;
		SET @query1=CONCAT('UPDATE `uploads_group` SET `id_campaign`=',@campid,', listgroup_type=',var_cam_type,' WHERE `id`=',var_leadgroup);
		PREPARE stmt FROM @query1;    
		
		EXECUTE stmt;
		SET @str=var_action;
		SET @inc=1+LENGTH(@str)-LENGTH(REPLACE(@str, ',', ''));     
				
		WHILE (@inc>0) DO 
			SET @tmp=(SUBSTRING_INDEX(@str,',',-1));
			SET @id = (SUBSTRING_INDEX(@tmp,'-',1));
			SET @typ = (SUBSTRING_INDEX(SUBSTRING_INDEX(@tmp,'-',2),'-',-1));
			SET @des = (SUBSTRING_INDEX(@tmp,'-',-1));
			
			SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
			
			
			
			INSERT INTO `action` (id_campaign, `key`, `action`, description) VALUES (@campid, @id, @typ, @des); 
			
			SET @inc=@inc-1;
		END WHILE; 
	
		SET @str=var_disposition;
		SET @inc=1+LENGTH(@str)-LENGTH(REPLACE(@str, ',', ''));
		
		WHILE (@inc>0) DO 
			SET @tmp=(SUBSTRING_INDEX(@str,',',-1));
			INSERT INTO `campaign_disposition` (id_campaign, `id_disposition`, iduser) VALUES (lastid , @tmp , var_user);
			
			SET @inc=@inc-1;
			SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
		END WHILE;  
			
			SELECT lastid ; 	
		
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `check_dispo` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `check_dispo`(`default` tinyint(2))
BEGIN	
	
if `default`= -1 then 
	
	drop table if exists temp_dispo;
	
	create temporary table temp_dispo (default_disposition varchar (255));
	INSERT INTO temp_dispo (default_disposition) value ('Do Not Call');
	INSERT INTO temp_dispo (default_disposition) VALUE ('Busy');
	INSERT INTO temp_dispo (default_disposition) VALUE ('Appointment scheduled');
	INSERT INTO temp_dispo (default_disposition) VALUE ('Callback');
	INSERT INTO temp_dispo (default_disposition) VALUE ('Abandoned in queue');
	INSERT INTO temp_dispo (default_disposition) VALUE ('Not interested');
	select default_disposition from temp_dispo;

end if ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ContactDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ContactDetails`(var_leadsgroup_id INT(11), var_id_user INT(11) )
BEGIN
	
	SELECT u.`id`, u.`name`, u.`leads`, u.`dialed`, u.`answered`, u.`noanswer`, u.`failed`  
	FROM `uploads` u  
	WHERE id_listgroup=var_leadsgroup_id;
	
	
	SELECT caller_id_number, direction  
	FROM `uploads_group` ug JOIN `uploads` u
	ON ug.id=u.id
	JOIN cdr_pd cp
	ON cp.upload_id=u.id
	WHERE ug.id=var_leadsgroup_id AND ug.iduser=var_id_user AND direction='outbound';
	
	
	SELECT caller_id_number, direction  
	FROM `uploads_group` ug JOIN `uploads` u
	ON ug.id=u.id
	JOIN cdr_pd cp
	ON cp.upload_id=u.id
	WHERE ug.id=var_leadsgroup_id AND ug.iduser=var_id_user AND direction='inbound';
	
	
	SELECT lead.phone1 `destination`, CONCAT(lead.first_name,' ', lead.last_name) `name`, lead.address1, 
	cdr.billsec `total_duration`, DATE_FORMAT(cdr.end_stamp,'%d-%m-%Y') `date`,DATE_FORMAT(cdr.start_stamp,'%H:%i:%s') `start`, 
	DATE_FORMAT(cdr.end_stamp,'%H:%i:%s') `end`, cdr.caller_id_number `source`, last_arg `queue`, hangup_cause `hangup` 
	FROM cdr_pd cdr LEFT JOIN leads_pd lead ON cdr.lead_id=lead.id_upload
	WHERE cdr.`iduser`=var_id_user AND 1 AND cdr.direction='inbound' AND billsec>0 AND cdr.current_application='callcenter' 
	ORDER BY cdr.`end_stamp` DESC LIMIT 1000;
	
	
	
	
	END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `crm_label` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `crm_label`( var_id int (11))
BEGIN
	declare id_agent int;
	SELECT iduser FROM agents WHERE id=var_id INTO @id_agent;
	SELECT crm_label FROM crm_label WHERE iduser=@id_agent;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteDNC_List` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteDNC_List`(var_dnc_id int(11))
BEGIN
	
	declare lstid int(11);
	SELECT `list_id` FROM `dnc` WHERE `dnc_id`= var_dnc_id into @lstid;
	
	DELETE FROM `dnc` WHERE `dnc_id`= var_dnc_id;
	UPDATE `dnc_lists` SET `dnc_count`= (`dnc_count`-1) WHERE dnclist_id = @lstid;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteMsg` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteMsg`(var_iduser int(11), var_id int(11))
BEGIN
	DELETE  from `message`  WHERE `iduser`= var_iduser AND `id`= var_id ; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DeletePackages` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeletePackages`(var_id int(11), var_iduser int(11))
BEGIN
	delete from `package` where id=var_id and iduser=var_iduser;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteSms_Template` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteSms_Template`( var_iduser INT(11), var_id int(11))
BEGIN
	delete from sms_template where id = var_id and iduser = var_iduser ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteUserManagement` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteUserManagement`(var_id int(11), var_type tinyint(1), var_idparent int(11))
BEGIN
	DELETE FROM `users` WHERE `id`=var_id AND `type`=var_type AND `idparent`=var_idparent ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DidAdd` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DidAdd`(var_didgroup_id INT(11), var_iduser INT(11), var_number TEXT, var_name VARCHAR(40))
BEGIN
	DECLARE groupname VARCHAR(200);
	DECLARE lastinsertid INT(11);
	DECLARE tmp VARCHAR(10);
	DECLARE inc INT DEFAULT 0;
	DECLARE str TEXT;
	DECLARE num VARCHAR(40);
	DECLARE cd VARCHAR(10);
	DECLARE stat VARCHAR(3);
	DECLARE cntry VARCHAR(20);
	
	
	
	
		SET @str=var_number;
		SET @inc=1+LENGTH(REPLACE(@str, ',', ''));
		WHILE (@inc>=0) DO 
			SET @tmp = (SUBSTRING_INDEX(@str,',',-1));
			SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
			
			
			SELECT SUBSTR(@tmp,2,3) us_code, u.state, u.country FROM did d JOIN us_states u ON SUBSTR(@tmp,2,3)=u.code 
			WHERE iduser=var_iduser LIMIT 1 INTO @cd, @stat, @cntry; 
			IF(@tmp>0) THEN                                              
				INSERT INTO did (iduser, id_group, country, state, areacode, number) VALUES (var_iduser, var_didgroup_id, @cntry, @stat, @cd, @tmp); 
			END IF;   
			SET @inc=@inc-1;
			
		END WHILE;
		
		INSERT INTO did_group (iduser, `name`, created) VALUE (var_iduser, var_name, NOW());
		
		SELECT g.id, g.`name`, g.created, COUNT(grel.id_did) AS `total_number` 
		FROM `did_group` g LEFT JOIN `didgroup_mapping_did` grel
		ON g.id=grel.id_groupdid 
		WHERE g.`id`=@lastinsertid AND iduser=var_iduser GROUP BY g.id ORDER BY g.id DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DidDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DidDelete`(var_id int(11), var_iduser int(11))
BEGIN
	delete from did where id = var_id and iduser = var_iduser ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DidEdit` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DidEdit`(var_iduser int(11),var_name varchar (145))
BEGIN
	update did_group set `name` = var_name  where iduser= var_iduser; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DidFileUpload` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DidFileUpload`(var_iduser INT(11), var_id_listgroup INT(11), var_name VARCHAR(255), var_filename VARCHAR(255),
var_filepath VARCHAR(255))
BEGIN
	iNSERT INTO uploads SET iduser = var_iduser, leads = 0, id_listgroup = var_id_listgroup, 
	`name` = var_name, filename = var_filename, filepath = var_filepath ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DidGroupDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DidGroupDelete`(var_id INT(11),var_iduser INT(11))
BEGIN
	DELETE FROM did_group WHERE id = var_id AND iduser = var_iduser ;
	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DidGroupDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DidGroupDetails`(var_id_didgroup INT(11), var_id_user INT(11))
BEGIN

		
		SELECT id_group , (SELECT `name` FROM did_group WHERE id= var_id_didgroup)`group_name`,(SELECT created FROM did_group WHERE id=var_id_didgroup)`date` , COUNT(number) total_did ,
		(SELECT COUNT(number) FROM did WHERE didtype=1) `purchased `, ( SELECT COUNT(number) FROM did WHERE didtype=2) `uploaded`,FLOOR(7 + (RAND() * 10000)) `answered` FROM did 
		WHERE iduser=var_id_user AND id_group =var_id_didgroup;
		
		
		SELECT `state`,`number`,`areacode`, country ,CASE 
		WHEN didtype='1' THEN 'Uploaded' WHEN didtype='2' THEN 'Purchased' END AS `didtype`, created_date FROM did WHERE iduser=var_id_user and id_group=var_id_didgroup;
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DidGroupEdit` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DidGroupEdit`(var_id int(11),var_name varchar (145))
BEGIN
	update did_group set `name` = var_name  where id = var_id ; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `did_group` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `did_group`(var_didGroupid INT(11), var_iduser INT(11))
BEGIN
	declare grp_id int(11);
	declare grp_name int(11);
	declare cnt int(10);
	
	# To get the all DID groups
	iF var_didGroupid=-1 THEN
		SELECT dg.id, `name`, COUNT(*) `did` FROM did_group dg
		LEFT JOIN did d ON dg.id=d.id_group
		WHERE dg.iduser=var_iduser
		GROUP BY dg.id;
		
		SELECT id, `state`,  `number`, `areacode`, `country`, case WHEN didtype='1' THEN 'Uploaded' WHEN didtype='2' THEN 'Purchased' END AS `didtype` FROM did
		WHERE id_group=(SELECT id FROM did_group WHERE iduser=var_iduser LIMIT 1) AND iduser=var_iduser;
		
	# To get the specific DID group + It's DIDs
	else			
		SELECT dg.id, `name`, COUNT(number) total_numbers FROM did_group dg
		LEFT JOIN did d ON dg.id=d.id_group
		WHERE dg.iduser=var_iduser
		GROUP BY dg.id;

		#SELECT `name` FROM did_group WHERE iduser=var_iduser and id=var_didGroupid into @grp_name;
		#select id from did_group where iduser=var_iduser AND `name`=@grp_name  into @grp_id;
		
		SELECT id ,`did_groupname`, `state`,  `number`, `areacode`, `country`, case 
		when didtype='1' then 'Uploaded' when didtype='2' then 'Purchased' end as `didtype`
		FROM did WHERE id_group=var_didGroupid AND iduser=var_iduser;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `Disposition_Label` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Disposition_Label`(var_id_agent int(11))
BEGIN		
	declare campid int;

	declare agentgroupid int;
	SELECT a.agentgroupid FROM agents `a` WHERE id = var_id_agent INTO @agentgroupid;
	
	
	
	SELECT c.id FROM campaign `c` WHERE id_agentgroup = @agentgroupid INTO @campid;	
	
	
	SELECT DISTINCT cust.id, cust.label, cust.abbr, cust.color FROM custom_disposition `cust` 
	inner JOIN campaign_disposition `camp` ON camp.iduser=cust.iduser
	WHERE id_campaign  = @campid;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetCampaign` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCampaign`(var_iduser INT(11), var_idcampaign INT(11) )
BEGIN
	IF var_idcampaign=-1 THEN
		
		SELECT `ca`.`id`, ca.cam_type `campaign_type`, `ca`.`name`, `ca`.`transfer_key`, `ca`.`primary_transfer`, `ca`.`backup_transfer`, `ca`.`msg_key`, `ca`.`amd_msg_key`, 
		`ca`.`call_per_hour`, `ca`.`amd`, `ca`.`voice_call`,DATE_FORMAT(`startdate`,"%Y/%m/%d")`startdate`,DATE_FORMAT(`enddate`,"%Y/%m/%d")`enddate`, `ca`.`starthr`, `ca`.`startmin`, `ca`.`endhr`,
		`ca`.`endmin`, ca.weekday, (SELECT `filename` FROM `message` WHERE `message`.id=ca.msg_key) `mainaudio`,  (SELECT GROUP_CONCAT(`description`) FROM `action` WHERE `action`.id_campaign=ca.id GROUP BY action.id_campaign) `agentgroup`,
		(SELECT `name` FROM `did_group` WHERE   ca.`didgroup`=did_group.`id`) `namedidgrp`, IFNULL(SUM(up.leads),0) leads, IFNULL(SUM(up.dialed),0) dialed, 
		IFNULL(SUM(up.answered),0) answered, IFNULL(SUM(up.noanswer),0) noanswer, IFNULL(SUM(up.failed),0) failed, IFNULL(SUM(up.minutes),0) minutes,
		IFNULL(SUM(up.total_transfer),0) total_transfer, CASE WHEN ca.is_status=1 THEN 'Running' WHEN ca.is_status=2 THEN 'Completed' WHEN ca.is_status=3 
		THEN 'Scheduled' ELSE 'Paused' END AS `campaign_status`, (
			select count(a.id) from `agents` a
			INNER JOIN `agents_group` b ON b.id=a.agentgroupid
			where a.`status`='Available' and b.id=ca.id_agentgroup
		) active_agents FROM `campaign` `ca`
		LEFT JOIN  `uploads` up ON ca.id=up.id_campaign
		WHERE `ca`.`iduser`= var_iduser
		GROUP BY `ca`.`id`
		ORDER BY ca.id DESC ;
		
	ELSE
		SELECT `ca`.`id`, `ca`.`name`, `ca`.`cam_type`, `ca`.`transfer_key`, `ca`.`primary_transfer`, `ca`.`backup_transfer`, `ca`.`msg_key`, `ca`.`amd_msg_key`,
		`ca`.`call_per_hour`, `ca`.`amd`, `ca`.`voice_call`,DATE_FORMAT(`startdate`,"%Y-%m-%d")`startdate`,DATE_FORMAT(`enddate`,"%Y-%m-%d")`enddate`, `ca`.`starthr`, `ca`.`startmin`, `ca`.`endhr`,
		`ca`.`endmin`, ca.weekday, `ca`.`description`, ca.lead_dialingtype , ca.didgroup, ca.sms_template,ca.max_calls,(SELECT `filename` FROM `message` WHERE `message`.id=ca.msg_key) `mainaudio`,
		(SELECT `name` FROM `did_group` WHERE   ca.`didgroup`=did_group.`id`) `namedidgrp`, IFNULL(SUM(up.leads),0) leads, IFNULL(SUM(up.dialed),0) dialed, 
		IFNULL(SUM(up.answered),0) answered, IFNULL(SUM(up.noanswer),0) noanswer, IFNULL(SUM(up.failed),0) failed, IFNULL(SUM(up.minutes),0) minutes,
		IFNULL(SUM(up.total_transfer),0) total_transfer, CASE WHEN ca.is_status=1 THEN 'Running' WHEN ca.is_status=2 THEN 'Completed' WHEN ca.is_status=3 
		THEN 'Scheduled' ELSE 'Paused' END AS `campaign_status`, ca.id_agentgroup `agentgroup`,
		(SELECT id FROM uploads_group WHERE id_campaign=var_idcampaign) `leadgroup_id`,
		(SELECT `name` FROM `sms_template` `s` WHERE `s`.id=`ca`.sms_template)`smsnametemp` , (SELECT `id` FROM `message` `m` WHERE `m`.`id`=`ca`.`amd_msg_key`) amd_msg_key ,
		(SELECT `id` FROM `message` `m` WHERE `m`.`id`=`ca`.`msg_key`) `msg_key`,(SELECT (SELECT CONCAT('{"menus":[', GROUP_CONCAT('{"id_campaign":"', a.`id_campaign`,'","keys":"', 
		a.`key`,'", "action":"', a.`action`, '", "values":"', a.`description`,'"}' ORDER BY FIELD (a.key,'1','2','3','4','5','6','7','8','9','0','*','#'),'"}') ,']}' ) 
		FROM `action` a WHERE id_campaign=var_idcampaign))`key_setting`,(SELECT CONCAT( GROUP_CONCAT('{"id_campaign":"', `id_campaign`,'","id_disposition":"', `id_disposition`,'"}')) 
		FROM `campaign_disposition` WHERE id_campaign=var_idcampaign) `disposition`, (
			SELECT COUNT(a.id) FROM `agents` a
			INNER JOIN `agents_group` b ON b.id=a.agentgroupid
			WHERE a.`status`='Available' AND b.id=ca.id_agentgroup
		) active_agents
		FROM `campaign` `ca` 
		LEFT JOIN  `uploads` up ON ca.id=up.id_campaign
		WHERE `ca`.`iduser`= var_iduser AND ca.id=var_idcampaign
		GROUP BY `ca`.`id` ORDER BY ca.id DESC;
		
		SELECT c.id, c.label, c.abbr, c.color, c.status, c.check_box, IF(cd.id IS NULL, 0, 1) `status` FROM custom_disposition c
		LEFT JOIN campaign_disposition cd ON c.id=cd.id_disposition 
		WHERE c.iduser=var_iduser AND cd.id_campaign=var_idcampaign;
	END IF;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetHeaderData` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetHeaderData`(var_leadid_upload INT(11))
BEGIN
	SELECT IFNULL(mapping_header,'0') `mapping_header` FROM uploads_group WHERE id=var_leadid_upload;
	select IFNULL(`crm_label`,'0') `crm_label` from `crm_label` where iduser=(select iduser from uploads_group WHERE id=var_leadid_upload);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSinglePackage` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSinglePackage`(var_id int(11), var_iduser int(11))
BEGIN
	select*from `package` where id=var_id and iduser=var_iduser ;
	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetSingleUser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSingleUser`(var_id int(11))
BEGIN
	SELECT  `id`, `idparent`, `fullname`, `username`, `password`, `company`, `contact`, `email`, `channels`, `balance`, 
	`billing_rate`, `type`, `package_id`, `threshold_balance` FROM `users` WHERE id=var_id ;
												

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetUsers` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUsers`(var_type tinyint(1), var_idparent int(11))
BEGIN
	SELECT `id`, `idparent`, `fullname`, `username`, `password`, `company`, `contact`, `email`, `channels`, `balance`, 
	`billing_rate`, `type`, `package_id`, `threshold_balance` FROM `users` WHERE `type`=var_type AND `idparent`=var_idparent ORDER BY id DESC;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GroupDetail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GroupDetail`(var_agentgroupid int(11))
BEGIN
	select count(t.idagent) Total_Agent, sum(talk_time) talk_time, sum(if(last_offered_call,1,0)) last_offered_call, 
		sum(if(calls_answered,1,0)) calls_answered, 
		(UNIX_TIMESTAMP(MAX(a.lastlogin))-UNIX_TIMESTAMP(MIN(a.lastlogin))) login_dur
	from `agents_group` ag left join  tiers t
	on ag.iduser=t.iduser
	right join agents a
	on t.idagent=a.id
	where ag.id=var_agentgroupid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertDisposition` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertDisposition`(var_iduser int(11), var_indexid int(11), var_label varchar(255), var_abbr varchar(100),
					   var_insert_cal tinyint(1), var_email_client tinyint(1), var_email_agent tinyint(1), var_voice_drop tinyint(1),
					   var_voicemsg int(11), var_emailtmp_agent int(11),var_emailtmp_client int(11), var_sms tinyint(1), var_smstxt tinyint(4), var_note tinyint(1),
					   var_notetxt text, var_callback tinyint(1), var_color varchar(50), var_workflow tinyint(1), var_workflowtxt int(11),
					   var_callqueue tinyint(1), var_callqueuetxt int(11), var_status tinyint(1), var_calevent tinyint(1), 
					   var_emailrem_agent int(11),var_emailrem_client int(11), var_smstxtrem int(11), var_voicemsg_rem int(1), var_event_title varchar(11),
					   var_client_duration int(1), var_client_duration_type varchar(11), var_agent_duration int(1), 
					   var_agent_duration_type varchar(11),var_text_duration int(1), var_text_duration_type varchar(11),
					   var_vm_duration int(1),var_vm_duration_type varchar(11),var_transfer int(11),var_number varchar(11),var_description varchar(200),var_check_box varchar(40)
					   ,`i` tinyint(2), var_is_sale varchar(10))
BEGIN

	if `i`=0 then
	DELETE FROM `custom_disposition` WHERE  `iduser`= var_iduser ;
	end if;
	
	INSERT INTO `custom_disposition`(`iduser`,`indexid`,`label`,`abbr`,`insert_cal`,`email_client`,`email_agent`,`voice_drop`,
	`voicemsg`,`emailtmp_agent`, `emailtmp_client`, `sms`, `smstxt`, `note`, `notetxt`, `callback`, `color`, `workflow`, `workflowtxt`, `callqueue`,
	`callqueuetxt`, `status`, `calevent`, `emailrem_agent`,`emailrem_client`,`smstxtrem`, `voicemsg_rem`, `event_title`, `client_duration`,
	`client_duration_type`, `agent_duration`, `agent_duration_type`, `text_duration`, `text_duration_type`, `vm_duration`, `vm_duration_type`,
	`transfer`, `number`, description, check_box, is_sale) 
	VALUES (var_iduser, var_indexid, var_label, var_abbr, var_insert_cal, var_email_client, var_email_agent, var_voice_drop,
		var_voicemsg, var_emailtmp_agent, var_emailtmp_client, var_sms, var_smstxt, var_note, var_notetxt , var_callback, var_color, var_workflow, var_workflowtxt,
		var_callqueue, var_callqueuetxt, var_status, var_calevent, var_emailrem_agent, var_emailrem_client, var_smstxtrem, var_voicemsg_rem, var_event_title,
		var_client_duration, var_client_duration_type, var_agent_duration, var_agent_duration_type,var_text_duration, var_text_duration_type, var_vm_duration,
		var_vm_duration_type, var_transfer,var_number,var_description, var_check_box, var_is_sale);
	
	SELECT `id`, `iduser`, `indexid`, `label`, `abbr`, `insert_cal`, `email_client`, `email_agent`, `voice_drop`, `voicemsg`,
	`emailtmp_agent`, `emailtmp_client`, `sms`, `smstxt`, `note`, `notetxt`, `callback`, `color`, `workflow`, `workflowtxt`, `callqueue`, `callqueuetxt`,
	`status`, `calevent`, `emailrem_agent`, `emailrem_client`, `smstxtrem`, `voicemsg_rem`, `event_title`, `client_duration`, `client_duration_type`,
	`agent_duration`, `agent_duration_type`, `text_duration`, `text_duration_type`, `vm_duration`, `vm_duration_type`, `transfer`, `number`, description
	 FROM `custom_disposition` WHERE `iduser`= var_iduser;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertDncNumber` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertDncNumber`(var_group_id int(11), var_iduser int(11), var_number varchar(15))
BEGIN
	
	
	INSERT INTO `dnc`(`list_id`,`iduser`,`number`,`created`) VALUES ( var_group_id, var_iduser, var_number , now() ) ;
	
	UPDATE `dnc_lists` SET `dnc_count`=(`dnc_count`+ 1 ) WHERE `dnclist_id`= var_group_id ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertUserManagement` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertUserManagement`(var_iduser int(11),var_type TINYINT(1),var_fullname varchar(25), var_username varchar(100), var_password varchar(100),
var_company varchar(100), var_contact varchar(12), var_email varchar(145), var_channels int(11), var_billing_rate double(12,6), var_package INT(11))
BEGIN
	DECLARE lastid INT(11);
	
	INSERT INTO `users` SET `idparent`=var_iduser, `fullname`=var_fullname, `username`=var_username, `password`=var_password, `company`=var_company,`contact`=var_contact,`email`=var_email,`channels`=var_channels ,`billing_rate`=var_billing_rate,`type`=var_type,`package_id`=var_package;
	SET lastid = LAST_INSERT_ID();
	INSERT INTO `user_minutes` (`iduser`, `rvm`, `ib`, `ob`, `sms`, `agent`, `did_credit`) VALUES (lastid, '10000', '10000', '10000', '10000', '10000', '10000'); 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `Insert_smstemplate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Insert_smstemplate`(var_id INT(11), var_iduser int(11), var_name varchar(255), var_sms_template text)
BEGIN

	if var_id=-1 then
		insert into sms_template set iduser = var_iduser, `name` = var_name, sms_template = var_sms_template ;
	else
		update sms_template SET iduser = var_iduser, `name` = var_name, sms_template = var_sms_template where id=var_id;
	end if;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadDelete`( var_id INT(11), var_grp_id INT(11),var_iduser int(11))
BEGIN
	DELETE FROM `uploads` WHERE id = var_id AND id_listgroup = var_grp_id and iduser=var_iduser;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadFileUpload` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadFileUpload`(var_iduser INT(11), var_id_listgroup INT(11), var_name VARCHAR(255), var_filename VARCHAR(255), var_listgroup_type int(10), 
var_filepath VARCHAR(255))
BEGIN

	DECLARE id INT(11);
	
	INSERT INTO uploads SET iduser = var_iduser, leads = 0, id_listgroup = var_id_listgroup, upload_type=var_listgroup_type,
	`name` = var_name, filename = var_filename, filepath = var_filepath ;
	
	SET id = LAST_INSERT_ID();
	SELECT id;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadGroupAdd` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadGroupAdd`(var_iduser int(11), var_listgroup_name varchar(255), var_listgroup_type tinyint(1))
BEGIN
	INSERT INTO `uploads_group`(`iduser`,`listgroup_name`,`listgroup_type`,`added_at`) 
	VALUES( var_iduser,var_listgroup_name,var_listgroup_type,NOW());

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadGroupDelete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadGroupDelete`(var_id int(11), var_iduser int(11) )
BEGIN
	delete from uploads_group where id = var_id and iduser = var_iduser ;
	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadGroupEdit` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadGroupEdit`(var_id int(11), var_listgroup_type tinyint(1), var_listgroup_name varchar(255) )
BEGIN
	update `uploads_group` set listgroup_name = var_listgroup_name , listgroup_type = var_listgroup_type where id = var_id ; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadGroupList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadGroupList`(var_iduser varchar(11))
BEGIN
	SELECT ug.id, u.id as id_upload, ug.listgroup_name, ug.listgroup_type,
	CASE
		WHEN ug.`listgroup_type` ='1'  THEN 'SMS' WHEN ug.`listgroup_type`='2'
		THEN 'VB' WHEN ug.`listgroup_type`='3' THEN 'RVM' WHEN ug.`listgroup_type`='4' 
		THEN 'PD' ELSE 'NA'  END AS `type`, COUNT(u.id_listgroup) total_list, ug.is_status 
		FROM uploads_group ug LEFT JOIN uploads u ON ug.id=u.id_listgroup AND ug.iduser=u.iduser 
		WHERE ug.iduser = var_iduser GROUP BY ug.id ;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadList`(var_grp_id INT(11), var_id_user INT (11))
BEGIN
	SELECT u.id,`name`, leads, dialed, answered, noanswer, total_transfer, failed 
	FROM uploads u LEFT JOIN uploads_group ug 
	ON ug.id = u.id_listgroup
	WHERE u.id_listgroup=var_grp_id AND ug.iduser = var_id_user ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `leads_group` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `leads_group`(var_id INT(11), var_iduser INT(11), var_listgroup_name VARCHAR(200))
BEGIN
		IF var_id=-1 THEN
			SELECT ug.id, u.id AS id_upload, ug.listgroup_name, ug.listgroup_type, 
			CASE WHEN ug.`listgroup_type`='1' THEN 'VB' WHEN ug.`listgroup_type`='2' THEN 'SMS' 
			WHEN ug.`listgroup_type`='3' THEN 'RVM' WHEN ug.`listgroup_type`='4' THEN 'PD' ELSE 'NA'  END AS `type`, 
			COUNT(u.id_listgroup) total_list, ug.is_status 
			FROM uploads_group ug LEFT JOIN uploads u ON ug.id=u.id_listgroup AND ug.iduser=u.iduser WHERE ug.iduser=var_iduser GROUP BY ug.id;
			
			SELECT u.id, u.`name`,u.leads, u.dialed, u.answered, u.noanswer, u.total_transfer
			FROM uploads_group ug LEFT JOIN uploads u ON ug.id=u.id_listgroup AND ug.iduser=u.iduser 
			WHERE ug.listgroup_name=var_listgroup_name GROUP BY ug.id;
		ELSE
			SELECT u.id, u.`name`,u.leads, u.dialed, u.answered, u.noanswer, u.total_transfer
			FROM uploads_group ug LEFT JOIN uploads u ON ug.id=u.id_listgroup AND ug.iduser=u.iduser 
			WHERE ug.listgroup_name=var_listgroup_name GROUP BY ug.id;
		END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `LeadUpdateSelect` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `LeadUpdateSelect`(var_lead_id INT(11),var_first_name VARCHAR(40),var_last_name VARCHAR(40),var_birth_date DATE, var_age int(11), var_street_address_1 VARCHAR(40),var_street_address_2 VARCHAR(40),var_city VARCHAR(40),var_state VARCHAR(40),var_country VARCHAR(40), var_zip VARCHAR(40), var_phone VARCHAR(40), var_cell_phone VARCHAR(40), var_work_phone VARCHAR(40), var_email VARCHAR(40),var_height VARCHAR(40),
var_weight INT(11), var_occupation VARCHAR(40),var_smoker VARCHAR(40), var_assign_date DATE, var_sales_rep_name VARCHAR(40), var_spouse_full_name VARCHAR(40), var_spouse_age VARCHAR(40), var_spouse_birth_date DATE,var_spouse_smoker VARCHAR(40), var_spouse_height INT(11), var_spouse_weight INT(11), var_spouse_occupation VARCHAR(40),var_purchase_refi VARCHAR(40),
var_home_value VARCHAR(40),var_health_issue VARCHAR(40), var_spouse_health_issue VARCHAR(40), var_lender VARCHAR(40), var_beneficiary VARCHAR(40), var_mortgage_term VARCHAR(40), var_mortgage_amount VARCHAR(40), var_monthly_mortage_payment VARCHAR(40),var_referrer VARCHAR(40),var_field1 VARCHAR(40), var_field2 VARCHAR(40),var_field3 VARCHAR(40),var_field4 VARCHAR(40),
var_field5 VARCHAR(40),var_field6 VARCHAR(40))
BEGIN
	
	UPDATE leads SET `first_name`=var_first_name,`last_name`=var_last_name,`birth_date`=var_birth_date, age=var_age,street_address_1=var_street_address_1, street_address_2=var_street_address_2, city=var_city, state=var_state, country=var_country, zip=var_zip, phone=var_phone, cell_phone=var_cell_phone , work_phone=var_work_phone , email=var_email, height=var_height,
		weight=var_weight, occupation=var_occupation,
		smoker=var_smoker, assign_date=var_assign_date , salse_rep_name=var_sales_rep_name, spouse_full_name=var_spouse_full_name, spouse_age=var_spouse_age, spouse_birth_date=var_spouse_birth_date, spouse_smoker=var_spouse_smoker, spouse_height=var_spouse_height, spouse_weight=var_spouse_weight, spouse_occupation=var_spouse_occupation,purchase_refi=var_purchase_refi,
		home_value=var_home_value, health_issue=var_health_issue, spouse_health_issue=var_spouse_health_issue, lender=var_lender, beneficiary=var_beneficiary, mortgage_term=var_mortgage_term, mortgage_amount=var_mortgage_amount , monthly_mortage_payment=var_monthly_mortage_payment, referrer=var_referrer,field1=var_field1, field2=var_field2,field3=var_field3,field4=var_field4,
		field5=var_field5,field6=var_field6  WHERE id=var_lead_id ; 
		
		

	
	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `listgroup` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `listgroup`()
BEGIN
	
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE ab VARCHAR(15);
	DECLARE i INT DEFAULT 0;

	
	DECLARE checkk CURSOR FOR
		SELECT id FROM uploads WHERE id_listgroup=4;
	
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        OPEN checkk;
		
	read_loop: LOOP
		FETCH checkk INTO ab;
	IF finished = 1 THEN 
		LEAVE read_loop;
	END IF;
		SELECT id, id_campaign, iduser FROM uploads WHERE id=ab;
 
	END LOOP;
	CLOSE checkk;
	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `makeDID` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `makeDID`(var_upload INT(11))
BEGIN
	DECLARE `var_iduser` INT DEFAULT 0;
	DECLARE `var_idcampaign` INT DEFAULT 0;
	DECLARE `dcall_leads` INT DEFAULT 0;
	DECLARE `dleads` INT DEFAULT 0;
	DROP TABLE IF EXISTS `tempstates`;
	DROP TABLE IF EXISTS `tempnum`;
	DELETE FROM did WHERE idupload=var_upload;
	DROP TABLE IF EXISTS `tempDID`;
	CREATE TEMPORARY TABLE `tempDID` (`phone` VARCHAR(15) DEFAULT '', `state` VARCHAR(5) DEFAULT '',`status` INT(3) DEFAULT '-1', UNIQUE KEY `phone` (`phone`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
	CREATE TEMPORARY TABLE `tempnum` (`num` VARCHAR(5) DEFAULT '', `state` VARCHAR(5) DEFAULT '', UNIQUE KEY `num` (`num`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
	CREATE TEMPORARY TABLE `tempstates` (`state` VARCHAR(15) DEFAULT '',`status` INT(3) DEFAULT '0', UNIQUE KEY `state` (`state`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
	INSERT HIGH_PRIORITY IGNORE INTO `tempstates` (`state`) (SELECT `state` FROM leads WHERE idupload=var_upload GROUP BY state);
	SELECT * FROM tempstates;
	SELECT iduser, id_campaign FROM uploads WHERE id=var_upload INTO @var_iduser, @var_idcampaign;
	SELECT COUNT(*) FROM `tempstates` WHERE `status`=0 AND state IS NOT NULL INTO @var_states;
	
	SET @i=0;
	WHILE (@i<@var_states) DO
		SELECT `state` FROM `tempstates` WHERE `status`=0 AND state IS NOT NULL LIMIT 1 INTO @var_tmpstate;
		UPDATE `tempstates` SET `status`=1 WHERE `state`=@var_tmpstate; 
		SET @i=@i+1;
	END WHILE;
	
	SET @i=0;
	WHILE(@i<200) DO
		INSERT HIGH_PRIORITY IGNORE INTO `tempnum` (`num`, `state`) VALUE (LPAD(FLOOR(RAND()*1000),3,'0'),'A');
		SET @i=@i+1;
	END WHILE;
	
	SET @i=0;
	WHILE (@i<@var_states) DO
		SELECT `state` FROM `tempstates` WHERE `status`=1 AND state IS NOT NULL LIMIT 1 INTO @var_tmpstate;
		UPDATE `tempnum` SET `state`=@var_tmpstate;
		SELECT COUNT(*) FROM `leads` WHERE idupload=var_upload AND state=@var_tmpstate INTO @var_leads;
		SET @var_ndids=CEIL(@var_leads/1000)*5;	
		IF @var_ndids<500 THEN
			SET @var_ndids=500;
		END IF;
		SET @query2=CONCAT('INSERT HIGH_PRIORITY IGNORE INTO  `tempDID` (`phone`, `state`, `status`) (SELECT CONCAT(t1.`dialcode`,t3.`num`)  
		`callerid`, t1.state, 1 `status` FROM (select * from usdialcode where state=\'',@var_tmpstate,'\') t1 JOIN `tempnum` t3 ON 
		t1.state=t3.state ORDER BY RAND() LIMIT ', CEIL(@var_ndids),');');
		
		PREPARE stmt2 FROM @query2;
		EXECUTE stmt2;
		UPDATE `tempstates` SET `status`=2 WHERE `state`=@var_tmpstate; 
		SET @i=@i+1;
	END WHILE;
	
	IF @var_idcampaign=0 THEN
		INSERT INTO did(`iduser`, `idupload`, `state`, `areacode`, `number`) (SELECT @var_iduser, var_upload, `state`, SUBSTR(phone,1,3), `phone` 
		FROM `tempDID`);
	ELSE
		INSERT INTO did(`iduser`, `idcampaign`, `idupload`, `state`, `areacode`, `number`) 
		(SELECT @var_iduser, @var_idcampaign, var_upload, `state`, SUBSTR(phone,1,3), `phone` FROM `tempDID`);
	END IF;
	
	SELECT COUNT(*) FROM call_leads WHERE idcampaign=@var_idcampaign INTO @dcall_leads;
	
	IF @dcall_leads>0 THEN
		UPDATE call_leads SET idcampaign=@var_idcampaign WHERE idupload=var_upload;
	END IF;
	
	SELECT COUNT(*) FROM leads WHERE idcampaign=@var_idcampaign INTO @dleads;
	
	IF @dleads>0 THEN
		UPDATE leads SET idcampaign=@var_idcampaign WHERE idupload=var_upload;
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `processdnc` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `processdnc`(var_iduser INT(11), var_campaign INT(11))
BEGIN
	DECLARE a INT(6);
	DECLARE b INT(3);
	DECLARE c INT(3);
	DECLARE var_upload INT(11) DEFAULT 0;
	DECLARE i INT DEFAULT 0;
	DECLARE var_didgroup INT DEFAULT 0;
	DECLARE curdid varchar(15);
	DECLARE new_curdid VARCHAR(15);
	DECLARE var_groupid INT DEFAULT 0;
	DECLARE var_agentgroup INT DEFAULT 0;
	DECLARE var_total INT DEFAULT 0;
	DECLARE var_left INT DEFAULT 0;
	DECLARE var_total_ph INT DEFAULT 0;
	DECLARE var_unique_ph INT DEFAULT 0;
	DECLARE str VARCHAR(255) DEFAULT NULL;
	DECLARE start_time VARCHAR(20) DEFAULT '';
	DECLARE end_time VARCHAR(20) DEFAULT '';
	
	DROP TABLE IF EXISTS `temp_dnccheck`;
	
	select didgroup, id_agentgroup from `campaign` where id=var_campaign into @var_didgroup, @var_agentgroup;
	select @var_didgroup, @var_agentgroup; 
	select id from uploads_group where id_campaign=var_campaign limit 1 into @var_groupid;
	select @var_groupid;
	select count(*) from `did` where id_group=@var_didgroup iNTO @b;
	select @b;
	SELECT COUNT(*) FROM `agents` WHERE agentgroupid=@var_agentgroup INTO @c;
	select @c;
	SELECT COUNT(*) FROM uploads WHERE uploads.id_listgroup=@var_groupid AND uploads.`status`=0 LIMIT 1 INTO @a;
	SELECT @a;
	if @c=0 then
		update campaign set is_status=21 where id=var_campaign;
	end if;
	
	IF @b=0 THEN
		UPDATE campaign SET is_status=11 WHERE id=var_campaign;
	END IF;
	
	IF @a=0 THEN
		UPDATE campaign SET is_status=9 WHERE id=var_campaign;
	END IF;
	
	SET @i=0;
	WHILE (@i<@a) DO
		
		SELECT `id` FROM uploads WHERE uploads.id_listgroup=@var_groupid AND uploads.`status`=0 LIMIT 1 INTO @var_upload;
		select @var_upload; 
		SELECT CURRENT_TIMESTAMP(6) INTO @start_time;
		
		SELECT COUNT(*) FROM `leads` WHERE `id_upload`=@var_upload INTO @var_total;
		select @var_total;
		CREATE TEMPORARY TABLE `temp_dnccheck` (id_campaign INT(11) DEFAULT -1, `id_lead` INT(11) DEFAULT -1,  `id_upload` INT(11) DEFAULT -1 , iduser int(11),  `callerid` VARCHAR(20) DEFAULT '', `phone` VARCHAR(15) DEFAULT '', `type` TINYINT(1) DEFAULT -1, `state` varchar(15), 
		UNIQUE KEY `phone` (`phone`),  KEY `id_lead` (`id_lead`,`id_upload`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
	
		INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`phone`) (SELECT DISTINCT(`number`) FROM `dnc` WHERE iduser=var_iduser);
		
		
		
		SELECT SUM(IF(LENGTH(`phone`)=10 OR LENGTH(`phone`)=11,1,0)) + SUM(IF(LENGTH(`cell_phone`)=10 OR LENGTH(`cell_phone`)=11,1,0))+
		SUM(IF(LENGTH(`work_phone`)=10 OR LENGTH(`work_phone`)=11,1,0))
		FROM `leads` WHERE `id_upload`=@var_upload INTO var_total_ph;
		select var_total_ph;
		
		IF @b=1 THEN
			SELECT number FROM `did` WHERE id_group=@var_didgroup INTO @curdid;
			select @curdid;
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,callerid, `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, @curdid ,`phone`,1,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`phone` REGEXP '^[0-9]{10}$') OR (`phone` REGEXP '^[0-9]{11}$')));		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,callerid, `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, @curdid ,`cell_phone`,2,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`cell_phone` REGEXP '^[0-9]{10}$') OR (`cell_phone` REGEXP '^[0-9]{11}$')));
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,callerid,`phone`, `type`, `state`) 
			(SELECT `id`, `id_upload`, iduser, @curdid , `work_phone`,3,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`work_phone` REGEXP '^[0-9]{10}$') OR (`work_phone` REGEXP '^[0-9]{11}$')));
		
		
		
		else	
			SELECT number FROM `did` WHERE id_group=@var_didgroup limit 1 INTO @new_curdid;
			select @new_curdid;
		
		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,  callerid , `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, @new_curdid , `phone`,1,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`phone` REGEXP '^[0-9]{10}$') OR (`phone` REGEXP '^[0-9]{11}$')));		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser , callerid , `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, @new_curdid , `cell_phone`,2,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`cell_phone` REGEXP '^[0-9]{10}$') OR (`cell_phone` REGEXP '^[0-9]{11}$')));
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser , callerid ,`phone`, `type`, `state`) 
			(SELECT `id`, `id_upload`, iduser, @new_curdid ,`work_phone`,3,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`work_phone` REGEXP '^[0-9]{10}$') OR (`work_phone` REGEXP '^[0-9]{11}$')));
			
		
		END IF;
		
		
		DELETE FROM `temp_dnccheck` WHERE `id_upload`=-1;
		
		
		INSERT INTO `call_leads` (`id_campaign`, `id_lead`, `id_upload`, id_user , `phone` , `callerid` , `state` ) (SELECT var_campaign, `id_lead`, `id_upload`, iduser , `phone` , callerid  , `state` FROM `temp_dnccheck`);
		select * from `call_leads`;
		SELECT COUNT(*) `id_upload` FROM `call_leads` INTO @var_left;
		SELECT @var_left;
	
		DROP TABLE IF EXISTS `temp_dnccheck`;
		SELECT CURRENT_TIMESTAMP(6) INTO @end_time;
	
		UPDATE `uploaded_file_process` SET total_records=@var_total, duplicate_records=(@var_total_ph-@var_unique_ph), total_dnc=(@var_unique_ph-@var_left), 
		execution_start=@start_time, execution_end=@end_time, `datetime`=NOW() WHERE id_upload=@var_upload;
		UPDATE uploads SET uploads.`status`=1 WHERE uploads.`id`=@var_upload LIMIT 1 ; 
		SET @i=@i+1;
	
	END WHILE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `process_dnc` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `process_dnc`(var_iduser INT(11), var_campaign INT(11))
BEGIN
	DECLARE a INT(6);
	DECLARE b INT(3);
	DECLARE c INT(3);
	DECLARE var_upload INT(11) DEFAULT 0;
	DECLARE i INT DEFAULT 0;
	DECLARE var_didgroup INT DEFAULT 0;
	DECLARE curdid VARCHAR(15);
	DECLARE var_groupid INT DEFAULT 0;
	DECLARE var_agentgroup INT DEFAULT 0;
	DECLARE var_total INT DEFAULT 0;
	DECLARE var_left INT DEFAULT 0;
	DECLARE var_total_ph INT DEFAULT 0;
	DECLARE var_unique_ph INT DEFAULT 0;
	DECLARE str VARCHAR(255) DEFAULT NULL;
	DECLARE start_time VARCHAR(20) DEFAULT '';
	DECLARE end_time VARCHAR(20) DEFAULT '';
	
	DROP TABLE IF EXISTS `temp_dnccheck`;
	
	SELECT didgroup, id_agentgroup FROM `campaign` WHERE id=var_campaign INTO @var_didgroup, @var_agentgroup;
	SELECT @var_didgroup, @var_agentgroup; 
	SELECT id FROM uploads_group WHERE id_campaign=var_campaign LIMIT 1 INTO @var_groupid;
	SELECT @var_groupid;
	SELECT COUNT(*) FROM `did` WHERE id_group=@var_didgroup INTO @b;
	SELECT @b;
	SELECT COUNT(*) FROM `agents` WHERE agentgroupid=@var_agentgroup INTO @c;
	SELECT @c;
	SELECT COUNT(*) FROM uploads WHERE uploads.id_listgroup=@var_groupid AND uploads.`status`=0 LIMIT 1 INTO @a;
	SELECT @a;
	IF @c=0 THEN
		UPDATE campaign SET is_status=21 WHERE id=var_campaign;
	END IF;
	
	IF @b=0 THEN
		UPDATE campaign SET is_status=11 WHERE id=var_campaign;
	END IF;
	
	IF @a=0 THEN
		UPDATE campaign SET is_status=9 WHERE id=var_campaign;
	END IF;
	
	SET @i=0;
	WHILE (@i<@a) DO
		
		SELECT `id` FROM uploads WHERE uploads.id_listgroup=@var_groupid AND uploads.`status`=0 LIMIT 1 INTO @var_upload;
		SELECT @var_upload; 
		SELECT CURRENT_TIMESTAMP(6) INTO @start_time;
		
		SELECT COUNT(*) FROM `leads` WHERE `id_upload`=@var_upload INTO @var_total;
		SELECT @var_total;
		CREATE TEMPORARY TABLE `temp_dnccheck` (id_campaign INT(11) DEFAULT -1, `id_lead` INT(11) DEFAULT -1,  `id_upload` INT(11) DEFAULT -1 , iduser INT(11),  `callerid` VARCHAR(20) DEFAULT '', `phone` VARCHAR(15) DEFAULT '', `type` TINYINT(1) DEFAULT -1, `state` VARCHAR(15), 
		UNIQUE KEY `phone` (`phone`),  KEY `id_lead` (`id_lead`,`id_upload`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
	
		INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`phone`) (SELECT DISTINCT(`number`) FROM `dnc` WHERE iduser=var_iduser);
		
		
		
		SELECT SUM(IF(LENGTH(`phone`)=10 OR LENGTH(`phone`)=11,1,0)) + SUM(IF(LENGTH(`cell_phone`)=10 OR LENGTH(`cell_phone`)=11,1,0))+
		SUM(IF(LENGTH(`work_phone`)=10 OR LENGTH(`work_phone`)=11,1,0))
		FROM `leads` WHERE `id_upload`=@var_upload INTO var_total_ph;
		SELECT var_total_ph;
		
		IF @b=1 THEN
			SELECT number FROM `did` WHERE id_group=@var_didgroup INTO @curdid;
			SELECT @curdid;
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,callerid, `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, @curdid ,`phone`,1,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`phone` REGEXP '^[0-9]{10}$') OR (`phone` REGEXP '^[0-9]{11}$')));		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,callerid, `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, @curdid ,`cell_phone`,2,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`cell_phone` REGEXP '^[0-9]{10}$') OR (`cell_phone` REGEXP '^[0-9]{11}$')));
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,callerid,`phone`, `type`, `state`) 
			(SELECT `id`, `id_upload`, iduser, @curdid , `work_phone`,3,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`work_phone` REGEXP '^[0-9]{10}$') OR (`work_phone` REGEXP '^[0-9]{11}$')));
		
		
		
		ELSE	
			
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`callerid`) SELECT number FROM `did` WHERE id_group=@var_didgroup;
			SELECT * FROM temp_dnccheck ;
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser , `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, `phone`,1,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`phone` REGEXP '^[0-9]{10}$') OR (`phone` REGEXP '^[0-9]{11}$')));		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,`phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser, `cell_phone`,2,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`cell_phone` REGEXP '^[0-9]{10}$') OR (`cell_phone` REGEXP '^[0-9]{11}$')));
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,`phone`, `type`, `state`) 
			(SELECT `id`, `id_upload`, iduser,`work_phone`,3,`state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`work_phone` REGEXP '^[0-9]{10}$') OR (`work_phone` REGEXP '^[0-9]{11}$')));
			
		
		END IF;
		
		
		DELETE FROM `temp_dnccheck` WHERE `id_upload`=-1;
		
		
		INSERT INTO `call_leads` (`id_campaign`, `id_lead`, `id_upload`, id_user , `phone` , `callerid` , `state` ) (SELECT var_campaign, `id_lead`, `id_upload`, iduser , `phone` , callerid  , `state` FROM `temp_dnccheck`);
		SELECT * FROM `call_leads`;
		SELECT COUNT(*) `id_upload` FROM `call_leads` INTO @var_left;
		SELECT @var_left;
	
		DROP TABLE IF EXISTS `temp_dnccheck`;
		SELECT CURRENT_TIMESTAMP(6) INTO @end_time;
	
		UPDATE `uploaded_file_process` SET total_records=@var_total, duplicate_records=(@var_total_ph-@var_unique_ph), total_dnc=(@var_unique_ph-@var_left), 
		execution_start=@start_time, execution_end=@end_time, `datetime`=NOW() WHERE id_upload=@var_upload;
		UPDATE uploads SET uploads.`status`=1 WHERE uploads.`id`=@var_upload LIMIT 1 ; 
		SET @i=@i+1;
	END WHILE;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `process_sp` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `process_sp`()
BEGIN
	DECLARE a INT(6);
	DECLARE b INT(3);
	DECLARE c INT(3);
	DECLARE var_upload INT(11) DEFAULT 0;
	DECLARE i INT DEFAULT 0;
	DECLARE var_didgroup INT DEFAULT 0;
	DECLARE curdid VARCHAR(15);
	DECLARE var_groupid INT DEFAULT 0;
	DECLARE var_agentgroup INT DEFAULT 0;
	DECLARE var_total INT DEFAULT 0;
	DECLARE var_left INT DEFAULT 0;
	DECLARE var_total_ph INT DEFAULT 0;
	DECLARE var_unique_ph INT DEFAULT 0;
	DECLARE str VARCHAR(255) DEFAULT NULL;
	DECLARE start_time VARCHAR(20) DEFAULT '';
	DECLARE end_time VARCHAR(20) DEFAULT '';
	
	DROP TABLE IF EXISTS `temp_dnccheck`;
	
	SELECT didgroup, id_agentgroup FROM `campaign` WHERE id=var_campaign INTO @var_didgroup, @var_agentgroup;
	SELECT @var_didgroup, @var_agentgroup; 
	SELECT id FROM uploads_group WHERE id_campaign=var_campaign LIMIT 1 INTO @var_groupid;
	SELECT @var_groupid;
	SELECT COUNT(*) FROM `did` WHERE id_group=@var_didgroup INTO @b;
	SELECT @b;
	SELECT COUNT(*) FROM `agents` WHERE agentgroupid=@var_agentgroup INTO @c;
	SELECT @c;
	SELECT COUNT(*) FROM uploads WHERE uploads.id_listgroup=@var_groupid AND uploads.`status`=0 LIMIT 1 INTO @a;
	SELECT @a;
	IF @c=0 THEN
		UPDATE campaign SET is_status=21 WHERE id=var_campaign;
	END IF;
	
	IF @b=0 THEN
		UPDATE campaign SET is_status=11 WHERE id=var_campaign;
	END IF;
	
	IF @a=0 THEN
		UPDATE campaign SET is_status=9 WHERE id=var_campaign;
	END IF;
	SELECT @a,@b,@c;
	SELECT id `campaign_id`, `name` campaign_nameaa, status_fp(is_status)AS `error` FROM campaign WHERE id=var_campaign;
	SET @i=0;
	WHILE (@i<@a) DO
		
		SELECT `id` FROM uploads WHERE uploads.id_listgroup=@var_groupid AND uploads.`status`=0 LIMIT 1 INTO @var_upload;
		SELECT @var_upload; 
		SELECT CURRENT_TIMESTAMP(6) INTO @start_time;
		
		
			
		SELECT COUNT(*) FROM `leads` WHERE `id_upload`=@var_upload INTO @var_total;
		SELECT @var_total;
		CREATE TEMPORARY TABLE `temp_dnccheck` (id_campaign INT(11) DEFAULT -1, `id_lead` INT(11) DEFAULT -1,  `id_upload` INT(11) DEFAULT -1, iduser INT(11),  `callerid` VARCHAR(15) DEFAULT '', `phone` VARCHAR(15) DEFAULT '', `type` TINYINT(1) DEFAULT -1, `state` VARCHAR(15), 
		UNIQUE KEY `phone` (`phone`),  KEY `id_lead` (`id_lead`,`id_upload`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
	
		INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`phone`) (SELECT DISTINCT(`number`) FROM `dnc` WHERE iduser=var_iduser);
	
		
		
		
		
		SELECT SUM(IF(LENGTH(`phone`)=10 OR LENGTH(`phone`)=11,1,0)) + SUM(IF(LENGTH(`cell_phone`)=10 OR LENGTH(`cell_phone`)=11,1,0))+
		SUM(IF(LENGTH(`work_phone`)=10 OR LENGTH(`work_phone`)=11,1,0))
		FROM `leads` WHERE `id_upload`=@var_upload INTO var_total_ph;
		SELECT var_total_ph;
		
		IF @b=1 THEN
			SELECT number FROM `did` WHERE id_group=@var_didgroup INTO @curdid;
			SELECT @curdid;
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,`callerid`, `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, iduser = var_iduser , @curdid ,right(`phone`,10)`phone`,1, `state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`phone` REGEXP '^[0-9]{10}$') OR (`phone` REGEXP '^[0-9]{11}$')));		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,`callerid`, `phone`, `type` , `state`) 
			(SELECT `id`, `id_upload`, var_iduser , @curdid, right(`cell_phone`,10)`cell_phone`,2, `state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`cell_phone` REGEXP '^[0-9]{10}$') OR (`cell_phone` REGEXP '^[0-9]{11}$')));
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser , `callerid`, `phone`, `type`, `state`) 
			(SELECT `id`, `id_upload`, var_iduser , @curdid, right(`work_phone`,10)`work_phone`,3,  `state` FROM `leads` WHERE `id_upload`=@var_upload AND ((`work_phone` REGEXP '^[0-9]{10}$') OR (`work_phone` REGEXP '^[0-9]{11}$')));
		ELSE	
		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser , `callerid`, `phone`, `type`,`state`) 
			(SELECT `id`, `id_upload`, iduser=var_iduser , @curdid , `phone`, 1 , `state` 
			FROM `leads` WHERE `id_upload`=@var_upload AND ((`phone` REGEXP '^[0-9]{10}$') OR (`phone` REGEXP '^[0-9]{11}$')));		
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser , `callerid`, `phone`, `type`,`state`) 
			(SELECT `id`, `id_upload`, iduser=var_iduser , @curdid , `cell_phone` , 2 , `state`
			FROM `leads` WHERE `id_upload`=@var_upload AND ((`cell_phone` REGEXP '^[0-9]{10}$') OR (`cell_phone` REGEXP '^[0-9]{11}$')));
			INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`id_lead`, `id_upload`, iduser ,`callerid`, `phone`, `type`,`state`) 
			(SELECT `id`, `id_upload`, iduser=var_iduser , @curdid , `work_phone`, 3 , `state`
			FROM `leads` WHERE `id_upload`=@var_upload AND ((`work_phone` REGEXP '^[0-9]{10}$') OR (`work_phone` REGEXP '^[0-9]{11}$')));
			
			
			
		
		END IF;
		
		SELECT * FROM temp_dnccheck ;
		DELETE FROM `temp_dnccheck` WHERE `id_upload`=-1;
	
		INSERT INTO `call_leads` (`id_campaign`, `id_lead`, `id_upload`, id_user , `phone` , `callerid` , `state` ) (SELECT var_campaign, `id_lead`, `id_upload`, iduser , `phone` , `callerid` , `state` FROM `temp_dnccheck`);
		SELECT * FROM `call_leads`;
		SELECT COUNT(*) `id_upload` FROM `call_leads` INTO @var_left;
		SELECT @var_left;
	
		DROP TABLE IF EXISTS `temp_dnccheck`;
		SELECT CURRENT_TIMESTAMP(6) INTO @end_time;
	
		UPDATE `uploaded_file_process` SET total_records=@var_total, duplicate_records=(@var_total_ph-@var_unique_ph), total_dnc=(@var_unique_ph-@var_left), 
		execution_start=@start_time, execution_end=@end_time, `datetime`=NOW() WHERE id_upload=@var_upload;
		UPDATE uploads SET uploads.`status`=1 WHERE uploads.`id`=@var_upload LIMIT 1 ; 
		SET @i=@i+1;
	END WHILE;

	END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SaveCampaign` */;
DELIMITER ;;
CREATE DEFINER=`t0shu`@`%` PROCEDURE `SaveCampaign`(var_iduser int(11), var_name varchar(100), var_transfer_key int(20), var_msg_key int(20), 
		var_call_per_hour int(10), var_amd tinyint(2), var_voice_call tinyint(1), var_starthr tinyint(2), var_startmin tinyint(2), 
		var_endhr tinyint(2), var_endmin tinyint(2), var_menu_file varchar(145), var_weekday varchar(20), var_didgroup int(11),
		var_lead_dialingtype tinyint(1),var_cam_type tinyint(1), var_max_calls int(10), `var_action` TEXT)
BEGIN
	declare id_camp int(11);
	DECLARE str TEXT; 
	DECLARE inc INT(5) DEFAULT 0; 
	DECLARE tmp INT(10);
	DECLARE a VARCHAR(50);
	DECLARE b VARCHAR(50);
	DECLARE id INT(11);
	DECLARE typ VARCHAR(100);
	DECLARE des VARCHAR(100);
	
	INSERT INTO `campaign` (`iduser`, `name`, `transfer_key`, `msg_key`, `call_per_hour`, `amd`, `voice_call`, `starthr`, `startmin`, `endhr`, `endmin`,
	`menu_file`, `weekday`,`didgroup`,`lead_dialingtype`,`cam_type`,`max_calls`) 
	VALUES
	(var_iduser, var_name, var_transfer_key, var_msg_key, var_call_per_hour, var_amd, var_voice_call, var_starthr, var_startmin, var_endhr, var_endmin,
		var_menu_file, var_weekday, var_didgroup, var_lead_dialingtype, var_cam_type, var_max_calls);
	
	set@id_camp=last_insert_id();
	
	SET @str=var_action;
	SET @inc=1+LENGTH(@str)-LENGTH(REPLACE(@str, ',', ''));     
	
	
	WHILE (@inc>0) DO 
		SET @tmp=(SUBSTRING_INDEX(@str,',',-1));
		SET @id = (SUBSTRING_INDEX(@tmp,'-',1));
		SET @typ = (SUBSTRING_INDEX(SUBSTRING_INDEX(@tmp,'-',2),'-',-1));
		SET @des = (SUBSTRING_INDEX(@tmp,'-',-1));
		
		SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
		
		
		INSERT INTO `action` (id_campaign, `key`, `action`, description) VALUES (@id_camp, @id, @typ, @des); 
		
		SET @inc=@inc-1;
	END WHILE;
	
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SaveDidNumber` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SaveDidNumber`(var_didgroup_id INT(11), var_iduser INT(11), var_number TEXT)
BEGIN
	
	
	DECLARE tmp VARCHAR(10);
	DECLARE inc INT DEFAULT 0;
	DECLARE str TEXT;
	DECLARE num VARCHAR(40);
	DECLARE cd VARCHAR(10);
	DECLARE stat VARCHAR(3);
	DECLARE cntry VARCHAR(20);
	
	
	
		SET @str=var_number;
		SET @inc=1+LENGTH(@str) - LENGTH(REPLACE(@str, ',', ''));
		
		WHILE (@inc>0) DO 
		SET @tmp = (SUBSTRING_INDEX(@str,',',-1));
		SET @str=TRIM(TRAILING ',' FROM REPLACE(@str,@tmp, ''));
		SELECT SUBSTR(@tmp,1,3) us_code, u.state, u.country FROM did d JOIN us_states u ON SUBSTR(@tmp,1,3)=u.code 
		WHERE iduser=var_iduser LIMIT 1 INTO @cd, @stat, @cntry; 
		
		IF(@inc>0) THEN     
	
			INSERT INTO did (iduser, id_group, country, state, areacode, number ,didtype , created_date) VALUES (var_iduser, var_didgroup_id, @cntry, @stat, @cd, @tmp, '1', NOW());
		end if;
		SET @inc=@inc-1;
		END WHILE;
	
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SavePackages` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SavePackages`(var_name varchar(45), var_id int(11), var_iduser int(11), var_sms_rate double(8,4), var_ib_rate DOUBLE(8,4), var_ob_rate DOUBLE(8,4), var_did_rate DOUBLE(8,4), var_rvm_rate DOUBLE(8,4),var_agent_rate DOUBLE(8,4),`type` tinyint(1))
BEGIN
	if `type`>0 then
	
		update `package` set `name`=var_name, iduser=var_iduser, sms_rate=var_sms_rate, ib_rate=var_ib_rate, ob_rate=var_ob_rate, did_rate=var_did_rate,
		rvm_rate=var_rvm_rate, agent_rate=var_agent_rate where id=var_id and iduser=var_iduser;
	else
	
		insert into `package` (`name`, iduser, sms_rate, ib_rate, ob_rate, did_rate,rvm_rate, agent_rate)
		values(var_name, var_iduser, var_sms_rate, var_ib_rate, var_ob_rate, var_did_rate, var_rvm_rate,var_agent_rate);
	
	end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SelectSingleLead` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectSingleLead`(var_lead_id INT (11))
BEGIN
	
	
	
	
	
	
	
	
	
	SELECT first_name , last_name, DATE_FORMAT(birth_date, '%Y-%m-%d') birth_date, age, street_address_1, street_address_2, city, state, country, zip, phone,
	cell_phone, work_phone, email, hall,bed_room,guest_room,bath_room,drawing_room,kitchen,balcony,dining_room,library,garage,store_room,swimming_pool,laundry_room,
	paintry_room,home_office,work_shop,kids_room,field1, field2,field3,field4,
	field5,field6,field7, field8,field9,field10,field11,field12 from leads where id=var_lead_id;
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
		

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SingleLeadUpdate` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SingleLeadUpdate`(var_lead_id INT(11),var_first_name VARCHAR(40),var_last_name VARCHAR(40),var_birth_date DATE, var_age INT(11), var_street_address_1 VARCHAR(40),var_street_address_2 VARCHAR(40),
	var_city VARCHAR(40),var_state VARCHAR(40), var_country varchar (40),var_zip VARCHAR(40), var_phone VARCHAR(40), var_cell_phone VARCHAR(40), var_work_phone VARCHAR(40), 
	var_email VARCHAR(40), var_hall VARCHAR(40),var_bed_room VARCHAR(40),var_guest_room VARCHAR(40),var_bath_room VARCHAR(40),var_drawing_room VARCHAR(40),var_kitchen VARCHAR(40),
	var_balcony VARCHAR(40),var_dining_room VARCHAR(40),var_library VARCHAR(40),var_garage VARCHAR(40),var_store_room VARCHAR(40),var_swimming_pool VARCHAR(40),var_laundry_room VARCHAR(40),
	var_paintry_room VARCHAR(40),var_home_office VARCHAR(40),var_work_shop VARCHAR(40),var_kids_room VARCHAR(40),var_field1 VARCHAR(40),var_field2 VARCHAR(40),var_field3 VARCHAR(40),var_field4 VARCHAR(40),
	var_field5 VARCHAR(40),var_field6 VARCHAR(40),var_field7 VARCHAR(40),var_field8 VARCHAR(40),var_field9 VARCHAR(40),var_field10 VARCHAR(40),var_field11 VARCHAR(40),var_field12 VARCHAR(40))
BEGIN
	
	UPDATE leads SET `first_name`=var_first_name,`last_name`=var_last_name,`birth_date`=var_birth_date, age=var_age,street_address_1=var_street_address_1, street_address_2=var_street_address_2, city=var_city, state=var_state,
	country=var_country,zip=var_zip, phone=var_phone, cell_phone=var_cell_phone , work_phone=var_work_phone , email=var_email,hall=var_hall,bed_room=var_bed_room,guest_room=var_guest_room,bath_room = var_bath_room,
	drawing_room=var_drawing_room, kitchen=var_kitchen, balcony=var_balcony,dining_room=var_dining_room,library=var_library,garage=var_garage,store_room=var_store_room, swimming_pool=var_swimming_pool,
	laundry_room=var_laundry_room,paintry_room=var_paintry_room,home_office=var_home_office,work_shop=var_work_shop,kids_room=var_kids_room,field1= var_field1,field2= var_field2,field3= var_field3,
	field4= var_field4,field5= var_field5,field6= var_field6,field7= var_field7,field8= var_field8,field9= var_field9,field10= var_field10,field11= var_field11,
	field12= var_field12 WHERE id=var_lead_id; 
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `StartCampaign` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `StartCampaign`(var_userid INT(11), var_campaignid INT(11), var_campaigntype INT(11))
BEGIN

	DECLARE `total` INT DEFAULT 0;
        DECLARE `total_leads` INT DEFAULT 0;
        DECLARE `used` INT DEFAULT 0;
        DECLARE `did_count` INT DEFAULT 0;
        DECLARE `table` VARCHAR(255) DEFAULT '';
        DECLARE `groupid` INT DEFAULT 0;
        declare `total_agents` int default 0;
        
        DROP TEMPORARY TABLE IF EXISTS TempCheck;
        CREATE TEMPORARY TABLE TempCheck AS
	SELECT id, rate, msg_key, didgroup, amd_msg_key, amd,menu_file, is_status, call_per_hour, balance, primary_transfer, primary_action, transfer_key,
	 iduser,`name`, startdate, enddate, starthr, endhr, endmin, startmin, min, hour
	FROM 
	(SELECT c.`id`, 0 `rate`, c.msg_key, c.didgroup, c.amd_msg_key, c.amd, c.`menu_file`, c.is_status, c.`call_per_hour`, (CASE WHEN c.cam_type=2 THEN u.ib WHEN c.cam_type=4 THEN u.ob END) balance, c.primary_transfer, c.primary_action, c.transfer_key, c.iduser,c.`name` ,IFNULL(c.`startdate`,NOW()) `startdate`, IFNULL(c.`enddate`,NOW()) `enddate`,IFNULL(c.`starthr`,0) `starthr`, IFNULL(c.`endhr`,23) `endhr`,IFNULL(c.`endmin`,59) `endmin`, IFNULL(c.`startmin`,0) `startmin`, CONVERT(DATE_FORMAT(NOW(),'%i'), SIGNED INTEGER) `min`,  CONVERT(DATE_FORMAT(NOW(),'%H'), SIGNED INTEGER) `hour`
	FROM campaign c JOIN user_minutes u ON c.`iduser`=u.`iduser` WHERE c.`id`=var_campaignid AND FIND_IN_SET(WEEKDAY(CURDATE()),`weekday`) AND curdate() BETWEEN c.`startdate` AND c.enddate) cmpp;
	
	SELECT COUNT(*) `num` FROM TempCheck WHERE
		((`endhr`>DATE_FORMAT(NOW(),'%H') AND `starthr`<DATE_FORMAT(NOW(),'%H')) OR 
		(`starthr`=DATE_FORMAT(NOW(
		),'%H') AND `startmin`>DATE_FORMAT(NOW(),'%i')) OR
		(`endhr`=DATE_FORMAT(NOW(),'%H') AND `endmin`<DATE_FORMAT(NOW(),'%i'))) AND balance>0 GROUP BY id INTO @total;
	
	IF @total=1 THEN 
		SELECT id FROM `uploads_group` WHERE id_campaign=var_campaignid INTO @groupid;
		SELECT COUNT(*) FROM agents a JOIN agents_group ag ON a.agentgroupid=ag.id JOIN campaign c ON c.id_agentgroup=a.agentgroupid WHERE c.id=var_campaignid INTO @total_agents;
		SELECT COUNT(*) FROM `did` WHERE id_group IN (select didgroup from campaign where id=var_campaignid) GROUP BY id_group INTO @did_count;
		
		
		IF @did_count>0 THEN 
			IF var_campaigntype=2 or 4 THEN
				SET @table='leads';
				CALL processdnc(var_userid , var_campaignid);
			ELSE
				SET @table='';
			END IF;
		
		SELECT COUNT(*) FROM leads l JOIN uploads u ON l.group_id = u.id_listgroup WHERE u.id_campaign = var_campaignid AND l.`status`=0 INTO @total_leads ;
			
			IF @total_leads>0 THEN
				UPDATE `campaign` SET `is_status`=1 WHERE id=var_campaignid;
				SELECT 1 `num`;
			ELSE
				UPDATE `campaign` SET `is_status`=2 WHERE id=var_campaignid;
				SELECT 0 `num`;
			END IF;
		ELSE
			UPDATE `campaign` SET `is_status`=5 WHERE id=var_campaignid;
		END IF;
	
	ELSE
		UPDATE `campaign` SET `is_status`=6 WHERE id=var_campaignid;
		
	END IF;
	
	
	SELECT CAST(IFNULL(@total, 0) AS INT) total, CAST(IFNULL(@did_count, 0) AS INT) did_count, CAST(IFNULL(@total_leads, 0) AS INT) lead_count , cast(ifnull(@total_agents,0) as int) agents_count;

	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `StopCampaign` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `StopCampaign`(var_userid INT(11), var_campaignid INT(11),var_campaigntype INT(11))
BEGIN
	DECLARE `table` VARCHAR(255) DEFAULT '';
	DECLARE `total_leads` INT DEFAULT 0;
	
	IF var_campaigntype=1 THEN
		SET @table='leads';
	ELSEIF var_campaigntype=2 THEN
		SET @table='leads';
	ELSEIF var_campaigntype=3 THEN
		SET @table='leads';
	ELSEIF var_campaigntype=4 THEN
		SET @table='leads';
	ELSEIF var_campaigntype=5 THEN
		SET @table='leads';
	ELSE
		SET @table='';
	END IF;
	
	
	
	
	
	
	SELECT count(*) FROM leads l JOIN uploads u ON l.group_id = u.id_listgroup WHERE u.id_campaign = var_campaignid AND l.`status`=0 INTO @total_leads ;
	
			
	IF @total_leads>0 THEN
		UPDATE `campaign` SET `is_status`=0 WHERE id=var_campaignid;
		SELECT 1 `num`;
	ELSE
		IF @total_leads>0 THEN
			UPDATE `campaign` SET `is_status`=2 WHERE id=var_campaignid;
			SELECT 0 `num`;
		END IF;
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `tempinserts` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `tempinserts`(var_campaign INT(11))
BEGIN
		SELECT didgroup FROM `campaign` WHERE id=var_campaign INTO @var_didgroup;
		CREATE TEMPORARY TABLE `temp_dnccheck` (id_campaign INT(11) DEFAULT -1, `id_lead` INT(11) DEFAULT -1,  `id_upload` INT(11) DEFAULT -1 , iduser INT(11),  `callerid` VARCHAR(15) DEFAULT '', `phone` VARCHAR(15) DEFAULT '', `type` TINYINT(1) DEFAULT -1, `state` VARCHAR(15), 
		UNIQUE KEY `phone` (`phone`),  KEY `id_lead` (`id_lead`,`id_upload`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
		
		INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`callerid`)(SELECT `number` FROM `did` WHERE id_group=@var_didgroup);
	
	
			

	END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `tempinsertss` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `tempinsertss`(var_campaign INT(11))
BEGIN
		SELECT didgroup FROM `campaign` WHERE id=var_campaign INTO @var_didgroup;
		CREATE TEMPORARY TABLE `temp_dnccheck` (id_campaign INT(11) DEFAULT -1, `id_lead` INT(11) DEFAULT -1,  `id_upload` INT(11) DEFAULT -1 , iduser INT(11),  `callerid` VARCHAR(15) DEFAULT '', `phone` VARCHAR(15) DEFAULT '', `type` TINYINT(1) DEFAULT -1, `state` VARCHAR(15), 
		UNIQUE KEY `phone` (`phone`),  KEY `id_lead` (`id_lead`,`id_upload`)) ENGINE=INNODB DEFAULT CHARSET=latin1;
		
		INSERT HIGH_PRIORITY IGNORE INTO `temp_dnccheck` (`callerid`)(SELECT `number` FROM `did` WHERE id_group=@var_didgroup);
		select * from temp_dnccheck ;
		insert into calls_leads (callerid) (select callerid from temp_dnccheck);
	
	
			

	END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateUserManagement` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateUserManagement`(var_id int(11),var_iduser INT(11),var_type TINYINT(1),var_fullname VARCHAR(25), var_username VARCHAR(100), var_password VARCHAR(100),
var_company VARCHAR(100), var_contact VARCHAR(12), var_email VARCHAR(145), var_channels INT(11), var_billing_rate DOUBLE(12,6))
BEGIN
		UPDATE `users` SET `idparent`=var_iduser, `fullname`=var_fullname, `username`=var_username, `password`=var_password, `company`=var_company, `contact`=var_contact, `email`=var_email, 
		`channels`=var_channels, `billing_rate`=var_billing_rate,`type`=var_type
		WHERE `id`=var_id ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_dialing` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_dialing`(var_lead TEXT, `status` INT(2))
BEGIN
        DECLARE `SQL2` TEXT DEFAULT '';
	SET @SQL2=CONCAT('UPDATE `call_leads` SET `status`=',`status`,' WHERE id_lead IN (', var_lead, ');');
        PREPARE stmt2 FROM @SQL2;
        EXECUTE stmt2;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UploadProfile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UploadProfile`(var_filename VARCHAR(255),  var_path VARCHAR(255), var_iduser INT(11))
BEGIN
	INSERT INTO `uploads`( `iduser`, `filename`, `filepath`) VALUES (var_iduser, var_filename, var_path);
	SELECT LAST_INSERT_ID() id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ViewDNC` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ViewDNC`()
BEGIN
	SELECT dnclist_id, iduser, list_name, dnc_count, globel_dnc, created FROM dnc_lists ORDER BY dnclist_id ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ViewDnlList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ViewDnlList`(var_list_id int(11))
BEGIN
	SELECT `dnc_id`, `number`, `created` FROM `dnc` WHERE `list_id`= var_list_id ORDER BY `dnc_id` DESC ;   
	
END ;;
DELIMITER ;

--
-- Final view structure for view `getCampaign`
--

/*!50001 DROP TABLE IF EXISTS `getCampaign`*/;
/*!50001 DROP VIEW IF EXISTS `getCampaign`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `getCampaign` AS (select `c`.`id` AS `id`,`c`.`cam_type` AS `campaign_type`,`c`.`menu_file` AS `menu_file`,`c`.`is_status` AS `is_status`,`c`.`call_per_hour` AS `call_per_hour`,`c`.`iduser` AS `iduser`,`ag`.`group_name` AS `group`,`c`.`id_agentgroup` AS `idgroup`,`u`.`billing_rate` AS `rate`,`u`.`balance` AS `balance`,`u`.`credit_limit` AS `credit_limit`,`c`.`name` AS `name`,unix_timestamp(`c`.`enddate`) AS `call_before`,unix_timestamp(`c`.`startdate`) AS `call_after`,`c`.`starthr` AS `starthr`,`c`.`endhr` AS `endhr`,`c`.`endmin` AS `endmin`,`c`.`startmin` AS `startmin`,`c`.`amd` AS `amd`,`c`.`words` AS `words`,`c`.`mdt` AS `mdt`,`c`.`mdst` AS `mdst`,`c`.`mdet` AS `mdet`,`c`.`mdset` AS `mdset` from ((`campaign` `c` left join `users` `u` on((`c`.`iduser` = `u`.`id`))) left join `agents_group` `ag` on((`ag`.`id` = `c`.`id_agentgroup`))) where ((`c`.`is_status` in (1,8)) and find_in_set(weekday(curdate()),`c`.`weekday`) and ((`u`.`balance` + `u`.`credit_limit`) > 0)) group by `c`.`id`) */;


