<?php
require_once "../Parameters.php";
require_once "../auth/func.php";

/*
    門市貨幣低水位判斷
*/



//設定最大連線間隔時間 單位秒
$n_max_connect_out_time = 2700;

$dsn = sprintf("sqlsrv:Server=%s; Database=%s;", $DataSource, $DataBase_Trend);
$pdo_mssql = new PDO($dsn, $UserID, $Password);

$n_alarm_code = 2;
//查詢錯誤碼資訊
$sql = sprintf(
    "SELECT 
            _secl.error_name
            ,_secl.error_handle
            ,_secl.stop_type
    FROM 	
        [%s].[dbo].[store_error_code_list] AS _secl 
    WHERE
        _secl.error_code = :error_code
    ",
    $DataBase_Alarm
);
//準備執行sql
$sth = $pdo_mssql->prepare($sql);
$sth->execute([
    "error_code" => $n_alarm_code
]);
$row = $sth->fetch(PDO::FETCH_ASSOC);
$s_alarm_name = $row["error_name"];
$s_alarm_handle = $row["error_handle"];
//判斷是否停機 0:不停機 1:停機 ，根據停機與否來更新cashier_state
$n_stop_type = $row["stop_type"];
$n_cashier_state = $n_stop_type == 1 ? 2 : 1;

//撈取各門市當前低水位警戒判斷值，並計算出各部門各面額最低數量限制為多少
$sql_store_set = sprintf(
    "SELECT
        _c.cashier_id,
        _c.store_id,
        _s.store_name,
        _s.cash_warning_level,
        _s.cash_stop_level,
        _s.coin_low_warning_water_level,
        _s.coin_low_stop_water_level,
        _s.petty_cash_500,
        _s.petty_cash_100,
        _s.petty_cash_50,
        _s.petty_cash_10,
        _s.petty_cash_5,
        _s.petty_cash_1,
        _s.store_state
    FROM
        [%s].[dbo].[store] AS _s
        LEFT JOIN [%s].[dbo].[cashier] AS _c ON _c.store_id = _s.store_id
    WHERE
        _s.store_state = 1",
    $DataBase_Trend,
    $DataBase_Trend
);
//準備執行sql
$sth = $pdo_mssql->prepare($sql_store_set);
$sth->execute([]);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
$a_store_low_level = [];
$a_store_info_list = [];
foreach ($rows as $k => $v) {
    //門市資訊對應陣列
    $a_store_info_list[$v["store_id"]] = [
        "cashier_id" => $v["cashier_id"],
        "store_name" => $v["store_name"]
    ];
    //低水位(警告)
    $a_store_low_level[$v["store_id"]]["warning"] = [
        "500" => (int)($v["petty_cash_500"] * $v["cash_warning_level"] / 100),
        "100" => (int)($v["petty_cash_100"] * $v["cash_warning_level"] / 100),
        "50" => (int)($v["petty_cash_50"] * $v["coin_low_warning_water_level"] / 100),
        "10" => (int)($v["petty_cash_10"] * $v["coin_low_warning_water_level"] / 100),
        "5" => (int)($v["petty_cash_5"] * $v["coin_low_warning_water_level"] / 100),
        "1" => (int)($v["petty_cash_1"] * $v["coin_low_warning_water_level"] / 100),
    ];
    //低水位(停止)
    $a_store_low_level[$v["store_id"]]["stop"] = [
        "500" => (int)($v["petty_cash_500"] * $v["cash_stop_level"] / 100),
        "100" => (int)($v["petty_cash_100"] * $v["cash_stop_level"] / 100),
        "50" => (int)($v["petty_cash_50"] * $v["coin_low_stop_water_level"] / 100),
        "10" => (int)($v["petty_cash_10"] * $v["coin_low_stop_water_level"] / 100),
        "5" => (int)($v["petty_cash_5"] * $v["coin_low_stop_water_level"] / 100),
        "1" => (int)($v["petty_cash_1"] * $v["coin_low_stop_water_level"] / 100),
    ];
}
//統計門市當前各部位數量
$sql_last_currency_data = sprintf(
    "SELECT
            _s.store_id,
            _c.cashier_id,
            v_sls.statistics_at,
            v_sls.last_cycle_box_500,
            v_sls.last_cycle_box_100,
            v_sls.last_cycle_box_50,
            v_sls.last_cycle_box_10,
            v_sls.last_cycle_box_5,
            v_sls.last_cycle_box_1,
            v_sls.last_safe_box_1000,
            v_sls.last_safe_box_500,
            v_sls.last_safe_box_100,
            v_sls.last_safe_box_50,
            v_sls.last_safe_box_10,
            v_sls.last_safe_box_5,
            v_sls.last_safe_box_1,
            v_sls.sub_total_1000_in,
            v_sls.sub_total_500_in,
            v_sls.sub_total_100_in,
            v_sls.sub_total_500_out,
            v_sls.sub_total_100_out,
            v_sts.cash_1000_in,
            v_sts.cash_500_in,
            v_sts.cash_100_in,
            v_sts.cash_50_in,
            v_sts.cash_10_in,
            v_sts.cash_5_in,
            v_sts.cash_1_in,
            v_sts.cash_500_out,
            v_sts.cash_100_out,
            v_sts.cash_50_out,
            v_sts.cash_10_out,
            v_sts.cash_5_out,
            v_sts.cash_1_out,
            v_sts.safe_box_1000,
            v_sts.safe_box_500,
            v_sts.safe_box_100,
            v_sts.safe_box_50,
            v_sts.safe_box_10,
            v_sts.safe_box_5,
            v_sts.safe_box_1,
            v_sts.cycle_box_500,
            v_sts.cycle_box_100,
            v_sts.cycle_box_50,
            v_sts.cycle_box_10,
            v_sts.cycle_box_5,
            v_sts.cycle_box_1,
            v_sts.sub_box_1000_in,
            v_sts.sub_box_500_in,
            v_sts.sub_box_100_in,
            v_sts.sub_box_500_out,
            v_sts.sub_box_100_out
        FROM 
            [%s].[dbo].[store] AS _s		
            LEFT JOIN [%s].[dbo].[cashier] AS _c ON _c.store_id = _s.store_id
            LEFT JOIN [%s].[dbo].[v_store_yesterday_currency_statement] AS v_sls ON v_sls.store_id = _s.store_id
            LEFT JOIN [%s].[dbo].[v_store_today_statistics_data] AS v_sts ON v_sts.store_id = _s.store_id
        WHERE
            _s.store_state = 1
        ",
    $DataBase_Trend,
    $DataBase_Trend,
    $DataBase_Trend,
    $DataBase_Trend
);
//準備執行sql
$sth = $pdo_mssql->prepare($sql_last_currency_data);
$sth->execute([]);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
$a_store_last_currency_data = [];
foreach ($rows as $k => $v) {
    //循環箱各面額數量計算 最後一次日結 + (當日循環箱入鈔 - 當日循環箱出鈔) + 當日循環箱換補操作
    $n_cycle_50 = $v["last_cycle_box_50"] + ($v["cash_50_in"] - $v["cash_50_out"]) + $v["cycle_box_50"];
    $n_cycle_10 = $v["last_cycle_box_10"] + ($v["cash_10_in"] - $v["cash_10_out"]) + $v["cycle_box_10"];
    $n_cycle_5 = $v["last_cycle_box_5"] + ($v["cash_5_in"] - $v["cash_5_out"]) + $v["cycle_box_5"];
    $n_cycle_1 = $v["last_cycle_box_1"] + ($v["cash_1_in"] - $v["cash_1_out"]) + $v["cycle_box_1"];
    $n_sub_out_box_500 = $v["sub_box_500_out"] + $v["sub_total_500_out"];
    $n_sub_out_box_100 = $v["sub_box_100_out"] + $v["sub_total_100_out"];
    $a_store_last_currency_data[$v["store_id"]] = [
        "500" => $n_sub_out_box_500,
        "100" => $n_sub_out_box_100,
        "50" => $n_cycle_50,
        "10" => $n_cycle_10,
        "5" => $n_cycle_5,
        "1" => $n_cycle_1,
    ];
}

