Учет услуг сотовой связи (постановка)

Материал из GedeminWiki
(Различия между версиями)
Перейти к: навигация, поиск
(→‎Примеры запросов)
 
(не показаны 47 промежуточных версий 2 участников)
Строка 1: Строка 1:
−
Предприятие заключило с оператором сотовой связи договор. В рамках договора предоставлены сим карты, которые предприятие передает своим сотрудникам. Ежемесячно оператор выставляет суммарный счет для оплаты и листинг к нему, где расписаны все суммы по каждому номеру. На предприятии действует определенная политика каким группам абонентов, какие услуги оплачивать за счет предприятия, а какие вычитать из начисленной зарплаты.
+
Предприятие заключило с оператором сотовой связи договор. В рамках договора предоставлены сим карты, которые предприятие передает своим сотрудникам. Ежемесячно оператор выставляет суммарный счет для оплаты и листинг к нему, где расписаны все начисления по каждому номеру. На предприятии действует определенная политика каким группам абонентов, какую часть суммы оплачивать за счет предприятия, а какую -- вычитать из начисленной зарплаты. Цель программы -- организовать парсинг листинга и вычисление сумм к удержанию из зарплаты по каждому сотруднику.
  
−
Услуга в нашем понимании это звонок, звонок на номер стационарной связи, звонок на мобильный номер, звонок на номер предприятия, звонок в роуминге и т.п. Справочник услуг наполняется в процессе внедрения и зависит от принятой политики предприятия.
+
# Введем понятие '''услуги''' -- это звонок на номер стационарной связи, звонок на мобильный номер, звонок на номер предприятия, звонок в роуминге, отправка СМС и т.п. Справочник услуг [[#usr$call_service|usr$call_service]] наполняется в процессе внедрения и зависит от принятой политики предприятия. При изменении справочника необходимо внести соответствующие коррективы в макрос импорта данных.
 +
# Будем вести список СИМ карт и отслеживать историю их перемещения между сотрудниками.
 +
# Каждый сотрудник принадлежит одной определенной группе абонентов. Отслеживается история перемещения сотрудников между группами.
 +
# Правило распределения сумм привязано к группе и услуге. Правила действуют с определенной даты. Последующее правило отменяет предыдущее.
 +
# В базе данных хранится вся история звонков (листинг) за 2-3 последних месяца. Должна быть предусмотрена функция удаления данных прошлых периодов.
 +
# В базе данных хранятся итоговые значения начислений по всем номерам за все время. Итоговые значения, на основании которых сформированы документы подсистемы Заработная плата или бухгалтерские проводки, блокируются для изменения.
 +
<!--
 +
# Лимиты устанавливаются для каждой группы абонентов. Сумма в пределах лимита покрывается предприятием. Сверх этой суммы -- оплачивает работник. Лимит действует с определенной даты до того момента, пока не будет изменен следующим установленным значением. Если для конкретной группы лимит не указан, то берется значение родительской группы и т.д. до корня дерева. Лимит может отсутствовать вообще.
 +
-->
 +
=== Алгоритм обработки листинга ===
  
−
Исходный файл (Excel или XML) мы обрабатываем последовательно. Распознав запись, мы берем из нее следующую информацию:
+
# На вход передаем: имя файла листинга, номер контракта, период.
 +
## Если в таблице с историей присутствует информация за указанный период, по указанному контракту -- предупреждаем пользователя и останавливаем процесс. Повторный импорт запрещен.
 +
# Исходный файл листинга (Excel или XML) обрабатываем последовательно. Полученную информацию помещаем в таблицу истории звонков для дальнейшей обработки.
 +
## Если, в процессе импорта, встречен новый телефонный номер, он помещается в справочник.
 +
## Если это телефонный номер нашего предприятия, он помечается флагом и в поле номер контракта заносится соответствующая информация.
 +
# Проверяем, не появились ли новые номера, которые не связаны ни с одним из работников. Если да, то предупреждаем пользователя. Он должен зайти в окно справочника номеров и присвоить им сотрудников.
 +
# По таблице истории звонков, в пределах периода обработки, организуем цикл по работникам предприятия:
 +
## Обнуляем переменные:
 +
### S1 -- рассчитанная рассчитанная сумма стоимости услуг
 +
### S2 -- рассчитанная сумма, оплачиваемая предприятием
 +
## Сканируем все записи для данного работника
 +
### Для каждой записи смотрим вид услуг, определяем группу, действующую на время записи, и извлекаем правило распределения затрат. Если группа не назначена, то все расходы идут за счёт сотрудника.
 +
### В соответствии с правилом увеличиваем переменные S1 и S2
 +
## По окончании, проверяем S2 на установленный для группы данного сотрудника лимит и, при необходимости, выполняем корректировку. Если лимит не установлен, то корректировка не выполняется.
 +
## Заносим информацию в таблицу с итоговыми значениями
 +
