<?php
class Salary_report_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->services = TBL_SERVICE;
        $this->prasadam = TBL_PRASADAM;
        $this->payments = TBL_PAY;
        $this->payment_type = TBL_PAY_TYPE;
        $this->abishegam = TBL_BA;
        $this->ab_prasadam = TBL_BAI;
		$this->homam = TBL_BHM;
        $this->hm_prasadam = TBL_BHMI;
        $this->festival_function = TBL_BILL_FESTIVAL_FUNCTION;
        $this->f_prasadam = TBL_BILL_FESTIVAL_PRASADAM;
        $this->ubayam = TBL_BILL_UBAYAM;
        $this->u_prasadam = TBL_BILL_UBAYAM_PRASADAM;
        $this->mega_function = TBL_BILL_MEGA_FUNCTION;
        $this->m_prasadam = TBL_BILL_MEGA_PRASADAM;
        $this->donation = TBL_BILL_DONATION;
        #$this->d_prasadam = TBL_BILL_DONATION;
        $this->other_services = TBL_BILL_OTHERS;
        $this->gold_silver = TBL_BILL_GOLD_SILVER_OFFERING;
        $this->temple = TBL_BILL_TEMPLE;
        $this->member_subscripe = TBL_BILL_MEMBER_SUBSCRIPE;
        $this->building_fund = TBL_BILL_BUILDING_FUND;
        $this->outdoor  = TBL_BILL_OUTDOOR_SERVICES;
        $this->bill_prasadam = TBL_BP;
        $this->madappalli = TBL_BM;
        $this->hall_booking = TBL_BH;
        $this->wedding_hall_booking = TBL_BWH;
        $this->settings = TBL_SETTINGS_GENERAL;
        $this->archanai = TBL_BAR;
        $this->archanai_item = TBL_BARI;
        $this->category = TBL_CATEGORY;
        $this->item = TBL_ITEM;
        $this->kattalai = TBL_BK;
        $this->kattalai_item = TBL_BKI;
        $this->refund = TBL_REFUND;
        $this->member_register = TBL_BILL_MEMBER_CARD;
        $this->card_topup = TBL_BILL_CARD_TOPUP;
        $this->temple_shop = TBL_BILL_TEMPLESHOP_SALES;
    }
    public function get_abishegam_data($start_date='',$end_date='') {
        $this->db->select('"Abishegam" as service_name, SUM(p.pay_amount) as amount', FALSE);
        $this->db->join($this->abishegam . ' as ab','ab.id = p.pay_ref_id AND p.pay_ref_type = 1','left');
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('p.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('p.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('p.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('p.created_on <=',date('Y-m-d 23:59:59',strtotime('+30 days')));
        }
        $this->db->where('p.status',1);
        $this->db->where('ab.ba_book_status',1);
        $results = $this->db->get($this->payments . ' as p')->result_array();
        return $results;
    }
	
    public function get_archanai_data($start_date='',$end_date='') {
		$this->db->select('SUM(ai.bari_sub_total) AS amount, CONCAT("Archanai - ", cat.category_name) as service_name, cat.category_name as type_name', FALSE);
       	$this->db->join($this->archanai . ' as a','a.id = ai.bar_id','left');
       	$this->db->join($this->category . ' as cat','cat.id = ai.bari_cat_id','left');
       
       if((!empty($start_date)) && (!empty($end_date))) {
           $this->db->where('a.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
           $this->db->where('a.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
       } else {
           $this->db->where('a.created_on >=',date('Y-m-d 00:00:00'));
           $this->db->where('a.created_on <=',date('Y-m-d 23:59:59',strtotime('+30 days')));
       }
       $this->db->where('a.status',1);
	   $this->db->group_by('type_name');
       $this->db->order_by('cat.category_order', 'ASC');
       $results = $this->db->get($this->archanai_item . ' as ai')->result_array();	
	   //echo $this->db->last_query();
       return $results;
    }
    
    public function get_prasadam_data($start_date='',$end_date='') {
        $this->db->select('SUM(p.pay_amount) as amount, CONCAT("Naivedyam - ", ser.name) as service_name, , ser.name as type_name', FALSE);
        $this->db->join($this->bill_prasadam . ' as bp','bp.id = p.pay_ref_id AND p.pay_ref_type = 15','left');
		$this->db->join($this->services . ' as ser','ser.id = bp.service_id');
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('bp.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('bp.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('bp.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('bp.created_on <=',date('Y-m-d 23:59:59',strtotime('+30 days')));
        }
        $this->db->where('p.status',1);
        $this->db->where('bp.bp_book_status',1);
		$this->db->group_by('type_name');
        $this->db->order_by('type_name', 'ASC');
        $results = $this->db->get($this->payments . ' as p')->result_array();
        return $results;
    }
	
	public function get_kattalai_data($start_date='',$end_date='') {
        $this->db->select('"Kattalai Archanai" as service_name, SUM(p.pay_amount) as amount', FALSE);
        $this->db->join($this->kattalai . ' as k','k.id = p.pay_ref_id AND k.type_id = p.pay_ref_type','left');
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('p.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('p.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('p.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('p.created_on <=',date('Y-m-d 23:59:59',strtotime('+30 days')));
        }
        $this->db->where('p.status',1);
        $this->db->where('k.bk_book_status',1);
        $results = $this->db->get($this->payments . ' as p')->result_array();
        return $results;
    }
}
?>