<?php
/**
 * 各能源使用量過賬
 */
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_energy_increment.log';

// 能源資料表
$r_table = [
	'R_PM',
	'R_SM',
	'R_GM',
	'R_WM',
];

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'];
	}
	
	/* ================================================ 撈取資料 ================================================ */
	$tran_start = date("Y-m-d H:i:s", strtotime("$tran_date -1 minute")); // 取到前一天的最後一筆
	$date_prm = [
		'tran_start' => $tran_start,
		'tran_end' => date("Y-m-d 23:59:59", strtotime($tran_date)),
		'tran_start_date' => date("Y-m-d", strtotime($tran_start)), // 要先去除前一天的日期資料才可以Group By
	];
	

	foreach($r_table as $tb)
	{
		$col_key = '';
		$col_as = '';
		if($tb == 'R_PM')
		{
			$col_key = 'KWH';
			$col_as = 'incr_kwh';
		}
		else
		{
			$col_key = 'Acc';
			$col_as = 'incr_acc';
		}
		// 撈取 當日增加量
		$sql = "SELECT 
					CAST([R].[DATE_TIME] AS date) AS [date_stamp]
					,CAST([R].[ID] AS int) AS [id]
					,SUM([R].[diff_val]) AS [$col_as]
				FROM (
					SELECT [DATE_TIME]
						,[ID]
						,[$col_key]
						,CASE WHEN ISNULL([$col_key] - LAG([$col_key],1) OVER (PARTITION BY [ID] ORDER BY [DATE_TIME] ASC),0) < 0 THEN 0 
						ELSE ISNULL([$col_key] - LAG([$col_key],1) OVER (PARTITION BY [ID] ORDER BY [DATE_TIME] ASC),0) END AS [diff_val]
					FROM [$tb]
					WHERE [DATE_TIME] BETWEEN :tran_start AND :tran_end AND [$col_key] > 0
				) AS [R]
				WHERE CAST([R].[DATE_TIME] AS date) != :tran_start_date
				GROUP BY [R].[ID], CAST([R].[DATE_TIME] AS date)
				ORDER BY [R].[ID], CAST([R].[DATE_TIME] AS date) ASC";
		$sth = $pdo_hty->prepare($sql);
		$sth->execute($date_prm);
		$r_data = $sth->fetchAll(PDO::FETCH_ASSOC);

		// 整理各id當日用量(key=>val)
		$id_inc = [];
		foreach($r_data as $row)
		{
			$id_inc[$row['id']] = $row[$col_as];
		}

		// ====================================== 特殊id ======================================
		switch ($tb) 
		{
			case 'R_PM':
				$id_26 = isset($id_inc['26']) ? $id_inc['26'] : 0;
				$id_10 = isset($id_inc['10']) ? $id_inc['10'] : 0;
				$id_65 = isset($id_inc['65']) ? $id_inc['65'] : 0;
				$id_66 = isset($id_inc['66']) ? $id_inc['66'] : 0;
				$id_67 = isset($id_inc['67']) ? $id_inc['67'] : 0;
				// A倉
				$inc = $id_26-$id_10;
				$r_data[] = ["date_stamp"=> $tran_date, "id"=> 2610, "$col_as"=> $inc];
				// 低溫空調
				$inc = $id_65-($id_66+$id_67);
				$r_data[] = ["date_stamp"=> $tran_date, "id"=> 656667, "$col_as"=> $inc];
				break;
			default:
				break;
		}
		// ====================================================================================

		foreach($r_data as $row)
		{
			// 寫入資料
			$tb_2 = $tb."_daily";
			$sql = "MERGE [$tb_2] AS [T]
							USING (
								SELECT :date_stamp AS [date_stamp],
									:id AS [id],
									:$col_as AS [$col_as]
							) 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].[$col_as] = [S].[$col_as]
							WHEN NOT MATCHED THEN
								INSERT (date_stamp, id, $col_as)
								VALUES ([S].[date_stamp], [S].[id], [S].[$col_as]);";
			$sth = $pdo->prepare($sql);
			$sth->execute($row);
			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);

?>