import json
import calendar
import re
from datetime import datetime ,timedelta
# from bin import *
import requests
from time import sleep
import os
import math
ismonthwrite=True#是否按月写入，数据量大时可开启
start_time="2024-06-01"#开始时间
end_time="2024-10-31"#结束时间
# end_time="2024-07-31"#结束时间
wirte_path = fr"C:\Users\isoftstone\Desktop\财务对账单数据导出.xlsx" # 写入的文件路径
#
# read_path = fr"C:\Users\issuser\Desktop\脚本模板\数据导出SQL\待结算工单导出.txt"#读取文件的路径
# read_path = fr"C:\Users\issuser\Desktop\脚本模板\数据导出SQL\服务商待入账费用单.txt"#读取文件的路径
# read_path = fr"C:\Users\isoftstone\Desktop\脚本模板\数据导出SQL\服务商待入账追溯单.txt"#读取文件的路径
read_path = fr"C:\Users\isoftstone\Desktop\脚本模板\数据导出SQL\财务对账单数据导出.txt"#读取文件的路径
# read_path = fr"C:\Users\issuser\Desktop\脚本模板\数据导出SQL\是否安装原因导出.txt"#读取文件的路径
# read_path = fr"C:\Users\issuser\Desktop\脚本模板\数据导出SQL\商户已完工订单查询.txt"#读取文件的路径
# read_path = fr"C:\Users\issuser\Desktop\脚本模板\数据导出SQL\用户验收表.txt"#读取文件的路径
# read_path = fr"C:\Users\issuser\Desktop\脚本模板\数据导出SQL\大尺寸勘测SQL.txt"#读取文件的路径
with open(read_path, 'r',encoding="utf-8") as file:
sql = file.read()
aa = datetime.now()# 记录代码执行时间
print(aa)
start_timelist = start_time.split("-")
end_timelist = end_time.split("-")
start_time_rq = int(start_timelist[2]) # 开始日期1-31号
start_time_yue = int(start_timelist[1]) # 开始月份
start_time_nian = int(start_timelist[0]) # 开始年份年份
end_time_r = int(end_timelist[2]) # 结束日期1-31号
end_time_yue = int(end_timelist[1]) # 结束月份
end_time_nian = int(end_timelist[0]) # 结束年份
data_vale = [] # 写入的数据表
# else:
# writer_excel(Threaddata)
# executor_Thread(sheet_num+1, writer_excel, datalist, num=1, isprint=True)
print("全部写入完成")
def splitdate(startdate,enddate):
time1 = datetime.strptime(startdate, "%Y-%m-%d %H:%M:%S")
time2 = datetime.strptime(enddate, "%Y-%m-%d %H:%M:%S")
# 计算两个时间的中间时间
delta = (time2 - time1) / 2
middle_time = time1 + delta
# 输出中间时间
return middle_time.strftime("%Y-%m-%d %H:%M:%S")
def add_one_second(time_str):
dt = datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S")
dt += timedelta(seconds=1)
return dt.strftime("%Y-%m-%d %H:%M:%S")
while start_time_nian <= end_time_nian:
end_time_rbf=end_time_r
# datenum = 0
for yue in range(start_time_yue, end_time_yue + 1):
day, daynum = calendar.monthrange(start_time_nian, yue) # 获取该年该月的一个月有多少天
# 获取当前日期时间
now = datetime.now()
# 获取当前的月份和日期
month = now.month
day_of_month = now.day
yuedatalen=0
# print(month,day_of_month)
# # 获取当前是一个月中的第几天
# day_of_month = now.day
if yue == start_time_yue:
start_time_rq = start_time_rq
if end_time_yue == start_time_yue:
end_time_rq = end_time_r
else:
end_time_rq = daynum
else:
start_time_rq = 1
if yue == month and daynum == end_time_r :
end_time_rq = daynum
else:
end_time_rq=end_time_rbf
select_date={}
for start_time_r in range(start_time_rq, end_time_rq + 1):
# datenum += 1
yuanshisql = sql
if yue == month and day_of_month == start_time_r - 1:
break
# select_date[datenum]=[start_time_nian,yue, start_time_r,]
print(yue, start_time_r)
str_yue = str(yue).zfill(2)
str_start_time_r = str(start_time_r).zfill(2)
sqlzhix = sql+f"""
between '{start_time_nian}-{str_yue}-{str_start_time_r} 00:00:00'
and '{start_time_nian}-{str_yue}-{str_start_time_r} 23:59:59' """
# select_date[datenum] = [start_time_nian, yue, start_time_r,sqlzhix ]
# for i in range(1,datenum+1):
# # 不同库需修改
# #工单库
#
# ku = "生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)"
# biao = "uss_workorder"
#基础结算库
#
ku = "生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)"
biao = "uss_finance"
#统付统付库
# biao = "uss_payunit"
# # 不同库需修改（工单库）
# while 1:
# try:
# statu2, data = DB_sql(sqlzhix, database="uss_workorder",
# example="生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)")
# break
# except:
# sleep(2)
# statu2, data = DB_sql(sqlzhix, database="uss_workorder",
# example="生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)")
# 不同库需修改（基础结算）
# while 1:
# try:
# statu2, data = DB_sql(sqlzhix, database="uss_finance",
# example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
# break
# except:
# sleep(2)
# statu2, data = DB_sql(sqlzhix, database="uss_finance",
# example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
# 不同库需修改（统收统付）
while 1:
try:
statu2, data = DB_sql(sqlzhix, database="uss_payunit",
example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
break
except:
sleep(2)
statu2, data = DB_sql(sqlzhix, database="uss_payunit",
example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
if statu2:
splitdatelist = []
header = statu2
if len(data)==100000:
print(f"{yue}月 {start_time_r}一次查询数据返回超过十万条，正在进行按时间拆分进行查询,请耐心等待。。。")
splitdatelist.append([f'{start_time_nian}-{str_yue}-{str_start_time_r} 00:00:00', f'{start_time_nian}-{str_yue}-{str_start_time_r} 23:59:59'])
while len(splitdatelist)!=0:
for i in splitdatelist:
zj = splitdate(i[0],i[1])
sqlzhix2 = sql + f""" between '{i[0]}'and '{zj}' """
statu2, data2 = DB_sql(sqlzhix2, database="uss_payunit",
example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
if len(data2) == 100000:
# zj = splitdate(i[0], zj)
splitdatelist.append([i[0],zj])
else:
for valus in data2:
yuedatalen += 1
data_vale.append(valus)
print(f""" between '{i[0]}'and '{zj}' """)
sqlzhix3 = sql + f""" between '{add_one_second(zj)}'and '{i[1]}' """
statu3, data3 = DB_sql(sqlzhix3, database="uss_payunit",
example="生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)")
if len(data3) == 100000:
splitdatelist.append([add_one_second(zj), i[1]])
else:
for valus in data3:
yuedatalen += 1
data_vale.append(valus)
print(f""" between '{add_one_second(zj)}'and '{i[1]}' """)
splitdatelist.remove(i)
else:
for i in data:
# if i not in data_vale:
yuedatalen+=1
data_vale.append(i)
if statu2:
if len(statu2) != len(set(statu2)):
print("列表中存在重复项,写入的表头不允许有重复项")
break
else:
print(yue, start_time_r, data,)
sql = yuanshisql
print(f"{yue}月共查询到{yuedatalen}条数据")
start_time_nian += 1
append_data_to_excel(wirte_path, data_vale, header=header)
print(aa, datetime.now())
