From 170e4fd777430062d64b89b93ed466dcf5a2b6bd Mon Sep 17 00:00:00 2001
From: Banana <banana@optimus.de>
Date: Sun, 25 Dec 2016 21:40:52 +0100
Subject: [PATCH] installation docu and dashboard view

---
 installation/README              |   8 ++
 installation/insipid-edit-me.sql | 162 +++++++++++++++++++++++++++++++
 webroot/lib/management.class.php |  24 +++--
 webroot/view/home.php            |   6 +-
 4 files changed, 190 insertions(+), 10 deletions(-)
 create mode 100644 installation/README
 create mode 100644 installation/insipid-edit-me.sql

diff --git a/installation/README b/installation/README
new file mode 100644
index 0000000..c78ac88
--- /dev/null
+++ b/installation/README
@@ -0,0 +1,8 @@
+The inspid-edit-me.sql NEEDS to be edited.
+Open the file and make a search and replace:
+
+search for: `#REPLACEME#
+replace with: `tableprefix
+
+The value tableprefix has to match with the value for DB_PREFIX in config.php
+
diff --git a/installation/insipid-edit-me.sql b/installation/insipid-edit-me.sql
new file mode 100644
index 0000000..3c156c8
--- /dev/null
+++ b/installation/insipid-edit-me.sql
@@ -0,0 +1,162 @@
+
+SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
+SET time_zone = "+00:00";
+
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8mb4 */;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `#REPLACEME#_category`
+--
+
+DROP TABLE IF EXISTS `#REPLACEME#_category`;
+CREATE TABLE `#REPLACEME#_category` (
+  `id` int(10) NOT NULL,
+  `name` varchar(128) COLLATE utf8mb4_bin NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `#REPLACEME#_categoryrelation`
+--
+
+DROP TABLE IF EXISTS `#REPLACEME#_categoryrelation`;
+CREATE TABLE `#REPLACEME#_categoryrelation` (
+  `linkid` int(10) NOT NULL,
+  `categoryid` int(10) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT;
+
+-- --------------------------------------------------------
+
+--
+-- Stand-in structure for view `#REPLACEME#_combined`
+-- (See below for the actual view)
+--
+DROP VIEW IF EXISTS `#REPLACEME#_combined`;
+CREATE TABLE `#REPLACEME#_combined` (
+`id` int(10)
+,`link` mediumtext
+,`created` datetime
+,`status` int(2)
+,`description` varchar(255)
+,`title` varchar(255)
+,`image` varchar(255)
+,`hash` char(32)
+,`tag` varchar(64)
+,`category` varchar(128)
+);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `#REPLACEME#_link`
+--
+
+DROP TABLE IF EXISTS `#REPLACEME#_link`;
+CREATE TABLE `#REPLACEME#_link` (
+  `id` int(10) NOT NULL,
+  `link` mediumtext COLLATE utf8mb4_bin NOT NULL,
+  `created` datetime NOT NULL,
+  `status` int(2) NOT NULL,
+  `description` varchar(255) COLLATE utf8mb4_bin NOT NULL,
+  `title` varchar(255) COLLATE utf8mb4_bin NOT NULL,
+  `image` varchar(255) COLLATE utf8mb4_bin NOT NULL,
+  `hash` char(32) COLLATE utf8mb4_bin NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `#REPLACEME#_tag`
+--
+
+DROP TABLE IF EXISTS `#REPLACEME#_tag`;
+CREATE TABLE `#REPLACEME#_tag` (
+  `id` int(10) NOT NULL,
+  `name` varchar(64) COLLATE utf8mb4_bin NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `#REPLACEME#_tagrelation`
+--
+
+DROP TABLE IF EXISTS `#REPLACEME#_tagrelation`;
+CREATE TABLE `#REPLACEME#_tagrelation` (
+  `linkid` int(10) NOT NULL,
+  `tagid` int(10) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT;
+
+-- --------------------------------------------------------
+
+--
+-- Structure for view `#REPLACEME#_combined`
+--
+DROP TABLE IF EXISTS `#REPLACEME#_combined`;
+
+CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `#REPLACEME#_combined`  AS  select `#REPLACEME#_link`.`id` AS `id`,`#REPLACEME#_link`.`link` AS `link`,`#REPLACEME#_link`.`created` AS `created`,`#REPLACEME#_link`.`status` AS `status`,`#REPLACEME#_link`.`description` AS `description`,`#REPLACEME#_link`.`title` AS `title`,`#REPLACEME#_link`.`image` AS `image`,`#REPLACEME#_link`.`hash` AS `hash`,`#REPLACEME#_tag`.`name` AS `tag`,`#REPLACEME#_category`.`name` AS `category` from ((((`#REPLACEME#_link` left join `#REPLACEME#_tagrelation` on((`#REPLACEME#_tagrelation`.`linkid` = `#REPLACEME#_link`.`id`))) left join `#REPLACEME#_tag` on((`#REPLACEME#_tag`.`id` = `#REPLACEME#_tagrelation`.`tagid`))) left join `#REPLACEME#_categoryrelation` on((`#REPLACEME#_categoryrelation`.`linkid` = `#REPLACEME#_link`.`id`))) left join `#REPLACEME#_category` on((`#REPLACEME#_category`.`id` = `#REPLACEME#_categoryrelation`.`categoryid`))) ;
+
+--
+-- Indexes for dumped tables
+--
+
+--
+-- Indexes for table `#REPLACEME#_category`
+--
+ALTER TABLE `#REPLACEME#_category`
+  ADD PRIMARY KEY (`id`);
+
+--
+-- Indexes for table `#REPLACEME#_categoryrelation`
+--
+ALTER TABLE `#REPLACEME#_categoryrelation`
+  ADD UNIQUE KEY `linkid` (`linkid`,`categoryid`);
+
+--
+-- Indexes for table `#REPLACEME#_link`
+--
+ALTER TABLE `#REPLACEME#_link`
+  ADD PRIMARY KEY (`id`),
+  ADD UNIQUE KEY `hash` (`hash`);
+
+--
+-- Indexes for table `#REPLACEME#_tag`
+--
+ALTER TABLE `#REPLACEME#_tag`
+  ADD PRIMARY KEY (`id`);
+
+--
+-- Indexes for table `#REPLACEME#_tagrelation`
+--
+ALTER TABLE `#REPLACEME#_tagrelation`
+  ADD UNIQUE KEY `linkid` (`linkid`,`tagid`);
+
+--
+-- AUTO_INCREMENT for dumped tables
+--
+
+--
+-- AUTO_INCREMENT for table `#REPLACEME#_category`
+--
+ALTER TABLE `#REPLACEME#_category`
+  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
+--
+-- AUTO_INCREMENT for table `#REPLACEME#_link`
+--
+ALTER TABLE `#REPLACEME#_link`
+  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
+--
+-- AUTO_INCREMENT for table `#REPLACEME#_tag`
+--
+ALTER TABLE `#REPLACEME#_tag`
+  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
diff --git a/webroot/lib/management.class.php b/webroot/lib/management.class.php
index b8ba7e5..4582475 100644
--- a/webroot/lib/management.class.php
+++ b/webroot/lib/management.class.php
@@ -98,15 +98,25 @@ class Management {
      */
     public function categoriesByDateAdded() {
         $ret = array();
-        $queryStr = "SELECT category FROM `".DB_PREFIX."_combined`
-                        WHERE `status` = 2
-                        GROUP BY category
-                        ORDER BY created DESC";
-        $query = $this->DB->query($queryStr);
-        if(!empty($query) && $query->num_rows > 0) {
-            $ret = $query->fetch_all(MYSQLI_ASSOC);
+
+        $categories = $this->categories();
+        foreach($categories as $cat) {
+            $queryStr = "SELECT insipid_category.name, insipid_link.created
+                            FROM `insipid_category`
+                            LEFT JOIN insipid_categoryrelation ON insipid_categoryrelation.categoryid = insipid_category.id
+                            LEFT JOIN insipid_link ON insipid_link.id = insipid_categoryrelation.linkid
+                            WHERE insipid_category.id = '".$this->DB->real_escape_string($cat['id'])."'
+                            ORDER BY insipid_link.created DESC
+                            LIMIT 1";
+            $query = $this->DB->query($queryStr);
+            if(!empty($query) && $query->num_rows > 0) {
+                $result = $query->fetch_assoc();
+                $ret[$result['name']] = $result['created'];
+            }
         }
 
+        arsort($ret);
+
         return $ret;
     }
 
diff --git a/webroot/view/home.php b/webroot/view/home.php
index ed6e41e..76b59b7 100644
--- a/webroot/view/home.php
+++ b/webroot/view/home.php
@@ -202,13 +202,13 @@
 	</div>
 <?php
     if(!empty($orderedCategories)) {
-        foreach ($orderedCategories as $cat) {
-            $links = $Management->linksByCategoryString($cat['category']);
+        foreach ($orderedCategories as $cat=>$date) {
+            $links = $Management->linksByCategoryString($cat);
 ?>
 	<div class="column">
 		<div class="card" data-equalizer-watch>
 			<div class="card-divider">
-				<h4><?php echo $cat['category']; ?></h4>
+				<h4><?php echo $cat; ?></h4>
 			</div>
 			<img src="assets/img/generic/rectangle-1.jpg">
 			<div class="card-section">
-- 
2.39.5