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_live.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 23:59:59', strtotime($qs['to_date'])));
}
//$this->db->where("A.step_completed!=", '0');
}
function scoreBoard(){
$res=[];
$this->set_search_cond();
$f="COUNT(1) 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, COUNT(IF(A.followup_count_all_paid>0, 1, NULL)) total_followups_paid";
$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['api_leads'])-$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(1) 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=['(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']=[];
$this->set_search_cond();
$fs = "
COUNT(A.id) total,
COUNT(A.id) step_1,
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
";
$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 AND 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.step2date IS NOT NULL AND A.step3date IS NOT NULL, 1, NULL)) susat_regd,
COUNT(IF(A.step2date IS NOT NULL AND A.step3date IS NOT NULL AND A.step4date IS NOT NULL, 1, NULL)) total_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,
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.payment_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.payment_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.payment_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.payment_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