#!/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>")