se/SE/se-lib/Data_Tree_Source.php

<?php

Lib::loadClass('Data_Source');

/**
 * API:
 *   get($field_name) - source field value
 *   get_item($id) - get item by ID
 */
class Data_Tree_Source extends Data_Source {

	protected $_parentIdField = '';
	protected $_nameField = '';
	protected $_sortField = '';

	public function isValidTree() {
		if (!empty($this->_parentIdField) && array_key_exists($this->_parentIdField, $this->_cols)
				&& !empty($this->_nameField) && array_key_exists($this->_nameField, $this->_cols)) {
			return true;
		}
		return false;
	}

	public function getParentIdField() {
		return $this->_parentIdField;
	}

	public function setParentIdField($parentIdField) {
		if (array_key_exists($parentIdField, $this->_cols)) {
			$this->_parentIdField = $parentIdField;
			return true;
		}
		return false;
	}

	public function setSortField($sortFld) {
		if (array_key_exists($sortFld, $this->_cols)) {
			$this->_sortField = $sortFld;
			return true;
		}
		return false;
	}

	public function hasSortField() {
		return !empty($this->_sortField);
	}

	public function getNameField() {
		return $this->_nameField;
	}

	public function setNameField($nameField) {
		if (array_key_exists($nameField, $this->_cols)) {
			$this->_nameField = $nameField;
			return true;
		}
		return false;
	}

	private function _parseSqlWhere($params) {
		$sql_where = '';

		$sql_where_and = array();
		foreach ($params as $k => $v) {
			if ($k == '_rawSql') {
				foreach ($v as $rawSql) {
					$sql_where_and[] = $rawSql;
				}
			}
			else if (strlen($k) > 3 && substr($k, 0, 2) == 'f_') {
				$sql_where_and[] = "t.`" . substr($k, 2) . "` like '" . DB::_($v) . "'";
			}
			else if (strlen($k) > 4 && substr($k, 0, 3) == 'sf_') {
				$sqlFltr = $this->_parseSpecialFilter(substr($k, 3), $v);
				if (!empty($sqlFltr)) {
					$sql_where_and[] = $sqlFltr;
				}
			}
		}
		if (!empty($sql_where_and)) {
			$sql_where = implode(" and ", $sql_where_and);
		}

		if (!$sql_where) $sql_where = "1=1";
		return $sql_where;
	}

