# 關於google chat推播問題

## 問題
alert_consecutive_winning_players_motivation 這支程式一直沒有出現alert到google chat，於是我在正式站跳板機上也寫了一個程式alert到line，最後再利用alert到line的message到mongo找出對應的order以下主要分成幾個部分：
1. 測試webhook
2. airflow上的code
3. 我在正式站上run的code
4. 在line上推播結果
5. 正式站mongodb之order

## 測試webhook
我寫了一個小程式測試webhook推播message到google chat 群組，確認webhook沒問題
webhook link: https://chat.googleapis.com/v1/spaces/AAAAgkwYVl4/messages?key=AIzaSyDdI0hCZtE6vySjMm-WEfRq3CPzqKqqsHI&token=pA7lOVp7jsHsAjBSKfJ7tGK_248Kh3Ye3nRnn8PmWqI


## airflow上的code

import pendulum
from pendulum.datetime import DateTime as pendulum_datetime
from datetime import timedelta
import logging

from utils.connection.mysql import mysql_cursor
from airflow.providers.mongo.hooks.mongo import MongoHook
from tracker.crud import agent
from tracker.crud.mongo import order
from utils.tools import google_chat_message

from airflow.models import Variable
from airflow.decorators import dag, task

logger = logging.getLogger("airflow.task")

default_args = {
    "depends_on_past": False,
    "email": ["[e-mail]"],
    "email_on_failure": True,
    "email_on_retry": False,
    "retries": 1,
    "retry_delay": timedelta(seconds=10),
}


@dag(
    start_date=pendulum.datetime(2023, 2, 20),
    schedule_interval="*/10 * * * *",
    default_args=default_args,
    catchup=False,
    max_active_tasks=4,
    tags=["PRO", "cq9", "VENUS", "168", "tracker"],
)
def alert_consecutive_winning_players_motivation():
    @task
    def alert_consecutive_winning_player_motivation(
        data_interval_end: pendulum_datetime = pendulum.now(), data_interval: int = 10
    ):
        _CFG = Variable.get("TRACKER_ALERT", deserialize_json=True)
        _NEW_CFG = _CFG.get("ALERT_CONSECUTIVE_WINNING", {})
        CONSECUTIVE_TIMES = _NEW_CFG.get("CONSECUTIVE_TIMES")
        GOOGLE_CHAT = _NEW_CFG.get("GOOGLE_CHAT")

        end_time = data_interval_end

        start_time = end_time.add(minutes=data_interval)
        if end_time.hour < 16:
            start_time_cal_daily_win = end_time.add(
                hours=-end_time.hour - 8, minutes=-end_time.minute
            )
        else:
            start_time_cal_daily_win = pendulum.datetime(
                year=end_time.year, month=end_time.month, day=end_time.day, hour=16
            )

        logger.info(
            f"Alert tracking consecutive winning player from {start_time} to {end_time}"
        )

        with (
            mysql_cursor(mysql_conn_id="mysql_slave_read") as cursor,
            MongoHook(conn_id="mongo_read") as mongo_hook,
        ):
            parent_list = {}
            for ssid, _parent, _owner, _ in agent.get_info(cursor):
                parent_list[ssid] = {"parent": _parent, "owner": _owner}

            mongo_cnx = mongo_hook.get_conn()
            infos = order.get_motivation_order(mongo_cnx, start_time, end_time)
            players_consecutive_win_count = {}

            for info in infos:
                wins = info.get("wins")
                account = info.get("account")
                playerid = info.get("playerid")
                parentid = info.get("parentid", " ")
                tableid = info.get("tableid")
                if parent_list.get(parentid):

                    if playerid not in players_consecutive_win_count:
                        players_consecutive_win_count[playerid] = {
                            "account": account,
                            "count_win": 0,
                            "tableids": [],
                        }

                    if wins > 0:
                        players_consecutive_win_count[playerid][
                            "count_win"
                        ] += 1
                        if tableid not in players_consecutive_win_count[playerid]["tableids"]:
                            players_consecutive_win_count[playerid][
                                "tableids"
                            ].append(tableid)
                    else:
                        if (
                            players_consecutive_win_count[playerid][
                                "count_win"
                            ]
                            >= CONSECUTIVE_TIMES
                        ):
                            count_win = players_consecutive_win_count[
                                playerid
                            ]["count_win"]
                            tableids = players_consecutive_win_count[
                                playerid
                            ]["tableids"]

                            msg = f"type : player has more than {CONSECUTIVE_TIMES} consecutive wins:\n"
                            msg += f"owner : {parent_list[parentid].get('owner')}\n"
                            msg += f"parent : {parent_list[parentid].get('parent')}\n"
                            msg += f"account : {account}\n"
                            msg += f"連贏超過五局之tableid : {', '.join(tableids)}\n"
                            msg += f"連贏局數 : {count_win}\n"

                            (
                                daily_bet,
                                daily_win,
                                daily_income,
                            ) = order.get_player_daily_bet_win(
                                mongo_cnx, start_time_cal_daily_win, end_time, playerid
                            )
                            msg += f"總投注額 : {daily_bet:,.0f}\n"
                            msg += f"總輸贏 : {daily_win:,.0f}\n"
                            msg += f"玩家損益金額 :{daily_income:,.0f}"
                            google_chat_message(GOOGLE_CHAT, msg)

                        players_consecutive_win_count[playerid][
                            "count_win"
                        ] = 0
                        players_consecutive_win_count[playerid][
                            "tableids"
                        ] = []

            for playerid, _value in players_consecutive_win_count.items():
                account = _value.get("account")
                count_win = _value.get("count_win")
                if count_win >= CONSECUTIVE_TIMES:
                    tableids = _value["tableids"]
                    msg = f"type : player has more than {CONSECUTIVE_TIMES} consecutive wins:\n"
                    msg += f"owner : {parent_list[parentid].get('owner')}\n"
                    msg += f"parent : {parent_list[parentid].get('parent')}\n"
                    msg += f"account : {account}\n"
                    msg += f"連贏超過五局之tableid : {', '.join(tableids)}\n"
                    msg += f"連贏局數 : {count_win}\n"

                    (
                        daily_bet,
                        daily_win,
                        daily_income,
                    ) = order.get_player_daily_bet_win(
                        mongo_cnx, start_time, end_time, playerid
                    )
                    msg += f"總投注額 : {daily_bet:,.0f}\n"
                    msg += f"總輸贏 : {daily_win:,.0f}\n"
                    msg += f"玩家損益金額 :{daily_income:,.0f}"
                    google_chat_message(GOOGLE_CHAT, msg)

    alert_consecutive_winning_player_motivation()


