/lms_debug/protected/controllers/DashboardController_24dec2014.php
PHP | 1812 lines | 1312 code | 346 blank | 154 comment | 166 complexity | 394e6e3f5a3a4803e04ea46ae8c38922 MD5 | raw file
Possible License(s): LGPL-2.1, LGPL-3.0, LGPL-2.0
- <?php
-
- class DashboardController extends Controller
- {
- const SHARED_TITLE = "Admin Panel";
- /**
- * Declares class-based actions.
- */
- public function filters()
- {
- return array('accessControl', array(
- 'CHttpCacheFilter + view',
- 'cacheControl' => " max-age=604800, must-revalidate",
- )); // perform access control for CRUD operations
- }
-
- public function actions()
- {
- return array(
- // captcha action renders the CAPTCHA image displayed on the contact page
- 'captcha' => array(
- 'class' => 'CCaptchaAction',
- 'backColor' => 0xFFFFFF,
- ),
- // page action renders "static" pages stored under 'protected/views/site/pages'
- // They can be accessed via: index.php?r=site/page&view=FileName
- 'page' => array(
- 'class' => 'CViewAction',
- ),
- );
- }
-
- /**
- * This is the 'roles' action that is invoked
- * when an action is not explicitly requested by users.
- */
- protected function array_flatten($array)
- {
- if (!is_array($array))
- {
- return FALSE;
- }
- $result = array();
- foreach ($array as $key => $value)
- {
- if (is_array($value))
- {
- $result = array_merge($result, $this->array_flatten($value));
- }
- else
- {
- $result[$key] = $value;
- }
- }
- return $result;
- }
-
-
-
- /**
- * Action dashboard. //added by shakti. Please remove if not used.
- */
- public function actiondashboard()
- {
- $financialyear = @$_POST['financialyear'] ? $_POST['financialyear'] : date('Y');
- $month = date('m');
- self::insertUserLeadAllCountRecord($financialyear, $month);
- if (empty(Yii::app()->session['id']) or Yii::app()->session['id'] == '')
- {
- $this->redirect(array('site/index'));
- }
- $leadCond = '';
- $PiechartCond = '';
- $NotificationCond1 = '';
- $NotificationCond2 = '';
- $currentDateNot = date('d-m-Y');
- $currentTimestamp = strtotime($currentDateNot);
-
- if (isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
- {
- $searchDate = $_POST['searchDashboard'];
- $searchTimestamp = strtotime(@$searchDate);
- $searchEndTimestamp = $searchTimestamp + 86400;
- $leadCond = " and lead.updatedOn >= '" . @$searchTimestamp . "' and lead.updatedOn < '" . @$searchEndTimestamp . "'";
- $PiechartCond = " and leadSourceMaster.updatedOn >= '" . @$searchTimestamp . "' and leadSourceMaster.updatedOn < '" . @$searchEndTimestamp . "'";
-
- $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') )";
- $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') )";
- }
- if (Yii::app()->session->get('isAllocator') == 'yes' or Yii::app()->session->get('type') == 'superadmin' or Yii::app()->session->get('roleId') == 1)
- {
- $LeasStatusCond = '';
- }
- else
- {
- $LeasStatusCond = ' AND lead.currentAllocatedId = ' . Yii::app()->session->get('id') . '';
- }
-
-
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
- $CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
- $CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
-
- $where = '';
-
-
-
- if (Yii::app()->session->get('type') != 'superadmin')
- {
- $where = ' where notificationMapping.roleId="' . Yii::app()->session->get('roleId') . '"';
- $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') )";
- $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();
- }
- else
- {
- $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') )";
- $notifications = Yii::app()->db->createCommand("select notification.* from notification left join notificationMapping on notificationMapping.notificationId=notification.notificationId " . @$cls . " " . $NotificationCond2 . " group by notification.notificationId ")->queryAll();
- }
-
- $sourcewiseDetails = Yii::app()->db->createCommand("select leadSourceMaster.* from leadSourceMaster ")->queryAll();
-
- $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();
-
-
- $total_firstLevel = array();
- $total_nextLevel = array();
-
- $managerIds = self::fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));
- if (empty($managerIds))
- {
- $managerIds[] = $_SESSION['id'];
- }
- else
- {
- $pusharray = array();
- $pusharray[] = @$_SESSION['id'];
- $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));
- }
- if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")
- {
- $teamLeaderTeamUserIdsRes = array();
- $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();
- if (count($teamLeaderTeamUserIds) > 0)
- {
- foreach ($teamLeaderTeamUserIds as $keyd => $valued)
- {
- $teamLeaderTeamUserIdsRes[] = $valued['userId'];
- }
- $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));
- }
- }
- $managerDetailsArr = self::ManagerDetails($managerIds);
-
- Yii::app()->session['relationalManager'] = $managerIds;
- //$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();
- $speedmeterUser = @$_REQUEST['speedmeterUser'];
- if (isset($speedmeterUser) && !empty($speedmeterUser))
- {
- $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);
- }
- else
- {
- $totalAmountOfSpeedometer = 0;
- }
-
-
-
-
- $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `leadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";
-
- // $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";
-
-
- $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();
- $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(
- 'keyField' => 'leadstatusId',
- 'totalItemCount' => count($count),
- 'sort' => array(
- 'attributes' => array(
- 'leadStatusId',
- ),
- ),
- 'pagination' => array(
- 'pageSize' => 10,
- 'pageVar' => 'gyu', //page selector
- ),
- ));
-
-
-
-
- if (Yii::app()->session->get('isAllocator') == 'yes')
- {
- $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();
-
- $impresuser = @$allocatorTl[0]['TLIDS'];
- }
- else
- {
- $impresuser = $this->implodeResult(Yii::app()->session['relationalManager']);
- }
-
-
- //calculate performance
- /*
- $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
- $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;
- $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;
- $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);
- //echo date('d M Y :: H:i:s',$CurrentMonth_first_minute); die;
-
- $filtercaller = @$_REQUEST['User']['userName'];
- $filtersource = @$_REQUEST['LeadSourceMaster']['leadSourceId'];
- $filterdate1 = @$_REQUEST['Lead']['createdOn'];
- $filterdate2 = @$_REQUEST['Lead']['createdOn2'];
- $filterCond = '';
- if (isset($filtercaller) && !empty($filtercaller))
- {
- $filterCond .= " AND lead.currentAllocatedId = " . $filtercaller . "";
- }
- if (isset($filtersource) && !empty($filtersource))
- {
- $filterCond .= " AND lead.leadSourceId = " . $filtersource . "";
- }
- if (isset($filterdate1) && !empty($filterdate1) && isset($filterdate2) && !empty($filterdate2))
- {
- $date1Filer = strtotime($filterdate1);
- $date2Filer = strtotime($filterdate2);
- $filterCond .= " AND lead.updatedOn >= " . $date1Filer . " AND lead.updatedOn <= " . $date2Filer . "";
- }
- */
- /* commented by shakti
- $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 ";
- $countTeamworkLoad = Yii::app()->db->createCommand($teamworkloadSql)->queryAll();
- $dataProviderWorkload = new CSqlDataProvider($teamworkloadSql, array(
- 'keyField' => 'Id',
- 'totalItemCount' => count($countTeamworkLoad),
- 'sort' => array(
- 'attributes' => array(
- 'Id',
- ),
- ),
- 'pagination' => array(
- 'pageSize' => 10,
- 'pageVar' => 'workload', //page selector
- ),
- ));
- */
-
- self::getRequiredVariables();
-
- $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 . " ";
- $countcallerInfo = Yii::app()->db->createCommand($callerTeamSql)->queryAll();
- $dataProvidercaller = new CSqlDataProvider($callerTeamSql, array(
- 'keyField' => 'Id',
- 'totalItemCount' => count($countcallerInfo),
- 'sort' => array(
- 'attributes' => array(
- 'Id',
- ),
- ),
- 'pagination' => false,
- ));
-
- $dataProviderPerf = self::getDataProviderPerf();
-
- $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));
- }
-
-
- /**
-
- * Get Lead Status Count.(Eg Manager, add leadtypecount of all Teamleaders & Teamleader, add leadtypecount of all callers).
-
- * @see _leadfortoday.php
-
- * @return int
-
- */
-
- public static function getLeadStatusCount($leadStatusId, $accessKey)
-
- {
-
- $userList = $userId = Yii::app()->session['id'];
-
-
-
- $userList = User::getMemebersList();
-
-
-
- if(count($userList))
-
- {
-
- $allCallersList = array();
-
- $lCount = array();
-
- foreach($userList as $list)
-
- {
-
- $allCallersList[] = $list['id'];
-
- }
-
- $userList = implode(', ', $allCallersList);
-
- //print $userList . '==';
-
- $totalCount = Yii::app()->db->createCommand("select sum(count) as count from leadallcount where userid in ( $userList ) AND leadtype = $leadStatusId AND accesskey = '$accessKey'")->queryRow();
-
- return $totalCount['count'];
-
- }
-
-
-
- $count = Yii::app()->db->createCommand("select sum(count) as count from leadallcount where userid = $userId AND leadtype = $leadStatusId AND accesskey = '$accessKey'")->queryRow();
-
- return $count['count'];
-
- }
-
-
-
-
-
-
- /**
-
- * Insert Users Record (Eg. call this method to insert all callers and the logged in teamleader (callers, teamleader & logged in manager)).
-
- * @see _leadfortoday.php
-
- * @return int
-
- */
-
- public static function insertUsersRecord($accessKey)
-
- {
-
- $userList = User::getMemebersList();
-
-
-
- $financialDate = explode('-', $accessKey);
-
- $financialDate[0] = $financialDate[0] ? $financialDate[0] : date('Y');
-
- $financialDate[1] = @$financialDate[1] ? $financialDate[1] : date('m');
-
-
-
- if (count($userList))
-
- {
-
- foreach ($userList as $list)
-
- {
-
- self::insertUserLeadAllCountRecord($financialDate[0], $financialDate[1], $list['id']);
-
- }
-
- } else if(Yii::app()->session->get('type') == 'superadmin')
-
- {
-
- self::insertUserLeadAllCountRecord($financialDate[0], $financialDate[1], Yii::app()->session['id']);
-
- }
-
-
- }
- public static function getMTDForUser($leadStatusId, $userId)
-
- {
-
- if (Yii::app()->session->get('type') == 'superadmin')
-
- {
-
- $cond = '';
-
- }
-
- else
-
- {
-
- if (count(@$_SESSION['relationalManager']) > 0)
-
- {
-
- $teamids = implode(",", $_SESSION['relationalManager']);
-
- }
-
- else
-
- {
-
- $teamids = Yii::app()->session->get('id');
-
- }
-
- $cond = "AND l.currentAllocatedId = " . $userId;
-
- }
-
-
-
- $financialyear = @$_POST['financialyear'];
-
- if (isset($financialyear) && !empty($financialyear))
-
- {
-
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);
-
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);
-
- }
-
- else
-
- {
-
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
-
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
-
- }
-
-
-
- $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 ";
-
- $count = Yii::app()->db->createCommand($sql)->queryColumn();
-
-
-
- return @$count[0];
-
- //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
-
- }
-
- public static function getYTDForUser($leadStatusId, $userId)
-
- {
-
-
-
- if (Yii::app()->session->get('type') == 'superadmin')
-
- {
-
- $cond = '';
-
- }
-
- else
-
- {
-
- if (count(@$_SESSION['relationalManager']) > 0)
-
- {
-
- $teamids = implode(",", $_SESSION['relationalManager']);
-
- }
-
- else
-
- {
-
- $teamids = Yii::app()->session->get('id');
-
- }
-
- $cond = "AND l.currentAllocatedId = " . $userId;
-
- }
-
-
-
- $financialyear = @$_POST['financialyear'];
-
- if (isset($financialyear) && !empty($financialyear))
-
- {
-
- $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);
-
- $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);
-
- }
-
- else
-
- {
-
- $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));
-
- $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);
-
- }
-
-
-
- //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
-
- //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
-
- $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 ";
-
- $count = Yii::app()->db->createCommand($sql)->queryColumn();
-
- //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
-
- return @$count[0];
-
- //return @$count[0];
-
- }
-
-
-
-
-
- /**
- * This is the 'module master' action that is invoked
- * when an action is not explicitly requested by users.
- */
- public function actionmodule_master()
- {
- // renders the view file 'protected/views/site/index.php'
- // using the default layout 'protected/views/layouts/main.php'
-
- $model = new ModuleMaster();
- $model->scenario = 'ModuleMaster';
-
- if (!empty($_GET['moduleId']))
- {
- $moduleId = $_GET['moduleId'];
- $model = ModuleMaster::model()->findByPk($moduleId);
- }
-
- if (!empty($_POST['ModuleMaster']['moduleId']))
- {
- $moduleId = $_POST['ModuleMaster']['moduleId'];
- $model = ModuleMaster::model()->findByPk($moduleId);
- }
-
- if (isset($_POST['ModuleMaster']))
- {
- $model->setAttributes(array(
- 'createdOn' => time(), //additional data you want to insert
- 'updatedOn' => time(), //additional
- 'updatedBy' => time(), //additional
- 'createdBy' => time(),
- 'status' => 'Activated', //additional //additional
- ));
- foreach ($_POST['ModuleMaster'] as $name => $value)
- {
- $model->$name = $value;
- }
-
- if ($model->validate())
- {
- $model->save();
- $model2 = ModuleMaster::model()->findAll();
- $this->render('module_master', array('model' => $model, 'model2' => $model2));
- Yii::app()->user->setFlash('module_master_msg_success', 'Successfully Inserted');
- }
- else
- {
- Yii::app()->user->setFlash('module_master_msg_error', 'Please check. error occured');
- $this->render('module_master', array('model' => $model));
- }
- }
- else
- {
- $model2 = ModuleMaster::model()->findAll();
- //Yii::app()->user->setFlash('lead_stage_msg_error','Please check. error occured');
- $this->render('module_master', array('model' => $model, 'model2' => $model2));
- }
- }
-
- /**
- * This is the action to handle external exceptions.
- */
- public function actionError()
- {
- if ($error = Yii::app()->errorHandler->error)
- {
- if (Yii::app()->request->isAjaxRequest)
- echo $error['message'];
- else
- $this->render('error', $error);
- }
- }
-
- /**
- * Displays the contact page
- */
- public function actionContact()
- {
- $model = new ContactForm;
- if (isset($_POST['ContactForm']))
- {
- $model->attributes = $_POST['ContactForm'];
- if ($model->validate())
- {
- $name = '=?UTF-8?B?' . base64_encode($model->name) . '?=';
- $subject = '=?UTF-8?B?' . base64_encode($model->subject) . '?=';
- $headers = "From: $name <{$model->email}>\r\n" .
- "Reply-To: {$model->email}\r\n" .
- "MIME-Version: 1.0\r\n" .
- "Content-type: text/plain; charset=UTF-8";
-
- mail(Yii::app()->params['adminEmail'], $subject, $model->body, $headers);
- Yii::app()->user->setFlash('contact', 'Thank you for contacting us. We will respond to you as soon as possible.');
- $this->refresh();
- }
- }
- $this->render('contact', array('model' => $model));
- }
-
- /**
- * Displays the login page
- */
- public function actionLogin()
- {
- $model = new User;
-
- // if it is ajax validation request
- if (isset($_POST['ajax']) && $_POST['ajax'] === 'login-form')
- {
- echo CActiveForm::validate($model);
- Yii::app()->end();
- }
-
- // collect user input data
- if (isset($_POST['User']))
- {
-
- $model->attributes = $_POST['User'];
- // validate user input and redirect to the previous page if valid
- if ($model->validate() && $model->login())
- {
- $this->redirect(array('masters/dashboard'));
- }
- else
- {
- $this->redirect(Yii::app()->user->returnUrl);
- }
- }
- // display the login form
- $this->render('login', array('model' => $model));
- }
-
- /**
- * Logs out the current user and redirect to homepage.
- */
- public function actionLogout()
- {
- Yii::app()->user->logout();
- $this->redirect(Yii::app()->homeUrl);
- }
-
- public function getPageType()
- {
- return "";
- }
-
- public function actionViewFancyboxData()
- {
- $userId = @$_REQUEST['id'];
- $model = new User;
- $model = User::model()->findByPk($userId);
- if (!Yii::app()->request->isAjaxRequest)
- {
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/masters/partialNotification');
- Yii::app()->end();
- }
- }
-
- public function actionViewWorkLoadData()
- {
- if (!Yii::app()->request->isAjaxRequest)
- {
- echo "ddd";
- // $this->render('/leads/partialWorkLoadData');
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/leads/partialWorkLoadData');
- Yii::app()->end();
- }
- }
-
- public function actionViewLeadSummaryData()
- {
- //$this->layout='column3';
- $this->layout = false;
- $parser = new CHtmlPurifier();
- $leadStatusId = $parser->purify(intval(@$_REQUEST['leadStatusId']));
- /* $model=new Lead;
- $model=Lead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");
- */
- if (!Yii::app()->request->isAjaxRequest)
- {
- $this->render('/leads/partialLeadSummary', array('leadStatusId' => $leadStatusId));
- }
- else
- {
- $this->layout=false;
- $this->render('/leads/partialLeadSummary', array('leadStatusId' => $leadStatusId));
- Yii::app()->end();
- }
- }
-
- public function actionRenewalViewLeadSummaryData()
- {
- //$this->layout='column3';
- $this->layout = false;
- $res = new MySanitizeClass;
- $leadStatusId = $res->sanitize_description(@$_REQUEST['leadStatusId']);
- /* $model=new Lead;
- $model=Lead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");
- */ if (!Yii::app()->request->isAjaxRequest)
- {
- $this->render('/renewalleads/renewalpartialLeadSummary', array('leadStatusId' => $leadStatusId));
- }
- else
- {
- //$this->layout=false;
- $this->render('/renewalleads/renewalpartialLeadSummary', array('leadStatusId' => $leadStatusId));
- Yii::app()->end();
- }
- }
-
- function getparentLeadStatus($parentId)
- {
- $primary = LeadStatus::model()->findByPk($parentId);
- if ($primary['parentId'] > 0)
- {
- $parentName = LeadStatus::model()->findByPk($primary['parentId']);
- $pname = $parentName['leadStatus'];
- }
- else
- {
- $pname = "Primary";
- }
-
- return $pname;
- }
-
- public function actionViewAllWorkLoadData()
- {
-
- function ManagerDetails($userIds)
- {
- $userIds = UserWithManagerIdsImploded($userIds);
- if ($userIds != '')
- {
- return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
- }
- }
-
- function UserWithManagerIdsImploded($userIds)
- {
- return $userIds = implode(",", $userIds);
- }
-
- $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
- if (!Yii::app()->request->isAjaxRequest)
- {
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/leads/partialAllWorkLoadData', array('managerDetailsArr' => $managerDetailsArr));
- Yii::app()->end();
- }
- }
-
- public function actionViewAllPerformanceData()
- {
-
- function ManagerDetails($userIds)
- {
- $userIds = UserWithManagerIdsImploded($userIds);
- if ($userIds != '')
- {
- return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
- }
- }
-
- function UserWithManagerIdsImploded($userIds)
- {
- return $userIds = implode(",", $userIds);
- }
-
- $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
- if (!Yii::app()->request->isAjaxRequest)
- {
- $this->render('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
- Yii::app()->end();
- }
- }
-
- public function actionrenewal()
- {
-
- //$this->redirect(array('dashboard/dashboard'));
- $leadCond = '';
- $PiechartCond = '';
- $NotificationCond1 = '';
- $NotificationCond2 = '';
- $currentDateNot = date('d-m-Y');
- $currentTimestamp = strtotime($currentDateNot);
-
- if (isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
- {
- $searchDate = $_POST['searchDashboard'];
- $searchTimestamp = strtotime(@$searchDate);
- $searchEndTimestamp = $searchTimestamp + 86400;
- $leadCond = " and renewallead.updatedOn >= '" . @$searchTimestamp . "' and renewallead.updatedOn < '" . @$searchEndTimestamp . "'";
- $PiechartCond = " and renewalleadSourceMaster.updatedOn >= '" . @$searchTimestamp . "' and renewalleadSourceMaster.updatedOn < '" . @$searchEndTimestamp . "'";
-
- $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') )";
- $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') )";
- }
- if (Yii::app()->session->get('isAllocator') == 'yes' or Yii::app()->session->get('type') == 'superadmin')
- {
- $LeasStatusCond = '';
- }
- else
- {
- $LeasStatusCond = ' AND renewallead.currentAllocatedId = "' . Yii::app()->session->get('id') . '"';
- }
- $LeadStatus = Yii::app()->db->createCommand('SELECT
- me.leadStatusId AS me_id,
- me.leadStatus AS me_leadStatus,
- renewallead.leadStatusId AS lead_leadStatusId,
- renewallead.leadId AS leadId,
- count(*) AS total
- FROM
- renewalleadStatus me
- left join renewallead on renewallead.leadStatusId=me.leadStatusId where renewallead.leadStatusId > 0 ' . $leadCond . ' ' . $LeasStatusCond . ' group by renewallead.leadStatusId')->queryAll();
-
- $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `renewalleadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";
-
- // $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";
-
-
- $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();
- $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(
- 'keyField' => 'leadstatusId',
- 'totalItemCount' => count($count),
- 'sort' => array(
- 'attributes' => array(
- 'leadStatusId',
- ),
- ),
- 'pagination' => array(
- 'pageSize' => 10,
- 'pageVar' => 'gyu', //page selector
- ),
- ));
-
-
-
-
- $where = '';
-
- if (Yii::app()->session->get('type') != 'superadmin')
- {
- $where = ' where renewalnotificationMapping.roleId="' . Yii::app()->session->get('roleId') . '"';
- $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') )";
- $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();
- }
- else
- {
- $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') )";
- $notifications = Yii::app()->db->createCommand("select renewalnotification.* from renewalnotification left join renewalnotificationMapping on renewalnotificationMapping.notificationId=renewalnotification.notificationId " . @$cls . " " . $NotificationCond2 . " group by renewalnotification.notificationId ")->queryAll();
- }
-
- $sourcewiseDetails = Yii::app()->db->createCommand("select renewalleadSourceMaster.* from renewalleadSourceMaster ")->queryAll();
-
- $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();
- function SecondLevel($managerId, $userId)
- {
- return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId='$managerId' and user.managerId!='$userId' ")->queryAll();
- }
-
- function ManagerLevel($userIds, $managerIds)
- {
- return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId ='$userIds' and id!='$managerIds' ")->queryAll();
- }
-
- function ManagerDetails($userIds)
- {
- $userIds = UserWithManagerIdsImploded($userIds);
- if ($userIds != '')
- {
- return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
- }
- }
-
- function UserWithManagerIdsImploded($userIds)
- {
- return $userIds = implode(",", $userIds);
- }
-
- $total_firstLevel = array();
- $total_nextLevel = array();
- function fetchAllmanagerLevelRecords(&$total_firstLevel, $ManagerDetails, $currentUserIds)
- {
- foreach ($ManagerDetails as $key => $value)
- {
- $total_firstLevel[] = $value['Id'];
- $total_res = SecondLevel($value['Id'], $currentUserIds);
- $res[] = count($total_res);
- if (count($total_res) > 0)
- {
- $o = 0;
- foreach ($total_res as $key1 => $value1)
- {
- $total_firstLevel[] = $value1['Id'];
- $net_res = fetchAllmanagerLevelRecords($total_firstLevel, ManagerLevel($value1['Id'], Yii::app()->session->get('id')), Yii::app()->session->get('id'));
- $o++;
- }
- }
- }
- return $total_firstLevel;
- }
-
- $managerIds = fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));
- if (empty($managerIds))
- {
- $managerIds[] = $_SESSION['id'];
- }
- else
- {
- $pusharray = array();
- $pusharray[] = @$_SESSION['id'];
- $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));
- }
- if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")
- {
- $teamLeaderTeamUserIdsRes = array();
- $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();
- if (count($teamLeaderTeamUserIds) > 0)
- {
- foreach ($teamLeaderTeamUserIds as $keyd => $valued)
- {
- $teamLeaderTeamUserIdsRes[] = $valued['userId'];
- }
- $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));
- }
- }
- $managerDetailsArr = ManagerDetails($managerIds);
- Yii::app()->session['relationalManager'] = $managerIds;
- $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();
- $speedmeterUser = @$_REQUEST['speedmeterUser'];
- if (isset($speedmeterUser) && !empty($speedmeterUser))
- {
- $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);
- }
- else
- {
- $totalAmountOfSpeedometer = 0;
- }
- $this->render('renewaldashboard', array('LeadStatus' => @$LeadStatus, 'notifications' => @$notifications, 'sourcewiseDetails' => @$sourcewiseDetails, 'sourcewiseDetailsWithPiechart' => @$sourcewiseDetailsWithPiechart, 'managerDetailsArr' => @$managerDetailsArr, 'totalAmountOfSpeedometer' => @$totalAmountOfSpeedometer, 'dataProviderc' => $dataProviderc));
- }
-
- public function actionrenViewLeadSummaryData()
- {
- $this->layout = 'column3';
- $leadStatusId = @$_REQUEST['leadStatusId'];
- //$model=new Renewallead;
- //$model=Renewallead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");
- if (!Yii::app()->request->isAjaxRequest)
- {
- $this->redirect('dashboard/renewal');
- }
- else
- {
-
- $this->render('/renewalleads/partialLeadSummary', array('leadStatusId' => $leadStatusId));
- Yii::app()->end();
- }
- }
-
- public function actionrenViewWorkLoadData()
- {
- if (!Yii::app()->request->isAjaxRequest)
- {
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/renewalleads/partialWorkLoadData');
- Yii::app()->end();
- }
- }
-
- public function actionrenViewAllWorkLoadData()
- {
-
- function ManagerDetails($userIds)
- {
- $userIds = UserWithManagerIdsImploded($userIds);
- if ($userIds != '')
- {
- return Yii::app()->db->createCommand("select id from user where user.Id IN ($userIds) ")->queryAll();
- }
- }
-
- function UserWithManagerIdsImploded($userIds)
- {
- return $userIds = implode(",", $userIds);
- }
-
- $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
- if (!Yii::app()->request->isAjaxRequest)
- {
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/renewalleads/partialAllWorkLoadData', array('managerDetailsArr' => $managerDetailsArr));
- Yii::app()->end();
- }
- }
-
- public function actionrenViewFancyboxData()
- {
- $userId = @$_REQUEST['id'];
- $model = new User;
- $model = User::model()->findByPk($userId);
- if (!Yii::app()->request->isAjaxRequest)
- {
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/renewalmasters/partialNotification');
- Yii::app()->end();
- }
- }
-
- public function actionrenViewAllPerformanceData()
- {
-
- function ManagerDetails($userIds)
- {
- $userIds = UserWithManagerIdsImploded($userIds);
- if ($userIds != '')
- {
- return Yii::app()->db->createCommand("select id from user where user.Id IN ($userIds) ")->queryAll();
- }
- }
-
- function UserWithManagerIdsImploded($userIds)
- {
- return $userIds = implode(",", array_filter($userIds));
- }
-
- $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
- if (!Yii::app()->request->isAjaxRequest)
- {
- $this->render('/renewalleads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
- // $this->render('yourView');
- }
- else
- {
- $this->renderPartial('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));
- Yii::app()->end();
- }
- }
-
- public function actionfullcalender()
- {
- // renders the view file 'protected/views/site/index.php'
- // using the default layout 'protected/views/layouts/main.php'
-
- $this->render('fullcalender');
- }
-
- public function actionmyteamworkload()
- {
- // renders the view file 'protected/views/site/index.php'
- // using the default layout 'protected/views/layouts/main.php'
- $this->layout = false;
- function ManagerDetails($userIds)
- {
- $userIds = UserWithManagerIdsImploded($userIds);
- if ($userIds != '')
- {
- return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
- }
- }
-
- function UserWithManagerIdsImploded($userIds)
- {
- return $userIds = implode(",", array_filter($userIds));
- }
-
- $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);
- $this->render('myteamworkload', array('managerDetailsArr' => @$managerDetailsArr));
- }
-
- public static function implodeResult($userIds)
- {
- $userIds = array_filter($userIds);
- return $userIds = implode(",", $userIds);
- }
-
- public function actionshowLeadCount()
- {
- $this->layout = false;
- $this->render('showLeadCount');
- }
-
- public function actionshowleadcountdisp()
- {
- $this->layout = false;
- $this->render('showleadcountdisp');
- }
-
- public static function getMTD($leadStatusId)
- {
- if (Yii::app()->session->get('type') == 'superadmin')
- {
- $cond = '';
- }
- else
- {
- if (count(@$_SESSION['relationalManager']) > 0)
- {
- $teamids = implode(",", $_SESSION['relationalManager']);
- }
- else
- {
- $teamids = Yii::app()->session->get('id');
- }
- $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
- }
-
- $financialyear = @$_POST['financialyear'];
- if (isset($financialyear) && !empty($financialyear))
- {
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);
- }
- else
- {
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
- }
-
- $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 ";
- $count = Yii::app()->db->createCommand($sql)->queryColumn();
-
- return @$count[0];
- //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
- }
-
- public static function getYTD($leadStatusId)
- {
-
- if (Yii::app()->session->get('type') == 'superadmin')
- {
- $cond = '';
- }
- else
- {
- if (count(@$_SESSION['relationalManager']) > 0)
- {
- $teamids = implode(",", $_SESSION['relationalManager']);
- }
- else
- {
- $teamids = Yii::app()->session->get('id');
- }
- $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
- }
-
- $financialyear = @$_POST['financialyear'];
- if (isset($financialyear) && !empty($financialyear))
- {
- $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);
- $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);
- }
- else
- {
- $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));
- $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);
- }
-
- //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
- //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
- $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 ";
- $count = Yii::app()->db->createCommand($sql)->queryColumn();
- //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
- return @$count[0];
- //return @$count[0];
- }
-
- public static function rengetMTD($leadStatusId)
- {
- if (Yii::app()->session->get('type') == 'superadmin')
- {
- $cond = '';
- }
- else
- {
- if (count(@$_SESSION['relationalManager']) > 0)
- {
- $teamids = implode(",", $_SESSION['relationalManager']);
- }
- else
- {
- $teamids = Yii::app()->session->get('id');
- }
- $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
- }
-
- $financialyear = @$_POST['financialyear'];
- if (isset($financialyear) && !empty($financialyear))
- {
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);
- }
- else
- {
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
- }
-
- $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 ";
- $count = Yii::app()->db->createCommand($sql)->queryColumn();
-
- return @$count[0];
- //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
- }
-
- public static function rengetYTD($leadStatusId)
- {
-
- if (Yii::app()->session->get('type') == 'superadmin')
- {
- $cond = '';
- }
- else
- {
- if (count(@$_SESSION['relationalManager']) > 0)
- {
- $teamids = implode(",", $_SESSION['relationalManager']);
- }
- else
- {
- $teamids = Yii::app()->session->get('id');
- }
- $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";
- }
-
- $financialyear = @$_POST['financialyear'];
- if (isset($financialyear) && !empty($financialyear))
- {
- $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);
- $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);
- }
- else
- {
- $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));
- $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);
- }
-
- //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
- //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
- $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 ";
- $count = Yii::app()->db->createCommand($sql)->queryColumn();
- //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));
- return @$count[0];
- //return @$count[0];
- }
-
- /**
- * added by shakti.
- */
- public function actionWorkloadIncorrect()
- {
- $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
- $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;
- $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;
- $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);
-
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
-
- $leadCond = '';
-
- if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
- {
- $searchDate=$_POST['searchDashboard'];
- $searchTimestamp=strtotime(@$searchDate);
- $searchEndTimestamp=$searchTimestamp+86400;
- $leadCond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";
- }
-
- $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." ";
-
- $countcallerInfo=Yii::app()->db->createCommand($callerTeamSql)->queryAll();
-
- $dataProvidercaller = new CSqlDataProvider($callerTeamSql, array(
- 'keyField' => 'Id',
- 'totalItemCount'=>count($countcallerInfo),
- 'sort'=>array(
- 'attributes'=>array(
- 'Id',
- ),
- ),
- 'pagination'=>false,
- ));
-
- $this->renderPartial('_workload', array('dataProviderWorkload' => $dataProviderWorkload));
- }
-
- //Working
- public function actionWorkload()
- {
- $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
- $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;
- $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;
- $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);
-
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
-
- $leadCond = '';
-
- if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
- {
- $searchDate=$_POST['searchDashboard'];
- $searchTimestamp=strtotime(@$searchDate);
- $searchEndTimestamp=$searchTimestamp+86400;
- $leadCond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";
- }
-
- $filtercaller = @$_REQUEST['User']['userName'];
- $filtersource = @$_REQUEST['LeadSourceMaster']['leadSourceId'];
- $filterdate1 = @$_REQUEST['Lead']['createdOn'];
- $filterdate2 = @$_REQUEST['Lead']['createdOn2'];
- $filterCond = '';
-
-
- if (isset($filtercaller) && !empty($filtercaller))
- {
- $filterCond .= " AND lead.currentAllocatedId = " . $filtercaller . "";
- }
- if (isset($filtersource) && !empty($filtersource))
- {
- $filterCond .= " AND lead.leadSourceId = " . $filtersource . "";
- }
- if (isset($filterdate1) && !empty($filterdate1) && isset($filterdate2) && !empty($filterdate2))
- {
- $date1Filer = strtotime($filterdate1);
- $date2Filer = strtotime($filterdate2);
- $filterCond .= " AND lead.updatedOn >= " . $date1Filer . " AND lead.updatedOn <= " . $date2Filer . "";
- }
-
- if (Yii::app()->session->get('isAllocator') == 'yes')
- {
- $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();
- $impresuser = @$allocatorTl[0]['TLIDS'];
- }
- else
- {
- $impresuser = $this->implodeResult(Yii::app()->session['relationalManager']);
- }
-
-
- $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 ";
-
- $countTeamworkLoad=Yii::app()->db->createCommand($teamworkloadSql)->queryAll();
-
- $dataProviderWorkload = new CSqlDataProvider($teamworkloadSql, array(
- 'keyField' => 'Id',
-
- 'totalItemCount'=>count($countTeamworkLoad),
- 'sort'=>array(
- 'attributes'=>array(
- 'Id',
- ),
- ),
- 'pagination'=>array(
- 'pageSize'=>10,
- 'pageVar'=>'workload', //page selector
- ),
- ));
- $this->layout = false;
- $this->render('_workload', array('dataProviderWorkload' => $dataProviderWorkload));
- }
-
- public function actionSpeedometer()
- {
- $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();
- $managerIds = self::fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));
- if (empty($managerIds))
- {
- $managerIds[] = $_SESSION['id'];
- }
- else
- {
- $pusharray = array();
- $pusharray[] = @$_SESSION['id'];
- $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));
- }
- if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")
- {
- $teamLeaderTeamUserIdsRes = array();
- $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();
- if (count($teamLeaderTeamUserIds) > 0)
- {
- foreach ($teamLeaderTeamUserIds as $keyd => $valued)
- {
- $teamLeaderTeamUserIdsRes[] = $valued['userId'];
- }
- $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));
- }
- }
- $managerDetailsArr = self::ManagerDetails($managerIds);
-
- Yii::app()->session['relationalManager'] = $managerIds;
- //$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();
- $speedmeterUser = @$_REQUEST['speedmeterUser'];
- if (isset($speedmeterUser) && !empty($speedmeterUser))
- {
- $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);
- }
- else
- {
- $totalAmountOfSpeedometer = 0;
- }
-
-
- $totalClosedAmount = intval(@$totalAmountOfSpeedometer[0]['totalAmount']);
-
- if ($totalClosedAmount > 1000000)
- {
- $totalClosedAmount = 1000000;
- }
- $totalclosedAmount = 1000000;
- if ($totalClosedAmount > 0)
- {
- $totalClosedAmount = $totalClosedAmount;
- }
- else
- {
- $totalClosedAmount = 1;
- }
-
- $chartAmount = intval($totalclosedAmount - $totalClosedAmount);
- $this->layout = 'maindashboard';
- $this->render('_speedometer', array('totalClosedAmount' => $totalClosedAmount,'totalAmountOfSpeedometer'=>@$totalAmountOfSpeedometer, 'chartAmount' => $chartAmount, 'managerDetailsArr' => $managerDetailsArr));
- }
-
- public function actionMeeting()
- {
- $this->layout = false;
- $this->render('_meeting');
- }
-
- /**
-
- * Action LeadforToday. Loads monthly and yearly lead count.
-
- */
-
- public function actionLeadForToday()
- {
- $financialyear = @$_POST['financialyear'] ? $_POST['financialyear'] : date('Y');
-
- $monthKey = date('m');
-
- self::insertUsersRecord($financialyear, $monthKey);
-
-
-
- $this->layout = false;
-
- $this->renderPartial('_leadfortoday', array('dataProviderc' => self::getDataProviderc()));
-
-
- }
-
-
-
-
- public function actionAnnouncement()
-
- {
-
- $this->layout = false;
- $this->renderPartial('_announcement');
- }
-
-
-
- //In Working condition.
- public function actionPerformance()
- {
- $dataProviderPerf = self::getDataProviderPerf();
- $this->layout = false;
- $this->renderPartial('_performance', array('dataProviderPerf' => $dataProviderPerf));
- }
-
- protected static function SecondLevel($managerId, $userId)
- {
- return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId='$managerId' and user.managerId!='$userId' ")->queryAll();
- }
-
- protected static function ManagerLevel($userIds, $managerIds)
- {
- return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId ='$userIds' and id!='$managerIds' ")->queryAll();
- }
-
- protected static function ManagerDetails($userIds)
- {
- $userIds = self::UserWithManagerIdsImploded($userIds);
- if ($userIds != '')
- {
- return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();
- }
- }
-
- protected static function UserWithManagerIdsImploded($userIds)
- {
- $userIds = array_filter($userIds);
- return $userIds = implode(",", $userIds);
- }
-
- protected static function fetchAllmanagerLevelRecords(&$total_firstLevel, $ManagerDetails, $currentUserIds)
- {
- foreach ($ManagerDetails as $key => $value)
- {
- $total_firstLevel[] = $value['Id'];
- $total_res = self::SecondLevel($value['Id'], $currentUserIds);
- $res[] = count($total_res);
- if (count($total_res) > 0)
- {
- $o = 0;
- foreach ($total_res as $key1 => $value1)
- {
- $total_firstLevel[] = $value1['Id'];
- $net_res = self::fetchAllmanagerLevelRecords($total_firstLevel, self::ManagerLevel($value1['Id'],
- Yii::app()->session->get('id')), Yii::app()->session->get('id'));
- $o++;
- }
- }
- }
- return $total_firstLevel;
- }
-
- protected static function getDataProviderc()
- {
- $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `leadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";
-
- // $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";
-
- $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();
- $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(
- 'keyField' => 'leadstatusId',
- 'totalItemCount' => count($count),
- 'sort' => array(
- 'attributes' => array(
- 'leadStatusId',
- ),
- ),
- 'pagination' => array(
- 'pageSize' => 50,
- 'pageVar' => 'gyu', //page selector
- ),
- ));
-
- return $dataProviderc;
- }
-
- protected static function getDataProviderPerf()
- {
- $leadCond='';
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
- $CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
- $CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
-
- if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))
- {
- $searchDate=$_POST['searchDashboard'];
- $searchTimestamp=strtotime(@$searchDate);
- $searchEndTimestamp=$searchTimestamp+86400;
- $leadCond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";
- }
-
- if (Yii::app()->session->get('isAllocator') == 'yes')
- {
- $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();
-
- $impresuser = @$allocatorTl[0]['TLIDS'];
- }
- else
- {
- $impresuser = Yii::app()->Controller->implodeResult(Yii::app()->session['relationalManager']);
- }
-
- $performanceSql = "SELECT u.firstName,u.lastName,u.id as Id,SUM(IF(lead.updatedOn >= '".@$CurrentMonth_first_minute."' and lead.updatedOn <= '".@$CurrentMonth_last_minute."' and lead.leadStatusId = 7,1,0)) as MTD,SUM(IF(lead.updatedOn >= '".@$CurrentYear_first_minute."' and lead.updatedOn <= '".@$CurrentYear_last_minute."' and lead.leadStatusId = 7,1,0)) as YTD ,SUM(IF(lead.updatedOn >= '".@$CurrentYear_first_minute."' and lead.updatedOn <= '".@$CurrentYear_last_minute."' and lead.leadStatusId = 7,lead.premium,0)) as premium,SUM(IF(lead.updatedOn >= '".@$CurrentMonth_first_minute."' and lead.updatedOn <= '".time()."' and lead.leadStatusId = 7,lead.premium,0)) as mtdpremium,SUM(IF(lead.leadStatusId=7,1,0)) as closeLeads 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.") ".$leadCond." group By u.id ";
-
- $countPerf=Yii::app()->db->createCommand($performanceSql)->queryAll();
-
- $dataProviderPerf = new CSqlDataProvider($performanceSql, array(
- 'keyField' => 'Id',
-
- 'totalItemCount'=>count($countPerf),
- 'sort'=>array(
- 'attributes'=>array(
- 'Id',
- ),
- ),
- 'pagination'=>array(
- 'pageSize'=>10,
- 'pageVar'=>'performance', //page selector
- ),
- ));
-
- return $dataProviderPerf;
- }
-
- public static function getRequiredVariables()
- {
- global $today12PM;
- global $tomorrow12PM;
- global $yesterday12PM;
- global $LastMonth_first_minute;
- global $LastMonth_first_minute;
- global $CurrentMonth_first_minute;
- global $CurrentMonth_last_minute;
- global $filtercaller;
- global $filtersource;
- global $filterdate1;
- global $filterdate2;
- global $filterCond;
- global $searchDate;
- global $searchTimestamp;
- global $searchEndTimestamp;
- global $leadCond;
- global $impresuser;
-
- $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
- $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;
- $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;
- $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);
-
- $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
- $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
-
- $filtercaller = @$_REQUEST['User']['userName'];
- $filtersource = @$_REQUEST['LeadSourceMaster']['leadSourceId'];
- $filterdate1 = @$_REQUEST['Lead']['createdOn'];
- $filterdate2 = @$_REQUEST['Lead']['createdOn2'];
- $filterCond = '';
-
- if(isset($_POST['searchDashboard']))
- {
- $searchDate = $_POST['searchDashboard'];
- }
- else
- {
- $searchDate = date('Y');
- }
- $searchTimestamp = strtotime(@$searchDate);
- $searchEndTimestamp = $searchTimestamp + 86400;
-
- $leadCond = " and lead.updatedOn >= '" . @$searchTimestamp . "' and lead.updatedOn < '" . @$searchEndTimestamp . "'";
- if (isset($filtercaller) && !empty($filtercaller))
- {
- $filterCond .= " AND lead.currentAllocatedId = " . $filtercaller . "";
- }
- if (isset($filtersource) && !empty($filtersource))
- {
- $filterCond .= " AND lead.leadSourceId = " . $filtersource . "";
- }
- if (isset($filterdate1) && !empty($filterdate1) && isset($filterdate2) && !empty($filterdate2))
- {
- $date1Filer = strtotime($filterdate1);
- $date2Filer = strtotime($filterdate2);
- $filterCond .= " AND lead.updatedOn >= " . $date1Filer . " AND lead.updatedOn <= " . $date2Filer . "";
- }
-
- if (Yii::app()->session->get('isAllocator') == 'yes')
- {
- $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();
-
- $impresuser = @$allocatorTl[0]['TLIDS'];
- }
- else
- {
- $impresuser = Yii::app()->getController()->implodeResult(Yii::app()->session['relationalManager']);
- }
- }
-
- public static function getMeetingDate()
- {
- $relationManager = $_SESSION['relationalManager'];
- $managerMemberId = implode(",", array_unique(@$relationManager));
- Yii::app()->getClientScript()->registerScriptFile(Yii::app()->baseUrl . '/js/fullcalendar.js');
- $todayTime = strtotime(date('d M Y'));
- $timbefore3days = strtotime('-3 days', time());
- $timafter3days = strtotime('+3 days', time());
- $models = Yii::app()->db->createCommand("SELECT startDate, count(*) AS total,leadFollowupId FROM leadFollowup WHERE
- userId IN (" . @$managerMemberId . ") AND STATUS = 'Pending' AND (actionType = 'Meeting' OR actionType = 'Follow Up') AND (startDate >= $timbefore3days and startDate <= $timafter3days ) GROUP BY startDate DESC")->queryAll();
- $meetingArray = array();
- for ($i = 0; $i < count($models); $i++)
- {
- $meetingDate = @$models[$i]['startDate'];
- $totalCount = @$models[$i]['total'];
- $monthValue = date('m', @$models[$i]['startDate']);
-
- $meetingArray[$i]['totalCount'] = @$totalCount;
- $meetingArray[$i]['day'] = date('d', @$models[$i]['startDate']);
- $meetingArray[$i]['month'] = $monthValue - 1;
- $meetingArray[$i]['year'] = date('Y', @$models[$i]['startDate']);
- $meetingArray[$i]['leadFollowupId'] = @$models[$i]['leadFollowupId'];
-
- $meetingModels = LeadFollowup::model()->findAll(array("condition" => "startDate = '" . @$meetingDate . "' AND userId IN(" . @$managerMemberId . ") AND STATUS = 'Pending' AND (actionType = 'Meeting' OR actionType = 'Follow Up') ORDER BY hour DESC"));
- $meetingArray[$i]['finalDescription'] = '';
-
- for ($j = 0; $j < count($meetingModels); $j++)
- {
- $srno = $j + 1;
- $meetingArray[$i]['finalDescription'] = @$meetingDate;
- }
- }
- return $meetingArray;
- }
-
-
- public static function insertUserLeadAllCountRecord($financialyear, $monthKey = null, $userId = null){
- if(!$userId)
- {
- $userId = Yii::app()->session['id'];
- }
- $monthKey = $monthKey;
-
- $userCountRecord = Yii::app()->db->createCommand("select count(*) as count from leadallcount where userId = $userId && accesskey = '$financialyear-$monthKey'")->queryRow();
- if(!$userCountRecord['count'])
- {
- $queryString = "insert into leadallcount (userid, accesskey, leadtype, count, createdOn, createdBy, updatedOn, updatedBy) values(";
- $leadStatusRecords = Yii::app()->db->createCommand('select leadStatusId from leadstatus')->queryAll();
- $createdOn = time();
- $totalRecordsInserted = 0;
-
- Yii::app()->db->createCommand('START TRANSACTION;')->execute();
- foreach($leadStatusRecords as $leadStatusId)
- {
- $statusId = $leadStatusId['leadStatusId'];
- if($monthKey)
- {
- $getMTDStatusCount = DashboardController::getMTDForUser($statusId,$userId);
- $insertMTDStatusCount = "$queryString $userId, '$financialyear-$monthKey', $statusId, $getMTDStatusCount, $createdOn, $userId, $createdOn, $userId)";
- Yii::app()->db->createCommand($insertMTDStatusCount)->execute();
- $totalRecordsInserted = $totalRecordsInserted + 1;
- }
- $getYTDStatusCount = DashboardController::getYTDForUser($statusId,$userId);
- $insertYTDStatusCount = "$queryString $userId, '$financialyear', $statusId, $getYTDStatusCount, $createdOn, $userId, $createdOn, $userId)";
- Yii::app()->db->createCommand($insertYTDStatusCount)->execute();
- $totalRecordsInserted = $totalRecordsInserted + 1;
- }
- $totalLeadTypes = Yii::app()->db->createCommand('select count(*) as count from leadstatus')->queryRow();
- if(($totalRecordsInserted == $totalLeadTypes['count']*2 && $monthKey) || ($totalRecordsInserted == $totalLeadTypes['count'] && !$monthKey))
- {
- Yii::app()->db->createCommand('COMMIT;')->execute();
- }
- else
- {
- Yii::app()->db->createCommand('ROLLBACK;')->execute();
- }
- }
- }
- }