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)