Примеры технических отчётов

В данной статье приведены примеры технических отчётов, которые наши сотрудники делают при решении задач - часто это короткие отчеты для каких-то специфичных кейсов.

{toc}

h2. Примеры

h3. Просмотр текущих прав доступа к типам адресных единиц для задачи SUP-632439

Выводит настроенные права доступа к справочнику "Типы адресных единиц"
{code}
select
agp.id,
ag.name,
ap.codename
from
auth_permission ap
left join
AUTH_GROUP_PERMISSIONS as agp
on agp.PERMISSION_ID = ap.id
left join
AUTH_GROUP as ag
on ag.id = agp.group_id
where
ap.codename like 'modelperm_HomeTypeName%' order by agp.id
{code}

h3. Пояснение структуры хранения данных в справочнике адресов по задаче SUP-594716

# Основные данные (страна, улица, номер дома) хранятся в таблице HOMES
# Основная таблица связана с таблицей типы адресных единицы (это строение, дом, микрорайон, улица, область или край и тд), название таблицы - HOME_TYPE_NAME
# Еще есть служебное поле с типом записи справочника дома - нужно для верного построения дерева домов, таблица называется HOME_TYPES

Ниже сам запрос и пример полученных данных

{code:title=Запрос}
select first 1
HOMES.ID "ID дома",
HOMES.ZIP_CODE "Индекс",
HOME_TYPE.NAME "Служебный тип",
HOMES.COUNTRY "Страна",
REGION_TYPE."FULL" "Тип региона",
HOMES.REGION "Регион",
CITY_TYPE."FULL" "Тип нас.пункта",
HOMES.CITY "Нас. пункт",
SETTLEMENT_TYPE."FULL" "Тип поселения",
HOMES.SETTLEMENT "Поселение",
STREET_TYPE."FULL" "Тип улицы",
HOMES.STREET "Улица",
S_NUMBER_TYPE."FULL" "Тип строения",
HOMES.S_NUMBER "Номер строения",
KLADR_ID "ID в ФИАС",
UNRESTRICTED_VALUE "Адрес строкой"
from HOMES
left join HOME_TYPES as HOME_TYPE on HOMES.HOME_TYPES_ID=HOME_TYPE.ID
left join HOME_TYPE_NAME as CITY_TYPE on HOMES.CITY_TYPE_ID=CITY_TYPE.ID
left join HOME_TYPE_NAME as DISTRICT_TYPE on HOMES.DISTRICT_TYPE_ID=DISTRICT_TYPE.ID
left join HOME_TYPE_NAME as REGION_TYPE on HOMES.REGION_TYPE_ID=REGION_TYPE.ID
left join HOME_TYPE_NAME as SETTLEMENT_TYPE on HOMES.SETTLEMENT_TYPE_ID=SETTLEMENT_TYPE.ID
left join HOME_TYPE_NAME as STREET_TYPE on HOMES.STREET_TYPE_ID=STREET_TYPE.ID
left join HOME_TYPE_NAME as S_NUMBER_TYPE on HOMES.S_NUMBER_TYPE_ID=S_NUMBER_TYPE.ID
left join HOME_TYPE_NAME as S_LITER_TYPE on HOMES.S_LITER_TYPE_ID=S_LITER_TYPE.ID
order by HOMES.ID desc
{code}

|| Пример данных ||
| ID дома | 2409 |
| Индекс | 665831 |
| Служебный тип | Строение |
| Страна | Россия |
| Тип региона | область |
| Регион | Иркутская |
| Тип нас.пункта | город |
| Нас. пункт | Ангарск |
| Тип поселения | микрорайон |
| Поселение | Байкальск |
| Тип улицы | улица |
| Улица | Торговая |
| Тип строения | дом |
| Номер строения | 10 |
| ID в ФИАС | 3800000400010560005 |
| Адрес строкой | 665831, Иркутская обл, г Ангарск, мкр Байкальск, ул Торговая, д 10 |

h3. Дубли реквизитов, задача SUP-645914

Отчёт находит абонентов с дублями [реквизитов|CarbonBilling:Реквизиты].

Абоненту можно добавить несколько экземпляров одного и того же реквизита (например, если нужно добавить несколько телефонов или файлов). Но в некоторых случаях это может создавать проблемы - например, при интеграции с СОРМ.

Отчёт покажет какие реквизиты у каких абонентов задублированы.

Последнее текстовое поле нужно поправить "под себя" - укажите там IP-адрес Вашего биллинга вместо 10.0.0.1