alert_consecutive_winning_players_motivation_dag = (
    alert_consecutive_winning_players_motivation()
)

## 我在正式站上run的code

from db_connector import connect_mongo, connect_mysql
from func import _logger, connect_cfg
import pendulum
import time
from bson.objectid import ObjectId
import requests

def post_data(message, token):
    try:
        url = "https://notify-api.line.me/api/notify"
        headers = {"Authorization": f"Bearer {token}"}
        payload = {"message": message}
        response = requests.request("POST", url, headers=headers, data=payload)
        if response.status_code == 200:
            print(f"Success -> {response.text}")
    except Exception as _:
        print(_)


def get_info(cursor):
    stmt = "SELECT PLP.ssid, CAST(PLP.account AS CHAR), CAST(PLO.account AS CHAR), FR.rate "
    stmt += "FROM cypress.parent_list PLP "
    stmt += "JOIN cypress.parent_list PLO ON PLP.owner=PLO.ssid AND PLO.istestss = 0 "
    stmt += "JOIN cypress.fx_rate FR ON PLP.currency = FR.short_name "
    stmt += "WHERE PLP.istestss = 0 "

    cursor.execute(stmt)
    result = list(cursor)
    logger.info("Get agent info SUCCESS")

    return result


def get_motivation_order(
    mongo_cnx,
    start_time,
    end_time,
):
    """
    Get motivation order collect by createtime
    """
    start_id = ObjectId.from_datetime(start_time)
    end_id = ObjectId.from_datetime(end_time)

    query = {
        "_id": {"$gte": start_id, "$lt": end_id},
        "gamecode": "GINKGO01",
    }
    field = [
        "ownerid",
        "parentid",
        "account",
        "playerid",
        "tableid",
        "roundid",
        "currency",
        "bets",
        "wins",
    ]

    _result = list(mongo_cnx.order.order.find(query, field))
    logger.info(f"Get GINKGO01 order from {start_time} to {end_time} SUCCESS!")

    return _result


def get_player_daily_bet_win(
    mongo_cnx,
    start_time,
    end_time,
    playerid,
):
    """
    Get motivation player daily bet and daily win
    """
    start_id = ObjectId.from_datetime(start_time)
    end_id = ObjectId.from_datetime(end_time)

    pipeline = [
        {
            "$match": {
                "_id": {"$gte": start_id, "$lt": end_id},
                "gamecode": "GINKGO01",
                "playerid": playerid,
            }
        },
        {
            "$group": {
                "_id": "$playerid",
                "daily_bet": {"$sum": "$bets"},
                "daily_win": {"$sum": "$wins"},
            }
        },
    ]
 
    result = list(mongo_cnx.order.order.aggregate(pipeline))
    if result:
        result = result[0]
    else:
        result = {"daily_bet": 0, "daily_win": 0 }
    logger.info(result)
    logger.info(
        f"Get {playerid} GINKGO01 daily bets and wins from {start_time} to {end_time} SUCCESS!"
    )

    return result.get("daily_bet"), result.get("daily_win"), result.get("daily_win")


