#!/usr/bin/ruby
##
# SkgDB RANK出力ツール
# ファイル名：keishin_rank2018.rb
# 作成日：2018/04/02
# 更新日：2018/04/02
#
# Copyright (C) 2008-2018 Japan Multimedia Equipment, inc. All Rights Reserved.
##


##
# ライブラリ読込み
##

## 標準ライブラリ読込み
require 'kconv'
require 'cgi'
## ユーザライブラリ読込み
require './lib/mysql_connect'


##
# 初期変数設定
##

## データベース変数設定
db_host = "localhost"
db_uid  = "root"
db_pass = "47qg*E5H&mxQ"
db_name = "jmedb_rank"

rank_p  = nil
rank_x1 = nil
rank_x2 = nil
rank_y  = nil
rank_z  = nil
rank_w  = nil

num_h = nil
num_k = nil

error = nil


##
# HTTP POST取得
##
req = CGI.new

pref     = req.params['pref'][0] != nil ? req.params['pref'][0] : nil
ctype    = req.params['ctype'][0] != nil ? req.params['ctype'][0] : nil
score_p  = req.params['score_p'][0] != nil ? req.params['score_p'][0] : nil
score_x1 = req.params['score_x1'][0] != nil ? req.params['score_x1'][0] : nil
score_x2 = req.params['score_x2'][0] != nil ? req.params['score_x2'][0] : nil
score_y  = req.params['score_y'][0] != nil ? req.params['score_y'][0] : nil
score_z  = req.params['score_z'][0] != nil ? req.params['score_z'][0] : nil
score_w  = req.params['score_w'][0] != nil ? req.params['score_w'][0] : nil


# 取得値チェック
# コードチェック関数
def check_code(val)
  if /^[0-9]{1,2}$/ !~ val
    val = nil
  end
  return val
end
# スコアチェック関数
def check_score(val)
  if val != nil
    val = val.to_s.tr('０-９', '0-9')
    val = val.gsub(/,/, '')
    if /^[0-9]*$/ !~ val
      val = nil
    end
  end
  return val
end

# チェック処理
pref  = check_code(pref)
pref_num = pref.to_i
pref  = pref != nil && pref_num >= 0 && pref_num <= 47 ? sprintf("%02d", pref_num) : nil

ctype = check_code(ctype)
ctype_num = ctype.to_i
ctype = ctype != nil && ctype_num >= 1 && ctype_num <= 33 ? sprintf("%02d", ctype_num) : nil

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` >= '2016' "
if pref == '00'
  sql << "AND HI.`license_code` = '00'"
else
  sql << "AND HI.`city_code` LIKE '#{pref}%'"
end
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` >= '2016' "
sql << "AND KK.`ctype_code` = '#{ctype}' "
if pref == '00'
  sql << "AND KI.`license_code` = '00'"
else
  sql << "AND KI.`city_code` LIKE '#{pref}%'"
end
sql << ") AS NK"
if score_p != nil && ctype != nil
  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` >= '2016' "
  sql << "AND PK.`ctype_code` = '#{ctype}' "
  if pref == '00'
    sql << "AND PI.`license_code` = '00'"
  else
    sql << "AND PI.`city_code` LIKE '#{pref}%' "
  end
  sql << "AND PK.`score_p` >#{score_p}"
  sql << ") AS P"
end
if score_x1 != nil && ctype != nil
  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` >= '2016' "
  sql << "AND X1K.`ctype_code` = '#{ctype}' "
  if pref == '00'
    sql << "AND X1I.`license_code` = '00'"
  else
    sql << "AND X1I.`city_code` LIKE '#{pref}%' "
  end
  sql << "AND X1K.`score_x1` >#{score_x1}"
  sql << ") AS X1"
end
if score_x2 != nil
  sql << ",(SELECT COUNT( * ) + 1 AS `RANK_X2` "
  sql << "FROM `corp_score` AS X2I "
  sql << "WHERE X2I.`year_div` >= '2016' "
  if pref == '00'
    sql << "AND X2I.`license_code` = '00'"
  else
    sql << "AND X2I.`city_code` LIKE '#{pref}%' "
  end
  sql << "AND X2I.`score_x2` >#{score_x2}"
  sql << ") AS X2"
end
if score_y != nil
  sql << ",(SELECT COUNT( * ) + 1 AS `RANK_Y` "
  sql << "FROM `corp_score` AS YI "
  sql << "WHERE YI.`year_div` >= '2016' "
  if pref == '00'
    sql << "AND YI.`license_code` = '00'"
  else
    sql << "AND YI.`city_code` LIKE '#{pref}%' "
  end
  sql << "AND YI.`score_y` >#{score_y}"
  sql << ") AS Y"
end
if score_z != nil && ctype != nil
  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` >= '2016' "
  sql << "AND ZK.`ctype_code` = '#{ctype}' "
  if pref == '00'
    sql << "AND ZI.`license_code` = '00'"
  else
    sql << "AND ZI.`city_code` LIKE '#{pref}%' "
  end
  sql << "AND ZK.`score_z` >#{score_z}"
  sql << ") AS Z"
end
if score_w != nil
  sql << ",(SELECT COUNT( * ) + 1 AS `RANK_W` "
  sql << "FROM `corp_score` AS WI "
  sql << "WHERE WI.`year_div` >= '2016' "
  if pref == '00'
    sql << "AND WI.`license_code` = '00'"
  else
    sql << "AND WI.`city_code` LIKE '#{pref}%' "
  end
  sql << "AND WI.`score_w` >#{score_w}"
  sql << ") AS W"
end

rec = Array.new

if pref != nil
  
  ## DB接続
  jmedb = Mysql_Connect.new(db_host, db_uid, db_pass, db_name)
  if jmedb.connect
    rec = jmedb.query(sql)
    jmedb.close
  else
    error = "#{jmedb.error}"or
    exit
  end
else
  error = "POST Data Syntax Error."
end

rank_xml = ''
rank_xml << "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"

rank_xml << "<Construction_Score>"
if rec.length != 0
  rank_p  = rec[0]['RANK_P'] != nil ? rec[0]['RANK_P'] : nil
  rank_x1 = rec[0]['RANK_X1'] != nil ? rec[0]['RANK_X1'] : nil
  rank_x2 = rec[0]['RANK_X2'] != nil ? rec[0]['RANK_X2'] : nil
  rank_y  = rec[0]['RANK_Y'] != nil ? rec[0]['RANK_Y'] : nil
  rank_z  = rec[0]['RANK_Z'] != nil ? rec[0]['RANK_Z'] : nil
  rank_w  = rec[0]['RANK_W'] != nil ? rec[0]['RANK_W'] : nil

  num_h = rec[0]['NUM_H'] != nil ? rec[0]['NUM_H'] : nil
  num_k = rec[0]['NUM_K'] != nil ? rec[0]['NUM_K'] : nil

  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>"
end

rank_xml << "<\/Construction_Score>"

print "Content-type: text/xml\n\n"
print rank_xml
exit