# В таблице с итоговыми значениями дополнительно присутствует скорректированная сумма, оплачиваемая предприятием, -- S3. Изначально, она пустая. Ответственное лицо может редактировать эту колонку, таким образом уменьшая или увеличивая сумму, оплачиваемую за работника предприятием.
 +
# После ручной корректировки формируется документ типа "Ручной ввод" для подситемы Заработная плата. После того, как для записи из таблицы с итоговыми значениями сформирована позиция документа ручной ввод, она блокируется для изменений и доступна только в режиме просмотра. За блокировку отвечают соответствующие триггеры.
  
−
* Дата и время
+
=== Структуры данных ===
−
* Тип услуги
+
−
* Абонент
+
−
* Стоимость
+
  
−
Зная абонента -- определяем его группу. По группе и типу услуги берем правило распределения затрат и вычисляем какую сумму возьмет на себя предприятие, а какую абонент покроет из своей зарплаты.
+
При описании структуры таблиц мы опускаем стандартные поля.
  
−
На выходе, после обработки исходного файла, мы должны получить список абонентов и две суммы для каждого: "на затраты" и "из зарплаты". Если для группы абонента установлен лимит, то проверяем сумму "на затраты" и ее превышение над лимитом переносим на вычет из зарплаты.
+
==== usr$call_phone ====
  
−
Лимиты устанавливаются для каждой группы абонентов. Сумма в пределах лимита покрывается предприятием. Сверх этой суммы -- оплачивает работник. Лимит действует с определенной даты до того момента, пока не будет изменен следующим установленным значением.
+
Справочник номеров телефонов. Таблица с идентификатором. Сюда заносятся все номера телефонов, которые фигурируют в листинге. В номере храним только цифры без разделителей, плюсов, скобок и т.п., в формате МРН, где М -- международный код, Р -- региональный код или код оператора, Н -- номер, или номера вида: #12345. Номера вида 802955555555 (если они будут в листинге) преобразуем программно в 375295555555.  
  
−
После этого оператор может вручную изменить (подкореетировать) сумму, которая будет возмещаться предприятием.  
+
{| class="wikitable" border="1"
 +
!Поле!!Тип поля!!Описание
 +
|-
 +
|usr$phone||usr$call_dphone||Номер телефона. По этому полю организуем уникальный индекс.
 +
|-
 +
|usr$our||dboolean_notnull||Признак нашего номера.
 +
|-
 +
|usr$contract||dtext20||Номер контракта или любое кодовое обозначение (например, VELCOM или MTS). Используется для группировки и фильтрации номеров, принадлежащих нашему предприятию в случае, когда контрактов с разными поставщиками услуг связи заключено несколько.
 +
|}
  
−
Правила устанавливаются для группы и конкретной услуги. В правиле мы указываем следующие параметры:
+
==== usr$call_service ====
  
−
* Рабочее время согласно графика -- Если признак установлен, то рабочее время учитывается согласно графика работы. Информации берется из подсистемы Кадры и Зарплата. Звонки в рабочее время оплачиваются предприятием. Вне рабочего времени -- сотрудником. Если признак установлен, то не действуют поля с временными интервалами.
+
Справочник услуг. Единица измерения услуги -- ссылка на стандартный справочник [[GD_VALUE]].
  
 +
{| class="wikitable" border="1"
 +
!Поле!!Тип поля!!Описание
 +
|-
 +
|usr$name||dname||Наименование услуги. По этому полю организуем уникальный индекс.
 +
|-
 +
|usr$valuekey||usr$call_dvaluekey||Ссылка на единицу измерения.
 +
|-
 +
|usr$onetime||dboolean_notnull||Признак одноразовая ли услуга
 +
|}
  
 +
==== usr$call_history ====
  
−
<syntaxhighlight lang="SQL">
+
Сюда попадают данные при импорте из файлов листинга.
  
−
/*
+
{| class="wikitable" border="1"
−
* Домены
+
!Поле!!Тип поля!!Описание
−
*
+
|-
−
*/
+
|usr$builddate||DATE NOT NULL||Дата построения отчёта
 +
|-
 +
|usr$date||DATE NOT NULL||Дата регистрации услуги.
 +
|-
 +
|usr$time||TIME NOT NULL||Время регистрации услуги.
 +
|-
 +
|usr$userkey||usr$call_dphonekey NOT NULL||Номер абонента, для которого зарегистрирована услуга. Ссылка на справочник номеров.
 +
|-
 +
|usr$fromkey||usr$call_dphonekey||Номер абонента совершающего звонок или отправляющего СМС. Следует обратить внимание, что поля usr$userkey и usr$fromkey будут различаться для входящих звонков. Хотя, внутри РБ входящие звонки обычно не тарифицируются, но они могут иметь стоимость при использовании роуминга.
 +
|-
 +
|usr$tokey||usr$call_dphonekey||Номер абонента принимающего звонок или получающего СМС.
 +
|-
 +
