Helloworld翻译PC版如何通过脚本自动化实现每日/每周翻译报告生成

import requests
import pandas as pd
from datetime import datetime, timedelta
import json
import os

# 配置信息 - 请替换为你的实际信息
BASE_URL = "https://api.hellopworld.com/v1"  # 示例API地址，请以官方文档为准
API_KEY = "your_api_key_here"
HEADERS = {
    "Authorization": f"Bearer {API_KEY}",
    "Content-Type": "application/json"
}

def make_api_request(endpoint, params=None):
    """通用的API请求函数，处理错误和重试。"""
    url = f"{BASE_URL}/{endpoint}"
    try:
        response = requests.get(url, headers=HEADERS, params=params, timeout=30)
        response.raise_for_status()  # 如果状态码不是200，抛出HTTPError异常
        return response.json()
    except requests.exceptions.RequestException as e:
        print(f"请求API失败 ({endpoint}): {e}")
        # 在实际应用中，这里可以添加重试逻辑或更详细的错误日志
        return None

def test_api_connection():
    """测试API连接是否正常。"""
    print("测试API连接...")
    # 假设有一个用于测试的端点，例如获取用户信息或项目列表
    data = make_api_request("projects", params={"limit": 1})
    if data and 'projects' in data:
        print("API连接成功！")
        return True
    else:
        print("API连接失败，请检查配置。")
        return False

def get_recent_projects(days_back=7):
    """获取最近N天内有活动的项目列表。"""
    since_date = (datetime.now() - timedelta(days=days_back)).isoformat() + 'Z'  # ISO格式，UTC时间
    params = {
        "updated_after": since_date,
        # "status": "in_progress,completed", # 可以筛选状态
        "limit": 100  # 根据实际情况调整，或处理分页
    }
    data = make_api_request("projects", params)
    if data and 'projects' in data:
        return data['projects']
    return []

def get_project_details(project_id):
    """获取特定项目的详细信息，包括统计。"""
    # 假设API有直接获取项目统计的端点
    stats_data = make_api_request(f"projects/{project_id}/statistics")
    tasks_data = make_api_request(f"projects/{project_id}/tasks")
    
    project_info = {}
    if stats_data:
        project_info['stats'] = stats_data
    if tasks_data and 'tasks' in tasks_data:
        project_info['tasks'] = tasks_data['tasks']
    return project_info

def get_task_activities(task_id, days_back=1):
    """获取特定任务在最近N天内的活动日志（如翻译、审校操作）。"""
    since_date = (datetime.now() - timedelta(days=days_back)).isoformat() + 'Z'
    params = {"activity_after": since_date}
    # 假设存在活动日志端点
    activities = make_api_request(f"tasks/{task_id}/activities", params)
    if activities and 'activities' in activities:
        return activities['activities']
    return []

def process_projects_to_dataframe(projects_list):
    """将项目列表处理成结构化的Pandas DataFrame。"""
    if not projects_list:
        return pd.DataFrame()
    
    rows = []
    for proj in projects_list:
        project_id = proj.get('id')
        details = get_project_details(project_id)
        if not details:
            continue
            
        stats = details.get('stats', {})
        # 从stats中提取关键指标，字段名需根据实际API响应调整
        row = {
            '项目ID': project_id,
            '项目名称': proj.get('name', 'N/A'),
            '客户/来源': proj.get('client', 'N/A'),
            '创建日期': proj.get('created_at', 'N/A'),
            '截止日期': proj.get('deadline', 'N/A'),
            '状态': proj.get('status', 'N/A'),
            '总字数': stats.get('total_words', 0),
            '新字数': stats.get('new_words', 0),
            '重复字数': stats.get('repetition_words', 0),
            '精确匹配字数': stats.get('exact_match_words', 0),
            '模糊匹配字数': stats.get('fuzzy_match_words', 0),
            '翻译耗时(小时)': stats.get('translation_hours', 0),
            '审校耗时(小时)': stats.get('review_hours', 0),
        }
        # 计算衍生指标
        total_translated = row['新字数'] + row['重复字数'] + row['精确匹配字数'] + row['模糊匹配字数']
        if row['翻译耗时(小时)'] > 0:
            row['翻译速度(字/时)'] = round(total_translated / row['翻译耗时(小时)'], 1)
        else:
            row['翻译速度(字/时)'] = 0
            
        rows.append(row)
    
    df = pd.DataFrame(rows)
    # 进行日期格式转换等后续处理
    if not df.empty and '创建日期' in df.columns:
        df['创建日期'] = pd.to_datetime(df['创建日期']).dt.strftime('%Y-%m-%d')
    return df

def generate_summary_statistics(df):
    """从项目DataFrame生成汇总统计数据。"""
    if df.empty:
        return {}
    
    summary = {
        '报告生成时间': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        '统计周期': f"最近{7}天", # 可根据参数动态化
        '活跃项目总数': len(df),
        '总处理字数': int(df['总字数'].sum()),
        '总新字数': int(df['新字数'].sum()),
        '总体重复率': f"{(df['重复字数'].sum() / df['总字数'].sum() * 100):.1f}%" if df['总字数'].sum() > 0 else "0%",
        '平均翻译速度': f"{df['翻译速度(字/时)'].mean():.1f}",
        '进行中项目数': len(df[df['状态'] == 'in_progress']),
        '已逾期项目数': len(df[(df['截止日期'] != 'N/A') & (pd.to_datetime(df['截止日期']) < datetime.now())]),
    }
    return summary

