def _render_sheet_csv(self, form_data, sheet, output_file=None, **kwargs):
total = 0
counter = 0
if output_file:
writer = csv.writer(output_file, **kwargs)
for line in self.iterate_sheet(form_data, sheet):
if isinstance(line, self.ProgressSetTotal):
total = line.total
continue
line = [
localize(f) if isinstance(f, Decimal) else f for f in line
]
writer.writerow(line)
if total:
counter += 1
if counter % max(10, total // 100) == 0:
self.progress_callback(counter / total * 100)
return self.get_filename() + '.csv', 'text/csv', None
else:
output = io.StringIO()
writer = csv.writer(output, **kwargs)
for line in self.iterate_sheet(form_data, sheet):
if isinstance(line, self.ProgressSetTotal):
total = line.total
continue
line = [
localize(f) if isinstance(f, Decimal) else f for f in line
]
writer.writerow(line)
if total:
counter += 1
if counter % max(10, total // 100) == 0:
self.progress_callback(counter / total * 100)
return self.get_filename() + '.csv', 'text/csv', output.getvalue(
).encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
if form_data.get('dialect', '-') in csv.list_dialects():
writer = csv.writer(output, dialect=form_data.get('dialect'))
else:
writer = csv.writer(output,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
writer.writerow([_('Order code'), _('Name'), _('Telephone')])
for order in self.event.orders.filter(status__in=form_data['status']):
row = [order.code]
try:
row.append(order.invoice_address.name)
except InvoiceAddress.DoesNotExist:
row.append("")
contact_form_data = json.loads(
order.meta_info)['contact_form_data']
if 'telephone' in contact_form_data:
row.append(contact_form_data['telephone'])
else:
row.append("")
writer.writerow(row)
return '{}_telephone.csv'.format(
self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def _render_sheet_csv(self, form_data, sheet, output_file=None, **kwargs):
if output_file:
writer = csv.writer(output_file, **kwargs)
for line in self.iterate_sheet(form_data, sheet):
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', None
else:
output = io.StringIO()
writer = csv.writer(output, **kwargs)
for line in self.iterate_sheet(form_data, sheet):
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', output.getvalue(
).encode("utf-8")
def _download_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC, delimiter=",")
headers = [
_('Voucher code'), _('Valid until'), _('Product'), _('Reserve quota'), _('Bypass quota'),
_('Price effect'), _('Value'), _('Tag'), _('Redeemed'), _('Maximum usages')
]
writer.writerow(headers)
for v in self.get_queryset():
if v.item:
if v.variation:
prod = '%s – %s' % (str(v.item), str(v.variation))
else:
prod = '%s' % str(v.item)
elif v.quota:
prod = _('Any product in quota "{quota}"').format(quota=str(v.quota.name))
row = [
v.code,
v.valid_until.isoformat() if v.valid_until else "",
prod,
_("Yes") if v.block_quota else _("No"),
_("Yes") if v.allow_ignore_quota else _("No"),
v.get_price_mode_display(),
str(v.value) if v.value is not None else "",
v.tag,
str(v.redeemed),
str(v.max_usages)
]
writer.writerow(row)
r = HttpResponse(output.getvalue().encode("utf-8"), content_type='text/csv')
r['Content-Disposition'] = 'attachment; filename="vouchers.csv"'
return r
def _download_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC, delimiter=",")
headers = [
_('Voucher code'), _('Valid until'), _('Product'), _('Reserve quota'), _('Bypass quota'),
_('Price effect'), _('Value'), _('Tag'), _('Redeemed'), _('Maximum usages')
]
writer.writerow(headers)
for v in self.get_queryset():
if v.item:
if v.variation:
prod = '%s – %s' % (str(v.item), str(v.variation))
else:
prod = '%s' % str(v.item)
elif v.quota:
prod = _('Any product in quota "{quota}"').format(quota=str(v.quota.name))
row = [
v.code,
v.valid_until.isoformat() if v.valid_until else "",
prod,
_("Yes") if v.block_quota else _("No"),
_("Yes") if v.allow_ignore_quota else _("No"),
v.get_price_mode_display(),
str(v.value) if v.value is not None else "",
v.tag,
str(v.redeemed),
str(v.max_usages)
]
writer.writerow(row)
r = HttpResponse(output.getvalue().encode("utf-8"), content_type='text/csv')
r['Content-Disposition'] = 'attachment; filename="vouchers.csv"'
return r
def render(self, form_data: dict):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC, delimiter=",")
headers = [
_('Quota name'), _('Total quota'), _('Paid orders'), _('Pending orders'), _('Blocking vouchers'),
_('Current user\'s carts'), _('Waiting list'), _('Current availability')
]
writer.writerow(headers)
for quota in self.event.quotas.all():
avail = quota.availability()
row = [
quota.name,
_('Infinite') if quota.size is None else quota.size,
quota.count_paid_orders(),
quota.count_pending_orders(),
quota.count_blocking_vouchers(),
quota.count_in_cart(),
quota.count_waiting_list_pending(),
_('Infinite') if avail[1] is None else avail[1]
]
writer.writerow(row)
return '{}_quotas.csv'.format(self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def get_refund_export_csv(refund_export: RefundExport):
byte_data = io.BytesIO()
StreamWriter = codecs.getwriter('utf-8')
output = StreamWriter(byte_data)
writer = csv.writer(output)
writer.writerow(
[_("Payer"), "IBAN", "BIC",
_("Amount"),
_("Currency"),
_("Code")])
for row in refund_export.rows_data:
bic = ''
if row.get('bic'):
try:
BICValidator()(row['bic'])
except ValidationError:
pass
else:
bic = row['bic']
writer.writerow([
row['payer'],
row['iban'],
bic,
localize(Decimal(row['amount'])),
refund_export.currency,
row['id'],
])
filename = _get_filename(refund_export) + ".csv"
byte_data.seek(0)
return filename, 'text/csv', byte_data
def _render_csv(self, form_data, **kwargs):
output = io.StringIO()
writer = csv.writer(output, **kwargs)
for line in self.iterate_list(form_data):
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', output.getvalue(
).encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
writer = csv.writer(output,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
headers = [
_('Quota name'),
_('Total quota'),
_('Paid orders'),
_('Pending orders'),
_('Blocking vouchers'),
_('Current user\'s carts'),
_('Waiting list'),
_('Current availability')
]
writer.writerow(headers)
for quota in self.event.quotas.all():
avail = quota.availability()
row = [
quota.name,
_('Infinite') if quota.size is None else quota.size,
quota.count_paid_orders(),
quota.count_pending_orders(),
quota.count_blocking_vouchers(),
quota.count_in_cart(),
quota.count_waiting_list_pending(),
_('Infinite') if avail[1] is None else avail[1]
]
writer.writerow(row)
return '{}_quotas.csv'.format(
self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def test_writer_fail():
f = StringIO()
spamwriter = csv.writer(f,
delimiter=' ',
quotechar='|',
quoting=csv.QUOTE_MINIMAL)
with pytest.raises(csv.Error):
spamwriter.writerow(None)
def render_to_csv(title, rows, delimiter=','):
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="%s.csv"' % title
writer = csv.writer(response, delimiter=delimiter)
for row in rows:
writer.writerow(['' if x is None else str(x) for x in row])
return response
def render(self, form_data: dict):
output = io.StringIO()
tz = pytz.timezone(self.event.settings.timezone)
writer = csv.writer(output,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
provider_names = {
k: v.verbose_name
for k, v in self.event.get_payment_providers().items()
}
payments = OrderPayment.objects.filter(
order__event=self.event, ).order_by('created')
refunds = OrderRefund.objects.filter(
order__event=self.event).order_by('created')
if form_data['successful_only']:
payments = payments.filter(
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_REFUNDED), )
refunds = refunds.filter(state=OrderRefund.REFUND_STATE_DONE, )
objs = sorted(list(payments) + list(refunds), key=lambda o: o.created)
headers = [
_('Order'),
_('Payment ID'),
_('Creation date'),
_('Completion date'),
_('Status'),
_('Amount'),
_('Payment method')
]
writer.writerow(headers)
for obj in objs:
if isinstance(obj, OrderPayment) and obj.payment_date:
d2 = obj.payment_date.astimezone(tz).date().strftime(
'%Y-%m-%d')
elif isinstance(obj, OrderRefund) and obj.execution_date:
d2 = obj.execution_date.astimezone(tz).date().strftime(
'%Y-%m-%d')
else:
d2 = ''
row = [
obj.order.code, obj.full_id,
obj.created.astimezone(tz).date().strftime('%Y-%m-%d'), d2,
obj.get_state_display(),
localize(obj.amount *
(-1 if isinstance(obj, OrderRefund) else 1)),
provider_names.get(obj.provider, obj.provider)
]
writer.writerow(row)
return '{}_payments.csv'.format(
self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def _render_sheet_csv(self, form_data, sheet, output_file=None, **kwargs):
if output_file:
writer = csv.writer(output_file, **kwargs)
for line in self.iterate_sheet(form_data, sheet):
line = [
localize(f) if isinstance(f, Decimal) else f for f in line
]
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', None
else:
output = io.StringIO()
writer = csv.writer(output, **kwargs)
for line in self.iterate_sheet(form_data, sheet):
line = [
localize(f) if isinstance(f, Decimal) else f for f in line
]
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', output.getvalue(
).encode("utf-8")
def sanitize_csv(csvfile, out):
"""
protects csv from formula injection attacks
:param csvfile: file like object containing a csv
:param out: file like object to write the sanitized csv into
"""
r = csv.reader(csvfile)
w = csv.writer(out)
for row in r:
w.writerow(row)
out.seek(0)
def _render_csv(self, form_data, output_file=None, **kwargs):
if output_file:
if 'b' in output_file.mode:
output_file = io.TextIOWrapper(output_file,
encoding='utf-8',
newline='')
writer = csv.writer(output_file, **kwargs)
total = 0
counter = 0
for line in self.iterate_list(form_data):
if isinstance(line, self.ProgressSetTotal):
total = line.total
continue
line = [
localize(f) if isinstance(f, Decimal) else f for f in line
]
if total:
counter += 1
if counter % max(10, total // 100) == 0:
self.progress_callback(counter / total * 100)
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', None
else:
output = io.StringIO()
writer = csv.writer(output, **kwargs)
total = 0
counter = 0
for line in self.iterate_list(form_data):
if isinstance(line, self.ProgressSetTotal):
total = line.total
continue
line = [
localize(f) if isinstance(f, Decimal) else f for f in line
]
if total:
counter += 1
if counter % max(10, total // 100) == 0:
self.progress_callback(counter / total * 100)
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', output.getvalue(
).encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
tz = pytz.timezone(self.event.settings.timezone)
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC, delimiter=",")
provider_names = {
k: v.verbose_name
for k, v in self.event.get_payment_providers().items()
}
payments = OrderPayment.objects.filter(
order__event=self.event,
).order_by('created')
refunds = OrderRefund.objects.filter(
order__event=self.event
).order_by('created')
if form_data['successful_only']:
payments = payments.filter(
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED, OrderPayment.PAYMENT_STATE_REFUNDED),
)
refunds = refunds.filter(
state=OrderRefund.REFUND_STATE_DONE,
)
objs = sorted(list(payments) + list(refunds), key=lambda o: o.created)
headers = [
_('Order'), _('Payment ID'), _('Creation date'), _('Completion date'), _('Status'),
_('Amount'), _('Payment method')
]
writer.writerow(headers)
for obj in objs:
if isinstance(obj, OrderPayment) and obj.payment_date:
d2 = obj.payment_date.astimezone(tz).date().strftime('%Y-%m-%d')
elif isinstance(obj, OrderRefund) and obj.execution_date:
d2 = obj.execution_date.astimezone(tz).date().strftime('%Y-%m-%d')
else:
d2 = ''
row = [
obj.order.code,
obj.full_id,
obj.created.astimezone(tz).date().strftime('%Y-%m-%d'),
d2,
obj.get_state_display(),
localize(obj.amount * (-1 if isinstance(obj, OrderRefund) else 1)),
provider_names.get(obj.provider, obj.provider)
]
writer.writerow(row)
return '{}_payments.csv'.format(self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def test_writer():
f = StringIO()
spamwriter = csv.writer(f,
delimiter=' ',
quotechar='|',
quoting=csv.QUOTE_MINIMAL)
spamwriter.writerow(['Spam'] * 5 + ['@SUM(1+1)*cmd|\' /C calc\'!A0 '])
spamwriter.writerows([['Spam', 'Lovely Spam', 'Wonderful Spam'],
['A', 'B', '=3+4']])
f.seek(0)
assert f.read() == "Spam Spam Spam Spam Spam |'@SUM(1+1)*cmd\\||' /C calc'!A0 |\r\n" \
"Spam |Lovely Spam| |Wonderful Spam|\r\nA B '=3+4\r\n"
assert spamwriter.dialect
def render(self, form_data: dict):
output = io.StringIO()
if form_data.get('dialect', '-') in csv.list_dialects():
writer = csv.writer(output, dialect=form_data.get('dialect'))
elif form_data.get('dialect', '-') == "semicolon":
writer = csv.writer(output, dialect='excel', delimiter=';')
else:
writer = csv.writer(output,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
cl = self.event.checkin_lists.get(pk=form_data['list'])
questions = list(
Question.objects.filter(event=self.event,
id__in=form_data['questions']))
qs = self._get_queryset(cl, form_data)
name_scheme = PERSON_NAME_SCHEMES[self.event.settings.name_scheme]
headers = [
_('Order code'),
_('Attendee name'),
]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
headers.append(_('Attendee name: {part}').format(part=label))
headers += [_('Product'), _('Price'), _('Checked in')]
if not cl.include_pending:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID,
Order.STATUS_PENDING))
headers.append(_('Paid'))
if form_data['secrets']:
headers.append(_('Secret'))
headers.append(_('E-mail'))
if self.event.has_subevents:
headers.append(pgettext('subevent', 'Date'))
for q in questions:
headers.append(str(q.question))
headers.append(_('Company'))
headers.append(_('Voucher code'))
writer.writerow(headers)
for op in qs:
try:
ia = op.order.invoice_address
except InvoiceAddress.DoesNotExist:
ia = InvoiceAddress()
last_checked_in = None
if isinstance(op.last_checked_in, str): # SQLite
last_checked_in = dateutil.parser.parse(op.last_checked_in)
elif op.last_checked_in:
last_checked_in = op.last_checked_in
if last_checked_in and not is_aware(last_checked_in):
last_checked_in = make_aware(last_checked_in, UTC)
row = [
op.order.code,
op.attendee_name
or (op.addon_to.attendee_name if op.addon_to else '')
or ia.name,
]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
row.append((op.attendee_name_parts
or (op.addon_to.attendee_name_parts
if op.addon_to else {})
or ia.name_parts).get(k, ''))
row += [
str(op.item) +
(" – " + str(op.variation.value) if op.variation else ""),
op.price,
date_format(last_checked_in.astimezone(self.event.timezone),
'SHORT_DATETIME_FORMAT') if last_checked_in else ''
]
if cl.include_pending:
row.append(
_('Yes') if op.order.status ==
Order.STATUS_PAID else _('No'))
if form_data['secrets']:
row.append(op.secret)
row.append(op.attendee_email
or (op.addon_to.attendee_email if op.addon_to else '')
or op.order.email or '')
if self.event.has_subevents:
row.append(str(op.subevent))
acache = {}
if op.addon_to:
for a in op.addon_to.answers.all():
acache[a.question_id] = str(a)
for a in op.answers.all():
acache[a.question_id] = str(a)
for q in questions:
row.append(acache.get(q.pk, ''))
row.append(ia.company)
row.append(op.voucher.code if op.voucher else "")
writer.writerow(row)
return '{}_checkin.csv'.format(
self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
writer = csv.writer(output,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
cl = self.event.checkin_lists.get(pk=form_data['list'])
questions = list(
Question.objects.filter(event=self.event,
id__in=form_data['questions']))
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')
qs = OrderPosition.objects.filter(order__event=self.event, ).annotate(
last_checked_in=Subquery(cqs)).prefetch_related(
'answers',
'answers__question').select_related('order', 'item',
'variation', 'addon_to')
if not cl.all_products:
qs = qs.filter(
item__in=cl.limit_products.values_list('id', flat=True))
if cl.subevent:
qs = qs.filter(subevent=cl.subevent)
if form_data['sort'] == 'name':
qs = qs.order_by(
Coalesce('attendee_name', 'addon_to__attendee_name'))
elif form_data['sort'] == 'code':
qs = qs.order_by('order__code')
headers = [
_('Order code'),
_('Attendee name'),
_('Product'),
_('Price'),
_('Checked in')
]
if not cl.include_pending:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID,
Order.STATUS_PENDING))
headers.append(_('Paid'))
if form_data['secrets']:
headers.append(_('Secret'))
if self.event.settings.attendee_emails_asked:
headers.append(_('E-mail'))
if self.event.has_subevents:
headers.append(pgettext('subevent', 'Date'))
for q in questions:
headers.append(str(q.question))
writer.writerow(headers)
for op in qs:
last_checked_in = None
if isinstance(op.last_checked_in, str): # SQLite
last_checked_in = dateutil.parser.parse(op.last_checked_in)
elif op.last_checked_in:
last_checked_in = op.last_checked_in
if last_checked_in and not is_aware(last_checked_in):
last_checked_in = make_aware(last_checked_in, UTC)
row = [
op.order.code, op.attendee_name
or (op.addon_to.attendee_name if op.addon_to else ''),
str(op.item.name) +
(" – " + str(op.variation.value) if op.variation else ""),
op.price,
date_format(last_checked_in.astimezone(self.event.timezone),
'SHORT_DATETIME_FORMAT') if last_checked_in else ''
]
if cl.include_pending:
row.append(
_('Yes') if op.order.status ==
Order.STATUS_PAID else _('No'))
if form_data['secrets']:
row.append(op.secret)
if self.event.settings.attendee_emails_asked:
row.append(op.attendee_email or
(op.addon_to.attendee_email if op.addon_to else ''))
if self.event.has_subevents:
row.append(str(op.subevent))
acache = {}
for a in op.answers.all():
acache[a.question_id] = str(a)
for q in questions:
row.append(acache.get(q.pk, ''))
writer.writerow(row)
return '{}_checkin.csv'.format(
self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
writer = csv.writer(output,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
cl = self.event.checkin_lists.get(pk=form_data['list'])
questions = list(
Question.objects.filter(event=self.event,
id__in=form_data['questions']))
qs = OrderPosition.objects.filter(
order__event=self.event, ).prefetch_related(
'answers',
'answers__question').select_related('order', 'item',
'variation', 'addon_to')
if not cl.all_products:
qs = qs.filter(
item__in=cl.limit_products.values_list('id', flat=True))
if cl.subevent:
qs = qs.filter(subevent=cl.subevent)
if form_data['sort'] == 'name':
qs = qs.order_by(
Coalesce('attendee_name', 'addon_to__attendee_name'))
elif form_data['sort'] == 'code':
qs = qs.order_by('order__code')
headers = [
_('Order code'),
_('Attendee name'),
_('Product'),
_('Price')
]
if form_data['paid_only']:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID,
Order.STATUS_PENDING))
headers.append(_('Paid'))
if form_data['secrets']:
headers.append(_('Secret'))
if self.event.settings.attendee_emails_asked:
headers.append(_('E-mail'))
if self.event.has_subevents:
headers.append(pgettext('subevent', 'Date'))
for q in questions:
headers.append(str(q.question))
writer.writerow(headers)
for op in qs:
row = [
op.order.code,
op.attendee_name
or (op.addon_to.attendee_name if op.addon_to else ''),
str(op.item.name) +
(" – " + str(op.variation.value) if op.variation else ""),
op.price,
]
if not form_data['paid_only']:
row.append(
_('Yes') if op.order.status ==
Order.STATUS_PAID else _('No'))
if form_data['secrets']:
row.append(op.secret)
if self.event.settings.attendee_emails_asked:
row.append(op.attendee_email or
(op.addon_to.attendee_email if op.addon_to else ''))
if self.event.has_subevents:
row.append(str(op.subevent))
acache = {}
for a in op.answers.all():
acache[a.question_id] = str(a)
for q in questions:
row.append(acache.get(q.pk, ''))
writer.writerow(row)
return 'checkin.csv', 'text/csv', output.getvalue().encode("utf-8")
def _render_sheet_csv(self, form_data, sheet, **kwargs):
output = io.StringIO()
writer = csv.writer(output, **kwargs)
for line in self.iterate_sheet(form_data, sheet):
writer.writerow(line)
return self.get_filename() + '.csv', 'text/csv', output.getvalue().encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
tz = pytz.timezone(self.event.settings.timezone)
writer = csv.writer(output,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
p_date = OrderPayment.objects.filter(
order=OuterRef('pk'),
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_REFUNDED),
payment_date__isnull=False).values('order').annotate(
m=Max('payment_date')).values('m').order_by()
qs = self.event.orders.annotate(
payment_date=Subquery(p_date, output_field=DateTimeField(
))).select_related('invoice_address').prefetch_related('invoices')
if form_data['paid_only']:
qs = qs.filter(status=Order.STATUS_PAID)
tax_rates = self._get_all_tax_rates(qs)
headers = [
_('Order code'),
_('Order total'),
_('Status'),
_('Email'),
_('Order date'),
_('Company'),
_('Name'),
]
name_scheme = PERSON_NAME_SCHEMES[self.event.settings.name_scheme]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
headers.append(label)
headers += [
_('Address'),
_('ZIP code'),
_('City'),
_('Country'),
_('VAT ID'),
_('Date of last payment'),
_('Fees'),
_('Order locale')
]
for tr in tax_rates:
headers += [
_('Gross at {rate} % tax').format(rate=tr),
_('Net at {rate} % tax').format(rate=tr),
_('Tax value at {rate} % tax').format(rate=tr),
]
headers.append(_('Invoice numbers'))
writer.writerow(headers)
full_fee_sum_cache = {
o['order__id']: o['grosssum']
for o in OrderFee.objects.values('tax_rate', 'order__id').order_by(
).annotate(grosssum=Sum('value'))
}
fee_sum_cache = {
(o['order__id'], o['tax_rate']): o
for o in OrderFee.objects.values('tax_rate', 'order__id').order_by(
).annotate(taxsum=Sum('tax_value'), grosssum=Sum('value'))
}
sum_cache = {
(o['order__id'], o['tax_rate']): o
for o in OrderPosition.objects.values('tax_rate', 'order__id').
order_by().annotate(taxsum=Sum('tax_value'), grosssum=Sum('price'))
}
for order in qs.order_by('datetime'):
row = [
order.code,
localize(order.total),
order.get_status_display(),
order.email,
order.datetime.astimezone(tz).strftime('%Y-%m-%d'),
]
try:
row += [
order.invoice_address.company,
order.invoice_address.name,
]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
row.append(order.invoice_address.name_parts.get(k, ''))
row += [
order.invoice_address.street,
order.invoice_address.zipcode,
order.invoice_address.city,
order.invoice_address.country
if order.invoice_address.country else
order.invoice_address.country_old,
order.invoice_address.vat_id,
]
except InvoiceAddress.DoesNotExist:
row += [''] * (7 + (len(name_scheme['fields'])
if len(name_scheme['fields']) > 1 else 0))
row += [
order.payment_date.astimezone(tz).strftime('%Y-%m-%d')
if order.payment_date else '',
localize(full_fee_sum_cache.get(order.id) or Decimal('0.00')),
order.locale,
]
for tr in tax_rates:
taxrate_values = sum_cache.get((order.id, tr), {
'grosssum': Decimal('0.00'),
'taxsum': Decimal('0.00')
})
fee_taxrate_values = fee_sum_cache.get(
(order.id, tr), {
'grosssum': Decimal('0.00'),
'taxsum': Decimal('0.00')
})
row += [
localize(taxrate_values['grosssum'] +
fee_taxrate_values['grosssum']),
localize(taxrate_values['grosssum'] -
taxrate_values['taxsum'] +
fee_taxrate_values['grosssum'] -
fee_taxrate_values['taxsum']),
localize(taxrate_values['taxsum'] +
fee_taxrate_values['taxsum']),
]
row.append(', '.join([i.number for i in order.invoices.all()]))
writer.writerow(row)
return '{}_orders.csv'.format(
self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
tz = pytz.timezone(self.event.settings.timezone)
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC, delimiter=",")
p_date = OrderPayment.objects.filter(
order=OuterRef('pk'),
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED, OrderPayment.PAYMENT_STATE_REFUNDED),
payment_date__isnull=False
).values('order').annotate(
m=Max('payment_date')
).values(
'm'
).order_by()
qs = self.event.orders.annotate(
payment_date=Subquery(p_date, output_field=DateTimeField())
).select_related('invoice_address').prefetch_related('invoices')
if form_data['paid_only']:
qs = qs.filter(status=Order.STATUS_PAID)
tax_rates = self._get_all_tax_rates(qs)
headers = [
_('Order code'), _('Order total'), _('Status'), _('Email'), _('Order date'),
_('Company'), _('Name'),
]
name_scheme = PERSON_NAME_SCHEMES[self.event.settings.name_scheme]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
headers.append(label)
headers += [
_('Address'), _('ZIP code'), _('City'), _('Country'), _('VAT ID'),
_('Date of last payment'), _('Fees'), _('Order locale')
]
for tr in tax_rates:
headers += [
_('Gross at {rate} % tax').format(rate=tr),
_('Net at {rate} % tax').format(rate=tr),
_('Tax value at {rate} % tax').format(rate=tr),
]
headers.append(_('Invoice numbers'))
writer.writerow(headers)
full_fee_sum_cache = {
o['order__id']: o['grosssum'] for o in
OrderFee.objects.values('tax_rate', 'order__id').order_by().annotate(grosssum=Sum('value'))
}
fee_sum_cache = {
(o['order__id'], o['tax_rate']): o for o in
OrderFee.objects.values('tax_rate', 'order__id').order_by().annotate(
taxsum=Sum('tax_value'), grosssum=Sum('value')
)
}
sum_cache = {
(o['order__id'], o['tax_rate']): o for o in
OrderPosition.objects.values('tax_rate', 'order__id').order_by().annotate(
taxsum=Sum('tax_value'), grosssum=Sum('price')
)
}
for order in qs.order_by('datetime'):
row = [
order.code,
localize(order.total),
order.get_status_display(),
order.email,
order.datetime.astimezone(tz).strftime('%Y-%m-%d'),
]
try:
row += [
order.invoice_address.company,
order.invoice_address.name,
]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
row.append(
order.invoice_address.name_parts.get(k, '')
)
row += [
order.invoice_address.street,
order.invoice_address.zipcode,
order.invoice_address.city,
order.invoice_address.country if order.invoice_address.country else
order.invoice_address.country_old,
order.invoice_address.vat_id,
]
except InvoiceAddress.DoesNotExist:
row += [''] * (7 + (len(name_scheme['fields']) if len(name_scheme['fields']) > 1 else 0))
row += [
order.payment_date.astimezone(tz).strftime('%Y-%m-%d') if order.payment_date else '',
localize(full_fee_sum_cache.get(order.id) or Decimal('0.00')),
order.locale,
]
for tr in tax_rates:
taxrate_values = sum_cache.get((order.id, tr), {'grosssum': Decimal('0.00'), 'taxsum': Decimal('0.00')})
fee_taxrate_values = fee_sum_cache.get((order.id, tr),
{'grosssum': Decimal('0.00'), 'taxsum': Decimal('0.00')})
row += [
localize(taxrate_values['grosssum'] + fee_taxrate_values['grosssum']),
localize(taxrate_values['grosssum'] - taxrate_values['taxsum']
+ fee_taxrate_values['grosssum'] - fee_taxrate_values['taxsum']),
localize(taxrate_values['taxsum'] + fee_taxrate_values['taxsum']),
]
row.append(', '.join([i.number for i in order.invoices.all()]))
writer.writerow(row)
return '{}_orders.csv'.format(self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
tz = pytz.timezone(self.event.settings.timezone)
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC, delimiter=",")
qs = self.event.orders.all().select_related('invoice_address').prefetch_related('invoices')
if form_data['paid_only']:
qs = qs.filter(status=Order.STATUS_PAID)
tax_rates = self._get_all_tax_rates(qs)
headers = [
_('Order code'), _('Order total'), _('Status'), _('Email'), _('Order date'),
_('Company'), _('Name'), _('Address'), _('ZIP code'), _('City'), _('Country'), _('VAT ID'),
_('Payment date'), _('Payment type'), _('Payment method fee'),
]
for tr in tax_rates:
headers += [
_('Gross at {rate} % tax').format(rate=tr),
_('Net at {rate} % tax').format(rate=tr),
_('Tax value at {rate} % tax').format(rate=tr),
]
headers.append(_('Invoice numbers'))
writer.writerow(headers)
provider_names = {
k: v.verbose_name
for k, v in self.event.get_payment_providers().items()
}
sum_cache = {
(o['order__id'], o['tax_rate']): o for o in
OrderPosition.objects.values('tax_rate', 'order__id').order_by().annotate(
taxsum=Sum('tax_value'), grosssum=Sum('price')
)
}
for order in qs.order_by('datetime'):
row = [
order.code,
localize(order.total),
order.get_status_display(),
order.email,
order.datetime.astimezone(tz).strftime('%Y-%m-%d'),
]
try:
row += [
order.invoice_address.company,
order.invoice_address.name,
order.invoice_address.street,
order.invoice_address.zipcode,
order.invoice_address.city,
order.invoice_address.country if order.invoice_address.country else order.invoice_address.country_old,
order.invoice_address.vat_id,
]
except InvoiceAddress.DoesNotExist:
row += ['', '', '', '', '', '', '']
row += [
order.payment_date.astimezone(tz).strftime('%Y-%m-%d') if order.payment_date else '',
provider_names.get(order.payment_provider, order.payment_provider),
localize(order.payment_fee)
]
for tr in tax_rates:
taxrate_values = sum_cache.get((order.id, tr), {'grosssum': Decimal('0.00'), 'taxsum': Decimal('0.00')})
if tr == order.payment_fee_tax_rate and order.payment_fee_tax_value:
taxrate_values['grosssum'] += order.payment_fee
taxrate_values['taxsum'] += order.payment_fee_tax_value
row += [
localize(taxrate_values['grosssum']),
localize(taxrate_values['grosssum'] - taxrate_values['taxsum']),
localize(taxrate_values['taxsum']),
]
row.append(', '.join([i.number for i in order.invoices.all()]))
writer.writerow(row)
return 'orders.csv', 'text/csv', output.getvalue().encode("utf-8")
def render(self, form_data: dict):
output = io.StringIO()
if form_data.get('dialect', '-') in csv.list_dialects():
writer = csv.writer(output, dialect=form_data.get('dialect'))
elif form_data.get('dialect', '-') == "semicolon":
writer = csv.writer(output, dialect='excel', delimiter=';')
else:
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC, delimiter=",")
cl = self.event.checkin_lists.get(pk=form_data['list'])
questions = list(Question.objects.filter(event=self.event, id__in=form_data['questions']))
qs = self._get_queryset(cl, form_data)
name_scheme = PERSON_NAME_SCHEMES[self.event.settings.name_scheme]
headers = [
_('Order code'),
_('Attendee name'),
]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
headers.append(_('Attendee name: {part}').format(part=label))
headers += [
_('Product'), _('Price'), _('Checked in')
]
if not cl.include_pending:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID, Order.STATUS_PENDING))
headers.append(_('Paid'))
if form_data['secrets']:
headers.append(_('Secret'))
headers.append(_('E-mail'))
if self.event.has_subevents:
headers.append(pgettext('subevent', 'Date'))
for q in questions:
headers.append(str(q.question))
headers.append(_('Company'))
headers.append(_('Voucher code'))
writer.writerow(headers)
for op in qs:
try:
ia = op.order.invoice_address
except InvoiceAddress.DoesNotExist:
ia = InvoiceAddress()
last_checked_in = None
if isinstance(op.last_checked_in, str): # SQLite
last_checked_in = dateutil.parser.parse(op.last_checked_in)
elif op.last_checked_in:
last_checked_in = op.last_checked_in
if last_checked_in and not is_aware(last_checked_in):
last_checked_in = make_aware(last_checked_in, UTC)
row = [
op.order.code,
op.attendee_name or (op.addon_to.attendee_name if op.addon_to else '') or ia.name,
]
if len(name_scheme['fields']) > 1:
for k, label, w in name_scheme['fields']:
row.append(
(
op.attendee_name_parts or
(op.addon_to.attendee_name_parts if op.addon_to else {}) or
ia.name_parts
).get(k, '')
)
row += [
str(op.item) + (" – " + str(op.variation.value) if op.variation else ""),
op.price,
date_format(last_checked_in.astimezone(self.event.timezone), 'SHORT_DATETIME_FORMAT')
if last_checked_in else ''
]
if cl.include_pending:
row.append(_('Yes') if op.order.status == Order.STATUS_PAID else _('No'))
if form_data['secrets']:
row.append(op.secret)
row.append(op.attendee_email or (op.addon_to.attendee_email if op.addon_to else '') or op.order.email or '')
if self.event.has_subevents:
row.append(str(op.subevent))
acache = {}
if op.addon_to:
for a in op.addon_to.answers.all():
acache[a.question_id] = str(a)
for a in op.answers.all():
acache[a.question_id] = str(a)
for q in questions:
row.append(acache.get(q.pk, ''))
row.append(ia.company)
row.append(op.voucher.code if op.voucher else "")
writer.writerow(row)
return '{}_checkin.csv'.format(self.event.slug), 'text/csv', output.getvalue().encode("utf-8")