|usr$volume||usr$call_dvolume||Объем оказанной услуги в числовом выражении. Там где это применимо. Например, при пересылке данных, доступе в интернет.
 +
|-
 +
|usr$duration||TIME||Продолжительность связи, там где это применимо.
 +
|-
 +
|usr$servicekey||usr$call_dservicekey||Ссылка на справочник услуг.
 +
|-
 +
|usr$cost||usr$call_dcost NOT NULL||Брутто стоимость, как указано в листинге. 0 для бесплатных или нетарифицируемых услуг.
 +
|}
  
−
CREATE DOMAIN usr$call_dphone
+
По полям usr$date, usr$time, usr$userkey, usr$servicekey создается уникальный индекс. Это обезопасит нас от повторного импорта данных.
−
  VARCHAR(12)
+
−
  CHECK(VALUE SIMILAR TO '[0-9]{5,12}');
+
  
−
-- ссылка на GD_VALUE
+
==== usr$call_phonelink ====
−
usr$call_dvaluekey
+
  
−
-- обязательная ссылка на услугу
+
Простая таблица с идентификатором. Здесь фиксируется история перехода СИМ карт из рук в руки. Если карточка никому не назначена, то либо для нее вообще нет записи в этой таблице (исходное состояние), либо добавляется запись, где usr$employeekey IS NULL.
−
usr$call_dservicekey
+
  
−
/*
+
{| class="wikitable" border="1"
−
*  Все номера телефонов, которые появляются в листинге.
+
!Поле!!Тип поля!!Описание
−
*  Храним только цифры без разделителей, плюсов, скобок и т.п.
+
|-
−
*  Флагом помечаем номера нашего предприятия.  
+
|usr$phonekey||usr$call_dphonekey NOT NULL||Ссылка на справочник номеров телефонов.
−
*/
+
|-
 +
|usr$employeekey||usr$call_employeekey||Ссылка на справочник контактов.
 +
|-
 +
|usr$fromdate||DATE NOT NULL||Дата, с которой карта передана сотруднику.
 +
|}
  
−
CREATE TABLE usr$call_phone (
+
Уникальный индекс по полям usr$phonekey, usr$employeekey, usr$fromdate.
−
  id            dintkey,
+
−
  usr$phone      usr$call_dphone,
+
−
  usr$our        dboolean_notnull
+
−
);
+
  
−
/*
+
==== usr$call_group ====
−
*  Список услуг: телефонный звонок, СМС, звонок в роуминге,
+
−
*  звонок на стационарную сеть и т.п.
+
−
*  Единица измерения услуги -- ссылка на стандартный
+
−
*  справочник GD_VALUE.
+
−
*/
+
  
−
CREATE TABLE usr$call_service (
+
Справочник групп абонентов. Простая таблица с идентификатором.
−
  id            dintkey,
+
−
  usr$name      dname,
+
−
  usr$valuekey  usr$call_dvaluekey
+
−
);
+
  
−
/*
+
{| class="wikitable" border="1"
−
*  Хронология звонков, услуг и т.п.
+
!Поле!!Тип поля!!Описание
−
*  Указывается объем и/или продолжительность.  
+
|-
−
*/
+
|usr$name||dname||Наименование группы. Организуем уникальный индекс по этому полю.
 +
|-
 +
|usr$comment||dtext1024||Комментарий.
 +
|}
  
−
CREATE TABLE usr$call_history (
+
==== usr$call_grouplink ====
−
  id            dintkey,
+
−
  usr$date      DATE NOT NULL,
+
−
  usr$time      TIME NOT NULL,
+
−
  usr$from      usr$call_dphonekey NOT NULL,
+
−
  usr$to        usr$call_dphonekey,
+
−
  usr$volume    usr$call_dvolume,
+
−
  usr$duration  TIME,
+
−
  usr$servicekey usr$call_dservicekey,
+
−
  usr$cost      usr$call_dcost NOT NULL
+
−
);
+
  
−
/*
+
История привязки номера к группе. Простая таблица с идентификатором.
−
*  История перехода СИМ карт из рук в руки.
+
−
*  Если карточка никому не назначена --
+
−
*  usr$employeekey IS NULL
+
−
*/
+
  
−
CREATE TABLE usr$call_phonelink (
+
{| class="wikitable" border="1"
−
  id              dintkey,
+
!Поле!!Тип поля!!Описание
−
  usr$phonekey    usr$call_dphonekey NOT NULL,
+
|-
−
  usr$employeekey usr$call_employeekey,
+
|usr$groupkey||usr$call_dgroupkey||Ссылка на группу.
−
  usr$fromdate   DATE NOT NULL
+
|-
−
);
+
|usr$phonekey||usr$call_dphonekey||Ссылка на номер.
 +
|-
 +
|usr$fromdate||DATE NOT NULL||Дата, с которой действует привязка.
 +
|}
  
−
/*
+
Уникальный индекс по полям usr$groupkey, usr$phonekey, usr$fromdate.
−
*  Список групп абонентов.
+
−
*  Правила вычета денег привязаны к группам.
+
−
* 
+
−
*/
+
  
