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

class D300(Declaration):
    def __init__(self):
        super().__init__()

    def from_xml(self, fp):
        '''
        xml and pdf keys are not metching!!!
        '''
        xml = ET.parse(fp)
        root = xml.getroot()
        def_root = defaultdict(lambda: 0)
        def_root.update(root.attrib)
        self.def_root = def_root
        self.cui = def_root['cui']
        self.den = def_root['den']
        # addr=def_root['adresa'],
        self.month = int(def_root['luna'])
        self.year = int(def_root['an'])
        # rect=int(def_root['d_rec']),
        # bank=def_root['banca'],
        self.tip_decont = def_root['tip_decont']
        self.control = int(def_root['totalPlata_A'])
        self.li = int(def_root['R1_1'])
        self.pi = int(def_root['R3_1_1'])
        self.aib = int(def_root['R5_1_1'])
        self.tva_aib = int(def_root['R5_1_2'])
        self.ais = int(def_root['R7_1_1'])
        self.tva_ais = int(def_root['R7_1_2'])
        self.liv_19 = int(def_root['R9_1'])
        self.tvaL_19 = int(def_root['R9_2'])
        self.liv_9 = int(def_root['R10_1'])
        self.tvaL_9 = int(def_root['R10_2'])
        self.liv_5 = int(def_root['R11_1'])
        self.tvaL_5 = int(def_root['R11_2'])
        self.liv_txi = int(def_root['R13_1'])
        self.sc_cfdd = int(def_root['R14_1'])
        self.sc_fdd = int(def_root['R15_1'])
        self.reg_liv = int(def_root['R16_1'])
        self.tva_reg = int(def_root['R16_2'])
        self.tot_col = int(def_root['R17_1'])
        self.tva_tot_col = int(def_root['R17_2'])
        self.ach_19 = int(def_root['R22_1'])
        self.tvaA_19 = int(def_root['R22_2'])
        self.ach_9 = int(def_root['R23_1'])
        self.tvaA_9 = int(def_root['R23_2'])
        self.ach_5 = int(def_root['R24_1'])
        self.tvaA_5 = int(def_root['R24_2'])
        self.tot_ded = int(def_root['R32_2'])
        self.tot_plata = int(def_root['R41_2'])
        self.tot_rec = int(def_root['R42_2'])
        self.root = root
        self.decl_type = self.root.tag.split("}", 1)[-1]
        return self

    @property
    def data(self):
        data = {
            self.cui:
                [
                    self.den,
                    # addr=def_root['adresa'],
                    self.month,
                    self.year,
                    # rect=int(def_root['d_rec']),
                    # bank=def_root['banca'],
                    self.tip_decont,
                    self.control,
                    self.li,
                    self.pi,
                    self.aib,
                    self.tva_aib,
                    self.ais,
                    self.tva_ais,
                    self.liv_19,
                    self.tvaL_19,
                    self.liv_9,
                    self.tvaL_9,
                    self.liv_5,
                    self.tvaL_5,
                    self.liv_txi,
                    self.sc_cfdd,
                    self.sc_fdd,
                    self.reg_liv,
                    self.tva_reg,
                    self.tot_col,
                    self.tva_tot_col,
                    self.ach_19,
                    self.tvaA_19,
                    self.ach_9,
                    self.tvaA_9,
                    self.ach_5,
                    self.tvaA_5,
                    self.tot_ded,
                    self.tot_plata,
                    self.tot_rec
                ]
        }
        self.dict_data = data
        return self.dict_data

    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 = D300()  # 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()