def items(self, obj):
# Front page news is a mix of global and conference news, possibly
# filtered by NewsPosterProfile.
if obj is None:
extrafilter = ""
params = {}
else:
extrafilter = " AND author_id=%(authorid)s"
params = {
'authorid': obj.pk,
}
with ensure_conference_timezone(None):
return exec_to_dict("""WITH main AS (
SELECT id, NULL::text as urlname, datetime, title, summary
FROM newsevents_news
WHERE datetime<CURRENT_TIMESTAMP AND inrss {0}
ORDER BY datetime DESC LIMIT 10),
conf AS (
SELECT n.id, c.urlname, datetime, c.conferencename || ' - ' || title AS title, summary
FROM confreg_conferencenews n
INNER JOIN confreg_conference c ON c.id=conference_id
WHERE datetime<CURRENT_TIMESTAMP AND inrss {0}
ORDER BY datetime DESC LIMIT 10)
SELECT id, urlname, datetime, title, summary, true AS readmore FROM main
UNION ALL
SELECT id, urlname, datetime, title, summary, false FROM conf
ORDER BY datetime DESC LIMIT 10""".format(extrafilter), params)
def items(self, obj):
with ensure_conference_timezone(None):
return exec_to_dict(
"""SELECT n.id, c.urlname, datetime, c.conferencename || ' - ' || title AS title, summary
FROM confreg_conferencenews n
INNER JOIN confreg_conference c ON c.id=conference_id
WHERE datetime<CURRENT_TIMESTAMP AND inrss AND conference_id=%(cid)s
ORDER BY datetime DESC LIMIT 10""", {
'cid': obj.id,
})
def build_attendee_report(request, conference, data):
title = data['title']
format = data['format']
orientation = data['orientation']
pagesize = data.get('pagesize', 'A4')
borders = data['borders']
pagebreaks = data['pagebreaks']
fields = data['fields']
extracols = [_f for _f in [x.strip() for x in data['additionalcols'].split(',')] if _f]
# Build the filters. Each filter within a filter group is ANDed together, and then the
# filter groups are ORed together. And finally, all of this is ANDed with the conference
# (so we don't get attendees from other conferences)
def _reduce_Q(x, y):
return (
x[0] + [y[0]],
dict(x[1], **y[1])
)
filtermap = attendee_report_filters_map(conference)
allBlockQs = []
for blockno, fltblock in enumerate(data['filters']):
if fltblock:
try:
blockQs = reduce(_reduce_Q,
[filtermap[flt['filter']].build_SQL(flt, blockno) for flt in fltblock],
([], {})
)
allBlockQs.append((
"(" + "\n AND ".join(blockQs[0]) + ")",
blockQs[1],
), )
except Exception as e:
if format == 'html':
messages.warning(request, "Could not process filter: {}".format(e))
else:
return HttpResponse("Could not process filter: {}".format(e))
if allBlockQs:
(allblocks, params) = reduce(_reduce_Q, allBlockQs, ([], {}))
where = "AND (\n {}\n)".format(
"\n OR ".join(allblocks),
)
else:
where = ""
params = {}
params.update({
'conference_id': conference.id,
})
ofields = [_attendee_report_field_map[f] for f in (data['orderby1'], data['orderby2'])]
if format not in ('json', 'badge'):
# Regular reports, so we control all fields
rfields = [_attendee_report_field_map[f] for f in fields]
# Colums to actually select (including expressions)
cols = [f.get_select_name() for f in rfields]
# Table to join in to get the required columns
joins = [j.get_join() for j in rfields if j.get_join()]
# There could be more joins needed for the order by
joins.extend([j.get_join() for j in ofields if j.get_join() and j.get_join() not in joins])
joinstr = "\n".join(joins)
if joinstr:
joinstr = "\n" + joinstr
query = "SELECT r.id,{}\nFROM confreg_conferenceregistration r INNER JOIN confreg_conference conference ON conference.id=r.conference_id{}\nWHERE r.conference_id=%(conference_id)s {}\nORDER BY {}".format(
", ".join(cols),
joinstr,
where,
", ".join([o.get_orderby_field() for o in ofields]),
)
else:
# For json and badge, we have a mostly hardcoded query, but we still get the filter from
# above.
# We do this hardcoded because the django ORM can't even begin to understand what we're
# doing here, and generates a horrible loop of queries.
def _get_table_aliased_field(fieldname):
# If we have aliased a table, we have to map it in the orderby field as well. So the list of
# table aliases here has to match that in the below query
if '.' not in fieldname:
return fieldname
(table, _f) = fieldname.split('.')
return '{}.{}'.format({
'confreg_conferenceregistration': 'r',
'confreg_conference': 'conference',
'confreg_registrationtype': 'rt',
'confreg_registrationclass': 'rc',
'confreg_conferenceregistration_additionaloptions': 'crao',
'confreg_conferenceadditionaloption': 'ao',
'confreg_shirtsize': 's',
}.get(table, table), _f)
query = """SELECT r.id, firstname, lastname, email, company, address, phone, dietary, twittername, nick, badgescan, shareemail, vouchercode,
country.name AS countryname, country.printable_name AS country,
s.shirtsize,
'ID$' || idtoken || '$ID' AS fullidtoken,
'AT$' || publictoken || '$AT' AS fullpublictoken,
regexp_replace(upper(substring(CASE WHEN conference.queuepartitioning=1 THEN lastname WHEN conference.queuepartitioning=2 THEN firstname END, 1, 1)), '[^A-Z]', 'Other') AS queuepartition,
json_build_object('regtype', rt.regtype, 'specialtype', rt.specialtype,
'days', (SELECT array_agg(day) FROM confreg_registrationday rd INNER JOIN confreg_registrationtype_days rtd ON rtd.registrationday_id=rd.id WHERE rtd.registrationtype_id=rt.id),
'regclass', json_build_object('regclass', rc.regclass, 'badgecolor', rc.badgecolor, 'badgeforegroundcolor', rc.badgeforegroundcolor,
'bgcolortuplestr', CASE WHEN badgecolor!='' THEN ('x'||substring(badgecolor, 2, 2))::bit(8)::int || ',' || ('x'||substring(badgecolor, 4, 2))::bit(8)::int || ',' || ('x'||substring(badgecolor, 6, 2))::bit(8)::int END,
'fgcolortuplestr', CASE WHEN badgeforegroundcolor!='' THEN ('x'||substring(badgeforegroundcolor, 2, 2))::bit(8)::int || ',' || ('x'||substring(badgeforegroundcolor, 4, 2))::bit(8)::int || ',' || ('x'||substring(badgeforegroundcolor, 6, 2))::bit(8)::int END
)
) AS regtype,
COALESCE(json_agg(json_build_object('id', ao.id, 'name', ao.name)) FILTER (WHERE ao.id IS NOT NULL), '[]') AS additionaloptions
FROM confreg_conferenceregistration r
INNER JOIN confreg_conference conference ON conference.id=r.conference_id
INNER JOIN confreg_registrationtype rt ON rt.id=r.regtype_id
INNER JOIN confreg_registrationclass rc ON rc.id=rt.regclass_id
LEFT JOIN confreg_conferenceregistration_additionaloptions crao ON crao.conferenceregistration_id=r.id
LEFT JOIN confreg_conferenceadditionaloption ao ON crao.conferenceadditionaloption_id=ao.id
LEFT JOIN country ON country.iso=r.country_id
LEFT JOIN confreg_shirtsize s ON s.id=r.shirtsize_id
WHERE r.conference_id=%(conference_id)s {}
GROUP BY r.id, conference.id, rt.id, rc.id, country.iso, s.id
ORDER BY {}""".format(where, ", ".join([_get_table_aliased_field(o.get_orderby_field()) for o in ofields]))
with ensure_conference_timezone(conference):
result = exec_to_dict(query, params)
if format == 'html':
writer = ReportWriterHtml(request, conference, title, borders)
elif format == 'pdf':
writer = ReportWriterPdf(request, conference, title, borders)
writer.set_orientation_and_size(orientation, pagesize)
elif format == 'csv':
writer = ReportWriterCsv(request, conference, title, borders)
elif format == 'json':
resp = HttpResponse(content_type='application/json')
json.dump(result, resp, indent=2)
return resp
elif format == 'badge':
try:
resp = HttpResponse(content_type='application/pdf')
render_jinja_badges(conference, settings.FONTROOT, result, resp, borders, pagebreaks, orientation, pagesize)
return resp
except Exception as e:
return HttpResponse("Exception occured: %s" % e, content_type='text/plain')
else:
raise Exception("Unknown format")
allheaders = [_attendee_report_field_map[f].title for f in fields]
if len(extracols):
allheaders.extend(extracols)
writer.set_headers(allheaders)
for r in result:
row = [_attendee_report_field_map[f].get_value(r[f]) for f in fields]
row.extend([[]] * len(extracols))
writer.add_row(row)
return writer.render()
def api(request, urlname, regtoken, what):
(conference, user, is_admin) = _get_checkin(request, urlname, regtoken)
if what == 'status':
return _json_response({
'user': user.attendee.username,
'name': user.fullname,
'active': conference.checkinactive,
'confname': conference.conferencename,
'admin': is_admin,
})
# Only the stats API call is allowed when check-in is not open
if not conference.checkinactive and what != 'stats':
return HttpResponse("Check-in not open", status=412)
if what == 'lookup':
token = request.GET.get('lookup')
if not (token.startswith('ID$') and token.endswith('$ID')):
raise Http404()
token = token[3:-3]
r = get_object_or_404(ConferenceRegistration,
conference=conference,
payconfirmedat__isnull=False,
canceledat__isnull=True,
idtoken=token)
return _json_response({'reg': _get_reg_json(r)})
elif what == 'search':
s = request.GET.get('search').strip()
return _json_response({
'regs': [
_get_reg_json(r)
for r in ConferenceRegistration.objects.filter(
Q(firstname__icontains=s) | Q(lastname__icontains=s),
conference=conference,
payconfirmedat__isnull=False,
canceledat__isnull=True,
)
],
})
elif is_admin and what == 'stats':
with ensure_conference_timezone(conference):
return _json_response(_get_statistics(conference))
elif request.method == 'POST' and what == 'checkin':
if not conference.checkinactive:
return HttpResponse("Check-in not open", status=412)
reg = get_object_or_404(ConferenceRegistration,
conference=conference,
payconfirmedat__isnull=False,
canceledat__isnull=True,
pk=get_int_or_error(request.POST, 'reg'))
if reg.checkedinat:
return HttpResponse("Already checked in.", status=412)
reg.checkedinat = timezone.now()
reg.checkedinby = user
reg.save()
return _json_response({
'reg': _get_reg_json(reg),
})
else:
raise Http404()