−
CREATE TABLE usr$call_group (
+
==== usr$call_rule ====
−
  id              dintkey,
+
−
  usr$name        dname,
+
−
  usr$comment    dtext1024,
+
−
/*добавлено 17.08.2011*/
+
−
  usr$limit      usr$call_dcost,
+
−
  usr$fromdate        ddate NOT NULL
+
−
/**/
+
−
);
+
  
−
/*
+
Правила распределения затрат. Простая таблица с идентификатором. Правило действует следующим образом:
−
*  Привязка номера к группе.
+
−
* 
+
−
*/
+
  
−
CREATE TABLE usr$call_grouplink (
+
# Мы разделяем дни на рабочие, праздничные и субботы. Праздничные дни это воскресенье + дни из справочника государственных праздников.
−
  id              dintkey,
+
# Для каждого типа дней введено два интервала.
−
/*Изменения 17.08.2011*/
+
# Если время регистрации услуги попадает в один из указанных интервалов, то она оплачивается предприятием. Если не попадает, то -- вычитается из зарплаты. Следовательно, если указан интервал 00:00:00-24:00:00, то услуга будет оплачиваться предприятием полностью. Если ни один из интервалов не указан, то услуга полностью оплачивается работником. Незаданный интервал -- это пара NULL-NULL. Так как есть услуги, которые всегда оплачиваются предприятием и есть услуги, которые никогда не оплачиваются, то, для упрощения ввода, на уровне диалогового окна надо будет предусмотреть селектор: всегда оплачивается, никогда не оплачивается и оплачивается по расписанию.
−
  /*usr$groupkey    dintkey,*/
+
−
  usr$groupkey    usr$call_dgroupkey,
+
−
  /*usr$phonekey    dintkey,*/
+
−
  usr$phonekey    usr$call_dphonekey,
+
−
/**/
+
−
  usr$fromdate    DATE NOT NULL
+
−
);
+
  
−
/*
+
{| class="wikitable" border="1"
−
*  Правила.  
+
!Поле!!Тип поля!!Описание
−
* 
+
|-
−
* 
+
|usr$servicekey||usr$call_dservicekey||Ссылка на услугу.
−
* 
+
|-
−
* 
+
|usr$name||dname||Наименование правила. Уникальный индекс по этому полю.
−
* 
+
|-
−
*/
+
|w1start||TIME||Начало интервала 1 для рабочего дня.
−
/*Исправлено 17.08.2011*/
+
|-
−
/*CREATE TABLE usr$call_rule (
+
|w1finish||TIME||Окончание интервала 1 для рабочего дня.
−
  id                dintkey,
+
|-
−
  usr$name          dname,
+
|w2start||TIME||Начало интервала 2 для рабочего дня.
−
  usr$servicekey    usr$call_dservicekey
+
|-
−
);*/
+
|w2finish||TIME||Окончание интервала 2 для рабочего дня.  
 +
|-
 +
|s1start||TIME||Начало интервала 1 для субботы.
 +
|-
 +
|s1finish||TIME||Окончание интервала 1 для субботы.
 +
|-
 +
|s2start||TIME||Начало интервала 2 для субботы.
 +
|-
 +
|s2finish||TIME||Окончание интервала 2 для субботы.
 +
|-
 +
|h1start||TIME||Начало интервала 1 для выходного дня.
 +
|-
 +
|h1finish||TIME||Окончание интервала 1 для выходного дня.
 +
|-
 +
|h2start||TIME||Начало интервала 2 для выходного дня.
 +
|-
 +
|h2finish||TIME||Окончание интервала 2 для выходного дня.
 +
|-
 +
|usesaturday||dboolean_notnull||Флаг учитывать субботы или нет.
 +
|}
  
−
CREATE TABLE USR$CALL_RULE
+
Для каждой пары start-finish создается ограничение:
−
(
+
−
  ID    DINTKEY,
+
−
  USR$SERVICEKEY    USR$CALL_DSERVICEKEY NOT NULL,
+
−
  USR$DAY    USR$CALL_DDAY,
+
−
  USR$TOTIME    DTIME,
+
−
  USR$FROMTIME    DTIME
+
−
);
+
  
−
/*Где USR$CALL_DDAY - это тип перечисление
+
  ((start IS NULL AND finish IS NULL) OR (start < finish))
−
* со следующими кодами и значениями:
+
−
* код              значение
+
−
* 1                    Рабочие дни
+
−
* 2                    Выходные дни
+
−
* 3                    Сб
+
−
* 4                    Все дни
+
−
*/
+
  
−
/*
+
==== usr$call_rulelink ====
−
*  Привязка правил к группе.
+
−
* 
+
−
* 
+
−
*/
+
  
 +
Привязка правила к группе. Простая таблица с идентификатором.
  
