<?php

/**
 * @throws Qs_Db_Exception|Qs_Db_Obj_Exception
 */

class Qs_Db_Obj implements Qs_DataObj_Interface
{
    use Qs_ConfigTrait;

    const MSG_INVALID_RECORD_ID = 'Invalid Record Id.';
    const MSG_INVALID_OPTION    = 'Invalid Option Name.';
    const MSG_HAS_DEPENDENCIES  = 'This %itemName% is linked with %foreignKeyLocks%(s) and can\'t be removed. Please remove the connections first.';

    const TAG_BASE_URL       = '{BASE_URL}';
    const TAG_BASE_URL_HTTP  = '{BASE_URL_HTTP}';
    const TAG_BASE_URL_HTTPS = '{BASE_URL_HTTPS}';
    const TAG_CURRENT_PAGE   = '{CURRENT_PAGE}';

    const RAW_FIELD_PREFIX = 'raw';

    const BACKEND_PREFIX = 'admin';

    /** @var Zend_Db_Adapter_Pdo_Mysql */
    protected $_db;

    protected $_tableAlias;
    protected $_tableShortAlias;
    protected $_tableName;

    /** @var string */
    protected $_resourceName;

    /** @var Zend_Db_Select */
    protected $_select;

    protected $_selectOptions = array();
    protected $_tables = array();
    protected $_primaryKey = null;
    protected $_data = null;
    protected $_language;
    protected $_urlParseFields = array();
    protected $_fromColumns = array('*');

    /**
     * Масив полів для який необхідно підготувани "сирі" дані. Формати
     * 1) array('rawData' => 'dirtyData') // дані візьмуться з поля 'dirtyData', обробляться і запишуться в поле 'rawData'
     * 2) array('dirtyData') // дані візьмуться з поля 'dirtyData', обробляться і запишуться в поле 'rawDirtyData'
     * @var array
     */
    protected $_rawDataFields = array();

    /**
     * допустимі формати:
     * 1) array('field-1', 'field-2', ...)
     * 2) array('field-1', 'subForm-1' => array('field-1', 'field-2', ...), ...)
     * @var array
     */
    protected $_nullableFields = array();

    protected $_fileFields = array();
    protected $_dependedFields = array();
    protected $_options = array();
    protected $_filter = array();
    protected $_filterFields = array();
    protected $_filterExcludeFields = array('query', '__idItem');
    protected $_filterAllowedEmptyFields = array();
    protected $_hasFilter = true;
    protected $_insertPlacement = 'PREPEND';
    protected $_linkInsertPlacement;
    protected $_reorderKeyColumn = 'id';
    protected $_reorderTitleColumn = 'title';
    protected $_reorderIsGroupedItems = false;
    protected $_reorderGroupKeyColumn = null;
    protected $_reorderGroupTitleColumn = null;
    protected $_reorderGroupSorterColumn = null;
    protected $_errors = array();
    protected $_htmlPlaceholdersFields = array();
    protected $_htmlPlaceholdersData = array();

    /**
     * if true, before delete the all foreign reference to current table
     * (with on delete restrict, on delete no action) will be checked.
     * Please, set it at config
     * @var null|bool
     */
    protected $_checkForeignKeyDependencies = null;
    protected $_constraintDeleteRules = array('RESTRICT', 'NO ACTION');

    /**
     * array for appropriates DB_Obj classes and tableAliases - is used for getForeignKeysLockItems()
     * Structure:
     * array(
     *   'tableAlias' => 'ItemClass',
     *   'News'       => '\App_News_Obj',
     *   'Mail'       => '\App\Newsletters\Obj',
     *  ......
     * )
     *
     * @var array
     */
    protected $_tableModulesConversion = array();

    /**
     * Масив повідомлень для перевизнаяення дефолтного self::MSG_HAS_DEPENDENCIES
     * Формат (аліаси в множині, s - вкінці):
     * array(
     *     'tableAlias1' => 'message 1',
     *     'tableAlias2' => 'message 2',
     *      ...
     * )
     * @var array
     */
    protected $_foreignKeyLocksMessages = array();

    public function __construct($options = array())
    {
        Qs_Options::setConstructorOptions($this);
        $this->setOptions($options);
        $this->_db = Qs_Db::getInstance();
        if (null === $this->_tableAlias) {
            throw new Qs_Db_Exception('_tableAlias is not defined');
        }
        if (!$this->_tableName) {
            $this->_tableName = Qs_Db::getTableName($this->_tableAlias);
        }
        $this->_init();
    }

    protected function _init()
    {
    }

    function __call($name, $args)
    {
        $matches = array();
        if (preg_match('/^get(.*)4Select$/', $name, $matches)) {
            array_unshift($args, $matches[1]);
            return call_user_func_array(array($this, '_get4Select'), $args);
        }
        throw new Qs_Db_Exception('Call to undefined method: ' . get_class($this) . ':' . $name);
    }

    public function getReorderOptionsCount()
    {
        $select = $this->_getReorderSelect();
        $adapter = new Zend_Paginator_Adapter_DbSelect($select);
        return (int) $adapter->count();
    }

    protected function _quoteField($field, $tableAlias = null)
    {
        return $this->_db->quoteIdentifier($this->_getField($field, $tableAlias));
    }

    protected function _getTableAlias()
    {
        return $this->_tableShortAlias ?: $this->_tableAlias;
    }

    protected function _getField($field, $tableAlias = null)
    {
        if (null === $tableAlias) {
            $tableAlias = $this->_getTableAlias();
        }
        return $tableAlias . '.' . $field;
    }

    protected function _getSubPair($tableSubAlias, $alias = null)
    {
        if (null === $alias) {
            $alias = $this->_getTableAlias() . $tableSubAlias;
        }
        return $this->_getPair($this->_tableAlias . $tableSubAlias, $alias);
    }

    protected function _getPair($tableAlias = null, $alias = null)
    {
        if (null === $tableAlias) {
            $tableAlias = $this->_tableAlias;
            if (null === $alias) {
                $alias = $this->_getTableAlias();
            }
        }
        if (null === $alias) {
            $alias = $tableAlias;
        }
        return Qs_Db::getPair($tableAlias, $alias);
    }

