ikan Uploader :
Directory : /var/www/html/shardahospital.org/shardalms/api/application/models/
Upload File : |
| Current File : /var/www/html/shardahospital.org/shardalms/api/application/models/Reports_model_live.php |
<?php
class Reports_model extends CI_Model {
function __construct() {
parent::__construct();
// $this->count_qr_select="COUNT(1) total,
// COUNT(IF(A.step_completed=1, 1, NULL)) step1,
// COUNT(IF(A.step_completed=2, 1, NULL)) step2,
// COUNT(IF(A.step_completed=3, 1, NULL)) step3,
// COUNT(IF(A.payment_status=1, 1, NULL)) paid,
// COUNT(IF(PSS.status='ADM_OFFERED' OR PSS.status='ADM_FINAL', 1, NULL)) adm_offered,
// COUNT(IF(PSS.status='ADM_FINAL', 1, NULL)) adm_final";
$this->count_qr_select="COUNT(1) total,
COUNT(IF(A.step_completed='1', 1, NULL)) step1,
COUNT(IF(A.step_completed='2', 1, NULL)) step2,
COUNT(IF(A.step_completed='3', 1, NULL)) step3,
COUNT(IF(A.step3date IS NOT NULL, 1, NULL)) paid,
COUNT(IF(A.step6date IS NOT NULL, 1, NULL)) adm_offered,
COUNT(IF(A.step7date IS NOT NULL, 1, NULL)) adm_final";
}
function set_search_cond($lvOpt=FALSE){
$qs=trim_array($_REQUEST);
if(USER_TYPE!='ADMIN' && USER_TYPE!='USER'){
$users=$this->user->active_users(true);
$userids=[0];
foreach($users as $u){
$userids[]=$u['id'];
}
}
if($qs['grp']){
$gutmids=$this->common->utm_group_ids($qs['grp']);
}
if($qs['data_set_id']){
$lvOpt=$this->dataset->detail($qs['data_set_id']);
}
$this->common->set_data_set_cond($lvOpt, true);
/** */
if(Lead_View_ByYou){
$uids=$userids;
if(!$uids){
$uids=[USER_ID];
}
$this->db->where_in("A.created_by", $uids);
}
if((Online_Leads || Offline_Leads) && !(Online_Leads && Offline_Leads)){
if(Online_Leads){
$this->db->where("U.verified_by!=", 'OFFLINE');
}
if(Offline_Leads){
$this->db->where("U.verified_by", 'OFFLINE');
}
}
/** \ */
if($qs['campus_id']){
$this->db->where("A.campus_id", $qs['campus_id']);
}
if($qs['utm_source_id']){
$this->db->where("U.utm_source_id", $qs['utm_source_id']);
}else if($qs['grp']){
$this->db->where_in("U.utm_source_id", $gutmids);
}
if($qs['state_id']){
$this->db->where("U.state_id", $qs['state_id']);
}
if($qs['dateFrom']){
$this->db->where("A.created >=", date('Y-m-d', strtotime($qs['dateFrom'])));
}
if($qs['dateTo']){
$this->db->where("A.created <=", date('Y-m-d 23:59:59', strtotime($qs['dateTo'])));
}
if($qs['med_phd']==='1'){
$this->db->where("U.is_medical", 1);
}elseif($qs['med_phd']==='2'){
$this->db->where("U.is_phd", 1);
}elseif($qs['med_phd']==='4'){
$this->db->where(["U.is_medical"=>0, "U.is_phd"=>0]);
}elseif($qs['med_phd']==='3'){
$this->db->where(["U.is_phd"=>0]);
}
$this->db->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT");
}
function dashboard($limit){
$lvOpt=$this->dataset->detail(0, USER_ID);
$source_id="-1";
if($lvOpt['utm_sources']){
$source_id=implode(",", $lvOpt['utm_sources']);
}
$res=array();
$today = date('Y-m-d');
$res['today_date']=date('d M Y');
$res['yesterday_date']=date('d M Y',strtotime('-1 day',strtotime($today)));
/** Today Leads */
$dtl=$this->today_yesterday_data('Today');
$res['today_leads']=$dtl['leads_count'];
$res['multi_applicant']['today']=$dtl['multi_applicant'];
$res['fail_txn']['today']=$dtl['fail_txn'];
$res['queries']['today']=$dtl['queries'];
$res['today_steps']=$dtl['steps'];
/** Old Leads */
$dtl=$this->today_yesterday_data('Yesterday');
$res['old_leads']=$dtl['leads_count'];
$res['multi_applicant']['old']=$dtl['multi_applicant'];
$res['fail_txn']['old']=$dtl['fail_txn'];
$res['queries']['old']=$dtl['queries'];
$res['old_steps']=$dtl['steps'];
$res['count']=$this->counts();
return $res;
// $res1=$this->dashboard_top_leads($limit);
// return array_merge($res, $res1);
}
function dashboard_top_leads($limit){
/** Top Lead Sources */
$res['lead_sources'] = $this->leadSourceWise($limit);
/** Top Lead States */
$res['lead_states'] = $this->leadStateWise($limit);
/** Top Lead Programmes */
$res['lead_programmes'] = $this->leadProgrammeWise($limit);
/** Top Lead Disciplines */
$res['lead_discipline'] = $this->leadDisciplineWise($limit);
/** Top Lead Schools */
$res['lead_schools'] = $this->leadSchoolWise($limit);
return $res;
}
function counts(){
$res=['queries_count'=>0, 'folloups_count'=>0, 'sent_emails_count'=>0, 'dead_leads_count'=>0];
$this->set_search_cond();
$rs=$this->db->select("COUNT(U.id) n", false)->from("applications A")->join("applicants U", "A.applicant_id=U.id")->join("issues_reported IR", "IR.applicant_id=U.id")->get()->row_array();
$res['queries_count']=(int)$rs['n'];
$this->set_search_cond();
$rs=$this->db->select("COUNT(U.id) n", false)->from("applications A")->join("applicants U", "A.applicant_id=U.id")->where(['A.last_followup_id>'=>0])->get()->row_array();
$res['folloups_count']=(int)$rs['n'];
$this->set_search_cond();
$rs=$this->db->select("COUNT(U.id) n", false)->from("applications A")->join("applicants U", "A.applicant_id=U.id")->join("sent_emails IR", "IR.to_email=U.email")->get()->row_array();
$res['sent_emails_count']=(int)$rs['n'];
$this->set_search_cond();
$rs=$this->db->select("COUNT(U.id) n", false)->from("applications A")->join("applicants U", "A.applicant_id=U.id")->where(['A.isdead'=>'Y'])->get()->row_array();
$res['dead_leads_count']=(int)$rs['n'];
return $res;
}
function today_yesterday_data($flg='Today'){
$report_query = "
COUNT(A.id) total,
COUNT(IF(A.step2date IS NOT NULL, 1, NULL)) step_2,
COUNT(IF(A.step2date IS NOT NULL AND A.step3date IS NOT NULL, 1, NULL)) susate_regd,
COUNT(IF(A.step2date IS NOT NULL AND A.step3date IS NOT NULL AND A.step4date IS NOT NULL, 1, NULL)) susate_appeared,
COUNT(IF(A.step2date IS NOT NULL AND A.step3date IS NOT NULL AND A.step4date IS NOT NULL AND A.step5date IS NOT NULL, 1, NULL)) admission_offered,
COUNT(IF(A.step2date IS NOT NULL AND A.step3date IS NOT NULL AND A.step4date IS NOT NULL AND A.step5date IS NOT NULL AND A.step6date IS NOT NULL, 1, NULL)) contract_signed,
COUNT(IF(A.step2date IS NOT NULL AND A.step3date IS NOT NULL AND A.step4date IS NOT NULL AND A.step5date IS NOT NULL AND A.step6date IS NOT NULL AND A.step7date IS NOT NULL, 1, NULL)) admitted
";
$today = date('Y-m-d');
$res=[];
$this->set_search_cond();
if($flg=='Today'){
$this->db->where("DATE(A.created)", $today);
}else{
$this->db->where("A.created <", $today);
}
$rs=$this->db->select($report_query, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
//->group_by("A.step_completed")
->get()->row_array();
//echo $this->db->last_query();
$res['leads_count']=['total'=>0, 'total_per'=>100, 'step_2'=>0, 'step_2_per'=>0, 'susate_regd'=>0, 'susate_regd_per'=>0, 'susate_appeared'=>0, 'susate_appeared_per'=>0, 'admission_offered'=>0, 'admission_offered_per'=>0, 'contract_signed'=>0, 'contract_signed_per'=>0, 'admitted' =>0, 'admitted_per' => 0];
$res['leads_count']['total']=$rs['total'];
$res['leads_count']['step_2']=$rs['step_2'];
$res['leads_count']['susate_regd']=$rs['susate_regd'];
$res['leads_count']['susate_appeared']=$rs['susate_appeared'];
$res['leads_count']['admission_offered']=$rs['admission_offered'];
$res['leads_count']['contract_signed']=$rs['contract_signed'];
$res['leads_count']['admitted']=$rs['admitted'];
if($res['leads_count']['total']>0){
$res['leads_count']['step_2_per']=round(($res['leads_count']['step_2']*100/$res['leads_count']['total']), 2);
$res['leads_count']['susate_regd_per']=round(($res['leads_count']['susate_regd']*100/$res['leads_count']['total']), 2);
$res['leads_count']['susate_appeared_per']=round(($res['leads_count']['susate_appeared']*100/$res['leads_count']['total']), 2);
$res['leads_count']['admission_offered_per']=round(($res['leads_count']['admission_offered']*100/$res['leads_count']['total']), 2);
$res['leads_count']['contract_signed_per']=round(($res['leads_count']['contract_signed']*100/$res['leads_count']['total']), 2);
$res['leads_count']['admitted_per']=round(($res['leads_count']['admitted']*100/$res['leads_count']['total']), 2);
}
$this->set_search_cond();
if($flg=='Today'){
$this->db->where("DATE(A.created)", $today);
}else{
$this->db->where("A.created <", $today);
}
$rs=$this->db->select("A.applicant_id, COUNT(DISTINCT A.id) total")
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("A.applicant_id")
->having("total >", 1)
->get()->result_array();
$res['multi_applicant']=(int)count($rs);
$this->set_search_cond();
if($flg=='Today'){
$this->db->where("DATE(T.created)", $today);
}else{
$this->db->where("T.created <", $today);
}
$this->db->where('T.txn_status', 'TXN_FAILURE');
$rs=$this->db->select("COUNT(DISTINCT T.id) total")
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("pay_trans T", "A.applicant_id=T.applicant_id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->get()->row_array();
$res['fail_txn']=(int)$rs['total'];
$this->set_search_cond();
$this->db->where('IR.parent_id', 0);
if($flg=='Today'){
$this->db->where("DATE(IR.created)", $today);
}else{
$this->db->where("IR.created <", $today);
}
$rs=$this->db->select("COUNT(DISTINCT IR.id) total")
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("issues_reported IR", "A.applicant_id=IR.applicant_id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->get()->row_array();
$res['queries']=(int)$rs['total'];
/** Cumalative Bar Chart */
$labels=['', 'Step 1', 'Prospect(Step-2)', 'SUSAT Registration(Step-3)', 'SUSAT Appeared(Step-4)', 'Admission Offered(Step-5)', 'Contract Signed(Step-6)', 'Admitted(Step-7)'];
$res['steps']=[
'steps'=>[], 'total'=>0, 'c_steps'=>[], 'c_total'=>0,
];
$this->set_search_cond();
if($flg=='Today'){
$this->db->where("DATE(A.created)", $today);
}else{
$this->db->where("A.created <", $today);
}
$rs=$this->db->select("IF(PSS.status='ADM_FINAL', '5', IF(PSS.status='ADM_OFFERED', '4', A.step_completed)) step, count(1) n")
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("step")
->get()->result_array();
$rs=key_val_array($rs, 'step', 'n');
for($i=1; $i<=7; $i++){
$res['steps']['steps'][]=['step'=>$labels[$i], 'c'=>(int)$rs[$i]];
$res['steps']['total']+=(int)$rs[$i];
$ct=0;
for($j=$i; $j<=7; $j++){
$ct+=(int)$rs[$j];
}
$res['steps']['c_steps'][]=['step'=>$labels[$i], 'c'=>$ct];
$res['steps']['c_total']=$res['steps']['total'];
}
return $res;
}
function count_lead_total_per($rs, $limit){
$res=['result'=>[], 'total'=>[]];
if($rs){
usort($rs, function($a, $b) {
return $b['total']-$a['total'];
});
$total['sum_total_lead']=$total['sum_step1']=$total['sum_step2']=$total['sum_step3']=$total['sum_paid']=$total['sum_adm_offered']=$total['sum_adm_final']=0;
$total['sum_total_lead_per']=100;
$total['sum_step1_per']=$total['sum_step2_per']=$total['sum_step3_per']=$total['sum_adm_offered_per']=$total['sum_adm_final_per']=0;
foreach($rs as $i=>$r){
if($i==$limit){
break;
}
$r['total_per']=100;
$r['step1_per']=$r['step2_per']=$r['step3_per']=$r['paid_per']=$r['adm_offered_per']=$r['adm_final_per']=0;
if($r['total']>0){
$r['step1_per']=number_format(($r['step1']*100/$r['total']), 1);
$r['step2_per']=number_format(($r['step2']*100/$r['total']), 1);
$r['step3_per']=number_format(($r['step3']*100/$r['total']), 1);
$r['paid_per']=number_format(($r['paid']*100/$r['total']), 1);
$r['adm_offered_per']=number_format(($r['adm_offered']*100/$r['total']), 1);
$r['adm_final_per']=number_format(($r['adm_final']*100/$r['total']), 1);
}
$total['sum_total_lead']=$total['sum_total_lead']+$r['total'];
$total['sum_step1']=$total['sum_step1']+$r['step1'];
$total['sum_step2']=$total['sum_step2']+$r['step2'];
$total['sum_step3']=$total['sum_step3']+$r['step3'];
$total['sum_paid']=$total['sum_paid']+$r['paid'];
$total['sum_adm_offered']=$total['sum_adm_offered']+$r['adm_offered'];
$total['sum_adm_final']=$total['sum_adm_final']+$r['adm_final'];
$res['result'][]=$r;
}
if($total['sum_total_lead']>0){
$total['sum_step1_per']=number_format(($total['sum_step1']*100/$total['sum_total_lead']), 1);
$total['sum_step2_per']=number_format(($total['sum_step2']*100/$total['sum_total_lead']), 1);
$total['sum_step3_per']=number_format(($total['sum_step3']*100/$total['sum_total_lead']), 1);
$total['sum_paid_per']=number_format(($total['sum_paid']*100/$total['sum_total_lead']), 1);
$total['sum_adm_offered_per']=number_format(($total['sum_adm_offered']*100/$total['sum_total_lead']), 1);
$total['sum_adm_final_per']=number_format(($total['sum_adm_final']*100/$total['sum_total_lead']), 1);
foreach($res['result'] as &$t){
$t['total_per_c']=$t['step1_per_c']=$t['step2_per_c']=$t['step3_per_c']=$t['paid_per_c']=$t['adm_offered_per_c']=$t['adm_final_per_c']=0;
if($total['sum_total_lead']>0){
$t['total_per_c']=number_format(($t['total']*100/$total['sum_total_lead']), 1);
}
if($total['sum_step1']>0){
$t['step1_per_c']=number_format(($t['step1']*100/$total['sum_step1']), 1);
}
if($total['sum_step2']>0){
$t['step2_per_c']=number_format(($t['step2']*100/$total['sum_step2']), 1);
}
if($total['sum_step3']>0){
$t['step3_per_c']=number_format(($t['step3']*100/$total['sum_step3']), 1);
}
if($total['sum_paid']>0){
$t['paid_per_c']=number_format(($t['paid']*100/$total['sum_paid']), 1);
}
if($total['sum_adm_offered']>0){
$t['adm_offered_per_c']=number_format(($t['adm_offered']*100/$total['sum_adm_offered']), 1);
}
if($total['sum_adm_final']>0){
$t['adm_final_per_c']=number_format(($t['adm_final']*100/$total['sum_adm_final']), 1);
}
}
}
$res['total']=$total;
}
return $res;
}
function leadGroupWise($limit=0){
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("S.grp title, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_utm_sources S", "U.utm_source_id=S.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("S.grp")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
//echo $this->db->last_query(); die;
return $this->count_lead_total_per($rs, $limit);
}
function leadSourceWise($limit=0){
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("S.title, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_utm_sources S", "U.utm_source_id=S.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("U.utm_source_id")
->get()->result_array();
return $this->count_lead_total_per($rs, $limit);
}
function leadMediumWise($limit=0){
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("IF(U.utm_medium='', 'N/A', U.utm_medium) title, {$this->count_qr_select}", false)
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("master_programs P", "A.program_id=P.id", "LEFT")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("U.utm_medium")
->get()->result_array();
return $this->count_lead_total_per($rs, $limit);
}
function leadStateWise($limit=0){
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("S.title, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_states S", "U.state_id=S.id")
->group_by("U.state_id")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
return $this->count_lead_total_per($rs, $limit);
}
function leadProgrammeWise($limit=0){
$programs=key_val_array($this->db->select("id,title")->get("master_programs")->result_array(), 'id', 'title');
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("A.program_id, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("A.program_id")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
//echo $this->db->last_query(); die;
if($rs){
foreach($rs as $i=>&$r){
$r['title']=$programs[$r['program_id']];
}
}
return $this->count_lead_total_per($rs, $limit);
}
function leadDisciplineWise($limit=0){
$disciplines=key_val_array($this->db->select("id,title")->get("master_disciplines")->result_array(), 'id', 'title');
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("P.discipline_id, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_programs P", "A.program_id=P.id", "LEFT")
//->join("master_disciplines D", "P.discipline_id=D.id")
//->join("master_utm_sources S", "U.utm_source_id=S.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("P.discipline_id")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
//echo $this->db->last_query(); die;
if($rs){
foreach($rs as $i=>&$r){
$r['title']=$disciplines[$r['discipline_id']];
}
}
return $this->count_lead_total_per($rs, $limit);
}
function leadSchoolWise($limit=0){
$schools=key_val_array($this->db->select("id,title")->get("master_schools")->result_array(), 'id', 'title');
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("P.school_id, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_programs P", "A.program_id=P.id", "LEFT")
//->join("master_schools S", "P.school_id=S.id")
//->join("master_utm_sources S", "U.utm_source_id=S.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("P.school_id")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
//echo $this->db->last_query(); die;
if($rs){
foreach($rs as $i=>&$r){
$r['title']=$schools[$r['school_id']];
}
}
return $this->count_lead_total_per($rs, $limit);
}
function leadLandingPageWise($limit=0){
$lpages=key_val_array($this->db->select("id,title")->get("landing_pages")->result_array(), 'id', 'title');
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("U.lpage_id, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("U.lpage_id")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
if($rs){
foreach($rs as $i=>&$r){
$r['title']=$lpages[$r['lpage_id']];
}
}
return $this->count_lead_total_per($rs, $limit);
}
function publishersWise($limit=0){
$publishers=$this->db->select("id, name, data_set_id")->get_where("users", ['type'=>'PUBLISHER', 'status'=>1])->result_array();
$rs=[];
foreach($publishers as $r){
$lvOpt=$this->dataset->detail($r['data_set_id']);
$this->set_search_cond($lvOpt);
$c=$this->db->select("{$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->get()->row_array();
$c['title']=$r['name'];
$rs[]=$c;
}
return $this->count_lead_total_per($rs, $limit?$limit:1000);
}
function genderWise($limit=0){
$limit=$limit?$limit:1000;
$this->set_search_cond();
$rs=$this->db->select("IF(U.gender='', 'N/A', U.gender) title, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("U.gender")
->get()->result_array();
return $this->count_lead_total_per($rs, $limit);
}
function amountWise($limit=0){
$limit=$limit?$limit:1000;
$this->set_search_cond();
$this->db->where('A.payment_status', 1);
$rs=$this->db->select("(A.fee-A.coupan_amt) title, {$this->count_qr_select}", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by("(A.fee-A.coupan_amt)")
->get()->result_array();
return $this->count_lead_total_per($rs, $limit);
}
function apiVendorWiseLeads(){
$qs=trim_array($_REQUEST);
if(IS_PUBLISHER){
$this->db->where("a.publisher_id", USER_ID);
}
if($qs['dateFrom']){
$this->db->where("a.created>=", get_date($qs['dateFrom'], '', 'Y-m-d'));
}
if($qs['dateTo']){
$this->db->where("a.created<=", get_date($qs['dateTo'], '', 'Y-m-d 23:59:59'));
}
$f="u.name vendor,
COUNT(1) total,
COUNT(IF(a.status='SUCCESS', 1, NULL)) success,
COUNT(IF(a.rejection_code='403', 1, NULL)) rejected_data_err,
COUNT(IF(a.rejection_code='405', 1, NULL)) rejected_duplicate,
COUNT(IF(a.rejection_code='406', 1, NULL)) rejected_state_not_allowed,
COUNT(IF(a.rejection_code='407', 1, NULL)) rejected_daily_limit
";
$rs=$this->db->select($f)
->from("api_leads a")
->join("users u", "u.id=a.publisher_id")
->group_by("a.publisher_id")
->order_by("vendor")
->get()
->result_array();
$totals=['total'=>0, 'success'=>0, 'rejected_duplicate'=>0, 'rejected_state_not_allowed'=>0, 'rejected_daily_limit'=>0, 'rejected_data_err'=>0];
if($rs){
foreach($rs as &$r){
foreach($totals as $k=>$v){
$r[$k]=(int)$r[$k];
$totals[$k]+=$r[$k];
}
}
}else{
$rs=[];
}
return ['lists'=>$rs, 'totals'=>$totals];
}
/** Detailed Analysis */
function leads_months(){
$rs=$this->db->select("DATE_FORMAT(created, '%Y-%m') ym, DATE_FORMAT(created, '%b,%Y') month_name")->from("applications")->group_by("ym")->get()->result_array();
return $rs;
}
function leads_paid_monthly_daily_format($rs, $k, $month=''){
if($month){
$heads=$this->db->select("DATE_FORMAT(created, '%Y-%m-%d') head, DATE_FORMAT(created, '%d %b,%Y') head_name")->from("applications")->like("created", $month, "AFTER")->group_by("head")->get()->result_array();
}else{
$heads=$this->db->select("DATE_FORMAT(created, '%Y-%m') head, DATE_FORMAT(created, '%b,%Y') head_name")->from("applications")->group_by("head")->get()->result_array();
}
$res=['head'=>$heads, 'result'=>[], 'total'=>[]];
if($rs){
$rs=group_array($rs, $k);
foreach($rs as $k=>$r){
$rs[$k]=group_array($rs[$k], 'head');
}
foreach($rs as $k=>$r){
foreach($res['head'] as $k1=>$r1){
$rs[$k][$r1['head']][0]['leads']=(int)$rs[$k][$r1['head']][0]['leads'];
$rs[$k][$r1['head']][0]['paid']=(int)$rs[$k][$r1['head']][0]['paid'];
$res['total'][$r1['head']]['leads']=(int)$res['total'][$r1['head']]['leads']+$rs[$k][$r1['head']][0]['leads'];
$res['total'][$r1['head']]['paid']=(int)$res['total'][$r1['head']]['paid']+$rs[$k][$r1['head']][0]['paid'];
}
}
$res['result']=$rs;
}
return $res;
}
function leads_group_wise_monthly(){
$this->set_search_cond(0,0);
$qs=trim_array($this->input->get());
if($qs['month']){
$f="S.grp grp, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="S.grp grp, DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$rs=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_utm_sources S", "U.utm_source_id=S.id")
->group_by("S.grp, head")
->order_by("leads", "DESC")
->get()->result_array();
return $this->leads_paid_monthly_daily_format($rs, 'grp', $qs['month']);
}
function leads_source_wise_monthly(){
$this->set_search_cond(0,0);
$qs=trim_array($this->input->get());
if($qs['month']){
$f="S.title source, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="S.title source, DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$rs=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_utm_sources S", "U.utm_source_id=S.id")
->group_by("U.utm_source_id, head")
->order_by("leads", "DESC")
->get()->result_array();
return $this->leads_paid_monthly_daily_format($rs, 'source', $qs['month']);
}
function leads_program_wise_monthly(){
$programs=key_val_array($this->db->select("id,title")->get("master_programs")->result_array(), 'id', 'title');
$this->set_search_cond(0,0);
$qs=trim_array($this->input->get());
if($qs['month']){
$f="A.program_id, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="A.program_id, DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$rs=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->group_by("A.program_id, head")
->order_by("leads", "DESC")
->get()->result_array();
foreach($rs as &$r){
$r['program']=$programs[$r['program_id']];
unset($r['program_id']);
}
return $this->leads_paid_monthly_daily_format($rs, 'program', $qs['month']);
}
function leads_discipline_wise_monthly(){
$disciplines=key_val_array($this->db->select("id,title")->get("master_disciplines")->result_array(), 'id', 'title');
$this->set_search_cond(0,0);
$qs=trim_array($this->input->get());
if($qs['month']){
$f="P.discipline_id, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="P.discipline_id, DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$rs=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_programs P", "A.program_id=P.id", "LEFT")
//->join("master_utm_sources S", "U.utm_source_id=S.id")
->group_by("P.discipline_id, head")
->order_by("leads", "DESC")
->get()->result_array();
foreach($rs as &$r){
$r['discipline']=$disciplines[$r['discipline_id']];
unset($r['discipline_id']);
}
return $this->leads_paid_monthly_daily_format($rs, 'discipline', $qs['month']);
}
function leads_state_wise_monthly(){
$this->set_search_cond(0,0);
$qs=trim_array($this->input->get());
if($qs['month']){
$f="S.title state, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="S.title state, DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$rs=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_states S", "U.state_id=S.id")
->group_by("U.state_id, head")
->order_by("leads", "DESC")
->get()->result_array();
return $this->leads_paid_monthly_daily_format($rs, 'state', $qs['month']);
}
function leads_publishers_wise_monthly(){
$qs=trim_array($this->input->get());
$publishers=$this->db->select("id, name, data_set_id")->get_where("users", ['type'=>'PUBLISHER'])->result_array();
$rs=[];
foreach($publishers as $r){
$lvOpt=$this->dataset->detail($r['data_set_id']);
$this->set_search_cond($lvOpt, 0);
if($qs['month']){
$f="DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$c=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->get()->row_array();
$c['name']=$r['name'];
$rs[]=$c;
}
usort($rs, function($a, $b) {
return $b['leads']-$a['leads'];
});
return $this->leads_paid_monthly_daily_format($rs, 'name', $qs['month']);
}
function leads_multi_source_da(){
$qs=trim_array($this->input->get());
//$sources=$this->db->select("id,title")->order_by("title")->get("master_utm_sources")->result_array();
//pr($sources);
$sources=$this->db->select("U.utm_source, COUNT(1) total, COUNT(IF(A.payment_status=1,1,NULL)) paid", FALSE)->from("applicants U")->join("applications A", "U.id=A.applicant_id")->group_by("U.utm_source")->get()->result_array();
//echo $this->db->last_query();
usort($sources, function($a, $b) {
return $b['total']-$a['total'];
});
$this->db->where(['H.is_paid'=>1]);
$paid_sources=$this->db->select("U.utm_source, IF(H.utm_source='', 'NA', H.utm_source) paid_source")->from("applicants U")->join("applicants_visit_history H", "U.id=H.applicant_id")->get()->result_array();
$paid_sources=group_array($paid_sources, 'utm_source');
$total=0;
$total_paid=0;
$res=['result'=>[], 'total'=>['leads'=>0, 'paid'=>0]];
foreach($sources as &$r){
$psources=$paid_sources[$r['utm_source']]?group_array($paid_sources[$r['utm_source']], 'paid_source'):[];
$r['psources']=[];
$npaid=0;
foreach($psources as $k=>$v){
$r['psources'][]=['source'=>$k, 'count'=>count($v)];
$npaid+=count($v);
}
if($r['paid']>$npaid){
$flg=0;
foreach($r['psources'] as $k=>$v){
if($v['source']=='NA'){
$r['psources'][$k]['count']+=($r['paid']-$npaid);
$flg=1;
}
}
if(!$flg){
$r['psources'][]=['source'=>'NA', 'count'=>($r['paid']-$npaid)];
}
}
usort($r['psources'], function($a, $b) {
return $b['count']-$a['count'];
});
$res['total']['leads']+=$r['total'];
$res['total']['paid']+=$r['paid'];
foreach($r['psources'] as $i=>$a){
$res['total']['total_s'.($i+1)]=intval($res['total']['total_s'.($i+1)])+$a['count'];
}
}
$res['result']=$sources;
$res['no_of_sources']=count($res['total'])-2;
return $res;
pr($res);
die;
}
function leads_gender_wise_monthly(){
$this->set_search_cond(0,0);
$qs=trim_array($this->input->get());
if($qs['month']){
$f="IF(U.gender='', 'N/A', U.gender) gender, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="IF(U.gender='', 'N/A', U.gender) gender, DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$rs=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->group_by("gender, head")
->order_by("leads", "DESC")
->get()->result_array();
return $this->leads_paid_monthly_daily_format($rs, 'gender', $qs['month']);
}
function leads_amount_wise_monthly(){
$this->set_search_cond(0,0);
$this->db->where('A.payment_status', 1);
$qs=trim_array($this->input->get());
if($qs['month']){
$f="(A.fee-A.coupan_amt) amount, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
$this->db->like("A.created", $qs['month'], "AFTER");
}else{
$f="(A.fee-A.coupan_amt) amount, DATE_FORMAT(A.created, '%Y-%m') head, COUNT(1) leads, COUNT(IF(A.payment_status=1, 1, NULL)) paid";
}
$rs=$this->db->select($f, false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->group_by("(A.fee-A.coupan_amt), DATE_FORMAT(A.created, '%Y-%m-%d')")
->order_by("leads", "DESC")
->get()->result_array();
return $this->leads_paid_monthly_daily_format($rs, 'amount', $qs['month']);
}
/** \ */
/** Other Reports */
function online_payments_cond($qs){
if($qs['status']){
if($qs['status']=='REFUNDED'){
$this->db->where("PT.payment_status", $qs['status']);
}else{
$this->db->where("PT.txn_status", $qs['status']);
}
}
if($qs['k']){
$this->db->group_start()
->like('U.name', $qs['k'])
->or_like('U.mob', $qs['k'])
->or_like('U.email', $qs['k'])
->or_like('A.application_no', $qs['k'])
->or_like('PT.pay_order_id', $qs['k'])
->or_like('PT.txn_id', $qs['k'])
->group_end();
}
if($qs['from_date']){
$this->db->where("PT.txn_date >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("PT.txn_date <=", date('Y-m-d 23:59:59', strtotime($qs['to_date'])));
}
if($qs['txn_amt']){
$this->db->where("PT.txn_amt", $qs['txn_amt']);
}
if($qs['med_phd']==1){
$this->db->where("U.is_medical", 1);
}elseif($qs['med_phd']==2){
$this->db->where("U.is_phd", 1);
}else{
$this->db->where(["U.is_medical"=>0, "U.is_phd"=>0]);
}
if($qs['state_ids']){
$this->db->where_in("U.state_id", explode(",", $qs['state_ids']));
}
if($qs['discipline_ids']){
$this->db->where_in("A.discipline_id", explode(",", $qs['discipline_ids']));
}
}
function online_payments($export=FALSE){
$qs=trim_array($this->input->get());
$this->online_payments_cond($qs);
$this->db->select("PT.id, PT.pay_order_id, PT.txn_id, PT.txn_date, PT.respmsg, PT.bank_name, PT.payment_mode, PT.bank_txn_id, PT.txn_status, PT.payment_status, PT.txn_amt, U.name, U.email, U.mob, A.application_no")
->from("pay_trans PT")
->join("applications A", "A.id=PT.application_id")
->join("applicants U", "U.id=PT.applicant_id")
->order_by("PT.id", "DESC");
$ps=50;
if($export){
$ps=100000;
}
$rs=$this->dba->pagedRows($qs['p'], $ps, 5, $export?'0':'');
foreach($rs['result'] as &$r){
$r['txn_date']=get_date($r['txn_date'], true);
}
return $rs;
}
function online_payments_total(){
$qs=trim_array($this->input->get());
$this->online_payments_cond($qs);
$total=$this->db->select("SUM(PT.txn_amt) amount")
->from("pay_trans PT")
->join("applications A", "A.id=PT.application_id")
->join("applicants U", "U.id=PT.applicant_id")
->get()->row()->amount;
return (float)$total;
}
function online_payments_init_data(){
$data['amt_lists']=$this->db->query("SELECT DISTINCT txn_amt FROM pay_trans WHERE txn_amt>0 ORDER BY txn_amt")->result_array();
$today=date('Y-m-d');
$todays=$this->db->select("txn_amt")->get_where("pay_trans", ['txn_status'=>'TXN_SUCCESS', 'DATE(txn_date)='=>$today])->result_array();
$data['today_paid_count']=$todays?count($todays):0;
$data['today_collection']=0;
foreach($todays as $r){
$data['today_collection']+=$r['txn_amt'];
}
$data['states']=$this->common->states();
$data['disciplines']=$this->common->disciplines();
return $data;
}
function cash_payments_cond($qs, $byyouonly=FALSE){
if($byyouonly){
$users=$this->user->active_users(true);
$ids=[];
foreach($users as $u){
$ids[]=$u['id'];
}
$this->db->where_in("C.created_by", $ids);
}
if($qs['k']){
$this->db->group_start()
->like('U.name', $qs['k'])
->or_like('U.mob', $qs['k'])
->or_like('A.application_no', $qs['k'])
->group_end();
}
if($qs['from_date']){
$this->db->where("C.created >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("C.created <=", date('Y-m-d 23:59:59', strtotime($qs['to_date'])));
}
if($qs['user_id']){
$this->db->where("C.created_by", $qs['user_id']);
}
/* if($qs['med_phd']==1){
$this->db->where("U.is_medical", 1);
}elseif($qs['med_phd']==2){
$this->db->where("U.is_phd", 1);
}else{
$this->db->where(["U.is_medical"=>0, "U.is_phd"=>0]);
} */
}
function cash_payments($export=FALSE, $byyouonly=FALSE){
$qs=trim_array($this->input->get());
$this->cash_payments_cond($qs, $byyouonly);
$this->db->select("A.id, U.name, U.mob, A.application_no, C.amount, C.created, U1.name usr")
->from("cash_collections C")
->join("applications A", "A.id=C.application_id")
->join("applicants U", "U.id=A.applicant_id")
->join("users U1", "C.created_by=U1.id")
->order_by("C.id", "DESC");
$ps=50;
if($export){
$ps=100000;
}
$rs=$this->dba->pagedRows($qs['p'], $ps, 5, $export?'0':'');
foreach($rs['result'] as &$r){
$r['created']=get_date($r['created'], true);
}
return $rs;
}
function cash_payments_total($byyouonly=FALSE){
$qs=trim_array($this->input->get());
$this->cash_payments_cond($qs, $byyouonly);
$total=$this->db->select("SUM(C.amount) amount")
->from("cash_collections C")
->join("applications A", "A.id=C.application_id")
->join("applicants U", "U.id=A.applicant_id")
->get()->row()->amount;
return (float)$total;
}
function cash_payments_summary($byyouonly=FALSE){
$this->cash_payments_cond(false, $byyouonly);
$today=date('Y-m-d');
$rs=$this->db->select("U1.id, U1.name, U1.type, SUM(C.amount) total, SUM(IF(DATE(C.created)='$today', C.amount, 0)) today_amt, SUM(IF(DATE(C.created)<'$today', C.amount, 0)) till_yest_amt")
->from("cash_collections C")
//->join("applications A", "A.id=C.application_id")
//->join("applicants U", "U.id=A.applicant_id")
->join("users U1", "C.created_by=U1.id")
->group_by("U1.id")
->order_by("total", "DESC")
->get()->result_array();
return $rs;
}
function callers_disp_cond($qs, $byyouonly=FALSE, $datetype=''){
if($byyouonly){
$users=$this->user->active_users(true);
$ids=[];
foreach($users as $u){
$ids[]=$u['id'];
}
$this->db->where_in("AR.created_by", $ids);
}
if($datetype==='Action' || !$datetype){
$c1="AR.created";
$c2="AR.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("$c2 <=", date('Y-m-d 23:59:59', strtotime($qs['to_date'])));
}
if($qs['user_id']){
$this->db->where("AR.created_by", $qs['user_id']);
}
}
function callers_disp($byyouonly=FALSE, $datetype=''){
$qs=trim_array($this->input->get());
$this->callers_disp_cond($qs, $byyouonly, $datetype);
$today=date('Y-m-d');
$f="LR.id, LR.title remarks, COUNT(DISTINCT AR.application_id) total";
$rs=$this->db->select($f)
->from("applications_remarks AR")
->join("master_lead_remarks LR", "LR.id=AR.remarks_id")
->join("applications A", "A.id=AR.application_id")
->group_by("LR.id")
->order_by("total", "DESC")
->get()->result_array();
return $rs;
}
function callers_disp_summary($byyouonly=FALSE){
$this->callers_disp_cond(false, $byyouonly);
$today=date('Y-m-d');
$rs=$this->db->select("U.id, U.name, U.type, U.data_set_id, COUNT(1) total, COUNT(IF(DATE(AR.created)='$today', 1, NULL)) today_count, SUM(IF(DATE(AR.created)<'$today', 1, NULL)) till_yest_count")
->from("applications_remarks AR")
->join("users U", "AR.created_by=U.id")
->group_by("U.id")
->order_by("total", "DESC")
->get()->result_array();
foreach($rs as &$r){
//$r['data_sets']=$this->user->user_data_sets($r);
}
return $rs;
}
/** Prev year analysis */
function prev_lms_acareers(){
return $this->db->select("DISTINCT career", false)->from("old_leads")->order_by("career")->get()->result_array();
}
function prev_lms_programs(){
return $this->db->select("DISTINCT course program", false)->from("old_leads")->order_by("program")->get()->result_array();
}
function prev_lms_plans(){
return $this->db->select("DISTINCT program plan", false)->from("old_leads")->order_by("plan")->get()->result_array();
}
function prev_lms_utmsources(){
return $this->db->select("DISTINCT utm_source", false)->from("old_leads")->order_by("utm_source")->get()->result_array();
}
function current_lms_data(){
$qs=trim_array($_REQUEST);
if($qs['from_date']){
$this->db->where("A.created >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("A.created <=", date('Y-m-d 23:59:59', strtotime($qs['to_date'])));
}
if($qs['utm_source_id']){
$this->db->where_in("U.utm_source_id", $qs['utm_source_id']);
}
if($qs['step_completed']){
$this->db->where_in("A.step_completed", $qs['step_completed']);
}
if($qs['ac_id']){
$this->db->where_in("P.ac_id", $qs['ac_id']);
}
if($qs['program_id']){
$this->db->where_in("P.id", $qs['program_id']);
}
if($qs['plan_id']){
$this->db->where_in("A.plan_id", $qs['plan_id']);
}
if($qs['state_id']){
$this->db->where_in("U.state_id", $qs['state_id']);
}
if($qs['gender']){
$this->db->where_in("U.gender", $qs['gender']);
}
$rs=$this->db->select("COUNT(1) total, COUNT(IF(A.step_completed=1, 1, NULL)) step1, COUNT(IF(A.step_completed=2, 1, NULL)) step2, COUNT(IF(A.step_completed=3, 1, NULL)) step3, COUNT(IF(A.step_completed=4, 1, NULL)) step4, COUNT(IF(A.step_completed=5, 1, NULL)) step5, COUNT(IF(A.step_completed=6, 1, NULL)) step6, COUNT(IF(A.step_completed=7, 1, NULL)) step7", false)
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("master_programs P", "A.program_id=P.id")
->get()->row_array();
return $rs;
}
function prev_lms_data(){
$qs=trim_array($_REQUEST);
if($qs['from_date']){
$this->db->where("A.created >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("A.created <=", date('Y-m-d 23:59:59', strtotime($qs['to_date'])));
}
if($qs['utm_source']){
$this->db->where_in("A.utm_source", $qs['utm_source']);
}
if($qs['step_completed']){
$this->db->where_in("A.step_completed", $qs['step_completed']);
}else{
$this->db->where("A.step_completed>", 0);
}
if($qs['ac']){
$this->db->where_in("A.career", $qs['ac']);
}
if($qs['program']){
$this->db->where_in("A.course", $qs['program']);
}
if($qs['plan']){
$this->db->where_in("A.program", $qs['plan']);
}
if($qs['state']){
$this->db->where_in("A.state", $qs['state']);
}
if($qs['gender']){
$this->db->where_in("A.gender", $qs['gender']);
}
$rs=$this->db->select("COUNT(1) total, COUNT(IF(A.step_completed=1, 1, NULL)) step1, COUNT(IF(A.step_completed=2, 1, NULL)) step2, COUNT(IF(A.step_completed=3, 1, NULL)) step3, COUNT(IF(A.step_completed=4, 1, NULL)) step4, COUNT(IF(A.step_completed=5, 1, NULL)) step5, COUNT(IF(A.step_completed=6, 1, NULL)) step6, COUNT(IF(A.step_completed=7, 1, NULL)) step7", false)
->from("old_leads A")
->get()->row_array();
return $rs;
}
function monthly_convertion_new_lms(){
$data['months']=$this->db->select("DISTINCT DATE_FORMAT(updated, '%Y-%m') ym, DATE_FORMAT(updated, '%b, %y') my", false)->from("applications")->get()->result_array();
foreach($data['months'] as &$m){
$total=$this->db->select("COUNT(1) n, COUNT(IF(step_completed=3, 1, NULL)) step3", false)->from("applications")->like("created", $m['ym'], "AFTER")->get()->row_array();
$m['total']=$total['n'];
$m['step3']=$total['step3'];
$paid_result=$this->db->select("DATE_FORMAT(step3date, '%Y-%m') ym, COUNT(1) n", false)
->from("applications")
->like("created", $m['ym'], "AFTER")
->where(['step_completed'=>3])
->group_by("ym")
->get()->result_array();
$paid_result=key_val_array($paid_result, 'ym', 'n');
foreach($data['months'] as $r){
$m['paid'][$r['ym']]=(int)$paid_result[$r['ym']];
}
}
return $data;
}
function monthly_convertion_prev_lms(){
$this->db->cache_on();
$data['months']=$this->db->select("DISTINCT DATE_FORMAT(updated, '%Y-%m') ym, DATE_FORMAT(updated, '%b, %y') my", false)->from("old_leads")->get()->result_array();
foreach($data['months'] as &$m){
$total=$this->db->select("COUNT(1) n, COUNT(IF(step_completed=3, 1, NULL)) step3", false)->from("old_leads")->like("created", $m['ym'], "AFTER")->get()->row_array();
$m['total']=$total['n'];
$m['step3']=$total['step3'];
$paid_result=$this->db->select("DATE_FORMAT(updated, '%Y-%m') ym, COUNT(1) n", false)
->from("old_leads")
->like("created", $m['ym'], "AFTER")
->where(['step_completed'=>3])
->group_by("ym")
->get()->result_array();
$paid_result=key_val_array($paid_result, 'ym', 'n');
foreach($data['months'] as $r){
$m['paid'][$r['ym']]=(int)$paid_result[$r['ym']];
}
}
return $data;
}
/** Campaigns */
function campaigns(){
$qs=trim_array($this->input->get());
if($qs['k']){
$this->db->group_start()
->like('C.title', $qs['k'])->or_like('T.title', $qs['k'])
->group_end();
}
if($qs['data_set_id']){
$this->db->where("C.data_set_id", $qs['data_set_id']);
}
if($qs['type']){
$this->db->where("T.type", $qs['type']);
}
$this->db->select("C.id, C.title, C.status, C.created, C.publish_date, C.no_of_leads, D.title data_set, T.title template, T.type, T.is_hidden")
->from("campaigns C")
->join("leads_data_sets D", "D.id=C.data_set_id")
->join("campaign_templates T", "T.id=C.template_id")
->order_by("C.id", "DESC");
$rs=$this->dba->pagedRows($qs['p'], 50, 5);
foreach($rs['result'] as &$r){
$r['created'] =get_date($r['created']);
$r['publish_date'] =get_date($r['publish_date']);
if($r['is_hidden']){
$r['template']='Without Template';
}
}
return $rs;
}
function campaign_detail($id){
$dtl=$this->db->get_where("campaigns", ['id'=>$id])->row_array();
if($dtl){
$tdtl=$this->campaign_template_detail($dtl['template_id']);
$dtl['type']=$tdtl['type'];
$dtl['is_hidden_temp']=(int)$tdtl['is_hidden'];
if($dtl['is_hidden_temp']){
$dtl['subject']=$tdtl['subject'];
$dtl['msg']=$tdtl['msg'];
}else{
$dtl['subject']='';
$dtl['msg']='';
}
}
return $dtl;
}
function delete_campaign($id){
$this->db->db_debug=FALSE;
$this->db->delete("campaigns", array('id'=>$id, 'status'=>'DRAFT'));
return $this->db->affected_rows();
}
function campaign_templates($type=''){
$qs=trim_array($this->input->get());
if($type){
if(is_array($type)){
$this->db->where_in("type", $type);
}else{
$this->db->where("type", $type);
}
}
if(!IS_ADMIN){
//$this->db->where("created_by", USER_ID);
}
$this->db->where("is_hidden", 0);
if($qs['k']){
$this->db->like('title', $qs['k'])->or_like('type', $qs['k']);
}
$rs=$this->db->select("id, title, type")->from("campaign_templates")->order_by("title")->get()->result_array();
return $rs?$rs:[];
}
function campaign_template_detail($id){
$dtl=$this->db->get_where("campaign_templates", ['id'=>$id])->row_array();
return $dtl;
}
function delete_campaign_template($id){
$this->db->db_debug=FALSE;
$this->db->delete("campaign_templates", array('id'=>$id));
return $this->db->affected_rows();
}
/** */
function publish_campaign($campaign_id){
ignore_user_abort(1);
$inf=array('success'=>false, 'msg'=>'Error while publishing!');
$dtl=$this->campaign_detail($campaign_id);
$no_of_leads=0;
if($dtl){
$lvOpt=$this->dataset->detail($dtl['data_set_id']);
if(!$lvOpt || ($lvOpt['expiry_date'] && strtotime($lvOpt['expiry_date'])<strtotime(date('Y-m-d')))){
$inf['msg']="Invalid Data set or Expired Data Set!";
return $inf;
}
$tdtl=$this->campaign_template_detail($dtl['template_id']);
if($tdtl){
$lpages=key_val_array($this->db->select("id,title")->get("landing_pages")->result_array(), 'id', 'title');
$states=key_val_array($this->db->select("id,title")->get("master_states")->result_array(), 'id', 'title');
$this->leads->set_search_cond(false, $lvOpt);
$rs=$this->db->select("A.id, A.application_no system_id, A.step_completed, U.name, U.email, U.mob, U.lpage_id, U.state_id, P.title program, PP.title plan, PSS.ADM_APPL_NBR ps_app_no")
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("master_programs P", "P.id=A.program_id")
->join("master_program_plans PP", "PP.id=A.plan_id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->get()->result_array();
if($rs){
$no_of_leads=count($rs);
$msg_list=[];
foreach($rs as $r){
$r['lpage']=$lpages[$r['lpage_id']];
$r['state']=$states[$r['state_id']];
$subject=$tdtl['subject']?campain_tag_value($tdtl['subject'], $r):'';
$msg=campain_tag_value($tdtl['msg'], $r);
$msg_list[]=['email'=>$r['email'], 'mob'=>$r['mob'], 'subject'=>$subject, 'msg'=>$msg];
}
if($tdtl['type']=='EMAIL'){
send_campaign_email($msg_list, $dtl['from_name'], $dtl['from_email'], $dtl['reply_to_email']);
}else{
$this->common->send_campaign_sms($msg_list);
}
$inf['msg_list']=$msg_list;
$inf['success']=true;
}
}
}
if($inf['success']){
$d=['id'=>$campaign_id, 'status'=>'PUBLISHED', 'publish_date'=>currentDT(), 'no_of_leads'=>$no_of_leads, 'sent_subject'=>$tdtl['subject'], 'sent_msg'=>$tdtl['msg']];
$this->common->save($d, "campaigns");
$inf['msg']="Campaign published successfully";
}
return $inf;
}
function test_campaign($post){
$inf=array('success'=>false, 'msg'=>'Error!');
$test_usr=explode(",", $post['test_usr']);
$rs=[];
foreach($test_usr as $u){
if(is_email($u)){
$this->db->where(['U.email'=>$u]);
}else{
$this->db->where(['U.mob'=>$u]);
}
$res=$this->db->select("A.id, A.application_no system_id, A.step_completed, U.name, U.email, U.mob, U.lpage_id, U.state_id, P.title program, PP.title plan, PSS.ADM_APPL_NBR ps_app_no")
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("master_programs P", "P.id=A.program_id")
->join("master_program_plans PP", "PP.id=A.plan_id")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->get()->row_array();
if($res){
$rs[]=$res;
}else{
$rs[]=['email'=>is_email($u)?$u:'', 'mob'=>!is_email($u)?$u:''];
}
}
if(!$rs){
$inf['msg']="Invalid test email/mob!";
return $inf;
}
$lpages=key_val_array($this->db->select("id,title")->get("landing_pages")->result_array(), 'id', 'title');
$states=key_val_array($this->db->select("id,title")->get("master_states")->result_array(), 'id', 'title');
$msg_list=[];
foreach($rs as $r){
$r['lpage']=$lpages[$r['lpage_id']];
$r['state']=$states[$r['state_id']];
$subject=$post['subject']?campain_tag_value($post['subject'], $r):'';
$msg=campain_tag_value($post['msg'], $r);
$msg_list[]=['email'=>$r['email'], 'mob'=>$r['mob'], 'subject'=>$subject, 'msg'=>$msg];
}
if($post['type']=='EMAIL'){
send_campaign_email($msg_list, $post['from_name'], $post['from_email'], $post['reply_to_email']);
}else{
$this->common->send_campaign_sms($msg_list);
}
$inf['msg_list']=$msg_list;
$inf['msg']="Test Campaign message sent successfully";
$inf['success']=true;
return $inf;
}
function lists_leads_login_url($export=FALSE){
$md=$this->common->master_data_for_leads();
$qs=trim_array($_REQUEST);
$this->db->where(["U.is_medical"=>0, "A.id>"=>0]);
if($qs['k']){
$this->db->group_start()
->where('U.name', $qs['k'])
->or_where('U.email', $qs['k'])
->or_where('U.mob', $qs['k'])
->or_where('A.application_no', $qs['k'])
->group_end();
}
if($qs['step_completed']){
$this->db->where_in("A.step_completed", explode(",", $qs['step_completed']));
}
if($qs['fromDate']){
$this->db->where("A.created >=", date('Y-m-d', strtotime($qs['fromDate'])));
}
if($qs['toDate']){
$this->db->where("A.created <=", date('Y-m-d 23:59:59', strtotime($qs['toDate'])));
}
$this->db->select("SQL_CALC_FOUND_ROWS A.id, A.applicant_id, A.application_no, A.step_completed,
A.created leadCreated, A.plan_id, P.title program, U.name, U.email,
U.mob, U.city, U.state_id, U.lpage_id, P.discipline_id", FALSE)
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("master_programs P", "P.id=A.program_id")
->order_by("A.id", "DESC");
$ps=50;
if($export){
$qs['p']=1;
$ps=1000000;
}
$rs=$this->dba->pagedRows($qs['p'], $ps, 5, $export?'0':'');
foreach($rs['result'] as &$r){
$r['plan'] =$md['plans'][$r['plan_id']];
$r['lpage'] =$md['lpages'][$r['lpage_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['state'] =$md['states'][$r['state_id']];
$r['leadCreated'] =get_date($r['leadCreated']);
$r['login_url'] =generate_login_url($r['lpage'], $r['mob']);
unset($r['plan_id'], $r['discipline_id'], $r['state_id'], $r['lpage_id']);
}
return $rs;
}
}
//End of file
Kontol Shell Bypass