spreadsheet - propmansheet - How to write a Propman spreadsheet using Active Scaffold. - Ruby on Rails spreadsheet to present data and format to upload to Grosvenor Systems Propman property database

spreadsheet

How to write a Propman spreadsheet using Active Scaffold.
Featured

Updated Feb 4, 2010 by Ezzy.Elliott

Introduction

Microsoft Excel spreadsheets are created using a graphical WYSIWYG interface.

However, Active Scaffold spreadsheets are made by writing several lines of plain text instructions describing the:

Data, also called the Model;
Output, also called the View and
Logic, also called the Controller.

This generally is known as the MVC (Model-View-Controller architecture)

How To

To explain this I will use the example of a British Red Cross Property department spreadsheet, the Top 20 Bids Report.

Install instantrails (Ruby on Rails (RoR)) and Active scaffold, test them and get them working.

Model

Using "Instant Rails" Ruby console window.

Goto to the rails application folder that you created when setting up RoR, for Top20 Bids report this is called for example, "D:\InstantRails\rails_apps\top20a"

Create a Model (blank spreadsheet), lets call it "bid", that mirrors your original Excel spreadsheet. Ruby on rails has a useful command that makes it easy to do this:

ruby script/generate model bid

This creates blank templates for the model.

Create a extra template to add test data:

ruby script/generate migration add_test_data

Using Radrails editor.

Create the columns (attributes) by editing the migrate file, "../db/001_create_bids.rb" So for Top20 bids report:

class CreateBids < ActiveRecord::Migration
  def self.up
    create_table :bids do |t|
      # t.column :name, :string
      t.column :year,        :integer, :null => false
      t.column :territory,        :integer, :null => false
      t.column :area,        :float,  :null => false
      t.column :prop_ref,        :integer, :null => false
      t.column :address,  :string
      t.column :business_unit,  :string
      t.column :prop_dept_contact,  :string
      t.column :project_type,  :string
      t.column :business_case,  :string
      t.column :on_target,  :string
      t.column :cashflow_qtr,  :string
      t.column :cap_receipt,        :integer, :null => false
      t.column :cap_spend,        :integer, :null => false
      t.column :cap_retail_spend,        :integer, :null => false
      t.column :completed,  :string
      t.column :comments,  :text
    end
  end

  def self.down
    drop_table :bids
  end
end

Add test data by editing the migrate file "../db/002_add_test_data.rb". So for the Top 20 report initial test data (can be changed later by editing spreadsheet) looks like:

