<?php
class Statistical_report_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->devotees = TBL_BD;
        $this->payments = TBL_PAY;
        $this->services = TBL_SERVICE;
        $this->service_menu = TBL_SERVICE_MENU;
        $this->abishegam = TBL_BA;
        $this->festival = TBL_BILL_FESTIVAL_FUNCTION;
        $this->ubayam = TBL_BILL_UBAYAM;
        $this->mega = TBL_BILL_MEGA_FUNCTION;
        $this->other = 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->prasadam = TBL_BP;
        $this->madappalli = TBL_BM;
        $this->donation = TBL_BILL_DONATION;
        $this->hall_booking = TBL_BH;
        $this->wedding_hall = TBL_BWH;
        $this->archanai = TBL_BAR;
        $this->kattalai = TBL_BK;
        $this->temple_shop = TBL_BILL_TEMPLESHOP_SALES;
        $this->member_register = TBL_BILL_MEMBER_CARD;
        $this->card_topup = TBL_BILL_CARD_TOPUP;
    }
    
    /*** Get Summary & Details Data - Start ***/
    public function devotee_list($contact_no,$top) {
        $this->db->select('d.bd_name, d.bd_contact_no', FALSE);
        $this->db->where('d.status',1);
        if(!empty($contact_no)) {
            $this->db->where('d.bd_contact_no',$contact_no);
        }
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('d.id');
        $results = $this->db->get($this->devotees . ' as d')->result();
        $return = $results;
        return $return;
    }
    
    public function get_abishegam_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(ab.ba_amount)) THEN "0.00" ELSE SUM(ab.ba_amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('ab.ba_amount as amount, DATE_FORMAT(ab.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = ab.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('ab.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ab.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('ab.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ab.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('ab.ba_contact_no',$mobile);
        }
        $this->db->where('ab.status',1);
        $this->db->where('ab.ba_book_status',1);
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('ab.id');
        return $this->db->get($this->abishegam . ' as ab')->result();
    }
    
    public function get_festival_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(ff.amount)) THEN "0.00" ELSE SUM(ff.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('ff.amount, DATE_FORMAT(ff.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = ff.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('ff.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ff.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('ff.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ff.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('ff.contact_no',$mobile);
        }
        $this->db->where('ff.status',1);
        $this->db->where('ff.book_status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('ff.id');
        return $this->db->get($this->festival . ' as ff')->result();
    }
    
    public function get_ubayam_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(u.amount)) THEN "0.00" ELSE SUM(u.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('u.amount, DATE_FORMAT(u.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = u.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('u.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('u.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('u.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('u.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('u.contact_no',$mobile);
        }
        $this->db->where('u.status',1);
        $this->db->where('u.book_status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('u.id');
        return $this->db->get($this->ubayam . ' as u')->result();
    }
    
    public function get_mega_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(m.amount)) THEN "0.00" ELSE SUM(m.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('m.amount, DATE_FORMAT(m.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = m.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('m.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('m.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('m.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('m.contact_no',$mobile);
        }
        $this->db->where('m.status',1);
        $this->db->where('m.book_status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('m.id');
        return $this->db->get($this->mega . ' as m')->result();
    }
    
    public function get_other_service_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(o.amount)) THEN "0.00" ELSE SUM(o.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('o.amount, DATE_FORMAT(o.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = o.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('o.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('o.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('o.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('o.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('o.contact_no',$mobile);
        }
        $this->db->where('o.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('o.id');
        return $this->db->get($this->other . ' as o')->result();
    }
    
    public function get_gold_silver_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(gs.amount)) THEN "0.00" ELSE SUM(gs.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('gs.amount, DATE_FORMAT(gs.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = gs.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('gs.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('gs.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('gs.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('gs.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('gs.contact_no',$mobile);
        }
        $this->db->where('gs.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('gs.id');
        return $this->db->get($this->gold_silver . ' as gs')->result();
    }
    
    public function get_temple_activity_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(t.amount)) THEN "0.00" ELSE SUM(t.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('t.amount, DATE_FORMAT(t.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = t.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('t.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('t.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('t.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('t.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('t.contact_no',$mobile);
        }
        $this->db->where('t.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('t.id');
        return $this->db->get($this->temple . ' as t')->result();
    }
    
    public function get_member_subscribe_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(ms.amount)) THEN "0.00" ELSE SUM(ms.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('ms.amount, DATE_FORMAT(ms.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = ms.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }        
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('ms.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ms.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('ms.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ms.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('ms.contact_no',$mobile);
        }
        $this->db->where('ms.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('ms.id');
        return $this->db->get($this->member_subscripe . ' as ms')->result();
    }
    
    public function get_building_fund_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(b.amount)) THEN "0.00" ELSE SUM(b.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('b.amount, DATE_FORMAT(b.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = b.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('b.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('b.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('b.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('b.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('b.contact_no',$mobile);
        }
        $this->db->where('b.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('b.id');
        return $this->db->get($this->building_fund . ' as b')->result();
    }
    
    public function get_outdoor_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(od.amount)) THEN "0.00" ELSE SUM(od.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('od.amount, DATE_FORMAT(od.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = od.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('od.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('od.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('od.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('od.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('od.contact_no1',$mobile);
        }
        $this->db->where('od.status',1);
        $this->db->where('od.book_status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('od.id');
        return $this->db->get($this->outdoor . ' as od')->result();
    }
    
    public function get_prasadam_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(p.bp_amount)) THEN "0.00" ELSE SUM(p.bp_amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('p.bp_amount as amount, DATE_FORMAT(p.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = p.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','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'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('p.bp_contact_no',$mobile);
        }
        $this->db->where('p.status',1);
        $this->db->where('p.bp_book_status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('p.id');
        return $this->db->get($this->prasadam . ' as p')->result();
    }
    
    public function get_madappalli_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(m.bm_amount)) THEN "0.00" ELSE SUM(m.bm_amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('m.bm_amount as amount, DATE_FORMAT(m.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = m.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('m.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('m.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('m.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('m.bm_contact_no',$mobile);
        }
        $this->db->where('m.status',1);
        $this->db->where('m.bm_book_status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('m.id');
        return $this->db->get($this->madappalli . ' as m')->result();
    }
    
    public function get_donation_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(d.amount)) THEN "0.00" ELSE SUM(d.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('d.amount, DATE_FORMAT(d.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = d.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('d.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('d.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('d.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('d.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('d.contact_no',$mobile);
        }
        $this->db->where('d.status',1);
        $this->db->where('d.amount_type !=',2);
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('d.id');
        return $this->db->get($this->donation . ' as d')->result();
    }
    
    public function get_hall_booking_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(p.pay_amount)) THEN "0.00" ELSE SUM(p.pay_amount) END as amount', FALSE);
            $this->db->join($this->hall_booking . ' as hb','hb.id = p.pay_ref_id','left');
        } elseif($data_type == 2) {
            $this->db->select('p.pay_amount as amount, DATE_FORMAT(p.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->hall_booking . ' as hb','hb.id = p.pay_ref_id','left');
            $this->db->join($this->services . ' as ser','ser.id = hb.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','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'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('hb.bh_contact_no',$mobile);
        }
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        
        $this->db->where('p.pay_ref_type',7);
        $this->db->where('p.status',1);
        $this->db->group_by('p.pay_ref_id');
        return $this->db->get($this->payments . ' as p')->result();
    }
    
    public function get_wed_hall_booking_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(p.pay_amount)) THEN "0.00" ELSE SUM(p.pay_amount) END as amount', FALSE);
            $this->db->join($this->wedding_hall . ' as whb','whb.id = p.pay_ref_id','left');
        } elseif($data_type == 2) {
            $this->db->select('p.pay_amount as amount, DATE_FORMAT(p.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->wedding_hall . ' as whb','whb.id = p.pay_ref_id','left');
            $this->db->join($this->services . ' as ser','ser.id = whb.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','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'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('whb.bwh_contact_no',$mobile);
        }
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        
        $this->db->where('p.pay_ref_type',18);
        $this->db->where('p.status',1);
        $this->db->group_by('p.pay_ref_id');
        return $this->db->get($this->payments . ' as p')->result();
    }
    
    public function get_archanai_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(a.bar_paid_amount)) THEN "0.00" ELSE SUM(a.bar_paid_amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('a.bar_paid_amount as amount, DATE_FORMAT(a.created_on, "%d-%m-%Y") as date_str, "Archanai" as service_name', FALSE);
        }
        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'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('a.contact_no',$mobile);
        }
        $this->db->where('a.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('a.id');
        return $this->db->get($this->archanai . ' as a')->result();
    }
    
    public function get_kattalai_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(k.bk_amount)) THEN "0.00" ELSE SUM(k.bk_amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('k.bk_amount as amount, DATE_FORMAT(k.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = k.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('k.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('k.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('k.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('k.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('k.contact_no',$mobile);
        }
        $this->db->where('k.status',1);
        $this->db->where('k.bk_book_status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('k.id');
        return $this->db->get($this->kattalai . ' as k')->result();
    }
    
    public function get_temple_shop_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(ts.amount)) THEN "0.00" ELSE SUM(ts.amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('ts.amount, DATE_FORMAT(ts.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->services . ' as ser','ser.id = ts.service_id','left');
            $this->db->join($this->service_menu . ' as sm','sm.id = ser.menu_name','left');
        }
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('ts.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ts.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('ts.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ts.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('ts.contact_no',$mobile);
        }
        $this->db->where('ts.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        $this->db->group_by('ts.id');
        return $this->db->get($this->temple_shop . ' as ts')->result();
    }
    
    public function get_membership_card_topup_data($start_date='',$end_date='',$mobile='',$top='',$data_type) {
        if($data_type == 1) {
            $this->db->select('CASE WHEN ISNULL (SUM(ct.topup_amount)) THEN "0.00" ELSE SUM(ct.topup_amount) END as amount', FALSE);
        } elseif($data_type == 2) {
            $this->db->select('ct.topup_amount as amount, DATE_FORMAT(ct.created_on, "%d-%m-%Y") as date_str, sm.service_name', FALSE);
            $this->db->join($this->service_menu . ' as sm','sm.id = mr.type_id','left');
        }
        $this->db->join($this->card_topup . ' as ct','ct.mem_id = mr.id');
        if((!empty($start_date)) && (!empty($end_date))) {
            $this->db->where('ct.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ct.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } else {
            $this->db->where('ct.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ct.created_on <=',date('Y-m-d 23:59:59'));
        }
        
        if(!empty($mobile)) {
            $this->db->where('mr.contact_no',$mobile);
        }
        $this->db->where('mr.status',1);
        
        if(!empty($top)) {
            $this->db->limit($top);
        }
        #$this->db->group_by('mr.id');
        return $this->db->get($this->member_register . ' as mr')->result();
    }
    
    public function get_monthwise_data($mY,$mobile,$top,$data_type) {
        $start_date = '01-' . $mY;
        $end_date   = '31-' . $mY;
        $mobile     = $mobile;
        $top        = $top;
        
        $abishegam = $this->get_abishegam_data($start_date,$end_date,$mobile,$top,$data_type);
        $festival = $this->get_festival_data($start_date,$end_date,$mobile,$top,$data_type);
        $ubayam = $this->get_ubayam_data($start_date,$end_date,$mobile,$top,$data_type);
        $mega = $this->get_mega_data($start_date,$end_date,$mobile,$top,$data_type,$data_type);
        $other = $this->get_other_service_data($start_date,$end_date,$mobile,$top,$data_type);
        $gold_silver = $this->get_gold_silver_data($start_date,$end_date,$mobile,$top,$data_type);
        $temple = $this->get_temple_activity_data($start_date,$end_date,$mobile,$top,$data_type);
        $member_subscripe = $this->get_member_subscribe_data($start_date,$end_date,$mobile,$top,$data_type);
        $building_fund  = $this->get_building_fund_data($start_date,$end_date,$mobile,$top,$data_type);
        $outdoor = $this->get_outdoor_data($start_date,$end_date,$mobile,$top,$data_type);
        $prasadam = $this->get_prasadam_data($start_date,$end_date,$mobile,$top,$data_type);
        $madappalli = $this->get_madappalli_data($start_date,$end_date,$mobile,$top,$data_type);
        $donation = $this->get_donation_data($start_date,$end_date,$mobile,$top,$data_type);
        $hall_booking = $this->get_hall_booking_data($start_date,$end_date,$mobile,$top,$data_type);
        $wedding_hall = $this->get_wed_hall_booking_data($start_date,$end_date,$mobile,$top,$data_type);
        $archanai = $this->get_archanai_data($start_date,$end_date,$mobile,$top,$data_type);
        $kattalai = $this->get_kattalai_data($start_date,$end_date,$mobile,$top,$data_type);
        $temple_shop = $this->get_temple_shop_data($start_date,$end_date,$mobile,$top,$data_type);
        $card_topup = $this->get_membership_card_topup_data($start_date,$end_date,$mobile,$top,$data_type);
        
        $results = array_merge($abishegam, $festival, $ubayam, $mega, $other, $gold_silver, $temple, $member_subscripe, $building_fund, $outdoor, $prasadam, $madappalli, $donation, $hall_booking, $wedding_hall, $archanai, $kattalai, $temple_shop, $card_topup);
        #error_log(print_r($results,1));
        return $results;
        
    }
    /*** Get Summary Data - End ***/
    
    /* public function get_test_array() {
        $array1 = array(
           array(
            'name' => 'Test1',
            'date' => date('d-m-Y')
            )
        );
        
        $array2 = array(
        array(
            'name' => 'Test2',
            'date' => date('d-m-Y')
            )
        );
        
        $array3 = array(
        array(
            'name' => 'Test3',
            'date' => date('d-m-Y')
            )
        );
        
        $array4 = array(
        array(
            'name' => '',
            'date' => ''
            )
        );
        
         $array5 = '';
        
        $results = array_merge($array1,$array2,$array3,$array4,$array5);
        error_log(print_r($results,1));
        return $results; 
    }*/
}
?>


