From 632cadaf7e460b9fa24de6cde1b5abe9b36ed4a5 Mon Sep 17 00:00:00 2001 From: Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com> Date: Tue, 16 Nov 2021 00:19:31 +0100 Subject: new trivia functions --- main.py | 63 ++++++++++++++++++++++++++ templates/longest_registered_executives.html | 29 ++++++++++++ templates/longest_registered_shareholders.html | 29 ++++++++++++ templates/oldest_shareholders.html | 29 ++++++++++++ templates/trivia.html | 3 ++ 5 files changed, 153 insertions(+) create mode 100644 templates/longest_registered_executives.html create mode 100644 templates/longest_registered_shareholders.html create mode 100644 templates/oldest_shareholders.html diff --git a/main.py b/main.py index a4d8293..d9e5d2e 100644 --- a/main.py +++ b/main.py @@ -504,6 +504,21 @@ def find_most_common_degree(): most_common_degree_after = count_common_degrees("after") return render_template("most_common_degree.html", most_common_degree_before = most_common_degree_before, most_common_degree_after = most_common_degree_after) +@app.route("/oldest_shareholders", methods=['GET', 'POST']) +def find_oldest_shareholder(): + oldest_shareholders_list = count_oldest_shareholders() + return render_template("oldest_shareholders.html", oldest_shareholders_list=oldest_shareholders_list) + +@app.route("/longest_registered_shareholders", methods=['GET', 'POST']) +def find_longest_registered_shareholders(): + longest_registered_shareholders_list = count_longest_registered_shareholders() + return render_template("longest_registered_shareholders.html", longest_registered_shareholders_list=longest_registered_shareholders_list) + +@app.route("/longest_registered_executives", methods=['GET', 'POST']) +def find_longest_registered_executives(): + longest_registered_executives_list = count_longest_registered_executives() + return render_template("longest_registered_executives.html", longest_registered_executives_list=longest_registered_executives_list) + def count_number_entries(): engine = create_engine('sqlite:///justice.db', echo=True) conn = engine.connect() @@ -609,6 +624,54 @@ def count_oldest_companies(): oldest_companies.append((selected_company[0].nazev, selected_company[0].zapis, selected_company[0].ico)) return oldest_companies +def count_oldest_shareholders(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT jmeno, prijmeni, datum_naroz, spolecnici.zapis_datum, company_id, fyzicke_osoby.adresa_id from fyzicke_osoby INNER JOIN spolecnici ON spolecnici.spolecnik_fo_id=fyzicke_osoby.id WHERE vymaz_datum = 0 ORDER BY CASE WHEN datum_naroz = 0 THEN '2099-10-10' ELSE datum_naroz END ASC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + oldest_shareholders = [] + for elem in result: + qry = Company.query + qry = qry.filter(Company.id == elem[4]) + selected_company = qry.all() + qry = Adresy_v2.query + qry = qry.filter(Adresy_v2.id == elem[5]) + selected_address = qry.all() + oldest_shareholders.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3])) + return oldest_shareholders + +def count_longest_registered_shareholders(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT jmeno, prijmeni, datum_naroz, spolecnici.zapis_datum, company_id, fyzicke_osoby.adresa_id from fyzicke_osoby INNER JOIN spolecnici ON spolecnici.spolecnik_fo_id=fyzicke_osoby.id WHERE vymaz_datum = 0 ORDER BY CASE WHEN zapis_datum = 0 THEN '2099-10-10' ELSE zapis_datum END ASC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + longest_registered_shareholders = [] + for elem in result: + qry = Company.query + qry = qry.filter(Company.id == elem[4]) + selected_company = qry.all() + qry = Adresy_v2.query + qry = qry.filter(Adresy_v2.id == elem[5]) + selected_address = qry.all() + longest_registered_shareholders.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3])) + return longest_registered_shareholders + +def count_longest_registered_executives(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT jmeno, prijmeni, datum_naroz, statutarni_organ_clen_relation.zapis_datum, statutarni_organ_id, fyzicke_osoby.adresa_id from fyzicke_osoby INNER JOIN statutarni_organ_clen_relation ON statutarni_organ_clen_relation.osoba_id=fyzicke_osoby.id WHERE vymaz_datum = 0 ORDER BY CASE WHEN zapis_datum = 0 THEN '2099-10-10' ELSE zapis_datum END ASC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + longest_registered_shareholders = [] + for elem in result: + qry = Company.query + qry = qry.join(Statutarni_Organ_Association, Company.statutarni_organ_text) + qry = qry.filter(Statutarni_Organ_Association.id == elem[4]) + selected_company = qry.all() + qry = Adresy_v2.query + qry = qry.filter(Adresy_v2.id == elem[5]) + selected_address = qry.all() + longest_registered_shareholders.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3])) + return longest_registered_shareholders if __name__ == '__main__': app.run() \ No newline at end of file diff --git a/templates/longest_registered_executives.html b/templates/longest_registered_executives.html new file mode 100644 index 0000000..281c3fb --- /dev/null +++ b/templates/longest_registered_executives.html @@ -0,0 +1,29 @@ +{% include 'header.html' %} + +

