Volder July 29, 2010 at 20:39

Calculation of the calendar of periodic events taking into account holidays

It’s unlikely to be attracted to PR, but except in “I PR” I haven’t figured out where to place it.

It all started with the fact that acquaintances in the bank from the reporting department asked me whether I knew a resource where you can automatically calculate the dates for reporting. At that time, a large sheet of A3 format 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 Central Bank usually sets the dates, for example, as “7 working days of the month following the reporting one”. At the same time, the forms can be either monthly or quarterly, annual, ten-day, five-day. And the fact that the Government establishes a production calendar for each calendar year in Russia, where holidays and all necessary transfers are fixed, only aggravates the situation, since it is necessary to adapt to all these changes.

Actually, on that sheet there were approximate delivery days, for example: for the 7th working day the report was displayed for delivery in the column "7th day of the month", which was obviously ahead of schedule. 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 delivery deadlines, since the appropriate measures of influence are relied on for violation by the Central Bank. And if you allow an delay of more than 15 days, then they can also revoke the license by law.

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

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

To do this, I created a list of events in Excel, and on the output I received a list of inserts for insertion into the settings table.

And 3 tables were required:

Holiday tables to add (add_red_days)
tables of days that are Saturday or Sunday, but in connection with the transfer become working (remove_red_days)
in fact, 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.

Generation of CSV for uploading to MS Outlook was made by 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.

On the output we get: All this is successfully imported into Outlook. And everything seems to be nothing, only because Oracle was not at work, I had to do all the unloading at home. Not very convenient. The new year has come, the deadlines and the list of reporting forms have been updated in connection with the new instruction of the Central Bank, a new production calendar has been established. I had to collect data on a new list of reports at work, update the data in the tables at home, regenerate CSV. I didn’t like it and I decided to create a small web service that will be useful primarily to myself, as well as to other people who are faced with a similar problem. The process was divided into 3 steps:

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","Обычная"

...

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

2. Create an event calendar and add individual events with parameters.

Parameters:
- frequency: month, quarter or year
- shift in months
- shift in calendar days
- shift in working days

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

- exclude the first
This parameter allows you to set specific dates for the first event of the year.

3. When we set up the calendar and the list of events, we need to upload the file for its subsequent upload to our event manager.

Now standard * .iCal and * .csv for MS Outlook are supported.

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

In addition to Central Bank reporting, tax officers and personnel officers began to use it. On the desired day, a reminder automatically pops up in Outlook in the morning if you need to provide some report or make a payment.

I hope someone comes in handy.

Tags:

Calculation of the calendar of periodic events taking into account holidays

Also popular now: