Учет услуг сотовой связи (постановка)
Предприятие заключило с оператором сотовой связи договор. В рамках договора предоставлены сим карты, которые предприятие передает своим сотрудникам. Ежемесячно оператор выставляет суммарный счет для оплаты и листинг к нему, где расписаны все начисления по каждому номеру. На предприятии действует определенная политика каким группам абонентов, какую часть суммы оплачивать за счет предприятия, а какую -- вычитать из начисленной зарплаты. Цель программы -- организовать парсинг листинга и вычисление сумм к удержанию из зарплаты по каждому сотруднику.
- Введем понятие услуги -- это звонок на номер стационарной связи, звонок на мобильный номер, звонок на номер предприятия, звонок в роуминге, отправка СМС и т.п. Справочник услуг usr$call_service наполняется в процессе внедрения и зависит от принятой политики предприятия. При изменении справочника необходимо внести соответствующие коррективы в макрос импорта данных.
- Будем вести список СИМ карт и отслеживать историю их перемещения между сотрудниками.
- Каждый сотрудник принадлежит одной определенной группе абонентов. Отслеживается история перемещения сотрудников между группами.
- Правило распределения сумм привязано к группе и услуге. Правила действуют с определенной даты. Последующее правило отменяет предыдущее.
- В базе данных хранится вся история звонков (листинг) за 2-3 последних месяца. Должна быть предусмотрена функция удаления данных прошлых периодов.
- В базе данных хранятся итоговые значения начислений по всем номерам за все время. Итоговые значения, на основании которых сформированы документы подсистемы Заработная плата или бухгалтерские проводки, блокируются для изменения.
Содержание |
Алгоритм обработки листинга
- На вход передаем: имя файла листинга, номер контракта, период.
- Если в таблице с историей присутствует информация за указанный период, по указанному контракту -- предупреждаем пользователя и останавливаем процесс. Повторный импорт запрещен.
- Исходный файл листинга (Excel или XML) обрабатываем последовательно. Полученную информацию помещаем в таблицу истории звонков для дальнейшей обработки.
- Если, в процессе импорта, встречен новый телефонный номер, он помещается в справочник.
- Если это телефонный номер нашего предприятия, он помечается флагом и в поле номер контракта заносится соответствующая информация.
- Проверяем, не появились ли новые номера, которые не связаны ни с одним из работников. Если да, то предупреждаем пользователя. Он должен зайти в окно справочника номеров и присвоить им сотрудников.
- По таблице истории звонков, в пределах периода обработки, организуем цикл по работникам предприятия:
- Обнуляем переменные:
- S1 -- рассчитанная рассчитанная сумма стоимости услуг
- S2 -- рассчитанная сумма, оплачиваемая предприятием
- Сканируем все записи для данного работника
- Для каждой записи смотрим вид услуг, определяем группу, действующую на время записи, и извлекаем правило распределения затрат. Если группа не назначена, то все расходы идут за счёт сотрудника.
- В соответствии с правилом увеличиваем переменные S1 и S2
- По окончании, проверяем S2 на установленный для группы данного сотрудника лимит и, при необходимости, выполняем корректировку. Если лимит не установлен, то корректировка не выполняется.
- Заносим информацию в таблицу с итоговыми значениями
- Обнуляем переменные:
- В таблице с итоговыми значениями дополнительно присутствует скорректированная сумма, оплачиваемая предприятием, -- S3. Изначально, она пустая. Ответственное лицо может редактировать эту колонку, таким образом уменьшая или увеличивая сумму, оплачиваемую за работника предприятием.
- После ручной корректировки формируется документ типа "Ручной ввод" для подситемы Заработная плата. После того, как для записи из таблицы с итоговыми значениями сформирована позиция документа ручной ввод, она блокируется для изменений и доступна только в режиме просмотра. За блокировку отвечают соответствующие триггеры.
Структуры данных
При описании структуры таблиц мы опускаем стандартные поля.
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
Правила распределения затрат. Простая таблица с идентификатором. Правило действует следующим образом:
- Мы разделяем дни на рабочие, праздничные и субботы. Праздничные дни это воскресенье + дни из справочника государственных праздников.
- Для каждого типа дней введено два интервала.
- Если время регистрации услуги попадает в один из указанных интервалов, то она оплачивается предприятием. Если не попадает, то -- вычитается из зарплаты. Следовательно, если указан интервал 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 | Сумма, которая пойдет на себестоимость. |
Документ формируется макросом, после того как все ручные корретировки выполнены, по следующему алгоритму:
- Запрашиваем период и дату документа
- Стартуем транзакцию. Все делаем на этой транзакции
- Подсчитываем сумму по записям из таблицы usr$sum
- Формируем документ. Заносим сумму. Сохраняем.
- Всем записям, которые участвовали в расчете, присваиваем в поле usr$dockey ИД созданного документа.
- Комитим транзакцию, если все прошло успешно.
Связь с подсистемой Заработная плата
- Создадим в справочнике удержаний новый тип "За телефонную связь"
- На основании импортированных данных будем формировать документ типа "Ручной ввод". В поле описание сформированного документа занесем сразу "Удержание за телефонную связь". Формирование документа целесообразно производить в том же макросе, где идет формирование документа на отнесение затрат на себестоимость. Аналогичным образом присваием полю usr$wagedockey значение идентификатора соответствующей позиции из документа ручной ввод.
- ТП по данному документу настроим таким образом, что она будет проверять тип удержания и формировать нужные проводки.
Расписать проводки!
Организация пользовательского интерфейса
Выходные формы
- Расшифровка по сотруднику. На вход передается период и сотрудник. Отчет содержит хронологию всех услуг по данному сотруднику из таблицы истории, а также суммарные данные за заданный период из таблицы с начислениями.
- Реестр по сотрудникам. На вход передается период и группа (опционально). Отчет содержит список сотрудников, для каждого указана сумма брутто, сумма, покрытая предприятием, сумма, удержанная из зарплаты.
Примеры запросов
Сотрудник предприятия по заданному номеру и дате:
SELECT FIRST 1 usr$employeekey FROM usr$call_phonelink WHERE usr$fromdate <= :D AND usr$phonekey = :P ORDER BY usr$fromdate
