<?php

/* 
 * Author       : Jai K
 * Purpose      : Model for Accessing Ajax req. for any of the modules
 * Created On   : 2016-08-25 13:17
 */


class Common_Model extends CI_Model {
    
    function __construct() {
        
        parent::__construct();
        
    }
    
    function get($tbl_name, $args = array()) {
        $is_where = TRUE;
        if( !empty($args['order_by']) ) :
            $this->db->order_by($args['order_by']);
            $is_where = FALSE;
        endif;
        
        $where = !empty($args['where']) ? $args['where'] : ( $is_where ? $args : array() );
        
        if( empty($where['self.status']) ) :
            $where['self.status'] = 1;
        endif;
        
        $q = $this->db->get_where($tbl_name.' AS self', $where);
        return $q->result_array();
        
    }
    
    function get_exe_install($hwID = ''){
        
        if( empty( $hwID ) ) :
            return '2'; // Hardware ID is Not Found/Unknown
        endif;
        
        $net_ip = REMOTE_ADDR;
        if( strpos($net_ip, '::') === FALSE ) :
            $e = explode('.', $net_ip);
            $net_ip = $e[0].'.'.$e[1].'.'.$e[2].'.*';
        endif;
        
        //$this->db->select('COUNT(id) AS installed_cnt');
        $this->db->select('id, value');
        $this->db->like('name', $like );
        $q = $this->db->get_where( TBL_BILL_SETT, array('slug' => 'exe_install', 'name' => $net_ip, 'status' => 1) );
        $results = $q->result_array();
        
        $id = 0;
        $install_cnt = 0;
        foreach( $results as $k => $row ) :
            if( $row['value'] == $hwID ) :
                $id = $row['id'];
            endif;
            
            ++$install_cnt;
        endforeach;
        
        if( $id == 0 && $install_cnt >= INSTALL_LIMIT  ) :
            // Exceeded the Installation Limit
            return '0';
        elseif( $id == 0 ):
            // Add This Installation 
            $this->db->insert( TBL_BILL_SETT, array( 'slug' => 'exe_install', 'name' => $net_ip, 'value' => $hwID, 'created_on' => date("Y-m-d H:i:s") ) );
        else :
            // Update Last Access time
            $this->db->update( TBL_BILL_SETT, array('updated_on' => date("Y-m-d H:i:s")),  array( 'id' => $id) );
        endif;

        return '1';
    }
    
    function set_server_down($is_down = TRUE ){
        
        $this->db->update( TBL_BILL_SETT, array('value' => $is_down ? '1' : '0'), array('slug' => 'server_down') );
        
        if( $is_down ) :
            /*
             * Reset the Session Values
             */
            $user_data = get_user_data();
            if( !empty($user_data['user_id']) ) :
                set_user_data($this->user_model->get_by_id($user_data['user_id']));
            endif;
        endif;
    }
    
    function sql( $db, $sql ) {
        $pdo = $db->prepare( $sql );
        $pdo->execute();
        return $pdo->fetchAll(PDO::FETCH_ASSOC);
    }
    
    function get_last_row($rec_no_field = 'bari_rec_no', $where = array()){
        $post = $this->input->post();
        
        $this->db->select('id, '. $rec_no_field.' AS rec_no');
        $this->db->order_by('id DESC');
        $this->db->limit(1);
        $q = $this->db->get_where( $post['tbl'], $where );
        /*if( !empty( $post['rec_nos'] ) ) :
            echo 'Q: '. $this->db->last_query();
        endif;*/
        return $q->row_array();
    }
    
