<?php

/* 
 * Author       : Jai K
 * Purpose      : Model for Billing - Hall Booking
 * Created On   : 2016-08-08 18:36
 */

class Hall_Booking_Model extends CI_Model {
    
    function __construct() {
        parent::__construct();
        
    }
    
    function get( $tbl_name, $where = array() ) {
        if ( ! is_array($where)) $where = array();
        $where['status'] = 1;
        if($tbl_name == 'thiru_payment_types') :
            $where['menu_id'] = 0;
        endif;
        
        $q = $this->db->get_where( $tbl_name, $where );
        return $q->result_array();
            
    }
    
    function get_nos() {
        /*
         * Get Ref. No & Contact No's
         */
        $post = $this->input->post();
        
        $where = array(
            'self.status'       => 1,
            'self.service_id'   => $post['typeId'],
        );
        
        // Get Only Parent's
        $this->db->where('self.self_id IS NULL', '', FALSE);
        $this->db->select('self.id, self.bh_rec_no, self.bh_contact_no');
        $q = $this->db->get_where( TBL_BH . ' AS self', $where );
        return $q->result_array();
    }
    
    function get_by_id($id){
        $this->db->where('id',$id);
        return $this->db->get(TBL_BH)->row();
    }
    
    function get_all( $id = '', $where = array() ) {
        if( !empty( $id ) ) :
            $where['self.id'] = $id;
        endif;
        
        // Get Only Parent's
        $this->db->where('self.self_id IS NULL', '', FALSE);
        
        $this->db->order_by( 'self.bh_rec_no', 'DESC' );
        $this->db->select( 'self.*, self.status AS status, serv.name AS service_name, serv.amount as ser_amount, serv.prefix' );
        $this->db->join( TBL_SERVICE . ' AS serv', 'serv.id = self.service_id', 'left' );
        
        $q = $this->db->get_where( TBL_BH . ' AS self', $where );
        return $q->result_array();
    }
    
    
    function get_payments( $bh_id ) {
        $where['self.pay_ref_type'] = 7; // For Billing Hall Booking
        $where['self.status'] = 1;
        $this->db->where('( self.pay_ref_id = '. $bh_id .' OR self.pay_parent_id = '. $bh_id .')', '', FALSE );
        
        $this->db->order_by('self.created_on', 'DESC');
        $this->db->select('*, self.created_on AS created_on');
        //$this->db->group_by( 'self.pay_type_id' );
        
        $this->db->join( TBL_PAY_TYPE . ' AS ptype', 'ptype.id = self.pay_type_id' );
        $q = $this->db->get_where( TBL_PAY . ' AS self', $where );
        return $q->result_array();
    }    
    
