Merge pull request #9 from SveterCZE/UBOs-evidence

Merge UBO evidence and advanced ownership interest display to main

12 files changed, 684 insertions, 115 deletions

diff --git a/backup_DB.py b/backup_DB.py
new file mode 100644
index 0000000..fb4d9a1
--- /dev/null
+++ b/backup_DB.py
@@ -0,0 +1,6 @@
+import shutil
+import os
+
+def backup_DB():
+    os.makedirs("backup", exist_ok=True)
+    shutil.move("justice.db", "backup/justice.db")
diff --git a/db_creation.py b/db_creation.py
index a8fefe7..c3836aa 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"),
@@ -195,9 +195,26 @@ 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 NOT NULL,
+	"spolecnik_id"	INTEGER,
+	"uvolneny_podil_id" INTEGER,
+	"spolecny_podil_id" INTEGER,
 	"zapis_datum"	DATE,
 	"vymaz_datum"	DATE,
 	"druh_podilu_id"	INTEGER,
@@ -209,6 +226,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)
 ); """
 
@@ -292,7 +310,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,12 +336,32 @@ 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")
 ); """
 
+    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")
+	) """
+
+    spolecnici_spolecny_podil = """ CREATE TABLE "spolecnici_spolecny_podil" (
+	"id" INTEGER NOT NULL UNIQUE,
+	"company_id"	INTEGER NOT NULL,
+	"zapis_datum"	DATE,
+	"vymaz_datum"	DATE,
+	"text_spolecny_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,
@@ -339,7 +377,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 +398,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,
+	"adresa_id" INTEGER,
+	"zapis_datum"  DATE,
+	"vymaz_datum"  DATE,
+	"postaveni" TEXT,
+	"koncovyPrijemceText" TEXT,
+	"skutecnymMajitelemOd" TEXT,
+	"vlastniPodilNaProspechu" TEXT,
+	"vlastniPodilNaProspechu_typ" TEXT,
+	"vlastniPodilNaProspechu_textValue" TEXT,
+	"vlastniPodilNaHlasovani" TEXT,
+	"vlastniPodilNaHlasovani_typ" TEXT,
+	"vlastniPodilNaHlasovani_value" TEXT,
+	FOREIGN KEY("company_id") REFERENCES "companies"("id"),
+	FOREIGN KEY("UBO_id") REFERENCES "fyzicke_osoby"("id"),
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
+	PRIMARY KEY("id" AUTOINCREMENT)
+	); """
+
     ucel = """ CREATE TABLE "ucel" (
 	"id"	INTEGER NOT NULL,
 	"ucel"	TEXT NOT NULL UNIQUE,
@@ -420,9 +480,9 @@ 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, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, 
+    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]
     for elem in list_of_tables:
         try:
@@ -548,6 +608,34 @@ def create_indices(conn):
 	"spolecnik_id"
 ); """
 
+    podily3 = """ CREATE INDEX "index podily3" ON "podily" (
+	"uvolneny_podil_id"
+); """
+
+    podily4 = """ CREATE INDEX "index podily4" ON "podily" (
+	"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"
 ); """
@@ -672,6 +760,23 @@ 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"
+); """
+
+    spolecnici_spolecny_podil1 = """ CREATE INDEX "index spolecny_podil1" on "spolecnici_spolecny_podil" (
+	"id"
+); """
+
+    spolecnici_spolecny_podil2 = """ CREATE INDEX "index spolecny_podil2" on "spolecnici_spolecny_podil" (
+	"company_id"
+); """
+
+
     statutarni_organy = """ CREATE INDEX "index statutarn_organy" ON "statutarni_organy" (
 	"id",
 	"statutarni_organ_text"
@@ -689,6 +794,23 @@ 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"
+); """
+
+    ubo4 = """ CREATE INDEX "index ubo4" ON "ubo" (
+	"UBO_adresa_id"
+); """
+
     ucel1 = """ CREATE INDEX "index ucel1" ON "ucel" (
 	"ucel"
 ); """
