<?php
require_once('class/DB/S_db2.php');
require_once('Qs/Db.php');
require_once('Qs/Array.php');

define('DB_OBJ_DEL_METHOD_PHYSICAL', 1);
define('DB_OBJ_DEL_METHOD_LOGICAL',  2);

class DBObj
{
    var $delMethod   = DB_OBJ_DEL_METHOD_PHYSICAL;
    /**
     * @var DBTable
     */
    var $table       = NULL;
    var $_tables     = array();
    var $tableName   = NULL;
    var $tableNameDB = NULL;
    var $itemName    = NULL;
    var $id          = NULL;
    /**
     * @var S_db2
     */
    var $db;
    var $_data;
    var $_files = array();
    var $login_field = 'login';
    var $pass_field  = 'password';
    var $_filterFields = array();
    var $_filterQuery = null;
    var $_base_url_tag = '{BASE_URL}';
    var $_urlParseFields = array();

    var $_cacheTableName = 'SiteCache';
    var $_cacheUrlFormat = '';
    var $_cacheUrlFormatFields = array();
    var $_cacheTitleFields = array();
    var $_cacheFields = array();
    protected $_errors = array();
    protected $_filter = array();

    function DBObj($id = NULL)
    {
        if ( !is_null($id) ) {
            $this->id = intval($id);
        }
        $this->db = S_db2::getInstance();

        if (is_null($this->tableName)){
            $this->tableName = ucwords(get_class($this));
        }
        $tableName = 'tbl'.$this->tableName;
        $this->tableNameDB = $this->db->$tableName;

        if (is_null($this->itemName)){
            $this->itemName = ucwords(get_class($this));
        }
    }

    function __call($name, $args)
    {
        if (preg_match('/^get(.*)4Select$/', $name, $matches)) {
            array_unshift($args, $matches[1]);
            return call_user_func_array(array($this, 'get4Select'), $args);
        }
        if (preg_match('/^get(.*)ById$/', $name, $matches)) {
             array_unshift($args, $matches[1]);
            return call_user_func_array(array($this, 'getById'), $args);
        }
        $debugData = debug_backtrace();
        echo "<span style=\"color:red;\"><b>Fatal error</b>: Call to undefined method ".get_class($this).": $name() in <b>{$debugData[1]['file']}</b> on line <b>{$debugData[1]['line']}</b></span>";
        exit;
    }

    public function __get($name)
    {
        if (strncmp($name, 'table', 5) == 0) {
            $tableName = substr($name, 5);
            if ($tableName === false) {
                $tableName = null;
            }
            return $this->getTable($tableName);
        }
        //throw new Exception('Unknown property name: ' . $name);
        return null;
    }

    public function clearErrors()
    {
        $this->_errors = array();
        return $this;
    }

    public function addError($message)
    {
        $this->_errors[] = $message;
        return $this;
    }

    public function addErrors(array $messages)
    {
        foreach ($messages as $message) {
            $this->addError($message);
        }
        return $this;
    }

    public function  getErrors()
    {
        return $this->_errors;
    }

    /**
     * @param  $name
     * @return DBTable
     */
    public function getTable($name = null)
    {
        if (null === $name) {
            $name = $this->tableName;
        }
        if (!is_object($this->_tables[$name])) {
            require_once('class/DB/DBTable.php');
            $this->_tables[$name] = new DBTable($name);
        }
        return $this->_tables[$name];
    }

    function setFilter($query)
    {
        $this->_filterQuery = (string)$query;
    }

    function _filterSql()
    {
        require_once('class/DB/DBObj/Filter.php');
        return DBObj_Filter::perseQr($this->db, $this->_filterFields, $this->_filterQuery);
    }

    function addFilter(array $filter)
    {
        $this->_filter = array_merge($this->_filter, $filter);
        return $this;
    }

    function getFieldList()
    {
        $qr = "SHOW FIELDS FROM {$this->tableNameDB}";
        return $this->db->queryAll($qr);
    }

    function initFromArray($data, $files = null)
    {
        $this->_data = (array)$data;
        $this->_files = (array)$files;
        return true;
    }

