diff options
| -rw-r--r-- | db_creation.py | 36 | ||||
| -rw-r--r-- | justice_main.py | 4 | ||||
| -rw-r--r-- | todolist.html | 1 | ||||
| -rw-r--r-- | update_db.py | 1338 |
4 files changed, 716 insertions, 663 deletions
diff --git a/db_creation.py b/db_creation.py index 3182d46..b455119 100644 --- a/db_creation.py +++ b/db_creation.py @@ -491,6 +491,10 @@ def create_indices(conn): "akcie_text" ); """ + akcie2 = """ CREATE INDEX "index akcie 2" ON "akcie" ( + "company_id" +); """ + akcionari = """ CREATE INDEX "index akcionari" ON "jediny_akcionar" ( "id", "company_id", @@ -562,6 +566,10 @@ def create_indices(conn): "nazev_text" ); """ + nazvy2 = """ CREATE INDEX "index nazvy 2" ON "nazvy" ( + "company_id" +); """ + obce = """ CREATE INDEX "index obce" ON "obce" ( "id", "obec_jmeno" @@ -624,6 +632,10 @@ def create_indices(conn): "pravni_forma" ); """ + pravni_formy_relation_2 = """ CREATE INDEX "index pravni_formy_relation_2" ON "pravni_formy_relation" ( + "company_id" +); """ + predmety_cinnosti_relation_v2 = """ CREATE INDEX "index predmety cinnosti relation v2" ON "predmety_cinnosti_relation" ( "company_id", "id", @@ -674,6 +686,10 @@ def create_indices(conn): "vymaz_datum" ); """ + sidlo_relation_2 = """ CREATE INDEX "index sidlo relation 2" ON "sidlo_relation" ( + "company_id" +); """ + sidlo2 = """ CREATE INDEX "index sidlo2" ON "sidla" ( "company_id" ); """ @@ -737,13 +753,21 @@ def create_indices(conn): "funkce" ); """ + statutarni_organy_relation_3 = """ CREATE INDEX "index statutarni organ relation 3" ON "statutarni_organ_relation" ( + "company_id" +); """ + v2 = """ CREATE INDEX "index v2" ON "statutarni_organ_relation" ( "statutarni_organ_id", "company_id", "id" ); """ - zapis2 = """ CREATE INDEX "index zapis2" ON "zapis_soudy" ( + zakladni_kapital = """ CREATE INDEX "index zakladni kapital" ON "zakladni_kapital" ( + "company_id" +); """ + + zapis2 = """ CREATE INDEX "index zapis2" ON "zapis_soudy" ( "company_id" ); """ @@ -770,6 +794,10 @@ def create_indices(conn): "vymaz_datum" ); """ + zpusob_jednani_relation_2 = """ CREATE INDEX "index zpusob jednani relation 2" ON "zpusoby_jednani_relation" ( + "statutarni_organ_id" +); """ + zpusoby_jednani = """ CREATE INDEX "index zpusoby_jednani" ON "zpusoby_jednani" ( "zpusob_jednani_text" ); """ @@ -780,9 +808,9 @@ def create_indices(conn): "nazev" ); """ - list_of_indices = [companies, adresy, adresa_text, akcie, akcionari, companies_ico, companies_nazvy, companies_vznik, dr_clen_relation, dr_relation, dr_relation2, insolvency_events, insolvency2, jmena_firem, nazvy_nazev_text, obce, obec_jmeno, osoby, ostatni_skutecnosti2, - pocty_clenu_organ, podily, podily_spolecnik, pravni_formy, predmety_cinnosti_relation_v2, predmety_podnikani_relation, predmety_cinnosti, predmety_podnikani, prokuriste, sidlo, sidlo_relation, sidlo2, soudni_zapis, spolecnici, spolecnici2, statutarni_organy, statutarni_organy_relation, - statutarni_organy_relation_v2, v2, zapis2, zapis_soudy, zpusob_jednani, zpusob_jednani_relation, zpusoby_jednani, pravnicke_osoby_index] + list_of_indices = [companies, adresy, adresa_text, akcie, akcie2, akcionari, companies_ico, companies_nazvy, companies_vznik, dr_clen_relation, dr_relation, dr_relation2, insolvency_events, insolvency2, jmena_firem, nazvy_nazev_text, nazvy2, obce, obec_jmeno, osoby, ostatni_skutecnosti2, + pocty_clenu_organ, podily, podily_spolecnik, pravni_formy, pravni_formy_relation_2, predmety_cinnosti_relation_v2, predmety_podnikani_relation, predmety_cinnosti, predmety_podnikani, prokuriste, sidlo, sidlo_relation, sidlo_relation_2, sidlo2, soudni_zapis, spolecnici, spolecnici2, statutarni_organy, statutarni_organy_relation, + statutarni_organy_relation_v2, statutarni_organy_relation_3, v2, zakladni_kapital, zapis2, zapis_soudy, zpusob_jednani, zpusob_jednani_relation, zpusob_jednani_relation_2, zpusoby_jednani, pravnicke_osoby_index] for elem in list_of_indices: try: c = conn.cursor() diff --git a/justice_main.py b/justice_main.py index 6fd9428..4dca06d 100644 --- a/justice_main.py +++ b/justice_main.py @@ -2,14 +2,10 @@ from db_creation import create_DB from download_files import download_data
from update_db import update_DB
-
def main():
typy_po = ["as", "sro", "vos", "ks", "dr", "zajzdrpo", "zahrfos", "ustav", "svj", "spolek", "prisp", "pobspolek",
"oszpo", "osznadf", "osznad", "orgzam", "odbororg", "nadf", "nad", "evrspol", "evrhzs", "evrdrspol"]
soudy = ["praha", "plzen", "brno", "ceske_budejovice", "hradec_kralove", "ostrava", "usti_nad_labem"]
-
- # typy_po = ["as"]
- # soudy = ["ceske_budejovice"]
DB_name = "justice.db"
create_DB(DB_name)
# download_data(typy_po, soudy)
diff --git a/todolist.html b/todolist.html index 668f329..8b83207 100644 --- a/todolist.html +++ b/todolist.html @@ -1,5 +1,4 @@ <ol> - <li>Update the search to allow for searches by other properties.</li> <li>Add a feature to display legal entities as members of corporate bodies.</li> <li>Add a feature to display other types of ownerhsip interests (joint onwership interest, vacant ownership interest).</li> <li>Refactor excessive duplications in the main code.</li> diff --git a/update_db.py b/update_db.py index 11ba6ec..e5f2caa 100644 --- a/update_db.py +++ b/update_db.py @@ -34,6 +34,51 @@ def parse_to_DB(file, DB_name): conn.close() return 0 +def get_ICO(element): + try: + return element.find('ico').text + except: + return "00000000" + +# Function to attempt to insert a placeholder for a new company based on ICO +def insert_new_ICO(c, ICO, conn, element): + try: + datum_zapis = str(get_prop(element, "zapisDatum")) + nazev = str(get_prop(element, "nazev")) + c.execute("INSERT INTO companies (ico, zapis, nazev) VALUES (?,?,?);", (ICO,datum_zapis,nazev,)) + return c.lastrowid + except: + pass + +def get_primary_sql_key(c, ICO): + try: + primary_key = c.execute("SELECT id FROM companies WHERE ico = (?)", (ICO,)) + primary_key = c.fetchone() + return primary_key[0] + except: + return 0 + +def insert_company_relations(c, ICO, element, conn, primary_sql_key): + insert_instructions = [(".//udaje/Udaj/pravniForma/nazev","pravni_formy", "pravni_forma", "pravni_formy_relation")] + for elem in insert_instructions: + insert_individual_relations(c, ICO, element, conn, primary_sql_key, elem) + return 0 + +def insert_individual_relations(c, ICO, element, conn, primary_sql_key, elem): + inserted_figure = str(get_prop(element, elem[0])) + insert_into_ancillary_table(c, elem, inserted_figure) + ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) + insert_relation_information(c, elem, primary_sql_key, ancillary_table_key) + return ancillary_table_key + +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,)) + except: + pass + return 0 + + def find_other_properties(c, ICO, element, conn, primary_sql_key): try: # my_iter = element.iter("udaje") @@ -44,7 +89,7 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key): for elem2 in my_iter2: udajTyp_name = str(get_prop(elem2, ".//udajTyp/kod")) if udajTyp_name == "SIDLO": - find_registered_office_v2(c, ICO, elem2, conn, primary_sql_key, element) + find_registered_office(c, ICO, elem2, conn, primary_sql_key, element) elif udajTyp_name == "NAZEV": find_nazev(c, ICO, elem2, conn, primary_sql_key, element) elif udajTyp_name == "SPIS_ZN": @@ -72,154 +117,41 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key): elif udajTyp_name == "AKCIONAR_SEKCE": find_sole_shareholder(c, ICO, elem2, conn, primary_sql_key, element) elif udajTyp_name == "INSOLVENCE_SEKCE": - find_active_insolvency(c, ICO, elem2, conn, primary_sql_key) + find_insolvency(c, ICO, elem2, conn, primary_sql_key) elif udajTyp_name == "KONKURS_SEKCE": - find_active_konkurz(c, ICO, elem2, conn, primary_sql_key) + find_konkurz(c, ICO, elem2, conn, primary_sql_key) except: pass -def find_sole_shareholder(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")) - text_akcionar = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) - typ_akcionar = str(get_prop(elem, "hodnotaUdaje/T")) - if typ_akcionar == "P": - spol_ico = str(get_prop(elem, "osoba/ico")) - regCislo = str(get_prop(elem, "osoba/regCislo")) - akcionar_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo) - # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, primary_sql_key) - c.execute("INSERT into jediny_akcionar (company_id, zapis_datum, vymaz_datum, text_akcionar, akcionar_po_id, adresa_id) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_akcionar, akcionar_po_id, adresa_id,)) - elif typ_akcionar == "F": - akcionar_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) - # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, primary_sql_key) - c.execute("INSERT into jediny_akcionar (company_id, zapis_datum, vymaz_datum, text_akcionar, akcionar_fo_id, adresa_id) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_akcionar, akcionar_fo_id, adresa_id,)) - except Exception as f: - print(f) - -def find_prokura(c, ICO, elem2, conn, primary_sql_key, element): - try: - my_iter = elem2.findall("podudaje/Udaj") - for elem in my_iter: - typ_zapis = str(get_prop(elem, "udajTyp/kod")) - if typ_zapis == "PROKURA_OSOBA": - zapis_datum = str(get_prop(elem, "zapisDatum")) - vymaz_datum = str(get_prop(elem, "vymazDatum")) - text_prokurista = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) - prokurista_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) - # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, primary_sql_key) - c.execute("INSERT INTO prokuriste (company_id, zapis_datum, vymaz_datum, prokurista_fo_id, adresa_id, text_prokurista) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, prokurista_fo_id, adresa_id, text_prokurista,)) - else: - zapis_datum = str(get_prop(elem, "zapisDatum")) - vymaz_datum = str(get_prop(elem, "vymazDatum")) - prokura_text = str(get_prop(elem, "hodnotaText")) - c.execute("INSERT INTO prokura_common_texts (company_id, zapis_datum, vymaz_datum, prokura_text) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, prokura_text,)) - 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")) - 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_oznaceni == "Společník": - if spolecnik_type == "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")) - # TODO Fix - make reference to type of person - some foreign persons have no ico or regCo, so they are assigned a number for a natural person - # if spol_ico == "0" and regCislo == "0": - if nazev == "0": - # I probably do not need the primary sql key - spolecnik_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) - # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, primary_sql_key) - c.execute("INSERT INTO spolecnici (company_id, spolecnik_fo_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, spolecnik_fo_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik,)) - c.execute ("SELECT last_insert_rowid()") - spolecnik_id = c.fetchone()[0] - # print(ICO, spolecnik_fo_id, adresa_id) - else: - spol_ico = str(get_prop(elem, "osoba/ico")) - regCislo = str(get_prop(elem, "osoba/regCislo")) - spolecnik_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo) - # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, primary_sql_key) - 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) - except Exception as f: - print(f) - -def insert_podily(c, elem, spolecnik_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 (spolecnik_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (spolecnik_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): +def find_registered_office(c, ICO, elem2, conn, primary_sql_key, element): try: - druhPodilu = str(get_prop(podil_elem, "hodnotaUdaje/druhPodilu")) - insert_druh_podilu(c, podil_elem, druhPodilu) - druh_podilu_id = find_druh_podilu_id(c, druhPodilu) - return druh_podilu_id + zapis_datum = str(get_prop(elem2, ".//zapisDatum")) + vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) + sidlo_id = sidlo3(c, elem2, primary_sql_key) + insert_instructions = [None,"adresy", "adresa_text", "sidlo_relation"] + insert_relation_information_v2(c, insert_instructions, primary_sql_key, sidlo_id, zapis_datum, vymaz_datum) except Exception as f: - print(f) + print(f) -def insert_druh_podilu(c, podil_elem, druhPodilu): +def find_nazev(c, ICO, elem2, conn, primary_sql_key, element): try: - c.execute("INSERT INTO druhy_podilu (druh_podilu) VALUES (?)", (druhPodilu,)) + zapis_datum = str(get_prop(elem2, ".//zapisDatum")) + vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) + nazev = str(get_prop(elem2, ".//hodnotaText")) + c.execute("INSERT INTO nazvy (company_id, zapis_datum, vymaz_datum, nazev_text) VALUES(?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, nazev,)) except: pass -def find_druh_podilu_id(c, druhPodilu): - try: - druh_podilu_id = c.execute("SELECT id FROM druhy_podilu WHERE druh_podilu = (?)", (druhPodilu,)) - druh_podilu_id = c.fetchone()[0] - return druh_podilu_id - except Exception as f: - print(f) - -def find_pravnicka_osoba(c, elem, spol_ico, regCislo): - try: - nazev = str(get_prop(elem, "osoba/nazev")) - insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev) - osoba_id = find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev) - return osoba_id - except Exception as f: - print(f) - -def find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev): - try: - anciallary_table_key = c.execute("SELECT id FROM pravnicke_osoby WHERE ico = (?) and reg_cislo = (?) and nazev = (?)", (spol_ico, regCislo, nazev,)) - anciallary_table_key = c.fetchone()[0] - return anciallary_table_key - except Exception as f: - print(f) - -def insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev): +def find_sp_zn(c, ICO, elem2, conn, primary_sql_key, element): try: - c.execute("INSERT into pravnicke_osoby (ico, reg_cislo, nazev) VALUES (?,?,?)", (spol_ico, regCislo, nazev,)) + zapis_datum = str(get_prop(elem2, ".//zapisDatum")) + vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) + soud = str(get_prop(elem2, ".//spisZn/soud/kod")) + oddil = str(get_prop(elem2, ".//spisZn/oddil")) + vlozka = str(get_prop(elem2, ".//spisZn/vlozka")) + c.execute("INSERT INTO zapis_soudy (company_id, zapis_datum, vymaz_datum, oddil, vlozka, soud) VALUES(?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, oddil, vlozka, soud,)) + if vymaz_datum == "0": + c.execute("UPDATE companies SET oddil = (?), vlozka = (?), soud = (?) WHERE id = (?)",(oddil,vlozka,soud,primary_sql_key,)) except: pass @@ -262,233 +194,44 @@ def find_statutar(c, ICO, elem2, conn, primary_sql_key, element): except Exception as f: print(f) -def find_dozorci_rada(c, ICO, elem2, conn, primary_sql_key, element): +def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): try: - zapis_datum = str(get_prop(elem2, "zapisDatum")) - vymaz_datum = str(get_prop(elem2, "vymazDatum")) - c.execute("INSERT into dozorci_rada_relation (company_id, zapis_datum, vymaz_datum) VALUES (?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum,)) - c.execute("SELECT id FROM dozorci_rada_relation WHERE company_id = (?) and zapis_datum = (?)", (primary_sql_key,zapis_datum,)) - relationship_table_key = c.fetchone()[0] - my_iter = elem2.findall("podudaje/Udaj") + my_iter = elem2.findall("podudaje/Udaj") for elem in my_iter: - udajTyp_name = str(get_prop(elem, "udajTyp/kod")) - if udajTyp_name == "POCET_CLENU_DOZORCI_RADA": - find_pocet_clenu_dr(c, ICO, elem, conn, relationship_table_key, element) - elif udajTyp_name == "DOZORCI_RADA_CLEN": - find_clen_dr(c, ICO, elem, conn, relationship_table_key, element) - # find_clen_dr(c, ICO, elem, conn, relationship_table_key, element) - 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) - # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, relationship_table_key) - 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_clen_dr(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) - # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, relationship_table_key) - c.execute("INSERT into dr_organ_clen_relation (dozorci_rada_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_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element): - try: - zapis_datum = str(get_prop(elem, "zapisDatum")) - vymaz_datum = str(get_prop(elem, "vymazDatum")) - pocet_clenu_number = str(get_prop(elem, "hodnotaText")) - c.execute("INSERT into pocty_clenu_organu (organ_id, pocet_clenu_value, zapis_datum, vymaz_datum) VALUES (?,?,?,?)", (relationship_table_key, pocet_clenu_number, zapis_datum, vymaz_datum,)) - except Exception as f: - print(f) - -# COMBINE WITH THE ABOVE -def find_pocet_clenu_dr(c, ICO, elem, conn, relationship_table_key, element): - try: - zapis_datum = str(get_prop(elem, "zapisDatum")) - vymaz_datum = str(get_prop(elem, "vymazDatum")) - pocet_clenu_number = str(get_prop(elem, "hodnotaText")) - c.execute("INSERT into pocty_clenu_DR (organ_id, pocet_clenu_value, zapis_datum, vymaz_datum) VALUES (?,?,?,?)", (relationship_table_key, pocet_clenu_number, zapis_datum, vymaz_datum,)) - except Exception as f: - print(f) - -def find_zpusob_jednani(c, ICO, elem, conn, relationship_table_key, element): - try: - zapis_datum = str(get_prop(elem, "zapisDatum")) - vymaz_datum = str(get_prop(elem, "vymazDatum")) - zpusob_jednani = str(get_prop(elem, "hodnotaText")) - insert_instructions = [(zpusob_jednani,"zpusoby_jednani", "zpusob_jednani_text", "zpusoby_jednani_relation")] - for elem in insert_instructions: - insert_into_ancillary_table(c, elem, zpusob_jednani) - ancillary_table_key = get_anciallary_table_key(c, elem, zpusob_jednani) - insert_relation_information_v2(c, elem, relationship_table_key, ancillary_table_key, zapis_datum, vymaz_datum) + spolecnik_type = 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_oznaceni == "Společník": + if spolecnik_type == "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")) + # TODO Fix - make reference to type of person - some foreign persons have no ico or regCo, so they are assigned a number for a natural person + # if spol_ico == "0" and regCislo == "0": + if nazev == "0": + # I probably do not need the primary sql key + spolecnik_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) + # adresa_id = find_and_store_address(c, elem) + adresa_id = sidlo3(c, elem, primary_sql_key) + c.execute("INSERT INTO spolecnici (company_id, spolecnik_fo_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, spolecnik_fo_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik,)) + c.execute ("SELECT last_insert_rowid()") + spolecnik_id = c.fetchone()[0] + # print(ICO, spolecnik_fo_id, adresa_id) + else: + spol_ico = str(get_prop(elem, "osoba/ico")) + regCislo = str(get_prop(elem, "osoba/regCislo")) + spolecnik_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo) + # adresa_id = find_and_store_address(c, elem) + adresa_id = sidlo3(c, elem, primary_sql_key) + 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) except Exception as f: print(f) -# THIS NEEDS TO BE REFACTORED -def find_registered_office(c, ICO, elem2, conn, primary_sql_key, element): - try: - zapis_datum = str(get_prop(elem2, ".//zapisDatum")) - vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) - sidlo = str(adresa(get_SIDLO_v3(elem2))) - if vymaz_datum == "0": - # sidlo2(c, elem2, primary_sql_key) - sidlo3(c, elem2, primary_sql_key) - # Insert current seat into the main table - c.execute("UPDATE companies SET sidlo = (?) WHERE id = (?)",(sidlo,primary_sql_key,)) - insert_prop(c, sidlo, conn, ICO, "sidlo") - obec = str(get_prop(elem2, ".//adresa/obec")) - insert_instructions = [(obec,"obce", "obec_jmeno", "obce_relation")] - for elem in insert_instructions: - insert_into_ancillary_table(c, elem, obec) - ancillary_table_key = get_anciallary_table_key(c, elem, obec) - insert_relation_information(c, elem, primary_sql_key, ancillary_table_key) - ulice = str(get_prop(elem2, ".//adresa/ulice")) - insert_instructions = [(ulice,"ulice", "ulice_jmeno", "ulice_relation")] - for elem in insert_instructions: - insert_into_ancillary_table(c, elem, ulice) - ancillary_table_key = get_anciallary_table_key(c, elem, ulice) - insert_relation_information(c, elem, primary_sql_key, ancillary_table_key) - insert_instructions = [(sidlo,"adresy", "adresa_text", "sidlo_relation")] - for elem in insert_instructions: - insert_into_ancillary_table(c, elem, sidlo) - ancillary_table_key = get_anciallary_table_key(c, elem, sidlo) - insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) - return 0 - except: - pass - -def find_registered_office_v2(c, ICO, elem2, conn, primary_sql_key, element): - try: - zapis_datum = str(get_prop(elem2, ".//zapisDatum")) - vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) - sidlo_id = sidlo3(c, elem2, primary_sql_key) - insert_instructions = [None,"adresy", "adresa_text", "sidlo_relation"] - insert_relation_information_v2(c, insert_instructions, primary_sql_key, sidlo_id, zapis_datum, vymaz_datum) - except Exception as f: - print(f) - -def sidlo2(c, elem, primary_sql_key): - try: - statNazev = get_prop(elem, ".//statNazev") - obec = get_prop(elem, ".//obec") - ulice = get_prop(elem, ".//ulice") - castObce = get_prop(elem, ".//castObce") - cisloPo = get_prop(elem, ".//cisloPo") - cisloOr = get_prop(elem, ".//cisloOr") - psc = get_prop(elem, ".//psc") - okres = get_prop(elem, ".//okres") - adresaText = get_prop(elem, ".//adresaText") - cisloEv = get_prop(elem, ".//cisloEv") - cisloText = get_prop(elem, ".//cisloText") - c.execute("INSERT INTO adresy_v2 (stat, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, komplet_adresa, cisloEv, cisloText, company_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (statNazev, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, adresaText, cisloEv, cisloText, primary_sql_key)) - except: - pass - -def sidlo3(c, elem, primary_sql_key): - try: - statNazev = get_prop(elem, ".//statNazev") - obec = get_prop(elem, ".//obec") - ulice = get_prop(elem, ".//ulice") - castObce = get_prop(elem, ".//castObce") - cisloPo = get_prop(elem, ".//cisloPo") - cisloOr = get_prop(elem, ".//cisloOr") - psc = get_prop(elem, ".//psc") - okres = get_prop(elem, ".//okres") - adresaText = get_prop(elem, ".//adresaText") - cisloEv = get_prop(elem, ".//cisloEv") - cisloText = get_prop(elem, ".//cisloText") - c.execute("SELECT * FROM adresy_v2 WHERE stat = (?) and obec = (?) and ulice = (?) and castObce = (?) and cisloPo = (?) and cisloOr = (?) and psc = (?) and okres = (?) and komplet_adresa = (?) and cisloEv = (?) and cisloText = (?)", (statNazev, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, adresaText, cisloEv, cisloText,)) - sidlo_id = c.fetchone() - if sidlo_id == None: - c.execute("INSERT INTO adresy_v2 (stat, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, komplet_adresa, cisloEv, cisloText) VALUES (?,?,?,?,?,?,?,?,?,?,?)", (statNazev, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, adresaText, cisloEv, cisloText)) - address_key = c.lastrowid - else: - address_key = sidlo_id[0] - return address_key - # print(address_key) - except Exception as e: - print(e) - - def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key, element): try: my_iter = predmet_podnikani_elem.findall("podudaje") @@ -523,28 +266,6 @@ def find_predmet_cinnosti(c, ICO, predmet_cinnosti_elem, conn, primary_sql_key, except: pass -def find_sp_zn(c, ICO, elem2, conn, primary_sql_key, element): - try: - zapis_datum = str(get_prop(elem2, ".//zapisDatum")) - vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) - soud = str(get_prop(elem2, ".//spisZn/soud/kod")) - oddil = str(get_prop(elem2, ".//spisZn/oddil")) - vlozka = str(get_prop(elem2, ".//spisZn/vlozka")) - c.execute("INSERT INTO zapis_soudy (company_id, zapis_datum, vymaz_datum, oddil, vlozka, soud) VALUES(?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, oddil, vlozka, soud,)) - if vymaz_datum == "0": - c.execute("UPDATE companies SET oddil = (?), vlozka = (?), soud = (?) WHERE id = (?)",(oddil,vlozka,soud,primary_sql_key,)) - except: - pass - -def find_nazev(c, ICO, elem2, conn, primary_sql_key, element): - try: - zapis_datum = str(get_prop(elem2, ".//zapisDatum")) - vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) - nazev = str(get_prop(elem2, ".//hodnotaText")) - c.execute("INSERT INTO nazvy (company_id, zapis_datum, vymaz_datum, nazev_text) VALUES(?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, nazev,)) - except: - pass - def find_zakladni_kapital(c, ICO, elem2, conn, primary_sql_key, element): try: zapis_datum = str(get_prop(elem2, ".//zapisDatum")) @@ -588,13 +309,69 @@ def find_akcie(c, ICO, ostatni_akcie_elem, conn, primary_sql_key, element): except: pass +def find_dozorci_rada(c, ICO, elem2, conn, primary_sql_key, element): + try: + zapis_datum = str(get_prop(elem2, "zapisDatum")) + vymaz_datum = str(get_prop(elem2, "vymazDatum")) + c.execute("INSERT into dozorci_rada_relation (company_id, zapis_datum, vymaz_datum) VALUES (?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum,)) + c.execute("SELECT id FROM dozorci_rada_relation WHERE company_id = (?) and zapis_datum = (?)", (primary_sql_key,zapis_datum,)) + 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_DOZORCI_RADA": + find_pocet_clenu_dr(c, ICO, elem, conn, relationship_table_key, element) + elif udajTyp_name == "DOZORCI_RADA_CLEN": + find_clen_dr(c, ICO, elem, conn, relationship_table_key, element) + # find_clen_dr(c, ICO, elem, conn, relationship_table_key, element) + except Exception as f: + print(f) -def insert_individual_relations_v2(c, ICO, conn, primary_sql_key, zapis_datum, vymaz_datum, hodnota_text): - insert_into_ancillary_table(c, elem, inserted_figure) - return 0 +def find_prokura(c, ICO, elem2, conn, primary_sql_key, element): + try: + my_iter = elem2.findall("podudaje/Udaj") + for elem in my_iter: + typ_zapis = str(get_prop(elem, "udajTyp/kod")) + if typ_zapis == "PROKURA_OSOBA": + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + text_prokurista = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) + prokurista_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) + # adresa_id = find_and_store_address(c, elem) + adresa_id = sidlo3(c, elem, primary_sql_key) + c.execute("INSERT INTO prokuriste (company_id, zapis_datum, vymaz_datum, prokurista_fo_id, adresa_id, text_prokurista) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, prokurista_fo_id, adresa_id, text_prokurista,)) + else: + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + prokura_text = str(get_prop(elem, "hodnotaText")) + c.execute("INSERT INTO prokura_common_texts (company_id, zapis_datum, vymaz_datum, prokura_text) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, prokura_text,)) + except Exception as f: + print(f) +def find_sole_shareholder(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")) + text_akcionar = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) + typ_akcionar = str(get_prop(elem, "hodnotaUdaje/T")) + if typ_akcionar == "P": + spol_ico = str(get_prop(elem, "osoba/ico")) + regCislo = str(get_prop(elem, "osoba/regCislo")) + akcionar_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo) + # adresa_id = find_and_store_address(c, elem) + adresa_id = sidlo3(c, elem, primary_sql_key) + c.execute("INSERT into jediny_akcionar (company_id, zapis_datum, vymaz_datum, text_akcionar, akcionar_po_id, adresa_id) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_akcionar, akcionar_po_id, adresa_id,)) + elif typ_akcionar == "F": + akcionar_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) + # adresa_id = find_and_store_address(c, elem) + adresa_id = sidlo3(c, elem, primary_sql_key) + c.execute("INSERT into jediny_akcionar (company_id, zapis_datum, vymaz_datum, text_akcionar, akcionar_fo_id, adresa_id) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_akcionar, akcionar_fo_id, adresa_id,)) + except Exception as f: + print(f) -def find_active_insolvency(c, ICO, insolvency_elem, conn, primary_sql_key): +def find_insolvency(c, ICO, insolvency_elem, conn, primary_sql_key): try: my_iter = insolvency_elem.findall("podudaje") for elem in my_iter: @@ -611,7 +388,7 @@ def find_active_insolvency(c, ICO, insolvency_elem, conn, primary_sql_key): except: pass -def find_active_konkurz(c, ICO, konkurz_elem, conn, primary_sql_key): +def find_konkurz(c, ICO, konkurz_elem, conn, primary_sql_key): try: my_iter = konkurz_elem.findall("podudaje") for elem in my_iter: @@ -628,38 +405,38 @@ def find_active_konkurz(c, ICO, konkurz_elem, conn, primary_sql_key): except: pass -def get_primary_sql_key(c, ICO): +def sidlo3(c, elem, primary_sql_key): try: - primary_key = c.execute("SELECT id FROM companies WHERE ico = (?)", (ICO,)) - primary_key = c.fetchone() - return primary_key[0] - except: - return 0 - - return - -def insert_primary_company_figures(c, ICO, element, conn): - insert_instructions = [("nazev","nazev"), ("zapisDatum","zapis"), (".//udaje/Udaj/spisZn/oddil","oddil"), - (".//udaje/Udaj/spisZn/vlozka","vlozka"),(".//udaje/Udaj/spisZn/soud/kod","soud")] - for elem in insert_instructions: - insert_prop(c, get_prop(element, elem[0]), conn, ICO, elem[1]) - # Override to insert the address - # insert_prop(c, insert_instructions[-1][0], conn, ICO, insert_instructions[-1][1]) - return 0 + statNazev = get_prop(elem, ".//statNazev") + obec = get_prop(elem, ".//obec") + ulice = get_prop(elem, ".//ulice") + castObce = get_prop(elem, ".//castObce") + cisloPo = get_prop(elem, ".//cisloPo") + cisloOr = get_prop(elem, ".//cisloOr") + psc = get_prop(elem, ".//psc") + okres = get_prop(elem, ".//okres") + adresaText = get_prop(elem, ".//adresaText") + cisloEv = get_prop(elem, ".//cisloEv") + cisloText = get_prop(elem, ".//cisloText") + c.execute("SELECT * FROM adresy_v2 WHERE stat = (?) and obec = (?) and ulice = (?) and castObce = (?) and cisloPo = (?) and cisloOr = (?) and psc = (?) and okres = (?) and komplet_adresa = (?) and cisloEv = (?) and cisloText = (?)", (statNazev, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, adresaText, cisloEv, cisloText,)) + sidlo_id = c.fetchone() + if sidlo_id == None: + c.execute("INSERT INTO adresy_v2 (stat, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, komplet_adresa, cisloEv, cisloText) VALUES (?,?,?,?,?,?,?,?,?,?,?)", (statNazev, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, adresaText, cisloEv, cisloText)) + address_key = c.lastrowid + else: + address_key = sidlo_id[0] + return address_key + # print(address_key) + except Exception as e: + print(e) -def insert_company_relations(c, ICO, element, conn, primary_sql_key): - insert_instructions = [(".//udaje/Udaj/pravniForma/nazev","pravni_formy", "pravni_forma", "pravni_formy_relation")] - for elem in insert_instructions: - insert_individual_relations(c, ICO, element, conn, primary_sql_key, elem) +def insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum): + try: + c.execute("INSERT INTO " + elem[3] + " VALUES(NULL, ?, ?, ?, ?)", (primary_sql_key, ancillary_table_key,zapis_datum, vymaz_datum,)) + except Exception as f: + print(f) return 0 -def insert_individual_relations(c, ICO, element, conn, primary_sql_key, elem): - inserted_figure = str(get_prop(element, elem[0])) - insert_into_ancillary_table(c, elem, inserted_figure) - ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) - insert_relation_information(c, elem, primary_sql_key, ancillary_table_key) - return ancillary_table_key - def insert_into_ancillary_table(c, elem, inserted_figure): try: c.execute("INSERT INTO " + elem[1] + "(" + elem[2] + ") VALUES(?)", (inserted_figure,)) @@ -673,292 +450,545 @@ 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,)) - except: - pass - return 0 -def insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum): +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 find_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element): try: - c.execute("INSERT INTO " + elem[3] + " VALUES(NULL, ?, ?, ?, ?)", (primary_sql_key, ancillary_table_key,zapis_datum, vymaz_datum,)) + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + pocet_clenu_number = str(get_prop(elem, "hodnotaText")) + c.execute("INSERT into pocty_clenu_organu (organ_id, pocet_clenu_value, zapis_datum, vymaz_datum) VALUES (?,?,?,?)", (relationship_table_key, pocet_clenu_number, zapis_datum, vymaz_datum,)) except Exception as f: print(f) - return 0 -def insert_obec_relation(c, conn, ICO, element, primary_sql_key): - obec = str(get_prop(element, ".//udaje/Udaj/adresa/obec")) - # Insert a municipality into a table with municipalites +def find_zpusob_jednani(c, ICO, elem, conn, relationship_table_key, element): try: - c.execute("INSERT INTO obce (obec_jmeno) VALUES(?)", (obec,)) - except: - pass - # Get municipality sql_id + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + zpusob_jednani = str(get_prop(elem, "hodnotaText")) + insert_instructions = [(zpusob_jednani,"zpusoby_jednani", "zpusob_jednani_text", "zpusoby_jednani_relation")] + for elem in insert_instructions: + insert_into_ancillary_table(c, elem, zpusob_jednani) + ancillary_table_key = get_anciallary_table_key(c, elem, zpusob_jednani) + insert_relation_information_v2(c, elem, relationship_table_key, ancillary_table_key, zapis_datum, vymaz_datum) + except Exception as f: + print(f) + +def find_clen_statut_org(c, ICO, elem, conn, relationship_table_key, element): try: - municipality_key = c.execute("SELECT id FROM obce WHERE obec_jmeno = (?)", (obec,)) - municipality_key = c.fetchone()[0] - except: - print("Nepovedlo se") - # Establish a relational link + 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) + # adresa_id = find_and_store_address(c, elem) + adresa_id = sidlo3(c, elem, relationship_table_key) + 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 insert_individual_relations_v2(c, ICO, conn, primary_sql_key, zapis_datum, vymaz_datum, hodnota_text): + insert_into_ancillary_table(c, elem, inserted_figure) + return 0 + +def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element): try: - c.execute("INSERT INTO obec_relation VALUES(?, ?)", (primary_sql_key, municipality_key,)) + 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 - return - -def zkusit_najit_vsechny_osoby(element): - stat_list = element.iter('osoba') - temp_osoby = [] - for elem in stat_list: - try: - osoba_temp = "" - osoba_temp += get_prop(element, ".//jmeno") + " " - osoba_temp += get_prop(element, ".//prijmeni") + ", nar. " - osoba_temp += get_prop(element, ".//narozDatum") - temp_osoby.append(osoba_temp) - except: - pass - return temp_osoby - -def zkusit_najit_vsechny_adresy(element): - stat_list = element.iter('adresa') - temp_adresy = [] - for elem in stat_list: - temp_adresy.append(str(adresa(get_SIDLO_v3(elem)))) - return temp_adresy - -def find_business(element): - subjekt_udaje = element.findall('.//Udaj') - for udaj in subjekt_udaje: - udaje_spolecnosti = udaj.findall(".//kod") - if "PREDMET_PODNIKANI_SEKCE" in udaje_spolecnosti[0].text: - predmety2 = [elem.text.replace(u'\xa0', u' ') for elem in udaj.iterfind(".//hodnotaText")] - return predmety2 - # TODO - Filter areas that are no longer relevant - -def insert_obec(c, obec, conn, ICO, sql_id): - try: - c.execute("INSERT INTO obce (obec_jmeno) VALUES(?)", (obec,)) +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 insert_adresa(c, adresa, conn, ICO, sql_id): +def find_osoba_id(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni): try: - c.execute("INSERT INTO adresy (adresa_jmeno) VALUES(?)", (adresa,)) - except: - pass + 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 insert_osoba(c, osoba, conn, ICO, sql_id): +def find_pravnicka_osoba(c, elem, spol_ico, regCislo): try: - c.execute("INSERT INTO osoby (osoba_jmeno) VALUES(?)", (osoba,)) - except: - pass + nazev = str(get_prop(elem, "osoba/nazev")) + insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev) + osoba_id = find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev) + return osoba_id + except Exception as f: + print(f) +def find_pocet_clenu_dr(c, ICO, elem, conn, relationship_table_key, element): + try: + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + pocet_clenu_number = str(get_prop(elem, "hodnotaText")) + c.execute("INSERT into pocty_clenu_DR (organ_id, pocet_clenu_value, zapis_datum, vymaz_datum) VALUES (?,?,?,?)", (relationship_table_key, pocet_clenu_number, zapis_datum, vymaz_datum,)) + except Exception as f: + print(f) -def insert_ulice(c, ulice, conn, ICO, sql_id): +def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element): try: - c.execute("INSERT INTO ulice (ulice_jmeno) VALUES(?)", (ulice,)) - except: - pass + 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) + # adresa_id = find_and_store_address(c, elem) + adresa_id = sidlo3(c, elem, relationship_table_key) + c.execute("INSERT into dr_organ_clen_relation (dozorci_rada_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 insert_prop_v2(c, prop, conn, ICO, column, table, sql_id): - if prop != None: - for elem in prop: - c.execute("INSERT INTO predmety_podnikani (company_id, predmet_podnikani) VALUES(?,?)", (sql_id, elem,)) +def insert_podily(c, elem, spolecnik_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 (spolecnik_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (spolecnik_id, zapisDatum, vymazDatum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text,)) + except Exception as f: + print(f) -# Function to attempt to insert a placeholder for a new company based on ICO -def insert_new_ICO(c, ICO, conn, element): +def get_druh_podilu_id(c, podil_elem): + try: + druhPodilu = str(get_prop(podil_elem, "hodnotaUdaje/druhPodilu")) + insert_druh_podilu(c, podil_elem, druhPodilu) + druh_podilu_id = find_druh_podilu_id(c, druhPodilu) + return druh_podilu_id + except Exception as f: + print(f) +def insert_druh_podilu(c, podil_elem, druhPodilu): try: - datum_zapis = str(get_prop(element, "zapisDatum")) - nazev = str(get_prop(element, "nazev")) - c.execute("INSERT INTO companies (ico, zapis, nazev) VALUES (?,?,?);", (ICO,datum_zapis,nazev,)) - return c.lastrowid + c.execute("INSERT INTO druhy_podilu (druh_podilu) VALUES (?)", (druhPodilu,)) except: pass -def get_ICO(element): +def find_druh_podilu_id(c, druhPodilu): try: - return element.find('ico').text - except: - return "00000000" + druh_podilu_id = c.execute("SELECT id FROM druhy_podilu WHERE druh_podilu = (?)", (druhPodilu,)) + druh_podilu_id = c.fetchone()[0] + return druh_podilu_id + except Exception as f: + print(f) -def get_prop(element, prop): +def find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev): try: - return element.find(prop).text - except: - return "0" + anciallary_table_key = c.execute("SELECT id FROM pravnicke_osoby WHERE ico = (?) and reg_cislo = (?) and nazev = (?)", (spol_ico, regCislo, nazev,)) + anciallary_table_key = c.fetchone()[0] + return anciallary_table_key + except Exception as f: + print(f) -def insert_prop(c, prop, conn, ICO, column): +def insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev): try: - c.execute("UPDATE companies SET (%s) = (?) WHERE ico = (?)" % (column), (prop, ICO,)) - except Exception as e: - print(e) + c.execute("INSERT into pravnicke_osoby (ico, reg_cislo, nazev) VALUES (?,?,?)", (spol_ico, regCislo, nazev,)) + except: + pass + + + + + + + + + + + + + + + + + -def get_SIDLO_v2(element): - address_field = [] - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/statNazev")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/obec")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/ulice")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/castObce")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloPo")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloOr")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/psc")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/okres")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/adresaText")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloEv")) - address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloText")) - if address_field[0] == "Česká republika - neztotožněno": - address_field[0] = "Česká republika" - for i in range(len(address_field)): - if address_field[i] == "0": - address_field[i] = None - return address_field - -def get_SIDLO_v3(element): - address_field = [] - address_field.append(get_prop(element, ".//statNazev")) - address_field.append(get_prop(element, ".//obec")) - address_field.append(get_prop(element, ".//ulice")) - address_field.append(get_prop(element, ".//castObce")) - address_field.append(get_prop(element, ".//cisloPo")) - address_field.append(get_prop(element, ".//cisloOr")) - address_field.append(get_prop(element, ".//psc")) - address_field.append(get_prop(element, ".//okres")) - address_field.append(get_prop(element, ".//adresaText")) - address_field.append(get_prop(element, ".//cisloEv")) - address_field.append(get_prop(element, ".//cisloText")) - if address_field[0] == "Česká republika - neztotožněno": - address_field[0] = "Česká republika" - for i in range(len(address_field)): - if address_field[i] == "0": - address_field[i] = None - return address_field - - -class adresa(object): - def __init__(self, adresa): - self.stat = adresa[0] - self.obec = adresa[1] - self.ulice = adresa[2] - self.castObce = adresa[3] - self.cisloPo = adresa[4] - self.cisloOr = adresa[5] - self.psc = adresa[6] - self.okres = adresa[7] - self.komplet_adresa = adresa[8] - self.cisloEv = adresa[9] - self.cisloText = adresa[10] - - def __str__ (self): - try: - # if self.obec == "-": - # return("Neznama adresa") - if self.komplet_adresa != None: - if self.stat != None: - return str(self.komplet_adresa + " " + self.stat) - else: - return str(self.komplet_adresa) - # if self.obec == None: - # return("Neznama adresa") - if self.cisloText != None: - if self.ulice == None: - if self.psc != None: - return str(self.cisloText + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) - else: - return str(self.cisloText + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) - if self.okres == None and self.castObce != None: - if self.psc != None: - return str(self.obec + " - " + self.castObce + ", " + self.ulice + " " + self.cisloText + ", PSČ " + self.psc) - else: - return str(self.obec + " - " + self.castObce + ", " + self.ulice + " " + self.cisloText) - if self.okres == None and self.castObce == None and self.psc != None: - return str(self.obec + ", " + self.ulice + " " + self.cisloText + ", PSČ " + self.psc) - if self.castObce == None and self.psc == None: - return str(self.obec + ", " + self.ulice + " " + self.cisloText) - else: - if self.psc != None and self.ulice == None: - return str(self.obec + " " + self.cisloText + " " + "okres " + self.okres + ", PSČ " + self.psc) - elif self.ulice == None: - return str(self.obec + " " + self.cisloText + " " + "okres " + self.okres) - elif self.obec != None and self.ulice != None and self.psc != None: - return str(self.obec + ", " + self.ulice + " " + self.cisloText + ", PSČ " + self.psc) - elif self.obec != None and self.ulice != None: - return str(self.obec + ", " + self.ulice + " " + self.cisloText) - if self.ulice != None : - if self.cisloOr != None: - if self.cisloPo == None: - return str(self.ulice + " " + self.cisloOr + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) - elif self.psc != None: - return str(self.ulice + " " + self.cisloPo + "/" + self.cisloOr + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) - else: - return str(self.ulice + " " + self.cisloPo + "/" + self.cisloOr + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) - if self.cisloPo == None: - if self.cisloEv == None: - if self.psc != None: - return str(self.obec + ", " + self.ulice + "" + srovnat_obec_cast(self.obec, self.castObce) + ", PSČ " + self.psc + " " + self.stat) - else: - return str(self.obec + ", " + self.ulice + "" + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.stat) - else: - return str(self.ulice + " č.ev. " + self.cisloEv + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) - else: - if self.psc != None: - return str(self.ulice + " " + self.cisloPo + "" + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) - else: - return str(self.ulice + " " + self.cisloPo + "" + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) - if self.cisloPo == None and self.cisloEv != None: - return str(self.obec + " č.ev. " + self.cisloEv + ", " + self.psc + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) - if self.cisloPo != None: - return str("č.p. " + self.cisloPo + ", " + self.psc + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) +# 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) - if self.cisloPo == None and self.cisloEv == None and self.ulice == None: - return (self.obec + " " + self.stat) +# 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) - except TypeError: - temp_adr = [] - if self.ulice != None: - temp_adr.append(self.ulice) - if self.obec != None: - temp_adr.append(self.obec) - if self.castObce != None: - temp_adr.append(self.castObce) +# def insert_address(c, addr): +# try: +# c.execute("INSERT into adresy (adresa_text) VALUES (?)", (addr,)) +# except: +# pass - if self.cisloPo != None: - temp_adr.append(self.cisloPo) - if self.cisloOr != None: - temp_adr.append(self.cisloOr) - if self.psc != None: - temp_adr.append(self.psc) - if self.okres != None: - temp_adr.append(self.okres) - if self.cisloEv != None: - temp_adr.append(self.cisloEv) - if self.cisloText != None: - temp_adr.append(self.cisloText) - if self.stat != None: - temp_adr.append(self.stat) +# def sidlo2(c, elem, primary_sql_key): +# try: +# statNazev = get_prop(elem, ".//statNazev") +# obec = get_prop(elem, ".//obec") +# ulice = get_prop(elem, ".//ulice") +# castObce = get_prop(elem, ".//castObce") +# cisloPo = get_prop(elem, ".//cisloPo") +# cisloOr = get_prop(elem, ".//cisloOr") +# psc = get_prop(elem, ".//psc") +# okres = get_prop(elem, ".//okres") +# adresaText = get_prop(elem, ".//adresaText") +# cisloEv = get_prop(elem, ".//cisloEv") +# cisloText = get_prop(elem, ".//cisloText") +# c.execute("INSERT INTO adresy_v2 (stat, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, komplet_adresa, cisloEv, cisloText, company_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (statNazev, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, adresaText, cisloEv, cisloText, primary_sql_key)) +# except: +# pass - listToStr = ' '.join([str(elem) for elem in temp_adr]) - return listToStr -def srovnat_obec_cast(obec, cast_obce): - if obec == cast_obce: - return str("") - elif cast_obce == None: - return str("") - else: - return str(", " + cast_obce)
\ No newline at end of file + + + + + + + + + + + + + + + + + + + + + + + + + +# def insert_primary_company_figures(c, ICO, element, conn): +# insert_instructions = [("nazev","nazev"), ("zapisDatum","zapis"), (".//udaje/Udaj/spisZn/oddil","oddil"), +# (".//udaje/Udaj/spisZn/vlozka","vlozka"),(".//udaje/Udaj/spisZn/soud/kod","soud")] +# for elem in insert_instructions: +# insert_prop(c, get_prop(element, elem[0]), conn, ICO, elem[1]) +# # Override to insert the address +# # insert_prop(c, insert_instructions[-1][0], conn, ICO, insert_instructions[-1][1]) +# return 0 + + + + + + + + + + + + + +# def insert_obec_relation(c, conn, ICO, element, primary_sql_key): +# obec = str(get_prop(element, ".//udaje/Udaj/adresa/obec")) +# # Insert a municipality into a table with municipalites +# try: +# c.execute("INSERT INTO obce (obec_jmeno) VALUES(?)", (obec,)) +# except: +# pass +# # Get municipality sql_id +# try: +# municipality_key = c.execute("SELECT id FROM obce WHERE obec_jmeno = (?)", (obec,)) +# municipality_key = c.fetchone()[0] +# except: +# print("Nepovedlo se") +# # Establish a relational link +# try: +# c.execute("INSERT INTO obec_relation VALUES(?, ?)", (primary_sql_key, municipality_key,)) +# except: +# pass + +# return + +# def zkusit_najit_vsechny_osoby(element): +# stat_list = element.iter('osoba') +# temp_osoby = [] +# for elem in stat_list: +# try: +# osoba_temp = "" +# osoba_temp += get_prop(element, ".//jmeno") + " " +# osoba_temp += get_prop(element, ".//prijmeni") + ", nar. " +# osoba_temp += get_prop(element, ".//narozDatum") +# temp_osoby.append(osoba_temp) +# except: +# pass +# return temp_osoby + +# def zkusit_najit_vsechny_adresy(element): +# stat_list = element.iter('adresa') +# temp_adresy = [] +# for elem in stat_list: +# temp_adresy.append(str(adresa(get_SIDLO_v3(elem)))) +# return temp_adresy + +# def find_business(element): +# subjekt_udaje = element.findall('.//Udaj') +# for udaj in subjekt_udaje: +# udaje_spolecnosti = udaj.findall(".//kod") +# if "PREDMET_PODNIKANI_SEKCE" in udaje_spolecnosti[0].text: +# predmety2 = [elem.text.replace(u'\xa0', u' ') for elem in udaj.iterfind(".//hodnotaText")] +# return predmety2 +# # TODO - Filter areas that are no longer relevant + +# def insert_obec(c, obec, conn, ICO, sql_id): +# try: +# c.execute("INSERT INTO obce (obec_jmeno) VALUES(?)", (obec,)) +# except: +# pass + +# def insert_adresa(c, adresa, conn, ICO, sql_id): +# try: +# c.execute("INSERT INTO adresy (adresa_jmeno) VALUES(?)", (adresa,)) +# except: +# pass + +# def insert_osoba(c, osoba, conn, ICO, sql_id): +# try: +# c.execute("INSERT INTO osoby (osoba_jmeno) VALUES(?)", (osoba,)) +# except: +# pass + + +# def insert_ulice(c, ulice, conn, ICO, sql_id): +# try: +# c.execute("INSERT INTO ulice (ulice_jmeno) VALUES(?)", (ulice,)) +# except: +# pass + +# def insert_prop_v2(c, prop, conn, ICO, column, table, sql_id): +# if prop != None: +# for elem in prop: +# c.execute("INSERT INTO predmety_podnikani (company_id, predmet_podnikani) VALUES(?,?)", (sql_id, elem,)) + + + + + +def get_prop(element, prop): + try: + return element.find(prop).text + except: + return "0" + +# def insert_prop(c, prop, conn, ICO, column): +# try: +# c.execute("UPDATE companies SET (%s) = (?) WHERE ico = (?)" % (column), (prop, ICO,)) +# except Exception as e: +# print(e) + +# def get_SIDLO_v2(element): +# address_field = [] +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/statNazev")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/obec")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/ulice")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/castObce")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloPo")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloOr")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/psc")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/okres")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/adresaText")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloEv")) +# address_field.append(get_prop(element, ".//udaje/Udaj/adresa/cisloText")) +# if address_field[0] == "Česká republika - neztotožněno": +# address_field[0] = "Česká republika" +# for i in range(len(address_field)): +# if address_field[i] == "0": +# address_field[i] = None +# return address_field + +# def get_SIDLO_v3(element): +# address_field = [] +# address_field.append(get_prop(element, ".//statNazev")) +# address_field.append(get_prop(element, ".//obec")) +# address_field.append(get_prop(element, ".//ulice")) +# address_field.append(get_prop(element, ".//castObce")) +# address_field.append(get_prop(element, ".//cisloPo")) +# address_field.append(get_prop(element, ".//cisloOr")) +# address_field.append(get_prop(element, ".//psc")) +# address_field.append(get_prop(element, ".//okres")) +# address_field.append(get_prop(element, ".//adresaText")) +# address_field.append(get_prop(element, ".//cisloEv")) +# address_field.append(get_prop(element, ".//cisloText")) +# if address_field[0] == "Česká republika - neztotožněno": +# address_field[0] = "Česká republika" +# for i in range(len(address_field)): +# if address_field[i] == "0": +# address_field[i] = None +# return address_field + + +# class adresa(object): +# def __init__(self, adresa): +# self.stat = adresa[0] +# self.obec = adresa[1] +# self.ulice = adresa[2] +# self.castObce = adresa[3] +# self.cisloPo = adresa[4] +# self.cisloOr = adresa[5] +# self.psc = adresa[6] +# self.okres = adresa[7] +# self.komplet_adresa = adresa[8] +# self.cisloEv = adresa[9] +# self.cisloText = adresa[10] + +# def __str__ (self): +# try: +# # if self.obec == "-": +# # return("Neznama adresa") +# if self.komplet_adresa != None: +# if self.stat != None: +# return str(self.komplet_adresa + " " + self.stat) +# else: +# return str(self.komplet_adresa) +# # if self.obec == None: +# # return("Neznama adresa") +# if self.cisloText != None: +# if self.ulice == None: +# if self.psc != None: +# return str(self.cisloText + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) +# else: +# return str(self.cisloText + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) +# if self.okres == None and self.castObce != None: +# if self.psc != None: +# return str(self.obec + " - " + self.castObce + ", " + self.ulice + " " + self.cisloText + ", PSČ " + self.psc) +# else: +# return str(self.obec + " - " + self.castObce + ", " + self.ulice + " " + self.cisloText) +# if self.okres == None and self.castObce == None and self.psc != None: +# return str(self.obec + ", " + self.ulice + " " + self.cisloText + ", PSČ " + self.psc) +# if self.castObce == None and self.psc == None: +# return str(self.obec + ", " + self.ulice + " " + self.cisloText) +# else: +# if self.psc != None and self.ulice == None: +# return str(self.obec + " " + self.cisloText + " " + "okres " + self.okres + ", PSČ " + self.psc) +# elif self.ulice == None: +# return str(self.obec + " " + self.cisloText + " " + "okres " + self.okres) +# elif self.obec != None and self.ulice != None and self.psc != None: +# return str(self.obec + ", " + self.ulice + " " + self.cisloText + ", PSČ " + self.psc) +# elif self.obec != None and self.ulice != None: +# return str(self.obec + ", " + self.ulice + " " + self.cisloText) +# if self.ulice != None : +# if self.cisloOr != None: +# if self.cisloPo == None: +# return str(self.ulice + " " + self.cisloOr + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) +# elif self.psc != None: +# return str(self.ulice + " " + self.cisloPo + "/" + self.cisloOr + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) +# else: +# return str(self.ulice + " " + self.cisloPo + "/" + self.cisloOr + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) +# if self.cisloPo == None: +# if self.cisloEv == None: +# if self.psc != None: +# return str(self.obec + ", " + self.ulice + "" + srovnat_obec_cast(self.obec, self.castObce) + ", PSČ " + self.psc + " " + self.stat) +# else: +# return str(self.obec + ", " + self.ulice + "" + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.stat) +# else: +# return str(self.ulice + " č.ev. " + self.cisloEv + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) +# else: +# if self.psc != None: +# return str(self.ulice + " " + self.cisloPo + "" + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.psc + " " + self.obec + ", " + self.stat) +# else: +# return str(self.ulice + " " + self.cisloPo + "" + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) + +# if self.cisloPo == None and self.cisloEv != None: +# return str(self.obec + " č.ev. " + self.cisloEv + ", " + self.psc + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) + +# if self.cisloPo != None: +# return str("č.p. " + self.cisloPo + ", " + self.psc + srovnat_obec_cast(self.obec, self.castObce) + ", " + self.obec + ", " + self.stat) + +# if self.cisloPo == None and self.cisloEv == None and self.ulice == None: +# return (self.obec + " " + self.stat) + +# except TypeError: +# temp_adr = [] +# if self.ulice != None: +# temp_adr.append(self.ulice) + +# if self.obec != None: +# temp_adr.append(self.obec) + +# if self.castObce != None: +# temp_adr.append(self.castObce) + +# if self.cisloPo != None: +# temp_adr.append(self.cisloPo) + +# if self.cisloOr != None: +# temp_adr.append(self.cisloOr) + +# if self.psc != None: +# temp_adr.append(self.psc) + +# if self.okres != None: +# temp_adr.append(self.okres) + +# if self.cisloEv != None: +# temp_adr.append(self.cisloEv) + +# if self.cisloText != None: +# temp_adr.append(self.cisloText) + +# if self.stat != None: +# temp_adr.append(self.stat) + +# listToStr = ' '.join([str(elem) for elem in temp_adr]) + +# return listToStr + +# def srovnat_obec_cast(obec, cast_obce): +# if obec == cast_obce: +# return str("") +# elif cast_obce == None: +# return str("") +# else: +# return str(", " + cast_obce)
\ No newline at end of file |