@@ -749,7 +871,6 @@ def create_indices(conn):
 	"osoba_id"
 ); """
 
-
     dr_relation_4 = """ CREATE INDEX "index dr clen relation4" ON "dr_organ_clen_relation" (
 	"pravnicka_osoba_id"
 ); """
@@ -761,13 +882,14 @@ 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, 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, 
-	spolecnici2, spolecnici3, spolecnici4, spolecnici5, statutarni_organy, statutarni_organy_relation1, statutarni_organy_relation2, 
+	spolecnici2, spolecnici3, spolecnici4, spolecnici5, spolecnici_uvolneny_podil1, spolecnici_uvolneny_podil2, spolecnici_spolecny_podil1, spolecnici_spolecny_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, 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, ubo4, 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..9acf1e7 100644
--- a/justice_build.py
+++ b/justice_build.py
@@ -1,17 +1,19 @@
 from db_creation import create_DB

 from download_files import download_data, get_valid_filenames

 from update_db import update_DB

+from backup_DB import backup_DB

 import os

 

 def main():

     DB_name = "justice.db"

+    # backup_DB()

     create_DB(DB_name)

     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:

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

-        update_DB(modified_file_name, DB_name)

-        

-main()

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

+            update_DB(modified_file_name, DB_name)

+            

+main()
\ No newline at end of file
diff --git a/main.py b/main.py
index 5d43f97..a4d8293 100644
--- a/main.py
+++ b/main.py
@@ -1,11 +1,11 @@
 from app import app
 from forms import GeneralSearchForm, JusticeSearchForm, CompanyForm, PersonSearchForm, EntitySearchForm 
 from flask import flash, render_template, request, redirect
-from models import Company, Insolvency_Events, Konkurz_Events, Predmet_Podnikani, Predmety_Podnikani_Association, Predmet_Cinnosti, Predmety_Cinnosti_Association 
+from models import Company, Insolvency_Events, Konkurz_Events, Predmet_Podnikani, Predmety_Podnikani_Association, Predmet_Cinnosti, Predmety_Cinnosti_Association, Ubo 
 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, Spolecny_Podil_Association, Podilnici_Association
 from tables import Results
 from sqlalchemy.sql import select
 from sqlalchemy.sql import text
@@ -425,6 +425,30 @@ def search_results_sidlo(adresa_id):
         table.border = True
         return render_template("results2.html", results=results, form=search, show_form = False)
 
+
+# UBO reults
+@app.route('/results-ubo-<int:ubo_id>', methods=['GET', 'POST'])
+def search_results_ubo(ubo_id):
+    search = JusticeSearchForm(request.form)
+
+    results = []
+    qry = Company.query
+    qry = qry.join(Ubo, Company.ubo)
+    qry = qry.filter(Ubo.vymaz_datum == 0)
+    qry = qry.join(Fyzicka_Osoba, Ubo.jmeno)
+    qry = qry.filter(Fyzicka_Osoba.id == ubo_id)
+    results = qry.all()
+
+    if not results:
+        flash('No results found!')
+        return redirect('/')
+
+    else:
+        table = Results(results)
+        table.border = True
+        return render_template("results2.html", results=results, form=search, show_form = False)
+
+
 @app.route("/<int:ico>", methods=['GET', 'POST'])
 def extract(ico):
     qry = Company.query
@@ -457,7 +481,12 @@ def find_oldest_companies():
 @app.route("/most_common_purpose", methods=['GET', 'POST'])
 def find_most_common_purpose():
     most_common_purpose = count_common_purpose()
-    return render_template("most_common_purpose.html", most_common_purpose = most_common_purpose)    
+    return render_template("most_common_purpose.html", most_common_purpose = most_common_purpose)
+
+@app.route("/most_common_ubo", methods=['GET', 'POST'])
+def find_most_common_ubo():
+    most_common_ubo = count_common_ubo()
+    return render_template("most_common_ubo.html", most_common_ubo = most_common_ubo)        
 
 @app.route("/most_common_business", methods=['GET', 'POST'])
 def find_most_common_business():
@@ -537,7 +566,22 @@ def count_common_purpose():
         selected_purpose = qry.all()
         addresses_frequency.append((selected_purpose[0].ucel, elem[1]))
     conn.close()
-    return addresses_frequency    
+    return addresses_frequency
+
+def count_common_ubo():
+    engine = create_engine('sqlite:///justice.db', echo=True)
+    conn = engine.connect()
+    text_instruction = text("SELECT UBO_id, COUNT(`UBO_id`) AS `value_occurrence` FROM ubo INNER JOIN fyzicke_osoby ON ubo.UBO_id=fyzicke_osoby.id WHERE vymaz_datum = 0 GROUP BY `UBO_id` ORDER BY `value_occurrence` DESC LIMIT 100;")
+    result = conn.execute(text_instruction).fetchall()
+    ubo_frequency = []
+    for elem in result:
+        qry = Fyzicka_Osoba.query
+        qry = qry.filter(Fyzicka_Osoba.id == elem[0])
+        selected_ubo = qry.all()
+        # ubo_frequency.append((selected_ubo[0].jmeno + " " + selected_ubo[0].prijmeni, elem[1], elem[0]))
+        ubo_frequency.append((selected_ubo[0], elem[1], elem[0]))
+    conn.close()
+    return ubo_frequency
 
 def count_common_degrees(method):
     engine = create_engine('sqlite:///justice.db', echo=True)
diff --git a/models.py b/models.py
index 797a5b1..400e5de 100644
--- a/models.py
+++ b/models.py
@@ -245,6 +245,7 @@ class Fyzicka_Osoba(db.Model):
     prokurista_association = db.relationship("Prokurista_Association")
     sole_shareholder_association = db.relationship("Jediny_Akcionar_Association")
     supervisory_board_member_association = db.relationship("Dozorci_Rada_Clen_Association")
+    ubo_association = db.relationship("Ubo")
     adresa = db.relationship("Adresy_v2")
 
     def get_name(self):
@@ -300,6 +301,57 @@ 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)
+    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 Spolecny_Podil_Association(db.Model):
+    __tablename__ = "spolecnici_spolecny_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_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)
+    company_id = db.Column(db.Integer, db.ForeignKey('companies.id'))
+    zapis_datum = db.Column(MyType)
+    vymaz_datum = db.Column(MyType)
+    UBO_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id'))
+    adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id'))
+    adresa = db.relationship("Adresy_v2")   
+    postaveni = db.Column(db.String)
+    koncovyPrijemceText = db.Column(db.String)
+    skutecnymMajitelemOd = db.Column(MyType)
+    jmeno = db.relationship("Fyzicka_Osoba")
+    company = db.relationship("Company")    
+
 class Prokurista_Association(db.Model):
     __tablename__ = "prokuriste"
     id = db.Column(db.Integer, primary_key=True)
@@ -340,6 +392,8 @@ 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'))
+    spolecny_podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_spolecny_podil.id'))
     zapis_datum = db.Column(MyType)
     vymaz_datum = db.Column(MyType)
     druh_podilu_id = db.Column(db.Integer, db.ForeignKey('druhy_podilu.id'))
@@ -393,10 +447,13 @@ 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")
+    spolecnici_spolecny_podil = db.relationship("Spolecny_Podil_Association")
     prokurista = db.relationship("Prokurista_Association")
     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:
@@ -446,7 +503,7 @@ class Adresy_v2(db.Model):
             joined_address += self.psc + " "
         if self.obec != "0" and self.obec != None:
             joined_address += self.obec
-        if (self.stat != "Česká republika") and (self.stat != "Česká republika - neztotožněno"):
+        if (self.stat != "Česká republika") and (self.stat != "Česká republika - neztotožněno") and (self.stat != "0"):
             joined_address += ", " + self.stat
         return joined_address
 
@@ -715,7 +772,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..691dae3 100644
--- a/templates/extract-actual.html
+++ b/templates/extract-actual.html
@@ -49,6 +49,20 @@
     {% 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 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 %}
@@ -56,6 +70,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 +214,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 -->
@@ -385,7 +382,7 @@
     <!-- END Display sole shareholder -->
 
     <!-- Display shareholders -->
-    {% if spolecnici_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>
@@ -406,44 +403,111 @@
         
         {% 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 %}        
         {% 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 -->
+    
+    <!-- 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 -->
 
+    <!-- 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 }} {{ubo_notes[i].jmeno.adresa }} 
+            {% if ubo_notes[i].postaveni != "0" %}<br>{{ ubo_notes[i].postaveni }}{% endif %}
+            {% if ubo_notes[i].koncovyPrijemceText != "0" %}<br>{{ ubo_notes[i].koncovyPrijemceText }}{% 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..9eb1985 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>    
@@ -448,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>
@@ -492,12 +466,155 @@
             {% 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 %}
+    <!-- END of vacant ownership interests  -->
+
+    <!-- Insert common ownership interests -->
+
+    {% for i in range (row.spolecnici_spolecny_podil|length) %}
+    <tr>
+        <td style = padding-left:2em>Společný obchodní podíl:</td>
+        {% if row.spolecnici_spolecny_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_spolecny_podil[i].text_uvolneny_podil != "0" %}{{ row.spolecnici_spolecny_podil[i].text_spolecny_podil }}{% endif %}   
+            {{ underlne_style_close|safe }}</td>
+        <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 common ownership interests -->
+        {% if row.spolecnici_spolecny_podil[i].podily|length > 0 %}
+                
+            {% for j in range (row.spolecnici_spolecny_podil[i].podily|length) %}
+            <tr>
+                <td style = padding-left:4em>Podíl:</td>
+                {% if row.spolecnici_spolecny_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_spolecny_podil[i].podily[j].my_rep() %}
+                            {{ elem }}<br>
+                        {% endfor %}
+                    {{ underlne_style_close|safe }}
+                </td>
+                <td>{{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_spolecny_podil[i].podily[j].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].podily[j].vymaz_datum != 0 %}<br>Vymazáno: {{ row.spolecnici_spolecny_podil[i].podily[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }}</td>
+            </tr>
+            {% endfor %}
+        {% endif %}
+    <!-- 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 %}
     <!-- 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 }} {{row.ubo[i].jmeno.adresa}} 
+            {% if row.ubo[i].postaveni != "0" %}<br>{{row.ubo[i].postaveni }}{% endif %}
+            {% if row.ubo[i].koncovyPrijemceText != "0" %}<br>{{row.ubo[i].koncovyPrijemceText }}{% 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/templates/most_common_ubo.html b/templates/most_common_ubo.html
new file mode 100644
index 0000000..318d52f
--- /dev/null
+++ b/templates/most_common_ubo.html
@@ -0,0 +1,24 @@
+{% include 'header.html' %}
+
+<h1>Nejčastější koneční vlastníci:</h1>
+
+<table class="table table-hover" style="width: auto">
+    <thead class="thead-dark">
+    <tr class="table-info">
+        <th scope="col">#</th>
+        <th scope="col">Jméno</th>
+        <th scope="col">Počet společností</th>
+    </tr>
+    </thead>
+    <tbody>
+    {% for i in range (most_common_ubo|length) %}
+    <tr>
+        <th scope = "row">{{ i + 1 }}</th>
+        <td><a href="/results-ubo-{{most_common_ubo[i][2]}}">{{ most_common_ubo[i][0] }}</a></td>
+        <td>{{ most_common_ubo[i][1] }}</td>
+    </tr>
+    {% endfor %}
+    </tbody>
+</table>
+
+{% include 'footer.html' %}
\ No newline at end of file
diff --git a/templates/results_persons.html b/templates/results_persons.html
index 65fac98..fc90e08 100644
--- a/templates/results_persons.html
+++ b/templates/results_persons.html
@@ -97,6 +97,24 @@
         {% endif %}
     {% endfor %}    
 
+    {% for elem in row.ubo_association %}
+        {% if selection_method == "actual_results" and elem.vymaz_datum != 0 %}
+            {% set xxx = [] %}
+        {% else %}     
+            {% set my_dict = {} %}
+            {% set x = my_dict.__setitem__("adresa", elem.adresa) %}
+            {% set x = my_dict.__setitem__("funkce", "konečný vlastník") %}
+            {% set x = my_dict.__setitem__("nazev", elem.company.nazev) %}
+            {% set x = my_dict.__setitem__("ico", elem.company.ico) %}
+            {% set x = my_dict.__setitem__("oddil", elem.company.oddil) %}
+            {% set x = my_dict.__setitem__("vlozka", elem.company.vlozka) %}
+            {% set x = my_dict.__setitem__("soud", elem.company.soud) %}
+            {% set x = my_dict.__setitem__("zapis", elem.company.zapis) %}
+            {% set x = my_dict.__setitem__("pravni_forma", elem.company.current_legal_form_text()) %}
+            {% set test_list = test_list.append(my_dict) %}
+        {% endif %}
+    {% endfor %}    
+
     {% for elem in test_list %}
         {% if count.value % 2 == 0 %}
             {% set table_style = "table table-light" %}
diff --git a/templates/trivia.html b/templates/trivia.html
index 750706c..43e82ab 100644
--- a/templates/trivia.html
+++ b/templates/trivia.html
@@ -6,6 +6,7 @@
 <p>Další zajímvavé údaje z obchodního resjtříku:</p>
 <p><a href="/most_common_addresses">Seznam nejčastějších sídel</a></p>
 <p><a href="/oldest_companies">Seznam nejstarších společností</a></p>
+<p><a href="/most_common_ubo">Seznam nejčastějších konečných vlastníků</a></p>
 <p><a href="/most_common_purpose">Seznam nejčastějších účelů</a></p>
 <p><a href="/most_common_business">Seznam nejčastějších předmětů podnikání</a></p>
 <p><a href="/most_common_activity">Seznam nejčastějších předmětů činnosti</a></p>
diff --git a/todolist.html b/todolist.html
index c86b17f..11f618f 100644
--- a/todolist.html
+++ b/todolist.html
@@ -1,7 +1,6 @@
 <ol>
     <li>Add a feature to display other types of ownerhsip interests (joint onwership interest, vacant ownership interest).</li>
     <li>Refactor excessive duplications in the main code.</li>
-    <li>Check how to make diacritics work in searches.</li>
     <li>Check if I can remove duplication in results when searching for legal or natural persons in a role.</li>
     <li>Write some documentation :)</li>
 </ol>
\ No newline at end of file
diff --git a/update_db.py b/update_db.py
index 6e51e78..8444e5b 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,16 +187,37 @@ 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"))
+            postaveni = str(get_prop(elem, "hodnotaUdaje/postaveni")).split(";")[0]
+            koncovyPrijemceText = str(get_prop(elem, "hodnotaUdaje/koncovyPrijemceText"))
+            skutecnymMajitelemOd = str(get_prop(elem, "hodnotaUdaje/skutecnymMajitelemOd"))
+            vlastniPodilNaProspechu = str(get_prop(elem, "hodnotaUdaje/vlastniPodilNaProspechu"))
+            vlastniPodilNaProspechu_typ = str(get_prop(elem, "hodnotaUdaje/podilNaProspechu/typ"))
+            vlastniPodilNaProspechu_textValue = str(get_prop(elem, "hodnotaUdaje/podilNaProspechu/textValue"))
+            vlastniPodilNaHlasovani = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani"))
+            vlastniPodilNaHlasovani_typ = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani/typ"))
+            vlastniPodilNaHlasovani_value = str(get_prop(elem, "hodnotaUdaje/podilNaHlasovani/textValue"))
+            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, adresa_id, zapis_datum, vymaz_datum, postaveni, koncovyPrijemceText, skutecnymMajitelemOd, vlastniPodilNaProspechu, vlastniPodilNaProspechu_typ, vlastniPodilNaProspechu_textValue, vlastniPodilNaHlasovani, vlastniPodilNaHlasovani_typ, vlastniPodilNaHlasovani_value) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (primary_sql_key, UBO_id, adresa_id, zapis_datum, vymaz_datum, postaveni, koncovyPrijemceText, skutecnymMajitelemOd, vlastniPodilNaProspechu, vlastniPodilNaProspechu_typ, vlastniPodilNaProspechu_textValue, vlastniPodilNaHlasovani, vlastniPodilNaHlasovani_typ, vlastniPodilNaHlasovani_value,))
+    except Exception as f:
+        print(f)
+
 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"))
@@ -213,10 +237,49 @@ 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":
+                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()")
+                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,))               
+                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)
 
+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")
@@ -227,7 +290,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)
@@ -244,7 +307,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)
@@ -261,7 +324,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)
@@ -504,8 +567,8 @@ def insert_individual_relations_v2(c, ICO, conn, primary_sql_key, zapis_datum, v
 
 def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, adresa_id):
     try:
-        jmeno = str(get_prop(elem, "osoba/jmeno"))
-        prijmeni = str(get_prop(elem, "osoba/prijmeni"))
+        jmeno = lower_names_chars(str(get_prop(elem, "osoba/jmeno")))
+        prijmeni = lower_names_chars(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"))
@@ -515,6 +578,20 @@ def find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element, adre
     except:
         pass
 
+def lower_names_chars(string_name):
+    updated_name = ""
+    previous_non_alpha = True
+    for elem in string_name:
+        if previous_non_alpha == True:
+            updated_name += elem
+        else:
+            updated_name += elem.lower()
+        if elem.isalpha() == True:
+            previous_non_alpha = False
+        else:
+            previous_non_alpha = True
+    return updated_name
+
 def insert_fyzicka_osoba(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni, adresa_id):
     try:
         c.execute("INSERT into fyzicke_osoby (titul_pred, jmeno, prijmeni, titul_za, datum_naroz, adresa_id) VALUES (?,?,?,?,?,?)", (titulPred, jmeno, prijmeni, titulZa, datum_narozeni,adresa_id,))
@@ -570,6 +647,7 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element):
     except Exception as f:
         print(f)
 
+# TODO MERGE THESE THREE FUNCTIONS INTO ONE
 def insert_podily(c, elem, spolecnik_id):
     try:
         podil_iter = elem.findall("podudaje/Udaj")
@@ -587,6 +665,42 @@ 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 insert_common_podily(c, elem, common_id):
+    try:
+        podil_iter = elem.findall("podudaje/Udaj")
+        for podil_elem in podil_iter:
+            if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODIL":
+                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 (spolecny_podil_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (common_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"))