From ff8a99329c9b79b3c5c8427d8f830821f13ccd32 Mon Sep 17 00:00:00 2001 From: erdgeist Date: Sat, 28 Feb 2026 00:23:20 +0100 Subject: multirange-ify years --- avon.py | 154 ++++++++++++++++++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 132 insertions(+), 22 deletions(-) (limited to 'avon.py') 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") -- cgit v1.2.3