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")