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/Leads_model.php |
<?php
class Leads_model extends CI_Model {
function set_search_cond($qs, $lvOpt){
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 && $qs['level']!='ASSIGNED_ME'){
$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['k']){
if(LEAD_RESTRICT){
$this->db->group_start()
->where('U.name', $qs['k'])
->or_where('U.email', $qs['k'])
->or_where('U.mob', $qs['k'])
->or_where('A.application_no', $qs['k'])
->group_end();
}else{
$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['campus_id']){
$this->db->where("A.campus_id", $qs['campus_id']);
}
if($qs['utm_group']){
$utmids=$this->db->select("id")->get_where("master_utm_sources", array('grp'=>$qs['utm_group']))->result_array();
$sids=[];
foreach($utmids as $r){
$sids[]=$r['id'];
}
if($sids){
$this->db->where_in("U.utm_source_id", $sids);
}
}
if($qs['utm_source_id']){
$this->db->where_in("U.utm_source_id", $qs['utm_source_id']);
}
if($qs['utm_campaign']){
$this->db->where_in("U.utm_campaign", $qs['utm_campaign']);
}
if($qs['utm_medium']){
$this->db->where_in("U.utm_medium", $qs['utm_medium']);
}
if($qs['step_completed']){
if($qs['level']!=='PHD'){
/* if(in_array('0', $qs['step_completed'])){
$this->db->where("A.step_completed", 0);
}else{
$this->db->where_in("A.step_completed", $qs['step_completed']);
} */
if(in_array('EX0', $qs['step_completed'])){
$this->db->where(["A.step_completed"=>0, "U.is_excel_upload"=>0]);
}elseif(in_array('API0', $qs['step_completed'])){
$this->db->where(["A.step_completed"=>0, "U.is_excel_upload"=>NULL]);
}else{
$this->db->where_in("A.step_completed", $qs['step_completed']);
}
}else{
$this->db->where_in("PF.step_completed", $qs['step_completed']);
}
}else{
//$this->db->where("A.step_completed!=", 0);
}
if(strlen($qs['method'])){
if($qs['method']==='01'){
$this->db->where_in("U.is_excel_upload", [0,1]);
}
if($qs['method']==='0'){
$this->db->where("U.is_excel_upload", 0);
}
if($qs['method']==='1'){
$this->db->where("U.is_excel_upload", 1);
}
if($qs['method']==='NULL'){
$this->db->where("U.is_excel_upload", NULL);
}
}
if($qs['payStatusMode']=='Paid'){
$this->db->where("A.payment_status", 1);
}elseif($qs['payStatusMode']=='OnlinePaid'){
$this->db->where("A.payment_mode", 'ONLINE');
}elseif($qs['payStatusMode']=='CashPaid'){
$this->db->where("A.payment_mode", 'CASH');
}elseif($qs['payStatusMode']=='NotPaid'){
$this->db->where("A.payment_status", 0);
}elseif($qs['payStatusMode']=='PaidFromApp'){
$this->db->where("PTRANS.from_mob_app", 1);
}elseif($qs['payStatusMode']=='Step1Paid'){
$this->db->where(['A.step1_paid'=>1]);
}
if($qs['paid_amt']){
$this->db->where("(A.fee-A.coupan_amt)=", $qs['paid_amt']);
}
if(!LEAD_RESTRICT){
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'])));
}
}
if($qs['remarks_id']){
/* $rids=array_map('intval', $qs['remarks_id']);
asort($rids);
$rids=implode("|", $rids);
$cond="CONCAT(',', A.remarks_ids , ',') REGEXP ".'",('.$rids.'),"';
$this->db->where($cond, NULL, FALSE); */
$this->db->where_in("AR.remarks_id", $qs['remarks_id']);
if($qs['subremarks']){
$this->db->where_in("AR.subremarks", $qs['subremarks']);
}
}
if($qs['type_id']){
$this->db->where("P.type_id", $qs['type_id']);
}
if($qs['ac_id']){
$this->db->where_in("P.ac_id", $qs['ac_id']);
}
if($qs['discipline_id']){
$this->db->where_in("P.discipline_id", $qs['discipline_id']);
}
if($qs['program_id']){
$this->db->where_in("P.id", $qs['program_id']);
}
if($qs['plan_id']){
$this->db->where_in("A.plan_id", $qs['plan_id']);
}
if($qs['state_id']){
$this->db->where_in("U.state_id", $qs['state_id']);
}
if($qs['city_id']){
$this->db->where_in("U.city_id", $qs['city_id']);
}
if($qs['lpage_id']){
$this->db->where_in("U.lpage_id", $qs['lpage_id']);
}
if($qs['ps_app_status']){
$this->db->where_in("PSS.DESCR1", $qs['ps_app_status']);
}
if($qs['ps_status']){
if($qs['ps_status']==='PAID'){
$this->db->where("PSS_PAID.EMPLID IS NOT NULL", null, false);
}else{
$this->db->where("PSS.status", $qs['ps_status']);
}
}
if($qs['isdead']){
$this->db->where("A.isdead", $qs['isdead']);
}
if(strlen($qs['suat_status'])){
if($qs['suat_status']==='NULL'){
$this->db->where("A.suat_status", NULL);
}else{
$this->db->where("A.suat_status", $qs['suat_status']);
}
}
/* if($qs['level']!=='PHD' && $qs['level']!=='CALLER_LEADS'){
$this->db->where("U.is_phd", 0);
}
if($qs['level']!=='MEDICAL' && $qs['level']!=='CALLER_LEADS'){
$this->db->where("U.is_medical", 0);
} */
if($qs['med_phd']==='1'){
$this->db->where("U.is_medical", 1);
}elseif($qs['med_phd']==='2'){
$this->db->where("U.is_phd", 1);
}elseif($qs['med_phd']==='4'){
$this->db->where(["U.is_medical"=>0, "U.is_phd"=>0]);
}elseif($qs['med_phd']==='3'){
$this->db->where(["U.is_phd"=>0]);
}
if($qs['online_offline']==='Online'){
$this->db->where(['U.verified_by!='=>'OFFLINE']);
}
if($qs['online_offline']==='Offline'){
$this->db->where(['U.verified_by='=>'OFFLINE']);
}
if($qs['followup_flg']=='Untouched'){
$this->db->where("A.last_followup_id", 0);
}
if($qs['followup_flg']=='Touched'){
$this->db->where("A.last_followup_id>",0);
}
if($qs['followup_flg']=='TouchedToday'){
$this->db->where("DATE(AR.created)=", date("Y-m-d"));
}
if($qs['followup_flg']=='TouchedYesterday'){
$this->db->where("DATE(AR.created)=", date("Y-m-d", strtotime("-1 days")));
}
if($qs['followup_flg']=='Upcoming'){
$this->db->where("A.next_followup>=", date("Y-m-d"));
}
if($qs['followup_flg']=='UpcomingToday'){
$this->db->where("A.next_followup=", date("Y-m-d"));
}
if($qs['followup_flg']=='Overdue'){
$this->db->where("A.next_followup<", date("Y-m-d"));
}
if($qs['no_of_visits']){
$a=substr($qs['no_of_visits'], -1);
$n=replace_non_digits($qs['no_of_visits']);
$this->db->where("U.no_of_visits".($a==='+'?'>=':($a==='-'?'<=':'')), $n);
}
if($qs['followup']=='Y'){
$this->db->where("A.last_followup_id>", 0);
}
if($qs['followup']=='N'){
$this->db->where("A.last_followup_id", 0);
}
if($qs['followup']=='PY'){
$this->db->where("A.followup_count_all_paid>", 0);
}
if($qs['followup']=='PN'){
$this->db->where("A.followup_count_all_paid", 0);
}
if(strlen($qs['followup_count_tab'])){
$qs['followup_count']=str_replace('p', '+', $qs['followup_count_tab']);
}
$followupCountKey="A.followup_count_all";
if($qs['followup_count_type']==='Paid'){
$followupCountKey="A.followup_count_all_paid";
}
if(strlen($qs['followup_count'])){
$n=(int)$qs['followup_count'];
switch($qs['followup_count']){
case '1':
case '2':
case '3':
$this->db->where("$followupCountKey", $n);
break;
case '1+':
case '2+':
case '3+':
$this->db->where("$followupCountKey >= ", $n);
break;
}
}
if($qs['only_other_cities']==='Y'){
$this->db->where("U.city_id", '-1');
}
if($qs['is_campus_offline']){
$this->db->where(['U.is_campus_offline='=>$qs['is_campus_offline']]);
}
if($qs['superbot_disposition']){
if($qs['superbot_disposition']==='all'){
$this->db->where("U.superbot_resp_id!=", null);
}else{
$this->db->where(['SR.disposition='=>$qs['superbot_disposition']]);
}
}
if($qs['level']){
switch($qs['level']){
case 'MEDICAL':
$this->db->where("U.is_medical", 1);
break;
case 'PHD':
$this->db->where("U.is_phd", 1);
break;
case 'ONLINE':
$this->db->where(['U.verified_by!='=>'OFFLINE']);
break;
case 'OFFLINE':
$this->db->where(['U.verified_by='=>'OFFLINE']);
break;
case 'CAMPUS_OFFLINE':
$this->db->where(['U.is_campus_offline='=>'Y']);
break;
case 'STEP1':
$this->db->where(['A.step_completed'=>1, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'STEP2':
$this->db->where(['A.step_completed'=>2, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'STEP1_PAID':
$this->db->where(['A.step_completed'=>1, 'A.payment_status'=>1, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'STEP3':
$this->db->where(['A.step_completed'=>3, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'STEP4':
$this->db->where(['A.step_completed'=>4, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'STEP5':
$this->db->where(['A.step_completed'=>5, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'STEP6':
$this->db->where(['A.step_completed'=>6, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'STEP7':
$this->db->where(['A.step_completed'=>7, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'PAID':
$this->db->where(['A.payment_status'=>1, "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'ADM_OFFERED':
$this->db->where(['PSS.status'=>'ADM_OFFERED', "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'ADM_FINAL':
$this->db->where(['PSS.status'=>'ADM_FINAL', "U.is_medical"=>0, "U.is_phd"=>0]);
break;
case 'ASSIGNED_ME':
$this->db->where(['A.assigned_to'=>USER_ID]);
break;
case 'RESOLVED':
$this->db->where(['A.isresolved'=>'Y']);
break;
case 'RESOLVED_ME':
$this->db->where(['A.resolved_by'=>USER_ID]);
break;
case 'PAYMENT_FAIL':
$this->db->where(['PTRANS.payment_status'=>'FAIL', 'A.payment_status'=>0]);
break;
}
}
}
function lists(){
$qs=trim_array($_REQUEST);
$lvOpt=$this->dataset->detail(0, USER_ID);
/* if($lvOpt['expiry_date'] && strtotime($lvOpt['expiry_date'])<strtotime(date('Y-m-d'))){
return [];
} */
//SQL_CALC_FOUND_ROWS
$this->set_search_cond($qs, $lvOpt);
$f="A.id, A.campus_id, A.applicant_id, A.application_no, A.created leadCreated, A.updated leadUpdated, A.plan_id, A.followup_by,
A.followup_count, A.followup_count_all, A.followup_count_all_paid, A.created_by, A.isdead, (A.fee-A.coupan_amt) paid_amt,
A.assigned_to, A.isresolved, A.resolved_by, A.resolved_on, A.suat_status, A.slot_booking_date, A.suat_score, A.next_followup,IF(A.admission_status IS NULL, 'NOT ADMITTED', 'ADMITTED') admission_status,
P.title program, P.ac_id, P.type_id, P.discipline_id, P.school_id,
U.is_phd, U.name, U.email, U.mob, U.city, U.per_12th, U.newsletter_subscribed, U.is_campus_offline,U.country, U.address, U.region,
U.pincode, U.verified_by, U.state_id, U.lpage_id, U.utm_source_id, U.utm_source, U.process_by, U.lead_source_id, U.lead_source_remarks, U.no_of_visits,
A.payment_status, A.payment_mode, A.payment_date, A.cash_order_id, PTRANS.pay_order_id, PTRANS.txn_id, PTRANS.txn_status,
PSS.ADM_APPL_NBR ps_app_no, PSS.DESCR1 ps_app_status, PSS.status ps_status, IF(PSS_PAID.EMPLID IS NULL, 'NOT_PAID', 'PAID') ps_paid_status,
AL.program_name api_program, AL.plan_name api_plan, SR.disposition superbot_disposition, CL.code, CL.title AS student_country,CL.isd_code AS ISD_CODE, MS.id AS region_id, MS.title AS state_name, MS.code";
if($qs['remarks_id'] || $qs['followup_flg']=='TouchedToday' || $qs['followup_flg']=='TouchedYesterday' || strlen($qs['followup_count'])){
$f="SQL_CALC_FOUND_ROWS ".$f;
}
if($qs['level']!=='PHD'){
$f.=", A.step_completed, A.step2date, A.step3date, A.step4date, A.step5date, A.step6date, A.step7date";
}
$this->db->select($f, FALSE)
->from("applications A") //.(ENVIRONMENT=='development'?" USE INDEX(applicant_id)":"")
->join("applicants U", "U.id=A.applicant_id")
->join("master_programs P", "P.id=A.program_id", "LEFT")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->join("ps_paid_system_ids PSS_PAID", "PSS_PAID.EMPLID=A.application_no", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->join("api_leads AL", "AL.id=A.api_lead_id", "LEFT")
->join("master_countrys AS CL", "CL.code = U.country", "LEFT")
->join("master_states AS MS", "MS.id = U.state_id", "LEFT");
if($qs['level']==='PHD'){
$this->db->join("phd_app_form PF", "PF.app_id=A.id", "LEFT");
$this->db->select("PF.step_completed, PF.step1date, PF.step2date, PF.step3date, PF.step4date, PF.step5date");
}
if($qs['remarks_id'] || $qs['followup_flg']=='TouchedToday' || $qs['followup_flg']=='TouchedYesterday'){
$this->db->join("applications_remarks AR", "AR.application_id=A.id", "LEFT");
$this->db->group_by('A.id');
}
switch($qs['level']){
case 'STEP1':
$this->db->order_by("A.id", "DESC");
$this->db->group_by('A.id');
break;
case 'STEP2':
$this->db->order_by("A.step2date", "DESC");
$this->db->group_by('A.id');
break;
case 'STEP1_PAID':
$this->db->order_by("A.payment_date", "DESC");
$this->db->group_by('A.id');
break;
case 'STEP3':
$this->db->order_by("A.step3date", "DESC");
$this->db->group_by('A.id');
break;
case 'PAID':
$this->db->order_by("A.payment_date", "DESC");
$this->db->group_by('A.id');
break;
case 'RESOLVED':
case 'RESOLVED_ME':
$this->db->order_by("A.resolved_on", "DESC");
$this->db->group_by('A.id');
break;
default:
$this->db->group_by('A.id');
$this->db->order_by("A.id", "DESC");
break;
}
$rs=$this->dba->pagedRows($qs['p'], 20);
if($rs['result']){
$md=$this->common->master_data_for_leads();
$usrs=$this->user->active_users();
$users=key_val_array($usrs, 'id', 'name');
$utypes=key_val_array($usrs, 'id', 'type');
$leadsources=key_val_array($this->master->all_lead_sources(), 'id', 'name');
foreach($rs['result'] as &$r){
$r['is_phd']=(int)$r['is_phd'];
$r['campus'] =$md['campus'][$r['campus_id']];
$r['state'] =$md['states'][$r['state_id']];
$r['lpage'] =$md['lpages'][$r['lpage_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program_type'] =$md['program_types'][$r['type_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['school'] =$md['schools'][$r['school_id']];
//$r['utm_source'] =$md['utm_sources'][$r['utm_source_id']];
$r['payment_status'] =(int)$r['payment_status'];
$r['leadCreated'] =get_date($r['leadCreated'], true);
$r['leadUpdated'] =get_date($r['leadUpdated'], true);
$r['next_followup'] =get_date($r['next_followup']);
$r['step2date'] =$r['step2date']?get_date($r['step2date'], true):'Pending';
$r['step3date'] =$r['step3date']?get_date($r['step3date'], true):'Pending';
$r['step1date'] =$r['step1date']?get_date($r['step1date'], true):'Pending';
$r['step4date'] =$r['step4date']?get_date($r['step4date'], true):'Pending';
$r['step5date'] =$r['step5date']?get_date($r['step5date'], true):'Pending';
$r['step6date'] =$r['step6date']?get_date($r['step6date'], true):'Pending';
$r['step7date'] =$r['step7date']?get_date($r['step7date'], true):'Pending';
$r['payment_date'] =$r['payment_date']?get_date($r['payment_date'], true):'N/A';
$r['utm_source'] =$r['utm_source']?$r['utm_source']:'N/A';
$r['lead_source'] =$leadsources[$r['lead_source_id']];
$r['payment_status_text']='Not Paid';
if($r['payment_status']){
$r['payment_status_text']='Paid';
}
if($r['payment_mode']=='CASH'){
$r['pay_order_id']=$r['cash_order_id'];
}
$r['pay_order_id']=$r['pay_order_id']?$r['pay_order_id']:'N/A';
$r['txn_id']=$r['txn_id']?$r['txn_id']:'N/A';
$r['txn_status']=$r['txn_status']?$r['txn_status']:'N/A';
$r['followup_by']=(int)$r['followup_by'];
$r['receiptPrintUrl']=URL.'common/printReceipt/'.$r['id'];
$r['ps_status']="N/A";
if($r['ps_app_no']){
if($r['ps_status']=='ADM_OFFERED'){
$r['ps_status']="OFFERED";
}elseif($r['ps_status']=='ADM_FINAL'){
$r['ps_status']="FINAL";
}elseif($r['ps_status']=='ADM_WITHDRAW'){
$r['ps_status']="WITHDRAW";
}else{
$r['ps_status']="N/A";
}
}
$r['usr']=$r['created_by']?($users[$r['created_by']].' ['.user_types($utypes[$r['created_by']]).']'):'Self Registered';
/** No. of Issues and emails */
$issue=$this->db->select("COUNT(IF(status=3, 1, NULL)) iclose, COUNT(1) iall", false)->from("issues_reported")->where(['applicant_id'=>$r['applicant_id']])->get()->row_array();
$r['iclose']=(int)$issue['iclose'];
$r['iall']=(int)$issue['iall'];
$r['email_count']=(int)$this->db->select("COUNT(1) n", false)->from("sent_emails")->where(['to_email'=>$r['email'], 'is_letter'=>0])->get()->row()->n;
$r['whatsapp_count']=(int)$this->db->select("COUNT(1) n", false)->from("sent_whatsapp")->where(['app_id'=>$r['id']])->get()->row()->n;
$r['letter_count']=(int)$this->db->select("COUNT(1) n", false)->from("sent_emails")->where(['to_email'=>$r['email'], 'is_letter'=>1])->get()->row()->n;
$r['sms_count']=(int)$this->db->select("COUNT(1) n", false)->from("followup_sent_sms")->where(['application_id'=>$r['id']])->get()->row()->n;
$r['admin_remark_count']=(int)$this->db->select("COUNT(1) n", false)->from("admin_remarks")->where(['application_id'=>$r['id']])->get()->row()->n;
/** \ */
$r['login_url']=generate_login_url($r['lpage'], $r['mob']);
$r['followup_count']=(int)$r['followup_count'];
$r['followup_count_all']=(int)$r['followup_count_all'];
$r['followup_count_all_paid']=(int)$r['followup_count_all_paid'];
$r['paid_amt']=(float)$r['paid_amt'];
/** Assigned */
$r['assigned_to_usr']=$r['assigned_to']?($users[$r['assigned_to']].' ['.user_types($utypes[$r['assigned_to']]).']'):'';
$r['isresolved']=($r['assigned_to'] || $r['isresolved']=='Y')?$r['isresolved']:'';
$r['resolve_allowed']=($r['assigned_to'] && (IS_ADMIN || $r['assigned_to']==USER_ID) && $r['isresolved']=='N')?1:0;
$r['resolved_by_usr']=$r['resolved_by']?($users[$r['resolved_by']].' ['.user_types($utypes[$r['resolved_by']]).']'):'';
$r['resolved_on']=get_date($r['resolved_on'], true);
$r['slot_booking_date']=get_date($r['slot_booking_date']);
unset($r['state_id'], $r['lpage_id'], $r['plan_id'], $r['ac_id'], $r['type_id']);
$r['admission_card_url']=URLF.'api/phdform/download_adm_form_pdf/'.encode($r['id']);
$r['admission_card_print_url']=$r['admission_card_url'].'?print=1';
}
$rs['result']=filterLeadData($rs['result'], $lvOpt);
}
return $rs;
}
function listsToExport(){
$md=$this->common->master_data_for_leads();
$qs=trim_array($this->input->get());
$lvOpt=$this->dataset->detail(0, USER_ID);
if($lvOpt['expiry_date'] && strtotime($lvOpt['expiry_date'])<strtotime(date('Y-m-d'))){
return [];
}
$f="A.id, A.campus_id, A.applicant_id, A.application_no, A.step_completed, A.created AS leadCreated, A.updated AS leadUpdated,
A.step2date, A.step3date, A.step4date, A.step6date, A.step6date, A.step7date, A.plan_id, A.followup_count, A.followup_count_all, A.followup_by, A.isdead, A.suat_status,A.susat_appeared_status,A.admission_status,A.contract_status,A.fee_paid_status,A.slot_booking_date, A.suat_score, A.admission_status,
P.title program, P.ac_id, P.type_id, P.discipline_id, S.title school, S.ps_code school_code,
U.name, U.email, U.mob, U.mob2, U.dob, U.gender, U.address, U.city, U.pincode, U.state_id, U.lpage_id,
U.cat_id, U.religion_id, U.verified_by, U.father_name, U.identity_proof, U.identity_proof_no, U.profile_pic, U.is_campus_offline,
US.grp utm_group, U.utm_source, U.utm_campaign, U.utm_medium, U.utm_term, U.utm_content, U.utm_keyword, U.utm_placement, U.is_medical, U.neet_roll_no,
U.neet_marks, U.neet_all_india_rank, U.per_12th, U.newsletter_subscribed,
ED.board_10th, ED.marking_scheme_id_10th, ED.marks_10th, ED.passing_year_10th,
ED.board_12th, ED.marking_scheme_id_12th, ED.marks_12th, ED.passing_year_12th,
ED.board_dip, ED.marking_scheme_id_dip, ED.marks_dip, ED.passing_year_dip,
ED.university_ug, ED.marking_scheme_id_ug, ED.marks_ug, ED.passing_year_ug,
ED.university_pg, ED.marking_scheme_id_pg, ED.marks_pg, ED.passing_year_pg,
A.payment_status, A.payment_mode, A.cash_order_id, PTRANS.pay_order_id, PTRANS.txn_id, PTRANS.txn_status, PTRANS.txn_amt,
PTRANS.respmsg, PSS.ADM_APPL_NBR ps_app_no, PSS.DESCR1 ps_app_status, PSS.status ps_status, IF(PSS_PAID.EMPLID IS NULL, 'NOT_PAID', 'PAID') ps_paid_status,
MLR.title followup_title, AR.subremarks, AR.feedback, AL.program_name api_program, AL.plan_name api_plan, SR.disposition superbot_disposition,
PHD.net, PHD.gate, PHD.slet, PHD.mphil, PHD.other_entrance_exam_dtl, MS.id state_id, MS.title region_name";
$rs=[];
for($i=0; $i<=99; $i++){
$limit=20000;
$offset=$i*$limit;
$this->set_search_cond($qs, $lvOpt);
$this->db->select($f)
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("master_programs P", "P.id=A.program_id", "LEFT")
->join("master_schools S", "S.id=A.school_id", "LEFT")
->join("master_utm_sources US", "U.utm_source_id=US.id", "LEFT")
->join("applicants_edu_details ED", "ED.applicant_id=U.id", "LEFT")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->join("ps_paid_system_ids PSS_PAID", "PSS_PAID.EMPLID=A.application_no", "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")
->join("api_leads AL", "AL.id=A.api_lead_id", "LEFT")
->join("phd_app_form PHD", "PHD.app_id=A.id", "LEFT")
->join("master_states MS", "MS.id=U.state_id", "LEFT")
->group_by('A.id')
->order_by("A.id", "DESC")
->limit($limit, $offset);
//echo $this->db->get_compiled_select(); die;
$records=$this->db->get()->result_array();
if($records){
foreach($records as $rw){
$rs[]=$rw;
}
unset($records);
}else{
break;
}
}
if($rs){
//$ps_status=$this->ps_stu_status();
}else{
return [];
}
foreach($rs as &$r){
$r['campus'] =$md['campus'][$r['campus_id']];
$r['state'] =$md['states'][$r['state_id']];
$r['stateCode'] =$md['state_codes'][$r['state_id']];
$r['lpage'] =$md['lpages'][$r['lpage_id']];
$r['plan'] =$md['plans'][$r['plan_id']];
$r['ac_code'] =$md['plan_ac_codes'][$r['plan_id']];
$r['prgCode'] =$md['plan_prg_codes'][$r['plan_id']];
$r['planCode'] =$md['plan_codes'][$r['plan_id']];
if(!$r['program']){
$r['program']=$r['api_program'];
}
if(!$r['plan']){
$r['plan']=$r['api_plan'];
}
$r['ac'] =$md['acs'][$r['ac_id']];
$r['program_type'] =$md['program_types'][$r['type_id']];
//$r['utm_source'] =$md['utm_sources'][$r['utm_source_id']];
$r['discipline'] =$md['disciplines'][$r['discipline_id']];
$r['cat'] =$md['soc_cats'][$r['cat_id']];
$r['religion'] =$md['religions'][$r['religion_id']];
$r['marking_scheme_10'] =$md['mark_schemes'][$r['marking_scheme_id_10th']];
$r['marking_scheme_12'] =$md['mark_schemes'][$r['marking_scheme_id_12th']];
$r['marking_scheme_dip'] =$md['mark_schemes'][$r['marking_scheme_id_dip']];
$r['marking_scheme_ug'] =$md['mark_schemes'][$r['marking_scheme_id_ug']];
$r['marking_scheme_pg'] =$md['mark_schemes'][$r['marking_scheme_id_pg']];
$r['payment_status']=(int)$r['payment_status'];
$r['leadCreated']=get_date($r['leadCreated'], true);
$r['leadUpdated']=get_date($r['leadUpdated'], true);
$r['susat_status'] = $r['suat_status']?'Y':'N';
$r['susat_appeared_status'] = $r['susat_appeared_status'] == '1'?'Y':'N';
$r['admission_status']=$r['admission_status'] == '1'?'Y':'N';
$r['contract_status']=$r['contract_status'] == '1'?'Y':'N';
$r['fee_paid_status']=$r['fee_paid_status'] == '1'?'Y':'N';
$r['step2date']=$r['step2date']?get_date($r['step2date'], true):'N/A';
$r['step3date']=$r['step3date']?get_date($r['step3date'], true):'N/A';
$r['step4date']=$r['step4date']?get_date($r['step4date'], true):'N/A';
$r['step5date']=$r['step5date']?get_date($r['step5date'], true):'N/A';
$r['step6date']=$r['step6date']?get_date($r['step6date'], true):'N/A';
$r['step7date']=$r['step7date']?get_date($r['step7date'], true):'N/A';
$r['dob']=get_date($r['dob']);
$r['utm_source']=$r['utm_source']?$r['utm_source']:'N/A';
$r['payment_status_text']='Not Paid';
if($r['payment_status']){
$r['payment_status_text']='Paid';
}
$r['passing_year_10th']=$r['passing_year_10th']?$r['passing_year_10th']:'';
$r['passing_year_12th']=$r['passing_year_12th']?$r['passing_year_12th']:'';
$r['passing_year_ug']=$r['passing_year_ug']?$r['passing_year_ug']:'';
$r['passing_year_pg']=$r['passing_year_pg']?$r['passing_year_pg']:'';
if($r['payment_mode']=='CASH'){
$r['pay_order_id']=$r['cash_order_id'];
}
$r['pay_order_id']=$r['pay_order_id']?$r['pay_order_id']:'N/A';
$r['txn_id']=$r['txn_id']?'#'.$r['txn_id']:'N/A';
$r['txn_status']=$r['txn_status']?$r['txn_status']:'N/A';
/*if($r['step_completed']==3){
$ps=$ps_status[$r['application_no']];
if($ps){
$r['ps_application_no']=$ps[0]['application_no'];
$r['ps_application_status']=$ps[0]['application_status'];
$r['ps_status']=$ps[0]['status'];
}
}*/
// if($r['ps_app_no']){
// if($r['ps_status']=='ADM_OFFERED'){
// $r['ps_status']="OFFERED";
// }elseif($r['ps_status']=='ADM_FINAL'){
// $r['ps_status']="FINAL";
// }elseif($r['ps_status']=='ADM_WITHDRAW'){
// $r['ps_status']="WITHDRAW";
// }else{
// $r['ps_status']="N/A";
// }
// }
// echo "<pre>"; print_r($r); die;
$r['login_url']=generate_login_url($r['lpage'], $r['mob']);
$r['slot_booking_date']=get_date($r['slot_booking_date']);
if(!$r['suat_status']){
$r['suat_status']='Slot Not Booked';
}
unset($r['state_id'], $r['lpage_id'], $r['plan_id'], $r['ac_id'], $r['type_id'], $r['discipline_id'], $r['marking_scheme_id_10th'], $r['marking_scheme_id_12th'], $r['marking_scheme_id_dip'], $r['marking_scheme_id_ug'], $r['marking_scheme_id_pg'], $r['cat_id'], $r['religion_id']);
}
if($rs){
$rs=filterLeadData($rs, $lvOpt, TRUE);
}
return $rs;
}
function listsStepZero($export=FALSE){
$qs=trim_array($_REQUEST);
if($qs['utm_source_id']){
$res=$this->db->select("title")->from("master_utm_sources")->where_in("id", $qs['utm_source_id'])->get()->result_array();
$sources=[];
foreach($res as $s){
$sources[]=$s['title'];
}
}
$lvOpt=$this->dataset->detail(0, USER_ID);
if($lvOpt['utm_sources']){
$res=$this->db->select("title")->from("master_utm_sources")->where_in("id", $lvOpt['utm_sources'])->get()->result_array();
$sources_lv=[];
foreach($res as $s){
$sources_lv[]=$s['title'];
}
}
if($lvOpt['expiry_date'] && strtotime($lvOpt['expiry_date'])<strtotime(date('Y-m-d'))){
return [];
}
if($lvOpt['steps']){
if(!in_array('0', $lvOpt['steps'])){
return [];
}
}
if($lvOpt['states']){
$this->db->where_in("U.state_id", $lvOpt['states']);
}
if($lvOpt['programs']){
$this->db->where_in("P.id", $lvOpt['programs']);
}
if($lvOpt['utm_sources']){
$this->db->where_in("U.utm_source", $sources_lv);
}
if($qs['campus_id']){
$this->db->where("U.campus_id", $qs['campus_id']);
}
if($qs['k']){
$this->db->group_start()
->like('U.name', $qs['k'])
->or_like('U.email', $qs['k'])
->or_like('U.mob', $qs['k'])
->group_end();
}
if($qs['type_id']){
$this->db->where("P.type_id", $qs['type_id']);
}
if($qs['utm_source_id']){
$this->db->where_in("U.utm_source", $sources);
}
if($qs['fromDate']){
$this->db->where("U.created >=", date('Y-m-d', strtotime($qs['fromDate'])));
}
if($qs['toDate']){
$this->db->where("U.created <=", date('Y-m-d 23:59:59', strtotime($qs['toDate'])));
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
$this->db->select("U.id, U.campus_id, U.created AS leadCreated, PP.title AS plan, P.title AS program, PP.ac_code, PP.program_code AS prgCode, PP.code AS planCode, AC.title AS ac, PT.title AS program_type,
U.name, U.email, U.mob, S.title AS state, S.code AS stateCode, LP.title AS lpage, U.utm_source, U.utm_campaign, U.utm_medium, U.utm_term, U.utm_content, U.utm_keyword, U.utm_placement,
U.is_medical, U.neet_roll_no, U.neet_marks, U.neet_all_india_rank, CMP.title campus")
->from("tmp_applicants U")
->join("master_campus CMP", "CMP.id=U.campus_id")
->join("landing_pages AS LP", "LP.id=U.lpage_id")
->join("master_states AS S", "S.id=U.state_id", "LEFT")
->join("master_program_plans AS PP", "PP.id=U.plan_id", "LEFT")
->join("master_programs AS P", "P.id=PP.program_id", "LEFT")
->join("master_academic_careers AS AC", "AC.id=P.ac_id", "LEFT")
->join("master_program_types AS PT", "PT.id=P.type_id", "LEFT")
->order_by("U.id", "DESC");
$ps=50;
if($export){
$qs['p']=1;
$ps=100000;
}
$rs=$this->dba->pagedRows($qs['p'], $ps, 5, $export?'0':'');
foreach($rs['result'] as &$r){
$r['step_completed']='-1';
$r['payment_status']=(int)$r['payment_status'];
$r['leadCreated']=get_date($r['leadCreated'], true);
$r['utm_source']=$r['utm_source']?$r['utm_source']:'N/A';
$r['payment_status_text']='Not Paid';
$r['pay_order_id']=$r['pay_order_id']?$r['pay_order_id']:'N/A';
$r['txn_id']=$r['txn_id']?$r['txn_id']:'N/A';
$r['txn_status']=$r['txn_status']?$r['txn_status']:'N/A';
}
if($rs['result']){
$rs['result']=filterLeadData($rs['result'], $lvOpt);
}
return $rs;
}
function save($data1, $data2, $edu=false){
$err = FALSE;
$this->db->trans_strict(FALSE);
$this->db->trans_begin();
try{
if(!$data1['id']){
$data1['verified_by']='OFFLINE';
}
$id=$this->dba->save("applicants", $data1);
$data2['applicant_id']=$id;
if(!$data2['id']){
/* if($data1['is_phd']){
$num=$this->getCounter('phd_app_count');
}else{
$num=$this->getCounter('application_no_count');
} */
$num=$this->getCounter('application_no_count');
$data2['application_no']=zero_format_no($num);
}
if($data2['step_completed']==1){
$data2['step1_date']=currentDT();
$data2['step1_by']=USER_ID;
}
if($data2['step_completed']==2){
$data2['step2_by']=USER_ID;
}
$app_id=$this->dba->save("applications", $data2);
if($edu){
$edu['id']=(int)$this->db->select("id")->get_where("applicants_edu_details", ['applicant_id'=>$id])->row()->id;
$edu['applicant_id']=$id;
$this->dba->save("applicants_edu_details", $edu);
}
if(!$data1['id']){
/** Visit History */
$d=['verified_by'=>'OFFLINE'];
$d['applicant_id']=$id;
$d['lpage']=rtrim($this->db->get_where("landing_pages", array('id'=>$data1['lpage_id']))->row()->title, "/");
$this->common->save(replace_null($d), "applicants_visit_history");
}
}catch(Exception $e) {
$err = TRUE;
$msg = $e->getMessage();
}
if($this->db->trans_status() === FALSE) {
$err = TRUE;
}
if($err){
$this->db->trans_rollback();
return FALSE;
}else{
$this->db->trans_commit();
return $app_id;
}
}
function delete($id){
//$inf['msg']="Can't be deleted because payment has been made!"; $this->json_data($inf);
//$this->db->db_debug=FALSE;
$this->db->delete("data_set_leads", array('application_id'=>$id));
$this->db->delete("data_set_split_leads", array('application_id'=>$id));
$this->db->delete("pay_trans", array('application_id'=>$id));
$this->db->delete("phd_app_form", array('app_id'=>$id));
$this->db->delete("superbot_campaign_leads", array('app_id'=>$id));
$this->db->delete("sent_emails", array('application_id'=>$id));
$this->db->delete("followup_sent_sms", array('application_id'=>$id));
$this->db->delete("applications", array('id'=>$id));
return $this->db->affected_rows();
}
function deleteApplicant($id){
$this->db->db_debug=FALSE;
$this->db->delete("applicants_edu_details", array('applicant_id'=>$id));
$this->db->delete("applications", array('applicant_id'=>$id));
return $this->db->delete("applicants", array('id'=>$id));
}
/** */
function userDetail($id){
$dtl=$this->db->get_where("applicants", array('id'=>$id))->row_array();
if($dtl){
$dtl['dob']=get_date($dtl['dob']);
$dtl['state_id']=$dtl['state_id']?$dtl['state_id']:'';
$dtl['city_id']=$dtl['city_id']?$dtl['city_id']:'';
$dtl['cat_id']=$dtl['cat_id']?$dtl['cat_id']:'';
$dtl['religion_id']=$dtl['religion_id']?$dtl['religion_id']:'';
$dtl['profilePicUrl']=$dtl['profile_pic']?UP_URLF.'profile_pics/'.$dtl['profile_pic']:'';
$dtl['is_medical']=(int)$dtl['is_medical'];
$dtl['neet_scorecard_file_url']=$dtl['neet_scorecard_file']?UP_URLF.'scorecards/'.$dtl['neet_scorecard_file']:'';
if($dtl['lead_source_id']){
$ls=$this->db->select("name, state_id")->get_where("lead_sources", ['id'=>$dtl['lead_source_id']])->row_array();
$dtl['lead_source']=$ls['name'];
$dtl['lead_source_state_id']=$ls['state_id'];
}else{
$dtl['lead_source']=$dtl['lead_source_id']=$dtl['lead_source_state_id']='';
}
}
return $dtl;
}
function eduDetail($id){
$dtl=$this->db->get_where("applicants_edu_details", array('applicant_id'=>$id))->row_array();
if($dtl){
$dtl['board_id_10th']=$dtl['board_id_10th']?$dtl['board_id_10th']:'';
$dtl['marking_scheme_id_10th']=$dtl['marking_scheme_id_10th']?$dtl['marking_scheme_id_10th']:'';
$dtl['passing_year_10th']=$dtl['passing_year_10th']?$dtl['passing_year_10th']:'';
$dtl['board_id_12th']=$dtl['board_id_12th']?$dtl['board_id_12th']:'';
$dtl['marking_scheme_id_12th']=$dtl['marking_scheme_id_12th']?$dtl['marking_scheme_id_12th']:'';
$dtl['passing_year_12th']=$dtl['passing_year_12th']?$dtl['passing_year_12th']:'';
$dtl['board_id_dip']=$dtl['board_id_dip']?$dtl['board_id_dip']:'';
$dtl['marking_scheme_id_dip']=$dtl['marking_scheme_id_dip']?$dtl['marking_scheme_id_dip']:'';
$dtl['passing_year_dip']=$dtl['passing_year_dip']?$dtl['passing_year_dip']:'';
$dtl['university_id_ug']=$dtl['university_id_ug']?$dtl['university_id_ug']:'';
$dtl['marking_scheme_id_ug']=$dtl['marking_scheme_id_ug']?$dtl['marking_scheme_id_ug']:'';
$dtl['passing_year_ug']=$dtl['passing_year_ug']?$dtl['passing_year_ug']:'';
$dtl['university_id_pg']=$dtl['university_id_pg']?$dtl['university_id_pg']:'';
$dtl['marking_scheme_id_pg']=$dtl['marking_scheme_id_pg']?$dtl['marking_scheme_id_pg']:'';
$dtl['passing_year_pg']=$dtl['passing_year_pg']?$dtl['passing_year_pg']:'';
}
return $dtl;
}
function getCounter($type, $dbcon=false){
if(!$dbcon){
$dbcon=$this->db;
}
while(1){
$c=(int)$dbcon->get_where("master_data", array('datakey'=>$type))->row()->datavalue;
$dbcon->update("master_data", array('datavalue'=>$c+1), array('datakey'=>$type));
if(!$dbcon->affected_rows()){
continue;
}
break;
}
return $c;
}
function updateLead($post, $appDtl){
$err = FALSE;
$this->db->trans_strict(FALSE);
$this->db->trans_begin();
try{
$dtl=$post['dtl'];
$dtl['id']=(int)$appDtl['applicant_id'];
if(!$dtl['id']){
$dtl['verified_by']='OFFLINE';
}
$applicant_id=$this->dba->save("applicants", replace_null($dtl));
$eduDtl=$post['eduDtl'];
$eduDtl['applicant_id']=$applicant_id;
$eduId=$this->db->select("id")->get_where("applicants_edu_details", array('applicant_id'=>$eduDtl['applicant_id']))->row()->id;
if($eduId){
$eduDtl['id']=$eduId;
}
$this->dba->save("applicants_edu_details", replace_null($eduDtl));
$app=$post['app'];
$prg=$this->db->select("type_id, ac_id, discipline_id, school_id, is_medical, is_phd")->get_where("master_programs", ['id'=>$app['program_id']])->row_array();
$appData=array(
'id'=>(int)$appDtl['id'],
'applicant_id'=>$applicant_id,
'step_completed'=>$appDtl['step_completed']<3?2:3,
'program_id'=>$app['program_id'],
'plan_id'=>$app['plan_id'],
'ac_id'=>$prg['ac_id'],
'school_id'=>$prg['school_id'],
'discipline_id'=>$prg['discipline_id'],
);
if(!$appData['id']){
$num=$this->getCounter('application_no_count');
$appData['application_no']=zero_format_no($num);
$appData['step2date']=currentDT();
}
$this->dba->save("applications", replace_null($appData));
}catch(Exception $e) {
$err = TRUE;
$msg = $e->getMessage();
}
if($this->db->trans_status() === FALSE) {
$err = TRUE;
}
if ($err) {
$this->db->trans_rollback();
return FALSE;
} else {
$this->db->trans_commit();
return TRUE;
}
}
/** */
function detail($id, $filter=true){
$this->db->select("A.id, A.campus_id, A.applicant_id, A.application_no, A.step_completed, A.created AS leadCreated, A.updated AS leadUpdated, A.step2date, A.step3date, A.suat_status, A.step_completed, PP.id plan_id, PP.title AS plan, P.id program_id, P.title AS program,
AC.title AS ac, PT.title AS program_type, PP.ac_code, PP.program_code AS prgCode, PP.code AS planCode, SCH.title school_name, SCH.website school_website,
U.name, U.email, U.mob, U.mob2, U.dob, U.gender, U.address, U.abc_id, U.is_campus_offline,U.nationality,U.city, U.relation,U.designation, U.parent_email_id, U.parent_mobile,U.parent_current_address,U.region,U.parent_country,U.awards_achievements,U.university_transfer, U.university_sponsored_student,
U.city, U.pincode, U.verified_by, U.father_name, U.identity_proof, U.identity_proof_no,U.passport_number, U.profile_pic, U.process_by, U.lead_source_id, U.lead_source_remarks,
U.utm_source, U.utm_campaign, U.utm_medium, U.utm_term, U.utm_content, U.utm_keyword, U.utm_placement, U.is_medical, U.neet_roll_no, U.neet_marks, U.neet_all_india_rank, U.neet_scorecard_file,U.tenth_document,U.twth_document, U.state_id,
ED.board_10th, B10.ps_code AS ps_code_b10, M10.title AS marking_scheme_10, M10.ps_code AS ps_code_m10, ED.marks_10th, ED.passing_year_10th,
ED.board_12th, B12.ps_code AS ps_code_b12, M12.title AS marking_scheme_12, M12.ps_code AS ps_code_m12, ED.marks_12th, ED.passing_year_12th,
ED.board_dip, DIP.ps_code AS ps_code_bdip, MDIP.title AS marking_scheme_dip, MDIP.ps_code AS ps_code_mdip, ED.marks_dip, ED.passing_year_dip,
ED.university_ug, UUG.ps_code AS ps_code_uug, MUG.title AS marking_scheme_ug, MUG.ps_code AS ps_code_mug, ED.marks_ug, passing_year_ug,
ED.university_pg, UPG.ps_code AS ps_code_upg, MPG.title AS marking_scheme_pg, MPG.ps_code AS ps_code_mpg, ED.marks_pg, passing_year_pg,
CT.title AS cat, CT.ps_code AS ps_code_cat, RL.title AS religion, RL.ps_code AS ps_code_religion,
LP.title AS lpage, A.payment_status, A.payment_mode, A.payment_date, A.step1_paid, A.cash_order_id, A.pay_trans_id, A.pay_link_sent, A.adm_paid_by,
PTRANS.pay_order_id, PTRANS.txn_id, PTRANS.txn_status, PTRANS.txn_amt, CMP.title campus, AL.program_name api_program, AL.plan_name api_plan,
SR.disposition superbot_disposition, CN.id, CN.title AS country_name, CL.code, CL.title AS student_country,CL.isd_code AS ISD_CODE, MS.id, MS.title AS state_name, MS.code")
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("master_campus CMP", "CMP.id=A.campus_id")
->join("landing_pages AS LP", "LP.id=U.lpage_id")
->join("master_countrys AS CN", "CN.id = U.parent_country", "LEFT")
->join("master_countrys AS CL", "CL.code = U.country", "LEFT")
->join("master_states AS MS", "MS.id = U.state_id", "LEFT")
->join("master_program_plans AS PP", "PP.id=A.plan_id", "LEFT")
->join("master_programs AS P", "P.id=PP.program_id", "LEFT")
->join("master_schools SCH", "SCH.id=P.school_id", "LEFT")
->join("master_academic_careers AS AC", "AC.id=P.ac_id", "LEFT")
->join("master_program_types AS PT", "PT.id=P.type_id", "LEFT")
->join("applicants_edu_details AS ED", "ED.applicant_id=U.id", "LEFT")
->join("master_edu_boards AS B10", "B10.id=ED.board_id_10th", "LEFT")
->join("master_marking_schemes AS M10", "M10.id=ED.marking_scheme_id_10th", "LEFT")
->join("master_edu_boards AS B12", "B12.id=ED.board_id_12th", "LEFT")
->join("master_marking_schemes AS M12", "M12.id=ED.marking_scheme_id_12th", "LEFT")
->join("master_edu_boards AS DIP", "DIP.id=ED.board_id_dip", "LEFT")
->join("master_marking_schemes AS MDIP", "MDIP.id=ED.marking_scheme_id_dip", "LEFT")
->join("master_universities AS UUG", "UUG.id=ED.university_id_ug", "LEFT")
->join("master_marking_schemes AS MUG", "MUG.id=ED.marking_scheme_id_ug", "LEFT")
->join("master_universities AS UPG", "UPG.id=ED.university_id_pg", "LEFT")
->join("master_marking_schemes AS MPG", "MPG.id=ED.marking_scheme_id_pg", "LEFT")
->join("master_applicat_cats AS CT", "CT.id=U.cat_id", "LEFT")
->join("master_applicat_religions AS RL", "RL.id=U.religion_id", "LEFT")
->join("pay_trans AS PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("api_leads AS AL", "AL.id=A.api_lead_id", "LEFT")
->join("superbot_response SR", "SR.id=U.superbot_resp_id", "LEFT")
->where("A.id", $id)
->order_by("A.updated", "DESC")->limit(1);
$rs=$this->db->get()->row_array();
//echo $this->db->last_query(); die;
$dtl=[];
if($rs){
$rs['profilePicUrl']=$rs['profile_pic']?UP_URLF.'profile_pics/'.$rs['profile_pic']:'';
$rs['receiptPrintUrl']=URL.'common/printReceipt/'.$rs['id'];
$rs['payment_status']=(int)$rs['payment_status'];
$rs['leadCreated']=get_date($rs['leadCreated'], true);
$rs['leadUpdated']=get_date($rs['leadUpdated'], true);
$rs['step2date']=get_date($rs['step2date'], true);
$rs['step3date']=get_date($rs['step3date'], true);
$rs['payment_date']=get_date($rs['payment_date'], true);
$rs['dob']=get_date($rs['dob']);
$rs['utm_source']=$rs['utm_source']?$rs['utm_source']:'N/A';
$rs['mob2']=$rs['mob2']?$rs['mob2']:'N/A';
$rs['identity_proof']=$rs['identity_proof']?$rs['identity_proof']:'N/A';
$rs['identity_proof_no']=$rs['identity_proof_no']?$rs['identity_proof_no']:'N/A';
$rs['utm_source']=$rs['utm_source']?$rs['utm_source']:'N/A';
$rs['utm_campaign']=$rs['utm_campaign']?$rs['utm_campaign']:'N/A';
$rs['utm_medium']=$rs['utm_medium']?$rs['utm_medium']:'N/A';
$rs['utm_term']=$rs['utm_term']?$rs['utm_term']:'N/A';
$rs['utm_content']=$rs['utm_content']?$rs['utm_content']:'N/A';
$rs['utm_keyword']=$rs['utm_keyword']?$rs['utm_keyword']:'N/A';
$rs['utm_placement']=$rs['utm_placement']?$rs['utm_placement']:'N/A';
$rs['payment_status_text']='Not Paid';
if($rs['payment_status']){
$rs['payment_status_text']='Paid';
}
if($rs['lead_source_id']){
$ls=$this->db->select("name, state_id")->get_where("lead_sources", ['id'=>$rs['lead_source_id']])->row_array();
$rs['lead_source']=$ls['name'];
$rs['lead_source_state_id']=$ls['state_id'];
}else{
$rs['lead_source']=$rs['lead_source_id']=$rs['lead_source_state_id']='';
}
$rs['passing_year_10th']=$rs['passing_year_10th']?$rs['passing_year_10th']:'';
$rs['passing_year_12th']=$rs['passing_year_12th']?$rs['passing_year_12th']:'';
$rs['passing_year_dip']=$rs['passing_year_dip']?$rs['passing_year_dip']:'';
$rs['passing_year_ug']=$rs['passing_year_ug']?$rs['passing_year_ug']:'';
$rs['passing_year_pg']=$rs['passing_year_pg']?$rs['passing_year_pg']:'';
if($rs['payment_mode']=='CASH'){
$rs['pay_order_id']=$rs['cash_order_id'];
}
$rs['pay_order_id']=$rs['pay_order_id']?$rs['pay_order_id']:'N/A';
$rs['txn_id']=$rs['txn_id']?$rs['txn_id']:'N/A';
$rs['txn_status']=$rs['txn_status']?$rs['txn_status']:'N/A';
$rs['payTrans']=$this->db->get_where("pay_trans", array('application_id'=>$id))->result_array();
if($rs['payTrans']){
foreach($rs['payTrans'] as &$r){
if($r['payment_status']=='FAIL'){
$r['txn_date']=$r['created'];
}
$r['txn_date']=get_date($r['txn_date'], true);
unset($r['created'], $r['updated'], $r['full_resp']);
}
}
$rs['cashTrans']=$this->db->get_where("cash_collections", array("application_id"=>$id))->row_array();
if($rs['cashTrans']){
$rs['cashTrans']['usr']=$this->db->select("name")->get_where("users", array('id'=>$rs['cashTrans']['created_by']))->row()->name;
}
$rs['is_medical']=(int)$rs['is_medical'];
$rs['neet_scorecard_file_url']=$rs['neet_scorecard_file']?UP_URLF.'scorecards/'.$rs['neet_scorecard_file']:'';
if($rs['step_completed']==3){
$rs['ps_app']=$this->ps_stu_status($rs['application_no']);
if(!$rs['ps_app']){
$rs['ps_app']=false;
}
}
$rs['login_url']=generate_login_url($rs['lpage'], $rs['mob']);
$rs['payment_link']=generate_payment_link($rs['lpage'], $rs['mob'], $rs['id']);
$rs['payment_link_adm']=generate_payment_link($rs['lpage'], $rs['mob'], $rs['id'], USER_ID);
$rs['pay_link_sent']=(int)$rs['pay_link_sent'];
$rs['adm_paid_by']=(int)$rs['adm_paid_by'];
$rs['plan_dtl']=$this->db->get_where("master_program_plans", array('id'=>$rs['plan_id']))->row_array();
$rs['plan_dtl']['fee_year_wise']=explode(",", $rs['plan_dtl']['fee_year_wise']);
$rs['plan_dtl']['fee_semester_wise']=explode(",", $rs['plan_dtl']['fee_semester_wise']);
if($filter){
$lvOpt=$this->dataset->detail(0, USER_ID);
$rs=filterLeadData($rs, $lvOpt, TRUE);
$dtl=$rs[0];
}else{
$dtl=$rs;
}
}
return $dtl;
}
function ps_stu_status($system_id=''){
if($system_id){
$dtl=$this->db->select("EMPLID system_id, ADM_APPL_NBR application_no, DESCR1 application_status, status")->where(['EMPLID'=>$system_id])->limit(1)->get("ps_stu_status")->row_array();
if($dtl){
if($dtl['status']=='ADM_OFFERED'){
$dtl['status']="OFFERED";
}elseif($dtl['status']=='ADM_FINAL'){
$dtl['status']="FINAL";
}elseif($dtl['ps_status']=='ADM_WITHDRAW'){
$dtl['ps_status']="WITHDRAW";
}else{
$dtl['status']="N/A";
}
}
return $dtl;
}
$rs=$this->db->select("EMPLID system_id, ADM_APPL_NBR application_no, DESCR1 application_status, status")->get("ps_stu_status")->result_array();
foreach($rs as &$r){
if($r['status']=='ADM_OFFERED'){
$r['status']="OFFERED";
}elseif($r['status']=='ADM_FINAL'){
$r['status']="FINAL";
}elseif($r['ps_status']=='ADM_WITHDRAW'){
$r['ps_status']="WITHDRAW";
}else{
$r['status']="N/A";
}
}
return group_array($rs, 'system_id');
}
function coupans($user_id, $plan_id=0, $program_id=0){
$lpage_id=$this->db->select()->get_where("applicants", array('id'=>$user_id))->row()->lpage_id;
$res=$this->db->select("coupan_id")->get_where("coupans_in_use", array('plan_id'=>$plan_id))->result_array();
if(!$res){
$res=$this->db->select("coupan_id")->get_where("coupans_in_use", array('program_id'=>$program_id))->result_array();
}
if(!$res){
$res=$this->db->select("coupan_id")->get_where("coupans_in_use", array('lpage_id'=>$lpage_id))->result_array();
}
$rs=[];
if($res){
$cids=[];
foreach($res as $r){
$cids[]=$r['coupan_id'];
}
$this->db->where(array('status'=>1, 'expiry_date >='=>date('Y-m-d')))->where_in("id", $cids);
$rs=$this->db->select("id, title, amt, description")->from("master_coupans")->order_by("disp_odr")->get()->result_array();
foreach($rs as &$r){
$r['amt']=(float)$r['amt'];
}
}
return $rs;
}
function applicationDtl($appId=0){
$r=$this->db->select("A.id, A.campus_id, A.applicant_id, A.application_no, A.step_completed, A.payment_status, A.payment_date, A.payment_mode, A.fee, A.coupan_used,
A.coupan_amt, A.pay_link_sent, A.adm_paid_by, A.created, P.ac_id, P.id AS program_id, A.plan_id, PP.title AS plan, PP.fee AS planAppFee, P.fee AS prgAppFee, P.title AS program,
P.image AS progImage, P.brochure AS progBrochure, AL.program_name api_program, AL.plan_name api_plan")
->from("applications AS A")
->join("master_program_plans AS PP", "A.plan_id=PP.id", "LEFT")
->join("master_programs AS P", "PP.program_id=P.id", "LEFT")
->join("api_leads AS AL", "AL.id=A.api_lead_id", "LEFT")
->where(array('A.id'=>$appId))
->get()->row_array();
if($r){
$r['progImageUrl']=$r['progImage']?UP_URLF.'programs_images/'.$r['progImage']:'';
$r['progBrochureUrl']=$r['progBrochure']?UP_URLF.'brochure/'.$r['progBrochure']:'';
$r['progBrochureDwnUrl']=$r['progBrochure']?(URL.'common/downloadFile/'.encode(UP_PATHF.'brochure/'.$r['progBrochure'])):'';
$r['receiptDownloadUrl']=URL.'common/downloadReceipt/'.$appId;
$r['receiptPrintUrl']=URL.'common/printReceipt/'.$appId;
$r['step_completed']=(int)$r['step_completed'];
if($r['step_completed']==3){
$r['fee']=(float)$r['fee'];
$r['coupan_amt']=(float)$r['coupan_amt'];
}else{
if((float)$r['planAppFee']){
$r['fee']=(float)$r['planAppFee'];
}else{
$r['fee']=(float)$r['prgAppFee'];
}
$r['coupan_used']='';
$r['coupan_amt']=0;
}
$r['created']=get_date($r['created']);
$udtl=$this->db->select("U.mob, U.gender, L.instant_fee, L.male_fee, L.female_fee, L.other_fee, L.title lpage")->from("applicants U")->join("landing_pages L", "L.id=U.lpage_id")->where(['U.id'=>$r['applicant_id']])->get()->row_array();
$r['instant_fee']=(int)$udtl['instant_fee'];
$r['male_fee']=(int)$udtl['male_fee'];
$r['female_fee']=(int)$udtl['female_fee'];
$r['other_fee']=(int)$udtl['other_fee'];
if($udtl['gender']==='Male' && $r['male_fee']>0){
$r['fee']=$r['male_fee'];
}
if($udtl['gender']==='Female' && $r['female_fee']>0){
$r['fee']=$r['female_fee'];
}
if($udtl['gender']==='Other' && $r['other_fee']>0){
$r['fee']=$r['other_fee'];
}
$r['netAmt']=($r['fee']-$r['coupan_amt']);
$r['payment_link']=generate_payment_link($udtl['lpage'], $udtl['mob'], $r['id']);
$r['payment_link_adm']=generate_payment_link($udtl['lpage'], $udtl['mob'], $r['id'], USER_ID);
$r['pay_link_sent']=(int)$r['pay_link_sent'];
$r['adm_paid_by']=(int)$r['adm_paid_by'];
$r['payment_status']=(int)$r['payment_status'];
unset($r['prgAppFee'], $r['planAppFee']);
}
return $r;
}
/** Follow-ups */
function remarks_cats(){
return $this->db->select('id,title')->from('master_lead_remarks_cats')->order_by('disp_odr')->get()->result_array();
}
function remarks(){
$rs=$this->db->select("id, cat_id, title")->from("master_lead_remarks")->where("status",1)->order_by("cat_id")->order_by("disp_odr")->get()->result_array();
foreach($rs as &$r){
$r['subremarks']=$this->db->select("title")->order_by("disp_odr")->get_where("master_lead_subremarks", array('remarks_id'=>$r['id']))->result_array();
if(!$r['subremarks']){
$r['subremarks']=[];
}
}
return $rs;
}
function get_follow_ups($id){
$rs=$this->db->select("AR.id, AR.application_id, AR.is_after_paid, MR.title remarks, AR.subremarks, AR.feedback, AR.comm_mode, AR.created, U.name usr, U.type usr_type")
->from("applications_remarks AR")
->join("master_lead_remarks MR", "AR.remarks_id=MR.id")
->join("users U", "AR.created_by=U.id", "LEFT")
->where("AR.application_id", $id)
->order_by("AR.id", "DESC")
->get()->result_array();
if($rs){
foreach($rs as &$r){
$r['created']=get_date($r['created'], true);
if(!$r['usr']){
$r['usr']="--User Deleted--";
}else{
$r['usr_type']=user_types($r['usr_type']);
}
}
}
return $rs;
}
function follow_up_detail($id=0){
$rs['dtl']=$this->db->select("AR.id, AR.application_id, AR.remarks_id, AR.subremarks, AR.feedback, AR.comm_mode")->from("applications_remarks AR")->where("AR.id", $id)->get()->row_array();
if($rs['dtl']){
$rs['dtl']['cat_id']=$this->db->select("cat_id")->get_where("master_lead_remarks", ['id'=>$rs['dtl']['remarks_id']])->row()->cat_id;
$rs['dtl']['next_followup']=$this->db->select("next_followup")->get_where("applications", ['id'=>$rs['dtl']['application_id']])->row()->next_followup;
$rs['dtl']['next_followup']=get_date($rs['dtl']['next_followup']);
$rs['subremarks']=$this->db->select("title")->order_by("disp_odr")->get_where("master_lead_subremarks", array('remarks_id'=>$rs['dtl']['remarks_id']))->result_array();
}
return $rs;
}
function delete_follow_up($id){
$this->db->db_debug=FALSE;
return $this->db->delete("applications_remarks", array('id'=>$id));
}
/** Emails */
function get_sent_emails($to_email, $short=false, $is_letter=0){
$post=$this->input->post();
if(!$post['is_letter']){
$post['is_letter']=$is_letter;
}
if($post['is_letter']){
$this->db->where("is_letter", 1);
}else{
$this->db->where("is_letter", 0);
}
$rs=$this->db->select("e.subject, e.body, e.attachment, e.created, u.name usr, u.type usr_type")
->from("sent_emails e")
->join("users u", "u.id=e.created_by")
->where(['to_email'=>$to_email])
->order_by("e.id", "desc")
->limit(20)
->get()->result_array();
foreach($rs as &$r){
$r['usr_type']=user_types($r['usr_type']);
$r['created']=get_date($r['created'], true);
if($short){
$r['body']=str_short($r['body'], 100);
unset($r['attachment']);
}else{
$r['attachment_urls']=[];
if($r['attachment']){
$attachments=explode(",", $r['attachment']);
foreach($attachments as $a){
$r['attachment_urls'][]=UP_URLF.'email_attachments/'.$a;
}
}
}
}
return $rs;
}
function get_sent_whatsapp($app_id){
$rs=$this->db->select("e.msg, e.created, u.name usr, u.type usr_type")
->from("sent_whatsapp e")
->join("users u", "u.id=e.created_by")
->where(['e.app_id'=>$app_id])
->order_by("e.id", "desc")
->limit(50)
->get()->result_array();
foreach($rs as &$r){
$r['usr_type']=user_types($r['usr_type']);
$r['created']=get_date($r['created'], true);
}
return $rs;
}
/** SMS */
function get_sent_sms($app_id){
$rs=$this->db->select("s.*, u.name usr, u.type usr_type")
->from("followup_sent_sms s")
->join("users u", "u.id=s.created_by")
->where(['s.application_id'=>$app_id])
->order_by("s.id", "desc")
->limit(20)
->get()->result_array();
foreach($rs as &$r){
$r['usr_type']=user_types($r['usr_type']);
$r['created']=get_date($r['created'], true);
}
return $rs;
}
/** Assign */
function application_assign_history($app_id){
$res=$this->db->select("H.id, H.assigned_to, H.remarks, H.created, U.name usr, U.type usr_type, AU.name assign_to, AU.type ausr_type")
->from("application_assign_history H")
->join("users U", "U.id=H.created_by")
->join("users AU", "AU.id=H.assigned_to")
->where(['H.application_id'=>$app_id])
->order_by("H.id")
->get()->result_array();
foreach($res as &$r){
$r['usr_type']=user_types($r['usr_type']);
$r['ausr_type']=user_types($r['ausr_type']);
$r['created']=get_date($r['created'], true);
}
return $res;
}
function visitHistory($applicant_id){
$rs=$this->db->order_by("id", "desc")->get_where("applicants_visit_history", ['applicant_id'=>$applicant_id])->result_array();
if($rs){
foreach($rs as &$r){
$r['is_paid']=(int)$r['is_paid'];
$r['created']=get_date($r['created'], true);
$r['is_paid']=$r['is_paid']?'YES':'NO';
unset($r['id'], $r['applicant_id']);
}
}else{
$rs=[];
}
return $rs;
}
/** Lead from Old to New */
function leadFromOldToNew($app_id){
$curdb=$this->load->database("curdb", true);
$curdb->query("SET sql_mode=''");
$curdb->query("SET foreign_key_checks=0");
$res=['success'=>false, 'msg'=>'Error!'];
$err=FALSE;
$this->db->trans_strict(FALSE);
$this->db->trans_begin();
try{
$a=$this->db->get_where("applications", ['id'=>$app_id])->row_array();
if(!$a){
throw new Exception("Error 1!");
}
$applicant_id=$a['applicant_id'];
$b=$this->db->get_where("applicants", ['id'=>$applicant_id])->row_array();
if(!$b){
throw new Exception("Error 2!");
}
$c=$this->db->get_where("admin_remarks", ['application_id'=>$app_id])->result_array();
$d=$this->db->get_where("adm_form", ['app_id'=>$app_id])->row_array();
$e=$this->db->get_where("applicants_edu_details", ['applicant_id'=>$applicant_id])->row_array();
$f=$this->db->get_where("applicants_visit_history", ['applicant_id'=>$applicant_id])->result_array();
$g=$this->db->get_where("applications_remarks", ['application_id'=>$app_id])->result_array();
$h=$this->db->get_where("application_assign_history", ['application_id'=>$app_id])->result_array();
$i=$this->db->get_where("pay_trans", ['application_id'=>$app_id])->result_array();
$j=$this->db->get_where("cash_collections", ['application_id'=>$app_id])->result_array();
unset($b['id']);
$b['created']=currentDT();
$b['updated']=currentDT();
$b['created_by']=USER_ID;
$b['updated_by']=USER_ID;
$curdb->insert("applicants", $b);
$newApplicantId=$curdb->insert_id();
unset($a['id']);
$a['step2date']=currentDT();
$a['step3date']=currentDT();
$a['created']=currentDT();
$a['updated']=currentDT();
$a['created_by']=USER_ID;
$a['updated_by']=USER_ID;
$a['applicant_id']=$newApplicantId;
$num=$this->getCounter('application_no_count', $curdb);
$a['application_no']=zero_format_no($num);
$curdb->insert("applications", $a);
$newAppId=$curdb->insert_id();
if($c){
foreach($c as $r){
unset($r['id']);
$r['application_id']=$newAppId;
$curdb->insert("applications", $r);
}
}
if($d){
unset($d['id']);
$d['app_id']=$newAppId;
$curdb->insert("adm_form", $d);
}
if($e){
unset($e['id']);
$e['applicant_id']=$newApplicantId;
$curdb->insert("applicants_edu_details", $e);
}
if($f){
foreach($f as $r){
unset($r['id']);
$r['applicant_id']=$newApplicantId;
$curdb->insert("applicants_visit_history", $r);
}
}
if($g){
foreach($g as $r){
unset($r['id']);
$r['application_id']=$newAppId;
$curdb->insert("applications_remarks", $r);
}
}
if($h){
foreach($h as $r){
unset($r['id']);
$r['application_id']=$newAppId;
$curdb->insert("application_assign_history", $r);
}
}
if($i){
foreach($i as $r){
unset($r['id']);
$r['application_id']=$newAppId;
$r['applicant_id']=$newApplicantId;
$curdb->insert("pay_trans", $r);
$trans_id=$curdb->insert_id();
$curdb->update("applications", ['pay_trans_id'=>$trans_id], ['id'=>$newAppId]);
}
}
if($j){
foreach($j as $r){
unset($r['id']);
$r['application_id']=$newAppId;
$curdb->insert("cash_collections", $r);
}
}
/** Removing */
$this->db->query("SET foreign_key_checks=0");
$this->db->delete("applications", ['id'=>$app_id]);
$this->db->delete("applicants", ['id'=>$applicant_id]);
$c=$this->db->delete("admin_remarks", ['application_id'=>$app_id]);
$d=$this->db->delete("adm_form", ['app_id'=>$app_id]);
$e=$this->db->delete("applicants_edu_details", ['applicant_id'=>$applicant_id]);
$f=$this->db->delete("applicants_visit_history", ['applicant_id'=>$applicant_id]);
$g=$this->db->delete("applications_remarks", ['application_id'=>$app_id]);
$h=$this->db->delete("application_assign_history", ['application_id'=>$app_id]);
$i=$this->db->delete("pay_trans", ['application_id'=>$app_id]);
$j=$this->db->delete("cash_collections", ['application_id'=>$app_id]);
/** \ */
/** Send Email */
$this->common->sendSystemIdGenMsg($newAppId, $curdb);
/** / */
$res['success']=true;
$res['msg']="Successfully moved to current year";
}catch(Exception $e){
$err=TRUE;
$res['msg']=$e->getMessage();
}
if($this->db->trans_status() === FALSE) {
$err=TRUE;
}
if($err){
$this->db->trans_rollback();
return FALSE;
}else{
$this->db->trans_commit();
return $res;
}
}
function susatExamStatus($id)
{
$data = $this->db->get_where("applications", array('id'=>$id))->row_array();
return $data;
}
function getIsdCodeByCode($country_code)
{
$data = $this->db->get_where("master_countrys", array('code'=>$country_code))->row_array();
return $data;
}
}
//End of file
Kontol Shell Bypass