pyanaf/tests/test_xml.py

'''Created 16 May 2024 Levi'''

import unittest
from collections import defaultdict
from datetime import date

import openpyxl
import shutil
from lxml import etree
from rich import print # noqa


class Test(unittest.TestCase):

    def test_xml(self):
        vb_path = r'\\mozaiq\levente.marton\#mngm\Formulare\AUTORITATI\MINISTERUL FINANTELOR\Note explicative (finalizat)\2024\lista_firme.xlsm'
        backup_path = r'\\mozaiq\levente.marton\#mngm\Formulare\AUTORITATI\MINISTERUL FINANTELOR\Note explicative (finalizat)\2024\lista_firme.xlsm.bak'
        xml_path = r'c:\Users\DECEL\Bilant anual\Kat_Imob\2023\s1005_38063294_2023_12.xml'
        xml = etree.parse(xml_path)
        # root = xml.getroot()
        tree = xml.xpath('/xmlns:Bilant1005/*', namespaces={'xmlns': 'mfp:anaf:dgti:s1005:declaratie:v12'})
        # print(tree[0].attrib['F10_0041'])

        def F10() -> dict[int]:
            def_tree = defaultdict(lambda: 0)
            def_tree.update(tree[0].attrib)
            xpaths = {}
            xpaths['total_act_imob_C1'] = def_tree['F10_0041']
            xpaths['total_act_imob_C2'] = def_tree['F10_0042']
            xpaths['total_act_circ_C1'] = def_tree['F10_0091']
            xpaths['total_act_circ_C2'] = def_tree['F10_0092']
            xpaths['total_chelt_av_C1'] = def_tree['F10_0101']
            xpaths['total_chelt_av_C2'] = def_tree['F10_0102']
            xpaths['total_dat_tot_C1'] = sum([int(def_tree['F10_0131']), int(def_tree['F10_0161'])])
            xpaths['total_dat_tot_C2'] = sum([int(def_tree['F10_0132']), int(def_tree['F10_0162'])])
            xpaths['total_ven_av_C1'] = def_tree['F10_0181']
            xpaths['total_ven_av_C2'] = def_tree['F10_0182']
            xpaths['total_cap_soc_C1'] = def_tree['F10_0291']
            xpaths['total_cap_soc_C2'] = def_tree['F10_0292']
            xpaths['total_rezerve_C1'] = def_tree['F10_0371']
            xpaths['total_rezerve_C2'] = def_tree['F10_0372']
            xpaths['total_rez_reev_C1'] = def_tree['F10_0361']
            xpaths['total_rez_reev_C2'] = def_tree['F10_0362']
            xpaths['total_prof_rep_C1'] = def_tree['F10_0411']
            xpaths['total_prof_rep_C2'] = def_tree['F10_0412']
            xpaths['total_pierd_rep_C1'] = def_tree['F10_0421']
            xpaths['total_pierd_rep_C2'] = def_tree['F10_0422']
            xpaths['total_prof_cur_C1'] = def_tree['F10_0431']
            xpaths['total_prof_cur_C2'] = def_tree['F10_0432']
            xpaths['total_pierd_cur_C1'] = def_tree['F10_0441']
            xpaths['total_pierd_cur_C2'] = def_tree['F10_0442']
            xpaths['total_rep_prof_C1'] = def_tree['F10_0451']
            xpaths['total_rep_prof_C2'] = def_tree['F10_0452']
            xpaths['total_cap_propr_C1'] = def_tree['F10_0461']
            xpaths['total_cap_propr_C2'] = def_tree['F10_0462']
            for keys, values in xpaths.items():
                xpaths[keys] = int(values)
            # print(xpaths)
            return xpaths
        # F10()

        def F20() -> dict[int]:
            def_tree = defaultdict(lambda: 0)
            def_tree.update(tree[1].attrib)
            xpaths = {}
            xpaths['total_ven_exp_C1'] = def_tree['F20_0161']
            xpaths['total_ven_exp_C2'] = def_tree['F20_0162']
            xpaths['total_chelt_exp_C1'] = def_tree['F20_0421']
            xpaths['total_chelt_exp_C2'] = def_tree['F20_0422']
            xpaths['total_ven_fin_C1'] = def_tree['F20_0521']
            xpaths['total_ven_fin_C2'] = def_tree['F20_0522']
            xpaths['total_chelt_fin_C1'] = def_tree['F20_0591']
            xpaths['total_chelt_fin_C2'] = def_tree['F20_0592']
            xpaths['total_imp_C1'] = sum([int(def_tree['F20_0661']), int(def_tree['F20_0681'])])
            xpaths['total_imp_C2'] = sum([int(def_tree['F20_0662']), int(def_tree['F20_0682'])])
            for keys, values in xpaths.items():
                xpaths[keys] = int(values)
            # print(xpaths)
            return xpaths

        def F30() -> dict[int]:
            def_tree = defaultdict(lambda: 0)
            def_tree.update(tree[2].attrib)
            xpaths = {}
            xpaths['total_ven_exp_C1'] = def_tree['F30_1821']
            xpaths['total_ven_exp_C2'] = def_tree['F30_1822']
            xpaths['total_chelt_exp_C1'] = def_tree['F30_2081']
            xpaths['total_chelt_exp_C2'] = def_tree['F30_2082']
            xpaths['total_ven_fin_C1'] = def_tree['F30_2181']
            xpaths['total_ven_fin_C2'] = def_tree['F30_2182']
            xpaths['total_chelt_fin_C1'] = def_tree['F30_2251']
            xpaths['total_chelt_fin_C2'] = def_tree['F30_2252']
            xpaths['total_imp_C1'] = sum([int(def_tree['F30_2321']), int(def_tree['F30_2341'])])
            xpaths['total_imp_C2'] = sum([int(def_tree['F30_2322']), int(def_tree['F30_2342'])])
            for keys, values in xpaths.items():
                xpaths[keys] = int(values)
            # print(xpaths)
            return xpaths
        # F20()
        # return F10, F20
        firms = ['KATONA IMOBILIARE SRL']
        shutil.copy2(vb_path, backup_path)
        wb = openpyxl.load_workbook(vb_path, keep_vba=True)
        sheet = wb.active
        for firm in firms:
            for row in sheet.iter_rows(min_row=1, max_col=sheet.max_column, max_row=sheet.max_row):
                if firm in str(row[0].value):
                    print('hit:', firm)
                    row[9].value = F10()['total_cap_soc_C2']
                    row[10].value = F10()['total_prof_cur_C2']
                    row[16].value = F10()['total_pierd_cur_C2']
                    row[18].value = F30()['total_ven_exp_C2']
                    row[19].value = F30()['total_ven_fin_C2']
                    row[22].value = F30()['total_chelt_exp_C2']
                    row[23].value = F30()['total_chelt_fin_C2']
                    row[26].value = F30()['total_imp_C2']
                    row[28].value = F10()['total_act_imob_C2']
                    row[29].value = F10()['total_act_circ_C2']
                    row[30].value = F10()['total_chelt_av_C2']
                    row[32].value = F10()['total_cap_propr_C2']
                    row[33].value = F10()['total_dat_tot_C2']
                    row[35].value = F10()['total_ven_av_C2']
                    row[47].value = 12
                    row[48].value = date.today().year - 1
                    row[49].value = date.today().strftime('%d.%m.%Y')
        wb.save(vb_path)


if __name__ == "__main__":
    Test().test_xml()
    # print(F10())

    # unittest.main()