<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<?php
	
function decodeSTRING($val){
	// #, &, +  < > などが URI 送信で化けるため encode して送られたものを decode
	$array = explode("%x0;", $val);
	$val = implode("#", $array);
	$array = explode("%x1;", $val);
	$val = implode("&", $array);
	$array = explode("%x2;", $val);
	$val = implode("+", $array);
	$array = explode("%x3;", $val);
	$val = implode("<", $array);
	$array = explode("%x4;", $val);
	$val = implode(">", $array);
	$array = explode("%x5;", $val);
	$val = implode(" ", $array);
	$array = explode("%x6;", $val);
	$val = implode("\n", $array);
	$array = explode("%x7;", $val);
	$val = implode("\"", $array);
	$array = explode("%x8;", $val);
	$val = implode("$", $array);
	return $val;
}
	
function timestamp(){
	// 現在のタイムスタンプを返す updateTime に使う
    date_default_timezone_set("Asia/Tokyo");
	return date("Y-m-d H:i:s",time());
}

function leftBrackets(){
	// $leftBrackets = "\^\[\^" では誤作動
	//	return "\^\[\^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
	return "^[^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
}
function rightBrackets(){
	return "^]^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
	//	return "\^\]\^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
}
function decodeObject($buff){
	// "key(value)" 形式の文字列を OBJECT に decode して返す
	$obj = array();
	//echo "decodeObject ===\n$buff\n"; //##
	for ($p=$begin=$nest=0, $len=strlen($buff); $p < $len; $p++){
		$ch = substr($buff, $p, 1);
		if (strcmp($ch, "(") == 0){
			if ($nest == 0){
				$key = trim(substr($buff, $begin, $p - $begin)); // key 終端
				$begin = $p + 1; // value 開始位置
			}
			$nest++;
		} else if (strcmp($ch, ")") == 0){
			if (--$nest == 0){
				if ($nest == 0){
					// value 終端 ( trim しない )
					$value = substr($buff,$begin,$p-$begin);
					$begin = $p + 1; // key 開始位置
				}
				if (preg_match("/\)/", $value)){ // まだ入れ子になっているなら
					$obj[$key] = decodeObject($value);
				} else {
					// 特定文字を "(", ")" へ戻す
					$array = explode(leftBrackets(), $value);
					$value = join("(", $array);
					$array = explode(rightBrackets(), $value);
					$value = join(")", $array);
					
					$obj[$key] = $value;
				}
				//echo "$key{".$obj[$key]."}\n"; //##
			}
		}
	}
	return $obj;
}
	
function getBookingList($link, $year, $month, $day){
	// $year, $month, $day の予約患者リストを返す
    if ($day > 0){
        $begin = "$year-$month-$day 00:00:00";
        $end = "$year-$month-$day 23:59:59";
    } else {
        $begin = "$year-$month-01 00:00:00";
        $end = "$year-$month-31 23:59:59";
    }
	
	$sql = "SELECT * FROM `FrontTable` WHERE `entryDate`>='$begin' AND `entryDate`<='$end' ORDER BY `entryDate`";
	echo "====== GET_BOOKING\n $sql \n";//###
	$result=mysqli_query($link, $sql);
	
	$array = array();
	if (mysqli_num_rows ($result) > 0){
		// 指定年月日の予約患者を $array に入れる
        while ($row=mysqli_fetch_array($result)){
            $obj = array();
            $obj['patientId'] = $row['patientId'];
            $obj['inOut'] = $row['inOut'];
            $obj['anotherId'] = $row['anotherId'];
            $obj['name'] = $row['kanjiName'];
            $obj['entryDate'] = $row['entryDate'];
            $obj['plan'] = $row['plan'];
            $array[] = $obj;
        }
	}
	return $array;
}
	
function getHospital($link, $hid){
	$sql = "SELECT * FROM `HospitalTable` WHERE `hospitalId`='$hid'";
	echo "===== getHospital\n $sql \n";
	$array = array();
	$result=mysqli_query($link, $sql);
	while ($row=mysqli_fetch_assoc($result)){ // 連想配列でレコードを取り出す
		$key = $row['key'];
		$value = $row['value'];
		$array[$key] = $value;
		echo "  $key -> $value \n"; //##
	}
	echo "\n\n";
	
	return $array;
}

