PageRenderTime 60ms CodeModel.GetById 31ms RepoModel.GetById 0ms app.codeStats 0ms

/lms_debug/protected/models/Lead.php

https://gitlab.com/badelal143/lms_debug
PHP | 424 lines | 336 code | 29 blank | 59 comment | 31 complexity | 258782512d1bf1115488248abf547060 MD5 | raw file
Possible License(s): LGPL-2.1, LGPL-3.0, LGPL-2.0
  1. <?php
  2. /**
  3. * This is the model class for table "lead".
  4. *
  5. * The followings are the available columns in table 'lead':
  6. * @property string $leadId
  7. * @property string $email
  8. * @property string $emailOptOut
  9. * @property string $mobile
  10. * @property string $mobileOptOut
  11. */
  12. class Lead extends CActiveRecord
  13. {
  14. public $city;
  15. public $state;
  16. public $leadSourceTitle;
  17. public $leadStage;
  18. public $customerType;
  19. public $productName;
  20. public $policyType;
  21. public $planType;
  22. public $campaignName;
  23. public $leadSourceCode;
  24. public $import;
  25. public $leadSearchBox;
  26. public $leadStatusParentId;
  27. public $createdOn2;
  28. public $totalagent;
  29. public $totalleadstage;
  30. public $set_period1;
  31. public $set_period2;
  32. public $period1;
  33. public $period2;
  34. /**
  35. * Returns the static model of the specified AR class.
  36. * @param string $className active record class name.
  37. * @return Lead the static model class
  38. */
  39. public static function model($className=__CLASS__)
  40. {
  41. return parent::model($className);
  42. }
  43. /**
  44. * @return string the associated database table name
  45. */
  46. public function tableName()
  47. {
  48. return 'lead';
  49. }
  50. /**
  51. * @return array validation rules for model attributes.
  52. */
  53. public function rules()
  54. {
  55. // NOTE: you should only define rules for those attributes that
  56. // will receive user inputs.
  57. return array(
  58. array('mobile','required'),
  59. array('email,name','required','on'=>'salesclosed'),
  60. array('email', 'email','on'=>'salesclosed'), //Email has to be a valid email address
  61. array('mobile', 'length', 'min'=>10,'tooShort'=>'Mobile is too short (minimum is 10 digit)'),
  62. array('mobile, members, si, premium', 'numerical', 'integerOnly'=>true),
  63. array('import','safe'),
  64. array('import', 'file','types'=>'xls','message'=>'Only xls format allowed!','on'=>'insert'),
  65. array('email, createdBy, updatedBy', 'length', 'max'=>255),
  66. array('emailOptOut, mobileOptOut', 'length', 'max'=>1),
  67. array('mobile', 'length', 'max'=>12),
  68. array('createdOn, updatedOn', 'length', 'max'=>20),
  69. // The following rule is used by search().
  70. // Please remove those attributes that should not be searched.
  71. array('leadId, email,isRead,leadDescription, emailOptOut,fileName, mobile, mobileOptOut, status, createdBy, createdOn, updatedBy, updatedOn, children, ageGroup, ws_sourcecode, ws_subject, ws_telephone,agentId, ws_plan, ws_product, ws_quoteid, ws_proposalid, salesClosedUpdatedOn,ws_leadstage, ws_agentid, ws_noofyears, ws_orgnaizationName,utmsource,isBulkImport', 'safe', 'on'=>'search'),
  72. );
  73. }
  74. /**
  75. * @return array relational rules.
  76. */
  77. public function relations()
  78. {
  79. // NOTE: you may need to adjust the relation name and the related
  80. // class name for the relations automatically generated below.
  81. return array(
  82. 'stateDetails' => array(self::BELONGS_TO, 'State', 'stateId'),
  83. 'cityDetails' => array(self::BELONGS_TO, 'City', 'cityId'),
  84. 'customerTypeDetails' => array(self::BELONGS_TO, 'CustomerTypeMaster', 'customerTypeId'),
  85. 'productDetails' => array(self::BELONGS_TO, 'ProductMaster', 'productId'),
  86. 'planTypeDetails' => array(self::BELONGS_TO, 'PlanTypeMaster', 'planTypeId'),
  87. 'policyTypeDetails' => array(self::BELONGS_TO, 'PolicyTypeMaster', 'policyTypeId'),
  88. 'campaignDetails' => array(self::BELONGS_TO, 'Campaign', 'campaignId'),
  89. 'leadSourceDetails' => array(self::BELONGS_TO, 'LeadSourceMaster', 'leadSourceId'),
  90. 'leadStageDetails' => array(self::BELONGS_TO, 'LeadStageMaster', 'leadStageId'),
  91. 'leadStatusDetails' => array(self::BELONGS_TO, 'LeadStatus', 'leadStatusId'),
  92. 'ownerDetails' => array(self::BELONGS_TO, 'User', 'currentAllocatedId'),
  93. 'usermatchedDetails' => array(self::BELONGS_TO, 'User', 'currentAllocatedId','joinType' => ' JOIN',),
  94. 'agentIds' => array(self::BELONGS_TO, 'lead', 'mobile'),
  95. 'MTD' => array(self::HAS_ONE, 'lead', 'leadstatusId'),
  96. );
  97. }
  98. /**
  99. * @return array customized attribute labels (name=>label)
  100. */
  101. public function attributeLabels()
  102. {
  103. return array(
  104. 'leadId' => 'Lead',
  105. 'email' => 'Email',
  106. 'emailOptOut' => 'Email Opt Out',
  107. 'mobile' => 'Mobile',
  108. 'mobileOptOut' => 'Mobile Opt Out',
  109. 'name' => 'Name',
  110. 'address' => 'Address',
  111. 'stateId' => 'State',
  112. 'cityId' => 'City',
  113. 'customerTypeId' => 'Customer Type',
  114. 'productId' => 'Product',
  115. 'planTypeId' => 'Plan Type',
  116. 'policyTypeId' => 'Policy Type',
  117. 'productDetails' => 'Product Details',
  118. 'members' => 'Members',
  119. 'si' => 'SI',
  120. 'premium' => 'Premium',
  121. 'isRead' => 'Is Read',
  122. 'children' => 'Children',
  123. 'ageGroup' => 'Age Group',
  124. 'campaignId' => 'Campaign',
  125. 'leadSourceId' => 'Lead Source',
  126. 'leadStageId' => 'Lead Stage',
  127. 'leadStatusId' => 'Lead Status',
  128. 'status' => 'Status',
  129. 'fileName' => 'FileName',
  130. 'leadDescription' => 'Lead Description',
  131. 'createdBy' => 'Created By',
  132. 'createdOn' => 'Created On',
  133. 'updatedBy' => 'Updated By',
  134. 'updatedOn' => 'Updated On',
  135. 'ws_sourcecode' => 'ws sourcecode ',
  136. 'ws_subject' => 'ws subject',
  137. 'ws_telephone' => 'ws telephone',
  138. 'ws_plan' => 'ws plan',
  139. 'ws_product' => 'ws product',
  140. 'ws_quoteid' => 'ws quoteid',
  141. 'ws_proposalid' => 'ws proposalid',
  142. 'ws_leadstage' => 'ws leadstage',
  143. 'ws_agentid' => 'ws agentid',
  144. 'ws_noofyears' => 'ws noofyears',
  145. 'ws_orgnaizationName' => 'ws orgnaizationName',
  146. 'utmsource' => 'utmsource',
  147. 'isBulkImport' => 'isBulkImport',
  148. 'agentId' => 'agentId',
  149. 'salesClosedUpdatedOn' => 'salesClosedUpdatedOn',
  150. );
  151. }
  152. /**
  153. * Retrieves a list of models based on the current search/filter conditions.
  154. * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
  155. */
  156. public function search1()
  157. {
  158. // Warning: Please modify the following code to remove attributes that
  159. // should not be searched.
  160. $criteria=new CDbCriteria;
  161. $this->leadSearchBox=@$_POST['lead']['leadSearchBox']?@$_POST['lead']['leadSearchBox']:@$_REQUEST['mob'];
  162. if($this->leadSearchBox!=''){
  163. $criteria->with = array('leadStageDetails','leadSourceDetails');
  164. $criteria->together = true;
  165. $condsc ='';
  166. if(@$_SESSION['roleId']=='3') {
  167. $condsc = " AND currentAllocatedId = '".@$_SESSION['id']."'";
  168. } else {
  169. $condsc ='';
  170. /*if(Yii::app()->session->get('type')=='superadmin') {
  171. $condsc = '';
  172. } else {
  173. $relationManager2 = $_SESSION['relationalManager'];
  174. $managerMemberId2 = implode(",",array_unique(@$relationManager2));
  175. $condsc = " AND currentAllocatedId IN ( '".@$managerMemberId2."' ) ";
  176. }*/
  177. }
  178. $criteria->addCondition("(name LIKE CONCAT('%', :leadSearchBox , '%') OR mobile LIKE CONCAT('%', :leadSearchBox , '%') OR email LIKE CONCAT('%', :leadSearchBox , '%') OR leadStageDetails.leadStage LIKE CONCAT('%', :leadSearchBox , '%') OR leadSourceDetails.leadSourceCode LIKE CONCAT('%', :leadSearchBox , '%') OR mobile LIKE CONCAT('%', :leadSearchBox , '%') ) $condsc");
  179. $criteria->params = array(':leadSearchBox' => $this->leadSearchBox);
  180. }
  181. $criteria->with = array('leadStageDetails','leadSourceDetails');
  182. $criteria->compare('leadId',$this->leadId,true);
  183. $criteria->compare('email',$this->email,true);
  184. $criteria->compare('emailOptOut',$this->emailOptOut,true);
  185. $criteria->compare('mobile',$this->mobile,true);
  186. $criteria->compare('mobileOptOut',$this->mobileOptOut,true);
  187. $criteria->compare('fileName',$this->fileName,true);
  188. $criteria->compare('leadDescription',$this->leadDescription,true);
  189. $criteria->compare('status',$this->status,true);
  190. $criteria->compare('children',$this->children,true);
  191. $criteria->compare('ageGroup',$this->ageGroup,true);
  192. $criteria->compare('createdOn',$this->createdOn,true);
  193. $criteria->compare('createdBy',$this->createdBy,true);
  194. $criteria->compare('updatedOn',$this->updatedOn,true);
  195. $criteria->compare('updatedBy',$this->updatedBy,true);
  196. $criteria->compare('ws_sourcecode',$this->ws_sourcecode,true);
  197. $criteria->compare('ws_subject',$this->ws_subject,true);
  198. $criteria->compare('ws_telephone',$this->ws_telephone,true);
  199. $criteria->compare('ws_plan',$this->ws_plan,true);
  200. $criteria->compare('ws_product',$this->ws_product,true);
  201. $criteria->compare('ws_quoteid',$this->ws_quoteid,true);
  202. $criteria->compare('ws_proposalid',$this->ws_proposalid,true);
  203. $criteria->compare('ws_leadstage',$this->ws_leadstage,true);
  204. $criteria->compare('ws_agentid',$this->ws_agentid,true);
  205. $criteria->compare('ws_noofyears',$this->ws_noofyears,true);
  206. $criteria->compare('ws_orgnaizationName',$this->ws_orgnaizationName,true);
  207. $criteria->compare('utmsource',$this->utmsource,true);
  208. $criteria->compare('isBulkImport',$this->isBulkImport,true);
  209. $criteria->compare('agentId',$this->agentId,true);
  210. $criteria->compare('salesClosedUpdatedOn',$this->salesClosedUpdatedOn,true);
  211. return new CActiveDataProvider($this, array(
  212. 'criteria'=>$criteria,
  213. 'sort'=>array(
  214. 'attributes'=>array(
  215. 'leadSourceDetails_search'=>array(
  216. 'asc'=>'leadSourceCode',
  217. 'desc'=>'leadSourceCode DESC',
  218. ),
  219. 'leadStageDetails_search'=>array(
  220. 'asc'=>'leadStage',
  221. 'desc'=>'leadStage DESC',
  222. ),
  223. '*',
  224. ),
  225. ),
  226. 'pagination'=>array(
  227. 'pageSize'=>20,
  228. 'params' => array('mob' =>$this->leadSearchBox),
  229. ),
  230. ));
  231. }
  232. /**
  233. * Retrieves a list of models based on the current search/filter conditions.
  234. * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
  235. */
  236. public function search()
  237. {
  238. $criteria = new CDbCriteria;
  239. $this->leadSearchBox = @$_POST['lead']['leadSearchBox'] ? @$_POST['lead']['leadSearchBox'] : @$_REQUEST['mob'];
  240. $criteria->limit = 20;
  241. if ($this->leadSearchBox != '')
  242. {
  243. $condsc = '';
  244. if (@$_SESSION['roleId'] == '3')
  245. {
  246. $condsc = " AND currentAllocatedId = '" . @$_SESSION['id'] . "'";
  247. }
  248. $criteria->together = true;
  249. $defaultCriteriaCond = trim(LeadsController::searchLeadList($this->leadSearchBox), 'AND ');
  250. $criteria->addCondition($defaultCriteriaCond . $condsc);
  251. $criteria->params = array(':leadSearchBox' => $this->leadSearchBox);
  252. }
  253. //$criteria->compare('leadSourceCode', $this->leadSourceCode, false);
  254. //$criteria->compare('leadStage', $this->leadStage, false);
  255. return new CActiveDataProvider($this, array(
  256. 'criteria' => $criteria,
  257. 'sort' => array(
  258. 'attributes' => array(
  259. 'leadSourceDetails_search' => array(
  260. 'asc' => 'leadSourceCode',
  261. 'desc' => 'leadSourceCode DESC',
  262. ),
  263. 'leadStageDetails_search' => array(
  264. 'asc' => 'leadStage',
  265. 'desc' => 'leadStage DESC',
  266. ),
  267. '*',
  268. ),
  269. ),
  270. 'pagination' => array(
  271. 'pageSize' => 20,
  272. 'params' => array('mob' => $this->leadSearchBox),
  273. ),
  274. ));
  275. }
  276. public function newLeads($userIds){
  277. $currentDate=date('d-m-Y',time());
  278. $starttimestamp=strtotime($currentDate);
  279. $cond='';
  280. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  281. $searchDate=@$_POST['searchDashboard'];
  282. $searchTimestamp=strtotime(@$searchDate);
  283. $searchEndTimestamp=$searchTimestamp+86400;
  284. $cond = " and t.updatedOn >= '".@$searchTimestamp."' and t.updatedOn < '".@$searchEndTimestamp."'";
  285. }
  286. //die;
  287. return Lead::model()->count(array("condition"=>" leadStatusId = '35' AND currentAllocatedId >0 AND isRead = 'no' and currentAllocatedId IN ($userIds) ".$cond." "));
  288. //return Yii::app()->db->createCommand(" ")->queryAll();
  289. }
  290. public function overallLeads($userIds){
  291. $cond='';
  292. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  293. $searchDate=@$_POST['searchDashboard'];
  294. $searchTimestamp=strtotime(@$searchDate);
  295. $searchEndTimestamp=$searchTimestamp+86400;
  296. $cond = " and t.updatedOn >= '".@$searchTimestamp."' and t.updatedOn < '".@$searchEndTimestamp."'";
  297. }
  298. return Lead::model()->count(array("condition"=>" currentAllocatedId IN ($userIds) ".$cond." "));
  299. //return Yii::app()->db->createCommand("select count(*) as total from lead where currentAllocatedId IN ($userIds) ".$cond." ")->queryAll();
  300. }
  301. public function allocatedLead12Pm($userIds){
  302. $cond='';
  303. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  304. $searchDate=@$_POST['searchDashboard'];
  305. $searchTimestamp=strtotime(@$searchDate);
  306. $searchEndTimestamp=$searchTimestamp+86400;
  307. $cond = " and t.updatedOn >= '".@$searchTimestamp."' and t.updatedOn < '".@$searchEndTimestamp."'";
  308. }
  309. $currentDate=date('d-m-Y',time());
  310. $starttimestamp=strtotime($currentDate);
  311. $midtimestamp=strtotime($currentDate)+43200;
  312. return Lead::model()->count(array("condition"=>" currentAllocatedId>0 AND isRead = 'yes' and currentAllocatedId IN ($userIds) ".$cond.""));
  313. //return Yii::app()->db->createCommand("select count(*) as total from lead where currentAllocatedId>0 AND isRead = 'yes' and currentAllocatedId IN ($userIds) ".$cond." ")->queryAll();
  314. }
  315. public function closeLeads($userIds){
  316. $cond='';
  317. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  318. $searchDate=@$_POST['searchDashboard'];
  319. $searchTimestamp=strtotime(@$searchDate);
  320. $searchEndTimestamp=$searchTimestamp+86400;
  321. $cond = " and t.updatedOn >= '".@$searchTimestamp."' and t.updatedOn < '".@$searchEndTimestamp."'";
  322. }
  323. return @$userIds?Lead::model()->count(array("condition"=>" (leadStatusId='7' || leadStatusId='1') and currentAllocatedId IN (".@$userIds.") ".$cond."")):0;
  324. //return Yii::app()->db->createCommand("select count(*) as total from lead where (leadStatusId='7' || leadStatusId='1') and currentAllocatedId IN ($userIds) ".$cond." ")->queryAll();
  325. }
  326. public function followUpLeads($userIds){
  327. $cond='';
  328. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  329. $searchDate=@$_POST['searchDashboard'];
  330. $searchTimestamp=strtotime(@$searchDate);
  331. $searchEndTimestamp=$searchTimestamp+86400;
  332. $cond = " and t.updatedOn >= '".@$searchTimestamp."' and t.updatedOn < '".@$searchEndTimestamp."'";
  333. }
  334. $currentDate=date('d-m-Y',time());
  335. $starttimestamp=strtotime($currentDate);
  336. return leadFollowup::model()->count(array("condition"=>" startDate='$starttimestamp' and status='Pending' and userId IN ($userIds) ".$cond.""));
  337. //return Yii::app()->db->createCommand("select count(*) as total from leadFollowup where startDate='$starttimestamp' and status='Pending' and userId IN ($userIds) ".$cond." ")->queryAll();
  338. }
  339. public function closedLeadsAmount($userIds){
  340. $cond='';
  341. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  342. $searchDate=@$_POST['searchDashboard'];
  343. $searchTimestamp=strtotime(@$searchDate);
  344. $searchEndTimestamp=$searchTimestamp+86400;
  345. $cond = " and lead.updatedOn >= '".@$searchTimestamp."' and lead.updatedOn < '".@$searchEndTimestamp."'";
  346. }
  347. return @$userIds?Yii::app()->db->createCommand("select SUM(premium) as totalAmount from lead where (leadStatusId='7' || leadStatusId='1') and currentAllocatedId IN ($userIds) ".$cond." ")->queryAll():0;
  348. }
  349. public function fetchteammemberByTeam($teamId){
  350. return Yii::app()->db->createCommand("select GROUP_CONCAT(distinct userId order by userId,',',true) as teamMemberIds from teamUserMapping where teamId = ".$teamId." ")->queryAll();
  351. }
  352. public function getMTD($leadStatusId){
  353. $cond='';
  354. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  355. $searchDate=@$_POST['searchDashboard'];
  356. $searchTimestamp=strtotime(@$searchDate);
  357. $searchEndTimestamp=$searchTimestamp+86400;
  358. $cond = " and t.updatedOn >= '".@$searchTimestamp."' and t.updatedOn <= '".@$searchEndTimestamp."'";
  359. } else {
  360. $CurrentMonth_first_minute = mktime(0, 0, 0, date("n"), 1);
  361. $CurrentMonth_last_minute = mktime(23, 59, 0, date("n"), date("t"));
  362. $cond = " and t.updatedOn >= '".@$CurrentMonth_first_minute."' and t.updatedOn <= '".@$CurrentMonth_last_minute."'";
  363. }
  364. $currentDate=date('d-m-Y',time());
  365. $starttimestamp=strtotime($currentDate);
  366. if(@$_SESSION['isAllocator']=='yes' or Yii::app()->session->get('type')=='superadmin') {
  367. return lead::model()->count(array("condition"=>" leadstatusId=".@$leadStatusId." ".$cond." "));
  368. } else {
  369. $ids= DashboardController::implodeResult($_SESSION['relationalManager']);
  370. return lead::model()->count(array("condition"=>" leadstatusId=".@$leadStatusId." and currentAllocatedId IN (".@$ids.") ".$cond.""));
  371. }
  372. }
  373. public function getYTD($leadStatusId){
  374. $cond='';
  375. if(isset($_POST['searchDashboard']) && !empty($_POST['searchDashboard'])) {
  376. $searchDate=@$_POST['searchDashboard'];
  377. $searchTimestamp=strtotime(@$searchDate);
  378. $searchEndTimestamp=$searchTimestamp+86400;
  379. $cond = " and t.updatedOn >= '".@$searchTimestamp."' and t.updatedOn < '".@$searchEndTimestamp."'";
  380. } else {
  381. $CurrentYear_first_minute = mktime(0, 0, 0, 1, 1, date('Y'));
  382. $CurrentYear_last_minute = mktime(0, 0, 0, 13, 0, date('Y'));
  383. $cond = " and t.updatedOn >= '".@$CurrentYear_first_minute."' and t.updatedOn <= '".@$CurrentYear_last_minute."'";
  384. }
  385. $currentDate=date('d-m-Y',time());
  386. $starttimestamp=strtotime($currentDate);
  387. if(@$_SESSION['isAllocator']=='yes' or Yii::app()->session->get('type')=='superadmin') {
  388. return lead::model()->count(array("condition"=>" leadstatusId=".@$leadStatusId." ".$cond." "));
  389. } else {
  390. $ids= DashboardController::implodeResult($_SESSION['relationalManager']);
  391. return lead::model()->count(array("condition"=>" leadstatusId=".@$leadStatusId." and currentAllocatedId IN (".@$ids.") ".$cond.""));
  392. }
  393. }
  394. }