Changeset 3730:5c45a5df9a59 for inc/core/class.dc.categories.php
- Timestamp:
- 03/08/18 17:58:39 (7 years ago)
- Branch:
- default
- File:
-
- 1 edited
-
inc/core/class.dc.categories.php (modified) (2 diffs)
Legend:
- Unmodified
- Added
- Removed
-
inc/core/class.dc.categories.php
r2707 r3730 10 10 # 11 11 # -- END LICENSE BLOCK ----------------------------------------- 12 if (!defined('DC_RC_PATH')) { return;}12 if (!defined('DC_RC_PATH')) {return;} 13 13 14 14 # nestedTree class is based on excellent work of Kuzma Feskov … … 19 19 class dcCategories extends nestedTree 20 20 { 21 protected $f_left= 'cat_lft';22 23 protected $f_id= 'cat_id';24 25 26 27 28 29 30 $this->core =&$core;31 $this->con =&$core->con;32 $this->blog_id= $core->blog->id;33 $this->table = $core->prefix.'category';34 $this->add_condition = array('blog_id' => "'".$this->con->escape($this->blog_id)."'");35 36 37 public function getChildren($start=0,$id=null,$sort='asc',$fields=array())38 39 $fields = array_merge(array('cat_title','cat_url','cat_desc'),$fields);40 return parent::getChildren($start,$id,$sort,$fields);41 42 43 public function getParents($id,$fields=array())44 45 $fields = array_merge(array('cat_title','cat_url','cat_desc'),$fields);46 return parent::getParents($id,$fields);47 48 49 public function getParent($id,$fields=array())50 51 $fields = array_merge(array('cat_title','cat_url','cat_desc'),$fields);52 return parent::getParent($id,$fields);53 21 protected $f_left = 'cat_lft'; 22 protected $f_right = 'cat_rgt'; 23 protected $f_id = 'cat_id'; 24 25 protected $core; 26 protected $blog_id; 27 28 public function __construct($core) 29 { 30 $this->core = &$core; 31 $this->con = &$core->con; 32 $this->blog_id = $core->blog->id; 33 $this->table = $core->prefix . 'category'; 34 $this->add_condition = array('blog_id' => "'" . $this->con->escape($this->blog_id) . "'"); 35 } 36 37 public function getChildren($start = 0, $id = null, $sort = 'asc', $fields = array()) 38 { 39 $fields = array_merge(array('cat_title', 'cat_url', 'cat_desc'), $fields); 40 return parent::getChildren($start, $id, $sort, $fields); 41 } 42 43 public function getParents($id, $fields = array()) 44 { 45 $fields = array_merge(array('cat_title', 'cat_url', 'cat_desc'), $fields); 46 return parent::getParents($id, $fields); 47 } 48 49 public function getParent($id, $fields = array()) 50 { 51 $fields = array_merge(array('cat_title', 'cat_url', 'cat_desc'), $fields); 52 return parent::getParent($id, $fields); 53 } 54 54 } 55 55 56 56 abstract class nestedTree 57 57 { 58 protected $con; 59 60 protected $table; 61 protected $f_left; 62 protected $f_right; 63 protected $f_id; 64 65 protected $add_condition = array(); 66 67 protected $parents; 68 69 public function __construct($con) 70 { 71 $this->con =& $con; 72 } 73 74 public function getChildren($start=0,$id=null,$sort='asc',$fields=array()) 75 { 76 $fields = count($fields) > 0 ? ', C2.'.implode(', C2.',$fields) : ''; 77 78 $sql = 'SELECT C2.'.$this->f_id.', C2.'.$this->f_left.', C2.'.$this->f_right.', COUNT(C1.'.$this->f_id.') AS level ' 79 . $fields.' ' 80 . 'FROM '.$this->table.' AS C1, '.$this->table.' AS C2 %s ' 81 . 'WHERE C2.'.$this->f_left.' BETWEEN C1.'.$this->f_left.' AND C1.'.$this->f_right.' ' 82 . ' %s ' 83 . $this->getCondition('AND','C2.') 84 . $this->getCondition('AND','C1.') 85 . 'GROUP BY C2.'.$this->f_id.', C2.'.$this->f_left.', C2.'.$this->f_right.' '.$fields.' ' 86 . ' %s ' 87 . 'ORDER BY C2.'.$this->f_left.' '.($sort == 'asc' ? 'ASC' : 'DESC').' '; 88 89 $from = $where = ''; 90 if ($start > 0) { 91 $from = ', '.$this->table.' AS C3'; 92 $where = 'AND C3.'.$this->f_id.' = '.(integer) $start.' AND C1.'.$this->f_left.' >= C3.'.$this->f_left.' AND C1.'.$this->f_right.' <= C3.'.$this->f_right; 93 $where .= $this->getCondition('AND','C3.'); 94 } 95 96 $having = ''; 97 if ($id !== null) { 98 $having = ' HAVING C2.'.$this->f_id.' = '.(integer) $id; 99 } 100 101 $sql = sprintf($sql,$from,$where,$having); 102 103 return $this->con->select($sql); 104 } 105 106 public function getParents($id,$fields=array()) 107 { 108 $fields = count($fields) > 0 ? ', C1.'.implode(', C1.',$fields) : ''; 109 110 return $this->con->select( 111 'SELECT C1.'.$this->f_id.' '.$fields.' ' 112 . 'FROM '.$this->table.' C1, '.$this->table.' C2 ' 113 . 'WHERE C2.'.$this->f_id.' = '.(integer) $id.' ' 114 . 'AND C1.'.$this->f_left.' < C2.'.$this->f_left.' ' 115 . 'AND C1.'.$this->f_right.' > C2.'.$this->f_right.' ' 116 . $this->getCondition('AND','C2.') 117 . $this->getCondition('AND','C1.') 118 . 'ORDER BY C1.'.$this->f_left.' ASC ' 119 ); 120 } 121 122 public function getParent($id,$fields=array()) 123 { 124 $fields = count($fields) > 0 ? ', C1.'.implode(', C1.',$fields) : ''; 125 126 return $this->con->select( 127 'SELECT C1.'.$this->f_id.' '.$fields.' ' 128 . 'FROM '.$this->table.' C1, '.$this->table.' C2 ' 129 . 'WHERE C2.'.$this->f_id.' = '.(integer) $id.' ' 130 . 'AND C1.'.$this->f_left.' < C2.'.$this->f_left.' ' 131 . 'AND C1.'.$this->f_right.' > C2.'.$this->f_right.' ' 132 . $this->getCondition('AND','C2.') 133 . $this->getCondition('AND','C1.') 134 . 'ORDER BY C1.'.$this->f_left.' DESC ' 135 . $this->con->limit(1) 136 ); 137 } 138 139 /* ------------------------------------------------ 140 * Tree manipulations 141 * ---------------------------------------------- */ 142 public function addNode($data,$target=0) 143 { 144 if (!is_array($data) && !($data instanceof cursor)) { 145 throw new Exception('Invalid data block'); 146 } 147 148 if (is_array($data)) 149 { 150 $D = $data; 151 $data = $this->con->openCursor($this->table); 152 foreach ($D as $k => $v) { 153 $data->{$k} = $v; 154 } 155 unset($D); 156 } 157 158 # We want to put it at the end 159 $this->con->writeLock($this->table); 160 try 161 { 162 $rs = $this->con->select('SELECT MAX('.$this->f_id.') as n_id FROM '.$this->table); 163 $id = $rs->n_id; 164 165 $rs = $this->con->select( 166 'SELECT MAX('.$this->f_right.') as n_r '. 167 'FROM '.$this->table. 168 $this->getCondition('WHERE') 169 ); 170 $last = $rs->n_r == 0 ? 1 : $rs->n_r; 171 172 $data->{$this->f_id} = $id+1; 173 $data->{$this->f_left} = $last+1; 174 $data->{$this->f_right} = $last+2; 175 176 $data->insert(); 177 $this->con->unlock(); 178 try { 179 $this->setNodeParent($id+1,$target); 180 return $data->{$this->f_id}; 181 } catch (Exception $e) {} # We don't mind error in this case 182 } 183 catch (Exception $e) 184 { 185 $this->con->unlock(); 186 throw $e; 187 } 188 } 189 190 public function updatePosition($id,$left,$right) 58 protected $con; 59 60 protected $table; 61 protected $f_left; 62 protected $f_right; 63 protected $f_id; 64 65 protected $add_condition = array(); 66 67 protected $parents; 68 69 public function __construct($con) 70 { 71 $this->con = &$con; 72 } 73 74 public function getChildren($start = 0, $id = null, $sort = 'asc', $fields = array()) 75 { 76 $fields = count($fields) > 0 ? ', C2.' . implode(', C2.', $fields) : ''; 77 78 $sql = 'SELECT C2.' . $this->f_id . ', C2.' . $this->f_left . ', C2.' . $this->f_right . ', COUNT(C1.' . $this->f_id . ') AS level ' 79 . $fields . ' ' 80 . 'FROM ' . $this->table . ' AS C1, ' . $this->table . ' AS C2 %s ' 81 . 'WHERE C2.' . $this->f_left . ' BETWEEN C1.' . $this->f_left . ' AND C1.' . $this->f_right . ' ' 82 . ' %s ' 83 . $this->getCondition('AND', 'C2.') 84 . $this->getCondition('AND', 'C1.') 85 . 'GROUP BY C2.' . $this->f_id . ', C2.' . $this->f_left . ', C2.' . $this->f_right . ' ' . $fields . ' ' 86 . ' %s ' 87 . 'ORDER BY C2.' . $this->f_left . ' ' . ($sort == 'asc' ? 'ASC' : 'DESC') . ' '; 88 89 $from = $where = ''; 90 if ($start > 0) { 91 $from = ', ' . $this->table . ' AS C3'; 92 $where = 'AND C3.' . $this->f_id . ' = ' . (integer) $start . ' AND C1.' . $this->f_left . ' >= C3.' . $this->f_left . ' AND C1.' . $this->f_right . ' <= C3.' . $this->f_right; 93 $where .= $this->getCondition('AND', 'C3.'); 94 } 95 96 $having = ''; 97 if ($id !== null) { 98 $having = ' HAVING C2.' . $this->f_id . ' = ' . (integer) $id; 99 } 100 101 $sql = sprintf($sql, $from, $where, $having); 102 103 return $this->con->select($sql); 104 } 105 106 public function getParents($id, $fields = array()) 107 { 108 $fields = count($fields) > 0 ? ', C1.' . implode(', C1.', $fields) : ''; 109 110 return $this->con->select( 111 'SELECT C1.' . $this->f_id . ' ' . $fields . ' ' 112 . 'FROM ' . $this->table . ' C1, ' . $this->table . ' C2 ' 113 . 'WHERE C2.' . $this->f_id . ' = ' . (integer) $id . ' ' 114 . 'AND C1.' . $this->f_left . ' < C2.' . $this->f_left . ' ' 115 . 'AND C1.' . $this->f_right . ' > C2.' . $this->f_right . ' ' 116 . $this->getCondition('AND', 'C2.') 117 . $this->getCondition('AND', 'C1.') 118 . 'ORDER BY C1.' . $this->f_left . ' ASC ' 119 ); 120 } 121 122 public function getParent($id, $fields = array()) 123 { 124 $fields = count($fields) > 0 ? ', C1.' . implode(', C1.', $fields) : ''; 125 126 return $this->con->select( 127 'SELECT C1.' . $this->f_id . ' ' . $fields . ' ' 128 . 'FROM ' . $this->table . ' C1, ' . $this->table . ' C2 ' 129 . 'WHERE C2.' . $this->f_id . ' = ' . (integer) $id . ' ' 130 . 'AND C1.' . $this->f_left . ' < C2.' . $this->f_left . ' ' 131 . 'AND C1.' . $this->f_right . ' > C2.' . $this->f_right . ' ' 132 . $this->getCondition('AND', 'C2.') 133 . $this->getCondition('AND', 'C1.') 134 . 'ORDER BY C1.' . $this->f_left . ' DESC ' 135 . $this->con->limit(1) 136 ); 137 } 138 139 /* ------------------------------------------------ 140 * Tree manipulations 141 * ---------------------------------------------- */ 142 public function addNode($data, $target = 0) 143 { 144 if (!is_array($data) && !($data instanceof cursor)) { 145 throw new Exception('Invalid data block'); 146 } 147 148 if (is_array($data)) { 149 $D = $data; 150 $data = $this->con->openCursor($this->table); 151 foreach ($D as $k => $v) { 152 $data->{$k} = $v; 153 } 154 unset($D); 155 } 156 157 # We want to put it at the end 158 $this->con->writeLock($this->table); 159 try 191 160 { 192 $node_left = (integer) $left; 193 $node_right = (integer) $right; 194 $node_id = (integer) $id; 195 $sql = 'UPDATE '.$this->table.' SET ' 196 .$this->f_left.' = '.$node_left.', ' 197 .$this->f_right.' = '.$node_right 198 .' WHERE '.$this->f_id .' = '.$node_id 199 .$this->getCondition(); 200 201 $this->con->begin(); 202 try { 203 $this->con->execute($sql); 204 $this->con->commit(); 205 } catch (Exception $e) { 206 $this->con->rollback(); 207 throw $e; 208 } 209 } 210 211 public function deleteNode($node,$keep_children=true) 212 { 213 $node = (integer) $node; 214 215 $rs = $this->getChildren(0,$node); 216 if ($rs->isEmpty()) { 217 throw new Exception('Node does not exist.'); 218 } 219 $node_left = (integer) $rs->{$this->f_left}; 220 $node_right = (integer) $rs->{$this->f_right}; 221 222 try 223 { 224 $this->con->begin(); 225 226 if ($keep_children) 227 { 228 $this->con->execute('DELETE FROM '.$this->table.' WHERE '.$this->f_id.' = '.$node); 229 230 $sql = 'UPDATE '.$this->table.' SET ' 231 . $this->f_right.' = CASE ' 232 . 'WHEN '.$this->f_right.' BETWEEN '.$node_left.' AND '.$node_right.' ' 233 . 'THEN '.$this->f_right.' - 1 ' 234 . 'WHEN '.$this->f_right.' > '.$node_right.' ' 235 . 'THEN '.$this->f_right.' - 2 ' 236 . 'ELSE '.$this->f_right.' ' 237 . 'END, ' 238 . $this->f_left.' = CASE ' 239 . 'WHEN '.$this->f_left.' BETWEEN '.$node_left.' AND '.$node_right.' ' 240 . 'THEN '.$this->f_left.' - 1 ' 241 . 'WHEN '.$this->f_left.' > '.$node_right.' ' 242 . 'THEN '.$this->f_left.' - 2 ' 243 . 'ELSE '.$this->f_left.' ' 244 . 'END ' 245 . 'WHERE '.$this->f_right.' > '.$node_left 246 . $this->getCondition(); 247 248 $this->con->execute($sql); 249 } 250 else 251 { 252 $this->con->execute('DELETE FROM '.$this->table.' WHERE '.$this->f_left.' BETWEEN '.$node_left.' AND '.$node_right); 253 254 $node_delta = $node_right - $node_left + 1; 255 $sql = 'UPDATE '.$this->table.' SET ' 256 . $this->f_left.' = CASE ' 257 . 'WHEN '.$this->f_left.' > '.$node_left.' ' 258 . 'THEN '.$this->f_left.' - ('.$node_delta.') ' 259 . 'ELSE '.$this->f_left.' ' 260 . 'END, ' 261 . $this->f_right.' = CASE ' 262 . 'WHEN '.$this->f_right.' > '.$node_left.' ' 263 . 'THEN '.$this->f_right.' - ('.$node_delta.') ' 264 . 'ELSE '.$this->f_right.' ' 265 . 'END ' 266 . 'WHERE '.$this->f_right.' > '.$node_right 267 . $this->getCondition(); 268 } 269 270 $this->con->commit(); 271 } 272 catch (Exception $e) 273 { 274 $this->con->rollback(); 275 throw $e; 276 } 277 } 278 279 public function resetOrder() 280 { 281 $rs = $this->con->select( 282 'SELECT '.$this->f_id.' ' 283 .'FROM '.$this->table.' ' 284 .$this->getCondition('WHERE') 285 .'ORDER BY '.$this->f_left.' ASC ' 286 ); 287 288 $lft = 2; 289 $this->con->begin(); 290 try 291 { 292 while ($rs->fetch()) { 293 $this->con->execute( 294 'UPDATE '.$this->table.' SET ' 295 .$this->f_left.' = '.($lft++).', ' 296 .$this->f_right.' = '.($lft++).' ' 297 .'WHERE '.$this->f_id .' = '.(integer) $rs->{$this->f_id}.' ' 298 .$this->getCondition() 299 ); 300 } 301 $this->con->commit(); 302 } 303 catch (Exception $e) 304 { 305 $this->con->rollback(); 306 throw $e; 307 } 308 } 309 310 public function setNodeParent($node,$target=0) 311 { 312 if ($node == $target) { 313 return; 314 } 315 $node = (integer) $node; 316 $target = (integer) $target; 317 318 $rs = $this->getChildren(0,$node); 319 if ($rs->isEmpty()) { 320 throw new Exception('Node does not exist.'); 321 } 322 $node_left = (integer) $rs->{$this->f_left}; 323 $node_right = (integer) $rs->{$this->f_right}; 324 $node_level = (integer) $rs->level; 325 326 if ($target > 0) 327 { 328 $rs = $this->getChildren(0,$target); 329 } 330 else 331 { 332 $rs = $this->con->select( 333 'SELECT MIN('.$this->f_left.')-1 AS '.$this->f_left.', MAX('.$this->f_right.')+1 AS '.$this->f_right.', 0 AS level '. 334 'FROM '.$this->table.' '. 335 $this->getCondition('WHERE') 336 ); 337 } 338 $target_left = (integer) $rs->{$this->f_left}; 339 $target_right = (integer) $rs->{$this->f_right}; 340 $target_level = (integer) $rs->level; 341 342 if ($node_left == $target_left 343 || ($target_left >= $node_left && $target_left <= $node_right) 344 || ($node_level == $target_level+1 && $node_left > $target_left && $node_right < $target_right) 345 ) 346 { 347 throw new Exception('Cannot move tree'); 348 } 349 350 if ($target_left < $node_left && $target_right > $node_right && $target_level < $node_level -1) 351 { 352 $sql = 'UPDATE '.$this->table.' SET ' 353 . $this->f_right.' = CASE ' 354 . 'WHEN '.$this->f_right.' BETWEEN '.($node_right+1).' AND '.($target_right-1).' ' 355 . 'THEN '.$this->f_right.'-('.($node_right-$node_left+1).') ' 356 . 'WHEN '.$this->f_left.' BETWEEN '.$node_left.' AND '.$node_right.' ' 357 . 'THEN '.$this->f_right.'+'.((($target_right-$node_right-$node_level+$target_level)/2)*2+$node_level-$target_level-1).' ' 358 . 'ELSE ' 359 . $this->f_right.' ' 360 . 'END, ' 361 . $this->f_left.' = CASE ' 362 . 'WHEN '.$this->f_left.' BETWEEN '.($node_right+1).' AND '.($target_right-1).' ' 363 . 'THEN '.$this->f_left.'-('.($node_right-$node_left+1).') ' 364 . 'WHEN '.$this->f_left.' BETWEEN '.$node_left.' AND '.$node_right.' ' 365 . 'THEN '.$this->f_left.'+'.((($target_right-$node_right-$node_level+$target_level)/2)*2+$node_level-$target_level-1).' ' 366 . 'ELSE '.$this->f_left.' ' 367 . 'END ' 368 . 'WHERE '.$this->f_left.' BETWEEN '.($target_left+1).' AND '.($target_right-1).''; 369 } 370 elseif ($target_left < $node_left) 371 { 372 $sql = 'UPDATE '.$this->table.' SET ' 373 . $this->f_left.' = CASE ' 374 . 'WHEN '.$this->f_left.' BETWEEN '.$target_right.' AND '.($node_left-1).' ' 375 . 'THEN '.$this->f_left.'+'.($node_right-$node_left+1).' ' 376 . 'WHEN '.$this->f_left.' BETWEEN '.$node_left.' AND '.$node_right.' ' 377 . 'THEN '.$this->f_left.'-('.($node_left-$target_right).') ' 378 . 'ELSE '.$this->f_left .' ' 379 . 'END, ' 380 . $this->f_right.' = CASE ' 381 . 'WHEN '.$this->f_right.' BETWEEN '.$target_right.' AND '.$node_left.' ' 382 . 'THEN '.$this->f_right.'+'.($node_right-$node_left+1).' ' 383 . 'WHEN '.$this->f_right.' BETWEEN '.$node_left.' AND '.$node_right.' ' 384 . 'THEN '.$this->f_right.'-('.($node_left-$target_right).') ' 385 . 'ELSE '.$this->f_right.' ' 386 . 'END ' 387 . 'WHERE ('.$this->f_left.' BETWEEN '.$target_left.' AND '.$node_right. ' ' 388 . 'OR '.$this->f_right.' BETWEEN '.$target_left.' AND '.$node_right.')'; 389 } 390 else 391 { 392 $sql = 'UPDATE '.$this->table.' SET ' 393 . $this->f_left.' = CASE ' 394 . 'WHEN '.$this->f_left.' BETWEEN '.$node_right.' AND '.$target_right.' ' 395 . 'THEN '.$this->f_left.'-'.($node_right-$node_left+1).' ' 396 . 'WHEN '.$this->f_left.' BETWEEN '.$node_left.' AND '.$node_right.' ' 397 . 'THEN '.$this->f_left.'+'.($target_right-1-$node_right).' ' 398 . 'ELSE '.$this->f_left.' ' 399 . 'END, ' 400 . $this->f_right.' = CASE ' 401 . 'WHEN '.$this->f_right.' BETWEEN '.($node_right+1).' AND '.($target_right-1).' ' 402 . 'THEN '.$this->f_right.'-'.($node_right-$node_left+1).' ' 403 . 'WHEN '.$this->f_right.' BETWEEN '.$node_left.' AND '.$node_right.' ' 404 . 'THEN '.$this->f_right.'+'.($target_right-1-$node_right).' ' 405 . 'ELSE '.$this->f_right.' ' 406 . 'END ' 407 . 'WHERE ('.$this->f_left.' BETWEEN '.$node_left.' AND '.$target_right.' ' 408 . 'OR '.$this->f_right.' BETWEEN '.$node_left.' AND '.$target_right.')'; 409 } 410 411 $sql .= ' '.$this->getCondition(); 412 413 $this->con->execute($sql); 414 } 415 416 public function setNodePosition($nodeA,$nodeB,$position='after') 417 { 418 $nodeA = (integer) $nodeA; 419 $nodeB = (integer) $nodeB; 420 421 $rs = $this->getChildren(0,$nodeA); 422 if ($rs->isEmpty()) { 423 throw new Exception('Node does not exist.'); 424 } 425 $A_left = $rs->{$this->f_left}; 426 $A_right = $rs->{$this->f_right}; 427 $A_level = $rs->level; 428 429 $rs = $this->getChildren(0,$nodeB); 430 if ($rs->isEmpty()) { 431 throw new Exception('Node does not exist.'); 432 } 433 $B_left = $rs->{$this->f_left}; 434 $B_right = $rs->{$this->f_right}; 435 $B_level = $rs->level; 436 437 if ($A_level != $B_level) { 438 throw new Exception('Cannot change position'); 439 } 440 441 $rs = $this->getParents($nodeA); 442 $parentA = $rs->isEmpty() ? 0 : $rs->{$this->f_id}; 443 $rs = $this->getParents($nodeB); 444 $parentB = $rs->isEmpty() ? 0 : $rs->{$this->f_id}; 445 446 if ($parentA != $parentB) { 447 throw new Exception('Cannot change position'); 448 } 449 450 if ($position == 'before') 451 { 452 if ($A_left > $B_left) { 453 $sql = 'UPDATE '.$this->table.' SET ' 454 . $this->f_right.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_right.' - ('.($A_left - $B_left).') ' 455 . 'WHEN '.$this->f_left.' BETWEEN '.$B_left.' AND '.($A_left - 1).' THEN '.$this->f_right.' + '.($A_right - $A_left + 1).' ELSE '.$this->f_right.' END, ' 456 . $this->f_left.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_left.' - ('.($A_left - $B_left).') ' 457 . 'WHEN '.$this->f_left.' BETWEEN '.$B_left.' AND '.($A_left - 1).' THEN '.$this->f_left.' + '.($A_right - $A_left + 1).' ELSE '.$this->f_left.' END ' 458 . 'WHERE '.$this->f_left.' BETWEEN '.$B_left.' AND '.$A_right; 459 } else { 460 $sql = 'UPDATE '.$this->table.' SET ' 461 . $this->f_right.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_right.' + '.(($B_left - $A_left) - ($A_right - $A_left + 1)).' ' 462 . 'WHEN '.$this->f_left.' BETWEEN '.($A_right + 1).' AND '.($B_left - 1).' THEN '.$this->f_right.' - ('.(($A_right - $A_left + 1)).') ELSE '.$this->f_right.' END, ' 463 . $this->f_left.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_left.' + '.(($B_left - $A_left) - ($A_right - $A_left + 1)).' ' 464 . 'WHEN '.$this->f_left.' BETWEEN '.($A_right + 1).' AND '.($B_left - 1).' THEN '.$this->f_left.' - ('.($A_right - $A_left + 1).') ELSE '.$this->f_left.' END ' 465 . 'WHERE '.$this->f_left.' BETWEEN '.$A_left.' AND '.($B_left - 1); 466 } 467 } 468 else 469 { 470 if ($A_left > $B_left) { 471 $sql = 'UPDATE '.$this->table.' SET ' 472 . $this->f_right.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_right.' - ('.($A_left - $B_left - ($B_right - $B_left + 1)).') ' 473 . 'WHEN '.$this->f_left.' BETWEEN '.($B_right + 1).' AND '.($A_left - 1).' THEN '.$this->f_right.' + '.($A_right - $A_left + 1).' ELSE '.$this->f_right.' END, ' 474 . $this->f_left.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_left.' - ('.($A_left - $B_left - ($B_right - $B_left + 1)).') ' 475 . 'WHEN '.$this->f_left.' BETWEEN '.($B_right + 1).' AND '.($A_left - 1).' THEN '.$this->f_left.' + '.($A_right - $A_left + 1).' ELSE '.$this->f_left.' END ' 476 . 'WHERE '.$this->f_left.' BETWEEN '.($B_right + 1).' AND '.$A_right; 477 } else { 478 $sql = 'UPDATE '.$this->table.' SET ' 479 . $this->f_right.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_right.' + '.($B_right - $A_right).' ' 480 . 'WHEN '.$this->f_left.' BETWEEN '.($A_right + 1).' AND '.$B_right.' THEN '.$this->f_right.' - ('.(($A_right - $A_left + 1)).') ELSE '.$this->f_right.' END, ' 481 . $this->f_left.' = CASE WHEN '.$this->f_left.' BETWEEN '.$A_left.' AND '.$A_right.' THEN '.$this->f_left.' + '.($B_right - $A_right).' ' 482 . 'WHEN '.$this->f_left.' BETWEEN '.($A_right + 1).' AND '.$B_right.' THEN '.$this->f_left.' - ('.($A_right - $A_left + 1).') ELSE '.$this->f_left.' END ' 483 . 'WHERE '.$this->f_left.' BETWEEN '.$A_left.' AND '.$B_right; 484 } 485 } 486 487 $sql .= $this->getCondition(); 488 $this->con->execute($sql); 489 } 490 491 protected function getCondition($start='AND',$prefix='') 492 { 493 if (empty($this->add_condition)) { 494 return ''; 495 } 496 497 $w = array(); 498 foreach ($this->add_condition as $c => $n) { 499 $w[] = $prefix.$c.' = '.$n; 500 } 501 return ' '.$start.' '.implode(' AND ',$w).' '; 502 } 161 $rs = $this->con->select('SELECT MAX(' . $this->f_id . ') as n_id FROM ' . $this->table); 162 $id = $rs->n_id; 163 164 $rs = $this->con->select( 165 'SELECT MAX(' . $this->f_right . ') as n_r ' . 166 'FROM ' . $this->table . 167 $this->getCondition('WHERE') 168 ); 169 $last = $rs->n_r == 0 ? 1 : $rs->n_r; 170 171 $data->{$this->f_id} = $id + 1; 172 $data->{$this->f_left} = $last + 1; 173 $data->{$this->f_right} = $last + 2; 174 175 $data->insert(); 176 $this->con->unlock(); 177 try { 178 $this->setNodeParent($id + 1, $target); 179 return $data->{$this->f_id}; 180 } catch (Exception $e) {} # We don't mind error in this case 181 } catch (Exception $e) { 182 $this->con->unlock(); 183 throw $e; 184 } 185 } 186 187 public function updatePosition($id, $left, $right) 188 { 189 $node_left = (integer) $left; 190 $node_right = (integer) $right; 191 $node_id = (integer) $id; 192 $sql = 'UPDATE ' . $this->table . ' SET ' 193 . $this->f_left . ' = ' . $node_left . ', ' 194 . $this->f_right . ' = ' . $node_right 195 . ' WHERE ' . $this->f_id . ' = ' . $node_id 196 . $this->getCondition(); 197 198 $this->con->begin(); 199 try { 200 $this->con->execute($sql); 201 $this->con->commit(); 202 } catch (Exception $e) { 203 $this->con->rollback(); 204 throw $e; 205 } 206 } 207 208 public function deleteNode($node, $keep_children = true) 209 { 210 $node = (integer) $node; 211 212 $rs = $this->getChildren(0, $node); 213 if ($rs->isEmpty()) { 214 throw new Exception('Node does not exist.'); 215 } 216 $node_left = (integer) $rs->{$this->f_left}; 217 $node_right = (integer) $rs->{$this->f_right}; 218 219 try 220 { 221 $this->con->begin(); 222 223 if ($keep_children) { 224 $this->con->execute('DELETE FROM ' . $this->table . ' WHERE ' . $this->f_id . ' = ' . $node); 225 226 $sql = 'UPDATE ' . $this->table . ' SET ' 227 . $this->f_right . ' = CASE ' 228 . 'WHEN ' . $this->f_right . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 229 . 'THEN ' . $this->f_right . ' - 1 ' 230 . 'WHEN ' . $this->f_right . ' > ' . $node_right . ' ' 231 . 'THEN ' . $this->f_right . ' - 2 ' 232 . 'ELSE ' . $this->f_right . ' ' 233 . 'END, ' 234 . $this->f_left . ' = CASE ' 235 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 236 . 'THEN ' . $this->f_left . ' - 1 ' 237 . 'WHEN ' . $this->f_left . ' > ' . $node_right . ' ' 238 . 'THEN ' . $this->f_left . ' - 2 ' 239 . 'ELSE ' . $this->f_left . ' ' 240 . 'END ' 241 . 'WHERE ' . $this->f_right . ' > ' . $node_left 242 . $this->getCondition(); 243 244 $this->con->execute($sql); 245 } else { 246 $this->con->execute('DELETE FROM ' . $this->table . ' WHERE ' . $this->f_left . ' BETWEEN ' . $node_left . ' AND ' . $node_right); 247 248 $node_delta = $node_right - $node_left + 1; 249 $sql = 'UPDATE ' . $this->table . ' SET ' 250 . $this->f_left . ' = CASE ' 251 . 'WHEN ' . $this->f_left . ' > ' . $node_left . ' ' 252 . 'THEN ' . $this->f_left . ' - (' . $node_delta . ') ' 253 . 'ELSE ' . $this->f_left . ' ' 254 . 'END, ' 255 . $this->f_right . ' = CASE ' 256 . 'WHEN ' . $this->f_right . ' > ' . $node_left . ' ' 257 . 'THEN ' . $this->f_right . ' - (' . $node_delta . ') ' 258 . 'ELSE ' . $this->f_right . ' ' 259 . 'END ' 260 . 'WHERE ' . $this->f_right . ' > ' . $node_right 261 . $this->getCondition(); 262 } 263 264 $this->con->commit(); 265 } catch (Exception $e) { 266 $this->con->rollback(); 267 throw $e; 268 } 269 } 270 271 public function resetOrder() 272 { 273 $rs = $this->con->select( 274 'SELECT ' . $this->f_id . ' ' 275 . 'FROM ' . $this->table . ' ' 276 . $this->getCondition('WHERE') 277 . 'ORDER BY ' . $this->f_left . ' ASC ' 278 ); 279 280 $lft = 2; 281 $this->con->begin(); 282 try 283 { 284 while ($rs->fetch()) { 285 $this->con->execute( 286 'UPDATE ' . $this->table . ' SET ' 287 . $this->f_left . ' = ' . ($lft++) . ', ' 288 . $this->f_right . ' = ' . ($lft++) . ' ' 289 . 'WHERE ' . $this->f_id . ' = ' . (integer) $rs->{$this->f_id} . ' ' 290 . $this->getCondition() 291 ); 292 } 293 $this->con->commit(); 294 } catch (Exception $e) { 295 $this->con->rollback(); 296 throw $e; 297 } 298 } 299 300 public function setNodeParent($node, $target = 0) 301 { 302 if ($node == $target) { 303 return; 304 } 305 $node = (integer) $node; 306 $target = (integer) $target; 307 308 $rs = $this->getChildren(0, $node); 309 if ($rs->isEmpty()) { 310 throw new Exception('Node does not exist.'); 311 } 312 $node_left = (integer) $rs->{$this->f_left}; 313 $node_right = (integer) $rs->{$this->f_right}; 314 $node_level = (integer) $rs->level; 315 316 if ($target > 0) { 317 $rs = $this->getChildren(0, $target); 318 } else { 319 $rs = $this->con->select( 320 'SELECT MIN(' . $this->f_left . ')-1 AS ' . $this->f_left . ', MAX(' . $this->f_right . ')+1 AS ' . $this->f_right . ', 0 AS level ' . 321 'FROM ' . $this->table . ' ' . 322 $this->getCondition('WHERE') 323 ); 324 } 325 $target_left = (integer) $rs->{$this->f_left}; 326 $target_right = (integer) $rs->{$this->f_right}; 327 $target_level = (integer) $rs->level; 328 329 if ($node_left == $target_left 330 || ($target_left >= $node_left && $target_left <= $node_right) 331 || ($node_level == $target_level + 1 && $node_left > $target_left && $node_right < $target_right) 332 ) { 333 throw new Exception('Cannot move tree'); 334 } 335 336 if ($target_left < $node_left && $target_right > $node_right && $target_level < $node_level - 1) { 337 $sql = 'UPDATE ' . $this->table . ' SET ' 338 . $this->f_right . ' = CASE ' 339 . 'WHEN ' . $this->f_right . ' BETWEEN ' . ($node_right + 1) . ' AND ' . ($target_right - 1) . ' ' 340 . 'THEN ' . $this->f_right . '-(' . ($node_right - $node_left + 1) . ') ' 341 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 342 . 'THEN ' . $this->f_right . '+' . ((($target_right - $node_right - $node_level + $target_level) / 2) * 2 + $node_level - $target_level - 1) . ' ' 343 . 'ELSE ' 344 . $this->f_right . ' ' 345 . 'END, ' 346 . $this->f_left . ' = CASE ' 347 . 'WHEN ' . $this->f_left . ' BETWEEN ' . ($node_right + 1) . ' AND ' . ($target_right - 1) . ' ' 348 . 'THEN ' . $this->f_left . '-(' . ($node_right - $node_left + 1) . ') ' 349 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 350 . 'THEN ' . $this->f_left . '+' . ((($target_right - $node_right - $node_level + $target_level) / 2) * 2 + $node_level - $target_level - 1) . ' ' 351 . 'ELSE ' . $this->f_left . ' ' 352 . 'END ' 353 . 'WHERE ' . $this->f_left . ' BETWEEN ' . ($target_left + 1) . ' AND ' . ($target_right - 1) . ''; 354 } elseif ($target_left < $node_left) { 355 $sql = 'UPDATE ' . $this->table . ' SET ' 356 . $this->f_left . ' = CASE ' 357 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $target_right . ' AND ' . ($node_left - 1) . ' ' 358 . 'THEN ' . $this->f_left . '+' . ($node_right - $node_left + 1) . ' ' 359 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 360 . 'THEN ' . $this->f_left . '-(' . ($node_left - $target_right) . ') ' 361 . 'ELSE ' . $this->f_left . ' ' 362 . 'END, ' 363 . $this->f_right . ' = CASE ' 364 . 'WHEN ' . $this->f_right . ' BETWEEN ' . $target_right . ' AND ' . $node_left . ' ' 365 . 'THEN ' . $this->f_right . '+' . ($node_right - $node_left + 1) . ' ' 366 . 'WHEN ' . $this->f_right . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 367 . 'THEN ' . $this->f_right . '-(' . ($node_left - $target_right) . ') ' 368 . 'ELSE ' . $this->f_right . ' ' 369 . 'END ' 370 . 'WHERE (' . $this->f_left . ' BETWEEN ' . $target_left . ' AND ' . $node_right . ' ' 371 . 'OR ' . $this->f_right . ' BETWEEN ' . $target_left . ' AND ' . $node_right . ')'; 372 } else { 373 $sql = 'UPDATE ' . $this->table . ' SET ' 374 . $this->f_left . ' = CASE ' 375 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $node_right . ' AND ' . $target_right . ' ' 376 . 'THEN ' . $this->f_left . '-' . ($node_right - $node_left + 1) . ' ' 377 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 378 . 'THEN ' . $this->f_left . '+' . ($target_right - 1 - $node_right) . ' ' 379 . 'ELSE ' . $this->f_left . ' ' 380 . 'END, ' 381 . $this->f_right . ' = CASE ' 382 . 'WHEN ' . $this->f_right . ' BETWEEN ' . ($node_right + 1) . ' AND ' . ($target_right - 1) . ' ' 383 . 'THEN ' . $this->f_right . '-' . ($node_right - $node_left + 1) . ' ' 384 . 'WHEN ' . $this->f_right . ' BETWEEN ' . $node_left . ' AND ' . $node_right . ' ' 385 . 'THEN ' . $this->f_right . '+' . ($target_right - 1 - $node_right) . ' ' 386 . 'ELSE ' . $this->f_right . ' ' 387 . 'END ' 388 . 'WHERE (' . $this->f_left . ' BETWEEN ' . $node_left . ' AND ' . $target_right . ' ' 389 . 'OR ' . $this->f_right . ' BETWEEN ' . $node_left . ' AND ' . $target_right . ')'; 390 } 391 392 $sql .= ' ' . $this->getCondition(); 393 394 $this->con->execute($sql); 395 } 396 397 public function setNodePosition($nodeA, $nodeB, $position = 'after') 398 { 399 $nodeA = (integer) $nodeA; 400 $nodeB = (integer) $nodeB; 401 402 $rs = $this->getChildren(0, $nodeA); 403 if ($rs->isEmpty()) { 404 throw new Exception('Node does not exist.'); 405 } 406 $A_left = $rs->{$this->f_left}; 407 $A_right = $rs->{$this->f_right}; 408 $A_level = $rs->level; 409 410 $rs = $this->getChildren(0, $nodeB); 411 if ($rs->isEmpty()) { 412 throw new Exception('Node does not exist.'); 413 } 414 $B_left = $rs->{$this->f_left}; 415 $B_right = $rs->{$this->f_right}; 416 $B_level = $rs->level; 417 418 if ($A_level != $B_level) { 419 throw new Exception('Cannot change position'); 420 } 421 422 $rs = $this->getParents($nodeA); 423 $parentA = $rs->isEmpty() ? 0 : $rs->{$this->f_id}; 424 $rs = $this->getParents($nodeB); 425 $parentB = $rs->isEmpty() ? 0 : $rs->{$this->f_id}; 426 427 if ($parentA != $parentB) { 428 throw new Exception('Cannot change position'); 429 } 430 431 if ($position == 'before') { 432 if ($A_left > $B_left) { 433 $sql = 'UPDATE ' . $this->table . ' SET ' 434 . $this->f_right . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_right . ' - (' . ($A_left - $B_left) . ') ' 435 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $B_left . ' AND ' . ($A_left - 1) . ' THEN ' . $this->f_right . ' + ' . ($A_right - $A_left + 1) . ' ELSE ' . $this->f_right . ' END, ' 436 . $this->f_left . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_left . ' - (' . ($A_left - $B_left) . ') ' 437 . 'WHEN ' . $this->f_left . ' BETWEEN ' . $B_left . ' AND ' . ($A_left - 1) . ' THEN ' . $this->f_left . ' + ' . ($A_right - $A_left + 1) . ' ELSE ' . $this->f_left . ' END ' 438 . 'WHERE ' . $this->f_left . ' BETWEEN ' . $B_left . ' AND ' . $A_right; 439 } else { 440 $sql = 'UPDATE ' . $this->table . ' SET ' 441 . $this->f_right . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_right . ' + ' . (($B_left - $A_left) - ($A_right - $A_left + 1)) . ' ' 442 . 'WHEN ' . $this->f_left . ' BETWEEN ' . ($A_right + 1) . ' AND ' . ($B_left - 1) . ' THEN ' . $this->f_right . ' - (' . (($A_right - $A_left + 1)) . ') ELSE ' . $this->f_right . ' END, ' 443 . $this->f_left . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_left . ' + ' . (($B_left - $A_left) - ($A_right - $A_left + 1)) . ' ' 444 . 'WHEN ' . $this->f_left . ' BETWEEN ' . ($A_right + 1) . ' AND ' . ($B_left - 1) . ' THEN ' . $this->f_left . ' - (' . ($A_right - $A_left + 1) . ') ELSE ' . $this->f_left . ' END ' 445 . 'WHERE ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . ($B_left - 1); 446 } 447 } else { 448 if ($A_left > $B_left) { 449 $sql = 'UPDATE ' . $this->table . ' SET ' 450 . $this->f_right . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_right . ' - (' . ($A_left - $B_left - ($B_right - $B_left + 1)) . ') ' 451 . 'WHEN ' . $this->f_left . ' BETWEEN ' . ($B_right + 1) . ' AND ' . ($A_left - 1) . ' THEN ' . $this->f_right . ' + ' . ($A_right - $A_left + 1) . ' ELSE ' . $this->f_right . ' END, ' 452 . $this->f_left . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_left . ' - (' . ($A_left - $B_left - ($B_right - $B_left + 1)) . ') ' 453 . 'WHEN ' . $this->f_left . ' BETWEEN ' . ($B_right + 1) . ' AND ' . ($A_left - 1) . ' THEN ' . $this->f_left . ' + ' . ($A_right - $A_left + 1) . ' ELSE ' . $this->f_left . ' END ' 454 . 'WHERE ' . $this->f_left . ' BETWEEN ' . ($B_right + 1) . ' AND ' . $A_right; 455 } else { 456 $sql = 'UPDATE ' . $this->table . ' SET ' 457 . $this->f_right . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_right . ' + ' . ($B_right - $A_right) . ' ' 458 . 'WHEN ' . $this->f_left . ' BETWEEN ' . ($A_right + 1) . ' AND ' . $B_right . ' THEN ' . $this->f_right . ' - (' . (($A_right - $A_left + 1)) . ') ELSE ' . $this->f_right . ' END, ' 459 . $this->f_left . ' = CASE WHEN ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $A_right . ' THEN ' . $this->f_left . ' + ' . ($B_right - $A_right) . ' ' 460 . 'WHEN ' . $this->f_left . ' BETWEEN ' . ($A_right + 1) . ' AND ' . $B_right . ' THEN ' . $this->f_left . ' - (' . ($A_right - $A_left + 1) . ') ELSE ' . $this->f_left . ' END ' 461 . 'WHERE ' . $this->f_left . ' BETWEEN ' . $A_left . ' AND ' . $B_right; 462 } 463 } 464 465 $sql .= $this->getCondition(); 466 $this->con->execute($sql); 467 } 468 469 protected function getCondition($start = 'AND', $prefix = '') 470 { 471 if (empty($this->add_condition)) { 472 return ''; 473 } 474 475 $w = array(); 476 foreach ($this->add_condition as $c => $n) { 477 $w[] = $prefix . $c . ' = ' . $n; 478 } 479 return ' ' . $start . ' ' . implode(' AND ', $w) . ' '; 480 } 503 481 }
Note: See TracChangeset
for help on using the changeset viewer.