    public function setOptions($options)
    {
        if ($options instanceof Zend_Config) {
            /** @var $options Zend_Config */
            $options  = $options->toArray();
        }
        if (isset($options['options'])) {
            unset($options['options']);
        }
        foreach ($options as $key => $value) {
            $method = 'set' . ucfirst($key);
            if (method_exists($this, $method)) {
                $this->$method($value);
            } else {
                $this->_options[$key] = $value;
            }
        }
        return $this;
    }

    public function setLanguage($language)
    {
        $this->_language = $language;
        return $this;
    }

    public function getLanguage()
    {
        if (null == $this->_language) {
            $this->_language = Qs_Constant::get('CURRENT_LANGUAGE');
        }
        return $this->_language;
    }

    public function isDefaultLanguage()
    {
        return $this->_language === Qs_Constant::get('DEFAULT_LANGUAGE');
    }

    public function setSelect(Zend_Db_Select $select)
    {
        $this->_select = $select;
        return $this;
    }

    protected function _getSelect()
    {
        if (null == $this->_select) {
            $this->_select = $this->_db->select();
        }
        return $this->_select;
    }

    protected function _getSorter($insertPlacement = null, $tableAlias = null)
    {
        $insertPlacement = (null === $insertPlacement) ? $this->_insertPlacement : $insertPlacement;
        $tableAlias = (null === $tableAlias) ? $this->_tableAlias : $tableAlias;

        if ('PREPEND' == $insertPlacement) {
            $function = 'MIN';
            $diff = -1;
        } else if ('APPEND' == $insertPlacement) {
            $function = 'MAX';
            $diff = 1;
        } else {
            throw new Qs_Db_Exception('Unknown insert placement: ' . $insertPlacement);
        }

        $select = $this->_db->select();
        $select->from($this->_getPair($tableAlias), "{$function}(`{$tableAlias}`.`sorter`)");
        $this->_where4Sorter($select);
        $select->limit(1);

        return (null === ($sorter = (int) $this->_db->fetchOne($select))) ? 0 : ($sorter + $diff);
    }

    /**
     * @param null $alias
     * @return Qs_Db_Table
     */
    protected function _getTable($alias = null)
    {
        if (null === $alias) {
            $alias = $this->_tableAlias;
        }

        if (!isset($this->_tables[$alias])) {
            $newTableName = Qs_Db::getTableName($alias);
            $this->_tables[$alias] = new Qs_Db_Table(array('name' => $newTableName, 'db' => $this->_db));
        }
        return $this->_tables[$alias];
    }

    protected function _getSubTable($suffix)
    {
        return $this->_getTable($this->_tableAlias . $suffix);
    }

    protected function _getTableName($alias = null)
    {
        if (null === $alias) {
            $alias = $this->_tableAlias;
        }
        return Qs_Db::getTableName($alias);
    }

    protected function _getQuotedTableName($alias = null)
    {
        return $this->_db->quoteIdentifier($this->_getTableName($alias));
    }

    protected function _setFromColumns(array $fromColumns)
    {
        $this->_fromColumns = $fromColumns;
        return $this;
    }

    protected function _getFromColumns()
    {
        return $this->_fromColumns;
    }

    public function setFilterFields(array $fields)
    {
        $this->_filterFields = $fields;
        return $this;
    }

    public function getFilterFields()
    {
        return $this->_filterFields;
    }

    protected function _where4Sorter(Zend_Db_Select $select)
    {
        return $this;
    }

    protected function _getWherePrimaryKey($alias = null)
    {
        return $this->_getTable()->prepareWhere(
            (array)$this->_primaryKey,
            (null === $alias) ? $this->_getTableAlias() : $alias
        );
    }

    public function initData()
    {
        $this->_data = $this->_getFromDb($this->getPrimaryKey());
        return $this;
    }

    public function initFromForm(array $data)
    {
        $this->_arrayParseUrl2Tag($data);
        $this->_prepareNullableFields($data);
        $this->_prepareRawData($data);
        $this->_data = $data;
        return $this;
    }

    protected function _prepareNullableFields(array &$data, $fields = null)
    {
        if (null === $fields) {
            $fields = $this->_nullableFields;
        }

        foreach ($fields as $name => $value) {
            if (is_array($value)) {
                if (array_key_exists($name, $data)) {
                    $this->_prepareNullableFields($data[$name], $value);
                }
            } else {
                $name = $value;
                if (empty($data[$name])) {
                    $data[$name] = null;
                }
            }
        }

        return $this;
    }

    public static function pregBaseUrl2Tag($content, $url)
    {
        $secure = Qs_SiteMap::isSecurePage($url);
        if (true === $secure) {
            $newUrl = str_replace(array(BASE_URL_HTTP, BASE_URL_HTTPS), Qs_Db_Obj::TAG_BASE_URL_HTTPS, $url);
        } else if (false === $secure) {
            $newUrl = str_replace(array(BASE_URL_HTTP, BASE_URL_HTTPS), Qs_Db_Obj::TAG_BASE_URL_HTTP, $url);
        } else if (0 === strpos($url, 'https://')) {
            $newUrl = str_replace(BASE_URL_HTTPS, Qs_Db_Obj::TAG_BASE_URL_HTTPS, $url);
        } else {
            $newUrl = str_replace(BASE_URL_HTTP, Qs_Db_Obj::TAG_BASE_URL_HTTP, $url);
        }
        return str_replace($url, $newUrl, $content);
    }

    protected function _baseUrl2Tag($content)
    {
        // base url
        $content = preg_replace(
            '/\<(?:a|area)[^\>"\']+href\s*=\s*(?:"|\')(https?:\/\/[^"\']+)(?:"|\')(?:[^\>]+)?\>/uie',
            'Qs_Db_Obj::pregBaseUrl2Tag(stripslashes(\'$0\'), stripslashes(\'$1\'))',
            $content
        );
        $content = str_replace(BASE_URL, Qs_Db_Obj::TAG_BASE_URL, $content);

        // anchors
        $pattern = '/<a([^>]+)?href\\s*=\\s*([\'"])(#[\\w-]+)\\2/';
        $replacement = '<a$1href=$2' . Qs_Db_Obj::TAG_BASE_URL . '/' . Qs_Db_Obj::TAG_CURRENT_PAGE . '$3$2';
        $content = preg_replace($pattern, $replacement, $content);

        return $content;
    }

