<?php
error_reporting(E_ALL);
ini_set("display_errors", 0);

//	exec("./php_my_root strongswan rereadsecrets"); // 테스트 끝나면 이 라인은 삭제할 것

ini_set('max_execution_time', 86400); // 실행지연시간 24시간(=86400초)로 설정함!
date_default_timezone_set('Asia/Seoul');
//date_default_timezone_set('Asia/Shanghai');

///// 자동동기화 STEP2 [[
/////
$B_START_TIME = date("Y-m-d",time() - 60*60*24*7);  // 최근7일간
//$B_START_TIME = date("Y-m-d H:i:s",time() - 60*60*24*185);  // 최근185일간
//$B_START_TIME = "1970-01-01";  // 1970-01-01부터

$B_LAST_TIME = date("Y-m-d",time()+172800); // 서버 시간 오차를 충분히 감안하여 현재시간+2일 지정함
/////

$B_process_start_time = time();

/// 연관되는테이블 지정 [[
require_once "db_source_1.php"; // 원본 DB 연결
require_once "db_target_1.php"; // 백업 DB 연결

$B_table_name_source_1 = "vpn_list";
$B_table_name_target_1 = "vpnnara_last_update";
/// 연관되는테이블 지정 ]]


$update_flag = "";
$ipsec_secret_change_flag = "no";
$NowDatetime = date("Y-m-d H:i:s");
$LINUX_LINEFEED_CHAR = chr(13).chr(10);



//////// 일시중지중이거나 요금이 소진된 접속자 강제접속 종료(KILL)시키기 1/2 [[

$C_query_source_1 = "SELECT * FROM `$B_table_name_source_1` WHERE vpn_last_status != 'active' OR vpn_point <= '0' OR ( date(vpn_datetime_start) > date_format('$NowDatetime','%Y-%m-%d %H:%i:%s') AND date(vpn_datetime_end) < date_format('$NowDatetime','%Y-%m-%d %H:%i:%s') ) ORDER BY vpn_update_time DESC ";
$C_result_source_1 = $DB_SOURCE_1->query($C_query_source_1);
$C_total_source_1 = mysqli_num_rows($C_result_source_1);
while( $C_row_source_1 = mysqli_fetch_array($C_result_source_1) ) {
	$vpn_id_kill = $C_row_source_1["vpn_id"];

	$D_query_target_1 = "SELECT * FROM `vpn_status` WHERE service_emailid = '$vpn_id_kill' ORDER BY no DESC LIMIT 1";
	$D_result_target_1 = $DB_TARGET_1->query($D_query_target_1);
	$D_total_target_1 = mysqli_num_rows($D_result_target_1);
	$D_row_target_1 = mysqli_fetch_array($D_result_target_1);
	$vpn_vdhcp_ip_kill = $D_row_target_1["service_vdhcp_id"];

	//echo "###".$vpn_id_kill."[".$vpn_vdhcp_ip_kill."]###<br>";

	exec("./php_my_root strongswan down-srcip ".$vpn_vdhcp_ip_kill, $result_commend);  // VPN서버 상태 조회 및 변수저장
	exec("./php_my_root strongswan rereadsecrets ");
	$ipsec_secret_change_flag = "yes";
}

