From b7bcefdffaec476dcc7d67610c7007e90cc89908 Mon Sep 17 00:00:00 2001
From: Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com>
Date: Sun, 30 Jan 2022 12:18:51 +0100
Subject: add a feature to display the youngest registered persons

---
 main.py                         | 88 +++++++++++++++++++++++++++++++++++++++++
 templates/trivia.html           |  1 +
 templates/youngest_persons.html | 31 +++++++++++++++
 3 files changed, 120 insertions(+)
 create mode 100644 templates/youngest_persons.html

diff --git a/main.py b/main.py
index 5eae170..2222e4c 100644
--- a/main.py
+++ b/main.py
@@ -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
-- 
cgit