Beer-o-Meter

Beer-o-Meter is a website where several teams compete against each other. It's all about drinking. In a block diagram, the number of drinks drunk by each team is compared and displayed in a bar chart.

Installation

Prerequisites:

• Docker Engine Installed on a Linux-System or Docker Desktop installed

Docker Compose

1. Deploy via following docker-compose.yaml file.

services:
  php-httpd:
    build:
      context: .
      dockerfile_inline: "FROM php:apache\nRUN apt update; apt upgrade;\nRUN docker-php-ext-install mysqli"
    restart: always
    depends_on:
      - mariadb
    ports:
      - 8080:80
    volumes:
      - ./html/:/var/www/html

  mariadb:
    image: mariadb:latest
    restart: always
    volumes:
      - ./mariadb:/var/lib/mysql
    environment:
      TZ: "Europe/Berlin"
      MYSQL_ALLOW_EMPTY_PASSWORD: "no"
      MYSQL_ROOT_PASSWORD: "GSMWGpERHUjUeGHZvQ8J"
    configs:
      - source: init_sql
        target: /docker-entrypoint-initdb.d/1.sql

configs:
  init_sql:
    content: |
      SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
      START TRANSACTION;
      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 */;
      
      CREATE DATABASE IF NOT EXISTS `beerometer_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
      USE `beerometer_db`;

      CREATE TABLE `beer` (
        `b_ID` int(10) UNSIGNED NOT NULL,
        `p_ID` int(10) UNSIGNED NOT NULL,
        `b_timestamp` timestamp NOT NULL DEFAULT current_timestamp()
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

      CREATE TABLE `options` (
        `o_ID` int(10) UNSIGNED NOT NULL,
        `o_value` varchar(100) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

      INSERT INTO `options` (`o_ID`, `o_value`) VALUES
      (1, 'oneToOne');

      CREATE TABLE `person` (
        `p_ID` int(10) UNSIGNED NOT NULL,
        `t_ID` int(10) UNSIGNED NOT NULL,
        `p_name` varchar(100) NOT NULL,
        `p_firstname` varchar(100) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

      CREATE TABLE `team` (
        `t_ID` int(10) UNSIGNED NOT NULL,
        `t_name` varchar(200) NOT NULL DEFAULT 'Teamname'
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

      INSERT INTO `team` (`t_ID`, `t_name`) VALUES
      (1, '-');

      ALTER TABLE `beer`
        ADD PRIMARY KEY (`b_ID`),
        ADD KEY `p_ID` (`p_ID`);

      ALTER TABLE `options`
        ADD PRIMARY KEY (`o_ID`);

      ALTER TABLE `person`
        ADD PRIMARY KEY (`p_ID`),
        ADD KEY `t_ID` (`t_ID`);

      ALTER TABLE `team`
        ADD PRIMARY KEY (`t_ID`);

      ALTER TABLE `beer`
        MODIFY `b_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

      ALTER TABLE `options`
        MODIFY `o_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

      ALTER TABLE `person`
        MODIFY `p_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

      ALTER TABLE `team`
        MODIFY `t_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

      ALTER TABLE `beer`
        ADD CONSTRAINT `beer_ibfk_1` FOREIGN KEY (`p_ID`) REFERENCES `person` (`p_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

      ALTER TABLE `person`
        ADD CONSTRAINT `person_ibfk_1` FOREIGN KEY (`t_ID`) REFERENCES `team` (`t_ID`) ON DELETE CASCADE ON UPDATE CASCADE;
      COMMIT;

      /*!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 */;

!!! Change the MYSQL_ROOT_PASSWORD password in docker compose !!!

2. Copy content of this Repository into the persistent volume of the php:apache Container.
3. !!! Change the MYSQL_ROOT_PASSWORD password !!! inside the php_includes/db_connect.php file.

Screenshots

Frontend Page

Backend Page

"Print QR-Codes" Page

"Add Beer" Page

Usage

Open http://localhost:8080 to open the Frontend Page. On this page you can monitor witch team has drank the most beer. Click anywhere on the screen to get redirected to the Backend.

On the Backend Page http://localhost:8080/backend.php you can add/remove Teams and add/remove persons to this teams. Each person gets is own QR-Code.

On the "Add Beer" Page http://localhost:8080/beer.php you can add beer to persons. You can use a connected Barcode-Scanner to scan the QR-Code of the Person you want to add a beer or you can copy the QR-Code Value from the table of the Backend Page.

DB Scheme

Options-Table

ID	value	desscription
1	oneToOne / oneToTeamSize	Calculation method how many point each team gets per beer. OneToOne: 1 Beer = 1 Point ------------- oneToTeamSize: 1 Beer / number of team members = 0,XX Points