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
from openpyxl.drawing.image import Image
from openpyxl.chart import LineChart, Reference, Series
from openpyxl.worksheet.dimensions import ColumnDimension

#参考线
Alert=0.0573
Alarm=0.0763
Action=0.1144
Alertneg=-0.0573
Alarmneg=-0.0763
Actionneg=-0.1144


#数据写入表格并生成曲线图*********************************************************************************************************************************************************************************
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.to_excel(writer, sheet_name='Report', startrow = startrowNo,startcol = 1,index=False)
    result_df1[['dRoll','dPitch']].to_excel(writer, sheet_name='Report',startrow = startrowNo,startcol = 5,index=False)
    result_df2[['dRoll','dPitch']].to_excel(writer, sheet_name='Report',startrow = startrowNo,startcol = 7,index=False)
    result_df3[['dRoll','dPitch']].to_excel(writer, sheet_name='Report',startrow = startrowNo,startcol = 9,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) 


    # 循环写入数据到指定范围
    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': 'Alert',
        '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': 'Alarm',
        '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': 'Action',
        '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_y_axis({'crossing': 'min',})
    chart.set_size({'width': 880, 'height': 320})
    # chart.set_x_axis({'crossing': 'min'})
    chart.set_y_axis({
        'crossing': 'min',
        'major_gridlines': {
        'visible': True,
        'line': {'color':'#eee2e1'},
        # 'line_dash_type': 'dash-dot'  # 设置线型为点划线
    }})
    # chart.set_x_axis({'position_axis': 'on_tick', 'crossing': 'min','date_axis': True})
    
    # 设置日期格式的X轴
    # chart.set_x_axis({'date_axis': True})
    
    # 将图表插入工作表
    worksheet.insert_chart('B9', chart)


    format1 = {
	 'bold': True,  # 字体加粗
	# 'align': 'center',  # 水平位置设置：居中
	'valign': 'vcenter',  # 垂直位置设置，居中
	'font_size': 8,  # '字体大小设置'
    }
    str_format1 = workbook.add_format(format1) #设置要调整的格式，可参阅链接文章实现更多样式选择
    worksheet.write('B4', 'CRBC - Build King Joint Venture',str_format1)
    worksheet.write('B5', 'MTR Contract 1500',str_format1) 
    worksheet.write('B6', 'TME Stations, Viaducts and River Crossing',str_format1) 
    worksheet.write('B8', 'Tilting Monitoring Record - A16 Rock Filling Works',str_format1) 
    worksheet.write('G5', 'AAA Value (degree)',str_format1)
    worksheet.write('G6', 'Alert',str_format1)
    worksheet.write('G7', 'Alarm',str_format1)
    worksheet.write('G8', 'Action',str_format1)   
    format2 = {
	#  'bold': True,  # 字体加粗
	'align': 'center',  # 水平位置设置：居中
	'valign': 'vcenter',  # 垂直位置设置，居中
	'font_size': 8,  # '字体大小设置'
    }
    str_format2 = workbook.add_format(format2) #设置要调整的格式，可参阅链接文章实现更多样式选择
    worksheet.write('H6', Alert,str_format2)
    worksheet.write('H7',Alarm,str_format2)
    worksheet.write('H8',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)#合并单元格输入

    # 保存Excel文件
    writer.close()


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

    # 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 = 'G5:H8'
    for row in ws[target_range]:
        for cell in row:
            cell.border = thin_border
    target_range = 'B25:M25'
    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('logo.png')
    ws.add_image(img, 'J3')

    # #这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)
    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']
          }, ignore_index=True)
      else:
          result_df = result_df._append({#当天没有数据的，取值为空
              'Date':date.date(),
              # 'Time': '10:30:00',
              'Time': 'AM',
              'dRoll': None,
              'dPitch': None
          }, ignore_index=True)


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


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

#登录获取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'+ 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)