    function import(){
        $return = array();
        $post = $this->input->post();
        
        // Get port no
        $e = explode( ':', $post['host'] );
        $db_host   = $e[0];
        $db_port   = $e[1];
        
        try {
        
        $db_str = 'mysql:host='.$db_host.';dbname='.$post['db'];
        $db     = new PDO($db_str, $post['user'], $post['pass']);
        
            if( $db ) :

                switch( $post['act'] ) :
                
                    case 'completed' :
                        $this->set_server_down(FALSE);
                        
                    break;    

                    case 'import' :

                        // Import
                        $sql = "(SELECT GROUP_CONCAT(COLUMN_NAME) AS a FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME != 'id' AND TABLE_NAME = '". $post['tbl'] ."' AND TABLE_SCHEMA = '". $post['db'] ."')";
                        $q = $this->db->query($sql);
                        $clmns = $q->row_array()['a'];
                        
                        $sql = 'SELECT '. $clmns .' FROM `'. $post['tbl'] .'` LIMIT '.$post['limit'];
                        $results = $this->sql($db, $sql);
                        
                        
                        $rec_clm = $post['rec_clm']; 
                        if( empty( $rec_clm ) ) :
                            
                            $rec_clm = 'NO';
                            preg_match( '/[,](.*?rec_no),/', $clmns, $mtchs);
                            
                            if( !empty( $mtchs ) ) :
                                $rec_clm =  $mtchs[1];
                            endif;
                        endif;    
                        $return['rec_clm'] = $rec_clm; 
                        
                        
                        $ids = array();
                        $rec_nos = json_decode( $post['rec_nos'], TRUE );
                        
                        $sql = 'INSERT INTO `'. $post['tbl'] .'` ('.$clmns.') VALUES ';
                        foreach( $results as $k => $row ) :
                            $val_str = '('; 
                            $bari_rec_cnt = '';
                            $ids[] = $row['id'];
                            unset($row['id']);
                            
                            foreach( $row as $clmn => $value ) :
                                
                                if( $rec_clm != 'NO' && $clmn == $rec_clm ) :
                                    // Rec. No Column
                                    
                                    if( $post['tbl'] == 'thiru_bar_items' ) :
                                        $whr = array();
                                        $cat_prefix = '';
                                        $indx = 'indx';
                                        
                                        if( $row['is_category'] == "1" ) :
                                            // With Category
                                            $cat_prefix = preg_replace('/\d+/', '', $value);
                                            $indx .= '-cat'. $row['bari_cat_id'];
                                        
                                            $whr['bari_cat_id'] = $row['bari_cat_id']; 
                                        endif;
                                        
                                        $indx .= '-isc'.$row['is_category'];
                                        $whr['is_category'] = $row['is_category']; 
                                        
                                        /*$indx .= '-iscg'.$row['is_cat_group'];
                                        $whr['is_cat_group'] = $row['is_cat_group']; */
                                        
                                        if( $row['is_cat_group'] != '1' ) :
                                            // Single
                                            $indx .= '-itm' . $row['bari_item_id'];
                                            $whr['bari_item_id'] = $row['bari_item_id'];
                                        endif;
                                            
                                        $last_row = $rec_nos[$indx];
                                        if( empty( $last_row['id'] ) ) :
                                            // Get Last Rec. No (From Current database)
                                            $last_row = $this->get_last_row( 'bari_rec_no', $whr );
                                        endif;
                                        
                                        $bari_rec_cnt = ($last_row['rec_no']+1);
                                        
                                        
                                    else :
                                        
                                       /*
                                        * For Other Table "Rec. No"
                                        */     
                                        $indx = $post['tbl'];
                                        if( empty( $rec_nos[$indx] ) ) :
                                            $last_row = $this->get_last_row( $rec_clm, array() );
                                        else :
                                            $last_row = $rec_nos[$indx]; 
                                        endif;
                                                
                                    endif;
                                    
                                    $last_row['rec_no'] = ++$last_row['rec_no'];
                                    $last_row['id'] = ++$last_row['id'];
                                    $rec_nos[$indx] = $last_row;
                                    
                                    $value = $cat_prefix . fill_digits( $last_row['rec_no'], 5);
                                    //$last_id = $last_row['id'];

                                elseif( $clmn == 'bari_rec_cnt' ) :
                                    $value = $bari_rec_cnt;
                                endif;
                                
                                $val_str .= '"'. $value . '", ';
                            endforeach; 
                            
                            $sql .= substr( $val_str, 0, -2 ) . '), ';
                                
                        endforeach;
                        
                        $sql = substr( $sql, 0, -2);
                        
                        $return['rec_nos'] = $rec_nos;
                        $return['insert'] = $sql;
                        
                        /*
                         * Insert
                         */
                        $this->db->query( $sql );
                        
                        /*
                         * Delete those inserted record from "Alt. Server"
                         */
                        $this->db->where_in('id', $ids);
                        //$this->db->update( $post['tbl'], array('status' => '10'), array() );
                        $this->db->delete( $post['tbl'], array() );
                        
                        //$return['err_no'] = 0;
                        //echo '<pre>Data:'; print_r( $results ); die;
                    break;    

                    case 'backup':

                        /*
                         * Take backup for Alt Server.
                         */
                        $backup_path = 'db/alt-server/';
                        if( !file_exists( $backup_path ) ) :
                            mkdir($backup_path, 0777, TRUE);
                            chmod($backup_path, 0777);
                        endif;
                        
                        preg_match('/\:\/\/(.*?)\/$/', $post['url'], $mtchs);
                        $backup_path .= str_replace( '/', '_', str_replace( '\\', '_', $mtchs[1] ) ) .'_'. date("d-m-Y-H-i-s").'.sql';
                        
                        // Get port no
                        $sql = 'c:\xampp\mysql\bin\mysqldump.exe --host='. $db_host. ( (!empty( $db_port ) && $db_host != "localhost" && $db_host != "127.0.0.1" ) ? ' --port='.$db_port : '' ) .' --user='.$post['user'].( !empty( $post['pass'] ) ? ' --password='.$post['pass'] : '' ). ' '. $post['db'].' > '.$backup_path;
                        $results = exec( $sql, $op );
                        
                        if( filesize($backup_path) > 0 ) :
                            // Error
                            $return['backup_path'] = BASE_URL . $backup_path;
                        else :
                            unlink( $backup_path );
                        endif;
                        
                    break;    


                    case 'total' :

                        /*
                         * Get Total No. of records
                         */
                        $sql = 'SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "'. $post['db'] .'" AND table_name NOT LIKE "%_settings" AND TABLE_ROWS > 0';
                        $return = $this->sql($db, $sql);
                        
                        if( empty( $return ) ) :
                            // No more Record need to import
                            $this->set_server_down(FALSE);
                        endif;

                    break;

                endswitch;
            endif;
            
        }catch(Exception $e){
            // Some problem with remote Server
            $return['err_no'] = 0;
        }
        
        //echo '<pre>Data:'; print_r($return); die;
        return $return;
        
        
    }
    
