python_utils/dify日志导出后端/xiaoshi_AI_userRecord_api.py

from flask import Flask, request, jsonify, Response,send_file,abort
import os
import requests
from requests.exceptions import HTTPError
import pymysql
import json
from dateutil import parser
from datetime import datetime, timedelta
import pytz
import random
import string
import time

from openpyxl import Workbook
from openpyxl.chart import BarChart,LineChart, Reference
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.utils import get_column_letter

# 定时
from apscheduler.schedulers.blocking import BlockingScheduler

from flask_cors import CORS
app = Flask(__name__)
CORS(app)



class Response1:
    def success(self,data,message='请求成功'):
        return {
            "code":200,
            "data":data,
            "message":message
        }

    def error(self,message='系统异常'):
        return {
            "code":500,
            "data":None,
            "message":message
        }
        
    def any(self,code,data,message):
        return {
            "code":code,
            "data":data,
            "message":message
        }

requestResponse = Response1()

file_prefix = 'file'

workFlow_logs = {
    'f815a6f2-4f8d-482c-b214-b8fd437ef12d':{
        "type":1,
        'name':'权利要求解释及有益效果'
    },
    '01f7efbf-d23f-49f9-8ee4-740892c4f6a5':{
        "type":2,
        'name':'OA答辩',
        'emails':['gaochangkui@china-wispro.com']
    },
    'a30f1218-bc9d-4f2a-985a-2386671a91dc':{
        "type":3,
        'name':'生成说明书',
        'emails':['lirenjie@china-wispro.com']
    },
    '1ca8efd1-b24a-4b7f-8853-001663bb56dc':{
        "type":2,
        'name':'OA答辩',
        'emails':['gaochangkui@china-wispro.com']
    }
}

chatFlow_logs = {
    '7f7812ce-89f4-4981-a55c-efe2e016cb36':{
        'name':'技术交底书理解',
        'emails':['lirenjie@china-wispro.com']
    },
    
}

#时间格式转换
def formateDate(time_str="Thu, 07 Mar 2024 18:36:18 GMT",time_format = "%a, %d %b %Y %H:%M:%S GMT",formate='%Y-%m-%d %H:%M:%S'):
    # 解析时间字符串为datetime对象，并设置时区为GMT
    dt = ''
    if isinstance(time_str, datetime):
        dt = time_str
    else:
        dt = datetime.strptime(time_str, time_format)
    # 转换为所需的日期格式 YYYY-mm-dd
    formatted_date = dt.strftime(formate)
    return formatted_date


def readAndWritePerson(users,userRecode_first,userRecode_end,useRecord_list,requestData):
    difyId = requestData.get('difyId')
    folder = getFilePath(difyId)
    json_file_path = f'{file_prefix}\{folder}\person-{folder}.json'
    personData = []
    data = {}
    removePerson = []
    useRecord = {}
    try:
        with open(json_file_path,'r',encoding='utf-8') as json_file:
            data = json.load(json_file)

    except FileNotFoundError:
        print(f"文件 {json_file_path} 未找到。")
    except json.JSONDecodeError:
        print(f"文件 {json_file_path} 不是有效的 JSON 格式。")
    except IOError as e:
        print(f"发生I/O错误: {e}")
    finally:
        personData = data.get('persons',[])
        removePerson = data.get('removePerson',["1","323","103","328"])
        useRecord = data.get('useRecord',{})
        # yesterday = formateDate(datetime.now() - timedelta(days=1),"%a, %d %b %Y %H:%M:%S GMT",'%Y-%m-%d')
        # yesterday = formateDate(requestData.get('startTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d')
        # useRecord[yesterday] = []

        for key,value in useRecord_list.items():
            if key in useRecord:
                continue
            else:
                useRecord[key] = value

        for user in users:
            id = str(user['id'])
            if id in removePerson:
                continue
            # if(user['personnelName']):
            #     useRecord[yesterday].append(user['personnelName'])
            # else:
            #     useRecord[yesterday].append(user['personnelUserName'])
            person_id = ''
            for person in personData:
                if(person['id'] == id):
                    person_id = person['id']
                    break
            if person_id:
                person['lastUseTime'] = userRecode_end[id]
            else:
                obj = {
                    "name":user['personnelName'],
                    "username":user['personnelUserName'],
                    "id":id,
                    "firstUseTime":userRecode_first[id],
                    "lastUseTime":userRecode_end[id],
                    "departmentId":user.get('departmentId',''),
                    "departmentName":user.get('departmentName',''),
                }
                personData.append(obj)
        data['persons'] = personData
        data['useRecord'] = useRecord

        with open(json_file_path, 'w', encoding='utf-8') as json_file:
            # 将 Python 对象转换为 JSON 格式并写入文件
            json.dump(data, json_file, ensure_ascii=False, indent=4)
    return personData

