Ejemplo n.º 1
0
def export_producer_stock(producers, customer_price=False, wb=None):
    yellowFill = Fill()
    yellowFill.start_color.index = 'FFEEEE11'
    yellowFill.end_color.index = 'FFEEEE11'
    yellowFill.fill_type = Fill.FILL_SOLID

    header = [
        (_("Id"), 5),
        (_("Producer"), 60),
        (_("Reference"), 20),
        (_("Product"), 60),
        (_("Customer unit price")
         if customer_price else _("Producer unit price"), 10),
        (_("Deposit"), 10),
        (_("Inventory"), 10),
        (repanier.apps.REPANIER_SETTINGS_CURRENCY_DISPLAY, 15),
    ]
    producers = producers.iterator()
    producer = next_row(producers)
    wb, ws = new_landscape_a4_sheet(wb, _('Inventory'), _('Inventory'), header)
    show_column_reference = False
    row_num = 1
    while producer is not None:
        products = Product.objects.filter(
            producer_id=producer.id,
            is_active=True,
            translations__language_code=translation.get_language()).order_by(
                "translations__long_name",
                "order_average_weight",
            ).select_related('producer', 'department_for_customer').iterator()
        product = next_row(products)
        while product is not None:
            if product.order_unit < PRODUCT_ORDER_UNIT_DEPOSIT:
                c = ws.cell(row=row_num, column=0)
                c.value = product.id
                c = ws.cell(row=row_num, column=1)
                c.value = "{}".format(product.producer)
                if len(product.reference) < 36:
                    if product.reference.isdigit():
                        # Avoid display of exponent by Excel
                        product_reference = "[{}]".format(product.reference)
                    else:
                        product_reference = product.reference
                    show_column_reference = True
                else:
                    product_reference = EMPTY_STRING
                c = ws.cell(row=row_num, column=2)
                c.value = "{}".format(product_reference)
                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                c.style.borders.bottom.border_style = Border.BORDER_THIN
                c = ws.cell(row=row_num, column=3)
                if product.department_for_customer is not None:
                    c.value = "{} - {}".format(
                        product.get_long_name(),
                        product.department_for_customer.short_name)
                else:
                    c.value = product.get_long_name()
                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                c.style.alignment.wrap_text = True
                c.style.borders.bottom.border_style = Border.BORDER_THIN
                c = ws.cell(row=row_num, column=4)
                unit_price = product.customer_unit_price if customer_price else product.producer_unit_price
                c.value = unit_price.amount
                c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                c.style.borders.bottom.border_style = Border.BORDER_THIN
                c = ws.cell(row=row_num, column=5)
                c.value = product.unit_deposit.amount
                c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                c.style.borders.bottom.border_style = Border.BORDER_THIN
                c = ws.cell(row=row_num, column=6)
                c.value = product.stock
                c.style.number_format.format_code = '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_ '
                c.style.font.color = Color(Color.BLUE)
                c.style.borders.bottom.border_style = Border.BORDER_THIN
                c = ws.cell(row=row_num, column=7)
                c.value = "=ROUND((E{}+F{})*G{},2)".format(
                    row_num + 1, row_num + 1, row_num + 1)
                c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                ws.conditional_formatting.addCellIs(
                    get_column_letter(8) + str(row_num + 1), 'notEqual', [
                        str((
                            (unit_price.amount + product.unit_deposit.amount) *
                            product.stock).quantize(TWO_DECIMALS))
                    ], True, wb, None, None, yellowFill)
                c.style.borders.bottom.border_style = Border.BORDER_THIN
                row_num += 1
            product = next_row(products)
        row_num += 1
        c = ws.cell(row=row_num, column=4)
        c.value = "{}".format(_("Total"), )
        c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
        c.style.font.bold = True
        c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT
        c = ws.cell(row=row_num, column=7)
        formula = "SUM(H{}:H{})".format(2, row_num)
        c.value = "=" + formula
        c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
        c.style.font.bold = True

        ws.column_dimensions[get_column_letter(1)].visible = False
        if not show_column_reference:
            ws.column_dimensions[get_column_letter(3)].visible = False
        producer = next_row(producers)
    return wb
