Company owner export from legacy

CompanyOwnerExport.sql

#
# Select all company owner users from legacy database
#
select 
Member.Username as username,Member.Password as password,
Person.Salutation as salutation, Person.Firstname as first_name,Person.Surname as last_name,Person.email,Person.Mobile as mobile_number,
Company.CompanyID as legacy_company_id, Company.CompanyName as name,
Address.vcStreetNo as street_no, Address.vcStreetName as street_name, Address.vcStreettype as street_type, Address.vcSuburb as suburb, Address.intPostCode as postcode, Address.vcAddress1 as address1
from person
join Member on Person.MemberID=Member.MemberID
join Company on Company.MemberID=Member.MemberID
left outer join Address on Address.intAddressID=Person.AddressID

#
# Select all company owners who do not have a username
#
select Member.Username,Member.Password,
Person.Salutation, Person.Firstname,Person.Surname,Person.email,Person.Mobile,
Company.CompanyID, Company.CompanyName
from Member 
join Company on Company.MemberID=Member.MemberID 
join Person on Person.MemberID=Member.MemberID
where Username is null

#
# Select all company owner users from legacy database without an email address
#
select Member.Username,Member.Password,
Person.Salutation, Person.Firstname,Person.Surname,Person.email,Person.Mobile,
Company.CompanyID, Company.CompanyName
from Member 
join Company on Company.MemberID=Member.MemberID 
join Person on Person.MemberID=Member.MemberID
where Email is null or Email=''

#
# Select all users who are a landlord
#
select
Member.Username as username,Member.Password as password,
Person.Salutation as salutation, Person.Firstname as first_name,Person.Surname as last_name,Person.email,Person.Mobile as mobile_number,
Address.vcStreetNo as street_no, Address.vcStreetName as street_name, Address.vcStreettype as street_type, Address.vcSuburb as suburb, Address.intPostCode as postcode, Address.vcAddress1 as address1
from person
join Member on Person.MemberID=Member.MemberID
left outer join Address on Address.intAddressID=Person.AddressID
where Member.GroupID=2

#
# Select all users who are not a company owner (i.e. landlords and renters)
#
select
Person.email,
Person.Firstname as first_name,Person.Surname as last_name
from person
join Member on Person.MemberID=Member.MemberID
where ((select count(*) from company where Company.memberid = Person.MemberID) = 0 and
        (Member.GroupID not in (3,4,99) and not Person.Email like 'davidb%rent%'))
order by email

#
# Select all user who share an email between a landlord and a company owener account.
# First, we collect all email addresses for company owners and put them in a temp table #abc.
# Then, select all email address that are not associated with a company owner but
# do appear in temp table #abc
#

INSERT INTO #abc (email)
select
Person.email from person
join Member on Person.MemberID=Member.MemberID
join Company on Company.MemberID=Member.MemberID
join Address on Address.intAddressID=Person.AddressID
where Member.GroupID=3

