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/React_model.php |
<?php
class React_model extends CI_Model {
function __construct() {
parent::__construct();
}
function leadsSearchCond(){
$qs=trimArray($this->input->post());
if($qs['k']){
$this->db->group_start()
->like('u.name', $qs['k'])
->or_like('u.email', $qs['k'])
->or_like('u.mob', $qs['k'])
->or_like('a.application_no', $qs['k'])
->group_end();
}
if($qs['emails']){
$this->db->where_in("u.email", $qs['emails']);
}
if($qs['steps']){
$this->db->where_in("a.step_completed", $qs['steps']);
}
if($qs['states']){
$this->db->where_in("u.state_id", $qs['states']);
}
if($qs['programs']){
$this->db->where_in("a.program_id", $qs['programs']);
}
if($qs['utm_groups']){
$this->db->where_in("us.grp", $qs['utm_groups']);
}
if(strlen($qs['followup_count'])){
$n=(int)$qs['followup_count'];
switch($qs['followup_count']){
case '1':
case '2':
case '3':
$this->db->where("a.followup_count_all", $n);
break;
case '1+':
case '2+':
case '3+':
$this->db->where("a.followup_count_all>=", $n);
break;
}
}
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['superbot_dispositions']){
$this->db->where_in("sr.disposition", $qs['superbot_dispositions']);
}
if($qs['verified_by']){
$this->db->where_in("u.verified_by", $qs['verified_by']);
}
if($qs['k']){
$this->db->group_start()
->like('u.name', $qs['k'])
->or_like('u.email', $qs['k'])
->or_like('u.mob', $qs['k'])
->or_like('a.application_no', $qs['k'])
->group_end();
}
if($qs['utm_source_ids']){
$this->db->where_in("u.utm_source_id", $qs['utm_source_ids']);
}
if($qs['ac_ids']){
$this->db->where_in("a.ac_id", $qs['ac_ids']);
}
if($qs['discipline_ids']){
$this->db->where_in("a.discipline_id", $qs['discipline_ids']);
}
if($qs['school_ids']){
$this->db->where_in("a.school_id", $qs['school_ids']);
}
if($qs['remarks_ids']){
$this->db->where_in("ar.remarks_id", $qs['remarks_ids']);
}
if($qs['isdead']){
$this->db->where("a.isdead", $qs['isdead']);
}
if($qs['lpage_ids']){
$this->db->where_in("u.lpage_id", $qs['lpage_ids']);
}
if($qs['valid_mob_only']==='Yes'){
$this->db->where("LENGTH(u.mob)=10 AND CAST(LEFT(u.mob, 1) AS UNSIGNED)>5", NULL, FALSE);
}
}
function leadsN($superbot_campaign_id='', $social_campaign_id=''){
$qs=trimArray($this->input->post());
$this->leadsSearchCond();
$f="a.id, a.application_no, a.step_completed, a.created, a.program_id, a.plan_id, a.followup_count_all,
a.isdead, u.name, u.email, u.mob, u.state_id, u.lpage_id, u.verified_by,
us.grp utm_group, us.title utm_source, mlr.title followup_remarks,
sr.disposition superbot_disposition";
$this->db->select($f)
->from("applications a")
->join("applicants u", "u.id=a.applicant_id")
->join("master_utm_sources us", "u.utm_source_id=us.id", "LEFT")
->join("applications_remarks ar", "ar.id=a.last_followup_id", "LEFT")
->join("master_lead_remarks mlr", "mlr.id=ar.remarks_id", "LEFT")
->join("superbot_response sr", "sr.id=u.superbot_resp_id", "LEFT")
->order_by("a.id", "desc")
->limit($qs['social']?50000:2000);
if(strlen($superbot_campaign_id)){
$this->db->select("sbl.created sent_to_superbot_on");
$this->db->join("superbot_campaign_leads sbl", "sbl.app_id=a.id")->where("sbl.campaign_id", $superbot_campaign_id);
}
if(strlen($social_campaign_id)){
$this->db->select("cl.created initiated_on, cl.sent_on, cl.opened_on");
$this->db->join("social_campaign_leads cl", "cl.app_id=a.id")->where("cl.campaign_id", $social_campaign_id);
if($qs['sent_status']==='Pending'){
$this->db->where("cl.sent_on", null);
}
if($qs['sent_status']==='Sent'){
$this->db->where("cl.sent_on!=", null);
}
if($qs['sent_status']==='Opened'){
$this->db->where("cl.opened_on!=", null);
}
}
$rs=$this->db->get()->result_array();
if($rs){
$md=$this->common->master_data_for_leads();
foreach($rs as &$r){
$r['step_completed']=(int)$r['step_completed'];
$r['followup_count_all']=(int)$r['followup_count_all'];
$r['state']=$md['states'][$r['state_id']];
$r['program']=$md['programs'][$r['program_id']];
$r['plan']=$md['plans'][$r['plan_id']];
//$r['lpage']=$md['lpages'][$r['lpage_id']];
}
return $rs;
}else{
return [];
}
}
function superbotCampaigns(){
$qs=trimArray($this->input->get());
if(strlen($qs['k'])){
$this->db->group_start();
$this->db->like("name", $qs['k']);
$this->db->group_end();
}
if(strlen($qs['status'])){
$this->db->where("status", $qs['status']);
}
return $this->db->order_by("id", "desc")->get_where("superbot_campaigns", ['type'=>'PastLeads'])->result_array();
}
function deleteSuperbotCampaign($id){
$this->db->db_debug=FALSE;
$this->db->delete("superbot_campaigns", ['id'=>$id]);
return $this->db->affected_rows()>0;
}
function superbotRespondedLeads($all=false){
$qs=trimArray($this->input->post());
$this->leadsSearchCond();
$f="a.id, a.application_no, a.step_completed, a.created, a.program_id, a.plan_id, a.followup_count_all,
u.name, u.email, u.mob, u.state_id, us.grp utm_group, us.title utm_source,
sr.disposition superbot_disposition, sr.call_recording_url, sr.call_duration, sr.time call_time";
$this->db->select($f)
->from("superbot_response sr")
->join("applicants u", "u.superbot_resp_id=sr.id")
->join("applications a", "a.applicant_id=u.id")
->join("master_utm_sources us", "u.utm_source_id=us.id", "LEFT")
->order_by("sr.id", "desc");
if($all){
$rs['data']=$this->db->get()->result_array();
}else{
$rs=$this->dba->pagedRowsNew($qs['p'], $qs['ps']);
}
$md=$this->common->master_data_for_leads();
foreach($rs['data'] as &$r){
$r['step_completed']=(int)$r['step_completed'];
$r['followup_count_all']=(int)$r['followup_count_all'];
$r['state']=$md['states'][$r['state_id']];
$r['program']=$md['programs'][$r['program_id']];
$r['plan']=$md['plans'][$r['plan_id']];
}
return $rs;
}
/** */
function socialCampaigns(){
$qs=trimArray($this->input->get());
if(strlen($qs['k'])){
$this->db->group_start();
$this->db->like("name", $qs['k']);
$this->db->group_end();
}
if(strlen($qs['status'])){
$this->db->where("status", $qs['status']);
}
$rs=$this->db->select("c.*, t.name template")->order_by("c.id", "desc")->from("social_campaigns c")->join("social_templates t", "t.id=c.template_id")->where(['c.type'=>$qs['type']])->get()->result_array();
$rs=$rs?$rs:[];
foreach($rs as &$r){
$r['leads_filter']=$r['leads_filter']?unserialize($r['leads_filter']):null;
}
return $rs;
}
function deleteSocialCampaign($id){
$this->db->db_debug=FALSE;
$this->db->delete("social_campaigns", ['id'=>$id]);
return $this->db->affected_rows()>0;
}
function socialTemplates(){
$qs=trimArray($this->input->get());
if(strlen($qs['k'])){
$this->db->group_start();
$this->db->like("st.name", $qs['k']);
$this->db->group_end();
}
if(strlen($qs['status'])){
$this->db->where("st.status", $qs['status']);
}
$rs=$this->db->select("st.*, f.file_name media_file_name, f.is_image is_media_image, f.is_pdf is_media_pdf")
->from("social_templates st")
->join("files_new f", "st.media_file_id=f.id", "left")
->where(['st.type'=>$qs['type']])
->order_by("st.id", "desc")
->get()
->result_array();
$rs=$rs?$rs:[];
foreach($rs as &$r){
$r['media_file_url']=$r['media_file_name']?UP_URLF.'files_new/'.$r['media_file_name']:'';
$r['is_media_image']=(int)$r['is_media_image'];
$r['is_media_pdf']=(int)$r['is_media_pdf'];
}
return $rs;
}
function deleteSocialTemplate($id){
$this->db->db_debug=FALSE;
$this->db->delete("social_templates", ['id'=>$id]);
return $this->db->affected_rows()>0;
}
/** */
function multisourceLeads($all=false){
$qs=trimArray($this->input->post());
if(ENVIRONMENT=='development'){
$this->db->cache_on();
}
$this->leadsSearchCond();
$f="SQL_CALC_FOUND_ROWS a.id, a.application_no, a.step_completed, a.created, a.program_id, a.plan_id, a.followup_count_all,
u.name, u.email, u.mob, u.state_id, u.lpage_id, us.grp utm_group, us.title utm_source, count(rt.id) no_of_times_tried,
mlr.title followup_title, ar.subremarks, ar.feedback";
$this->db->select($f, false)
->from("multi_registration_tried rt")
->join("applicants u", "u.mob=rt.mob")
->join("applications a", "a.applicant_id=u.id")
->join("master_utm_sources us", "u.utm_source_id=us.id", "LEFT")
->join("applications_remarks ar", "ar.id=a.last_followup_id", "LEFT")
->join("master_lead_remarks mlr", "mlr.id=ar.remarks_id", "LEFT")
->group_by("rt.mob");
//->group_by("a.id")
//->order_by("a.id", "desc");
if($all){
$rs['data']=$this->db->get()->result_array();
}else{
$rs=$this->dba->pagedRowsNew($qs['p'], $qs['ps']);
}
if(ENVIRONMENT=='development'){
$this->db->cache_off();
}
$md=$this->common->master_data_for_leads();
foreach($rs['data'] as &$r){
$r['no_of_times_tried']=((int)$r['no_of_times_tried'])+1;
$r['step_completed']=(int)$r['step_completed'];
$r['followup_count_all']=(int)$r['followup_count_all'];
$r['state']=$md['states'][$r['state_id']];
$r['program']=$md['programs'][$r['program_id']];
$r['plan']=$md['plans'][$r['plan_id']];
$r['lpage']=$md['lpages'][$r['lpage_id']];
$r['login_url']=generate_login_url($r['lpage'], $r['mob']);
}
return $rs;
}
function multisourceLeadsReport(){
if(ENVIRONMENT=='development'){
$this->db->cache_on();
}
$f="u.utm_source_id, u.utm_source, count(1) no_of_leads, count(DISTINCT rt.mob) secondary_count";
$this->db->select($f, false)
->from("applications a")
->join("applicants u", "u.id=a.applicant_id")
->join("multi_registration_tried rt", "u.mob=rt.mob")
->group_by("u.utm_source_id");
$result=$this->db->get()->result_array();
foreach($result as &$r){
$r['no_of_leads']=(int)$r['no_of_leads'];
$r['secondary_count']=(int)$r['secondary_count'];
$r['other_utms']=$this->db->select("rt.utm_source, COUNT(1) records_count", false)
->from("applications a")
->join("applicants u", "u.id=a.applicant_id")
->join("multi_registration_tried rt", "u.mob=rt.mob")
->where("u.utm_source_id", $r['utm_source_id'])
->group_by("rt.utm_source")
->get()
->result_array();
foreach($r['other_utms'] as &$r1){
$r1['records_count']=(int)$r1['records_count'];
}
/* usort($r['other_utms'], function($a, $b) {
return strcmp(strtolower($a['utm_source']), strtolower($b['utm_source']));
}); */
}
if(ENVIRONMENT=='development'){
$this->db->cache_off();
}
return $result;
}
}
//End of file
Kontol Shell Bypass