//////// 일시중지중이거나 요금이 소진된 접속자 강제접속 종료(KILL)시키기 1/2 ]]




	$B_IPSEC_SECRETS_SET[] = '# ipsec.secrets - strongSwan IPsec secrets file [Created '.$NowDatetime."]";
	$B_IPSEC_SECRETS_SET[] = ': RSA "privkey.pem"';


	$B_query_source_1 = "SELECT * FROM `$B_table_name_source_1` WHERE vpn_last_status = 'active' AND vpn_point > '0' AND date(vpn_datetime_start) <= date_format('$NowDatetime','%Y-%m-%d %H:%i:%s') AND date(vpn_datetime_end) >= date_format('$NowDatetime','%Y-%m-%d %H:%i:%s') ORDER BY vpn_update_time DESC ";

	$B_result_source_1 = $DB_SOURCE_1->query($B_query_source_1);
	$B_total_source_1 = mysqli_num_rows($B_result_source_1);

	if ($B_total_source_1 > 0) {

		$DB_TARGET_1->query("TRUNCATE TABLE `$B_table_name_target_1`"); // 새로 저장하기 위해 테이블 내용 비우기

		$B_row_source_1_cnt = 0;
		$B_row_target_1_cnt_insert = 0;
		$B_row_target_1_cnt_update = 0;
		$B_row_target_1_cnt_unknown = 0;




		while( $B_row_source_1 = mysqli_fetch_array($B_result_source_1) ) {
			$B_row_source_1_cnt++;

			$vpn_id_save = $B_row_source_1["vpn_id"];
			$vpn_passwd_save = $B_row_source_1["vpn_passwd"];

			$B_IPSEC_SECRETS_SET[] = $vpn_id_save.' : EAP "'.$vpn_passwd_save.'"';  // ipsec.secrets 파일을 업데이트 할 내용 작성

			$B_CNT=0;
			if (isset($B_result_target_1)) unset($B_result_target_1);
			if (isset($B_total_target_1)) unset($B_total_target_1);
			if (isset($B_array_source_1_json_data)) unset($B_array_source_1_json_data);
			if (isset($B_array_source_1_object)) unset($B_array_source_1_object);
			if (isset($B_QUERY_SET)) unset($B_QUERY_SET);

			$B_array_source_1_json_data = json_encode($B_row_source_1);
			$B_array_source_1_object = json_decode($B_array_source_1_json_data);

			foreach ($B_array_source_1_object as $key => $value) {
				$B_CNT++;
				if (($B_CNT % 2) == 0) {

					switch ($key) {
						case "no":
						case "uniqid":
						case "vpn_update_time":
						   $B_QUERY_SET[] = $key."='".$value."'";
							break;
						default:
							break;
					}

				}
			}


	//		echo $B_QUERY_SET_FINAL."<br><br>***********************<br><br>";



				//************ 백업하기 [[
				$B_row_target_1_cnt_insert++;
				if ($B_row_source_1_cnt == 1) {  // 최초 1개만 클라이언트 서버 DB에 저장하기 위함
					$B_QUERY_STRING = "INSERT `$B_table_name_target_1` SET " . $B_QUERY_SET_FINAL;
					$DB_TARGET_1->query($B_QUERY_STRING);
				}
				//************ 백업하기 ]]



		} //while

		$B_process_end_time = time();


	//	echo "[$B_START_TIME~$B_LAST_TIME] [$B_table_name_source_1] 원본갯수 $B_row_source_1_cnt | INSERT갯수 $B_row_target_1_cnt_insert  | UPDATE갯수 $B_row_target_1_cnt_update   | UNKNOWN갯수 $B_row_target_1_cnt_unknown | 처리속도 ".($B_process_end_time - $B_process_start_time)." 초";
		echo "".$NowDatetime." "."동작상태 아이디 : $B_row_source_1_cnt  개 , 처리속도 : ".($B_process_end_time - $B_process_start_time)." 초";

		$update_flag = "yes";


	} else {
		echo $NowDatetime." 처리된 자료가 없습니다<br>";
		$update_flag = "yes";
	}
	///// 자동동기화 STEP2 ]]
	?>


	<?php
	/////////// IKEv2 회원정보 업데이트 strongSwan에 비번 추가/수정후 서비스 재실행 [[
	if ($update_flag == "yes") {


	///// 테스트용 아이디 디폴트 [[
	$B_IPSEC_SECRETS_TESTID_TEXT = "user1 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user2 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user3 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user4 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user5 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user6 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user7 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user8 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user9 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."user10 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test1 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test2 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test3 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test4 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test5 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test6 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test7 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test8 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test9 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."test10 : EAP \"vpn12345\"".$LINUX_LINEFEED_CHAR."cuijin1501 : EAP \"vpn1501\"".$LINUX_LINEFEED_CHAR;




	$B_QUERY_SET_FINAL = implode($B_QUERY_SET, ",");
	$B_IPSEC_SECRETS_TEXT = implode($B_IPSEC_SECRETS_SET, $LINUX_LINEFEED_CHAR);

	$B_IPSEC_SECRETS_TEXT = $B_IPSEC_SECRETS_TEXT .$LINUX_LINEFEED_CHAR . $B_IPSEC_SECRETS_TESTID_TEXT;
	///// 테스트용 아이디 디폴트 ]]

		$fp = fopen("/etc/strongswan/ipsec.secrets" , "w");
		fwrite($fp,$B_IPSEC_SECRETS_TEXT,strlen($B_IPSEC_SECRETS_TEXT));
		fclose($fp);

		exec("./php_my_root strongswan rereadsecrets");

	/////////// IKEv2 회원정보 업데이트 strongSwan에 비번 추가/수정후 서비스 재실행 [[

	}




// 업데이트 여부 체크 [[
// 업데이트 원리 : 최근7일이내 새로 추가된 이미지가 있으면 최근 7일간의 자료 덮어쓰기
$B_query_source_1_precheck = "SELECT uniqid, vpn_update_time FROM `$B_table_name_source_1` ORDER BY vpn_update_time DESC LIMIT 1";
$B_result_source_1_precheck = $DB_SOURCE_1->query($B_query_source_1_precheck);
$B_row_source_1_precheck = $B_result_source_1_precheck->fetch_assoc();
$B_uniqid_source_1_precheck=addslashes($B_row_source_1_precheck['uniqid']);
$B_vpn_update_time_source_1_precheck=addslashes($B_row_source_1_precheck['vpn_update_time']);

$B_query_target_1_precheck = "SELECT uniqid, vpn_update_time FROM `$B_table_name_target_1` ORDER BY vpn_update_time DESC LIMIT 1";
$B_result_target_1_precheck = $DB_TARGET_1->query($B_query_target_1_precheck);
$B_row_target_1_precheck = $B_result_target_1_precheck->fetch_assoc();
$B_uniqid_target_1_precheck=addslashes($B_row_target_1_precheck['uniqid']);
$B_vpn_update_time_target_1_precheck=addslashes($B_row_target_1_precheck['vpn_update_time']);


if ((trim($B_vpn_update_time_source_1_precheck) != "") && (trim($B_vpn_update_time_source_1_precheck) == trim($B_vpn_update_time_target_1_precheck))) {  // 최종 DB정보가 같다면 실행중단
	//	echo "###".$B_uniqid_source_1_precheck . "(". $B_vpn_update_time_source_1_precheck.") + " . $B_uniqid_target_1_precheck ."(".$B_vpn_update_time_target_1_precheck.")###";
	echo $NowDatetime." 변경사항이 없습니다<br>";
} else if (trim($B_vpn_update_time_source_1_precheck) == "") {  //  || $B_vpn_update_time_target_1_precheck == "" 를 포함 안하는 이유는 빈 테이블 일수도 있어서 임
	// echo "@@@".$B_uniqid_source_1_precheck . "(". $B_vpn_update_time_source_1_precheck.") + " . $B_uniqid_target_1_precheck ."(".$B_vpn_update_time_target_1_precheck.")@@@";
}
// 업데이트 여부 체크 ]]





?>
