<?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_ab_prasadam_qty($god, $start_date = '', $end_date = '', $period = '') {
        $this->db->select('ai.prasadam_id, SUM(ai.bai_qty) as prasadam_qty, pm.name, pm.tamil_name,ai.god_id', 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.god_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_ff_prasadam_qty($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,fi.deity_id as god_id', 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.deity_id');
        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_mega_prasadam_qty($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,mi.deity_id as god_id', 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.deity_id');
        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_ubayam_prasadam_qty($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,ui.deity_id as god_id', 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.deity_id');
        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_prasadam_item_qty($god, $start_date = '', $end_date = '', $period = '') {
        $this->db->select('pi.prasadam_id , SUM(pi.bpi_qty) as prasadam_qty, pm.name, pm.tamil_name,pi.god_id', 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.god_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_madappalli_prasadam_qty($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,mi.god_id', 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.god_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();
      } */

    public function get_dis_prasadam_god_prasadam($start_date = '', $end_date = '', $period = '') {
        $this->db->select('p.id, pi.prasadam_id , pm.name, pm.tamil_name', FALSE);
        $this->db->join($this->prasadam . ' as p', 'p.id = pi.bp_id AND p.status = 1', 'left');
        $this->db->join($this->prasadam_master . ' as pm', 'pm.id = pi.prasadam_id AND pm.distribution_type = 1 AND pm.status = 1');
        $this->db->where('pi.god_id', 0);
        $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_dis_prasadam_name($id, $start_date = '', $end_date = '', $period = '') {
        $this->db->select('pi.prasadam_id , pi.bpi_qty as prasadam_qty, p.id, p.bp_dev_name_1 as dev_name, p.bp_tamil_name_1 as dev_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.distribution_type = 1 AND pm.status = 1');
        $this->db->where('pi.god_id', 0);
        $this->db->where('pi.prasadam_id', $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.prasadam_id');
        return $this->db->get($this->prasadam_item . ' as pi')->result();
    }

    public function get_group_total_id($id, $start_date = '', $end_date = '', $period = '') {
        $this->db->select('sum(pi.bpi_qty) as prasadam_qty', 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.distribution_type = 1 AND pm.status = 1');
        $this->db->where('pi.god_id', 0);
        $this->db->where('pi.prasadam_id', $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.prasadam_id');
        return $this->db->get($this->prasadam_item . ' as pi')->result();
    }

}

?>