−
/*CREATE TABLE usr$call_grouprule (
+
{| class="wikitable" border="1"
−
  id                dintkey,
+
!Поле!!Тип поля!!Описание
−
  usr$call_rulekey  usr$call_drulekey,
+
|-
−
  usr$call_groupkey  usr$call_dgroupkey,
+
|usr$groupkey||usr$call_dgroupkey||Ссылка на группу.
−
  usr$fromdate       DATE NOT NULL
+
|-
−
);*/
+
|usr$rulekey||usr$call_drulekey||Ссылка на правило.
 +
|-
 +
|usr$fromdate||DATE NOT NULL||Дата, с которой действует привязка.
 +
|}
  
−
CREATE TABLE USR$CALL_GROUPRULE
+
Уникальный индекс по полям usr$groupkey, usr$rulekey, usr$fromdate.
−
(
+
−
  ID    DINTKEY,
+
−
  USR$RULEKEY    USR$CALL_DRULEKEY NOT NULL,
+
−
  USR$GROUPKEY    USR$CALL_DGROUPKEY NOT NULL
+
−
);
+
  
 +
==== usr$call_sum  ====
  
 +
В этой таблице мы храним итоговую информацию с рассчитанными суммами. Простая таблица с идентификатором.
  
 +
{| class="wikitable" border="1"
 +
!Поле!!Тип поля!!Описание
 +
|-
 +
|usr$date||DATE NOT NULL||Дата начисления.
 +
|-
 +
|usr$employeekey||usr$call_demployeekey||Ссылка на сотрудника.
 +
|-
 +
|usr$total|| ||Сумма брутто.
 +
|-
 +
|usr$covered|| ||Рассчитанная сумма, покрываемая предприятием. На поле наложено ограничение usr$total >= usr$covered.
 +
|-
 +
|usr$corrected|| ||Скорректированная сумма, покрываемая предприятием. На поле наложено ограничение usr$total >= COALESCE(usr$corrected, 0).
 +
|-
 +
|usr$topay|| ||Сумма, к удержанию из зарплаты. Вычисляемое поле, рассчитываемое следующим образом: usr$total - COALESCE(usr$corrected, usr$covered).
 +
|-
 +
|usr$wagedockey|| ||Ссылка на документ Ручной ввод подсистемы Зарплата.
 +
|-
 +
|usr$dockey|| ||Ссылка на документ начисления затрат.
 +
|}
 +
 +
По полям usr$date, usr$employeekey организуется уникальный индекс.
 +
 +
Если поля usr$wagedockey или usr$dockey заполнены, то запрещается редактирование сумм, изменение даты или сотрудника или удаление записи.
 +
 +
==== usr$call_doc  ====
 +
 +
Документ отражения в бухгалтерском учете затрат по услугам связи, которые предприятие берет на себя. Нужен нам для формирования проводок.
 +
 +
{| class="wikitable" border="1"
 +
!Поле!!Тип поля!!Описание
 +
|-
 +
|usr$sum|| || Сумма, которая пойдет на себестоимость.
 +
|}
 +
 +
Документ формируется макросом, после того как все ручные корретировки выполнены, по следующему алгоритму:
 +
 +
# Запрашиваем период и дату документа
 +
# Стартуем транзакцию. Все делаем на этой транзакции
 +
# Подсчитываем сумму по записям из таблицы usr$sum
 +
# Формируем документ. Заносим сумму. Сохраняем.
 +
# Всем записям, которые участвовали в расчете, присваиваем в поле usr$dockey ИД созданного документа.
 +
# Комитим транзакцию, если все прошло успешно.
 +
 +
=== Связь с подсистемой Заработная плата ===
 +
 +
# Создадим в справочнике удержаний новый тип "За телефонную связь"
 +
# На основании импортированных данных будем формировать документ типа "Ручной ввод". В поле описание сформированного документа занесем сразу "Удержание за телефонную связь". Формирование документа целесообразно производить в том же макросе, где идет формирование документа на отнесение затрат на себестоимость. Аналогичным образом присваием полю usr$wagedockey значение идентификатора соответствующей позиции из документа ручной ввод.
 +
# ТП по данному документу настроим таким образом, что она будет проверять тип удержания и формировать нужные проводки.
 +
 +
''Расписать проводки!''
 +
 +
=== Организация пользовательского интерфейса ===
 +
 +
 +
 +
=== Выходные формы ===
 +
 +
# Расшифровка по сотруднику. На вход передается период и сотрудник. Отчет содержит хронологию всех услуг по данному сотруднику из таблицы истории, а также суммарные данные за заданный период из таблицы с начислениями.
 +
# Реестр по сотрудникам. На вход передается период и группа (опционально). Отчет содержит список сотрудников, для каждого указана сумма брутто, сумма, покрытая предприятием, сумма, удержанная из зарплаты.
 +
 +
=== Примеры запросов ===
 +
 +
Сотрудник предприятия по заданному номеру и дате:
 +
 +
<syntaxhighlight lang="SQL">
 +
