diff options
Diffstat (limited to 'update_db.py')
| -rw-r--r-- | update_db.py | 130 |
1 files changed, 122 insertions, 8 deletions
diff --git a/update_db.py b/update_db.py index 6e51e78..8444e5b 100644 --- a/update_db.py +++ b/update_db.py @@ -111,6 +111,9 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key): find_insolvency(c, ICO, elem2, conn, primary_sql_key) elif udajTyp_name == "KONKURS_SEKCE": find_konkurz(c, ICO, elem2, conn, primary_sql_key) + elif udajTyp_name == "SKUTECNY_MAJITEL_SEKCE": + find_UBO(c, ICO, elem2, conn, primary_sql_key, element) + except: pass @@ -184,16 +187,37 @@ def find_statutar(c, ICO, elem2, conn, primary_sql_key, element): except Exception as f: print(f) +def find_UBO(c, ICO, elem2, conn, primary_sql_key, element): + try: + my_iter = elem2.findall("podudaje/Udaj") + for elem in my_iter: + zapis_datum = str(get_prop(elem, "zapisDatum")) + vymaz_datum = str(get_prop(elem, "vymazDatum")) + postaveni = str(get_prop(elem, "hodnotaUdaje/postaveni")).split(";")[0] + koncovyPrijemceText = str(get_prop(elem, "hodnotaUdaje/koncovyPrijemceText")) + skutecnymMajitelemOd = str(get_prop(elem, "hodnotaUdaje/skutecnymMajitelemOd")) + vlastniPodilNaProspechu = str(get_prop(elem, "hodnotaUdaje/vlastniPodilNaProspechu")) + vlastniPodilNaProspechu_typ = str(get_prop(elem, "hodnotaUdaje/podilNaProspechu/typ")) + vlastniPodilNaProspechu_textValue = str(get_prop(elem, "hodnotaUdaje/podilNaProspechu/textValue")) + vlastniPodilNaHlasovani = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani")) + vlastniPodilNaHlasovani_typ = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani/typ")) + vlastniPodilNaHlasovani_value = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani/textValue")) + adresa_id = find_sidlo(c, elem, primary_sql_key) + UBO_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element, adresa_id) + c.execute("INSERT INTO ubo (company_id, UBO_id, adresa_id, zapis_datum, vymaz_datum, postaveni, koncovyPrijemceText, skutecnymMajitelemOd, vlastniPodilNaProspechu, vlastniPodilNaProspechu_typ, vlastniPodilNaProspechu_textValue, vlastniPodilNaHlasovani, vlastniPodilNaHlasovani_typ, vlastniPodilNaHlasovani_value) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (primary_sql_key, UBO_id, adresa_id, zapis_datum, vymaz_datum, postaveni, koncovyPrijemceText, skutecnymMajitelemOd, vlastniPodilNaProspechu, vlastniPodilNaProspechu_typ, vlastniPodilNaProspechu_textValue, vlastniPodilNaHlasovani, vlastniPodilNaHlasovani_typ, vlastniPodilNaHlasovani_value,)) + except Exception as f: + print(f) + def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): try: my_iter = elem2.findall("podudaje/Udaj") for elem in my_iter: - spolecnik_type = str(get_prop(elem, "udajTyp/kod")) + spolecnik_kod = str(get_prop(elem, "udajTyp/kod")) zapis_datum = str(get_prop(elem, "zapisDatum")) vymaz_datum = str(get_prop(elem, "vymazDatum")) spolecnik_typ = str(get_prop(elem, "hodnotaUdaje/typ")) # TODO Chech these conditions, they sometimes cause a person not being stored (IC 27650081) - if spolecnik_type == "SPOLECNIK_OSOBA" and spolecnik_typ == "OSOBA": + if spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "OSOBA": # TODO alternativy pro None, Spolecny podil a Uvolneny podil text_spolecnik = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) nazev = str(get_prop(elem, "osoba/nazev")) @@ -213,10 +237,49 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): c.execute("INSERT INTO spolecnici (company_id, spolecnik_po_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, spolecnik_po_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik,)) c.execute ("SELECT last_insert_rowid()") spolecnik_id = c.fetchone()[0] - insert_podily(c, elem, spolecnik_id) + insert_podily(c, elem, spolecnik_id) + + elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "SPOLECNY_PODIL": + text_spolecny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) + c.execute("INSERT INTO spolecnici_spolecny_podil (company_id, zapis_datum, vymaz_datum, text_spolecny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_spolecny_podil,)) + c.execute ("SELECT last_insert_rowid()") + spolecny_op_id = c.fetchone()[0] + insert_common_podily(c, elem, spolecny_op_id) + insert_common_shareholders(c, elem, spolecny_op_id) + + elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "UVOLNENY_PODIL": + text_uvolneny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) + c.execute("INSERT INTO spolecnici_uvolneny_podil (company_id, zapis_datum, vymaz_datum, text_uvolneny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_uvolneny_podil,)) + c.execute ("SELECT last_insert_rowid()") + uvolneny_op_id = c.fetchone()[0] + insert_vacant_podily(c, elem, uvolneny_op_id) except Exception as f: print(f) +def insert_common_shareholders(c, elem, spolecny_op_id): + try: + podil_iter = elem.findall("podudaje/Udaj") + for podil_elem in podil_iter: + if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODILNIK": + zapisDatum = str(get_prop(podil_elem, "zapisDatum")) + vymazDatum = str(get_prop(podil_elem, "vymazDatum")) + typ_podilnika = str(get_prop(podil_elem, "hodnotaText")) + if typ_podilnika == "AngazmaFyzicke": + adresa_id = find_sidlo(c, podil_elem, spolecny_op_id) + spolecnik_fo_id = find_fyzicka_osoba(c, 0, podil_elem, 0, spolecny_op_id, 0, adresa_id) + c.execute("INSERT INTO podilnici (podil_id, podilnik_fo_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_fo_id, zapisDatum, vymazDatum, adresa_id)) + if typ_podilnika == "AngazmaPravnicke": + spol_ico = str(get_prop(podil_elem, "osoba/ico")) + regCislo = str(get_prop(podil_elem, "osoba/regCislo")) + adresa_id = find_sidlo(c, podil_elem, spolecny_op_id) + spolecnik_po_id = find_pravnicka_osoba(c, podil_elem, spol_ico, regCislo, adresa_id) + c.execute("INSERT INTO podilnici (podil_id, podilnik_po_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_po_id, zapisDatum, vymazDatum, adresa_id)) + except Exception as f: + print(f) + + + + def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key, element): try: my_iter = predmet_podnikani_elem.findall("podudaje") @@ -227,7 +290,7 @@ def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) insert_instructions = [(".//hodnotaText","predmety_podnikani", "predmet_podnikani", "predmety_podnikani_relation")] for elem in insert_instructions: - inserted_figure = str(get_prop(elem2, ".//hodnotaText")) + inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize() insert_into_ancillary_table(c, elem, inserted_figure) ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) @@ -244,7 +307,7 @@ def find_predmet_cinnosti(c, ICO, predmet_cinnosti_elem, conn, primary_sql_key, vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) insert_instructions = [(".//hodnotaText","predmety_cinnosti", "predmet_cinnosti", "predmety_cinnosti_relation")] for elem in insert_instructions: - inserted_figure = str(get_prop(elem2, ".//hodnotaText")) + inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize() insert_into_ancillary_table(c, elem, inserted_figure) ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) @@ -261,7 +324,7 @@ def find_ucel(c, ICO, ucel_elem, conn, primary_sql_key, element): vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) insert_instructions = [(".//hodnotaText", "ucel", "ucel", "ucel_relation")] for elem in insert_instructions: - inserted_figure = str(get_prop(elem2, ".//hodnotaText")) + inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize() insert_into_ancillary_table(c, elem, inserted_figure) ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure) insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum) @@ -504,8 +567,8 @@ def insert_individual_relations_v2(c, ICO, conn, primary_sql_key, zapis_datum, v def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, adresa_id): try: - jmeno = str(get_prop(elem, "osoba/jmeno")) - prijmeni = str(get_prop(elem, "osoba/prijmeni")) + jmeno = lower_names_chars(str(get_prop(elem, "osoba/jmeno"))) + prijmeni = lower_names_chars(str(get_prop(elem, "osoba/prijmeni"))) datum_narozeni = str(get_prop(elem, "osoba/narozDatum")) titulPred = str(get_prop(elem, "osoba/titulPred")) titulZa = str(get_prop(elem, "osoba/titulZa")) @@ -515,6 +578,20 @@ def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, adre except: pass +def lower_names_chars(string_name): + updated_name = "" + previous_non_alpha = True + for elem in string_name: + if previous_non_alpha == True: + updated_name += elem + else: + updated_name += elem.lower() + if elem.isalpha() == True: + previous_non_alpha = False + else: + previous_non_alpha = True + return updated_name + def insert_fyzicka_osoba(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni, adresa_id): try: c.execute("INSERT into fyzicke_osoby (titul_pred, jmeno, prijmeni, titul_za, datum_naroz, adresa_id) VALUES (?,?,?,?,?,?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,adresa_id,)) @@ -570,6 +647,7 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element): except Exception as f: print(f) +# TODO MERGE THESE THREE FUNCTIONS INTO ONE def insert_podily(c, elem, spolecnik_id): try: podil_iter = elem.findall("podudaje/Udaj") @@ -587,6 +665,42 @@ def insert_podily(c, elem, spolecnik_id): except Exception as f: print(f) +def insert_vacant_podily(c, elem, vacant_id): + try: + podil_iter = elem.findall("podudaje/Udaj") + for podil_elem in podil_iter: + zapisDatum = str(get_prop(podil_elem, "zapisDatum")) + vymazDatum = str(get_prop(podil_elem, "vymazDatum")) + druh_podilu_id = get_druh_podilu_id(c, podil_elem) + vklad_typ = str(get_prop(podil_elem, "hodnotaUdaje/vklad/typ")) + vklad_text = str(get_prop(podil_elem, "hodnotaUdaje/vklad/textValue")) + souhrn_typ = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/typ")) + souhrn_text = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/textValue")) + splaceni_typ = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/typ")) + splaceni_text = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/textValue")) + c.execute("INSERT INTO podily (uvolneny_podil_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (vacant_id, zapisDatum, vymazDatum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text,)) + except Exception as f: + print(f) + +def insert_common_podily(c, elem, common_id): + try: + podil_iter = elem.findall("podudaje/Udaj") + for podil_elem in podil_iter: + if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODIL": + zapisDatum = str(get_prop(podil_elem, "zapisDatum")) + vymazDatum = str(get_prop(podil_elem, "vymazDatum")) + druh_podilu_id = get_druh_podilu_id(c, podil_elem) + vklad_typ = str(get_prop(podil_elem, "hodnotaUdaje/vklad/typ")) + vklad_text = str(get_prop(podil_elem, "hodnotaUdaje/vklad/textValue")) + souhrn_typ = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/typ")) + souhrn_text = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/textValue")) + splaceni_typ = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/typ")) + splaceni_text = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/textValue")) + c.execute("INSERT INTO podily (spolecny_podil_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (common_id, zapisDatum, vymazDatum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text,)) + except Exception as f: + print(f) + + def get_druh_podilu_id(c, podil_elem): try: druhPodilu = str(get_prop(podil_elem, "hodnotaUdaje/druhPodilu")) |