libs.commands.report.results_report

  1"""
  2libs/commands/report/results_report.py
  3"""
  4
  5import logging
  6import os
  7from datetime import datetime
  8from io import BytesIO
  9
 10import matplotlib.font_manager as fm
 11import matplotlib.pyplot as plt
 12import pandas as pd
 13from reportlab.lib import colors
 14from reportlab.lib.enums import TA_LEFT, TA_RIGHT
 15from reportlab.lib.pagesizes import A4, landscape
 16from reportlab.lib.styles import ParagraphStyle
 17from reportlab.lib.units import mm
 18from reportlab.pdfbase import pdfmetrics
 19from reportlab.pdfbase.ttfonts import TTFont
 20from reportlab.platypus import (Image, LongTable, PageBreak, Paragraph,
 21                                SimpleDocTemplate, Spacer, TableStyle)
 22
 23import libs.global_value as g
 24from libs.data import loader, lookup
 25from libs.utils import dbutil, formatter
 26
 27
 28def get_game_results() -> list:
 29    """月/年単位のゲーム結果集計
 30
 31    Returns:
 32        list: 集計結果のリスト
 33    """
 34
 35    g.params.update(starttime=g.params["starttime"].format("sql"))
 36    g.params.update(endtime=g.params["endtime"].format("sql"))
 37
 38    resultdb = dbutil.get_connection()
 39    rows = resultdb.execute(
 40        loader.query_modification(loader.load_query("report/personal_data.sql")),
 41        g.params,
 42    )
 43
 44    # --- データ収集
 45    results = [
 46        [
 47            "",
 48            "ゲーム数",
 49            "通算\nポイント",
 50            "平均\nポイント",
 51            "1位", "",
 52            "2位", "",
 53            "3位", "",
 54            "4位", "",
 55            "平均\n順位",
 56            "トビ", "",
 57        ]
 58    ]
 59
 60    for row in rows.fetchall():
 61        if row["ゲーム数"] == 0:
 62            break
 63
 64        results.append(
 65            [
 66                row["集計"],
 67                row["ゲーム数"],
 68                str(row["通算ポイント"]).replace("-", "▲") + "pt",
 69                str(row["平均ポイント"]).replace("-", "▲") + "pt",
 70                row["1位"], f"{row["1位率"]:.2f}%",
 71                row["2位"], f"{row["2位率"]:.2f}%",
 72                row["3位"], f"{row["3位率"]:.2f}%",
 73                row["4位"], f"{row["4位率"]:.2f}%",
 74                f"{row["平均順位"]:.2f}",
 75                row["トビ"], f"{row["トビ率"]:.2f}%",
 76            ]
 77        )
 78    logging.info("return record: %s", len(results))
 79    resultdb.close()
 80
 81    if len(results) == 1:  # ヘッダのみ
 82        return []
 83
 84    return results
 85
 86
 87def get_count_results(game_count: int) -> list:
 88    """指定間隔区切りのゲーム結果集計
 89
 90    Args:
 91        game_count (int): 区切るゲーム数
 92
 93    Returns:
 94        list: 集計結果のリスト
 95    """
 96
 97    g.params.update(interval=game_count)
 98    resultdb = dbutil.get_connection()
 99    rows = resultdb.execute(
100        loader.query_modification(loader.load_query("report/count_data.sql")),
101        g.params,
102    )
103
104    # --- データ収集
105    results = [
106        [
107            "開始",
108            "終了",
109            "ゲーム数",
110            "通算\nポイント",
111            "平均\nポイント",
112            "1位", "",
113            "2位", "",
114            "3位", "",
115            "4位", "",
116            "平均\n順位",
117            "トビ", "",
118        ]
119    ]
120
121    for row in rows.fetchall():
122        if row["ゲーム数"] == 0:
123            break
124
125        results.append(
126            [
127                row["開始"],
128                row["終了"],
129                row["ゲーム数"],
130                str(row["通算ポイント"]).replace("-", "▲") + "pt",
131                str(row["平均ポイント"]).replace("-", "▲") + "pt",
132                row["1位"], f"{row["1位率"]:.2f}%",
133                row["2位"], f"{row["2位率"]:.2f}%",
134                row["3位"], f"{row["3位率"]:.2f}%",
135                row["4位"], f"{row["4位率"]:.2f}%",
136                f"{row["平均順位"]:.2f}",
137                row["トビ"], f"{row["トビ率"]:.2f}%",
138            ]
139        )
140    logging.info("return record: %s", len(results))
141    resultdb.close()
142
143    if len(results) == 1:  # ヘッダのみ
144        return []
145
146    return results
147
148
149def get_count_moving(game_count: int) -> list:
150    """移動平均を取得する
151
152    Args:
153        game_count (int): 平滑化するゲーム数
154
155    Returns:
156        list: 集計結果のリスト
157    """
158
159    resultdb = dbutil.get_connection()
160    g.params.update(interval=game_count)
161    rows = resultdb.execute(
162        loader.query_modification(loader.load_query("report/count_moving.sql")),
163        g.params,
164    )
165
166    # --- データ収集
167    results = []
168    for row in rows.fetchall():
169        results.append(dict(row))
170
171    logging.info("return record: %s", len(results))
172    resultdb.close()
173
174    return results
175
176
177def graphing_mean_rank(df: pd.DataFrame, title: str, whole: bool = False) -> BytesIO:
178    """平均順位の折れ線グラフを生成
179
180    Args:
181        df (pd.DataFrame): 描写データ
182        title (str): グラフタイトル
183        whole (bool, optional): 集計種別. Defaults to False.
184            - True: 全体集計
185            - False: 指定範囲集計
186
187    Returns:
188        BytesIO: 画像データ
189    """
190
191    imgdata = BytesIO()
192
193    if whole:
194        df.plot(
195            kind="line",
196            figsize=(12, 5),
197            fontsize=14,
198        )
199        plt.legend(
200            title="開始 - 終了",
201            ncol=int(len(df.columns) / 5) + 1,
202        )
203    else:
204        df.plot(
205            kind="line",
206            y="rank_avg",
207            x="game_no",
208            legend=False,
209            figsize=(12, 5),
210            fontsize=14,
211        )
212
213    plt.title(title, fontsize=18)
214    plt.grid(axis="y")
215
216    # Y軸設定
217    plt.ylabel("平均順位", fontsize=14)
218    plt.yticks([4.0, 3.5, 3.0, 2.5, 2.0, 1.5, 1.0])
219    for ax in plt.gcf().get_axes():  # 逆向きにする
220        ax.invert_yaxis()
221
222    # X軸設定
223    plt.xlabel("ゲーム数", fontsize=14)
224
225    plt.savefig(imgdata, format="jpg", bbox_inches="tight")
226    plt.close()
227
228    return imgdata
229
230
231def graphing_total_points(df: pd.DataFrame, title: str, whole: bool = False) -> BytesIO:
232    """通算ポイント推移の折れ線グラフを生成
233
234    Args:
235        df (pd.DataFrame): 描写データ
236        title (str): グラフタイトル
237        whole (bool, optional): 集計種別. Defaults to False.
238            - True: 全体集計 / 移動平均付き
239            - False: 指定範囲集計
240    Returns:
241        BytesIO: 画像データ
242    """
243
244    imgdata = BytesIO()
245
246    if whole:
247        df.plot(
248            kind="line",
249            figsize=(12, 8),
250            fontsize=14,
251        )
252        plt.legend(
253            title="通算 （ 開始 - 終了 ）",
254            ncol=int(len(df.columns) / 5) + 1,
255        )
256    else:
257        point_sum = df.plot(
258            kind="line",
259            y="point_sum",
260            label="通算",
261            figsize=(12, 8),
262            fontsize=14,
263        )
264        if len(df) > 50:
265            point_sum = df["point_sum"].rolling(40).mean().plot(
266                kind="line", label="移動平均(40ゲーム)",
267                ax=point_sum,
268            )
269        if len(df) > 100:
270            point_sum = df["point_sum"].rolling(80).mean().plot(
271                kind="line", label="移動平均(80ゲーム)",
272                ax=point_sum,
273            )
274        plt.legend()
275
276    plt.title(title, fontsize=18)
277    plt.grid(axis="y")
278
279    # Y軸設定
280    plt.ylabel("ポイント", fontsize=14)
281    ylocs, ylabs = plt.yticks()
282    new_ylabs = [ylab.get_text().replace("−", "▲") for ylab in ylabs]
283    plt.yticks(list(ylocs[1:-1]), new_ylabs[1:-1])
284
285    # X軸設定
286    plt.xlabel("ゲーム数", fontsize=14)
287
288    plt.savefig(imgdata, format="jpg", bbox_inches="tight")
289    plt.close()
290
291    return imgdata
292
293
294def graphing_rank_distribution(df: pd.DataFrame, title: str) -> BytesIO:
295    """順位分布の棒グラフを生成
296
297    Args:
298        df (pd.DataFrame): 描写データ
299        title (str): グラフタイトル
300
301    Returns:
302        BytesIO: 画像データ
303    """
304
305    imgdata = BytesIO()
306
307    df.plot(
308        kind="bar",
309        stacked=True,
310        figsize=(12, 7),
311        fontsize=14,
312    )
313
314    plt.title(title, fontsize=18)
315    plt.legend(
316        bbox_to_anchor=(0.5, 0),
317        loc="lower center",
318        ncol=4,
319        fontsize=12,
320    )
321
322    # Y軸設定
323    plt.yticks([0, 25, 50, 75, 100])
324    plt.ylabel("（％）", fontsize=14)
325    for ax in plt.gcf().get_axes():  # グリッド線を背後にまわす
326        ax.set_axisbelow(True)
327        plt.grid(axis="y")
328
329    # X軸設定
330    if len(df) > 10:
331        plt.xticks(rotation=30, ha="right")
332    else:
333        plt.xticks(rotation=30)
334
335    plt.savefig(imgdata, format="jpg", bbox_inches="tight")
336    plt.close()
337
338    return imgdata
339
340
341def gen_pdf() -> tuple[str | bool, str | bool]:
342    """成績レポートを生成する
343
344    Returns:
345        tuple[str | bool, str | bool]:
346        - str: レポート対象メンバー名
347        - str: レポート保存パス
348    """
349
350    plt.close()
351
352    if not g.params.get("player_name"):  # レポート対象の指定なし
353        return (False, False)
354
355    # 対象メンバーの記録状況
356    target_info = lookup.db.member_info(g.params["player_name"])
357    logging.info(target_info)
358
359    if not target_info["game_count"] > 0:  # 記録なし
360        return (False, False)
361
362    # 書式設定
363    font_path = os.path.join(os.path.realpath(os.path.curdir), g.cfg.setting.font_file)
364    pdf_path = os.path.join(
365        g.cfg.setting.work_dir,
366        f"{g.params["filename"]}.pdf" if g.params.get("filename") else "results.pdf",
367    )
368    pdfmetrics.registerFont(TTFont("ReportFont", font_path))
369
370    doc = SimpleDocTemplate(
371        pdf_path,
372        pagesize=landscape(A4),
373        topMargin=10.0 * mm,
374        bottomMargin=10.0 * mm,
375        # leftMargin=1.5 * mm,
376        # rightMargin=1.5 * mm,
377    )
378
379    style: dict = {}
380    style["Title"] = ParagraphStyle(
381        name="Title", fontName="ReportFont", fontSize=24
382    )
383    style["Normal"] = ParagraphStyle(
384        name="Normal", fontName="ReportFont", fontSize=14
385    )
386    style["Left"] = ParagraphStyle(
387        name="Left", fontName="ReportFont", fontSize=14, alignment=TA_LEFT
388    )
389    style["Right"] = ParagraphStyle(
390        name="Right", fontName="ReportFont", fontSize=14, alignment=TA_RIGHT
391    )
392
393    plt.rcParams.update(plt.rcParamsDefault)
394    font_prop = fm.FontProperties(fname=font_path)
395    plt.rcParams["font.family"] = font_prop.get_name()
396    fm.fontManager.addfont(font_path)
397
398    # レポート作成
399    elements: list = []
400    elements.extend(cover_page(style, target_info))  # 表紙
401    elements.extend(entire_aggregate(style))  # 全期間
402    elements.extend(periodic_aggregation(style))  # 期間集計
403    elements.extend(sectional_aggregate(style, target_info))  # 区間集計
404
405    doc.build(elements)
406    logging.notice("report generation: %s", g.params["player_name"])  # type: ignore
407
408    return (g.params["player_name"], pdf_path)
409
410
411def cover_page(style: dict, target_info: dict) -> list:
412    """表紙生成
413
414    Args:
415        style (dict): レイアウトスタイル
416        target_info (dict): プレイヤー情報
417
418    Returns:
419        list: 生成内容
420    """
421
422    elements: list = []
423
424    first_game = datetime.fromtimestamp(  # 最初のゲーム日時
425        float(target_info["first_game"])
426    )
427    last_game = datetime.fromtimestamp(  # 最後のゲーム日時
428        float(target_info["last_game"])
429    )
430
431    if g.params.get("anonymous"):
432        mapping_dict = formatter.anonymous_mapping([g.params["player_name"]])
433        target_player = next(iter(mapping_dict.values()))
434    else:
435        target_player = g.params["player_name"]
436
437    # 表紙
438    elements.append(Spacer(1, 40 * mm))
439    elements.append(Paragraph(f"成績レポート：{target_player}", style["Title"]))
440    elements.append(Spacer(1, 10 * mm))
441    elements.append(
442        Paragraph(
443            f"集計期間：{first_game.strftime("%Y-%m-%d %H:%M")} - {last_game.strftime("%Y-%m-%d %H:%M")}",
444            style["Normal"]
445        )
446    )
447    elements.append(Spacer(1, 100 * mm))
448    elements.append(
449        Paragraph(
450            f"作成日：{datetime.now().strftime('%Y-%m-%d')}",
451            style["Right"]
452        )
453    )
454    elements.append(PageBreak())
455
456    return elements
457
458
459def entire_aggregate(style: dict) -> list:
460    """全期間
461
462    Args:
463        style (dict): レイアウトスタイル
464
465    Returns:
466        list: 生成内容
467    """
468
469    elements: list = []
470
471    elements.append(Paragraph("全期間", style["Left"]))
472    elements.append(Spacer(1, 5 * mm))
473    data: list = []
474    g.cfg.aggregate_unit = "A"
475    tmp_data = get_game_results()
476
477    if not tmp_data:
478        return []
479
480    for _, val in enumerate(tmp_data):  # ゲーム数を除外
481        data.append(val[1:])
482    tt = LongTable(data, repeatRows=1)
483    tt.setStyle(TableStyle([
484        ("FONT", (0, 0), (-1, -1), "ReportFont", 10),
485        ("GRID", (0, 0), (-1, -1), 0.25, colors.black),
486        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
487        ("ALIGN", (0, 0), (-1, -1), "CENTER"),
488        ('SPAN', (3, 0), (4, 0)),
489        ('SPAN', (5, 0), (6, 0)),
490        ('SPAN', (7, 0), (8, 0)),
491        ('SPAN', (9, 0), (10, 0)),
492        ('SPAN', (12, 0), (13, 0)),
493        # ヘッダ行
494        ("BACKGROUND", (0, 0), (-1, 0), colors.navy),
495        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
496    ]))
497    elements.append(tt)
498
499    # 順位分布
500    imgdata = BytesIO()
501    gdata = pd.DataFrame(
502        {
503            "順位分布": [
504                float(str(data[1][4]).replace("%", "")),
505                float(str(data[1][6]).replace("%", "")),
506                float(str(data[1][8]).replace("%", "")),
507                float(str(data[1][10]).replace("%", "")),
508            ],
509        }, index=["1位率", "2位率", "3位率", "4位率"]
510    )
511    gdata.plot(
512        kind="pie",
513        y="順位分布",
514        labels=None,
515        figsize=(6, 6),
516        fontsize=14,
517        autopct="%.2f%%",
518        wedgeprops={"linewidth": 1, "edgecolor": "white"},
519    )
520    plt.title("順位分布 （ 全期間 ）", fontsize=18)
521    plt.ylabel("")
522    plt.legend(
523        list(gdata.index),
524        bbox_to_anchor=(0.5, -0.1),
525        loc="lower center",
526        ncol=4,
527        fontsize=12
528    )
529    plt.savefig(imgdata, format="jpg", bbox_inches="tight")
530
531    elements.append(Spacer(1, 5 * mm))
532    elements.append(Image(imgdata, width=600 * 0.5, height=600 * 0.5))
533    plt.close()
534
535    data = get_count_moving(0)
536    df = pd.DataFrame(data)
537    df["playtime"] = pd.to_datetime(df["playtime"])
538
539    # 通算ポイント推移
540    imgdata = graphing_total_points(df, "通算ポイント推移 （ 全期間 ）", False)
541    elements.append(Image(imgdata, width=1200 * 0.5, height=800 * 0.5))
542
543    # 平均順位
544    imgdata = graphing_mean_rank(df, "平均順位推移 （ 全期間 ）", False)
545    elements.append(Image(imgdata, width=1200 * 0.5, height=500 * 0.5))
546
547    elements.append(PageBreak())
548
549    return elements
550
551
552def periodic_aggregation(style: dict) -> list:
553    """期間集計
554
555    Args:
556        style (dict): レイアウトスタイル
557
558    Returns:
559        list: 生成内容
560    """
561
562    elements: list = []
563
564    pattern: list[tuple[str, str, str]] = [
565        # 表タイトル, グラフタイトル, フラグ
566        ("月別集計", "順位分布（月別）", "M"),
567        ("年別集計", "順位分布（年別）", "Y"),
568    ]
569
570    for table_title, graph_title, flag in pattern:
571        elements.append(Paragraph(table_title, style["Left"]))
572        elements.append(Spacer(1, 5 * mm))
573
574        data: list = []
575        g.cfg.aggregate_unit = flag
576        tmp_data = get_game_results()
577
578        if not tmp_data:
579            return []
580
581        for _, val in enumerate(tmp_data):  # 日時を除外
582            data.append(val[:15])
583
584        tt = LongTable(data, repeatRows=1)
585        ts = TableStyle([
586            ("FONT", (0, 0), (-1, -1), "ReportFont", 10),
587            ("GRID", (0, 0), (-1, -1), 0.25, colors.black),
588            ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
589            ("ALIGN", (0, 0), (-1, -1), "CENTER"),
590            ('SPAN', (4, 0), (5, 0)),
591            ('SPAN', (6, 0), (7, 0)),
592            ('SPAN', (8, 0), (9, 0)),
593            ('SPAN', (10, 0), (11, 0)),
594            ('SPAN', (13, 0), (14, 0)),
595            # ヘッダ行
596            ("BACKGROUND", (0, 0), (-1, 0), colors.navy),
597            ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
598        ])
599
600        if len(data) > 4:
601            for i in range(len(data) - 2):
602                if i % 2 == 0:
603                    ts.add("BACKGROUND", (0, i + 2), (-1, i + 2), colors.lightgrey)
604        tt.setStyle(ts)
605        elements.append(tt)
606        elements.append(Spacer(1, 10 * mm))
607
608        # 順位分布
609        df = pd.DataFrame(
610            {
611                "1位率": [float(str(data[x + 1][5]).replace("%", "")) for x in range(len(data) - 1)],
612                "2位率": [float(str(data[x + 1][7]).replace("%", "")) for x in range(len(data) - 1)],
613                "3位率": [float(str(data[x + 1][9]).replace("%", "")) for x in range(len(data) - 1)],
614                "4位率": [float(str(data[x + 1][11]).replace("%", "")) for x in range(len(data) - 1)],
615            }, index=[data[x + 1][0] for x in range(len(data) - 1)]
616        )
617
618        imgdata = graphing_rank_distribution(df, graph_title)
619        elements.append(Spacer(1, 5 * mm))
620        elements.append(Image(imgdata, width=1200 * 0.5, height=700 * 0.5))
621
622        elements.append(PageBreak())
623
624    return elements
625
626
627def sectional_aggregate(style: dict, target_info: dict) -> list:
628    """区間集計
629
630    Args:
631        style (dict): レイアウトスタイル
632        target_info (dict): プレイヤー情報
633
634    Returns:
635        list: 生成内容
636    """
637
638    elements: list = []
639
640    pattern: list[tuple[int, int, str]] = [
641        # 区切り回数, 閾値, タイトル
642        (80, 100, "短期"),
643        (200, 240, "中期"),
644        (400, 500, "長期"),
645    ]
646
647    for count, threshold, title in pattern:
648        if target_info["game_count"] > threshold:
649            # テーブル
650            elements.append(Paragraph(f"区間集計 （ {title} ）", style["Left"]))
651            elements.append(Spacer(1, 5 * mm))
652            data = get_count_results(count)
653
654            if not data:
655                return []
656
657            tt = LongTable(data, repeatRows=1)
658            ts = TableStyle([
659                ("FONT", (0, 0), (-1, -1), "ReportFont", 10),
660                ("GRID", (0, 0), (-1, -1), 0.25, colors.black),
661                ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
662                ("ALIGN", (0, 0), (-1, -1), "CENTER"),
663                ('SPAN', (5, 0), (6, 0)),
664                ('SPAN', (7, 0), (8, 0)),
665                ('SPAN', (9, 0), (10, 0)),
666                ('SPAN', (11, 0), (12, 0)),
667                ('SPAN', (14, 0), (15, 0)),
668                # ヘッダ行
669                ("BACKGROUND", (0, 0), (-1, 0), colors.navy),
670                ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
671            ])
672            if len(data) > 4:
673                for i in range(len(data) - 2):
674                    if i % 2 == 0:
675                        ts.add("BACKGROUND", (0, i + 2), (-1, i + 2), colors.lightgrey)
676            tt.setStyle(ts)
677            elements.append(tt)
678
679            # 順位分布
680            df = pd.DataFrame(
681                {
682                    "1位率": [float(str(data[x + 1][6]).replace("%", "")) for x in range(len(data) - 1)],
683                    "2位率": [float(str(data[x + 1][8]).replace("%", "")) for x in range(len(data) - 1)],
684                    "3位率": [float(str(data[x + 1][10]).replace("%", "")) for x in range(len(data) - 1)],
685                    "4位率": [float(str(data[x + 1][12]).replace("%", "")) for x in range(len(data) - 1)],
686                }, index=[f"{str(data[x + 1][0])} - {str(data[x + 1][1])}" for x in range(len(data) - 1)]
687            )
688
689            imgdata = graphing_rank_distribution(df, f"順位分布 （ 区間 {title} ）")
690            elements.append(Spacer(1, 5 * mm))
691            elements.append(Image(imgdata, width=1200 * 0.5, height=800 * 0.5))
692
693            # 通算ポイント推移
694            data = get_count_moving(count)
695            tmp_df = pd.DataFrame(data)
696            df = pd.DataFrame()
697            for i in sorted(tmp_df["interval"].unique().tolist()):
698                list_data = tmp_df[tmp_df.interval == i]["point_sum"].to_list()
699                game_count = tmp_df[tmp_df.interval == i]["total_count"].to_list()
700                df[f"{min(game_count)} - {max(game_count)}"] = [None] * (count - len(list_data)) + list_data
701
702            imgdata = graphing_total_points(df, f"通算ポイント推移（区間 {title}）", True)
703            elements.append(Image(imgdata, width=1200 * 0.5, height=800 * 0.5))
704
705            # 平均順位
706            df = pd.DataFrame()
707            for i in sorted(tmp_df["interval"].unique().tolist()):
708                list_data = tmp_df[tmp_df.interval == i]["rank_avg"].to_list()
709                game_count = tmp_df[tmp_df.interval == i]["total_count"].to_list()
710                df[f"{min(game_count)} - {max(game_count)}"] = [None] * (count - len(list_data)) + list_data
711
712            imgdata = graphing_mean_rank(df, f"平均順位推移（区間 {title}）", True)
713            elements.append(Image(imgdata, width=1200 * 0.5, height=500 * 0.5))
714
715            elements.append(PageBreak())
716
717    return elements