{code}
select
*
from
(select
count(*) cnt,
ua.name attr_name,
av.attribute_id attr_id,
av.abonent_id abon_id,
a.name abon_name,
'http://10.0.0.1:8082/admin/Abonents/' || av.abonent_id || '/?tab=tab1147' url_to_abon_attrs
from
ATTRIBUTE_VALUES av
join
USER_ATTRIBUTES ua
on av.attribute_id=ua.attribute_id
join
abonents a
on
av.abonent_id=a.id
group by
2,3,4,5,6)
where
cnt>1
order by
4,5,1 desc
{code}

h3. Учётные записи с логином равным номеру договора другого абонента, задача SUP-650800

При создании абонента, создаётся и учётная запись с логином соответствующим номеру договора, поэтому маловероятно что такой логин будет использоваться у како-то другого абонента.

Тем неменее, такую ситуацию можно воспроизвести при интеграции IPTV, когда отдельная услуга создаёт учетную запись, в результате "второму" абоненту не будет добавлена новая учетная запись, а [система мониторинга|http://docs.carbonsoft.ru/51019784#Системамониторинга-checkerrorworker.sh] создаст автозаявку о проблеме.

Чтобы проблема не возникала, лучше не создавать ситуацию когда номер договора соответствует логину учетной записи другого абонента. Отчёт позволит найти таких абонентов.

{code:title=Отчёт покажет проблемных абонентов}select
u.abonent_id,
u.login,
a.id,
a.contract_number
from
users u
join
abonents a
on u.login=a.contract_number
and u.abonent_id<>a.id
where
a.is_folder=0{code}
{code:title=Отчёт покажет количество проблемных абонентов}
select
count(*)
from users u
join abonents a on u.login=a.contract_number and u.abonent_id<>a.id
where a.is_folder=0{code}

h3. Отчет со списком абонентов, подключивших оповещение через Telegram. SUP-651766
{code:title=Номер договора, ФИО, ID}select
A.CONTRACT_NUMBER as "Номер договора",
A.Name as "ФИО",
TLG.ATTRIBUTE_VALUE as "ID Telegram"
from ABONENTS A
left join ATTRIBUTE_VALUES TLG on TLG.ABONENT_ID=A.ID and TLG.ATTRIBUTE_ID=1014
where
A.is_folder=0
and A.deleted=0
and coalesce(TLG.ATTRIBUTE_VALUE,'') != ''{code}
В данном примере явно указан ATTRIBUTE_ID=1014, в вашем биллинге идентификатор может быть совсем иным. Чтобы проверить это, необходимо обратиться к справочнику ["Реквизиты"|https://docs.carbonsoft.ru/pages/viewpage.action?pageId=48693342] По столбцу *№* вы сможете определить нужный id для подстановки в отчет.

h3. Количество точек подключения по адресам SUP-633846
Отчёт покажет только адреса, на которых есть хотя бы одна точка подключения.
{code}
select
count(*),
h.street "Улица",
h.s_number "Номер дома",
h.id "ID дома"
from
connection_points cp
join
homes h
on cp.home_id=h.id
group by
2,3,4
order by
3,4
{code}

h3. Точки подключения по выбранному адресу SUP-633846
Отчёт покажет только точки подключения, в которых выбран коммутатор и порт
{code}
select
cp.id "ID точки п.",
case
when coalesce(sp.name,'')<>'' then sp.name ||', ' || uf_ip2string(sw.ip)
when coalesce(sp.num,-100)<>-100 then sp.num ||', ' || uf_ip2string(sw.ip)
else ''
end "Коммутатор/порт",
cp.socket "Розетка",
'(ID ' || h.id ||'), ' || h.street ||', ' || h.s_number ||', ' || cp.flat "Адрес",
case
when cp.abonent_id is not null then (select a.id || ' ' || a.name from abonents a where a.id=cp.abonent_id)
when cp.user_id is not null then (select u.id || ' ' || u.login from users u where u.id=cp.user_id)
else 'Свободно'
end "Кем занято"
from
connection_points cp
join
homes h
on cp.home_id=h.id
join
switch_ports sp
on cp.switch_p_id=sp.id
join
switch sw
on sp.switch_id=sw.id
where
cp.home_id = ':Дом|select[Homes,home_types_id=6]$'
order by
h.id,cp.flat
{code}

h3. Проверка дублей портов SUP-633846
Порты заведённые с одним наименованием более одного раза
{code}
select
*
from (select
count(*) cnt,
sp.switch_id,
sp.name
from
switch_ports sp
where
coalesce(name,'')<>''
group by
sp.switch_id,
sp.name
)
where
cnt>1
order by
1 desc
{code}

h3. Транки операторов SUP-246480
Отчёт покажет настройку транков в услугах операторов для учёта [межоператороского трафика|CarbonBilling:Перерасчет межоператорского трафика].
{code}select distinct
a.name "Оператор",
coalesce(u.name,'Нет услуги') "Услуга",
coalesce(vt.name,'') "Тип трафика",
iif(uu.id is not null,voip_operator_match_format,'оттсутствуют') "Транки"
from
abonents a
join users_usluga uu
on a.id=uu.abonent_id
and voip_operator_match_format<>''
left join usluga u
on uu.usluga_id=u.id
left join v_type vt
on u.voip_v_type_id=vt.id
where
a.category_id=2
and a.id>2

order by :Сортировка по:|choices[1^]Наименованию^[3^]Транкам]${code}

h3. Услуги, привязанные к NAS SUP-675790
Отчёт поможет проверить корректность настройки интеграции [Flussonic Watcher v1 (архивная)] и [IPTV|CarbonBilling:Интеграция сервисов интернет-телевидения], показав услуги привязанные к выбранному NAS. Так Вы сможете проверить, правильно ли указаны параметры активации/деактивации услуги, все ли они привязаны в нужному NAS-серверу.
{code}
select
u.id,
u.name "Название",
u.activate_string,
u.deactivate_string,
ut.name "Тип услуги",
uat.name "Метод списания",
case
when coalesce(post_pay,0)=1 then 'Постоплата'
else 'Предоплата'
end "Тип списания",
case
when coalesce(u.deleted,0)=1 then 'Удалена'
else '-'
end "Услуга удалена?"
from
usluga u
join
usluga_type ut
on u.system_type=ut.id
join
usluga_abon_type uat
on u.usluga_abon_type_id=uat.id
where
nas_id = :NAS|select[NAS]$
and coalesce(u.deleted,0) :Показывать удалённы услуги?|choices[=0^]Нет^[<>2^]Да]$
order by
u.activate_string
{code}

