diff options
| author | Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com> | 2021-05-02 00:36:28 +0200 |
|---|---|---|
| committer | Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com> | 2021-05-02 00:36:28 +0200 |
| commit | 8e88b741af93bfed8e218306b584c8783e7830b0 (patch) | |
| tree | 73421022d48d08b992f6241e79a2651f3ba06f97 | |
| parent | e6ab38a4d9ca3744f39ef15789878a07fcc5effb (diff) | |
| parent | 8ad1f0d450c5ec9cfd7a4a9d0dc19c7a216d8c97 (diff) | |
| download | justice-8e88b741af93bfed8e218306b584c8783e7830b0.tar.gz | |
Merge branch 'main' of https://github.com/SveterCZE/justice
| -rw-r--r-- | README.md | 133 | ||||
| -rw-r--r-- | app.py | 3 | ||||
| -rw-r--r-- | db_creation.py | 83 | ||||
| -rw-r--r-- | db_setup.py | 2 | ||||
| -rw-r--r-- | forms.py | 48 | ||||
| -rw-r--r-- | justice_build.py (renamed from justice_main.py) | 4 | ||||
| -rw-r--r-- | main.py | 272 | ||||
| -rw-r--r-- | models.py | 237 | ||||
| -rw-r--r-- | templates/extract-actual.html | 92 | ||||
| -rw-r--r-- | templates/extract.html | 118 | ||||
| -rw-r--r-- | templates/header.html | 10 | ||||
| -rw-r--r-- | templates/most_common_activity.html | 24 | ||||
| -rw-r--r-- | templates/most_common_addresses.html | 23 | ||||
| -rw-r--r-- | templates/most_common_business.html | 24 | ||||
| -rw-r--r-- | templates/most_common_degree.html | 28 | ||||
| -rw-r--r-- | templates/most_common_purpose.html | 24 | ||||
| -rw-r--r-- | templates/oldest_companies.html | 23 | ||||
| -rw-r--r-- | templates/results2.html | 10 | ||||
| -rw-r--r-- | templates/results_entities.html | 128 | ||||
| -rw-r--r-- | templates/results_persons.html | 145 | ||||
| -rw-r--r-- | templates/search_form.html | 4 | ||||
| -rw-r--r-- | templates/search_form_entity.html | 68 | ||||
| -rw-r--r-- | templates/search_form_person.html | 69 | ||||
| -rw-r--r-- | templates/trivia.html | 14 | ||||
| -rw-r--r-- | todolist.html | 3 | ||||
| -rw-r--r-- | update_db.py | 50 |
26 files changed, 1397 insertions, 242 deletions
@@ -1,4 +1,131 @@ -# justice -A tool to download data from justice.cz and convert them into an sqlite3 database. +<h1>Introduction:</h1> +My first larger project, an application using Pyhon, Flask and SQLite to download open commercial register data from the Czech Ministry of Justice (justice.cz) and convert them into an sqlite3 database. The applicaton also contains a front-end component to search in the database by various properties. The main goal of the application is to provide users with more advanced search options compared to the official search at the Ministry of Justice website. -Detailed description of the features to be added later. +<h1>Installation:</h1> + +<h1>Running the application:</h1> +Firstly, you need to download the open data from the Ministry of Justice and build the database. Run "python justice_build.py", which will download all the source files, create an SQLite database and parse the downloaded data into the database. Note that after the downloaded data are uncompressed, they are around 13 GB and the database file has around 3.6 GB. It takes approximately 1 hour to download the data and build the database. + +Once the database is built, run "python main.py" and open http://127.0.0.1:5000 You will be greeted with a search form that is described below. + +<h1>Features:</h1> +<h2>Search by company properties:</h2> +You will be greeted with the following search form. + + + +The form allows you to search for companies registered in the Commercial Register by the following properties: +<ul> + <li>Company name</li> + <li>Identification number</li> + <li>Municipality</li> + <li>Street name</li> + <li>Court register section (<i>oddíl</i>)</li> + <li>Court register file No. (<i>vložka</i>)</li> + <li>Legal form</li> + <li>Court of registration</li> + <li>Active insolvency record</li> + <li>Date of registration</li> +</ul> + +In the fields where you fill-in input data, you can also select one of the following options: +<ul> + <li>Your input can be anywere in the result. For example, if you input <i>Trade</i> as a company name, the application will find and return both <i>SILOMAN Trade a.s.</i> and <i>Trade Bridge a.s.</i> </li> + <li>The result must start with your input. For example, if you input <i>Trade</i> as a company name, the application will find and reutrn only <i>Trade Bridge a.s.</i>, but not <i>SILOMAN Trade a.s.</i></li> + <li>The result and your input must match. For example, if you input <i>Trade</i> as a company name, the application will neither <i>SILOMAN Trade a.s.</i> nor <i>Trade Bridge a.s.</i></li> +</ul> + +In addition, you can also select one of the following options: +<ul> + <li>Your input must match the company data that are up-to-date. For example, if you input <i>Trade</i> as a company name, a company that used to be names <i>Global Trade, a.s.</i>, but had since been renamed, will not be found.</li> + <li>Your input may match the company data that are no longer up-to-date. For example, if you input <i>Trade</i> as a company name, a company that used to be names <i>Global Trade, a.s.</i>, but had since been renamed, will be found.</li> +</ul> + +<h2>Search by natural persons:</h2> +You will be greeted with the following search form: + + + +The form allows you to search for natural persons registered in the Commercial Register as members of executive bodies, members of supervisory boards, prokurists, shareholders in a limited liability companies, sole shareholders in a joint stock companies. The form allows you to search by the following properties: +<ul> + <li>First name</li> + <li>Surname</li> + <li>Date of birth</li> +</ul> + +In the fields where you fill-in input data, you can also select one of the following options: +<ul> + <li>Your input can be anywere in the result. For example, if you input <i>Nová</i> as a surname, the application will find and return both <i>Novák</i> and <i>Kuhnová</i> </li> + <li>The result must start with your input. For example, if you input <i>Nová</i> as a surname, the application will find and reutrn only <i>Novák</i>, but not <i>Kuhnová</i></li> + <li>The result and your input must match. For example, if you input <i>Nová</i> as a surname, the application will neither <i>Novák</i> nor <i>Kuhnová</i></li> +</ul> + +In addition, you can also select whether the application shall return only the persons who are still active in the company or whether it shall reutrn also those that are no longer active. + +<h2>Search by natural persons:</h2> +You will be greeted with the following search form: + + + +The form allows you to search for legal persons registered in the Commercial Register as members of executive bodies, members of supervisory boards, shareholders in a limited liability companies, sole shareholders in a joint stock companies. The form allows you to search by the following properties: +<ul> + <li>Company name</li> + <li>Identification No. (for Czech companies)</li> + <li>Foreign registration No. (for foreign copmpanies)</li> +</ul> + +In the fields where you fill-in input data, you can also select one of the following options: +<ul> + <li>Your input can be anywere in the result. For example, if you input <i>Trade</i> as a company name, the application will find and return both <i>SILOMAN Trade a.s.</i> and <i>Trade Bridge a.s.</i> </li> + <li>The result must start with your input. For example, if you input <i>Trade</i> as a company name, the application will find and reutrn only <i>Trade Bridge a.s.</i>, but not <i>SILOMAN Trade a.s.</i></li> + <li>The result and your input must match. For example, if you input <i>Trade</i> as a company name, the application will neither <i>SILOMAN Trade a.s.</i> nor <i>Trade Bridge a.s.</i></li> +</ul> + +In addition, you can also select whether the application shall return only the legal persons who are still active in the company or whether it shall reutrn also those that are no longer active. + +<h2>Display search results:</h2> +Once you hit the search button, you the results will be displayed to you. Several examples are displayed below. + +A stadard result when you search by company properties. + + + +If a company has an active insolvency record, it will be highlighted in red and the relevant insovlency notes will be added. + + + +A standard result when you search for a natural person active in a company. It firstly displays the natural person's identification details and then the identification details of the company in which such person is or was active. + + + +A standard result when you search for a legal person active in a company. It firstly displays the legal person's identification details and then the identification details of the company in which such person is or was active. + + + +<h2>Display detailed company data:</h2> +The application also allows you to display detailed data about each company in two forms: + +<ul> +<li>Historical extract, showing all data, including those that are no longer relevant</li> +<li>Current extract, showing only the up-to-date data</li> +</ul> + +A historical extract can look something like this. The data that are no longer up-to-date are underlined. + + + +A current extract looks like this. + + +<h2>Trivia:</h2> +You can also explore some interesting information about the data stored in the Commercial register. You can find, for example, a list of the oldest existing companies or addresses at which the most companies have their registered office. Go and check it yourselves :) + +<h1>Known issues:</h1> +This is an early prototype not yet suitable for production deployment as there are multiple issues that need to be resolved. The main issues are as follows: +<ul> +<li>Czech diacritics are not processed properly and based on my initial research, I suspect that this is due to SQLite limitations.</li> +<li>Not all information available in the open data sets are stored in the application database.</li> +<li>The underlying code should be refactored to remove excessive duplications.</li> +<li>I do not know how to deploy such application :)</li> + +</ul> @@ -10,11 +10,12 @@ Created on Sun Jan 17 09:38:19 2021 from flask import Flask from flask_debugtoolbar import DebugToolbarExtension from flask_sqlalchemy import SQLAlchemy +from key_gen import get_key app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///justice.db' app.config["SQLALCHEMY_ECHO"] = True -app.secret_key = "123456" app.debug = True +app.secret_key = get_key toolbar = DebugToolbarExtension(app) db = SQLAlchemy(app)
\ No newline at end of file diff --git a/db_creation.py b/db_creation.py index a704a85..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, @@ -323,6 +324,7 @@ def create_tables(conn): "id" INTEGER NOT NULL UNIQUE, "statutarni_organ_id" INTEGER NOT NULL, "osoba_id" INTEGER, + "prav_osoba_id" INTEGER, "adresa_id" INTEGER, "zapis_datum" DATE, "vymaz_datum" DATE, @@ -354,6 +356,23 @@ def create_tables(conn): PRIMARY KEY("id" AUTOINCREMENT) ); """ + ucel = """ CREATE TABLE "ucel" ( + "id" INTEGER NOT NULL, + "ucel" TEXT NOT NULL UNIQUE, + PRIMARY KEY("id" AUTOINCREMENT) +); """ + + ucel_relation = """ CREATE TABLE "ucel_relation" ( + "id" INTEGER NOT NULL UNIQUE, + "company_id" INTEGER NOT NULL, + "ucel_id" INTEGER NOT NULL, + "zapis_datum" DATE, + "vymaz_datum" DATE, + PRIMARY KEY("id" AUTOINCREMENT), + FOREIGN KEY("company_id") REFERENCES "companies"("id"), + FOREIGN KEY("ucel_id") REFERENCES "ucel"("id") +); """ + zakladni_kapital = """ CREATE TABLE "zakladni_kapital" ( "id" INTEGER NOT NULL UNIQUE, "company_id" INTEGER NOT NULL, @@ -400,7 +419,7 @@ def create_tables(conn): jediny_akcionar, konkurz_events, nazvy, ostatni_skutecnosti, pocty_clenu_DR, pocty_clenu_organu, podily, 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, - zakladni_kapital, zapis_soudy, zpusoby_jednani, zpusoby_jednani_relation] + ucel, ucel_relation, zakladni_kapital, zapis_soudy, zpusoby_jednani, zpusoby_jednani_relation] for elem in list_of_tables: try: c = conn.cursor() @@ -537,6 +556,22 @@ def create_indices(conn): "id" ); """ + pravnicke_osoby1 = """ CREATE INDEX "pravnicke_osoby1" ON "pravnicke_osoby" ( + "ico" +); """ + + pravnicke_osoby2 = """ CREATE INDEX "pravnicke_osoby2" ON "pravnicke_osoby" ( + "id" +); """ + + pravnicke_osoby3 = """ CREATE INDEX "pravnicke_osoby3" ON "pravnicke_osoby" ( + "reg_cislo" +); """ + + pravnicke_osoby4 = """ CREATE INDEX "pravnicke_osoby4" ON "pravnicke_osoby" ( + "nazev" +); """ + predmety_cinnosti_relation1 = """ CREATE INDEX "index predmety cinnosti relation1" ON "predmety_cinnosti_relation" ( "company_id" ); """ @@ -650,6 +685,26 @@ def create_indices(conn): "company_id" ); """ + ucel1 = """ CREATE INDEX "index ucel1" ON "ucel" ( + "ucel" +); """ + + ucel2 = """ CREATE INDEX "index ucel2" ON "predmety_podnikani" ( + "id" +); """ + + ucel_relation1 = """ CREATE INDEX "index ucel relation1" ON "ucel_relation" ( + "company_id" +); """ + + ucel_relation2 = """ CREATE INDEX "index ucel relation2" ON "ucel_relation" ( + "id" +); """ + + ucel_relation3 = """ CREATE INDEX "index ucel relation3" ON "ucel_relation" ( + "ucel_id" +); """ + zakladni_kapital1 = """ CREATE INDEX "index zakladni kapital1" ON "zakladni_kapital" ( "company_id" ); """ @@ -674,36 +729,32 @@ def create_indices(conn): "zpusob_jednani_id" ); """ - pravnicke_osoby1 = """ CREATE INDEX "pravnicke_osoby1" ON "pravnicke_osoby" ( - "ico" -); """ - - pravnicke_osoby2 = """ CREATE INDEX "pravnicke_osoby2" ON "pravnicke_osoby" ( + fyzicke_osoby1 = """ CREATE INDEX "index fyzicke_osoby1" ON "fyzicke_osoby" ( "id" ); """ - pravnicke_osoby3 = """ CREATE INDEX "pravnicke_osoby3" ON "pravnicke_osoby" ( - "reg_cislo" + statutarni_organy_relation_4 = """ CREATE INDEX "index statutarni organ relation 4" ON "statutarni_organ_clen_relation" ( + "osoba_id" ); """ - pravnicke_osoby4 = """ CREATE INDEX "pravnicke_osoby4" ON "pravnicke_osoby" ( - "nazev" + dr_relation_3 = """ CREATE INDEX "index dr clen relation3" ON "dr_organ_clen_relation" ( + "osoba_id" ); """ - fyzicke_osoby1 = """ CREATE INDEX "fyzicke_osoby1" ON "fyzicke_osoby" ( - "id" + akcionari3 = """ CREATE INDEX "index akcionari3" ON "jediny_akcionar" ( + "akcionar_fo_id" ); """ list_of_indices = [companies1, companies2, companies3, companies4, companies5, adresy1, adresy2, adresy3, - akcie, akcie2, akcionari1, akcionari2, dr_clen_relation1, dr_clen_relation2, dr_relation, dr_relation2, + akcie, akcie2, akcionari1, akcionari2, akcionari3, dr_clen_relation1, dr_clen_relation2, dr_relation, dr_relation2, dr_relation_3, 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, 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, - statutarni_organy_relation_3, zakladni_kapital1, zakladni_kapital2, zpusob_jednani, zpusob_jednani_relation1, zpusob_jednani_relation2, - zpusob_jednani_relation3, pravnicke_osoby1, pravnicke_osoby2, pravnicke_osoby3, pravnicke_osoby4, fyzicke_osoby1] + 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, fyzicke_osoby1, ucel1, ucel2, ucel_relation1, ucel_relation2, ucel_relation3] i = 0 for elem in list_of_indices: i += 1 diff --git a/db_setup.py b/db_setup.py index 2944ef0..24e44de 100644 --- a/db_setup.py +++ b/db_setup.py @@ -10,7 +10,7 @@ from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base -engine = create_engine('sqlite:///justice2.db', convert_unicode=True) +engine = create_engine('sqlite:///justice.db', convert_unicode=True) db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) @@ -9,12 +9,6 @@ Created on Sun Jan 17 10:05:34 2021 from wtforms import Form, StringField, SelectField, BooleanField from wtforms.fields.html5 import DateField -# class JusticeSearchForm(Form): -# choices = [('ico', 'Identifikační číslo'), -# ('nazev', 'Obchodní firma'),] -# select = SelectField('Search for company:', choices=choices) -# search = StringField('') - class JusticeSearchForm(Form): search_options = [("text_anywhere","Kedkoliv v textu"), ("text_beginning","Začátek výrazu"), @@ -87,7 +81,49 @@ class JusticeSearchForm(Form): insolvent_only_search = BooleanField("Pouze společnosti s insolvenčním zápisem") zapis_do = DateField(u'Zapsáno do:', format='%Y-%m-%d') zapis_od = DateField(u'Zapsáno od:', format='%Y-%m-%d') + +class PersonSearchForm(Form): + # VYMAZAT DUPLICITU + search_options = [("text_anywhere","Kedkoliv v textu"), + ("text_beginning","Začátek výrazu"), + ("text_exact","Přesný výraz"), + ] + actual_options = [("actual_results","Jen platné"), + ("complete_results","Platné i neplatné"),] + + fist_name_search = StringField(u'Jméno:') + fist_name_search_selection = SelectField('', choices=search_options) + fist_name_search_actual = SelectField('', choices=actual_options) + + surname_search = StringField(u'Příjmení:') + surname_search_selection = SelectField('', choices=search_options) + surname_search_actual = SelectField('', choices=actual_options) + + person_actual_selection = SelectField('', choices=actual_options) + + birthday = DateField(u'Datum narození:', format='%Y-%m-%d') + +class EntitySearchForm(Form): + # VYMAZAT DUPLICITU + search_options = [("text_anywhere","Kedkoliv v textu"), + ("text_beginning","Začátek výrazu"), + ("text_exact","Přesný výraz"), + ] + actual_options = [("actual_results","Jen platné"), + ("complete_results","Platné i neplatné"),] + + entity_name_search = StringField(u'Název:') + entity_name_search_selection = SelectField('', choices=search_options) + entity_name_search_actual = SelectField('', choices=actual_options) + entity_number_search = StringField(u'Identifikační číslo:') + entity_number_search_selection = SelectField('', choices=search_options) + + foreign_entity_number_search = StringField(u'Zahraniční registrační číslo:') + foreign_entity_number_search_selection = SelectField('', choices=search_options) + + entity_actual_selection = SelectField('', choices=actual_options) + class CompanyForm(Form): oddil = [('A', 'A'), ('B', 'B'), diff --git a/justice_main.py b/justice_build.py index 348ee59..eebca0c 100644 --- a/justice_main.py +++ b/justice_build.py @@ -6,9 +6,9 @@ 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"]
+
DB_name = "justice.db"
create_DB(DB_name)
download_data(typy_po, soudy)
update_DB(typy_po, soudy, DB_name)
-
-main()
\ No newline at end of file +main()
@@ -1,15 +1,18 @@ from app import app from db_setup import init_db, db_session -from forms import JusticeSearchForm, CompanyForm +from forms import 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 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, Jediny_Akcionar_Association, Prokura_Common_Text_Association, Soudni_Zapisy +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 tables import Results +from sqlalchemy.sql import select +from sqlalchemy.sql import text +from sqlalchemy import create_engine -init_db() +# init_db() @app.route('/', methods=['GET', 'POST']) def index(): @@ -18,7 +21,116 @@ def index(): if request.method == 'POST': return search_results(search) - return render_template('index.html', form=search) + return render_template('search_form.html', form=search) + +@app.route('/osoby', methods=['GET', 'POST']) +def search_person(): + search = PersonSearchForm(request.form) + print(search) + if request.method == 'POST': + return search_results_person(search) + + return render_template('search_form_person.html', form=search) + +@app.route('/entity', methods=['GET', 'POST']) +def search_entity(): + search = EntitySearchForm(request.form) + print(search) + if request.method == 'POST': + return search_results_entity(search) + + return render_template('search_form_entity.html', form=search) + + +@app.route('/results_person') +def search_results_person(search): + result = [] + + first_name = search.fist_name_search.data + first_name_search_method = search.fist_name_search_selection.data + + surname = search.surname_search.data + surname_search_method = search.surname_search_selection.data + + birthday = search.birthday.data + + actual_selection = search.person_actual_selection.data + + qry = Fyzicka_Osoba.query + + if first_name: + if first_name_search_method == "text_anywhere": + qry = qry.filter(Fyzicka_Osoba.jmeno.contains(first_name)) + elif first_name_search_method == "text_beginning": + qry = qry.filter(Fyzicka_Osoba.jmeno.like(f'{first_name}%')) + elif first_name_search_method == "text_exact": + qry = qry.filter(Fyzicka_Osoba.jmeno == first_name) + + if surname: + if surname_search_method == "text_anywhere": + qry = qry.filter(Fyzicka_Osoba.prijmeni.contains(surname)) + elif surname_search_method == "text_beginning": + qry = qry.filter(Fyzicka_Osoba.prijmeni.like(f'{surname}%')) + elif surname_search_method == "text_exact": + qry = qry.filter(Fyzicka_Osoba.prijmeni == surname) + + if birthday: + qry = qry.filter(Fyzicka_Osoba.datum_naroz == birthday) + + results = qry.all() + print(results) + + if not results: + flash('No results found!') + return redirect('/osoby') + + else: + table = Results(results) + table.border = True + return render_template("results_persons.html", results=results, form=search, show_form = True, selection_method = actual_selection) + +@app.route('/results_entity') +def search_results_entity(search): + entity_name = search.entity_name_search.data + entity_name_search_method = search.entity_name_search_selection.data + entity_name_actual_or_full = search.entity_name_search_actual.data + + entity_number = search.entity_number_search.data + entity_number_search_method = search.entity_number_search_selection.data + entity_number_actual_or_full = search.entity_name_search_actual.data + + actual_selection = search.entity_actual_selection.data + + qry = Pravnicka_Osoba.query + + 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 entity_name: + if entity_name_search_method == "text_anywhere": + qry = qry.filter(Pravnicka_Osoba.nazev.contains(entity_name)) + elif entity_name_search_method == "text_beginning": + qry = qry.filter(Pravnicka_Osoba.nazev.like(f'{entity_name}%')) + elif entity_name_search_method == "text_exact": + qry = qry.filter(Pravnicka_Osoba.nazev == entity_name) + + results = qry.all() + print(results) + + if not results: + flash('No results found!') + return redirect('/entity') + + else: + table = Results(results) + table.border = True + return render_template("results_entities.html", results=results, form=search, show_form = True, selection_method = actual_selection) + @app.route('/results') def search_results(search): @@ -160,7 +272,30 @@ def search_results(search): else: table = Results(results) table.border = True - return render_template("results2.html", results=results, form=search, zapsano_od=zapsano_od, zapsano_do=zapsano_do) + return render_template("results2.html", results=results, form=search, zapsano_od=zapsano_od, zapsano_do=zapsano_do, show_form = True) + + + +@app.route('/results-sidlo-<int:adresa_id>', methods=['GET', 'POST']) +def search_results_sidlo(adresa_id): + search = JusticeSearchForm(request.form) + + results = [] + qry = Company.query + qry = qry.join(Sidlo_Association, Company.sidlo_text) + qry = qry.filter(Sidlo_Association.vymaz_datum == 0) + qry = qry.join(Adresy_v2, Sidlo_Association.sidlo_text) + qry = qry.filter(Adresy_v2.id == adresa_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): @@ -176,5 +311,132 @@ def extract_actual(ico): results = qry.all() return render_template("extract-actual.html", ico = ico, results = results) +@app.route("/trivia", methods=['GET', 'POST']) +def trivia(): + number_entities = count_number_entries() + return render_template("trivia.html", number_entities = number_entities) + +@app.route("/most_common_addresses", methods=['GET', 'POST']) +def find_most_common_addresses(): + most_common_addresses = count_common_addresses() + return render_template("most_common_addresses.html", most_common_addresses = most_common_addresses) + +@app.route("/oldest_companies", methods=['GET', 'POST']) +def find_oldest_companies(): + oldest_companies = count_oldest_companies() + return render_template("oldest_companies.html", oldest_companies = 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) + +@app.route("/most_common_business", methods=['GET', 'POST']) +def find_most_common_business(): + most_common_business = count_common_business() + return render_template("most_common_business.html", most_common_business = most_common_business) + +@app.route("/most_common_activity", methods=['GET', 'POST']) +def find_most_common_activity(): + most_common_activity = count_common_activity() + return render_template("most_common_activity.html", most_common_activity = most_common_activity) + +@app.route("/most_common_degree", methods=['GET', 'POST']) +def find_most_common_degree(): + most_common_degree_before = count_common_degrees("before") + most_common_degree_after = count_common_degrees("after") + return render_template("most_common_degree.html", most_common_degree_before = most_common_degree_before, most_common_degree_after = most_common_degree_after) + +def count_number_entries(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT COUNT(id) FROM companies;") + entries_number = conn.execute(text_instruction).fetchall() + conn.close() + return entries_number[0][0] + +def count_common_addresses(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT sidlo_id, COUNT(`sidlo_id`) AS `value_occurrence` FROM sidlo_relation INNER JOIN adresy_v2 ON sidlo_relation.sidlo_id=adresy_v2.id WHERE vymaz_datum = 0 GROUP BY `sidlo_id` ORDER BY `value_occurrence` DESC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + addresses_frequency = [] + for elem in result: + qry = Adresy_v2.query + qry = qry.filter(Adresy_v2.id == elem[0]) + selected_address = qry.all() + addresses_frequency.append((selected_address[0], elem[1], elem[0])) + conn.close() + return addresses_frequency + +def count_common_business(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT predmet_podnikani_id, COUNT(`predmet_podnikani_id`) AS `value_occurrence` FROM predmety_podnikani_relation INNER JOIN predmety_podnikani ON predmety_podnikani_relation.predmet_podnikani_id=predmety_podnikani.id WHERE vymaz_datum = 0 GROUP BY `predmet_podnikani_id` ORDER BY `value_occurrence` DESC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + business_frequency = [] + for elem in result: + qry = Predmet_Podnikani.query + qry = qry.filter(Predmet_Podnikani.id == elem[0]) + selected_business = qry.all() + business_frequency.append((selected_business[0].predmet_podnikani, elem[1])) + conn.close() + return business_frequency + +def count_common_activity(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT predmet_cinnosti_id, COUNT(`predmet_cinnosti_id`) AS `value_occurrence` FROM predmety_cinnosti_relation INNER JOIN predmety_cinnosti ON predmety_cinnosti_relation.predmet_cinnosti_id=predmety_cinnosti.id WHERE vymaz_datum = 0 GROUP BY `predmet_cinnosti_id` ORDER BY `value_occurrence` DESC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + activity_frequency = [] + for elem in result: + qry = Predmet_Cinnosti.query + qry = qry.filter(Predmet_Cinnosti.id == elem[0]) + selected_activity = qry.all() + activity_frequency.append((selected_activity[0].predmet_cinnosti, elem[1])) + conn.close() + return activity_frequency + +def count_common_purpose(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT ucel_id, COUNT(`ucel_id`) AS `value_occurrence` FROM ucel_relation INNER JOIN ucel ON ucel_relation.ucel_id=ucel.id WHERE vymaz_datum = 0 GROUP BY `ucel_id` ORDER BY `value_occurrence` DESC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + addresses_frequency = [] + for elem in result: + qry = Ucel.query + qry = qry.filter(Ucel.id == elem[0]) + selected_purpose = qry.all() + addresses_frequency.append((selected_purpose[0].ucel, elem[1])) + conn.close() + return addresses_frequency + +def count_common_degrees(method): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + if method == "before": + text_instruction = text("SELECT titul_pred, COUNT(`titul_pred`) AS `value_occurrence` FROM fyzicke_osoby GROUP BY `titul_pred` ORDER BY `value_occurrence` DESC LIMIT 100;") + else: + text_instruction = text("SELECT titul_za, COUNT(`titul_za`) AS `value_occurrence` FROM fyzicke_osoby GROUP BY `titul_za` ORDER BY `value_occurrence` DESC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + degree_frequency = [] + for elem in result: + degree_frequency.append((elem[0], elem[1])) + return degree_frequency[1:] + +def count_oldest_companies(): + engine = create_engine('sqlite:///justice.db', echo=True) + conn = engine.connect() + text_instruction = text("SELECT id from companies ORDER BY zapis ASC LIMIT 100;") + result = conn.execute(text_instruction).fetchall() + oldest_companies = [] + for elem in result: + qry = Company.query + qry = qry.filter(Company.id == elem[0]) + selected_company = qry.all() + oldest_companies.append((selected_company[0].nazev, selected_company[0].zapis, selected_company[0].ico)) + return oldest_companies + + if __name__ == '__main__': app.run()
\ No newline at end of file @@ -5,7 +5,6 @@ from app import db from sqlalchemy.orm import relationship, backref import sqlalchemy.types as types - def convert_date_to_string(converted_date): try: if converted_date == 0: @@ -69,6 +68,24 @@ def convert_soud_to_string(my_soud): else: return "podivného soudu" +def convert_currency(my_currency): + if my_currency == "KORUNY": + return "Kč" + elif my_currency == "EURA": + return "Euro" + else: + return "" + +def convert_contribution(my_contribution): + if my_contribution == "KORUNY": + return "Kč" + elif my_contribution == "PROCENTA": + return "%" + elif my_contribution == "EURA": + return "Euro" + else: + return "" + class MyType(types.TypeDecorator): impl = types.Unicode @@ -89,11 +106,31 @@ class MySoud(types.TypeDecorator): def copy(self, **kw): return MySoud(self.impl.length) +class MyCurrency(types.TypeDecorator): + + impl = types.Unicode + + def process_result_value(self, value, dialect): + return convert_currency(value) + + def copy(self, **kw): + return MyCurrency(self.impl.length) + +class MyContribution(types.TypeDecorator): + + impl = types.Unicode + + def process_result_value(self, value, dialect): + return convert_contribution(value) + + def copy(self, **kw): + return MyContribution(self.impl.length) + class Predmety_Podnikani_Association(db.Model): __tablename__ = 'predmety_podnikani_relation' - id = db.Column(db.Integer) + id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) - predmet_podnikani_id = db.Column(db.Integer, db.ForeignKey('predmety_podnikani.id'), primary_key=True) + predmet_podnikani_id = db.Column(db.Integer, db.ForeignKey('predmety_podnikani.id')) zapis_datum = db.Column(MyType) vymaz_datum = db.Column(MyType) predmet_podnikani = db.relationship("Predmet_Podnikani", back_populates="company_predmet_podnikani") @@ -101,14 +138,24 @@ class Predmety_Podnikani_Association(db.Model): class Predmety_Cinnosti_Association(db.Model): __tablename__ = 'predmety_cinnosti_relation' - id = db.Column(db.Integer) + id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) - predmet_cinnosti_id = db.Column(db.Integer, db.ForeignKey('predmety_cinnosti.id'), primary_key=True) + predmet_cinnosti_id = db.Column(db.Integer, db.ForeignKey('predmety_cinnosti.id')) zapis_datum = db.Column(MyType) vymaz_datum = db.Column(MyType) predmet_cinnosti = db.relationship("Predmet_Cinnosti") company = db.relationship("Company") +class Ucel_Association(db.Model): + __tablename__ = 'ucel_relation' + id = db.Column(db.Integer, primary_key=True) + company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) + ucel_id = db.Column(db.Integer, db.ForeignKey('ucel.id')) + zapis_datum = db.Column(MyType) + vymaz_datum = db.Column(MyType) + ucel = db.relationship("Ucel") + company = db.relationship("Company") + class Sidlo_Association(db.Model): __tablename__ = 'sidlo_relation' id = db.Column(db.Integer, primary_key=True) @@ -129,11 +176,21 @@ class Pravni_Forma_Association_v2(db.Model): pravni_forma_text = db.relationship("Pravni_Formy") company = db.relationship("Company") +class Dozorci_Rada_Association(db.Model): + __tablename__ = 'dozorci_rada_relation' + 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) + company = db.relationship("Company") + pocet_clenu = db.relationship("Pocty_Clenu_DR") + clenove = db.relationship("Dozorci_Rada_Clen_Association") + class Statutarni_Organ_Association(db.Model): __tablename__ = 'statutarni_organ_relation' - id = db.Column(db.Integer) + id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer, db.ForeignKey('companies.id')) - statutarni_organ_id = db.Column(db.Integer, db.ForeignKey('statutarni_organy.id'), primary_key=True) + statutarni_organ_id = db.Column(db.Integer, db.ForeignKey('statutarni_organy.id')) zapis_datum = db.Column(MyType) vymaz_datum = db.Column(MyType) statutarni_organ_text = db.relationship("Statutarni_Organy") @@ -142,20 +199,12 @@ class Statutarni_Organ_Association(db.Model): zpusoby_jednani = db.relationship("Zpusob_Jednani_Association") clenove = db.relationship("Statutarni_Organ_Clen_Association") -class Dozorci_Rada_Association(db.Model): - __tablename__ = 'dozorci_rada_relation' - 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) - pocet_clenu = db.relationship("Pocty_Clenu_DR") - clenove = db.relationship("Dozorci_Rada_Clen_Association") - class Statutarni_Organ_Clen_Association(db.Model): __tablename__ = 'statutarni_organ_clen_relation' id = db.Column(db.Integer, primary_key=True) statutarni_organ_id = db.Column(db.Integer, db.ForeignKey('statutarni_organ_relation.id')) osoba_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id')) + prav_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) @@ -166,12 +215,58 @@ class Statutarni_Organ_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") + statutarni_organ = db.relationship("Statutarni_Organ_Association") + +class Pravnicka_Osoba(db.Model): + __tablename__ = "pravnicke_osoby" + id = db.Column(db.Integer, primary_key=True) + ico = db.Column(db.String) + reg_cislo = db.Column(db.String) + nazev = db.Column(db.String) + 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") + +class Fyzicka_Osoba(db.Model): + __tablename__ = "fyzicke_osoby" + id = db.Column(db.Integer, primary_key=True) + titul_pred = db.Column(db.String) + jmeno = db.Column(db.String) + prijmeni = db.Column(db.String) + titul_za = db.Column(db.String) + datum_naroz = db.Column(MyType) + 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") + + def get_name(self): + joined_name = "" + if self.titul_pred != "0" and self.titul_pred != None: + joined_name += self.titul_pred + " " + if self.jmeno != "0" and self.jmeno != None: + joined_name += self.jmeno + " " + if self.prijmeni != "0" and self.prijmeni != None: + joined_name += self.prijmeni + if self.titul_za != "0" and self.titul_za != None: + joined_name += ", " + self.titul_za + return joined_name + + def __repr__(self): + joined_name = self.get_name() + if self.datum_naroz != 0 and self.datum_naroz != None and self.datum_naroz != "": + joined_name += ", nar. " + self.datum_naroz + return joined_name class Dozorci_Rada_Clen_Association(db.Model): __tablename__ = 'dr_organ_clen_relation' 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) @@ -182,6 +277,8 @@ 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): __tablename__ = "spolecnici" @@ -194,9 +291,10 @@ class Spolecnici_Association(db.Model): adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id')) text_spolecnik = db.Column(db.String) adresa = db.relationship("Adresy_v2") - jmeno = db.relationship("Fyzicka_Osoba") + jmeno = db.relationship("Fyzicka_Osoba", back_populates="spolecnik_association") oznaceni_po = db.relationship("Pravnicka_Osoba") podily = db.relationship("Podily_Association") + company = db.relationship("Company") class Prokurista_Association(db.Model): __tablename__ = "prokuriste" @@ -209,6 +307,7 @@ class Prokurista_Association(db.Model): adresa = db.relationship("Adresy_v2") jmeno = db.relationship("Fyzicka_Osoba") text_prokurista = db.Column(db.String) + company = db.relationship("Company") class Prokura_Common_Text_Association(db.Model): __tablename__ = "prokura_common_texts" @@ -231,6 +330,7 @@ class Jediny_Akcionar_Association(db.Model): adresa = db.relationship("Adresy_v2") jmeno = db.relationship("Fyzicka_Osoba") oznaceni_po = db.relationship("Pravnicka_Osoba") + company = db.relationship("Company") class Podily_Association(db.Model): __tablename__ = "podily" @@ -239,13 +339,25 @@ 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(db.String) + vklad_typ = db.Column(MyCurrency) vklad_text = db.Column(db.String) - souhrn_typ = db.Column(db.String) + souhrn_typ = db.Column(MyContribution) souhrn_text = db.Column(db.String) - splaceni_typ = db.Column(db.String) + splaceni_typ = db.Column(MyContribution) splaceni_text = db.Column(db.String) druh_podilu = db.relationship("Druhy_Podilu") + + def my_rep(self): + podil_descr = "Vklad: " + self.vklad_text + " " + self.vklad_typ + "\n" + podil_descr += "Splaceno: " + self.splaceni_text + " " + self.splaceni_typ + if self.souhrn_text != "0": + podil_descr += "\n" + "Podíl: " + self.souhrn_text + self.souhrn_typ + if self.druh_podilu.druh_podilu != "0": + podil_descr += "\n" + "Druh podílu: " + self.druh_podilu.druh_podilu + return podil_descr.split("\n") + + class Zpusob_Jednani_Association(db.Model): __tablename__ = 'zpusoby_jednani_relation' @@ -270,6 +382,7 @@ class Company(db.Model): konkurz = db.relationship("Konkurz_Events") predmet_podnikani = db.relationship("Predmety_Podnikani_Association") predmet_cinnosti = db.relationship("Predmety_Cinnosti_Association") + ucel = db.relationship("Ucel_Association") zakladni_kapital = db.relationship("Zakladni_Kapital") ostatni_skutecnosti = db.relationship("Ostatni_Skutecnosti") akcie = db.relationship("Akcie") @@ -304,12 +417,12 @@ class Adresy_v2(db.Model): joined_address = "" if self.komplet_adresa != "0": return self.komplet_adresa - if self.ulice != "0": + if self.ulice != "0" and self.ulice != None: joined_address += self.ulice + " " if self.cisloText != "0" and self.cisloText != None: joined_address += self.cisloText + ", " if self.cisloPo != 0: - if self.ulice == "0": + if self.ulice == "0" and self.ulice != None: joined_address += "č.p. " joined_address += str(self.cisloPo) if self.cisloOr != 0: @@ -320,11 +433,11 @@ class Adresy_v2(db.Model): joined_address += str(self.cisloOr) + ", " if self.cisloEv != 0: joined_address += str(self.cisloEv) + ", " - if (self.castObce != "0") and (self.castObce != self.obec): + if (self.castObce != "0") and (self.castObce != self.obec) and self.castObce != None: joined_address += self.castObce + ", " if self.psc != "0" and self.psc != None: joined_address += self.psc + " " - if self.obec != "0": + 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"): joined_address += ", " + self.stat @@ -356,6 +469,27 @@ class Zakladni_Kapital(db.Model): vklad_hodnota = db.Column(db.String) 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 + " " + + if self.vklad_typ == "KORUNY": + joined_zk += "Kč " + elif self.vklad_typ == "EURA": + joined_zk += "euro " + + if self.splaceni_hodnota != "0": + joined_zk += "\n" + joined_zk += "Splaceno: " + self.splaceni_hodnota + " " + if self.splaceni_typ == "KORUNY": + joined_zk += "Kč " + elif self.splaceni_typ == "PROCENTA": + joined_zk += "% " + elif self.splaceni_typ == "EURA": + joined_zk += "euro " + return joined_zk.split("\n") class Predmet_Podnikani(db.Model): __tablename__ = "predmety_podnikani" @@ -369,6 +503,12 @@ class Predmet_Cinnosti(db.Model): predmet_cinnosti = db.Column(db.String) company_predmet_cinnosti = db.relationship("Predmety_Cinnosti_Association") +class Ucel(db.Model): + __tablename__ = "ucel" + id = db.Column(db.Integer, primary_key=True) + ucel = db.Column(db.String) + company_ucel = db.relationship("Ucel_Association") + class Sidlo(db.Model): __tablename__ = "adresy" id = db.Column(db.Integer, primary_key=True) @@ -406,6 +546,40 @@ class Akcie(db.Model): akcie_hodnota_typ = db.Column(db.String) akcie_hodnota_value = db.Column(db.String) akcie_text = db.Column(db.String) + def __repr__(self): + joined_share_descr = "" + self.akcie_pocet + " ks " + + if self.akcie_typ == "KMENOVE_NA_JMENO": + joined_share_descr += "kmenové akcie na jméno " + elif self.akcie_typ == "KMENOVE_NA_MAJITELE": + joined_share_descr += "kmenové akcie na majitele " + elif self.akcie_typ == "KUSOVE_NA_JMENO": + joined_share_descr += "kusové akcie " + elif self.akcie_typ == "NA_JMENO": + joined_share_descr += "akcie na jméno " + elif self.akcie_typ == "NA_MAJITELE": + joined_share_descr += "akcie na majitele " + elif self.akcie_typ == "PRIORITNI_NA_JMENO": + joined_share_descr += "prioritní akcie na jméno " + elif self.akcie_typ == "ZAMESTNANECKE_NA_JMENO": + joined_share_descr += "zaměstnanecké akcie na jméno " + elif self.akcie_typ == "ZVLASTNI_PRAVA": + joined_share_descr += "akcie se zvláštními právy " + + if self.akcie_podoba == "LISTINNA": + joined_share_descr += "v listinné podobě " + elif self.akcie_podoba == "ZAKNIHOVANA": + joined_share_descr += "v zaknihované podobě " + 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_typ == "KORUNY": + joined_share_descr += "Kč" + elif self.akcie_hodnota_typ == "EURA": + joined_share_descr += "euro" + return joined_share_descr class Nazvy(db.Model): __tablename__ = "nazvy" @@ -452,18 +626,3 @@ class Druhy_Podilu(db.Model): id = db.Column(db.Integer, primary_key=True) druh_podilu = db.Column(db.String) -class Fyzicka_Osoba(db.Model): - __tablename__ = "fyzicke_osoby" - id = db.Column(db.Integer, primary_key=True) - titul_pred = db.Column(db.String) - jmeno = db.Column(db.String) - prijmeni = db.Column(db.String) - titul_za = db.Column(db.String) - datum_naroz = db.Column(MyType) - -class Pravnicka_Osoba(db.Model): - __tablename__ = "pravnicke_osoby" - id = db.Column(db.Integer, primary_key=True) - ico = db.Column(db.String) - reg_cislo = db.Column(db.String) - nazev = db.Column(db.String)
\ No newline at end of file diff --git a/templates/extract-actual.html b/templates/extract-actual.html index ab19efc..6bf6087 100644 --- a/templates/extract-actual.html +++ b/templates/extract-actual.html @@ -21,6 +21,13 @@ {% endif %} {% endfor %} +{% set ucel_notes = [] %} +{% for i in range (row.ucel|length) %} + {% if row.ucel[i].vymaz_datum == 0 %} + {% set ucel_notes = ucel_notes.append(row.ucel[i].ucel.ucel) %} + {% endif %} +{% endfor %} + {% set zakladni_kapital_notes = [] %} {% for i in range (row.zakladni_kapital|length) %} {% if row.zakladni_kapital[i].vymaz_datum == 0 %} @@ -98,22 +105,6 @@ {% endif %} {% endfor %} -{% if row.soud == "MSPH" %} - {% set soud = "Městského soudu v Praze" %} -{% elif row.soud == "KSCB" %} - {% set soud = "Krajského soudu v Českých Budějovicích" %} -{% elif row.soud == "KSOS" %} - {% set soud = "Krajského soudu v Ostravě" %} -{% elif row.soud == "KSPL" %} - {% set soud = "Krajského soudu v Plzni" %} -{% elif row.soud == "KSBR" %} - {% set soud = "Krajského soudu v Brně" %} -{% elif row.soud == "KSHK" %} - {% set soud = "Krajského soudu v Hradci Králové" %} -{% elif row.soud == "KSUL" %} - {% set soud = "Krajského soudu v Ústí nad Labem" %} -{% endif %} - {% if insolvency_notes|length > 0 %} {% set table_style = "table table-danger" %} {% elif loop.index % 2 == 0 %} @@ -255,6 +246,17 @@ {% endif %} <!-- END Display areas of activities --> + <!-- Display purpose --> + {% if ucel_notes|length > 0 %} + {% for i in range (ucel_notes|length) %} + <tr> + <td>{% if i == 0%}Účel:{% endif %}</td> + <td>{{ ucel_notes[i] }}</td> + </tr> + {% endfor %} + {% endif %} + <!-- END Display purpose --> + <!-- Display statutory bodies --> {% if statutarni_organ_notes|length > 0 %} {% for i in range (statutarni_organ_notes|length) %} @@ -269,8 +271,8 @@ {% if statutarni_organ_notes[i].clenove[j].vymaz_datum == 0 %} <tr> <td style = padding-left:2em>{% if statutarni_organ_notes[i].clenove[j].funkce != "0" %} {{ statutarni_organ_notes[i].clenove[j].funkce }} {% endif %}</td> - <td>{% if statutarni_organ_notes[i].clenove[j].jmeno.jmeno != "0" %}{{ statutarni_organ_notes[i].clenove[j].jmeno.jmeno }} {% endif %} - {{ statutarni_organ_notes[i].clenove[j].jmeno.prijmeni }}{% if statutarni_organ_notes[i].clenove[j].jmeno.datum_naroz != "" %}, nar. {{ statutarni_organ_notes[i].clenove[j].jmeno.datum_naroz }}{% endif %} <br> + <td>{% if statutarni_organ_notes[i].clenove[j].jmeno != None %}{{ statutarni_organ_notes[i].clenove[j].jmeno }} + {% else %}{{ statutarni_organ_notes[i].clenove[j].jmeno_po.nazev}}{% if statutarni_organ_notes[i].clenove[j].jmeno_po.reg_cislo != 0 %}, reg č. {{ statutarni_organ_notes[i].clenove[j].jmeno_po.reg_cislo }}{% endif %}{% if statutarni_organ_notes[i].clenove[j].jmeno_po.ico != 0 %}, IČ <a href="/{{ statutarni_organ_notes[i].clenove[j].jmeno_po.ico }}">{{ statutarni_organ_notes[i].clenove[j].jmeno_po.ico }}</a>{% endif %} {% endif %}<br> {{ statutarni_organ_notes[i].clenove[j].adresa }} {% if statutarni_organ_notes[i].clenove[j].funkce_od != 0 %}<br>Den vzniku funkce: {{ statutarni_organ_notes[i].clenove[j].funkce_od}}{% endif %} {% if statutarni_organ_notes[i].clenove[j].clenstvi_od != 0 %}<br>Den vzniku členství: {{ statutarni_organ_notes[i].clenove[j].clenstvi_od}}{% endif %}</td> @@ -323,8 +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>{% if dozorci_rada_notes[i].clenove[j].jmeno.jmeno != 0 %}{{ dozorci_rada_notes[i].clenove[j].jmeno.jmeno }} {% endif %} - {{ dozorci_rada_notes[i].clenove[j].jmeno.prijmeni }}{% if dozorci_rada_notes[i].clenove[j].jmeno.datum_naroz != "" %}, nar. {{ dozorci_rada_notes[i].clenove[j].jmeno.datum_naroz }}{% endif %} <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> @@ -354,8 +356,7 @@ {% for i in range (prokurist_notes|length) %} <tr> <td>{% if i == 0%}Prokura:{% endif %}</td> - <td>{% if prokurist_notes[i].jmeno.jmeno != "0" %}{{ prokurist_notes[i].jmeno.jmeno }} {% endif %} - {{ prokurist_notes[i].jmeno.prijmeni }}{% if prokurist_notes[i].jmeno.datum_naroz != "" %}, nar. {{ prokurist_notes[i].jmeno.datum_naroz }}{% endif %}<br> + <td>{{ prokurist_notes[i].jmeno}}<br> {{ prokurist_notes[i].adresa }}{% if prokurist_notes[i].text_prokurista != "0" %}<br>{{ prokurist_notes[i].text_prokurista }}{% endif %}</td> </tr> {% endfor %} @@ -375,9 +376,7 @@ {% for i in range (sole_shareholder_notes|length) %} <tr> <td>{% if i == 0%}Jediný akcionář:{% endif %}</td> - <td>{% if sole_shareholder_notes[i].akcionar_po_id == None %} - {% if sole_shareholder_notes[i].jmeno.jmeno != "0" %}{{ sole_shareholder_notes[i].jmeno.jmeno }} {% endif %} - {{ sole_shareholder_notes[i].jmeno.prijmeni }}{% if sole_shareholder_notes[i].jmeno.datum_naroz != "" %}, nar. {{ sole_shareholder_notes[i].jmeno.datum_naroz }}{% endif %} {% endif %} + <td>{% if sole_shareholder_notes[i].akcionar_po_id == None %}{{ sole_shareholder_notes[i].jmeno }}{% endif %} {% if sole_shareholder_notes[i].akcionar_fo_id == None %} {{ sole_shareholder_notes[i].oznaceni_po.nazev }}{% if sole_shareholder_notes[i].oznaceni_po.reg_cislo != 0 %}, reg č. {{ sole_shareholder_notes[i].oznaceni_po.reg_cislo }}{% endif %}{% if sole_shareholder_notes[i].oznaceni_po.ico != 0 %}, IČ <a href="/{{ sole_shareholder_notes[i].oznaceni_po.ico }}-actual">{{ sole_shareholder_notes[i].oznaceni_po.ico }}</a>{% endif %} {% endif %} <br>{{sole_shareholder_notes[i].adresa}}</td> </tr> @@ -394,9 +393,7 @@ {% for i in range (spolecnici_notes|length) %} <tr> <td style = padding-left:2em>Společník:</td> - <td>{% if spolecnici_notes[i].spolecnik_po_id == None %} - {% if spolecnici_notes[i].jmeno.jmeno != "0" %}{{ spolecnici_notes[i].jmeno.jmeno }} {% endif %} - {{ spolecnici_notes[i].jmeno.prijmeni }}{% if spolecnici_notes[i].jmeno.datum_naroz != "" %}, nar. {{ spolecnici_notes[i].jmeno.datum_naroz }}{% endif %} {% endif %} + <td>{% if spolecnici_notes[i].spolecnik_po_id == None %}{{ spolecnici_notes[i].jmeno }}{% endif %} {% if spolecnici_notes[i].spolecnik_fo_id == None %} {{ spolecnici_notes[i].oznaceni_po.nazev }}{% if spolecnici_notes[i].oznaceni_po.reg_cislo != 0 %}, reg č. {{ spolecnici_notes[i].oznaceni_po.reg_cislo }}{% endif %}{% if spolecnici_notes[i].oznaceni_po.ico != 0 %}, IČ <a href="/{{ spolecnici_notes[i].oznaceni_po.ico }}-actual">{{ spolecnici_notes[i].oznaceni_po.ico }}</a>{% endif %} {% endif %} <br>{{spolecnici_notes[i].adresa}}</td> </tr> @@ -452,44 +449,7 @@ {% for i in range (shares_notes|length) %} <tr> <td>{% if i == 0%}Akcie:{% endif %}</td> - {% if shares_notes[i].akcie_podoba == "LISTINNA" %} - {% set akcie_podoba_description = "v listinné podobě" %} - {% elif shares_notes[i].akcie_podoba == "ZAKNIHOVANA" %} - {% set akcie_podoba_description = "v zaknihované podobě" %} - {% elif shares_notes[i].akcie_podoba == "IMOBILIZOVANA" %} - {% set akcie_podoba_description = "v imobilizované podobě" %} - {% else %} - {% set akcie_podoba_description = "" %} - {% endif %} - - {% if shares_notes[i].akcie_typ == "KMENOVE_NA_JMENO" %} - {% set akcie_typ_description = "kmenové akcie na jméno" %} - {% elif shares_notes[i].akcie_typ == "KMENOVE_NA_MAJITELE" %} - {% set akcie_typ_description = "kmenové akcie na majitele" %} - {% elif shares_notes[i].akcie_typ == "KUSOVE_NA_JMENO" %} - {% set akcie_typ_description = "kusové akcie" %} - {% elif shares_notes[i].akcie_typ == "NA_JMENO" %} - {% set akcie_typ_description = "akcie na jméno" %} - {% elif shares_notes[i].akcie_typ == "NA_MAJITELE" %} - {% set akcie_typ_description = "akcie na majitele" %} - {% elif shares_notes[i].akcie_typ == "PRIORITNI_NA_JMENO" %} - {% set akcie_typ_description = "prioritní akcie na jméno" %} - {% elif shares_notes[i].akcie_typ == "ZAMESTNANECKE_NA_JMENO" %} - {% set akcie_typ_description = "zaměstnanecké akcie na jméno" %} - {% elif shares_notes[i].akcie_typ == "ZVLASTNI_PRAVA" %} - {% set akcie_typ_description = "akcie se zvláštními právy" %} - {% else %} - {% set akcie_typ_description = "" %} - {% endif %} - - {% if shares_notes[i].akcie_hodnota_typ == "KORUNY" %} - {% set akcie_hodnota_typ_symbol = "Kč" %} - {% elif shares_notes[i].akcie_hodnota_typ == "EURA" %} - {% set akcie_hodnota_typ_symbol = "euro" %} - {% else %} - {% set akcie_hodnota_typ_symbol = "" %} - {% endif %} - <td>{{ shares_notes[i].akcie_pocet }} ks {{ akcie_typ_description }} {{ akcie_podoba_description }} ve jmenovité hodnotě {{ shares_notes[i].akcie_hodnota_value }} {{ akcie_hodnota_typ_symbol }} {% if shares_notes[i].akcie_text != "0" %} <br> {{ shares_notes[i].akcie_text }} {% endif %}</td> + <td>{{ shares_notes[i] }} {% if shares_notes[i].akcie_text != "0" %} <br> {{ shares_notes[i].akcie_text }} {% endif %}</td> </tr> {% endfor %} {% endif %} diff --git a/templates/extract.html b/templates/extract.html index 2bacb06..3600cd5 100644 --- a/templates/extract.html +++ b/templates/extract.html @@ -163,7 +163,7 @@ {% set splaceni_typ_description = row.zakladni_kapital[i].splaceni_hodnota %} {% endif %} - <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>{{ 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> {% endfor %} @@ -208,6 +208,25 @@ {% endif %} <!-- END Display areas of activities --> + <!-- Display purpose --> + {% if row.ucel|length > 0 %} + {% for i in range (row.ucel|length) %} + <tr> + <td>{% if i == 0%}Účel:{% endif %}</td> + {% if row.ucel[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.ucel[i].ucel.ucel }} {{ underlne_style_close|safe }}</td> + <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.ucel[i].zapis_datum }} {% if row.ucel[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.ucel[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> + </tr> + {% endfor %} + {% endif %} + <!-- END Display purpose --> + <!-- Display statutory bodies --> {% if row.statutarni_organ_text|length > 0 %} {% for i in range (row.statutarni_organ_text|length) %} @@ -237,8 +256,8 @@ {% set underlne_style_close = "" %} {% endif %} <td>{{ underlne_style_open|safe }} - {% if row.statutarni_organ_text[i].clenove[j].jmeno.jmeno != "0" %}{{ row.statutarni_organ_text[i].clenove[j].jmeno.jmeno }} {% endif %} - {{ row.statutarni_organ_text[i].clenove[j].jmeno.prijmeni }}{% if row.statutarni_organ_text[i].clenove[j].jmeno.datum_naroz != "" %}, nar. {{ row.statutarni_organ_text[i].clenove[j].jmeno.datum_naroz }}{% endif %} <br> + {% if row.statutarni_organ_text[i].clenove[j].jmeno != None %}{{ row.statutarni_organ_text[i].clenove[j].jmeno }} + {% else %}{{ row.statutarni_organ_text[i].clenove[j].jmeno_po.nazev}}{% if row.statutarni_organ_text[i].clenove[j].jmeno_po.reg_cislo != 0 %}, reg č. {{ row.statutarni_organ_text[i].clenove[j].jmeno_po.reg_cislo }}{% endif %}{% if row.statutarni_organ_text[i].clenove[j].jmeno_po.ico != 0 %}, IČ <a href="/{{ row.statutarni_organ_text[i].clenove[j].jmeno_po.ico }}">{{ row.statutarni_organ_text[i].clenove[j].jmeno_po.ico }}</a>{% endif %} {% endif %}<br> {{ row.statutarni_organ_text[i].clenove[j].adresa }} {% if row.statutarni_organ_text[i].clenove[j].funkce_od != 0 %}<br>Den vzniku funkce: {{ row.statutarni_organ_text[i].clenove[j].funkce_od}}{% endif %} {% if row.statutarni_organ_text[i].clenove[j].funkce_do != 0 %}<br>Den zániku funkce: {{ row.statutarni_organ_text[i].clenove[j].funkce_do}}{% endif %} @@ -322,8 +341,8 @@ {% set underlne_style_close = "" %} {% endif %} <td>{{ underlne_style_open|safe }} - {% if row.dozorci_rada_text[i].clenove[j].jmeno.jmeno != "0" %}{{ row.dozorci_rada_text[i].clenove[j].jmeno.jmeno }} {% endif %} - {{ row.dozorci_rada_text[i].clenove[j].jmeno.prijmeni }}{% if row.dozorci_rada_text[i].clenove[j].jmeno.datum_naroz != "" %}, nar. {{ row.dozorci_rada_text[i].clenove[j].jmeno.datum_naroz }}{% endif %} <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 %} @@ -378,8 +397,7 @@ {% set underlne_style_close = "" %} {% endif %} <td>{{ underlne_style_open|safe }} - {% if row.prokurista[i].jmeno.jmeno != "0" %}{{ row.prokurista[i].jmeno.jmeno }} {% endif %} - {{ row.prokurista[i].jmeno.prijmeni }}{% if row.prokurista[i].jmeno.datum_naroz != "" %}, nar. {{ row.prokurista[i].jmeno.datum_naroz }}{% endif %}<br> + {{ row.prokurista[i].jmeno }}<br> {{ row.prokurista[i].adresa }}{% if row.prokurista[i].text_prokurista != "0" %}<br>{{ row.prokurista[i].text_prokurista }}{% endif %}{{ underlne_style_close|safe }}</td> <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.prokurista[i].zapis_datum }} {% if row.prokurista[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.prokurista[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> </tr> @@ -420,9 +438,7 @@ {% set underlne_style_open = "" %} {% set underlne_style_close = "" %} {% endif %} - <td>{{ underlne_style_open|safe }} {% if row.jediny_akcionar[i].akcionar_po_id == None %} - {% if row.jediny_akcionar[i].jmeno.jmeno != "0" %}{{ row.jediny_akcionar[i].jmeno.jmeno }} {% endif %} - {{ row.jediny_akcionar[i].jmeno.prijmeni }}{% if row.jediny_akcionar[i].jmeno.datum_naroz != "" %}, nar. {{ row.jediny_akcionar[i].jmeno.datum_naroz }}{% endif %} {% endif %} + <td>{{ underlne_style_open|safe }} {% if row.jediny_akcionar[i].akcionar_po_id == None %}{{ row.jediny_akcionar[i].jmeno }}{% endif %} {% if row.jediny_akcionar[i].akcionar_fo_id == None %} {{ row.jediny_akcionar[i].oznaceni_po.nazev }}{% if row.jediny_akcionar[i].oznaceni_po.reg_cislo != 0 %}, reg č. {{ row.jediny_akcionar[i].oznaceni_po.reg_cislo }}{% endif %}{% if row.jediny_akcionar[i].oznaceni_po.ico != 0 %}, IČ <a href="/{{ row.jediny_akcionar[i].oznaceni_po.ico }}">{{ row.jediny_akcionar[i].oznaceni_po.ico }}</a>{% endif %} {% endif %} <br>{{row.jediny_akcionar[i].adresa}} {{ underlne_style_close|safe }}</td> <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.jediny_akcionar[i].zapis_datum }} {% if row.jediny_akcionar[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.jediny_akcionar[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> @@ -448,9 +464,7 @@ {% set underlne_style_open = "" %} {% set underlne_style_close = "" %} {% endif %} - <td>{{ underlne_style_open|safe }} {% if row.spolecnici[i].spolecnik_po_id == None %} - {% if row.spolecnici[i].jmeno.jmeno != "0" %}{{ row.spolecnici[i].jmeno.jmeno }} {% endif %} - {{ row.spolecnici[i].jmeno.prijmeni }}{% if row.spolecnici[i].jmeno.datum_naroz != "" %}, nar. {{ row.spolecnici[i].jmeno.datum_naroz }}{% endif %} {% endif %} + <td>{{ underlne_style_open|safe }} {% if row.spolecnici[i].spolecnik_po_id == None %}{{ row.spolecnici[i].jmeno }}{% endif %} {% if row.spolecnici[i].spolecnik_fo_id == None %} {{ row.spolecnici[i].oznaceni_po.nazev }}{% if row.spolecnici[i].oznaceni_po.reg_cislo != 0 %}, reg č. {{ row.spolecnici[i].oznaceni_po.reg_cislo }}{% endif %}{% if row.spolecnici[i].oznaceni_po.ico != 0 %}, IČ <a href="/{{ row.spolecnici[i].oznaceni_po.ico }}">{{ row.spolecnici[i].oznaceni_po.ico }}</a>{% endif %} {% endif %} <br>{{row.spolecnici[i].adresa}} {{ underlne_style_close|safe }}</td> <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.spolecnici[i].zapis_datum }} {% if row.spolecnici[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.spolecnici[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> @@ -458,34 +472,6 @@ {% if row.spolecnici[i].podily|length > 0 %} {% for j in range (row.spolecnici[i].podily|length) %} - <!-- Set type of deposit --> - {% if row.spolecnici[i].podily[j].vklad_typ == "KORUNY" %} - {% set vklad_typ_string = "Kč" %} - {% elif row.spolecnici[i].podily[j].vklad_typ == "EURA" %} - {% set vklad_typ_string = "Euro" %} - {% else %} - {% set vklad_typ_string = "" %} - {% endif %} - - <!-- Set type of repaid contribution --> - {% if row.spolecnici[i].podily[j].splaceni_typ == "KORUNY" %} - {% set splaceni_typ_string = "Kč" %} - {% elif row.spolecnici[i].podily[j].splaceni_typ == "PROCENTA" %} - {% set splaceni_typ_string = "%" %} - {% elif row.spolecnici[i].podily[j].splaceni_typ == "EURA" %} - {% set splaceni_typ_string = "Euro" %} - {% else %} - {% set splaceni_typ_string = "" %} - {% endif %} - - <!-- Set type of share --> - {% if row.spolecnici[i].podily[j].souhrn_typ == "PROCENTA" %} - {% set souhrn_typ_string = "%" %} - {% else %} - {% set souhrn_typ_string = "" %} - {% endif %} - - <tr> <td style = padding-left:4em>Podíl:</td> {% if row.spolecnici[i].podily[j].vymaz_datum != 0 %} @@ -495,10 +481,11 @@ {% set underlne_style_open = "" %} {% set underlne_style_close = "" %} {% endif %} - <td>{{ underlne_style_open|safe }}Vklad: {{ row.spolecnici[i].podily[j].vklad_text }} {{ vklad_typ_string }}<br> - Splaceno: {{ row.spolecnici[i].podily[j].splaceni_text }} {{ splaceni_typ_string }}<br> - {% if row.spolecnici[i].podily[j].souhrn_text != "0"%}Podíl: {{ row.spolecnici[i].podily[j].souhrn_text }} {{ souhrn_typ_string }}{% endif %} - {% if row.spolecnici[i].podily[j].druh_podilu.druh_podilu != "0" %}<br>Druh podílu: {{ row.spolecnici[i].podily[j].druh_podilu.druh_podilu }}{% endif %}{{ underlne_style_close|safe }} + <td>{{ underlne_style_open|safe }} + {% for elem in row.spolecnici[i].podily[j].my_rep() %} + {{ elem }}<br> + {% endfor %} + {{ underlne_style_close|safe }} </td> <td>{{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici[i].podily[j].zapis_datum }} {% if row.spolecnici[i].podily[j].vymaz_datum != 0 %}<br>Vymazáno: {{ row.spolecnici[i].podily[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }}</td> </tr> @@ -523,46 +510,7 @@ {% set underlne_style_open = "" %} {% set underlne_style_close = "" %} {% endif %} - - {% if row.akcie[i].akcie_podoba == "LISTINNA" %} - {% set akcie_podoba_description = "v listinné podobě" %} - {% elif row.akcie[i].akcie_podoba == "ZAKNIHOVANA" %} - {% set akcie_podoba_description = "v zaknihované podobě" %} - {% elif row.akcie[i].akcie_podoba == "IMOBILIZOVANA" %} - {% set akcie_podoba_description = "v imobilizované podobě" %} - {% else %} - {% set akcie_podoba_description = "" %} - {% endif %} - - {% if row.akcie[i].akcie_typ == "KMENOVE_NA_JMENO" %} - {% set akcie_typ_description = "kmenové akcie na jméno" %} - {% elif row.akcie[i].akcie_typ == "KMENOVE_NA_MAJITELE" %} - {% set akcie_typ_description = "kmenové akcie na majitele" %} - {% elif row.akcie[i].akcie_typ == "KUSOVE_NA_JMENO" %} - {% set akcie_typ_description = "kusové akcie" %} - {% elif row.akcie[i].akcie_typ == "NA_JMENO" %} - {% set akcie_typ_description = "akcie na jméno" %} - {% elif row.akcie[i].akcie_typ == "NA_MAJITELE" %} - {% set akcie_typ_description = "akcie na majitele" %} - {% elif row.akcie[i].akcie_typ == "PRIORITNI_NA_JMENO" %} - {% set akcie_typ_description = "prioritní akcie na jméno" %} - {% elif row.akcie[i].akcie_typ == "ZAMESTNANECKE_NA_JMENO" %} - {% set akcie_typ_description = "zaměstnanecké akcie na jméno" %} - {% elif row.akcie[i].akcie_typ == "ZVLASTNI_PRAVA" %} - {% set akcie_typ_description = "akcie se zvláštními právy" %} - {% else %} - {% set akcie_typ_description = "" %} - {% endif %} - - {% if row.akcie[i].akcie_hodnota_typ == "KORUNY" %} - {% set akcie_hodnota_typ_symbol = "Kč" %} - {% elif row.akcie[i].akcie_hodnota_typ == "EURA" %} - {% set akcie_hodnota_typ_symbol = "euro" %} - {% else %} - {% set akcie_hodnota_typ_symbol = "" %} - {% endif %} - - <td>{{ underlne_style_open|safe }} {{ row.akcie[i].akcie_pocet }} ks {{ akcie_typ_description }} {{ akcie_podoba_description }} ve jmenovité hodnotě {{ row.akcie[i].akcie_hodnota_value }} {{ akcie_hodnota_typ_symbol }} {% if row.akcie[i].akcie_text != "0" %} <br> {{ row.akcie[i].akcie_text }} {% endif %} {{ underlne_style_close|safe }}</td> + <td>{{ underlne_style_open|safe }} {{ row.akcie[i] }} {% if row.akcie[i].akcie_text != "0" %} <br> {{ row.akcie[i].akcie_text }} {% endif %} {{ underlne_style_close|safe }}</td> <td>{{ underlne_style_open|safe }} Zapsáno: {{ row.akcie[i].zapis_datum }} {% if row.akcie[i].vymaz_datum != 0 %} <br> Vymazáno: {{ row.akcie[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }}</td> </tr> {% endfor %} diff --git a/templates/header.html b/templates/header.html index 30a4ebd..5b50d1a 100644 --- a/templates/header.html +++ b/templates/header.html @@ -8,4 +8,12 @@ <!-- Bootstrap CSS --> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous"> <title>Search Results - Flask Justice Database</title> -<body>
\ No newline at end of file +<body> +<table class="table"> + <tr> + <td><a href="/">Veřejný rejstřík podle subjektů</a></td> + <td><a href="/osoby">Veřejný rejstřík podle fyzických osob v angažmá</a></td> + <td><a href="/entity">Veřejný rejstřík podle právnických osob v angažmá</a></td> + <td><a href="/trivia">Zajímavosti z obchodního rejstříku</a></td> + </tr> +</table>
\ No newline at end of file diff --git a/templates/most_common_activity.html b/templates/most_common_activity.html new file mode 100644 index 0000000..dd73b41 --- /dev/null +++ b/templates/most_common_activity.html @@ -0,0 +1,24 @@ +{% include 'header.html' %} + +<h1>Nejčastější předmět činnosti:</h1> + +<table class="table table-hover" style="width: auto"> + <thead class="thead-dark"> + <tr class="table-info"> + <th scope="col">#</th> + <th scope="col">Předmět činnosti</th> + <th scope="col">Frekvence</th> + </tr> + </thead> + <tbody> + {% for i in range (most_common_activity|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td>{{ most_common_activity[i][0] }}</td> + <td>{{ most_common_activity[i][1] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/most_common_addresses.html b/templates/most_common_addresses.html new file mode 100644 index 0000000..4ee45ed --- /dev/null +++ b/templates/most_common_addresses.html @@ -0,0 +1,23 @@ +{% include 'header.html' %} + +<h1>Nejčastější sídla:</h1> +<table class="table table-hover" style="width: auto"> + <thead class="thead-dark"> + <tr class="table-info"> + <th scope="col">#</th> + <th scope="col">Adresa</th> + <th scope="col">Frekvence</th> + </tr> + </thead> + <tbody> + {% for i in range (most_common_addresses|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td><a href="/results-sidlo-{{most_common_addresses[i][2]}}">{{ most_common_addresses[i][0] }}</a></td> + <td>{{ most_common_addresses[i][1] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/most_common_business.html b/templates/most_common_business.html new file mode 100644 index 0000000..56822a5 --- /dev/null +++ b/templates/most_common_business.html @@ -0,0 +1,24 @@ +{% include 'header.html' %} + +<h1>Nejčastější předmět podnikání:</h1> + +<table class="table table-hover" style="width: auto"> + <thead class="thead-dark"> + <tr class="table-info"> + <th scope="col">#</th> + <th scope="col">Předmět podnikání</th> + <th scope="col">Frekvence</th> + </tr> + </thead> + <tbody> + {% for i in range (most_common_business|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td>{{ most_common_business[i][0] }}</td> + <td>{{ most_common_business[i][1] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/most_common_degree.html b/templates/most_common_degree.html new file mode 100644 index 0000000..72ac5b2 --- /dev/null +++ b/templates/most_common_degree.html @@ -0,0 +1,28 @@ +{% include 'header.html' %} + +<h1>Nejčastější tituly:</h1> + +<table class="table table-hover" style="width: auto"> + <thead class="thead-dark"> + <tr class="table-info"> + <th scope="col">#</th> + <th scope="col">Titul před jménem</th> + <th scope="col">Frekvence</th> + <th scope="col">Titul za jménem</th> + <th scope="col">Frekvence</th> + </tr> + </thead> + <tbody> + {% for i in range (most_common_degree_before|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td>{{ most_common_degree_before[i][0] }}</td> + <td>{{ most_common_degree_before[i][1] }}</td> + <td>{{ most_common_degree_after[i][0] }}</td> + <td>{{ most_common_degree_after[i][1] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/most_common_purpose.html b/templates/most_common_purpose.html new file mode 100644 index 0000000..a5e17a7 --- /dev/null +++ b/templates/most_common_purpose.html @@ -0,0 +1,24 @@ +{% include 'header.html' %} + +<h1>Nejčastější účel:</h1> + +<table class="table table-hover" style="width: auto"> + <thead class="thead-dark"> + <tr class="table-info"> + <th scope="col">#</th> + <th scope="col">Účel</th> + <th scope="col">Frekvence</th> + </tr> + </thead> + <tbody> + {% for i in range (most_common_purpose|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td>{{ most_common_purpose[i][0] }}</td> + <td>{{ most_common_purpose[i][1] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/oldest_companies.html b/templates/oldest_companies.html new file mode 100644 index 0000000..f79e954 --- /dev/null +++ b/templates/oldest_companies.html @@ -0,0 +1,23 @@ +{% include 'header.html' %} + +<h1>Nejstarší právnické osoby:</h1> +<table class="table table-hover" style="width: auto"> + <thead class="thead-dark"> + <tr class="table-info"> + <th scope="col">#</th> + <th scope="col">Název</th> + <th scope="col">Datum založení</th> + </tr> + </thead> + <tbody> + {% for i in range (oldest_companies|length) %} + <tr> + <th scope = "row">{{ i + 1 }}</th> + <td><a href="{{ oldest_companies[i][2] }}">{{ oldest_companies[i][0] }}</a></td> + <td>{{ oldest_companies[i][1] }}</td> + </tr> + {% endfor %} + </tbody> +</table> + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/results2.html b/templates/results2.html index 84b8326..9f41c18 100644 --- a/templates/results2.html +++ b/templates/results2.html @@ -1,6 +1,8 @@ -{% include 'header.html' %} - -{% include 'search_form.html' %} +{% if show_form == True %} + {% include 'search_form.html' %} +{% else %} + {% include 'header.html' %} +{% endif %} <p><b>Počet nalezených subjektů: {{ results|length }}</b></p> {% for row in results %} @@ -42,8 +44,6 @@ {% endif %} {% endfor %} - - <table class= "{{table_style}}" style="width: 100%"> <p></p> <tr> diff --git a/templates/results_entities.html b/templates/results_entities.html new file mode 100644 index 0000000..8465a9c --- /dev/null +++ b/templates/results_entities.html @@ -0,0 +1,128 @@ +{% if show_form == True %} + {% include 'search_form_entity.html' %} +{% else %} + {% include 'header.html' %} +{% endif %} + +{% set count = namespace(value=0) %} + {% for row in results %} + {% set test_list = [] %} + + {% for elem in row.spolecnik_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", "společní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 test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + + {% for elem in row.sole_shareholder_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", "jediný akcionář") %} + {% 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 test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + + {% for elem in row.statut_org_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", elem.funkce) %} + {% set x = my_dict.__setitem__("nazev", elem.statutarni_organ.company.nazev) %} + {% set x = my_dict.__setitem__("ico", elem.statutarni_organ.company.ico) %} + {% set x = my_dict.__setitem__("oddil", elem.statutarni_organ.company.oddil) %} + {% set x = my_dict.__setitem__("vlozka", elem.statutarni_organ.company.vlozka) %} + {% set x = my_dict.__setitem__("soud", elem.statutarni_organ.company.soud) %} + {% set x = my_dict.__setitem__("zapis", elem.statutarni_organ.company.zapis) %} + {% set test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + + {% for elem in row.supervisory_board_member_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", elem.funkce) %} + {% set x = my_dict.__setitem__("nazev", elem.dozorci_rada.company.nazev) %} + {% set x = my_dict.__setitem__("ico", elem.dozorci_rada.company.ico) %} + {% set x = my_dict.__setitem__("oddil", elem.dozorci_rada.company.oddil) %} + {% set x = my_dict.__setitem__("vlozka", elem.dozorci_rada.company.vlozka) %} + {% set x = my_dict.__setitem__("soud", elem.dozorci_rada.company.soud) %} + {% set x = my_dict.__setitem__("zapis", elem.dozorci_rada.company.zapis) %} + {% 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" %} + {% else %} + {% set table_style = "table table-secondary" %} + {% endif %} + + <table class= "{{table_style}}" style="width: 100%"> + <p></p> + <tr> + <td style="width:20%">Název:</td> + <td style="width:40%"><b>{{ row.nazev }}</b></td> + <td style="width:15%">IČO:</td> + <td style="width:25%"><a href="/{{ row.ico }}"><b>{{ row.ico }}</b></a></td> + </tr> + + <tr> + <td>Adresa:</td> + <td>{{ elem.adresa }}</td> + <td>Angažmá:</td> + <td>{{ elem.funkce }}</td> + </tr> + + <tr> + <td><i>ve společnosti:</i></td> + <td></td> + <td></td> + <td></td> + </tr> + + <tr> + <td>Název subjektu:</td> + <td><b>{{ elem.nazev }}</b></td> + <td>IČO:</td> + <td><a href="/{{ elem.ico }}"><b>{{ elem.ico }}</b></a></td> + </tr> + + <tr> + <td>Spisová značka:</td> + <td>{{elem.oddil}} {{elem.vlozka}} vedená u {{elem.soud}}</td> + <td>Den zápisu:</td> + <td>{{elem.zapis}}</td> + </tr> + + </table> + {% set count.value = count.value + 1 %} + {% endfor %} +{% endfor %} + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/results_persons.html b/templates/results_persons.html new file mode 100644 index 0000000..abb4387 --- /dev/null +++ b/templates/results_persons.html @@ -0,0 +1,145 @@ +{% if show_form == True %} + {% include 'search_form_person.html' %} +{% else %} + {% include 'header.html' %} +{% endif %} + +{% set count = namespace(value=0) %} + {% for row in results %} + {% set test_list = [] %} + {% for elem in row.statut_org_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", elem.funkce) %} + {% set x = my_dict.__setitem__("nazev", elem.statutarni_organ.company.nazev) %} + {% set x = my_dict.__setitem__("ico", elem.statutarni_organ.company.ico) %} + {% set x = my_dict.__setitem__("oddil", elem.statutarni_organ.company.oddil) %} + {% set x = my_dict.__setitem__("vlozka", elem.statutarni_organ.company.vlozka) %} + {% set x = my_dict.__setitem__("soud", elem.statutarni_organ.company.soud) %} + {% set x = my_dict.__setitem__("zapis", elem.statutarni_organ.company.zapis) %} + {% set test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + + {% for elem in row.supervisory_board_member_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", elem.funkce) %} + {% set x = my_dict.__setitem__("nazev", elem.dozorci_rada.company.nazev) %} + {% set x = my_dict.__setitem__("ico", elem.dozorci_rada.company.ico) %} + {% set x = my_dict.__setitem__("oddil", elem.dozorci_rada.company.oddil) %} + {% set x = my_dict.__setitem__("vlozka", elem.dozorci_rada.company.vlozka) %} + {% set x = my_dict.__setitem__("soud", elem.dozorci_rada.company.soud) %} + {% set x = my_dict.__setitem__("zapis", elem.dozorci_rada.company.zapis) %} + {% set test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + + {% for elem in row.spolecnik_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", "společní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 test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + + {% for elem in row.sole_shareholder_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", "jediný akcionář") %} + {% 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 test_list = test_list.append(my_dict) %} + {% endif %} + {% endfor %} + + {% for elem in row.prokurista_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", "prokurista") %} + {% 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 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" %} + {% else %} + {% set table_style = "table table-secondary" %} + {% endif %} + + <table class= "{{table_style}}" style="width: 100%"> + <p></p> + <tr> + <td style="width:20%">Jméno:</td> + <td style="width:40%"><b>{{ row.get_name() }}</b></td> + <td style="width:15%">Datum narození:</td> + <td style="width:25%"><b>{{ row.datum_naroz }}</b></td> + </tr> + + <tr> + <td>Adresa:</td> + <td>{{ elem.adresa }}</td> + <td>Angažmá:</td> + <td>{{ elem.funkce }}</td> + </tr> + + <tr> + <td><i>ve společnosti:</i></td> + <td></td> + <td></td> + <td></td> + </tr> + + <tr> + <td>Název subjektu:</td> + <td><b>{{ elem.nazev }}</b></td> + <td>IČO:</td> + <td><a href="/{{ elem.ico }}"><b>{{ elem.ico }}</b></a></td> + </tr> + + <tr> + <td>Spisová značka:</td> + <td>{{elem.oddil}} {{elem.vlozka}} vedená u {{elem.soud}}</td> + <td>Den zápisu:</td> + <td>{{elem.zapis}}</td> + </tr> + + </table> + {% set count.value = count.value + 1 %} + {% endfor %} +{% endfor %} + + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/search_form.html b/templates/search_form.html index 1832f87..14c387d 100644 --- a/templates/search_form.html +++ b/templates/search_form.html @@ -1,3 +1,4 @@ +{% include 'header.html' %} <title>Justice Database</title> <h2>Justice Database</h2> @@ -102,4 +103,5 @@ </p> <p><input type="submit" value="Search"> </p> -</form>
\ No newline at end of file +</form> +{% include 'footer.html' %}
\ No newline at end of file diff --git a/templates/search_form_entity.html b/templates/search_form_entity.html new file mode 100644 index 0000000..70f0b5c --- /dev/null +++ b/templates/search_form_entity.html @@ -0,0 +1,68 @@ +{% include 'header.html' %} +<title>Justice Database</title> + +<h2>Justice Database</h2> + +<p></p><p> + +{% with messages = get_flashed_messages() %} + {% if messages %} + <ul class="flashes"> + {% for message in messages %} + <li>{{ message }}</li> + {% endfor %} + </ul> + {% endif %} +{% endwith %} + + +{% from "_formhelpers.html" import render_field %} + +<!-- NAME --> +<form method="post"> + <div class="row g-2"> + <div class="col-sm-3"> + {{ form.entity_name_search.label }} + </div> + <div class="col-sm"> + {{ form.entity_name_search()|safe }} {{ form.entity_name_search_selection }} + </div> + </div> + +<!-- ID No. --> + <div class="row g-2"> + <div class="col-sm-3"> + {{ form.entity_number_search.label }} + </div> + <div class="col-sm"> + {{ form.entity_number_search()|safe }} {{ form.entity_number_search_selection }} + </div> + </div> + +<!-- Foreign ID No. --> + <div class="row g-2"> + <div class="col-sm-3"> + {{ form.foreign_entity_number_search.label }} + </div> + <div class="col-sm"> + {{ form.foreign_entity_number_search()|safe }} {{ form.foreign_entity_number_search_selection }} + </div> + </div> + +<!-- Search --> + <div class="row g-2"> + <div class="col-sm-3"> + Vyhledávat údaje: + </div> + <div class="col-sm"> + {{ form.entity_actual_selection }} + </div> + </div> + </p> + <p><input type="submit" value="Search"> + </p> +</form> + +{% include 'footer.html' %} + + diff --git a/templates/search_form_person.html b/templates/search_form_person.html new file mode 100644 index 0000000..30d4865 --- /dev/null +++ b/templates/search_form_person.html @@ -0,0 +1,69 @@ +{% include 'header.html' %} +<title>Justice Database</title> + +<h2>Justice Database</h2> + +<p></p><p> + +{% with messages = get_flashed_messages() %} + {% if messages %} + <ul class="flashes"> + {% for message in messages %} + <li>{{ message }}</li> + {% endfor %} + </ul> + {% endif %} +{% endwith %} + + +{% from "_formhelpers.html" import render_field %} + +<!-- NAME --> +<form method="post"> + <div class="row g-2"> + <div class="col-sm-3"> + {{ form.fist_name_search.label }} + </div> + <div class="col-sm"> + {{ form.fist_name_search()|safe }} {{ form.fist_name_search_selection }} + </div> + </div> + +<!-- SURNAME --> + <div class="row g-2"> + <div class="col-sm-3"> + {{ form.surname_search.label }} + </div> + <div class="col-sm"> + {{ form.surname_search()|safe }} {{ form.surname_search_selection }} + </div> + </div> + +<!-- BIRTHDAY --> + <div class="row g-2"> + <div class="col-sm-3"> + {{ form.birthday.label }} + </div> + <div class="col-sm"> + {{ form.birthday }} + </div> + </div> + + <div class="row g-2"> + <div class="col-sm-3"> + Vyhledávat údaje: + </div> + <div class="col-sm"> + {{ form.person_actual_selection }} + </div> + </div> + + + </p> + <p><input type="submit" value="Search"> + </p> +</form> + +{% include 'footer.html' %} + + diff --git a/templates/trivia.html b/templates/trivia.html new file mode 100644 index 0000000..750706c --- /dev/null +++ b/templates/trivia.html @@ -0,0 +1,14 @@ +{% include 'header.html' %} + +<h1>Zajímavosti z rejstříku</h1> + +<p>Počet registrovaných osob: {{ number_entities }}</p> +<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_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> +<p><a href="/most_common_degree">Seznam nejčastějších titulů</a></p> + +{% include 'footer.html' %}
\ No newline at end of file diff --git a/todolist.html b/todolist.html index 8b83207..c86b17f 100644 --- a/todolist.html +++ b/todolist.html @@ -1,8 +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>Add some other minor data to the databases - especially those relating to non-profit companies.</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 3a9fba0..b85eb52 100644 --- a/update_db.py +++ b/update_db.py @@ -102,6 +102,8 @@ def find_other_properties(c, ICO, element, conn, primary_sql_key): find_predmet_podnikani(c, ICO, elem2, conn, primary_sql_key, element) elif udajTyp_name == "PREDMET_CINNOSTI_SEKCE": find_predmet_cinnosti(c, ICO, elem2, conn, primary_sql_key, element) + elif udajTyp_name == "UCEL_SUBJEKTU_SEKCE": + find_ucel(c, ICO, elem2, conn, primary_sql_key, element) elif udajTyp_name == "ZAKLADNI_KAPITAL": find_zakladni_kapital(c, ICO, elem2, conn, primary_sql_key, element) elif udajTyp_name == "OST_SKUTECNOSTI_SEKCE": @@ -125,7 +127,7 @@ def find_registered_office(c, ICO, elem2, conn, primary_sql_key, element): try: zapis_datum = str(get_prop(elem2, ".//zapisDatum")) vymaz_datum = str(get_prop(elem2, ".//vymazDatum")) - sidlo_id = sidlo3(c, elem2, primary_sql_key) + sidlo_id = find_sidlo(c, elem2, primary_sql_key) insert_instructions = [None,"adresy", "adresa_text", "sidlo_relation"] insert_relation_information_v2(c, insert_instructions, primary_sql_key, sidlo_id, zapis_datum, vymaz_datum) except Exception as f: @@ -209,7 +211,7 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): # I probably do not need the primary sql key spolecnik_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, primary_sql_key) + adresa_id = find_sidlo(c, elem, primary_sql_key) 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] @@ -219,7 +221,7 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element): regCislo = str(get_prop(elem, "osoba/regCislo")) spolecnik_po_id = find_pravnicka_osoba(c, elem, spol_ico, regCislo) # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, primary_sql_key) + adresa_id = find_sidlo(c, elem, primary_sql_key) 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] @@ -261,6 +263,24 @@ def find_predmet_cinnosti(c, ICO, predmet_cinnosti_elem, conn, primary_sql_key, except: pass +def find_ucel(c, ICO, ucel_elem, conn, primary_sql_key, element): + try: + my_iter = ucel_elem.findall("podudaje") + for elem in my_iter: + my_iter2 = elem.iter("Udaj") + for elem2 in my_iter2: + zapis_datum = str(get_prop(elem2, ".//zapisDatum")) + 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")) + 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) + except Exception as f: + print(f) + + def find_zakladni_kapital(c, ICO, elem2, conn, primary_sql_key, element): try: zapis_datum = str(get_prop(elem2, ".//zapisDatum")) @@ -331,7 +351,7 @@ def find_prokura(c, ICO, elem2, conn, primary_sql_key, element): vymaz_datum = str(get_prop(elem, "vymazDatum")) text_prokurista = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value")) prokurista_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) - adresa_id = sidlo3(c, elem, primary_sql_key) + adresa_id = find_sidlo(c, elem, primary_sql_key) c.execute("INSERT INTO prokuriste (company_id, zapis_datum, vymaz_datum, prokurista_fo_id, adresa_id, text_prokurista) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, prokurista_fo_id, adresa_id, text_prokurista,)) else: zapis_datum = str(get_prop(elem, "zapisDatum")) @@ -353,11 +373,11 @@ def find_sole_shareholder(c, ICO, elem2, conn, primary_sql_key, element): 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 = sidlo3(c, elem, primary_sql_key) + adresa_id = find_sidlo(c, elem, primary_sql_key) 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": akcionar_fo_id = find_fyzicka_osoba(c, ICO, elem, conn, primary_sql_key, element) - adresa_id = sidlo3(c, elem, primary_sql_key) + adresa_id = find_sidlo(c, elem, primary_sql_key) c.execute("INSERT into jediny_akcionar (company_id, zapis_datum, vymaz_datum, text_akcionar, akcionar_fo_id, adresa_id) VALUES (?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_akcionar, akcionar_fo_id, adresa_id,)) except Exception as f: print(f) @@ -396,7 +416,7 @@ def find_konkurz(c, ICO, konkurz_elem, conn, primary_sql_key): except: pass -def sidlo3(c, elem, primary_sql_key): +def find_sidlo(c, elem, primary_sql_key): try: statNazev = get_prop(elem, ".//statNazev") obec = get_prop(elem, ".//obec") @@ -479,8 +499,14 @@ def find_clen_statut_org(c, ICO, elem, conn, relationship_table_key, element): clenstviDo = str(get_prop(elem, "clenstviDo")) if typ_osoby == "AngazmaFyzicke": osoba_id = find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element) - adresa_id = sidlo3(c, elem, relationship_table_key) + adresa_id = find_sidlo(c, elem, relationship_table_key) c.execute("INSERT into statutarni_organ_clen_relation (statutarni_organ_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,)) + 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) + 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) @@ -546,8 +572,14 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element): if typ_osoby == "AngazmaFyzicke": osoba_id = find_fyzicka_osoba(c, ICO, elem, conn, relationship_table_key, element) # adresa_id = find_and_store_address(c, elem) - adresa_id = sidlo3(c, elem, relationship_table_key) + adresa_id = find_sidlo(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 = find_sidlo(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) |