    function scan(){
        /*
         * Kiosk - Card Scan Events
         */
        $return = array();
        
        // Events
        $this->db->order_by('value ASC, created_on ASC');
        $this->db->where( 'CURDATE() BETWEEN DATE(created_on) AND DATE(updated_on)', '', FALSE );
        
        $q = $this->db->get_where( TBL_BILL_SETT, array( 'status' => 1, 'slug' => 'event' ) );
        $return['event'] =  $q->result_array();
        //echo 'Q: '. $this->db->last_query(); die;
        
        // Video URL
        $q = $this->db->get_where( TBL_BILL_SETT, array( 'status' => 1, 'slug' => 'video_url' ) );
        $return['video_url'] =  $q->row_array();
        
        // Ad
        $this->db->order_by('created_on ASC');
        $q = $this->db->get_where( TBL_BILL_SETT, array( 'status' => 1, 'slug' => 'advertisement' ) );
        $return['advertisement'] =  $q->result_array();
        
        return $return;
    }
    
    function get_booked(){
        $post = $this->input->post();
        
        /*
         * Book Tables List
         */
        $tbls = get_booking_tables();
        
        $return = array(
            'tbls'  => $tbls,
        );
        
        if( !empty( $post['cntrl'] ) ) :
            $this->db->select('table_name');
            $q = $this->db->get_where( TBL_SERVICE_MENU, array('controller_name' => $post['cntrl'], 'status' => 1) );
            $service_menu = $q->row_array();
            
            if(array_key_exists($service_menu['table_name'], $tbls) ) :
                $return['tbl']= $service_menu['table_name'];
                $post['tbl']    = $return['tbl'];        
            endif;
        endif;
        
        $tbls = !empty( $post['tbl'] ) ? array($post['tbl'] => $tbls[$post['tbl']]) : $tbls;
        
        foreach( $tbls as $tbl_name => $val ) :
            $e = explode('`', $val);
            $prefix = !empty( $e[0] ) ? $e[0] .'_' : '';
            $clm_book_date = $prefix . ( !empty( $e[1] ) ? $e[1] : 'booked_date' );
            $clm_rec_no    = $prefix . ( !empty( $e[3] ) ? $e[3] : 'rec_no' ); 
            $clm_name    = $prefix . ( !empty( $e[4] ) ? $e[4] : 'name' ); 
            
            
            $sql = 'SELECT GROUP_CONCAT( `'. $clm_rec_no .'`) AS rec_no, GROUP_CONCAT(`'. $clm_name .'`) AS name, `'. $clm_book_date .'` AS date, COUNT(id) AS total_cnt FROM (`'. TBL_PREFIX . $tbl_name .'`) WHERE `status` = 1';
            if( $e[2] != 'NO_BOOK_STATUS' ) :
                // Book Status
                $sql .= ' AND `'. $prefix.'book_status` = 2';
            endif;
            
            if( !empty( $post['date'] ) ) :
                $sql .= ' AND `'. $clm_book_date .'` LIKE "'. date("Y-m", strtotime($post['date'])) .'%" GROUP BY DATE_FORMAT(`'. $clm_book_date .'`, "%Y-%m-%d" )';
            endif;     
            
            $q = $this->db->query( $sql );
            $res = $q->result_array();
            
            //echo "Q: ". $this->db->last_query(); die;
            
            if( !empty( $res ) ) :
                foreach( $res as $k => $r ) :
                    $d = date("j", strtotime($r['date']));

                    $return['data'][$d][$tbl_name] = $r['total_cnt'] . '`'. $r['rec_no'].'`'.$r['name'];
                    $return['data'][$d]['total'] += $r['total_cnt'];
                endforeach;    
            endif;    
        endforeach;
        
        return $return;
        
    }
    