h3. Количество абонентов с услугами привязанными к NAS-серверам SUP-675790

Отчёт пригодится для учета абонентов интернет-телевидения и видеонаблюдения - он покажет количество абонентов с услугами, в которых указан какой-либо NAS, что используется в схемах интеграции с этими сервисами.

{code}
select
count(distinct uu.abonent_id) "Кол-во абонентов",
n.name "NAS"
from
users_usluga uu
join
usluga u
on uu.usluga_id=u.id
join
nas n
on u.nas_id=n.id
where
uu.abonent_id is not null
and uu.deleted=0
and u.deleted=0
group by
n.name
order by
1
{code}

h3. Flussonic Watcher v2, учтётные записи, камеры и услуги по камерам для схемы интеграции SUP-675790

Отчёт покажет учетную запись Flussonic с ID пользователя в Watcher. Если у абонента есть камеры, то в отчёте будет её наименование и привязанные услуги - пресет и глубина DVR записи с детекцией движения.
u.nas_id=1184 во втором подапросе это ID NAS Flussonic со 2 версией схемы.


{code}
--УСЛУГИ И КАМЕРЫ
select
a.id "ID абонента",
'' "Номер договора",
'' "ФИО",
'' "ID учетной записи",
'' "Логин",
'' "Пароль",
'' "ID Watcher",
coalesce(ususl.serial_no,''),
coalesce(ususl.usl_list,'')
from
abonents a
join
(
select
e.serial_no serial_no,
e.name e_name,
e.id e_id,
uu.abonent_id abonent_id,
list('Услуга ' || coalesce(usl.name,'') || ' (activate_string: ' || coalesce(usl.activate_string,'') || ')') usl_list
from
users_usluga uu
join
usluga usl
on uu.usluga_id=usl.id
join
nas n
on usl.nas_id=n.id
and n.nas_scheme_id=219001 --схема Flussonic
and n.nas_scheme_ver=2 --версия, в которой реализован синхронизатор
join
equipment e
on uu.equipment_id=e.id
where
uu.deleted=0
and coalesce(uu.equipment_id,'')<>''
group by
e.serial_no,
e.name,
e.id,
uu.abonent_id
) ususl
on ususl.abonent_id=a.id
where
a.id>0

union

--УЧЕТНЫЕ ЗАПИСИ
select
a.id "ID абонента",
a.contract_number "Номер договора",
a.name "ФИО",
cast(u.id as varchar(32)) "",
u.login,
u.gen_pwd,
coalesce(u.ext_id,''),
'',
''
from
abonents a
join
users u
on u.abonent_id=a.id
where
a.id>0
and u.nas_id=1184

--1 - СОРТИРУЕМ ПО ID АБОНЕНТА ЧТОБЫ УЧЕТКА И УСЛУГИ С КАМЕРАМИ БЫЛИ РЯДОМ
--4 desc - СОРТИРУЕМ ТАК, ЧТОБЫ СВЕРХУ БЫЛА УЧЕТКА, ПОД НЕЙ УСЛУГИ
order by
1,4 desc
{code}

