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'
Example #5
0
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')
Example #6
0
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
Example #8
0
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
Example #9
0
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
Example #10
0
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
Example #13
0
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']}"
Example #15
0
def export_purchase(permanence=None,
                    year=None,
                    producer=None,
                    customer=None,
                    wb=None):
    yellowFill = Fill()
    yellowFill.start_color.index = 'FFEEEE11'
    yellowFill.end_color.index = 'FFEEEE11'
    yellowFill.fill_type = Fill.FILL_SOLID

    header = [
        (_("Format"), 5),
        (_("Id"), 10),
        (_("Date"), 15),
        (_("producer"), 15),
        (_("product"), 60),
        (_("customer"), 15),
        (_("quantity invoiced"), 10),
        (_("producer unit price"), 10),
        (_("deposit"), 10),
        (_("purchase price"), 10),
        (_("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
Example #16
0
def export_producer_stock(producers, customer_price=False, wb=None):
    yellowFill = Fill()
    yellowFill.start_color.index = 'FFEEEE11'
    yellowFill.end_color.index = 'FFEEEE11'
    yellowFill.fill_type = Fill.FILL_SOLID

    header = [
        (_("Id"), 5),
        (_("Producer"), 60),
        (_("Reference"), 20),
        (_("Product"), 60),
        (_("customer unit price") if customer_price else _("producer unit price"), 10),
        (_("Deposit"), 10),
        (_("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
Example #17
0
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
Example #18
0
def export_purchase(permanence=None, year=None, producer=None, customer=None, wb=None):
    yellowFill = Fill()
    yellowFill.start_color.index = 'FFEEEE11'
    yellowFill.end_color.index = 'FFEEEE11'
    yellowFill.fill_type = Fill.FILL_SOLID

    header = [
        (_("Format"), 5),
        (_("Id"), 10),
        (_("Date"), 15),
        (_("producer"), 15),
        (_("product"), 60),
        (_("customer"), 15),
        (_("quantity invoiced"), 10),
        (_("producer unit price"), 10),
        (_("deposit"), 10),
        (_("purchase price"), 10),
        (_("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
Example #19
0
def export_permanence_stock(permanence,
                            deliveries_id=(),
                            customer_price=False,
                            wb=None,
                            ws_customer_title=None):
    if settings.REPANIER_SETTINGS_STOCK and wb is not None:
        yellowFill = Fill()
        yellowFill.start_color.index = 'FFEEEE11'
        yellowFill.end_color.index = 'FFEEEE11'
        yellowFill.fill_type = Fill.FILL_SOLID

        header = [
            (_("Id"), 5),
            (_("OfferItem"), 5),
            (_("Reference"), 20),
            (_("Product"), 60),
            (_("Customer unit price")
             if customer_price else _("Producer unit price"), 10),
            (_("Deposit"), 10),
            (_("Asked"), 10),
            (_("Quantity ordered"), 10),
            (_("Initial stock"), 10),
            (repanier.apps.REPANIER_SETTINGS_CURRENCY_DISPLAY, 15),
            (_("Stock used"), 10),
            (_("Additional"), 10),
            (_("Remaining stock"), 10),
            (repanier.apps.REPANIER_SETTINGS_CURRENCY_DISPLAY, 15),
        ]
        offer_items = OfferItemWoReceiver.objects.filter(
            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
Example #20
0
def export_producer_stock(producers, customer_price=False, wb=None):
    yellowFill = Fill()
    yellowFill.start_color.index = 'FFEEEE11'
    yellowFill.end_color.index = 'FFEEEE11'
    yellowFill.fill_type = Fill.FILL_SOLID

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

        ws.column_dimensions[get_column_letter(1)].visible = False
        if not show_column_reference:
            ws.column_dimensions[get_column_letter(3)].visible = False
        producer = next_row(producers)
    return wb