qingdan/subdir/db.py

import xml.etree.ElementTree as ET
from subdir import service, util
from bson.objectid import ObjectId
import uuid
from datetime import datetime
import numpy as np
import re
from fastapi import HTTPException, status

###############helper##################

def getDingercj_default(root, bh, bt, qdbm, debm, key):
    result = [["ID", "人材机编码", "名称", "规格型号", "单位", "单价", "产地", 
    "供应厂商", "人材机类别", "甲供标志", "含量", "合价", "暂估价标志", "主要材料标志", "主材标志", "设备标志", "隐藏", "key", "bc" ]]
    #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
    Rcjhz = None
    for child in item:
        if child.tag == 'Rcjhz':
            Rcjhz = child
            break
    kv = {}
    for entry in Rcjhz:
        kv[entry.attrib["RcjId"]]=entry

    container = None
    for child in Fywj:
        ##child is mx
        if child.tag == "Qdmx" and child.attrib["Qdbm"] == qdbm:
            zimu = child
            break
    box = None
    if not zimu:
        return result
    for child in zimu:
        if child.tag == "Qdxdezj":
            box = child
            break
    hlmx_parent = None
    for child in box:
        if child.attrib["Debm"] == debm and child.attrib['key'] == key:
            for grandchild in child:
                if grandchild.tag == "Qdxdercjhl":
                    hlmx_parent = grandchild
    for child in hlmx_parent:
        result.append([child.attrib["RcjId"], 
        kv[child.attrib["RcjId"]].attrib["RcjBm"], 
        kv[child.attrib["RcjId"]].attrib["Mc"], 
        kv[child.attrib["RcjId"]].attrib["Ggxh"], 
        kv[child.attrib["RcjId"]].attrib["Dw"], 
        kv[child.attrib["RcjId"]].attrib["Dj"], 
        kv[child.attrib["RcjId"]].attrib["Cd"], 
        kv[child.attrib["RcjId"]].attrib["Gycs"], 
        kv[child.attrib["RcjId"]].attrib["Rcjlb"], 
        kv[child.attrib["RcjId"]].attrib["Jgbz"], 
        child.attrib["Rcjhl"], child.attrib["Rcjhj"], kv[child.attrib["RcjId"]].attrib["Zgjbz"], 
             kv[child.attrib["RcjId"]].attrib["Zyclbz"], kv[child.attrib["RcjId"]].attrib["Zcbz"], kv[child.attrib["RcjId"]].attrib["Sbbz"],  "", ""
        ])

    return result
def getDingercj_(root, bh, bt, qdbm, debm, key):
    if bt == 'Default':
        return getDingercj_default(root, bh, bt, qdbm, debm, key)
    result = [["ID", "人材机编码", "名称", "规格型号", "单位", "单价", "产地", 
    "供应厂商", "人材机类别", "甲供标志", "含量", "合价", "暂估价标志", "主要材料标志", "主材标志", "设备标志", "key", "bc" ]]
    #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
    Rcjhz = None
    for child in item:
        if child.tag == 'Rcjhz':
            Rcjhz = child
            break
    kv = {}
    for entry in Rcjhz:
        kv[entry.attrib["RcjId"]]=entry

    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 not container:
        container = Fywj
    zimu = None
    for child in container:
        if child.attrib["Qdbm"] == qdbm:
            zimu = child
            break
    box = None
    if not zimu:
        return result
    for child in zimu:
        if child.tag == "Qdxdezj":
            box = child
            break
    hlmx_parent = None
    for child in box:
        if child.attrib["Debm"] == debm and child.attrib['key'] == key:
            for grandchild in child:
                if grandchild.tag == "Qdxdercjhl":
                    hlmx_parent = grandchild
    for child in hlmx_parent:
        result.append([child.attrib["RcjId"], 
        kv[child.attrib["RcjId"]].attrib["RcjBm"], 
        kv[child.attrib["RcjId"]].attrib["Mc"], 
        kv[child.attrib["RcjId"]].attrib["Ggxh"], 
        kv[child.attrib["RcjId"]].attrib["Dw"], 
        kv[child.attrib["RcjId"]].attrib["Dj"], 
        kv[child.attrib["RcjId"]].attrib["Cd"], 
        kv[child.attrib["RcjId"]].attrib["Gycs"], 
        kv[child.attrib["RcjId"]].attrib["Rcjlb"], 
        kv[child.attrib["RcjId"]].attrib["Jgbz"], 
        child.attrib["Rcjhl"], child.attrib["Rcjhj"], kv[child.attrib["RcjId"]].attrib["Zgjbz"], 
             kv[child.attrib["RcjId"]].attrib["Zyclbz"], kv[child.attrib["RcjId"]].attrib["Zcbz"], kv[child.attrib["RcjId"]].attrib["Sbbz"], "", ""
        ])

    return result


def getQingdanrcj_default(root, bh, bt, bm):
   
    result = [["ID", "人材机编码", "名称", "规格型号", "单位", "单价", "产地", 
    "供应厂商", "人材机类别", "甲供标志", "含量", "合价", "暂估价标志", "主要材料标志", "主材标志", "设备标志"]]
    #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
    Rcjhz = None
    for child in item:
        if child.tag == 'Rcjhz':
            Rcjhz = child
            break
    kv = {}
    for entry in Rcjhz:
        kv[entry.attrib["RcjId"]]=entry

    container = None
    for child in Fywj:
        ##child is mx
        if child.tag == "Qdmx" and child.attrib["Qdbm"] == bm:
            zimu = child
            break
    box = None
    if not zimu:
        return result
    for child in zimu:
        if child.tag == "Qdxrcjhl":
            box = child
            break
    for child in box:
        result.append([child.attrib["RcjId"], 
        kv[child.attrib["RcjId"]].attrib["RcjBm"], 
        kv[child.attrib["RcjId"]].attrib["Mc"], 
        kv[child.attrib["RcjId"]].attrib["Ggxh"], 
        kv[child.attrib["RcjId"]].attrib["Dw"], 
        kv[child.attrib["RcjId"]].attrib["Dj"], 
        kv[child.attrib["RcjId"]].attrib["Cd"], 
        kv[child.attrib["RcjId"]].attrib["Gycs"], 
        kv[child.attrib["RcjId"]].attrib["Rcjlb"], 
        kv[child.attrib["RcjId"]].attrib["Jgbz"], 
        child.attrib["Rcjhl"], child.attrib["Rcjhj"], kv[child.attrib["RcjId"]].attrib["Zgjbz"], 
        kv[child.attrib["RcjId"]].attrib["Zyclbz"], kv[child.attrib["RcjId"]].attrib["Zcbz"], kv[child.attrib["RcjId"]].attrib["Sbbz"]
        ])

    return result


def getQingdanrcj_(root, bh, bt, bm):
    if bt == 'Default':
        return getQingdanrcj_default(root, bh, bt, bm)
    
    result = [["ID", "人材机编码", "名称", "规格型号", "单位", "单价", "产地", 
    "供应厂商", "人材机类别", "甲供标志", "含量", "合价", "暂估价标志", "主要材料标志", "主材标志", "设备标志" ]]
    #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
    Rcjhz = None
    for child in item:
        if child.tag == 'Rcjhz':
            Rcjhz = child
            break
    kv = {}
    for entry in Rcjhz:
        kv[entry.attrib["RcjId"]]=entry

    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
    zimu = None
    for child in container:
        if child.attrib["Qdbm"] == bm:
            zimu = child
            break
    box = None
    if not zimu:
        return result
    for child in zimu:
        if child.tag == "Qdxrcjhl":
            box = child
            break
    for child in box:
        result.append([child.attrib["RcjId"], 
        kv[child.attrib["RcjId"]].attrib["RcjBm"], 
        kv[child.attrib["RcjId"]].attrib["Mc"], 
        kv[child.attrib["RcjId"]].attrib["Ggxh"], 
        kv[child.attrib["RcjId"]].attrib["Dw"], 
        kv[child.attrib["RcjId"]].attrib["Dj"], 
        kv[child.attrib["RcjId"]].attrib["Cd"], 
        kv[child.attrib["RcjId"]].attrib["Gycs"], 
        kv[child.attrib["RcjId"]].attrib["Rcjlb"], 
        kv[child.attrib["RcjId"]].attrib["Jgbz"], 
        child.attrib["Rcjhl"], child.attrib["Rcjhj"], kv[child.attrib["RcjId"]].attrib["Zgjbz"], 
        kv[child.attrib["RcjId"]].attrib["Zyclbz"], kv[child.attrib["RcjId"]].attrib["Zcbz"], kv[child.attrib["RcjId"]].attrib["Sbbz"]
        ])

    return result


def getDjcsDingercj_(root, bh, bt, qdbm, debm, key):
    result = [["ID", "人材机编码", "名称", "规格型号", "单位", "单价", "产地", 
    "供应厂商", "人材机类别", "甲供标志", "含量", "合价", "暂估价标志", "主要材料标志", "主材标志", "设备标志", "key", "bc" ]]
    #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 == 'Csxm':
            Fywj= child
            break
    Rcjhz = None
    for child in item:
        if child.tag == 'Rcjhz':
            Rcjhz = child
            break
    kv = {}
    for entry in Rcjhz:
        kv[entry.attrib["RcjId"]]=entry

    container = None
    for child in Fywj:
        if child.tag == "DjCs":
            container = child
            break
    zimu = None
    for child in container:
        if child.attrib["Bm"] == qdbm:
            zimu = child
            break
    box = None
    if not zimu:
        return result
    for child in zimu:
        if child.tag == "Csxdezj":
            box = child
            break
    hlmx_parent = None
    for child in box:
        if child.attrib["Debm"] == debm and child.attrib['key'] == key:
            for grandchild in child:
                if grandchild.tag == "Csxdercjhl":
                    hlmx_parent = grandchild
    for child in hlmx_parent:
        result.append([child.attrib["RcjId"], 
        kv[child.attrib["RcjId"]].attrib["RcjBm"], 
        kv[child.attrib["RcjId"]].attrib["Mc"], 
        kv[child.attrib["RcjId"]].attrib["Ggxh"], 
        kv[child.attrib["RcjId"]].attrib["Dw"], 
        kv[child.attrib["RcjId"]].attrib["Dj"], 
        kv[child.attrib["RcjId"]].attrib["Cd"], 
        kv[child.attrib["RcjId"]].attrib["Gycs"], 
        kv[child.attrib["RcjId"]].attrib["Rcjlb"], 
        kv[child.attrib["RcjId"]].attrib["Jgbz"], 
        child.attrib["Rcjhl"], child.attrib["Rcjhj"], kv[child.attrib["RcjId"]].attrib["Zgjbz"], 
             kv[child.attrib["RcjId"]].attrib["Zyclbz"], kv[child.attrib["RcjId"]].attrib["Zcbz"], 
             kv[child.attrib["RcjId"]].attrib["Sbbz"], "", ""
        ])

    return result
def getDjcsQingdanrcj_(root, bh, bt, bm):
    result = [["ID", "人材机编码", "名称", "规格型号", "单位", "单价", "产地", 
    "供应厂商", "人材机类别", "甲供标志", "含量", "合价", "暂估价标志", "主要材料标志", "主材标志", "设备标志" ]]
    #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 == 'Csxm':
            Fywj= child
            break
    hl_parent = None
    for child in Fywj:
        if child.tag == "DjCs":
            hl_parent = child
    Rcjhz = None
    for child in item:
        if child.tag == 'Rcjhz':
            Rcjhz = child
            break
    kv = {}
    for entry in Rcjhz:
        kv[entry.attrib["RcjId"]]=entry

    zimu = None
    for child in hl_parent:
        if child.attrib["Bm"] == bm:
            zimu = child
            break
    box = None
    if not zimu:
        return result
    for child in zimu:
        if child.tag == "Csxrcjhl":
            box = child
            break
    for child in box:
        result.append([child.attrib["RcjId"], 
        kv[child.attrib["RcjId"]].attrib["RcjBm"], 
        kv[child.attrib["RcjId"]].attrib["Mc"], 
        kv[child.attrib["RcjId"]].attrib["Ggxh"], 
        kv[child.attrib["RcjId"]].attrib["Dw"], 
        kv[child.attrib["RcjId"]].attrib["Dj"], 
        kv[child.attrib["RcjId"]].attrib["Cd"], 
        kv[child.attrib["RcjId"]].attrib["Gycs"], 
        kv[child.attrib["RcjId"]].attrib["Rcjlb"], 
        kv[child.attrib["RcjId"]].attrib["Jgbz"], 
        child.attrib["Rcjhl"], child.attrib["Rcjhj"], kv[child.attrib["RcjId"]].attrib["Zgjbz"], 
        kv[child.attrib["RcjId"]].attrib["Zyclbz"], kv[child.attrib["RcjId"]].attrib["Zcbz"], kv[child.attrib["RcjId"]].attrib["Sbbz"]
        ])

    return result
def getRcjbc(rcj):
    result = []
    for entry in rcj:
        if  '10000F' in entry[1]:
            result.append({
                "id": str(uuid.uuid4()),
                "主材标志": "true",
                "主要材料标志": "true",
                "产地": "",
                "人材机类别": "2",
                "人材机编码": entry[1],
                "供应厂商": "",
                "单价": entry[5],
                "单位": entry[4],
                "名称": entry[2],
                "含量": entry[10],
                "暂估价标志": "",
                "甲供标志": "",
                "规格型号": "",
                "设备标志": ""
            })
    return result


