1: <?php
  2: 
  3: /**
  4:  * Pry Framework
  5:  *
  6:  * LICENSE
  7:  *
  8:  * This source file is subject to the new BSD license that is bundled
  9:  * with this package in the file LICENSE.txt.
 10:  * 
 11:  */
 12: 
 13: namespace Pry\Db;
 14: 
 15: /**
 16:  * Classe Représentation intervallaire
 17:  *
 18:  * Permet la gestion de table à représentation intervallaire
 19:  * 
 20:  * <code>
 21:  * $interval = new NestedTree('category');
 22:  * $interval->setDB($objetZendDBAdapter);
 23:  * echo $interval->getHTMLTree();
 24:  * $interval->setCurrent(5);
 25:  * $interval->addChild('Fils de 5');
 26:  * $internal->getChilds(); // Retourne tous les enfant de 5
 27:  * $interval->getChilds(Db_NestedTree::LEAFONLY); // Retourne tous les enfant de 5 qui ne sont pas des noeuds
 28:  * </code>
 29:  *
 30:  * @category Pry
 31:  * @package Db
 32:  * @version 0.9.9 
 33:  * @author Olivier ROGER <oroger.fr>
 34:  *        
 35:  * @todo suppression de noeud
 36:  */
 37: class NestedTree
 38: {
 39: 
 40:     /**
 41:      * Objet base de données
 42:      * @var Zend_Db_Adapter_Abstract
 43:      */
 44:     protected $oSql = null;
 45: 
 46:     /**
 47:      * Nom de la catégorie
 48:      * @var string
 49:      */
 50:     protected $label;
 51: 
 52:     /**
 53:      * Nom du champs de limite gauche
 54:      * @var string
 55:      */
 56:     protected $leftBound;
 57: 
 58:     /**
 59:      * Nom du champs de limite droit
 60:      * @var string
 61:      */
 62:     protected $rightBound;
 63: 
 64:     /**
 65:      * Nom de la table
 66:      * @var string
 67:      */
 68:     protected $tableName;
 69: 
 70:     /**
 71:      * Nom du champs de profondeur
 72:      * @var string
 73:      */
 74:     protected $level;
 75: 
 76:     /**
 77:      * Nom du champs id
 78:      * @var string
 79:      */
 80:     protected $id;
 81: 
 82:     /**
 83:      * Tableau contenant les bornes et le niveau de l'élément courant
 84:      * @var array
 85:      */
 86:     protected $current;
 87: 
 88:     const LEAFONLY = 1;
 89:     const NODEONLY = 2;
 90: 
 91:     /**
 92:      * Initialise la représentation
 93:      *
 94:      * @param string $table nom de la table
 95:      * @param string $label Nom du champs label
 96:      * @param string $left Nom du champs de borne gauche
 97:      * @param string $right Nom du champs de borne droite
 98:      * @param string $level Nom du champs du niveau de profondeur
 99:      * @param string $id Nom du champs id
100:      * @access public
101:      */
102:     public function __construct($table, $label = 'nom', $left = 'left', $right = 'right', $level = 'level', $id = 'id')
103:     {
104:         $this->tableName  = $table;
105:         $this->leftBound  = $left;
106:         $this->rightBound = $right;
107:         $this->level      = $level;
108:         $this->id         = $id;
109:         $this->label      = $label;
110:         $this->current    = array();
111:     }
112: 
113:     public function setDB(Zend_Db_Adapter_Abstract $db)
114:     {
115:         $this->oSql = $db;
116:     }
117: 
118:     /**
119:      * Défini l'élément de travail
120:      *
121:      * @param int $id Id de l'élément
122:      * @access public
123:      * @return string Nom de l'élément courant
124:      */
125:     public function setCurrent($id)
126:     {
127:         if (empty($this->oSql))
128:             throw new \RuntimeException("No db object set. Please use setDB() first");
129: 
130:         $prepare = $this->oSql->prepare('SELECT `' . $this->leftBound . '`,`' . $this->rightBound . '`,`' . $this->level . '`,`' . $this->label . '` 
131:                                       FROM ' . $this->tableName . ' 
132:                                       WHERE `' . $this->id . '` =:id');
133:         $prepare->execute(array(':id' => $id));
134:         $current                = $prepare->fetch(PDO::FETCH_ASSOC);
135:         $this->current['left']  = intval($current[$this->leftBound]);
136:         $this->current['right'] = intval($current[$this->rightBound]);
137:         $this->current['level'] = intval($current[$this->level]);
138:         return $current[$this->label];
139:     }
140: 
141:     /**
142:      * Ajoute un élément racine.
143:      * Permet de débuter un arbre
144:      *
145:      * @param string $nom Nom de l'élément
146:      * @access public
147:      * @return int Id de l'élément
148:      */
149:     public function addRootElement($nom)
150:     {
151:         if (empty($this->oSql))
152:             throw new \RuntimeException("No db object set. Please use setDB() first");
153: 
154:         //Existe il déjà un élément root ?
155:         $data = $this->oSql->query('SELECT `' . $this->rightBound . '` FROM ' . $this->tableName . ' WHERE `' . $this->leftBound . '` = 1')->fetch(PDO::FETCH_ASSOC);
156:         if (!is_null($data[$this->rightBound]))
157:         {
158:             $leftB  = $right + 1;
159:             $rightB = $right + 2;
160:             $level  = $data[$this->level] + 1;
161:         }
162:         else
163:         {
164:             $leftB   = 1;
165:             $rightB  = 2;
166:             $level   = 0;
167:         }
168:         $prepare = $this->oSql->prepare('INSERT INTO ' . $this->tableName . ' (`' . $this->label . '`,`' . $this->leftBound . '`,`' . $this->rightBound . '`,`' . $this->level . '`) 
169:                             VALUES(:nom,:left,:right,:level)');
170:         $prepare->execute(array(':nom' => $nom, ':left' => $leftB, ':right' => $rightB, ':level' => $level));
171: 
172:         return $this->oSql->last_id();
173:     }
174: 
175:     /**
176:      * Retourne un tableau de l'arborescence avec des niveaux de profondeur
177:      * 
178:      * @access public
179:      * @return array
180:      */
181:     public function getTree()
182:     {
183:         if (empty($this->oSql))
184:             throw new \RuntimeException("No db object set. Please use setDB() first");
185: 
186:         $data   = $this->oSql->query('SELECT node.' . $this->label . ', (COUNT(parent.' . $this->label . ')-1) as depth 
187:                             FROM ' . $this->tableName . ' as node,' . $this->tableName . ' as parent 
188:                             WHERE node.' . $this->leftBound . ' BETWEEN parent.' . $this->leftBound . ' AND parent.' . $this->rightBound . ' 
189:                             GROUP BY node.' . $this->label . '
190:                             ORDER BY node.' . $this->leftBound . ',depth');
191:         while ($result = $data->fetch(PDO::FETCH_ASSOC))
192:             $tree[] = $result;
193: 
194:         return $tree;
195:     }
196: 
197:     /**
198:      * Retourne l'arborescence sous forme de balise HTML
199:      *
200:      * @access public
201:      * @return string
202:      */
203:     public function getHTMLTree()
204:     {
205:         $tree     = $this->getTree();
206:         //var_dump($tree);
207:         $depth    = 0;
208:         $htmlTree = '';
209:         $htmlTree .='<ul>';
210:         foreach ($tree as $value) {
211:             if ($depth < $value['depth'])
212:             {
213:                 $htmlTree .=str_repeat('<ul>', ($value['depth'] - $depth));
214:                 $depth = $value['depth'];
215:                 $htmlTree .= '<li>' . $value[$this->label] . '</li>';
216:             }
217:             elseif ($depth > $value['depth'])
218:             {
219:                 $htmlTree .= str_repeat('</ul>', ++$depth - $value['depth']);
220:                 $depth = $value['depth'];
221:                 $htmlTree .='<ul><li>' . $value[$this->label] . '</li>';
222:             }
223:             else
224:                 $htmlTree .='<li>' . $value[$this->label] . '</li>';
225:         }
226:         $htmlTree.='</ul>';
227:         return $htmlTree;
228:     }
229: 
230:     /**
231:      * Compte les enfants de l'élément
232:      * 
233:      * @param int $option Permet de choisir si on veut uniquement les noeuds ou les feuilles
234:      * @access public
235:      * @return int
236:      */
237:     public function countChilds($option = null)
238:     {
239:         if (empty($this->oSql))
240:             throw new \RuntimeException("No db object set. Please use setDB() first");
241: 
242:         $this->checkCurrent();
243: 
244:         if (!empty($option))
245:         {
246:             if (self::LEAFONLY == $option)
247:                 $option = ' AND (`' . $this->rightBound . '`-`' . $this->leftBound . '`)=1';
248:             elseif (self::NODEONLY == $option)
249:                 $option = ' AND (`' . $this->rightBound . '`-`' . $this->leftBound . '`)>1';
250:             else
251:                 $option = '';
252:         }
253:         else
254:             $option = '';
255: 
256:         $child = $this->oSql->query('SELECT COUNT(*) 
257:                                       FROM ' . $this->tableName . '
258:                                       WHERE `' . $this->leftBound . '`>' . $this->current['left'] . ' 
259:                                         AND `' . $this->rightBound . '` < ' . $this->current['right'] . $option)
260:                 ->fetchColumn(0);
261:         return $child;
262:     }
263: 
264:     /**
265:      * Retourne l'id et le label des enfants de l'éléments.
266:      * 
267:      * @param boolean $direct Liste que les fils de niveau n+1
268:      * @param int $option Permet de choisir si on veut uniquement les noeud ou les feuilles
269:      * @access public
270:      * @return array
271:      */
272:     public function getChilds($direct = false, $option = null)
273:     {
274:         if (empty($this->oSql))
275:             throw new \RuntimeException("No db object set. Please use setDB() first");
276: 
277:         $this->checkCurrent();
278: 
279:         $childs = array();
280:         if (!empty($option))
281:         {
282:             if (self::LEAFONLY == $option)
283:                 $option = ' AND (`' . $this->rightBound . '`-`' . $this->leftBound . '`)=1';
284:             elseif (self::NODEONLY == $option)
285:                 $option = ' AND (`' . $this->rightBound . '`-`' . $this->leftBound . '`)>1';
286:             else
287:                 $option = '';
288:         }
289:         else
290:             $option = '';
291: 
292:         if ($direct)
293:             $option .= ' AND ' . $this->level . ' = ' . ($this->current['level'] + 1);
294: 
295:         $child = $this->oSql->query('SELECT ' . $this->id . ',' . $this->label . ' 
296:                                       FROM ' . $this->tableName . '
297:                                       WHERE `' . $this->leftBound . '`>' . $this->current['left'] . ' 
298:                                         AND `' . $this->rightBound . '` < ' . $this->current['right'] . $option);
299: 
300:         while ($result   = $child->fetch(PDO::FETCH_ASSOC))
301:             $childs[] = $result;
302: 
303:         return $childs;
304:     }
305: 
306:     /**
307:      * Ajoute un enfant (par la droite) à l'élément
308:      *
309:      * @param string $nom Nom de l'élément à ajouter
310:      * @access public
311:      * @return id Id de l'élément ajouté
312:      */
313:     public function addChild($nom)
314:     {
315:         if (empty($this->oSql))
316:             throw new \RuntimeException("No db object set. Please use setDB() first");
317: 
318:         $this->checkCurrent();
319: 
320:         // insertion après la borne droite du père
321:         $this->oSql->query('UPDATE ' . $this->tableName . ' 
322:                             SET `' . $this->rightBound . '`=`' . $this->rightBound . '`+2
323:                             WHERE `' . $this->rightBound . '`>=' . $this->current['right']);
324:         // 2 requetes de décalage pour éviter les problème d'unicité
325:         $this->oSql->query('UPDATE ' . $this->tableName . ' 
326:                             SET `' . $this->leftBound . '`=`' . $this->leftBound . '`+2
327:                             WHERE `' . $this->leftBound . '`>=' . $this->current['right']);
328: 
329: 
330:         $prepare = $this->oSql->prepare('INSERT INTO ' . $this->tableName . ' (`' . $this->label . '`,`' . $this->leftBound . '`,`' . $this->rightBound . '`,`' . $this->level . '`)
331:                                     VALUES(:nom,:left,:right,:level)');
332:         $prepare->execute(array(':nom' => $nom, ':left' => $this->current['right'], ':right' => $this->current['right'] + 1, ':level' => $this->current['level'] + 1));
333: 
334:         $this->current['right']+=2;
335:         return $this->oSql->last_id();
336:     }
337: 
338:     /**
339:      * Supprime l'élément courant si c'est une feuille
340:      *
341:      * @access public
342:      * @return boolean
343:      */
344:     public function delete()
345:     {
346:         if (empty($this->oSql))
347:             throw new \RuntimeException("No db object set. Please use setDB() first");
348: 
349:         $this->checkCurrent();
350: 
351:         if (($this->current['right'] - $this->current['left'] == 1))
352:         {
353:             $this->oSql->query('DELETE FROM ' . $this->tableName . ' 
354:                                 WHERE `' . $this->leftBound . '` = ' . $this->current['left']);
355: 
356:             $this->oSql->query('UPDATE ' . $this->tableName . ' 
357:                                 SET `' . $this->leftBound . '`=`' . $this->leftBound . '`-2
358:                                 WHERE `' . $this->leftBound . '`>=' . $this->current['left']);
359: 
360:             $this->oSql->query('UPDATE ' . $this->tableName . ' 
361:                                 SET `' . $this->rightBound . '`=`' . $this->rightBound . '`-2
362:                                 WHERE `' . $this->rightBound . '`>=' . $this->current['left']);
363:             return true;
364:         }
365:         else
366:         {
367:             return false;
368:         }
369:     }
370: 
371:     /**
372:      * Compte les parents de l'élément
373:      * @access public
374:      * @return int
375:      */
376:     public function countParents()
377:     {
378:         if (empty($this->oSql))
379:             throw new \RuntimeException("No db object set. Please use setDB() first");
380: 
381:         $this->checkCurrent();
382: 
383:         $parent = $this->oSql->query('SELECT COUNT(*) 
384:                                       FROM ' . $this->tableName . '
385:                                       WHERE `' . $this->leftBound . '`<' . $this->current['left'] . ' 
386:                                         AND `' . $this->rightBound . '` > ' . $this->current['right'])
387:                 ->fetchColumn(0);
388:         return $parent;
389:     }
390: 
391:     /**
392:      * Retourne les parents de l'élément
393:      *
394:      * @access public
395:      * @return array
396:      */
397:     public function getParents()
398:     {
399:         if (empty($this->oSql))
400:             throw new \RuntimeException("No db object set. Please use setDB() first");
401: 
402:         $this->checkCurrent();
403: 
404:         $parents = array();
405:         $parent = $this->oSql->query('SELECT ' . $this->id . ',' . $this->label . ' 
406:                                       FROM ' . $this->tableName . '
407:                                       WHERE `' . $this->leftBound . '`<' . $this->current['left'] . ' 
408:                                         AND `' . $this->rightBound . '` > ' . $this->current['right']);
409: 
410:         while ($result    = $parent->fetch(PDO::FETCH_ASSOC))
411:             $parents[] = $result;
412: 
413:         return $parents;
414:     }
415: 
416:     /**
417:      * Vérifie si un élément est séléctionné
418:      * @access private
419:      */
420:     private function checkCurrent()
421:     {
422:         if (empty($this->current))
423:         {
424:             throw new \Exception('Aucun élément courant n\'est défini');
425:         }
426:     }
427: 
428: }
429: 
430: ?>