	public function getTreeNodes($parentID, $params = array()) {
		$DBG = ('1' == V::get('DBG_DS', '', $_REQUEST));
		$nodes = array();

		if (!$this->isValidTree()) return $nodes;

		$params['_rawSql'][] = "t.`{$this->_parentIdField}` = {$parentID}";

		//$sql_limit = V::get('limit', $this->_default_sql_limit, $params, 'int');
		//$sql_offset = V::get('limitstart', 0, $params, 'int');
		$sql_order_by = V::get('order_by', '', $params);
		if ($sql_order_by) {
			$sql_order_dir = V::get('order_dir', '', $params);

			// prevent from sorting by special columns
			if (!array_key_exists($sql_order_by, $this->_cols)) {
				$sql_order_by = null;
				$sql_order_dir = null;
			}
		}
		if ($sql_order_by) {
			$sql_order_by = "order by t.`{$sql_order_by}`";
			if ($sql_order_dir) {
				$sql_order_by = "{$sql_order_by} {$sql_order_dir}";
			}
		}

		$sql_cols = $this->_get_sql_cols();
		$sql_where = $this->_parseSqlWhere($params);
		$sql = "select {$sql_cols}
			from {$this->_tbl} as t
			where {$sql_where}
			{$sql_order_by}
		";
		//	limit {$sql_limit} offset {$sql_offset}
		if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			$node = new stdClass();
			$node->id = $r->ID;
			$node->parent_id = (int)$r->{$this->_parentIdField};
			$node->type = 'folder';
			$node->data = $r;
			$node->name = $r->{$this->_nameField};
			$nodes[$r->ID] = $node;
		}
		return $nodes;
	}

	public function getTreeNode($id, $params = array()) {
		$DBG = ('1' == V::get('DBG_DS', '', $_REQUEST));

		if (!$this->isValidTree()) return null;

		$params['_rawSql'][] = "t.`ID`={$id}";

		$sql_cols = $this->_get_sql_cols();
		$sql_where = $this->_parseSqlWhere($params);
		$sql = "select {$sql_cols}
			from {$this->_tbl} as t
			where {$sql_where}
			limit 1
		";
		if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$node = new stdClass();
			$node->id = $r->ID;
			$node->parent_id = (int)$r->{$this->_parentIdField};
			$node->type = 'folder';
			$node->data = $r;
			$node->name = $r->{$this->_nameField};
			return $node;
		}
		return null;
	}

	private function _isOnPathRec($id, $nodeId, $limit = 10) {
		//trigger_error('TODO: _isOnPathRec('.$id.', '.$nodeId.', '.$limit.')', E_USER_NOTICE);
		$sql = "select t.`ID`, t.`{$this->_parentIdField}`
			from `{$this->_tbl}` as t
			where t.`ID`={$nodeId}
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$p_id = (int)$r->{$this->_parentIdField};
			if ($p_id > 0) {
				if ($p_id == $id) {
					//trigger_error('TODO: return true '.$id.' == '.$p_id.'', E_USER_NOTICE);
					return true;
				}

				if ($limit--) {
					return $this->_isOnPathRec($id, $p_id, $limit);
				}
			}
		}
		return false;
	}

	/**
	 * Check if $id is on path between root to $nodeId
	 */
	public function isOnPath($id, $nodeId) {
		//trigger_error('TODO: isOnPath('.$id.', '.$nodeId.')', E_USER_NOTICE);
		return $this->_isOnPathRec($id, $nodeId);
	}

	/**
	 * Try to move $id under $p_id
	 * 
	 * $p_id cant be under $id - check path
	 */
	public function moveTreeNode($id, $p_id) {
		$DBG = ('1' == V::get('DBG_DS', '', $_REQUEST));
		if (!$this->isValidTree()) return false;

		if ($p_id == $id) return false;
		$node = $this->_db->get_by_id($this->_tbl, $id);
		if (!$node) return false;
		if ($p_id == (int)$node->P_ID) return false;

		if ($this->isOnPath($id, $p_id)) return false;

		$sqlObj = new stdClass();
		$sqlObj->ID = $id;
		$sqlObj->{$this->_parentIdField} = $p_id;
		$affected = $this->_db->UPDATE_OBJ($this->_tbl, $sqlObj);
		if ($this->_db->has_errors()) {
			return false;
		}
		return ($affected > 0);
	}

	/**
	 * Change order - put $id before $p_id
	 */
	public function moveTreeNodeSort($id, $destId, $destDir) {
		if ($id < 0 || $destId < 0 || $id == $destId) return false;
		if (!$this->_sortField) return false;
		if (!$this->isValidTree()) return false;
		$DBG = ('1' == V::get('DBG_DS', '', $_REQUEST));

		$node = $this->_db->get_by_id($this->_tbl, $id);
		$destItem = $this->_db->get_by_id($this->_tbl, $destId);
		if (!$node || !$destItem) return false;
		if ($node->{$this->_parentIdField} < 0 || $node->{$this->_parentIdField} != $destItem->{$this->_parentIdField}) return false;

		$rows = array();
		// $sql->order_by = "order by t.`SORT_PRIO` asc, t.`ID` asc";
		$sql = "select t.`ID`, t.`{$this->_parentIdField}`, t.`{$this->_sortField}`
			from `{$this->_tbl}` as t
			where t.`{$this->_parentIdField}`='{$node->{$this->_parentIdField}}'
			order by t.`{$this->_sortField}`
		";
		if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">sql (dir:'.$destDir.') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($sql);echo'</pre>';}
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			if ($r->ID == $node->ID) continue;
			$rows[$r->ID] = $r->{$this->_sortField};
		}
		if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">rows (dir:'.$destDir.') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($rows);echo'</pre>';}
		$foundDestId = false;
		$prevSortPrio = 0;
		$changeArr = array();
		foreach ($rows as $id => $sortPrio) {
			if (!$foundDestId && $id == $destId) {
				$foundDestId = true;

				if ($destDir == 'before') {
					$prevSortPrio = $sortPrio;
					$changeArr[$node->ID] = $prevSortPrio;
					$prevSortPrio++;
					$changeArr[$id] = $prevSortPrio;
				}
				else if ($destDir == 'after') {
					$prevSortPrio = $sortPrio;
					$prevSortPrio++;
					$changeArr[$node->ID] = $prevSortPrio;
				}
			}
			else if ($foundDestId) {
				$prevSortPrio++;
				if ($prevSortPrio > $sortPrio) {
					$changeArr[$id] = $prevSortPrio;
				} else {
					$prevSortPrio = $sortPrio;
				}
			}
		}
		if($DBG){echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">changeArr (dir:'.$destDir.') (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($changeArr);echo'</pre>';}

		if (!empty($changeArr)) {
			foreach ($changeArr as $id => $sortPrio) {
				$sqlObj = new stdClass();
				$sqlObj->ID = $id;
				$sqlObj->{$this->_sortField} = $sortPrio;
				$affected = $this->_db->UPDATE_OBJ($this->_tbl, $sqlObj);
			}
		}
		return true;
	}

	public function moveTreeNodeSortBefore($id, $beforeId) {
		return $this->moveTreeNodeSort($id, $beforeId, 'after');
	}

	/**
	 * Change order - put $id after $p_id
	 */
	public function moveTreeNodeSortAfter($id, $afterId) {
		return $this->moveTreeNodeSort($id, $afterId, 'before');
	}

}