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

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

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

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

Исходный файл листинга (Excel или XML) мы обрабатываем последовательно, анализируя каждую строку и извлекая следующую информацию:

• Дата и время
• Услуга
• Абонент
• Стоимость
• Корреспондирующий номер

Данную информацию помещаем в базу данных для дальнейшей обработки.

Расчет сумм выполняем по информации из базы данных по следующему алгоритму:

1. Организуем цикл по работникам предприятия, участвующим в листинге
   1. Определяем группу работника.
   2. Обнуляем переменные:
      1. S1 -- рассчитанная рассчитанная сумма стоимости услуг
      2. S2 -- рассчитанная сумма, оплачиваемая предприятием
   3. Сканируем все записи для данного работника
      1. Для каждой записи смотрим вид услуг и извлекаем правило распределения затрат
      2. В соответствии с правилом увеличиваем переменные S1 и S2
   4. По окончании, проверяем S2 на установленный для данной группы лимит и, при необходимости, выполняем корректировку
   5. Заносим информацию в таблицу с итоговыми значениями

В таблице с итоговыми значениями дополнительно присутствует скорректированная сумма, оплачиваемая предприятием, -- S3. Изначально, она пустая. Ответственное лицо может редактировать эту колонку, таким образом уменьшая или увеличивая сумму, оплачиваемую за работника предприятием.

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

/*
 * Домены
 *
 */
 
CREATE DOMAIN usr$call_dphone 
  VARCHAR(12)
  CHECK(VALUE SIMILAR TO '[0-9]{5,12}');
 
-- ссылка на GD_VALUE
usr$call_dvaluekey
 
-- обязательная ссылка на услугу
usr$call_dservicekey 
 
/* 
 *  Все номера телефонов, которые появляются в листинге.
 *  Храним только цифры без разделителей, плюсов, скобок и т.п.
 *  Флагом помечаем номера нашего предприятия. 
 */
 
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
);