service.py 16.6 KB
Newer Older
Patrick Kimber's avatar
python3  
Patrick Kimber committed
1
# -*- encoding: utf-8 -*-
2
import csv
Patrick Kimber's avatar
python3  
Patrick Kimber committed
3
import io
4

5
from datetime import date
6
from decimal import Decimal
Patrick Kimber's avatar
Patrick Kimber committed
7 8
from django.apps import apps
from django.conf import settings
9
from django.core.files.base import ContentFile
10
from django.db import transaction
11
from reportlab import platypus
12 13
from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
14

15
from finance.models import VatSettings
16
from report.pdf import PDFReport, NumberedCanvas
Patrick Kimber's avatar
Patrick Kimber committed
17
from .models import (
18
    Invoice,
Patrick Kimber's avatar
Patrick Kimber committed
19
    InvoiceContact,
Patrick Kimber's avatar
Patrick Kimber committed
20
    InvoiceError,
21
    InvoiceLine,
22
    InvoiceSettings,
23
    TimeRecord,
24 25 26
)


27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
def export_to_r(response):
    """Export invoice data to a CSV file (for data analysis using R).

    Documentation at:
    https://www.kbsoftware.co.uk/docs/app-invoice.html#export-to-csv-r

    """
    count = 0
    qs = InvoiceLine.objects.all().order_by("-pk")
    csv_writer = csv.writer(response, dialect="excel-tab")
    csv_writer.writerow(
        [
            "username",
            "invoice_date",
            "source",
            "category",
            "sku",
            "product",
            "quantity",
            "net",
        ]
    )
49
    for line in qs.iterator():
50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
        count = count + 1
        contact = line.invoice.contact
        csv_writer.writerow(
            [
                contact.user.username,
                line.invoice.invoice_date.isoformat(),
                line.invoice.source,
                line.product.category.name,
                line.product.name,
                line.product.description,
                line.quantity,
                line.net,
            ]
        )
        # if count > 3000:
        #    break
    return response
67 68


69
class InvoiceCreate:
70 71
    """ Create invoices for outstanding time records """

72 73
    def _add_time_records(self, user, invoice, time_records):
        """Add time records to a draft invoice."""
74
        invoice_settings = InvoiceSettings.objects.settings()
75
        vat_settings = VatSettings.objects.settings()
76
        for tr in time_records:
Patrick Kimber's avatar
Patrick Kimber committed
77 78
            contact = invoice.contact
            invoice_contact = InvoiceContact.objects.get(contact=contact)
79
            invoice_line = InvoiceLine(
80
                user=user,
81
                invoice=invoice,
82
                line_number=invoice.get_next_line_number(),
83
                product=invoice_settings.time_record_product,
84
                quantity=tr.invoice_quantity,
Patrick Kimber's avatar
Patrick Kimber committed
85
                price=invoice_contact.hourly_rate,
Patrick Kimber's avatar
Patrick Kimber committed
86
                units="hours",
87
                vat_code=vat_settings.standard_vat_code,
88
            )
Patrick Kimber's avatar
Patrick Kimber committed
89
            invoice_line.save_and_calculate()
90
            # link time record to invoice line
91 92 93
            tr.invoice_line = invoice_line
            tr.save()

94
    def _is_valid(self, contact, time_records, raise_exception=None):
95
        result = []
96
        # check the invoice settings are set-up
97 98 99 100 101 102
        invoice_settings = InvoiceSettings.objects.settings()
        if not invoice_settings.time_record_product:
            result.append(
                "Cannot create an invoice.  The invoice settings need a "
                "product selected to use for time records."
            )
103 104
        # check the VAT settings are set-up
        VatSettings.objects.settings()
105 106 107 108 109 110 111 112
        # check the hourly rate is setup
        hourly_rate = None
        try:
            invoice_contact = InvoiceContact.objects.get(contact=contact)
            hourly_rate = invoice_contact.hourly_rate
        except InvoiceContact.DoesNotExist:
            pass
        if not hourly_rate:
