rakefile

require 'Net/http'
require 'zip'
require 'sqlite3'
require 'rexml/document'
include REXML

namespace :dinesafe do

    desc "Downloads the Dine Safe API"
    task :fetch do
        uri = URI('http://opendata.toronto.ca/public.health/dinesafe/dinesafe.zip')
        zipped_folder = Net::HTTP.get(uri)

        File.open('dinesafe.zip', 'wb') do |file|
            file.write(zipped_folder)
        end
    end

    desc 'Extracts the DineSafe ZIP File into resources\\dinesafe.xml'
    task :extract do
        zip_file = Zip::File.open('dinesafe.zip')
        zip_file.each do |file|
            if file.name == 'dinesafe.xml'
                fpath = File.join(File.dirname(__FILE__) + '\\resources', file.name)
                file.extract(fpath) {true}
            end
        end
    end

    desc 'Creates sql Database with DineSafe.xml'
    task :create do
        file = File.new("resources/dinesafe.xml")
        doc = Document.new(file)
        db =SQLite3::Database.new('dinesafe.db')
        drop = "DROP TABLE IF EXISTS Establishment"
        db.execute(drop)
        create = %{CREATE TABLE Establishment (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            establishment_id INTEGER,
            inspection_id INTEGER,
            establishment_name TEXT,
            establishment_type TEXT,
            establishment_address TEXT,
            latitude REAL,
            longitude REAL,
            establishment_status TEXT,
            minimum_inspections_per_year INTEGER,
            infraction_details  TEXT,
            inspection_date DATE,
            severity TEXT,
            action TEXT,
            court_outcome TEXT,
            amount_fined TEXT
        )}
        db.execute(create)
        doc.root.elements.each('ROW') do |node|
            sql_insert = %Q(INSERT INTO Establishment (
                establishment_id,
                inspection_id,
                establishment_name,
                establishment_type,
                establishment_address,
                latitude,
                longitude,
                establishment_status,
                minimum_inspections_per_year,
                infraction_details,
                inspection_date,
                severity,
                action,
                court_outcome,
                amount_fined
                )
            VALUES (
                #{XPath.match(node, 'ESTABLISHMENT_ID/text()')[0]},
                #{XPath.match(node,'INSPECTION_ID/text()')[0]},
                "#{XPath.match(node,'ESTABLISHMENT_NAME/text()')[0]}",
                "#{XPath.match(node,'ESTABLISHMENTTYPE/text()')[0]}",
                "#{XPath.match(node,'ESTABLISHMENT_ADDRESS/text()')[0]}",
                #{XPath.match(node,'LATITUDE/text()')[0]},
                #{XPath.match(node,'LONGITUDE/text()')[0]},
                "#{XPath.match(node,'ESTABLISHMENT_STATUS/text()')[0]}",
                #{XPath.match(node,'MINIMUM_INSPECTIONS_PERYEAR/text()')[0]},
                "#{XPath.match(node,'INFRACTION_DETAILS/text()')[0]}",
                #{XPath.match(node,'INSPECTION_DATE/text()')[0]},
                "#{XPath.match(node,'SEVERITY/text()')[0]}",
                "#{XPath.match(node,'ACTION/text()')[0]}",
                "#{XPath.match(node,'COURT_OUTCOME/text()')[0]}",
                "#{XPath.match(node,'AMOUNT_FINED/text()')[0]}"
            ))
            #puts sql_insert
            db.execute(sql_insert)
        end
    end
end