<?php
//	exec("./php_my_root strongswan rereadsecrets"); // 테스트 끝나면 이 라인은 삭제할 것
require_once "db_target_1.php"; // 이 서버의 DB 연결

exec("./php_my_root strongswan statusall", $result_commend);  // VPN서버 상태 조회 및 변수저장

// $줄바꿈문자제거arr = array("\r\n","\r","\n","\t","  ");
for ($i=0; $i < count($result_commend); $i++) {
	$result_commend[$i] = $result_commend[$i]."Ž";
//	echo "[ $i ]$result_commend[$i]<br>";
}


$uptime = $result_commend[1];
$malloc = $result_commend[2];
$Virtual_IP_pools = $result_commend[2];
$Listening_IP_addresses = $result_commend[8];
$local = $result_commend[11];
$cert = $result_commend[11];
$remote = $result_commend[13];
$child = $result_commend[14];

$SERVICE_ID = 0;

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

for ($k=16; $k < count($result_commend); $k++) {  // 배열 $result_commend의 16번째부터 연결정보가 시작됨

	$dbsave_flag = "no"; ////// db 저장여부 flag 초기화

	////// 변수 내용 모두 비움 [[
	$stat="";
	$service_emailid="";
	$service_vdhcp_id="";
	$service_ip="";
	$service_Rx_bytes="";
	$service_Rx_ago="";
	$service_Tx_bytes="";
	$service_Tx_ago="";
	////// 변수 내용 모두 비움 ]]

	if (strpos($result_commend[$k], "CONNECTING") !== false) {

		$stat = "CONNECTING";
		$dbsave_flag = "yes";

		$result_msg = preg_match_all("|\[(.*?)\]|", $result_commend[$k], $result_text);
		$service_emailid = $result_text[1][2]; // 접속자의 아이디(이메일주소)
		$result_msg = preg_match_all("|\.\.\.(.*?)\[|", $result_commend[$k], $result_text); // 접속자의 공인IP
		$service_ip = $result_text[1][0]; // 접속자의 공인IP
		$FINALDATA = $stat."œ".$service_emailid."œ".""."œ".$service_ip."œ".""."œ".""."œ".""."œ"."";

		$FINALDATA[] = $FINALDATA;
//		echo "<br>FINALDATA(C) = ".$FINALDATA."<br>";

	} else if (strpos($result_commend[$k], "ESTABLISHED") !== false) {

		$stat = "ESTABLISHED";
		$dbsave_flag = "yes";

		if (strpos($result_commend[$k+1], "Remote EAP identity:") !== false) {

			$result_msg = preg_match_all("|Remote EAP identity:(.*?)Ž|", $result_commend[$k+1], $result_text);
			$service_emailid = trim($result_text[1][0]); // 접속자의 아이디(이메일주소)

			$result_msg = preg_match_all("|\.\.\.(.*?)\[|", $result_commend[$k], $result_text);
			$service_ip = trim($result_text[1][0]); // 접속자의 공인IP

			$result_text = explode(",",$result_commend[$k+5]);
			for ($z=1; $z<=4; $z++) {
				$result_text_tmp = explode(" ",trim($result_text[$z]));
				$result_text[$z] = $result_text_tmp[0];
			}
			$service_Rx_bytes = trim($result_text[1]); // 접속자의 IN(Rx) 네트워크 사용량
			$service_Rx_ago = trim($result_text[2]); // 접속자의 IN(Rx) 네트워크 마지막 전송된 시간 (몇초전)
			$service_Tx_bytes = trim($result_text[3]); // 접속자의 OUT(Tx) 네트워크 사용량
			$service_Tx_ago = trim($result_text[4]); // 접속자의 OUT(Tx) 네트워크 마지막 전송된 시간 (몇초전)

			$result_msg = preg_match_all("|===(.*?)/32Ž|", $result_commend[$k+6], $result_text);
			$service_vdhcp_id = trim($result_text[1][0]); // 접속자에게 할당된 가상 DHCP IP

			$FINALDATA = $stat."œ".$service_emailid."œ".$service_vdhcp_id."œ".$service_ip."œ".$service_Rx_bytes."œ".$service_Rx_ago."œ".$service_Tx_bytes."œ".$service_Tx_ago;

			$FINALDATA_ARR[] = $FINALDATA;
//			echo "<br>FINALDATA(A) = ".$FINALDATA."<br>";

		} else {

			$result_msg = preg_match_all("|\[(.*?)\]|", $result_commend[$k], $result_text);
			$service_emailid = $result_text[1][2]; // 접속자의 아이디(이메일주소)

			$result_msg = preg_match_all("|\.\.\.(.*?)\[|", $result_commend[$k], $result_text); // 접속자의 공인IP
			$service_ip = $result_text[1][0]; // 접속자의 공인IP

			$result_text = explode(",",$result_commend[$k+4]);
			for ($z=1; $z<=4; $z++) {
				$result_text_tmp = explode(" ",trim($result_text[$z]));
				$result_text[$z] = $result_text_tmp[0];
			}
			$service_Rx_bytes = trim($result_text[1]); // 접속자의 IN(Rx) 네트워크 사용량
			$service_Rx_ago = trim($result_text[2]); // 접속자의 IN(Rx) 네트워크 마지막 전송된 시간 (몇초전)
			$service_Tx_bytes = trim($result_text[3]); // 접속자의 OUT(Tx) 네트워크 사용량
			$service_Tx_ago = trim($result_text[4]); // 접속자의 OUT(Tx) 네트워크 마지막 전송된 시간 (몇초전)

			$result_msg = preg_match_all("|===(.*?)/32Ž|", $result_commend[$k+5], $result_text);
			$service_vdhcp_id = trim($result_text[1][0]); // 접속자에게 할당된 가상 DHCP IP

			$FINALDATA = $stat."œ".$service_emailid."œ".$service_vdhcp_id."œ".$service_ip."œ".$service_Rx_bytes."œ".$service_Rx_ago."œ".$service_Tx_bytes."œ".$service_Tx_ago;

			$FINALDATA_ARR[] = $FINALDATA;
//			echo "<br>FINALDATA(B) = ".$FINALDATA."<br>";

		}

	}


	///// 현재 상태를 DB에 INSERT [[
	if (trim($stat)!="" && $dbsave_flag == "yes") {
		$QUERY_SET_FINAL = "stat='$stat',service_emailid='$service_emailid',service_vdhcp_id='$service_vdhcp_id',service_ip='$service_ip',service_Rx_bytes='$service_Rx_bytes',service_Rx_ago='$service_Rx_ago',service_Tx_bytes='$service_Tx_bytes',service_Tx_ago='$service_Tx_ago'";

		$QUERY_STRING = "INSERT `$table_name_target_1` SET " . $QUERY_SET_FINAL;
		$query_result = $DB_TARGET_1->query($QUERY_STRING);
		if ($query_result) {

		}

	}
	///// 현재 상태를 DB에 INSERT [[

}



