<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class member_subscription_model extends CI_Model {
    public function __construct() {
        parent::__construct();
        $this->member_subscript = TBL_BILL_MEMBER_SUBSCRIPE;
        $this->payment = TBL_PAY_TYPE;
        $this->service = TBL_SERVICE;
        $this->service_menu = TBL_SERVICE_MENU;
        $this->paid_type = TBL_PAY;
    }
    
    public function list_all() {
        $select = 'ms.id, ms.receipt_no, ms.member_name, ms.contact_no, s.name as service, s.prefix, ms.amount as ms_amount, ms.status, ms.del_reason';
        $this->db->select($select,FALSE);
        $this->db->join($this->service . ' as s','s.id = ms.service_id');
        #$this->db->where('ms.status !=',2);
        $this->db->order_by('ms.receipt_no','desc');
        return $this->db->get($this->member_subscript . ' as ms')->result();
    }
    
    public function payment_mode_lists() {
        $this->db->where('status',1);
        $this->db->where('menu_id',0);
        return $this->db->get($this->payment)->result();
    }
    
    
    public function get_service_type($type= ''){
        $this->db->where('s.menu_name',$type);
        $this->db->where('s.status',1);
		$this->db->order_by('s.name','asc');
        return $this->db->get($this->service . ' as s')->result();
    }
    
    public function insert($data){
        $this->db->insert($this->member_subscript, $data);
        return $this->db->insert_id();
    }
    
    public function get_amount_by_service_id($id) {
        $this->db->where('id',$id);
        return $this->db->get($this->service)->row();
    }
    
    public function service_type_exists($type) {
        $this->db->where('service_id',$type);
        //$this->db->where('status !=',2);
        $this->db->order_by('receipt_no','desc');
        return $this->db->get($this->member_subscript);
    }
    
    public function update($data,$id){
        $this->db->where('id',$id);
        $this->db->update($this->member_subscript,$data);
        return $this->db->affected_rows();
    }
    
    public function get_by_id($id) {
        $select = 'ms.*, CASE ms.status WHEN 1 THEN "Active" ELSE "Inactive" END as status_str, ms.status AS status';
        $select .= ', CASE ms.entry_fee WHEN 1 THEN "Yes" ELSE "No" END as entry_fee_str';
        $select .= ', CASE ms.maritial_status WHEN 1 THEN "Single" ELSE "Married" END as maritial_status_str';
        $select .= ', CASE ms.sex WHEN 1 THEN "Male" ELSE "Female" END as sex_str, s.name as service, ms.amount as ms_amount, s.prefix';
        $this->db->select($select,FALSE);
        $this->db->join($this->service . ' as s','s.id = ms.service_id','left');
        $this->db->where('ms.id',$id);
        return $this->db->get($this->member_subscript . ' as ms')->row();
    }
    
    public function member_subscript_url_key_exists($url_key, $id) {
        if (!empty($id)) {
            $this->db->where('id !=', $id);
        }
        $this->db->where('url_key', $url_key);
        return $this->db->get($this->member_subscript)->num_rows();
    }
    
    public function insert_payment($data){
        $this->db->insert($this->paid_type,$data);
        return $this->db->insert_id();
    }
    
    public function get_paid_type($menu,$type,$id) {
        $this->db->where('pay_ref_type',$menu);
        $this->db->where('pay_ref_id',$type);
        $this->db->where('pay_type_id',$id);
        $this->db->where('status',1);
        return $this->db->get($this->paid_type)->row();
    }
    
    public function get_paid_type_by_id($menu,$type) {
        $this->db->select('p.pay_amount, pt.img_url, pt.name',FALSE);
        $this->db->join($this->payment . ' as pt','pt.id = p.pay_type_id');
        $this->db->where('p.pay_ref_type',$menu);
        $this->db->where('p.pay_ref_id',$type);
        $this->db->where('p.status',1);
        return $this->db->get($this->paid_type . ' as p')->result();
    }
    
    public function delete_paid_type($type_id,$id) {
        $this->db->where('pay_ref_type',$type_id);
        $this->db->where('pay_ref_id',$id);
        $this->db->delete($this->paid_type);
        return $this->db->affected_rows();
    }
    
    public function update_paid_type($data,$type_id,$id) {
        $this->db->where('pay_ref_type',$type_id);
        $this->db->where('pay_ref_id',$id);
        $this->db->update($this->paid_type,$data);
        return $this->db->affected_rows();
    }
    
    public function total_paid_amount($menu,$type) {
        $this->db->select('SUM(pay_amount) as paidAmount');
        $this->db->where('pay_ref_type',$menu);
        $this->db->where('pay_ref_id',$type);
        $this->db->where('status',1);
        return $this->db->get($this->paid_type)->row();
    }
    
    public function check_mem_no_exists($mem_no,$mem_id) {
        $this->db->where('member_no',$mem_no);
        if(!empty($mem_id))
            $this->db->where('id !=',$mem_id);
        
        $this->db->where('status',1);
        $getData = $this->db->get($this->member_subscript);
        if($getData->num_rows() > 0)
            return 1;
        
        else
            return 0;
    }
    
    public function get_paidID($menu,$type,$id) {
        $this->db->where('pay_ref_type',$menu);
        $this->db->where('pay_ref_id',$type);
        $this->db->where('pay_type_id',$id);
        $this->db->where('status',1);
        return $this->db->get($this->paid_type)->result();
    }
    
    public function delete_payment_by_paid_id($id) {
        $this->db->where_in('id',$id);
        $this->db->delete($this->paid_type);
        return $this->db->affected_rows();
    }
}
?>