Tom Wellige

Public Holidays (In Germany, Incl. Federal States)

Recommended Posts

The well known Forum user JoergG has provided me with the following VBScript function to calculate public holidays in Germany. The function even takes the different federal states into consideration.

 

(Diese Funktion ermittelt, ob ein gegebenes Datum ein Feiertag in einem Bundesland (oder mehreren) innerhalb Deutschlands ist)

 

[EDIT 21.01.2015]: I have changed the original function a little bit and made it a little bit more handy to use.

 

[EDIT 22.01.2015]: Da es sich um ein Skript für den Einsatz in Deutschland handelt, erlaube ich mir nach der Englischen Anleitung auch noch eine Anleitung in Deutsch zu posten (obwohl der Development Bereich im Swyx Forum eigentlich Englisch sprachig ist).

 

[EDIT 27.02.2015]: This is now the third version of the script, provided by Matthias. Thanks for that!

 

' Federal States of Germany
const vb_FS_BW = 1       ' Baden-Württemberg
const vb_FS_BY = 2       ' Bayern
const vb_FS_BE = 4       ' Berlin
const vb_FS_BB = 8       ' Brandenburg
const vb_FS_HB = 16      ' Bremen
const vb_FS_HH = 32      ' Hamburg
const vb_FS_HE = 64      ' Hessen
const vb_FS_MV = 128     ' Mecklenburg-Vorpommern
const vb_FS_NI = 256     ' Niedersachsen
const vb_FS_NW = 512     ' Nordrhein-Westfalen
const vb_FS_RP = 1024    ' Rheinland-Pfalz
const vb_FS_SL = 2048    ' Saarland
const vb_FS_SN = 4096    ' Sachsen
const vb_FS_ST = 8192    ' Sachen-Anhalt
const vb_FS_SH = 16384   ' Schleswig-Holstein
const vb_FS_TH = 32768   ' Thüringen
const vb_FS_KD = 65536   ' Köln/Düsseldorf (Rosenmontag)

