further update search by legal persons in a role

7 files changed, 18 insertions, 8 deletions

diff --git a/db_creation.py b/db_creation.py
index 58ad840..a05b35e 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -84,7 +84,8 @@ def create_tables(conn):
     dr_organ_clen_relation = """ CREATE TABLE "dr_organ_clen_relation" (
 	"id"	INTEGER NOT NULL UNIQUE,
 	"dozorci_rada_id"	INTEGER NOT NULL,
-	"osoba_id"	INTEGER NOT NULL,
+	"osoba_id"	INTEGER,
+	"pravnicka_osoba_id" INTEGER,
 	"adresa_id"	INTEGER,
 	"zapis_datum"	DATE,
 	"vymaz_datum"	DATE,
diff --git a/models.py b/models.py
index ae45f0c..7a9160c 100644
--- a/models.py
+++ b/models.py
@@ -227,7 +227,7 @@ class Pravnicka_Osoba(db.Model):
     spolecnik_association = db.relationship("Spolecnici_Association")
     sole_shareholder_association = db.relationship("Jediny_Akcionar_Association")
     statut_org_association = db.relationship("Statutarni_Organ_Clen_Association")
-    # supervisory_board_member_association = db.relationship("Dozorci_Rada_Clen_Association")
+    supervisory_board_member_association = db.relationship("Dozorci_Rada_Clen_Association")
 
 class Fyzicka_Osoba(db.Model):
     __tablename__ = "fyzicke_osoby"
@@ -266,6 +266,7 @@ class Dozorci_Rada_Clen_Association(db.Model):
     id = db.Column(db.Integer, primary_key=True)
     dozorci_rada_id = db.Column(db.Integer, db.ForeignKey('dozorci_rada_relation.id'))
     osoba_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id'))
+    pravnicka_osoba_id = db.Column(db.Integer, db.ForeignKey('pravnicke_osoby.id'))
     adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id'))
     zapis_datum = db.Column(MyType)
     vymaz_datum = db.Column(MyType)
@@ -276,6 +277,7 @@ class Dozorci_Rada_Clen_Association(db.Model):
     funkce = db.Column(db.String)
     adresa = db.relationship("Adresy_v2")
     jmeno = db.relationship("Fyzicka_Osoba")
+    jmeno_po = db.relationship("Pravnicka_Osoba")
     dozorci_rada = db.relationship("Dozorci_Rada_Association")
 
 class Spolecnici_Association(db.Model):
diff --git a/templates/extract-actual.html b/templates/extract-actual.html
index 791b260..6bf6087 100644
--- a/templates/extract-actual.html
+++ b/templates/extract-actual.html
@@ -325,7 +325,8 @@
             {% if dozorci_rada_notes[i].clenove[j].vymaz_datum == 0 %}
             <tr>
                 <td style = padding-left:2em>{% if dozorci_rada_notes[i].clenove[j].funkce != "0" %} {{dozorci_rada_notes[i].clenove[j].funkce }} {% endif %}</td>     
-                <td>{{ dozorci_rada_notes[i].clenove[j].jmeno }}<br> 
+                <td>{% if dozorci_rada_notes[i].clenove[j].jmeno != None %}{{ dozorci_rada_notes[i].clenove[j].jmeno }}
+                    {% else %}{{ dozorci_rada_notes[i].clenove[j].jmeno_po.nazev}}{% if dozorci_rada_notes[i].clenove[j].jmeno_po.reg_cislo != 0 %}, reg č. {{ dozorci_rada_notes[i].clenove[j].jmeno_po.reg_cislo }}{% endif %}{% if dozorci_rada_notes[i].clenove[j].jmeno_po.ico != 0 %}, IČ <a href="/{{ dozorci_rada_notes[i].clenove[j].jmeno_po.ico }}">{{ dozorci_rada_notes[i].clenove[j].jmeno_po.ico }}</a>{% endif %} {% endif %}<br> 
                     {{ dozorci_rada_notes[i].clenove[j].adresa }} 
                     {% if dozorci_rada_notes[i].clenove[j].funkce_od != 0 %}<br>Den vzniku funkce: {{ dozorci_rada_notes[i].clenove[j].funkce_od}}{% endif %}
                     {% if dozorci_rada_notes[i].clenove[j].clenstvi_od != 0 %}<br>Den vzniku členství: {{ dozorci_rada_notes[i].clenove[j].clenstvi_od}}{% endif %}</td>
diff --git a/templates/extract.html b/templates/extract.html
index 034bdb2..3600cd5 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -341,7 +341,8 @@
             {% set underlne_style_close = "" %}
         {% endif %}          
         <td>{{ underlne_style_open|safe }}
-            {{ row.dozorci_rada_text[i].clenove[j].jmeno }}<br> 
+            {% if row.dozorci_rada_text[i].clenove[j].jmeno != None %}{{ row.dozorci_rada_text[i].clenove[j].jmeno }}
+            {% else %}{{ row.dozorci_rada_text[i].clenove[j].jmeno_po.nazev}}{% if row.dozorci_rada_text[i].clenove[j].jmeno_po.reg_cislo != 0 %}, reg č. {{ row.dozorci_rada_text[i].clenove[j].jmeno_po.reg_cislo }}{% endif %}{% if row.dozorci_rada_text[i].clenove[j].jmeno_po.ico != 0 %}, IČ <a href="/{{ row.dozorci_rada_text[i].clenove[j].jmeno_po.ico }}">{{ row.dozorci_rada_text[i].clenove[j].jmeno_po.ico }}</a>{% endif %} {% endif %}<br>
             {{ row.dozorci_rada_text[i].clenove[j].adresa }} 
             {% if row.dozorci_rada_text[i].clenove[j].funkce_od != 0 %}<br>Den vzniku funkce: {{ row.dozorci_rada_text[i].clenove[j].funkce_od}}{% endif %}
             {% if row.dozorci_rada_text[i].clenove[j].funkce_do != 0 %}<br>Den zániku funkce: {{ row.dozorci_rada_text[i].clenove[j].funkce_do}}{% endif %}
diff --git a/templates/results_entities.html b/templates/results_entities.html
index 3b87fc5..b45b65f 100644
--- a/templates/results_entities.html
+++ b/templates/results_entities.html
@@ -59,8 +59,7 @@
         {% endif %}
     {% endfor %}
 
-    <!-- Insert once data on supervisory board members (natural persons) are available -->
-    <!-- {% for elem in row.supervisory_board_member_association %}
+    {% for elem in row.supervisory_board_member_association %}
         {% if selection_method == "actual_results" and elem.vymaz_datum != 0 %}
             {% set xxx = [] %}
         {% else %}    
@@ -75,7 +74,7 @@
             {% set x = my_dict.__setitem__("zapis", elem.dozorci_rada.company.zapis) %}
             {% set test_list = test_list.append(my_dict) %}
         {% endif %}
-    {% endfor %}     -->
+    {% endfor %}
 
     {% for elem in test_list %}
         {% if count.value % 2 == 0 %}
diff --git a/todolist.html b/todolist.html
index 772d8e6..c86b17f 100644
--- a/todolist.html
+++ b/todolist.html
@@ -1,7 +1,7 @@
 <ol>
-    <li>Add a feature to display legal entities as members of corporate bodies.</li>
     <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 90e66cc..2d68617 100644
--- a/update_db.py
+++ b/update_db.py
@@ -574,6 +574,12 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element):
             # adresa_id = find_and_store_address(c, elem)
             adresa_id = sidlo3(c, elem, relationship_table_key)
             c.execute("INSERT into dr_organ_clen_relation (dozorci_rada_id, osoba_id, adresa_id, zapis_datum, vymaz_datum, funkce_od, funkce_do, clenstvi_od, clenstvi_do, funkce) VALUES (?,?,?,?,?,?,?,?,?,?)", (relationship_table_key, osoba_id, adresa_id, zapis_datum, vymaz_datum, funkceOd, funkceDo, clenstviOd, clenstviDo, funkce_statutar_organu,))
+        elif typ_osoby == "AngazmaPravnicke":
+            spol_ico = str(get_prop(elem, "osoba/ico"))
+            regCislo = str(get_prop(elem, "osoba/regCislo"))
+            pravnicka_osoba_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo)
+            adresa_id = sidlo3(c, elem, relationship_table_key)
+            c.execute("INSERT into dr_organ_clen_relation (dozorci_rada_id, pravnicka_osoba_id, adresa_id, zapis_datum, vymaz_datum, funkce_od, funkce_do, clenstvi_od, clenstvi_do, funkce) VALUES (?,?,?,?,?,?,?,?,?,?)", (relationship_table_key, pravnicka_osoba_id, adresa_id, zapis_datum, vymaz_datum, funkceOd, funkceDo, clenstviOd, clenstviDo, funkce_statutar_organu,))
     except Exception as f:
         print(f)