h3. Flussonic Watcher v2, услуги сервиса, которые можно подключить абонентам, с контролем ошибок SUP-772439

{code}
select
u.id,
u.name "Название",
u.activate_string,
u.deactivate_string,
ut.name "Тип услуги",
uat.name "Метод списания",
case
when coalesce(u.post_pay,0)=1 then 'Постоплата'
else 'Предоплата'
end "Тип списания",
case
when coalesce(u.deleted,0)=1 then 'Удалена'
else '-'
end "Услуга удалена?",
case
when coalesce(list(duplicate.id),'-') <> '-' then list('ID=' || cast(duplicate.id as varchar(32)) || ' ' || duplicate.name)
else '-'
end "Дубль?",
case
when u.create_login=1 and u.require_assign_to_user=1 then '1=Создавать учетную запись при подключении, 2=Для услуги необходима привязка к учетной записи'
when u.create_login=1 and u.require_assign_to_user=0 then 'Создавать учетную запись при подключении'
when u.create_login=0 and u.require_assign_to_user=1 then 'Для услуги необходима привязка к учетной записи'
else '-'
end "Отключите опции!",
case
when coalesce(list(tuu.tarif_id),'-') <> '-' and coalesce(u.deleted,0)=0 then 'Удалите услугу из этих тарифов: ' || list('ID=' || tuu.tarif_id || ' ' || t.name)
else '-'
end "УберитеИзТарифoв!"
from
usluga u
join
nas n
on u.nas_id=n.id
join
usluga_type ut
on u.system_type=ut.id
join
usluga_abon_type uat
on u.usluga_abon_type_id=uat.id
left join
tarif_users_usluga tuu
on tuu.usluga_id = u.id
left join
tarif t
on tuu.tarif_id = t.id
left join
usluga duplicate
on (u.activate_string = duplicate.activate_string
or u.deactivate_string = duplicate.deactivate_string)
and u.id<>duplicate.id
and u.nas_id=duplicate.nas_id
and coalesce(duplicate.deleted,0)=0
where
n.nas_scheme_id = 219001
and coalesce(u.deleted,0) :Показывать удалённы услуги?|choices[=0^]Нет^[<>2^]Да]$
group by
u.id, u.name, u.activate_string, u.deactivate_string, ut.name, uat.name, u.post_pay, u.deleted, u.create_login, u.require_assign_to_user
order by
u.activate_string
{code}

h3. Дубли по Названию/ФИО, SUP‑765404

Отчёт выведет абонентов с повторяющимися наименованием или ФИО.
{tip}Замените IP 10.0.0.2 на IP/домен Вашего сервера, чтобы удобней копировать ссылки.{tip}

{code}
select distinct
a.name,
case
when coalesce(a.deleted,0) = 1 then 'Удалён'
else ''
end,
'http://10.0.0.2:8082/admin/Abonents/' || a.id
from
abonents a
join
abonents a_double
on a.name = a_double.name
and a.id <> a_double.id
where
1=1
and a.name <> 'Новый абонент'
order by
a.name
{code}

h3. Изменения даты договора, отчёт по аудиту, SUP-812311

Отчёт поможет найти абонентов, у которых менялась дата в поле "*Дата договора*".
В выборку попадёт только аудит, находящийся в основной базе: по-умолчанию, [за последний месяц|CarbonBilling:Настройка периода хранения исторических данных в базе].
Самая левая колонка - логин администратора, который менял дату.

{code}
select
admins.username,
audit.*
from
audit_operations audit
left join
auth_user admins on
audit.admin_id = admins.id

where
table_name = 'abonents'
and descr like '%Изменения%Дата договора%Ссылки%'
order by
op_time desc
{code}

h3. Учётные записи, сгруппированные по типу авторизации и NAS, SUP-1004993

У технической поддержки при часто возникает необходимость узнать, какой тип авторизации, на каком NAS и с какой схемой используется. Это необходимо, чтобы правильно диагностировать причины различных инцедентов с авторизацией.

В отчёт попадут только абоненты, у которых указан NAS и задан либо IP, либо телефонный номер.

{code}
select
count(*) "Количество",
n.name || ' (ID=' || cast(n.id as varchar(32)) || ')' "NAS",
ns.name "Схема",
coalesce(aut.name,'Не задан') "Тип авторизации"
from
users u
left join
auth_types aut
on u.auth_type=aut.id
join
nas n
on u.nas_id=n.id
join
nas_scheme ns
on n.nas_scheme_id=ns.id
where
u.deleted=0
and u.is_template=0
and (
u.ip is not null
or u.phone is not null
)
and u.id>0
group by
n.id,
n.name,
ns.name,
aut.name
order by