'-------------------------------------------------------------------
' Name:     IsPublicHoliday
'           ===============
'
' Returns true if the given date is a public holiday in the given German
' federal state. Multiple federal states can be combined with "OR"
'
' Parameter:
'   nFederalState     single or combination of federal states
'   vCheckDate        vbscript date to check (e.g. as returned by now) or 
'                     "" (for current date)
'
' Returns:
'   Boolean           True = is public holiday
'
'--------------------------------------------------------------------
Function IsPublicHoliday ( nFederalState, vCheckDate )

  On Error Resume Next
  PBXScript.OutputTrace "-------> IsPublicHoliday"    
  PBXScript.OutputTrace "nFederalState = " & nFederalState    
  PBXScript.OutputTrace "vCheckDate = " & vCheckDate    
  
  Dim bReturn    
  bReturn = False    
  
  Dim a, b, c, d, e, f    
  Dim nTempYear, vTempDate    
  Dim Neujahr, Erscheinungsfest, Karfreitag, Ostersonntag, Ostermontag    
  Dim Maifeiertag, Rosenmontag, ChrHimmelfahrt, Pfingstmontag, Fronleichnam    
  Dim MarieaHimmelfahrt, Tagdereinheit, Reformationstag, Allerheiligen    
  Dim BussUndBettag, Weihnachten1, Weihnachten2     
  
  if not IsDate(vCheckDate) then vCheckDate = Now
  
  vTempDate = DateSerial(Year(vCheckDate), Month(vCheckDate), Day(vCheckDate))    
  nTempYear = Year(vTempDate)    
  PBXScript.OutputTrace "Using nTempYear = " & nTempYear    
  PBXScript.OutputTrace "Using vTempDate = " & vTempDate

  ' Gauss Formular
  a = nTempYear Mod 19    
  b = nTempYear \ 100    
  c = (8 * b + 13) \ 25 - 2    
  d = b - (nTempYear \ 400) - 2    
  e = (19 * (nTempYear Mod 19) + ((15 - c + d) Mod 30)) Mod 30    
  
  if e = 28 then
    if a > 10 then 
	  e = 27
	 end if
  elseif e = 29 then
    e = 28    
  end if    
  
  f = (d + 6 * e + 2 * (nTempYear Mod 4) + 4 * (nTempYear Mod 7) + 6) Mod 7    
  
  ' Calculate public holidays    
  Neujahr           = DateSerial(nTempYear, 1, 1)    
  Erscheinungsfest  = DateSerial(nTempYear, 1, 6)    
  Ostersonntag      = DateSerial(nTempYear, 3, e + f + 22)    
  Rosenmontag       = DateSerial(nTempYear, 3, e + f + 22 - 48)    
  Karfreitag        = DateSerial(nTempYear, 3, e + f + 22 - 2)    
  Ostermontag       = DateSerial(nTempYear, 3, e + f + 22 + 1)    
  Maifeiertag       = DateSerial(nTempYear, 5, 1)    
  ChrHimmelfahrt    = DateSerial(nTempYear, 3, e + f + 22 + 39)    
  Pfingstmontag     = DateSerial(nTempYear, 3, e + f + 22 + 50)    
  Fronleichnam      = DateSerial(nTempYear, 3, e + f + 22 + 60)    
  MarieaHimmelfahrt = DateSerial(nTempYear, 8, 15)     
  Tagdereinheit     = DateSerial(nTempYear, 10, 3)     
  Reformationstag   = DateSerial(nTempYear, 10, 31)     
  Allerheiligen     = DateSerial(nTempYear, 11, 1)     
  BussUndBettag     = DateSerial(nTempYear, 12, 25) - Weekday(DateSerial(nTempYear, 12, 25), vbMonday) - 4 * 7 - vbWednesday     
  Weihnachten1      = DateSerial(nTempYear, 12, 25)    
  Weihnachten2      = DateSerial(nTempYear, 12, 26)    
  
  ' Is public holiday?    
  select case vTempDate        
    case Neujahr            
      bReturn = True        
    case Erscheinungsfest            
      if (nFederalState and (vb_FS_BW or vb_FS_BY or vb_FS_ST)) then bReturn = True        
    case Ostersonntag            
      bReturn = True        
    case Rosenmontag            
      If(nFederalState and (vb_FS_KD)) then bReturn = True        
    case Karfreitag            
      bReturn = True        
    case Ostermontag            
      bReturn = True        
    case Maifeiertag            
      bReturn = True        
    case ChrHimmelfahrt            
      bReturn = True        
    case Pfingstmontag            
      bReturn = True        
    case Fronleichnam            
      if (nFederalState and (vb_FS_BW or vb_FS_BY or vb_FS_HE or vb_FS_NW or vb_FS_RP or vb_FS_SL or vb_FS_SN or vb_FS_TH)) then bReturn = True        
    case MarieaHimmelfahrt            
      if (nFederalState and (vb_FS_BY or vb_FS_SL)) then bReturn = True        
    case Tagdereinheit            
      bReturn = True        
    case Reformationstag            
      if (nFederalState and (vb_FS_BB or vb_FS_MV or vb_FS_SN or vb_FS_ST or vb_FS_TH)) then bReturn = True        
    case Allerheiligen            
      if (nFederalState and (vb_FS_BW or vb_FS_BY or vb_FS_NW or vb_FS_RP or vb_FS_SL)) then bReturn = True        
    case BussUndBettag            
      if (nFederalState and (vb_FS_SN)) then bReturn = True        
    case Weihnachten1            
      bReturn = True        
    case Weihnachten2            
      bReturn = True    
  end select    
  
  IsPublicHoliday = bReturn    
  
  PBXScript.OutputTrace "bReturn = " & bReturn    
  PBXScript.OutputTrace "<------- IsPublicHoliday"
  
End Function

bankholiday.zip

 

*** USAGE ***

 

To use this function in your Call Routing scripts just do the following:

  • Copy & Paste the above code into the Properties page of your script's Start block.
     
  • Now it can be used practically everywhere within the GSE, the most easiest place with be the Evaluate block. This would have the advantage that the return value from the function (is public holiday or not) is directly mapped on the two exits (True and False) of the block.

    To call the function just place the function call into the text field of the Properties page of the Evaluate block.

    Examples:

    Check in Nordrhein-Westfalen for current date:

      IsPublicHoliday(vb_FS_NW, "") 
      IsPublicHoliday(vb_FS_NW, Now) 

    Check in Bayern and Saarland for a given date:

      IsPublicHoliday(vb_FS_BY or vb_FS_SL, "01.05.2015") 

 

 

The first parameter of the function is either one or a list federal states (linked by OR). See the above code for the possible values for each federal state.

 

The second parameter is the date to check. This can either be a VBScript variant type (like e.g. Now returns or have been loaded from a database field of type datetime) or a string representing the date to be checked. Please note that in this case the string must be a valid date according the the regional settings configuration of your server machine (in regard to is format).

 

 

Note: previous versions of this script used other parameters!

 

 

*** ANLEITUNG ***

 

