/*

This code implements one part of functonality of

free available library PL/Vision. Please look www.quest.com

This library isn't optimalized for big numbers, for working

with n days (n > 10000), can be slow (on my P4 31ms).

Original author: Steven Feuerstein, 1996 - 2002

PostgreSQL implementation author: Pavel Stehule, 2006-2026

This module is under BSD Licence

*/

#define PLVDATE_VERSION "PostgreSQL PLVdate, version 3.7, October 2018"

#include "postgres.h"

#include "utils/date.h"

#include "utils/builtins.h"

#include <sys/time.h>

#include <stdlib.h>

#include "orafce.h"

#include "builtins.h"

#if PG_VERSION_NUM >= 160000

#include "varatt.h"

#endif

PG_FUNCTION_INFO_V1(plvdate_add_bizdays);

PG_FUNCTION_INFO_V1(plvdate_nearest_bizday);

PG_FUNCTION_INFO_V1(plvdate_next_bizday);

PG_FUNCTION_INFO_V1(plvdate_bizdays_between);

PG_FUNCTION_INFO_V1(plvdate_prev_bizday);

PG_FUNCTION_INFO_V1(plvdate_isbizday);

PG_FUNCTION_INFO_V1(plvdate_set_nonbizday_dow);

PG_FUNCTION_INFO_V1(plvdate_unset_nonbizday_dow);

PG_FUNCTION_INFO_V1(plvdate_set_nonbizday_day);

PG_FUNCTION_INFO_V1(plvdate_unset_nonbizday_day);

PG_FUNCTION_INFO_V1(plvdate_use_easter);

PG_FUNCTION_INFO_V1(plvdate_using_easter);

PG_FUNCTION_INFO_V1(plvdate_use_great_friday);

PG_FUNCTION_INFO_V1(plvdate_using_great_friday);

PG_FUNCTION_INFO_V1(plvdate_include_start);

PG_FUNCTION_INFO_V1(plvdate_including_start);

PG_FUNCTION_INFO_V1(plvdate_default_holidays);

PG_FUNCTION_INFO_V1(plvdate_version);

PG_FUNCTION_INFO_V1(plvdate_days_inmonth);

PG_FUNCTION_INFO_V1(plvdate_isleapyear);

#define CHECK_SEQ_SEARCH(_l, _s) \

do { \

if ((_l) < 0) { \

ereport(ERROR, \

(errcode(ERRCODE_INVALID_DATETIME_FORMAT), \

errmsg("invalid value for %s", (_s)))); \

} \

} while (0)

#define SUNDAY (1 << 0)

#define SATURDAY (1 << 6)

static unsigned char nonbizdays = SUNDAY | SATURDAY;

static bool use_easter = true;

static bool use_great_friday = true;

static bool include_start = true;

static int country_id = -1; /* unknown */

#define MAX_holidays 30

#define MAX_EXCEPTIONS 50

typedef struct

{

char day;

char month;

} holiday_desc;

typedef struct

{

unsigned char nonbizdays;

bool use_easter;

bool use_great_friday;

holiday_desc *holidays;

int holidays_c;

} cultural_info;

static holiday_desc holidays[MAX_holidays]; /* sorted array */

static DateADT exceptions[MAX_EXCEPTIONS]; /* sorted array */

static int holidays_c = 0;

static int exceptions_c = 0;

static holiday_desc czech_holidays[] = {

{1, 1}, //Novy rok

{1, 5}, //Svatek prace

{8, 5}, //Den osvobozeni

{5, 7}, //Den slovanskych verozvestu

{6, 7}, //Den upaleni mistra Jana Husa

{28, 9}, //Den ceske statnosti

{28, 10}, //Den vzniku samostatneho ceskoslovenskeho statu

{17, 11}, //Den boje za svobodu a demokracii

{24, 12}, //Stedry den

{25, 12}, //1. svatek vanocni

{26, 12} //2. svatek vanocni

};

static holiday_desc germany_holidays[] = {

{1, 1}, {1, 5}, {25, 5}, {4, 6}, {5, 6},

{15, 8}, {3, 10}, {25, 12}, {26, 12}

};

