Initial

4 files changed, 174 insertions, 0 deletions

diff --git a/Makefile b/Makefile
new file mode 100644
index 0000000..484791c
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,11 @@
+all: install
+
+run:
+        venv/bin/python avon.py -P 8080 &
+
+venv:
+        python3 -m venv ./venv
+
+install: venv
+        . venv/bin/activate && pip install --upgrade pip
+        . venv/bin/activate && pip install -r requirements.txt
diff --git a/avon.py b/avon.py
new file mode 100755
index 0000000..d47382d
--- /dev/null
+++ b/avon.py
@@ -0,0 +1,91 @@
+#! venv/bin/python
+
+import os
+import sys
+import pprint
+import json
+from flask import Flask,render_template,request
+from argparse import ArgumentParser
+import psycopg2
+
+app = Flask(__name__)
+
+class Convert_jinja_object:
+
+    def __init__(self):
+        self.myvar = 'sample_var'
+
+    def bits_to_years(self, x):
+        year_map = [ "92Q2", "95Q0", "96Q0", "96Q1", "97Q1", "97Q3", "98Q1", "98Q3", "99Q1", "99Q3", "00Q1", "00Q3", "01Q1", "01Q2", "01Q3", "01Q4", "02Q1", "02Q3", "03Q1", "03Q3", "04Q1", "04Q3", "05Q1", "05Q3", "06Q1", "06Q3", "07Q1", "07Q3", "08Q1", "08Q3", "09Q1", "09Q3", "10Q1", "10Q3", "11Q1", "11Q3", "12Q1", "12Q3", "13Q1", "13Q3", "14Q1", "14Q3", "15Q1", "15Q3", "16Q1", "16Q3", "17Q1", "17Q3", "18Q1", "18Q3", "19Q1", "19Q3", "20Q1", "20Q3" ]
+        if x == 0:
+            return '/'
+        years = ''
+        index = 0
+        while x > 0:
+            if index > 0:
+                years = years + ','
+            start_off = (x&-x).bit_length()-1
+            x = ~(x >> start_off)
+            end_off = (x&-x).bit_length()-1
+            x = ~(x >> (end_off + 1))
+            years = years + (year_map[index + start_off])
+            if end_off > 1:
+                years = years + '-' + year_map[index + start_off + end_off - 1]
+            index = index + start_off + end_off + 1
+        return years
+
+@app.route("/", methods=['GET', 'POST'])
+def root():
+    content = request.json
+
+    query = "SELECT DISTINCT ON (id) presence_flag, reverse_flag, biz_flag, zip, nachname, vorname, zusaetze, strasse, hausnummer, verweise, ort, vorwahl, rufnummer, web, email, coords FROM Telefonbuch "
+    where = ""
+    params = []
+    anyjoin = ""
+
+    for i in range(1,5):
+        search = request.form.get("search_{0}_string".format(i))
+        if not search: continue
+        column = request.form.get("search_{0}_column".format(i))
+        operator = request.form.get("search_{0}_operator".format(i))
+        isany = request.form.get("search_{0}_any".format(i))
+
+        if not column in [ 'zip', 'nachname', 'vorname', 'zusaetze', 'strasse', 'hausnummer', 'verweise', 'ort', 'vorwahl', 'rufnummer', 'web', 'email', 'coords' ]: continue
+
+        query = query + "INNER JOIN table_{0} ON Telefonbuch.id = table_{0}.telefonbuch_id ".format(column)
+        if len(where):
+            where = where + "AND "
+        else:
+            where = "WHERE "
+        where = where + "table_{0}.value {1} %s ".format(column, {'equals': '=', 'equalsnot': '<>', 'contains': 'ILIKE', 'containsnot': 'NOT ILIKE', 'beginswith': 'ILIKE'}[operator])
+        if not isany:
+            if len(anyjoin) == 0:
+                anyjoin = "table_" + column
+            else:
+                where = where + "AND " + anyjoin + ".offs = table_" + column + ".offs "
+        if operator in ['contains','containsnot']:
+            search = "%" + search + "%"
+        if operator in ['beginswith']:
+            search = search + "%"
+        params.append(search)
+
+    if len(where) > 0:
+        conn = psycopg2.connect(database="erdgeist", user="postgres", password="", host="127.0.0.1")
+        cur = conn.cursor()
+        print (query + where + ' LIMIT 10000')
+        pprint.pprint( params )
+
+        cur.execute(query + where + ' LIMIT 10000', params)
+        rows = cur.fetchall()
+    else:
+        rows = [[0,0,0,{},{},{},{},{},{},{},{},{},{}]]
+
+    return render_template('index.html', rows=rows, request=request, convert=Convert_jinja_object())
+
+if __name__ == "__main__":
+    parser = ArgumentParser(description="AVON")
+    parser.add_argument("-H", "--host", help="Hostname of the Flask app " + "[default %s]" % "127.0.0.1", default="127.0.0.1")
+    parser.add_argument("-P", "--port", help="Port for the Flask app " + "[default %s]" % "5000", default="5000")
+    args = parser.parse_args()
+
+    app.run(host=args.host, port=int(args.port))
diff --git a/requirements.txt b/requirements.txt
new file mode 100644
index 0000000..7ed34e8
--- /dev/null
+++ b/requirements.txt
@@ -0,0 +1,12 @@
+certifi==2018.11.29
+chardet==3.0.4
+Click==7.0
+Flask==1.0.2
+idna==2.8
+itsdangerous==1.1.0
+Jinja2==2.10
+MarkupSafe==1.1.1
+psycopg2==2.7.7
+requests==2.21.0
+urllib3==1.24.1
+Werkzeug==0.14.1
diff --git a/templates/index.html b/templates/index.html
new file mode 100644
index 0000000..cf4e616
--- /dev/null
+++ b/templates/index.html
@@ -0,0 +1,60 @@
+<html>
+<title>AVON</title>
+<head>
+<style>
+  td { vertical-align: top; }
+  tr.color_1 { background: #ccc; }
+  table { border: none; }
+</style>
+</head>
+<body>
+<form action="/" METHOD="POST">
+  {%- for field in range(1,5) -%}
+    <div class="search_entry">
+      <select name="search_{{ field }}_column">
+        {% set column_search = request.form.get('search_'+(field|string)+'_string', '') %}
+        {% if column_search | length %}
+          {% set column_val = request.form.get('search_'+(field|string)+'_column') %}
+          {% set operator_val = request.form.get('search_'+(field|string)+'_operator') %}
+          {% set any_val = request.form.get('search_'+(field|string)+'_any') %}
+        {% else %}
+          {% set column_val = ['nachname', 'vorname', 'ort', 'rufnummer'][field-1] %}
+          {% set operator_val = ['equals', 'contains', 'equals', 'contains'][field-1] %}
+          {% set any_val = ['', '', 'on', ''][field-1] %}
+        {% endif %}
+        {%- for column in [ ['vorname', 'Vorname'], ['nachname', 'Nachname'], ['zip', 'PLZ'], ['ort', 'Ort'], ['vorwahl', 'Vorwahl'], ['rufnummer', 'Rufnummer'], ['strasse', 'Straße'], ['hausnummer', 'Hausnummer'], ['zusaetze', 'Zusatz'], ['verweise', 'Verweise / Branche'], ['email', 'E-Mail'], ['web', 'Webseite'] ] -%}
+          <option value="{{ column[0] }}" {%- if column_val== column[0] -%}selected="selected"{%- endif -%}>{{ column[1] }}</option>
+        {%- endfor -%}
+      </select>
+      <select name="search_{{ field }}_operator">
+        {%- for operator in [ [ 'equals', 'equals'], ['beginswith', 'begins with'], ['contains', 'contains' ], ['equalsnot', 'equals not'], ['containsnot', 'contains not'] ] -%}
+          <option value="{{ operator[0] }}" {%- if operator_val == operator[0] -%}selected="selected"{%- endif -%}>{{ operator[1] }}</option>
+        {%- endfor -%}
+      </select>
+      <input type="checkbox" name="search_{{ field }}_any" {%- if any_val == 'on' -%}checked="{%- endif -%}">any</input>
+      <input type="text" name="search_{{ field }}_string" value="{{ request.form.get('search_'+(field|string)+'_string', '') }}">
+    </div>
+  {%- endfor -%}
+  <input type="submit" value="Search">
+</form>
+{% if rows | length > 0 -%}<h3>Found {{ rows | length }} matches.</h3>{% endif %}
+{% set color = { 'value': 0 } %}
+<table>
+  <thead><td>flags</td><td>zip</td></thead>
+{%- for row in rows -%}
+{% set span = row[3:16] | reject("none") | map('length') | max %}
+  {% for r in range (span) %}
+    {% if ( r == 0 ) and ( color.update({ 'value': 1 - color.value }) ) %} {% endif %}
+    <tr class="color_{{ color.value }}">
+      {%- if r == 0 -%}
+        <td rowspan="{{span}}">{{ convert.bits_to_years(row[0]|int) }} : {{ convert.bits_to_years(row[1]|int) }} : {{ convert.bits_to_years(row[2]|int) }}</td>
+      {%- endif -%}
+      {%- for c in range(3, 16) -%}
+        <td>{{ row[c][r] or '' }}</td>
+      {%- endfor -%}
+    </tr>
+  {%- endfor -%}
+{%- endfor -%}
+</table>
+</body>
+</html>