From 0b14d5eb64694ac67c5faea26fe942b947126125 Mon Sep 17 00:00:00 2001 From: Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com> Date: Sat, 20 Feb 2021 23:07:46 +0100 Subject: function to display members of bodies --- justice_main.py | 154 +++++++++++++++++++++++++++++++++++++++++++++++--------- 1 file changed, 131 insertions(+), 23 deletions(-) (limited to 'justice_main.py') diff --git a/justice_main.py b/justice_main.py index 318f5ae..8adb991 100644 --- a/justice_main.py +++ b/justice_main.py @@ -6,6 +6,9 @@ import gzip import send2trash import os from datetime import datetime +import cProfile +import re +from sqlalchemy import text, exc, insert, engine # The function opens a file and parses the extracted data into the database def parse_to_DB(file): @@ -40,6 +43,7 @@ def purge_DB(): c.execute("DELETE FROM adresy") c.execute("DELETE FROM akcie") c.execute("DELETE FROM companies") + c.execute("DELETE FROM fyzicke_osoby") c.execute("DELETE FROM insolvency_events") c.execute("DELETE FROM konkurz_events") c.execute("DELETE FROM nazvy") @@ -57,8 +61,9 @@ def purge_DB(): c.execute("DELETE FROM sidla") c.execute("DELETE FROM sidlo_relation") c.execute("DELETE FROM sqlite_sequence") - c.execute("DELETE FROM statutarni_organy") + c.execute("DELETE FROM statutarni_organ_clen_relation") c.execute("DELETE FROM statutarni_organ_relation") + c.execute("DELETE FROM statutarni_organy") c.execute("DELETE FROM ulice") c.execute("DELETE FROM ulice_relation") c.execute("DELETE FROM zakladni_kapital") @@ -78,30 +83,30 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key): for elem in my_iter: my_iter2 = elem.iter("Udaj") for elem2 in my_iter2: - # print(ICO, str(get_prop(elem2, ".//udajTyp/kod"))) - if str(get_prop(elem2, ".//udajTyp/kod")) == "SIDLO": + udajTyp_name = str(get_prop(elem2, ".//udajTyp/kod")) + if udajTyp_name == "SIDLO": find_registered_office(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "NAZEV": + elif udajTyp_name == "NAZEV": find_nazev(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "STATUTARNI_ORGAN": + elif udajTyp_name == "STATUTARNI_ORGAN": find_statutar(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "INSOLVENCE_SEKCE": + elif udajTyp_name == "INSOLVENCE_SEKCE": find_active_insolvency(c, ICO, elem2, conn, primary_sql_key) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "KONKURS_SEKCE": + elif udajTyp_name == "KONKURS_SEKCE": find_active_konkurz(c, ICO, elem2, conn, primary_sql_key) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "PREDMET_PODNIKANI_SEKCE": + elif udajTyp_name == "PREDMET_PODNIKANI_SEKCE": find_predmet_podnikani(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "PREDMET_CINNOSTI_SEKCE": + elif udajTyp_name == "PREDMET_CINNOSTI_SEKCE": find_predmet_cinnosti(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "ZAKLADNI_KAPITAL": + elif udajTyp_name == "ZAKLADNI_KAPITAL": find_zakladni_kapital(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "OST_SKUTECNOSTI_SEKCE": + elif udajTyp_name == "OST_SKUTECNOSTI_SEKCE": find_ostatni_skutecnosti(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "AKCIE_SEKCE": + elif udajTyp_name == "AKCIE_SEKCE": find_akcie(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "SPIS_ZN": + elif udajTyp_name == "SPIS_ZN": find_sp_zn(c, ICO, elem2, conn, primary_sql_key, element) - elif str(get_prop(elem2, ".//udajTyp/kod")) == "PRAVNI_FORMA": + elif udajTyp_name == "PRAVNI_FORMA": find_pravni_forma(c, ICO, elem2, conn, primary_sql_key, element) except: pass @@ -132,21 +137,123 @@ def find_statutar(c, ICO, elem2, conn, primary_sql_key, element): insert_into_ancillary_table(c, elem, oznaceni_statutar_organu) ancillary_table_key = get_anciallary_table_key(c, elem, oznaceni_statutar_organu) insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) - relationship_table_key = c.execute("SELECT id FROM statutarni_organ_relation WHERE company_id = (?) and statutarni_organ_id = (?)", (primary_sql_key,ancillary_table_key,)) - relationship_table_key = c.fetchone()[0] - my_iter = elem2.findall("podudaje/Udaj") + relationship_table_key = get_relationship_table_key(c, primary_sql_key, ancillary_table_key) + # relationship_table_key = c.execute("SELECT id FROM statutarni_organ_relation WHERE company_id = (?) and statutarni_organ_id = (?)", (primary_sql_key,ancillary_table_key,)) + # relationship_table_key = c.fetchone()[0] + my_iter = elem2.findall("podudaje/Udaj") for elem in my_iter: - if (str(get_prop(elem, "udajTyp/kod"))) == "POCET_CLENU": + udajTyp_name = str(get_prop(elem, "udajTyp/kod")) + if udajTyp_name == "POCET_CLENU": find_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element) - elif (str(get_prop(elem, "udajTyp/kod"))) == "ZPUSOB_JEDNANI": + elif udajTyp_name == "ZPUSOB_JEDNANI": find_zpusob_jednani(c, ICO, elem, conn, relationship_table_key, element) - elif (str(get_prop(elem, "udajTyp/kod"))) == "STATUTARNI_ORGAN_CLEN": - pass + elif udajTyp_name == "STATUTARNI_ORGAN_CLEN": + find_clen_statut_org(c, ICO, elem, conn, relationship_table_key, element) else: print(str(get_prop(elem, "udajTyp/kod"))) except Exception as f: print(f) +def find_clen_statut_org(c, ICO, elem, conn, relationship_table_key, element): + try: + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + funkce_statutar_organu = str(get_prop(elem, "funkce")) + typ_osoby = str(get_prop(elem, "hodnotaText")) + funkceOd = str(get_prop(elem, "funkceOd")) + clenstviOd = str(get_prop(elem, "clenstviOd")) + funkceDo = str(get_prop(elem, "funkceDo")) + clenstviDo = str(get_prop(elem, "clenstviDo")) + if typ_osoby == "AngazmaFyzicke": + osoba_id = find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element) + else: + osoba_id = "1111111" + adresa_id = find_and_store_address(c, elem) + c.execute("INSERT into statutarni_organ_clen_relation (statutarni_organ_id, osoba_id, adresa_id, zapis_datum, vymaz_datum, funkce_od, funkce_do, clenstvi_od, clenstvi_do, funkce) VALUES (?,?,?,?,?,?,?,?,?,?)", (relationship_table_key, osoba_id, adresa_id, zapis_datum, vymaz_datum, funkceOd, funkceDo, clenstviOd, clenstviDo, funkce_statutar_organu,)) + except Exception as f: + print(f) + +def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element): + try: + jmeno = str(get_prop(elem, "osoba/jmeno")) + prijmeni = 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")) + insert_fyzicka_osoba(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni) + osoba_id = find_osoba_id(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni) + return osoba_id + except: + pass + +def insert_fyzicka_osoba(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni): + try: + c.execute("INSERT into fyzicke_osoby (titul_pred, jmeno, prijmeni, titul_za, datum_naroz) VALUES (?,?,?,?,?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,)) + except: + pass + +def find_osoba_id(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni): + try: + anciallary_table_key = c.execute("SELECT id FROM fyzicke_osoby WHERE titul_pred = (?) and jmeno = (?) and prijmeni = (?) and titul_za = (?) and datum_naroz = (?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,)) + anciallary_table_key = c.fetchone()[0] + return anciallary_table_key + except Exception as f: + print(f) + +def find_and_store_address(c, elem): + try: + addr = str(adresa(get_SIDLO_v3(elem))) + insert_address(c, addr) + address_id = find_address_id(c, addr) + return address_id + except Exception as f: + print(f) + +def find_address_id(c, addr): + try: + anciallary_table_key = c.execute("SELECT id FROM adresy WHERE adresa_text = (?)", (addr,)) + anciallary_table_key = c.fetchone()[0] + return anciallary_table_key + except Exception as f: + print(f) + +def get_relationship_table_key(c, primary_sql_key, ancillary_table_key): + c.execute("SELECT id FROM statutarni_organ_relation WHERE company_id = (?) and statutarni_organ_id = (?)", (primary_sql_key,ancillary_table_key,)) + return c.fetchone()[0] + +def insert_address(c, addr): + try: + c.execute("INSERT into adresy (adresa_text) VALUES (?)", (addr,)) + except: + pass + +# def find_statutar(c, ICO, elem2, conn, primary_sql_key, element): +# try: +# zapis_datum = str(get_prop(elem2, "zapisDatum")) +# vymaz_datum = str(get_prop(elem2, "vymazDatum")) +# oznaceni_statutar_organu = str(get_prop(elem2, ".//hlavicka")) +# # print(ICO, zapis_datum, vymaz_datum, oznaceni_statutar_organu) +# insert_instructions = [(oznaceni_statutar_organu,"statutarni_organy", "statutarni_organ_text", "statutarni_organ_relation")] +# for elem in insert_instructions: +# insert_into_ancillary_table(c, elem, oznaceni_statutar_organu) +# ancillary_table_key = get_anciallary_table_key(c, elem, oznaceni_statutar_organu) +# insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) +# relationship_table_key = c.execute("SELECT id FROM statutarni_organ_relation WHERE company_id = (?) and statutarni_organ_id = (?)", (primary_sql_key,ancillary_table_key,)) +# relationship_table_key = c.fetchone()[0] +# my_iter = elem2.findall("podudaje/Udaj") +# for elem in my_iter: +# udajTyp_name = str(get_prop(elem, "udajTyp/kod")) +# if udajTyp_name == "POCET_CLENU": +# find_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element) +# elif udajTyp_name == "ZPUSOB_JEDNANI": +# find_zpusob_jednani(c, ICO, elem, conn, relationship_table_key, element) +# elif udajTyp_name == "STATUTARNI_ORGAN_CLEN": +# pass +# else: +# print(str(get_prop(elem, "udajTyp/kod"))) +# except Exception as f: +# print(f) + def find_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element): try: zapis_datum = str(get_prop(elem, "zapisDatum")) @@ -390,7 +497,7 @@ def get_anciallary_table_key(c, elem, inserted_figure): return anciallary_table_key except Exception as f: print(f) - + def insert_relation_information(c, elem, primary_sql_key, ancillary_table_key): try: c.execute("INSERT INTO " + elem[3] + " VALUES(?, ?)", (primary_sql_key, ancillary_table_key,)) @@ -781,6 +888,7 @@ purge_DB() # general_update("db_update") parse_to_DB("as-full-ostrava-2021.xml") +# parse_to_DB("sro-full-ceske_budejovice-2021.xml") # parse_to_DB("sro-actual-praha-2020.xml") @@ -790,4 +898,4 @@ parse_to_DB("as-full-ostrava-2021.xml") # do_both() -# cProfile.run('do_both()') \ No newline at end of file +# cProfile.run('general_update("db_update")') \ No newline at end of file -- cgit