add feature to display vacant OIs

7 files changed, 162 insertions, 18 deletions

diff --git a/db_creation.py b/db_creation.py
index cf9d504..0e6299a 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -197,7 +197,8 @@ def create_tables(conn):
 
     podily = """ CREATE TABLE "podily" (
 	"id"	INTEGER NOT NULL UNIQUE,
-	"spolecnik_id"	INTEGER NOT NULL,
+	"spolecnik_id"	INTEGER,
+	"uvolneny_podil_id" INTEGER,
 	"zapis_datum"	DATE,
 	"vymaz_datum"	DATE,
 	"druh_podilu_id"	INTEGER,
@@ -209,6 +210,7 @@ def create_tables(conn):
 	"splaceni_text"	TEXT,
 	FOREIGN KEY("druh_podilu_id") REFERENCES "druhy_podilu"("id"),
 	FOREIGN KEY("spolecnik_id") REFERENCES "spolecnici"("id"),
+	FOREIGN KEY("uvolneny_podil_id") REFERENCES "spolecnici_uvolneny_podil"("id"),
 	PRIMARY KEY("id" AUTOINCREMENT)
 ); """
 
@@ -324,6 +326,17 @@ def create_tables(conn):
 	FOREIGN KEY("company_id") REFERENCES "companies"("id")
 ); """
 
+    spolecnici_uvolneny_podil = """ CREATE TABLE "spolecnici_uvolneny_podil" (
+	"id" INTEGER NOT NULL UNIQUE,
+	"company_id"	INTEGER NOT NULL,
+	"zapis_datum"	DATE,
+	"vymaz_datum"	DATE,
+	"text_uvolneny_podil"	TEXT,
+	PRIMARY KEY("id" AUTOINCREMENT),
+	FOREIGN KEY("company_id") REFERENCES "companies"("id")
+	) """
+
+
     statutarni_organ_clen_relation = """ CREATE TABLE "statutarni_organ_clen_relation" (
 	"id"	INTEGER NOT NULL UNIQUE,
 	"statutarni_organ_id"	INTEGER NOT NULL,
@@ -444,7 +457,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, 
     pravni_formy_relation, pravnicke_osoby, predmety_cinnosti, predmety_cinnosti_relation, prdmety_podnikani, predmety_podnikani_relation,
-    prokura_common_texts, prokuriste, sidlo_relation, spolecnici, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, ubo, 
+    prokura_common_texts, prokuriste, sidlo_relation, spolecnici, spolecnici_uvolneny_podil, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, ubo, 
     ucel, ucel_relation, zakladni_kapital, zapis_soudy, zpusoby_jednani, zpusoby_jednani_relation]
     for elem in list_of_tables:
         try:
@@ -570,6 +583,10 @@ def create_indices(conn):
 	"spolecnik_id"
 ); """
 
+    podily3 = """ CREATE INDEX "index podily3" ON "podily" (
+	"uvolneny_podil_id"
+); """
+
     pravni_formy = """ CREATE INDEX "index pravni_formy" ON "pravni_formy" (
 	"pravni_forma"
 ); """
@@ -694,6 +711,14 @@ def create_indices(conn):
 	"adresa_id"
 ); """
 
