Skip to content

Instantly share code, notes, and snippets.

@DavidEdwards1

DavidEdwards1/new_banking_rollout_2.rb

Last active November 23, 2017 22:43
Show Gist options
  • Save DavidEdwards1/eff5fc7342715995f46ba3dd2e805a1a to your computer and use it in GitHub Desktop.
Save DavidEdwards1/eff5fc7342715995f46ba3dd2e805a1a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
new_banking_rollout_2.rb
switch_to_slave_db
# not going to be offered if they have ever had mfa bank feed
# so just check for created date of feeds with yodlee_mfa_type
company_bank_feeds = BankFeed.joins(:bank_account).joins(:bank_service).
where('bank_services.yodlee_mfa_type is not null').
group('bank_accounts.company_id').minimum(:created_at)
all_companies = Company.joins(:subscription).
where("subscriptions.cancelled_at > '2017-01-01' or subscriptions.cancelled_at is null").
where(:type => 'UkLimitedCompany').
pluck(:id,'subscriptions.channel',:vat_registration_effective_date,:vat_deregistration_effective_date);
stock_first_added = StockItem.group(:company_id).minimum(:created_at);
direct_companies_first_payment = SubscriptionPayment.joins(:company).joins(company: :subscription).
where("companies.type = 'UkLimitedCompany'").
where("subscriptions.channel = 'Direct'").
where("subscriptions.account_type = 'Live'").
group(:company_id).minimum(:created_at);
other_companies_first_payment = Company.joins(:setup_stage).joins(:subscription).
where("subscriptions.channel != 'Direct'").
where("company_setup_stages.stage_type = 'SetupDone'").
where(:type => 'UkLimitedCompany').
where("subscriptions.account_type = 'Live'").
pluck(:id, 'company_setup_stages.created_at').to_h;
companies_first_payment = direct_companies_first_payment.merge(other_companies_first_payment);
company_data = {}
file = "/data/results/new_banking_rollout.csv"
CSV.open(file, "w") do |csv|
all_companies.each do |company|
id = company[0]
channel = company[1]
vat_reg_date = company[2]
vat_dereg_date = company[3]
next unless companies_first_payment.key?(id)
company_data[id] = {
"company_id": id,
"channel": channel,
"previous_bank_feeds": 0,
"current_bank_feeds": 0,
"previous_stock": 0,
"current_stock": 0,
"previous_vat_registration": 0,
"current_vat_registration": 0,} if !company_data.key?(id)
#find first_compare_date for company
first_compare_date = [companies_first_payment[id].to_date,Date.new(2017,1,1)].max
# check for bank feeds
if company_bank_feeds.key?(id)
if company_bank_feeds[id] <= first_compare_date
company_data[id][:previous_bank_feeds] = 1
company_data[id][:current_bank_feeds] = 1
else
company_data[id][:current_bank_feeds] = 1
end
end
#check for stock
if stock_first_added.key?(id)
# compare addition date to first compare
if stock_first_added[id] <= first_compare_date
company_data[id][:previous_stock] = 1
company_data[id][:current_stock] = 1
else
company_data[id][:current_stock] = 1
end
end
#check for vat registration
if vat_reg_date.present? #there has been vat registration
if !vat_dereg_date.present? #no deregistration
#registered before first_compare_date?
company_data[id][:current_vat_registration] = 1
if vat_reg_date <= first_compare_date #registration before first date
company_data[id][:previous_vat_registration] = 1
end
elsif vat_reg_date > first_compare_date #registered after first date
company_data[id][:current_vat_registration] = 1
elsif first_compare_date.between?(vat_reg_date,vat_dereg_date)
company_data[id][:previous_vat_registration] = 1
end
end
csv << company_data[id].keys if csv.lineno.zero?
csv << company_data[id].values
end
end;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment