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, })