ikan Uploader :
Directory : /var/www/html/shardahospital.org/shardalms/lms/lms/api/application/models/
Upload File : |
| Current File : /var/www/html/shardahospital.org/shardalms/lms/lms/api/application/models/Reportsnew_model.php |
<?php
class Reportsnew_model extends CI_Model{
function totalLeadsCount(){
$qs=trim_array($this->input->post());
if($qs['from_date']){
$this->db->where("created>=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("created<=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$n=(int)$this->db->select("count(1) n")->from("applications")->get()->row("n");
return $n;
}
function appVerifiedCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="step1_date";
$c2="step1_date";
}else{
$c1="created";
$c2="created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["step1_by"=>$user_id]);
$n=(int)$this->db->select("count(1) n")->from("applications")->get()->row("n");
return $n;
}
function step2Count($user_id){
$this->common->set_data_set_cond(0,0,1, $user_id);
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="A.created";
$c2="A.step2date";
}else{
$c1="A.created";
$c2="A.created";
}
$this->db->where(['A.step_completed'=>2]);
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$n=(int)$this->db->select("count(1) n")->from("applications A")->join("applicants U ", "A.applicant_id=U.id")->get()->row("n");
return $n;
}
function step2PlusCount($user_id){
$this->common->set_data_set_cond(0,0,1, $user_id);
$qs=trim_array($this->input->post());
$this->db->where(['A.step_completed>='=>2]);
if($qs['datetype']==='Action'){
$c1="A.step2date";
$c2="A.step3date";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$n=(int)$this->db->select("count(1) n")->from("applications A")->join("applicants U ", "A.applicant_id=U.id")->get()->row("n");
return $n;
}
function paidLeadsCount($user_id){
$this->common->set_data_set_cond(0,0,1, $user_id);
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="A.payment_date";
$c2="A.payment_date";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(['A.payment_status'=>1]);
$n=(int)$this->db->select("count(1) n")->from("applications A")->join("applicants U ", "A.applicant_id=U.id")->get()->row("n");
return $n;
}
function admOfferedCount($user_id){
$qs=trim_array($this->input->post());
$this->common->set_data_set_cond(0,0,1, $user_id);
$this->db->where(['PSS.status'=>'ADM_OFFERED']);
if($qs['datetype']==='Action'){
$c1="A.created";
$c2="A.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$n=(int)$this->db->select("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")
->get()->row("n");
return $n;
}
function admFinalCount($user_id){
$qs=trim_array($this->input->post());
$this->common->set_data_set_cond(0,0,1, $user_id);
$this->db->where(['PSS.status'=>'ADM_FINAL']);
if($qs['datetype']==='Action'){
$c1="A.created";
$c2="A.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$n=(int)$this->db->select("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")
->get()->row("n");
return $n;
}
function followUpCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="AR.created";
$c2="AR.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["AR.created_by"=>$user_id]);
$n=(int)$this->db->select("count(1) n")->from("applications_remarks AR")->join("applications A", "A.id=AR.application_id")->get()->row("n");
//echo $this->db->last_query();
//die;
return $n;
}
function followUpLeadsCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="AR.created";
$c2="AR.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["AR.created_by"=>$user_id]);
$n=(int)$this->db->select("count(DISTINCT AR.application_id) n")
->from("applications_remarks AR")
->join("applications A", "A.id=AR.application_id")
->join("applicants U", "U.id=A.applicant_id")
->get()->row("n");
return $n;
}
function upcomingFollowupLeadsCount($user_id){
$this->common->set_data_set_cond(0,0,1, $user_id);
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="A.created";
$c2="A.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where("A.next_followup>=", get_date(time(), '', 'Y-m-d'));
$n=(int)$this->db->select("count(1) n")->from("applications A")->join("applicants U ", "A.applicant_id=U.id")->get()->row("n");
return $n;
}
function overdueFollowupLeadsCount($user_id){
$this->common->set_data_set_cond(0,0,1, $user_id);
$qs=trim_array($this->input->post());
if($qs['from_date']){
$this->db->where("A.created>=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("A.created<=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where("A.next_followup<", get_date(time(), '', 'Y-m-d'));
$n=(int)$this->db->select("count(1) n")->from("applications A")->join("applicants U ", "A.applicant_id=U.id")->get()->row("n");
return $n;
}
function emailsSentCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="E.created";
$c2="E.created";
}else{
$c1="U.created";
$c2="U.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["E.created_by"=>$user_id]);
$n=(int)$this->db->select("count(1) n")->from("sent_emails E")->join("applicants U", "U.email=E.to_email")->get()->row("n");
return $n;
}
function emailsSentLeadsCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="E.created";
$c2="E.created";
}else{
$c1="U.created";
$c2="U.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["E.created_by"=>$user_id]);
$n=(int)$this->db->select("count(DISTINCT E.to_email) n")->from("sent_emails E")->join("applicants U", "U.email=E.to_email")->get()->row("n");
return $n;
}
function smsSentCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="S.created";
$c2="S.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["S.created_by"=>$user_id]);
$n=(int)$this->db->select("count(1) n")->from("followup_sent_sms S")->join("applications A", "A.id=S.application_id")->get()->row("n");
return $n;
}
function smsSentLeadsCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="S.created";
$c2="S.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["S.created_by"=>$user_id]);
$n=(int)$this->db->select("count(DISTINCT application_id) n")->from("followup_sent_sms S")->join("applications A", "A.id=S.application_id")->get()->row("n");
return $n;
}
function issuesResolvedCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="I.close_date";
$c2="I.close_date";
}else{
$c1="U.created";
$c2="U.created";
}
if(!$qs['from_date']){
$qs['from_date']="2001-01-01";
}
if(!$qs['to_date']){
$qs['to_date']="2050-01-01";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["I.updated_by"=>$user_id, "I.status"=>3]);
$n=(int)$this->db->select("count(1) n")->from("issues_reported I")->join("applicants U", "U.id=I.applicant_id")->get()->row("n");
return $n;
}
function assignedCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="I.created";
$c2="I.created";
}else{
$c1="A.created";
$c2="A.created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["I.assigned_to"=>$user_id]);
$n=(int)$this->db->select("count(DISTINCT application_id) n")->from("application_assign_history I")->join("applications A", "A.id=I.application_id")->get()->row("n");
return $n;
}
function deadCount($user_id){
$qs=trim_array($this->input->post());
if($qs['datetype']==='Action'){
$c1="dead_on";
$c2="dead_on";
}else{
$c1="created";
$c2="created";
}
if($qs['from_date']){
$this->db->where("$c1 >=", get_date($qs['from_date'], '', 'Y-m-d'));
}
if($qs['to_date']){
$this->db->where("$c2 <=", get_date($qs['to_date'], '', 'Y-m-d 23:59:59'));
}
$this->db->where(["dead_by"=>$user_id, "isdead"=>"Y"]);
$n=(int)$this->db->select("count(1) n")->from("applications")->get()->row("n");
return $n;
}
function caller_leads_count($user_id, $from_date='', $to_date='', $datetype=''){
if($datetype==='Action'){
$c1="dh.created";
$c2="dh.created";
}else{
$c1="ds.lead_date_from";
$c2="ds.lead_date_to";
}
if($from_date){
$this->db->where("$c1 >= ", get_date($from_date, '', 'Y-m-d'));
}
if($to_date){
$this->db->where("$c2 <= ", get_date($to_date, '', 'Y-m-d 23:59:59'));
}
$n=(int)$this->db->select("SUM(dh.no_of_leads) n")
->from("data_set_split_dtl_history dh")
->join("leads_data_sets ds", "ds.id=dh.data_set_id")
->where("dh.user_id", $user_id)
->get()
->row("n");
return $n;
}
function counsellorReport($type='Counsellor', $byYouOnly=true){
$qs=trim_array($this->input->post());
//if(in_array(USER_TYPE, ['CONSULTANT_MANAGER', 'RO_MANAGER', 'CAMPUS_MANAGER', 'CALLER_MANAGER'])){
if($byYouOnly){
$users=$this->user->active_users(true);
$ids=[];
foreach($users as $u){
$ids[]=$u['id'];
}
$this->db->where_in("u.id", $ids);
}
if(strlen($qs['status'])){
$this->db->where(['u.status'=>$qs['status']]);
}else{
$this->db->where(['u.status'=>1]);
}
$this->db->where(['u.type!='=>'ADMIN']);
$this->db->where(['u.reporting_type'=>$type]);
$f="u.id, u.name, u.type, u.status, u.data_set_id, m.name manager";
$rs=$this->db->select($f)
->from("users u")
->join("users m", "m.id=u.parent_id", "LEFT")
->order_by("u.name")
->get()->result_array();
if($rs){
$totalLeads=$this->totalLeadsCount();
}
foreach($rs as &$r){
if($type==='Caller'){
$r['leads_count']=$this->caller_leads_count($r['id'], $qs['from_date'], $qs['to_date'], $qs['datetype']);
}else{
if($r['data_set_id']){
$data_sets=$this->user->user_data_sets($r, 0, $qs['from_date'], $qs['to_date']);
$r['leads_count']=(int)$data_sets[0]['leads_count'];
}else{
$r['leads_count']=$totalLeads;
}
}
$_GET['user_id']=$r['id'];
$_GET['from_date']=$qs['from_date'];
$_GET['to_date']=$qs['to_date'];
$r['dispositions']=$this->rp->callers_disp(false, $qs['datetype']);
$r['app_verified']=$this->appVerifiedCount($r['id']);
/* if($type==='Operation'){
}else{ */
$r['step2_count']=$this->step2Count($r['id']);
//$r['step2plus_count']=$this->step2PlusCount($r['id']);
$r['paid_count']=$this->paidLeadsCount($r['id']);
$r['adm_offered_count']=$this->admOfferedCount($r['id']);
$r['adm_final_count']=$this->admFinalCount($r['id']);
$r['followup_count']=$this->followUpCount($r['id']);
$r['followup_leads_count']=$this->followUpLeadsCount($r['id']);
$r['untouched_count']=$r['leads_count']-$r['followup_leads_count'];
if($r['untouched_count']<0){
$r['untouched_count']=0;
}
$r['upcoming_count']=$this->upcomingFollowupLeadsCount($r['id']);
$r['overdue_count']=$this->overdueFollowupLeadsCount($r['id']);
$r['emails_count']=$this->emailsSentCount($r['id']);
$r['emails_leads_count']=$this->emailsSentLeadsCount($r['id']);
$r['sms_count']=$this->smsSentCount($r['id']);
$r['sms_leads_count']=$this->smsSentLeadsCount($r['id']);
$r['issues_resolved_count']=$this->issuesResolvedCount($r['id']);
$r['assigned_count']=$this->assignedCount($r['id']);
$r['dead_count']=$this->deadCount($r['id']);
//}
}
return $rs;
}
/** */
function leads(){
$md=$this->common->master_data_for_leads();
$from=get_date($_REQUEST['from_date'], '', 'Y-m-d');
$to=get_date($_REQUEST['to_date'], '', 'Y-m-d 23:59:59');
if($from){
$this->db->where("a.created>=", $from);
}
if($to){
$this->db->where("a.created<=", $to);
}
$f="a.id, a.application_no, u.name, u.mob, u.email, a.step_completed, a.created, IF(a.isdead='Y', 'Dead', 'Not Dead') dead,
u.state_id, a.ac_id, a.discipline_id, a.program_id, a.plan_id";
$rs=$this->db->select($f)
->from("applications a")
->join("applicants u", "a.applicant_id=u.id")
->get()->result_array();
foreach($rs as &$r){
$r['state'] =$md['states'][$r['state_id']];
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program'] =$md['programs'][$r['program_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['created']=get_date($r['created'], '', 'd-M-Y');
}
return $rs;
}
function followUps(){
$md=$this->common->master_data_for_leads();
$from=get_date($_REQUEST['from_date'], '', 'Y-m-d');
$to=get_date($_REQUEST['to_date'], '', 'Y-m-d 23:59:59');
if($from){
$this->db->where("a.created>=", $from);
}
if($to){
$this->db->where("a.created<=", $to);
}
$f="a.id, a.application_no, u.name, u.mob, u.email, a.step_completed, a.created, IF(a.isdead='Y', 'Dead', 'Not Dead') dead,
u.state_id, a.ac_id, a.discipline_id, a.program_id, a.plan_id, COUNT(ar.id) followup_count";
$rs=$this->db->select($f)
->from("applications a")
->join("applicants u", "a.applicant_id=u.id")
->join("applications_remarks ar", "ar.application_id=a.id")
->where("a.last_followup_id>", 0)
->group_by("a.id")
->get()->result_array();
foreach($rs as &$r){
$r['state'] =$md['states'][$r['state_id']];
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program'] =$md['programs'][$r['program_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['created']=get_date($r['created'], '', 'd-M-Y');
$remarks=$this->db->select("lr.title remarks, ar.subremarks, ar.created, u.name user, u.type user_type")
->from("applications_remarks ar")
->join("master_lead_remarks lr", "lr.id=ar.remarks_id")
->join("users u", "ar.created_by=u.id")
->where("ar.application_id", $r['id'])
->order_by("ar.id", "DESC")
->limit(4)
->get()->result_array();
foreach($remarks as $i=>$a){
$n=$i+1;
$r['remarks'.$n]=$a['remarks'];
$r['subremarks'.$n]=$a['subremarks'];
$r['remarks_user'.$n]=$a['user'];
$r['remarks_created'.$n]=get_date($a['created'], '', 'd-M-Y');
$r['remarks_time'.$n]=get_date($a['created'], '', 'h:i A');
}
}
return $rs;
}
function assignedLeads(){
$md=$this->common->master_data_for_leads();
$from=get_date($_REQUEST['from_date'], '', 'Y-m-d');
$to=get_date($_REQUEST['to_date'], '', 'Y-m-d 23:59:59');
if($from){
$this->db->where("a.created>=", $from);
}
if($to){
$this->db->where("a.created<=", $to);
}
$f="a.id, a.application_no, u.name, u.mob, u.email, a.step_completed, a.created, IF(a.isdead='Y', 'Dead', 'Not Dead') dead,
u.state_id, a.ac_id, a.discipline_id, a.program_id, a.plan_id,,
u1.name assigned_to, u1.type assigned_user_type";
$rs=$this->db->select($f)
->from("applications a")
->join("applicants u", "a.applicant_id=u.id")
->join("users u1", "a.assigned_to=u1.id")
->join("users u2", "a.resolved_by=u2.id", "LEFT")
->get()->result_array();
foreach($rs as &$r){
$r['state'] =$md['states'][$r['state_id']];
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program'] =$md['programs'][$r['program_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['created']=get_date($r['created'], '', 'd-M-Y');
}
return $rs;
}
function resolvedLeads(){
$md=$this->common->master_data_for_leads();
$from=get_date($_REQUEST['from_date'], '', 'Y-m-d');
$to=get_date($_REQUEST['to_date'], '', 'Y-m-d 23:59:59');
if($from){
$this->db->where("a.created>=", $from);
}
if($to){
$this->db->where("a.created<=", $to);
}
$f="a.id, a.application_no, u.name, u.mob, u.email, a.step_completed, a.created, IF(a.isdead='Y', 'Dead', 'Not Dead') dead,
u.state_id, a.ac_id, a.discipline_id, a.program_id, a.plan_id, a.resolved_on,
u1.name resolved_by, u1.type resolved_by_user_type";
$rs=$this->db->select($f)
->from("applications a")
->join("applicants u", "a.applicant_id=u.id")
->join("users u1", "a.resolved_by=u1.id")
->get()->result_array();
foreach($rs as &$r){
$r['states'] =$md['state'][$r['state_id']];
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program'] =$md['programs'][$r['program_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['created']=get_date($r['created'], '', 'd-M-Y');
$r['resolved_on']=get_date($r['resolved_on'], '', 'd-M-Y');
}
return $rs;
}
function deadLeads(){
$md=$this->common->master_data_for_leads();
$from=get_date($_REQUEST['from_date'], '', 'Y-m-d');
$to=get_date($_REQUEST['to_date'], '', 'Y-m-d 23:59:59');
if($from){
$this->db->where("a.created>=", $from);
}
if($to){
$this->db->where("a.created<=", $to);
}
$f="a.id, a.application_no, u.name, u.mob, u.email, a.step_completed, a.created, IF(a.isdead='Y', 'Dead', 'Not Dead') dead,
u.state_id, a.ac_id, a.discipline_id, a.program_id, a.plan_id, a.dead_on,
u1.name dead_by, u1.type dead_by_user_type";
$rs=$this->db->select($f)
->from("applications a")
->join("applicants u", "a.applicant_id=u.id")
->join("users u1", "a.dead_by=u1.id")
->where("a.isdead", "Y")
->get()->result_array();
foreach($rs as &$r){
$r['states'] =$md['states'][$r['state_id']];
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program'] =$md['programs'][$r['program_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['created']=get_date($r['created'], '', 'd-M-Y');
$r['dead_on']=get_date($r['dead_on'], '', 'd-M-Y');
}
return $rs;
}
function sentEmailsLeads(){
$md=$this->common->master_data_for_leads();
$from=get_date($_REQUEST['from_date'], '', 'Y-m-d');
$to=get_date($_REQUEST['to_date'], '', 'Y-m-d 23:59:59');
if($from){
$this->db->where("a.created>=", $from);
}
if($to){
$this->db->where("a.created<=", $to);
}
$f="a.id, a.application_no, u.name, u.mob, u.email, a.step_completed, a.created, IF(a.isdead='Y', 'Dead', 'Not Dead') dead,
u.state_id, a.ac_id, a.discipline_id, a.program_id, a.plan_id, COUNT(se.id) email_count, MAX(se.id) last_email_id";
$rs=$this->db->select($f)
->from("applications a")
->join("applicants u", "a.applicant_id=u.id")
->join("sent_emails se", "se.to_email=u.email")
->group_by("a.id")
->get()->result_array();
foreach($rs as &$r){
$r['state'] =$md['states'][$r['state_id']];
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program'] =$md['programs'][$r['program_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['created']=get_date($r['created'], '', 'd-M-Y');
$lastEmail=$this->db->select("se.created, u.name user, u.type user_type")
->from("sent_emails se")
->join("users u", "se.created_by=u.id")
->where("se.id", $r['last_email_id'])
->get()->row_array();
$r['last_email_date']=get_date($lastEmail['created'], '', 'd-M-Y');
$r['last_email_time']=get_date($lastEmail['created'], '', 'h:i A');
$r['email_by']=$lastEmail['user'];
}
return $rs;
}
function leadsIssues(){
$md=$this->common->master_data_for_leads();
$issueStatusList=issueStatusLookup();
$from=get_date($_REQUEST['from_date'], '', 'Y-m-d');
$to=get_date($_REQUEST['to_date'], '', 'Y-m-d 23:59:59');
if($from){
$this->db->where("a.created>=", $from);
}
if($to){
$this->db->where("a.created<=", $to);
}
$f="a.id, a.application_no, u.name, u.mob, u.email, a.step_completed, a.created, IF(a.isdead='Y', 'Dead', 'Not Dead') dead,
u.state_id, a.ac_id, a.discipline_id, a.program_id, a.plan_id,
i.cat issue_cat, i.issue, i.created issue_date, i.updated issue_updated, i.status issue_status,
u1.name action_by, u1.type action_by_user_type";
$rs=$this->db->select($f)
->from("applications a")
->join("applicants u", "a.applicant_id=u.id")
->join("issues_reported i", "i.applicant_id=u.id")
->join("users u1", "i.updated_by=u1.id", "LEFT")
->where("i.parent_id", "0")
->get()->result_array();
foreach($rs as &$r){
$r['state'] =$md['states'][$r['state_id']];
$r['isd_code'] =$md['isd_codes'][$r['country_id']].'#';
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program'] =$md['programs'][$r['program_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['created']=get_date($r['created'], '', 'd-M-Y');
$r['issue_date']=get_date($r['issue_date'], '', 'd-M-Y');
$r['issue_updated']=get_date($r['issue_updated'], '', 'd-M-Y');
if($r['issue_status']<=1){
$r['action_by']='';
$r['issue_updated']='';
}
$r['issue_status']=$issueStatusList[$r['issue_status']];
}
return $rs;
}
/** */
function usersLoginHistiry($all=''){
$qs=trim_array($this->input->post());
if($qs['user_id']){
$this->db->where("UH.user_id", $qs['user_id']);
}
if($qs['from_date']){
$this->db->where("UH.login_time >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("UH.login_time <=", date('Y-m-d 23:59:59', strtotime($qs['to_date'])));
}
$this->db->select("UH.*, U.name, U.type")
->from("user_login_history UH")
->join("users U", "U.id=UH.user_id")
->order_by("UH.id", "DESC");
if($all){
$rs['result']=$this->db->get()->result_array();
}else{
$rs=$this->dba->pagedRows($qs['p'], 50, 5);
}
foreach($rs['result'] as &$r){
$r['login_time']=get_date($r['login_time'], true);
$r['logout_time']=get_date($r['logout_time'], true);
}
return $rs;
}
function usersActivities($all=''){
$qs=trim_array($this->input->post());
if($qs['user_id']){
$this->db->where("UH.user_id", $qs['user_id']);
}
if($qs['from_date']){
$this->db->where("UH.action_time >=", date('Y-m-d', strtotime($qs['from_date'])));
}
if($qs['to_date']){
$this->db->where("UH.action_time <=", date('Y-m-d 23:59:59', strtotime($qs['to_date'])));
}
$this->db->select("UH.*, U.name, U.type")
->from("user_activities UH")
->join("users U", "U.id=UH.user_id")
->order_by("UH.id", "DESC");
if($all){
$rs['result']=$this->db->get()->result_array();
}else{
$rs=$this->dba->pagedRows($qs['p'], 50, 5);
}
foreach($rs['result'] as &$r){
$r['action_time']=get_date($r['action_time'], true);
//$r['data']=json_decode($r['data'], true);
}
return $rs;
}
}
//End of file
Kontol Shell Bypass