diff options
| -rw-r--r-- | db_creation.py | 40 | ||||
| -rw-r--r-- | justice_build.py | 7 | ||||
| -rw-r--r-- | main.py | 2 | ||||
| -rw-r--r-- | models.py | 17 | ||||
| -rw-r--r-- | templates/extract-actual.html | 58 | ||||
| -rw-r--r-- | templates/extract.html | 37 | ||||
| -rw-r--r-- | update_db.py | 31 |
7 files changed, 174 insertions, 18 deletions
diff --git a/db_creation.py b/db_creation.py index 70a08df..c3836aa 100644 --- a/db_creation.py +++ b/db_creation.py @@ -195,6 +195,21 @@ 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, @@ -465,7 +480,7 @@ 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, 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] @@ -601,6 +616,26 @@ def create_indices(conn): "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" ); """ @@ -847,7 +882,8 @@ 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, podily3, podily4, 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, diff --git a/justice_build.py b/justice_build.py index 4b82660..9acf1e7 100644 --- a/justice_build.py +++ b/justice_build.py @@ -8,11 +8,10 @@ def main(): DB_name = "justice.db"
# backup_DB()
create_DB(DB_name)
- # valid_files = get_valid_filenames()
- valid_files = ["sro-full-ceske_budejovice-2021"]
+ 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:
+ 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)
@@ -5,7 +5,7 @@ from models import Company, Insolvency_Events, Konkurz_Events, Predmet_Podnikani 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, Uvolneny_Podil_Association, Spolecny_Podil_Association +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 @@ -301,6 +301,21 @@ 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) @@ -319,9 +334,9 @@ class Spolecny_Podil_Association(db.Model): 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) diff --git a/templates/extract-actual.html b/templates/extract-actual.html index f24cb80..691dae3 100644 --- a/templates/extract-actual.html +++ b/templates/extract-actual.html @@ -49,7 +49,6 @@ {% endif %} {% endfor %} - {% set vacant_shares_notes = [] %} {% for i in range (row.spolecnici_uvolneny_podil|length) %} {% if row.spolecnici_uvolneny_podil[i].vymaz_datum == 0 %} @@ -57,6 +56,13 @@ {% 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 %} @@ -376,7 +382,7 @@ <!-- END Display sole shareholder --> <!-- Display shareholders --> - {% if spolecnici_notes|length > 0 or vacant_shares_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> @@ -433,6 +439,54 @@ {% 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 --> diff --git a/templates/extract.html b/templates/extract.html index ec58487..9eb1985 100644 --- a/templates/extract.html +++ b/templates/extract.html @@ -530,7 +530,7 @@ <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 vacant ownership interests --> + <!-- 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) %} @@ -553,15 +553,40 @@ </tr> {% endfor %} {% endif %} - <!-- END of individual vacant ownership interests --> - {% endfor %} + <!-- 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 %} + {% endif %} <!-- END Display shareholders --> <!-- Display UBO --> diff --git a/update_db.py b/update_db.py index df844f6..8444e5b 100644 --- a/update_db.py +++ b/update_db.py @@ -238,12 +238,15 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): c.execute ("SELECT last_insert_rowid()") spolecnik_id = c.fetchone()[0] 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()") - uvolneny_op_id = c.fetchone()[0] - insert_common_podily(c, elem, uvolneny_op_id) + 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,)) @@ -253,6 +256,30 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): 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") |