creating more indices

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