def next_purchase(purchases):
purchase = next_row(purchases)
while purchase is not None \
and purchase.quantity_invoiced <= DECIMAL_ZERO \
and purchase.offer_item.order_unit < PRODUCT_ORDER_UNIT_DEPOSIT:
purchase = next_row(purchases)
return purchase
def next_purchase(purchases):
purchase = next_row(purchases)
while purchase is not None \
and purchase.quantity_invoiced <= DECIMAL_ZERO \
and purchase.offer_item.order_unit < PRODUCT_ORDER_UNIT_DEPOSIT:
purchase = next_row(purchases)
return purchase
def export_customer_prices(producer_qs, wb=None, producer_prices=True):
now = timezone.now()
if producer_prices:
wb, ws = new_landscape_a4_sheet(
wb, "%s" % _("Producer prices list"),
now.strftime(settings.DJANGO_SETTINGS_DATETIME))
else:
wb, ws = new_landscape_a4_sheet(
wb, "%s" % _("Customer prices list"),
now.strftime(settings.DJANGO_SETTINGS_DATETIME))
row_num = 0
products = Product.objects.filter(
is_active=True,
is_into_offer=True,
translations__language_code=translation.get_language(),
producer__in=producer_qs).order_by(
"department_for_customer",
"translations__long_name",
"order_average_weight",
).select_related('producer', 'department_for_customer').iterator()
product = next_row(products)
while product is not None:
row = [
(_("reference"), 10, product.reference
if len(product.reference) < 36 else EMPTY_STRING,
NumberFormat.FORMAT_TEXT, False),
(_("department_for_customer"), 15,
product.department_for_customer.short_name
if product.department_for_customer is not None else " ",
NumberFormat.FORMAT_TEXT, False),
# (_("is_into_offer"), 7, _("Yes") if product.is_into_offer else _("No"),
# NumberFormat.FORMAT_TEXT, False),
(_("wrapped"), 7, _("Yes") if product.wrapped else _("No"),
NumberFormat.FORMAT_TEXT, False),
(_("producer"), 15, product.producer.short_profile_name,
NumberFormat.FORMAT_TEXT, False),
(_("long_name"), 60, product.get_long_name(),
NumberFormat.FORMAT_TEXT, False),
(_("vat"), 10, product.get_vat_level_display(),
NumberFormat.FORMAT_TEXT, False),
]
if row_num == 0:
worksheet_set_header(ws, row)
row_num += 1
for col_num in range(len(row)):
c = ws.cell(row=row_num, column=col_num)
c.value = "%s" % (row[col_num][ROW_VALUE])
c.style.number_format.format_code = row[col_num][ROW_FORMAT]
if row[col_num][ROW_BOX]:
c.style.borders.top.border_style = Border.BORDER_THIN
c.style.borders.bottom.border_style = Border.BORDER_THIN
c.style.borders.left.border_style = Border.BORDER_THIN
c.style.borders.right.border_style = Border.BORDER_THIN
else:
c.style.borders.bottom.border_style = Border.BORDER_HAIR
row_num += 1
product = next_row(products)
return wb
def export(producer_qs, wb=None, producer_prices=True):
now = timezone.now()
if producer_prices:
wb, ws = new_landscape_a4_sheet(wb, "%s" % _("Producer prices list"),
now.strftime(settings.DJANGO_SETTINGS_DATETIME))
else:
wb, ws = new_landscape_a4_sheet(wb, "%s" % _("Customer prices list"),
now.strftime(settings.DJANGO_SETTINGS_DATETIME))
row_num = 0
products = Product.objects.filter(
is_active=True,
translations__language_code=translation.get_language(),
producer__in=producer_qs
).order_by(
"department_for_customer__tree_id",
"translations__long_name",
"order_average_weight",
).select_related(
'producer', 'department_for_customer'
).iterator()
product = next_row(products)
while product is not None:
row = [
(_("reference"), 10, product.reference if len(product.reference) < 36 else EMPTY_STRING,
NumberFormat.FORMAT_TEXT, False),
(_("department_for_customer"), 15,
product.department_for_customer.short_name if product.department_for_customer is not None else " ",
NumberFormat.FORMAT_TEXT, False),
(_("is_into_offer"), 7, _("Yes") if product.is_into_offer else _("No"),
NumberFormat.FORMAT_TEXT, False),
(_("wrapped"), 7, _("Yes") if product.wrapped else _("No"),
NumberFormat.FORMAT_TEXT, False),
(_("producer"), 15, product.producer.short_profile_name, NumberFormat.FORMAT_TEXT, False),
(_("long_name"), 60, product.get_long_name(), NumberFormat.FORMAT_TEXT, False),
(_("vat"), 10, product.get_vat_level_display(), NumberFormat.FORMAT_TEXT, False),
]
if row_num == 0:
worksheet_set_header(ws, row)
row_num += 1
for col_num in range(len(row)):
c = ws.cell(row=row_num, column=col_num)
c.value = "%s" % (row[col_num][ROW_VALUE])
c.style.number_format.format_code = row[col_num][ROW_FORMAT]
if row[col_num][ROW_BOX]:
c.style.borders.top.border_style = Border.BORDER_THIN
c.style.borders.bottom.border_style = Border.BORDER_THIN
c.style.borders.left.border_style = Border.BORDER_THIN
c.style.borders.right.border_style = Border.BORDER_THIN
else:
c.style.borders.bottom.border_style = Border.BORDER_HAIR
row_num += 1
product = next_row(products)
return wb
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),
(_("tax"), 10),
(_("rule of 3"), 10),
(_("comment"), 30),
(_("vat level"), 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 = "%s-%s" % (customer.short_basket_name, permanence)
else:
producers = Producer.objects.filter(
producerinvoice__permanence_id=permanence.id).distinct(
).iterator()
title1 = "%s" % permanence
else:
producers = Producer.objects.filter(
producerinvoice__permanence__permanence_date__year=year
).distinct().iterator()
title1 = "%s-%d" % (customer.short_basket_name, year)
else:
producers = Producer.objects.filter(id=producer.id).iterator()
title1 = "%s-%d" % (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_date__year=year,
producer_id=producer.id,
offer_item__translations__language_code=translation
.get_language()
).order_by(
"permanence_date", "permanence_id",
"customer__short_basket_name",
"offer_item__translations__preparation_sort_order"
).iterator()
else:
purchases = Purchase.objects.filter(
permanence__status__gte=PERMANENCE_INVOICED,
permanence_date__year=year,
customer_id=customer.id,
producer_id=producer.id,
offer_item__translations__language_code=translation
.get_language()
).order_by(
"permanence_date", "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 = 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 = "%s - %s" % (
purchase.get_long_name(),
department_for_customer_save__short_name
)
else:
c.value = "%s" % purchase.get_long_name()
c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
c = ws.cell(row=row_num, column=5)
c.value = 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
# if year is None:
# c.style.font.color = Color(Color.BLUE)
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%s*(H%s+I%s),2)' % (
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
# Asked by GAC HAMOIS : sell broken products...
# 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(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%s*%s' % (
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 = cap(purchase.comment, 100)
c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
c = ws.cell(row=row_num, column=13)
c.value = 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 = 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%s:J%s)' % (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%s:J%s),2)' % (
row_start_permanence, row_num)
purchase_price_all_purchase.append(
purchase_price_producer_purchase)
tax_producer_purchase = 'SUM(K%s:K%s)' % (
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 = "%s : %s %s" % (
_("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 = '=%s' % 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 = '=%s' % 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_date__year=year,
producer_id=producer.id,
offer_item__translations__language_code=translation
.get_language()
).order_by(
"permanence_date", "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_date__year=year,
customer_id=customer.id,
producer_id=producer.id,
offer_item__translations__language_code=translation
.get_language()
).order_by(
"permanence_date", "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 = 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 = "%s - %s" % (
purchase.get_long_name(),
department_for_customer_save__short_name
)
else:
c.value = "%s" % 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 = 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%s' % (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%s*(H%s+I%s),2)' % (
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%s*%s' % (
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 = cap(purchase.comment, 100)
c.style.number_format.format_code = NumberFormat.FORMAT_TEXT
c = ws.cell(row=row_num, column=13)
c.value = 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 = 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%s:J%s)' % (
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%s:J%s),2)' % (
row_start_permanence, row_num)
purchase_price_all_purchase.append(
purchase_price_producer_purchase)
tax_producer_purchase = 'SUM(K%s:K%s)' % (
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 = "%s : %s %s" % (
_("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 = '=%s' % 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 = '=%s' % 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 = "%s : %s" % (_('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 = '=%s' % "+".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 = '=%s' % "+".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
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),
(_("Current stock"), 10),
(repanier.apps.REPANIER_SETTINGS_CURRENCY_DISPLAY, 15),
]
producers = producers.iterator()
producer = next_row(producers)
wb, ws = new_landscape_a4_sheet(
wb,
_('Current stock'),
_('Current stock'),
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 = "%s" % product.producer
if len(product.reference) < 36:
product_reference = product.reference
show_column_reference = True
else:
product_reference = EMPTY_STRING
c = ws.cell(row=row_num, column=2)
c.value = 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 = "%s - %s" % (
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.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%s+F%s)*G%s,2)' % (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 = "%s" % (_("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%s:H%s)' % (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
def export_permanence_stock(permanence, deliveries_id=None, customer_price=False, wb=None, ws_customer_title=None):
if 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),
(_("Add 2 stock"), 10),
(_("Final stock"), 10),
(repanier.apps.REPANIER_SETTINGS_CURRENCY_DISPLAY, 15),
]
offer_items = OfferItem.objects.filter(
Q(
permanence_id=permanence.id,
manage_replenishment=True,
translations__language_code=translation.get_language()
) |
Q(
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 deliveries_id is not None:
for delivery_cpt, delivery_id in enumerate(deliveries_id):
ws_sc_name = cap('%d-%s' % (delivery_cpt, ws_customer_title), 31)
for sheet in wb.worksheets:
if ws_sc_name == sheet.title:
deliveries_ws.append(ws_sc_name)
break
else:
ws_sc_name = cap(slugify(ws_customer_title), 31)
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 = 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:
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 - offer_item.add_2_stock
stock = offer_item.stock
add_2_stock = offer_item.add_2_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 = 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 = "%s - %s" % (
offer_item.get_long_name(is_quantity_invoiced=True),
department_for_customer_save__short_name
)
else:
c.value = "%s" % offer_item.get_long_name(is_quantity_invoiced=True)
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=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:
# # Without any deleveriy point
# sum_value = "SUMIF('%s'!B2:B5000,B%s,'%s'!F2:F5000)" % \
# (ws_customer_title, row_num + 1, ws_customer_title)
# else:
sum_value = "+".join("SUMIF('%s'!B2:B5000,B%s,'%s'!F2:F5000)" % \
(delivery_ws, row_num + 1, delivery_ws)
for delivery_ws in deliveries_ws
)
c.value = "=%s" % 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%s-K%s+L%s' % (row_num + 1, row_num + 1, row_num + 1)
if not show_column_qty_ordered:
show_column_qty_ordered = (asked - min(asked, stock) + add_2_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%s*(E%s+F%s),2)' % (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%s,I%s)' % (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=11)
c.value = add_2_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(12) + str(row_num + 1), 'notEqual',
[str(add_2_stock)], True, wb,
None, None, yellowFill
)
if not show_column_add2stock:
show_column_add2stock = add_2_stock > 0
c = ws.cell(row=row_num, column=12)
c.value = '=I%s-K%s+L%s' % (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%s*(E%s+F%s),2)' % (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 = "%s %s" % (_("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%s:J%s)' % (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%s:N%s)' % (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 = "%s" % _("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
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
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(
Q(permanence_id=permanence.id,
manage_replenishment=True,
translations__language_code=translation.get_language())
| Q(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 - offer_item.add_2_stock
stock = offer_item.stock
add_2_stock = offer_item.add_2_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) + add_2_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=11)
c.value = add_2_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(12) + str(row_num + 1),
'notEqual', [str(add_2_stock)], True, wb, None,
None, yellowFill)
if not show_column_add2stock:
show_column_add2stock = add_2_stock > 0
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
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),
(_("Tax"), 10),
(_("Rule of 3"), 10),
(_("Comment"), 30),
(_("VAT level"), 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
# if year is None:
# c.style.font.color = Color(Color.BLUE)
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
# Asked by GAC HAMOIS : sell broken products...
# 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(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
