I have a csv of zip code and time zone like so.
array_zipcodes
''ZIPCode,ZIPType,CityName,CityType,CountyName,CountyFIPS,StateName,StateAbbr,StateFIPS,MSACode,AreaCode,TimeZone,UTC,DST,Latitude,Longitude
00501,U,Holtsville,D,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813078, -73.046388
00501,U,I R S Service Center,N,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813078, -73.046388
00544,U,Holtsville,D,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813223, -73.049288
00544,U,IRS Service Center,N,Suffolk,36103,New York,NY,
36,5380,631,Eastern,-5.0,Y, 40.813223, -73.049288''
I want a hash like
zip_to_UTC = [
00501 => -5,
00544 => -5,]
Then I am interacting with salesforce API to grab customer data and
figure out all their zip codes and put them through this to generate
SQL update statement.
for your pleasure if you want to know how to grab salesforce data. I
made my own hash for State to UTC offset, but I need it to be zip to
UTC as that is more accurate. Any help very much appreciated.
require ''rubygems''
require ''activerecord''
require ''activesalesforce''
require ''fastercsv'' ## this is what I have so far.
FIELDS = [ "ZIPCode","UTC"]
csv_data = FasterCSV.read("5-digit Commercial.csv")
class Account < ActiveRecord::Base
# end
#
# module DiiTimeZone
# module SF
# class TZLookup
# def initialize(username, password)
# ActiveRecord::Base.establish_connection({:adapter =>
''activesalesforce'', :url =>
''https://www.salesforce.com/services/Soap/
u/7.0'',
# :username =>
username, :password => password})
# @connection = ActiveRecord::Base.connection
# end
#
# def get_address(jobid)
# result = nil
# state_to_UTC = {
# "AL" => "-06",
# "AK" => "-09",
# "AS" => "-11",
# "AZ" => "-07",
# "AR" => "-06",
# "CA" => "-08",
# "CO" => "-07",
# "CT" => "-05",
# "DE" => "-05",
# "FL" => "-05",
# "GA" => "-05",
# "GU" => "+10",
# "HI" => "-10",
# "ID" => "-07",
# "IL" => "-06",
# "IN" => "-05",
# "IA" => "-06",
# "KS" => "-06",
# "KY" => "-05",
# "LA" => "-06",
# "ME" => "-05",
# "MD" => "-05",
# "MA" => "-05",
# "MI" => "-05",
# "MN" => "-06",
# "MS" => "-06",
# "MO" => "-06",
# "MT" => "-07",
# "NE" => "-06",
# "NV" => "-08",
# "NH" => "-05",
# "NJ" => "-05",
# "NM" => "-07",
# "NY" => "-05",
# "NC" => "-05",
# "ND" => "-06",
# "MP" => "+10",
# "OH" => "-05",
# "OK" => "-06",
# "OR" => "-08",
# "PA" => "-05",
# "PR" => "-04",
# "RI" => "-05",
# "SC" => "-05",
# "SD" => "-06",
# "TN" => "-06",
# "UT" => "-07",
# "VT" => "-05",
# "VA" => "-05",
# "VI" => "-04",
# "WA" => "-08",
# "DC" => "-05",
# "WV" => "-05",
# "WI" => "-06",
# "WY" => "-07"
# }
# if (sfaccount = Account.find_by_dii_jobid__c(jobid.to_s))
# result = {:jobid => sfaccount[:dii_jobid__c], :utcoffset
=> state_to_UTC[sfaccount[:billing_state]]}
# if(result[:utcoffset]!=nil && result[:jobid]!=nil)
# @@updatestatement += "UPDATE JOB SET
TIMEZONE_OFFSET_FROM_UTC = " +
"''#{result[:utcoffset]}''"+ " WHERE
JOBID = " + "''#{result[:jobid]}''; \n"
# end
# end
# result
# end
#
# end
# end
# end
# jobids = [
# ''49241'',
# ''49242'',
# ''80745'',
# ''80752'',] ## this is actually about a thousand entries
larger
#
# @@updatestatement = ""
# sf =
DiiTimeZone::SF::TZLookup.new(''jmontross-VgLEMrDEpOOJ8c2fQYRYNw@public.gmane.org'',
blankedout'')
# @@jobid = ''72887''
# puts jobids.length
# jobids.each { |jobids | "#{sf.get_address(jobids)}" }
# # p sf.get_address(@@jobid)
#
# puts @@updatestatement