Um diese Funktion zur in einem eigenen Call Routing Skript zu verwenden, einfach die folgenden Schritte befolgen:

  • Den obigen Skript Code in die Eigenschaften Seite des Start Blocks kopieren.
     
  • Nun kann die Funktion beinahe überall im GSE direkt aufgerufen werde. Der sinnvollste Ort dafür ist sicherlich der Variable auswerten Block. Dies hat den Vorteil, dass der Rückgabewert der Funktion (gegebenes Datum ist ein Feiertag oder nicht) direkt auf einen der beiden Ausgänge (Wahr oder Falsch) geleitet wird.

    Um die Funktion aufzurufen, einfach den Funkitonsaufruf in das Textfeld auf der Eigenschaften Seite des Variable auswerten Blocks eintragen.

    Beispiele:

    Überprüfe in Nordrhein-Westfalen für aktuelles Datum:

      IsPublicHoliday(vb_FS_NW, "")
      IsPublicHoliday(vb_FS_NW, Now)  


    Überprüfe in Bayern und Saarland für ein bestimmtes Datum:

      IsPublicHoliday(vb_FS_BY or vb_FS_SL, "01.05.2015") 

 

 

Der erste Parameter ist das Bundesland (bzw. auch eine per OR verknüpfte Liste mehrer Bundesländer). Eine komplette Liste der verfügbaren Bundesländer und ihrer zu verwendenen Kürzel befindet sich am Anfang des Codes.

 

Der zweite Parameter ist das Datum, welches überprüft werden soll. Dies kann entweder ein VBSkript Variant Typ sein (wie ihn z.B. die Funktion Now zurück liefert oder aus einem Datenbank Feld vom Typ datetime geladen wurde) oder eine Zeichenkette (String) welche das Datum representiert. Zu beachten ist in diesem Fall, dass das Datum gemäß der regionalen Einstellungen der Server Maschine formatiert werden muss.

 

 

Hinweis: frühere Versionen dieser Funktion verwendeten ggf. andere Parameter!

 

Thank you,

Jörg!

 

Share this post


Link to post
Share on other sites

Thank you for this script!


 


I have some questions:


1. I got error messages because of the comma at the end of the line where the variables are declared (e.g. "Ostermontag,")


2. The constant "vbSN" is declared for the Bundesland Sachsen. But in the script vbSA is used i think.


3. How is the correct syntax to run the script? I think it is possible to omit the date if I want to check the current day, but I did not manage to do so.


Share this post


Link to post
Share on other sites
big_D    0

Hello Markus,


 


just checked the dates and it looks like vbSA is vbSN, so a typo.


 


The same should apply to the commas, I am not (yet) a Swyx user, but this looks like VB / VBA code and the commas at the end of the DIM statements should be omitted.


 


The function must be supplied with the State (Bundesland), but if the supplied date is empty, then it takes today's date.


 


If this is VBA, then the parameter can be made optional as follows:


 


Function IstFeiertag(BundesLand, Optional Datum = "")


 


As I said, I am not yet a Swyx user, so that might not be correct, I was here looking for other information, but this was the first post I saw. I hope that helps.


Share this post


Link to post
Share on other sites
Daniel    1

Okay guys,


 


as I am a total noob to VB-Code, could you please explain how exactly i have to set the variables, and where to put the evaluate-block and how it has to be configured?


As i understand i have to copy the whole code from above into the start-block.


Do i have to set the state right here like :


 


Function IstFeiertag(vbNW, DateSerial(Now))


 


And then i have to define a new "Evaluate Variable" - Block and put something like this in:


IstFeiertag = True use exit 0 ?


Share this post


Link to post
Share on other sites

To use this function within a call routing script just do the following:

  1. Copy & Paste the above code into the Start block.
  2. Call the function within an Evaluate (Variable auswerten) block.
  3. On it's Properties page just write the function call. For a check of today in Nordrhein-Westfalen use the following code:

    IstFeiertag(vbNW, DateSerial(Now))
     
  4. As the function returns already a boolean value it can be called directly and it's return value will be automatically mapped on the exits of the block.

Share this post


Link to post
Share on other sites
SvenS    17

I've tried the script but it doesn't work on my Swyx Server 2015


 


I get the following error in the log. It's seems that "DateSerial" is unknown. What can i do to get it work ?