    function initFilesFromForm($form)
    {
        $files = array();
        // Якщо список файлових полів задано в проперті fileFields тоді крутимо цикл по ній
        if (is_array($this->fileFields) && !empty($this->fileFields)) {
            foreach ($this->fileFields as $elementName) {
                if ($form->elementExists($elementName)) {
                    $files[$elementName] = $form->getElementValue($elementName);
                }
            }
            return $files;
        }
        // інакше шукаємо файлові поля в формі
        $elements = $form->getElements();
        if (!is_array($elements) || empty($elements)) {
            return $files;
        }
        foreach ($elements as $element) {
            if ($element instanceof HTML_QuickForm_file_db) {
                $elementName = $element->getName();
                $files[$elementName] = $form->getElementValue($elementName);
            }
        }
        return $files;
    }

    protected function _initDateFromForm(Form $form, &$data, $onlyElements = array())
    {
        $elements = $form->getElements();
        if (!is_array($elements) || empty($elements)) {
            return $data;
        }
        foreach ($elements as $element) {
            /** @var $element HTML_QuickForm_element  */
            if ($element instanceof HTML_QuickForm_calendar) {
                $elementName = $element->getName();
                if (!empty($onlyElements) && !in_array($elementName, $onlyElements)) {
                    continue;
                }
                $date = $element->exportValue();
                if (intval($date['Y']) && intval($date['M']) && intval($date['d'])) {
                    $data[$elementName] = sprintf('%04d-%02d-%02d', $date['Y'], $date['M'], $date['d']);
                } else {
                    $data[$elementName] = null;
                }
            }
        }
        return $data;
    }

    function initFromForm(&$frm)
    {
        $files = array();
        $data = $frm->exportValues();
        $this->arrayParseUrl2Tag($data);
        $this->initFromArray($data, $files);
        return true;
    }

    function _initFile()
    {
        if (!is_object($this->file)) {
            require_once('class/DB/ImageDB.php');
            $this->file = new ImageDB();
        }
        return true;
    }

    function handleFiles()
    {

        foreach ($this->_files as $name => $file){
            $this->handleFile($name, $file);
        }
        return true;
    }

    function handleFile($name, $file)
    {
        assert(' !is_null($this->id) ');
        $this->_initTable();
        $data = array();
        $this->_initFile();
        if ($file['del']){
            $this->file->delete( $this->getFromDB($this->id, $name) );
            $data[$name] = '';
        }else {
            $data[$name]  = $this->file->handleUpload($file,
                    $this->getFromDB($this->id, $name) );
        }
        $this->table->update($data, 'id = '. $this->db->quote($this->id, 'integer') );
    }

    function initFromDB()
    {
        $this->_data = $this->getFromDB($this->id);
        return true;
    }

    function getData($field = false)
    {
        return  $this->getSubElem($this->_data, $field);
    }

    function _initTable()
    {
        if (!is_object($this->table)) {
            require_once('class/DB/DBTable.php');
            $this->table = new DBTable($this->tableName);
        }
        return true;
    }

    function _getCacheURL()
    {
        $res = '';
        if ($this->_cacheUrlFormat != '') {
            $res = $this->_cacheUrlFormat;
            if (is_array($this->_cacheUrlFormatFields) && !empty($this->_cacheUrlFormatFields)) {
                $args = array();
                foreach ($this->_cacheUrlFormatFields as $field) {
                    switch ($field) {
                        case 'id':
                            $args[] = (int)$this->id;
                            break;
                        default:
                            $args[] = $this->_data[$field];

                    }
                }
                $res = vsprintf($res, $args);
            }
        }
        return $res;
    }

    function _getCacheData()
    {
        $res = '';
        if (is_array($this->_cacheFields) && !empty($this->_cacheFields)) {
            foreach ($this->_cacheFields as $key) {
                $keys = explode('[', $key);
                $key = preg_replace("/^{$keys[0]}/", "['{$keys[0]}']", $key);
                $value = '$this->_data' . $key;
                $res .= eval("return $value;") . ' ';
            }
            $res = trim($res);
        }
        return strip_tags($res);
    }

