    
import csv
import math
import os.path
import operator
import sys
import re
import asyncio

from qgis.PyQt.QtCore import QSettings, QTranslator, QCoreApplication, QDir
from qgis.PyQt.QtGui import QIcon
from qgis.PyQt.QtWidgets import QAction, QFileDialog
from qgis.core import QgsProject, Qgis
from osgeo import ogr
import pandas as pd
import numpy as np
import xlrd
import webbrowser
from collections import OrderedDict
import simplejson as json
import json
from json import *
from itertools import groupby

# Import the code for the dialog
import os.path
import processing
import pathlib
from pathlib import Path
from qgis.core import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from qgis.core import QgsVectorFileWriter
import qgis.utils
from qgis.utils import iface
from PyQt5.QtWidgets import QApplication
from qgis.core import QgsApplication, QgsProject
from qgis.gui import QgsMapCanvas, QgsLayerTreeMapCanvasBridge

def open_link_with_browser(url):
    QDesktopServices.openUrl(QUrl(url))

def open_PDF(path):
    webbrowser.open_new(path)

def config(conf_dir,refer):
    with open(conf_dir, 'w') as f:
        f.write(refer)

def checkInput(self,attr_name):
    if self.path_solution != '':
        conf_dir = str(Path(__file__).parent.absolute()) + '/data/config.txt'
        f = open(conf_dir, 'r')
        conf_code = f.read()
        matinh = int(conf_code)

        in_shp = self.path_solution
        driver = ogr.GetDriverByName("ESRI Shapefile")
        dataSource = driver.Open(in_shp, 0)
        layer = dataSource.GetLayer()
        file_kq = []
        attr = []
        field_chuan = attr_name
        field_names = [field.name for field in layer.schema]
        field_lower = [name.lower() for name in field_names]
        for x in field_chuan:
            if(x not in field_lower):
                file_kq.append(x)
        
        if len(file_kq) > 0:
            self.iface.messageBar().pushMessage("Dữ liệu đầu vào không hợp lệ", level=Qgis.Warning, duration=5)
            self.buttonBox.setEnabled(False)
        else:
            for feature in layer:
                feat = feature['matinh']
                attr.append(feat)
            if matinh not in attr:
                if matinh == 0:
                    self.buttonBox.setEnabled(True)
                else:
                    self.iface.messageBar().pushMessage("Dữ liệu đầu vào không hợp lệ", level=Qgis.Warning, duration=5)
                    self.buttonBox.setEnabled(False)
            else:
                self.buttonBox.setEnabled(True)

def checkExcel (self,col_name):
    if self.path_solution != '':
        in_exc = self.path_solution
        col_kq = []
        col_chuan = col_name
        col_exc = pd.read_excel(in_exc,skiprows=0).columns
        col_lower = [name.lower() for name in col_exc]
        for x in col_chuan:
            if(x not in col_lower):
                col_kq.append(x)
        
        if len(col_kq) > 0:
            self.iface.messageBar().pushMessage("Dữ liệu đầu vào không hợp lệ", level=Qgis.Warning, duration=5)
            self.buttonBox.setEnabled(False)
        else:
            self.buttonBox.setEnabled(True)

def docdstinh():
    dir1 = str(Path(__file__).parent.absolute()) + '\data\provincelist.json'
    with open(dir1) as f1:
        datax1 = json.load(f1)
        return datax1
def docdshuyen():
    dir2 = str(Path(__file__).parent.absolute()) + '\data\districtlist.json'
    with open(dir2) as f2:
        datax2 = json.load(f2)
        return datax2
        
def docdsxa():
    dir3 = str(Path(__file__).parent.absolute()) + '\data\communelist.json'
    with open(dir3) as f3:
        datax3 = json.load(f3)
        return datax3

def query(syntax):
    sql = """(SELECT ROW_NUMBER() OVER() as tt, 0 as id, province.province_code matinh, district.district_code mahuyen, plot.commune_code maxa, commune.name as xa, """
    sql += """plot.compt_code tk, plot.sub_compt_code khoanh, plot.plot_code lo, plot.parcel_code thuad, plot.map_sheet tobando, plot.village ddanh, plot.area dtich, """
    sql += """plot.forest_org_code nggocr, ft.abbreviation as ldlr, plot.forest_type_code maldlr, _formis_get_tree_spec_abbr(plot.tree_spec_code) sldlr, plot.planting_year namtr, """
    sql += """plot.avg_year_canopy captuoi, 0 as ktan, plot.plant_state_code thanhrung, plot.volume_per_ha mgo, plot.volume_per_plot mgolo, plot.stem_per_ha mtn, plot.stem_per_plot mtnlo, """
    sql += """plot.p_forest_org_code nggocrt, plot.site_cond_code as lapdia, up.forest_func_main_code malr3, up.abbreviation mdsd, plot.forest_func_sub_code mamdsd, fuo.actor_type_code dtuong, """
    sql += """fuo.actor_name churung, plot.actor_id machur, plot.conflict_sit_code trchap, plot.land_use_cert_code quyensd, plot.land_use_terune thoihansd, plot.prot_contr_code khoan, """
    sql += """plot.forest_use_sit_code nqh, fuonk.actor_name AS nguoink, fuotrch.actor_name AS nguoitrch, plot.actor_id_conflict mangnk, plot.actor_id_prot mangtrch, plot.nar_for_org_code ngsinh, """
    sql += """0 as kd, 0 as vd, 0 as capkd, 0 as capvd, plot.old_plot_code locu, plot.pos_status_code vitrithua, province.name AS tinh, district.name AS huyen, plot.geom FROM plot plot """
    sql += """LEFT JOIN plot_position_status pps ON plot.pos_status_code::numeric = pps.pos_status_code  LEFT JOIN commune commune ON plot.commune_code = commune.commune_code """
    sql += """LEFT JOIN district district ON commune.district_code = district.district_code LEFT JOIN province province ON district.province_code = province.province_code """
    sql += """LEFT JOIN protection_contract pc ON plot.prot_contr_code = pc.prot_contr_code and pc.lang= 'vi' LEFT JOIN forest_origin foc ON plot.forest_org_code = foc.forest_org_code and foc.lang= 'vi' """
    sql += """LEFT JOIN forest_type ft ON plot.forest_type_code = ft.forest_type_code and ft.lang= 'vi' LEFT JOIN forest_use_situation fus ON plot.forest_use_sit_code = fus.forest_use_sit_code and fus.lang= 'vi' """
    sql += """LEFT JOIN p_forest_origin pfo ON plot.p_forest_org_code = pfo.p_forest_org_code and pfo.lang= 'vi' LEFT JOIN conflict_situation ls ON plot.conflict_sit_code = ls.conflict_sit_code and ls.lang= 'vi' """
    sql += """LEFT JOIN natural_forest_origin ps ON plot.nar_for_org_code = ps.nar_for_org_code and ps.lang= 'vi' LEFT JOIN site_condition scc ON plot.site_cond_code = scc.site_cond_code and scc.lang= 'vi' """
    sql += """LEFT JOIN forest_function up ON plot.forest_func_sub_code = up.forest_func_sub_code and up.lang= 'vi' LEFT JOIN land_use_certificate urs ON plot.land_use_cert_code = urs.land_use_cert_code and urs.lang= 'vi' """
    sql += """LEFT JOIN forest_actor fuo ON fuo.commune_code = plot.commune_code AND fuo.actor_id = plot.actor_id LEFT JOIN forest_actor fuotrch ON fuotrch.commune_code = plot.commune_code AND fuotrch.actor_id = plot.actor_id_conflict """
    sql += """LEFT JOIN forest_actor fuonk ON fuonk.commune_code = plot.commune_code AND fuonk.actor_id = plot.actor_id_prot where plot.commune_code in (select commune_code from commune where district_code in ( select district_code from district where """+ syntax +""")))"""
    return sql

