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_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
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