PDF report generator

reports.rb

# gem install rdbi rdbi-driver-odbc
require "rdbi-driver-odbc"

dbh = RDBI.connect(
  :ODBC,
  :db       => "db_name",
  :username => "db_username",
  :password => "db_password"
  )

# Stores/Branches
branches_hash = []
branches = dbh.execute("SELECT * FROM branches")
branches.as(:Struct).fetch(:all).each do |branch|
  branches_hash << Hash[branch.each_pair.to_a]
end

# Reports
reports_hash = []
reports = dbh.execute("select products.style as 'STYLE', products.colour_name as 'COLOUR', products.size_name as 'SIZE', products.description as 'DESC', stock_quantity as 'STOCK', min_stock_quantity as 'MIN STOCK', sum(min_stock_quantity-stock_quantity) as 'REQUIRED', root_plu as 'ROOT PLU', SIZE_OFFSET from branchstock inner join products on (products.plu = branchstock.plu) inner join branches on (branches.id = branchstock.branch_id) where (stock_quantity < min_stock_quantity) and (min_stock_quantity > 0) and (branches.name = 'LINCOLN') group by style, colour_name, size_name order by style, colour_name, SIZE_OFFSET;")
puts branches.schema
reports.as(:Struct).fetch(:all).each do |report|
  reports_hash << Hash[report.each_pair.to_a]
end

# Disconnect Database before garbage collection
dbh.disconnect

# puts branches_hash
# file_names = branches_hash.map { |b| b[:NAME] }
# puts reports_hash

#File.open("reports.txt", 'w') do |file|
#	file.write reports_hash
#end



