Adding some trivia

8 files changed, 161 insertions, 12 deletions

diff --git a/db_setup.py b/db_setup.py
index 2944ef0..24e44de 100644
--- a/db_setup.py
+++ b/db_setup.py
@@ -10,7 +10,7 @@ from sqlalchemy import create_engine
 from sqlalchemy.orm import scoped_session, sessionmaker
 from sqlalchemy.ext.declarative import declarative_base
 
-engine = create_engine('sqlite:///justice2.db', convert_unicode=True)
+engine = create_engine('sqlite:///justice.db', convert_unicode=True)
 db_session = scoped_session(sessionmaker(autocommit=False,
                                          autoflush=False,
                                          bind=engine))
diff --git a/justice_main.py b/justice_main.py
index 805e1a2..16b9a84 100644
--- a/justice_main.py
+++ b/justice_main.py
@@ -3,18 +3,12 @@ from download_files import download_data
 from update_db import update_DB

 

 def main():

-    # typy_po = ["as", "sro", "vos", "ks", "dr", "zajzdrpo", "zahrfos", "ustav", "svj", "spolek", "prisp", "pobspolek",

-    #                 "oszpo", "osznadf", "osznad", "orgzam", "odbororg", "nadf", "nad", "evrspol", "evrhzs", "evrdrspol"]

-    # soudy = ["praha", "plzen", "brno", "ceske_budejovice", "hradec_kralove", "ostrava", "usti_nad_labem"]

+    typy_po = ["as", "sro", "vos", "ks", "dr", "zajzdrpo", "zahrfos", "ustav", "svj", "spolek", "prisp", "pobspolek",

+                    "oszpo", "osznadf", "osznad", "orgzam", "odbororg", "nadf", "nad", "evrspol", "evrhzs", "evrdrspol"]

+    soudy = ["praha", "plzen", "brno", "ceske_budejovice", "hradec_kralove", "ostrava", "usti_nad_labem"]

     

-    typy_po = ["spolek"]

-    soudy = ["brno"]

-

     DB_name = "justice.db"

     create_DB(DB_name)

     # download_data(typy_po, soudy)

     update_DB(typy_po, soudy, DB_name)

-

-

-

 main()
\ No newline at end of file
diff --git a/main.py b/main.py
index 79fe6a6..a259134 100644
--- a/main.py
+++ b/main.py
@@ -8,8 +8,11 @@ from models import Zpusob_Jednani_Association, Zpusob_Jednani, Statutarni_Organ_
 from models import Prokurista_Association, Jediny_Akcionar_Association, Prokura_Common_Text_Association, Soudni_Zapisy, Ucel, Ucel_Association
 from models import Adresy_v2
 from tables import Results
+from sqlalchemy.sql import select
+from sqlalchemy.sql import text
+from sqlalchemy import create_engine
 
-init_db()
+# init_db()
 
 @app.route('/', methods=['GET', 'POST'])
 def index():
@@ -176,5 +179,75 @@ def extract_actual(ico):
     results = qry.all()
     return render_template("extract-actual.html", ico = ico, results = results)
 
+@app.route("/trivia", methods=['GET', 'POST'])
+def trivia():
+    number_entities = count_number_entries()
+    return render_template("trivia.html", number_entities = number_entities)
+
+@app.route("/most_common_addresses", methods=['GET', 'POST'])
+def find_most_common_addresses():
+    most_common_addresses = count_common_addresses()
+    return render_template("most_common_addresses.html", most_common_addresses = most_common_addresses)    
+
+@app.route("/oldest_companies", methods=['GET', 'POST'])
+def find_oldest_companies():
+    oldest_companies = count_oldest_companies()
+    return render_template("oldest_companies.html", oldest_companies = oldest_companies)
+
+@app.route("/most_common_purpose", methods=['GET', 'POST'])
+def find_most_common_purpose():
+    most_common_purpose = count_common_purpose()
+    return render_template("most_common_purpose.html", most_common_purpose = most_common_purpose)    
+
+def count_number_entries():
+    engine = create_engine('sqlite:///justice.db', echo=True)
+    conn = engine.connect()
+    text_instruction = text("SELECT COUNT(id) FROM companies;")
+    entries_number = conn.execute(text_instruction).fetchall()
+    conn.close()
+    return entries_number[0][0]
+
+def count_common_addresses():
+    engine = create_engine('sqlite:///justice.db', echo=True)
+    conn = engine.connect()
+    text_instruction = text("SELECT sidlo_id, COUNT(`sidlo_id`) AS `value_occurrence` FROM sidlo_relation INNER JOIN adresy_v2 ON sidlo_relation.sidlo_id=adresy_v2.id WHERE vymaz_datum = 0 GROUP BY `sidlo_id` ORDER BY `value_occurrence` DESC LIMIT 100;")
+    result = conn.execute(text_instruction).fetchall()
+    addresses_frequency = []
+    for elem in result:
+        qry = Adresy_v2.query
+        qry = qry.filter(Adresy_v2.id == elem[0])
+        selected_address = qry.all()
+        addresses_frequency.append((selected_address[0], elem[1]))
+    conn.close()
+    return addresses_frequency
+
+def count_common_purpose():
+    engine = create_engine('sqlite:///justice.db', echo=True)
+    conn = engine.connect()
+    text_instruction = text("SELECT ucel_id, COUNT(`ucel_id`) AS `value_occurrence` FROM ucel_relation INNER JOIN ucel ON ucel_relation.ucel_id=ucel.id WHERE vymaz_datum = 0 GROUP BY `ucel_id` ORDER BY `value_occurrence` DESC LIMIT 100;")
+    result = conn.execute(text_instruction).fetchall()
+    addresses_frequency = []
+    for elem in result:
+        qry = Ucel.query
+        qry = qry.filter(Ucel.id == elem[0])
+        selected_purpose = qry.all()
+        addresses_frequency.append((selected_purpose[0].ucel, elem[1]))
+    conn.close()
+    return addresses_frequency    
+
+def count_oldest_companies():
+    engine = create_engine('sqlite:///justice.db', echo=True)
+    conn = engine.connect()
+    text_instruction = text("SELECT id from companies ORDER BY zapis ASC LIMIT 100;")
+    result = conn.execute(text_instruction).fetchall()
+    oldest_companies = []
+    for elem in result:
+        qry = Company.query
+        qry = qry.filter(Company.id == elem[0])
+        selected_company = qry.all()
+        oldest_companies.append((selected_company[0].nazev, selected_company[0].zapis, selected_company[0].ico)) 
+    return oldest_companies
+    
+
 if __name__ == '__main__':
     app.run()
