import requests # type: ignore
import json
from datetime import datetime, timedelta
import pandas as pd
import xlsxwriter # type: ignore

import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Border, Side,Alignment,NamedStyle
from openpyxl.drawing.image import Image
from openpyxl.chart import LineChart, Reference, Series
from openpyxl.worksheet.dimensions import ColumnDimension

import schedule # type: ignore
import time
import configparser
import math

from pandas import ExcelWriter
from pandas import ExcelFile


#参考线
Alert=0.0573
Alarm=0.0763
Action=0.1144
Alertneg=-0.0573
Alarmneg=-0.0763
Actionneg=-0.1144
Initial_Date='2025-5-7'
Initial_Reading=0

#数据写入表格并生成曲线图*********************************************************************************************************************************************************************************
def create_excel_with_chart(result_df, result_df1, result_df2,result_df3,output_file,startrowNo):
    print("开始生成报告......")
    result_df['dPitch']=pd.to_numeric(result_df['dPitch'], errors='coerce')
    result_df['dRoll']=pd.to_numeric(result_df['dRoll'], errors='coerce')
    result_df1['dPitch']=pd.to_numeric(result_df1['dPitch'], errors='coerce')
    result_df1['dRoll']=pd.to_numeric(result_df1['dRoll'], errors='coerce')
    result_df2['dPitch']=pd.to_numeric(result_df2['dPitch'], errors='coerce')
    result_df2['dRoll']=pd.to_numeric(result_df2['dRoll'], errors='coerce')
    result_df3['dPitch']=pd.to_numeric(result_df3['dPitch'], errors='coerce')
    result_df3['dRoll']=pd.to_numeric(result_df3['dRoll'], errors='coerce')

    # 创建Excel写入器
    writer = pd.ExcelWriter(output_file, engine='xlsxwriter')
    # 将DataFrame写入Excel
    result_df[['Date','Time']].to_excel(writer, sheet_name='Report', startrow = startrowNo,startcol = 1,index=False)
    # result_df['dPitch']=0
    result_df[['dPitch']].to_excel(writer, sheet_name='Report', startrow = startrowNo,startcol = 3,index=False)
    result_df[['dRoll']].to_excel(writer, sheet_name='Report', startrow = startrowNo,startcol = 4,index=False)
    result_df1[['dPitch']].to_excel(writer, sheet_name='Report', startrow = startrowNo,startcol = 5,index=False)
    result_df1[['dRoll']].to_excel(writer, sheet_name='Report',startrow = startrowNo,startcol = 6,index=False)
    result_df2[['dPitch']].to_excel(writer, sheet_name='Report', startrow = startrowNo,startcol = 7,index=False)   
    result_df2[['dRoll']].to_excel(writer, sheet_name='Report',startrow = startrowNo,startcol = 8,index=False)
    result_df3[['dPitch']].to_excel(writer, sheet_name='Report', startrow = startrowNo,startcol = 9,index=False)
    result_df3[['dRoll']].to_excel(writer, sheet_name='Report',startrow = startrowNo,startcol = 10,index=False)

    # 获取工作簿和工作表对象
    workbook = writer.book
    worksheet = writer.sheets['Report']
    worksheet2 = workbook.add_worksheet('Data')#6条参考线的数据放到Sheet2里面
    worksheet.set_column('D:K', 10)
    worksheet.set_column('B:B', 15)
    worksheet.set_column('C:C', 5) 

    data_format = workbook.add_format({
        'align': 'center',
        'valign': 'vcenter'
    })

     # 其实表中数据已写入，但是to_excel不能居中写入，为了让表格的数据居中，重新写入，带居中格式
    # 应用数据格式
    for row_num in range(len(result_df)):
        cell_value = result_df.iat[row_num, 0]
        worksheet.write(row_num + 26, 0 + 1, str(cell_value), data_format)
    for row_num in range(len(result_df)):
        cell_value = result_df.iat[row_num,1]
        worksheet.write(row_num + 26, 0 + 2, cell_value, data_format)
    for row_num in range(len(result_df)):
        cell_value = result_df.iat[row_num,2]
        if str(cell_value) in ["NaN","nan","NAN"]:#如果有"NaN","nan","NAN"，会报错，替换为none
            cell_value=None
        worksheet.write(row_num + 26, 0 + 4, cell_value, data_format)
    for row_num in range(len(result_df)):
        cell_value = result_df.iat[row_num,3]
        if str(cell_value) in ["NaN","nan","NAN"]:
            cell_value=None
        worksheet.write(row_num + 26, 0 + 3, cell_value, data_format)
    for row_num in range(len(result_df1)):
        cell_value = result_df1.iat[row_num,3]
        if str(cell_value) in ["NaN","nan","NAN"]:
            cell_value=None
        worksheet.write(row_num + 26, 0 + 5, cell_value, data_format)
    for row_num in range(len(result_df1)):
        cell_value = result_df1.iat[row_num,2]
        if str(cell_value) in ["NaN","nan","NAN"]:
            cell_value=None
        worksheet.write(row_num + 26, 0 + 6, cell_value, data_format)
    for row_num in range(len(result_df2)):
        cell_value = result_df2.iat[row_num,3]
        if str(cell_value) in ["NaN","nan","NAN"]:
            cell_value=None
        worksheet.write(row_num + 26, 0 + 7, cell_value, data_format)
    for row_num in range(len(result_df2)):
        cell_value = result_df2.iat[row_num,2]
        if str(cell_value) in ["NaN","nan","NAN"]:
            cell_value=None
        worksheet.write(row_num + 26, 0 + 8, cell_value, data_format)
    for row_num in range(len(result_df3)):
        cell_value = result_df3.iat[row_num,3]
        if str(cell_value) in ["NaN","nan","NAN"]:
            cell_value=None
        worksheet.write(row_num + 26, 0 + 9, cell_value, data_format)
    for row_num in range(len(result_df3)):
        cell_value = result_df3.iat[row_num,2]
        if str(cell_value) in ["NaN","nan","NAN"]:
            cell_value=None
        worksheet.write(row_num + 26, 0 + 10, cell_value, data_format)
    # 将参考线的数据写入到第二张表中，方便引用
    # 循环写入数据到指定范围
    for row in range(27, 56 + 1):
        worksheet2.write(row - 1, 16 - 1, Alert)  # 注意：write方法中的行和列是从0开始计数的
    # 循环写入数据到指定范围
    for row in range(27, 56 + 1):
        worksheet2.write(row - 1, 17 - 1, Alarm)  # 注意：write方法中的行和列是从0开始计数的
    # 循环写入数据到指定范围
    for row in range(27, 56 + 1):
        worksheet2.write(row - 1, 18 - 1, Action)  # 注意：write方法中的行和列是从0开始计数的
    # 循环写入数据到指定范围
    for row in range(27, 56 + 1):
        worksheet2.write(row - 1, 19 - 1, Alertneg)  # 注意：write方法中的行和列是从0开始计数的
    # 循环写入数据到指定范围
    for row in range(27, 56 + 1):
        worksheet2.write(row - 1, 20 - 1, Alarmneg)  # 注意：write方法中的行和列是从0开始计数的
    # 循环写入数据到指定范围
    for row in range(27, 56 + 1):
        worksheet2.write(row - 1, 21 - 1, Actionneg)  # 注意：write方法中的行和列是从0开始计数的
    # worksheet.set_column('P:U', 0) 

    # 创建折线图
    chart = workbook.add_chart({'type': 'line'})
    
    # 配置图表数据
    max_row = len(result_df) +1  # +1因为第一行是标题
    chart.add_series({
        'name': 'TM9',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Report', 1+25, 4, max_row-1+25, 4],      # dPitch列作为Y轴
        'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 0.8},
    })
    chart.add_series({
        'name': 'TM10',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Report', 1+25, 6, max_row-1+25, 6],      # dPitch列作为Y轴
        'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 0.8},        
    })
    chart.add_series({
        'name': 'TM10A',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Report', 1+25, 8, max_row-1+25, 8],      # dPitch列作为Y轴
        'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 0.8},  
    })
    chart.add_series({
        'name': 'TM10B',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Report', 1+25, 10, max_row-1+25, 10],      # dPitch列作为Y轴
        'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 0.8},  
    })
    chart.add_series({
        'name': '',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Data', 1+25, 15, max_row-1+25, 15],      # dPitch列作为Y轴
        # 'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 1.5,'color':'green'},  
    })
    chart.add_series({
        'name': '',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Data', 1+25, 16, max_row-1+25, 16],      # dPitch列作为Y轴
        # 'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 1.5,'color':'yellow'},  
    })
    chart.add_series({
        'name': '',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Data', 1+25, 17, max_row-1+25, 17],      # dPitch列作为Y轴
        # 'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 1.5,'color':'red'},  
    })
    chart.add_series({
        'name': 'Alert',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Data', 1+25, 18, max_row-1+25, 18],      # dPitch列作为Y轴
        # 'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 1.5,'color':'green'},  
    })
    chart.add_series({
        'name': 'Alarm',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Data', 1+25, 19, max_row-1+25, 19],      # dPitch列作为Y轴
        # 'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 1.5,'color':'yellow'},  
    })
    chart.add_series({
        'name': 'Action',
        'categories': ['Report', 1+25, 1, max_row-1+25, 1],  # Date列作为X轴
        'values': ['Data', 1+25, 20, max_row-1+25, 20],      # dPitch列作为Y轴
        # 'marker': {'type': 'circle','size': 4},  # 系列标记
        'line': {'width': 1.5,'color':'red'},  
    })
 
    # 设置图表标题和轴标签
    # chart.set_title({'name': 'dPitch over Time'})
    chart.set_size({'width': 670, 'height': 315})
    chart.set_x_axis({'num_font': {'rotation': -60},'name_font': {'size': 5,},})    
    # chart.set_x_axis({'crossing': 'min'})
    chart.set_y_axis({
        'min':-0.15, 
        'max': 0.15,     
        'crossing': 'min',
        'major_gridlines': {
        'visible': True,
        'line': {'color':'#eee2e1'},
        # 'line_dash_type': 'dash-dot'  # 设置线型为点划线
        },

        })
    # chart.set_y_axis({'crossing': -0.15}) 
    # chart.set_y_axis({'min':-0.15, 'max': 0.15})  # 设置Y轴范围 

    # chart.set_x_axis({'position_axis': 'on_tick', 'crossing': 'min','date_axis': True})
    
    # 设置日期格式的X轴
    # chart.set_x_axis({'date_axis': True})


    # chart.set_legend_options({'delete_series': [4,5,6,7,8,9]}) 
    chart.set_legend({'delete_series': [4,5,6,7,8,9]})
    
    # 将图表插入工作表
    worksheet.insert_chart('B7', chart)


    format1 = {
	 'bold': True,  # 字体加粗
	# 'align': 'center',  # 水平位置设置：居中
	'valign': 'vcenter',  # 垂直位置设置，居中
	'font_size': 8,  # '字体大小设置'
    }
    str_format1 = workbook.add_format(format1) #设置要调整的格式，可参阅链接文章实现更多样式选择
    worksheet.write('B2', 'CRBC - Build King Joint Venture',str_format1)
    worksheet.write('B3', 'MTR Contract 1500',str_format1) 
    worksheet.write('B4', 'TME Stations, Viaducts and River Crossing',str_format1) 
    worksheet.write('B6', 'Wong Chu Road Bridge Tilting Monitoring Record',str_format1) 
    worksheet.write('G3', 'AAA Value (degree)',str_format1)
    worksheet.write('G4', 'Alert',str_format1)
    worksheet.write('G5', 'Alarm',str_format1)
    worksheet.write('G6', 'Action',str_format1)   
    format2 = {
	#  'bold': True,  # 字体加粗
	'align': 'center',  # 水平位置设置：居中
	'valign': 'vcenter',  # 垂直位置设置，居中
	'font_size': 8,  # '字体大小设置'
    }
    str_format2 = workbook.add_format(format2) #设置要调整的格式，可参阅链接文章实现更多样式选择
    worksheet.write('H4', Alert,str_format2)
    worksheet.write('H5',Alarm,str_format2)
    worksheet.write('H6',Action,str_format2)

    format3 = {
	 'bold': True,  # 字体加粗
	'align': 'center',  # 水平位置设置：居中
	'valign': 'vcenter',  # 垂直位置设置，居中
	'font_size': 11,  # '字体大小设置'
    }
    str_format3 = workbook.add_format(format3) #设置要调整的格式，可参阅链接文章实现更多样式选择
    worksheet.merge_range('B25:C25', 'PointID', str_format3)#合并单元格输入
    worksheet.merge_range('D25:E25', 'TM9', str_format3)#合并单元格输入
    worksheet.merge_range('F25:G25', 'TM10', str_format3)#合并单元格输入
    worksheet.merge_range('H25:I25', 'TM10A', str_format3)#合并单元格输入
    worksheet.merge_range('J25:K25', 'TM10B', str_format3)#合并单元格输入
    worksheet.merge_range('L25:M25', 'Remarks', str_format3)#合并单元格输入

    worksheet.merge_range('B24:C24', 'Initial Date', str_format3)#合并单元格输入
    worksheet.merge_range('D24:E24', Initial_Date, str_format3)#合并单元格输入
    worksheet.merge_range('F24:G24', Initial_Date, str_format3)#合并单元格输入
    worksheet.merge_range('H24:I24', Initial_Date, str_format3)#合并单元格输入
    worksheet.merge_range('J24:K24', Initial_Date, str_format3)#合并单元格输入

    worksheet.merge_range('B23:C23', 'Initial Reading', str_format3)#合并单元格输入
    worksheet.merge_range('D23:E23', Initial_Reading, str_format3)#合并单元格输入
    worksheet.merge_range('F23:G23', Initial_Reading, str_format3)#合并单元格输入
    worksheet.merge_range('H23:I23', Initial_Reading, str_format3)#合并单元格输入
    worksheet.merge_range('J23:K23', Initial_Reading, str_format3)#合并单元格输
    worksheet.write('D26','Degree',str_format3)
    worksheet.write('F26','Degree',str_format3)
    worksheet.write('H26','Degree',str_format3)
    worksheet.write('J26','Degree',str_format3)
    worksheet.write('E26','ΔDegree',str_format3)
    worksheet.write('G26','ΔDegree',str_format3)
    worksheet.write('I26','ΔDegree',str_format3)
    worksheet.write('K26','ΔDegree',str_format3)

    worksheet.merge_range('K7:M7',"Site Location", str_format3)#合并单元格
    format4 = {
	 'border': 1,  # 字体加粗
    }
    cell_format = workbook.add_format(format4)  # 细边框
    worksheet.conditional_format('B23:M56', {'type': 'no_errors', 'format': cell_format})
    worksheet.conditional_format('K7:M20', {'type': 'no_errors', 'format': cell_format})
    worksheet.conditional_format('G3:H6', {'type': 'no_errors', 'format': cell_format})

    worksheet.insert_image('K8', './reportservice/location.jpg', {
                            'x_scale': 1.02,  # 水平缩放50%
                                'y_scale': 1.2   # 垂直缩放30%
                            })  # 在B2单元格插入图片
    worksheet.insert_image('J2', './reportservice/logo.png', {
                            'x_scale':1,  # 水平缩放50%
                                'y_scale': 1.08   # 垂直缩放30%
                            })  # 在B2单元格插入图片
    
    # 用openpyxl读取数据
    # wb = load_workbook(output_file)
    # ws = wb.active
    # 获取矩形区域数据
    # data = []
    # for row in ws['B27':'M56']:
    #     row_data = [cell.value for cell in row]
    #     data.append(row_data)
    # # 设置居中格式
    # center_format = workbook.add_format({'align': 'center'})
    # # 写入数据并应用格式
    # for row_num, row_data in enumerate(data):
    #     for col_num, cell_data in enumerate(row_data):
    #         worksheet.write(row_num, col_num, cell_data, center_format)
    # wb.close()

    # workbook.close()
  







    # 保存Excel文件
    writer.close()


    # # 1. 加载现有工作簿
    # wb = load_workbook(output_file)  # 替换为实际文件名
    # # wb.read_excel('your_file.xls', engine='xlrd')
    # ws = wb.active  # 默认操作当前活动工作表

    # # wb = xlrd.open_workbook(output_file)
    # # ws = wb.sheet_by_index(0)

    # # 2. 定义边框样式（可自定义）
    # thin_border = Border(
    #     left=Side(style='thin'),
    #     right=Side(style='thin'),
    #     top=Side(style='thin'),
    #     bottom=Side(style='thin')
    # )

    # # 3. 设置矩形区域边框（示例：B2:D10）
    # target_range = 'B26:M56'
    # for row in ws[target_range]:
    #     for cell in row:
    #         cell.border = thin_border
    # target_range = 'G3:H6'
    # for row in ws[target_range]:
    #     for cell in row:
    #         cell.border = thin_border
    # target_range = 'B23:M25'
    # for row in ws[target_range]:
    #     for cell in row:
    #         cell.border = thin_border
    # target_range = 'K7:M20'
    # for row in ws[target_range]:
    #     for cell in row:
    #         cell.border = thin_border
    
    
    # # # 设置连续单元格的范围（例如 A1:B2）
    # # cell_range = "B25:M56"
    # # start_col, start_row = openpyxl.utils.coordinate_to_tuple(cell_range.split(':')[0])
    # # end_col, end_row = openpyxl.utils.coordinate_to_tuple(cell_range.split(':')[1])
    
    # # 创建一个对齐对象，并设置居中对齐
    # alignment = Alignment(horizontal='center', vertical='center')
    
    # # 迭代范围内的每个单元格并应用对齐方式
    # for col in range(1, 14):
    #     for row in range(27, 57):
    #         cell = ws.cell(row=row, column=col)
    #         cell.alignment = alignment

    # # 插入logo图片
    # img = Image('../reportservice/logo.png')
    # ws.add_image(img, 'J2')
    # img = Image('../reportservice/location.jpg')
    # img.width, img.height = 1.15*img.width, 1.2*img.height  # 将图片大小放大
    # ws.add_image(img, 'K8')

    # # #这3列放Alert、Alarm、Action数据用于生成折线图的警戒线
    # # ws.column_dimensions.group("P", hidden=True)  # 隐藏列
    # # ws.column_dimensions.group("Q", hidden=True)  
    # # ws.column_dimensions.group("R", hidden=True)  

    # # 4. 保存修改（可另存为新文件）
    # wb.save(output_file)
    # # data = pd.read_excel(output_file, engine='openpyxl')
    # # data.to_excel(output_file, index=False, engine='xlwt')
    # # 读取xlsx文件
    # df = pd.read_excel(output_file, engine='openpyxl')
    
    # # # 将DataFrame写入xls文件
    # with ExcelWriter('output.xls', engine='xlwt') as writer:
    #     df.to_excel(writer, index=False)
    print("生成报告完成！")