def update_malr3(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        ma3lr = feature['mdsd']
        if ma3lr in ("PHDN","PHCS","PHCC","PHMT"):
            feature.SetField("malr3", 1)
        elif ma3lr in ("VQG","BTTN","NCKH","VHCQ"):
            feature.SetField("malr3", 2)
        elif ma3lr in ("SXGL","SXGN","SXTN","MDK"):
            feature.SetField("malr3", 3)
        else:
            pass
        layer.SetFeature(feature)

def update_MALDLR33(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        feature['maldlr'] = get_MALDLR33(feature['ldlr'])
        layer.SetFeature(feature)

def get_MALDLR33(ldlr):
    data = docmaldlr33()   
    for x in data:
        if x["ldlr"] == ldlr:
            maldlr = x["maldlr"]
            return maldlr

def delete_fields(inputPath,outputPath):
    del_fields = ["plot_uuid","desc","change_typ","date_time","area_affec","volume_aff","stems_no_a", "method_pla","density_wo", "density_ba","stems_no_t","stems_no_b","desc_monit", "change_t_1", "ts_created", "ts_modifie","avg_year_c"]
    processing.run('qgis:deletecolumn', {'INPUT':inputPath,'COLUMN':del_fields,'OUTPUT': outputPath})


def delete_watershed_fields(inputPath,outputPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inputPath, 0)
    layer = dataSource.GetLayer()
    del_fields = []
    field_chuan = ['tt','id','maxa','mahuyen','matinh','xa','huyen','tinh','tk','khoanh','lo','thuad','tobando','ddanh','dtich','nggocr','maldlr','ldlr','sldlr','namtr','captuoi','ktan','thanhrung','mgo','mgolo','mtn','mtnlo','nggocrt','lapdia','malr3','mdsd','mamdsd','dtuong','machur','churung','trchap','quyensd','thoihansd','khoan','nqh','nguoink','nguoitrch','mangnk','mangtrch','ngsinh','kd','vd','capkd','capvd','locu','vitrithua','vungchitra','chitra','dgia','thanhtien','maluuvuc','k0','k1','k2','k3','k4','khuvuc','mucct','dtichct','dtichgk','cmt','gcn','nguq','ngaycap','cql','dtuongnk','dt2']
    field_names = [field.name for field in layer.schema]
    field_lower = [name.lower() for name in field_names]
    for x in field_lower:
        if(x not in field_chuan):
            del_fields.append(x)
    processing.run('qgis:deletecolumn', {'INPUT':inputPath,'COLUMN':del_fields,'OUTPUT': outputPath})

def add_fields(layer):
    layer.isValid()
    layer_provider = layer.dataProvider()
    layer_provider.addAttributes([QgsField("tt", QVariant.Double,'',7,0),
                            QgsField("id", QVariant.Double,'',7,0),
                            QgsField("tinh", QVariant.String,'',50),
                            QgsField("huyen", QVariant.String,'',50),
                            QgsField("xa", QVariant.String,'',50),
                            QgsField("matinh", QVariant.Double,'',2,0),
                            QgsField("mahuyen", QVariant.Double,'',3,0),
                            QgsField("churung", QVariant.String,'',50),
                            QgsField("nguoink", QVariant.String,'',50),
                            QgsField("nguoitrch", QVariant.String,'',50),
                            QgsField("kd", QVariant.Double,'',10,2),
                            QgsField("vd", QVariant.Double,'',11,2),
                            QgsField("capkd", QVariant.Double,'',5,0),
                            QgsField("capvd", QVariant.Double,'',5,0),
                            QgsField("ldlr", QVariant.String,'',10),
                            QgsField("sldlr", QVariant.String,'',120),
                            QgsField("mdsd", QVariant.String,'',4),
                            QgsField("malr3", QVariant.Double,'',2,0),
                            QgsField("ktan", QVariant.Double,'',2,0),
                            QgsField("captuoi", QVariant.Double,'',2,0)])

def add_fields2(layer):
    layer.isValid()
    layer_provider = layer.dataProvider()
    layer_provider.addAttributes([QgsField("tt", QVariant.Double,'',7,0),
                            QgsField("ldlr", QVariant.String,'',10),
                            QgsField("sldlr", QVariant.String,'',120),
                            QgsField("mdsd", QVariant.String,'',4),
                            QgsField("malr3", QVariant.Double,'',2,0),
                            QgsField("ktan", QVariant.Double,'',2,0)])

def rename_fields(layer):
    old_fields = ['commune_co','compt_code','sub_compt_','plot_code','parcel_cod','map_sheet','village','area','forest_org','forest_typ','tree_spec_','planting_y','p_forest_o','plant_stat','volume_per','stem_per_h','volume_p_1','stem_per_p','site_cond_','forest_fun','actor_type','actor_id','conflict_s','land_use_c','land_use_t','prot_contr','forest_use','actor_id_p','actor_id_c','nar_for_or','old_plot_c','pos_status']
    new_field = ['maxa','tk','khoanh','lo','thuad','tobando','ddanh','dtich','nggocr','maldlr','maloaicay','namtr','nggocrt','thanhrung','mgo','mtn','mgolo','mtnlo','lapdia','mamdsd','dtuong','machur','trchap','quyensd','thoihansd','khoan','nqh','mangnk','mangtrch','ngsinh','locu','vitrithua']
    current_field = [x.name() for x in layer.fields()]
    for index, old_field in enumerate(old_fields):
        if old_field in current_field:
            with edit(layer):
                column = layer.fields().indexFromName(old_field)
                layer.renameAttribute(column,new_field[index])

def rename_fields2(layer):
    old_fields = ['fid','province_c', 'province_n','district_c','district_n','commune_co','commune_na','plot_code','sub_compt_','compt_code','parcel_cod','village','area','forest_org','forest_typ','planting_y','avg_year_c','p_forest_o','plant_stat','volume_per','stem_per_h','volume_p_1','stem_per_p','site_cond_','forest_fun','actor_type','actor_id','actor_name','actor_id_c','actor_id_p','tree_spec_','prot_contr','forest_use']
    new_field = ['id','matinh','tinh','mahuyen','huyen','maxa','xa','lo','khoanh','tk','thuad','ddanh','dtich','nggocr','maldlr','namtr','captuoi','nggocrt','thanhrung','mgo','mtn','mgolo','mtnlo','lapdia','mamdsd','dtuong','machur','churung','mangtrch','mangnk','maloaicay','khoan','nqh']
    current_field = [x.name() for x in layer.fields()]
    for index, old_field in enumerate(old_fields):
        if old_field in current_field:
            with edit(layer):
                column = layer.fields().indexFromName(old_field)
                layer.renameAttribute(column,new_field[index])


def join_fields(output_Path,churung_Path):

    df_churung = pd.read_excel(churung_Path)
    ds_churung = json.loads(df_churung.to_json(orient='records'))

    with open(str(Path(__file__).parent.absolute()) +"/data/ds_tinhhuyenxa.json", 'r') as myfile:
        data=myfile.read()
    ds_tinhhuyenxa = json.loads(data)

    with open(str(Path(__file__).parent.absolute()) +"/data/maldlrtt33.json", 'r') as myfile:
        data=myfile.read()
    ds_ldlr = json.loads(data)

    with open(str(Path(__file__).parent.absolute()) +"/data/ds_loaicay.json", 'r') as myfile:
        data=myfile.read()
    ds_loaicay = json.loads(data)

    with open(str(Path(__file__).parent.absolute()) +"/data/ds_mdsd.json", 'r') as myfile:
        data=myfile.read()
    ds_mdsd = json.loads(data)

    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(output_Path, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        maxa = str(feature.GetField("maxa"))
        xa = [x for x in ds_tinhhuyenxa if x['maxa']==maxa]
        if len(xa) >0:
            feature.SetField("xa", xa[0]['xa'])
            feature.SetField("tinh", xa[0]['tinh'])
            feature.SetField("matinh", xa[0]['matinh'])
            feature.SetField("huyen", xa[0]['huyen'])
            feature.SetField("mahuyen", xa[0]['mahuyen'])

        maldlr = feature.GetField("maldlr")
        ldlr = [x for x in ds_ldlr if x['maldlr']== maldlr]
        if len(ldlr) >0:
            feature.SetField("ldlr", ldlr[0]['ldlr'])

        maloaicay = str(feature.GetField("maloaicay"))
        sldlr = [x for x in ds_loaicay if x['maloaicay']== maloaicay]

        if len(sldlr) >0:
            feature.SetField("sldlr", sldlr[0]['sldlr'])
            feature.SetField("captuoi", sldlr[0]['captuoi'])

        mamdsd = feature.GetField("mamdsd")
        mdsd = [x for x in ds_mdsd if x['mamdsd']== mamdsd]

        if len(mdsd) > 0:
            feature.SetField("mdsd", mdsd[0]['mdsd'])
            feature.SetField("malr3", mdsd[0]['malr3'])

        machur = feature.GetField("machur")
        maxa = feature.GetField("maxa")

        churung = [x for x in ds_churung if x['commune_code']== maxa and x['actor_id']== machur]
        if len(churung)>0:
            feature.SetField("churung",churung[0]['actor_name'])
        layer.SetFeature(feature)

def join_fields2(output_Path):

    with open(str(Path(__file__).parent.absolute()) +"/data/maldlrtt33.json", 'r') as myfile:
        data=myfile.read()
    ds_ldlr = json.loads(data)

    with open(str(Path(__file__).parent.absolute()) +"/data/ds_loaicay.json", 'r') as myfile:
        data=myfile.read()
    ds_loaicay = json.loads(data)

    with open(str(Path(__file__).parent.absolute()) +"/data/ds_mdsd.json", 'r') as myfile:
        data=myfile.read()
    ds_mdsd = json.loads(data)

    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(output_Path, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        maldlr = feature.GetField("maldlr")
        ldlr = [x for x in ds_ldlr if x['maldlr']== maldlr]
        if len(ldlr) >0:
            feature.SetField("ldlr", ldlr[0]['ldlr'])

        maloaicay = str(feature.GetField("maloaicay"))
        sldlr = [x for x in ds_loaicay if x['maloaicay']== maloaicay]

        if len(sldlr) >0:
            feature.SetField("sldlr", sldlr[0]['sldlr'])
            feature.SetField("captuoi", sldlr[0]['captuoi'])

        mamdsd = feature.GetField("mamdsd")
        mdsd = [x for x in ds_mdsd if x['mamdsd']== mamdsd]

        if len(mdsd) > 0:
            feature.SetField("mdsd", mdsd[0]['mdsd'])
            feature.SetField("malr3", mdsd[0]['malr3'])
        layer.SetFeature(feature)

def docdsloaicay():
    ds = str(Path(__file__).parent.absolute()) + '/data/ds_loaicay.json'
    with open(ds) as f1:
        data = json.load(f1)
        return data

def layloaicay(malc):
    dsloai = docdsloaicay()
    dsma = malc.split("+")
    fcay = (dsma)[0]
    for lc in dsloai:
        global floai
        if lc['maloaicay'] == fcay:
            floai = lc['sldlr']
    tenloai = floai
    n=1
    for malc in range (1, len(dsma)):
        spcode = (dsma)[n]
        for lc in dsloai:
            global loai
            if lc['maloaicay'] == spcode:
                loai = lc['sldlr']
        n=n+1
        tenloai = tenloai + '+' + loai
    return tenloai

def update_loaicay(inpath):
    layer = QgsVectorLayer(inpath, '', 'ogr')
    QgsProject.instance().addMapLayer(layer)

    with edit(layer):     
        for feature in layer.getFeatures():
            malcay = feature['maloaicay']
            if malcay:
                loaicay = layloaicay(malcay)
                feature.setAttribute(feature.fieldNameIndex('sldlr'), loaicay)
                layer.updateFeature(feature)

def refactor(inputPath,templatePath, outputPath):
    fields_mapping = []
    formPath = QgsVectorLayer(templatePath, '', 'ogr')
    for f in formPath.fields():
        fields_mapping.append({
            'name': f.name(),              # Giữ nguyên tên trường như lớp mẫu
            'type': f.type(),              # Kiểu dữ liệu
            'length': f.length(),          # Độ dài
            'precision': f.precision(),    # Số chữ số thập phân
            'expression': f'"{f.name()}"'  # Lấy dữ liệu từ trường cùng tên trong lớp input (nếu có)
        })
    processing.run("qgis:refactorfields", {'INPUT': inputPath,'FIELDS_MAPPING': fields_mapping,'OUTPUT': outputPath})                

def add_newfields(layer, export):
    layer.isValid()
    layer_provider = layer.dataProvider()
    layer_provider.addAttributes([QgsField("vungchitra", QVariant.Double,'',1,0),
                            QgsField("chitra", QVariant.Double,'',1,0),
                            QgsField("dgia", QVariant.Double,'',9,0),
                            QgsField("dtichct", QVariant.Double,'',9,2),
                            QgsField("thanhtien", QVariant.Double,'',9,0),
                            QgsField("k0", QVariant.Double,'',5,2),
                            QgsField("k1", QVariant.Double,'',3,2),
                            QgsField("k2", QVariant.Double,'',3,2),
                            QgsField("k3", QVariant.Double,'',3,2),
                            QgsField("k4", QVariant.Double,'',3,2),
                            QgsField("mucct", QVariant.Double,'',2,0),
                            QgsField("khuvuc", QVariant.Double,'',2,0),
                            QgsField("maluuvuc", QVariant.String,'',30),
                            QgsField("dt2", QVariant.Double,'',9,2)])
    layer.updateFields()

    idx = layer_provider.fieldNameIndex('dt2')
    areas = [ feat.geometry().area()
          for feat in layer.getFeatures() ]
    for area in areas:
        new_values = {idx : float(area)/10000}
        layer_provider.changeAttributeValues({areas.index(area):new_values})
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def add_newfields_TH(layer, export):
    layer.isValid()
    layer_provider = layer.dataProvider()
    layer_provider.addAttributes([QgsField("vungchitra", QVariant.Double,'',1,0),
                            QgsField("chitra", QVariant.Double,'',1,0),
                            QgsField("dgia", QVariant.Double,'',9,0),
                            QgsField("dtichct", QVariant.Double,'',9,2),
                            QgsField("dtichgk", QVariant.Double,'',9,2),
                            QgsField("thanhtien", QVariant.Double,'',9,0),
                            QgsField("k0", QVariant.Double,'',5,2),
                            QgsField("k1", QVariant.Double,'',3,2),
                            QgsField("k2", QVariant.Double,'',3,2),
                            QgsField("k3", QVariant.Double,'',3,2),
                            QgsField("k4", QVariant.Double,'',3,2),
                            QgsField("mucct", QVariant.Double,'',2,0),
                            QgsField("khuvuc", QVariant.Double,'',2,0),
                            QgsField("maluuvuc", QVariant.String,'',30),
                            QgsField("cmt", QVariant.String,'',20),
                            QgsField("nguq", QVariant.String,'',50),
                            QgsField("ngaycap", QVariant.String,'',20),
                            QgsField("dt2", QVariant.Double,'',9,2)])
    layer.updateFields()

    idx = layer_provider.fieldNameIndex('dt2')
    areas = [ feat.geometry().area()
          for feat in layer.getFeatures() ]
    for area in areas:
        new_values = {idx : float(area)/10000}
        layer_provider.changeAttributeValues({areas.index(area):new_values})
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def add_newfields_SL(layer, export):
    layer.isValid()
    layer_provider = layer.dataProvider()
    layer_provider.addAttributes([QgsField("vungchitra", QVariant.Double,'',1,0),
                            QgsField("chitra", QVariant.Double,'',1,0),
                            QgsField("dgia", QVariant.Double,'',9,0),
                            QgsField("dtichct", QVariant.Double,'',9,2),
                            QgsField("thanhtien", QVariant.Double,'',9,0),
                            QgsField("k0", QVariant.Double,'',5,2),
                            QgsField("k1", QVariant.Double,'',3,2),
                            QgsField("k2", QVariant.Double,'',3,2),
                            QgsField("k3", QVariant.Double,'',3,2),
                            QgsField("k4", QVariant.Double,'',3,2),
                            QgsField("mucct", QVariant.Double,'',2,0),
                            QgsField("khuvuc", QVariant.Double,'',2,0),
                            QgsField("maluuvuc", QVariant.String,'',30),
                            QgsField("cmt", QVariant.String,'',20),
                            QgsField("gcn", QVariant.String,'',20),
                            QgsField("dt2", QVariant.Double,'',9,2)])
    layer.updateFields()

    idx = layer_provider.fieldNameIndex('dt2')
    areas = [ feat.geometry().area()
          for feat in layer.getFeatures() ]
    for area in areas:
        new_values = {idx : float(area)/10000}
        layer_provider.changeAttributeValues({areas.index(area):new_values})
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def add_newfields_NA(layer, export):
    layer.isValid()
    layer_provider = layer.dataProvider()
    layer_provider.addAttributes([QgsField("vungchitra", QVariant.Double,'',1,0),
                            QgsField("chitra", QVariant.Double,'',1,0),
                            QgsField("dgia", QVariant.Double,'',9,0),
                            QgsField("dtichct", QVariant.Double,'',9,2),
                            QgsField("thanhtien", QVariant.Double,'',9,0),
                            QgsField("k0", QVariant.Double,'',5,2),
                            QgsField("k1", QVariant.Double,'',3,2),
                            QgsField("k2", QVariant.Double,'',3,2),
                            QgsField("k3", QVariant.Double,'',3,2),
                            QgsField("k4", QVariant.Double,'',3,2),
                            QgsField("mucct", QVariant.Double,'',2,0),
                            QgsField("khuvuc", QVariant.Double,'',2,0),
                            QgsField("maluuvuc", QVariant.String,'',30),
                            QgsField("cql", QVariant.String,'',50),
                            QgsField("dtuongnk", QVariant.String,'',50),
                            QgsField("dt2", QVariant.Double,'',9,2)])
    layer.updateFields()

    idx = layer_provider.fieldNameIndex('dt2')
    areas = [ feat.geometry().area()
          for feat in layer.getFeatures() ]
    for area in areas:
        new_values = {idx : float(area)/10000}
        layer_provider.changeAttributeValues({areas.index(area):new_values})
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def convert_json(inputDsLuuvuc,jsDir):

    # Open the workbook and select the first worksheet
    wb = xlrd.open_workbook(inputDsLuuvuc)
    sh = wb.sheet_by_index(0)
    # List to hold dictionaries
    data_list = []
    # Iterate through each row in worksheet and fetch values into dict
    for rownum in range(1, sh.nrows):
        data = OrderedDict()
        row_values = sh.row_values(rownum)
        data['id'] = row_values[0]
        data['malv'] = row_values[1]
        data['tenlv'] = row_values[2]
        data['tongsotien'] = row_values[3]
        data['dgia'] = row_values[4]
        data_list.append(data)
    # Serialize the list of dicts to JSON
    j = json.dumps(data_list,ensure_ascii=False)
    # Write to file
    export = jsDir
    with open(export, 'w',encoding='utf8') as f:
        f.write(j)

def laymalv(tenlv):
    listlv = docdsluuvuc()
    for lvchon in listlv:
        if lvchon['tenlv'] == tenlv:
            return lvchon['malv']

def fixgeometry(inputPath, outPath):   
    processing.run("native:fixgeometries", {"INPUT":inputPath, "OUTPUT":outPath})

def disaggregate(inputPath, outPath):   
    processing.run("qgis:multiparttosingleparts", {"INPUT":inputPath, "OUTPUT":outPath})

def delete_duplicate(inputPath, outPath):
    processing.run("native:deleteduplicategeometries", {"INPUT":inputPath, "OUTPUT":outPath})

def del_MergeFields(inputPath,outputPath):
    del_fields = ["layer","path"]
    processing.run('qgis:deletecolumn', {'INPUT':inputPath,'COLUMN':del_fields,'OUTPUT': outputPath})

def clip_extent(inputPath,extentPath,outPath):	
    processing.run("gdal:clipvectorbyextent", {"INPUT":inputPath, "EXTENT":extentPath,"OPTIONS":'', "OUTPUT":outPath})

def clip (inputPath,overlayPath,outPath):  
    processing.run("native:clip", {"INPUT":inputPath, "OVERLAY":overlayPath, "OUTPUT":outPath})

def difference (inputPath,overlayPath,outPath):  
    processing.run("native:difference", {"INPUT":inputPath, "OVERLAY":overlayPath, "OUTPUT":outPath})

def mergeLayers (inputPath, outPath):  
    processing.run("native:mergevectorlayers", {"LAYERS":inputPath, "OUTPUT":outPath})

def union(inputPath,overlayPath,outPath):	
    processing.run("qgis:union", {"INPUT":inputPath, "OVERLAY":overlayPath, "OUTPUT":outPath})

def buffer(inputPath,outPath):
    processing.run("native:buffer", {'INPUT':inputPath,
              'DISTANCE': 0.01,
              'SEGMENTS': 5,
              'DISSOLVE': False,
              'END_CAP_STYLE': 0,
              'JOIN_STYLE': 0,
              'MITER_LIMIT': 2,
              'OUTPUT': outPath})

def docdsluuvuc(): 
    ds = str(Path(__file__).parent.absolute()) + '/data/dsluuvuc.json'
    with open(ds) as f1:
        data = json.load(f1)
        return data

def updatePaymentZone (inpath, malv, export):
    layer = QgsVectorLayer(inpath, '', 'ogr')
    QgsProject.instance().addMapLayer(layer)
    with edit(layer):
        for feature in layer.getFeatures():
            feature.setAttribute(feature.fieldNameIndex('vungchitra'),1)
            if feature['maluuvuc'] == NULL :
                feature.setAttribute(feature.fieldNameIndex('maluuvuc'),malv )
            else:
                giatri = feature['maluuvuc'] + '+' + malv
                feature.setAttribute(feature.fieldNameIndex('maluuvuc'), giatri)
            layer.updateFeature(feature)
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def selectbylocation(inpath,clippath,malv,export):
    layer = QgsVectorLayer(inpath, '', 'ogr')
    QgsProject.instance().addMapLayer(layer)
    processing.run("qgis:selectbylocation", {"INPUT":inpath, "INTERSECT":clippath, "METHOD": 0,"PREDICATE": [6]})
    with edit(layer):
        listOfIds = [feat.id() for feat in layer.getFeatures() if feat['maxa'] == NULL]
        layer.deleteFeatures(listOfIds)
        for feature in layer.getSelectedFeatures():
            feature.setAttribute(feature.fieldNameIndex('vungchitra'),1)
            if feature['maluuvuc'] == NULL :
                feature.setAttribute(feature.fieldNameIndex('maluuvuc'),malv )
            else:
                giatri = feature['maluuvuc'] + '+' + malv
                feature.setAttribute(feature.fieldNameIndex('maluuvuc'), giatri)
            layer.updateFeature(feature)
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def recalculate_area_2(layer, export):
    layer.isValid()
    layer_provider = layer.dataProvider()
    layer_provider.addAttributes([QgsField("dt3", QVariant.Double,'',9,2)])

    layer.updateFields()
    idx1 = layer_provider.fieldNameIndex('dtich')
    idx3 = layer_provider.fieldNameIndex('dt3')
    idx2 = layer_provider.fieldNameIndex('dt2')

    areas = [ feat.geometry().area()
          for feat in layer.getFeatures() ]

    for area in areas:
        new_values = {idx3 : float(area)/10000}
        layer_provider.changeAttributeValues({areas.index(area):new_values})

    layer.startEditing()
    for feature in layer.getFeatures():
        s1 = feature['dtich']
        s2 = feature['dt2']
        s3 = feature['dt3']
        if s3 == NULL or s2 == NULL or s1 == NULL:
            s4 = 0.00
        elif s2 == 0:
            s4 = 0.00
        else:
            s4 = s3 * s1 / s2

        feature.setAttribute(idx1, s4)
        layer.updateFeature(feature)
    layer.commitChanges()

    res = layer_provider.deleteAttributes([idx2,idx3])
    layer.updateFields()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def recalculate_area_1(layer, export):
    layer.isValid()
    layer_provider = layer.dataProvider()
    idx1 = layer_provider.fieldNameIndex('dtich')
    idx2 = layer_provider.fieldNameIndex('dt2')

    areas = [ feat.geometry().area()
          for feat in layer.getFeatures() ]

    for area in areas:
        new_values = {idx1 : float(area)/10000}
        layer_provider.changeAttributeValues({areas.index(area):new_values})

    res = layer_provider.deleteAttributes([idx2])
    layer.updateFields()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def eliminate(inPath,outPath):
    shp =  QgsVectorLayer(inPath, '', 'ogr')
    layer = QgsProject.instance().addMapLayer(shp)
    exp='dtich = 0'
    processing.run("qgis:selectbyexpression", {"EXPRESSION":exp, "INPUT":layer, "METHOD": 0})
    processing.run("qgis:eliminateselectedpolygons", {'INPUT':layer,'MODE':2,'OUTPUT': outPath})

def check_maldlr_nggocr_33(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['nggocr'] ==1 and feature['maldlr'] > 64:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa nguồn gốc rừng và mã loại đất loại rừng')
            if feature['nggocr'] ==2 and (feature['maldlr'] < 65 or feature['maldlr'] > 76):
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa nguồn gốc rừng và mã loại đất loại rừng')
            if feature['nggocr'] ==3 and (0 < feature['maldlr'] < 77):
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa nguồn gốc rừng và mã loại đất loại rừng')
            if (feature['nggocr'] ==0 or feature['nggocr'] >3) and feature['nqh'] <2:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai mã nguồn gốc rừng hoặc mã loại đất loại rừng')
            if (feature['maldlr'] ==0 or feature['maldlr'] > 98) and feature['nqh'] <2:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai mã nguồn gốc rừng hoặc mã loại đất loại rừng')   
            layer.updateFeature(feature)

def check_maldlr_nggocr_34(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['nggocr'] ==1 and feature['maldlr'] > 59 and feature['maldlr'] < 94:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa nguồn gốc rừng và mã loại đất loại rừng')
            if feature['nggocr'] ==2 and (feature['maldlr'] < 60 or feature['maldlr'] > 71):
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa nguồn gốc rừng và mã loại đất loại rừng')
            if feature['nggocr'] ==3 and (0 < feature['maldlr'] < 72 or feature['maldlr'] > 93):
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa nguồn gốc rừng và mã loại đất loại rừng')
            if (feature['nggocr'] ==0 or feature['nggocr'] >3) and feature['nqh'] <2:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai mã nguồn gốc rừng hoặc mã loại đất loại rừng')
            if (feature['maldlr'] ==0 or feature['maldlr'] > 98) and feature['nqh'] <2:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai mã nguồn gốc rừng hoặc mã loại đất loại rừng')   
            layer.updateFeature(feature)

def docmaldlr33():
    maldlr = str(Path(__file__).parent.absolute()) + '/data/maldlrtt33.json'
    with open(maldlr) as f1:
        data = json.load(f1)
        return data

def docmaldlr34():
    maldlr = str(Path(__file__).parent.absolute()) + '/data/maldlrtt34.json'
    with open(maldlr) as f1:
        data = json.load(f1)
        return data

def findLDLR33(maldlr,ldlr):
    data = docmaldlr33()   
    for x in data:
        if ldlr != NULL:
            if x["maldlr"] == maldlr and (x["ldlr"]).upper() != (ldlr).upper():
                return 1

def findLDLR34(maldlr,ldlr):
    data = docmaldlr34()   
    for x in data:
        if ldlr != NULL:
            if x["maldlr"] == maldlr and (x["ldlr"]).upper() != (ldlr).upper():
                return 1


def check_maldlr_ldlr_33(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            compare = findLDLR33(feature['maldlr'], feature['ldlr'])
            if compare == 1 and feature['loi'] != NULL:
                val = feature['loi'] + ' & ' + 'Lỗi sai logic giữa loại đất loại rừng và mã loại đất loại rừng'
                feature.setAttribute(feature.fieldNameIndex('loi'), val)
            if compare == 1 and feature['loi'] == NULL:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa loại đất loại rừng và mã loại đất loại rừng')              
            layer.updateFeature(feature)

def check_maldlr_ldlr_34(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            compare = findLDLR34(feature['maldlr'], feature['ldlr'])
            if compare == 1 and feature['loi'] != NULL:
                val = feature['loi'] + ' & ' + 'Lỗi sai logic giữa loại đất loại rừng và mã loại đất loại rừng'
                feature.setAttribute(feature.fieldNameIndex('loi'), val)
            if compare == 1 and feature['loi'] == NULL:
                feature.setAttribute(feature.fieldNameIndex('loi'), 'Lỗi sai logic giữa loại đất loại rừng và mã loại đất loại rừng')             
            layer.updateFeature(feature)

def check_nggocr_sldlr(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['nggocr'] ==1 and feature['sldlr'] != NULL and feature['loi'] != NULL:
                val = feature['loi'] + ' & ' + 'Rừng tự nhiên nhưng có tên loài cây trồng'
                feature.setAttribute(feature.fieldNameIndex('loi'),val)
            if feature['nggocr'] ==1 and feature['sldlr'] != NULL and feature['loi'] == NULL:
                feature.setAttribute(feature.fieldNameIndex('loi'),'Rừng tự nhiên nhưng có tên loài cây trồng')            
            layer.updateFeature(feature)

def check_3lr_nqh(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['nqh'] == 0 and feature['malr3'] == 0 and feature['loi'] != NULL:
                val1 = feature['loi'] + ' & ' + 'Lỗi sai logic giữa các trường quy hoạch 3 loại rừng'
                feature.setAttribute(feature.fieldNameIndex('loi'),val1)
            if feature['nqh'] == 0 and feature['malr3'] == 0 and feature['loi'] == NULL:
                feature.setAttribute(feature.fieldNameIndex('loi'),'Lỗi sai logic giữa các trường quy hoạch 3 loại rừng')
            if feature['nqh'] > 0  and feature['malr3'] > 0 and feature['malr3'] < 4 and feature['loi'] != NULL:
                val2 = feature['loi'] + ' & ' + 'Lỗi sai logic giữa các trường quy hoạch 3 loại rừng'
                feature.setAttribute(feature.fieldNameIndex('loi'),val2)           
            if feature['nqh'] > 0 and feature['malr3'] > 0 and feature['malr3'] < 4 and feature['loi'] == NULL:
                feature.setAttribute(feature.fieldNameIndex('loi'),'Lỗi sai logic giữa các trường quy hoạch 3 loại rừng')
            if (feature['nqh'] > 2 or feature['malr3'] > 3) and feature['loi'] != NULL:
                val3 = feature['loi'] + ' & ' + 'Lỗi sai mã quy hoạch 3 loại rừng'
                feature.setAttribute(feature.fieldNameIndex('loi'),val3)
            if (feature['nqh'] > 2 or feature['malr3'] > 3) and feature['loi'] == NULL:
                feature.setAttribute(feature.fieldNameIndex('loi'),'Lỗi sai mã quy hoạch 3 loại rừng')          
            layer.updateFeature(feature)

def Error_Export(inpath,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['loi'].isnull() == False)]
    df_pfes = df_pfes.loc[:, ['huyen','xa','tk','khoanh','lo','nggocr','ldlr','maldlr','sldlr','malr3','nqh','loi']]
    df_pfes.to_excel(output)
            
def rtn_payment(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['nggocr']==1:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def rtg_payment33(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] >64 and feature['maldlr'] <70:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def rtg_payment34(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] >59 and feature['maldlr'] <65:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def rttn_payment33(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] >69 and feature['maldlr'] <75:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def rttn_payment34(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] > 64 and feature['maldlr'] <70:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def rtk_payment33(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] >74 and feature['maldlr'] <77:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def rtk_payment34(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] >69 and feature['maldlr'] <72:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def ctr_payment33(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] >76 and feature['maldlr'] <83:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def ctr_payment34(layer):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['vungchitra'] ==1 and feature['maldlr'] >71 and feature['maldlr'] <78:
                feature.setAttribute(feature.fieldNameIndex('chitra'),1)
            layer.updateFeature(feature)

def lc_payment(layer,ds_loaicay,export):
    with edit(layer):
        for feature in layer.getFeatures():
            if feature['sldlr'] in ds_loaicay:
                feature.setAttribute(feature.fieldNameIndex('chitra'),0)
            layer.updateFeature(feature)
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def lc__non_payment(layer,export):
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile")

def docdsxkk():
    ds = str(Path(__file__).parent.absolute()) + '/data/xakk.json'
    with open(ds) as f1:
        data = json.load(f1)
        return data

def update_xkk(layer,maxa,khuvuc):
    with edit(layer):
        for feature in layer.getFeatures():
            giatri = str(feature['maxa'])
            if feature['chitra']== 1 and giatri in maxa:
                index = maxa.index(giatri)
                feature.setAttribute(feature.fieldNameIndex('khuvuc'),khuvuc[index])
            layer.updateFeature(feature)

def edit_dsxkk(maxa,khuvuc):
    ds = str(Path(__file__).parent.absolute()) + '/data/xakk.json'
    with open(ds) as f1:
        data = json.load(f1)
        for feat in data:
            if feat['MAXA'] in maxa:
                ind = maxa.index(feat['MAXA'])
                feat['KHUVUC'] = khuvuc[ind]
        j = json.dumps(data, ensure_ascii=False)
        export = str(Path(__file__).parent.absolute()) + '/data/xakk.json'
        with open(export, 'w',encoding='utf8') as f:
            f.write(j)

def join_xkk(inPath):
    with open(str(Path(__file__).parent.absolute()) +"/data/xakk.json", 'r') as myfile:
        data=myfile.read()
    ds_xkk = json.loads(data)

    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1:
            maxa = str(feature.GetField("maxa"))
            xa = [x for x in ds_xkk if x['MAXA']==maxa]
            if len(xa) >0:
                feature.SetField("khuvuc", xa[0]['KHUVUC'])
            layer.SetFeature(feature)

def update_K1_33(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1:
            if 0<feature['maldlr']<52:
                if feature['mgo']>200:
                    feature.SetField("k1", 1)
                elif 100< feature['mgo'] <=200:
                    feature.SetField("k1", 0.95)
                elif feature['mgo'] <=100:
                    feature.SetField("k1", 0.9)
                else:
                    feature.SetField("k1", 0.9)
            else:
                feature.SetField("k1", 0.9)
        layer.SetFeature(feature)

def update_K1_34(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1 :
            if 0<feature['maldlr']<48 or 59<feature['maldlr']<65 or 69<feature['maldlr']<72 or 93<feature['maldlr']<99:
                if feature['mgo']>200:
                    feature.SetField("k1", 1)
                elif 100< feature['mgo'] <=200:
                    feature.SetField("k1", 0.95)
                elif feature['mgo'] <=100:
                    feature.SetField("k1", 0.9)
                else:
                    feature.SetField("k1", 0.9)
            elif feature['maldlr']== 48 or feature['maldlr']== 50 or feature['maldlr']== 65 or feature['maldlr']== 66:
                if feature['mtn'] >=3:
                    feature.SetField("k1", 1)
                elif 1<= feature['mtn'] <3:
                    feature.SetField("k1", 0.95)
                elif feature['mtn'] <1:
                    feature.SetField("k1", 0.9)
                else:
                    feature.SetField("k1", 0.9)
            elif feature['maldlr']== 49:
                if feature['mtn'] >=8:
                    feature.SetField("k1", 1)
                elif 5<= feature['mtn'] <8:
                    feature.SetField("k1", 0.95)
                elif feature['mtn'] <5:
                    feature.SetField("k1", 0.9)
                else:
                    feature.SetField("k1", 0.9)
            elif feature['maldlr']== 51:
                if feature['mtn'] >=4:
                    feature.SetField("k1", 1)
                elif 2<= feature['mtn'] <4:
                    feature.SetField("k1", 0.95)
                elif feature['mtn'] <2:
                    feature.SetField("k1", 0.9)
                else:
                    feature.SetField("k1", 0.9)
            elif feature['maldlr']== 52:
                if feature['mtn'] >=10:
                    feature.SetField("k1", 1)
                elif 6<= feature['mtn'] <10:
                    feature.SetField("k1", 0.95)
                elif feature['mtn'] <6:
                    feature.SetField("k1", 0.9)
                else:
                    feature.SetField("k1", 0.9)
            elif feature['maldlr']== 53:
                if feature['mtn'] >=6:
                    feature.SetField("k1", 1)
                elif 3<= feature['mtn'] <6:
                    feature.SetField("k1", 0.95)
                elif feature['mtn'] <3:
                    feature.SetField("k1", 0.9)
                else:
                    feature.SetField("k1", 0.9)
            elif 53 <feature['maldlr']< 60 or 66 <feature['maldlr']< 70 or 71 <feature['maldlr']< 78:
                feature.SetField("k1", 0.9)
            else:
                feature.SetField("k1", 0.9)
        layer.SetFeature(feature)

def update_K1_uncheck(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1:
            feature.SetField("k1", 1)
        layer.SetFeature(feature)

def update_K2(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1 :
            if feature['malr3']== 2:
                feature.SetField("k2", 1)
            elif feature['malr3']== 1:
                feature.SetField("k2", 0.95)
            else:
                feature.SetField("k2", 0.9)
        layer.SetFeature(feature)

def update_K2_uncheck(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1:
            feature.SetField("k2", 1)
        layer.SetFeature(feature)

def update_K3(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1 :
            if feature['nggocr']== 1:
                feature.SetField("k3", 1)
            else:
                feature.SetField("k3", 0.9)
        layer.SetFeature(feature)

def update_K3_uncheck(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1:
            feature.SetField("k3", 1)
        layer.SetFeature(feature)

def update_K4(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1 :
            if feature['khuvuc']== 3:
                feature.SetField("k4", 1)
            elif feature['khuvuc']== 2:
                feature.SetField("k4", 0.95)
            else:
                feature.SetField("k4", 0.9)
        layer.SetFeature(feature)

def update_K4_uncheck(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1:
            feature.SetField("k4", 1)
        layer.SetFeature(feature)

def update_K0(inPath):
    shp =  QgsVectorLayer(inPath, '', 'ogr')
    layer = QgsProject.instance().addMapLayer(shp)

    exp = QgsExpression('"k1"*"k2"*"k3"*"k4"')
    context = QgsExpressionContext()
    context.appendScopes(QgsExpressionContextUtils.globalProjectLayerScopes(layer))

    with edit(layer):
        for feature in layer.getFeatures():
            context.setFeature(feature)
            if feature['chitra']== 1:
                feature['k0'] = exp.evaluate(context)
            layer.updateFeature(feature)

def payment_area(inPath):
    shp =  QgsVectorLayer(inPath, '', 'ogr')
    layer = QgsProject.instance().addMapLayer(shp)

    exp = QgsExpression('"dtich"*"k0"')
    context = QgsExpressionContext()
    context.appendScopes(QgsExpressionContextUtils.globalProjectLayerScopes(layer))

    with edit(layer):
        for feature in layer.getFeatures():
            context.setFeature(feature)
            if feature['k0'] == NULL:
                feature['dtichct'] = 0
            elif feature['k0']> 0:
                feature['dtichct'] = exp.evaluate(context)
            else:
                feature['dtichct'] = 0
            layer.updateFeature(feature)

def edit_price(malv,dongia):
    ds = str(Path(__file__).parent.absolute()) + '/data/dsluuvuc.json'
    export = str(Path(__file__).parent.absolute()) + '/data/dsluuvuc.json'

    with open(ds) as f1:
        data = json.load(f1)
        for feat in data:
            if feat['malv'] == malv:
                feat['dgia'] = dongia
        j = json.dumps(data, ensure_ascii=False)
        with open(export, 'w',encoding='utf8') as f:
            f.write(j)

def price(inPath):
    dslv = str(Path(__file__).parent.absolute()) + '/data/dsluuvuc.json'

    with open(dslv, 'r') as myfile:
        data=myfile.read()
    ds_dongia = json.loads(data)

    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()
    listmlv = []
    listdg = []
    for feat in ds_dongia:
        listmlv.append(feat['malv'])
        listdg.append(feat['dgia'])

    for feature in layer:
        if feature['chitra']== 1:
            malv = feature.GetField("maluuvuc").split('+')
            dongia = 0
            for x in malv:
                ind = listmlv.index(x)
                dongia = dongia + listdg[ind]
            feature.SetField("dgia", dongia)
            layer.SetFeature(feature)

def payment_level(inPath):
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    for feature in layer:
        if feature['chitra']== 1 :
            if feature['dgia']<= 50000:
                feature.SetField("mucct", 1)
            elif 50000<feature['dgia']<= 100000:
                feature.SetField("mucct", 2)
            elif 100000<feature['dgia']<= 150000:
                feature.SetField("mucct", 3)
            elif 150000<feature['dgia']<= 200000:
                feature.SetField("mucct", 4)
            elif 200000<feature['dgia']<= 300000:
                feature.SetField("mucct", 5)
            elif 300000<feature['dgia']<= 500000:
                feature.SetField("mucct", 6)
            elif feature['dgia']> 500000:
                feature.SetField("mucct", 7)
            else:
                pass
        layer.SetFeature(feature)

    shp =  QgsVectorLayer(inPath, '', 'ogr')
    layer = QgsProject.instance().addMapLayer(shp)

    exp = QgsExpression('"dtichct"*"dgia"')
    context = QgsExpressionContext()
    context.appendScopes(QgsExpressionContextUtils.globalProjectLayerScopes(layer))

    with edit(layer):
        for feature in layer.getFeatures():
            context.setFeature(feature)
            if feature['dgia']> 0:
                feature['thanhtien'] = exp.evaluate(context)
            layer.updateFeature(feature)

def export_Attribute(inPath, export):
    conf_dir = str(Path(__file__).parent.absolute()) + '/data/config.txt'
    f = open(conf_dir, 'r')
    conf_code = f.read()

    if conf_code == '38':
        shp =  QgsVectorLayer(inPath, '', 'ogr')
        layer = QgsProject.instance().addMapLayer(shp)
        with edit(layer):
            for feature in layer.getFeatures():
                if feature['cmt']== NULL:
                    feature.setAttribute(feature.fieldNameIndex('cmt'),'k')
                if feature['nguq']== NULL:
                    feature.setAttribute(feature.fieldNameIndex('nguq'),'k')
                if feature['ngaycap']== NULL:
                    feature.setAttribute(feature.fieldNameIndex('ngaycap'),'k')
                layer.updateFeature(feature)
        QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "xlsx") 
        QgsProject.instance().removeAllMapLayers()

    elif conf_code == '14':
        shp =  QgsVectorLayer(inPath, '', 'ogr')
        layer = QgsProject.instance().addMapLayer(shp)
        with edit(layer):
            for feature in layer.getFeatures():
                if feature['cmt']== NULL:
                    feature.setAttribute(feature.fieldNameIndex('cmt'),'ifee')
                if feature['gcn']== NULL:
                    feature.setAttribute(feature.fieldNameIndex('gcn'),'ifee')
                layer.updateFeature(feature)
        QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "xlsx") 
        QgsProject.instance().removeAllMapLayers()

    else:
        shp =  QgsVectorLayer(inPath, '', 'ogr')
        layer = QgsProject.instance().addMapLayer(shp)
        QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "xlsx") 
        QgsProject.instance().removeAllMapLayers()

def export_THX(inPath, export):
    conf_dir = str(Path(__file__).parent.absolute()) + '/data/config.txt'
    f = open(conf_dir, 'r')
    conf_code = f.read()
    if conf_code == '40':
        df = pd.read_excel(inPath)
        df.columns = [x.lower() for x in df.columns]
        df = df.loc[(df['vungchitra'] == 1) & (df['machur'] < 9000)]
        df = df.loc[:, ['matinh','tinh','mahuyen','huyen','maxa','xa']]
        df.drop_duplicates(subset=['huyen'], keep='first', inplace=True)
        df.to_json(export, orient='records')

        with open(export) as f1:
            data = json.load(f1)
            j = json.dumps(data, ensure_ascii=False)
            with open(export, 'w',encoding='utf8') as f:
                f.write(j)

    elif conf_code == '17':
        df = pd.read_excel(inPath)
        df.columns = [x.lower() for x in df.columns]
        df = df.loc[(df['dtichct'] > 0) & (df['machur'] < 9000)]
        df = df.loc[:, ['matinh','tinh','mahuyen','huyen','maxa','xa','ddanh']]
        df.drop_duplicates(subset=['ddanh'], keep='first', inplace=True)
        df.to_json(export, orient='records')

        with open(export) as f1:
            data = json.load(f1)
            j = json.dumps(data, ensure_ascii=False)
            with open(export, 'w',encoding='utf8') as f:
                f.write(j)

    else:
        df = pd.read_excel(inPath)
        df.columns = [x.lower() for x in df.columns]
        df = df.loc[(df['dtichct'] > 0) & (df['machur'] < 9000)]
        df = df.loc[:, ['matinh','tinh','mahuyen','huyen','maxa','xa']]
        df.drop_duplicates(subset=['xa'], keep='first', inplace=True)
        df.to_json(export, orient='records')

        with open(export) as f1:
            data = json.load(f1)
            j = json.dumps(data, ensure_ascii=False)
            with open(export, 'w',encoding='utf8') as f:
                f.write(j)

def export_ChuRung(inPath, export):
    conf_dir = str(Path(__file__).parent.absolute()) + '/data/config.txt'
    f = open(conf_dir, 'r')
    conf_code = f.read()
    if conf_code == '40':
        df = pd.read_excel(inPath)
        df.columns = [x.lower() for x in df.columns]
        df = df.loc[(df['vungchitra'] == 1) & (df['machur'] > 9000)]
        df = df.loc[:, ['matinh','tinh','machur','churung']]
        df.drop_duplicates(subset=['machur'], keep='first', inplace=True)
        df.to_json(export, orient='records')

        with open(export) as f1:
            data = json.load(f1)
            j = json.dumps(data, ensure_ascii=False)
            with open(export, 'w',encoding='utf8') as f:
                f.write(j)
    else:
        df = pd.read_excel(inPath)
        df.columns = [x.lower() for x in df.columns]
        df = df.loc[(df['dtichct'] > 0) & (df['machur'] > 9000)]
        df = df.loc[:, ['matinh','tinh','machur','churung']]
        df.drop_duplicates(subset=['machur'], keep='first', inplace=True)
        df.to_json(export, orient='records')

        with open(export) as f1:
            data = json.load(f1)
            j = json.dumps(data, ensure_ascii=False)
            with open(export, 'w',encoding='utf8') as f:
                f.write(j)

def docdshanhchinh():
    dir = str(Path(__file__).parent.absolute()) + '/tempo/THX.json'
    with open(dir) as f:
        datax = json.load(f)
        return datax

def docdschurung():
    dir = str(Path(__file__).parent.absolute()) + '/tempo/ChuRung.json'
    with open(dir) as f:
        datax = json.load(f)
        return datax

def report_comumune(inpath,maxa,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.sort_values(by=['maxa','dtuong','churung'], ascending=True)
    df_pfes = df_pfes.loc[((df_pfes['dtichct'] > 0))]
    dtuong_header = [['I','TÊN HỘ GIA ĐÌNH, CÁ NHÂN'],['II','TÊN CỘNG ĐỒNG DÂN CƯ'],['III','ỦY BAN NHÂN DÂN XÃ']]
    df_group = df_pfes.groupby(['maxa','dtuong'])
    df_doituong = df_pfes[df_pfes['maxa']==maxa]
    ds_dtuong_temp = df_doituong['dtuong'].unique().tolist()
    ds_dtuong = sorted(list(filter(lambda x: x > 0 and x < 4, ds_dtuong_temp)))

    def tinh_doituong(df_con):
        out = df_con.groupby(['churung']).apply(lambda sub_df: sub_df.pivot_table(
            index=['dtuong', 'churung', 'lo', 'khoanh','tk','ddanh', 'k0', 'k1', 'k2', 'k3', 'k4'],
            values=['dtich', 'dtichct'],
            aggfunc={'dtich': np.sum, 'dtichct': np.sum},
            fill_value=0,
            margins=True,
            margins_name='Tổng'))
        out.index = out.index.droplevel(0)
        return out

    # Tạo file tạm và trường dữ liệu
    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()

    myheader = ['TT','Bên cung ứng DVMTR','Lô','Khoảnh','Tiểu khu','Tên địa phương(Nếu có)','Diện tích cung ứng DVMTR (ha)','Hệ số K','K1','K2','K3','K4','Diện tích được chi trả tiền DVMTR (ha)']

    myField = QgsField( 'ID', QVariant.Double)
    temp.addAttribute(myField)

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()
    # Xong tạo file tạm
    # Một số biến tạm để đếm
    row_id = 0
    thutu = 0
    name = ''
    i = 0
    total_cungung = 0
    total_ct = 0

    for index,dtuong  in enumerate(ds_dtuong):

        row_id = row_id +1
        f = QgsFeature()
        f.setAttributes([row_id,dtuong_header[index][0],dtuong_header[index][1],'','','','','','','','','','',''])
        temp.addFeature(f)


        df_doituong_con = df_group.get_group((maxa,dtuong))
        doituongX = tinh_doituong(df_doituong_con)

        for row in doituongX.itertuples():
            row_id = row_id +1

            dtich_cung_ung = str(round(float(row[1]),2))
            dtich_ct = str(round(float(row[2]),2))

            if len(str(row[0][1])) == 0:
                cot3 = 'Cộng'
                total_cungung = total_cungung +float(row[1])
                total_ct = total_ct +float(row[2])
            else:
                cot3 = row[0][1]
                if row[0][1] != name:
                    thutu = thutu+1
                    name =  row[0][1]

            fet = QgsFeature()
            fet.setAttributes([row_id,thutu, cot3, row[0][2],row[0][3],row[0][4],row[0][5],dtich_cung_ung,row[0][6],row[0][7],row[0][8],row[0][9], row[0][10],dtich_ct])
            temp.addFeature(fet)

    row_id = row_id +1
    f = QgsFeature()
    f.setAttributes([row_id,'','Tổng','','','','',str(round(total_cungung,2)),'','','','','',str(round(total_ct,2))])
    temp.addFeature(f)

    temp.deleteAttributes([0])
    temp.updateFields()
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)
    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, output , "utf-8", layer.crs(), 'xlsx')

def form_HGD_HB(inpath,thonban,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.sort_values(by=['ddanh','dtuong','churung'], ascending=True)
    df_pfes = df_pfes.loc[((df_pfes['dtichct'] > 0))]
    dtuong_header = [['I','TÊN HỘ GIA ĐÌNH, CÁ NHÂN'],['II','TÊN CỘNG ĐỒNG DÂN CƯ'],['III','ỦY BAN NHÂN DÂN XÃ']]
    df_group = df_pfes.groupby(['ddanh','dtuong'])
    df_doituong = df_pfes[df_pfes['ddanh']== thonban]
    ds_dtuong_temp = df_doituong['dtuong'].unique().tolist()
    ds_dtuong = sorted(list(filter(lambda x: x > 0 and x < 2, ds_dtuong_temp)))

    def tinh_doituong(df_con):
        out = df_con.groupby(['churung']).apply(lambda sub_df: sub_df.pivot_table(
            index=['dtuong', 'churung','thuad', 'lo', 'khoanh','tk', 'k0', 'dgia', 'maluuvuc'],
            values=['dtich', 'dtichct','thanhtien'],
            aggfunc={'dtich': np.sum, 'dtichct': np.sum, 'thanhtien':np.sum},
            fill_value=0,
            margins=True,
            margins_name='Tổng'))
        out.index = out.index.droplevel(0)
        return out

    # Tạo file tạm và trường dữ liệu
    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()

    myheader = ['TT','Bên cung ứng DVMTR','Thửa đất','Lô','Khoảnh','Tiểu khu','Diện tích cung ứng DVMTR (ha)','Hệ số K tổng hợp','Diện tích được chi trả tiền DVMTR (ha)','Tổng đơn giá','Mã lưu vực','Thành tiền']

    myField = QgsField( 'ID', QVariant.Double)
    temp.addAttribute(myField)

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()
    # Xong tạo file tạm
    # Một số biến tạm để đếm
    row_id = 0
    thutu = 0
    name = ''
    i = 0
    total_cungung = 0
    total_ct = 0
    total_thanhtien = 0

    for index,dtuong  in enumerate(ds_dtuong):

        row_id = row_id +1
        f = QgsFeature()
        f.setAttributes([row_id,dtuong_header[index][0],dtuong_header[index][1],'','','','','','','','','',''])
        temp.addFeature(f)


        df_doituong_con = df_group.get_group((thonban,dtuong))
        doituongX = tinh_doituong(df_doituong_con)

        for row in doituongX.itertuples():
            row_id = row_id +1

            dtich_cung_ung = str(round(float(row[1]),2))
            dtich_ct = str(round(float(row[2]),2))
            thanhtien = str(round(float(row[3]),0))

            if len(str(row[0][1])) == 0:
                cot3 = 'Cộng'
                total_cungung = total_cungung +float(row[1])
                total_ct = total_ct +float(row[2])
                total_thanhtien = total_thanhtien +float(row[3])
            else:
                cot3 = row[0][1]
                if row[0][1] != name:
                    thutu = thutu+1
                    name =  row[0][1]

            fet = QgsFeature()
            fet.setAttributes([row_id,thutu, cot3, row[0][2],row[0][3],row[0][4],row[0][5],dtich_cung_ung,row[0][6],dtich_ct,row[0][7],row[0][8], thanhtien])
            temp.addFeature(fet)

    row_id = row_id +1
    f = QgsFeature()
    f.setAttributes([row_id,'','Tổng','','','','',str(round(total_cungung,2)),'',str(round(total_ct,2)),'','',str(round(total_thanhtien,0))])
    temp.addFeature(f)

    temp.deleteAttributes([0])
    temp.updateFields()
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)
    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, output , "utf-8", layer.crs(), 'xlsx')

def form_UBND_HB(inpath,xa,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.sort_values(by=['xa','dtuong','churung'], ascending=True)
    df_pfes = df_pfes.loc[((df_pfes['dtichct'] > 0))]
    dtuong_header = [['I','TÊN CỘNG ĐỒNG DÂN CƯ'],['II','ỦY BAN NHÂN DÂN XÃ']]
    df_group = df_pfes.groupby(['xa','dtuong'])
    df_doituong = df_pfes[df_pfes['xa']== xa]
    ds_dtuong_temp = df_doituong['dtuong'].unique().tolist()
    ds_dtuong = sorted(list(filter(lambda x: x > 1 and x < 4, ds_dtuong_temp)))

    def tinh_doituong(df_con):
        out = df_con.groupby(['churung']).apply(lambda sub_df: sub_df.pivot_table(
            index=['dtuong', 'churung','thuad', 'lo', 'khoanh','tk', 'k0', 'dgia', 'maluuvuc'],
            values=['dtich', 'dtichct','thanhtien'],
            aggfunc={'dtich': np.sum, 'dtichct': np.sum, 'thanhtien':np.sum},
            fill_value=0,
            margins=True,
            margins_name='Tổng'))
        out.index = out.index.droplevel(0)
        return out

    # Tạo file tạm và trường dữ liệu
    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()

    myheader = ['TT','Bên cung ứng DVMTR','Thửa đất','Lô','Khoảnh','Tiểu khu','Diện tích cung ứng DVMTR (ha)','Hệ số K tổng hợp','Diện tích được chi trả tiền DVMTR (ha)','Tổng đơn giá','Mã lưu vực','Thành tiền']

    myField = QgsField( 'ID', QVariant.Double)
    temp.addAttribute(myField)

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()
    # Xong tạo file tạm
    # Một số biến tạm để đếm
    row_id = 0
    thutu = 0
    name = ''
    i = 0
    total_cungung = 0
    total_ct = 0
    total_thanhtien = 0

    for index,dtuong  in enumerate(ds_dtuong):

        row_id = row_id +1
        f = QgsFeature()
        f.setAttributes([row_id,dtuong_header[index][0],dtuong_header[index][1],'','','','','','','','','',''])
        temp.addFeature(f)


        df_doituong_con = df_group.get_group((xa,dtuong))
        doituongX = tinh_doituong(df_doituong_con)

        for row in doituongX.itertuples():
            row_id = row_id +1

            dtich_cung_ung = str(round(float(row[1]),2))
            dtich_ct = str(round(float(row[2]),2))
            thanhtien = str(round(float(row[3]),0))

            if len(str(row[0][1])) == 0:
                cot3 = 'Cộng'
                total_cungung = total_cungung +float(row[1])
                total_ct = total_ct +float(row[2])
                total_thanhtien = total_thanhtien +float(row[3])
            else:
                cot3 = row[0][1]
                if row[0][1] != name:
                    thutu = thutu+1
                    name =  row[0][1]

            fet = QgsFeature()
            fet.setAttributes([row_id,thutu, cot3, row[0][2],row[0][3],row[0][4],row[0][5],dtich_cung_ung,row[0][6],dtich_ct,row[0][7],row[0][8], thanhtien])
            temp.addFeature(fet)

    row_id = row_id +1
    f = QgsFeature()
    f.setAttributes([row_id,'','Tổng','','','','',str(round(total_cungung,2)),'',str(round(total_ct,2)),'','',str(round(total_thanhtien,0))])
    temp.addFeature(f)

    temp.deleteAttributes([0])
    temp.updateFields()
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)
    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, output , "utf-8", layer.crs(), 'xlsx')

def form_CR1_NB(inpath,maxa,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.sort_values(by=['maxa','dtuong','churung'], ascending=True)
    df_pfes = df_pfes.loc[((df_pfes['dtichct'] > 0))]
    dtuong_header = [['I','TÊN HỘ GIA ĐÌNH, CÁ NHÂN'],['II','TÊN CỘNG ĐỒNG DÂN CƯ'],['III','ỦY BAN NHÂN DÂN XÃ']]
    df_group = df_pfes.groupby(['maxa','dtuong'])
    df_doituong = df_pfes[df_pfes['maxa']== maxa]
    ds_dtuong_temp = df_doituong['dtuong'].unique().tolist()
    ds_dtuong = sorted(list(filter(lambda x: x > 0 and x < 4, ds_dtuong_temp)))

    def tinh_doituong(df_con):
        out = df_con.groupby(['churung']).apply(lambda sub_df: sub_df.pivot_table(
            index=['dtuong', 'churung','ddanh', 'lo', 'khoanh','tk', 'k0', 'dgia', 'maluuvuc'],
            values=['dtich', 'dtichct','thanhtien'],
            aggfunc={'dtich': np.sum, 'dtichct': np.sum, 'thanhtien':np.sum},
            fill_value=0,
            margins=True,
            margins_name='Tổng'))
        out.index = out.index.droplevel(0)
        return out

    # Tạo file tạm và trường dữ liệu
    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()

    myheader = ['TT','Bên cung ứng DVMTR','Địa danh','Lô','Khoảnh','Tiểu khu','Diện tích cung ứng DVMTR (ha)','Hệ số K tổng hợp','Diện tích được chi trả tiền DVMTR (ha)','Tổng đơn giá','Mã lưu vực','Thành tiền']

    myField = QgsField( 'ID', QVariant.Double)
    temp.addAttribute(myField)

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()
    # Xong tạo file tạm
    # Một số biến tạm để đếm
    row_id = 0
    thutu = 0
    name = ''
    i = 0
    total_cungung = 0
    total_ct = 0
    total_thanhtien = 0

    for index,dtuong  in enumerate(ds_dtuong):

        row_id = row_id +1
        f = QgsFeature()
        f.setAttributes([row_id,dtuong_header[index][0],dtuong_header[index][1],'','','','','','','','','',''])
        temp.addFeature(f)


        df_doituong_con = df_group.get_group((maxa,dtuong))
        doituongX = tinh_doituong(df_doituong_con)

        for row in doituongX.itertuples():
            row_id = row_id +1

            dtich_cung_ung = str(round(float(row[1]),2))
            dtich_ct = str(round(float(row[2]),2))
            thanhtien = str(round(float(row[3]),0))

            if len(str(row[0][1])) == 0:
                cot3 = 'Cộng'
                total_cungung = total_cungung +float(row[1])
                total_ct = total_ct +float(row[2])
                total_thanhtien = total_thanhtien +float(row[3])
            else:
                cot3 = row[0][1]
                if row[0][1] != name:
                    thutu = thutu+1
                    name =  row[0][1]

            fet = QgsFeature()
            fet.setAttributes([row_id,thutu, cot3, row[0][2],row[0][3],row[0][4],row[0][5],dtich_cung_ung,row[0][6],dtich_ct,row[0][7],row[0][8], thanhtien])
            temp.addFeature(fet)

    row_id = row_id +1
    f = QgsFeature()
    f.setAttributes([row_id,'','Tổng','','','','',str(round(total_cungung,2)),'',str(round(total_ct,2)),'','',str(round(total_thanhtien,0))])
    temp.addFeature(f)

    temp.deleteAttributes([0])
    temp.updateFields()
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)
    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, output , "utf-8", layer.crs(), 'xlsx')

def hanhchinh_export(inPath,maxa,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['maxa'] == maxa)]
    df =df.loc[:, ['xa','huyen','tinh']]
    df.drop_duplicates(subset=['xa'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['Churung']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[1] + '-' + df[2]+ '-' +df[3]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def hanhchinh_HGD_HB_export(inPath,thonban,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['ddanh'] == thonban)]
    df =df.loc[:, ['ddanh','xa','huyen','tinh']]
    df.drop_duplicates(subset=['ddanh'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['Churung']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[1] + '-' + df[2]+ '-' +df[3]+'-' +df[4]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def hanhchinh_UBND_HB_export(inPath,xa,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['xa'] == xa)]
    df =df.loc[:, ['xa','huyen','tinh']]
    df.drop_duplicates(subset=['xa'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['Churung']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes(['UBND ' + df[1]+ '-' +df[2]+'-' +df[3]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def hanhchinh_NA_export(inPath,huyen,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['huyen'] == huyen)]
    df =df.loc[:, ['huyen','tinh']]
    df.drop_duplicates(subset=['huyen'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['Churung']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[1] + '-' + df[2]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def tinh_export(inPath,maxa,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['maxa'] == maxa)]
    df =df.loc[:, ['xa','huyen','tinh']]
    df.drop_duplicates(subset=['xa'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['tinh']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[3]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def huyen_export(inPath,maxa,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['maxa'] == maxa)]
    df =df.loc[:, ['xa','huyen']]
    df.drop_duplicates(subset=['xa'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['huyen']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[2]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def xa_export(inPath,maxa,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['maxa'] == maxa)]
    df =df.loc[:, ['xa']]
    df.drop_duplicates(subset=['xa'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['xa']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[1]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def convert_TVKD(text):
    patterns = {
        '[àáảãạăắằẵặẳâầấậẫẩ]': 'a',
        '[ÀÁẢÃẠĂẮẰẴẶẲÂẦẤẬẪẨ]' : 'A',
        '[đ]': 'd',
        '[Đ]': 'D',
        '[èéẻẽẹêềếểễệ]': 'e',
        '[ÈÉẺẼẸÊỀẾỂỄỆ]':'E',
        '[ìíỉĩị]': 'i',
        '[ÌÍỈĨỊ]': 'I',
        '[ÒÓỎÕỌÔỒỐỔỖỘƠỜỚỞỠỢ]': 'O',
        '[òóỏõọôồốổỗộơờớởỡợ]': 'o',
        '[ùúủũụưừứửữự]': 'u',
        '[ÙÚỦŨỤƯỪỨỬỮỰ]': 'U',
        '[ỳýỷỹỵ]': 'y',
        '[ỲÝỶỸỴ]': 'Y',
        '[`~@#$%^&*-<>?/\!-+!"]': '_',
        '[ ]' : ''
    }
    output = text
    for regex, replace in patterns.items():
        output = re.sub(regex, replace, output)
        # deal with upper case
        output = re.sub(regex.upper(), replace.upper(), output)
    return output

def report_forestActor(inpath,machur,outputpath):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    df_pfes = df_pfes.loc[(df_pfes['dtichct'] > 0)]
    def tinh_doituong(df_con):
        out = df_con.pivot_table(
            index=['lo', 'khoanh', 'tk', 'k0', 'k1', 'k2', 'k3', 'k4'],
            values=['dtich', 'dtichct'],
            aggfunc={'dtich': np.sum, 'dtichct': np.sum},
            fill_value=0,
            margins=True,
            margins_name='Tổng')
        return out
    # Tạo file tạm và trường dữ liệu
    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()

    myheader = ['TT','Lô','Khoảnh','Tiểu khu','Diện tích cung ứng DVMTR (ha)','Hệ số K','K1','K2','K3','K4','Diện tích được chi trả tiền DVMTR (ha)']
    myField = QgsField( 'ID', QVariant.Double)
    temp.addAttribute(myField)

    for head in myheader : 
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    # Một số biến tạm để đếm
    row_id = 0
    thutu = 0
    name = ''
    total_cungung = 0
    total_ct = 0
    
    doituongX = tinh_doituong(df_pfes)
    for row in doituongX.itertuples():    
        row_id = row_id +1
        dtich_cung_ung = str(round(float(row[1]),2))
        dtich_ct = str(round(float(row[2]),2))
        thutu = thutu+1        
        lo = row[0][0]        
        if row[0][0] == 'Tổng':
            lo = 'Tổng'

        fet = QgsFeature()
        fet.setAttributes([row_id,thutu,lo,row[0][1],row[0][2],dtich_cung_ung,row[0][3],row[0][4],row[0][5],row[0][6],row[0][7],dtich_ct])
        temp.addFeature(fet)

    temp.deleteAttributes([0])
    temp.updateFields()
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)
    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, outputpath , "utf-8", layer.crs(), 'xlsx')

def report_forestActor_HB(inpath,machur,outputpath):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    df_pfes = df_pfes.loc[(df_pfes['dtichct'] > 0)]
    def tinh_doituong(df_con):
        out = df_con.pivot_table(
            index=['huyen','xa','lo', 'khoanh','tk' , 'k0', 'dgia','maluuvuc'],
            values=['dtich', 'dtichct','thanhtien'],
            aggfunc={'dtich': np.sum, 'dtichct': np.sum, 'thanhtien': np.sum},
            fill_value=0,
            margins=True,
            margins_name='Tổng')
        return out
    # Tạo file tạm và trường dữ liệu
    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()

    myheader = ['TT','Huyện','Xã','Lô','Khoảnh','Tiểu khu','Diện tích cung ứng DVMTR (ha)','Hệ số K tổng hợp','Diện tích được chi trả tiền DVMTR (ha)','Tổng đơn giá chi trả','Mã lưu vực','Thành tiền',]
    myField = QgsField( 'ID', QVariant.Double)
    temp.addAttribute(myField)

    for head in myheader : 
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    # Một số biến tạm để đếm
    row_id = 0
    thutu = 0
    name = ''
    total_cungung = 0
    total_ct = 0
    total_thanhtien = 0
    
    doituongX = tinh_doituong(df_pfes)
    for row in doituongX.itertuples():    
        row_id = row_id +1
        dtich_cung_ung = str(round(float(row[1]),2))
        dtich_ct = str(round(float(row[2]),2))
        thanhtien = str(round(float(row[3]),0))

        thutu = thutu+1        
        lo = row[0][0]        
        if row[0][0] == 'Tổng':
            lo = 'Tổng'

        fet = QgsFeature()
        fet.setAttributes([row_id,thutu,lo,row[0][1],row[0][2],row[0][3],row[0][4],dtich_cung_ung,row[0][5],dtich_ct,row[0][6],row[0][7],thanhtien])
        temp.addFeature(fet)

    temp.deleteAttributes([0])
    temp.updateFields()
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)
    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, outputpath , "utf-8", layer.crs(), 'xlsx')

def formCR1_SL(inpath,maxa,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['maxa'] == maxa)]
    df_pfes = df_pfes.loc[(df_pfes['dtuong'] < 4)]
    df_pfes = df_pfes.loc[(df_pfes['dtichct'] > 0)]
    pv = df_pfes.groupby(['nqh','dtuong','ddanh','churung']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich','dtichct'], 
        index=['churung','cmt','gcn','tk','khoanh','lo','k0','k1','k2','k3','k4'], 
        aggfunc= {'dtich': np.sum, 'dtichct':np.sum },
        margins = True, 
        margins_name='Cộng')).to_excel(output)

def formCR2_SL(inpath,machur,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    df_pfes = df_pfes.loc[(df_pfes['dtichct'] > 0)]
    pv = df_pfes.groupby(['nqh']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich','dtichct'], 
        index=['huyen','xa','tk','khoanh','lo','ldlr','k0'], 
        aggfunc= {'dtich': np.sum, 'dtichct':np.sum })).to_excel(output)

def formCR1_nggocr_NA(inpath,mahuyen,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['mahuyen'] == mahuyen)]
    pv = df_pfes.groupby(['xa','cql']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich'], 
        index=['dtuongnk'], 
        columns=['nggocr', 'nqh'], 
        aggfunc= np.sum ,
        margins = True, 
        margins_name='Tổng')).to_excel(output)

def formCR1_3lr_NA(inpath,mahuyen,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['mahuyen'] == mahuyen)]
    pv = df_pfes.groupby(['xa','cql']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich'], 
        index=['dtuongnk'], 
        columns=['malr3', 'thanhrung'], 
        aggfunc= np.sum ,
        margins = True, 
        margins_name='Tổng')).to_excel(output)

def formCR1_lv_NA(inpath,mahuyen,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['mahuyen'] == mahuyen)]
    pv = df_pfes.pivot_table(   
        index=['xa','cql','dtuongnk','maluuvuc'],
        values=['dtich'], 
        columns=['thanhrung'], 
        aggfunc= np.sum ,
        ).to_excel(output)

def formCR2_nggocr_NA(inpath,machur,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    if machur == 9002 or machur == 9005:
        pv = df_pfes.groupby(['tinh','huyen']).apply(lambda sub_df: sub_df.pivot_table(
            values=['dtich'], 
            index=['dtuongnk'], 
            columns=['nggocr', 'nqh'], 
            aggfunc= np.sum ,
            margins = True, 
            margins_name='Tổng')).to_excel(output)
    else :
        pv = df_pfes.groupby(['huyen','xa']).apply(lambda sub_df: sub_df.pivot_table(
            values=['dtich'], 
            index=['dtuongnk'], 
            columns=['nggocr', 'nqh'], 
            aggfunc= np.sum ,
            margins = True, 
            margins_name='Tổng')).to_excel(output)

def formCR2_3lr_NA(inpath,machur,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    if machur == 9002 or machur == 9005:
        pv = df_pfes.groupby(['tinh','huyen']).apply(lambda sub_df: sub_df.pivot_table(
            values=['dtich'], 
            index=['dtuongnk'], 
            columns=['malr3', 'thanhrung'], 
            aggfunc= np.sum ,
            margins = True, 
            margins_name='Tổng')).to_excel(output)
    else:
        pv = df_pfes.groupby(['huyen','xa']).apply(lambda sub_df: sub_df.pivot_table(
            values=['dtich'], 
            index=['dtuongnk'], 
            columns=['malr3', 'thanhrung'], 
            aggfunc= np.sum ,
            margins = True, 
            margins_name='Tổng')).to_excel(output)

def formCR2_lv_NA(inpath,machur,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    if machur == 9002 or machur == 9005:
        pv = df_pfes.pivot_table(   
            index=['huyen','dtuongnk','maluuvuc'],
            values=['dtich'], 
            columns=['thanhrung'], 
            aggfunc= np.sum ,
            ).to_excel(output)
    else:
        pv = df_pfes.pivot_table(   
            index=['xa','dtuongnk','maluuvuc'],
            values=['dtich'], 
            columns=['thanhrung'], 
            aggfunc= np.sum ,
            ).to_excel(output)

def formCR1_TH(inpath,maxa,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['maxa'] == maxa)]
    df_pfes = df_pfes.loc[(df_pfes['dtuong'] == 1)]
    df_pfes = df_pfes.loc[(df_pfes['dtichct'] > 0)]
    pv = df_pfes.groupby(['churung']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtichgk','dtich','dtichct','thanhtien'], 
        index=['churung','nguq','cmt','ngaycap','lo','khoanh','tk','k0','dgia'], 
        aggfunc= {'dtichgk':np.sum,'dtich': np.sum, 'dtichct':np.sum,'thanhtien': np.sum},
        margins = True, 
        margins_name='Cộng')).to_excel(output)

def formCR1_nggocr_QTI(inpath,mahuyen,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['mahuyen'] == mahuyen)]
    pv = df_pfes.groupby(['xa','dtuong']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich'], 
        index=['churung'], 
        columns=['nggocr', 'nqh'], 
        aggfunc= np.sum ,
        margins = True, 
        margins_name='Tổng')).to_excel(output)

def formCR1_3lr_QTI(inpath,mahuyen,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['mahuyen'] == mahuyen)]
    pv = df_pfes.groupby(['xa','dtuong']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich'], 
        index=['churung'], 
        columns=['malr3', 'thanhrung'], 
        aggfunc= np.sum ,
        margins = True, 
        margins_name='Tổng')).to_excel(output)

def formCR1_lv_QTI(inpath,mahuyen,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['mahuyen'] == mahuyen)]
    pv = df_pfes.pivot_table(   
        index=['xa','dtuong','churung','maluuvuc'],
        values=['dtich'], 
        columns=['thanhrung'], 
        aggfunc= np.sum ,
        ).to_excel(output)

def formCR2_nggocr_QTI(inpath,machur,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    pv = df_pfes.groupby(['tinh','huyen']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich'], 
        index=['churung'], 
        columns=['nggocr', 'nqh'], 
        aggfunc= np.sum ,
        margins = True, 
        margins_name='Tổng')).to_excel(output)

def formCR2_3lr_QTI(inpath,machur,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    pv = df_pfes.groupby(['tinh','huyen']).apply(lambda sub_df: sub_df.pivot_table(
        values=['dtich'], 
        index=['churung'], 
        columns=['malr3', 'thanhrung'], 
        aggfunc= np.sum ,
        margins = True, 
        margins_name='Tổng')).to_excel(output)

def formCR2_lv_QTI(inpath,machur,output):
    df_pfes = pd.read_excel(inpath)
    df_pfes.columns = [x.lower() for x in df_pfes.columns]
    df_pfes = df_pfes.loc[(df_pfes['machur'] == machur)]
    pv = df_pfes.pivot_table(   
        index=['huyen','churung','maluuvuc'],
        values=['dtich'], 
        columns=['thanhrung'], 
        aggfunc= np.sum ,
        ).to_excel(output)

def churung_export(inPath,machurung,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['chitra'] == 1)]
    df = df.loc[(df['machur'] == machurung)]
    df = df.loc[:, ['churung']]
    df.drop_duplicates(subset=['churung'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['Churung']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[1]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def province_export(inPath,machurung,export):
    df = pd.read_excel(inPath)
    df.columns = [x.lower() for x in df.columns]
    df = df.loc[(df['dgia'] > 0)]
    df = df.loc[(df['machur'] == machurung)]
    df =df.loc[:, ['tinh']]
    df.drop_duplicates(subset=['tinh'], keep='first', inplace=True)

    temp = QgsVectorLayer("none","result","memory")
    temp_data = temp.dataProvider()
    temp.startEditing()
    myheader = ['tinh']

    for head in myheader :
        myField = QgsField( head, QVariant.String )
        temp.addAttribute(myField)
    temp.updateFields()

    for df in df.itertuples():
        f = QgsFeature()
        f.setAttributes([df[1]])
        temp.addFeature(f)
    temp.commitChanges()
    QgsProject.instance().addMapLayer(temp)

    layer = iface.activeLayer()
    QgsVectorFileWriter.writeAsVectorFormat(layer, export, "utf-8", layer.crs(), 'xlsx')

def province_replace(inPath):
    conf_dir = str(Path(__file__).parent.absolute()) + '/data/configFolder.txt'
    f = open(conf_dir, 'r')
    conf_folder = f.read()
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    dstinh = []
    for feature in layer:
        dstinh.append(feature['matinh'])
    province = list(set(dstinh))

    for tinh in province:
        if os.path.exists(conf_folder + "/Layout/CapTinh/Tinh_"+ str(tinh)):
            layer = QgsVectorLayer(inPath, '', 'ogr')
            QgsProject.instance().addMapLayer(layer)
            processing.run("qgis:selectbyattribute", {"FIELD":'matinh', "INPUT":layer, "METHOD": 0,"OPERATOR": 0,"VALUE":tinh})
            export = conf_folder + "/Layout/CapTinh/"+"Tinh_"+ str(tinh) + "/Tinh_"+ str(tinh) + ".shp"
            QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile", onlySelected=True)

def district_replace(inPath):
    conf_dir = str(Path(__file__).parent.absolute()) + '/data/configFolder.txt'
    f = open(conf_dir, 'r')
    conf_folder = f.read()
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    dshuyen = []
    for feature in layer:
        dshuyen.append(feature['mahuyen'])
    district = list(set(dshuyen))

    for huyen in district:
        if os.path.exists(conf_folder + "/Layout/CapHuyen/Huyen_"+ str(huyen)):
            layer = QgsVectorLayer(inPath, '', 'ogr')
            QgsProject.instance().addMapLayer(layer)
            processing.run("qgis:selectbyattribute", {"FIELD":'mahuyen', "INPUT":layer, "METHOD": 0,"OPERATOR": 0,"VALUE":huyen})
            export = conf_folder + "/Layout/CapHuyen/"+"Huyen_"+ str(huyen) + "/Huyen_"+ str(huyen) + ".shp"
            QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile", onlySelected=True)

def commune_replace(inPath):
    conf_dir = str(Path(__file__).parent.absolute()) + '/data/configFolder.txt'
    f = open(conf_dir, 'r')
    conf_folder = f.read()
    driver = ogr.GetDriverByName("ESRI Shapefile")
    dataSource = driver.Open(inPath, 1)
    layer = dataSource.GetLayer()

    dsxa = []
    for feature in layer:
        dsxa.append(feature['maxa'])
    commune = list(set(dsxa))

    for xa in commune:
        if os.path.exists(conf_folder + "/Layout/CapXa/Xa_"+ str(xa)):
            layer = QgsVectorLayer(inPath, '', 'ogr')
            QgsProject.instance().addMapLayer(layer)
            processing.run("qgis:selectbyattribute", {"FIELD":'maxa', "INPUT":layer, "METHOD": 0,"OPERATOR": 0,"VALUE":xa})
            export = conf_folder + "/Layout/CapXa/"+"Xa_"+ str(xa) + "/Xa_"+ str(xa) + ".shp"
            QgsVectorFileWriter.writeAsVectorFormat(layer, export, "UTF-8", layer.crs(), "ESRI Shapefile", onlySelected=True)

def openLayout(code,conf_folder):
    caphc = code.split("_")[0]
    if caphc == "Tinh":
        project = QgsProject.instance()
        project.read(conf_folder+ "/Layout/CapTinh/"+ code + "/" + code + ".qgz")       
        
    if caphc == "Huyen":
        project = QgsProject.instance()
        project.read(conf_folder+ "/Layout/CapHuyen/"+ code + "/" + code + ".qgz")        

    if caphc == "Xa":
        project = QgsProject.instance()       
        project.read(conf_folder+ "/Layout/CapXa/"+ code + "/" + code + ".qgz")


    
   
        
