#!/usr/bin/env ruby
# coding: utf-8

require 'sqlite3'
require 'cgi'
require 'json'
require 'csv'

cgi = CGI.new(:accept_charset => "UTF-8")
print "Content-type: text/html; charset=UTF-8\n\n"
mode = cgi['mode']

db=SQLite3::Database.new("db/viewer_prc.sq3")
thiscgi = "./"+File.basename($0)

def cat(file)
  open(file, "r"){|f| print f.read}
end

def header()
  cat "header.html"
end

def footer()
  cat "footer.html"
end


if mode == ""#htmlに表示するやつを書く
  header()

  print <<__EOM__
 <h1>データベースから抽出andデータ作成</h1>

 <form method="POST" action="#{thiscgi}"  enctype="multipart/form-data">


<p>条件指定<select name="cond2"></p>
__EOM__
#<p>条件指定: <input  name="cond" length="20"></p>
  db.execute("SELECT maptype, mapname FROM mapname") do |row|
    printf(<<__EOM__, CGI.escapeHTML(row[0]), CGI.escapeHTML(row[1]))
    <option value="%s">%s</option>
__EOM__
  end

    print <<__EOM__
</select>
    <p><input type="submit" value="OK"></p>
 </form>

__EOM__
  footer()
else
  # ok push後こっちにくる。この辺でgeojsonを作る
  header()
#  cond  = CGI.escapeHTML(cgi["cond"].read)
  cond2  = CGI.escapeHTML(cgi["cond2"].read)
  #if cond = "" ##formに何もなかったら場合の処理
  printf("%s",cond2)
  dbn = db.execute(<<-_EOS_, cond2.to_s)
	SELECT  mapitem.ename,
		mapitem.name,
		mapitem.addr,
		mapitem.lat,
		mapitem.lon,
		object.objtype,
		object.jsonval,
		maptype.maptype,
		group_concat(attribute.attr  || ',' ||attribute.value)
	FROM	mapitem JOIN attribute JOIN object JOIN maptype
	     ON mapitem.ename == attribute.ename
		AND mapitem.ename == object.ename
		AND mapitem.ename == maptype.ename
		WHERE maptype.maptype == ?
	GROUP BY maptype.maptype
	_EOS_
  if dbn[0][5] == "Point"
    hoge1 = Array.new
    hoge1 = dbn[0][6].split(",")
    hash = {
      "type"=>"FeatureCollection",
      "features" => [{
          "type" =>"Feature",
          "properties"=>{
            "name" => dbn[0][1],
            "address"=>dbn[0][2],
            "maptype"=>dbn[0][7]},
          "geometry" =>{
            "type" => dbn[0][5],
            "coordinates" => [hoge1[0].to_f,hoge1[1].to_f]}}]}
  else
    ##Point以外の時の処理
    hash = {
      "type" => "FeatureCollection",
      "features" => [{
          "type" =>"Feature",
          "properties" => {
            "name"=>dbn[0][1],
            "address"=>dbn[0][2],
            "maptype"=>dbn[0][7]
          },
          "geometry" => {
            "type" => dbn[0][5],
            "coordinates"=>[dbn[0][6]]}}]}
  end
### ここまで

##ここから add kye value

hogehoge2 = Array.new
hogehoge3 = Array.new
hogehoge4 = Array.new

hogehoge2 =  dbn[0][8].split(",")
tt = 0
  for t in 0..hogehoge2.length - 1
    if tt % 2 == 0 #あまりが0 == 偶数
      hogehoge3[0] = hogehoge2[0]
    else
      hogehoge4[0] = hogehoge2[1]
    end
    tt += 1
  end

  for g in 0.. hogehoge3.length - 1
    hash["features"][0]["properties"][hogehoge3[0]] = hogehoge4[0]
  end
  ###ここまで

  hash_leng =  dbn.length #データの抽出量を数える

  if hash_leng >= 2 ###データの抽出が2個以上ある時の処理
    i = 1
    #u = 1
    for hoge2 in 1..hash_leng - 1
    if dbn[i][5] == "Point"
      hoge1 = Array.new
      hoge1 = dbn[i][6].split(",")
      hash["features"][i] = {
          "type" =>"Feature",
          "properties"=>{
            "name"=>dbn[i][1],
            "address"=>dbn[i][2],
            "maptype"=>dbn[i][7]},
          "geometry" =>{
            "type" => dbn[i][5],
            "coordinates"=>[hoge1[0].to_f,hoge1[1].to_f]}}
    else
      #### point以外の処理
      hash["features"][i] = {
          "type" =>"Feature",
          "properties"=>{
            "name"=>dbn[i][1],
            "address"=>dbn[i][2],
            "maptype"=>dbn[i][7]
          },
          "geometry" =>{
            "type" => dbn[i][5],
            "coordinates"=>[dbn[i][6]]}}
    end
      hogehoge2 = Array.new  ##配列リセット
      hogehoge3 = Array.new
      hogehoge4 = Array.new

    hogehoge2 =  dbn[i][8].split(",")
      tt1 = 0
      tt2 = 0
      tt3 = 0
      tt4 = 0
      tt5 = 1

      for t in 0..hogehoge2.length - 1
        if tt1 % 2 == 0 #あまりが0 == 偶数
          hogehoge3[tt2] = hogehoge2[tt4]##kye
          tt2 += 1
          tt4 += 2
        else
        hogehoge4[tt3] = hogehoge2[tt5]##value
          tt3 += 1
          tt5 += 2
        end
      tt1 += 1
      end

      ww = 0
      for g in 0.. hogehoge3.length - 1
        hash["features"][i]["properties"][hogehoge3[ww]] = hogehoge4[ww]
        ww += 1
      end
      i += 1
      # u += 1
    end
  end

  #### GeoJSON write
  open("geojson/test.geojson",'w') do |row|
    geojson = JSON.pretty_generate(hash)
    row.print geojson
     #puts geojson
   # print("geojsonできたよ\n")
  #end
#### ここまで


    ### create kml
    #system("ogr2ogr -f KML geojson/test.KML geojson/test.geojson")
    #print("kmlできたよ\n")
    ### ここまで

    #### gitbucket push GeoJSON
    #system("sh auto_push.sh \"add geojson\"")
    #print("pushされたよ\n")

    geojson  = JSON.pretty_generate(hash)
    print <<__EOM__
<p>条件: #{cond2}</p>
<p> #{geojson}</p>
<p> 以下でダウンロードできます</p>
<a href="geojson/test.geojson" download="test.geojson">ダウンロード</a>
__EOM__
    footer()
  end
end