@BusinessDays In LS

In R6 you can use @BusinessDays to calculate the number of working days between one date and another, excluding non-working days and a list of dates to exclude as well. As there is no equivalent in LotusScript, I wrote a small class to simulate @BusinessDays in LS.
Actually I did not reinvent the wheel, but simply used evaluate in combination with @BusinessDays to achieve the aim.

Here is my code:

Class BusinessDay

	Private holidays As String
	Private nondays As String

	Sub New (strExcludeDays As String,strExcludeDates As String)
		holidays = "[01/01/1899]"
		nondays = "0"
		If strExcludeDays <> "" Then
			nondays = strExcludeDays
		End If

		If strExcludeDates <> "" Then
			holidays = strExcludeDates
		End If
	End Sub

	Public Function GetBusinessDays(dtStart As String,dtEnd As String) As Integer

		Dim bd As Variant
		Dim StartDT As New NotesDateTime(dtStart)
		Dim EndDT As New NotesDateTime(dtEnd)

		GetBusinessDays = 0
		bd = Evaluate({@BusinessDays([}&_
		Cdat(StartDT.DateOnly)& {];[}&_
		Cdat(EndDT.DateOnly)& {];}&_
		Me.nondays &{;}&_
		Me.holidays &_
		{)})
		GetBusinessDays = Cint(bd(0))
	End Function

End Class

To test the code, put the code into the declaration section of a button. In your “Click” event type

Sub Click(Source As Button)
	Dim dtStart As String
	Dim dtEnd As String
	dtStart = "05.06.2007"
	dtEnd = "11.06.2007"

	'Dim b As New BusinessDay("1:7","[08.06.2007]:[06.06.2007]:[07.06.2007]")
	'Dim b As New BusinessDay("","[08.06.2007]:[06.06.2007]:[07.06.2007]")
	Dim b As New BusinessDay("","")
	Msgbox b.GetBusinessDays (dtStart,dtEnd )
End Sub

SuperNTF – Beta 0.9.4 released

Kevin Pettitt released version 0.9.4 of SuperNTF on OpenNTF.

SuperNTF by Kevin Pettitt

The initial release of SuperNTF is full of goodies. The first thing you will see is the clean navigation framework. Give yourself the “Admin” role and you will see a link to the “Administration” panel, where all sorts of configuration and logging functions are visible.

You should click the “DB Config” item under “Configuration” to change some global database properties. Check out the other configuration options, and note the “code helpers” which build formula syntax for using keyword and other formulas automatically.

I highly recommend keeping soft deletions enabled, as it works VERY well, regardless of how you delete documents (including “cuts”). Also note the mouseover behavior of the trash icon (and several others).

If you write any agents, check out the “Agent Templates” which provide a couple different common agent frameworks. Note that these samples include the standard OpenLog error trapping elements.

The standard “view actions” are highly tuned to show/hide as appropriate. E.g. the “Search” button only shows if the db is full text indexed. Also visible (only on windows) are the excel export actions.

All read and edit activity is tracked, so if you want to know what you looked at yesterday, check the “My History” view. To see what your boss read yesterday, look in the “User Activity” views in the Administration section. You can also see the history of specific field value changes at the document level.

And much more…


Adjust Date/Time To Next Business Day

Finding the difference in seconds between one date-time and another can be done using the timedifference method of the NotesDateTime class in LotusScript. In formula language, @BusinessDays returns the number of business days in one or more date ranges.
Assume you have the following requirements:

  • Calculate the time in minutes between “DateCreated” and “DateClosed”.
  • Businessdays are from Monday to Friday
  • BusinessHours ar from 8 AM to 10 PM
  • Exclude holidays
  • If the date/time value is greater than the end of a businessday, set date to next businessday
  • if the date/time value is before the beginning of a businessday, set the time to i.e 8 AM

Here is a LotusScript class which does the trick.

Class DateTimeCalculator

	Private StartDT As NotesDateTime
	Private EndDT As NotesDateTime
	Private dt3 As NotesDateTime
	Private dt4 As NotesDateTime
	Private dt5 As NotesDateTime
	Private NewDT As Boolean
	Private tmp As Variant
	Private i As Integer
	Private j As Integer
	Private k As Integer
	Private x As Integer
	Private elapsed As Integer
	Private ExcludeDays() As String
	Private ExcludeDates() As NotesDateTime
	Private WDENDHOUR As String
	Private WDSTARTHOUR As String

	Sub New (strExcludeDays As String_
                       , strExcludeDates As String_
                       , strWDSTARTHOUR As String_
                       , strWDENDHOUR As String)

		If strWDSTARTHOUR = "" Then
			WDSTARTHOUR = "00:00"
		Else
			WDSTARTHOUR = strWDSTARTHOUR
		End If

		If strWDENDHOUR = "" Then
			WDENDHOUR = "23:59"
		Else
			WDENDHOUR = strWDENDHOUR
		End If

		' strExcludeDays contains a comma separated list of dayes that are not work days
		tmp = Split(strExcludeDays,",")
		Redim Me.ExcludeDays(Ubound(tmp))
		For x = 0 To Ubound(tmp)
			Me.ExcludeDays(x) = tmp(x)
		Next

		' strExcludeDates contains a comma separated list of dates that are not work days
		tmp = Split(strExcludeDates,",")
		Redim Me.ExcludeDates(Ubound(tmp))
		For x = 0 To Ubound(tmp)
			Set Me.ExcludeDates(x) = New NotesDateTime(tmp(x))
		Next
	End Sub

	Public Function GetNextBusinessDay ( dt1 As String )As String
		Set Me.StartDT = New NotesDateTime (dt1)
		NewDt = False
		Set dt3 = New NotesDateTime (Me.StartDT.DateOnly & " " & Me.WDSTARTHOUR)
		Set dt4 = New NotesDateTime (Me.StartDT.DateOnly & " " & Me.WDENDHOUR)
		If Me.StartDT.TimeDifference(dt3) < 0 Then ' StartDT < WDSTARTHOUR
			Set Me.StartDT = dt3
		End If

		If dt4.TimeDifference(Me.StartDT) < 0 Then ' StartDT > WDENDHOUR
			Set Me.StartDT = dt3
			Call StartDT.AdjustDay(1)
		End If

		For j = 0 To Ubound ( Me.ExcludeDates ) ' Check for excluded dates
			For k = 0 To Ubound ( Me.ExcludeDates )
				If Me.StartDT.DateOnly = Me.ExcludeDates(k).DateOnly Then
					Call Me.StartDT.AdjustDay(1)
					NewDT = True
					For i = 0 To Ubound ( Me.ExcludeDays ) ' Check if businessday
						If Instr(Implode ( Me.ExcludeDays )_
                                                    , Cstr(Weekday(Me.StartDT.DateOnly))) > 0 Then
							Call Me.StartDT.AdjustDay(1)
						End If
					Next
					k = Ubound(Me.ExcludeDates)
				Else
					For i = 0 To Ubound ( Me.ExcludeDays ) ' Check if businessday
						If Instr(Implode ( Me.ExcludeDays _
                                                    ), Cstr(Weekday(Me.StartDT.DateOnly))) > 0 Then
							Call Me.StartDT.AdjustDay(1)
							NewDT = True
						End If
					Next
				End If
			Next
		Next

		If NewDT Then
			Set dt5 = _
                          New NotesDateTime (Me.StartDT.DateOnly & " " & Me.WDSTARTHOUR)
			Set Me.StartDT = dt5
		End If
		GetNextBusinessDay = Me.StartDT.LocalTime
	End Function

End Class

This class has a New function that is used to instantiate the class. A list of business days, list of holidays and the start and end of the workday is passed when the object is created.

	Dim DTCalc As New DateTimeCalculator ( "1,7", "11.06.2007","08:00", "22:00")

Once instatiated you can now adjust a date/time value to the next business by calling the GetNextBusinessDay method and passing the date/time value as a string parameter.

	StartDT = doc.GetFirstItem("DateCreated").text
         ...
        Set dt1 = New NotesDateTime ( DTCalc.GetNextBusinessDay(StartDT) )

The returned value can now be used to find the difference between this date/time value and another one.

	diff = dt2.TimeDifference(dt1)/60

Here is an example of how to put this all together in a click button. I have attached a sample database at the end of this article.

