Time Travel (Memento Pattern) SQL queries, using state_machine and state_machine_transition record

time_traveller.rb

module ReportersCommon::TimeTraveller
  def investment_ids_for_state(state, date)
    available_states = Set.new [
                                   Investment::PENDING_BID,
                                   Investment::CONFIRMED_BID,
                                   Investment::ACTIVE,
                                   Investment::COMPLETED,
                                   Investment::DEFAULT,
                                   Investment::CANCELLED,
                                   Investment::EXPIRED_BID
                               ]
    state = available_states.intersection([state]).to_a.last
    date = date.strftime('%Y-%m-%d')
    state_changed_sql = "
        SELECT
          prev.investment_id,
          prev.from as prev_from,
          prev.to as prev_to,
          next.from as next_from,
          next.to as next_to,
          to_date(cast(prev.created_at as TEXT), 'YYYY-MM-DD') as prev_created_at,
          to_date(cast(next.created_at as TEXT), 'YYYY-MM-DD') as next_created_at
        FROM investment_state_transitions as prev
        INNER JOIN investment_state_transitions as next
        ON
          prev.investment_id = next.investment_id
          AND
          prev.to = next.from
        WHERE
          prev.to = '#{state}'
          AND
          date(prev.created_at) <= '#{date}'
          AND
          date(next.created_at) > '#{date}'
        ORDER BY prev.investment_id ASC
       "
    state_changed_result = ActiveRecord::Base.connection.execute state_changed_sql
    state_changed_ids = state_changed_result.to_a.map{|x| x['investment_id']}.uniq
    state_unchanged_sql = "
      SELECT investments.*, trans.*
      FROM investments
      INNER JOIN investment_state_transitions AS trans
      ON
          investments.id = trans.investment_id
          AND
          trans.to = '#{state}'
          AND
          date(trans.created_at) <= '#{date}'
      WHERE
          -- this is required because we want the ones that haven changed state
          investments.state = '#{state}'
      ORDER BY investments.id
    "
    state_unchanged_result = ActiveRecord::Base.connection.execute state_unchanged_sql
    state_unchanged_ids = state_unchanged_result.to_a.map{|x| x['investment_id']}.uniq
    return state_changed_ids.concat(state_unchanged_ids).uniq
  end

  def investment_value(state, date=Date.today)
    investments = Investment.where id: investment_ids_for_state(state, date)
    investments.sum(:invest_amount)
  end

  def loan_ids_for_state(status, date)
    available_states = Set.new Loan::STATUSES
    status = available_states.intersection([status]).to_a.last
    date = date.strftime('%Y-%m-%d')
    state_changed_sql = "
        SELECT
          prev.loan_id,
          prev.from as prev_from,
          prev.to as prev_to,
          next.from as next_from,
          next.to as next_to,
          to_date(cast(prev.created_at as TEXT), 'YYYY-MM-DD') as prev_created_at,
          to_date(cast(next.created_at as TEXT), 'YYYY-MM-DD') as next_created_at
        FROM loan_loan_status_transitions as prev
        INNER JOIN loan_loan_status_transitions as next
        ON
          prev.loan_id = next.loan_id
          AND
          prev.to = next.from
        WHERE
          prev.to = '#{status}'
          AND
          date(prev.created_at) <= '#{date}'
          AND
          date(next.created_at) > '#{date}'
        ORDER BY prev.loan_id ASC
       "
    state_changed_result = ActiveRecord::Base.connection.execute state_changed_sql
    state_changed_ids = state_changed_result.to_a.map{|x| x['loan_id']}.uniq
    state_unchanged_sql = "
      SELECT loans.*, trans.*
      FROM loans
      INNER JOIN loan_loan_status_transitions AS trans
      ON
          loans.id = trans.loan_id
          AND
          trans.to = '#{status}'
          AND
          date(trans.created_at) <= '#{date}'
      WHERE
          -- this is required because we want the ones that haven changed state
          loans.loan_status = '#{status}'
      ORDER BY loans.id
    "
    state_unchanged_result = ActiveRecord::Base.connection.execute state_unchanged_sql
    state_unchanged_ids = state_unchanged_result.to_a.map{|x| x['loan_id']}.uniq
    return state_changed_ids.concat(state_unchanged_ids).uniq
  end
end