#!/usr/bin/env ruby
#    This file is part of LCR-AGI.
#
#    LCR-AGI is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    LCR-AGI is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with LCR-AGI.  If not, see <http://www.gnu.org/licenses/>.
#
# Copyright (C) 2009 Alkaloid Networks LLC
# Author: Ben Klang <ben@alkaloid.net>
# Alkaloid Networks LLC - http://projects.alkaloid.net
# $Id$

require 'config'
require 'rubygems'
require 'parseexcel'
require 'dbi'
require 'logger'

uri = ['DBI', 'Mysql', $database[:dbname], $database[:host]].join(':')
dbh = DBI.connect(uri, $database[:user], $database[:pass])

$logger = Logger.new($logfile)
$logger.level = $loglevel
$logger.formatter = Logger::Formatter.new

# http://www.nanpa.com/reports/reports_npa.html
# http://www.nanpa.com/nas/public/npasInServiceByLocationReport.do?method=displayNpasInServiceByLocationReport

# The link above has a button that POSTs and empty form to
# http://www.nanpa.com/nas/public/downloadNpasInServiceByLocationReport.do;nanpaid=PMjdKzyKGTLSnndTmzp6ntB2yHp6nSNLd9M61Vv6jkgWDX2Qy7CF!1966113721?method=downloadExcelNpasInServiceByLocationReport
# ... but it doesn't seem to mind wget either.

if (ARGV[0].nil? || !File.exists?(ARGV[0]))
    puts "Must supply NANPA .xls file as sole argument."
end

workbook = Spreadsheet::ParseExcel.parse(ARGV[0])

worksheet = workbook.worksheet(0)

# Pass in the number of rows to skip to each()
locations = {}
sth = dbh.prepare("INSERT INTO areacodes (location, npa) VALUES (?, ?)")
worksheet.each(1){|row|
    location = row[0].to_s('latin1')
    npa = row[1].to_i

    # The following code will arrange the area codes into a hash with the
    # locations as the key and an array of area codes as the value.
    #if (locations[location].nil?)
    #    locations[location] = []
    #end
    #locations[location].push(row[1].to_i)

    sth.execute(location, npa)
}