Nejdéle registrovaní statutáři:

+ + + + + + + + + + + + + {% for i in range (longest_registered_executives_list|length) %} + + + + + + + + + {% endfor %} + +
#Jméno a příjmeníDatum narozeníAdresaVe společnostiDatum zápisu
{{ i + 1 }}{% if longest_registered_executives_list[i][0] != "0" %} {{ longest_registered_executives_list[i][0] }} {% endif %}{{ longest_registered_executives_list[i][1] }}{{ longest_registered_executives_list[i][2] }}{{ longest_registered_executives_list[i][5] }}{{ longest_registered_executives_list[i][3] }}, IČ: {{ longest_registered_executives_list[i][4] }}{{ longest_registered_executives_list[i][6] }}
+ +{% include 'footer.html' %} \ No newline at end of file diff --git a/templates/longest_registered_shareholders.html b/templates/longest_registered_shareholders.html new file mode 100644 index 0000000..86e572d --- /dev/null +++ b/templates/longest_registered_shareholders.html @@ -0,0 +1,29 @@ +{% include 'header.html' %} + +

Nejdéle registrovaní společníci:

+ + + + + + + + + + + + + {% for i in range (longest_registered_shareholders_list|length) %} + + + + + + + + + {% endfor %} + +
#Jméno a příjmeníDatum narozeníAdresaVe společnostiDatum zápisu
{{ i + 1 }}{% if longest_registered_shareholders_list[i][0] != "0" %} {{ longest_registered_shareholders_list[i][0] }} {% endif %}{{ longest_registered_shareholders_list[i][1] }}{{ longest_registered_shareholders_list[i][2] }}{{ longest_registered_shareholders_list[i][5] }}{{ longest_registered_shareholders_list[i][3] }}, IČ: {{ longest_registered_shareholders_list[i][4] }}{{ longest_registered_shareholders_list[i][6] }}
+ +{% include 'footer.html' %} \ No newline at end of file diff --git a/templates/oldest_shareholders.html b/templates/oldest_shareholders.html new file mode 100644 index 0000000..02767af --- /dev/null +++ b/templates/oldest_shareholders.html @@ -0,0 +1,29 @@ +{% include 'header.html' %} + +

Nejstarší společníci:

+ + + + + + + + + + + + + {% for i in range (oldest_shareholders_list|length) %} + + + + + + + + + {% endfor %} + +
#Jméno a příjmeníDatum narozeníAdresaVe společnostiDatum zápisu
{{ i + 1 }}{% if oldest_shareholders_list[i][0] != "0" %} {{ oldest_shareholders_list[i][0] }} {% endif %}{{ oldest_shareholders_list[i][1] }}{{ oldest_shareholders_list[i][2] }}{{ oldest_shareholders_list[i][5] }}{{ oldest_shareholders_list[i][3] }}, IČ: {{ oldest_shareholders_list[i][4] }}{{ oldest_shareholders_list[i][6] }}
+ +{% include 'footer.html' %} \ No newline at end of file diff --git a/templates/trivia.html b/templates/trivia.html index 43e82ab..bb4e0b5 100644 --- a/templates/trivia.html +++ b/templates/trivia.html @@ -11,5 +11,8 @@

Seznam nejčastějších předmětů podnikání

Seznam nejčastějších předmětů činnosti

Seznam nejčastějších titulů

+

Seznam nejstarších společníků

+

Seznam nejdéle registrovaných společníků

+

Seznam nejdéle registrovaných statutárů

{% include 'footer.html' %} \ No newline at end of file -- cgit