Ubilling/docs/presets/MikroTik/system/database/mysql.drv

<?php if ( !defined('ENVIRONMENT') ) exit('Only STG can run script!' . "\n");

    class Database {
        
        // Database's vars:
        private $connected;
        private $identifier;
        private $cache = array();
        
        // Query counter:
        public $qc = 0;
        
        // Recived data from `ubilling.cls`:
        private $config;
        private $log;
        
        // Constructor:
        public function __construct($data) {
            // Put all recived data to specified vars:
            foreach ($data as $key => $value) {
                $this->$key = $value;
            }
            // Write log message, that class is loaded:
            $this->log->message(__CLASS__, "MySQL driver loaded", 'debug');
            // Connect to database:
            $this->connect();
        }

        private function connect() {
            if ( !empty($this->config) ) {
                $dbport = (empty($this->config['port'])) ? 3306 : $this->config['port'];

                if (extension_loaded('mysqli')) {
                    $this->log->message(__CLASS__, "Use extension: MySQLi", 'debug');
                    $this->identifier = new mysqli($this->config['hostname'], $this->config['username'], $this->config['password'], $this->config['database'], $dbport);
                    if ($this->identifier->connect_error) {
                        $this->log->message(__CLASS__, 'Connection error (' . $this->identifier->connect_errno . ') ' . $this->identifier->connect_error, 'error');
                    } else {
                        $this->connected = TRUE;
                        return $this->connected;
                    }
                } else {
                    $this->log->message(__CLASS__, "Use extension: MySQL", 'debug');
                    $this->identifier = mysql_connect($this->config['hostname'] . ':' . $dbport, $this->config['username'], $this->config['password']);
                    if ( $this->identifier ) {
                        if ( mysql_select_db($this->config['database'], $this->identifier) ) {
                            $this->connected = TRUE;
                            $this->log->message(__CLASS__, "Connection with database is established...", 'debug');
                        } else $this->log->message(__CLASS__, "Can't select database, name is incorrect!", 'error');
                        return $this->connected;
                    } else $this->log->message(__CLASS__, "Can't connect to database, authorization data wrong or server halted!", 'error');
                }
            } else $this->log->message(__CLASS__, "Can't connect to database, no configurations data!", 'error');
        }

        private function select($cols, $table, $where = array()) {
            $columns = "`" . str_replace(', ', '`, `', $cols) . "`";
            $query = "SELECT " . $columns . " FROM " . $table;
            if ( !empty($where) ) {
                $query .= " WHERE";
                foreach ($where as $key => $value) {
                    $query .= " ";
                    $query .= "`" . $key . "` = '" . $value . "'";
                }
            }
            $queryID = md5($query);
            if ( !array_key_exists($queryID, $this->cache) ) {
                $this->log->message(__CLASS__, "Query -> " . $query, 'debug');
                if (extension_loaded('mysqli')) {
                    $result = $this->identifier->query($query);
                    $this->qc++;
                    while ($row = mysqli_fetch_assoc($result)) {
                        $this->cache[$queryID][] = $row;
                    }
                } else {
                    $result = mysql_query($query, $this->identifier);
                    $this->qc++;
                    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                        $this->cache[$queryID][] = $row;
                    }
                }
            } else $this->log->message(__CLASS__, "Cashe -> " . $query, 'debug');
            return $this->cache[$queryID];
        }

        /* DATABASE DATA QUERIES << */
        
        public function get_user_rate() {
            $return = array('tx' => 0, 'rx' => 0);
            if ( $this->connected ) {
                $return = $this->get_user_reassigned_rate();
                if ( empty($return['tx']) && empty($return['rx'])) {
                    $tariff = $this->get_user_tariff();
                    if ( !empty($tariff) ) {
                        $return = $this->get_tariff_rate($tariff);
                    }
                }
                return $return;
            } else $this->log->message(__CLASS__, "Can't get user's rate from database, no connection!", 'error');
        }

        private function get_user_reassigned_rate() {
            $return = array('tx' => 0, 'rx' => 0, 'bt_tx' => 0, 'bt_rx' => 0, 'bt_tx_t' => 0, 'bt_rx_t' => 0);
            $result = $this->select('speed', 'userspeeds', array('login' => LOGIN));
            foreach ( $result as $data ) {
                if ( !empty($data['speed']) ) {
                    $return['tx'] = $data['speed'];
                    $return['rx'] = $data['speed'];
                    $this->log->message(__CLASS__, "User's reassigned rate - `" . $data['speed'] . "`", 'debug');
                } else return FALSE;
            }
            return $return;
        }

        private function get_tariff_rate($tariff) {
            $return = array('tx' => 0, 'rx' => 0, 'bt_tx' => 0, 'bt_rx' => 0, 'bt_tx_t' => 0, 'bt_rx_t' => 0);
            $result = $this->select('speedup, speeddown, burstupload, burstdownload, burstimetupload, bursttimedownload' , 'speeds', array('tariff' => $tariff));
            foreach ($result as $data) {
                if ( !empty($data['speedup']) ) {
                    $return['tx'] = $data['speedup'];
                    $this->log->message(__CLASS__, "User's tariff TX rate - `" . $data['speedup'] . "`", 'debug');
                } else $this->log->message(__CLASS__, "User's tariff has no TX rate limit!", "error");
                if ( !empty($data['speeddown']) ) {
                    $return['rx'] = $data['speeddown'];
                    $this->log->message(__CLASS__, "User's tariff RX rate - `" . $data['speeddown'] . "`", 'debug');
                } else $this->log->message(__CLASS__, "User's tariff has no RX rate limit!", 'error');
                if ( !empty($data['burstupload']) ) {
                    $return['bt_tx'] = $data['burstupload'];
                    $this->log->message(__CLASS__, "User's tariff Burst TX rate - `" . $data['burstupload'] . "`", 'debug');
                };
                if ( !empty($data['burstdownload']) ) {
                    $return['bt_rx'] = $data['burstdownload'];
                    $this->log->message(__CLASS__, "User's tariff Burst RX rate - `" . $data['burstdownload'] . "`", 'debug');
                };
                if ( !empty($data['burstimetupload']) ) {
                    $return['bt_tx_t'] = $data['burstimetupload'];
                    $this->log->message(__CLASS__, "User's tariff Burst Time TX rate - `" . $data['burstimetupload'] . "`", 'debug');
                };
                if ( !empty($data['bursttimedownload']) ) {
                    $return['bt_rx_t'] = $data['bursttimedownload'];
                    $this->log->message(__CLASS__, "User's tariff Burst Time RX rate - `" . $data['bursttimedownload'] . "`", 'debug');
                };
            }
            return $return;
        }

        public function get_user_tariff() {
            $return = NULL;
            $result = $this->select('Tariff', 'users', array('login' => LOGIN));
            foreach ( $result as $data ) {
                if ( !empty($data['Tariff']) AND $data['Tariff'] != '*_NO_TARIFF_*' ) {
                    $return = $data['Tariff'];
                    $this->log->message(__CLASS__, "User's tariff - `" . $data['Tariff'] . "`", 'debug');
                } else $this->log->message(__CLASS__, "User has no assigned tariff!", 'error');
            }
            return $return;
        }

        public function get_nas_data() {
            $return = array();
            if ( $this->connected ) {
                $netID = $this->get_network_id();
                $result = $this->select('nasip, nastype, options', 'nas', array('netid' => $netID));
                foreach ($result as $data) {
                    if ( !empty($data['nasip']) ) {
                        $return['ip'] = $data['nasip'];
                        $this->log->message(__CLASS__, "Data -> `nasip` = " . $data['nasip'], 'debug');
                    } else $this->log->message(__CLASS__, "Can't find IP address of NAS!", 'error');
                    if ( !empty($data['nastype']) ) {
                        switch ($data['nastype']) {
                            case 'mikrotik':
                                // Get NAS'es options (for MikroTik only):
                                $options = array();
                                if ( !empty($data['options']) ) {
                                    $options = unserialize(base64_decode($data['options']));
                                    foreach ($options as $option => $value) {
                                        $this->log->message(__CLASS__, "Option -> `" . $option . "` = '" . $value . "'", 'debug');
                                    }
                                } else $this->log->message(__CLASS__, "Can't find user's NAS options!", 'error');
                                $return['options'] = $options;
                            case 'rscriptd':
                            case 'local':
                            case 'other':
                                $return['type'] = $data['nastype'];
                                $this->log->message(__CLASS__, "Data -> `nastype` = " . $data['nastype'], 'debug');
                                break;
                        }
                    } else $this->log->message(__CLASS__, "Can't find user's NAS type!", 'error');
                }
            } else $this->log->message(__CLASS__, "Can't get NAS's data from database, no connection!", 'error');
            return $return;
        }

        public function get_network_id() {
            $return = NULL;
            switch ( ENVIRONMENT ) {
                case 'OnConnect':
                case 'OnDisconnect':
                    $result = $this->select('netid', 'nethosts', array('ip' => IP));
                    foreach ($result as $data) {
                        if ( !empty($data['netid']) ) {
                            $return = $data['netid'];
                        }
                    }
                    break;
                case 'OnChange':
                case 'OnUserDel':
                    $result = $this->select('id, desc', 'networks');
                    if ( !empty($result) ) {
                        foreach ($result as $network) {
                            list($netIP, $netCIDR) = explode('/', $network['desc']); 
                            if ( (ip2long(IP) & ~((1 << (32 - $netCIDR))-1)) == ip2long($netIP) ) {
                                $return = $network['id'];
                                break;
                            }
                        }
                    }
                    break;
            }
            if ( empty($result) ) $this->log->message(__CLASS__, "Can't find user's network id!", 'error');
            return $return;
        }
        
        public function get_user_ip() {
            $return = NULL;
            $result = $this->select('IP', 'users', array('login' => LOGIN));
            if ( !empty($result) ) {
                foreach ($result as $data) {
                    if ( !empty($data['IP']) ) {
                        $return = $data['IP'];
                        $this->log->message(__CLASS__, "Data -> `IP` is " . $data['IP'], 'debug');
                    }
                    break;
                }
            } else $this->log->message(__CLASS__, "Can't find user's IP", 'error');
            return $return;
        }

        public function get_user_mac() {
            if ( $this->connected ) {
                $return = NULL;
                $result = $this->select('mac', 'nethosts', array('ip' => IP));
                if ( !empty($result) ) {
                    foreach ($result as $data) {
                        if ( !empty($data['mac']) ) {
                            $return = $data['mac'];
                            $this->log->message(__CLASS__, "Data -> `mac` is " . $data['mac'], 'debug');
                        }
                        break;
                    }
                } else $this->log->message(__CLASS__, "Can't find user's MAC", 'error');
                return $return;
            } else $this->log->message(__CLASS__, "Can't get user's MAC from database, no connection!", 'error');
        }

        public function get_user_password() {
            $default = '';
            if ( $this->connected ) {
                $result = $this->select('Password', 'users', array('login' => LOGIN));
                return !empty($result) ? $result[0]['Password'] : $default;
            } else $this->log->message(__CLASS__, "Can't get user's password from database, no connection!", 'error');
        }
        
        /* >> DATABASE DATA QUERIES */
        
    }
?>