    function _getCacheTitle()
    {
        $res = '';
        if (is_array($this->_cacheTitleFields) && !empty($this->_cacheTitleFields)) {
            foreach ($this->_cacheTitleFields as $key) {
                $keys = explode('[', $key);
                $key = preg_replace("/^{$keys[0]}/", "['{$keys[0]}']", $key);
                $value = '$this->_data' . $key;
                $res .= eval("return $value;") . ' :: ';
            }
            $res = substr($res, 0, -4);
        }
        return $res;
    }

    function _cacheItem()
    {
        $title = $this->_getCacheTitle();
        $data = $this->_getCacheData();
        $url = $this->_getCacheURL();
        if (!empty($data) && !empty($title) && !empty($url)) {
            $data = array(
                'table_name = '. $this->db->quote($this->tableName),
                'table_id = ' . $this->db->quote($this->id),
                'url = ' . $this->db->quote($url),
                'title = ' . $this->db->quote($title),
                'data = ' . $this->db->quote($data),
            );
            $changed = $this->db->quote(date('Y-m-d H:i:s'));
            $tbl = "tbl{$this->_cacheTableName}";
            $sql = "insert into {$this->db->$tbl}
                    set " . implode(', ', $data) . ", added = {$changed}, changed = {$changed}
                    on duplicate key update " . implode(', ', $data) . ", changed = {$changed}
            ";
            $this->db->query($sql);
        }
        return true;
    }

    function _cacheDelete()
    {
        $tbl = "tbl{$this->_cacheTableName}";
        $sql = "delete from {$this->db->$tbl} where table_name = " . $this->db->quote($this->tableName) . " and table_id = " . $this->db->quote($this->id);
        return $this->db->query($sql);
    }