function getUsers($link, $groupCode){
	if ($groupCode && (strlen(groupCode) > 0))
		$sql = "SELECT * FROM `UserTable` WHERE `groupCode`='$groupCode'";
	else
		$sql = "SELECT * FROM `UserTable`";
	echo "===== getUser\n $sql \n";
	$array = array();
	$result=mysqli_query($link, $sql);
	if (mysqli_num_rows ($result) > 0){
		while ($row=mysqli_fetch_assoc($result)){
			$ary = array();
			$ary['loginName'] = $row['loginName'];
			$ary['kanjiName'] = $row['kanjiName'];
			$ary['groupCode'] = $row['groupCode'];
			$array[] = $ary;
		}
	}
	return $array;
}
    
    function getPreference($link, $hospitalId, $owner, $groupCode){
        // 予約の初期設定を 'interval(15)' 形式で返す
        
        $records = array();
        $sql = "SELECT * FROM `MenuTable`
        WHERE `tag`='FrontTable.plan' AND `owner`='$owner'
        ORDER BY `freq` DESC";
        echo "=== GET_BOOKING_PREFERENCE\n $sql \n\n"; //##
        $result=mysqli_query($link, $sql);
        
        if (mysqli_num_rows ($result) == 0){
            // ## private なデータがない場合は public を使う
            $sql = "SELECT * FROM `MenuTable`
            WHERE `tag`='FrontTable.plan' AND `owner`='public'
            ORDER BY `freq` DESC";
            echo "=== $sql \n\n"; //##
            $result=mysqli_query($link, $sql);
        }
        
        $array = array();
        while ($row=mysqli_fetch_array($result)){
            $menu = $row['menu'];
            $array[$menu] = $row['freq'];
        }
        $records['planMenu'] = $array;
        
        $sql = "SELECT * FROM `FrontTable`
        WHERE `patientId`='BOOKING_PREFERENCE' AND `owner`='$owner'";
        echo "=== $sql \n"; //##
        $result=mysqli_query($link, $sql);
        
        if (mysqli_num_rows ($result) == 0){
            // ## private なデータがない場合は public を使う
            $sql = "SELECT * FROM `FrontTable`
            WHERE `patientId`='BOOKING_PREFERENCE' AND `owner`='public'";
            echo "=== $sql \n"; //##
            $result=mysqli_query($link, $sql);
        }
        // plan に encodeObject() 形式で設定値が収納されている
        $row = mysqli_fetch_array($result);
        $records['preference'] = $row['plan'];
        
        // 予約票のコメントを得る
        $sql = "SELECT * FROM `FrontTable`
        WHERE `patientId`='BOOKING_COMMENT'";
        echo "=== $sql \n"; //##
        $result=mysqli_query($link, $sql);
        $row = mysqli_fetch_array($result);
        $records['comment'] = $row['plan'];
        
        // hospitalInfo
        $records['hospitalInfo'] = getHospital($link, $hospitalId);
        
        // userInfo
        $records['userInfo'] = getUsers($link, $groupCode);
        
        return $records;
    }
	
	
$command = $_GET['command']; $command=htmlspecialchars($command);
$owner = $_GET['owner']; $owner=htmlspecialchars($owner);
$value = $_GET['value']; $value=htmlspecialchars($value);
$value = decodeSTRING($value);
	
// DB を開いて該当レコードを読み込む

// ### セキュリティーを保つには、以下のファイルを外からアクセスできない 
// ### directory に置き、以下の cfg.php の pass をそこへ変更
require_once('cfg.php');

$link = mysqli_connect($db['host'], $db['user'], $db['pwd'], $db['dbname']);

// 接続状況をチェック
if (mysqli_connect_errno()) {
    echo("Connect failed: ".mysqli_connect_error()." <br>");
    exit();
}

$db_select = mysqli_select_db($link, $db['dbname']);
mysqli_query($link, "set names utf8");
    
