From 93a05b5de5d5d2bcb5d6b0c60f55d7d926c3ffff Mon Sep 17 00:00:00 2001 From: Holger Cremer Date: Sat, 15 Dec 2018 19:06:15 +0100 Subject: adds a script thats sends product stats to mqtt --- shop2mqtt/.gitignore | 2 ++ shop2mqtt/sendStock2Mqtt.py | 39 +++++++++++++++++++++++++++++++++++++++ shop2mqtt/shop2mqtt.conf.example | 10 ++++++++++ 3 files changed, 51 insertions(+) create mode 100644 shop2mqtt/.gitignore create mode 100755 shop2mqtt/sendStock2Mqtt.py create mode 100644 shop2mqtt/shop2mqtt.conf.example 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/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 -- cgit v1.2.3 From 7bb9be84cc958a6d8f55996036af46cb7dfe0057 Mon Sep 17 00:00:00 2001 From: Holger Cremer Date: Tue, 18 Dec 2018 21:04:39 +0100 Subject: adds a readme --- shop2mqtt/README.md | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 shop2mqtt/README.md 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 -- cgit v1.2.3 From d92aa9d79a3f80557119b660f7191e96b7d79878 Mon Sep 17 00:00:00 2001 From: Holger Cremer Date: Fri, 5 Apr 2019 18:01:28 +0200 Subject: Several pdf generation improvements. - 2 scripts to create barcodes - "joined add" parameter to create partial lists for updates - a dockerfile to easy creation --- generation/.gitignore | 4 ++++ generation/Dockerfile | 16 ++++++++++++++++ generation/README.md | 22 ++++++++++++++++++++++ generation/barcodelist.rb | 16 ++++++++-------- generation/createBarcodeList.sh | 15 +++++++++++++++ generation/createPassList.sh | 15 +++++++++++++++ generation/passlist.rb | 14 +++++++------- generation/query.sh | 18 +++++++++++++++++- 8 files changed, 104 insertions(+), 16 deletions(-) create mode 100644 generation/.gitignore create mode 100644 generation/Dockerfile create mode 100644 generation/README.md create mode 100755 generation/createBarcodeList.sh create mode 100755 generation/createPassList.sh 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 < 0 +$DATE_PART +EOF +` +sqlite3 shop.db "$SQL" \ + | sed "s~|~,~g" > users.csv -- cgit v1.2.3