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

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

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

Алгоритм обработки листинга

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

Структуры данных

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

usr$call_phone

Справочник номеров телефонов. Таблица с идентификатором. Сюда заносятся все номера телефонов, которые фигурируют в листинге. В номере храним только цифры без разделителей, плюсов, скобок и т.п., в формате МРН, где М -- международный код, Р -- региональный код или код оператора, Н -- номер. Номера вида 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$call_history

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

Поле	Тип поля	Описание
usr$date	DATE NOT NULL	Дата регистрации услуги.
usr$time	TIME NOT NULL	Время регистрации услуги.
usr$user	usr$call_dphonekey NOT NULL	Номер абонента, для которого зарегистрирована услуга. Ссылка на справочник номеров.
usr$from	usr$call_dphonekey	Номер абонента совершающего звонок или отправляющего СМС.
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	Брутто стоимость, как указано в листинге. 0 для бесплатных или нетарифицируемых услуг.

По полям usr$date, usr$time, usr$user, 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	Дата, с которой действует привязка.

/* 
 *  Правила. 
 *  
 *  
 *  
 *  
 *  
 */
/*Исправлено 17.08.2011*/
/*CREATE TABLE usr$call_rule (
  id                 dintkey,
  usr$name           dname,
  usr$servicekey     usr$call_dservicekey
);*/
 
CREATE TABLE USR$CALL_RULE
(
  ID    DINTKEY,
  USR$SERVICEKEY    USR$CALL_DSERVICEKEY NOT NULL,
  USR$DAY    USR$CALL_DDAY,
  USR$TOTIME    DTIME,
  USR$FROMTIME    DTIME
);
 
/*Где USR$CALL_DDAY - это тип перечисление
 * со следующими кодами и значениями:
 * код               значение
 * 1                    Рабочие дни
 * 2                    Выходные дни
 * 3                    Сб
 * 4                    Все дни
*/
 
/* 
 *  Привязка правил к группе. 
 *  
 *  
 */
 
 
/*CREATE TABLE usr$call_grouprule (
  id                 dintkey,
  usr$call_rulekey   usr$call_drulekey,
  usr$call_groupkey  usr$call_dgroupkey,
  usr$fromdate       DATE NOT NULL
);*/
 
CREATE TABLE USR$CALL_GROUPRULE
(
  ID    DINTKEY,
  USR$RULEKEY    USR$CALL_DRULEKEY NOT NULL,
  USR$GROUPKEY    USR$CALL_DGROUPKEY NOT NULL
);

Связь с подсистемами Бухгалтерского учета и Заработной платы

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

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

Выходные формы

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

Примеры запросов

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

SELECT FIRST 1
  usr$employeekey
FROM
  usr$call_phonelink
WHERE
  usr$fromdate <= :D AND usr$phonekey = :P
ORDER BY
  usr$fromdate