logger = _logger.get_logger("./log", "test_check_alert.log")

maria_cfg, mongo_cfg = connect_cfg.extract_db_cfg(
    maria_name="maria_read_pro", mongo_name="mongo_read_pro"
)

client = connect_mongo.MongoConn(mongo_cfg, logger)
db = client.setup()

connection = connect_mysql.MySQLConn(maria_cfg, logger, dict_mode=False)
cursor = connection.setup()

CONSECUTIVE_TIMES = 5
TOKEN = "caVXAKPVaahnOiOhNkSTTcVJNbT9rcIPFrHmcVlkH2Z"

while True:
    end_time = pendulum.now(tz="UTC")
    end_time = end_time.add(
        seconds = -end_time.second, microseconds=-end_time.microsecond
    )

    if end_time.minute % 10 == 0:
        print(f"{pendulum.now('UTC')} : running")
        start_time = end_time.add(minutes=-10)

        start_id = ObjectId.from_datetime(start_time)
        end_id = ObjectId.from_datetime(end_time)

        start_time_cal_daily_win = (
            end_time.add(hours=-end_time.hour - 8, minutes=-end_time.minute)
            if end_time.hour < 16
            else pendulum.datetime(
                year=end_time.year, month=end_time.month, day=end_time.day, hour=16
            )
        )

        logger.info(f"Alert tracking consecutive winning player from {start_time} to {end_time}")

        parent_list = {}
        for ssid, _parent, _owner, _ in get_info(cursor):
            parent_list[ssid] = {"parent": _parent, "owner": _owner}

        infos = get_motivation_order(db, start_time, end_time)
        players_consecutive_win_count = {}
        for info in infos:
            wins = info.get("wins")
            account = info.get("account")
            playerid = info.get("playerid")
            parentid = info.get("parentid", " ")
            tableid = info.get("tableid")
            roundid = info.get("roundid")
            if parent_list.get(parentid):
                if playerid not in players_consecutive_win_count:
                    players_consecutive_win_count[playerid] = {
                        "account": account,
                        "count_win": 0,
                        "tableids": [],
                        "roundids": [],
                    }

                if wins > 0:
                    players_consecutive_win_count[playerid][
                        "count_win"
                    ] += 1
                    if tableid not in players_consecutive_win_count[playerid]["tableids"]:
                        players_consecutive_win_count[playerid][
                            "tableids"
                        ].append(tableid)
                    players_consecutive_win_count[playerid]["roundids"].append(roundid)
                else:
                    if (
                        players_consecutive_win_count[playerid][
                            "count_win"
                        ]
                        >= CONSECUTIVE_TIMES
                    ):
                        count_win = players_consecutive_win_count[
                            playerid
                        ]["count_win"]
                        tableids = players_consecutive_win_count[
                            playerid
                        ]["tableids"]
                        roundids = players_consecutive_win_count[playerid]["roundids"]

                        msg = f"\ntype : player has more than {CONSECUTIVE_TIMES} consecutive wins:\n"
                        msg += f"owner : {parent_list[parentid].get('owner')}\n"
                        msg += f"parent : {parent_list[parentid].get('parent')}\n"
                        msg += f"account : {account}\n"
                        msg += f"playerid : {playerid}\n"
                        msg += f"連贏超過五局之tableid : {', '.join(tableids)}\n"
                        msg += f"連贏超過五局之roundid : {', '.join(roundids)}\n"
                        msg += f"連贏局數 : {count_win}\n"
                        print(playerid)
                        (
                            daily_bet,
                            daily_win,
                            daily_income,
                        ) = get_player_daily_bet_win(
                            db, start_time_cal_daily_win, end_time, playerid
                        )
                        msg += f"總投注額 : {daily_bet:,.0f}\n"
                        msg += f"總輸贏 : {daily_win:,.0f}\n"
                        msg += f"玩家損益金額 :{daily_income:,.0f}"
                        
                        post_data(msg, TOKEN)
                    
                    players_consecutive_win_count[playerid][
                        "count_win"
                    ] = 0
                    players_consecutive_win_count[playerid][
                        "tableids"
                    ] = []
                    players_consecutive_win_count[playerid][
                        "roundids"
                    ] = []

        for playerid, _value in players_consecutive_win_count.items():
            account = _value.get("account")
            count_win = _value.get("count_win")
            if count_win >= CONSECUTIVE_TIMES:
                tableids = _value["tableids"]
                roundids = players_consecutive_win_count[playerid]["roundids"]
                msg = f"\ntype : player has more than {CONSECUTIVE_TIMES} consecutive wins:\n"
                msg += f"owner : {parent_list[parentid].get('owner')}\n"
                msg += f"parent : {parent_list[parentid].get('parent')}\n"
                msg += f"account : {account}\n"
                msg += f"playerid : {playerid}\n"
                msg += f"連贏超過五局之tableid : {', '.join(tableids)}\n"
                msg += f"連贏超過五局之roundid : {', '.join(roundids)}\n"
                msg += f"連贏局數 : {count_win}\n"

                (
                    daily_bet,
                    daily_win,
                    daily_income,
                ) = get_player_daily_bet_win(
                    db, start_time, end_time, playerid
                )
                msg += f"總投注額 : {daily_bet:,.0f}\n"
                msg += f"總輸贏 : {daily_win:,.0f}\n"
                msg += f"玩家損益金額 :{daily_income:,.0f}"

                post_data(msg, TOKEN)


    print(f"{pendulum.now('UTC')} : sleeping")
    time.sleep(59.8)

