<?php
/* 初期設定 */
$db_host = "localhost";
$db_uid  = "root";
$db_pass = "47qg*E5H&mxQ";
$db_name = "jmedb_rank";

$year_div = '2018';
$year_limit = date("Y-m-d", strtotime("-19 month"));


/* 変数初期化 */
$rank_p  = null;
$rank_x1 = null;
$rank_x2 = null;
$rank_y  = null;
$rank_z  = null;
$rank_w  = null;

$num_h = null;
$num_k = null;

$error = null;

$pref = null;
$ctype = null;
$score_p = null;
$score_x1 = null;
$score_x2 = null;
$score_y = null;
$score_z = null;
$score_w = null;


/* HTTP POST取得 */
if(isset($_POST['pref'])) $pref = $_POST['pref'];
if(isset($_POST['ctype'])) $ctype = $_POST['ctype'];
if(isset($_POST['score_p'])) $score_p = $_POST['score_p'];
if(isset($_POST['score_x1'])) $score_x1 = $_POST['score_x1'];
if(isset($_POST['score_x2'])) $score_x2 = $_POST['score_x2'];
if(isset($_POST['score_y'])) $score_y = $_POST['score_y'];
if(isset($_POST['score_z'])) $score_z = $_POST['score_z'];
if(isset($_POST['score_w'])) $score_w = $_POST['score_w'];


/* 型チェック処理 */
$pref  = check_code($pref);
$pref_num = (int)$pref;
$pref  = $pref != null && $pref_num >= 0 && $pref_num <= 47 ? sprintf("%02d", $pref_num) : null;
if($pref == null) $error = "検索条件が正しくありません。";

$ctype = check_code($ctype);
$ctype_num = (int)$ctype;
$ctype = $ctype != null && $ctype_num >= 1 && $ctype_num <= 33 ? sprintf("%02d", $ctype_num) : null;

$score_p  = check_score($score_p);
$score_x1 = check_score($score_x1);
$score_x2 = check_score($score_x2);
$score_y  = check_score($score_y);
$score_z  = check_score($score_z);
$score_w  = check_score($score_w);


/* SQL生成 */
$sql = '';

$sql .= "SELECT * ";
$sql .= "FROM ";
$sql .= "( ";
$sql .= "SELECT COUNT( * ) AS `NUM_H` ";
$sql .= "FROM `corp_score` AS HI ";
$sql .= "WHERE HI.`year_div` >= '${year_div}' ";
if($pref == '00') {
  $sql .= "AND HI.`license_code` = '00'";
} else {
  $sql .= "AND HI.`city_code` LIKE '${pref}'";
}
$sql .= ") AS NH";
$sql .= ",(SELECT COUNT( * ) AS `NUM_K` ";
$sql .= "FROM `corp_score` AS KI, `const_score` AS KK ";
$sql .= "WHERE KI.`hder` = KK.`hder` ";
$sql .= "AND KI.`year_div` >= '${year_div}' ";
$sql .= "AND KK.`ctype_code` = '${ctype}' ";
if($pref == '00') {
  $sql .= "AND KI.`license_code` = '00'";
} else {
  $sql .= "AND KI.`city_code` LIKE '${pref}%'";
}
$sql .= ") AS NK";
if ($score_p != null && $ctype != null) {
  $sql .= ",(SELECT COUNT( * ) + 1 AS `RANK_P` ";
  $sql .= "FROM `corp_score` AS PI, `const_score` AS PK ";
  $sql .= "WHERE PI.`hder` = PK.`hder` ";
  $sql .= "AND PI.`year_div` >= '${year_div}' ";
  $sql .= "AND PK.`ctype_code` = '${ctype}' ";
  if($pref == '00') {
    $sql .= "AND PI.`license_code` = '00'";
  } else {
    $sql .= "AND PI.`city_code` LIKE '${pref}%' ";
  }
  $sql .= "AND PK.`score_p` > ${score_p}";
  $sql .= ") AS P";
}
if($score_x1 != null && ctype != null) {
  $sql .= ",(SELECT COUNT( * ) + 1 AS `RANK_X1` ";
  $sql .= "FROM `corp_score` AS X1I, `const_score` AS X1K ";
  $sql .= "WHERE X1I.`hder` = X1K.`hder` ";
  $sql .= "AND X1I.`year_div` >= '${year_div}' ";
  $sql .= "AND X1K.`ctype_code` = '${ctype}' ";
  if($pref == '00') {
    $sql .= "AND X1I.`license_code` = '00'";
  } else {
    $sql .= "AND X1I.`city_code` LIKE '${pref}%' ";
  }
  $sql .= "AND X1K.`score_x1` > ${score_x1}";
  $sql .= ") AS X1";
}
if($score_x2 != null) {
  $sql .= ",(SELECT COUNT( * ) + 1 AS `RANK_X2` ";
  $sql .= "FROM `corp_score` AS X2I ";
  $sql .= "WHERE X2I.`year_div` >= '${year_div}' ";
  if($pref == '00') {
    $sql .= "AND X2I.`license_code` = '00'";
  } else {
    $sql .= "AND X2I.`city_code` LIKE '${pref}%' ";
  }
  $sql .= "AND X2I.`score_x2` > ${score_x2}";
  $sql .= ") AS X2";
}
if($score_y != null) {
  $sql .= ",(SELECT COUNT( * ) + 1 AS `RANK_Y` ";
  $sql .= "FROM `corp_score` AS YI ";
  $sql .= "WHERE YI.`year_div` >= '${year_div}' ";
  if($pref == '00') {
    $sql .= "AND YI.`license_code` = '00'";
  } else {
    $sql .= "AND YI.`city_code` LIKE '${pref}%' ";
  }
  $sql .= "AND YI.`score_y` > ${score_y}";
  $sql .= ") AS Y";
}
if($score_z != null && ctype != null) {
  $sql .= ",(SELECT COUNT( * ) + 1 AS `RANK_Z` ";
  $sql .= "FROM `corp_score` AS ZI, `const_score` AS ZK ";
  $sql .= "WHERE ZI.`hder` = ZK.`hder` ";
  $sql .= "AND ZI.`year_div` >= '${year_div}' ";
  $sql .= "AND ZK.`ctype_code` = '${ctype}' ";
  if($pref == '00') {
    $sql .= "AND ZI.`license_code` = '00'";
  } else {
    $sql .= "AND ZI.`city_code` LIKE '${pref}%' ";
  }
  $sql .= "AND ZK.`score_z` > ${score_z}";
  $sql .= ") AS Z";
}
if($score_w != null) {
  $sql .= ",(SELECT COUNT( * ) + 1 AS `RANK_W` ";
  $sql .= "FROM `corp_score` AS WI ";
  $sql .= "WHERE WI.`year_div` >= '${year_div}' ";
  if($pref == '00') {
    $sql .= "AND WI.`license_code` = '00'";
  } else {
    $sql .= "AND WI.`city_code` LIKE '${pref}%' ";
  }
  $sql .= "AND WI.`score_w` > ${score_w}";
  $sql .= ") AS W";
}

