initial commit to add display ubo feature

6 files changed, 136 insertions, 91 deletions

diff --git a/db_creation.py b/db_creation.py
index a8fefe7..55e7f32 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -94,7 +94,7 @@ def create_tables(conn):
 	"clenstvi_od"	DATE,
 	"clenstvi_do"	DATE,
 	"funkce"	TEXT,
-	FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"),
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
 	FOREIGN KEY("dozorci_rada_id") REFERENCES "dozorci_rada_relation"("id"),
 	FOREIGN KEY("osoba_id") REFERENCES "fyzicke_osoby"("id"),
 	PRIMARY KEY("id" AUTOINCREMENT)
@@ -138,7 +138,7 @@ def create_tables(conn):
 	"akcionar_po_id"	INTEGER,
 	"akcionar_fo_id"	INTEGER,
 	"adresa_id"	INTEGER,
-	FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"),
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
 	FOREIGN KEY("company_id") REFERENCES "companies"("id"),
 	FOREIGN KEY("akcionar_po_id") REFERENCES "pravnicke_osoby"("id"),
 	FOREIGN KEY("akcionar_fo_id") REFERENCES "fyzicke_osoby"("id"),
@@ -292,7 +292,7 @@ def create_tables(conn):
 	"prokurista_fo_id"	INTEGER,
 	"adresa_id"	INTEGER,
 	"text_prokurista"	TEXT,
-	FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"),
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
 	FOREIGN KEY("prokurista_fo_id") REFERENCES "fyzicke_osoby"("id"),
 	FOREIGN KEY("company_id") REFERENCES "companies"("id"),
 	PRIMARY KEY("id" AUTOINCREMENT)
@@ -318,7 +318,7 @@ def create_tables(conn):
 	"vymaz_datum"	DATE,
 	"adresa_id"	INTEGER,
 	"text_spolecnik"	TEXT,
-	FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"),
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
 	PRIMARY KEY("id" AUTOINCREMENT),
 	FOREIGN KEY("spolecnik_fo_id") REFERENCES "fyzicke_osoby"("id"),
 	FOREIGN KEY("company_id") REFERENCES "companies"("id")
@@ -339,7 +339,7 @@ def create_tables(conn):
 	"funkce"	TEXT,
 	FOREIGN KEY("osoba_id") REFERENCES "fyzicke_osoby"("id"),
 	FOREIGN KEY("statutarni_organ_id") REFERENCES "statutarni_organ_relation"("id"),
-	FOREIGN KEY("adresa_id") REFERENCES "adresy"("id"),
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
 	PRIMARY KEY("id" AUTOINCREMENT)
 ); """
 
@@ -360,6 +360,28 @@ def create_tables(conn):
 	PRIMARY KEY("id" AUTOINCREMENT)
 ); """
 
+    ubo = """ CREATE TABLE "ubo" (
+	"id" INTEGER NOT NULL UNIQUE,
+	"company_id"  INTEGER NOT NULL, 
+	"UBO_id" INTEGER NOT NULL, 
+	"zapis_datum"  DATE,
+	"vymaz_datum"  DATE,
+	"UBO_valid" TEXT, 
+	"spravce" TEXT,  
+	"T_TBC" TEXT, 
+	"postaveni_jinak" TEXT, 
+	"postaveni" TEXT, 
+	"primaUcast" TEXT, 
+	"primaUcastPodil" TEXT, 
+	"rozdeleniProstredku"  TEXT, 
+	"slovni_vyjadreni"  TEXT, 
+	"urcenPoziciVeStatOrg"  TEXT, 
+	"email" TEXT,
+	FOREIGN KEY("company_id") REFERENCES "companies"("id"),
+	FOREIGN KEY("UBO_id") REFERENCES "fyzicke_osoby"("id"),
+	PRIMARY KEY("id" AUTOINCREMENT)
+	); """
+
     ucel = """ CREATE TABLE "ucel" (
 	"id"	INTEGER NOT NULL,
 	"ucel"	TEXT NOT NULL UNIQUE,
@@ -422,7 +444,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, 
+    prokura_common_texts, prokuriste, sidlo_relation, spolecnici, 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:
@@ -689,6 +711,20 @@ def create_indices(conn):
 	"company_id"
 ); """
 
+
+    ubo1 = """ CREATE INDEX "index ubo1" ON "ubo" (
+	"id"
+); """
+
+    ubo2 = """ CREATE INDEX "index ubo2" ON "ubo" (
+	"company_id"
+); """
+
+    ubo3 = """ CREATE INDEX "index ubo3" ON "ubo" (
+	"UBO_id"
+); """
+
+
     ucel1 = """ CREATE INDEX "index ucel1" ON "ucel" (
 	"ucel"
 ); """