static holiday_desc poland_holidays[] = {

{1, 1}, {1, 5}, {3, 5}, {15, 6}, {15, 8},

{1, 11}, {11, 11}, {25, 12}, {26, 12}

};

static holiday_desc austria_holidays[] = {

{1, 1}, {6, 1}, {1, 5}, {25, 5}, {4, 6},

{5, 6}, {15, 6}, {15, 8}, {26, 10}, {1, 11},

{8, 12}, {25, 12}, {26, 12}

};

static holiday_desc slovakia_holidays[] = {

{1, 1}, {6, 1}, {1, 5}, {8, 5}, {5, 7},

{29, 8}, {1, 9}, {15, 9}, {1, 11}, {17, 11},

{24, 12}, {25, 12}, {26, 12}

};

static holiday_desc russian_holidays[] = {

{1, 1}, {2, 1}, {3, 1}, {4, 1}, {5, 1},

{7, 1}, {23, 2}, {8, 3}, {1, 5}, {9, 5},

{12, 6}, {4, 11}

};

static holiday_desc england_holidays[] = {

{1, 1}, {2, 1}, {1, 5}, {29, 5}, {28, 8},

{25, 12}, {26, 12}

};

static holiday_desc usa_holidays[] = {

{1, 1}, {16, 1}, {20, 2}, {29, 5}, {4, 7},

{4, 9}, {9, 10}, {11, 11}, {23, 11}, {25, 12}

};

static cultural_info defaults_ci[] = {

{SUNDAY | SATURDAY, true, true, czech_holidays, 11},

{SUNDAY | SATURDAY, true, true, germany_holidays, 9},

{SUNDAY | SATURDAY, true, false, poland_holidays, 9},

{SUNDAY | SATURDAY, true, false, austria_holidays, 13},

{SUNDAY | SATURDAY, true, true, slovakia_holidays, 13},

{SUNDAY | SATURDAY, false, false, russian_holidays, 12},

{SUNDAY | SATURDAY, true, true, england_holidays, 7},

{SUNDAY | SATURDAY, false, false, usa_holidays, 10}

};

static STRING_PTR_FIELD_TYPE states[] =

{

"Czech", "Germany", "Poland",

"Austria", "Slovakia", "Russia",

"Gb", "Usa",

NULL,

};

static int

dateadt_comp(const void *a, const void *b)

{

DateADT *_a = (DateADT *) a;

DateADT *_b = (DateADT *) b;

return *_a - *_b;

}

static int

holiday_desc_comp(const void *a, const void *b)

{

int result;

if (0 == (result = ((holiday_desc *) a)->month - ((holiday_desc *) b)->month))

result = ((holiday_desc *) a)->day - ((holiday_desc *) b)->day;

return result;

}

static void

calc_easter_sunday(int year, int *dd, int *mm)

{

int b,

d,

e,

q;

if (year < 1900 || year > 2099)

ereport(ERROR,

(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),

errmsg("date is out of range"),

errdetail("Easter is defined only for years between 1900 and 2099")));

b = 255 - 11 * (year % 19);

d = ((b - 21) % 30) + 21;

if (d > 38)

d -= 1;

e = (year + year / 4 + d + 1) % 7;

q = d + 7 - e;

if (q < 32)

{

*dd = q;

*mm = 3;

}

else

{

*dd = q - 31;

*mm = 4;

}

}

/*

* returns true, when day d is any easter holiday.

*

*/

static bool

easter_holidays(DateADT day, int y, int m)

{

if (use_great_friday || use_easter)

{

if (m == 3 || m == 4)

{

int easter_sunday_day;

int easter_sunday_month;

int easter_sunday;

calc_easter_sunday(y, &easter_sunday_day, &easter_sunday_month);

easter_sunday = date2j(y, easter_sunday_month, easter_sunday_day) - POSTGRES_EPOCH_JDATE;

if (use_easter && (day == easter_sunday || day == easter_sunday + 1))

return true;

if (use_great_friday && day == easter_sunday - 2)

{

/* Great Friday is introduced in Czech Republic in 2016 */

if (country_id == 0)

{

if (y >= 2016)

return true;

}

else

return true;

}

}

}

return false;

}

static DateADT

ora_add_bizdays(DateADT day, int ndays)

