display common ownership interests

7 files changed, 174 insertions, 18 deletions

diff --git a/db_creation.py b/db_creation.py
index 70a08df..c3836aa 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -195,6 +195,21 @@ def create_tables(conn):
 	PRIMARY KEY("id" AUTOINCREMENT)
 ); """
 
+    podilnici = """ CREATE TABLE "podilnici" (
+	"id"	INTEGER NOT NULL UNIQUE,
+	"podil_id"	INTEGER,
+	"podilnik_fo_id"	INTEGER,
+	"podilnik_po_id"	INTEGER,
+	"zapis_datum"	DATE,
+	"vymaz_datum"	DATE,
+	"adresa_id"	INTEGER,
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
+	PRIMARY KEY("id" AUTOINCREMENT),
+	FOREIGN KEY("podilnik_fo_id") REFERENCES "fyzicke_osoby"("id"),
+	FOREIGN KEY("podilnik_po_id") REFERENCES "pravnicke_osoby"("id"),	
+	FOREIGN KEY("podil_id") REFERENCES "spolecnici_spolecny_podil"("id")
+); """
+
     podily = """ CREATE TABLE "podily" (
 	"id"	INTEGER NOT NULL UNIQUE,
 	"spolecnik_id"	INTEGER,
@@ -465,7 +480,7 @@ def create_tables(conn):
 ); """
 
     list_of_tables = [companies, adresy_v2, akcie, dr_relation, dr_organ_clen_relation, druhy_podilu, fyzicke_osoby, insolvency_events, 
-    jediny_akcionar, konkurz_events, nazvy, ostatni_skutecnosti, pocty_clenu_DR, pocty_clenu_organu, podily, pravni_formy, 
+    jediny_akcionar, konkurz_events, nazvy, ostatni_skutecnosti, pocty_clenu_DR, pocty_clenu_organu, podily, podilnici, pravni_formy, 
     pravni_formy_relation, pravnicke_osoby, predmety_cinnosti, predmety_cinnosti_relation, prdmety_podnikani, predmety_podnikani_relation,
     prokura_common_texts, prokuriste, sidlo_relation, spolecnici, spolecnici_uvolneny_podil, spolecnici_spolecny_podil, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, ubo, 
     ucel, ucel_relation, zakladni_kapital, zapis_soudy, zpusoby_jednani, zpusoby_jednani_relation]
@@ -601,6 +616,26 @@ def create_indices(conn):
 	"spolecny_podil_id"
 ); """
 
+    podilnici1 = """ CREATE INDEX "index podilnici1" ON "podilnici" (
+	"id"
+); """
+
+    podilnici2 = """ CREATE INDEX "index podilnici2" ON "podilnici" (
+	"podil_id"
+); """
+
+    podilnici3 = """ CREATE INDEX "index podilnici3" ON "podilnici" (
+	"podilnik_fo_id"
+); """
+
+    podilnici4 = """ CREATE INDEX "index podilnici4" ON "podilnici" (
+	"podilnik_po_id"
+); """
+
+    podilnici5 = """ CREATE INDEX "index podilnici5" ON "podilnici" (
+	"adresa_id"
+); """
+
     pravni_formy = """ CREATE INDEX "index pravni_formy" ON "pravni_formy" (
 	"pravni_forma"
 ); """
