数据校验--校验生产辅材是否维护正确

from USSyunwei.bin import *
from tqdm import tqdm



import decimal
error_list=set()
#查询十分到家分账金额大于费用项金额的一半
sql="select * from uss_payunit.tcl_special_pardon_charge where show_scene_type=1  "
# uat_DB=DB(sql=sql,DB={'ip': "10.74.149.251", 'port': 6033, 'usr': "rw_uss2_uat", 'pwd': "rw_uss2_uat_123#"},database="uss_payunit")

# statu,text=uat_DB.select()
statu,text=DB_sql(sql=sql,database="uss_payunit",example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
n=0
if statu:
    for i in text:
        # print(type(i[17]),i[17],type(i[27]),i[27])
        if float(i[17])/2<float(i[27]):
            n+=1
            error_list.add(f"{i[9]}十分到家分账金额大于费用项金额的一半")
else:
    print(f"{sql}查询失败，{text}")

error_list=set()
#查询辅材组数据id
sql=" select * from uss_basicdata.bas_aux_material_group  where group_status =1  "
# uat_DB=DB(sql=sql,DB={'ip': "10.74.149.251", 'port': 6033, 'usr': "rw_uss2_uat", 'pwd': "rw_uss2_uat_123#"},database="uss_basicdata")
# statu,text=uat_DB.select()
statu,text=DB_sql(sql=sql,database="uss_payunit",example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
# print(sql)
# print(text)
if len(text)==0:
    print("辅材组没有正常的数据请维护")
else:
    #根据辅材组id查出每个备件的id
    for i in tqdm(text):
        sfdj_am = 0
        sql = f"select parts_id,parts_qty,unit from uss_basicdata.bas_aux_material_group_item where group_id ={i[0]}"
        # print(sql)
        # uat_DB = DB(sql=sql, DB={'ip': "10.74.149.251", 'port': 6033, 'usr': "rw_uss2_uat", 'pwd': "rw_uss2_uat_123#"},
        #             database="uss_basicdata")
        # statu,text = uat_DB.select()
        statu, text = DB_sql(sql=sql, database="uss_payunit",
                             example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
        for k in text:
            # print(k)
            #根据备件id查询备件批发价格
            sql = f"select sale_price from uss_parts.sp_parts_price where parts_id={k[0]} order by creation_date desc limit 1"
            # uat_DB = DB(sql=sql, DB={'ip': "10.74.149.251", 'port': 6033, 'usr': "rw_uss2_uat", 'pwd': "rw_uss2_uat_123#"},
            #             database="uss_basicdata")
            # statu, text = uat_DB.select()
            statu, text = DB_sql(sql=sql, database="uss_parts",
                                 example="生产_营销_十分到家_USS2.0_parts_MYSQL_从(10.68.70.35)")
            #根据批发价算出辅材总价
            # if k[2]=="分米":
            #     num=k[1]/10
            # elif k[2]=="厘米":
            #     num = k[1] / 100
            # elif k[2]=="公分":
            #     num = k[1] / 100
            # else:
            #     num=k[1]
            # print(sql)
            # print(sfdj_am,text,k)
        #每个（备件批发价*数量）
            sfdj_am=sfdj_am+ float(text[0][0]) * float(k[1])
        #查询十分到家分账金额错误或者辅材维护了，特设费用没有维护的数据
        # print(i[10])
        sql=f"select * from uss_payunit.tcl_special_pardon_charge where charge_name='{i[10]}' and  usable=0 and channel_id !=0"
        # uat_DB=DB(sql=sql,DB={'ip': "10.74.149.251", 'port': 6033, 'usr': "rw_uss2_uat", 'pwd': "rw_uss2_uat_123#"},database="uss_payunit")
        # statu,text=uat_DB.select()
        statu, text = DB_sql(sql=sql, database="uss_payunit",
                             example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
        if statu:
            for p in text:
                sfdj_am=f"{float(sfdj_am):.2f}"
                # print(sql)
                if float(sfdj_am)!=float(p[27]):
                    error_list.add(f"{i[10]}数据维护错误,辅材金额为：{sfdj_am}，特设费用十分到家维护为：{p[27]}")
                # else:
                #     print(f"{i[10]},辅材金额为：{sfdj_am}，特设费用十分到家维护为：{p[27]}数据一致")

        else:
            error_list.add(f"费用名称为：'{i[10]}' ，特设费用没有维护请维护{text}")
            # print(f"费用名称为：'{i[10]}' ，特设费用没有维护请维护{text}")
                    # print(f"{i[1]}数据维护错误,辅材金额为：{sfdj_am}，特设费用十分到家维护为：{p[27]}")

# print("开始校验：特设费用维护了，辅材没维护的数据")
#查询特设费用维护了，辅材没维护的数据
sql=f"select * from uss_payunit.tcl_special_pardon_charge where show_scene_type=1  and  usable=0 and channel_id !=0"
# uat_DB=DB(sql=sql,DB={'ip': "10.74.149.251", 'port': 6033, 'usr': "rw_uss2_uat", 'pwd': "rw_uss2_uat_123#"},database="uss_payunit")
# statu,text=uat_DB.select()
statu,text=DB_sql(sql=sql,database="uss_payunit",example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
if len(text)==0:
    print("特设费用没有查到数据请检查是否导入成功，及导入标识是否正确")

else:
    print(f"-------------总计获取到{len(text)}条特设费用，下面是校验结果：-----------------------")
    sql = f"select group_name from uss_basicdata.bas_aux_material_group  where group_status =1"
    # uat_DB = DB(sql=sql, DB={'ip': "10.74.149.251", 'port': 6033, 'usr': "rw_uss2_uat", 'pwd': "rw_uss2_uat_123#"},
    #             database="uss_basicdata")
    # statu1, text1 = uat_DB.select()
    statu1, text1  = DB_sql(sql=sql, database="uss_payunit", example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
    group_name_list=[]
    for r in text1:
        group_name_list.append(r[0])
    l=0
    for u in tqdm(text):
        if statu1:
            if u[9] not in group_name_list:
                # print(group_name_list)
                error_list.add(f"{u[9]}特设费用维护了，但是辅材组没维护")
                # print(f"{u[9]}特设费用维护了，但是辅材组没维护")
        else:
            print(f"{u[9]}查询失败失败原因为{text1}")
        l+=1
for i in error_list:
    print(i)
print("代码执行结束")