{

int d,

dx;

int y,

m,

auxd;

holiday_desc hd;

d = j2day(day + POSTGRES_EPOCH_JDATE);

dx = ndays > 0 ? 1 : -1;

while (ndays != 0)

{

d = (d + dx) % 7;

d = (d < 0) ? 6 : d;

day += dx;

if ((1 << d) & nonbizdays)

continue;

if (NULL != bsearch(&day, exceptions, exceptions_c,

sizeof(DateADT), dateadt_comp))

continue;

j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &auxd);

hd.day = (char) auxd;

hd.month = (char) m;

if (easter_holidays(day, y, m))

continue;

if (NULL != bsearch(&hd, holidays, holidays_c,

sizeof(holiday_desc), holiday_desc_comp))

continue;

ndays -= dx;

}

return day;

}

static int

ora_diff_bizdays(DateADT day1, DateADT day2)

{

int d,

ndays;

int y,

m,

auxd;

holiday_desc hd;

int loops = 0;

bool start_is_bizday = false;

if (day1 > day2)

{

DateADT aux_day;

aux_day = day1;

day1 = day2;

day2 = aux_day;

}

/* d is incremented on start of cycle, so now I have to decrease one */

d = j2day(day1 + POSTGRES_EPOCH_JDATE - 1);

ndays = 0;

while (day1 <= day2)

{

loops++;

day1 += 1;

d = (d + 1) % 7;

if ((1 << d) & nonbizdays)

continue;

if (NULL != bsearch(&day1, exceptions, exceptions_c,

sizeof(DateADT), dateadt_comp))

continue;

j2date(day1 + POSTGRES_EPOCH_JDATE, &y, &m, &auxd);

hd.day = (char) auxd;

hd.month = (char) m;

if (easter_holidays(day1, y, m))

continue;

if (NULL != bsearch(&hd, holidays, holidays_c,

sizeof(holiday_desc), holiday_desc_comp))

continue;

/* now the day have to be bizday, remember if first day was bizday */

if (loops == 1)

start_is_bizday = true;

ndays += 1;

}

/*

* decrease result when first day was bizday, but we don't want calculate

* first day.

*/

if (start_is_bizday && !include_start && ndays > 0)

ndays -= 1;

return ndays;

}

/****************************************************************

* PLVdate.add_bizdays

*

* Syntax:

* FUNCTION add_bizdays(IN dt DATE, IN ndays int) RETURNS DATE;

*

* Purpose:

* Get the date created by adding <n> business days to a date

*

****************************************************************/

Datum

plvdate_add_bizdays(PG_FUNCTION_ARGS)

{

DateADT day = PG_GETARG_DATEADT(0);

int ndays = PG_GETARG_INT32(1);

PG_RETURN_DATEADT(ora_add_bizdays(day, ndays));

}

/****************************************************************

* PLVdate.nearest_bizday

*

* Syntax:

* FUNCTION nearest_bizday(IN dt DATE) RETURNS DATE;

*

* Purpose:

* Get the nearest business date to a given date, user defined

*

****************************************************************/

Datum

plvdate_nearest_bizday(PG_FUNCTION_ARGS)

{

DateADT dt = PG_GETARG_DATEADT(0);

DateADT d1,

d2,

res;

d1 = ora_add_bizdays(dt, -1);

d2 = ora_add_bizdays(dt, 1);

if ((dt - d1) > (d2 - dt))

res = d2;

else

res = d1;

PG_RETURN_DATEADT(res);

}

/****************************************************************

* PLVdate.next_bizday

*

* Syntax:

* FUNCTION next_bizday(IN dt DATE) RETURNS DATE;

*

* Purpose:

* Get the next business date from a given date, user defined

*

****************************************************************/

Datum

plvdate_next_bizday(PG_FUNCTION_ARGS)

{

DateADT day = PG_GETARG_DATEADT(0);

PG_RETURN_DATEADT(ora_add_bizdays(day, 1));

}

/****************************************************************

* PLVdate.bizdays_between

*

* Syntax:

* FUNCTION bizdays_between(IN dt1 DATE, IN dt2 DATE)

* RETURNS int;

*

* Purpose:

* Get the number of business days between two dates

*

****************************************************************/

Datum

plvdate_bizdays_between(PG_FUNCTION_ARGS)