select
Person.email,
Person.Firstname as first_name,Person.Surname as last_name
from person
join Member on Person.MemberID=Member.MemberID
where ((select count(*) from company where Company.memberid = Person.MemberID) = 0 and
		(select COUNT(*) from #abc where #abc.email = Person.Email) > 0
		and Member.GroupID not in ( 1,6))
order by email
		
drop table #abc
#

export_company_owners.rb

#
# Company users export class
#
module ActiveListings

class ConnectionException < Exception
end

class ExportCompanyOwners
    @db_host = ''
    @db_user = ''
    @db_pwd = ''
    @db_conn = nil

    def initialize
      set_connection_parameters
      @db_conn = DbManagement::DbServer.new(@db_host, @db_user, @db_pwd)
      if @db_conn.isOpen
        puts "Connected!"
        @tbl = DbManagement::SqlsTable.new(@db_conn, '')
      else
        puts "Connection to legacy database failed"
      end
    end

    #-----------------------------------------------------------------------
    # Set the connection parameters as per the environment
    #-----------------------------------------------------------------------

    def set_connection_parameters
      if Rails.env == 'production'
        @db_host = "203.23.213.168"
        @db_user = "rentLive";
        @db_pwd = "Consult23";
      else
        @db_host = "192.168.0.244"
        @db_user = "rent_staging";
        @db_pwd = "GreenFish123";
      end
    end

    #-----------------------------------------------------------------------
    # Return the company users
    #-----------------------------------------------------------------------

    def get_company_users
      sql = "select Member.Username as username,Member.Password as password, Person.Salutation as salutation, Person.Firstname as first_name,Person.Surname as last_name,Person.email,Person.Mobile as mobile_number, Company.CompanyID as legacy_company_id, Company.CompanyName as name, Address.vcStreetNo as street_no, Address.vcStreetName as street_name, Address.vcStreettype as street_type, Address.vcSuburb as suburb, Address.intPostCode as postcode, Address.vcAddress1 as address1 from person join Member on Person.MemberID=Member.MemberID join Company on Company.MemberID=Member.MemberID left outer join Address on Address.intAddressID=Person.AddressID"

      res = @tbl.query(sql)
      puts "Found #{res.count} rows"
      return res
    end
end

end

export_company_users.rake

#
# Rake task to export company users to CSV file /data/shared/log/export_company_users.csv
#
  task :export_company_owners, [:rails_env] => [:environment] do |t, args|
    rails_env = args[:rails_env]

    def get_user(c, id, fn, ln)
        un = c.get_username(id, fn, ln)
        puts ("Username for [#{id}][#{fn}][#{ln}]: #{un}")
    end

    rails_env = 'development' if rails_env.nil?
    ENV['RAILS_ENV'] = rails_env
    puts ("Exporting company owners, environment = #{rails_env}")
    puts ("----------------------------------------------------")
    c = ActiveListings::ExportCompanyOwners.new

    res = c.get_company_users

    file_name = '/data/shared/log/export_company_users.csv'
    fd = File.new(file_name, 'w')
    if fd
      #debugger
        s = ''
        s << res[0].keys.to_s.sub(/[\[]/, '').sub(/[\]]/,'') << "\n"
        fd.write(s)
      res.each do |row|
        #row.values.each {|v| print v; print ','}
        s = ''
        s << row.values.to_s.sub(/[\[]/, '').sub(/[\]]/,'') << "\n"
        fd.write(s)
      end
      fd.close
      puts "Finished"
    else
      puts "Cannot open file #{file_name}"
    end
  end
end

import_company_owners.rake

namespace :db_management do
require 'csv'

  desc 'Import company user from csv file'

  task :import_company_owners, [:rails_env] => [:environment] do |t, args|
    rails_env = args[:rails_env]

    def create_or_update_user(row, company)
      user = User.find_or_initialize_by_email(row["email"])

#     if user.new?
#        user.attributes = row
#      else
      row.each do |k, v|
        user.send("#{k}=", v) unless k == "username" || !user.respond_to?(k) || user.send(k).present?
      end

      return nil unless user.valid?
      user.company = company
      user.skip_confirmation!
      user.save!
      return user
    end

    rails_env = 'development' if rails_env.nil?
    ENV['RAILS_ENV'] = rails_env
    puts ("Importing company owners, environment = #{rails_env}")
    puts ("----------------------------------------------------")
    c = ActiveListings::ExportCompanyOwners.new

    res = c.get_company_users

    res.each do |row|
      cid = row["legacy_company_id"]
      company = Company.find_by_legacy_company_id(cid)
      if company.blank?
        puts ("Error: Company with legacy id #{cid} not found")
      else
        puts ("Company found: #{company.name}") if company.present?
        u = create_or_update_user(row, company)
        #u.send(k + "=", v) if k != 'legacy_company_id' && k != 'username' && k != 'password' && k != 'company_name'}
        if u.nil?
          puts "User #{row["email"]} not found and could not be created"
        else
          puts "Updated user #{u.email}"
          p u
        end
      end
    end
  end
end

sql.php

<?php

function imageFolder()
{
  return '\/Images\/images_agents1\/';
}

function getSqlForProperty($pid)
{
  return sprintf ("
select
PropertyCat.Category as listing_type,
cast (case when prop.uif1=1 then 'street' else (case when prop.uif1=2 then 'full' else 'suburb' end) end as varchar(10)) as address_display_type,
pc.Category as property_type,
CONVERT(varchar,prop.sdtExpiryDate,126) as [expiry_date],
CONVERT(varchar,prop.dteDateEntered,126) as created_at,
prop.txtComments as description,
prop.txtAdvertByline as byline,
dbo.PropertyStatus.vcStatusDesc as status_type,
CONVERT(varchar,prop.AvailableDate,126) as date_available,
prop.MinTerm as desired_term,
(case prop.uvcf2
  when 'M' then (prop.RelPrice) * 12 / 52
  when 'D' then (prop.RelPrice) * 7
  else prop.RelPrice end) as weekly_rent,
(case prop.uvcf2
  when 'M' then (prop.priceRangeFrom) * 12 / 52
  when 'D' then (prop.priceRangeFrom) * 7
  else prop.priceRangeFrom end) as weekly_rent_minimum,
(case prop.uvcf2
  when 'M' then (prop.priceRangeTo) * 12 / 52
  when 'D' then (prop.priceRangeTo) * 7
  else prop.priceRangeTo end) as weekly_rent_maximum,
prop.Bond as bond,
prop.intPropertyID as legacy_property_id,
prop.FeedReference as feed_ref,
prop.poa as rent_display_override,
prop.sleeps as holiday_sleeps,
prop.uvcf2 as rent_frequency,
Address.vcStreetNo as street_number,
Address.vcStreetNoTo as street_number_to,
Address.vcStreetName as street_name,
Address.vcStreetType as street_type,
Address.vcSuburb as suburb,
Address.chrState as state,
Address.intPostCode as postcode,
ISNULL(Address.vcSuiteNo, Address.vcFloorNo) as unit_number,
prop.sntSize as bedrooms,

(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (17,1108) order by pf.IntFeatureID
) as bathrooms,
(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (49,1110) order by pf.IntFeatureID
) as ensuites,
(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (86,1555) order by pf.IntFeatureID
) as parking_type,
(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (75,1554,917) order by pf.IntFeatureID
) as car_spaces,
(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (56,1113) order by pf.IntFeatureID
) as furnished,

(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (704,705,706) order by pf.IntFeatureID
) as share_preference_gender,

(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (707,708,709,710,711,725,731) order by pf.IntFeatureID
) as share_preference_age,

(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (716,717,718) order by pf.IntFeatureID
) as share_preference_lifestyle,

(select top(1) fv.vcFeatureValue from PropertyFeatureRel as pf
left outer join featurevalues as fv on fv.IntValueId=pf.IntValueId
where pf.IntPropertyID=%d and pf.IntFeatureID in (712,713,714,715) order by pf.IntFeatureID
) as share_flatmates,

(select pf.txtDetails from PropertyFeatureRel as pf
where pf.IntPropertyID=%d and pf.IntFeatureID=1499
) as share_main_language,

(select pf.txtDetails from PropertyFeatureRel as pf
where pf.IntPropertyID=%d and pf.IntFeatureID=1430
) as total_land_area,
(select pf.txtDetails from PropertyFeatureRel as pf
where pf.IntPropertyID=%d and pf.IntFeatureID=1633
) as energy_efficiency_rating,
(select top(1) pf.txtDetails from PropertyFeatureRel as pf
where pf.IntPropertyID=%d and pf.IntFeatureID in (5,1303) order by pf.IntFeatureID
) as property_age,
(select top(1) cast(case when bitValue=1 then 'true' else 'false' end as varchar(10)) from PropertyFeatureRel as pf
where pf.IntPropertyID=%d and pf.IntFeatureID in (1423,1334,1335,1336,1426)) as pets_allowed,
(select top(1) cast(case when bitValue=1 then 'true' else 'false' end as varchar(10)) from PropertyFeatureRel as pf
where pf.IntPropertyID=%d and pf.IntFeatureID in (1593,1594,1595)) as no_pets_allowed
from property prop
join Address on Address.intAddressId=prop.intAddressId
join PropertyCat on PropertyCat.PropertyCatID=prop.intPropertyCatID
join PropertyStatus on PropertyStatus.intStatusId=prop.intStatusID
join dbo.PropertySubCatRel pr on prop.intPropertyID=pr.intPropertyId
join PropertyCat pc on pr.intPropertyCatID=pc.PropertyCatID
where prop.intPropertyID=%d",
    $pid, $pid, $pid, $pid, $pid, $pid, $pid, $pid, $pid, $pid, $pid);
}

function getSqlForPhotos($pid)
{
    return sprintf ("
        select
      vcDescription as caption,
      vcFilePathName as original_url,
      vcThumbnailPath as thumbnail_url,
      vcDisplaySortOrder as [order],
      bitMainImage as [default],
      CONVERT(varchar,modifiedDate,126) as updated_at
    from PropertyImages
    where intPropertyID=%d", $pid);
}

function getSqlForFeatures($pid)
{
    return sprintf ("select
    pf.vcFeature as name,
    (select  vcFeature from PropertyFeature as pf1 where pf.IntParentFeatureID=pf1.IntFeatureID) as [group]
    from PropertyFeatureRel
    left outer join PropertyFeature pf on pf.IntFeatureID=PropertyFeatureRel.IntFeatureID
    left outer join FeatureValues on FeatureValues.IntValueId=PropertyFeatureRel.IntValueId
    left outer join FeatureValues_Desc on FeatureValues_Desc.IntValueDescID=FeatureValues.IntValueDescId
    where PropertyFeatureRel.IntPropertyID=%d and bitValue > 0", $pid);
}

function getSqlForLaundryFeature($pid)
{
    return sprintf ("select
    pf.vcFeature as [group],
    FeatureValues.vcFeatureValue AS name
    from PropertyFeatureRel
    left outer join PropertyFeature pf on pf.IntFeatureID=PropertyFeatureRel.IntFeatureID
    left outer join FeatureValues on FeatureValues.IntValueId=PropertyFeatureRel.IntValueId
    left outer join FeatureValues_Desc on FeatureValues_Desc.IntValueDescID=FeatureValues.IntValueDescId
    where PropertyFeatureRel.IntPropertyID=%d and PropertyFeatureRel.intValueId in (214,676)  order by PropertyFeatureRel.IntFeatureID", $pid);
}

function getSqlForContact($pid)
{
  $s = imageFolder();

    return sprintf ("select
    CASE WHEN pc.Surname IS NULL then person.FirstName ELSE pc.FirstName END as first_name,
        CASE WHEN pc.Surname IS NULL then person.Surname ELSE pc.SurName END as last_name,
    pc.personId as legacy_contact_id,
    person.BusPhone as phone_number, person.Mobile as mobile_number, person.Fax as fax,
    person.Email as email_address,
    person.Mobile as mobile_number,
    person.IsMainPropManager as is_main_property_manager,
    CAST(CASE WHEN member.ContactImageId IS NULL THEN NULL ELSE  '/Images/images_agents1/' + CAST(member.ContactImageId AS varchar(250)) END AS varchar(500)) AS photo
    from dbo.Property as prop
    INNER JOIN Person ON Person.PersonID = prop.intContactID
    LEFT OUTER join PersonContact as pc on pc.PersonID=Person.PersonContactID
    where prop.intPropertyID=%d", $pid);
}

function getSqlForLandlord($pid)
{
    return sprintf ("select
    person.FirstName as first_name,
    person.Surname as last_name,
    person.Email as email_address,
    person.BusPhone as phone_number,
    person.Mobile as mobile_number,
    person.TradingName as trading_name,
    contact.personId as legacy_landlord_id,
    addr.vcStreetNo as street_address,
    addr.vcStreetName as street_address_1,
    addr.vcSuburb as suburb,
    addr.intPostCode as postcode,
    addr.chrState as state
    from dbo.Property as prop
    join dbo.Person as person on person.MemberID=prop.intOwnerID
    inner join dbo.PersonContact as contact on contact.PersonId=person.PersonContactID
    left outer join dbo.Address as addr on addr.intAddressID=person.AddressID
    where prop.intPropertyID=%d
    and not exists (select MemberID from company where company.MemberID=prop.intOwnerID)",
    $pid);
}

function getSqlForCompany($pid)
{
  $s = imageFolder();

    return sprintf ("select
    main.FirstName as first_name,
    main.Surname as last_name,
    main.Email as email_address,
    main.Mobile as mobile_number,
    comp.CompanyName as name,
    comp.LicenseNo as trading_name,
    comp.Telephone_No as phone_number,
    comp.FeedReferenceID as feed_ref,
    comp.CompanyID as legacy_company_id,
    addr.vcAddress1 as street_address,
    addr.vcAddress2 as street_address_1,
    addr.vcSuburb as suburb,
    addr.intPostCode as postcode,
    addr.chrState as state,
    '/Images/images_agents1/' + banners.Path as banner
    from dbo.Property as prop
    join dbo.Person as person on person.MemberID=prop.intOwnerID
    left outer join dbo.PersonContact as contact on contact.PersonId=person.PersonID
    join CompanyContact as cc on cc.PersonID=person.Personid
    left outer join dbo.Company as comp on cc.CompanyID=comp.CompanyID
    join Person as main on comp.MemberID=main.MemberID
    left outer join dbo.LogosAndImages as banners on banners.OwnerID=comp.CompanyID and banners.ImageType=101
    left outer join dbo.Address as addr on addr.intAddressID=person.AddressID
    where prop.intPropertyID=%d", $pid);
}

function getSqlForInspectionTimes($pid)
{
    return sprintf ("
    select
    null as day_of_week,
    CONVERT(varchar, Convert(datetime, strInspectDate) + Convert(datetime,replace (strStartTime,'.',':')),126) as start_time,
    CONVERT(varchar, Convert(datetime, strInspectDate) + Convert(datetime,replace (strEndTime,'.',':')),126) as end_time
    from Inspections
    where PropertyID=%d
    UNION ALL
    select
    'monday' as day_of_week,
    cast (('1-1-1970 ' + CONVERT(varchar,sntFromHrs) + ':' + CONVERT(varchar,sntFromMins) + vcFromAMPM) as datetime) as start_time,
    cast (('1-1-1970 ' + CONVERT(varchar,sntToHrs) + ':' + CONVERT(varchar,sntToMins) + vcToAMPM) as datetime) as end_time
    from InspectionDetails
    where intPropertyID=%d and bitMondays=1
    UNION ALL
    select
    'tuesday' as day_of_week,
    cast (('1-1-1970 ' + CONVERT(varchar,sntFromHrs) + ':' + CONVERT(varchar,sntFromMins) + vcFromAMPM) as datetime) as start_time,
    cast (('1-1-1970 ' + CONVERT(varchar,sntToHrs) + ':' + CONVERT(varchar,sntToMins) + vcToAMPM) as datetime) as end_time
    from InspectionDetails
    where intPropertyID=%d and bitTuesdays=1
    UNION ALL
    select
    'wednesday' as day_of_week,
    cast (('1-1-1970 ' + CONVERT(varchar,sntFromHrs) + ':' + CONVERT(varchar,sntFromMins) + vcFromAMPM) as datetime) as start_time,
    cast (('1-1-1970 ' + CONVERT(varchar,sntToHrs) + ':' + CONVERT(varchar,sntToMins) + vcToAMPM) as datetime) as end_time
    from InspectionDetails
    where intPropertyID=%d and bitWednesdays=1
    UNION ALL
    select
    'thursday' as day_of_week,
    cast (('1-1-1970 ' + CONVERT(varchar,sntFromHrs) + ':' + CONVERT(varchar,sntFromMins) + vcFromAMPM) as datetime) as start_time,
    cast (('1-1-1970 ' + CONVERT(varchar,sntToHrs) + ':' + CONVERT(varchar,sntToMins) + vcToAMPM) as datetime) as end_time
    from InspectionDetails
    where intPropertyID=%d and bitThursdays=1
    UNION ALL
    select
    'friday' as day_of_week,
    cast (('1-1-1970 ' + CONVERT(varchar,sntFromHrs) + ':' + CONVERT(varchar,sntFromMins) + vcFromAMPM) as datetime) as start_time,
    cast (('1-1-1970 ' + CONVERT(varchar,sntToHrs) + ':' + CONVERT(varchar,sntToMins) + vcToAMPM) as datetime) as end_time
    from InspectionDetails
    where intPropertyID=%d and bitFridays=1
    UNION ALL
    select
    'saturday' as day_of_week,
    cast (('1-1-1970 ' + CONVERT(varchar,sntFromHrs) + ':' + CONVERT(varchar,sntFromMins) + vcFromAMPM) as datetime) as start_time,
    cast (('1-1-1970 ' + CONVERT(varchar,sntToHrs) + ':' + CONVERT(varchar,sntToMins) + vcToAMPM) as datetime) as end_time
    from InspectionDetails
    where intPropertyID=%d and bitSaturdays=1
    UNION ALL
    select
    'sunday' as day_of_week,
    cast (('1-1-1970 ' + CONVERT(varchar,sntFromHrs) + ':' + CONVERT(varchar,sntFromMins) + vcFromAMPM) as datetime) as start_time,
    cast (('1-1-1970 ' + CONVERT(varchar,sntToHrs) + ':' + CONVERT(varchar,sntToMins) + vcToAMPM) as datetime) as end_time
    from InspectionDetails
    where intPropertyID=%d and bitSundays=1",
    $pid, $pid, $pid, $pid, $pid, $pid, $pid, $pid);
}


?>