\ No newline at end of file
diff --git a/templates/most_common_addresses.html b/templates/most_common_addresses.html
new file mode 100644
index 0000000..8c2277f
--- /dev/null
+++ b/templates/most_common_addresses.html
@@ -0,0 +1,23 @@
+{% include 'header.html' %}
+
+<h1>Nejčastější sídla:</h1>
+<table class="table table-hover">
+    <thead class="thead-dark">
+    <tr>
+        <th scope="col">#</th>
+        <th scope="col">Adresa</th>
+        <th scope="col">Frekvence</th>
+    </tr>
+    </thead>
+    <tbody>
+    {% for i in range (most_common_addresses|length) %}
+    <tr>
+        <th scope = "row">{{ i + 1 }}</th>
+        <td>{{ most_common_addresses[i][0] }}</td>
+        <td>{{ most_common_addresses[i][1] }}</td>
+    </tr>
+    {% endfor %}
+    </tbody>
+</table>
+
+{% include 'footer.html' %}
\ No newline at end of file
diff --git a/templates/most_common_purpose.html b/templates/most_common_purpose.html
new file mode 100644
index 0000000..99bdc77
--- /dev/null
+++ b/templates/most_common_purpose.html
@@ -0,0 +1,24 @@
+{% include 'header.html' %}
+
+<h1>Nejčastější účel:</h1>
+
+<table class="table table-hover">
+    <thead class="thead-dark">
+    <tr>
+        <th scope="col">#</th>
+        <th scope="col">Účel</th>
+        <th scope="col">Frekvence</th>
+    </tr>
+    </thead>
+    <tbody>
+    {% for i in range (most_common_purpose|length) %}
+    <tr>
+        <th scope = "row">{{ i + 1 }}</th>
+        <td>{{ most_common_purpose[i][0] }}</td>
+        <td>{{ most_common_purpose[i][1] }}</td>
+    </tr>
+    {% endfor %}
+    </tbody>
+</table>
+
+{% include 'footer.html' %}
\ No newline at end of file
diff --git a/templates/oldest_companies.html b/templates/oldest_companies.html
new file mode 100644
index 0000000..443ad1c
--- /dev/null
+++ b/templates/oldest_companies.html
@@ -0,0 +1,23 @@
+{% include 'header.html' %}
+
+<h1>Nejstarší společnosti:</h1>
+<table class="table table-hover">
+    <thead class="thead-dark">
+    <tr>
+        <th scope="col">#</th>
+        <th scope="col">Jméno</th>
+        <th scope="col">Datum založení</th>
+    </tr>
+    </thead>
+    <tbody>
+    {% for i in range (oldest_companies|length) %}
+    <tr>
+        <th scope = "row">{{ i + 1 }}</th>
+        <td><a href="{{ oldest_companies[i][2] }}">{{ oldest_companies[i][0] }}</a></td>
+        <td>{{ oldest_companies[i][1] }}</td>
+    </tr>
+    {% endfor %}
+    </tbody>
+</table>
+
+{% include 'footer.html' %}
\ No newline at end of file
diff --git a/templates/trivia.html b/templates/trivia.html
new file mode 100644
index 0000000..ced2c75
--- /dev/null
+++ b/templates/trivia.html
@@ -0,0 +1,13 @@
+{% include 'header.html' %}
+
+<h1>Zajímavosti z rejstříku</h1>
+
+<p>Počet registrovaných osob: {{ number_entities }}</p>
+
+<p>Další zajímvavé údaje z obchodního resjtříku:</p>
+<p><a href="/most_common_addresses">Seznam nejčastějších sídel</a></p>
+<p><a href="/oldest_companies">Seznam nejstarších společností</a></p>
+<p><a href="/most_common_purpose">Seznam nejčastějších účelů</a></p>
+
+
+{% include 'footer.html' %}
\ No newline at end of file
diff --git a/todolist.html b/todolist.html
index 8b83207..772d8e6 100644
--- a/todolist.html
+++ b/todolist.html
@@ -2,7 +2,6 @@
     <li>Add a feature to display legal entities as members of corporate bodies.</li>
     <li>Add a feature to display other types of ownerhsip interests (joint onwership interest, vacant ownership interest).</li>
     <li>Refactor excessive duplications in the main code.</li>
-    <li>Add some other minor data to the databases - especially those relating to non-profit companies.</li>
     <li>Check how to make diacritics work in searches.</li>
     <li>Write some documentation :)</li>
 </ol>
\ No newline at end of file