{

DateADT day1 = PG_GETARG_DATEADT(0);

DateADT day2 = PG_GETARG_DATEADT(1);

PG_RETURN_INT32(ora_diff_bizdays(day1, day2));

}

/****************************************************************

* PLVdate.prev_bizday

*

* Syntax:

* FUNCTION prev_bizday(IN dt DATE) RETURNS date;

*

* Purpose:

* Get the previous business date from a given date, user

* defined

*

****************************************************************/

Datum

plvdate_prev_bizday(PG_FUNCTION_ARGS)

{

DateADT day = PG_GETARG_DATEADT(0);

PG_RETURN_DATEADT(ora_add_bizdays(day, -1));

}

/****************************************************************

* PLVdate.isbizday

*

* Syntax:

* FUNCTION isbizday(IN dt DATE) RETURNS bool;

*

* Purpose:

* Call this function to determine if a date is a business day

*

****************************************************************/

Datum

plvdate_isbizday(PG_FUNCTION_ARGS)

{

DateADT day = PG_GETARG_DATEADT(0);

int y,

m,

d;

holiday_desc hd;

if (0 != ((1 << j2day(day + POSTGRES_EPOCH_JDATE)) & nonbizdays))

return false;

if (NULL != bsearch(&day, exceptions, exceptions_c,

sizeof(DateADT), dateadt_comp))

return false;

j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);

hd.month = m;

hd.day = d;

if (easter_holidays(day, y, m))

return false;

PG_RETURN_BOOL(NULL == bsearch(&hd, holidays, holidays_c,

sizeof(holiday_desc), holiday_desc_comp));

}

/****************************************************************

* PLVdate.set_nonbizday

*

* Syntax:

* FUNCTION set_nonbizday(IN dow VARCHAR) RETURNS void;

*

* Purpose:

* Set day of week as non bussines day

*

****************************************************************/

Datum

plvdate_set_nonbizday_dow(PG_FUNCTION_ARGS)

{

unsigned char check;

text *day_txt = PG_GETARG_TEXT_PP(0);

int d = ora_seq_search(VARDATA_ANY(day_txt), ora_days, VARSIZE_ANY_EXHDR(day_txt));

CHECK_SEQ_SEARCH(d, "DAY/Day/day");

check = nonbizdays | (1 << d);

if (check == 0x7f)

ereport(ERROR,

(errcode(ERRCODE_DATA_EXCEPTION),

errmsg("nonbizday registration error"),

errdetail("Constraint violation."),

errhint("One day in week have to be bizday.")));

nonbizdays = nonbizdays | (1 << d);

PG_RETURN_VOID();

}

/****************************************************************

* PLVdate.unset_nonbizday

*

* Syntax:

* FUNCTION unset_nonbizday(IN dow VARCHAR) RETURNS void;

*

* Purpose:

* Unset day of week as non bussines day

*

****************************************************************/

Datum

plvdate_unset_nonbizday_dow(PG_FUNCTION_ARGS)

{

text *day_txt = PG_GETARG_TEXT_PP(0);

int d = ora_seq_search(VARDATA_ANY(day_txt), ora_days, VARSIZE_ANY_EXHDR(day_txt));

CHECK_SEQ_SEARCH(d, "DAY/Day/day");

nonbizdays = (nonbizdays | (1 << d)) ^ (1 << d);

PG_RETURN_VOID();

}

/****************************************************************

* PLVdate.set_nonbizday

*

* Syntax:

* FUNCTION set_nonbizday(IN day DATE) RETURNS void;

* FUNCTION set_nonbizday(IN day DATE, IN repeat := false BOOL) RETURNS void;

*

* Purpose:

* Set day as non bussines day, second arg specify year's

* periodicity

*

****************************************************************/

Datum

plvdate_set_nonbizday_day(PG_FUNCTION_ARGS)

