from datetime import datetime
import os
from lxml import etree
import sqlite3
# The function opens a file and parses the extracted data into the database
def update_DB(file, DB_name):
print("Processing ", str(file))
conn = sqlite3.connect(DB_name)
c = conn.cursor()
for event, element in etree.iterparse(file, tag="Subjekt"):
# Bugfix for companies which have been deleted but appear in the list of existing companies
if ([element.find('vymazDatum')][0]) != None:
continue
else:
ICO = get_ICO(element)
# Bugfix to skip the old companies that have no Identification No.
if ICO == None:
continue
# Vlozit prazdny radek s ICO
insert_new_ICO(c, ICO, element)
primary_sql_key = get_primary_sql_key(c, ICO)
# Vlozit jednolive parametry
insert_company_relations(c, element, primary_sql_key)
find_other_properties(c, ICO, element, conn, primary_sql_key)
element.clear()
conn.commit()
conn.close()
return 0
def get_ICO(element):
try:
return element.find('ico').text
except:
return None
# Function to attempt to insert a placeholder for a new company based on ICO
def insert_new_ICO(c, ICO, 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, element, 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, element, primary_sql_key, elem)
return 0
def insert_individual_relations(c, element, 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.findall("udaje")
for elem in my_iter:
my_iter2 = elem.findall("Udaj")
for elem2 in my_iter2:
udajTyp_name = str(get_prop(elem2, ".//udajTyp/kod"))
if udajTyp_name == "SIDLO":
find_registered_office(c, elem2, primary_sql_key)
elif udajTyp_name == "NAZEV":
find_nazev(c, elem2, primary_sql_key)
elif udajTyp_name == "SPIS_ZN":
find_sp_zn(c, elem2, primary_sql_key)
elif udajTyp_name == "PRAVNI_FORMA":
find_pravni_forma(c, elem2, primary_sql_key)
elif udajTyp_name == "STATUTARNI_ORGAN":
find_statutar(c, elem2, primary_sql_key)
elif udajTyp_name == "SPOLECNIK":
find_spolecnik(c, elem2, primary_sql_key)
elif udajTyp_name == "PREDMET_PODNIKANI_SEKCE":
find_predmet_podnikani(c, elem2, primary_sql_key)
elif udajTyp_name == "PREDMET_CINNOSTI_SEKCE":
find_predmet_cinnosti(c, elem2, primary_sql_key)
elif udajTyp_name == "UCEL_SUBJEKTU_SEKCE":
find_ucel(c, elem2, primary_sql_key)
elif udajTyp_name == "ZAKLADNI_KAPITAL":
find_zakladni_kapital(c, elem2, primary_sql_key)
elif udajTyp_name == "OST_SKUTECNOSTI_SEKCE":
find_ostatni_skutecnosti(c, elem2, primary_sql_key)
elif udajTyp_name == "AKCIE_SEKCE":
find_akcie(c, elem2, primary_sql_key)
elif udajTyp_name == "DOZORCI_RADA":
find_dozorci_rada(c, elem2, primary_sql_key)
elif udajTyp_name == "PROKURA":
find_prokura(c, elem2, primary_sql_key)
elif udajTyp_name == "AKCIONAR_SEKCE":
find_sole_shareholder(c, elem2, primary_sql_key)
elif udajTyp_name == "INSOLVENCE_SEKCE":
find_insolvency(c, elem2, primary_sql_key)
elif udajTyp_name == "KONKURS_SEKCE":
find_konkurz(c, elem2, primary_sql_key)
elif udajTyp_name == "SKUTECNY_MAJITEL_SEKCE":
find_UBO(c, elem2, primary_sql_key)
except:
pass
def find_registered_office(c, elem2, primary_sql_key):
try:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
sidlo_id = find_sidlo(c, elem2)
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 find_nazev(c, elem2, primary_sql_key):
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_sp_zn(c, elem2, primary_sql_key):
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_pravni_forma(c, elem2, primary_sql_key):
try:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
pravni_forma = str(get_prop(elem2, ".//pravniForma/nazev"))
insert_instructions = [(pravni_forma,"pravni_formy", "pravni_forma", "pravni_formy_relation")]
for elem in insert_instructions:
insert_into_ancillary_table(c, elem, pravni_forma)
ancillary_table_key = get_anciallary_table_key(c, elem, pravni_forma)
insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum)
except:
pass
def find_statutar(c, elem2, primary_sql_key):
try:
zapis_datum = str(get_prop(elem2, "zapisDatum"))
vymaz_datum = str(get_prop(elem2, "vymazDatum"))
oznaceni_statutar_organu = str(get_prop(elem2, ".//hlavicka"))
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 = get_relationship_table_key(c, primary_sql_key, ancillary_table_key)
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, elem, relationship_table_key)
elif udajTyp_name == "ZPUSOB_JEDNANI":
find_zpusob_jednani(c, elem, relationship_table_key)
elif udajTyp_name == "STATUTARNI_ORGAN_CLEN":
find_clen_statut_org(c, elem, relationship_table_key)
else:
pass
except Exception as f:
print(f)
def find_UBO(c, elem2, primary_sql_key):
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)
UBO_id = find_fyzicka_osoba(c, elem, 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, elem2, primary_sql_key):
try:
my_iter = elem2.findall("podudaje/Udaj")
for elem in my_iter:
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"))
if spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "OSOBA":
text_spolecnik = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))
nazev = str(get_prop(elem, "osoba/nazev"))
if nazev == "0":
adresa_id = find_sidlo(c, elem)
spolecnik_fo_id = find_fyzicka_osoba(c, elem, adresa_id)
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]
else:
spol_ico = str(get_prop(elem, "osoba/ico"))
regCislo = str(get_prop(elem, "osoba/regCislo"))
adresa_id = find_sidlo(c, elem)
spolecnik_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
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)
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)
spolecnik_fo_id = find_fyzicka_osoba(c, podil_elem, 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)
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, predmet_podnikani_elem, primary_sql_key):
try:
my_iter = predmet_podnikani_elem.findall("podudaje")
for elem in my_iter:
my_iter2 = elem.iter("Udaj")
for elem2 in my_iter2:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
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")).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)
except:
pass
def find_predmet_cinnosti(c, predmet_cinnosti_elem, primary_sql_key):
try:
my_iter = predmet_cinnosti_elem.findall("podudaje")
for elem in my_iter:
my_iter2 = elem.iter("Udaj")
for elem2 in my_iter2:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
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")).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)
except:
pass
def find_ucel(c, ucel_elem, primary_sql_key):
try:
my_iter = ucel_elem.findall("podudaje")
for elem in my_iter:
my_iter2 = elem.iter("Udaj")
for elem2 in my_iter2:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
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")).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)
except Exception as f:
print(f)
def find_zakladni_kapital(c, elem2, primary_sql_key):
try:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
vklad_typ = str(get_prop(elem2, ".//hodnotaUdaje/vklad/typ"))
vklad_hodnota = str(get_prop(elem2, ".//hodnotaUdaje/vklad/textValue"))
splaceni_typ = str(get_prop(elem2, ".//hodnotaUdaje/splaceni/typ"))
splaceni_hodnota = str(get_prop(elem2, ".//hodnotaUdaje/splaceni/textValue"))
c.execute("INSERT INTO zakladni_kapital (company_id, zapis_datum, vymaz_datum, vklad_typ, vklad_hodnota, splaceni_typ, splaceni_hodnota) VALUES(?, ?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, vklad_typ, vklad_hodnota, splaceni_typ, splaceni_hodnota,))
except:
pass
def find_ostatni_skutecnosti(c, ostatni_skutecnosti_elem, primary_sql_key):
try:
my_iter = ostatni_skutecnosti_elem.findall("podudaje")
for elem in my_iter:
my_iter2 = elem.iter("Udaj")
for elem2 in my_iter2:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
inserted_figure = str(get_prop(elem2, ".//hodnotaText"))
c.execute("INSERT INTO ostatni_skutecnosti (company_id, zapis_datum, vymaz_datum, ostatni_skutecnost) VALUES(?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, inserted_figure,))
except:
pass
def find_akcie(c, ostatni_akcie_elem, primary_sql_key):
try:
my_iter = ostatni_akcie_elem.findall("podudaje")
for elem in my_iter:
my_iter2 = elem.iter("Udaj")
for elem2 in my_iter2:
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
akcie_podoba = str(get_prop(elem2, ".//hodnotaUdaje/podoba"))
akcie_typ = str(get_prop(elem2, ".//hodnotaUdaje/typ"))
akcie_pocet = str(get_prop(elem2, ".//hodnotaUdaje/pocet"))
akcie_hodnota_typ = str(get_prop(elem2, ".//hodnotaUdaje/hodnota/typ"))
akcie_hodnota_value = str(get_prop(elem2, ".//hodnotaUdaje/hodnota/textValue"))
akcie_text = str(get_prop(elem2, ".//hodnotaUdaje/text"))
c.execute("INSERT INTO akcie (company_id, zapis_datum, vymaz_datum, akcie_podoba, akcie_typ, akcie_pocet, akcie_hodnota_typ, akcie_hodnota_value, akcie_text) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, akcie_podoba, akcie_typ, akcie_pocet, akcie_hodnota_typ, akcie_hodnota_value,akcie_text,))
except:
pass
def find_dozorci_rada(c, elem2, primary_sql_key):
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, elem, relationship_table_key)
elif udajTyp_name == "DOZORCI_RADA_CLEN":
find_clen_dr(c, elem, relationship_table_key)
except Exception as f:
print(f)
def find_prokura(c, elem2, primary_sql_key):
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"))
adresa_id = find_sidlo(c, elem)
prokurista_fo_id = find_fyzicka_osoba(c, elem, adresa_id)
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, elem2, primary_sql_key):
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"))
adresa_id = find_sidlo(c, elem)
akcionar_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
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":
adresa_id = find_sidlo(c, elem)
akcionar_fo_id = find_fyzicka_osoba(c, elem, adresa_id)
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_insolvency(c, insolvency_elem, primary_sql_key):
try:
my_iter = insolvency_elem.findall("podudaje")
for elem in my_iter:
my_iter2 = elem.iter("Udaj")
for elem2 in my_iter2:
insolvency_text = str(get_prop(elem2, ".//text"))
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
if insolvency_text != "0":
try:
c.execute("INSERT INTO insolvency_events (company_id, zapis_datum, vymaz_datum, insolvency_event) VALUES(?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, insolvency_text,))
except:
pass
except:
pass
def find_konkurz(c, konkurz_elem, primary_sql_key):
try:
my_iter = konkurz_elem.findall("podudaje")
for elem in my_iter:
my_iter2 = elem.iter("Udaj")
for elem2 in my_iter2:
konkurz_text = str(get_prop(elem2, ".//text"))
zapis_datum = str(get_prop(elem2, ".//zapisDatum"))
vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
if konkurz_text != "0":
try:
c.execute("INSERT INTO konkurz_events (company_id, zapis_datum, vymaz_datum, konkurz_event) VALUES(?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, konkurz_text,))
except:
pass
except:
pass
def find_sidlo(c, elem):
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
except Exception as e:
print(e)
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_into_ancillary_table(c, elem, inserted_figure):
try:
c.execute("INSERT INTO " + elem[1] + "(" + elem[2] + ") VALUES(?)", (inserted_figure,))
except:
pass
def get_anciallary_table_key(c, elem, inserted_figure):
try:
anciallary_table_key = c.execute("SELECT id FROM " + elem[1] + " WHERE " + elem[2] + " = (?)", (inserted_figure,))
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 find_pocet_clenu(c, elem, relationship_table_key):
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)
def find_zpusob_jednani(c, elem, relationship_table_key):
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)
except Exception as f:
print(f)
def find_clen_statut_org(c, elem, relationship_table_key):
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":
adresa_id = find_sidlo(c, elem)
osoba_id = find_fyzicka_osoba(c, elem, adresa_id)
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,))
if typ_osoby == "AngazmaPravnicke":
spol_ico = str(get_prop(elem, "osoba/ico"))
regCislo = str(get_prop(elem, "osoba/regCislo"))
adresa_id = find_sidlo(c, elem)
prav_osoba_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
c.execute("INSERT into statutarni_organ_clen_relation (statutarni_organ_id, prav_osoba_id, adresa_id, zapis_datum, vymaz_datum, funkce_od, funkce_do, clenstvi_od, clenstvi_do, funkce) VALUES (?,?,?,?,?,?,?,?,?,?)", (relationship_table_key, prav_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, elem, adresa_id):
try:
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"))
insert_fyzicka_osoba(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni, adresa_id)
osoba_id = find_osoba_id(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni, adresa_id)
return osoba_id
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,))
except:
pass
def find_osoba_id(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni, adresa_id):
try:
anciallary_table_key = c.execute("SELECT id FROM fyzicke_osoby WHERE titul_pred = (?) and jmeno = (?) and prijmeni = (?) and titul_za = (?) and datum_naroz = (?) and adresa_id = (?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,adresa_id,))
anciallary_table_key = c.fetchone()[0]
return anciallary_table_key
except Exception as f:
print(f)
def find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id):
try:
nazev = str(get_prop(elem, "osoba/nazev"))
insert_pravnicka_osoba(c, spol_ico, regCislo, nazev, adresa_id)
osoba_id = find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev, adresa_id)
return osoba_id
except Exception as f:
print(f)
def find_pocet_clenu_dr(c, elem, relationship_table_key):
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_clen_dr(c, elem, relationship_table_key):
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":
adresa_id = find_sidlo(c, elem)
osoba_id = find_fyzicka_osoba(c, elem, adresa_id)
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,))
elif typ_osoby == "AngazmaPravnicke":
spol_ico = str(get_prop(elem, "osoba/ico"))
regCislo = str(get_prop(elem, "osoba/regCislo"))
adresa_id = find_sidlo(c, elem)
pravnicka_osoba_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
c.execute("INSERT into dr_organ_clen_relation (dozorci_rada_id, pravnicka_osoba_id, adresa_id, zapis_datum, vymaz_datum, funkce_od, funkce_do, clenstvi_od, clenstvi_do, funkce) VALUES (?,?,?,?,?,?,?,?,?,?)", (relationship_table_key, pravnicka_osoba_id, adresa_id, zapis_datum, vymaz_datum, funkceOd, funkceDo, clenstviOd, clenstviDo, funkce_statutar_organu,))
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")
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 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"))
insert_druh_podilu(c, 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, druhPodilu):
try:
c.execute("INSERT INTO druhy_podilu (druh_podilu) VALUES (?)", (druhPodilu,))
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_id(c, spol_ico, regCislo, nazev, adresa_id):
try:
anciallary_table_key = c.execute("SELECT id FROM pravnicke_osoby WHERE ico = (?) and reg_cislo = (?) and nazev = (?) and adresa_id = (?)", (spol_ico, regCislo, nazev, adresa_id))
anciallary_table_key = c.fetchone()[0]
return anciallary_table_key
except Exception as f:
print(f)
def insert_pravnicka_osoba(c, spol_ico, regCislo, nazev, adresa_id):
try:
c.execute("INSERT into pravnicke_osoby (ico, reg_cislo, nazev, adresa_id) VALUES (?,?,?, ?)", (spol_ico, regCislo, nazev, adresa_id,))
except:
pass
def get_prop(element, prop):
try:
return element.find(prop).text
except:
return "0"
