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