Home » Archives » February 2004 » workBLOGS . oTimeTotal

[ Previous entry: THOUGHTS . OpenSource ]
[ Next entry: Hardware . Lian Li PC-V1000 ]
02/23/2004:
workBLOGS . oTimeTotal


A little something something.

It's already 2:40AM, I'm done with a couple of Reports and I'm still online trying to annoy a VB6-lovin'-former classmate of mine.

To give you an overview of this late-night-to-early-morning little quest of mine, I'll start with the target task. The objective is to add those TimeTotal and FeeTotal amounts in the reports that deals on the usage of the internet/computer.

So it's something like this:

NAME	TIME IN	TIME OUT	TIME TOTAL	TOTAL FEE
Richelle	09:19:03	11:05:45	01:46:42	44.17
Marv	17:32:25	18:57:49	01:25:24	35.42
Teresa	20:27:22	21:15:05	00:47:43	19.58
			cTotalTime	nTotalFee

This is just part of the data and at 2AM I'm not sure if they're the right numbers so tone down that meticulous geek in you, I just want to present an overview.

Generating nTotalFee is easy using VisualFoxpro's Report Tool/Designer. You simply create a variable, let's say oTotalFee, store the value of the database field (USAGE.FeeTotal), set the initial value to 0 and click the SUM part on the Calculate frame. You can set the variable to reset at the end of the page or at the end of the report. Just that and hardworkin' VFP will do the things automatically for you in the background while your report is being generated.

The trickier part is the cTotalTime since it is a character (well I made it that way months ago probably to make things harder for me in the future). '01:00:00' + '00:00:25' is equal to '01:00:0000:00:25' and not '01:00:25'. I tried another couple of barfed-out-rushed patch solutions which in the end didn't work.

That was when I remembered that there's a CTOT() function. But CTOT() needs additional characters such as CTOT("2000-10-24T13:30:00") and the other time/date-related functions I found were of no use, well at least in this situation. Besides I was secretly praying that none of these reserved functions would work so that I can have an excuse to do things the 'old school' way.

But before that I was even considering the lame approach of simply dividing nTotalFee with the RatePerHour and a little TimeConversion() algorithm to derive cTotalTime... a sort of a short circuit cheat. But that my friend, is not the way things are done especially when I'm in the mood to punish myself.

So choosing the 'right' path, I entered the following manipulation inside the variable part:

(val(substr(usage.timetotal, 1, 2))*3600) +
(val(substr(usage.timetotal, 4, 2))*60) +
(val(substr(usage.timetotal, 7, 2)))

In the report, a field control is added to store this code:

NUMTOSTR(alltrim(str(int(oTotalTime/3600))), 2) + ':' +
NUMTOSTR(alltrim(str(int((oTotalTime%3600)/60))), 2) + ':' +
NUMTOSTR(alltrim(str((oTotalTime%3600)%60)), 2)

NumToStr is a function I use to convert a number to a character field with additional 'pads'. Something like NumToStr(97, 5) would yield '00097'. It is quite useful too in IDNumber/Code formatting and other related functions.

So with that... the quest was completed.

Perhaps there are better solutions for this but for now this will do, donkey. It gives you a little taste of how things are done the 'good-old-school' way. Ah well at least a step above the older assembly/machine code approach.