    function get_settings( $tbl_name = TBL_SETTINGS_BASIC ) {
        $q = $this->db->get_where( $tbl_name, array() );
        return $q->result_array();
    }
    
    function search_dev() {
        $post = $this->input->post();
        $value = $post['value'];
        $where = array(
            'bd.status' => 1,
           // 'bdi.status' => 1
        );
        
        if( $post['include_contact'] == 'true' ) :
            $this->db->where('bd.bd_contact_no', $value);
            $this->db->or_where('bd.bd_ref_no', $value);
        else :
            // Search by only ref. no
            //$where['bd.bd_ref_no'] = $value;
			$this->db->where('bd.bd_contact_no', $value);
            $this->db->or_where('bd.bd_ref_no', $value);
            
        endif;    
        
        $this->db->select('*, bd.id AS bd_id, bd.status AS status');//, bdi.id AS bdi_id');
        //$this->db->join(TBL_BDI . ' AS bdi', 'bdi.bd_id = bd.id');
        $q = $this->db->get_where( TBL_BD . ' AS bd', $where );
        $results = $q->result_array();
        //echo "Query:".$this->db->last_query(); die;

        $return = array();
        foreach( $results  as $k => $row ) :
            $this->db->select('*, bdi.id AS bdi_id');
            $q = $this->db->get_where( TBL_BDI . ' AS bdi', array('bdi.bd_id' => $row['bd_id'], 'bdi.status' => 1) );
            
            $return[$row['bd_id']][] = $row;
            $return[$row['bd_id']]['items'] = $q->result_array();
            
        endforeach;
        
        return $return;
    }
    
    public function get_ref_no($id) {
        $this->db->where('id',$id);
        return $this->db->get(TBL_BD)->row()->bd_ref_no;
    }
    
