From cc86be247b6e10ccc0d21771c8d3583eb501b54e Mon Sep 17 00:00:00 2001 From: Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com> Date: Thu, 10 Jun 2021 23:36:54 +0200 Subject: updating the ubo feature --- db_creation.py | 27 +++++++++++++---------- justice_build.py | 8 +++---- main.py | 50 +++++++++++++++++++++++++++++++++++++++--- models.py | 11 +++++++--- templates/extract-actual.html | 5 ++++- templates/extract.html | 4 +++- templates/most_common_ubo.html | 24 ++++++++++++++++++++ templates/results_persons.html | 18 +++++++++++++++ templates/trivia.html | 1 + update_db.py | 22 +++++++++---------- 10 files changed, 134 insertions(+), 36 deletions(-) create mode 100644 templates/most_common_ubo.html diff --git a/db_creation.py b/db_creation.py index 55e7f32..cf9d504 100644 --- a/db_creation.py +++ b/db_creation.py @@ -363,22 +363,22 @@ def create_tables(conn): ubo = """ CREATE TABLE "ubo" ( "id" INTEGER NOT NULL UNIQUE, "company_id" INTEGER NOT NULL, - "UBO_id" INTEGER NOT NULL, + "UBO_id" INTEGER NOT NULL, + "adresa_id" INTEGER, "zapis_datum" DATE, "vymaz_datum" DATE, - "UBO_valid" TEXT, - "spravce" TEXT, - "T_TBC" TEXT, - "postaveni_jinak" TEXT, - "postaveni" TEXT, - "primaUcast" TEXT, - "primaUcastPodil" TEXT, - "rozdeleniProstredku" TEXT, - "slovni_vyjadreni" TEXT, - "urcenPoziciVeStatOrg" TEXT, - "email" TEXT, + "postaveni" TEXT, + "koncovyPrijemceText" TEXT, + "skutecnymMajitelemOd" TEXT, + "vlastniPodilNaProspechu" TEXT, + "vlastniPodilNaProspechu_typ" TEXT, + "vlastniPodilNaProspechu_textValue" TEXT, + "vlastniPodilNaHlasovani" TEXT, + "vlastniPodilNaHlasovani_typ" TEXT, + "vlastniPodilNaHlasovani_value" TEXT, FOREIGN KEY("company_id") REFERENCES "companies"("id"), FOREIGN KEY("UBO_id") REFERENCES "fyzicke_osoby"("id"), + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), PRIMARY KEY("id" AUTOINCREMENT) ); """ @@ -724,6 +724,9 @@ def create_indices(conn): "UBO_id" ); """ + ubo4 = """ CREATE INDEX "index ubo4" ON "ubo" ( + "UBO_adresa_id" +); """ ucel1 = """ CREATE INDEX "index ucel1" ON "ucel" ( "ucel" diff --git a/justice_build.py b/justice_build.py index 9a48f55..380d32b 100644 --- a/justice_build.py +++ b/justice_build.py @@ -11,7 +11,7 @@ def main(): for valid_file in valid_files: download_data(valid_file) for valid_file in valid_files: - modified_file_name = os.path.join(str(os.getcwd()), "data", valid_file + ".xml") - update_DB(modified_file_name, DB_name) - -main() + modified_file_name = os.path.join(str(os.getcwd()), "data", valid_file + ".xml") + update_DB(modified_file_name, DB_name) + +main() \ No newline at end of file diff --git a/main.py b/main.py index 5d43f97..deb9d51 100644 --- a/main.py +++ b/main.py @@ -1,7 +1,7 @@ from app import app from forms import GeneralSearchForm, JusticeSearchForm, CompanyForm, PersonSearchForm, EntitySearchForm from flask import flash, render_template, request, redirect -from models import Company, Insolvency_Events, Konkurz_Events, Predmet_Podnikani, Predmety_Podnikani_Association, Predmet_Cinnosti, Predmety_Cinnosti_Association +from models import Company, Insolvency_Events, Konkurz_Events, Predmet_Podnikani, Predmety_Podnikani_Association, Predmet_Cinnosti, Predmety_Cinnosti_Association, Ubo from models import Zakladni_Kapital, Akcie, Nazvy, Sidlo, Sidlo_Association, Pravni_Forma_Association_v2, Pravni_Formy, Statutarni_Organ_Association, Statutarni_Organy, Pocty_Clenu_Organu from models import Zpusob_Jednani_Association, Zpusob_Jednani, Statutarni_Organ_Clen_Association, Fyzicka_Osoba, Spolecnici_Association, Podily_Association, Druhy_Podilu, Pravnicka_Osoba from models import Prokurista_Association, Dozorci_Rada_Clen_Association, Jediny_Akcionar_Association, Prokura_Common_Text_Association, Soudni_Zapisy, Ucel, Ucel_Association @@ -425,6 +425,30 @@ def search_results_sidlo(adresa_id): table.border = True return render_template("results2.html", results=results, form=search, show_form = False) + +# UBO reults +@app.route('/results-ubo-', methods=['GET', 'POST']) +def search_results_ubo(ubo_id): + search = JusticeSearchForm(request.form) + + results = [] + qry = Company.query + qry = qry.join(Ubo, Company.ubo) + qry = qry.filter(Ubo.vymaz_datum == 0) + qry = qry.join(Fyzicka_Osoba, Ubo.jmeno) + qry = qry.filter(Fyzicka_Osoba.id == ubo_id) + results = qry.all() + + if not results: + flash('No results found!') + return redirect('/') + + else: + table = Results(results) + table.border = True + return render_template("results2.html", results=results, form=search, show_form = False) + + @app.route("/", methods=['GET', 'POST']) def extract(ico): qry = Company.query @@ -457,7 +481,12 @@ def find_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) + return render_template("most_common_purpose.html", most_common_purpose = most_common_purpose) + +@app.route("/most_common_ubo", methods=['GET', 'POST']) +def find_most_common_ubo(): + most_common_ubo = count_common_ubo() + return render_template("most_common_ubo.html", most_common_ubo = most_common_ubo) @app.route("/most_common_business", methods=['GET', 'POST']) def find_most_common_business(): @@ -537,7 +566,22 @@ def count_common_purpose(): selected_purpose = qry.all() addresses_frequency.append((selected_purpose[0].ucel, elem[1])) conn.close() - return addresses_frequency + return addresses_frequency + +def count_common_ubo(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT UBO_id, COUNT(`UBO_id`) AS `value_occurrence` FROM ubo INNER JOIN fyzicke_osoby ON ubo.UBO_id=fyzicke_osoby.id WHERE vymaz_datum = 0 GROUP BY `UBO_id` ORDER BY `value_occurrence` DESC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + ubo_frequency = [] + for elem in result: + qry = Fyzicka_Osoba.query + qry = qry.filter(Fyzicka_Osoba.id == elem[0]) + selected_ubo = qry.all() + # ubo_frequency.append((selected_ubo[0].jmeno + " " + selected_ubo[0].prijmeni, elem[1], elem[0])) + ubo_frequency.append((selected_ubo[0], elem[1], elem[0])) + conn.close() + return ubo_frequency def count_common_degrees(method): engine = create_engine('sqlite:///justice.db', echo=True) diff --git a/models.py b/models.py index 7263177..6c1b84c 100644 --- a/models.py +++ b/models.py @@ -245,6 +245,7 @@ class Fyzicka_Osoba(db.Model): prokurista_association = db.relationship("Prokurista_Association") sole_shareholder_association = db.relationship("Jediny_Akcionar_Association") supervisory_board_member_association = db.relationship("Dozorci_Rada_Clen_Association") + ubo_association = db.relationship("Ubo") adresa = db.relationship("Adresy_v2") def get_name(self): @@ -304,10 +305,14 @@ class Ubo(db.Model): __tablename__ = "ubo" id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) - UBO_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id')) zapis_datum = db.Column(MyType) vymaz_datum = db.Column(MyType) - slovni_vyjadreni = db.Column(db.String) + UBO_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id')) + adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id')) + adresa = db.relationship("Adresy_v2") + postaveni = db.Column(db.String) + koncovyPrijemceText = db.Column(db.String) + skutecnymMajitelemOd = db.Column(MyType) jmeno = db.relationship("Fyzicka_Osoba") company = db.relationship("Company") @@ -458,7 +463,7 @@ class Adresy_v2(db.Model): joined_address += self.psc + " " if self.obec != "0" and self.obec != None: joined_address += self.obec - if (self.stat != "Česká republika") and (self.stat != "Česká republika - neztotožněno"): + if (self.stat != "Česká republika") and (self.stat != "Česká republika - neztotožněno") and (self.stat != "0"): joined_address += ", " + self.stat return joined_address diff --git a/templates/extract-actual.html b/templates/extract-actual.html index a1f0241..6bc51fe 100644 --- a/templates/extract-actual.html +++ b/templates/extract-actual.html @@ -410,7 +410,10 @@ {% for i in range (ubo_notes|length) %} - {{ubo_notes[i].jmeno }}
{{ubo_notes[i].jmeno.adresa }} {% if ubo_notes[i].slovni_vyjadreni != "0" %}
{{ ubo_notes[i].slovni_vyjadreni }}
{% endif %} + {{ubo_notes[i].jmeno }} {{ubo_notes[i].jmeno.adresa }} + {% if ubo_notes[i].postaveni != "0" %}
{{ ubo_notes[i].postaveni }}{% endif %} + {% if ubo_notes[i].koncovyPrijemceText != "0" %}
{{ ubo_notes[i].koncovyPrijemceText }}{% endif %} + {% endfor %} {% endif %} diff --git a/templates/extract.html b/templates/extract.html index 6ff31de..17bdd73 100644 --- a/templates/extract.html +++ b/templates/extract.html @@ -489,7 +489,9 @@ {% set underlne_style_open = "" %} {% set underlne_style_close = "" %} {% endif %} - {{ underlne_style_open|safe }} {{row.ubo[i].jmeno }}
{{ubo_notes[i].jmeno.adresa }} {% if row.ubo[i].slovni_vyjadreni != "0" %}
{{row.ubo[i].slovni_vyjadreni }}
{% endif %} {{ underlne_style_close|safe }} + {{ underlne_style_open|safe }} {{row.ubo[i].jmeno }} {{row.ubo[i].jmeno.adresa}} + {% if row.ubo[i].postaveni != "0" %}
{{row.ubo[i].postaveni }}{% endif %} + {% if row.ubo[i].koncovyPrijemceText != "0" %}
{{row.ubo[i].koncovyPrijemceText }}{% endif %} {{ underlne_style_close|safe }} {{ underlne_style_open|safe }} Zapsáno: {{ row.ubo[i].zapis_datum }} {% if row.ubo[i].vymaz_datum != 0 %}
Vymazáno: {{ row.ubo[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }} {% endfor %} diff --git a/templates/most_common_ubo.html b/templates/most_common_ubo.html new file mode 100644 index 0000000..318d52f --- /dev/null +++ b/templates/most_common_ubo.html @@ -0,0 +1,24 @@ +{% include 'header.html' %} + +

Nejčastější koneční vlastníci:

+ + + + + + + + + + + {% for i in range (most_common_ubo|length) %} + + + + + + {% endfor %} + +
#JménoPočet společností
{{ i + 1 }}{{ most_common_ubo[i][0] }}{{ most_common_ubo[i][1] }}
+ +{% include 'footer.html' %} \ No newline at end of file diff --git a/templates/results_persons.html b/templates/results_persons.html index 65fac98..fc90e08 100644 --- a/templates/results_persons.html +++ b/templates/results_persons.html @@ -97,6 +97,24 @@ {% endif %} {% endfor %} + {% for elem in row.ubo_association %} + {% if selection_method == "actual_results" and elem.vymaz_datum != 0 %} + {% set xxx = [] %} + {% else %} + {% set my_dict = {} %} + {% set x = my_dict.__setitem__("adresa", elem.adresa) %} + {% set x = my_dict.__setitem__("funkce", "konečný vlastník") %} + {% set x = my_dict.__setitem__("nazev", elem.company.nazev) %} + {% set x = my_dict.__setitem__("ico", elem.company.ico) %} + {% set x = my_dict.__setitem__("oddil", elem.company.oddil) %} + {% set x = my_dict.__setitem__("vlozka", elem.company.vlozka) %} + {% set x = my_dict.__setitem__("soud", elem.company.soud) %} + {% set x = my_dict.__setitem__("zapis", elem.company.zapis) %} + {% set x = my_dict.__setitem__("pravni_forma", elem.company.current_legal_form_text()) %} + {% set test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + {% for elem in test_list %} {% if count.value % 2 == 0 %} {% set table_style = "table table-light" %} diff --git a/templates/trivia.html b/templates/trivia.html index 750706c..43e82ab 100644 --- a/templates/trivia.html +++ b/templates/trivia.html @@ -6,6 +6,7 @@

Další zajímvavé údaje z obchodního resjtříku:

Seznam nejčastějších sídel

Seznam nejstarších společností

+

Seznam nejčastějších konečných vlastníků

Seznam nejčastějších účelů

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

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

diff --git a/update_db.py b/update_db.py index 6c50b70..2f86c35 100644 --- a/update_db.py +++ b/update_db.py @@ -193,20 +193,18 @@ def find_UBO(c, ICO, elem2, conn, primary_sql_key, element): for elem in my_iter: zapis_datum = str(get_prop(elem, "zapisDatum")) vymaz_datum = str(get_prop(elem, "vymazDatum")) - UBO_valid = str(get_prop(elem, "hodnotaUdaje/valid")) - spravce = str(get_prop(elem, "hodnotaUdaje/spravce")) - T_TBC = str(get_prop(elem, "hodnotaUdaje/T")) - postaveni_jinak = str(get_prop(elem, "hodnotaUdaje/postaveniJinak")) - postaveni = str(get_prop(elem, "hodnotaUdaje/postaveni")) - primaUcast = str(get_prop(elem, "hodnotaUdaje/PrimaUcast")) - primaUcastPodil = str(get_prop(elem, "hodnotaUdaje/primaUcastPodil")) - rozdeleniProstredku = str(get_prop(elem, "hodnotaUdaje/PrimaUcast")) - slovni_vyjadreni = str(get_prop(elem, "hodnotaUdaje/slovniVyjadreni")) - urcenPoziciVeStatOrg = str(get_prop(elem, "hodnotaUdaje/urcenPoziciVeStatOrg")) - email = str(get_prop(elem, "hodnotaUdaje/email")) + postaveni = str(get_prop(elem, "hodnotaUdaje/postaveni")).split(";")[0] + koncovyPrijemceText = str(get_prop(elem, "hodnotaUdaje/koncovyPrijemceText")) + skutecnymMajitelemOd = str(get_prop(elem, "hodnotaUdaje/skutecnymMajitelemOd")) + vlastniPodilNaProspechu = str(get_prop(elem, "hodnotaUdaje/vlastniPodilNaProspechu")) + vlastniPodilNaProspechu_typ = str(get_prop(elem, "hodnotaUdaje/podilNaProspechu/typ")) + vlastniPodilNaProspechu_textValue = str(get_prop(elem, "hodnotaUdaje/podilNaProspechu/textValue")) + vlastniPodilNaHlasovani = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani")) + vlastniPodilNaHlasovani_typ = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani/typ")) + vlastniPodilNaHlasovani_value = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani/textValue")) adresa_id = find_sidlo(c, elem, primary_sql_key) UBO_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element, adresa_id) - c.execute("INSERT INTO ubo (company_id, UBO_id, zapis_datum, vymaz_datum, UBO_valid, spravce, T_TBC, postaveni_jinak, postaveni, primaUcast, primaUcastPodil, rozdeleniProstredku, slovni_vyjadreni, urcenPoziciVeStatOrg, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (primary_sql_key, UBO_id, zapis_datum, vymaz_datum, UBO_valid, spravce, T_TBC, postaveni_jinak, postaveni, primaUcast, primaUcastPodil, rozdeleniProstredku, slovni_vyjadreni, urcenPoziciVeStatOrg, email,)) + c.execute("INSERT INTO ubo (company_id, UBO_id, adresa_id, zapis_datum, vymaz_datum, postaveni, koncovyPrijemceText, skutecnymMajitelemOd, vlastniPodilNaProspechu, vlastniPodilNaProspechu_typ, vlastniPodilNaProspechu_textValue, vlastniPodilNaHlasovani, vlastniPodilNaHlasovani_typ, vlastniPodilNaHlasovani_value) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (primary_sql_key, UBO_id, adresa_id, zapis_datum, vymaz_datum, postaveni, koncovyPrijemceText, skutecnymMajitelemOd, vlastniPodilNaProspechu, vlastniPodilNaProspechu_typ, vlastniPodilNaProspechu_textValue, vlastniPodilNaHlasovani, vlastniPodilNaHlasovani_typ, vlastniPodilNaHlasovani_value,)) except Exception as f: print(f) -- cgit