From 9e70cd8362a097da0ebe03261ea56674b73af388 Mon Sep 17 00:00:00 2001
From: Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com>
Date: Sat, 26 Jun 2021 23:35:00 +0200
Subject: display common ownership interests
---
db_creation.py | 40 +++++++++++++++++++++++++++--
justice_build.py | 7 +++---
main.py | 2 +-
models.py | 17 ++++++++++++-
templates/extract-actual.html | 58 +++++++++++++++++++++++++++++++++++++++++--
templates/extract.html | 37 ++++++++++++++++++++++-----
update_db.py | 31 +++++++++++++++++++++--
7 files changed, 174 insertions(+), 18 deletions(-)
diff --git a/db_creation.py b/db_creation.py
index 70a08df..c3836aa 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -195,6 +195,21 @@ def create_tables(conn):
PRIMARY KEY("id" AUTOINCREMENT)
); """
+ podilnici = """ CREATE TABLE "podilnici" (
+ "id" INTEGER NOT NULL UNIQUE,
+ "podil_id" INTEGER,
+ "podilnik_fo_id" INTEGER,
+ "podilnik_po_id" INTEGER,
+ "zapis_datum" DATE,
+ "vymaz_datum" DATE,
+ "adresa_id" INTEGER,
+ FOREIGN KEY("adresa_id") REFERENCES "adresy_v2"("id"),
+ PRIMARY KEY("id" AUTOINCREMENT),
+ FOREIGN KEY("podilnik_fo_id") REFERENCES "fyzicke_osoby"("id"),
+ FOREIGN KEY("podilnik_po_id") REFERENCES "pravnicke_osoby"("id"),
+ FOREIGN KEY("podil_id") REFERENCES "spolecnici_spolecny_podil"("id")
+); """
+
podily = """ CREATE TABLE "podily" (
"id" INTEGER NOT NULL UNIQUE,
"spolecnik_id" INTEGER,
@@ -465,7 +480,7 @@ def create_tables(conn):
); """
list_of_tables = [companies, adresy_v2, akcie, dr_relation, dr_organ_clen_relation, druhy_podilu, fyzicke_osoby, insolvency_events,
- jediny_akcionar, konkurz_events, nazvy, ostatni_skutecnosti, pocty_clenu_DR, pocty_clenu_organu, podily, pravni_formy,
+ jediny_akcionar, konkurz_events, nazvy, ostatni_skutecnosti, pocty_clenu_DR, pocty_clenu_organu, podily, podilnici, pravni_formy,
pravni_formy_relation, pravnicke_osoby, predmety_cinnosti, predmety_cinnosti_relation, prdmety_podnikani, predmety_podnikani_relation,
prokura_common_texts, prokuriste, sidlo_relation, spolecnici, spolecnici_uvolneny_podil, spolecnici_spolecny_podil, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, ubo,
ucel, ucel_relation, zakladni_kapital, zapis_soudy, zpusoby_jednani, zpusoby_jednani_relation]
@@ -601,6 +616,26 @@ def create_indices(conn):
"spolecny_podil_id"
); """
+ podilnici1 = """ CREATE INDEX "index podilnici1" ON "podilnici" (
+ "id"
+); """
+
+ podilnici2 = """ CREATE INDEX "index podilnici2" ON "podilnici" (
+ "podil_id"
+); """
+
+ podilnici3 = """ CREATE INDEX "index podilnici3" ON "podilnici" (
+ "podilnik_fo_id"
+); """
+
+ podilnici4 = """ CREATE INDEX "index podilnici4" ON "podilnici" (
+ "podilnik_po_id"
+); """
+
+ podilnici5 = """ CREATE INDEX "index podilnici5" ON "podilnici" (
+ "adresa_id"
+); """
+
pravni_formy = """ CREATE INDEX "index pravni_formy" ON "pravni_formy" (
"pravni_forma"
); """
@@ -847,7 +882,8 @@ def create_indices(conn):
list_of_indices = [companies1, companies2, companies3, companies4, companies5, adresy1, adresy2, adresy3,
akcie, akcie2, akcionari1, akcionari2, akcionari3, dr_clen_relation1, dr_clen_relation2, dr_relation, dr_relation2, dr_relation_3, dr_relation_4,
insolvency1, insolvency2, konkurz1, konkurz2, nazvy1, nazvy2, nazvy3, ostatni_skutecnosti, ostatni_skutecnosti2,
- pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, podily3, podily4, pravni_formy, pravni_formy_relation1, pravni_formy_relation2,
+ pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, podily3, podily4, podilnici1, podilnici2, podilnici3, podilnici4, podilnici5,
+ 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,
diff --git a/justice_build.py b/justice_build.py
index 4b82660..9acf1e7 100644
--- a/justice_build.py
+++ b/justice_build.py
@@ -8,11 +8,10 @@ def main():
DB_name = "justice.db"
# backup_DB()
create_DB(DB_name)
- # valid_files = get_valid_filenames()
- valid_files = ["sro-full-ceske_budejovice-2021"]
+ valid_files = get_valid_filenames()
os.makedirs("data", exist_ok=True)
- # for valid_file in valid_files:
- # download_data(valid_file)
+ for valid_file in valid_files:
+ download_data(valid_file)
for valid_file in valid_files:
modified_file_name = os.path.join(str(os.getcwd()), "data", valid_file + ".xml")
update_DB(modified_file_name, DB_name)
diff --git a/main.py b/main.py
index 31dde42..a4d8293 100644
--- a/main.py
+++ b/main.py
@@ -5,7 +5,7 @@ from models import Company, Insolvency_Events, Konkurz_Events, Predmet_Podnikani
from models import Zakladni_Kapital, Akcie, Nazvy, Sidlo, Sidlo_Association, Pravni_Forma_Association_v2, Pravni_Formy, Statutarni_Organ_Association, Statutarni_Organy, Pocty_Clenu_Organu
from models import Zpusob_Jednani_Association, Zpusob_Jednani, Statutarni_Organ_Clen_Association, Fyzicka_Osoba, Spolecnici_Association, Podily_Association, Druhy_Podilu, Pravnicka_Osoba
from models import Prokurista_Association, Dozorci_Rada_Clen_Association, Jediny_Akcionar_Association, Prokura_Common_Text_Association, Soudni_Zapisy, Ucel, Ucel_Association
-from models import Adresy_v2, Uvolneny_Podil_Association, Spolecny_Podil_Association
+from models import Adresy_v2, Uvolneny_Podil_Association, Spolecny_Podil_Association, Podilnici_Association
from tables import Results
from sqlalchemy.sql import select
from sqlalchemy.sql import text
diff --git a/models.py b/models.py
index a6d2eaf..400e5de 100644
--- a/models.py
+++ b/models.py
@@ -301,6 +301,21 @@ class Spolecnici_Association(db.Model):
podily = db.relationship("Podily_Association")
company = db.relationship("Company")
+class Podilnici_Association(db.Model):
+ __tablename__ = "podilnici"
+ id = db.Column(db.Integer, primary_key=True)
+ podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_spolecny_podil.id'))
+ podilnik_fo_id = db.Column(db.Integer, db.ForeignKey('fyzicke_osoby.id'))
+ podilnik_po_id = db.Column(db.Integer, db.ForeignKey('pravnicke_osoby.id'))
+ zapis_datum = db.Column(MyType)
+ vymaz_datum = db.Column(MyType)
+ adresa_id = db.Column(db.Integer, db.ForeignKey('adresy_v2.id'))
+ # text_podilnik = db.Column(db.String)
+ adresa = db.relationship("Adresy_v2")
+ jmeno = db.relationship("Fyzicka_Osoba")
+ oznaceni_po = db.relationship("Pravnicka_Osoba")
+ podily = db.relationship("Spolecny_Podil_Association")
+
class Uvolneny_Podil_Association(db.Model):
__tablename__ = "spolecnici_uvolneny_podil"
id = db.Column(db.Integer, primary_key=True)
@@ -319,9 +334,9 @@ class Spolecny_Podil_Association(db.Model):
vymaz_datum = db.Column(MyType)
text_spolecny_podil = db.Column(db.String)
podily = db.relationship("Podily_Association")
+ podilnici = db.relationship("Podilnici_Association")
company = db.relationship("Company")
-
class Ubo(db.Model):
__tablename__ = "ubo"
id = db.Column(db.Integer, primary_key=True)
diff --git a/templates/extract-actual.html b/templates/extract-actual.html
index f24cb80..691dae3 100644
--- a/templates/extract-actual.html
+++ b/templates/extract-actual.html
@@ -49,7 +49,6 @@
{% endif %}
{% endfor %}
-
{% set vacant_shares_notes = [] %}
{% for i in range (row.spolecnici_uvolneny_podil|length) %}
{% if row.spolecnici_uvolneny_podil[i].vymaz_datum == 0 %}
@@ -57,6 +56,13 @@
{% endif %}
{% endfor %}
+{% set common_shares_notes = [] %}
+{% for i in range (row.spolecnici_spolecny_podil|length) %}
+ {% if row.spolecnici_spolecny_podil[i].vymaz_datum == 0 %}
+ {% set common_shares_notes = common_shares_notes.append(row.spolecnici_spolecny_podil[i]) %}
+ {% endif %}
+{% endfor %}
+
{% set sole_shareholder_notes = [] %}
{% for i in range (row.jediny_akcionar|length) %}
{% if row.jediny_akcionar[i].vymaz_datum == 0 %}
@@ -376,7 +382,7 @@
- {% if spolecnici_notes|length > 0 or vacant_shares_notes|length > 0 %}
+ {% if spolecnici_notes|length > 0 or vacant_shares_notes|length > 0 or common_shares_notes|length > 0 %}
| Společníci: |
|
@@ -433,6 +439,54 @@
{% endfor %}
+
+ {% for i in range (common_shares_notes|length) %}
+
+ | Společný obchodní podíl: |
+ {% if common_shares_notes[i].text_spolecny_podil != "0" %}{{ common_shares_notes[i].text_spolecny_podil }}{% endif %} |
+
+ {% set common_shares = [] %}
+ {% for j in range (common_shares_notes[i].podily|length) %}
+ {% if common_shares_notes[i].podily[j].vymaz_datum == 0 %}
+ {% set common_shares = common_shares.append(common_shares_notes[i].podily[j]) %}
+ {% endif %}
+ {% endfor %}
+
+ {% for k in range (common_shares|length) %}
+
+ | Podíl: |
+
+ {% for elem in common_shares[k].my_rep() %}
+ {{ elem }}
+ {% endfor %}
+ |
+
+ {% endfor %}
+
+ {% set common_shares_shareholders = [] %}
+ {% for j in range (common_shares_notes[i].podilnici|length) %}
+ {% if common_shares_notes[i].podilnici[j].vymaz_datum == 0 %}
+ {% set common_shares_shareholders = common_shares_shareholders.append(common_shares_notes[i].podilnici[j]) %}
+ {% endif %}
+ {% endfor %}
+
+ {% for k in range (common_shares_shareholders|length) %}
+
+ | Podílník: |
+
+ {% if common_shares_shareholders[k].podilnik_fo_id != None %}
+ {{ common_shares_shareholders[k].jmeno }}
+ {% else %}
+ {{ common_shares_shareholders[k].oznaceni_po.nazev }}{% if common_shares_shareholders[k].oznaceni_po.reg_cislo != 0 %}, reg č. {{ common_shares_shareholders[k].oznaceni_po.reg_cislo }}{% endif %}{% if common_shares_shareholders[k].oznaceni_po.ico != 0 %}, IČ {{ common_shares_shareholders[k].oznaceni_po.ico }}{% endif %}
+ {% endif %}
+
{{ common_shares_shareholders[k].adresa }}
+ |
+
+ {% endfor %}
+
+ {% endfor %}
+
+
{% endif %}
diff --git a/templates/extract.html b/templates/extract.html
index ec58487..9eb1985 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -530,7 +530,7 @@
{{ underlne_style_open|safe }} Zapsáno: {{ row.spolecnici_spolecny_podil[i].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].vymaz_datum != 0 %}
Vymazáno: {{ row.spolecnici_spolecny_podil[i].vymaz_datum }} {% endif %} {{ underlne_style_close|safe }} |
-
+
{% if row.spolecnici_spolecny_podil[i].podily|length > 0 %}
{% for j in range (row.spolecnici_spolecny_podil[i].podily|length) %}
@@ -553,15 +553,40 @@
{% endfor %}
{% endif %}
-
- {% endfor %}
+
+
+ {% if row.spolecnici_spolecny_podil[i].podilnici|length > 0 %}
+
+ {% for j in range (row.spolecnici_spolecny_podil[i].podilnici|length) %}
+
+ | Podílník: |
+ {% if row.spolecnici_spolecny_podil[i].podilnici[j].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 %}
+ {{ underlne_style_open|safe }}
+ {% if row.spolecnici_spolecny_podil[i].podilnici[j].podilnik_fo_id != None %}
+ {{ row.spolecnici_spolecny_podil[i].podilnici[j].jmeno }}
+ {% else %}
+ {{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.nazev }}{% if row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.reg_cislo != 0 %}, reg č. {{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.reg_cislo }}{% endif %}{% if row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico != 0 %}, IČ {{ row.spolecnici_spolecny_podil[i].podilnici[j].oznaceni_po.ico }}{% endif %}
+ {% endif %}
+
{{ row.spolecnici_spolecny_podil[i].podilnici[j].adresa }}
+ {{ underlne_style_close|safe }}
+ |
+ {{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_spolecny_podil[i].podilnici[j].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum != 0 %}
Vymazáno: {{ row.spolecnici_spolecny_podil[i].podilnici[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }} |
+
+ {% endfor %}
+{% endif %}
+
+ {% endfor %}
-
-
-{% endif %}
+ {% endif %}
diff --git a/update_db.py b/update_db.py
index df844f6..8444e5b 100644
--- a/update_db.py
+++ b/update_db.py
@@ -238,12 +238,15 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
c.execute ("SELECT last_insert_rowid()")
spolecnik_id = c.fetchone()[0]
insert_podily(c, elem, spolecnik_id)
+
elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "SPOLECNY_PODIL":
text_spolecny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))
c.execute("INSERT INTO spolecnici_spolecny_podil (company_id, zapis_datum, vymaz_datum, text_spolecny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_spolecny_podil,))
c.execute ("SELECT last_insert_rowid()")
- uvolneny_op_id = c.fetchone()[0]
- insert_common_podily(c, elem, uvolneny_op_id)
+ spolecny_op_id = c.fetchone()[0]
+ insert_common_podily(c, elem, spolecny_op_id)
+ insert_common_shareholders(c, elem, spolecny_op_id)
+
elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "UVOLNENY_PODIL":
text_uvolneny_podil = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))
c.execute("INSERT INTO spolecnici_uvolneny_podil (company_id, zapis_datum, vymaz_datum, text_uvolneny_podil) VALUES (?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, text_uvolneny_podil,))
@@ -253,6 +256,30 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
except Exception as f:
print(f)
+def insert_common_shareholders(c, elem, spolecny_op_id):
+ try:
+ podil_iter = elem.findall("podudaje/Udaj")
+ for podil_elem in podil_iter:
+ if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODILNIK":
+ zapisDatum = str(get_prop(podil_elem, "zapisDatum"))
+ vymazDatum = str(get_prop(podil_elem, "vymazDatum"))
+ typ_podilnika = str(get_prop(podil_elem, "hodnotaText"))
+ if typ_podilnika == "AngazmaFyzicke":
+ adresa_id = find_sidlo(c, podil_elem, spolecny_op_id)
+ spolecnik_fo_id = find_fyzicka_osoba(c, 0, podil_elem, 0, spolecny_op_id, 0, adresa_id)
+ c.execute("INSERT INTO podilnici (podil_id, podilnik_fo_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_fo_id, zapisDatum, vymazDatum, adresa_id))
+ if typ_podilnika == "AngazmaPravnicke":
+ spol_ico = str(get_prop(podil_elem, "osoba/ico"))
+ regCislo = str(get_prop(podil_elem, "osoba/regCislo"))
+ adresa_id = find_sidlo(c, podil_elem, spolecny_op_id)
+ spolecnik_po_id = find_pravnicka_osoba(c, podil_elem, spol_ico, regCislo, adresa_id)
+ c.execute("INSERT INTO podilnici (podil_id, podilnik_po_id, zapis_datum, vymaz_datum, adresa_id) VALUES (?, ?, ?, ?, ?)", (spolecny_op_id, spolecnik_po_id, zapisDatum, vymazDatum, adresa_id))
+ except Exception as f:
+ print(f)
+
+
+
+
def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key, element):
try:
my_iter = predmet_podnikani_elem.findall("podudaje")
--
cgit