from xml.etree import ElementTree as ET
import xlsxwriter as xlsw
import openpyxl as xl
from .declaration import Declaration

class D390(Declaration):
    def __init__(self,
                 cui=None,
                 den=None,
                 addr=None,
                 month=None,
                 year=None,
                 control=None,
                 decl_name=None,
                 decl_func=None,
                 rect=None,
                 root=None,
                 n_operations=None,
                 bazaL=None,
                 bazaT=None,
                 bazaA=None,
                 bazaP=None,
                 bazaS=None,
                 bazaR=None,
                 total_baza=None,
                 decl_type=None):

        super().__init__(cui=cui,
                         den=den,
                         addr=addr,
                         month=month,
                         year=year,
                         control=control,
                         decl_name=decl_name,
                         decl_func=decl_func,
                         rect=rect,
                         root=root,
                         decl_type=decl_type)
        self.n_operations = n_operations,
        self.bazaL = bazaL,
        self.bazaT = bazaT,
        self.bazaA = bazaA,
        self.bazaP = bazaP,
        self.bazaS = bazaS,
        self.bazaR = bazaR,
        self.total_baza = total_baza

    def from_xml(self, fp):
        xml = ET.parse(fp)
        root = xml.getroot()
        # self.root = root
        decl_type = root.tag.split("}", 1)[-1]
        return D390(cui=root.attrib['cui'],
                    den=root.attrib['den'],
                    addr=root.attrib['adresa'],
                    month=int(root.attrib['luna']),
                    year=int(root.attrib['an']),
                    rect=int(root.attrib['d_rec']),
                    control=int(root.attrib['totalPlata_A']),
                    n_operations=int(root[0].attrib['nrOPI']),
                    bazaL=int(root[0].attrib['bazaL']),
                    bazaT=int(root[0].attrib['bazaT']),
                    bazaA=int(root[0].attrib['bazaA']),
                    bazaP=int(root[0].attrib['bazaP']),
                    bazaS=int(root[0].attrib['bazaS']),
                    bazaR=int(root[0].attrib['bazaR']),
                    total_baza=int(root[0].attrib['total_baza']),
                    root=root,
                    decl_type=decl_type)

    def from_xml_all(self, fp):
        xml = ET.parse(fp)
        root = xml.getroot()
        op_list = []
        for ch in root:
            if 'tip' in ch.attrib:
                try:
                    d = {
                        root.attrib['cui']: [
                            int(root.attrib['luna']),
                            int(root.attrib['an']),
                            ch.attrib['tip'],
                            ch.attrib['tara'],
                            ch.attrib['codO'],
                            ch.attrib['denO'],
                            ch.attrib['baza']
                        ]
                    }
                    op_list.append(d)
                except KeyError:
                    pass
        self.op_list = op_list
        return self.op_list

    @property
    def data(self):
        data = {
            self.cui:
                [
                    self.den[0], self.addr[0], self.month[0], self.year[0],
                    self.rect, self.control[0], self.n_operations[0],
                    self.bazaT[0], self.bazaL[0], self.bazaA[0],
                    self.bazaP[0], self.bazaS[0], self.bazaR[0], self.total_baza
                ]
        }
        self.dict_data = data
        return self.dict_data

    @property
    def headers(self):
        header = [h for h in self.root.attrib.keys()]
        ops = [h for h in self.root[0].attrib.keys()]
        del(ops[0])
        header.extend(ops)
        self.header = header
        return self.header

    def to_xlsx(self, xl_name, w_sheet_name, files, headers=[], obj={'type': None}, **kwargs):
        book = xlsw.Workbook(xl_name + '.xlsm')
        book.add_vba_project('./vbaProject.bin')
        sheet = book.add_worksheet(w_sheet_name)
        sheet2 = book.add_worksheet('all')
        row, row2, col = 1, 1, 0
        headformat = book.add_format({'bold': True})
        hcol, hcol2 = 0, 0
        for h in headers:
            sheet.write(1, hcol, h, headformat)
            hcol += 1
        for _ in range(8):
            sheet2.write(1, hcol2, 'x', headformat)
            hcol2 += 1
        for file in files:
            d = D390()  # obj['type']()
            d = d.from_xml(file)
            for i in d.data:
                row += 1
                sheet.write(row, col, i)
                print(f'write {row}', i)
                sheet.write_row(row, 1, d.data[i])
            d_all = d.from_xml_all(file)
            for el in d_all:
                for dict_ in el:
                    row2 += 1
                    sheet2.write(row2, col, dict_)
                    sheet2.write_row(row2, 1, el[dict_])
        for k, a in kwargs.items():
            if k == 'autofilter':
                sheet.autofilter(a[0], a[1], 100, 100)
                sheet2.autofilter(a[0], a[1], 1000, 1000)
            if k == 'freeze':
                sheet.freeze_panes(a[0], a[1])
                sheet2.freeze_panes(a[0], a[1])
        book.close()
        # self._format_sh1(xl_name)
        # self._format_sh2(xl_name)

    def _format_sh1(self, xl_name):
        wb = xl.load_workbook(xl_name + '.xlsx')
        sh = wb.worksheets[0]
        colG = sh['G3':'G{}'.format(sh.max_row)]
        colIO = sh['I3':'O{}'.format(sh.max_row)]
        for valuesG, valuesIO in zip(colG, colIO):
            for value in valuesG:
                if isinstance(value.value, str):
                    try:
                        num_value = float(value.value)
                        value.value = num_value
                    except ValueError as ve:
                        print(str(ve))
                value.number_format = '#,##0.00'
            for value in valuesIO:
                if isinstance(value.value, str):
                    try:
                        num_value = float(value.value)
                        value.value = num_value
                    except ValueError as ve:
                        print(str(ve))
                value.number_format = '#,##0.00'
        wb.save(xl_name + '.xlsx')
        wb.close()

    def _format_sh2(self, xl_name):
        wb = xl.load_workbook(xl_name + '.xlsx')
        sh = wb.worksheets[1]
        colH = sh['H3':'H{}'.format(sh.max_row)]
        for valuesG in colH:
            for value in valuesG:
                if isinstance(value.value, str):
                    try:
                        num_value = float(value.value)
                        value.value = num_value
                    except ValueError as ve:
                        print(str(ve))
                value.number_format = '#,##0.00'
        wb.save(xl_name + '.xlsx')
        wb.close()