Mega Code Archive
The dating game
<![CDATA[
The Perpetual Newbie - Log Entry #6.1
This article first appeared on http://www.undu.com
If you have parameterized queries, you probably are getting date ranges for one field or another in the process. A typical need might be a sales total report for a specific time.
The simple solution is to set up a couple of datepickers and use the params function of the query to assign the value. Personally, I like to build the query dynamically each time. It's a tad slower, but I can see the whole query in my programming code and that's frequently a boon.
Additionally, I like to offer my users a quick method to enter such nebulous time frames as this month, last quarter or year to date. So, what follows is how I build in a Date Ranger into my applications.
The first step is to acquire the ESB Dates freeware library. Glen Crouch and contributors have created a great resource at the web site: www.esbconsult.com.au
While you are at the ESB site, grab the freeware libraries for math and for routines. Each has code included and his top-notch programming. And have a look at the Stats program that is the main feature of the site. If you do ANY fancy math in your program, then this is the tool for you.
I use the Date Ranger form to also serve as a confirmation that the user wants to run the report. It has two datepickers (if you've been following other threads on the web, you'll know about the recent fears about the native Delphi pickers. I use Orpheus's version instead, with its ability to just type in short english phrases for dates. Orpheus is available from TurboPower at www.turbopower.com). Set the two pickers to today's date (or the most common date range if there is one overwhelmingly popular requirement) in the form create proc.
One to the advantages of using the Orpheus control is that is has an easy function to convert dates into four-digit year strings. It's called DateString and requires a mask, as you will see.
Inbetweeen the two datepickers, named dFrom and dTo respectively, is a button with a simple TO caption. It serves as a label AND a conduit to a popup menu to allow for easy picking of a set date range. The procedure for this button looks like this:
procedure TFrmReport.BtnToClick(Sender: TObject);
begin
pmDateRanger.Popup(FrmReport.Left + 120, FrmReport.Top + 84);
end;
In forcing the calling of the popup, you have to tell it where to pop up. You can experiment and get absolute co-ordinates, or you can use a formula that can get quite involved. One thing not to forget, when using the following formula, you have to create a big enough off-set to accommodate the caption and any menu height:
(FrmReport.left+BtnTo.left, FrmReport.top+BtnTo.top+BtnTo.height+25)
You can also point the popup menu property of the button to the pmDateRanger. That way, if the user clicks, it shows up in the proscribed place. If the user right clicks on the button, the menu pops up at the default, some number of pixels lower and to the right. It's only a bit disconcerting.
The popup menu is populated by your menu choices. The group I use might or might NOT match yours. I'm sure you'll have a different grouping concept than mine. However, the secret is in setting the tag of each menu option so that it can be processed by the one central handler. I use the 11-13 for days, 21-23 for weeks, 31-33 for months, 41-43 for quarters, 51-54 for the to-date group, 61-62 for biz days, 71-72 for years and 80 for the ubiquitous ALL Dates. Note that you might want to set the dFrom to an earlier date that 1980 if you have need for even MORE historical date ranging.
It is important at this point to tell you of a trap I fell into the first three times I tried to program this from memory. (I'm a newbie, afterall). There is a feeling that you should use the OnPopUp or OnChange event of the popup menu to do the menu picking handling. Nope! Create a separate procedure to use of the OnCLICK method of each menu item, WHILE it is showing in the menu designer. Then, you can shift-click on all immediate action menu items and point the OnClick to your handler. DO NOT include the parent of any child menus you might have. Made that mistake to.
The code to update the date picker for a set range runs of the pmMenuHandler proc:
procedure TFrmReport.pmMenuHandler(Sender: TObject);
var
t, s : TDateTime;
begin
t := date;
case (sender as TMenuItem).tag of
11 : begin // yesterday
eDateFrom.date := t - 1;
eDateTo.date := t - 1;
end;
12 : begin // today
eDateFrom.date := t;
eDateTo.date := t;
end;
13 : begin // tomorrow
eDateFrom.date := t + 1;
eDateTo.date := t + 1;
end;
21 : begin // last week
s := t - 7;
while isMonday(s) = false do
s := s - 1;
eDateFrom.date := s;
eDateTo.date := s + 6;
end;
22 : begin // this week
s := t;
while isMonday(s) = false do
s := s - 1;
eDateFrom.date := s;
eDateTo.date := s + 6;
end;
23 : begin // next week
s := t + 7;
while isMonday(s) = false do
s := s - 1;
eDateFrom.date := s;
eDateTo.date := s + 6;
end;
31 : begin // last month
s := GetFirstDayOfMonth(t)-1; // gets last day of last mo.
eDateFrom.date := GetFirstDayOfMonth(s);
eDateTo.date := s;
end;
32 : begin // this month
eDateFrom.date := GetFirstDayOfMonth(t);
eDateTo.date := GetLastDayOfMonth(t);
end;
33 : begin // next month
s := GetLastDayOfMonth(t)+1; // gets first day of next mo.
eDateFrom.date := s;
eDateTo.date := GetLastDayOfMonth(s);
end;
41 : begin // last quarter
s := GetFirstDayOfQuarter(t)-1; // gets last day of last Q
eDateFrom.date := GetFirstDayOfQuarter(s);
eDateTo.date := s;
end;
42 : begin // this quarter
eDateFrom.date := GetFirstDayOfQuarter(t);
eDateTo.date := GetLastDayOfQuarter(t);
end;
43 : begin // next quarter
s := GetLastDayOfQuarter(t)+1; // gets first day of next Q
eDateFrom.date := s;
eDateTo.date := GetLastDayOfQuarter(s);
end;
51 : begin // month to date
eDateFrom.date := EncodeDate(ThisYear,ThisMonth,1);
eDateTo.date := t;
end;
52 : begin // year to date
eDateFrom.date := EncodeDate(ThisYear,1,1);
eDateTo.date := t;
end;
53 : begin // last 365 days
eDateFrom.date := t - 365;
eDateTo.date := t;
end;
54 : begin // last 13 weeks
eDateFrom.date := t - 91;
eDateTo.date := t;
end;
61 : begin // prior biz day
s := t-1; // yesterday
while isWeekend(s)
do s := s - 1;
eDateFrom.date := s;
eDateTo.date := s;
end;
62 : begin // next biz day
s := t+1; // tomorrow
while isWeekend(s)
do s := s + 1;
eDateFrom.date := s;
eDateTo.date := s;
end;
71 : begin // last year
s := GetFirstDayOfYear(Date2Year(t))-1; // gets last day of last yr.
eDateFrom.date := GetFirstDayOfYear(Date2Year(s));
eDateTo.date := s;
end;
72 : begin // this year
eDateFrom.date := GetFirstDayOfYear(Date2Year(t));
eDateTo.date := GetLastDayOfYear(Date2Year(t));
end;
73 : begin // next year
s := GetLastDayOfYear(Date2Year(t))+1; // gets first day of next mo.
eDateFrom.date := s;
eDateTo.date := GetLastDayOfYear(Date2Year(s));
end;
80 : begin // all dates
eDateFrom.date := strToDate('1/1/1980');
eDateTo.date := strToDate('1/1/2222');
end;
end;
end;
Later, a code fragment to take advantage of what we get with the date range now selected. Here's the code for initiating a query using the date range selected:
procedure TFrmReport.BtnRepPrintClick(Sender: TObject);
var
dStart, dEnd : string;
begin
dStart := ' (DateProd >= ''' + dFrom.DateString('mm/dd/yyyy') + ''')';
dEnd := ' AND (DateProd <= ''' + dTo.DateString('mm/dd/yyyy') + ''')';
with qValue do begin
active := false;
sql.beginUpdate;
sql.clear;
sql.add('SELECT DISTINCT ');
sql.add(' PartNum,');
sql.add(' LotNum,');
sql.add(' DateProd,');
sql.add(' Production,');
sql.add(' ProductionValue');
sql.add('FROM ');
sql.add(' "SALES.db"');
sql.add('WHERE ');
sql.add( DStart);
sql.add( DEnd);
sql.add('ORDER BY ');
sql.add(' PartNum,');
sql.add(' LotNum,');
sql.add(' DateProd,');
sql.add(' Production,');
sql.add(' ProductionValue');
sql.EndUpdate;
active := true;
end;
qrValue.Print;
end;
There's no question I could have paramaterized this query. But it's really easy to build and to modify. For example, have a lot number edit box on the report form to allow for optional limiting the report. An easy line to add after the "add dEnd" line would be:
if editLotNum.text <> ''
then sql.add (' AND LotNum="' + editLotNum.text + '")';
If you have LOTS of parameters to fill this way, then go to one of the dynamic sql creating utilities. They seem to be cropping up everywhere. I use SimpleQuery from lelSoft (http://lelsoft.hypermart.net). But if date ranging is all you require, you should be game to write your own.
Gary Mugford
Idea Mechanic, Bramalea ON Canada
mugford@aztec-net.com
]]>