PageRenderTime 50ms CodeModel.GetById 8ms RepoModel.GetById 1ms app.codeStats 0ms

/lms_debug/protected/controllers/DashboardController_24dec2014.php

https://gitlab.com/badelal143/lms_debug
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 
    

  1. <?php

    2. 

  2. 

    3. 

  3. class DashboardController extends Controller

    4. 

  4. {

    5. 

  5.     const SHARED_TITLE = "Admin Panel";

    6. 

  6.     /**

    7. 

  7.      * Declares class-based actions.

    8. 

  8.      */

    9. 

  9.     public function filters()

    10. 

  10.     {

    11. 

  11.         return array('accessControl', array(

    12. 

  12.                 'CHttpCacheFilter + view',

    13. 

  13.                 'cacheControl' => " max-age=604800, must-revalidate",

    14. 

  14.             )); // perform access control for CRUD operations

    15. 

  15.     }

    16. 

  16. 

    17. 

  17.     public function actions()

    18. 

  18.     {

    19. 

  19.         return array(

    20. 

  20.             // captcha action renders the CAPTCHA image displayed on the contact page

    21. 

  21.             'captcha' => array(

    22. 

  22.                 'class' => 'CCaptchaAction',

    23. 

  23.                 'backColor' => 0xFFFFFF,

    24. 

  24.             ),

    25. 

  25.             // page action renders "static" pages stored under 'protected/views/site/pages'

    26. 

  26.             // They can be accessed via: index.php?r=site/page&view=FileName

    27. 

  27.             'page' => array(

    28. 

  28.                 'class' => 'CViewAction',

    29. 

  29.             ),

    30. 

  30.         );

    31. 

  31.     }

    32. 

  32. 

    33. 

  33.     /**

    34. 

  34.      * This is the 'roles' action that is invoked

    35. 

  35.      * when an action is not explicitly requested by users.

    36. 

  36.      */

    37. 

  37.     protected function array_flatten($array)

    38. 

  38.     {

    39. 

  39.         if (!is_array($array))

    40. 

  40.         {

    41. 

  41.             return FALSE;

    42. 

  42.         }

    43. 

  43.         $result = array();

    44. 

  44.         foreach ($array as $key => $value)

    45. 

  45.         {

    46. 

  46.             if (is_array($value))

    47. 

  47.             {

    48. 

  48.                 $result = array_merge($result, $this->array_flatten($value));

    49. 

  49.             }

    50. 

  50.             else

    51. 

  51.             {

    52. 

  52.                 $result[$key] = $value;

    53. 

  53.             }

    54. 

  54.         }

    55. 

  55.         return $result;

    56. 

  56.     }

    57. 

  57. 

    58. 

  58.     

    59. 

  59.     

    60. 

  60.     /**

    61. 

  61.      * Action dashboard. //added by shakti. Please remove if not used.

    62. 

  62.      */

    63. 

  63.         public function actiondashboard()

    64. 

  64. 		{   

    65. 

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

    66. 

  66.         $month = date('m');

    67. 

  67.         self::insertUserLeadAllCountRecord($financialyear, $month);

    68. 

  68.         if (empty(Yii::app()->session['id']) or Yii::app()->session['id'] == '')

    69. 

  69.         {

    70. 

  70.             $this->redirect(array('site/index'));

    71. 

  71.         }

    72. 

  72.         $leadCond = '';

    73. 

  73.         $PiechartCond = '';

    74. 

  74.         $NotificationCond1 = '';

    75. 

  75.         $NotificationCond2 = '';

    76. 

  76.         $currentDateNot = date('d-m-Y');

    77. 

  77.         $currentTimestamp = strtotime($currentDateNot);

    78. 

  78. 

    79. 

  79.         if (isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))

    80. 

  80.         {

    81. 

  81.             $searchDate = $_POST['searchDashboard'];

    82. 

  82.             $searchTimestamp = strtotime(@$searchDate);

    83. 

  83.             $searchEndTimestamp = $searchTimestamp + 86400;

    84. 

  84.             $leadCond = " and lead.updatedOn >= '" . @$searchTimestamp . "' and lead.updatedOn < '" . @$searchEndTimestamp . "'";

    85. 

  85.             $PiechartCond = " and leadSourceMaster.updatedOn >= '" . @$searchTimestamp . "' and leadSourceMaster.updatedOn < '" . @$searchEndTimestamp . "'";

    86. 

  86. 

    87. 

  87.             $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') )";

    88. 

  88.             $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') )";

    89. 

  89.         }

    90. 

  90.         if (Yii::app()->session->get('isAllocator') == 'yes' or Yii::app()->session->get('type') == 'superadmin' or Yii::app()->session->get('roleId') == 1)

    91. 

  91.         {

    92. 

  92.             $LeasStatusCond = '';

    93. 

  93.         }

    94. 

  94.         else

    95. 

  95.         {

    96. 

  96.             $LeasStatusCond = ' AND lead.currentAllocatedId = ' . Yii::app()->session->get('id') . '';

    97. 

  97.         }

    98. 

  98. 

    99. 

  99. 

    100. 

  100.         $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    101. 

  101.         $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    102. 

  102.         $CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));

    103. 

  103.         $CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));

    104. 

  104. 

    105. 

  105.         $where = '';

    106. 

  106. 

    107. 

  107. 

    108. 

  108. 

    109. 

  109.         if (Yii::app()->session->get('type') != 'superadmin')

    110. 

  110.         {

    111. 

  111.             $where = ' where notificationMapping.roleId="' . Yii::app()->session->get('roleId') . '"';

    112. 

  112.             $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') )";

    113. 

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

    114. 

  114.         }

    115. 

  115.         else

    116. 

  116.         {

    117. 

  117.             $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') )";

    118. 

  118.             $notifications = Yii::app()->db->createCommand("select notification.* from notification left join notificationMapping on notificationMapping.notificationId=notification.notificationId  " . @$cls . " " . $NotificationCond2 . " group by notification.notificationId ")->queryAll();

    119. 

  119.         }

    120. 

  120. 

    121. 

  121.         $sourcewiseDetails = Yii::app()->db->createCommand("select leadSourceMaster.* from leadSourceMaster ")->queryAll();

    122. 

  122. 

    123. 

  123.         $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();

    124. 

  124.         

    125. 

  125. 

    126. 

  126.         $total_firstLevel = array();

    127. 

  127.         $total_nextLevel = array();

    128. 

  128.         

    129. 

  129.         $managerIds = self::fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));

    130. 

  130.         if (empty($managerIds))

    131. 

  131.         {

    132. 

  132.             $managerIds[] = $_SESSION['id'];

    133. 

  133.         }

    134. 

  134.         else

    135. 

  135.         {

    136. 

  136.             $pusharray = array();

    137. 

  137.             $pusharray[] = @$_SESSION['id'];

    138. 

  138.             $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));

    139. 

  139.         }

    140. 

  140.         if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")

    141. 

  141.         {

    142. 

  142.             $teamLeaderTeamUserIdsRes = array();

    143. 

  143.             $teamLeaderTeamUserIds = Yii::app()->db->createCommand("select id as userId from user where id IN (select distinct userId from teamUserMapping where teamId IN (select distinct teamId from teamUserMapping where userId='" . Yii::app()->session->get('id') . "') ) and status='Active' and type!='superadmin' ")->queryAll();

    144. 

  144.             if (count($teamLeaderTeamUserIds) > 0)

    145. 

  145.             {

    146. 

  146.                 foreach ($teamLeaderTeamUserIds as $keyd => $valued)

    147. 

  147.                 {

    148. 

  148.                     $teamLeaderTeamUserIdsRes[] = $valued['userId'];

    149. 

  149.                 }

    150. 

  150.                 $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));

    151. 

  151.             }

    152. 

  152.         }

    153. 

  153.         $managerDetailsArr = self::ManagerDetails($managerIds);

    154. 

  154. 

    155. 

  155.         Yii::app()->session['relationalManager'] = $managerIds;

    156. 

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

    157. 

  157.         $speedmeterUser = @$_REQUEST['speedmeterUser'];

    158. 

  158.         if (isset($speedmeterUser) && !empty($speedmeterUser))

    159. 

  159.         {

    160. 

  160.             $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);

    161. 

  161.         }

    162. 

  162.         else

    163. 

  163.         {

    164. 

  164.             $totalAmountOfSpeedometer = 0;

    165. 

  165.         }

    166. 

  166. 

    167. 

  167. 

    168. 

  168. 

    169. 

  169. 

    170. 

  170.         $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `leadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";

    171. 

  171. 

    172. 

  172.         // $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"; 

    173. 

  173. 

    174. 

  174. 

    175. 

  175.         $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();

    176. 

  176.         $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(

    177. 

  177.             'keyField' => 'leadstatusId',

    178. 

  178.             'totalItemCount' => count($count),

    179. 

  179.             'sort' => array(

    180. 

  180.                 'attributes' => array(

    181. 

  181.                     'leadStatusId',

    182. 

  182.                 ),

    183. 

  183.             ),

    184. 

  184.             'pagination' => array(

    185. 

  185.                 'pageSize' => 10,

    186. 

  186.                 'pageVar' => 'gyu', //page selector

    187. 

  187.             ),

    188. 

  188.         ));

    189. 

  189. 

    190. 

  190.         

    191. 

  191.         

    192. 

  192. 

    193. 

  193.         if (Yii::app()->session->get('isAllocator') == 'yes')

    194. 

  194.         {

    195. 

  195.             $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();

    196. 

  196. 

    197. 

  197.             $impresuser = @$allocatorTl[0]['TLIDS'];

    198. 

  198.         }

    199. 

  199.         else

    200. 

  200.         {

    201. 

  201.             $impresuser = $this->implodeResult(Yii::app()->session['relationalManager']);

    202. 

  202.         }

    203. 

  203. 

    204. 

  204. 

    205. 

  205.         //calculate performance

    206. 

  206.         /*

    207. 

  207.         $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));

    208. 

  208.         $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;

    209. 

  209.         $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;

    210. 

  210.         $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);

    211. 

  211.         //echo date('d M Y :: H:i:s',$CurrentMonth_first_minute); die;

    212. 

  212. 

    213. 

  213.         $filtercaller = @$_REQUEST['User']['userName'];

    214. 

  214.         $filtersource = @$_REQUEST['LeadSourceMaster']['leadSourceId'];

    215. 

  215.         $filterdate1 = @$_REQUEST['Lead']['createdOn'];

    216. 

  216.         $filterdate2 = @$_REQUEST['Lead']['createdOn2'];

    217. 

  217.         $filterCond = '';

    218. 

  218.         if (isset($filtercaller) && !empty($filtercaller))

    219. 

  219.         {

    220. 

  220.             $filterCond .= " AND lead.currentAllocatedId = " . $filtercaller . "";

    221. 

  221.         }

    222. 

  222.         if (isset($filtersource) && !empty($filtersource))

    223. 

  223.         {

    224. 

  224.             $filterCond .= " AND lead.leadSourceId = " . $filtersource . "";

    225. 

  225.         }

    226. 

  226.         if (isset($filterdate1) && !empty($filterdate1) && isset($filterdate2) && !empty($filterdate2))

    227. 

  227.         {

    228. 

  228.             $date1Filer = strtotime($filterdate1);

    229. 

  229.             $date2Filer = strtotime($filterdate2);

    230. 

  230.             $filterCond .= " AND lead.updatedOn >= " . $date1Filer . " AND lead.updatedOn <= " . $date2Filer . "";

    231. 

  231.         }

    232. 

  232. */

    233. 

  233.         /* commented by shakti

    234. 

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

    235. 

  235.         $countTeamworkLoad = Yii::app()->db->createCommand($teamworkloadSql)->queryAll();

    236. 

  236.         $dataProviderWorkload = new CSqlDataProvider($teamworkloadSql, array(

    237. 

  237.             'keyField' => 'Id',

    238. 

  238.             'totalItemCount' => count($countTeamworkLoad),

    239. 

  239.             'sort' => array(

    240. 

  240.                 'attributes' => array(

    241. 

  241.                     'Id',

    242. 

  242.                 ),

    243. 

  243.             ),

    244. 

  244.             'pagination' => array(

    245. 

  245.                 'pageSize' => 10,

    246. 

  246.                 'pageVar' => 'workload', //page selector

    247. 

  247.             ),

    248. 

  248.         ));

    249. 

  249.         */

    250. 

  250.         

    251. 

  251.         self::getRequiredVariables();

    252. 

  252.         

    253. 

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

    254. 

  254.         $countcallerInfo = Yii::app()->db->createCommand($callerTeamSql)->queryAll();

    255. 

  255.         $dataProvidercaller = new CSqlDataProvider($callerTeamSql, array(

    256. 

  256.             'keyField' => 'Id',

    257. 

  257.             'totalItemCount' => count($countcallerInfo),

    258. 

  258.             'sort' => array(

    259. 

  259.                 'attributes' => array(

    260. 

  260.                     'Id',

    261. 

  261.                 ),

    262. 

  262.             ),

    263. 

  263.             'pagination' => false,

    264. 

  264.         ));

    265. 

  265. 

    266. 

  266.         $dataProviderPerf = self::getDataProviderPerf();

    267. 

  267.         

    268. 

  268.         $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));

    269. 

  269.     }

    270. 

  270. 

    271. 

  271. 

    272. 

  272.  /**

    273. 

  273. 

    274. 

  274.      * Get Lead Status Count.(Eg Manager, add leadtypecount of all Teamleaders & Teamleader, add leadtypecount of all callers).

    275. 

  275. 

    276. 

  276.      * @see _leadfortoday.php

    277. 

  277. 

    278. 

  278.      * @return int

    279. 

  279. 

    280. 

  280.      */

    281. 

  281. 

    282. 

  282.     public static function getLeadStatusCount($leadStatusId, $accessKey)

    283. 

  283. 

    284. 

  284.     { 

    285. 

  285. 

    286. 

  286.         $userList = $userId = Yii::app()->session['id'];

    287. 

  287. 

    288. 

  288.         

    289. 

  289. 

    290. 

  290.         $userList = User::getMemebersList();  

    291. 

  291. 

    292. 

  292.             

    293. 

  293. 

    294. 

  294.         if(count($userList))

    295. 

  295. 

    296. 

  296.         {   

    297. 

  297. 

    298. 

  298.             $allCallersList = array();

    299. 

  299. 

    300. 

  300.             $lCount = array();

    301. 

  301. 

    302. 

  302.             foreach($userList as $list)

    303. 

  303. 

    304. 

  304.             {                       

    305. 

  305. 

    306. 

  306.                 $allCallersList[] = $list['id']; 

    307. 

  307. 

    308. 

  308.             }

    309. 

  309. 

    310. 

  310.             $userList = implode(', ', $allCallersList);                

    311. 

  311. 

    312. 

  312.             //print $userList . '==';

    313. 

  313. 

    314. 

  314.             $totalCount = Yii::app()->db->createCommand("select sum(count) as count from leadallcount where userid in ( $userList ) AND leadtype = $leadStatusId AND accesskey = '$accessKey'")->queryRow();                

    315. 

  315. 

    316. 

  316.             return $totalCount['count'];

    317. 

  317. 

    318. 

  318.         }

    319. 

  319. 

    320. 

  320.         

    321. 

  321. 

    322. 

  322.         $count = Yii::app()->db->createCommand("select sum(count) as count from leadallcount where userid = $userId AND leadtype = $leadStatusId AND accesskey = '$accessKey'")->queryRow();

    323. 

  323. 

    324. 

  324.         return $count['count'];

    325. 

  325. 

    326. 

  326.     }

    327. 

  327. 

    328. 

  328.  

    329. 

  329. 

    330. 

  330. 

    331. 

  331.     

    332. 

  332. 

    333. 

  333.     /**

    334. 

  334. 

    335. 

  335.      * Insert Users Record (Eg. call this method to insert all callers and the logged in teamleader (callers, teamleader & logged in manager)).

    336. 

  336. 

    337. 

  337.      * @see _leadfortoday.php

    338. 

  338. 

    339. 

  339.      * @return int

    340. 

  340. 

    341. 

  341.      */

    342. 

  342. 

    343. 

  343.     public static function insertUsersRecord($accessKey)

    344. 

  344. 

    345. 

  345.     { 

    346. 

  346. 

    347. 

  347.         $userList = User::getMemebersList();

    348. 

  348. 

    349. 

  349.  

    350. 

  350. 

    351. 

  351.         $financialDate = explode('-', $accessKey);

    352. 

  352. 

    353. 

  353.         $financialDate[0] = $financialDate[0] ? $financialDate[0] : date('Y');

    354. 

  354. 

    355. 

  355.         $financialDate[1] = @$financialDate[1] ? $financialDate[1] : date('m');

    356. 

  356. 

    357. 

  357.  

    358. 

  358. 

    359. 

  359.         if (count($userList))

    360. 

  360. 

    361. 

  361.         {

    362. 

  362. 

    363. 

  363.             foreach ($userList as $list)

    364. 

  364. 

    365. 

  365.             {

    366. 

  366. 

    367. 

  367.                 self::insertUserLeadAllCountRecord($financialDate[0], $financialDate[1], $list['id']);

    368. 

  368. 

    369. 

  369.             }

    370. 

  370. 

    371. 

  371.         }     else if(Yii::app()->session->get('type') == 'superadmin')

    372. 

  372. 

    373. 

  373.         {

    374. 

  374. 

    375. 

  375.             self::insertUserLeadAllCountRecord($financialDate[0], $financialDate[1], Yii::app()->session['id']);            

    376. 

  376. 

    377. 

  377.         }

    378. 

  378.    

    379. 

  379. 

    380. 

  380.     }

    381. 

  381. public static function getMTDForUser($leadStatusId, $userId)

    382. 

  382. 

    383. 

  383.     {

    384. 

  384. 

    385. 

  385.         if (Yii::app()->session->get('type') == 'superadmin')

    386. 

  386. 

    387. 

  387.         {

    388. 

  388. 

    389. 

  389.             $cond = '';

    390. 

  390. 

    391. 

  391.         }

    392. 

  392. 

    393. 

  393.         else

    394. 

  394. 

    395. 

  395.         {

    396. 

  396. 

    397. 

  397.             if (count(@$_SESSION['relationalManager']) > 0)

    398. 

  398. 

    399. 

  399.             {

    400. 

  400. 

    401. 

  401.                 $teamids = implode(",", $_SESSION['relationalManager']);

    402. 

  402. 

    403. 

  403.             }

    404. 

  404. 

    405. 

  405.             else

    406. 

  406. 

    407. 

  407.             {

    408. 

  408. 

    409. 

  409.                 $teamids = Yii::app()->session->get('id');

    410. 

  410. 

    411. 

  411.             }

    412. 

  412. 

    413. 

  413.             $cond = "AND l.currentAllocatedId = " . $userId;

    414. 

  414. 

    415. 

  415.         }

    416. 

  416. 

    417. 

  417.  

    418. 

  418. 

    419. 

  419.         $financialyear = @$_POST['financialyear'];

    420. 

  420. 

    421. 

  421.         if (isset($financialyear) && !empty($financialyear))

    422. 

  422. 

    423. 

  423.         {

    424. 

  424. 

    425. 

  425.             $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);

    426. 

  426. 

    427. 

  427.             $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);

    428. 

  428. 

    429. 

  429.         }

    430. 

  430. 

    431. 

  431.         else

    432. 

  432. 

    433. 

  433.         {

    434. 

  434. 

    435. 

  435.             $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    436. 

  436. 

    437. 

  437.             $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    438. 

  438. 

    439. 

  439.         }

    440. 

  440. 

    441. 

  441.  

    442. 

  442. 

    443. 

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

    444. 

  444. 

    445. 

  445.         $count = Yii::app()->db->createCommand($sql)->queryColumn();

    446. 

  446. 

    447. 

  447.  

    448. 

  448. 

    449. 

  449.         return @$count[0];

    450. 

  450. 

    451. 

  451.         //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));

    452. 

  452. 

    453. 

  453.     }

    454. 

  454. 

    455. 

  455.     public static function getYTDForUser($leadStatusId, $userId)

    456. 

  456. 

    457. 

  457.     {

    458. 

  458. 

    459. 

  459.  

    460. 

  460. 

    461. 

  461.         if (Yii::app()->session->get('type') == 'superadmin')

    462. 

  462. 

    463. 

  463.         {

    464. 

  464. 

    465. 

  465.             $cond = '';

    466. 

  466. 

    467. 

  467.         }

    468. 

  468. 

    469. 

  469.         else

    470. 

  470. 

    471. 

  471.         {

    472. 

  472. 

    473. 

  473.             if (count(@$_SESSION['relationalManager']) > 0)

    474. 

  474. 

    475. 

  475.             {

    476. 

  476. 

    477. 

  477.                 $teamids = implode(",", $_SESSION['relationalManager']);

    478. 

  478. 

    479. 

  479.             }

    480. 

  480. 

    481. 

  481.             else

    482. 

  482. 

    483. 

  483.             {

    484. 

  484. 

    485. 

  485.                 $teamids = Yii::app()->session->get('id');

    486. 

  486. 

    487. 

  487.             }

    488. 

  488. 

    489. 

  489.             $cond = "AND l.currentAllocatedId = " . $userId;

    490. 

  490. 

    491. 

  491.         }

    492. 

  492. 

    493. 

  493.  

    494. 

  494. 

    495. 

  495.         $financialyear = @$_POST['financialyear'];

    496. 

  496. 

    497. 

  497.         if (isset($financialyear) && !empty($financialyear))

    498. 

  498. 

    499. 

  499.         {

    500. 

  500. 

    501. 

  501.             $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);

    502. 

  502. 

    503. 

  503.             $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);

    504. 

  504. 

    505. 

  505.         }

    506. 

  506. 

    507. 

  507.         else

    508. 

  508. 

    509. 

  509.         {

    510. 

  510. 

    511. 

  511.             $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));

    512. 

  512. 

    513. 

  513.             $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);

    514. 

  514. 

    515. 

  515.         }

    516. 

  516. 

    517. 

  517.  

    518. 

  518. 

    519. 

  519.         //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));

    520. 

  520. 

    521. 

  521.         //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));

    522. 

  522. 

    523. 

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

    524. 

  524. 

    525. 

  525.         $count = Yii::app()->db->createCommand($sql)->queryColumn();

    526. 

  526. 

    527. 

  527.         //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));

    528. 

  528. 

    529. 

  529.         return @$count[0];

    530. 

  530. 

    531. 

  531.         //return @$count[0];

    532. 

  532. 

    533. 

  533.     }

    534. 

  534. 

    535. 

  535.  

    536. 

  536. 

    537. 

  537. 

    538. 

  538. 

    539. 

  539.     /**

    540. 

  540.      * This is the 'module master' action that is invoked

    541. 

  541.      * when an action is not explicitly requested by users.

    542. 

  542.      */

    543. 

  543.     public function actionmodule_master()

    544. 

  544.     {

    545. 

  545.         // renders the view file 'protected/views/site/index.php'

    546. 

  546.         // using the default layout 'protected/views/layouts/main.php'

    547. 

  547. 

    548. 

  548.         $model = new ModuleMaster();

    549. 

  549.         $model->scenario = 'ModuleMaster';

    550. 

  550. 

    551. 

  551.         if (!empty($_GET['moduleId']))

    552. 

  552.         {

    553. 

  553.             $moduleId = $_GET['moduleId'];

    554. 

  554.             $model = ModuleMaster::model()->findByPk($moduleId);

    555. 

  555.         }

    556. 

  556. 

    557. 

  557.         if (!empty($_POST['ModuleMaster']['moduleId']))

    558. 

  558.         {

    559. 

  559.             $moduleId = $_POST['ModuleMaster']['moduleId'];

    560. 

  560.             $model = ModuleMaster::model()->findByPk($moduleId);

    561. 

  561.         }

    562. 

  562. 

    563. 

  563.         if (isset($_POST['ModuleMaster']))

    564. 

  564.         {

    565. 

  565.             $model->setAttributes(array(

    566. 

  566.                 'createdOn' => time(), //additional data you want to insert

    567. 

  567.                 'updatedOn' => time(), //additional

    568. 

  568.                 'updatedBy' => time(), //additional

    569. 

  569.                 'createdBy' => time(),

    570. 

  570.                 'status' => 'Activated', //additional //additional

    571. 

  571.             ));

    572. 

  572.             foreach ($_POST['ModuleMaster'] as $name => $value)

    573. 

  573.             {

    574. 

  574.                 $model->$name = $value;

    575. 

  575.             }

    576. 

  576. 

    577. 

  577.             if ($model->validate())

    578. 

  578.             {

    579. 

  579.                 $model->save();

    580. 

  580.                 $model2 = ModuleMaster::model()->findAll();

    581. 

  581.                 $this->render('module_master', array('model' => $model, 'model2' => $model2));

    582. 

  582.                 Yii::app()->user->setFlash('module_master_msg_success', 'Successfully Inserted');

    583. 

  583.             }

    584. 

  584.             else

    585. 

  585.             {

    586. 

  586.                 Yii::app()->user->setFlash('module_master_msg_error', 'Please check. error occured');

    587. 

  587.                 $this->render('module_master', array('model' => $model));

    588. 

  588.             }

    589. 

  589.         }

    590. 

  590.         else

    591. 

  591.         {

    592. 

  592.             $model2 = ModuleMaster::model()->findAll();

    593. 

  593.             //Yii::app()->user->setFlash('lead_stage_msg_error','Please check. error occured');

    594. 

  594.             $this->render('module_master', array('model' => $model, 'model2' => $model2));

    595. 

  595.         }

    596. 

  596.     }

    597. 

  597. 

    598. 

  598.     /**

    599. 

  599.      * This is the action to handle external exceptions.

    600. 

  600.      */

    601. 

  601.     public function actionError()

    602. 

  602.     {

    603. 

  603.         if ($error = Yii::app()->errorHandler->error)

    604. 

  604.         {

    605. 

  605.             if (Yii::app()->request->isAjaxRequest)

    606. 

  606.                 echo $error['message'];

    607. 

  607.             else

    608. 

  608.                 $this->render('error', $error);

    609. 

  609.         }

    610. 

  610.     }

    611. 

  611. 

    612. 

  612.     /**

    613. 

  613.      * Displays the contact page

    614. 

  614.      */

    615. 

  615.     public function actionContact()

    616. 

  616.     {

    617. 

  617.         $model = new ContactForm;

    618. 

  618.         if (isset($_POST['ContactForm']))

    619. 

  619.         {

    620. 

  620.             $model->attributes = $_POST['ContactForm'];

    621. 

  621.             if ($model->validate())

    622. 

  622.             {

    623. 

  623.                 $name = '=?UTF-8?B?' . base64_encode($model->name) . '?=';

    624. 

  624.                 $subject = '=?UTF-8?B?' . base64_encode($model->subject) . '?=';

    625. 

  625.                 $headers = "From: $name <{$model->email}>\r\n" .

    626. 

  626.                     "Reply-To: {$model->email}\r\n" .

    627. 

  627.                     "MIME-Version: 1.0\r\n" .

    628. 

  628.                     "Content-type: text/plain; charset=UTF-8";

    629. 

  629. 

    630. 

  630.                 mail(Yii::app()->params['adminEmail'], $subject, $model->body, $headers);

    631. 

  631.                 Yii::app()->user->setFlash('contact', 'Thank you for contacting us. We will respond to you as soon as possible.');

    632. 

  632.                 $this->refresh();

    633. 

  633.             }

    634. 

  634.         }

    635. 

  635.         $this->render('contact', array('model' => $model));

    636. 

  636.     }

    637. 

  637. 

    638. 

  638.     /**

    639. 

  639.      * Displays the login page

    640. 

  640.      */

    641. 

  641.     public function actionLogin()

    642. 

  642.     {

    643. 

  643.         $model = new User;

    644. 

  644. 

    645. 

  645.         // if it is ajax validation request

    646. 

  646.         if (isset($_POST['ajax']) && $_POST['ajax'] === 'login-form')

    647. 

  647.         {

    648. 

  648.             echo CActiveForm::validate($model);

    649. 

  649.             Yii::app()->end();

    650. 

  650.         }

    651. 

  651. 

    652. 

  652.         // collect user input data

    653. 

  653.         if (isset($_POST['User']))

    654. 

  654.         {

    655. 

  655. 

    656. 

  656.             $model->attributes = $_POST['User'];

    657. 

  657.             // validate user input and redirect to the previous page if valid

    658. 

  658.             if ($model->validate() && $model->login())

    659. 

  659.             {

    660. 

  660.                 $this->redirect(array('masters/dashboard'));

    661. 

  661.             }

    662. 

  662.             else

    663. 

  663.             {

    664. 

  664.                 $this->redirect(Yii::app()->user->returnUrl);

    665. 

  665.             }

    666. 

  666.         }

    667. 

  667.         // display the login form

    668. 

  668.         $this->render('login', array('model' => $model));

    669. 

  669.     }

    670. 

  670. 

    671. 

  671.     /**

    672. 

  672.      * Logs out the current user and redirect to homepage.

    673. 

  673.      */

    674. 

  674.     public function actionLogout()

    675. 

  675.     {

    676. 

  676.         Yii::app()->user->logout();

    677. 

  677.         $this->redirect(Yii::app()->homeUrl);

    678. 

  678.     }

    679. 

  679. 

    680. 

  680.     public function getPageType()

    681. 

  681.     {

    682. 

  682.         return "";

    683. 

  683.     }

    684. 

  684. 

    685. 

  685.     public function actionViewFancyboxData()

    686. 

  686.     {

    687. 

  687.         $userId = @$_REQUEST['id'];

    688. 

  688.         $model = new User;

    689. 

  689.         $model = User::model()->findByPk($userId);

    690. 

  690.         if (!Yii::app()->request->isAjaxRequest)

    691. 

  691.         {

    692. 

  692.             // $this->render('yourView');

    693. 

  693.         }

    694. 

  694.         else

    695. 

  695.         {

    696. 

  696.             $this->renderPartial('/masters/partialNotification');

    697. 

  697.             Yii::app()->end();

    698. 

  698.         }

    699. 

  699.     }

    700. 

  700. 

    701. 

  701.     public function actionViewWorkLoadData()

    702. 

  702.     {

    703. 

  703.         if (!Yii::app()->request->isAjaxRequest)

    704. 

  704.         {

    705. 

  705. 		 echo "ddd";

    706. 

  706. 		// $this->render('/leads/partialWorkLoadData');

    707. 

  707.             // $this->render('yourView');

    708. 

  708.         }

    709. 

  709.         else

    710. 

  710.         {

    711. 

  711.             $this->renderPartial('/leads/partialWorkLoadData');

    712. 

  712.             Yii::app()->end();

    713. 

  713.         }

    714. 

  714.     }

    715. 

  715. 

    716. 

  716.     public function actionViewLeadSummaryData()

    717. 

  717.     {

    718. 

  718.         //$this->layout='column3';

    719. 

  719.         $this->layout = false;

    720. 

  720.         $parser = new CHtmlPurifier();

    721. 

  721.         $leadStatusId = $parser->purify(intval(@$_REQUEST['leadStatusId']));

    722. 

  722.         /* 			$model=new Lead;

    723. 

  723.           $model=Lead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");

    724. 

  724.          */ 

    725. 

  725.         if (!Yii::app()->request->isAjaxRequest)

    726. 

  726.         {

    727. 

  727.             $this->render('/leads/partialLeadSummary', array('leadStatusId' => $leadStatusId));

    728. 

  728.         }

    729. 

  729.         else

    730. 

  730.         {

    731. 

  731.             $this->layout=false;

    732. 

  732.             $this->render('/leads/partialLeadSummary', array('leadStatusId' => $leadStatusId));

    733. 

  733.             Yii::app()->end();

    734. 

  734.         }

    735. 

  735.     }

    736. 

  736. 

    737. 

  737.     public function actionRenewalViewLeadSummaryData()

    738. 

  738.     {

    739. 

  739.         //$this->layout='column3';

    740. 

  740.         $this->layout = false;

    741. 

  741.         $res = new MySanitizeClass;

    742. 

  742.         $leadStatusId = $res->sanitize_description(@$_REQUEST['leadStatusId']);

    743. 

  743.         /* 			$model=new Lead;

    744. 

  744.           $model=Lead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");

    745. 

  745.          */ if (!Yii::app()->request->isAjaxRequest)

    746. 

  746.         {

    747. 

  747.             $this->render('/renewalleads/renewalpartialLeadSummary', array('leadStatusId' => $leadStatusId));

    748. 

  748.         }

    749. 

  749.         else

    750. 

  750.         {

    751. 

  751.             //$this->layout=false;

    752. 

  752.             $this->render('/renewalleads/renewalpartialLeadSummary', array('leadStatusId' => $leadStatusId));

    753. 

  753.             Yii::app()->end();

    754. 

  754.         }

    755. 

  755.     }

    756. 

  756. 

    757. 

  757.     function getparentLeadStatus($parentId)

    758. 

  758.     {

    759. 

  759.         $primary = LeadStatus::model()->findByPk($parentId);

    760. 

  760.         if ($primary['parentId'] > 0)

    761. 

  761.         {

    762. 

  762.             $parentName = LeadStatus::model()->findByPk($primary['parentId']);

    763. 

  763.             $pname = $parentName['leadStatus'];

    764. 

  764.         }

    765. 

  765.         else

    766. 

  766.         {

    767. 

  767.             $pname = "Primary";

    768. 

  768.         }

    769. 

  769. 

    770. 

  770.         return $pname;

    771. 

  771.     }

    772. 

  772. 

    773. 

  773.     public function actionViewAllWorkLoadData()

    774. 

  774.     {

    775. 

  775. 

    776. 

  776.         function ManagerDetails($userIds)

    777. 

  777.         {

    778. 

  778.             $userIds = UserWithManagerIdsImploded($userIds);

    779. 

  779.             if ($userIds != '')

    780. 

  780.             {

    781. 

  781.                 return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();

    782. 

  782.             }

    783. 

  783.         }

    784. 

  784. 

    785. 

  785.         function UserWithManagerIdsImploded($userIds)

    786. 

  786.         {

    787. 

  787.             return $userIds = implode(",", $userIds);

    788. 

  788.         }

    789. 

  789. 

    790. 

  790.         $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);

    791. 

  791.         if (!Yii::app()->request->isAjaxRequest)

    792. 

  792.         {

    793. 

  793.             // $this->render('yourView');

    794. 

  794.         }

    795. 

  795.         else

    796. 

  796.         {

    797. 

  797.             $this->renderPartial('/leads/partialAllWorkLoadData', array('managerDetailsArr' => $managerDetailsArr));

    798. 

  798.             Yii::app()->end();

    799. 

  799.         }

    800. 

  800.     }

    801. 

  801. 

    802. 

  802.     public function actionViewAllPerformanceData()

    803. 

  803.     {

    804. 

  804. 

    805. 

  805.         function ManagerDetails($userIds)

    806. 

  806.         {

    807. 

  807.             $userIds = UserWithManagerIdsImploded($userIds);

    808. 

  808.             if ($userIds != '')

    809. 

  809.             {

    810. 

  810.                 return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();

    811. 

  811.             }

    812. 

  812.         }

    813. 

  813. 

    814. 

  814.         function UserWithManagerIdsImploded($userIds)

    815. 

  815.         {

    816. 

  816.             return $userIds = implode(",", $userIds);

    817. 

  817.         }

    818. 

  818. 

    819. 

  819.         $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);

    820. 

  820.         if (!Yii::app()->request->isAjaxRequest)

    821. 

  821.         {

    822. 

  822.             $this->render('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));

    823. 

  823.             // $this->render('yourView');

    824. 

  824.         }

    825. 

  825.         else

    826. 

  826.         {

    827. 

  827.             $this->renderPartial('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));

    828. 

  828.             Yii::app()->end();

    829. 

  829.         }

    830. 

  830.     }

    831. 

  831. 

    832. 

  832.     public function actionrenewal()

    833. 

  833.     {

    834. 

  834. 

    835. 

  835.         //$this->redirect(array('dashboard/dashboard'));	

    836. 

  836.         $leadCond = '';

    837. 

  837.         $PiechartCond = '';

    838. 

  838.         $NotificationCond1 = '';

    839. 

  839.         $NotificationCond2 = '';

    840. 

  840.         $currentDateNot = date('d-m-Y');

    841. 

  841.         $currentTimestamp = strtotime($currentDateNot);

    842. 

  842. 

    843. 

  843.         if (isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))

    844. 

  844.         {

    845. 

  845.             $searchDate = $_POST['searchDashboard'];

    846. 

  846.             $searchTimestamp = strtotime(@$searchDate);

    847. 

  847.             $searchEndTimestamp = $searchTimestamp + 86400;

    848. 

  848.             $leadCond = " and renewallead.updatedOn >= '" . @$searchTimestamp . "' and renewallead.updatedOn < '" . @$searchEndTimestamp . "'";

    849. 

  849.             $PiechartCond = " and renewalleadSourceMaster.updatedOn >= '" . @$searchTimestamp . "' and renewalleadSourceMaster.updatedOn < '" . @$searchEndTimestamp . "'";

    850. 

  850. 

    851. 

  851.             $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') )";

    852. 

  852.             $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') )";

    853. 

  853.         }

    854. 

  854.         if (Yii::app()->session->get('isAllocator') == 'yes' or Yii::app()->session->get('type') == 'superadmin')

    855. 

  855.         {

    856. 

  856.             $LeasStatusCond = '';

    857. 

  857.         }

    858. 

  858.         else

    859. 

  859.         {

    860. 

  860.             $LeasStatusCond = ' AND renewallead.currentAllocatedId = "' . Yii::app()->session->get('id') . '"';

    861. 

  861.         }

    862. 

  862.         $LeadStatus = Yii::app()->db->createCommand('SELECT 

    863. 

  863. 			me.leadStatusId AS me_id,

    864. 

  864. 			me.leadStatus AS me_leadStatus,

    865. 

  865. 			renewallead.leadStatusId AS lead_leadStatusId,

    866. 

  866. 			renewallead.leadId AS leadId,

    867. 

  867. 			count(*) AS total

    868. 

  868. 			FROM

    869. 

  869. 			renewalleadStatus me 

    870. 

  870. 			left join renewallead on renewallead.leadStatusId=me.leadStatusId where renewallead.leadStatusId > 0 ' . $leadCond . ' ' . $LeasStatusCond . ' group by renewallead.leadStatusId')->queryAll();

    871. 

  871. 

    872. 

  872.         $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `renewalleadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";

    873. 

  873. 

    874. 

  874.         // $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"; 

    875. 

  875. 

    876. 

  876. 

    877. 

  877.         $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();

    878. 

  878.         $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(

    879. 

  879.             'keyField' => 'leadstatusId',

    880. 

  880.             'totalItemCount' => count($count),

    881. 

  881.             'sort' => array(

    882. 

  882.                 'attributes' => array(

    883. 

  883.                     'leadStatusId',

    884. 

  884.                 ),

    885. 

  885.             ),

    886. 

  886.             'pagination' => array(

    887. 

  887.                 'pageSize' => 10,

    888. 

  888.                 'pageVar' => 'gyu', //page selector

    889. 

  889.             ),

    890. 

  890.         ));

    891. 

  891. 

    892. 

  892. 

    893. 

  893. 

    894. 

  894. 

    895. 

  895.         $where = '';

    896. 

  896. 

    897. 

  897.         if (Yii::app()->session->get('type') != 'superadmin')

    898. 

  898.         {

    899. 

  899.             $where = ' where renewalnotificationMapping.roleId="' . Yii::app()->session->get('roleId') . '"';

    900. 

  900.             $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') )";

    901. 

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

    902. 

  902.         }

    903. 

  903.         else

    904. 

  904.         {

    905. 

  905.             $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') )";

    906. 

  906.             $notifications = Yii::app()->db->createCommand("select renewalnotification.* from renewalnotification left join renewalnotificationMapping on renewalnotificationMapping.notificationId=renewalnotification.notificationId  " . @$cls . " " . $NotificationCond2 . " group by renewalnotification.notificationId ")->queryAll();

    907. 

  907.         }

    908. 

  908. 

    909. 

  909.         $sourcewiseDetails = Yii::app()->db->createCommand("select renewalleadSourceMaster.* from renewalleadSourceMaster ")->queryAll();

    910. 

  910. 

    911. 

  911.         $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();

    912. 

  912.         function SecondLevel($managerId, $userId)

    913. 

  913.         {

    914. 

  914.             return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId='$managerId' and user.managerId!='$userId' ")->queryAll();

    915. 

  915.         }

    916. 

  916. 

    917. 

  917.         function ManagerLevel($userIds, $managerIds)

    918. 

  918.         {

    919. 

  919.             return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId ='$userIds' and id!='$managerIds'   ")->queryAll();

    920. 

  920.         }

    921. 

  921. 

    922. 

  922.         function ManagerDetails($userIds)

    923. 

  923.         {

    924. 

  924.             $userIds = UserWithManagerIdsImploded($userIds);

    925. 

  925.             if ($userIds != '')

    926. 

  926.             {

    927. 

  927.                 return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();

    928. 

  928.             }

    929. 

  929.         }

    930. 

  930. 

    931. 

  931.         function UserWithManagerIdsImploded($userIds)

    932. 

  932.         {

    933. 

  933.             return $userIds = implode(",", $userIds);

    934. 

  934.         }

    935. 

  935. 

    936. 

  936.         $total_firstLevel = array();

    937. 

  937.         $total_nextLevel = array();

    938. 

  938.         function fetchAllmanagerLevelRecords(&$total_firstLevel, $ManagerDetails, $currentUserIds)

    939. 

  939.         {

    940. 

  940.             foreach ($ManagerDetails as $key => $value)

    941. 

  941.             {

    942. 

  942.                 $total_firstLevel[] = $value['Id'];

    943. 

  943.                 $total_res = SecondLevel($value['Id'], $currentUserIds);

    944. 

  944.                 $res[] = count($total_res);

    945. 

  945.                 if (count($total_res) > 0)

    946. 

  946.                 {

    947. 

  947.                     $o = 0;

    948. 

  948.                     foreach ($total_res as $key1 => $value1)

    949. 

  949.                     {

    950. 

  950.                         $total_firstLevel[] = $value1['Id'];

    951. 

  951.                         $net_res = fetchAllmanagerLevelRecords($total_firstLevel, ManagerLevel($value1['Id'], Yii::app()->session->get('id')), Yii::app()->session->get('id'));

    952. 

  952.                         $o++;

    953. 

  953.                     }

    954. 

  954.                 }

    955. 

  955.             }

    956. 

  956.             return $total_firstLevel;

    957. 

  957.         }

    958. 

  958. 

    959. 

  959.         $managerIds = fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));

    960. 

  960.         if (empty($managerIds))

    961. 

  961.         {

    962. 

  962.             $managerIds[] = $_SESSION['id'];

    963. 

  963.         }

    964. 

  964.         else

    965. 

  965.         {

    966. 

  966.             $pusharray = array();

    967. 

  967.             $pusharray[] = @$_SESSION['id'];

    968. 

  968.             $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));

    969. 

  969.         }

    970. 

  970.         if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")

    971. 

  971.         {

    972. 

  972.             $teamLeaderTeamUserIdsRes = array();

    973. 

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

    974. 

  974.             if (count($teamLeaderTeamUserIds) > 0)

    975. 

  975.             {

    976. 

  976.                 foreach ($teamLeaderTeamUserIds as $keyd => $valued)

    977. 

  977.                 {

    978. 

  978.                     $teamLeaderTeamUserIdsRes[] = $valued['userId'];

    979. 

  979.                 }

    980. 

  980.                 $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));

    981. 

  981.             }

    982. 

  982.         }

    983. 

  983.         $managerDetailsArr = ManagerDetails($managerIds);

    984. 

  984.         Yii::app()->session['relationalManager'] = $managerIds;

    985. 

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

    986. 

  986.         $speedmeterUser = @$_REQUEST['speedmeterUser'];

    987. 

  987.         if (isset($speedmeterUser) && !empty($speedmeterUser))

    988. 

  988.         {

    989. 

  989.             $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);

    990. 

  990.         }

    991. 

  991.         else

    992. 

  992.         {

    993. 

  993.             $totalAmountOfSpeedometer = 0;

    994. 

  994.         }

    995. 

  995.         $this->render('renewaldashboard', array('LeadStatus' => @$LeadStatus, 'notifications' => @$notifications, 'sourcewiseDetails' => @$sourcewiseDetails, 'sourcewiseDetailsWithPiechart' => @$sourcewiseDetailsWithPiechart, 'managerDetailsArr' => @$managerDetailsArr, 'totalAmountOfSpeedometer' => @$totalAmountOfSpeedometer, 'dataProviderc' => $dataProviderc));

    996. 

  996.     }

    997. 

  997. 

    998. 

  998.     public function actionrenViewLeadSummaryData()

    999. 

  999.     {

    1000. 

  1000.         $this->layout = 'column3';

    1001. 

  1001.         $leadStatusId = @$_REQUEST['leadStatusId'];

    1002. 

  1002.         //$model=new Renewallead;

    1003. 

  1003.         //$model=Renewallead::model()->findAll("leadStatusId = '".@$leadStatusId."' ");

    1004. 

  1004.         if (!Yii::app()->request->isAjaxRequest)

    1005. 

  1005.         {

    1006. 

  1006.             $this->redirect('dashboard/renewal');

    1007. 

  1007.         }

    1008. 

  1008.         else

    1009. 

  1009.         {

    1010. 

  1010. 

    1011. 

  1011.             $this->render('/renewalleads/partialLeadSummary', array('leadStatusId' => $leadStatusId));

    1012. 

  1012.             Yii::app()->end();

    1013. 

  1013.         }

    1014. 

  1014.     }

    1015. 

  1015. 

    1016. 

  1016.     public function actionrenViewWorkLoadData()

    1017. 

  1017.     {

    1018. 

  1018.         if (!Yii::app()->request->isAjaxRequest)

    1019. 

  1019.         {

    1020. 

  1020.             // $this->render('yourView');

    1021. 

  1021.         }

    1022. 

  1022.         else

    1023. 

  1023.         {

    1024. 

  1024.             $this->renderPartial('/renewalleads/partialWorkLoadData');

    1025. 

  1025.             Yii::app()->end();

    1026. 

  1026.         }

    1027. 

  1027.     }

    1028. 

  1028. 

    1029. 

  1029.     public function actionrenViewAllWorkLoadData()

    1030. 

  1030.     {

    1031. 

  1031. 

    1032. 

  1032.         function ManagerDetails($userIds)

    1033. 

  1033.         {

    1034. 

  1034.             $userIds = UserWithManagerIdsImploded($userIds);

    1035. 

  1035.             if ($userIds != '')

    1036. 

  1036.             {

    1037. 

  1037.                 return Yii::app()->db->createCommand("select id from user where user.Id IN ($userIds) ")->queryAll();

    1038. 

  1038.             }

    1039. 

  1039.         }

    1040. 

  1040. 

    1041. 

  1041.         function UserWithManagerIdsImploded($userIds)

    1042. 

  1042.         {

    1043. 

  1043.             return $userIds = implode(",", $userIds);

    1044. 

  1044.         }

    1045. 

  1045. 

    1046. 

  1046.         $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);

    1047. 

  1047.         if (!Yii::app()->request->isAjaxRequest)

    1048. 

  1048.         {

    1049. 

  1049.             // $this->render('yourView');

    1050. 

  1050.         }

    1051. 

  1051.         else

    1052. 

  1052.         {

    1053. 

  1053.             $this->renderPartial('/renewalleads/partialAllWorkLoadData', array('managerDetailsArr' => $managerDetailsArr));

    1054. 

  1054.             Yii::app()->end();

    1055. 

  1055.         }

    1056. 

  1056.     }

    1057. 

  1057. 

    1058. 

  1058.     public function actionrenViewFancyboxData()

    1059. 

  1059.     {

    1060. 

  1060.         $userId = @$_REQUEST['id'];

    1061. 

  1061.         $model = new User;

    1062. 

  1062.         $model = User::model()->findByPk($userId);

    1063. 

  1063.         if (!Yii::app()->request->isAjaxRequest)

    1064. 

  1064.         {

    1065. 

  1065.             // $this->render('yourView');

    1066. 

  1066.         }

    1067. 

  1067.         else

    1068. 

  1068.         {

    1069. 

  1069.             $this->renderPartial('/renewalmasters/partialNotification');

    1070. 

  1070.             Yii::app()->end();

    1071. 

  1071.         }

    1072. 

  1072.     }

    1073. 

  1073. 

    1074. 

  1074.     public function actionrenViewAllPerformanceData()

    1075. 

  1075.     {

    1076. 

  1076. 

    1077. 

  1077.         function ManagerDetails($userIds)

    1078. 

  1078.         {

    1079. 

  1079.             $userIds = UserWithManagerIdsImploded($userIds);

    1080. 

  1080.             if ($userIds != '')

    1081. 

  1081.             {

    1082. 

  1082.                 return Yii::app()->db->createCommand("select id from user where user.Id IN ($userIds) ")->queryAll();

    1083. 

  1083.             }

    1084. 

  1084.         }

    1085. 

  1085. 

    1086. 

  1086.         function UserWithManagerIdsImploded($userIds)

    1087. 

  1087.         {

    1088. 

  1088.             return $userIds = implode(",", array_filter($userIds));

    1089. 

  1089.         }

    1090. 

  1090. 

    1091. 

  1091.         $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);

    1092. 

  1092.         if (!Yii::app()->request->isAjaxRequest)

    1093. 

  1093.         {

    1094. 

  1094.             $this->render('/renewalleads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));

    1095. 

  1095.             // $this->render('yourView');

    1096. 

  1096.         }

    1097. 

  1097.         else

    1098. 

  1098.         {

    1099. 

  1099.             $this->renderPartial('/leads/partialAllPerfLoadData', array('managerDetailsArr' => $managerDetailsArr));

    1100. 

  1100.             Yii::app()->end();

    1101. 

  1101.         }

    1102. 

  1102.     }

    1103. 

  1103. 

    1104. 

  1104.     public function actionfullcalender()

    1105. 

  1105.     {

    1106. 

  1106.         // renders the view file 'protected/views/site/index.php'

    1107. 

  1107.         // using the default layout 'protected/views/layouts/main.php'

    1108. 

  1108. 

    1109. 

  1109.         $this->render('fullcalender');

    1110. 

  1110.     }

    1111. 

  1111. 

    1112. 

  1112.     public function actionmyteamworkload()

    1113. 

  1113.     {

    1114. 

  1114.         // renders the view file 'protected/views/site/index.php'

    1115. 

  1115.         // using the default layout 'protected/views/layouts/main.php'

    1116. 

  1116.         $this->layout = false;

    1117. 

  1117.         function ManagerDetails($userIds)

    1118. 

  1118.         {

    1119. 

  1119.             $userIds = UserWithManagerIdsImploded($userIds);

    1120. 

  1120.             if ($userIds != '')

    1121. 

  1121.             {

    1122. 

  1122.                 return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();

    1123. 

  1123.             }

    1124. 

  1124.         }

    1125. 

  1125. 

    1126. 

  1126.         function UserWithManagerIdsImploded($userIds)

    1127. 

  1127.         {

    1128. 

  1128.             return $userIds = implode(",", array_filter($userIds));

    1129. 

  1129.         }

    1130. 

  1130. 

    1131. 

  1131.         $managerDetailsArr = ManagerDetails(Yii::app()->session['relationalManager']);

    1132. 

  1132.         $this->render('myteamworkload', array('managerDetailsArr' => @$managerDetailsArr));

    1133. 

  1133.     }

    1134. 

  1134. 

    1135. 

  1135.     public static function implodeResult($userIds)

    1136. 

  1136.     {

    1137. 

  1137.         $userIds = array_filter($userIds);

    1138. 

  1138.         return $userIds = implode(",", $userIds);

    1139. 

  1139.     }

    1140. 

  1140. 

    1141. 

  1141.     public function actionshowLeadCount()

    1142. 

  1142.     {

    1143. 

  1143.         $this->layout = false;

    1144. 

  1144.         $this->render('showLeadCount');

    1145. 

  1145.     }

    1146. 

  1146. 

    1147. 

  1147.     public function actionshowleadcountdisp()

    1148. 

  1148.     {

    1149. 

  1149.         $this->layout = false;

    1150. 

  1150.         $this->render('showleadcountdisp');

    1151. 

  1151.     }

    1152. 

  1152. 

    1153. 

  1153.     public static function getMTD($leadStatusId)

    1154. 

  1154.     {

    1155. 

  1155.         if (Yii::app()->session->get('type') == 'superadmin')

    1156. 

  1156.         {

    1157. 

  1157.             $cond = '';

    1158. 

  1158.         }

    1159. 

  1159.         else

    1160. 

  1160.         {

    1161. 

  1161.             if (count(@$_SESSION['relationalManager']) > 0)

    1162. 

  1162.             {

    1163. 

  1163.                 $teamids = implode(",", $_SESSION['relationalManager']);

    1164. 

  1164.             }

    1165. 

  1165.             else

    1166. 

  1166.             {

    1167. 

  1167.                 $teamids = Yii::app()->session->get('id');

    1168. 

  1168.             }

    1169. 

  1169.             $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";

    1170. 

  1170.         }

    1171. 

  1171. 

    1172. 

  1172.         $financialyear = @$_POST['financialyear'];

    1173. 

  1173.         if (isset($financialyear) && !empty($financialyear))

    1174. 

  1174.         {

    1175. 

  1175.             $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);

    1176. 

  1176.             $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);

    1177. 

  1177.         }

    1178. 

  1178.         else

    1179. 

  1179.         {

    1180. 

  1180.             $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    1181. 

  1181.             $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    1182. 

  1182.         }

    1183. 

  1183. 

    1184. 

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

    1185. 

  1185.         $count = Yii::app()->db->createCommand($sql)->queryColumn();

    1186. 

  1186. 

    1187. 

  1187.         return @$count[0];

    1188. 

  1188.         //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));

    1189. 

  1189.     }

    1190. 

  1190. 

    1191. 

  1191.     public static function getYTD($leadStatusId)

    1192. 

  1192.     {

    1193. 

  1193. 

    1194. 

  1194.         if (Yii::app()->session->get('type') == 'superadmin')

    1195. 

  1195.         {

    1196. 

  1196.             $cond = '';

    1197. 

  1197.         }

    1198. 

  1198.         else

    1199. 

  1199.         {

    1200. 

  1200.             if (count(@$_SESSION['relationalManager']) > 0)

    1201. 

  1201.             {

    1202. 

  1202.                 $teamids = implode(",", $_SESSION['relationalManager']);

    1203. 

  1203.             }

    1204. 

  1204.             else

    1205. 

  1205.             {

    1206. 

  1206.                 $teamids = Yii::app()->session->get('id');

    1207. 

  1207.             }

    1208. 

  1208.             $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";

    1209. 

  1209.         }

    1210. 

  1210. 

    1211. 

  1211.         $financialyear = @$_POST['financialyear'];

    1212. 

  1212.         if (isset($financialyear) && !empty($financialyear))

    1213. 

  1213.         {

    1214. 

  1214.             $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);

    1215. 

  1215.             $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);

    1216. 

  1216.         }

    1217. 

  1217.         else

    1218. 

  1218.         {

    1219. 

  1219.             $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));

    1220. 

  1220.             $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);

    1221. 

  1221.         }

    1222. 

  1222. 

    1223. 

  1223.         //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));

    1224. 

  1224.         //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));

    1225. 

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

    1226. 

  1226.         $count = Yii::app()->db->createCommand($sql)->queryColumn();

    1227. 

  1227.         //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));

    1228. 

  1228.         return @$count[0];

    1229. 

  1229.         //return @$count[0];

    1230. 

  1230.     }

    1231. 

  1231. 

    1232. 

  1232.     public static function rengetMTD($leadStatusId)

    1233. 

  1233.     {

    1234. 

  1234.         if (Yii::app()->session->get('type') == 'superadmin')

    1235. 

  1235.         {

    1236. 

  1236.             $cond = '';

    1237. 

  1237.         }

    1238. 

  1238.         else

    1239. 

  1239.         {

    1240. 

  1240.             if (count(@$_SESSION['relationalManager']) > 0)

    1241. 

  1241.             {

    1242. 

  1242.                 $teamids = implode(",", $_SESSION['relationalManager']);

    1243. 

  1243.             }

    1244. 

  1244.             else

    1245. 

  1245.             {

    1246. 

  1246.                 $teamids = Yii::app()->session->get('id');

    1247. 

  1247.             }

    1248. 

  1248.             $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";

    1249. 

  1249.         }

    1250. 

  1250. 

    1251. 

  1251.         $financialyear = @$_POST['financialyear'];

    1252. 

  1252.         if (isset($financialyear) && !empty($financialyear))

    1253. 

  1253.         {

    1254. 

  1254.             $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1, $financialyear);

    1255. 

  1255.             $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"), $financialyear);

    1256. 

  1256.         }

    1257. 

  1257.         else

    1258. 

  1258.         {

    1259. 

  1259.             $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    1260. 

  1260.             $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    1261. 

  1261.         }

    1262. 

  1262. 

    1263. 

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

    1264. 

  1264.         $count = Yii::app()->db->createCommand($sql)->queryColumn();

    1265. 

  1265. 

    1266. 

  1266.         return @$count[0];

    1267. 

  1267.         //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));

    1268. 

  1268.     }

    1269. 

  1269. 

    1270. 

  1270.     public static function rengetYTD($leadStatusId)

    1271. 

  1271.     {

    1272. 

  1272. 

    1273. 

  1273.         if (Yii::app()->session->get('type') == 'superadmin')

    1274. 

  1274.         {

    1275. 

  1275.             $cond = '';

    1276. 

  1276.         }

    1277. 

  1277.         else

    1278. 

  1278.         {

    1279. 

  1279.             if (count(@$_SESSION['relationalManager']) > 0)

    1280. 

  1280.             {

    1281. 

  1281.                 $teamids = implode(",", $_SESSION['relationalManager']);

    1282. 

  1282.             }

    1283. 

  1283.             else

    1284. 

  1284.             {

    1285. 

  1285.                 $teamids = Yii::app()->session->get('id');

    1286. 

  1286.             }

    1287. 

  1287.             $cond = "AND l.currentAllocatedId IN (" . @$teamids . ")";

    1288. 

  1288.         }

    1289. 

  1289. 

    1290. 

  1290.         $financialyear = @$_POST['financialyear'];

    1291. 

  1291.         if (isset($financialyear) && !empty($financialyear))

    1292. 

  1292.         {

    1293. 

  1293.             $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, $financialyear);

    1294. 

  1294.             $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, $financialyear + 1);

    1295. 

  1295.         }

    1296. 

  1296.         else

    1297. 

  1297.         {

    1298. 

  1298.             $CurrentYear_first_minute = mktime(0, 0, 0, 4, 1, date('Y'));

    1299. 

  1299.             $CurrentYear_last_minute = mktime(0, 0, 0, 4, 0, date('Y') + 1);

    1300. 

  1300.         }

    1301. 

  1301. 

    1302. 

  1302.         //$CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));

    1303. 

  1303.         //$CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));

    1304. 

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

    1305. 

  1305.         $count = Yii::app()->db->createCommand($sql)->queryColumn();

    1306. 

  1306.         //echo Lead::model()->count(array("select"=>'leadid',"condition"=>" leadStatusId = ".@$leadStatusId.""));

    1307. 

  1307.         return @$count[0];

    1308. 

  1308.         //return @$count[0];

    1309. 

  1309.     }

    1310. 

  1310.     

    1311. 

  1311.     /**

    1312. 

  1312.      * added by shakti.

    1313. 

  1313.      */

    1314. 

  1314.     public function actionWorkloadIncorrect()

    1315. 

  1315.     {           

    1316. 

  1316.         $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));

    1317. 

  1317.         $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;

    1318. 

  1318.         $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;

    1319. 

  1319.         $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);

    1320. 

  1320.         

    1321. 

  1321.         $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    1322. 

  1322.         $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    1323. 

  1323.         

    1324. 

  1324.         $leadCond = '';

    1325. 

  1325.         

    1326. 

  1326.         if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))

    1327. 

  1327.         {

    1328. 

  1328. 			$searchDate=$_POST['searchDashboard'];

    1329. 

  1329. 			$searchTimestamp=strtotime(@$searchDate);

    1330. 

  1330. 			$searchEndTimestamp=$searchTimestamp+86400;

    1331. 

  1331. 			$leadCond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";			

    1332. 

  1332. 		}

    1333. 

  1333.         

    1334. 

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

    1335. 

  1335.         

    1336. 

  1336.         $countcallerInfo=Yii::app()->db->createCommand($callerTeamSql)->queryAll();

    1337. 

  1337.         

    1338. 

  1338.         $dataProvidercaller = new CSqlDataProvider($callerTeamSql, array(

    1339. 

  1339.                                                     'keyField' => 'Id',

    1340. 

  1340.                                                     'totalItemCount'=>count($countcallerInfo),

    1341. 

  1341.                                                     'sort'=>array(

    1342. 

  1342.                                                        'attributes'=>array(

    1343. 

  1343.                                                                        'Id',

    1344. 

  1344.                                                         ),

    1345. 

  1345.                                                     ),

    1346. 

  1346.                                                     'pagination'=>false,

    1347. 

  1347.                                                     ));

    1348. 

  1348.         

    1349. 

  1349.         $this->renderPartial('_workload', array('dataProviderWorkload' => $dataProviderWorkload));

    1350. 

  1350.     }

    1351. 

  1351.     

    1352. 

  1352.     //Working

    1353. 

  1353.     public function actionWorkload()

    1354. 

  1354.     {

    1355. 

  1355.         $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));

    1356. 

  1356.         $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;

    1357. 

  1357.         $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;

    1358. 

  1358.         $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);

    1359. 

  1359.         

    1360. 

  1360.         $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    1361. 

  1361.         $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    1362. 

  1362.         

    1363. 

  1363.         $leadCond = '';

    1364. 

  1364.         

    1365. 

  1365.         if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))

    1366. 

  1366.         {

    1367. 

  1367. 			$searchDate=$_POST['searchDashboard'];

    1368. 

  1368. 			$searchTimestamp=strtotime(@$searchDate);

    1369. 

  1369. 			$searchEndTimestamp=$searchTimestamp+86400;

    1370. 

  1370. 			$leadCond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";			

    1371. 

  1371. 		}

    1372. 

  1372.         

    1373. 

  1373.         $filtercaller = @$_REQUEST['User']['userName'];

    1374. 

  1374.         $filtersource = @$_REQUEST['LeadSourceMaster']['leadSourceId'];

    1375. 

  1375.         $filterdate1  = @$_REQUEST['Lead']['createdOn'];

    1376. 

  1376.         $filterdate2  = @$_REQUEST['Lead']['createdOn2'];

    1377. 

  1377.         $filterCond   = '';

    1378. 

  1378. 

    1379. 

  1379.         

    1380. 

  1380.         if (isset($filtercaller) && !empty($filtercaller))

    1381. 

  1381.         {

    1382. 

  1382.             $filterCond .= " AND lead.currentAllocatedId = " . $filtercaller . "";

    1383. 

  1383.         }

    1384. 

  1384.         if (isset($filtersource) && !empty($filtersource))

    1385. 

  1385.         {

    1386. 

  1386.             $filterCond .= " AND lead.leadSourceId = " . $filtersource . "";

    1387. 

  1387.         }

    1388. 

  1388.         if (isset($filterdate1) && !empty($filterdate1) && isset($filterdate2) && !empty($filterdate2))

    1389. 

  1389.         {

    1390. 

  1390.             $date1Filer = strtotime($filterdate1);

    1391. 

  1391.             $date2Filer = strtotime($filterdate2);

    1392. 

  1392.             $filterCond .= " AND lead.updatedOn >= " . $date1Filer . " AND lead.updatedOn <= " . $date2Filer . "";

    1393. 

  1393.         }        

    1394. 

  1394.         

    1395. 

  1395.         if (Yii::app()->session->get('isAllocator') == 'yes')

    1396. 

  1396.         {

    1397. 

  1397.             $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();

    1398. 

  1398.             $impresuser = @$allocatorTl[0]['TLIDS'];

    1399. 

  1399.         }

    1400. 

  1400.         else

    1401. 

  1401.         {

    1402. 

  1402.             $impresuser = $this->implodeResult(Yii::app()->session['relationalManager']);

    1403. 

  1403.         }

    1404. 

  1404.         

    1405. 

  1405.         

    1406. 

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

    1407. 

  1407.         

    1408. 

  1408. 		$countTeamworkLoad=Yii::app()->db->createCommand($teamworkloadSql)->queryAll();

    1409. 

  1409.         

    1410. 

  1410.         $dataProviderWorkload = new CSqlDataProvider($teamworkloadSql, array(

    1411. 

  1411.                                                         'keyField' => 'Id',

    1412. 

  1412. 

    1413. 

  1413.                                                         'totalItemCount'=>count($countTeamworkLoad),

    1414. 

  1414.                                                         'sort'=>array(

    1415. 

  1415.                                                            'attributes'=>array(

    1416. 

  1416.                                                                            'Id',

    1417. 

  1417.                                                             ),

    1418. 

  1418.                                                         ),

    1419. 

  1419.                                                         'pagination'=>array(

    1420. 

  1420.                                                         'pageSize'=>10,

    1421. 

  1421.                                                         'pageVar'=>'workload', //page selector

    1422. 

  1422.                                                         ),

    1423. 

  1423.                 ));

    1424. 

  1424.         $this->layout = false;

    1425. 

  1425.         $this->render('_workload', array('dataProviderWorkload' => $dataProviderWorkload));

    1426. 

  1426.     }

    1427. 

  1427.     

    1428. 

  1428.     public function actionSpeedometer()

    1429. 

  1429.     {        

    1430. 

  1430.         $ManagerDetails = Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where managerId='" . Yii::app()->session->get('id') . "'")->queryAll();

    1431. 

  1431.         $managerIds = self::fetchAllmanagerLevelRecords($total_firstLevel, $ManagerDetails, Yii::app()->session->get('id'));

    1432. 

  1432.         if (empty($managerIds))

    1433. 

  1433.         {

    1434. 

  1434.             $managerIds[] = $_SESSION['id'];

    1435. 

  1435.         }

    1436. 

  1436.         else

    1437. 

  1437.         {

    1438. 

  1438.             $pusharray = array();

    1439. 

  1439.             $pusharray[] = @$_SESSION['id'];

    1440. 

  1440.             $managerIds = array_unique(array_merge(@$managerIds, @$pusharray));

    1441. 

  1441.         }

    1442. 

  1442.         if (strtolower(Yii::app()->session->get('roleName')) == "teamleader")

    1443. 

  1443.         {

    1444. 

  1444.             $teamLeaderTeamUserIdsRes = array();

    1445. 

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

    1446. 

  1446.             if (count($teamLeaderTeamUserIds) > 0)

    1447. 

  1447.             {

    1448. 

  1448.                 foreach ($teamLeaderTeamUserIds as $keyd => $valued)

    1449. 

  1449.                 {

    1450. 

  1450.                     $teamLeaderTeamUserIdsRes[] = $valued['userId'];

    1451. 

  1451.                 }

    1452. 

  1452.                 $managerIds = array_unique(array_merge($managerIds, $teamLeaderTeamUserIdsRes));

    1453. 

  1453.             }

    1454. 

  1454.         }

    1455. 

  1455.         $managerDetailsArr = self::ManagerDetails($managerIds);

    1456. 

  1456. 

    1457. 

  1457.         Yii::app()->session['relationalManager'] = $managerIds;

    1458. 

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

    1459. 

  1459.         $speedmeterUser = @$_REQUEST['speedmeterUser'];

    1460. 

  1460.         if (isset($speedmeterUser) && !empty($speedmeterUser))

    1461. 

  1461.         {

    1462. 

  1462.             $totalAmountOfSpeedometer = Lead::model()->closedLeadsAmount($speedmeterUser);

    1463. 

  1463.         }

    1464. 

  1464.         else

    1465. 

  1465.         {

    1466. 

  1466.             $totalAmountOfSpeedometer = 0;

    1467. 

  1467.         }

    1468. 

  1468.         

    1469. 

  1469.         

    1470. 

  1470.         $totalClosedAmount = intval(@$totalAmountOfSpeedometer[0]['totalAmount']);

    1471. 

  1471.         

    1472. 

  1472.         if ($totalClosedAmount > 1000000)

    1473. 

  1473.         {

    1474. 

  1474.             $totalClosedAmount = 1000000;

    1475. 

  1475.         }

    1476. 

  1476.         $totalclosedAmount = 1000000;

    1477. 

  1477.         if ($totalClosedAmount > 0)

    1478. 

  1478.         {

    1479. 

  1479.             $totalClosedAmount = $totalClosedAmount;

    1480. 

  1480.         }

    1481. 

  1481.         else

    1482. 

  1482.         {

    1483. 

  1483.             $totalClosedAmount = 1;

    1484. 

  1484.         }

    1485. 

  1485.         

    1486. 

  1486.         $chartAmount = intval($totalclosedAmount - $totalClosedAmount);

    1487. 

  1487. 		 $this->layout = 'maindashboard';

    1488. 

  1488.         $this->render('_speedometer', array('totalClosedAmount' => $totalClosedAmount,'totalAmountOfSpeedometer'=>@$totalAmountOfSpeedometer, 'chartAmount' => $chartAmount, 'managerDetailsArr' => $managerDetailsArr));

    1489. 

  1489.     }

    1490. 

  1490. 

    1491. 

  1491.     public function actionMeeting()

    1492. 

  1492.     {

    1493. 

  1493.         $this->layout = false;

    1494. 

  1494.         $this->render('_meeting');

    1495. 

  1495.     }

    1496. 

  1496.     

    1497. 

  1497.    /**

    1498. 

  1498. 

    1499. 

  1499.      * Action LeadforToday. Loads monthly and yearly lead count.

    1500. 

  1500. 

    1501. 

  1501.      */

    1502. 

  1502. 

    1503. 

  1503.     public function actionLeadForToday()

    1504. 

  1504. 	{        

    1505. 

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

    1506. 

  1506. 

    1507. 

  1507.         $monthKey        = date('m');   

    1508. 

  1508. 

    1509. 

  1509.         self::insertUsersRecord($financialyear, $monthKey);

    1510. 

  1510. 

    1511. 

  1511.         

    1512. 

  1512. 

    1513. 

  1513.         $this->layout = false;        

    1514. 

  1514. 

    1515. 

  1515.         $this->renderPartial('_leadfortoday', array('dataProviderc' => self::getDataProviderc()));

    1516. 

  1516. 

    1517. 

  1517. 

    1518. 

  1518.     }

    1519. 

  1519. 

    1520. 

  1520.  

    1521. 

  1521. 

    1522. 

  1522. 

    1523. 

  1523.     public function actionAnnouncement()

    1524. 

  1524. 

    1525. 

  1525.     {

    1526. 

  1526. 		

    1527. 

  1527. 	$this->layout = false;        

    1528. 

  1528. 	$this->renderPartial('_announcement');

    1529. 

  1529.     }

    1530. 

  1530. 

    1531. 

  1531. 

    1532. 

  1532.     

    1533. 

  1533.     //In Working condition.

    1534. 

  1534.     public function actionPerformance()

    1535. 

  1535.     {               

    1536. 

  1536.         $dataProviderPerf = self::getDataProviderPerf();

    1537. 

  1537.         $this->layout = false;        

    1538. 

  1538.         $this->renderPartial('_performance', array('dataProviderPerf' => $dataProviderPerf));

    1539. 

  1539.     }    

    1540. 

  1540.     

    1541. 

  1541.     protected static function SecondLevel($managerId, $userId)

    1542. 

  1542.     {

    1543. 

  1543.         return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId='$managerId' and user.managerId!='$userId' ")->queryAll();

    1544. 

  1544.     }

    1545. 

  1545.                 

    1546. 

  1546.     protected static function ManagerLevel($userIds, $managerIds)

    1547. 

  1547.     {

    1548. 

  1548.         return Yii::app()->db->createCommand("select user.managerId,user.Id,user.userName from user where user.managerId ='$userIds' and id!='$managerIds'   ")->queryAll();

    1549. 

  1549.     }

    1550. 

  1550.     

    1551. 

  1551.     protected static function ManagerDetails($userIds)

    1552. 

  1552.     {

    1553. 

  1553.         $userIds = self::UserWithManagerIdsImploded($userIds);

    1554. 

  1554.         if ($userIds != '')

    1555. 

  1555.         {

    1556. 

  1556.             return Yii::app()->db->createCommand("select * from user where user.Id IN ($userIds) ")->queryAll();

    1557. 

  1557.         }

    1558. 

  1558.     }

    1559. 

  1559.     

    1560. 

  1560.     protected static function UserWithManagerIdsImploded($userIds)

    1561. 

  1561.     {

    1562. 

  1562.         $userIds = array_filter($userIds);

    1563. 

  1563.       return $userIds = implode(",", $userIds);

    1564. 

  1564.     }

    1565. 

  1565.     

    1566. 

  1566.     protected static function fetchAllmanagerLevelRecords(&$total_firstLevel, $ManagerDetails, $currentUserIds)

    1567. 

  1567.     {

    1568. 

  1568.         foreach ($ManagerDetails as $key => $value)

    1569. 

  1569.         {

    1570. 

  1570.             $total_firstLevel[] = $value['Id'];

    1571. 

  1571.             $total_res = self::SecondLevel($value['Id'], $currentUserIds);

    1572. 

  1572.             $res[] = count($total_res);

    1573. 

  1573.             if (count($total_res) > 0)

    1574. 

  1574.             {

    1575. 

  1575.                 $o = 0;

    1576. 

  1576.                 foreach ($total_res as $key1 => $value1)

    1577. 

  1577.                 {

    1578. 

  1578.                     $total_firstLevel[] = $value1['Id'];

    1579. 

  1579.                     $net_res = self::fetchAllmanagerLevelRecords($total_firstLevel, self::ManagerLevel($value1['Id'],

    1580. 

  1580.                                                                  Yii::app()->session->get('id')), Yii::app()->session->get('id'));

    1581. 

  1581.                     $o++;

    1582. 

  1582.                 }

    1583. 

  1583.             }

    1584. 

  1584.         }

    1585. 

  1585.         return $total_firstLevel;

    1586. 

  1586.     }

    1587. 

  1587. 

    1588. 

  1588.     protected static function getDataProviderc()

    1589. 

  1589.     {

    1590. 

  1590.         $LeadStatusSql = "SELECT ls.leadstatus,ls.leadstatusId,ls.sortOrder FROM `leadstatus` ls where ls.status='Active' order by ls.sortOrder ASC ";

    1591. 

  1591. 

    1592. 

  1592.         // $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"; 

    1593. 

  1593. 

    1594. 

  1594.         $count = Yii::app()->db->createCommand($LeadStatusSql)->queryAll();

    1595. 

  1595.         $dataProviderc = new CSqlDataProvider($LeadStatusSql, array(

    1596. 

  1596.             'keyField' => 'leadstatusId',

    1597. 

  1597.             'totalItemCount' => count($count),

    1598. 

  1598.             'sort' => array(

    1599. 

  1599.                 'attributes' => array(

    1600. 

  1600.                     'leadStatusId',

    1601. 

  1601.                 ),

    1602. 

  1602.             ),

    1603. 

  1603.             'pagination' => array(

    1604. 

  1604.                 'pageSize' => 50,

    1605. 

  1605.                 'pageVar' => 'gyu', //page selector

    1606. 

  1606.             ),

    1607. 

  1607.         ));

    1608. 

  1608.         

    1609. 

  1609.         return $dataProviderc;

    1610. 

  1610.     }   

    1611. 

  1611.     

    1612. 

  1612.     protected static function getDataProviderPerf()

    1613. 

  1613.     {

    1614. 

  1614.         $leadCond='';    

    1615. 

  1615.         $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    1616. 

  1616. 		$CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    1617. 

  1617.         $CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));

    1618. 

  1618.         $CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));

    1619. 

  1619.             

    1620. 

  1620. 		if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard']))

    1621. 

  1621.         {

    1622. 

  1622. 			$searchDate=$_POST['searchDashboard'];

    1623. 

  1623. 			$searchTimestamp=strtotime(@$searchDate);

    1624. 

  1624. 			$searchEndTimestamp=$searchTimestamp+86400;

    1625. 

  1625. 			$leadCond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";			

    1626. 

  1626. 		}

    1627. 

  1627.         

    1628. 

  1628.         if (Yii::app()->session->get('isAllocator') == 'yes')

    1629. 

  1629.         {

    1630. 

  1630.             $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();

    1631. 

  1631. 

    1632. 

  1632.             $impresuser = @$allocatorTl[0]['TLIDS'];

    1633. 

  1633.         }

    1634. 

  1634.         else

    1635. 

  1635.         {

    1636. 

  1636.             $impresuser = Yii::app()->Controller->implodeResult(Yii::app()->session['relationalManager']);

    1637. 

  1637.         }

    1638. 

  1638.         

    1639. 

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

    1640. 

  1640.         

    1641. 

  1641.         $countPerf=Yii::app()->db->createCommand($performanceSql)->queryAll();

    1642. 

  1642. 		 

    1643. 

  1643.         $dataProviderPerf = new CSqlDataProvider($performanceSql, array(

    1644. 

  1644.                                                         'keyField' => 'Id',

    1645. 

  1645. 

    1646. 

  1646.                                                         'totalItemCount'=>count($countPerf),

    1647. 

  1647.                                                         'sort'=>array(

    1648. 

  1648.                                                            'attributes'=>array(

    1649. 

  1649.                                                                            'Id',

    1650. 

  1650.                                                             ),

    1651. 

  1651.                                                         ),

    1652. 

  1652.                                                         'pagination'=>array(

    1653. 

  1653.                                                         'pageSize'=>10,

    1654. 

  1654.                                                         'pageVar'=>'performance', //page selector

    1655. 

  1655.                                                         ),

    1656. 

  1656. 						));

    1657. 

  1657. 

    1658. 

  1658.        return $dataProviderPerf;

    1659. 

  1659.     }

    1660. 

  1660.     

    1661. 

  1661.     public static function getRequiredVariables()

    1662. 

  1662.     {

    1663. 

  1663.         global $today12PM;

    1664. 

  1664.         global $tomorrow12PM;

    1665. 

  1665.         global $yesterday12PM;

    1666. 

  1666.         global $LastMonth_first_minute;

    1667. 

  1667.         global $LastMonth_first_minute;

    1668. 

  1668.         global $CurrentMonth_first_minute;

    1669. 

  1669.         global $CurrentMonth_last_minute;

    1670. 

  1670.         global $filtercaller;

    1671. 

  1671.         global $filtersource;

    1672. 

  1672.         global $filterdate1;

    1673. 

  1673.         global $filterdate2;

    1674. 

  1674.         global $filterCond;

    1675. 

  1675.         global $searchDate;

    1676. 

  1676.         global $searchTimestamp;

    1677. 

  1677.         global $searchEndTimestamp;

    1678. 

  1678.         global $leadCond;

    1679. 

  1679.         global $impresuser;

    1680. 

  1680.         

    1681. 

  1681.         $today12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y"));

    1682. 

  1682.         $tomorrow12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) + 86400;

    1683. 

  1683.         $yesterday12PM = mktime(0, 0, 0, date("m"), date("d"), date("Y")) - 86400;

    1684. 

  1684.         $LastMonth_first_minute = mktime(0, 0, 0, date("n") - 1, 1);

    1685. 

  1685.         

    1686. 

  1686.         $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);

    1687. 

  1687.         $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));

    1688. 

  1688.         

    1689. 

  1689.         $filtercaller = @$_REQUEST['User']['userName'];

    1690. 

  1690.         $filtersource = @$_REQUEST['LeadSourceMaster']['leadSourceId'];

    1691. 

  1691.         $filterdate1 = @$_REQUEST['Lead']['createdOn'];

    1692. 

  1692.         $filterdate2 = @$_REQUEST['Lead']['createdOn2'];

    1693. 

  1693.         $filterCond = '';

    1694. 

  1694.         

    1695. 

  1695.         if(isset($_POST['searchDashboard']))

    1696. 

  1696.         {

    1697. 

  1697.             $searchDate = $_POST['searchDashboard'];

    1698. 

  1698.         }

    1699. 

  1699.         else

    1700. 

  1700.         {

    1701. 

  1701.             $searchDate = date('Y');

    1702. 

  1702.         }

    1703. 

  1703.         $searchTimestamp = strtotime(@$searchDate);

    1704. 

  1704.         $searchEndTimestamp = $searchTimestamp + 86400;

    1705. 

  1705.         

    1706. 

  1706.         $leadCond = " and lead.updatedOn >= '" . @$searchTimestamp . "' and lead.updatedOn < '" . @$searchEndTimestamp . "'";

    1707. 

  1707.         if (isset($filtercaller) && !empty($filtercaller))

    1708. 

  1708.         {

    1709. 

  1709.             $filterCond .= " AND lead.currentAllocatedId = " . $filtercaller . "";

    1710. 

  1710.         }

    1711. 

  1711.         if (isset($filtersource) && !empty($filtersource))

    1712. 

  1712.         {

    1713. 

  1713.             $filterCond .= " AND lead.leadSourceId = " . $filtersource . "";

    1714. 

  1714.         }

    1715. 

  1715.         if (isset($filterdate1) && !empty($filterdate1) && isset($filterdate2) && !empty($filterdate2))

    1716. 

  1716.         {

    1717. 

  1717.             $date1Filer = strtotime($filterdate1);

    1718. 

  1718.             $date2Filer = strtotime($filterdate2);

    1719. 

  1719.             $filterCond .= " AND lead.updatedOn >= " . $date1Filer . " AND lead.updatedOn <= " . $date2Filer . "";

    1720. 

  1720.         }

    1721. 

  1721.         

    1722. 

  1722.         if (Yii::app()->session->get('isAllocator') == 'yes')

    1723. 

  1723.         {

    1724. 

  1724.             $allocatorTl = Yii::app()->db->createCommand("select group_concat(distinct id) as TLIDS from user where roleId=4")->queryAll();

    1725. 

  1725. 

    1726. 

  1726.             $impresuser = @$allocatorTl[0]['TLIDS'];

    1727. 

  1727.         }

    1728. 

  1728.         else

    1729. 

  1729.         {

    1730. 

  1730.             $impresuser = Yii::app()->getController()->implodeResult(Yii::app()->session['relationalManager']);

    1731. 

  1731.         }

    1732. 

  1732.     }

    1733. 

  1733.     

    1734. 

  1734.     public static function getMeetingDate()

    1735. 

  1735.     {

    1736. 

  1736.         $relationManager = $_SESSION['relationalManager'];

    1737. 

  1737.         $managerMemberId = implode(",", array_unique(@$relationManager));

    1738. 

  1738.         Yii::app()->getClientScript()->registerScriptFile(Yii::app()->baseUrl . '/js/fullcalendar.js');

    1739. 

  1739.         $todayTime = strtotime(date('d M Y'));

    1740. 

  1740.         $timbefore3days = strtotime('-3 days', time());

    1741. 

  1741.         $timafter3days = strtotime('+3 days', time());

    1742. 

  1742.         $models = Yii::app()->db->createCommand("SELECT startDate,	count(*) AS total,leadFollowupId	FROM leadFollowup WHERE 

    1743. 

  1743.                 userId IN (" . @$managerMemberId . ") AND STATUS = 'Pending' AND (actionType = 'Meeting' OR actionType = 'Follow Up') AND (startDate >= $timbefore3days and startDate <= $timafter3days ) GROUP BY startDate DESC")->queryAll();

    1744. 

  1744.         $meetingArray = array();

    1745. 

  1745.         for ($i = 0; $i < count($models); $i++)

    1746. 

  1746.         {

    1747. 

  1747.             $meetingDate = @$models[$i]['startDate'];

    1748. 

  1748.             $totalCount = @$models[$i]['total'];

    1749. 

  1749.             $monthValue = date('m', @$models[$i]['startDate']);

    1750. 

  1750. 

    1751. 

  1751.             $meetingArray[$i]['totalCount'] = @$totalCount;

    1752. 

  1752.             $meetingArray[$i]['day'] = date('d', @$models[$i]['startDate']);

    1753. 

  1753.             $meetingArray[$i]['month'] = $monthValue - 1;

    1754. 

  1754.             $meetingArray[$i]['year'] = date('Y', @$models[$i]['startDate']);

    1755. 

  1755.             $meetingArray[$i]['leadFollowupId'] = @$models[$i]['leadFollowupId'];

    1756. 

  1756. 

    1757. 

  1757.             $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"));

    1758. 

  1758.             $meetingArray[$i]['finalDescription'] = '';

    1759. 

  1759. 

    1760. 

  1760.             for ($j = 0; $j < count($meetingModels); $j++)

    1761. 

  1761.             {

    1762. 

  1762.                 $srno = $j + 1;

    1763. 

  1763.                 $meetingArray[$i]['finalDescription'] = @$meetingDate;

    1764. 

  1764.             }

    1765. 

  1765.         }

    1766. 

  1766.         return $meetingArray;

    1767. 

  1767.     }

    1768. 

  1768. 	

    1769. 

  1769. 	

    1770. 

  1770.   public static function insertUserLeadAllCountRecord($financialyear, $monthKey = null, $userId = null){

    1771. 

  1771.         if(!$userId)

    1772. 

  1772.         {

    1773. 

  1773.             $userId          = Yii::app()->session['id'];           

    1774. 

  1774.         }

    1775. 

  1775. 		$monthKey        = $monthKey;

    1776. 

  1776. 		

    1777. 

  1777.         $userCountRecord = Yii::app()->db->createCommand("select count(*) as count from leadallcount where userId = $userId && accesskey = '$financialyear-$monthKey'")->queryRow();

    1778. 

  1778.         if(!$userCountRecord['count'])

    1779. 

  1779.         {   

    1780. 

  1780.             $queryString          = "insert into leadallcount (userid, accesskey, leadtype, count, createdOn, createdBy, updatedOn, updatedBy) values(";    

    1781. 

  1781.             $leadStatusRecords    = Yii::app()->db->createCommand('select leadStatusId from leadstatus')->queryAll();

    1782. 

  1782.             $createdOn            = time();

    1783. 

  1783.             $totalRecordsInserted = 0;

    1784. 

  1784.             

    1785. 

  1785.             Yii::app()->db->createCommand('START TRANSACTION;')->execute();

    1786. 

  1786.             foreach($leadStatusRecords as $leadStatusId)

    1787. 

  1787.             {

    1788. 

  1788.                 $statusId             = $leadStatusId['leadStatusId'];

    1789. 

  1789.                 if($monthKey)

    1790. 

  1790.                 {

    1791. 

  1791.                     $getMTDStatusCount    = DashboardController::getMTDForUser($statusId,$userId);                

    1792. 

  1792.                     $insertMTDStatusCount = "$queryString $userId, '$financialyear-$monthKey', $statusId, $getMTDStatusCount, $createdOn, $userId, $createdOn, $userId)";

    1793. 

  1793.                     Yii::app()->db->createCommand($insertMTDStatusCount)->execute();

    1794. 

  1794.                     $totalRecordsInserted = $totalRecordsInserted + 1;

    1795. 

  1795.                 }

    1796. 

  1796.                 $getYTDStatusCount    = DashboardController::getYTDForUser($statusId,$userId);

    1797. 

  1797.                 $insertYTDStatusCount = "$queryString $userId, '$financialyear', $statusId, $getYTDStatusCount, $createdOn, $userId, $createdOn, $userId)";

    1798. 

  1798.                 Yii::app()->db->createCommand($insertYTDStatusCount)->execute();         

    1799. 

  1799.                 $totalRecordsInserted = $totalRecordsInserted + 1;

    1800. 

  1800.             }  

    1801. 

  1801.             $totalLeadTypes   = Yii::app()->db->createCommand('select count(*) as count from leadstatus')->queryRow();  

    1802. 

  1802.             if(($totalRecordsInserted == $totalLeadTypes['count']*2 && $monthKey) || ($totalRecordsInserted == $totalLeadTypes['count'] && !$monthKey))

    1803. 

  1803.             {

    1804. 

  1804.                 Yii::app()->db->createCommand('COMMIT;')->execute();

    1805. 

  1805.             }

    1806. 

  1806.             else

    1807. 

  1807.             {

    1808. 

  1808.                 Yii::app()->db->createCommand('ROLLBACK;')->execute();

    1809. 

  1809.             }

    1810. 

  1810.         }

    1811. 

  1811.     }

    1812. 

  1812. }
    1813.