/lms_debug/protected/controllers/ReportsController_23rdApr15.php
PHP | 1810 lines | 1456 code | 250 blank | 104 comment | 242 complexity | b0d5c579e08d11125233839a3a2ab865 MD5 | raw file
Possible License(s): LGPL-2.1, LGPL-3.0, LGPL-2.0
Large files files are truncated, but you can click here to view the full file
- <?php
- class ReportsController extends Controller
- {
- const SHARED_TITLE = "Admin Panel";
- /**
- * Declares class-based actions.
- */
- public function filters()
- {
- return array( 'accessControl' ); // perform access control for CRUD operations
- }
-
- public function actions()
- {
- return array(
- // captcha action renders the CAPTCHA image displayed on the contact page
- 'captcha'=>array(
- 'class'=>'CCaptchaAction',
- 'backColor'=>0xFFFFFF,
- ),
- // page action renders "static" pages stored under 'protected/views/site/pages'
- // They can be accessed via: index.php?r=site/page&view=FileName
- 'page'=>array(
- 'class'=>'CViewAction',
- ),
- );
- }
-
- /**
- * It calls the actionCreateExcel to generates excel reports'
- * when an action is not explicitly requested by users.
- */
- public function actionExportExcelReport()
- {
- // It calls the actionCreateExcel to generates excel reports'
- @$getName=$_POST['LeadCronReport']['name'];
- @$getStatus=$_POST['LeadCronReport']['status'];
- @$getStage=$_POST['LeadCronReport']['stage'];
- @$date1=$_POST['LeadCronReport']['period1'];
- @$date2=$_POST['LeadCronReport']['period2'];
- @$getleadSourceId=$_POST['LeadCronReport']['leadSourceId'];
- @$getleadStatusId=$_POST['leadStatusId'];
- @$datetype=@$_POST['LeadCronReport']['datetype'];
- if(isset($datetype) && !empty($datetype)){
- $datetype=$datetype;
- } else {
- $datetype='updatedOn';
- }
- $isjunk = @$_REQUEST['isJunk'];
- $isjunk = $isjunk?$isjunk:'no';
- $this->actionCreateExcel($getName,$getStatus,$getStage,$date1,$date2,$getleadSourceId,$getleadStatusId,$datetype,$isjunk);
-
-
- }
- public function actionreports() {
-
- // renders the view file 'protected/views/site/index.php'
- // using the default layout 'protected/views/layouts/main.php'
- $model=new LeadCronReport('search');
- $model->scenario = 'LeadCronReport';
- $teamId = '';
- $teamUserId = '';
- if(@$_POST['LeadCronReport'] !='' && isset($_POST['LeadCronReport'])){
- @$primaryLeadStatus = @$_POST['leadStatusId'];
- if(count($primaryLeadStatus) > 0){
- $primaryLeadStatus = implode(",",$primaryLeadStatus);
- $primaryDisposition=Yii::app()->db->createCommand("select leadStatus.leadStatus,leadStatusId from leadStatus where parentId IN (".@$primaryLeadStatus.") ")->queryAll();
-
-
- if(count($primaryDisposition) > 0){
- for($w=0;$w<count($primaryDisposition);$w++){
- @$PrimaryDispositionIds[] = @$primaryDisposition[$w]['leadStatusId'];
- }
- } else {
- @$PrimaryDispositionIds[] = '';
- }
- @$PrimaryDispositionIds = implode(",",@$PrimaryDispositionIds);
- } else {
- @$PrimaryDispositionIds = '';
- }
-
- $teamId = @$_POST['Team']['teamId'];
- $teamUserId = @$_POST['Team']['teamMemberId'];
-
- if($teamUserId=='' or empty($teamUserId)){
- $teamUsers=Yii::app()->db->createCommand("select userId from teamUserMapping where teamId = '".@$teamId."' ")->queryAll();
- $reportsteamUsersIds = array();
- foreach($teamUsers as $key=>$value){
- $reportsteamUsersIds[] = $value['userId'];
- }
- } else {
- $reportsteamUsersIds[] = $teamUserId;
- }
-
-
- $name=@$_POST['LeadCronReport']['name'];
- $status=@$_POST['LeadCronReport']['status'];
- $stage=@$_POST['LeadCronReport']['stage'];
- /*$utmCode=@$_POST['LeadCronReport']['utmCode'];
- $teleCaller=@$_POST['LeadCronReport']['teleCaller'];*/
- $period1=@$_POST['LeadCronReport']['period1'];
- $period2=@$_POST['LeadCronReport']['period2'];
- $leadSourceId=@$_POST['LeadCronReport']['leadSourceId'];
- $ws_agentid=@$_POST['LeadCronReport']['ws_agentid'];
- $datetype=@$_POST['LeadCronReport']['datetype'];
- $isjunk = @$_REQUEST['isJunk'];
- $isjunk = $isjunk?$isjunk:'no';
- if(isset($datetype) && !empty($datetype)){
- $datetype=$datetype;
- } else {
- $datetype='updatedOn';
- }
- //echo $datetype; die;
-
- $criteria = new CDbCriteria;
- $criteria->select = 't.*,`city`.`city`,`state`.`state`, `leadSourceMaster`.`leadSourceTitle`, `leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`,`customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName`';
- $criteria->join ='LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId)LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId)LEFT JOIN campaign ON (campaign.campaignId = t.campaignId)';
- $criteria->order='leadId DESC';
- if(count($reportsteamUsersIds) > 0){
- $comd = implode(',',$reportsteamUsersIds);
- $criteria->addCondition("t.currentAllocatedId IN (".$comd.") ");
- }
- if(isset($name) && !empty($name))
- {/*LIKE '$this->time_up%'";*/
- $criteria->addCondition("t.name like '%".$name."'");
- }
- if(isset($status) && !empty($status))
- {
- $criteria->addCondition("t.leadStatusId =".$status);
- }
- if(isset($PrimaryDispositionIds) && !empty($PrimaryDispositionIds))
- {
- $criteria->addCondition("t.leadStatusId IN (".@$PrimaryDispositionIds.") ");
- }
- if(isset($stage) && !empty($stage))
- {
- $criteria->addCondition("t.leadStageId =".$stage);
- }
- if(isset($leadSourceId) && !empty($leadSourceId))
- {
- $criteria->addCondition("t.leadSourceId =".$leadSourceId);
- }
- if(isset($isjunk) && !empty($isjunk) && $isjunk=='yes' )
- {
- $criteria->addCondition("t.leadStatusId !=36");
- }
- if(isset($ws_agentid) && !empty($ws_agentid))
- {
- $criteria->addCondition("t.ws_agentid='".$ws_agentid."'");
- }
- if((isset($period1) && !empty($period1)) && (isset($period2) && !empty($period2)))
- {
- $period1=strtotime($period1);
- $period2=strtotime($period2)+86399;
- // echo date('d m y :: h: i: s: A',$period2); die;
- $criteria->addCondition('t.'.$datetype.' >= '.$period1.' AND t.'.$datetype.' <= '.$period2.'');
- // $criteria->addBetweenCondition('t.'.$datetype,$period1,$period2,'');
-
- }
- /*echo "<pre>";
- print_r($criteria);
- die();*/
- $model->set_name=@$_POST['LeadCronReport']['name'];
- $model->set_stage=@$_POST['LeadCronReport']['stage'];
- $model->set_status=@$_POST['LeadCronReport']['status'];
- $model->set_period1=@$_POST['LeadCronReport']['period1'];
- $model->set_period2=@$_POST['LeadCronReport']['period2'];
- $model->set_leadStatusId=@$_POST['leadStatusId'];
- $model->set_leadSourceId=@$_POST['LeadCronReport']['leadSourceId'];
- $model->set_ws_agentid=@$_POST['LeadCronReport']['ws_agentid'];
- $criteria->order='leadId DESC';
- $dataProvider=new CActiveDataProvider('LeadCronReport', array('criteria'=>$criteria,'pagination'=>array(
- 'pageSize'=>20,
- 'params' => array('period1' =>@$period1?date("d-m-Y",@$period1):'','period2' =>@$period2?date("d-m-Y",@$period2):''),
- ),));
-
- $this->render('reports',array('model'=>$model,'dataProvider'=>$dataProvider,'teamId'=>$teamId,'teamUserId'=>$teamUserId));
- }else{
-
- $criteria = new CDbCriteria;
- $criteria->select = 't.*';
- $criteria->order='leadId DESC';
-
- $dataProvider=new CActiveDataProvider('LeadCronReport', array('criteria'=>$criteria,));
- $this->render('reports',array('model'=>$model,'dataProvider'=>$dataProvider,'teamId'=>$teamId,'teamUserId'=>$teamUserId));
- }
- }
- /**
- * This function is used to get column name from number of column
- * calls from 'actionCreateExcel' function.
- */
- public function getNameFromNumber($num) {
- $numeric = $num % 26;
- $letter = chr(65 + $numeric);
- $num2 = intval($num / 26);
- if ($num2 > 0) {
- return getNameFromNumber($num2 - 1) . $letter;
- } else {
- return $letter;
- }
- }
- public function getPageType()
- {
- return "Reports";
- }
-
- /**
- * This function is used to create excel file
- * calls from 'actionreports' function.
- */
- public function actionCreateExcel($getName,$getStatus,$getStage,$date1,$date2,$getleadSourceId,$getleadStatusId,$datetype,$isjunk){
- $paginationExcelValue = @$_POST['fetchrecordsBydropdown'];
-
- if(isset($paginationExcelValue) && !empty($paginationExcelValue)){
- $explodedpaginationValue = explode('-',$paginationExcelValue);
- $startLimit = $explodedpaginationValue[0];
- $endLimit = 10000;
- } else {
- $startLimit = '';
- $endLimit = '';
-
- }
- if(isset($startLimit) && isset($endLimit) && !empty($endLimit)){
- $limitCond = " limit $startLimit , $endLimit";
- } else {
- $limitCond = '';
- }
- //Here is the code to set conditions for fetch records from database
- $condfilter = '';
-
- if((isset($getName)&& (!empty($getName))) || (isset($getStatus)&& (!empty($getStatus))) || (isset($getStage)&& (!empty($getStage)))|| (isset($date1)&& (!empty($date1))) || (isset($date2)&& (!empty($date2))))
- {
-
- if(isset($getName) && !empty($getName))
- {
- $condfilter .= "t.name like '%".$getName."' AND ";
- }
- if(isset($getStatus) && !empty($getStatus))
- {
- $condfilter .= "t.leadStatusId =".$getStatus." AND ";
- }
- if(isset($getStage) && !empty($getStage))
- {
- $condfilter .= "t.leadStageId =".$getStage." AND ";
- }
- if(isset($getleadSourceId) && !empty($getleadSourceId))
- {
- $condfilter .= "t.leadSourceId =".$getleadSourceId." AND ";
- }
- if(isset($isjunk) && !empty($isjunk))
- {
- $condfilter .= "t.leadStatusId != 36 AND ";
- }
- if((isset($date1) && !empty($date1)) && (isset($date2) && !empty($date2)))
- {
- $date1=strtotime($date1);
- $date2=strtotime($date2)+86399;
- $condfilter .= "t.".$datetype." >=".$date1." AND t.".$datetype." <= ".$date2." AND ";
- }
- if(isset($condfilter) && !empty($condfilter)){
- $wherecon = "WHERE ";
- } else {
- $wherecon = "";
- }
- $data=Yii::app()->db->createCommand("select t.leadId,t.name,t.mobile,t.email,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.updatedOn,t.jsonAllData from leads_cron as t $wherecon $condfilter t.leadId > 0 order by t.leadId DESC $limitCond")->queryAll();
-
- }else{
-
- if(isset($getleadSourceId) && !empty($getleadSourceId))
- {
- $condfilter .= "t.leadSourceId =".$getleadSourceId." AND";
- }
- if(count($getleadStatusId) > 0){
- $getleadStatusId = implode(",",$getleadStatusId);
- $primaryDisposition=Yii::app()->db->createCommand("select leadStatus.leadStatus,leadStatusId from leadStatus where parentId IN (".@$getleadStatusId.") ")->queryAll();
-
-
- if(count($primaryDisposition) > 0){
- for($w=0;$w<count($primaryDisposition);$w++){
- @$PrimaryDispositionIds[] = @$primaryDisposition[$w]['leadStatusId'];
- }
- } else {
- @$PrimaryDispositionIds[] = '';
- }
- @$PrimaryDispositionIds = implode(",",@$PrimaryDispositionIds);
- } else {
- @$PrimaryDispositionIds = '';
- }
- if(isset($PrimaryDispositionIds) && !empty($PrimaryDispositionIds))
- {
- $condfilter .= "t.leadStatusId IN (".@$PrimaryDispositionIds.") AND ";
-
- }
-
- if(isset($condfilter) && !empty($condfilter)){
- $wherecon = "WHERE ";
- } else {
- $wherecon = "";
- }
- $data=Yii::app()->db->createCommand("select t.leadId,t.name,t.mobile,t.email,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.updatedOn,t.jsonAllData from leads_cron as t $wherecon $condfilter t.leadId > 0 order by t.leadId DESC $limitCond ")->queryAll();
-
- }
-
-
- if(count($data)>0){
- $file_name = "lms" . "_" . date("Y-m-d_H-i", time()) . ".csv";
- header('Content-Type: text/html; charset=utf-8');
- header("Content-type: application/octet-stream");
- header("Content-Disposition: attachment; filename=\"$file_name\"");
- header("Cache-Control: cache, must-revalidate");
- header("Pragma: public");
- $col = array(
- 'SNO.','Name','Mobile Number',' Email Id','Address','City','State','Members','Premium','Product Details','Lead Status','Status','isJunk','Lead Source','Lead Stage','Lead Description','Activity Description','Customer Type','Product Name','Policy Type','Plan Type','Campaign Name','UTM Code','Owner','Last Modified Date','Agent-Id');
-
- foreach ($col as $key => $value) {
- echo $value . ",";
- }
- echo "\n";
- $i=1;
-
- foreach ($data as $key => $value) {
-
- $jsonArray = json_decode($value['jsonAllData']);
- $state = $jsonArray->state;
- $city = $jsonArray->city;
- $product = $jsonArray->product;
- $leadStatus = $jsonArray->leadStatus;
- $leadSource = $jsonArray->leadSource;
- $leadStage = $jsonArray->leadStage;
- $ActivityDescription = $jsonArray->ActivityDescription;
- $owner = $jsonArray->owner;
- $customerType = $jsonArray->customerType;
- $policyType = $jsonArray->policyType;
- $planType = $jsonArray->planType;
- $campaign = $jsonArray->campaign;
-
-
- // $sqlDesc = "select group_concat(description) as ActivityDescription from leadFollowup where leadId=".@$value["leadId"]." ";
- // $dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();
-
- // $leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",","/",@$dataDesc[0]['ActivityDescription']):'';
- if($value['currentAllocatedId']==$value['createdBy']){
- $owner = $owner."(C)";
- } else {
- $owner = $owner;
- }
- $lastModifieddate = date("d-M-Y H-i-s A",$value['updatedOn']);
-
-
- echo $i++ . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["name"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["mobile"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["email"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["address"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$city)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$state)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["members"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["premium"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value['productDetails'])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$leadStatus)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["status"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["isJunk"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$leadSource)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$leadStage)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["leadDescription"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$ActivityDescription)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$customerType)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$product)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$policyType)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$planType)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$campaign)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["leadSourceCode"])) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$owner)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$lastModifieddate)) . ",";
- echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["ws_agentid"])) . ",";
- echo "\n";
- }
-
- }
- else{
- $this->redirect('index.php?r=reports/reports');
- //$dataProvider=new CActiveDataProvider('LeadCronReport', array('criteria'=>@$criteria,));
- //$this->render('reports',array('model'=>$model,'dataProvider'=>$dataProvider));
- }
- }
-
-
- public function getLeadDescription($leadId){
- $records=Yii::app()->db->createCommand("select leadDescription from lead where leadId='".@$leadId."' ")->queryAll();
-
- if(count($records) > 0) {
- for($i=0;$i<count($records);$i++){
- $record[] = @$records[$i]['leadDescription'];
- }
- } else {
- echo "";
- }
-
- if( count(@$record) > 1){
- echo "<label title='".implode(',',$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";
-
- } else if( count(@$record) == 1) {
- echo "<label title='".implode(',',@$record)."'><a href='#'>".implode(',',$record)."</a></label>";
- } else {
- echo "";
- }
- //echo explode(',',$agentId);
- }
- public function getLeadActivityDescription($leadId){
- $records=Yii::app()->db->createCommand("select description from leadFollowup where leadId='".@$leadId."' ")->queryAll();
- if(count($records) > 0) {
- for($i=0;$i<count($records);$i++){
- $record[] = @$records[$i]['description'];
- }
- } else {
- echo "";
- }
-
- if( count(@$record) > 1){
- echo "<label title='".implode(',',$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";
-
- } else if( count(@$record) == 1) {
- echo "<label title='".implode(',',@$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";
- } else {
- echo "";
- }
-
- //echo explode(',',$agentId);
- }
-
- public function getLeadActivityDescriptionExcel($leadId){
- $records=Yii::app()->db->createCommand("select description from leadFollowup where leadId='".@$leadId."' ")->queryAll();
- if(count($records) > 0) {
- for($i=0;$i<count($records);$i++){
- $record[] = @$records[$i]['description'];
- }
- } else {
- echo "";
- }
-
- if( count(@$record) > 1){
- echo implode(',',$record);
-
- } else if( count(@$record) == 1) {
- echo implode(',',$record);
- } else {
- echo "";
- }
-
- //echo explode(',',$agentId);
- }
-
- public function actionCheckanddelete(){
- $reportmodel=Lead::model()->findAll();
- print_r($reportmodel);
- die;
- }
- function actioncroneJobForReport(){
-
- $sourcewiseDetails=Yii::app()->db->createCommand("select * from leads_cron_history where id='lead_updated_on' ")->queryAll();
- $sourcewiseDetails[0]['updatedOn'];
-
-
- $queryUpdatedOn = "select t.*,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName` from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where (t.updatedOn > ".@$sourcewiseDetails[0]['updatedOn']." || t.createdOn > ".@$sourcewiseDetails[0]['updatedOn']." ) order by t.leadId DESC ";
-
- $dataUpdatedOn=Yii::app()->db->createCommand($queryUpdatedOn)->queryAll();
-
- $updateleads_cron_history = " update leads_cron_history set updated_ts = '".date('Y-m-d H:i:s')."',updatedOn = '".time()."' where id='lead_updated_on' ";
- $executeQueryder=Yii::app()->db->createCommand($updateleads_cron_history)->execute();
-
-
- //$queryCreatedOn = "select t.leadId,t.name,t.mobile,t.email,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.updatedOn,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName` from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where t.leadId <= 2 and createdOn > ".@$sourcewiseDetails[0]['updatedOn']." order by t.leadId DESC ";
-
- //$dataCreatedOn=Yii::app()->db->createCommand($queryCreatedOn)->queryAll();
-
-
- $insertcount=0;
- $updatecount=0;
-
- if(count($dataUpdatedOn) > 0){
-
- foreach ($dataUpdatedOn as $key => $value) {
-
- $owner = @$value['firstName']?(@$value['firstName']." ".@$value['lastName']):"NA";
- $sqlDesc = "select group_concat(description) as ActivityDescription from renewalleadFollowup where leadId=".@$value["leadId"]." ";
- $dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();
-
- $leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",",":",@$dataDesc[0]['ActivityDescription']):'';
-
-
-
-
-
- $state = @$value["state"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["state"])):'NA';
- $city = @$value["city"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["city"])):'NA';
- $customerType = @$value["customerType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["customerType"])):'NA';
- $productName = @$value["productName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["productName"])):'NA';
- $policyType = @$value["policyType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["policyType"])):'NA';
- $planType = @$value["planType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["planType"])):'NA';
- $campaignName = @$value["campaignName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["campaignName"])):'NA';
- $leadSourceTitle = @$value["leadSourceTitle"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadSourceTitle"])):'NA';
- $leadStatus = @$value["leadStatus"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStatus"])):'NA';
- $leadStage = @$value["leadStage"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStage"])):'NA';
- $owner = @$owner?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$owner)):'NA';
- $agntName = @$value["agntName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["agntName"])):'NA';
- $leadActivity = @$leadActivity?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$leadActivity)):'NA';
-
-
-
- $newarray = array(
- 'state' => $state,
- 'city' => $city,
- 'customerType' => $customerType,
- 'product' => $productName,
- 'policyType' => $policyType,
- 'planType' => $planType,
- 'campaign' => $campaignName,
- 'leadSource' => $leadSourceTitle,
- 'leadStatus' => $leadStatus,
- 'leadStage' => $leadStage,
- 'owner' => addslashes($owner),
- 'agntName' => $agntName,
- 'ActivityDescription' => str_replace("'","`",$leadActivity)
- );
- $jsonData = json_encode($newarray);
-
- //$insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values (".$value['leadId'].",".$value['name'].",".$value['email'].",".$value['emailOptOut'].",".$value['mobile'].",".$value['mobileOptOut'].",".$value['address'].",".$value['stateId'].",".$value['cityId'].",".$value['customerTypeId'].",".$value['productId'].",".$value['policyTypeId'].",".$value['planTypeId'].",".$value['campaignId'].",".$value['members'].",".$value['si'].",".$value['premium'].",".$value['productDetails'].",".$value['ageGroup'].",".$value['children'].",".$value['leadSourceId'].",".$value['leadStatusId'].",".$value['leadStageId'].",".$value['isJunk'].",".$value['currentAllocatedId'].",".$value['currentAllocatedDate'].",".$value['isDuplicate'].",".$value['duplicateLeadId'].",".$value['age'].",".$value['dateonSubmit'].",".$value['fileName'].",".$value['leadDescription'].",".$value['isRead'].",".$value['status'].",".$value['ipaddress'].",".$value['agentId'].",".$value['createdOn'].",".$value['createdBy'].",".$value['updatedOn'].",".$value['updatedBy'].",".$value['ws_sourcecode'].",".$value['ws_subject'].",".$value['ws_telephone'].",".$value['ws_plan'].",".$value['ws_product'].",".$value['ws_quoteid'].",".$value['ws_proposalid'].",".$value['ws_leadstage'].",".$value['ws_agentid'].",".$value['ws_noofyears'].",".$value['ws_orgnaizationName'].",".$value['utmsource'].",".$value['isBulkImport'].",".$value['salesClosedUpdatedOn'].",'".$jsonData."',".$value['jsonDataUpdatedOn'].",".$value['jsonDataUpdatedBy'].") ";
-
- if($value['createdOn'] >= $sourcewiseDetails[0]['updatedOn']){
- $insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values ('".$value['leadId']."','".addslashes($value['name'])."','".$value['email']."','".$value['emailOptOut']."','".$value['mobile']."','".$value['mobileOptOut']."','".addslashes($value['address'])."','".$value['stateId']."','".$value['cityId']."','".$value['customerTypeId']."','".$value['productId']."','".$value['policyTypeId']."','".$value['planTypeId']."','".$value['campaignId']."','".addslashes($value['members'])."','".addslashes($value['si'])."','".addslashes($value['premium'])."','".addslashes($value['productDetails'])."','".$value['ageGroup']."','".$value['children']."','".$value['leadSourceId']."','".$value['leadStatusId']."','".$value['leadStageId']."','".$value['isJunk']."','".$value['currentAllocatedId']."','".$value['currentAllocatedDate']."','".$value['isDuplicate']."','".$value['duplicateLeadId']."','".$value['age']."','".$value['dateonSubmit']."','".$value['fileName']."','".addslashes($value['leadDescription'])."','".$value['isRead']."','".$value['status']."','".$value['ipaddress']."','".$value['agentId']."','".$value['createdOn']."','".$value['createdBy']."','".$value['updatedOn']."','".$value['updatedBy']."','".$value['ws_sourcecode']."','".$value['ws_subject']."','".$value['ws_telephone']."','".$value['ws_plan']."','".$value['ws_product']."','".$value['ws_quoteid']."','".$value['ws_proposalid']."','".$value['ws_leadstage']."','".$value['ws_agentid']."','".$value['ws_noofyears']."','".$value['ws_orgnaizationName']."','".$value['utmsource']."','".$value['isBulkImport']."','".$value['salesClosedUpdatedOn']."','".$jsonData."','".time()."','amit') ";
- $executeQuery4=Yii::app()->db->createCommand($insertQuery)->execute();
- //echo "<br><br>" .$insertQuery;
- $insertcount++;
-
-
- } else if($value['updatedOn'] >= $sourcewiseDetails[0]['updatedOn']){
- $updateQuery = " update leads_cron set leadId='".$value['leadId']."',name='".addslashes($value['name'])."',email='".$value['email']."',emailOptOut='".$value['emailOptOut']."',mobile='".$value['mobile']."',mobileOptOut='".$value['mobileOptOut']."',address='".addslashes($value['address'])."',stateId='".$value['stateId']."',cityId='".$value['cityId']."',customerTypeId='".$value['customerTypeId']."',productId='".$value['productId']."',policyTypeId='".$value['policyTypeId']."',planTypeId='".$value['planTypeId']."',campaignId='".$value['campaignId']."',members='".addslashes($value['members'])."',si='".addslashes($value['si'])."',premium='".addslashes($value['premium'])."',productDetails='".addslashes($value['productDetails'])."',ageGroup='".$value['ageGroup']."',children='".$value['children']."',leadSourceId='".$value['leadSourceId']."',leadStatusId='".$value['leadStatusId']."',leadStageId='".$value['leadStageId']."',isJunk='".$value['isJunk']."',currentAllocatedId='".$value['currentAllocatedId']."',currentAllocatedDate='".$value['currentAllocatedDate']."',isDuplicate='".$value['isDuplicate']."',duplicateLeadId='".$value['duplicateLeadId']."',age='".$value['age']."',dateonSubmit='".$value['dateonSubmit']."',fileName='".$value['fileName']."',leadDescription='".addslashes($value['leadDescription'])."',isRead='".$value['isRead']."',status='".$value['status']."',ipaddress='".$value['ipaddress']."',agentId='".$value['agentId']."',createdOn='".$value['createdOn']."',createdBy='".$value['createdBy']."',updatedOn='".$value['updatedOn']."',updatedBy='".$value['updatedBy']."',ws_sourcecode='".$value['ws_sourcecode']."',ws_subject='".$value['ws_subject']."',ws_telephone='".$value['ws_telephone']."',ws_plan='".$value['ws_plan']."',ws_product='".$value['ws_product']."',ws_quoteid='".$value['ws_quoteid']."',ws_proposalid='".$value['ws_proposalid']."',ws_leadstage='".$value['ws_leadstage']."',ws_agentid='".$value['ws_agentid']."',ws_noofyears='".$value['ws_noofyears']."',ws_orgnaizationName='".$value['ws_orgnaizationName']."',utmsource='".$value['utmsource']."',isBulkImport='".$value['isBulkImport']."',salesClosedUpdatedOn='".$value['salesClosedUpdatedOn']."',jsonAllData='".$jsonData."',jsonDataUpdatedOn='".time()."',jsonDataUpdatedBy='amit' where leadId = ".@$value['leadId']." ";
- $executeQuery2=Yii::app()->db->createCommand($updateQuery)->execute();
- //echo "<br><br>" .$updateQuery;
- $updatecount++;
-
- }
-
- }
-
- }
- echo "insert count=".$insertcount." and update count=".$updatecount;
-
- }
- function actioncroneFirstTime(){
- $startId = @$_REQUEST['startId'];
- $endId = @$_REQUEST['endId'];
- if(empty($startId) || empty($endId)){
- echo "parameter missing";
- die;
- }
- if($startId > $endId){
- echo "start Limit can not be greater or equal to end Limit";
- die;
- }
- $query = "select t.*,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName` from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where t.leadId >= $startId and t.leadId <= $endId ";
- $data=Yii::app()->db->createCommand($query)->queryAll();
-
- $updateleads_cron_history = " update leads_cron_history set updated_ts = '".date('Y-m-d H:i:s')."',updatedOn = '".time()."' where id='lead_updated_on' ";
- $executeQuery=Yii::app()->db->createCommand($updateleads_cron_history)->execute();
- //$r=0;
- foreach ($data as $key => $value) {
-
- $owner = @$value['firstName']?(@$value['firstName']." ".@$value['lastName']):"NA";
- $sqlDesc = "select group_concat(description) as ActivityDescription from renewalleadFollowup where leadId=".@$value["leadId"]." ";
- $dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();
-
- $leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",",":",@$dataDesc[0]['ActivityDescription']):'';
-
-
-
-
-
- $state = @$value["state"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["state"])):'NA';
- $city = @$value["city"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["city"])):'NA';
- $customerType = @$value["customerType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["customerType"])):'NA';
- $productName = @$value["productName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["productName"])):'NA';
- $policyType = @$value["policyType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["policyType"])):'NA';
- $planType = @$value["planType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["planType"])):'NA';
- $campaignName = @$value["campaignName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["campaignName"])):'NA';
- $leadSourceTitle = @$value["leadSourceTitle"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadSourceTitle"])):'NA';
- $leadStatus = @$value["leadStatus"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStatus"])):'NA';
- $leadStage = @$value["leadStage"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStage"])):'NA';
- $owner = @$owner?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$owner)):'NA';
- $agntName = @$value["agntName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["agntName"])):'NA';
- $leadActivity = @$leadActivity?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$leadActivity)):'NA';
-
-
-
- $newarray = array(
- 'state' => $state,
- 'city' => $city,
- 'customerType' => addslashes($customerType),
- 'product' => addslashes($productName),
- 'policyType' => addslashes($policyType),
- 'planType' => addslashes($planType),
- 'campaign' => addslashes($campaignName),
- 'leadSource' => addslashes($leadSourceTitle),
- 'leadStatus' => addslashes($leadStatus),
- 'leadStage' => addslashes($leadStage),
- 'owner' => addslashes($owner),
- 'agntName' => addslashes($agntName),
- 'ActivityDescription' => str_replace("'","`",$leadActivity)
- );
- $jsonData = json_encode($newarray);
-
- $insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values ('".$value['leadId']."','".addslashes($value['name'])."','".$value['email']."','".$value['emailOptOut']."','".$value['mobile']."','".$value['mobileOptOut']."','".addslashes($value['address'])."','".$value['stateId']."','".$value['cityId']."','".$value['customerTypeId']."','".$value['productId']."','".$value['policyTypeId']."','".$value['planTypeId']."','".$value['campaignId']."','".addslashes($value['members'])."','".addslashes($value['si'])."','".addslashes($value['premium'])."','".addslashes($value['productDetails'])."','".$value['ageGroup']."','".$value['children']."','".$value['leadSourceId']."','".$value['leadStatusId']."','".$value['leadStageId']."','".$value['isJunk']."','".$value['currentAllocatedId']."','".$value['currentAllocatedDate']."','".$value['isDuplicate']."','".$value['duplicateLeadId']."','".$value['age']."','".$value['dateonSubmit']."','".$value['fileName']."','".addslashes($value['leadDescription'])."','".$value['isRead']."','".$value['status']."','".$value['ipaddress']."','".$value['agentId']."','".$value['createdOn']."','".$value['createdBy']."','".$value['updatedOn']."','".$value['updatedBy']."','".$value['ws_sourcecode']."','".$value['ws_subject']."','".$value['ws_telephone']."','".$value['ws_plan']."','".$value['ws_product']."','".$value['ws_quoteid']."','".$value['ws_proposalid']."','".$value['ws_leadstage']."','".$value['ws_agentid']."','".$value['ws_noofyears']."','".$value['ws_orgnaizationName']."','".$value['utmsource']."','".$value['isBulkImport']."','".$value['salesClosedUpdatedOn']."','".$jsonData."','".time()."','amit') ";
- $executeQuerys=Yii::app()->db->createCommand($insertQuery)->execute();
- //echo $value['leadId']."<br>";
- // $r++;
-
-
- }
- }
- public function actionagentwisetotalcalls(){
-
- $time = time();
- $parser = new CHtmlPurifier();
- $afterMonth = $parser->purify(intval(@$_REQUEST['month']));
- $afterDate = $parser->purify(intval(@$_REQUEST['date']));
- $afterYear = $parser->purify(intval(@$_REQUEST['year']));
- if(isset($afterMonth) && !empty($afterMonth) && isset($afterDate) && !empty($afterDate) && isset($afterYear) && !empty($afterYear)){
- $start = mktime(0, 0, 0,$afterMonth,$afterDate,$afterYear);
- $end = mktime(23, 59, 59,$afterMonth,$afterDate,$afterYear);
- } else {
- $start = mktime(0, 0, 0);
- $end = mktime(23, 59, 59);
- }
- $date = date('d_M_Y',$start)."_";
- $host = '10.216.6.59'; // MYSQL database host adress
- $db = 'lms'; // MYSQL database name
- $user = 'root'; // Mysql Datbase user
- $pass = '0kwp6aNR'; // Mysql Datbase password
- $link=mysql_connect($host,$user,$pass);
- mysql_select_db($db,$link);
- $table='report_agentwisetotalcalls';
- $filename = "filesystem/".$date.$table.'.csv';
- $csv_terminated = "\n";
- $csv_separator = ",";
- $csv_enclosed = '"';
- $csv_escaped = "\\";
-
- $totalleadstatusids = '7,8,9,10,37,38,11,12,42,43,13,14,15,16,17,18,19,30,21,22,23,24,25,26,27,28,29,30,33,39,43';
- mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','agentwisetotalcalls','".@$filename."','".@$time."','".@$_SESSION['id']."','".@$time."','".@$_SESSION['id']."')");
- $chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());
- $tableres ='create table IF NOT EXISTS '.$table.' as SELECT
- user.userName as `Caller Name`,
- SUM(CASE WHEN lead.currentAllocatedId=user.id AND lead.leadStatusId IN ('.@$totalleadstatusids.') THEN 1 ELSE 0 END) AS `Total Diposed`,
- SUM(CASE WHEN LS.`leadStatusId` = 7 THEN 1 ELSE 0 END) AS `Sale Done`,
- SUM(CASE WHEN LS.`leadStatusId` = 8 THEN 1 ELSE 0 END) AS `Follow Up`,
- SUM(CASE WHEN LS.`leadStatusId` = 9 THEN 1 ELSE 0 END) AS `Do Not Call - Will Buy self on Website`,
- SUM(CASE WHEN LS.`leadStatusId` = 10 THEN 1 ELSE 0 END) AS `Duplicate - Already In Talk With Team`,
- SUM(CASE WHEN LS.`leadStatusId` = 37 THEN 1 ELSE 0 END) AS `In Contact with Oflline Agent`,
- SUM(CASE WHEN LS.`leadStatusId` = 38 THEN 1 ELSE 0 END) AS `In Touch With Other Partner`,
- SUM(CASE WHEN LS.`leadStatusId` = 11 THEN 1 ELSE 0 END) AS `Short Hangup`,
- SUM(CASE WHEN LS.`leadStatusId` = 12 THEN 1 ELSE 0 END) AS `Call Later`,
- SUM(CASE WHEN LS.`leadStatusId` = 42 THEN 1 ELSE 0 END) AS `Ringing Less Than Five Tries`,
- SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off Less than Five Tries`,
- SUM(CASE WHEN LS.`leadStatusId` = 13 THEN 1 ELSE 0 END) AS `Not Interested - Fund Problem`,
- SUM(CASE WHEN LS.`leadStatusId` = 14 THEN 1 ELSE 0 END) AS `Just Checking Website`,
- SUM(CASE WHEN LS.`leadStatusId` = 15 THEN 1 ELSE 0 END) AS `Not Interested - Insured From Employer`,
- SUM(CASE WHEN LS.`leadStatusId` = 16 THEN 1 ELSE 0 END) AS `Not Interested - High Premium`,
- SUM(CASE WHEN LS.`leadStatusId` = 17 THEN 1 ELSE 0 END) AS `Under Age`,
- SUM(CASE WHEN LS.`leadStatusId` = 18 THEN 1 ELSE 0 END) AS `Not Visited Website`,
- SUM(CASE WHEN LS.`leadStatusId` = 19 THEN 1 ELSE 0 END) AS `Do Not Call`,
- SUM(CASE WHEN LS.`leadStatusId` = 20 THEN 1 ELSE 0 END) AS `Not Interested - General`,
- SUM(CASE WHEN LS.`leadStatusId` = 21 THEN 1 ELSE 0 END) AS `Over Age`,
- SUM(CASE WHEN LS.`leadStatusId` = 22 THEN 1 ELSE 0 END) AS `Not Interested - Already Insured`,
- SUM(CASE WHEN LS.`leadStatusId` = 23 THEN 1 ELSE 0 END) AS `Duplicate Data - Not Interested`,
- SUM(CASE WHEN LS.`leadStatusId` = 24 THEN 1 ELSE 0 END) AS `Not Eligible - PED`,
- SUM(CASE WHEN LS.`leadStatusId` = 25 THEN 1 ELSE 0 END) AS `Not Interested - Govt. Employee`,
- SUM(CASE WHEN LS.`leadStatusId` = 26 THEN 1 ELSE 0 END) AS `Not Interested - Senior Citizen`,
- SUM(CASE WHEN LS.`leadStatusId` = 27 THEN 1 ELSE 0 END) AS `Wrong Number`,
- SUM(CASE WHEN LS.`leadStatusId` = 28 THEN 1 ELSE 0 END) AS `Customer Not Available`,
- SUM(CASE WHEN LS.`leadStatusId` = 29 THEN 1 ELSE 0 END) AS `Language Barrier`,
- SUM(CASE WHEN LS.`leadStatusId` = 30 THEN 1 ELSE 0 END) AS `No Response`,
- SUM(CASE WHEN LS.`leadStatusId` = 33 THEN 1 ELSE 0 END) AS `Invalid Number`,
- SUM(CASE WHEN LS.`leadStatusId` = 36 THEN 1 ELSE 0 END) AS `Junk`,
- SUM(CASE WHEN LS.`leadStatusId` = 39 THEN 1 ELSE 0 END) AS `Ringing no response after five tries`,
- SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off/ Not Reachable after 5 tries`
- FROM `lead` join user on user.id=lead.currentAllocatedId left join leadStatus LS on LS.leadStatusId=lead.leadStatusId where lead.leadStatusId !=36 AND lead.updatedOn >= "'.@$start.'" and lead.updatedOn <= "'.@$end.'" group by lead.currentAllocatedId';
- $esx = mysql_query($tableres) or die(mysql_error());
- $queryforinsert = "Insert into ".$table." (`Caller Name`,`Total Diposed`,`Sale Done`,`Follow Up`,`Do Not Call - Will Buy self on Website`,`Duplicate - Already In Talk With Team`,`In Contact with Oflline Agent`,`In Touch With Other Partner`,`Short Hangup`,`Call Later`,`Ringing Less Than Five Tries`,`Switched Off Less than Five Tries`,`Not Interested - Fund Problem`,`Just Checking Website`,`Not Interested - Insured From Employer`,`Not Interested - High Premium`,`Under Age`,`Not Visited Website`,`Do Not Call`,`Not Interested - General`,`Over Age`,`Not Interested - Already Insured`,`Duplicate Data - Not Interested`,`Not Eligible - PED`,`Not Interested - Govt. Employee`,`Not Interested - Senior Citizen`,`Wrong Number`,`Customer Not Available`,`Language Barrier`,`No Response`,`Invalid Number`,`Junk`,`Ringing no response after five tries`,`Switched Off/ Not Reachable after 5 tries`)
-
- select 'Total',SUM(`Total Diposed`), SUM(`Sale Done`), SUM(`Follow Up`), SUM(`Do Not Call - Will Buy self on Website`), SUM(`Duplicate - Already In Talk With Team`), SUM(`In Contact with Oflline Agent`), SUM(`In Touch With Other Partner`), SUM(`Short Hangup`), SUM(`Call Later`), SUM(`Ringing Less Than Five Tries`), SUM(`Switched Off Less than Five Tries`), SUM(`Not Interested - Fund Problem`), SUM(`Just Checking Website`), SUM(`Not Interested - Insured From Employer`), SUM(`Not Interested - High Premium`), SUM(`Under Age`), SUM(`Not Visited Website`), SUM(`Do Not Call`), SUM(`Not Interested - General`), SUM(`Over Age`), SUM(`Not Interested - Already Insured`), SUM(`Duplicate Data - Not Interested`), SUM(`Not Eligible - PED`), SUM(`Not Interested - Govt. Employee`), SUM(`Not Interested - Senior Citizen`), SUM(`Wrong Number`), SUM(`Customer Not Available`), SUM(`Language Barrier`), SUM(`No Response`), SUM(`Invalid Number`), SUM(`Junk`), SUM(`Ringing no response after five tries`), SUM(`Switched Off/ Not Reachable after 5 tries`) from ".$table."";
- mysql_query($queryforinsert);
- $sql_query = "select * from $table";
-
- // Gets the data from the database
- $result = mysql_query($sql_query) or die(mysql_error());
- $fields_cnt = mysql_num_fields($result);
-
-
- $schema_insert = '';
-
- for ($i = 0; $i < $fields_cnt; $i++)
- {
- $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
- stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
- $schema_insert .= $l;
- $schema_insert .= $csv_separator;
- } // end for
-
- $out = trim(substr($schema_insert, 0, -1));
- $out .= $csv_terminated;
-
- // Format the data
- while ($row = mysql_fetch_array($result))
- {
- $schema_insert = '';
- for ($j = 0; $j < $fields_cnt; $j++)
- {
- if ($row[$j] == '0' || $row[$j] != '')
- {
-
- if ($csv_enclosed == '')
- {
- $schema_insert .= $row[$j];
- } else
- {
- $schema_insert .= $csv_enclosed .
- str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
- }
- } else
- {
- $schema_insert .= '';
- }
-
- if ($j < $fields_cnt - 1)
- {
- $schema_insert .= $csv_separator;
- }
- } // end for
-
- $out .= $schema_insert;
- $out .= $csv_terminated;
- } // end while
-
- /// header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
- //header("Content-Length: " . strlen($out));
- // Output to browser with appropriate mime type, you choose ;)
- // header("Content-Disposition: attachment; filename=$filename");
- //header("Content-type: text/x-csv");
- $fd = fopen ($filename, "w");
- fputs($fd, $out);
- fclose($fd);
- echo "success";
- //header("Content-type: text/csv");
- //header("Content-type: application/csv");
- //echo $out;
- exit;
-
- }
- public function actionagentfreshleadsdisposition(){
-
- /* $date = date('d_M_Y')."_";
- $start = mktime(0, 0, 0);
- $end = mktime(23, 59, 59);
- */
- ini_set('max_input_time', 2400);
- ini_set('max_execution_time', 2400);
-
- $parser = new CHtmlPurifier();
- $afterMonth = $parser->purify(intval(@$_REQUEST['month']));
- $afterDate = $parser->purify(intval(@$_REQUEST['date']));
- $afterYear = $parser->purify(intval(@$_REQUEST['year']));
- if(isset($afterMonth) && !empty($afterMonth) && isset($afterDate) && !empty($afterDate) && isset($afterYear) && !empty($afterYear)){
- $start = mktime(0, 0, 0,$afterMonth,$afterDate,$afterYear);
- $end = mktime(23, 59, 59,$afterMonth,$afterDate,$afterYear);
- } else {
- $start = mktime(0, 0, 0);
- $end = mktime(23, 59, 59);
- }
-
- $time = time();
- $date = date('d_M_Y',$start).'_';
-
-
-
- $host = '10.216.6.59'; // MYSQL database host adress
- $db = 'lms'; // MYSQL database name
- $user = 'root'; // Mysql Datbase user
- $pass = '0kwp6aNR'; // Mysql Datbase password
- $link=mysql_connect($host,$user,$pass);
- mysql_select_db($db,$link);
- $table='report_agentfreshleadsdisposition';
- $filename = "filesystem/".$date.$table.'.csv';
- $csv_terminated = "\n";
- $csv_separator = ",";
- $csv_enclosed = '"';
- $csv_escaped = "\\";
- $time = time();
- mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','agentfreshleadsdisposition','".@$filename."','".@$time."','".@$_SESSION['id']."','".@$time."','".@$_SESSION['id']."')");
- $totalleadstatusids = '7,8,9,10,37,38,11,12,42,43,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,33,39,43';
- $chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());
-
-
- $tableres ='create table IF NOT EXISTS '.$table.' as SELECT
- user.userName as `Caller Name`,
- SUM(CASE WHEN lead.currentAllocatedId=user.id THEN 1 ELSE 0 END) AS `Total Assigned`,
- SUM(CASE WHEN lead.currentAllocatedId=user.id AND lead.leadStatusId NOT IN ('.@$totalleadstatusids.') THEN 1 ELSE 0 END) AS `No Update`,
- SUM(CASE WHEN lead.currentAllocatedId=user.id AND lead.leadStatusId IN ('.@$totalleā¦
Large files files are truncated, but you can click here to view the full file