Geekly Articles each Day
📜 ⬆️ ⬇️

Calculation of the calendar of periodic events taking into account holidays

image

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:

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.

image

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

image

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.

image

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.

Source: https://habr.com/ru/post/96496/

More articles:


All Articles