@@ -767,7 +803,7 @@ def create_indices(conn):
 	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, 
 	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, ucel1, ucel2, ucel_relation1, ucel_relation2, ucel_relation3]
+	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
     for elem in list_of_indices:
         i += 1
diff --git a/justice_build.py b/justice_build.py
index 287e3e6..9a48f55 100644
--- a/justice_build.py
+++ b/justice_build.py
@@ -13,5 +13,5 @@ def main():
     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)

-        

+  

 main()

diff --git a/models.py b/models.py
index 797a5b1..7263177 100644
--- a/models.py
+++ b/models.py
@@ -300,6 +300,17 @@ class Spolecnici_Association(db.Model):
     podily = db.relationship("Podily_Association")
     company = db.relationship("Company")
 
+class Ubo(db.Model):
+    __tablename__ = "ubo"
+    id = db.Column(db.Integer, primary_key=True)
+    company_id = db.Column(db.Integer, db.ForeignKey('companies.id'))
+    UBO_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id'))   
+    zapis_datum = db.Column(MyType)
+    vymaz_datum = db.Column(MyType)
+    slovni_vyjadreni = db.Column(db.String)
+    jmeno = db.relationship("Fyzicka_Osoba")
+    company = db.relationship("Company")    
+
 class Prokurista_Association(db.Model):
     __tablename__ = "prokuriste"
     id = db.Column(db.Integer, primary_key=True)
@@ -397,6 +408,7 @@ class Company(db.Model):
     prokura_common_text = db.relationship("Prokura_Common_Text_Association")
     jediny_akcionar = db.relationship("Jediny_Akcionar_Association")
     sidlo_text = db.relationship("Sidlo_Association")
+    ubo = db.relationship("Ubo")
 
     def current_legal_form_text(self):
         for elem in self.pravni_forma_text:
@@ -715,7 +727,8 @@ class Akcie(db.Model):
         elif self.akcie_podoba == "IMOBILIZOVANA":
             joined_share_descr += "v imobilizované podobě "
 
-        joined_share_descr += "ve jmenovité hodnotě " + self.akcie_hodnota_value        
+        if self.akcie_hodnota_value != "0":
+            joined_share_descr += "ve jmenovité hodnotě " + self.akcie_hodnota_value        
 
         if self.akcie_hodnota_typ == "KORUNY":
             joined_share_descr += "Kč"
diff --git a/templates/extract-actual.html b/templates/extract-actual.html
index 6bf6087..a1f0241 100644
--- a/templates/extract-actual.html
+++ b/templates/extract-actual.html
@@ -56,6 +56,13 @@
     {% endif %}
 {% endfor %}
 
+{% set ubo_notes = [] %}
+{% for i in range (row.ubo|length) %}
+    {% if row.ubo[i].vymaz_datum == 0 %}
+        {% set ubo_notes = ubo_notes.append(row.ubo[i]) %}
+    {% endif %}
+{% endfor %}
+
 {% set shares_notes = [] %}
 {% for i in range (row.akcie|length) %}
     {% if row.akcie[i].vymaz_datum == 0 %}
@@ -193,33 +200,9 @@
     <!-- Display registered capital -->
     {% if zakladni_kapital_notes|length > 0 %}
     <tr>
-        {% if zakladni_kapital_notes[0].vklad_typ == "KORUNY" %}
-            {% set vklad_typ_symbol = "Kč" %}
-            {% set vklad_typ_description = zakladni_kapital_notes[0].vklad_hodnota %}
-        {% elif zakladni_kapital_notes[0].vklad_typ == "EURA" %}
-            {% set vklad_typ_symbol = "euro" %}
-            {% set vklad_typ_description = zakladni_kapital_notes[0].vklad_hodnota %}
-        {% else %}
-            {% set vklad_typ_symbol = "" %}
-            {% set vklad_typ_description = zakladni_kapital_notes[0].vklad_hodnota %}
-        {% endif %}           
-
-        {% if zakladni_kapital_notes[0].splaceni_typ == "KORUNY" %}
-            {% set splaceni_typ_symbol = "Kč" %}
-            {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %}
-        {% elif zakladni_kapital_notes[0].splaceni_typ == "PROCENTA" %}
-            {% set splaceni_typ_symbol = "%" %}
-            {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %}
-        {% elif zakladni_kapital_notes[0].splaceni_typ == "EURA" %}
-            {% set splaceni_typ_symbol = "euro" %}
-            {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %}
-        {% else %}
-            {% set splaceni_typ_symbol = "" %}
-            {% set splaceni_typ_description = zakladni_kapital_notes[0].splaceni_hodnota %}
-        {% endif %}  
 
         <td>Základní kapitál:</td>
