import streamlit as st import google.generativeai as genai import pandas as pd from sqlalchemy import create_engine, text import pymysql import hashlib from datetime import datetime, timedelta # ========================================== # 1. 設定・構成部分 # ========================================== # ページ設定 st.set_page_config(page_title="SkgDB-TtoS", layout="wide") # セッション状態の初期化（ログイン状態管理） if "is_logged_in" not in st.session_state: st.session_state["is_logged_in"] = False if "user_info" not in st.session_state: st.session_state["user_info"] = None # ========================================== # (追加) モデル設定とレートリミット定義 # ========================================== MODEL_CONFIG = { "gemini-2.5-flash": { "name": "Gemini 2.5 Flash", "rpm_limit": 5, # 1分間のリクエスト上限 "tpm_limit": 250000, # 1分間のトークン上限 "rpd_limit": 20 # 1日のリクエスト上限 }, "gemini-2.5-flash-lite": { "name": "Gemini 2.5 Flash Lite", "rpm_limit": 10, # Liteは少し緩いと仮定 "tpm_limit": 250000, "rpd_limit": 20 } } # ========================================== # (追加) API使用量の管理機能 # ========================================== def log_api_usage(user_id, tokens, model_name): """API利用履歴をDBに保存（モデル名付き）""" try: with engine.connect() as conn: stmt = text(""" INSERT INTO api_logs (user_id, total_tokens, request_timestamp, model_name) VALUES (:uid, :tok, NOW(), :model) """) conn.execute(stmt, {"uid": user_id, "tok": tokens, "model": model_name}) conn.commit() except Exception as e: print(f"ログ保存エラー: {e}") def get_api_metrics(target_model): """指定されたモデルの直近利用状況を集計して返す""" metrics = {"rpm": 0, "tpm": 0, "rpd": 0} try: with engine.connect() as conn: # 1. RPM (対象モデルの過去1分間) query_rpm = text(""" SELECT COUNT(*) FROM api_logs WHERE model_name = :model AND request_timestamp >= NOW() - INTERVAL 1 MINUTE """) metrics["rpm"] = conn.execute(query_rpm, {"model": target_model}).scalar() # 2. TPM query_tpm = text(""" SELECT IFNULL(SUM(total_tokens), 0) FROM api_logs WHERE model_name = :model AND request_timestamp >= NOW() - INTERVAL 1 MINUTE """) metrics["tpm"] = conn.execute(query_tpm, {"model": target_model}).scalar() # 3. RPD query_rpd = text(""" SELECT COUNT(*) FROM api_logs WHERE model_name = :model AND request_timestamp >= CURDATE() """) metrics["rpd"] = conn.execute(query_rpd, {"model": target_model}).scalar() except Exception as e: print(f"メトリクス取得エラー: {e}") return metrics # ========================================== # 2. データベース接続 # ========================================== # DB接続はログイン判定でも使うため、関数の外（またはキャッシュ関数）で定義推奨ですが、 # 今回はシンプルにここで定義します。 try: db_str = 'mysql+pymysql://' + st.secrets["DB_USER"] + ':' + st.secrets["DB_PASSWORD"] + '@' + st.secrets["DB_HOST"] + '/' + st.secrets["DB_NAME"] + '?charset=utf8mb4' engine = create_engine(db_str) except Exception as e: st.error(f"データベース接続設定エラー: {e}") st.stop() # ========================================== # 3. ログイン機能の実装 # ========================================== def hash_password(password): """パスワードをSHA-256でハッシュ化""" return hashlib.sha256(password.encode()).hexdigest() def login(): st.title("🔒 SkgDB-TtoS ログイン") # ログインフォーム with st.form("login_form"): user_id = st.text_input("ユーザーID") password = st.text_input("パスワード", type="password") submit_button = st.form_submit_button("ログイン") if submit_button: hashed_input = hash_password(password) # SQLインジェクション対策のためパラメータバインドを使用 query = text("SELECT user_name FROM account WHERE user_id = :uid AND password = :pw") try: with engine.connect() as conn: result = conn.execute(query, {"uid": user_id, "pw": hashed_input}).fetchone() if result: # ログイン成功 st.session_state["is_logged_in"] = True st.session_state["user_info"] = {"id": user_id, "name": result[0]} st.success("ログイン成功") st.rerun() else: st.error("IDまたはパスワードが間違っています。") except Exception as e: st.error(f"ログイン処理エラー: {e}") def logout(): st.session_state["is_logged_in"] = False st.session_state["user_info"] = None st.session_state["messages"] = [] st.rerun() # ========================================== # 追加機能: パスワード変更ロジック # ========================================== def change_password(user_id, current_pw, new_pw, new_pw_confirm): # 1. 入力チェック if not current_pw or not new_pw or not new_pw_confirm: st.error("全ての項目を入力してください。") return False if new_pw != new_pw_confirm: st.error("新しいパスワードが一致しません。") return False if len(new_pw) < 4: # 最低文字数制限（任意） st.error("パスワードは4文字以上にしてください。") return False # 2. 現在のパスワードが正しいか確認 hashed_current = hash_password(current_pw) try: with engine.connect() as conn: # DBから現在のハッシュを取得 query_check = text("SELECT password FROM account WHERE user_id = :uid") result = conn.execute(query_check, {"uid": user_id}).fetchone() if not result or result[0] != hashed_current: st.error("現在のパスワードが間違っています。") return False # 3. 新しいパスワードで更新 hashed_new = hash_password(new_pw) query_update = text("UPDATE account SET password = :new_pw WHERE user_id = :uid") conn.execute(query_update, {"new_pw": hashed_new, "uid": user_id}) conn.commit() # 重要: コミットしないと反映されません return True except Exception as e: st.error(f"データベースエラー: {e}") return False # ========================================== # 4. メインアプリケーション (ログイン後) # ========================================== def main_app(): st.title(f"SkgDB データ抽出アプリケーション") # サイドバー設定 with st.sidebar: st.write(f"👤 **{st.session_state['user_info']['name']}** さん") if st.button("ログアウト"): logout() # --- 【追加】パスワード変更フォーム --- with st.expander("🔑 パスワード変更"): with st.form("password_change_form"): current_pw_input = st.text_input("現在のパスワード", type="password") new_pw_input = st.text_input("新しいパスワード", type="password") new_pw_conf_input = st.text_input("新しいパスワード(確認)", type="password") btn_change = st.form_submit_button("変更を実行") if btn_change: user_id = st.session_state["user_info"]["id"] if change_password(user_id, current_pw_input, new_pw_input, new_pw_conf_input): st.success("パスワードを変更しました！") # 念のためフォームを空にする等の処理を入れたい場合はrerunしますが、 # successメッセージを見せるためにそのままでもOKです。 st.divider() # --- 【追加】データ突合用アップローダー --- st.subheader("📂 データ突合 (Excel/CSV)") uploaded_file = st.file_uploader("照合したいファイルをアップロード", type=["csv", "xlsx"]) # APIキー取得 if "GOOGLE_API_KEY" in st.secrets: api_key = st.secrets["GOOGLE_API_KEY"] else: api_key = st.text_input("Gemini API Key", type="password") # ========================================== # (追加) サイドバーに稼働状況を表示 # ========================================== with st.sidebar: st.divider() st.subheader("⚙️ モデル設定") # 【追加】モデル選択ボックス selected_model_key = st.selectbox( "使用モデル", options=list(MODEL_CONFIG.keys()), format_func=lambda x: MODEL_CONFIG[x]["name"] # 表示名をリッチにする ) # 選択されたモデルの設定値を取得 config = MODEL_CONFIG[selected_model_key] st.divider() st.subheader(f"📊 稼働状況 ({config['name']})") # 選択されたモデルのメトリクスを集計 metrics = get_api_metrics(selected_model_key) # リミット値も設定から取得して動的に表示 limit_rpm = config["rpm_limit"] limit_tpm = config["tpm_limit"] limit_rpd = config["rpd_limit"] # RPM表示 st.write(f"**RPM (1分間の回数)**: {metrics['rpm']} / {limit_rpm}") st.progress(float(min(metrics['rpm'] / limit_rpm, 1.0))) # float()を追加 # TPM表示 st.write(f"**TPM (1分間のトークン)**: {metrics['tpm']:,} / {limit_tpm:,}") st.progress(float(min(metrics['tpm'] / limit_tpm, 1.0))) # float()を追加 # RPD表示 st.write(f"**RPD (本日の回数)**: {metrics['rpd']} / {limit_rpd}") st.progress(float(min(metrics['rpd'] / limit_rpd, 1.0))) # float()を追加 st.divider() # 履歴クリアボタン if st.button("🗑️ 会話履歴をクリア"): st.session_state["messages"] = [] st.rerun() if not api_key: st.warning("左のサイドバーにAPIキーを入力してください。") st.stop() # Geminiの設定 genai.configure(api_key=api_key) # ※ gemini-2.5-flash は執筆時点で未公開または限定公開の可能性があります。 # エラーが出る場合は 'gemini-1.5-flash' に変更してください。 model = genai.GenerativeModel(selected_model_key) #client = genai.Client(api_key=api_key) # ========================================== # LLMへの指示書 (DBスキーマ定義) # ========================================== DB_SCHEMA = """ あなたはMySQLの専門家です。以下のテーブル定義に基づいて、ユーザーの質問に対するSQLクエリを作成してください。 [テーブル定義] -- テーブル: 91pref（都道府県マスタ） CREATE TABLE `91pref` ( `県コード` varchar(2) NOT NULL DEFAULT '', -- keishin_index.県コード, keishin_header.県コードと結合 `県名` varchar(4) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- テーブル: 92koji（工種マスタ） CREATE TABLE `92koji` ( `工事の種類` tinyint(2) NOT NULL DEFAULT '0', -- keishin_index.工事の種類, keishin_header.工事の種類, keishin_koshu.工事の種類と結合 `工事名` varchar(30) NOT NULL DEFAULT '' -- 工事名は、「XX工事」ではなく「XX」で格納されている ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- テーブル: keishin_index（経審データ_直近のみ） -- 新旧区分カラム: 0=旧経審データ, 1=新経審データ -- 許可区分カラム: 空欄or0=なし, 1=一般許可, 2=特定許可 -- 完成工事高_激変緩和措置カラム: 0=2年平均, 1=3年平均 -- 自己資本額_激変緩和措置カラム: 0=単独, 1=2年平均 -- 決算区分カラム: 0=単独決算, 1=連結決算, 99=不明 CREATE TABLE `keishin_index` ( `新旧区分` tinyint(1) NOT NULL DEFAULT '0', `ID` varchar(9) NOT NULL DEFAULT '', -- keishin_header.IDと結合 `許可番号` varchar(9) NOT NULL DEFAULT '00-000000', `審査基準日` date NOT NULL DEFAULT '0000-00-00', `HDER` varchar(12) NOT NULL DEFAULT '', -- keishin_koshu.HDERと結合 `企業名` varchar(90) NOT NULL DEFAULT '', `代表者名` varchar(36) NOT NULL DEFAULT '', `郵便番号` varchar(8) NOT NULL DEFAULT '000-0000', `住所` varchar(130) NOT NULL DEFAULT '', `電話番号` varchar(15) NOT NULL DEFAULT '', `県コード` varchar(2) NOT NULL DEFAULT '', -- 91pref.県コードと結合 `市区町村コード` varchar(5) NOT NULL DEFAULT '00000', `行政庁記入欄` varchar(50) DEFAULT NULL, `資本金` int(10) NOT NULL DEFAULT '0', -- 単位:千円 `完成工事高比率` float(5,2) NOT NULL DEFAULT '0.00', `売上高` bigint(20) UNSIGNED NOT NULL DEFAULT '0', -- 単位:千円 `売上総利益` bigint(20) DEFAULT NULL, -- 単位:千円 `受取利息配当金` int(10) DEFAULT NULL, -- 単位:千円 `支払利息` int(10) DEFAULT NULL, -- 単位:千円 `経常利益` int(10) DEFAULT '0', -- 単位:千円 `営業キャッシュフロー当期` bigint(20) DEFAULT NULL, -- 単位:千円 `営業キャッシュフロー前期` bigint(20) DEFAULT NULL, -- 単位:千円 `固定資産当期` bigint(20) DEFAULT '0', -- 単位:千円 `固定資産前期` bigint(20) DEFAULT '0', -- 単位:千円 `流動負債` bigint(20) DEFAULT NULL, -- 単位:千円 `固定負債` bigint(20) DEFAULT '0', -- 単位:千円 `利益剰余金` bigint(20) DEFAULT NULL, -- 単位:千円 `自己資本` bigint(20) DEFAULT '0', -- 単位:千円 `総資本当期` bigint(20) DEFAULT '0', -- 単位:千円 `総資本前期` bigint(20) DEFAULT '0', -- 単位:千円 `許可区分` tinyint(1) UNSIGNED DEFAULT NULL, `工事の種類` tinyint(2) UNSIGNED ZEROFILL DEFAULT NULL, -- 92koji.工事の種類, keishin_koshu.工事の種類と結合 `総合評点P` smallint(5) DEFAULT '0', `完成工事高_激変緩和措置` tinyint(1) UNSIGNED DEFAULT NULL, `自己資本額_激変緩和措置` tinyint(1) UNSIGNED DEFAULT NULL, `評点X1` smallint(5) UNSIGNED DEFAULT '0', `平均完成工事高` bigint(20) DEFAULT '0', -- 単位:千円 `評点X2` smallint(4) UNSIGNED DEFAULT '0', `自己資本額点数X21` smallint(4) DEFAULT NULL, `自己資本額数値` bigint(20) DEFAULT '0', -- 単位:千円 `平均利益額X22` smallint(4) DEFAULT NULL, `平均利益額数値` bigint(20) DEFAULT NULL, -- 単位:千円 `評点Y` smallint(4) UNSIGNED DEFAULT '0', `決算区分` tinyint(1) UNSIGNED DEFAULT NULL, `純支払利息比率X1` float(9,3) DEFAULT '0.000', `負債回転期間X2` float(9,3) DEFAULT NULL, `総資本売上総利益率X3` float(9,3) DEFAULT NULL, `売上高経常利益率X4` float(9,3) DEFAULT NULL, `自己資本対固定資産比率X5` float(9,3) DEFAULT '0.000', `自己資本比率X6` float(9,3) DEFAULT NULL, `営業キャッシュフローX7` float(9,3) DEFAULT NULL, `利益剰余金X8` float(9,3) DEFAULT NULL, `評点Z` smallint(5) UNSIGNED DEFAULT '0', `一級技術者数` smallint(5) UNSIGNED DEFAULT '0', `一級監理受講者数` smallint(5) DEFAULT NULL, `監理技術者補佐数` smallint(5) DEFAULT NULL, `基幹技能者数` smallint(5) DEFAULT NULL, `二級技術者数` smallint(5) DEFAULT NULL, `その他技術者数` smallint(5) DEFAULT NULL, `元請完成工事高` bigint(20) DEFAULT NULL, `評点W` smallint(5) DEFAULT '0', `雇用保険` varchar(6) DEFAULT NULL, -- 外=適応外,有,無,適応外,除外,空欄,NULL `健康保険` varchar(6) DEFAULT NULL, -- 有,無,除外,空欄,NULL `厚生年金保険` varchar(6) DEFAULT NULL, -- 有,無,除外,空欄,NULL `健康保険及び厚生年金保険` varchar(6) DEFAULT NULL, -- 外=適応外,有,無,適応外,除外,空欄,NULL `建設業退職金共済制度` varchar(6) DEFAULT NULL, -- 有,無 `退職一時金制度` varchar(6) DEFAULT NULL, -- 有,無,NULL `企業年金制度` varchar(6) DEFAULT NULL, -- 有,無,NULL `退職一時金若しくは企業年金制度` varchar(6) DEFAULT NULL, -- 有,無,NULL `法定外労働災害補償制度` varchar(6) DEFAULT NULL, -- 有,無 `労働福祉の状況W1` smallint(3) DEFAULT NULL, `営業年数` int(3) UNSIGNED DEFAULT '0', `民事再生法又は会社更生法の適用の有無` varchar(6) DEFAULT NULL, -- 有,無,NULL `建設業の営業年数W2` smallint(3) DEFAULT NULL, `防災協定有無` varchar(6) DEFAULT '', -- 有,無,空欄 `防災活動` tinyint(2) UNSIGNED DEFAULT '0', `営業停止処分の有無` varchar(6) NOT NULL DEFAULT '', -- 有,無,空欄 `指示処分の有無` varchar(6) NOT NULL DEFAULT '', -- 有,無,空欄 `法令遵守の状況W4` smallint(3) NOT NULL DEFAULT '0', `監査の受審状況` varchar(10) NOT NULL DEFAULT '', -- 会計参与,会計監査人,自主監査,無,空欄 `公認会計士等の数` smallint(5) NOT NULL DEFAULT '0', `二級登録経理試験合格者の数` smallint(5) NOT NULL DEFAULT '0', `建設業の経理の状況W5` smallint(3) NOT NULL DEFAULT '0', `研究開発費` bigint(20) NOT NULL DEFAULT '0', -- 単位:千円 `研究開発の状況W6` smallint(3) NOT NULL DEFAULT '0', `建設機械の所有及びリース台数` smallint(5) UNSIGNED DEFAULT NULL, `建設機械の保有状況W7` smallint(3) DEFAULT NULL, `ISO9001の登録の有無` varchar(6) DEFAULT NULL, -- 有,無,NULL `ISO14001の登録の有無` varchar(6) DEFAULT NULL, -- 有,無,NULL `エコアクション21の認証の有無` varchar(6) DEFAULT NULL, -- 有,無,空欄,NULL `ISOが定めた規格による登録の状況W8` smallint(3) DEFAULT NULL, `若年技術職員の継続的な育成及び確保` varchar(6) DEFAULT NULL, -- 該当,非該当,空欄,NULL `新規若年技術職員の育成及び確保` varchar(6) DEFAULT NULL, -- 該当,非該当,空欄,NULL `若年の技術者及び技能労働者の育成及び確保の状況W9` smallint(3) DEFAULT NULL, `CPD単位取得数` smallint(5) DEFAULT NULL, `技術者数` smallint(5) DEFAULT NULL, `レベル向上者数` smallint(5) DEFAULT NULL, `技能者数` smallint(5) DEFAULT NULL, `控除対象者数` smallint(5) DEFAULT NULL, `知識及び技術又は技能の向上に関する取組の状況W10` smallint(3) DEFAULT NULL, `女性の職業生活における活躍の推進に関する法律に基づく認定の状況` varchar(16) DEFAULT NULL, -- 「えるぼし」の事。えるぼし１,えるぼし２,えるぼし３,プラチナえるぼし,非該当,NULL `次世代育成支援対策推進法に基づく認定の状況` varchar(16) DEFAULT NULL, -- 「くるみん」の事。くるみん,トライくるみん,プラチナくるみん,非該当,NULL `青少年の雇用の促進等に関する法律に基づく認定の状況` varchar(16) DEFAULT NULL, -- ユースエール,非該当,NULL `建設工事に従事する者の就業履歴を蓄積するために必要な措置の実施状況` varchar(16) DEFAULT NULL, -- 公共工事,公共・民間工事,非該当,空欄,NULL `建設工事の担い手の育成及び確保に関する取組の状況W1` smallint(3) DEFAULT NULL, `審査基準年度` smallint(4) UNSIGNED DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- テーブル: keishin_header（経審データ_通期） -- 新旧区分カラム: 0=旧経審データ, 1=新経審データ -- 許可区分カラム: 空欄or0=なし, 1=一般許可, 2=特定許可 -- 完成工事高_激変緩和措置カラム: 0=2年平均, 1=3年平均 -- 自己資本額_激変緩和措置カラム: 0=単独, 1=2年平均 -- 決算区分カラム: 0=単独決算, 1=連結決算, 99=不明 CREATE TABLE `keishin_header` ( `新旧区分` tinyint(1) NOT NULL DEFAULT '0', `ID` varchar(9) NOT NULL DEFAULT '', -- keishin_index.IDと結合 `許可番号` varchar(9) NOT NULL DEFAULT '00-000000', `審査基準日` date NOT NULL DEFAULT '0000-00-00', `HDER` varchar(12) NOT NULL DEFAULT '', -- keishin_koshu.HDERと結合 `企業名` varchar(90) NOT NULL DEFAULT '', `代表者名` varchar(36) NOT NULL DEFAULT '', `郵便番号` varchar(8) NOT NULL DEFAULT '000-0000', `住所` varchar(130) NOT NULL DEFAULT '', `電話番号` varchar(15) NOT NULL DEFAULT '', `県コード` varchar(2) NOT NULL DEFAULT '', -- 91pref.県コードと結合 `市区町村コード` varchar(5) NOT NULL DEFAULT '00000', `行政庁記入欄` varchar(50) DEFAULT NULL, `資本金` int(10) NOT NULL DEFAULT '0', -- 単位:千円 `完成工事高比率` float(5,2) NOT NULL DEFAULT '0.00', `売上高` bigint(20) UNSIGNED NOT NULL DEFAULT '0', -- 単位:千円 `売上総利益` bigint(20) DEFAULT NULL, -- 単位:千円 `受取利息配当金` int(10) DEFAULT NULL, -- 単位:千円 `支払利息` int(10) DEFAULT NULL, -- 単位:千円 `経常利益` int(10) DEFAULT '0', -- 単位:千円 `営業キャッシュフロー当期` bigint(20) DEFAULT NULL, -- 単位:千円 `営業キャッシュフロー前期` bigint(20) DEFAULT NULL, -- 単位:千円 `固定資産当期` bigint(20) DEFAULT '0', -- 単位:千円 `固定資産前期` bigint(20) DEFAULT '0', -- 単位:千円 `流動負債` bigint(20) DEFAULT NULL, -- 単位:千円 `固定負債` bigint(20) DEFAULT '0', -- 単位:千円 `利益剰余金` bigint(20) DEFAULT NULL, -- 単位:千円 `自己資本` bigint(20) DEFAULT '0', -- 単位:千円 `総資本当期` bigint(20) DEFAULT '0', -- 単位:千円 `総資本前期` bigint(20) DEFAULT '0', -- 単位:千円 `許可区分` tinyint(1) UNSIGNED DEFAULT NULL, `工事の種類` tinyint(2) UNSIGNED ZEROFILL DEFAULT NULL, -- 92koji.工事の種類, keishin_koshu.工事の種類と結合 `総合評点P` smallint(5) DEFAULT '0', `完成工事高_激変緩和措置` tinyint(1) UNSIGNED DEFAULT NULL, `自己資本額_激変緩和措置` tinyint(1) UNSIGNED DEFAULT NULL, `評点X1` smallint(5) UNSIGNED DEFAULT '0', `平均完成工事高` bigint(20) DEFAULT '0', -- 単位:千円 `評点X2` smallint(4) UNSIGNED DEFAULT '0', `自己資本額点数X21` smallint(4) DEFAULT NULL, `自己資本額数値` bigint(20) DEFAULT '0', -- 単位:千円 `平均利益額X22` smallint(4) DEFAULT NULL, `平均利益額数値` bigint(20) DEFAULT NULL, -- 単位:千円 `評点Y` smallint(4) UNSIGNED DEFAULT '0', `決算区分` tinyint(1) UNSIGNED DEFAULT NULL, `純支払利息比率X1` float(9,3) DEFAULT '0.000', `負債回転期間X2` float(9,3) DEFAULT NULL, `総資本売上総利益率X3` float(9,3) DEFAULT NULL, `売上高経常利益率X4` float(9,3) DEFAULT NULL, `自己資本対固定資産比率X5` float(9,3) DEFAULT '0.000', `自己資本比率X6` float(9,3) DEFAULT NULL, `営業キャッシュフローX7` float(9,3) DEFAULT NULL, `利益剰余金X8` float(9,3) DEFAULT NULL, `評点Z` smallint(5) UNSIGNED DEFAULT '0', `一級技術者数` smallint(5) UNSIGNED DEFAULT '0', `一級監理受講者数` smallint(5) DEFAULT NULL, `監理技術者補佐数` smallint(5) DEFAULT NULL, `基幹技能者数` smallint(5) DEFAULT NULL, `二級技術者数` smallint(5) DEFAULT NULL, `その他技術者数` smallint(5) DEFAULT NULL, `元請完成工事高` bigint(20) DEFAULT NULL, `評点W` smallint(5) DEFAULT '0', `雇用保険` varchar(6) DEFAULT NULL, -- 外=適応外,有,無,適応外,除外,空欄,NULL `健康保険` varchar(6) DEFAULT NULL, -- 有,無,除外,空欄,NULL `厚生年金保険` varchar(6) DEFAULT NULL, -- 有,無,除外,空欄,NULL `健康保険及び厚生年金保険` varchar(6) DEFAULT NULL, -- 外=適応外,有,無,適応外,除外,空欄,NULL `建設業退職金共済制度` varchar(6) DEFAULT NULL, -- 有,無 `退職一時金制度` varchar(6) DEFAULT NULL, -- 有,無,NULL `企業年金制度` varchar(6) DEFAULT NULL, -- 有,無,NULL `退職一時金若しくは企業年金制度` varchar(6) DEFAULT NULL, -- 有,無,NULL `法定外労働災害補償制度` varchar(6) DEFAULT NULL, -- 有,無 `労働福祉の状況W1` smallint(3) DEFAULT NULL, `営業年数` int(3) UNSIGNED DEFAULT '0', `民事再生法又は会社更生法の適用の有無` varchar(6) DEFAULT NULL, -- 有,無,NULL `建設業の営業年数W2` smallint(3) DEFAULT NULL, `防災協定有無` varchar(6) DEFAULT '', -- 有,無,空欄 `防災活動` tinyint(2) UNSIGNED DEFAULT '0', `営業停止処分の有無` varchar(6) NOT NULL DEFAULT '', -- 有,無,空欄 `指示処分の有無` varchar(6) NOT NULL DEFAULT '', -- 有,無,空欄 `法令遵守の状況W4` smallint(3) NOT NULL DEFAULT '0', `監査の受審状況` varchar(10) NOT NULL DEFAULT '', -- 会計参与,会計監査人,自主監査,無,空欄 `公認会計士等の数` smallint(5) NOT NULL DEFAULT '0', `二級登録経理試験合格者の数` smallint(5) NOT NULL DEFAULT '0', `建設業の経理の状況W5` smallint(3) NOT NULL DEFAULT '0', `研究開発費` bigint(20) NOT NULL DEFAULT '0', -- 単位:千円 `研究開発の状況W6` smallint(3) NOT NULL DEFAULT '0', `建設機械の所有及びリース台数` smallint(5) UNSIGNED DEFAULT NULL, `建設機械の保有状況W7` smallint(3) DEFAULT NULL, `ISO9001の登録の有無` varchar(6) DEFAULT NULL, -- 有,無,NULL `ISO14001の登録の有無` varchar(6) DEFAULT NULL, -- 有,無,NULL `エコアクション21の認証の有無` varchar(6) DEFAULT NULL, -- 有,無,空欄,NULL `ISOが定めた規格による登録の状況W8` smallint(3) DEFAULT NULL, `若年技術職員の継続的な育成及び確保` varchar(6) DEFAULT NULL, -- 該当,非該当,空欄,NULL `新規若年技術職員の育成及び確保` varchar(6) DEFAULT NULL, -- 該当,非該当,空欄,NULL `若年の技術者及び技能労働者の育成及び確保の状況W9` smallint(3) DEFAULT NULL, `CPD単位取得数` smallint(5) DEFAULT NULL, `技術者数` smallint(5) DEFAULT NULL, `レベル向上者数` smallint(5) DEFAULT NULL, `技能者数` smallint(5) DEFAULT NULL, `控除対象者数` smallint(5) DEFAULT NULL, `知識及び技術又は技能の向上に関する取組の状況W10` smallint(3) DEFAULT NULL, `女性の職業生活における活躍の推進に関する法律に基づく認定の状況` varchar(16) DEFAULT NULL, -- 「えるぼし」の事。えるぼし１,えるぼし２,えるぼし３,プラチナえるぼし,非該当,NULL `次世代育成支援対策推進法に基づく認定の状況` varchar(16) DEFAULT NULL, -- 「くるみん」の事。くるみん,トライくるみん,プラチナくるみん,非該当,NULL `青少年の雇用の促進等に関する法律に基づく認定の状況` varchar(16) DEFAULT NULL, -- ユースエール,非該当,NULL `建設工事に従事する者の就業履歴を蓄積するために必要な措置の実施状況` varchar(16) DEFAULT NULL, -- 公共工事,公共・民間工事,非該当,空欄,NULL `建設工事の担い手の育成及び確保に関する取組の状況W1` smallint(3) DEFAULT NULL, `審査基準年度` smallint(4) UNSIGNED DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- テーブル: keishin_koshu（経審データ_工種別） -- 許可区分カラム: 空欄=なし, 般=一般許可, 特=特定許可 -- 完成工事高_激変緩和措置カラム: 0=2年平均, 1=3年平均 CREATE TABLE `keishin_koshu` ( `K_HDER` varchar(14) NOT NULL DEFAULT '', `HDER` varchar(12) NOT NULL DEFAULT '', -- keishin_index.HDER, keishin_header.HDERと結合 `申請区分` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', `許可区分` varchar(2) NOT NULL DEFAULT '', `工事の種類` tinyint(2) UNSIGNED ZEROFILL NOT NULL DEFAULT '00', `総合評点P` smallint(4) NOT NULL DEFAULT '0', `完成工事高_激変緩和措置` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', `平均完成工事高` bigint(20) NOT NULL DEFAULT '0', `評点X1` smallint(5) NOT NULL DEFAULT '0', `一級技術者数` smallint(5) NOT NULL DEFAULT '0', `一級監理受講者数` smallint(11) DEFAULT NULL, `監理技術者補佐数` smallint(11) DEFAULT NULL, `基幹技能者数` smallint(11) DEFAULT NULL, `二級技術者数` smallint(5) NOT NULL DEFAULT '0', `その他技術者数` smallint(5) NOT NULL DEFAULT '0', `評点Z` smallint(5) NOT NULL DEFAULT '0', `元請完成工事高` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; -- テーブル: label_list（メタデータラベル） -- label: DX認定,ON-SITE X,スーパーゼネコン,三重県建設業協会,京都府建設業協会,佐賀県建設業協会,兵庫県建設業協会,北海道建設業協会,千葉県建設業協会,埼玉県建設業協会,大分県建設業協会,大阪建設業協会,奈良県建設業協会,宮城県建設業協会,宮崎県建設業協会,富山県建設業協会,山形県建設業協会,山梨県建設業協会,岐阜県建設業協会,島根県建設業協会,広島県建設業協会,建設ディレクター,徳島県建設業協会,愛知県建設業協会,新潟県建設業協会,日建連,東京建設業協会,栃木県建設業協会,滋賀県建設業協会,石川県建設業協会,神奈川県建設業協会,福井県建設業協会,福岡県建設業協会,福島県建設業協会,秋田県建設業協会,群馬県建設業協会,茨城県建設業協会,長野県建設業協会,青森県建設業協会,静岡県建設業協会,香川県建設業協会,高知県建設業協会,鹿児島県建設業協会 CREATE TABLE `label_list` ( `LB_HDER` int(10) UNSIGNED ZEROFILL NOT NULL, `ID` varchar(9) NOT NULL, -- keishin_index.ID, keishin_header.IDと結合 `企業名` varchar(128) NOT NULL, `label` varchar(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; [制約事項] - SQLコードのみを出力してください。Markdownタグ（```sql など）は不要です。 - データの変更・削除（INSERT, UPDATE, DELETE, DROP）は禁止です。SELECTのみ使用してください。 - 出力は必ず実行可能なSQL文1つだけにしてください。説明は不要です。 - 抽出条件に審査基準日の指定がない場合、審査基準日が過去2年より新しいデータになるように条件を指定してください。 - 工事名を指定する場合、「XX工事」ではなく「XX」で指定してください。（例：建築一式工事⇒建築一式） - 基本情報を出すよう指示された場合は、ID、企業名、代表者名、郵便番号、住所、電話番号を出力してください。 """ # セッションステートで会話履歴を初期化 if "messages" not in st.session_state: st.session_state["messages"] = [] # ========================================== # コアロジック # ========================================== # def get_sql_with_context(model_instance, current_question, model_name): # history_text = "" # for msg in st.session_state["messages"]: # role = "User" if msg["role"] == "user" else "AI" # content = msg["content"] # history_text += f"{role}: {content}\n" # # prompt = f""" # {DB_SCHEMA} # # [これまでの会話履歴] # {history_text} # # [今回の質問] # User: {current_question} # # [SQL] # """ # try: # #response = model_instance.generate_content(model=model_name, contents=prompt) # response = model_instance.generate_content(prompt) # # # --- (追加) ログ保存処理 --- # # usage_metadata からトークン数を取得 # # 入力トークン + 出力トークン # if response.usage_metadata: # usage = response.usage_metadata # total_tokens = usage.prompt_token_count + usage.candidates_token_count # # # DBに保存 # user_id = st.session_state["user_info"]["id"] # log_api_usage(user_id, total_tokens, model_name) # # ------------------------ # # sql = response.text.strip().replace("```sql", "").replace("```", "").strip() # return sql # except Exception as e: # return f"ERROR: {e}" # ========================================== # コアロジック (リスト検索対応版) # ========================================== # 引数に search_list (検索用リスト) を追加 def get_sql_with_match(model_instance, current_question, model_name, search_col_names=None, search_value_combinations=None): history_text = "" for msg in st.session_state["messages"]: role = "User" if msg["role"] == "user" else "AI" content = msg["content"] history_text += f"{role}: {content}\n" # 検索リストがある場合のプロンプト生成 match_instruction = "" if search_value_combinations and search_col_names: # 複数カラム名を表示 (例: "企業名" と "電話番号") cols_str = ", ".join(search_col_names) # 値の組み合わせを SQLの IN句の形式に変換 # Pythonのタプル文字列表記 (val1, val2) を利用しつつ、NoneはNULL扱いにケアが必要だが、 # Pandasでdropna済み前提であればそのまま文字列化で概ねOK # 例: ('A社', '03-0000'), ('B社', '06-0000') ... val_strs = [] for row in search_value_combinations: # 値をシングルクォートで囲む処理 formatted_row = [] for v in row: formatted_row.append(f"'{str(v)}'") val_strs.append(f"({', '.join(formatted_row)})") # IN句の中身を結合 in_clause_values = ", ".join(val_strs) # 複合キーのWHERE句指示を作成 # MySQLでは WHERE (col1, col2) IN ((v1, v2), (v3, v4)) が有効 match_instruction = f""" [重要: データ突合モード] ユーザーはアップロードしたファイルの以下の列をキーとしてDBと照合したいと考えています。 キー列: {cols_str} 以下の値の組み合わせに該当するデータのみを抽出するSQLを作成してください。 WHERE句で、タプル構文を使用した IN 句を使ってください。 例: WHERE (DB側の列1, DB側の列2) IN {in_clause_values} 【制約】 1. 結合キーとなるカラムは、必ずSELECT句に含めてください。 2. 可能であれば、SELECT句のカラムエイリアス(AS)を使って、アップロードファイルの列名「{cols_str}」と一致させてください。 """ prompt = f""" {DB_SCHEMA} {match_instruction} [これまでの会話履歴] {history_text} [今回の質問] User: {current_question} [SQL] """ try: # プロンプトのみ渡す（古いSDKの場合） response = model_instance.generate_content(prompt) # ログ保存 (省略: 既存コードのまま) if hasattr(response, 'usage_metadata'): usage = response.usage_metadata total_tokens = usage.prompt_token_count + usage.candidates_token_count user_id = st.session_state["user_info"]["id"] log_api_usage(user_id, total_tokens, model_name) sql = response.text.strip().replace("```sql", "").replace("```", "").strip() return sql except Exception as e: return f"ERROR: {e}" def execute_sql(sql_query): forbidden = ["DROP", "DELETE", "UPDATE", "INSERT", "ALTER", "TRUNCATE"] if any(word in sql_query.upper() for word in forbidden): return None, "セキュリティ警告: データの変更・削除操作は許可されていません。" try: df = pd.read_sql(sql_query, engine) return df, None except Exception as e: return None, f"SQL実行エラー: {e}" # ========================================== # UI表示 # ========================================== # --- 1. ファイルアップロード時の処理 --- upload_df = None search_cols = [] # リストに変更 search_vals = None if uploaded_file: st.info("📂 ファイルがアップロードされました。データ突合モードで動作します。") try: if uploaded_file.name.endswith('.csv'): upload_df = pd.read_csv(uploaded_file) else: upload_df = pd.read_excel(uploaded_file) with st.expander("アップロードデータのプレビュー", expanded=True): st.dataframe(upload_df.head()) # 【変更】複数選択に対応 (multiselect) st.markdown("#### DBと照合するキー項目を選んでください（複数可）") search_cols = st.multiselect("照合に使用する列名", upload_df.columns) if search_cols: # 指定された列のデータのみ抽出し、欠損除去・重複排除 # values.tolist() で [[val1, val2], [val3, val4]...] の形にする unique_df = upload_df[search_cols].dropna().drop_duplicates() search_vals = unique_df.values.tolist() st.write(f"照合対象パターン: {len(search_vals)} 通り") st.write(f"例: {search_vals[:3]} ...") except Exception as e: st.error(f"ファイル読み込みエラー: {e}") # --- 2. 履歴表示 --- for i, msg in enumerate(st.session_state["messages"]): with st.chat_message(msg["role"]): if msg["role"] == "user": # ユーザーのメッセージはそのまま表示 st.write(msg["content"]) else: # AIのメッセージ if "data" in msg: # 成功時（データがある場合） # SQLをExpanderの中に隠す with st.expander("生成されたSQLを確認する", expanded=False): st.code(msg["content"], language="sql") # データを表示 st.dataframe(msg["data"]) # ダウンロードボタンを配置 # 【重要】keyにインデックス(i)を使って、ボタンIDが重複しないようにする csv = msg["data"].to_csv(index=False).encode('utf-8_sig') st.download_button( label="📥 CSVダウンロード", data=csv, file_name=f"data_{i}.csv", mime="text/csv", key=f"download_btn_{i}" ) else: # エラーメッセージなどの場合 st.error(msg["content"]) # --- 3. 入力フォーム --- # モードによってガイドメッセージを変える placeholder_text = "質問を入力してください" if upload_df is not None and search_cols: col_names = "/".join(search_cols) placeholder_text = f"【突合モード】キー「{col_names}」にマッチするデータに対して、何を追加したいですか？" user_input = st.chat_input(placeholder_text) if user_input: st.chat_message("user").write(user_input) st.session_state["messages"].append({"role": "user", "content": user_input}) with st.spinner("AIが思考＆データ結合中..."): # 突合モードかどうか if upload_df is not None and search_cols and search_vals: # 【変更】リストとカラム名のリストを渡す generated_sql = get_sql_with_match(model, user_input, selected_model_key, search_cols, search_vals) else: generated_sql = get_sql_with_match(model, user_input, selected_model_key) # SQL実行 result_df, error_msg = execute_sql(generated_sql) if error_msg: st.session_state["messages"].append({"role": "assistant", "content": error_msg}) else: final_df = result_df # --- データ結合処理 (Merge) --- if upload_df is not None and search_cols and not result_df.empty: try: # 結合キーの型を強制的に文字列(String)に合わせる # (片方がint, 片方がstrだと結合できないため) # アップロードデータのキー列を文字列化 for col in search_cols: upload_df[col] = upload_df[col].astype(str) # DB結果データのキー列も文字列化 # ※AIがカラム名を変えている可能性があるため、 # upload_dfのキーと同じ名前のカラムがあればそれを使用し、 # なければ「たぶんこれだろう」と推測して変換するのは危険なので、 # 今回は「AIにアップロードファイルと同じ列名を出力させている」前提で進めます。 merge_keys_db = [] for col in search_cols: if col in result_df.columns: result_df[col] = result_df[col].astype(str) merge_keys_db.append(col) else: # 同じ名前のカラムがない場合、結合キー不足として警告 st.warning(f"警告: DB抽出結果にキー列 '{col}' が見つかりませんでした。AIが別名で出力している可能性があります。") # 両方に存在するキーだけで結合 valid_keys = [k for k in search_cols if k in result_df.columns] if valid_keys: # 複数キーでLeft Join final_df = pd.merge( upload_df, result_df, on=valid_keys, how='left', suffixes=('_元', '_追加') ) st.toast(f"{len(valid_keys)}個のキーで結合しました！") else: st.error("結合できる共通のカラム名が見つかりませんでした。") final_df = result_df except Exception as e: st.warning(f"データの結合に失敗しました: {e}") final_df = result_df # 履歴保存 st.session_state["messages"].append({ "role": "assistant", "content": generated_sql, "data": final_df }) st.rerun() # ========================================== # 5. アプリケーション実行制御 # ========================================== if __name__ == "__main__": if st.session_state["is_logged_in"]: main_app() else: login()