SELECT FIRST 1
 +
  usr$employeekey
 +
FROM
 +
  usr$call_phonelink
 +
WHERE
 +
  usr$fromdate <= :D AND usr$phonekey = :P
 +
ORDER BY
 +
  usr$fromdate
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
[[Category:Постановка]]
+
[[Category:Постановка-Сделано]]

Текущая версия на 19:28, 4 января 2013

Предприятие заключило с оператором сотовой связи договор. В рамках договора предоставлены сим карты, которые предприятие передает своим сотрудникам. Ежемесячно оператор выставляет суммарный счет для оплаты и листинг к нему, где расписаны все начисления по каждому номеру. На предприятии действует определенная политика каким группам абонентов, какую часть суммы оплачивать за счет предприятия, а какую -- вычитать из начисленной зарплаты. Цель программы -- организовать парсинг листинга и вычисление сумм к удержанию из зарплаты по каждому сотруднику.

  1. Введем понятие услуги -- это звонок на номер стационарной связи, звонок на мобильный номер, звонок на номер предприятия, звонок в роуминге, отправка СМС и т.п. Справочник услуг usr$call_service наполняется в процессе внедрения и зависит от принятой политики предприятия. При изменении справочника необходимо внести соответствующие коррективы в макрос импорта данных.
  2. Будем вести список СИМ карт и отслеживать историю их перемещения между сотрудниками.
  3. Каждый сотрудник принадлежит одной определенной группе абонентов. Отслеживается история перемещения сотрудников между группами.
  4. Правило распределения сумм привязано к группе и услуге. Правила действуют с определенной даты. Последующее правило отменяет предыдущее.
  5. В базе данных хранится вся история звонков (листинг) за 2-3 последних месяца. Должна быть предусмотрена функция удаления данных прошлых периодов.
  6. В базе данных хранятся итоговые значения начислений по всем номерам за все время. Итоговые значения, на основании которых сформированы документы подсистемы Заработная плата или бухгалтерские проводки, блокируются для изменения.

Содержание

[править] Алгоритм обработки листинга

  1. На вход передаем: имя файла листинга, номер контракта, период.
    1. Если в таблице с историей присутствует информация за указанный период, по указанному контракту -- предупреждаем пользователя и останавливаем процесс. Повторный импорт запрещен.
  2. Исходный файл листинга (Excel или XML) обрабатываем последовательно. Полученную информацию помещаем в таблицу истории звонков для дальнейшей обработки.
    1. Если, в процессе импорта, встречен новый телефонный номер, он помещается в справочник.
    2. Если это телефонный номер нашего предприятия, он помечается флагом и в поле номер контракта заносится соответствующая информация.
  3. Проверяем, не появились ли новые номера, которые не связаны ни с одним из работников. Если да, то предупреждаем пользователя. Он должен зайти в окно справочника номеров и присвоить им сотрудников.
  4. По таблице истории звонков, в пределах периода обработки, организуем цикл по работникам предприятия:
    1. Обнуляем переменные:
      1. S1 -- рассчитанная рассчитанная сумма стоимости услуг
      2. S2 -- рассчитанная сумма, оплачиваемая предприятием
    2. Сканируем все записи для данного работника
      1. Для каждой записи смотрим вид услуг, определяем группу, действующую на время записи, и извлекаем правило распределения затрат. Если группа не назначена, то все расходы идут за счёт сотрудника.
      2. В соответствии с правилом увеличиваем переменные S1 и S2
    3. По окончании, проверяем S2 на установленный для группы данного сотрудника лимит и, при необходимости, выполняем корректировку. Если лимит не установлен, то корректировка не выполняется.
    4. Заносим информацию в таблицу с итоговыми значениями
  5. В таблице с итоговыми значениями дополнительно присутствует скорректированная сумма, оплачиваемая предприятием, -- S3. Изначально, она пустая. Ответственное лицо может редактировать эту колонку, таким образом уменьшая или увеличивая сумму, оплачиваемую за работника предприятием.
  6. После ручной корректировки формируется документ типа "Ручной ввод" для подситемы Заработная плата. После того, как для записи из таблицы с итоговыми значениями сформирована позиция документа ручной ввод, она блокируется для изменений и доступна только в режиме просмотра. За блокировку отвечают соответствующие триггеры.

[править] Структуры данных

При описании структуры таблиц мы опускаем стандартные поля.

[править] usr$call_phone

Справочник номеров телефонов. Таблица с идентификатором. Сюда заносятся все номера телефонов, которые фигурируют в листинге. В номере храним только цифры без разделителей, плюсов, скобок и т.п., в формате МРН, где М -- международный код, Р -- региональный код или код оператора, Н -- номер, или номера вида: #12345. Номера вида 802955555555 (если они будут в листинге) преобразуем программно в 375295555555.

