def doExportXls(self, abfrage, sql):
"""
Liefert die exportieren Daten als MS Excel.
@param abfrage Abfrage Objekt
@param sql SQL Anweisung
HINT: um Macros bereinigt
"""
c = self.db.cursorFactory()
c.row_factory = sqlite3.Row
xlsRow = 0
xlsCol = 0
dc = Dateconverter()
def Latin1ToUtf8(self, value):
if value is None: return None
try:
return value.decode('latin1').encode('utf8')
except:
return value
xlsFile = tempfile.NamedTemporaryFile(delete=False,
prefix='export-{0}'.format(
abfrage.abfID))
xlsFileName = xlsFile.name
xlsFile.close()
self.workbook = Workbook(encoding='UTF-8')
self.sheet = self.workbook.add_sheet('Data')
self.sheet.write(
xlsRow, 0,
'Auswertung: {0} vom: {1}'.format(abfrage.abfName,
dc.giveAsGermanDateTime('now')))
xlsRow += 1
cnt = 0
for row in c.execute(sql):
cnt += 1
xlsCol = 0
if cnt == 1:
colHeader = row.keys()
for colh in colHeader:
self.sheet.write(xlsRow, xlsCol, colh)
xlsCol += 1
xlsCol = 0
xlsRow += 1
for v in row:
self.sheet.write(xlsRow, xlsCol, v)
xlsCol += 1
try:
self.workbook.save(xlsFileName)
except Exception, e:
raise Exception(e)
def doExportXls(self,abfrage,sql):
"""
Liefert die exportieren Daten als MS Excel.
@param abfrage Abfrage Objekt
@param sql SQL Anweisung
HINT: um Macros bereinigt
"""
c = self.db.cursorFactory()
c.row_factory = sqlite3.Row
xlsRow=0
xlsCol=0
dc = Dateconverter()
def Latin1ToUtf8(self,value):
if value is None: return None
try:
return value.decode('latin1').encode('utf8')
except:
return value
xlsFile = tempfile.NamedTemporaryFile(delete=False,prefix='export-{0}'.format(abfrage.abfID))
xlsFileName = xlsFile.name
xlsFile.close()
self.workbook = Workbook(encoding='UTF-8')
self.sheet = self.workbook.add_sheet('Data')
self.sheet.write(xlsRow,0,'Auswertung: {0} vom: {1}'.format(abfrage.abfName,dc.giveAsGermanDateTime('now')))
xlsRow+=1
cnt = 0
for row in c.execute(sql):
cnt += 1
xlsCol=0
if cnt == 1:
colHeader = row.keys()
for colh in colHeader:
self.sheet.write(xlsRow,xlsCol,colh)
xlsCol+=1
xlsCol=0
xlsRow+=1
for v in row:
self.sheet.write(xlsRow,xlsCol,v)
xlsCol+=1
try:
self.workbook.save(xlsFileName)
except Exception,e:
raise Exception(e)
def isDate(self,value):
"""
Pruefen auf gueltiges Datum vorhanden ist dies geschieht durch die Klasse Dateconverter
@param value
@return [True|False]
"""
dc = Dateconverter()
try:
dc.fromString(value)
except:
return False
return True
def isDate(self, value):
"""
Pruefen auf gueltiges Datum vorhanden ist dies geschieht durch die Klasse Dateconverter
@param value
@return [True|False]
"""
dc = Dateconverter()
value = value.replace(',', '.')
value = value.replace('*', ' ')
try:
dc.fromString(value)
except:
return False
return True
def __setattr__(self, name, value):
if name == 'id':
if not self.isInteger(value):
raise ValueError(self.errmsg('int', name, value))
else:
self.setValue(name, value)
elif name == 'name':
if not self.isString(value):
raise ValueError(self.errmsg('str', name, value))
else:
self.setValue('name', value)
elif name == 'gehalt':
if not self.isFloat(value):
raise ValueError(self.errmsg('float', name, value))
else:
self.setValue(name, value)
elif name == 'email':
if not self.isEmail(value):
raise ValueError(self.errmsg('email', name, value))
else:
self.setValue(name, value)
elif name == 'groesseCm':
if self.isInteger(value) or self.isFloat(value):
self.setValue(name, value * 2.54)
else:
raise ValueError(self.errmsg('int/float', name, value))
elif name == 'geburtsdatum':
if self.isDate(value):
dc = Dateconverter()
self.setValue(name, dc.giveAsANSIDate(value))
else:
raise ValueError(self.errmsg('int/float', name, value))
else:
raise ValueError(
"'%(name)s' ist kein Attribut der Klasse %(classname)s" % {
'name': name,
'classname': self.__class__
})
def __setattr__(self, name, value):
if name == "id":
if not self.isInteger(value):
raise ValueError(self.errmsg("int", name, value))
else:
self.setValue(name, value)
elif name == "name":
if not self.isString(value):
raise ValueError(self.errmsg("str", name, value))
else:
self.setValue("name", value)
elif name == "gehalt":
if not self.isFloat(value):
raise ValueError(self.errmsg("float", name, value))
else:
self.setValue(name, value)
elif name == "email":
if not self.isEmail(value):
raise ValueError(self.errmsg("email", name, value))
else:
self.setValue(name, value)
elif name == "groesseCm":
if self.isInteger(value) or self.isFloat(value):
self.setValue(name, value * 2.54)
else:
raise ValueError(self.errmsg("int/float", name, value))
elif name == "geburtsdatum":
if self.isDate(value):
dc = Dateconverter()
self.setValue(name, dc.giveAsANSIDate(value))
else:
raise ValueError(self.errmsg("int/float", name, value))
else:
raise ValueError(
"'%(name)s' ist kein Attribut der Klasse %(classname)s" % {"name": name, "classname": self.__class__}
)
from dateconverter import Dateconverter
def hasException(code):
"""Prueft ob bei uebergebenen Code eine Exception auftritt"""
retval = False
try:
eval(code)
except:
return True
return False
dc = Dateconverter()
assert hasException("dc.giveAsANSIDate()")
assert dc.fromString("1.1.2011") == "2011-01-01T00:00"
assert dc.fromString("01.01.2011") == "2011-01-01T00:00"
assert dc.fromString("2011-01-01") == "2011-01-01T00:00"
assert dc.fromString("2011-01-01") == "2011-01-01T00:00"
assert dc.fromString("2011-01-01T01:01") == "2011-01-01T01:01"
assert dc.fromString("2011-01-01T01:01:33") == "2011-01-01T01:01:33"
assert dc.fromString("31.12.2011 11:22:33") == "2011-12-31T11:22:33"
assert dc.fromString("31.12.2011 10:20") == "2011-12-31T10:20"
assert hasException("dc.fromString('31.2.2011')")
def export(self):
workbook = None
sheet = None
gruppe = GruppeDomain(db=self.db)
personen = PersonDomain(db=self.db)
xlsRow=0
xlsCol=0
dc = Dateconverter()
xlsFile = tempfile.NamedTemporaryFile(delete=False,prefix='export-personen')
xlsFileName = xlsFile.name
xlsFile.close()
self.workbook = Workbook(encoding='UTF-8')
self.sheet = self.workbook.add_sheet('Data')
self.insertList(sheet,xlsRow,['Auswertung vom: {0}'.format(dc.giveAsGermanDateTime('now'))])
xlsRow+=1
auxFilter = self.cgiparam('_filter')
_filter = Utility.normalizeFilter(auxFilter)
where = '(1=1)'
if _filter.startswith('id:'):
where = "perID={0}".format(_filter[3:])
elif _filter.startswith('tn:'):
where = "perArt like '%-{0}'".format(_filter[3:])
elif _filter.startswith('bvkey:'):
where = "perBVKey='{0}'".format(_filter[6:])
elif _filter == '%':
pass
else:
where = "lower(perNachname) like lower('%{0}%') or lower(perVorname) like lower('%{0}%')".format(_filter)
if self.cgiparam('_perStatus'):
where += " AND perStatus='{0}'".format(self.cgiparam('_perStatus'))
if self.cgiparam('_perArt'):
where += " AND perArt='{0}'".format(self.cgiparam('_perArt'))
if self.cgiparam('_grpID'):
where += " AND grpID='{0}'".format(self.cgiparam('_grpID'))
self.insertList(sheet,xlsRow,[
'ID',
'Vorname',
'Nachname',
'Gender',
'Kl.Gruppe',
'Status',
'Art',
'Verpflegung',
'EMail',
'Telefon'
])
xlsRow+=1
for person in personen.eachDomain(where=where):
gruppe.get(person.grpID or 0)
self.insertList(sheet,xlsRow,[
person.perID,
person.perVorname,
person.perNachname,
person.perGender,
person.kgrKennung,
person.perStatus,
person.perArt,
person.perVerpflegung,
person.perEMail or '',
person.perTelefon or ''
])
xlsRow+=1
try:
self.workbook.save(xlsFileName)
except Exception,e:
raise Exception(e)
def __setattr__(self,name,value):
"""
Pruefen auf Typengueltigkeit.
HINT:
geht die Pruefung fehl, so wird ein
Fehler geworfen.
@param name Domainfeldname
@param value Wert
"""
# Wurde Typenueberpruefung abgeschalten
if not self.typecheckStrict:
self.__dict__[name] = value
return
# None als Eingabewert erlaubt.
if self.typecheckNoneAllowed and value == None:
self.__dict__[name] = value
return
# Ist das ein Domainfeld
if name in self.meta['fields']:
# Feldobjekt
fld = self.meta['fields'][name]
# Wurde type in meta deklariert
if 'type' in fld:
# Integer pruefen
if fld['type'] == 'Integer':
if self.isInteger(value):
self.__dict__[name] = value
else:
self.raiseTypeError(fld['type'],name,value)
# String pruefen
elif fld['type'] == 'String':
if self.isString(value):
self.__dict__[name] = value
else:
self.raiseTypeError(fld['type'],name,value)
# Emailadresse pruefen
elif fld['type'] == 'Email':
if self.isString(value):
self.__dict__[name] = value
else:
self.raiseTypeError(fld['type'],name,value)
# Float
elif fld['type'] == 'Float':
if self.isFloat(value):
self.__dict__[name] = value
else:
self.raiseTypeError(fld['type'],name,value)
# Double ist equivalent zu float
elif fld['type'] == 'Double':
if self.isFloat(value):
self.__dict__[name] = value
else:
self.raiseTypeError(fld['type'],name,value)
# AnsiDate
# In der Domain kann die Option secs (True/False) angegeben werden
# diese steuert ob Sekunden angegeben werden sollen.
elif fld['type'] == 'AnsiDate':
if self.isDate(value):
dc = Dateconverter()
secs = True
if 'secs' in fld: secs = fld['secs']
self.__dict__[name] = dc.giveAsANSIDateTime(value,secs=secs)
else:
self.raiseTypeError(fld['type'],name,value)
else:
raise Exception("Ungueltiger Type '{0} in Feld {1}".format(fld['type'],name))
return
else:
pass
else:
self.__dict__[name] = value
from dateconverter import Dateconverter
def hasException(code):
"""Prueft ob bei uebergebenen Code eine Exception auftritt"""
retval = False
try:
eval(code)
except:
return True
return False
dc = Dateconverter()
assert (hasException("dc.giveAsANSIDate()"))
assert (dc.fromString('1.1.2011') == '2011-01-01T00:00')
assert (dc.fromString('01.01.2011') == '2011-01-01T00:00')
assert (dc.fromString('2011-01-01') == '2011-01-01T00:00')
assert (dc.fromString('2011-01-01') == '2011-01-01T00:00')
assert (dc.fromString('2011-01-01T01:01') == '2011-01-01T01:01')
assert (dc.fromString('2011-01-01T01:01:33') == '2011-01-01T01:01:33')
assert (dc.fromString('31.12.2011 11:22:33') == '2011-12-31T11:22:33')
assert (dc.fromString('31.12.2011 10:20') == '2011-12-31T10:20')
assert (hasException("dc.fromString('31.2.2011')"))
assert (hasException("dc.fromString('31.2.2011 24:01')"))
db = Database('oracle', 'SCOTT', 'TIGER', 'v9i', '194.37.51.27', '1521')
def hasException(code):
"""Prueft ob bei uebergebenen Code eine Exception auftritt"""
retval = False
try:
eval(code)
except:
return True
return False
dc = Dateconverter()
t1 = TestDomain(db)
t2 = TestDomain(db)
t1.deleteAll(where='1=1')
t1.clear()
t1.tstID = 1
t1.tstDate = dc.fromString('1.1.2010')
t2.tstID = 23
t2.tstDate = t1.tstDate
print t1.tstDate
print t2.tstDate
from domain.testdomain import TestDomain
from dateconverter import Dateconverter
db = Database('oracle','SCOTT','TIGER','v9i','194.37.51.27','1521')
def hasException (code):
"""Prueft ob bei uebergebenen Code eine Exception auftritt"""
retval = False
try:
eval(code)
except:
return True
return False
dc = Dateconverter()
t1 = TestDomain(db)
t2 = TestDomain(db)
t1.deleteAll(where='1=1')
t1.clear()
t1.tstID = 1
t1.tstDate = dc.fromString('1.1.2010')
t2.tstID=23
t2.tstDate = t1.tstDate
print t1.tstDate
print t2.tstDate
def getCurrentDate(self):
"""
Liefert heutiges Datum
"""
dc = Dateconverter()
return dc.giveAsGermanDate('now')
