Calculation of the calendar of periodic events taking into account holidays

It is unlikely that PR will pull, but except in “I am promoting” I did not think of where to place it.



It all started with the fact that friends in the bank from the reporting department turned to me asking if I didn’t know a resource where you can automatically calculate the reporting dates. At that time, a large A3 sheet was used, which hung on the wall and contained a list of reporting forms and a deadline in accordance with the regulatory requirements of the Bank of Russia.

')

The whole point is that the terms of the Central Bank usually set, for example, as "7 working day of the month following the reporting". At that time, the forms can be both monthly and quarterly, annual, ten-day, five-week days. And the fact that for each calendar year in Russia the Government establishes a production calendar where holidays are fixed and all the necessary postponements only aggravates the situation, since it is necessary to adapt to all these changes.





Actually, on that sheet there were approximate days of delivery, somehow: for the 7th working day, the report was displayed for delivery in the column “7th day of the month”, which was obviously ahead of time. And it turned out, you always need to keep this in mind and do not forget to submit this or that report. All this was not very convenient. And the main task was to control the deadlines, since the corresponding measures are relied on for violation by the Central Bank. And if we allow a delay of more than 15 days, then the license may be revoked by law.



In general, having poked Outlook and a number of calendar managers, I realized that periodic events, which are calculated in working days, cannot be configured. I thought about making a crutch, since the reporting calendar can be calculated a year in advance and forget about it for a while.



The simplest idea that came to mind is to get CSV in the right format, which can be uploaded to Outlook. In order to generate CSV, the first thing I thought about was SQL, the benefit some time ago I was dealing with Oracle and the SQL standard was expanded there with a lot of useful functionality.



To do this, I got a list of events in Excel, and at the output I got a list of inserts for insertion into the setup table.



And it took 3 base tables:

• holiday tables to add (add_red_days)
• tables of days that are Saturday or Sunday, but become working (remove_red_days) due to the transfer
• Actually, the list of reporting forms themselves with the necessary parameters (cb_reports_settings)

SQL > desc remove_red_days

Name Type Nullable Default Comments

-- -- ---- -------- ------- --------

DT DATE Y



SQL > select * from remove_red_days

2 /



DT

--------- --

27.02.2010

13.11.2010



SQL > desc add_red_days

Name Type Nullable Default Comments

-- -- ---- -------- ------- --------

DT DATE Y



SQL > select * from add_red_days

2 /



DT

--------- --

01.01.2010

04.01.2010

05.01.2010

06.01.2010

07.01.2010

08.01.2010

22.02.2010

23.02.2010

08.03.2010

03.05.2010

10.05.2010

14.06.2010

04.11.2010

05.11.2010



14 rows selected



SQL > desc cb_reports_settings

Name Type Nullable Default Comments

------------- -- ------------- -------- ------- --------

FORM_NAME VARCHAR2(100) Y

CALENDAR_DAY NUMBER(2) Y

WORKING_DAY NUMBER(2) Y

TIME_TO_BE_SENT NUMBER(2) Y

IS_QUARTERLY NUMBER(1) Y



SQL > select * from cb_reports_settings

2 /



FORM_NAME CALENDAR_DAY WORKING_DAY TIME_TO_BE_SENT IS_QUARTERLY

------------------- -- ------------ ----------- --------------- ------------

350 1 2 0

301 1 2 13 0

634_ 1 4 0

101 1 4 0

134 1 4 0

135 1 6 0

153 1 6 0

711 1 5 0

301 5 2 13 0

115 1 7 0

116 1 7 0

117 1 7 0

118 1 7 0

155 1 7 0

125 1 8 0

157 1 8 0

501 1 8 0

603 1 8 0

102 1 8 1

110 1 8 0

128 1 7 0

129 1 7 0

302 1 10 0

316 1 10 0

251 1 10 1

401 1 10 0

301 10 2 13 0

350 11 2 0

634_ 11 4 0

345 1 11 1

405 1 17 0

301 15 2 13 0

301 20 2 13 0

350 21 2 0

634_ 21 4 0

301 25 2 13 0

342- () 1 10 0



37 rows selected



* This source code was highlighted with Source Code Highlighter .

Generating CSV for download in MS Outlook made such a request:

/* */

with t1 as ( select trunc(sysdate, 'y' )+rownum-1 dt from dual connect by level <=365),

--

/* */

t2 as ( select dt, row_number() over (partition by trunc(dt, 'mm' ) order by dt) working_day,

row_number() over ( order by dt) wd2 from t1

where (mod(to_char(dt, 'j' ),7) +1 not in (6,7)--

or dt in ( select dt from remove_red_days)) -- ,

and dt not in ( select dt from add_red_days)), --