    protected static function _tag2BaseUrl($content)
    {
        $placeHolders = array();

        /**
         * якщо ми на адмін частині, то вважаю що відбувається підготовка даних для форми редагування,
         * тому привожу всі "anchors" до початкового стану
         */
        $currentPageFirstSlice = explode('/', CURRENT_PAGE);
        $currentPageFirstSlice = reset($currentPageFirstSlice);
        if (self::BACKEND_PREFIX == $currentPageFirstSlice) {
            $key = self::TAG_BASE_URL . '/' . self::TAG_CURRENT_PAGE . '#';
            $placeHolders[$key] = '#';
        }

        $placeHolders[self::TAG_BASE_URL] = BASE_URL;
        $placeHolders[self::TAG_BASE_URL_HTTP] = BASE_URL_HTTP;
        $placeHolders[self::TAG_BASE_URL_HTTPS] = BASE_URL_HTTPS;
        $placeHolders[self::TAG_CURRENT_PAGE] = CURRENT_PAGE;

        return str_replace(array_keys($placeHolders), array_values($placeHolders), $content);
    }

    protected function _arrayParseTag2Url(array &$array, $fields = null)
    {
        if (null === $fields) {
            $fields = $this->_urlParseFields;
        }
        if (empty($fields) || !is_array($array) || empty($array)) {
            return $this;
        }
        foreach ($fields as $field) {
            if (isset($array[$field])) {
                $array[$field] = $this->_tag2BaseUrl($array[$field]);
            }
        }
        return $this;
    }

    protected function _arrayParseUrl2Tag(&$array)
    {
        if (empty($this->_urlParseFields) || !is_array($array) || empty($array)) {
            return $this;
        }
        foreach ($this->_urlParseFields as $field) {
            if (isset($array[$field])) {
                $array[$field] = $this->_baseUrl2Tag($array[$field]);
            }
        }
        return $this;
    }

    /**
     * @param array $data
     * @return Qs_Db_Obj
     */
    protected function _prepareRawData(array &$data)
    {
        if (is_array($this->_rawDataFields) && $this->_rawDataFields) {
            foreach ($this->_rawDataFields as $rawDataField => $dirtyDataField) {
                if (!$rawDataField || is_numeric($rawDataField)) {
                    $rawDataField = static::RAW_FIELD_PREFIX . ucfirst($dirtyDataField);
                }
                $data[$rawDataField] = Qs_Text_Mark::prepareSearchText($data, $dirtyDataField);
            }
        }
        return $this;
    }

    protected function _prepareList(&$list)
    {
        foreach (array_keys($list) as $key) {
            $this->_prepareRow($list[$key]);
        }
        return $this;
    }

    protected function _prepareRow(array &$row)
    {
        $this->_arrayParseTag2Url($row);
        return $this;
    }

    public function insert(array $data = null)
    {
        if (null === $data) {
            $data = $this->_data;
        } else {
            $this->_data = $data;
        }
        unset($data['id']);
        $metaData = $this->_getTable()->getMetaData();
        if (isset($metaData['sorter'])) {
            $data['sorter'] = $this->_getSorter();
        }
        $this->_db->beginTransaction();
        $this->_clearErrors();
        try {
            $this->_primaryKey = $this->_getTable()->insert($data);
            $this->_insertDependency();
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_deleteFiles($data);
            $this->_db->rollBack();
            Qs_Debug::log($e->getMessage(), 3);
            $this->_addError($e->getMessage());
            return false;
        }
        $this->_handleFiles();
        return $this->_primaryKey;
    }

    protected function _insertDependency()
    {
        return $this;
    }

    public function update(array $data = null)
    {
        if (null === $data) {
            $data = $this->_data;
        } else {
            $this->_data = $data;
        }
        unset($data['id']);
        $this->_clearErrors();
        $this->_db->beginTransaction();
        try {
            $result = $this->_getTable()->updateByKey($data, $this->_primaryKey);
            $this->_updateDependency();
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_db->rollBack();
            Qs_Debug::log($e->getMessage(), 3);
            $this->_addError($e->getMessage());
            return false;
        }
        $this->_handleFiles();
        return $result;
    }

    protected function _updateDependency()
    {
        return $this;
    }

    protected function _updateIds($tableAlias, $referenceField, $fieldName, $fieldValues)
    {
        $this->_deleteIds($tableAlias, $referenceField);

        if (!$this->_primaryKey || !is_array($fieldValues) || empty($fieldValues)) {
            return false;
        }
        if (is_array($this->_primaryKey)) {
            throw new Qs_Db_Obj_Exception(__METHOD__ . ' does not support complex primary keys');
        }

        $fields = array($referenceField, $fieldName);

        $valueParts = array();
        foreach ($fieldValues as $referenceId) {
            $values = array($this->_primaryKey, $referenceId);
            $values = array_map(array($this->_db, 'quote'), $values);
            $valueParts[] = '(' . implode(', ', $values). ')';
        }

        $sql = 'INSERT INTO ' . $this->_getQuotedTableName($tableAlias)
            . ' (`' . implode('`, `', $fields) . '`) VALUES ' . PHP_EOL
            . implode(', ' . PHP_EOL, $valueParts) . ';';
        return $this->_db->query($sql)->rowCount();
    }

    protected function _deleteIds($tableAlias, $referenceField)
    {
        if (!$this->_primaryKey) {
            return false;
        }

        if (is_array($this->_primaryKey)) {
            throw new Qs_Db_Obj_Exception(__METHOD__ . ' does not support complex primary keys');
        }

        $sql = 'DELETE FROM ' . $this->_getQuotedTableName($tableAlias) . ' '
            . 'WHERE ' . $this->_db->quoteIdentifier($referenceField)
            . ' = ' . $this->_db->quote($this->_primaryKey, Qs_Db::INT_TYPE);
        return $this->_db->query($sql)->rowCount();
    }

    protected function _handleFiles()
    {
        $session = new Qs_Session_Namespace(CURRENT_PAGE);
        if (isset($session->files) && !empty($session->files)) {
            $adapter = new Qs_File_Transfer_Adapter_Db();
            foreach ($session->files as $file => $name) {
                $adapter->delete($file, $name);
            }
            unset($session->files);
        }
        return $this;
    }

    protected function removeFileFromSession($file)
    {
        $session = new Qs_Session_Namespace(CURRENT_PAGE);
        if (isset($session->files) && !empty($session->files)) {
            unset($session->files[$file]);
        }
        return $this;
    }