//開始判斷各部位是否低於警戒、停止水位
$a_store_check = [];
if (!empty($a_store_last_currency_data)) {
    foreach ($a_store_last_currency_data as $k => $v) {
        foreach ($v as $k_2 => $v_2) {

            $n_check_warning = $a_store_low_level[$k]["warning"][$k_2];
            $n_check_stop = $a_store_low_level[$k]["stop"][$k_2];
            if ($n_check_stop > 0 || $n_check_warning > 0) {
                //先判斷是否小於停止水位，如果小於 則不用再判斷是否小於警戒水位(發最小的那一個就好)
                if ($v_2 <= $n_check_stop) {
                    $a_store_check[$k][] = [
                        "set" => $n_check_stop,
                        "last_count" => $v_2,
                        "val" => $k_2,
                        "type" => "stop"
                    ];
                    $stop = 1;
                }
                //小於警戒水位
                else if ($v_2 <= $n_check_warning) {
                    $a_store_check[$k][] = [
                        "set" => $n_check_warning,
                        "last_count" => $v_2,
                        "val" => $k_2,
                        "type" => "warning"
                    ];
                    $stop = 1;
                }
            }
        }
    }
}

//根據當前需發送警報的門市，撈取各門市於最近一次換補後的低水位警報清單(因為需要判斷是否要發送line推播)
//順便整理需要寫入的log警報資料
$a_alarm_store = [];
$a_alarm_insert = []; //門市警報log寫入陣列
$a_cashier_update = []; //門市機台狀態更新用
foreach ($a_store_check as $k => $v) {
    $s_store_id = $k;
    $s_cashier_id = $a_store_info_list[$s_store_id]["cashier_id"];
    $a_alarm_store[] = $s_store_id;

    foreach ($v as $k_2 => $v_2) {
        $s_system_handle = json_encode([
            "type" => $v_2["type"],
            "val" => $v_2["val"],
        ]);
        $n_alarm_stop = $v_2["type"] == "stop" ? 1 : 0;
        //判斷是否有造成停機 如果有更改機台狀態為 2:異常
        if ($n_alarm_stop) {
            $n_cashier_state = 2;
        }
        $a_alarm_insert[] = "INSERT INTO [$DataBase_Alarm].[dbo].[store_alarm_report](
                             cashier_id,
                             store_id,
                             alarm_code,
                             alarm_name,
                             alarm_handle,
                             system_handle,
                             alarm_stop
                         )
                         VALUES
                         (
                             '$s_cashier_id',
                             $s_store_id,
                             $n_alarm_code,
                             '$s_alarm_name',
                             '$s_alarm_handle',
                             '$s_system_handle',
                             $n_alarm_stop
                         )
             			";
    }
    // 更新機台狀態陣列
    $a_cashier_update[] = "UPDATE [$DataBase_Trend].[dbo].[cashier]
        SET
            cashier_state = $n_cashier_state
        WHERE
            cashier_id = '$s_cashier_id' AND 
            store_id = $s_store_id          
        ";
}
$s_alarm_store = implode(",", $a_alarm_store);