######################end of helper ########################
async def resolve(data, user, client):
    
    root = ET.fromstring(data)
    ##print(root)

    Dwgcbh = []
    jingjibiao = {}
    jingjibiao['user'] = user
    jingjibiao['visible'] = False
    jingjibiao['created'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    jingjibiao["BiaoDuanNO"] = root.attrib["BiaoDuanNO"]
    jingjibiao["Jsfs"] = root.attrib["Jsfs"]
    jingjibiao["Version"] = root.attrib["Version"]
    jingjibiao["Xmmc"] = root.attrib["Xmmc"]
    jingjibiao["Dxgcxx"] = []
    for child in root:
        if child.tag == "ZhaoBiaoXx":
            jingjibiao["ZhaoBiaoXx"] = {}
            jingjibiao["ZhaoBiaoXx"]["BzTime"] = child.attrib["BzTime"]
            jingjibiao["ZhaoBiaoXx"]["Bzr"] = child.attrib["Bzr"]
            jingjibiao["ZhaoBiaoXx"]["FhTime"] = child.attrib["FhTime"]
            jingjibiao["ZhaoBiaoXx"]["Fhr"] = child.attrib["Fhr"]
            jingjibiao["ZhaoBiaoXx"]["Zbr"] = child.attrib["Zbr"]
            jingjibiao["ZhaoBiaoXx"]["ZbrDb"] = child.attrib["ZbrDb"]
            jingjibiao["ZhaoBiaoXx"]["Zxr"] = child.attrib["Zxr"]
            jingjibiao["ZhaoBiaoXx"]["ZxrDb"] = child.attrib["ZxrDb"]
        elif child.tag == "TouBiaoXx":
            jingjibiao["TouBiaoXx"] = {}
            jingjibiao["TouBiaoXx"]["Zbr"] = child.attrib["Zbr"]
            jingjibiao["TouBiaoXx"]["Tbr"] = child.attrib["Tbr"]
            jingjibiao["TouBiaoXx"]["TbrDb"] = child.attrib["TbrDb"]
            jingjibiao["TouBiaoXx"]["Bzr"] = child.attrib["Bzr"]
            jingjibiao["TouBiaoXx"]["BzTime"] = child.attrib["BzTime"]
            jingjibiao["TouBiaoXx"]["Tbzj"] = child.attrib["Tbzj"]
            jingjibiao["TouBiaoXx"]["Zgj"] = child.attrib["Zgj"]
            jingjibiao["TouBiaoXx"]["Aqwmf"] = child.attrib["Aqwmf"]
            jingjibiao["TouBiaoXx"]["Gf"] = child.attrib["Gf"]
        elif child.tag == "Dxgcxx":
            Dxgcxx = {}
            Dxgcxx["Aqwmf"] = child.attrib["Aqwmf"]
            Dxgcxx["Dxgcbh"] = child.attrib["Dxgcbh"]
            Dxgcxx["Dxgcmc"] = child.attrib["Dxgcmc"]
            Dxgcxx["Gf"] = child.attrib["Gf"]
            Dxgcxx["Je"] = child.attrib["Je"]
            Dxgcxx["Zgj"] = child.attrib["Zgj"]
            Dxgcxx["Dwgc"] = []

            for grandchild in child:
                if grandchild.tag == "Dwgcxx":
                    Dwgcxx = {}
                    Dwgcxx["DogNum"] = grandchild.attrib["DogNum"]
                    Dwgcxx["Dwgcbh"] = grandchild.attrib["Dwgcbh"]
                    Dwgcbh.append({"bh": grandchild.attrib["Dwgcbh"], "num":  grandchild.attrib["DogNum"]})
                    Dwgcxx["Dwgcmc"] = grandchild.attrib["Dwgcmc"]
                    Dwgcxx["MachineKey"] = grandchild.attrib["MachineKey"]
                    Dwgcxx["SoftName"] = grandchild.attrib["SoftName"]
                    Dwgcxx["SoftNum"] = grandchild.attrib["SoftNum"]
                    Dwgcxx["Zylb"] = grandchild.attrib["Zylb"]
                    Dxgcxx["Dwgc"].append(Dwgcxx)
            jingjibiao["Dxgcxx"].append(Dxgcxx)

    if not 'TouBiaoXx' in jingjibiao:
        jingjibiao['TouBiaoXx'] = {'Tbzj' : '', 'Zgj' : '', 'Aqwmf' : '', 'Gf' : '' } 
    db = client["baojia"]
    collection = db["jingjibiao"]
    biao_id = (await collection.insert_one(jingjibiao)).inserted_id
    biao_id = str(biao_id)

    collection = db["Dwgc"]
    for entry in Dwgcbh:
        bjhz = service.getBjhz(root, entry["bh"])###array which contains __children
        gfsj = service.getGfsj(root, entry["bh"])##array which contains __children
        qtxm = service.getQtxm(root, entry["bh"])##array which contains __children
        zlje = service.getZlje(root, entry["bh"])
        jrg = service.getJrg(root, entry["bh"])
        zcbfwf = service.getZcbfwf(root, entry["bh"])
        fbrgycl = service.getFbrgycl(root, entry["bh"])
        rcjhz = service.getRcjhz(root, entry["bh"])
        zjcs = service.getZjcs(root, entry["bh"])
        qdbt = service.getQdxm(root, entry["bh"])
        qdbt_addition = service.getQdxm_addition(root, entry["bh"])
        zygczgj = service.getZygczgj(root, entry["bh"])
        Dwgc = {}
        Dwgc["bjhz"] = bjhz
        Dwgc["gfsj"] = gfsj
        Dwgc["qtxm"] = qtxm
        Dwgc["zlje"] = zlje
        Dwgc["jrg"] = jrg
        Dwgc['zygczgj'] = zygczgj
        Dwgc["zcbfwf"] = zcbfwf
        Dwgc["fbrgycl"] = fbrgycl
        Dwgc["rcjhz"] = rcjhz
        Dwgc["zjcs"] = zjcs
        Dwgc["qdbt"] = qdbt
        Dwgc['qdbt_addition'] = qdbt_addition
        Dwgc["DogNum"] = entry["num"]
        Dwgc["Dwgcbh"] = entry["bh"]
        Dwgc["biao_id"] = biao_id
        await collection.insert_one(Dwgc)

    collection = db["Djcs"]
    for entry in Dwgcbh:
        
        djcs = service.getDjcs(root, entry["bh"])
        for cs in djcs:
            cs["DogNum"] = entry["num"]
            cs["Dwgcbh"] = entry["bh"]
            cs["biao_id"] = biao_id
            rcj = getDjcsQingdanrcj_(root, entry["bh"], "", cs['清单编码'])
            cs["rcj"] = rcj
            if "__children" in cs:
                children = cs["__children"]
                for child in children:
                    dercj = getDjcsDingercj_(root, entry["bh"], "", cs["清单编码"], child["清单编码"], child['key'])
                    child["dercj"] = dercj
                    child['fuzhuEnable'] = False
                    child['yuban'] = []
            await collection.insert_one(cs)

    collection = db["qdxm"]
    for entry in Dwgcbh:
        
        bts = service.getQdxm(root, entry["bh"])
        for bt in bts:
            if bt == 'Default':
                qdmx = service.getQdmx_default(root, entry["bh"], bt)
            else:
                qdmx = service.getQdmx(root, entry["bh"], bt)
            for qd in qdmx:
                qd["DogNum"] = entry["num"]
                qd["Dwgcbh"] = entry["bh"]
                qd["biao_id"] = biao_id
                qd["bt"] = bt
                rcj = getQingdanrcj_(root, entry["bh"], bt, qd["清单编码"])
                qd["rcj"] = rcj
                if "__children" in qd:
                    children = qd["__children"]
                    for child in children:
                        dercj = getDingercj_(root, entry["bh"], bt, qd["清单编码"], child["清单编码"], child['key'])
                        child["dercj"] = dercj
                        child['fuzhuEnable'] = False
                        child['yuban'] = []
                        child['rcjbc'] = getRcjbc(dercj)
                        
                            
                        child['azfytag'] = []###常规定额
                qd['user'] = user          
                await collection.insert_one(qd)
            

    collection = db["qufei"]
    document = jingjibiao
    dxgc = document['Dxgcxx']
    result = []
    for entry in dxgc:
        newone = {"名称" : entry['Dxgcmc'],  "工程类型":"", "工程类别": "", "管理费（%）": "", "利润（%）": "","备注":"", "key" : str(uuid.uuid4()) }
        children = []
        for dwgc in entry['Dwgc']:
            children.append({"名称" : dwgc['Dwgcmc'], "工程类型":"",  "工程类别": "", "管理费（%）": "", "利润（%）": "","备注":"", "key" : dwgc['Dwgcbh']})
        newone["children"] = children
        result.append(newone)
    await collection.insert_one({"biao_id": biao_id, "user": user, "qufei": [{
        "名称" : document['Xmmc'], "工程类型":"", "工程类别": "", "管理费（%）": "", "利润（%）": "","备注":"", "key" : str(uuid.uuid4()), "children" : result
    }]})

    collection = db["jingjibiao"]

    await collection.update_one({'_id': ObjectId(biao_id)}, {'$set': {'visible': True}})






def dfs(items):
    result = []
    for entry in items:
        result.append([entry['序号'], entry['名称'], entry['金额'], entry['暂估价'], entry['类别']])
        if '__children' in entry:
            result_ = dfs(entry['__children'])
            for r in result_:
                result.append(r)
    return result

def dfs2(items):
    result = []
    for entry in items:
        result.append([entry['序号'], entry['名称'], entry['取费基数'], entry['计算基础'], entry['费率'], entry['金额'], entry['类别']])
        if '__children' in entry:
            result_ = dfs2(entry['__children'])
            for r in result_:
                result.append(r)
    return result

def dfs3(items):
    result = []
    for entry in items:
        result.append([entry['序号'], entry['名称'], entry['金额'], entry['项目类别'], entry['备注']])
        if '__children' in entry:
            result_ = dfs3(entry['__children'])
            for r in result_:
                result.append(r)
    return result

def roundstr(input):
    a = float(input)
    b = round(a, 2)
    return str(b)


def roundstr4(input):
    a = float(input)
    b = round(a, 4)
    return str(b)



###################################build xml file##################################
async def build(client, root, id):
    db = client["baojia"]
    collection = db["jingjibiao"]
    document = await collection.find_one({'_id': ObjectId(id)})

    root.set('BiaoDuanNO', document['BiaoDuanNO'])
    root.set('Jsfs', document['Jsfs'])
    root.set('Version', document['Version'])
    root.set('Xmmc', document['Xmmc'])

    TouBiaoXx = ET.SubElement(root, 'TouBiaoXx')
    ##{'Tbzj' : '', 'Zgj' : '', 'Aqwmf' : '', 'Gf' : '' } 
    TouBiaoXx.set('Tbzj', roundstr(document['TouBiaoXx']['Tbzj']))
    TouBiaoXx.set('Zgj', roundstr(document['TouBiaoXx']['Zgj']))
    TouBiaoXx.set('Aqwmf', roundstr(document['TouBiaoXx']['Aqwmf']))
    TouBiaoXx.set('Gf', roundstr(document['TouBiaoXx']['Gf']))
    if 'Zbr' in document['TouBiaoXx']:
        TouBiaoXx.set('Zbr', str(document['TouBiaoXx']['Zbr']))
    if 'Tbr' in document['TouBiaoXx']:
        TouBiaoXx.set('Tbr', str(document['TouBiaoXx']['Tbr']))
    if 'TbrDb' in document['TouBiaoXx']:
        TouBiaoXx.set('TbrDb', str(document['TouBiaoXx']['TbrDb']))
    if 'Bzr' in document['TouBiaoXx']:
        TouBiaoXx.set('Bzr', str(document['TouBiaoXx']['Bzr']))
    if 'BzTime' in document['TouBiaoXx']:
        TouBiaoXx.set('BzTime', str(document['TouBiaoXx']['BzTime']))
    for item in document['Dxgcxx']:
        Dxgcxx = ET.SubElement(root, 'Dxgcxx')
        Dxgcxx.set('Dxgcbh', str(item['Dxgcbh']))
        Dxgcxx.set('Dxgcmc', str(item['Dxgcmc']))
        Dxgcxx.set('Je', roundstr(item['Je']))
        Dxgcxx.set('Zgj', roundstr(item['Zgj']))
        Dxgcxx.set('Aqwmf', roundstr(item['Aqwmf']))
        Dxgcxx.set('Gf', roundstr(item['Gf']))
        for entry in item['Dwgc']:
            Dwgcxx = ET.SubElement(Dxgcxx, 'Dwgcxx')
            Dwgcxx.set('Dwgcbh', str(entry['Dwgcbh']))
            Dwgcxx.set('Dwgcmc', str(entry['Dwgcmc']))
            Dwgcxx.set('Zylb', str(entry['Zylb']))
            Dwgcxx.set('SoftName', str(entry['SoftName']))
            Dwgcxx.set('SoftNum', str(entry['SoftNum']))
            Dwgcxx.set('DogNum', str(entry['DogNum']))
            Dwgcxx.set('MachineKey', str(entry['MachineKey']))
            collection = db["Dwgc"]
            Dwgc = await collection.find_one({'biao_id': id, "Dwgcbh": entry['Dwgcbh']})
            bjhz = dfs(Dwgc['bjhz'])
            gfsj = dfs2(Dwgc['gfsj'])
            qtxm = dfs3(Dwgc['qtxm'])
            zlje = Dwgc['zlje']
            zygczgj = Dwgc['zygczgj']
            jrg = Dwgc['jrg']
            zcbfwf = Dwgc['zcbfwf']
            fbrgycl = Dwgc['fbrgycl']
            rcjhz = Dwgc['rcjhz']
            zjcs = Dwgc['zjcs']
            qdbt = Dwgc['qdbt']
            ########################################Fywj##########################
            Fywj = ET.SubElement(Dwgcxx, 'Fywj')
            for bjhz_ in bjhz:
                FywjMx = ET.SubElement(Fywj, 'FywjMx')
                FywjMx.set('Xh', str(bjhz_[0]))
                FywjMx.set('Mc', str(bjhz_[1]))
                FywjMx.set('Je', roundstr(bjhz_[2]))
                FywjMx.set('Zgj', roundstr(bjhz_[3]))
                FywjMx.set('Fyxlb', str(bjhz_[4]))
            ########################################Gfsj##########################
            Gfsj = ET.SubElement(Dwgcxx, 'Gfsj')
            for gfsj_ in gfsj:
                GfsjMx = ET.SubElement(Gfsj, 'GfsjMx')
                GfsjMx.set('Xh', str(gfsj_[0]))
                GfsjMx.set('Mc', str(gfsj_[1]))
                GfsjMx.set('Qfjs', roundstr(gfsj_[2]))
                GfsjMx.set('Jsjc', str(gfsj_[3]))
                GfsjMx.set('Fl', str(gfsj_[4]))
                GfsjMx.set('Je', roundstr(gfsj_[5]))
                GfsjMx.set('Fyxlb', str(gfsj_[6]))
            ########################################Qdxm##########################
            Qdxm = ET.SubElement(Dwgcxx, 'Qdxm')
            if 'Default' in qdbt:
                collection = db["qdxm"]
                before_sort = []
                async for post in collection.find({"biao_id": id, "Dwgcbh": entry['Dwgcbh'], 'bt': 'Default'}):
                    before_sort.append(post)
                after_sort = sorted(before_sort, key=lambda x: int(x['序号']))
                for qdmx in after_sort:
                    Qdmx = ET.SubElement(Qdxm, 'Qdmx')
                    Qdmx.set('Xh', str(qdmx['序号']))
                    Qdmx.set('Qdbm', str(qdmx['清单编码']))
                    Qdmx.set('Mc', str(qdmx['名称']))
                    Qdmx.set('Xmtz', str(qdmx['项目特征']))
                    Qdmx.set('Jsgz', str(qdmx['计算规则']))
                    Qdmx.set('Dw', str(qdmx['单位']))
                    Qdmx.set('Sl', roundstr4(qdmx['数量']))
                    Qdmx.set('Zhdj', roundstr4(qdmx['综合单价']))
                    Qdmx.set('Zhhj', roundstr4(qdmx['合价']))
                    Qdmx.set('Rgf', roundstr4(qdmx['人工费']))
                    Qdmx.set('Zcf', roundstr4(qdmx['主材费']))
                    Qdmx.set('Sbf', roundstr4(qdmx['设备费']))
                    Qdmx.set('Fcf', roundstr4(qdmx['辅材费']))
                    Qdmx.set('Clf', roundstr4(qdmx['材料费']))
                    Qdmx.set('Jxf', roundstr4(qdmx['机械费']))
                    Qdmx.set('Glf', roundstr4(qdmx['管理费']))
                    Qdmx.set('Lr', roundstr4(qdmx['利润']))
                    Qdmx.set('Zgj', roundstr4(qdmx['暂估价']))
                    Qdmx.set('Zgr', roundstr4(qdmx['综合人工工日']))
                    Qdmx.set('Bz', str(qdmx['备注']))
                    Qdxdezj = ET.SubElement(Qdmx, 'Qdxdezj')
                    if '__children' in qdmx:
                        for child in qdmx['__children']:
                            QdxdezjMx = ET.SubElement(Qdxdezj, 'QdxdezjMx')
                            QdxdezjMx.set('Debm', str(child['清单编码']))
                            QdxdezjMx.set('Mc', str(child['名称']))
                            QdxdezjMx.set('Dw', str(child['单位']))
                            QdxdezjMx.set('DwQdSl', roundstr4(child['数量']))
                            QdxdezjMx.set('Dj', roundstr4(child['综合单价']))
                            QdxdezjMx.set('Hj', roundstr4(child['合价']))
                            QdxdezjMx.set('Rgf', roundstr4(child['人工费']))
                            QdxdezjMx.set('Zcf', roundstr4(child['主材费']))
                            QdxdezjMx.set('Sbf', roundstr4(child['设备费']))
                            QdxdezjMx.set('Fcf', roundstr4(child['辅材费']))
                            QdxdezjMx.set('Clf', roundstr4(child['材料费']))
                            QdxdezjMx.set('Jxf', roundstr4(child['机械费']))
                            QdxdezjMx.set('Glf', roundstr4(child['管理费']))
                            QdxdezjMx.set('Lr', roundstr4(child['利润']))
                            Qdxdercjhl = ET.SubElement(QdxdezjMx, 'Qdxdercjhl')
                            for i in range(len(child['dercj'])):
                                if i == 0:
                                    pass
                                else:
                                    QdxdercjhlMx = ET.SubElement(Qdxdercjhl, 'QdxdercjhlMx')
                                    QdxdercjhlMx.set('RcjId', util.indexrcj(rcjhz, child['dercj'][i]))
                                    QdxdercjhlMx.set('Rcjhl', roundstr4(child['dercj'][i][10]))
                                    QdxdercjhlMx.set('Rcjhj', roundstr4(child['dercj'][i][11]))
                    Qdxrcjhl = ET.SubElement(Qdmx, 'Qdxrcjhl')
                    for i in range(len(qdmx['rcj'])):
                        if i == 0:
                            pass
                        else:
                            QdxrcjhlMx = ET.SubElement(Qdxrcjhl, 'QdxrcjhlMx')
                            QdxrcjhlMx.set('RcjId', util.indexrcj(rcjhz, qdmx['rcj'][i]))
                                           ##str(djcs['rcj'][i][0]))
                            QdxrcjhlMx.set('Rcjhl', roundstr4(qdmx['rcj'][i][10]))
                            QdxrcjhlMx.set('Rcjhj', roundstr4(qdmx['rcj'][i][11]))
                            QdxrcjhlMx.set('Zgjbz', str(qdmx['rcj'][i][12]))
                            QdxrcjhlMx.set('Zyclbz', str(qdmx['rcj'][i][13]))
                            QdxrcjhlMx.set('Zcbz', str(qdmx['rcj'][i][14]))
                            QdxrcjhlMx.set('Sbbz', str(qdmx['rcj'][i][15]))
            
            qdbt_addition = Dwgc['qdbt_addition']
            for bt in qdbt_addition:
                QdBt = ET.SubElement(Qdxm, 'QdBt')
                QdBt.set('Xh', bt['Xh'])
                QdBt.set('Mc', bt['Mc'])
                QdBt.set('Je', roundstr(bt['Je']))
                QdBt.set('Zgj', roundstr(bt['Zgj']))
                QdBt.set('Bz', bt['Bz'])
                collection = db["qdxm"]
                before_sort = []
                async for post in collection.find({"biao_id": id, "Dwgcbh": entry['Dwgcbh'], 'bt': bt['Mc']}):
                    before_sort.append(post)
                after_sort = sorted(before_sort, key=lambda x: int(x['序号']))
                for qdmx in after_sort:
                    Qdmx = ET.SubElement(QdBt, 'Qdmx')
                    Qdmx.set('Xh', str(qdmx['序号']))
                    Qdmx.set('Qdbm', str(qdmx['清单编码']))
                    Qdmx.set('Mc', str(qdmx['名称']))
                    Qdmx.set('Xmtz', str(qdmx['项目特征']))
                    Qdmx.set('Jsgz', str(qdmx['计算规则']))
                    Qdmx.set('Dw', str(qdmx['单位']))
                    Qdmx.set('Sl', roundstr4(qdmx['数量']))
                    Qdmx.set('Zhdj', roundstr4(qdmx['综合单价']))
                    Qdmx.set('Zhhj', roundstr4(qdmx['合价']))
                    Qdmx.set('Rgf', roundstr4(qdmx['人工费']))
                    Qdmx.set('Zcf', roundstr4(qdmx['主材费']))
                    Qdmx.set('Sbf', roundstr4(qdmx['设备费']))
                    Qdmx.set('Fcf', roundstr4(qdmx['辅材费']))
                    Qdmx.set('Clf', roundstr4(qdmx['材料费']))
                    Qdmx.set('Jxf', roundstr4(qdmx['机械费']))
                    Qdmx.set('Glf', roundstr4(qdmx['管理费']))
                    Qdmx.set('Lr', roundstr4(qdmx['利润']))
                    Qdmx.set('Zgj', roundstr4(qdmx['暂估价']))
                    Qdmx.set('Zgr', roundstr4(qdmx['综合人工工日']))
                    Qdmx.set('Bz', str(qdmx['备注']))
                    Qdxdezj = ET.SubElement(Qdmx, 'Qdxdezj')
                    if '__children' in qdmx:
                        for child in qdmx['__children']:
                            QdxdezjMx = ET.SubElement(Qdxdezj, 'QdxdezjMx')
                            QdxdezjMx.set('Debm', str(child['清单编码']))
                            QdxdezjMx.set('Mc', str(child['名称']))
                            QdxdezjMx.set('Dw', str(child['单位']))
                            QdxdezjMx.set('DwQdSl', roundstr4(child['数量']))
                            QdxdezjMx.set('Dj', roundstr4(child['综合单价']))
                            QdxdezjMx.set('Hj', roundstr4(child['合价']))
                            QdxdezjMx.set('Rgf', roundstr4(child['人工费']))
                            QdxdezjMx.set('Zcf', roundstr4(child['主材费']))
                            QdxdezjMx.set('Sbf', roundstr4(child['设备费']))
                            QdxdezjMx.set('Fcf', roundstr4(child['辅材费']))
                            QdxdezjMx.set('Clf', roundstr4(child['材料费']))
                            QdxdezjMx.set('Jxf', roundstr4(child['机械费']))
                            QdxdezjMx.set('Glf', roundstr4(child['管理费']))
                            QdxdezjMx.set('Lr', roundstr4(child['利润']))
                            Qdxdercjhl = ET.SubElement(QdxdezjMx, 'Qdxdercjhl')
                            for i in range(len(child['dercj'])):
                                if i == 0:
                                    pass
                                else:
                                    QdxdercjhlMx = ET.SubElement(Qdxdercjhl, 'QdxdercjhlMx')
                                    QdxdercjhlMx.set('RcjId', util.indexrcj(rcjhz, child['dercj'][i]))
                                    QdxdercjhlMx.set('Rcjhl', roundstr4(child['dercj'][i][10]))
                                    QdxdercjhlMx.set('Rcjhj', roundstr4(child['dercj'][i][11]))
                    Qdxrcjhl = ET.SubElement(Qdmx, 'Qdxrcjhl')
                    for i in range(len(qdmx['rcj'])):
                        if i == 0:
                            pass
                        else:
                            QdxrcjhlMx = ET.SubElement(Qdxrcjhl, 'QdxrcjhlMx')
                            QdxrcjhlMx.set('RcjId', util.indexrcj(rcjhz, qdmx['rcj'][i]))
                                       ##str(djcs['rcj'][i][0]))
                            QdxrcjhlMx.set('Rcjhl', roundstr4(qdmx['rcj'][i][10]))
                            QdxrcjhlMx.set('Rcjhj', roundstr4(qdmx['rcj'][i][11]))
                            QdxrcjhlMx.set('Zgjbz', str(qdmx['rcj'][i][12]))
                            QdxrcjhlMx.set('Zyclbz', str(qdmx['rcj'][i][13]))
                            QdxrcjhlMx.set('Zcbz', str(qdmx['rcj'][i][14]))
                            QdxrcjhlMx.set('Sbbz', str(qdmx['rcj'][i][15]))

            ########################################Csxm##########################
            Csxm = ET.SubElement(Dwgcxx, 'Csxm')
            ZjCs = ET.SubElement(Csxm, 'ZjCs')
            for zjcs_ in zjcs:
                ZjCsMx = ET.SubElement(ZjCs, 'ZjCsMx')
                ZjCsMx.set('Xh', str(zjcs_['序号']))
                ZjCsMx.set('Bm', str(zjcs_['清单编码']))
                ZjCsMx.set('Mc', str(zjcs_['名称']))
                ZjCsMx.set('Js', roundstr(zjcs_['计算基数']))
                ZjCsMx.set('Jsjc', str(zjcs_['计算基础']))
                ZjCsMx.set('Fl', str(zjcs_['费率']))
                ZjCsMx.set('Je', roundstr(zjcs_['金额']))
                ZjCsMx.set('Xmlb', str(zjcs_['项目类别']))
                if '__children' in zjcs_:
                    AqwmfMx = ET.SubElement(ZjCsMx, 'AqwmfMx')
                    AqwmfMx.set('Mc', str(zjcs_['__children'][0]['名称']))
                    AqwmfMx.set('Js', roundstr(zjcs_['__children'][0]['计算基数']))
                    AqwmfMx.set('Jsjc', str(zjcs_['__children'][0]['计算基础']))
                    AqwmfMx.set('Fl', str(zjcs_['__children'][0]['费率']))
                    AqwmfMx.set('Je', roundstr(zjcs_['__children'][0]['金额']))
                    AqwmfMx.set('Xmlb', str(zjcs_['__children'][0]['项目类别']))
                    AqwmfMx = ET.SubElement(ZjCsMx, 'AqwmfMx')
                    AqwmfMx.set('Mc', str(zjcs_['__children'][1]['名称']))
                    AqwmfMx.set('Js', roundstr(zjcs_['__children'][1]['计算基数']))
                    AqwmfMx.set('Jsjc', str(zjcs_['__children'][1]['计算基础']))
                    AqwmfMx.set('Fl', str(zjcs_['__children'][1]['费率']))
                    AqwmfMx.set('Je', roundstr(zjcs_['__children'][1]['金额']))
                    AqwmfMx.set('Xmlb', str(zjcs_['__children'][1]['项目类别']))
                    AqwmfMx = ET.SubElement(ZjCsMx, 'AqwmfMx')
                    AqwmfMx.set('Mc', str(zjcs_['__children'][2]['名称']))
                    AqwmfMx.set('Js', roundstr(zjcs_['__children'][2]['计算基数']))
                    AqwmfMx.set('Jsjc', str(zjcs_['__children'][2]['计算基础']))
                    AqwmfMx.set('Fl', str(zjcs_['__children'][2]['费率']))
                    AqwmfMx.set('Je', roundstr(zjcs_['__children'][2]['金额']))
                    AqwmfMx.set('Xmlb', str(zjcs_['__children'][2]['项目类别']))
            DjCs = ET.SubElement(Csxm, 'DjCs')
            collection = db["Djcs"]
            before_sort = []
            async for post in collection.find({"biao_id": id, "Dwgcbh": entry['Dwgcbh']}):
                before_sort.append(post)
            after_sort = sorted(before_sort, key=lambda x: int(x['序号']))
            for djcs in after_sort:
                DjCsMx = ET.SubElement(DjCs, 'DjCsMx')
                DjCsMx.set('Xh', str(djcs['序号']))
                DjCsMx.set('Bm', str(djcs['清单编码']))
                DjCsMx.set('Mc', str(djcs['名称']))
                DjCsMx.set('Xmtz', str(djcs['项目特征']))
                DjCsMx.set('Jsgz', str(djcs['计算规则']))
                DjCsMx.set('Dw', str(djcs['单位']))
                DjCsMx.set('Sl', roundstr4(djcs['数量']))
                DjCsMx.set('Zhdj', roundstr4(djcs['综合单价']))
                DjCsMx.set('Zhhj', roundstr4(djcs['合价']))
                DjCsMx.set('Rgf', roundstr4(djcs['人工费']))
                DjCsMx.set('Zcf', roundstr4(djcs['主材费']))
                DjCsMx.set('Sbf', roundstr4(djcs['设备费']))
                DjCsMx.set('Fcf', roundstr4(djcs['辅材费']))
                DjCsMx.set('Clf', roundstr4(djcs['材料费']))
                DjCsMx.set('Jxf', roundstr4(djcs['机械费']))
                DjCsMx.set('Glf', roundstr4(djcs['管理费']))
                DjCsMx.set('Lr', roundstr4(djcs['利润']))
                DjCsMx.set('Zgj', roundstr4(djcs['暂估价']))
                DjCsMx.set('Zgr', roundstr4(djcs['综合人工工日']))
                Csxdezj = ET.SubElement(DjCsMx, 'Csxdezj')
                if '__children' in djcs:
                    for child in djcs['__children']:
                        CsxdezjMx = ET.SubElement(Csxdezj, 'CsxdezjMx')
                        CsxdezjMx.set('Debm', str(child['清单编码']))
                        CsxdezjMx.set('Mc', str(child['名称']))
                        CsxdezjMx.set('Dw', str(child['单位']))
                        CsxdezjMx.set('DwQdSl', roundstr4(child['数量']))
                        CsxdezjMx.set('Dj', roundstr4(child['综合单价']))
                        CsxdezjMx.set('Hj', roundstr4(child['合价']))
                        CsxdezjMx.set('Rgf', roundstr4(child['人工费']))
                        CsxdezjMx.set('Zcf', roundstr4(child['主材费']))
                        CsxdezjMx.set('Sbf', roundstr4(child['设备费']))
                        CsxdezjMx.set('Fcf', roundstr4(child['辅材费']))
                        CsxdezjMx.set('Clf', roundstr4(child['材料费']))
                        CsxdezjMx.set('Jxf', roundstr4(child['机械费']))
                        CsxdezjMx.set('Glf', roundstr4(child['管理费']))
                        CsxdezjMx.set('Lr', roundstr4(child['利润']))
                        Csxdercjhl = ET.SubElement(CsxdezjMx, 'Csxdercjhl')
                        for i in range(len(child['dercj'])):
                            if i == 0:
                                pass
                            else:
                                CsxdercjhlMx = ET.SubElement(Csxdercjhl, 'CsxdercjhlMx')
                                CsxdercjhlMx.set('RcjId', util.indexrcj(rcjhz, child['dercj'][i]))
                                CsxdercjhlMx.set('Rcjhl', roundstr4(child['dercj'][i][10]))
                                CsxdercjhlMx.set('Rcjhj', roundstr4(child['dercj'][i][11]))
                    Csxrcjhl = ET.SubElement(DjCsMx, 'Csxrcjhl')
                    for i in range(len(djcs['rcj'])):
                        if i == 0:
                            pass
                        else:
                            CsxrcjhlMx = ET.SubElement(Csxrcjhl, 'CsxrcjhlMx')
                            CsxrcjhlMx.set('RcjId', util.indexrcj(rcjhz, djcs['rcj'][i]))
                                           ##str(djcs['rcj'][i][0]))
                            CsxrcjhlMx.set('Rcjhl', roundstr4(djcs['rcj'][i][10]))
                            CsxrcjhlMx.set('Rcjhj', roundstr4(djcs['rcj'][i][11]))
                            CsxrcjhlMx.set('Zgjbz', str(djcs['rcj'][i][12]))
                            CsxrcjhlMx.set('Zyclbz', str(djcs['rcj'][i][13]))
                            CsxrcjhlMx.set('Zcbz', str(djcs['rcj'][i][14]))
                            CsxrcjhlMx.set('Sbbz', str(djcs['rcj'][i][15]))

            ########################################Qtxm##########################
            Qtxm = ET.SubElement(Dwgcxx, 'Qtxm')
            for qtxm_ in qtxm:
                QtxmMx = ET.SubElement(Qtxm, 'QtxmMx')
                QtxmMx.set('Xh', str(qtxm_[0]))
                QtxmMx.set('Mc', str(qtxm_[1]))
                QtxmMx.set('Je', roundstr(qtxm_[2]))
                QtxmMx.set('Xmlb', str(qtxm_[3]))
                QtxmMx.set('Bz', str(qtxm_[4]))
            Zlje = ET.SubElement(Dwgcxx, 'Zlje')
            for i in range(len(zlje)):
                ZljeMx = ET.SubElement(Zlje, 'ZljeMx')
                ZljeMx.set('Xh', str(zlje[i]['序号']))
                ZljeMx.set('Mc', str(zlje[i]['名称']))
                ZljeMx.set('Dw', str(zlje[i]['单位']))
                ZljeMx.set('Zdje', roundstr(zlje[i]['暂定金额']))
                ZljeMx.set('Bz', str(zlje[i]['备注']))
            Clzg = ET.SubElement(Dwgcxx, 'Clzg')
            Zygczg = ET.SubElement(Dwgcxx, 'Zygczg')
            for zygczgj_ in zygczgj:
                ZygczgMx = ET.SubElement(Zygczg, 'ZygczgMx')
                ZygczgMx.set('Xh', str(zygczgj_['序号']))
                ZygczgMx.set('Mc', str(zygczgj_['名称']))
                ZygczgMx.set('Gcnr', str(zygczgj_['工程内容']))
                ZygczgMx.set('Je', roundstr(zygczgj_['金额']))
                ZygczgMx.set('Bz', str(zygczgj_['备注']))
            Jrg = ET.SubElement(Dwgcxx, 'Jrg')
            for jrg_ in jrg:
                JrgBt = ET.SubElement(Jrg, 'JrgBt')
                JrgBt.set('Xh', str(jrg_['序号']))
                JrgBt.set('Mc', str(jrg_['名称']))
                JrgBt.set('Je', roundstr(jrg_['金额']))
                JrgBt.set('Lb', str(jrg_['类别']))
            Zcbfwf = ET.SubElement(Dwgcxx, 'Zcbfwf')
            for zcbfwf_ in zcbfwf:
                ZcbfwfMx = ET.SubElement(Zcbfwf, 'ZcbfwfMx')
                ZcbfwfMx.set('Xh', str(zcbfwf_['序号']))
                ZcbfwfMx.set('Mc', str(zcbfwf_['名称']))
                ZcbfwfMx.set('Xmjz', roundstr(zcbfwf_['项目价值']))
                ZcbfwfMx.set('Fwnr', str(zcbfwf_['服务内容']))
                ZcbfwfMx.set('Jsjc', str(zcbfwf_['计算基础']))
                ZcbfwfMx.set('Fl', str(zcbfwf_['费率']))
                ZcbfwfMx.set('Je', roundstr(zcbfwf_['金额']))
            Zjxmjdkzffj = ET.SubElement(Dwgcxx, 'Zjxmjdkzffj')
            Fbrgycl = ET.SubElement(Dwgcxx, 'Fbrgycl')
            for fbrgycl_ in fbrgycl:
                FbrgyclMx = ET.SubElement(Fbrgycl, 'FbrgyclMx')
                FbrgyclMx.set('Xh', str(fbrgycl_['序号']))
                FbrgyclMx.set('RcjId', util.indexrcj(rcjhz, [fbrgycl_['序号'], fbrgycl_['材料编号'], fbrgycl_['名称'], fbrgycl_['规格型号'], fbrgycl_['单位']]))
                FbrgyclMx.set('Clbh', str(fbrgycl_['材料编号']))
                FbrgyclMx.set('Mc', str(fbrgycl_['名称']))
                FbrgyclMx.set('Ggxh', str(fbrgycl_['规格型号']))
                FbrgyclMx.set('Dw', str(fbrgycl_['单位']))
                FbrgyclMx.set('Sl', roundstr4(fbrgycl_['数量']))
                FbrgyclMx.set('Dj', roundstr4(fbrgycl_['单价']))
                FbrgyclMx.set('Hj', roundstr4(fbrgycl_['合价']))
                FbrgyclMx.set('Jhfs', str(fbrgycl_['交货方式']))
                FbrgyclMx.set('Sddd', str(fbrgycl_['送达地点']))
                FbrgyclMx.set('Bz', str(fbrgycl_['备注']))
            Cbrgycl = ET.SubElement(Dwgcxx, 'Cbrgycl')
            Rcjhz = ET.SubElement(Dwgcxx, 'Rcjhz')
            for i in range(len(rcjhz)):
                if i == 0:
                    pass 
                else:
                    RcjhzMx = ET.SubElement(Rcjhz, 'RcjhzMx')
                    RcjhzMx.set('RcjId', str(i))
                    RcjhzMx.set('RcjBm', str(rcjhz[i][1]))
                    RcjhzMx.set('Mc', str(rcjhz[i][2]))
                    RcjhzMx.set('Ggxh', str(rcjhz[i][3]))
                    RcjhzMx.set('Dw', str(rcjhz[i][4]))
                    RcjhzMx.set('Sl', roundstr4(rcjhz[i][5]))
                    RcjhzMx.set('Dj', roundstr4(rcjhz[i][6]))
                    RcjhzMx.set('Hj', roundstr4(rcjhz[i][7]))
                    RcjhzMx.set('Cd', str(rcjhz[i][8]))
                    RcjhzMx.set('Gycs', str(rcjhz[i][9]))
                    RcjhzMx.set('Rcjlb', str(rcjhz[i][10]))
                    RcjhzMx.set('Zyclbz', str(rcjhz[i][11]))
                    RcjhzMx.set('Zcbz', str(rcjhz[i][12]))
                    RcjhzMx.set('Sbbz', str(rcjhz[i][13]))
                    RcjhzMx.set('Jgbz', str(rcjhz[i][14]))
                    RcjhzMx.set('Zgjbz', str(rcjhz[i][15]))


############################end of build xml file#################################

async def delete_files(client, id, user):
    db = client["baojia"]
    collection = db["jingjibiao"]
    result = []
    await collection.update_one({'_id': ObjectId(id), 'user': user}, {'$set': {'visible': False}})
    return result



async def list_files(client, user):
    db = client["baojia"]
    collection = db["jingjibiao"]
    result = []
    async for post in collection.find({"visible": True, 'user': user}):
        result.append([str(post['_id']), post["Xmmc"], post['created'] if 'created' in post else ""])
    return result

def getDwgc(id, Zylb):
    result = []
    id = id + "Zylb" + Zylb
    
    
    result.append({"id": id + "bao jia hui zong", "label": "报价汇总表"})
    
    result.append({"id": id + "gui fei shui jin", "label": "规费税金"})
    
    result.append({"id": id + "qing dan xiang mu", "label": "清单项目"})
    
    result.append({"id": id + "cuo shi xiang mu", "label": "措施项目"})
    
    result.append({"id": id +"qi ta xiang mu", "label": "其他项目"})
    
    result.append({"id": id +"zan lie jin e", "label": "暂列金额"})
    
    result.append({"id": id +"cai liao zan gu jia", "label": "材料暂估价"})
    
    result.append({"id": id + "zhuan ye gong cheng zan gu jia", "label": "专业工程暂估价"})
    
    result.append({"id": id + "ji ri gong", "label": "计日工"})
    
    result.append({"id": id + "zong cheng bao fu wu fei", "label": "总承包服务费"})
    
    result.append({"id": id + "zong jia xiang mu jin du kuan zhi fu fen jie", "label": "总价项目进度款支付分解"})
    
    result.append({"id": id + "fa bao ren gong ying cai liao", "label": "发包人供应材料"})
    
    result.append({"id": id + "cheng bao ren gong ying cai liao", "label": "承包人供应材料"})
    
    result.append({"id": id + "ren cai ji hui zong", "label": "人材机汇总"})
    
    return result


async def getOutline(client, id, user):


    db = client["baojia"]
    collection = db["jingjibiao"]
    document = await collection.find_one({'_id': ObjectId(id)})
    if document['user'] != user:
        raise HTTPException(
           status_code=status.HTTP_401_UNAUTHORIZED,
           detail="Invalid credentials",
           headers={"WWW-Authenticate": "Basic"},
        )
    result = []
    if 'TouBiaoXx' in document:
        result.append({"id" : "TouBiaoXx", "label" : "投标信息" })
    if 'Dxgcxx' in document:
        Dxgcxx = document["Dxgcxx"]
        for dxgc in Dxgcxx:
            result2 = []
            Dwgc = dxgc["Dwgc"]
            for grandchild in Dwgc:
                result2.append({"id" : grandchild['Dwgcbh'], "Zylb":grandchild['Zylb'],  "label": grandchild["Dwgcmc"], "children" : getDwgc(grandchild['Dwgcbh'], grandchild['Zylb'])})
            result.append({"id" : dxgc['Dxgcbh'], "label" : dxgc['Dxgcmc'], "children" : result2})
    return result
        
async def getDetail(client, id, user):


    db = client["baojia"]
    collection = db["jingjibiao"]
    document = await collection.find_one({'_id': ObjectId(id)})
    if document['user'] != user:
        raise HTTPException(
           status_code=status.HTTP_401_UNAUTHORIZED,
           detail="Invalid credentials",
           headers={"WWW-Authenticate": "Basic"},
        )
    result = []
    ##result.append(["名称", "金额", "暂估价", "安全文明施工费", "规费"])
    if 'TouBiaoXx' in document:
        toubiaoxx = document["TouBiaoXx"]
        result.append({'名称': '投标总价', '金额' : toubiaoxx["Tbzj"], '暂估价': toubiaoxx["Zgj"], '安全文明施工费': toubiaoxx["Aqwmf"], '规费' : toubiaoxx["Gf"], '_children': []})
        
        if 'Dxgcxx' in document:
            Dxgcxx = document["Dxgcxx"]
            for dxgc in Dxgcxx:
            
                result[0]['_children'].append({'名称' :dxgc["Dxgcmc"], '金额' : dxgc["Je"], '暂估价' : dxgc["Zgj"],'安全文明施工费' : dxgc["Aqwmf"], '规费' : dxgc["Gf"]})
        return result
    else:
        if 'Dxgcxx' in document:
            Dxgcxx = document["Dxgcxx"]
            for dxgc in Dxgcxx:
            
                result.append({'名称' :dxgc["Dxgcmc"], '金额' : dxgc["Je"], '暂估价' : dxgc["Zgj"],'安全文明施工费' : dxgc["Aqwmf"], '规费' : dxgc["Gf"]})
        return result


async def getBjhz(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})


    return document['bjhz']