    public function updateOrder(array $order)
    {
        foreach ($order as $sorter => $id) {
            $this->_getTable()->updateByKey(array('sorter' => (int)$sorter), $id);
        }
        return $this;
    }

    public function getFileFields()
    {
        return $this->_fileFields;
    }

    public function setFileFields($fields)
    {
        $this->_fileFields = $fields;
    }

    protected function _deleteFiles($row = null)
    {
        $adapter = new Qs_File_Transfer_Adapter_Db();
        if (is_array($this->_fileFields) && !empty($this->_fileFields)) {
            if (null === $row) {
                if (false !== ($row = $this->_getTable()->findRow($this->getPrimaryKey()))) {
                    $row = $row->toArray();
                }
            }
            if (is_array($row) && !empty($row)) {
                foreach ($this->_fileFields as $name) {
                    if (isset($row[$name]) && !empty($row[$name])) {
                        $adapter->delete($row[$name], $name);
                    }
                }
            }
        }
        foreach ($this->_dependedFields as $file) {
            $adapter->deleteFromDefaultDestination($file);
        }
        return $this;
    }

    /**
     * @return int|bool Affected Rows
     */
    public function delete()
    {
        $this->_clearErrors();

        if (null === ($row = $this->getData())) {
            $this->_addError(static::MSG_INVALID_RECORD_ID);
            return false;
        }

        if ($this->getCheckForeignKeyDependencies()) {
            $foreignKeyLocks = $this->getForeignKeysLockItems();
            if ($foreignKeyLocks) {
                $this->_addError($this->_getForeignKeyLocksMessages($foreignKeyLocks));
                return false;
            }
        }

        $this->_beforeDelete();

        $this->_db->beginTransaction();
        try {
            $this->_deleteDependency();
            $result = $this->_getTable()->deleteByKey($this->_primaryKey);
            $this->_db->commit();
            $this->_afterDeleteSuccess();
        } catch (Exception $e) {
            $this->_db->rollBack();
            $this->_afterDeleteFail();
            Qs_Debug::log($e->getMessage(), 3);
            $this->_addError($e->getMessage());
            return false;
        }

        $this->_deleteFiles($row);

        return $result;
    }

    protected function _beforeDelete()
    {
        return $this;
    }

    protected function _afterDeleteSuccess()
    {
        return $this;
    }

    protected function _afterDeleteFail()
    {
        return $this;
    }

    protected function _deleteDependency()
    {
        return $this;
    }

    public function clearData()
    {
        $this->_data = null;
        return $this;
    }

    public function clearPrimaryKey()
    {
        $this->_primaryKey = null;
        return $this;
    }
    /**
     * @param bool $field
     * @param null $default
     * @return null|array
     */
    public function getData($field = null, $default = null)
    {
        if (null === $this->_data && $this->getPrimaryKey() && $data = $this->_getFromDb($this->getPrimaryKey())) {
            $this->_data = $data;
        }
        if (null === $this->_data) {
            return null;
        }
        return  Qs_Array::get($this->_data, $field, $default);
    }

    public function getObjectInfo()
    {
        $data = (array) $this->getData();
        if (array_key_exists('title', $data)) {
            $data['itemTitle'] = $data['title'];
        } elseif (array_key_exists('name', $data)) {
            $data['itemTitle'] = $data['name'];
        }
        return $data;
    }

    protected function _getFromDbColumns()
    {
        return array('*');
    }