if (strcmp($command,"GET_BOOKING") == 0){
	// 予約日をピックアップ
	$args = decodeObject($value); // client 側との約束ごと
	$year = $args['year'];
	$month = $args['month'];
	$day = $args['day'];
	$array = getBookingList($link, $year, $month, $day);
	
	echo "<SEPARATOR>";
	echo json_encode($array);
} else if (strcmp($command,"CHANGE_BOOKING") == 0){
	// 予約時刻を変更
	$args = decodeObject($value); // client 側との約束ごと
    $patientId = $args['patientId'];
	$inOut = $args['inPatient'];
	$anotherId = $args['anotherId'];
	$name = $args['name'];
	$entryDate = $args['entryDate'];
	$newEntryDate = $args['newEntryDate'];
	$plan = $args['plan'];
	$updateTime = timestamp();
	
	$sql = "SELECT * FROM `FrontTable` WHERE `patientId`='$patientId' AND `entryDate`='$entryDate'";
	echo "=== CHANGE_BOOKING\n $sql \n"; //##
	$result=mysqli_query($link, $sql);
	// レコードがあれば　$fieldArray 内のものをアップデート
	if (mysqli_num_rows ($result) > 0){
		// 該当するレコードの rowid をピックアップ
		$row = mysqli_fetch_array($result);
		$rowid = $row['rowid'];
		
		$sql = "UPDATE `FrontTable` 
		SET `anotherId`='$anotherId',`plan`='$plan',`inOut`='$inOut'
		,`reservedDate`='$newEntryDate',`entryDate`='$newEntryDate',`updateTime`='$updateTime'
		WHERE `rowid`='$rowid'";
	} else {
		$sql = "INSERT INTO `FrontTable` 
		(`inOut`,`patientId`,`anotherId`,`kanjiName`,`plan`,`reservedDate`,`entryDate`,`updateTime` )
		VALUES ('$inOut','$patientId','$anotherId','$name','$plan','$newEntryDate','$newEntryDate','$updateTime')";
	}
	echo "=== $sql \n"; //##
	mysqli_query($link, $sql); // $sql 実行 ####
	
	// 更新後のリストを返す
	$datetimes = explode(" ", $entryDate);
	$dates = explode("-", $datetimes[0]);
	$array = getBookingList($link, $dates[0], $dates[1], $dates[2]);
	
	echo "<SEPARATOR>";
	echo json_encode($array);
} else if (strcmp($command,"REMOVE_BOOKING") == 0){
	// 予約を削除
	$args = decodeObject($value); // client 側との約束ごと
	$patientId = $args['patientId'];
	$entryDate = $args['entryDate'];
	$updateTime = timestamp();
	
	$sql = "DELETE FROM `FrontTable`
	WHERE `patientId`='$patientId' AND `entryDate`='$entryDate'";
	echo "=== REMOVE_BOOKING\n $sql \n"; //##
	$result=mysqli_query($link, $sql);
	
	// 更新後のリストを返す
	$datetimes = explode(" ", $entryDate);
	$dates = explode("-", $datetimes[0]);
	$array = getBookingList($link, $dates[0], $dates[1], $dates[2]);
	
	echo "<SEPARATOR>";
	echo json_encode($array);
} else if (strcmp($command,"PUT_BOOKING_PREFERENCE") == 0){
	// 予約の初期設定を保存
    
    // ## INTERVEL と HOLIDAYS を保存 #######################
	// $value にアーカイブした設定値が入っている
    echo "PUT_BOOKING_PREFERENCE\n$value \n\n"; //##
    
    $obj = json_decode($value, TRUE);
    echo "obj === $obj \n\n"; //##
    
    $owner = $obj['owner'];
    $minAndHolidays = $obj['minAndHolidays'];
    $comments = $obj['comments'];
	$hospitalId = $args['hospitalId']; // 更新データ確認用
	$groupCode = $args['groupCode']; // 更新データ確認用
	$patientId = "BOOKING_PREFERENCE";
	$timestamp = timestamp();
	
	$sql = "SELECT * FROM `FrontTable` WHERE `patientId`='$patientId'";
	echo "=== PUT_BOOKING_PREFERENCE\n $sql \n\n"; //##
	$result=mysqli_query($link, $sql);
	// レコードがあれば　$fieldArray 内のものをアップデート
	if (mysqli_num_rows ($result) > 0){
		// 該当するレコードの rowid をピックアップ
		$row = mysqli_fetch_array($result);
		$rowid = $row['rowid'];
		
		$sql = "UPDATE `FrontTable` SET `plan`='$minAndHolidays',`owner`='$owner',`updateTime`='$timestamp' WHERE `rowid`='$rowid'";
	} else {
		$sql = "INSERT INTO `FrontTable` (`patientId`,`plan`,`owner`,`updateTime` ) VALUES ('$patientId','$minAndHolidays','$owner','$timestamp')";
	}
	echo "=== $sql \n"; //##
	mysqli_query($link, $sql); // $sql 実行 ####
    
    // ## COMMENT1 と COMMENT2 を保存 ######################
	$patientId = "BOOKING_COMMENT";
	$timestamp = timestamp();
	
	$sql = "SELECT * FROM `FrontTable` WHERE `patientId`='$patientId'";
	echo "=== PUT_BOOKING_COMMENT\n $sql \n\n"; //##
	$result=mysqli_query($link, $sql);
	// レコードがあれば　$fieldArray 内のものをアップデート
	if (mysqli_num_rows ($result) > 0){
		// 該当するレコードの rowid をピックアップ
		$row = mysqli_fetch_array($link, $result);
		$rowid = $row['rowid'];
		
		// $value の中に encodeObject() 形式で入っている
		$sql = "UPDATE `FrontTable` SET `plan`='$comments',`owner`='$owner',`updateTime`='$timestamp' WHERE `rowid`='$rowid'";
	} else {
		$sql = "INSERT INTO `FrontTable` (`patientId`,`plan`,`owner`,`updateTime` ) VALUES ('$patientId','$comments','$owner','$timestamp')";
	}
	echo "=== $sql \n"; //##
	mysqli_query($link, $sql); // $sql 実行 ####
    
    // 更新結果を返す
    $records = getPreference($link, $hospitalId, $owner, $groupCode);
	echo "<SEPARATOR>";
	echo json_encode($records);
} else if (strcmp($command,"GET_BOOKING_PREFERENCE") == 0){
	// 予約の初期設定を 'interval(15)' 形式で返す
	$args = decodeObject($value); // client 側との約束ごと
	$hospitalId = $args['hospitalId'];
	$owner = $args['owner'];
	$groupCode = $args['groupCode'];

    $records = getPreference($link, $hospitalId, $owner, $groupCode);

	echo "<SEPARATOR>";
	echo json_encode($records);
} else if (strcmp($command,"GET_BOOKING_HISTORY") == 0){
	$args = decodeObject($value); // client 側との約束ごと
	$yy = $args['year'];
	$mm = $args['month'];
	$begin = "$yy-$mm-01 00:00:00";
	$yy2 = $yy;
	$mm2 = $mm + 2;
	if ($mm2 > 12){
		$yy2++;
        if ($mm2 == 13) // $mm2 が13月の場合は1月を返す
            $mm2 = 1;
        else if ($mm2 == 14) // $mm2 が14月の場合は2月を返す
            $mm2 = 2;
	}
	$end = "$yy2-$mm2-31 23:59:59";

	$sql = "SELECT * FROM `FrontTable` WHERE `entryDate`>='$begin' AND `entryDate`<='$end' ORDER BY `entryDate`";
	echo "=== GET_BOOKING_HISTORY\n $sql \n\n"; //##
	$result=mysqli_query($link, $sql);

	$array = array();
	while ($row = mysqli_fetch_array($result)){
		$date = $row['entryDate'];
		$ary = explode(" ", $date);
        $yymmdd = $ary[0];

        // $yymmdd の予約者数を $array に記憶
        $count = ($array[$yymmdd]) ? $array[$yymmdd] : 1;
		$array[$yymmdd] = $count + 1;
	}
	
	echo "<SEPARATOR>";
	$records = array();
	$records['year'] = $args['year'];
	$records['month'] = $args['month'];
	$records['history'] = $array;
	echo json_encode($records);
} else if (strcmp($command,"GET_BOOKING_DATE") == 0){
    // patientId の予約日を検索
	$args = decodeObject($value); // client 側との約束ごと
	$patientId = $args['patientId'];
    $today = date("Y-m-d H:i:s",time());
    
	$sql = "SELECT * FROM `FrontTable` WHERE `patientId`='$patientId' AND `entryDate`>'$today' ORDER by `entryDate`";
	echo "=== GET_BOOKING_DATE\n $sql \n\n"; //##
	$result=mysqli_query($link, $sql);
    
	$array = array();
	while ($row = mysqli_fetch_array($result)){
		$array[] = $row['entryDate'];
	}
	
	echo "<SEPARATOR>";
	echo json_encode($array);
} else
	echo "*** unknown command: $command ***";

?>