import re import pandas as pd import os from dotenv import load_dotenv import openai from datetime import datetime import httpx import gradio as gr from docxtpl import DocxTemplate # 加载 .env 文件 load_dotenv() authorization = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImp0aSI6IjkxMzIxYjY3YWM3ZWZlNTdjMGRmNWJkNmMxMTU2ZTI3OTU3OWI0M2ZjZDhjYWYxMGE1ZjllY2UzNWNjZmVlNTIxZTU5MGJjMzZiYzM5NzNhIn0.eyJhdWQiOiJGRnlIN0VCbTc1aFF4cnJZTWU4NWVVbnNsNWdVQy1aSWVDdnpuN2hwYkJBIiwianRpIjoiOTEzMjFiNjdhYzdlZmU1N2MwZGY1YmQ2YzExNTZlMjc5NTc5YjQzZmNkOGNhZjEwYTVmOWVjZTM1Y2NmZWU1MjFlNTkwYmMzNmJjMzk3M2EiLCJpYXQiOjE3Mjk4NTIzMjIsIm5iZiI6MTcyOTg1MjMyMiwiZXhwIjoxNzI5OTM4NzIyLjExNjMwOCwic3ViIjoiNTU5NTgiLCJzY29wZSI6WyJhdXRoZW50aWNhdGVkIl0sImVtYWlsIjoiamllLndhbmdAa2luZGluZ2xhdy5jb20iLCJzdWJzY3JpcHRpb24iOiJlc3NlbnRpYWwiLCJ0ZW5hbnRfZXhwaXJ5IjpudWxsLCJuYW1lIjoiSmllIFdhbmcifQ.e8bjYP0qebVjdiw8SIJYEVFj9agn_7ZS5EWvEEm_sUuDFSn2IfvIr2U2ExhF6oKlj0TXPatLFLOLZJgXjIyOGn3k2beP1QEsq3jtVrfM8-KG7ZnLXehYl9xp7gRDqNST8_M_tt6m1cLWoFl7-BvpSBJQxFCsD8_uOzK5swB1MHDUegZnvwMKHHP4rm5sHinXcEQ_eyzKsiZ8ZE4Zn6LCa7HWam0Ca61BGPMU4GrNK2kfn19rIb70huJ8tNN3ulqp5x1bJQVfIKUEWTrp0KJmQOsvY7idfi-jWluuJ3g3VULxzZuwU7YN2Gxv5gom9N-eCAdiPyb3IOumLnN2mr3ZT09R8nhGzW8MO2JRai-YgbnVMrkTqTnpFgz9JfOrNOme-Hw1AhLvJN3O2Db8uY6evtljeJqikfjHvWyztOntlCE5RpfCihGHDorFiKhSu2vxA9f4c_Dt0Cm3_HjDMSuqy0jU14F-CQkaJbT6ApCAIUS2xSUCzSpcjSR8BUjjua5KfMh_hM8eFQxOWWXmJBomCX0ZnQeADYJ5USK_NO89DCsSdUkYsBeP9vBbjiD8FS71vu4mfv4Mdz18ZVL1yDjIq8HboLjT7KLPQDHI9PSDzochvxTmHnW6MayTyvuFGPAUvPMDAUL2-kSdTDhdRwYZF1GTk4K2Dd7vsTpLNBZMdDY" headers = { 'accept': '*/*', 'accept-language': 'zh-CN,zh;q=0.9', 'content-type': 'application/json', 'dnt': '1', 'origin': 'https://www.dataguidance.ai', 'priority': 'u=1, i', 'referer': 'https://www.dataguidance.ai', 'sec-ch-ua': '"Not?A_Brand";v="99", "Chromium";v="130"', 'sec-ch-ua-mobile': '?0', 'sec-ch-ua-platform': '"macOS"', 'sec-fetch-dest': 'empty', 'sec-fetch-mode': 'cors', 'sec-fetch-site': 'cross-site', 'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36' } if os.getenv('MODE') == "dev": proxies = { 'http://': 'http://127.0.0.1:7890', 'https://': 'http://127.0.0.1:7890' } else: proxies = None title_translate_prompt = """ 你是一位翻译专家，精通中英文，擅长复杂的术论文翻译成易懂的科普文章。 请将输入的标题从英文翻译到中文。 """ country_translate_prompt = """ 你是一位翻译专家，精通中英文，擅长复杂的术论文翻译成易懂的科普文章。 请将输入的国家或地区名称从英文翻译到中文。 """ prompt = """ # Character 你是一位翻译专家，精通中英文，擅长复杂的术论文翻译成易懂的科普文章。你无需编程，而是专注于题解和翻译。 ## Skills - 将英文学术论文翻译成中文科普文章（保持原有格式和专业术语，例如FLAC，JPEG，Microsoft，Amazon等） - 注意必须准确传达原文的事实和背景 - 在需要的时候，在括号中标记对应的英文单词 ### Skill 1: 直接翻译 - 根据英文内容直接翻译，保持原有的格式，尽量不遗漏任何信息 ## 策略 策略： 1. 根据Skill 1进行英文内容直详，保持原有格式，不要遗漏任何信息 2. 去除所有的HTML标签 3. 去除所有的Markdown格式 ## 限制 - 必须翻译原值的全部内容，包括专业术语（例如 FLCA ，JPEG等）以及公司名词（例如 Microsoft，Amazon等） - 根据数据保护的相关术语词汇对应表，Controller对应中文"控制者"，Processor对应"处理者"Data breach对应"数据泄漏"，Sub processor对应"子处理者"，Information Subject对应"数据主体"，Transfer对应"传输" - 在应对可能存在多义的英文词汇时，要在括号中标记对应的英文单词 - 回答所有问题时，不能使用"很抱歉，但是"等开头 - 必须遵守道德和法律，不能产生、传播或解释任何非法、有害或歧视性的内容 """ # OpenRouter API 配置 openrouter_url = 'https://openrouter.ai/api/v1' # 从Excel读取区域映射 def load_area_mapping(): mapping_df = pd.read_excel('area_mapping.xlsx') area_mapping = {} for _, row in mapping_df.iterrows(): area_mapping[row['area']] = [country.strip() for country in row['countries'].split(',')] return area_mapping # 替换原来的硬编码映射 area_mapping = load_area_mapping() def process_urls(urls_text, auth, progress=gr.Progress()): # Update headers with the provided auth token headers['authorization'] = f"Bearer {auth}" # Initialize or load existing DataFrame if os.path.exists('output.xlsx'): df = pd.read_excel('output.xlsx') else: df = pd.DataFrame(columns=['url', 'html', 'area', 'country', 'date', 'translated_title', 'translated_content', 'comment']) # Load area mapping area_mapping = load_area_mapping() # Split URLs into list urls = [url.strip() for url in urls_text.split('\n') if url.strip()] results = [] for url in progress.tqdm(urls): # Check if URL already exists in DataFrame if url in df['url'].values: if pd.notna(df.loc[df['url'] == url, 'translated_content'].iloc[0]): results.append(f"跳过已存在的 URL: {url}") continue try: # Extract path from URL match = re.search(r'/news/(.+?)(?:\?|$)', url) if not match: results.append(f"URL 格式不正确: {url}") continue path = match.group(0) composed_url = f'https://dgcb20-ca-northeurope-dglive.yellowground-c1f17366.northeurope.azurecontainerapps.io/api/v1/content/articles/by_path?path={path}' # Get article content headers['referer'] = url response = httpx.get(composed_url, headers=headers, proxies=proxies) if response.status_code != 200: results.append(f"获取内容失败 ({response.status_code}): {url}") continue data = response.json() html_content = data['contentBody']['html']['en'] title = data['title']['en'] split_title = title.split(':') country_en = split_title[0].strip() # Translate country country_zh = client.chat.completions.create( model="google/gemini-flash-1.5-8b", messages=[ {"role": "system", "content": country_translate_prompt}, {"role": "user", "content": country_en} ] ).choices[0].message.content # Determine area area = '其他' for region, countries in area_mapping.items(): if any(country in country_zh for country in countries): area = region break # Process date published_on = data['publishedOn'] published_on_zh = datetime.strptime(published_on, '%Y-%m-%dT%H:%M:%S%z').strftime('%Y年%m月%d日') # Translate title title_zh = client.chat.completions.create( model="gpt-4o-mini", messages=[ {"role": "system", "content": title_translate_prompt}, {"role": "user", "content": title} ] ).choices[0].message.content # Translate content translation_response = client.beta.chat.completions.parse( model="gpt-4o-mini", messages=[ {"role": "system", "content": prompt}, {"role": "user", "content": html_content} ] ) translated_content = translation_response.choices[0].message.content.replace('\n', '\r\n') # Add or update DataFrame new_row = { 'url': url, 'html': html_content, 'area': area, 'country': country_zh, 'date': published_on_zh, 'translated_title': title_zh, 'translated_content': translated_content, 'comment': '' } if url in df['url'].values: df.loc[df['url'] == url] = new_row else: df = pd.concat([df, pd.DataFrame([new_row])], ignore_index=True) results.append(f"成功处理 URL: {url}") except Exception as e: results.append(f"处理出错 ({str(e)}): {url}") # Save after each successful processing df.to_excel('output.xlsx', index=False) # Return both the results text and the path to the Excel file return '\n'.join(results), 'output.xlsx' def process_excel_and_generate_docs(excel_file, term, start_year, end_year, start_month, start_day, end_month, end_day): # 读取Excel文件 df = pd.read_excel(excel_file.name) # Initialize the news dictionary news_dict = {'news': {}} # Group by area and convert to the desired format for area, group in df.groupby('area'): news_dict['news'][area.lower()] = [ { 'title': row['translated_title'], 'date': row['date'], 'content': str(row['translated_content']).replace('\r\n', '\n').replace('\r', '\n').replace('\n', '\r\n'), 'comment': row['comment'], 'country': row['country'] } for _, row in group.iterrows() ] # Create the context dictionary with all required fields context = { 'term': term, 'start_year': start_year, 'end_year': end_year, "start_month": start_month, "start_day": start_day, "end_month": end_month, "end_day": end_day, **news_dict } # Render PDF template pdf_output_path = "pdf.docx" pdf_tpl = DocxTemplate("v1.1 周报模板.docx") pdf_tpl.render(context) pdf_tpl.save(pdf_output_path) # Render Email template email_output_path = "email.docx" email_tpl = DocxTemplate("v1.1 周报邮件格式调整.docx") email_tpl.render(context) email_tpl.save(email_output_path) return [pdf_output_path, email_output_path] def create_combined_interface(): with gr.Blocks() as app: gr.Markdown("# News Processing & Report Generation Tool") with gr.Tabs() as tabs: # Tab 1: URL Processing with gr.Tab("URL Processing"): with gr.Row(): auth_input = gr.Textbox( label="Authorization Token", value=authorization, type="password", lines=1, ) with gr.Row(): urls_input = gr.Textbox( label="Input URLs (one per line)", placeholder="https://www.dataguidance.ai/news/...\nhttps://www.dataguidance.ai/news/...", lines=20 ) with gr.Row(): process_button = gr.Button("Process URLs") with gr.Row(): output = gr.Textbox(label="Processing Results", lines=20) with gr.Row(): file_output = gr.File(label="Download Processed Excel") # Tab 2: Report Generation with gr.Tab("Report Generation"): with gr.Row(): excel_file = gr.File(label="Upload Excel File") with gr.Row(): term = gr.Textbox(label="期数", value="201") start_year = gr.Textbox(label="起始年", value="2024") end_year = gr.Textbox(label="结束年", value="2024") with gr.Row(): start_month = gr.Textbox(label="起始月份", value="9") start_day = gr.Textbox(label="起始日", value="1") end_month = gr.Textbox(label="结束月份", value="9") end_day = gr.Textbox(label="结束日", value="15") with gr.Row(): generate_btn = gr.Button("Generate Reports") with gr.Row(): pdf_output = gr.File(label="Download PDF Report") email_output = gr.File(label="Download Email Template") # Connect the buttons to their respective functions process_button.click( fn=lambda auth, urls: process_urls(urls, auth=auth), inputs=[auth_input, urls_input], outputs=[output, file_output] ) generate_btn.click( fn=process_excel_and_generate_docs, inputs=[excel_file, term, start_year, end_year, start_month, start_day, end_month, end_day], outputs=[pdf_output, email_output] ) return app auth = (os.environ.get("GRADIO_USERNAME", "admin"), os.environ.get("GRADIO_PASSWORD", "password123")) if __name__ == "__main__": # Initialize OpenAI client client = openai.Client( api_key=os.getenv('OPENROUTER_API_KEY'), base_url=openrouter_url, http_client=httpx.Client(proxies=proxies) ) # Launch combined Gradio interface with authentication app = create_combined_interface() app.launch( # auth=auth, max_threads=3, # Limit concurrent processing show_error=True, share=True, server_name="0.0.0.0" )