8detect
7/3/2013 - 11:08 PM
SQLClosureTree
SQLClosureTree
<?php
namespace PHN\Model;
class SQLClosureTree{
public $db;
public $tableMain;//ex "acl_role";
public $tableMainId;
public $tableMainCode;
public $tablePath;//ex "acl_role_treepath";
/**
*
*/
function __construct($cfg)
{
$this->db = $cfg['db'];
$this->tableMain = $cfg['tableMain'];
$this->tableMainId = $cfg['tableMainId']; //field id ex : node_id
$this->tableMainCode = $cfg['tableMainCode']; //field code ex: node_code
$this->tableMainName = $cfg['tableMainName']; //field name ex: node_name
$this->tablePath = $cfg['tablePath'];//closure table
//default table
$this->db->table($this->tableMain);
}
/**
* return node as hash
* @param int/string $node_id
* @return null/array
*/
public function getNode($node_id)
{
//neu node_id = null tuc la lay root ( parent_id = 0 )
if (!empty($node_id))
{
$this->db->table($this->tableMain);
if (ctype_digit($node_id))
$node= $this->db->getOne(array('where'=>array($this->tableMainId .' = ? '=>$node_id)));
else
$node= $this->db->getOne(array('where'=>array($this->tableMainCode .' = ? '=>$node_id)));
}
else return null;
return $node;
}
/**
* return node_id
* @param int/string $node_id
* @return null/int
*/
public function getNodeId($node_id)
{
//neu node_id = null tuc la lay root ( parent_id = 0 )
if (!empty($node_id))
{
$this->db->table($this->tableMain);
if (ctype_digit($node_id))
$node= $this->db->getVal(array('select'=>$this->tableMainId,'where'=>array($this->tableMainId .' = ? '=>$node_id)));
else
$node= $this->db->getVal(array('select'=>$this->tableMainId,'where'=>array($this->tableMainCode .' = ? '=>$node_id)));
}
else return null;
return $node;
}
/**
* return array of sorted childern
* @param int/string $parent_id
* @param int $level , level =0 means all children
* @return null/array
*/
public function getChildren($parent_id,$level=0)
{
if (!ctype_digit($parent_id)) $parent_id = $this->getNodeId($parent_id);
if (!$parent_id) return null;
if ($level) $LEVEL = "AND p.length <= $level ";
else $LEVEL='';
$nodes=$this->db->getAll("
SELECT m.*,p.length
FROM {$this->tableMain} as m,{$this->tablePath} as p
WHERE p.ancestor = $parent_id AND m.{$this->tableMainId} = p.descendant $LEVEL;
");
//preprocessor
for ($i=0; $i < count($nodes);$i++)
{
}
return $nodes;
}
public function getPath($node_id)
{
if (!ctype_digit($node_id)) $node_id = $this->getNodeId($node_id);
if (!$node_id) return null;
$nodes=$this->db->getAll("
SELECT m.*
FROM {$this->tableMain} as m,{$this->tablePath} as p
WHERE p.descendant =$node_id AND m.{$this->tableMainId} = p.ancestor ;
");
//preprocessor
for ($i=0; $i < count($nodes);$i++)
{
}
return $nodes;
}
public function getTreeSorted($node_id)
{
if (!ctype_digit($node_id)) $node_id = $this->getNodeId($node_id);
if (!$node_id) return null;
return $this->db->getAll("
SELECT a.descendant as {$this->tableMainId},a.length,t.{$this->tableMainCode},t.{$this->tableMainName},GROUP_CONCAT(n.sort ORDER BY n.sort separator ';') AS path
FROM {$this->tablePath} d
JOIN {$this->tablePath} a ON (a.descendant = d.descendant)
JOIN {$this->tableMain} n ON (n.{$this->tableMainId} = a.ancestor)
JOIN {$this->tableMain} t ON (t.{$this->tableMainId} =a.descendant)
WHERE d.ancestor = $node_id
GROUP BY d.descendant ORDER BY path
");
}
public function insertNode($code,$name)
{
$this->db->table($this->tableMain);
$id= $this->db->insert(array($this->tableMainCode=>$code,$this->tableMainName=>$name));
if ($id) $this->db->update(array('sort'=>$id),$this->tableMainId.'='.$id);
return $id;
}
public function append($node,$parent_id=null)
{
if (!$parent_id) {
$node_id = $this->insertNode($node['code'], $node['name']);
if (!$node_id) return null;
$this->db->query("
INSERT INTO {$this->tablePath} (ancestor, descendant)
VALUES ($node_id, $node_id);
");
}
else {
if (!ctype_digit($parent_id)) $parent_id = $this->getNodeId($parent_id);
$node_id = $this->insertNode($node['code'], $node['name']);
if (!$node_id) return null;
if ($parent_id) {
$this->db->query("
INSERT INTO {$this->tablePath} (ancestor, descendant,length)
SELECT p.ancestor, $node_id,p.length+1
FROM {$this->tablePath} AS p
WHERE p.descendant = $parent_id
UNION ALL
SELECT $node_id, $node_id,0;
");
}
}
return $node_id;
}
public function replaceParent($node_id,$parent_id)
{
if (!ctype_digit($node_id)) $node_id=$this->getNodeId ($node_id);
if (!ctype_digit($parent_id)) $parent_id=$this->getNodeId ($parent_id);
if (!$node_id || !$parent_id) return;
//detach the node from old parent
$this->db->query("
DELETE a FROM {$this->tablePath} AS a
JOIN {$this->tablePath} AS d ON a.descendant = d.descendant
LEFT JOIN {$this->tablePath} AS x
ON x.ancestor = d.ancestor AND x.descendant = a.ancestor
WHERE d.ancestor = $node_id AND x.ancestor IS NULL;");
//attach the node to new parent
$this->db->query("
INSERT INTO {$this->tablePath} (ancestor, descendant, length)
SELECT supertree.ancestor, subtree.descendant,
supertree.length+subtree.length+1
FROM {$this->tablePath} AS supertree JOIN {$this->tablePath} AS subtree
WHERE subtree.ancestor = $node_id
AND supertree.descendant = $parent_id;
");
}
public function delete($node_id,$tree=true)
{
if (!ctype_digit($node_id)) $node_id=$this->getNodeId ($node_id);
if (!$node_id) return;
if (!$tree) {
$this->db->query("
DELETE FROM {$this->tableMain}
WHERE {$this->tableMainId} = $node_id ");
}
else {
$ids=$this->db->getArray("SELECT descendant
FROM {$this->tablePath}
WHERE ancestor = $node_id");
if ($ids) $ids = implode(',',$ids);
$this->db->query("
DELETE FROM {$this->tableMain}
WHERE {$this->tableMainId} IN ($ids) ");
}
}
//node co dang array[key=>val]
public function insertAfter($node,$sibling)
{
}
//cho truoc tree co dang adjency node ( parent_id )
//rebuild thanh dang preorder traversal lft rgt
//rebuild tu goc : ->rebuild(null,1);
//rebuild tu node nao do : ->rebuild(node,node['cat_id'])
public function rebuild($node=null,$lft=1)
{
//neu node == null thi lay toan bo cay tinh tu root
if (!is_object($node) && !is_array($node) ) $node = $this->getNode($node);
$rgt = $lft+1;
//var_dump($node);
$children=$this->db->queryAndFetchAll("
SELECT * FROM {$this->table}
WHERE parent_id = {$node[$this->pki_name]}
ORDER BY {$this->pki_name}
");
//echo $rgt;
for($i=0;$i<count($children);$i++)
{
$rgt = $this->rebuild($children[$i],$rgt);
}
$this->db->query("
UPDATE {$this->table}
SET lft = $lft , rgt = $rgt
WHERE {$this->pki_name} = {$node[$this->pki_name]}
");
return $rgt+1;
}
//fields = danh sach cac field se cap nhat vao table (detect tu XML attribute )
//duyet xml -> table
//fuc vu cho xml2DB
private function _traverse(&$node,$lft=1,$parent_id=0,$level=0)
{
$rgt = $lft+1;
$a=(array)$node->attributes();
$INPUT=array_merge($a['@attributes'],array('parent_id'=>$parent_id,'lft'=>$lft,'level'=>$level));
$this->db->insert($this->tableCols,$INPUT);
$parent_id=$this->db->lastInsertId();
$children=&$node->children();
//echo $rgt;
$level++;
for($i=0;$i<count($children);$i++)
{
$rgt = $this->_traverse($children[$i],$rgt,$parent_id,$level);
}
//update rgt
$INPUT=array("rgt"=>$rgt);
$this->db->update($INPUT,"{$this->pki_name} = $parent_id");
return ++$rgt;
}
/*
* $cols danh sach ten cac column trong table tuong u voi attribute trong XML
* $path=true thi xml se la duong dan thay vi xml str
*/
public function xml2DB($root,$xml,$path=false)
{
$this->tableCols=$this->db->getArray("DESCRIBE ".$this->table);
$xml = new SimpleXMLElement($xml, LIBXML_NOBLANKS,$path);
$this->_traverse($xml->$root);
}
//fields = danh sach cac field se chuyen thanh attribute trong xml tag
//duyet table -> xml
public function db2XML($node=null,$FILTER=null)
{
$nodes=&$this->getTree($node);
$xml[]="<CATS>";
for($i=0;$i<count($nodes);$i++)
{
//attributes
$attr="";
foreach ($nodes[$i] as $key => $value) {
if (!empty($FILTER) && !in_array($key, $FILTER)) continue;
$attr .= "$key=\"$value\" ";
}
//kiem tra xem node truoc do co level nho hon node hien tai ko
//neu co thi phai ket thuc = </CAT>
if ($i-1>0 && $nodes[$i-1]['level']>$nodes[$i]['level'] )
{
$level_before = $nodes[$i-1]['level'];
$level_current = $nodes[$i]['level'];
for($c=$level_before;$c>$level_current;$c--)
$xml[] = str_repeat("\t",$c-1)."</CAT>";
}
if ($nodes[$i]['children'] >0) $close_tag =">"; else $close_tag = "/>";
$xml[]= str_repeat("\t",$nodes[$i]['level']) . "<CAT $attr".$close_tag ;
//reach end of tree
//do close all open tags
if ($i == count($nodes)-1 && $nodes[$i]['level'] !=0)
{
$level_lft = $nodes[$i]['level'];
for($c=$level_lft;$c>0;$c--)
$xml[] = str_repeat("\t",$c-1)."</CAT>";
}
}
$xml[]="</CATS>";
return implode("\n",$xml);
}
//draw tree for debugging
public function dumpTree($node=null)
{
$tr= &$this->getTree($node);
CORE::header('utf8');
echo "<pre>";
for($i=0;$i<count($tr);$i++)
{
if ($tr[$i]['parent_id'] == 0) $name = "<b><font color=green>{$tr[$i][$this->name]}</b></font>";
elseif($tr[$i]['width'] >1) $name = "<b>{$tr[$i][$this->name]}</b>";
else $name = "<i>{$tr[$i][$this->name]}</i>";
echo str_repeat('-',$tr[$i]['level'] ). $name . ' #'.$tr[$i][$this->pki_name]. '<br>';
}
}
public function dumpPath($node=null)
{
$p= &$this->getPath($node);
CORE::header('utf8');
echo "<pre>";
for($i=0;$i<count($p);$i++)
{
if ($p[$i]['parent_id'] == 0) $name = "<b><font color=green>{$p[$i][$this->name]}</b></font>";
else $name = "{$p[$i][$this->name]}";
echo $name . ' #'.$p[$i][$this->pki_name] .'::';
}
}
}