@@ -847,7 +882,8 @@ def create_indices(conn):
     list_of_indices = [companies1, companies2, companies3, companies4, companies5, adresy1, adresy2, adresy3,
 	akcie, akcie2, akcionari1, akcionari2, akcionari3, dr_clen_relation1, dr_clen_relation2, dr_relation, dr_relation2, dr_relation_3, dr_relation_4, 
 	insolvency1, insolvency2, konkurz1, konkurz2, nazvy1, nazvy2, nazvy3, ostatni_skutecnosti, ostatni_skutecnosti2, 
-	pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, podily3, podily4, pravni_formy, pravni_formy_relation1, pravni_formy_relation2, 
+	pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, podily3, podily4, podilnici1, podilnici2, podilnici3, podilnici4, podilnici5,
+	pravni_formy, pravni_formy_relation1, pravni_formy_relation2, 
 	predmety_cinnosti_relation1, predmety_cinnosti_relation2, predmety_cinnosti_relation3, predmety_podnikani_relation1, predmety_podnikani_relation2, 
 	predmety_podnikani_relation3, predmety_cinnosti1, predmety_cinnosti2, predmety_podnikani1, predmety_podnikani2, prokuriste1, 
 	prokuriste2, prokuriste3, prokuriste4, sidlo_relation1, sidlo_relation_2, sidlo_relation_3, soudni_zapis1, soudni_zapis2, spolecnici1, 
diff --git a/justice_build.py b/justice_build.py
index 4b82660..9acf1e7 100644
--- a/justice_build.py
+++ b/justice_build.py
@@ -8,11 +8,10 @@ def main():
     DB_name = "justice.db"

     # backup_DB()

     create_DB(DB_name)

-    # valid_files = get_valid_filenames()

-    valid_files = ["sro-full-ceske_budejovice-2021"]

+    valid_files = get_valid_filenames()

     os.makedirs("data", exist_ok=True)

-    # for valid_file in valid_files:

-    #     download_data(valid_file)

+    for valid_file in valid_files:

+        download_data(valid_file)

     for valid_file in valid_files:

             modified_file_name = os.path.join(str(os.getcwd()), "data", valid_file + ".xml")

             update_DB(modified_file_name, DB_name)

diff --git a/main.py b/main.py
index 31dde42..a4d8293 100644
--- a/main.py
+++ b/main.py
@@ -5,7 +5,7 @@ from models import Company, Insolvency_Events, Konkurz_Events, Predmet_Podnikani
 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, Spolecnici_Association, Podily_Association, Druhy_Podilu, Pravnicka_Osoba
 from models import Prokurista_Association, Dozorci_Rada_Clen_Association, Jediny_Akcionar_Association, Prokura_Common_Text_Association, Soudni_Zapisy, Ucel, Ucel_Association
-from models import Adresy_v2, Uvolneny_Podil_Association, Spolecny_Podil_Association
+from models import Adresy_v2, Uvolneny_Podil_Association, Spolecny_Podil_Association, Podilnici_Association
 from tables import Results
 from sqlalchemy.sql import select
 from sqlalchemy.sql import text
diff --git a/models.py b/models.py
index a6d2eaf..400e5de 100644
--- a/models.py
+++ b/models.py
@@ -301,6 +301,21 @@ class Spolecnici_Association(db.Model):
     podily = db.relationship("Podily_Association")
     company = db.relationship("Company")
 
+class Podilnici_Association(db.Model):
+    __tablename__ = "podilnici"
+    id = db.Column(db.Integer, primary_key=True)
+    podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_spolecny_podil.id'))
+    podilnik_fo_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id'))
+    podilnik_po_id = db.Column(db.Integer, db.ForeignKey('pravnicke_osoby.id'))
+    zapis_datum = db.Column(MyType)
+    vymaz_datum = db.Column(MyType)
+    adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id'))
+    # text_podilnik = db.Column(db.String)
+    adresa = db.relationship("Adresy_v2")
+    jmeno = db.relationship("Fyzicka_Osoba")
+    oznaceni_po = db.relationship("Pravnicka_Osoba")
+    podily = db.relationship("Spolecny_Podil_Association")
+
 class Uvolneny_Podil_Association(db.Model):
     __tablename__ = "spolecnici_uvolneny_podil"
     id = db.Column(db.Integer, primary_key=True)
@@ -319,9 +334,9 @@ class Spolecny_Podil_Association(db.Model):
     vymaz_datum = db.Column(MyType)
     text_spolecny_podil = db.Column(db.String)
     podily = db.relationship("Podily_Association")
+    podilnici = db.relationship("Podilnici_Association")
     company = db.relationship("Company")
 
-
 class Ubo(db.Model):
     __tablename__ = "ubo"
     id = db.Column(db.Integer, primary_key=True)
diff --git a/templates/extract-actual.html b/templates/extract-actual.html
index f24cb80..691dae3 100644
--- a/templates/extract-actual.html
+++ b/templates/extract-actual.html
@@ -49,7 +49,6 @@
     {% endif %}
 {% endfor %}
 
-
 {% set vacant_shares_notes = [] %}
 {% for i in range (row.spolecnici_uvolneny_podil|length) %}
     {% if row.spolecnici_uvolneny_podil[i].vymaz_datum == 0 %}
@@ -57,6 +56,13 @@
     {% endif %}
 {% endfor %}
 
+{% set common_shares_notes = [] %}
+{% for i in range (row.spolecnici_spolecny_podil|length) %}
+    {% if row.spolecnici_spolecny_podil[i].vymaz_datum == 0 %}
+        {% set common_shares_notes = common_shares_notes.append(row.spolecnici_spolecny_podil[i]) %}
+    {% endif %}
+{% endfor %}
+
 {% set sole_shareholder_notes = [] %}
 {% for i in range (row.jediny_akcionar|length) %}
     {% if row.jediny_akcionar[i].vymaz_datum == 0 %}
@@ -376,7 +382,7 @@
     <!-- END Display sole shareholder -->
 
     <!-- Display shareholders -->
-    {% if spolecnici_notes|length > 0 or vacant_shares_notes|length > 0 %}
+    {% if spolecnici_notes|length > 0 or vacant_shares_notes|length > 0 or common_shares_notes|length > 0 %}
     <tr>
         <td>Společníci:</td>
         <td></td>
@@ -433,6 +439,54 @@
         {% endfor %}   
     <!-- END Display vacant shares -->
     
+    <!-- Display common shares -->
+    {% for i in range (common_shares_notes|length) %}
+    <tr>
+        <td style = padding-left:2em>Společný obchodní podíl:</td>
+        <td>{% if common_shares_notes[i].text_spolecny_podil != "0" %}{{ common_shares_notes[i].text_spolecny_podil }}{% endif %}</td>
+    </tr>
+    {% set common_shares = [] %}
+    {% for j in range (common_shares_notes[i].podily|length) %}
+        {% if common_shares_notes[i].podily[j].vymaz_datum == 0 %}
+            {% set common_shares = common_shares.append(common_shares_notes[i].podily[j]) %}
+        {% endif %}
+    {% endfor %}
+    
+    {% for k in range (common_shares|length) %}
+    <tr>
+        <td style = padding-left:4em>Podíl:</td>      
+        <td>
+            {% for elem in common_shares[k].my_rep() %}
+                {{ elem }}<br>
+            {% endfor %} 
+        </td>
+    </tr>
+    {% endfor %}        
+
+    {% set common_shares_shareholders = [] %}
+    {% for j in range (common_shares_notes[i].podilnici|length) %}
+        {% if common_shares_notes[i].podilnici[j].vymaz_datum == 0 %}
+            {% set common_shares_shareholders = common_shares_shareholders.append(common_shares_notes[i].podilnici[j]) %}
+        {% endif %}
+    {% endfor %}
+
+    {% for k in range (common_shares_shareholders|length) %}
+    <tr>
+        <td style = padding-left:4em>Podílník:</td>      
+        <td>
+            {% if common_shares_shareholders[k].podilnik_fo_id != None %}
+                {{ common_shares_shareholders[k].jmeno }}
+            {% else %}
+                {{ common_shares_shareholders[k].oznaceni_po.nazev }}{% if common_shares_shareholders[k].oznaceni_po.reg_cislo != 0 %}, reg č. {{ common_shares_shareholders[k].oznaceni_po.reg_cislo }}{% endif %}{% if common_shares_shareholders[k].oznaceni_po.ico != 0 %}, IČ <a href="/{{ common_shares_shareholders[k].oznaceni_po.ico }}">{{ common_shares_shareholders[k].oznaceni_po.ico }}</a>{% endif %}
+            {% endif %}
+            <br>{{ common_shares_shareholders[k].adresa }}
+        </td>
+    </tr>
+    {% endfor %}
+
+    {% endfor %}   
+    <!-- END Display common shares -->
+
     {% endif %}
     <!-- END Display shareholders -->
 
diff --git a/templates/extract.html b/templates/extract.html
index ec58487..9eb1985 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -530,7 +530,7 @@
         <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.spolecnici_spolecny_podil[i].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].vymaz_datum != 0 %} <br>   Vymazáno: {{ row.spolecnici_spolecny_podil[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td>
     </tr>
 
-    <!-- Insert individual vacant ownership interests -->
+    <!-- Insert individual common ownership interests -->
         {% if row.spolecnici_spolecny_podil[i].podily|length > 0 %}
                 
             {% for j in range (row.spolecnici_spolecny_podil[i].podily|length) %}
@@ -553,15 +553,40 @@
             </tr>
             {% endfor %}
         {% endif %}
-    <!-- END of individual vacant ownership interests -->
-    {% endfor %}
+    <!-- END of individual common ownership interests -->
 
+    <!-- Insert individual co-shareholders -->
+    {% if row.spolecnici_spolecny_podil[i].podilnici|length > 0 %}
+                
+    {% for j in range (row.spolecnici_spolecny_podil[i].podilnici|length) %}
+    <tr>
+        <td style = padding-left:4em>Podílník:</td>
+        {% if row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum != 0 %}
+            {% set underlne_style_open = undedrline_open_deleted %}
+            {% set underlne_style_close = undedrline_closed_deleted %}
+        {% else %}
+            {% set underlne_style_open = "" %}
+            {% set underlne_style_close = "" %}
+        {% endif %}
+        <td>{{ underlne_style_open|safe }} 
+            {% if row.spolecnici_spolecny_podil[i].podilnici[j].podilnik_fo_id != None %}
+                {{ row.spolecnici_spolecny_podil[i].podilnici[j].jmeno }}
+            {% else %}
+                {{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.nazev }}{% if row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.reg_cislo != 0 %}, reg č. {{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.reg_cislo }}{% endif %}{% if row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico != 0 %}, IČ <a href="/{{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico }}">{{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico }}</a>{% endif %}
+            {% endif %}
+            <br>{{ row.spolecnici_spolecny_podil[i].podilnici[j].adresa }}
+            {{ underlne_style_close|safe }}
+        </td>
+        <td>{{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_spolecny_podil[i].podilnici[j].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum != 0 %}<br>Vymazáno: {{ row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }}</td>
+    </tr>
+    {% endfor %}
+{% endif %}
+    <!-- END of individual co-shareholders -->
 
+    {% endfor %}
     <!-- END of common ownership interests -->
 
-
-
-{% endif %}
+    {% endif %}
     <!-- END Display shareholders -->
 
     <!-- Display UBO -->
diff --git a/update_db.py b/update_db.py
index df844f6..8444e5b 100644
--- a/update_db.py
+++ b/update_db.py
@@ -238,12 +238,15 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
                     c.execute ("SELECT last_insert_rowid()")
                     spolecnik_id = c.fetchone()[0]
                 insert_podily(c, elem, spolecnik_id)
+            
             elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "SPOLECNY_PODIL":
                 text_spolecny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))
                 c.execute("INSERT INTO spolecnici_spolecny_podil (company_id, zapis_datum, vymaz_datum, text_spolecny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_spolecny_podil,))               
                 c.execute ("SELECT last_insert_rowid()")
-                uvolneny_op_id = c.fetchone()[0]               
-                insert_common_podily(c, elem, uvolneny_op_id)
+                spolecny_op_id = c.fetchone()[0]               
+                insert_common_podily(c, elem, spolecny_op_id)
+                insert_common_shareholders(c, elem, spolecny_op_id)
+
             elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "UVOLNENY_PODIL":
                 text_uvolneny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))
                 c.execute("INSERT INTO spolecnici_uvolneny_podil (company_id, zapis_datum, vymaz_datum, text_uvolneny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_uvolneny_podil,))               
@@ -253,6 +256,30 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
     except Exception as f:
         print(f)
 
+def insert_common_shareholders(c, elem, spolecny_op_id):
+    try:
+        podil_iter = elem.findall("podudaje/Udaj")
+        for podil_elem in podil_iter:
+            if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODILNIK":
+                zapisDatum = str(get_prop(podil_elem, "zapisDatum"))
+                vymazDatum = str(get_prop(podil_elem, "vymazDatum"))
+                typ_podilnika = str(get_prop(podil_elem, "hodnotaText"))
+                if typ_podilnika == "AngazmaFyzicke":
+                    adresa_id = find_sidlo(c, podil_elem, spolecny_op_id)
+                    spolecnik_fo_id = find_fyzicka_osoba(c, 0, podil_elem, 0, spolecny_op_id, 0, adresa_id)
+                    c.execute("INSERT INTO podilnici (podil_id, podilnik_fo_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_fo_id, zapisDatum, vymazDatum, adresa_id))
+                if typ_podilnika == "AngazmaPravnicke":
+                    spol_ico = str(get_prop(podil_elem, "osoba/ico"))
+                    regCislo = str(get_prop(podil_elem, "osoba/regCislo"))
+                    adresa_id = find_sidlo(c, podil_elem, spolecny_op_id)
+                    spolecnik_po_id = find_pravnicka_osoba(c, podil_elem, spol_ico, regCislo, adresa_id)
+                    c.execute("INSERT INTO podilnici (podil_id, podilnik_po_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_po_id, zapisDatum, vymazDatum, adresa_id))
+    except Exception as f:
+        print(f)   
+
+
+
+
 def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key, element):
     try:
         my_iter = predmet_podnikani_elem.findall("podudaje")