def list_volunteers(message='', status='active', title='Volunteers'):
title = '%s Volunteers' % (status.title())
with conn:
c = conn.cursor()
c.execute("SELECT id, name, date(orientation) as orientation, status FROM volunteers WHERE status = ? ORDER BY name", (status,))
result = c.fetchall()
return template('volunteer_list', title=title, admin=request.get_cookie("admin"), message=message, rows=result, status=status)
def list_hours(message='',
title='Hours Report',
start=datetime.date.today(),
end=(datetime.date.today() + datetime.timedelta(days=1)),
group_by=''):
with conn:
c = conn.cursor()
if group_by == '':
c.execute(
"SELECT hours.id, volunteers.name AS volunteer, activities.name AS activity, strftime('%m-%d %H:%M',start), strftime('%m-%d %H:%M',end), (strftime('%s',end)-strftime('%s',start))/3600.0 AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id LEFT OUTER JOIN activities ON hours.activity_id=activities.id WHERE start >= ? AND end <= ? ORDER BY start",
(start, end))
elif group_by == 'volunteer':
c.execute(
"SELECT volunteers.name AS volunteer, SUM(strftime('%s',end)-strftime('%s',start))/3600.0 AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id WHERE start >= ? AND end <= ? GROUP BY volunteer_id ORDER BY volunteer",
(start, end))
elif group_by == 'activity':
c.execute(
"SELECT activities.name AS activity, SUM(strftime('%s',end)-strftime('%s',start))/3600.0 AS totalHours FROM hours JOIN activities ON hours.activity_id=activities.id WHERE start >= ? AND end <= ? GROUP BY activity_id ORDER BY activity",
(start, end))
hours = c.fetchall()
return template('hour_list',
title=title,
admin=request.get_cookie("admin"),
message=message,
rows=hours,
start=start,
end=end,
group_by=group_by)
def new_volunteer_form(message='', title='New Volunteer'):
today = datetime.date.today()
return template('volunteer_form',
title=title,
admin=request.get_cookie("admin"),
message=message,
today=today)
def db_backup():
filename = "db/backups/backup-{}.sql".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S"))
with open(filename, 'w') as f:
with conn:
for line in conn.iterdump():
f.write("{}\n".format(line))
return template('message_only', title='Database Backup', admin=request.get_cookie("admin"), message='Database Backed up to {}'.format(filename))
def check_out_form(id, message='', title='Check Out'):
now = datetime.datetime.now()
with conn:
c = conn.cursor()
c.execute(
"SELECT id, volunteer_id, activity_id, start, end FROM hours WHERE id LIKE ?",
(id, ))
result = c.fetchone()
c.execute("SELECT id, name FROM volunteers WHERE id = ?",
(result['volunteer_id'], ))
volunteer = c.fetchone()
c.execute(
"SELECT id, name FROM activities WHERE status = 'active' ORDER BY name"
)
activities = c.fetchall()
start = datetime.datetime.strptime(result['start'], "%Y-%m-%d %H:%M:%S")
return template('check_out',
title=title,
admin=request.get_cookie("admin"),
message=message,
now=now,
volunteer=volunteer,
activities=activities,
id=id,
values=result,
start=start)
def edit_volunteer_form(id, message='', title='Edit Volunteer'):
with conn:
c = conn.cursor()
c.execute("SELECT id, name, date(orientation) AS orientation, status FROM volunteers WHERE id LIKE ?", (id,))
result = c.fetchone()
return template('volunteer_form', title=title, admin=request.get_cookie("admin"), message=message, id=id,
values=result)
def edit_activity_form(id, message='', title='Edit Activity'):
with conn:
c = conn.cursor()
c.execute("SELECT id, name, status FROM activities WHERE id LIKE ?", (id,))
result = c.fetchone()
return template('activity_form', title=title, admin=request.get_cookie("admin"), message=message, id=id,
values=result)
def list_activities(message='', status='active', title='Activities'):
title = '%s Activities' % (status.title())
with conn:
c = conn.cursor()
c.execute("SELECT id, name, status FROM activities WHERE status = ? ORDER BY name", (status,))
result = c.fetchall()
return template('activity_list', title=title, admin=request.get_cookie("admin"), message=message, rows=result, status=status)
def check_in_form(message='', title='Checked-in Volunteers'):
with conn:
c = conn.cursor()
c.execute("SELECT hours.id, volunteer_id, volunteers.name AS volunteer, strftime('%m-%d %H:%M',start), end FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id WHERE end is null ORDER BY volunteer")
hours = c.fetchall()
c.execute("SELECT id, name FROM volunteers WHERE status = 'active' ORDER BY name")
volunteers = c.fetchall()
return template('check_in', title=title, admin=request.get_cookie("admin"), message=message, rows=hours, volunteers=volunteers)
def delete_hour_confirm(id, title='Delete Record?', message=''):
with conn:
c = conn.cursor()
c.execute(
"SELECT hours.id, volunteers.name AS volunteer, activities.name AS activity, strftime('%m/%d/%Y %H:%M',start), strftime('%m/%d/%Y %H:%M',end) FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id LEFT OUTER JOIN activities ON hours.activity_id=activities.id WHERE hours.id LIKE ?",
(id,))
result = c.fetchone()
return template('hour_delete', title=title, admin=request.get_cookie("admin"), message=message, id=id,
values=result, delete=True)
def db_backup():
filename = "db/backups/backup-{}.sql".format(
datetime.datetime.now().strftime("%Y%m%d%H%M%S"))
with open(filename, 'w') as f:
with conn:
for line in conn.iterdump():
f.write("{}\n".format(line))
return template('message_only',
title='Database Backup',
admin=request.get_cookie("admin"),
message='Database Backed up to {}'.format(filename))
def list_hours(message='', title='Hours Report', start=datetime.date.today(), end=(datetime.date.today()+datetime.timedelta(days=1)), group_by=''):
with conn:
c = conn.cursor()
if group_by == '':
c.execute("SELECT hours.id, volunteers.name AS volunteer, activities.name AS activity, strftime('%m-%d %H:%M',start), strftime('%m-%d %H:%M',end), (strftime('%s',end)-strftime('%s',start))/3600.0 AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id LEFT OUTER JOIN activities ON hours.activity_id=activities.id WHERE start >= ? AND end <= ? ORDER BY start", (start, end))
elif group_by == 'volunteer':
c.execute("SELECT volunteers.name AS volunteer, SUM(strftime('%s',end)-strftime('%s',start))/3600.0 AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id WHERE start >= ? AND end <= ? GROUP BY volunteer_id ORDER BY volunteer", (start, end))
elif group_by == 'activity':
c.execute("SELECT activities.name AS activity, SUM(strftime('%s',end)-strftime('%s',start))/3600.0 AS totalHours FROM hours JOIN activities ON hours.activity_id=activities.id WHERE start >= ? AND end <= ? GROUP BY activity_id ORDER BY activity", (start, end))
hours = c.fetchall()
return template('hour_list', title=title, admin=request.get_cookie("admin"), message=message, rows=hours, start=start, end=end, group_by=group_by)
def edit_activity_form(id, message='', title='Edit Activity'):
with conn:
c = conn.cursor()
c.execute("SELECT id, name, status FROM activities WHERE id LIKE ?",
(id, ))
result = c.fetchone()
return template('activity_form',
title=title,
admin=request.get_cookie("admin"),
message=message,
id=id,
values=result)
def check_out_form(id, message='', title='Check Out'):
now = datetime.datetime.now()
with conn:
c = conn.cursor()
c.execute("SELECT id, volunteer_id, activity_id, start, end FROM hours WHERE id LIKE ?", (id,))
result = c.fetchone()
c.execute("SELECT id, name FROM volunteers WHERE id = ?", (result['volunteer_id'],) )
volunteer = c.fetchone()
c.execute("SELECT id, name FROM activities WHERE status = 'active' ORDER BY name")
activities = c.fetchall()
start = datetime.datetime.strptime(result['start'], "%Y-%m-%d %H:%M:%S")
return template('check_out', title=title, admin=request.get_cookie("admin"), message=message, now=now,
volunteer=volunteer, activities=activities, id=id, values=result, start=start)
def edit_volunteer_form(id, message='', title='Edit Volunteer'):
with conn:
c = conn.cursor()
c.execute(
"SELECT id, name, date(orientation) as orientation, status FROM volunteers WHERE id LIKE ?",
(id, ))
result = c.fetchone()
return template('volunteer_form',
title=title,
admin=request.get_cookie("admin"),
message=message,
id=id,
values=result)
def delete_hour_confirm(id, title='Delete Record?'):
with conn:
c = conn.cursor()
c.execute(
"SELECT hours.id, volunteers.name AS volunteer, activities.name AS activity, strftime('%m-%d %H:%M',start), strftime('%m-%d %H:%M',end) FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id LEFT OUTER JOIN activities ON hours.activity_id=activities.id WHERE hours.id LIKE ?",
(id, ))
result = c.fetchone()
return template('hour_delete',
title=title,
admin=request.get_cookie("admin"),
message=message,
id=id,
values=result,
delete=True)
def list_activities(message='', status='active', title='Activities'):
title = '%s Activities' % (status.title())
with conn:
c = conn.cursor()
c.execute(
"SELECT id, name, status FROM activities WHERE status = ? ORDER BY name",
(status, ))
result = c.fetchall()
return template('activity_list',
title=title,
admin=request.get_cookie("admin"),
message=message,
rows=result,
status=status)
def list_volunteers(message='', status='active', title='Volunteers'):
title = '%s Volunteers' % (status.title())
with conn:
c = conn.cursor()
c.execute(
"SELECT id, name, date(orientation) as orientation, status FROM volunteers WHERE status = ? ORDER BY name",
(status, ))
result = c.fetchall()
return template('volunteer_list',
title=title,
admin=request.get_cookie("admin"),
message=message,
rows=result,
status=status)
def check_in_form(message='', title='Checked-in Volunteers'):
with conn:
c = conn.cursor()
c.execute(
"SELECT hours.id, volunteer_id, volunteers.name AS volunteer, strftime('%m-%d %H:%M',start), end FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id WHERE end is null ORDER BY volunteer"
)
hours = c.fetchall()
c.execute(
"SELECT id, name FROM volunteers WHERE status = 'active' ORDER BY name"
)
volunteers = c.fetchall()
return template('check_in',
title=title,
admin=request.get_cookie("admin"),
message=message,
rows=hours,
volunteers=volunteers)
def list_hours(message='', title='Hours Report', start=datetime.date.today(),
end=(datetime.date.today() + datetime.timedelta(days=1)), group_by='', volunteer_id=''):
with conn:
c = conn.cursor()
# No grouping selected (default)
if group_by == '':
# "All Volunteers" selected (default)
if volunteer_id == '':
c.execute(
"SELECT hours.id, volunteers.name AS volunteer, activities.name AS activity, strftime('%m/%d/%Y %H:%M',start), strftime('%m/%d/%Y %H:%M',end), ROUND((strftime('%s',end)-strftime('%s',start))/3600.0,2) AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id LEFT OUTER JOIN activities ON hours.activity_id=activities.id WHERE start >= ? AND end <= ? ORDER BY start",
(start, end))
# A specific volunteer was selected
else:
c.execute(
"SELECT hours.id, volunteers.name AS volunteer, activities.name AS activity, strftime('%m/%d/%Y %H:%M',start), strftime('%m/%d/%Y %H:%M',end), ROUND((strftime('%s',end)-strftime('%s',start))/3600.0,2) AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id LEFT OUTER JOIN activities ON hours.activity_id=activities.id WHERE start >= ? AND end <= ? AND volunteers.id LIKE ? ORDER BY start",
(start, end, volunteer_id))
elif group_by == 'volunteer':
# "All Volunteers" selected (default)
if volunteer_id == '':
c.execute(
"SELECT volunteers.name AS volunteer, ROUND(SUM(strftime('%s',end)-strftime('%s',start))/3600.0,2) AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id WHERE start >= ? AND end <= ? GROUP BY volunteer_id ORDER BY volunteer",
(start, end))
# A specific volunteer was selected
else:
c.execute(
"SELECT volunteers.name AS volunteer, ROUND(SUM(strftime('%s',end)-strftime('%s',start))/3600.0,2) AS totalHours FROM hours JOIN volunteers ON hours.volunteer_id=volunteers.id WHERE start >= ? AND end <= ? AND volunteers.id LIKE ? GROUP BY volunteer_id ORDER BY volunteer",
(start, end, volunteer_id))
elif group_by == 'activity':
# "All Volunteers" selected (default)
if volunteer_id == '':
c.execute(
"SELECT activities.name AS activity, ROUND(SUM(strftime('%s',end)-strftime('%s',start))/3600.0,2) AS totalHours FROM hours JOIN activities ON hours.activity_id=activities.id WHERE start >= ? AND end <= ? GROUP BY activity_id ORDER BY activity",
(start, end))
# A specific volunteer was selected
else:
c.execute(
"SELECT activities.name AS activity, ROUND(SUM(strftime('%s',end)-strftime('%s',start))/3600.0,2) AS totalHours FROM hours JOIN activities ON hours.activity_id=activities.id JOIN volunteers ON hours.volunteer_id=volunteers.id WHERE start >= ? AND end <= ? AND volunteers.id LIKE ? GROUP BY activity_id ORDER BY activity",
(start, end, volunteer_id))
hours = c.fetchall()
c.execute("SELECT id, name FROM volunteers WHERE status = 'active' ORDER BY name")
volunteers = c.fetchall()
return template('hour_list', title=title, admin=request.get_cookie("admin"), message=message, rows=hours,
start=start, end=end, group_by=group_by, volunteers=volunteers, volunteer_id=volunteer_id)
def admin_login_form(message=''):
return template('admin_login', title="Admin Log-in", admin=request.get_cookie("admin"), message=message)
def new_activity_form(message='', title='New Activity'):
return template('activity_form',
title=title,
admin=request.get_cookie("admin"),
message=message)
def new_activity_form(message='', title='New Activity'):
return template('activity_form', title=title, admin=request.get_cookie("admin"), message=message)
def new_volunteer_form(message='', title='New Volunteer'):
today = datetime.date.today()
return template('volunteer_form', title=title, admin=request.get_cookie("admin"), message=message, today=today)
def admin_login_form(message=''):
return template('admin_login',
title="Admin Log-in",
admin=request.get_cookie("admin"),
message=message)