I have been working on a program called Ctrlpanlel for some time now and I have it up on Heroku and it is working... mostly with only a few exceptions. For background it used to run on Ruby 2.2.2 and Rails 4.2.1 and I have upgraded it to Ruby 2.7.2 and Rails 6.1.3.2
One of the functions this app does is weekly expense reports and I am having a problem with some of the query logic because it was originally written using a gem called squeel. I am very new to Rails and Activerecord although I am learning I am trying to un-squeel the code in a few places. I believe I have the first couple of queries fixed (I think at least) and it goes past the first couple of part where I was running into an error, I can run the task by running rails weekly_expense_reports and it goes to the letter opener with no issues and shows the people and the number of charges they each have but then it also says the following error for each one of them:
ERROR processing Smith John because: undefined method `order' for #ActiveRecord::QueryMethods::WhereChain:0x000000001067ee08
I changed the employees name to John Smith just to be clear. :)
It does this for each person so I know either the first query I thought I fixed properly I did NOT, or there is another issue in the controller in the section that has the .order lines in it which is what it is referring to. I have learned that sometimes in Rails when it points to a bad method sometimes it is the data supplying the method that is the issue and not necessarily the method itself. In this case I am not sure which one it is.
The first line I changed is:
@users = User.where.not(:expense_reports_to_user_id => nil)
The second line I changed is:
charge_count = Charge.between_times( start_date, end_date, field: :post_date ).
where(:cardholder_name => user.cardholder_name).count
Here is the TASK in scheduler.rake
task :weekly_expense_reports => :environment do
if Time.zone.now.wday == 2
log = []
# Squeel logic I had to de-squeel v0.0040 on 5/10/2021 Scott Milella
# @users = User.where{ expense_reports_to_user_id.not_eq nil }
@users = User.where.not(:expense_reports_to_user_id => nil) # <- First Line I changed
message = "Evaluating #{@users.count} users"
puts message
log << message
start_date = Time.zone.today - 7.days
end_date = Time.zone.today - 1.days
if Charge.between_times( start_date, end_date, field: :post_date ).any?
date_ranges = []
if start_date.month == end_date.month
date_ranges << { start_date: start_date, end_date: end_date }
else
date_ranges << { start_date: start_date, end_date: start_date.end_of_month }
date_ranges << { start_date: end_date.beginning_of_month, end_date: end_date }
end
message = "Found charges in date ranges: #{date_ranges.join(" to ")}"
puts message
log << message
@users.each do |user|
puts "Checking #{user.name} for charges..."
# Below here is the next query I changed...
# More squeel code yet again, v0.0040 on 5/10/2021 Scott Milella
# charge_count = Charge.between_times( start_date, end_date, field: :post_date ).
# where{ cardholder_name.eq my{ user.cardholder_name } }.count
charge_count = Charge.between_times( start_date, end_date, field: :post_date ).
where(:cardholder_name => user.cardholder_name).count
if charge_count > 0
message = "Found #{charge_count} charges for #{user.name}"
puts message
log << message
begin
ExpenseMailer.weekly_report(user, date_ranges).deliver_now!
sleep 1
message = "Successfully emailed charges for #{user.name}"
puts message
log << message
rescue Exception => error
message = "ERROR processing #{user.name} because: #{error}"
puts message
log << message
end
end
end
# finished processing emails, send report
log << "Finished Processing Expense Reports"
ActionMailer::Base.mail(
from: "tech@des.direct",
to: "terrym@des.direct",
subject: "Expense Report Summary",
body: log.join("\n\n").html_safe
).deliver_now
else
# no charges found, send notification
emails = ["terrym@des.direct", "laurah@des.direct"]
puts "No charges were found, skipping expense report."
ActionMailer::Base.mail(
from: "tech@des.direct",
to: emails,
subject: "[Expense Reports] No Charges Found, Cannot Process",
body: "Error, no charges were uploaded for the period of #{start_date} to #{end_date}, please reprocess manually."
).deliver_now
end
log = nil
end
end
Here is the model just in case:
class Charge < ActiveRecord::Base
attr_accessible :card_last_four, :cardholder_name, :cardholder_last_name, :cardholder_origin,
:post_date, :merchant_name, :amount, :trans_type, :gl_code, :mcc_code,
:mcc_description, :gas_region, :transaction_date
before_save :add_cardholder_last_name
def add_cardholder_last_name
if cardholder_name
self.cardholder_last_name = cardholder_name.split(" ").last
end
end
def self.allowed_user_ids
administration_user_ids
end
def self.administration_user_ids
[ 8,
9,
36,
50,
128 #
] # this includes (not in order)
end
def update_gl_code
self.gl_code = Charge.mcc_to_gl_table[mcc_code.to_i]
self.save
end
def self.mcc_to_gl_table
{
0 => 740,
}
end
def self.cardholder_name_select
Charge.pluck(:cardholder_name).
uniq.
delete_if{ |x| x.in?( Charge.cardholder_reject_list ) }.
reject(&:blank?).
sort_by{ |k| k.split(" ").last }
end
def self.cardholder_reject_list
[
"JOHN DOE",
"JOHN DOE",
"JOHN DOE",
"JOHN DOE",
]
end
def self.cardholder_name_to_details
{
"ACCOUNTS PAYABLE" => { branch: "redding", region: "TRAVEL" },
}
end
def self.import_csv_text(csv_text)
require 'csv'
csv = CSV.parse( csv_text, headers: true )
csv.each do |raw|
row = raw.to_hash
merchant_name = row["Merchant Name"].strip
if merchant_name == "PAYMENT - THANK YOU"
card_last_four = 0
cardholder_name = "Payment"
else
if row["Originating Account Number"].present?
card_last_four = row["Originating Account Number"][-4, 4]
cardholder_name = row["Cardholder Name"].strip
else
cardholder_name = "ACCOUNTS PAYABLE"
card_last_four = 1234
end
end
transaction_date = Chronic.parse( row["Tran Date"] ).to_date
post_date = Chronic.parse( row["Post Date"] ).to_date
amount = row["Amount"].remove('$')
trans_type = row["Tran Type"].strip
mcc_code = row["MCC Code"].to_i
mcc_description = row["MCC Description"].strip
reference_number = row["Reference Number"].strip
gl_code = Charge.mcc_to_gl_table[ mcc_code ]
cardholder_details = Charge.cardholder_name_to_details[ cardholder_name ]
if cardholder_details
cardholder_origin = cardholder_details[:branch]
gas_region = cardholder_details[:region]
end
@charge = Charge.where(reference_number: reference_number, amount: amount).first_or_create
@charge.card_last_four = card_last_four
@charge.transaction_date = transaction_date
@charge.post_date = post_date
@charge.merchant_name = merchant_name
@charge.cardholder_name = cardholder_name
@charge.trans_type = trans_type
@charge.mcc_code = mcc_code unless @charge.mcc_code.present?
@charge.mcc_description = mcc_description unless @charge.mcc_description.present?
@charge.gl_code = gl_code unless @charge.gl_code.present?
@charge.cardholder_origin = cardholder_origin unless @charge.cardholder_origin.present?
@charge.gas_region = gas_region unless @charge.gas_region.present?
@charge.save
end
end
end
Here is the complete error from the rails panel with the employee names changed:
From:
tech@email.domain
Subject:
Expense Report Summary
Date:
May 11, 2021 03:41:12 PM Pacific Daylight Time
To:
Tech@email.domain
Evaluating 38 users
Found charges in date ranges: {:start_date=>Tue, 04 May 2021, :end_date=>Mon, 10 May 2021}
Found 10 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000cc282e0>
Found 3 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x0000000009970410>
Found 1 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x00000000106fc830>
Finished Processing Expense Reports
I appreciate any advice, recommendations, solutions anyone has to offer!
Thank You,
Scott
Here is a copy strait from the rails panel, this is the only way I know of to manually trigger this script. Normally it would be called from Heroku Scheduler.
scottm@RED-IT-LAP-0001 MINGW64 /d/rails/ctrlpanel (master)
$ rake weekly_expense_reports
Evaluating 38 users
Found charges in date ranges: {:start_date=>Wed, 05 May 2021, :end_date=>Tue, 11 May 2021}
Checking John Doe for charges...
Found 8 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000f06fdb0>
Checking John Doe for charges...
Found 2 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000f3bec78>
Checking John Doe for charges...
Checking John Doe for charges...
Found 5 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000dfc0a50>
Checking John Doe for charges...
Found 1 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000f5e1fa0>
Checking John Doe for charges...
Found 1 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000f653420>
Checking John Doe for charges...
Found 1 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000dbfbe38>
Found 5 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000000f70ac38>
Checking John Doe for charges...
Checking John Doe for charges...
Found 5 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x0000000010614328>
Checking John Doe for charges...
Found 1 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x00000000106cc810>
Checking John Doe for charges...
Found 3 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000001072c6c0>
Checking John Doe for charges...
Found 1 charges for John Doe
ERROR processing John Doe because: undefined method `order' for #<ActiveRecord::QueryMethods::WhereChain:0x000000001078d2e0>
scottm@RED-IT-LAP-0001 MINGW64 /d/rails/ctrlpanel (master)
$
As you can see above it pretty much gives me the very same information as what was on the email which is not very helpful. Sorry that I provided too much code, I thought I only provided the necessary code around the issue and people usually ask me for more.
The lines it is referencing as having the problem are in the charges_controller.rb but I am concerned that the queries I modified (and possibly incorrectly) might be the reason it is saying the method is undefined, because the controller logic looks ok to me? But then again I am very new and still struggling to understand the Rails logic. The method from the control is below::
def index
if current_user.id.in?( Charge.administration_user_ids ) || current_user.cardholder_name.present?
params[:q] = {} if params[:q].blank?
params[:q][:post_date_gteq] = Date.today.beginning_of_month - 1.month if params[:q][:post_date_gteq].blank?
# pre-set the cardholder name if they've been assigned
# unless they're an administrator
if current_user.cardholder_name.present? && !current_user.id.in?( Charge.administration_user_ids )
params[:q]["cardholder_name_eq"] = current_user.cardholder_name
end
@q = Charge.ransack(params[:q])
# done too quickly, seriously needs rework
if params[:q][:cardholder_name_eq].present?
if current_user.id.in?( Charge.administration_user_ids )
@charges = @q.result.order(:gl_code, :post_date).group_by(&:gl_code)
else
@charges = @q.result.order(:cardholder_name, :post_date)
end
else
@charges = @q.result.order(:cardholder_name, :post_date)
end
else
redirect_to root_path, notice: "Nope."
end
end
To be clear I renamed all of the employee names to John Doe. :)
If there is anything else you need please let me know and thank you again! Scott
I removed the log file since it was made clear that it was pretty much the same information in a different way.
Here is the file in app/mailers/expense_mailer.rb
class ExpenseMailer < ApplicationMailer
def weekly_report(user, date_ranges)
require 'prawn'
date_ranges.each do | range |
start_date = range[ :start_date ]
end_date = range[ :end_date ]
@charges = Charge.between_times( start_date, end_date, field: :post_date ).
where{cardholder_name.eq my { user.cardholder_name } }.
order("post_date asc")
if @charges.any?
prawn_output = ExpenseReport.new(user.cardholder_name, start_date, end_date, @charges).render
prawn_object = CombinePDF.parse(prawn_output, page_layout: :landscape)
combined_file = CombinePDF.new
template_path = 'app/assets/pdfs/DES_Expense_Report_W4.pdf'
template_page = CombinePDF.load( template_path, page_layout: :landscape ).pages[0] ; true
prawn_object.pages.each do |page|
template_page = CombinePDF.load( template_path, page_layout: :landscape ).pages[0] ; true
template_page[:Annots].each { |a|
a[:referenced_object][:T] = 'Combine' + SecureRandom.hex(7) + 'PDF'
}
combined_file << template_page
end
prawn_object.pages.each_with_index do |page, i|
combined_file.pages[i] << prawn_object.pages[i]
end
page_total = combined_file.pages.count
combined_file.pages.each_with_index do |page, i|
page.textbox( "Page #{i + 1}/#{page_total}", x: 680, y: -520, max_font_size: 10 )
end
attachments["#{user.cardholder_name} [ #{start_date.strftime("%m/%d/%Y")} - #{end_date.strftime("%m/%d/%Y")} ].pdf"] = combined_file.to_pdf
end
end
to_with_name = %("#{user.expense_report_manager.name}" <#{user.expense_report_manager.email}>)
# to_with_name = %("John" <johnd@mail.domain>)
from_with_name = %("CtrlPanel" <tech@mail.domain>)
mail to: to_with_name, bcc: 'tech@mail.domain
from: from_with_name, subject: "Expense Report for #{user.name} [ #{date_ranges.first[:start_date].strftime("%m/%d/%Y")} - #{date_ranges.last[:end_date].strftime("%m/%d/%Y")} ]"
end
end
Thank You, Scott
The answer turned out to be in the Expense_Mailer.rb file. It had logic from an old gem called squeel.
This line had the bad logic in it:
where{cardholder_name.eq my { user.cardholder_name } }.order("post_date asc")
Changed it to:
where(:cardholder_name => user.cardholder_name).order("post_date asc")
Then it started working properly. Thank You again @Denny Mueller if you had not informed me that it was calling out to another file I wouldn't have found that other logic. Still trying to fully understand how rails is all tied together.