def test_conditional_formatting_addCellIs_notEqual(self):
cf = ConditionalFormatting()
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
cf.addCellIs('U10:U18', 'notEqual', ['U$7'], True, self.workbook,
None, None, redFill)
cf.addCellIs('V10:V18', '!=', ['V$7'], True, self.workbook, None,
None, redFill)
rules = cf.cf_rules
assert 'U10:U18' in rules
assert len(cf.cf_rules['U10:U18']) == 1
assert rules['U10:U18'][0]['priority'] == 1
assert rules['U10:U18'][0]['type'] == 'cellIs'
assert rules['U10:U18'][0]['dxfId'] == 0
assert rules['U10:U18'][0]['operator'] == 'notEqual'
assert rules['U10:U18'][0]['formula'][0] == 'U$7'
assert rules['U10:U18'][0]['stopIfTrue'] == '1'
assert 'V10:V18' in rules
assert len(cf.cf_rules['V10:V18']) == 1
assert rules['V10:V18'][0]['priority'] == 2
assert rules['V10:V18'][0]['type'] == 'cellIs'
assert rules['V10:V18'][0]['dxfId'] == 1
assert rules['V10:V18'][0]['operator'] == 'notEqual'
assert rules['V10:V18'][0]['formula'][0] == 'V$7'
assert rules['V10:V18'][0]['stopIfTrue'] == '1'
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader',
'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font']['color'] == Color('FF9C0006')
assert cond_styles['font']['bold'] == False
assert cond_styles['font']['italic'] == False
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'][0] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(
wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader', 'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected', 'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font']['color'] == Color('FF9C0006')
assert cond_styles['font']['bold'] == False
assert cond_styles['font']['italic'] == False
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'][0] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_conditional_formatting_addCellIs_notEqual(self):
cf = ConditionalFormatting()
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
cf.addCellIs('U10:U18', 'notEqual', ['U$7'], True, self.workbook, None,
None, redFill)
cf.addCellIs('V10:V18', '!=', ['V$7'], True, self.workbook, None, None,
redFill)
rules = cf.cf_rules
assert 'U10:U18' in rules
assert len(cf.cf_rules['U10:U18']) == 1
assert rules['U10:U18'][0]['priority'] == 1
assert rules['U10:U18'][0]['type'] == 'cellIs'
assert rules['U10:U18'][0]['dxfId'] == 0
assert rules['U10:U18'][0]['operator'] == 'notEqual'
assert rules['U10:U18'][0]['formula'][0] == 'U$7'
assert rules['U10:U18'][0]['stopIfTrue'] == '1'
assert 'V10:V18' in rules
assert len(cf.cf_rules['V10:V18']) == 1
assert rules['V10:V18'][0]['priority'] == 2
assert rules['V10:V18'][0]['type'] == 'cellIs'
assert rules['V10:V18'][0]['dxfId'] == 1
assert rules['V10:V18'][0]['operator'] == 'notEqual'
assert rules['V10:V18'][0]['formula'][0] == 'V$7'
assert rules['V10:V18'][0]['stopIfTrue'] == '1'
def export_table(table, format='xlsx'):
""" Get a django_tables2 table and generate a spreadsheet
with its data and send the file to a response
"""
if format != 'xlsx':
return HttpResponseGone("Format not implemented")
wb = Workbook()
ws = wb.active
ws.title = table.verbose_name
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.fitToWidth = 1
# header style
header_fill = Fill()
header_fill.start_color.index = Color.BLUE
header_fill.end_color.index = Color.BLUE
header_fill.fill_type = Fill.FILL_SOLID
column_widths = []
col = 0
for column in table.columns:
c = ws.cell(row=0, column=col)
c.value = column.header.encode('utf8')
c.style.fill = header_fill
c.style.font.color.index = Color.WHITE
c.style.font.bold = True
c.style.font.size = 12
column_widths.append(len(unicode(c.value)))
col = col + 1
row = 1
for obj in table.rows:
col = 0
for value in obj:
c = ws.cell(row=row, column=col)
c.value = unicode(value)
if len(unicode(c.value)) > column_widths[col]:
column_widths[col] = len(c.value)
col = col + 1
row = row + 1
for i, column_width in enumerate(column_widths):
ws.column_dimensions[get_column_letter(i + 1)].width = column_width + 2
suffix = '.xlsx'
tf = tempfile.NamedTemporaryFile(suffix=suffix)
wb.save(tf.name)
filename = "{date:%Y-%m-%d}-{name}{suffix}".format(
date=date.today(),
name=slugify(ws.title),
suffix=suffix)
return send_file(
path=tf.name,
filename=filename,
mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
def parse_fills(root, color_index, skip_find=False):
"""Read in the list of fills"""
fill_list = []
if skip_find:
fills = root
else:
fills = root.find('{%s}fills' % SHEET_MAIN_NS)
count = 0
if fills is not None:
fillNodes = fills.findall('{%s}fill' % SHEET_MAIN_NS)
for fill in fillNodes:
# Rotation is unset
patternFill = fill.find('{%s}patternFill' % SHEET_MAIN_NS)
if patternFill is not None:
newFill = Fill()
newFill.fill_type = patternFill.get('patternType')
fgColor = patternFill.find('{%s}fgColor' % SHEET_MAIN_NS)
if fgColor is not None:
if fgColor.get('indexed') is not None and 0 <= int(
fgColor.get('indexed')) < len(color_index):
newFill.start_color.index = color_index[int(
fgColor.get('indexed'))]
elif fgColor.get('indexed') is not None:
# Invalid color - out of range of color_index, set to white
newFill.start_color.index = 'FFFFFFFF'
elif fgColor.get('theme') is not None:
if fgColor.get('tint') is not None:
newFill.start_color.index = 'theme:%s:%s' % (
fgColor.get('theme'), fgColor.get('tint'))
else:
newFill.start_color.index = 'theme:%s:' % fgColor.get(
'theme') # prefix color with theme
else:
newFill.start_color.index = fgColor.get('rgb')
bgColor = patternFill.find('{%s}bgColor' % SHEET_MAIN_NS)
if bgColor is not None:
if bgColor.get('indexed') is not None and 0 <= int(
bgColor.get('indexed')) < len(color_index):
newFill.end_color.index = color_index[int(
bgColor.get('indexed'))]
elif bgColor.get('indexed') is not None:
# Invalid color - out of range of color_index, set to white
newFill.end_color.index = 'FFFFFFFF'
elif bgColor.get('theme') is not None:
if bgColor.get('tint') is not None:
newFill.end_color.index = 'theme:%s:%s' % (
bgColor.get('theme'), bgColor.get('tint'))
else:
newFill.end_color.index = 'theme:%s:' % bgColor.get(
'theme') # prefix color with theme
elif bgColor.get('rgb'):
newFill.end_color.index = bgColor.get('rgb')
count += 1
fill_list.append(newFill)
return fill_list
def test_conditional_formatting_addCellIs_notBetween(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
self.worksheet.conditional_formatting.addCellIs('U10:U18', 'notBetween', ['U$7', 'U$8'], True, self.workbook,
None, None, redFill)
xml = write_worksheet(self.worksheet, None, None)
expected = '<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xml:space="preserve" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetPr><outlinePr summaryRight="1" summaryBelow="1"></outlinePr></sheetPr><dimension ref="A1:A1"></dimension><sheetViews><sheetView workbookViewId="0"><selection sqref="A1" activeCell="A1"></selection></sheetView></sheetViews><sheetFormatPr defaultRowHeight="15"></sheetFormatPr><sheetData></sheetData><conditionalFormatting sqref="U10:U18"><cfRule priority="1" dxfId="0" type="cellIs" stopIfTrue="1" operator="notBetween"><formula>U$7</formula><formula>U$8</formula></cfRule></conditionalFormatting></worksheet>'
diff = compare_xml(xml, expected)
assert diff is None, diff
def parse_fills(root, color_index, skip_find=False):
"""Read in the list of fills"""
fill_list = []
if skip_find:
fills = root
else:
fills = root.find('{%s}fills' % SHEET_MAIN_NS)
count = 0
if fills is not None:
fillNodes = fills.findall('{%s}fill' % SHEET_MAIN_NS)
for fill in fillNodes:
# Rotation is unset
patternFill = fill.find('{%s}patternFill' % SHEET_MAIN_NS)
if patternFill is not None:
newFill = Fill()
newFill.fill_type = patternFill.get('patternType')
fgColor = patternFill.find('{%s}fgColor' % SHEET_MAIN_NS)
if fgColor is not None:
if fgColor.get('indexed') is not None and 0 <= int(fgColor.get('indexed')) < len(color_index):
newFill.start_color.index = color_index[int(fgColor.get('indexed'))]
elif fgColor.get('indexed') is not None:
# Invalid color - out of range of color_index, set to white
newFill.start_color.index = 'FFFFFFFF'
elif fgColor.get('theme') is not None:
if fgColor.get('tint') is not None:
newFill.start_color.index = 'theme:%s:%s' % (fgColor.get('theme'), fgColor.get('tint'))
else:
newFill.start_color.index = 'theme:%s:' % fgColor.get('theme') # prefix color with theme
else:
newFill.start_color.index = fgColor.get('rgb')
bgColor = patternFill.find('{%s}bgColor' % SHEET_MAIN_NS)
if bgColor is not None:
if bgColor.get('indexed') is not None and 0 <= int(bgColor.get('indexed')) < len(color_index):
newFill.end_color.index = color_index[int(bgColor.get('indexed'))]
elif bgColor.get('indexed') is not None:
# Invalid color - out of range of color_index, set to white
newFill.end_color.index = 'FFFFFFFF'
elif bgColor.get('theme') is not None:
if bgColor.get('tint') is not None:
newFill.end_color.index = 'theme:%s:%s' % (bgColor.get('theme'), bgColor.get('tint'))
else:
newFill.end_color.index = 'theme:%s:' % bgColor.get('theme') # prefix color with theme
elif bgColor.get('rgb'):
newFill.end_color.index = bgColor.get('rgb')
count += 1
fill_list.append(newFill)
return fill_list
def parse_fills(root, color_index, skip_find=False):
"""Read in the list of fills"""
fill_list = []
if skip_find:
fills = root
else:
fills = root.find("{%s}fills" % SHEET_MAIN_NS)
count = 0
if fills is not None:
fillNodes = fills.findall("{%s}fill" % SHEET_MAIN_NS)
for fill in fillNodes:
# Rotation is unset
patternFill = fill.find("{%s}patternFill" % SHEET_MAIN_NS)
if patternFill is not None:
newFill = Fill()
newFill.fill_type = patternFill.get("patternType")
fgColor = patternFill.find("{%s}fgColor" % SHEET_MAIN_NS)
if fgColor is not None:
if fgColor.get("indexed") is not None and 0 <= int(fgColor.get("indexed")) < len(color_index):
newFill.start_color.index = color_index[int(fgColor.get("indexed"))]
elif fgColor.get("indexed") is not None:
# Invalid color - out of range of color_index, set to white
newFill.start_color.index = "FFFFFFFF"
elif fgColor.get("theme") is not None:
if fgColor.get("tint") is not None:
newFill.start_color.index = "theme:%s:%s" % (fgColor.get("theme"), fgColor.get("tint"))
else:
newFill.start_color.index = "theme:%s:" % fgColor.get("theme") # prefix color with theme
else:
newFill.start_color.index = fgColor.get("rgb")
bgColor = patternFill.find("{%s}bgColor" % SHEET_MAIN_NS)
if bgColor is not None:
if bgColor.get("indexed") is not None and 0 <= int(bgColor.get("indexed")) < len(color_index):
newFill.end_color.index = color_index[int(bgColor.get("indexed"))]
elif bgColor.get("indexed") is not None:
# Invalid color - out of range of color_index, set to white
newFill.end_color.index = "FFFFFFFF"
elif bgColor.get("theme") is not None:
if bgColor.get("tint") is not None:
newFill.end_color.index = "theme:%s:%s" % (bgColor.get("theme"), bgColor.get("tint"))
else:
newFill.end_color.index = "theme:%s:" % bgColor.get("theme") # prefix color with theme
elif bgColor.get("rgb"):
newFill.end_color.index = bgColor.get("rgb")
count += 1
fill_list.append(newFill)
return fill_list
def parse_fills(root, xmlns, color_index):
"""Read in the list of fills"""
fill_list = []
fills = root.find(QName(xmlns, 'fills').text)
count = 0
if fills is not None:
fillNodes = fills.findall(QName(xmlns, 'fill').text)
for fill in fillNodes:
newFill = Fill()
# Rotation is unset
patternFill = fill.find(QName(xmlns, 'patternFill').text)
newFill.fill_type = patternFill.get('patternType')
fgColor = patternFill.find(QName(xmlns, 'fgColor').text)
if fgColor is not None:
if fgColor.get('indexed') is not None and 0 <= int(
fgColor.get('indexed')) < len(color_index):
newFill.start_color.index = color_index[int(
fgColor.get('indexed'))]
elif fgColor.get('theme') is not None:
if fgColor.get('tint') is not None:
newFill.start_color.index = 'theme:%s:%s' % (
fgColor.get('theme'), fgColor.get('tint'))
else:
newFill.start_color.index = 'theme:%s:' % fgColor.get(
'theme') # prefix color with theme
else:
newFill.start_color.index = fgColor.get('rgb')
bgColor = patternFill.find(QName(xmlns, 'bgColor').text)
if bgColor is not None:
if bgColor.get('indexed') is not None and 0 <= int(
bgColor.get('indexed')) < len(color_index):
newFill.end_color.index = color_index[int(
bgColor.get('indexed'))]
elif bgColor.get('theme') is not None:
if bgColor.get('tint') is not None:
newFill.end_color.index = 'theme:%s:%s' % (
bgColor.get('theme'), bgColor.get('tint'))
else:
newFill.end_color.index = 'theme:%s:' % bgColor.get(
'theme') # prefix color with theme
elif bgColor.get('rgb'):
newFill.end_color.index = bgColor.get('rgb')
count += 1
fill_list.append(newFill)
return fill_list
def test_conditional_formatting_addDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
dxfId = cf.addDxfStyle(self.workbook, font, borders, fill)
assert dxfId == 0
dxfId = cf.addDxfStyle(self.workbook, None, None, fill)
assert dxfId == 1
assert len(self.workbook.style_properties['dxf_list']) == 2
def test_conditional_formatting_addDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
dxfId = cf.addDxfStyle(self.workbook, font, borders, fill)
assert dxfId == 0
dxfId = cf.addDxfStyle(self.workbook, None, None, fill)
assert dxfId == 1
assert len(self.workbook.style_properties['dxf_list']) == 2
def parse_fills(root, xmlns, color_index):
"""Read in the list of fills"""
fill_list = []
fills = root.find(QName(xmlns, 'fills').text)
count = 0
if fills is not None:
fillNodes = fills.findall(QName(xmlns, 'fill').text)
for fill in fillNodes:
newFill = Fill()
# Rotation is unset
patternFill = fill.find(QName(xmlns, 'patternFill').text)
if patternFill is not None:
newFill.fill_type = patternFill.get('patternType')
fgColor = patternFill.find(QName(xmlns, 'fgColor').text)
if fgColor is not None:
if fgColor.get('indexed') is not None and 0 <= int(fgColor.get('indexed')) < len(color_index):
newFill.start_color.index = color_index[int(fgColor.get('indexed'))]
elif fgColor.get('theme') is not None:
if fgColor.get('tint') is not None:
newFill.start_color.index = 'theme:%s:%s' % (fgColor.get('theme'), fgColor.get('tint'))
else:
newFill.start_color.index = 'theme:%s:' % fgColor.get('theme') # prefix color with theme
else:
newFill.start_color.index = fgColor.get('rgb')
bgColor = patternFill.find(QName(xmlns, 'bgColor').text)
if bgColor is not None:
if bgColor.get('indexed') is not None and 0 <= int(bgColor.get('indexed')) < len(color_index):
newFill.end_color.index = color_index[int(bgColor.get('indexed'))]
elif bgColor.get('theme') is not None:
if bgColor.get('tint') is not None:
newFill.end_color.index = 'theme:%s:%s' % (bgColor.get('theme'), bgColor.get('tint'))
else:
newFill.end_color.index = 'theme:%s:' % bgColor.get('theme') # prefix color with theme
elif bgColor.get('rgb'):
newFill.end_color.index = bgColor.get('rgb')
count += 1
fill_list.append(newFill)
return fill_list
def test_conditional_formatting_addDxfStyle(self):
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
dxfId = self.worksheet.conditional_formatting.addDxfStyle(self.workbook, font, borders, fill)
assert dxfId == 0
dxfId = self.worksheet.conditional_formatting.addDxfStyle(self.workbook, None, None, fill)
assert dxfId == 1
assert len(self.workbook.style_properties['dxf_list']) == 2
assert repr(self.workbook.style_properties['dxf_list'][0]) == "{'font': ['Arial':12:True:False:False:False:'single':False:'FF000000'], 'border': ['none':'FF000000':'none':'FF000000':'thin':'FF808000':'thin':'FF000000':'none':'FF000000':0:'none':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000':'none':'FF000000'], 'fill': ['solid':0:'FFEE1111':'FFEE1111']}"
assert repr(self.workbook.style_properties['dxf_list'][1]) == "{'fill': ['solid':0:'FFEE1111':'FFEE1111']}"
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_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),
]
if producer is None:
if permanence is not None:
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:
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:
if customer is None:
purchases = Purchase.objects.filter(
permanence__status__gte=PERMANENCE_DONE,
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_DONE,
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
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
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
delta = 5
for col_num in range(5):
c = ws.cell(row=row_num, column=delta + col_num)
c.style.borders.bottom.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, 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 + 1), 'notEqual',
[str(purchases_price)], True, wb,
None, None, yellowFill
)
c = ws.cell(row=row_num, 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.bottom.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.
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:
if customer is None:
purchases = Purchase.objects.filter(
permanence__status__gte=PERMANENCE_DONE,
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_DONE,
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.bottom.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
delta = 5
for col_num in range(5):
c = ws.cell(row=row_num, column=delta + col_num)
c.style.borders.bottom.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, 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 + 1), 'notEqual',
[str(offer_items_price)], True, wb,
None, None, yellowFill
)
c = ws.cell(row=row_num, column=0)
c.value = "D"
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.bottom.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.bottom.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
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_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