4 files changed, 211 insertions, 48 deletions

diff --git a/Makefile b/Makefile
index 484791c..6713e46 100644
--- a/Makefile
+++ b/Makefile
@@ -1,7 +1,7 @@
 all: install
 
 run:
-        venv/bin/python avon.py -P 8080 &
+        PYTHONIOENCODING=UTF-8 venv/bin/python avon.py -H 127.0.1.24 -P 8080
 
 venv:
         python3 -m venv ./venv
diff --git a/avon.py b/avon.py
index d47382d..79b5a1b 100755
--- a/avon.py
+++ b/avon.py
@@ -4,19 +4,18 @@ import os
 import sys
 import pprint
 import json
-from flask import Flask,render_template,request
+from flask import Flask,render_template,request,url_for
 from argparse import ArgumentParser
-import psycopg2
+import psycopg
 
 app = Flask(__name__)
 
 class Convert_jinja_object:
 
     def __init__(self):
-        self.myvar = 'sample_var'
+        self.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", "21Q1", "21Q3", "22Q1", "22Q3", "23Q1", "23Q3", "24Q1", "24Q3", "25Q1", "25Q3" ]
 
     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 = ''
@@ -28,59 +27,170 @@ class Convert_jinja_object:
             x = ~(x >> start_off)
             end_off = (x&-x).bit_length()-1
             x = ~(x >> (end_off + 1))
-            years = years + (year_map[index + start_off])
+            years = years + (self.year_map[index + start_off])
             if end_off > 1:
-                years = years + '-' + year_map[index + start_off + end_off - 1]
+                years = years + '-' + self.year_map[index + start_off + end_off - 1]
             index = index + start_off + end_off + 1
         return years
 
+    def multirange_to_years(self, row):
+    # Format of multi-ranges is {[low,high),[low,high],(low,high),(low,high)]}
+    # Although Telefonbuch only ever inserts the [low,high) range types, so for now let's keep the parser simple
+        years = []
+        for range_item in row:
+            low = range_item.lower
+            high = range_item.upper - 1
+            year = self.year_map[low]
+            if (low != high):
+                year += '-' + self.year_map[high]
+            years.append(year)
+        return ', '.join(years)
+
 @app.route("/", methods=['GET', 'POST'])
 def root():
-    content = request.json
+    convert = Convert_jinja_object()
 
     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 = ""
+    pastjoin = []
+    rv = request.values.to_dict(flat=True)
+        
+    # pprint.pprint(request.values)
 
     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))
+        search = rv.get("s{0}_val".format(i))
+        if not search:
+            rv.pop("s{0}_val".format(i), '')
+            rv.pop("s{0}_col".format(i), '')
+            rv.pop("s{0}_op".format(i), '')
+            rv.pop("s{0}_any".format(i), '')
+            continue
+        column = rv.get("s{0}_col".format(i))
+        operator = rv.get("s{0}_op".format(i))
+        isany = rv.get("s{0}_any".format(i))
+
+        if not column in [ 'zip', 'nachname', 'vorname', 'zusaetze', 'strasse', 'hausnummer', 'verweise', 'ort', 'vorwahl', 'rufnummer', 'web', 'email', 'coords', 'id' ]: continue
 
-        if not column in [ 'zip', 'nachname', 'vorname', 'zusaetze', 'strasse', 'hausnummer', 'verweise', 'ort', 'vorwahl', 'rufnummer', 'web', 'email', 'coords' ]: continue
+        if not column in pastjoin:
+            query = query + "INNER JOIN table_{0} ON Telefonbuch.id = table_{0}.telefonbuch_id ".format(column)
+            pastjoin.append(column)
 
-        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])
+        where = where + "table_{0}.value {1} %s ".format(column, {'equals': '=', 'equalsnot': '<>', 'contains': 'ILIKE', 'containsnot': 'NOT ILIKE', 'beginswith': 'LIKE', 'beginslike': 'ILIKE'}[operator])
         if not isany:
-            if len(anyjoin) == 0:
+            if len(anyjoin) == 0 or "table_" + column == anyjoin:
                 anyjoin = "table_" + column
             else:
                 where = where + "AND " + anyjoin + ".offs = table_" + column + ".offs "
         if operator in ['contains','containsnot']:
             search = "%" + search + "%"
