ikan Uploader :
Directory : /var/www/html/shardahospital.org/shardalms/api/application/models_old/
Upload File : |
| Current File : /var/www/html/shardahospital.org/shardalms/api/application/models_old/Leads_model.php |
<?php
class Leads_model extends CI_Model {
function set_search_cond($qs, $lvOpt){
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("U.state_id", $state_id);
if($lvOpt['cities'][$state_id]){
$lvOpt['cities'][$state_id][]=0;
$this->db->where_in("U.city_id", $lvOpt['cities'][$state_id]);
}
$this->db->group_end();
}
$this->db->group_end();
}else 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_id", $lvOpt['utm_sources']);
}
if($lvOpt['steps']){
$this->db->where_in("A.step_completed", $lvOpt['steps']);
}
if($lvOpt['payment_modes']){
$lvOpt['payment_modes'][]='';
$this->db->where_in("A.payment_mode", $lvOpt['payment_modes']);
}
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($lvOpt['ps_app_status']){
$this->db->where_in("PSS.DESCR1", $lvOpt['ps_app_status']);
}
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['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']){
$this->db->where_in("A.step_completed", $qs['step_completed']);
}
if($qs['payStatusMode']=='Paid'){
$this->db->where("A.payment_status", 1);
}else if($qs['payStatusMode']=='OnlinePaid'){
$this->db->where("A.payment_mode", 'ONLINE');
}else if($qs['payStatusMode']=='CashPaid'){
$this->db->where("A.payment_mode", 'CASH');
}else if($qs['payStatusMode']=='NotPaid'){
$this->db->where("A.payment_status", 0);
}
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']){
$this->db->where_in("AR.remarks_id", $qs['remarks_id']);
}
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']){
$this->db->where("PSS.status", $qs['ps_status']);
}
if($qs['is_medical']){
$this->db->where("U.is_medical", 1);
}else{
$this->db->where("U.is_medical", 0);
}
}
function lists(){
$md=$this->common->master_data_for_leads();
$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 [];
}
$this->set_search_cond($qs, $lvOpt);
$this->db->select("SQL_CALC_FOUND_ROWS A.id, A.applicant_id, A.application_no, A.step_completed, A.created leadCreated, A.updated leadUpdated, A.step2date,
A.step3date, A.plan_id, P.title program, P.ac_id, P.type_id, U.name, U.email, U.mob,
U.city, U.pincode, U.verified_by, U.state_id, U.lpage_id, U.utm_source, A.payment_status,
A.payment_mode, A.cash_order_id, PTRANS.pay_order_id, PTRANS.txn_id, PTRANS.txn_status,
IF(AR.id IS NULL,0,1) isFeedbackGiven, PSS.ADM_APPL_NBR ps_app_no, PSS.DESCR1 ps_app_status, PSS.status ps_status", FALSE)
->from("applications A")
->join("applicants U", "U.id=A.applicant_id")
->join("master_programs P", "P.id=A.program_id")
->join("pay_trans PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("applications_remarks AR", "AR.application_id=A.id", "LEFT")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by('A.id')
->order_by("A.id", "DESC");
//echo $this->db->get_compiled_select(); die;
$rs=$this->dba->pagedRows($qs['p'], 50, 5);
foreach($rs['result'] as &$r){
$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['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['step2date'] =$r['step2date']?get_date($r['step2date'], true):'N/A';
$r['step3date'] =$r['step3date']?get_date($r['step3date'], true):'N/A';
$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';
}
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['isFeedbackGiven']=(int)$r['isFeedbackGiven'];
$r['receiptPrintUrl']=URL.'common/printReceipt/'.$r['id'];
/*if($r['step_completed']==3){
$r['ps_app']=$this->ps_stu_status($r['application_no']);
if(!$r['ps_app']){
$r['ps_app']=false;
}
}*/
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";
}else{
$r['ps_status']="N/A";
}
}
unset($r['state_id'], $r['lpage_id'], $r['plan_id'], $r['ac_id'], $r['type_id'], $r['utm_source_id']);
}
if($rs['result']){
$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 [];
}
$this->set_search_cond($qs, $lvOpt);
$this->db->select("A.id, A.applicant_id, A.application_no, A.step_completed, A.created AS leadCreated, A.updated AS leadUpdated,
A.step2date, A.step3date, A.plan_id, P.title program, P.ac_id, P.type_id, P.discipline_id,
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.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,
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, passing_year_ug,
ED.university_pg, ED.marking_scheme_id_pg, ED.marks_pg, 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")
->from("applications AS A")
->join("applicants AS U", "U.id=A.applicant_id")
->join("master_programs AS P", "P.id=A.program_id")
->join("applicants_edu_details AS ED", "ED.applicant_id=U.id", "LEFT")
->join("pay_trans AS PTRANS", "PTRANS.id=A.pay_trans_id", "LEFT")
->join("ps_stu_status PSS", "PSS.EMPLID=A.application_no", "LEFT")
->group_by('A.id')
->order_by("A.id", "DESC");
//echo $this->db->get_compiled_select(); die;
$rs=$this->db->get()->result_array();
if($rs){
//$ps_status=$this->ps_stu_status();
}else{
return [];
}
foreach($rs as &$r){
$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']];
$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['step2date']=$r['step2date']?get_date($r['step2date'], true):'N/A';
$r['step3date']=$r['step3date']?get_date($r['step3date'], 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";
}else{
$r['ps_status']="N/A";
}
}
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['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.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")
->from("tmp_applicants AS U")
->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']='0';
$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 delete($id){
$this->db->db_debug=FALSE;
$this->db->delete("pay_trans", array('application_id'=>$id));
return $this->db->delete("applications", array('id'=>$id));
}
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']:'';
}
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 updateLead($post, $appDtl){
$err = FALSE;
$this->db->trans_strict(FALSE);
$this->db->trans_begin();
try{
$dtl=$post['dtl'];
$dtl['id']=(int)$appDtl['applicant_id'];
$applicant_id=$this->common->save(replace_null($dtl), "applicants");
$eduDtl=$post['eduDtl'];
$eduDtl['applicant_id']=$appDtl['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->common->save(replace_null($eduDtl), "applicants_edu_details");
if($appDtl['step_completed']<3){
$app=$post['app'];
$appData=array(
'id'=>(int)$appDtl['id'],
'step_completed'=>2,
'ac_id'=>$app['ac_id'],
'program_id'=>$app['program_id'],
'plan_id'=>$app['plan_id']
);
$this->common->save(replace_null($appData), "applications");
}
}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.applicant_id, A.application_no, A.step_completed, A.created AS leadCreated, A.updated AS leadUpdated, A.step2date, A.step3date, 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, U.name, U.email, U.mob, U.mob2, U.dob, U.gender, U.address,
U.city, U.pincode, S.title AS state, S.code AS stateCode, U.verified_by, U.father_name, U.identity_proof, U.identity_proof_no, U.profile_pic,
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,
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.cash_order_id, A.pay_trans_id, PTRANS.pay_order_id, PTRANS.txn_id, PTRANS.txn_status, PTRANS.txn_amt")
->from("applications AS A")
->join("applicants AS U", "U.id=A.applicant_id")
->join("master_states AS S", "S.id=U.state_id")
->join("landing_pages AS LP", "LP.id=U.lpage_id")
->join("master_program_plans AS PP", "PP.id=A.plan_id")
->join("master_programs AS P", "P.id=PP.program_id")
->join("master_academic_careers AS AC", "AC.id=P.ac_id")
->join("master_program_types AS PT", "PT.id=P.type_id")
->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")
->where("A.id", $id)
->order_by("A.updated", "DESC")->limit(1);
$rs=$this->db->get()->row_array();
$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['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';
}
$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;
}
}
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";
}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";
}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.applicant_id, A.application_no, A.step_completed, A.fee, A.coupan_used, A.coupan_amt, 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")
->from("applications AS A")
->join("master_program_plans AS PP", "A.plan_id=PP.id")
->join("master_programs AS P", "PP.program_id=P.id")
->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['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['netAmt']=($r['fee']-$r['coupan_amt']);
$r['created']=get_date($r['created']);
unset($r['prgAppFee'], $r['planAppFee']);
}
return $r;
}
/** Remarks/Feedback */
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("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();
}
return $rs;
}
function getRemarksFeedback($id=0){
$rs['dtl']=$this->db->select("AR.id, AR.application_id, AR.remarks_id, AR.subremarks, AR.feedback")->from("applications_remarks AR")->where("AR.application_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['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;
}
}
//End of file
Kontol Shell Bypass