PageRenderTime 54ms CodeModel.GetById 12ms RepoModel.GetById 0ms app.codeStats 1ms

/lms_debug/protected/controllers/ReportsController.php

https://gitlab.com/badelal143/lms_debug
PHP | 1881 lines | 1515 code | 259 blank | 107 comment | 252 complexity | 99fef50f9b4be590022ce0de046ffd27 MD5 | raw file
Possible License(s): LGPL-2.1, LGPL-3.0, LGPL-2.0

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. class ReportsController extends Controller
  3. {
  4. const SHARED_TITLE = "Admin Panel";
  5. /**
  6. * Declares class-based actions.
  7. */
  8. public function filters()
  9. {
  10. return array( 'accessControl' ); // perform access control for CRUD operations
  11. }
  12. public function actions()
  13. {
  14. return array(
  15. // captcha action renders the CAPTCHA image displayed on the contact page
  16. 'captcha'=>array(
  17. 'class'=>'CCaptchaAction',
  18. 'backColor'=>0xFFFFFF,
  19. ),
  20. // page action renders "static" pages stored under 'protected/views/site/pages'
  21. // They can be accessed via: index.php?r=site/page&view=FileName
  22. 'page'=>array(
  23. 'class'=>'CViewAction',
  24. ),
  25. );
  26. }
  27. /**
  28. * It calls the actionCreateExcel to generates excel reports'
  29. * when an action is not explicitly requested by users.
  30. */
  31. public function actionExportExcelReport()
  32. {
  33. // It calls the actionCreateExcel to generates excel reports'
  34. @$getName=$_POST['LeadCronReport']['name'];
  35. @$getStatus=$_POST['LeadCronReport']['status'];
  36. @$getStage=$_POST['LeadCronReport']['stage'];
  37. @$date1=$_POST['LeadCronReport']['period1'];
  38. @$date2=$_POST['LeadCronReport']['period2'];
  39. @$getleadSourceId=$_POST['LeadCronReport']['leadSourceId'];
  40. @$getleadStatusId=$_POST['leadStatusId'];
  41. @$datetype=@$_POST['LeadCronReport']['datetype'];
  42. @$mobile=@$_POST['LeadCronReport']['mobile'];
  43. @$datetype=@$_POST['LeadCronReport']['datetype'];
  44. if(isset($datetype) && !empty($datetype)){
  45. $datetype=$datetype;
  46. } else {
  47. $datetype='updatedOn';
  48. }
  49. $isjunk = @$_REQUEST['isJunk'];
  50. $isjunk = $isjunk?$isjunk:'no';
  51. $this->actionCreateExcel($getName,$getStatus,$getStage,$date1,$date2,$getleadSourceId,$getleadStatusId,$datetype,$isjunk,$mobile);
  52. }
  53. /**
  54. * Action Report.
  55. */
  56. public function actionreports()
  57. {
  58. $model = new LeadCronReport('search');
  59. $model->scenario = 'LeadCronReport';
  60. $teamId = '';
  61. $teamUserId = '';
  62. if (@$_POST['LeadCronReport'] != '' && isset($_POST['LeadCronReport']))
  63. {
  64. if (Yii::app()->session->get('roleName') == 'agent')
  65. {
  66. @$_POST['LeadCronReport']['ws_agentid'] = self::getAgentId();
  67. }
  68. @$primaryLeadStatus = @$_POST['leadStatusId'];
  69. if (count($primaryLeadStatus) > 0)
  70. {
  71. $primaryLeadStatus = implode(",", $primaryLeadStatus);
  72. $primaryDisposition = Yii::app()->db->createCommand("select leadStatus.leadStatus,leadStatusId from leadStatus where parentId IN (" . @$primaryLeadStatus . ") ")->queryAll();
  73. if (count($primaryDisposition) > 0)
  74. {
  75. for ($w = 0; $w < count($primaryDisposition); $w++)
  76. {
  77. @$PrimaryDispositionIds[] = @$primaryDisposition[$w]['leadStatusId'];
  78. }
  79. }
  80. else
  81. {
  82. @$PrimaryDispositionIds[] = '';
  83. }
  84. @$PrimaryDispositionIds = implode(",", @$PrimaryDispositionIds);
  85. }
  86. else
  87. {
  88. @$PrimaryDispositionIds = '';
  89. }
  90. $teamId = @$_POST['Team']['teamId'];
  91. $teamUserId = @$_POST['Team']['teamMemberId'];
  92. if ($teamUserId == '' or empty($teamUserId))
  93. {
  94. $teamUsers = Yii::app()->db->createCommand("select userId from teamUserMapping where teamId = '" . @$teamId . "' ")->queryAll();
  95. $reportsteamUsersIds = array();
  96. foreach ($teamUsers as $key => $value)
  97. {
  98. $reportsteamUsersIds[] = $value['userId'];
  99. }
  100. }
  101. else
  102. {
  103. $reportsteamUsersIds[] = $teamUserId;
  104. }
  105. $name = @$_POST['LeadCronReport']['name'];
  106. $status = @$_POST['LeadCronReport']['status'];
  107. $stage = @$_POST['LeadCronReport']['stage'];
  108. /* $utmCode=@$_POST['LeadCronReport']['utmCode'];
  109. $teleCaller=@$_POST['LeadCronReport']['teleCaller']; */
  110. $period1 = @$_POST['LeadCronReport']['period1'];
  111. $period2 = @$_POST['LeadCronReport']['period2'];
  112. $leadSourceId = @$_POST['LeadCronReport']['leadSourceId'];
  113. $ws_agentid = @$_POST['LeadCronReport']['ws_agentid'];
  114. $datetype = @$_POST['LeadCronReport']['datetype'];
  115. $isjunk = @$_REQUEST['isJunk'];
  116. $mobile = @$_POST['LeadCronReport']['mobile'];
  117. $isjunk = $isjunk ? $isjunk : 'no';
  118. if (isset($datetype) && !empty($datetype))
  119. {
  120. $datetype = $datetype;
  121. }
  122. else
  123. {
  124. $datetype = 'updatedOn';
  125. }
  126. //echo $datetype; die;
  127. $criteria = new CDbCriteria;
  128. $criteria->select = 't.*,`city`.`city`,`state`.`state`, `leadSourceMaster`.`leadSourceTitle`, `leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`,`customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName`';
  129. $criteria->join = 'LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId)LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId)LEFT JOIN campaign ON (campaign.campaignId = t.campaignId)';
  130. $criteria->order = 'leadId DESC';
  131. if (count($reportsteamUsersIds) > 0)
  132. {
  133. $comd = implode(',', $reportsteamUsersIds);
  134. $criteria->addCondition("t.currentAllocatedId IN (" . $comd . ") ");
  135. }
  136. if (isset($name) && !empty($name))
  137. {/* LIKE '$this->time_up%'"; */
  138. $criteria->addCondition("t.name like '%" . $name . "%'");
  139. }
  140. if (isset($status) && !empty($status))
  141. {
  142. $criteria->addCondition("t.leadStatusId =" . $status);
  143. }
  144. if (isset($PrimaryDispositionIds) && !empty($PrimaryDispositionIds))
  145. {
  146. $criteria->addCondition("t.leadStatusId IN (" . @$PrimaryDispositionIds . ") ");
  147. }
  148. if (isset($stage) && !empty($stage))
  149. {
  150. $criteria->addCondition("t.leadStageId =" . $stage);
  151. }
  152. if (isset($leadSourceId) && !empty($leadSourceId))
  153. {
  154. $criteria->addCondition("t.leadSourceId =" . $leadSourceId);
  155. }
  156. if (isset($isjunk) && !empty($isjunk) && $isjunk == 'yes')
  157. {
  158. $criteria->addCondition("t.leadStatusId !=36");
  159. }
  160. if (isset($ws_agentid) && !empty($ws_agentid))
  161. {
  162. $criteria->addCondition("t.leadSourceId='" . $ws_agentid . "'");
  163. }
  164. if (isset($mobile) && !empty($mobile))
  165. {
  166. $criteria->addCondition("t.mobile='" . $mobile . "'");
  167. }
  168. if ((isset($period1) && !empty($period1)) && (isset($period2) && !empty($period2)))
  169. {
  170. $period1 = strtotime($period1);
  171. $period2 = strtotime($period2) + 86399;
  172. // echo date('d m y :: h: i: s: A',$period2); die;
  173. $criteria->addCondition('t.' . $datetype . ' >= ' . $period1 . ' AND t.' . $datetype . ' <= ' . $period2 . '');
  174. // $criteria->addBetweenCondition('t.'.$datetype,$period1,$period2,'');
  175. }
  176. $model->set_name = @$_POST['LeadCronReport']['name'];
  177. $model->set_stage = @$_POST['LeadCronReport']['stage'];
  178. $model->set_status = @$_POST['LeadCronReport']['status'];
  179. $model->set_period1 = @$_POST['LeadCronReport']['period1'];
  180. $model->set_period2 = @$_POST['LeadCronReport']['period2'];
  181. $model->set_leadStatusId = @$_POST['leadStatusId'];
  182. $model->set_leadSourceId = @$_POST['LeadCronReport']['leadSourceId'];
  183. $model->set_ws_agentid = @$_POST['LeadCronReport']['ws_agentid'];
  184. $model->set_mobile = @$_POST['LeadCronReport']['mobile'];
  185. $criteria->order = 'leadId DESC';
  186. $dataProvider = new CActiveDataProvider('LeadCronReport', array('criteria' => $criteria, 'pagination' => array(
  187. 'pageSize' => 20,
  188. 'params' => array('period1' => @$period1 ? date("d-m-Y", @$period1) : '', 'period2' => @$period2 ? date("d-m-Y", @$period2) : ''),
  189. ),));
  190. if (Yii::app()->session->get('roleName') == 'agent')
  191. {
  192. $this->render('agentreports', array('model' => $model, 'dataProvider' => $dataProvider));
  193. }
  194. else
  195. {
  196. $this->render('reports', array('model' => $model, 'dataProvider' => $dataProvider, 'teamId' => $teamId, 'teamUserId' => $teamUserId));
  197. }
  198. }
  199. else
  200. {
  201. $criteria = new CDbCriteria;
  202. $criteria->select = 't.*';
  203. $criteria->order = 'leadId DESC';
  204. if (Yii::app()->session->get('roleName') == 'agent')
  205. {
  206. $criteria->condition = 'leadSourceId = ' . self::getAgentId();
  207. $dataProvider = new CActiveDataProvider('LeadCronReport', array('criteria' => $criteria,));
  208. $this->render('agentreports', array('model' => $model, 'dataProvider' => $dataProvider, 'teamId' => $teamId, 'teamUserId' => $teamUserId));
  209. }
  210. else
  211. {
  212. $dataProvider = new CActiveDataProvider('LeadCronReport', array('criteria' => $criteria,));
  213. $this->render('reports', array('model' => $model, 'dataProvider' => $dataProvider, 'teamId' => $teamId, 'teamUserId' => $teamUserId));
  214. }
  215. }
  216. }
  217. /**
  218. * Get Agent Id.
  219. * @return int.
  220. */
  221. public static function getAgentId()
  222. {
  223. $userId = Yii::app()->session->get('id');
  224. //echo "select leadsourcecode from leadsourcemaster where leadsourceid = (select leadsourceid from user where id = $userId )"; die;
  225. $leadSourceId = Yii::app()->db->createCommand("select leadsourceid from user where id = $userId ")->queryRow();
  226. return $leadSourceId['leadsourceid'];
  227. }
  228. /**
  229. * This function is used to get column name from number of column
  230. * calls from 'actionCreateExcel' function.
  231. */
  232. public function getNameFromNumber($num) {
  233. $numeric = $num % 26;
  234. $letter = chr(65 + $numeric);
  235. $num2 = intval($num / 26);
  236. if ($num2 > 0) {
  237. return getNameFromNumber($num2 - 1) . $letter;
  238. } else {
  239. return $letter;
  240. }
  241. }
  242. public function getPageType()
  243. {
  244. return "Reports";
  245. }
  246. /**
  247. * This function is used to create excel file
  248. * calls from 'actionreports' function.
  249. */
  250. public function actionCreateExcel($getName,$getStatus,$getStage,$date1,$date2,$getleadSourceId,$getleadStatusId,$datetype,$isjunk,$mobile){
  251. $paginationExcelValue = @$_POST['fetchrecordsBydropdown'];
  252. if(isset($paginationExcelValue) && !empty($paginationExcelValue)){
  253. $explodedpaginationValue = explode('-',$paginationExcelValue);
  254. $startLimit = $explodedpaginationValue[0];
  255. $endLimit = 10000;
  256. } else {
  257. $startLimit = '';
  258. $endLimit = '';
  259. }
  260. if(isset($startLimit) && isset($endLimit) && !empty($endLimit)){
  261. $limitCond = " limit $startLimit , $endLimit";
  262. } else {
  263. $limitCond = '';
  264. }
  265. //Here is the code to set conditions for fetch records from database
  266. $condfilter = '';
  267. if((isset($getName)&& (!empty($getName))) || (isset($getStatus)&& (!empty($getStatus))) || (isset($getStage)&& (!empty($getStage)))|| (isset($date1)&& (!empty($date1))) || (isset($date2)&& (!empty($date2))))
  268. {
  269. if(isset($getName) && !empty($getName))
  270. {
  271. $condfilter .= "t.name like '%".$getName."' AND ";
  272. }
  273. if(isset($getStatus) && !empty($getStatus))
  274. {
  275. $condfilter .= "t.leadStatusId =".$getStatus." AND ";
  276. }
  277. if(isset($getStage) && !empty($getStage))
  278. {
  279. $condfilter .= "t.leadStageId =".$getStage." AND ";
  280. }
  281. if(isset($getleadSourceId) && !empty($getleadSourceId))
  282. {
  283. $condfilter .= "t.leadSourceId =".$getleadSourceId." AND ";
  284. }
  285. if(isset($isjunk) && !empty($isjunk) && $isjunk=='yes' )
  286. {
  287. $condfilter .= "t.leadStatusId != 36 AND ";
  288. }
  289. if(isset($mobile) && !empty($mobile))
  290. {
  291. $condfilter .= "t.mobile ='".$mobile."' AND ";
  292. }
  293. if((isset($date1) && !empty($date1)) && (isset($date2) && !empty($date2)))
  294. {
  295. $date1=strtotime($date1);
  296. $date2=strtotime($date2)+86399;
  297. $condfilter .= "t.".$datetype." >=".$date1." AND t.".$datetype." <= ".$date2." AND ";
  298. }
  299. if(isset($condfilter) && !empty($condfilter)){
  300. $wherecon = "WHERE ";
  301. } else {
  302. $wherecon = "";
  303. }
  304. $data=Yii::app()->db->createCommand("select t.leadId,t.name,t.mobile,t.email,t.address,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.createdOn,t.updatedOn,t.jsonAllData from leads_cron as t $wherecon $condfilter t.leadId > 0 order by t.leadId DESC $limitCond")->queryAll();
  305. }else{
  306. if(isset($getleadSourceId) && !empty($getleadSourceId))
  307. {
  308. $condfilter .= "t.leadSourceId =".$getleadSourceId." AND";
  309. }
  310. if(count($getleadStatusId) > 0){
  311. $getleadStatusId = implode(",",$getleadStatusId);
  312. $primaryDisposition=Yii::app()->db->createCommand("select leadStatus.leadStatus,leadStatusId from leadStatus where parentId IN (".@$getleadStatusId.") ")->queryAll();
  313. if(count($primaryDisposition) > 0){
  314. for($w=0;$w<count($primaryDisposition);$w++){
  315. @$PrimaryDispositionIds[] = @$primaryDisposition[$w]['leadStatusId'];
  316. }
  317. } else {
  318. @$PrimaryDispositionIds[] = '';
  319. }
  320. @$PrimaryDispositionIds = implode(",",@$PrimaryDispositionIds);
  321. } else {
  322. @$PrimaryDispositionIds = '';
  323. }
  324. if(isset($PrimaryDispositionIds) && !empty($PrimaryDispositionIds))
  325. {
  326. $condfilter .= "t.leadStatusId IN (".@$PrimaryDispositionIds.") AND ";
  327. }
  328. if(isset($condfilter) && !empty($condfilter)){
  329. $wherecon = "WHERE ";
  330. } else {
  331. $wherecon = "";
  332. }
  333. $data=Yii::app()->db->createCommand("select t.leadId,t.name,t.mobile,t.email,t.address,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.createdOn,t.updatedOn,t.jsonAllData from leads_cron as t $wherecon $condfilter t.leadId > 0 order by t.leadId DESC $limitCond ")->queryAll();
  334. }
  335. if(count($data)>0){
  336. $file_name = "lms" . "_" . date("Y-m-d_H-i", time()) . ".csv";
  337. header('Content-Type: text/html; charset=utf-8');
  338. header("Content-type: application/octet-stream");
  339. header("Content-Disposition: attachment; filename=\"$file_name\"");
  340. header("Cache-Control: cache, must-revalidate");
  341. header("Pragma: public");
  342. $col = array(
  343. 'SNO.','Name','Mobile Number',' Email Id','Address','City','State','Members','Premium','Product Details','Lead Status','Status','isJunk','Lead Source','Lead Stage','Lead Description','Activity Description','Customer Type','Product Name','Policy Type','Plan Type','Campaign Name','UTM Code','Owner', 'Upload Date','Last Modified Date','Agent-Id');
  344. foreach ($col as $key => $value) {
  345. echo $value . ",";
  346. }
  347. echo "\n";
  348. $i=1;
  349. foreach ($data as $key => $value) {
  350. $jsonArray = json_decode($value['jsonAllData']);
  351. $state = $jsonArray->state;
  352. $city = $jsonArray->city;
  353. $product = $jsonArray->product;
  354. $leadStatus = $jsonArray->leadStatus;
  355. $leadSource = $jsonArray->leadSource;
  356. $leadStage = $jsonArray->leadStage;
  357. $ActivityDescription = $jsonArray->ActivityDescription;
  358. $owner = $jsonArray->owner;
  359. $customerType = $jsonArray->customerType;
  360. $policyType = $jsonArray->policyType;
  361. $planType = $jsonArray->planType;
  362. $campaign = $jsonArray->campaign;
  363. // $sqlDesc = "select group_concat(description) as ActivityDescription from leadFollowup where leadId=".@$value["leadId"]." ";
  364. // $dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();
  365. // $leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",","/",@$dataDesc[0]['ActivityDescription']):'';
  366. if($value['currentAllocatedId']==$value['createdBy']){
  367. $owner = $owner."(C)";
  368. } else {
  369. $owner = $owner;
  370. }
  371. $uploadDate = date("d-M-Y H-i-s A",$value['createdOn']);
  372. $lastModifieddate = date("d-M-Y H-i-s A",$value['updatedOn']);
  373. echo $i++ . ",";
  374. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["name"])) . ",";
  375. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["mobile"])) . ",";
  376. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["email"])) . ",";
  377. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["address"])) . ",";
  378. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$city)) . ",";
  379. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$state)) . ",";
  380. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["members"])) . ",";
  381. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["premium"])) . ",";
  382. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value['productDetails'])) . ",";
  383. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$leadStatus)) . ",";
  384. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["status"])) . ",";
  385. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["isJunk"])) . ",";
  386. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$leadSource)) . ",";
  387. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$leadStage)) . ",";
  388. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["leadDescription"])) . ",";
  389. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$ActivityDescription)) . ",";
  390. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$customerType)) . ",";
  391. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$product)) . ",";
  392. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$policyType)) . ",";
  393. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$planType)) . ",";
  394. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$campaign)) . ",";
  395. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["leadSourceCode"])) . ",";
  396. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$owner)) . ",";
  397. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$uploadDate)) . ",";
  398. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$lastModifieddate)) . ",";
  399. echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["ws_agentid"])) . ",";
  400. echo "\n";
  401. }
  402. }
  403. else{
  404. $this->redirect('index.php?r=reports/reports');
  405. //$dataProvider=new CActiveDataProvider('LeadCronReport', array('criteria'=>@$criteria,));
  406. //$this->render('reports',array('model'=>$model,'dataProvider'=>$dataProvider));
  407. }
  408. }
  409. public function getLeadDescription($leadId){
  410. $records=Yii::app()->db->createCommand("select leadDescription from lead where leadId='".@$leadId."' ")->queryAll();
  411. if(count($records) > 0) {
  412. for($i=0;$i<count($records);$i++){
  413. $record[] = @$records[$i]['leadDescription'];
  414. }
  415. } else {
  416. echo "";
  417. }
  418. if( count(@$record) > 1){
  419. echo "<label title='".implode(',',$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";
  420. } else if( count(@$record) == 1) {
  421. echo "<label title='".implode(',',@$record)."'><a href='#'>".implode(',',$record)."</a></label>";
  422. } else {
  423. echo "";
  424. }
  425. //echo explode(',',$agentId);
  426. }
  427. public function getLeadActivityDescription($leadId){
  428. $records=Yii::app()->db->createCommand("select description from leadFollowup where leadId='".@$leadId."' ")->queryAll();
  429. if(count($records) > 0) {
  430. for($i=0;$i<count($records);$i++){
  431. $record[] = @$records[$i]['description'];
  432. }
  433. } else {
  434. echo "";
  435. }
  436. if( count(@$record) > 1){
  437. echo "<label title='".implode(',',$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";
  438. } else if( count(@$record) == 1) {
  439. echo "<label title='".implode(',',@$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";
  440. } else {
  441. echo "";
  442. }
  443. //echo explode(',',$agentId);
  444. }
  445. public function getLeadActivityDescriptionExcel($leadId){
  446. $records=Yii::app()->db->createCommand("select description from leadFollowup where leadId='".@$leadId."' ")->queryAll();
  447. if(count($records) > 0) {
  448. for($i=0;$i<count($records);$i++){
  449. $record[] = @$records[$i]['description'];
  450. }
  451. } else {
  452. echo "";
  453. }
  454. if( count(@$record) > 1){
  455. echo implode(',',$record);
  456. } else if( count(@$record) == 1) {
  457. echo implode(',',$record);
  458. } else {
  459. echo "";
  460. }
  461. //echo explode(',',$agentId);
  462. }
  463. public function actionCheckanddelete(){
  464. $reportmodel=Lead::model()->findAll();
  465. print_r($reportmodel);
  466. die;
  467. }
  468. function actioncroneJobForReport(){
  469. $sourcewiseDetails=Yii::app()->db->createCommand("select * from leads_cron_history where id='lead_updated_on' ")->queryAll();
  470. $sourcewiseDetails[0]['updatedOn'];
  471. $queryUpdatedOn = "select t.*,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName` from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where (t.updatedOn > ".@$sourcewiseDetails[0]['updatedOn']." || t.createdOn > ".@$sourcewiseDetails[0]['updatedOn']." ) order by t.leadId DESC ";
  472. $dataUpdatedOn=Yii::app()->db->createCommand($queryUpdatedOn)->queryAll();
  473. $updateleads_cron_history = " update leads_cron_history set updated_ts = '".date('Y-m-d H:i:s')."',updatedOn = '".time()."' where id='lead_updated_on' ";
  474. $executeQueryder=Yii::app()->db->createCommand($updateleads_cron_history)->execute();
  475. //$queryCreatedOn = "select t.leadId,t.name,t.mobile,t.email,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.updatedOn,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName` from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where t.leadId <= 2 and createdOn > ".@$sourcewiseDetails[0]['updatedOn']." order by t.leadId DESC ";
  476. //$dataCreatedOn=Yii::app()->db->createCommand($queryCreatedOn)->queryAll();
  477. $insertcount=0;
  478. $updatecount=0;
  479. if(count($dataUpdatedOn) > 0){
  480. foreach ($dataUpdatedOn as $key => $value) {
  481. $owner = @$value['firstName']?(@$value['firstName']." ".@$value['lastName']):"NA";
  482. $sqlDesc = "select group_concat(description) as ActivityDescription from renewalleadFollowup where leadId=".@$value["leadId"]." ";
  483. $dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();
  484. $leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",",":",@$dataDesc[0]['ActivityDescription']):'';
  485. $state = @$value["state"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["state"])):'NA';
  486. $city = @$value["city"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["city"])):'NA';
  487. $customerType = @$value["customerType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["customerType"])):'NA';
  488. $productName = @$value["productName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["productName"])):'NA';
  489. $policyType = @$value["policyType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["policyType"])):'NA';
  490. $planType = @$value["planType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["planType"])):'NA';
  491. $campaignName = @$value["campaignName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["campaignName"])):'NA';
  492. $leadSourceTitle = @$value["leadSourceTitle"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadSourceTitle"])):'NA';
  493. $leadStatus = @$value["leadStatus"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStatus"])):'NA';
  494. $leadStage = @$value["leadStage"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStage"])):'NA';
  495. $owner = @$owner?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$owner)):'NA';
  496. $agntName = @$value["agntName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["agntName"])):'NA';
  497. $leadActivity = @$leadActivity?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$leadActivity)):'NA';
  498. $newarray = array(
  499. 'state' => $state,
  500. 'city' => $city,
  501. 'customerType' => $customerType,
  502. 'product' => $productName,
  503. 'policyType' => $policyType,
  504. 'planType' => $planType,
  505. 'campaign' => $campaignName,
  506. 'leadSource' => $leadSourceTitle,
  507. 'leadStatus' => $leadStatus,
  508. 'leadStage' => $leadStage,
  509. 'owner' => addslashes($owner),
  510. 'agntName' => $agntName,
  511. 'ActivityDescription' => str_replace("'","`",$leadActivity)
  512. );
  513. $jsonData = json_encode($newarray);
  514. //$insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values (".$value['leadId'].",".$value['name'].",".$value['email'].",".$value['emailOptOut'].",".$value['mobile'].",".$value['mobileOptOut'].",".$value['address'].",".$value['stateId'].",".$value['cityId'].",".$value['customerTypeId'].",".$value['productId'].",".$value['policyTypeId'].",".$value['planTypeId'].",".$value['campaignId'].",".$value['members'].",".$value['si'].",".$value['premium'].",".$value['productDetails'].",".$value['ageGroup'].",".$value['children'].",".$value['leadSourceId'].",".$value['leadStatusId'].",".$value['leadStageId'].",".$value['isJunk'].",".$value['currentAllocatedId'].",".$value['currentAllocatedDate'].",".$value['isDuplicate'].",".$value['duplicateLeadId'].",".$value['age'].",".$value['dateonSubmit'].",".$value['fileName'].",".$value['leadDescription'].",".$value['isRead'].",".$value['status'].",".$value['ipaddress'].",".$value['agentId'].",".$value['createdOn'].",".$value['createdBy'].",".$value['updatedOn'].",".$value['updatedBy'].",".$value['ws_sourcecode'].",".$value['ws_subject'].",".$value['ws_telephone'].",".$value['ws_plan'].",".$value['ws_product'].",".$value['ws_quoteid'].",".$value['ws_proposalid'].",".$value['ws_leadstage'].",".$value['ws_agentid'].",".$value['ws_noofyears'].",".$value['ws_orgnaizationName'].",".$value['utmsource'].",".$value['isBulkImport'].",".$value['salesClosedUpdatedOn'].",'".$jsonData."',".$value['jsonDataUpdatedOn'].",".$value['jsonDataUpdatedBy'].") ";
  515. if($value['createdOn'] >= $sourcewiseDetails[0]['updatedOn']){
  516. $insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values ('".$value['leadId']."','".addslashes($value['name'])."','".$value['email']."','".$value['emailOptOut']."','".$value['mobile']."','".$value['mobileOptOut']."','".addslashes($value['address'])."','".$value['stateId']."','".$value['cityId']."','".$value['customerTypeId']."','".$value['productId']."','".$value['policyTypeId']."','".$value['planTypeId']."','".$value['campaignId']."','".addslashes($value['members'])."','".addslashes($value['si'])."','".addslashes($value['premium'])."','".addslashes($value['productDetails'])."','".$value['ageGroup']."','".$value['children']."','".$value['leadSourceId']."','".$value['leadStatusId']."','".$value['leadStageId']."','".$value['isJunk']."','".$value['currentAllocatedId']."','".$value['currentAllocatedDate']."','".$value['isDuplicate']."','".$value['duplicateLeadId']."','".$value['age']."','".$value['dateonSubmit']."','".$value['fileName']."','".addslashes($value['leadDescription'])."','".$value['isRead']."','".$value['status']."','".$value['ipaddress']."','".$value['agentId']."','".$value['createdOn']."','".$value['createdBy']."','".$value['updatedOn']."','".$value['updatedBy']."','".$value['ws_sourcecode']."','".$value['ws_subject']."','".$value['ws_telephone']."','".$value['ws_plan']."','".$value['ws_product']."','".$value['ws_quoteid']."','".$value['ws_proposalid']."','".$value['ws_leadstage']."','".$value['ws_agentid']."','".$value['ws_noofyears']."','".$value['ws_orgnaizationName']."','".$value['utmsource']."','".$value['isBulkImport']."','".$value['salesClosedUpdatedOn']."','".$jsonData."','".time()."','amit') ";
  517. $executeQuery4=Yii::app()->db->createCommand($insertQuery)->execute();
  518. //echo "<br><br>" .$insertQuery;
  519. $insertcount++;
  520. } else if($value['updatedOn'] >= $sourcewiseDetails[0]['updatedOn']){
  521. $updateQuery = " update leads_cron set leadId='".$value['leadId']."',name='".addslashes($value['name'])."',email='".$value['email']."',emailOptOut='".$value['emailOptOut']."',mobile='".$value['mobile']."',mobileOptOut='".$value['mobileOptOut']."',address='".addslashes($value['address'])."',stateId='".$value['stateId']."',cityId='".$value['cityId']."',customerTypeId='".$value['customerTypeId']."',productId='".$value['productId']."',policyTypeId='".$value['policyTypeId']."',planTypeId='".$value['planTypeId']."',campaignId='".$value['campaignId']."',members='".addslashes($value['members'])."',si='".addslashes($value['si'])."',premium='".addslashes($value['premium'])."',productDetails='".addslashes($value['productDetails'])."',ageGroup='".$value['ageGroup']."',children='".$value['children']."',leadSourceId='".$value['leadSourceId']."',leadStatusId='".$value['leadStatusId']."',leadStageId='".$value['leadStageId']."',isJunk='".$value['isJunk']."',currentAllocatedId='".$value['currentAllocatedId']."',currentAllocatedDate='".$value['currentAllocatedDate']."',isDuplicate='".$value['isDuplicate']."',duplicateLeadId='".$value['duplicateLeadId']."',age='".$value['age']."',dateonSubmit='".$value['dateonSubmit']."',fileName='".$value['fileName']."',leadDescription='".addslashes($value['leadDescription'])."',isRead='".$value['isRead']."',status='".$value['status']."',ipaddress='".$value['ipaddress']."',agentId='".$value['agentId']."',createdOn='".$value['createdOn']."',createdBy='".$value['createdBy']."',updatedOn='".$value['updatedOn']."',updatedBy='".$value['updatedBy']."',ws_sourcecode='".$value['ws_sourcecode']."',ws_subject='".$value['ws_subject']."',ws_telephone='".$value['ws_telephone']."',ws_plan='".$value['ws_plan']."',ws_product='".$value['ws_product']."',ws_quoteid='".$value['ws_quoteid']."',ws_proposalid='".$value['ws_proposalid']."',ws_leadstage='".$value['ws_leadstage']."',ws_agentid='".$value['ws_agentid']."',ws_noofyears='".$value['ws_noofyears']."',ws_orgnaizationName='".$value['ws_orgnaizationName']."',utmsource='".$value['utmsource']."',isBulkImport='".$value['isBulkImport']."',salesClosedUpdatedOn='".$value['salesClosedUpdatedOn']."',jsonAllData='".$jsonData."',jsonDataUpdatedOn='".time()."',jsonDataUpdatedBy='amit' where leadId = ".@$value['leadId']." ";
  522. $executeQuery2=Yii::app()->db->createCommand($updateQuery)->execute();
  523. //echo "<br><br>" .$updateQuery;
  524. $updatecount++;
  525. }
  526. }
  527. }
  528. echo "insert count=".$insertcount." and update count=".$updatecount;
  529. }
  530. function actioncroneFirstTime(){
  531. $startId = @$_REQUEST['startId'];
  532. $endId = @$_REQUEST['endId'];
  533. if(empty($startId) || empty($endId)){
  534. echo "parameter missing";
  535. die;
  536. }
  537. if($startId > $endId){
  538. echo "start Limit can not be greater or equal to end Limit";
  539. die;
  540. }
  541. $query = "select t.*,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName` from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where t.leadId >= $startId and t.leadId <= $endId ";
  542. $data=Yii::app()->db->createCommand($query)->queryAll();
  543. $updateleads_cron_history = " update leads_cron_history set updated_ts = '".date('Y-m-d H:i:s')."',updatedOn = '".time()."' where id='lead_updated_on' ";
  544. $executeQuery=Yii::app()->db->createCommand($updateleads_cron_history)->execute();
  545. //$r=0;
  546. foreach ($data as $key => $value) {
  547. $owner = @$value['firstName']?(@$value['firstName']." ".@$value['lastName']):"NA";
  548. $sqlDesc = "select group_concat(description) as ActivityDescription from renewalleadFollowup where leadId=".@$value["leadId"]." ";
  549. $dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();
  550. $leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",",":",@$dataDesc[0]['ActivityDescription']):'';
  551. $state = @$value["state"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["state"])):'NA';
  552. $city = @$value["city"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["city"])):'NA';
  553. $customerType = @$value["customerType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["customerType"])):'NA';
  554. $productName = @$value["productName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["productName"])):'NA';
  555. $policyType = @$value["policyType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["policyType"])):'NA';
  556. $planType = @$value["planType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["planType"])):'NA';
  557. $campaignName = @$value["campaignName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["campaignName"])):'NA';
  558. $leadSourceTitle = @$value["leadSourceTitle"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadSourceTitle"])):'NA';
  559. $leadStatus = @$value["leadStatus"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStatus"])):'NA';
  560. $leadStage = @$value["leadStage"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStage"])):'NA';
  561. $owner = @$owner?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$owner)):'NA';
  562. $agntName = @$value["agntName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["agntName"])):'NA';
  563. $leadActivity = @$leadActivity?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$leadActivity)):'NA';
  564. $newarray = array(
  565. 'state' => $state,
  566. 'city' => $city,
  567. 'customerType' => addslashes($customerType),
  568. 'product' => addslashes($productName),
  569. 'policyType' => addslashes($policyType),
  570. 'planType' => addslashes($planType),
  571. 'campaign' => addslashes($campaignName),
  572. 'leadSource' => addslashes($leadSourceTitle),
  573. 'leadStatus' => addslashes($leadStatus),
  574. 'leadStage' => addslashes($leadStage),
  575. 'owner' => addslashes($owner),
  576. 'agntName' => addslashes($agntName),
  577. 'ActivityDescription' => str_replace("'","`",$leadActivity)
  578. );
  579. $jsonData = json_encode($newarray);
  580. $insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values ('".$value['leadId']."','".addslashes($value['name'])."','".$value['email']."','".$value['emailOptOut']."','".$value['mobile']."','".$value['mobileOptOut']."','".addslashes($value['address'])."','".$value['stateId']."','".$value['cityId']."','".$value['customerTypeId']."','".$value['productId']."','".$value['policyTypeId']."','".$value['planTypeId']."','".$value['campaignId']."','".addslashes($value['members'])."','".addslashes($value['si'])."','".addslashes($value['premium'])."','".addslashes($value['productDetails'])."','".$value['ageGroup']."','".$value['children']."','".$value['leadSourceId']."','".$value['leadStatusId']."','".$value['leadStageId']."','".$value['isJunk']."','".$value['currentAllocatedId']."','".$value['currentAllocatedDate']."','".$value['isDuplicate']."','".$value['duplicateLeadId']."','".$value['age']."','".$value['dateonSubmit']."','".$value['fileName']."','".addslashes($value['leadDescription'])."','".$value['isRead']."','".$value['status']."','".$value['ipaddress']."','".$value['agentId']."','".$value['createdOn']."','".$value['createdBy']."','".$value['updatedOn']."','".$value['updatedBy']."','".$value['ws_sourcecode']."','".$value['ws_subject']."','".$value['ws_telephone']."','".$value['ws_plan']."','".$value['ws_product']."','".$value['ws_quoteid']."','".$value['ws_proposalid']."','".$value['ws_leadstage']."','".$value['ws_agentid']."','".$value['ws_noofyears']."','".$value['ws_orgnaizationName']."','".$value['utmsource']."','".$value['isBulkImport']."','".$value['salesClosedUpdatedOn']."','".$jsonData."','".time()."','amit') ";
  581. $executeQuerys=Yii::app()->db->createCommand($insertQuery)->execute();
  582. //echo $value['leadId']."<br>";
  583. // $r++;
  584. }
  585. }
  586. public function actionagentwisetotalcalls(){
  587. $time = time();
  588. $parser = new CHtmlPurifier();
  589. $afterMonth = $parser->purify(intval(@$_REQUEST['month']));
  590. $afterDate = $parser->purify(intval(@$_REQUEST['date']));
  591. $afterYear = $parser->purify(intval(@$_REQUEST['year']));
  592. if(isset($afterMonth) && !empty($afterMonth) && isset($afterDate) && !empty($afterDate) && isset($afterYear) && !empty($afterYear)){
  593. $start = mktime(0, 0, 0,$afterMonth,$afterDate,$afterYear);
  594. $end = mktime(23, 59, 59,$afterMonth,$afterDate,$afterYear);
  595. } else {
  596. $start = mktime(0, 0, 0);
  597. $end = mktime(23, 59, 59);
  598. }
  599. $date = date('d_M_Y',$start)."_";
  600. $host = '10.216.6.59'; // MYSQL database host adress
  601. $db = 'lms'; // MYSQL database name
  602. $user = 'root'; // Mysql Datbase user
  603. $pass = '0kwp6aNR'; // Mysql Datbase password
  604. $link=mysql_connect($host,$user,$pass);
  605. mysql_select_db($db,$link);
  606. $table='report_agentwisetotalcalls';
  607. $filename = "filesystem/".$date.$table.'.csv';
  608. $csv_terminated = "\n";
  609. $csv_separator = ",";
  610. $csv_enclosed = '"';
  611. $csv_escaped = "\\";
  612. $totalleadstatusids = '7,8,9,10,37,38,11,12,42,43,13,14,15,16,17,18,19,30,21,22,23,24,25,26,27,28,29,30,33,39,43';
  613. mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','agentwisetotalcalls','".@$filename."','".@$time."','".@$_SESSION['id']."','".@$time."','".@$_SESSION['id']."')");
  614. $chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());
  615. $tableres ='create table IF NOT EXISTS '.$table.' as SELECT
  616. user.userName as `Caller Name`,
  617. SUM(CASE WHEN lead.currentAllocatedId=user.id AND lead.leadStatusId IN ('.@$totalleadstatusids.') THEN 1 ELSE 0 END) AS `Total Diposed`,
  618. SUM(CASE WHEN LS.`leadStatusId` = 7 THEN 1 ELSE 0 END) AS `Sale Done`,
  619. SUM(CASE WHEN LS.`leadStatusId` = 8 THEN 1 ELSE 0 END) AS `Follow Up`,
  620. SUM(CASE WHEN LS.`leadStatusId` = 9 THEN 1 ELSE 0 END) AS `Do Not Call - Will Buy self on Website`,
  621. SUM(CASE WHEN LS.`leadStatusId` = 10 THEN 1 ELSE 0 END) AS `Duplicate - Already In Talk With Team`,
  622. SUM(CASE WHEN LS.`leadStatusId` = 37 THEN 1 ELSE 0 END) AS `In Contact with Oflline Agent`,
  623. SUM(CASE WHEN LS.`leadStatusId` = 38 THEN 1 ELSE 0 END) AS `In Touch With Other Partner`,
  624. SUM(CASE WHEN LS.`leadStatusId` = 11 THEN 1 ELSE 0 END) AS `Short Hangup`,
  625. SUM(CASE WHEN LS.`leadStatusId` = 12 THEN 1 ELSE 0 END) AS `Call Later`,
  626. SUM(CASE WHEN LS.`leadStatusId` = 42 THEN 1 ELSE 0 END) AS `Ringing Less Than Five Tries`,
  627. SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off Less than Five Tries`,
  628. SUM(CASE WHEN LS.`leadStatusId` = 13 THEN 1 ELSE 0 END) AS `Not Interested - Fund Problem`,
  629. SUM(CASE WHEN LS.`leadStatusId` = 14 THEN 1 ELSE 0 END) AS `Just Checking Website`,
  630. SUM(CASE WHEN LS.`leadStatusId` = 15 THEN 1 ELSE 0 END) AS `Not Interested - Insured From Employer`,
  631. SUM(CASE WHEN LS.`leadStatusId` = 16 THEN 1 ELSE 0 END) AS `Not Interested - High Premium`,
  632. SUM(CASE WHEN LS.`leadStatusId` = 17 THEN 1 ELSE 0 END) AS `Under Age`,
  633. SUM(CASE WHEN LS.`leadStatusId` = 18 THEN 1 ELSE 0 END) AS `Not Visited Website`,
  634. SUM(CASE WHEN LS.`leadStatusId` = 19 THEN 1 ELSE 0 END) AS `Do Not Call`,
  635. SUM(CASE WHEN LS.`leadStatusId` = 20 THEN 1 ELSE 0 END) AS `Not Interested - General`,
  636. SUM(CASE WHEN LS.`leadStatusId` = 21 THEN 1 ELSE 0 END) AS `Over Age`,
  637. SUM(CASE WHEN LS.`leadStatusId` = 22 THEN 1 ELSE 0 END) AS `Not Interested - Already Insured`,
  638. SUM(CASE WHEN LS.`leadStatusId` = 23 THEN 1 ELSE 0 END) AS `Duplicate Data - Not Interested`,
  639. SUM(CASE WHEN LS.`leadStatusId` = 24 THEN 1 ELSE 0 END) AS `Not Eligible - PED`,
  640. SUM(CASE WHEN LS.`leadStatusId` = 25 THEN 1 ELSE 0 END) AS `Not Interested - Govt. Employee`,
  641. SUM(CASE WHEN LS.`leadStatusId` = 26 THEN 1 ELSE 0 END) AS `Not Interested - Senior Citizen`,
  642. SUM(CASE WHEN LS.`leadStatusId` = 27 THEN 1 ELSE 0 END) AS `Wrong Number`,
  643. SUM(CASE WHEN LS.`leadStatusId` = 28 THEN 1 ELSE 0 END) AS `Customer Not Available`,
  644. SUM(CASE WHEN LS.`leadStatusId` = 29 THEN 1 ELSE 0 END) AS `Language Barrier`,
  645. SUM(CASE WHEN LS.`leadStatusId` = 30 THEN 1 ELSE 0 END) AS `No Response`,
  646. SUM(CASE WHEN LS.`leadStatusId` = 33 THEN 1 ELSE 0 END) AS `Invalid Number`,
  647. SUM(CASE WHEN LS.`leadStatusId` = 36 THEN 1 ELSE 0 END) AS `Junk`,
  648. SUM(CASE WHEN LS.`leadStatusId` = 39 THEN 1 ELSE 0 END) AS `Ringing no response after five tries`,
  649. SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off/ Not Reachable after 5 tries`
  650. FROM `lead` join user on user.id=lead.currentAllocatedId left join leadStatus LS on LS.leadStatusId=lead.leadStatusId where lead.leadStatusId !=36 AND lead.updatedOn >= "'.@$start.'" and lead.updatedOn <= "'.@$end.'" group by lead.currentAllocatedId';
  651. $esx = mysql_query($tableres) or die(mysql_error());
  652. $queryforinsert = "Insert into ".$table." (`Caller Name`,`Total Diposed`,`Sale Done`,`Follow Up`,`Do Not Call - Will Buy self on Website`,`Duplicate - Already In Talk With Team`,`In Contact with Oflline Agent`,`In Touch With Other Partner`,`Short Hangup`,`Call Later`,`Ringing Less Than Five Tries`,`Switched Off Less than Five Tries`,`Not Interested - Fund Problem`,`Just Checking Website`,`Not Interested - Insured From Employer`,`Not Interested - High Premium`,`Under Age`,`Not Visited Website`,`Do Not Call`,`Not Interested - General`,`Over Age`,`Not Interested - Already Insured`,`Duplicate Data - Not Interested`,`Not Eligible - PED`,`Not Interested - Govt. Employee`,`Not Interested - Senior Citizen`,`Wrong Number`,`Customer Not Available`,`Language Barrier`,`No Response`,`Invalid Number`,`Junk`,`Ringing no response after five tries`,`Switched Off/ Not Reachable after 5 tries`)
  653. select 'Total',SUM(`Total Diposed`), SUM(`Sale Done`), SUM(`Follow Up`), SUM(`Do Not Call - Will Buy self on Website`), SUM(`Duplicate - Already In Talk With Team`), SUM(`In Contact with Oflline Agent`), SUM(`In Touch With Other Partner`), SUM(`Short Hangup`), SUM(`Call Later`), SUM(`Ringing Less Than Five Tries`), SUM(`Switched Off Less than Five Tries`), SUM(`Not Interested - Fund Problem`), SUM(`Just Checking Website`), SUM(`Not Interested - Insured From Employer`), SUM(`Not Interested - High Premium`), SUM(`Under Age`), SUM(`Not Visited Website`), SUM(`Do Not Call`), SUM(`Not Interested - General`), SUM(`Over Age`), SUM(`Not Interested - Already Insured`), SUM(`Duplicate Data - Not Interested`), SUM(`Not Eligible - PED`), SUM(`Not Interested - Govt. Employee`), SUM(`Not Interested - Senior Citizen`), SUM(`Wrong Number`), SUM(`Customer Not Available`), SUM(`Language Barrier`), SUM(`No Response`), SUM(`Invalid Number`), SUM(`Junk`), SUM(`Ringing no response after five tries`), SUM(`Switched Off/ Not Reachable after 5 tries`) from ".$table."";
  654. mysql_query($queryforinsert);
  655. $sql_query = "select * from $table";
  656. // Gets the data from the database
  657. $result = mysql_query($sql_query) or die(mysql_error());
  658. $fields_cnt = mysql_num_fields($result);
  659. $schema_insert = '';
  660. for ($i = 0; $i < $fields_cnt; $i++)
  661. {
  662. $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
  663. stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
  664. $schema_insert .= $l;
  665. $schema_insert .= $csv_sep

Large files files are truncated, but you can click here to view the full file