def purge_personal_data(request, urlname):
conference = get_authenticated_conference(request, urlname)
if conference.personal_data_purged:
messages.warning(request, 'Personal data for this conference has already been purged')
return HttpResponseRedirect('../')
if request.method == 'POST':
exec_no_result("INSERT INTO confreg_aggregatedtshirtsizes (conference_id, size_id, num) SELECT conference_id, shirtsize_id, count(*) FROM confreg_conferenceregistration WHERE conference_id=%(confid)s AND shirtsize_id IS NOT NULL GROUP BY conference_id, shirtsize_id", {'confid': conference.id, })
exec_no_result("INSERT INTO confreg_aggregateddietary (conference_id, dietary, num) SELECT conference_id, lower(dietary), count(*) FROM confreg_conferenceregistration WHERE conference_id=%(confid)s AND dietary IS NOT NULL AND dietary != '' GROUP BY conference_id, lower(dietary)", {'confid': conference.id, })
exec_no_result("UPDATE confreg_conferenceregistration SET shirtsize_id=NULL, dietary='', phone='', address='' WHERE conference_id=%(confid)s", {'confid': conference.id, })
conference.personal_data_purged = timezone.now()
conference.save()
messages.info(request, "Personal data purged from conference")
return HttpResponseRedirect('../')
return render(request, 'confreg/admin_purge_personal_data.html', {
'conference': conference,
'helplink': 'personaldata',
'counts': exec_to_dict("""SELECT
count(1) FILTER (WHERE shirtsize_id IS NOT NULL) AS "T-shirt size registrations",
count(1) FILTER (WHERE dietary IS NOT NULL AND dietary != '') AS "Dietary needs",
count(1) FILTER (WHERE phone IS NOT NULL AND phone != '') AS "Phone numbers",
count(1) FILTER (WHERE address IS NOT NULL AND address != '') AS "Addresses"
FROM confreg_conferenceregistration WHERE conference_id=%(confid)s""", {
'confid': conference.id,
})[0],
})
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,
}
return exec_to_dict(
"""WITH main AS (
SELECT id, NULL::text as link, 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.confurl AS link, 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, link, datetime, title, summary, true AS readmore FROM main
UNION ALL
SELECT id, link, datetime, title, summary, false FROM conf
ORDER BY datetime DESC LIMIT 10""".format(extrafilter), params)
def items(self, obj):
return exec_to_dict(
"""SELECT n.id, c.confurl AS link, 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 admin_scan_status(request, confurlname):
conference = get_authenticated_conference(request, confurlname)
if not conference.askbadgescan:
return HttpResponse("Badge scanning not active")
uniquebysponsor = exec_to_keyed_scalar(
"""
SELECT
sp.id AS sponsorid,
count(DISTINCT sa.attendee_id) AS num
FROM confsponsor_sponsorscanner sc
INNER JOIN confsponsor_sponsor sp ON sc.sponsor_id=sp.id
LEFT JOIN confsponsor_scannedattendee sa ON sa.sponsor_id=sp.id
WHERE sp.conference_id=%(confid)s
GROUP BY sp.id""", {
'confid': conference.id,
})
uniquebyscanner = exec_to_dict(
"""
SELECT
sp.id AS sponsorid,
sp.name AS sponsorname,
r.email,
count(DISTINCT sa.attendee_id) AS num
FROM confsponsor_sponsorscanner sc
INNER JOIN confsponsor_sponsor sp ON sc.sponsor_id=sp.id
INNER JOIN confsponsor_sponsorshiplevel l ON sp.level_id=l.id
INNER JOIN confreg_conferenceregistration r ON r.id=sc.scanner_id
LEFT JOIN confsponsor_scannedattendee sa ON sa.sponsor_id=sp.id AND sa.scannedby_id=r.id
WHERE sp.conference_id=%(confid)s
GROUP BY sp.id, sp.name, l.id, r.email
ORDER BY l.levelcost DESC, l.levelname, sp.name, r.email
""", {
'confid': conference.id,
})
return render(
request, 'confsponsor/admin_scanstatus.html', {
'conference':
conference,
'uniquebysponsor':
uniquebysponsor,
'scans':
uniquebyscanner,
'breadcrumbs': (('/events/sponsor/admin/{0}/'.format(
conference.urlname), 'Sponsors'), ),
})
def accountstructure(request):
authenticate_backend_group(request, 'Accounting managers')
accounts = exec_to_dict(
"""SELECT ac.id AS classid, ac.name AS classname, ac.inbalance,
ag.id AS groupid, ag.name AS groupname,
a.id AS accountid, a.num AS accountnum, a.name AS accountname
FROM accounting_accountclass ac
INNER JOIN accounting_accountgroup ag ON ag.accountclass_id=ac.id
INNER JOIN accounting_account a ON a.group_id=ag.id
ORDER BY a.num""")
return render(request, 'accounting/structure.html', {
'accounts': accounts,
'topadmin': 'Accounting',
'helplink': 'accounting',
})
def index(request):
events = Conference.objects.filter(
promoactive=True, enddate__gte=today_global()).order_by('startdate')
series = ConferenceSeries.objects.filter(visible=True).extra(where=[
"EXISTS (SELECT 1 FROM confreg_conference c WHERE c.series_id=confreg_conferenceseries.id AND c.promoactive)"
])
# Native query, because django ORM vs UNION...
# If a news item has the flag "high priority until" until a date that's still in the future,
# make sure it always bubbles to the top of the list. We do this by creating a secondary ordering
# field to order by first. To make sure we capture all such things, we need to get at least the
# same number of items from each subset and then LIMIT it once again for the total limit.
news = exec_to_dict("""WITH main AS (
SELECT id, NULL::text AS confurl, NULL::text AS urlname, CASE WHEN highpriorityuntil > CURRENT_TIMESTAMP THEN 1 ELSE 0 END AS priosort, datetime, title, summary
FROM newsevents_news
WHERE datetime<CURRENT_TIMESTAMP ORDER BY datetime DESC LIMIT 5),
conf AS (
SELECT n.id, c.confurl, c.urlname, 0 AS priosort, 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
ORDER BY datetime DESC LIMIT 5)
SELECT id, confurl, urlname, datetime, title, summary, priosort FROM main
UNION ALL
SELECT id, confurl, urlname, datetime, title, summary, priosort FROM conf
ORDER BY priosort DESC, datetime DESC LIMIT 5""")
for n in news:
n['summaryhtml'] = markdown.markdown(n['summary'])
if n['confurl']:
n['itemlink'] = '/events/{0}/news/{1}-{2}/'.format(
n['urlname'],
slugify(n['title']),
n['id'],
)
else:
n['itemlink'] = '/news/{0}-{1}/'.format(slugify(n['title']),
n['id'])
return render(request, 'index.html', {
'events': events,
'series': series,
'news': news,
})
def send_volunteer_notification(conference, assignment, subject, template):
if not conference.notifyvolunteerstatus:
return
# No filter aggregates in our version of Django, so direct SQL it is
pending = exec_to_dict(
"SELECT count(*) FILTER (WHERE NOT org_confirmed) AS admin, count(*) FILTER (WHERE NOT vol_confirmed) AS volunteer FROM confreg_volunteerassignment a INNER JOIN confreg_volunteerslot s ON s.id=a.slot_id WHERE s.conference_id=%(confid)s",
{
'confid': conference.id,
})[0]
send_conference_notification_template(
conference,
subject,
'confreg/mail/{}'.format(template),
{
'conference': conference,
'assignment': assignment,
'pending': pending,
},
)
def refundexposure(request):
authenticate_backend_group(request, 'Invoice managers')
data = exec_to_dict("""WITH t AS (
SELECT DISTINCT c.conferencename as confname, coalesce(r.invoice_id, b.invoice_id) as invoiceid
FROM confreg_conferenceregistration r
INNER JOIN confreg_conference c ON c.id=r.conference_id
LEFT JOIN confreg_bulkpayment b on b.id=r.bulkpayment_id
WHERE c.enddate > CURRENT_DATE-'1 month'::interval
and payconfirmedat is not null and canceledat is null
and (r.invoice_id is not null or b.invoice_id is not null)
)
SELECT confname, internaldescription, count(*), sum(total_amount)
FROM invoices_invoice i
INNER JOIN t on i.id=t.invoiceid
INNER JOIN invoices_invoicepaymentmethod m on m.id=paidusing_id
GROUP BY rollup(confname), rollup(internaldescription)
ORDER BY 1,2;
""")
return render(request, 'invoices/refund_exposure.html', {
'data': data,
'helplink': 'payment',
})
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 _attendee_email_form(request, conference, query, breadcrumbs):
if request.method == 'POST':
idlist = list(map(int, request.POST['idlist'].split(',')))
else:
if 'idlist' not in request.GET:
raise Http404("Mandatory parameter idlist is missing")
idlist = list(map(int, request.GET['idlist'].split(',')))
queryparams = {'conference': conference.id, 'idlist': idlist}
recipients = exec_to_dict(query, queryparams)
initial = {
'_from': '{0} <{1}>'.format(conference.conferencename, conference.contactaddr),
'recipients': escape(", ".join(['{0} <{1}>'.format(x['fullname'], x['email']) for x in recipients])),
'idlist': ",".join(map(str, idlist)),
'storeonregpage': True,
}
if request.method == 'POST':
p = request.POST.copy()
p['recipients'] = initial['recipients']
form = BackendSendEmailForm(conference, data=p, initial=initial)
if form.is_valid():
with transaction.atomic():
if form.cleaned_data['storeonregpage']:
mailid = exec_to_scalar("INSERT INTO confreg_attendeemail (conference_id, sentat, subject, message, tocheckin, tovolunteers) VALUES (%(confid)s, CURRENT_TIMESTAMP, %(subject)s, %(message)s, false, false) RETURNING id", {
'confid': conference.id,
'subject': form.cleaned_data['subject'],
'message': form.cleaned_data['message'],
})
for r in recipients:
send_conference_mail(conference,
r['email'],
form.cleaned_data['subject'],
'confreg/mail/attendee_mail.txt',
{
'body': form.cleaned_data['message'],
'linkback': form.cleaned_data['storeonregpage'],
},
receivername=r['fullname'],
)
if form.cleaned_data['storeonregpage']:
if r['regid']:
# Existing registration, so attach directly to attendee
exec_no_result("INSERT INTO confreg_attendeemail_registrations (attendeemail_id, conferenceregistration_id) VALUES (%(mailid)s, %(reg)s)", {
'mailid': mailid,
'reg': r['regid'],
})
else:
# No existing registration, so queue it up in case the attendee
# might register later. We have the userid...
exec_no_result("INSERT INTO confreg_attendeemail_pending_regs (attendeemail_id, user_id) VALUES (%(mailid)s, %(userid)s)", {
'mailid': mailid,
'userid': r['user_id'],
})
if form.cleaned_data['storeonregpage']:
messages.info(request, "Email sent to %s attendees, and added to their registration pages when possible" % len(recipients))
else:
messages.info(request, "Email sent to %s attendees" % len(recipients))
return HttpResponseRedirect('../')
else:
form = BackendSendEmailForm(conference, initial=initial)
return render(request, 'confreg/admin_backend_form.html', {
'conference': conference,
'basetemplate': 'confreg/confadmin_base.html',
'form': form,
'what': 'new email',
'savebutton': 'Send email',
'cancelurl': '../',
'breadcrumbs': breadcrumbs,
})
