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

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

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

db=SQLite3::Database.new("viewer.sq3")

db.execute("CREATE TABLE IF NOT EXISTS " +
           "mapdate( name text primary key, period text, address text, season text, viewer text, latlng text, type text)")

thiscgi = "./"+File.basename($0)

def head()
print <<__EOM__
<!DOCTYPE html>
<html lang="ja">
<head>
<title>make GeoJSON</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
</head>
<body>
__EOM__
end

def body()
print <<__EOM__
</body>
</html>
__EOM__
end

if mode == ""#htmlに表示するやつを書く
head()
  print <<__EOM__
 <h1>データベースから抽出andデータ作成</h1>
 <form method="POST" action="#{thiscgi}"  enctype="multipart/form-data">
 <p>条件指定: <input  name="cond" length="20"></p>
 <p><input type="submit" value="OK"></p>
 </form>
__EOM__
  body()
else
  # ok push後こっちにくる。この辺でgeojsonを作る
  head()
  cond  = CGI.escapeHTML(cgi["cond"].read)
  if cond = "" ##formに何もなかったら場合の処理
    dbn = db.execute("SELECT * from mapdate3 WHERE viewer=1")
  else
    dbn = db.execute("SELECT * from mapdate3 WHERE viewer=?", cond)
  end

  hash_leng =  dbn.length #データの抽出量を数える
  hash = {"type"=>"FeatureCollection","features" => [{"type" =>"Feature","properties"=>{"name"=>dbn[0][0],"description"=>dbn[0][1]},"geometry" =>{"type" => dbn[0][6],"coordinates"=>[dbn[0][5]]}}]}

  if hash_leng >= 2 ###データの抽出が2個以上ある時の処理
    i = 1
    u = 1
    for hoge2 in 1..hash_leng - 1
      hash["features"][i] =  {"type" =>"Feature","properties"=>{"name"=>dbn[u][0],"description"=>dbn[u][1]},"geometry" =>{"type" => dbn[u][7],"coordinates"=>[dbn[u][5].to_f,dbn[u][6].to_f]}}
      i += 1
      u += 1
    end
  end

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

 body()
end




#insert into mapdate (name,period,address,season,viewer,latlng,type)values('hoge','ho','sakata','aki','1','138,39','point');
