function to display members of bodies

5 files changed, 165 insertions, 30 deletions

diff --git a/.gitignore b/.gitignore
index 2c35535..c694f56 100644
--- a/.gitignore
+++ b/.gitignore
@@ -391,3 +391,6 @@ data/zajzdrpo-full-plzen-2021.xml
 data/zajzdrpo-full-praha-2021.xml
 data/zajzdrpo-full-usti_nad_labem-2021.xml
 as-full-ostrava-2021.xml
+sro-full-ceske_budejovice-2021.xml
+sro-full-ostrava-2021.xml
+sro-full-praha-2021.xml
diff --git a/justice_main.py b/justice_main.py
index 318f5ae..8adb991 100644
--- a/justice_main.py
+++ b/justice_main.py
@@ -6,6 +6,9 @@ import gzip
 import send2trash

 import os

 from datetime import datetime

+import cProfile

+import re

+from sqlalchemy import text, exc, insert, engine

 

 # The function opens a file and parses the extracted data into the database

 def parse_to_DB(file):

@@ -40,6 +43,7 @@ def purge_DB():
         c.execute("DELETE FROM adresy")

         c.execute("DELETE FROM akcie")

         c.execute("DELETE FROM companies")

+        c.execute("DELETE FROM fyzicke_osoby")

         c.execute("DELETE FROM insolvency_events")

         c.execute("DELETE FROM konkurz_events")

         c.execute("DELETE FROM nazvy")

@@ -57,8 +61,9 @@ def purge_DB():
         c.execute("DELETE FROM sidla")

         c.execute("DELETE FROM sidlo_relation")

         c.execute("DELETE FROM sqlite_sequence")

-        c.execute("DELETE FROM statutarni_organy")

+        c.execute("DELETE FROM statutarni_organ_clen_relation")

         c.execute("DELETE FROM statutarni_organ_relation")

+        c.execute("DELETE FROM statutarni_organy")

         c.execute("DELETE FROM ulice")

         c.execute("DELETE FROM ulice_relation")

         c.execute("DELETE FROM zakladni_kapital")

@@ -78,30 +83,30 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key):
         for elem in my_iter:

             my_iter2 = elem.iter("Udaj")

             for elem2 in my_iter2:

-                # print(ICO, str(get_prop(elem2, ".//udajTyp/kod")))

-                if str(get_prop(elem2, ".//udajTyp/kod")) == "SIDLO":

+                udajTyp_name = str(get_prop(elem2, ".//udajTyp/kod"))

