Add Friday Date

[Keith Hubert]

Hi Guys,

I need to append to a "Holiday.dbf" table the date of every Friday in a given year.

Trying to do this with xbasic and not getting very far.

All help gratefully appreciated.

[Tom Cone Jr]

Re: Add Friday Date

Brute force approach:

1) begin on January 1
2) loop through the days til you get to the first Friday
3) append that date to the table
4) add 7 days to the previous Friday's date
5) go to step 3 and repeat steps 3 and 4
6) until adding 7 to the previous Friday pushes your date into the next year

-- tom

[Keith Hubert]

Re: Add Friday Date

Hi Tom,

Once again you have come to the rescue. Many thanks for your time and help.

For those that like to this type of code, here it is. I would replace the values written to the table with variables.

First_fri = {01/01/2013}
Is_Fri = 0
while is_fri <> 6
 Is_Fri = dow(first_fri)
 if is_Fri = 6
  exit while
  end if
 first_fri = first_fri + 1
 end while
 
next_Fri = First_fri

' open table here
dim tbl as p
tbl= table.open("holidays",FILE_RW_SHARED)
while year(next_fri) = 2013
 'table - enter begin
 
 tbl.enter_begin()
	tbl.school_id="sch_1001"
    tbl.date=next_fri
    tbl.Holiday="test##"
 tbl.enter_end(.t.)
 
 ' ... enter_end(.t.)
 
  next_fri = next_fri + 7

 end while

' close the table here 
tbl.close() 
end

[Clunes]

Re: Add Friday Date

Hi Keith,

For what its worth, I had a bit of fun with your script. This may help you for future years or days. Copy and past and run. Note it is designed to add dates to a table called holidays as you describe above.
You can cancel on the dialogbox to stop it. This whole thing could also be modified to add a single date to the table.

'---------------------
DIM SHARED Year as C
DIM SHARED SDOW as C
DIM SHARED varC_result as C
dim shared dow2 as n
Year = ""
SDOW = ""
DELETE a_SDOW
DIM a_SDOW[5] as c
dim temp_list as c
temp_list = <<%list%
Monday
Tuesday
Wednesday
Thursday
Friday

%list%

a_SDOW.initialize(temp_list)
ok_button_label = "&Save"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Setup a holiday",<<%dlg%
{region}
Insert Year:| [.10Year1];
Select the day of the week:| [%v%.17SDOW^=a_SDOW];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if a_dlg_button = "OK" .and. Year = "" .and. dow2 = "" then
a_dlg_button = ""
ui_msg_box("Insufficient data", "You must enter a year and select a day")
end if


%code%)
If varC_result = "Cancel" .or. varC_result = "" then
end
end if

select
case SDow = "Monday"
dow2 = 2
case SDow = "Tuesday"
dow2 = 3
case SDow = "Wednesday"
dow2 = 4
case SDow = "Thursday"
dow2 = 5
case SDow = "Friday"
dow2 = 6
end select

Dim First_fri as d
dim result1 as d
dim i1 as n

dim tbl as p
tbl= table.open("holidays",FILE_RW_SHARED)
i1 = ctod("31/12/" + year1) -ctod("01/01/" + year1) +1


for i = 0 to i1
First_fri = date() + i

if dow(First_fri) = dow2 .and. year(First_fri) = val(year1) then

result1 = First_fri
tbl.enter_begin()
tbl.school_id="sch_1001"
tbl.date=RESULT1
tbl.Holiday="Yes"
tbl.enter_end(.t.)
end if

next
tbl.close()

ui_msg_box("Holidays added","Your dates have been added to the holidays list")

[Tom Cone Jr]

Re: Add Friday Date

Mark,

Interesting alternative. Thanks for posting.

Did you do "bounds" testing. i.e. if the last day of the year is the selected day of the week, will it be appended? I always worry whether the "difference" between two dates is the same thing as the number of days INCLUDING the first and last dates.

-- tom

[Clunes]

Re: Add Friday Date

In this case the difference between two dates is the number of the days in the year less 1. You will see I add 1 to the total. This works for leap years as well. The boundaries are restricted to the i1 variable. If i1 = 366 as per a leap year and that day matches the result required, then it will add a record.

Note that while the boundaries assume a full year, this script will only add those dates from the day it is done. EG as we are already in March for 2013 and we run it today, it will only add holidays from today till the end. assumption is what is done is don and cannot be undone. That said this would be easy to adjust to thinks backwards as well.

[Tom Cone Jr]

Re: Add Friday Date

Thanks, Mark.

Adding 1 to the difference assures the last date will be processed.

-- tom

[Tim Kiebert]

Re: Add Friday Date

Note that while the boundaries assume a full year, this script will only add those dates from the day it is done. EG as we are already in March for 2013 and we run it today, it will only add holidays from today till the end. assumption is what is done is don and cannot be undone. That said this would be easy to adjust to thinks backwards as well.

However, as the code stands it will continue into the next year since i1 = 365. If you want to limit the records to the chosen year then the calc for i1 needs to use today's date.

But then since the ability to choose a particular year has been added to the process one would assume that todays' date is probably not relevant. eg setting up the data for next year.

Something that could be added is check of the holiday table as records are entered to prevent duplicates. Say someone has already run the process or has added the odd date here and there.

[Tim Kiebert]

Re: Add Friday Date

A tip on using arrays as a source for a list. (As in the case for the weekdays).

Dimming the receiving variable as C will result in grabbing the value but dimming the var as N will grab the index position in the array. This can be useful in this case because we can eliminate the 'select case' to set dow2. Instead with SDOW dimmed as N we just need to add 1 to give us the result we need.

dow2 = SDOW + 1

[MoGrace]

Re: Add Friday Date

Hi Keith,
If every Friday is a holiday, who will be there to see what's in the table?

[Clunes]

Re: Add Friday Date

Just goes to show that when you want to give user a wizard automated process, how many different actions should be catered for.

[Keith Hubert]

Re: Add Friday Date

Hi Guys,

Many thanks for the additional ideas and suggestions, I shall certainly keep them in mind.

[G Gabriel]

Re: Add Friday Date

First_Fri=nth_DOW1("fri",2013,1,1)
Last_Fri={12/31/13}-dow({12/31/13})-1
x=(Last_Fri-First_Fri)/7
t=table.open("holidays.dbf")
for i= 0 to x
	t.enter_begin(.t.)
	t.date=First_Fri+i*7
	..add other fields..
	t.enter_end(.t.)
next
t.close()

[richardurban]

Re: Add Friday Date

Here is the loop using a begin and end date variable on a UX component, with a MYSQL backend:

First_sun = convert_type(e.dataSubmitted.begin_date,"d")
Is_sun = 0
while is_sun <> 1
 Is_sun = dow(convert_type(first_sun,"d"))
 if is_sun = 1
  exit while
  end if
 first_sun = first_sun + 1
 end while
 
next_sun = First_sun

' open table here

Dim cn as sql::connection 
Dim args as SQL::Arguments

flag = cn.open("::Name::urbangrocery")
dim sqlCommand as c

while next_sun <= convert_type(e.dataSubmitted.end_date,"d")
	args.set("date",next_sun)
sqlCommand="INSERT INTO Delivery_Dates (date)VALUES (:date)"
flag = cn.execute(sqlCommand,args)

  next_sun = next_sun + 7

end while

cn.Close()
end