class AddTestData < ActiveRecord::Migration
  def self.up
  
  Bid.delete_all
  Bid.create( :year => 2007, :territory => 1, :area => 1.1, :prop_ref => 1167, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 30000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Disposal - ? Resi value})
  Bid.create( :year => 2007, :territory => 1, :area => 1.1, :prop_ref => 1666, :address => 'Ptest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 52777, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Disposal  Vacant Property -completed 29/1/07})
  Bid.create( :year => 2007, :territory => 1, :area => 1.2, :prop_ref => 1665, :address => 'Ftest, Fire Station', :business_unit => 'Ops', :prop_dept_contact => 'GP&AN', :project_type => 'Sub', :business_case => 'N/A', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Space to let in the Tender Bays;Ambulance Brigade interested - HOT to be agreed -July 2006. 4 oustanding points with Helen Bath + plan })
  Bid.create( :year => 2007, :territory => 1, :area => 1.2, :prop_ref => 2020, :address => 'Replacement for Dtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'A', :business_case => 'No', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Alternative premises required for the delivery of services assume rental with fit out £10k. test})
  Bid.create( :year => 2007, :territory => 1, :area => 1.3, :prop_ref => 1443, :address => 'Gtest', :business_unit => 'Ops/CT', :prop_dept_contact => 'SR&GP', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 50000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Ops wish to remain and reconfigure existing property, take, say, a 10 year lease.  test})
  Bid.create( :year => 2007, :territory => 1, :area => 1.3, :prop_ref => 1191, :address => 'Dtest', :business_unit => 'Ops', :prop_dept_contact => 'GP', :project_type => 'C', :business_case => 'N/A', :on_target => 'Undecided', :cashflow_qtr => 'Q2', :cap_receipt => 0, :cap_spend => 25000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Retain; consider and cost repair needs.  Roof is roadmap. Plaster repairs & insulation is Capital. Complication with gas pipe})
  Bid.create( :year => 2007, :territory => 1, :area => 1.4, :prop_ref => 1999, :address => 'Gtest, Htest Rd, Htestn', :business_unit => 'Ops', :prop_dept_contact => 'GP', :project_type => 'C', :business_case => 'Yes', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 26000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Lease completed 21.09.05. Fit out priced by test + Fees + VAT;Ops  Retention})
  Bid.create( :year => 2007, :territory => 1, :area => 1.4, :prop_ref => 1160, :address => 'Ktest, Jtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 35000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Disposal of vacant property, test})
  Bid.create( :year => 2007, :territory => 1, :area => 1.4, :prop_ref => 1153, :address => 'Atest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GP', :project_type => 'C', :business_case => 'NO', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 40000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Reconfigure in 2006.  The property is in v poor repair as a result of years test})
  Bid.create( :year => 2007, :territory => 1, :area => 1.4, :prop_ref => 1153, :address => 'Atest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GP', :project_type => 'C', :business_case => 'NO', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 40000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Reconfigure in 2006.  The property is in v poor repair as a result of years test})
  Bid.create( :year => 2007, :territory => 1, :area => 1.5, :prop_ref => 1352, :address => 'Dtest, Dtest Rd - Itest', :business_unit => 'Ops', :prop_dept_contact => 'GM', :project_type => 'D', :business_case => 'N/A', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 300000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Residential consent  being sought.  Will market once planning achieved.})
  Bid.create( :year => 2007, :territory => 1, :area => 1.5, :prop_ref => 1661, :address => 'Ltest, Gtest Hospital', :business_unit => 'Ops', :prop_dept_contact => 'GM', :project_type => 'C', :business_case => 'N/A', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Current lease to be terminated & Licence of smaller space [ex test] to be granted, Intend to relocate 2007})
  Bid.create( :year => 2007, :territory => 1, :area => 1.5, :prop_ref => 1927, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'GM', :project_type => 'A', :business_case => 'Yes', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 1000000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Relocation. test searching.  Opportunity in test being further investigated. test})
  Bid.create( :year => 2008, :territory => 1, :area => 1.1, :prop_ref => 1235, :address => 'Itest', :business_unit => 'Ops/CT', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'No', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 250000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Put on market once relocation property has been secured})
  Bid.create( :year => 2008, :territory => 1, :area => 1.1, :prop_ref => 1918, :address => 'Itest', :business_unit => 'Ops/CT/Options', :prop_dept_contact => 'AN', :project_type => 'A', :business_case => 'Awaited', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 1000000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Awaiting "in principle" Business Case.  This is to be the beacon site with test and test as satelites.  I test})
  Bid.create( :year => 2008, :territory => 1, :area => 1.5, :prop_ref => 1308, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'Yes', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 475000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Following the acquisition in 2006})
  Bid.create( :year => 2009, :territory => 1, :area => 1.1, :prop_ref => 1213, :address => 'Atest   Qtest Road', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'No', :on_target => '2009', :cashflow_qtr => '2009', :cap_receipt => 1000000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Tto market once new test is in place})
  Bid.create( :year => 2007, :territory => 2, :area => 2.1, :prop_ref => 256, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'Awaited', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation, complex development opportunities, unlikely to complete in 2007 })
  Bid.create( :year => 2007, :territory => 2, :area => 2.1, :prop_ref => 172, :address => 'Stest by Sea', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'Awaited', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 125000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation})
  Bid.create( :year => 2007, :territory => 2, :area => 2.1, :prop_ref => 3012, :address => 'Stest on Ttest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Awaited', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Relocation sought, deferred to 2007, budget test, too low, unlikely to complete in 2007?})
  Bid.create( :year => 2007, :territory => 2, :area => 2.1, :prop_ref => 519, :address => 'Stest on Ttest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'Awaited', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation, deferred to 2007, unlikely to complete in 2007?})
  Bid.create( :year => 2007, :territory => 2, :area => 2.2, :prop_ref => 3010, :address => 'Ktest Utest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 0, :cap_spend => 25000, :cap_retail_spend => 0, :completed => 'N', :comments => %{In negotiation})
  Bid.create( :year => 2007, :territory => 2, :area => 2.2, :prop_ref => 908, :address => 'Ktest Utest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 275000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation})
  Bid.create( :year => 2007, :territory => 2, :area => 2.2, :prop_ref => 829, :address => 'Ytest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Awaited', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 100000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Possible reconfiguration works})
  Bid.create( :year => 2007, :territory => 2, :area => 2.3, :prop_ref => 3015, :address => 'Btest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 0, :cap_spend => 825000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Price agreed test, circa fit out test (test transfer of Btest money from test sold in 2006, test test bid 2007)})
  Bid.create( :year => 2007, :territory => 2, :area => 2.3, :prop_ref => 901, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 80000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Awaiting go ahead from Ops Dir to put on the market})
  Bid.create( :year => 2007, :territory => 2, :area => 2.3, :prop_ref => 376, :address => 'Dtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 125000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{31 July 06 OK to start disposal process, complex title issues, linked to test acquisition})
  Bid.create( :year => 2007, :territory => 2, :area => 2.3, :prop_ref => 1583, :address => 'Ltest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 105000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Offer of test accepted, subject to survey, if gross test net test})
  Bid.create( :year => 2007, :territory => 2, :area => 2.3, :prop_ref => 250, :address => 'Otest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Awaiting go ahead from Ops Dir to put on the market, linked to test acquisition})
  Bid.create( :year => 2007, :territory => 2, :area => 2.3, :prop_ref => 767, :address => 'Rtest', :business_unit => 'Ops/Retail', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Developer approached BRCS, Ops will room hire, Retail seek a new shop in test, linked to test acquisition})
  Bid.create( :year => 2007, :territory => 2, :area => 2.3, :prop_ref => 566, :address => 'Stest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Awaiting go ahead from Ops Dir to put on the market, unlikely to complete in 2007 })
  Bid.create( :year => 2007, :territory => 2, :area => 2.4, :prop_ref => 1078, :address => 'Ktest', :business_unit => 'Ops', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 45000, :cap_retail_spend => 0, :completed => 'N', :comments => %{2006 project c/f, upgrade test and other improvements, test being difficult on planning})
  Bid.create( :year => 2007, :territory => 2, :area => 2.4, :prop_ref => 1078, :address => 'Ktest', :business_unit => 'Ops', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Air con for test, budget awaited})
  Bid.create( :year => 2007, :territory => 2, :area => 2.4, :prop_ref => 497, :address => 'Ltest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 150000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Intended lease regearing and disposal on hold, need to look at re-using the property, expanding the garage would solve proposed vehicle test requirement, may slip to 2008})
  Bid.create( :year => 2007, :territory => 2, :area => 2.4, :prop_ref => 876, :address => 'Ltest', :business_unit => 'Ops', :prop_dept_contact => 'GM', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Very  restrictive lease, complex issues, planning application submitted})
  Bid.create( :year => 2007, :territory => 2, :area => 2.4, :prop_ref => 522, :address => 'Mtest', :business_unit => 'Ops', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 30000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Adaptations to the test test})
  Bid.create( :year => 2007, :territory => 2, :area => 2.4, :prop_ref => 1915, :address => 'Ntest', :business_unit => 'Ops/CT/Terr', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 30000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Size of break out area to be increased})
  Bid.create( :year => 2007, :territory => 2, :area => 2.4, :prop_ref => 3005, :address => 'Ntest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Yes', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test garaging, freehold or leasehold property sought, to be abandoned if test can provide solution})
  Bid.create( :year => 2007, :territory => 2, :area => 2.5, :prop_ref => 3026, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Awaited', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to test, may need to accelerate acquisition in 2007})
  Bid.create( :year => 2007, :territory => 2, :area => 2.5, :prop_ref => 1936, :address => 'Gtest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 0, :cap_spend => 490000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Proposed extension, planning application submitted  })
  Bid.create( :year => 2007, :territory => 2, :area => 2.5, :prop_ref => 1125, :address => 'Ntest', :business_unit => 'Ops/CT/  Terr/UKO', :prop_dept_contact => 'GA', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 250000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Refurbishment preferred to relocation because of costs, budget to be revised following site meeting test, budget too test?})
  Bid.create( :year => 2007, :territory => 2, :area => 2.5, :prop_ref => 1938, :address => 'Stest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Yes', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 15000, :cap_retail_spend => 0, :completed => 'N', :comments => %{In negotiation})
  Bid.create( :year => 2008, :territory => 2, :area => 2.1, :prop_ref => 256, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 600000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation, complex development opportunities unlikely to complete in 2007 })
  Bid.create( :year => 2008, :territory => 2, :area => 2.1, :prop_ref => 3012, :address => 'Stest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Awaited', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 350000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Relocation sought, deferred to 2007, budget test, too low, unlikely to complete in 2007?})
  Bid.create( :year => 2008, :territory => 2, :area => 2.1, :prop_ref => 519, :address => 'Stest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 150000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation, deferred to 2007, unlikely to complete in 2007?})
  Bid.create( :year => 2008, :territory => 2, :area => 2.3, :prop_ref => 566, :address => 'Stest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 50000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Awaiting go ahead from Ops Dir to put on the market, unlikely to complete in 2007 })
  Bid.create( :year => 2008, :territory => 2, :area => 2.5, :prop_ref => 930, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'No', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 160000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation})
  Bid.create( :year => 2009, :territory => 2, :area => 2.1, :prop_ref => 1022, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'No', :on_target => '2009', :cashflow_qtr => '2009', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation})
  Bid.create( :year => 2009, :territory => 2, :area => 2.1, :prop_ref => 136, :address => 'Ntest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'No', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 1000000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation})
  Bid.create( :year => 2009, :territory => 2, :area => 2.1, :prop_ref => 1827, :address => 'Stest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'No', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 75000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{tbc})
  Bid.create( :year => 2009, :territory => 2, :area => 2.2, :prop_ref => 859, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'No', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 300000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation})
  Bid.create( :year => 2009, :territory => 2, :area => 2.3, :prop_ref => 439, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'No', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 50000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{tbc})
  Bid.create( :year => 2009, :territory => 2, :area => 2.3, :prop_ref => 850, :address => 'Stest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'No', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{tbc})
  Bid.create( :year => 2007, :territory => 3, :area => 3.1, :prop_ref => 1945, :address => 'Gtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'YES', :on_target => 'Y', :cashflow_qtr => '2010', :cap_receipt => 0, :cap_spend => 50000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Capital contribution to test})
  Bid.create( :year => 2007, :territory => 3, :area => 3.1, :prop_ref => 758, :address => 'Gtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => 'Y', :cashflow_qtr => '2010', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{  on market -  })
  Bid.create( :year => 2007, :territory => 3, :area => 3.1, :prop_ref => 802, :address => 'Gtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => 'Y', :cashflow_qtr => '2010', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{on market Jan '06 })
  Bid.create( :year => 2007, :territory => 3, :area => 3.1, :prop_ref => 597, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => '2010', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{April 06-warned by Ops - to be sold Valuation requested May '06 - plannning delays to 2007})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 113, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'No', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 150000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{ dependant on new acquisition})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 642, :address => 'Dtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 250000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test man instructed. Layout being agreed march '06. Being progressed April '06 test May '06 test wants excessive % of gain. Nov '06 test now persuaded! 50:50 deal. Planning access to be test})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 981, :address => 'Ftest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'No', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test facility})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 894, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'YES', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test facility})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 3029, :address => '  Htest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'YES', :on_target => 'N', :cashflow_qtr => 'Q3', :cap_receipt => 0, :cap_spend => 165000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Tenders Jan 2007-cost test})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 1537, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'No', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{disposal})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 642, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'No', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test Facility})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 964, :address => 'Ltest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'No', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test facility})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 3030, :address => 'Ktest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'A', :business_case => 'No', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{"Spoke" to replace previous test acquisition on same estate but smaller - Oct 06 dead-revived Dec '06. Close to test Jan '06})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 937, :address => 'Ttest', :business_unit => 'Ops', :prop_dept_contact => 'GM', :project_type => 'D', :business_case => 'No', :on_target => 'N', :cashflow_qtr => 'Q2', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test dealing. Very slow. BRCS to demolish building. Assume test to demolish -test. - Jan '07})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 849, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 240000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{On market. Price reduced march '06. Under offer test -problem with BRCS failure to obtain test - applying for retrospective consent -Sep '06 - Application made & ongoing Jan '06})
  Bid.create( :year => 2007, :territory => 3, :area => 3.2, :prop_ref => 624, :address => 'Ptest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => 'N', :cashflow_qtr => 'Q2', :cap_receipt => 175000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test decision -valuation ion Feb 06. Ops advised vacated March 2007 in Jan '07. Marketing being prepared})
  Bid.create( :year => 2007, :territory => 3, :area => 3.3, :prop_ref => 1515, :address => 'Mtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D/A', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Brought forward due to test being offered at 25% test. Space specxs done. No budgets yet. Site found - ? Appropriate course of action. Acquisition on hold Dec '06. Need to know what Ops want to do on hold for moment})
  Bid.create( :year => 2007, :territory => 3, :area => 3.3, :prop_ref => 1949, :address => 'Otest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'A', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Ops to decide-may not be necessary})
  Bid.create( :year => 2007, :territory => 3, :area => 3.3, :prop_ref => 1088, :address => 'Rtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 500000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Alternative schemes being considered re: redevelopment jan '06. Cost test but planning probs -up to test -Need accurate Budget. Planning Application Dec '06. Existing building a test})
  Bid.create( :year => 2007, :territory => 3, :area => 3.3, :prop_ref => 1089, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{await planning + land acquisition . Plans approved by Ops - Apr 06 Budget price £240,000   but new garage required. P.P. refused - lack of floodplain info -awaiting Council decision as to Fire Brigade HQ. Complained to Council re: lack of progress Dec '06-received usual platitudes in reply})
  Bid.create( :year => 2007, :territory => 3, :area => 3.4, :prop_ref => 493, :address => 'Etest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Valued at test. Redevelop. Consider reevelop})
  Bid.create( :year => 2007, :territory => 3, :area => 3.4, :prop_ref => 349, :address => 'Rtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'NO', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 50000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Re: Planning. Met with test Dec 2005-Planning Application May 2006-decision 11Nov valuation Notable plannng victory for BRCS. test-works deferred to 2007})
  Bid.create( :year => 2007, :territory => 3, :area => 3.4, :prop_ref => 944, :address => 'Etest', :business_unit => 'CT', :prop_dept_contact => 'CH', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 0, :cap_spend => 65000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Total cost test - split provisional - test received for avoiding break clause})
  Bid.create( :year => 2007, :territory => 3, :area => 3.5, :prop_ref => 1054, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'A', :business_case => 'n/a', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 80000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Agreed. Trustee Approval gained. In test hands-will be slow! -Apr 06 test imminent - may '06-& Oct '06 - received dec '06})
  Bid.create( :year => 2007, :territory => 3, :area => 3.5, :prop_ref => 1378, :address => 'Etest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'NO', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 15000, :cap_retail_spend => 0, :completed => 'N', :comments => %{evaluating options-Approached freeholder Feb 2006. Agreed freehold test. Negotiating for front March 2006 Will be very test - chased Sept '06-still slow! Chased dec '06})
  Bid.create( :year => 2007, :territory => 3, :area => 3.5, :prop_ref => 2007, :address => 'Etest', :business_unit => 'Ops', :prop_dept_contact => 'CH', :project_type => 'C', :business_case => 'NO', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{   Relocation at no cost to BRCS})
  Bid.create( :year => 2007, :territory => 3, :area => 3.5, :prop_ref => 527, :address => 'Otest', :business_unit => 'Retail', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'N/A', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Retail test. Awaiting plan to enable Planning Application to be made July '06 - test aug '04 -considering appeal-made 2nd application Dec '06 - looks test})
  Bid.create( :year => 2007, :territory => 3, :area => 3.6, :prop_ref => 2008, :address => 'Dtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'NO', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 200000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Developer interested-John A is dealing offered land-not attractive - apr '06 Now offered RC £200K to go. Wrote to Op[s May '06 with options-await decision. Ops want out at £200K-awaiting consultation before invoking clause- note sent out Oct '06 explaining position - Accepted offer to surrender Dec '06})
  Bid.create( :year => 2007, :territory => 3, :area => 3.5, :prop_ref => 881, :address => 'Rtest', :business_unit => 'Ops', :prop_dept_contact => 'CH', :project_type => 'C', :business_case => 'NO', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{following test. Revised strategy suggested to Ops. Decided to replace building. External test available now-with test for planning & costs-considering alternative methods of construction to reduce price -Dec '06})
  Bid.create( :year => 2007, :territory => 3, :area => 3.6, :prop_ref => 1913, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'AN&CH', :project_type => 'A', :business_case => 'YES', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Land Purchjase completed may 2006. Planning received. B regs received. Awaiting completion of Spec to go to Tender - test too high Aug '06 -persuing alternative options. ON HOLD Dec '06})
  Bid.create( :year => 2008, :territory => 3, :area => 3.1, :prop_ref => 596, :address => 'Dtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'N/A', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 45000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Ops to decide - on hold})
  Bid.create( :year => 2008, :territory => 3, :area => 3.1, :prop_ref => 466, :address => 'Htest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'N/A', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 250000, :cap_retail_spend => 0, :completed => 'N', :comments => %{ Inspected. Have queried whether we should just test.  Possibility of test wanting   test -Dec '06 - plans with Ops & test for test. Excess land ? For test test TO ADD test RECEIPT FOR test DEVELOPMENT})
  Bid.create( :year => 2008, :territory => 3, :area => 3.1, :prop_ref => 1950, :address => 'Ptest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'No', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 750000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Lease expires test. Have queried future use with Clive Evans. Temporary building -Feb '06 Written to test March '06. Await BC. test dealing-May '06 test circa test test. test prepared to grant new lease. Do Ops want new test-Aug 2006. Yes! Ops sent plans for test Dec '06 for test})
  Bid.create( :year => 2008, :territory => 3, :area => 3.2, :prop_ref => 1968, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => ' NO', :on_target => ' N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 1000000, :cap_retail_spend => 0, :completed => 'N', :comments => %{ Planning Brief now involved from test.  Meeting with test + Ops. plans drawn-comments from Ops with Architects ec '06- looks test - budget to be done. Costs will be offset by test sale -awaiting valuations- Jan '06})
  Bid.create( :year => 2008, :territory => 3, :area => 3.3, :prop_ref => 1088, :address => 'Rtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 500000, :cap_retail_spend => 0, :completed => 'N', :comments => %{test schemes being test re: redevelopment jan '06. Cost test but planning probs -up to test -Need accurate test. Planning Application Dec '06. Existing building a test})
  Bid.create( :year => 2008, :territory => 3, :area => 3.3, :prop_ref => 1089, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'C', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 240000, :cap_retail_spend => 0, :completed => 'N', :comments => %{await test + land test . Plans approved by Ops - Apr 06 Budget price test   but new garage required. test - lack of test info -awaiting Council decision as to test. Complained to test re: lack of progress Dec '06-received usual test in test})
  Bid.create( :year => 2008, :territory => 3, :area => 3.4, :prop_ref => 480, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'AN', :project_type => 'D', :business_case => 'NO', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test gearing on long lease. test user. Value looks test. Referred back to Valuer-August 2006. Adjusted back to NIL by Valuer!})
  Bid.create( :year => 2008, :territory => 3, :area => 3.6, :prop_ref => 1913, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'AN&CH', :project_type => 'A', :business_case => 'YES', :on_target => 'N', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test Purchjase completed may 2006. Planning received. B regs received. Awaiting completion of Spec to go to Tender - May 2006 Prices too test Aug '06 -persuing alternative options. ON HOLD Dec '06})
  Bid.create( :year => 2007, :territory => 4, :area => 4.1, :prop_ref => 481, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Long leashold, nil receipt, NB test costs (test)})
  Bid.create( :year => 2007, :territory => 4, :area => 4.1, :prop_ref => 3021, :address => 'Atest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Awaited', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Extension to test, ML, defer to 2007 as test consent test })
  Bid.create( :year => 2007, :territory => 4, :area => 4.1, :prop_ref => 383, :address => 'Ltest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 225000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to relocation})
  Bid.create( :year => 2007, :territory => 4, :area => 4.1, :prop_ref => 3057, :address => 'Ptest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 15000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Lease completed 5 test 2007})
  Bid.create( :year => 2007, :territory => 4, :area => 4.1, :prop_ref => 1855, :address => 'Ntest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GM', :project_type => 'A', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 746000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Expect to start on site Q2 06 and complete Q1 07 (test 05,  test 06, test  07, 2008 Q1 test)})
  Bid.create( :year => 2007, :territory => 4, :area => 4.1, :prop_ref => 806, :address => 'Ntest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 480000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to acquisition & new build, best bids 5 Feb, offer of test accepted, net test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.2, :prop_ref => 1330, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Undecided', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Joint tenancy with test who wish to redevelop test or lease test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.2, :prop_ref => 3038, :address => 'Ttest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A ', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 0, :cap_spend => 15000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Leasehold test, maybe with test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.2, :prop_ref => 1627, :address => 'Wtest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GM', :project_type => 'C', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 926000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Works to commence Aug 06 and complete Mar 07, total project cost test : spend test, test 07})
  Bid.create( :year => 2007, :territory => 4, :area => 4.2, :prop_ref => 328, :address => 'Wtest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 450000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Offer of test accepted, net test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.3, :prop_ref => 3039, :address => 'Btest', :business_unit => 'CT', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 50000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Relocation by test to test up accom at test, leasehold, search test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.3, :prop_ref => 1550, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'DE', :project_type => 'C', :business_case => 'Awaited', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 50000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Reconfiguration & air con, linked to test relocation})
  Bid.create( :year => 2007, :territory => 4, :area => 4.3, :prop_ref => 459, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'No', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 150000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{test sale as a going concern, freehold, QSR obtained})
  Bid.create( :year => 2007, :territory => 4, :area => 4.3, :prop_ref => 295, :address => 'Ntest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 75000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Lease restructured, now on the test, offer of test accepted   })
  Bid.create( :year => 2007, :territory => 4, :area => 4.4, :prop_ref => 3023, :address => 'Qtest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 750000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Freehold purchase completed 5 February 2007, price test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.4, :prop_ref => 395, :address => 'Ktest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 350000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to test upgrade, expect to start marketing in June})
  Bid.create( :year => 2007, :territory => 4, :area => 4.4, :prop_ref => 574, :address => 'Stest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 400000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to test acquisition, on the market})
  Bid.create( :year => 2007, :territory => 4, :area => 4.4, :prop_ref => 1655, :address => 'Wtest ', :business_unit => 'Ops', :prop_dept_contact => 'DE', :project_type => 'C', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 0, :cap_spend => 50000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Minor reconfiguartion & improvement works})
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 3052, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 325000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Freehold purchase completed 12 January 2007, price test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 853, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 190000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Exchanged at test, net test})
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 556, :address => 'Mtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 200000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Advice obtained, awaiting go ahead to put on market})
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 776, :address => 'Ttest', :business_unit => 'Ops', :prop_dept_contact => 'DE', :project_type => 'C', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 40000, :cap_retail_spend => 0, :completed => 'N', :comments => %{New test to be built on existing test property})
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 564, :address => 'Ttest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 150000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Pursuing planning options})
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 673, :address => 'Ttest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q3', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Pursuing planning options})
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 1112, :address => 'Ttest', :business_unit => 'Ops/CT', :prop_dept_contact => 'DE', :project_type => 'C', :business_case => 'Approved', :on_target => 'Y', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 950000, :cap_retail_spend => 0, :completed => 'N', :comments => %{test and test, planning test obtained, start on site Aug 06 complete Apr 07, total test, 2006 Q4 test, 2007 Q1 test, Q3 test  test })
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 88, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'Y', :cashflow_qtr => 'Q2', :cap_receipt => 200000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Planning for test dev obtained, to go on the test Feb 07 })
  Bid.create( :year => 2007, :territory => 4, :area => 4.5, :prop_ref => 868, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => 'N', :cashflow_qtr => '2008', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{To be confirmed, test to complete in 2007})
  Bid.create( :year => 2008, :territory => 4, :area => 4.1, :prop_ref => 3021, :address => 'Atest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'A', :business_case => 'Awaited', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 0, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Extension to test test, test, defer to test as planning consent test })
  Bid.create( :year => 2008, :territory => 4, :area => 4.1, :prop_ref => 1855, :address => 'Ntest', :business_unit => 'Ops/CT', :prop_dept_contact => 'GM', :project_type => 'A', :business_case => 'Approved', :on_target => '2008', :cashflow_qtr => 'Q1', :cap_receipt => 0, :cap_spend => 18000, :cap_retail_spend => 0, :completed => 'N', :comments => %{Expect to start on site Q2 06 and complete Q1 07 (test 05,  test 06, test  07, 2008 Q1 test)})
  Bid.create( :year => 2008, :territory => 4, :area => 4.2, :prop_ref => 222, :address => 'Htest', :business_unit => 'Ops/CT', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 500000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to acquisition})
  Bid.create( :year => 2008, :territory => 4, :area => 4.5, :prop_ref => 639, :address => 'Ltest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 40000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{To be confirmed})
  Bid.create( :year => 2008, :territory => 4, :area => 4.5, :prop_ref => 868, :address => 'Wtest', :business_unit => 'Ops', :prop_dept_contact => 'MD', :project_type => 'D', :business_case => 'N/A', :on_target => '2008', :cashflow_qtr => 'Q4', :cap_receipt => 60000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{To be confirmed, unlikely to complete in 2007})
  Bid.create( :year => 2009, :territory => 4, :area => 4.2, :prop_ref => 509, :address => 'Btest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 50000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to acquisition})
  Bid.create( :year => 2009, :territory => 4, :area => 4.2, :prop_ref => 656, :address => 'Ktest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 50000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to acquisition})
  Bid.create( :year => 2009, :territory => 4, :area => 4.5, :prop_ref => 803, :address => 'Ctest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 125000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{Linked to completed acquisition})
  Bid.create( :year => 2009, :territory => 4, :area => 4.5, :prop_ref => 716, :address => 'Ftest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :completed => 'N', :comments => %{To be confirmed})
  Bid.create( :year => 2009, :territory => 4, :area => 4.5, :prop_ref => 716, :address => 'Ftest', :business_unit => 'Ops', :prop_dept_contact => 'MJ', :project_type => 'D', :business_case => 'N/A', :on_target => '2010', :cashflow_qtr => '2010', :cap_receipt => 100000, :cap_spend => 0, :cap_retail_spend => 0, :comments => %{To be confirmed})
  
  end

  def self.down
  end
end

Using "Instant Rails" Ruby console window. Run these instructions:

rake db:migrate

Next use RadRails to edit the bid model. "../app/model/bid.rb" to validate data input into cells so to stop rubbish being put into spreadsheet. Example for Bids report:

class Bid < ActiveRecord::Base



  validates_presence_of :address, :business_unit, :prop_dept_contact, :project_type, :business_case, :on_target, :cashflow_qtr
  validates_numericality_of :year, :territory, :prop_ref, :cap_receipt, :cap_spend, :cap_retail_spend, :only_integer => true, :allow_nil => false, :message => " - Oh come on, whole numbers not decimals!"
  validates_numericality_of :area, :allow_nil => false
  validates_uniqueness_of :prop_ref
  validates_format_of :area, :with => /^[1-2]\.[1-5]$|^[3]\.[1-6]$|^[4]\.[1-5]$/, :message => " - What's wrong with you? Use normal Areas!"
  validates_format_of :territory, :with => /[1-4]/, :message => " - Wrong, there are four of them one to four"
  validates_format_of :year, :with => /2007|2008|2009/, :message => " - Wrong, You can only enter bids for years 2007, 2008 or 2009"
  validates_format_of :prop_ref, :with => /^[2-9][0-9]$|^[1-9][0-9][0-9]$|^[1-3][0-9][0-9][0-9]$/, :message => " - Wrong, use Propman Ref. not made-up number!"
  validates_inclusion_of :business_unit, :in => %w{ Ops Retail Ops/CT Ops/Retail Ops/CT/Terr CT Ops/CT/Options Ops/CT/Terr/UKO }, :message => " - Use normal Propman divisions"
  validates_inclusion_of :prop_dept_contact, :in => %w{ GM AN MD SR&GP GP MJ GA AN&CH DE CH GP&AW}, :message => " - Use only normal Top20 initials - no spaces, use & for AND"
  validates_inclusion_of :project_type, :in => %w{ A D C D/A Sub }, :message => " - Use only A, D, C, D/A or Sub"
  validates_inclusion_of :business_case, :in => %w{ Draft N/A Yes No Awaited Approved }, :message => " - Use only Draft, N/A, Yes, No or Awaited"
  validates_inclusion_of :on_target, :in => %w{ Draft Y N 2008 2009 2010 Undecided }, :message => " - Use only Draft, Y, N, 2008, 2009, 2010 or Undecided"
  validates_inclusion_of :cashflow_qtr, :in => %w{ Q1 Q2 Q3 Q4 2008 2009 2010 }, :message => " - Use only Q1, Q2, Q3 or Q4"
  validates_inclusion_of :completed, :in => %w{ Y N }, :message => " - Use only Y or N "
  

  
  
  
  protected
  def validate
    errors.add(:cap_receipt, " - Should be positive") if cap_receipt.nil? || cap_receipt < -0.01
    errors.add(:cap_spend, " - Should be positive") if cap_spend.nil? || cap_spend < -0.01
    errors.add(:cap_retail_spend, " - Should be positive") if cap_retail_spend.nil? || cap_retail_spend < -0.01
    
    if project_type == "A" && (cap_receipt != 0 || cap_retail_spend != 0)
      errors.add(:project_type, " - A only Capital Spend allowed")
    end
    
    if project_type == "D" && (cap_spend != 0 || cap_retail_spend != 0)
      errors.add(:project_type, " - D only Capital Receipt allowed")
    end
    
    if project_type == "C" && (cap_receipt != 0 )
      errors.add(:project_type, " - C only Capital Spend and Capital Retail Spend allowed")
    end
           
    if project_type == "Sub" && (cap_spend != 0 || cap_retail_spend != 0)
      errors.add(:project_type, " - Sub only Capital Receipt allowed")
    end
   
      
  end

end

Controller

Next you need to add spreadsheet functionality to the bid model by creating a controller. In the "Instant Rails" Ruby console window type:

ruby script/generate controller bids

This creates a blank bids_controller template. Use RadRails to edit "../app/controllers/bids_controller.rb". Add instructions to use Active Scaffold with the following functionality:

Exclude search and show buttons
Sum capital receipts column
Sum capital spend column
Sum capital retail spend column
Count number of addresses (items) in address column
Set to sort initially ascending by property ref
No page breaks by setting number of rows to page to 1,000
Give spreadsheet title of, "Top 20 Bids 2007"
Give each column a short heading and a description
Set condition for listing rows to be dependent on territory and year.

The completed controller for the bids example looks like this:

class BidsController < ApplicationController

  active_scaffold :bid do |config|
  
  config.actions.exclude :search
  config.actions.exclude :show
  
  config.columns[:cap_receipt].calculate = "sum"
  config.columns[:cap_spend].calculate = "sum"
  config.columns[:cap_retail_spend].calculate = "sum"
  config.columns[:address].calculate = "count"
  
  list.sorting = {:prop_ref => 'ASC'}
  
  list.per_page = 1000
  list.label = "Top 20 Bids 2007"
  columns[:year].label = "Y."
  columns[:year].description = "Year"
  
  columns[:territory].label = "T."
  columns[:territory].description = "Territory"
  
  columns[:area].label = "A."
  columns[:area].description = "Area"
  
  columns[:prop_ref].label = "PR."
  columns[:prop_ref].description = "Property Reference"
  
  columns[:address].label = "Town"
  columns[:address].description = "Town"
  
  columns[:business_unit].label = "Div"
  columns[:business_unit].description = "Division: CT Ops/Retail Ops/CT/Terr CT Ops/CT/Options Ops/CT/Terr/UKO"
  
  columns[:prop_dept_contact].label = "Man"
  columns[:prop_dept_contact].description = "Contact: GM AN MD SR&GP GP MJ GA AN&CH DE CH GP&AW"
  
  columns[:project_type].label = "P."
  columns[:project_type].description = "Type: A, D, C, D/A or Sub"
  
  columns[:business_case].label = "B. Case"
  columns[:business_case].description = "Business Case: Draft N/A Yes No Awaited Approved"
  
  columns[:on_target].label = "On Target"
  columns[:on_target].description = "On Target: Draft N/A Yes No Awaited Approved"
  
  columns[:cashflow_qtr].label = "Qtr"
  columns[:cashflow_qtr].description = "Cashflow Quarter: Q1 Q2 Q3 Q4 2008 2009 2010"
  
  columns[:completed].label = "C"
  columns[:completed].description = "Completed: Y N"
  
  columns[:cap_receipt].label = "Repeipt"
  columns[:cap_receipt].description = "Capital Receipt"
  
  columns[:cap_spend].label = "Spend"
  columns[:cap_spend].description = "Capital Spend"
  
  columns[:cap_retail_spend].label = "Retail"
  columns[:cap_retail_spend].description = "Retail Cap Spend"
  
  columns[:comments].label = "Comments"
  columns[:comments].description = "Comments"
  
  end

  def conditions_for_collection
   ['territory = ? and year = ?', params[:terr], params[:yr] ]
  end

end

Next need to create a controller to reformat the spreadsheet data and output it as a tab separated file that can be imported into Propman. Use the InstantRails console window to create a new controller template:

ruby script/generate controller datafile

Then edit the resulting blank template, "../app/controllers/datafile_controller.rb" using the RadRails.

It is easy to make a tab separate file by using the "concatenate" command "<<";the "/t" tab symbol and the loop for all bids construct "Bid.find(:all).each { COMMAND TO DO FOR EACH BID }". See example:

class DatafileController < ApplicationController
  def make_property_analysis_file
    #for each property id add property code tab analysis group code tab analysis group type code new line to content file
    string=String.new
    #For Division (DIV) /Business Unit
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"DIV"+"\t"+bid.business_unit+"\n"  }
    #For Property Dept Contact (WHO)
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"WHO"+"\t"+bid.prop_dept_contact+"\n"  }
    #For Project Type (TYP)
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"TYP"+"\t"+bid.project_type+"\n"  }
    #For Business Case (BUS)
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"BUS"+"\t"+bid.business_case+"\n"  }
    #For Year (YOC)
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"YOC"+"\t"+bid.year.to_s+"\n"  }
    #For On_target *(OTG) Needs to be set up on Propman
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"OTG"+"\t"+bid.on_target+"\n"  }
    #For Cashflow (YOQ)
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"YOQ"+"\t"+bid.cashflow_qtr+"\n"  }
    #For Completed * (CMT)Needs to be set up on Propman
    Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"CMT"+"\t"+bid.completed+"\n"  }
    
    File.open("C:/Prop_analysis.txt", "w")  { |f| f.write(string) }
    
    @pa_content = string 
    @pa_content_type='text/plain'
    @response.headers["Content-Type"] = @pa_content_type
    render :layout=>false
      
    
  end
  
  def make_key_note_file
  string=String.new
  #for RM2005 keynote is property id tab1 key-word tab2 tab3 tab4 tab5 tab6 tab7 tab8 tab9 cap_receipt.to_s tab10 cap_spend.to_s tab11 tab12 cap_retail_spend.to_s tab13 new_line
  Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"RM2005"+"\t\t\t\t\t\t\t\t"+bid.cap_receipt.to_s+"\t"+bid.cap_spend.to_s+"\t\t"+bid.cap_retail_spend.to_s+"\t"+"\n"  }
  #for Top20 keynote is property id tab1 key-word tab2 tab3 tab4 tab5 tab6 tab7 tab8 tab9 tab10 tab11 tab12 tab13 comments new-line
  Bid.find(:all).each { |bid| string << bid.prop_ref.to_s+"\t"+"Top20"+"\t\t\t\t\t\t\t\t\t\t\t\t"+bid.comments+"\n"  }
  
  File.open("C:/Key_Note.txt", "w")  { |f| f.write(string) }
  
  @kn_content = string 
  @kn_content_type='text/plain'
  @response.headers["Content-Type"] = @kn_content_type
  render :layout=>false
  
  end
  
  
  
end

View

Next use RadRails to create the main webpage (View), "../app/views/layouts/bids.rhtml". This is a normal web page with some extra lines to add spreadsheet:

<%= javascript_include_tag :defaults %>
<%= active_scaffold_includes %>
<%= @content_for_layout %>

And to add year and territory filter for example report:

<%= form_tag %>
<table>
<td>Territory: <%= select_tag(:terr,
                            options_for_select(%w{1 2 3 4 5},
                            params[:terr])) %></td>
                            <td>&nbsp;</td>
<td>Year: <%= select_tag(:yr,
                            options_for_select(%w{2007 2008 2009 },
                            params[:yr])) %></td> 
                            <td>&nbsp;</td>                         
<td><%= submit_tag %></td>
</table>
<%= end_form_tag %>

Also add a simple file to display Propman analysis and keynote import files: "..\app\views\datafile\make_property_analysis_file.rhtml"

<%=@pa_content%>

"..\app\views\datafile\make_key_note_file.rhtml"

<%=@kn_content%>

The full view (web page) looks like this:

<!DOCTYPE html
        PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
    <title>BRITISH RED CROSS SOCIETY Top Twenty Report (Project Bids)</title>
 
   
 
   
</head>
<body>


<table>
<tr>
<td>
<font color="#000099"><b><font face="trebuchet ms" size="5">BRITISH RED CROSS TOP TWENTY REPORT</font></b></font>
</td>
<td>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</td>
<td>
<img src="\images\brcslogo.jpg">

</td>
</tr>
</table>
<font face="trebuchet ms">
<%= form_tag %>
<table>
<td>Territory: <%= select_tag(:terr,
                            options_for_select(%w{1 2 3 4 5},
                            params[:terr])) %></td>
                            <td>&nbsp;</td>
<td>Year: <%= select_tag(:yr,
                            options_for_select(%w{2007 2008 2009 },
                            params[:yr])) %></td> 
                            <td>&nbsp;</td>                         
<td><%= submit_tag %></td>
</table>
<%= end_form_tag %>

<%= javascript_include_tag :defaults %>
<%= active_scaffold_includes %>
<%= @content_for_layout %> 

<%= link_to("Open Crystal Reports - Total Top 20 Report (Formatted for Printing)", "file://S:/SSD/PROP/Errol Elliott/PropMan/Reports/Top20/Top20_RoR.rpt") %>
<Br>
<%= link_to("Open Crystal Reports - Total Top 20 Report (Formatted for Exporting to Excel)", "file://S:/SSD/PROP/Errol Elliott/PropMan/Reports/Top20/Top20_RoR_plain.rpt") %>
<Br>
<%= link_to "Update Propman Product Analysis Upload File", :controller => 'datafile',  :action => 'make_property_analysis_file'%>
<Br>
<%= link_to "Update Propman Key Note Upload File", :controller => 'datafile',  :action => 'make_key_note_file'%>

</font>  
</body>
</html>

To Run

Then to run browse to the view:

http://localhost:3000/bids

► Sign in to add a comment