Curne) Simon Conrad-Armes
10/4/2004 5:22:00 PM
I thought this was cool enough to share.
At work, once a month, I have to send in a time-sheet in a specific
format. Today I was looking for a clever way of converting the raw data
exported from my time-logger (JobLog) into that format. After messing
around with regular expressions for a while my Ruby program was longer
than fifty lines and I thought to myself, "there must be a better way."
What I really wanted was the power of Ruby syntax but to run queries
against my data like a relational database. So I thought of
sqlite-ruby, which I often use for application development. The
combination of the tabular data (making a simple split/insert real
easy) and the inline queries in the HERE document makes it real easy to
create and maintain.
This is the code I wrote in 20 minutes:
--CODE START--
#!/usr/bin/ruby
#JobLog:<id>\t<client/project/
job>\t<date>\t<time>\t<minutes>\t<filename>
require 'rubygems';require_gem 'sqlite-ruby'
require 'ftools'
begin
db = SQLite::Database.new('temp')
db.execute 'create table job (id int, client text, project text, job
text, date text, time text, minutes int, filename text)'
while gets "\r"
job = chomp.split(/\t/)
job[1,1] = job[1].split '/'
db.execute 'insert into job values (?,?,?,?,?,?,?,?)', *job
end
puts <<EOS
#{db.get_first_value 'select filename from job'}, #{ENV['USER']}
#{db.execute('select client, project, round(sum(minutes)/60.0, 2), job
from job group by client, project, job').collect {|row| (row.join ', ')
}.join "\n" }
----------------------------------------
#{db.execute('select client, round(sum(minutes)/60.0, 2) from job group
by client').collect {|row| (row.join ', ') }.join "\n" }
========================================
#{db.get_first_value 'select round(sum(minutes)/60.0, 2) from job'}
EOS
ensure
db.close if defined? db
File.rm_f 'temp'
end
--CODE END--
--SAMPLE OUTPUT START--
client1, project1, 6.82, job1
client1, project1, 1.52, job2
----------------------------------------
client1, 8.33
========================================
8.33
--SAMPLE OUTPUT END--
Simon Conrad-Armes