///// 사용(관리)자가 요구한 정렬방식으로 출력해서 ajax호출한 곳으로 echo 해줌 [[
switch ($REQUEST['sort']) {

	case "dhcpasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_vdhcp_id ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "dhcpdesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_vdhcp_id DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	case "statasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY stat ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "statdesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY stat DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	case "emailidasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_emailid ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "emailiddesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_emailid DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	case "ipasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_ip ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "ipdesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_ip DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	case "txbytesasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_tx_bytes ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "txbytesdesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_tx_bytes DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	case "txagoasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_tx_ago ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "txagodesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_tx_ago DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	case "rxbytesasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_rx_bytes ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "rxbytesdesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_rx_bytes DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	case "rxagoasc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_rx_ago ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;
	case "rxagodesc":
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_rx_ago DESC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
		break;


	default :
		$query = "SELECT * FROM `$table_name_target_1` ORDER BY service_vdhcp_id ASC";
		$result = $DB_TARGET_1->query($query);
		$total = mysqli_num_rows($result);

		if ($total > 0) {
			while( $row = mysqli_fetch_array($result) ) {
				echo $row['no']."|".$row['stat']."|".$row['service_emailid']."|".$row['service_vdhcp_id']."|".$row['service_ip']."|".$row['service_rx_bytes']."|".$row['service_rx_ago']."|".$row['service_tx_bytes']."|".$row['service_tx_ago']."Œ";
			}
		}
}


?>