113
            result.append(
114
                "Cannot create invoice - no hourly rate for "
115
                "{}".format(contact.full_name)
116
            )
Patrick Kimber's avatar
Patrick Kimber committed
117
        # if not time_records.count():
118
        #    result.append("Cannot create invoice.  There are no time records.")
119
        for tr in time_records:
120 121 122 123 124 125 126 127 128
            if tr.is_complete:
                if tr.start_time > tr.end_time:
                    result.append(
                        "Time record '{}' ends before it starts "
                        "(from {} to {}) (are you running tests near "
                        "midnight?)".format(tr.pk, tr.start_time, tr.end_time)
                    )
                    break
            else:
129 130 131 132 133
                result.append(
                    "Cannot create invoice.  Time record '{}' does "
                    "not have a start date/time or end time.".format(tr)
                )
                break
134
        if result and raise_exception:
Patrick Kimber's avatar
Patrick Kimber committed
135
            raise InvoiceError(", ".join(result))
136 137
        else:
            return result
138

Patrick Kimber's avatar
Patrick Kimber committed
139 140
    def create(self, user, contact, currency, iteration_end):
        """Create invoices from time records."""
141
        invoice = None
142 143 144
        time_records = TimeRecord.objects.to_invoice_contact(
            contact, iteration_end
        )
145 146 147
        self._is_valid(contact, time_records, raise_exception=True)
        with transaction.atomic():
            if time_records.count():
148
                next_number = Invoice.objects.next_number()
149
                invoice = Invoice(
150
                    number=next_number,
151 152
                    invoice_date=date.today(),
                    contact=contact,
Patrick Kimber's avatar
Patrick Kimber committed
153
                    currency=currency,
154 155 156 157 158 159 160 161
                    user=user,
                )
                invoice.save()
            self._add_time_records(user, invoice, time_records)
        return invoice

    def draft(self, contact, iteration_end):
        """Return a queryset with time records selected to invoice"""
162
        return TimeRecord.objects.to_invoice_contact(contact, iteration_end)
163 164 165

    def is_valid(self, contact, raise_exception=None):
        iteration_end = date.today()
166 167 168
        time_records = TimeRecord.objects.to_invoice_contact(
            contact, iteration_end
        )
169 170 171 172 173 174 175 176
        return self._is_valid(contact, time_records, raise_exception)

    def refresh(self, user, invoice, iteration_end):
        """Add invoice lines to a previously created (draft) invoice."""
        if not invoice.is_draft:
            raise InvoiceError(
                "Time records can only be added to a draft invoice."
            )
177
        time_records = TimeRecord.objects.to_invoice_contact(
Patrick Kimber's avatar
Patrick Kimber committed
178
            invoice.contact, iteration_end
179 180 181 182 183 184
        )
        self._is_valid(invoice.contact, time_records, raise_exception=True)
        with transaction.atomic():
            self._add_time_records(user, invoice, time_records)
        return invoice

185

186
class InvoiceCreateBatch:
Patrick Kimber's avatar
Patrick Kimber committed
187
    def create(self, user, currency, iteration_end):
188
        """ Create invoices from time records """
189
        invoice_create = InvoiceCreate()
Patrick Kimber's avatar
Patrick Kimber committed
190 191
        model = apps.get_model(settings.CONTACT_MODEL)
        for contact in model.objects.all():
Patrick Kimber's avatar
Patrick Kimber committed
192
            invoice_create.create(user, contact, currency, iteration_end)
193 194


195
class InvoicePrint(PDFReport):
196 197 198 199 200 201 202 203
    """
    Write a PDF for an invoice which has already been created in the database.
    """

    def _get_column_styles(self, column_widths):
        # style - add vertical grid lines
        style = []
        for idx in range(len(column_widths) - 1):
