diff options
| -rw-r--r-- | db_setup.py | 2 | ||||
| -rw-r--r-- | justice_main.py | 12 | ||||
| -rw-r--r-- | main.py | 75 | ||||
| -rw-r--r-- | templates/most_common_addresses.html | 23 | ||||
| -rw-r--r-- | templates/most_common_purpose.html | 24 | ||||
| -rw-r--r-- | templates/oldest_companies.html | 23 | ||||
| -rw-r--r-- | templates/trivia.html | 13 | ||||
| -rw-r--r-- | todolist.html | 1 |
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 @@ -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 |