diff options
| -rw-r--r-- | main.py | 88 | ||||
| -rw-r--r-- | templates/trivia.html | 1 | ||||
| -rw-r--r-- | templates/youngest_persons.html | 31 |
3 files changed, 120 insertions, 0 deletions
@@ -518,6 +518,12 @@ def find_longest_registered_persons(): sorted_list = sorted(connected_list, key=lambda elem: elem[6]) return render_template("longest_registered_persons.html", longest_registered_persons_list=sorted_list[:100]) +@app.route("/youngest_persons", methods=['GET', 'POST']) +def find_younges_persons(): + connected_list = count_youngest_shareholders() + count_youngest_executieves() + count_youngest_sole_shareholders() + count_youngest_prokurists() + count_youngest_supervisory_members() + sorted_list = sorted(connected_list, key=lambda elem: elem[2], reverse=True) + return render_template("youngest_persons.html", youngest_persons_list=sorted_list[:100]) + def count_number_entries(): engine = create_engine('sqlite:///justice.db', echo=True) conn = engine.connect() @@ -704,6 +710,88 @@ def count_oldest_supervisory_members(): oldest_supervisory_members.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3], "člen dozorčí rady")) return oldest_supervisory_members +def count_youngest_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 '1111-10-10' ELSE datum_naroz END DESC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + youngest_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() + youngest_shareholders.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3], "společník")) + return youngest_shareholders + +def count_youngest_executieves(): + 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 datum_naroz = 0 THEN '1111-10-10' ELSE datum_naroz END DESC LIMIT 100;") + results = conn.execute(text_instruction).fetchall() + youngest_executives = [] + for elem in results: + 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() + youngest_executives.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3], "člen statutárního orgánu")) + return youngest_executives + +def count_youngest_prokurists(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT jmeno, prijmeni, datum_naroz, prokuriste.zapis_datum, company_id, fyzicke_osoby.adresa_id from fyzicke_osoby INNER JOIN prokuriste ON prokuriste.prokurista_fo_id=fyzicke_osoby.id WHERE vymaz_datum = 0 ORDER BY CASE WHEN datum_naroz = 0 THEN '1111-10-10' ELSE datum_naroz END DESC LIMIT 100;") + results = conn.execute(text_instruction).fetchall() + youngest_prokurists = [] + for elem in results: + 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() + youngest_prokurists.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3], "prokurista")) + return youngest_prokurists + +def count_youngest_sole_shareholders(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT jmeno, prijmeni, datum_naroz, jediny_akcionar.zapis_datum, company_id, fyzicke_osoby.adresa_id from fyzicke_osoby INNER JOIN jediny_akcionar ON jediny_akcionar.akcionar_fo_id=fyzicke_osoby.id WHERE vymaz_datum = 0 ORDER BY CASE WHEN datum_naroz = 0 THEN '1111-10-10' ELSE datum_naroz END DESC LIMIT 100;") + results = conn.execute(text_instruction).fetchall() + youngest_sole_shareholders = [] + for elem in results: + 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() + youngest_sole_shareholders.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3], "jediný akcionář")) + return youngest_sole_shareholders + +def count_youngest_supervisory_members(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT jmeno, prijmeni, datum_naroz, dr_organ_clen_relation.zapis_datum, dozorci_rada_id, fyzicke_osoby.adresa_id from fyzicke_osoby INNER JOIN dr_organ_clen_relation ON dr_organ_clen_relation.osoba_id=fyzicke_osoby.id WHERE vymaz_datum = 0 ORDER BY CASE WHEN datum_naroz = 0 THEN '1111-10-10' ELSE datum_naroz END DESC LIMIT 100;") + results = conn.execute(text_instruction).fetchall() + youngest_supervisory_members = [] + for elem in results: + qry = Company.query + qry = qry.join(Dozorci_Rada_Association, Company.dozorci_rada_text) + qry = qry.filter(Dozorci_Rada_Association.id == elem[4]) + selected_company = qry.all() + qry = Adresy_v2.query + qry = qry.filter(Adresy_v2.id == elem[5]) + selected_address = qry.all() + youngest_supervisory_members.append((elem[0], elem[1], elem[2], selected_company[0].nazev, selected_company[0].ico, selected_address[0], elem[3], "člen dozorčí rady")) + return youngest_supervisory_members + def count_longest_registered_shareholders(): engine = create_engine('sqlite:///justice.db', echo=True) conn = engine.connect() diff --git a/templates/trivia.html b/templates/trivia.html index 643ee2f..fcfc01e 100644 --- a/templates/trivia.html +++ b/templates/trivia.html @@ -12,6 +12,7 @@ <p><a href="/most_common_activity">Seznam nejčastějších předmětů činnosti</a></p> <p><a href="/most_common_degree">Seznam nejčastějších titulů</a></p> <p><a href="/oldest_persons">Seznam nejstarších osob</a></p> +<p><a href="/youngest_persons">Seznam nejmladších osob</a></p> <p><a href="/longest_registered_persons">Seznam nejdéle registrovaných osob</a></p> {% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/youngest_persons.html b/templates/youngest_persons.html new file mode 100644 index 0000000..d800340 --- /dev/null +++ b/templates/youngest_persons.html @@ -0,0 +1,31 @@ +{% include 'header.html' %} + +<h1>Nejmladší osoby:</h1> +<table class="table table-hover" style="width: auto"> + <thead class="thead-dark"> + <tr class="table-info"> + <th scope="col">#</th> + <th scope="col">Jméno a příjmení</th> + <th scope="col">Datum narození</th> + <th scope="col">Adresa</th> + <th scope="col">Funkce</th> + <th scope="col">Ve společnosti</th> + <th scope="col">Datum zápisu</th> + </tr> + </thead> + <tbody> + {% for i in range (youngest_persons_list|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td>{% if youngest_persons_list[i][0] != "0" %} {{ youngest_persons_list[i][0] }} {% endif %}{{ youngest_persons_list[i][1] }}</a></td> + <td>{{ youngest_persons_list[i][2] }}</td> + <td>{{ youngest_persons_list[i][5] }}</td> + <td>{{ youngest_persons_list[i][7] }}</td> + <td>{{ youngest_persons_list[i][3] }}, IČ: <a href="{{ youngest_persons_list[i][4] }}">{{ youngest_persons_list[i][4] }}</a></td> + <td>{{ youngest_persons_list[i][6] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% include 'footer.html' %}
\ No newline at end of file |