На пиар вряд ли тянет, но кроме как в «Я пиарюсь» не придумал, куда разместить.
Все началось с того, что знакомые в банке из отдела отчетности обратились ко мне с просьбой, не знаю ли я ресурса, где можно автоматически рассчитать даты сдачи отчетности. На тот момент использовался большой лист формата А3, который висел на стене и содержал в себе список отчетных форм и срок сдачи в соответствии с нормативными требованиями Банка России.
Вся соль в том, что сроки ЦБ обычно устанавливает, например, как «7 рабочий день месяца, следующего за отчетным». В то время, как формы могут быть как месячные, так и квартальные, годовые, декадные, пятидневки. А тот факт, что на каждый календарный год в России Правительством устанавливается производственный календарь, где закрепляются праздничные дни и все необходимые переносы, только усугубляет положение, так как нужно подстраиваться под все эти изменения.
Собственно, на том листе стояли примерные дни сдачи, как-то: для 7 рабочего дня отчет отображался к сдаче в графе «7 число месяца», что заведомо было раньше срока. И получалось, всегда это нужно держать в голове и не забыть сдать тот или иной отчет. Все это было не сильно удобно. А главная задача была — контроль сроков сдачи, так как за нарушение от ЦБ полагаются соответствующие меры воздействия. А если допустить просрочку свыше 15 дней, то могут и лицензию по закону отозвать.
В общем, потыкав Outlook и еще ряд календарных менеджеров, понял, что периодические события, со сроком исчисляемым в рабочих днях, настроить нельзя. Подумал сделать костыль, так как календарь отчетности можно за раз рассчитать на год вперед и на время забыть про это.
Самая простая идея, которая пришла в голову — получить CSV в нужном формате, который можно подгрузить в Outlook. Для того, чтобы сгенерировать CSV, первое о чем подумал, — SQL, благо какое-то время назад разбирался с Oracle и стандарт SQL там расширен множеством полезного функционала.
Для этого завел список событий в Excel, на выходе получал список insert'ов для вставки в таблицу настройки.
А базовых таблиц понадобилось 3:
- таблицы праздничных дней, которые нужно добавить (add_red_days)
- таблицы дней, которые являются субботой или воскресенье, но в связи с переносом становятся рабочими (remove_red_days)
- собственно, список самих форм отчетности с необходимыми параметрами (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.Генерацию CSV для загрузки в MS Outlook сделал таким вот запросом:
/*сгенерируем все даты года*/
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.На выходе получаем:
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","Обычная"
...
Все это успешно импортируется в Outlook.
И все вроде ничего, вот только так как Oracle на работе отсутствовал, пришлось все выгрузки делать дома. Не совсем удобно.
Пришел новый год, обновились сроки сдачи и список отчетных форм в связи с новым указанием ЦБ, установлен новый производственный календарь. Пришлось собрать данные по новому списку отчетов на работе, дома обновлять данные в таблицах, заново генерить CSV.
Не понравилось мне это и решил я сделать небольшой веб-сервис, который будет полезен в первую очередь мне самому, а также другим людям, кто столкнулся с аналогичной задачей.
Процесс разбил на 3 шага:
1. Нужно отметить какие даты являются праздничными на очередной год. Это можно сделать после того, как примут официальный производственный календарь. Также можно поменять признак выходного дня на рабочий, в случае, если осуществляется перенос.
2. Создание календаря событий и добавление отдельных событий с параметрами.
Параметры:
— периодичность: месяц, квартал или год
— сдвиг в месяцах
— сдвиг в календарных днях
— сдвиг в рабочих днях
Последние три параметра нужны для установки сдвига срока сдачи отчетности. Например, если выбрана периодичность «месяц» в выгрузке будет 12 событий для каждого месяца. По умолчанию дата события 01 число каждого месяца. Вот от нее и считаем сдвиг.
— исключить первый
Этот параметр, позволяет задать особые сроки для первого события в году.
3. Когда мы настроили календарь и список событий, то нужно выгрузить файл для его последующей загрузки в свой менеджер событий.
Сейчас поддерживаются стандартный *.iCal и *.csv для MS Outlook.
Собственно, адрес getcalendar.ru. Так как, скорее всего, сервис будет полезен очень редко (раз в год), никакой регистрации локальной не стал делать, а сделал авторизацию только через OpenID.
На работе помимо отчетников ЦБ, стали пользоваться и налоговики, и кадровики. В нужный день в Outlook автоматически с утра всплывает напоминание, если нужно какой-то отчет предоставить или совершить платеж.
Надеюсь, кому-нибудь пригодится.
