feature to display shareholders

4 files changed, 175 insertions, 6 deletions

diff --git a/justice_main.py b/justice_main.py
index 6f4b6c1..fad022f 100644
--- a/justice_main.py
+++ b/justice_main.py
@@ -45,6 +45,7 @@ def purge_DB():
         c.execute("DELETE FROM companies")

         c.execute("DELETE FROM dozorci_rada_relation")

         c.execute("DELETE FROM dr_organ_clen_relation")

+        c.execute("DELETE FROM druhy_podilu")

         c.execute("DELETE FROM fyzicke_osoby")

         c.execute("DELETE FROM insolvency_events")

         c.execute("DELETE FROM konkurz_events")

@@ -55,6 +56,7 @@ def purge_DB():
         c.execute("DELETE FROM ostatni_skutecnosti")

         c.execute("DELETE FROM pocty_clenu_DR")

         c.execute("DELETE FROM pocty_clenu_organu")

+        c.execute("DELETE FROM podily")

         c.execute("DELETE FROM pravni_formy")

         c.execute("DELETE FROM pravni_formy_relation")

         c.execute("DELETE FROM predmety_cinnosti")

@@ -63,6 +65,7 @@ def purge_DB():
         c.execute("DELETE FROM predmety_podnikani_relation")

         c.execute("DELETE FROM sidla")

         c.execute("DELETE FROM sidlo_relation")

+        c.execute("DELETE FROM spolecnici")

         c.execute("DELETE FROM sqlite_sequence")

         c.execute("DELETE FROM statutarni_organ_clen_relation")

         c.execute("DELETE FROM statutarni_organ_relation")

@@ -95,6 +98,8 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key):
                     find_nazev(c, ICO, elem2, conn, primary_sql_key, element)

                 elif udajTyp_name == "STATUTARNI_ORGAN":

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

+                elif udajTyp_name == "SPOLECNIK":

+                    find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element)

                 elif udajTyp_name == "INSOLVENCE_SEKCE":

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

                 elif udajTyp_name == "KONKURS_SEKCE":

@@ -118,6 +123,113 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key):
     except:

         pass

 

+

+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"))

+        # if vymaz_datum != "0":

+        #     print(ICO, zapis_datum, vymaz_datum)

+        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_oznaceni = str(get_prop(elem, "hlavicka"))

+            if spolecnik_type == "SPOLECNIK_OSOBA" and spolecnik_oznaceni == "Společník":

+                # TODO alternativy pro None, Spolecny podil a Uvolneny podil

+                spol_ico = str(get_prop(elem, "osoba/ico"))

+                regCislo = str(get_prop(elem, "osoba/regCislo"))

+                text_spolecnik = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))

+                if spol_ico == "0" and regCislo == "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)

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

+                    spolecnik_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo)

+                    adresa_id = find_and_store_address(c, elem)

+                    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)

+

+                    # nazev = str(get_prop(elem, "osoba/nazev"))

+                    # addr = str(adresa(get_SIDLO_v3(elem)))

+                    # print(ICO, nazev, spol_ico, addr)

+                

+    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)

+            # druhPodilu = str(get_prop(podil_elem, "hodnotaUdaje/druhPodilu"))

+            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,))

+            # print(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):

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

+        c.execute("INSERT INTO druhy_podilu (druh_podilu) VALUES (?)", (druhPodilu,))

+    except:

+        pass

+

+def find_druh_podilu_id(c, druhPodilu):

+    try:

+        druh_podilu_id = c.execute("SELECT id FROM druhy_podilu WHERE druh_podilu = (?)", (druhPodilu,))

+        druh_podilu_id = c.fetchone()[0]

+        return druh_podilu_id

+    except Exception as f:

+        print(f) 

+

+def find_pravnicka_osoba(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):

+    try:

+        c.execute("INSERT into pravnicke_osoby (ico, reg_cislo, nazev) VALUES (?,?,?)", (spol_ico, regCislo, nazev,))

+    except:

+        pass

+

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

     try:

         zapis_datum = str(get_prop(elem2, ".//zapisDatum"))

@@ -157,7 +269,8 @@ def find_statutar(c, ICO, elem2, conn, primary_sql_key, element):
             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")))

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

+                pass

     except Exception as f:

         print(f)

 

@@ -943,7 +1056,7 @@ def delete_archive(file):
 purge_DB()

 

 # parse_to_DB("data/as-full-ceske_budejovice-2021.xml")

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

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

 

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

 

@@ -951,6 +1064,6 @@ def do_both():
     general_update("down")

     general_update("db_update")

 

-# do_both()

+do_both()

 

-cProfile.run('general_update("db_update")')
\ 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 9660a47..a8ea9a1 100644
--- a/main.py
+++ b/main.py
@@ -6,7 +6,7 @@ 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 
 from models import Zakladni_Kapital, Akcie, Nazvy, Sidlo, Sidlo_Association, Pravni_Forma_Association_v2, Pravni_Formy, Statutarni_Organ_Association, Statutarni_Organy, Pocty_Clenu_Organu
-from models import Zpusob_Jednani_Association, Zpusob_Jednani, Statutarni_Organ_Clen_Association, Fyzicka_Osoba
+from models import Zpusob_Jednani_Association, Zpusob_Jednani, Statutarni_Organ_Clen_Association, Fyzicka_Osoba, Spolecnici_Association, Podily_Association, Druhy_Podilu, Pravnicka_Osoba
 from tables import Results
 
 init_db()
