import sys
import os
import sqlite3
import pandas as pd
import numpy as np
from datetime import datetime, timedelta, timezone
import re
import pyodbc
import shutil
import requests
import json
import math
import glob
import io
import subprocess
import logging
from logging.handlers import RotatingFileHandler
import traceback
from PyQt6.QtWidgets import (QApplication, QMainWindow, QTabWidget, QWidget,
QVBoxLayout, QHBoxLayout, QPushButton, QLabel,
QFileDialog, QTableWidget, QTableWidgetItem, QComboBox,
QDateEdit, QMessageBox, QGroupBox, QHeaderView, QTextEdit,
QLineEdit, QCheckBox, QFormLayout, QScrollArea, QMenu, QGridLayout,
QSystemTrayIcon, QStyle, QRadioButton, QButtonGroup, QFrame, QDateTimeEdit,QGraphicsView,QGraphicsScene)
from PyQt6.QtCore import QDate, Qt, QPoint, QTimer, QDateTime,QSettings
from PyQt6.QtGui import QAction, QColor, QFont, QPixmap, QImageReader
# ==========================================
# ログ設定 (長期稼働用のエラー記録)
# ==========================================
# まず一番最初にベースとなるディレクトリパスを取得
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
log_formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
# ここでBASE_DIRを使って絶対パスにする（二重定義しない）
log_handler = RotatingFileHandler(os.path.join(BASE_DIR, 'weather_system.log'), maxBytes=5*1024*1024, backupCount=3, encoding='utf-8')
log_handler.setFormatter(log_formatter)
logger = logging.getLogger()
logger.setLevel(logging.INFO)
logger.addHandler(log_handler)
class FilterableTableWidget(QTableWidget):
def __init__(self, rows, columns):
super().__init__(rows, columns)
self.horizontalHeader().sectionClicked.connect(self.show_filter_menu)
self.filters = {}
self.setStyleSheet("QHeaderView::section { background-color: #F6F8FA; padding: 5px; border: 1px solid #D0D7DE; }")
def keyPressEvent(self, event):
if event.key() == Qt.Key.Key_C and (event.modifiers() & Qt.KeyboardModifier.ControlModifier):
self.copy_selection()
else:
super().keyPressEvent(event)
def copy_selection(self):
selection = self.selectedIndexes()
if not selection: return
rows = sorted(list(set(idx.row() for idx in selection)))
cols = sorted(list(set(idx.column() for idx in selection)))
copy_text = ""
for r in rows:
row_data = []
for c in cols:
item = self.item(r, c)
if item and self.isItemSelected(item):
row_data.append(item.text().replace("\n", " "))
else:
row_data.append("")
copy_text += "\t".join(row_data) + "\n"
QApplication.clipboard().setText(copy_text)
def show_filter_menu(self, logical_index):
menu = QMenu(self)
values = set()
for row in range(self.rowCount()):
item = self.item(row, logical_index)
if item: values.add(item.text())
values = sorted(list(values))
action_all = menu.addAction("すべて表示 (フィルタ解除)")
menu.addSeparator()
for val in values: menu.addAction(val)
header_pos = self.horizontalHeader().sectionViewportPosition(logical_index)
global_pos = self.mapToGlobal(self.horizontalHeader().pos() + QPoint(header_pos, self.horizontalHeader().height()))
selected_action = menu.exec(global_pos)
if not selected_action: return
if selected_action == action_all:
if logical_index in self.filters: del self.filters[logical_index]
else:
self.filters[logical_index] = selected_action.text()
self.apply_filters()
def apply_filters(self):
for row in range(self.rowCount()):
hidden = False
for col, filter_val in self.filters.items():
item = self.item(row, col)
if item and item.text() != filter_val:
hidden = True; break
self.setRowHidden(row, hidden)
class ZoomableView(QGraphicsView):
def __init__(self, scene, parent=None):
super().__init__(scene, parent)
self.setDragMode(QGraphicsView.DragMode.ScrollHandDrag)
self.setTransformationAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)
self.setResizeAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)
def wheelEvent(self, event):
zoom_in_factor = 1.15
zoom_out_factor = 1.0 / zoom_in_factor
if event.angleDelta().y() > 0:
zoom_factor = zoom_in_factor
else:
zoom_factor = zoom_out_factor
self.scale(zoom_factor, zoom_factor)
class WeatherVerificationApp(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("気象予報精度検証システム (Ver 22.1 - パス記憶・ギャップ検知強化版)")
self.resize(1500, 950)
self.really_quit = False
# --- 設定の保存・読み込み用 ---
self.settings = QSettings("WeatherApp", "VerificationApp")
# 多重実行防止フラグ
self._is_fetching_ms = False
self._is_fetching_api = False
self._is_fetching_fcst = False
self._is_backing_up = False
self.setStyleSheet("""
/* OSのダークモード干渉を防ぐため、全体の基本文字色と背景色を強制指定 */
QWidget { color: #333333; font-family: 'Segoe UI', 'Meiryo', sans-serif; }
QMainWindow, QScrollArea, QScrollArea > QWidget > QWidget { background-color: #F4F6F9; }
QTabWidget::pane { border: 1px solid #D0D7DE; background: white; border-radius: 8px; }
QTabBar::tab { background: #FFD180; color: #555; padding: 10px 20px; margin-right: 2px; border-top-left-radius: 8px; border-top-right-radius: 8px; font-weight: bold; }
QTabBar::tab:selected { background: #FF8C00; color: white; border-bottom: 2px solid #E65100; }
QGroupBox { font-weight: bold; color: #E65100; border: 1px solid #D0D7DE; border-radius: 6px; margin-top: 15px; background-color: #FFFFFF; }
QGroupBox::title { subcontrol-origin: margin; left: 10px; padding: 0 5px; color: #E65100; }
QPushButton { background-color: #FF8C00; color: white; border: none; border-radius: 5px; padding: 8px 16px; font-weight: bold; }
QPushButton:hover { background-color: #F57C00; }
QPushButton:pressed { background-color: #E65100; }
QPushButton:disabled { background-color: #A0A0A0; color: #E0E0E0; }
QPushButton#ActionBtn { background-color: #28A745; }
QPushButton#ActionBtn:hover { background-color: #218838; }
QPushButton#TestBtn { background-color: #007BFF; }
QPushButton#TestBtn:hover { background-color: #0069D9; }
QPushButton#AutoBtn { background-color: #D73A49; }
QPushButton#QuitBtn { background-color: #CB2431; }
/* フォーム入力欄の文字色を明示的に指定（白文字化対策） */
QLineEdit, QComboBox, QDateEdit, QDateTimeEdit { border: 1px solid #D0D7DE; border-radius: 4px; padding: 5px; background-color: #FAFBFC; color: #333333; min-height: 25px; }
QDateEdit, QDateTimeEdit { min-width: 140px; font-size: 13px; }
QComboBox { min-width: 120px; }
/* テーブルウィジェットとヘッダーの文字色も明示的に指定 */
QTableWidget { gridline-color: #E1E4E8; border: 1px solid #D0D7DE; font-size: 13px; background-color: #FFFFFF; color: #333333; }
QHeaderView::section { background-color: #F6F8FA; color: #333333; padding: 5px; border: 1px solid #D0D7DE; }
QScrollArea { border: none; background-color: transparent; }
""")
self.tray_icon = QSystemTrayIcon(self)
self.tray_icon.setIcon(self.style().standardIcon(QStyle.StandardPixmap.SP_ComputerIcon))
self.tray_icon.setToolTip("気象検証システム - 継続抽出・計算中")
tray_menu = QMenu()
show_action = QAction("画面を表示する", self)
show_action.triggered.connect(self.showNormal)
quit_action = QAction("完全に終了する", self)
quit_action.triggered.connect(self.force_quit)
tray_menu.addAction(show_action); tray_menu.addSeparator(); tray_menu.addAction(quit_action)
self.tray_icon.setContextMenu(tray_menu); self.tray_icon.show()
self.tray_icon.activated.connect(self.on_tray_icon_activated)
# ★★★ 修正ポイント: QSettingsから各パスの読み込みを行う ★★★
# 冒頭で定義したBASE_DIRを使って絶対パス化する
default_db = os.path.join(BASE_DIR, "weather_verification(削除禁止).db")
self.db_path = self.settings.value("last_db_path", default_db)
self.last_forecast_folder = self.settings.value("last_forecast_folder", "未設定")
self.backup_folder = self.settings.value("last_backup_folder", "未設定")
self.t1_auto_timer = QTimer(self); self.t1_auto_timer.timeout.connect(self.extract_and_aggregate_obs)
self.t2_auto_timer = QTimer(self); self.t2_auto_timer.timeout.connect(self.fetch_amedas_api)
self.t3_auto_timer = QTimer(self); self.t3_auto_timer.timeout.connect(self.auto_load_forecast_model_folder)
if hasattr(self, 'setup_tray_icon'):
self.setup_tray_icon()
self.init_db()
self.init_ui()
logging.info("システム起動完了")
QTimer.singleShot(500, self.ask_auto_start_all)
def ask_auto_start_all(self):
reply = QMessageBox.question(
self, "自動起動の確認",
"システムが起動しました。\n以下の自動取得を同時に開始しますか？\n\n・MS観測値抽出 (10分毎)\n・アメダスAPI取得 (1時間毎)\n・予報モデル抽出 (10分毎)",
QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No
)
if reply == QMessageBox.StandardButton.Yes:
if not self.t1_auto_timer.isActive(): self.toggle_t1_auto()
if not self.t2_auto_timer.isActive(): self.toggle_t2_auto()
# 予報フォルダが設定されている場合のみ起動
if self.last_forecast_folder != "未設定" and not self.t3_auto_timer.isActive():
self.toggle_t3_auto()
elif self.last_forecast_folder == "未設定":
QMessageBox.warning(self, "警告", "予報モデルのフォルダが未設定のため、予報自動抽出のみ開始をスキップしました。")
def on_tray_icon_activated(self, reason):
if reason == QSystemTrayIcon.ActivationReason.DoubleClick:
self.showNormal(); self.activateWindow()
def closeEvent(self, event):
if not self.really_quit:
event.ignore(); self.hide()
self.tray_icon.showMessage("バックグラウンドで実行中", "自動抽出・計算処理は継続しています。\n完全に終了する場合は右クリックまたは完全終了ボタンから終了してください。", QSystemTrayIcon.MessageIcon.Information, 3000)
else:
logging.info("システムを完全に終了します")
event.accept()
def force_quit(self):
self.really_quit = True
QApplication.quit()
def select_db(self):
path, _ = QFileDialog.getOpenFileName(self, "SQLiteデータベースを選択", "", "SQLite DB (*.db);;すべてのファイル (*)")
if path:
self.db_path = path
# ★ 修正ポイント: 変更したDBパスを記憶
self.settings.setValue("last_db_path", self.db_path)
self.lbl_db_path.setText(f"📁 現在のDB: {os.path.basename(path)} (※内部データ時間軸: UTC世界標準時で統一)")
try:
self.conn.close(); self.init_db()
self.update_tab1_station_combo(); self.update_tab5_station_combo()
self.refresh_source_combos()
# ★ 追加: タブ7のギャップ検知用テーブルリストも更新
self.update_gap_table_combo()
logging.info(f"データベースを切り替えました: {path}")
QMessageBox.information(self, "完了", f"データベースを切り替えました。\n{path}")
except Exception as e:
logging.error(f"データベースの切り替えに失敗しました: {e}", exc_info=True)
QMessageBox.critical(self, "エラー", f"データベースの読み込みに失敗しました:\n{e}")
def jst_date_range_to_utc_str(self, qdate_from, qdate_to):
dt_from = datetime.combine(qdate_from.date().toPyDate(), datetime.min.time()) - timedelta(hours=9)
dt_to = datetime.combine(qdate_to.date().toPyDate(), datetime.max.time().replace(second=0, microsecond=0)) - timedelta(hours=9)
return dt_from.strftime("%Y-%m-%d %H:%M"), dt_to.strftime("%Y-%m-%d %H:%M")
def create_fcst_table_if_not_exists(self, table_name):
if not table_name.startswith("FCST_"): return
cursor = self.conn.cursor()
cursor.execute(f'''CREATE TABLE IF NOT EXISTS {table_name} (IssueDay TEXT, IssueTime TEXT, TargetDay TEXT, TargetTime TEXT, MsCd TEXT, ForecastStep INTEGER, FcstRainfall REAL, FcstSnowfall REAL, PRIMARY KEY (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep))''')
self.conn.commit()
def init_db(self):
# タイムアウト時間を延長（排他制御時の待機時間を確保）
self.conn = sqlite3.connect(self.db_path, timeout=15.0)
# 長期連続稼働における安定性（排他制御・ロック回避）のためにWALモードを有効化
self.conn.execute("PRAGMA journal_mode=WAL;")
self.conn.execute("PRAGMA synchronous=NORMAL;")
cursor = self.conn.cursor()
cursor.execute("DROP TABLE IF EXISTS FCST")
self.create_fcst_table_if_not_exists("FCST_saiteki")
self.create_fcst_table_if_not_exists("FCST_rankup")
cursor.execute('''CREATE TABLE IF NOT EXISTS JMA_FCST (TargetStart TEXT, TargetEnd TEXT, Region TEXT, Element TEXT, RawText TEXT, MinVal REAL, MaxVal REAL, PRIMARY KEY (TargetStart, TargetEnd, Region, Element))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS GPV_Points (PointID INTEGER PRIMARY KEY AUTOINCREMENT, Lon REAL, Lat REAL, UNIQUE(Lon, Lat))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS OBS (ObsDay TEXT, ObsTime TEXT, MsCd TEXT, Rainfall1h REAL, Snowfall1h REAL, SnowDepth REAL, PRIMARY KEY (ObsDay, ObsTime, MsCd))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS OBS_GP (ObsDay TEXT, ObsTime TEXT, MsCd TEXT, Rainfall1h REAL, Snowfall1h REAL, SnowDepth REAL, PRIMARY KEY (ObsDay, ObsTime, MsCd))''')
cursor.execute('''CREATE TABLE IF NOT EXISTS ManualExtractSettings (TemplateId INTEGER PRIMARY KEY, TableName TEXT, SheetName TEXT, RainKw TEXT, SnowKw TEXT)''')
cursor.execute("SELECT COUNT(*) FROM ManualExtractSettings")
if cursor.fetchone()[0] == 0:
cursor.execute("INSERT INTO ManualExtractSettings VALUES (1, 'FCST_type01', 'シート名', '(3)から自動整合', '天気整合')")
cursor.execute("PRAGMA table_info(TargetStations)")
tgt_cols = [r[1] for r in cursor.fetchall()]
if not tgt_cols or "TemplateId" not in tgt_cols:
cursor.execute("DROP TABLE IF EXISTS TargetStations")
cursor.execute('''CREATE TABLE TargetStations (TemplateId INTEGER, FcstName TEXT, PRIMARY KEY (TemplateId, FcstName))''')
cursor.execute("SELECT COUNT(*) FROM TargetStations")
if cursor.fetchone()[0] == 0:
base_targets = ["大通り・円山・山鼻", "屯田・篠路", "苗穂・元町・栄町", "菊水・白石・南郷", "大谷地・新札幌", "豊平・平岸・月寒", "真駒内・澄川・藻岩下", "琴似・発寒", "前田・山口", "北野・清田・里塚", "花川・花畔・新港", "恵庭", "石山・常盤", "野幌・江別"]
tgt_defaults = []
for tid in range(1, 6):
for name in base_targets:
tgt_defaults.append((tid, name))
cursor.executemany("INSERT INTO TargetStations VALUES (?,?)", tgt_defaults)
cursor.execute("PRAGMA table_info(Mapping)")
map_cols = [r[1] for r in cursor.fetchall()]
if not map_cols or "TemplateId" not in map_cols:
cursor.execute("DROP TABLE IF EXISTS Mapping")
cursor.execute('''CREATE TABLE Mapping (TemplateId INTEGER, ObsCd TEXT, FcstName TEXT, PRIMARY KEY (TemplateId, ObsCd))''')
cursor.execute("SELECT COUNT(*) FROM Mapping")
if cursor.fetchone()[0] == 0:
base_mappings = [
("MS1", "大通り・円山・山鼻"), ("MS2", "屯田・篠路"), ("MS3", "苗穂・元町・栄町"),
("MS4", "菊水・白石・南郷"), ("MS5", "大谷地・新札幌"), ("MS6", "豊平・平岸・月寒"),
("MS7", "真駒内・澄川・藻岩下"), ("MS8", "琴似・発寒"), ("MS9", "前田・山口"),
("MS25", "北野・清田・里塚"), ("アメダス:札幌", "大通り・円山・山鼻"),
("アメダス:石狩", "花川・花畔・新港"), ("アメダス:恵庭島松", "恵庭"),
("アメダス:小金湯", "石山・常盤"), ("アメダス:江別", "野幌・江別"),
("アメダス:手稲山口", "前田・山口")
]
map_defaults = []
for tid in range(1, 6):
for obs, fcst in base_mappings:
map_defaults.append((tid, obs, fcst))
cursor.executemany("INSERT INTO Mapping VALUES (?,?,?)", map_defaults)
self.conn.commit()
def refresh_source_combos(self):
if not hasattr(self, 'cmb_source'): return
cursor = self.conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'FCST_%' ORDER BY name")
tables = [r[0] for r in cursor.fetchall()]
for cmb in [self.cmb_source, self.ex_cmb_source]:
current = cmb.currentText()
cmb.blockSignals(True)
cmb.clear()
cmb.addItem("気象庁手入力フォーマット (JMA_FCST)")
for t in tables:
cmb.addItem(f"{t} (Excel抽出)")
idx = cmb.findText(current)
if idx >= 0: cmb.setCurrentIndex(idx)
else: cmb.setCurrentIndex(1 if cmb.count() > 1 else 0)
cmb.blockSignals(False)
if hasattr(self, 'cmb_preview_table'):
current_prev = self.cmb_preview_table.currentText()
self.cmb_preview_table.blockSignals(True)
self.cmb_preview_table.clear()
for t in tables: self.cmb_preview_table.addItem(t)
idx = self.cmb_preview_table.findText(current_prev)
if idx >= 0: self.cmb_preview_table.setCurrentIndex(idx)
self.cmb_preview_table.blockSignals(False)
self.update_fcst_list(self.cmb_source.currentText())
def create_scroll_tab(self):
scroll = QScrollArea(); scroll.setWidgetResizable(True); inner_widget = QWidget(); scroll.setWidget(inner_widget); return scroll, inner_widget
def init_ui(self):
main_widget = QWidget()
main_layout = QVBoxLayout(main_widget)
top_header = QHBoxLayout()
# ★修正: basename ではなくフルパス (self.db_path) をそのまま表示する
self.lbl_db_path = QLabel(f"📁 現在のDB: {self.db_path} \n(※内部データ時間軸: UTC世界標準時で統一)")
self.lbl_db_path.setStyleSheet("font-weight: bold; color: #444;")
btn_db = QPushButton("⚙️ DBを選択 / 変更")
btn_db.clicked.connect(self.select_db)
btn_quit = QPushButton("❌ 完全に終了する")
btn_quit.setObjectName("QuitBtn")
btn_quit.clicked.connect(self.force_quit)
top_header.addWidget(self.lbl_db_path); top_header.addStretch()
top_header.addWidget(btn_db); top_header.addWidget(btn_quit)
main_layout.addLayout(top_header)
self.tabs = QTabWidget()
main_layout.addWidget(self.tabs)
self.setCentralWidget(main_widget)
self.setup_tab1(); self.setup_tab2()
self.setup_tab3(); self.setup_tab4(); self.setup_tab5(); self.setup_tab6(); self.setup_tab7()
self.refresh_source_combos()
# ==========================================
# タブ1: MS観測値
# ==========================================
def setup_tab1(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_db = QGroupBox("SQL Server 接続設定 ＆ データ抽出 (※取得時にJSTからUTCへ自動変換してDB保存されます)"); db_layout = QVBoxLayout(); db_layout.setContentsMargins(15, 25, 15, 15)
header_layout = QHBoxLayout()
self.lbl_t1_auto = QLabel(""); self.lbl_t1_auto.setStyleSheet("color: #D73A49; font-weight: bold; font-size: 14px;")
header_layout.addStretch(); header_layout.addWidget(self.lbl_t1_auto); db_layout.addLayout(header_layout)
form_layout = QFormLayout()
self.cmb_driver = QComboBox(); self.cmb_driver.setEditable(True); self.cmb_driver.addItems(["SQL Server", "ODBC Driver 17 for SQL Server"])
self.txt_server = QLineEdit("172."); self.txt_db = QLineEdit("S"); self.txt_user = QLineEdit("sa"); self.txt_pwd = QLineEdit("")
self.txt_pwd.setEchoMode(QLineEdit.EchoMode.Password)
form_layout.addRow("ODBC:", self.cmb_driver); form_layout.addRow("Host:", self.txt_server)
form_layout.addRow("DB:", self.txt_db); form_layout.addRow("User:", self.txt_user); form_layout.addRow("Pass:", self.txt_pwd)
db_layout.addLayout(form_layout)
filter_layout = QHBoxLayout(); self.t1_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.t1_date_from.setCalendarPopup(True)
self.t1_date_to = QDateEdit(QDate.currentDate()); self.t1_date_to.setCalendarPopup(True)
filter_layout.addWidget(QLabel("抽出期間 (JST):")); filter_layout.addWidget(self.t1_date_from); filter_layout.addWidget(QLabel("～")); filter_layout.addWidget(self.t1_date_to); filter_layout.addStretch()
db_layout.addLayout(filter_layout)
action_layout = QHBoxLayout()
btn_test_conn = QPushButton("🔌 接続テスト"); btn_test_conn.setObjectName("TestBtn"); btn_test_conn.clicked.connect(self.test_sql_connection)
btn_extract = QPushButton("手動で抽出して保存"); btn_extract.setObjectName("ActionBtn"); btn_extract.clicked.connect(self.extract_and_aggregate_obs)
self.btn_t1_auto = QPushButton("MS自動取得を開始 (10分毎)"); self.btn_t1_auto.setObjectName("AutoBtn"); self.btn_t1_auto.clicked.connect(self.toggle_t1_auto)
action_layout.addWidget(btn_test_conn); action_layout.addWidget(btn_extract); action_layout.addWidget(self.btn_t1_auto); action_layout.addStretch()
db_layout.addLayout(action_layout); group_db.setLayout(db_layout)
group_view = QGroupBox("ローカルDB (OBS) 検索・閲覧ビューア (※期間指定はJST、画面表示時もJSTに逆変換されます)"); view_layout = QVBoxLayout(); view_layout.setContentsMargins(15, 25, 15, 15)
search_layout = QHBoxLayout(); self.v_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.v_date_from.setCalendarPopup(True)
self.v_date_to = QDateEdit(QDate.currentDate()); self.v_date_to.setCalendarPopup(True)
self.v_cmb_station = QComboBox(); self.update_tab1_station_combo()
btn_view_search = QPushButton("検索"); btn_view_search.clicked.connect(self.search_obs_table)
search_layout.addWidget(QLabel("表示期間 (JST):")); search_layout.addWidget(self.v_date_from); search_layout.addWidget(QLabel("～")); search_layout.addWidget(self.v_date_to)
search_layout.addWidget(QLabel("地点:")); search_layout.addWidget(self.v_cmb_station); search_layout.addWidget(btn_view_search); search_layout.addStretch()
view_layout.addLayout(search_layout)
self.tbl_obs = FilterableTableWidget(0, 6)
self.tbl_obs.setHorizontalHeaderLabels(["観測日(JST)▽", "観測時間(JST)▽", "地点コード▽", "降水量(mm)▽", "降雪量(cm)▽", "積雪深(cm)▽"])
self.tbl_obs.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_obs.setMinimumHeight(350); view_layout.addWidget(self.tbl_obs); group_view.setLayout(view_layout)
layout.addWidget(group_db); layout.addWidget(group_view); self.tabs.addTab(scroll, "1. 観測値設定① (MS)")
def test_sql_connection(self):
try:
conn_str = self.get_sql_connection_string(); conn = pyodbc.connect(conn_str, timeout=5); conn.close()
QMessageBox.information(self, "導通点検成功", "SQL Server への接続に成功しました！")
except Exception as e: QMessageBox.critical(self, "導通点検エラー", f"接続失敗:\n{e}")
def toggle_t1_auto(self):
if self.t1_auto_timer.isActive():
self.t1_auto_timer.stop(); self.lbl_t1_auto.setText(""); self.btn_t1_auto.setText("MS自動取得を開始")
logging.info("MS自動取得タイマー停止")
else:
self.t1_auto_timer.start(600000); self.lbl_t1_auto.setText("🔴 MS自動取得中"); self.btn_t1_auto.setText("MS自動取得を停止")
logging.info("MS自動取得タイマー開始 (10分毎)")
def update_tab1_station_combo(self):
self.v_cmb_station.clear(); self.v_cmb_station.addItem("すべて")
cursor = self.conn.cursor(); cursor.execute("SELECT DISTINCT MsCd FROM OBS ORDER BY MsCd"); self.v_cmb_station.addItems([str(r[0]) for r in cursor.fetchall()])
def search_obs_table(self):
utc_start, utc_end = self.jst_date_range_to_utc_str(self.v_date_from, self.v_date_to)
st = self.v_cmb_station.currentText()
cursor = self.conn.cursor()
if st == "すべて":
cursor.execute("SELECT ObsDay, ObsTime, MsCd, Rainfall1h, Snowfall1h, SnowDepth FROM OBS WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (utc_start, utc_end))
else:
cursor.execute("SELECT ObsDay, ObsTime, MsCd, Rainfall1h, Snowfall1h, SnowDepth FROM OBS WHERE MsCd=? AND ObsDay || ' ' || ObsTime BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (st, utc_start, utc_end))
rows = cursor.fetchall(); self.tbl_obs.setRowCount(len(rows))
for r_i, r_data in enumerate(rows):
dt_utc = datetime.strptime(f"{r_data[0]} {r_data[1]}", "%Y-%m-%d %H:%M")
dt_jst = dt_utc + timedelta(hours=9)
self.tbl_obs.setItem(r_i, 0, QTableWidgetItem(dt_jst.strftime("%Y-%m-%d")))
self.tbl_obs.setItem(r_i, 1, QTableWidgetItem(dt_jst.strftime("%H:%M")))
self.tbl_obs.setItem(r_i, 2, QTableWidgetItem(str(r_data[2])))
self.tbl_obs.setItem(r_i, 3, QTableWidgetItem(str(r_data[3]) if r_data[3] is not None else ""))
self.tbl_obs.setItem(r_i, 4, QTableWidgetItem(str(r_data[4]) if r_data[4] is not None else ""))
self.tbl_obs.setItem(r_i, 5, QTableWidgetItem(str(r_data[5]) if r_data[5] is not None else ""))
def get_sql_connection_string(self):
return f"DRIVER={{{self.cmb_driver.currentText()}}};SERVER={self.txt_server.text()};DATABASE={self.txt_db.text()};UID={self.txt_user.text()};PWD={self.txt_pwd.text()}"
def extract_and_aggregate_obs(self):
if self._is_fetching_ms:
logging.warning("MS抽出が既に実行中のためスキップしました（重複実行防止）。")
return
self._is_fetching_ms = True
try:
d_from = self.t1_date_from.date().toString("yyyy-MM-dd")
d_to = self.t1_date_to.date().toString("yyyy-MM-dd")
conn_sql = pyodbc.connect(self.get_sql_connection_string())
query = f"""
SELECT ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth
FROM SNET.T_MS_Statistics
WHERE MsCd IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 25)
AND ObsDay BETWEEN '{d_from}' AND '{d_to}'
AND (ObsTime LIKE '%00')
"""
import warnings
with warnings.catch_warnings():
warnings.simplefilter("ignore")
df = pd.read_sql(query, conn_sql)
mask_2400 = df['ObsTime'].astype(str).str.strip() == '2400'
# 2. Pandasがエラーを起こさないように、一時的に '2400' を '0000' にすり替える
df.loc[mask_2400, 'ObsTime'] = '0000'
conn_sql.close()
if df.empty:
if not self.t1_auto_timer.isActive(): QMessageBox.warning(self, "通知", "指定期間のデータが存在しません。")
return
df['MsCd'] = ['MS' + str(x) for x in df['MsCd']]
df['datetime_str'] = df['ObsDay'].astype(str) + ' ' + df['ObsTime'].astype(str)
df['datetime_jst'] = pd.to_datetime(df['datetime_str'], errors='coerce')
df = df.dropna(subset=['datetime_jst'])
df.loc[mask_2400, 'datetime_jst'] += pd.Timedelta(days=1)
df['HourlyTime_UTC'] = df['datetime_jst'] - pd.Timedelta(hours=9)
df['ObsDay_UTC'] = df['HourlyTime_UTC'].dt.strftime('%Y-%m-%d')
df['ObsTime_UTC'] = df['HourlyTime_UTC'].dt.strftime('%H:%M')
cursor = self.conn.cursor()
inserted = 0
for _, row in df.iterrows():
sd = float(row['SnowDepth']) if not pd.isna(row['SnowDepth']) else None
sf = float(row['SnowfallP1']) if not pd.isna(row['SnowfallP1']) else 0.0
rf = float(row['RainfallP1']) if not pd.isna(row['RainfallP1']) else 0.0
cursor.execute("""
INSERT OR REPLACE INTO OBS
(ObsDay, ObsTime, MsCd, Rainfall1h, Snowfall1h, SnowDepth)
VALUES (?, ?, ?, ?, ?, ?)
""", (str(row['ObsDay_UTC']), str(row['ObsTime_UTC']), str(row['MsCd']), rf, sf, sd))
inserted += 1
self.conn.commit()
self.update_tab1_station_combo(); self.search_obs_table()
if not self.t1_auto_timer.isActive():
QMessageBox.information(self, "完了", f"MS抽出が完了しました。\nデータ数: {inserted} 件")
else:
logging.info(f"自動抽出: MSデータ {inserted}件 保存完了")
except Exception as e:
err_msg = f"MSデータ抽出エラー:\n{e}"
logging.error(err_msg, exc_info=True)
if self.t1_auto_timer.isActive():
self.tray_icon.showMessage("自動取得エラー", "MSデータの抽出に失敗しました。詳細はログを確認してください。", QSystemTrayIcon.MessageIcon.Warning, 5000)
else:
QMessageBox.critical(self, "エラー", err_msg)
finally:
self._is_fetching_ms = False
# ==========================================
# タブ2: アメダス抽出
# ==========================================
def setup_tab2(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_api = QGroupBox("気象庁 アメダスAPI (JSON) から自動取得")
api_layout = QVBoxLayout(); api_layout.setContentsMargins(15, 20, 15, 15)
lbl_api = QLabel("札幌圏のアメダスの最新データを取得し、UTC時間に変換して保存します。")
api_layout.addWidget(lbl_api)
h_api = QHBoxLayout()
btn_api_now = QPushButton("最新アメダスデータを1回取得"); btn_api_now.setObjectName("ActionBtn"); btn_api_now.clicked.connect(self.fetch_amedas_api)
self.btn_t2_auto = QPushButton("アメダスAPI自動取得開始 (1時間毎)"); self.btn_t2_auto.setObjectName("AutoBtn"); self.btn_t2_auto.clicked.connect(self.toggle_t2_auto)
self.lbl_t2_auto = QLabel(""); self.lbl_t2_auto.setStyleSheet("color: #D73A49; font-weight: bold;")
h_api.addWidget(btn_api_now); h_api.addWidget(self.btn_t2_auto); h_api.addWidget(self.lbl_t2_auto); h_api.addStretch()
api_layout.addLayout(h_api); group_api.setLayout(api_layout)
group_csv = QGroupBox("気象庁アメダス CSV取り込み (過去データ用)")
csv_layout = QHBoxLayout(); csv_layout.setContentsMargins(15, 20, 15, 15)
btn_csv = QPushButton("JMA CSVを選択してOBSへ取り込み"); btn_csv.setObjectName("TestBtn"); btn_csv.clicked.connect(self.import_jma_csv)
csv_layout.addWidget(btn_csv); csv_layout.addStretch(); group_csv.setLayout(csv_layout)
self.txt_log2 = QTextEdit(); self.txt_log2.setReadOnly(True); self.txt_log2.setText("ここに読込ログが表示されます..."); self.txt_log2.setMinimumHeight(350)
layout.addWidget(group_api); layout.addWidget(group_csv); layout.addWidget(self.txt_log2); layout.addStretch(); self.tabs.addTab(scroll, "2. アメダス観測値")
def toggle_t2_auto(self):
if self.t2_auto_timer.isActive():
self.t2_auto_timer.stop()
self.lbl_t2_auto.setText("")
self.btn_t2_auto.setText("アメダスAPI自動取得開始")
logging.info("アメダスAPI自動取得タイマー停止")
else:
self.t2_auto_timer.start(3600000)
self.lbl_t2_auto.setText("🔴 API自動取得中 (1時間毎)")
self.btn_t2_auto.setText("アメダス自動取得停止")
logging.info("アメダスAPI自動取得タイマー開始 (1時間毎)")
self.fetch_amedas_api()
def fetch_amedas_api(self):
if self._is_fetching_api:
logging.warning("アメダスAPI取得が既に実行中のためスキップしました（重複実行防止）。")
return
self._is_fetching_api = True
target_stations = {"14163": "札幌", "14206": "恵庭島松", "14116": "手稲山口", "14121": "石狩", "14136": "江別", "14157": "手稲山", "14191": "小金湯"}
try:
latest_time_url = "https://www.jma.go.jp/bosai/amedas/data/latest_time.txt"
res_time = requests.get(latest_time_url, timeout=10)
latest_time_iso = res_time.text.strip()
dt_jst_latest = datetime.fromisoformat(latest_time_iso)
dt_jst_floored = dt_jst_latest.replace(minute=0, second=0, microsecond=0)
target_times_jst = [dt_jst_floored - timedelta(hours=i) for i in range(48)]
target_times_jst.reverse()
cursor = self.conn.cursor()
inserted = 0
self.txt_log2.append(f"--- API取得開始 ---")
QApplication.processEvents()
for t_jst in target_times_jst:
time_str = t_jst.strftime("%Y%m%d%H0000")
data_url = f"https://www.jma.go.jp/bosai/amedas/data/map/{time_str}.json"
try:
res_data = requests.get(data_url, timeout=10)
if res_data.status_code != 200: continue
data_json = res_data.json()
except Exception as req_e:
logging.warning(f"アメダスデータ個別取得スキップ ({time_str}): {req_e}")
continue
t_utc = t_jst.astimezone(timezone.utc)
obs_day_utc = t_utc.strftime("%Y-%m-%d")
obs_time_utc = t_utc.strftime("%H:%M")
for st_code, st_name in target_stations.items():
if st_code in data_json:
st_data = data_json[st_code]
rain_list = st_data.get('precipitation1h') or st_data.get('precipitation') or [0.0]
try: rain = float(rain_list[0]) if rain_list[0] is not None else 0.0
except Exception: rain = 0.0
snow_list = st_data.get('snow') or [None]
try: snow_depth = float(snow_list[0]) if snow_list[0] is not None else None
except Exception: snow_depth = None
ms_cd = f"アメダス:{st_name}"
snowfall = 0.0
if snow_depth is not None:
cursor.execute("SELECT SnowDepth FROM OBS WHERE MsCd=? AND SnowDepth IS NOT NULL AND ObsDay || ' ' || ObsTime < ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 1", (ms_cd, f"{obs_day_utc} {obs_time_utc}"))
last_row = cursor.fetchone()
if last_row and last_row[0] is not None:
snowfall = max(0.0, float(snow_depth) - float(last_row[0]))
cursor.execute("""
INSERT OR REPLACE INTO OBS (ObsDay, ObsTime, MsCd, Rainfall1h, Snowfall1h, SnowDepth)
VALUES (?, ?, ?, ?, ?, ?)
""", (obs_day_utc, obs_time_utc, ms_cd, float(rain) if rain is not None else 0.0, snowfall, snow_depth))
inserted += 1
QApplication.processEvents()
self.conn.commit()
self.txt_log2.append(f"✅ 完了: {inserted} 件のデータをUTC統一軸で格納しました。")
self.update_tab1_station_combo()
if not self.t2_auto_timer.isActive():
QMessageBox.information(self, "完了", f"アメダスAPI取得完了。(UTC保存)\n保存件数: {inserted} 件")
else:
logging.info(f"自動抽出: アメダスAPIデータ {inserted}件 保存完了")
except Exception as e:
err_msg = f"API取得エラー: {e}"
self.txt_log2.append(f"❌ {err_msg}")
logging.error(err_msg, exc_info=True)
if self.t2_auto_timer.isActive():
self.tray_icon.showMessage("自動取得エラー", "アメダスAPIの取得に失敗しました。詳細はログを確認してください。", QSystemTrayIcon.MessageIcon.Warning, 5000)
else:
QMessageBox.critical(self, "エラー", err_msg)
finally:
self._is_fetching_api = False
def import_jma_csv(self):
path, _ = QFileDialog.getOpenFileName(self, "気象庁アメダスCSVを選択", "", "CSV Files (*.csv)")
if not path: return
try:
with open(path, 'r', encoding='shift_jis', errors='replace') as f:
lines = f.readlines()
header_idx = -1
for i, line in enumerate(lines[:15]):
if "年月日時" in line:
header_idx = i; break
if header_idx == -1 or header_idx == 0:
return QMessageBox.critical(self, "エラー", "CSVから「年月日時」の行が見つかりません。")
stations_raw = lines[header_idx - 1].strip('\n').split(',')
elements_raw = lines[header_idx].strip('\n').split(',')
current_st = ""
stations = []
for s in stations_raw:
if s.strip(): current_st = s.strip()
stations.append(current_st)
inserted = 0
cursor = self.conn.cursor()
self.txt_log2.append(f"--- CSV読込: {os.path.basename(path)} ---")
for line in lines[header_idx + 1:]:
cols = line.strip('\n').split(',')
if not cols[0].startswith("20"): continue
try:
dt_jst = pd.to_datetime(cols[0].strip())
dt_utc = dt_jst - timedelta(hours=9)
obs_day_utc = dt_utc.strftime("%Y-%m-%d")
obs_time_utc = dt_utc.strftime("%H:%M")
except: continue
station_data = {}
for col_idx in range(1, min(len(cols), len(stations), len(elements_raw))):
st = stations[col_idx]
el = elements_raw[col_idx]
val_str = cols[col_idx].strip()
if not st or not el: continue
if st not in station_data:
station_data[st] = {'rain': None, 'snow': None, 'snow_depth': None}
try: val = float(val_str) if val_str else 0.0
except: val = 0.0
if "降水量" in el and station_data[st]['rain'] is None: station_data[st]['rain'] = val
elif "降雪" in el and station_data[st]['snow'] is None: station_data[st]['snow'] = val
elif "積雪" in el and station_data[st]['snow_depth'] is None: station_data[st]['snow_depth'] = val
for st, vals in station_data.items():
r = vals['rain'] if vals['rain'] is not None else 0.0
s = vals['snow'] if vals['snow'] is not None else 0.0
sd = vals['snow_depth']
cursor.execute("""
INSERT OR REPLACE INTO OBS
(ObsDay, ObsTime, MsCd, Rainfall1h, Snowfall1h, SnowDepth)
VALUES (?, ?, ?, ?, ?, ?)
""", (obs_day_utc, obs_time_utc, f"アメダス:{st}", float(r), float(s), sd))
inserted += 1
self.conn.commit()
self.update_tab1_station_combo()
self.txt_log2.append(f"✅ 完了: {inserted} 件の観測データを保存しました。\n")
QMessageBox.information(self, "完了", f"CSV取り込みが完了しました。\n保存データ数: {inserted} 件")
except Exception as e:
logging.error(f"CSV読込失敗: {e}", exc_info=True)
QMessageBox.critical(self, "エラー", f"CSV読込失敗:\n{e}")
# ==========================================
# タブ3: 予報Excel抽出
# ==========================================
def setup_tab3(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
top_layout = QHBoxLayout()
left_panel = QVBoxLayout()
group_targets = QGroupBox("抽出対象の予報地点設定 (テンプレート機能)"); target_layout = QVBoxLayout(); target_layout.setContentsMargins(15, 20, 15, 15)
tgt_header_layout = QHBoxLayout(); self.cmb_template = QComboBox(); self.cmb_template.addItems([f"テンプレート{i}" for i in range(1, 6)])
self.cmb_template.currentIndexChanged.connect(self.load_target_stations)
tgt_header_layout.addWidget(QLabel("選択中:")); tgt_header_layout.addWidget(self.cmb_template); tgt_header_layout.addStretch()
self.lbl_t3_auto = QLabel(""); self.lbl_t3_auto.setStyleSheet("color: #D73A49; font-weight: bold; font-size: 14px;")
tgt_header_layout.addWidget(self.lbl_t3_auto); target_layout.addLayout(tgt_header_layout)
tbl_layout = QHBoxLayout(); self.tbl_targets = FilterableTableWidget(20, 1)
self.tbl_targets.setHorizontalHeaderLabels(["抽出対象に含める予報地点名▽"]); self.tbl_targets.horizontalHeader().setSectionResizeMode(0, QHeaderView.ResizeMode.Stretch)
self.tbl_targets.setMinimumHeight(200)
tgt_btn_layout = QVBoxLayout(); tgt_btn_layout.addWidget(QLabel("現在のテンプレートに保存")); btn_save_tgt = QPushButton("保存")
btn_save_tgt.setObjectName("ActionBtn"); btn_save_tgt.clicked.connect(self.save_target_stations)
tgt_btn_layout.addWidget(btn_save_tgt); tgt_btn_layout.addStretch()
tbl_layout.addWidget(self.tbl_targets); tbl_layout.addLayout(tgt_btn_layout); target_layout.addLayout(tbl_layout); group_targets.setLayout(target_layout)
group_op = QGroupBox("標準・ランクアップ・マニュアルモデル 一括自動抽出設定"); op_layout = QVBoxLayout(); op_layout.setContentsMargins(15, 20, 15, 15)
filter_layout = QHBoxLayout(); self.t3_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.t3_date_from.setCalendarPopup(True)
self.t3_date_to = QDateEdit(QDate.currentDate()); self.t3_date_to.setCalendarPopup(True)
filter_layout.addWidget(QLabel("発表日 (JST):")); filter_layout.addWidget(self.t3_date_from); filter_layout.addWidget(QLabel("～")); filter_layout.addWidget(self.t3_date_to); filter_layout.addStretch()
action_layout = QHBoxLayout(); btn_folder = QPushButton("手動でフォルダを指定")
btn_folder.setObjectName("ActionBtn"); btn_folder.clicked.connect(self.manual_load_forecast_model_folder)
self.btn_t3_auto = QPushButton("全モデル自動取得を開始"); self.btn_t3_auto.setObjectName("AutoBtn"); self.btn_t3_auto.clicked.connect(self.toggle_t3_auto)
action_layout.addWidget(btn_folder); action_layout.addWidget(self.btn_t3_auto); action_layout.addStretch(); op_layout.addLayout(filter_layout); op_layout.addLayout(action_layout); group_op.setLayout(op_layout)
left_panel.addWidget(group_targets); left_panel.addWidget(group_op)
right_panel = QVBoxLayout()
group_info = QGroupBox("抽出の要領と自動変換について"); info_layout = QVBoxLayout(); info_layout.setContentsMargins(15, 20, 15, 15)
self.lbl_folder_path = QLabel(f"📁 抽出先フォルダ: {self.last_forecast_folder}")
self.lbl_folder_path.setStyleSheet("font-weight: bold; color: #0366D6; padding-bottom: 5px;")
info_text = """<b>【抽出システム動作要領】</b><br>
① フォルダ内のExcelファイル名から「発表日時(JST)」を参照します。<br>
② 該当ファイルの「シート名」を開きます。<br>
③ 指定キーワードがある行を探し、その<b>隣の列の１行上</b>の「地点名」が、抽出対象リストと合致するか確認します。<br>
④ 合致した場合、同行から予報値を抽出します。<br>
⑤ 値はすべて内部UTC（-9時間）に自動変換されて指定のDBへ保存されます。<br>
※自動取得開始時は、下記で保存したマニュアル条件も並行して追従抽出されます。"""
lbl_info = QLabel(info_text); lbl_info.setWordWrap(True); lbl_info.setStyleSheet("background-color: #F6F8FA; border: 1px solid #D0D7DE; padding: 10px; border-radius: 5px;")
info_layout.addWidget(self.lbl_folder_path); info_layout.addWidget(lbl_info); info_layout.addStretch(); group_info.setLayout(info_layout)
group_manual = QGroupBox("マニュアル抽出設定 ＆ 実行 (任意追加モデル用)"); manual_layout = QVBoxLayout(); manual_layout.setContentsMargins(15, 15, 15, 15)
m_form = QGridLayout(); m_form.setSpacing(10)
self.txt_m_table = QLineEdit(); self.txt_m_table.setPlaceholderText("例: FCST_type01")
self.txt_m_sheet = QLineEdit(); self.txt_m_sheet.setPlaceholderText("例: ②類似モデル")
self.txt_m_rain = QLineEdit(); self.txt_m_rain.setPlaceholderText("例: (3)から自動整合")
self.txt_m_snow = QLineEdit(); self.txt_m_snow.setPlaceholderText("例: 天気整合")
m_form.addWidget(QLabel("保存先DB名 (※FCST_で始まる事):"), 0, 0); m_form.addWidget(self.txt_m_table, 0, 1)
m_form.addWidget(QLabel("抽出対象のシート名:"), 1, 0); m_form.addWidget(self.txt_m_sheet, 1, 1)
m_form.addWidget(QLabel("降水量検索キーワード:"), 2, 0); m_form.addWidget(self.txt_m_rain, 2, 1)
m_form.addWidget(QLabel("降雪量検索キーワード:"), 3, 0); m_form.addWidget(self.txt_m_snow, 3, 1)
m_btn_layout = QHBoxLayout()
btn_m_save = QPushButton("設定を保存"); btn_m_save.clicked.connect(self.save_manual_settings)
btn_m_exec = QPushButton("フォルダから抽出"); btn_m_exec.setObjectName("TestBtn"); btn_m_exec.clicked.connect(self.execute_manual_extraction)
btn_m_file = QPushButton("個別ファイルを選択して抽出"); btn_m_file.setObjectName("ActionBtn"); btn_m_file.clicked.connect(self.execute_manual_file_extraction)
m_btn_layout.addWidget(btn_m_save); m_btn_layout.addWidget(btn_m_exec); m_btn_layout.addWidget(btn_m_file); m_btn_layout.addStretch()
manual_layout.addLayout(m_form); manual_layout.addLayout(m_btn_layout); group_manual.setLayout(manual_layout)
right_panel.addWidget(group_info); right_panel.addWidget(group_manual)
top_layout.addLayout(left_panel, 5); top_layout.addLayout(right_panel, 5)
group_view = QGroupBox("データプレビュー (直近50件・JST変換表示)"); view_layout = QVBoxLayout(); view_layout.setContentsMargins(15, 20, 15, 15)
prev_h = QHBoxLayout()
self.cmb_preview_table = QComboBox(); self.cmb_preview_table.currentTextChanged.connect(lambda x: self.load_preview_data(x, self.tbl_fcst))
prev_h.addWidget(QLabel("プレビュー対象テーブル:")); prev_h.addWidget(self.cmb_preview_table); prev_h.addStretch()
view_layout.addLayout(prev_h)
self.tbl_fcst = FilterableTableWidget(0, 7)
self.tbl_fcst.setHorizontalHeaderLabels(["発表日(JST)▽", "発表時間(JST)▽", "対象日(JST)▽", "対象時間(JST)▽", "予報地点名▽", "予報降水量▽", "予報降雪量▽"])
self.tbl_fcst.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch); self.tbl_fcst.setMinimumHeight(250); view_layout.addWidget(self.tbl_fcst); group_view.setLayout(view_layout)
layout.addLayout(top_layout); layout.addWidget(group_view); self.tabs.addTab(scroll, "3. 予報値抽出設定")
self.load_target_stations(); self.load_manual_settings()
def manual_load_forecast_model_folder(self):
folder = QFileDialog.getExistingDirectory(self, "予報Excelフォルダを選択")
if folder:
self.last_forecast_folder = folder
self.lbl_folder_path.setText(f"📁 抽出先フォルダ:\n{folder}")
# ★追加: レジストリ/設定ファイルに記憶させる
self.settings.setValue("last_forecast_folder", folder)
self.process_forecast_folder(folder, silent=False)
def load_target_stations(self):
tid = self.cmb_template.currentIndex() + 1
cursor = self.conn.cursor()
cursor.execute("SELECT FcstName FROM TargetStations WHERE TemplateId = ? ORDER BY rowid", (tid,))
rows = cursor.fetchall(); self.tbl_targets.clearContents()
for i in range(20):
item = QTableWidgetItem(str(rows[i][0]) if i < len(rows) else "")
self.tbl_targets.setItem(i, 0, item)
def save_target_stations(self):
tid = self.cmb_template.currentIndex() + 1
cursor = self.conn.cursor(); cursor.execute("DELETE FROM TargetStations WHERE TemplateId = ?", (tid,))
for i in range(self.tbl_targets.rowCount()):
item = self.tbl_targets.item(i, 0)
if item and item.text().strip(): cursor.execute("INSERT INTO TargetStations VALUES (?, ?)", (tid, item.text().strip()))
self.conn.commit(); QMessageBox.information(self, "完了", f"テンプレート{tid} を保存しました。")
def load_manual_settings(self):
cursor = self.conn.cursor()
cursor.execute("SELECT TableName, SheetName, RainKw, SnowKw FROM ManualExtractSettings WHERE TemplateId=1")
row = cursor.fetchone()
if row:
self.txt_m_table.setText(row[0] if row[0] else "")
self.txt_m_sheet.setText(row[1] if row[1] else "")
self.txt_m_rain.setText(row[2] if row[2] else "")
self.txt_m_snow.setText(row[3] if row[3] else "")
def save_manual_settings(self):
table_name = self.txt_m_table.text().strip()
sheet_name = self.txt_m_sheet.text().strip()
if not table_name or not sheet_name:
return QMessageBox.warning(self, "エラー", "保存先DB名とシート名は必須です。")
if not table_name.startswith("FCST_"):
return QMessageBox.warning(self, "エラー", "保存先DB名は必ず 'FCST_' から始まる文字列にしてください。")
cursor = self.conn.cursor()
cursor.execute("INSERT OR REPLACE INTO ManualExtractSettings (TemplateId, TableName, SheetName, RainKw, SnowKw) VALUES (1, ?, ?, ?, ?)",
(table_name, sheet_name, self.txt_m_rain.text().strip(), self.txt_m_snow.text().strip()))
self.conn.commit()
self.refresh_source_combos()
QMessageBox.information(self, "保存", "マニュアル抽出設定を保存しました。")
def toggle_t3_auto(self):
if self.t3_auto_timer.isActive():
self.t3_auto_timer.stop(); self.lbl_t3_auto.setText(""); self.btn_t3_auto.setText("全モデル自動取得を開始")
logging.info("予報モデル一括抽出タイマー停止")
else:
if self.last_forecast_folder == "未設定": return QMessageBox.warning(self, "警告", "まずは「手動でフォルダを指定」を実行し、フォルダを記憶させてください。")
self.t3_auto_timer.start(600000); self.lbl_t3_auto.setText("🔴 予報自動取得中"); self.btn_t3_auto.setText("自動取得を停止")
logging.info(f"予報モデル一括抽出タイマー開始 (10分毎) フォルダ: {self.last_forecast_folder}")
self.auto_load_forecast_model_folder()
def manual_load_forecast_model_folder(self):
folder = QFileDialog.getExistingDirectory(self, "予報Excelフォルダを選択")
if folder: self.last_forecast_folder = folder; self.lbl_folder_path.setText(f"📁 抽出先フォルダ:\n{folder}"); self.process_forecast_folder(folder, silent=False)
def auto_load_forecast_model_folder(self):
if self.last_forecast_folder != "未設定": self.process_forecast_folder(self.last_forecast_folder, silent=True)
def extract_sheet_to_table(self, file_path, issue_dt_jst, target_sheet, target_table, rain_kw, snow_kw, valid_targets):
try:
excel_file = pd.ExcelFile(file_path)
if target_sheet not in excel_file.sheet_names:
return 0
# file_path の代わりに excel_file を直接渡すことで二重読み込みを防ぐ
df = pd.read_excel(excel_file, header=None, sheet_name=target_sheet)
except Exception as e:
logging.error(f"Excel read error ({file_path}): {e}")
return 0
current_mode = None
seen_rain_stations = set()
seen_snow_stations = set()
total_inserted = 0
cursor = self.conn.cursor()
cols_3h = 12
for row_idx in range(len(df)):
cat_str = str(df.iloc[row_idx, 0]).strip()
if cat_str == "nan": cat_str = ""
is_snow_trigger = (snow_kw in cat_str) if snow_kw else False
is_rain_trigger = (rain_kw in cat_str) if rain_kw else False
if is_snow_trigger: current_mode = "snow"
elif is_rain_trigger: current_mode = "rain"
elif cat_str != "": current_mode = None
if not current_mode: continue
rows_to_process = [row_idx]
if is_snow_trigger and current_mode == "snow" and row_idx > 0: rows_to_process = [row_idx - 1, row_idx]
elif is_rain_trigger and current_mode == "rain" and row_idx > 0: rows_to_process = [row_idx - 1, row_idx]
for r_idx in rows_to_process:
raw_station = str(df.iloc[r_idx, 1]).strip()
if not raw_station or raw_station == "nan": continue
clean_name = re.sub(r'^.*?[)）]','', raw_station).strip()
if clean_name not in valid_targets: continue
if current_mode == "rain":
if clean_name in seen_rain_stations: continue
seen_rain_stations.add(clean_name)
elif current_mode == "snow":
if clean_name in seen_snow_stations: continue
seen_snow_stations.add(clean_name)
for i in range(cols_3h):
col_idx = 2 + i
if col_idx >= len(df.columns): break
try: val = float(df.iloc[r_idx, col_idx]) if not pd.isna(df.iloc[r_idx, col_idx]) else 0.0
except ValueError: val = 0.0
target_dt_jst = issue_dt_jst + timedelta(hours=(i+1)*3)
issue_dt_utc = issue_dt_jst - timedelta(hours=9)
target_dt_utc = target_dt_jst - timedelta(hours=9)
i_day_u, i_time_u = issue_dt_utc.strftime("%Y-%m-%d"), issue_dt_utc.strftime("%H:%M")
t_day_u, t_time_u = target_dt_utc.strftime("%Y-%m-%d"), target_dt_utc.strftime("%H:%M")
if current_mode == "rain":
cursor.execute(f"INSERT OR REPLACE INTO {target_table} (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstRainfall, FcstSnowfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstSnowfall FROM {target_table} WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 3, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 3))
else:
cursor.execute(f"INSERT OR REPLACE INTO {target_table} (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstSnowfall, FcstRainfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstRainfall FROM {target_table} WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 3, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 3))
total_inserted += 1
base_12h_col = 2 + cols_3h
for j in range(3):
col_idx = base_12h_col + j
if col_idx >= len(df.columns): break
issue_time_jst = issue_dt_jst.strftime("%H:%M")
if issue_time_jst in ["09:00", "12:00"] and j == 0: continue
try: val = float(df.iloc[r_idx, col_idx]) if not pd.isna(df.iloc[r_idx, col_idx]) else 0.0
except ValueError: val = 0.0
if issue_time_jst == "18:00": target_dt_jst = issue_dt_jst + timedelta(hours=12*(j+1))
else: target_dt_jst = datetime(issue_dt_jst.year, issue_dt_jst.month, issue_dt_jst.day, 18, 0) + timedelta(hours=12*j)
issue_dt_utc = issue_dt_jst - timedelta(hours=9)
target_dt_utc = target_dt_jst - timedelta(hours=9)
i_day_u, i_time_u = issue_dt_utc.strftime("%Y-%m-%d"), issue_dt_utc.strftime("%H:%M")
t_day_u, t_time_u = target_dt_utc.strftime("%Y-%m-%d"), target_dt_utc.strftime("%H:%M")
if current_mode == "rain":
cursor.execute(f"INSERT OR REPLACE INTO {target_table} (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstRainfall, FcstSnowfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstSnowfall FROM {target_table} WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 12, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 12))
else:
cursor.execute(f"INSERT OR REPLACE INTO {target_table} (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep, FcstSnowfall, FcstRainfall) VALUES (?,?,?,?,?,?,?, COALESCE((SELECT FcstRainfall FROM {target_table} WHERE TargetDay=? AND TargetTime=? AND MsCd=? AND IssueDay=? AND IssueTime=? AND ForecastStep=?), 0.0))", (i_day_u, i_time_u, t_day_u, t_time_u, clean_name, 12, val, t_day_u, t_time_u, clean_name, i_day_u, i_time_u, 12))
total_inserted += 1
return total_inserted
def process_forecast_folder(self, folder_path, silent=False):
if self._is_fetching_fcst:
logging.warning("予報モデル一括抽出が既に実行中のためスキップしました（重複実行防止）。")
return
self._is_fetching_fcst = True
try:
d_from = self.t3_date_from.date().toString("yyyy-MM-dd")
d_to = self.t3_date_to.date().toString("yyyy-MM-dd")
tid = self.cmb_template.currentIndex() + 1; cursor = self.conn.cursor()
cursor.execute("SELECT FcstName FROM TargetStations WHERE TemplateId = ?", (tid,))
valid_targets = [str(r[0]) for r in cursor.fetchall()]
if not valid_targets:
if not silent: QMessageBox.warning(self, "警告", "現在のテンプレートの抽出対象リストが空です。")
return
cursor.execute("SELECT TableName, SheetName, RainKw, SnowKw FROM ManualExtractSettings WHERE TemplateId=1")
m_row = cursor.fetchone()
m_enabled = (m_row and m_row[0] and m_row[1])
total_inserted = 0
for filename in os.listdir(folder_path):
if not filename.endswith(".xlsx") or filename.startswith("~"): continue
if "コピー" in filename:
continue
file_path = os.path.join(folder_path, filename)
d_match = re.search(r'(20\d{6})', filename)
issue_day_jst = f"{d_match.group(1)[:4]}-{d_match.group(1)[4:6]}-{d_match.group(1)[6:8]}" if d_match else datetime.now(timezone.utc).strftime("%Y-%m-%d")
if not (d_from <= issue_day_jst <= d_to): continue
t_match = re.search(r'（(\d{2})時用）', filename)
issue_time_jst = f"{t_match.group(1)}:00" if t_match else "00:00"
issue_dt_jst = datetime.strptime(f"{issue_day_jst} {issue_time_jst}", "%Y-%m-%d %H:%M")
total_inserted += self.extract_sheet_to_table(file_path, issue_dt_jst, "①最適モデル", "FCST_saiteki", "(3)から自動整合", "天気整合", valid_targets)
total_inserted += self.extract_sheet_to_table(file_path, issue_dt_jst, "ランクアップ最適モデル", "FCST_rankup", "(3)から自動整合", "天気整合", valid_targets)
if m_enabled:
self.create_fcst_table_if_not_exists(m_row[0])
total_inserted += self.extract_sheet_to_table(file_path, issue_dt_jst, m_row[1], m_row[0], m_row[2], m_row[3], valid_targets)
self.conn.commit()
self.refresh_source_combos()
if self.cmb_preview_table.count() > 0:
self.load_preview_data(self.cmb_preview_table.currentText(), self.tbl_fcst)
if not silent:
msg = f"抽出が完了しました。(内部UTC統一格納)\n処理要素数: {total_inserted} 件\n(FCST_saiteki, FCST_rankup"
if m_enabled: msg += f", {m_row[0]}"
msg += " へ格納しました)"
QMessageBox.information(self, "完了", msg)
else:
logging.info(f"自動抽出: 予報モデル一括抽出 {total_inserted}件 保存完了")
except Exception as e:
err_msg = f"予報モデル一括抽出エラー: {e}"
logging.error(err_msg, exc_info=True)
if self.t3_auto_timer.isActive():
self.tray_icon.showMessage("自動取得エラー", "予報エクセルの抽出に失敗しました。詳細はログを確認してください。", QSystemTrayIcon.MessageIcon.Warning, 5000)
else:
QMessageBox.critical(self, "エラー", err_msg)
finally:
self._is_fetching_fcst = False
def execute_manual_extraction(self):
if self.last_forecast_folder == "未設定":
return QMessageBox.warning(self, "警告", "先に「手動でフォルダを指定」から読み込み先フォルダを設定してください。")
table_name = self.txt_m_table.text().strip()
sheet_name = self.txt_m_sheet.text().strip()
rain_kw = self.txt_m_rain.text().strip()
snow_kw = self.txt_m_snow.text().strip()
if not table_name or not sheet_name:
return QMessageBox.warning(self, "エラー", "保存先DB名とシート名は必須です。")
if not table_name.startswith("FCST_"):
return QMessageBox.warning(self, "エラー", "DB名は必ず 'FCST_' から始めてください。")
self.create_fcst_table_if_not_exists(table_name)
d_from = self.t3_date_from.date().toString("yyyy-MM-dd")
d_to = self.t3_date_to.date().toString("yyyy-MM-dd")
tid = self.cmb_template.currentIndex() + 1; cursor = self.conn.cursor()
cursor.execute("SELECT FcstName FROM TargetStations WHERE TemplateId = ?", (tid,))
valid_targets = [str(r[0]) for r in cursor.fetchall()]
if not valid_targets: return QMessageBox.warning(self, "警告", "現在のテンプレートの抽出対象リストが空です。")
total_inserted = 0
for filename in os.listdir(self.last_forecast_folder):
if not filename.endswith(".xlsx") or filename.startswith("~"): continue
if "コピー" in filename:
continue
file_path = os.path.join(self.last_forecast_folder, filename)
d_match = re.search(r'(20\d{6})', filename)
issue_day_jst = f"{d_match.group(1)[:4]}-{d_match.group(1)[4:6]}-{d_match.group(1)[6:8]}" if d_match else datetime.now(timezone.utc).strftime("%Y-%m-%d")
if not (d_from <= issue_day_jst <= d_to): continue
t_match = re.search(r'（(\d{2})時用）', filename)
issue_time_jst = f"{t_match.group(1)}:00" if t_match else "00:00"
issue_dt_jst = datetime.strptime(f"{issue_day_jst} {issue_time_jst}", "%Y-%m-%d %H:%M")
total_inserted += self.extract_sheet_to_table(file_path, issue_dt_jst, sheet_name, table_name, rain_kw, snow_kw, valid_targets)
self.conn.commit()
self.refresh_source_combos()
idx = self.cmb_preview_table.findText(table_name)
if idx >= 0: self.cmb_preview_table.setCurrentIndex(idx)
QMessageBox.information(self, "完了", f"マニュアル一括抽出が完了しました。\n保存先テーブル: {table_name}\n処理要素数: {total_inserted} 件")
def execute_manual_file_extraction(self):
table_name = self.txt_m_table.text().strip()
sheet_name = self.txt_m_sheet.text().strip()
rain_kw = self.txt_m_rain.text().strip()
snow_kw = self.txt_m_snow.text().strip()
if not table_name or not sheet_name:
return QMessageBox.warning(self, "エラー", "保存先DB名とシート名は必須です。")
if not table_name.startswith("FCST_"):
return QMessageBox.warning(self, "エラー", "DB名は必ず 'FCST_' から始めてください。")
file_path, _ = QFileDialog.getOpenFileName(self, "予報Excelファイルを選択", "", "Excel Files (*.xlsx)")
if not file_path: return
if "コピー" in os.path.basename(file_path):
QMessageBox.warning(self, "ファイル選択エラー", "ファイル名に「コピー」が含まれているファイルを選択してください。")
return
self.create_fcst_table_if_not_exists(table_name)
tid = self.cmb_template.currentIndex() + 1; cursor = self.conn.cursor()
cursor.execute("SELECT FcstName FROM TargetStations WHERE TemplateId = ?", (tid,))
valid_targets = [str(r[0]) for r in cursor.fetchall()]
if not valid_targets: return QMessageBox.warning(self, "警告", "現在のテンプレートの抽出対象リストが空です。")
filename = os.path.basename(file_path)
d_match = re.search(r'(20\d{6})', filename)
issue_day_jst = f"{d_match.group(1)[:4]}-{d_match.group(1)[4:6]}-{d_match.group(1)[6:8]}" if d_match else datetime.now(timezone.utc).strftime("%Y-%m-%d")
t_match = re.search(r'（(\d{2})時用）', filename)
issue_time_jst = f"{t_match.group(1)}:00" if t_match else "00:00"
issue_dt_jst = datetime.strptime(f"{issue_day_jst} {issue_time_jst}", "%Y-%m-%d %H:%M")
total_inserted = self.extract_sheet_to_table(file_path, issue_dt_jst, sheet_name, table_name, rain_kw, snow_kw, valid_targets)
self.conn.commit()
self.refresh_source_combos()
idx = self.cmb_preview_table.findText(table_name)
if idx >= 0: self.cmb_preview_table.setCurrentIndex(idx)
QMessageBox.information(self, "完了", f"個別ファイルの抽出が完了しました。\n保存先テーブル: {table_name}\n処理要素数: {total_inserted} 件")
def load_preview_data(self, table_name, widget):
if not table_name: return
cursor = self.conn.cursor()
try:
if table_name.startswith("FCST_"):
cursor.execute(f"SELECT IssueDay, IssueTime, TargetDay, TargetTime, MsCd, FcstRainfall, FcstSnowfall FROM {table_name} ORDER BY rowid DESC LIMIT 50")
else:
cursor.execute(f"SELECT * FROM {table_name} LIMIT 50")
rows = cursor.fetchall(); widget.setRowCount(len(rows)); col_count = widget.columnCount()
for r_i, r_data in enumerate(rows):
if table_name.startswith("FCST_"):
dt_iss_utc = datetime.strptime(f"{r_data[0]} {r_data[1]}", "%Y-%m-%d %H:%M")
dt_tgt_utc = datetime.strptime(f"{r_data[2]} {r_data[3]}", "%Y-%m-%d %H:%M")
dt_iss_jst = dt_iss_utc + timedelta(hours=9)
dt_tgt_jst = dt_tgt_utc + timedelta(hours=9)
widget.setItem(r_i, 0, QTableWidgetItem(dt_iss_jst.strftime("%Y-%m-%d")))
widget.setItem(r_i, 1, QTableWidgetItem(dt_iss_jst.strftime("%H:%M")))
widget.setItem(r_i, 2, QTableWidgetItem(dt_tgt_jst.strftime("%Y-%m-%d")))
widget.setItem(r_i, 3, QTableWidgetItem(dt_tgt_jst.strftime("%H:%M")))
widget.setItem(r_i, 4, QTableWidgetItem(str(r_data[4])))
widget.setItem(r_i, 5, QTableWidgetItem(str(r_data[5])))
widget.setItem(r_i, 6, QTableWidgetItem(str(r_data[6])))
else:
for c_i, c_data in enumerate(r_data):
if c_i < col_count: widget.setItem(r_i, c_i, QTableWidgetItem(str(c_data) if c_data is not None else ""))
except sqlite3.OperationalError:
pass
# ==========================================
# タブ4: 気象庁予報 直打ち入力フォーマット
# ==========================================
def setup_tab4(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
lbl_desc = QLabel("<b>【気象庁予報 (降雪量) 手入力フォーマット】(※画面はJST表記、保存時に内部UTCへ自動変換)</b><br>日本時間(JST)で時間枠が表示されます。値を保存する際、システムが自動でマイナス9時間してUTC軸に位置合わせします。")
lbl_desc.setStyleSheet("color: #0366D6; font-size: 14px; padding: 10px; background-color: #F1F8FF; border-radius: 5px;")
layout.addWidget(lbl_desc)
group_input = QGroupBox("対象日の指定 ＆ 降雪量入力 (JST基準で入力)"); input_layout = QVBoxLayout(); input_layout.setContentsMargins(15, 20, 15, 15)
date_layout = QHBoxLayout()
self.jma_base_date = QDateEdit(QDate.currentDate()); self.jma_base_date.setCalendarPopup(True)
self.jma_base_date.dateChanged.connect(self.update_jma_table_dates)
date_layout.addWidget(QLabel("【対象日(夕方発表の直近の夜)】(JST):")); date_layout.addWidget(self.jma_base_date); date_layout.addStretch()
input_layout.addLayout(date_layout)
self.tbl_jma = QTableWidget(6, 5)
self.tbl_jma.setHorizontalHeaderLabels(["地域", "対象期間(JST)", "開始日時(JST)", "終了日時(JST)", "降雪量予報(cm) 手入力"])
self.tbl_jma.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.tbl_jma.setAlternatingRowColors(True)
regions = ["石狩地方北部", "石狩地方中部", "石狩地方南部"]
periods = ["18:00〜06:00 (夜間)", "06:00〜18:00 (日中)"]
for i in range(3):
for j in range(2):
row = i * 2 + j
item_reg = QTableWidgetItem(regions[i]); item_reg.setFlags(item_reg.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 0, item_reg)
item_per = QTableWidgetItem(periods[j]); item_per.setFlags(item_per.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 1, item_per)
item_start = QTableWidgetItem(""); item_start.setFlags(item_start.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 2, item_start)
item_end = QTableWidgetItem(""); item_end.setFlags(item_end.flags() & ~Qt.ItemFlag.ItemIsEditable); self.tbl_jma.setItem(row, 3, item_end)
self.tbl_jma.setItem(row, 4, QTableWidgetItem(""))
self.update_jma_table_dates()
input_layout.addWidget(self.tbl_jma)
btn_save_jma = QPushButton("JMA_FCST テーブルに保存する"); btn_save_jma.setObjectName("ActionBtn"); btn_save_jma.clicked.connect(self.save_jma_manual_data)
input_layout.addWidget(btn_save_jma); group_input.setLayout(input_layout); layout.addWidget(group_input)
group_preview = QGroupBox("保存済みデータ (JMA_FCST) (※表示用にJSTに自動逆変換して表示しています)"); prev_layout = QVBoxLayout(); prev_layout.setContentsMargins(15, 20, 15, 15)
self.tbl_jma_prev = FilterableTableWidget(0, 6)
self.tbl_jma_prev.setHorizontalHeaderLabels(["開始日時(JST)▽", "終了日時(JST)▽", "地域▽", "生テキスト(範囲)▽", "内部最小値▽", "内部最大値▽"])
self.tbl_jma_prev.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
prev_layout.addWidget(self.tbl_jma_prev); group_preview.setLayout(prev_layout); layout.addWidget(group_preview); layout.addStretch()
self.tabs.addTab(scroll, "4. 気象庁予報(手入力)")
self.refresh_jma_preview()
def load_tab4_data(self):
for row in range(self.tbl_jma.rowCount()):
self.tbl_jma.setItem(row, 4, QTableWidgetItem(""))
cursor = self.conn.cursor()
for row in range(self.tbl_jma.rowCount()):
region = self.tbl_jma.item(row, 0).text()
start_jst_str = self.tbl_jma.item(row, 2).text()
dt_s_utc = datetime.strptime(start_jst_str, "%Y-%m-%d %H:%M") - timedelta(hours=9)
start_utc_str = dt_s_utc.strftime("%Y-%m-%d %H:%M")
cursor.execute("SELECT RawText FROM JMA_FCST WHERE Region = ? AND TargetStart = ?", (region, start_utc_str))
res = cursor.fetchone()
if res:
self.tbl_jma.setItem(row, 4, QTableWidgetItem(str(res[0])))
def update_jma_table_dates(self):
base_dt = self.jma_base_date.date().toPyDate()
for i in range(3):
r0 = i * 2; s0 = datetime(base_dt.year, base_dt.month, base_dt.day, 18, 0); e0 = s0 + timedelta(hours=12)
self.tbl_jma.item(r0, 2).setText(s0.strftime("%Y-%m-%d %H:%M")); self.tbl_jma.item(r0, 3).setText(e0.strftime("%Y-%m-%d %H:%M"))
r1 = i * 2 + 1; s1 = e0; e1 = s1 + timedelta(hours=12)
self.tbl_jma.item(r1, 2).setText(s1.strftime("%Y-%m-%d %H:%M")); self.tbl_jma.item(r1, 3).setText(e1.strftime("%Y-%m-%d %H:%M"))
self.load_tab4_data()
def save_jma_manual_data(self):
cursor = self.conn.cursor()
saved = 0
for row in range(self.tbl_jma.rowCount()):
region = self.tbl_jma.item(row, 0).text()
start_jst_str = self.tbl_jma.item(row, 2).text()
end_jst_str = self.tbl_jma.item(row, 3).text()
val_str = self.tbl_jma.item(row, 4).text().strip()
if not val_str: continue
nums = re.findall(r'\d+', val_str)
if not nums: continue
min_v = float(nums[0]); max_v = float(nums[-1])
dt_s_utc = datetime.strptime(start_jst_str, "%Y-%m-%d %H:%M") - timedelta(hours=9)
dt_e_utc = datetime.strptime(end_jst_str, "%Y-%m-%d %H:%M") - timedelta(hours=9)
start_utc_str = dt_s_utc.strftime("%Y-%m-%d %H:%M")
end_utc_str = dt_e_utc.strftime("%Y-%m-%d %H:%M")
cursor.execute("INSERT OR REPLACE INTO JMA_FCST (TargetStart, TargetEnd, Region, Element, RawText, MinVal, MaxVal) VALUES (?, ?, ?, ?, ?, ?, ?)",
(start_utc_str, end_utc_str, region, "降雪量", val_str, min_v, max_v))
saved += 1
self.conn.commit()
self.refresh_jma_preview()
QMessageBox.information(self, "保存完了", f"{saved}件の気象庁手入力データを保存しました。(内部UTC変換)")
def refresh_jma_preview(self):
cursor = self.conn.cursor()
cursor.execute("SELECT TargetStart, TargetEnd, Region, RawText, MinVal, MaxVal FROM JMA_FCST ORDER BY TargetStart DESC LIMIT 50")
rows = cursor.fetchall(); self.tbl_jma_prev.setRowCount(len(rows))
for r_i, r_data in enumerate(rows):
dt_s_utc = datetime.strptime(r_data[0], "%Y-%m-%d %H:%M")
dt_e_utc = datetime.strptime(r_data[1], "%Y-%m-%d %H:%M")
dt_s_jst = dt_s_utc + timedelta(hours=9)
dt_e_jst = dt_e_utc + timedelta(hours=9)
self.tbl_jma_prev.setItem(r_i, 0, QTableWidgetItem(dt_s_jst.strftime("%Y-%m-%d %H:%M")))
self.tbl_jma_prev.setItem(r_i, 1, QTableWidgetItem(dt_e_jst.strftime("%Y-%m-%d %H:%M")))
self.tbl_jma_prev.setItem(r_i, 2, QTableWidgetItem(str(r_data[2])))
self.tbl_jma_prev.setItem(r_i, 3, QTableWidgetItem(str(r_data[3])))
self.tbl_jma_prev.setItem(r_i, 4, QTableWidgetItem(str(r_data[4])))
self.tbl_jma_prev.setItem(r_i, 5, QTableWidgetItem(str(r_data[5])))
# ==========================================
# タブ5: 精度評価・マッピング
# ==========================================
def setup_tab5(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_mapping = QGroupBox("地点マッピング設定 (観測地点 ⇔ 予報地点/PDF推定地域)")
mapping_layout = QVBoxLayout(); mapping_layout.setContentsMargins(15, 20, 15, 10)
map_header = QHBoxLayout()
self.cmb_map_template = QComboBox()
self.cmb_map_template.addItems([f"テンプレート{i}" for i in range(1, 6)])
self.cmb_map_template.currentIndexChanged.connect(self.load_mapping_data)
map_header.addWidget(QLabel("選択中:")); map_header.addWidget(self.cmb_map_template); map_header.addStretch()
mapping_layout.addLayout(map_header)
tbl_and_btn = QHBoxLayout()
self.tbl_mapping = FilterableTableWidget(0, 3)
self.tbl_mapping.setHorizontalHeaderLabels(["観測地点 (OBS)▽", "連携▽", "予報地点名/地域 (FCST/JMA)▽"])
self.tbl_mapping.horizontalHeader().setSectionResizeMode(0, QHeaderView.ResizeMode.ResizeToContents)
self.tbl_mapping.horizontalHeader().setSectionResizeMode(1, QHeaderView.ResizeMode.ResizeToContents)
self.tbl_mapping.horizontalHeader().setSectionResizeMode(2, QHeaderView.ResizeMode.Stretch)
self.tbl_mapping.setMinimumHeight(250)
map_btn_layout = QVBoxLayout()
lbl_help = QLabel("※ 気象庁予報の検証時は、ここの「予報地点名」を\nタブ4の地域に書き換えてください。")
btn_save_map = QPushButton("現在のテンプレートに保存")
btn_save_map.setObjectName("ActionBtn"); btn_save_map.clicked.connect(self.save_mapping_data)
map_btn_layout.addWidget(lbl_help); map_btn_layout.addWidget(btn_save_map); map_btn_layout.addStretch()
tbl_and_btn.addWidget(self.tbl_mapping); tbl_and_btn.addLayout(map_btn_layout)
mapping_layout.addLayout(tbl_and_btn); group_mapping.setLayout(mapping_layout)
group_filter = QGroupBox("検証条件の指定 (※日付入力はJSTベースで行い、自動変換してUTC完全同期抽出されます)")
filter_layout_main = QVBoxLayout(); filter_layout_main.setContentsMargins(15, 15, 15, 15)
row_src = QHBoxLayout()
self.cmb_source = QComboBox()
self.cmb_source.setStyleSheet("background-color: #FFF3E0; font-weight:bold; border: 2px solid #FF8C00; font-size: 14px;")
self.cmb_source.currentTextChanged.connect(self.update_fcst_list)
row_src.addWidget(QLabel("【評価対象データ】:")); row_src.addWidget(self.cmb_source); row_src.addStretch()
filter_layout_main.addLayout(row_src); filter_layout_main.addSpacing(10)
row1 = QHBoxLayout()
self.issue_from = QDateEdit(QDate.currentDate().addDays(-7)); self.issue_from.setCalendarPopup(True)
self.issue_to = QDateEdit(QDate.currentDate()); self.issue_to.setCalendarPopup(True)
self.target_from = QDateEdit(QDate.currentDate().addDays(-7)); self.target_from.setCalendarPopup(True)
self.target_to = QDateEdit(QDate.currentDate().addDays(2)); self.target_to.setCalendarPopup(True)
row1.addWidget(QLabel("【発表日】(JST/Excel用):")); row1.addWidget(self.issue_from); row1.addWidget(QLabel("～")); row1.addWidget(self.issue_to)
row1.addSpacing(20)
row1.addWidget(QLabel("【対象日】(JST):")); row1.addWidget(self.target_from); row1.addWidget(QLabel("～")); row1.addWidget(self.target_to); row1.addStretch()
row2 = QHBoxLayout()
self.cmb_station = QComboBox(); self.update_tab5_station_combo()
self.cmb_element = QComboBox(); self.cmb_element.addItems(["降水量", "降雪量"])
self.cmb_element.currentTextChanged.connect(self.update_custom_labels_visibility)
self.cmb_step = QComboBox(); self.cmb_step.addItems(["3時間ステップ", "12時間ステップ", "1時間ステップ"]); self.cmb_step.setCurrentText("3時間ステップ")
self.cmb_leadtime = QComboBox(); self.cmb_leadtime.addItems(["期間制限なし", "12時間以内", "24時間以内", "36時間以内"])
self.btn_search = QPushButton("精度評価を実行"); self.btn_search.setObjectName("ActionBtn"); self.btn_search.clicked.connect(self.calculate_accuracy)
row2.addWidget(QLabel("観測地点:")); row2.addWidget(self.cmb_station); row2.addWidget(QLabel("要素:")); row2.addWidget(self.cmb_element)
row2.addWidget(QLabel("時間(Excel用):")); row2.addWidget(self.cmb_step)
row2.addWidget(QLabel("発出(Excel用):")); row2.addWidget(self.cmb_leadtime); row2.addStretch(); row2.addWidget(self.btn_search)
filter_layout_main.addLayout(row1); filter_layout_main.addSpacing(10); filter_layout_main.addLayout(row2)
group_filter.setLayout(filter_layout_main)
eval_layout = QHBoxLayout()
group_quant = QGroupBox("量的評価指標"); quant_layout = QVBoxLayout(); quant_layout.setContentsMargins(15, 20, 15, 15)
self.lbl_rmse = QLabel("RMSE: --"); self.lbl_me = QLabel("ME (差): --"); self.lbl_mae = QLabel("MAE: --")
quant_layout.addWidget(self.lbl_rmse); quant_layout.addWidget(self.lbl_me); quant_layout.addWidget(self.lbl_mae); group_quant.setLayout(quant_layout)
group_cat = QGroupBox("カテゴリ全体評価 (>0.0 / 気象庁は範囲的中)"); cat_layout = QGridLayout(); cat_layout.setContentsMargins(15, 20, 15, 15)
self.lbl_hit = QLabel("的中率: --"); self.lbl_pod = QLabel("捕捉率: --"); self.lbl_ts = QLabel("スレッドスコア: --")
self.lbl_far = QLabel("空振り率: --"); self.lbl_miss = QLabel("見逃し率: --")
cat_layout.addWidget(self.lbl_hit, 0, 0); cat_layout.addWidget(self.lbl_pod, 0, 1); cat_layout.addWidget(self.lbl_ts, 0, 2)
cat_layout.addWidget(self.lbl_far, 1, 0); cat_layout.addWidget(self.lbl_miss, 1, 1); group_cat.setLayout(cat_layout)
group_custom = QGroupBox("プロ仕様 独自閾値別評価 (回数内訳完全可視化)"); custom_layout = QGridLayout(); custom_layout.setContentsMargins(15, 10, 15, 10)
self.custom_labels = []
for i in range(4):
l_title = QLabel(f"【閾値{i+1}】: --"); l_title.setStyleSheet("font-weight: bold; color: #D73A49;")
l_stats = QLabel("全数: -- / 的中(A): -- / 空振(B): -- / 見逃(C): -- / 発現: -- / 予報: --")
l_rates = QLabel("的: -- / 捕: -- / 空: -- / TS: --")
self.custom_labels.append({"title": l_title, "stats": l_stats, "rates": l_rates})
custom_layout.addWidget(l_title, i, 0); custom_layout.addWidget(l_stats, i, 1); custom_layout.addWidget(l_rates, i, 2)
group_custom.setLayout(custom_layout)
eval_layout.addWidget(group_quant, 1); eval_layout.addWidget(group_cat, 1); eval_layout.addWidget(group_custom, 2)
self.tbl_detail = FilterableTableWidget(0, 7)
self.tbl_detail.setHorizontalHeaderLabels(["観測地点▽", "発表日時(JST)▽", "対象日時(JST)▽", "予報値▽", "実況値(加算済)▽", "差分▽", "判定▽"])
self.tbl_detail.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch); self.tbl_detail.setMinimumHeight(250)
layout.addWidget(group_mapping); layout.addWidget(group_filter); layout.addLayout(eval_layout); layout.addWidget(self.tbl_detail); layout.addStretch()
self.tabs.addTab(scroll, "5. 精度評価・マッピング")
self.update_custom_labels_visibility()
self.load_mapping_data()
def update_fcst_list(self, text):
if not hasattr(self, 'cmb_element'): return
self.cmb_element.clear()
if "JMA_FCST" in text:
self.cmb_element.addItems(["降雪量"])
else:
self.cmb_element.addItems(["降水量", "降雪量"])
def update_custom_labels_visibility(self):
is_snow = self.cmb_element.currentText() == "降雪量"
self.custom_labels[3]["title"].setVisible(is_snow)
self.custom_labels[3]["stats"].setVisible(is_snow)
self.custom_labels[3]["rates"].setVisible(is_snow)
def load_mapping_data(self):
tid = self.cmb_map_template.currentIndex() + 1
cursor = self.conn.cursor()
cursor.execute("SELECT ObsCd, FcstName FROM Mapping WHERE TemplateId = ? ORDER BY ObsCd", (tid,))
rows = cursor.fetchall()
self.tbl_mapping.setRowCount(max(len(rows), 20))
for i in range(self.tbl_mapping.rowCount()):
obs_val = str(rows[i][0]) if i < len(rows) else ""
fcst_val = str(rows[i][1]) if i < len(rows) else ""
item_obs = QTableWidgetItem(obs_val)
item_obs.setBackground(QColor("#F6F8FA"))
self.tbl_mapping.setItem(i, 0, item_obs)
item_arrow = QTableWidgetItem(" ⟷ ")
item_arrow.setFlags(item_arrow.flags() & ~Qt.ItemFlag.ItemIsEditable)
self.tbl_mapping.setItem(i, 1, item_arrow)
self.tbl_mapping.setItem(i, 2, QTableWidgetItem(fcst_val))
self.update_tab5_station_combo()
def update_tab5_station_combo(self):
if not hasattr(self, 'cmb_map_template'): return
tid = self.cmb_map_template.currentIndex() + 1
self.cmb_station.clear()
self.cmb_station.addItem("全地点総合評価")
cursor = self.conn.cursor()
cursor.execute("SELECT DISTINCT ObsCd FROM Mapping WHERE TemplateId = ?", (tid,))
self.cmb_station.addItems([str(r[0]) for r in cursor.fetchall()])
def save_mapping_data(self):
tid = self.cmb_map_template.currentIndex() + 1
cursor = self.conn.cursor()
cursor.execute("DELETE FROM Mapping WHERE TemplateId = ?", (tid,))
for i in range(self.tbl_mapping.rowCount()):
obs_item = self.tbl_mapping.item(i, 0)
fcst_item = self.tbl_mapping.item(i, 2)
if obs_item and obs_item.text().strip():
cursor.execute("INSERT OR REPLACE INTO Mapping (TemplateId, ObsCd, FcstName) VALUES (?, ?, ?)",
(tid, obs_item.text().strip(), fcst_item.text().strip() if fcst_item else ""))
self.conn.commit()
self.update_tab5_station_combo()
QMessageBox.information(self, "完了", f"テンプレート{tid} のマッピングを保存しました。")
def calculate_accuracy(self):
self.btn_search.setText("⏳ 計算中...お待ちください"); self.btn_search.setEnabled(False); QApplication.processEvents()
try:
obs_cd_sel = self.cmb_station.currentText()
is_rain = self.cmb_element.currentText() == "降水量"
src_selected = self.cmb_source.currentText()
src_jma = "JMA_FCST" in src_selected
target_fcst_table = src_selected.split(" ")[0]
utc_tgt_start, utc_tgt_end = self.jst_date_range_to_utc_str(self.target_from, self.target_to)
utc_iss_start, utc_iss_end = self.jst_date_range_to_utc_str(self.issue_from, self.issue_to)
step_val = 3 if "3時間" in self.cmb_step.currentText() else (12 if "12時間" in self.cmb_step.currentText() else 1)
lead_time_txt = self.cmb_leadtime.currentText()
cursor = self.conn.cursor()
if obs_cd_sel == "全地点総合評価":
cursor.execute("SELECT ObsCd, FcstName FROM Mapping WHERE TemplateId = ?", (self.cmb_map_template.currentIndex()+1,))
mappings = cursor.fetchall()
else:
cursor.execute("SELECT FcstName FROM Mapping WHERE ObsCd = ? AND TemplateId = ?", (obs_cd_sel, self.cmb_map_template.currentIndex()+1))
row = cursor.fetchone()
if not row:
self.btn_search.setText("精度評価を実行"); self.btn_search.setEnabled(True)
return QMessageBox.warning(self, "通知", "マッピングが見つかりません。")
mappings = [(obs_cd_sel, row[0])]
all_dfs = []
for obs_cd, fcst_name in mappings:
target_obs_table = "OBS_GP" if str(obs_cd).startswith("GPV_") else "OBS"
if src_jma:
query = "SELECT ? AS Station, '-' AS IssueDT, TargetStart || '～' || TargetEnd AS TargetDT, RawText AS FcstText, MinVal, MaxVal, TargetStart, TargetEnd FROM JMA_FCST WHERE Region = ? AND Element = ? AND TargetStart BETWEEN ? AND ?"
df_fcst = pd.read_sql_query(query, self.conn, params=(obs_cd, fcst_name, self.cmb_element.currentText(), utc_tgt_start, utc_tgt_end))
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, Rainfall1h, Snowfall1h, SnowDepth FROM {target_obs_table} WHERE MsCd = ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd,))
if df_obs.empty or df_fcst.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT'])
merged_rows = []
for _, row in df_fcst.iterrows():
ts_start = pd.to_datetime(row['TargetStart']); ts_end = pd.to_datetime(row['TargetEnd'])
mask = (df_obs['ObsDT'] > ts_start) & (df_obs['ObsDT'] <= ts_end)
if is_rain:
obs_val = df_obs.loc[mask, 'Rainfall1h'].sum()
else:
if df_obs['SnowDepth'].notna().any():
sd_start = df_obs.loc[df_obs['ObsDT'] == ts_start, 'SnowDepth'].last_valid_index()
sd_end = df_obs.loc[df_obs['ObsDT'] == ts_end, 'SnowDepth'].last_valid_index()
start_val = df_obs.loc[sd_start, 'SnowDepth'] if sd_start is not None else 0
end_val = df_obs.loc[sd_end, 'SnowDepth'] if sd_end is not None else 0
obs_val = max(end_val - start_val, 0)
else:
obs_val = df_obs.loc[mask, 'Snowfall1h'].sum()
is_hit = row['MinVal'] <= obs_val <= row['MaxVal']
diff = 0.0
if obs_val < row['MinVal']: diff = obs_val - row['MinVal']
elif obs_val > row['MaxVal']: diff = obs_val - row['MaxVal']
merged_rows.append({
'Station': row['Station'], 'IssueDT': row['IssueDT'], 'TargetDT': row['TargetDT'],
'Fcst': row['MaxVal'], 'FcstText': row['FcstText'], 'Obs': obs_val, 'Diff': diff,
'MinVal': row['MinVal'], 'MaxVal': row['MaxVal'], 'IsRangeHit': is_hit
})
if merged_rows: all_dfs.append(pd.DataFrame(merged_rows))
else:
try:
fcst_query = f"SELECT '{obs_cd}' AS Station, IssueDay || ' ' || IssueTime AS IssueDT, TargetDay || ' ' || TargetTime AS TargetDT, {'FcstRainfall' if is_rain else 'FcstSnowfall'} AS Fcst FROM {target_fcst_table} WHERE MsCd = ? AND IssueDay || ' ' || IssueTime BETWEEN ? AND ? AND TargetDay || ' ' || TargetTime BETWEEN ? AND ? AND ForecastStep = ?"
df_fcst = pd.read_sql_query(fcst_query, self.conn, params=(fcst_name, utc_iss_start, utc_iss_end, utc_tgt_start, utc_tgt_end, step_val))
except sqlite3.OperationalError:
continue
obs_start_dt = datetime.strptime(utc_tgt_start.split()[0], "%Y-%m-%d") - timedelta(days=2)
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, Rainfall1h, Snowfall1h, SnowDepth FROM {target_obs_table} WHERE MsCd = ? AND ObsDay >= ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd, obs_start_dt.strftime("%Y-%m-%d")))
if df_fcst.empty or df_obs.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT']); df_obs.set_index('ObsDT', inplace=True)
df_obs_agg = df_obs.resample('1h').agg({'Rainfall1h': 'sum', 'Snowfall1h': 'sum', 'SnowDepth': 'last'})
df_obs_agg['SnowDepth'] = df_obs_agg['SnowDepth'].ffill()
if is_rain:
if step_val > 1: df_obs_agg['Obs'] = df_obs_agg['Rainfall1h'].rolling(window=step_val, min_periods=1).sum()
else: df_obs_agg['Obs'] = df_obs_agg['Rainfall1h']
else:
# 積雪深(SnowDepth)の引き算による算出を廃止し、常に前1時間降雪(P1)の累積に統一
df_obs_agg['Obs'] = df_obs_agg['Snowfall1h'].rolling(window=step_val, min_periods=1).sum() if step_val > 1 else df_obs_agg['Snowfall1h']
df_obs_agg = df_obs_agg.reset_index()
df_obs_agg['TargetDT'] = df_obs_agg['ObsDT'].dt.strftime('%Y-%m-%d %H:%M')
df_merged = pd.merge(df_fcst, df_obs_agg[['TargetDT', 'Obs']], on='TargetDT', how='inner')
if not df_merged.empty:
df_merged['Diff'] = df_merged['Fcst'] - df_merged['Obs']
df_merged['FcstText'] = df_merged['Fcst'].apply(lambda x: f"{x:.1f}")
all_dfs.append(df_merged)
if not all_dfs:
self.tbl_detail.setRowCount(0)
QMessageBox.warning(self, "データ不足", "条件に合致するデータがありませんでした。")
return
df = pd.concat(all_dfs, ignore_index=True)
if lead_time_txt != "期間制限なし":
max_hours = int(lead_time_txt.replace("時間以内", ""))
if 'LeadTime' not in df.columns:
df['LeadTime'] = (pd.to_datetime(df['TargetDT']) - pd.to_datetime(df['IssueDT'])).dt.total_seconds() / 3600
df = df[(df['LeadTime'] >= 0) & (df['LeadTime'] <= max_hours)]
if df.empty:
self.tbl_detail.setRowCount(0)
return QMessageBox.warning(self, "データ不足", "発出時間制限により、該当データが0件になりました。")
self.lbl_rmse.setText(f"RMSE: {np.sqrt((df['Diff']**2).mean()):.2f}"); self.lbl_me.setText(f"ME (差): {df['Diff'].mean():.2f}"); self.lbl_mae.setText(f"MAE: {df['Diff'].abs().mean():.2f}")
def calc_metrics(A, B, C, D):
total = A + B + C + D
hit = (A + D) / total * 100 if total > 0 else 0
pod = A / (A + C) * 100 if (A + C) > 0 else 0
far = B / (A + B) * 100 if (A + B) > 0 else 0
miss = C / (A + C) * 100 if (A + C) > 0 else 0
ts = A / (A + B + C) * 100 if (A + B + C) > 0 else 0
return total, hit, pod, far, miss, ts
if src_jma:
A = len(df[(df['MaxVal'] > 0) & (df['Obs'] > 0) & df['IsRangeHit']]); B = len(df[(df['MaxVal'] > 0) & (~df['IsRangeHit']) & (df['Diff'] < 0)])
C = len(df[(df['MaxVal'] > 0) & (~df['IsRangeHit']) & (df['Diff'] > 0)]); D = len(df[(df['MaxVal'] == 0) & (df['Obs'] == 0) & df['IsRangeHit']])
hit_rate = len(df[df['IsRangeHit']]) / len(df) * 100 if len(df) > 0 else 0
self.lbl_hit.setText(f"完全範囲的中率: {hit_rate:.1f} %"); self.lbl_pod.setText("捕捉率: (範囲判定)"); self.lbl_ts.setText(f"全体TS(概算): {hit_rate:.1f} %")
self.lbl_far.setText("空振り率: --"); self.lbl_miss.setText("見逃し率: --")
else:
A = len(df[(df['Fcst'] > 0) & (df['Obs'] > 0)]); B = len(df[(df['Fcst'] > 0) & (df['Obs'] == 0)])
C = len(df[(df['Fcst'] == 0) & (df['Obs'] > 0)]); D = len(df[(df['Fcst'] == 0) & (df['Obs'] == 0)])
total, hit, pod, far, miss, ts = calc_metrics(A, B, C, D)
self.lbl_hit.setText(f"的中率: {hit:.1f} %"); self.lbl_pod.setText(f"捕捉率: {pod:.1f} %")
self.lbl_far.setText(f"空振り率: {far:.1f} %"); self.lbl_miss.setText(f"見逃し率: {miss:.1f} %"); self.lbl_ts.setText(f"スレッドスコア: {ts:.1f} %")
unit = "mm" if is_rain else "cm"
thresholds = [1.0, 10.0, 20.0] if is_rain else [1.0, 10.0, 20.0, 30.0]
for i, T in enumerate(thresholds):
self.custom_labels[i]["title"].setText(f"【閾値 >= {T} {unit}】")
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_t = len(df[(df[fcst_col] >= T) & (df['Obs'] >= T)]); B_t = len(df[(df[fcst_col] >= T) & (df['Obs'] < T)])
C_t = len(df[(df[fcst_col] < T) & (df['Obs'] >= T)]); D_t = len(df[(df[fcst_col] < T) & (df['Obs'] < T)])
tot_t, hit_t, pod_t, far_t, miss_t, ts_t = calc_metrics(A_t, B_t, C_t, D_t)
self.custom_labels[i]["stats"].setText(f"全数: {tot_t} / 的中(A): {A_t} / 空振(B): {B_t} / 見逃(C): {C_t} / 発現: {A_t+C_t} / 予報: {A_t+B_t}")
self.custom_labels[i]["rates"].setText(f"的: {hit_t:.1f}% / 捕: {pod_t:.1f}% / 空: {far_t:.1f}% / TS: {ts_t:.1f}%")
self.tbl_detail.setRowCount(len(df))
for i, r in df.reset_index().iterrows():
iss_jst, tgt_jst = "-", "-"
if str(r['IssueDT']) != "-":
iss_jst = (datetime.strptime(str(r['IssueDT']), "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
if "～" in str(r['TargetDT']):
t_parts = str(r['TargetDT']).split("～")
t_s = (datetime.strptime(t_parts[0], "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%m/%d %H:%M")
t_e = (datetime.strptime(t_parts[1], "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%m/%d %H:%M")
tgt_jst = f"{t_s}～{t_e}"
else:
tgt_jst = (datetime.strptime(str(r['TargetDT']), "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
self.tbl_detail.setItem(i, 0, QTableWidgetItem(str(r['Station'])))
self.tbl_detail.setItem(i, 1, QTableWidgetItem(iss_jst))
self.tbl_detail.setItem(i, 2, QTableWidgetItem(tgt_jst))
self.tbl_detail.setItem(i, 3, QTableWidgetItem(str(r['FcstText'])))
self.tbl_detail.setItem(i, 4, QTableWidgetItem(f"{r['Obs']:.1f}"))
diff_item = QTableWidgetItem(f"{r['Diff']:+.1f}")
if r['Diff'] > 0: diff_item.setForeground(QColor("#D73A49"))
elif r['Diff'] < 0: diff_item.setForeground(QColor("#0366D6"))
self.tbl_detail.setItem(i, 5, diff_item)
judge_text = "範囲的中" if src_jma and r.get('IsRangeHit') else ("的中" if not src_jma and ((r['Fcst']>0 and r['Obs']>0) or (r['Fcst']==0 and r['Obs']==0)) else "外れ")
self.tbl_detail.setItem(i, 6, QTableWidgetItem(judge_text))
except Exception as e:
logging.error(f"計算処理中にエラーが発生: {e}", exc_info=True)
QMessageBox.critical(self, "エラー", f"計算処理中にエラーが発生しました:\n{e}")
finally:
self.btn_search.setText("精度評価を実行"); self.btn_search.setEnabled(True)
# ==========================================
# タブ6: 検証Excel出力
# ==========================================
def setup_tab6(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
group_filter = QGroupBox("プロ仕様・検証レポート出力 (※日付はJST入力、出力されるレポートの表はJST軸に展開されます)"); filter_layout_main = QVBoxLayout(); filter_layout_main.setContentsMargins(15, 20, 15, 15)
row_sync = QHBoxLayout()
btn_sync = QPushButton("🔄 タブ5の条件をここにコピー"); btn_sync.clicked.connect(self.sync_from_tab5)
row_sync.addWidget(btn_sync); row_sync.addStretch(); filter_layout_main.addLayout(row_sync)
row1 = QHBoxLayout()
self.ex_issue_from = QDateEdit(QDate.currentDate().addDays(-7)); self.ex_issue_from.setCalendarPopup(True)
self.ex_issue_to = QDateEdit(QDate.currentDate()); self.ex_issue_to.setCalendarPopup(True)
self.ex_target_from = QDateEdit(QDate.currentDate().addDays(-7)); self.ex_target_from.setCalendarPopup(True)
self.ex_target_to = QDateEdit(QDate.currentDate().addDays(2)); self.ex_target_to.setCalendarPopup(True)
row1.addWidget(QLabel("【発表日】(JST/Excel用):")); row1.addWidget(self.ex_issue_from); row1.addWidget(QLabel("～")); row1.addWidget(self.ex_issue_to)
row1.addSpacing(30)
row1.addWidget(QLabel("【対象日】(JST):")); row1.addWidget(self.ex_target_from); row1.addWidget(QLabel("～")); row1.addWidget(self.ex_target_to); row1.addStretch()
row2 = QHBoxLayout()
self.ex_cmb_source = QComboBox()
self.ex_cmb_station = QComboBox(); cursor = self.conn.cursor(); cursor.execute("SELECT DISTINCT ObsCd FROM Mapping")
self.ex_cmb_station.addItem("全地点及び全地点総合評価"); self.ex_cmb_station.addItems([str(r[0]) for r in cursor.fetchall()])
self.ex_cmb_element = QComboBox(); self.ex_cmb_element.addItems(["降水量", "降雪量"])
self.ex_cmb_step = QComboBox(); self.ex_cmb_step.addItems(["1時間ステップ", "3時間ステップ", "12時間ステップ"]); self.ex_cmb_step.setCurrentText("3時間ステップ")
self.ex_cmb_leadtime = QComboBox(); self.ex_cmb_leadtime.addItems(["期間制限なし", "12時間以内", "24時間以内", "36時間以内"])
row2.addWidget(QLabel("対象ソース:")); row2.addWidget(self.ex_cmb_source)
row2.addWidget(QLabel("観測地点:")); row2.addWidget(self.ex_cmb_station); row2.addWidget(QLabel("要素:")); row2.addWidget(self.ex_cmb_element)
row2.addWidget(QLabel("ステップ:")); row2.addWidget(self.ex_cmb_step)
row2.addWidget(QLabel("発出制限:")); row2.addWidget(self.ex_cmb_leadtime); row2.addStretch()
filter_layout_main.addLayout(row1); filter_layout_main.addSpacing(10); filter_layout_main.addLayout(row2)
group_filter.setLayout(filter_layout_main)
group_export = QGroupBox("レポート出力実行"); e_layout = QVBoxLayout(); e_layout.setContentsMargins(15, 25, 15, 15)
btn_export = QPushButton("検証レポートExcelを作成"); btn_export.setObjectName("ActionBtn"); btn_export.clicked.connect(self.export_matrix_excel)
e_layout.addWidget(QLabel("指定期間の精度を網羅して出力します。(内部UTCで計算し、ExcelシートにはわかりやすいJST時間軸で出力されます)")); e_layout.addWidget(btn_export); e_layout.addStretch(); group_export.setLayout(e_layout)
layout.addWidget(group_filter); layout.addWidget(group_export); layout.addStretch(); self.tabs.addTab(scroll, "6. 検証Excel出力")
def sync_from_tab5(self):
self.ex_cmb_source.setCurrentText(self.cmb_source.currentText())
self.ex_issue_from.setDate(self.issue_from.date()); self.ex_issue_to.setDate(self.issue_to.date())
self.ex_target_from.setDate(self.target_from.date()); self.ex_target_to.setDate(self.target_to.date())
if self.cmb_station.currentText() == "全地点総合評価":
self.ex_cmb_station.setCurrentText("全地点及び全地点総合評価")
else:
self.ex_cmb_station.setCurrentText(self.cmb_station.currentText())
self.ex_cmb_element.setCurrentText(self.cmb_element.currentText())
self.ex_cmb_step.setCurrentText(self.cmb_step.currentText()); self.ex_cmb_leadtime.setCurrentText(self.cmb_leadtime.currentText())
def export_matrix_excel(self):
now_str = datetime.now(timezone.utc).strftime('%Y%m%d_%H%M%S')
default_name = f"Verification_Report_{now_str}.xlsx"
path, _ = QFileDialog.getSaveFileName(self, "検証レポートを保存", default_name, "Excel Files (*.xlsx)")
if not path: return
try:
obs_cd_sel = self.ex_cmb_station.currentText()
is_rain = self.ex_cmb_element.currentText() == "降水量"
src_selected = self.ex_cmb_source.currentText()
src_jma = "JMA_FCST" in src_selected
target_fcst_table = src_selected.split(" ")[0]
utc_iss_start, utc_iss_end = self.jst_date_range_to_utc_str(self.ex_issue_from, self.ex_issue_to)
utc_tgt_start, utc_tgt_end = self.jst_date_range_to_utc_str(self.ex_target_from, self.ex_target_to)
step_val = 3 if "3時間" in self.ex_cmb_step.currentText() else (12 if "12時間" in self.ex_cmb_step.currentText() else 1)
lead_time_txt = self.ex_cmb_leadtime.currentText()
unit = "mm" if is_rain else "cm"
cursor = self.conn.cursor()
if obs_cd_sel == "全地点及び全地点総合評価":
cursor.execute("SELECT ObsCd, FcstName FROM Mapping WHERE TemplateId = ?", (self.cmb_map_template.currentIndex()+1,))
mappings = cursor.fetchall()
else:
cursor.execute("SELECT FcstName FROM Mapping WHERE ObsCd = ? AND TemplateId = ?", (obs_cd_sel, self.cmb_map_template.currentIndex()+1))
row = cursor.fetchone()
if not row: return QMessageBox.warning(self, "エラー", "マッピングが見つかりません。")
mappings = [(obs_cd_sel, row[0])]
def calc_m(A, B, C, D):
tot = A + B + C + D; h = (A + D) / tot * 100 if tot > 0 else 0; p = A / (A + C) * 100 if (A + C) > 0 else 0
f = B / (A + B) * 100 if (A + B) > 0 else 0; m = C / (A + C) * 100 if (A + C) > 0 else 0; ts = A / (A + B + C) * 100 if (A + B + C) > 0 else 0
return tot, h, p, f, m, ts
all_eval_dfs = []
wrote_any = False
with pd.ExcelWriter(path, engine='openpyxl') as writer:
for obs_cd, fcst_name in mappings:
target_obs_table = "OBS_GP" if str(obs_cd).startswith("GPV_") else "OBS"
df = pd.DataFrame()
if src_jma:
query = "SELECT ? AS Station, '-' AS IssueDT, TargetStart || '～' || TargetEnd AS TargetDT, RawText AS FcstText, MinVal, MaxVal, TargetStart, TargetEnd FROM JMA_FCST WHERE Region = ? AND Element = ? AND TargetStart BETWEEN ? AND ?"
df_fcst = pd.read_sql_query(query, self.conn, params=(obs_cd, fcst_name, self.ex_cmb_element.currentText(), utc_tgt_start, utc_tgt_end))
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, Rainfall1h, Snowfall1h, SnowDepth FROM {target_obs_table} WHERE MsCd = ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd,))
if df_obs.empty or df_fcst.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT'])
merged_rows = []
for _, row in df_fcst.iterrows():
ts_start = pd.to_datetime(row['TargetStart']); ts_end = pd.to_datetime(row['TargetEnd'])
mask = (df_obs['ObsDT'] > ts_start) & (df_obs['ObsDT'] <= ts_end)
if is_rain:
obs_val = df_obs.loc[mask, 'Rainfall1h'].sum()
else:
if df_obs['SnowDepth'].notna().any():
sd_start = df_obs.loc[df_obs['ObsDT'] == ts_start, 'SnowDepth'].last_valid_index()
sd_end = df_obs.loc[df_obs['ObsDT'] == ts_end, 'SnowDepth'].last_valid_index()
start_val = df_obs.loc[sd_start, 'SnowDepth'] if sd_start is not None else 0
end_val = df_obs.loc[sd_end, 'SnowDepth'] if sd_end is not None else 0
obs_val = max(end_val - start_val, 0)
else:
obs_val = df_obs.loc[mask, 'Snowfall1h'].sum()
is_hit = row['MinVal'] <= obs_val <= row['MaxVal']
diff = 0.0
if obs_val < row['MinVal']: diff = obs_val - row['MinVal']
elif obs_val > row['MaxVal']: diff = obs_val - row['MaxVal']
merged_rows.append({
'Station': row['Station'], 'IssueDT': row['IssueDT'], 'TargetDT': row['TargetDT'],
'Fcst': row['MaxVal'], 'FcstText': row['FcstText'], 'Obs': obs_val, 'Diff': diff,
'MinVal': row['MinVal'], 'MaxVal': row['MaxVal'], 'IsRangeHit': is_hit
})
if merged_rows:
df = pd.DataFrame(merged_rows)
else:
try:
fcst_query = f"SELECT '{obs_cd}' AS Station, IssueDay || ' ' || IssueTime AS IssueDT, TargetDay || ' ' || TargetTime AS TargetDT, {'FcstRainfall' if is_rain else 'FcstSnowfall'} AS Fcst FROM {target_fcst_table} WHERE MsCd = ? AND IssueDay || ' ' || IssueTime BETWEEN ? AND ? AND TargetDay || ' ' || TargetTime BETWEEN ? AND ? AND ForecastStep = ?"
df_fcst = pd.read_sql_query(fcst_query, self.conn, params=(fcst_name, utc_iss_start, utc_iss_end, utc_tgt_start, utc_tgt_end, step_val))
except sqlite3.OperationalError:
continue
obs_start_dt = datetime.strptime(utc_tgt_start.split()[0], "%Y-%m-%d") - timedelta(days=2)
obs_query = f"SELECT ObsDay || ' ' || ObsTime AS ObsDT, Rainfall1h, Snowfall1h, SnowDepth FROM {target_obs_table} WHERE MsCd = ? AND ObsDay >= ?"
df_obs = pd.read_sql_query(obs_query, self.conn, params=(obs_cd, obs_start_dt.strftime("%Y-%m-%d")))
if df_fcst.empty or df_obs.empty: continue
df_obs['ObsDT'] = pd.to_datetime(df_obs['ObsDT']); df_obs.set_index('ObsDT', inplace=True)
df_obs_agg = df_obs.resample('1h').agg({'Rainfall1h': 'sum', 'Snowfall1h': 'sum', 'SnowDepth': 'last'})
df_obs_agg['SnowDepth'] = df_obs_agg['SnowDepth'].ffill()
if is_rain:
if step_val > 1: df_obs_agg['Obs'] = df_obs_agg['Rainfall1h'].rolling(window=step_val, min_periods=1).sum()
else: df_obs_agg['Obs'] = df_obs_agg['Rainfall1h']
else:
df_obs_agg['Obs'] = df_obs_agg['Snowfall1h'].rolling(window=step_val, min_periods=1).sum() if step_val > 1 else df_obs_agg['Snowfall1h']
df_obs_agg = df_obs_agg.reset_index(); df_obs_agg['TargetDT'] = df_obs_agg['ObsDT'].dt.strftime('%Y-%m-%d %H:%M')
df_merged = pd.merge(df_fcst, df_obs_agg[['TargetDT', 'Obs']], on='TargetDT', how='inner')
if df_merged.empty: continue
df = df_merged
df['Diff'] = df['Fcst'] - df['Obs']
df['FcstText'] = df['Fcst'].apply(lambda x: f"{x:.1f}")
if lead_time_txt != "期間制限なし":
max_hours = int(lead_time_txt.replace("時間以内", ""))
df['LeadTime'] = (pd.to_datetime(df['TargetDT']) - pd.to_datetime(df['IssueDT'])).dt.total_seconds() / 3600
df = df[(df['LeadTime'] >= 0) & (df['LeadTime'] <= max_hours)].copy()
if df.empty: continue
df = df.copy()
df['IssueDT'] = df['IssueDT'].apply(lambda x: (datetime.strptime(x, "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M") if x != "-" else "-")
if src_jma:
def convert_jma_target_dt(x):
if "～" in str(x):
parts = str(x).split("～")
t_s = (datetime.strptime(parts[0].strip(), "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
t_e = (datetime.strptime(parts[1].strip(), "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M")
return f"{t_s}～{t_e}"
return x
df['TargetDT'] = df['TargetDT'].apply(convert_jma_target_dt)
else:
df['TargetDT'] = df['TargetDT'].apply(lambda x: (datetime.strptime(x, "%Y-%m-%d %H:%M") + timedelta(hours=9)).strftime("%Y-%m-%d %H:%M"))
all_eval_dfs.append(df)
rmse = np.sqrt((df['Diff']**2).mean())
me = df['Diff'].mean(); mae = df['Diff'].abs().mean()
if src_jma:
hit_rate = len(df[df['IsRangeHit']]) / len(df) * 100 if len(df) > 0 else 0
tot0, hit0, pod0, far0, miss0, ts0 = len(df), hit_rate, 0, 0, 0, hit_rate
A0 = int(len(df) * hit_rate / 100); B0 = 0; C0 = 0
else:
A0 = len(df[(df['Fcst'] > 0) & (df['Obs'] > 0)]); B0 = len(df[(df['Fcst'] > 0) & (df['Obs'] == 0)])
C0 = len(df[(df['Fcst'] == 0) & (df['Obs'] > 0)]); D0 = len(df[(df['Fcst'] == 0) & (df['Obs'] == 0)])
tot0, hit0, pod0, far0, miss0, ts0 = calc_m(A0, B0, C0, D0)
thresholds = [1.0, 10.0, 20.0] if is_rain else [1.0, 10.0, 20.0, 30.0]
t_stats = []
for T in thresholds:
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_t = len(df[(df[fcst_col] >= T) & (df['Obs'] >= T)]); B_t = len(df[(df[fcst_col] >= T) & (df['Obs'] < T)])
C_t = len(df[(df[fcst_col] < T) & (df['Obs'] >= T)]); D_t = len(df[(df[fcst_col] < T) & (df['Obs'] < T)])
t_stats.append((calc_m(A_t, B_t, C_t, D_t), A_t, B_t, C_t, A_t+C_t, A_t+B_t))
pivot_fcst = df.pivot_table(index='IssueDT', columns='TargetDT', values='FcstText', aggfunc=lambda x: ' '.join(x))
pivot_obs = df.pivot_table(index='IssueDT', columns='TargetDT', values='Obs', aggfunc='mean')
pivot_diff = df.pivot_table(index='IssueDT', columns='TargetDT', values='Diff', aggfunc='mean')
combined_rows = []; targets = sorted(pivot_fcst.columns)
for issue in sorted(pivot_fcst.index):
obs_row = [f"{issue} (観測)"]; fcst_row = [f"{issue} (予報)"]; diff_row = ["差分 (予報-観測)"]
for t in targets:
obs_row.append(pivot_obs.loc[issue, t] if t in pivot_obs.columns and not pd.isna(pivot_obs.loc[issue, t]) else np.nan)
fcst_row.append(pivot_fcst.loc[issue, t] if t in pivot_fcst.columns and not pd.isna(pivot_fcst.loc[issue, t]) else np.nan)
diff_row.append(pivot_diff.loc[issue, t] if t in pivot_diff.columns and not pd.isna(pivot_diff.loc[issue, t]) else np.nan)
combined_rows.extend([obs_row, fcst_row, diff_row, [np.nan] * (len(targets) + 1)])
out_df = pd.DataFrame(combined_rows)
sheet_name = obs_cd.replace(":", "_")[:31]
chart_start_row = 12 + len(thresholds) * 5 + 2
start_row_matrix = chart_start_row + 33
daily_stats = []
try:
df['TargetDay_JST'] = df['TargetDT'].apply(lambda x: str(x).split()[0])
days = sorted(df['TargetDay_JST'].unique())
T_chart = 10.0
for d in days:
df_d = df[df['TargetDay_JST'] == d]
if df_d.empty: continue
r_val = np.sqrt((df_d['Diff']**2).mean())
m_val = df_d['Diff'].abs().mean()
me_val = df_d['Diff'].mean()
rmse_d = float(round(r_val, 2)) if pd.notna(r_val) else None
mae_d = float(round(m_val, 2)) if pd.notna(m_val) else None
me_d = float(round(me_val, 2)) if pd.notna(me_val) else None
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_d = len(df_d[(df_d[fcst_col] >= T_chart) & (df_d['Obs'] >= T_chart)])
B_d = len(df_d[(df_d[fcst_col] >= T_chart) & (df_d['Obs'] < T_chart)])
C_d = len(df_d[(df_d[fcst_col] < T_chart) & (df_d['Obs'] >= T_chart)])
D_d = len(df_d[(df_d[fcst_col] < T_chart) & (df_d['Obs'] < T_chart)])
_, hit_d, pod_d, far_d, miss_d, _ = calc_m(A_d, B_d, C_d, D_d)
daily_stats.append([d, rmse_d, mae_d, me_d, float(hit_d), float(miss_d), float(far_d), float(pod_d)])
except Exception as e:
print(f"日別集計エラー: {e}")
out_df.to_excel(writer, sheet_name=sheet_name, startrow=start_row_matrix+1, index=False, header=False)
ws = writer.sheets[sheet_name]
ws.cell(row=1, column=1, value="【検証パラメータ】(※出力時間軸: JST基準)")
ws.cell(row=2, column=1, value=f"観測地点: {obs_cd}"); ws.cell(row=2, column=2, value=f"要素: {self.ex_cmb_element.currentText()} ({'JMA手入力' if src_jma else target_fcst_table})")
ws.cell(row=3, column=1, value=f"時間ステップ: {step_val}時間"); ws.cell(row=3, column=2, value=f"発出制限: {lead_time_txt}")
ws.cell(row=4, column=1, value=f"発表日期間(JST): {self.ex_issue_from.date().toString('yyyy-MM-dd')} ～ {self.ex_issue_to.date().toString('yyyy-MM-dd')}")
ws.cell(row=5, column=1, value=f"対象日期間(JST): {self.ex_target_from.date().toString('yyyy-MM-dd')} ～ {self.ex_target_to.date().toString('yyyy-MM-dd')}")
ws.cell(row=7, column=1, value="【全体精度】")
ws.cell(row=8, column=1, value=f"RMSE: {rmse:.2f}"); ws.cell(row=8, column=2, value=f"ME: {me:.2f}"); ws.cell(row=8, column=3, value=f"MAE: {mae:.2f}")
ws.cell(row=9, column=1, value=f"全数: {tot0}"); ws.cell(row=9, column=2, value=f"的中回数(A): {A0}"); ws.cell(row=9, column=3, value=f"空振り回数(B): {B0}"); ws.cell(row=9, column=4, value=f"見逃し回数(C): {C0}")
ws.cell(row=10, column=1, value=f"的中率: {hit0:.1f}%"); ws.cell(row=10, column=2, value=f"捕捉率: {pod0:.1f}%"); ws.cell(row=10, column=3, value=f"空振り率: {far0:.1f}%"); ws.cell(row=10, column=4, value=f"見逃し率: {miss0:.1f}%"); ws.cell(row=10, column=5, value=f"TS: {ts0:.1f}%")
start_row = 12
for i, (T, stats_data) in enumerate(zip(thresholds, t_stats)):
s_mets, A_val, B_val, C_val, occur_val, fcst_val = stats_data
ws.cell(row=start_row, column=1, value=f"【独自指標: 閾値 >= {T} {unit}】")
ws.cell(row=start_row+1, column=1, value=f"全数: {s_mets[0]}"); ws.cell(row=start_row+1, column=2, value=f"的中(A): {A_val}"); ws.cell(row=start_row+1, column=3, value=f"空振(B): {B_val}"); ws.cell(row=start_row+1, column=4, value=f"見逃(C): {C_val}")
ws.cell(row=start_row+2, column=1, value=f"発現回数: {occur_val}"); ws.cell(row=start_row+2, column=2, value=f"予報回数: {fcst_val}")
ws.cell(row=start_row+3, column=1, value=f"的中率: {s_mets[1]:.1f}%"); ws.cell(row=start_row+3, column=2, value=f"捕捉率: {s_mets[2]:.1f}%"); ws.cell(row=start_row+3, column=3, value=f"空振り率: {s_mets[3]:.1f}%"); ws.cell(row=start_row+3, column=4, value=f"見逃し率: {s_mets[4]:.1f}%"); ws.cell(row=start_row+3, column=5, value=f"TS: {s_mets[5]:.1f}%")
start_row += 5
ws.cell(row=start_row_matrix, column=1, value="▼ 詳細マトリクスデータ (※時間軸: JST)")
ws.cell(row=start_row_matrix+1, column=1, value="発表日時(JST)")
for col_i, t in enumerate(targets):
if "～" in str(t):
ws.cell(row=start_row_matrix, column=col_i+2, value=str(t).split("～")[0].strip())
ws.cell(row=start_row_matrix+1, column=col_i+2, value=str(t).split("～")[-1].strip())
else:
dt_obj = pd.to_datetime(t)
ws.cell(row=start_row_matrix, column=col_i+2, value=dt_obj.strftime('%Y-%m-%d'))
ws.cell(row=start_row_matrix+1, column=col_i+2, value=dt_obj.strftime('%H:%M'))
if len(daily_stats) > 0:
try:
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.marker import Marker
headers = ["Date", "RMSE", "MAE", "ME", "的中率", "見逃し率", "空振り率", "捕捉率"]
for c_idx, h in enumerate(headers, start=53):
ws.cell(row=1, column=c_idx, value=h)
for r_idx, row_data in enumerate(daily_stats, start=2):
try:
date_obj = datetime.strptime(row_data[0], "%Y-%m-%d").date()
ws.cell(row=r_idx, column=53, value=date_obj)
except:
ws.cell(row=r_idx, column=53, value=row_data[0])
for c_offset, val in enumerate(row_data[1:]):
c_idx = 54 + c_offset
clean_val = None if pd.isna(val) else float(val)
ws.cell(row=r_idx, column=c_idx, value=clean_val)
data_len = len(daily_stats)
chart1 = LineChart()
chart1.title = "日別 誤差推移"
chart1.y_axis.title = f"誤差 ({unit})"
chart1.x_axis.title = "日付 (JST)"
chart1.legend.position = "b"
chart1.height = 8; chart1.width = 18
# 【修正】縦軸を強制表示し、グリッド線を消す
chart1.y_axis.delete = False
chart1.y_axis.tickLblPos = "nextTo"
chart1.y_axis.majorTickMark = "out"
chart1.y_axis.majorGridlines = None
data1 = Reference(ws, min_col=54, min_row=1, max_col=56, max_row=data_len+1)
cats1 = Reference(ws, min_col=53, min_row=2, max_row=data_len+1)
chart1.add_data(data1, titles_from_data=True)
chart1.set_categories(cats1)
for s in chart1.series: s.marker = Marker(symbol="circle", size=5)
ws.add_chart(chart1, f"A{chart_start_row}")
chart2 = LineChart()
chart2.title = f"日別 精度推移 (閾値: >= {T_chart} {unit})"
chart2.y_axis.title = "パーセント (%)"
chart2.x_axis.title = "日付 (JST)"
chart2.legend.position = "b"
chart2.height = 8; chart2.width = 18
# 【修正】縦軸を強制表示し、グリッド線を消す
chart2.y_axis.delete = False
chart2.y_axis.tickLblPos = "nextTo"
chart2.y_axis.majorTickMark = "out"
chart2.y_axis.majorGridlines = None
data2 = Reference(ws, min_col=57, min_row=1, max_col=60, max_row=data_len+1)
chart2.add_data(data2, titles_from_data=True)
chart2.set_categories(cats1)
for s in chart2.series: s.marker = Marker(symbol="circle", size=5)
ws.add_chart(chart2, f"A{chart_start_row + 16}")
except Exception as e:
print(f"グラフ生成失敗: {e}")
ws.cell(row=chart_start_row, column=1, value=f"※グラフ生成に失敗しました: {e}")
wrote_any = True
if obs_cd_sel == "全地点及び全地点総合評価" and all_eval_dfs:
df_all = pd.concat(all_eval_dfs, ignore_index=True)
sheet_name = "総合評価"
# 白紙化バグを修正
pd.DataFrame().to_excel(writer, sheet_name=sheet_name, index=False, header=False)
ws_all = writer.sheets[sheet_name]
rmse_all = np.sqrt((df_all['Diff']**2).mean())
me_all = df_all['Diff'].mean(); mae_all = df_all['Diff'].abs().mean()
if src_jma:
A0_all = len(df_all[(df_all['MaxVal'] > 0) & (df_all['Obs'] > 0) & df_all['IsRangeHit']])
hit_rate_all = len(df_all[df_all['IsRangeHit']]) / len(df_all) * 100 if len(df_all) > 0 else 0
tot0_all, hit0_all, pod0_all, far0_all, miss0_all, ts0_all = len(df_all), hit_rate_all, 0, 0, 0, hit_rate_all
B0_all = 0; C0_all = 0
else:
A0_all = len(df_all[(df_all['Fcst'] > 0) & (df_all['Obs'] > 0)]); B0_all = len(df_all[(df_all['Fcst'] > 0) & (df_all['Obs'] == 0)])
C0_all = len(df_all[(df_all['Fcst'] == 0) & (df_all['Obs'] > 0)]); D0_all = len(df_all[(df_all['Fcst'] == 0) & (df_all['Obs'] == 0)])
tot0_all, hit0_all, pod0_all, far0_all, miss0_all, ts0_all = calc_m(A0_all, B0_all, C0_all, D0_all)
ws_all.cell(row=1, column=1, value="【全地点総合評価 パラメータ】(※JST出力)")
ws_all.cell(row=2, column=1, value=f"要素: {self.ex_cmb_element.currentText()}"); ws_all.cell(row=2, column=2, value=f"ステップ: {step_val}時間")
ws_all.cell(row=3, column=1, value=f"発表日期間(JST): {self.ex_issue_from.date().toString('yyyy-MM-dd')} ～ {self.ex_issue_to.date().toString('yyyy-MM-dd')}")
ws_all.cell(row=4, column=1, value=f"対象日期間(JST): {self.ex_target_from.date().toString('yyyy-MM-dd')} ～ {self.ex_target_to.date().toString('yyyy-MM-dd')}")
ws_all.cell(row=6, column=1, value="【全体精度】")
ws_all.cell(row=7, column=1, value=f"RMSE: {rmse_all:.2f}"); ws_all.cell(row=7, column=2, value=f"ME: {me_all:.2f}"); ws_all.cell(row=7, column=3, value=f"MAE: {mae_all:.2f}")
ws_all.cell(row=8, column=1, value=f"全数: {tot0_all}"); ws_all.cell(row=8, column=2, value=f"的中回数(A): {A0_all}"); ws_all.cell(row=8, column=3, value=f"空振り回数(B): {B0_all}"); ws_all.cell(row=8, column=4, value=f"見逃し回数(C): {C0_all}")
ws_all.cell(row=9, column=1, value=f"的中率: {hit0_all:.1f}%"); ws_all.cell(row=9, column=2, value=f"捕捉率: {pod0_all:.1f}%"); ws_all.cell(row=9, column=3, value=f"空振り率: {far0_all:.1f}%"); ws_all.cell(row=9, column=4, value=f"見逃し率: {miss0_all:.1f}%"); ws_all.cell(row=9, column=5, value=f"TS: {ts0_all:.1f}%")
start_row_all = 11
for T in thresholds:
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_t = len(df_all[(df_all[fcst_col] >= T) & (df_all['Obs'] >= T)]); B_t = len(df_all[(df_all[fcst_col] >= T) & (df_all['Obs'] < T)])
C_t = len(df_all[(df_all[fcst_col] < T) & (df_all['Obs'] >= T)]); D_t = len(df_all[(df_all[fcst_col] < T) & (df_all['Obs'] < T)])
tot_t, hit_t, pod_t, far_t, miss_t, ts_t = calc_m(A_t, B_t, C_t, D_t)
ws_all.cell(row=start_row_all, column=1, value=f"【独自指標: 閾値 >= {T} {unit}】")
ws_all.cell(row=start_row_all+1, column=1, value=f"全数: {tot_t}"); ws_all.cell(row=start_row_all+1, column=2, value=f"的中(A): {A_t}"); ws_all.cell(row=start_row_all+1, column=3, value=f"空振(B): {B_t}"); ws_all.cell(row=start_row_all+1, column=4, value=f"見逃(C): {C_t}")
ws_all.cell(row=start_row_all+2, column=1, value=f"発現回数: {A_t+C_t}"); ws_all.cell(row=start_row_all+2, column=2, value=f"予報回数: {A_t+B_t}")
ws_all.cell(row=start_row_all+3, column=1, value=f"的中率: {hit_t:.1f}%"); ws_all.cell(row=start_row_all+3, column=2, value=f"捕捉率: {pod_t:.1f}%"); ws_all.cell(row=start_row_all+3, column=3, value=f"空振り率: {far_t:.1f}%"); ws_all.cell(row=start_row_all+3, column=4, value=f"見逃し率: {miss_t:.1f}%"); ws_all.cell(row=start_row_all+3, column=5, value=f"TS: {ts_t:.1f}%")
start_row_all += 5
# ---------------------------------------------------------
# 【追加】総合評価シート用のグラフ生成
# ---------------------------------------------------------
daily_stats_all = []
try:
df_all['TargetDay_JST'] = df_all['TargetDT'].apply(lambda x: str(x).split()[0])
days_all = sorted(df_all['TargetDay_JST'].unique())
T_chart = 10.0
for d in days_all:
df_d = df_all[df_all['TargetDay_JST'] == d]
if df_d.empty: continue
r_val = np.sqrt((df_d['Diff']**2).mean())
m_val = df_d['Diff'].abs().mean()
me_val = df_d['Diff'].mean()
rmse_d = float(round(r_val, 2)) if pd.notna(r_val) else None
mae_d = float(round(m_val, 2)) if pd.notna(m_val) else None
me_d = float(round(me_val, 2)) if pd.notna(me_val) else None
fcst_col = 'MaxVal' if src_jma else 'Fcst'
A_d = len(df_d[(df_d[fcst_col] >= T_chart) & (df_d['Obs'] >= T_chart)])
B_d = len(df_d[(df_d[fcst_col] >= T_chart) & (df_d['Obs'] < T_chart)])
C_d = len(df_d[(df_d[fcst_col] < T_chart) & (df_d['Obs'] >= T_chart)])
D_d = len(df_d[(df_d[fcst_col] < T_chart) & (df_d['Obs'] < T_chart)])
_, hit_d, pod_d, far_d, miss_d, _ = calc_m(A_d, B_d, C_d, D_d)
daily_stats_all.append([d, rmse_d, mae_d, me_d, float(hit_d), float(miss_d), float(far_d), float(pod_d)])
except Exception as e:
print(f"総合評価 日別集計エラー: {e}")
if len(daily_stats_all) > 0:
try:
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.marker import Marker
chart_start_row_all = start_row_all + 2
headers = ["Date", "RMSE", "MAE", "ME", "的中率", "見逃し率", "空振り率", "捕捉率"]
for c_idx, h in enumerate(headers, start=53):
ws_all.cell(row=1, column=c_idx, value=h)
for r_idx, row_data in enumerate(daily_stats_all, start=2):
try:
date_obj = datetime.strptime(row_data[0], "%Y-%m-%d").date()
ws_all.cell(row=r_idx, column=53, value=date_obj)
except:
ws_all.cell(row=r_idx, column=53, value=row_data[0])
for c_offset, val in enumerate(row_data[1:]):
c_idx = 54 + c_offset
clean_val = None if pd.isna(val) else float(val)
ws_all.cell(row=r_idx, column=c_idx, value=clean_val)
data_len = len(daily_stats_all)
chart1_all = LineChart()
chart1_all.title = "【総合】日別 誤差推移"
chart1_all.y_axis.title = f"誤差 ({unit})"
chart1_all.x_axis.title = "日付 (JST)"
chart1_all.legend.position = "b"
chart1_all.height = 8; chart1_all.width = 18
chart1_all.y_axis.delete = False
chart1_all.y_axis.tickLblPos = "nextTo"
chart1_all.y_axis.majorTickMark = "out"
chart1_all.y_axis.majorGridlines = None
data1_all = Reference(ws_all, min_col=54, min_row=1, max_col=56, max_row=data_len+1)
cats1_all = Reference(ws_all, min_col=53, min_row=2, max_row=data_len+1)
chart1_all.add_data(data1_all, titles_from_data=True)
chart1_all.set_categories(cats1_all)
for s in chart1_all.series: s.marker = Marker(symbol="circle", size=5)
ws_all.add_chart(chart1_all, f"A{chart_start_row_all}")
chart2_all = LineChart()
chart2_all.title = f"【総合】日別 精度推移 (閾値: >= {T_chart} {unit})"
chart2_all.y_axis.title = "パーセント (%)"
chart2_all.x_axis.title = "日付 (JST)"
chart2_all.legend.position = "b"
chart2_all.height = 8; chart2_all.width = 18
chart2_all.y_axis.delete = False
chart2_all.y_axis.tickLblPos = "nextTo"
chart2_all.y_axis.majorTickMark = "out"
chart2_all.y_axis.majorGridlines = None
data2_all = Reference(ws_all, min_col=57, min_row=1, max_col=60, max_row=data_len+1)
chart2_all.add_data(data2_all, titles_from_data=True)
chart2_all.set_categories(cats1_all)
for s in chart2_all.series: s.marker = Marker(symbol="circle", size=5)
ws_all.add_chart(chart2_all, f"A{chart_start_row_all + 16}")
except Exception as e:
print(f"総合グラフ生成失敗: {e}")
ws_all.cell(row=chart_start_row_all, column=1, value=f"※総合グラフ生成に失敗しました: {e}")
if not wrote_any:
pd.DataFrame([["指定された条件に合致するデータが存在しませんでした。"]]).to_excel(writer, sheet_name="データなし", index=False, header=False)
if wrote_any: QMessageBox.information(self, "完了", f"レポートを出力しました。(内部UTC計算・JST軸出力)\n保存先: {path}")
else: QMessageBox.warning(self, "データなし", "出力可能なデータがありませんでした。「データなし」シートのみ出力しました。")
except Exception as e:
logging.error(f"Excel出力失敗: {e}", exc_info=True)
QMessageBox.critical(self, "エラー", f"出力失敗:\n{e}")
# ==========================================
# タブ7: データベース管理 (モニタリング機能統合版)
# ==========================================
def setup_tab7(self):
scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
# --- バックアップ領域 ---
group_backup = QGroupBox("データベースのバックアップ"); b_layout = QVBoxLayout(); b_layout.setContentsMargins(15, 20, 15, 15)
h1 = QHBoxLayout()
# ★ 修正ポイント: 初期化時に読み込んだパスをそのまま表示する
self.lbl_backup_folder = QLabel(f"📁 バックアップ先: {self.backup_folder}")
self.lbl_backup_folder.setStyleSheet("font-weight: bold; color: #0366D6;")
btn_sel_b = QPushButton("保存先フォルダを選択"); btn_sel_b.clicked.connect(self.select_backup_folder)
h1.addWidget(self.lbl_backup_folder); h1.addWidget(btn_sel_b); h1.addStretch()
b_layout.addLayout(h1)
h2 = QHBoxLayout()
btn_manual_b = QPushButton("今すぐバックアップを作成"); btn_manual_b.setObjectName("ActionBtn"); btn_manual_b.clicked.connect(self.manual_backup)
self.lbl_auto_b = QLabel("自動バックアップ: 停止中"); self.lbl_auto_b.setStyleSheet("color: #D73A49; font-weight: bold;")
self.btn_auto_b = QPushButton("12時間毎の自動バックアップを開始"); self.btn_auto_b.setObjectName("AutoBtn"); self.btn_auto_b.clicked.connect(self.toggle_auto_backup)
h2.addWidget(btn_manual_b); h2.addWidget(self.btn_auto_b); h2.addWidget(self.lbl_auto_b); h2.addStretch()
b_layout.addLayout(h2)
group_backup.setLayout(b_layout)
# --- 復元領域（全データ） ---
group_restore = QGroupBox("データの復元 (統合)"); r_layout = QVBoxLayout(); r_layout.setContentsMargins(15, 20, 15, 15)
lbl_r = QLabel("過去のバックアップファイルを選択し、現在のDBにデータを結合（復元）します。\n※重複するデータは現在のものが優先され、不足分のみ安全に追記されます。")
btn_restore = QPushButton("バックアップDBを選択して【全データ】を復元"); btn_restore.setObjectName("TestBtn"); btn_restore.clicked.connect(self.restore_database)
r_layout.addWidget(lbl_r); r_layout.addWidget(btn_restore); r_layout.addSpacing(10)
# --- 復元領域（個別テーブル指定） ---
h_single = QHBoxLayout()
self.txt_single_restore = QLineEdit(); self.txt_single_restore.setPlaceholderText("例: FCST_type01")
self.txt_single_restore.setMinimumWidth(200)
btn_single_restore = QPushButton("指定した個別テーブルのみを復元"); btn_single_restore.setObjectName("ActionBtn")
btn_single_restore.clicked.connect(self.restore_single_table)
h_single.addWidget(QLabel("個別テーブル名:")); h_single.addWidget(self.txt_single_restore)
h_single.addWidget(btn_single_restore); h_single.addStretch()
r_layout.addLayout(h_single)
group_restore.setLayout(r_layout)
# ==========================================
# モニタリング・保守領域
# ==========================================
group_monitor = QGroupBox("データベース蓄積状況モニタリング")
layout_monitor = QVBoxLayout(); layout_monitor.setContentsMargins(15, 20, 15, 15)
h_btn_layout = QHBoxLayout()
btn_scan = QPushButton("DB全体スキャン (期間・件数確認)")
btn_scan.clicked.connect(self.scan_database_status)
btn_scan.setStyleSheet("background-color: #e0f7fa; font-weight: bold; color: #000000;")
# ★★★ 修正ポイント: テーブル指定用のコンボボックスを追加 ★★★
self.cmb_gap_table = QComboBox()
self.cmb_gap_table.setMinimumWidth(150)
self.update_gap_table_combo()
btn_gap = QPushButton("選択したテーブルの欠落検知")
btn_gap.clicked.connect(self.detect_data_gaps)
h_btn_layout.addWidget(btn_scan)
h_btn_layout.addStretch()
h_btn_layout.addWidget(QLabel("ギャップ検知対象:"))
h_btn_layout.addWidget(self.cmb_gap_table)
h_btn_layout.addWidget(btn_gap)
layout_monitor.addLayout(h_btn_layout)
self.table_monitor = QTableWidget()
self.table_monitor.setColumnCount(5)
self.table_monitor.setHorizontalHeaderLabels(["データ種類 (テーブル)", "最古データ日時", "最新データ日時", "レコード件数", "状態・警告"])
self.table_monitor.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
self.table_monitor.setMinimumHeight(200)
layout_monitor.addWidget(self.table_monitor)
group_monitor.setLayout(layout_monitor)
group_maint = QGroupBox("システム保守 (パフォーマンス維持)")
layout_maint = QHBoxLayout(); layout_maint.setContentsMargins(15, 20, 15, 15)
btn_vacuum = QPushButton("DB最適化 (VACUUM処理による圧縮・高速化)")
btn_vacuum.clicked.connect(self.optimize_database)
layout_maint.addWidget(btn_vacuum)
group_maint.setLayout(layout_maint)
# --- レイアウトへの追加 ---
layout.addWidget(group_backup)
layout.addWidget(group_restore)
layout.addWidget(group_monitor)
layout.addWidget(group_maint)
layout.addStretch()
self.tabs.addTab(scroll, "7. データベース管理")
self.auto_backup_timer = QTimer(self)
self.auto_backup_timer.timeout.connect(self.manual_backup)
# ★ 修正: ここでの self.backup_folder = "" を削除（リセットさせないため）
def restore_single_table(self):
target_table = self.txt_single_restore.text().strip()
if not target_table:
return QMessageBox.warning(self, "エラー", "復元したいテーブル名を入力してください。")
path, _ = QFileDialog.getOpenFileName(self, "復元元のDBファイルを選択", "", "SQLite DB (*.db);;すべてのファイル (*)")
if not path: return
try:
cursor = self.conn.cursor()
# バックアップDBを別名(backup_db)としてアタッチ
cursor.execute(f"ATTACH DATABASE '{path}' AS backup_db")
# 指定されたテーブルがバックアップ内に存在するか確認
cursor.execute("SELECT name FROM backup_db.sqlite_master WHERE type='table' AND name=?", (target_table,))
if not cursor.fetchone():
cursor.execute("DETACH DATABASE backup_db")
return QMessageBox.warning(self, "エラー", f"選択したバックアップDB内にテーブル '{target_table}' が見つかりません。")
# 現在のDB側にテーブルが無ければ作成（FCST_で始まる前提）
self.create_fcst_table_if_not_exists(target_table)
# データの安全な結合（重複は無視し、新規のみ挿入）
cursor.execute(f"INSERT OR IGNORE INTO {target_table} SELECT * FROM backup_db.{target_table}")
self.conn.commit()
cursor.execute("DETACH DATABASE backup_db")
self.refresh_source_combos()
# プレビュー画面を該当テーブルに切り替え
idx = self.cmb_preview_table.findText(target_table)
if idx >= 0: self.cmb_preview_table.setCurrentIndex(idx)
logging.info(f"テーブル '{target_table}' の復元完了")
QMessageBox.information(self, "完了", f"テーブル '{target_table}' の復元・結合が完了しました。")
except Exception as e:
logging.error(f"個別テーブルの復元エラー: {e}", exc_info=True)
QMessageBox.critical(self, "エラー", f"個別テーブルの復元中にエラーが発生しました:\n{e}")
try:
self.conn.cursor().execute("DETACH DATABASE backup_db")
except:
pass
def select_backup_folder(self):
folder = QFileDialog.getExistingDirectory(self, "バックアップ先フォルダを選択")
if folder:
self.backup_folder = folder
# ★ 修正ポイント: 変更したバックアップフォルダを記憶
self.settings.setValue("last_backup_folder", folder)
self.lbl_backup_folder.setText(f"📁 バックアップ先: {folder}")
def manual_backup(self):
if self._is_backing_up:
logging.warning("バックアップ処理が既に実行中です。")
return
self._is_backing_up = True
try:
if not self.backup_folder:
if not self.auto_backup_timer.isActive(): QMessageBox.warning(self, "警告", "バックアップ先のフォルダを設定してください。")
return
now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
dest = os.path.join(self.backup_folder, f"weather_db_backup_{now_str}.db")
self.conn.commit()
shutil.copy2(self.db_path, dest)
logging.info(f"バックアップ作成成功: {dest}")
if not self.auto_backup_timer.isActive():
QMessageBox.information(self, "完了", f"データベースのバックアップを作成しました。\n\n保存先:\n{dest}")
except Exception as e:
logging.error(f"バックアップ失敗: {e}", exc_info=True)
if self.auto_backup_timer.isActive():
self.tray_icon.showMessage("バックアップエラー", "自動バックアップに失敗しました。ログを確認してください。", QSystemTrayIcon.MessageIcon.Warning, 5000)
else:
QMessageBox.critical(self, "エラー", f"バックアップに失敗しました:\n{e}")
finally:
self._is_backing_up = False
def toggle_auto_backup(self):
if self.auto_backup_timer.isActive():
self.auto_backup_timer.stop()
self.btn_auto_b.setText("12時間毎の自動バックアップを開始")
self.lbl_auto_b.setText("自動バックアップ: 停止中")
logging.info("自動バックアップタイマー停止")
else:
if not self.backup_folder:
QMessageBox.warning(self, "警告", "バックアップ先のフォルダを設定してください。")
return
self.auto_backup_timer.start(12 * 60 * 60 * 1000)
self.btn_auto_b.setText("自動バックアップを停止")
self.lbl_auto_b.setText("🔴 自動バックアップ: 稼働中 (12時間毎)")
logging.info("自動バックアップタイマー開始 (12時間毎)")
self.manual_backup()
def restore_database(self):
path, _ = QFileDialog.getOpenFileName(self, "復元元のDBファイルを選択", "", "SQLite DB (*.db);;すべてのファイル (*)")
if not path: return
try:
cursor = self.conn.cursor()
cursor.execute(f"ATTACH DATABASE '{path}' AS backup_db")
cursor.execute("INSERT OR IGNORE INTO OBS SELECT * FROM backup_db.OBS")
cursor.execute("INSERT OR IGNORE INTO OBS_GP SELECT * FROM backup_db.OBS_GP")
cursor.execute("INSERT OR IGNORE INTO JMA_FCST SELECT * FROM backup_db.JMA_FCST")
cursor.execute("SELECT name FROM backup_db.sqlite_master WHERE type='table' AND name LIKE 'FCST_%'")
backup_tables = [r[0] for r in cursor.fetchall()]
for tbl in backup_tables:
self.create_fcst_table_if_not_exists(tbl)
cursor.execute(f"INSERT OR IGNORE INTO {tbl} SELECT * FROM backup_db.{tbl}")
self.conn.commit()
cursor.execute("DETACH DATABASE backup_db")
self.refresh_source_combos()
logging.info("全データの復元（結合）完了")
QMessageBox.information(self, "復元完了", "データの結合（復元）が完了しました。")
self.update_tab1_station_combo()
self.search_obs_table()
except Exception as e:
logging.error(f"復元中エラー: {e}", exc_info=True)
QMessageBox.critical(self, "エラー", f"復元中にエラーが発生しました:\n{e}")
try: self.conn.cursor().execute("DETACH DATABASE backup_db")
except: pass
def scan_database_status(self):
"""DB内の全テーブルをスキャンし、期間と件数をリストアップする"""
try:
self.table_monitor.setRowCount(0)
cursor = self.conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = [r[0] for r in cursor.fetchall() if r[0] not in ('sqlite_sequence', 'Mapping')]
row_idx = 0
for tbl in tables:
try:
if tbl.startswith('OBS'):
cursor.execute(f"SELECT COUNT(*), MIN(ObsDay || ' ' || ObsTime), MAX(ObsDay || ' ' || ObsTime) FROM {tbl}")
count, min_dt, max_dt = cursor.fetchone()
name_disp = f"観測データ ({tbl})"
elif tbl == 'JMA_FCST':
cursor.execute(f"SELECT COUNT(*), MIN(TargetStart), MAX(TargetEnd) FROM {tbl}")
count, min_dt, max_dt = cursor.fetchone()
name_disp = "気象庁 手入力予報"
elif tbl.startswith('FCST_'):
cursor.execute(f"SELECT COUNT(*), MIN(IssueDay || ' ' || IssueTime), MAX(IssueDay || ' ' || IssueTime) FROM {tbl}")
count, min_dt, max_dt = cursor.fetchone()
name_disp = f"予報モデル ({tbl})"
else:
continue
if count == 0:
min_dt, max_dt = "-", "-"
status = "データなし"
else:
status = "正常"
self.table_monitor.insertRow(row_idx)
self.table_monitor.setItem(row_idx, 0, QTableWidgetItem(name_disp))
self.table_monitor.setItem(row_idx, 1, QTableWidgetItem(str(min_dt)))
self.table_monitor.setItem(row_idx, 2, QTableWidgetItem(str(max_dt)))
self.table_monitor.setItem(row_idx, 3, QTableWidgetItem(f"{count:,} 件"))
status_item = QTableWidgetItem(status)
if count == 0: status_item.setForeground(QColor("gray"))
self.table_monitor.setItem(row_idx, 4, status_item)
row_idx += 1
except sqlite3.OperationalError:
# まだカラムが整備されていない空テーブル等のエラーをスキップ
continue
except Exception as e:
logging.error(f"スキャンエラー: {e}")
QMessageBox.warning(self, "スキャンエラー", f"スキャン中にエラーが発生しました:\n{e}")
def update_gap_table_combo(self):
"""ギャップ検知用のテーブル選択コンボボックスを最新状態に更新する"""
if hasattr(self, 'cmb_gap_table'):
current = self.cmb_gap_table.currentText()
self.cmb_gap_table.blockSignals(True)
self.cmb_gap_table.clear()
try:
cursor = self.conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
exclude_tables = {'sqlite_sequence', 'Mapping', 'TargetStations', 'ManualExtractSettings', 'GPV_Points'}
tables = [r[0] for r in cursor.fetchall() if r[0] not in exclude_tables]
self.cmb_gap_table.addItems(tables)
idx = self.cmb_gap_table.findText(current)
if idx >= 0: self.cmb_gap_table.setCurrentIndex(idx)
except Exception:
pass
self.cmb_gap_table.blockSignals(False)
def detect_data_gaps(self):
"""指定された単一テーブルのデータの抜け(ギャップ)を検知する"""
target_table = self.cmb_gap_table.currentText()
if not target_table:
return QMessageBox.warning(self, "エラー", "チェック対象のテーブルを選択してください。")
try:
cursor = self.conn.cursor()
# テーブルのカラム構成を取得
cursor.execute(f"PRAGMA table_info({target_table})")
cols = [col_info[1] for col_info in cursor.fetchall()]
query = ""
gap_threshold = 1.5
# テーブルのカラム構成に合わせてクエリと閾値を動的に設定
if "ObsDay" in cols and "ObsTime" in cols:
# 観測データ (OBS, OBS_GPなど): 1時間おき想定
query = f"SELECT DISTINCT ObsDay, ObsTime FROM {target_table} ORDER BY ObsDay DESC, ObsTime DESC"
gap_threshold = 1.5
elif "IssueDay" in cols and "IssueTime" in cols:
# 予報モデル (FCST系): 3〜6時間おき想定
query = f"SELECT DISTINCT IssueDay, IssueTime FROM {target_table} ORDER BY IssueDay DESC, IssueTime DESC"
gap_threshold = 6.5
elif "TargetStart" in cols:
# 気象庁予報 (JMA_FCSTなど)
query = f"SELECT DISTINCT substr(TargetStart, 1, 10), substr(TargetStart, 12, 5) FROM {target_table} ORDER BY TargetStart DESC"
gap_threshold = 6.5
else:
return QMessageBox.warning(self, "エラー", "このテーブルは時間軸カラムの仕様が異なるためチェックできません。")
QApplication.setOverrideCursor(Qt.CursorShape.WaitCursor)
cursor.execute(query)
rows = cursor.fetchall()
QApplication.restoreOverrideCursor()
if len(rows) < 2:
return QMessageBox.information(self, "ギャップ検知", f"【{target_table}】 には比較可能な十分なデータがありません。")
gaps = []
for i in range(len(rows)-1):
day1, time1 = str(rows[i][0]).strip(), str(rows[i][1]).strip()
day2, time2 = str(rows[i+1][0]).strip(), str(rows[i+1][1]).strip()
time1_short = time1[:5] if time1 else "00:00"
time2_short = time2[:5] if time2 else "00:00"
dt1_str = f"{day1} {time1_short}"
dt2_str = f"{day2} {time2_short}"
if len(dt1_str) < 15 or len(dt2_str) < 15: continue
try:
dt1 = datetime.strptime(dt1_str, "%Y-%m-%d %H:%M")
dt2 = datetime.strptime(dt2_str, "%Y-%m-%d %H:%M")
except ValueError:
continue
diff = (dt1 - dt2).total_seconds() / 3600
# 閾値以上間隔が空いていたら欠落とみなす
if diff > gap_threshold:
gaps.append(f"・{dt2.strftime('%Y/%m/%d %H:%M')} ～ {dt1.strftime('%Y/%m/%d %H:%M')} (約{int(diff)}時間欠落)")
if gaps:
msg = "\n".join(gaps)
self.show_large_text_dialog(f"【{target_table}】 データ欠落検知結果", f"{len(gaps)} 箇所のデータ欠落が見つかりました。\n\n{msg}")
else:
QMessageBox.information(self, "ギャップ検知", f"【{target_table}】 に異常なデータの欠落は見つかりませんでした。\n(設定した閾値内での連続性を確認しました)")
except Exception as e:
try: QApplication.restoreOverrideCursor()
except: pass
logging.error(f"ギャップ検知エラー: {e}")
QMessageBox.warning(self, "エラー", f"ギャップ検知中にエラーが発生しました:\n{e}")
def show_large_text_dialog(self, title, text):
"""長文テキストを大きなウィンドウで表示するためのカスタムダイアログ"""
from PyQt6.QtWidgets import QDialog, QVBoxLayout, QTextEdit, QPushButton
dialog = QDialog(self)
dialog.setWindowTitle(title)
dialog.resize(800, 600) # 画面サイズを大きく設定
layout = QVBoxLayout(dialog)
text_edit = QTextEdit()
text_edit.setReadOnly(True)
text_edit.setPlainText(text)
# プロフェッショナルな用途に合わせて、等幅フォントで見やすく設定
text_edit.setStyleSheet("font-family: Consolas, 'Courier New', monospace; font-size: 13px; background-color: #FAFBFC;")
layout.addWidget(text_edit)
btn_close = QPushButton("閉じる")
btn_close.clicked.connect(dialog.accept)
btn_close.setStyleSheet("padding: 8px; font-weight: bold;")
layout.addWidget(btn_close)
dialog.exec()
def optimize_database(self):
"""VACUUMを実行してDBを最適化する"""
reply = QMessageBox.question(self, "確認", "データベースの最適化(VACUUM)を実行しますか？\n肥大化したDBを圧縮して高速化しますが、少し時間がかかります。",
QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No)
if reply == QMessageBox.StandardButton.Yes:
try:
QApplication.setOverrideCursor(Qt.CursorShape.WaitCursor)
self.conn.execute("VACUUM")
QApplication.restoreOverrideCursor()
QMessageBox.information(self, "完了", "データベースの最適化が完了しました。")
logging.info("DB最適化(VACUUM)完了")
except Exception as e:
QApplication.restoreOverrideCursor()
logging.error(f"最適化エラー: {e}")
QMessageBox.critical(self, "エラー", f"最適化に失敗しました:\n{e}")
if __name__ == "__main__":
app = QApplication(sys.argv)
# ★追加: OSのダークモード等に影響されず、デザインを統一・安定させる
app.setStyle("Fusion")
app.setQuitOnLastWindowClosed(False)
QImageReader.setAllocationLimit(0)
window = WeatherVerificationApp()
window.show()
sys.exit(app.exec())
未分類