    /**
     * @param $primaryKey
     * @return Zend_Db_Select
     */
    protected function _getFromDbSelect($primaryKey)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair(), $this->_getFromDbColumns());
        $select->where($this->_getTable()->prepareWhere((array)$primaryKey, $this->_getTableAlias()));
        return $select;
    }

    protected function _getFromDb($primaryKey, $field = null)
    {
        if (false === ($row = $this->_db->fetchRow($this->_getFromDbSelect($primaryKey)))) {
            return false;
        }
        if (null === $field) {
            $this->_addDependenciesFromDb($row);
        }
        $this->_prepareRow($row);
        return Qs_Array::get($row, $field);
    }

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

    protected function _getDependencySelect($dictionaryTable, $fields, $dependedTable, $linkField, array $key)
    {
        $select = $this->_db->select();
        $where = '';
        foreach ($key as $name => $value) {
            if (is_array($value)) {
                $where .= $this->_db->quoteInto("`t`.`{$name}` IN (?) ", $value) . ' ';
            } else {
                $where .= "`t`.`{$name}` = " . ((is_numeric($value))? intval($value) : $this->_db->quote($value)) . ' ';
            }
        }
        $where .= " AND `d`.`id` = `t`.`{$linkField}`";
        $select->from(array('d' => $dictionaryTable), $fields)
            ->join(array('t' => $dependedTable), $where, []);
        return $select;
    }

    /**
     * @deprecated use _getListDependencyMap
     * @param $dictionaryTable
     * @param $fields
     * @param $dependedTable
     * @param $linkField
     * @param array $key
     * @return array
     */
    protected function _getDependencyFromDb($dictionaryTable, $fields, $dependedTable, $linkField, array $key)
    {
        $select = $this->_getDependencySelect($dictionaryTable, $fields, $dependedTable, $linkField, $key);
        if (is_array($fields) && count($fields) > 1) {
            $list = $this->_db->fetchPairs($select);
        } else {
            $list = $this->_db->fetchCol($select);
        }
        return $list;
    }

    protected function _prepareListDependency(array &$list, $listKey, $dictionaryTableAlias, $columns, $dependedTableAlias,
                                              $dictionaryLinkField, array $key)
    {
        $map = $this->_getListDependencyMap($dictionaryTableAlias, $columns, $dependedTableAlias, $dictionaryLinkField,
                                            $key);
        foreach ($list as &$row) {
            $row[$listKey] = \Qs_Array::get($map, $row['id'], []);
        }
        return $this;
    }

    protected function _getListDependencyMap($dictionaryTableAlias, $columns, $dependedTableAlias, $dictionaryLinkField,
                                             array $key)
    {
        $select = $this->_getDependencySelect(
            $this->_getTableName($dictionaryTableAlias),
            $columns,
            $this->_getTableName($dependedTableAlias),
            $dictionaryLinkField,
            $key
        );
        if (1 == count($columns)) {
            return $this->_db->fetchCol($select);
        }
        return $this->_db->fetchAll($select, [], \Qs_Db::FETCH_GROUP | \Qs_Db::FETCH_COLUMN);
    }

    /**
     * @return Zend_Db_Select
     */
    public function getListSelect()
    {
        if (null === $this->_select) {
            $this->_select = $this->_db->select();
            $this->_select->from($this->_getPair(), $this->_getFromColumns());
            $this->_filter($this->_select);
            $this->_applySelectOptions($this->_select, $this->_selectOptions);
        }
        return $this->_select;
    }

    public function setSelectOptions(array $options = array())
    {
        $this->_selectOptions = $options;
        if (null !== $this->_select) {
            $this->_applySelectOptions($this->_select, $this->_selectOptions);
        }
        return $this;
    }

    protected function _applySelectOptions(Zend_Db_Select $select, array $options = array())
    {

        foreach ($options as $name => $value) {
            switch ($name) {
                case 'order':
                    $select->reset(Zend_Db_Select::ORDER)->order($value);
                    break;
                case 'limitPage':
                    $select->reset(Zend_Db_Select::LIMIT_COUNT)->reset(Zend_Db_Select::LIMIT_OFFSET);
                    call_user_func_array(array($select, 'limitPage'), $value);
                    break;
                default:
                    Qs_Debug::log('Unsupported option name: ' . $name);
                    break;
            }
        }
        return $this;
    }

    protected function _filter(Zend_Db_Select $select)
    {
        if (!$this->hasFilter()) {
            return $this;
        }
        $filterFields = $this->getFilterFields();
        if (!empty($filterFields) && array_key_exists('query', $this->_filter)) {
            Qs_Db_Filter::where($select, $filterFields, $this->_filter['query']);
        }
        $filter = $this->_getCleanedFilter($this->_filter);
        Qs_Db::filter($select, $filter, $this->_getTableAlias());
        return $this;
    }

    protected function _filterEnabled(Zend_Db_Select $select)
    {
        $select->where($this->_quoteField('enabled') . ' = ?', 'y');
        return $this;
    }

    public function getListStatement()
    {
        return $this->getListSelect()->query();
    }

    public function getListStatement4XmlSitemap($columns = array('id'))
    {
        return $this->getListSelect()->reset(Zend_Db_Select::COLUMNS)->columns($columns)->query();
    }

    public function getList()
    {
        $list = $this->getListStatement()->fetchAll();
        $this->_prepareList($list);
        return $list;
    }

    public function getPrimary()
    {
        return $this->_getTable()->getPrimary();
    }

    /**
     * @return int|array|null
     */
    public function getPrimaryKey()
    {
        return $this->_primaryKey;
    }

    public function getPrimaryKeyArray()
    {
        $key = $this->getPrimaryKey();
        if (null !== $key && !is_array($key)) {
            $primary = $this->getPrimary();
            if (count($primary) != 1) {
                throw new Qs_Db_Obj_Exception('Primary key is wrong');
            }
            $key = array(reset($primary) => $key);
        }
        return (array) $key;
    }

    public function getMetaData()
    {
        return $this->_getTable()->getMetaData();
    }

    public function hasPrimaryKey()
    {
        return (null != $this->_primaryKey);
    }

    public function setPrimaryKeyByAlias($alias, $primary = 'id')
    {
        $this->setPrimaryKey($this->_getTable()->searchBy(compact('alias'), $primary));
        return $this;
    }

    public function setPrimaryKey($_primaryKey)
    {
        if (empty($_primaryKey)) {
            return $this;
        }

        if (is_numeric($_primaryKey)) {
            $this->_primaryKey = intval($_primaryKey);
            return $this;
        } else if (is_string($_primaryKey)) {
            $this->_primaryKey = strval($_primaryKey);
            return $this;
        }

        foreach ($_primaryKey as $key => $value) {
            if (null === $value || '' === $value) {
                unset($_primaryKey[$key]);
            }
        }

        $metaData = $this->_getTable()->getMetaData();
        foreach ($_primaryKey as $name => &$value) {
            switch($metaData[$name]['DATA_TYPE']) {
                case 'int':
                    $value = intval($value);
                    break;
                case 'varchar':
                case 'char':
                    $value = strval($value);
                    break;
                default:
                    break;
            }
        }

        if (!empty($_primaryKey)) {
            if (count($_primaryKey) > 1) {
                $this->_primaryKey = $_primaryKey;
            } else {
                $this->_primaryKey = reset($_primaryKey);
            }
        }

        return $this;
    }

    protected function _encryptPass($password, $salt = '')
    {
        return md5($salt . $password);
    }

    protected function _get4Select($tableAlias, $columns = '*', $where = null, $order = null,
                                   $limit = null)
    {
        return $this->_getTable($tableAlias)->get4Select($columns, $where, $order, $limit);
    }

    public function changeEnumOption($name)
    {
        $this->_clearErrors();
        if (null === $this->getData()) {
            $this->_addError(static::MSG_INVALID_RECORD_ID);
            return false;
        }
        $meta = $this->getMetaData();
        if (!array_key_exists($name, $meta)) {
            $this->_addError(static::MSG_INVALID_OPTION);
            return false;
        }
        $field = $meta[$name];
        if (0 !== strncasecmp($field['DATA_TYPE'], 'enum', 4)) {
            $this->_addError(static::MSG_INVALID_OPTION);
            return false;
        }
        preg_match_all("/'([^']+)'/", $field['DATA_TYPE'], $matches);
        $values = $matches[1];
        $value = $this->getData($name);
        if (false !== ($index = array_search($value, $values)) && array_key_exists($index + 1, $values)) {
            $newValue = $values[$index + 1];
        } else {
            $newValue = (!empty($meta['DEFAULT'])) ? $meta['DEFAULT'] : $values[0];
        }
        $this->_getTable()->updateByKey(array($name => $newValue), $this->getPrimaryKey());
        return true;
    }

    /**
     * @param string | array $field
     * @param mixed $value
     * @return Qs_Db_Obj
     */
    public function setFilter($field, $value = null)
    {
        if (is_array($field)) {
            $this->_filter = $field;
        } else {
            $this->_filter[$field] = $value;
        }
        $this->_clearSelect();
        return $this;
    }

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

    protected function _clearSelect()
    {
        $this->_select = null;
        return $this;
    }

    /**
     * Remove from filter unused values
     * @param array $filter
     * @return array
     */
    protected function _getCleanedFilter($filter)
    {
        $fields = array_keys($filter);
        $fields = array_diff($fields, $this->_filterAllowedEmptyFields);
        foreach ($fields as $name) {
            if (is_scalar($filter[$name])) {
                if ('' == trim($filter[$name])) {
                    unset($filter[$name]);
                }
            } else {
                if (empty($filter[$name])) {
                    unset($filter[$name]);
                }
            }
        }
        foreach ($this->_filterExcludeFields as $name) {
            unset($filter[$name]);
        }
        return $filter;
    }

    public function getFilter($field = null)
    {
        if (null === $field) {
            return $this->_filter;
        }
        return Qs_Array::get($this->_filter, $field);
    }

    public function hasFilter()
    {
        return $this->_hasFilter;
    }

    public function disableFilter()
    {
        $this->_hasFilter = false;
        return $this;
    }

    public function enableFilter()
    {
        $this->_hasFilter = true;
        return $this;
    }

    public function setReorderTitleColumn($name)
    {
        $this->_reorderTitleColumn = $name;
        return $this;
    }

    public function getReorderTitleColumn()
    {
        return $this->_reorderTitleColumn;
    }

    public function setReorderKeyColumn($name)
    {
        $this->_reorderKeyColumn = $name;
        return $this;
    }

    public function getReorderKeyColumn()
    {
        return $this->_reorderKeyColumn;
    }

    protected function _getReorderSelect()
    {
        $select = clone $this->getListSelect();
        $this->_where4Sorter($select);
        if ($this->_reorderIsGroupedItems && $this->_reorderGroupSorterColumn) {
            $select->order($this->_reorderGroupSorterColumn);
        }
        $select->order($this->_getTableAlias() . '.sorter');
        return $select;
    }

    public function getReorderGroupKeyColumn()
    {
        return $this->_reorderGroupKeyColumn;
    }

    public function getReorderGroupTitleColumn()
    {
        $titleColumn = $this->_reorderGroupTitleColumn;

        if (empty($titleColumn) && $this->hasReorderGroupedItems()) {
            $keyColumn = $this->getReorderGroupKeyColumn();
            $keyColumnLength = strlen($keyColumn);

            if (substr($keyColumn, $keyColumnLength - 2) === 'Id') {
                $titleColumn = substr($keyColumn, 0, $keyColumnLength - 2);
            } else if (substr($keyColumn, 0, 2) === 'id') {
                $titleColumn = lcfirst(substr($keyColumn, 2, $keyColumnLength - 2));
            }
        }

        return $titleColumn;
    }

    public function hasReorderGroupedItems()
    {
        return $this->_reorderIsGroupedItems;
    }

    public function getReorderOptions($keyColumn = null, $titleColumn = null, $reorderGroupKeyColumn = null)
    {
        $options = array();

        $keyColumn = (null === $keyColumn) ? $this->getReorderKeyColumn() : $keyColumn;
        $titleColumn = (null === $titleColumn) ? $this->getReorderTitleColumn() : $titleColumn;

        $list = $this->_db->fetchAll($this->_getReorderSelect());

        $reorderGroupKeyColumn = (null === $reorderGroupKeyColumn)
                               ? $this->getReorderGroupKeyColumn()
                               : $reorderGroupKeyColumn;

        $reorderGroupTitleColumn = $this->getReorderGroupTitleColumn();

        foreach ($list as $row) {
            if ($this->hasReorderGroupedItems() && !empty($reorderGroupKeyColumn)) {
                $reorderGroupTitle = (array_key_exists($reorderGroupTitleColumn, $row))
                                   ? $row[$reorderGroupTitleColumn]
                                   : $row[$reorderGroupKeyColumn];
                $options[$reorderGroupTitle][$row[$keyColumn]] = $row[$titleColumn];
            } else {
                $options[$row[$keyColumn]] = $row[$titleColumn];
            }
        }

        if ($this->hasReorderGroupedItems() && 1 == count($options)) {
            $options = reset($options);
        }

        return $options;
    }

    protected function _clearErrors()
    {
        $this->_errors = array();
        return $this;
    }

    protected function _addError($message)
    {
        if (is_string($message)) {
            $message = array($message);
        }
        $this->_errors = array_merge($this->_errors, $message);
        return $this;
    }

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

    /**
     * Converts array of ids in two dimensional array for Qs_Db_Obj->_insertLinks()
     * @param string $linkIdField
     * @param array $linkedIds
     * @param string $itemIdField
     * @param int $itemId
     * @param array|null $linkData [OPTIONAL]
     * @return array|null
     */
    protected function _ids2Links($linkIdField, $linkedIds, $itemIdField, $itemId, array $linkData = null)
    {
        $links = array();
        if (is_array($linkedIds)) {
            foreach ($linkedIds as $id) {
                $row = array($itemIdField => $itemId, $linkIdField => $id);
                $links[] = (null === $linkData) ? $row : array_merge($linkData, $row);
            }
        }
        return (empty($links)) ? null : $links;
    }

    /**
     * @throws Qs_Db_Obj_Exception
     * @param string $tableAlias
     * @param array $list Multi dimensional array with data for each link (overrides $linkData)
     * @param array $linkData Data that should be added to each link
     * @return int Affected rows count
     */
    protected function _insertLinks($tableAlias, array $list = null, array $linkData = array())
    {
        if (empty($list)) {
            return 0;
        }

        $firstRow = reset($list);
        if (!is_array($list) || !is_array($firstRow)) {
            throw new Qs_Db_Obj_Exception('Param $list is in wrong format');
        }

        /** @var $table Qs_Db_Table */
        $table = $this->_getTable($tableAlias);
        $tableName = $this->_getTableName($tableAlias);
        $metaData = $table->info(Zend_Db_Table::METADATA);
        $linkData = (array) $linkData;

        // init date
        if (isset($metaData['added']) || isset($metaData['changed'])) {
            $linkData['added'] = $linkData['changed'] = date('Y-m-d H:i:s');
        }

        // init sorter
        $initSorter = $insertPlacement = null;
        if (isset($metaData['sorter']) && !isset($firstRow['sorter'])) {
            if (isset($linkData['sorter'])) {
                $initSorter = (int) $linkData['sorter'];
            } else {
                $insertPlacement = (null === $this->_linkInsertPlacement)
                    ? $this->_insertPlacement
                    : $this->_linkInsertPlacement;
                $initSorter = $this->_getSorter($insertPlacement, $tableAlias);
            }
            $linkData['sorter'] = $initSorter;
        }

        $fields = array_keys(array_intersect_key($metaData, array_merge($linkData, $firstRow)));
        $sqlParts = array();
        foreach ($list as $row) {
            $_row = array_merge($linkData, $row);
            if (!isset($row['sorter']) && null !== $initSorter) {
                $_row['sorter'] = ('APPEND' === $insertPlacement) ? $initSorter++ : $initSorter--;
            }
            $_row = Qs_Array::map($_row, $fields);
            $_row = Qs_Db::quoteRow($_row, $table);
            if ($fields !== array_keys($_row)) {
                throw new Qs_Db_Obj_Exception('Inconsistent columns count');
            }
            $sqlParts[] = '(' . implode(', ', $_row). ')';
        }
        $sql = 'INSERT INTO ' . $this->_db->quoteIdentifier($tableName)
            . ' (`' . implode('`, `', $fields) . '`) VALUES ' . PHP_EOL
            . implode(', ' . PHP_EOL, $sqlParts) . ';';
        return $this->_db->query($sql)->rowCount();
    }

    public function setResourceName($resourceName)
    {
        $this->_resourceName = $resourceName;
        return $this;
    }

    public function getResourceName()
    {
        if (null === $this->_resourceName) {
            if (null === ($this->_resourceName = $this->getConfig('resourceName'))) {
                throw new Qs_Db_Exception('Resource name is not defined');
            }
        }
        return $this->_resourceName;
    }

    public function getObjectId()
    {
        return $this->getPrimaryKey();
    }

    /**
     * Return paceholders with values for html blocks
     *
     * @return array
     */
    public function getPlaceholdersData()
    {
        return $this->_getHmlPlaceholdersData();
    }

    /**
     * Return placeholder fields for placeholders
     *
     * @return array
     */
    public function getPlaceholders()
    {
        $placeholders = array();

        foreach ($this->_getHtmlPlaceholdersFields() as $field => $value) {
            if (is_int($field)) {
                $field = $value;
                $value = ucfirst(preg_replace('/([a-z])([A-Z])/', '$1 $2', $value));
            }
            $field = '{' . $field . '}';
            $placeholders[$field] = $value;
        }

        return $placeholders;
    }

    /**
     * Return data for placeholder replace
     *
     * @return array
     */
    protected function _getHtmlPlaceholdersData()
    {
        return $this->_htmlPlaceholdersData;
    }

    /**
     * Set data for placeholred replace
     *
     * @param array $data
     * @return Qs_Db_Obj
     */
    protected function _setHtmlPlaceholdersData($data)
    {
        $this->_htmlPlaceholdersData = $data;
        return $this;
    }

    /**
     * Prepare html placeholder - array example: array('placeholder' => 'placeholder title', ....)
     *
     * @return array
     */
    protected function _getHmlPlaceholdersData()
    {
        $placeholdersData = array();
        $data = $this->_getHtmlPlaceholdersData();
        if (!empty($data)) {
            foreach ($this->_getHtmlPlaceholdersFields() as $key => $field) {
                $index = (ctype_digit($key) || is_int($key)) ? $field : $key;
                $placeholder = '{' . $index . '}';
                $placeholdersData[$placeholder] = Qs_Array::get($data, $index);
            }
        }
        return $placeholdersData;
    }

    protected function _setHtmlPlaceholdersFields(array $fields)
    {
        $this->_htmlPlaceholdersFields = $fields;
        return $this;
    }

    protected function _getHtmlPlaceholdersFields()
    {
        if (empty($this->_htmlPlaceholdersFields)) {
            $this->_htmlPlaceholdersFields = array_keys($this->_htmlPlaceholdersData);
        }
        return $this->_htmlPlaceholdersFields;
    }

    /**
     * @param  array $providerOptions provider options from site/App/Search/config.php
     * @return array
     */
    public function getSearchList(array $providerOptions)
    {
        $list = $this->getListStatement()->fetchAll();
        $this->_prepareSearchList($list, $providerOptions);
        return $list;
    }

    /**
     * @param array $list items list
     * @param array $providerOptions provider options from site/App/Search/config.php
     * @return Qs_Db_Obj
     */
    protected function _prepareSearchList(array &$list, array $providerOptions)
    {
        $this->_prepareList($list);

        if (!empty($list)) {

            $query = $this->_filter['query'];

            $sitemapUrl = '';
            if (($selector = Qs_Array::get($providerOptions, 'sitemapSelector'))) {
                 if (!is_array($selector)) {
                     $selector = array($selector);
                 } else {
                     $paramsCount = count($selector);
                     if ($paramsCount < 3) {
                         $selector = array_merge($selector, array_fill(0, (3 - $paramsCount), null));
                     }
                 }
                $selector[] = 'url';
                $sitemapUrl = call_user_func_array('Qs_SiteMap::findFirst', $selector);
            }

            foreach ($list as &$row) {
                $row['sitemapUrl'] = $sitemapUrl;

                $row['searchUrl'] = $this->_buildSearchUrl(Qs_Array::get($providerOptions, 'urlPattern'), $row);

                $titleField = Qs_Array::get($providerOptions, 'titleField', App\Search\View::DEFAULT_TITLE_FIELD);
                $row['title'] = Qs_Text_Mark::markSearchWords($row[$titleField], $query);

                $contentFields = $this->getFilterFields();
                $contentFields = Qs_Array::excludeValue($contentFields, $titleField);
                if (in_array('title', $contentFields)) {
                    $contentFields = Qs_Array::excludeValue($contentFields, 'title');
                }

                $content = Qs_Text_Mark::prepareSearchText($row, $contentFields, false);
                $row['content'] = Qs_Text_Mark::markSearchWords($content, $query);
            }
        }

        return $this;
    }

    /**
     * @param string|null $pattern
     * @param array $data
     * @return string
     */
    protected function _buildSearchUrl($pattern, array $data)
    {
        $placeholders = array_keys($data);
        array_walk($placeholders, function (&$value, $key) { $value = '{' . $value . '}'; });

        $values = array_values($data);
        array_walk($values, function (&$value, $key) { if (!is_scalar($value)) { $value = ''; } });

        return str_replace($placeholders, $values, $pattern);
    }

    protected function _getForeignKeyLocksMessages($foreignKeyLocks)
    {
        $replacements = array(
            '%itemName%' => $this->getConfig()->itemName,
            '%name%'     => $this->getData('name')
        );
        $placeholders = array_keys($replacements);

        $messages = array();
        if (!empty($this->_foreignKeyLocksMessages)) {
            foreach ($foreignKeyLocks as $index => $foreignKey) {
                if (array_key_exists($foreignKey, $this->_foreignKeyLocksMessages)) {
                    $message = $this->_foreignKeyLocksMessages[$foreignKey];
                    $messages[] = str_replace($placeholders, $replacements, $message);
                    unset($foreignKeyLocks[$index]);
                }
            }
        }

        if (!empty($foreignKeyLocks)) {
            $replacements['%foreignKeyLocks%'] = implode(', ', $foreignKeyLocks);
            $placeholders = array_keys($replacements);
            $messages[] = str_replace($placeholders, $replacements, static::MSG_HAS_DEPENDENCIES);
        }

        return $messages;
    }

    /**
     * @return bool|null
     */
    public function getCheckForeignKeyDependencies()
    {
        if (null == $this->_checkForeignKeyDependencies) {
            $this->_checkForeignKeyDependencies = (bool)$this->getConfig('checkForeignKeyDependencies');
        }
        return $this->_checkForeignKeyDependencies;
    }

    /**
     * ATTENTION! use ONLY for projects with SINGLE DB.
     *
     * method return array of items (table aliases or appropriates from $this->_tableModulesConversion)
     * which have foreign keys referenced to current $_tableAlias
     *
     * @return array
     */
    public function getForeignKeysLockItems()
    {
        return $this->_getLockItemsByRules($this->_getLockRulesForDeleteConstraints());
    }

    /**
     * structure of rules see at \Qs_Db_Obj::_getLockRulesForDeleteConstraints() returns.
     *
     * @param array $rules
     * @return array
     */
    protected function _getLockItemsByRules($rules)
    {
        if (!$rules) {
            return array();
        }
        $dependencies = array();
        $tablePrefix = \Qs_Db::getConfig()->database->tablePrefix;
        $this->_db->query('USE `' . \Qs_Db::getConfig()->database->params->dbname . '`');
        foreach ($rules as $rule) {
            $referenceFieldValue = $this->getData($rule['referencedColumn']);
            $sql = "
                SELECT '1'
                FROM `{$rule['tableName']}`
                WHERE `{$rule['tableName']}`.`{$rule['column']}` = " . $this->_db->quote($referenceFieldValue) . "
                LIMIT 1
            ";
            if ($this->_db->fetchOne($sql)) {
                $tableAlias = str_replace($tablePrefix, '', $rule['tableName']);
                $dependencies[] = $this->_getForeignKeyLockItemName($tableAlias);
            }
        }
        return $dependencies;
    }

    protected function _getForeignKeyLockItemName($tableAlias)
    {
        $itemsName = null;
        if (!empty($this->_tableModulesConversion[$tableAlias])) {
            /** @var string $className */
            $className = $this->_tableModulesConversion[$tableAlias];
            if (class_exists($className)) {
                /** @var \Qs_Db_Obj $itemObj */
                $itemObj = new $className();
                $itemsName = $itemObj->getConfig('itemsName');
            }
        }

        if (!$itemsName) {
            $itemsName = \Qs_Translate::getPlural($tableAlias, 2);
        }

        return $itemsName;
    }

    /**
     * method is used for getting tables and columns that have foreign key reference to current Db_Obj table
     *
     * method return arrays of array of tableName, column and referenceColumn.
     * The referenceColumn (a column from current Db_Obj table)
     * is the foreign key reference for `tableName`.`column`
     *
     * Structure of returns:
     * array(
     *  array(
     *      'tableName'       => 'Table1',
     *      'column'          => 'someId',
     *      'referenceColumn  => 'id',
     *  ),
     *  array(
     *      'tableName'       => 'Table2',
     *      'column'          => 'someId',
     *      'referenceColumn  => 'id',
     *  ),
     *  array(
     *      'tableName'       => 'Table3',
     *      'column'          => 'someFieldName',
     *      'referenceColumn  => 'fieldName',
     *  ),
     *   ....
     * )
     *
     *
     * @return array
     */
    protected function _getLockRulesForDeleteConstraints()
    {
        if (!$this->_constraintDeleteRules) {
            return array();
        }

        return $this->_db->fetchAll($this->_getColumnUsageInformationSql());
    }

    /**
     * method returns sql-string for getting tables and columns that have foreign key reference to current Db_Obj table
     *
     *
     * @return string
     */
    protected function _getColumnUsageInformationSql()
    {
        $dbName = \Qs_Db::getConfig()->database->params->dbname;
        return "
            SELECT `TABLE_NAME` as `tableName`,
                   `COLUMN_NAME` as `column`,
                   `REFERENCED_COLUMN_NAME` as `referencedColumn`
            FROM  `information_schema`.`KEY_COLUMN_USAGE`
            WHERE
                `TABLE_SCHEMA` =  " . $this->_db->quote($dbName) . "
                AND `CONSTRAINT_NAME` IN (" . $this->_getConstraintSubSql() . ");
            ";
    }

    /**
     * method returns sql-string for getting constraints names (foreign keys names)
     * from `information_schema`.`REFERENTIAL_CONSTRAINTS`
     * for current project db, where reference table is current Db_Obj table
     * with specified foreign key delete rules
     *
     * @return string
     */
    protected function _getConstraintSubSql()
    {
        $dbName = \Qs_Db::getConfig()->database->params->dbname;
        $quotedDbName = $this->_db->quote($dbName);
        $quotedTableName = $this->_db->quote($this->_getTableName());
        return "
            SELECT `CONSTRAINT_NAME`
            FROM `information_schema`.`REFERENTIAL_CONSTRAINTS` as `rc`
            WHERE
                `rc`.`CONSTRAINT_SCHEMA` =  " . $quotedDbName . "
                AND `rc`.`REFERENCED_TABLE_NAME` = " . $quotedTableName . "
                AND `rc`.`DELETE_RULE` IN (" . $this->_db->quote($this->_constraintDeleteRules) . ")
        ";
    }

}