def __call__(self):
session = DBSession()
transaction = Transaction()
params = self.request.params
year = int(params['year'])
month = int(params['month'])
if params['ttype_id'] == '--':
return self.redir_to_list(year, month, msg='Please select a type.')
ttype = session.query(TransactionType).get(params['ttype_id'])
transaction.ttype = ttype
transaction.amount = float(params['amount'])
if ttype.mem_sup == 'memb' and 'mem_id' in params:
transaction.member = session.query(Member).get(params['mem_id'])
if ttype.mem_sup == 'whol' and 'wh_id' in params:
transaction.wholesaler = session.query(Wholesaler).get(
params['wh_id'])
if ttype.mem_sup == 'vers' and 'vers_id' in params:
transaction.vers_supplier = session.query(VersSupplier).get(
params['vers_id'])
transaction.comment = params['comment']
if 'ord_no' in params and not params['ord_no'] in ('', '--'):
transaction.ord_no = int(params['ord_no'])
if 'late' in params:
transaction.late = bool(params['late'])
day = int(params['day'])
adate = datetime.date(year, month, day)
transaction.date = adate
transaction.validate()
session.add(transaction)
session.flush()
return self.redir_to_list(year, month, msg='Transaction "{}" has been '\
'added to the list.'.format(str(transaction)))
def get_member(login):
'''
Get a member object
:param string login: the login can either be the member ID or the email-address
:returns: The Member object or None if none was found for login information
'''
mem = None
session = DBSession()
if "@" in str(login): # email address given as login?
mem = session.query(Member).filter(Member.mem_email == login).first()
else: # then assume id
mem = session.query(Member).filter(Member.mem_id == login).first()
return mem
def number_of_current_orderers():
if running_sqlite():
return -1
query = """SELECT max(ord_no) from wh_line;"""
cur_ord_no = list(DBSession().connection().engine.execute(query))[0][0]
cur_order = DBSession.query(Order).filter(Order.id == cur_ord_no).first()
members = DBSession.query(Member).all()
orderers = 0
for m in members:
mo = MemberOrder(m, cur_order)
if mo.amount > 0:
orderers += 1
return orderers
def __call__(self):
session = DBSession()
self.wg = session.query(Workgroup).get(
int(self.request.matchdict['wg_id']))
self.year = int(self.request.matchdict['year'])
self.all_shift_data = {}
self.month_sums = {}
self.member_sums = {}
self.members_with_shifts = set()
for m in self.wg.members:
self.member_sums[m.mem_id] = [0, 0]
self.sum_overall = [0, 0]
self.sum_open_overall = 0
self.months = [
'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep',
'Oct', 'Nov', 'Dec'
]
q = session.query(Shift).filter(Shift.wg_id == self.wg.id)\
.filter(Shift.year == self.year)
for month in self.months:
self.all_shift_data[month] = {}
self.month_sums[month] = [0, 0]
qm = q.filter(Shift.month == self.months.index(month) + 1)
for m in self.wg.members:
qmm = qm.filter(Shift.mem_id == m.mem_id)
# worked shifts shifts of this member this month
wmm = qmm.filter(Shift.state == 'worked').count()
# all assigned (assigned, worked or not-worked)
amm = wmm + qmm.filter(Shift.state == 'assigned').count()
amm += qmm.filter(Shift.state == 'no-show').count()
if amm > 0:
self.members_with_shifts.add(m)
self.all_shift_data[month][m.mem_id] = wmm, amm
self.month_sums[month][0] += wmm
self.month_sums[month][1] += amm
self.member_sums[m.mem_id][0] += wmm
self.member_sums[m.mem_id][1] += amm
self.sum_overall[0] += wmm
self.sum_overall[1] += amm
# now open (not assigned) shifts
om = qm.filter(Shift.state == 'open').count()
self.all_shift_data[month][-1] = om
self.sum_open_overall += om
return dict(msg='')
def __call__(self):
'''
Default: Show form to identify member
If data entered and member identified, send reset request email
'''
self.login_necessary = False
session = DBSession()
p = self.request.params
member = None
if 'email' in p and p['email'] != "":
member = session.query(Member)\
.filter(Member.mem_email == p['email']).first()
if 'mem_id' in p and p['mem_id'] != "":
try:
member = get_member(session, self.request)
except Exception:
member = None
if member:
send_pwdreset_request(member, self.request.application_url)
return dict(msg=u'A reset link has been sent to the email'\
' address {!s}.'.format(member.mem_email),
m=member, form=None, key=None)
if (('email' in p and p['email'] != "")
or ('mem_id' in p and p['mem_id'] != "")):
return dict(msg=u'Cannot find any member with this information.',
m=member,
form='request',
key=None)
return dict(msg=u'', m=member, form='request', key=None)
def __call__(self):
db_session = DBSession()
wg = get_wg(db_session, self.request)
self.user_is_wgmember = self.user in wg.members
self.user_is_wgleader = self.user in wg.leaders
# we view shifts per-month here
# use parameters to determine month, default is current month
self.month = int(self.request.matchdict['month'])
self.year = int(self.request.matchdict['year'])
schedule_date = datetime.date(self.year, self.month, 1)
self.month_info = month_info(schedule_date)
q = """SELECT descr FROM shift_days_descriptions ORDER BY id;"""
day_literals = [i[0] for i in list(db_session.execute(q))]
self.days = day_literals + list(
range(1, self.month_info.days_in_month + 1))
shifts = db_session.query(Shift).filter(Shift.wg_id == wg.id)\
.filter(Shift.month == self.month)\
.filter(Shift.year == self.year)\
.order_by(Shift.day, Shift.task, Shift.mem_id)\
.all()
# show msg
if 'msg' in self.request.params:
msg = self.request.params['msg']
else:
msg = ''
return dict(shifts=shifts, wg=wg, msg=msg)
def __call__(self):
dbsession = DBSession()
wg_query = dbsession.query(Workgroup)
# -- inactive workgroups? --
show_inactive = True
if not 'include_inactive' in self.request.params\
or not self.request.params['include_inactive']:
show_inactive = False
wg_query = wg_query.filter(Workgroup.active == True)
# show msg
if 'msg' in self.request.params:
msg = self.request.params['msg']
else:
msg = ''
# -- ordering --
# direction
odir = asc
if 'order_dir' in self.request.params\
and self.request.params['order_dir'] == 'desc':
odir = desc
# ordering choice
order_name_choice = 'asc'
if odir == asc:
order_name_choice = 'desc'
wg_query = wg_query.order_by(odir('id'))
workgroups = wg_query.all()
self.wg_count = len(workgroups)
return dict(workgroups=workgroups,
msg=msg,
order_name_choice=order_name_choice,
show_inactive=show_inactive)
def orders_money_and_people():
'''
Count money and number of people who ordered for each order cycle.
A more straightforward way to do this might be transactions, but we have
lots of legacy data without them at Vokomokum and this also works okay.
'''
# 1. preparing the data
session = DBSession()
orders = session.query(Order).order_by(Order.completed).all()
members = session.query(Member).all()
money_list = []
people_list = []
max_money = 0
max_people = 0
for o in orders:
money = 0
people = 0
for m in members:
mo = MemberOrder(m, o)
if mo.amount > 0:
money += mo.amount
people += 1
money_list.append(round(money, 1))
max_money = max(money, max_money)
max_people = max(people, max_people)
people_list.append(people)
# 2. writing graph configuration
graph = dict(title=dict(text='Order history'), credits=dict(enabled=False))
graph['series'] = []
graph['series'].append(dict(name='money', data=money_list))
graph['series'].append(dict(name='people', data=people_list, yAxis=1))
graph['xAxis'] = dict(categories=[o.label for o in orders],
tickPositions=[0, len(orders)/2, len(orders)-1])
graph['yAxis'] = [dict(title=dict(text='money'), min=0, max=max_money*1.1),
dict(title=dict(text='people'), min=0, max=max_people*1.1,
opposite=True)]
# 3. write to file
settings = get_settings()
gf = settings['vokomokum.graph_folder']
json.dump(graph, open('{}/orders_money_and_people.json'.format(gf), 'w'))
def validate(self):
''' checks on address, bank account, ...
'''
# check missing fields
missing = []
for f in ('mem_fname', 'mem_lname', 'mem_email'):
if not f in self.__dict__ or self.__dict__[f] == '':
missing.append(f)
if len(missing) > 0:
raise VokoValidationError('We still require you to fill in: %s'\
% ', '.join([m[4:] for m in missing]))
self.validate_email()
# also check unique constraint on email address here for nicer error msg
session = DBSession()
members = session.query(Member)\
.filter(Member.mem_email == self.mem_email).all()
if len(members) > 0:
if not (len(members) == 1 and members[0].mem_id == self.mem_id):
raise VokoValidationError('The email address already exists '\
'for a member in the database.')
# we want one telephone number, as well
sd = self.__dict__
if ((not 'mem_home_tel' in sd and not 'mem_work_tel' in sd
and not 'mem_mobile' in sd)
or (self.mem_home_tel == "" and self.mem_work_tel == ""
and self.mem_mobile == "")):
raise VokoValidationError('Please specify at least one telephone '\
'number.')
# check postcode
if self.mem_postcode and len(self.mem_postcode) > 0\
and not (self.mem_postcode[:4].isdigit()\
and self.mem_postcode[-2:].isalpha()):
raise VokoValidationError('The postcode does not seem to be'\
' valid (should be NNNNLL, where N=number and L=letter).')
# check bank no
if self.mem_bank_no:
bank_no_clean = self.mem_bank_no.replace(' ', '').replace('-', '')
if not len(bank_no_clean) in [0, 7, 8, 9]:
# length of 8 is legacy data
raise VokoValidationError('Bank number needs to consist of 7 '\
'(postbank) or 9 numbers.')
if len(bank_no_clean) > 0 and not bank_no_clean.isdigit():
raise VokoValidationError('Bank number needs to consist of '\
'only numbers.')
# household size
if self.mem_household_size is None or self.mem_household_size < 1:
raise VokoValidationError('Please specify how many people live '\
'in the household.')
def __call__(self):
'''
Send members an email about their negative balance.
We send these mails after orders, that is why this action is tied
to an order number (it also helps us to keep track a little over
when we already sent these reminders).
'''
o_id = self.request.matchdict['o_id']
session = DBSession()
order = session.query(Order).\
filter(Order.id == o_id).first()
if not order:
return dict(msg='An order with ID {} does not exist.'.format(o_id),
order=None)
members = [m for m in session.query(Member).all() if m.balance < 0]
now = datetime.datetime.now()
deadline = now + datetime.timedelta(days = 3)
weekdays_en= ['monday', 'tuesday', 'wednesday', 'thursday',
'friday', 'saturday', 'sunday']
weekdays_nl = ['maandag', 'dinsdag', 'woensdag', 'donderdag',
'vrijdag', 'zaterdag', 'zondag']
deadline_en = '{} {}.{}.{}'.format(weekdays_en[deadline.weekday()],
deadline.day, deadline.month, deadline.year)
deadline_nl = '{} {}.{}.{}'.format(weekdays_nl[deadline.weekday()],
deadline.day, deadline.month, deadline.year)
for m in members:
subject = 'Payment request / Verzoek tot betaling'
mail_templ = open('members/templates/order_charge_txt.eml', 'r')
mail = mail_templ.read()
body = mail.format(mem_id=m.mem_id, label=order.label, amount=round(m.balance, 2),
deadline_nl=deadline_nl, deadline_en=deadline_en)
sendmail(m.mem_email, subject, body,
folder='order-charges/{}'.format(order.id),
sender='*****@*****.**')
return dict(msg=u'Emails with payment reminders have been sent out '\
'after order {}'.format(order.label))
def __call__(self):
dbsession = DBSession()
m_query = dbsession.query(Member)
# show msg
if 'msg' in self.request.params:
msg = self.request.params['msg']
else:
msg = ''
# -- inactive members? --
show_inactive = True
if not 'include_inactive' in self.request.params\
or not self.request.params['include_inactive']:
show_inactive = False
m_query = m_query.filter(Member.mem_active == True)
# -- ordering --
# direction
odir = asc
if 'order_dir' in self.request.params\
and self.request.params['order_dir'] == 'desc':
odir = desc
# order by
# key is what the outside world sees, value is what SQLAlchemy uses
order_idxs = {'id': Member.mem_id, 'name': Member.mem_lname}
order_by = 'id'
if 'order_by' in self.request.params\
and self.request.params['order_by'] in order_idxs:
order_by = self.request.params['order_by']
# ordering choices
order_id_choice = 'asc'
if order_by == 'id' and odir == asc:
order_id_choice = 'desc'
order_name_choice = 'asc'
if order_by == 'name' and odir == asc:
order_name_choice = 'desc'
m_query = m_query.order_by(odir(order_idxs[order_by]))
members = m_query.all()
self.mem_count = len(members)
return dict(members=members,
msg=msg,
order_by=order_by,
order_id_choice=order_id_choice,
order_name_choice=order_name_choice,
show_inactive=show_inactive)
def __call__(self):
session = DBSession()
self.m = get_member(session, self.request)
msg = ''
if 'msg' in self.request.params:
msg = self.request.params['msg']
nov12 = datetime.datetime(2012, 11, 1)
orders = [MemberOrder(self.m, o) for o in session.query(Order)\
.order_by(desc(Order.completed)).all()]
old_orders = [o for o in orders if o.amount > 0\
and str(o.order.completed).strip() != ''\
and str(o.order.completed) < str(nov12)]
return dict(m=self.m,
msg=msg,
shifts=self.m.shifts,
old_orders=old_orders,
transactions=self.m.transactions)
def __call__(self):
name = self.request.params['name']
pos_neg = self.request.params['pos_neg']
mem_sup = self.request.params['mem_sup']
tt = TransactionType(None, name=name, pos_neg=pos_neg, mem_sup=mem_sup)
tt.validate()
session = DBSession()
existing = session.query(TransactionType)\
.filter(TransactionType.name == tt.name).all()
if len(existing) > 0:
return self.redirect('/transaction-types?msg=Transaction type '\
' with name "{}" already exists'\
.format(tt.name))
session.add(tt)
session.flush()
return self.redirect('/transaction-types?msg=Transaction type "{}"'\
' has been added to the list.'.format(tt.name))
def groupfinder(memid, request):
session = DBSession()
groups = ['group:members']
context = request.context
if context.__class__ == Member:
mem_id = request.matchdict.get('mem_id', -1)
if not mem_id == 'new':
if int(mem_id) == memid:
groups.append('group:this-member')
if context.__class__ == Workgroup:
if 'wg_id' in request.matchdict:
wg_id = request.matchdict['wg_id']
if not wg_id == 'new':
wg = session.query(Workgroup).get(wg_id)
if memid in [m.mem_id for m in wg.leaders]:
groups.append('group:wg-leaders')
if memid in [m.mem_id for m in wg.members]:
groups.append('group:wg-members')
admins = session.query(Member).filter(Member.mem_admin == True).all()
if memid in [m.mem_id for m in admins]:
groups.append('group:admins')
# membership people:
membership = session.query(Workgroup).\
filter(Workgroup.name == 'Membership').first()
if membership:
if memid in [m.mem_id for m in membership.members]:
groups.append('group:membership')
# finance people:
finance = session.query(Workgroup).\
filter(Workgroup.name == 'Finance').first()
if finance:
if memid in [m.mem_id for m in finance.members]:
groups.append('group:finance')
# bestelling people:
bestelling = session.query(Workgroup).\
filter(Workgroup.name == 'Bestelling').first()
if bestelling:
if memid in [m.mem_id for m in bestelling.members]:
groups.append('group:bestelling')
# vers bestel people:
vers_bestel = session.query(Workgroup).\
filter(Workgroup.name == 'Vers bestel').first()
if vers_bestel:
if memid in [m.mem_id for m in vers_bestel.members]:
groups.append('group:vers-bestel')
#print "+++++++++++++++++++++++++++++++++++++++ User is in groups:", groups
return groups
def __call__(self):
session = DBSession()
if 'msg' in self.request.params:
msg = self.request.params['msg']
else:
msg = ''
if 'confirm-deletion-of' in self.request.params:
self.cdo = \
get_transaction(session,
self.request.params['confirm-deletion-of'])
self.month = int(self.request.matchdict['month'])
self.year = int(self.request.matchdict['year'])
schedule_date = datetime.date(self.year, self.month, 1)
self.month_info = month_info(schedule_date)
self.days = range(1, self.month_info.days_in_month + 1)
now = datetime.datetime.now()
if self.month == now.month:
self.today = now.day
else:
self.today = 1
self.show_items = False # we only show summary when list is not asked for
transactions = []
query = session.query(Transaction)
# fitering by type
self.ttid = None
if 'ttype' in self.request.params:
self.show_items = True
if self.request.params['ttype'] != '':
self.ttid = int(self.request.params['ttype'])
query = query.filter(Transaction.ttype_id == self.ttid)
# ordering
order_by = Transaction.date
self.order_criterion = 'date'
if 'order_by' in self.request.params:
if self.request.params['order_by'] == 'date':
order_by = Transaction.date
if self.request.params['order_by'] == 'amount':
order_by = Transaction.amount
if self.request.params['order_by'] == 'type':
order_by = Transaction.ttype_id
self.order_criterion = self.request.params['order_by']
query = query.order_by(order_by)
self.order_criteria = ('date', 'amount', 'type')
if self.show_items:
tz = pytz.timezone('Europe/Amsterdam')
first = tz.localize(
datetime.datetime(self.year, self.month, 1, 0, 0, 0))
last = tz.localize(
datetime.datetime(self.year, self.month,
self.month_info.days_in_month, 23, 59, 59))
# .filter(Transaction.date >= first and Transaction.date <= last)\
# .order_by(Transaction.id)\
# .all()
# This is here because the filter above doesn't work for me
# I only get pure string comparison to work, which works for now
transactions = [t for t in query.all()\
if (str(t.date.tzinfo and t.date or tz.localize(t.date)) >= str(first))\
and (str(t.date.tzinfo and t.date or tz.localize(t.date)) <= str(last))]
self.sums = {}
for ttype in self.transaction_types:
self.sums[ttype.name] =\
get_transaction_sums(self.year, self.month, ttype)
self.overall = get_transaction_sums(self.year, self.month, None)
return dict(msg=msg, transactions=transactions)
def orders(self):
''' return a dict, with order IDs as keys and labels as values'''
session = DBSession()
return session.query(Order)\
.order_by(desc(Order.completed)).all()
def transaction_types(self):
session = DBSession()
return session.query(TransactionType).all()
def __call__(self):
session = DBSession()
transaction = get_transaction(session, self.request.matchdict['t_id'])
msg = ''
if not transaction:
raise Exception("No transaction with id %d"\
% self.request.matchdict['t_id'])
action = self.request.matchdict['action']
if action == "":
raise Exception('No action given.')
if action == "setttype":
transaction.ttype = session.query(TransactionType)\
.get(self.request.params['ttype_id'])
msg = 'Transaction Type was updated.'
if action == "setmember":
if not 'mem_id' in self.request.params:
msg = 'No member was selected.'
else:
transaction.member = get_member(session, self.request)
msg = u'Transaction got a new member:{}'\
.format(transaction.member)
if action == "setwholesaler":
if not 'wh_id' in self.request.params:
msg = 'No wholesaler was selected.'
else:
transaction.whol_id = self.request.params['wh_id']
msg = u'Transaction got a new wholesaler.'
if action == "setverssupplier":
if not 'vers_id' in self.request.params:
msg = 'No vers supplier was selected.'
else:
transaction.vers_id = self.request.params['vers_id']
msg = u'Transaction got a new vers supplier.'
if action == "setamount":
amount = self.request.params['amount']
try:
amount = float(amount)
msg = 'Amount was updated.'
except:
msg = 'Invalid amount: {}'.format(amount)
transaction.amount = amount
if action == "setcomment":
transaction.comment = self.request.params['comment']
msg = "Comment was updated."
if action == "setdate":
adate = datetime.datetime.now()
adate.day = self.request.params['day']
adate.month = self.request.params['month']
adate.year = self.request.params['year']
transaction.date = adate
msg = "Date was updated."
if action == "setlate":
if 'late' in self.request.params:
transaction.late = bool(self.request.params['late'])
else:
transaction.late = False
msg = "Late-status of transaction was set to {}."\
.format(transaction.late)
if action == 'setorder':
transaction.ord_no = int(self.request.params['ord_no'])
msg = "order was set."
transaction.validate()
session.flush()
return self.redir_to_list(transaction.date.year, transaction.date.month,
msg='Transaction has been saved. {}'\
.format(msg), show_list=True,
tid=transaction.id)
def __call__(self):
'''
Create order charges for a given order in the system
'''
o_id = int(self.request.matchdict['o_id'])
session = DBSession()
order = session.query(Order).\
filter(Order.id == o_id).first()
if not order:
return dict(msg='An order with ID {} does not exist.'.format(o_id),
order=None, action=None)
charge_ttype_id = get_ttypeid_by_name('Order Charge')
if not 'Finance' in [wg.name for wg in self.user.workgroups]\
and not self.user.mem_admin:
return dict(order=order, action=None,
msg='Only Finance people can do this.')
# all (positive) charges for members who have not been charged for
# this order yet
charges = [MemberOrder(m, order) for m in session.query(Member).all()]
charges = [c for c in charges if c.amount > 0 and not o_id in\
[t.order.id for t in c.member.transactions\
if t.ttype_id == charge_ttype_id]]
first_orderers = []
if not 'action' in self.request.params:
# show charges that would be made
return dict(order=order, action='show', charges=charges)
else:
# temporary backdoor to test this
if self.request.params['action'] == 'gggraph':
orders_money_and_people()
if self.request.params['action'] == 'charge':
# confirmation: make charges
for c in charges:
t = Transaction(amount = -1 * c.amount,
comment = 'Automatically charged.'
)
ttype = session.query(TransactionType)\
.get(charge_ttype_id)
t.ttype = ttype
t.member = c.member
t.order = c.order
t.validate()
session.add(t)
# first order of this member? Charge Membership fee
if c.is_first_order():
first_orderers.append(c.member)
mf = Transaction(\
amount = membership_fee(c.member) * -1,
comment = 'Automatically charged (for {}'\
' people in the household) on first-time'\
' order ({})'\
.format(c.member.mem_household_size,
c.order.label)
)
ttype = session.query(TransactionType)\
.get(get_ttypeid_by_name('Membership Fee'))
mf.ttype = ttype
mf.member = c.member
mf.validate()
session.add(mf)
# use this opportunity to update graph data
orders_money_and_people()
# inform membership about people who ordered for first time
subject = 'First-time orderers'
body = 'FYI: Below is a list of people who ordered for the'\
' first time today. This mail was auto-generated.\n\n'
body += '\n'.join(['{} [ID:{}]'.format(ascii_save(m.fullname), m.mem_id)\
for m in first_orderers])
sendmail('*****@*****.**', subject, body,
folder='order-charges/{}'.format(o_id),
sender='*****@*****.**')
return dict(order=order, action='done')
return dict(order=order, action='')
def members(self):
session = DBSession()
return session.query(Member).order_by(asc(Member.mem_id)).all()
def vers_suppliers(self):
session = DBSession()
return session.query(VersSupplier).all()
def wholesalers(self):
session = DBSession()
return session.query(Wholesaler).all()
def get_ttypeid_by_name(name):
session = DBSession()
tt = session.query(TransactionType)\
.filter(TransactionType.name == name).first()
return tt.id
