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