celery/service.py

import xml.etree.ElementTree as ET
from typing import Union
import os
import json
import uuid
import re
from pydantic import BaseModel
import pandas as pd
from numpy import float64, int64
import numpy as np




def chaifen(result, zhuanye):
    clde = result['clde']
    if clde == None:
        return result
    if zhuanye == 10:
        pbf = pd.read_csv("de/tj/JD_PeiBiF.csv")
        clk = pd.read_csv("de/tj/JD_CaiLiaoK.csv")
    elif zhuanye == 20:
        pbf = pd.read_csv("de/sz/JD_PeiBiF.csv")
        clk = pd.read_csv("de/sz/JD_CaiLiaoK.csv")
    elif zhuanye == 30:
        pbf = pd.read_csv("de/az/JD_PeiBiF.csv")
        clk = pd.read_csv("de/az/JD_CaiLiaoK.csv")
    elif zhuanye == 40:
        pbf = pd.read_csv("de/yl/JD_PeiBiF.csv")
        clk = pd.read_csv("de/yl/JD_CaiLiaoK.csv")
    elif zhuanye == 50:
        pbf = pd.read_csv("de/xstj/JD_PeiBiF.csv")
        clk = pd.read_csv("de/xstj/JD_CaiLiaoK.csv")
    else:
        pbf = pd.read_csv("de/xsaz/JD_PeiBiF.csv")
        clk = pd.read_csv("de/xsaz/JD_CaiLiaoK.csv")
    for entry in clde:
        filtered = pbf[pbf["PBBH"]==entry['CLBH']]
        if len(filtered) > 0:
            entry["children"] = []
            ids = filtered['RcjID'].tolist()
            cls = clk[clk['RcjID'].isin(ids)]
            joined = pd.merge(filtered, cls, on='RcjID')
            for row in joined.itertuples():
                CLBH = row.CLBH
                CLMC = row.CLMC
                JLDW = row.JLDW
                YSJG = row.Csdj
                SL = float(row.SL) * float(entry['SL'])
                HJ = float(row.HJ) * float(entry['SL'])
                entry['children'].append({'CLBH': CLBH,
                                          'CLMC': CLMC,
                                          'JLDW': JLDW,
                                          'YSJG': YSJG,
                                          'SL': SL,
                                          'HJ': HJ
                                          })


                


    ##print(result)
    return result





def getQingdanTuijian(bh, bt, bm):
    result = []
    try:
        all = pd.read_csv("QingDanAllInOne.csv")
        bm_ = int(bm) // 1000
        hit = all[all["qdbh"] == str(bm_)]
        for i in range(len(hit)):
            result.append({'ID': hit.iloc[i]["index"].item(), '定额编号': hit.iloc[i]["debh"], '工程量名称': hit.iloc[i]["gclmc"], '工作内容': hit.iloc[i]["work"]})
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    finally:

        return result







def getQdmx(root, bh, bt):
    result = []
    #result = [["序号", "清单编码", "名称", "项目特征", "计算规则", "单位", "数量", "综合单价", "合价", "人工费", "主材费", "设备费", "辅材费", "材料费", "机械费", "管理费", "利润" , "暂估价", "综合人工工日", "备注" ]]
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == bh:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Qdxm':
            Fywj= child
            break
    container = None
    for child in Fywj:
        ##child is mx
        

        if child.tag == "QdBt":
            if child.attrib["Mc"] == bt:
                count = 0
                for grandchild in child:
                    count = count + 1
                if count > 0:
                    
                    container = child
                    break
   
    
    if  container == None:
        container = Fywj
    
    
    for child in container:
        
        #result = [["序号", "清单编码", "名称", "项目特征", "计算规则", "单位", "数量", "综合单价", "合价", "人工费", "主材费",
        #  "设备费", "辅材费", "材料费", "机械费", "管理费", "利润" , "暂估价", "综合人工工日", "备注" ]]
        result.append({
            "操作" : "",
            "序号": child.attrib["Xh"], "清单编码" : child.attrib["Qdbm"], "名称" : child.attrib["Mc"],"项目特征" : child.attrib["Xmtz"],
            "计算规则" : child.attrib["Jsgz"],
            "单位" : child.attrib["Dw"],
            "数量": child.attrib["Sl"],
            "综合单价" : child.attrib["Zhdj"],
            "合价" : child.attrib["Zhhj"],
            "人工费": child.attrib["Rgf"],
            "主材费" : child.attrib["Zcf"],
            "设备费": child.attrib["Sbf"],
            "辅材费": child.attrib["Fcf"],
            "材料费" : child.attrib["Clf"],
            "机械费" : child.attrib["Jxf"],
            "管理费": child.attrib["Glf"],
            "利润": child.attrib["Lr"],
            "暂估价" : child.attrib["Zgj"],
            "综合人工工日" : child.attrib["Zgr"],
            "备注" : child.attrib["Bz"]}
        )
        for child_zj in child:
            if child_zj.tag == "Qdxdezj":
                for child_mx in child_zj:
                    if child_mx.tag == "QdxdezjMx":
                        key = str(uuid.uuid4())
                        child_mx.set("key", key)
                        ##print("set key")
                        ##print(key)
                        ##print(child_mx)
                        parent = result[-1]
                        if "__children" in parent:
                            parent["__children"].append({
                                "key": key,
                                "操作" : "",
                                "序号": None, "清单编码" : child_mx.attrib["Debm"], "名称" : child_mx.attrib["Mc"],"项目特征" : None,
                                "计算规则" : None,
                                "单位" : child_mx.attrib["Dw"],
                                "数量": child_mx.attrib["DwQdSl"],
                                "综合单价" : child_mx.attrib["Dj"],
                                "合价" : child_mx.attrib["Hj"],
                                "人工费": child_mx.attrib["Rgf"],
                                "主材费" : child_mx.attrib["Zcf"],
                                "设备费": child_mx.attrib["Sbf"],
                                "辅材费": child_mx.attrib["Fcf"],
                                "材料费" : child_mx.attrib["Clf"],
                                "机械费" : child_mx.attrib["Jxf"],
                                "管理费": child_mx.attrib["Glf"],
                                "利润": child_mx.attrib["Lr"],
                                "暂估价" : None,
                                "综合人工工日" : None,
                                "备注" : None}

                            )
                        else:
                            parent["__children"] = [{
                                "key": key,
                                "操作" : "",
                                "序号": None, "清单编码" : child_mx.attrib["Debm"], "名称" : child_mx.attrib["Mc"],"项目特征" : None,
                                "计算规则" : None,
                                "单位" : child_mx.attrib["Dw"],
                                "数量": child_mx.attrib["DwQdSl"],
                                "综合单价" : child_mx.attrib["Dj"],
                                "合价" : child_mx.attrib["Hj"],
                                "人工费": child_mx.attrib["Rgf"],
                                "主材费" : child_mx.attrib["Zcf"],
                                "设备费": child_mx.attrib["Sbf"],
                                "辅材费": child_mx.attrib["Fcf"],
                                "材料费" : child_mx.attrib["Clf"],
                                "机械费" : child_mx.attrib["Jxf"],
                                "管理费": child_mx.attrib["Glf"],
                                "利润": child_mx.attrib["Lr"],
                                "暂估价" : None,
                                "综合人工工日" : None,
                                "备注" : None}]



    return result

def getQdmx_default(root, bh, bt):
    result = []
    #result = [["序号", "清单编码", "名称", "项目特征", "计算规则", "单位", "数量", "综合单价", "合价", "人工费", "主材费", "设备费", "辅材费", "材料费", "机械费", "管理费", "利润" , "暂估价", "综合人工工日", "备注" ]]
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == bh:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Qdxm':
            Fywj= child
            break
    container = None
    for child in Fywj:
        ##child is mx
        

        if child.tag == "Qdmx":

        #result = [["序号", "清单编码", "名称", "项目特征", "计算规则", "单位", "数量", "综合单价", "合价", "人工费", "主材费",
        #  "设备费", "辅材费", "材料费", "机械费", "管理费", "利润" , "暂估价", "综合人工工日", "备注" ]]
            result.append({
            "操作" : "",
            "序号": child.attrib["Xh"], "清单编码" : child.attrib["Qdbm"], "名称" : child.attrib["Mc"],"项目特征" : child.attrib["Xmtz"],
            "计算规则" : child.attrib["Jsgz"],
            "单位" : child.attrib["Dw"],
            "数量": child.attrib["Sl"],
            "综合单价" : child.attrib["Zhdj"],
            "合价" : child.attrib["Zhhj"],
            "人工费": child.attrib["Rgf"],
            "主材费" : child.attrib["Zcf"],
            "设备费": child.attrib["Sbf"],
            "辅材费": child.attrib["Fcf"],
            "材料费" : child.attrib["Clf"],
            "机械费" : child.attrib["Jxf"],
            "管理费": child.attrib["Glf"],
            "利润": child.attrib["Lr"],
            "暂估价" : child.attrib["Zgj"],
            "综合人工工日" : child.attrib["Zgr"],
            "备注" : child.attrib["Bz"]}
            )
            for child_zj in child:
                if child_zj.tag == "Qdxdezj":
                    for child_mx in child_zj:
                        if child_mx.tag == "QdxdezjMx":
                            key = str(uuid.uuid4())
                            child_mx.set("key", key)
                            ##print("set key")
                            ##print(key)
                            ##print(child_mx)
                            parent = result[-1]
                            if "__children" in parent:
                                parent["__children"].append({
                                "key": key,    
                                "操作" : "",
                                "序号": None, "清单编码" : child_mx.attrib["Debm"], "名称" : child_mx.attrib["Mc"],"项目特征" : None,
                                "计算规则" : None,
                                "单位" : child_mx.attrib["Dw"],
                                "数量": child_mx.attrib["DwQdSl"],
                                "综合单价" : child_mx.attrib["Dj"],
                                "合价" : child_mx.attrib["Hj"],
                                "人工费": child_mx.attrib["Rgf"],
                                "主材费" : child_mx.attrib["Zcf"],
                                "设备费": child_mx.attrib["Sbf"],
                                "辅材费": child_mx.attrib["Fcf"],
                                "材料费" : child_mx.attrib["Clf"],
                                "机械费" : child_mx.attrib["Jxf"],
                                "管理费": child_mx.attrib["Glf"],
                                "利润": child_mx.attrib["Lr"],
                                "暂估价" : None,
                                "综合人工工日" : None,
                                "备注" : None}

                            )
                            else:
                                parent["__children"] = [{
                                "key": key,
                                "操作" : "",
                                "序号": None, "清单编码" : child_mx.attrib["Debm"], "名称" : child_mx.attrib["Mc"],"项目特征" : None,
                                "计算规则" : None,
                                "单位" : child_mx.attrib["Dw"],
                                "数量": child_mx.attrib["DwQdSl"],
                                "综合单价" : child_mx.attrib["Dj"],
                                "合价" : child_mx.attrib["Hj"],
                                "人工费": child_mx.attrib["Rgf"],
                                "主材费" : child_mx.attrib["Zcf"],
                                "设备费": child_mx.attrib["Sbf"],
                                "辅材费": child_mx.attrib["Fcf"],
                                "材料费" : child_mx.attrib["Clf"],
                                "机械费" : child_mx.attrib["Jxf"],
                                "管理费": child_mx.attrib["Glf"],
                                "利润": child_mx.attrib["Lr"],
                                "暂估价" : None,
                                "综合人工工日" : None,
                                "备注" : None}]



    return result




