1001 Things You Wanted To Know About Visual FoxPro phần 2 ppt

38 1001 Things You Always Wanted to Know About Visual FoxPro

**********************************************************************

* Program....: nthSomeDayOfMonth

* Compiler...: Visual FoxPro 06.00.8492.00 for Windows

* Abstract...: Returns the date of a specific type of day; e.g., the

* ...........: second Tuesday in November of the year 2001

* ...........: nthSomedayOfMonth( 4, 3, 7, 2000 ) returns the date of

* ...........: the 3rd Wednesday in July of the year 2000

* Parameters.: tnDayNum: Day number 1=Sunday 7=Saturday

* ...........: tnWhich : Which one to find; 1st, 2nd, etc.

* ...........: If tnwhich > the number of this kind of day

* ...........: in the month, the last one is returned

* ...........: tnMonth : Month Number in which to find the day

* ...........: tnYear : Year in which to find the day

**********************************************************************

FUNCTION nthSomedayOfMonth( tnDayNum, tnWhich, tnMonth, tnYear )

LOCAL ldDate, lnCnt

*** Start at the first day of the specified month

ldDate = DATE( tnYear, tnMonth, 01 )

*** Find the first one of the specified day of the week

DO WHILE DOW( ldDate ) # tnDayNum

ldDate = ldDate + 1

ENDDO

*** Find the specified one of these...e.g, 2nd, 3rd, or last

IF tnWhich > 1

lnCnt = 1

DO WHILE lnCnt < tnWhich

lnCnt = lnCnt + 1

*** Move forward one week to get the next one of these in the month

ldDate = ldDate + 7

*** Are we are still in the correct month?

IF MONTH( ldDate ) # tnMonth

*** If not, jump back to the last one of these we found and exit

ldDate = ldDate - 7

EXIT

ENDIF

ENDDO

ENDIF

RETURN ldDate

Setting up a payment schedule

Another interesting problem is that of setting up a monthly schedule. Take, for example, a

schedule of monthly payments to be collected via direct debit of a debtor's checking account.

Obviously these payments cannot be collected on Sundays or holidays. They also cannot be

collected earlier than the day specified when the schedule is first set up. This poses some

interesting problems if the initial seed date for the schedule is between the 28th and the 31st of

the month. So, in this case, simply using the GOMONTH() function may return an

unacceptable date.

This function handles weekends, holidays, and GOMONTH() and assumes that you have

created your holiday table with two columns: one for the date and one for the name of the

Chapter 2: Functions and Procedures 39

holiday. An index on the holiday date is also desirable. Also keep in mind that to be useful, this

holiday table must contain, at the very least, the holidays for both this year and next year.

FUNCTION MonthlySchedule ( tdStartDate, tnNumberOfMonths )

LOCAL laDates[1], lnCnt, ldDate, llOK, llUsed

*** Make sure we have the class library loaded

IF 'CH02' $ SET( 'CLASSLIB' )

*** Do nothing...class library is loaded

ELSE

SET CLASSLIB TO CH02 ADDITIVE

ENDIF

*** Make sure we have the Holidays table available

IF !USED( 'Holidays' )

USE Holidays In 0

llUsed = .F.

ELSE

llUsed = .T.

ENDIF

SELECT Holidays

SET ORDER TO dHoliday

FOR lnCnt = 1 TO tnNumberOfMonths

*** we want to return the passed date as date[1]

IF lnCnt > 1

ldDate = GOMONTH( tdStartDate, lnCnt-1 )

ELSE

ldDate = tdStartDate

ENDIF

*** Now we have to check to be sure that GoMonth didn't give us back a day

*** that is earlier than the seed date...can't do a direct debit BEFORE the

*** specified date i.e., the 28th of the month

IF DAY(tdStartDate) > 28

IF BETWEEN( DAY( ldDate ), 28, DAY( tdStartDate ) - 1 )

ldDate = ldDate + 1

ENDIF

ENDIF

llOK = .F.

DO WHILE !llOK

*** If current date is a Saturday, go to Monday

IF DOW( ldDate ) = 7

ldDate = ldDate + 2

ELSE

*** If current date is a Sunday, go to Monday

IF DOW( ldDate ) = 1

ldDate = ldDate + 1

ENDIF

ENDIF

*** OK, now check for Holidays

IF !SEEK( ldDate, 'Holidays', 'dHoliday' )

llOK = .T.

ELSE

ldDate = ldDate + 1

ENDIF

ENDDO

DIMENSION laDates[lnCnt]

laDates[lnCnt] = ldDate

40 1001 Things You Always Wanted to Know About Visual FoxPro

ENDFOR

IF !llUsed

USE IN Holidays

ENDIF

RETURN CREATEOBJECT( 'xParameters', @laDates )

What date is ten business days from today?

A somewhat similar problem is how to calculate a date that is a specified number of business

days from a given date. As with the previous example, this assumes the existence of a holiday

table that is both region and application specific.

FUNCTION BusinessDays ( tdStartDate, tnNumberOfDays )

LOCAL lnCnt, ldDate, llOK, llUsed

*** Make sure we have the Holidays table available

IF !USED( 'Holidays' )

USE Holidays In 0

llUsed = .F.

ELSE

llUsed = .T.

ENDIF

SELECT Holidays

SET ORDER TO dHoliday

