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

/lms_debug/protected/controllers/DashboardController_11dec2014.php

https://gitlab.com/badelal143/lms_debug
PHP | 1587 lines | 1222 code | 230 blank | 135 comment | 151 complexity | b53882cc1d7d83e45390b21b64b66841 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 DashboardController 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', array(
  11. 'CHttpCacheFilter + view',
  12. 'cacheControl' => " max-age=604800, must-revalidate",
  13. )); // perform access control for CRUD operations
  14. }
  15. public function actions()
  16. {
  17. return array(
  18. // captcha action renders the CAPTCHA image displayed on the contact page
  19. 'captcha' => array(
  20. 'class' => 'CCaptchaAction',
  21. 'backColor' => 0xFFFFFF,
  22. ),
  23. // page action renders "static" pages stored under 'protected/views/site/pages'
  24. // They can be accessed via: index.php?r=site/page&view=FileName
  25. 'page' => array(
  26. 'class' => 'CViewAction',
  27. ),
  28. );
  29. }
  30. /**
  31. * This is the 'roles' action that is invoked
  32. * when an action is not explicitly requested by users.
  33. */
  34. protected function array_flatten($array)
  35. {
  36. if (!is_array($array))
  37. {
  38. return FALSE;
  39. }
  40. $result = array();
  41. foreach ($array as $key => $value)
  42. {
  43. if (is_array($value))
  44. {
  45. $result = array_merge($result, $this->array_flatten($value));
  46. }
  47. else
  48. {
  49. $result[$key] = $value;
  50. }
  51. }
  52. return $result;
  53. }
  54. /**
  55. * Action dashboard. //added by shakti. Please remove if not used.
  56. */
  57. public function actiondashboard()
  58. {
  59. //update table leadallcount.
  60. $userId = Yii::app()->session['id'];
  61. $financialyear = @$_POST['financialyear'] ? $_POST['financialyear'] : date('Y');
  62. $monthKey = date('m');
  63. $userCountRecord = Yii::app()->db->createCommand("select * from leadallcount where userId = $userId && accesskey = $financialyear")->queryRow();
  64. if(!$userCountRecord)
  65. {
  66. $queryString = "insert into leadallcount (userid, accesskey, value, count, createdOn, createdBy, updatedOn, updatedBy) values(";
  67. $leadStatusRecords = Yii::app()->db->createCommand('select leadStatusId from leadstatus')->queryAll();
  68. $createdOn = time();
  69. foreach($leadStatusRecords as $leadStatusId)
  70. {
  71. $statusId = $leadStatusId['leadStatusId'];
  72. $getMTDStatusCount = DashboardController::getMTD($statusId);
  73. $insertMTDStatusCount = "$queryString $userId, '$financialyear-$monthKey', $statusId, $getMTDStatusCount, $createdOn, $userId, $createdOn, $userId)";
  74. Yii::app()->db->createCommand($insertMTDStatusCount)->execute();
  75. $getYTDStatusCount = DashboardController::getYTD($statusId);
  76. $insertYTDStatusCount = "$queryString $userId, '$financialyear', $statusId, $getYTDStatusCount, $createdOn, $userId, $createdOn, $userId)";
  77. Yii::app()->db->createCommand($insertYTDStatusCount)->execute();
  78. }
  79. }
  80. //
  81. if (empty(Yii::app()->session['id']) or Yii::app()->session['id'] == '')
  82. {
  83. $this->redirect(array('site/index'));
  84. }
  85. $leadCond = '';
  86. $PiechartCond = '';
  87. $NotificationCond1 = '';
  88. $NotificationCond2 = '';
  89. $currentDateNot = date('d-m-Y');
  90. $currentTimestamp = strtotime($currentDateNot);
  91. if (isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
  92. {
  93. $searchDate = $_POST['searchDashboard'];
  94. $searchTimestamp = strtotime(@$searchDate);
  95. $searchEndTimestamp = $searchTimestamp + 86400;
  96. $leadCond = " and lead.updatedOn >= '" . @$searchTimestamp . "' and lead.updatedOn < '" . @$searchEndTimestamp . "'";
  97. $PiechartCond = " and leadSourceMaster.updatedOn >= '" . @$searchTimestamp . "' and leadSourceMaster.updatedOn < '" . @$searchEndTimestamp . "'";
  98. $NotificationCond1 = " and notification.updatedOn >= '" . @$searchTimestamp . "' and notification.updatedOn < '" . @$searchEndTimestamp . "' AND (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(notification.startDate, '%d-%m-%Y') AND STR_TO_DATE(notification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  99. $NotificationCond2 = " and notification.updatedOn >= '" . @$searchTimestamp . "' and notification.updatedOn < '" . @$searchEndTimestamp . "' AND (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(notification.startDate, '%d-%m-%Y') AND STR_TO_DATE(notification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  100. }
  101. if (Yii::app()->session->get('isAllocator') == 'yes' or Yii::app()->session->get('type') == 'superadmin' or Yii::app()->session->get('roleId') == 1)
  102. {
  103. $LeasStatusCond = '';
  104. }
  105. else
  106. {
  107. $LeasStatusCond = ' AND lead.currentAllocatedId = ' . Yii::app()->session->get('id') . '';
  108. }
  109. $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
  110. $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
  111. $CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
  112. $CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
  113. $where = '';
  114. if (Yii::app()->session->get('type') != 'superadmin')
  115. {
  116. $where = ' where notificationMapping.roleId="' . Yii::app()->session->get('roleId') . '"';
  117. $cls = " AND (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(notification.startDate, '%d-%m-%Y') AND STR_TO_DATE(notification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  118. $notifications = Yii::app()->db->createCommand("select notification.* from notification left join notificationMapping on notificationMapping.notificationId=notification.notificationId $where " . $NotificationCond1 . " " . @$cls . " group by notification.notificationId ")->queryAll();
  119. }
  120. else
  121. {
  122. $cls = " WHERE (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(notification.startDate, '%d-%m-%Y') AND STR_TO_DATE(notification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  123. $notifications = Yii::app()->db->createCommand("select notification.* from notification left join notificationMapping on notificationMapping.notificationId=notification.notificationId " . @$cls . " " . $NotificationCond2 . " group by notification.notificationId ")->queryAll();
  124. }
  125. $sourcewiseDetails = Yii::app()->db->createCommand("select leadSourceMaster.* from leadSourceMaster ")->queryAll();
  126. $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();
  127. $total_firstLevel = array();
  128. $total_nextLevel = array();
  129. $managerIds = self::fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));
  130. if (empty($managerIds))
  131. {
  132. $managerIds[] = $_SESSION['id'];
  133. }
  134. else
  135. {
  136. $pusharray = array();
  137. $pusharray[] = @$_SESSION['id'];
  138. $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));
  139. }
  140. if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")
  141. {
  142. $teamLeaderTeamUserIdsRes = array();
  143. $teamLeaderTeamUserIds = Yii::app()->db->createCommand("select id as userId from user where id IN (select distinct userId from teamUserMapping where teamId IN (select distinct teamId from teamUserMapping where userId='" . Yii::app()->session->get('id') . "') ) and status='Active' and type!='superadmin' ")->queryAll();
  144. if (count($teamLeaderTeamUserIds) > 0)
  145. {
  146. foreach ($teamLeaderTeamUserIds as $keyd => $valued)
  147. {
  148. $teamLeaderTeamUserIdsRes[] = $valued['userId'];
  149. }
  150. $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));
  151. }
  152. }
  153. $managerDetailsArr = self::ManagerDetails($managerIds);
  154. Yii::app()->session['relationalManager'] = $managerIds;
  155. //$sourcewiseDetailsWithPiechart=Yii::app()->db->createCommand("SELECT leadSourceMaster.*,count(*) as total FROM `leadSourceMaster` left join lead on leadSourceMaster.leadSourceId=lead.leadSourceId where leadSourceMaster.status='Active' ".$PiechartCond." group by leadSourceMaster.leadSourceId ")->queryAll();
  156. $speedmeterUser = @$_REQUEST['speedmeterUser'];
  157. if (isset($speedmeterUser) && !empty($speedmeterUser))
  158. {
  159. $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);
  160. }
  161. else
  162. {
  163. $totalAmountOfSpeedometer = 0;
  164. }
  165. $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `leadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";
  166. // $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder,SUM(IF(lead.updatedOn >= '".@$CurrentMonth_first_minute."' and lead.updatedOn <= '".@$CurrentMonth_last_minute."' and ls.leadstatusId=lead.leadstatusId and u.status='Active',1,0)) as MTD,SUM(IF(lead.updatedOn >= '".@$CurrentYear_first_minute."' and lead.updatedOn <= '".@$CurrentYear_last_minute."' and u.status='Active',1,0)) as YTD FROM `leadstatus` ls join lead on lead.leadstatusId=ls.leadstatusId left join user u on u.id=lead.currentAllocatedId where ls.status='Active' ".$leadCond." ".$LeasStatusCond." group By lead.leadstatusId order by ls.sortOrder ASC";
  167. $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();
  168. $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(
  169. 'keyField' => 'leadstatusId',
  170. 'totalItemCount' => count($count),
  171. 'sort' => array(
  172. 'attributes' => array(
  173. 'leadStatusId',
  174. ),
  175. ),
  176. 'pagination' => array(
  177. 'pageSize' => 10,
  178. 'pageVar' => 'gyu', //page selector
  179. ),
  180. ));
  181. if (Yii::app()->session->get('isAllocator') == 'yes')
  182. {
  183. $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();
  184. $impresuser = @$allocatorTl[0]['TLIDS'];
  185. }
  186. else
  187. {
  188. $impresuser = $this->implodeResult(Yii::app()->session['relationalManager']);
  189. }
  190. //calculate performance
  191. /*
  192. $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
  193. $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;
  194. $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;
  195. $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);
  196. //echo date('d M Y :: H:i:s',$CurrentMonth_first_minute); die;
  197. $filtercaller = @$_REQUEST['User']['userName'];
  198. $filtersource = @$_REQUEST['LeadSourceMaster']['leadSourceId'];
  199. $filterdate1 = @$_REQUEST['Lead']['createdOn'];
  200. $filterdate2 = @$_REQUEST['Lead']['createdOn2'];
  201. $filterCond = '';
  202. if (isset($filtercaller) && !empty($filtercaller))
  203. {
  204. $filterCond .= " AND lead.currentAllocatedId = " . $filtercaller . "";
  205. }
  206. if (isset($filtersource) && !empty($filtersource))
  207. {
  208. $filterCond .= " AND lead.leadSourceId = " . $filtersource . "";
  209. }
  210. if (isset($filterdate1) && !empty($filterdate1) && isset($filterdate2) && !empty($filterdate2))
  211. {
  212. $date1Filer = strtotime($filterdate1);
  213. $date2Filer = strtotime($filterdate2);
  214. $filterCond .= " AND lead.updatedOn >= " . $date1Filer . " AND lead.updatedOn <= " . $date2Filer . "";
  215. }
  216. */
  217. /* commented by shakti
  218. $teamworkloadSql = "SELECT u.firstName,u.lastName,u.id as Id,SUM(IF(lead.updatedOn >= '" . @$today12PM . "' and lead.updatedOn <= '" . @$tomorrow12PM . "',1,0)) as T,SUM(IF(lead.updatedOn >= '" . @$yesterday12PM . "' and lead.updatedOn <= '" . @$today12PM . "',1,0)) as Y,SUM(IF(lead.updatedOn >= '" . @$CurrentMonth_first_minute . "' and lead.updatedOn <= '" . @$CurrentMonth_last_minute . "',1,0)) as M,SUM(IF(lead.updatedOn >= '" . @$LastMonth_first_minute . "' and lead.updatedOn <= '" . @$CurrentMonth_first_minute . "',1,0)) as LM from user u left join lead on lead.currentAllocatedId=u.Id where u.Id IN (" . @$impresuser . ") and u.status='Active' and lead.currentAllocatedId IN (" . @$impresuser . ") " . @$filterCond . " " . $leadCond . " group By u.id ";
  219. $countTeamworkLoad = Yii::app()->db->createCommand($teamworkloadSql)->queryAll();
  220. $dataProviderWorkload = new CSqlDataProvider($teamworkloadSql, array(
  221. 'keyField' => 'Id',
  222. 'totalItemCount' => count($countTeamworkLoad),
  223. 'sort' => array(
  224. 'attributes' => array(
  225. 'Id',
  226. ),
  227. ),
  228. 'pagination' => array(
  229. 'pageSize' => 10,
  230. 'pageVar' => 'workload', //page selector
  231. ),
  232. ));
  233. */
  234. self::getRequiredVariables();
  235. $callerTeamSql = "SELECT user.firstName,user.lastName,user.id as Id,SUM(IF(lead.updatedOn >= '" . @$today12PM . "' and lead.updatedOn <= '" . @$tomorrow12PM . "' and lead.leadSourceId = 1,1,0)) as WT,SUM(IF(lead.updatedOn >= '" . @$yesterday12PM . "' and lead.updatedOn <= '" . @$today12PM . "' and lead.leadSourceId = 1,1,0)) as WY,SUM(IF(lead.updatedOn >= '" . @$CurrentMonth_first_minute . "' and lead.updatedOn <= '" . @$CurrentMonth_last_minute . "' and lead.leadSourceId = 1,1,0)) as WM,SUM(IF(lead.updatedOn >= '" . @$LastMonth_first_minute . "' and lead.updatedOn <= '" . @$CurrentMonth_first_minute . "' and lead.leadSourceId = 1,1,0)) as WLM,SUM(IF(lead.updatedOn >= '" . @$today12PM . "' and lead.updatedOn <= '" . @$tomorrow12PM . "' and lead.leadSourceId != 1,1,0)) as OT,SUM(IF(lead.updatedOn >= '" . @$yesterday12PM . "' and lead.updatedOn <= '" . @$today12PM . "' and lead.leadSourceId != 1,1,0)) as OY,SUM(IF(lead.updatedOn >= '" . @$CurrentMonth_first_minute . "' and lead.updatedOn <= '" . @$CurrentMonth_last_minute . "' and lead.leadSourceId != 1,1,0)) as OM,SUM(IF(lead.updatedOn >= '" . @$LastMonth_first_minute . "' and lead.updatedOn <= '" . @$CurrentMonth_first_minute . "' and lead.leadSourceId != 1,1,0)) as OLM from user left join lead on lead.currentAllocatedId=user.Id where user.Id = " . Yii::app()->session['id'] . " and user.status='Active' and lead.currentAllocatedId =" . Yii::app()->session['id'] . " " . $leadCond . " ";
  236. $countcallerInfo = Yii::app()->db->createCommand($callerTeamSql)->queryAll();
  237. $dataProvidercaller = new CSqlDataProvider($callerTeamSql, array(
  238. 'keyField' => 'Id',
  239. 'totalItemCount' => count($countcallerInfo),
  240. 'sort' => array(
  241. 'attributes' => array(
  242. 'Id',
  243. ),
  244. ),
  245. 'pagination' => false,
  246. ));
  247. $dataProviderPerf = self::getDataProviderPerf();
  248. $this->render('dashboard', array('LeadStatus' => @$LeadStatus, 'notifications' => @$notifications, 'sourcewiseDetails' => @$sourcewiseDetails, 'managerDetailsArr' => @$managerDetailsArr, 'totalAmountOfSpeedometer' => @$totalAmountOfSpeedometer, 'dataProviderc' => $dataProviderc, 'dataProviderPerf' => $dataProviderPerf, 'dataProviderWorkload' => @$dataProviderWorkload, 'dataProvidercaller' => @$dataProvidercaller, 'dataProvideradmin' => @$dataProvideradmin, 'impresuser' => @$impresuser));
  249. }
  250. /**
  251. * Get Lead All Counts. Fetch & return the counts relevent to the access key & status of logged in user.
  252. * @return array
  253. */
  254. public static function getLeadAllCounts()
  255. {
  256. $userId = Yii::app()->session['id'];
  257. return Yii::app()->db->createCommand("select * from leadallcount where userid = $userId")->queryAll();
  258. }
  259. /**
  260. * Get Lead Status Count.
  261. * @return int
  262. */
  263. public static function getLeadStatusCount($leadStatusId, $accessKey, $leadAllCounts)
  264. {
  265. foreach($leadAllCounts as $record)
  266. {
  267. if($record['accesskey'] == $accessKey && $record['value'] == $leadStatusId)
  268. {
  269. return $record['count'];
  270. }
  271. }
  272. }
  273. //Added by shakti upto here
  274. /**
  275. * This is the 'module master' action that is invoked
  276. * when an action is not explicitly requested by users.
  277. */
  278. public function actionmodule_master()
  279. {
  280. // renders the view file 'protected/views/site/index.php'
  281. // using the default layout 'protected/views/layouts/main.php'
  282. $model = new ModuleMaster();
  283. $model->scenario = 'ModuleMaster';
  284. if (!empty($_GET['moduleId']))
  285. {
  286. $moduleId = $_GET['moduleId'];
  287. $model = ModuleMaster::model()->findByPk($moduleId);
  288. }
  289. if (!empty($_POST['ModuleMaster']['moduleId']))
  290. {
  291. $moduleId = $_POST['ModuleMaster']['moduleId'];
  292. $model = ModuleMaster::model()->findByPk($moduleId);
  293. }
  294. if (isset($_POST['ModuleMaster']))
  295. {
  296. $model->setAttributes(array(
  297. 'createdOn' => time(), //additional data you want to insert
  298. 'updatedOn' => time(), //additional
  299. 'updatedBy' => time(), //additional
  300. 'createdBy' => time(),
  301. 'status' => 'Activated', //additional //additional
  302. ));
  303. foreach ($_POST['ModuleMaster'] as $name => $value)
  304. {
  305. $model->$name = $value;
  306. }
  307. if ($model->validate())
  308. {
  309. $model->save();
  310. $model2 = ModuleMaster::model()->findAll();
  311. $this->render('module_master', array('model' => $model, 'model2' => $model2));
  312. Yii::app()->user->setFlash('module_master_msg_success', 'Successfully Inserted');
  313. }
  314. else
  315. {
  316. Yii::app()->user->setFlash('module_master_msg_error', 'Please check. error occured');
  317. $this->render('module_master', array('model' => $model));
  318. }
  319. }
  320. else
  321. {
  322. $model2 = ModuleMaster::model()->findAll();
  323. //Yii::app()->user->setFlash('lead_stage_msg_error','Please check. error occured');
  324. $this->render('module_master', array('model' => $model, 'model2' => $model2));
  325. }
  326. }
  327. /**
  328. * This is the action to handle external exceptions.
  329. */
  330. public function actionError()
  331. {
  332. if ($error = Yii::app()->errorHandler->error)
  333. {
  334. if (Yii::app()->request->isAjaxRequest)
  335. echo $error['message'];
  336. else
  337. $this->render('error', $error);
  338. }
  339. }
  340. /**
  341. * Displays the contact page
  342. */
  343. public function actionContact()
  344. {
  345. $model = new ContactForm;
  346. if (isset($_POST['ContactForm']))
  347. {
  348. $model->attributes = $_POST['ContactForm'];
  349. if ($model->validate())
  350. {
  351. $name = '=?UTF-8?B?' . base64_encode($model->name) . '?=';
  352. $subject = '=?UTF-8?B?' . base64_encode($model->subject) . '?=';
  353. $headers = "From: $name <{$model->email}>\r\n" .
  354. "Reply-To: {$model->email}\r\n" .
  355. "MIME-Version: 1.0\r\n" .
  356. "Content-type: text/plain; charset=UTF-8";
  357. mail(Yii::app()->params['adminEmail'], $subject, $model->body, $headers);
  358. Yii::app()->user->setFlash('contact', 'Thank you for contacting us. We will respond to you as soon as possible.');
  359. $this->refresh();
  360. }
  361. }
  362. $this->render('contact', array('model' => $model));
  363. }
  364. /**
  365. * Displays the login page
  366. */
  367. public function actionLogin()
  368. {
  369. $model = new User;
  370. // if it is ajax validation request
  371. if (isset($_POST['ajax']) && $_POST['ajax'] === 'login-form')
  372. {
  373. echo CActiveForm::validate($model);
  374. Yii::app()->end();
  375. }
  376. // collect user input data
  377. if (isset($_POST['User']))
  378. {
  379. $model->attributes = $_POST['User'];
  380. // validate user input and redirect to the previous page if valid
  381. if ($model->validate() && $model->login())
  382. {
  383. $this->redirect(array('masters/dashboard'));
  384. }
  385. else
  386. {
  387. $this->redirect(Yii::app()->user->returnUrl);
  388. }
  389. }
  390. // display the login form
  391. $this->render('login', array('model' => $model));
  392. }
  393. /**
  394. * Logs out the current user and redirect to homepage.
  395. */
  396. public function actionLogout()
  397. {
  398. Yii::app()->user->logout();
  399. $this->redirect(Yii::app()->homeUrl);
  400. }
  401. public function getPageType()
  402. {
  403. return "";
  404. }
  405. public function actionViewFancyboxData()
  406. {
  407. $userId = @$_REQUEST['id'];
  408. $model = new User;
  409. $model = User::model()->findByPk($userId);
  410. if (!Yii::app()->request->isAjaxRequest)
  411. {
  412. // $this->render('yourView');
  413. }
  414. else
  415. {
  416. $this->renderPartial('/masters/partialNotification');
  417. Yii::app()->end();
  418. }
  419. }
  420. public function actionViewWorkLoadData()
  421. {
  422. if (!Yii::app()->request->isAjaxRequest)
  423. {
  424. // $this->render('yourView');
  425. }
  426. else
  427. {
  428. $this->renderPartial('/leads/partialWorkLoadData');
  429. Yii::app()->end();
  430. }
  431. }
  432. public function actionViewLeadSummaryData()
  433. {
  434. //$this->layout='column3';
  435. $this->layout = false;
  436. $parser = new CHtmlPurifier();
  437. $leadStatusId = $parser->purify(intval(@$_REQUEST['leadStatusId']));
  438. /* $model=new Lead;
  439. $model=Lead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");
  440. */
  441. if (!Yii::app()->request->isAjaxRequest)
  442. {
  443. $this->render('/leads/partialLeadSummary', array('leadStatusId' => $leadStatusId));
  444. }
  445. else
  446. {
  447. $this->layout=false;
  448. $this->render('/leads/partialLeadSummary', array('leadStatusId' => $leadStatusId));
  449. Yii::app()->end();
  450. }
  451. }
  452. public function actionRenewalViewLeadSummaryData()
  453. {
  454. //$this->layout='column3';
  455. $this->layout = false;
  456. $res = new MySanitizeClass;
  457. $leadStatusId = $res->sanitize_description(@$_REQUEST['leadStatusId']);
  458. /* $model=new Lead;
  459. $model=Lead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");
  460. */ if (!Yii::app()->request->isAjaxRequest)
  461. {
  462. $this->render('/renewalleads/renewalpartialLeadSummary', array('leadStatusId' => $leadStatusId));
  463. }
  464. else
  465. {
  466. //$this->layout=false;
  467. $this->render('/renewalleads/renewalpartialLeadSummary', array('leadStatusId' => $leadStatusId));
  468. Yii::app()->end();
  469. }
  470. }
  471. function getparentLeadStatus($parentId)
  472. {
  473. $primary = LeadStatus::model()->findByPk($parentId);
  474. if ($primary['parentId'] > 0)
  475. {
  476. $parentName = LeadStatus::model()->findByPk($primary['parentId']);
  477. $pname = $parentName['leadStatus'];
  478. }
  479. else
  480. {
  481. $pname = "Primary";
  482. }
  483. return $pname;
  484. }
  485. public function actionViewAllWorkLoadData()
  486. {
  487. function ManagerDetails($userIds)
  488. {
  489. $userIds = UserWithManagerIdsImploded($userIds);
  490. if ($userIds != '')
  491. {
  492. return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
  493. }
  494. }
  495. function UserWithManagerIdsImploded($userIds)
  496. {
  497. return $userIds = implode(",", $userIds);
  498. }
  499. $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
  500. if (!Yii::app()->request->isAjaxRequest)
  501. {
  502. // $this->render('yourView');
  503. }
  504. else
  505. {
  506. $this->renderPartial('/leads/partialAllWorkLoadData', array('managerDetailsArr' => $managerDetailsArr));
  507. Yii::app()->end();
  508. }
  509. }
  510. public function actionViewAllPerformanceData()
  511. {
  512. function ManagerDetails($userIds)
  513. {
  514. $userIds = UserWithManagerIdsImploded($userIds);
  515. if ($userIds != '')
  516. {
  517. return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
  518. }
  519. }
  520. function UserWithManagerIdsImploded($userIds)
  521. {
  522. return $userIds = implode(",", $userIds);
  523. }
  524. $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
  525. if (!Yii::app()->request->isAjaxRequest)
  526. {
  527. $this->render('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
  528. // $this->render('yourView');
  529. }
  530. else
  531. {
  532. $this->renderPartial('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
  533. Yii::app()->end();
  534. }
  535. }
  536. public function actionrenewal()
  537. {
  538. //$this->redirect(array('dashboard/dashboard'));
  539. $leadCond = '';
  540. $PiechartCond = '';
  541. $NotificationCond1 = '';
  542. $NotificationCond2 = '';
  543. $currentDateNot = date('d-m-Y');
  544. $currentTimestamp = strtotime($currentDateNot);
  545. if (isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
  546. {
  547. $searchDate = $_POST['searchDashboard'];
  548. $searchTimestamp = strtotime(@$searchDate);
  549. $searchEndTimestamp = $searchTimestamp + 86400;
  550. $leadCond = " and renewallead.updatedOn >= '" . @$searchTimestamp . "' and renewallead.updatedOn < '" . @$searchEndTimestamp . "'";
  551. $PiechartCond = " and renewalleadSourceMaster.updatedOn >= '" . @$searchTimestamp . "' and renewalleadSourceMaster.updatedOn < '" . @$searchEndTimestamp . "'";
  552. $NotificationCond1 = " and renewalnotification.updatedOn >= '" . @$searchTimestamp . "' and renewalnotification.updatedOn < '" . @$searchEndTimestamp . "' AND (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(notification.startDate, '%d-%m-%Y') AND STR_TO_DATE(renewalnotification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  553. $NotificationCond2 = " and renewalnotification.updatedOn >= '" . @$searchTimestamp . "' and renewalnotification.updatedOn < '" . @$searchEndTimestamp . "' AND (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(renewalnotification.startDate, '%d-%m-%Y') AND STR_TO_DATE(renewalnotification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  554. }
  555. if (Yii::app()->session->get('isAllocator') == 'yes' or Yii::app()->session->get('type') == 'superadmin')
  556. {
  557. $LeasStatusCond = '';
  558. }
  559. else
  560. {
  561. $LeasStatusCond = ' AND renewallead.currentAllocatedId = "' . Yii::app()->session->get('id') . '"';
  562. }
  563. $LeadStatus = Yii::app()->db->createCommand('SELECT
  564. me.leadStatusId AS me_id,
  565. me.leadStatus AS me_leadStatus,
  566. renewallead.leadStatusId AS lead_leadStatusId,
  567. renewallead.leadId AS leadId,
  568. count(*) AS total
  569. FROM
  570. renewalleadStatus me
  571. left join renewallead on renewallead.leadStatusId=me.leadStatusId where renewallead.leadStatusId > 0 ' . $leadCond . ' ' . $LeasStatusCond . ' group by renewallead.leadStatusId')->queryAll();
  572. $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `renewalleadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";
  573. // $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder,SUM(IF(lead.updatedOn >= '".@$CurrentMonth_first_minute."' and lead.updatedOn <= '".@$CurrentMonth_last_minute."' and ls.leadstatusId=lead.leadstatusId and u.status='Active',1,0)) as MTD,SUM(IF(lead.updatedOn >= '".@$CurrentYear_first_minute."' and lead.updatedOn <= '".@$CurrentYear_last_minute."' and u.status='Active',1,0)) as YTD FROM `leadstatus` ls join lead on lead.leadstatusId=ls.leadstatusId left join user u on u.id=lead.currentAllocatedId where ls.status='Active' ".$leadCond." ".$LeasStatusCond." group By lead.leadstatusId order by ls.sortOrder ASC";
  574. $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();
  575. $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(
  576. 'keyField' => 'leadstatusId',
  577. 'totalItemCount' => count($count),
  578. 'sort' => array(
  579. 'attributes' => array(
  580. 'leadStatusId',
  581. ),
  582. ),
  583. 'pagination' => array(
  584. 'pageSize' => 10,
  585. 'pageVar' => 'gyu', //page selector
  586. ),
  587. ));
  588. $where = '';
  589. if (Yii::app()->session->get('type') != 'superadmin')
  590. {
  591. $where = ' where renewalnotificationMapping.roleId="' . Yii::app()->session->get('roleId') . '"';
  592. $cls = " AND (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(renewalnotification.startDate, '%d-%m-%Y') AND STR_TO_DATE(renewalnotification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  593. $notifications = Yii::app()->db->createCommand("select renewalnotification.* from renewalnotification left join renewalnotificationMapping on renewalnotificationMapping.notificationId=renewalnotification.notificationId $where " . $NotificationCond1 . " " . @$cls . " group by renewalnotification.notificationId ")->queryAll();
  594. }
  595. else
  596. {
  597. $cls = " WHERE (STR_TO_DATE('$currentDateNot', '%d-%m-%Y') >= STR_TO_DATE(renewalnotification.startDate, '%d-%m-%Y') AND STR_TO_DATE(renewalnotification.endDate, '%d-%m-%Y') >= STR_TO_DATE('$currentDateNot', '%d-%m-%Y') )";
  598. $notifications = Yii::app()->db->createCommand("select renewalnotification.* from renewalnotification left join renewalnotificationMapping on renewalnotificationMapping.notificationId=renewalnotification.notificationId " . @$cls . " " . $NotificationCond2 . " group by renewalnotification.notificationId ")->queryAll();
  599. }
  600. $sourcewiseDetails = Yii::app()->db->createCommand("select renewalleadSourceMaster.* from renewalleadSourceMaster ")->queryAll();
  601. $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();
  602. function SecondLevel($managerId, $userId)
  603. {
  604. return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId='$managerId' and user.managerId!='$userId' ")->queryAll();
  605. }
  606. function ManagerLevel($userIds, $managerIds)
  607. {
  608. return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId ='$userIds' and id!='$managerIds' ")->queryAll();
  609. }
  610. function ManagerDetails($userIds)
  611. {
  612. $userIds = UserWithManagerIdsImploded($userIds);
  613. if ($userIds != '')
  614. {
  615. return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
  616. }
  617. }
  618. function UserWithManagerIdsImploded($userIds)
  619. {
  620. return $userIds = implode(",", $userIds);
  621. }
  622. $total_firstLevel = array();
  623. $total_nextLevel = array();
  624. function fetchAllmanagerLevelRecords(&$total_firstLevel, $ManagerDetails, $currentUserIds)
  625. {
  626. foreach ($ManagerDetails as $key => $value)
  627. {
  628. $total_firstLevel[] = $value['Id'];
  629. $total_res = SecondLevel($value['Id'], $currentUserIds);
  630. $res[] = count($total_res);
  631. if (count($total_res) > 0)
  632. {
  633. $o = 0;
  634. foreach ($total_res as $key1 => $value1)
  635. {
  636. $total_firstLevel[] = $value1['Id'];
  637. $net_res = fetchAllmanagerLevelRecords($total_firstLevel, ManagerLevel($value1['Id'], Yii::app()->session->get('id')), Yii::app()->session->get('id'));
  638. $o++;
  639. }
  640. }
  641. }
  642. return $total_firstLevel;
  643. }
  644. $managerIds = fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));
  645. if (empty($managerIds))
  646. {
  647. $managerIds[] = $_SESSION['id'];
  648. }
  649. else
  650. {
  651. $pusharray = array();
  652. $pusharray[] = @$_SESSION['id'];
  653. $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));
  654. }
  655. if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")
  656. {
  657. $teamLeaderTeamUserIdsRes = array();
  658. $teamLeaderTeamUserIds = Yii::app()->db->createCommand("select id as userId from user where id IN (select distinct userId from renewalteamUserMapping where teamId IN (select distinct teamId from renewalteamUserMapping where userId='" . Yii::app()->session->get('id') . "') ) and status='Active' and type!='superadmin' ")->queryAll();
  659. if (count($teamLeaderTeamUserIds) > 0)
  660. {
  661. foreach ($teamLeaderTeamUserIds as $keyd => $valued)
  662. {
  663. $teamLeaderTeamUserIdsRes[] = $valued['userId'];
  664. }
  665. $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));
  666. }
  667. }
  668. $managerDetailsArr = ManagerDetails($managerIds);
  669. Yii::app()->session['relationalManager'] = $managerIds;
  670. $sourcewiseDetailsWithPiechart = Yii::app()->db->createCommand("SELECT renewalleadSourceMaster.*,count(*) as total FROM `renewalleadSourceMaster` left join renewallead on renewalleadSourceMaster.leadSourceId=renewallead.leadSourceId where renewalleadSourceMaster.status='Active' " . $PiechartCond . " group by renewalleadSourceMaster.leadSourceId ")->queryAll();
  671. $speedmeterUser = @$_REQUEST['speedmeterUser'];
  672. if (isset($speedmeterUser) && !empty($speedmeterUser))
  673. {
  674. $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);
  675. }
  676. else
  677. {
  678. $totalAmountOfSpeedometer = 0;
  679. }
  680. $this->render('renewaldashboard', array('LeadStatus' => @$LeadStatus, 'notifications' => @$notifications, 'sourcewiseDetails' => @$sourcewiseDetails, 'sourcewiseDetailsWithPiechart' => @$sourcewiseDetailsWithPiechart, 'managerDetailsArr' => @$managerDetailsArr, 'totalAmountOfSpeedometer' => @$totalAmountOfSpeedometer, 'dataProviderc' => $dataProviderc));
  681. }
  682. public function actionrenViewLeadSummaryData()
  683. {
  684. $this->layout = 'column3';
  685. $leadStatusId = @$_REQUEST['leadStatusId'];
  686. //$model=new Renewallead;
  687. //$model=Renewallead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");
  688. if (!Yii::app()->request->isAjaxRequest)
  689. {
  690. $this->redirect('dashboard/renewal');
  691. }
  692. else
  693. {
  694. $this->render('/renewalleads/partialLeadSummary', array('leadStatusId' => $leadStatusId));
  695. Yii::app()->end();
  696. }
  697. }
  698. public function actionrenViewWorkLoadData()
  699. {
  700. if (!Yii::app()->request->isAjaxRequest)
  701. {
  702. // $this->render('yourView');
  703. }
  704. else
  705. {
  706. $this->renderPartial('/renewalleads/partialWorkLoadData');
  707. Yii::app()->end();
  708. }
  709. }
  710. public function actionrenViewAllWorkLoadData()
  711. {
  712. function ManagerDetails($userIds)
  713. {
  714. $userIds = UserWithManagerIdsImploded($userIds);
  715. if ($userIds != '')
  716. {
  717. return Yii::app()->db->createCommand("select id from user where user.Id IN ($userIds) ")->queryAll();
  718. }
  719. }
  720. function UserWithManagerIdsImploded($userIds)
  721. {
  722. return $userIds = implode(",", $userIds);
  723. }
  724. $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
  725. if (!Yii::app()->request->isAjaxRequest)
  726. {
  727. // $this->render('yourView');
  728. }
  729. else
  730. {
  731. $this->renderPartial('/renewalleads/partialAllWorkLoadData', array('managerDetailsArr' => $managerDetailsArr));
  732. Yii::app()->end();
  733. }
  734. }
  735. public function actionrenViewFancyboxData()
  736. {
  737. $userId = @$_REQUEST['id'];
  738. $model = new User;
  739. $model = User::model()->findByPk($userId);
  740. if (!Yii::app()->request->isAjaxRequest)
  741. {
  742. // $this->render('yourView');
  743. }
  744. else
  745. {
  746. $this->renderPartial('/renewalmasters/partialNotification');
  747. Yii::app()->end();
  748. }
  749. }
  750. public function actionrenViewAllPerformanceData()
  751. {
  752. function ManagerDetails($userIds)
  753. {
  754. $userIds = UserWithManagerIdsImploded($userIds);
  755. if ($userIds != '')
  756. {
  757. return Yii::app()->db->createCommand("select id from user where user.Id IN ($userIds) ")->queryAll();
  758. }
  759. }
  760. function UserWithManagerIdsImploded($userIds)
  761. {
  762. return $userIds = implode(",", array_filter($userIds));
  763. }
  764. $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
  765. if (!Yii::app()->request->isAjaxRequest)
  766. {
  767. $this->render('/renewalleads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
  768. // $this->render('yourView');
  769. }
  770. else
  771. {
  772. $this->renderPartial('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
  773. Yii::app()->end();
  774. }
  775. }
  776. public function actionfullcalender()
  777. {
  778. // renders the view file 'protected/views/site/index.php'
  779. // using the default layout 'protected/views/layouts/main.php'
  780. $this->render('fullcalender');
  781. }
  782. public function actionmyteamworkload()
  783. {
  784. // renders the view file 'protected/views/site/index.php'
  785. // using the default layout 'protected/views/layouts/main.php'
  786. $this->layout = false;
  787. function ManagerDetails($userIds)
  788. {
  789. $userIds = UserWithManagerIdsImploded($userIds);
  790. if ($userIds != '')
  791. {
  792. return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
  793. }
  794. }
  795. function UserWithManagerIdsImploded($userIds)
  796. {
  797. return $userIds = implode(",", array_filter($userIds));
  798. }
  799. $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
  800. $this->render('myteamworkload', array('managerDetailsArr' => @$managerDetailsArr));
  801. }
  802. public static function implodeResult($userIds)
  803. {
  804. $userIds = array_filter($userIds);
  805. return $userIds = implode(",", $userIds);
  806. }
  807. public function actionshowLeadCount()
  808. {
  809. $this->layout = false;
  810. $this->render('showLeadCount');
  811. }
  812. public function actionshowleadcountdisp()
  813. {
  814. $this->layout = false;
  815. $this->render('showleadcountdisp');
  816. }
  817. public static function getMTD($leadStatusId)
  818. {
  819. if (Yii::app()->session->get('type') == 'superadmin')
  820. {
  821. $cond = '';
  822. }
  823. else
  824. {
  825. if (count(@$_SESSION['relationalManager']) > 0)
  826. {
  827. $teamids = implode(",", $_SESSION['relationalManager']);
  828. }
  829. else
  830. {
  831. $teamids = Yii::app()->session->get('id');
  832. }
  833. $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
  834. }
  835. $financialyear = @$_POST['financialyear'];
  836. if (isset($financialyear) && !empty($financialyear))
  837. {
  838. $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);
  839. $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);
  840. }
  841. else
  842. {
  843. $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
  844. $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
  845. }
  846. $sql = "select count(leadId) as total from lead l join user u on u.id=l.currentAllocatedId where l.leadStatusId = " . $leadStatusId . " and (l.updatedOn between '" . @$CurrentMonth_first_minute . "' and '" . @$CurrentMonth_last_minute . "') and u.status='Active' $cond limit 1 ";
  847. $count = Yii::app()->db->createCommand($sql)->queryColumn();
  848. return @$count[0];
  849. //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
  850. }
  851. public static function getYTD($leadStatusId)
  852. {
  853. if (Yii::app()->session->get('type') == 'superadmin')
  854. {
  855. $cond = '';
  856. }
  857. else
  858. {
  859. if (count(@$_SESSION['relationalManager']) > 0)
  860. {
  861. $teamids = implode(",", $_SESSION['relationalManager']);
  862. }
  863. else
  864. {
  865. $teamids = Yii::app()->session->get('id');
  866. }
  867. $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
  868. }
  869. $financialyear = @$_POST['financialyear'];
  870. if (isset($financialyear) && !empty($financialyear))
  871. {
  872. $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);
  873. $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);
  874. }
  875. else
  876. {
  877. $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));
  878. $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);
  879. }
  880. //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
  881. //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
  882. $sql = "select count(leadId) as total from lead l join user u on u.id=l.currentAllocatedId where l.leadStatusId = " . $leadStatusId . " and (l.updatedOn between '" . @$CurrentYear_first_minute . "' and '" . @$CurrentYear_last_minute . "') and u.status='Active' $cond ";
  883. $count = Yii::app()->db->createCommand($sql)->queryColumn();
  884. //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
  885. return @$count[0];
  886. //return @$count[0];
  887. }
  888. public static function rengetMTD($leadStatusId)
  889. {
  890. if (Yii::app()->session->get('type') == 'superadmin')
  891. {
  892. $cond = '';
  893. }
  894. else
  895. {
  896. if (count(@$_SESSION['relationalManager']) > 0)
  897. {
  898. $teamids = implode(",", $_SESSION['relationalManager']);
  899. }
  900. else
  901. {
  902. $teamids = Yii::app()->session->get('id');
  903. }
  904. $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
  905. }
  906. $financialyear = @$_POST['financialyear'];
  907. if (isset($financialyear) && !empty($financialyear))
  908. {
  909. $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);
  910. $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);
  911. }
  912. else
  913. {
  914. $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
  915. $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
  916. }
  917. $sql = "select count(leadId) as total from renewallead l join user u on u.id=l.currentAllocatedId where l.leadStatusId = " . $leadStatusId . " and (l.updatedOn between '" . @$CurrentMonth_first_minute . "' and '" . @$CurrentMonth_last_minute . "') and u.status='Active' and u.isRenewalUser='yes' $cond limit 1 ";
  918. $count = Yii::app()->db->createCommand($sql)->queryColumn();
  919. return @$count[0];
  920. //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
  921. }
  922. public static function rengetYTD($leadStatusId)
  923. {
  924. if (Yii::app()->session->get('type') == 'superadmin')
  925. {
  926. $cond = '';
  927. }
  928. else
  929. {
  930. if (count(@$_SESSION['relationalManager']) > 0)
  931. {
  932. $teamids = implode(",", $_SESSION['relationalManager']);
  933. }
  934. else
  935. {
  936. $teamids = Yii::app()->session->get('id');
  937. }
  938. $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
  939. }
  940. $financialyear = @$_POST['financialyear'];
  941. if (isset($financialyear) && !empty($financialyear))
  942. {
  943. $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);
  944. $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);
  945. }
  946. else
  947. {
  948. $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));
  949. $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);
  950. }
  951. //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
  952. //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
  953. $sql = "select count(leadId) as total from renewallead l join user u on u.id=l.currentAllocatedId where l.leadStatusId = " . $leadStatusId . " and (l.updatedOn between '" . @$CurrentYear_first_minute . "' and '" . @$CurrentYear_last_minute . "') and u.status='Active' and u.isRenewalUser='yes' $cond ";
  954. $count = Yii::app()->db->createCommand($sql)->queryColumn();
  955. //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
  956. return @$count[0];
  957. //return @$count[0];
  958. }
  959. /**
  960. * added by shakti.
  961. */
  962. public function actionWorkloadIncorrect()
  963. {
  964. $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
  965. $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;
  966. $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;
  967. $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);
  968. $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
  969. $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
  970. $leadCond = '';
  971. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
  972. {
  973. $searchDate=$_POST['searchDashboard'];
  974. $searchTimestamp=strtotime(@$searchDate);
  975. $searchEndTimestamp=$searchTimestamp+86400;
  976. $leadCond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";
  977. }
  978. $callerTeamSql = "SELECT user.firstName,user.lastName,user.id as Id,SUM(IF(lead.updatedOn >= '".@$today12PM."' and lead.updatedOn <= '".@$tomorrow12PM."' and lead.leadSourceId = 1,1,0)) as WT,SUM(IF(lead.updatedOn >= '".@$yesterday12PM."' and lead.updatedOn <= '".@$today12PM."' and lead.leadSourceId = 1,1,0)) as WY,SUM(IF(lead.updatedOn >= '".@$CurrentMonth_first_minute."' and lead.updatedOn <= '".@$CurrentMonth_last_minute."' and lead.leadSourceId = 1,1,0)) as WM,SUM(IF(lead.updatedOn >= '".@$LastMonth_first_minute."' and lead.updatedOn <= '".@$CurrentMonth_first_minute."' and lead.leadSourceId = 1,1,0)) as WLM,SUM(IF(lead.…

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