    function update() {
        $post = $this->input->post();
        $date_now = date("Y-m-d H:i:s");
        $session_data = $this->session->userdata( TBL_PREFIX . 'user_logged_in' );
        parse_str($post['data'], $data);
        $db_id = $data['db_id'];
        
        //echo "<pre>Data: "; print_r( $data ); die;
        
        switch( $post['type'] ) :
            
            case 'rec-sett' :
                
                $this->db->where_not_in('id', $data['cats']);
                $this->db->update(TBL_CATEGORY, array( 'is_separate' => '0' ), array('status' => 1));
                
                $this->db->where_in('id', $data['cats']);
                $this->db->update(TBL_CATEGORY, array( 'is_separate' => '1' ), array('status' => 1));
                
                
                /*$id = $data['rs_cat'];
                $tbl_name = TBL_CATEGORY;
                if( $data['rs_set_for'] == '0' ) :
                    // For Items
                    
                    $id = $data['rs_itm'];
                    $tbl_name = TBL_ITEM;
                
                    $args = array(
                        'god_name'          => $data['rs_god_name'],
                        'receipt'           => $data['rs_receipt'],
                        'is_enable_footer'  => $data['rs_is_enable'],
                        'is_inventory'      => $data['rs_is_inventory'],
                        'order_sequence'    => $data['rs_order'],
                    );
                    
                else :
                    
                    $args = array(
                        'grouping'          => $data['rs_grouping'],
                        'is_separate'       => $data['rs_is_sepr'],
                        'category_order'    => $data['rs_order'],
                    );
                    
                endif;
                
                foreach($args as $k => $arg) :
                    if( empty( $arg ) ) :
                        $args[$k] = 0;
                    endif;
                endforeach;
                
                $this->db->update($tbl_name, $args, array('id' => $id));
                return $id;*/
                
                
            break;
        
            case 'devotee' :
                
                $args = array(
                    'bd_star_id'    => $data['star'],
                    'bd_nric_no'    => $data['nric_no'],
                    'bd_name'       => $data['name'],
                    'bd_tamil_name' => htmlspecialchars( $data['tamil_name'] ),
                    'bd_contact_no' => $data['contact_no'],
                    'bd_email'      => $data['email'],
                    'bd_address'    => $data['address'],
                    
                    'bd_sms_types'   => $data['sms_types'],
                    'lang_switch'    => $post['lang'],
                );
                
                if( empty( $db_id ) ) :
                    // Insert
                    
                    /*
                    * Get last Ref. ID
                    */
                    $this->db->select('COUNT(id) AS last_count');
                    $q = $this->db->get_where( TBL_BD, array() );
                    $ref_id = $q->row_array()['last_count'];
                    
                    $args['bd_ref_no']   = fill_digits( ++$ref_id, 4 );
                    $args['created_on']   = $date_now;
                    $args['created_by']   = $session_data['user_id'];    
                    
                    $this->db->insert( TBL_BD, $args );
                    $db_id = $this->db->insert_id();
                else :
                    // Update
                    $args['updated_on']   = $date_now;
                    $args['updated_by']   = $session_data['user_id'];    
                    
                    $this->db->update( TBL_BD, $args, array('id' => $db_id) );
                    $db_id = $db_id;
                endif;
                
                /*
                 * Update the removed Members
                 */
                if( !empty( $data['removed-childs'] ) ) :
                    $ee = explode(',', trim($data['removed-childs'], ','));
                    foreach( $ee as $ek => $e ) :
                        $args = array(
                            'status' => 2,
                        );
                        $this->db->update( TBL_BDI, $args, array('id' => $e ) );    
                    endforeach;
                endif;
                
                
                if( !empty( $data['mem_name'][0] ) ) :
                    
                    // Members
                    
                    foreach($data['mem_name'] as $k => $mem) :
                        $args = array(
                            'bd_id'         => $db_id,
                            'bdi_star_id'   => $data['mem_star'][$k],
                            'bdi_name'      => $data['mem_name'][$k],
                            'bdi_tamil_name'=> htmlspecialchars( $data['mem_tamil_name'][$k] ),
                            
                            'created_on'    => $date_now,
                            'created_by'    => $session_data['user_id']
                        );
                
                        if( empty( $data['mem_id'][$k] ) ) :
                            // Insert
                            $args['created_on']   = $date_now;
                            $args['created_by']   = $session_data['user_id'];    
                        
                            $this->db->insert( TBL_BDI, $args );
                        else :
                            // Update
                            $args['updated_on']   = $date_now;
                            $args['updated_by']   = $session_data['user_id'];    
                    
                            $this->db->update( TBL_BDI, $args, array('id' => $data['mem_id'][$k]) );    
                        endif;
                
                        
                     
                    endforeach;
                endif;
                return $db_id;
            break;
            
        endswitch;
        
        
        
    }
    
