I have the following definition which is looking at an apache database,
and then a sql database. I need to run a sql statement and sum 2
columns. When I do this, the result that is getting returned is not
even close. It's not what it should be.
I search the apache database for a list of projects, then for each
project, I search sql for any records that have that project. I want to
sum their project costs column, where the project id's are equal.
This is the code:
# CREATE MAP OF PROJECT IDS IN CURRENT OPEN APACHE CASE
t_Case = $current_case.search("special-metadata:ProjectID AND
has-exclusion:0")
t_projectIDs = t_Case.map {|item| item.getspecialMetadata['ProjectID']}
t_projectIDs = t_projectIDs.compact.uniq.sort
connection = java.sql.DriverManager.getConnection(db_url, user_name,
password)
#DEFINITION TO SEARCH THE CURRENT CASE, BY BATCH, FOR ANY DOCUMENTS THAT
HAVE UPDATED TEXT
def select_items(connection, table_oname, tablelog, t_projectIDs)
$t_projectids = Array.new
t_projectIDs.each do |projCheck|
projCosts = $current_case.search("path-name:#{projCheck} AND
previous-version-docid:* AND has-exclusion:0")
puts projCheck
dedeupprojCosts = $utilities.item_utility.deduplicate(projCosts)
projdedupCosts = dedeupprojCosts.count
puts projdedupCosts.to_s + " is the unique cost sum"
if projdedupCosts > 0
puts "in projdedupCosts count is greater than 0"
t_projdedupinterest = dedeupprojCosts.map { |pages|
pages.getProperties["Interest"] == nil ?
pages.getProperties["InterestVar"] : pages.getProperties["Interst"] }
t_projdedupinterestSum = t_projdedupinterest.inject(0, :+)
sqlsame = "SELECT sum(projectCost) AS sumProjCost, sum(interest AS
sumInterest FROM " + table_oname + " WHERE AssetTagBatch =
'#{projCheck}'"
statement = connection.prepareStatement(sqlsame)
rows = statement.execute_query
#IF THERE IS A RESULT FOR THE QUERY THAT THE SQL OCR TABLE HAS A
MATCHING BATCH AND COUNTS, THEN REPORT TO THE CONSOLE AND LOG TABLE THAT
THE BATCH IS FULLY REPORTED
if rows != nil
sqlprojcostsum = sqlsame[0]
sqlinterestsum = sqlsame[1]
puts sqlprojcostsum
puts "that was the sqlprojcostsum"
exit 0
end
end
end
end
select_items(connection, table_projname, table_namelog, t_projectIDs)
···
---------------------
If anyone can help, I would appreciate it.
Right now the puts sqlprojcostsum is reporting 83, but for the first
project, it should actually be 1.
--
Posted via http://www.ruby-forum.com/.