def get_regulations(self):
self.regulations_uk = []
self.regulations_xi = []
sql = """
select distinct m.measure_generating_regulation_id
from utils.materialized_measures_real_end_dates m
where measure_type_id = %s
and (validity_end_date is null or validity_end_date::date > current_date)"""
# Get the UK version first
d = Database("uk")
params = [self.measure_type_id]
rows = d.run_query(sql, params)
if rows:
for row in rows:
self.regulations_uk.append(row[0])
# Get the XI version next
d = Database("xi")
params = [self.measure_type_id]
rows = d.run_query(sql, params)
if rows:
for row in rows:
self.regulations_xi.append(row[0])
self.regulations_uk = self.format_regulations(self.regulations_uk)
self.regulations_xi = self.format_regulations(self.regulations_xi)
self.regulations_uk_string = "\n".join(self.regulations_uk)
self.regulations_xi_string = "\n".join(self.regulations_xi)
def __init__(self, *args, **kwargs):
super(Employees, self).__init__(orientation="vertical")
# kontent aplikace
global app
app = App.get_running_app()
rolovaci = ScrollView()
self.list = MDList()
self.db = Database(dbtype='sqlite', dbname='firmy.db')
self.vypis_prepis()
rolovaci.add_widget(self.list)
self.add_widget(rolovaci)
#Tlačítko pro vytvoření nové firmy
button1 = MDFlatButton()
button1.text = "NOVÁ FIRMA"
button1.size_hint = (0, .1)
button1.font_style = "Button"
button1.on_release = self.create_firma
self.add_widget(button1)
button2 = MDFlatButton()
button2.text = "NOVÝ ZAMĚSTNANEC"
button2.size_hint = (0, .1)
button2.font_style = "Button"
button2.on_release = self.create_zamestnanec
self.add_widget(button2)
def get_measure_conditions(self):
d = Database()
c = Config()
self.measure_conditions = []
self.meursing_code = False
measure_sid_string = self.get_measure_sid_string()
c.sql["measure_conditions"] = c.sql["measure_conditions"].replace(
"<params>", measure_sid_string)
rows = d.run_query(c.sql["measure_conditions"])
for row in rows:
mc = MeasureCondition()
mc.measure_sid = row[0]
mc.condition_code = row[1]
mc.condition_code_description = row[2]
mc.certificate_type_code = row[3]
mc.certificate_code = row[4]
mc.document_code = row[5]
mc.action_code = row[6]
mc.action = row[7]
mc.certificate_type_description = row[8]
mc.component_sequence_number = row[9]
mc.certificate_description = row[10]
mc.get_requirement()
self.measure_conditions.append(mc)
if mc.condition_code == "V":
self.meursing_code = True
def __init__(self):
self.condition_code_dict = {}
sql = """select condition_code, description from measure_condition_code_descriptions mccd order by 1"""
d = Database()
rows = d.run_query(sql)
for row in rows:
self.condition_code_dict[row[0]] = row[1]
def get_mfns(self):
sql = """
select measure_sid, goods_nomenclature_item_id, measure_type_id, additional_code
from utils.materialized_measures_real_end_dates m
where measure_type_id in ('103', '105')
and (validity_end_date is null or validity_end_date > %s)
and left(goods_nomenclature_item_id, 2) = %s
order by goods_nomenclature_item_id;
"""
params = [
self.date_string,
self.chapter_id
]
d = Database("uk")
rows = d.run_query(sql, params)
self.measures_dict = {}
for row in rows:
measure_obj = {
"measure_sid": row[0],
"goods_nomenclature_item_id": row[1],
"measure_type_id": row[2],
"additional_code": row[3],
"measure_components": [],
"duty_string": ""
}
self.measures_dict[row[0]] = measure_obj
self.get_mfn_measure_components()
self.build_duty_strings()
self.apply_duties_to_commodities()
def run():
global title
animation_completed = False
if title.get_curr_frame() < title.get_final_frame() - 1:
title.update()
else:
animation_completed = True
title.set_curr_frame(0)
window.set_background_color((0, 0, 0))
title.draw()
if animation_completed:
aux_name = input("Qual seu nome? \n")
while len(aux_name) > 9 or len(aux_name) < 3:
print("Por favor, insira um nome entre 3 e 9 caracteres.\n")
aux_name = input("Qual seu nome? \n")
Database().save_score(aux_name.lower(), store.get("score"), store.get("wave"))
return True
else:
return False
def lookup_start_date(self):
self.validity_start_date = ""
sql = "select validity_start_date from goods_nomenclature_description_periods where goods_nomenclature_description_period_sid = " + str(self.goods_nomenclature_description_period_sid)
d = Database()
rows = d.run_query(sql)
for row in rows:
self.validity_start_date = str(row[0])
def __init__(self):
self.geography_list = []
self.geography_dict = {}
self.geography_hjid_dict = {}
sql = """SELECT g.geographical_area_sid,
g.parent_geographical_area_group_sid,
geo1.geographical_area_id,
geo1.description,
g.geographical_code,
g.validity_start_date,
g.validity_end_date, g.hjid
FROM geographical_area_descriptions geo1,
geographical_areas g
WHERE g.geographical_area_id::text = geo1.geographical_area_id::text AND (geo1.geographical_area_description_period_sid IN ( SELECT max(geo2.geographical_area_description_period_sid) AS max
FROM geographical_area_descriptions geo2
WHERE geo1.geographical_area_id::text = geo2.geographical_area_id::text))
ORDER BY geo1.geographical_area_id;"""
d = Database()
rows = d.run_query(sql)
for row in rows:
hjid = int(row[7])
geo = GeographicalArea(row[0], row[1], row[2], row[3], row[4],
row[5], row[6], hjid)
self.geography_list.append(geo)
self.geography_dict[row[2]] = row[3]
self.geography_hjid_dict[hjid] = row[2] + " - " + row[3]
def get_mfn_measure_components(self):
sql = """select mc.measure_sid, mc.duty_expression_id, mc.duty_amount, mc.monetary_unit_code,
mc.measurement_unit_code, mc.measurement_unit_qualifier_code
from measure_components mc, utils.materialized_measures_real_end_dates m
where m.measure_sid = mc.measure_sid
and measure_type_id in ('103', '105')
and (validity_end_date is null or validity_end_date > %s)
and left(goods_nomenclature_item_id, 2) = %s
order by mc.measure_sid, mc.duty_expression_id;
"""
params = [
self.date_string,
self.chapter_id
]
d = Database("uk")
rows = d.run_query(sql, params)
for row in rows:
mc = MeasureComponent()
mc.measure_sid = row[0]
mc.duty_expression_id = row[1]
mc.duty_amount = row[2]
mc.monetary_unit_code = row[3]
mc.measurement_unit_code = row[4]
mc.measurement_unit_qualifier_code = row[5]
mc.get_friendly_monetary_unit()
mc.get_friendly_measurement_units()
mc.build_duty()
self.measures_dict[mc.measure_sid]["measure_components"].append(mc)
a = 1
def get_permutations(self):
self.permutations = []
sql = """select distinct conditions
from utils.materialized_conditions mc
where measure_type_id = %s
and conditions is not null
order by 1;"""
params = [self.measure_type_id]
d = Database("uk")
rows = d.run_query(sql, params)
for row in rows:
permutation = row[0]
self.permutations.append(permutation)
new_section = self.document.add_section(WD_SECTION.NEW_PAGE)
self.change_orientation_portrait(new_section)
count = len(self.permutations)
if count == 0:
return
self.get_permutation_examples()
self.document.add_heading(
"Permutations in which measure type {measure_type_id} is used".
format(measure_type_id=self.measure_type_id), 1)
paragraph = self.document.add_paragraph(
"Measure type {measure_type_id} is used in {count} permutations.".
format(measure_type_id=self.measure_type_id, count=str(count)),
style='Normal')
table = self.document.add_table(rows=count + 1, cols=3) # 17.17
widths = (Cm(9.17), Cm(3), Cm(5))
for row in table.rows:
for idx, width in enumerate(widths):
row.cells[idx].width = width
cells = table.rows[0].cells
cells[0].text = "Permutation"
cells[1].text = "Sample"
cells[2].text = "Notes"
index = 0
for permutation in self.permutations:
index += 1
cells = table.rows[index].cells
cells[0].text = permutation
try:
cells[1].text = self.permutation_examples[permutation][
"goods_nomenclature_item_id"]
except:
pass
try:
cells[2].text = self.permutation_examples[permutation][
"description"]
except:
pass
self.style_table(table, "List Table 3")
def __init__(self):
self.database = Database()
self.cat_name = str
self.list_cat_ids = []
self.list_cat_names = []
self.checked_input = None
self.selected_cat_id = None
self.selected_cat_name = None
self.selectionnable_prod = None
def get_meursing_measures(additional_code_id, geographical_area_id):
d = Database()
gn = GoodsNomenclature("", additional_code_id, geographical_area_id)
gn.get_measures()
gn.get_measure_components()
gn.assign_measure_components()
gn.get_duty_expressions()
gn.assign_meursing_measures()
return gn.meursing_measures
def get_sample_comm_codes(self):
sql = "select distinct(goods_nomenclature_item_id) from measures where ordernumber = '" + self.quota_order_number_id + "' order by 1 limit 5"
d = Database()
rows = d.run_query(sql)
for row in rows:
self.comm_code_string += row[0] + ", "
self.comm_code_string = self.comm_code_string.strip()
self.comm_code_string = self.comm_code_string.strip(",")
def get_meursing_code_list():
d = Database()
additional_codes = []
sql = """select additional_code from meursing_additional_codes mac
where validity_end_date is null order by 1"""
rows = d.run_query(sql)
for row in rows:
additional_codes.append(row[0])
return additional_codes
def __init__(self):
self.database = Database()
# self.database.mostrar_tudo()
#self.database.create('Fernanda', 666)
#self.database.mostrar_tudo()
#self.database.update_password('Layane', 0301, '12345')
#player = self.database.read('Fernanda',666)
#print(player)
self.database.mostrar_tudo()
def get_footnotes(self):
self.footnotes = {}
sql = """with cte_footnotes as (
select distinct on (f.footnote_type_id, f.footnote_id)
f.footnote_type_id, f.footnote_id, fd.description
from footnotes f, footnote_descriptions fd, footnote_description_periods fdp
where f.footnote_type_id = fd.footnote_type_id
and f.footnote_id = fd.footnote_id
and f.footnote_type_id = fdp.footnote_type_id
and f.footnote_id = fdp.footnote_id
order by f.footnote_type_id, f.footnote_id, fdp.validity_start_date desc
)
select distinct (f.footnote_type_id || f.footnote_id) as code, m.geographical_area_id, f.description
from cte_footnotes f, utils.materialized_measures_real_end_dates m, footnote_association_measures fam
where fam.footnote_type_id = f.footnote_type_id
and fam.footnote_id = f.footnote_id
and fam.measure_sid = m.measure_sid
and m.validity_start_date::date <= current_date
and (m.validity_end_date::date >= current_date or m.validity_end_date is null)
and m.measure_type_id = %s"""
params = [self.measure_type_id]
d = Database("uk")
rows = d.run_query(sql, params)
for row in rows:
self.footnotes[row[0]] = {"geography": row[1], "footnote": row[2]}
count = len(self.footnotes)
self.document.add_heading(
"Footnotes used on measure type {measure_type_id}".format(
measure_type_id=self.measure_type_id), 1)
paragraph = self.document.add_paragraph(
"Measure type {measure_type_id} has {count} footnotes.".format(
measure_type_id=self.measure_type_id, count=str(count)),
style='Normal')
table = self.document.add_table(rows=count + 1, cols=3) # 17.17
widths = (Cm(4), Cm(4), Cm(9.17))
for row in table.rows:
for idx, width in enumerate(widths):
row.cells[idx].width = width
cells = table.rows[0].cells
cells[0].text = "Footnote ID"
cells[1].text = "Geography"
cells[2].text = "Footnote"
index = 0
for key in self.footnotes.keys():
index += 1
cells = table.rows[index].cells
cells[0].text = key
cells[1].text = self.footnotes[key]["geography"]
cells[2].text = self.footnotes[key]["footnote"]
self.style_table(table, "List Table 3")
def get_descendants(self):
# Get descendants of the selected commodity code or heading
d = Database()
c = Config()
self.descendants = []
params = [
self.date,
self.date,
self.date,
self.heading_id,
self.goods_nomenclature_item_id + self.productline_suffix
]
rows = d.run_query(c.sql["descendants"], params)
row_count = len(rows)
if row_count < 1:
return False
# Data in the first row is the current commodity
self.goods_nomenclature_sid = rows[0][0]
self.description = rows[0][3]
self.get_formatted_descriptions()
self.number_indents = rows[0][4]
self.commcode_plus_suffix = rows[0][5]
start_indent = self.number_indents
for row in rows:
goods_nomenclature_sid = row[0]
goods_nomenclature_item_id = row[1]
productline_suffix = row[2]
description = row[3]
number_indents = row[4]
commcode_plus_suffix = row[5]
if (number_indents > start_indent):
gn = GoodsNomenclature(goods_nomenclature_item_id)
gn.goods_nomenclature_sid = goods_nomenclature_sid
gn.productline_suffix = productline_suffix
gn.description = description
gn.number_indents = number_indents
gn.commcode_plus_suffix = commcode_plus_suffix
gn.context = "descendant"
gn.get_formatted_descriptions()
if commcode_plus_suffix != self.commcode_plus_suffix:
self.descendants.append(gn.as_dict())
if commcode_plus_suffix > self.commcode_plus_suffix:
if number_indents <= start_indent:
break
self.leaf = True if len(self.descendants) == 0 else False
self.declarable = True if (
len(self.descendants) == 0 and self.productline_suffix == '80') else False
self.get_descendant_parentage(self.goods_nomenclature_sid)
return True
def get_measures(self):
c = Config()
d = Database()
self.measures = []
if self.is_meursing:
params = [
self.meursing_additional_code_id,
self.meursing_additional_code_id,
self.meursing_geographical_area_id,
'2021-01-01',
'2021-01-01',
self.meursing_geographical_area_id
]
rows = d.run_query(c.sql["meursing_measures"], params)
else:
comm_code_string = self.get_comm_code_string()
c.sql["measures"] = c.sql["measures"].replace("<params>", comm_code_string)
c.sql["measures"] = c.sql["measures"].replace("<date>", self.date)
rows = d.run_query(c.sql["measures"])
self.measure_sids = []
for row in rows:
m = Measure()
m.measure_sid = row[0]
m.goods_nomenclature_item_id = row[1]
m.geographical_area_id = row[2]
m.measure_type_id = row[3]
m.measure_type_description = row[4]
m.trade_movement_code = row[5]
m.measure_type_series_id = row[6]
m.measure_generating_regulation_id = row[7]
m.ordernumber = row[8]
m.additional_code_type_id = row[9]
m.additional_code_id = row[10]
m.additional_code = row[11]
m.geographical_area_sid = row[12]
m.goods_nomenclature_sid = row[13]
m.additional_code_sid = row[14]
m.effective_start_date = row[15]
m.effective_end_date = row[16]
m.regulation_officialjournal_number = row[17]
m.regulation_officialjournal_page = row[18]
m.regulation_published_date = row[19]
m.regulation_validity_start_date = row[20]
m.regulation_validity_end_date = row[21]
m.geographical_area_description = row[22]
m.reduction_indicator = row[23]
self.measure_sids.append(m.measure_sid)
if m.effective_start_date is not None:
m.effective_start_date += "T00:00:00.000Z"
m.interpret()
self.measures.append(m)
def __init__(self):
self.measure_type_dict = {}
sql = """select mt.measure_type_id, mtd.description
from measure_types mt, measure_type_descriptions mtd
where mt.measure_type_id = mtd.measure_type_id
and validity_end_date is null
order by 1"""
d = Database()
rows = d.run_query(sql)
for row in rows:
self.measure_type_dict[row[0]] = row[1]
def get_meursing_codes():
d = Database()
additional_codes = []
sql = """select additional_code from meursing_additional_codes mac
where validity_end_date is null
order by 1"""
rows = d.run_query(sql)
for row in rows:
obj = AdditionalCode("7", row[0], "")
additional_codes.append(obj.as_dict())
return additional_codes
def find_user_by_username(username): # Returns a User Object.
db = Database()
db.connect()
sql = "SELECT * from user where username = '******'".format(username)
row = db.query(sql)
row = row[0]
newUser = User()
newUser.set_id(row['id'])
newUser.set_username(row['username'])
newUser.set_password(row['password'])
newUser.set_email(row['email'])
return newUser
def new_UserObject(id):
db = Database()
db.connect()
sql = "SELECT * from user WHERE id = {0}".format(id)
row = db.query(sql)
row = row[0]
newUser = User()
newUser.set_id(row['id'])
newUser.set_username(row['username'])
newUser.set_password(row['password'])
newUser.set_email(row['email'])
return newUser
def get_commodity_footnotes(self):
d = Database()
c = Config()
self.commodity_footnotes = []
comm_code_string = self.get_comm_code_string()
c.sql["commodity_footnotes"] = c.sql["commodity_footnotes"].replace(
"<params>", comm_code_string)
rows = d.run_query(c.sql["commodity_footnotes"])
for row in rows:
f = Footnote(row[0], row[1], row[2])
self.commodity_footnotes.append(f.as_dict())
def get_ancestors(self):
# Get core data like the description
d = Database()
c = Config()
self.ancestors = []
self.codes = []
params = [
self.date,
self.date,
self.date,
self.chapter_id,
self.goods_nomenclature_item_id
]
rows = d.run_query(c.sql["ancestors"], params)
row_count = len(rows)
if row_count < 1:
return
# Data in the first row is the current commodity
self.description = rows[0][3]
self.number_indents = rows[0][4]
self.commcode_plus_suffix = rows[0][5]
current_indent = 99
for row in rows:
goods_nomenclature_sid = row[0]
goods_nomenclature_item_id = row[1]
productline_suffix = row[2]
description = row[3]
number_indents = row[4]
commcode_plus_suffix = row[5]
if (number_indents < current_indent) or (goods_nomenclature_item_id[-8:] == "00000000"):
gn = GoodsNomenclature(goods_nomenclature_item_id)
gn.goods_nomenclature_sid = goods_nomenclature_sid
gn.productline_suffix = productline_suffix
gn.description = description
gn.number_indents = number_indents
gn.commcode_plus_suffix = commcode_plus_suffix
gn.get_formatted_descriptions()
gn.context = "ancestor"
if commcode_plus_suffix != self.commcode_plus_suffix:
self.ancestors.append(gn)
if productline_suffix == '80':
self.codes.append(goods_nomenclature_item_id)
if number_indents < current_indent:
current_indent = number_indents
return row_count
def get_footnote_types():
d = Database()
footnote_types = []
sql = """select ft.footnote_type_id, application_code, description
from footnote_types ft, footnote_type_descriptions ftd
where ft.footnote_type_id = ftd.footnote_type_id
and ft.validity_end_date is null
order by 1"""
rows = d.run_query(sql)
for row in rows:
obj = FootnoteType(row[0], row[1], row[2])
footnote_types.append(obj.as_dict())
return footnote_types
def get_measure_type_series():
d = Database()
measure_type_series = []
sql = """select mts.measure_type_series_id, mts.measure_type_combination, mtsd.description
from measure_type_series mts, measure_type_series_descriptions mtsd
where mts.measure_type_series_id = mtsd.measure_type_series_id
and mts.validity_end_date is null order by 1;"""
rows = d.run_query(sql)
for row in rows:
obj = MeasureTypeSeries(row[0], row[1], row[2])
measure_type_series.append(obj.as_dict())
return measure_type_series
def get_measure_exclusions(self):
d = Database()
c = Config()
self.measure_exclusions = []
measure_sid_string = self.get_measure_sid_string()
c.sql["exclusions"] = c.sql["exclusions"].replace(
"<params>", measure_sid_string)
rows = d.run_query(c.sql["exclusions"])
for row in rows:
ga = GeographicalArea(row[0], row[1])
ga.measure_sid = row[2]
self.measure_exclusions.append(ga)
def get_section(self):
d = Database()
c = Config()
# Get core data like the description
params = [
self.chapter.goods_nomenclature_sid
]
rows = d.run_query(c.sql["section"], params)
row = rows[0]
section_position = row[0]
section_title = row[1]
s = Section(section_position, section_title)
self.section = s.as_dict()
def get_measure_footnotes(self):
d = Database()
c = Config()
self.measure_footnotes = []
measure_sid_string = self.get_measure_sid_string()
c.sql["measure_footnotes"] = c.sql["measure_footnotes"].replace(
"<params>", measure_sid_string)
rows = d.run_query(c.sql["measure_footnotes"])
for row in rows:
f = Footnote(row[0], row[1], row[2], row[3])
self.measure_footnotes.append(f)
def selecionar():
db = Database(config['sql'])
rows = db.execute('SELECT * FROM seguranca.usuario;')
lista = []
for row in rows:
obj = {}
obj['id'] = row.id
obj['nome'] = row.nome
lista.append(obj)
# row.dataCadastro = None
return lista
