app_12809.rb

# APP-12809 - Data Science: Uncategorized Transactions Amount on P&L
# Description
# Pre-requisites:
#
# companies.status = active
# billing_infos.full_service = 1
# transactions.post_date = 1/1/17-12/31/17
# category = uncategorized
# company_infos.doing_test_mode = false
# Columns:
#
# money in
# money out
# total (net)
# Things I had to modify to use it with the read-only local prod copy:
#
# diff --git a/app/services/journal_entry_processor.rb b/app/services/journal_entry_processor.rb
# index 5a868db2ce..07e3522b5b 100644
# --- a/app/services/journal_entry_processor.rb
# +++ b/app/services/journal_entry_processor.rb
# @@ -4,6 +4,7 @@ class JournalEntryProcessor
#    # Aggregates journal entry amounts based on account and type
#    # Useful for getting running totals (among other things)
#    def self.fetch_journal_entry_sums(options, use_cache = true)
# +    use_cache = false
#      company = options[:company]
#      date_end = options[:date_end] || Date.today
#      date_start = options[:date_start] || company.company_info.try(:balance_sheet_import_date)

def path
  return @path if defined?(@path)
  timestamp = Time.now.to_s.gsub(':', ' ').gsub(' ', '_').sub('+', '_')
  dir = '/tmp'
  filename = "app_12809_#{timestamp}.csv"
  @path = File.join(dir, filename)
end

def generate_csv
  CSV.open(path, 'w') do |csv|
    headers = [
      'company_id',
      'company_name',
      'status',
      'uncategorized_revenue',
      'uncategorized_spending',
      'net'
    ]
    csv << headers
  end
end

def append_to_csv(row)
  CSV.open(path, 'a') do |csv|
    csv << row
  end
end

def log(message)
  time = Time.now
  line = "[#{time}] #{message}"
  puts line
  log_path = path + '.log'
  File.open(log_path, 'a+') do |file|
    file.puts line
  end
end

def get_uncategorized_revenue(response)
  response.
    try(:[], :income).
    try(:[], :revenue).
    try(:[], :master_accounts).
    try(:find) do |category|
      category[:name] == 'Uncategorized'
    end.
    try(:[], :totals).
    try(:find) do |total|
      total[:date] == 'Total'
    end.
    try(:[], :amount).
    try(:to_f)
end

def get_uncategorized_spending(response)
  response.
    try(:[], :expense).
    try(:[], :spending).
    try(:[], :master_accounts).
    try(:find) do |category|
      category[:name] == 'Uncategorized'
    end.
    try(:[], :totals).
    try(:find) do |total|
      total[:date] == 'Total'
    end.
    try(:[], :amount).
    try(:to_f)
end

generate_csv

script_time_start = Time.now
log('Starting...')

# We also want to get inactive companies
# company_ids = [1885, 32625, 33220, 34553]
# company_ids = [33220]
# companies = Company.find(company_ids)
companies = Company.full_service.no_test_mode

log("Fetched #{companies.count} companies!")

date_start = Date.parse('1/1/2017')
date_end = Date.parse('12/31/2017')

companies.each_with_index do |company, index|
  iteration_time_start = Time.now
  company_id = company.id
  log("Started company #{company_id}... (#{index + 1} of #{companies.count})")
  company_name = company.try(:name)
  status = company.try(:status)
  profit_loss_params = {
    company: company,
    start_date: date_start,
    end_date: date_end,
    period: ProfitLossServices::ProfitLoss::PERIODS[:ANNUAL]
  }
  service = ProfitLossServices::ProfitLoss.new(profit_loss_params)
  response = Presenter::ProfitLoss.api_format(service)
  uncategorized_revenue = get_uncategorized_revenue(response) || 0.0
  uncategorized_spending = get_uncategorized_spending(response) || 0.0
  net = uncategorized_revenue.try(:-, uncategorized_spending) || 0.0
  row = [
    company_id,
    company_name,
    status,
    uncategorized_revenue,
    uncategorized_spending,
    net
  ]
  append_to_csv(row)
  iteration_time_end = Time.now
  iteration_duration = iteration_time_end - iteration_time_start
  log("Finished company #{company_id}! (took #{iteration_duration.round(2)} seconds)")
end

script_time_end = Time.now
script_duration = script_time_end - script_time_start
log("Done! (took #{script_duration.round(2)} seconds)")
log("Export can be found at #{path}")