Ejemplo n.º 2
0
def export_purchase(permanence=None,
                    year=None,
                    producer=None,
                    customer=None,
                    wb=None):
    yellowFill = Fill()
    yellowFill.start_color.index = 'FFEEEE11'
    yellowFill.end_color.index = 'FFEEEE11'
    yellowFill.fill_type = Fill.FILL_SOLID

    header = [
        (_("Format"), 5),
        (_("Id"), 10),
        (_("Date"), 15),
        (_("Producer"), 15),
        (_("Product"), 60),
        (_("Customer"), 15),
        (_("Quantity invoiced"), 10),
        (_("Producer unit price"), 10),
        (_("Deposit"), 10),
        (_("Purchase price"), 10),
        (_("VAT"), 10),
        (_("Rule of 3"), 10),
        (_("Comment"), 30),
        (_("VAT rate"), 10),
        (_("CustId_01"), 10),
    ]

    if producer is None:
        if permanence is not None:
            if customer is not None:
                producers = Producer.objects.filter(
                    producerinvoice__permanence_id=permanence.id).distinct(
                    ).iterator()
                title1 = "{}-{}".format(customer.short_basket_name, permanence)
            else:
                producers = Producer.objects.filter(
                    producerinvoice__permanence_id=permanence.id).distinct(
                    ).iterator()
                title1 = "{}".format(permanence)
        else:
            producers = Producer.objects.filter(
                producerinvoice__permanence__permanence_date__year=year
            ).distinct().iterator()
            title1 = "{}-{}".format(customer.short_basket_name, year)
    else:
        producers = Producer.objects.filter(id=producer.id).iterator()
        title1 = "{}-{}".format(producer.short_profile_name, year)
    producer = next_row(producers)
    if producer is not None:
        wb, ws = new_landscape_a4_sheet(wb, title1, _('Invoices'), header)
        row_num = 1
        count_all_purchase = 0
        purchase_price_all_purchase = []
        tax_all_purchase = []
        while producer is not None:
            producer_save = producer
            # count_producer_purchase = 0
            producer_price = DECIMAL_ZERO
            if producer.invoice_by_basket:
                if year is None:
                    if customer is None:
                        purchases = Purchase.objects.filter(
                            permanence_id=permanence.id,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(
                            "customer__short_basket_name",
                            "offer_item__translations__preparation_sort_order"
                        ).iterator()
                    else:
                        purchases = Purchase.objects.filter(
                            permanence_id=permanence.id,
                            customer_id=customer.id,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(
                            "customer__short_basket_name",
                            "offer_item__translations__preparation_sort_order"
                        ).iterator()
                else:
                    if customer is None:
                        purchases = Purchase.objects.filter(
                            permanence__status__gte=PERMANENCE_INVOICED,
                            permanence__permanence_date__year=year,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(
                            "permanence_id", "customer__short_basket_name",
                            "offer_item__translations__preparation_sort_order"
                        ).iterator()
                    else:
                        purchases = Purchase.objects.filter(
                            permanence__status__gte=PERMANENCE_INVOICED,
                            permanence__permanence_date__year=year,
                            customer_id=customer.id,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(
                            "permanence_id",
                            "offer_item__translations__preparation_sort_order"
                        ).iterator()
                purchase = next_purchase(purchases)
                while purchase is not None:
                    permanence_save = purchase.permanence
                    count_permanence_purchase = 0
                    row_start_permanence = 0
                    row_num += 1
                    while purchase is not None and permanence_save.id == purchase.permanence_id:
                        customer_save = purchase.customer
                        count_purchase = 0
                        row_start_purchase = 0
                        purchases_price = DECIMAL_ZERO
                        while purchase is not None and permanence_save.id == purchase.permanence_id \
                                and customer_save.id == purchase.customer_id:
                            offer_item_save = purchase.offer_item
                            department_for_customer_save = offer_item_save.department_for_customer
                            department_for_customer_save__short_name = department_for_customer_save.short_name \
                                if department_for_customer_save is not None else EMPTY_STRING

                            while purchase is not None and permanence_save.id == purchase.permanence_id \
                                    and customer_save.id == purchase.customer_id \
                                    and department_for_customer_save == purchase.offer_item.department_for_customer:
                                c = ws.cell(row=row_num, column=1)
                                c.value = purchase.id
                                c = ws.cell(row=row_num, column=2)
                                c.value = permanence_save.permanence_date
                                c.style.number_format.format_code = NumberFormat.FORMAT_DATE_DDMMYYYY
                                c = ws.cell(row=row_num, column=3)
                                c.value = "{}".format(
                                    producer_save.short_profile_name)
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                if count_purchase == 0:
                                    row_start_purchase = row_num + 1
                                    if count_permanence_purchase == 0:
                                        c.style.font.bold = True
                                        row_start_permanence = row_start_purchase
                                    c = ws.cell(row=row_num, column=0)
                                    c.value = "A"
                                else:
                                    c = ws.cell(row=row_num, column=0)
                                    c.value = "B"
                                count_purchase += 1
                                c = ws.cell(row=row_num, column=4)
                                if department_for_customer_save__short_name is not None:
                                    c.value = "{} - {}".format(
                                        purchase.get_long_name(),
                                        department_for_customer_save__short_name
                                    )
                                else:
                                    c.value = "{}".format(
                                        purchase.get_long_name())
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                c = ws.cell(row=row_num, column=5)
                                c.value = "{}".format(
                                    customer_save.short_basket_name)
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                if count_purchase == 0 and customer is None:
                                    c.style.font.bold = True
                                c = ws.cell(row=row_num, column=6)
                                c.value = purchase.quantity_invoiced
                                c.style.number_format.format_code = '#,##0.????'
                                if year is None:
                                    c.style.font.color = Color(Color.BLUE)
                                    ws.conditional_formatting.addCellIs(
                                        get_column_letter(7) +
                                        str(row_num + 1), 'notEqual',
                                        [str(purchase.quantity_invoiced)],
                                        True, wb, None, None, yellowFill)
                                c = ws.cell(row=row_num, column=7)
                                c.value = purchase.get_producer_unit_price()
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                c = ws.cell(row=row_num, column=8)
                                c.value = purchase.offer_item.unit_deposit.amount
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                c = ws.cell(row=row_num, column=9)
                                c.value = "=ROUND(G{}*(H{}+I{}),2)".format(
                                    row_num + 1, row_num + 1, row_num + 1)
                                if year is None:
                                    purchase_price = (
                                        purchase.quantity_invoiced *
                                        (purchase.get_producer_unit_price() +
                                         purchase.get_unit_deposit())
                                    ).quantize(TWO_DECIMALS)
                                    purchases_price += purchase_price
                                    c.style.font.color = Color(Color.BLUE)
                                    ws.conditional_formatting.addCellIs(
                                        get_column_letter(10) +
                                        str(row_num + 1), 'notEqual',
                                        [str(purchase_price)], True, wb, None,
                                        None, yellowFill)
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                c = ws.cell(row=row_num, column=10)
                                c.value = "=G{}*{}".format(
                                    row_num + 1,
                                    purchase.offer_item.customer_vat.amount)
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                c = ws.cell(row=row_num, column=12)
                                c.value = "{}".format(
                                    cap(purchase.comment, 100))
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                c = ws.cell(row=row_num, column=13)
                                c.value = "{}".format(purchase.offer_item.
                                                      get_vat_level_display())
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                c = ws.cell(row=row_num, column=14)
                                c.value = "{}".format(
                                    purchase.customer.user.email)
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                delta = 5
                                for col_num in range(5):
                                    c = ws.cell(row=row_num,
                                                column=delta + col_num)
                                    c.style.borders.top.border_style = Border.BORDER_THIN
                                row_num += 1
                                purchase = next_purchase(purchases)

                        count_permanence_purchase += count_purchase
                        if year is None and count_purchase > 1:
                            c = ws.cell(row=row_num - 1, column=11)
                            c.value = "=SUM(J{}:J{})".format(
                                row_start_purchase, row_num)
                            c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                            c.style.font.color = Color(Color.BLUE)
                            ws.conditional_formatting.addCellIs(
                                get_column_letter(12) + str(row_num),
                                'notEqual', [str(purchases_price)], True, wb,
                                None, None, yellowFill)
                            c = ws.cell(row=row_num - 1, column=0)
                            c.value = "C"
                            # row_num += 1

                        producer_price += purchases_price
                    if count_permanence_purchase > 0:
                        count_all_purchase += count_permanence_purchase
                        purchase_price_producer_purchase = "ROUND(SUM(J{}:J{}),2)".format(
                            row_start_permanence, row_num)
                        purchase_price_all_purchase.append(
                            purchase_price_producer_purchase)
                        tax_producer_purchase = "SUM(K{}:K{})".format(
                            row_start_permanence, row_num)
                        tax_all_purchase.append(tax_producer_purchase)

                        row_num += 1
                        c = ws.cell(row=row_num, column=8)
                        c.value = "{} : {} {}".format(
                            _("Total"), producer_save.short_profile_name,
                            permanence_save)
                        c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                        c.style.font.bold = True
                        c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT
                        c = ws.cell(row=row_num, column=9)
                        c.value = "={}".format(
                            purchase_price_producer_purchase)
                        c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                        c.style.font.bold = True
                        if year is None:
                            ws.conditional_formatting.addCellIs(
                                get_column_letter(10) + str(row_num + 1),
                                'notEqual', [str(producer_price)], True, wb,
                                None, None, yellowFill)
                        c = ws.cell(row=row_num, column=10)
                        c.value = "={}".format(tax_producer_purchase)
                        c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                        row_num += 1
                        for col_num in range(14):
                            c = ws.cell(row=row_num, column=col_num)
                            c.style.borders.top.border_style = Border.BORDER_MEDIUMDASHED
                        row_num += 1
            else:
                if year is None:
                    # Using quantity_for_preparation_sort_order the order is by customer__short_basket_name if the product
                    # is to be distributed by piece, otherwise by lower qty first.
                    if customer is None:
                        purchases = Purchase.objects.filter(
                            permanence_id=permanence.id,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(  # "product__placement",
                            "offer_item__translations__preparation_sort_order",
                            "quantity_for_preparation_sort_order",
                            "customer__short_basket_name").iterator()
                    else:
                        purchases = Purchase.objects.filter(
                            permanence_id=permanence.id,
                            customer_id=customer.id,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(  # "product__placement",
                            "offer_item__translations__preparation_sort_order",
                            "quantity_for_preparation_sort_order",
                            "customer__short_basket_name").iterator()
                else:
                    if customer is None:
                        purchases = Purchase.objects.filter(
                            permanence__status__gte=PERMANENCE_INVOICED,
                            permanence__permanence_date__year=year,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(
                            "permanence_id",
                            "offer_item__translations__preparation_sort_order",
                            "quantity_for_preparation_sort_order",
                            "customer__short_basket_name").iterator()
                    else:
                        purchases = Purchase.objects.filter(
                            permanence__status__gte=PERMANENCE_INVOICED,
                            permanence__permanence_date__year=year,
                            customer_id=customer.id,
                            producer_id=producer.id,
                            offer_item__translations__language_code=translation
                            .get_language()
                        ).order_by(
                            "permanence_id",
                            "offer_item__translations__preparation_sort_order",
                            "quantity_for_preparation_sort_order").iterator()
                purchase = next_purchase(purchases)
                while purchase is not None:
                    permanence_save = purchase.permanence
                    count_permanence_purchase = 0
                    row_start_permanence = 0
                    row_num += 1
                    while purchase is not None and permanence_save.id == purchase.permanence_id:
                        producer_save = purchase.producer
                        department_for_customer_save = purchase.offer_item.department_for_customer
                        department_for_customer_save__short_name = department_for_customer_save.short_name \
                            if department_for_customer_save is not None else EMPTY_STRING
                        while purchase is not None and permanence_save.id == purchase.permanence_id \
                                and producer_save == purchase.producer \
                                and department_for_customer_save == purchase.offer_item.department_for_customer:
                            offer_item_save = purchase.offer_item
                            count_offer_item = 0
                            row_first_offer_item = row_num
                            offer_items_price = DECIMAL_ZERO
                            for col_num in range(14):
                                c = ws.cell(row=row_num, column=col_num)
                                c.style.borders.top.border_style = Border.BORDER_THIN
                            row_start_offer_item = 0
                            while purchase is not None and offer_item_save == purchase.offer_item:
                                c = ws.cell(row=row_num, column=1)
                                c.value = purchase.id
                                c = ws.cell(row=row_num, column=2)
                                c.value = permanence_save.permanence_date
                                c.style.number_format.format_code = NumberFormat.FORMAT_DATE_DDMMYYYY
                                c = ws.cell(row=row_num, column=3)
                                c.value = "{}".format(
                                    producer_save.short_profile_name)
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                if count_offer_item == 0:
                                    row_start_offer_item = row_num + 1
                                    if count_permanence_purchase == 0:
                                        c.style.font.bold = True
                                        row_start_permanence = row_start_offer_item
                                    c = ws.cell(row=row_num, column=0)
                                    c.value = "A"
                                else:
                                    c = ws.cell(row=row_num, column=0)
                                    c.value = "B"
                                c = ws.cell(row=row_num, column=4)
                                if department_for_customer_save__short_name is not None:
                                    c.value = "{} - {}".format(
                                        purchase.get_long_name(),
                                        department_for_customer_save__short_name
                                    )
                                else:
                                    c.value = "{}".format(
                                        purchase.get_long_name())
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                if count_offer_item != 0:
                                    c.style.font.color.index = 'FF939393'
                                c = ws.cell(row=row_num, column=5)
                                c.value = "{}".format(
                                    purchase.customer.short_basket_name)
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                c = ws.cell(row=row_num, column=6)
                                c.value = purchase.quantity_invoiced
                                c.style.number_format.format_code = '#,##0.????'
                                if year is None:
                                    c.style.font.color = Color(Color.BLUE)
                                    ws.conditional_formatting.addCellIs(
                                        get_column_letter(7) +
                                        str(row_num + 1), 'notEqual',
                                        [str(purchase.quantity_invoiced)],
                                        True, wb, None, None, yellowFill)
                                c = ws.cell(row=row_num, column=7)
                                if count_offer_item == 0:
                                    producer_unit_price = purchase.get_producer_unit_price(
                                    )
                                    c.value = producer_unit_price
                                    c.style.font.color = Color(Color.BLUE)
                                    ws.conditional_formatting.addCellIs(
                                        get_column_letter(8) +
                                        str(row_num + 1), 'notEqual',
                                        [str(producer_unit_price)], True, wb,
                                        None, None, yellowFill)
                                else:
                                    c.value = "=H{}".format(
                                        row_first_offer_item + 1)
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                c = ws.cell(row=row_num, column=8)
                                c.value = purchase.offer_item.unit_deposit.amount
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                c = ws.cell(row=row_num, column=9)
                                c.value = "=ROUND(G{}*(H{}+I{}),2)".format(
                                    row_num + 1, row_first_offer_item + 1,
                                    row_num + 1)
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                if year is None:
                                    offer_item_price = (
                                        purchase.quantity_invoiced *
                                        (purchase.get_producer_unit_price() +
                                         purchase.get_unit_deposit())
                                    ).quantize(TWO_DECIMALS)
                                    offer_items_price += offer_item_price
                                    if offer_item_save.order_unit in [
                                            PRODUCT_ORDER_UNIT_KG,
                                            PRODUCT_ORDER_UNIT_PC_KG,
                                            PRODUCT_ORDER_UNIT_LT
                                    ]:
                                        c.style.font.color = Color(Color.BLUE)
                                    ws.conditional_formatting.addCellIs(
                                        get_column_letter(10) +
                                        str(row_num + 1), 'notEqual',
                                        [str(offer_item_price)], True, wb,
                                        None, None, yellowFill)
                                c = ws.cell(row=row_num, column=10)
                                c.value = "=G{}*{}".format(
                                    row_num + 1,
                                    purchase.offer_item.customer_vat.amount)
                                c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                c = ws.cell(row=row_num, column=12)
                                c.value = "{}".format(
                                    cap(purchase.comment, 100))
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                c = ws.cell(row=row_num, column=13)
                                c.value = "{}".format(purchase.offer_item.
                                                      get_vat_level_display())
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                c = ws.cell(row=row_num, column=14)
                                c.value = "{}".format(
                                    purchase.customer.user.email)
                                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                                delta = 5
                                for col_num in range(5):
                                    c = ws.cell(row=row_num,
                                                column=delta + col_num)
                                    c.style.borders.top.border_style = Border.BORDER_THIN

                                purchase = next_purchase(purchases)
                                row_num += 1
                                count_offer_item += 1

                            count_permanence_purchase += count_offer_item
                            if year is None and count_offer_item > 1:
                                if not offer_item_save.wrapped and offer_item_save.order_unit in [
                                        PRODUCT_ORDER_UNIT_KG,
                                        PRODUCT_ORDER_UNIT_PC_KG,
                                        PRODUCT_ORDER_UNIT_LT
                                ]:
                                    c = ws.cell(row=row_num - 1, column=11)
                                    c.value = "=SUM(J{}:J{})".format(
                                        row_start_offer_item, row_num)
                                    c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                                    c.style.font.color = Color(Color.BLUE)
                                    ws.conditional_formatting.addCellIs(
                                        get_column_letter(12) + str(row_num),
                                        'notEqual', [str(offer_items_price)],
                                        True, wb, None, None, yellowFill)
                                    c = ws.cell(row=row_num - 1, column=0)
                                    c.value = "D"
                                    # row_num += 1

                            producer_price += offer_items_price

                    if count_permanence_purchase > 0:
                        count_all_purchase += count_permanence_purchase
                        purchase_price_producer_purchase = "ROUND(SUM(J{}:J{}),2)".format(
                            row_start_permanence, row_num)
                        purchase_price_all_purchase.append(
                            purchase_price_producer_purchase)
                        tax_producer_purchase = "SUM(K{}:K{})".format(
                            row_start_permanence, row_num)
                        tax_all_purchase.append(tax_producer_purchase)

                        row_num += 1
                        c = ws.cell(row=row_num, column=8)
                        c.value = "{} : {} {}".format(
                            _("Total"), producer_save.short_profile_name,
                            permanence_save)
                        c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                        c.style.font.bold = True
                        c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT
                        c = ws.cell(row=row_num, column=9)
                        c.value = "={}".format(
                            purchase_price_producer_purchase)
                        c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                        c.style.font.bold = True
                        if year is None:
                            ws.conditional_formatting.addCellIs(
                                get_column_letter(10) + str(row_num + 1),
                                'notEqual', [str(producer_price)], True, wb,
                                None, None, yellowFill)
                        c = ws.cell(row=row_num, column=10)
                        c.value = "={}".format(tax_producer_purchase)
                        c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
                        row_num += 1
                        for col_num in range(14):
                            c = ws.cell(row=row_num, column=col_num)
                            c.style.borders.top.border_style = Border.BORDER_MEDIUMDASHED
                        row_num += 1
            producer = next_row(producers)

        if count_all_purchase > 0:
            row_num += 1
            c = ws.cell(row=row_num, column=8)
            c.value = "{} : {}".format(_('Total'), title1)
            c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
            c.style.font.bold = True
            c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT
            c = ws.cell(row=row_num, column=9)
            c.value = "={}".format("+".join(purchase_price_all_purchase))
            c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
            c.style.font.bold = True
            c = ws.cell(row=row_num, column=10)
            c.value = "={}".format("+".join(tax_all_purchase))
            c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX
            row_num += 1
            for col_num in range(14):
                c = ws.cell(row=row_num, column=col_num)
                c.style.borders.top.border_style = Border.BORDER_MEDIUMDASHED

        if year is None:
            ws.column_dimensions[get_column_letter(3)].visible = False
            ws.column_dimensions[get_column_letter(11)].visible = False
        else:
            ws.column_dimensions[get_column_letter(12)].visible = False
        ws.column_dimensions[get_column_letter(1)].visible = False
        ws.column_dimensions[get_column_letter(15)].visible = False

    return wb
Ejemplo n.º 3
0
def export_permanence_stock(permanence,
                            deliveries_id=(),
                            customer_price=False,
                            wb=None,
                            ws_customer_title=None):
    if settings.REPANIER_SETTINGS_STOCK and wb is not None:
        yellowFill = Fill()
        yellowFill.start_color.index = 'FFEEEE11'
        yellowFill.end_color.index = 'FFEEEE11'
        yellowFill.fill_type = Fill.FILL_SOLID

        header = [
            (_("Id"), 5),
            (_("OfferItem"), 5),
            (_("Reference"), 20),
            (_("Product"), 60),
            (_("Customer unit price")
             if customer_price else _("Producer unit price"), 10),
            (_("Deposit"), 10),
            (_("Asked"), 10),
            (_("Quantity ordered"), 10),
            (_("Initial stock"), 10),
            (repanier.apps.REPANIER_SETTINGS_CURRENCY_DISPLAY, 15),
            (_("Stock used"), 10),
            (_("Additional"), 10),
            (_("Remaining stock"), 10),
            (repanier.apps.REPANIER_SETTINGS_CURRENCY_DISPLAY, 15),
        ]
        offer_items = OfferItemWoReceiver.objects.filter(
            permanence_id=permanence.id,
            manage_production=True,
            translations__language_code=translation.get_language()).order_by(
                "producer",
                "translations__long_name",
                "order_average_weight",
            ).select_related('producer', 'department_for_customer').iterator()
        offer_item = next_row(offer_items)
        if offer_item is not None:
            # Check if there are deliveries_ws
            deliveries_ws = []
            if len(deliveries_id) > 0:
                for delivery_cpt, delivery_id in enumerate(deliveries_id):
                    ws_sc_name = format_worksheet_title("{}-{}".format(
                        delivery_cpt, ws_customer_title))
                    for sheet in wb.worksheets:
                        if ws_sc_name == sheet.title:
                            deliveries_ws.append(ws_sc_name)
                            break
            else:
                ws_sc_name = format_worksheet_title(ws_customer_title)
                for sheet in wb.worksheets:
                    if ws_sc_name == sheet.title:
                        deliveries_ws.append(ws_sc_name)
                        break
            wb, ws = new_landscape_a4_sheet(wb, _('Stock check'), permanence,
                                            header)
            formula_main_total_a = []
            formula_main_total_b = []
            show_column_reference = False
            show_column_qty_ordered = False
            show_column_add2stock = False
            row_num = 1
            while offer_item is not None:
                producer_save = offer_item.producer
                row_start_producer = row_num + 1
                c = ws.cell(row=row_num, column=2)
                c.value = "{}".format(producer_save.short_profile_name)
                c.style.font.bold = True
                c.style.font.italic = True
                while offer_item is not None and producer_save.id == offer_item.producer_id:
                    department_for_customer_save__id = offer_item.department_for_customer_id
                    department_for_customer_save__short_name = offer_item.department_for_customer.short_name \
                        if offer_item.department_for_customer is not None else None
                    while offer_item is not None and producer_save.id == offer_item.producer_id \
                            and department_for_customer_save__id == offer_item.department_for_customer_id:
                        if len(offer_item.reference) < 36:
                            if offer_item.reference.isdigit():
                                # Avoid display of exponent by Excel
                                offer_item_reference = "[{}]".format(
                                    offer_item.reference)
                            else:
                                offer_item_reference = offer_item.reference
                            show_column_reference = True
                        else:
                            offer_item_reference = EMPTY_STRING
                        if offer_item.order_unit < PRODUCT_ORDER_UNIT_DEPOSIT:

                            asked = offer_item.quantity_invoiced
                            stock = offer_item.stock
                            c = ws.cell(row=row_num, column=0)
                            c.value = offer_item.producer_id
                            c = ws.cell(row=row_num, column=1)
                            c.value = offer_item.id
                            c = ws.cell(row=row_num, column=2)
                            c.value = "{}".format(offer_item_reference)
                            c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=3)
                            if department_for_customer_save__short_name is not None:
                                c.value = "{} - {}".format(
                                    offer_item.get_long_name(),
                                    department_for_customer_save__short_name)
                            else:
                                c.value = "{}".format(
                                    offer_item.get_long_name())
                            c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                            c.style.alignment.wrap_text = True
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=4)
                            unit_price = offer_item.customer_unit_price if customer_price else offer_item.producer_unit_price
                            c.value = unit_price.amount
                            c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=5)
                            c.value = offer_item.unit_deposit.amount
                            c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=6)
                            if ws_customer_title is None:
                                c.value = asked
                            else:
                                if len(deliveries_ws) > 0:
                                    sum_value = "+".join(
                                        "SUMIF('{}'!B:B,B{},'{}'!F:F)".format(
                                            delivery_ws, row_num +
                                            1, delivery_ws)
                                        for delivery_ws in deliveries_ws)
                                    c.value = "={}".format(sum_value)
                                else:
                                    c.value = DECIMAL_ZERO
                            c.style.number_format.format_code = '#,##0.???'
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=7)
                            c.value = "=G{}-K{}+L{}".format(
                                row_num + 1, row_num + 1, row_num + 1)
                            if not show_column_qty_ordered:
                                show_column_qty_ordered = (
                                    asked - min(asked, stock)) > 0
                            c.style.number_format.format_code = '#,##0.???'
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=8)
                            c.value = stock
                            c.style.number_format.format_code = '#,##0.???'
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c.style.font.color = Color(Color.BLUE)
                            ws.conditional_formatting.addCellIs(
                                get_column_letter(9) + str(row_num + 1),
                                'notEqual', [str(stock)], True, wb, None, None,
                                yellowFill)
                            c = ws.cell(row=row_num, column=9)
                            c.value = "=ROUND(I{}*(E{}+F{}),2)".format(
                                row_num + 1, row_num + 1, row_num + 1)
                            c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=10)
                            c.value = "=MIN(G{},I{})".format(
                                row_num + 1, row_num + 1)
                            c.style.number_format.format_code = '#,##0.???'
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c = ws.cell(row=row_num, column=12)
                            c.value = "=I{}-K{}+L{}".format(
                                row_num + 1, row_num + 1, row_num + 1)
                            c.style.number_format.format_code = '#,##0.???'
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            c.style.font.bold = True
                            c = ws.cell(row=row_num, column=13)
                            c.value = "=ROUND(M{}*(E{}+F{}),2)".format(
                                row_num + 1, row_num + 1, row_num + 1)
                            c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                            c.style.borders.bottom.border_style = Border.BORDER_THIN
                            row_num += 1
                        offer_item = next_row(offer_items)
                row_num += 1
                c = ws.cell(row=row_num, column=3)
                c.value = "{} {}".format(_("Total price"),
                                         producer_save.short_profile_name)
                c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
                c.style.font.bold = True
                c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT
                c = ws.cell(row=row_num, column=9)
                formula = "SUM(J{}:J{})".format(row_start_producer, row_num)
                c.value = "=" + formula
                c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                c.style.font.bold = True
                formula_main_total_a.append(formula)
                c = ws.cell(row=row_num, column=13)
                formula = "SUM(N{}:N{})".format(row_start_producer, row_num)
                c.value = "=" + formula
                c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
                c.style.font.bold = True
                formula_main_total_b.append(formula)

                if offer_items is not None:
                    # Display a separator line between producers
                    row_num += 1
                    for col_num in range(16):
                        c = ws.cell(row=row_num, column=col_num)
                        c.style.borders.bottom.border_style = Border.BORDER_MEDIUMDASHED
                    row_num += 2

            c = ws.cell(row=row_num, column=3)
            c.value = "{}".format(_("Total price"))
            c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
            c.style.font.bold = True
            c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT
            c = ws.cell(row=row_num, column=9)
            c.value = "=" + "+".join(formula_main_total_a)
            c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
            c.style.font.bold = True
            c = ws.cell(row=row_num, column=13)
            c.value = "=" + "+".join(formula_main_total_b)
            c.style.number_format.format_code = repanier.apps.REPANIER_SETTINGS_CURRENCY_XLSX
            c.style.font.bold = True

            row_num += 1
            for col_num in range(16):
                c = ws.cell(row=row_num, column=col_num)
                c.style.borders.bottom.border_style = Border.BORDER_MEDIUMDASHED

            ws.column_dimensions[get_column_letter(1)].visible = False
            ws.column_dimensions[get_column_letter(2)].visible = False
            ws.column_dimensions[get_column_letter(11)].visible = False
            if not show_column_reference:
                ws.column_dimensions[get_column_letter(3)].visible = False
            if not show_column_qty_ordered:
                ws.column_dimensions[get_column_letter(8)].visible = False
            if not show_column_add2stock:
                ws.column_dimensions[get_column_letter(12)].visible = False
    return wb