-        if operator in ['beginswith']:
+        if operator in ['beginswith', 'beginslike']:
+            search = search + "%"
+        params.append(search)
+
+    range_min = int(rv.get("year_range_min", '0'))
+    range_max = int(rv.get("year_range_max", len(convert.year_map) - 1))
+    if range_min > range_max:
+        range_max, range_min = range_min, range_max
+        rv["year_range_min"] = range_min
+        rv["year_range_max"] = range_max
+
+    if range_min > 0 or range_max < len(convert.year_map) - 1:
+        if len(where) > 0:
+            where += "AND presence_flag && int4range({},{})" .format(range_min, range_max)
+    else:
+        rv.pop("year_range_min", 0)
+        rv.pop("year_range_max", 0)
+
+    limit = "10000"
+    if rv.get("huge"):
+        limit = "100000"
+
+    if len(where) > 0:
+        conn = psycopg.connect(dbname="telefonbuch", user="postgres", password="", host="127.0.0.1")
+        cur = conn.cursor()
+        print (query + where + ' LIMIT ' + limit)
+        pprint.pprint( params )
+
+        cur.execute(query + where + ' LIMIT ' + limit, params)
+        rows = cur.fetchall()
+    else:
+        rows = []
+
+    return render_template('index.html', rows=rows, request=request, rv=rv, convert=convert)
+
+@app.route("/facebook", methods=['GET', 'POST'])
+def facebook():
+
+    query = "SELECT phone, id, first_name, last_name, gender, location, originally_from, relationship_state, ocupation, email, birthday FROM facebook "
+    where = ""
+    params = []
+    anyjoin = ""
+    pastjoin = []
+    rv = request.values.to_dict(flat=True)
+        
+    pprint.pprint(request.values)
+
+    for i in range(1,5):
+        search = rv.get("s{0}_val".format(i))
+        if not search:
+            rv.pop("s{0}_val".format(i), '')
+            rv.pop("s{0}_col".format(i), '')
+            rv.pop("s{0}_op".format(i), '')
+            rv.pop("s{0}_any".format(i), '')
+            continue
+        column = rv.get("s{0}_col".format(i))
+        operator = rv.get("s{0}_op".format(i))
+        isany = rv.get("s{0}_any".format(i))
+
+        if not column in [ 'phone', 'first_name', 'last_name', 'gender', 'location', 'originally_from', 'relationship_state', 'ocupation', 'email', 'id' ]: continue
+
+        if column == 'phone':
+            search = search.lstrip('+')
+
+        if len(where):
+            where = where + "AND "
+        else:
+            where = "WHERE "
+        where = where + "{0} {1} %s ".format(column, {'equals': '=', 'equalsnot': '<>', 'contains': 'ILIKE', 'containsnot': 'NOT ILIKE', 'beginswith': 'LIKE', 'beginslike': 'ILIKE'}[operator])
+        if operator in ['contains','containsnot']:
+            search = "%" + search + "%"
+        if operator in ['beginswith', 'beginslike']:
             search = search + "%"
         params.append(search)
 
+    limit = "10000"
+    if rv.get("huge"):
+        limit = "100000"
+
     if len(where) > 0:
-        conn = psycopg2.connect(database="erdgeist", user="postgres", password="", host="127.0.0.1")
+        conn = psycopg.connect(dbname="tbuch", user="postgres", password="", host="127.0.0.1")
         cur = conn.cursor()
-        print (query + where + ' LIMIT 10000')
+        print (query + where + ' LIMIT ' + limit)
         pprint.pprint( params )
 
-        cur.execute(query + where + ' LIMIT 10000', params)
+        cur.execute(query + where + ' LIMIT ' + limit, params)
         rows = cur.fetchall()
     else:
-        rows = [[0,0,0,{},{},{},{},{},{},{},{},{},{}]]
+        rows = []
+
+    return render_template('facebook.html', rows=rows, request=request, rv=rv)
+
+def column_and_op_to_where(column, operator):
+    return "{0} {1} %s ".format(column, {'equals': '=', 'equalsnot': '<>', 'contains': 'ILIKE', 'containsnot': 'NOT ILIKE', 'beginswith': 'LIKE', 'beginslike': 'ILIKE'}[operator])
+
+def search_and_operator_to_search(search, operator):
+    if operator in ['contains','containsnot']:
+        search = "%" + search + "%"
+    if operator in ['beginswith', 'beginslike']:
+        search = search + "%"
+    return search
 
-    return render_template('index.html', rows=rows, request=request, convert=Convert_jinja_object())
 
 if __name__ == "__main__":
     parser = ArgumentParser(description="AVON")
