Created
November 20, 2010 18:08
-
-
Save david-sabata/708020 to your computer and use it in GitHub Desktop.
Neomezeně zanořitelné kategorie pomocí [parent], [order], [level]
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * Model kategorii zbozi (pro administraci) | |
| * @author David Šabata | |
| */ | |
| class AdminGoodsCategories extends Object | |
| { | |
| const TABLE = 'categories'; | |
| const GOODS_TABLE = 'goods'; | |
| private $menuid = 'main'; // zatim pouze jedine, do budoucna k odliseni vic nezavislych stromu kategorii | |
| /** | |
| * Vraci pole (klic=>hodnota) pro predani selectu pri tvorbe formu | |
| * @return array | |
| */ | |
| public function getSelectOptions() | |
| { | |
| $ret = Array(); // vracena hodnota | |
| $result = dibi::query(" | |
| SELECT [id],[title],[level] | |
| FROM [".self::TABLE."] | |
| WHERE [title]!='' | |
| ORDER BY [order] ASC | |
| "); | |
| foreach ($result as $row) { | |
| $ret[$row['id']] = str_repeat('- ',$row['level']) . $row['title']; | |
| } | |
| return $ret; | |
| } | |
| /** | |
| * Vraci dataSource pro pouziti dataGridu (krome 'draftu') | |
| * @return dibi dataSource | |
| */ | |
| public function getDataSource() | |
| { | |
| return dibi::dataSource(" | |
| SELECT * FROM [".self::TABLE."] WHERE [title]!='' ORDER BY [order] ASC" | |
| ); | |
| } | |
| /** | |
| * Vytvori novy zaznam v tabulce s defaultnima hodnotama | |
| * a vrati jeho ID | |
| * @return int id nove vytvoreneho zaznamu | |
| */ | |
| public function addNewItem() { | |
| dibi::query("INSERT INTO [".self::TABLE."] SET [id]=''"); | |
| return dibi::insertId(); | |
| } | |
| /** | |
| * Vraci pole hodnot pro dany zaznam pro pouziti formulare | |
| * @return array defaultValues | |
| */ | |
| public function getFormValues($id) { | |
| $result = dibi::query("SELECT * FROM [".self::TABLE."] WHERE id=%i", $id); | |
| if (count($result)==1) { | |
| $d = $result->fetch(); | |
| return $d; | |
| } else | |
| throw new Exception('Položka '.$id.' neexistuje'); | |
| } | |
| /** | |
| * Vraci pole titulek=>link vsech clanku, razeno abecedne | |
| * @param bool vratit link vcetne presenteru? | |
| * @return array | |
| */ | |
| public static function getLinksAndTitles($fullLink = false) { | |
| $res = dibi::query(" | |
| SELECT [title],%if", $fullLink, "CONCAT(':Front:GoodsCategories:default, ', [link]) AS %end [link] | |
| FROM [".self::TABLE."] | |
| WHERE [link]!=%s", '', " | |
| ORDER BY [title] ASC | |
| "); | |
| return $res->fetchPairs('link', 'title'); | |
| } | |
| /** | |
| * Smaze v databazi docasny zaznam (vytvoreny, ale neulozeny) s danym ID | |
| * @param id cislo docasneho zaznamu ke smazani (pokud docasny neni, nesmaze se) | |
| * @return void | |
| */ | |
| public function deleteIfTemp($id) { | |
| dibi::query(" | |
| DELETE FROM [".self::TABLE."] WHERE [id]=%i", $id, " AND [title]='' LIMIT 1 | |
| "); | |
| } | |
| /** | |
| * Vraci [order] posledniho ditete (libovolne hluboko zanoreneho) pro daneho rodice | |
| * nebo vlastni [order] pokud zadne deti nema | |
| * | |
| * @param int $parent id rodice | |
| * @return int | |
| */ | |
| private function getLastChildOrder($parent) { | |
| // parent = 0 je virtualni korenova polozka, vracime posledni pozici v tabulce | |
| if ($parent == 0) | |
| return dibi::query(" | |
| SELECT MAX([order]) | |
| FROM [".self::TABLE."] | |
| WHERE [menuid]=%s", $this->menuid | |
| )->fetchSingle(); | |
| // vyhledame rodice | |
| $p = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [id]=%i", $parent)->fetch(); | |
| $nextSibling = dibi::query(" | |
| SELECT [order] | |
| FROM [".self::TABLE."] | |
| WHERE [menuid]=%s", $this->menuid, "AND [level]<=%i", $p->level, "AND [order]>%i", $p->order, " | |
| ORDER BY [order] ASC | |
| LIMIT 1 | |
| ")->fetch(); | |
| if ($nextSibling) { | |
| return ($nextSibling->order - 1); | |
| } else { | |
| return dibi::query(" | |
| SELECT MAX([order]) | |
| FROM [".self::TABLE."] | |
| WHERE [menuid]=%s", $this->menuid | |
| )->fetchSingle(); | |
| } | |
| } | |
| /** | |
| * Vraci pole ID potomku daneho rodice | |
| * | |
| * @param int $parent id | |
| */ | |
| private function getKidsIds($parent) { | |
| $pOrder = dibi::query("SELECT [order] FROM [".self::TABLE."] WHERE [id]=%i", $parent)->fetchSingle(); | |
| $kids = array(); | |
| $lastKidOrder = $this->getLastChildOrder($parent); | |
| // pokud je order posledniho ditete stejny jako rodicuv, je jasne ze deti nema | |
| if ($lastKidOrder == $pOrder) | |
| return $kids; | |
| $res = dibi::query(" | |
| SELECT [id] | |
| FROM [".self::TABLE."] | |
| WHERE [menuid]=%s", $this->menuid, " AND [order]>%i", $pOrder, "AND [order]<=%i", $lastKidOrder | |
| ); | |
| foreach ($res as $row) | |
| $kids[] = $row['id']; | |
| return $kids; | |
| } | |
| /** | |
| * Saves data to the specified item. On error returns the message. | |
| * @return TRUE|string | |
| */ | |
| public function saveItem($id, $data) { | |
| $err = array(); | |
| // nacist puvodni polozku | |
| $oldItem = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [id]=%i", $id)->fetch(); | |
| // polozku nelze zanorit samu pod sebe | |
| if ($data['parent'] == $id) | |
| return 'Položku nelze zanořit samu pod sebe'; | |
| // nelogicke zanoreni - pro nove vkladane nekontrolovat | |
| if ($oldItem->parent > -1 && array_search($data['parent'], $this->getKidsIds($id))!==false) | |
| return 'Pod tuto položku nelze zanořit. Zvolte prosím jinou.'; | |
| // pokud se zmenil rodic, cimz i poradi, prepocitat | |
| $oldParent = dibi::query("SELECT [parent] FROM [".self::TABLE."] WHERE [id]=%i", $id)->fetchSingle(); | |
| if ($oldParent != $data['parent']) { | |
| // potomky muze mit pouze polozka ktera uz v tabulce byla | |
| if ($oldItem['parent'] > -1) { | |
| // najit posledniho potomka | |
| $lastKid = $this->getLastChildOrder($id); | |
| // nacist vsechny polozky zanorene pod upravovanou polozkou | |
| $oldKids = dibi::query(" | |
| SELECT * | |
| FROM [".self::TABLE."] | |
| WHERE [menuid]=%s", $this->menuid, "AND [order]>%i", $oldItem->order, " | |
| AND [order]<=%i", $lastKid, " | |
| ORDER BY [order] | |
| ")->fetchAll(); | |
| // ted upravovanou i deti vyjmeme - presuneme do jineho pomocneho menu | |
| dibi::query(" | |
| UPDATE [".self::TABLE."] | |
| SET [menuid]=%s", 'tmpMenu', " | |
| WHERE [menuid]=%s", $this->menuid, " AND [order]>=%i", $oldItem->order, "AND [order]<=%i", $lastKid | |
| ); | |
| // a posuneme vse pod nima nahoru | |
| dibi::query(" | |
| UPDATE [".self::TABLE."] | |
| SET [order]=[order]-%i", (count($oldKids)+1), " | |
| WHERE [menuid]=%s", $this->menuid, "AND [order]>%i", $oldItem->order | |
| ); | |
| // ------------------------------------------------------------------------ | |
| // ted by melo byt menu v regulernim stavu a vyjmute polozky mame bokem | |
| // bude potreba je precislovat, prelevelovat a vrazit zpatky do menu | |
| // ------------------------------------------------------------------------ | |
| /* | |
| echo 'presouvane polozky<br />'; | |
| echo '<table border="1" style="border-collapse:collapse" cellpadding="5"><tr><th>ID</th><th>Parent</th><th>Order</th><th>Level</th><th>Title</th></tr>'; | |
| $res = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [menuid]=%s", 'tmpMenu', "ORDER BY [order] ASC"); | |
| foreach ($res as $row) | |
| echo '<tr><td>'.$row['id'].'</td><td>'.$row['parent'].'</td><td>'.$row['order'].'</td><td>'.$row['level'].'</td><td>'.$row['title'].'</td></tr>'; | |
| echo '</table><br />'; | |
| echo 'tabulka po vyjmuti presouvanych polozek<br />'; | |
| echo '<table border="1" style="border-collapse:collapse" cellpadding="5"><tr><th>ID</th><th>Parent</th><th>Order</th><th>Level</th><th>Title</th></tr>'; | |
| $res = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [menuid]=%s", $this->menuid, "ORDER BY [order] ASC"); | |
| foreach ($res as $row) | |
| echo '<tr><td>'.$row['id'].'</td><td>'.$row['parent'].'</td><td>'.$row['order'].'</td><td>'.$row['level'].'</td><td>'.$row['title'].'</td></tr>'; | |
| echo '</table><br />'; | |
| */ | |
| // podle noveho rodice dopocitame uroven zanoreni | |
| $level = dibi::query("SELECT [level] FROM [".self::TABLE."] WHERE [id]=%i", $data['parent'])->fetchSingle(); | |
| $data['level'] = $level + 1; | |
| //echo 'novy level presouvane polozky '.$data['level'].'<br />'; | |
| // najit posledniho potomka rodice, jako chceme mit ted | |
| $lastSibling = $this->getLastChildOrder($data['parent']); | |
| //echo 'posledni potomek noveho rodice '.$lastSibling.'<br />'; | |
| $data['order'] = $lastSibling + 1; // zaradit za posledni dite rodice, resp. za nej, pokud zadne nema | |
| //echo 'nova pozice presouvane polozky '.$data['order'].'<br />'; | |
| // odsunout polozky s vyssim poradovym cislem o 1+pocet deti | |
| dibi::query(" | |
| UPDATE [".self::TABLE."] | |
| SET [order]=[order]+%i", (count($oldKids)+1), " | |
| WHERE [order]>=%i", $data['order'], "AND [menuid]=%s", $this->menuid | |
| ); | |
| //echo 'posouvam polozky na pozici '.$data['order'].' a vyssi o '.(count($oldKids)+1).' pozic nize<br />'; | |
| // precislovat deti presouvane polozky - jsou porad v tmpMenu | |
| dibi::query(" | |
| UPDATE [".self::TABLE."] | |
| SET [level]=[level]+%i", ($data['level'] - $oldItem->level), ", | |
| [order]=[order]+%i", ($data['order'] - $oldItem->order), ", | |
| [menuid]=%s", $oldItem->menuid, " | |
| WHERE [menuid]=%s", 'tmpMenu', "AND [order]>%i", $oldItem->order | |
| ); | |
| //echo 'vsem detem nastavuju order += '.($data['order'] - $oldItem->order).' a level += '.($data['level'] - $oldItem->level).'<br />'; | |
| $data['menuid'] = $oldItem->menuid; | |
| } // oldItem->parent > -1 | |
| else { | |
| // jde o novou polozku, neni potreba resit deti | |
| // podle noveho rodice dopocitame uroven zanoreni | |
| $level = dibi::query(" | |
| SELECT [level] | |
| FROM [".self::TABLE."] | |
| WHERE [id]=%i", $data['parent'] | |
| )->fetchSingle(); | |
| $data['level'] = $level + 1; | |
| // najdeme posledni dite noveho rodice a dopocteme nove poradi | |
| $lastKid = $this->getLastChildOrder($data['parent']); | |
| $data['order'] = $lastKid + 1; | |
| // a posuneme vsechny polozky o pozici dolu | |
| dibi::query(" | |
| UPDATE [".self::TABLE."] | |
| SET [order]=[order]+1 | |
| WHERE [menuid]=%s", $this->menuid, "AND [order]>%i", $lastKid, " | |
| "); | |
| } | |
| } // prepocitani, zmenil se parent | |
| // vytvorit link jestli neni | |
| if ($data['link']=='') | |
| $data['link'] = String::webalize($data['title']); | |
| // zkontrolovat duplicitu linku | |
| if (dibi::fetchSingle("SELECT COUNT(*) FROM [".self::TABLE."] WHERE [link]=%s", $data['link'], "AND [id]!=%i", $id)) | |
| $err[] = 'Tento odkaz již vede na jinou kategorii, zvolte prosím jiný'; | |
| // ulozit zmeny | |
| dibi::query("UPDATE [".self::TABLE."] SET ", $data, "WHERE [id]=%i", $id, "LIMIT 1"); | |
| // a vratit true/chyba | |
| return count($err)>0 ? implode('<br />', $err) : true; | |
| } | |
| /** | |
| * Smaze jeden zaznam na zaklade id | |
| */ | |
| public function deleteItem($id) { | |
| // neni polozka chranena proti smazani? muze ji odstranit jen developer - viz. config.ini | |
| $protected = dibi::query("SELECT [protected] FROM [".self::TABLE."] WHERE [id]=%i", $id)->fetchSingle(); | |
| if ($protected == 1 && Environment::getUser()->isDeveloper() == false) | |
| return 'Tato položka je součástí designu webu a nelze ji odstranit'; | |
| // zkontrolovat jestli v kategorii nejsou nejake polozky | |
| $items = dibi::query("SELECT COUNT(*) FROM [".self::GOODS_TABLE."] WHERE [category]=%i", $id)->fetchSingle(); | |
| if ($items > 0) | |
| return 'Do této kategorie je stále zařazeno zboží'; | |
| // zapamatovat si vsechny potomky odstranovane polozky (kvuli posunu) | |
| $kids = dibi::query("SELECT [id] FROM [".self::TABLE."] WHERE [parent]=%i", $id)->fetchAll(); | |
| // zapamatovat si poradi, at muzeme ostatni posunout | |
| $order = dibi::query("SELECT [order] FROM [".self::TABLE."] WHERE [id]=%i", $id)->fetchSingle(); | |
| // smazat | |
| dibi::query("DELETE FROM [".self::TABLE."] WHERE [id]=%i", $id, "LIMIT 1"); | |
| // posunout zbyle o pozici nahoru, at se zachova poradi | |
| $shift = 1 + count($kids); | |
| dibi::query("UPDATE [".self::TABLE."] SET [order]=[order]-%i", $shift, "WHERE [order]>%i", $order); | |
| // presunout sirotky na konec a nastavit jim rodice na 0 | |
| $max = dibi::query("SELECT MAX([order]) FROM [".self::TABLE."] LIMIT 1")->fetchSingle(); | |
| foreach ($kids as $kid) { | |
| $max++; | |
| dibi::query("UPDATE [".self::TABLE."] SET [order]=%i", $max, ", [parent]=0, [level]=1 WHERE [id]=%i", $kid['id']); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Moves the item one step up or down | |
| * | |
| * @param int $ord item order | |
| * @param string $dir [up|down] | |
| * @return void | |
| */ | |
| public function positionMove($ord, $dir) { | |
| // get the item | |
| $item = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [order]=%i", $ord)->fetch(); | |
| // get the boundaries | |
| $limit = dibi::query("SELECT MAX([order]) AS [max], MIN([order]) AS [min] FROM [".self::TABLE."]")->fetch(); | |
| // cant move the first item up or the last item down | |
| if ($item['order'] == $limit['max'] && $dir == 'down' || $item['order'] == $limit['min'] && $dir == 'up') | |
| return; | |
| if ($dir == 'up') { | |
| // predchozi polozka na stejne urovni; mezi ni a posouvanou muzou byt zanorene polozky | |
| $prevItem = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [level]=%i", $item['level'], " AND [parent]=%i", $item['parent'], " AND [order]<%i", $item['order'], " ORDER BY [order] DESC LIMIT 1")->fetch(); | |
| // polozka je (pro svoji uroven) na zacatku tabulky | |
| if (!$prevItem) | |
| return false; | |
| // polozka za posouvanou na stejne nebo vyssi urovni - podle ni pozname kde konci potomci posouvane polozky | |
| $nextItem = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [level]<=%i", $item['level'], "AND [order]>%i", $item['order'], "ORDER BY [order] ASC LIMIT 1")->fetch(); | |
| // konec tabulky? simulujeme order nextItem | |
| if (!$nextItem) | |
| $nextItem = array('order'=>$limit['max']+1); | |
| // polozky ktere se posunou nahoru, je potreba si je zapamatovat | |
| $shiftUp = dibi::query("SELECT [id] FROM [".self::TABLE."] WHERE [order]>=%i", $item['order'], "AND [order]<%i", $nextItem['order'])->fetchAll(); | |
| $shiftUpIds = array(); | |
| foreach ($shiftUp as $tmp) | |
| $shiftUpIds[] = $tmp->id; | |
| $shiftDown = dibi::query("SELECT [id] FROM [".self::TABLE."] WHERE [order]>=%i", $prevItem['order'], "AND [order]<%i", $item['order'])->fetchAll(); | |
| $shiftDownIds = array(); | |
| foreach ($shiftDown as $tmp) | |
| $shiftDownIds[] = $tmp->id; | |
| dibi::query("UPDATE [".self::TABLE."] SET [order]=[order]+%i", count($shiftUpIds), "WHERE [id]=" . implode(" OR [id]=", $shiftDownIds)); | |
| dibi::query("UPDATE [".self::TABLE."] SET [order]=[order]-%i", count($shiftDownIds), "WHERE [id]=" . implode(" OR [id]=", $shiftUpIds)); | |
| } | |
| if ($dir == 'down') { | |
| // nasledujici polozka na stejne urovni; mezi ni a posouvanou muzou byt zanorene polozky | |
| $nextItem = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [level]=%i", $item['level'], " AND [parent]=%i", $item['parent'], " AND [order]>%i", $item['order'], " ORDER BY [order] ASC LIMIT 1")->fetch(); | |
| // polozka je (pro svoji uroven) na konci tabulky | |
| if (!$nextItem) | |
| return false; | |
| // polozka za nasledujici, stejne nebo vyssi urovne - podle ni pozname kde konci potomci nasledujici polozky | |
| $lastItem = dibi::query("SELECT * FROM [".self::TABLE."] WHERE [level]<=%i", $item['level'], "AND [order]>%i", $nextItem['order'], "ORDER BY [order] ASC LIMIT 1")->fetch(); | |
| // konec tabulky? simulujeme order lastItem | |
| if (!$lastItem) | |
| $lastItem = array('order'=>$limit['max']+1); | |
| // polozky ktere se posunou nahoru, je potreba si je zapamatovat | |
| $shiftUp = dibi::query("SELECT [id] FROM [".self::TABLE."] WHERE [order]>=%i", $nextItem['order'], "AND [order]<%i", $lastItem['order'])->fetchAll(); | |
| $shiftUpIds = array(); | |
| foreach ($shiftUp as $tmp) | |
| $shiftUpIds[] = $tmp->id; | |
| $shiftDown = dibi::query("SELECT [id] FROM [".self::TABLE."] WHERE [order]>=%i", $item['order'], "AND [order]<%i", $nextItem['order'])->fetchAll(); | |
| $shiftDownIds = array(); | |
| foreach ($shiftDown as $tmp) | |
| $shiftDownIds[] = $tmp->id; | |
| dibi::query("UPDATE [".self::TABLE."] SET [order]=[order]+%i", count($shiftUpIds), "WHERE [id]=" . implode(" OR [id]=", $shiftDownIds)); | |
| dibi::query("UPDATE [".self::TABLE."] SET [order]=[order]-%i", count($shiftDownIds), "WHERE [id]=" . implode(" OR [id]=", $shiftUpIds)); | |
| } | |
| return true; | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment