<?php
class Prasadam_report_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->services = TBL_SERVICE;
        $this->prasadam = TBL_BP;
        $this->prasadam_item = TBL_BPI;
        $this->star = TBL_STAR;
        $this->god = TBL_GOD_NAME;
        $this->prasadam_master = TBL_PRASADAM;
        $this->madappalli_master = TBL_MADAPPALLI;
        $this->abishegam = TBL_BA;
        $this->abishegam_item = TBL_BAI;
        $this->festival = TBL_BILL_FESTIVAL_FUNCTION;
        $this->festival_item = TBL_BILL_FESTIVAL_PRASADAM;
        $this->mega = TBL_BILL_MEGA_FUNCTION;
        $this->mega_item = TBL_BILL_MEGA_PRASADAM;
        $this->ubayam = TBL_BILL_UBAYAM;
        $this->ubayam_item = TBL_BILL_UBAYAM_PRASADAM;
        $this->madappalli = TBL_BM;
        $this->madappalli_item = TBL_BMI;        
    }
    
    public function get_data($start_date='',$end_date='',$period='',$date_type=''){
        $this->db->select('s.name as service_name, s.prefix, star.name as star, star2.name as star2, CASE p.bp_period WHEN 1 THEN "Morning" ELSE "Evening" END as period_str, p.*',FALSE);
        $this->db->join($this->services . ' as s','s.id = p.service_id');
        $this->db->join($this->star . ' as star','star.id = p.star_id_1','left');
        $this->db->join($this->star . ' as star2','star2.id = p.star_id_2','left');
        if(!empty($date_type) && $date_type == 1) {
            if(!empty($start_date) && !empty($end_date)) {
                $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
                $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
            } elseif(!empty($start_date) && (empty($end_date)) ) {
                $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
                $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
            } else {
                $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00'));
                $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59'));
            }
        } else if(!empty($date_type) && $date_type == 2) {
            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)));
            } elseif(!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($start_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($period)) {
            $this->db->where('p.bp_period',$period);
        }
        #$this->db->where('p.status',1);
        $this->db->where('p.bp_book_status',1);
        $this->db->order_by('p.id','ASC');
        return $this->db->get($this->prasadam . ' as p')->result();
    }
    
    public function get_abishegam_god_prasadam($start_date='',$end_date='',$period='') {
        $this->db->select('g.id,g.name as god_name, g.tamil_name as god_tamil',FALSE);
        $this->db->join($this->abishegam . ' as a','a.id = ai.ba_id AND a.status = 1');
        $this->db->join($this->god . ' as g','g.id = ai.god_id');
        $this->db->where('ai.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('a.ba_fn_from_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('a.ba_fn_from_date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('a.ba_fn_from_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('a.ba_fn_from_date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('a.ba_fn_from_date >=',date('Y-m-d 00:00:00'));
            $this->db->where('a.ba_fn_from_date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('a.ba_period',$period);
        }
        $this->db->group_by('ai.god_id');
        return $this->db->get($this->abishegam_item . ' as ai')->result();
    }
    
    
    public function get_festival_god_prasadam($start_date='',$end_date='',$period='') {
        $this->db->select('g.id,g.name as god_name, g.tamil_name as god_tamil',FALSE);
        $this->db->join($this->festival . ' as f','f.id = fi.festival_id AND f.status = 1');
        $this->db->join($this->god . ' as g','g.id = fi.deity_id');
        $this->db->where('fi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('f.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('f.date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('f.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('f.date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('f.date >=',date('Y-m-d 00:00:00'));
            $this->db->where('f.date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('f.period',$period);
        }
        $this->db->group_by('fi.deity_id');
        return $this->db->get($this->festival_item . ' as fi')->result();
    }
    
    public function get_mega_god_prasadam($start_date='',$end_date='',$period='') {
        $this->db->select('g.id,g.name as god_name, g.tamil_name as god_tamil',FALSE);
        $this->db->join($this->mega . ' as m','m.id = mi.mega_id AND m.status = 1');
        $this->db->join($this->god . ' as g','g.id = mi.deity_id');
        $this->db->where('mi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('m.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('m.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('m.date >=',date('Y-m-d 00:00:00'));
            $this->db->where('m.date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('m.period',$period);
        }
        $this->db->group_by('mi.deity_id');
        return $this->db->get($this->mega_item . ' as mi')->result();
    }
    
    public function get_ubayam_god_prasadam($start_date='',$end_date='',$period='') {
        $this->db->select('g.id,g.name as god_name, g.tamil_name as god_tamil',FALSE);
        $this->db->join($this->ubayam . ' as u','u.id = ui.ubayam_id AND u.status = 1');
        $this->db->join($this->god . ' as g','g.id = ui.deity_id');
        $this->db->where('ui.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('u.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('u.date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('u.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('u.date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('u.date >=',date('Y-m-d 00:00:00'));
            $this->db->where('u.date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('u.period',$period);
        }
        $this->db->group_by('ui.deity_id');
        return $this->db->get($this->ubayam_item . ' as ui')->result();
    }
    
    public function get_prasadam_god_prasadam($start_date='',$end_date='',$period='') {
        $this->db->select('g.id,g.name as god_name, g.tamil_name as god_tamil',FALSE);
        $this->db->join($this->prasadam . ' as p','p.id = pi.bp_id AND p.status = 1');
        $this->db->join($this->god . ' as g','g.id = pi.god_id');
        $this->db->where('pi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00'));
            $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('p.bp_period',$period);
        }
        $this->db->group_by('pi.god_id');
        return $this->db->get($this->prasadam_item . ' as pi')->result();
    }
    
    public function get_madappalli_god_prasadam($start_date='',$end_date='',$period='') {
        $this->db->select('g.id,g.name as god_name, g.tamil_name as god_tamil',FALSE);
        $this->db->join($this->madappalli . ' as m','m.id = mi.bm_id AND m.status = 1');
        $this->db->join($this->god . ' as g','g.id = mi.god_id');
        $this->db->where('mi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('m.bm_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.bm_fn_date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('m.bm_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.bm_fn_date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('m.bm_fn_date >=',date('Y-m-d 00:00:00'));
            $this->db->where('m.bm_fn_date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('m.bm_period',$period);
        }
        $this->db->group_by('mi.god_id');
        return $this->db->get($this->madappalli_item . ' as mi')->result();
    }
    
    public function get_ab_prasadam($god,$start_date='',$end_date='',$period='') {
        $this->db->select('ai.prasadam_id, SUM(ai.bai_qty) as prasadam_qty, pm.name, pm.tamil_name',FALSE);
        $this->db->join($this->abishegam . ' as a','a.id = ai.ba_id AND a.status = 1');
        $this->db->join($this->prasadam_master . ' as pm','pm.id = ai.prasadam_id AND pm.status = 1');
        $this->db->where('ai.god_id',$god);
        $this->db->where('ai.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('a.ba_fn_from_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('a.ba_fn_from_date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('a.ba_fn_from_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('a.ba_fn_from_date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('a.ba_fn_from_date >=',date('Y-m-d 00:00:00'));
            $this->db->where('a.ba_fn_from_date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('a.ba_period',$period);
        }
        $this->db->group_by('ai.prasadam_id');
        return $this->db->get($this->abishegam_item . ' as ai')->result();
    }
    
    public function get_ff_prasadam($god,$start_date='',$end_date='',$period='') {
        $this->db->select('fi.prasadam as prasadam_id , SUM(fi.quantity) as prasadam_qty, pm.name, pm.tamil_name',FALSE);
        $this->db->join($this->festival . ' as f','f.id = fi.festival_id AND f.status = 1');
        $this->db->join($this->prasadam_master . ' as pm','pm.id = fi.prasadam AND pm.status = 1');
        $this->db->where('fi.deity_id',$god);
        $this->db->where('fi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('f.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('f.date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('f.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('f.date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('f.date >=',date('Y-m-d 00:00:00'));
            $this->db->where('f.date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('f.period',$period);
        }
        $this->db->group_by('fi.prasadam');
        return $this->db->get($this->festival_item . ' as fi')->result();
    }
    
    public function get_mega_prasadam($god,$start_date='',$end_date='',$period='') {
        $this->db->select('mi.prasadam as prasadam_id , SUM(mi.quantity) as prasadam_qty, pm.name, pm.tamil_name',FALSE);
        $this->db->join($this->mega . ' as m','m.id = mi.mega_id AND m.status = 1');
        $this->db->join($this->prasadam_master . ' as pm','pm.id = mi.prasadam AND pm.status = 1');
        $this->db->where('mi.deity_id',$god);
        $this->db->where('mi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('m.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('m.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('m.date >=',date('Y-m-d 00:00:00'));
            $this->db->where('m.date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('m.period',$period);
        }
        $this->db->group_by('mi.prasadam');
        return $this->db->get($this->mega_item . ' as mi')->result();
    }
    
    public function get_ubayam_prasadam($god,$start_date='',$end_date='',$period='') {
        $this->db->select('ui.prasadam as prasadam_id , SUM(ui.quantity) as prasadam_qty, pm.name, pm.tamil_name',FALSE);
        $this->db->join($this->ubayam . ' as u','u.id = ui.ubayam_id AND u.status = 1');
        $this->db->join($this->prasadam_master . ' as pm','pm.id = ui.prasadam AND pm.status = 1');
        $this->db->where('ui.deity_id',$god);
        $this->db->where('ui.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('u.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('u.date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('u.date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('u.date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('u.date >=',date('Y-m-d 00:00:00'));
            $this->db->where('u.date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('u.period',$period);
        }
        $this->db->group_by('ui.prasadam');
        return $this->db->get($this->ubayam_item . ' as ui')->result();
    }
    
    public function get_prasadam_item($god,$start_date='',$end_date='',$period='') {
        $this->db->select('pi.prasadam_id , SUM(pi.bpi_qty) as prasadam_qty, pm.name, pm.tamil_name',FALSE);
        $this->db->join($this->prasadam . ' as p','p.id = pi.bp_id AND p.status = 1');
        $this->db->join($this->prasadam_master . ' as pm','pm.id = pi.prasadam_id AND pm.status = 1');
        $this->db->where('pi.god_id',$god);
        $this->db->where('pi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('p.bp_fn_date >=',date('Y-m-d 00:00:00'));
            $this->db->where('p.bp_fn_date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('p.bp_period',$period);
        }
        $this->db->group_by('pi.prasadam_id');
        return $this->db->get($this->prasadam_item . ' as pi')->result();
    }
    
    public function get_madappalli_prasadam($god,$start_date='',$end_date='',$period='') {
        $this->db->select('mi.prasadam_id , SUM(mi.bmi_qty) as prasadam_qty, mm.prasadam_name as name, mm.prasadam_tamil_name as tamil_name',FALSE);
        $this->db->join($this->madappalli . ' as m','m.id = mi.bm_id AND m.status = 1');
        $this->db->join($this->madappalli_master . ' as mm','mm.id = mi.prasadam_id AND mm.status = 1');
        $this->db->where('mi.god_id',$god);
        $this->db->where('mi.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('m.bm_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.bm_fn_date <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('m.bm_fn_date >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('m.bm_fn_date <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('m.bm_fn_date >=',date('Y-m-d 00:00:00'));
            $this->db->where('m.bm_fn_date <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('m.bm_period',$period);
        }
        $this->db->group_by('mi.prasadam_id');
        return $this->db->get($this->madappalli_item . ' as mi')->result();
    }
    
    
    /*  public function get_abishegam_prasadam($god, $prasadam, $start_date='',$end_date='',$period='') {        
        $this->db->select('CASE WHEN ISNULL(ai.bai_qty) THEN "0" ELSE SUM(ai.bai_qty) END  as prasadam_qty, ai.god_id, ai.prasadam_id',FALSE);
        $this->db->join($this->abishegam . ' as a','a.id = ai.ba_id AND a.status = 1');
        $this->db->where('ai.prasadam_id',$prasadam);
        $this->db->where('ai.god_id',$god);
        $this->db->where('ai.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('a.ba_period',$period);
        }
        $this->db->group_by('ai.prasadam_id');
        return $this->db->get($this->abishegam_item . ' as ai')->result();
    } 
    
    public function get_itm_qty($god, $start_date='',$end_date='',$period='') {
        $select = ', (SELECT CASE WHEN ISNULL(ai.bai_qty) THEN "0" ELSE SUM(ai.bai_qty) END as qty FROM '  . $this->abishegam_item . ' as i WHERE i.id = ai.id) as prasadam_qty';
        
        $this->db->select('ai.god_id, ai.prasadam_id' . $select,FALSE);
        $this->db->join($this->abishegam . ' as a','a.id = ai.ba_id AND a.status = 1');
        #$this->db->where('ai.prasadam_id',$prasadam);
        $this->db->where('ai.god_id',$god);
        $this->db->where('ai.status',1);
        if(!empty($start_date) && !empty($end_date)) {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59',strtotime($end_date)));
        } elseif(!empty($start_date) && (empty($end_date)) ) {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00',strtotime($start_date)));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59',strtotime($start_date)));
        } else {
            $this->db->where('ai.created_on >=',date('Y-m-d 00:00:00'));
            $this->db->where('ai.created_on <=',date('Y-m-d 23:59:59'));
        }
        if(!empty($period)) {
            $this->db->where('a.ba_period',$period);
        }
        $this->db->group_by('ai.prasadam_id');
        return $this->db->get($this->abishegam_item . ' as ai')->result();
    } */
}
?>