-        <td>{{ underlne_style_open|safe }} {{ vklad_typ_description }} {{ vklad_typ_symbol }} {% if splaceni_typ_description != "0" %} <br> Splaceno: {{ splaceni_typ_description }} {{ splaceni_typ_symbol }} {% endif %}  {{ underlne_style_close|safe }}</td>
+        <td>{% for elem in zakladni_kapital_notes[0].my_rep() %} {{elem}}<br>{% endfor %}</td>
     </tr>
     {% endif %}
     <!-- END Display registered capital -->
@@ -406,37 +389,11 @@
         
         {% for k in range (spolecnik_podily|length) %}
         <tr>
-            <td style = padding-left:4em>Podíl:</td>
-                <!-- Set type of deposit -->
-                {% if spolecnik_podily[k].vklad_typ == "KORUNY" %}
-                    {% set vklad_typ_string = "Kč" %}
-                {% elif spolecnik_podily[k].vklad_typ == "EURA" %}
-                    {% set vklad_typ_string = "Euro" %}
-                {% else %}
-                    {% set vklad_typ_string = "" %}
-                {% endif %}
-
-                <!-- Set type of repaid contribution -->
-                {% if spolecnik_podily[k].splaceni_typ == "KORUNY" %}
-                    {% set splaceni_typ_string = "Kč" %}
-                {% elif spolecnik_podily[k].splaceni_typ == "PROCENTA" %}
-                    {% set splaceni_typ_string = "%" %}                      
-                {% elif spolecnik_podily[k].splaceni_typ == "EURA" %}
-                    {% set splaceni_typ_string = "Euro" %}
-                {% else %}
-                    {% set splaceni_typ_string = "" %}
-                {% endif %}
-                
-                <!-- Set type of share -->
-                {% if spolecnik_podily[k].souhrn_typ == "PROCENTA" %}
-                    {% set souhrn_typ_string = "%" %}
-                {% else %}
-                    {% set souhrn_typ_string = "" %}
-                {% endif %}            
-            <td>Vklad: {{ spolecnik_podily[k].vklad_text }} {{ vklad_typ_string }}<br>
-                    Splaceno: {{ spolecnik_podily[k].splaceni_text }} {{ splaceni_typ_string }}<br>
-                    {% if spolecnik_podily[k].souhrn_text != "0"%}Podíl: {{ spolecnik_podily[k].souhrn_text }} {{ souhrn_typ_string }}{% endif %}
-                    {% if spolecnik_podily[k].druh_podilu.druh_podilu != "0" %}<br>Druh podílu: {{ spolecnik_podily[k].druh_podilu.druh_podilu }}{% endif %}
+            <td style = padding-left:4em>Podíl:</td>      
+            <td>
+                {% for elem in spolecnik_podily[k].my_rep() %}
+                    {{ elem }}<br>
+                {% endfor %} 
             </td>
         </tr>
         {% endfor %}        
@@ -444,6 +401,21 @@
     {% endif %}
     <!-- END Display shareholders -->
 
+    <!-- Display UBO -->
+    {% if ubo_notes|length > 0 %}
+    <tr>
+        <td>Konečný vlastník:</td>
+        <td></td>
+    </tr>
+    {% for i in range (ubo_notes|length) %}
+    <tr>
+        <td></td>
+        <td>{{ubo_notes[i].jmeno }}<br>{{ubo_notes[i].jmeno.adresa }} {% if ubo_notes[i].slovni_vyjadreni != "0" %}<br>{{ ubo_notes[i].slovni_vyjadreni }}</br>{% endif %} </td>
+    </tr>   
+    {% endfor %}
+    {% endif %}
+    <!-- END Display UBO -->
+
     <!-- Display shares -->
     {% if shares_notes|length > 0 %}
         {% for i in range (shares_notes|length) %}
diff --git a/templates/extract.html b/templates/extract.html
index 0c4efd0..6ff31de 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -137,32 +137,6 @@
             {% set underlne_style_close = "" %}
         {% endif %}
 