    function pass_code() {
        $post = $this->input->post();
        
        switch( $post['type'] ) :
            case 'new' :
                // Generate New
                
                $code       = $this->user_model->get_code();
                $pass_code  =  $this->user_model->get_pass_code($code);
                $user_id = $post['id'];
                
                $q = $this->db->update(TBL_USERS, array('pass_code' => $pass_code), array('id' => $user_id));
                $results = $code;
                
            break;    
            
            case 'get' :
            case 'login' :
                
                $code = $post['code'];
                ///$pass_code  =  $this->user_model->get_pass_code($code);
                $q = $this->db->get_where(TBL_USERS, array( 'status !=' => 2 ));//array( 'pass_code' => $pass_code, 'status' => 1 ));
                $users = $q->result_array();
                
                $results = array();
                foreach( $users as $k => $user ) :
                  if( password_verify( $code, $user['pass_code'] ) ) :
                      $results = $user;
                      break;
                  endif;  
                endforeach;
                
            break;    
        
        endswitch;
        
        return $results;
    }
    
    public function get_payment_modes(){
        $this->db->select('pt.name, pt.slug, (CASE WHEN pay.pay_amount IS NULL THEN "0.00" ELSE SUM(pay.pay_amount) END) as paid_amount',FALSE);
        $this->db->join(TBL_PAY . ' as pay','pay.pay_type_id = pt.id AND pay.status = 1 AND ( pay.created_on >= "' . date('Y-m-d 00:00:00') .'" AND pay.created_on <= "' . date('Y-m-d H:i:s') .'")' ,'left');
        $this->db->where('pt.status',1);
        $this->db->group_by('pt.id');
        return $this->db->get(TBL_PAY_TYPE . ' as pt')->result();
    }
    
    public function get_total_sale_amount(){
        $this->db->select('(CASE WHEN pay_amount IS NULL THEN "0.00" ELSE SUM(pay_amount) END) as total_amount',FALSE);
        $this->db->where('status',1);
        $this->db->where('created_on >=',date('Y-m-d 00:00:00'));
        $this->db->where('created_on <=',date('Y-m-d H:i:s'));
        return $this->db->get(TBL_PAY)->row();
    }
    
    public function open_balance_exists() {
        $this->db->where('date',date('Y-m-d'));
        $getData = $this->db->get(TBL_OPEN_BALANCE);
        if($getData->num_rows() > 0)
            return 1;
        else 
            return 0;
    }
    
    public function get_opening_balance() {
        $this->db->where('date',date('Y-m-d'));
        return $this->db->get(TBL_OPEN_BALANCE)->row()->amount;
    }
    
    public function add_open_balance($data) {
        $this->db->insert(TBL_OPEN_BALANCE,$data);
        return $this->db->insert_id();
    }
    
    public function check_unique_details($val,$field) {
        if($field == 'Contact No'){
            $this->db->where('bd_contact_no',$val);
        } else if($field == 'Email') {
            $this->db->where('bd_email',$val);
        } else {
            $this->db->where('bd_nric_no',$val);
        }
        $getData = $this->db->get(TBL_BD);
        if($getData->num_rows() > 0)
            return 1;
         
        else
            return 0;
        
    }
    
}