Patrick Kimber's avatar
Patrick Kimber committed
204 205 206 207 208 209 210 211
            style.append(
                (
                    "LINEAFTER",
                    (idx, 0),
                    (idx, -1),
                    self.GRID_LINE_WIDTH,
                    colors.gray,
                )
Patrick Kimber's avatar
Patrick Kimber committed
212
            )
213 214 215
        return style

    def create_pdf(self, invoice, header_image):
216
        self.is_valid(invoice, raise_exception=True)
217
        # Create the document template
Patrick Kimber's avatar
python3  
Patrick Kimber committed
218
        buff = io.BytesIO()
219
        doc = platypus.SimpleDocTemplate(
Patrick Kimber's avatar
Patrick Kimber committed
220
            buff, title=invoice.description, pagesize=A4
221
        )
222 223
        invoice_settings = InvoiceSettings.objects.settings()
        vat_settings = VatSettings.objects.settings()
224 225
        # Container for the 'Flowable' objects
        elements = []
226
        elements.append(
227
            self._table_header(
Patrick Kimber's avatar
Patrick Kimber committed
228
                invoice, invoice_settings, vat_settings, header_image
229
            )
230
        )
231 232 233
        elements.append(platypus.Spacer(1, 12))
        elements.append(self._table_lines(invoice))
        elements.append(self._table_totals(invoice))
234
        for text in self._text_footer(invoice_settings.footer):
235 236
            elements.append(self._para(text))
        # write the document to disk
Patrick Kimber's avatar
Patrick Kimber committed
237
        doc.build(elements, canvasmaker=NumberedCanvas)
238 239
        pdf = buff.getvalue()
        buff.close()
Patrick Kimber's avatar
Patrick Kimber committed
240
        invoice_filename = "{}.pdf".format(invoice.invoice_number)
241 242
        invoice.is_draft = False
        invoice.pdf.save(invoice_filename, ContentFile(pdf), save=True)
243 244
        return invoice_filename

245 246 247 248 249 250 251 252 253 254 255 256
    def is_valid(self, invoice, raise_exception=None):
        result = []
        if not invoice.has_lines:
            result.append(
                "Invoice {} has no lines - cannot create "
                "PDF".format(invoice.invoice_number)
            )
        if not invoice.is_draft:
            result.append(
                "Invoice {} is not a draft invoice - cannot "
                "create a PDF".format(invoice.invoice_number)
            )
257 258 259 260 261 262 263 264 265 266 267 268
        is_credit = invoice.is_credit
        for line in invoice.invoiceline_set.all():
            if not line.is_credit == is_credit:
                if is_credit:
                    result.append(
                        "All credit note lines must have a negative quantity."
                    )
                else:
                    result.append(
                        "All invoice lines must have a positive quantity."
                    )
                break
269
        if result and raise_exception:
Patrick Kimber's avatar
Patrick Kimber committed
270
            raise InvoiceError(", ".join(result))
271 272 273
        else:
            return result

274 275 276 277 278 279 280 281 282
    def _table_invoice_detail(self, invoice):
        """
        Create a (mini) table containing the invoice date and number.

        This is returned as a 'mini table' which is inserted into the main
        header table to keep headings and data aligned.
        """
        # invoice header
        invoice_header_data = [
Patrick Kimber's avatar
Patrick Kimber committed
283
            [
Patrick Kimber's avatar
Patrick Kimber committed
284 285
                self._bold("Date"),
                "%s" % invoice.invoice_date.strftime("%d/%m/%Y"),
Patrick Kimber's avatar
Patrick Kimber committed
286
            ],
Patrick Kimber's avatar
Patrick Kimber committed
287
            [self._bold(invoice.description), "%s" % invoice.invoice_number],
288 289 290 291 292
        ]
        return platypus.Table(
            invoice_header_data,
            colWidths=[70, 200],
            style=[
Patrick Kimber's avatar
Patrick Kimber committed
293 294 295 296
                # ('GRID', (0, 0), (-1, -1), self.GRID_LINE_WIDTH, colors.grey),
                ("VALIGN", (0, 0), (-1, -1), "TOP"),
                ("LEFTPADDING", (0, 0), (-1, -1), 0),
            ],
297 298
        )

