Improve entry flow a little

Reviewed-on: https://git.fsmi.org/paul/jon/pulls/6
Reviewed-by: Paul Brinkmeier <paul.brinkmeier@fsmi.uni-karlsruhe.de>

Dockerfile

@@ -0,0 +1,25 @@
+FROM debian:latest as builder
+
+RUN apt-get update 
+RUN apt-get upgrade -y
+RUN apt-get install -y elm-compiler make ca-certificates
+
+COPY . /app
+WORKDIR /app
+RUN rm -rf .venv venv
+
+RUN make frontend
+
+
+FROM python:3.11-alpine as runner
+COPY --from=builder /app /app
+
+WORKDIR /app
+RUN pip install -r requirements.txt
+RUN pip install gunicorn
+
+EXPOSE 5000
+ENV JON_DB_CONNECTION_STRING="host=fsmi-db.fsmi.org dbname=garfield"
+ENV JON_SECRET_KEY="changeme"
+
+CMD ["sh", "-c", "gunicorn -b '0.0.0.0:5000' --chdir /app 'jon:create_app()'"]

README.md

@@ -45,6 +45,20 @@ flask --app jon run --debug
 
 `--debug` restarts the server when a source file changes.
 
+## Running with docker
+When you prefer running the application using docker you can just use
+```
+docker compose up
+```
+
+In case your local username does not line up with your FSMI-username, you need to specify your FSMI-username
+using `USER=<username>`, e.g.:
+```
+USER=shirkanesi docker compose up
+```
+
+This can also be persisted by following the instructions in the docker-compose.yml
+
 ## fsmi-db forward
 
 ```
@@ -68,3 +82,4 @@ ssh -nNTvL 5432:fsmi-db.fsmi.org:5432 fsmi-login.fsmi.uni-karlsruhe.de
   - [x] Figure out/Add documentation about building `entry.js`
   - [ ] Clean up the code a little and add some comments
   - [ ] Needs good documentation for maintainability
+- [ ] Use cool new function for deactivating items

docker-compose.yml

@@ -0,0 +1,19 @@
+---
+version: '3.7'
+
+services:
+  jon:
+    container_name: jon
+    build: .
+    ports:
+      - "5000:5000"
+    volumes:
+      - ~/.pgpass:/root/.pgpass:ro
+    dns:
+      - 1.1.1.1
+      - 8.8.8.8
+    network_mode: bridge
+    environment:
+      # If your local user is different from your fsmi user, change $USER here!
+      - JON_DB_CONNECTION_STRING="host=fsmi-db.fsmi.org dbname=garfield user=$USER"
+      - JON_SECRET_KEY="changemetosomethingsuperrandomandsecure"

frontend/Entry.elm

@@ -133,8 +133,11 @@ type alias Context =
 
 type alias Globals =
   { locations : List Location
+  , defaultLocation : Location
   , groups : List Group
+  , defaultGroup : Group
   , taxGroups : List TaxGroup
+  , defaultTaxGroup : TaxGroup
   }
 
 type State
@@ -158,7 +161,7 @@ type Msg
   = SetSearchTerm String
   | SubmitSearch
   | ReceiveSearchResults (Result Http.Error (List SearchResult))
-  | GotoItemEditor SearchResult
+  | GotoItemEditor IEInit
   | SetBarcode String
   | SetName String
   | SetSalesUnits String
@@ -169,6 +172,10 @@ type Msg
   | SetLocation String
   | SetTaxGroup String
 
+type IEInit
+  = IEInitBarcode String
+  | IEInitSearchResult SearchResult
+
 -- Update logic: State machine etc.
 
 update msg { globals, state } =
@@ -190,7 +197,21 @@ updateState msg globals state = case state of
       )
     ReceiveSearchResults (Ok searchResults) ->
       (ItemSearch { model | searchResults = searchResults }, Cmd.none)
-    GotoItemEditor searchResult ->
+    GotoItemEditor (IEInitBarcode barcode) ->
+      ( ItemEditor
+        { barcode = barcode
+        , name = ""
+        , calculator = Calculator.init 0
+        , netUnitPrice = NumberInput.fromFloat 0
+        , grossUnitPrice = NumberInput.fromFloat 0
+        , salesUnits = NumberInput.fromInt 0
+        , group = Select.init (.id >> String.fromInt) (.name) globals.defaultGroup globals.groups
+        , location = Select.init (.id >> String.fromInt) (.name) globals.defaultLocation globals.locations
+        , taxGroup = Select.init (.id >> String.fromInt) (.description) globals.defaultTaxGroup globals.taxGroups
+        }
+      , Cmd.none
+      )
+    GotoItemEditor (IEInitSearchResult searchResult) ->
       case find (\tg -> tg.id == searchResult.taxGroupId) globals.taxGroups of
         Nothing -> (state, Cmd.none)
         Just taxGroup ->
@@ -245,7 +266,13 @@ suggestedGrossPrice netPrice percentage =
 
 view { globals, state } = case state of
   ItemSearch model ->
-    fieldset []
+    div []
+      [ div []
+        [ if model.searchTerm == ""
+          then button [ disabled True ] [ text "Neuer Artikel" ]
+          else button [ onClick <| GotoItemEditor <| IEInitBarcode model.searchTerm  ] [ text <| "Neuer Artikel mit Barcode " ++ model.searchTerm ]
+        ]
+      , fieldset []
         [ legend [] [ text "Vorlage für Auftrag wählen" ]
         , Html.form [ onSubmit SubmitSearch ]
           [ div [ class "form-input" ]
@@ -255,6 +282,7 @@ view { globals, state } = case state of
           , table [] <| searchResultHeaders :: List.map viewSearchResult model.searchResults
           ]
         ]
+      ]
   ItemEditor model ->
     Html.form [ method "POST" ]
       [ fieldset []
@@ -385,7 +413,7 @@ viewSearchResult model =
     , td [] [ text model.locationName ]
     , td [] [ text <| showBool model.available ]
     , td []
-      [ Html.form [ onSubmit <| GotoItemEditor model ]
+      [ Html.form [ onSubmit <| GotoItemEditor <| IEInitSearchResult model ]
         [ button [] [ text "Als Vorlage verwenden" ]
         ]
       ]

jon/__init__.py

@@ -20,6 +20,7 @@ def create_app():
     # You don't need a config.json. If you don't provide one, default-config.json
     # is used.
     app.config.from_file("config.json", load=json.load, silent=True)
+    app.config.from_prefixed_env(prefix="JON")
 
     db.init_app(app)
     auth.init_app(app)

jon/db/add_views.sql

@@ -59,7 +59,6 @@ FROM garfield.inventory_items
 ORDER BY inventory_items.name;
 
 -- How many *other* active inventory lines exist with the same barcode in the same location that are newer?
-
 CREATE TEMPORARY VIEW more_recent_inventory_lines_with_same_barcode AS
 SELECT
   a.item_id,
@@ -74,3 +73,41 @@ LEFT JOIN garfield.inventory_items AS b
       AND b.available
       AND b.bought > a.bought
 GROUP BY a.item_id;
+
+-- We need to create this table so that we can put an index on it
+-- Otherwise the join in the consumption graph query becomes much slower
+-- Perhaps it would be nicer to use a materialized view instead
+DROP TABLE IF EXISTS last_n_days;
+CREATE TEMPORARY TABLE last_n_days AS (
+  SELECT
+    generate_series(now() - interval '14 days', now(), interval '1 day')::date AS sale_date
+);
+CREATE UNIQUE INDEX last_n_days_sale_date ON last_n_days (sale_date);
+
+-- Get an array of how often items were sold over the last 14 days
+CREATE TEMPORARY VIEW inventory_last_n_days_sales AS
+WITH
+sales_by_date AS (
+  SELECT
+    inventory_line AS item_id,
+    date_trunc('day', snack_sales_log_timestamp AT TIME ZONE 'UTC+1') AS sale_date,
+    count(*)::int AS sales
+  FROM garfield.snack_sales_log
+  GROUP BY item_id, sale_date
+),
+beeg AS (
+  SELECT item_id, sale_date, count(snack_sales_log_timestamp)::int AS sales
+  FROM garfield.inventory_items
+  CROSS JOIN last_n_days
+  LEFT JOIN garfield.snack_sales_log
+         ON inventory_line = item_id
+-- snack_sales_log has an index on snack_sales_log_timestamp to speed up this query
+-- If that index doesn't exist the query takes much longer.
+        AND sale_date = date_trunc('day', snack_sales_log_timestamp AT TIME ZONE 'UTC+1')
+  WHERE available
+  GROUP BY item_id, sale_date
+  ORDER BY item_id, sale_date
+)
+SELECT item_id, array_agg(sales) AS last_n_days_sales
+FROM beeg
+GROUP BY item_id

jon/db/get_inventory_overview.sql

@@ -2,6 +2,7 @@ SELECT
   *
 FROM all_inventory_item_overview
 LEFT JOIN more_recent_inventory_lines_with_same_barcode USING (item_id)
+LEFT JOIN inventory_last_n_days_sales USING (item_id)
 WHERE (%(location_id)s IS NULL OR location = %(location_id)s)
   AND available
 ORDER BY

jon/db/get_inventory_report.sql

@@ -1,49 +1,43 @@
+-- parameters:
+--
+-- location_id: Location to generate the report for
+
 WITH
-most_recent_sales AS (
-  SELECT DISTINCT ON (inventory_line)
-    inventory_line, snack_sales_log_id, snack_sales_log_timestamp AS most_recent_sale
+sales_by_item_id AS (
+  SELECT
+    inventory_line AS item_id,
+    max(snack_sales_log_timestamp) AS last_sold,
+    count(*)::int AS units_sold
   FROM garfield.snack_sales_log
-  ORDER BY inventory_line ASC, snack_sales_log_timestamp DESC
+  WHERE type_id = 'SNACK_BUY'
+    AND inventory_line IS NOT NULL
+    AND snack_sales_log_timestamp > NOW() - INTERVAL '120 days'
+    AND location_id = %(location_id)s
+  GROUP BY item_id
 ),
-enhanced_overview1 AS (
+sales_by_barcode AS (
   SELECT
-    inventory_items.item_id,
-    inventory_items.item_barcode,
-    inventory_items.name,
-    units_left,
-    inventory_items.sales_units,
-    correction_delta,
-    location_name,
-    location,
-      CASE
-        WHEN snack_sales_log_id IS NULL THEN 0
-        ELSE sales / (EXTRACT(EPOCH FROM (
-          CASE
-            WHEN units_left <= 0 THEN most_recent_sale
-            ELSE NOW()
-          END
-        )) - EXTRACT(EPOCH FROM bought)) * 24 * 3600
-    END AS per_day
-  FROM garfield.inventory_item_overview
+    item_barcode,
+    max(name) AS name,
+    sum(units_sold) AS units_sold,
+    round(avg((units_sold / extract(epoch FROM (CASE WHEN available THEN now() ELSE last_sold END) - bought)) * 86400)::numeric, 2) AS sold_per_day
+  FROM sales_by_item_id
   LEFT JOIN garfield.inventory_items USING (item_id)
-  LEFT JOIN most_recent_sales ON item_id = inventory_line
+  GROUP BY item_barcode
 ),
-enhanced_overview2 AS (
+current_inventory AS (
   SELECT
-    *,
-    CASE
-      WHEN per_day = 0 THEN NULL
-      ELSE GREATEST(0, units_left / per_day)
-    END AS days_left
-  FROM enhanced_overview1
+    item_barcode,
+    sum(units_left)::int AS units_left
+  FROM all_inventory_item_overview
+  WHERE available
+    AND location = %(location_id)s
+  GROUP BY item_barcode
 )
 
 SELECT
-  *,
-  CASE
-    WHEN days_left IS NULL THEN NULL
-    ELSE GREATEST(0, (60 - days_left) * per_day)
-  END AS for_two_months
-FROM enhanced_overview2
-WHERE (%(location_id)s IS NULL OR location = %(location_id)s)
-ORDER BY days_left ASC, per_day DESC
+  sales_by_barcode.*,
+  COALESCE(current_inventory.units_left, 0)::int AS units_left
+FROM sales_by_barcode
+LEFT JOIN current_inventory USING (item_barcode)
+ORDER BY units_sold DESC

jon/entry.py

@@ -94,10 +94,13 @@ def new_order():
     with db.run_query("entry/get_tax_groups.sql") as cursor:
         tax_groups = cursor.fetchall()
 
+    selected_location = current_user.data.location
+
     return render_template(
         "entry/new-order.html",
         groups=groups,
         locations=locations,
+        default_location=next(location for location in locations if location["id"] == selected_location["location_id"]) if selected_location is not None else locations[0],
         tax_groups=tax_groups
     )
 

jon/inventory.py

@@ -22,11 +22,17 @@ def index():
 @bp.get("/report")
 def read_report():
     location = current_user.data.location
+
+    if location is None:
+        # TODO: Error handling
+        return "please select a location in order to generate a report", 400
+
     items = db.run_query("get_inventory_report.sql", {
-        "location_id": None if location is None else location["location_id"]
+        "location_id": location["location_id"]
     }).fetchall()
 
     return render_template("inventory/read_report.html", **{
+        "location": location,
         "items": items
     })
 

jon/static/jon.css

@@ -40,6 +40,9 @@ nav > ul > li + li:before {
 .--centered {
   text-align: center;
 }
+.--not-important {
+  color: #aaa;
+}
 @keyframes wiggle {
   0%, 100% { margin-top: 0; }
   50% { margin-top: -0.5em; }
@@ -55,6 +58,10 @@ th {
   body {
     font-size: 8px;
   }
+  /* hide the menu when printing */
+  header {
+    display: none;
+  }
 }
 .form-input > label {
   font-size: .8em;
@@ -74,3 +81,7 @@ th {
 details {
   font-size: 0.8em;
 }
+.consumption-graph {
+  display: block;
+  height: 1em;
+}

jon/templates/base.html

@@ -12,6 +12,7 @@
         <ul>
           <li{{ " class=current-page" if request.path == "/" else "" }}><a href="/">Home</a></li>
           <li{{ " class=current-page" if request.path.startswith("/inventory") else "" }}><a href="/inventory">Inventar</a></li>
+          <li><a href="/inventory/report">Einkäuferbericht</a></li>
           <li{{ " class=current-page" if request.path.startswith("/entry") else "" }}><a href="/entry">Eintragen</a></li>
           <li{{ " class=current-page" if request.path.startswith("/location") else "" }}>
             <a href="/location">

jon/templates/entry/new-order.html

@@ -8,8 +8,11 @@ Elm.Entry.init({
   node: document.querySelector('.entry-app'),
   flags: {
     locations: {{ to_json(locations) | safe }},
+    defaultLocation: {{ to_json(default_location) | safe }},
     groups: {{ to_json(groups) | safe }},
-    taxGroups: {{ to_json(tax_groups) | safe }}
+    defaultGroup: {{ to_json(groups[0]) | safe }},
+    taxGroups: {{ to_json(tax_groups) | safe }},
+    defaultTaxGroup: {{ to_json(tax_groups[0]) | safe }},
   }
 });
 </script>

jon/templates/inventory/index.html

@@ -1,3 +1,26 @@
+{% macro consumption_graph_svg(values) -%}
+{% set stroke_width = 8 %}
+{% set width = 300 %}
+{% set height = 100 %}
+{% set padding = 4 %}
+{% set dx = (width - 2 * padding) / ((values | length) + 1) %}
+{% set dy = (height - 2 * padding) / ((values + [1]) | max) %}
+<svg viewBox="0 0 {{ width }} {{ height }}" role="img" class="consumption-graph">
+  <polyline
+    points="
+    {% for value in values %}
+      {{ padding + loop.index * dx }}, {{ height - padding - value * dy }}
+    {% endfor %}
+    "
+    stroke="green"
+    stroke-width="{{ stroke_width }}"
+    stroke-linecap="round"
+    stroke-linejoin="round"
+    fill="none"
+  >
+</svg>
+{% endmacro -%}
+
 {% extends "base.html" %}
 
 {% block content %}
@@ -5,6 +28,7 @@
   <tr>
     <th>Stat</th>
     <th>ID</th>
+    <th>Graph</th>
     <th>Barcode</th>
     <th>Name</th>
     <th>Preis (Netto)</th>
@@ -23,6 +47,7 @@
       {% if item.other_lines_count != 0 %}<span title="{% if item.other_lines_count == 1 %}Neuerer Eintrag mit demselben Barcode ist aktiv{% else %}{{ item.other_lines_count }} Einträge mit demselben Barcode sind aktiv{% endif %}">🔄</span>{% endif %}
     </td>
     <td><a href="/inventory/item/{{ item.item_id }}">{{ item.item_id }}</a></td>
+    <td>{{ consumption_graph_svg(item.last_n_days_sales) }}</td>
     <td><code>{{ item.item_barcode }}</code></td>
     <td>{{ item.name }}</td>
     <td class="--align-right">{{ format_currency(item.unit_price) }}</td>

jon/templates/inventory/read_report.html

@@ -1,29 +1,29 @@
 {% extends "base.html" %}
 
 {% block content %}
+<h2>Einkäuferbericht für {{ location.location_name }}</h2>
+
 <table>
   <tr>
-    <th>ID</th>
     <th>Barcode</th>
     <th>Name</th>
     <th>Inventar</th>
-    <th>Gesamt</th>
-    <th>Raum</th>
+    <th title="In den letzten 4 Monaten verkauft">Verkauft</th>
     <th>Verbrauch [1/d]</th>
+    <th>Verbrauch [1/60d]</th>
+    <!--
     <th title="Estimated Time Until Empty">ETUE [d]</th>
     <th>Für 2m</th>
+    -->
   </tr>
 {% for item in items %}
-  <tr>
-    <td><a href="/inventory/item/{{ item.item_id }}">{{ item.item_id }}</a></td>
+<tr{% if item.units_left >= item.units_sold %} class="--not-important"{% endif %}>
     <td><code>{{ item.item_barcode }}</code></td>
     <td>{{ item.name }}</td>
     <td class="--align-right">{{ item.units_left }}</td>
-    <td class="--align-right">{{ item.sales_units + item.correction_delta }}</td>
-    <td>{{ item.location_name }}</td>
-    <td class="--align-right">{{ item.per_day|round(2) }}</td>
-    <td class="--align-right">{% if item.days_left != None %}{{ item.days_left|round(1) }}{% endif %}</td>
-    <td class="--align-right">{% if item.for_two_months %}{{ item.for_two_months|round(1) }}{% endif %}</td>
+    <td class="--align-right">{{ item.units_sold }}</td>
+    <td class="--align-right">{{ item.sold_per_day }}</td>
+    <td class="--align-right">{{ item.sold_per_day * 60 }}</td>
   </tr>
 {% endfor %}
 </table>