async def getGfsj(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})


    return document['gfsj']


async def getQtxm(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})


    return document['qtxm']

async def getZygczgj(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})


    return document['zygczgj']

async def getZlje(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})


    return document['zlje']

async def getJrg(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})

    return document['jrg']

async def getZcbfwf(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({"Dwgcbh": id, "biao_id": objectid})


    return document['zcbfwf']
    

async def getFbrgycl(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})


    return document['fbrgycl']

async def getRcjhz(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})

    return document['rcjhz']

async def getZjcs(client, objectid, id):
    db = client["baojia"]


    collection = db["Dwgc"]
    document = await collection.find_one({ "Dwgcbh": id, "biao_id": objectid})

    return document['zjcs']

async def getDjcs(client, objectid, id):
    db = client["baojia"]


    collection = db["Djcs"]
    result = []
    async for post in collection.find({"Dwgcbh": id, "biao_id": objectid}):
        del post["_id"]
        result.append(post)
    return result

async def getDjcsQingdanrcj(client, name, bh, bt, bm):
    db = client["baojia"]
   

    collection = db["Djcs"]
    document = await collection.find_one({ "Dwgcbh": bh, "清单编码": bm, "biao_id": name})
    result = document["rcj"]
    return result


async def getDjcsDingercj(client, name, bh, bt, qdbm, debm):
    db = client["baojia"]
    

    collection = db["Djcs"]
    document = await collection.find_one({"Dwgcbh": bh, "清单编码": qdbm, "biao_id": name})
    children = document["__children"]
    result = []
    for child in children:
        if child["清单编码"] == debm:
            result = child["dercj"]
    return result