299
    def _table_header(
Patrick Kimber's avatar
Patrick Kimber committed
300 301
        self, invoice, invoice_settings, vat_settings, header_image
    ):
302 303 304 305 306 307 308 309 310 311 312 313 314
        """
        Create a table for the top section of the invoice (before the project
        description and invoice detail)
        """
        left = []
        right = []
        # left hand content
        left.append(self._para(self._text_invoice_address(invoice)))
        left.append(platypus.Spacer(1, 12))
        left.append(self._table_invoice_detail(invoice))
        # right hand content
        if header_image:
            right.append(self._image(header_image))
Patrick Kimber's avatar
Patrick Kimber committed
315 316 317 318 319
        right.append(
            self._para(
                self._text_our_address(invoice_settings.name_and_address)
            )
        )
320
        right.append(self._bold(invoice_settings.phone_number))
321
        if vat_settings.vat_number:
Patrick Kimber's avatar
Patrick Kimber committed
322 323 324
            right.append(
                self._para(self._text_our_vat_number(vat_settings.vat_number))
            )
325
        heading = [platypus.Paragraph(invoice.description, self.head_1)]
326 327
        # If the invoice has a logo, then the layout is different
        if header_image:
Patrick Kimber's avatar
Patrick Kimber committed
328
            data = [[heading + left, right]]  # left  # right
329 330
        else:
            data = [
Patrick Kimber's avatar
Patrick Kimber committed
331 332
                [heading, []],  # left (row one)  # right (row one)
                [left, right],  # left (row two)  # right (row two)
333 334 335 336 337 338
            ]

        return platypus.Table(
            data,
            colWidths=[300, 140],
            style=[
Patrick Kimber's avatar
Patrick Kimber committed
339 340 341 342
                ("VALIGN", (0, 0), (-1, -1), "TOP"),
                ("LEFTPADDING", (0, 0), (-1, -1), 0),
                # ('GRID', (0, 0), (-1, -1), self.GRID_LINE_WIDTH, colors.grey),
            ],
343 344 345 346 347
        )

    def _table_lines(self, invoice):
        """ Create a table for the invoice lines """
        # invoice line header
Patrick Kimber's avatar
Patrick Kimber committed
348 349 350
        data = [
            [None, self._para("Description"), "Net", "%VAT", "VAT", "Gross"]
        ]
351 352 353 354
        # lines
        lines = self._get_invoice_lines(invoice)
        # initial styles
        style = [
Patrick Kimber's avatar
Patrick Kimber committed
355 356 357 358
            # ('BOX', (0, 0), (-1, -1), self.GRID_LINE_WIDTH, colors.gray),
            ("LINEABOVE", (0, 0), (-1, 0), self.GRID_LINE_WIDTH, colors.gray),
            ("VALIGN", (0, 0), (0, -1), "TOP"),
            ("ALIGN", (2, 0), (-1, -1), "RIGHT"),
359 360 361 362 363
        ]
        # style - add horizontal grid lines
        for idx, line in enumerate(lines):
            row_number = line[0]
            if not row_number:
Patrick Kimber's avatar
Patrick Kimber committed
364 365 366 367 368 369 370 371
                style.append(
                    (
                        "LINEBELOW",
                        (0, idx),
                        (-1, idx),
                        self.GRID_LINE_WIDTH,
                        colors.gray,
                    )
Patrick Kimber's avatar
Patrick Kimber committed
372
                )
373
        # column widths
374
        column_widths = [30, 220, 50, 40, 50, 50]
375 376 377
        style = style + self._get_column_styles(column_widths)
        # draw the table
        return platypus.Table(
Patrick Kimber's avatar
Patrick Kimber committed
378
            data + lines, colWidths=column_widths, repeatRows=1, style=style
379 380 381 382 383 384 385
        )

    def _table_totals(self, invoice):
        """ Create a table for the invoice totals """
        gross = invoice.gross
        net = invoice.net
        vat = invoice.gross - invoice.net
