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')