--

/* */

t3 as ( select t1.dt, to_number(to_char(t1.dt, 'dd' )) calendar_day, working_day, wd2 from t1, t2

where t1.dt = t2.dt(+)

order by t1.dt),

--

/* , , */

t4 as (

select t3.*,

first_value(decode(working_day, null , null , t3.wd2) ignore nulls) over ( order by t3.dt rows between current row and unbounded following) wd3,

first_value(decode(working_day, null , null , t3.dt) ignore nulls) over ( order by t3.dt rows between current row and unbounded following) dt2,

nvl2(working_day, dt, null ) dt3

from t3

) /* select * from t4*/,

--

t5 as ( select ( select dt

from t4 t4_inner

where t4_inner.wd3 - t4_outer.wd3 + 1 = cbrs.working_day

and t4_inner.wd2 is not null

and rownum = 1) needed_date,

t4_outer.*,

cbrs.*

from t4 t4_outer,

cb_reports_settings cbrs

where t4_outer.calendar_day = cbrs.calendar_day

and (cbrs.is_quarterly = 0 or cbrs.is_quarterly = 1 and trunc(t4_outer.dt, 'mm' ) = trunc(t4_outer.dt, 'Q' ))

order by 1)

--

select '"' ||form_name|| '","' ||to_char(needed_date, 'dd.mm.yyyy' )|| '","' ||nvl(time_to_be_sent, '17' )|| ':00:00","' ||to_char(needed_date, 'dd.mm.yyyy' )|| '","' ||nvl(time_to_be_sent, '17' )|| ':00:00","","","' ||to_char(needed_date, 'dd.mm.yyyy' )|| '","09:00:00",""' col

from t5

order by form_name, needed_date



* This source code was highlighted with Source Code Highlighter .

At the output we get:

COL

--------

"101","14.01.2010","17:00:00","14.01.2010","17:00:00","","","14.01.2010","09:00:00",""

"101","04.02.2010","17:00:00","04.02.2010","17:00:00","","","04.02.2010","09:00:00",""

"101","04.03.2010","17:00:00","04.03.2010","17:00:00","","","04.03.2010","09:00:00",""

"101","06.04.2010","17:00:00","06.04.2010","17:00:00","","","06.04.2010","09:00:00",""

"101","07.05.2010","17:00:00","07.05.2010","17:00:00","","","07.05.2010","09:00:00",""

"101","04.06.2010","17:00:00","04.06.2010","17:00:00","","","04.06.2010","09:00:00",""

"101","06.07.2010","17:00:00","06.07.2010","17:00:00","","","06.07.2010","09:00:00",""

"101","05.08.2010","17:00:00","05.08.2010","17:00:00","","","05.08.2010","09:00:00",""

"101","06.09.2010","17:00:00","06.09.2010","17:00:00","","","06.09.2010","09:00:00",""

"101","06.10.2010","17:00:00","06.10.2010","17:00:00","","","06.10.2010","09:00:00",""

...





All this is successfully imported into Outlook.



And everything seems to be nothing, only since Oracle was absent at work, I had to do all the unloading at home. Not quite comfortable.



The new year has come, the delivery dates and the list of reporting forms have been updated in connection with the new indication of the Central Bank, a new production calendar has been installed. It was necessary to collect data on the new list of reports at work, at home to update the data in the tables, to regenerate CSV.



I didn’t like it and I decided to create a small web service that would be useful first of all to myself, as well as to other people who are faced with a similar task.



The process is divided into 3 steps:



1. It should be noted which dates are festive for the next year. This can be done after they accept the official production calendar. You can also change the sign of the day off to work, in case the transfer is carried out.







2. Creating an event calendar and adding individual events with parameters.



Options:

- frequency: month, quarter or year

- shift in months

- shift in calendar days

- shift in working days







The last three parameters are needed to set the shift deadline reporting. For example, if the periodicity “month” is selected in the upload, there will be 12 events for each month. By default, the event date is 01 day of each month. It is from her that we consider the shift.



- exclude the first

This option allows you to set specific dates for the first event of the year.



3. When we set up a calendar and a list of events, then we need to upload the file to upload it to your event manager.







Standard * .iCal and * .csv for MS Outlook are now supported.



Actually, the address getcalendar.ru . Since, most likely, the service will be very rarely useful (once a year), I did not do any local registration, and did authorization only through OpenID.



At work, in addition to the Central Bank otchetniki, began to use and tax, and personnel officers. On the desired day, a reminder pops up automatically in the morning in the morning, if you need to submit a report or make a payment.



I hope someone will come in handy.