def generate_excel_report(df, summary, output_path="translation_report.xlsx"):
    """生成包含多个工作表的Excel报告。"""
    if df.empty:
        print("没有数据可生成报告。")
        return False
    
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        # 工作表1：详细数据
        df.to_excel(writer, sheet_name='项目详情', index=False)
        
        # 工作表2：汇总统计
        summary_df = pd.DataFrame([summary]) # 将字典转换为单行DataFrame
        summary_df.T.to_excel(writer, sheet_name='报告摘要', header=False) # 转置，让键值对垂直显示
        
        # （可选）工作表3：按译员或语言对汇总
        # 这里假设df中有‘译员’和‘目标语言’列
        if '译员' in df.columns:
            translator_summary = df.groupby('译员').agg({
                '总字数': 'sum',
                '翻译耗时(小时)': 'sum'
            }).round(1)
            translator_summary.to_excel(writer, sheet_name='译员绩效')
        
        # 获取工作表对象进行格式美化
        workbook = writer.book
        detail_sheet = writer.sheets['项目详情']
        # 设置列宽（示例）
        for column in detail_sheet.columns:
            max_length = 0
            column_letter = column[0].column_letter
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 30)
            detail_sheet.column_dimensions[column_letter].width = adjusted_width
    
    print(f"Excel报告已生成: {output_path}")
    return True

from jinja2 import Template

def generate_html_report(df, summary, output_path="translation_report.html"):
    """使用Jinja2模板生成HTML报告。"""
    
    # 一个简单的内联HTML模板
    html_template = """
    <!DOCTYPE html>
    <html>
    <head>
        <title>Helloworld翻译工作报告 - {{ summary.报告生成时间 }}</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 40px; }
            h1 { color: #333; }
            .summary { background-color: #f5f5f5; padding: 20px; border-radius: 5px; margin-bottom: 30px; }
            .summary-item { margin: 5px 0; }
            .summary-key { font-weight: bold; display: inline-block; width: 200px; }
            table { border-collapse: collapse; width: 100%; margin-top: 20px; }
            th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
            th { background-color: #4CAF50; color: white; }
            tr:nth-child(even) { background-color: #f2f2f2; }
        </style>
    </head>
    <body>
        <h1>📊 Helloworld翻译自动化工作报告</h1>
        <div class="summary">
            <h2>报告摘要 ({{ summary.统计周期 }})</h2>
            {% for key, value in summary.items() %}
            <div class="summary-item"><span class="summary-key">{{ key }}:</span> {{ value }}</div>
            {% endfor %}
        </div>
        
        <h2>项目详情</h2>
        <table>
            <thead>
                <tr>
                    {% for column in df.columns %}
                    <th>{{ column }}</th>
                    {% endfor %}
                </tr>
            </thead>
            <tbody>
                {% for _, row in df.iterrows() %}
                <tr>
                    {% for value in row %}
                    <td>{{ value }}</td>
                    {% endfor %}
                </tr>
                {% endfor %}
            </tbody>
        </table>
        <p><em>报告由自动化脚本生成于 {{ summary.报告生成时间 }}。</em></p>
    </body>
    </html>
    """
    
    template = Template(html_template)
    # 将DataFrame转换为适合模板渲染的格式（如将NaN转为空字符串）
    df_html = df.fillna('').astype(str)
    html_content = template.render(summary=summary, df=df_html)
    
    with open(output_path, 'w', encoding='utf-8') as f:
        f.write(html_content)
    
    print(f"HTML报告已生成: {output_path}")
    return output_path

# 如果需要转换为PDF，安装weasyprint后可以添加如下函数
# from weasyprint import HTML
# def convert_html_to_pdf(html_path, pdf_path):
#     HTML(html_path).write_pdf(pdf_path)
#     print(f"PDF报告已生成: {pdf_path}")

def main(report_days=7, output_format='excel'):
    """主函数，协调整个报告生成流程。"""
    print("="*50)
    print("开始生成Helloworld翻译自动化报告...")
    
    # 1. 测试连接
    if not test_api_connection():
        return
    
    # 2. 获取并处理数据
    print("获取项目数据...")
    projects = get_recent_projects(days_back=report_days)
    if not projects:
        print("在指定时间段内未找到活跃项目。")
        return
    
    df = process_projects_to_dataframe(projects)
    summary = generate_summary_statistics(df)
    
    # 3. 生成报告
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    
    if output_format.lower() == 'excel':
        filename = f"translation_report_{timestamp}.xlsx"
        success = generate_excel_report(df, summary, filename)
    elif output_format.lower() == 'html':
        filename = f"translation_report_{timestamp}.html"
        success = generate_html_report(df, summary, filename)
        # 可选：转换为PDF
        # pdf_name = filename.replace('.html', '.pdf')
        # convert_html_to_pdf(filename, pdf_name)
    else:
        print(f"不支持的输出格式: {output_format}")
        return
    
    if success:
        print("报告生成流程完成！")
        # 此处可以调用邮件发送或文件上传函数
        # send_email_with_attachment(filename, summary)
    print("="*50)

if __name__ == "__main__":
    # 可以通过命令行参数控制，这里简单示例
    main(report_days=1, output_format='excel')  # 生成每日Excel报告