<?php

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

class Wedding_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_by_id($id) {
        $this->db->where('id',$id);
        return $this->db->get(TBL_BWH)->row();
    }
    
    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.bwh_rec_no, self.bwh_contact_no');
        $q = $this->db->get_where( TBL_BWH . ' AS self', $where );
        return $q->result_array();
    }
    
    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.bwh_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_BWH . ' AS self', $where );
        //echo "Query:".$this->db->last_query(); die;

        return $q->result_array();
    }
    
    
    function get_payments( $bwh_id ) {
        $where['self.pay_ref_type'] = 18; // For Billing Wedding Hall Booking
        $where['self.status'] = 1;
        $this->db->where('( self.pay_ref_id = '. $bwh_id .' OR self.pay_parent_id = '. $bwh_id .')', '', FALSE );
        
        $this->db->order_by('self.created_on', 'DESC');
        $this->db->select('*, self.created_on AS created_on');
        
        $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: ".$db_id; 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'],
            'bwh_period'         	=> $post['fn_period'],
            
            'bwh_from_date'      	=> $fn_date . ' ' . $post['fn_from_time'],
            'bwh_to_date'        	=> $fn_date . ' ' . $post['fn_to_time'],
            'bwh_groom_name'     	=> $post['groom_name'],
            'bwh_bride_name'     	=> $post['bride_name'],
            'bwh_contact_name'   	=> $post['contact_name'],
            'bwh_contact_no'     	=> $post['contact_no'],
            'bwh_email'          	=> $post['email'],
            'bwh_address'        	=> $post['address'],
            'bwh_desc'           	=> $post['desc'],
            'gst'                	=> $gst,
            'gst_value'          	=> $gst_amount,
            
            'bwh_groom_proof'   	=> !empty( $post['proof_1'] ) ? $post['proof_1'] : '0',
            'bwh_bride_proof'   	=> !empty( $post['proof_2'] ) ? $post['proof_2'] : '0',
            'bwh_invitation_proof'	=> !empty( $post['proof_3'] ) ? $post['proof_3'] : '0',
			'bwh_rom_proof'			=> !empty( $post['proof_4'] ) ? $post['proof_4'] : '0',
            
        );

       
       // 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_BWH, 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()->bwh_rec_no;
                $last_no = (int)$last_receipt_no;
                $receipt_no = (($last_no + 1)  > 9999) ? ($last_no + 1)  : sprintf('0%03d', ($last_no + 1) );
            }
            $args['bwh_rec_no'] = $receipt_no;
            

            $this->db->insert( TBL_BWH, $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_BWH, $where );
            $db_row = $q->row_array();
            $paid_amt += $db_row['bwh_paid_amount'];
            
            $receipt_no = $db_row['bwh_rec_no'];

            $this->db->update( TBL_BWH, $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'  => 18, '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;
            #if($paid_now > 0) {
                $args = array(
                    'pay_ref_type'  => 18, // For Billing Wedding 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(
            'bwh_amount'     => $total_amt,
            'bwh_paid_amount'=> $paid_amt,
            'bwh_pay_status' => $paid_amt >= $total_amt ? '3' : ( $paid_amt > 0 ? '2' : '1' ),
        );
        
        $this->db->update( TBL_BWH, $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' => TRUE,
                
                'data'      => array( 
                    
                    
                    // Type = 1 && 3 && 4
                    'name'      => array($post['contact_name'], $post['groom_name'], $post['bride_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'] : '' ),
                    'proof'     => array(!empty( $post['proof_1'] ) ? $post['proof_1'] : '0', !empty( $post['proof_2'] ) ? $post['proof_2'] : '0', !empty( $post['proof_3'] ) ? $post['proof_3'] : '0',!empty( $post['proof_4'] ) ? $post['proof_4'] : '0'),
                    'period'    => $post['fn_period'] == '1' ? 'Morning / காலை' : 'Evening  / மாலை',
                    
                    
                ),
                
            );
            
            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' => 18, 'pay_ref_id' => $id ) );
        
        $args = array(
            'bwh_paid_amount' => 0,
            'status' => 2,
            'del_reason' =>$hidden_del_reason
        );
        
        $this->db->where('( self.id = '. $id .' OR self.self_id = '. $id .')', '', FALSE );
        $this->db->update( TBL_BWH . ' as self', $args, 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(`bwh_from_date`) >= '21:08' AND  TIME(`bwh_from_date`) <= '22:00') OR ( TIME(`bwh_to_date`) >= '21:08' AND  TIME(`bwh_to_date`) <= '22:00')
                
                /*
                 * 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_BWH .'`) WHERE ( ( DATE_FORMAT(`bwh_from_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND  DATE_FORMAT(`bwh_from_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'") OR ( DATE_FORMAT(`bwh_to_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND  DATE_FORMAT(`bwh_to_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'") )';
                $qry = 'SELECT * FROM (`'. TBL_BWH .'`) WHERE ( ( DATE_FORMAT(`bwh_from_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND DATE_FORMAT(`bwh_from_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'") OR ( DATE_FORMAT(`bwh_to_date`, "%Y-%m-%d %H:%i") >= "'. $from_date .'" AND DATE_FORMAT(`bwh_to_date`, "%Y-%m-%d %H:%i") <= "'. $to_date .'") OR ( DATE_FORMAT(`bwh_from_date`, "%Y-%m-%d %H:%i") <= "'. $from_date .'" AND DATE_FORMAT(`bwh_to_date`, "%Y-%m-%d %H:%i") >= "'. $to_date .'"))';
                
                if( !empty( $post['load_id'] ) ) :
                    // Ignore current page ID..
                    $qry .= ' AND `id` != "'.$post['load_id'].'"';
                endif;
                
                $qry .= ' AND `service_id` = "'. $post['service_id'].'" AND `status` = "1"';
                $q = $this->db->query( $qry );
                
                $results = $q->row_array();
                //echo $qry;
                //echo "<pre>Data: "; print_r( $results ); die;
                
                if( !empty( $results ) ) :
                    $results['from_time'] = date( "H:i", strtotime( $results['bwh_from_date'] ) );
                    $results['to_time'] = date( "H:i", strtotime( $results['bwh_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.bwh_from_date) >= '] = $e[2] . '-' . $e[1] . '-' . $e[0]; 
                endif;
                if( !empty( $post['to_date'] ) ) :
                    $e = explode("-", $post['to_date']);
                    $where['DATE(self.bwh_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_BWH . ' 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_BWH, $where );
                $bwh = $q->row_array();
                
                $now_amount = $post['val'] + $bwh['bwh_paid_amount'];
                $args = array(
                    'bwh_paid_amount'    => $now_amount,
                    'bwh_pay_status'     => $now_amount >= $bwh['bwh_amount'] ? '3' : $bwh['bwh_pay_status']
                );
                
                $is_updated = $this->db->update( TBL_BWH, $args, $where );

                $q = $this->db->query( 'SHOW COLUMNS FROM `'. TBL_BWH .'`' );
                $cols = $q->result_array();
                
                foreach($cols as $k => $col ) :
                    $col = $col['Field'];
                    if($col != 'id') :
                        $args[$col] = array_key_exists( $col, $args ) ? $args[$col] : $bwh[$col];
                    endif;
                endforeach;
                
                $args['self_id'] = $post['id'];
                $args['bwh_rec_no'] = generate_rec_no( TBL_BWH );
                $args['updated_on'] = $date_now;
                $args['updated_by'] = $session_data['user_id'];
                
                $this->db->insert( TBL_BWH, $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' => 18, // For Billing Wedding 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('bwh_rec_no','desc');
        return $this->db->get(TBL_BWH);
    }
    
    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, $data ) {
        /*
         * Set paid amount
         */
        $where['id']        = $id;
        $q = $this->db->get_where( TBL_BWH, $where );
        $bwh = $q->row_array();
        
        $now_amount = !empty( $data['now_paid'] ) ? $data['now_paid'] : $bwh['bwh_paid_amount'];
        
        $service = $this->get_amount_by_service_id($bwh['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'    => $bwh['bwh_rec_no'],
            'title'     => 'Wedding Hall Booking - '.$service->name,
            'amount'    => array($bwh['bwh_amount'], $bwh['bwh_paid_amount'], $now_amount),
            'gst'       => $service->gst,
            'copy'      => TRUE,
            'pay_type' => $data['pay_type'],
            'printType' => TRUE,
            
            'data'      => array( 
                
                
                // Type = 1 && 3 && 4
                'name'      => array($bwh['bwh_contact_name'], $bwh['bwh_groom_name'], $bwh['bwh_bride_name']),
                'contact_no'=> $bwh['bwh_contact_no'],
                'email'     => $bwh['bwh_email'],
                
                // Type = 1 && 3
                'address'   => $bwh['bwh_address'],
                'desc'      => $bwh['bwh_desc'],
                
                
                // Type = 3 
                'date'      => date('d-m-Y',strtotime($bwh['bwh_from_date'])),
                'time'      => date('h:i',strtotime($bwh['bwh_from_date'])) . ( !empty( $bwh['bwh_to_date'] ) ? ' - '.date('h:i',strtotime($bwh['bwh_to_date'])) : '' ),
                'proof'     => array(!empty( $bwh['bwh_groom_proof'] ) ? $bwh['bwh_groom_proof'] : '0', !empty( $bwh['bwh_bride_proof'] ) ? $bwh['bwh_bride_proof'] : '0', !empty( $bwh['bwh_invitation_proof'] ) ? $bwh['bwh_invitation_proof'] : '0', !empty( $bwh['bwh_rom_proof'] ) ? $bwh['bwh_rom_proof'] : '0'),
                'period'    => $bwh['bwh_period'] == '1' ? 'Morning / காலை' : 'Evening  / மாலை',                
            ),
            
        );
       return print_rec($args);
       
    }
    
    public function get_part_pay_rec_no($id) {
        $this->db->select('*, GROUP_CONCAT(DISTINCT bwh_rec_no SEPARATOR ",") as receipt_no',FALSE);
        $this->db->where('self_id',$id);
        return $this->db->get(TBL_BWH)->row();
    }

    public function get_payment_date( $bw_id ) {
        $where['self.pay_ref_type'] = 18; // Billing Kattalai Archanai
        $where['self.pay_ref_id'] = $bw_id;
        $where['self.status'] = 1;        
        $q = $this->db->get_where( TBL_PAY . ' AS self', $where );
        return $q->result_array();
    }

}
