I’ve been wearing my management hat for many months, with little time to do any actual programming. Since this is our IT department’s slow time of the year, I like to use this time to search for performance improvements (while also putting my programming skills to use before they die from inactivity).
One of the tasks my programmers hate is tracking their billable time on timesheets. We use a homegrown (legacy) jobcost system company-wide. This system tracks client jobs, tasks, and hours, which we use to bill employee time to clients. In addition, the IT department uses Bugzilla to track IT tasks. Bugzilla provides far more details than the jobcost system and our company provides these details to the client to substantiate billable IT work.
Bugzilla has recently added an option to track hours (personally, I had been waiting years for this feature). I figured a fun and productive project would be to grab hours from Bugzilla and import directly into our legacy jobcost system, eliminating the need for my programmers to track their hours on timesheets.
So, we begin with the MySQL select statement. The data I need is fairly simple: bugID, employee, date, hours worked, keyword (this represents the client in our Bugzilla configuration), and component (this represents the task code). For reasons I don’t understand, Bugzilla decided to place “hours” in the longdescs table. After many variations, I found the following select to work well for the data I need to capture.
SELECT longdescs.bug_id, longdescs.who, longdescs.bug_when, longdescs.work_time, bugs.short_desc, products.name, components.name, keyworddefs.name, profiles.login_name INTO OUTFILE "testbugs2.txt" FROM longdescs, bugs, products, components, keywords, keyworddefs, profiles WHERE longdescs.bug_id = bugs.bug_id AND bugs.product_id = products.id AND bugs.component_id = components.id AND bugs.bug_id = keywords.bug_id AND keywords.keywordid = keyworddefs.id AND longdescs.who = profiles.userid AND longdescs.bug_when >= "2009-11-22 00:01" AND longdescs.bug_when <= "2009-11-22 23:59" AND longdescs.who IN ( SELECT userid FROM profiles WHERE userid = "2" OR userid = "3" OR userid = "4" OR userid = "33")
The nested SELECT ensures I get only the IT employees, rather than all Bugzilla users.
Next, I put the SELECT into a php program that can be scheduled in crontab to run every night for the current day’s activity. Instead of the fixed dates above, I just use the php date() function like this:
Then I change the select from:
AND longdescs.bug_when >= "2009-11-22 00:01" AND longdescs.bug_when <= "2009-11-22 23:59"
AND longdescs.bug_when LIKE "%$my_date%"
The tough part will be getting my employees to update their time every day. As an alternative, I can select and update the longdescs records with a flag that identifies them as pulled, then change the select to simply pull all non-flagged rows. This is a better way to ensure I get all of the proper data.
On the legacy app side (D3 application), I wrote an import program that grabs the data, maps the keywords and components to jobs and tasks, converts the date format, and updates the time. I assign a unique batch code to each update run, in case the data needs to be pulled out for some reason.