<?php
class Monthly_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->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 payment_type_list() {
        $this->db->select('id,name, slug as payment_mode');
        $this->db->where('status',1);
        return $this->db->get($this->payment_type)->result();
    }
    
    public function get_total_paid_amt_by_type($id,$start_date='',$end_date='') {
        $this->db->select('SUM(pay_amount) as amount',FALSE);
        $this->db->where('pay_type_id',$id);
        if((!empty($start_date)) && (!empty($end_date))) {
           $this->db->where('DATE(created_on) >=',date('Y-m-d',strtotime($start_date)));
           $this->db->where('DATE(created_on) <=',date('Y-m-d',strtotime($end_date)));
        } else {
			$this->db->where('DATE(created_on) =',date('Y-m-d'));
           /*$this->db->where('DATE(created_on) >=',date('Y-m-d'));
           $this->db->where('DATE(created_on) <=',date('Y-m-d',strtotime('+27 days')));*/
        }
       $this->db->where('status',1);
       return $this->db->get($this->payments)->row(); 
    }
    
    public function get_payment_details($type_id,$service_id,$start_date= '',$end_date='',$mem_card_id) {
        $this->db->select('*, GROUP_CONCAT(DISTINCT pt.name SEPARATOR ",") as payment',FALSE);
        $this->db->join($this->payment_type . ' as pt','pt.id = p.pay_type_id AND pt.status != 2','left');
        $this->db->where('p.pay_ref_type',$type_id);
        if(!empty($service_id)){
            $this->db->where('p.pay_ref_id',$service_id);
        }
        if(!empty($mem_card_id)){
            $this->db->where('p.mem_card_id',$mem_card_id);
        }
        $this->db->where('p.status',1);
        if((!empty($start_date)) && (!empty($end_date))) {
           $this->db->where('DATE(p.created_on) >=',date('Y-m-d',strtotime($start_date)));
           $this->db->where('DATE(p.created_on) <=',date('Y-m-d',strtotime($end_date)));
        } else {
			$this->db->where('DATE(p.created_on) =',date('Y-m-d'));
           /*$this->db->where('DATE(p.created_on) >=',date('Y-m-d'));
           $this->db->where('DATE(p.created_on) <=',date('Y-m-d',strtotime('+27 days')));*/
        }
        return $this->db->get($this->payments . ' as p')->result_array();
    }
    
    public function get_by_id() {
        $resource = $this->db->get($this->settings);
        return $resource->row();
    }
    
    public function get_refund_amount($start_date,$end_date) {
        $this->db->select('CASE WHEN amount IS NULL THEN "0.00" ELSE SUM(amount) END as refund_amount',FALSE);
        if((!empty($start_date)) && (!empty($end_date))) {
           $this->db->where('DATE(created_on) >=',date('Y-m-d',strtotime($start_date)));
           $this->db->where('DATE(created_on) <=',date('Y-m-d',strtotime($end_date)));
        } else {
			$this->db->where('DATE(created_on) =',date('Y-m-d'));
           /*$this->db->where('DATE(created_on) >=',date('Y-m-d'));
           $this->db->where('DATE(created_on) <=',date('Y-m-d',strtotime('+27 days')));*/
        }
        $this->db->where('status',1);
        return $this->db->get($this->refund)->row()->refund_amount;
    }
    
    public function get_refund_amt_by_ids($menu_id,$service_id,$auto_id,$start_date,$end_date) {
        if(!empty($menu_id) && !empty($service_id) && !empty($auto_id) && !empty($start_date) && !empty($end_date)) {
            $this->db->select('CASE WHEN amount IS NULL THEN "0.00" ELSE SUM(amount) END as refund_amount',FALSE);
            $this->db->where('menu_id',$menu_id);
            $this->db->where('service_id',$service_id);
            $this->db->where('auto_id',$auto_id);
            $this->db->where('DATE(created_on) >=',date('Y-m-d',strtotime($start_date)));
            $this->db->where('DATE(created_on) <=',date('Y-m-d',strtotime($end_date)));
            $this->db->where('status',1);
            return $this->db->get($this->refund)->row()->refund_amount;
        }
    }
    
    public function get_monthly_data_details($start_date='',$end_date='',$data_type='') {
        $general = $this->get_by_id();
        
        $query1 = 'SELECT "Temple Services" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, ab.ba_rec_no as receipt, ab.service_id, ab.ba_amount as tamount, ab.id, "1" as type_id, ab.gst, ab.gst_value, ab.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->abishegam . ' as ab ON ab.id = p.pay_ref_id AND p.pay_ref_type = 1 JOIN ' . $this->services . ' as ser ON ser.id = ab.service_id WHERE p.status = 1 AND ab.ba_book_status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query1 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query1 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query1 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
            /*$query1 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query1 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        $query1 .= ' GROUP BY p.pay_ref_id';
		
		//echo $query1; die();
        
        $query2 = 'SELECT "Festival and Function" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, f.receipt_no as receipt, f.service_id, f.amount as tamount, f.id, f.type_id, f.gst, f.gst_value, f.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->festival_function . ' as f ON f.id = p.pay_ref_id AND f.type_id = p.pay_ref_type JOIN ' . $this->services . ' as ser ON ser.id = f.service_id WHERE p.status = 1 AND f.book_status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query2 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query2 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query2 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
            /*$query2 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query2 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        $query2 .= ' GROUP BY p.pay_ref_id';
        
        $query3 = 'SELECT "Ubayam" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, u.receipt_no as receipt, u.service_id, u.amount as tamount, u.id, u.type_id, u.gst, u.gst_value, u.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->ubayam . ' as u ON u.id = p.pay_ref_id AND u.type_id = p.pay_ref_type JOIN ' . $this->services . ' as ser ON ser.id = u.service_id WHERE u.book_status = 1 AND p.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query3 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query3 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query3 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
            /*$query3 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query3 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        $query3 .= ' GROUP BY p.pay_ref_id';
        
        $query4 = 'SELECT "Prasadam" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, bp.bp_rec_no as receipt, bp.service_id, bp.bp_amount as tamount, bp.id, "15" as type_id, bp.gst, bp.gst_value,  bp.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->bill_prasadam . ' as bp ON bp.id = p.pay_ref_id AND p.pay_ref_type = 15 JOIN ' . $this->services . ' as ser ON ser.id = bp.service_id WHERE bp.bp_book_status = 1 AND p.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query4 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query4 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query4 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
           /*$query4 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query4 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        $query4 .= ' GROUP BY p.pay_ref_id';
        
//        $query5 = 'SELECT "Mega Function" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, m.receipt_no as receipt, m.service_id, m.amount as tamount, m.id, m.type_id, m.gst, m.gst_value, m.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->mega_function . ' as m ON m.id = p.pay_ref_id AND m.type_id = p.pay_ref_type JOIN ' . $this->services . ' as ser ON ser.id = m.service_id WHERE m.book_status = 1 AND p.status = 1';
//        if((!empty($start_date)) && (!empty($end_date))) {
//            $query5 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
//            $query5 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
//        } else {
//            $query5 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
//            $query5 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';
//        }
//        $query5 .= ' GROUP BY p.pay_ref_id';
        
        $query6 = 'SELECT "Other Services" as menu_str, os.amount, ser.name as type_name, ser.prefix, os.receipt_no as receipt, os.service_id, os.amount as tamount, os.id, os.type_id, os.gst, os.gst_value, os.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->other_services . ' as os JOIN ' . $this->services . ' as ser ON ser.id = os.service_id';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query6 .= ' WHERE DATE(os.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query6 .= ' AND DATE(os.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query6 .= ' WHERE DATE(os.created_on) = "' . date('Y-m-d') . '"';
            /*$query6 .= ' WHERE DATE(os.created_on) >= "' . date('Y-m-d') . '"';
            $query6 .= ' AND DATE(os.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        
        $query7 = 'SELECT "Gold Silver" as menu_str, gs.amount, ser.name as type_name, ser.prefix, gs.receipt_no as receipt, gs.service_id, gs.amount as tamount, gs.id, gs.type_id, gs.gst, gs.gst_value, gs.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->gold_silver . ' as gs JOIN ' . $this->services . ' as ser ON ser.id = gs.service_id';        
        if((!empty($start_date)) && (!empty($end_date))) {
            $query7 .= ' WHERE DATE(gs.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query7 .= ' AND DATE(gs.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query7 .= ' WHERE DATE(gs.created_on) = "' . date('Y-m-d') . '"';
            /*$query7 .= ' WHERE DATE(gs.created_on) >= "' . date('Y-m-d') . '"';
            $query7 .= ' AND DATE(gs.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        
//        $query8 = 'SELECT "Temple Activities" as menu_str, t.amount, ser.name as type_name, ser.prefix, t.receipt_no as receipt, t.service_id, t.amount as tamount, t.id, t.type_id, t.gst, t.gst_value, t.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->temple . ' as t JOIN ' . $this->services . ' as ser ON ser.id = t.service_id';
//        if((!empty($start_date)) && (!empty($end_date))) {
//            $query8 .= ' WHERE DATE(t.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
//            $query8 .= ' AND DATE(t.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
//        } else {
//            $query8 .= ' WHERE DATE(t.created_on) >= "' . date('Y-m-d') . '"';
//            $query8 .= ' AND DATE(t.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';
//        }
        
//        $query9 = 'SELECT "Member Subscription" as menu_str, mem.amount, ser.name as type_name, ser.prefix, mem.receipt_no as receipt, mem.service_id, mem.amount as tamount, mem.id, mem.type_id, mem.gst, mem.gst_value, mem.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->member_subscripe . ' as mem JOIN ' . $this->services . ' as ser ON ser.id = mem.service_id';
//        if((!empty($start_date)) && (!empty($end_date))) {
//            $query9 .= ' WHERE DATE(mem.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
//            $query9 .= ' AND DATE(mem.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
//        } else {
//            $query9 .= ' WHERE DATE(mem.created_on) >= "' . date('Y-m-d') . '"';
//            $query9 .= ' AND DATE(mem.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';
//        }
        
//        $query10 = 'SELECT "Building Fund" as menu_str, bf.amount, ser.name as type_name, ser.prefix, bf.receipt_no as receipt, bf.service_id, bf.amount as tamount, bf.id, bf.type_id, bf.gst, bf.gst_value, bf.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->building_fund . ' as bf JOIN ' . $this->services . ' as ser ON ser.id = bf.service_id';
//        if((!empty($start_date)) && (!empty($end_date))) {
//            $query10 .= ' WHERE DATE(bf.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
//            $query10 .= ' AND DATE(bf.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
//        } else {
//            $query10 .= ' WHERE DATE(bf.created_on) >= "' . date('Y-m-d') . '"';
//            $query10 .= ' AND DATE(bf.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';
//        }
        
        $query11 = 'SELECT "Outdoor Service" as menu_str, od.amount,ser.name as type_name, ser.prefix, od.receipt_no as receipt, od.service_id, od.amount as tamount, od.id, od.type_id, od.gst, od.gst_value, od.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->outdoor . ' as od JOIN ' . $this->services . ' as ser ON ser.id = od.service_id WHERE od.book_status = 1 ';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query11 .= ' AND DATE(od.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query11 .= ' AND DATE(od.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query11 .= ' AND DATE(od.created_on) = "' . date('Y-m-d') . '"';
            /*$query11 .= ' AND DATE(od.created_on) >= "' . date('Y-m-d') . '"';
            $query11 .= ' AND DATE(od.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        
//        $query12 = 'SELECT "Madappalli Service" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, bm.bm_rec_no as receipt, bm.service_id, bm.bm_amount as tamount, bm.id, "9" as type_id, bm.gst, bm.gst_value, bm.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->madappalli . ' as bm ON bm.id = p.pay_ref_id AND p.pay_ref_type = 9 JOIN ' . $this->services . ' as ser ON ser.id = bm.service_id WHERE bm.bm_book_status = 1 AND p.status = 1';
//        if((!empty($start_date)) && (!empty($end_date))) {
//            $query12 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
//            $query12 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
//        } else {
//            $query12 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
//            $query12 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';
//        }
//        $query12 .= ' GROUP BY p.pay_ref_id';
        
        $query13 = 'SELECT "Hall Booking" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, hb.bh_rec_no as receipt, hb.service_id, hb.bh_amount as tamount, hb.id, "7" as type_id, hb.gst, hb.gst_value, hb.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p JOIN ' . $this->hall_booking . ' as hb ON hb.id = p.pay_ref_id JOIN ' . $this->services . ' as ser ON ser.id = hb.service_id WHERE p.pay_ref_type = 7 AND p.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query13 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query13 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query13 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
            /*$query13 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query13 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        $query13 .= ' GROUP BY p.pay_ref_id';
        
        $query14 = 'SELECT "Hall Booking" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, whb.bwh_rec_no as receipt, whb.service_id,  whb.bwh_amount as tamount, whb.id, "18" as type_id,  whb.gst, whb.gst_value, whb.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p JOIN ' . $this->wedding_hall_booking . ' as whb ON whb.id = p.pay_ref_id JOIN ' . $this->services . ' as ser ON ser.id = whb.service_id WHERE p.pay_ref_type = 18 AND p.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query14 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query14 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query14 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
            /*$query14 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query14 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }        
        $query14 .= ' GROUP BY p.pay_ref_id';
        
        $query15 = 'SELECT "Kattalai Archanai" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, k.bk_rec_no as receipt, k.service_id, k.bk_amount as tamount, k.id, k.type_id, k.gst, k.gst_value, k.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->kattalai . ' as k ON k.id = p.pay_ref_id AND k.type_id = p.pay_ref_type JOIN ' . $this->services . ' as ser ON ser.id = k.service_id WHERE k.bk_book_status = 1 AND p.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query15 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query15 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query15 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
            /*$query15 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query15 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }        
        $query15 .= ' GROUP BY p.pay_ref_id';
        
        /* $query16 = 'SELECT "Temple Shop Sale" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, ts.receipt_no as receipt, ts.service_id, ts.amount as tamount, ts.id, "19" as type_id, ts.gst, ts.gst_value, ts.del_reason, ser.menu_name, ser.gst as is_gst, GROUP_CONCAT(DISTINCT pt.name SEPARATOR ",") as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->temple_shop . ' as ts ON ts.id = p.pay_ref_id AND p.pay_ref_type = 19 JOIN ' . $this->services . ' as ser ON ser.id = ts.service_id LEFT JOIN ' . $this->payment_type . ' as pt ON pt.id = p.pay_type_id WHERE p.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query16 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query16 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
            $query16 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query16 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';
        }
        $query16 .= ' GROUP BY p.pay_ref_id'; */
        
        $query17 = 'SELECT "Donation" as menu_str, SUM(p.pay_amount) as amount, ser.name as type_name, ser.prefix, d.receipt_no as receipt, d.service_id, d.amount as tamount, d.id, d.type_id, d.gst, d.gst_value, d.del_reason, ser.menu_name, ser.gst as is_gst, "" as payment, d.amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->payments . ' as p LEFT JOIN ' . $this->donation . ' as d ON d.id = p.pay_ref_id AND d.type_id = p.pay_ref_type JOIN ' . $this->services . ' as ser ON ser.id = d.service_id WHERE p.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query17 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query17 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query17 .= ' AND DATE(p.created_on) = "' . date('Y-m-d') . '"';
            /*$query17 .= ' AND DATE(p.created_on) >= "' . date('Y-m-d') . '"';
            $query17 .= ' AND DATE(p.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        $query17 .= ' GROUP BY p.pay_ref_id';
        
        $query18 = 'SELECT "Prepaid Card" as menu_str, mr.amount as amount, "User Registration" as type_name, "" as prefix, mr.receipt_no as receipt, "0" as service_id, mr.actual_amount as tamount, mr.id, "12" as type_id, "" as gst, "" as gst_value, mr.del_reason, "12" as menu_name, "" as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->member_register . ' as mr';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query18 .= ' WHERE DATE(mr.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query18 .= ' AND DATE(mr.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query18 .= ' WHERE DATE(mr.created_on) = "' . date('Y-m-d') . '"';
            /*$query18 .= ' WHERE DATE(mr.created_on) >= "' . date('Y-m-d') . '"';
            $query18 .= ' AND DATE(mr.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        
        $query19 = 'SELECT "Prepaid Card" as menu_str, SUM(ct.topup_amount) as amount, "Card Topup" as type_name, "" as prefix, ct.receipt_no as receipt, "00" as service_id, ct.topup_amount as tamount, ct.id, "12" as type_id, "" as gst,  "" as gst_value, "Delete the record" as del_reason, "12" as menu_name, "" as is_gst, "" as payment, "" as amount_type, "" as item_id, "" as grouping, "" as qty, "" as aprice, "" as item_name, "" as is_category FROM ' . $this->card_topup . ' as ct WHERE ct.mem_first_data != 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query19 .= ' AND DATE(ct.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query19 .= ' AND DATE(ct.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query19 .= ' AND DATE(ct.created_on) = "' . date('Y-m-d') . '"';
            /*$query19 .= ' AND DATE(ct.created_on) >= "' . date('Y-m-d') . '"';
            $query19 .= ' AND DATE(ct.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        $query19 .= ' GROUP BY ct.mem_id';
        
       //$query20 = 'SELECT "Archanai" as menu_str, ai.bari_sub_total as amount, cat.category_name as type_name, "" as prefix, ai.bari_rec_no as receipt,  cat.id as service_id, ai.bari_sub_total as tamount, a.id, "2" as type_id, a.gst, a.gst_value, "" as del_reason, "2" as menu_name, "" as is_gst, "" as payment, "" as amount_type, item.id as item_id, cat.grouping, ai.bari_qty as qty, ai.bari_price as aprice, item.name as item_name, ai.is_category FROM ' . $this->archanai . ' as a LEFT JOIN ' .$this->archanai_item . ' as ai ON a.id = ai.bar_id LEFT JOIN ' . $this->category . ' as cat ON cat.id = ai.bari_cat_id LEFT JOIN ' . $this->item . ' as item ON item.id = ai.bari_item_id WHERE a.status = 1';
		
		$query20 = 'SELECT "Archanai" as menu_str, ai.bari_sub_total as amount, cat.category_name as type_name, "" as prefix, ai.bari_rec_no as receipt,  cat.id as service_id, ai.bari_sub_total as tamount, a.id, "2" as type_id, CASE item.gst WHEN 1 THEN 7 ELSE 0 END gst, a.gst_value, "" as del_reason, "2" as menu_name, "" as is_gst, "" as payment, "" as amount_type, item.id as item_id, cat.grouping, ai.bari_qty as qty, ai.bari_price as aprice, item.name as item_name, ai.is_category FROM ' . $this->archanai_item . ' as ai LEFT JOIN ' . $this->archanai . ' as a ON a.id = ai.bar_id LEFT JOIN ' . $this->category . ' as cat ON cat.id = ai.bari_cat_id LEFT JOIN ' . $this->item . ' as item ON item.id = ai.bari_item_id WHERE a.status = 1';
        if((!empty($start_date)) && (!empty($end_date))) {
            $query20 .= ' AND DATE(a.created_on) >= "' . date('Y-m-d',strtotime($start_date)) . '"';
            $query20 .= ' AND DATE(a.created_on) <= "' . date('Y-m-d',strtotime($end_date)) . '"';
        } else {
			$query20 .= ' AND DATE(a.created_on) = "' . date('Y-m-d') . '"';
            /*$query20 .= ' AND DATE(a.created_on) >= "' . date('Y-m-d') . '"';
            $query20 .= ' AND DATE(a.created_on) <= "' . date('Y-m-d',strtotime('+27 days')) . '"';*/
        }
        
        //echo 'SELECT * FROM (' . $query1." UNION ".$query20." UNION ".$query10." UNION ".$query17." UNION ".$query2." UNION ".$query7." UNION ".$query13." 
        //UNION ".$query15." UNION ".$query12." UNION ".$query5." UNION ".$query9." UNION ".$query18." UNION ".$query19." UNION ".$query11." UNION ".$query6." UNION ".$query4." UNION ".$query8." UNION ".$query3." UNION ".$query14 . ') as t ORDER BY menu_str ASC';
        //$query = $this->db->query('SELECT * FROM (' . $query1." UNION ".$query20." UNION ".$query10." UNION ".$query17." UNION ".$query2." UNION ".$query7." UNION ".$query13." UNION ".$query15." UNION ".$query12." UNION ".$query5." UNION ".$query9." UNION ".$query18." UNION ".$query19." UNION ".$query11." UNION ".$query6." UNION ".$query4." UNION ".$query8." UNION ".$query3." UNION ".$query14 . ') as t ORDER BY menu_str ASC');
		$query = $this->db->query('SELECT * FROM (' . $query1." UNION ".$query20." UNION ".$query17." UNION ".$query2." UNION ".$query7." UNION ".$query13." UNION ".$query15." UNION ".$query18." UNION ".$query19." UNION ".$query11." UNION ".$query6." UNION ".$query4." UNION ".$query3." UNION ".$query14 . ') as t ORDER BY menu_str ASC');
        
        $results = $query->result_array();
		
		//echo "<pre>";print_r($results); die();
        
        if((!empty($data_type)) && ($data_type == 'Get Details Data' || $data_type == 'Print Details Data'))
            $is_detail = TRUE;
        
        else
            $is_detail = FALSE;
        
        $return = array();
		if($results){
			foreach($results as $key=>$value) {
				
				if($value['menu_str'] == "Archanai") :
						if( $is_detail ) :
						// Submitted for "Details"
						
						// Summary
						$indx = $value['service_id'].'_'.$value['aprice'].$key;
						
						// Categery Name
						if($value['is_category'] == 0) : // With Category Condition
							if( empty( $return[$indx]['cat_name'] ) ) :
								$return[$indx]['cat_name'] = $value['type_name'];
							endif;
						else :
							$return[$indx]['cat_name'] = 'Archanai';
						endif;
						
						// Sub Categery Name
						if( empty( $return[$indx]['name'] ) ) :
							$return[$indx]['name'] = $value['item_name'];
						endif;
						
					   // Rec No
						$return[$indx]['rec_no'] = $value['receipt'];
						
						// Price
						$return[$indx]['price'] = $value['aprice'];
						
						// Qty 
						$return[$indx]['qty'] = $value['qty'];
						
						// Amount
						$return[$indx]['amount'] = ((!empty( $return[$indx]['amount']) )) ? $return[$indx]['amount'] + $value['amount'] : $value['amount'];
						
						// Auto increment id 
						$return[$indx]['id'] = $value['id'] ;
						
						// Type id 
						$return[$indx]['type_id'] = 2 ;
						
						//GST Value in Settings
						$return[$indx]['gst'] = $value['gst'];
						
						//GST is enable or not in Service Master
						$return[$indx]['is_gst'] = ($general->gst > 0) ? 1 : 0;
						
					else :
							if($value['grouping'] == 0) :
							// Summary
							$indx = $value['service_id'].'_'.$value['item_id'];
						 
							if($value['is_category'] == 0) : // With Category Condition
								// Name
								if( empty( $return[$indx]['name'] ) ) :
									$return[$indx]['name'] = $value['type_name'] . ' - '. $value['item_name'];
								endif;
								
							else :
							
								if( empty( $return[$indx]['name'] ) ) :
									$return[$indx]['name'] = $value['item_name'];
								endif;
							endif;
						
						else :
							if($value['is_category'] == 0) : // With Category Condition
								// Summary
								$indx = $value['service_id'].'_'.$value['bari_price'];
							 
								// Name
								if( empty( $return[$indx]['name'] ) ) :
									$return[$indx]['name'] = $value['type_name'] . ' - '. $value['bari_price'];
								endif;
								
							else :
								// Summary
								$indx = $value['service_id'].'_'.$value['item_id'];
							 
								// Name
								if( empty( $return[$indx]['name'] ) ) :
									$return[$indx]['name'] = $value['item_name'];
								endif;
								
							endif;
						endif;
						
						// Rec No
						/* $return[$indx]['rec_no'] = !empty( $return[$indx]['rec_no'] ) ? ( preg_replace( '/\-(.*?)/', '', $return[$indx]['rec_no'] ) . '-'. $value['bar_rec_no'] ) : $value['bar_rec_no']; */
						
						$return[$indx]['rec_no'] = !empty( $return[$indx]['rec_no'] ) ? ( preg_replace( '/\-.+/', '', $return[$indx]['rec_no'] ) . '-'. $value['receipt'] ) :  $value['receipt'] . '-' . $value['receipt'];
						
						// Price
						$return[$indx]['price'] = $value['aprice'];
						
						// Qty 
						$return[$indx]['qty'] = !empty( $return[$indx]['qty'] ) ? $return[$indx]['qty']+$value['qty'] : $value['qty'];
						
						// Amount
						$return[$indx]['amount'] = ((!empty( $return[$indx]['amount']) )) ? $return[$indx]['amount'] + $value['amount'] : $value['amount'];
						
						//GST Value in Settings
						$return[$indx]['gst'] = $value['gst'];
						
						//GST is enable or not in Service Master
						$return[$indx]['is_gst'] = ($general->gst > 0) ? 1 : 0;
						
						// Auto increment id 
						$return[$indx]['id'] = $value['id'] ;
						
						// Type id 
						$return[$indx]['type_id'] = 2 ;
					
					endif;
					
				else :
				
					if( $is_detail ) :
						// Submitted for "Details"
						
						$indx = $value['service_id'].$key;
					 
						// Categery Name
						if( empty( $return[$indx]['cat_name'] ) ) :
							$return[$indx]['cat_name'] = $value['menu_str'];
						endif;
						
						// Sub Categery Name
						if( empty( $return[$indx]['name'] ) ) :
							$return[$indx]['name'] = $value['type_name'];
						endif;
						
					   // Rec No
						$return[$indx]['rec_no'] = (($general->prefix == 1 && (!empty($value['prefix']))) ? $value['prefix'] : '') . $value['receipt'];
						
						// Auto increment id 
						$return[$indx]['id'] = $value['id'] ;
						
						// Type id 
						$return[$indx]['type_id'] = $value['menu_name'];
						
						if($value['menu_str'] == "Prepaid Card" && $value['type_name'] == "User Registration") {
							$return[$indx]['amount'] = ((!empty( $return[$indx]['amount']) )) ? $return[$indx]['amount'] + (($value['amount'] > 0) ? '10.00' : '0.00') : (($value['amount'] > 0) ? '10.00' : '0.00');
							
						} else if($value['menu_str'] == "Prepaid Card" && $value['type_name'] == "Card Topup") {
							$return[$indx]['amount'] = ((!empty( $return[$indx]['amount']) )) ? number_format((($return[$indx]['amount'] + $value['amount'])),2,'.','') : number_format(($value['amount']),2,'.','');
							
							$return[$indx]['mem_card_id'] = $value['id'] ;
							
						} else {
							if($value['amount_type'] == '' || $value['amount_type'] != 2) {
								// Amount
								$return[$indx]['service_id'] = $value['service_id'];
								$return[$indx]['amount'] = ($return[$indx]['service_id'] == $indx && (!empty( $return[$indx]['amount']) )) ? $return[$indx]['amount'] + $value['amount'] : $value['amount'];
							} else {
								$return[$indx]['service_id'] = $value['service_id'];
								$return[$indx]['amount'] = 0;
							}
						}
						
						
						
						if($value['menu_str'] == "Donation") {
							$return[$indx]['amount_type'] = $value['amount_type'];
						}
						
						//GST Value in Settings
						$return[$indx]['gst'] = $value['gst'];
						
						//GST is enable or not in Service Master
						$return[$indx]['is_gst'] = $value['is_gst'];
						
						//Delete Reason
						$return[$indx]['del_reason'] = $value['del_reason'];
						
					else :
						// Summary
						$indx = $value['service_id'];
					 
						// Name
						if( empty( $return[$indx]['name'] ) ) :
							$return[$indx]['name'] = $value['menu_str'].' - ' . $value['type_name'];
						endif;
						
						// Rec No
						$return[$indx]['rec_no'] = !empty( $return[$indx]['rec_no'] ) ? ( preg_replace( '/\-.+/', '', $return[$indx]['rec_no'] ) . '-'. (($general->prefix == 1 && (!empty($value['prefix']))) ? $value['prefix'] : '') . $value['receipt'] ) : (($general->prefix == 1 && (!empty($value['prefix']))) ? $value['prefix'] : '') . $value['receipt'] . '-' . (($general->prefix == 1 && (!empty($value['prefix']))) ? $value['prefix'] : '') . $value['receipt'];
						
						// Price
						$return[$indx]['price'] = '-';
						
						if($value['menu_str'] == "Prepaid Card" && $value['type_name'] == "User Registration") {
							$return[$indx]['service_id'] = $value['id'];
							$return[$indx]['static_id'] = 1;
							$return[$indx]['qty'] = ((!empty($return[$indx]['qty'])) && $return[$indx]['static_id'] == $indx ) ? (++$return[$indx]['qty']) : 1;
							
							$return[$indx]['amount'] = ($value['amount'] > 0) ? $return[$indx]['amount'] + $value['tamount'] : $return[$indx]['amount'];
							
						} else if($value['menu_str'] == "Prepaid Card" && $value['type_name'] == "Card Topup") {
							$return[$indx]['service_id'] = $value['id'];
							$return[$indx]['static_id'] = 1;
							$return[$indx]['qty'] = ((!empty($return[$indx]['qty'])) && $return[$indx]['static_id'] == $indx ) ? (++$return[$indx]['qty']) : 1;
							
							$return[$indx]['amount'] = ($return[$indx]['static_id'] == $indx && (!empty( $return[$indx]['amount']) )) ? $return[$indx]['amount'] + $value['amount'] : $value['amount'];
							
							$return[$indx]['mem_card_id'] = $value['id'] ;
							
						} else {
							// Qty
							$return[$indx]['service_id'] = $value['service_id'];
							$return[$indx]['qty'] = ((!empty($return[$indx]['qty'])) && $return[$indx]['service_id'] == $indx ) ? (++$return[$indx]['qty']) : 1;
							
							if($value['amount_type'] == '' || $value['amount_type'] != 2) {
								// Amount
								$return[$indx]['amount'] = ($return[$indx]['service_id'] == $indx && (!empty( $return[$indx]['amount']) )) ? $return[$indx]['amount'] + $value['amount'] : $value['amount'];
							}
						}
						
						
						if($value['menu_str'] == "Donation") {
							$return[$indx]['amount_type'] = $value['amount_type'];
						}
						
						//GST Value in Settings
						$return[$indx]['gst'] = $value['gst'];
						
						//GST is enable or not in Service Master
						$return[$indx]['is_gst'] = $value['is_gst'];
						
						// Auto increment id 
						$return[$indx]['id'] = $value['id'] ;
						
						// Type id 
						$return[$indx]['type_id'] = $value['menu_name'];
						
						//Delete Reason
						$return[$indx]['del_reason'] = $value['del_reason'];
					
					endif;
			   endif;
			}
			//print_r($return); die();
			return $return;
		}
	}
}
?>