<?php
header("Access-Control-Allow-Origin: *");
header('Content-Type: application/json');
include "config.php";

$curDate = strtotime('+5 hour 30 minutes');

$state_data="SELECT states.state_name,states.image_icon,exam.state_id, exam.display_order, exam.board, exam.board_image FROM sp_board_exam AS exam LEFT JOIN sp_states AS states ON states.state_id=exam.state_id WHERE exam.status='1' GROUP BY exam.state_id,exam.board ORDER BY exam.update_date DESC";
$statesql=$db->prepare($state_data);
$statesql->execute();

$statearray=array();
while($staterow=$statesql->fetch()){
	$stateid=$staterow['state_id'];
	$statename=$staterow['state_name'];
	$image_icon=$staterow['image_icon'];
	$board=$staterow['board'];
	$board_image=$staterow['board_image'];
	
	if($statename!=''){
		$ret = $statename;
	}	else {
		$ret = $board;
	}

	if($board_image!=''){
		$img_ret=$board_image;
	} else if($stateid!=0){
		$img_ret=$image_icon;
	} else {
		$img_ret="no";
	}

$board_data="SELECT exam.id,exam.for_class, exam.state_id, exam.remark, exam.link, exam.add_by, exam.add_date, exam.status, states.state_name,exam.entity_text FROM sp_board_exam AS exam LEFT JOIN sp_states AS states ON states.state_id=exam.state_id WHERE exam.status='1' AND exam.state_id='$stateid' AND exam.board='$board' ORDER BY exam.update_date DESC";
$boardsql=$db->prepare($board_data);
$boardsql->execute();

$mainarray=array();

while($row=$boardsql->fetch()){

$id=$row['id'];
$for_class=$row['for_class'];
$remark= $row['remark'];
$link= $row['link'];
$add_date=$row['add_date'];
$status=$row['status'];
$state_id = $row['state_id']; 
$state_name=$row['state_name'];
$entity_text=$row['entity_text'];

$array=array("id"=>$id, "for_class"=>$for_class, "link"=>$link, "remark"=>$remark, "add_date"=>$add_date, "status"=>$status, "entity_text"=>$entity_text);
$mainarray[]=$array;
}

$state=array("state_name"=>$ret,"image_icon"=>$img_ret,"board_data"=>$mainarray);
$statearray[]=$state;
}

echo json_encode($statearray);
?>