Merge pull request #45 from smilix/master

mqtt script & pdf generation improvements

12 files changed, 172 insertions, 16 deletions

diff --git a/generation/.gitignore b/generation/.gitignore
new file mode 100644
index 0000000..026ff7b
--- /dev/null
+++ b/generation/.gitignore
@@ -0,0 +1,4 @@
+users.csv
+shop.db
+barcodes/
+passlist/
diff --git a/generation/Dockerfile b/generation/Dockerfile
new file mode 100644
index 0000000..e469d08
--- /dev/null
+++ b/generation/Dockerfile
@@ -0,0 +1,16 @@
+FROM debian:stretch-slim
+
+ENV DEBIAN_FRONTEND noninteractive
+
+RUN apt-get update && apt-get install -y --no-install-recommends \
+    sqlite3 \
+    ruby \
+    barcode \
+    texlive-latex-base texlive-font-utils ghostscript \
+    && rm -rf /var/lib/apt/lists/*
+
+ENV LANG C.UTF-8
+ENV LC_ALL=C.UTF-8
+ENV LANGUAGE=C.UTF-8
+
+WORKDIR /gen
diff --git a/generation/README.md b/generation/README.md
new file mode 100644
index 0000000..40508db
--- /dev/null
+++ b/generation/README.md
@@ -0,0 +1,22 @@
+# With docker
+
+```bash
+# build
+docker build -t list-gen .
+# run
+docker run --rm -it --user=$(id -u):$(id -g) -v $(pwd)/:/gen list-gen /bin/bash
+```
+
+# Usage
+
+```bash
+./createBarcodeList.sh
+# or with joined_at date
+./createBarcodeList.sh 2019-01-15
+```
+
+```bash
+./createPassList.sh
+# or with joined_at date
+./createPassList.sh 2019-01-15
+```
diff --git a/generation/barcodelist.rb b/generation/barcodelist.rb
index 9784c79..980837c 100644
--- a/generation/barcodelist.rb
+++ b/generation/barcodelist.rb
@@ -19,7 +19,7 @@ require "csv"
 		\maketitle
 		\begin{center}
 		\begin{longtable}{|c|c|}
-			%s	
+			%s
 		\end{longtable}
 	\end{center}
 	\end{document}}
@@ -32,13 +32,13 @@ require "csv"
 	\hline}
 
 @graphics = %q{ \includegraphics{%s} %s}
-@name = %q{ %s %s %s}
+@name = %q{ %s %s (%s) %s}
 
 @csv = CSV.read(ARGV[0])
 
 #generate barcodes
-@csv.each{|r| 
-	system("barcode -n -E -b 'USER %s' -o '%s.eps' -u mm -g 80x30 -e 39\n" % [r[0], r[0]])
+@csv.each{|r|
+	system("barcode -n -E -b 'USER %s' -o 'barcodes/%s.eps' -u mm -g 80x30 -e 39\n" % [r[0], r[0]])
 }
 
 #generate latex
@@ -49,11 +49,11 @@ name = ""
 	le = i % 2 == 0 || i >= @csv.length
 	sign = le ? "\\\\" : "&"
 	graphics += @graphics % [@csv[i-1][0], sign]
-	name += @name % [@csv[i-1][1], @csv[i-1][2], sign]
-	if le 
-		tmp += @line % [graphics, name] 
+	name += @name % [@csv[i-1][1], @csv[i-1][2], @csv[i-1][0], sign]
+	if le
+		tmp += @line % [graphics, name]
 		graphics = ""
 		name = ""
 	end
 }
-File.open("barcode.latex", "w+"){|f| f.write(@template % tmp)}
+File.open("barcodes/barcode.latex", "w+"){|f| f.write(@template % tmp)}
diff --git a/generation/createBarcodeList.sh b/generation/createBarcodeList.sh
new file mode 100755
index 0000000..a62335e
--- /dev/null
+++ b/generation/createBarcodeList.sh
@@ -0,0 +1,15 @@
+#!/bin/bash
+FOLDER=barcodes
+
+echo "Cleanup..."
+rm -rf $FOLDER
+mkdir -p $FOLDER
+
+echo "Create user list from db..."
+./query.sh $1
+
+echo "Make latex document and images..."
+ruby barcodelist.rb users.csv
+
+echo "Run pdflatex..."
+( cd $FOLDER && pdflatex barcode.latex )
diff --git a/generation/createPassList.sh b/generation/createPassList.sh
new file mode 100755
index 0000000..5226308
--- /dev/null
+++ b/generation/createPassList.sh
@@ -0,0 +1,15 @@
+#!/bin/bash
+FOLDER=passlist
+
+echo "Cleanup..."
+rm -rf $FOLDER
+mkdir -p $FOLDER
+
+echo "Create user list from db..."
+./query.sh $1
+
+echo "Make latex document and images..."
+ruby passlist.rb users.csv
+
+echo "Run pdflatex..."
+( cd $FOLDER && pdflatex passlist.latex )
diff --git a/generation/passlist.rb b/generation/passlist.rb
index cb02bd1..9702762 100644
--- a/generation/passlist.rb
+++ b/generation/passlist.rb
@@ -22,7 +22,7 @@ require "csv"
 	\begin{document}
 		\begin{center}
 		\begin{longtable}{| >{\centering\arraybackslash}p{8.5cm}| >{\centering\arraybackslash}p{8.5cm}| >{\centering\arraybackslash}p{8.5cm}|}
-			%s	
+			%s
 		\end{longtable}
 	\end{center}
 	\end{document}}
@@ -36,13 +36,13 @@ require "csv"
 
 @graphics = %q{ \includegraphics{%s} \rule{0cm}{3.5cm} %s}
 @name = %q{ %s %s %s}
-@ktt = %q{\includegraphics[width=8cm,angle=180]{ktt}  %s }
+@ktt = %q{\includegraphics[width=8cm,angle=180]{../ktt}  %s }
 
 @csv = CSV.read(ARGV[0])
 
 #generate barcodes
-@csv.each{|r| 
-	system("barcode -n -E -b 'USER %s' -o '%s.eps' -e 39\n" % [r[0], r[0]])
+@csv.each{|r|
+	system("barcode -n -E -b 'USER %s' -o 'passlist/%s.eps' -e 39\n" % [r[0], r[0]])
 }
 
 #generate latex
@@ -54,8 +54,8 @@ name = ""
 	sign = le ? "\\\\" : "&"
 	graphics += @graphics % [@csv[i-1][0], sign]
 	name += @name % [@csv[i-1][1], @csv[i-1][2], sign]
-	if le 
-		tmp += @line % [graphics, name] 
+	if le
+		tmp += @line % [graphics, name]
 		graphics = ""
 		name = ""
 		1.upto(3) {|j|
@@ -68,4 +68,4 @@ name = ""
 		name = ""
 	end
 }
-File.open("barcode.latex", "w+"){|f| f.write(@template % tmp)}
+File.open("passlist/passlist.latex", "w+"){|f| f.write(@template % tmp)}
diff --git a/generation/query.sh b/generation/query.sh
index 67accaf..a6a9783 100755
--- a/generation/query.sh
+++ b/generation/query.sh
@@ -1 +1,17 @@
-sqlite3 shop.db "SELECT id,firstname,lastname FROM users LEFT JOIN authentication ON users.id = authentication.user WHERE (users.disabled IS NULL or users.disabled != 1) and id > 0" | sed "s~|~,~g" > users.csv
+#!/bin/bash
+
+if [[ "$1" != "" ]]; then
+  # format YYYY-MM-DD, e.g. 2017-01-01
+  DATE_PART="AND joined_at > strftime('%s', '$1')"
+fi
+
+SQL=`cat <<EOF
+SELECT id,firstname,lastname FROM users
+LEFT JOIN authentication ON users.id = authentication.user
+WHERE (users.disabled IS NULL or users.disabled != 1)
+AND id > 0
+$DATE_PART
+EOF
+`
+sqlite3 shop.db "$SQL" \
+  | sed "s~|~,~g" > users.csv
diff --git a/shop2mqtt/.gitignore b/shop2mqtt/.gitignore
new file mode 100644
index 0000000..1e25736
--- /dev/null
+++ b/shop2mqtt/.gitignore
@@ -0,0 +1,2 @@
+.idea/
+shop2mqtt.conf
diff --git a/shop2mqtt/README.md b/shop2mqtt/README.md
new file mode 100644
index 0000000..9627e82
--- /dev/null
+++ b/shop2mqtt/README.md
@@ -0,0 +1,17 @@
+# Setup
+
+You need the python3 paho-mqtt lib. E.g. 
+```
+sudo apt-get install python3-paho-mqtt
+```
+
+Create the config from the example: 
+```
+cp shop2mqtt.conf.example shop2mqtt.conf
+```
+and change the config to your needs.
+
+
+# Run
+
+Just run `./sendStock2Mqtt.py` or use cron.
\ No newline at end of file
diff --git a/shop2mqtt/sendStock2Mqtt.py b/shop2mqtt/sendStock2Mqtt.py
new file mode 100755
index 0000000..a54675d
--- /dev/null
+++ b/shop2mqtt/sendStock2Mqtt.py
@@ -0,0 +1,39 @@
+#!/usr/bin/python3
+import configparser
+import json
+import os
+import sqlite3
+import ssl
+
+from paho.mqtt import publish
+
+
+def get_current_stock(shop_config):
+    db_file = shop_config["shop_db"]
+    if not os.path.isfile(db_file):
+        print("Shop db not found: %s" % db_file)
+        exit(1)
+    conn = sqlite3.connect(db_file)
+    c = conn.cursor()
+
+    data = []
+    for row in c.execute('SELECT name, amount FROM products WHERE deprecated = 0 AND amount > 0'):
+        data.append(row)
+
+    return data
+
+
+def send_to_mqtt(mqtt_config, payload):
+    publish.single(mqtt_config["topic"], payload,
+                   hostname=mqtt_config["host"], port=int(mqtt_config["port"]),
+                   auth={'username': mqtt_config["username"], 'password': mqtt_config["password"]},
+                   tls={'ca_certs': mqtt_config["host_cert"], 'tls_version': ssl.PROTOCOL_TLS, 'insecure': False},
+                   client_id="send2Stock2Mqtt")
+
+
+if __name__ == '__main__':
+    config = configparser.ConfigParser()
+    config.read("shop2mqtt.conf")
+
+    stock_data = get_current_stock(config["shop"])
+    send_to_mqtt(config["mqtt"], json.dumps(stock_data, indent=None, separators=(',', ':')))
diff --git a/shop2mqtt/shop2mqtt.conf.example b/shop2mqtt/shop2mqtt.conf.example
new file mode 100644
index 0000000..39aece5
--- /dev/null
+++ b/shop2mqtt/shop2mqtt.conf.example
@@ -0,0 +1,10 @@
+[mqtt]
+host = spacegate.mainframe.lan
+host_cert = spacegate.cert.pem
+port = 8883
+username = shop
+password = ...
+topic = /shop/stock
+
+[shop]
+shop_db = ../shop.db
\ No newline at end of file