    function manipulate() {
        $post   = $this->input->post();
        $date_now = date("Y-m-d H:i:s");
        $session_data = $this->session->userdata( TBL_PREFIX . 'user_logged_in' );
        $segments = $this->uri->segments;
        $page   = $segments[2];
        $is_add = $page == 'add' || $page == 'insert' ? TRUE : FALSE;
        $db_id  = $is_add ? '' : $segments[3];
        $service_id = $is_add ? $segments[3] : '';
        
        #echo "<pre>Data: "; print_r( $post ); die;
        $services = $this->service_type_exists($post['fn_type']);
        
        $gst_details = $this->get_amount_by_service_id($post['fn_type']);
        
        if($gst_details->gst == 1) {
            $gst = get_general_setting()->gst; 
            $gst_amount = ($gst / 100) * ($post['amount']);
        } else {
            $gst = '0.00';
            $gst_amount = '0.00';
        }
        
        $q = $this->db->get_where( TBL_SERVICE, array('id' => $post['fn_type']) );
        $service = $q->row_array();
        
        $q = $this->db->get_where( TBL_SERVICE_MENU, array('id' => $service['menu_name']) );
        $main_service = $q->row_array();
        
        $total_amt = $paid_amt = 0;
        $e = explode(( strpos( $post['fn_date'], '/' ) > 0 ? '/' : '-' ), $post['fn_date']); // dd-mm-yyyy
        $fn_date = $e[2] . '-' . $e[1] . '-' .$e[0];
        
        $args = array(
            'service_id'        => $post['fn_type'],
            'bh_period'         => $post['fn_period'],
            
            'bh_from_date'      => $fn_date . ' ' . $post['fn_from_time'],
            'bh_to_date'        => $fn_date . ' ' . $post['fn_to_time'],
            'bh_name'           => $post['name'],
            'bh_tamil_name'     => htmlspecialchars( $post['tamil_name'] ),
            'bh_contact_no'     => $post['contact_no'],
            'bh_email'          => $post['email'],
            'bh_address'        => $post['address'],
            'bh_desc'           => $post['desc'],
            'bh_evt_type'       => $post['evnt_type'],
            'gst'               => $gst,
            'gst_value'         => $gst_amount
        );

       
        //echo "<pre>Data: "; print_r( $args ); die;
        
        if( $is_add ) :
            // Insert 

            $args['created_on'] = $date_now;
            $args['created_by'] = $session_data['user_id'];

            
            /*
            * Get last Receipt ID
            */
            $this->db->select('COUNT(id) AS last_count');
            $q = $this->db->get_where( TBL_BH, array( 'service_id' => $service['id'] ) );
            $rec_id = $q->row_array()['last_count'];

            $receipt_no = '0001';
            if($services->num_rows() > 0) {
                $last_receipt_no = $services->row()->bh_rec_no;
                $last_no = (int)$last_receipt_no;
                $receipt_no = (($last_no + 1)  > 9999) ? ($last_no + 1)  : sprintf('0%03d', ($last_no + 1) );
            }
            $args['bh_rec_no'] = $receipt_no;
            
            $this->db->insert( TBL_BH, $args );
            $db_id = $this->db->insert_id();

        elseif( !empty( $db_id ) ):
            // Update
            $args['updated_on'] = $date_now;
            $args['updated_by'] = $session_data['user_id'];
            
            $where = array(
                'id' => $db_id,
            );
            
            /*
             * Get Previous Paid amount
             */
            $q = $this->db->get_where( TBL_BH, $where );
            $db_row = $q->row_array();
            $paid_amt += $db_row['bh_paid_amount'];
            
            $receipt_no = $db_row['bh_rec_no'];

            $this->db->update( TBL_BH, $args, $where );

        endif;
        
        

        /*
         * Add Service Amount
         */
        #$total_amt += $service['amount'];
        $total_amt += $post['amount'];
        
        /*
         * Payments
         */
        
        // Drop all previous payments
        //$this->db->delete( TBL_PAY, array( 'pay_ref_type'  => 7, 'pay_ref_id'    => $db_id ) );
        $paid_now = 0;
        foreach( $post['pay_type'] as $pay_type_id => $pay_amt ) :
            if( empty( $pay_amt ) ) :
                // Skip empty amounts ..
                continue;
            endif;

            $this->db->select('slug');
            $q = $this->db->get_where( TBL_PAY_TYPE, array('id' => $pay_type_id) );
            $prow = $q->row_array();
           
            $pay_details = array();
            if( $prow['slug'] == 'cheque' && !empty($pay_amt) ) :
               // Cheque
               $e = explode('-', $post['chq_date']);
               $pay_details = array(
                   'name'   => $post['chq_bnk_name'],
                   'no'   => $post['chq_no'],
                   'date'   => $e[2].'-'.$e[1].'-'.$e[0],
               );
            endif;
           
            $pay_details = json_encode( $pay_details );             
            
            $paid_amt += $pay_amt;
            $paid_now += $pay_amt;
            $args = array(
                'pay_ref_type'  => 7, // For Billing Hall Booking
                'pay_ref_id'    => $db_id, // Main ID
                'pay_type_id'   => $pay_type_id,
                'pay_amount'    => $pay_amt,
                'pay_details'   => $pay_details,
                'created_on'    => $date_now,
                'created_by'    => $session_data['user_id'],
            );
            
            // Insert
            $this->db->insert( TBL_PAY, $args );

        endforeach;    

        
        $args = array(
            'bh_amount'     => $total_amt,
            'bh_paid_amount'=> $paid_amt,
            'bh_pay_status' => $paid_amt >= $total_amt ? '3' : ( $paid_amt > 0 ? '2' : '1' ),
        );
        
        $this->db->update( TBL_BH, $args, array( 'id' => $db_id ) );
        
        if($paid_now > 0) {
            
            /*
             * Print
             */
            $args = array(
                'type'      => '3', // Default = 1 // Normal (Double print), '2' => Kattalai Archanai,   '3' = Hall Booking | Wedding Hall, '4' => 'Madapalli', '5' => Archanai, '6' => Common
                'rec_no'    => $receipt_no,
                'title'     => $main_service['service_name']. ' - '.$service['name'],
                'amount'    => array($total_amt, $paid_amt, $paid_now),
                'gst'       => $gst_details->gst,
                'copy'      => TRUE,
                'printType' => FALSE,
                
                'data'      => array( 
                    
                    
                    // Type = 1 && 3 && 4
                    'name'      => array($post['name'] . ( !empty( $post['tamil_name'] ) ? ' / '.tamil( $post['tamil_name'] ) : '') ),
                    'contact_no'=> $post['contact_no'],
                    'email'     => $post['email'],
                    
                    // Type = 1 && 3
                    'address'   => $post['address'],
                    'desc'      => $post['desc'],
                    
                    
                    // Type = 3 
                    'date'      => $post['fn_date'],
                    'time'      => $post['fn_from_time'] . ( !empty( $post['fn_to_time'] ) ? ' - '.$post['fn_to_time'] : '' ),
                    'period'    => $post['fn_period'] == '1' ? 'Morning / காலை' : 'Evening / மாலை',
                    'event_type'=> $post['evnt_type'],    
                    
                ),
                
                
            );
            
            print_rec($args);
        }
    }
    
