import json
import calendar
from bin import *
start_time_yue = 1
end_time=start_time_yue+1
start_time_r=1
day,daynum=calendar.monthrange(2023,start_time_yue)
numtable=1
num=1
data_vale=[]
path = r"C:\Users\isoftstone\Desktop\导出.xlsx"
header=["case","工单","服务方式","分类","品牌","品类","小类","产品","向公司收费","向用户收费","完工时间"]
while 1<=start_time_yue <2:
# if start_time_yue<=start_time_yue <end_time:
# if 2<=start_time_yue<3:
if start_time_r<=daynum:
print(start_time_yue, start_time_r)
data_vale = []
str_start_time_yue=str(start_time_yue).zfill(2)
str_start_time_r = str(start_time_r).zfill(2)
sql=f"""select submit_param,finish_time from sa_order_finish_info a left join sa_order b on b.order_no=a.order_no where b.item_cat_name ='空调' and a.parent_service_type_name='安装' and
a.finish_time>='2023-{str_start_time_yue}-{str_start_time_r} 00:00:00' and a.finish_time<='2023-{str_start_time_yue}-{str_start_time_r} 23:59:59' """
ku="生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)"
biao="uss_workorder"
while 1:
try:
statu2, data = DB_sql(sql, database="uss_workorder",example="生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)")
break
except:
sleep(2)
statu2, data = DB_sql(sql, database="uss_workorder",
example="生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)")
for i in data:
# print(i)
excel_table_list = []
com_fee=""
sur_fee=""
# print()
data=json.loads(i[0])
try:
applyFee=data["applyFeeList"]
except:
pass
try:
ussFeedata=data["userFeeList"]
except:
pass
excel_table_list.append(data["caseNo"])
excel_table_list.append(data["orderNo"])
excel_table_list.append(data["parentServiceTypeName"])
excel_table_list.append(data["serviceTypeName"])
excel_table_list.append(data["brandName"])
excel_table_list.append(data["bigItemCatName"])
excel_table_list.append(data["smallItemCatName"])
excel_table_list.append(data["itemName"])
try:
if len(applyFee)!=0:
# 特殊处理向公司收费
for Fee in applyFee:
com_fee=com_fee+str(Fee["feeProjectName"])+":"+str(Fee["price"])+","
# excel_table_list.append(Fee["feeProjectName"])
# excel_table_list.append(Fee["price"])
except:
pass
excel_table_list.append(com_fee)
try:
if len(ussFeedata) != 0:
#特殊处理向用户收费
for usrFee in ussFeedata:
sur_fee = sur_fee + str(usrFee["feeProjectName"]) + ":" + str(usrFee["price"]) + ","
# excel_table_list.append(usrFee["feeProjectName"])
# excel_table_list.append(usrFee["price"])
except:
pass
excel_table_list.append(sur_fee)
excel_table_list.append(i[1])
data_vale.append(excel_table_list)
# 调用函数追加写入数据到 Excel 文件
append_data_to_excel(data_vale, path,header,num)
# excel_zhui_data(path,data_vale,num)
num += len(data_vale)
# numtable+=len(data_vale)
start_time_r+=1
else:
start_time_yue+=1
start_time_r = 1
# print(start_time_yue,start_time_r)