# RESULTING REPORT
[{:STYLE=>"UD/SW-SHIRT/PREM/ROYAL-BLUE", :COLOUR=>"SIR FRANCIS HILL COMMUNITY(LN)", :SIZE=>"30", :DESC=>"ROYAL BLUE PREMIUM SWEATSHIRT", :STOCK=>-2, :"MIN STOCK"=>12, :REQUIRED=>14, :"ROOT PLU"=>"00006210", :SIZE_OFFSET=>7}
{:STYLE=>"UD/SW-SHIRT/PREM/ROYAL-BLUE", :COLOUR=>"SIR FRANCIS HILL COMMUNITY(LN)", :SIZE=>"32", :DESC=>"ROYAL BLUE PREMIUM SWEATSHIRT", :STOCK=>-2, :"MIN STOCK"=>11, :REQUIRED=>13, :"ROOT PLU"=>"00006210", :SIZE_OFFSET=>8}
{:STYLE=>"UD/SW-SHIRT/PREM/ROYAL-BLUE", :COLOUR=>"WINCHELSEA PRIMARY SCHOOL (LN)", :SIZE=>"24", :DESC=>"ROYAL BLUE PREMIUM SWEATSHIRT", :STOCK=>3, :"MIN STOCK"=>4, :REQUIRED=>1, :"ROOT PLU"=>"00006227", :SIZE_OFFSET=>4}
{:STYLE=>"UD/SW-SHIRT/PREM/ROYAL-BLUE", :COLOUR=>"WINCHELSEA PRIMARY SCHOOL (LN)", :SIZE=>"30", :DESC=>"ROYAL BLUE PREMIUM SWEATSHIRT", :STOCK=>2, :"MIN STOCK"=>7, :REQUIRED=>5, :"ROOT PLU"=>"00006227", :SIZE_OFFSET=>7}
{:STYLE=>"UD/SW-SHIRT/PREM/ROYAL-BLUE", :COLOUR=>"WINCHELSEA PRIMARY SCHOOL (LN)", :SIZE=>"34", :DESC=>"ROYAL BLUE PREMIUM SWEATSHIRT", :STOCK=>3, :"MIN STOCK"=>4, :REQUIRED=>1, :"ROOT PLU"=>"00006227", :SIZE_OFFSET=>9}
{:STYLE=>"UD\\BOYS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROYA", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"32 (10)", :DESC=>"BOYS FITTED BLAZER WITHROYAL BRAID AND ROYAL LINI", :STOCK=>2, :"MIN STOCK"=>7, :REQUIRED=>5, :"ROOT PLU"=>"00017226", :SIZE_OFFSET=>8}
{:STYLE=>"UD\\BOYS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROYA", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"33 (11)", :DESC=>"BOYS FITTED BLAZER WITHROYAL BRAID AND ROYAL LINI", :STOCK=>-4, :"MIN STOCK"=>6, :REQUIRED=>10, :"ROOTPLU"=>"00017226", :SIZE_OFFSET=>9}
{:STYLE=>"UD\\BOYS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROYA", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"34 (12)", :DESC=>"BOYS FITTED BLAZER WITHROYAL BRAID AND ROYAL LINI", :STOCK=>3, :"MIN STOCK"=>6, :REQUIRED=>3, :"ROOT PLU"=>"00017226", :SIZE_OFFSET=>10}
{:STYLE=>"UD\\BOYS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROYA", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"35 (13)", :DESC=>"BOYS FITTED BLAZER WITHROYAL BRAID AND ROYAL LINI", :STOCK=>1, :"MIN STOCK"=>6, :REQUIRED=>5, :"ROOT PLU"=>"00017226", :SIZE_OFFSET=>11}
{:STYLE=>"UD\\BOYS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROYA", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"36 (14)", :DESC=>"BOYS FITTED BLAZER WITHROYAL BRAID AND ROYAL LINI", :STOCK=>2, :"MIN STOCK"=>6, :REQUIRED=>4, :"ROOT PLU"=>"00017226", :SIZE_OFFSET=>12}
{:STYLE=>"UD\\BOYS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROYA", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"37 (15)", :DESC=>"BOYS FITTED BLAZER WITHROYAL BRAID AND ROYAL LINI", :STOCK=>0, :"MIN STOCK"=>5, :REQUIRED=>5, :"ROOT PLU"=>"00017226", :SIZE_OFFSET=>13}
{:STYLE=>"UD\\BOYS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROYA", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"38 (16)", :DESC=>"BOYS FITTED BLAZER WITHROYAL BRAID AND ROYAL LINI", :STOCK=>3, :"MIN STOCK"=>4, :REQUIRED=>1, :"ROOT PLU"=>"00017226", :SIZE_OFFSET=>14}
{:STYLE=>"UD\\GIRLS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROY", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"27 ##", :DESC=>"GIRLS FITTED BLAZER WITH ROYAL BRAID AND ROYAL LIN", :STOCK=>-1, :"MIN STOCK"=>1, :REQUIRED=>2, :"ROOT PLU"=>"00017280", :SIZE_OFFSET=>3}
{:STYLE=>"UD\\GIRLS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROY", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"30 (8)", :DESC=>"GIRLS FITTED BLAZER WITHROYAL BRAID AND ROYAL LIN", :STOCK=>1, :"MIN STOCK"=>4, :REQUIRED=>3, :"ROOT PLU"=>"00017280", :SIZE_OFFSET=>6}
{:STYLE=>"UD\\GIRLS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROY", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"32 (10)", :DESC=>"GIRLS FITTED BLAZER WITH ROYAL BRAID AND ROYAL LIN", :STOCK=>6, :"MIN STOCK"=>7, :REQUIRED=>1, :"ROOT PLU"=>"00017280", :SIZE_OFFSET=>8}
{:STYLE=>"UD\\GIRLS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROY", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"34 (12)", :DESC=>"GIRLS FITTED BLAZER WITH ROYAL BRAID AND ROYAL LIN", :STOCK=>6, :"MIN STOCK"=>7, :REQUIRED=>1, :"ROOT PLU"=>"00017280", :SIZE_OFFSET=>10}
{:STYLE=>"UD\\GIRLS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROY", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"35 (13)", :DESC=>"GIRLS FITTED BLAZER WITH ROYAL BRAID AND ROYAL LIN", :STOCK=>2, :"MIN STOCK"=>7, :REQUIRED=>5, :"ROOT PLU"=>"00017280", :SIZE_OFFSET=>11}
{:STYLE=>"UD\\GIRLS-BLAZER\\HQ\\FITTED\\NAVY\\ROYALLING\\BRAID\\ROY", :COLOUR=>"LINCOLN CASTLE ACADEMY (LN)", :SIZE=>"36 (14)", :DESC=>"GIRLS FITTED BLAZER WITH ROYAL BRAID AND ROYAL LIN", :STOCK=>2, :"MIN STOCK"=>5, :REQUIRED=>3, :"ROOT PLU"=>"00017280", :SIZE_OFFSET=>12}]