Конструктор отчетов. Отчёт

Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.

Изменения (29)

просмотр истории страницы
...


Из командной сторки запросы выполняются при помощи утилиты sqlexec таким образомsqlexec "текст запроса" sqlexec.


h6. Дополнительные моменты

Вывод на экран в виде списка sqlexec "set list on ; текст запроса"

Вывод на экран русских символов - добавьте в конец строки. \| iconv \-f cp1251

Запись в файл, для того чтобы забрать его по WinSCP / SCP позже: > /tmp/file

h2. Примеры:
...
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID
where PL.MSG_OUT = 'ACCEPTED' and PL.OPERATOR_DATE_IN >= '2013.01.01' '2013-01-01'
group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY
{code}
...
{code}

*Пример 45. Список должников абонентов с последней оплатой (ФИО,Логин,Договор,Дата последнего платежа,Сумма последнего платежа,Текущий баланс)*

Запрос:
...
{code}
SET TERM ^ ;
create procedure REP_FINOPER_LAST1 (
OP_TYPE_IN integer)
returns (
...
from FINANCE_OPERATIONS
where OP_ID = :TMP_OP_ID
and DESCR not like '%Обещанный платеж%'
order by OP_ID desc
into :OP_ID,
...
suspend;
end

end^
SET TERM ; ^
...
{code}
Пояснения полей:
PULL_ID - номер пула
TOTAL - всего адресов в пуле
TOTAL_IN_PULLCACHE - кол-во пред выделенных адресов (далее цифры уже будут зависеть от этого значения)
STEP_1_FREE - кол-во адресов, которые уже выделены абонентам и будут им повторно выделяться
STEP_2_FREE - кол-во адресов, которые не привязаны к абонентам
STEP_3_FREE - кол-во адресов, у которых IP_LOCK is null
STEP_3_FREE_WITHOUT_USED - кол-во адресов, у которых IP_LOCK is null и которые не используются (могут быть забраны у разлогиненого абонента, например)
STEP_4_FREE - кол-во адресов, которые назначены абонентам, но которые можно забрать

*Пример 89. Список финансовых операций, который биыли проведены задним числом оператором с id=2222 (ФИО, Логин абонента, Дата операции в прошлом, Системная(фактическая) дата, сумма операции, оператор который проводил операцию)*
{code}
select U.identify as "ФИО",
...
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where
FO.op_type=2 and
cast(FO.OP_DATE as date) < cast(FO.SYSTEM_DATE as date) and
cast(FO.SYSTEM_DATE as date) = current_date and
FO.OWNER_ID = 2222
{code}

*Пример 90. Список id абонентов, с выставленными вручную суммами абон.платы, сумма абон.платы по назначенному тарифу (упрощенная версия, берется только ежемесячная сумма без учета настроек тарифа) и кол-во под-абонентов (актуально для фин.групп)*
======= {code}
select ID,
ABONENT_PAY / 10000000000.00,
(select ABONENT_PAY
from TARIF_PLAN
where PLAN_ID = USERS.TARIFF_ID_CACHE) / 10000000000.00 as FORCED_PAY_SUM,
(select count(1)
from USERS
where END_USER = 1 and DELETED = 0 and PARID = U.ID) as CHILD_COUNT
from USERS U
where U.ABONENT_PAY is not null
Отчет {code}
Отчеты
*Пример 91. Список поступлений абонентов в формате(ФИО, Номер договора, Тарифный план, сумма) за период с 2015-01-01 по 2015-01-31*
{code}
select
u.identify as "ФИО",
u.contract_number as "Договор",
tp.plan_name as "Тариф",
sum(FO.OP_SUMMA)/10000000000.00 as "Приход"
from users u
left join tarif_plan tp on u.TARIFF_ID_CACHE=tp.plan_id
left join finance_operations fo on u.id=fo.USER_ID
where fo.op_type=2 and (fo.OP_DATE between '2015-01-01' and '2015-07-01')
group by U.IDENTIFY,U.CONTRACT_NUMBER,tp.plan_name
{code}

*Пример 92. Список абонентов в формате ID, ФИО, у которых не заполнен реквизит Паспорт*
{code}
select
u.id,
u.identify
from users u
left join attribute_values av on u.id=av.user_id and av.ATTRIBUTE_ID=14
where av.ATTRIBUTE_VALUE is null and u.id<100000 and u.end_user=1 and u.deleted=0
{code}

*Пример 93. * \*Список итоговой суммы все{*}{color:#333333}{*}х активированных карт по{*}{color} *выбранной группе и времени с учётом включенных папок в папке 656*
{code}
select sum(card_series.card_value)/10000000000 as ALL_PERIOD_SUM FROM CARDS
LEFT JOIN CARD_SERIES ON CARDS.SERIES_NO = card_series.SERIES_NO
left join users on users.id=CARDS.user_id
where users.parid in (select GROUP_ID from GLN_RECURSIVE_GROUP_WALK(656))
and cards.date_used > '2012-12-01 00:00:00'
and cards.date_used < '2013-01-01 00:00:00'
and cards.date_used is not null
{code}

*Пример 94. Дата последнего платежа всех пользователей в группе*
{code}select distinct
users.IDENTIFY as "ФИО",
users.login as "Логин",
users.sms as "Телефон",
AV.ATTRIBUTE_VALUE as "Адрес",
max(FINANCE_OPERATIONS.OP_DATE) as "Дата последнего платежа"
from users
left join FINANCE_OPERATIONS
on users.id=FINANCE_OPERATIONS.USER_ID
and FINANCE_OPERATIONS.OP_TYPE=2
left join ATTRIBUTE_VALUES AV on ( Users.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3)
where users.parid=8
and users.deleted=0
group by users.IDENTIFY, users.sms, users.login,AV.ATTRIBUTE_VALUE
{code}

*Пример 95. Отчет для ФСБ. Список пользователей в формате (IP, логин, ФИО, ИНН, Дата рождения, Прописка, Серия паспорта, Номер, Дата выдачи, Кем выдан, Дата подключения, Дата отключения, Номер телефона, Статус)*
{code:lang=sql}
select
uf_ip2string(U.IP) as "IP",
U.LOGIN as "Логин",
U.IDENTIFY as "ФИО",
AV4.ATTRIBUTE_VALUE as "ИНН",
AV3.ATTRIBUTE_VALUE as "Дата рождения",
AV15.ATTRIBUTE_VALUE as "Прописка",
AV14.ATTRIBUTE_VALUE || ' ' ||
AV13.ATTRIBUTE_VALUE || ' ' ||
AV16.ATTRIBUTE_VALUE || ' ' ||
AV17.ATTRIBUTE_VALUE as "Паспортные данные",
U.CREATE_DATE as "Дата подключения",
(select max(ao1.op_time) from audit_operations ao1 where ao1.object_id=u.id and ao1.descr like '%Отключен=1' ) as "Дата отключения",
AV1.ATTRIBUTE_VALUE as "Номер телефона",
U.ENABLED as "Статус (1-подключен,0-отключен)"
from users U
left join attribute_values av1 on U.ID=AV1.user_id and AV1.attribute_id=1
left join attribute_values av4 on U.ID=AV4.user_id and AV4.attribute_id=4
left join attribute_values av13 on U.ID=AV13.user_id and AV13.attribute_id=13
left join attribute_values av14 on U.ID=AV14.user_id and AV14.attribute_id=14
left join attribute_values av15 on U.ID=AV15.user_id and AV15.attribute_id=15
left join attribute_values av16 on U.ID=AV16.user_id and AV16.attribute_id=16
left join attribute_values av17 on U.ID=AV17.user_id and AV17.attribute_id=17
left join attribute_values av3 on U.ID=AV3.user_id and AV3.attribute_id=3
where end_user=1 and u.id<100000
{code}

*Пример 96. Свободные IP из пулов адресов (IP, имя пула, состояние пула - включен(1)/выключен(0))*
{code:lang=sql}
select uf_ip2string(pc.ip) as "IP",
ipp.name as "POOL",
ipp.enabled as "Pool On(1)/Off(0)"
from pull_cache pc
left join ip_pull ipp on ipp.pull_id = pc.pull_id
where pc.user_id is null
order by pc.pull_id, pc.ip
{code}
*Пример 97. Абоненты с не уникальными snat_ip&nbsp;**(ID, SNAT_IP)*
{code:lang=sql}
select id,
uf_ip2string(snatip_cache) as "SNAT_IP"
from users
where uf_ip2string(snatip_cache) in
(select uf_ip2string(snatip_cache) from users group by uf_ip2string(snatip_cache) having count(*)>1)
and server=0
order by snatip_cache desc
{code}

*Пример 98. Списание платы за выбранные по id (ruleset_no) доп услуги за определенный период (ФИО, Услуга, Количество дней, Списано руб.)*
{code:lang=sql}
select u.identify as "ФИО",us.name as "Услуга",count(ruleset_no) as "Количество дней",sum(s.session_credit)/10000000000 as "Списано, руб."
FROM sessions s
left join users u on s.id=u.id left join usluga us on -us.id=s.ruleset_no
where s.ruleset_no in (-31,-35,-37,-38,-42,-43,-44) and
(s.date_in between '2017-02-01' and '2017-02-28')
group by s.id,u.identify,us.name
order by u.identify
{code}

*Пример 99. Список подключенных услуг у конкретного абонента с указанным логином (Услуга)*
{code:lang=sql}
select us.name as "Услуга"
from users_usluga uu
left join users u on uu.user_id=u.id
left join usluga us on uu.USLUGA_ID=us.id
where u.login='pupkin'
{code}

*Пример 100. Список приходов абонентов на конкретном тариф за указанный период*
{code:lang=sql}
select sum(FO.OP_SUMMA)/10000000000.00 as "Сумма"
from finance_operations fo
left join users u on u.id=fo.USER_ID
where fo.op_type=2 and u.TARIFF_ID_CACHE=37 and OP_DATE between '2016-09-01' and '2016-10-01'
{code}

*Пример 101. Список приходов абонентов через платежные системы за указанный период с указанием группы абонентов (папки) со всеми подгруппами и с Итоговой суммой (Номер договора, ФИО, Дата последнего платежа, Сумма платежа)*
{code:lang=sql}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as "FIO",
max(PL.OPERATOR_DATE_IN) as "LAST PAY DATE",
sum(PL.SUMMA_IN)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID
where PL.MSG_OUT = 'ACCEPTED' and PL.OPERATOR_DATE_IN >= '2013.01.01' and
U.parid in (select GROUP_ID from GLN_RECURSIVE_GROUP_WALK(654))
group by U.CONTRACT_NUMBER, U.IDENTIFY
union
select
cast('total' as varchar(32)),
cast('' as varchar(128)),
cast('' as timestamp),
sum(PL.SUMMA_IN)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID
where PL.MSG_OUT = 'ACCEPTED' and PL.OPERATOR_DATE_IN >= '2013.01.01' and
U.parid in (select GROUP_ID from GLN_RECURSIVE_GROUP_WALK(654))
{code}
*Пример 102. Отчёт по платежам за выбранный период в формате "id платежа - номер договора - сумма платежа"*
{code}
select
fo.OP_ID,
u.contract_number,
sum(FO.OP_SUMMA)/10000000000.00 as "Приход"
from users u
left join tarif_plan tp on u.TARIFF_ID_CACHE=tp.plan_id
left join finance_operations fo on u.id=fo.USER_ID
where fo.op_type=2 and (fo.OP_DATE between '2015-01-01' and '2018-07-01')
group by fo.OP_ID,U.CONTRACT_NUMBER
{code}

*Пример 103. Отчёт который показывает абонентов, у которых финансовых операций с типом расход больше, чем 1 за один и тот же день.*
{code}
select OP_DATE,
user_id,
count(*)
from finance_operations
where op_type=1
group by OP_DATE,user_id
having count(*)>1
{code}