/lms_debug/protected/controllers/DashboardController.php

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

<?php



class DashboardController extends BaseController

{

    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.

     */

    public function actiondashboard()

    {

        $financialyear = @$_POST['financialyear'] ? $_POST['financialyear'] : date('Y');

        $month = date('m');

        if(BaseController::getConfigurationValue('leadallcount'))

        {

            self::insertUsersRecord($financialyear);

        }

        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();

        }



        $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 ";



        $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

            ),

        ));



        //Required Variables

        $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']);

        }

        //Required Variables

       

        $this->render('dashboard', array('LeadStatus' => @$LeadStatus, 'notifications' => @$notifications, 'managerDetailsArr' => @$managerDetailsArr, 'totalAmountOfSpeedometer' => @$totalAmountOfSpeedometer, 'dataProviderc' => $dataProviderc, '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'];

        if(Yii::app()->session->get('roleId') != 3)

        {

            $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']);

        }

    }



    /**

     * Get monthly lead count of User by leadstatus.

     * @param int $leadStatusId

     * @param int $userId

     * @return int

     */

    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];

    }



    /**

     * Get yearly lead count of User by leadstatus.

     * @param int $leadStatusId

     * @param int $userId

     * @return int

     */

    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);

        }

        

        $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();

        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));

        }

        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);

        }



        $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();

        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];

    }



    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);

        }



        $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();

        return @$count[0];

    }

    



    /**

     * Action Workload

     */

    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']);

        }



        //dataProviderWorkload

        $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…