'''
Created on Mar 11, 2020 @author: Levi
'''

from parser_intrastat import *   # @UnusedWildImport @UnresolvedImport

def parse_packing(articles, export, partners):
    ib = xl.load_workbook(articles)
    isheet = ib.worksheets[0]
    cb = xl.load_workbook(export)
    csheet = cb.worksheets[0]
    pb = xl.load_workbook(partners)
    psheet = pb.worksheets[0]
    pack_book = xl.Workbook()
    pack_book.create_sheet('packing', 0)
    pack_sheet = pack_book.worksheets[0]
    header_create(pack_sheet)
    for n, p in enumerate(csheet.iter_rows(min_row=2, max_col=14, max_row=csheet.max_row - 1)):
        pack_sheet[f'A{n+2}'] = p[4].value  # noqa E:226
        pack_sheet[f'B{n+2}'] = p[0].value  # noqa E:226
        pack_sheet[f'L{n+2}'] = p[9].value  # noqa E:226
        pack_sheet[f'N{n+2}'] = p[11].value  # noqa E:226
        pack_sheet[f'O{n+2}'] = p[10].value  # noqa E:226
        pack_sheet[f'P{n+2}'] = p[12].value  # noqa E:226
        pack_sheet[f'R{n+2}'] = p[13].value  # noqa E:226

        for c in psheet.iter_rows(min_row=2, max_col=9, max_row=psheet.max_row):
            if pack_sheet[f'B{n+2}'].value == c[1].value:  # noqa E:226
                pack_sheet[f'C{n+2}'] = c[2].value  # noqa E:226
                pack_sheet[f'D{n+2}'] = c[3].value  # noqa E:226
                pack_sheet[f'E{n+2}'] = c[4].value  # noqa E:226
                pack_sheet[f'F{n+2}'] = c[5].value  # noqa E:226
                pack_sheet[f'G{n+2}'] = c[6].value  # noqa E:226
                pack_sheet[f'H{n+2}'] = c[7].value  # noqa E:226
                pack_sheet[f'I{n+2}'] = c[8].value  # noqa E:226

        for i in isheet.iter_rows(min_row=2, max_col=10, max_row=isheet.max_row):
            if pack_sheet[f'L{n+2}'].value == i[1].value:  # noqa E:226
                pack_sheet[f'K{n+2}'] = i[3].value  # noqa E:226
                pack_sheet[f'Q{n+2}'] = i[9].value * pack_sheet[f'O{n+2}'].value  # noqa E:226
                pack_sheet[f'U{n+2}'] = i[6].value * pack_sheet[f'O{n+2}'].value  # noqa E:226
                pack_sheet[f'V{n+2}'] = i[7].value * pack_sheet[f'O{n+2}'].value  # noqa E:226
    format_after(pack_sheet, ref=f'A1:Y{pack_sheet.max_row}')  # noqa E:226
    pack_book.save('packing.xlsx')


if __name__ == '__main__':
    parse_packing('intrastat_art.xlsx', 'Export.xlsx', 'intrastat_part.xlsx')