File: /home/cafsindia/crm_cafsindia_com/application/models/Renewal_model.php
<?php
class renewal_model extends Person
{
public function search($search, $filters, $rows = 0, $limit_from = 0, $sort = '', $order = 'asc'){
$start_date = $filters['start_date'];
$end_date = $filters['end_date'];
$emp_id = $this->session->userdata('emp_id');
$category = $this->session->userdata('emp_category');
$role_id = $this->session->userdata('emp_role');
if(!$sort){
$sort = "renewal.renewal_date";
}
$this->db->select('lead_type_info.lead_id as leads_id,customers.cust_id,customers.cust_name,customers.cust_mobile,customers.cust_address,prospect_level.prospect_name,renewal.ncd,lead_type_info.remarks,lead_type_info.created_by,lead_type_info.current_control,category.cat_name,renewal.due_date as renewal_date,renewal.renewal_id,product_name,renew_status,overall_total_premium,rm_name,renewal.policy_no as new_policy,happy_calling.policy_no as policy,renewal.sum_assured,paying_year,happy_calling.issued_date,vendor.vendorcompanyname as vendor,net_premium');
$this->db->from('renewal');
$this->db->join('lead_type_info','lead_type_info.lead_type_id = renewal.lead_id');
$this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
$this->db->join('leads_product','leads_product.customer_product_id = renewal.product_id');
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
$this->db->join('prospect_level', 'prospect_level.prospect_id = lead_type_info.prospect_level');
$this->db->join('category', 'category.cat_id = leads_product.product_category');
$this->db->join('vendor', 'vendor.vendor_id = leads_product.company');
$this->db->join('items', 'items.product_id = leads_product.product');
$this->db->join('happy_calling', 'happy_calling.call_product_id = leads_product.customer_product_id');
$this->db->group_start();
$this->db->like('cust_name', $search);
$this->db->or_like('customers.cust_mobile', $search);
$this->db->or_like('product_name', $search);
$this->db->or_like('happy_calling.policy_no', $search);
$this->db->or_like('net_premium', $search);
$this->db->or_like('renewal.renew_status', $search);
$this->db->or_like('prospect_level.prospect_name', $search);
$this->db->or_like('renewal.ncd', date('Y-m-d',strtotime($search)));
$this->db->or_like('renewal.due_date', date('Y-m-d',strtotime($search)));
$this->db->group_end();
$cat = array();
if($filters['General_Insurance'] != FALSE){
$cat[] = 1;
}
if($filters['Portfolio'] != FALSE){
$cat[] = 2;
}
if($filters['Health_Insurance'] != FALSE){
$cat[] = 3;
}
if($filters['Mutual_Funds'] != FALSE){
$cat[] = 4;
}
if($filters['Term_Plan'] != FALSE){
$cat[] = 5;
}
if(!empty($cat)) {
$this->db->where_in('lead_type_info.category', $cat);
}
if($role_id === "5"){
$this->db->group_start();
$this->db->where("lead_type_info.created_by",$emp_id)->or_where("lead_type_info.rm_name",$emp_id);
$this->db->group_end();
}
$this->db->group_start();
$this->db->where('DATE_FORMAT(due_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$this->db->group_end();
$this->db->where('lead_type_info.status', 1);
$this->db->where('leads_product.status', 1);
$this->db->order_by($sort, $order);
if($rows > 0){
$this->db->limit($rows, $limit_from);
}
return $this->db->get();
//echo $this->db->last_query();
}
public function get_found_rows($search, $filters){
return $this->search($search, $filters)->num_rows();
}
public function get_info($renewal_id){
$this->db->select('*,renewal.mode as renew_mode,happy_calling.issued_date as issued,renewal.client_status as client_status,renewal.sum_assured,renewal.remarks as remarks,lead_type_info.created_by as source');
$this->db->from('renewal');
$this->db->join('lead_type_info','lead_type_info.lead_type_id = renewal.lead_id');
$this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
$this->db->join('leads_product','leads_product.customer_product_id = renewal.product_id');
$this->db->join('happy_calling','happy_calling.call_product_id = renewal.product_id');
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
$this->db->join('family', 'family.family_id = leads_product.proposer');
$this->db->where('renewal.renewal_id', $renewal_id);
$query = $this->db->get();
if($query->num_rows() === 1){
return $query->row();
}else{
$item_obj = new stdClass();
foreach($this->db->list_fields('lead_type_info') as $field){
$item_obj->$field = '';
}
return $item_obj;
}
}
public function get_company(){
$this->db->from('vendor');
$this->db->where('deleted', 0);
return $this->db->get();
}
public function get_product(){
$this->db->from('items');
$this->db->where('deleted', 0);
return $this->db->get();
}
public function save($renewal_data,$renewal_id){
$count = $this->exists($renewal_id);
if($count === 1){
$this->db->where('renewal_id', $renewal_id);
return $this->db->update('renewal', $renewal_data);
}else{
$this->db->insert('renewal', $renewal_data);
return $call_data['renewal_id'] = $this->db->insert_id();
}
}
public function save_log($renewal_log_data,$renewal_id){
$policy_no = $renewal_log_data['policy_no'];
$sts = $renewal_log_data['renew_status'];
$log_info = $this->log_exists($renewal_id,$policy_no,$sts);
$count = $log_info->num_rows();
$log_Rslt = $log_info->row();
$log_id = $log_Rslt->renewal_log_id;
if($sts === "Renewed"){
if($count === 1){
$this->db->where('renewal_log_id', $log_id);
return $this->db->update('renewal_log', $renewal_log_data);
}else{
return $this->db->insert('renewal_log', $renewal_log_data);
}
}else
if($sts === "Paid"){
/* Only one policy number so can't check this */
if($count === 1){
$this->db->where('renewal_id', $renewal_id);
return $this->db->update('renewal_log', $renewal_log_data);
}else{
return $this->db->insert('renewal_log', $renewal_log_data);
}
}else{
return $this->db->insert('renewal_log', $renewal_log_data);
}
}
public function update_next_renewal($renewal_id,$mode){
$renewal_info = $this->get_renewal_info($renewal_id);
$renewal_date = $renewal_info->due_date;
$paying_year = $renewal_info->paying_year;
//$paying_year = $paying_year +1;
if($mode === "1"){ //Yearly
$next_renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($renewal_date)) . " + 1 year"));
}else
if($mode === "2"){ //Semi-Annual
$next_renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($renewal_date)) . " + 6 months"));
}else
if($mode === "3"){ //Monthly
$next_renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($renewal_date)) . " + 1 month"));
}else
if($mode === "4"){ //quaterly
$next_renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($renewal_date)) . " + 3 month"));
}
$next_renewal_date = date("Y-m-d", strtotime($next_renewal_date));
$this->db->where('renewal_id', $renewal_id);
$this->db->update('renewal',array('due_date' => $next_renewal_date,'paying_year' => $paying_year));
return $next_renewal_date;
}
public function update_next_HTH_renewal($renewal_id,$due_date,$paying_year){
//$next_renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($to)) . " + 1 year"));
$next_renewal_date = date("Y-m-d", strtotime($due_date));
$this->db->where('renewal_id', $renewal_id);
return $this->db->update('renewal',array('due_date' => $next_renewal_date,'paying_year' => $paying_year));
}
public function exists($renewal_id){
$this->db->from('renewal');
$this->db->where('renewal_id',$renewal_id);
return $this->db->get()->num_rows();
}
public function log_exists($renewal_id,$policy_no,$sts){
$this->db->from('renewal_log');
$this->db->where('renewal_id',$renewal_id);
$this->db->where('log_policy_no',$policy_no);
$this->db->where('log_renew_status',$sts);
return $this->db->get();
}
public function get_renewal_info($renewal_id){
$this->db->from('renewal');
$this->db->where('renewal_id',$renewal_id);
return $this->db->get()->row();
}
public function get_rm_name($rm_name){
$this->db->from('people');
$this->db->where('person_id',$rm_name);
$final_rm_name = $this->db->get()->row();
return $final_rm_name->first_name;
}
public function get_leader($tl_n){
$qry = $this->db->query("SELECT * from ospos_team where find_in_set('$tl_n',teammembers)");
$tl_name = $qry->result_array();
$tl = $tl_name[0]['teamleader'];
if($tl){
return $this->get_tl_name($tl);
}else{
return $this->get_tl_name($tl_n);
}
}
public function get_family_list($renew_id){
$this->db->from('family');
$this->db->join('lead_type_info','lead_type_info.lead_id = family.lead_id');
$this->db->join('leads_product','leads_product.lead_info_id = lead_type_info.lead_type_id');
$this->db->join('renewal','renewal.product_id = leads_product.customer_product_id');
$this->db->where('renewal.renewal_id',$renew_id);
$this->db->where('deleted',0);
return $this->db->get()->result();
}
public function get_family($renew_id){
$this->db->from('family');
$this->db->join('lead_type_info','lead_type_info.lead_id = family.lead_id');
$this->db->join('leads_product','leads_product.lead_info_id = lead_type_info.lead_type_id');
$this->db->join('renewal','renewal.product_id = leads_product.customer_product_id');
$this->db->where('renewal.renewal_id',$renew_id);
$this->db->where('deleted',0);
return $this->db->get();
}
public function get_tl_name($name){
$this->db->from('people');
$this->db->where('person_id',$name);
$final_tl_name = $this->db->get()->row();
$leader = $final_tl_name->first_name;
return $leader;
}
public function view_history($renewal_id){
$this->db->from('renewal_log');
$this->db->join('renewal','renewal.renewal_id = renewal_log.renewal_id');
$this->db->join('leads_product','leads_product.customer_product_id = renewal.product_id');
$this->db->group_start();
$this->db->where('log_renew_status', "Paid")->or_where("log_renew_status","Renewed");
$this->db->group_end();
$this->db->where('renewal_log.renewal_id', $renewal_id);
//$this->db->group_by('renewal_id');
return $this->db->get()->result_array();
//echo $this->db->last_query();
}
public function get_source_name($source){
$this->db->from('employees');
$this->db->join('people','people.person_id = employees.person_id');
$this->db->where('id',$source);
$reporting = $this->db->get()->row();
$tl_id = $reporting->reporting;
$this->db->from('employees');
$this->db->join('people','people.person_id = employees.person_id');
$this->db->where('id',$tl_id);
$tl = $this->db->get()->row();
$tl_name = $tl->first_name;
return $tl_name;
}
public function view_login($cust_id){
$login_query = $this->db->query("SELECT *,ospos_lead_type_info.created_by as owner,ospos_vendor.vendorcompanyname as companyname from ospos_customers left join ospos_leads on ospos_leads.cust_id = ospos_customers.cust_id inner join ospos_lead_type_info on ospos_lead_type_info.lead_id = ospos_leads.lead_id inner join ospos_leads_product on lead_info_id = lead_type_id inner join ospos_vendor on ospos_vendor.vendor_id = ospos_leads_product.company inner join ospos_items on ospos_items.product_id = ospos_leads_product.product inner join ospos_happy_calling on ospos_happy_calling.call_lead_id = ospos_leads_product.lead_info_id where ospos_customers.cust_id = '$cust_id' and ospos_leads_product.status = '1' and ospos_lead_type_info.status = '1' GROUP BY customer_product_id");
return $login_query->result_array();
}
public function cust_info($cust_id){
$cust_query = $this->db->query("SELECT * from ospos_customers where cust_id = '$cust_id'");
return $cust_query->result_array();
}
public function get_view_family($cust_id){
$cust_query = $this->db->query("SELECT * from ospos_family where cust_id = '$cust_id' and deleted = '0'");
return $cust_query->result_array();
}
public function get_renewal_log($renew_id){
$this->db->from('renewal_log');
$this->db->where('renewal_log.renewal_id', $renew_id);
$this->db->order_by('renewal_log.renewal_log_id', 'DESC');
return $this->db->get()->result();
}
}
?>