    function delete( $id,$hidden_del_reason ) {
        
        $args = array(
            'pay_amount'    => 0, // Deleted
        );
        
        $this->db->update( TBL_PAY, $args, array( 'pay_ref_type' => 7, 'pay_ref_id' => $id ) );
        
        $update = array(
            'bh_paid_amount' => 0,
            'del_reason'     => $hidden_del_reason,
            'status' => 2
        );
        
        $this->db->where('( self.id = '. $id .' OR self.self_id = '. $id .')', '', FALSE );
        $this->db->update( TBL_BH . ' as self', $update, array() );
        
    }
    
    function ajax() {
        $post = $this->input->post();
        $date_now = date("Y-m-d H:i:s");
        $session_data = $this->session->userdata( TBL_PREFIX . 'user_logged_in' );
        
        switch( $post['act'] ) :
            case 'nos' :
                // Get all "Ref. No" OR "Contact No", based on type
                $results = $this->get_nos();
                
            break;
        
            case 'check' :
                
                //SELECT * FROM (`thiru_bill_hall_booking`) WHERE ( TIME(`bh_from_date`) >= '21:08' AND  TIME(`bh_from_date`) <= '22:00') OR ( TIME(`bh_to_date`) >= '21:08' AND  TIME(`bh_to_date`) <= '22:00')
                //SELECT * FROM (`thiru_bill_hall_booking`) WHERE ( DATE(`bh_from_date`) >= $from_date AND  DATE(`bh_from_date`) <= $to_date) OR ( DATE(`bh_to_date`) >= $from_time AND  DATE(`bh_to_date`) <= $to_time)
                
                /*
                 * 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_BH .'`) WHERE ( ( DATE_FORMAT(`bh_from_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND  DATE_FORMAT(`bh_from_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'") OR ( DATE_FORMAT(`bh_to_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND  DATE_FORMAT(`bh_to_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'")  OR ( DATE_FORMAT(`bh_from_date`, "%Y-%m-%d %H:%i") <= "'. $from_date .'" AND DATE_FORMAT(`bh_to_date`, "%Y-%m-%d %H:%i") >= "'. $to_date .'"))';
                
                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"';
                $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;
                
                if( !empty( $results ) ) :
                    $results['from_time'] = date( "H:i", strtotime( $results['bh_from_date'] ) );
                    $results['to_time'] = date( "H:i", strtotime( $results['bh_to_date'] ) );
                endif;
                
            break;    
            
            case 'availability' :
                
                /*
                 * Get all
                 */
                $where['self.service_id'] = $post['service_id'];
                if( !empty( $post['from_date'] ) ) :
                    $e = explode("-", $post['from_date']);
                    $where['DATE(self.bh_from_date) >= '] = $e[2] . '-' . $e[1] . '-' . $e[0]; 
                endif;
                if( !empty( $post['to_date'] ) ) :
                    $e = explode("-", $post['to_date']);
                    $where['DATE(self.bh_to_date) <= '] = $e[2] . '-' . $e[1] . '-' . $e[0]; 
                endif;
                
                $results = $this->get_all( '', $where );
                //echo "<pre>Data: "; print_r( $results ); die;
                
            break; 
            
            case 'balance' :
                
                /*
                 * Get balance
                 */
                $where['self.status']       = 1;
                $where['self.service_id']   = $post['service_id'];
                $where['self.id']           = $post['val'];

                $this->db->order_by('self.created_on', 'DESC');
                $q = $this->db->get_where( TBL_BH . ' AS self', $where );
                $results = $q->row_array();
                
                //echo "Query:".$this->db->last_query(); die;
                //echo "<pre>Data: "; print_r( $results ); die;
                
            break; 
        
            case 'pay' :
                
                /*
                 * Set paid amount
                 */
                $where['id']        = $post['id'];
                $q = $this->db->get_where( TBL_BH, $where );
                $bh = $q->row_array();
                
                $now_amount = $post['val'] + $bh['bh_paid_amount'];
                $args = array(
                    'bh_paid_amount'    => $now_amount,
                    'bh_pay_status'     => $now_amount >= $bh['bh_amount'] ? '3' : $bh['bh_pay_status']
                );
                
                $is_updated = $this->db->update( TBL_BH, $args, $where );
                
                $q = $this->db->query( 'SHOW COLUMNS FROM `'. TBL_BH .'`' );
                $cols = $q->result_array();
                
                foreach($cols as $k => $col ) :
                    $col = $col['Field'];
                    if($col != 'id') :
                        $args[$col] = array_key_exists( $col, $args ) ? $args[$col] : $bh[$col];
                    endif;
                endforeach;
                
                $args['self_id'] = $post['id'];
                $args['bh_rec_no'] = generate_rec_no( TBL_BH );
                $args['updated_on'] = $date_now;
                $args['updated_by'] = $session_data['user_id'];
                
                $this->db->insert( TBL_BH, $args);
                $new_id = $this->db->insert_id();
                
                
                $pay_details = array();
                if( $post['cheque'] == 'pay-cheque' ) :
                   // Cheque
                   $e = explode('-', $post['cheque_date']);
                   $pay_details = array(
                       'name'   => $post['bank_name'],
                       'no'   => $post['cheque_no'],
                       'date'   => $e[2].'-'.$e[1].'-'.$e[0],
                   );
                endif;
               
                $pay_details = json_encode( $pay_details );
                
                /*
                 * Update Payments Table
                 */
                $args = array(
                    'pay_ref_type' => 7, // For Billing Hall Booking
                    'pay_parent_id'=> $post['id'],
                    'pay_ref_id'   => $new_id,
                    'pay_type_id'  => $post['mode'], // Default
                    'pay_amount'   => $post['val'],
                    'pay_desc'     => 'Short payment',
                    'pay_details'  => $pay_details,
                    'created_on'   => $date_now,
                    'created_by'   => $session_data['user_id'], 
                );
                $this->db->insert( TBL_PAY, $args );
                
                if($is_updated) :
                    $this->print_pay_data($new_id,array('now_paid' => $post['val'], 'pay_type' => array( $args['pay_type_id'] => $args['pay_amount'])));
                endif;
                
            break; 
            
        endswitch; // 
        
        return $results;
    }
    