#登录获取token***********************************************************************************************************************************************************************************************
def  login():
    url = "https://iot.raytue.com/prod-api/login"
    payload = {
      "username": "admin",
      "password": "@raytue123",
      "sourceType": 1
    }
    headers = {
        'Content-Type': 'application/json'
    }
    # 发送POST请求
    response = requests.post(url,  headers=headers, data=json.dumps(payload))

    # 检查请求是否成功
    if response.status_code == 200:
        # 解析并打印返回的数据
        data = response.json()
        # print(json.dumps(data, indent=4))
        print("请求token成功！")
        token= data["token"]  
        return token      
    else:
        # 打印错误信息
        print(f"请求失败，状态码：{response.status_code}，响应内容：{response.text}")
        return None

#************获取历史数据，字典字符数组输出*****************************************************************************************************************************************************************************************
def getResponseTilt(token,url,deviceId,serialNumber,beginTime,endTime):
  beginTime = str(beginTime)
  endTime = str(endTime)
  # 定义请求的载荷
  payload ={
    "deviceId": deviceId,
    "serialNumber": serialNumber,
    "identifierList": [
      {
        "identifier": "tiltData_time",
        "type": 1
      },
      {
        "identifier": "tiltData_dRoll",
        "type": 1
      },
      {
        "identifier": "tiltData_dPitch",
        "type": 1
      }
    ],
  "beginTime": beginTime,
  "endTime": endTime
}
  # 设置请求头，包括Authorization
  headers = {
      'Authorization': token,
      'Content-Type': 'application/json'
  }

  # 发送POST请求
  response = requests.post(url, headers=headers, data=json.dumps(payload))

  # 检查请求是否成功
  if response.status_code == 200:
      # 解析并打印返回的数据
      data1 = response.json()
      # print(json.dumps(data1, indent=4))
      print(f"成功获取设备历史数据,deviceId:{serialNumber}")
  else:
      # 打印错误信息
      print(f"请求失败，状态码：{response.status_code}，响应内容：{response.text}")

  original_json = json.dumps(data1, indent=4)
  # 将JSON字符串转换为Python字典
  data_dict = json.loads(original_json)

  # 初始化tiltData数组
  tilt_data_array = []

  # 遍历data数组
  for item in data_dict['data']:
      # 获取时间戳作为键（这里我们不需要它作为最终输出的一部分，但用它来访问内部列表）
      timestamp_key = list(item.keys())[0]
      # 获取与该时间戳关联的数据列表
      data_list = item[timestamp_key]
      
      # 初始化一个空字典来存储组合后的数据
      combined_data = {}
      
      # 遍历数据列表，将相关数据合并到字典中
      for data in data_list:
          for key, value in data.items():
              combined_data[key] = value
      
      # 将组合后的数据字典添加到tiltData数组中
      tilt_data_array.append(combined_data)
  return tilt_data_array

