def add(self, item): try: session = get_session(DB_KEY) session.add(item) except Exception, e: # This might be a bit too generic return e
def set(self, value, _sa_initiator=None): key = self.keyfunc(value) if key is None: session = get_session(DB_KEY) session.flush() key = self.keyfunc(value) self.__setitem__(key, value, _sa_initiator)
def __init__(self, parent, name, quizz, quizzes): self.__parent__ = parent self.__name__ = name self.quizz = quizz self.quizzes = quizzes session = get_session('school') courses = session.query(Course).\ filter(Course.quizz_type == quizz.__tablename__).\ filter(Course.company_id == parent.id).\ join(Student, and_( Student.course_id==Course.id, Student.completion_date != None )).\ group_by(Course.id).\ having(func.count(Student.access) >= 7) rc = [] for c in courses.all(): title = c.name if len(list(c.sessions)) > 1: title = "%s [Durchschnitt]" %(c.name) rc.append(SimpleTerm(value=c, token=c.id, title=title)) # self.all_courses = SimpleVocabulary([ # SimpleTerm(value=c, token=c.id, title=c.name) # for c in courses.all() # ]) self.all_courses = SimpleVocabulary(rc) self.courses = [c.value for c in self.all_courses]
def accounts_choice(context): session = get_session('school') accounts = session.query(Account).all() return SimpleVocabulary([ SimpleTerm(value=c.email, token=c.email, title='%s (%s)' % (c.name, c.email)) for c in accounts])
def handle_save(self): data, errors = self.extractData() if errors: self.flash(_(u'An error occurred.')) return FAILURE session = get_session('school') strategy = dict( nb_students=data.pop('nb_students'), strategy=data.get('strategy') ) clssession = ClassSession(**data) clssession.course_id = self.context.id clssession.company_id = self.context.__parent__.id session.add(clssession) session.flush() session.refresh(clssession) if strategy.get('strategy') in ('mixed','fixed'): if strategy['nb_students'] <= 7: self.flash(u'Auswertungen sind erst ab 7 Teilnehmer zulässig. Bitte erhöhen Sie die Anzahl der Teilnehmer auf mindestens 7') return FAILURE for student in clssession.generate_students(strategy['nb_students']): clssession.append(student) self.flash(_(u'Session added with success.')) self.redirect('%s' % self.application_url()) return SUCCESS
def log_in(self, request, username, password, **kws): session = get_session('school') account = session.query(Account).filter( func.lower(Account.email) == username.lower()) if account.count() == 1: account = account.first() else: account = None send(u"Benutzer konnte nicht gefunden werden", BASE_MESSAGE_TYPE) return if account is not None: return account pwhash = hashed(password, account.salt) if account.password == pwhash: if account.activated is not None: return account activation = kws.get('activation') if activation is not None: if activation == account.activation: account.activated = datetime.now() return account else: return SuccessMarker( 'Activation failed', False, url=activate_url(request.path, **kws)) else: return SuccessMarker( 'Aktivierungs-Key Fehlt', False, url=activate_url(request.path, **kws)) send("Falsches Passwort", BASE_MESSAGE_TYPE) return None
def all_vouchers(context): items = [SimpleTerm(None, None, u'Bitte auswählen')] session = get_session('ukhvoucher') query = session.query(Voucher) for item in query.all(): items.append(SimpleTerm(item.oid, token=item.oid, title="%s - %s" %(item.title, item.status.strip()))) return SimpleVocabulary(items)
def accounts(context): session = get_session('ukhvoucher') items = [SimpleTerm(None, None, u'Bitte auswählen')] for user_id in session.query(distinct(Voucher.user_id)).all(): userid = int(user_id[0]) items.append(SimpleTerm(int(userid), token=userid, title=userid)) return SimpleVocabulary(items)
def handle_save(self): data, errors = self.extractData() journal_note = data.pop('note') now = datetime.now() if errors: self.flash(_(u'Es ist ein Fehler aufgetreten!')) return FAILURE number = data.get('number', 0) if number: session = get_session('ukhvoucher') try: from sqlalchemy.sql.functions import max oid = int(session.query(max(Voucher.oid)).one()[0]) + 1 except: oid = 100000 from ukhvoucher.models import Generation import json p = int(session.query(max(Generation.oid)).one()[0] or 0) + 1 generation = Generation( oid=p, date=now.strftime('%Y-%m-%d'), type=data['kategorie'], data=json.dumps('Manuelle Erzeugung'), user=self.request.principal.id, uoid=oid ) for idx in range(number): voucher = Voucher( creation_date=datetime.now().strftime('%Y-%m-%d'), status=CREATED, cat = data['kategorie'], user_id=self.context.oid, generation_id=p, oid=oid) oid += 1 session.add(voucher) session.add(generation) # journalize entry = JournalEntry( date=datetime.now().strftime('%Y-%m-%d'), userid=self.request.principal.id, action=u"Berechtigungsscheine manuell erstellt", #action=u"Add:%s" % self.context.model.__label__, oid=str(self.context.oid), note=journal_note) session.add(entry) # redirect self.flash(_(u"%s Berechtigungsscheine erstellt" % number)) self.redirect(self.application_url()) return SUCCESS else: self.flash(_(u"The demand must be for at least 1 voucher.")) self.redirect(self.url(self.context)) return FAILURE
def companies_choice(context): request = getRequest() session = get_session('school') accounts = session.query(Company).filter(Company.account_id == request.principal.id).all() return SimpleVocabulary([ SimpleTerm(value=c, token=c.id, title='%s (%s)' % (c.name, c.account_id)) for c in accounts])
def log_in(self, request, username, password, **kws): session = get_session('ukhvoucher') user = session.query(Account).filter(Account.login == username, Account.az == "eh") if user.count(): user = user.one() if user.password.strip() == password: return user return None
def criterias_choice(context): session = get_session('school') company = get_company_id(context) criterias = session.query(Criteria).filter( Criteria.company_id == company.id) return SimpleVocabulary([ SimpleTerm(value=c, token=c.id, title=c.title) for c in criterias])
def __call__(self, form): data, errors = form.extractData() if errors: form.flash(_(u'An error occurred.')) return FAILURE session = get_session('school') # HERE WE CREATE THE STUDENT. from datetime import date uuid = Student.generate_access() student = Student( anonymous=True, access=uuid, completion_date=date.today(), company_id=form.context.course.company_id, session_id=form.context.id, course=form.context.course, course_id=form.context.course.id, quizz_type=form.context.course.quizz_type) fields = form.fields extra_answers = {} keys = data.keys() for key in keys: if key.startswith('criteria_'): cid = key.split('_', 1)[1] value = data.pop(key) field = fields.get(key) criteria_answer = CriteriaAnswer( criteria_id=cid, student_id=student.access, session_id=student.session_id, answer=value, ) session.add(criteria_answer) elif key.startswith('extra_'): value = data.pop(key) field = fields.get(key) if isinstance(value, set): value = list(value) extra_answers[field.description] = value data['extra_questions'] = json.dumps(extra_answers) student.complete_quizz() quizz = form.quizz(**data) quizz.student_id = student.access quizz.company_id = student.company_id quizz.course_id = student.course_id quizz.session_id = student.session_id session.add(student) session.add(quizz) form.flash(_(u'Eingabe wurde gespeichert.')) form.redirect(form.request.url) return SUCCESS
def getInvoiceId(): from ukhvoucher.models import Invoice from sqlalchemy.sql.functions import max session = get_session('ukhvoucher') try: oid = int(session.query(max(Invoice.oid)).one()[0]) + 1 except: oid = 100000 return unicode(oid)
def update(self): session = get_session('school') quizz = getUtility(IQuizz, name=self.context.quizz_type) answers = list(session.query(quizz).filter( quizz.student_id == self.context.access)) if len(answers) == 1: self.answers = answers[0] self.fields = OrderedDict(getFieldsInOrder(quizz.__schema__)) self.extra = json.loads(self.answers.extra_questions)
def __call__(self, form): form.updateLines(mark_selected=True) session = get_session(form.context.db_key) for line in form.lines: if line.selected: obj = line.getContent() session.delete(obj) send(_(u"Deletion successful.")) url = get_absolute_url(form.context, form.request) return SuccessMarker('Deleted', True, url=url)
def render(self): session = get_session('school') query = session.query(CriteriaAnswer) for ca in query: sid = ca.student.session_id try: ca.session_id = int(sid) except: #print ca.student pass
def get_user(self, username): session = get_session('school') username = username account = session.query(Account).filter( func.lower(Account.email) == username.lower()) if account.count() == 1: return account.first() return None
def handle_save(self): data, errors = self.extractData() session = get_session('school') if errors: return FAILURE if not data['password'] == data['verif']: self.errors.append( Error(identifier='form.field.password', title='Password and verification mismatch')) self.errors.append( Error(identifier='form.field.verif', title='Password and verification mismatch')) self.flash(_(u'Password and verification mismatch.')) return FAILURE existing = session.query(Account).filter( func.lower(Account.email) == data['email'].lower()) if existing.count(): self.flash(_(u'User with given email already exists.')) self.errors.append( Error( identifier='form.field.email', title='Diese E-Mail Adresse existiert bereits im System.')) return FAILURE # pop the captcha and verif, it's not a needed data data.pop('verif') data.pop('captcha') # hashing the password salt = uuid.uuid4().hex password = data.pop('password').encode('utf-8') data['password'] = hashlib.sha512(password + salt).hexdigest() data['salt'] = salt account = Account(**data) code = account.activation = str(uuid.uuid1()) session.add(account) session.flush() session.refresh(account) base_url = self.application_url().replace('/register', '') # We send the email. send_activation_code( self.context.configuration.smtp_server, data['name'], data['email'], code, base_url) self.flash(_(u'Account added with success.')) self.redirect('%s/registered' % self.application_url()) return SUCCESS
def getGenerations(self): rc = [] session = get_session('ukhvoucher') generations = session.query(models.Generation).filter( models.Voucher.generation_id == models.Generation.oid, models.Voucher.user_id == int(self.request.principal.oid)).all() for generation in generations: for voucher in generation.voucher: if voucher.status.strip() == CREATED: if generation not in rc: rc.append(generation) return rc
def handle_save(self): data, errors = self.extractData() if errors: #self.flash(_(u'An error occurred.')) return FAILURE session = get_session('school') csdata = dict( startdate=data.pop('startdate'), enddate=data.pop('enddate'), about=data.pop('about'), strategy=data.pop('strategy'), p2p=data.pop('p2p') ) strategy = dict( nb_students=data.pop('nb_students'), strategy=csdata.get('strategy') ) #data['quizz_type'] = "quizz2" course = Course(**data) course.company_id = self.context.id session.add(course) session.flush() session.refresh(course) clssession = ClassSession(**csdata) clssession.course_id = course.id clssession.company_id = self.context.id session.add(clssession) session.flush() session.refresh(clssession) if strategy.get('strategy') in ('mixed','fixed'): if strategy['nb_students'] <= 7 or strategy['nb_students'] is NO_VALUE: self.flash(u'Auswertungen sind erst ab 7 Teilnehmer zulässig. Bitte erhöhen Sie die Anzahl der Teilnehmer auf mindestens 7') return FAILURE for student in clssession.generate_students(strategy['nb_students']): clssession.append(student) # update order for idx, criteria in enumerate(data.get('criterias', []), 1): query = criterias_table.update().where( criterias_table.c.courses_id == course.id ).where( criterias_table.c.criterias_id == criteria.id ).where( criterias_table.c.company_id == self.context.id ).values(order=idx) session.execute(query) self.flash(_(u'Course added with success.')) self.redirect(self.application_url()) return SUCCESS
def handle_save(self): session = get_session('school') record = HistoryEntry( action=u"Delete", type=u"Session", description=u"Delete Session %s" % self.context.id) session.add(record) session.delete(self.context) session.flush() self.flash(_(u'Deleted with success.')) self.redirect(self.application_url()) return SUCCESS
def vouchers(context): session = get_session('ukhvoucher') items = [] disabled = set() query = session.query(Voucher) if isinstance(context, Account): query = query.filter(Voucher.user_id==context.oid) for item in query.all(): items.append(SimpleTerm(item, token=item.oid, title="%s - %s %s" %(item.title, item.status.strip(), item.cat))) if item.invoice is not None or item.status.strip() in (DISABLED, BOOKED): disabled.add(str(item.oid)) disabled.add(item.oid) vocabulary = SimpleVocabulary(items) vocabulary.disabled_items = disabled return vocabulary
def handle_save(self): data, errors = self.extractData() if errors: self.flash(_(u'An error occurred.')) return FAILURE session = get_session('school') criteria = Criteria(**data) criteria.company_id = self.context.__parent__.id session.add(criteria) session.flush() session.refresh(criteria) self.flash(_(u'Criteria added with success.')) self.redirect(self.application_url()) return SUCCESS
def __getitem__(self, key): session = get_session('ukhvoucher') try: ret = session.query(Account).filter(Account.oid == key).all() if ret > 0: model = ret[0] proxy = ILocation(model, None) if proxy is None: proxy = LocationProxy(model) locate(proxy, self, str(model.oid)) return proxy else: raise KeyError except: raise KeyError
def update(self): js.need() data, errors = self.extractData() kats = get_kategorie(None) session = get_session('ukhvoucher') rc = [] query = session.query( models.Voucher.cat, func.count(models.Voucher.oid)).group_by(models.Voucher.cat).filter(models.Voucher.status == BOOKED).order_by(models.Voucher.cat) if data['von'] and data['von'] is not NO_VALUE: query = query.filter(models.Voucher.creation_date > data['von']) if data['bis'] and data['bis'] is not NO_VALUE: query = query.filter(models.Voucher.creation_date < data['bis']) for cat, count in query.all(): rc.append((kats.getTerm(cat.strip()).title, str(count))) self.statdata = rc
def getAddress(self): session = get_session('ukhvoucher') oid = str(self.context.user.oid) address = session.query(models.Address).get(oid) if address: return address @ram.cache(_render_details_cachekey) def getSlowAdr(oid): print "ADR FROM CAHCE" address = session.query(models.AddressTraeger).get(oid) if address: return address address = session.query(models.AddressEinrichtung).get(oid) if address: return address return getSlowAdr(oid)
def getIKNummer(self, mnr): from cromlech.sqlalchemy import get_session session = get_session('ukhvoucher') print mnr sql = """SELECT ENRRCD FROM EDUCUSADAT.MIENR1AA WHERE ENREA1 = '3' AND ENREA2 in ('2', '3') AND ENRLFD = %s""" % mnr res = session.execute(sql).fetchone() print res if res: accounts = session.query(Account).filter(Account.oid == int(res[0])).all() print accounts if accounts: return accounts[0].login return "NOTHING FOuND"
def sessions(context, threshold=7): if ICourse.providedBy(context): session = get_session('school') sessions = session.query(ClassSession).\ filter(Course.id == context.id).\ filter(ClassSession.course_id == context.id).\ join(Student, and_( Student.course_id==context.id, Student.completion_date != None )).\ group_by(ClassSession.id).\ having(func.count(Student.access) >= threshold) return SimpleVocabulary([ SimpleTerm(value=s, token=s.id, title=s.title) for s in sessions ]) raise NotImplementedError
def site_manager(self, request): username = request.principal.id.lower() if username != unauthenticated_principal.id: session = get_session(self.configuration.name) account = session.query(Account).filter( func.lower(Account.email) == username) if account.count() == 1: account = account.first() else: account = None if account is not None: account.configuration = self.configuration account.getSiteManager = getGlobalSiteManager alsoProvides(account, IPublicationRoot) return Site(account) return Site(NoAccess(request, self.configuration))
def shell(): session = get_session(self.configuration.name) namespace['sql_session'] = session return super(SQLPublication, self).__interact__(banner=u'', **namespace)