update search by legal persons

6 files changed, 121 insertions, 14 deletions

diff --git a/db_creation.py b/db_creation.py
index 9aeb780..a8fefe7 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -234,7 +234,9 @@ def create_tables(conn):
 	"ico"	INTEGER,
 	"reg_cislo"	INTEGER,
 	"nazev"	TEXT,
-	UNIQUE("ico","reg_cislo","nazev"),
+	"adresa_id" INTEGER,
+	UNIQUE("ico","reg_cislo","nazev","adresa_id"),
+	FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
 	PRIMARY KEY("id" AUTOINCREMENT)
 ); """
 
diff --git a/forms.py b/forms.py
index 56d9b65..5f67564 100644
--- a/forms.py
+++ b/forms.py
@@ -147,6 +147,23 @@ class EntitySearchForm(Form):
     entity_number_search = StringField(u'IČO nebo zahraniční registrační číslo:')
     entity_number_search_selection = SelectField('', choices=search_options)
 
+    # REMOVE DUPLICITY
+    obec_search = StringField(u'Obec:')
+    obec_search_selection = SelectField('', choices=search_options)
+    obec_search_actual = SelectField('', choices=actual_options) 
+    
+    ulice_search = StringField(u'Ulice:')
+    ulice_search_selection = SelectField('', choices=search_options)
+    ulice_search_actual = SelectField('', choices=actual_options) 
+
+    cp_search = StringField(u'Číslo popisné:')
+    cp_search_selection = SelectField('', choices=search_options)
+    cp_search_actual = SelectField('', choices=actual_options) 
+
+    co_search = StringField(u'Číslo orientační:')
+    co_search_selection = SelectField('', choices=search_options)
+    co_search_actual = SelectField('', choices=actual_options) 
+
     entity_actual_selection = SelectField('', choices=actual_options)                
 
 class CompanyForm(Form):
diff --git a/main.py b/main.py
index 6c4a2cf..cc512ea 100644
--- a/main.py
+++ b/main.py
@@ -142,6 +142,18 @@ def search_results_entity(search):
     entity_number = search.entity_number_search.data
     entity_number_search_method = search.entity_number_search_selection.data
 
+    obec = search.obec_search.data
+    obec_search_method = search.obec_search_selection.data
+    
+    ulice = search.ulice_search.data
+    ulice_search_method = search.ulice_search_selection.data
+
+    co = search.co_search.data
+    co_search_method = search.co_search_selection.data
+
+    cp = search.cp_search.data
+    cp_search_method = search.cp_search_selection.data
+
     actual_selection = search.entity_actual_selection.data
 
     qry = Pravnicka_Osoba.query
@@ -167,6 +179,42 @@ def search_results_entity(search):
         elif entity_name_search_method == "text_exact":
             qry = qry.filter(Pravnicka_Osoba.nazev == entity_name)
 
+    if obec:
+        qry = qry.join(Adresy_v2, Pravnicka_Osoba.adresa)
+        if obec_search_method == "text_anywhere":
+            qry = qry.filter(Adresy_v2.obec.contains(obec))
+        elif obec_search_method == "text_beginning":
+            qry = qry.filter(Adresy_v2.obec.like(f'{obec}%'))
+        elif obec_search_method == "text_exact":
+            qry = qry.filter(Adresy_v2.obec == obec)
+
+    if ulice:
+        qry = qry.join(Adresy_v2, Pravnicka_Osoba.adresa)
+        if ulice_search_method == "text_anywhere":
+            qry = qry.filter(Adresy_v2.ulice.contains(ulice))
+        elif ulice_search_method == "text_beginning":
+            qry = qry.filter(Adresy_v2.ulice.like(f'{ulice}%'))
+        elif ulice_search_method == "text_exact":
+            qry = qry.filter(Adresy_v2.ulice == ulice)
+
+    if cp:
+        qry = qry.join(Adresy_v2, Pravnicka_Osoba.adresa)
+        if cp_search_method == "text_anywhere":
+            qry = qry.filter(Adresy_v2.cisloPo.contains(cp))
+        elif cp_search_method == "text_beginning":
+            qry = qry.filter(Adresy_v2.cisloPo.like(f'{cp}%'))
+        elif cp_search_method == "text_exact":
+            qry = qry.filter(Adresy_v2.cisloPo == cp)
+
+    if co:
+        qry = qry.join(Adresy_v2, Pravnicka_Osoba.adresa)
+        if co_search_method == "text_anywhere":
+            qry = qry.filter(Adresy_v2.cisloOr.contains(co))
+        elif co_search_method == "text_beginning":
+            qry = qry.filter(Adresy_v2.cisloOr.like(f'{co}%'))
+        elif co_search_method == "text_exact":
+            qry = qry.filter(Adresy_v2.cisloOr == co)    
+
     results = qry.all()
     print(results)
 
diff --git a/models.py b/models.py
index eb427e9..1540aa9 100644
--- a/models.py
+++ b/models.py
@@ -228,6 +228,8 @@ class Pravnicka_Osoba(db.Model):
     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")
+    adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id'))
+    adresa = db.relationship("Adresy_v2")
 
 class Fyzicka_Osoba(db.Model):
     __tablename__ = "fyzicke_osoby"
@@ -341,7 +343,6 @@ class Podily_Association(db.Model):
     zapis_datum = db.Column(MyType)
     vymaz_datum = db.Column(MyType)
     druh_podilu_id = db.Column(db.Integer, db.ForeignKey('druhy_podilu.id'))
-    # vklad_typ = db.Column(db.String)
     vklad_typ = db.Column(MyCurrency)
     vklad_text = db.Column(db.String)
     souhrn_typ = db.Column(MyContribution)
diff --git a/templates/search_form_entity.html b/templates/search_form_entity.html
index 7d3d0ed..9491dba 100644
--- a/templates/search_form_entity.html
+++ b/templates/search_form_entity.html
@@ -40,6 +40,46 @@
         </div>
       </div>
 
+<!-- MUNICIPALITY -->
+    <div class="row g-2">
+      <div class="col-sm-3">
+        {{ form.obec_search.label }}
+      </div>
+      <div class="col-sm">
+        {{ form.obec_search()|safe }} {{ form.obec_search_selection }}
+      </div>
+    </div>
+
+<!-- STREET -->
+    <div class="row g-2">
+      <div class="col-sm-3">
+        {{ form.ulice_search.label }}
+      </div>
+      <div class="col-sm">
+        {{ form.ulice_search()|safe }} {{ form.ulice_search_selection }}
+      </div>
+    </div>
+
+<!-- DESCR. NO. -->
+    <div class="row g-2">
+      <div class="col-sm-3">
+        {{ form.cp_search.label }}
+      </div>
+      <div class="col-sm">
+        {{ form.cp_search()|safe }} {{ form.cp_search_selection }}
+      </div>
+    </div>
+
+<!-- OREINT. NO. -->
+    <div class="row g-2">
+      <div class="col-sm-3">
+        {{ form.co_search.label }}
+      </div>
+      <div class="col-sm">
+        {{ form.co_search()|safe }} {{ form.co_search_selection }}
+      </div>
+    </div>
+
 <!-- Search -->
       <div class="row g-2">
         <div class="col-sm-3">
diff --git a/update_db.py b/update_db.py
index e59831f..b237003 100644
--- a/update_db.py
+++ b/update_db.py
@@ -214,12 +214,11 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
                     c.execute("INSERT INTO spolecnici (company_id, spolecnik_fo_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, spolecnik_fo_id, zapis_datum, vymaz_datum, adresa_id, text_spolecnik,))
                     c.execute ("SELECT last_insert_rowid()")
                     spolecnik_id = c.fetchone()[0]
-                    # print(ICO, spolecnik_fo_id, adresa_id)
                 else:
                     spol_ico = str(get_prop(elem, "osoba/ico"))
                     regCislo = str(get_prop(elem, "osoba/regCislo"))
-                    spolecnik_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo)
                     adresa_id = find_sidlo(c, elem, primary_sql_key)
+                    spolecnik_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
                     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]
@@ -370,8 +369,8 @@ def find_sole_shareholder(c, ICO, elem2, conn, primary_sql_key, element):
             if typ_akcionar == "P":
                 spol_ico = str(get_prop(elem, "osoba/ico"))
                 regCislo = str(get_prop(elem, "osoba/regCislo"))
-                akcionar_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo)
                 adresa_id = find_sidlo(c, elem, primary_sql_key)
+                akcionar_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
                 c.execute("INSERT into jediny_akcionar (company_id, zapis_datum, vymaz_datum, text_akcionar, akcionar_po_id, adresa_id) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_akcionar, akcionar_po_id, adresa_id,))
             elif typ_akcionar == "F":
                 adresa_id = find_sidlo(c, elem, primary_sql_key)
@@ -502,8 +501,8 @@ def find_clen_statut_org(c, ICO, elem, conn, relationship_table_key, element):
         if typ_osoby == "AngazmaPravnicke":
             spol_ico = str(get_prop(elem, "osoba/ico"))
             regCislo = str(get_prop(elem, "osoba/regCislo"))
-            prav_osoba_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo)
             adresa_id = find_sidlo(c, elem, relationship_table_key)
+            prav_osoba_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
             c.execute("INSERT into statutarni_organ_clen_relation (statutarni_organ_id, prav_osoba_id, adresa_id, zapis_datum, vymaz_datum, funkce_od, funkce_do, clenstvi_od, clenstvi_do, funkce) VALUES (?,?,?,?,?,?,?,?,?,?)", (relationship_table_key, prav_osoba_id, adresa_id, zapis_datum, vymaz_datum, funkceOd, funkceDo, clenstviOd, clenstviDo, funkce_statutar_organu,))
     except Exception as f:
         print(f)
@@ -539,11 +538,11 @@ def find_osoba_id(c, titulPred, jmeno, prijmeni, titulZa, datum_narozeni, adresa
     except Exception as f:
         print(f) 
 
-def find_pravnicka_osoba(c, elem, spol_ico, regCislo):
+def find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id):
     try:
         nazev = str(get_prop(elem, "osoba/nazev"))
-        insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev)
-        osoba_id = find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev)
+        insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev, adresa_id)
+        osoba_id = find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev, adresa_id)
         return osoba_id
     except Exception as f:
         print(f)
@@ -574,8 +573,8 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element):
         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 = find_sidlo(c, elem, relationship_table_key)
+            pravnicka_osoba_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo, adresa_id)
             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)
@@ -620,17 +619,17 @@ def find_druh_podilu_id(c, druhPodilu):
     except Exception as f:
         print(f) 
 
-def find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev):
+def find_pravnicka_osoba_id(c, spol_ico, regCislo, nazev, adresa_id):
     try:
-        anciallary_table_key = c.execute("SELECT id FROM pravnicke_osoby WHERE ico = (?) and reg_cislo = (?) and nazev = (?)", (spol_ico, regCislo, nazev,))
+        anciallary_table_key = c.execute("SELECT id FROM pravnicke_osoby WHERE ico = (?) and reg_cislo = (?) and nazev = (?) and adresa_id = (?)", (spol_ico, regCislo, nazev, adresa_id))
         anciallary_table_key = c.fetchone()[0]
         return anciallary_table_key
     except Exception as f:
         print(f) 
 
-def insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev):
+def insert_pravnicka_osoba(c, elem, spol_ico, regCislo, nazev, adresa_id):
     try:
-        c.execute("INSERT into pravnicke_osoby (ico, reg_cislo, nazev) VALUES (?,?,?)", (spol_ico, regCislo, nazev,))
+        c.execute("INSERT into pravnicke_osoby (ico, reg_cislo, nazev, adresa_id) VALUES (?,?,?, ?)", (spol_ico, regCislo, nazev, adresa_id,))
     except:
         pass