/* DB接続 */
$db = mysqli_connect($db_host, $db_uid, $db_pass) or $error = "DBへの接続に失敗しました。>";
if($error == null) mysqli_select_db($db, $db_name) or $error = "DBの選択に失敗しました。";
// mysqli_query($db, "SET NAMES utf8mb4");

/* SQLクエリ処理 */
if($error == null) $rs_rank = mysqli_query($db, $sql) or $error = '順位算出クエリが失敗しました。:'.$sql;
if($error == null) {
  while($row = mysqli_fetch_assoc($rs_rank)) {
    $num_h   = $row['NUM_H'];
    $num_k  = $row['NUM_K'];
    $rank_p  = $row['RANK_P'];
    $rank_x1 = $row['RANK_X1'];
    $rank_x2 = $row['RANK_X2'];
    $rank_y = $row['RANK_Y'];
    $rank_z = $row['RANK_Z'];
    $rank_w = $row['RANK_W'];
  }
  mysqli_free_result($rs_rank);
}


/* XML生成 */
$rank_xml = '';
$rank_xml .= "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";

$rank_xml .= "<Construction_Score>";
if($error == null) {
  $rank_xml .= "<Rank>";
  $rank_xml .= "<Population>";
  $rank_xml .= "<Pref>${num_h}</Pref>";
  $rank_xml .= "<Type>${num_k}</Type>";
  $rank_xml .= "</Population>";
  $rank_xml .= "<Score_P>${rank_p}</Score_P>";
  $rank_xml .= "<Score_X1>${rank_x1}</Score_X1>";
  $rank_xml .= "<Score_X2>${rank_x2}</Score_X2>";
  $rank_xml .= "<Score_Y>${rank_y}</Score_Y>";
  $rank_xml .= "<Score_Z>${rank_z}</Score_Z>";
  $rank_xml .= "<Score_W>${rank_w}</Score_W>";
  $rank_xml .= "</Rank>";
} else {
  $rank_xml .= "<Error>${error}</Error>";
}
$rank_xml .= "</Construction_Score>";


/* XML出力・終了処理 */
header("Content-type: text/xml");
echo $rank_xml;

exit();


/* 取得値チェック */
/* コードチェック関数 */
function check_code($val) {
  if(!preg_match('/^[0-9]{1,2}$/', $val)){
    $val = null;
  }
  return $val;
}
/* スコアチェック関数 */
function check_score($val) {
  if($val != null) {
    $val = mb_convert_kana($val, "n");;
    $val = str_replace(',', '', $val);
    if(!preg_match('/^[0-9]*$/', $val)) {
      $val = null;
    }
  }
  return $val;
}
?>