diff --git a/requirements.txt b/requirements.txt
index 7ed34e8..40d4446 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -1,12 +1,15 @@
-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
+certifi
+chardet
+Click
+Flask
+idna
+itsdangerous
+Jinja2
+MarkupSafe
+psycopg
+psycopg-c
+requests
+typing_extensions
+urllib3
+Werkzeug
+
diff --git a/templates/index.html b/templates/index.html
index cf4e616..955d813 100644
--- a/templates/index.html
+++ b/templates/index.html
@@ -5,39 +5,89 @@
   td { vertical-align: top; }
   tr.color_1 { background: #ccc; }
   table { border: none; }
+  #year-slider { margin: 0.3em; width: 50vw; }
+  #slider-container { display: inline; padding: 0.3em; }
 </style>
+<link rel="stylesheet" type="text/css" href="static/nouislider.min.css">
+<script src="static/nouislider.min.js"></script>
 </head>
 <body>
-<form action="/" METHOD="POST">
+<div>Jahre: <div id="slider-container"><div id="year-slider" class="search_entry"></div></div>
+<div id="main-form"><form action="/" METHOD="POST">
+  {% set yrmin = rv.get('year_range_min', 0) | int %}
+  {% set yrmax = rv.get('year_range_max', convert.year_map | length - 1 ) | int %}
+  <label for="year-range-value-min">Ab Jahr</label>
+  <select id="year-range-value-min" name="year_range_min">
+    {%- for year in convert.year_map -%}
+      <option value="{{loop.index - 1}}" {% if loop.index - 1 == yrmin %} selected="selected"{% endif %}>{{year}}</option>
+    {%- endfor -%}
+  </select>
+  <label for="year-range-value-max">Bis Jahr</label>
+  <select id="year-range-value-max" name="year_range_max">
+    {%- for year in convert.year_map -%}
+      <option value="{{loop.index - 1}}" {% if loop.index - 1 == yrmax %} selected="selected"{% endif %}>{{year}}</option>
+    {%- endfor -%}
+  </select>
+
+  <!--p><b>Wir importieren momentan das neue Telefonbuch Herbst 2025. Wahrscheinlich wird die Suche solange nicht korrekt funktionieren.</b></p-->
+
   {%- 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', '') %}
+      <select name="s{{ field }}_col">
+        {% set column_search = rv.get('s'+(field|string)+'_val', '') %}
         {% 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') %}
+          {% set column_val = rv.get('s'+(field|string)+'_col') %}
+          {% set operator_val = rv.get('s'+(field|string)+'_op') %}
+          {% set any_val = rv.get('s'+(field|string)+'_any') %}
         {% else %}
           {% set column_val = ['nachname', 'vorname', 'ort', 'rufnummer'][field-1] %}
-          {% set operator_val = ['equals', 'contains', 'equals', 'contains'][field-1] %}
+          {% set operator_val = ['equals', 'equals', 'equals', 'beginswith'][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'] ] -%}
+      <select name="s{{ field }}_op">
+        {%- for operator in [ [ 'equals', 'equals'], ['beginswith', 'begins with'], ['beginslike', 'begins like'], ['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', '') }}">
+      <input type="checkbox" name="s{{ field }}_any" {%- if any_val == 'on' -%}checked="{%- endif -%}">any</input>
+      <input type="text" name="s{{ field }}_val" value="{{ rv.get('s'+(field|string)+'_val', '') }}">
     </div>
   {%- endfor -%}
+  <input type="checkbox" value="on" name="huge" id="huge" {%- if rv.get('huge', '') == "on" -%}checked{%- endif -%}>
+  <label for="huge">unlimited</label>
   <input type="submit" value="Search">
 </form>
-{% if rows | length > 0 -%}<h3>Found {{ rows | length }} matches.</h3>{% endif %}
+<script>
+  var slider = document.getElementById('year-slider');
+  var dropdownmin = document.getElementById('year-range-value-min');
+  var dropdownmax = document.getElementById('year-range-value-max');
+  noUiSlider.create(slider, {
+    start: [ {{ yrmin }}, {{ yrmax }} ],
+    connect: true,
+    step: 1,
+    range: { 'min': 0, 'max': {{ convert.year_map | length - 1}} },
+  });
+  var year_map = [
+  {%- for year in convert.year_map -%}
+    '{{ year }}',
+  {%- endfor -%}
+  ];
+  var year_range_display = document.getElementById('year-range-display');
+  var year_range_value_min = document.getElementById('year-range-value-min');
+  var year_range_value_max = document.getElementById('year-range-value-max');
+  slider.noUiSlider.on('update', function (values, handle) {
+    year_range_value_min.value = Math.floor(values[0]);
+    year_range_value_max.value = Math.floor(values[1]);
+  });
+  dropdownmin.onchange = function() { slider.noUiSlider.set( [this.value, null]); }
+  dropdownmax.onchange = function() { slider.noUiSlider.set( [null, this.value]); }
+</script>
+</div>
+<h3>Found {{ rows | length }} matches for your <a href="{{ url_for(request.endpoint,**rv) }}">query</a>. <small>You may also try <a href="/facebook">the facebook search</a></small>.</h3> <!-- *dict(request.values)) }}">query</a>.</h3-->
 {% set color = { 'value': 0 } %}
 <table>
   <thead><td>flags</td><td>zip</td></thead>
@@ -47,7 +97,7 @@
     {% 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>
+        <td rowspan="{{span}}">{{ convert.multirange_to_years(row[0]) }} : {{ convert.multirange_to_years(row[1]) }} : {{ convert.multirange_to_years(row[2]) }}</td>
       {%- endif -%}
       {%- for c in range(3, 16) -%}
         <td>{{ row[c][r] or '' }}</td>