I'm trying to access some data from our on-site billing server to be populate into a rails app that i'm deploying. I've done some digging about and think Tiny TDS and a rake task is the best way to go but I seem to be getting a bit stuck. The code showed below is just an example and not finished! I keep getting a server name not found in configuration files error.
task :import_customers do
RAILS_HOME = File.expand_path(File.join(File.dirname(__FILE__),"../.."))
RAILS_CONFIG = "#{RAILS_HOME}/config"
require "#{RAILS_CONFIG}/environment"
require 'tiny_tds'
client = TinyTds::Client.new(:username => 'user', :password => 'pass', :host => 'SQLSRVR')
result = client.execute("select sitedetails.siteid, company.id as companyid, sitedetails.shortname, company.name,sitedetails.sitename as [Site Name] from company inner join sitedetails on company.id=sitedetails.id left outer join solutionscustinfo s on sitedetails.siteid=s.siteid left outer join paymentconditions a on s.paymentconditions_id=a.id left outer join company agent on company.agent_id=agent.id left outer join sitecontacts billingcontact on billingcontact.contactid=s.billingcontact_id left outer join package p on p.id=package left outer join tariffnames v on v.tariffcode=isnull(s.lcr_tariff,p.lcr_tariff) left outer join tariffnames d on d.tariffcode=isnull(s.data_tariff,p.data_tariff) left outer join tariffnames m on m.tariffcode=isnull(s.mob_tariff,p.mob_tariff) left outer join (select invoiceaddress from sitedetails group by invoiceaddress) ba on ba.invoiceaddress=sitedetails.siteid left outer join discount on discount.id=isnull(s.discount,p.discount) left outer join billrun on billrun.id=s.bill_run left outer join report_profile on report_profile.id=s.report_profile left outer join account_manager on account_manager.id=company.acctmgr_id where company.is_customer<>0 order by company.name,sitedetails.shortname")
result.each do |row|
puts row
name = row['name']
sitename = row['Site Name']
puts sitename
@company = Company.all
end
end
Sorry for not replying I went about this in a different way in the end.
namespace :abillity do
desc "import customers"
def return_address(siteid)
client = TinyTds::Client.new(:username => 'earth', :password => 'gs500e', :host => '192.168.1.38')
result = client.execute("select Address, Town, County, PostCode from sitedetails WHERE SiteID = '#{siteid}'")
result.each do |row|
if (row['Address'] == nil or row['Town'] == nil or row['County'] == nil or row['PostCode'] == nil)
return "Not Listed"
end
@address = row['Address'] + " " + row['Town'] + " " + row['County'] + " " + row['PostCode']
if (@address == nil)
return "Not Listed"
elsif (@address.strip == "")
return "Not Listed"
else
return @address
end
return ""
end
end
task :import_customers => :environment do
require 'tiny_tds'
require 'rubygems'
client = TinyTds::Client.new(:username => 'earth', :password => 'gs500e', :host => '192.168.1.38')
result = client.execute("select sitedetails.siteid, company.id as companyid, sitedetails.shortname, company.name,sitedetails.sitename as [Site Name],sitedetails.sage_id as [Account No] from company inner join sitedetails on company.id=sitedetails.id left outer join solutionscustinfo s on sitedetails.siteid=s.siteid left outer join paymentconditions a on s.paymentconditions_id=a.id left outer join company agent on company.agent_id=agent.id left outer join sitecontacts billingcontact on billingcontact.contactid=s.billingcontact_id left outer join package p on p.id=package left outer join tariffnames v on v.tariffcode=isnull(s.lcr_tariff,p.lcr_tariff) left outer join tariffnames d on d.tariffcode=isnull(s.data_tariff,p.data_tariff) left outer join tariffnames m on m.tariffcode=isnull(s.mob_tariff,p.mob_tariff) left outer join (select invoiceaddress from sitedetails group by invoiceaddress) ba on ba.invoiceaddress=sitedetails.siteid left outer join discount on discount.id=isnull(s.discount,p.discount) left outer join billrun on billrun.id=s.bill_run left outer join report_profile on report_profile.id=s.report_profile left outer join account_manager on account_manager.id=company.acctmgr_id where company.is_customer<>0 order by company.name,sitedetails.shortname")
result.each do |row|
# First of all check if a company allready exists that has the same name and site name
existing = Company.where(["name = ?",row['name']]).where(["site = ?",row['Site Name']]).limit(1)
if (row['Account No'] == nil or row['Account No'] == "")
row['Account No'] = "N/A"
end
if (existing.first == nil)
# Company does not exist add it
@company = Company.new
@company.name = row['name']
@company.site = row['Site Name']
@company.accountNumber = row['Account No']
@company.address = return_address(row['siteid'])
@company.companytype = "Customer"
if (@company.save! == false)
debugger
end
else
# Existing, make sure acct no and address is up to date
@company = existing[0]
@company.accountNumber = row['Account No']
@company.address = return_address(row['siteid'])
@company.save
end
end
end
end