diff options
| -rw-r--r-- | backup_DB.py | 6 | ||||
| -rw-r--r-- | db_creation.py | 146 | ||||
| -rw-r--r-- | justice_build.py | 10 | ||||
| -rw-r--r-- | main.py | 52 | ||||
| -rw-r--r-- | models.py | 62 | ||||
| -rw-r--r-- | templates/extract-actual.html | 178 | ||||
| -rw-r--r-- | templates/extract.html | 171 | ||||
| -rw-r--r-- | templates/most_common_ubo.html | 24 | ||||
| -rw-r--r-- | templates/results_persons.html | 18 | ||||
| -rw-r--r-- | templates/trivia.html | 1 | ||||
| -rw-r--r-- | todolist.html | 1 | ||||
| -rw-r--r-- | update_db.py | 130 |
12 files changed, 684 insertions, 115 deletions
diff --git a/backup_DB.py b/backup_DB.py new file mode 100644 index 0000000..fb4d9a1 --- /dev/null +++ b/backup_DB.py @@ -0,0 +1,6 @@ +import shutil +import os + +def backup_DB(): + os.makedirs("backup", exist_ok=True) + shutil.move("justice.db", "backup/justice.db") diff --git a/db_creation.py b/db_creation.py index a8fefe7..c3836aa 100644 --- a/db_creation.py +++ b/db_creation.py @@ -94,7 +94,7 @@ def create_tables(conn): "clenstvi_od" DATE, "clenstvi_do" DATE, "funkce" TEXT, - FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"), + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), FOREIGN KEY("dozorci_rada_id") REFERENCES "dozorci_rada_relation"("id"), FOREIGN KEY("osoba_id") REFERENCES "fyzicke_osoby"("id"), PRIMARY KEY("id" AUTOINCREMENT) @@ -138,7 +138,7 @@ def create_tables(conn): "akcionar_po_id" INTEGER, "akcionar_fo_id" INTEGER, "adresa_id" INTEGER, - FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"), + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), FOREIGN KEY("company_id") REFERENCES "companies"("id"), FOREIGN KEY("akcionar_po_id") REFERENCES "pravnicke_osoby"("id"), FOREIGN KEY("akcionar_fo_id") REFERENCES "fyzicke_osoby"("id"), @@ -195,9 +195,26 @@ def create_tables(conn): PRIMARY KEY("id" AUTOINCREMENT) ); """ + podilnici = """ CREATE TABLE "podilnici" ( + "id" INTEGER NOT NULL UNIQUE, + "podil_id" INTEGER, + "podilnik_fo_id" INTEGER, + "podilnik_po_id" INTEGER, + "zapis_datum" DATE, + "vymaz_datum" DATE, + "adresa_id" INTEGER, + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), + PRIMARY KEY("id" AUTOINCREMENT), + FOREIGN KEY("podilnik_fo_id") REFERENCES "fyzicke_osoby"("id"), + FOREIGN KEY("podilnik_po_id") REFERENCES "pravnicke_osoby"("id"), + FOREIGN KEY("podil_id") REFERENCES "spolecnici_spolecny_podil"("id") +); """ + podily = """ CREATE TABLE "podily" ( "id" INTEGER NOT NULL UNIQUE, - "spolecnik_id" INTEGER NOT NULL, + "spolecnik_id" INTEGER, + "uvolneny_podil_id" INTEGER, + "spolecny_podil_id" INTEGER, "zapis_datum" DATE, "vymaz_datum" DATE, "druh_podilu_id" INTEGER, @@ -209,6 +226,7 @@ def create_tables(conn): "splaceni_text" TEXT, FOREIGN KEY("druh_podilu_id") REFERENCES "druhy_podilu"("id"), FOREIGN KEY("spolecnik_id") REFERENCES "spolecnici"("id"), + FOREIGN KEY("uvolneny_podil_id") REFERENCES "spolecnici_uvolneny_podil"("id"), PRIMARY KEY("id" AUTOINCREMENT) ); """ @@ -292,7 +310,7 @@ def create_tables(conn): "prokurista_fo_id" INTEGER, "adresa_id" INTEGER, "text_prokurista" TEXT, - FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"), + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), FOREIGN KEY("prokurista_fo_id") REFERENCES "fyzicke_osoby"("id"), FOREIGN KEY("company_id") REFERENCES "companies"("id"), PRIMARY KEY("id" AUTOINCREMENT) @@ -318,12 +336,32 @@ def create_tables(conn): "vymaz_datum" DATE, "adresa_id" INTEGER, "text_spolecnik" TEXT, - FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"), + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), PRIMARY KEY("id" AUTOINCREMENT), FOREIGN KEY("spolecnik_fo_id") REFERENCES "fyzicke_osoby"("id"), FOREIGN KEY("company_id") REFERENCES "companies"("id") ); """ + spolecnici_uvolneny_podil = """ CREATE TABLE "spolecnici_uvolneny_podil" ( + "id" INTEGER NOT NULL UNIQUE, + "company_id" INTEGER NOT NULL, + "zapis_datum" DATE, + "vymaz_datum" DATE, + "text_uvolneny_podil" TEXT, + PRIMARY KEY("id" AUTOINCREMENT), + FOREIGN KEY("company_id") REFERENCES "companies"("id") + ) """ + + spolecnici_spolecny_podil = """ CREATE TABLE "spolecnici_spolecny_podil" ( + "id" INTEGER NOT NULL UNIQUE, + "company_id" INTEGER NOT NULL, + "zapis_datum" DATE, + "vymaz_datum" DATE, + "text_spolecny_podil" TEXT, + PRIMARY KEY("id" AUTOINCREMENT), + FOREIGN KEY("company_id") REFERENCES "companies"("id") + ) """ + statutarni_organ_clen_relation = """ CREATE TABLE "statutarni_organ_clen_relation" ( "id" INTEGER NOT NULL UNIQUE, "statutarni_organ_id" INTEGER NOT NULL, @@ -339,7 +377,7 @@ def create_tables(conn): "funkce" TEXT, FOREIGN KEY("osoba_id") REFERENCES "fyzicke_osoby"("id"), FOREIGN KEY("statutarni_organ_id") REFERENCES "statutarni_organ_relation"("id"), - FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"), + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), PRIMARY KEY("id" AUTOINCREMENT) ); """ @@ -360,6 +398,28 @@ def create_tables(conn): PRIMARY KEY("id" AUTOINCREMENT) ); """ + ubo = """ CREATE TABLE "ubo" ( + "id" INTEGER NOT NULL UNIQUE, + "company_id" INTEGER NOT NULL, + "UBO_id" INTEGER NOT NULL, + "adresa_id" INTEGER, + "zapis_datum" DATE, + "vymaz_datum" DATE, + "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) + ); """ + ucel = """ CREATE TABLE "ucel" ( "id" INTEGER NOT NULL, "ucel" TEXT NOT NULL UNIQUE, @@ -420,9 +480,9 @@ def create_tables(conn): ); """ list_of_tables = [companies, adresy_v2, akcie, dr_relation, dr_organ_clen_relation, druhy_podilu, fyzicke_osoby, insolvency_events, - jediny_akcionar, konkurz_events, nazvy, ostatni_skutecnosti, pocty_clenu_DR, pocty_clenu_organu, podily, pravni_formy, + jediny_akcionar, konkurz_events, nazvy, ostatni_skutecnosti, pocty_clenu_DR, pocty_clenu_organu, podily, podilnici, pravni_formy, pravni_formy_relation, pravnicke_osoby, predmety_cinnosti, predmety_cinnosti_relation, prdmety_podnikani, predmety_podnikani_relation, - prokura_common_texts, prokuriste, sidlo_relation, spolecnici, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, + prokura_common_texts, prokuriste, sidlo_relation, spolecnici, spolecnici_uvolneny_podil, spolecnici_spolecny_podil, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, ubo, ucel, ucel_relation, zakladni_kapital, zapis_soudy, zpusoby_jednani, zpusoby_jednani_relation] for elem in list_of_tables: try: @@ -548,6 +608,34 @@ def create_indices(conn): "spolecnik_id" ); """ + podily3 = """ CREATE INDEX "index podily3" ON "podily" ( + "uvolneny_podil_id" +); """ + + podily4 = """ CREATE INDEX "index podily4" ON "podily" ( + "spolecny_podil_id" +); """ + + podilnici1 = """ CREATE INDEX "index podilnici1" ON "podilnici" ( + "id" +); """ + + podilnici2 = """ CREATE INDEX "index podilnici2" ON "podilnici" ( + "podil_id" +); """ + + podilnici3 = """ CREATE INDEX "index podilnici3" ON "podilnici" ( + "podilnik_fo_id" +); """ + + podilnici4 = """ CREATE INDEX "index podilnici4" ON "podilnici" ( + "podilnik_po_id" +); """ + + podilnici5 = """ CREATE INDEX "index podilnici5" ON "podilnici" ( + "adresa_id" +); """ + pravni_formy = """ CREATE INDEX "index pravni_formy" ON "pravni_formy" ( "pravni_forma" ); """ @@ -672,6 +760,23 @@ def create_indices(conn): "adresa_id" ); """ + spolecnici_uvolneny_podil1 = """ CREATE INDEX "index uvolneny_podil1" on "spolecnici_uvolneny_podil" ( + "id" +); """ + + spolecnici_uvolneny_podil2 = """ CREATE INDEX "index uvolneny_podil2" on "spolecnici_uvolneny_podil" ( + "company_id" +); """ + + spolecnici_spolecny_podil1 = """ CREATE INDEX "index spolecny_podil1" on "spolecnici_spolecny_podil" ( + "id" +); """ + + spolecnici_spolecny_podil2 = """ CREATE INDEX "index spolecny_podil2" on "spolecnici_spolecny_podil" ( + "company_id" +); """ + + statutarni_organy = """ CREATE INDEX "index statutarn_organy" ON "statutarni_organy" ( "id", "statutarni_organ_text" @@ -689,6 +794,23 @@ def create_indices(conn): "company_id" ); """ + + ubo1 = """ CREATE INDEX "index ubo1" ON "ubo" ( + "id" +); """ + + ubo2 = """ CREATE INDEX "index ubo2" ON "ubo" ( + "company_id" +); """ + + ubo3 = """ CREATE INDEX "index ubo3" ON "ubo" ( + "UBO_id" +); """ + + ubo4 = """ CREATE INDEX "index ubo4" ON "ubo" ( + "UBO_adresa_id" +); """ + ucel1 = """ CREATE INDEX "index ucel1" ON "ucel" ( "ucel" ); """ @@ -749,7 +871,6 @@ def create_indices(conn): "osoba_id" ); """ - dr_relation_4 = """ CREATE INDEX "index dr clen relation4" ON "dr_organ_clen_relation" ( "pravnicka_osoba_id" ); """ @@ -761,13 +882,14 @@ def create_indices(conn): list_of_indices = [companies1, companies2, companies3, companies4, companies5, adresy1, adresy2, adresy3, akcie, akcie2, akcionari1, akcionari2, akcionari3, dr_clen_relation1, dr_clen_relation2, dr_relation, dr_relation2, dr_relation_3, dr_relation_4, insolvency1, insolvency2, konkurz1, konkurz2, nazvy1, nazvy2, nazvy3, ostatni_skutecnosti, ostatni_skutecnosti2, - pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, pravni_formy, pravni_formy_relation1, pravni_formy_relation2, + pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, podily3, podily4, podilnici1, podilnici2, podilnici3, podilnici4, podilnici5, + pravni_formy, pravni_formy_relation1, pravni_formy_relation2, predmety_cinnosti_relation1, predmety_cinnosti_relation2, predmety_cinnosti_relation3, predmety_podnikani_relation1, predmety_podnikani_relation2, predmety_podnikani_relation3, predmety_cinnosti1, predmety_cinnosti2, predmety_podnikani1, predmety_podnikani2, prokuriste1, prokuriste2, prokuriste3, prokuriste4, sidlo_relation1, sidlo_relation_2, sidlo_relation_3, soudni_zapis1, soudni_zapis2, spolecnici1, - spolecnici2, spolecnici3, spolecnici4, spolecnici5, statutarni_organy, statutarni_organy_relation1, statutarni_organy_relation2, + spolecnici2, spolecnici3, spolecnici4, spolecnici5, spolecnici_uvolneny_podil1, spolecnici_uvolneny_podil2, spolecnici_spolecny_podil1, spolecnici_spolecny_podil2, statutarni_organy, statutarni_organy_relation1, statutarni_organy_relation2, statutarni_organy_relation_3, statutarni_organy_relation_4, zakladni_kapital1, zakladni_kapital2, zpusob_jednani, zpusob_jednani_relation1, zpusob_jednani_relation2, - zpusob_jednani_relation3, pravnicke_osoby1, pravnicke_osoby2, pravnicke_osoby3, pravnicke_osoby4, statutarni_organy_relation_5, fyzicke_osoby1, ucel1, ucel2, ucel_relation1, ucel_relation2, ucel_relation3] + zpusob_jednani_relation3, pravnicke_osoby1, pravnicke_osoby2, pravnicke_osoby3, pravnicke_osoby4, statutarni_organy_relation_5, fyzicke_osoby1, ubo1, ubo2, ubo3, ubo4, ucel1, ucel2, ucel_relation1, ucel_relation2, ucel_relation3] i = 0 for elem in list_of_indices: i += 1 diff --git a/justice_build.py b/justice_build.py index 287e3e6..9acf1e7 100644 --- a/justice_build.py +++ b/justice_build.py @@ -1,17 +1,19 @@ from db_creation import create_DB
from download_files import download_data, get_valid_filenames
from update_db import update_DB
+from backup_DB import backup_DB
import os
def main():
DB_name = "justice.db"
+ # backup_DB()
create_DB(DB_name)
valid_files = get_valid_filenames()
os.makedirs("data", exist_ok=True)
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 @@ -1,11 +1,11 @@ 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 -from models import Adresy_v2 +from models import Adresy_v2, Uvolneny_Podil_Association, Spolecny_Podil_Association, Podilnici_Association from tables import Results from sqlalchemy.sql import select from sqlalchemy.sql import text @@ -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-<int:ubo_id>', 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("/<int:ico>", 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) @@ -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): @@ -300,6 +301,57 @@ class Spolecnici_Association(db.Model): podily = db.relationship("Podily_Association") company = db.relationship("Company") +class Podilnici_Association(db.Model): + __tablename__ = "podilnici" + id = db.Column(db.Integer, primary_key=True) + podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_spolecny_podil.id')) + podilnik_fo_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id')) + podilnik_po_id = db.Column(db.Integer, db.ForeignKey('pravnicke_osoby.id')) + zapis_datum = db.Column(MyType) + vymaz_datum = db.Column(MyType) + adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id')) + # text_podilnik = db.Column(db.String) + adresa = db.relationship("Adresy_v2") + jmeno = db.relationship("Fyzicka_Osoba") + oznaceni_po = db.relationship("Pravnicka_Osoba") + podily = db.relationship("Spolecny_Podil_Association") + +class Uvolneny_Podil_Association(db.Model): + __tablename__ = "spolecnici_uvolneny_podil" + id = db.Column(db.Integer, primary_key=True) + company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) + zapis_datum = db.Column(MyType) + vymaz_datum = db.Column(MyType) + text_uvolneny_podil = db.Column(db.String) + podily = db.relationship("Podily_Association") + company = db.relationship("Company") + +class Spolecny_Podil_Association(db.Model): + __tablename__ = "spolecnici_spolecny_podil" + id = db.Column(db.Integer, primary_key=True) + company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) + zapis_datum = db.Column(MyType) + vymaz_datum = db.Column(MyType) + text_spolecny_podil = db.Column(db.String) + podily = db.relationship("Podily_Association") + podilnici = db.relationship("Podilnici_Association") + company = db.relationship("Company") + +class Ubo(db.Model): + __tablename__ = "ubo" + id = db.Column(db.Integer, primary_key=True) + company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) + zapis_datum = db.Column(MyType) + vymaz_datum = db.Column(MyType) + 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") + class Prokurista_Association(db.Model): __tablename__ = "prokuriste" id = db.Column(db.Integer, primary_key=True) @@ -340,6 +392,8 @@ class Podily_Association(db.Model): __tablename__ = "podily" id = db.Column(db.Integer, primary_key=True) spolecnik_id = db.Column(db.Integer, db.ForeignKey('spolecnici.id')) + uvolneny_podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_uvolneny_podil.id')) + spolecny_podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_spolecny_podil.id')) zapis_datum = db.Column(MyType) vymaz_datum = db.Column(MyType) druh_podilu_id = db.Column(db.Integer, db.ForeignKey('druhy_podilu.id')) @@ -393,10 +447,13 @@ class Company(db.Model): statutarni_organ_text = db.relationship("Statutarni_Organ_Association") dozorci_rada_text = db.relationship("Dozorci_Rada_Association") spolecnici = db.relationship("Spolecnici_Association") + spolecnici_uvolneny_podil = db.relationship("Uvolneny_Podil_Association") + spolecnici_spolecny_podil = db.relationship("Spolecny_Podil_Association") prokurista = db.relationship("Prokurista_Association") prokura_common_text = db.relationship("Prokura_Common_Text_Association") jediny_akcionar = db.relationship("Jediny_Akcionar_Association") sidlo_text = db.relationship("Sidlo_Association") + ubo = db.relationship("Ubo") def current_legal_form_text(self): for elem in self.pravni_forma_text: @@ -446,7 +503,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 @@ -715,7 +772,8 @@ class Akcie(db.Model): elif self.akcie_podoba == "IMOBILIZOVANA": joined_share_descr += "v imobilizované podobě " - joined_share_descr += "ve jmenovité hodnotě " + self.akcie_hodnota_value + if self.akcie_hodnota_value != "0": + joined_share_descr += "ve jmenovité hodnotě " + self.akcie_hodnota_value if self.akcie_hodnota_typ == "KORUNY": joined_share_descr += "Kč" diff --git a/templates/extract-actual.html b/templates/extract-actual.html index 6bf6087..691dae3 100644 --- a/templates/extract-actual.html +++ b/templates/extract-actual.html @@ -49,6 +49,20 @@ {% endif %} {% endfor %} +{% set vacant_shares_notes = [] %} +{% for i in range (row.spolecnici_uvolneny_podil|length) %} + {% if row.spolecnici_uvolneny_podil[i].vymaz_datum == 0 %} + {% set vacant_shares_notes = vacant_shares_notes.append(row.spolecnici_uvolneny_podil[i]) %} + {% endif %} +{% endfor %} + +{% set common_shares_notes = [] %} +{% for i in range (row.spolecnici_spolecny_podil|length) %} + {% if row.spolecnici_spolecny_podil[i].vymaz_datum == 0 %} + {% set common_shares_notes = common_shares_notes.append(row.spolecnici_spolecny_podil[i]) %} + {% endif %} +{% endfor %} + {% set sole_shareholder_notes = [] %} {% for i in range (row.jediny_akcionar|length) %} {% if row.jediny_akcionar[i].vymaz_datum == 0 %} @@ -56,6 +70,13 @@ {% endif %} {% endfor %} +{% set ubo_notes = [] %} +{% for i in range (row.ubo|length) %} + {% if row.ubo[i].vymaz_datum == 0 %} + {% set ubo_notes = ubo_notes.append(row.ubo[i]) %} + {% endif %} +{% endfor %} + {% set shares_notes = [] %} {% for i in range (row.akcie|length) %} {% if row.akcie[i].vymaz_datum == 0 %} @@ -193,33 +214,9 @@ <!-- Display registered capital --> {% if zakladni_kapital_notes|length > 0 %} <tr> - {% if zakladni_kapital_notes[0].vklad_typ == "KORUNY" %} - {% set vklad_typ_symbol = "Kč" %} - {% set vklad_typ_description = zakladni_kapital_notes[0].vklad_hodnota %} - {% elif zakladni_kapital_notes[0].vklad_typ == "EURA" %} - {% set vklad_typ_symbol = "euro" %} - {% set vklad_typ_description = zakladni_kapital_notes[0].vklad_hodnota %} - {% else %} - {% set vklad_typ_symbol = "" %} - {% set vklad_typ_description = zakladni_kapital_notes[0].vklad_hodnota %} - {% endif %} - - {% if zakladni_kapital_notes[0].splaceni_typ == "KORUNY" %} - {% set splaceni_typ_symbol = "Kč" %} - {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %} - {% elif zakladni_kapital_notes[0].splaceni_typ == "PROCENTA" %} - {% set splaceni_typ_symbol = "%" %} - {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %} - {% elif zakladni_kapital_notes[0].splaceni_typ == "EURA" %} - {% set splaceni_typ_symbol = "euro" %} - {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %} - {% else %} - {% set splaceni_typ_symbol = "" %} - {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %} - {% endif %} <td>Základní kapitál:</td> - <td>{{ underlne_style_open|safe }} {{ vklad_typ_description }} {{ vklad_typ_symbol }} {% if splaceni_typ_description != "0" %} <br> Splaceno: {{ splaceni_typ_description }} {{ splaceni_typ_symbol }} {% endif %} {{ underlne_style_close|safe }}</td> + <td>{% for elem in zakladni_kapital_notes[0].my_rep() %} {{elem}}<br>{% endfor %}</td> </tr> {% endif %} <!-- END Display registered capital --> @@ -385,7 +382,7 @@ <!-- END Display sole shareholder --> <!-- Display shareholders --> - {% if spolecnici_notes|length > 0 %} + {% if spolecnici_notes|length > 0 or vacant_shares_notes|length > 0 or common_shares_notes|length > 0 %} <tr> <td>Společníci:</td> <td></td> @@ -406,44 +403,111 @@ {% for k in range (spolecnik_podily|length) %} <tr> - <td style = padding-left:4em>Podíl:</td> - <!-- Set type of deposit --> - {% if spolecnik_podily[k].vklad_typ == "KORUNY" %} - {% set vklad_typ_string = "Kč" %} - {% elif spolecnik_podily[k].vklad_typ == "EURA" %} - {% set vklad_typ_string = "Euro" %} - {% else %} - {% set vklad_typ_string = "" %} - {% endif %} - - <!-- Set type of repaid contribution --> - {% if spolecnik_podily[k].splaceni_typ == "KORUNY" %} - {% set splaceni_typ_string = "Kč" %} - {% elif spolecnik_podily[k].splaceni_typ == "PROCENTA" %} - {% set splaceni_typ_string = "%" %} - {% elif spolecnik_podily[k].splaceni_typ == "EURA" %} - {% set splaceni_typ_string = "Euro" %} - {% else %} - {% set splaceni_typ_string = "" %} - {% endif %} - - <!-- Set type of share --> - {% if spolecnik_podily[k].souhrn_typ == "PROCENTA" %} - {% set souhrn_typ_string = "%" %} - {% else %} - {% set souhrn_typ_string = "" %} - {% endif %} - <td>Vklad: {{ spolecnik_podily[k].vklad_text }} {{ vklad_typ_string }}<br> - Splaceno: {{ spolecnik_podily[k].splaceni_text }} {{ splaceni_typ_string }}<br> - {% if spolecnik_podily[k].souhrn_text != "0"%}Podíl: {{ spolecnik_podily[k].souhrn_text }} {{ souhrn_typ_string }}{% endif %} - {% if spolecnik_podily[k].druh_podilu.druh_podilu != "0" %}<br>Druh podílu: {{ spolecnik_podily[k].druh_podilu.druh_podilu }}{% endif %} + <td style = padding-left:4em>Podíl:</td> + <td> + {% for elem in spolecnik_podily[k].my_rep() %} + {{ elem }}<br> + {% endfor %} </td> </tr> {% endfor %} {% endfor %} + + <!-- Display vacant shares --> + {% for i in range (vacant_shares_notes|length) %} + <tr> + <td style = padding-left:2em>Uvolněný obchodní podíl:</td> + <td>{% if vacant_shares_notes[i].text_uvolneny_podil != "0" %}{{ vacant_shares_notes[i].text_uvolneny_podil }}{% endif %}</td> + </tr> + {% set vacant_shares = [] %} + {% for j in range (vacant_shares_notes[i].podily|length) %} + {% if vacant_shares_notes[i].podily[j].vymaz_datum == 0 %} + {% set vacant_shares = vacant_shares.append(vacant_shares_notes[i].podily[j]) %} + {% endif %} + {% endfor %} + + {% for k in range (vacant_shares|length) %} + <tr> + <td style = padding-left:4em>Podíl:</td> + <td> + {% for elem in vacant_shares[k].my_rep() %} + {{ elem }}<br> + {% endfor %} + </td> + </tr> + {% endfor %} + {% endfor %} + <!-- END Display vacant shares --> + + <!-- Display common shares --> + {% for i in range (common_shares_notes|length) %} + <tr> + <td style = padding-left:2em>Společný obchodní podíl:</td> + <td>{% if common_shares_notes[i].text_spolecny_podil != "0" %}{{ common_shares_notes[i].text_spolecny_podil }}{% endif %}</td> + </tr> + {% set common_shares = [] %} + {% for j in range (common_shares_notes[i].podily|length) %} + {% if common_shares_notes[i].podily[j].vymaz_datum == 0 %} + {% set common_shares = common_shares.append(common_shares_notes[i].podily[j]) %} + {% endif %} + {% endfor %} + + {% for k in range (common_shares|length) %} + <tr> + <td style = padding-left:4em>Podíl:</td> + <td> + {% for elem in common_shares[k].my_rep() %} + {{ elem }}<br> + {% endfor %} + </td> + </tr> + {% endfor %} + + {% set common_shares_shareholders = [] %} + {% for j in range (common_shares_notes[i].podilnici|length) %} + {% if common_shares_notes[i].podilnici[j].vymaz_datum == 0 %} + {% set common_shares_shareholders = common_shares_shareholders.append(common_shares_notes[i].podilnici[j]) %} + {% endif %} + {% endfor %} + + {% for k in range (common_shares_shareholders|length) %} + <tr> + <td style = padding-left:4em>Podílník:</td> + <td> + {% if common_shares_shareholders[k].podilnik_fo_id != None %} + {{ common_shares_shareholders[k].jmeno }} + {% else %} + {{ common_shares_shareholders[k].oznaceni_po.nazev }}{% if common_shares_shareholders[k].oznaceni_po.reg_cislo != 0 %}, reg č. {{ common_shares_shareholders[k].oznaceni_po.reg_cislo }}{% endif %}{% if common_shares_shareholders[k].oznaceni_po.ico != 0 %}, IČ <a href="/{{ common_shares_shareholders[k].oznaceni_po.ico }}">{{ common_shares_shareholders[k].oznaceni_po.ico }}</a>{% endif %} + {% endif %} + <br>{{ common_shares_shareholders[k].adresa }} + </td> + </tr> + {% endfor %} + + {% endfor %} + <!-- END Display common shares --> + {% endif %} <!-- END Display shareholders --> + <!-- Display UBO --> + {% if ubo_notes|length > 0 %} + <tr> + <td>Konečný vlastník:</td> + <td></td> + </tr> + {% for i in range (ubo_notes|length) %} + <tr> + <td></td> + <td>{{ubo_notes[i].jmeno }} {{ubo_notes[i].jmeno.adresa }} + {% if ubo_notes[i].postaveni != "0" %}<br>{{ ubo_notes[i].postaveni }}{% endif %} + {% if ubo_notes[i].koncovyPrijemceText != "0" %}<br>{{ ubo_notes[i].koncovyPrijemceText }}{% endif %} + </td> + </tr> + {% endfor %} + {% endif %} + <!-- END Display UBO --> + <!-- Display shares --> {% if shares_notes|length > 0 %} {% for i in range (shares_notes|length) %} diff --git a/templates/extract.html b/templates/extract.html index 0c4efd0..9eb1985 100644 --- a/templates/extract.html +++ b/templates/extract.html @@ -137,32 +137,6 @@ {% set underlne_style_close = "" %} {% endif %} - <!-- TODO: Have backend handle this --> - {% if row.zakladni_kapital[i].vklad_typ == "KORUNY" %} - {% set vklad_typ_symbol = "Kč" %} - {% set vklad_typ_description = row.zakladni_kapital[i].vklad_hodnota %} - {% elif row.zakladni_kapital[i].vklad_typ == "EURA" %} - {% set vklad_typ_symbol = "euro" %} - {% set vklad_typ_description = row.zakladni_kapital[i].vklad_hodnota %} - {% else %} - {% set vklad_typ_symbol = "" %} - {% set vklad_typ_description = row.zakladni_kapital[i].vklad_hodnota %} - {% endif %} - - {% if row.zakladni_kapital[i].splaceni_typ == "KORUNY" %} - {% set splaceni_typ_symbol = "Kč" %} - {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %} - {% elif row.zakladni_kapital[i].splaceni_typ == "PROCENTA" %} - {% set splaceni_typ_symbol = "%" %} - {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %} - {% elif row.zakladni_kapital[i].splaceni_typ == "EURA" %} - {% set splaceni_typ_symbol = "euro" %} - {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %} - {% else %} - {% set splaceni_typ_symbol = "" %} - {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %} - {% endif %} - <td>{{ underlne_style_open|safe }} {% for elem in row.zakladni_kapital[i].my_rep() %} {{elem}}<br>{% endfor %} {{ underlne_style_close|safe }}</td> <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.zakladni_kapital[i].zapis_datum }} {% if row.zakladni_kapital[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.zakladni_kapital[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> </tr> @@ -448,7 +422,7 @@ <!-- END Display sole shareholder --> <!-- Display shareholders --> - {% if row.spolecnici|length > 0 %} +{% if row.spolecnici|length > 0 or row.spolecnici_uvolneny_podil|length > 0 %} <tr> <td>Společníci:</td> <td></td> @@ -492,12 +466,155 @@ {% endfor %} {% endif %} <!-- END of individual ownership interests --> + </tr> + {% endfor %} + + + <!-- Insert vacant ownership interests --> + {% for i in range (row.spolecnici_uvolneny_podil|length) %} + <tr> + <td style = padding-left:2em>Uvolněný obchodní podíl:</td> + {% if row.spolecnici_uvolneny_podil[i].vymaz_datum != 0 %} + {% set underlne_style_open = undedrline_open_deleted %} + {% set underlne_style_close = undedrline_closed_deleted %} + {% else %} + {% set underlne_style_open = "" %} + {% set underlne_style_close = "" %} + {% endif %} + <td>{{ underlne_style_open|safe }} {% if row.spolecnici_uvolneny_podil[i].text_uvolneny_podil != "0" %}{{ row.spolecnici_uvolneny_podil[i].text_uvolneny_podil }}{% endif %} + {{ underlne_style_close|safe }}</td> + <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.spolecnici_uvolneny_podil[i].zapis_datum }} {% if row.spolecnici_uvolneny_podil[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.spolecnici_uvolneny_podil[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> + </tr> + + <!-- Insert individual vacant ownership interests --> + {% if row.spolecnici_uvolneny_podil[i].podily|length > 0 %} + + {% for j in range (row.spolecnici_uvolneny_podil[i].podily|length) %} + <tr> + <td style = padding-left:4em>Podíl:</td> + {% if row.spolecnici_uvolneny_podil[i].podily[j].vymaz_datum != 0 %} + {% set underlne_style_open = undedrline_open_deleted %} + {% set underlne_style_close = undedrline_closed_deleted %} + {% else %} + {% set underlne_style_open = "" %} + {% set underlne_style_close = "" %} + {% endif %} + <td>{{ underlne_style_open|safe }} + {% for elem in row.spolecnici_uvolneny_podil[i].podily[j].my_rep() %} + {{ elem }}<br> + {% endfor %} + {{ underlne_style_close|safe }} + </td> + <td>{{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_uvolneny_podil[i].podily[j].zapis_datum }} {% if row.spolecnici_uvolneny_podil[i].podily[j].vymaz_datum != 0 %}<br>Vymazáno: {{ row.spolecnici_uvolneny_podil[i].podily[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }}</td> + </tr> + {% endfor %} + {% endif %} + <!-- END of individual vacant ownership interests --> + {% endfor %} + <!-- END of vacant ownership interests --> + + <!-- Insert common ownership interests --> + + {% for i in range (row.spolecnici_spolecny_podil|length) %} + <tr> + <td style = padding-left:2em>Společný obchodní podíl:</td> + {% if row.spolecnici_spolecny_podil[i].vymaz_datum != 0 %} + {% set underlne_style_open = undedrline_open_deleted %} + {% set underlne_style_close = undedrline_closed_deleted %} + {% else %} + {% set underlne_style_open = "" %} + {% set underlne_style_close = "" %} + {% endif %} + <td>{{ underlne_style_open|safe }} {% if row.spolecnici_spolecny_podil[i].text_uvolneny_podil != "0" %}{{ row.spolecnici_spolecny_podil[i].text_spolecny_podil }}{% endif %} + {{ underlne_style_close|safe }}</td> + <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.spolecnici_spolecny_podil[i].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.spolecnici_spolecny_podil[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> + </tr> + <!-- Insert individual common ownership interests --> + {% if row.spolecnici_spolecny_podil[i].podily|length > 0 %} + + {% for j in range (row.spolecnici_spolecny_podil[i].podily|length) %} + <tr> + <td style = padding-left:4em>Podíl:</td> + {% if row.spolecnici_spolecny_podil[i].podily[j].vymaz_datum != 0 %} + {% set underlne_style_open = undedrline_open_deleted %} + {% set underlne_style_close = undedrline_closed_deleted %} + {% else %} + {% set underlne_style_open = "" %} + {% set underlne_style_close = "" %} + {% endif %} + <td>{{ underlne_style_open|safe }} + {% for elem in row.spolecnici_spolecny_podil[i].podily[j].my_rep() %} + {{ elem }}<br> + {% endfor %} + {{ underlne_style_close|safe }} + </td> + <td>{{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_spolecny_podil[i].podily[j].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].podily[j].vymaz_datum != 0 %}<br>Vymazáno: {{ row.spolecnici_spolecny_podil[i].podily[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }}</td> + </tr> + {% endfor %} + {% endif %} + <!-- END of individual common ownership interests --> + + <!-- Insert individual co-shareholders --> + {% if row.spolecnici_spolecny_podil[i].podilnici|length > 0 %} + + {% for j in range (row.spolecnici_spolecny_podil[i].podilnici|length) %} + <tr> + <td style = padding-left:4em>Podílník:</td> + {% if row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum != 0 %} + {% set underlne_style_open = undedrline_open_deleted %} + {% set underlne_style_close = undedrline_closed_deleted %} + {% else %} + {% set underlne_style_open = "" %} + {% set underlne_style_close = "" %} + {% endif %} + <td>{{ underlne_style_open|safe }} + {% if row.spolecnici_spolecny_podil[i].podilnici[j].podilnik_fo_id != None %} + {{ row.spolecnici_spolecny_podil[i].podilnici[j].jmeno }} + {% else %} + {{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.nazev }}{% if row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.reg_cislo != 0 %}, reg č. {{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.reg_cislo }}{% endif %}{% if row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico != 0 %}, IČ <a href="/{{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico }}">{{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico }}</a>{% endif %} + {% endif %} + <br>{{ row.spolecnici_spolecny_podil[i].podilnici[j].adresa }} + {{ underlne_style_close|safe }} + </td> + <td>{{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_spolecny_podil[i].podilnici[j].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum != 0 %}<br>Vymazáno: {{ row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }}</td> </tr> {% endfor %} +{% endif %} + <!-- END of individual co-shareholders --> + + {% endfor %} + <!-- END of common ownership interests --> + {% endif %} <!-- END Display shareholders --> + <!-- Display UBO --> + {% if row.ubo|length > 0 %} + <tr> + <td>Konečný vlastník:</td> + <td></td> + <td></td> + </tr> + {% for i in range (row.ubo|length) %} + <tr> + <td></td> + {% if row.ubo[i].vymaz_datum != 0 %} + {% set underlne_style_open = undedrline_open_deleted %} + {% set underlne_style_close = undedrline_closed_deleted %} + {% else %} + {% set underlne_style_open = "" %} + {% set underlne_style_close = "" %} + {% endif %} + <td>{{ underlne_style_open|safe }} {{row.ubo[i].jmeno }} {{row.ubo[i].jmeno.adresa}} + {% if row.ubo[i].postaveni != "0" %}<br>{{row.ubo[i].postaveni }}{% endif %} + {% if row.ubo[i].koncovyPrijemceText != "0" %}<br>{{row.ubo[i].koncovyPrijemceText }}{% endif %} {{ underlne_style_close|safe }}</td> + <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.ubo[i].zapis_datum }} {% if row.ubo[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.ubo[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> + </tr> + {% endfor %} + {% endif %} + <!-- END Display UBO --> + <!-- Display shares --> {% if row.akcie|length > 0 %} {% for i in range (row.akcie|length) %} 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' %} + +<h1>Nejčastější koneční vlastníci:</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</th> + <th scope="col">Počet společností</th> + </tr> + </thead> + <tbody> + {% for i in range (most_common_ubo|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td><a href="/results-ubo-{{most_common_ubo[i][2]}}">{{ most_common_ubo[i][0] }}</a></td> + <td>{{ most_common_ubo[i][1] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% 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 @@ <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_ubo">Seznam nejčastějších konečných vlastníků</a></p> <p><a href="/most_common_purpose">Seznam nejčastějších účelů</a></p> <p><a href="/most_common_business">Seznam nejčastějších předmětů podnikání</a></p> <p><a href="/most_common_activity">Seznam nejčastějších předmětů činnosti</a></p> diff --git a/todolist.html b/todolist.html index c86b17f..11f618f 100644 --- a/todolist.html +++ b/todolist.html @@ -1,7 +1,6 @@ <ol> <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>Check how to make diacritics work in searches.</li> <li>Check if I can remove duplication in results when searching for legal or natural persons in a role.</li> <li>Write some documentation :)</li> </ol>
\ No newline at end of file diff --git a/update_db.py b/update_db.py index 6e51e78..8444e5b 100644 --- a/update_db.py +++ b/update_db.py @@ -111,6 +111,9 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key): find_insolvency(c, ICO, elem2, conn, primary_sql_key) elif udajTyp_name == "KONKURS_SEKCE": find_konkurz(c, ICO, elem2, conn, primary_sql_key) + elif udajTyp_name == "SKUTECNY_MAJITEL_SEKCE": + find_UBO(c, ICO, elem2, conn, primary_sql_key, element) + except: pass @@ -184,16 +187,37 @@ def find_statutar(c, ICO, elem2, conn, primary_sql_key, element): except Exception as f: print(f) +def find_UBO(c, ICO, elem2, conn, primary_sql_key, element): + try: + my_iter = elem2.findall("podudaje/Udaj") + for elem in my_iter: + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + 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, 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) + def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): try: my_iter = elem2.findall("podudaje/Udaj") for elem in my_iter: - spolecnik_type = str(get_prop(elem, "udajTyp/kod")) + spolecnik_kod = str(get_prop(elem, "udajTyp/kod")) zapis_datum = str(get_prop(elem, "zapisDatum")) vymaz_datum = str(get_prop(elem, "vymazDatum")) spolecnik_typ = str(get_prop(elem, "hodnotaUdaje/typ")) # TODO Chech these conditions, they sometimes cause a person not being stored (IC 27650081) - if spolecnik_type == "SPOLECNIK_OSOBA" and spolecnik_typ == "OSOBA": + if spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "OSOBA": # TODO alternativy pro None, Spolecny podil a Uvolneny podil text_spolecnik = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) nazev = str(get_prop(elem, "osoba/nazev")) @@ -213,10 +237,49 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): c.execute("INSERT INTO spolecnici (company_id, spolecnik_po_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, spolecnik_po_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik,)) c.execute ("SELECT last_insert_rowid()") spolecnik_id = c.fetchone()[0] - insert_podily(c, elem, spolecnik_id) + insert_podily(c, elem, spolecnik_id) + + elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "SPOLECNY_PODIL": + text_spolecny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) + c.execute("INSERT INTO spolecnici_spolecny_podil (company_id, zapis_datum, vymaz_datum, text_spolecny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_spolecny_podil,)) + c.execute ("SELECT last_insert_rowid()") + spolecny_op_id = c.fetchone()[0] + insert_common_podily(c, elem, spolecny_op_id) + insert_common_shareholders(c, elem, spolecny_op_id) + + elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "UVOLNENY_PODIL": + text_uvolneny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) + c.execute("INSERT INTO spolecnici_uvolneny_podil (company_id, zapis_datum, vymaz_datum, text_uvolneny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_uvolneny_podil,)) + c.execute ("SELECT last_insert_rowid()") + uvolneny_op_id = c.fetchone()[0] + insert_vacant_podily(c, elem, uvolneny_op_id) except Exception as f: print(f) +def insert_common_shareholders(c, elem, spolecny_op_id): + try: + podil_iter = elem.findall("podudaje/Udaj") + for podil_elem in podil_iter: + if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODILNIK": + zapisDatum = str(get_prop(podil_elem, "zapisDatum")) + vymazDatum = str(get_prop(podil_elem, "vymazDatum")) + typ_podilnika = str(get_prop(podil_elem, "hodnotaText")) + if typ_podilnika == "AngazmaFyzicke": + adresa_id = find_sidlo(c, podil_elem, spolecny_op_id) + spolecnik_fo_id = find_fyzicka_osoba(c, 0, podil_elem, 0, spolecny_op_id, 0, adresa_id) + c.execute("INSERT INTO podilnici (podil_id, podilnik_fo_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_fo_id, zapisDatum, vymazDatum, adresa_id)) + if typ_podilnika == "AngazmaPravnicke": + spol_ico = str(get_prop(podil_elem, "osoba/ico")) + regCislo = str(get_prop(podil_elem, "osoba/regCislo")) + adresa_id = find_sidlo(c, podil_elem, spolecny_op_id) + spolecnik_po_id = find_pravnicka_osoba(c, podil_elem, spol_ico, regCislo, adresa_id) + c.execute("INSERT INTO podilnici (podil_id, podilnik_po_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_po_id, zapisDatum, vymazDatum, adresa_id)) + except Exception as f: + print(f) + + + + def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key, element): try: my_iter = predmet_podnikani_elem.findall("podudaje") @@ -227,7 +290,7 @@ def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) insert_instructions = [(".//hodnotaText","predmety_podnikani", "predmet_podnikani", "predmety_podnikani_relation")] for elem in insert_instructions: - inserted_figure = str(get_prop(elem2, ".//hodnotaText")) + inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize() insert_into_ancillary_table(c, elem, inserted_figure) ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) @@ -244,7 +307,7 @@ def find_predmet_cinnosti(c, ICO, predmet_cinnosti_elem, conn, primary_sql_key, vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) insert_instructions = [(".//hodnotaText","predmety_cinnosti", "predmet_cinnosti", "predmety_cinnosti_relation")] for elem in insert_instructions: - inserted_figure = str(get_prop(elem2, ".//hodnotaText")) + inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize() insert_into_ancillary_table(c, elem, inserted_figure) ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) @@ -261,7 +324,7 @@ def find_ucel(c, ICO, ucel_elem, conn, primary_sql_key, element): vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) insert_instructions = [(".//hodnotaText", "ucel", "ucel", "ucel_relation")] for elem in insert_instructions: - inserted_figure = str(get_prop(elem2, ".//hodnotaText")) + inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize() insert_into_ancillary_table(c, elem, inserted_figure) ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) @@ -504,8 +567,8 @@ def insert_individual_relations_v2(c, ICO, conn, primary_sql_key, zapis_datum, v def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, adresa_id): try: - jmeno = str(get_prop(elem, "osoba/jmeno")) - prijmeni = str(get_prop(elem, "osoba/prijmeni")) + jmeno = lower_names_chars(str(get_prop(elem, "osoba/jmeno"))) + prijmeni = lower_names_chars(str(get_prop(elem, "osoba/prijmeni"))) datum_narozeni = str(get_prop(elem, "osoba/narozDatum")) titulPred = str(get_prop(elem, "osoba/titulPred")) titulZa = str(get_prop(elem, "osoba/titulZa")) @@ -515,6 +578,20 @@ def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, adre except: pass +def lower_names_chars(string_name): + updated_name = "" + previous_non_alpha = True + for elem in string_name: + if previous_non_alpha == True: + updated_name += elem + else: + updated_name += elem.lower() + if elem.isalpha() == True: + previous_non_alpha = False + else: + previous_non_alpha = True + return updated_name + def insert_fyzicka_osoba(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni, adresa_id): try: c.execute("INSERT into fyzicke_osoby (titul_pred, jmeno, prijmeni, titul_za, datum_naroz, adresa_id) VALUES (?,?,?,?,?,?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,adresa_id,)) @@ -570,6 +647,7 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element): except Exception as f: print(f) +# TODO MERGE THESE THREE FUNCTIONS INTO ONE def insert_podily(c, elem, spolecnik_id): try: podil_iter = elem.findall("podudaje/Udaj") @@ -587,6 +665,42 @@ def insert_podily(c, elem, spolecnik_id): except Exception as f: print(f) +def insert_vacant_podily(c, elem, vacant_id): + try: + podil_iter = elem.findall("podudaje/Udaj") + for podil_elem in podil_iter: + zapisDatum = str(get_prop(podil_elem, "zapisDatum")) + vymazDatum = str(get_prop(podil_elem, "vymazDatum")) + druh_podilu_id = get_druh_podilu_id(c, podil_elem) + vklad_typ = str(get_prop(podil_elem, "hodnotaUdaje/vklad/typ")) + vklad_text = str(get_prop(podil_elem, "hodnotaUdaje/vklad/textValue")) + souhrn_typ = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/typ")) + souhrn_text = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/textValue")) + splaceni_typ = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/typ")) + splaceni_text = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/textValue")) + c.execute("INSERT INTO podily (uvolneny_podil_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (vacant_id, zapisDatum, vymazDatum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text,)) + except Exception as f: + print(f) + +def insert_common_podily(c, elem, common_id): + try: + podil_iter = elem.findall("podudaje/Udaj") + for podil_elem in podil_iter: + if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODIL": + zapisDatum = str(get_prop(podil_elem, "zapisDatum")) + vymazDatum = str(get_prop(podil_elem, "vymazDatum")) + druh_podilu_id = get_druh_podilu_id(c, podil_elem) + vklad_typ = str(get_prop(podil_elem, "hodnotaUdaje/vklad/typ")) + vklad_text = str(get_prop(podil_elem, "hodnotaUdaje/vklad/textValue")) + souhrn_typ = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/typ")) + souhrn_text = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/textValue")) + splaceni_typ = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/typ")) + splaceni_text = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/textValue")) + c.execute("INSERT INTO podily (spolecny_podil_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (common_id, zapisDatum, vymazDatum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text,)) + except Exception as f: + print(f) + + def get_druh_podilu_id(c, podil_elem): try: druhPodilu = str(get_prop(podil_elem, "hodnotaUdaje/druhPodilu")) |