{

DateADT arg1 = PG_GETARG_DATEADT(0);

bool arg2 = PG_GETARG_BOOL(1);

int y,

m,

d;

holiday_desc hd;

if (arg2)

{

if (holidays_c == MAX_holidays)

ereport(ERROR,

(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),

errmsg("nonbizday registration error"),

errdetail("Too much registered nonbizdays."),

errhint("Increase MAX_holidays in 'plvdate.c'.")));

j2date(arg1 + POSTGRES_EPOCH_JDATE, &y, &m, &d);

hd.month = m;

hd.day = d;

if (NULL != bsearch(&hd, holidays, holidays_c, sizeof(holiday_desc), holiday_desc_comp))

ereport(ERROR,

(errcode(ERRCODE_DUPLICATE_OBJECT),

errmsg("nonbizday registration error"),

errdetail("Date is registered.")));

holidays[holidays_c].month = m;

holidays[holidays_c].day = d;

holidays_c += 1;

qsort(holidays, holidays_c, sizeof(holiday_desc), holiday_desc_comp);

}

else

{

if (exceptions_c == MAX_EXCEPTIONS)

ereport(ERROR,

(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),

errmsg("nonbizday registration error"),

errdetail("Too much registered nonrepeated nonbizdays."),

errhint("Increase MAX_EXCEPTIONS in 'plvdate.c'.")));

if (NULL != bsearch(&arg1, exceptions, exceptions_c, sizeof(DateADT), dateadt_comp))

ereport(ERROR,

(errcode(ERRCODE_DUPLICATE_OBJECT),

errmsg("nonbizday registration error"),

errdetail("Date is registered.")));

exceptions[exceptions_c++] = arg1;

qsort(exceptions, exceptions_c, sizeof(DateADT), dateadt_comp);

}

PG_RETURN_VOID();

}

/****************************************************************

* PLVdate.unset_nonbizday

*

* Syntax:

* FUNCTION unset_nonbizday(IN day DATE) RETURNS void;

* FUNCTION unset_nonbizday(IN day DATE, IN repeat := false BOOL) RETURNS void;

*

* Purpose:

* Unset day as non bussines day, second arg specify year's

* periodicity

*

****************************************************************/

Datum

plvdate_unset_nonbizday_day(PG_FUNCTION_ARGS)

{

DateADT arg1 = PG_GETARG_DATEADT(0);

bool arg2 = PG_GETARG_BOOL(1);

int y,

m,

d;

bool found = false;

int i;

if (arg2)

{

j2date(arg1 + POSTGRES_EPOCH_JDATE, &y, &m, &d);

for (i = 0; i < holidays_c; i++)

{

if (!found && holidays[i].month == m && holidays[i].day == d)

found = true;

else if (found)

{

holidays[i - 1].month = holidays[i].month;

holidays[i - 1].day = holidays[i].day;

}

}

if (found)

holidays_c -= 1;

}

else

{

for (i = 0; i < exceptions_c; i++)

if (!found && exceptions[i] == arg1)

found = true;

else if (found)

exceptions[i - 1] = exceptions[i];

if (found)

exceptions_c -= 1;

}

if (!found)

ereport(ERROR,

(errcode(ERRCODE_UNDEFINED_OBJECT),

errmsg("nonbizday unregistration error"),

errdetail("Nonbizday not found.")));

PG_RETURN_VOID();

}

/****************************************************************

* PLVdate.use_easter

*

* Syntax:

* FUNCTION unuse_easter() RETURNS void;

* FUNCTION use_easter() RETURNS void;

* FUNCTION use_easter(IN bool) RETURNS void

*

* Purpose:

* Have to use easter as nonbizday?

*

****************************************************************/

Datum

plvdate_use_easter(PG_FUNCTION_ARGS)

{

use_easter = PG_GETARG_BOOL(0);

PG_RETURN_VOID();

}

/****************************************************************

* PLVdate.using_easter

*

* Syntax:

* FUNCTION using_easter() RETURNS bool

*

* Purpose:

* Use it easter as nonbizday?

*

****************************************************************/

Datum

plvdate_using_easter(PG_FUNCTION_ARGS)

{

PG_RETURN_BOOL(use_easter);

}

/****************************************************************

* PLVdate.use_great_friday

*

* Syntax:

* FUNCTION unuse_great_friday() RETURNS void;

* FUNCTION use_great_friday() RETURNS void;

* FUNCTION use_great_friday(IN bool) RETURNS void

*

* Purpose:

* Have to use great_friday as nonbizday?

*

****************************************************************/

Datum