Поле Тип поля Описание
usr$phone usr$call_dphone Номер телефона. По этому полю организуем уникальный индекс.
usr$our dboolean_notnull Признак нашего номера.
usr$contract dtext20 Номер контракта или любое кодовое обозначение (например, VELCOM или MTS). Используется для группировки и фильтрации номеров, принадлежащих нашему предприятию в случае, когда контрактов с разными поставщиками услуг связи заключено несколько.

[править] usr$call_service

Справочник услуг. Единица измерения услуги -- ссылка на стандартный справочник GD_VALUE.

Поле Тип поля Описание
usr$name dname Наименование услуги. По этому полю организуем уникальный индекс.
usr$valuekey usr$call_dvaluekey Ссылка на единицу измерения.
usr$onetime dboolean_notnull Признак одноразовая ли услуга

[править] usr$call_history

Сюда попадают данные при импорте из файлов листинга.

Поле Тип поля Описание
usr$builddate DATE NOT NULL Дата построения отчёта
usr$date DATE NOT NULL Дата регистрации услуги.
usr$time TIME NOT NULL Время регистрации услуги.
usr$userkey usr$call_dphonekey NOT NULL Номер абонента, для которого зарегистрирована услуга. Ссылка на справочник номеров.
usr$fromkey usr$call_dphonekey Номер абонента совершающего звонок или отправляющего СМС. Следует обратить внимание, что поля usr$userkey и usr$fromkey будут различаться для входящих звонков. Хотя, внутри РБ входящие звонки обычно не тарифицируются, но они могут иметь стоимость при использовании роуминга.
usr$tokey usr$call_dphonekey Номер абонента принимающего звонок или получающего СМС.
usr$volume usr$call_dvolume Объем оказанной услуги в числовом выражении. Там где это применимо. Например, при пересылке данных, доступе в интернет.
usr$duration TIME Продолжительность связи, там где это применимо.
usr$servicekey usr$call_dservicekey Ссылка на справочник услуг.
usr$cost usr$call_dcost NOT NULL Брутто стоимость, как указано в листинге. 0 для бесплатных или нетарифицируемых услуг.

По полям usr$date, usr$time, usr$userkey, usr$servicekey создается уникальный индекс. Это обезопасит нас от повторного импорта данных.

[править] usr$call_phonelink

Простая таблица с идентификатором. Здесь фиксируется история перехода СИМ карт из рук в руки. Если карточка никому не назначена, то либо для нее вообще нет записи в этой таблице (исходное состояние), либо добавляется запись, где usr$employeekey IS NULL.

Поле Тип поля Описание
usr$phonekey usr$call_dphonekey NOT NULL Ссылка на справочник номеров телефонов.
usr$employeekey usr$call_employeekey Ссылка на справочник контактов.
usr$fromdate DATE NOT NULL Дата, с которой карта передана сотруднику.

Уникальный индекс по полям usr$phonekey, usr$employeekey, usr$fromdate.

[править] usr$call_group

Справочник групп абонентов. Простая таблица с идентификатором.

Поле Тип поля Описание
usr$name dname Наименование группы. Организуем уникальный индекс по этому полю.
usr$comment dtext1024 Комментарий.

[править] usr$call_grouplink

История привязки номера к группе. Простая таблица с идентификатором.

Поле Тип поля Описание
usr$groupkey usr$call_dgroupkey Ссылка на группу.
usr$phonekey usr$call_dphonekey Ссылка на номер.
usr$fromdate DATE NOT NULL Дата, с которой действует привязка.

Уникальный индекс по полям usr$groupkey, usr$phonekey, usr$fromdate.

[править] usr$call_rule

Правила распределения затрат. Простая таблица с идентификатором. Правило действует следующим образом:

  1. Мы разделяем дни на рабочие, праздничные и субботы. Праздничные дни это воскресенье + дни из справочника государственных праздников.
  2. Для каждого типа дней введено два интервала.
  3. Если время регистрации услуги попадает в один из указанных интервалов, то она оплачивается предприятием. Если не попадает, то -- вычитается из зарплаты. Следовательно, если указан интервал 00:00:00-24:00:00, то услуга будет оплачиваться предприятием полностью. Если ни один из интервалов не указан, то услуга полностью оплачивается работником. Незаданный интервал -- это пара NULL-NULL. Так как есть услуги, которые всегда оплачиваются предприятием и есть услуги, которые никогда не оплачиваются, то, для упрощения ввода, на уровне диалогового окна надо будет предусмотреть селектор: всегда оплачивается, никогда не оплачивается и оплачивается по расписанию.
Поле Тип поля Описание
usr$servicekey usr$call_dservicekey Ссылка на услугу.
usr$name dname Наименование правила. Уникальный индекс по этому полю.
w1start TIME Начало интервала 1 для рабочего дня.
w1finish TIME Окончание интервала 1 для рабочего дня.
w2start TIME Начало интервала 2 для рабочего дня.
w2finish TIME Окончание интервала 2 для рабочего дня.
s1start TIME Начало интервала 1 для субботы.
s1finish TIME Окончание интервала 1 для субботы.
s2start TIME Начало интервала 2 для субботы.
s2finish TIME Окончание интервала 2 для субботы.
h1start TIME Начало интервала 1 для выходного дня.
h1finish TIME Окончание интервала 1 для выходного дня.
h2start TIME Начало интервала 2 для выходного дня.
h2finish TIME Окончание интервала 2 для выходного дня.
usesaturday dboolean_notnull Флаг учитывать субботы или нет.