async def getQdxm(client, name, bh):
    db = client["baojia"]
    collection = db["Dwgc"]
    document = await collection.find_one({'biao_id': name, "Dwgcbh": bh})
    if document:
        return  document['qdbt']
    else:
        return []
    
async def getQdmx(client, name, bh, bt):
    db = client["baojia"]
    collection = db["qdxm"]
    result = []
    if bt == 'ALL':
        async for post in collection.find({'biao_id': name, "Dwgcbh": bh,}):
            del post["_id"]
            result.append(post)
    else:
        async for post in collection.find({'biao_id': name, "Dwgcbh": bh, "bt": bt}):
            del post["_id"]
            result.append(post)
    return result



async def getQingdanrcj(client, name, bh, bt, bm):
    db = client["baojia"]
    collection = db["qdxm"]


    document = await collection.find_one({ "Dwgcbh": bh, "清单编码": bm, "biao_id": name, "bt": bt})
    result = document["rcj"]
    return result


async def getDingercj(client, name, bh, bt, qdbm, debm):
    db = client["baojia"]
    

    collection = db["qdxm"]
    document = await collection.find_one({"Dwgcbh": bh, "清单编码": qdbm, "biao_id": name, "bt": bt})
    result = []
    if "__children" in document:
        children = document["__children"]
        
        for child in children:
            if child["清单编码"] == debm:
                result = child["dercj"]
    return result



async def getQufei(client, id, user):
    db = client["baojia"]
    collection = db["qufei"]
    document = await collection.find_one({'biao_id': id})
    if document['user'] != user:
        raise HTTPException(
           status_code=status.HTTP_401_UNAUTHORIZED,
           detail="Invalid credentials",
           headers={"WWW-Authenticate": "Basic"},
        )
    return document['qufei']