ldDate = tdStartDate

FOR lnCnt = 1 TO tnNumberOfDays

ldDate = ldDate + 1

llOK = .F.

DO WHILE !llOK

*** If current date is a Saturday, go to Monday

IF DOW( ldDate ) = 7

ldDate = ldDate + 2

ELSE

*** If current date is a Sunday, go to Monday

IF DOW( ldDate ) = 1

ldDate = ldDate + 1

ENDIF

ENDIF

*** OK, now check for Holidays

IF !SEEK( ldDate, 'Holidays', 'dHoliday' )

llOK = .T.

ELSE

ldDate = ldDate + 1

ENDIF

ENDDO

ENDFOR

IF !llUsed

USE IN Holidays

ENDIF

RETURN ldDate

Chapter 2: Functions and Procedures 41

Gotcha! Strict date format and parameterized views

Visual FoxPro's StrictDate format is especially comforting with the specter of the millennium

bug looming large in front of us. At least it is as we are writing this. There, is however, one

small bug that you should be aware of. If you have SET STRICTDATE TO 2 and try to open a

parameterized view that takes a date as its parameter, you will be in for trouble. If the view

parameter is not defined or is not in scope when you open or re-query the view, the friendly

little dialog box prompting for the view parameter will not accept anything you enter. It will

keep saying you have entered an ambiguous date/datetime constant.

The workaround is to ensure your view parameter is defined and in scope before trying to

open or re-query the view. This means that, if your view is part of a form’s data environment,

its NoDataOnLoad property must be set to avoid getting the dialog as the form loads.

The other workaround, setting StrictDate to 0 and then back to 2, is not recommended. As

we have already mentioned, using a global solution for a local problem is a little bit like

swatting flies with a sledgehammer.

Working with numbers

Mathematical calculations have been handled fairly well since the days of Eniac and Maniac,

except for the notable bug in the Pentium math co-processor. The most common problems arise

because many calculations produce irrational results such as numbers that carry on for an

infinite number of decimal places. Rounding errors are impossible to avoid because computing

demands these numbers be represented in a finite form. The study of numerical analysis deals

with how to minimize these errors by changing the order in which mathematical operations are

performed as well as providing methods such as the trapezoidal method for calculating the area

under the curve. A discussion of this topic is beyond the scope of this book, but we can give

you some tips and gotchas to watch out for when working with numbers in your application.

Converting numbers to strings

Converting integers to strings is fairly straightforward. ALLTRIM( STR( lnSomeNumber ) )

will handle the conversion if the integer contains ten digits or less. If the integer contains more

than ten digits, this function will produce a string in scientific notation format unless you

specify the length of the string result as the second parameter. When converting numeric values

containing decimal points or currency values, it is probably better to use another function.

Although it can be accomplished using the STR() function, it is difficult to write a generic

conversion routine. In order to convert the entire number you must specify both the total length

of the number (including the decimal point) and the number of digits to the right of the decimal

point. Thus STR(1234.5678) will produce '1235' as its result, and to get the correct conversion

you must specify STR(1234.5678, 9, 4).

In Visual FoxPro 6.0, the TRANSFORM() function has been extended so that when called

without any formatting parameters, it simply returns the passed value as its equivalent

character string. Thus TRANSFORM(1234.5678) will correctly return '1234.5678'.

In all versions of Visual FoxPro you can use ALLTRIM( PADL ( lnSomeNumber, 32 ) )

to get the same result (providing that the total length of lnSomeNumber is less than thirty-two

digits).

42 1001 Things You Always Wanted to Know About Visual FoxPro

Gotcha! calculations that involve money

This one can bite if you are not careful. Try this in the command window and you will see

what we mean.

? ( $1000 / 3 )

returns the expected result of 333.3333 since currency values are always calculated to a

precision of four decimal places. However,

? ( $1000 * ( 1/3 ) )

returns 333.3000, which is not a very accurate result! Especially when you consider the

result of the equivalent numeric calculation:

SET DECIMALS TO 4

? ( 1000 * ( 1/3 ) )

returns 333.3333. The actual precision of the displayed result depends on the setting of

SET DECIMALS, although the result is actually calculated to 8 places by default.

The moral of this story is that currency values should always be converted to numeric prior

to using them in arithmetic operations. The functions MTON() and NTOM() are essential in this

scenario, although watch out for unexpected results if you do not convert both ways!

? ( MTON( $1000 ) * ( 1/3 ) )

displays 333.333333 even with decimals set to 2. While

? NTOM( ( MTON( $1000 ) * ( 1/3 ) ) )

finally gets the expected result of 333.3333.

String functions

Visual FoxPro has several native string manipulation functions to handle almost everything

you could ever need. ALLTRIM() to remove leading and trailing spaces, PADL() and PADR()

to left and right pad, and STRTRAN() and CHRTRAN() to replace individual characters within a

string. But did you know that you can use this line of code:

cString1 – cString2 – cString3

to accomplish the same thing as this one?

RTRIM( cString1 ) + RTRIM( cString2 ) + RTRIM( cString3 )

Gotcha! string concatenation

Even if the tables in your application do not allow null values, you may still need to deal with

them. Very often, SQL statements using outer joins result in one or more columns that contain

null values. This can be troublesome in cases where you may want to display a concatenated

value from a result set, for example, in a drop down list. Try this in the command window: