<?php

/* 
 * Author       : Kowsalya K
 * Purpose      : Model for Billing Outdoor Services 
 * Created On   : 2016-08-12
 */

class Outdoor_services_model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        $this->service = TBL_SERVICE;
        $this->outdoor_services = TBL_BILL_OUTDOOR_SERVICES;
        $this->payment_type = TBL_PAY_TYPE;
        $this->priest = TBL_PRIEST;
        $this->payment = TBL_PAY;
    }
    
    public function list_all() {
        $select = 'os.id, os.receipt_no, os.devotee_name, os.tamil_name, os.contact_no1, s.name as service, s.prefix, os.amount as os_amount, os.status, os.del_reason';
        $this->db->select($select,FALSE);
        $this->db->join($this->service . ' as s','s.id = os.service_id');
        #$this->db->where('os.status !=',2);
        $this->db->where('os.book_status',1);
        $this->db->order_by('os.receipt_no','desc');
        return $this->db->get($this->outdoor_services . ' as os')->result();
    }
    
    public function get( $tbl_name, $where = array(), $args = array() ) {
        if (!is_array($where)) { $where = array(); }
        $where['status'] = 1;
		
		if( !empty( $args['order_by'] ) ) :
            $this->db->order_by( $args['order_by'] );
        endif;
        
        $q = $this->db->get_where( $tbl_name, $where );
        return $q->result_array();
            
    }
    
    public function payment_mode_lists() {
        $this->db->where('status',1);
        $this->db->where('menu_id',0);
        return $this->db->get($this->payment_type)->result();
    }
    
    public function get_designation_list($designation){
        $this->db->where('designation',$designation);
        $this->db->where('status',1);
        return $this->db->get($this->priest)->result();
    }
    
    public function outdoor_url_key_exists($url_key, $id) {
        if (!empty($id)) {
            $this->db->where('id !=', $id);
        }
        $this->db->where('url_key', $url_key);
        return $this->db->get($this->outdoor_services)->num_rows();
    }
    
    public function service_type_exists($type, $bk_status = '') {
        $this->db->where('service_id',$type);
        $this->db->where('status !=',2);
        
        if( $bk_status != '' ) :
            $this->db->where('book_status', $bk_status);
        endif;
        
        $this->db->order_by('receipt_no','desc');
        return $this->db->get($this->outdoor_services);
    }
    
    public function insert($data) {
        $this->db->insert($this->outdoor_services,$data);
        return $this->db->insert_id();
    }
    
    public function update($data,$id) {
        $this->db->where('id',$id);
        $this->db->update($this->outdoor_services,$data);
        return $this->db->affected_rows();
    }
    
    public function insert_payment($data){
        $this->db->insert($this->payment,$data);
        return $this->db->insert_id();
    }
    
    public function get_amount_by_service_id($id) {
        $this->db->where('id',$id);
        return $this->db->get($this->service)->row();
    }
    
    public function get_by_id($id) {
        $select = 'os.*, CASE os.status WHEN 1 THEN "Active" ELSE "Inactive" END as status_str, os.status AS status';
        $select .= ', CASE os.period WHEN 1 THEN "Morning" ELSE "Evening" END as period_str';
        $select .= ', s.name as service, os.amount as os_amount, p1.name as priest_name, p2.name as nathaswaram_name, p3.name as melam_name, p4.name as temple_assistant_name, s.amount as ser_amount, s.prefix';
        $this->db->select($select,FALSE);
        $this->db->join($this->service . ' as s','s.id = os.service_id');
        $this->db->join($this->priest . ' as p1','p1.id = os.priest_id','left');
        $this->db->join($this->priest . ' as p2','p2.id = os.nathaswaram_id','left');
        $this->db->join($this->priest . ' as p3','p3.id = os.melam_id','left');
		$this->db->join($this->priest . ' as p4','p4.id = os.temple_assistant_id','left');
        $this->db->where('os.id',$id);
        return $this->db->get($this->outdoor_services . ' as os')->row();
    }
    
    public function total_paid_amount($menu,$type) {
        $this->db->select('SUM(pay_amount) as paidAmount');
        $this->db->where('pay_ref_type',$menu);
        $this->db->where('pay_ref_id',$type);
        $this->db->where('status',1);
        return $this->db->get($this->payment)->row();
    }
    
    public function get_paid_type($menu,$type,$id) {
        $this->db->where('pay_ref_type',$menu);
        $this->db->where('pay_ref_id',$type);
        $this->db->where('pay_type_id',$id);
        $this->db->where('status',1);
        return $this->db->get($this->payment)->row();
    }
    
    public function delete_paid_type($type_id,$id) {
        $this->db->where('pay_ref_type',$type_id);
        $this->db->where('pay_ref_id',$id);
        $this->db->delete($this->payment);
        return $this->db->affected_rows();
    }
    
    public function get_paid_type_by_id($menu,$type) {
        $this->db->select('p.pay_amount, pt.img_url, pt.name',FALSE);
        $this->db->join($this->payment_type . ' as pt','pt.id = p.pay_type_id');
        $this->db->where('p.pay_ref_type',$menu);
        $this->db->where('p.pay_ref_id',$type);
        $this->db->where('p.status',1);
        return $this->db->get($this->payment . ' as p')->result();
    }
    
    public function update_paid_type($data,$type_id,$id) {
        $this->db->where('pay_ref_type',$type_id);
        $this->db->where('pay_ref_id',$id);
        $this->db->update($this->payment,$data);
        return $this->db->affected_rows();
    }
    
    function get_all( $id, $where = array() ) {
        if( !empty( $id ) ) :
            $where['self.id'] = $id;
        endif;
        
        $where['self.status'] = 1;
        
        $this->db->select( 'self.*, self.status AS status, serv.name AS service_name, serv.prefix' );
        $this->db->join( TBL_SERVICE . ' AS serv', 'serv.id = self.service_id', 'left' );
        
        $q = $this->db->get_where( TBL_BILL_OUTDOOR_SERVICES . ' AS self', $where );
        //echo "Query:".$this->db->last_query(); die;

        return $q->result_array();
    }
    
    function ajax() {
        $post = $this->input->post(); error_log(print_r($post,1));
        $date_now = date("Y-m-d H:i:s");
        $session_data = $this->session->userdata( TBL_PREFIX . 'user_logged_in' );
        
        switch( $post['act'] ) :
            case 'check' :
                /*
                 * Check whether already exists
                 */
                $e = explode("-", $post['date']);
                $from_date = $e[2] . '-' . $e[1] . '-' . $e[0] . ' ' . $post['from_time'];
                $to_date = $e[2] . '-' . $e[1] . '-' . $e[0] . ' ' . $post['to_time'];
                
               /* $qry = 'SELECT * FROM (`'. TBL_BILL_OUTDOOR_SERVICES .'`) WHERE date = "' . $from_date . '" AND ((TIME(`from_time`) >= "'. date("h:i:s",strtotime($post['from_time'])) .'" AND TIME(`from_time`) <= "'. date("h:i:s",strtotime($post['from_time'])) .'" ) OR (TIME(`to_time`) >= "'. date("h:i:s",strtotime($post['to_time'])) .'" AND TIME(`to_time`) <= "'. date("h:i:s",strtotime($post['to_time'])) .'" ))';  */
               
               /* $qry = 'SELECT * FROM (`'. TBL_BH .'`) WHERE ( ( (DATE_FORMAT(`date`, "%Y-%m-%d") >= "'. $from_date .'" AND (TIME(`from_time`) >= "' . date("h:i:s",strtotime($post['from_time'])) . '")) AND  (DATE_FORMAT(`date`, "%Y-%m-%d") <= "'. $to_date .'" AND (TIME(`from_time`) <= "' . date("h:i:s",strtotime($post['to_time'])) . '")) ) OR ( (DATE_FORMAT(`date`, "%Y-%m-%d") >= "'. $from_date .'" AND (TIME(`to_time`) >= "' . date("h:i:s",strtotime($post['from_time'])) . '")) AND  (DATE_FORMAT(`date`, "%Y-%m-%d") <= "'. $to_date .'" AND (TIME(`to_time`) <= "' . date("h:i:s",strtotime($post['to_time'])) . '")) ) )';*/
               
                
                /* if( !empty( $post['load_id'] ) ) :
                    // Ignore current page ID..
                    //$where['self.id !='] = $post['load_id'];
                    $qry .= ' AND `id` != "'.$post['load_id'].'"';
                endif;
                
                $qry .= ' AND `service_id` = "'. $post['service_id'].'" AND `status` = "1" AND `book_status` = "1"';
                $q = $this->db->query( $qry );
                $results = $q->row_array();
               
                if( !empty( $results ) ) :
                    $results['from_time'] = date( "H:i", strtotime( $results['from_time'] ) );
                    $results['to_time'] = date( "H:i", strtotime( $results['to_time'] ) );
                endif;
                
            break; */
            if(!empty($post['priest_name']) || !empty($post['melam_name']) || !empty($post['nathaswaram_name'])) {
                $qry = 'SELECT * FROM (`'. TBL_BILL_OUTDOOR_SERVICES .'`)';
                $wheretxt = '';
                
                 if(!empty($post['priest_name'])) {
                    $wheretxt .= (!empty($wheretxt)) ? ' OR priest_id = ' . $post['priest_name'] : ' priest_id = ' . $post['priest_name'];
                }
                
                if(!empty($post['melam_name'])) {
                    $wheretxt .= (!empty($wheretxt)) ? ' OR melam_id = ' . $post['melam_name'] : ' melam_id = ' . $post['melam_name'];
                }
                
                if(!empty($post['nathaswaram_name'])){
                    $wheretxt .= (!empty($wheretxt)) ? ' OR nathaswaram_id = ' . $post['nathaswaram_name'] : ' nathaswaram_id = ' . $post['nathaswaram_name'];
                }
                
                $whereqry = ' WHERE ( '.$wheretxt.' ) AND '; 
                $where = '';
                $where .= (!empty($wheretxt)) ? $whereqry : ' WHERE ';
                
                $where .= '( ( DATE_FORMAT(`od_from_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND DATE_FORMAT(`od_from_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'") OR ( DATE_FORMAT(`od_to_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND DATE_FORMAT(`od_to_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'") OR ( DATE_FORMAT(`od_from_date`, "%Y-%m-%d %H:%i") <= "'. $from_date .'" AND DATE_FORMAT(`od_to_date`, "%Y-%m-%d %H:%i") >= "'. $to_date .'"))';
                    
                $qry .= $where;
                    if( !empty( $post['load_id'] ) ) :
                        // Ignore current page ID..
                        //$where['self.id !='] = $post['load_id'];
                        $qry .= ' AND `id` != "'.$post['load_id'].'"';
                    endif;
                    
                    //$where['self.service_id'] = $post['service_id'];
                    //$where['self.status'] = 1;
                    $qry .= ' AND `service_id` = "'. $post['service_id'].'" AND `status` = "1"';
                    //error_log($qry);
                    $q = $this->db->query( $qry );
                    
                    //$where['DATE_FORMAT(self.bh_from_date, "%Y-%m-%d %H:%i") >= '] = $e[2] . '-' . $e[1] . '-' . $e[0] . ' ' . $post['from_time']; 
                    //$where['DATE_FORMAT(self.bh_to_date, "%Y-%m-%d %H:%i") <= '] = $e[2] . '-' . $e[1] . '-' . $e[0] . ' ' . $post['to_time']; 
                    
                    //$q = $this->db->get_where( TBL_BH . ' AS self', $where );
                    
                    $results = $q->row_array();
                    //echo "Query:".$this->db->last_query(); die;
                    //echo 'Qry: '.$qry; 
                    //echo "<pre>Data: "; print_r( $results ); die;
                    //error_log($this->db->last_query());
            }
                
                
                if( !empty( $results ) ) :
                    $results['from_time'] = date( "H:i", strtotime( $results['od_from_date'] ) );
                    $results['to_time'] = date( "H:i", strtotime( $results['od_to_date'] ) );
                endif;
                
            break; 
            
            case 'availability' :
                /*
                 * Get all
                 */
                $where['self.service_id'] = $post['service_id'];
                //$where['self.book_status'] = 1;
                $where['self.status'] = 1;
                if( !empty( $post['from_date'] ) ) :
                    $e = explode("-", $post['from_date']);
                    $where['DATE(self.od_from_date) >= '] = $e[2] . '-' . $e[1] . '-' . $e[0]; 
                endif;
                if( !empty( $post['to_date'] ) ) :
                    $e = explode("-", $post['to_date']);
                    $where['DATE(self.od_to_date) <= '] = $e[2] . '-' . $e[1] . '-' . $e[0]; 
                endif;
                
                $results = $this->get_all( '', $where );
                //echo "<pre>Data: "; print_r( $results ); die;
                
            break;
            
        endswitch; // 
        
        return $results;
    }

    public function get_all_result( $id, $where = array() ) {
        if( !empty( $id ) ) :
            $where['self.id'] = $id;
        endif;
        
        $where['self.status'] = 1;
        
        
        $this->db->select( 'self.*, self.status AS status, serv.name AS service_name, serv.prefix' );
        $this->db->join( $this->service . ' AS serv', 'serv.id = self.service_id', 'left' );
        $this->db->order_by('self.id','desc');
        $q = $this->db->get_where( $this->outdoor_services . ' AS self', $where );
        return $q->result_array();
    }

    public function get_ajax() {
        $post = $this->input->post();
       
        switch( $post['act'] ) :
            case 'booked' :
                
                /*
                 * Get all booked details
                 */
                $where['self.book_status'] = 2;
                $where['self.status'] = 1;
                
                if( !empty( $post['fromDate'] ) ) :
                    $e = explode("-", $post['fromDate']);
                    $where['DATE(self.date) >= '] = $e[2] . '-' . $e[1] . '-' . $e[0]; 
                endif;
                
                if( !empty( $post['toDate'] ) ) :
                    $e = explode("-", $post['toDate']);
                    $where['DATE(self.date) <= '] = $e[2] . '-' . $e[1] . '-' . $e[0]; 
                endif;
                
                if( !empty( $post['period'] ) ) :
                    $where['self.period'] = $post['period']; 
                endif;

                $results = $this->get_all_result( '', $where );
                
            break;    
            
        endswitch;
        
        return $results;
    }
    
    public function get_paidID($menu,$type,$id) {
        $this->db->where('pay_ref_type',$menu);
        $this->db->where('pay_ref_id',$type);
        $this->db->where('pay_type_id',$id);
        $this->db->where('status',1);
        return $this->db->get($this->payment)->result();
    }
    
    public function delete_payment_by_paid_id($id) {
        $this->db->where_in('id',$id);
        $this->db->delete($this->payment);
        return $this->db->affected_rows();
    }

    public function get_payment_date( $bo_id ) {
        $where['self.pay_ref_type'] = 13; // Billing Kattalai Archanai
        $where['self.pay_ref_id'] = $bo_id;
        $where['self.status'] = 1;        
        $q = $this->db->get_where( TBL_PAY . ' AS self', $where );
        return $q->result_array();
    }
    
	function get_additional_payments( $bo_id ) {
        $this->db->select('id, additional_amount, desc'); 
		$this->db->where('self_id', $bo_id);
		$this->db->where('additional_amount != ""');
        $q = $this->db->get($this->outdoor_services);
        return $q->result_array();
    } 
	
	function get_additional_amt_details($id) {
		$this->db->select('desc, additional_amount');
        $this->db->where('self_id',$id);
		$this->db->where('additional_amount != ""');
        return $this->db->get($this->outdoor_services)->result();
    }
} 
?>