<?php

/* 
 * Author       : Kowsalya K
 * Purpose      : Model for Donation Billing
 * Created On   : 2016-08-09
 */

class Donation_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->service = TBL_SERVICE;
        $this->donation = TBL_BILL_DONATION;
        $this->donation_pledge = TBL_BILL_DONATION_PLEDGE;
        $this->donation_transaction = TBL_BILL_DONATION_TRANSACTION;
        $this->payment_type = TBL_PAY_TYPE;
        $this->payment = TBL_PAY;
        $this->god = TBL_GOD_NAME;
        $this->stars = TBL_STAR;
        $this->prasadam = TBL_PRASADAM;
        $this->refund = TBL_REFUND;
    }
    public function list_all() {
        $select = 'd.id, d.receipt_no, d.name, d.tamil_name, d.contact_no, s.name as service, s.prefix, d.amount as d_amount, dp.d_id, d.created_on, d.status, d.del_reason';
        $this->db->select($select,FALSE);
        $this->db->join($this->donation_pledge . ' as dp','dp.d_id = d.id','left');
        $this->db->join($this->service . ' as s','s.id = d.service_id');
        #$this->db->where('d.status !=',2);
        $this->db->order_by('d.receipt_no','desc');
        return $this->db->get($this->donation . ' as d')->result();
    }
    
     public function get_pledge_data() {
        $this->db->select('s.name as service_name, s.prefix, dp.*, dp.pledge_amount as total_amount',FALSE);
        $this->db->join($this->service . ' as s','s.id = dp.service_id');
        //$this->db->where('dp.status',1);
        $this->db->order_by('dp.id','desc');
        return $this->db->get($this->donation_pledge . ' as dp')->result();
    }
    
    public function donation_pledge_data_by_id($pledge_id) {
        $this->db->where('dp.id',$pledge_id);
        //$this->db->where('dp.status',1);
        $this->db->order_by('dp.id','desc');
        return $this->db->get($this->donation_pledge . ' as dp')->row();
    }
    
    public function get_donation_pledge_transaction($pledge_id='') {
        /* $this->db->join($this->donation_pledge . ' as dp','dp.service_id = dpt.service_id AND dp.name = dpt.name AND dp.contact_no = dpt.contact_no AND dp.pledge_status'); */
        $this->db->where('dpt.pledge_id',$pledge_id);
        //$this->db->where('dpt.status',1);
        $this->db->order_by('dpt.id','desc');
        return $this->db->get($this->donation_transaction . ' as dpt')->result();
    }
    
    public function get( $tbl_name, $where = array() ) {
        if ( ! is_array($where)) $where = array();
        $where['status'] = 1;
        
        $q = $this->db->get_where( $tbl_name, $where );
        return $q->result_array();
            
    }
    
    public function payment_mode_lists() {
        $this->db->where('status',1);
        $this->db->where('menu_id',0);
        return $this->db->get($this->payment_type)->result();
    }
    
    public function donation_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->donation)->num_rows();
    }
    
    public function service_type_exists($type) {
        $this->db->where('service_id',$type);
        //$this->db->where('status !=',2);
        //$this->db->order_by('receipt_no','desc');
		$this->db->order_by('id','desc');
        return $this->db->get($this->donation);
    }
    
    public function insert($data){
        $this->db->insert($this->donation,$data);
        return $this->db->insert_id();
    }
    
    public function get_pledge_amount_details($type,$name,$contact_no) {
        $this->db->select('dp.*');
        //$this->db->join($this->donation . ' as d','d.pledge_id = dp.id','left');
        $this->db->where('dp.service_id',$type);
        $this->db->where('dp.name',$name);
        $this->db->where('dp.contact_no',$contact_no);
        $this->db->where('dp.pledge_status',1);
        //$this->db->where('dp.status',1);
        return $this->db->get($this->donation_pledge . ' as dp')->num_rows();
    }
    
    public function get_pledge_amount_details_by_data($type,$name,$contact_no) {
        $this->db->select('dp.*,  d.tamil_name, d.email, d.address, d.desc, d.id as donation_id',FALSE);
        $this->db->join($this->donation . ' as d','d.pledge_id = dp.id','left');
        $this->db->where('dp.service_id',$type);
        $this->db->where('dp.name',$name);
        $this->db->where('dp.contact_no',$contact_no);
        $this->db->where('dp.pledge_status',1);
       // $this->db->where('dp.status',1);
        return $this->db->get($this->donation_pledge . ' as dp')->row();
    }
    
    public function get_pledge_transaction_details_by_id($type,$name,$contact_no,$rec_no) {
        $this->db->select('dt.*',FALSE);
        //$this->db->join($this->donation . ' as d','d.name = dp.name AND d.contact_no = dp.contact_no');
        $this->db->where('dt.service_id',$type);
        $this->db->where('dt.name',$name);
        $this->db->where('dt.contact_no',$contact_no);
        //$this->db->where('dt.status',1);
        $this->db->where('dt.receipt_no',$rec_no);
        return $this->db->get($this->donation_transaction . ' as dt')->row();
    }
    
    public function get_balance_amt_by_id($pledge_id,$id) {
        $this->db->select('SUM(dt.amount) as balance_amount',FALSE);
        $this->db->where('dt.pledge_id',$pledge_id);
        $this->db->where('dt.id !=',$id);
        //$this->db->where('dt.status',1);
        $this->db->group_by('dt.pledge_id');
        return $this->db->get($this->donation_transaction . ' as dt')->row();
    }
    
    public function get_pledge_transaction_details($type,$name,$contact_no,$pledge_id) {
        $this->db->select('dt.*',FALSE);
        //$this->db->join($this->donation . ' as d','d.name = dp.name AND d.contact_no = dp.contact_no');
        $this->db->where('dt.service_id',$type);
        $this->db->where('dt.name',$name);
        $this->db->where('dt.contact_no',$contact_no);
        $this->db->where('dt.pledge_type',1);
        $this->db->where('dt.amount_type !=',3);
        //$this->db->where('dt.status',1);
        $this->db->where('dt.pledge_id',$pledge_id);
        return $this->db->get($this->donation_transaction . ' as dt')->result();
    }
    
    public function update($data,$id) {
        $this->db->where('id',$id);
        $this->db->update($this->donation,$data);
        return $this->db->affected_rows();
    }
    
    public function insert_donation_pledge($data) {
        $this->db->insert($this->donation_pledge,$data);
        return $this->db->insert_id();
    }
    
    public function update_donation_pledge($data,$id) {
        $this->db->where('id',$id);
        $this->db->update($this->donation_pledge,$data);
        return $this->db->affected_rows();
    }
    
    public function insert_pledge_transaction($data) {
        $this->db->insert($this->donation_transaction,$data);
        return $this->db->insert_id();
    }
    
    public function insert_payment($data){
        $this->db->insert($this->payment,$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 get_service_type($type= ''){
        $this->db->where('s.menu_name',$type);
        $this->db->where('s.status',1);
        return $this->db->get($this->service . ' as s')->result();
    }
    
    public function get_by_id($id) {
        $select = 'd.*, CASE d.status WHEN 1 THEN "Active" ELSE "Inactive" END as status_str, d.status AS status';
        $select .= ', s.name as service, s.prefix, d.amount as d_amount, dp.pledge_type, dp.amount_type, dp.pledge_amount, dp.amount as dp_amount, dp.pledge_status, dp.id as pledge_id';
        $select .= ', CASE dp.amount_type WHEN 1 THEN "Amount" WHEN 2 THEN "Item" ELSE "Ongoing" END as amount_str';
        $this->db->select($select,FALSE);
        $this->db->join($this->donation_pledge . ' as dp','dp.name = d.name AND dp.contact_no = d.contact_no AND dp.service_id = d.service_id AND d.pledge_id = dp.id','left');
        $this->db->join($this->service . ' as s','s.id = d.service_id');
        $this->db->where('d.id',$id);
        return $this->db->get($this->donation . ' as d')->row();
    }
    public function get_paid_type($menu,$type,$id) {
        $this->db->select('*,SUM(pay_amount) as pay_amount');
        $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->payment)->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_type . ' 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->payment . ' as p')->result();
    }
    
    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->payment,$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->payment)->row();
    }
    
    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->payment);
        return $this->db->affected_rows();
    }
    
    public function get_data_by_service_type($type_id){
        $this->db->where('service_id',$type_id);
        $this->db->where('status !=',2);
        return $this->db->get($this->donation)->result();
    }
    
    public function get_contact_by_service_type($type_id){
        $this->db->where('service_id',$type_id);
        //$this->db->where('status !=',2);
        $this->db->group_by('contact_no');
        return $this->db->get($this->donation)->result();
    }
    
    public function get_all( $id = '', $where = array() ) {
        if( !empty( $id ) ) :
            $where['self.id'] = $id;
        endif;
        
        //$where['self.status'] = 1;
        
        
        $this->db->select( 'self.*, self.status AS status, serv.name AS service_name, serv.prefix' );
        
        $this->db->join( TBL_SERVICE . ' AS serv', 'serv.id = self.service_id', 'left' );
        $q = $this->db->get_where( TBL_BILL_DONATION . ' AS self', $where );
        return $q->result_array();
    }
    
    public function ajax() {
        $post = $this->input->post();
        /*
         * Get all booked details
         */
        $where['self.service_id'] = $post['service_type'];
        //$where['self.status'] = 1;
        
        //if( !empty( $post['search_by'] ) ){
            if($post['search_by'] == 1) {
                $where['self.receipt_no'] = $post['ref_no']; 
            } else {
                $where['self.contact_no'] = $post['contact_no'];
            }
        //}

        $results = $this->get_all( '', $where );
        
        return $results;
    }
    
    public function update_pledge($data,$id,$rec_no) {
        $this->db->where('d_id',$id);
        $this->db->where('receipt_no',$rec_no);
        $this->db->update($this->donation_pledge,$data);
        return $this->db->affected_rows();
    }
    
    public function update_transaction($data,$id,$rec_no) {
        $this->db->where('d_id',$id);
        $this->db->where('receipt_no',$rec_no);
        $this->db->update($this->donation_transaction,$data);
        return $this->db->affected_rows();
    }
    
    public function updtae_pledge_amt_transaction($data,$pledge_id) {
        $this->db->where('pledge_id',$pledge_id);
        $this->db->update($this->donation_transaction,$data);
        return $this->db->affected_rows();
    }
    
    public function update_refund($data,$type_id,$id) {
        $this->db->where('menu_id',$type_id);
        $this->db->where('auto_id',$id);
        $this->db->update($this->refund,$data);
        return $this->db->affected_rows();
    }
    
    public function get_pledge_last_data($service,$name,$contact_no) {
        $this->db->where('service_id',$service);
        $this->db->where('name',$name);
        $this->db->where('contact_no',$contact_no);
        //$this->db->where('status',1);
        $this->db->order_by('id','desc');
        $this->db->limit(1);
        return $this->db->get($this->donation)->row();
    }

    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->payment)->result();
    }
    public function delete_payment_by_paid_id($id) {
        $this->db->where_in('id',$id);
        $this->db->delete($this->payment);
        return $this->db->affected_rows();
    }
    public function get_payment_date( $bd_id ) {
        $where['self.pay_ref_type'] = 4; // Billing Kattalai Archanai
        $where['self.pay_ref_id'] = $bd_id;
        $where['self.status'] = 1;        
        $q = $this->db->get_where( TBL_PAY . ' AS self', $where );
        return $q->result_array();
    }
}
?>

