ikan Uploader :
Directory : /var/www/html/shardahospital.org/shardalms/api/application/models_old/
Upload File : |
| Current File : /var/www/html/shardahospital.org/shardalms/api/application/models_old/Reports_model.php |
<?php
class Reports_model extends CI_Model {
function set_search_cond($lvOpt=FALSE){
$qs=trim_array($_REQUEST);
if(!$lvOpt){
$lvOpt=$this->dataset->detail(0, USER_ID);
}
if($lvOpt['utm_sources']){
$this->db->where_in("U.utm_source_id", $lvOpt['utm_sources']);
}
if($lvOpt['states'] && $lvOpt['cities']){
$this->db->group_start();
foreach($lvOpt['states'] as $i=>$state_id){
if($i==0){
$this->db->group_start();
}else{
$this->db->or_group_start();
}
$this->db->where("U.state_id", $state_id);
if($lvOpt['cities'][$state_id]){
$lvOpt['cities'][$state_id][]=0;
$this->db->where_in("U.city_id", $lvOpt['cities'][$state_id]);
}
$this->db->group_end();
}
$this->db->group_end();
}else if($lvOpt['states']){
$this->db->where_in("U.state_id", $lvOpt['states']);
}
if($lvOpt['programs']){
$this->db->where_in("A.program_id", $lvOpt['programs']);
}
if($lvOpt['steps']){
$this->db->where_in("A.step_completed", $lvOpt['steps']);
}
if($lvOpt['payment_modes']){
$lvOpt['payment_modes'][]='';
$this->db->where_in("A.payment_mode", $lvOpt['payment_modes']);
}
if($lvOpt['lead_date_from']){
$this->db->where("A.created >=", date('Y-m-d', strtotime($lvOpt['lead_date_from'])));
}
if($lvOpt['lead_date_to']){
$this->db->where("A.created <=", date('Y-m-d 23:59:59', strtotime($lvOpt['lead_date_to'])));
}
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'])));
}
$this->db->where("U.id>", 0);
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
}
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'];
/** 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'];
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 today_yesterday_data($flg='Today'){
$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("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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PS", "PS.EMPLID=A.application_no", "LEFT")
//->group_by("A.step_completed")
->get()->row_array();
$res['leads_count']=['total'=>0, 'total_per'=>100, 'step1'=>0, 'step1_per'=>0, 'step2'=>0, 'step2_per'=>0, 'step3'=>0, 'step3_per'=>0, 'adm_offered'=>0, 'adm_offered_per'=>0, 'adm_final'=>0, 'adm_final_per'=>0];
$res['leads_count']['total']=$rs['total'];
$res['leads_count']['step1']=$rs['step1'];
$res['leads_count']['step2']=$rs['step2'];
$res['leads_count']['step3']=$rs['step3'];
$res['leads_count']['adm_offered']=$rs['adm_offered'];
$res['leads_count']['adm_final']=$rs['adm_final'];
if($res['leads_count']['total']>0){
$res['leads_count']['step1_per']=round(($res['leads_count']['step1']*100/$res['leads_count']['total']), 2);
$res['leads_count']['step2_per']=round(($res['leads_count']['step2']*100/$res['leads_count']['total']), 2);
$res['leads_count']['step3_per']=round(($res['leads_count']['step3']*100/$res['leads_count']['total']), 2);
$res['leads_count']['adm_offered_per']=round(($res['leads_count']['adm_offered']*100/$res['leads_count']['total']), 2);
$res['leads_count']['adm_final_per']=round(($res['leads_count']['adm_final']*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(A.id) total")
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->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(T.id) total")
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("pay_trans T", "A.applicant_id=T.applicant_id")
->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(IR.id) total")
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("issues_reported IR", "A.applicant_id=IR.applicant_id")
->get()->row_array();
$res['queries']=(int)$rs['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_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['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['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_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_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['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_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, 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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", 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 PS", "PS.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, 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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", 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 PS", "PS.EMPLID=A.application_no", "LEFT")
->group_by("U.utm_source_id")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
//echo $this->db->last_query(); die;
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, 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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_states S", "U.state_id=S.id")
->join("ps_stu_status PS", "PS.EMPLID=A.application_no", "LEFT")
->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, 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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PS", "PS.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, 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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_programs P", "A.program_id=P.id")
//->join("master_disciplines D", "P.discipline_id=D.id")
//->join("master_utm_sources S", "U.utm_source_id=S.id")
->join("ps_stu_status PS", "PS.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, 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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("master_programs P", "A.program_id=P.id")
//->join("master_schools S", "P.school_id=S.id")
//->join("master_utm_sources S", "U.utm_source_id=S.id")
->join("ps_stu_status PS", "PS.EMPLID=A.application_no", "LEFT")
->group_by("P.school_id")
//->order_by("total", "DESC")
//->limit($limit)
->get()->result_array();
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, 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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PS", "PS.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("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(PS.status='ADM_OFFERED', 1, NULL)) adm_offered, COUNT(IF(PS.status='ADM_FINAL', 1, NULL)) adm_final", false)
->from("applications A ")
->join("applicants U ", "A.applicant_id=U.id")
->join("ps_stu_status PS", "PS.EMPLID=A.application_no", "LEFT")
->get()->row_array();
$c['title']=$r['name'];
$rs[]=$c;
}
return $this->count_lead_total_per($rs, $limit?$limit:1000);
}
/** 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(){
$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.step_completed=3, 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.step_completed=3, 1, NULL)) paid";
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
$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(){
$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.step_completed=3, 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.step_completed=3, 1, NULL)) paid";
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
$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(){
$qs=trim_array($this->input->get());
$programs=key_val_array($this->db->select("id,title")->get("master_programs")->result_array(), 'id', 'title');
if($qs['month']){
$f="A.program_id, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.step_completed=3, 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.step_completed=3, 1, NULL)) paid";
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
$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(){
$qs=trim_array($this->input->get());
$disciplines=key_val_array($this->db->select("id,title")->get("master_disciplines")->result_array(), 'id', 'title');
if($qs['month']){
$f="P.discipline_id, DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.step_completed=3, 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.step_completed=3, 1, NULL)) paid";
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
$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")
//->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(){
$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.step_completed=3, 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.step_completed=3, 1, NULL)) paid";
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
$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', 'status'=>1])->result_array();
$rs=[];
foreach($publishers as $r){
$lvOpt=$this->dataset->detail($r['data_set_id']);
$this->set_search_cond($lvOpt);
if($qs['month']){
$f="DATE_FORMAT(A.created, '%Y-%m-%d') head, COUNT(1) leads, COUNT(IF(A.step_completed=3, 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.step_completed=3, 1, NULL)) paid";
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
$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']);
}
/** \ */
/** 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('S.name', $qs['k'])
->or_like('S.mob', $qs['k'])
->or_like('S.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['is_medical']){
$this->db->where("S.is_medical", 1);
}else{
$this->db->where("S.is_medical", 0);
}
}
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, S.name, S.email, S.mob, A.application_no")
->from("pay_trans PT")
->join("applications A", "A.id=PT.application_id")
->join("applicants S", "S.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 S", "S.id=PT.applicant_id")
->get()->row()->amount;
return (float)$total;
}
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('S.name', $qs['k'])
->or_like('S.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['is_medical']){
$this->db->where("S.is_medical", 1);
}else{
$this->db->where("S.is_medical", 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, S.name, S.mob, A.application_no, C.amount, C.created, U.name usr")
->from("cash_collections C")
->join("applications A", "A.id=C.application_id")
->join("applicants S", "S.id=A.applicant_id")
->join("users U", "C.created_by=U.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 S", "S.id=A.applicant_id")
->join("users U", "C.created_by=U.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("U.id, U.name, U.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("users U", "C.created_by=U.id")
->group_by("U.id")
->order_by("total", "DESC")
->get()->result_array();
return $rs;
}
function callers_disp_cond($qs, $byyouonly=FALSE){
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($qs['from_date']){
$this->db->where("AR.created >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("AR.created <=", 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){
$qs=trim_array($this->input->get());
$this->callers_disp_cond($qs, $byyouonly);
$today=date('Y-m-d');
$rs=$this->db->select("LR.id, LR.title remarks, COUNT(1) total")
->from("applications_remarks AR")
->join("master_lead_remarks LR", "LR.id=AR.remarks_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, DS.title data_set, 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")
->join("leads_data_sets DS", "U.data_set_id=DS.id", "LEFT")
->group_by("U.id")
->order_by("total", "DESC")
->get()->result_array();
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", 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", 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(){
$qs=trim_array($this->input->get());
$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;
}
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