diff --git a/models.py b/models.py
index 1447bd1..6f6123d 100644
--- a/models.py
+++ b/models.py
@@ -226,6 +226,35 @@ class Dozorci_Rada_Clen_Association(db.Model):
     adresa = db.relationship("Sidlo")
     jmeno = db.relationship("Fyzicka_Osoba")
 
+class Spolecnici_Association(db.Model):
+    __tablename__ = "spolecnici"
+    id = db.Column(db.Integer, primary_key=True)
+    company_id = db.Column(db.Integer, db.ForeignKey('companies.id'), nullable=False)
+    spolecnik_fo_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id'), nullable=False)
+    spolecnik_po_id = db.Column(db.Integer, db.ForeignKey('pravnicke_osoby.id'), nullable=False)
+    zapis_datum = db.Column(MyType)
+    vymaz_datum = db.Column(MyType)
+    adresa_id = db.Column(db.Integer, db.ForeignKey('adresy.id'), nullable=False)
+    text_spolecnik = db.Column(db.String)
+    adresa = db.relationship("Sidlo")
+    jmeno = db.relationship("Fyzicka_Osoba")
+    oznaceni_po = db.relationship("Pravnicka_Osoba")
+    podily = db.relationship("Podily_Association")
+
+class Podily_Association(db.Model):
+    __tablename__ = "podily"
+    id = db.Column(db.Integer, primary_key=True)
+    spolecnik_id = db.Column(db.Integer, db.ForeignKey('spolecnici.id'), nullable=False)
+    zapis_datum = db.Column(MyType)
+    vymaz_datum = db.Column(MyType)
+    druh_podilu_id = db.Column(db.Integer, db.ForeignKey('druhy_podilu.id'), nullable=False)
+    vklad_typ = db.Column(db.String)
+    vklad_text = db.Column(db.String)
+    souhrn_typ = db.Column(db.String)
+    souhrn_text = db.Column(db.String)
+    splaceni_typ = db.Column(db.String)
+    splaceni_text = db.Column(db.String)
+
 class Zpusob_Jednani_Association(db.Model):
     __tablename__ = 'zpusoby_jednani_relation'
     id = db.Column(db.Integer, primary_key=True)
@@ -262,6 +291,7 @@ 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")
     dozorci_rada_text = db.relationship("Dozorci_Rada_Association")
+    spolecnici = db.relationship("Spolecnici_Association")
 
 class Obce(db.Model):
     __tablename__ = "obce"
@@ -401,6 +431,11 @@ class Zpusob_Jednani(db.Model):
     zpusob_jednani_text = db.Column(db.String)
     zpusob_jednani_rship = db.relationship("Zpusob_Jednani_Association", back_populates="zpusob_jednani")
 
+class Druhy_Podilu(db.Model):
+    __tablename__ = "druhy_podilu"
+    id = db.Column(db.Integer, primary_key=True)
+    druh_podilu = db.Column(db.String)
+
 class Fyzicka_Osoba(db.Model):
     __tablename__ = "fyzicke_osoby"
     id = db.Column(db.Integer, primary_key=True)
@@ -408,4 +443,11 @@ class Fyzicka_Osoba(db.Model):
     jmeno = db.Column(db.String)
     prijmeni = db.Column(db.String)
     titul_za = db.Column(db.String)
-    datum_naroz = db.Column(MyType)
\ No newline at end of file
+    datum_naroz = db.Column(MyType)
+
+class Pravnicka_Osoba(db.Model):
+    __tablename__ = "pravnicke_osoby"
+    id = db.Column(db.Integer, primary_key=True)
+    ico = db.Column(db.String)
+    reg_cislo = db.Column(db.String)
+    nazev = db.Column(db.String)
\ No newline at end of file
diff --git a/templates/extract.html b/templates/extract.html
index b404a74..579051c 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -758,6 +758,20 @@
 
 <!--SUPERVISORY BOARD END-->
 
+<!-- BEGINNING OF SPOLECNIK -->
+    {% if row.spolecnici|length > 0 %}
+    {% for i in range (row.spolecnici|length) %}
+    <tr>
+        <td>{% if i == 0 %}Společníci:{% endif %}</td>
+        <td>{% if row.spolecnici[i].spolecnik_po_id == None %} {{ row.spolecnici[i].jmeno.jmeno }} {{ row.spolecnici[i].jmeno.prijmeni }}{% if row.spolecnici[i].jmeno.datum_naroz != "" %}, nar. {{ row.spolecnici[i].jmeno.datum_naroz }}{% endif %} {% endif %}   
+            {% if row.spolecnici[i].spolecnik_fo_id == None %} {{ row.spolecnici[i].oznaceni_po.nazev }}{% if row.spolecnici[i].oznaceni_po.reg_cislo == 0 %}, IČ {{ row.spolecnici[i].oznaceni_po.ico }}{% endif %}{% if row.spolecnici[i].oznaceni_po.ico == 0 %}, reg č. {{ row.spolecnici[i].oznaceni_po.reg_cislo }}{% endif %} {% endif %} 
+            <br>{{row.spolecnici[i].adresa.adresa_text}}</td>
+        <td>Zapsáno: {{ row.spolecnici[i].zapis_datum }} {% if row.spolecnici[i].vymaz_datum != 0 %} <br>   Vymazáno: {{ row.spolecnici[i].vymaz_datum }} {% endif %} {{ row.spolecnici[i].podily}}</td>
+    </tr>
+    {% endfor %}
+    {% endif %}
+<!-- END OF SPOLECNIK -->
+
     {% if row.akcie|length > 0 %}
     <tr>
         <td>Akcie:</td>