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

Версия 17:57, 24 августа 2011

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

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

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

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

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

/*
 * Домены
 *
 */
 
CREATE DOMAIN usr$call_dphone 
  VARCHAR(14)
  CHECK(VALUE SIMILAR TO '[0-9]{3,14}');
 
-- ссылка на GD_VALUE
usr$call_dvaluekey
 
-- обязательная ссылка на услугу
usr$call_dservicekey 
 
/* 
 *  Все номера телефонов, которые появляются в листинге.
 *  Храним только цифры без разделителей, плюсов, скобок и т.п.
 *  В формате международный код внутренний код номер.
 *  Номера вида 802955555555 преобразуем программно в 375295555555.
 *  Флагом помечаем номера нашего предприятия. 
 */
 
CREATE TABLE usr$call_phone (
  id             dintkey,
  usr$phone      usr$call_dphone,
  usr$our        dboolean_notnull
);
 
/* 
 *  Список услуг: телефонный звонок, СМС, звонок в роуминге,
 *  звонок на стационарную сеть и т.п. 
 *  Единица измерения услуги -- ссылка на стандартный 
 *  справочник GD_VALUE.
 */
 
CREATE TABLE usr$call_service (
  id             dintkey,
  usr$name       dname,
  usr$valuekey   usr$call_dvaluekey
);
 
/* 
 *  Хронология звонков, услуг и т.п.
 *  Указывается объем и/или продолжительность. 
 */
 
CREATE TABLE usr$call_history (
  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 (
  id              dintkey,
  usr$phonekey    usr$call_dphonekey NOT NULL,
  usr$employeekey usr$call_employeekey,
  usr$fromdate    DATE NOT NULL
);
 
/* 
 *  Список групп абонентов.
 *  Правила вычета денег привязаны к группам.
 *  
 */
 
CREATE TABLE usr$call_group (
  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*/
  /*usr$groupkey    dintkey,*/
  usr$groupkey    usr$call_dgroupkey,
  /*usr$phonekey    dintkey,*/
  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. ТП по данному документу настроим таким образом, что она будет проверять тип удержания и формировать нужные проводки.

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

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

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

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