def _get_statistics(conference):
return [
(('Registration types', 'Done', 'Left'),
exec_to_list(
"SELECT regtype, count(1) FILTER (WHERE checkedinat IS NOT NULL), count(1) FILTER (WHERE checkedinat IS NULL) FROM confreg_conferenceregistration r INNER JOIN confreg_registrationtype rt ON rt.id=r.regtype_id WHERE r.conference_id=%(confid)s AND payconfirmedat IS NOT NULL GROUP BY ROLLUP(1) ORDER BY 1",
{'confid': conference.id})),
(('Check in users', 'Done', ''),
exec_to_list(
"SELECT username, count(1), NULL FROM confreg_conferenceregistration r INNER JOIN confreg_conferenceregistration r2 ON r2.id=r.checkedinby_id INNER JOIN auth_user u ON u.id=r2.attendee_id WHERE r.conference_id=%(confid)s AND r.payconfirmedat IS NOT NULL AND r.checkedinat IS NOT NULL GROUP BY 1 ORDER BY 2 DESC",
{'confid': conference.id})),
(('Latest checkins', 'By', 'Who'),
exec_to_list(
"SELECT to_char(r.checkedinat, 'ddth hh24:mi:ss'), username, r.firstname || ' ' || r.lastname FROM confreg_conferenceregistration r INNER JOIN confreg_conferenceregistration r2 ON r2.id=r.checkedinby_id INNER JOIN auth_user u ON u.id=r2.attendee_id WHERE r.conference_id=%(confid)s AND r.payconfirmedat IS NOT NULL AND r.checkedinat IS NOT NULL ORDER BY r.checkedinat DESC LIMIT 10",
{"confid": conference.id})),
]
def _common_countries():
for iso, prn in exec_to_list(
"WITH t AS (SELECT iso, printable_name FROM country c INNER JOIN (SELECT country_id FROM confreg_conferenceregistration r WHERE r.conference_id=%(confid)s AND payconfirmedat IS NOT NULL AND canceledat IS NULL AND country_id IS NOT NULL UNION ALL SELECT country_id FROM confreg_conference_initial_common_countries WHERE conference_id=%(confid)s) x ON c.iso=x.country_id GROUP BY c.iso ORDER BY count(iso) DESC LIMIT 8) SELECT iso, printable_name FROM t ORDER BY printable_name",
{
'confid': self.instance.conference.id,
}):
yield (iso, prn)
def get_sponsor_dashboard_data(conference):
return (
["Level", "Confirmed", "Unconfirmed"],
exec_to_list(
"SELECT l.levelname, count(s.id) FILTER (WHERE confirmed) AS confirmed, count(s.id) FILTER (WHERE NOT confirmed) AS unconfirmed FROM confsponsor_sponsorshiplevel l LEFT JOIN confsponsor_sponsor s ON s.level_id=l.id WHERE l.conference_id=%(confid)s GROUP BY l.id ORDER BY levelcost",
{
'confid': conference.id,
}))
def _get_merchantaccount_choices():
# Get all possible merchant accounts off creditcard settings
return [('', '---')] + exec_to_list(
"SELECT DISTINCT config->>'merchantaccount',config->>'merchantaccount' FROM invoices_invoicepaymentmethod where classname='postgresqleu.util.payment.adyen.AdyenCreditcard'"
)
def write_query(self, query, params):
self.write_rows(exec_to_list(query, params))
def signup_admin_sendmail(request, urlname, signupid):
conference = get_authenticated_conference(request, urlname)
signup = get_object_or_404(Signup, conference=conference, pk=signupid)
optionstrings = signup.options.split(',')
additional_choices = [('r_{0}'.format(r), 'Recipients who responded {0}'.format(optionstrings[r])) for r in range(len(optionstrings))]
if request.method == 'POST':
params = {'confid': conference.id, 'signup': signup.id}
rr = request.POST['recipients']
if signup.public:
qq = "FROM confreg_conferenceregistration r WHERE payconfirmedat IS NOT NULL AND canceledat IS NULL AND conference_id=%(confid)s"
else:
qq = "FROM confreg_conferenceregistration r WHERE payconfirmedat IS NOT NULL AND canceledat IS NULL AND conference_id=%(confid)s AND (regtype_id IN (SELECT registrationtype_id FROM confwiki_signup_regtypes srt WHERE srt.signup_id=%(signup)s) OR id IN (SELECT conferenceregistration_id FROM confwiki_signup_attendees WHERE signup_id=%(signup)s))"
if rr == 'responded':
qq += " AND EXISTS (SELECT 1 FROM confwiki_attendeesignup was WHERE was.signup_id=%(signup)s AND was.attendee_id=r.id)"
elif rr == 'noresp':
qq += " AND NOT EXISTS (SELECT 1 FROM confwiki_attendeesignup was WHERE was.signup_id=%(signup)s AND was.attendee_id=r.id)"
elif rr.startswith('r_'):
optnum = int(rr[2:])
qq += " AND EXISTS (SELECT 1 FROM confwiki_attendeesignup was WHERE was.signup_id=%(signup)s AND was.attendee_id=r.id AND choice=%(choice)s)"
params['choice'] = optionstrings[optnum]
numtosend = exec_to_scalar("SELECT count(*) {0}".format(qq), params)
form = SignupSendmailForm(conference, additional_choices, data=request.POST, num=numtosend)
if form.is_valid():
towhat = next(v for k, v in form.recipient_choices if k == rr)
recipients = exec_to_list("SELECT firstname || ' ' || lastname, email {0}".format(qq), params)
for n, e in recipients:
send_simple_mail(conference.contactaddr,
e,
"[{0}] {1}".format(conference.conferencename, form.cleaned_data['subject']),
"{0}\n\nTo view the signup, please see {1}/events/{2}/register/signup/{3}-{4}/".format(
form.cleaned_data['body'],
settings.SITEBASE, conference.urlname, slugify(signup.title), signup.id),
sendername=conference.conferencename,
receivername=n)
send_simple_mail(conference.notifyaddr,
conference.notifyaddr,
'Email sent to signup {0}'.format(signup.title),
"""An email was sent to recipients of the signup "{0}"\nIt was sent to {1}, leading to {2} recipients.\n\nSubject:{3}\nBody:\n{4}\n""".format(
signup.title,
towhat,
numtosend,
form.cleaned_data['subject'],
form.cleaned_data['body'],
),
sendername=conference.conferencename,
)
messages.info(request, "E-mail delivered to {0} recipients.".format(numtosend))
return HttpResponseRedirect('../')
else:
if signup.public and rr == 'all':
messages.warning(request, "Since this is a public signup and you are sending to all attendees, you should probably consider using regular mail send instead of signup mail send, so it gets delivered to future attendees as well!")
else:
form = SignupSendmailForm(conference, additional_choices)
numtosend = None
return render(request, 'confwiki/signup_sendmail_form.html', {
'conference': conference,
'form': form,
'signup': signup,
'numtosend': numtosend,
'breadcrumbs': (
('/events/admin/{0}/signups/'.format(conference.urlname), 'Signups'),
('/events/admin/{0}/signups/{1}/'.format(conference.urlname, signup.id), signup.title),
),
'helplink': 'signups',
})