-        <!-- TODO: Have backend handle this -->
-        {% if row.zakladni_kapital[i].vklad_typ == "KORUNY" %}
-            {% set vklad_typ_symbol = "Kč" %}
-            {% set vklad_typ_description = row.zakladni_kapital[i].vklad_hodnota %}
-        {% elif row.zakladni_kapital[i].vklad_typ == "EURA" %}
-            {% set vklad_typ_symbol = "euro" %}
-            {% set vklad_typ_description = row.zakladni_kapital[i].vklad_hodnota %}
-        {% else %}
-            {% set vklad_typ_symbol = "" %}
-            {% set vklad_typ_description = row.zakladni_kapital[i].vklad_hodnota %}
-        {% endif %}           
-
-        {% if row.zakladni_kapital[i].splaceni_typ == "KORUNY" %}
-            {% set splaceni_typ_symbol = "Kč" %}
-            {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %}
-        {% elif row.zakladni_kapital[i].splaceni_typ == "PROCENTA" %}
-            {% set splaceni_typ_symbol = "%" %}
-            {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %}
-        {% elif row.zakladni_kapital[i].splaceni_typ == "EURA" %}
-            {% set splaceni_typ_symbol = "euro" %}
-            {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %}
-        {% else %}
-            {% set splaceni_typ_symbol = "" %}
-            {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %}
-        {% endif %}  
-
         <td>{{ underlne_style_open|safe }} {% for elem in row.zakladni_kapital[i].my_rep() %} {{elem}}<br>{% endfor %} {{ underlne_style_close|safe }}</td>
         <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.zakladni_kapital[i].zapis_datum }} {% if row.zakladni_kapital[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.zakladni_kapital[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td>
     </tr>    
@@ -498,6 +472,30 @@
     {% endif %}
     <!-- END Display shareholders -->
 
+    <!-- Display UBO -->
+    {% if row.ubo|length > 0 %}
+    <tr>
+        <td>Konečný vlastník:</td>
+        <td></td>
+        <td></td>
+    </tr>
+    {% for i in range (row.ubo|length) %}
+    <tr>
+        <td></td>
+        {% if row.ubo[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 }} {{row.ubo[i].jmeno }}<br>{{ubo_notes[i].jmeno.adresa }} {% if row.ubo[i].slovni_vyjadreni != "0" %}<br>{{row.ubo[i].slovni_vyjadreni }}</br>{% endif %} {{ underlne_style_close|safe }}</td>
+        <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.ubo[i].zapis_datum }} {% if row.ubo[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.ubo[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td>
+    </tr>    
+    {% endfor %}
+    {% endif %}
+    <!-- END Display UBO -->
+
     <!-- Display shares -->
     {% if row.akcie|length > 0 %}
     {% for i in range (row.akcie|length) %}
diff --git a/update_db.py b/update_db.py
index 6e51e78..6c50b70 100644
--- a/update_db.py
+++ b/update_db.py
@@ -111,6 +111,9 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key):
                     find_insolvency(c, ICO, elem2, conn, primary_sql_key)
                 elif udajTyp_name == "KONKURS_SEKCE":
                     find_konkurz(c, ICO, elem2, conn, primary_sql_key)
+                elif udajTyp_name == "SKUTECNY_MAJITEL_SEKCE":
+                    find_UBO(c, ICO, elem2, conn, primary_sql_key, element)
+
     except:
         pass
 
@@ -184,6 +187,29 @@ def find_statutar(c, ICO, elem2, conn, primary_sql_key, element):
     except Exception as f:
         print(f)
 
+def find_UBO(c, ICO, elem2, conn, primary_sql_key, element):
+    try:
+        my_iter = elem2.findall("podudaje/Udaj")
+        for elem in my_iter:
+            zapis_datum = str(get_prop(elem, "zapisDatum"))
+            vymaz_datum = str(get_prop(elem, "vymazDatum"))
+            UBO_valid = str(get_prop(elem, "hodnotaUdaje/valid"))
+            spravce = str(get_prop(elem, "hodnotaUdaje/spravce"))
+            T_TBC = str(get_prop(elem, "hodnotaUdaje/T"))
+            postaveni_jinak = str(get_prop(elem, "hodnotaUdaje/postaveniJinak"))
+            postaveni = str(get_prop(elem, "hodnotaUdaje/postaveni")) 
+            primaUcast = str(get_prop(elem, "hodnotaUdaje/PrimaUcast"))
+            primaUcastPodil = str(get_prop(elem, "hodnotaUdaje/primaUcastPodil"))
+            rozdeleniProstredku = str(get_prop(elem, "hodnotaUdaje/PrimaUcast"))
+            slovni_vyjadreni = str(get_prop(elem, "hodnotaUdaje/slovniVyjadreni"))
+            urcenPoziciVeStatOrg = str(get_prop(elem, "hodnotaUdaje/urcenPoziciVeStatOrg"))
+            email = str(get_prop(elem, "hodnotaUdaje/email"))
+            adresa_id = find_sidlo(c, elem, primary_sql_key)
+            UBO_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element, adresa_id)
+            c.execute("INSERT INTO ubo (company_id, UBO_id, zapis_datum, vymaz_datum, UBO_valid, spravce, T_TBC, postaveni_jinak, postaveni, primaUcast, primaUcastPodil, rozdeleniProstredku, slovni_vyjadreni, urcenPoziciVeStatOrg, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (primary_sql_key, UBO_id, zapis_datum, vymaz_datum, UBO_valid, spravce, T_TBC, postaveni_jinak, postaveni, primaUcast, primaUcastPodil, rozdeleniProstredku, slovni_vyjadreni, urcenPoziciVeStatOrg, email,))
+    except Exception as f:
+        print(f)
+
 def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
     try:
         my_iter = elem2.findall("podudaje/Udaj")