expand search options for natural persons

1 files changed, 12 insertions, 15 deletions

diff --git a/update_db.py b/update_db.py
index b85eb52..e59831f 100644
--- a/update_db.py
+++ b/update_db.py
@@ -209,9 +209,8 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
                 # 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 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 = find_sidlo(c, elem, primary_sql_key)
+                    spolecnik_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element, 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]
@@ -220,7 +219,6 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
                     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 = find_sidlo(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()")
@@ -350,8 +348,8 @@ def find_prokura(c, ICO, elem2, conn, primary_sql_key, element):
                 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_sidlo(c, elem, primary_sql_key)
+                prokurista_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element, 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"))
@@ -376,8 +374,8 @@ def find_sole_shareholder(c, ICO, elem2, conn, primary_sql_key, element):
                 adresa_id = find_sidlo(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_sidlo(c, elem, primary_sql_key)
+                akcionar_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element, 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)
@@ -498,8 +496,8 @@ def find_clen_statut_org(c, ICO, elem, conn, relationship_table_key, element):
         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_sidlo(c, elem, relationship_table_key)
+            osoba_id = find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, 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"))
@@ -514,28 +512,28 @@ def insert_individual_relations_v2(c, ICO, conn, primary_sql_key, zapis_datum, v
     insert_into_ancillary_table(c, elem, inserted_figure)
     return 0
 
-def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element):
+def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, adresa_id):
     try:
         jmeno = str(get_prop(elem, "osoba/jmeno"))
         prijmeni = str(get_prop(elem, "osoba/prijmeni"))
         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)
+        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 insert_fyzicka_osoba(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni):
+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) VALUES (?,?,?,?,?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,))
+        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):
+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 = (?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,))
+        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:
@@ -570,9 +568,8 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element):
         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 = find_sidlo(c, elem, relationship_table_key)
+            osoba_id = find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, 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"))