#********************筛选30天内10:30的数据，dataframe格式输出******************************************************************************************************************************************************************************
def getdfData(tilt_data_array):
    
  tilt_data = json.dumps(tilt_data_array)

  # 转换为DataFrame
  df = pd.DataFrame(json.loads(tilt_data))
  #print(df['tiltData_time'])

  # 将字符串时间转换为datetime对象
  df['tiltData_time'] =pd.to_datetime( df['tiltData_time'].str.slice(stop=-6))

  # 获取当前日期和时间
  current_time = datetime.now()

  # print(current_time)

  # 计算前30天的日期列表
  dates = [current_time - timedelta(days=i) for i in range(1, 31)]

  # 初始化一个空的DataFrame来存储结果
  result_df = pd.DataFrame(columns=['Date', 'Time', 'dRoll', 'dPitch'])

  # 遍历前30天的日期
  for date in dates:
      # 获取当天上午的数据
      # print("--------------------------------------------------------------------")
      # print(date.date())

      day_data = df[(df['tiltData_time'].dt.date == date.date()) & (df['tiltData_time'].dt.hour < 12)]
      # print(day_data)
      # print("--------------------------------------------------------------------")
      # print(day_data)
      # 如果当天有数据，则找到最接近10点30分的数据
      if not day_data.empty:
          # 计算每个数据与10点30分的时间差
          time_diffs = abs(day_data['tiltData_time']- pd.to_datetime(f'{date.date()} 10:30:00'))
          # print("--------------------------------------------------------------------")
          # print(time_diffs.idxmin())
      #     # 找到时间差最小的数据
          closest_data = df.iloc[time_diffs.idxmin()]
          # print(closest_data)
          # 将数据添加到结果DataFrame中
          result_df = result_df._append({
              'Date':(closest_data['tiltData_time'].date()),
              # 'Time': closest_data['tiltData_time'].time(),
              'Time': 'AM',
              'dRoll': closest_data['tiltData_dRoll'],
            #   'dPitch': closest_data['tiltData_dPitch']#表格这一列固定为0，所以下一句代替
              'dPitch': 0
          }, ignore_index=True)
      else:
          result_df = result_df._append({#当天没有数据的，取值为空
              'Date':date.date(),
              # 'Time': '10:30:00',
              'Time': 'AM',
              'dRoll': '',
              'dPitch':''
          }, ignore_index=True)


  # 按照时间从小到大排序
  result_df = result_df.sort_values(by=['Date', 'Time'])
  return result_df