$sql_store_alarm = sprintf(
    "SELECT
        v_salc.store_id,
        v_salc.system_handle
    FROM
        [%s].[dbo].[v_store_alarm_low_currency] AS v_salc
    WHERE
        v_salc.store_id IN (%s)",
    $DataBase_Trend,
    $s_alarm_store
);
//準備執行sql
$sth = $pdo_mssql->prepare($sql_store_alarm);
$sth->execute([]);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
$a_store_alarm_line_notify = []; //門市警報各面額log判斷用陣列 (各門市於最後一次換捕到當前所有面額的低水位警報紀錄)
foreach ($rows as $k => $v) {
    $a_temp_err_json = json_decode($v["system_handle"], true);
    if (is_array($a_temp_err_json)) {
        if (empty($a_store_alarm_line_notify[$v["store_id"]][$a_temp_err_json["type"]])) {
            $a_store_alarm_line_notify[$v["store_id"]][$a_temp_err_json["type"]] = [];
        }
        if (!in_array($a_temp_err_json["val"], $a_store_alarm_line_notify[$v["store_id"]][$a_temp_err_json["type"]])) {
            $a_store_alarm_line_notify[$v["store_id"]][$a_temp_err_json["type"]][] = $a_temp_err_json["val"];
        }
    }
}

//開始資料庫處理
if (!empty($a_alarm_insert)) {
    $pdo_mssql->beginTransaction();
    try {
        // 寫入log紀錄
        foreach ($a_alarm_insert as $k => $v) {
            $sth = $pdo_mssql->prepare($v);
            $sth->execute();
        }
        // 更新門市機台狀態
        foreach ($a_cashier_update as $k => $v) {
            $sth = $pdo_mssql->prepare($v);
            $sth->execute();
        }
        $pdo_mssql->commit();
        //line 推播判斷
        foreach ($a_store_check as $k => $v) {
            foreach ($v as $k_2 => $v_2) {
                $stop = 1;
                //當此次判斷到的門市該面額尚未存在歷史警報資料中時 則發送line通知
                if (
                    empty($a_store_alarm_line_notify[$k][$v_2["type"]]) ||
                    !in_array($v_2["val"],  $a_store_alarm_line_notify[$k][$v_2["type"]])
                ) {
                    $s_petty_type = $v_2["val"] < 100 ? "出幣機" : "出鈔機";

                    $s_alarm_note = sprintf(
                        "%s面額%s元的數量(%s)已低於警戒水位(%s個)！",
                        $s_petty_type,
                        $v_2["val"],
                        $v_2["last_count"],
                        $v_2["set"]
                    );
                    //line推播
                    $a_notify_data = [
                        "門市" =>  $a_store_info_list[$k]["store_name"],
                        "時間" => date("Y-m-d H:i:s"),
                        "異常代碼" => $n_alarm_code,
                        "異常狀況" => $s_alarm_name,
                        "對應動作" => $s_alarm_handle,
                        "備註" => $s_alarm_note,
                    ];
                    base_class::line_notify($a_notify_data);
                }
            }
        }
    } catch (Exception $e) {
        $pdo_mssql->rollBack();
    }
}