+    spolecnici_uvolneny_podil1 = """ CREATE INDEX "index uvolneny_podil1" on "spolecnici_uvolneny_podil" (
+	"id"
+); """
+
+    spolecnici_uvolneny_podil2 = """ CREATE INDEX "index uvolneny_podil2" on "spolecnici_uvolneny_podil" (
+	"company_id"
+); """
+
     statutarni_organy = """ CREATE INDEX "index statutarn_organy" ON "statutarni_organy" (
 	"id",
 	"statutarni_organ_text"
@@ -800,11 +825,11 @@ 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, pravni_formy, pravni_formy_relation1, pravni_formy_relation2, 
+	pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, podily3, 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, 
-	spolecnici2, spolecnici3, spolecnici4, spolecnici5, statutarni_organy, statutarni_organy_relation1, statutarni_organy_relation2, 
+	spolecnici2, spolecnici3, spolecnici4, spolecnici5, spolecnici_uvolneny_podil1, spolecnici_uvolneny_podil2, statutarni_organy, statutarni_organy_relation1, statutarni_organy_relation2, 
 	statutarni_organy_relation_3, statutarni_organy_relation_4, zakladni_kapital1, zakladni_kapital2, zpusob_jednani, zpusob_jednani_relation1, zpusob_jednani_relation2, 
 	zpusob_jednani_relation3, pravnicke_osoby1, pravnicke_osoby2, pravnicke_osoby3, pravnicke_osoby4, statutarni_organy_relation_5, fyzicke_osoby1, ubo1, ubo2, ubo3, ucel1, ucel2, ucel_relation1, ucel_relation2, ucel_relation3]
     i = 0
diff --git a/justice_build.py b/justice_build.py
index 5850340..4b82660 100644
--- a/justice_build.py
+++ b/justice_build.py
@@ -6,12 +6,13 @@ import os
 

 def main():

     DB_name = "justice.db"

-    backup_DB()

+    # backup_DB()

     create_DB(DB_name)

-    valid_files = get_valid_filenames()

+    # valid_files = get_valid_filenames()

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

     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 deb9d51..80b598e 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
+from models import Adresy_v2, Uvolneny_Podil_Association
 from tables import Results
 from sqlalchemy.sql import select
 from sqlalchemy.sql import text
diff --git a/models.py b/models.py
index 6c1b84c..4b94374 100644
--- a/models.py
+++ b/models.py
@@ -301,6 +301,16 @@ class Spolecnici_Association(db.Model):
     podily = db.relationship("Podily_Association")
     company = db.relationship("Company")
 
+class Uvolneny_Podil_Association(db.Model):
+    __tablename__ = "spolecnici_uvolneny_podil"
+    id = db.Column(db.Integer, primary_key=True)
+    company_id = db.Column(db.Integer, db.ForeignKey('companies.id'))
+    zapis_datum = db.Column(MyType)
+    vymaz_datum = db.Column(MyType)
+    text_uvolneny_podil = db.Column(db.String)
+    podily = db.relationship("Podily_Association")
+    company = db.relationship("Company")
+
 class Ubo(db.Model):
     __tablename__ = "ubo"
     id = db.Column(db.Integer, primary_key=True)
@@ -356,6 +366,7 @@ 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'))
+    uvolneny_podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_uvolneny_podil.id'))
     zapis_datum = db.Column(MyType)
     vymaz_datum = db.Column(MyType)
     druh_podilu_id = db.Column(db.Integer, db.ForeignKey('druhy_podilu.id'))
@@ -409,6 +420,7 @@ class Company(db.Model):
     statutarni_organ_text = db.relationship("Statutarni_Organ_Association")
     dozorci_rada_text = db.relationship("Dozorci_Rada_Association")
     spolecnici = db.relationship("Spolecnici_Association")
+    spolecnici_uvolneny_podil = db.relationship("Uvolneny_Podil_Association")
     prokurista = db.relationship("Prokurista_Association")
     prokura_common_text = db.relationship("Prokura_Common_Text_Association")
     jediny_akcionar = db.relationship("Jediny_Akcionar_Association")
diff --git a/templates/extract-actual.html b/templates/extract-actual.html
index 6bc51fe..f24cb80 100644
--- a/templates/extract-actual.html
+++ b/templates/extract-actual.html
@@ -49,6 +49,14 @@
     {% endif %}
 {% endfor %}
 
+
+{% set vacant_shares_notes = [] %}
+{% for i in range (row.spolecnici_uvolneny_podil|length) %}
+    {% if row.spolecnici_uvolneny_podil[i].vymaz_datum == 0 %}
+        {% set vacant_shares_notes = vacant_shares_notes.append(row.spolecnici_uvolneny_podil[i]) %}
+    {% endif %}
+{% endfor %}
+
 {% set sole_shareholder_notes = [] %}
 {% for i in range (row.jediny_akcionar|length) %}
     {% if row.jediny_akcionar[i].vymaz_datum == 0 %}
@@ -368,7 +376,7 @@
     <!-- END Display sole shareholder -->
 
     <!-- Display shareholders -->
-    {% if spolecnici_notes|length > 0 %}
+    {% if spolecnici_notes|length > 0 or vacant_shares_notes|length > 0 %}
     <tr>
         <td>Společníci:</td>
         <td></td>
@@ -398,6 +406,33 @@
         </tr>
         {% endfor %}        
         {% endfor %}
+    
+    <!-- Display vacant shares -->
+        {% for i in range (vacant_shares_notes|length) %}
+        <tr>
+            <td style = padding-left:2em>Uvolněný obchodní podíl:</td>
+            <td>{% if vacant_shares_notes[i].text_uvolneny_podil != "0" %}{{ vacant_shares_notes[i].text_uvolneny_podil }}{% endif %}</td>
+        </tr>
+        {% set vacant_shares = [] %}
+        {% for j in range (vacant_shares_notes[i].podily|length) %}
+            {% if vacant_shares_notes[i].podily[j].vymaz_datum == 0 %}
+                {% set vacant_shares = vacant_shares.append(vacant_shares_notes[i].podily[j]) %}
+            {% endif %}
+        {% endfor %}
+        
+        {% for k in range (vacant_shares|length) %}
+        <tr>
+            <td style = padding-left:4em>Podíl:</td>      
+            <td>
+                {% for elem in vacant_shares[k].my_rep() %}
+                    {{ elem }}<br>
+                {% endfor %} 
+            </td>
+        </tr>
+        {% endfor %}        
+        {% endfor %}   
+    <!-- END Display vacant shares -->
+    
     {% endif %}
     <!-- END Display shareholders -->
 
diff --git a/templates/extract.html b/templates/extract.html
index 17bdd73..2b27462 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -422,7 +422,7 @@
     <!-- END Display sole shareholder -->
 
     <!-- Display shareholders -->
-    {% if row.spolecnici|length > 0 %}
+{% if row.spolecnici|length > 0 or row.spolecnici_uvolneny_podil|length > 0 %}
     <tr>
         <td>Společníci:</td>
         <td></td>
@@ -466,10 +466,54 @@
             {% endfor %}
         {% endif %}
         <!-- END of individual ownership interests -->
+    </tr>
+    {% endfor %}
 
+
+    <!-- Insert vacant ownership interests  -->
+    {% for i in range (row.spolecnici_uvolneny_podil|length) %}
+    <tr>
+        <td style = padding-left:2em>Uvolněný obchodní podíl:</td>
+        {% if row.spolecnici_uvolneny_podil[i].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_uvolneny_podil[i].text_uvolneny_podil != "0" %}{{ row.spolecnici_uvolneny_podil[i].text_uvolneny_podil }}{% endif %}   
+            {{ underlne_style_close|safe }}</td>
+        <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.spolecnici_uvolneny_podil[i].zapis_datum }} {% if row.spolecnici_uvolneny_podil[i].vymaz_datum != 0 %} <br>   Vymazáno: {{ row.spolecnici_uvolneny_podil[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td>
     </tr>
+
+    <!-- Insert individual vacant ownership interests -->
+        {% if row.spolecnici_uvolneny_podil[i].podily|length > 0 %}
+                
+            {% for j in range (row.spolecnici_uvolneny_podil[i].podily|length) %}
+            <tr>
+                <td style = padding-left:4em>Podíl:</td>
+                {% if row.spolecnici_uvolneny_podil[i].podily[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 }} 
+                        {% for elem in row.spolecnici_uvolneny_podil[i].podily[j].my_rep() %}
+                            {{ elem }}<br>
+                        {% endfor %}
+                    {{ underlne_style_close|safe }}
+                </td>
+                <td>{{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_uvolneny_podil[i].podily[j].zapis_datum }} {% if row.spolecnici_uvolneny_podil[i].podily[j].vymaz_datum != 0 %}<br>Vymazáno: {{ row.spolecnici_uvolneny_podil[i].podily[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }}</td>
+            </tr>
+            {% endfor %}
+        {% endif %}
+    <!-- END of individual vacant ownership interests -->
     {% endfor %}
-    {% endif %}
+    <!-- END of vacant ownership interests  -->
+
+{% endif %}
     <!-- END Display shareholders -->
 
     <!-- Display UBO -->
diff --git a/update_db.py b/update_db.py
index d69f777..43dac45 100644
--- a/update_db.py
+++ b/update_db.py
@@ -212,12 +212,12 @@ 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"))
+            spolecnik_kod = 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_typ == "OSOBA":
+            if spolecnik_kod == "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"))
@@ -237,7 +237,17 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
                     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)             
+                insert_podily(c, elem, spolecnik_id)
+            elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "SPOLECNY_PODIL":
+                pass
+                # text_spolecnik = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))
+                # print(text_spolecnik)
+            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,))               
+                c.execute ("SELECT last_insert_rowid()")
+                uvolneny_op_id = c.fetchone()[0]
+                insert_vacant_podily(c, elem, uvolneny_op_id)
     except Exception as f:
         print(f)
 
@@ -251,7 +261,7 @@ def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key
                 vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
                 insert_instructions = [(".//hodnotaText","predmety_podnikani", "predmet_podnikani", "predmety_podnikani_relation")]
                 for elem in insert_instructions:
-                    inserted_figure = str(get_prop(elem2, ".//hodnotaText"))
+                    inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize()
                     insert_into_ancillary_table(c, elem, inserted_figure)
                     ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure)
                     insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum)
@@ -268,7 +278,7 @@ def find_predmet_cinnosti(c, ICO, predmet_cinnosti_elem, conn, primary_sql_key,
                 vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
                 insert_instructions = [(".//hodnotaText","predmety_cinnosti", "predmet_cinnosti", "predmety_cinnosti_relation")]
                 for elem in insert_instructions:
-                    inserted_figure = str(get_prop(elem2, ".//hodnotaText"))
+                    inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize()
                     insert_into_ancillary_table(c, elem, inserted_figure)
                     ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure)
                     insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum)
@@ -285,7 +295,7 @@ def find_ucel(c, ICO, ucel_elem, conn, primary_sql_key, element):
                 vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))
                 insert_instructions = [(".//hodnotaText", "ucel", "ucel", "ucel_relation")]
                 for elem in insert_instructions:
-                    inserted_figure = str(get_prop(elem2, ".//hodnotaText"))
+                    inserted_figure = str(get_prop(elem2, ".//hodnotaText")).capitalize()
                     insert_into_ancillary_table(c, elem, inserted_figure)
                     ancillary_table_key = get_anciallary_table_key(c, elem, inserted_figure)
                     insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum)
@@ -625,6 +635,23 @@ def insert_podily(c, elem, spolecnik_id):
     except Exception as f:
         print(f)
 
+def insert_vacant_podily(c, elem, vacant_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 (uvolneny_podil_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (vacant_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"))