    public function service_type_exists($type) {
        $this->db->where('service_id',$type);
        //$this->db->where('status !=',2);
        $this->db->order_by('bh_rec_no','desc');
        return $this->db->get(TBL_BH);
    }
    
    public function get_amount_by_service_id($id) {
        $this->db->where('id',$id);
        return $this->db->get(TBL_SERVICE)->row();
    }
    
    public function print_pay_data($id,$amount) {
        /*
         * Set paid amount
         */
        $where['id']        = $id;
        $q = $this->db->get_where( TBL_BH, $where );
        $bh = $q->row_array();
        
        $now_amount = !empty( $data['now_paid'] ) ? $data['now_paid'] : $bh['bh_paid_amount'];
        
        $service = $this->get_amount_by_service_id($bh['service_id']);
        
        /*
         * Print
         */
        $args = array(
            'type'      => '3', // Default = 1 // Normal (Double print), '2' => Kattalai Archanai,   '3' = Hall Booking | Wedding Hall, '4' => 'Madapalli', '5' => Archanai, '6' => Common
            'rec_no'    => $bh['bh_rec_no'],
            'title'     => 'Hall Booking - '.$service->name,
            'amount'    => array($bh['bh_amount'], $bh['bh_paid_amount'], $now_amount),
            'gst'       => $service->gst,
            'copy'      => TRUE,
            'pay_type' => $data['pay_type'],
            
            'data'      => array( 
                
                
                // Type = 1 && 3 && 4
                'name'      => array($bh['bh_name'] . ( !empty( $bh['bh_tamil_name'] ) ? ' / '.tamil( $bh['bh_tamil_name'] ) : '') ),
                'contact_no'=> $bh['bh_contact_no'],
                'email'     => $bh['bh_email'],
                
                // Type = 1 && 3
                'address'   => $bh['bh_address'],
                'desc'      => $bh['bh_desc'],
                
                
                // Type = 3 
                'date'      => date('d-m-Y',strtotime($bh['bh_from_date'])),
                'time'      => date('h:i',strtotime($bh['bh_from_date'])) . ( !empty( $bh['bh_to_date'] ) ? ' - '. date('h:i',strtotime($bh['bh_to_date'])) : '' ),
                'period'    => $bh['bh_period'] == '1' ? 'Morning / காலை' : 'Evening / மாலை',
                'event_type'=> $bh['bh_evt_type'],    
                
            ),
        );
       return print_rec($args);
    }
    
    public function get_part_pay_rec_no($id) {
        $this->db->select('*, GROUP_CONCAT(DISTINCT bh_rec_no SEPARATOR ",") as receipt_no',FALSE);
        $this->db->where('self_id',$id);
        return $this->db->get(TBL_BH)->row();
    }

}
