updating the ubo feature

10 files changed, 134 insertions, 36 deletions

diff --git a/db_creation.py b/db_creation.py
index 55e7f32..cf9d504 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -363,22 +363,22 @@ def create_tables(conn):
     ubo = """ CREATE TABLE "ubo" (
 	"id" INTEGER NOT NULL UNIQUE,
 	"company_id"  INTEGER NOT NULL, 
-	"UBO_id" INTEGER NOT NULL, 
+	"UBO_id" INTEGER NOT NULL,
+	"adresa_id" INTEGER,
 	"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,
+	"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)
 	); """
 
@@ -724,6 +724,9 @@ def create_indices(conn):
 	"UBO_id"
 ); """
 
+    ubo4 = """ CREATE INDEX "index ubo4" ON "ubo" (
+	"UBO_adresa_id"
+); """
 
     ucel1 = """ CREATE INDEX "index ucel1" ON "ucel" (
 	"ucel"
diff --git a/justice_build.py b/justice_build.py
index 9a48f55..380d32b 100644
--- a/justice_build.py
+++ b/justice_build.py
@@ -11,7 +11,7 @@ def main():
     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..deb9d51 100644
--- a/main.py
+++ b/main.py
@@ -1,7 +1,7 @@
 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
@@ -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 7263177..6c1b84c 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):
@@ -304,10 +305,14 @@ 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)
+    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")    
 
@@ -458,7 +463,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
 
diff --git a/templates/extract-actual.html b/templates/extract-actual.html
index a1f0241..6bc51fe 100644
--- a/templates/extract-actual.html
+++ b/templates/extract-actual.html
@@ -410,7 +410,10 @@
     {% 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>
+        <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 %}
diff --git a/templates/extract.html b/templates/extract.html
index 6ff31de..17bdd73 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -489,7 +489,9 @@
             {% 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 }} {{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 %}
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/update_db.py b/update_db.py
index 6c50b70..2f86c35 100644
--- a/update_db.py
+++ b/update_db.py
@@ -193,20 +193,18 @@ def find_UBO(c, ICO, elem2, conn, primary_sql_key, element):
         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"))
+            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, 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,))
+            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)