async def searchde(client, zhuanye, text):
    db = client["dinge"]
    collection = db["de-collection"]
    result = []
    async for post in  collection.find({'$text': {"$search": "\"" + text + "\""}, "zhuanye": zhuanye}):
        result.append(post["DEBH"] + " " + post['GCLMC'] )
        if len(result) > 10:
            break
    return result

async def getJiagongcai(client, id):
    db = client["baojia"]
    collection = db["Dwgc"]
    result = []
    async for post in collection.find({'biao_id': id}, {'rcjhz': 0}):
       for entry in post['fbrgycl']:
           result.append(entry)
    return result


async def getSingleDeXilie_(model, client, zhuanye, debh, mc):
    db = client["dinge"]
    collection = db["de-collection"]
    count = 0
    actual_zhuanye = []
    mcs = []
    parents = []
    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]
    for post in collection.find({'DEBH': debh}):
        ##print(post)
        actual_zhuanye.append(post['zhuanye'])
        mcs.append(post['GCLMC'])
        parents.append(post['parent'])
        count = count + 1
    if count == 0:
        return  None, None, None, None, None, None, None, zhuanye
    elif count == 1:
        if actual_zhuanye[0] == '土建':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_tj(debh)
            return A1, A2, A3, A4, A5, A6, A7, 10
        elif actual_zhuanye[0] == '市政':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_sz(debh)
            return A1, A2, A3, A4, A5, A6, A7, 20
        elif actual_zhuanye[0] == '安装':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_az(debh)
            return A1, A2, A3, A4, A5, A6, A7, 30
        elif actual_zhuanye[0] == '园林':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_yl(debh)
            return A1, A2, A3, A4, A5, A6, A7,  40  
        elif actual_zhuanye[0] == '修缮工程土建':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xstj(debh)
            return A1, A2, A3, A4, A5, A6, A7, 50   
        elif actual_zhuanye[0] == '修缮工程安装':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xsaz(debh)
            return A1, A2, A3, A4, A5, A6, A7, 60   
    else:
        hit = False
        for i in range(0, count):
            if mcs[i] in mc:
                hit = True
                actual_zhuanye[0] = actual_zhuanye[i]
        if hit:
            if actual_zhuanye[0] == '土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_tj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 10
            elif actual_zhuanye[0] == '市政':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_sz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 20
            elif actual_zhuanye[0] == '安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_az(debh)
                return A1, A2, A3, A4, A5, A6, A7, 30
            elif actual_zhuanye[0] == '园林':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_yl(debh)
                return A1, A2, A3, A4, A5, A6, A7,  40  
            elif actual_zhuanye[0] == '修缮工程土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xstj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 50   
            elif actual_zhuanye[0] == '修缮工程安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xsaz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 60   
        else:
            for i in range(len(parents)):
                mcs[i] = parents[i] + ' ' + mcs[i]
            mcs.append(mc)
            #print(mcs)
            embeddings = model.encode(mcs)
            similarities = model.similarity( embeddings[-1], embeddings)
            ##array([1.0000002, 0.7662151, 1.0000002], dtype=float32)
            ##print(similarities)
            sim = similarities[0].numpy()[:-1]
            ##print(sim)
            index = np.argmax(sim).item()
            ##print(index)
            ##print(actual_zhuanye)

            actual_zhuanye[0] = actual_zhuanye[index]
            if actual_zhuanye[0] == '土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_tj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 10
            elif actual_zhuanye[0] == '市政':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_sz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 20
            elif actual_zhuanye[0] == '安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_az(debh)
                return A1, A2, A3, A4, A5, A6, A7, 30
            elif actual_zhuanye[0] == '园林':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_yl(debh)
                return A1, A2, A3, A4, A5, A6, A7,  40  
            elif actual_zhuanye[0] == '修缮工程土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xstj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 50   
            elif actual_zhuanye[0] == '修缮工程安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xsaz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 60   

async def getSingleDeXilie(model, client, zhuanye, debh, mc):
    db = client["dinge"]
    collection = db["de-collection"]
    count = 0
    actual_zhuanye = []
    mcs = []
    parents = []
    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]
    async for post in collection.find({'DEBH': debh}):
        ##print(post)
        actual_zhuanye.append(post['zhuanye'])
        mcs.append(post['GCLMC'])
        parents.append(post['parent'])
        count = count + 1
    if count == 0:
        return  None, None, None, None, None, None, None, zhuanye
    elif count == 1:
        if actual_zhuanye[0] == '土建':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_tj(debh)
            return A1, A2, A3, A4, A5, A6, A7, 10
        elif actual_zhuanye[0] == '市政':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_sz(debh)
            return A1, A2, A3, A4, A5, A6, A7, 20
        elif actual_zhuanye[0] == '安装':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_az(debh)
            return A1, A2, A3, A4, A5, A6, A7, 30
        elif actual_zhuanye[0] == '园林':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_yl(debh)
            return A1, A2, A3, A4, A5, A6, A7,  40  
        elif actual_zhuanye[0] == '修缮工程土建':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xstj(debh)
            return A1, A2, A3, A4, A5, A6, A7, 50   
        elif actual_zhuanye[0] == '修缮工程安装':
            A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xsaz(debh)
            return A1, A2, A3, A4, A5, A6, A7, 60   
    else:
        hit = False
        for i in range(0, count):
            if mcs[i] in mc:
                hit = True
                actual_zhuanye[0] = actual_zhuanye[i]
        if hit:
            if actual_zhuanye[0] == '土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_tj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 10
            elif actual_zhuanye[0] == '市政':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_sz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 20
            elif actual_zhuanye[0] == '安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_az(debh)
                return A1, A2, A3, A4, A5, A6, A7, 30
            elif actual_zhuanye[0] == '园林':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_yl(debh)
                return A1, A2, A3, A4, A5, A6, A7,  40  
            elif actual_zhuanye[0] == '修缮工程土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xstj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 50   
            elif actual_zhuanye[0] == '修缮工程安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xsaz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 60   
        else:
            for i in range(len(parents)):
                mcs[i] = parents[i] + " " + mcs[i]
            mcs.append(mc)
            embeddings = model.encode(mcs)
            similarities = model.similarity( embeddings[-1], embeddings)
            ##array([1.0000002, 0.7662151, 1.0000002], dtype=float32)
            ##print(similarities)
            sim = similarities[0].numpy()[:-1]
            ##print(sim)
            index = np.argmax(sim).item()
            ##print(index)
            ##print(actual_zhuanye)

            actual_zhuanye[0] = actual_zhuanye[index]
            if actual_zhuanye[0] == '土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_tj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 10
            elif actual_zhuanye[0] == '市政':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_sz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 20
            elif actual_zhuanye[0] == '安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_az(debh)
                return A1, A2, A3, A4, A5, A6, A7, 30
            elif actual_zhuanye[0] == '园林':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_yl(debh)
                return A1, A2, A3, A4, A5, A6, A7,  40  
            elif actual_zhuanye[0] == '修缮工程土建':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xstj(debh)
                return A1, A2, A3, A4, A5, A6, A7, 50   
            elif actual_zhuanye[0] == '修缮工程安装':
                A1, A2, A3, A4, A5, A6, A7 = service.getSingleDeXilie_xsaz(debh)
                return A1, A2, A3, A4, A5, A6, A7, 60   

















   

##################################editor_code##########################
def bjhz_template():
    result = []
    result.append({'序号': '1', '名称': '分部分项工程', '金额': '0', '暂估价': '0', '类别' : '1', '__children': [
        {'序号': '1.1', '名称': '人工费', '金额': '0', '暂估价': '0', '类别' : '1.1'},
        {'序号': '1.2', '名称': '材料费', '金额': '0', '暂估价': '0', '类别' : '1.2'},
        {'序号': '1.3', '名称': '施工机具使用费', '金额': '0', '暂估价': '0', '类别' : '1.3'},
        {'序号': '1.4', '名称': '企业管理费', '金额': '0', '暂估价': '0', '类别' : '1.4'},
        {'序号': '1.5', '名称': '利润', '金额': '0', '暂估价': '0', '类别' : '1.5'}
    ]})
    result.append({'序号': '2', '名称': '措施项目', '金额': '0', '暂估价': '0', '类别' : '2', '__children': [
        {'序号': '2.1', '名称': '单价措施项目费', '金额': '0', '暂估价': '0', '类别' : '2.1'},
        {'序号': '2.2', '名称': '总价措施项目费', '金额': '0', '暂估价': '0', '类别' : '2.2', '__children' : [
        {'序号': '2.2.1', '名称': '其中：安全文明施工措施费', '金额': '0', '暂估价': '0', '类别' : '2.2.1'},]
        }
       
    ]})
    result.append({'序号': '3', '名称': '其他项目', '金额': '0', '暂估价': '0', '类别' : '3', '__children': [
        {'序号': '3.1', '名称': '其中：暂列金额', '金额': '0', '暂估价': '0', '类别' : '3.1'},
        {'序号': '3.2', '名称': '其中：专业工程暂估价', '金额': '0', '暂估价': '0', '类别' : '3.2'},
        {'序号': '3.3', '名称': '其中：计日工', '金额': '0', '暂估价': '0', '类别' : '3.3'},
        {'序号': '3.4', '名称': '其中：总承包服务费', '金额': '0', '暂估价': '0', '类别' : '3.4'},
        
    ]})
    result.append({'序号': '4', '名称': '规费', '金额': '0', '暂估价': '0', '类别' : '4', })
    result.append({'序号': '5', '名称': '税金', '金额': '0', '暂估价': '0', '类别' : '5', })
    result.append({'序号': '6', '名称': '工程造价', '金额': '0', '暂估价': '0', '类别' : '6', })

    return result


async def zongjiaDwgc(client, biao_id, bh):
    db = client["baojia"]
    djcs_sum = 0###单价措施汇总
    collection = db["Djcs"]
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        djcs_sum = djcs_sum + float(post['合价'])
    
    collection = db['Dwgc']
    document = await collection.find_one({'biao_id': biao_id, "Dwgcbh": bh}, {'rcjhz': 0})
    qdbt = document['qdbt']
    qdbt_addition = document['qdbt_addition']
    bts = {}
    distinguish = True
    
    for item in qdbt_addition:
        bts[item['Mc']] = 0
    
    collection = db["qdxm"]
    qd_sum = 0###清单汇总
    rgf_sum = 0
    clf_sum = 0
    jxf_sum = 0
    glf_sum = 0
    lr_sum = 0
    fbrgycl_sum = 0
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        qd_sum = qd_sum + float(post['合价'])
        rgf_sum = rgf_sum + float(post['数量']) * float(post['人工费'])
        clf_sum = clf_sum + float(post['数量']) * float(post['材料费'])
        jxf_sum = jxf_sum + float(post['数量']) * float(post['机械费'])
        glf_sum = glf_sum + float(post['数量']) * float(post['管理费'])
        lr_sum = lr_sum + float(post['数量']) * float(post['利润'])
        if distinguish and post['bt'] in bts:
            bts[post['bt']] = bts[post['bt']] + float(post['合价'])

    sum = qd_sum + djcs_sum##作为总价措施和后续费用计算的的计算基础
    fbrgycl = document['fbrgycl']
    for entry in fbrgycl:
        fbrgycl_sum = fbrgycl_sum + float(entry['合价'])
    zjcs = document['zjcs']
    def process(n):
        n['计算基数'] = str(sum)
        n['金额'] = float(sum) * float(n['费率']) / float(100)
        if "__children" in n:
            for entry in n['__children']:
                entry['计算基数'] = str(sum)
                entry['金额'] = float(sum) * float(entry['费率']) / float(100)
        return n

    ##目标数据，会存到数据库
    result = [process(item) for item in zjcs]
    for entry in result:
        if '安全文明施工' in entry['名称']:
            sum_ = 0
            for child in entry['__children']:
                sum_ = sum_ + float(child['金额'])
            entry['金额'] = str(sum_)
            entry['计算基数'] = str(sum_)
    zjcs_sum = 0###总价措施汇总
    for entry in result:
        zjcs_sum = zjcs_sum + float(entry['金额'])
    qtxm = document['qtxm']##dwgc qtxm
    qtxm_sum = 0###其他项目汇总， 为了计算规费税金，其他项目本身不会改变
    for entry in qtxm:
        qtxm_sum = qtxm_sum + float(entry['金额'])
    gfsj = document['gfsj']
    gf_sum = 0###规费税金汇总
    sj_sum = 0
    for item in gfsj:
        if item['名称'] == '规费':
            child = item['__children']
            for children in child:
                children['取费基数'] = str(sum + zjcs_sum + qtxm_sum)
                children['金额'] = str(float(sum + zjcs_sum + qtxm_sum) * float(children['费率']) / float(100))
                gf_sum = gf_sum + float(children['金额'])
    for item in gfsj:
        if item['名称'] == '规费':
            item['取费基数'] = str(gf_sum)
            item['金额'] = str(gf_sum)
    for item in gfsj:
        if item['名称'] == '税金':
            item['取费基数'] = str(sum + zjcs_sum + qtxm_sum + gf_sum - fbrgycl_sum / float(1.01))
            item['金额'] = float(item['取费基数']) * float(item['费率']) / float(100)
            sj_sum = float(item['金额'])
    for item in gfsj:
        if item['名称'] == '合计':
            item['取费基数'] = str(sj_sum + gf_sum)
            item['金额'] = item['取费基数']

    qtxm = document['qtxm']
    zlje = 0
    zygczgj = 0
    jrg = 0
    zcbfwf = 0
    clzgj = 0
    for entry in qtxm:
        if entry['序号'] == '1':
            zlje = entry['金额']
        if entry['序号'] == '3':
            jrg = entry['金额']
        if entry['序号'] == '4':
            zcbfwf = entry['金额']
        if entry['序号'] == '2':
            for child in entry['__children']:
                if child['序号'] == '2.2':
                    zygczgj = child['金额']
                if child['序号'] == '2.1':
                    clzgj = child['金额']
    ###重新算了一遍其他项目汇总
    qtxm_sum = float(zlje) + float(zygczgj) + float(jrg) + float(zcbfwf) + float(clzgj)
                
    bjhz = bjhz_template()
    for entry in bjhz:
        if entry['序号'] == '1':
            entry['金额'] = qd_sum
            for child in entry['__children']:
                if child['序号'] == '1.1':
                    child['金额'] = str(rgf_sum)
                if child['序号'] == '1.2':
                    child['金额'] = str(clf_sum)
                if child['序号'] == '1.3':
                    child['金额'] = str(jxf_sum)
                if child['序号'] == '1.4':
                    child['金额'] = str(glf_sum)
                if child['序号'] == '1.5':
                    child['金额'] = str(lr_sum)
        if entry['序号'] == '2':
            entry['金额'] = str(djcs_sum + zjcs_sum)
            for child in entry['__children']:
                if child['序号'] == '2.1':
                    child['金额'] = str(djcs_sum)
                if child['序号'] == '2.2':
                    child['金额'] = str(zjcs_sum)
                    for grandchild in child['__children']:
                        if grandchild['序号'] == '2.2.1':
                            grandchild['金额'] = str(sum_)
        if entry['序号'] == '3':
            entry['金额'] = str(qtxm_sum)
        if entry['序号'] == '4':
            entry['金额'] = str(gf_sum)
        if entry['序号'] == '5':
            entry['金额'] = str(sj_sum)
        if entry['序号'] == '6':
            entry['金额'] = str(float(qd_sum) + float(djcs_sum) + float(zjcs_sum) + float(qtxm_sum) + float(gf_sum) + float(sj_sum))
        

    collection = db['Dwgc']
    if distinguish:
        for entry in qdbt_addition:
            entry['Je'] = str(bts[entry['Mc']])    
    await collection.update_one({'biao_id': biao_id, "Dwgcbh": bh}, {'$set': {'zjcs': result, 'gfsj' : gfsj, 'bjhz': bjhz, 'qdbt_addition' : qdbt_addition}})
    