+                if udajTyp_name == "SIDLO":

                     find_registered_office(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "NAZEV":

+                elif udajTyp_name == "NAZEV":

                     find_nazev(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "STATUTARNI_ORGAN":

+                elif udajTyp_name == "STATUTARNI_ORGAN":

                     find_statutar(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "INSOLVENCE_SEKCE":

+                elif udajTyp_name == "INSOLVENCE_SEKCE":

                     find_active_insolvency(c, ICO, elem2, conn, primary_sql_key)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "KONKURS_SEKCE":

+                elif udajTyp_name == "KONKURS_SEKCE":

                     find_active_konkurz(c, ICO, elem2, conn, primary_sql_key)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "PREDMET_PODNIKANI_SEKCE":

+                elif udajTyp_name == "PREDMET_PODNIKANI_SEKCE":

                     find_predmet_podnikani(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "PREDMET_CINNOSTI_SEKCE":

+                elif udajTyp_name == "PREDMET_CINNOSTI_SEKCE":

                     find_predmet_cinnosti(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "ZAKLADNI_KAPITAL":

+                elif udajTyp_name == "ZAKLADNI_KAPITAL":

                     find_zakladni_kapital(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "OST_SKUTECNOSTI_SEKCE":

+                elif udajTyp_name == "OST_SKUTECNOSTI_SEKCE":

                     find_ostatni_skutecnosti(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "AKCIE_SEKCE":

+                elif udajTyp_name == "AKCIE_SEKCE":

                     find_akcie(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "SPIS_ZN":

+                elif udajTyp_name == "SPIS_ZN":

                     find_sp_zn(c, ICO, elem2, conn, primary_sql_key, element)

-                elif str(get_prop(elem2, ".//udajTyp/kod")) == "PRAVNI_FORMA":

+                elif udajTyp_name == "PRAVNI_FORMA":

                     find_pravni_forma(c, ICO, elem2, conn, primary_sql_key, element)

     except:

         pass

@@ -132,21 +137,123 @@ def find_statutar(c, ICO, elem2, conn, primary_sql_key, element):
             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 = c.execute("SELECT id FROM statutarni_organ_relation WHERE company_id = (?) and statutarni_organ_id = (?)", (primary_sql_key,ancillary_table_key,))

-            relationship_table_key = c.fetchone()[0]

-        my_iter = elem2.findall("podudaje/Udaj")

+            relationship_table_key = get_relationship_table_key(c, primary_sql_key, ancillary_table_key)

+            # relationship_table_key = c.execute("SELECT id FROM statutarni_organ_relation WHERE company_id = (?) and statutarni_organ_id = (?)", (primary_sql_key,ancillary_table_key,))

+            # relationship_table_key = c.fetchone()[0]

+        my_iter = elem2.findall("podudaje/Udaj") 

         for elem in my_iter:

-            if (str(get_prop(elem, "udajTyp/kod"))) == "POCET_CLENU":

+            udajTyp_name = str(get_prop(elem, "udajTyp/kod"))

+            if udajTyp_name == "POCET_CLENU":

                 find_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element)

-            elif (str(get_prop(elem, "udajTyp/kod"))) == "ZPUSOB_JEDNANI":

+            elif udajTyp_name == "ZPUSOB_JEDNANI":

                 find_zpusob_jednani(c, ICO, elem, conn, relationship_table_key, element)

-            elif (str(get_prop(elem, "udajTyp/kod"))) == "STATUTARNI_ORGAN_CLEN":

-                pass

+            elif udajTyp_name == "STATUTARNI_ORGAN_CLEN":

+                find_clen_statut_org(c, ICO, elem, conn, relationship_table_key, element)

             else:

                 print(str(get_prop(elem, "udajTyp/kod")))

     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)

+        else:

+            osoba_id = "1111111"

+        adresa_id = find_and_store_address(c, elem)

+        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_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_statutar(c, ICO, elem2, conn, primary_sql_key, element):

+#     try:

+#         zapis_datum = str(get_prop(elem2, "zapisDatum"))

+#         vymaz_datum = str(get_prop(elem2, "vymazDatum"))

+#         oznaceni_statutar_organu = str(get_prop(elem2, ".//hlavicka"))

+#         # print(ICO, zapis_datum, vymaz_datum, oznaceni_statutar_organu)

+#         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 = c.execute("SELECT id FROM statutarni_organ_relation WHERE company_id = (?) and statutarni_organ_id = (?)", (primary_sql_key,ancillary_table_key,))

+#             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":

+#                 find_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element)

+#             elif udajTyp_name == "ZPUSOB_JEDNANI":

+#                 find_zpusob_jednani(c, ICO, elem, conn, relationship_table_key, element)

+#             elif udajTyp_name == "STATUTARNI_ORGAN_CLEN":

+#                 pass

+#             else:

+#                 print(str(get_prop(elem, "udajTyp/kod")))

+#     except Exception as f:

+#         print(f)

+

 def find_pocet_clenu(c, ICO, elem, conn, relationship_table_key, element):

     try:

         zapis_datum = str(get_prop(elem, "zapisDatum"))

@@ -390,7 +497,7 @@ 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,))

@@ -781,6 +888,7 @@ purge_DB()
 # general_update("db_update")

 

 parse_to_DB("as-full-ostrava-2021.xml")

+# parse_to_DB("sro-full-ceske_budejovice-2021.xml")

 

 # parse_to_DB("sro-actual-praha-2020.xml")

 

@@ -790,4 +898,4 @@ parse_to_DB("as-full-ostrava-2021.xml")
 

 # do_both()

 

-# cProfile.run('do_both()')
\ No newline at end of file
+# cProfile.run('general_update("db_update")')
\ No newline at end of file
diff --git a/main.py b/main.py
index a23fa3f..6a2af7a 100644
--- a/main.py
+++ b/main.py
@@ -4,7 +4,7 @@ from db_setup import init_db, db_session
 from forms import JusticeSearchForm, CompanyForm
 from flask import flash, render_template, request, redirect
 # from models import Company, Soud
-from models import Company, Obce, Ulice, Insolvency_Events, Predmet_Podnikani, Predmety_Podnikani_Association, Predmet_Cinnosti, Predmety_Cinnosti_Association, Zakladni_Kapital, Akcie, Nazvy, Sidlo, Sidlo_Association, Pravni_Forma_Association_v2, Pravni_Formy, Statutarni_Organ_Association, Statutarni_Organy, Pocty_Clenu_Organu, Zpusob_Jednani_Association, Zpusob_Jednani
+from models import Company, Obce, Ulice, Insolvency_Events, Predmet_Podnikani, Predmety_Podnikani_Association, Predmet_Cinnosti, Predmety_Cinnosti_Association, Zakladni_Kapital, Akcie, Nazvy, Sidlo, Sidlo_Association, Pravni_Forma_Association_v2, Pravni_Formy, Statutarni_Organ_Association, Statutarni_Organy, Pocty_Clenu_Organu, Zpusob_Jednani_Association, Zpusob_Jednani, Statutarni_Organ_Clen_Association, Fyzicka_Osoba
 from tables import Results
 
 init_db()
diff --git a/models.py b/models.py
index 5ffd568..d891d0e 100644
--- a/models.py
+++ b/models.py
@@ -165,8 +165,6 @@ class Pravni_Forma_Association_v2(db.Model):
     pravni_forma_text = db.relationship("Pravni_Formy", back_populates="company_pravni_forma")
     company = db.relationship("Company", back_populates="pravni_forma_text")
 
-
-
 class Statutarni_Organ_Association(db.Model):
     __tablename__ = 'statutarni_organ_relation'
     id = db.Column(db.Integer)
@@ -178,7 +176,23 @@ class Statutarni_Organ_Association(db.Model):
     company = db.relationship("Company", back_populates="statutarni_organ_text")
     pocet_clenu = db.relationship("Pocty_Clenu_Organu", backref="statutarni_organ_relation")
     zpusoby_jednani = db.relationship("Zpusob_Jednani_Association", back_populates="statutarni_organ")
+    clenove = db.relationship("Statutarni_Organ_Clen_Association")
 
+class Statutarni_Organ_Clen_Association(db.Model):
+    __tablename__ = 'statutarni_organ_clen_relation'
+    id = db.Column(db.Integer, primary_key=True)
+    statutarni_organ_id = db.Column(db.Integer, db.ForeignKey('statutarni_organ_relation.id'), nullable=False)
+    osoba_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id'), nullable=False)
+    adresa_id = db.Column(db.Integer, db.ForeignKey('adresy.id'), nullable=False)
+    zapis_datum = db.Column(MyType)
+    vymaz_datum = db.Column(MyType)
+    funkce_od = db.Column(MyType)
+    funkce_do = db.Column(MyType)
+    clenstvi_od = db.Column(MyType)
+    clenstvi_do = db.Column(MyType)
+    funkce = db.Column(db.String)
+    adresa = db.relationship("Sidlo")
+    jmeno = db.relationship("Fyzicka_Osoba")
 
 class Zpusob_Jednani_Association(db.Model):
     __tablename__ = 'zpusoby_jednani_relation'
@@ -217,7 +231,6 @@ class Company(db.Model):
     pravni_forma_text = db.relationship("Pravni_Forma_Association_v2", back_populates="company")
     statutarni_organ_text = db.relationship("Statutarni_Organ_Association", back_populates="company")
 
-
 class Obce(db.Model):
     __tablename__ = "obce"
     id = db.Column(db.Integer, primary_key=True)
@@ -346,4 +359,13 @@ class Zpusob_Jednani(db.Model):
     __tablename__ = "zpusoby_jednani"
     id = db.Column(db.Integer, primary_key=True)
     zpusob_jednani_text = db.Column(db.String)
-    zpusob_jednani_rship = db.relationship("Zpusob_Jednani_Association", back_populates="zpusob_jednani")
\ No newline at end of file
+    zpusob_jednani_rship = db.relationship("Zpusob_Jednani_Association", back_populates="zpusob_jednani")
+
+class Fyzicka_Osoba(db.Model):
+    __tablename__ = "fyzicke_osoby"
+    id = db.Column(db.Integer, primary_key=True)
+    titul_pred = db.Column(db.String)
+    jmeno = db.Column(db.String)
+    prijmeni = db.Column(db.String)
+    titul_za = db.Column(db.String)
+    datum_naroz = db.Column(db.String)
\ No newline at end of file
diff --git a/templates/extract.html b/templates/extract.html
index 71fd402..e8f97cc 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -65,6 +65,8 @@
 {% endif %}
 {% endfor %}
 
+<p>Test: {{ row.statutarni_organ_text[1].clenove[0].funkce }}</p>
+
 <p><a href="/">Zpět na vyhledání.</a></p>
 
 <table class= "table" style="width: 100%">
@@ -462,7 +464,7 @@
             {% set underlne_style_open = "" %}
             {% set underlne_style_close = "" %}
         {% endif %}
-        <td>{{ underlne_style_open|safe }} {{ row.statutarni_organ_text[i].pocet_clenu[0].zpusob_jednani.zpusob_jednani_text }} {{ underlne_style_close|safe }}</td>
+        <td>{{ underlne_style_open|safe }} {{ row.statutarni_organ_text[i].zpusoby_jednani[0].zpusob_jednani.zpusob_jednani_text }} {{ underlne_style_close|safe }}</td>
         <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.statutarni_organ_text[i].zpusoby_jednani[0].zapis_datum }} {% if row.statutarni_organ_text[i].zpusoby_jednani[0].vymaz_datum != 0 %} <br>   Vymazáno: {{ row.statutarni_organ_text[i].zpusoby_jednani[0].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td>
     </tr>
     {% for j in range (1, row.statutarni_organ_text[i].zpusoby_jednani|length) %}
@@ -475,7 +477,7 @@
             {% set underlne_style_open = "" %}
             {% set underlne_style_close = "" %}
         {% endif %}
-        <td>{{ underlne_style_open|safe }} {{ row.statutarni_organ_text[i].pocet_clenu[j].zpusob_jednani.zpusob_jednani_text }} {{ underlne_style_close|safe }}</td>
+        <td>{{ underlne_style_open|safe }} {{ row.statutarni_organ_text[i].zpusoby_jednani[j].zpusob_jednani.zpusob_jednani_text }} {{ underlne_style_close|safe }}</td>
         <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.statutarni_organ_text[i].zpusoby_jednani[j].zapis_datum }} {% if row.statutarni_organ_text[i].zpusoby_jednani[j].vymaz_datum != 0 %} <br>   Vymazáno: {{ row.statutarni_organ_text[i].zpusoby_jednani[j].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td>
     </tr>    
     {% endfor %}