app.py

import gradio as gr
import json
import pandas as pd
from groq import Groq
import mysql.connector
import os
from datetime import datetime


#MySQLに接続
conn = mysql.connector.connect(
    host="www.ryhintl.com",
    user="smairuser",
    password="smairuser",
    port=36000,
    database="smair"
)

# カーソルを取得
cursor = conn.cursor(dictionary=True)

# List API Keys
#select_one_data_query = "SELECT * FROM agentic_apis"
#cursor.execute(select_one_data_query)
#result = cursor.fetchall()
# JSONをパースしてkeyを抽出
#keys = [item['key'] for item in result]

#os.environ["GROQ_API_KEY"] = keys[2]


client = Groq(api_key=os.environ["GROQ_API_KEY"])

def update_total_amount(expenses):    
    df = pd.DataFrame(expenses)
    df["金額 (JPY)"] = df["金額 (JPY)"].astype(float)
    total_amount = df["金額 (JPY)"].sum()
    grand = int(total_amount)
    return grand

def show_image1():
    return "./receipt1.png"

def show_image2():
    return "./receipt2.png"

def show_image3():
    return "./receipt3.png"


def audit_check(indata,rules):
    # Set the system prompt
    system_prompt = {
        "role": "system",
        "content": "You are a helpful assistant, answer questions concisely."
    }

    # Set the user prompt
    user_input = f'{indata}+"から不正利用が疑われるreport_idとemployee_nameを教えて。判断条件は[rule]に基づいて評価してください。経費の詳細も教えてください。[rule]='+rules
    user_prompt = {
        "role": "user", "content": user_input
    }

    # Initialize the chat history
    chat_history = [system_prompt, user_prompt]

    response = client.chat.completions.create(
        model="llama-3.3-70b-versatile",
        messages=chat_history,
        max_tokens=1024,
        temperature=0)

    myresp = response.choices[0].message.content

    responded = f'{myresp}'
    return responded

def generate_json(report_id, employee_name, department, submission_date, total_amount, status, receipt1, receipt2, receipt3, expenses):
    if isinstance(expenses, pd.DataFrame):
        expenses = expenses.values.tolist()  # DataFrameをリストのリストに変換

    expense_list = []
    for expense in expenses:
        if len(expense) < 5:
            print(f"Error: Unexpected data format -> {expense}")
            continue  # エラー回避のためスキップ

        expense_list.append({
            "expense_id": expense[0],
            "date": expense[1],
            "category": expense[2],
            "amount": expense[3],
            "description": expense[4]
        })
    
    report = {
        "report_id": report_id,
        "employee_name": employee_name,
        "department": department,
        "submission_date": submission_date,
        "total_amount": total_amount,
        "status": status,
        "expenses": expense_list
    }

    print("tmp:",report)
    return report
    #return json.dumps(report, indent=4, ensure_ascii=False)

with gr.Blocks(css="footer {visibility: hidden;}",title="経費報告書チェック") as check:
    gr.HTML('<div style="display: flex; justify-content: center; align-items: center; font-size: 20px; font-weight: bold; color: #333; background: #E2E6FA; padding: 10px; border-radius: 8px; box-shadow: 2px 2px 5px rgba(0,0,0,0.2);">🗞 CONCUR 経費報告書チェック</div>')

    with gr.Tab("💹 経費報告書"):
        gr.Markdown("### 経費報告書")
        today = datetime.now()
        mydate = today.date().strftime("%Y-%m-%d")

        report_id = gr.Textbox(label="報告書ID", value="EXP123456")
        employee_name = gr.Textbox(label="従業員名", value="山田太郎")
        
        department = gr.Dropdown(
            ["営業部", "顧客サポート部", "マーケティング部", "人事・総務部"], value="営業部", multiselect=False, label="部署", info="所属部門"
        )
        submission_date = gr.DateTime(label="提出日", include_time=False, value=mydate)
        total_amount = gr.Textbox(label="総額", value="69280")
        status = gr.Dropdown(
            ["承認待ち", "却下", "承認済"], value="承認待ち", multiselect=False, label="部署", info="承認状況"
        )    

        with gr.Row():
            receipt1 = gr.Image(value=show_image1, label="交通費")
            receipt2 = gr.Image(value=show_image2, label="食事代")
            receipt3 = gr.Image(value=show_image3, label="宿泊費")
    
        expenses = gr.Dataframe(headers=["経費ID", "日付", "カテゴリ", "金額 (JPY)", "説明"],
                            show_search="検索...",
                            show_copy_button=True,
                            show_fullscreen_button=True,
                            show_row_numbers=True,
                            pinned_columns=1,
                            static_columns=[0,1,2,4],
                            column_widths=["300px"],
                            value=[["EXP001", "2025-04-10", "交通費", "17980", "航空運賃"],
                                   ["EXP002", "2025-04-11", "食事代", "9900", "クライアントとのランチ"],
                                   ["EXP003", "2025-04-12", "宿泊費", "41400", "出張ホテル代"]],
                            interactive=True)
        
        expenses.change(fn=update_total_amount, inputs=expenses, outputs=total_amount)
    
        generate_button = gr.Button("経費生成")
        output_json = gr.Textbox(label="生成された経費データ", interactive=False, show_copy_button=True)

        generate_button.click(generate_json, 
                          inputs=[report_id, employee_name, department, submission_date, total_amount, status, receipt1, receipt2, receipt3, expenses],
                          outputs=output_json)
        
    with gr.Tab("✔️ 経費報告書チェック"):
        gr.Markdown("### 経費報告書チェック")
        json = '''{'report_id': 'EXP123456', 'employee_name': '山田太郎', 'department': '営業部', 'submission_date': '2025-04-14', 'total_amount': '69280'', 'status': '承認待ち', 'expenses': [{'expense_id': 'EXP001', 'date': '2025-04-10', 'category': '交通費', 'amount': '17980', 'description': '電車代'}, {'expense_id': 'EXP002', 'date': '2025-04-11', 'category': '食事代', 'amount': '9900', 'description': 'クライアントとのランチ'}, {'expense_id': 'EXP003', 'date': '2025-04-12', 'category': '宿泊費', 'amount': '41400', 'description': '出張ホテル代'}]}'''
        concur_json = gr.Textbox(label="経費内訳", value=json,lines=10)
        rules = gr.Textbox(label="経費ルール", 
                           value='''1.一回の合計の上限は60000円です。\n2.月の食事代の回数は2回までです。''',lines=10, interactive=True)
        
        concur_output = gr.Textbox(label="経費チェック結果", lines=10)

        audit_button = gr.Button("経費チェック")
        audit_button.click(audit_check, 
                          inputs=[concur_json,rules],
                          outputs=concur_output)

check.launch(show_api=False)