#!/usr/bin/env ruby
# coding: utf-8
Encoding.default_external = 'utf-8'	# UTF-8のCSVファイルを読むため
require 'cgi'
require 'csv'
require 'kakasi'
require 'sqlite3'
c = CGI.new(:accept_charset => "UTF-8")
csv = CSV.read("gomi.csv", headers:true)
db = SQLite3::Database.new("tmpdb/gomi.sq3")

puts "Content-type: text/html; charset=utf-8"
db.execute_batch(<<~EOF)
	DROP TABLE IF EXISTS gomi;
	CREATE TABLE IF NOT EXISTS gomi(name text,type text,remark text,kana text,kata text,roma text);
    BEGIN;
    EOF

result = []
csv.each do |row|
    values = row.collect{|x| x[-1]}
    values << Kakasi.kakasi('-JH -KH',row["品名"])	# 酒田市 → さかたし
    values << Kakasi.kakasi('-JK -HK',row["品名"])	# さかたし → サカタシ
    values << Kakasi.kakasi('-Ja -Ha -Ka',row["品名"])	# さかたし → sakatashi
    db.execute("INSERT INTO gomi VALUES(?,?,?,?,?,?)", *values)
    trash = c["gomi"]
    db.results_as_hash = true
    result = db.execute("SELECT * FROM gomi where name||kana||kata||roma LIKE '%#{trash}%';")
end
db.execute("END;")

print("
<!DOCTYPE html>
<html>
<head><title>検索結果</title>
<style type=\"text/css\">
<!--
h1 {color: black ; text-align: left}
 tr {color : black ; text-align: center}
 th {background:#e0ffff; color:inherit; text-align: center; padding:7px 5px 5px 5px; }
 td {color : inherit; ; text-align: center;padding:10px 0 5px 5px;}
 -->
</style>
</head>
<body>
")
puts("<h1>検索結果</h1>")
puts("<table border=\"1\">")
result.each{|row|
printf("<tr><th>品名</th><th>ごみ区分</th><th>備考</th></tr><tr><td>%s</td><td>%s</td><td>%s</td></tr>",row["name"], row["type"], row["remark"])}    
puts("
</body>
</html>")

