From c7d33751e951f9cc4dfd90467854cc6e20cc341b Mon Sep 17 00:00:00 2001 From: Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com> Date: Fri, 7 May 2021 23:21:08 +0200 Subject: expand search options for natural persons --- db_creation.py | 4 ++- forms.py | 20 ++++++++++++ justice_build.py | 8 +++-- main.py | 69 +++++++++++++++++++++++++++------------ models.py | 3 +- templates/extract.html | 6 ++-- templates/search_form_person.html | 41 +++++++++++++++++++++++ update_db.py | 27 +++++++-------- 8 files changed, 135 insertions(+), 43 deletions(-) diff --git a/db_creation.py b/db_creation.py index c1f0cc1..9aeb780 100644 --- a/db_creation.py +++ b/db_creation.py @@ -113,7 +113,9 @@ def create_tables(conn): "prijmeni" TEXT, "titul_za" TEXT, "datum_naroz" TEXT, - UNIQUE("titul_pred","jmeno","prijmeni","titul_za","datum_naroz"), + "adresa_id" INTEGER, + UNIQUE("titul_pred","jmeno","prijmeni","titul_za","datum_naroz","adresa_id"), + FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"), PRIMARY KEY("id" AUTOINCREMENT) ); """ diff --git a/forms.py b/forms.py index 73e576a..56d9b65 100644 --- a/forms.py +++ b/forms.py @@ -9,6 +9,8 @@ Created on Sun Jan 17 10:05:34 2021 from wtforms import Form, StringField, SelectField, BooleanField from wtforms.fields.html5 import DateField +# CREATE A GENERAL CLASS TO REMOVE DUPLICITIES? + class JusticeSearchForm(Form): search_options = [("text_anywhere","Kedkoliv v textu"), ("text_beginning","Začátek výrazu"), @@ -107,6 +109,24 @@ class PersonSearchForm(Form): surname_search_selection = SelectField('', choices=search_options) surname_search_actual = SelectField('', choices=actual_options) + # Delete the 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) + person_actual_selection = SelectField('', choices=actual_options) birthday = DateField(u'Datum narození:', format='%Y-%m-%d') diff --git a/justice_build.py b/justice_build.py index eebca0c..018e7cb 100644 --- a/justice_build.py +++ b/justice_build.py @@ -3,9 +3,11 @@ from download_files import download_data from update_db import update_DB def main(): - typy_po = ["as", "sro", "vos", "ks", "dr", "zajzdrpo", "zahrfos", "ustav", "svj", "spolek", "prisp", "pobspolek", - "oszpo", "osznadf", "osznad", "orgzam", "odbororg", "nadf", "nad", "evrspol", "evrhzs", "evrdrspol"] - soudy = ["praha", "plzen", "brno", "ceske_budejovice", "hradec_kralove", "ostrava", "usti_nad_labem"] + # typy_po = ["as", "sro", "vos", "ks", "dr", "zajzdrpo", "zahrfos", "ustav", "svj", "spolek", "prisp", "pobspolek", + # "oszpo", "osznadf", "osznad", "orgzam", "odbororg", "nadf", "nad", "evrspol", "evrhzs", "evrdrspol"] + # soudy = ["praha", "plzen", "brno", "ceske_budejovice", "hradec_kralove", "ostrava", "usti_nad_labem"] + typy_po = ["as"] + soudy = ["ceske_budejovice"] DB_name = "justice.db" create_DB(DB_name) diff --git a/main.py b/main.py index 608e50f..6c4a2cf 100644 --- a/main.py +++ b/main.py @@ -51,6 +51,18 @@ def search_results_person(search): birthday = search.birthday.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.person_actual_selection.data qry = Fyzicka_Osoba.query @@ -70,7 +82,43 @@ def search_results_person(search): qry = qry.filter(Fyzicka_Osoba.prijmeni.like(f'{surname}%')) elif surname_search_method == "text_exact": qry = qry.filter(Fyzicka_Osoba.prijmeni == surname) - + + if obec: + qry = qry.join(Adresy_v2, Fyzicka_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, Fyzicka_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, Fyzicka_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, Fyzicka_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) + if birthday: qry = qry.filter(Fyzicka_Osoba.datum_naroz == birthday) @@ -94,9 +142,6 @@ def search_results_entity(search): entity_number = search.entity_number_search.data entity_number_search_method = search.entity_number_search_selection.data - # foreign_entity_number = search.foreign_entity_number_search.data - # foreign_entity_number_search_method = search.foreign_entity_number_search_selection.data - actual_selection = search.entity_actual_selection.data qry = Pravnicka_Osoba.query @@ -114,22 +159,6 @@ def search_results_entity(search): qry1 = qry1.filter(Pravnicka_Osoba.reg_cislo == entity_number) qry = qry.union(qry1) - # if entity_number: - # if entity_number_search_method == "text_anywhere": - # qry = qry.filter(Pravnicka_Osoba.ico.contains(entity_number)) - # elif entity_number_search_method == "text_beginning": - # qry = qry.filter(Pravnicka_Osoba.ico.like(f'{entity_number}%')) - # elif entity_number_search_method == "text_exact": - # qry = qry.filter(Pravnicka_Osoba.ico == entity_number) - - # if foreign_entity_number: - # if foreign_entity_number_search_method == "text_anywhere": - # qry = qry.filter(Pravnicka_Osoba.reg_cislo.contains(foreign_entity_number)) - # elif foreign_entity_number_search_method == "text_beginning": - # qry = qry.filter(Pravnicka_Osoba.reg_cislo.like(f'{foreign_entity_number}%')) - # elif foreign_entity_number_search_method == "text_exact": - # qry = qry.filter(Pravnicka_Osoba.reg_cislo == foreign_entity_number) - if entity_name: if entity_name_search_method == "text_anywhere": qry = qry.filter(Pravnicka_Osoba.nazev.contains(entity_name)) diff --git a/models.py b/models.py index f1f93aa..eb427e9 100644 --- a/models.py +++ b/models.py @@ -237,11 +237,13 @@ class Fyzicka_Osoba(db.Model): prijmeni = db.Column(db.String) titul_za = db.Column(db.String) datum_naroz = db.Column(MyType) + adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id')) statut_org_association = db.relationship("Statutarni_Organ_Clen_Association") spolecnik_association = db.relationship("Spolecnici_Association") prokurista_association = db.relationship("Prokurista_Association") sole_shareholder_association = db.relationship("Jediny_Akcionar_Association") supervisory_board_member_association = db.relationship("Dozorci_Rada_Clen_Association") + adresa = db.relationship("Adresy_v2") def get_name(self): joined_name = "" @@ -468,7 +470,6 @@ class Zakladni_Kapital(db.Model): splaceni_typ = db.Column(db.String) splaceni_hodnota = db.Column(db.String) - # def __repr__(self): def my_rep(self): joined_zk = "" joined_zk += self.vklad_hodnota + " " diff --git a/templates/extract.html b/templates/extract.html index 3600cd5..0c4efd0 100644 --- a/templates/extract.html +++ b/templates/extract.html @@ -258,7 +258,7 @@