#**********main()*******************************************************************************************************************************************************************************************


def job():
    #登录获取token
    token = login()

    #历史数据接口地址
    url = "https://iot.raytue.com/prod-api/data/center/deviceHistory"

    #获取传感器数据字典格式数组
    tilt_data_array_TM9 = getResponseTilt(token,url,277,"D23ANR8041JQI",(datetime.now()-timedelta(31)).strftime("%Y-%m-%d %H:%M:%S"),datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    #转换成dataframe数据
    result_df_TM9 =getdfData(tilt_data_array_TM9)

    tilt_data_array_TM10 = getResponseTilt(token,url,278,"D2367JBEYPY32",(datetime.now()-timedelta(31)).strftime("%Y-%m-%d %H:%M:%S"),datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    result_df_TM10 =getdfData(tilt_data_array_TM10)

    tilt_data_array_TM10A = getResponseTilt(token,url,279,"D23AFZM72Q620",(datetime.now()-timedelta(31)).strftime("%Y-%m-%d %H:%M:%S"),datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    result_df_TM10A =getdfData(tilt_data_array_TM10A)

    tilt_data_array_TM10B = getResponseTilt(token,url,280,"D2368757I1Y4G",(datetime.now()-timedelta(31)).strftime("%Y-%m-%d %H:%M:%S"),datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    result_df_TM10B =getdfData(tilt_data_array_TM10B)

    # print(result_df_TM10B)

    #生成当天日期命名的文件名
    reportname ='./report/tilt_report_'+ str(datetime.now().date()).replace("-", "") + '.xlsx'

    # # 将结果保存到Excel表格中
    # result_df.to_excel(reportname, index=False)

    # print(f"数据已保存到{reportname}文件中。")

    #生成报告
    create_excel_with_chart(result_df_TM9,result_df_TM10,result_df_TM10A,result_df_TM10B,reportname,25)

# job()
# 每天上午8点执行job函数
# 创建ConfigParser对象并读取INI文件
config = configparser.ConfigParser()
config.read('./config.ini')  # 确保路径正确，替换为你的文件名    
TILT_REPORT_TIME= config['REPORTTIME']['TILT_REPORT_TIME']
# print(TILT_REPORT_TIME)
print("开启tilt报告服务！")
schedule.every().day.at(TILT_REPORT_TIME).do(job)
 
while True:
    schedule.run_pending()
    time.sleep(60)