# 查询今日会话情况（调用dify日志）
def fetch_data(page_size,data):
    token = data.get('token')
    difyId = data.get('difyId')
    headers={
        'Content-Type': 'application/json',
        'Authorization':f"Bearer {token}"
    }
    params1 = {
        "page": page_size,
        "limit": 10,
        "start": formateDate(data.get('startTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d %H:%M'),#时间需要修改
        "end": formateDate(data.get('endTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d %H:%M'),#时间需要修改
        "sort_by": "created_at",
        "annotation_status": "all"
    }
    url = f"http://192.168.2.24/console/api/apps/{difyId}/chat-conversations"
    response = requests.get(url, headers=headers,params=params1)
    response.raise_for_status()
    return response.json()


def getChatMessageRecord(conversation_id,data):
    token = data.get('token')
    difyId = data.get('difyId')
    headers={
        'Content-Type': 'application/json',
        'Authorization':f"Bearer {token}"
    }
    url = f"http://192.168.2.24/console/api/apps/{difyId}/chat-messages"
    params1 = {
        "limit":10,
        "conversation_id":conversation_id
    }
    response = requests.get(url, headers=headers,params=params1)
    response.raise_for_status()
    return response.json()


# 获取所有人员信息
def get_personByIds(ids):
    url = "http://47.116.194.135:8880/permission/api/system/getPersonnelByIds"
    params1 = {
        "ids":ids
    }
    headers={
        'Content-Type': 'application/json',
    }
    response = requests.post(url, headers=headers,data=json.dumps(ids))
    response.raise_for_status()
    return response.json()


# 获取两个日期之间的所有日期
def get_dates_between(start_date_str, end_date_str, date_format='%Y-%m-%d'):
    # 将字符串转换为 datetime 对象
    start_date = datetime.strptime(start_date_str, date_format)
    end_date = datetime.strptime(end_date_str, date_format)
    
    # 初始化一个空列表来存储日期
    date_list = []
    
    # 使用一个循环来生成日期范围
    current_date = start_date
    while current_date <= end_date:
        date_list.append(current_date.strftime(date_format))
        current_date += timedelta(days=1)
    
    return date_list


# 获取活跃用户数数据
def getActiveUser(requestData,data):
    sheetData = []
    # 写入表头
    sheetData.append(['时间','人数','人员'])
    useRecord = data.get('useRecord',{})
    for time, users in useRecord.items(): 
        user_len = len(users)
        if user_len != 0:
            sheetData.append([time,user_len,','.join(users)])
    
    return sheetData


#获取对话记录数据
def getChatRecord(requestData,data):
    sheetData = []
    # 写入表头
    sheetData.append(['序号','姓名','所属部门','提交文件','问题','答案','结果文件','时间'])

    # 写入数据
    num = 1
    for item in data:
        body = [num]
        fieldList = ['name','departmentName','file','input','answer','resultFile','time']
        for field in fieldList:
            if field in item:
                body.append(item[field])
            else:
                if field == 'file' or field == 'resultFile':
                    body.append(
                        {
                            'guid':'',
                            'originalName':''
                        }
                    )
                else:
                    body.append('')
        sheetData.append(body)
        num = num + 1
    return sheetData


# 分组
def groupByField(data,field='id',fun=None):
    groupData = {}
    for item in data:
        value = ''
        if fun:
            value = fun(item[field])
        else:
            if field in item:
                value = item[field]
            else:
                value = "UnKnow"
        if value in groupData:
            groupData[value]['num'] = groupData[value]['num'] + 1
        else:
            groupData[value] = {}
            groupData[value]['data'] = []
            groupData[value]['num'] = 1
        groupData[value]['data'].append(item)
    return groupData

# 时间转日期
def timeToDate(time):
    date = formateDate(time,'%Y-%m-%d %H:%M:%S','%Y-%m-%d')
    return date

# 新增新用户
def getNewUserNum(requestData,data):
    sheetData = []
    # 写入表头
    sheetData.append(['时间','新用户数量','人员'])
    persons = data.get('persons',[])
    obj = groupByField(persons,'firstUseTime',timeToDate)
    for key ,value in obj.items():
        num = value['num']
        data2 = value['data']
        user = [person['name'] or person['username']  for person in data2]
        sheetData.append([key,num,','.join(user)])

    return sheetData


#获取两个日期之间的间隔天数
def getBetweenDay(date_str1,date_str2):
    date1 = datetime.strptime(date_str1, "%Y-%m-%d")
    date2 = datetime.strptime(date_str2, "%Y-%m-%d")
    
    # 计算两个日期之间的差异
    delta = date2 - date1
    
    # 获取间隔天数
    days_between = delta.days
    return days_between

#用户存活率
def getUserActive(requestData,data):
    sheetData = []
    # 写入表头
    sheetData.append(['未使用时长','数量','人员'])
    persons = data.get('persons',[])
    obj = groupByField(persons,'lastUseTime',timeToDate)
    today = formateDate(datetime.now() - timedelta(days=1),"%a, %d %b %Y %H:%M:%S GMT",'%Y-%m-%d')
    for key ,value in obj.items():
        num = value['num']
        data2 = value['data']
        user = [person['name'] or person['username']  for person in data2]
        betweenDay = getBetweenDay(key,today)
        sheetData.append([betweenDay,num,','.join(user)])
    return sheetData

# 获取用户使用天数
def getUseDays(data):
    useDays = {}
    for key,value in data.items():
        if len(value)>0:
            for item in value:
                if item in useDays:
                    useDays[item] += 1
                else:
                    useDays[item] = 1
    return useDays

# 用户使用情况
def getUserUsage(requestData,data):
    sheetData = []
    # 写入表头
    sheetData.append(['姓名','部门','首次使用时间','最后一次使用时间','使用天数'])
    persons = data.get('persons',[])
    useRecord = data.get('useRecord',{})
    useDays = getUseDays(useRecord)
    for person in persons:
        sheetData.append([person['name'],person['departmentName'],person['firstUseTime'],person['lastUseTime'],useDays[person['name']]])
    return sheetData


# 部门使用情况
def getDepartmentUsage(requestData,data):
    sheetData = []
    persons = data.get('persons',[])
    # 写入表头
    sheetData.append(['部门','部长','副部长','使用人数','人员'])
    departmentMessage=[]
    # 读取部门信息
    json_file_path = f'departmentMessage.json'
    try:
        with open(json_file_path,'r',encoding='utf-8') as json_file:
            fileData = json.load(json_file)
            departmentMessage = fileData.get('data',[])
    except FileNotFoundError:
        print(f"文件 {json_file_path} 未找到。")
    except json.JSONDecodeError:
        print(f"文件 {json_file_path} 不是有效的 JSON 格式。")
    except IOError as e:
        print(f"发生I/O错误: {e}")
    
    obj = groupByField(persons,'departmentId')
    for department in departmentMessage:
        departmentId = department['departmentId']
        users = []
        num = 0
        if departmentId in obj:
            department_obj = obj[departmentId]
            num = department_obj['num']
            department_data = department_obj['data']
            users = [person['name'] or person['username']  for person in department_data]
        sheetData.append([department['departmentName'],department['minister'],department['deputyMinister'],num,','.join(users)])
    return sheetData


# 各分所使用情况
def getAreaUsage(requestData,data):
    sheetData = []
    persons = data.get('persons',[])
    # 写入表头
    sheetData.append(['分所','总人数','使用人数','人员'])
    person_area = {}
    json_file_path = f'personnel_area.json'
    try:
        with open(json_file_path,'r',encoding='utf-8') as json_file:
            person_area = json.load(json_file)
    except FileNotFoundError:
        print(f"文件 {json_file_path} 未找到。")
    except json.JSONDecodeError:
        print(f"文件 {json_file_path} 不是有效的 JSON 格式。")
    except IOError as e:
        print(f"发生I/O错误: {e}")

    if not person_area:
        return sheetData
    person_area_data = person_area['data']
    # person_area_areas = person_area['areas']
    person_area_areas = set()
    person_area_data_nums = {}
    # 获取每个分所的人数
    for key,value in person_area_data.items():
        person_area_areas.add(value)
        if value in person_area_data_nums:
            person_area_data_nums[value] += 1
        else:
            person_area_data_nums[value] = 1
    for item in persons:
        person_id = item['id']
        if person_id in person_area_data:
            item['area'] = person_area_data[str(person_id)]
    obj = groupByField(persons,'area')
    
    for item in person_area_areas:
        num = 0
        area_name = item
        users = []
        if area_name in obj:
            obj_value = obj[area_name]
            num = obj_value['num']
            obj_data = obj_value['data']
            users = [person['name'] or person['username']  for person in obj_data]
        sheetData.append([area_name,person_area_data_nums[area_name] or 0,num,','.join(users)])
    # for key,value in obj.items():
    #     num = value['num']
    #     obj_data = value['data']
    #     users = [person['name'] or person['username']  for person in obj_data]
    #     sheetData.append([key,num,','.join(users)])
    return sheetData



# 导出excel
def exportExcel(chat_message_records,requestData,time_obj):
    difyId = requestData.get('difyId')
    # now_time = formateDate(datetime.now(),"%a, %d %b %Y %H:%M:%S GMT",'%Y-%m-%d')
    # 创建一个新的工作簿和工作表
    wb = Workbook()
    ws = wb.active

    # 删除原工作表
    if "Sheet" in wb.sheetnames:
        default_sheet = wb["Sheet"]
        wb.remove(default_sheet)


    #查询当前人员数据
    person_data = {}
    folder = getFilePath(difyId)
    json_file_path = f'{file_prefix}\{folder}\person-{folder}.json'
    try:
        with open(json_file_path,'r',encoding='utf-8') as json_file:
            person_data = json.load(json_file)
    except FileNotFoundError:
        print(f"文件 {json_file_path} 未找到。")
    except json.JSONDecodeError:
        print(f"文件 {json_file_path} 不是有效的 JSON 格式。")
    except IOError as e:
        print(f"发生I/O错误: {e}")

    data_for_sheets = {
        "活跃用户数":{
            "type":LineChart,
            "data":[],
            "fun":getActiveUser,
            "params":person_data
        },
        "新增新用户":{
            "type":LineChart,
            "data":[],
            "fun":getNewUserNum,
            "params":person_data
        },
        "用户死亡率":{
            "type":LineChart,
            "data":[],
            "fun":getUserActive,
            "params":person_data
        },
        "用户使用情况":{
            "type":'default',
            "data":[],
            "fun":getUserUsage,
            "params":person_data
        },
        "部门使用情况":{
            "type":'default',
            "data":[],
            "fun":getDepartmentUsage,
            "params":person_data
        },
        "分所使用情况":{
            "type":'default',
            "data":[],
            "fun":getAreaUsage,
            "params":person_data
        },
        "对话记录":{
            "type":'default',
            "data":[],
            "fun":getChatRecord,
            "params":chat_message_records,
            "file_index":4,
            "result_file_index":7
        }
    }

    hyperlink_font = Font(color="0000FF", underline="single")
    header_fill = PatternFill(start_color="8DB4E2", end_color="8DB4E2", fill_type="solid")
    download_file_url = 'https://xsip.cn/api/fileManager/downloadFile?fileId='
    # 向每个工作表插入数据
    for sheet_name, data in data_for_sheets.items():
        # 如果工作表不存在，则创建它
        sheet = None
        if sheet_name not in wb.sheetnames:
            sheet = wb.create_sheet(title=sheet_name)
        else:
            sheet = wb[sheet_name]
        
        if "fun" in data:
            if "params" in data:
                data['data'] = data['fun'](requestData,data['params'])
            else:
                data['data'] = data['fun'](requestData)
        # 写入数据到工作表
        for row_idx, row in enumerate(data['data'], start=1):  # start=1 因为 Excel 行号从 1 开始
            for col_idx, value in enumerate(row, start=1):
                file_index = None
                result_file_index = None
                if 'file_index' in data:
                    file_index = data['file_index']
                if 'result_file_index' in data:
                    result_file_index = data['result_file_index']
                if row_idx != 1 and (col_idx == file_index or col_idx == result_file_index):#文件
                    if 'originalName' in value and value['originalName']:
                        link_cell = sheet.cell(row=row_idx, column=col_idx, value=value['originalName'])
                        link_cell.hyperlink = download_file_url + value['guid']
                        link_cell.font = hyperlink_font
                    else:
                        sheet.cell(row=row_idx, column=col_idx, value='')
                elif row_idx == 1:
                    cell = sheet.cell(row=row_idx, column=col_idx, value=value)
                    cell.alignment = Alignment(horizontal='center')
                    cell.fill = header_fill
                else:
                    sheet.cell(row=row_idx, column=col_idx, value=value)
        # 设置单元格垂直居中
        sheet.freeze_panes = 'A2'
        for row in sheet.iter_rows():
            for cell in row:
                cell.alignment = Alignment(vertical='center')
        # 设置行高为45        
        for row in range(1, sheet.max_row + 1):
            sheet.row_dimensions[row].height = 45
        # 设置列宽自适应
        if sheet_name != "对话记录":
            for col in sheet.columns:
                max_length = 10
                column = col[0].column_letter  # 获取列字母
                for cell in col:
                    try:
                        if len(str(cell.value)) > max_length:
                            max_length = len(str(cell.value))
                    except:
                        pass
                adjusted_width = (max_length * 2)
                sheet.column_dimensions[column].width = adjusted_width
        if data['type'] != 'default':
            type = data.get('type','BarChart')
            try:
                chart = type()
                length = len(data['data'])
                # 定义数据范围（不包括标题行）
                data_values_rang = f"B2:B{length}"
                if(length == 2):
                    data_values_rang = f"B2"
                
                data_values = Reference(sheet, range_string=f"{sheet_name}!{data_values_rang}")
                # data_values = Reference(sheet, min_col=2, min_row=2, max_col=2, max_row=length)
                # categories = Reference(sheet, min_col=1, min_row=2, max_row=length)
                categories_rang = f"A2:A{length}"
                if(length == 2):
                    categories_rang = f"A2"
                
                categories = Reference(sheet, range_string=f"{sheet_name}!{categories_rang}")

                # 将数据和类别添加到图表
                chart.add_data(data_values, titles_from_data=False)  # 如果数据有标题行，可以设置为 True
                chart.set_categories(categories)

                # 可选：设置图表标题和轴标签
                chart.title = data.get('title','')
                chart.x_axis.title = data.get('x','')
                chart.y_axis.title = data.get('y','')

                # 将图表添加到工作表（指定位置，例如从 E1 开始）
                lei = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
                first_data = data['data'][0]
                first_data_len = len(first_data)
                zimu = lei[first_data_len + 1]
                sheet.add_chart(chart, f"{zimu}2")
            except Exception as e:
                print(e)

    # 保存工作簿
    # file_name = f'{now_time}-{difyId}-使用情况.xlsx'
    file_name = time_obj['file_name']
    wb.save(file_name)
    return file_name



def getDifyRecord1(data,time_obj):
    difyId = data.get('difyId')
    results = []
     # 获取时间范围内提问的人员以及首次提问时间和最后一次提问时间
    unique_userId = set()
    userRecode_first = {}#每个人第一次一次使用时间
    userRecode_end = {}#每个人最后一次使用时间
    if difyId not in workFlow_logs:
        # 查询时间范围内所有的提问记录
        page = 0
        currentResult = {
            'has_more': True,
        }
        while True:
            if currentResult.get('has_more') != True:
                break
            # 发送请求
            page = page + 1
            try:
                currentResult = fetch_data(page,data)
                results.extend(currentResult.get('data'))
            finally:
                print('获取日志')
        
        for item in results:
            if 'from_end_user_session_id' in item:  # 确保字典中有 'from_end_user_session_id' 键
                id = item['from_end_user_session_id']
                created_time = datetime.fromtimestamp(item['created_at'])
                unique_userId.add(id)
                if id in userRecode_first:
                    userRecode_end[id] = formateDate(created_time)
                else:
                    userRecode_first[id] = formateDate(created_time)
                    userRecode_end[id] = formateDate(created_time)
    else:
        currentWorkFlow = workFlow_logs[difyId]
        params = {
            "startTime":data['startTime'],
            "endTime":data['endTime'],
            "type":currentWorkFlow['type'],
        }
        results = getSession(params)
        for item in results:
            id = item['from_end_user_session_id']
            created_time = item['time']
            unique_userId.add(id)
            if id in userRecode_first:
                userRecode_end[id] = created_time
            else:
                userRecode_first[id] = created_time
                userRecode_end[id] = created_time
    # 获取人员信息并保存
    unique_userId_List = list(unique_userId)
    persons = get_personByIds(unique_userId_List)
    departmentList = getDepartmentByPersonIds(unique_userId)
    persons_obj = {}
    person_dep = {}
    if(persons.get('code') == 200):
        persons_list = persons.get('data')

        # 获取人员部门
        if len(departmentList):
            for person in persons_list:
                personId = person['id']
                for department in departmentList:
                    if personId == department['personId']:
                        person['departmentName'] = department['departmentName']
                        person['departmentId'] = department['departmentId']
                        break


        for person in persons_list:
            person_id = person['id']
            if 'departmentName' in person:
                person_dep[str(person_id)] = person['departmentName']
            else:
                person_dep[str(person_id)] = ''
            if person['personnelName']:
                persons_obj[str(person_id)] = person['personnelName']
            else:
                persons_obj[str(person_id)] = person['personnelUserName']

        useRecord_list = {}#每日的使用人
        removePerson = ["1","323","103","328"]
        for item in results:
            created_time = ''
            created_time_date=''
            if difyId not in workFlow_logs:
                created_time = datetime.fromtimestamp(item['created_at'])
                created_time_date = formateDate(created_time,"%a, %d %b %Y %H:%M:%S GMT",'%Y-%m-%d')
            else:
                created_time = item['time']
                created_time_date = formateDate(created_time,"%Y-%m-%d %H:%M:%S",'%Y-%m-%d')
            if 'from_end_user_session_id' in item:  # 确保字典中有 'from_end_user_session_id' 键
                id = item['from_end_user_session_id']
                if id in removePerson:
                    continue
                name = ''
                if id in persons_obj:
                    name = persons_obj[id]
                if created_time_date in useRecord_list:
                    if name in useRecord_list[created_time_date]:
                        continue
                    else:
                        useRecord_list[created_time_date].append(name)
                else:
                    useRecord_list[created_time_date] = []
                    useRecord_list[created_time_date].append(name)
        
        # 记录使用人员
        # if time_obj['is_same_day']:
        #     print('进来了')
        readAndWritePerson(persons_list,userRecode_first,userRecode_end,useRecord_list,data)
        

    # 获取具体提问内容以及答案
    chat_message_records = []
    if difyId not in workFlow_logs:
        for chat_record in results:
            try:
                conversation_id = chat_record.get('id')
                user_id = chat_record['from_end_user_session_id']
                chat_message = getChatMessageRecord(conversation_id,data)
                chat_message_data = chat_message.get('data')
                for message in chat_message_data:
                    created_time = datetime.fromtimestamp(message['created_at'])
                    obj = {
                        "input":message['query'],
                        "answer":message['answer'],
                        "answer_tokens":message['answer_tokens'],
                        "conversation_id":message['conversation_id'],
                        "id":user_id,
                        "name":persons_obj[user_id],
                        'departmentName':person_dep[user_id],
                        "time":formateDate(created_time)
                    }
                    chat_message_records.append(obj)
            finally:
                print('获取对话记录')
    else:
        for chat_record in results:
            user_id = chat_record['from_end_user_session_id']
            chat_record['id'] =  user_id
            chat_record['name'] =  persons_obj[user_id]
            chat_record['departmentName'] =  person_dep[user_id]
            chat_message_records.append(chat_record)
    #导出
    file_name = exportExcel(chat_message_records,data,time_obj)
    
    return file_name


# 对比两个时间是否是同一天
def is_same_day_str(date_str1, date_str2, fmt="%Y-%m-%d"):
    date1 = datetime.strptime(date_str1, fmt)
    date2 = datetime.strptime(date_str2, fmt)
    return date1.date() == date2.date()


#获取文件路径
def getFilePath(difyId):
    folder = ''
    if difyId in workFlow_logs:
        folder = workFlow_logs[difyId]['name'] or 'file'
    elif difyId in chatFlow_logs:
        folder = chatFlow_logs[difyId]['name'] or 'file'
    return folder

#获取待发送的邮箱
def getEmails(difyId):
    emails = []
    if difyId in workFlow_logs:
        folder = workFlow_logs[difyId]['emails'] or []
    elif difyId in chatFlow_logs:
        folder = chatFlow_logs[difyId]['emails'] or []
    return emails

def getDifyRecord_method(data):
    difyId = data.get('difyId')
    start_date = formateDate(data.get('startTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d')
    end_date = formateDate(data.get('endTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d')
    time_obj = {
        "startTime":formateDate(data.get('startTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d %H:%M'),
        "endTime":formateDate(data.get('endTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d %H:%M'),
        "startDate":start_date,
        "endDate":end_date,
        "is_same_day":is_same_day_str(start_date,end_date)
    }
    # now_time = formateDate(datetime.now(),"%a, %d %b %Y %H:%M:%S GMT",'%Y-%m-%d')
    time_str = start_date
    if not time_obj['is_same_day']:
        time_str = f'({start_date}-{end_date})'
    
    folder = getFilePath(difyId)

    file_name = f'{file_prefix}\{folder}\{time_str}-{folder}-使用情况.xlsx'
    time_obj['file_name'] = file_name
    file_path = os.path.join(os.getcwd(), '', file_name)
    if not os.path.exists(file_path):
        try:
            result = getDifyRecord1(data,time_obj)
            try:
                emails = getEmails(difyId)
                send_email(file_name,'请查收文件',[file_name],emails)
            except Exception as e:
                print(str(e))
            return jsonify(requestResponse.success(result))
        except HTTPError as http_err:
            return jsonify(requestResponse.any(http_err.response.status_code,'','token错误'))
        except Exception as e:
            print(e)
            return jsonify(requestResponse.error('error'))
    else:
        return jsonify(requestResponse.success(file_name))

#获取当前显示版本数据
@app.route(f'/api/getDifyRecord', methods=['POST'])
async def getDifyRecord():
    data = request.get_json()
    return getDifyRecord_method(data)
    

#下载文件
@app.route(f'/api/download',methods=['GET'])
async def download():
    get_params = request.args
    filename = get_params.get('filePath')
    file_path = os.path.join(os.getcwd(), '', filename)

    if not os.path.exists(file_path):
        abort(404)  # 返回 404 错误
 
    try:
        # 返回文件给用户下载
        return send_file(file_path, as_attachment=True)
    except Exception as e:
        # 处理可能的异常（例如文件权限问题）
        return str(e), 500


import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
from email.header import Header
from email.mime.application import MIMEApplication


# 获取文件路径中的文件名称
def getFileNameByFilePath(filePath):
    result = filePath.split('\\')
    if result and len(result)>0:
        return result[len(result)-1]
    else:
        return filePath


def send_email(subject="使用情况", body='', attachments=None,emails=[]):
    sender="zhuliu@china-wispro.com"
    password="Xiaoshi221101"
    receiver="zl2780212168@163.com"
    if len(emails)>0:
        receiver = receiver + ', ' + ', '.join(emails)
    message = MIMEMultipart()
    message["From"] = sender
    message["To"] = receiver

    subject_str = getFileNameByFilePath(subject)

    message["Subject"] = Header(subject_str, "utf-8").encode()
    message.attach(MIMEText(body, "plain", "utf-8"))

    if attachments:
        for filename in attachments:
            with open(filename, "rb") as f:
                attachment = MIMEApplication(f.read())
                filename_str = getFileNameByFilePath(filename)
                attachment.add_header('Content-Disposition', 'attachment', filename=filename_str)
                message.attach(attachment)

    try:
        with smtplib.SMTP_SSL("smtp.exmail.qq.com", 465) as server:
            # server.starttls()
            server.login(sender, password)
            server.sendmail(sender, receiver, message.as_string())
        return True
    except Exception as e:
        print(f"Error: {e}")
        return False





# MySQL数据库配置
db_config = {
    'host': '139.224.34.118',
    'user': 'root',
    'password': 'TU5x6IeBi7rl',
    'db': 'PCS_PROD',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}
db_config2 = {
    'host': '139.224.34.118',
    'user': 'root',
    'password': 'TU5x6IeBi7rl',
    'db': 'pas_prod2',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}
# db_config2 = {
#     'host': '192.168.2.24',
#     'user': 'root',
#     'password': 'rrzTwWAYX8Gxh5JH',
#     'db': 'pas_prod2',
#     'charset': 'utf8mb4',
#     'cursorclass': pymysql.cursors.DictCursor,
# }

# 根据人员id获取部门
def getDepartmentByPersonIds(ids):
    result = []
    if not ids:
        return result
    if len(ids) == 0:
        return result
    
    departmentList = []
    # 连接到数据库
    connection = pymysql.connect(**db_config)
    with connection.cursor() as cursor:
        query = f"SELECT a.PERSONNEL_ID as personId, b.* FROM ASSOCIATE_PERSONNEL_DP a left join ASSOCIATE_DEPARTMENT_POSITION b ON a.DEPARTMENT_AND_POSITION_ID = b.id where a.IS_DELETE != 1 and a.PERSONNEL_ID in ({','.join(ids)})"
        cursor.execute(query)
        results = cursor.fetchall()
        if results and len(results)>0:
            departmentIds = []
            for department in results:
                if 'DEPARTMENT_ID' in department:
                    departmentIds.append(department['DEPARTMENT_ID'])
            query = f"SELECT * from DEPARTMENT where IS_DELETE != 1 && id in ({','.join(map(str,departmentIds))})"
            cursor.execute(query)
            departmentList = cursor.fetchall()
            if len(departmentList)>0:
                for person in results:
                    departmentId = person['DEPARTMENT_ID']
                    for department in departmentList:
                        if departmentId == department['ID']:
                            result.append(
                                {
                                    'personId':person['personId'],
                                    'departmentId':departmentId,
                                    'departmentName':department['DEPARTMENT_NAME']
                                }
                            )
                            break
    connection.close()
    return result


# 获取workflow的使用记录
def getSession(data):
    start_date = formateDate(data.get('startTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d %H:%M')
    end_date = formateDate(data.get('endTime'),"%Y-%m-%dT%H:%M",'%Y-%m-%d %H:%M')
    type = data['type']
    result = []
    # 连接到数据库
    connection = pymysql.connect(**db_config2)
    with connection.cursor() as cursor:
        query = f'SELECT a.conversation_id,a.conversation_name,a.create_id as from_end_user_session_id,a.content,a.create_time as time,a.guid ,b.guid as resultGuid from confession_session a left join asso_confession_session_file b on a.id=b.confession_session_id where type = {type} and create_time >= "{start_date}" and create_time <= "{end_date}" order by create_time DESC'
        cursor.execute(query)
        results = cursor.fetchall()
        if results:
            # guids = [item['guid'] for item in results]
            guids = []
            resultGuids = []
            for item in results:
                if item['guid']:
                    guids.append(item['guid'])
                if item['resultGuid']:
                    resultGuids.append(item['resultGuid'])
            file_guid_obj = {}
            if len(guids)>0:
                guid_files = getFileMessageByGuid(guids)
                file_guid_obj = groupByField(guid_files,'guid')
            
            resultGuids_obj = {}
            if len(resultGuids)>0:
                resultGuids_files = getFileMessageByGuid(resultGuids)
                resultGuids_obj = groupByField(resultGuids_files,'guid')

            for item in results:
                guid = item['guid']
                guid_obj = {}
                if guid in file_guid_obj:
                    file_data = file_guid_obj[guid]['data']
                    if len(file_data):
                        guid_obj = file_data[0]
                resultGuid = item['resultGuid']
                resultGuid_obj = {}
                if resultGuid in resultGuids_obj:
                    resultGuid_data = resultGuids_obj[resultGuid]['data']
                    if len(resultGuid_data):
                        resultGuid_obj = resultGuid_data[0]
                obj = {
                    "conversation_id":item['conversation_id'],
                    "conversation_name":item['conversation_name'],
                    "from_end_user_session_id":item['from_end_user_session_id'],
                    "time":item['time'].strftime("%Y-%m-%d %H:%M:%S"),
                    'guid':guid,
                    'file':guid_obj,
                    "input":"",
                    "answer":"",
                    'resultFile':resultGuid_obj
                }
                if item['content']:
                    try:
                        contents = json.loads(item['content'])
                        if 'query' in contents:
                            obj['input'] = str(contents['query'])
                        else:
                            obj['input'] = ''
                        
                        if 'answer' in contents:
                            obj['answer'] = str(contents['answer'])
                        else:
                            obj['answer'] = ''
                    except Exception as e:
                        print(e)
                result.append(obj)
    connection.close()
    return result

#根据文件guid获取文件信息
def getFileMessageByGuid(guids):
    if(not guids or len(guids) == 0):
        return []
    try:
        headers = {'Content-Type': 'application/json'}
        fileResponse = requests.post('https://xsip.cn/api/fileManager/getFileData', headers=headers, data=json.dumps(guids))
        fileResponse.raise_for_status()
        fileJson = fileResponse.json()
        return fileJson or [] 
    except Exception as e:
        return []        




if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=3500)