various tweaks and updates

7 files changed, 111 insertions, 56 deletions

diff --git a/app.py b/app.py
index bc8d051..c89b6e6 100644
--- a/app.py
+++ b/app.py
@@ -8,10 +8,13 @@ Created on Sun Jan 17 09:38:19 2021
 
 # app.py
 from flask import Flask
+from flask_debugtoolbar import DebugToolbarExtension
 from flask_sqlalchemy import SQLAlchemy
 
 app = Flask(__name__)
 app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///justice.db'
 app.config["SQLALCHEMY_ECHO"] = True
 app.secret_key = "123456"
+app.debug = True
+toolbar = DebugToolbarExtension(app)
 db = SQLAlchemy(app)
\ No newline at end of file
diff --git a/justice_main.py b/justice_main.py
index 38d6cce..823df32 100644
--- a/justice_main.py
+++ b/justice_main.py
@@ -22,10 +22,10 @@ def parse_to_DB(file):
         else:

             ICO = get_ICO(element)

             # Vlozit prazdny radek s ICO

-            insert_new_ICO(c, ICO, conn)

+            insert_new_ICO(c, ICO, conn, element)

             primary_sql_key = get_primary_sql_key(c, ICO)

             # Vlozit jednolive parametry

-            insert_primary_company_figures(c, ICO, element, conn)

+            # insert_primary_company_figures(c, ICO, element, conn)

             insert_company_relations(c, ICO, element, conn, primary_sql_key)

             # insert_obec_relation(c, conn, ICO, element, primary_sql_key)

             find_other_properties(c, ICO, element, conn, primary_sql_key)

@@ -41,6 +41,7 @@ def purge_DB():
         conn = sqlite3.connect('justice.db')

         c = conn.cursor()

         c.execute("DELETE FROM adresy")

+        c.execute("DELETE FROM adresy_v2")

         c.execute("DELETE FROM akcie")

         c.execute("DELETE FROM companies")

         c.execute("DELETE FROM dozorci_rada_relation")

@@ -183,10 +184,11 @@ def find_spolecnik(c, ICO, elem2, conn, primary_sql_key, element):
             spolecnik_type = str(get_prop(elem, "udajTyp/kod"))

             zapis_datum = str(get_prop(elem, "zapisDatum"))

             vymaz_datum = str(get_prop(elem, "vymazDatum"))

-            spolecnik_oznaceni = str(get_prop(elem, "hlavicka"))

+            # spolecnik_oznaceni = str(get_prop(elem, "hlavicka"))

+            spolecnik_typ =  str(get_prop(elem, "hodnotaUdaje/typ"))

             # TODO Chech these conditions, they sometimes cause a person not being stored (IC 27650081)

             # if spolecnik_type == "SPOLECNIK_OSOBA" and spolecnik_oznaceni == "Společník":

-            if spolecnik_type == "SPOLECNIK_OSOBA":

+            if spolecnik_type == "SPOLECNIK_OSOBA" and spolecnik_typ == "OSOBA":

                 # TODO alternativy pro None, Spolecny podil a Uvolneny podil

                 text_spolecnik = str(get_prop(elem, "hodnotaUdaje/textZaOsobu/value"))

                 nazev = str(get_prop(elem, "osoba/nazev"))

@@ -502,6 +504,9 @@ def find_registered_office(c, ICO, elem2, conn, primary_sql_key, element):
         vymaz_datum = str(get_prop(elem2, ".//vymazDatum"))

         sidlo = str(adresa(get_SIDLO_v3(elem2)))

         if vymaz_datum == "0":

+            sidlo2(c, elem2, primary_sql_key)

+            # Insert current seat into the main table

+            c.execute("UPDATE companies SET sidlo = (?) WHERE id = (?)",(sidlo,primary_sql_key,))

             insert_prop(c, sidlo, conn, ICO, "sidlo")

             obec = str(get_prop(elem2, ".//adresa/obec"))

             insert_instructions = [(obec,"obce", "obec_jmeno", "obce_relation")]

@@ -522,10 +527,24 @@ def find_registered_office(c, ICO, elem2, conn, primary_sql_key, element):
             insert_relation_information_v2(c, elem, primary_sql_key, ancillary_table_key, zapis_datum, vymaz_datum)

         return 0

     except Exception as f:

-        if ICO == "49790498":

-            print(f)

-        else:

-            pass

+        print(f)

+

+def sidlo2(c, elem, primary_sql_key):

+    try:

+        statNazev = get_prop(elem, ".//statNazev")

