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/Dashboard_model_06082024.php |
<?php
class Dashboard_model extends CI_Model {
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['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['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', strtotime($qs['to_date'])));
}
//$this->db->where("A.step_completed!=", '0');
}
function scoreBoard(){
$res=[];
$this->set_search_cond();
$f="COUNT(A.id) total, COUNT(IF(A.step_completed='3', 1, NULL)) paid, COUNT(IF(U.verified_by='OFFLINE', 1, NULL)) offline, COUNT(IF(A.api_lead_id>0, 1, NULL)) api_leads, COUNT(IF(A.last_followup_id>0, 1, NULL)) total_followups";
$rs=$this->db->select($f, false)
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->get()->row_array();
$res['total']=(int)$rs['total'];
$res['paid']=(int)$rs['paid'];
$res['offline']=(int)$rs['offline'];
$res['api_leads']=(int)$rs['api_leads'];
$res['online']=($res['total'])-$res['offline'];
$res['offline_per']=$res['total']>0?round(($res['offline']*100)/$res['total']):0;
$res['online_per']=$res['total']>0?round(($res['online']*100)/$res['total']):0;
$res['api_leads_per']=$res['total']>0?round(($res['api_leads']*100)/$res['total']):0;
$res['total_followups']=(int)$rs['total_followups'];
$res['total_followups_paid']=(int)$rs['total_followups_paid'];
$res['untouched']=$res['total']-$rs['total_followups'];
$res['untouched_paid']=$res['paid']-$rs['total_followups_paid'];
$this->set_search_cond();
$rs=$this->db->select("R.id, R.title, R.disp_odr, count(A.id) c")
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("applications_remarks AR", "AR.id=A.last_followup_id")
->join("master_lead_remarks R", "AR.remarks_id=R.id")
->group_by("R.id")
->order_by("R.disp_odr")
->get()->result_array();
$res['followups']=$rs?$rs:[];
foreach($res['followups'] as &$a1){
$a1['c']=(int)$a1['c'];
$a1['per']=$res['total_followups']>0?round(($a1['c']*100)/$res['total_followups']):0;
}
$labels=['Enquiry', 'Prospect (Step-2)', 'SUSAT Registration(Step-3)', 'SUSAT Appeared(Step-4)', 'Admission Offered(Step-5)', 'Contract Signed(Step-6)', 'Admitted(Step-7)'];
$res['steps']=[];
$this->set_search_cond();
$fs = "
COUNT(A.id) total,
COUNT(A.id) step_1,
COUNT(IF(A.step3date IS NOT NULL, 1, NULL)) step_2,
COUNT(IF(A.step3date IS NOT NULL, 1, NULL)) susate_regd,
COUNT(IF(A.step3date IS NOT NULL AND A.step4date IS NOT NULL, 1, NULL)) susate_appeared,
COUNT(IF(A.step3date IS NOT NULL AND A.step4date IS NOT NULL AND A.step5date IS NOT NULL, 1, NULL)) admission_offered,
COUNT(IF(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.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
";
$rs=$this->db->select($fs, false)->from("applications A")
->get()
->result_array();
//echo "<pre>"; print_r($rs); die;
if (!empty($rs)) {
$rs = $rs[0];
$res['total'] = $rs['total'];
$steps_data = [
$rs['step_1'],
$rs['step_2'],
$rs['susate_regd'],
$rs['susate_appeared'],
$rs['admission_offered'],
$rs['contract_signed'],
$rs['admitted']
];
for ($i = 0; $i <= 6; $i++) {
$ct = $steps_data[$i];
$per = $res['total'] > 0 ? round(($ct * 100) / $res['total']) : 0;
$res['steps'][] = [
'step' => $labels[$i],
'c' => $ct,
'per' => $per
];
}
}
//echo "<pre>"; print_r($res['steps']); die;
$this->set_search_cond();
$rs=$this->db->select("count(1) n")
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->join("people_soft_tbl PS", "PS.EMPLID=A.application_no")
->get()->row_array();
$res['psoft']=['total'=>(int)$rs['n']];
$this->set_search_cond();
$f="COUNT(U.id) total, COUNT(IF(IR.status=1, 1, NULL)) open, COUNT(IF(IR.status=2, 1, NULL)) progress, COUNT(IF(IR.status=3, 1, NULL)) closed";
$rs=$this->db->select($f, false)->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->join("issues_reported IR", "IR.applicant_id=U.id")
->get()->row_array();
$res['queries']=[
'total'=>(int)$rs['total'],
'open'=>(int)$rs['open'],
'progress'=>(int)$rs['progress'],
'closed'=>(int)$rs['closed'],
];
$res['queries']['open_per']=$res['queries']['total']>0?round(($res['queries']['open']*100)/$res['queries']['total']):0;
$res['queries']['progress_per']=$res['queries']['total']>0?round(($res['queries']['progress']*100)/$res['queries']['total']):0;
$res['queries']['closed_per']=$res['queries']['total']>0?round(($res['queries']['closed']*100)/$res['queries']['total']):0;
$this->set_search_cond();
$res['email_count']=(int)$this->db->select("count(1) c")
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->join("sent_emails SE", "SE.to_email=U.email")
->get()->row("c");
$this->set_search_cond();
$res['sms_count']=(int)$this->db->select("count(1) c")
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->join("followup_sent_sms SS", "SS.application_id=A.id")
->get()->row("c");
$this->set_search_cond();
$rs=$this->db->select("AR.comm_mode, count(1) c")
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->join("applications_remarks AR", "AR.application_id=A.id")
->group_by("AR.comm_mode")
->order_by("comm_mode")
->get()->result_array();
$res['cmodes']=['total'=>$res['email_count']+$res['sms_count'], 'modes'=>[]];
foreach($rs as $a2){
$res['cmodes']['total']+=(int)$a2['c'];
}
$cmodes=lf_communication_modes();
foreach($cmodes as $m){
$mode=['mode'=>$m, 'c'=>0, 'per'=>0];
foreach($rs as $a2){
if($a2['comm_mode']==$m){
$mode['c']+=(int)$a2['c'];
}
}
if($m==='SMS'){
$mode['c']+=$res['sms_count'];
}
if($m==='Email'){
$mode['c']+=$res['email_count'];
}
$mode['per']=$res['cmodes']['total']>0?round(($mode['c']*100)/$res['cmodes']['total']):0;
$res['cmodes']['modes'][]=$mode;
}
return $res;
}
function applicationFunnel(){
$grp="CASE
WHEN grp='Education Portals_API' THEN 'Education Portals'
WHEN grp='Re-Marketing' OR grp='Media_Content_Data Marketing' THEN 'Media_Content_Data Marketing'
ELSE grp
END";
$res=[
'sources'=>key_val_array($this->db->select("DISTINCT $grp grp")->get_where("master_utm_sources")->result_array(), 'grp', 'grp')
];
$this->set_search_cond();
//$this->db->where("A.step_completed>", 1);
$f="COUNT(IF(A.step_completed>=0, 1, NULL)) total,
COUNT(IF(A.step2date IS NOT NULL OR A.step3date IS NOT NULL, 1, NULL)) paid,
COUNT(IF(A.suat_status='1' AND U.verified_by='OFFLINE', 1, NULL)) offline_paid,
COUNT(IF(A.payment_status='0' and A.pay_trans_id>0, 1, NULL)) payment_initiated";
$rs=$this->db->select($f, false)->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->get()->row_array();
$res['total']=(int)$rs['total'];
$res['paid']=(int)$rs['paid'];
$res['offline_paid']=(int)$rs['offline_paid'];
$res['non_paid']=$res['total']-$res['paid'];
$res['payment_initiated']=(int)$rs['payment_initiated'];
$res['payment_not_initiated']=$res['non_paid']-$res['payment_initiated'];
$this->set_search_cond();
$this->db->where("A.suat_status", '1');
$rs=$this->db->select("$grp grp, count(1) n")
->from("applications A")
->join("applicants U", "A.applicant_id=U.id")
->join("master_utm_sources S", "U.utm_source_id=S.id")
->group_by($grp)
->get()
->result_array();
$rs=key_val_array($rs, 'grp', 'n');
$res['counts']=[];
foreach($res['sources'] as $id=>$lbl){
$res['counts'][$id]=(int)$rs[$id];
}
return $res;
}
function leadFunnel(){
$res=[];
$this->set_search_cond();
$f="COUNT(1) total,
COUNT(IF(A.step3date IS NOT NULL, 1, NULL)) susat_regd,
COUNT(IF(A.step3date IS NOT NULL AND A.step4date IS NOT NULL, 1, NULL)) total_appeared,
COUNT(IF(A.step3date IS NOT NULL AND A.step4date IS NOT NULL AND A.step5date IS NOT NULL, 1, NULL)) admission_offered,
COUNT(IF(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.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,
COUNT(IF(PSS.DESCR1='Deposit Fee Paid', 1, NULL)) deposit_fee_paid,
COUNT(IF(PSS.status='ADM_FINAL', 1, NULL)) adm_final";
$rs=$this->db->select($f, false)
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->get()
->row_array();
$res['total']=(int)$rs['total'];
$res['susat_regd']=(int)$rs['susat_regd'];
$res['total_appeared']=(int)$rs['total_appeared'];
$res['admission_offered']=(int)$rs['admission_offered'];
$res['contract_signed']=(int)$rs['contract_signed'];
$res['admitted']=(int)$rs['admitted'];
$res['adm_final']=(int)$rs['adm_final'];
return $res;
}
function leadTimeSlot($paid=false){
$res=['monthWise'=>[], 'dayWise'=>[], 'hrWise'=>[]];
$months=['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
$this->set_search_cond();
if($paid){
$this->db->where("A.suat_status", 1);
}
$rs=$this->db->select("DATE_FORMAT(A.created, '%b') mon, COUNT(1) n")->from("applications A")->join("applicants U", "U.id=A.applicant_id")->group_by("mon")->get()->result_array();
$rs=key_val_array($rs, 'mon', 'n');
foreach($months as $lbl){
$res['monthWise'][]=['lbl'=>$lbl, 'c'=>(int)$rs[$lbl]];
}
$days=['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
$this->set_search_cond();
if($paid){
$this->db->where("A.suat_status", 1);
}
$rs=$this->db->select("DATE_FORMAT(A.created, '%W') day, COUNT(1) n")->from("applications A")->join("applicants U", "U.id=A.applicant_id")->group_by("day")->get()->result_array();
$rs=key_val_array($rs, 'day', 'n');
foreach($days as $lbl){
$res['dayWise'][$lbl]=(int)$rs[$lbl];
}
$hrs=['12 AM', '01 AM', '02 AM', '03 AM', '04 AM', '05 AM', '06 AM', '07 AM', '08 AM', '09 AM', '10 AM', '11 AM',
'12 PM', '01 PM', '02 PM', '03 PM', '04 PM', '05 PM', '06 PM', '07 PM', '08 PM', '09 PM', '10 PM', '11 PM'];
$this->set_search_cond();
if($paid){
$this->db->where("A.suat_status", 1);
}
$rs=$this->db->select("DATE_FORMAT(A.created, '%h %p') hr, COUNT(1) n")->from("applications A")->join("applicants U", "U.id=A.applicant_id")->group_by("hr")->get()->result_array();
$rs=key_val_array($rs, 'hr', 'n');
foreach($hrs as $lbl){
$res['hrWise'][]=['lbl'=>$lbl, 'c'=>(int)$rs[$lbl]];
}
$this->set_search_cond();
if($paid){
$this->db->where("A.suat_status", 1);
}
$rs=$this->db->select("DATE_FORMAT(A.created, '%W') day, DATE_FORMAT(A.created, '%h %p') hr, COUNT(1) c")
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->group_by(["day", "hr"])->get()->result_array();
$res['dayHrWise']=$rs;
$res['hrs']=$hrs;
$res['days']=$days;
return $res;
}
function genderWiseLeads(){
$this->set_search_cond();
$this->db->where("A.step_completed>", 1);
$rs=$this->db->select("IF(U.gender='', 'N/A', U.gender) gender, COUNT(1) no_of_leads")
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->group_by("U.gender")
->get()
->result_array();
foreach($rs as &$r){
$r['no_of_leads']=(int)$r['no_of_leads'];
}
return $rs;
}
function ageWiseLeads(){
$this->set_search_cond();
$this->db->where("A.step_completed>=", 1);
$agec="DATE_FORMAT(FROM_DAYS(DATEDIFF(now(), U.dob)), '%Y')+0";
$rs=$this->db->select("$agec age, COUNT(1) no_of_leads")
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->group_by($agec)
->order_by("age")
->get()
->result_array();
$res=[15=>0, 20=>0, 25=>0, 30=>0, 35=>0, 40=>0, 45=>0, 50=>0, 55=>0, 60=>0];
foreach($rs as $r){
$age=(int)$r['age'];
$no_of_leads=(int)$r['no_of_leads'];
if($age>=15 && $age<20){
$res[15]+=$no_of_leads;
}
if($age>=20 && $age<25){
$res[20]+=$no_of_leads;
}
if($age>=25 && $age<30){
$res[25]+=$no_of_leads;
}
if($age>=30 && $age<35){
$res[30]+=$no_of_leads;
}
if($age>=35 && $age<40){
$res[35]+=$no_of_leads;
}
if($age>=40 && $age<45){
$res[40]+=$no_of_leads;
}
if($age>=45 && $age<50){
$res[45]+=$no_of_leads;
}
if($age>=50 && $age<55){
$res[50]+=$no_of_leads;
}
if($age>=55 && $age<60){
$res[55]+=$no_of_leads;
}
if($age>=60){
$res[60]+=$no_of_leads;
}
}
return $res;
}
}
//End of file
Kontol Shell Bypass