1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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))
|
