<?php
// http://127.0.0.1/hsintungyang-energy/action/schedule/daily_power_range.php?tran_date=2024-07-01
/**
 * 各能源使用量過賬
 */
set_time_limit(0);
require_once "../Parameters.php";

// 建立DB連綫 ...
$pdo = new PDO(sprintf("sqlsrv:Server=%s; Database=%s;", $DataSource, $DataBase_Alarm), $UserID, $Password);
$pdo->beginTransaction(); // sql transction開始
$log_file = './daily_power_range.log';


try 
{
	$check = @fsockopen(HTY_Server, '1433', $errno, $errstr, 0.2);
	$pdo_hty = null;
	if ($check)
	{
		$pdo_hty = new PDO(sprintf("sqlsrv:Server=%s; Database=%s;ConnectionPooling=0", HTY_Server, HTY_Database), HTY_UserID, HTY_Password);
		// $pdo_hty->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	}
	else
	{
		throw new Exception('HTY database conection error.');
	}

	$yesterday = new DateTime();
	$yesterday->modify("-1 days");
	$tran_date = $yesterday->format('Y-m-d');
	if(isset($_GET['tran_date']))
	{
		$tran_date = $_GET['tran_date'];
	}
	
	// 是否為假日
	$sql = "SELECT *
					FROM [HTY].[dbo].[holiday]
					WHERE [date_time] = '$tran_date'";
	$sth = $pdo_hty->prepare($sql);
	$sth->execute();
	$holiday = $sth->fetch(PDO::FETCH_ASSOC);

	$weekday_id = null;
	$wd = date("N", strtotime($tran_date));
	if($holiday)
	{
		$weekday_id = 7; // 國定假日
	}
	else
	{
		if($wd >= 1 && $wd <=5)
		{
			$weekday_id = 1; // 平日
		}
		else
		{
			$weekday_id = $wd; // 週六/日
		}
	}	

	// 是否為夏月
	$is_summer = 0;
	$sql = "SELECT * 
					FROM [cfg_summer_range] 
					WHERE '$tran_date' >= CAST(CONCAT(YEAR(GETDATE()),'-',[start_month],'-',[start_day]) AS date) 
						AND '$tran_date' <= CAST(CONCAT(YEAR(GETDATE()),'-',[end_month],'-',[end_day]) AS date)";
	$sth = $pdo->prepare($sql);
	$sth->execute();
	if($sth->fetch(PDO::FETCH_ASSOC))
	{
		$is_summer = 1;
	}

	// 撈取各用電時段
	$cfg_param = [
		'is_summer'=> $is_summer,
		'weekday_id' => $weekday_id,
	];
	$sql = "SELECT * 
					FROM [cfg_power_range] 
					WHERE [weekday_id] = :weekday_id AND [is_summer] = :is_summer
					ORDER BY [peak_id] ASC";
	$sth = $pdo->prepare($sql);
	$sth->execute($cfg_param);
	$power_range = $sth->fetchAll(PDO::FETCH_ASSOC);

	$sql_arr = [];
	foreach($power_range as $pr)
	{

		$peak_name = "peak_".$pr['peak_id'];
		if($pr['peak_id'] == 2 && ($weekday_id == 1 || $weekday_id == 6))
		{
			$peak_name .= ('_'.$weekday_id);
		}
		$start_time = $tran_date.' '.$pr['start_time'];
		// 避免漏算各時段交接處的用量，開始時間減1分鐘
		$start_time = date("Y-m-d H:i:s", strtotime("$start_time -1 minute"));
		$end_time = $tran_date.' '.$pr['end_time'];
		$sql_arr[] = "SELECT [R].[ID],
							'$peak_name' AS [peak],
							SUM([R].[diff_val]) AS [incr_kwh],
							SUM([R].[diff_val]) * ISNULL({$pr['price']},0) AS [cost]
						FROM ( 
							SELECT [DATE_TIME] ,
								[ID] ,
								[KWH] ,
								CASE WHEN ISNULL([KWH] - LAG([KWH],1) OVER (PARTITION BY [ID] ORDER BY [DATE_TIME] ASC),0) < 0 THEN 0 
								ELSE ISNULL([KWH] - LAG([KWH],1) OVER (PARTITION BY [ID] ORDER BY [DATE_TIME] ASC),0) END AS [diff_val] 
							FROM [R_PM] 
							WHERE [DATE_TIME] BETWEEN '$start_time' AND '$end_time' AND [KWH] > 0 
						) AS [R]
						GROUP BY [R].[ID]";
	}
	$sql = sprintf("WITH [peak_data] AS (
		%s
	) SELECT [ID],
			[peak],
			SUM([incr_kwh]) AS [sum_kwh],
			SUM([cost]) AS [sum_cost]
	FROM [peak_data] 
	GROUP BY [ID],[peak]
	ORDER BY [ID],[peak]", implode(' UNION ', $sql_arr));
	$sth = $pdo_hty->prepare($sql);
	$sth->execute();
	$peak_data = $sth->fetchAll(PDO::FETCH_ASSOC);

	// 整理ID組合資料
	$peak_data_group = [];
	foreach($peak_data as $row)
	{
		$peak_data_group[intval($row['ID'])][] = $row;
	}

	$peak_cols = [
		'peak_1',
		'peak_2_1',
		'peak_2_6',
		'peak_3'
	];
	foreach($peak_data_group as $id => $id_row) // 走訪ID組合資料
	{
		// 整理寫入參數
		$prd_param = ['date_stamp' => $tran_date, 'id' => $id, 'cost' => 0];
		foreach($peak_cols as $col)
		{
			$prd_param[$col] = 0;
			foreach($id_row as $row)
			{
				if($row['peak'] == $col)
				{
					$prd_param[$col] = $row['sum_kwh'];
					$prd_param['cost'] += floatval($row['sum_cost']);
				}
			}
		}
		// 寫入資料
		$sql = "MERGE [PM_range_daily] AS [T]
					USING (
						SELECT :date_stamp AS [date_stamp],
							:id AS [id],
							:peak_1 AS [peak_1],
							:peak_2_1 AS [peak_2_1],
							:peak_2_6 AS [peak_2_6],
							:peak_3 AS [peak_3],
							:cost AS [cost]
					) AS [S]
					ON [T].[date_stamp] = [S].[date_stamp] AND [T].[id] = [S].[id]
					WHEN MATCHED THEN
						UPDATE SET [T].[updated_at] = GETDATE(),
									[T].[date_stamp] = [S].[date_stamp],
									[T].[id] = [S].[id],
									[T].[peak_1] = [S].[peak_1],
									[T].[peak_2_1] = [S].[peak_2_1],
									[T].[peak_2_6] = [S].[peak_2_6],
									[T].[peak_3] = [S].[peak_3],
									[T].[cost] = [S].[cost]
					WHEN NOT MATCHED THEN
						INSERT ([date_stamp],[id],[peak_1],[peak_2_1],[peak_2_6],[peak_3],[cost])
						VALUES ([S].[date_stamp],[S].[id],[S].[peak_1],[S].[peak_2_1],[S].[peak_2_6],[S].[peak_3],[S].[cost]);";
		$sth = $pdo->prepare($sql);
		$sth->execute($prd_param);
		if ($sth->rowCount() <= 0) 
		{
			throw new Exception($sth->errorInfo()[2]);
		}
	}

	$pdo->commit();
	echo 'OK';
	$ok_msg = date('Y-m-d H:i:s').' tran_date: '.$tran_date.' OK'."\n";
	file_put_contents($log_file, $ok_msg, FILE_APPEND);
} 
catch (Exception $e) 
{
	$pdo->rollBack();
	echo $e->getMessage();
	$error_msg = date('Y-m-d H:i:s').' '.$e->getMessage()."\n";
	file_put_contents($log_file, $error_msg, FILE_APPEND);
}
// echo json_encode($_FILES); exit(1);

?>