From 00fd5abb4c35f4b035b30787f5c1788290c7933a Mon Sep 17 00:00:00 2001
From: Petr Šmerkl <46304018+SveterCZE@users.noreply.github.com>
Date: Sun, 20 Jun 2021 18:14:38 +0200
Subject: interim solution for common ownership interest
---
db_creation.py | 32 +++++++++++++++++++++++++++-----
main.py | 2 +-
models.py | 13 +++++++++++++
templates/extract.html | 48 ++++++++++++++++++++++++++++++++++++++++++++++++
update_db.py | 28 +++++++++++++++++++++++++---
5 files changed, 114 insertions(+), 9 deletions(-)
diff --git a/db_creation.py b/db_creation.py
index 0e6299a..70a08df 100644
--- a/db_creation.py
+++ b/db_creation.py
@@ -199,6 +199,7 @@ def create_tables(conn):
"id" INTEGER NOT NULL UNIQUE,
"spolecnik_id" INTEGER,
"uvolneny_podil_id" INTEGER,
+ "spolecny_podil_id" INTEGER,
"zapis_datum" DATE,
"vymaz_datum" DATE,
"druh_podilu_id" INTEGER,
@@ -336,6 +337,15 @@ def create_tables(conn):
FOREIGN KEY("company_id") REFERENCES "companies"("id")
) """
+ spolecnici_spolecny_podil = """ CREATE TABLE "spolecnici_spolecny_podil" (
+ "id" INTEGER NOT NULL UNIQUE,
+ "company_id" INTEGER NOT NULL,
+ "zapis_datum" DATE,
+ "vymaz_datum" DATE,
+ "text_spolecny_podil" TEXT,
+ PRIMARY KEY("id" AUTOINCREMENT),
+ FOREIGN KEY("company_id") REFERENCES "companies"("id")
+ ) """
statutarni_organ_clen_relation = """ CREATE TABLE "statutarni_organ_clen_relation" (
"id" INTEGER NOT NULL UNIQUE,
@@ -457,7 +467,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,
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, statutarni_organ_clen_relation, statutarni_organ_relation, statutarni_organy, ubo,
+ 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]
for elem in list_of_tables:
try:
@@ -587,6 +597,10 @@ def create_indices(conn):
"uvolneny_podil_id"
); """
+ podily4 = """ CREATE INDEX "index podily4" ON "podily" (
+ "spolecny_podil_id"
+); """
+
pravni_formy = """ CREATE INDEX "index pravni_formy" ON "pravni_formy" (
"pravni_forma"
); """
@@ -719,6 +733,15 @@ def create_indices(conn):
"company_id"
); """
+ spolecnici_spolecny_podil1 = """ CREATE INDEX "index spolecny_podil1" on "spolecnici_spolecny_podil" (
+ "id"
+); """
+
+ spolecnici_spolecny_podil2 = """ CREATE INDEX "index spolecny_podil2" on "spolecnici_spolecny_podil" (
+ "company_id"
+); """
+
+
statutarni_organy = """ CREATE INDEX "index statutarn_organy" ON "statutarni_organy" (
"id",
"statutarni_organ_text"
@@ -813,7 +836,6 @@ def create_indices(conn):
"osoba_id"
); """
-
dr_relation_4 = """ CREATE INDEX "index dr clen relation4" ON "dr_organ_clen_relation" (
"pravnicka_osoba_id"
); """
@@ -825,13 +847,13 @@ 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, pravni_formy, pravni_formy_relation1, pravni_formy_relation2,
+ pocty_clenu_organ1, pocty_clenu_organ2, podily1, podily2, podily3, podily4, 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, spolecnici_uvolneny_podil1, spolecnici_uvolneny_podil2, statutarni_organy, statutarni_organy_relation1, statutarni_organy_relation2,
+ spolecnici2, spolecnici3, spolecnici4, spolecnici5, spolecnici_uvolneny_podil1, spolecnici_uvolneny_podil2, spolecnici_spolecny_podil1, spolecnici_spolecny_podil2, statutarni_organy, statutarni_organy_relation1, statutarni_organy_relation2,
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, statutarni_organy_relation_5, fyzicke_osoby1, ubo1, ubo2, ubo3, ucel1, ucel2, ucel_relation1, ucel_relation2, ucel_relation3]
+ zpusob_jednani_relation3, pravnicke_osoby1, pravnicke_osoby2, pravnicke_osoby3, pravnicke_osoby4, statutarni_organy_relation_5, fyzicke_osoby1, ubo1, ubo2, ubo3, ubo4, ucel1, ucel2, ucel_relation1, ucel_relation2, ucel_relation3]
i = 0
for elem in list_of_indices:
i += 1
diff --git a/main.py b/main.py
index 80b598e..31dde42 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
+from models import Adresy_v2, Uvolneny_Podil_Association, Spolecny_Podil_Association
from tables import Results
from sqlalchemy.sql import select
from sqlalchemy.sql import text
diff --git a/models.py b/models.py
index 4b94374..a6d2eaf 100644
--- a/models.py
+++ b/models.py
@@ -311,6 +311,17 @@ class Uvolneny_Podil_Association(db.Model):
podily = db.relationship("Podily_Association")
company = db.relationship("Company")
+class Spolecny_Podil_Association(db.Model):
+ __tablename__ = "spolecnici_spolecny_podil"
+ 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)
+ text_spolecny_podil = db.Column(db.String)
+ podily = db.relationship("Podily_Association")
+ company = db.relationship("Company")
+
+
class Ubo(db.Model):
__tablename__ = "ubo"
id = db.Column(db.Integer, primary_key=True)
@@ -367,6 +378,7 @@ class Podily_Association(db.Model):
id = db.Column(db.Integer, primary_key=True)
spolecnik_id = db.Column(db.Integer, db.ForeignKey('spolecnici.id'))
uvolneny_podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_uvolneny_podil.id'))
+ spolecny_podil_id = db.Column(db.Integer, db.ForeignKey('spolecnici_spolecny_podil.id'))
zapis_datum = db.Column(MyType)
vymaz_datum = db.Column(MyType)
druh_podilu_id = db.Column(db.Integer, db.ForeignKey('druhy_podilu.id'))
@@ -421,6 +433,7 @@ class Company(db.Model):
dozorci_rada_text = db.relationship("Dozorci_Rada_Association")
spolecnici = db.relationship("Spolecnici_Association")
spolecnici_uvolneny_podil = db.relationship("Uvolneny_Podil_Association")
+ spolecnici_spolecny_podil = db.relationship("Spolecny_Podil_Association")
prokurista = db.relationship("Prokurista_Association")
prokura_common_text = db.relationship("Prokura_Common_Text_Association")
jediny_akcionar = db.relationship("Jediny_Akcionar_Association")
diff --git a/templates/extract.html b/templates/extract.html
index 2b27462..ec58487 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -513,6 +513,54 @@
{% endfor %}
+
+
+ {% for i in range (row.spolecnici_spolecny_podil|length) %}
+
+ | Společný obchodní podíl: |
+ {% if row.spolecnici_spolecny_podil[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 %}
+ {{ underlne_style_open|safe }} {% if row.spolecnici_spolecny_podil[i].text_uvolneny_podil != "0" %}{{ row.spolecnici_spolecny_podil[i].text_spolecny_podil }}{% endif %}
+ {{ underlne_style_close|safe }} |
+ {{ 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) %}
+
+ | Podíl: |
+ {% if row.spolecnici_spolecny_podil[i].podily[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 }}
+ {% for elem in row.spolecnici_spolecny_podil[i].podily[j].my_rep() %}
+ {{ elem }}
+ {% endfor %}
+ {{ underlne_style_close|safe }}
+ |
+ {{ underlne_style_open|safe }}Zapsáno: {{ row.spolecnici_spolecny_podil[i].podily[j].zapis_datum }} {% if row.spolecnici_spolecny_podil[i].podily[j].vymaz_datum != 0 %}
Vymazáno: {{ row.spolecnici_spolecny_podil[i].podily[j].vymaz_datum }}{% endif %}{{ underlne_style_close|safe }} |
+
+ {% endfor %}
+ {% endif %}
+
+ {% endfor %}
+
+
+
+
+
+
{% endif %}
diff --git a/update_db.py b/update_db.py
index 43dac45..df844f6 100644
--- a/update_db.py
+++ b/update_db.py
@@ -239,9 +239,11 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
spolecnik_id = c.fetchone()[0]
insert_podily(c, elem, spolecnik_id)
elif spolecnik_kod == "SPOLECNIK_OSOBA" and spolecnik_typ == "SPOLECNY_PODIL":
- pass
- # text_spolecnik = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))
- # print(text_spolecnik)
+ 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)
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,))
@@ -618,6 +620,7 @@ def find_clen_dr(c, ICO, elem, conn, relationship_table_key, element):
except Exception as f:
print(f)
+# TODO MERGE THESE THREE FUNCTIONS INTO ONE
def insert_podily(c, elem, spolecnik_id):
try:
podil_iter = elem.findall("podudaje/Udaj")
@@ -652,6 +655,25 @@ def insert_vacant_podily(c, elem, vacant_id):
except Exception as f:
print(f)
+def insert_common_podily(c, elem, common_id):
+ try:
+ podil_iter = elem.findall("podudaje/Udaj")
+ for podil_elem in podil_iter:
+ if str(get_prop(podil_elem, "udajTyp/kod")) == "SPOLECNIK_PODIL":
+ zapisDatum = str(get_prop(podil_elem, "zapisDatum"))
+ vymazDatum = str(get_prop(podil_elem, "vymazDatum"))
+ druh_podilu_id = get_druh_podilu_id(c, podil_elem)
+ vklad_typ = str(get_prop(podil_elem, "hodnotaUdaje/vklad/typ"))
+ vklad_text = str(get_prop(podil_elem, "hodnotaUdaje/vklad/textValue"))
+ souhrn_typ = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/typ"))
+ souhrn_text = str(get_prop(podil_elem, "hodnotaUdaje/souhrn/textValue"))
+ splaceni_typ = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/typ"))
+ splaceni_text = str(get_prop(podil_elem, "hodnotaUdaje/splaceni/textValue"))
+ c.execute("INSERT INTO podily (spolecny_podil_id, zapis_datum, vymaz_datum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text) VALUES (?,?,?,?,?,?,?,?,?,?)", (common_id, zapisDatum, vymazDatum, druh_podilu_id, vklad_typ, vklad_text, souhrn_typ, souhrn_text, splaceni_typ, splaceni_text,))
+ except Exception as f:
+ print(f)
+
+
def get_druh_podilu_id(c, podil_elem):
try:
druhPodilu = str(get_prop(podil_elem, "hodnotaUdaje/druhPodilu"))
--
cgit