async def tbxx(client, biao_id):
    db = client["baojia"]
    collection = db["jingjibiao"]
    document = await collection.find_one({'_id': ObjectId(biao_id)})
    dxgcxx = document['Dxgcxx']
    tbxx = document['TouBiaoXx']
    tbxx['Tbzj'] = '0'
    tbxx['Zgj'] = '0'
    tbxx['Aqwmf'] = '0'
    tbxx['Gf'] = '0'
    
    dwdx = {}##单位工程映射单项工程
    for dxgc in dxgcxx:
        dxgc['Aqwmf'] = '0'
        dxgc['Gf'] = '0'
        dxgc['Je'] = '0'
        dxgc['Zgj'] = '0'
        mc = dxgc['Dxgcmc']
        Dwgc = dxgc['Dwgc']
        for dwgc in Dwgc:
            Dwgcbh = dwgc['Dwgcbh']
            dwdx[Dwgcbh] = dxgc
    collection = db['Dwgc']
    async for post in collection.find({'biao_id': biao_id}):
        bh = post['Dwgcbh']
        dx = dwdx[bh]
        bjhz = post['bjhz']
        
        if len(bjhz) == 0:
            pass
        else:
            qtxm = post['qtxm']
            dx['Aqwmf'] = str(float(dx['Aqwmf']) + float(bjhz[1]['__children'][1]['__children'][0]['金额']))
            dx['Gf'] = str(float(dx['Gf']) + float(bjhz[3]['金额']))
            dx['Je'] = str(float(dx['Je']) + float(bjhz[5]['金额']))
            dx['Zgj']= str(float(dx['Zgj']) + float(qtxm[1]['金额']))
    for dxgc in dxgcxx:
        tbxx['Tbzj'] = str(float(tbxx['Tbzj']) + float(dxgc['Je']))
        tbxx['Zgj'] = str(float(tbxx['Zgj']) + float(dxgc['Zgj']))
        tbxx['Aqwmf'] = str(float(tbxx['Aqwmf']) + float(dxgc['Aqwmf']))
        tbxx['Gf'] = str(float(tbxx['Gf']) + float(dxgc['Gf']))
    collection = db["jingjibiao"]


    await collection.update_one({'_id': ObjectId(biao_id)}, {'$set': {'Dxgcxx': dxgcxx, 'TouBiaoXx' : tbxx}})





async def updatezjcs(client, id, bh, mc, fl):
    db = client["baojia"]
    collection = db["Dwgc"]
    
    document =  await collection.find_one({'biao_id': id, 'Dwgcbh': bh}, {'rcjhz': 0})
    zjcs = document['zjcs']
    for entry in zjcs:
        if entry['名称'] == mc:
            entry['费率'] = fl
            entry['金额'] = str(float(entry['计算基数']) * float(fl) / float(100))
    

    await collection.update_one({'biao_id': id, "Dwgcbh": bh}, {'$set': {'zjcs': zjcs}})
    await zongjiaDwgc(client, id, bh)
    await tbxx(client, id)



    
        


#################保存清单项#############################
async def save(client, data):
    db = client["baojia"]
    collection = db["qdxm"]
    biao_id = None
    bh = None
    for entry in data:
        entry["__children"] = entry["_children"]
        del entry["_children"]
        biao_id = entry['biao_id']
        bh = entry['Dwgcbh']
        await collection.replace_one({"biao_id": entry["biao_id"], "Dwgcbh": entry["Dwgcbh"], "bt": entry["bt"], "清单编码": entry["清单编码"]}, entry)
    if not biao_id:
        return {"result" : "ok"}
    rcjhz = []
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        qdrcj = post['rcj'][1:]
        for entry in qdrcj:
            ##print(entry[4])
            if entry[4] != '%':
                entry[10] = float(entry[10]) * float(post['数量'])
        rcjhz = rcjhz + qdrcj
    collection = db["Djcs"]
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        
        qdrcj = post['rcj'][1:]
        for entry in qdrcj:
            ##print(entry[4])
            if entry[4] != '%':
                entry[10] = float(entry[10]) * float(post['数量'])
        rcjhz = rcjhz + qdrcj
    newHuizong = util.huizongrcj(rcjhz)
    collection = db["Dwgc"]
    document = await collection.find_one({'biao_id': biao_id,'Dwgcbh': bh})
    fbrgycl = document['fbrgycl']
    for entry in fbrgycl:
        id = entry['材料编号']
        for entry2 in newHuizong:
            if entry2[1] == id:
                entry['数量'] = entry2[5]
                entry['合价'] = entry2[7]

    await collection.update_one({'biao_id': biao_id, 'Dwgcbh': bh}, {'$set': {'rcjhz' : newHuizong, 'fbrgycl': fbrgycl}})
    await zongjiaDwgc(client, biao_id, bh)
    await tbxx(client, biao_id)
    return {"result": "ok"}

async def savedjcs(client, data):
    db = client["baojia"]
    collection = db["Djcs"]
    biao_id = None
    bh = None
    for entry in data:
        entry["__children"] = entry["_children"]
        del entry["_children"]
        biao_id = entry['biao_id']
        bh = entry['Dwgcbh']
        await collection.replace_one({"biao_id": entry["biao_id"], "Dwgcbh": entry["Dwgcbh"],  "清单编码": entry["清单编码"]}, entry)

    if not biao_id:
        return {"result" : "ok"}
    rcjhz = []
    collection = db["qdxm"]
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        qdrcj = post['rcj'][1:]
        for entry in qdrcj:
            if entry[4] != '%':
                entry[10] = float(entry[10]) * float(post['数量'])
        rcjhz = rcjhz + qdrcj
    collection = db["Djcs"]
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        qdrcj = post['rcj'][1:]
        for entry in qdrcj:
            if entry[4] != '%':
                entry[10] = float(entry[10]) * float(post['数量'])
        rcjhz = rcjhz + qdrcj
    newHuizong = util.huizongrcj(rcjhz)
    collection = db["Dwgc"]
    document = await collection.find_one({'biao_id': biao_id,'Dwgcbh': bh})
    fbrgycl = document['fbrgycl']
    for entry in fbrgycl:
        id = entry['材料编号']
        for entry2 in newHuizong:
            if entry2[1] == id:
                entry['数量'] = entry2[5]
                entry['合价'] = entry2[7]

    await collection.update_one({'biao_id': biao_id, 'Dwgcbh': bh}, {'$set': {'rcjhz' : newHuizong, 'fbrgycl': fbrgycl}})
    await zongjiaDwgc(client, biao_id, bh)
    await tbxx(client, biao_id)
    return {"result": "ok"}

async def applyFL(client, id, data):
    db = client["baojia"]
    collection = db["qufei"]
    
    await collection.replace_one({"biao_id": id}, {"biao_id": id, "qufei": data})
    collection = db['qdxm']
    xm = data[0]
    for dxgc in xm["children"]:
        for dwgc in dxgc["children"]:
            bh = dwgc['key']
            glf = float(dwgc['管理费（%）'])
            lr = float(dwgc['利润（%）'])
            bz = dwgc['备注']
            ##print(bh+glf+lr+bz)
            async for post in collection.find({'biao_id': id, "Dwgcbh": bh}):
                rgfs = float(post['人工费'])
                clfs = float(post['材料费'])
                jxfs = float(post['机械费'])
                sl = float(post['数量'])
                if rgfs < 0.0001 and clfs < 0.0001 and jxfs < 0.0001:
                    continue
                ##danxiang = False
                
                for child in post['__children']:##一条定额
                    bm = child['清单编码']
                    if bm.startswith("D"):
                        ##danxiang = True
                        continue
                    rgf = float(child['人工费'])
                    clf = float(child['材料费'])
                    jxf = float(child['机械费'])
                    zhdj = float(child['综合单价'])
                    sl_ = float(child['数量'])
                    if len(bz) == 0:
                        ##常规计算
                        child['管理费'] = (rgf + jxf) * glf / float(100)
                        child['利润'] = (rgf + jxf) * lr / float(100)
                        child['综合单价'] = rgf + clf + jxf + child['管理费'] + child['利润']
                        child['合价'] = sl * sl_ * child['综合单价'] 

                    else:
                        child['管理费'] = (rgf) * glf / float(100)
                        child['利润'] = (rgf) * lr / float(100)
                        child['综合单价'] = rgf + clf + jxf + child['管理费'] + child['利润']
                        child['合价'] = sl * sl_ * child['综合单价'] 
                if True:
                    post['管理费'] = 0
                    post['利润'] = 0
                    for entry in post['__children']:
                        post['管理费'] = post['管理费'] + float(entry['管理费']) * float(entry['数量'])
                        post['利润'] = post['利润'] + float(entry['利润']) * float(entry['数量'])
                    post['管理费'] =  str(post['管理费'])
                    post['利润'] = str(post['利润'])
                    post['综合单价'] = str(float(post['人工费']) + float(post['材料费']) + float(post['机械费']) + float(post['管理费']) + float(post['利润']))
                    post['合价'] = str(float(post['综合单价']) * float(post['数量']))
                    
                

                await collection.replace_one({'_id': post['_id']}, post )
    collection = db['Djcs']
    
    for dxgc in xm["children"]:
        for dwgc in dxgc["children"]:
            bh = dwgc['key']
            glf = float(dwgc['管理费（%）'])
            lr = float(dwgc['利润（%）'])
            bz = dwgc['备注']
            ##print(bh+glf+lr+bz)
            async for post in collection.find({'biao_id': id, "Dwgcbh": bh}):
                rgfs = float(post['人工费'])
                clfs = float(post['材料费'])
                jxfs = float(post['机械费'])
                sl = float(post['数量'])
                if rgfs < 0.0001 and clfs < 0.0001 and jxfs < 0.0001:
                    continue
                ##danxiang = False##一般有单项定额的，清单就一条定额，就是这个单项定额
                
                for child in post['__children']:
                    bm = child['清单编码']
                    if bm.startswith("D"):
                        ##danxiang = True
                        continue
                    rgf = float(child['人工费'])
                    clf = float(child['材料费'])
                    jxf = float(child['机械费'])
                    zhdj = float(child['综合单价'])
                    sl_ = float(child['数量'])
                    if len(bz) == 0:
                        ##常规计算
                        child['管理费'] = (rgf + jxf) * glf / float(100)
                        child['利润'] = (rgf + jxf) * lr / float(100)
                        child['综合单价'] = rgf + clf + jxf + child['管理费'] + child['利润']
                        child['合价'] = sl * sl_ * child['综合单价'] 

                    else:
                        child['管理费'] = (rgf) * glf / float(100)
                        child['利润'] = (rgf) * lr / float(100)
                        child['综合单价'] = rgf + clf + jxf + child['管理费'] + child['利润']
                        child['合价'] = sl * sl_ * child['综合单价'] 
                if True:
                    post['管理费'] = 0
                    post['利润'] = 0
                    for entry in post['__children']:
                        post['管理费'] = post['管理费'] + float(entry['管理费']) * float(entry['数量'])
                        post['利润'] = post['利润'] + float(entry['利润']) * float(entry['数量'])
                    post['管理费'] =  str(post['管理费'])
                    post['利润'] = str(post['利润'])
                    post['综合单价'] = str(float(post['人工费']) + float(post['材料费']) + float(post['机械费']) + float(post['管理费']) + float(post['利润']))
                    post['合价'] = str(float(post['综合单价']) * float(post['数量']))
                    

                await collection.replace_one({'_id': post['_id']}, post )


    for dxgc in xm["children"]:
        for dwgc in dxgc["children"]:
            bh = dwgc['key']
            await zongjiaDwgc(client, id, bh)
    await tbxx(client, id)
    return {"result": "ok"}


