Hey guys,
I'm trying to create a SQLFORM.grid that will display the total number of
entries for each 'user' in a certain time period.
Basically, my model states that a user can only be in bursary_entries table
once per day (This equates to a user swiping their ID card). I would like
to be able to find out how many entries are recorded for a specific user
per week, month, year, etc. and also filter it by the 'location' column too
if possible.
from datetime import datetime
db.define_table('bursary_entries',
Field('barcode', type='string', requires=[IS_IN_DB_UPPER(db,
'bursary_users.barcode')]),
Field('entry_time', type='datetime', default=request.now,
readable=False, writable=False),
Field('entry_location', type='string', readable=False,
writable=False, requires=[IS_IN_DB(db, 'bursary_locations.bursary_location'
)]),
Field('bursary_at_time', type='string', readable=False,
writable=False, requires=[IS_IN_DB(db, 'bursary_users.bursary')]),
format='%(barcode)s'
)
I've tried simply:
query = (db.bursary_entries.entry_time > reportsform.vars.start_date)& \
(db.bursary_entries.entry_time < reportsform.vars.end_date)
# Left outer-joins
joins = [db.bursary_users.on(db.bursary_entries.barcode==db.
bursary_users.barcode)]
grid = SQLFORM.grid(query, left=joins, groupby=db.bursary_entries.
barcode)
But obviously that groups the values but doesn't have any kind of
count/sum. I've tried a whole host of other things, but they've all fallen
flat - Can anyone shed any light on the possibility of this?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
