class UpdateFormView(CreateFormView):
formClass = UpdateForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
getter = """SELECT title, description, homepage
FROM %s WHERE %s_id = %s;""" % (
self.context.tablename, self.context.tablename, self.itemid)
self.result = self.db.execute(getter)
self.db.close()
return self.index()
def renderForm(self):
self.form.title.default = self.result[0][0]
self.form.description.default = self.result[0][1]
self.form.homepage.default = self.result[0][2]
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.item_id.data
if button == 'Speichern': #and self.validate():
command = """UPDATE manufacturer SET title='%s', description='%s', homepage='%s'
WHERE manufacturer_id = %s;""" % (
self.form.title.data, self.form.description.data,
self.form.homepage.data, self.form.item_id.data)
self.db.execute(command)
message = u'Der Hersteller wurde erfolgreich aktualisiert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
#message = u'Fehler beim Aktualisieren des Gefahrstoffgemisches'
#ploneapi.portal.show_message(message=message, type='error', request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class UpdateManufacturerFormView(CreateFormView):
formClass = UpdateManufacturerForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
return self.index()
def renderForm(self):
try:
insert = "SELECT manufacturer_id, title FROM manufacturer ORDER BY title;"
manus = self.db.execute(insert)
except:
manus = []
self.form.manufacturer_id.choices = manus
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.item_id.data
if button == 'Speichern': #and self.validate():
command = """UPDATE substance_mixture SET manufacturer_id=%s WHERE substance_mixture_id = %s;""" % (
self.form.manufacturer_id.data, self.form.item_id.data)
self.db.execute(command)
message = u'Das Gefahrstoffgemisch wurde erfolgreich aktualisiert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
#message = u'Fehler beim Aktualisieren des Gefahrstoffgemisches'
#ploneapi.portal.show_message(message=message, type='error', request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class DeleteIngredientsFormView(CreateFormView):
formClass = DeleteIngredientsForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
self.db.close()
return self.index()
def renderForm(self):
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.item_id.data
if button == 'Speichern' and self.form.sure.data is True: #and self.validate():
command = "DELETE FROM recipes WHERE mixture_id = %s" % (
self.form.item_id.data)
self.db.execute(command)
message = u'Die Bestandteile wurden erfolgreich gelöscht'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Speichern' and self.form.sure.data is False:
message = u'Die Bestandteile wurden nicht gelöscht, da das Bestätigungsfeld nicht ausgewählt war.'
ploneapi.portal.show_message(message=message,
type='error',
request=self.request)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class SynonymFormView(CreateFormView):
formClass = SynonymForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
self.db.close()
return self.index()
def renderForm(self):
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
#redirect_url = self.context.aq_parent.absolute_url()
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.item_id.data
if button == 'Speichern': #and self.validate():
insert = "INSERT INTO synonyms VALUES (DEFAULT, %s, '%s');" % (
self.form.item_id.data, self.form.synonym_name.data)
self.db.execute(insert)
message = u'Das Synonym wurde erfolgreich angelegt'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class CreateFormView(WTFormView):
formClass = CreateForm
buttons = ('Speichern', 'Abbrechen')
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
self.host = self.context.aq_parent.host
self.dbname = self.context.aq_parent.database
self.username = self.context.aq_parent.username
self.password = self.context.aq_parent.password
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
return self.index()
def submit(self, button):
redirect_url = self.context.aq_parent.absolute_url()
if button == 'Speichern' and self.validate():
if True:
insert = """INSERT INTO manufacturer VALUES (DEFAULT, '%s', '%s', '%s', %s);""" % (
self.form.title.data, self.form.description.data,
self.context.aq_parent.get_webcode(),
check_value(self.form.homepage.data))
self.db.execute(insert)
self.db.close()
message = u'Der Hersteller wurde erfolgreich gespeichert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
#except:
# print(u'Fehler beim Einfügen in die Datenbank')
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class DeleteSynonymsFormView(CreateFormView):
formClass = DeleteSynonymsForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
self.db.close()
return self.index()
def renderForm(self):
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
#redirect_url = self.context.aq_parent.absolute_url()
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.item_id.data
if button == 'Speichern': #and self.validate():
insert = "DELETE FROM synonyms WHERE substance_id = %s;" % self.form.item_id.data
self.db.execute(insert)
message = u'Die Synonyme wurden erfolgreich gelöscht'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class Gefahrstoffliste(BrowserView):
def __call__(self):
self.db = DBConnect(host=self.context.host,
db=self.context.database,
user=self.context.username,
password=self.context.password)
mixtures = []
select = "SELECT substance_mixture_id, title FROM substance_mixture;"
gemische = self.db.execute(select)
for gemisch in gemische:
mixture_entry = {}
selectoldid = "SELECT link FROM oldlinks WHERE mixture_id = %s" % gemisch[
0]
oldid = self.db.execute(selectoldid)
if oldid:
mixture_entry['@id'] = oldid[0][0]
else:
mixture_entry['@id'] = "bgetem.substance_mixture." + str(
gemisch[0]
) # über die Punkt-Notation könnten mehrere potenzielle Quellen angezapft werden
mixture_entry['title'] = gemisch[1]
mixtures.append(mixture_entry)
return jsonlib.write(mixtures)
class Gefahrstoff(BrowserView):
def __call__(self):
self.db = DBConnect(host=self.context.host, db=self.context.database, user=self.context.username, password=self.context.password)
gemischid = self.request.get('gemischid')
#gemischid = "https://emissionsarme-produkte.bgetem.de/datenbank-chemie-dp/wasch-und-reinigungsmittel-fuer-den-etikettendruck/biolon-xi-fluessig"
if gemischid.startswith('https://'):
select = "SELECT mixture_id FROM oldlinks WHERE link = '%s'" % gemischid
mixture_id = self.db.execute(select)
else:
mixture_id = gemischid.split('.')[-1]
mixture_id = mixture_id[0][0]
data1select = "SELECT * FROM substance_mixture WHERE substance_mixture_id = %s" % mixture_id
data1 = self.db.execute(data1select)
data2select = "SELECT * FROM manufacturer WHERE manufacturer_id = %s" % data1[0][25]
data2 = self.db.execute(data2select)
data3select = "SELECT * FROM recipes WHERE mixture_id = %s" % mixture_id
data3 = self.db.execute(data3select)
gefahrstoffdata = {}
hersteller = {}
hersteller['title'] = data2[0][1]
hersteller['@id'] = "bgetem.manufacturer."+str(data2[0][0])
hersteller['description'] = data2[0][2]
hersteller['homepage'] = data2[0][4]
inhaltsstoffe = list()
for inhalt in data3:
inhaltsstoff = {}
select = "SELECT * FROM substance WHERE substance_id = %s" % inhalt[1]
reinstoff = self.db.execute(select)
inhaltsstoff['cas'] = reinstoff[0][4]
inhaltsstoff['gefahrstoff'] = reinstoff[0][1]
inhaltsstoff['anteil_min'] = inhalt[3]
inhaltsstoff['anteil_max'] = inhalt[4]
inhaltsstoff['anteil'] = f">= {inhalt[3]}% - <= {inhalt[4]}%"
inhaltsstoffe.append(inhaltsstoff)
productclassselect = "SELECT class_name FROM productclasses WHERE class_id = %s" % data1[0][27]
try:
productclass = self.db.execute(productclassselect)
productclass = productclass[0][0]
except:
productclass = None
produktkategorien = {
"label": "Reinigungsmittel im Etikettendruck",
"offset": "Offsetdruck allgemein",
"heatset": "Heatsetwaschmittel",
"uv": "Reinigungsmittel im UV-Druck",
"special": "Sonderreiniger",
"dental": "Dentaltechnik",
"textil": "Textil und Mode"
}
produktkategorie = list()
produktkategorie.append(produktkategorien[data1[0][5]])
gefahrstoffdata['hersteller'] = hersteller
gefahrstoffdata['hskategorie'] = data1[0][16]
gefahrstoffdata['bemerkungen'] = data1[0][23]
gefahrstoffdata['chemikalienliste'] = inhaltsstoffe
gefahrstoffdata['UID'] = data1[0][3]
gefahrstoffdata['title'] = data1[0][1]
gefahrstoffdata['review_state'] = data1[0][26]
gefahrstoffdata['emissionsgeprueft'] = data1[0][17]
gefahrstoffdata['produktkategorie'] = produktkategorie
gefahrstoffdata['description'] = data1[0][2]
gefahrstoffdata['wertebereich'] = data1[0][20]
gefahrstoffdata['flammpunkt'] = data1[0][19]
gefahrstoffdata['@id'] = gemischid
gefahrstoffdata['produktklasse'] = productclass
return jsonlib.write(gefahrstoffdata)
class TabelleFormView(WTFormView):
formClass = BaseForm
buttons = ('Suche', 'Alle anzeigen')
def __call__(self):
self.columnids = self.getindexfortablename()
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host, db=dbdata.database, user=dbdata.username, password=dbdata.password)
self.host = self.context.aq_parent.host
self.dbname = self.context.aq_parent.database
self.username = self.context.aq_parent.username
self.password = self.context.aq_parent.password
self.preselects = self.get_preselects()
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
else:
button = "Alle anzeigen"
result = self.submit(button)
if result:
return result
return self.index()
def get_preselects(self):
moreresultcolumns = self.context.moreresultcolumns
#brains = self.context.getFolderContents()
preselects = []
for i in moreresultcolumns:
entry = dict()
obj = self.context[i]
entry['id'] = obj.id
entry['title'] = obj.title
entry['preselects'] = obj.preselects
entry['vocab'] = obj.vocab
preselects.append(entry)
return preselects
def get_preergs(self, preselects, vocab, value):
erg = list()
for select in preselects:
if not erg:
sel = Template(select).render(value=value)
try:
resu = self.db.execute(sel)
resu = [i[0] for i in resu]
if vocab:
erg = self.get_attr_translation(vocab, resu[0])
else:
erg = resu
except:
erg = ' '
else:
res = erg
erg = []
for entry in res:
sel = Template(select).render(value=entry)
try:
resu = self.db.execute(sel)
if vocab:
pdb.set_trace()
result = self.get_attr_translation(vocab, resu[0])
else:
result = resu
erg += [i[0] for i in result]
except:
result = ' '
if vocab:
result = erg
else:
result = ', '.join(erg)
return result
def getindexfortablename(self):
columnids = list()
print(self.context.resultcolumns)
for i in self.context.resultcolumns:
columnids.append(possibleColumns(self.context).getTerm(i).token)
return columnids
def get_tablehead(self, column):
result = tableheads(column)
return result
def userCanAdd(self):
if not ploneapi.user.is_anonymous():
current = ploneapi.user.get_current()
roles = ploneapi.user.get_roles(user=current)
if addrole in roles or 'Manager' in roles or 'Site Administrator' in roles:
return self.context.absolute_url() + '/create-%s-form' % self.context.tablename
return False
def get_attr_translation(self, attribute, value):
print(attribute, value)
vocabulary = get_vocabulary(attribute)
for i in vocabulary:
if i[0] == value:
return i[1]
return value
def show_all(self):
results = []
searchkey = self.context.tablename + '_id'
searchtable = self.context.tablename
resultcolumns = self.context.resultcolumns
if not resultcolumns:
select = "SELECT %s, title FROM %s WHERE status = 'published' ORDER BY title ASC;" % (searchkey, searchtable)
else:
select = "SELECT * FROM %s WHERE status = 'published' ORDER BY title ASC;" % (searchtable)
try:
conn = psycopg2.connect(host=self.host, user=self.username, password=self.password, dbname=self.dbname)
cur = conn.cursor()
cur.execute(select)
results = cur.fetchall()
cur.close
conn.close()
except:
results = []
#results = select um alle Produkte der Tabelle auszuwählen
return results
def submit(self, button):
#if button == 'Suche' and self.validate():
if button == 'Alle anzeigen':
#print(self.context.mixturetype)
self.ergs = self.show_all()
if button == 'Suche':
searchkey = self.context.tablename + '_id'
searchtable = self.context.tablename
#manu_id = self.form.manu.data
select = "SELECT * FROM %s WHERE manufacturer_id = '%s';" % (searchkey, searchtable, manu_id)
try:
conn = psycopg2.connect(host=self.host, user=self.username, password=self.password, dbname=self.dbname)
cur = conn.cursor()
cur.execute(select)
self.ergs = cur.fetchall()
cur.close
conn.close()
except:
self.ergs = []
elif button == 'Abbrechen':
url = self.context.aq_parent.absolute_url()
return self.request.response.redirect(url)
class SingleView(BrowserView):
index = ViewPageTemplateFile('standard.pt')
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
self.itemid = self.request.get('item')
self.host = self.context.aq_parent.host
self.dbname = self.context.aq_parent.database
self.username = self.context.aq_parent.username
self.password = self.context.aq_parent.password
self.article = self.get_article()
self.machines = []
self.secsheet = []
#import pdb; pdb.set_trace()
self.definitions = self.get_definitions()
print(self.definitions)
self.more_definitions = self.get_more_definitions()
if self.context.tablename == 'substance_mixture':
#self.machines = self.get_machines()
#self.secsheet = self.get_recipes()
template = ViewPageTemplateFile('single_view.pt')
self.image_url = self.get_image_url()
self.template = BoundPageTemplate(template, self)
return self.template()
if self.context.tablename == 'substance':
#self.machines = self.get_machines()
#self.secsheet = self.get_recipes()
template = ViewPageTemplateFile('single_view.pt')
self.template = BoundPageTemplate(template, self)
return self.template()
elif self.context.tablename == 'spray_powder':
template = ViewPageTemplateFile('single_view.pt')
self.image_url = self.get_image_url()
self.template = BoundPageTemplate(template, self)
return self.template()
elif self.context.tablename == 'manufacturer':
template = ViewPageTemplateFile('single_view.pt')
self.template = BoundPageTemplate(template, self)
return self.template()
return self.index()
def get_definitions(self):
fragments = list()
columns = self.context.columns
for key in columns:
key_value_pair = getattr(
self, key, None
) # hole die Methode für Aufbereitung der Daten in DB-Spalte key
if key_value_pair:
entry = key_value_pair()
if entry:
title = entry['title']
value = entry['value']
fragment = f'<dt class="col col-sm-5">{title}</dt><dd class="col col-sm-7">{value}</dd><div class="w-100 divider"></div>'
fragments.append(fragment)
return fragments
def get_more_definitions(self):
fragments = list()
preselects = list()
for column in self.context.morecolumns:
entry = dict()
obj = self.context[column]
entry['id'] = obj.id
entry['title'] = obj.title
entry['preselects'] = obj.preselects
entry['vocab'] = obj.vocab
preselects.append(entry)
for select in preselects:
title = select['title']
value = self.get_preergs(select)
if value:
fragment = f'<dt class="col col-sm-5">{title}</dt><dd class="col col-sm-7">{value}</dd><div class="w-100 divider"></div>'
fragments.append(fragment)
return fragments
def get_preergs(self, preselect):
erg = list()
vocabulary = preselect.get('vocab')
preselects = preselect.get('preselects')
for select in preselects:
if not erg:
sel = Template(select).render(value=self.itemid)
try:
resu = self.db.execute(sel)
resu = [i[0] for i in resu]
if vocabulary:
erg = [
self.get_attr_translation(vocabulary, i)
for i in resu
]
else:
erg = resu
except:
erg = []
else:
res = erg
erg = []
for entry in res:
sel = Template(select).render(value=entry)
try:
resu = self.db.execute(sel)
if vocabulary:
result = [
self.get_attr_translation(vocabulary, i)
for i in resu
]
else:
result = resu
erg += [i[0] for i in result]
except:
result = []
if len(erg) == 1:
return erg[0]
elif len(erg) > 1:
htmlstring = '<span><ul>'
for element in erg:
htmlstring += f'<li>{element}</li>'
htmlstring += '</ul></span>'
return htmlstring
else:
return ''
def is_mixture(self):
if self.context.tablename == 'substance_mixture':
return True
else:
return False
def is_substance(self):
if self.context.tablename == 'substance':
return True
else:
return False
def is_manufacturer(self):
if self.context.tablename == 'manufacturer':
return True
else:
return False
def is_powder(self):
if self.context.tablename == 'spray_powder':
return True
else:
return False
def substance_type(self):
title = "Typ des Wasch- und Reinigungsmittels"
fieldindex = possibleColumns(
self.context).getTerm('substance_type').token
value = self.get_attr_translation('substance_type',
self.article[int(fieldindex)])
if value:
return {'title': title, 'value': value}
return {}
def branch(self):
title = "Branche"
if self.context.tablename == 'substance_mixture':
fieldindex = possibleColumns(self.context).getTerm('branch').token
value = self.get_attr_translation('branch',
self.article[int(fieldindex)])
elif self.context.tablename == 'substance':
fieldindex = possibleColumns(self.context).getTerm('branch').token
value = self.get_attr_translation('branch',
self.article[int(fieldindex)])
if value:
return {'title': title, 'value': value}
return {}
def manufacturer_id(self):
title = "Hersteller"
if self.context.tablename == 'substance_mixture':
fieldindex = possibleColumns(
self.context).getTerm('manufacturer_id').token
value = self.get_manufacturer(self.article[int(fieldindex)])
elif self.context.tablename == 'spray_powder':
fieldindex = possibleColumns(
self.context).getTerm('manufacturer_id').token
value = self.get_manufacturer(self.article[int(fieldindex)])
elif self.context.tablename == 'manufacturer':
value = False
if value:
return {'title': title, 'value': value}
return {}
def skin_category(self):
title = "Hautschutzmittelgruppe"
if self.context.tablename == 'substance_mixture':
fieldindex = possibleColumns(
self.context).getTerm('skin_category').token
value = self.get_attr_translation('hskategorie',
self.article[int(fieldindex)])
elif self.context.tablename == 'substance':
fieldindex = possibleColumns(
self.context).getTerm('skin_category').token
value = self.get_attr_translation('hskategorie',
self.article[int(fieldindex)])
if value:
return {'title': title, 'value': value}
return {}
def checked_emissions(self):
title = "Emissionsarmes Produkt"
if self.context.tablename == 'substance_mixture':
fieldindex = possibleColumns(
self.context).getTerm('checked_emissions').token
value = self.get_attr_translation(
'boolvocab', str(self.article[int(fieldindex)]))
elif self.context.tablename == 'spray_powder':
fieldindex = possibleColumns(
self.context).getTerm('checked_emissions').token
value = self.get_attr_translation(
'boolvocab', str(self.article[int(fieldindex)]))
if value:
return {'title': title, 'value': value}
return {}
def evaporation_lane_150(self):
title = "Verdampfungsfaktor 150 Grad"
fieldindex = possibleColumns(
self.context).getTerm('evaporation_lane_150').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def evaporation_lane_160(self):
title = "Verdampfungsfaktor 160 Grad"
fieldindex = possibleColumns(
self.context).getTerm('evaporation_lane_160').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def evaporation_lane_170(self):
title = "Verdampfungsfaktor 170 Grad"
fieldindex = possibleColumns(
self.context).getTerm('evaporation_lane_170').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def evaporation_lane_180(self):
title = "Verdampfungsfaktor 180 Grad"
fieldindex = possibleColumns(
self.context).getTerm('evaporation_lane_180').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def date_checked(self):
title = "Prüfdatum"
fieldindex = possibleColumns(
self.context).getTerm('date_checked').token
if self.context.tablename == 'substance_mixture':
value = self.article[int(fieldindex)]
elif self.context.tablename == 'spray_powder':
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def flashpoint(self):
title = "Flammpunkt in °C"
fieldindex = possibleColumns(self.context).getTerm('flashpoint').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def ueg(self):
title = "UEG in g/m3"
fieldindex = possibleColumns(self.context).getTerm('ueg').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def response(self):
title = "Responsefaktor"
fieldindex = possibleColumns(self.context).getTerm('response').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def application_areas(self):
title = "Anwendungsgebiete"
fieldindex = possibleColumns(
self.context).getTerm('application_areas').token
mixtureid = possibleColumns(
self.context).getTerm('substance_mixture_id').token
value = self.translate_application_areas(
self.new_application_areas_translation2(
self.article[int(mixtureid)]))
if value:
return {'title': title, 'value': value}
return {}
def usecases(self):
title = "Verwendungszwecke"
fieldindex = possibleColumns(self.context).getTerm('usecases').token
mixtureid = possibleColumns(
self.context).getTerm('substance_mixture_id').token
value = self.translate_usecases(
self.new_usecase_translation2(self.article[int(mixtureid)]))
if value:
return {'title': title, 'value': value}
return {}
def concentration(self):
title = "Konzentration in wässriger Lösung"
fieldindex = possibleColumns(
self.context).getTerm('concentration').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def casnr(self):
title = "CAS-Nummer"
fieldindex = possibleColumns(self.context).getTerm('casnr').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def egnr(self):
title = "EG-Nummer"
fieldindex = possibleColumns(self.context).getTerm('egnr').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def dnel_lokal(self):
title = "DNEL Inhalation [mg/m3]: lokal"
fieldindex = possibleColumns(self.context).getTerm('dnel_lokal').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def dnel_systemisch(self):
title = "DNEL Inhalation [mg/m3]: systemisch"
fieldindex = possibleColumns(
self.context).getTerm('dnel_systemisch').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def formula(self):
title = "Formel"
fieldindex = possibleColumns(self.context).getTerm('formula').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def mol(self):
title = "Molmasse [g/mol]"
fieldindex = possibleColumns(self.context).getTerm('mol').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def homepage(self):
title = "Homepage"
fieldindex = possibleColumns(self.context).getTerm('homepage').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def productclass(self):
title = "Produktklasse"
if self.context.tablename == 'substance_mixture':
fieldindex = possibleColumns(
self.context).getTerm('productclass').token
value = self.get_attr_translation('produktklassenid',
self.article[int(fieldindex)])
elif self.context.tablename == 'spray_powder':
fieldindex = possibleColumns(
self.context).getTerm('productclass').token
value = self.get_attr_translation('produktklassenid',
self.article[int(fieldindex)])
if value:
return {'title': title, 'value': value}
def product_class(self):
title = "Produktklasse"
fieldindex = possibleColumns(
self.context).getTerm('product_class').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def starting_material(self):
title = "Ausgangsmaterial"
fieldindex = possibleColumns(
self.context).getTerm('starting_material').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def volume_share(self):
title = "Volumenanteil"
fieldindex = possibleColumns(
self.context).getTerm('volume_share').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def median_value(self):
title = "Medianwert"
fieldindex = possibleColumns(
self.context).getTerm('median_value').token
value = self.article[int(fieldindex)]
if value:
return {'title': title, 'value': value}
return {}
def edit_url(self):
if self.context.tablename == 'substance_mixture':
link = self.context.absolute_url(
) + '/update-mixture-form?itemid=%s' % self.itemid
return link
elif self.context.tablename == 'substance':
link = self.context.absolute_url(
) + '/update-substance-form?itemid=%s' % self.itemid
return link
elif self.context.tablename == 'manufacturer':
link = self.context.absolute_url(
) + '/update-manufacturer-form?itemid=%s' % self.itemid
return link
elif self.context.tablename == 'spray_powder':
link = self.context.absolute_url(
) + '/update-powder-form?itemid=%s' % self.itemid
return link
def delete_url(self):
if self.context.tablename == 'substance_mixture':
link = self.context.absolute_url(
) + '/delete-mixture-form?itemid=%s' % self.itemid
return link
elif self.context.tablename == 'substance':
link = self.context.absolute_url(
) + '/delete-substance-form?itemid=%s' % self.itemid
return link
elif self.context.tablename == 'manufacturer':
link = self.context.absolute_url(
) + '/delete-manufacturer-form?itemid=%s' % self.itemid
return link
elif self.context.tablename == 'spray_powder':
link = self.context.absolute_url(
) + '/delete-powder-form?itemid=%s' % self.itemid
return link
def userCanEdit(self):
if not ploneapi.user.is_anonymous():
current = ploneapi.user.get_current()
roles = ploneapi.user.get_roles(user=current)
if editrole in roles or 'Manager' in roles or 'Site Administrator' in roles:
return self.context.absolute_url(
) + '/update-%s-form' % self.context.tablename
return False
def get_article(self):
conn = psycopg2.connect(host=self.host,
user=self.username,
dbname=self.dbname,
password=self.password)
cur = conn.cursor()
tablename = self.context.tablename
select = "SELECT * from %s WHERE %s_id = %s" % (tablename, tablename,
self.itemid)
cur.execute(select)
article = cur.fetchall()[0]
print(article)
cur.close
conn.close()
return article
def get_image_url(self):
conn = psycopg2.connect(host=self.host,
user=self.username,
dbname=self.dbname,
password=self.password)
cur = conn.cursor()
tablename = self.context.tablename
select = "SELECT image_url from %s WHERE %s_id = %s" % (
tablename, tablename, self.itemid)
cur.execute(select)
uid = cur.fetchall()[0][0]
cur.close
conn.close()
if uid:
imageobj = ploneapi.content.get(UID=uid)
image_url = '%s/@@images/image/preview' % imageobj.absolute_url()
return image_url
else:
return False
"""
def get_machines(self):
machine_titles = []
conn = psycopg2.connect(host=self.host, user=self.username, dbname=self.dbname, password=self.password)
cur = conn.cursor()
select = "SELECT machine_id from mixture_pairs WHERE mixture_id = %s" %self.itemid
cur.execute(select)
machine_ids = cur.fetchall() #TODO: muss evenutell noch behandelt werden
cur.close()
for i in machine_ids:
cur = conn.cursor()
select = "SELECT title from printing_machine WHERE printing_machine_id = %s" %i
cur.execute(select)
machine_title = cur.fetchall()
cur.close()
machine_titles.append(machine_title)
conn.close()
return machine_titles
"""
def get_synonyms(self):
synonyms = []
select = "SELECT synonym_name from synonyms WHERE substance_id = %s" % self.itemid
synonyms = self.db.execute(select)
return synonyms
def get_manufacturer(self, manu):
select = "SELECT title from manufacturer WHERE manufacturer_id = %s" % manu
manufacturer = self.db.execute(select)
result = manufacturer[0][0]
return result
def translate_synonyms(self, synonyms):
resultstring = ""
index = 0
for i in synonyms:
resultstring = resultstring + "%s, " % (synonyms[index][0])
index = index + 1
resultstring = resultstring[:-2]
return resultstring
def get_recipes(self):
substances = []
select = "SELECT substance_id, concentration_min, concentration_max from recipes WHERE mixture_id = %s" % self.itemid
substance_ids = self.db.execute(select)
# Continue here
for sid, concentration_min, concentration_max in substance_ids:
select = "SELECT title from substance WHERE substance_id = %s" % sid
substance_title = self.db.execute(select)
entry = {
'title': substance_title,
'concentration_min': concentration_min,
'concentration_max': concentration_max
}
entry['resultstring'] = self.translate_recipes(entry)
substances.append(entry)
#self.db.close()
return substances
def translate_recipes(self, recipe):
resultstring = "%s (%s %s %s, %s %s %s)" % (
recipe['title'][0][0], ">=", recipe['concentration_min'], "%",
"<=", recipe['concentration_max'], "%")
return resultstring
def get_attr_translation(self, attribute, value):
vocabulary = get_vocabulary(attribute)
for i in vocabulary:
if i[0] == value:
return i[1]
return value
def get_none_translation(self, value):
if value == None or value == 'None':
return ''
else:
return value
def usecase_translation(self, value):
vocabulary = get_vocabulary('usecases')
newlist = list()
try:
for v in value:
for i in vocabulary:
if i[0] == v:
newlist.append(i[1])
result = ', '.join(newlist)
except:
result = ''
return result
def new_usecase_translation(self):
usecases = []
select = "SELECT usecase_id from usecasepairs WHERE mixture_id = %s" % self.itemid
usecaseids = self.db.execute(select)
# Continue here
for ucid in usecaseids:
select = "SELECT usecase_name from usecases WHERE usecase_id = %s" % ucid
usecase_title = self.db.execute(select)
entry = {'title': usecase_title}
usecases.append(entry)
# self.db.close()
return usecases
def new_usecase_translation2(self, id):
usecases = []
select = "SELECT usecase_id from usecasepairs WHERE mixture_id = %s" % id
usecaseids = self.db.execute(select)
# Continue here
for ucid in usecaseids:
select = "SELECT usecase_name from usecases WHERE usecase_id = %s" % ucid
usecase_title = self.db.execute(select)
entry = {'title': usecase_title}
usecases.append(entry)
# self.db.close()
return usecases
def translate_usecases(self, usecases):
resultstring = ""
index = 0
for i in usecases:
resultstring = resultstring + "%s, " % (
usecases[index]['title'][0][0])
index = index + 1
resultstring = resultstring[:-2]
return resultstring
def new_usecases_translation(self):
usecases = []
select = "SELECT usecase_id from usecasepairs WHERE mixture_id = %s" % self.itemid
caseids = self.db.execute(select)
# Continue here
for caseid in caseids:
select = "SELECT usecase_name from usecases WHERE usecase_id = %s" % caseid
case_title = self.db.execute(select)
entry = {'title': case_title}
usecases.append(entry)
# self.db.close()
return usecases
def new_application_areas_translation(self):
applicationareas = []
select = "SELECT area_id from areapairs WHERE mixture_id = %s" % self.itemid
areaids = self.db.execute(select)
# Continue here
for arid in areaids:
select = "SELECT application_area_name from application_areas WHERE application_area_id = %s" % arid
area_title = self.db.execute(select)
entry = {'title': area_title}
applicationareas.append(entry)
# self.db.close()
return applicationareas
def new_application_areas_translation2(self, id):
applicationareas = []
select = "SELECT area_id from areapairs WHERE mixture_id = %s" % id
areaids = self.db.execute(select)
# Continue here
for arid in areaids:
select = "SELECT application_area_name from application_areas WHERE application_area_id = %s" % arid
area_title = self.db.execute(select)
entry = {'title': area_title}
applicationareas.append(entry)
# self.db.close()
return applicationareas
def translate_application_areas(self, areas):
resultstring = ""
index = 0
for i in areas:
resultstring = resultstring + "%s, " % (
areas[index]['title'][0][0])
index = index + 1
resultstring = resultstring[:-2]
return resultstring
def application_areas_translation(self, value):
vocabulary = get_vocabulary('application_areas')
newlist = list()
try:
for v in value:
for i in vocabulary:
if i[0] == v:
newlist.append(i[1])
result = ', '.join(newlist)
except:
result = ''
return result
def unbundle_list(self, value):
if value:
result = value.split('@')
else:
result = list()
return result
def getRightUrl(self, value):
urlels = value.split('=')
if len(urlels[1]) < 6:
param = urlels[1].zfill(6)
return urlels[0] + '=' + param
return value
class CreateIngredientForm(WTFormView):
formClass = CreateForm
buttons = ('Speichern', 'Abbrechen')
def __call__(self):
self.form.itemid.default = self.request.get('itemid')
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
self.host = self.context.aq_parent.host
self.dbname = self.context.aq_parent.database
self.username = self.context.aq_parent.username
self.password = self.context.aq_parent.password
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
return self.index()
def renderForm(self):
try:
select = "SELECT substance_id, title, casnr, egnr FROM substance ORDER BY title;"
substances = self.db.execute(select)
except:
substances = []
optionlist = list()
for i in substances:
subid = i[0]
subname = i[1]
subcas = i[2]
subeg = i[3]
subentry = f"{subname} CAS:{subcas} EG:{subeg} ID:{subid}"
optionlist.append((i[0], subentry))
self.form.substance.choices = optionlist
self.form.process()
return self.formTemplate()
def submit(self, button):
self.form.itemid.default = self.request.get('itemid')
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.itemid.data
if button == 'Speichern': #and self.validate():
insert = """INSERT INTO recipes (mixture_id, substance_id, concentration_min, concentration_max)
VALUES (%s, %s, %s, %s);""" \
% (self.form.itemid.data,
int(self.form.substance.data.split('ID:')[-1]),
self.form.concentration_min.data,
self.form.concentration_max.data,
)
try:
self.db.execute(insert)
self.db.close()
message = u'Der Bestandteil wurde erfolgreich hinzugefügt.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
except:
message = u'Fehler beim Hinzufügen des Bestandteils'
ploneapi.portal.show_message(message=message,
type='error',
request=self.request)
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class CreateFormView(WTFormView):
formClass = CreateForm
buttons = ('Speichern', 'Abbrechen')
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
self.host = self.context.aq_parent.host
self.dbname = self.context.aq_parent.database
self.username = self.context.aq_parent.username
self.password = self.context.aq_parent.password
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
return self.index()
def renderForm(self):
try:
command = "SELECT manufacturer_id, title FROM manufacturer ORDER BY title;"
manus = self.db.execute(command)
except:
manus = []
self.form.manufacturer_id.choices = manus
self.form.process()
return self.formTemplate()
def create_image(self, image, title):
filedata = image.data.read()
filename = image.data.filename
blobimage = NamedBlobImage(data=filedata, filename=filename)
obj = ploneapi.content.create(type='Image',
title=title,
image=blobimage,
container=self.context)
obj.indexObject()
transaction.commit()
return obj.UID()
def submit(self, button):
image_url = ''
if self.form.image_url.data.filename:
image_url = self.create_image(self.form.image_url,
self.form.title.data)
redirect_url = self.context.aq_parent.absolute_url()
if button == 'Speichern': #and self.validate():
insert = """INSERT INTO spray_powder VALUES (DEFAULT, '%s', '%s', '%s',
%s, %s, %s, %s, %s, %s, %s, %s);""" % (
self.form.title.data, self.form.description.data,
self.context.aq_parent.get_webcode(),
check_value(self.form.product_class.data),
check_value(self.form.starting_material.data),
check_value(self.form.median_value.data),
check_value(self.form.volume_share.data),
check_value(self.form.checked_emissions.data),
check_value(self.form.date_checked.data),
check_value(image_url), self.form.manufacturer_id.data)
if self.form.image_url.data.filename:
try:
self.db.execute(insert)
message = u'Das Wasch- und Reinigungsmittel wurde erfolgreich gespeichert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
except:
imageobj = ploneapi.content.get(UID=image_url)
ploneapi.content.delete(imageobj)
message = u'Fehler beim Hinzufügen des Gefahrstoffgemisches'
ploneapi.portal.show_message(message=message,
type='error',
request=self.request)
self.db.close()
else:
self.db.execute(insert)
self.db.close()
message = u'Das Wasch- und Reinigungsmittel wurde erfolgreich gespeichert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class UpdateFormView(CreateFormView):
formClass = UpdateForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
getter = """SELECT title, description, product_class, starting_material, median_value, volume_share, checked_emissions
FROM %s WHERE %s_id = %s;""" % (
self.context.tablename, self.context.tablename, self.itemid)
self.result = self.db.execute(getter)
self.db.close()
return self.index()
def renderForm(self):
self.form.title.default = self.result[0][0]
self.form.description.default = self.result[0][1]
self.form.product_class.default = self.result[0][2]
self.form.starting_material.default = self.result[0][3]
self.form.median_value.default = self.result[0][4]
self.form.volume_share.default = self.result[0][5]
self.form.checked_emissions.default = self.result[0][6]
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.item_id.data
if button == 'Speichern': #and self.validate():
command = """UPDATE spray_powder SET title='%s', description='%s', product_class='%s', starting_material='%s',
median_value=%s, volume_share=%s, checked_emissions=%s
WHERE spray_powder_id = %s;""" % (
self.form.title.data, self.form.description.data,
self.form.product_class.data, self.form.starting_material.data,
check_value(self.form.median_value.data),
check_value(self.form.volume_share.data),
check_value(
self.form.checked_emissions.data), self.form.item_id.data)
self.db.execute(command)
message = u'Der Druckbestäubungspuder wurde erfolgreich aktualisiert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
#message = u'Fehler beim Aktualisieren des Gefahrstoffgemisches'
#ploneapi.portal.show_message(message=message, type='error', request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class UpdateFormView(CreateFormView):
formClass = UpdateForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
getter = """SELECT title, description, casnr, egnr, concentration, skin_category, branch, formula, mol, link
FROM %s WHERE %s_id = %s;""" % (
self.context.tablename, self.context.tablename, self.itemid)
self.result = self.db.execute(getter)
self.db.close()
return self.index()
def renderForm(self):
self.form.title.default = self.result[0][0]
self.form.description.default = self.result[0][1]
self.form.casnr.default = self.result[0][2]
self.form.egnr.default = self.result[0][3]
self.form.concentration.default = self.result[0][4]
self.form.skin_category.default = self.result[0][5]
self.form.branch.default = self.result[0][6]
self.form.formula.default = self.result[0][7]
self.form.mol.default = self.result[0][8]
self.form.gestislink.default = self.result[0][9]
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
redirect_url = self.context.aq_parent.absolute_url()
if button == 'Speichern': #and self.validate():
command = """UPDATE substance SET title='%s', description='%s', casnr=%s, egnr=%s, concentration=%s,
skin_category='%s', branch='%s', formula='%s', mol='%s', link='%s'
WHERE substance_id = %s;""" % (
self.form.title.data, self.form.description.data,
check_value(
self.form.casnr.data), check_value(self.form.egnr.data),
check_value(self.form.concentration.data),
self.form.skin_category.data, self.form.branch.data,
self.form.formula.data, self.form.mol.data,
self.form.gestislink.data, self.form.item_id.data)
self.db.execute(command)
message = u'Der Reinstoff wurde erfolgreich aktualisiert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
#message = u'Fehler beim Aktualisieren des Gefahrstoffgemisches'
#ploneapi.portal.show_message(message=message, type='error', request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class UpdateFormView(CreateFormView):
formClass = UpdateForm
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
self.itemid = self.request.get('itemid')
getter = """SELECT title, description, branch, substance_type,
application_areas, usecases, evaporation_lane_150, evaporation_lane_160, evaporation_lane_170,
evaporation_lane_180, ueg, response, skin_category, checked_emissions, date_checked, flashpoint,
values_range, comments, image_url, productclass
FROM %s WHERE %s_id = %s;""" % (
self.context.tablename, self.context.tablename, self.itemid)
relationalgetter = "SELECT application_areas.application_area_name FROM application_areas, areapairs WHERE areapairs.mixture_id = %s and areapairs.area_id = application_areas.application_area_id ;" % self.itemid
relationalgetter2 = "SELECT usecases.usecase_name FROM usecases, usecasepairs WHERE usecasepairs.mixture_id = %s and usecasepairs.usecase_id = usecases.usecase_id ;" % self.itemid
self.result = self.db.execute(getter)
self.relational = self.db.execute(relationalgetter)
self.relational2 = self.db.execute(relationalgetter2)
self.db.close()
return self.index()
def renderForm(self):
self.form.title.default = self.result[0][0]
self.form.description.default = self.result[0][1]
self.form.branch.default = self.result[0][2]
self.form.substance_type.default = self.result[0][3]
self.form.application_areas.default = new_list_handler2(
self.relational)
self.form.usecases.default = new_list_handler3(self.relational2)
self.form.evaporation_lane_150.default = self.result[0][6]
self.form.evaporation_lane_160.default = self.result[0][7]
self.form.evaporation_lane_170.default = self.result[0][8]
self.form.evaporation_lane_180.default = self.result[0][9]
self.form.productclass.default = self.result[0][19]
self.form.ueg.default = self.result[0][10]
self.form.response.default = self.result[0][11]
self.form.skin_category.default = self.result[0][12]
self.form.checked_emissions.default = self.result[0][13]
self.form.date_checked.default = self.result[0][14]
self.form.flashpoint.default = self.result[0][15]
self.form.values_range.default = self.result[0][16]
self.form.comments.default = self.result[0][17]
self.form.image_url.default = self.result[0][18]
self.form.item_id.default = self.itemid
self.form.process()
return self.formTemplate()
def submit(self, button):
"""
"""
redirect_url = self.context.absolute_url(
) + '/single_view?item=' + self.form.item_id.data
if button == 'Speichern': #and self.validate():
command = """UPDATE substance_mixture SET title=%s, description=%s, branch=%s, substance_type=%s,
evaporation_lane_150=%s, evaporation_lane_160=%s, evaporation_lane_170=%s, evaporation_lane_180=%s,
ueg=%s, response=%s, skin_category=%s, checked_emissions=%s,
flashpoint=%s, values_range=%s, comments=%s, productclass=%s
WHERE substance_mixture_id = %s;""" % \
(check_value(self.form.title.data),
check_value(self.form.description.data),
check_value(self.form.branch.data),
check_value(self.form.substance_type.data),
check_value(self.form.evaporation_lane_150.data),
check_value(self.form.evaporation_lane_160.data),
check_value(self.form.evaporation_lane_170.data),
check_value(self.form.evaporation_lane_180.data),
check_value(self.form.ueg.data),
check_value(self.form.response.data),
check_value(self.form.skin_category.data),
check_value(self.form.checked_emissions.data),
check_value(self.form.flashpoint.data),
check_value(self.form.values_range.data),
check_value(self.form.comments.data),
check_value(self.form.productclass.data),
check_value(self.form.item_id.data))
self.db.execute(command)
"""
neueliste = list()
neuevocab = list()
resultlist = list()
listtoadd = list()
vocabulary = get_vocabulary('application_areas')
for n in vocabulary:
neuevocab.append(n[0])
for i in self.form.application_areas.data:
getfromvocab = "SELECT DISTINCT mixture_id, area_id FROM areapairs, application_areas WHERE mixture_id = %s" % self.form.item_id.data
currentareas = self.db.execute(getfromvocab)
for v in currentareas:
command = "SELECT application_area_name FROM application_areas WHERE application_area_id = %s" % v[1]
result = self.db.execute(command)
result = result[0][0]
resultlist.append(result)
for m in neuevocab:
#import pdb; pdb.set_trace()
for l in resultlist:
if i == l and i not in neueliste:
neueliste.append(i)
if i not in neueliste:
idcommand = "SELECT application_area_id FROM application_areas WHERE application_area_name = 'Reiniger_Leitstaende_Sensoren'"
richtigeid = self.db.execute(idcommand)
insertcommand = "INSERT INTO areapairs (area_id, mixture_id) VALUES (%s, %s);" % (richtigeid[0][0],
self.form.item_id.data)
self.db.execute(insertcommand)
print(neueliste)
print(listtoadd)
#del newlist[::2]
#import pdb; pdb.set_trace()
"""
deletecommand = "DELETE FROM areapairs WHERE mixture_id = %s" % self.form.item_id.data
self.db.execute(deletecommand)
for i in self.form.application_areas.data:
insertcommand = "INSERT INTO areapairs (area_id, mixture_id) VALUES (%s, %s)" % (
int(i), self.form.item_id.data)
self.db.execute(insertcommand)
deletecommand2 = "DELETE FROM usecasepairs WHERE mixture_id = %s" % self.form.item_id.data
self.db.execute(deletecommand2)
for i in self.form.usecases.data:
insertcommand2 = "INSERT INTO usecasepairs (usecase_id, mixture_id) VALUES (%s, %s)" % (
int(i), self.form.item_id.data)
self.db.execute(insertcommand2)
message = u'Das Gefahrstoffgemisch wurde erfolgreich aktualisiert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
#message = u'Fehler beim Aktualisieren des Gefahrstoffgemisches'
#ploneapi.portal.show_message(message=message, type='error', request=self.request)
self.db.close()
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)
class CreateFormView(WTFormView):
formClass = CreateForm
buttons = ('Speichern', 'Abbrechen')
def __call__(self):
dbdata = self.context.aq_parent
self.db = DBConnect(host=dbdata.host,
db=dbdata.database,
user=dbdata.username,
password=dbdata.password)
self.host = self.context.aq_parent.host
self.dbname = self.context.aq_parent.database
self.username = self.context.aq_parent.username
self.password = self.context.aq_parent.password
if self.submitted:
button = self.hasButtonSubmitted()
if button:
result = self.submit(button)
if result:
return result
return self.index()
def renderForm(self):
try:
select = "SELECT manufacturer_id, title FROM manufacturer ORDER BY title;"
erg = self.db.execute(select)
manus = [(result[0], result[1] + ' ID:' + str(result[0]))
for result in erg]
except:
manus = []
self.form.manufacturer_id.choices = manus
self.form.process()
return self.formTemplate()
def create_image(self, image, title):
filedata = image.data.read()
filename = image.data.filename
blobimage = NamedBlobImage(data=filedata, filename=filename)
obj = ploneapi.content.create(type='Image',
title=title,
image=blobimage,
container=self.context)
obj.indexObject()
transaction.commit()
return obj.UID()
def submit(self, button):
image_url = ''
if self.form.image_url.data.filename:
image_url = self.create_image(self.form.image_url,
self.form.title.data)
redirect_url = self.context.aq_parent.absolute_url()
if button == 'Speichern': #and self.validate():
conn = psycopg2.connect(host=self.host,
user=self.username,
dbname=self.dbname,
password=self.password)
cur = conn.cursor()
insert = """INSERT INTO substance_mixture (title, description, webcode, branch, substance_type,
evaporation_lane_150, evaporation_lane_160,
evaporation_lane_170, evaporation_lane_180, ueg, response,
skin_category, checked_emissions, date_checked, flashpoint,
values_range, comments, image_url, manufacturer_id)
VALUES ('%s', '%s', '%s', %s, '%s',
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s);""" \
% (self.form.title.data,
self.form.description.data,
self.context.aq_parent.get_webcode(),
check_value(self.form.branch.data),
self.form.substance_type.data,
check_value(self.form.evaporation_lane_150.data),
check_value(self.form.evaporation_lane_160.data),
check_value(self.form.evaporation_lane_170.data),
check_value(self.form.evaporation_lane_180.data),
check_value(self.form.ueg.data),
check_value(self.form.response.data),
check_value(self.form.skin_category.data),
self.form.checked_emissions.data,
check_value(self.form.date_checked.data),
check_value(self.form.flashpoint.data),
self.form.values_range.data,
check_value(self.form.comments.data),
check_value(image_url),
check_value(self.form.manufacturer_id.data.split('ID:')[-1]))
areaids = list()
for i in self.form.application_areas.data:
selectcommand = "SELECT substance_mixture_id FROM substance_mixture ORDER BY substance_mixture_id DESC LIMIT 1"
selectedid = self.db.execute(selectcommand)
areaids.append([int(i), (int(selectedid[0][0])) + 1])
caseids = list()
for i in self.form.usecases.data:
selectcommand = "SELECT substance_mixture_id FROM substance_mixture ORDER BY substance_mixture_id DESC LIMIT 1"
selectedid = self.db.execute(selectcommand)
caseids.append([int(i), (int(selectedid[0][0])) + 1])
if self.form.image_url.data.filename:
try:
self.db.execute(insert)
for i in areaids:
insertcommand = "INSERT INTO areapairs (area_id, mixture_id) VALUES (%s, %s)" % (
i[0], i[1])
self.db.execute(insertcommand)
for i in caseids:
insertcommand = "INSERT INTO usecasepairs (usecase_id, mixture_id) VALUES (%s, %s)" % (
i[0], i[1])
self.db.execute(insertcommand)
message = u'Das Wasch- und Reinigungsmittel wurde erfolgreich gespeichert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
except:
imageobj = ploneapi.content.get(UID=image_url)
ploneapi.content.delete(imageobj)
message = u'Fehler beim Hinzufügen des Gefahrstoffgemisches'
ploneapi.portal.show_message(message=message,
type='error',
request=self.request)
self.db.close()
else:
self.db.execute(insert)
for i in areaids:
insertcommand = "INSERT INTO areapairs (area_id, mixture_id) VALUES (%s, %s)" % (
i[0], i[1])
self.db.execute(insertcommand)
for i in caseids:
insertcommand = "INSERT INTO usecasepairs (usecase_id, mixture_id) VALUES (%s, %s)" % (
i[0], i[1])
self.db.execute(insertcommand)
self.db.close()
message = u'Das Wasch- und Reinigungsmittel wurde erfolgreich gespeichert.'
ploneapi.portal.show_message(message=message,
type='info',
request=self.request)
return self.request.response.redirect(redirect_url)
elif button == 'Abbrechen':
return self.request.response.redirect(redirect_url)