###人材机汇总页面调价
async def tiaojia(client, biao_id, bh, bm, mingcheng, danwei, jiage, glf, lr, bz):
    db = client["baojia"]
    rcjhz = []
    collection = db["qdxm"]
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}):
        if "__children" not in post:
            ##print(post)
            continue
        children = post["__children"]
        qdsl = post['数量']
        hit = False##清单级别
        for entry in children:###对一条定额，调价
            hitde = False
            dercj = entry["dercj"]
            dergf = 0
            declf = 0
            dezcf = 0
            dejxf = 0
            for i in range(1, len(dercj)):
                rcj = dercj[i]
                if rcj[1] == bm and rcj[2] == mingcheng and rcj[4] == danwei:
                    hit = True
                    hitde = True
                    rcj[5] = jiage
                    hanliang = rcj[10]
                    rcj[11] = str(float(jiage) * float(hanliang))
                if int(rcj[8]) == 1:##rcjlb
                    dergf = dergf + float(rcj[11])
                elif int(rcj[8]) == 2:
                    declf = declf + float(rcj[11])
                    if rcj[14] == 'true' :
                        dezcf = dezcf + float(rcj[11])
                elif int(rcj[8]) == 3:
                    dejxf = dejxf + float(rcj[11])
            if hitde:
                for i in range(1, len(dercj)):
                    rcj = dercj[i]
                    if rcj[1] == '00EXP001' and rcj[4] == '%':##//回程费占人工费
                        rcj[11] = float(rcj[10]) * dergf / float(100)
                    if rcj[1] == '99EXP007' and rcj[4] == '%':##//回程费占机械费
                        rcj[11] = float(rcj[10]) * dejxf / float(100)
                    if rcj[1] == '99EXP022' and rcj[4] == '%':##//回程费占机械费
                        rcj[11] = float(rcj[10]) * dejxf / float(100)
                    if rcj[1] == '31130104' and rcj[4] == '%':##//回程费占材料费
                        rcj[11] = float(rcj[10]) * declf / float(100)
                dergf = 0
                declf = 0
                dejxf = 0
                dezcf = 0
                for i in range(1, len(dercj)):
                    rcj = dercj[i]
                    
                    if int(rcj[8]) == 1:
                        dergf = dergf + float(rcj[11])
                    elif int(rcj[8]) == 2:
                        declf = declf + float(rcj[11])
                        if rcj[14] == 'true' :
                            dezcf = dezcf + float(rcj[11])
                    elif int(rcj[8]) == 3:
                        dejxf = dejxf + float(rcj[11])


                entry['人工费'] = str(dergf)
                entry['辅材费'] = str(declf - dezcf)
                entry['主材费'] = str(dezcf)
                entry['材料费'] = str(declf)
                entry['机械费'] = str(dejxf)
                if bm.startswith("D"):
                    entry['管理费'] = '0'
                    entry['利润'] = '0'
                elif bz == 0:
                    entry['管理费'] = str((dergf + dejxf) * float(glf) / float(100))
                    entry['利润'] =   str((dergf + dejxf) * float(lr) / float(100))
                else:
                    entry['管理费'] = str((dergf) * float(glf) / float(100))
                    entry['利润'] =   str((dergf) * float(lr) / float(100))
                entry['综合单价'] = str(float(entry['人工费']) + float(entry['材料费']) + float(entry['机械费']) + float(entry['管理费']) + float(entry['利润']))
                entry['合价'] = str(float(entry['综合单价']) * float(entry['数量']) * float(qdsl))
        if hit:
           
            post['综合单价'] = 0
            post['合价'] = 0
            post['人工费'] = 0
            post['主材费'] = 0
            post['设备费'] = 0
            post['辅材费'] = 0
            post['材料费'] = 0
            post['机械费'] = 0
            post['管理费'] = 0
            post['利润'] = 0
            for i in range(len(post["__children"])):
                if len(post["__children"][i]['azfytag']) > 0:
                    continue
                post['综合单价'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['综合单价'])
                post['合价'] += float(post['__children'][i]['合价'])
                post['人工费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['人工费'])
                post['主材费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['主材费'])
                post['设备费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['设备费'])
                post['辅材费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['辅材费'])
                post['材料费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['材料费'])
                post['机械费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['机械费'])
                post['管理费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['管理费'])
                post['利润'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['利润'])
            
            ##处理安装费用
            ##console.log(qd["_children"].length);
            for i in range(len(post["__children"])):
                ##console.log(i);
                if len(post["__children"][i]['azfytag']) > 0:
                    tag = post["__children"][i]['azfytag']
                    if tag[0] == '人工费':
                       current_rgf = post['人工费']
                       added = float(current_rgf) * float(tag[1]) / float(100)
                       added_rgf = added * float(tag[2]) / float(100)
                       added_clf = added * float(tag[3]) / float(100)
                       added_jxf = added * float(tag[4]) / float(100)
                       post["__children"][i]['人工费'] = added_rgf
                       post["__children"][i]['辅材费'] = added_clf
                       post["__children"][i]['材料费'] = added_clf
                       post["__children"][i]['机械费'] = added_jxf
                       if bz == 0:
                           ##changuiqufei
                           post["__children"][i]['管理费'] = (added_rgf + added_jxf) * float(glf) / float(100)
                           post["__children"][i]['利润'] = (added_rgf + added_jxf) * float(lr) / float(100)
                       else:
                           post["__children"][i]['管理费'] = (added_rgf) * float(glf) / float(100)
                           post["__children"][i]['利润'] = (added_rgf) * float(lr) / float(100)
                       
                       
                       post["__children"][i]['综合单价'] = post["__children"][i]['人工费'] + post["__children"][i]['材料费'] + post["__children"][i]['机械费'] + post["__children"][i]['管理费'] +  post["__children"][i]['利润']
                       post["__children"][i]['合价'] = post["__children"][i]['综合单价'] * float(post["__children"][i]['数量']) * float(post['数量'])
                       dercj = post["__children"][i]['dercj']
                       for j in range(1, len(dercj)):
                           dercj[j][11] = float(added) * float(dercj[j][10]) / float(100)
                       
                    
                    if tag[0] == '人工费+机械费':
                       current_rgf = post['人工费'] + post['机械费']
                       added = float(current_rgf) * float(tag[1]) / float(100)
                       added_rgf = added * float(tag[2]) / float(100)
                       added_clf = added * float(tag[3]) / float(100)
                       added_jxf = added * float(tag[4]) / float(100)
                       post["__children"][i]['人工费'] = added_rgf
                       post["__children"][i]['辅材费'] = added_clf
                       post["__children"][i]['材料费'] = added_clf
                       post["__children"][i]['机械费'] = added_jxf
                       if bz == 0:
                           ###changuiqufei
                           post["__children"][i]['管理费'] = (added_rgf + added_jxf) * float(glf) / float(100)
                           post["__children"][i]['利润'] = (added_rgf + added_jxf) * float(lr) / float(100)
                       else:
                           post["__children"][i]['管理费'] = (added_rgf) * float(glf) / float(100)
                           post["__children"][i]['利润'] = (added_rgf) * float(lr) / float(100)
                       
                       
                       post["__children"][i]['综合单价'] = post["__children"][i]['人工费'] + post["__children"][i]['材料费'] + post["__children"][i]['机械费'] + post["__children"][i]['管理费'] +  post["__children"][i]['利润']
                       post["__children"][i]['合价'] = post["__children"][i]['综合单价'] * float(post["__children"][i]['数量']) * float(post["数量"])
                       dercj = post["__children"][i]['dercj']
                       for j in range(1, len(dercj)):
                           dercj[j][11] = float(added) * float(dercj[j][10]) / float(100)
                       
                    
            qdrcj = []
            for i in range(len(post["__children"])):
                dercj = post["__children"][i]['dercj']
                sl = post["__children"][i]['数量']
                for entry in dercj:
                    if '000FE' in entry[1] and entry[4] == '%': ##azfy
                        qdrcj.append(entry)
                    elif '00EXP001' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif '99EXP007' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif '99EXP022' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif '31130104' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif entry[10] != '含量':
                        entry[10] = float(sl) * float(entry[10])
                        qdrcj.append(entry)
            post["rcj"] = util.huizongrcj_order(qdrcj)
            
            post['综合单价'] = 0
            post['合价'] = 0
            post['人工费'] = 0
            post['主材费'] = 0
            post['设备费'] = 0
            post['辅材费'] = 0
            post['材料费'] = 0
            post['机械费'] = 0
            post['管理费'] = 0
            post['利润'] = 0
            for i in range(len(post["__children"])):
                post['综合单价'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['综合单价'])
                post['合价'] += float(post['__children'][i]['合价'])
                post['人工费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['人工费'])
                post['主材费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['主材费'])
                post['设备费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['设备费'])
                post['辅材费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['辅材费'])
                post['材料费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['材料费'])
                post['机械费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['机械费'])
                post['管理费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['管理费'])
                post['利润'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['利润'])
                
         
            await collection.replace_one({'_id': post['_id']}, post )
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        qdrcj = post['rcj'][1:]
        for entry in qdrcj:
            if entry[4] != '%':
                entry[10] = float(entry[10]) * float(post['数量'])
        rcjhz = rcjhz + qdrcj
    collection = db["Djcs"]
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}):
        if "__children" not in post:
            ##print(post)
            continue
        children = post["__children"]
        qdsl = post['数量']
        hit = False
        for entry in children:###对一条定额，调价
            hitde = False
            dercj = entry["dercj"]
            dergf = 0
            declf = 0
            dejxf = 0
            dezcf = 0
            for i in range(1, len(dercj)):
                rcj = dercj[i]
                if rcj[1] == bm and rcj[2] == mingcheng and rcj[4] == danwei:
                    hit = True
                    hitde = True
                    rcj[5] = jiage
                    hanliang = rcj[10]
                    rcj[11] = str(float(jiage) * float(hanliang))
                if int(rcj[8]) == 1:
                    dergf = dergf + float(rcj[11])
                elif int(rcj[8]) == 2:
                    declf = declf + float(rcj[11])
                    if rcj[14] == 'true' :
                        dezcf = dezcf + float(rcj[11])
                elif int(rcj[8]) == 3:
                    dejxf = dejxf + float(rcj[11])
            if hitde:
                for i in range(1, len(dercj)):
                    rcj = dercj[i]
                    if rcj[1] == '00EXP001' and rcj[4] == '%':##//回程费占人工费
                        rcj[11] = float(rcj[10]) * dergf / float(100)
                    if rcj[1] == '99EXP007' and rcj[4] == '%':##//回程费占机械费
                        rcj[11] = float(rcj[10]) * dejxf / float(100)
                    if rcj[1] == '99EXP022' and rcj[4] == '%':##//回程费占机械费
                        rcj[11] = float(rcj[10]) * dejxf / float(100)
                    if rcj[1] == '31130104' and rcj[4] == '%':##//回程费占材料费
                        rcj[11] = float(rcj[10]) * declf / float(100)
                dergf = 0
                declf = 0
                dejxf = 0
                dezcf = 0
                for i in range(1, len(dercj)):
                    rcj = dercj[i]
                    
                    if int(rcj[8]) == 1:
                        dergf = dergf + float(rcj[11])
                    elif int(rcj[8]) == 2:
                        declf = declf + float(rcj[11])
                        if rcj[14] == 'true' :
                            dezcf = dezcf + float(rcj[11])
                    elif int(rcj[8]) == 3:
                        dejxf = dejxf + float(rcj[11])
 


                entry['人工费'] = str(dergf)
                entry['辅材费'] = str(declf - dezcf)
                entry['材料费'] = str(declf)
                entry['机械费'] = str(dejxf)
                if bm.startswith("D"):
                    entry['管理费'] = '0'
                    entry['利润'] = '0'  
                elif bz == 0:
                    entry['管理费'] = str((dergf + dejxf) * float(glf) / float(100))
                    entry['利润'] =   str((dergf + dejxf) * float(lr) / float(100))
                else:
                    entry['管理费'] = str((dergf) * float(glf) / float(100))
                    entry['利润'] =   str((dergf) * float(lr) / float(100))
                entry['综合单价'] = str(float(entry['人工费']) + float(entry['材料费']) + float(entry['机械费']) + float(entry['管理费']) + float(entry['利润']))
                entry['合价'] = str(float(entry['综合单价']) * float(entry['数量']) * float(qdsl))
        if hit:
            qdrcj = []
            for i in range(len(post["__children"])):
                dercj = post["__children"][i]['dercj']
                sl = post["__children"][i]['数量']
                for entry in dercj:
                    if '000FE' in entry[1] and entry[4] == '%': ##azfy
                        qdrcj.append(entry)
                    elif '00EXP001' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif '99EXP007' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif '99EXP022' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif '31130104' in entry[1] and entry[4] == '%': ##回程费
                        qdrcj.append(entry)
                    elif entry[10] != '含量':
                        entry[10] = float(sl) * float(entry[10])
                        qdrcj.append(entry)
            post["rcj"] = util.huizongrcj_order(qdrcj)
            post['综合单价'] = 0
            post['合价'] = 0
            post['人工费'] = 0
            post['主材费'] = 0
            post['设备费'] = 0
            post['辅材费'] = 0
            post['材料费'] = 0
            post['机械费'] = 0
            post['管理费'] = 0
            post['利润'] = 0
            for i in range(len(post["__children"])):
                post['综合单价'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['综合单价'])
                post['合价'] += float(post['__children'][i]['合价'])
                post['人工费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['人工费'])
                post['主材费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['主材费'])
                post['设备费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['设备费'])
                post['辅材费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['辅材费'])
                post['材料费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['材料费'])
                post['机械费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['机械费'])
                post['管理费'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['管理费'])
                post['利润'] += float(post['__children'][i]['数量']) * float(post['__children'][i]['利润'])
            
            
            await collection.replace_one({'_id': post['_id']}, post )
    
    async for post in collection.find({'biao_id': biao_id, "Dwgcbh": bh}, {'__children': 0}):
        
        qdrcj = post['rcj'][1:]
        for entry in qdrcj:
            if entry[4] != '%':
                entry[10] = float(entry[10]) * float(post['数量'])
        rcjhz = rcjhz + qdrcj
    newHuizong = util.huizongrcj(rcjhz)
    collection = db["Dwgc"]
    document = await collection.find_one({'biao_id': biao_id,'Dwgcbh': bh})
    fbrgycl = document['fbrgycl']
    for entry in fbrgycl:
        id = entry['材料编号']
        for entry2 in newHuizong:
            if entry2[1] == id:
                entry['数量'] = entry2[5]
                entry['合价'] = entry2[7]

    await collection.update_one({'biao_id': biao_id, "Dwgcbh": bh}, {'$set': {'rcjhz': newHuizong, 'fbrgycl': fbrgycl}})
    await zongjiaDwgc(client, biao_id, bh)
    await tbxx(client, biao_id)
    return newHuizong




#################清单AI#############################

async def tuijian(client, query):##default return 10 neighbors
    
    collection = client.get_or_create_collection(name="qingdan")
    result = collection.get(ids=[query], include=["documents", "metadatas", "embeddings"])
    if result and len(result['embeddings']) > 0:
        result_ = re.findall(r'\d{9}', query)
        bh = result_[0]
        if bh[0:2] == '01':
            collection = client.get_or_create_collection(name="de-tj")
            hit = collection.query(
                query_embeddings=result['embeddings']
            )
            answer = []
            if len(hit['ids']) == 0:
                return answer
            for i in range(len(hit['ids'][0])):
                answer.append({'组价定额': hit['ids'][0][i], '定额名称': hit['documents'][0][i], "id": hit['ids'][0][i]})
            return answer
        elif bh[0:2] == '03':
            collection = client.get_or_create_collection(name="de-az")
            hit = collection.query(
                query_embeddings=result['embeddings']
            )
            answer = []
            if len(hit['ids']) == 0:
                return answer
            for i in range(len(hit['ids'][0])):
                answer.append({'组价定额': hit['ids'][0][i], '定额名称': hit['documents'][0][i], "id": hit['ids'][0][i]})
            return answer
        elif bh[0:2] == '04':
            collection = client.get_or_create_collection(name="de-sz")
            hit = collection.query(
                query_embeddings=result['embeddings']
            )
            answer = []
            if len(hit['ids']) == 0:
                return answer
            for i in range(len(hit['ids'][0])):
                answer.append({'组价定额': hit['ids'][0][i], '定额名称': hit['documents'][0][i], "id": hit['ids'][0][i]})
            return answer
        elif bh[0:2] == '05':
            collection = client.get_or_create_collection(name="de-yl")
            hit = collection.query(
                query_embeddings=result['embeddings']
            )
            answer = []
            if len(hit['ids']) == 0:
                return answer
            for i in range(len(hit['ids'][0])):
                answer.append({'组价定额': hit['ids'][0][i], '定额名称': hit['documents'][0][i], "id": hit['ids'][0][i]})
            return answer
        else:
            return []
        

    else:
        return []




async def zujia(client, query, user):
    db = client["baojia"]
    collection = db["qdxm"]
    regex_pattern1 = re.compile("^"+query, re.IGNORECASE)
    result = {}
    async for post in collection.find({'清单编码': {"$regex" : regex_pattern1}, "user": user, "__children.0": {"$exists" : 1} }):
        t = []
        name = []
        for entry in post["__children"]:
            t.append(entry["清单编码"])
            name.append(entry["名称"])
        
        key = "@".join(t)
        key = key + "@" + post['单位']
        names = "@@".join(name)
        if key in result:
            result[key]["count"] = result[key]["count"] + 1
        else:
            result[key] = {"count": 1, "name" : names, "unit" : post["单位"], "price" : post["综合单价"]}
    return [{"id" : x, "组价定额" : x[0:x.rfind("@")], "数量" : result[x]["count"], "定额名称" : result[x]["name"], "单位" : result[x]['unit'], "综合单价" : result[x]['price']} for x in result.keys()]

async def statistics(client, query, user):
    
    db = client["baojia"]
    collection = db["qdxm"]
    if query == "":
        tj = 0
        fg = 0
        az = 0
        sz = 0
        yl = 0
        regex_pattern1 = re.compile("^01", re.IGNORECASE)
        regex_pattern2 = re.compile("^02", re.IGNORECASE)
        regex_pattern3 = re.compile("^03", re.IGNORECASE)
        regex_pattern4 = re.compile("^04", re.IGNORECASE)
        regex_pattern5 = re.compile("^05", re.IGNORECASE)

        tj = await collection.count_documents({'清单编码': regex_pattern1, "user" : user})
        fg = await collection.count_documents({'清单编码': regex_pattern2, "user" : user})
        az = await collection.count_documents({'清单编码': regex_pattern3, "user" : user})
        sz = await collection.count_documents({'清单编码': regex_pattern4, "user" : user})
        yl = await collection.count_documents({'清单编码': regex_pattern5, "user" : user})
    
        return [["工程类别", "清单数"],
    ["建筑与装饰工程", tj],
    ["仿古建筑", fg],
    ["安装工程", az],
    ["市政工程", sz],
    ["园林绿化工程", yl]]

    elif query == "建筑与装饰工程":
        tsf = 0
        ldm = 0
        zj = 0
        qz = 0
        hnt = 0
        js = 0
        qzm = 0
        mc = 0
        wm = 0
        total = 0
        tp = 0
        yq = 0
        
        regex_pattern1 = re.compile("^01", re.IGNORECASE)
        regex_pattern2 = re.compile("^0101", re.IGNORECASE)
        regex_pattern3 = re.compile("^0111", re.IGNORECASE)
        regex_pattern4 = re.compile("^0103", re.IGNORECASE)
        regex_pattern5 = re.compile("^0104", re.IGNORECASE)
        regex_pattern6 = re.compile("^0105", re.IGNORECASE)
        regex_pattern7 = re.compile("^0106", re.IGNORECASE)
        regex_pattern8 = re.compile("^0112", re.IGNORECASE)
        regex_pattern9 = re.compile("^0108", re.IGNORECASE)
        regex_pattern10 = re.compile("^0109", re.IGNORECASE)
        regex_pattern11 = re.compile("^0113", re.IGNORECASE)
        regex_pattern12 = re.compile("^0114", re.IGNORECASE)
        

        total = await collection.count_documents({'清单编码': regex_pattern1, "user" : user})
        tsf = await collection.count_documents({'清单编码': regex_pattern2, "user" : user})
        ldm = await collection.count_documents({'清单编码': regex_pattern3, "user" : user})
        zj = await collection.count_documents({'清单编码': regex_pattern4, "user" : user})
        qz = await collection.count_documents({'清单编码': regex_pattern5, "user" : user})
        hnt = await collection.count_documents({'清单编码': regex_pattern6, "user" : user})
        js = await collection.count_documents({'清单编码': regex_pattern7, "user" : user})
        qzm = await collection.count_documents({'清单编码': regex_pattern8, "user" : user})
        mc = await collection.count_documents({'清单编码': regex_pattern9, "user" : user})
        wm = await collection.count_documents({'清单编码': regex_pattern10, "user" : user})
        tp = await collection.count_documents({'清单编码': regex_pattern11, "user" : user})
        yq = await collection.count_documents({'清单编码': regex_pattern12, "user" : user})
        
    
        return [
          ["工程类别", "清单数"],
          ['土石方工程', tsf],
          ['楼地面', ldm],
          ['桩基工程', zj],
          ['砌筑工程', qz],
          ['（钢筋）混凝土', hnt],
          ['金属结构', js],
          ['墙、柱面', qzm],
          ['门窗工程', mc],
          ['屋面及防水工程', wm],
          ['天棚工程', tp],
          ['油漆、涂料、裱糊', yq],
          ['其他', total - tsf - ldm - zj - qz - hnt - js - qzm - mc - wm - tp - yq],
          ]

    elif query == "安装工程":
        jx = 0
        rl = 0
        jz = 0
        dq = 0
        znh = 0
        yb = 0
        tf = 0
        gd = 0
        xf = 0
        jps = 0
        tx = 0
        sy = 0
        total = 0
       
        regex_pattern1 = re.compile("^03", re.IGNORECASE)
        regex_pattern2 = re.compile("^0301", re.IGNORECASE)
        regex_pattern3 = re.compile("^0302", re.IGNORECASE)
        regex_pattern4 = re.compile("^0303", re.IGNORECASE)
        regex_pattern5 = re.compile("^0304", re.IGNORECASE)
        regex_pattern6 = re.compile("^0305", re.IGNORECASE)
        regex_pattern7 = re.compile("^0306", re.IGNORECASE)
        regex_pattern8 = re.compile("^0307", re.IGNORECASE)
        regex_pattern9 = re.compile("^0308", re.IGNORECASE)
        regex_pattern10 = re.compile("^0309", re.IGNORECASE)
        regex_pattern11 = re.compile("^0310", re.IGNORECASE)
        regex_pattern12 = re.compile("^0311", re.IGNORECASE)
        regex_pattern13 = re.compile("^0312", re.IGNORECASE)

        

        total = await collection.count_documents({'清单编码': regex_pattern1, "user" : user})
        jx = await collection.count_documents({'清单编码': regex_pattern2, "user" : user})
        rl = await collection.count_documents({'清单编码': regex_pattern3, "user" : user})
        jz = await collection.count_documents({'清单编码': regex_pattern4, "user" : user})
        dq = await collection.count_documents({'清单编码': regex_pattern5, "user" : user})
        znh = await collection.count_documents({'清单编码': regex_pattern6, "user" : user})
        yb = await collection.count_documents({'清单编码': regex_pattern7, "user" : user})
        tf = await collection.count_documents({'清单编码': regex_pattern8, "user" : user})
        gd = await collection.count_documents({'清单编码': regex_pattern9, "user" : user})
        xf = await collection.count_documents({'清单编码': regex_pattern10, "user" : user})
        jps = await collection.count_documents({'清单编码': regex_pattern11, "user" : user})
        tx = await collection.count_documents({'清单编码': regex_pattern12, "user" : user})
        sy = await collection.count_documents({'清单编码': regex_pattern13, "user" : user})
    
        return [
          ["工程类别", "清单数"],
          ['机械设备', jx],
          ['热力设备', rl],
          ['静置设备、金属结构', jz],
          ['电气设备', dq],
          ['智能化', znh],
          ['仪表安装', yb],
          ['通风空调', tf],
          ['工业管道', gd],
          ['消防工程', xf],
          ['给排水、采暖、燃气', jps],
          ['通信设备', tx],
          ['刷油、防腐蚀、绝热', sy],
          ['其他', total - jx - rl - jz - dq - znh - yb - tf - gd - xf - jps - tx - sy],
          ]

    elif query == "市政工程":
        tsf = 0
        dl = 0
        qh = 0
        sd = 0
        gw = 0
        scl = 0
        lj = 0
        ld = 0
        gj = 0
        cc = 0
        total = 0
       
        regex_pattern1 = re.compile("^04", re.IGNORECASE)
        regex_pattern2 = re.compile("^0401", re.IGNORECASE)
        regex_pattern3 = re.compile("^0402", re.IGNORECASE)
        regex_pattern4 = re.compile("^0403", re.IGNORECASE)
        regex_pattern5 = re.compile("^0404", re.IGNORECASE)
        regex_pattern6 = re.compile("^0405", re.IGNORECASE)
        regex_pattern7 = re.compile("^0406", re.IGNORECASE)
        regex_pattern8 = re.compile("^0407", re.IGNORECASE)
        regex_pattern9 = re.compile("^0408", re.IGNORECASE)
        regex_pattern10 = re.compile("^0409", re.IGNORECASE)
        regex_pattern11 = re.compile("^0410", re.IGNORECASE)
       

        

        total = await collection.count_documents({'清单编码': regex_pattern1, "user" : user})
        tsf = await collection.count_documents({'清单编码': regex_pattern2, "user" : user})
        dl = await collection.count_documents({'清单编码': regex_pattern3, "user" : user})
        qh = await collection.count_documents({'清单编码': regex_pattern4, "user" : user})
        sd = await collection.count_documents({'清单编码': regex_pattern5, "user" : user})
        gw = await collection.count_documents({'清单编码': regex_pattern6, "user" : user})
        scl = await collection.count_documents({'清单编码': regex_pattern7, "user" : user})
        lj = await collection.count_documents({'清单编码': regex_pattern8, "user" : user})
        ld = await collection.count_documents({'清单编码': regex_pattern9, "user" : user})
        gj = await collection.count_documents({'清单编码': regex_pattern10, "user" : user})
        cc = await collection.count_documents({'清单编码': regex_pattern11, "user" : user})
      
        return [
          ["工程类别", "清单数"],
          ['土石方', tsf],
          ['道路工程', dl],
          ['桥涵工程', qh],
          ['隧道工程', sd],
          ['管网工程', gw],
          ['水处理', scl],
          ['垃圾处理', lj],
          ['路灯工程', ld],
          ['钢筋工程', gj],
          ['拆除工程', cc],
        
          ['其他', total - tsf - dl - qh - sd - gw - scl - lj - ld - gj - cc ],
          ]
    elif query == "园林绿化工程":
        lh = 0
        yl = 0
        jg = 0
       
        total = 0
       
        regex_pattern1 = re.compile("^05", re.IGNORECASE)
        regex_pattern2 = re.compile("^0501", re.IGNORECASE)
        regex_pattern3 = re.compile("^0502", re.IGNORECASE)
        regex_pattern4 = re.compile("^0503", re.IGNORECASE)
       
       

        

        total = await collection.count_documents({'清单编码': regex_pattern1, "user" : user})
        lh = await collection.count_documents({'清单编码': regex_pattern2, "user" : user})
        yl = await collection.count_documents({'清单编码': regex_pattern3, "user" : user})
        jg = await collection.count_documents({'清单编码': regex_pattern4, "user" : user})
       
      
        return [
          ["工程类别", "清单数"],
          ['绿化工程', lh],
          ['园路、园桥', yl],
          ['园林景观', jg],
         
        
          ['其他', total - lh - yl - jg ],
          ]
    elif query == "仿古建筑":
        zz = 0
        sz = 0
        ll = 0
        hnt = 0
        mz = 0
        dm = 0
        mh = 0
        yq = 0
       
       
        total = 0
       
        regex_pattern1 = re.compile("^02", re.IGNORECASE)
        regex_pattern2 = re.compile("^0201", re.IGNORECASE)
        regex_pattern3 = re.compile("^0202", re.IGNORECASE)
        regex_pattern4 = re.compile("^0203", re.IGNORECASE)
        regex_pattern5 = re.compile("^0204", re.IGNORECASE)
        regex_pattern6 = re.compile("^0205", re.IGNORECASE)
        regex_pattern7 = re.compile("^0206", re.IGNORECASE)
        regex_pattern8 = re.compile("^0207", re.IGNORECASE)
        regex_pattern9 = re.compile("^0208", re.IGNORECASE)
        regex_pattern10 = re.compile("^0209", re.IGNORECASE)
       
       

        

        total = await collection.count_documents({'清单编码': regex_pattern1, "user" : user})
        zz = await collection.count_documents({'清单编码': regex_pattern2, "user" : user})
        sz = await collection.count_documents({'清单编码': regex_pattern3, "user" : user})
        ll = await collection.count_documents({'清单编码': regex_pattern4, "user" : user})
        hnt = await collection.count_documents({'清单编码': regex_pattern5, "user" : user})
        mz = await collection.count_documents({'清单编码': regex_pattern6, "user" : user})
        wm = await collection.count_documents({'清单编码': regex_pattern7, "user" : user})
        dm = await collection.count_documents({'清单编码': regex_pattern8, "user" : user })
        mh = await collection.count_documents({'清单编码': regex_pattern9, "user" : user})
        yq = await collection.count_documents({'清单编码': regex_pattern10, "user" : user})
       
      
        return [
          ["工程类别", "清单数"],
          ['砖作工程', zz],
          ['石作工程', sz],
          ['琉璃砌筑', ll],
          ['（钢筋）混凝土', hnt],
          ['木作工程', mz],
          ['屋面工程', wm],
          ['地面工程', dm],
          ['抹灰工程', mh],
          ['油漆彩画', yq],
         
        
          ['其他', total - zz - sz - ll - hnt - mz - wm - dm - mh - yq ],
          ]

async def frequency(client, type, query, user):
    db = client["baojia"]
    collection = db["qdxm"]
    output = []
    if type == '安装工程':
        if query == "电气设备":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0304", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "机械设备":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0301", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "热力设备":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0302", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "静置设备、金属结构":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0303", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "智能化":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0305", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "仪表安装":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0306", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "通风空调":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0307", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "工业管道":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0308", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "消防工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0309", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "给排水、采暖、燃气":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0310", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "通信设备":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0311", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "刷油、防腐蚀、绝热":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0312", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "其他":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0313", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        


    elif type == "建筑与装饰工程":
        if query == "（钢筋）混凝土":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0105", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "金属结构":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0106", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "土石方工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0101", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "桩基工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0103", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "砌筑工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0104", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "门窗工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0108", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "屋面及防水工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0109", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "楼地面":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0110", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "墙、柱面":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0112", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "天棚工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0113", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "油漆、涂料、裱糊":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0114", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
        elif query == "其他":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^(0102|0107|0110|0115|0116|0117|0118)", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", "").replace("(屋面)", ""), "数量" : item["totalQuantity"]})
    
    elif type == "市政工程":
        if query == "管网工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0405", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "道路工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0402", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "土石方":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0401", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "路灯工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0408", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "拆除工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0410", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "桥涵工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0403", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "隧道工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0404", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "水处理":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0406", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "垃圾处理":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0407", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "钢筋工程":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0409", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == "其他":
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^(0411|0413)", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
    
    elif type == "园林绿化工程":
        if query == '绿化工程':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0501", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '园路、园桥':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0502", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '园林景观':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0503", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '其他':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0504", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        
    elif type == "仿古建筑":
        if query == '砖作工程':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0201", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '石作工程':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0202", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '琉璃砌筑':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0203", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '（钢筋）混凝土':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0204", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '木作工程':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0205", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '屋面工程':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0206", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '地面工程':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0207", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '抹灰工程':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0208", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '油漆彩画':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0209", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        elif query == '其他':
            pipeline = [
                {"$match":
                    {"清单编码": re.compile("^0210", re.IGNORECASE), "user" : user},
                  
                    
                },
                 
                {"$project" : {"code" : {"$substrCP": [ "$清单编码", 0, 9 ]},
                              "名称" : 1}
                },
                {
                   "$group": { "_id": "$code", "totalQuantity": { "$count": {} }, "name" : {"$first": "$名称"}}
                },
                {
                  "$sort" : { "totalQuantity": -1 }
                },
                {"$limit" : 10}
            ]
            result = await collection.aggregate(pipeline)
            async for item in result:
                output.append({"id" : item["_id"], "清单编码": item["_id"], "名称" : item['name'].replace("（超高）", ""), "数量" : item["totalQuantity"]})
        






    return output