21 11:04:22.101 001098 Info SrvScript 07ED4778 0000000f SPBXScript::OutputTrace () GSEVersion: 10.0.0.1
21 11:04:22.101 001098 Info SrvScript 07ED4778 0000000f SPBXScript::OutputTrace () case [Start0]
21 11:04:22.101 001098 Info SrvScript 07ED4778 0000000f SPBXScript::OutputTrace () --> gseStart()
21 11:04:22.101 001098 Info SrvScript 07ED4778 0000000f SPBXScript::OutputTrace () <-- gseStart, rc = 1 [gseStateStarted]
21 11:04:22.101 001098 Info SrvScript 07ED4778 0000000f SPBXScript::OutputTrace () case [Evaluate4]
21 11:04:22.101 001098 Info SrvScript 07ED4778 0000000f SPBXScript::OutputTrace () IstFeiertag(vbHE, DateSerial(Now))
21 11:04:22.112 000848 Info SwSIPSub 04B59058 00000000 SwSIPEndp::OnPresenceNotify () Notify for IpPbx user ID 18
21 11:04:22.112 000848 Info SwSIPSub 04B59058 00000000 SwSIPEndp::OnPresenceNotify () Found resource list subscription 06CDFBE0 of user for event package 'presence' in list of server subscriptions!
21 11:04:22.112 001f54 Info SwSIPSub 06CDFBE0 00000000 SFsm::OnProcessEvent () Subscribed evtUserStatusChange Result: 1 NewSt: Subscribed
21 11:04:22.117 001098 *Err SrvScrAPI 0703F468 0000000f SPBXScriptSite::OnScriptError ()
~Code: 0
~Src: Laufzeitfehler in Microsoft VBScript
~Desc: Falsche Anzahl an Argumenten oder ungültige Eigenschaftszuweisung: 'DateSerial'
~RetVal: 800a01c2
~Line: 14978
~Column: 8
~Source code:
~ PBXScriptOutputTrace "IstFeiertag(vbHE, DateSerial(Now))"

~ ^ error position


Share this post


Link to post
Share on other sites

Well, I don't know if you find someone here to explain the Easter days calculation.


 


The church itself wasn't able to come up with a proper set of rules for the for centuries. Only Carl Friedrich Gauss came up with a proper calculation in 1800, and updated it again in 1816.


 


To be honest, I can't explain it to you. It just works :-)


 


 


If you have public holidays on a fixed day just take a look how it is done e.g. with Christmas.

Share this post


Link to post
Share on other sites

Wow what a script! can someone explain the calculation method so i can change this to a dutch version? 

 

If you manage to create a dutch version of this function it would be nice if you would post it here as well. This is of course true also for ALL other countries :-)

Share this post


Link to post
Share on other sites
Younes    0

If you manage to create a dutch version of this function it would be nice if you would post it here as well. This is of course true also for ALL other countries :-)

 

Going to look in to it sometime! 

Share this post


Link to post
Share on other sites
Matthias    5

Hey Younes,


 


dutch or not - Easter has the same place in the calendar. So, don't care for the calculation, just use it :-)


The key-part after the calculation is the line "Ostersonntag = DateSerial(nTempYear, 3, e + f + 22)". Every other holiday around easter is calculated relatively to that day.


 


The harder part for you is to define all national and (so they exist) regional holidays for the netherlands, but this should be mostly work, not brainwork.


 


And don't forget to insert: FootballChampion = DateSerial(9999, 2, 31) :P


 


Cheers, Matthias

Share this post


Link to post
Share on other sites
Virikas    31

The harder part for you is to define all national and (so they exist) regional holidays for the netherlands, but this should be mostly work, not brainwork.

Most of the national public holidays in the netherlands are AFAIK as static defined as newyear (DateSerial(Year, 1, 1))),

so that should not be that complicated. The mathematical most complicated date (easter) is already there (and should be the same for the whole world.)

And don't forget to insert: FootballChampion = DateSerial(9999, 2, 31) :P

If we still talk about the netherlands:

It should be SoccerChampion =DateSerial(1/0, 2, 31) shouldn't it? :P

SCNR ;)

Share this post


Link to post
Share on other sites
Matthias    5

... as long as you keep "2, 31", I don't insist on a special year ;)
 
I tried the script on my system and had some problems with the CDate-function, because it doesn't cut off the time from the date when it's given with "now". And therefore I didn't get any holiday except the call is at midnight.
 
I changed the calculation of the year and the at the beginning a little bit and moved the DateSerial from the function-call into the function.
With the change, you can call the function in the following ways:

  • IsPublicHoliday (vb_FS_NI, "")
  • IsPublicHoliday (vb_FS_NI, Now)
  • IsPublicHoliday (vb_FS_NI, "29.2.2016")

 

[ADMIN EDIT: copied the script code into the first post of this thread]

 

 

and I also fixed the Sachsen-bug

 

Cheers, Matthias

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now