def getQdxm_addition(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Qdxm':
            Fywj= child
            break
    
    for child in Fywj:
        ##child is mx
        if child.tag == "QdBt":
            count = 0
            for grandchild in child:
                count = count + 1
            if count > 0:
                result.append({'Bz': child.attrib["Bz"], 'Je': child.attrib['Je'], 'Mc': child.attrib['Mc'], 'Xh': child.attrib['Xh'], 'Zgj': child.attrib['Zgj']})
    
    return result




def getQdxm(root, id):###qdxm下面既有qdmx,也有qdbt，qdbt可能为空
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Qdxm':
            Fywj= child
            break
    
    for child in Fywj:
        ##child is mx
        if child.tag == 'Qdmx':
            if 'Default' in result:
                pass
            else:
                result.append('Default')
        if child.tag == "QdBt":
            count = 0
            for grandchild in child:
                count = count + 1
            if count > 0:
                result.append(child.attrib["Mc"])
    
    return result

def getZjcs(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Csxm':
            Fywj= child
            break
    Zjcs_parent = None
    for child in Fywj:
        if child.tag == 'ZjCs':
            Zjcs_parent = child
            break
    
    for child in Zjcs_parent:
        ##child is mx {
        result.append({    
                    "序号" : child.attrib["Xh"], "清单编码": child.attrib["Bm"], "名称" : child.attrib["Mc"],
                     "计算基数" : child.attrib["Js"],
                     "计算基础" : child.attrib["Jsjc"], "费率" : child.attrib["Fl"],
                      "金额" : child.attrib["Je"], "项目类别" : child.attrib["Xmlb"]})
        __children = []
        for grandchild in child:
            __children.append({
                    "序号" : None, "清单编码": None, "名称" : grandchild.attrib["Mc"],
                     "计算基数" : grandchild.attrib["Js"],
                     "计算基础" : grandchild.attrib["Jsjc"], "费率" : grandchild.attrib["Fl"],
                      "金额" : grandchild.attrib["Je"], "项目类别" : grandchild.attrib["Xmlb"]})

        if len(__children) > 0:
            result[-1]["__children"] = __children 



    return result

def getZcbfwf(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Zcbfwf':
            Fywj= child
            break
    for child in Fywj:
            result.append(
                {'序号': child.attrib["Xh"], 
            "名称" : child.attrib["Mc"],  "项目价值" : child.attrib["Xmjz"], "服务内容" :child.attrib["Fwnr"], "计算基础" :child.attrib["Jsjc"],
            "费率" :child.attrib["Fl"], "金额" :child.attrib["Je"]}
            )
    return result

def getRcjhz(root, id):
    result = [[ "ID", "编码", "名称", "规格型号", "单位", "数量", "单价", "合价", "产地", 
    "供应厂商", "人材机类别", "主要材料标志", "主材标志", "设备标志", "甲供标志", "暂估价标志"]]


    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Rcjhz':
            Fywj= child
            break
    for child in Fywj:
            result.append(
                [
            child.attrib["RcjId"],  child.attrib["RcjBm"], child.attrib["Mc"], child.attrib["Ggxh"],
            child.attrib["Dw"], child.attrib["Sl"], child.attrib["Dj"], child.attrib["Hj"], 
            child.attrib["Cd"], child.attrib["Gycs"], child.attrib["Rcjlb"],
            child.attrib["Zyclbz"], child.attrib["Zcbz"], child.attrib["Sbbz"], child.attrib["Jgbz"],
            child.attrib["Zgjbz"]]
            )
    return result  

def getFbrgycl(root, id):
    result = []


    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Fbrgycl':
            Fywj= child
            break
    for child in Fywj:
            result.append(
                {'序号': child.attrib["Xh"], 
            'ID': child.attrib["RcjId"],  '材料编号': child.attrib["Clbh"], '名称': child.attrib["Mc"], '规格型号': child.attrib["Ggxh"],
            '单位': child.attrib["Dw"], '数量': child.attrib["Sl"], '单价': child.attrib["Dj"], '合价': child.attrib["Hj"], '交货方式': child.attrib["Jhfs"],
            '送达地点': child.attrib["Sddd"], '备注': child.attrib["Bz"]}
            )
    return result   

def getJrg(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Jrg':
            Fywj= child
            break
    for child in Fywj:
            result.append(
                {'序号': child.attrib["Xh"], 
                  '名称': child.attrib["Mc"],  '金额': child.attrib["Je"], '类别': child.attrib["Lb"]}
            )
    return result 

def getZlje(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Zlje':
            Fywj= child
            break
    for child in Fywj:
            result.append(
                {'序号': child.attrib["Xh"], 
            '名称': child.attrib["Mc"],   '单位': child.attrib["Dw"], '暂定金额': child.attrib["Zdje"], '备注': child.attrib["Bz"]}
            )
    return result  


def getZygczgj(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Zygczg':
            Fywj= child
            break
    for child in Fywj:
            result.append(
                {'序号': child.attrib["Xh"], 
            '名称': child.attrib["Mc"],  '工程内容': child.attrib["Gcnr"], '金额': child.attrib["Je"], '备注': child.attrib["Bz"]}
            )
    return result  

def getQtxm(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Qtxm':
            Fywj= child
            break
    for child in Fywj:
        ##child is mx
        xh = child.attrib["Xh"]
        if "." in xh:
            count = 0
            for entry in xh:
                if entry == ".":
                    count = count + 1
            if count == 1:
                parent = result[-1]
                if "__children" in parent:
                    children = parent["__children"]
                    children.append(

                      {"序号":xh, "名称": child.attrib["Mc"], 
                       "金额": child.attrib["Je"],  "项目类别" : child.attrib["Xmlb"], "备注" : child.attrib["Bz"]}
                    )
                else:
                    parent["__children"] = [
                {"序号":xh, "名称": child.attrib["Mc"], 
                 "金额": child.attrib["Je"],  "项目类别" : child.attrib["Xmlb"], "备注" : child.attrib["Bz"]}]
                    

        else:
            result.append(
                {"序号":xh, "名称": child.attrib["Mc"], 
            "金额": child.attrib["Je"],  "项目类别" : child.attrib["Xmlb"], "备注" : child.attrib["Bz"]}
            )
    return result    

def getGfsj(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Gfsj':
            Fywj= child
            break
    for child in Fywj:
        ##child is mx
        xh = child.attrib["Xh"]
        if "." in xh:
            count = 0
            for entry in xh:
                if entry == ".":
                    count = count + 1
            if count == 1:
                parent = result[-1]
                if "__children" in parent:
                    children = parent["__children"]
                    children.append({"序号":xh, "名称": child.attrib["Mc"], "取费基数": child.attrib["Qfjs"], "计算基础": child.attrib["Jsjc"], "费率": child.attrib['Fl'], "金额": child.attrib["Je"],  "类别" : child.attrib["Fyxlb"]})
                else:
                    parent["__children"] = [{"序号":xh, "名称": child.attrib["Mc"], "取费基数": child.attrib["Qfjs"], "计算基础": child.attrib["Jsjc"], "费率": child.attrib['Fl'], "金额": child.attrib["Je"],  "类别" : child.attrib["Fyxlb"]}]
            elif count == 2:
                parent = result[-1]
                if "__children" in parent:
                    parent = parent["__children"][-1]
                    ##print(parent)
                    if "__children" in parent:
                        children = parent["__children"]
                        children.append({"序号":xh, "名称": child.attrib["Mc"], "取费基数": child.attrib["Qfjs"], "计算基础": child.attrib["Jsjc"], "费率": child.attrib['Fl'], "金额": child.attrib["Je"],  "类别" : child.attrib["Fyxlb"]})
                        
                    else:
                        parent["__children"] = [{"序号":xh, "名称": child.attrib["Mc"], "取费基数": child.attrib["Qfjs"], "计算基础": child.attrib["Jsjc"], "费率": child.attrib['Fl'], "金额": child.attrib["Je"],  "类别" : child.attrib["Fyxlb"]}]
                    

        else:
            ###["序号", "名称", "取费基数", "计算基础", "金额", "类别"]);
            result.append({"序号":xh, "名称": child.attrib["Mc"], "取费基数": child.attrib["Qfjs"], "计算基础": child.attrib["Jsjc"], "费率": child.attrib['Fl'], "金额": child.attrib["Je"],  "类别" : child.attrib["Fyxlb"]})



    return result

def getBjhz(root, id):
    ##print(root)
    ##print(id)
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    ##print(item)
    Fywj = None
    for child in item:
        if child.tag == 'Fywj':
            Fywj= child
            break
    for child in Fywj:
        ##child is mx
        xh = child.attrib["Xh"]
        if "." in xh:
            count = 0
            for entry in xh:
                if entry == ".":
                    count = count + 1
            if count == 1:
                parent = result[-1]
                if "__children" in parent:
                    children = parent["__children"]
                    children.append({"序号":xh, "名称": child.attrib["Mc"], "金额": child.attrib["Je"],   "暂估价" : child.attrib["Zgj"], "类别" : child.attrib["Fyxlb"]})
                else:
                    parent["__children"] = [{"序号":xh, "名称": child.attrib["Mc"], "金额": child.attrib["Je"],   "暂估价" : child.attrib["Zgj"], "类别" : child.attrib["Fyxlb"]}]
            elif count == 2:
                parent = result[-1]
                if "__children" in parent:
                    parent = parent["__children"][-1]
                    ##print(parent)
                    if "__children" in parent:
                        children = parent["__children"]
                        children.append({"序号":xh, "名称": child.attrib["Mc"], "金额": child.attrib["Je"],   "暂估价" : child.attrib["Zgj"], "类别" : child.attrib["Fyxlb"]})
                        
                    else:
                        parent["__children"] = [{"序号":xh, "名称": child.attrib["Mc"], "金额": child.attrib["Je"],   "暂估价" : child.attrib["Zgj"], "类别" : child.attrib["Fyxlb"]}]
                    

        else:
            ###"序号", "名称", "金额", "暂估价", "类别"]
            result.append({"序号":xh, "名称": child.attrib["Mc"], "金额": child.attrib["Je"], 
             "暂估价" : child.attrib["Zgj"], "类别" : child.attrib["Fyxlb"]})



    return result

def processQds(dataframe, from_, end, level):
    result = []
    for i  in range(from_, end):
        item = dataframe.iloc[i]
        fbcch = str(item['fbcch'])
        if fbcch == str(level):
            entry = {"id": str(item["ID"]),"key": str(item["ID"]), "value": str(item["ID"]), "name": str(item['qdbh']) + " " + str(item["xmmc"]), "title": str(item['qdbh'])}
            next = i + 1
            while next < end and str(dataframe.iloc[next]["fbcch"]) != str(level):
                next = next + 1
            entry["children"] = processQds(dataframe, i + 1, next, level + 1)
            result.append(entry)
    return result

def processDes(dataframe, from_, end, level):
    result = []
    for i  in range(from_, end):
        item = dataframe.iloc[i]
        fbcch = str(item['fbcch'])
        if fbcch == str(level):
            entry = {"id": str(item["ID"]),"key": str(item["ID"]), "value": str(item["ID"]), "label": str(item["zjbt"]), "title": str(item["zjbt"])}
            next = i + 1
            while next < end and str(dataframe.iloc[next]["fbcch"]) != str(level):
                next = next + 1
            entry["children"] = processDes(dataframe, i + 1, next, level + 1)
            result.append(entry)
    return result

def processPbs(dataframe, from_, end, level):
    result = []
    for i  in range(from_, end):
        item = dataframe.iloc[i]
        fbcch = str(item['fbcch'])
        if fbcch == str(level):
            entry = {"id": str(item["ID"]),"key": str(item["ID"]), "value": str(item["ID"]), "label": str(item["flmc"]),"title": str(item["flmc"]), "data": item["pblbh"]}
            next = i + 1
            while next < end and str(dataframe.iloc[next]["fbcch"]) != str(level):
                next = next + 1
            entry["children"] = processPbs(dataframe, i + 1, next, level + 1)
            result.append(entry)
    return result

def processJxs(dataframe, from_, end, level):
    result = []
    for i  in range(from_, end):
        item = dataframe.iloc[i]
        fbcch = str(item['fbcch'])
        if fbcch == str(level):
            entry = {"id": str(item["ID"]),"key": str(item["ID"]), "value": str(item["ID"]), "label": str(item["flmc"]),"title": str(item["flmc"]), "data": item["jxlbh"]}
            next = i + 1
            while next < end and str(dataframe.iloc[next]["fbcch"]) != str(level):
                next = next + 1
            entry["children"] = processJxs(dataframe, i + 1, next, level + 1)
            result.append(entry)
    return result

def processCls(dataframe, from_, end, level):
    result = []
    for i  in range(from_, end):
        item = dataframe.iloc[i]
        fbcch = str(item['fbcch'])
        if fbcch == str(level):
            entry = {"id": str(item["ID"]),"key": str(item["ID"]), "value": str(item["ID"]), "label": str(item["flmc"]),"title": str(item["flmc"]), "data": item["cllbh"].item() if type(item["cllbh"]) == int64 else item["cllbh"]}
            ##print(entry)
            next = i + 1
            while next < end and str(dataframe.iloc[next]["fbcch"]) != str(level):
                next = next + 1
            entry["children"] = processCls(dataframe, i + 1, next, level + 1)
            result.append(entry)
    return result


def getCankao():
    tree = ET.parse('de/DeData_Fl.xml')
    root = tree.getroot()
    JSFF = None
    CsFl = None
    result = []
    for child in root:
        if child.tag == "JJFF":
            for grandchild in child:
                if grandchild.tag == 'JSFF':
                    JSFF = grandchild
    for child in JSFF:
        if child.attrib['Markup'] == '1':
            for grandchild in child:
                if grandchild.tag == 'CsFl':
                    CsFl = grandchild
    for child in CsFl:
        result.append({'工程类型': child.attrib['Gclx'], '名称': child.attrib['Mc'], '计算基础': child.attrib['Jsjc'],
                       '参考费率': child.attrib['CkFl'], '地区': child.attrib['Dq'], '备注': child.attrib['Bz']})
    return result



def getQds():
    shu = pd.read_csv("JD_QingDanXM.csv")
    length = len(shu)
    return processQds(shu, 0, length, 1)

def getQingdan(bm):
    shu = pd.read_csv("JD_QingDanXM.csv")
    filtered = shu[shu['qdbh']==(bm)]
    filtered = filtered.fillna("")
    print(filtered)
    if len(filtered) == 0:
        return []
    data = filtered.iloc[0]
    tz = pd.read_csv("JD_QingDanTZ.csv")
    tz_ = tz[tz['Qdbm'] == data['qdbh']]['Qdtz']
    tz_list = tz_.to_list()
    nr = pd.read_csv("JD_QingDanGZNR.csv")
    nr_ = nr[nr['Qdbm'] == data['qdbh']]['Gznr']
    nr_list = nr_.to_list()

    
    return [{
        "id" : data['ID'].item(),
        "清单编码" : data['qdbh'],
        "项目名称" : data["xmmc"],
        "项目特征" : "\n".join(tz_list),
        "计量单位" : data["dw"],
        "工程量计算规则" : data["qdjsgz"],
        "工作内容" : "\n".join(nr_list)
    }]
    


def getDes(value):
    if value == 10:
        shu = pd.read_csv("de/tj/JD_DingEShu.csv")
    elif value == 20:
        shu = pd.read_csv("de/sz/JD_DingEShu.csv")
    elif value == 30:
        shu = pd.read_csv("de/az/JD_DingEShu.csv")
    elif value == 40:
        shu = pd.read_csv("de/yl/JD_DingEShu.csv")
    elif value == 50:
        shu = pd.read_csv("de/xstj/JD_DingEShu.csv")
    else:
        shu = pd.read_csv("de/xsaz/JD_DingEShu.csv")
    length = len(shu)
    return processDes(shu, 1, length, 2)
def getPbs(value):
    if value == 10:
        shu = pd.read_csv("de/tj/JD_PeiBiFLB.csv")
    elif value == 20: 
        shu = pd.read_csv("de/tj/JD_PeiBiFLB.csv")
    elif value == 30: 
        shu = pd.read_csv("de/tj/JD_PeiBiFLB.csv")
    else: 
        return []
    length = len(shu)
    return processPbs(shu, 0, length, 1)

def getJxs(value):
    if value == 10:
        shu = pd.read_csv("de/tj/JD_JiXieFLB.csv")
    elif value == 20: 
        shu = pd.read_csv("de/tj/JD_JiXieFLB.csv")
    elif value == 30: 
        shu = pd.read_csv("de/tj/JD_JiXieFLB.csv")
    else: 
        return []
    length = len(shu)
    return processJxs(shu, 0, length, 1)

def getCls(value):
    if value == 10:
        shu = pd.read_csv("de/tj/JD_CaiLiaoFLB.csv")
    elif value == 20: 
        shu = pd.read_csv("de/sz/JD_CaiLiaoFLB.csv")
    elif value == 30: 
        shu = pd.read_csv("de/az/JD_CaiLiaoFLB.csv")
    elif value == 40: 
        shu = pd.read_csv("de/yl/JD_CaiLiaoFLB.csv")
    elif value == 50: 
        shu = pd.read_csv("de/xstj/JD_CaiLiaoFLB.csv")
    elif value == 60: 
        shu = pd.read_csv("de/xsaz/JD_CaiLiaoFLB.csv")
    else: 
        return []
    length = len(shu)
    return processCls(shu, 0, length, 1)

def read_singledexilie_tjazfy(debh):
    id = "412"
    r_debh = debh.split("!")[0]
    if len(debh.split("!")) == 1:
        return json.dumps({
            "reverse": id, 
            "rgde": [],
            "jxde": [],
            "clde": [],
            "actual_zhuanye": 10,
            "bz_selected": {"BZBH": {}},
            "bz_selected2": {"BZBH": {}}
        }, ensure_ascii=False)
    gclmc = debh.split("!")[1]
        
    return json.dumps({
            "reverse": id, 
            "DEBH": {"111": r_debh},
            "GCLMC": {"111": gclmc},
            "DW": {"111": '项'},
            "rgde": [],
            "jxde": [],
            "clde": [],
            "actual_zhuanye": 10,
            "bz_selected": {"BZBH": {}},
            "bz_selected2": {"BZBH": {}}
        }, ensure_ascii=False)

def read_singledexilie_xsazfy(debh):
    id = "None"
    if "-F1" in debh:
        id = "185"
    elif "-F2" in debh:
        id = "186"
    elif "-F3" in debh:
        id = "187"
    
    r_debh = debh.split("!")[0]
    if len(debh.split("!")) == 1:
        return json.dumps({
            "reverse": id, 
            "rgde": [],
            "jxde": [],
            "clde": [],
            "actual_zhuanye": 60,
            "bz_selected": {"BZBH": {}},
            "bz_selected2": {"BZBH": {}}
        }, ensure_ascii=False)
    gclmc = debh.split("!")[1]
        
    return json.dumps({
            "reverse": id, 
            "DEBH": {"111": r_debh},
            "GCLMC": {"111": gclmc},
            "DW": {"111": '项'},
            "rgde": [],
            "jxde": [],
            "clde": [],
            "actual_zhuanye": 60,
            "bz_selected": {"BZBH": {}},
            "bz_selected2": {"BZBH": {}}
        }, ensure_ascii=False)

def read_singledexilie_azfy(debh):
    id = "None"
    if "-F1" in debh:
        id = "2076"
    elif "-F2" in debh:
        id = "2077"
    elif "-F3" in debh:
        id = "2078"
    elif "-F4" in debh:
        id = "2079"
    elif "-F5" in debh:
        if '4-F5' in debh:
            id = "2081"
        elif '5-F5' in debh:
            id = '2082'
        elif '7-F5' in debh:
            id = '2083'
        elif '9-F5' in debh:
            id = '2084'
        elif '10-F5' in debh:
            id = '2085'
        elif '11-F5' in debh:
            id = '2086'
    elif "-F6" in debh:
        id = "2087"
    r_debh = debh.split("!")[0]
    if len(debh.split("!")) == 1:
        return json.dumps({
            "reverse": id, 
            "rgde": [],
            "jxde": [],
            "clde": [],
            "actual_zhuanye": 30,
            "bz_selected": {"BZBH": {}},
            "bz_selected2": {"BZBH": {}}
        }, ensure_ascii=False)
    gclmc = debh.split("!")[1]
        
    return json.dumps({
            "reverse": id, 
            "DEBH": {"111": r_debh},
            "GCLMC": {"111": gclmc},
            "DW": {"111": '项'},
            "rgde": [],
            "jxde": [],
            "clde": [],
            "actual_zhuanye": 30,
            "bz_selected": {"BZBH": {}},
            "bz_selected2": {"BZBH": {}}
        }, ensure_ascii=False)

def getDeXilie_xsazfy(value, id):
    if int(id) == 184:
        return "{\"Unnamed: 0\":{},\"id\":{},\"DEBH\":{},\"GCLMC\":{},\"GCLSJDJ\":{},\"DW\":{},\"DeId\":{}}"
    if int(id) == 185:
        data = {'DW': ['项', '项', '项'], 
                'DEBH': ['1-F1', '2-F1', '3-F1'],
                 "GCLMC": [ "第一章" , "第二章", "第三章"],
                   "GCLSJDJ": ["0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 186:
        data = {'DW': ['项', '项', '项'], 
                'DEBH': ['1-F2', '2-F2', '3-F2'],
                 "GCLMC": [ "第一章" , "第二章", "第三章"],
                   "GCLSJDJ": ["0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 187:
        data = {'DW': ['项', '项', '项'], 
                'DEBH': ['1-F3', '2-F3', '3-F3'],
                 "GCLMC": [ "第一章" , "第二章", "第三章"],
                   "GCLSJDJ": ["0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)


def getDeXilie_azfy(value, id):
    if int(id) == 2075 or int(id) == 2080:
        return "{\"Unnamed: 0\":{},\"id\":{},\"DEBH\":{},\"GCLMC\":{},\"GCLSJDJ\":{},\"DW\":{},\"DeId\":{}}"
    if int(id) == 2076:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项", "项", "项", "项", "项", "项", "项"], 
                'DEBH': ['2-F1', '2-F1', '3-F1', '3-F1', '4-F1',
                         '5-F1', '6-F1', '7-F1', '8-F1', '9-F1', '10-F1', '11-F1', '11-F1', '11-F1', '11-F1', '11-F1'],
                 "GCLMC": ["第二册第1-5章(定额2-1～2-390)", "第二册第6章(定额2-391～2-9999)", "第三册静置设备制作(定额3-1～3-615)",
                            "第三册除静置设备制作以外(定额3-616～3-9999)", "第四册", 
                            "第五册", "第六册", "第七册", "第八册", "第九册", "第十册",
                              "第十一册刷油工程(定额11-51～11-329)", 
                              "第十一册防腐蚀工程(定额11-330～11-1689)", "第十一册绝热工程(定额11-1690～11-2327)", "第十一册除锈工程(定额11-1～11-50)刷油工程", "第十一册除锈工程(定额11-1～11-50)防腐蚀工程"],
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2077:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项','项','项',
                       '项','项','项','项','项','项','项','项','项','项',
                       '项','项','项',
                       '项','项', "项",'项','项','项','项', "项",'项', "项"], 
                'DEBH': ['1-F2', '1-F2','1-F2','1-F2','1-F2','1-F2','1-F2','1-F2','1-F2','1-F2','1-F2','1-F2', 
                         '4-F2', '5-F2', '5-F2', '5-F2','6-F2',
                         '7-F2', '9-F2','9-F2','9-F2','9-F2', '10-F2','10-F2','10-F2','10-F2', 
                         '11-F2','11-F2','11-F2','11-F2', '11-F2','11-F2','11-F2' ],
                 "GCLMC": ["第一册第1-6章(定额1-1～1-571)10-15m",
                           "第一册第1-6章(定额1-1～1-571)15-20m",
                           "第一册第1-6章(定额1-1～1-571)20-25m",
                           "第一册第1-6章(定额1-1～1-571)25-30m",
                           "第一册第1-6章(定额1-1～1-571)30-40m",
                           "第一册第1-6章(定额1-1～1-571)40m以上",
                           "第一册第8-14章(定额1-672～1-9999)10-15m",
                           "第一册第8-14章(定额1-672～1-9999)15-20m",
                           "第一册第8-14章(定额1-672～1-9999)20-25m",
                           "第一册第8-14章(定额1-672～1-9999)25-30m",
                           "第一册第8-14章(定额1-672～1-9999)30-40m",
                           "第一册第8-14章(定额1-672～1-9999)40m以上",
                           "第四册",
                           "第五册5m以上到10m以内", 
                           "第五册10-20m", 
                           "第五册20m以上", 
                           "第六册", "第七册", 
                           "第九册5m以上到8m以内",
                            "第九册8-12m",
                            "第九册12-16m",
                            "第九册16-20m",
                            "第十册3.6-8m以内",
                            "第十册8-12m以内",
                            "第十册12-16m以内",
                            "第十册16-20m",
                           "第十一册6-20m",
                           "第十一册20-30m",
                           "第十一册30-40m",
                           "第十一册40-50m",
                           "第十一册50-60m",
                           "第十一册60-70m",
                           "第十一册80m以上",

                           ],
                   "GCLSJDJ": ["0", "0", '0', '0',"0", "0", "0", "0", "0", "0",
                               "0", "0", '0', '0',"0", "0", "0", "0", "0", "0",
                                "0","0", "0", 
                                "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2078:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项", "项"], 
                'DEBH': ['1-F4', '1-F4', '2-F4', '3-F4', '4-F4',
                         '5-F4', '6-F4', '7-F4', '8-F4', '9-F4', '11-F4'],
                 "GCLMC": ["第一册第1-6章", "第一册第8-14章", "第二册",
                            "第三册", "第四册", 
                            "第五册", "第六册", "第七册", "第八册", "第九册", 
                              "第十一册", ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2079:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项", "项"], 
                'DEBH': ['1-F5', '1-F5', '2-F5', '3-F5', '4-F5',
                         '5-F5', '6-F5', '7-F5', '8-F5', '9-F5', '11-F5'],
                 "GCLMC": ["第一册第1-6章", "第一册第8-14章", "第二册",
                            "第三册", "第四册", 
                            "第五册", "第六册", "第七册", "第八册", "第九册", 
                              "第十一册", ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2081:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                        "项",  '项', '项', '项', '项', "项", "项", "项"], 
                'DEBH': ['4-F3', '4-F3','4-F3', '4-F3','4-F3', '4-F3','4-F3', '4-F3',
                          '4-F3', '4-F3','4-F3', '4-F3','4-F3', '4-F3','4-F3', '4-F3','4-F3', '4-F3'],
                 "GCLMC": ["9层或30m以内", 
                           "12层或40m以内",
                           "15层或50m以内",
                            "18层或60m以内",
                            "21层或70m以内",
                            "24层或80m以内",
                            "27层或90m以内",
                            "30层或100m以内",
                            "33层或110m以内",
                            "36层或120m以内",
                            "40层或130m以内",
                            "42层或140m以内",
                            "45层或150m以内",
                            "48层或160m以内",
                            "51层或170m以内",
                            "54层或180m以内",
                            "57层或190m以内",
                            "60层或200m以内",
                            ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                                "0",  "0",  "0",  "0", "0",  "0",  "0",  "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2082:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                        "项",  '项', '项', '项', '项', "项", "项", "项"], 
                'DEBH': ['5-F3', '5-F3','5-F3', '5-F3','5-F3', '5-F3','5-F3', '5-F3',
                          '5-F3', '5-F3','5-F3', '5-F3','5-F3', '5-F3','5-F3', '5-F3','5-F3', '5-F3'],
                 "GCLMC": ["9层或30m以内", 
                           "12层或40m以内",
                           "15层或50m以内",
                            "18层或60m以内",
                            "21层或70m以内",
                            "24层或80m以内",
                            "27层或90m以内",
                            "30层或100m以内",
                            "33层或110m以内",
                            "36层或120m以内",
                            "40层或130m以内",
                            "42层或140m以内",
                            "45层或150m以内",
                            "48层或160m以内",
                            "51层或170m以内",
                            "54层或180m以内",
                            "57层或190m以内",
                            "60层或200m以内",
                            ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                                "0",  "0",  "0",  "0", "0",  "0",  "0",  "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2083:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                        "项",  '项', '项', '项', '项', "项", "项", "项"], 
                'DEBH': ['7-F3', '7-F3','7-F3', '7-F3','7-F3', '7-F3','7-F3', '7-F3',
                          '7-F3', '7-F3','7-F3', '7-F3','7-F3', '7-F3','7-F3', '7-F3','7-F3', '7-F3'],
                 "GCLMC": ["9层或30m以内", 
                           "12层或40m以内",
                           "15层或50m以内",
                            "18层或60m以内",
                            "21层或70m以内",
                            "24层或80m以内",
                            "27层或90m以内",
                            "30层或100m以内",
                            "33层或110m以内",
                            "36层或120m以内",
                            "40层或130m以内",
                            "42层或140m以内",
                            "45层或150m以内",
                            "48层或160m以内",
                            "51层或170m以内",
                            "54层或180m以内",
                            "57层或190m以内",
                            "60层或200m以内",
                            ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                                "0",  "0",  "0",  "0", "0",  "0",  "0",  "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2084:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                        "项",  '项', '项', '项', '项', "项", "项", "项"], 
                'DEBH': ['9-F3', '9-F3','9-F3', '9-F3','9-F3', '9-F3','9-F3', '9-F3',
                          '9-F3', '9-F3','9-F3', '9-F3','9-F3', '9-F3','9-F3', '9-F3','9-F3', '9-F3'],
                 "GCLMC": ["9层或30m以内", 
                           "12层或40m以内",
                           "15层或50m以内",
                            "18层或60m以内",
                            "21层或70m以内",
                            "24层或80m以内",
                            "27层或90m以内",
                            "30层或100m以内",
                            "33层或110m以内",
                            "36层或120m以内",
                            "40层或130m以内",
                            "42层或140m以内",
                            "45层或150m以内",
                            "48层或160m以内",
                            "51层或170m以内",
                            "54层或180m以内",
                            "57层或190m以内",
                            "60层或200m以内",
                            ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                                "0",  "0",  "0",  "0", "0",  "0",  "0",  "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2085:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                        "项",  '项', '项', '项', '项', "项", "项", "项"], 
                'DEBH': ['10-F3', '10-F3','10-F3', '10-F3','10-F3', '10-F3','10-F3', '10-F3',
                          '10-F3', '10-F3','10-F3', '10-F3','10-F3', '10-F3','10-F3', '10-F3','10-F3', '10-F3'],
                 "GCLMC": ["9层或30m以内", 
                           "12层或40m以内",
                           "15层或50m以内",
                            "18层或60m以内",
                            "21层或70m以内",
                            "24层或80m以内",
                            "27层或90m以内",
                            "30层或100m以内",
                            "33层或110m以内",
                            "36层或120m以内",
                            "40层或130m以内",
                            "42层或140m以内",
                            "45层或150m以内",
                            "48层或160m以内",
                            "51层或170m以内",
                            "54层或180m以内",
                            "57层或190m以内",
                            "60层或200m以内",
                            ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                                "0",  "0",  "0",  "0", "0",  "0",  "0",  "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2086:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                       '项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                       '项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                       '项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                       '项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                       '项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                       '项', '项', '项', '项', '项', '项', '项', '项', "项", "项",
                        "项",  '项'], 
                'DEBH': ['11-F3', '11-F3',
                          '11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3',
                          '11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3',
                          '11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3',
                           '11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3',
                          '11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3',
                          '11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3',
                           '11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3','11-F3', '11-F3',
                          ],
                 "GCLMC": [
                           "(按通风空调工程)9层或30m以内", 
                           "(按通风空调工程)12层或40m以内",
                           "(按通风空调工程)15层或50m以内",
                            "(按通风空调工程)18层或60m以内",
                            "(按通风空调工程)21层或70m以内",
                            "(按通风空调工程)24层或80m以内",
                            "(按通风空调工程)27层或90m以内",
                            "(按通风空调工程)30层或100m以内",
                            "(按通风空调工程)33层或110m以内",
                            "(按通风空调工程)36层或120m以内",
                            "(按通风空调工程)40层或130m以内",
                            "(按通风空调工程)42层或140m以内",
                            "(按通风空调工程)45层或150m以内",
                            "(按通风空调工程)48层或160m以内",
                            "(按通风空调工程)51层或170m以内",
                            "(按通风空调工程)54层或180m以内",
                            "(按通风空调工程)57层或190m以内",
                            "(按通风空调工程)60层或200m以内",
                            ############################
                             "(按消防工程)9层或30m以内", 
                           "(按消防工程)12层或40m以内",
                           "(按消防工程)15层或50m以内",
                            "(按消防工程)18层或60m以内",
                            "(按消防工程)21层或70m以内",
                            "(按消防工程)24层或80m以内",
                            "(按消防工程)27层或90m以内",
                            "(按消防工程)30层或100m以内",
                            "(按消防工程)33层或110m以内",
                            "(按消防工程)36层或120m以内",
                            "(按消防工程)40层或130m以内",
                            "(按消防工程)42层或140m以内",
                            "(按消防工程)45层或150m以内",
                            "(按消防工程)48层或160m以内",
                            "(按消防工程)51层或170m以内",
                            "(按消防工程)54层或180m以内",
                            "(按消防工程)57层或190m以内",
                            "(按消防工程)60层或200m以内",
                            ############################
                               "(按给排水、采暖工程)9层或30m以内", 
                           "(按给排水、采暖工程)12层或40m以内",
                           "(按给排水、采暖工程)15层或50m以内",
                            "(按给排水、采暖工程)18层或60m以内",
                            "(按给排水、采暖工程)21层或70m以内",
                            "(按给排水、采暖工程)24层或80m以内",
                            "(按给排水、采暖工程)27层或90m以内",
                            "(按给排水、采暖工程)30层或100m以内",
                            "(按给排水、采暖工程)33层或110m以内",
                            "(按给排水、采暖工程)36层或120m以内",
                            "(按给排水、采暖工程)40层或130m以内",
                            "(按给排水、采暖工程)42层或140m以内",
                            "(按给排水、采暖工程)45层或150m以内",
                            "(按给排水、采暖工程)48层或160m以内",
                            "(按给排水、采暖工程)51层或170m以内",
                            "(按给排水、采暖工程)54层或180m以内",
                            "(按给排水、采暖工程)57层或190m以内",
                            "(按给排水、采暖工程)60层或200m以内",
                            ############################
                               "(按电气工程)9层或30m以内", 
                           "(按电气工程)12层或40m以内",
                           "(按电气工程)15层或50m以内",
                            "(按电气工程)18层或60m以内",
                            "(按电气工程)21层或70m以内",
                            "(按电气工程)24层或80m以内",
                            "(按电气工程)27层或90m以内",
                            "(按电气工程)30层或100m以内",
                            "(按电气工程)33层或110m以内",
                            "(按电气工程)36层或120m以内",
                            "(按电气工程)40层或130m以内",
                            "(按电气工程)42层或140m以内",
                            "(按电气工程)45层或150m以内",
                            "(按电气工程)48层或160m以内",
                            "(按电气工程)51层或170m以内",
                            "(按电气工程)54层或180m以内",
                            "(按电气工程)57层或190m以内",
                            "(按电气工程)60层或200m以内",

                            ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                               "0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                               "0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                               "0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                               "0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                               "0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                               "0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                                "0",  "0" ]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)
    if int(id) == 2087:
        data = {'DW': ['项', '项', '项', '项', '项', '项', '项', '项', "项"], 
                'DEBH': ['1-F6', '3-F6', '4-F6', '7-F6', '8-F6',
                         '10-F6', '10-F6', '10-F6', '11-F6'],
                 "GCLMC": ["第一册 站内工艺系统", 
                            "第三册 站内工艺系统", "第四册 站内工艺系统", 
                             "第七册", "第八册 站内工艺系统",  
                              "第十册供暖器具安装", "第十册空调水工程管道安装", "第十册空调水工程管道附件", "第十一册 站内工艺系统" ],
                             
                   "GCLSJDJ": ["0", "0", "0", "0", "0", "0", "0", "0", "0"]}
        df = pd.DataFrame(data)
        return df.to_json(force_ascii=False)

def getDeXilie(value, id):
    ##print(type(id))
    if value == 10:
        shu = pd.read_csv("de/tj/JD_DingEShu.csv")
    elif value == 20:
        shu = pd.read_csv("de/sz/JD_DingEShu.csv")
    elif value == 30:
        shu = pd.read_csv("de/az/JD_DingEShu.csv")
    elif value == 40:
        shu = pd.read_csv("de/yl/JD_DingEShu.csv")
    elif value == 50:
        shu = pd.read_csv("de/xstj/JD_DingEShu.csv")
    else:
        shu = pd.read_csv("de/xsaz/JD_DingEShu.csv")
    hit = shu[shu["ID"]==int(id)]
    ##print(hit)
    if len(hit) == 0:
        return []
    hit0 = hit.iloc[0]
    index = str(hit0['zjh'])
    ##print("index=" + index)
    if value == 10:
        gj = pd.read_csv("de/tj/JD_DanWeiGJ.csv")
        filtered = gj[gj["YSDELBH"]==int(index)]
    elif value == 20:
        gj = pd.read_csv("de/sz/JD_DanWeiGJ.csv")
        filtered = gj[gj["YSDELBH"]==(index)]
    elif value == 30:
        gj = pd.read_csv("de/az/JD_DanWeiGJ.csv")
        filtered = gj[gj["YSDELBH"]==(index)]
    elif value == 40:
        gj = pd.read_csv("de/yl/JD_DanWeiGJ.csv")
        filtered = gj[gj["YSDELBH"]==int(index)]
    elif value == 50:
        gj = pd.read_csv("de/xstj/JD_DanWeiGJ.csv")
        filtered = gj[gj["YSDELBH"]==int(index)]
    else:
        gj = pd.read_csv("de/xsaz/JD_DanWeiGJ.csv")
        filtered = gj[gj["YSDELBH"]==int(index)]
    filtered = filtered.drop(['BH1', 'BH2', "GCLCLF", "GCLCLF1", "GCLGG", "GCLGLF", "GCLJJ", "GCLJXF", "GCLLR", "GCLRGF", "GCLRGR", "GCPBHJ", "GLFFL", "LRFL", "PBBH", "PBBHM", "PBDJ", "PBSL", "THMC", "THSL", "dejb", "is_lock", "mcxz", "qfbz", "sptfl"], axis=1)
    return filtered.to_json(force_ascii=False)




def getPbxl(name, zhuanye):
    if name == "0":
        if zhuanye == 10:
            gj = pd.read_csv("de/tj/JD_PeiBi.csv")
        elif zhuanye == 20:
            gj = pd.read_csv("de/tj/JD_PeiBi.csv")
        elif zhuanye == 30:
            gj = pd.read_csv("de/tj/JD_PeiBi.csv")
        elif zhuanye == 40:
            gj = pd.read_csv("de/yl/JD_PeiBi.csv")
        elif zhuanye == 50:
            gj = pd.read_csv("de/xstj/JD_PeiBi.csv")
        elif zhuanye == 60:
            gj = pd.read_csv("de/xsaz/JD_PeiBi.csv")
        return gj.to_json(force_ascii=False)
    shu = pd.read_csv("de/tj/JD_PeiBiFLB.csv")
    hit = shu[shu["ID"]==int(name)]
    ##print(hit)
    if len(hit) == 0:
        return []
    hit0 = hit.iloc[0]
    index = str(hit0['pblbh'])
    index_list = index.split(";")
    gj = pd.read_csv("de/tj/JD_PeiBi.csv")
    filtered = gj[gj["PBBH"].str.startswith(tuple(index_list))]
    return filtered.to_json(force_ascii=False)


def getJxxl(name, zhuanye):
    if name == "0":
        if zhuanye == 10:
            gj = pd.read_csv("de/tj/JD_JiXieK.csv")
        elif zhuanye == 20:
            gj = pd.read_csv("de/tj/JD_JiXieK.csv")
        elif zhuanye == 30:
            gj = pd.read_csv("de/tj/JD_JiXieK.csv")
        elif zhuanye == 40:
            gj = pd.read_csv("de/yl/JD_JiXieK.csv")
        elif zhuanye == 50:
            gj = pd.read_csv("de/xstj/JD_JiXieK.csv")
        elif zhuanye == 60:
            gj = pd.read_csv("de/xsaz/JD_JiXieK.csv")
        return gj.to_json(force_ascii=False)
    shu = pd.read_csv("de/tj/JD_JiXieFLB.csv")
    hit = shu[shu["ID"]==int(name)]
    ##print(hit)
    if len(hit) == 0:
        return []
    hit0 = hit.iloc[0]
    index = str(hit0['jxlbh'])
    index_list = index.split(";")
    gj = pd.read_csv("de/tj/JD_JiXieK.csv")
    filtered = gj[gj["jxbh"].str.startswith(tuple(index_list))]
    return filtered.to_json(force_ascii=False)


def getClxl(name, zhuanye):
    if name == "0":
        if zhuanye == 10:
            gj = pd.read_csv("de/tj/JD_CaiLiaoK.csv")
        elif zhuanye == 20:
            gj = pd.read_csv("de/sz/JD_CaiLiaoK.csv")
        elif zhuanye == 30:
            gj = pd.read_csv("de/az/JD_CaiLiaoK.csv")
        elif zhuanye == 40:
            gj = pd.read_csv("de/yl/JD_CaiLiaoK.csv")
        elif zhuanye == 50:
            gj = pd.read_csv("de/xstj/JD_CaiLiaoK.csv")
        elif zhuanye == 60:
            gj = pd.read_csv("de/xsaz/JD_CaiLiaoK.csv")
        return gj.to_json(force_ascii=False)
    if zhuanye == 10:
        shu = pd.read_csv("de/tj/JD_CaiLiaoFLB.csv")
    if zhuanye == 20:
        shu = pd.read_csv("de/sz/JD_CaiLiaoFLB.csv")
    if zhuanye == 30:
        shu = pd.read_csv("de/az/JD_CaiLiaoFLB.csv")
    if zhuanye == 40:
        shu = pd.read_csv("de/yl/JD_CaiLiaoFLB.csv", dtype={'cllbh': str})
    if zhuanye == 50:
        shu = pd.read_csv("de/xstj/JD_CaiLiaoFLB.csv", dtype={'cllbh': str})
    if zhuanye == 60:
        shu = pd.read_csv("de/xsaz/JD_CaiLiaoFLB.csv", dtype={'cllbh': str})
    hit = shu[shu["ID"]==int(name)]
    ##print(hit)
    if len(hit) == 0:
        return []
    hit0 = hit.iloc[0]
    index = str(hit0['cllbh'])
    index_list = index.split(";")
    if zhuanye == 10:
        gj = pd.read_csv("de/tj/JD_CaiLiaoK.csv")
    if zhuanye == 20:
        gj = pd.read_csv("de/sz/JD_CaiLiaoK.csv")
    if zhuanye == 30:
        gj = pd.read_csv("de/az/JD_CaiLiaoK.csv")
    if zhuanye == 40:
        gj = pd.read_csv("de/yl/JD_CaiLiaoK.csv")
    if zhuanye == 50:
        gj = pd.read_csv("de/xstj/JD_CaiLiaoK.csv")
    if zhuanye == 60:
        gj = pd.read_csv("de/xsaz/JD_CaiLiaoK.csv")
    ##print(index_list)
    ##print(gj)
    filtered = gj[gj["CLBH"].str.startswith(tuple(index_list))]
    return filtered.to_json(force_ascii=False)



def getSingleDeXilie(zhuanye, debh):
    print(debh)
    if zhuanye == 10:
        A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_tj(debh)
        if A1:
            return A1, A2, A3, A4, A5, A6, A7, 10
        else:
            A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_sz(debh)
            return A1, A2, A3, A4,A5, A6, A7, 20
    elif zhuanye == 20:##shizheng
        A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_sz(debh)
        if A1:
            return A1, A2, A3, A4, A5, A6, A7, 20
        else:
            A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_tj(debh)
            if A1:
                return A1, A2, A3, A4, A5, A6, A7, 10
            else:
                A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_az(debh)
                return A1, A2, A3, A4, A5, A6, A7, 30
    elif zhuanye == 30:##anzhuang
        A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_az(debh)
        return A1, A2, A3, A4, A5, A6, A7, 30
    elif zhuanye == 40:##yuanlin
        A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_yl(debh)
        return A1, A2, A3, A4, A5, A6, A7,  40  
    elif zhuanye == 50: ## xiushan tujian
        A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_xstj(debh)
        if A1:
            return A1, A2, A3, A4, A5, A6, A7, 50
        else:
            A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_tj(debh)
            return A1, A2, A3, A4,A5, A6, A7, 10
    else:
        A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_xsaz(debh)
        if A1:
            return A1, A2, A3, A4, A5, A6, A7, 60
        else:
            A1, A2, A3, A4, A5, A6, A7 = getSingleDeXilie_az(debh)
            return A1, A2, A3, A4,A5, A6, A7, 30

def getSingleDeXilie_xsaz(debh):
    id = None
    bz_selected = []
    bz_selected2 = []
    if "附注" in debh:
        position = debh.find("附注")
        debh = debh[:position]
    if "*" in debh:
        position = debh.find("*")
        debh = debh[:position]
    if debh.endswith("换"):
        debh = debh[:-1]
    if "[" in debh:
        position = debh.find("[")
        debh = debh[:position-1]
    gj = pd.read_csv("de/xsaz/JD_DanWeiGJ.csv")
    filtered = gj[gj["DEBH"]==debh]
    ##print(filtered)
    if len(filtered) > 0:
        index = (filtered.iloc[0]["YSDELBH"])
        shu = pd.read_csv("de/xsaz/JD_DingEShu.csv")
        hit = shu[shu["zjh"]==index]
        if len(hit) > 0:
            id = hit.iloc[0]["ID"]
        beizhu = str(filtered.iloc[0]["BZBH"])
        if 'nan' in beizhu:
            pass
        else:
            beizhus = beizhu.split(";")
            ##print(beizhus)
            bzfile = pd.read_csv("de/xsaz/JD_BeiZhuZK.csv")
            bzfile2 = pd.read_csv("de/xsaz/JD_BeiZhuFK.csv", dtype=str)
            bz_selected = bzfile[bzfile["BZBH"].isin(beizhus)] 
            bz_selected2 = bzfile2[bzfile2["BZBH"].isin(beizhus)] 
    else:
        return None, None, None, None, None, None, None
    ####rengong
    rgde = []
    rg = pd.read_csv("de/xsaz/JD_GuJiaRG.csv")
    filtered1 = rg[rg["DEBH"]==debh]
    for i in range(len(filtered1)):
        rgde_ = {}
        gr = filtered1.iloc[i]["RGR"]##gongri
        gf = filtered1.iloc[i]["RGF"]##gongfei
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/xsaz/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            rgde_["CLBH"] = CLBH
            rgde_["CLMC"] = CLMC
            rgde_["JLDW"] = JLDW
            rgde_["YSJG"] = YSJG.item()
            rgde_["gr"] = gr.item()
            rgde_["gf"] = gf.item()
        rgde.append(rgde_)
    ##print(rgde)
    ####jixie
    jxde = []
    jx = pd.read_csv("de/xsaz/JD_GuJiaJX.csv")
    filtered1 = jx[jx["DEBH"]==debh]
    for i in range(len(filtered1)):
        jxde_ = {}
        sl = filtered1.iloc[i]["SL"]##shuliang
        hj = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/xsaz/JD_JiXieK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            jxbh = filtered2.iloc[0]["jxbh"]
            jxmc = filtered2.iloc[0]["jxmc"]
            DW = filtered2.iloc[0]["DW"]
            tbdj = filtered2.iloc[0]["Csdj"]
            jxde_["jxbh"] = jxbh
            jxde_["jxmc"] = jxmc
            jxde_["DW"] = DW
            jxde_["tbdj"] = tbdj.item()
            jxde_["sl"] = sl.item()
            jxde_["hj"] = hj.item()
        jxde.append(jxde_)
    ####cailiao
    clde = []
    cl = pd.read_csv("de/xsaz/JD_GuJiaCL.csv")
    filtered1 = cl[cl["DEBH"]==debh]
    for i in range(len(filtered1)):
        clde_ = {}
        SL = filtered1.iloc[i]["SL"]##shuliang
        HJ = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/xsaz/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        detail_ = pd.read_csv("de/xsaz/JD_PeiBi.csv")
        filtered2_ = detail_[detail_["RcjID"]==rcjID]
        if len(filtered2) > 0 and len(filtered2_) == 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            clde_["CLBH"] = CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item()
            clde_["SL"] = SL.item()
            clde_["HJ"] = HJ.item()
            clde.append(clde_)
        elif len(filtered2) == 0 and len(filtered2_) > 0:
            
            
            CLBH = filtered2_.iloc[0]["PBBH"]
            CLMC = filtered2_.iloc[0]["PBMC"]
            JLDW = filtered2_.iloc[0]["DW"]
            YSJG = filtered2_.iloc[0]["Csdj"]
            clde_["CLBH"] = CLBH.item()
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item()
            clde_["SL"] = SL.item()
            clde_["HJ"] = HJ.item()
            clde.append(clde_)
        elif len(filtered2) > 0 and len(filtered2_) > 0:
            YSJG = filtered2.iloc[0]["Csdj"]
            YSJG_ = filtered2_.iloc[0]["Csdj"]
            if float(HJ) < 0.00001:
                if float(YSJG) < 0.00001:
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)
                elif float(YSJG_) < 0.00001:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH.item()
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)
            else:

                ratio = float(YSJG) / (float(HJ) / float(SL))
                ratio_ = float(YSJG_) / (float(HJ) / float(SL))
                if abs(ratio - 1) < abs(ratio_ -1):
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)
                else:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH.item()
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)




    filtered = filtered.drop(['BH1', 'BH2', "GCLCLF", "GCLCLF1", "GCLGG", "GCLGLF", "GCLJJ", "GCLJXF", "GCLLR", "GCLRGF", "GCLRGR", "GCLSJDJ", "GCPBHJ",  "GLFFL", "LRFL", "PBBH", "PBBHM", "PBDJ", "PBSL", "THMC", "THSL", "dejb", "is_lock",  "mcxz", "qfbz",  "sptfl"], axis=1)
    return filtered.to_json(force_ascii=False), id, rgde, jxde, clde, bz_selected.to_json(force_ascii=False) if len(bz_selected) > 0 else None, bz_selected2.to_json(force_ascii=False) if len(bz_selected2) > 0 else None





def getSingleDeXilie_xstj(debh):
    id = None
    bz_selected = []
    bz_selected2 = []
    additional = []
    if "附注" in debh:
        position = debh.find("附注")
        debh = debh[:position]
    if "*" in debh:
        position = debh.find("*")
        debh = debh[:position]
    if debh.endswith("换"):
        debh = debh[:-1]
    if "[" in debh:
        position = debh.find("[")
        debh = debh[:position-1]
    gj = pd.read_csv("de/xstj/JD_DanWeiGJ.csv")
    filtered = gj[gj["DEBH"]==debh]
    ##print(filtered)
    if len(filtered) > 0:
        index = (filtered.iloc[0]["YSDELBH"])
        shu = pd.read_csv("de/xstj/JD_DingEShu.csv")
        hit = shu[shu["zjh"]==index]
        if len(hit) > 0:
            id = hit.iloc[0]["ID"]
        beizhu = str(filtered.iloc[0]["BZBH"])
        if 'nan' in beizhu:
            pass
        else:
            beizhus = beizhu.split(";")
            ##print(beizhus)
            bzfile = pd.read_csv("de/xstj/JD_BeiZhuZK.csv")
            bzfile2 = pd.read_csv("de/xstj/JD_BeiZhuFK.csv", dtype=str)
            bz_selected = bzfile[bzfile["BZBH"].isin(beizhus)] 
            bz_selected2 = bzfile2[bzfile2["BZBH"].isin(beizhus)] 
            bz_selected2 = bz_selected2.astype({'BH': str})
            additional = bz_selected2['BH'].tolist()
            additional = [entry for entry in additional if not entry in ['000000', '000001', 'J00000', '100000', '800101', '015103', '066121', '066501', '066503', '070101', '070303', '070902', '071121', '241503', '8021', '80210']]
   
    else:
        return None, None, None, None, None, None, None
    ####rengong
    rgde = []
    rg = pd.read_csv("de/xstj/JD_GuJiaRG.csv")
    filtered1 = rg[rg["DEBH"]==debh]
    for i in range(len(filtered1)):
        rgde_ = {}
        gr = filtered1.iloc[i]["RGR"]##gongri
        gf = filtered1.iloc[i]["RGF"]##gongfei
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/xstj/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            rgde_["CLBH"] = CLBH
            rgde_["CLMC"] = CLMC
            rgde_["JLDW"] = JLDW
            rgde_["YSJG"] = YSJG.item()
            rgde_["gr"] = gr.item()
            rgde_["gf"] = gf.item()
            if CLBH in additional:
                additional.remove(CLBH)
        rgde.append(rgde_)
    detail = pd.read_csv("de/xstj/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([1, '1'])]
    added = detail[detail['CLBH'].isin(additional)]
    for _, entry in added.iterrows():
        rgde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        rgde_["CLBH"] = CLBH
        rgde_["CLMC"] = CLMC
        rgde_["JLDW"] = JLDW
        rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        rgde_["gr"] = 0
        rgde_["gf"] = 0
        rgde.append(rgde_)
    ####jixie
    jxde = []
    jx = pd.read_csv("de/xstj/JD_GuJiaJX.csv")
    filtered1 = jx[jx["DEBH"]==debh]
    for i in range(len(filtered1)):
        jxde_ = {}
        sl = filtered1.iloc[i]["SL"]##shuliang
        hj = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/xstj/JD_JiXieK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            jxbh = filtered2.iloc[0]["jxbh"]
            jxmc = filtered2.iloc[0]["jxmc"]
            DW = filtered2.iloc[0]["DW"]
            tbdj = filtered2.iloc[0]["Csdj"]
            jxde_["jxbh"] = jxbh
            jxde_["jxmc"] = jxmc
            jxde_["DW"] = DW
            jxde_["tbdj"] = tbdj.item()
            jxde_["sl"] = sl.item()
            jxde_["hj"] = hj.item()
            if jxbh in additional:
                additional.remove(jxbh)
        jxde.append(jxde_)
    detail = pd.read_csv("de/xstj/JD_JiXieK.csv")
    added = detail[detail['jxbh'].isin(additional)]
    for _, entry in added.iterrows():
        jxde_ = {}
        jxbh = entry["jxbh"]
        jxmc = entry["jxmc"]
        DW = entry["DW"]
        tbdj = entry["Csdj"]
        jxde_["jxbh"] = jxbh
        jxde_["jxmc"] = jxmc
        jxde_["DW"] = DW
        jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
        jxde_["sl"] = 0
        jxde_["hj"] = 0
        jxde.append(jxde_)
    ####cailiao
    clde = []
    cl = pd.read_csv("de/xstj/JD_GuJiaCL.csv")
    filtered1 = cl[cl["DEBH"]==debh]
    for i in range(len(filtered1)):
        clde_ = {}
        SL = filtered1.iloc[i]["SL"]##shuliang
        HJ = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/xstj/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        detail_ = pd.read_csv("de/xstj/JD_PeiBi.csv")
        filtered2_ = detail_[detail_["RcjID"]==rcjID]
        if len(filtered2) > 0 and len(filtered2_) == 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            clde_["CLBH"] = CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item()
            clde_["SL"] = SL.item()
            clde_["HJ"] = HJ.item()
            clde.append(clde_)
        elif len(filtered2) == 0 and len(filtered2_) > 0:
            
            
            CLBH = filtered2_.iloc[0]["PBBH"]
            CLMC = filtered2_.iloc[0]["PBMC"]
            JLDW = filtered2_.iloc[0]["DW"]
            YSJG = filtered2_.iloc[0]["Csdj"]
            clde_["CLBH"] = CLBH.item()
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item()
            clde_["SL"] = SL.item()
            clde_["HJ"] = HJ.item()
            clde.append(clde_)
        elif len(filtered2) > 0 and len(filtered2_) > 0:
            YSJG = filtered2.iloc[0]["Csdj"]
            YSJG_ = filtered2_.iloc[0]["Csdj"]
            if float(HJ) < 0.00001:
                if float(YSJG) < 0.00001:
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)
                elif float(YSJG_) < 0.00001:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH.item()
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)
            else:
                

                ratio = float(YSJG) / (float(HJ) / float(SL))
                ratio_ = float(YSJG_) / (float(HJ) / float(SL))
                if abs(ratio - 1) < abs(ratio_ -1):
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)
                else:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH.item()
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item()
                    clde_["SL"] = SL.item()
                    clde_["HJ"] = HJ.item()
                    clde.append(clde_)
        if 'CLBH' in clde_ and clde_['CLBH'] in additional:
            additional.remove(clde_['CLBH'])
    detail = pd.read_csv("de/xstj/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([2, '2'])]
    added = detail[detail['CLBH'].isin(additional)]
    added = added.drop_duplicates(subset=['CLBH'])
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)
    detail = pd.read_csv("de/xstj/JD_PeiBi.csv")
    added = detail[detail['PBBH'].isin(additional)]
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["PBBH"]
        CLMC = entry["PBMC"]
        JLDW = entry["DW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)



    filtered = filtered.drop(['BH1', 'BH2', "GCLCLF", "GCLCLF1", "GCLGG", "GCLGLF", "GCLJJ", "GCLJXF", "GCLLR", "GCLRGF", "GCLRGR", "GCLSJDJ", "GCPBHJ",  "GLFFL", "LRFL", "PBBH", "PBBHM", "PBDJ", "PBSL", "THMC", "THSL", "dejb", "is_lock",  "mcxz", "qfbz",  "sptfl"], axis=1)
    return filtered.to_json(force_ascii=False), id, rgde, jxde, clde, bz_selected.to_json(force_ascii=False) if len(bz_selected) > 0 else None, bz_selected2.to_json(force_ascii=False) if len(bz_selected2) > 0 else None


def getSingleDeXilie_yl(debh):
    id = None
    bz_selected = []
    bz_selected2 = []
    additional = []
    if "附注" in debh:
        position = debh.find("附注")
        debh = debh[:position]
    if "*" in debh:
        position = debh.find("*")
        debh = debh[:position]
    if debh.endswith("换"):
        debh = debh[:-1]
    if "[" in debh:
        position = debh.find("[")
        debh = debh[:position-1]
    gj = pd.read_csv("de/yl/JD_DanWeiGJ.csv")
    filtered = gj[gj["DEBH"]==debh]
    ##print(filtered)
    if len(filtered) > 0:
        index = (filtered.iloc[0]["YSDELBH"])
        shu = pd.read_csv("de/yl/JD_DingEShu.csv")
        hit = shu[shu["zjh"]==index]
        if len(hit) > 0:
            id = hit.iloc[0]["ID"]
        beizhu = str(filtered.iloc[0]["BZBH"])
        if 'nan' in beizhu:
            pass
        else:
            beizhus = beizhu.split(";")
            ##print(beizhus)
            bzfile = pd.read_csv("de/yl/JD_BeiZhuZK.csv")
            bzfile2 = pd.read_csv("de/yl/JD_BeiZhuFK.csv", dtype=str)
            bz_selected = bzfile[bzfile["BZBH"].isin(beizhus)] 
            bz_selected2 = bzfile2[bzfile2["BZBH"].isin(beizhus)] 
            bz_selected2 = bz_selected2.astype({'BH': str})
            additional = bz_selected2['BH'].tolist()
            additional = [entry for entry in additional if not entry in ['000000', '000001', 'J00000', '100000', '800101', '015103', '066121', '066501', '066503', '070101', '070303', '070902', '071121', '241503', '8021', '80210']]
   
    else:
        return None, None, None, None, None, None, None
    ####rengong
    rgde = []
    rg = pd.read_csv("de/yl/JD_GuJiaRG.csv")
    filtered1 = rg[rg["DEBH"]==debh]
    for i in range(len(filtered1)):
        rgde_ = {}
        gr = filtered1.iloc[i]["RGR"]##gongri
        gf = filtered1.iloc[i]["RGF"]##gongfei
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/yl/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            rgde_["CLBH"] = CLBH
            rgde_["CLMC"] = CLMC
            rgde_["JLDW"] = JLDW
            rgde_["YSJG"] = YSJG.item()
            rgde_["gr"] = gr.item()
            rgde_["gf"] = gf.item()
            if CLBH in additional:
                additional.remove(CLBH)
        rgde.append(rgde_)
    detail = pd.read_csv("de/yl/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([1, '1'])]
    added = detail[detail['CLBH'].isin(additional)]
    for _, entry in added.iterrows():
        rgde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        rgde_["CLBH"] = CLBH
        rgde_["CLMC"] = CLMC
        rgde_["JLDW"] = JLDW
        rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        rgde_["gr"] = 0
        rgde_["gf"] = 0
        rgde.append(rgde_)
    ####jixie
    jxde = []
    jx = pd.read_csv("de/yl/JD_GuJiaJX.csv")
    filtered1 = jx[jx["DEBH"]==debh]
    for i in range(len(filtered1)):
        jxde_ = {}
        sl = filtered1.iloc[i]["SL"]##shuliang
        hj = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/yl/JD_JiXieK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            jxbh = filtered2.iloc[0]["jxbh"]
            jxmc = filtered2.iloc[0]["jxmc"]
            DW = filtered2.iloc[0]["DW"]
            tbdj = filtered2.iloc[0]["Csdj"]
            jxde_["jxbh"] = jxbh
            jxde_["jxmc"] = jxmc
            jxde_["DW"] = DW
            jxde_["tbdj"] = tbdj.item()
            jxde_["sl"] = sl.item()
            jxde_["hj"] = hj.item()
            if jxbh in additional:
                additional.remove(jxbh)
        jxde.append(jxde_)
    detail = pd.read_csv("de/yl/JD_JiXieK.csv")
    added = detail[detail['jxbh'].isin(additional)]
    for _, entry in added.iterrows():
        jxde_ = {}
        jxbh = entry["jxbh"]
        jxmc = entry["jxmc"]
        DW = entry["DW"]
        tbdj = entry["Csdj"]
        jxde_["jxbh"] = jxbh
        jxde_["jxmc"] = jxmc
        jxde_["DW"] = DW
        jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
        jxde_["sl"] = 0
        jxde_["hj"] = 0
        jxde.append(jxde_)
    ####cailiao
    clde = []
    cl = pd.read_csv("de/yl/JD_GuJiaCL.csv")
    filtered1 = cl[cl["DEBH"]==debh]
    for i in range(len(filtered1)):
        clde_ = {}
        SL = filtered1.iloc[i]["SL"]##shuliang
        HJ = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/yl/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            clde_["CLBH"] = CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item()
            clde_["SL"] = SL.item()
            clde_["HJ"] = HJ.item()
            clde.append(clde_)
        else:
            detail = pd.read_csv("de/yl/JD_PeiBi.csv")
            filtered2 = detail[detail["RcjID"]==rcjID]
            if len(filtered2) > 0:
                CLBH = filtered2.iloc[0]["PBBH"]
                CLMC = filtered2.iloc[0]["PBMC"]
                JLDW = filtered2.iloc[0]["DW"]
                YSJG = filtered2.iloc[0]["Csdj"]
                clde_["CLBH"] = CLBH.item()
                clde_["CLMC"] = CLMC
                clde_["JLDW"] = JLDW
                clde_["YSJG"] = YSJG.item()
                clde_["SL"] = SL.item()
                clde_["HJ"] = HJ.item()
                clde.append(clde_)
        if 'CLBH' in clde_ and clde_['CLBH'] in additional:
            additional.remove(clde_['CLBH'])
    detail = pd.read_csv("de/yl/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([2, '2'])]
    added = detail[detail['CLBH'].isin(additional)]
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)
    detail = pd.read_csv("de/yl/JD_PeiBi.csv")
    added = detail[detail['PBBH'].isin(additional)]
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["PBBH"]
        CLMC = entry["PBMC"]
        JLDW = entry["DW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)
    filtered = filtered.drop(['BH1', 'BH2', "GCLCLF", "GCLCLF1", "GCLGG", "GCLGLF", "GCLJJ", "GCLJXF", "GCLLR", "GCLRGF", "GCLRGR", "GCLSJDJ", "GCPBHJ",  "GLFFL", "LRFL", "PBBH", "PBBHM", "PBDJ", "PBSL", "THMC", "THSL", "dejb", "is_lock",  "mcxz", "qfbz",  "sptfl"], axis=1)
    return filtered.to_json(force_ascii=False), id, rgde, jxde, clde, bz_selected.to_json(force_ascii=False) if len(bz_selected) > 0 else None, bz_selected2.to_json(force_ascii=False) if len(bz_selected2) > 0 else None



def getSingleDeXilie_az(debh):
    id = None
    bz_selected = []
    bz_selected2 = []
    additional = []
    if "附注" in debh:
        position = debh.find("附注")
        debh = debh[:position]
    if "*" in debh:
        position = debh.find("*")
        debh = debh[:position]
    if debh.endswith("换"):
        debh = debh[:-1]
    if "[" in debh:
        position = debh.find("[")
        debh = debh[:position-1]
    gj = pd.read_csv("de/az/JD_DanWeiGJ.csv")
    filtered = gj[gj["DEBH"]==debh]
    ##print(filtered)
    if len(filtered) > 0:
        index = (filtered.iloc[0]["YSDELBH"])
        shu = pd.read_csv("de/az/JD_DingEShu.csv")
        hit = shu[shu["zjh"]==index]
        if len(hit) > 0:
            id = hit.iloc[0]["ID"]
        beizhu = str(filtered.iloc[0]["BZBH"])
        if 'nan' in beizhu:
            pass
        else:
            beizhus = beizhu.split(";")
            ##print(beizhus)
            bzfile = pd.read_csv("de/az/JD_BeiZhuZK.csv")
            bzfile2 = pd.read_csv("de/az/JD_BeiZhuFK.csv", dtype=str)
            bz_selected = bzfile[bzfile["BZBH"].isin(beizhus)] 
            bz_selected2 = bzfile2[bzfile2["BZBH"].isin(beizhus)] 
            bz_selected2 = bz_selected2.astype({'BH': str})
            additional = bz_selected2['BH'].tolist()
            additional = [entry for entry in additional if not entry in ['000000', '000001', 'J00000', '100000', '800101', '015103', '066121', '066501', '066503', '070101', '070303', '070902', '071121', '241503', '8021', '80210']]
  
    else:
        return None, None, None, None, None, None, None
    ####rengong
    rgde = []
    rg = pd.read_csv("de/az/JD_GuJiaRG.csv")
    filtered1 = rg[rg["DEBH"]==debh]
    for i in range(len(filtered1)):
        rgde_ = {}
        gr = filtered1.iloc[i]["RGR"]##gongri
        gf = filtered1.iloc[i]["RGF"]##gongfei
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/az/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            rgde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
            rgde_["CLMC"] = CLMC
            rgde_["JLDW"] = JLDW
            rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
            rgde_["gr"] = gr.item() if type(gr) == float64 else gr
            rgde_["gf"] = gf.item() if type(gf) == float64 else gf
            if CLBH in additional:
                additional.remove(CLBH)
        rgde.append(rgde_)
    detail = pd.read_csv("de/az/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([1, '1'])]
    added = detail[detail['CLBH'].isin(additional)]
    for _, entry in added.iterrows():
        rgde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        rgde_["CLBH"] = CLBH
        rgde_["CLMC"] = CLMC
        rgde_["JLDW"] = JLDW
        rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        rgde_["gr"] = 0
        rgde_["gf"] = 0
        rgde.append(rgde_)
    ####jixie
    jxde = []
    jx = pd.read_csv("de/az/JD_GuJiaJX.csv")
    filtered1 = jx[jx["DEBH"]==debh]
    for i in range(len(filtered1)):
        jxde_ = {}
        sl = filtered1.iloc[i]["SL"]##shuliang
        hj = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/az/JD_JiXieK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            jxbh = filtered2.iloc[0]["jxbh"]
            jxmc = filtered2.iloc[0]["jxmc"]
            DW = filtered2.iloc[0]["DW"]
            tbdj = filtered2.iloc[0]["Csdj"]
            jxde_["jxbh"] = jxbh
            jxde_["jxmc"] = jxmc
            jxde_["DW"] = DW
            jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
            jxde_["sl"] = sl.item() if type(sl) == float64 else sl
            jxde_["hj"] = hj.item() if type(hj) == float64 else hj
            if jxbh in additional:
                additional.remove(jxbh)
        jxde.append(jxde_)
    detail = pd.read_csv("de/az/JD_JiXieK.csv")
    added = detail[detail['jxbh'].isin(additional)]
    for _, entry in added.iterrows():
        jxde_ = {}
        jxbh = entry["jxbh"]
        jxmc = entry["jxmc"]
        DW = entry["DW"]
        tbdj = entry["Csdj"]
        jxde_["jxbh"] = jxbh
        jxde_["jxmc"] = jxmc
        jxde_["DW"] = DW
        jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
        jxde_["sl"] = 0
        jxde_["hj"] = 0
        jxde.append(jxde_)
    ####cailiao
    clde = []
    cl = pd.read_csv("de/az/JD_GuJiaCL.csv")
    filtered1 = cl[cl["DEBH"]==debh]
    for i in range(len(filtered1)):
        clde_ = {}
        SL = filtered1.iloc[i]["SL"]##shuliang
        HJ = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/az/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
            if np.isnan(clde_['YSJG']):
                clde_['YSJG']=0
            clde_["SL"] = SL.item() if type(SL) == float64 else SL
            clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
            clde.append(clde_)
        else:
            detail = pd.read_csv("de/az/JD_PeiBi.csv")
            filtered2 = detail[detail["RcjID"]==rcjID]
            if len(filtered2) > 0:
                CLBH = filtered2.iloc[0]["PBBH"]
                CLMC = filtered2.iloc[0]["PBMC"]
                JLDW = filtered2.iloc[0]["DW"]
                YSJG = filtered2.iloc[0]["Csdj"]
                clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
                clde_["CLMC"] = CLMC
                clde_["JLDW"] = JLDW
                clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
                if np.isnan(clde_['YSJG']):
                    clde_['YSJG']=0
                clde_["SL"] = SL.item() if type(SL) == float64 else SL
                clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                clde.append(clde_)

        if 'CLBH' in clde_ and clde_['CLBH'] in additional:
            additional.remove(clde_['CLBH'])
    detail = pd.read_csv("de/az/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([2, '2'])]
    added = detail[detail['CLBH'].isin(additional)]
    added = added.drop_duplicates(subset=['CLBH'])
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        if np.isnan(clde_['YSJG']):
            clde_['YSJG']=0
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)
    detail = pd.read_csv("de/az/JD_PeiBi.csv")
    added = detail[detail['PBBH'].isin(additional)]
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["PBBH"]
        CLMC = entry["PBMC"]
        JLDW = entry["DW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        if np.isnan(clde_['YSJG']):
            clde_['YSJG']=0
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)
        
    filtered = filtered.drop(['BH1', 'BH2', "GCLCLF", "GCLCLF1", "GCLGG", "GCLGLF", "GCLJJ", "GCLJXF", "GCLLR", "GCLRGF", "GCLRGR", "GCLSJDJ", "GCPBHJ",  "GLFFL", "LRFL", "PBBH", "PBBHM", "PBDJ", "PBSL", "THMC", "THSL", "dejb", "is_lock",  "mcxz", "qfbz",  "sptfl"], axis=1)
    return filtered.to_json(force_ascii=False), id, rgde, jxde, clde, bz_selected.to_json(force_ascii=False) if len(bz_selected) > 0 else None, bz_selected2.to_json(force_ascii=False) if len(bz_selected2) > 0 else None



def getSingleDeXilie_sz(debh):
    id = None
    bz_selected = []
    bz_selected2 = []
    additional = []
    if "附注" in debh:
        position = debh.find("附注")
        debh = debh[:position]
    if "*" in debh:
        position = debh.find("*")
        debh = debh[:position]
    if debh.endswith("换"):
        debh = debh[:-1]
    if "[" in debh:
        position = debh.find("[")
        debh = debh[:position-1]
    gj = pd.read_csv("de/sz/JD_DanWeiGJ.csv")
    filtered = gj[gj["DEBH"]==debh]
    ##print(filtered)
    if len(filtered) > 0:
        index = (filtered.iloc[0]["YSDELBH"])
        shu = pd.read_csv("de/sz/JD_DingEShu.csv")
        hit = shu[shu["zjh"]==index]
        if len(hit) > 0:
            id = hit.iloc[0]["ID"]
        beizhu = str(filtered.iloc[0]["BZBH"])
        if 'nan' in beizhu:
            pass
        else:
            beizhus = beizhu.split(";")
            ##print(beizhus)
            bzfile = pd.read_csv("de/sz/JD_BeiZhuZK.csv")
            bzfile2 = pd.read_csv("de/sz/JD_BeiZhuFK.csv", dtype=str)
            bz_selected = bzfile[bzfile["BZBH"].isin(beizhus)] 
            bz_selected2 = bzfile2[bzfile2["BZBH"].isin(beizhus)] 
            bz_selected2 = bz_selected2.astype({'BH': str})
            additional = bz_selected2['BH'].tolist()
            additional = [entry for entry in additional if not entry in ['000000', '000001', 'J00000', '100000', '800101', '015103', '066121', '066501', '066503', '070101', '070303', '070902', '071121', '241503', '8021', '80210']]
   
    else:
        return None, None, None, None, None, None, None
    ####rengong
    rgde = []
    rg = pd.read_csv("de/sz/JD_GuJiaRG.csv")
    filtered1 = rg[rg["DEBH"]==debh]
    for i in range(len(filtered1)):
        rgde_ = {}
        gr = filtered1.iloc[i]["RGR"]##gongri
        gf = filtered1.iloc[i]["RGF"]##gongfei
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/sz/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            rgde_["CLBH"] = CLBH
            rgde_["CLMC"] = CLMC
            rgde_["JLDW"] = JLDW
            rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
            rgde_["gr"] = gr.item() if type(gr) == float64 else gr
            rgde_["gf"] = gf.item() if type(gf) == float64 else gf
            if CLBH in additional:
                additional.remove(CLBH)
        rgde.append(rgde_)
    detail = pd.read_csv("de/sz/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([1, '1'])]
    added = detail[detail['CLBH'].isin(additional)]
    for _, entry in added.iterrows():
        rgde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        rgde_["CLBH"] = CLBH
        rgde_["CLMC"] = CLMC
        rgde_["JLDW"] = JLDW
        rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        rgde_["gr"] = 0
        rgde_["gf"] = 0
        rgde.append(rgde_)
    ####jixie
    jxde = []
    jx = pd.read_csv("de/sz/JD_GuJiaJX.csv")
    filtered1 = jx[jx["DEBH"]==debh]
    for i in range(len(filtered1)):
        jxde_ = {}
        sl = filtered1.iloc[i]["SL"]##shuliang
        hj = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/sz/JD_JiXieK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            jxbh = filtered2.iloc[0]["jxbh"]
            jxmc = filtered2.iloc[0]["jxmc"]
            DW = filtered2.iloc[0]["DW"]
            tbdj = filtered2.iloc[0]["Csdj"]
            jxde_["jxbh"] = jxbh
            jxde_["jxmc"] = jxmc
            jxde_["DW"] = DW
            jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
            jxde_["sl"] = sl.item() if type(sl) == float64 else sl
            jxde_["hj"] = hj.item() if type(hj) == float64 else hj
            if jxbh in additional:
                additional.remove(jxbh)
        jxde.append(jxde_)
    detail = pd.read_csv("de/sz/JD_JiXieK.csv")
    added = detail[detail['jxbh'].isin(additional)]
    for _, entry in added.iterrows():
        jxde_ = {}
        jxbh = entry["jxbh"]
        jxmc = entry["jxmc"]
        DW = entry["DW"]
        tbdj = entry["Csdj"]
        jxde_["jxbh"] = jxbh
        jxde_["jxmc"] = jxmc
        jxde_["DW"] = DW
        jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
        jxde_["sl"] = 0
        jxde_["hj"] = 0
        jxde.append(jxde_)
    ####cailiao
    clde = []
    cl = pd.read_csv("de/sz/JD_GuJiaCL.csv")
    filtered1 = cl[cl["DEBH"]==debh]
    for i in range(len(filtered1)):
        clde_ = {}
        SL = filtered1.iloc[i]["SL"]##shuliang
        HJ = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/sz/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        detail_ = pd.read_csv("de/sz/JD_PeiBi.csv")
        filtered2_ = detail_[detail_["RcjID"]==rcjID]
        if len(filtered2) > 0 and len(filtered2_) == 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
            clde_["SL"] = SL.item() if type(SL) == float64 else SL
            clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
            clde.append(clde_)
        elif len(filtered2) == 0 and len(filtered2_) > 0:
            CLBH = filtered2_.iloc[0]["PBBH"]
            CLMC = filtered2_.iloc[0]["PBMC"]
            JLDW = filtered2_.iloc[0]["DW"]
            YSJG = filtered2_.iloc[0]["Csdj"]
            clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG 
            clde_["SL"] = SL.item() if type(SL) == float64 else SL
            clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
            clde.append(clde_)
        elif len(filtered2) > 0 and len(filtered2_) > 0:
            YSJG = filtered2.iloc[0]["Csdj"]
            YSJG_ = filtered2_.iloc[0]["Csdj"]
            ##print(debh)
            if float(HJ) < 0.00001:
                if float(YSJG) < 0.00001:
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
                elif float(YSJG_) < 0.00001:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG 
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
            else:

                ratio = float(YSJG) / (float(HJ) / float(SL))
                ratio_ = float(YSJG_) / (float(HJ) / float(SL))
                if abs(ratio - 1) < abs(ratio_ - 1):
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
                else:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = str(CLBH.item()) if type(CLBH) == int64 else CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG 
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
        if 'CLBH' in clde_ and clde_['CLBH'] in additional:
            additional.remove(clde_['CLBH'])
    detail = pd.read_csv("de/sz/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([2, '2'])]
    added = detail[detail['CLBH'].isin(additional)]
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)
    detail = pd.read_csv("de/sz/JD_PeiBi.csv")
    added = detail[detail['PBBH'].isin(additional)]
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["PBBH"]
        CLMC = entry["PBMC"]
        JLDW = entry["DW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)

    filtered = filtered.drop(['BH1', 'BH2', "GCLCLF", "GCLCLF1", "GCLGG", "GCLGLF", "GCLJJ", "GCLJXF", "GCLLR", "GCLRGF", "GCLRGR", "GCLSJDJ", "GCPBHJ",  "GLFFL", "LRFL", "PBBH", "PBBHM", "PBDJ", "PBSL", "THMC", "THSL", "dejb", "is_lock",  "mcxz", "qfbz",  "sptfl"], axis=1)
    return filtered.to_json(force_ascii=False), id, rgde, jxde, clde, bz_selected.to_json(force_ascii=False) if len(bz_selected) > 0 else None, bz_selected2.to_json(force_ascii=False) if len(bz_selected2) > 0 else None
        
        
def getSingleDeXilie_tj(debh):
    id = None
    bz_selected = []
    bz_selected2 = []
    additional = []
       

    if "附注" in debh:
        position = debh.find("附注")
        debh = debh[:position]
    if "*" in debh:
        position = debh.find("*")
        debh = debh[:position]
    if debh.endswith("换"):
        debh = debh[:-1]
    if "[" in debh:
        position = debh.find("[")
        debh = debh[:position-1]
    gj = pd.read_csv("de/tj/JD_DanWeiGJ.csv")
    print(debh)
    filtered = gj[gj["DEBH"]==debh]
    ##print(filtered)
    if len(filtered) > 0:
        index = int(filtered.iloc[0]["YSDELBH"])
        shu = pd.read_csv("de/tj/JD_DingEShu.csv")
        hit = shu[shu["zjh"]==index]
        if len(hit) > 0:
            id = hit.iloc[0]["ID"]
        beizhu = str(filtered.iloc[0]["BZBH"])
        if 'nan' in beizhu:
            pass
        else:
            beizhus = beizhu.split(";")
            ##print(beizhus)
            bzfile = pd.read_csv("de/tj/JD_BeiZhuZK.csv")
            bzfile2 = pd.read_csv("de/tj/JD_BeiZhuFK.csv", dtype=str)
            bz_selected = bzfile[bzfile["BZBH"].isin(beizhus)] 
            bz_selected2 = bzfile2[bzfile2["BZBH"].isin(beizhus)]
            bz_selected2 = bz_selected2.astype({'BH': str})
            additional = bz_selected2['BH'].tolist()
            additional = [entry for entry in additional if not entry in ['000000', '000001', 'J00000', '100000', '800101', '015103', '066121', '066501', '066503', '070101', '070303', '070902', '071121', '241503', '8021', '80210']]
    else:
        return None, None, None, None, None, None, None
    ####rengong
    rgde = []
    rg = pd.read_csv("de/tj/JD_GuJiaRG.csv")
    filtered1 = rg[rg["DEBH"]==debh]
    for i in range(len(filtered1)):
        rgde_ = {}
        gr = filtered1.iloc[i]["RGR"]##gongri
        gf = filtered1.iloc[i]["RGF"]##gongfei
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/tj/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            rgde_["CLBH"] = CLBH
            rgde_["CLMC"] = CLMC
            rgde_["JLDW"] = JLDW
            rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
            rgde_["gr"] = gr.item() if type(gr) == float64 else gr
            rgde_["gf"] = gf.item() if type(gf) == float64 else gf
            if CLBH in additional:
                additional.remove(CLBH)
        rgde.append(rgde_)
    detail = pd.read_csv("de/tj/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([1, '1'])]
    added = detail[detail['CLBH'].isin(additional)]
    for _, entry in added.iterrows():
        rgde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        rgde_["CLBH"] = CLBH
        rgde_["CLMC"] = CLMC
        rgde_["JLDW"] = JLDW
        rgde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        rgde_["gr"] = 0
        rgde_["gf"] = 0
        rgde.append(rgde_)

    ####jixie
    jxde = []
    jx = pd.read_csv("de/tj/JD_GuJiaJX.csv")
    filtered1 = jx[jx["DEBH"]==debh]
    for i in range(len(filtered1)):
        jxde_ = {}
        sl = filtered1.iloc[i]["SL"]##shuliang
        hj = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/tj/JD_JiXieK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        if len(filtered2) > 0:
            jxbh = filtered2.iloc[0]["jxbh"]
            jxmc = filtered2.iloc[0]["jxmc"]
            DW = filtered2.iloc[0]["DW"]
            tbdj = filtered2.iloc[0]["Csdj"]
            jxde_["jxbh"] = jxbh
            jxde_["jxmc"] = jxmc
            jxde_["DW"] = DW
            jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
            jxde_["sl"] = sl.item() if type(sl) == float64 else sl
            jxde_["hj"] = hj.item() if type(hj) == float64 else hj
            if jxbh in additional:
                additional.remove(jxbh)
        jxde.append(jxde_)
    detail = pd.read_csv("de/tj/JD_JiXieK.csv")
    added = detail[detail['jxbh'].isin(additional)]
    for _, entry in added.iterrows():
        jxde_ = {}
        jxbh = entry["jxbh"]
        jxmc = entry["jxmc"]
        DW = entry["DW"]
        tbdj = entry["Csdj"]
        jxde_["jxbh"] = jxbh
        jxde_["jxmc"] = jxmc
        jxde_["DW"] = DW
        jxde_["tbdj"] = tbdj.item() if type(tbdj) == float64 else tbdj
        jxde_["sl"] = 0
        jxde_["hj"] = 0
        jxde.append(jxde_)

    clde = []
    cl = pd.read_csv("de/tj/JD_GuJiaCL.csv")
    filtered1 = cl[cl["DEBH"]==debh]
    for i in range(len(filtered1)):
        clde_ = {}
        SL = filtered1.iloc[i]["SL"]##shuliang
        HJ = filtered1.iloc[i]["HJ"]##hejia
        rcjID = filtered1.iloc[i]["RcjID"]
        detail = pd.read_csv("de/tj/JD_CaiLiaoK.csv")
        filtered2 = detail[detail["RcjID"]==rcjID]
        detail_ = pd.read_csv("de/tj/JD_PeiBi.csv")
        filtered2_ = detail_[detail_["RcjID"]==rcjID]
        if len(filtered2) > 0 and len(filtered2_) == 0:
            CLBH = filtered2.iloc[0]["CLBH"]
            CLMC = filtered2.iloc[0]["CLMC"]
            JLDW = filtered2.iloc[0]["JLDW"]
            YSJG = filtered2.iloc[0]["Csdj"]
            clde_["CLBH"] = CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
            clde_["SL"] = SL.item() if type(SL) == float64 else SL
            clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
            clde.append(clde_)
        elif len(filtered2) == 0 and len(filtered2_) > 0:
            CLBH = filtered2_.iloc[0]["PBBH"]
            CLMC = filtered2_.iloc[0]["PBMC"]
            JLDW = filtered2_.iloc[0]["DW"]
            YSJG = filtered2_.iloc[0]["Csdj"]
            clde_["CLBH"] = CLBH
            clde_["CLMC"] = CLMC
            clde_["JLDW"] = JLDW
            clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
            clde_["SL"] = SL.item() if type(SL) == float64 else SL
            clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
            clde.append(clde_)
        elif len(filtered2) > 0 and len(filtered2_) > 0:
            YSJG = filtered2.iloc[0]["Csdj"]
            YSJG_ = filtered2_.iloc[0]["Csdj"]
            if float(HJ) < 0.00001:
                if float(YSJG) < 0.00001:
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
                elif float(YSJG_) < 0.00001:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
            else:

                ratio = float(YSJG) / (float(HJ) / float(SL))
                ratio_ = float(YSJG_) / (float(HJ) / float(SL))
                if abs(ratio - 1) < abs(ratio_ - 1):
                    CLBH = filtered2.iloc[0]["CLBH"]
                    CLMC = filtered2.iloc[0]["CLMC"]
                    JLDW = filtered2.iloc[0]["JLDW"]
                    YSJG = filtered2.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
                else:
                    CLBH = filtered2_.iloc[0]["PBBH"]
                    CLMC = filtered2_.iloc[0]["PBMC"]
                    JLDW = filtered2_.iloc[0]["DW"]
                    YSJG = filtered2_.iloc[0]["Csdj"]
                    clde_["CLBH"] = CLBH
                    clde_["CLMC"] = CLMC
                    clde_["JLDW"] = JLDW
                    clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
                    clde_["SL"] = SL.item() if type(SL) == float64 else SL
                    clde_["HJ"] = HJ.item() if type(HJ) == float64 else HJ
                    clde.append(clde_)
        if 'CLBH' in clde_ and clde_['CLBH'] in additional:
            additional.remove(clde_['CLBH'])
        
    detail = pd.read_csv("de/tj/JD_CaiLiaoK.csv")
    detail = detail[detail['RcjLb'].isin([2, '2'])]
    added = detail[detail['CLBH'].isin(additional)]
    added = added.drop_duplicates(subset=['CLBH'])
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["CLBH"]
        CLMC = entry["CLMC"]
        JLDW = entry["JLDW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        
        clde.append(clde_)
    detail = pd.read_csv("de/tj/JD_PeiBi.csv")
    added = detail[detail['PBBH'].isin(additional)]
    for _, entry in added.iterrows():
        clde_ = {}
        CLBH = entry["PBBH"]
        CLMC = entry["PBMC"]
        JLDW = entry["DW"]
        YSJG = entry["Csdj"]
        clde_["CLBH"] = CLBH
        clde_["CLMC"] = CLMC
        clde_["JLDW"] = JLDW
        clde_["YSJG"] = YSJG.item() if type(YSJG) == float64 else YSJG
        clde_["SL"] = 0
        clde_["HJ"] = 0
        clde.append(clde_)


    filtered = filtered.drop(['BH1', 'BH2', "GCLCLF", "GCLCLF1", "GCLGG", "GCLGLF", "GCLJJ", "GCLJXF", "GCLLR", "GCLRGF", "GCLRGR", "GCLSJDJ", "GCPBHJ", "GLFFL", "LRFL", "PBBH", "PBBHM", "PBDJ", "PBSL", "THMC", "THSL", "dejb", "is_lock",  "mcxz", "qfbz",  "sptfl"], axis=1)
    return filtered.to_json(force_ascii=False), id, rgde, jxde, clde, bz_selected.to_json(force_ascii=False) if len(bz_selected) > 0 else None, bz_selected2.to_json(force_ascii=False) if len(bz_selected2) > 0 else None

def getDwgc(root, id, Zylb):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    id = id + "Zylb" + Zylb
    for child in item:
        if child.tag == "Fywj":
            result.append({"id": id + "bao jia hui zong", "label": "报价汇总表"})
        elif child.tag == "Gfsj":
            result.append({"id": id + "gui fei shui jin", "label": "规费税金"})
        elif child.tag == "Qdxm":
            result.append({"id": id + "qing dan xiang mu", "label": "清单项目"})
        elif child.tag == "Csxm":
            result.append({"id": id + "cuo shi xiang mu", "label": "措施项目"})
        elif child.tag == "Qtxm":
            result.append({"id": id +"qi ta xiang mu", "label": "其他项目"})
        elif child.tag == "Zlje":
            result.append({"id": id +"zan lie jin e", "label": "暂列金额"})
        elif child.tag == "Clzg":
            result.append({"id": id +"cai liao zan gu jia", "label": "材料暂估价"})
        elif child.tag == "Zygczg":
            result.append({"id": id + "zhuan ye gong cheng zan gu jia", "label": "专业工程暂估价"})
        elif child.tag == "Jrg":
            result.append({"id": id + "ji ri gong", "label": "计日工"})
        elif child.tag == "Zcbfwf":
            result.append({"id": id + "zong cheng bao fu wu fei", "label": "总承包服务费"})
        elif child.tag == "Zjxmjdkzffj":
            result.append({"id": id + "zong jia xiang mu jin du kuan zhi fu fen jie", "label": "总价项目进度款支付分解"})
        elif child.tag == "Fbrgycl":
            result.append({"id": id + "fa bao ren gong ying cai liao", "label": "发包人供应材料"})
        elif child.tag == "Cbrgycl":
            result.append({"id": id + "cheng bao ren gong ying cai liao", "label": "承包人供应材料"})
        elif child.tag == "Rcjhz":
            result.append({"id": id + "ren cai ji hui zong", "label": "人材机汇总"})
        else:
            print(child.tag)
            print("WARNING####################################################################")
    return result

def getDjcs(root, id):
    result = []
    #result.append(["ming cheng", "jin e", "zan gu jia", "anquan wenming fei", "guifei"])
    item = None
    for child in root:
    
        if child.tag == "Dxgcxx":
            for child1 in child:
                Dwgcbh = child1.attrib["Dwgcbh"]
                if Dwgcbh == id:
                    item = child1
                    break
            ##result.append([child.attrib["Dxgcmc"], child.attrib["Je"], child.attrib["Zgj"], child.attrib["Aqwmf"], child.attrib["Gf"]])
    Fywj = None
    for child in item:
        if child.tag == 'Csxm':
            Fywj= child
            break
    Djcs_parent = None
    for child in Fywj:
        if child.tag == 'DjCs':
            Djcs_parent = child
            break
    
    for child in Djcs_parent:
        ##child is mx {
        result.append({   

                    "序号": child.attrib["Xh"], "清单编码": child.attrib["Bm"], "名称" : child.attrib["Mc"],
                     "项目特征" : child.attrib["Xmtz"], 
                    "计算规则" : child.attrib["Jsgz"], "单位" : child.attrib["Dw"], "数量" : child.attrib["Sl"], 
                    "综合单价" : child.attrib["Zhdj"], "合价" : child.attrib["Zhhj"], "人工费" : child.attrib["Rgf"],
                     "主材费" : child.attrib["Zcf"],
                     "设备费" : child.attrib["Sbf"], "辅材费" : child.attrib["Fcf"], "材料费" : child.attrib["Clf"], 
                    "机械费" : child.attrib["Jxf"], "管理费" : child.attrib["Glf"], "利润"  : child.attrib["Lr"],
                     "暂估价" : child.attrib["Zgj"], "综合人工工日" : child.attrib["Zgr"] })
        __children = []
        zj_parent = None
        for grandchild in child:
            if grandchild.tag == "Csxdezj":
                zj_parent = grandchild
        
        if zj_parent:
            for entry in zj_parent:
                key = str(uuid.uuid4())
                entry.set("key", key)
                __children.append({
                    "key": key,
                    "序号": None, "清单编码": entry.attrib["Debm"], "名称" : entry.attrib["Mc"],
                     "项目特征" : None, 
                    "计算规则" : None, "单位" : entry.attrib["Dw"], "数量" : entry.attrib["DwQdSl"], 
                    "综合单价" : entry.attrib["Dj"], "合价" : entry.attrib["Hj"], "人工费" : entry.attrib["Rgf"],
                     "主材费" : entry.attrib["Zcf"],
                     "设备费" : entry.attrib["Sbf"], "辅材费" : entry.attrib["Fcf"], "材料费" : entry.attrib["Clf"], 
                    "机械费" : entry.attrib["Jxf"], "管理费" : entry.attrib["Glf"], "利润"  : entry.attrib["Lr"],
                     "暂估价" : None, "综合人工工日" : None })

        if len(__children) > 0:
            result[-1]["__children"] = __children 



    return result