Для каждой пары start-finish создается ограничение: 

 ((start IS NULL AND finish IS NULL) OR (start < finish))

[править] usr$call_rulelink

Привязка правила к группе. Простая таблица с идентификатором.

Поле Тип поля Описание
usr$groupkey usr$call_dgroupkey Ссылка на группу.
usr$rulekey usr$call_drulekey Ссылка на правило.
usr$fromdate DATE NOT NULL Дата, с которой действует привязка.

Уникальный индекс по полям usr$groupkey, usr$rulekey, usr$fromdate.

[править] usr$call_sum

В этой таблице мы храним итоговую информацию с рассчитанными суммами. Простая таблица с идентификатором.

Поле Тип поля Описание
usr$date DATE NOT NULL Дата начисления.
usr$employeekey usr$call_demployeekey Ссылка на сотрудника.
usr$total Сумма брутто.
usr$covered Рассчитанная сумма, покрываемая предприятием. На поле наложено ограничение usr$total >= usr$covered.
usr$corrected Скорректированная сумма, покрываемая предприятием. На поле наложено ограничение usr$total >= COALESCE(usr$corrected, 0).
usr$topay Сумма, к удержанию из зарплаты. Вычисляемое поле, рассчитываемое следующим образом: usr$total - COALESCE(usr$corrected, usr$covered).
usr$wagedockey Ссылка на документ Ручной ввод подсистемы Зарплата.
usr$dockey Ссылка на документ начисления затрат.

По полям usr$date, usr$employeekey организуется уникальный индекс.

Если поля usr$wagedockey или usr$dockey заполнены, то запрещается редактирование сумм, изменение даты или сотрудника или удаление записи.

[править] usr$call_doc

Документ отражения в бухгалтерском учете затрат по услугам связи, которые предприятие берет на себя. Нужен нам для формирования проводок.

Поле Тип поля Описание
usr$sum Сумма, которая пойдет на себестоимость.

Документ формируется макросом, после того как все ручные корретировки выполнены, по следующему алгоритму:

  1. Запрашиваем период и дату документа
  2. Стартуем транзакцию. Все делаем на этой транзакции
  3. Подсчитываем сумму по записям из таблицы usr$sum
  4. Формируем документ. Заносим сумму. Сохраняем.
  5. Всем записям, которые участвовали в расчете, присваиваем в поле usr$dockey ИД созданного документа.
  6. Комитим транзакцию, если все прошло успешно.

[править] Связь с подсистемой Заработная плата

  1. Создадим в справочнике удержаний новый тип "За телефонную связь"
  2. На основании импортированных данных будем формировать документ типа "Ручной ввод". В поле описание сформированного документа занесем сразу "Удержание за телефонную связь". Формирование документа целесообразно производить в том же макросе, где идет формирование документа на отнесение затрат на себестоимость. Аналогичным образом присваием полю usr$wagedockey значение идентификатора соответствующей позиции из документа ручной ввод.
  3. ТП по данному документу настроим таким образом, что она будет проверять тип удержания и формировать нужные проводки.

Расписать проводки!

[править] Организация пользовательского интерфейса

[править] Выходные формы

  1. Расшифровка по сотруднику. На вход передается период и сотрудник. Отчет содержит хронологию всех услуг по данному сотруднику из таблицы истории, а также суммарные данные за заданный период из таблицы с начислениями.
  2. Реестр по сотрудникам. На вход передается период и группа (опционально). Отчет содержит список сотрудников, для каждого указана сумма брутто, сумма, покрытая предприятием, сумма, удержанная из зарплаты.

[править] Примеры запросов

Сотрудник предприятия по заданному номеру и дате: 

SELECT FIRST 1
  usr$employeekey
FROM
  usr$call_phonelink
WHERE
  usr$fromdate <= :D AND usr$phonekey = :P
ORDER BY
  usr$fromdate
Источник — «https://gsbelarus.com:80/gs/wiki/index.php?title=%D0%A3%D1%87%D0%B5%D1%82_%D1%83%D1%81%D0%BB%D1%83%D0%B3_%D1%81%D0%BE%D1%82%D0%BE%D0%B2%D0%BE%D0%B9_%D1%81%D0%B2%D1%8F%D0%B7%D0%B8_(%D0%BF%D0%BE%D1%81%D1%82%D0%B0%D0%BD%D0%BE%D0%B2%D0%BA%D0%B0)&oldid=5545»
Персональные инструменты
Пространства имён

Варианты
Действия
Навигация
Инструменты