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 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 __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 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 __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_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.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_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 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_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_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_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_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 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 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
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 __init__(self, module_name: AnyStr):
self.all_codes = StatusCodes()
self.code = self.all_codes.undefended
self.module_name = module_name
self.__db__ = Database(path='db/modules_stat.db')
self.__stat__ = 'Undefended'
self.__db__.create_table(
table_name='statuses',
rows={
'module': 'TEXT PRIMARY KEY UNIQUE',
'status': 'TEXT',
'code': 'INTEGER DEFAULT 0',
})
self.undefended()
def parse_quota_balances(self):
the_date = datetime.now()
date_string = datetime.strftime(the_date, '%Y-%m-%d')
balance_path = os.path.join(self.balance_path, "balances_" + date_string + ".csv")
sql = "select * from utils.quota_balances qb"
print("Writing quota balances")
d = Database()
rows = d.run_query(sql)
fields = ['Order number', 'Definition ID', 'Definition start', 'Latest balance', 'Description']
with open(balance_path, mode='w') as csv_file:
write = csv.writer(csv_file)
write.writerow(fields)
write.writerows(rows)
def get_certificate_types():
d = Database()
certificate_types = []
sql = """select ct.certificate_type_code, ctd.description
from certificate_types ct, certificate_type_descriptions ctd
where ct.certificate_type_code = ctd.certificate_type_code
and ct.validity_end_date is null
order by 1"""
rows = d.run_query(sql)
for row in rows:
obj = CertificateType(row[0], row[1])
certificate_types.append(obj.as_dict())
return certificate_types
def __init__(self, listen_address, listen_port, database_path):
"""Args:
listen_address (str): IP address of the listening interface
listen_port (int): port number for puppet connections
"""
self.__database = Database(database_path)
self.__listen_address = listen_address
self.__listen_port = listen_port
self.__socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
self.__socket.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
self.__socket.bind((self.__listen_address, self.__listen_port))
self.__connected_puppets = []
_load_animation()
def __init__( self ):
pygame.init()
self.running = True
self.will_render = True
self.display = pygame.display.set_mode(
[config.window_width, config.window_height],
pygame.HWSURFACE | pygame.DOUBLEBUF
)
self.clock = pygame.time.Clock()
self.font = pygame.font.SysFont("monospace", 12)
self.environment = Environment()
self.database = Database()
self.steps = 0
self.sim_start = pygame.time.get_ticks()
self.display.fill((255,255,255))
pygame.display.flip()
class Main:
def __init__( self ):
pygame.init()
self.running = True
self.will_render = True
self.display = pygame.display.set_mode(
[config.window_width, config.window_height],
pygame.HWSURFACE | pygame.DOUBLEBUF
)
self.clock = pygame.time.Clock()
self.font = pygame.font.SysFont("monospace", 12)
self.environment = Environment()
self.database = Database()
self.steps = 0
self.sim_start = pygame.time.get_ticks()
self.display.fill((255,255,255))
pygame.display.flip()
def execute( self ):
while( self.running ):
if not self.will_render:
self.clock.tick(60)
for event in pygame.event.get():
self.events(event)
keys = pygame.key.get_pressed()
if keys[pygame.K_SPACE]:
self.will_render = False
else:
self.will_render = True
self.update()
self.render()
self.steps += 1
if self.environment.generations == config.maximum_generations:
self.running = False
self.cleanup()
def events( self, event ):
if event.type == pygame.QUIT:
self.running = False
def update( self ):
self.environment.update_all()
if self.environment.has_active == False:
self.environment.perform_scoring()
self.database.save_population( self.environment.members )
self.environment.perform_selection()
self.environment.perform_crossover()
self.environment.perform_mutation()
self.environment.generations += 1
def render( self ):
self.display.fill((255,255,255))
for obj in self.environment.get_objects():
obj.draw( self.display )
pygame.draw.rect( self.display, [150,150,150], [config.viewport_width, 0, config.window_width - config.viewport_width, config.viewport_height])
pygame.draw.aaline( self.display, [0,0,0], [config.viewport_width, 0], [config.viewport_width, config.viewport_height], 1)
labels = [
self.font.render("Mutation Rate: " + str(config.mutation_rate * 100) + '%', 1, [0,0,0] ),
self.font.render("Selection Rate: " + str(config.selection_rate * 100) + '%', 1, [0,0,0] ),
self.font.render("Simulation Steps: " + str(self.steps), 1, [0,0,0] ),
self.font.render("Generation: " + str(self.environment.generations), 1, [0,0,0] ),
self.font.render("Members Alive: " + str(self.environment.remaining), 1, [0,0,0] ),
self.font.render("Overall Best:", 1, [0,0,0] )
]
if self.environment.overall_best != None:
labels.append(self.font.render("Score : " + str(self.environment.overall_best.score), 1, [0,0,0] ))
labels.append(self.font.render("Neurons : " + str(len(self.environment.overall_best.brain.all_neurons())), 1, [0,0,0] ))
labels.append(self.font.render("Food : " + str(self.environment.overall_best.food), 1, [0,0,0] ))
padding_increment = 10
for ind, label in enumerate(labels):
self.display.blit( label, (config.viewport_width + config.panel_padding, padding_increment))
if ind == 4 or ind == 1: padding_increment += 20
else: padding_increment += 10
pygame.display.flip()
def cleanup( self ):
self.database.graph_optimization()
self.database.generate_csv()
pygame.quit()