+        obec = get_prop(elem, ".//obec")

+        ulice = get_prop(elem, ".//ulice")

+        castObce = get_prop(elem, ".//castObce")

+        cisloPo = get_prop(elem, ".//cisloPo")

+        cisloOr = get_prop(elem, ".//cisloOr")

+        psc = get_prop(elem, ".//psc")

+        okres = get_prop(elem, ".//okres")

+        adresaText = get_prop(elem, ".//adresaText")

+        cisloEv = get_prop(elem, ".//cisloEv")

+        cisloText = get_prop(elem, ".//cisloText")

+        c.execute("INSERT INTO adresy_v2 (stat, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, komplet_adresa, cisloEv, cisloText, company_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (statNazev, obec, ulice, castObce, cisloPo, cisloOr, psc, okres, adresaText, cisloEv, cisloText, primary_sql_key))

+    except Exception as f:

+        print(f)

 

 def find_predmet_podnikani(c, ICO, predmet_podnikani_elem, conn, primary_sql_key, element):

     try:

@@ -571,6 +590,8 @@ def find_sp_zn(c, ICO, elem2, conn, primary_sql_key, element):
         oddil = str(get_prop(elem2, ".//spisZn/oddil"))

         vlozka = str(get_prop(elem2, ".//spisZn/vlozka"))

         c.execute("INSERT INTO zapis_soudy (company_id, zapis_datum, vymaz_datum, oddil, vlozka, soud) VALUES(?, ?, ?, ?, ?, ?)", (primary_sql_key, zapis_datum, vymaz_datum, oddil, vlozka, soud,))

+        if vymaz_datum == "0":

+            c.execute("UPDATE companies SET oddil = (?), vlozka = (?), soud = (?) WHERE id = (?)",(oddil,vlozka,soud,primary_sql_key,))

     except:

         pass

 

@@ -821,17 +842,19 @@ def insert_prop_v2(c, prop, conn, ICO, column, table, sql_id):
 

 

 # Function to attempt to insert a placeholder for a new company based on ICO

-def insert_new_ICO(c, ICO, conn):

+def insert_new_ICO(c, ICO, conn, element):

 

     try:

-        c.execute("INSERT INTO companies (ico) VALUES (?);", (ICO,))

+        datum_zapis = str(get_prop(element, "zapisDatum"))

+        nazev = str(get_prop(element, "nazev"))

+        c.execute("INSERT INTO companies (ico, zapis, nazev) VALUES (?,?,?);", (ICO,datum_zapis,nazev,))

         return c.lastrowid

 

     # c.execute("INSERT INTO companies VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (ICO, "", "", "", "", "", "", "", "", "", ""))

 

     #     # conn.commit()

-    except:

-           pass

+    except Exception as f:

+           print(f)

 

 def get_ICO(element):

     try:

@@ -1111,7 +1134,7 @@ def delete_archive(file):
 

 purge_DB()

 

-# parse_to_DB("data/sro-full-ceske_budejovice-2021.xml")

+parse_to_DB("data/as-full-ceske_budejovice-2021.xml")

 # parse_to_DB("data/sro-full-ceske_budejovice-2021.xml")

 

 # parse_to_DB("sro-actual-praha-2020.xml")

@@ -1120,6 +1143,6 @@ def do_both():
     general_update("down")

     general_update("db_update")

 

-do_both()

+# do_both()

 

 # cProfile.run('general_update("db_update")')
\ No newline at end of file
diff --git a/main.py b/main.py
index 1013d0d..27523f4 100644
--- a/main.py
+++ b/main.py
@@ -4,10 +4,11 @@ from db_setup import init_db, db_session
 from forms import JusticeSearchForm, CompanyForm
 from flask import flash, render_template, request, redirect
 # from models import Company, Soud
-from models import Company, Obce, Ulice, Insolvency_Events, Predmet_Podnikani, Predmety_Podnikani_Association, Predmet_Cinnosti, Predmety_Cinnosti_Association 
+from models import Company, Obce, Ulice, 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 Adresy_v2
 from tables import Results
 
 init_db()
@@ -52,13 +53,21 @@ def search_results(search):
     soud = search.soud_search.data
     soud_actual_or_full = search.soud_search_actual.data
 
-
     insolvent_only = search.insolvent_only_search.data
     
     zapsano_od = search.zapis_od.data
     zapsano_do = search.zapis_do.data
     
     qry = Company.query
+
+    if insolvent_only:
+        qry = qry.join(Insolvency_Events, Company.insolvence)
+        qry = qry.filter(Insolvency_Events.vymaz_datum == 0)
+        qry_konkurz = Company.query
+        qry_konkurz = qry_konkurz.join(Konkurz_Events, Company.konkurz)
+        qry_konkurz = qry_konkurz.filter(Konkurz_Events.vymaz_datum == 0)
+        qry = qry.union(qry_konkurz)
+
     if ico:
         if ico_search_method == "text_anywhere":
             qry = qry.filter(Company.ico.contains(ico))
@@ -133,8 +142,6 @@ def search_results(search):
         
         # qry = qry.filter(Company.soud.contains(soud))
     
-    
-    
     if zapsano_od:
         qry = qry.filter(Company.zapis >= zapsano_od)
     if zapsano_do:
diff --git a/models.py b/models.py
index c939652..ed336ec 100644
--- a/models.py
+++ b/models.py
@@ -115,19 +115,6 @@ ulice_association = db.Table("ulice_relation",
                                 # db.PrimaryKeyConstraint('company_id', 'obec_id')
                                 )
 
-# pravni_forma_association=db.Table("pravni_formy_relation",
-#                                   db.Column("company_id", db.Integer, db.ForeignKey("companies.id"), primary_key=True, nullable=False),
-#                                   db.Column("pravni_forma_id", db.Integer, db.ForeignKey("pravni_formy.id"), nullable=False),
-#                                   )
-
-# predmety_podnikani_association = db.Table("predmety_podnikani_relation",
-#                                 db.Column("company_id", db.Integer, db.ForeignKey("companies.id"), primary_key=True, nullable=False),
-#                                 db.Column("predmet_podnikani_id", db.Integer, db.ForeignKey("predmety_podnikani.id"), nullable=False),
-#                                 db.Column("zapis_datum", db.String),
-#                                 db.Column("vymaz_datum", db.String),
-#                                 )
-
-
 class Predmety_Podnikani_Association(db.Model):
     __tablename__ = 'predmety_podnikani_relation'
     id = db.Column(db.Integer)
@@ -309,10 +296,9 @@ class Company(db.Model):
     sidlo = db.Column(db.String)
     oddil = db.Column(db.String)
     vlozka = db.Column(db.String)
-    soud = db.Column(db.String)
+    soud = db.Column(MySoud)
     obec = db.relationship("Obce", secondary=association_table, backref="companies")
     ulice = db.relationship("Ulice", secondary=ulice_association, backref="companies")
-    # pravni_forma = db.relationship("Pravni_Forma", secondary=pravni_forma_association, backref="companies")
     insolvence = db.relationship("Insolvency_Events", backref="companies")
     konkurz = db.relationship("Konkurz_Events", backref="companies")
     predmet_podnikani = db.relationship("Predmety_Podnikani_Association", back_populates="company")
@@ -330,6 +316,53 @@ class Company(db.Model):
     prokurista = db.relationship("Prokurista_Association")
     prokura_common_text = db.relationship("Prokura_Common_Text_Association")
     jediny_akcionar = db.relationship("Jediny_Akcionar_Association")
+    sidlo_v2 = db.relationship("Adresy_v2")
+
+class Adresy_v2(db.Model):
+    __tablename__ = "adresy_v2"
+    id = db.Column(db.Integer, primary_key=True)
+    company_id = db.Column(db.String, db.ForeignKey("companies.id"))
+    stat = db.Column(db.String)
+    obec = db.Column(db.String)
+    ulice = db.Column(db.String)
+    castObce = db.Column(db.String)
+    cisloPo = db.Column(db.Integer)
+    cisloOr = db.Column(db.Integer)
+    psc = db.Column(db.String)
+    okres = db.Column(db.String)
+    komplet_adresa = db.Column(db.String)
+    cisloEv = db.Column(db.Integer)
+    cisloText = db.Column(db.String)
+
+    def __repr__(self):
+        joined_address = ""
+        if self.komplet_adresa != "0":
+            return self.komplet_adresa
+        if self.ulice != "0":
+            joined_address += self.ulice + " "
+        if self.cisloText != "0":
+            joined_address += self.cisloText + ", "   
+        if self.cisloPo != 0:
+            if self.ulice == "0":
+                joined_address += "č.p. "
+            joined_address += str(self.cisloPo)
+            if self.cisloOr != 0:
+                joined_address += "/"
+            else:
+                joined_address += ", "
+        if self.cisloOr != 0:
+            joined_address += str(self.cisloOr) + ", "
+        if self.cisloEv != 0:
+            joined_address += str(self.cisloEv) + ", "
+        if (self.castObce != "0") and (self.castObce != self.obec):
+            joined_address += self.castObce + ", "
+        if self.psc != "0":
+            joined_address += self.psc + " "
+        if self.obec != "0":
+            joined_address += self.obec
+        if (self.stat != "Česká republika") and (self.stat != "Česká republika - neztotožněno"):
+            joined_address += ", " + self.stat
+        return joined_address
 
 class Obce(db.Model):
     __tablename__ = "obce"
diff --git a/templates/extract.html b/templates/extract.html
index 512ac22..28a50a0 100644
--- a/templates/extract.html
+++ b/templates/extract.html
@@ -33,6 +33,8 @@
 
 <p><a href="/">Zpět na vyhledání</a></p>
 
+<p>Sidlo2: {{ row.sidlo_v2[0] }}</p>
+
 <table class= "table" style="width: 100%">
     <!-- Display registration date -->
     <tr>
diff --git a/templates/results2.html b/templates/results2.html
index 4f8bf73..4f22971 100644
--- a/templates/results2.html
+++ b/templates/results2.html
@@ -11,16 +11,10 @@
                 {% set insolvency_notes = insolvency_notes.append(row.insolvence[i].insolvency_event) %}
             {% endif %}    
         {% endfor %}
-
-        {% set soud = [] %}
-        {% set oddil = [] %}
-        {% set vlozka = [] %}
-        {% for i in range (row.soudni_zapis|length) %}
-            {% if row.soudni_zapis[i].vymaz_datum == 0 %}
-                {% set soud = soud.append(row.soudni_zapis[i].soud) %}
-                {% set oddil = oddil.append(row.soudni_zapis[i].oddil) %}
-                {% set vlozka = vlozka.append(row.soudni_zapis[i].vlozka) %}
-            {% endif %}
+        {% for i in range (row.konkurz|length) %}
+            {% if row.konkurz[i].vymaz_datum == 0 %}
+                {% set insolvency_notes = insolvency_notes.append(row.konkurz[i].konkurz_event) %}
+            {% endif %}    
         {% endfor %}
 
         {% if insolvency_notes|length > 0 %}
@@ -39,14 +33,8 @@
             
         {% else %}
             {% set ico_buffer = "" %}
-        {% endif %}
-    
-        {% set sidlo = [] %}
-        {% for i in range (row.sidlo_text|length) %}
-            {% if row.sidlo_text[i].vymaz_datum == 0 %}
-                {% set sidlo = sidlo.append(row.sidlo_text[i].sidlo_text.adresa_text) %}
-            {% endif %}
-        {% endfor %}
+        {% endif %}    
+
 
     <table class= "{{table_style}}" style="width: 100%">
     <p></p>
@@ -58,13 +46,15 @@
     </tr>
     <tr>
         <td>Spisová značka:</td>
-        <td>{{ oddil|join("") }} {{ vlozka|join("") }} vedená u {{ soud|join("") }}</td>
+        <!-- <td>{{ oddil|join("") }} {{ vlozka|join("") }} vedená u {{ soud|join("") }}</td> -->
+        <td>{{row.oddil}} {{row.vlozka}} vedená u {{row.soud}}</td>
         <td>Den zápisu:</td>
         <td>{{row.zapis}}</td>
     </tr>
     <tr>
         <td>Sídlo:</td>
-        <td>{{ sidlo|join("") }}</td>
+        <!-- <td>{{ sidlo|join("") }}</td> -->
+        <td>{{ row.sidlo }} {{ row.sidlo_v2 }} </td>
         <td><a href="/{{ row.ico }}">Úplný výpis</a></td>
         <td><a href="/{{ row.ico }}-actual">Aktuální výpis</a></td>
     </tr>
diff --git a/todolist.html b/todolist.html
index eebf0e8..d0bafd0 100644
--- a/todolist.html
+++ b/todolist.html
@@ -1,12 +1,9 @@
 <ol>
-    <li>Update the search results to always show the current data.</li>
     <li>Change how the address is stored.</li>
     <li>Update the search to allow for searches by other properties.</li>
-    <li>Add a feature to allow to search based on the data that are no longer relevant.</li>
     <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>Add a code to create all the tables.</li>
-    <li>Optimize the speed, employ the SQL indexes.</li>
     <li>Refactor excessive duplications in the main code.</li>
     <li>Break down the main files to smaller ones?</li>
     <li>Add some other minor data to the databases - especially those relating to non-profit companies.</li>