Changeset 12

Timestamp:

Aug 29, 2019, 12:14:23 AM (5 years ago)

Author:

chronos

Message:

• Added: Summary page with total numbers of items in all categories.

Location:

trunk

Files:

• SQL/Structure.sql (modified) (6 diffs)
• index.php (modified) (3 diffs)

trunk/SQL/Structure.sql

@@ -3 +3 @@
 -- https://www.phpmyadmin.net/
 --
--- Počítač: localhost:3306
--- Vytvořeno: Pon 10. zář 2018, 00:54
--- Verze serveru: 5.7.23-0ubuntu0.18.04.1
--- Verze PHP: 7.1.15-0ubuntu0.17.10.1
+-- Host: localhost:3306
+-- Generation Time: Aug 29, 2019 at 12:02 AM
+-- Server version: 10.3.17-MariaDB-0ubuntu0.19.04.1
+-- PHP Version: 7.2.19-0ubuntu0.19.04.2
 
 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 */;
+
 --
--- Databáze: `teribear`
+-- Database: `teribear`
 --
+CREATE DATABASE IF NOT EXISTS `teribear` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
+USE `teribear`;
 
 -- --------------------------------------------------------
 
 --
--- Struktura tabulky `Runner`
+-- Table structure for table `Runner`
 --
 
 CREATE TABLE `Runner` (
   `Id` int(11) NOT NULL,
-  `Name` varchar(255) CHARACTER SET utf8 NOT NULL,
+  `Name` varchar(255) NOT NULL,
   `Gender` int(11) NOT NULL,
-  `Team` int(11) NOT NULL,
+  `Team` int(11) DEFAULT NULL,
   `ChipNumber` int(11) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
---
--- RELACE PRO TABULKU `Runner`:
---   `Team`
---       `Team` -> `Id`
---
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
 
 -- --------------------------------------------------------
 
 --
--- Struktura tabulky `RunnerStat`
+-- Table structure for table `RunnerStat`
 --
 
@@ -48 +50 @@
   `Rank` int(11) NOT NULL,
   `Money` int(11) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
---
--- RELACE PRO TABULKU `RunnerStat`:
---   `Runner`
---       `Runner` -> `Id`
---
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
 
 -- --------------------------------------------------------
 
 --
--- Struktura tabulky `Team`
+-- Table structure for table `Team`
 --
 
 CREATE TABLE `Team` (
   `Id` int(11) NOT NULL,
-  `Name` varchar(255) CHARACTER SET utf8 NOT NULL,
-  `WebId` int(11) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+  `Name` varchar(255) NOT NULL,
+  `WebId` int(11) NOT NULL,
+  `IsFamily` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
+
+-- --------------------------------------------------------
 
 --
--- RELACE PRO TABULKU `Team`:
+-- Table structure for table `TeamStat`
+--
+
+CREATE TABLE `TeamStat` (
+  `Id` int(11) NOT NULL,
+  `Time` datetime NOT NULL,
+  `Team` int(11) NOT NULL,
+  `Distance` decimal(10,1) NOT NULL,
+  `Rank` int(11) NOT NULL,
+  `Money` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
+
+--
+-- Indexes for dumped tables
 --
 
 --
--- Klíče pro exportované tabulky
---
-
---
--- Klíče pro tabulku `Runner`
+-- Indexes for table `Runner`
 --
 ALTER TABLE `Runner`
@@ -84 +92 @@
 
 --
--- Klíče pro tabulku `RunnerStat`
+-- Indexes for table `RunnerStat`
 --
 ALTER TABLE `RunnerStat`
@@ -91 +99 @@
 
 --
--- Klíče pro tabulku `Team`
+-- Indexes for table `Team`
 --
 ALTER TABLE `Team`
@@ -97 +105 @@
 
 --
--- AUTO_INCREMENT pro tabulky
+-- Indexes for table `TeamStat`
+--
+ALTER TABLE `TeamStat`
+  ADD PRIMARY KEY (`Id`),
+  ADD KEY `Team` (`Team`);
+
+--
+-- AUTO_INCREMENT for dumped tables
 --
 
 --
--- AUTO_INCREMENT pro tabulku `Runner`
+-- AUTO_INCREMENT for table `Runner`
 --
 ALTER TABLE `Runner`
-  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=59;
+  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18158;
 --
--- AUTO_INCREMENT pro tabulku `RunnerStat`
+-- AUTO_INCREMENT for table `RunnerStat`
 --
 ALTER TABLE `RunnerStat`
-  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41;
+  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=136448;
 --
--- AUTO_INCREMENT pro tabulku `Team`
+-- AUTO_INCREMENT for table `Team`
 --
 ALTER TABLE `Team`
-  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
+  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12718;
 --
--- Omezení pro exportované tabulky
+-- AUTO_INCREMENT for table `TeamStat`
+--
+ALTER TABLE `TeamStat`
+  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4088;
+--
+-- Constraints for dumped tables
 --
 
 --
--- Omezení pro tabulku `Runner`
+-- Constraints for table `Runner`
 --
 ALTER TABLE `Runner`
@@ -126 +146 @@
 
 --
--- Omezení pro tabulku `RunnerStat`
+-- Constraints for table `RunnerStat`
 --
 ALTER TABLE `RunnerStat`
   ADD CONSTRAINT `RunnerStat_ibfk_1` FOREIGN KEY (`Runner`) REFERENCES `Runner` (`Id`);
+
+--
+-- Constraints for table `TeamStat`
+--
+ALTER TABLE `TeamStat`
+  ADD CONSTRAINT `TeamStat_ibfk_1` FOREIGN KEY (`Team`) REFERENCES `Team` (`Id`);
+
+/*!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 */;

trunk/index.php

@@ -36 +36 @@
   {
     $Output = '<div>'.
+      '<a href="'.$this->Link('/').'">Summary</a> '.
       '<a href="'.$this->Link('/runners').'">Runners</a> '.
       '<a href="'.$this->Link('/teams').'">Teams</a> '.
@@ -592 +593 @@
   function ShowMain()
   {
-    return "";
+    $Output = '';
+    $DbResult = $this->Database->select('Runner', 'COUNT(*) AS Total');
+    $DbRow = $DbResult->fetch_assoc();
+    $RunnersCount = $DbRow['Total'];
+
+    $Output = '';
+    $DbResult = $this->Database->select('Runner', 'COUNT(*) AS Total', 'Gender=1');
+    $DbRow = $DbResult->fetch_assoc();
+    $MenCount = $DbRow['Total'];
+
+    $Output = '';
+    $DbResult = $this->Database->select('Runner', 'COUNT(*) AS Total', 'Gender=2');
+    $DbRow = $DbResult->fetch_assoc();
+    $WomenCount = $DbRow['Total'];
+
+    $Output = '';
+    $DbResult = $this->Database->select('Runner', 'COUNT(*) AS Total', 'Gender=3');
+    $DbRow = $DbResult->fetch_assoc();
+    $KidsCount = $DbRow['Total'];
+
+    $DbResult = $this->Database->select('Team', 'COUNT(*) AS Total', 'IsFamily=0');
+    $DbRow = $DbResult->fetch_assoc();
+    $TeamsCount = $DbRow['Total'];
+
+    $DbResult = $this->Database->select('Team', 'COUNT(*) AS Total', 'IsFamily=1');
+    $DbRow = $DbResult->fetch_assoc();
+    $FamiliesCount = $DbRow['Total'];
+    
+    $Output .= '<div class="page-title">Summary</div>';
+    $Output .= '<table class="WideTable">';
+    $Output .= '<tr><th>Category</th><th>Total count</th></tr>';
+    $Output .= '<tr><td>Runners</td><td>'.$RunnersCount.'</td></tr>';
+    $Output .= '<tr><td>Men</td><td>'.$MenCount.'</td></tr>';
+    $Output .= '<tr><td>Women</td><td>'.$WomenCount.'</td></tr>';
+    $Output .= '<tr><td>Kids</td><td>'.$KidsCount.'</td></tr>';
+    $Output .= '<tr><td>Teams</td><td>'.$TeamsCount.'</td></tr>';
+    $Output .= '<tr><td>Families</td><td>'.$FamiliesCount.'</td></tr>';
+    $Output .= '</table>';
+
+    return $Output;
   }
 
@@ -632 +672 @@
     {
       if($this->PathItems[0] == 'sync') $Output .= $this->ShowSync();
-      else if($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
+      //else if($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
       else if($this->PathItems[0] == 'runner') $Output .= $this->ShowRunner();
       else if($this->PathItems[0] == 'runners') $Output .= $this->ShowRunners();