File: /home/cafsindia/crm_cafsindia_com/application/models/Mis_model_12sep2018.php
<?php
class mis_model extends CI_Model{
/*
Perform a search on items
*/
public function search($search, $filters, $filters_mis, $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');
$this->db->distinct('leads_id');
if($filters_mis === "Yet_to_start"){
$this->db->select("customers.cust_id,lead_type_info.lead_id as leads_id,customers.cust_name,customers.cust_mobile,lead_type.lead_type,lead_status.statusname,prospect_level.prospect_name,lead_type_info.ncd,lead_type_info.expected_login,lead_type_info.remarks,lead_type_info.created_by,lead_type_info.current_control,lead_type_info.created_date,category.cat_name,crm.first_name as crmName, rm.first_name as rmName,re.first_name as reName");
}else{
$this->db->select("count(customer_product_id) as count,customers.cust_id,lead_type_info.lead_id as leads_id,customers.cust_name,customers.cust_mobile,lead_type.lead_type,lead_status.statusname,prospect_level.prospect_name,lead_type_info.ncd,lead_type_info.expected_login,lead_type_info.remarks,lead_type_info.created_by,lead_type_info.current_control,lead_type_info.created_date,category.cat_name,crm.first_name as crmName, rm.first_name as rmName,re.first_name as reName,pro.login_date as log_date,sum(net_premium) as net_premium,issuance_status,policy_no,rider_net_premium");
}
$this->db->from('lead_type_info');
$this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
if($filters_mis !== "Yet_to_start"){
$this->db->join('leads_product pro', "pro.lead_info_id = lead_type_info.lead_type_id and pro.status = '1'" );
$this->db->join('happy_calling', 'happy_calling.call_product_id = pro.customer_product_id');
}
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
$this->db->join('lead_type', 'lead_type.lead_type_id = lead_type_info.lead_type');
$this->db->join('lead_status', 'lead_status.status_id = lead_type_info.lead_status');
$this->db->join('prospect_level', 'prospect_level.prospect_id = lead_type_info.prospect_level');
$this->db->join('category', 'category.cat_id = lead_type_info.category');
$this->db->join('employees','employees.id = lead_type_info.created_by');
$this->db->join('people crm', 'crm.person_id = employees.person_id');
$this->db->join('people rm' ,'rm.person_id = lead_type_info.rm_name','left');
$this->db->join('people re' ,'re.person_id = lead_type_info.re_name','left');
$this->db->group_start();
$this->db->like('customers.cust_name', $search);
$this->db->or_like('customers.cust_mobile', $search);
//$this->db->or_like('lead_status.statusname', $search);
if($filters_mis !== "Yet_to_start"){
$this->db->or_like('happy_calling.policy_no', $search);
}
//$this->db->or_like('prospect_level.prospect_name', $search);
$this->db->or_like('category.cat_name', $search);
$this->db->or_like('rm.first_name', $search);
$this->db->or_like('re.first_name', $search);
$this->db->or_like('crm.first_name', $search);
if(strpos($search, '-') !== false){
$this->db->or_like('lead_type_info.ncd', date('Y-m-d',strtotime($search)));
$this->db->or_like('lead_type_info.created_date', date('Y-m-d',strtotime($search)));
if($filters_mis === "Yet_to_start"){
if(!$sort){
$sort = "leads_id";
}
}else{
$this->db->or_like('pro.login_date', date('Y-m-d',strtotime($search)));
if(!$sort){
$sort = "log_date";
}
}
}
$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);
}
$this->db->group_start();
$this->db->where('lead_type_info.lead_status', 8)->or_where('lead_type_info.lead_status', 11)->or_where('lead_type_info.prospect_level', 4);
$this->db->group_end();
$this->db->where('lead_type_info.status', 1);
$this->db->where('lead_type_info.category!=', 4);
//$this->db->where('pro.status', 1);
//$this->db->or_where('pro.status !=', 0);
if($filters_mis === "Yet_to_start"){
$this->db->where('current_control', "MIS");
}else
if($filters_mis === "Pending"){
$this->db->where('current_control', "HC");
$this->db->group_start();
$this->db->where('issuance_status!=', "Medical Declined");
$this->db->where('issuance_status!=', "Cheque Dishonor");
$this->db->where('issuance_status!=', "Medical Freelook");
$this->db->group_end();
$this->db->group_start();
$this->db->where('DATE_FORMAT(pro.login_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date))->or_where('pro.login_date', NULL,true);
$this->db->group_end();
}else
if($filters_mis === "Dropped"){
$this->db->where('current_control', "HC");
$this->db->group_start();
$this->db->where('issuance_status', "Medical Declined")->or_where('issuance_status', "Cheque Dishonor")->or_where('issuance_status', "Freelook");
$this->db->group_end();
$this->db->group_start();
$this->db->where('DATE_FORMAT(pro.login_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date))->or_where('pro.login_date', NULL,true);
$this->db->group_end();
}else{
$this->db->where('current_control', "Completed");
$this->db->group_start();
$this->db->where('issuance_status!=', "Medical Declined");
$this->db->where('issuance_status!=', "Cheque Dishonor");
$this->db->where('issuance_status!=', "Medical Freelook");
$this->db->group_end();
$this->db->group_start();
$this->db->where('DATE_FORMAT(pro.login_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date))->or_where('pro.login_date', NULL,true);
$this->db->group_end();
}
$this->db->group_by('leads_id');
$this->db->order_by($sort, $order);
if($rows > 0){
$this->db->limit($rows, $limit_from);
}
return $this->db->get();
//echo $this->db->last_query();
}
/* Get number of rows */
public function get_found_rows($search, $filters, $filters_mis){
return $this->search($search, $filters, $filters_mis)->num_rows();
}
//Get Company code
public function get_login_code(){
$this->db->from('code');
$this->db->where('status', 1);
return $this->db->get();
}
/*
Gets information about a particular item
*/
public function get_info($lead_id){
$this->db->from('leads');
$this->db->join('lead_type_info', 'lead_type_info.lead_id = leads.lead_id');
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
$this->db->where('lead_type_info.lead_id', $lead_id);
$query = $this->db->get();
if($query->num_rows() == 1){
return $query->row();
}
return false;
}
//Get Company
public function get_company($lead_id){
$this->db->from('vendor');
$this->db->where('deleted', 0);
return $this->db->get();
}
public function get_company_list($cat_id){
$this->db->from('vendor');
$this->db->where('category_id',$cat_id);
$this->db->where('deleted', 0);
return $this->db->get()->result();
}
public function get_code_list($cat_id){
$this->db->from('code');
$this->db->where('category_id',$cat_id);
$this->db->where('status', 1);
return $this->db->get()->result();
}
public function get_company_product($company_id){
$this->db->from('items');
$this->db->where('vendorcompanyname',$company_id);
$this->db->where('deleted', 0);
return $this->db->get()->result();
}
public function get_mis_product(){
$this->db->from('items');
$this->db->where('deleted', 0);
return $this->db->get();
}
//get_bank_info
public function get_bank_info(){
$this->db->from('banks');
$this->db->where('status', 1);
return $this->db->get()->result();
}
public function get_fund_name_list(){
$this->db->from('fund_name');
$this->db->where('status', 0);
return $this->db->get()->result();
}
public function get_amc($fund_id=FALSE){
$this->db->from('amc');
if($fund_id){
$this->db->where('fund_id', $fund_id);
}
$this->db->where('status', 0);
return $this->db->get()->result();
}
/*
update a customer
*/
public function lead_update_customer($customer_data, $cust_id){
$this->db->where('cust_id', $cust_id);
return $this->db->update('customers', $customer_data);
}
/*
Insert or update a family
*/
public function update_customer_info($cust_info,$lead_id){
$this->db->where('lead_id', $lead_id);
$this->db->where('fam_relation', 'Self');
$this->db->update('family', $cust_info);
}
public function lead_save_family($family_data,$lead_id,$family_id){
if($family_id === "-1"){
$this->db->insert('family', $family_data);
$family_id = $this->db->insert_id();
}else{
$this->db->where('family_id', $family_id);
$this->db->update('family', $family_data);
}
return $this->get_family_list($lead_id);
}
public function get_family_list($lead_id){
$this->db->from('family');
$this->db->where('lead_id',$lead_id);
$this->db->where('deleted',0);
return $this->db->get()->result();
}
public function lead_save_product($product_data,$lead_id,$customer_product_id){
$count = $this->is_product_exist($customer_product_id);
if($count === 0){
$this->db->insert('leads_product', $product_data);
}else{
$this->db->where('customer_product_id', $customer_product_id);
$this->db->update('leads_product', $product_data);
}
return $this->get_product_list($lead_id);
}
public function get_product_list($lead_id){
$this->db->select('*,vendor.vendorcompanyname as comp');
$this->db->from('leads_product');
$this->db->join('happy_calling', 'happy_calling.call_product_id = leads_product.customer_product_id','left');
$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('lead_type_info', 'lead_type_info.lead_type_id = leads_product.lead_info_id');
$this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
$this->db->where('leads.lead_id',$lead_id);
$this->db->where('leads_product.status', 1);
return $this->db->get()->result();
//echo $this->db->last_query();
}
public function get_hc_product_list($lead_id){
$this->db->select('*,vendor.vendorcompanyname as comp');
$this->db->from('happy_calling');
$this->db->join('leads_product', 'leads_product.customer_product_id = happy_calling.call_product_id','left');
$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->where('happy_calling.call_lead_id',$lead_id);
$this->db->where('leads_product.status', 1);
return $this->db->get()->result();
//echo $this->db->last_query();
}
public function get_product($product_id){
$this->db->from('leads_product');
$this->db->where('customer_product_id',$product_id);
return $this->db->get()->result();
}
public function get_family_person($family_id){
$this->db->from('family');
$this->db->where('family_id',$family_id);
$this->db->where('deleted',0);
return $this->db->get()->result();
}
public function remove_family_person($family_id,$family_data){
$this->db->where('family_id',$family_id);
return $this->db->update('family', $family_data);
}
public function is_product_exist($product_id){
$this->db->from('leads_product');
$this->db->where('customer_product_id',$product_id);
return $this->db->get()->num_rows();
}
public function update_completed_status($count,$product_id,$lead_info_id){
$data = array('completed' => $count);
$hc = "HC";
$cc_data = array('current_control' => $hc);
$this->db->where('customer_product_id', $product_id);
$result = $this->db->update('leads_product', $data);
$this->db->where('lead_type_id', $lead_info_id);
$result = $this->db->update('lead_type_info', $cc_data);
if($this->hc_exists($lead_info_id, $product_id) === 0){
$call_data['call_lead_id'] = $lead_info_id;
$call_data['call_product_id'] = $product_id;
$this->db->insert('happy_calling', $call_data);
}
return $result;
}
public function hc_exists($lead_id, $product_id){
$this->db->from('happy_calling');
$this->db->where('call_lead_id',$lead_id);
$this->db->where('call_product_id',$product_id);
return $this->db->get()->num_rows();
}
public function delete_product($product_id,$product_data,$lead_id){
$this->db->where('customer_product_id',$product_id);
return $this->db->update('ospos_leads_product', $product_data);
}
public function delete_doc($cust_id,$id){
if($id === "1"){
$data = array('id_proof' => '');
}else
if($id === "2"){
$data = array('address_proof' => '');
}else
if($id === "3"){
$data = array('income_proof' => '');
}else
if($id === "4"){
$data = array('bank_cheque' => '');
}else
if($id === "5"){
$data = array('mf_pan' => '');
}
$this->db->where('cust_id',$cust_id);
return $this->db->update('ospos_customers', $data);
}
public function get_product_gst($product){
$this->db->select('gst');
$this->db->from('items');
$this->db->where('product_id',$product);
return $this->db->get()->row();
}
//HC Start
public function exists($lead_id, $product_id){
$this->db->from('happy_calling');
$this->db->where('call_lead_id',$lead_id);
$this->db->where('call_product_id',$product_id);
return $this->db->get()->num_rows();
}
public function save_call($call_data, $lead_id, $product_id){
$count = $this->exists($lead_id, $product_id);
if($count === 1){
$call_data['updated_by'] = $this->session->userdata('emp_id');
$call_data['updated_date'] = date('Y-m-d h:i:s');
$this->db->where('call_lead_id', $lead_id);
$this->db->where('call_product_id', $product_id);
$this->db->update('happy_calling', $call_data);
}else{
$this->db->insert('happy_calling', $call_data);
//return $call_data['call_id'] = $this->db->insert_id();
}
return $this->get_hc_product_list($lead_id);
}
public function get_category($product_id){
$this->db->from('leads_product');
$this->db->where('leads_product.customer_product_id', $product_id);
return $this->db->get()->row()->product_category;
}
public function get_hc_product($call_lead_id,$call_product_id){
$cat = $this->get_category($call_product_id);
if($cat !== "4"){
$this->db->select('*,vendor.vendorcompanyname as company,lead_type_info.created_date as created');
}else{
$this->db->select('*,lead_type_info.created_date as created');
}
$this->db->from('happy_calling');
$this->db->join('leads_product', 'leads_product.customer_product_id = happy_calling.call_product_id');
$this->db->join('lead_type_info', 'lead_type_info.lead_type_id = happy_calling.call_lead_id');
$this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
if($cat !== "4"){
$this->db->join('vendor', 'vendor.vendor_id = leads_product.company');
$this->db->join('items', 'items.product_id = leads_product.product');
}
$this->db->where('happy_calling.call_lead_id', $call_lead_id);
$this->db->where('happy_calling.call_product_id', $call_product_id);
return $this->db->get()->result();
}
public function get_rm_name($person_id){
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->where('employees.id', $person_id);
return $this->db->get()->row()->first_name;
}
public function can_update($product_id){
//issuance_status,document_issue_status,confirmation
$this->db->from('happy_calling');
$this->db->where('call_product_id', $product_id);
$result = $this->db->get()->row();
$issuance_status = $result->issuance_status;
$document_issue_status = $result->document_issue_status;
$confirmation = $result->confirmation;
$rm_comitted_status = $result->rm_comitted_status;
//&& ($rm_comitted_status === "1")
if(($issuance_status === "Issued") && ($document_issue_status === "Dispatched") && ( $confirmation === "Received")){
return true;
}else{
return false;
}
}
public function update_completed($count,$product_id,$lead_info_id){
$cc_data = array('current_control' => "Completed");
$this->db->where('lead_type_id', $lead_info_id);
$this->db->update('lead_type_info', $cc_data);
$data = array('hc_status' => $count);
$this->db->where('call_lead_id', $lead_info_id);
$this->db->where('call_product_id', $product_id);
$result = $this->db->update('happy_calling', $data);
if($this->renewal_exists($lead_info_id, $product_id) === 0){
$product_info = $this->get_product_info($lead_info_id, $product_id);
$category = $product_info[0]['product_category'];
$issued_date = $product_info[0]['issued_date'];
$mode = $product_info[0]['mode'];
$sum_assured = $product_info[0]['sum_assured'];
$rider = $product_info[0]['rider'];
$rider_name = $product_info[0]['rider_name'];
$net_premium = $product_info[0]['net_premium'];
$gst = $product_info[0]['gst'];
$total_premium = $product_info[0]['total_premium'];
$rider_net_premium = $product_info[0]['rider_net_premium'];
$rider_gst = $product_info[0]['rider_gst'];
$rider_total_premium = $product_info[0]['rider_total_premium'];
$overall_net_premium = $product_info[0]['overall_net_premium'];
$overall_total_premium = $product_info[0]['overall_total_premium'];
$policy_no = $product_info[0]['policy_no'];
$paid_by = $product_info[0]['paid_by'];
if($mode === "1"){ //Yearly
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 1 year"));
}else
if($mode === "2"){ //Semi-Annual
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 6 months"));
}else
if($mode === "3"){ //Monthly
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 1 month"));
}else
if($mode === "4"){ //Quarterly
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 3 month"));
}
//Renewal Calculations
if($category === "2"){
$gst = $gst / 2;
$gstVal = ($gst/100)*($net_premium);
$renewal_total_premium = $gstVal + $net_premium;
$riderval = ($rider_gst/100)*($rider_net_premium);
$rider_total_premium = $riderval + $rider_total_premium;
$overall_net = round($net_premium + $rider_net_premium);
$overall_total = round($renewal_total_premium + $rider_total_premium);
}else
if($category === "3"){
$renewal_total_premium = $total_premium;
$overall_net = $net_premium;
$overall_total = $total_premium;
}
//Renewal Insert
$renewal['lead_id'] = $lead_info_id;
$renewal['product_id'] = $product_id;
$renewal['renewal_date'] = date("Y-m-d", strtotime($issued_date));
$renewal['due_date'] = date("Y-m-d", strtotime($renewal_date));
$renewal['sum_assured'] = $sum_assured;
$renewal['paying_year'] = 1;
$renewal['ren_rider'] = $rider;
$renewal['ren_rider_name'] = $rider_name;
$renewal['ren_net_premium'] = $net_premium;
$renewal['ren_gst'] = $gst;
$renewal['ren_total_premium'] = $renewal_total_premium;
$renewal['ren_rider_net_premium'] = $rider_net_premium;
$renewal['ren_rider_gst'] = $rider_gst;
$renewal['ren_rider_total_premium'] = $rider_total_premium;
$renewal['ren_overall_net_premium'] = $overall_net;
$renewal['ren_overall_total_premium']= $overall_total;
$renewal['mode'] = $mode;
$this->db->insert('renewal', $renewal);
$renewal_id = $this->db->insert_id();
if($category === "2"){
$gst = $gst * 2;
}
//Renewal Log Insert
$renewal_log['renewal_id'] = $renewal_id;
$renewal_log['log_policy_no'] = $policy_no;
$renewal_log['log_paying_year'] = 1;
$renewal_log['log_mode'] = $mode;
$renewal_log['log_sum_assured'] = $sum_assured;
$renewal_log['log_renewal_date'] = date("Y-m-d", strtotime($issued_date));
$renewal_log['log_renew_status'] = "Paid";
$renewal_log['log_renew_paid_by'] = $paid_by;
$renewal_log['log_net_premium'] = $net_premium;
$renewal_log['log_gst'] = $gst;
$renewal_log['log_total_premium'] = $total_premium;
$renewal_log['log_rider_net_premium'] = $rider_net_premium;
$renewal_log['log_rider_gst'] = $rider_gst;
$renewal_log['log_rider_total_premium'] = $rider_total_premium;
$renewal_log['log_overall_net_premium'] = $overall_net_premium;
$renewal_log['log_overall_total_premium'] = $overall_total_premium;
$renewal_log['log_due_date'] = date("Y-m-d", strtotime($renewal_date));
$this->db->insert('renewal_log', $renewal_log);
}else{
//Renewal Update
$product_info = $this->get_product_info($lead_info_id, $product_id);
$issued_date = $product_info[0]['issued_date'];
$mode = $product_info[0]['mode'];
if($mode === "1"){ //Yearly
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 1 year"));
}else
if($mode === "2"){ //Semi-Annual
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 6 months"));
}else
if($mode === "3"){ //Monthly
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 1 month"));
}else
if($mode === "4"){ //Quarterly
$renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 3 month"));
}
$renewal['due_date'] = date("Y-m-d", strtotime($renewal_date));
$renewal['updated_by'] = $this->session->userdata('emp_id');
$renewal['updated_date'] = date('Y-m-d h:i:s');
$this->db->where('lead_id', $lead_info_id);
$this->db->where('product_id', $product_id);
$this->db->update('renewal', $renewal);
}
return $result;
}
public function get_product_info($lead_id, $product_id){
$this->db->from('happy_calling');
$this->db->join('leads_product', 'leads_product.customer_product_id = happy_calling.call_product_id');
$this->db->where('call_lead_id', $lead_id);
$this->db->where('call_product_id', $product_id);
return $this->db->get()->result_array();
}
public function renewal_exists($lead_id, $product_id){
$this->db->from('renewal');
$this->db->where('lead_id',$lead_id);
$this->db->where('product_id',$product_id);
return $this->db->get()->num_rows();
}
public function view_login($cust_id){
$login_query = $this->db->query("SELECT *,ospos_happy_calling.issuance_status as iss_status,ospos_lead_type_info.created_by as owner,ospos_vendor.vendorcompanyname as companyname from ospos_customers inner 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 left join ospos_happy_calling on ospos_happy_calling.call_product_id = ospos_leads_product.customer_product_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 update_freelook($count,$product_id,$lead_info_id){
$this->db->where_in('lead_type_id', $lead_info_id);
$this->db->update('lead_type_info', array('status' => 0));
$this->db->where_in('customer_product_id', $product_id);
$this->db->update('leads_product', array('status' => 0));
$this->db->where_in('call_lead_id', $lead_info_id);
return $this->db->update('happy_calling', array('deleted' => 1));
}
*/
}
?>