## 在line上推播結果

## 正式站mongodb之order
#### query

db.order.find({'playerid' : "646a218e1e07ff0c3e9c3e5e", 'gamecode' : "GINKGO01", "createtime": {"$gte" : ISODate('2023-06-12T12:00:00.000+08:00',"$lt": ISODate('2023-06-12T12:10:00.000+08:00'))}})
   .projection(['playerid', 'roundid', 'bets', 'wins', "createtime"])
   .sort({'createtime':-1})
   .limit(100)

#### result
這邊只擷取上圖line 推播結果之roundid，結果顯示確實該玩家連續贏錢五局（/* 19 ~ /* 24）之後於/* 18結束連續贏錢，以上在airflow和正式站跳板機執行的程式理論上都要同時alert(airflow alert至google chat，跳板機alert至line)，但至今line已經出現很多alert但google chat未見任何一個alert

/* 18 createdAt:6/12/2023, 4:07:07 AM*/
{
	"_id" : ObjectId("648699eb013c2f065be1f611"),
	"playerid" : "646a218e1e07ff0c3e9c3e5e",
	"roundid" : "GINKGO230612000000102",
	"wins" : -100,
	"bets" : 100,
	"createtime" : ISODate("2023-06-12T04:07:07.534+00:00")
},

/* 19 createdAt:6/12/2023, 4:05:57 AM*/
{
	"_id" : ObjectId("648699a59e675718bcfb35f7"),
	"playerid" : "646a218e1e07ff0c3e9c3e5e",
	"roundid" : "GINKGO230612000000084",
	"wins" : 45,
	"bets" : 150,
	"createtime" : ISODate("2023-06-12T04:05:57.545+00:00")
},

/* 20 createdAt:6/12/2023, 4:02:58 AM*/
{
	"_id" : ObjectId("648698f29c4833aabf7e6e92"),
	"playerid" : "646a218e1e07ff0c3e9c3e5e",
	"roundid" : "GINKGO230612000000038",
	"wins" : 617.5,
	"bets" : 650,
	"createtime" : ISODate("2023-06-12T04:02:57.913+00:00")
},

/* 21 createdAt:6/12/2023, 4:02:27 AM*/
{
	"_id" : ObjectId("648698d354e2f60c8d4ab132"),
	"playerid" : "646a218e1e07ff0c3e9c3e5e",
	"roundid" : "GINKGO230612000000031",
	"wins" : 95,
	"bets" : 100,
	"createtime" : ISODate("2023-06-12T04:02:27.574+00:00")
},

/* 22 createdAt:6/12/2023, 4:01:54 AM*/
{
	"_id" : ObjectId("648698b221593112e5030bb6"),
	"playerid" : "646a218e1e07ff0c3e9c3e5e",
	"roundid" : "GINKGO230612000000025",
	"wins" : 150,
	"bets" : 150,
	"createtime" : ISODate("2023-06-12T04:01:53.974+00:00")
},

/* 23 createdAt:6/12/2023, 4:01:28 AM*/
{
	"_id" : ObjectId("648698984b58f337aa0b6768"),
	"playerid" : "646a218e1e07ff0c3e9c3e5e",
	"roundid" : "GINKGO230612000000018",
	"wins" : 1550,
	"bets" : 1550,
	"createtime" : ISODate("2023-06-12T04:01:28.784+00:00")
},

/* 24 createdAt:6/12/2023, 4:00:33 AM*/
{
	"_id" : ObjectId("648698614767d964f34d60df"),
	"playerid" : "646a218e1e07ff0c3e9c3e5e",
	"roundid" : "GINKGO230612000000003",
	"wins" : 47.5,
	"bets" : 50,
	"createtime" : ISODate("2023-06-12T04:00:32.927+00:00")
}