libs.data.loader

  1"""
  2libs/data/loader.py
  3"""
  4
  5import logging
  6import re
  7from datetime import datetime
  8
  9import pandas as pd
 10
 11import libs.global_value as g
 12from libs.utils import dbutil
 13
 14
 15def read_data(keyword: str) -> pd.DataFrame:
 16    """データベースからデータを取得する
 17
 18    Args:
 19        keyword (str): SQL選択キーワード
 20
 21    Returns:
 22        pd.DataFrame: 集計結果
 23    """
 24
 25    # デバッグ用
 26    pd.set_option("display.max_rows", None)
 27    pd.set_option("display.max_columns", None)
 28
 29    if "starttime" in g.params:
 30        g.params.update(starttime=g.params["starttime"].format("sql"))
 31    if "endtime" in g.params:
 32        g.params.update(endtime=g.params["endtime"].format("sql"))
 33    if "rule_version" not in g.params:
 34        g.params.update(rule_version=g.cfg.mahjong.rule_version)
 35
 36    sql = query_modification(dbutil.query(keyword))
 37    logging.debug("prm: %s", g.params)
 38    logging.debug("sql: %s", named_query(sql))
 39
 40    df = pd.read_sql(
 41        sql=sql,
 42        con=dbutil.connection(g.cfg.setting.database_file),
 43        params=g.params,
 44    )
 45    logging.trace(df)  # type: ignore
 46
 47    return df
 48
 49
 50def query_modification(sql: str) -> str:
 51    """クエリをオプションの内容で修正する
 52
 53    Args:
 54        sql (str): 修正するクエリ
 55
 56    Returns:
 57        str: 修正後のクエリ
 58    """
 59
 60    if g.params.get("individual"):  # 個人集計
 61        sql = sql.replace("--[individual] ", "")
 62        # ゲスト関連フラグ
 63        if g.params.get("unregistered_replace"):
 64            sql = sql.replace("--[unregistered_replace] ", "")
 65            if g.params.get("guest_skip"):
 66                sql = sql.replace("--[guest_not_skip] ", "")
 67            else:
 68                sql = sql.replace("--[guest_skip] ", "")
 69        else:
 70            sql = sql.replace("--[unregistered_not_replace] ", "")
 71    else:  # チーム集計
 72        g.params.update(unregistered_replace=False)
 73        g.params.update(guest_skip=True)
 74        sql = sql.replace("--[team] ", "")
 75        if not g.params.get("friendly_fire"):
 76            sql = sql.replace("--[friendly_fire] ", "")
 77
 78    # 集約集計
 79    match g.params.get("collection"):
 80        case "daily":
 81            sql = sql.replace("--[collection_daily] ", "")
 82            sql = sql.replace("--[collection] ", "")
 83        case "monthly":
 84            sql = sql.replace("--[collection_monthly] ", "")
 85            sql = sql.replace("--[collection] ", "")
 86        case "yearly":
 87            sql = sql.replace("--[collection_yearly] ", "")
 88            sql = sql.replace("--[collection] ", "")
 89        case "all":
 90            sql = sql.replace("--[collection_all] ", "")
 91            sql = sql.replace("--[collection] ", "")
 92        case _:
 93            sql = sql.replace("--[not_collection] ", "")
 94
 95    # ルール横断集計
 96    if g.params.get("mixed"):
 97        sql = sql.replace("game_info.rule_version = :rule_version", "1 = 1")
 98        sql = sql.replace("results.rule_version = :rule_version", "1 = 1")
 99        sql = sql.replace("rule_version = :rule_version", "1 = 1")
100
101    # コメント検索
102    if g.params.get("search_word") or g.params.get("group_length"):
103        sql = sql.replace("--[group_by] ", "")
104    else:
105        sql = sql.replace("--[not_group_by] ", "")
106
107    if g.params.get("search_word"):
108        sql = sql.replace("--[search_word] ", "")
109    else:
110        sql = sql.replace("--[not_search_word] ", "")
111
112    if g.params.get("group_length"):
113        sql = sql.replace("--[group_length] ", "")
114    else:
115        sql = sql.replace("--[not_group_length] ", "")
116        if g.params.get("search_word"):
117            sql = sql.replace("--[comment] ", "")
118        else:
119            sql = sql.replace("--[not_comment] ", "")
120
121    # 直近N検索用（全範囲取得してから絞る）
122    if g.params.get("target_count") != 0:
123        sql = sql.replace(
124            "and my.playtime between",
125            "-- and my.playtime between"
126        )
127
128    # プレイヤーリスト
129    if g.params.get("player_name"):
130        sql = sql.replace("--[player_name] ", "")
131        sql = sql.replace(
132            "<<player_list>>",
133            ":" + ", :".join(g.params["player_list"])
134        )
135    sql = sql.replace("<<guest_mark>>", g.cfg.setting.guest_mark)
136
137    # フラグの処理
138    match g.cfg.aggregate_unit:
139        case "M":
140            sql = sql.replace("<<collection>>", "substr(collection_daily, 1, 7) as 集計")
141            sql = sql.replace("<<group by>>", "group by 集計")
142        case "Y":
143            sql = sql.replace("<<collection>>", "substr(collection_daily, 1, 4) as 集計")
144            sql = sql.replace("<<group by>>", "group by 集計")
145        case "A":
146            sql = sql.replace("<<collection>>", "'合計' as 集計")
147            sql = sql.replace("<<group by>>", "")
148
149    if g.params.get("interval") is not None:
150        if g.params.get("interval") == 0:
151            sql = sql.replace("<<Calculation Formula>>", ":interval")
152        else:
153            sql = sql.replace(
154                "<<Calculation Formula>>",
155                "(row_number() over (order by total_count desc) - 1) / :interval"
156            )
157    if g.params.get("kind") is not None:
158        if g.params.get("kind") == "grandslam":
159            if g.cfg.undefined_word == 0:
160                sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 0)")
161            else:
162                sql = sql.replace("<<where_string>>", "and words.type = 0")
163        else:
164            match g.cfg.undefined_word:
165                case 1:
166                    sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 1)")
167                case 2:
168                    sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 2)")
169                case _:
170                    sql = sql.replace("<<where_string>>", "and (words.type = 1 or words.type = 2)")
171
172    # SQLコメント削除
173    sql = re.sub(r"^ *--\[.*$", "", sql, flags=re.MULTILINE)
174    sql = re.sub(r"\n+", "\n", sql, flags=re.MULTILINE)
175
176    return sql
177
178
179def named_query(query: str) -> str:
180    """クエリにパラメータをバインドして返す
181
182    Args:
183        query (str): SQL
184
185    Returns:
186        str: バインド済みSQL
187    """
188
189    for k, v in g.params.items():
190        if isinstance(v, datetime):
191            g.params[k] = v.strftime("%Y-%m-%d %H:%M:%S")
192
193    return re.sub(r":(\w+)", lambda m: repr(g.params.get(m.group(1), m.group(0))), query)