ikan Uploader :
Directory : /var/www/html/shardahospital.org/lms/api/application/models/
Upload File : |
| Current File : /var/www/html/shardahospital.org/lms/api/application/models/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("A.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("A.state_id", $state_id);
if($lvOpt['cities'][$state_id]){
$lvOpt['cities'][$state_id][]=0;
$this->db->where_in("A.city_id", $lvOpt['cities'][$state_id]);
}
$this->db->group_end();
}
$this->db->group_end();
}else if($lvOpt['states']){
$this->db->where_in("A.state_id", $lvOpt['states']);
}
if($lvOpt['programs']){
$this->db->where_in("A.program_id", $lvOpt['programs']);
}
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'])));
}
}
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['queries']['today']=$dtl['queries'];
/** Old Leads */
$dtl=$this->today_yesterday_data('Yesterday');
$res['old_leads']=$dtl['leads_count'];
$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);
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")->from("applicants A")->get()->row_array();
$res['leads_count']=['total'=>$rs['total'], 'total_per'=>100];
$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("applicants A")->join("issues_reported IR", "A.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']=0;
$total['sum_total_lead_per']=100;
foreach($rs as $i=>$r){
if($i==$limit){
break;
}
$r['total_per']=100;
$total['sum_total_lead']=$total['sum_total_lead']+$r['total'];
$res['result'][]=$r;
}
if($total['sum_total_lead']>0){
foreach($res['result'] as &$t){
$t['total_per_c']=0;
if($total['sum_total_lead']>0){
$t['total_per_c']=number_format(($t['total']*100/$total['sum_total_lead']), 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", false)
->from("applicants A")
->join("master_utm_sources S", "A.utm_source_id=S.id")
->group_by("S.grp")
->get()->result_array();
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", false)
->from("applicants A")
->join("master_utm_sources S", "A.utm_source_id=S.id")
->group_by("A.utm_source_id")
->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, COUNT(1) total", false)
->from("applicants A")
->join("master_states S", "A.state_id=S.id")
->group_by("A.state_id")
->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", false)
->from("applicants A")
->group_by("A.program_id")
->get()->result_array();
if($rs){
foreach($rs as $i=>&$r){
$r['title']=$programs[$r['program_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("A.lpage_id, COUNT(1) total", false)
->from("applicants A")
->group_by("A.lpage_id")
->get()->result_array();
if($rs){
foreach($rs as $i=>&$r){
$r['title']=$lpages[$r['lpage_id']];
}
}
return $this->count_lead_total_per($rs, $limit);
}
/** Other Reports */
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("A.id, A.name, A.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=A.id")
->join("leads_data_sets DS", "A.data_set_id=DS.id", "LEFT")
->group_by("A.id")
->order_by("total", "DESC")
->get()->result_array();
return $rs;
}
/** 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.name, A.email, A.mob, A.lpage_id, A.state_id, P.title program")
->from("applicants A")
->join("master_programs P", "P.id=A.program_id")
->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(['A.email'=>$u]);
}else{
$this->db->where(['A.mob'=>$u]);
}
$res=$this->db->select("A.id, A.application_no system_id, A.name, A.email, A.mob, A.lpage_id, A.state_id, P.title program")
->from("applicants A")
->join("master_programs P", "P.id=A.program_id")
->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);
if($qs['k']){
$this->db->group_start()
->where('A.name', $qs['k'])
->or_where('A.email', $qs['k'])
->or_where('A.mob', $qs['k'])
->or_where('A.application_no', $qs['k'])
->group_end();
}
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.application_no, A.created leadCreated, P.title program, A.name, A.email, A.mob, A.city, A.state_id, A.lpage_id", FALSE)
->from("applicants A")
->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['lpage'] =$md['lpages'][$r['lpage_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['state_id'], $r['lpage_id']);
}
return $rs;
}
}
//End of file
Kontol Shell Bypass