/lms_debug/protected/controllers/ReportsController.php
PHP | 1881 lines | 1515 code | 259 blank | 107 comment | 252 complexity | 99fef50f9b4be590022ce0de046ffd27 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'];
- @$mobile=@$_POST['LeadCronReport']['mobile'];
- @$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,$mobile);
-
-
- }
-
- /**
- * Action Report.
- */
- public function actionreports()
- {
- $model = new LeadCronReport('search');
- $model->scenario = 'LeadCronReport';
- $teamId = '';
- $teamUserId = '';
- if (@$_POST['LeadCronReport'] != '' && isset($_POST['LeadCronReport']))
- {
-
- if (Yii::app()->session->get('roleName') == 'agent')
- {
- @$_POST['LeadCronReport']['ws_agentid'] = self::getAgentId();
- }
-
- @$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'];
- $mobile = @$_POST['LeadCronReport']['mobile'];
- $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.leadSourceId='" . $ws_agentid . "'");
- }
- if (isset($mobile) && !empty($mobile))
- {
- $criteria->addCondition("t.mobile='" . $mobile . "'");
- }
-
- 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,'');
- }
-
- $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'];
- $model->set_mobile = @$_POST['LeadCronReport']['mobile'];
- $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) : ''),
- ),));
- if (Yii::app()->session->get('roleName') == 'agent')
- {
- $this->render('agentreports', array('model' => $model, 'dataProvider' => $dataProvider));
- }
- else
- {
- $this->render('reports', array('model' => $model, 'dataProvider' => $dataProvider, 'teamId' => $teamId, 'teamUserId' => $teamUserId));
- }
- }
- else
- {
-
- $criteria = new CDbCriteria;
- $criteria->select = 't.*';
- $criteria->order = 'leadId DESC';
- if (Yii::app()->session->get('roleName') == 'agent')
- {
- $criteria->condition = 'leadSourceId = ' . self::getAgentId();
- $dataProvider = new CActiveDataProvider('LeadCronReport', array('criteria' => $criteria,));
- $this->render('agentreports', array('model' => $model, 'dataProvider' => $dataProvider, 'teamId' => $teamId, 'teamUserId' => $teamUserId));
- }
- else
- {
- $dataProvider = new CActiveDataProvider('LeadCronReport', array('criteria' => $criteria,));
- $this->render('reports', array('model' => $model, 'dataProvider' => $dataProvider, 'teamId' => $teamId, 'teamUserId' => $teamUserId));
- }
- }
- }
-
- /**
- * Get Agent Id.
- * @return int.
- */
- public static function getAgentId()
- {
- $userId = Yii::app()->session->get('id');
- //echo "select leadsourcecode from leadsourcemaster where leadsourceid = (select leadsourceid from user where id = $userId )"; die;
- $leadSourceId = Yii::app()->db->createCommand("select leadsourceid from user where id = $userId ")->queryRow();
- return $leadSourceId['leadsourceid'];
- }
-
- /**
- * 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,$mobile){
- $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) && $isjunk=='yes' )
- {
- $condfilter .= "t.leadStatusId != 36 AND ";
- }
- if(isset($mobile) && !empty($mobile))
- {
- $condfilter .= "t.mobile ='".$mobile."' 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.address,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.createdOn,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.address,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.createdOn,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', 'Upload Date','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;
- }
- $uploadDate = date("d-M-Y H-i-s A",$value['createdOn']);
- $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@._\'-]|'$/", " ",@$uploadDate)) . ",";
- 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_sep…
Large files files are truncated, but you can click here to view the full file