Sub Click(Source As Button)
	Dim s As New NotesSession
	Dim db As NotesDatabase
	Dim col As NotesDocumentCollection
	Dim doc As NotesDocument
	Set db = s.CurrentDatabase
	Set col = db.UnprocessedDocuments
	Set doc = col.GetFirstDocument

	Dim msg As String
	Dim StartDT As String
	Dim EndDT As String
	Dim dt1 As NotesDateTime
	Dim dt2 As NotesDateTime
	Dim diff As Long

	StartDT = doc.GetFirstItem("DateCreated").text
	EndDT = doc.GetFirstItem("DateClosed").text

	Dim DTCalc As New DateTimeCalculator ( "1,7", "11.06.2007","08:00", "22:00")

	Set dt1 = New NotesDateTime ( DTCalc.GetNextBusinessDay(StartDT) )
	Set dt2 = New NotesDateTime ( DTCalc.GetNextBusinessDay(EndDT) )

	diff = dt2.TimeDifference(dt1)/60
	msg = msg  & "Created On : " & StartDT & CRLF
	msg = msg  & "Closed On : " & EndDT & CRLF
	msg = msg & "Difference : " & diff & " minutes"
	Msgbox msg
End Sub

Download SampleDB


!!HELP!! and ExtND

Today I built in the ExtND framework into the !!HELP!! database. Without digging to deep into the framework, I was able to create a simple web interface for the helpdesk. Unfortunately, the framework does not support categorized views at the moment.

But this doesn’t matter. It is an Alpha release. But it already produces nice looking grid views with navigation and stuff. and works as expected in Firefox and IE.

!!HELP!! - ExtND

The next release of !!HELP!! will include the ExtND framework along with a handful of views and forms to enable end-users to watch the progress of their service calls on the web.

I would like to thank all who contributed to the !!HELP!! project. If you, as an end-user, would like to write a testimonial, you can do so here


The Day Before ILUG2007

Just returned from the lobby of the Alexander Hotel in Dublin, Ireland. Loads of geeks already there from mostly the U.S.A and Canada. Attached you find a photo taken while Kitty, Eileen and a Scotsman named Bill preparing some secret stuff for tomorrows event.

Final Preparations


It’s time for ILUG2007

Bags are packed, ready to go. In about 24 hours I’ll start my trip to Dublin, Ireland. I’ll arrive on wednesday moring at 11 am and will stay at the Dublin City Hotel , Dame Street. Looking forward to seeing you all at the conference. In advance, thanks to all organizers for making this event possible.

ILUG2007


Was passiert wenn …

ein Richter beschlie├â?t, da├â? zuk├╝nftig keine Mais mehr gel├Âscht werden d├╝rfen, beschreibt Chris Byrne in seinem Blog

Jetzt erkl├Ąre mir bitte einmal in diesem Zusammenhang, welchen Sinn es macht, eine wegen Gr├Â├â?enbeschr├Ąnkung nicht zustellbare Mail incl. aller Anh├Ąnge an den Absender zur├╝ckzuschicken.


Which color is suitable for which purpose?

color in motionColors reflect a certain personality. They also have several meanings, most of which are closely connected to each other.
For example, blue stands for sky, heaven and water. It reflects freedom and peace, but it can also mean cold, protective, authoritative or technical.
Red is the color of blood, it reflects courage, romance, but it also means hot, dynamic, vital, commanding or alert. All these symbolic connotations are perfectly visualized by Claudia Cortes in her Color in Motion, a real treat for the eye


Change Domino’s DOCTYPE

Maybe everybody already knows … except me.
You can change the doctype ( from at least version 6.5.3 on ) by setting the DominoCompleteDoctype environment variable in notes.ini. It has 3 different values:

0 = <DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”\\>
1 = <!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”>
2 = <!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01//EN” “http://www.w3.org/TR/html4/strict.dtd”>

So far, so good. But what, if you want to have

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”> ??

As of Domino 7.0.2 there is a new field that allows you to set the doctype on a form basis. Just add a computed for display field called $$HTMLFrontMatter.


Charting In Microsoft Excel

I was searching for informations on how to create charts in Microsoft Excel and found Jon Peltier’s page. A great resource, that I would like to share with you!

Over the years I have learned a great deal about making charts in Microsoft Excel, and I have amassed a large collection of techniques for creating and formatting special charts. This is a categorized collection of my knowledge.

I’ve sorted the charts into the following topics. Many of the examples here fit into multiple topics.

{ Link }