Nice work Linda.
Rob
________________________________
From: Linda Lowney <llowney@...>
To: vantage@yahoogroups.com
Sent: Tuesday, June 16, 2009 4:36:45 PM
Subject: RE: [Vantage] BAQ - Count work days
Hi Everyone,
I finally finished my working days project on the memo screen. I
borrowed and modified some code that I found on the internet to
calculate the holidays. We are in Canada, but I kept the USA holidays
in the formula, though I did not check the returned value of any of the
US holidays. Just change the value of strCountry from "Canada" to
"USA". If anyone can see any "programming errors" please let me know.
Here is the end product in case anyone else can make use of it.
Regards,
Linda
'//********* ********* ********* ********* ********* *****
'// Custom VB.NET code for MemoForm
'// Created: 6/16/09
'//********* ********* ********* ********* ********* *****
Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Windows. Forms
Imports System.ComponentMod el
Imports Microsoft.VisualBas ic
Imports Epicor.Mfg.UI
Imports Epicor.Mfg.UI. FrameWork
Imports Epicor.Mfg.UI. ExtendedProps
Imports Epicor.Mfg.UI. FormFunctions
Imports Epicor.Mfg.UI. Customization
Imports Epicor.Mfg.UI. Adapters
Imports Epicor.Mfg.UI. Searches
Imports Epicor.Mfg.BO
Module Script
'// ** Wizard Insert Location - Do Not Remove 'Begin/End
Wizard Added Module Level Variables' Comments! **
'// Begin Wizard Added Module Level Variables **
'// End Wizard Added Module Level Variables **
Sub InitializeCustomCod e()
'// ** Wizard Insert Location - Do not
delete 'Begin/End Wizard Added Variable Intialization' lines **
'// Begin Wizard Added Variable
Intialization
'// End Wizard Added Variable
Intialization
'// Begin Custom Method Calls
'// End Custom Method Calls
End Sub
Sub DestroyCustomCode( )
'// ** Wizard Insert Location - Do not
delete 'Begin/End Wizard Added Object Disposal' lines **
'// Begin Wizard Added Object Disposal
'// End Wizard Added Object Disposal
'// Begin Custom Code Disposal
'// End Custom Code Disposal
End Sub
Private Sub Memo_AfterFieldChan ge(ByVal sender As object, ByVal args As
DataColumnChangeEve ntArgs) Handles Memo_Column. ColumnChanged
'// ** Argument Properties and Uses **
'// args.Row("[FieldNam e]")
'// args.Column, args.ProposedValue, args.Row
'
'Add Event Handler Code
dim edvMemo As EpiDataView = CType(oTrans. EpiDataViews( "Memo"),
EpiDataView)
dim epdStartDate as DateTime = dateserial(1980, 1,1)
dim epdEndDate as DateTime = dateserial(1980, 1,1)
dim lngWorkingDays as long = 0
dim strCountry as string = "Canada"
Select Case args.Column. ColumnName
Case "Date01"
if isdate(edvMemo. dataView( edvMemo.Row) ("Date01" )) and _
isdate(edvMemo. dataView( edvMemo.Row) ("Date02" )) then
epdStartDate = edvMemo.dataView( edvMemo.Row) ("Date01" )
epdEndDate = edvMemo.dataView( edvMemo.Row) ("Date02" )
if epdEndDate > epdStartDate then
lngWorkingDays = BusinessDays( epdStartDate,
epdEndDate, strCountry)
edvMemo.dataview( edvMemo.row) ("Number01" ) =
cdbl(lngWorkingDays )
'msgbox("Working days = " & lngWorkingDays)
else
edvMemo.dataview( edvMemo.row) ("Number01" ) = 0.00
end if
end if
Case "Date02"
if isdate(edvMemo. dataView( edvMemo.Row) ("Date01" )) and _
isdate(edvMemo. dataView( edvMemo.Row) ("Date02" )) then
epdStartDate = edvMemo.dataView( edvMemo.Row) ("Date01" )
epdEndDate = edvMemo.dataView( edvMemo.Row) ("Date02" )
if epdEndDate > epdStartDate then
lngWorkingDays = BusinessDays( epdStartDate,
epdEndDate, strCountry)
edvMemo.dataview( edvMemo.row) ("Number01" ) =
cdbl(lngWorkingDays )
'msgbox("Working days = " & lngWorkingDays)
else
edvMemo.dataview( edvMemo.row) ("Number01" ) = 0.00
end if
end if
Case Else
End Select
End Sub
Public Function BusinessDays( ByVal dteStartDate As Date, ByVal
dteEndDate As Date, ByVal strCountry As String) As Long
' Calculate Business Days - No Holiday Table Needed
Dim lngYear As Long
Dim lngEYear As Long
Dim dteStart As Date, dteEnd As Date
Dim dteCurr As Date
Dim lngDay As Long
Dim lngDiff As Long
Dim lngACount As Long
Dim dteLoop As long
Dim blnHol As Boolean
Dim dteHoliday() As Date
Dim lngCount As Long
Dim lngTotal As Long
dim lngFamily as long
Dim lngThanks As Long
Dim dteEaster As Date
dteStart = dteStartDate
dteEnd = dteEndDate
lngYear = DatePart("yyyy" , dteStart)
lngEYear = DatePart("yyyy" , dteEnd)
If lngYear <> lngEYear Then
lngDiff = (((lngEYear - lngYear) + 1) * 10) - 1
ReDim dteHoliday(lngDiff)
Else
ReDim dteHoliday(30)
End If
lngACount = -1
For lngCount = lngYear To lngEYear
lngACount = lngACount + 1
' New Years
If Weekday(DateSerial( lngCount, 1, 1)) = 7 Then ' Saturday -
move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 1, 3)
Else
If Weekday(DateSerial( lngCount, 1, 1)) = 1 Then ' Sunday -
move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 1, 2)
Else
dteHoliday(lngACoun t) = DateSerial(lngCount , 1, 1)
End If
End If
dteHoliday(lngACoun t) = DateSerial(lngCount , 1, 1)
' MsgBox ("New Years day = " & dteHoliday(lngACoun t))
lngACount = lngACount + 1
' Family Day - 3rd Monday in February - Canada (AB, ON, SK)
If strCountry = "Canada" Then
lngDay = 1
lngfamily = 0
Do
If Weekday(DateSerial( lngCount, 2, lngDay)) = 2 Then
lngfamily = lngfamily + 1
End If
lngDay = lngDay + 1
Loop Until lngfamily = 3
dteHoliday(lngACoun t) = DateSerial(lngCount , 2, lngDay - 1)
'MsgBox ("Family Day = " & dteHoliday(lngACoun t))
lngACount = lngACount + 1
End If
' Easter
lngDay = (((255 - 11 * (lngCount Mod 19)) - 21) Mod 30) + 21
dteEaster = DateSerial(lngCount , 3, 1)
dteEaster = dteEaster.AddDays( lngDay + _
(lngDay > 48) + 6 - ((lngCount + lngCount \ 4 + _
lngDay + (lngDay > 48) + 1)) Mod 7)
dteHoliday(lngACoun t) = DateSerial(dteEaste r.Year,
dteEaster.Month, dteEaster.Day - 2)
'MsgBox ("Easter = " & dteHoliday(lngACoun t))
lngACount = lngACount + 1
' Victoria Day - Monday before May 25th - Canada
If strCountry = "Canada" Then
lngDay = 24
Do
If Weekday(DateSerial( lngCount, 5, lngDay)) = 2 Then
dteHoliday(lngACoun t) = DateSerial(lngCount , 5,
lngDay)
Else
lngDay = lngDay - 1
End If
Loop Until dteHoliday(lngACoun t) >= DateSerial(lngCount , 5,
1)
'MsgBox ("Victoria Day = " & dteHoliday(lngACoun t))
lngACount = lngACount + 1
End If
' Memorial Day - Last Monday of May
If strCountry = "USA" Then
lngDay = 31
Do
If Weekday(DateSerial( lngCount, 5, lngDay)) = 2 Then
dteHoliday(lngACoun t) = DateSerial(lngCount , 5,
lngDay)
Else
lngDay = lngDay - 1
End If
Loop Until dteHoliday(lngACoun t) >= DateSerial(lngCount , 5,
1)
lngACount = lngACount + 1
End If
' July 1st
If strCountry = "Canada" Then
If Weekday(DateSerial( lngCount, 7, 1)) = 7 Then ' Saturday -
move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 7, 3)
Else
If Weekday(DateSerial( lngCount, 7, 1)) = 1 Then ' Sunday
- move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 7, 2)
Else
dteHoliday(lngACoun t) = DateSerial(lngCount , 7, 1)
End If
End If
'MsgBox ("Canada Day = " & dteHoliday(lngACoun t))
lngACount = lngACount + 1
End If
' July 4th
If strCountry = "USA" Then
dteHoliday(lngACoun t) = DateSerial(lngCount , 7, 4)
lngACount = lngACount + 1
End If
' Civic Holiday - 1st Monday in August - Canada
If strCountry = "Canada" Then
lngDay = 1
lngfamily = 0
Do
If Weekday(DateSerial( lngCount, 8, lngDay)) = 2 Then
lngfamily = lngfamily + 1
End If
lngDay = lngDay + 1
Loop Until lngfamily = 1
dteHoliday(lngACoun t) = DateSerial(lngCount , 8, lngDay - 1)
'MsgBox ("Civic holiday = " & dteHoliday(lngACoun t))
lngACount = lngACount + 1
End If
' Labor Day - First Monday of Septemeber
lngDay = 1
Do
If Weekday(DateSerial( lngCount, 9, lngDay)) = 2 Then
dteHoliday(lngACoun t) = DateSerial(lngCount , 9, lngDay)
Else
lngDay = lngDay + 1
End If
Loop Until dteHoliday(lngACoun t) >= DateSerial(lngCount , 9, 1)
'MsgBox ("Labor day = " & dteHoliday(lngACoun t))
' Thanksgiving - 2nd Monday of October
If strCountry = "Canada" Then
lngDay = 1
lngThanks = 0
Do
If Weekday(DateSerial( lngCount, 10, lngDay)) = 1 Then
lngThanks = lngThanks + 1
End If
lngDay = lngDay + 1
Loop Until lngThanks = 2
dteHoliday(lngACoun t) = DateSerial(lngCount , 10, lngDay)
'MsgBox ("Thanksgiving day = " & dteHoliday(lngACoun t))
lngACount = lngACount + 1
End If
' Thanksgiving - 4th Thursday of November
If strCountry = "USA" Then
lngDay = 1
lngThanks = 0
Do
If Weekday(DateSerial( lngCount, 11, lngDay)) = 5 Then
lngThanks = lngThanks + 1
End If
lngDay = lngDay + 1
Loop Until lngThanks = 4
dteHoliday(lngACoun t) = DateSerial(lngCount , 11, lngDay)
lngACount = lngACount + 1
End If
' Christmas
If Weekday(DateSerial( lngCount, 12, 25)) = 7 Then ' Saturday -
move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 12, 27)
Else
If Weekday(DateSerial( lngCount, 12, 25)) = 1 Then ' Sunday -
move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 12, 26)
Else
dteHoliday(lngACoun t) = DateSerial(lngCount , 12, 25)
End If
End If
'MsgBox ("Christmas = " & dteHoliday(lngACoun t))
If strCountry = "Canada" Then ' In US Christmas will be last
holiday of year - don't need to add counter
lngACount = lngACount + 1
End If
' Boxing Day
If strCountry = "Canada" Then
If Weekday(DateSerial( lngCount, 12, 26)) = 7 Then ' Saturday
- move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 12, 28)
Else
If Weekday(DateSerial( lngCount, 12, 26)) = 1 Then '
Sunday - move to Monday
dteHoliday(lngACoun t) = DateSerial(lngCount , 12, 27)
Else
dteHoliday(lngACoun t) = DateSerial(lngCount , 12, 26)
End If
End If
'MsgBox ("Boxing Day = " & dteHoliday(lngACoun t))
End If
Next
' Weekends
For lngCount = 1 To DateDiff("d" , dteStart, dteEnd)
dteCurr = (dteStart.adddays( lngCount) )
If (Weekday(dteCurr) <> 1) And (Weekday(dteCurr) <> 7) Then
blnHol = False
For dteLoop = 0 To UBound(dteHoliday)
'MsgBox dteHoliday(dteLoop) & " " & dteLoop
If (dteHoliday( dteLoop) = dteCurr) Then
blnHol = True
End If
Next dteLoop
If blnHol = False Then
lngTotal = lngTotal + 1
'MsgBox dteCurr
End If
End If
Next lngCount
BusinessDays = lngTotal
End Function
End Module
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Robert Brown
Sent: Monday, June 08, 2009 5:10 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] BAQ - Count work days
Ari,
Sure. The 'canned' calender table schema is a little overly complex for
most date math purposes (for my tastes) - We set up a much cleaner UD
table (much like Vic just suggested) - but they are certainly usable.
The problem lies with the very limited Progress 4GL support within BAQ
calculated fields. (It's doable with SQL.)
You could create the syntax to do the loop to test from start.date + 1
thru end.date - but I think you could only test for weekend dates. I
don't see support for traversing thru every record of the unjoined
calendar table to test for a holiday.
Any ideas?!?!?!
Rob Brown
____________ _________ _________ __
From: Ari Footlik <ari@zweig-cnc. com <mailto:ari% 40zweig-cnc. com> >
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Sent: Monday, June 8, 2009 3:02:03 PM
Subject: RE: [Vantage] BAQ - Count work days
Couldn't you create a company calendar, not link it to any resources,
and set the "work days" on it to reflect your organization' s schedule?
____________ _________ _________ __
From: vantage@yahoogroups .com [mailto:vantage@ yahoogroups .com] On
Behalf
Of Robert Brown
Sent: Monday, June 08, 2009 1:22 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] BAQ - Count work days
The weekend stuff is pretty easy. Take Rob's formula and enclose it
with: INTEGER(5/7* (end.date - start.date) - "5" assuming a 5 day
standard week.
4GL also has a WEEKDAY(yourdate. field) function that returns (integer)
1
for Sunday, 2 Monday, etc.,.
(Amazingly the braindead BAQ wizard actually understands that
function... It doesn't understand most of the potentially useful 4GL
functions.)
The problem is with the holidays.
1. What's your source?
2. Never tried it but I suspect if you left this 'holiday' table
unjoined to all others (which you would have to - like you would in
SQL), the BAQ would hang (or run awful slow).
2. Even with a table source you can't write a looping procedure with
defined variables inside a BAQ calc field that would allow you to start
with one date and go forward (or backward) testing for weekends and
holidays (through the unjoined table) and count valid workdays until you
reached your other date.
Your best bet is to do it with multiple BAQs (one for your start/end
date records, one for the holiday table) and make the dashboard, excel
report or crystal report do the work.
If you are doing this in an app, you can bring the BAQ results into
arrays & then use VB custom code to calc the days (and redisplay the
result in a 3rd array in an ultragrid).
A good example of VB code for calculating workdays:
http://social. msdn.microsoft. com/Forums/ en-US/vbgeneral/
thread/90acd242-
6984-4b24-a44e- 3bcc09ab1e1e/
<http://social. msdn.microsoft. com/Forums/ en-US/vbgeneral/
thread/90acd242
-6984-4b24-a44e- 3bcc09ab1e1e/ >
Best of luck Linda. Would love to see it if you come up with something.
Rob Brown
____________ _________ _________ __
From: Rob Bucek <rbucek@dsmfg. com <mailto:rbucek% 40dsmfg.com> >
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Sent: Monday, June 8, 2009 1:12:13 PM
Subject: RE: [Vantage] BAQ - Count work days
Without doing a lot more investigation I couldn't tell you that. I do
know that BAQ calculated fields can be very finicky, and that (at least
up to 407) they cannot reference other calculated fields. You can do a
fair bit of date manipulation and calculation with progress, but I don't
have an easy solution for that. Perhaps a more knowledgeable progress
person might. Sorry..
Rob Bucek
Manufacturing Engineer
PH: 715-284-5376 ext 311
FAX: 715-284-4084
<http://www.dsmfg. com/>
(Click the logo to view our site)
____________ _________ _________ __
From: vantage@yahoogroups .com [mailto:vantage@ yahoogroups .com] On
Behalf
Of Linda Lowney
Sent: Monday, June 08, 2009 12:06 PM
To: vantage@yahoogroups .com
Subject: RE: [Vantage] BAQ - Count work days
Hi Rob,
Thank you, but I need working days - no weekends - no holidays. I can
do this in Crystal and Excel, but I don't know much about creating
calculated fields in BAQ Progress.
Regards,
Linda
From: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
[mailto:vantage@ yahoogroups .com <mailto:vantage% 40yahoogroups. com> ]
On
Behalf
Of Rob Bucek
Sent: Monday, June 08, 2009 12:50 PM
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
Subject: RE: [Vantage] BAQ - Count work days
An example of syntax
ShipHead.ShipDate - OrderRel.ReqDate
Format
>>99 (this will give you a two digit Integer)
Data Type
Integer
It is possible to get what you are after.
Rob Bucek
Manufacturing Engineer
PH: 715-284-5376 ext 311
FAX: 715-284-4084
<http://www.dsmfg. com/ <http://www.dsmfg. com/> >
(Click the logo to view our site)
____________ _________ _________ __
From: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
<mailto:vantage% 40yahoogroups. com>
[mailto:vantage@ yahoogroups .com <mailto:vantage% 40yahoogroups. com>
<mailto:vantage% 40yahoogroups. com> ] On
Behalf
Of lindalowney
Sent: Monday, June 08, 2009 10:59 AM
To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>
<mailto:vantage% 40yahoogroups. com>
Subject: [Vantage] BAQ - Count work days
Hi Group,
I need to create a field in a BAQ showing the number of working days
between two dates. Is it possible to create a calculation field to
provide this information?
Regards,
Linda
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]