Patrick Kimber's avatar
Patrick Kimber committed
386 387 388 389 390 391 392 393 394
        data = [
            [
                self._bold("Totals"),
                "%.2f" % net,
                None,
                "%.2f" % vat,
                "%.2f" % gross,
            ]
        ]
395
        style = [
Patrick Kimber's avatar
Patrick Kimber committed
396 397 398
            ("ALIGN", (1, 0), (-1, -1), "RIGHT"),
            ("LINEBELOW", (0, 0), (-1, 0), self.GRID_LINE_WIDTH, colors.gray),
            ("LINEABOVE", (0, 0), (-1, 0), 1, colors.black),
399 400 401
        ]
        column_widths = [250, 50, 40, 50, 50]
        style = style + self._get_column_styles(column_widths)
Patrick Kimber's avatar
Patrick Kimber committed
402
        return platypus.Table(data, colWidths=column_widths, style=style)
403 404 405 406

    def _get_invoice_line_description(self, invoice_line):
        result = []
        if invoice_line.description:
Patrick Kimber's avatar
Patrick Kimber committed
407
            result.append("{}".format(invoice_line.description))
408 409
        if invoice_line.has_time_record:
            time_record = invoice_line.timerecord
410
            if time_record.title:
Patrick Kimber's avatar
Patrick Kimber committed
411 412 413 414 415 416 417 418 419 420 421 422 423 424
                result.append("{}".format(time_record.title))
            result.append(
                "%s %s to %s"
                % (
                    time_record.date_started.strftime("%a %d %b %Y"),
                    time_record.start_time.strftime("from %H:%M"),
                    time_record.end_time.strftime("%H:%M"),
                )
            )
        result.append(
            "%.2f %s @ %s pounds"
            % (invoice_line.quantity, invoice_line.units, invoice_line.price)
        )
        return "<br />".join(result)
425 426 427 428 429 430 431 432

    def _get_invoice_lines(self, invoice):
        data = []
        ticket_pk = None
        for line in invoice.invoiceline_set.all():
            # ticket heading (do not repeat)
            if line.has_time_record and ticket_pk != line.timerecord.ticket.pk:
                ticket_pk = line.timerecord.ticket.pk
Patrick Kimber's avatar
Patrick Kimber committed
433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452
                data.append(
                    [
                        None,
                        self._bold(line.timerecord.ticket.title),
                        None,
                        None,
                        None,
                        None,
                    ]
                )
            data.append(
                [
                    "%s" % line.line_number,
                    self._para(self._get_invoice_line_description(line)),
                    "%.2f" % line.net,
                    "{:g}".format(self._round(line.vat_rate * Decimal("100"))),
                    "%.2f" % line.vat,
                    "%.2f" % (line.vat + line.net),
                ]
            )
453 454 455 456 457
        return data

    def _text_footer(self, footer):
        """ Build a list of text to go in the footer """
        result = []
Patrick Kimber's avatar
Patrick Kimber committed
458
        lines = footer.split("\n")
459
        for text in lines:
460 461 462 463 464
            result.append(text)
        return tuple(result)

    def _text_invoice_address(self, invoice):
        """ Name and address of contact we are invoicing """
465
        separator = "<br />"
Patrick Kimber's avatar
Patrick Kimber committed
466
        contact = invoice.contact
467
        result = contact.address(separator=separator)
468
        result = "{}{}{}".format(contact.full_name, separator, result)
469
        return result
470 471 472

    def _text_our_address(self, name_and_address):
        """ Company name and address """
Patrick Kimber's avatar
Patrick Kimber committed
473 474
        lines = name_and_address.split("\n")
        return "<br />".join(lines)
475 476

    def _text_our_vat_number(self, vat_number):
Patrick Kimber's avatar
Patrick Kimber committed
477
        return "<b>VAT Number</b> {}".format(vat_number)