plvdate_use_great_friday(PG_FUNCTION_ARGS)

{

use_great_friday = PG_GETARG_BOOL(0);

PG_RETURN_VOID();

}

/****************************************************************

* PLVdate.using_great_friday

*

* Syntax:

* FUNCTION using_great_friday() RETURNS bool

*

* Purpose:

* Use it great friday as nonbizday?

*

****************************************************************/

Datum

plvdate_using_great_friday(PG_FUNCTION_ARGS)

{

PG_RETURN_BOOL(use_great_friday);

}

/****************************************************************

* PLVdate.include_start

*

* Syntax:

* FUNCTION include_start() RETURNS void;

* FUNCTION noinclude_start() RETURNS void;

* FUNCTION include_start(IN bool) RETURNS void

*

* Purpose:

* Have to include current day in bizdays_between calculation?

*

****************************************************************/

Datum

plvdate_include_start(PG_FUNCTION_ARGS)

{

include_start = PG_GETARG_BOOL(0);

PG_RETURN_VOID();

}

/****************************************************************

* PLVdate.including_start

*

* Syntax:

* FUNCTION including_start() RETURNS bool

*

* Purpose:

* include current day in bizdays_between calculation?

*

****************************************************************/

Datum

plvdate_including_start(PG_FUNCTION_ARGS)

{

PG_RETURN_BOOL(include_start);

}

/*

* Load some national configurations

*

*/

Datum

plvdate_default_holidays(PG_FUNCTION_ARGS)

{

text *country = PG_GETARG_TEXT_PP(0);

country_id = ora_seq_search(VARDATA_ANY(country), states, VARSIZE_ANY_EXHDR(country));

CHECK_SEQ_SEARCH(country_id, "STATE/State/state");

nonbizdays = defaults_ci[country_id].nonbizdays;

use_easter = defaults_ci[country_id].use_easter;

use_great_friday = defaults_ci[country_id].use_great_friday;

exceptions_c = 0;

holidays_c = defaults_ci[country_id].holidays_c;

memcpy(holidays, defaults_ci[country_id].holidays, holidays_c * sizeof(holiday_desc));

PG_RETURN_VOID();

}

/*

* helper maintaince functions

*/

Datum

plvdate_version(PG_FUNCTION_ARGS)

{

PG_RETURN_CSTRING(PLVDATE_VERSION);

}

/****************************************************************

* PLVdate.days_inmonth

*

* Syntax:

* FUNCTION days_inmonth(date) RETURNS integer

*

* Purpose:

* Returns month's length

*

****************************************************************/

Datum

plvdate_days_inmonth(PG_FUNCTION_ARGS)

{

DateADT day = PG_GETARG_DATEADT(0);

int result;

int y,

m,

d;

j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);

result = date2j(y, m + 1, 1) - date2j(y, m, 1);

PG_RETURN_INT32(result);

}

/****************************************************************

* PLVdate.isleapyear

*

* Syntax:

* FUNCTION isleapyear() RETURNS bool

*

* Purpose:

* Returns true, if year is leap

*

****************************************************************/

Datum

plvdate_isleapyear(PG_FUNCTION_ARGS)

{

DateADT day = PG_GETARG_DATEADT(0);

int y,

m,

d;

bool result;

j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);

result = (((y % 4) == 0) && ((y % 100) != 0)) || ((y % 400) == 0);

PG_RETURN_BOOL(result);

}

/****************************************************************

* PLVdate.set_nonbizdays

*

* Syntax:

* FUNCTION set_nonbizdays(IN dow bool[7]) RETURNS void;

*

* Purpose:

* Set pattern bussines/nonbussines days in week

*

****************************************************************/

/****************************************************************

* PLVdate.set_nonbizday

*

* Syntax:

* FUNCTION set_nonbizdays(IN days DATE[]) RETURNS void;

* FUNCTION set_nonbizdays(IN days DATE[], IN repeat := false BOOL) RETURNS void;

*

* Purpose:

* Set days as non bussines day, second arg specify year's

* periodicity

*

****************************************************************/

/****************************************************************

* PLVdate.display

*

* Syntax:

* FUNCTION display() RETURNS void;

*

* Purpose:

* Show current calendar

*

****************************************************************/