<?php
require_once('class/DB/S_db2.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 $table       = NULL;
    var $tableName   = NULL;
    var $tableNameDB = NULL;
    var $itemName    = NULL;
    var $itemsName   = NULL;
    var $id          = NULL;
    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 $fileFields = 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;
	}

    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 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;
	}
	
	function initDateFromForm($form, $data)
	{
		$elements = $form->getElements();
		if (!is_array($elements) || empty($elements)) {
			return $files;
		}
		foreach ($elements as $element) {
			if ($element instanceof HTML_QuickForm_calendar) {
				$elementName = $element->getName();
				$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($form)
    {	
		$files = $this->initFilesFromForm($form);
		$data = $form->exportValues();
		$data = $this->initDateFromForm($form, $data);
		$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 insert($data = null, $files = null)
	{
		if (!is_null($data)) {
			$this->initFromArray($data, $files);
		}
        $this->_initTable();
        $data = $this->_data;
		unset($data['id']);
		$fields = $this->getSimpleFieldList();
		if (isset($fields['sorter'])) {
			$data['sorter'] = $this->getSorter();
		}
        $this->id = $this->table->insert($data);
		$this->handleFiles();
        return $this->id;
	}

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

	function delete()
	{
		$this->_files = array();
		if (is_array($this->fileFields) && !empty($this->fileFields)) {
			foreach ($this->fileFields as $elementName) {
				$this->_files[$elementName] = array('del' => 1);
			}
		}
        $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 getFromDB($id, $field = false)
    {
    	$id = intval($id);
    	$sql = "SELECT {$this->tableName}.*
    			FROM {$this->tableNameDB} AS {$this->tableName}
    			WHERE {$this->tableName}.id = $id";
    	$res = $this->db->queryRow($sql);
		$this->arrayParseTag2Url(&$res);
    	return $this->getSubElem($res, $field);
    }

    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)(isset($opt['addonWhere'])? $opt['addonWhere']: '').' ';
		$where .= ' '.$this->_filterSql();
		return $where;
    }

    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);
		$res = 	$this->db->query($sql);
        $this->isDBError($res);
		return $res;
    }

	function getList4Grid($opt = array() )
	{
		$start = 0; $ipp = 1024 * 1024 * 1024; // default values
		extract($opt);
        // Фікси глюків скелетону з GROUP BY і LIIMIT (EXPERIMENTAL)
		// if ('' == $this->_getGroup4Grid($opt)){
			$this->db->setLimit($ipp, $start);
			$opt['SQL_CALC_FOUND_ROWS'] = 'SQL_CALC_FOUND_ROWS';
			$res = $this->_getRes4Grid($this->_getWhat4Grid($opt), $opt);
			if ($this->isDBError($res)) {
				$all = array();
			} else {
				$all = $res->fetchAll();
				$res->free();
			}

			$num_rows = $this->db->queryOne('SELECT FOUND_ROWS()');
			if ($this->isDBError($num_rows)) {
			    $num_rows = 0;
			}
			//unset($opt['order_by']);
			//$num_rows_res = $this->_getRes4Grid('COUNT(*)', $opt);
			//if ($this->isDBError($num_rows_res)) {
			//    $num_rows = 0;
			//} else {
			//    $num_rows = $num_rows_res->fetchOne();
			//    $num_rows_res->free();
			//}
		// } else {
			// $opt['SQL_CALC_FOUND_ROWS'] = '';
			// $res = $this->_getRes4Grid($this->_getWhat4Grid($opt), $opt);
			// $all = array();
			// $num_rows = 0;
			// if (!$this->isDBError($res)) {
				// $res->seek($start);
		        // while (($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) && count($all) < $ipp) {
		        	// $all[] = $row;
		        // }
				// $num_rows = $res->numRows();
				// $res->free();
			// }
		// }
		$this->listParseUrl($all);
		$list = array(
			'num_rows'  => $num_rows,
			'num_pages' => 1 + floor(($num_rows - 1) / $ipp),
			'list'      => $all,
		);
		return $list;
	}

	function _getGroup4Grid()
	{
		return '';
	}

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

	function _getJoin4Grid()
	{
		return '';
	}

	function _getWhat4Grid($opt = array())
	{
		return "{$this->tableName}.*";
	}

	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->getUserInfo());
			} 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 = null, $field = 'sorter', $table_name_db = '')
	{
		if (null === $where) {
			$where = $this->_getWhere4Sorter();
		}
		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} as {$this->tableName} " . $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} as {$this->tableName} " . $where;
			$sorter = $this->db->queryOne($sql);
			$sorter = intval($sorter);
		} else {
			$sorter = ($sorter > 0 ? ++$sorter : $sorter);
		}

		return $sorter;
	}

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

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

	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);
		if ($this->isDBError($res)) {
			return array();
		}
		return $res;
	}

	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);
		if ($this->isDBError($res)) {
			return false;
		}
		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), 'id = '.$this->db->quote($id));
		}
		return true;
	}
	
	 function move($direction = 'up')
    {
        $max_sorter = $this->getSorter();
        $max_sorter = ($max_sorter > 0 ? ($max_sorter - 1) : 0);
        $sorter = $this->db->queryOne("SELECT sorter FROM {$this->tableNameDB} WHERE id = " . $this->id);
        if (($sorter == 0 && $direction == 'up') || ($sorter == $max_sorter && $direction == 'down')) {
            return true;
        }
        
        $order_by = 'ORDER BY sorter';
        if ($direction == 'down') {
            $order_by .= ' DESC';
        }
        $sql = "
			SELECT id, sorter 
            FROM {$this->tableNameDB} as {$this->tableName}
			".$this->_getWhere4Sorter()."
			{$order_by}
        ";
        $items = $this->db->queryAll($sql);
        $items_cnt = count($items);
        for ($i = 0; $i < $items_cnt - 1; $i++) {
            if ($items[$i + 1]['id'] == $this->id) {
                $sorter_tmp = $items[$i]['sorter'];
				$items[$i]['sorter'] = $items[$i + 1]['sorter'];
				$items[$i + 1]['sorter'] = $sorter_tmp;
                break;
            }
        }
		$items = php_multisort($items, array(array('key'=>'sorter', 'sort'=>'asc', 'type'=>'numeric')));
		$this->_initTable();
		$i =0 ;
		foreach($items as $row) {
			$this->table->update(array('sorter'=>intval($i++)), '`id` = '.intval($row['id']));
		}
        return true;
    }
	
	function _getWhere4Sorter()
	{
		return 'WHERE 1 ';
	}
	
    function isUnique($field, $name)
    {
        $sql = 'SELECT COUNT(*) FROM `' . $this->tableNameDB . '` '
             . 'WHERE `' . $field . '` = ' . $this->db->quote($name) . ' ';
        if ($this->id){
            $sql .=  ' AND `id` != ' . $this->db->quote($this->id, 'integer');
        }
        return !(bool)$this->db->queryOne($sql) ;
    }
    
    protected function _getDependencyFromDb($dictionaryTable, $fields, $dependedTable, $referenceField, $linkField, $id)
    {
        if (is_string($fields)) {
            $fields = array($fields);
        }
        $sql = 'SELECT `d`.`' . implode('`, `d`.`', $fields) . '` '
             . 'FROM `' . $dictionaryTable . '` AS `d` '
             . 'JOIN `' . $dependedTable . '` AS `t` '
             . 'ON  `t`.`' . $referenceField . '` = ' . intval($id) . ' '
             . 'AND `t`.`' . $linkField . '` = `d`.`id` ';
        if (count($fields) > 1) {
            $result = $this->db->queryAll($sql, 0, null, true);
        } else {
            $result = $this->db->queryCol($sql);
        }
        $this->isDBError($result);
        return $result;
    }
}