    function insert($data = null, $files = null)
    {
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }
        $this->_initTable();
        $this->_beforeInsert();
        $data = $this->_data;
        unset($data['id']);
        $fields = $this->getSimpleFieldList();
        if (isset($fields['sorter'])) {
            $data['sorter'] = $this->getSorter();
        }
        $this->id = $this->table->insert($data);
        $this->_insertDependency();
        $this->handleFiles();
        return $this->id;
    }

    function update($data = null, $files = null)
    {
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }
        $this->_initTable();
        $this->_beforeUpdate();
        $this->table->update($this->_data, 'id = '. $this->db->quote($this->id, 'integer') );
        $this->_updateDependency();
        $this->handleFiles();
        return true;
    }

    function _beforeInsert()
    {
    }

    function _beforeUpdate()
    {
    }

    function _insertDependency()
    {
    }

    function _updateDependency()
    {
    }

    function delete()
    {
        $this->_initTable();
        switch ($this->delMethod) {
            case DB_OBJ_DEL_METHOD_LOGICAL:
                $data = array('deleted' => 'y');
                $this->table->update($data, 'id = '. $this->db->quote($this->id, 'integer'));
                break;
            case DB_OBJ_DEL_METHOD_PHYSICAL:
                $this->handleFiles();
                $this->table->delete('id = '. $this->db->quote($this->id, 'integer') );
                break;
        }
        return true;
    }

    function baseUrl2Tag($content)
    {
        return str_replace(BASE_URL, $this->_base_url_tag, $content);
    }

    function tag2BaseUrl($content)
    {
        return str_replace($this->_base_url_tag, BASE_URL, $content);
    }

    function arrayParseTag2Url(&$arr)
    {
        if (empty($this->_urlParseFields) || !is_array($arr) || empty($arr)) return false;
        foreach ($this->_urlParseFields as $field) {
            if (!isset($arr[$field])) continue;
            $arr[$field] = $this->tag2BaseUrl($arr[$field]);
        }
    }

    function arrayParseUrl2Tag(&$arr)
    {
        if (empty($this->_urlParseFields) || !is_array($arr) || empty($arr)) return false;
        foreach ($this->_urlParseFields as $field) {
            if (!isset($arr[$field])) continue;
            $arr[$field] = $this->baseUrl2Tag($arr[$field]);
        }
    }

    function listParseUrl(&$list)
    {
        if (empty($this->_urlParseFields) || !is_array($list) || empty($list)) return false;
        foreach ($list as $k=>$arr) {
            $this->arrayParseTag2Url($list[$k]);
        }
    }

    function getFromDbSql($id)
    {
        $sql = 'SELECT `' . $this->tableName . '`.* '
             . 'FROM `' . $this->tableNameDB . '` AS `' . $this->tableName . '` '
             . 'WHERE `' . $this->tableName . '`.`id` = ' . $this->db->quote($id, 'integer') . ' ';
        return $sql;
    }

    function getFromDB($id, $field = false)
    {
        $sql = $this->getFromDbSql($id);
        $data = $this->db->queryRow($sql);
        $this->isDBError($data);
        if (false === $field && is_array($data)) {
            $this->_addDependenciesFromDb($data);
        }
        $this->arrayParseTag2Url(&$data);
        return Qs_Array::get($data, $field);
    }

    function _addDependenciesFromDb(&$data)
    {
        return $this;
    }

    function getSubElem($res, $field)
    {
        require_once('class/Arr.php');
        return Arr::getSubElem($res, $field);
    }

    function _getOrder4Grid($opt = array())
    {
        $order_by = trim((string)$opt['order_by']);
        if ('' != $order_by){
//    		$order_by = preg_replace('/([\w\-\+]+)( DESC)?/', '`$1`$2', $order_by);
            $order_by = " ORDER BY $order_by ";
        }
        return $order_by;
    }

    function _getWhere4Grid($opt = array())
    {
        if (DB_OBJ_DEL_METHOD_LOGICAL == $this->delMethod){
            $where = " WHERE {$this->tableName}.deleted = 'n' ";
        }else {
            $where = ' WHERE 1 ';
        }
        $where .= ' ' .(string)$opt['addonWhere'].' ';
        $where .= ' '.$this->_filterSql();
        return $where;
    }

    protected function _getLimit4Grid($ipp, $start = 0)
    {
        return " LIMIT {$start}, {$ipp}";
    }

    /**
     * @param $what
     * @param $opt
     * @return MDB2_BufferedResult_mysql
     */
    function _getRes4Grid($what, $opt)
    {
        $sql = 'SELECT ' . $opt['SQL_CALC_FOUND_ROWS'] .  ' '
             . $what . ' '
             . 'FROM  `' . $this->tableNameDB . '` AS `' . $this->tableName . '` '
             . $this->_getJoin4Grid($opt) . ' '
             . $this->_getWhere4Grid($opt) . ' '
             . $this->_getGroup4Grid($opt) . ' '
             . $this->_getHaving4Grid($opt) . ' '
             . $this->_getOrder4Grid($opt);
        if (null !== ($ipp = Qs_Array::get($opt, 'ipp'))) {
            $sql .= $this->_getLimit4Grid($ipp, Qs_Array::get($opt, 'start', 0));
        }
        $res = $this->db->query($sql);
        if (PEAR::isError($res)) {
            $GLOBALS['_DEBUG']['level'] = D_ECHO;
            dump($res->getUserInfo(), 'getList4Grid error');
        }
        return $res;
    }

    function getList4Grid($opt = array())
    {
        $all = $this->getRows($opt);
        $num_rows = $this->db->queryOne('SELECT FOUND_ROWS()');
        if ($this->isDBError($num_rows)) {
            $num_rows = 0;
        }
        $this->listParseUrl($all);
        $this->_prepareList($all);
        $list = array(
            'num_rows'  => $num_rows,
            'num_pages' => 1,
            'list'      => $all,
        );
        if (null !== ($ipp = Qs_Array::get($opt, 'ipp'))) {
            $list['num_pages'] += floor(($num_rows - 1) / $ipp);
        }
        $this->_prepareList4GridResult($list);
        return $list;
    }

    protected function _prepareList(&$list)
    {
        return $this;
    }

    protected function _prepareList4GridResult(&$listItem)
    {
        return $this;
    }

    function getRows($opt = array())
    {
        $opt['SQL_CALC_FOUND_ROWS'] = 'SQL_CALC_FOUND_ROWS';
        $res = $this->_getRes4Grid($this->_getWhat4Grid($opt), $opt);
        if ($this->isDBError($res)) {
            $list = array();
        } else {
            $list = $res->fetchAll();
            $res->free();
        }
        return $list;
    }

    function _getGroup4Grid()
    {
        return '';
    }

    function _getHaving4Grid($opt = array())
    {
        return '';
    }

    function _getJoin4Grid()
    {
        return '';
    }

    function _getFromColumns()
    {
        return array($this->tableName . '.*');
    }

    function _getWhat4Grid($opt = array())
    {
        $columns = $this->_getFromColumns();
        $parts = array();
        foreach ($columns as $field => $value) {
            if (is_numeric($field)) {
                $parts[] = $value;
            } else {
                $parts[] = $value . " AS `{$field}`";
            }
        }
        return implode(', ', $parts);
    }

    function _encryptPass($pas)
    {
        return md5($pas);
    }

    function _getWhere4DBLogin($user)
    {

        $sql =  " WHERE " . $this->login_field . " = " . $this->db->quote($user['login']). " AND
                " . $this->pass_field . " = " . $this->db->quote($this->_encryptPass($user['password'])).' ';
        return $sql;
    }

    function DBLogin($user)
    {
        $sql = "SELECT id FROM {$this->tableNameDB} ".$this->_getWhere4DBLogin($user);
        $id = $this->db->queryOne($sql);
        if (!MDB2::isError($id) && $id != 0) {
            $userOut = $this->getFromDB($id);
            $userOut[$this->pass_field] = $user[$this->pass_field];
        }else {
            $userOut = array();
        }

        return $userOut;
    }

    function isDBError($res)
    {
        if (MDB2::isError($res)) {
            if (defined('DEBUG') && DEBUG) {
                vdie($res);
            } else {
                sendDeveloperEmail($res->getUserInfo());
            }
            return true;
        }
        return false;
    }

    function getSimpleFieldList($table_name_db = '')
    {
        if ($table_name_db == '') {
            $table_name_db = $this->tableNameDB;
        }

        $sql = "SHOW FIELDS FROM {$table_name_db}";
        $list = $this->db->queryAll($sql);
        $ret = array();
        if (is_array($list) && !empty($list)) {
            foreach ($list as $k => $v) {
                $ret[$v['field']] = $v;
            }
        }
        return $ret;
    }

    function getSorter($where = '1', $field = 'sorter', $table_name_db = '')
    {
        if ($table_name_db == '') {
            $table_name_db = $this->tableNameDB;
        }

        $fields = $this->getSimpleFieldList($table_name_db);
        if (!isset($fields[$field])) {
            return false;
        }

        $sql = "select max({$field}) from {$table_name_db} where " . $where;
        $sorter = $this->db->queryOne($sql);
        if ($this->isDBError($sorter)) {
            $sorter = 0;
        }
        $sorter = (int)$sorter;
        if ($sorter == 0) {
            $sql = "select count(*) from {$table_name_db} where " . $where;
            $sorter = $this->db->queryOne($sql);
            $sorter = intval($sorter);
        } else {
            $sorter = ($sorter > 0 ? ++$sorter : $sorter);
        }

        return $sorter;
    }

    function updateIds($tableName, $fieldName, $list, $parentFieldName = 'id_parent')
    {
        $this->deleteIds($tableName, $parentFieldName);
        if (!$this->id || !is_array($list) || empty($list)) {
            return false;
        }
        $id = $this->db->quote($this->id, 'integer');
        $sql = "INSERT INTO {$tableName} (`{$parentFieldName}`, `{$fieldName}`) VALUES";
        foreach ($list as $k=>$v) {
            $sql .= "\n($id, ".$this->db->quote($v)."),";
        }
        $sql = rtrim($sql, ",").";";
        $res = $this->db->query($sql);
        $this->_sqlError($res);
    }

    function deleteIds($tableName, $parentFieldName = 'id_parent')
    {
        if (!$this->id) {
            return false;
        }
        $sql = "DELETE FROM {$tableName} WHERE `{$parentFieldName}` = ".$this->db->quote($this->id, 'integer');
        $res = $this->db->query($sql);
        $this->_sqlError($res);
    }

    function _sqlError(&$res)
    {
        if (PEAR::isError($res)) {
            if (defined('DEBUG') && DEBUG) {
                vdie($res->getUserInfo(), get_class($this).' error '.print_r(debug_backtrace(), true));
            } else {
                sendDeveloperEmail($res->getUserInfo());
            }
            return array();
        }
    }

    function get4Select($_tableName, $order_by = 'sorter', $where = null)
    {
        $tableName = 'tbl'.$_tableName;
        $tableName = $this->db->$tableName;
        $sql = "
            SELECT id, title
            FROM {$tableName}
            WHERE 1 ".(($where)?' AND ':'').$where."
            ORDER BY {$order_by}
        ";
        $res = $this->db->queryAll($sql, 0, null, true);
        $this->_sqlError($res);
        return $res;
    }

    function getDState4Select()
    {
        $sql = "
            SELECT short as id, short as title
            FROM {$this->db->tblDState}
            ORDER BY title
        ";
        return $this->db->queryAll($sql, 0, null, true);
    }

    function getById($_tableName, $id = null)
    {
        $tableName = 'tbl'.$_tableName;
        $tableName = $this->db->$tableName;
        $sql = "
            SELECT *
            FROM {$tableName}
            WHERE id = ".$this->db->quote($id, 'integer')."
        ";
        $res = $this->db->queryRow($sql);
        $this->_sqlError($res);
        return $res;
    }

    // ����� ENUM �������� �� ��������
    function changeOption($opt)
    {
        if (!strlen($opt)) return false;
        $opt = $this->db->quote($opt);
        $opt = substr($opt, 1, strlen($opt)-2);
        $field = $this->db->queryRow("SHOW COLUMNS FROM {$this->tableNameDB} LIKE '%{$opt}%'");
        if (!isset($field['field'])) return false;
        if (strncmp($field['type'], 'enum', 4)!=0) return false;
        eval("\$arrValues = ".str_replace('enum', 'array', $field['type']).';');
        $val = $this->db->queryOne("SELECT `$opt` FROM {$this->tableNameDB} WHERE id = ".$this->db->quote($this->id));
        $valPos = array_search($val, $arrValues);
        $val = ((isset($arrValues[$valPos+1])))?$arrValues[$valPos+1]:$arrValues[0];
        $this->db->query("UPDATE {$this->tableNameDB} SET `$opt` = ".$this->db->quote($val).' WHERE id='.$this->db->quote($this->id).' LIMIT 1');
    }

    function reorder($list)
    {
        $this->_initTable();
        foreach ((array)$list as $sorter => $id) {
            $this->table->update(array('sorter'=>$sorter + 1), 'id = '.$this->db->quote($id));
        }
        return true;
    }

    function isUnique($field, $value)
    {
        $sql = 'SELECT COUNT(*) FROM `' . $this->tableNameDB . '` '
             . 'WHERE `' . $field . '` = ' . $this->db->quote($value) . ' ';
        if ($this->id){
            $sql .=  ' AND `id` != ' . $this->db->quote($this->id, 'integer');
        }
        return !(bool)$this->db->queryOne($sql) ;
    }

    function quoteField($identifier, $alias = null)
    {
        if (null === $alias) {
            $alias = $this->tableName;
        }
        return '`' . $alias . '`.' . $this->db->quoteIdentifier($identifier);
    }

    protected function _getPair($fullAlias = null, $shortAlias = null)
    {
        if (null === $fullAlias) {
            $fullAlias = $this->tableName;
        }
        return Qs_Db::getPair($fullAlias, $shortAlias);
    }
}