Используемые системой статические IP адреса
=============================
Присвоенные абонентам и используемые системой статические IP адреса
=============================
SELECT u.id AS login, pi.fio, INET_NTOA(dv.ip) AS ip_adress from users u
INNER JOIN users_pi pi ON (pi.uid=u.uid)
INNER JOIN dv_main dv ON (u.uid=dv.uid)
ORDER BY `login`
ASC LIMIT 10000
=============================
=============================
login:$_LOGIN:
fio:$_FIO:
ip_adress:ip_adress:
=============================
Последние подключения
=============================
=============================
SELECT
MAX(start) AS last_session, dv_log.uid, u.id as login
FROM dv_log
INNER JOIN users u ON (u.uid=dv_log.uid)
GROUP BY uid
HAVING last_session > '%PARAMETER%'
ORDER BY 1
=============================
=============================
=============================
Клиенты со статусом "Отключено" и привязанным IP-адресом
=============================
(только для модуля Dv)
=============================
SELECT u.uid,u.id,p.fio,inet_ntoa(d.ip), d.tp_id,t.name,
(SELECT MAX(datetime) FROM admin_actions WHERE uid = u.uid AND module = 'Dv' AND action_type = 4) status_change
FROM dv_main AS d
LEFT JOIN tarif_plans AS t ON t.id=d.tp_id
LEFT JOIN users AS u ON u.uid=d.uid
LEFT JOIN users_pi AS p ON p.uid=d.uid
WHERE d.disable = '1' AND
inet_ntoa(d.ip) != '0.0.0.0'
ORDER BY status_change desc;
=============================
=============================
=============================
Клиенты, которые подключились и не пользуются услугами
=============================
Клиенты, которые были подключены в течение последних трёх месяцев, но сейчас не работают, и количество дней, которые они не работают.
(только для модуля Dv)
=============================
SELECT
u.uid
, u.id login
, pi.fio
, pi.contract_id
, CASE
WHEN dv.disable = 0 THEN 'Активный'
WHEN dv.disable = 1 THEN 'Отключен'
WHEN dv.disable = 2 THEN 'Не активизирован'
WHEN dv.disable = 3 THEN 'Приостановление'
WHEN dv.disable = 4 THEN 'Откл: Неоплата'
WHEN dv.disable = 5 THEN 'Маленький депозит'
ELSE 'Неопределённый'
END status
, u.registration
, DATEDIFF(CURDATE(), IFNULL((SELECT MAX(datetime) FROM admin_actions WHERE uid = u.uid AND module = 'Dv' AND action_type = 4), u.registration)) change_days_ago
FROM
users u
LEFT JOIN users_pi pi
ON u.uid = pi.uid
LEFT JOIN dv_main dv
ON dv.uid = u.uid
WHERE
u.gid IN (10, 150)
AND u.registration > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
AND dv.disable NOT IN (0)
ORDER BY change_days_ago;
=============================
=============================
uid:uid
login:логин
fio:ФИО
contract_id:Договор
status:Статус
change_days_ago:Дней неактивности
=============================
Неиспользуемые тарифные планы
=============================
Тарифные планы у которых количество пользователей равно 0
(только для модуля Dv)
=============================
SELECT tp.id, tp.name, tp.month_fee, tt.prepaid, tt.in_price, COUNT(dv.tp_id) AS tp_qty
FROM tarif_plans AS tp
LEFT JOIN dv_main AS dv ON dv.tp_id=tp.id
LEFT JOIN trafic_tarifs AS tt ON tp.tp_id=tt.tp_id
WHERE dv.uid IS NULL
GROUP BY tp.id
ORDER BY tp.id
=============================
=============================
id:ID
name: Название
month_fee: Абон. плата
prepaid: Предоплата(Мб)
in_price: Цена за 1Мб
tp_qty: Кол-во пользователей
=============================
Клиенты, у которых было больше 1 месячного списания
=============================
(только для модуля Dv)
=============================
SELECT
*
FROM
(SELECT
DATE_FORMAT(date, '%Y-%m') AS mon,
f.uid,
u.id,
pi.fio,
g.name AS group_name,
COUNT(*) AS cnt
FROM
fees f
INNER JOIN dv_main AS dvm ON f.uid = dvm.uid
INNER JOIN tarif_plans AS tp ON dvm.tp_id = tp.id
INNER JOIN users AS u ON f.uid = u.uid
INNER JOIN users_pi AS pi ON f.uid = pi.uid
INNER JOIN groups AS g ON u.gid = g.gid
WHERE
method IN (1) AND
dsc LIKE "Internet: %"
GROUP BY DATE_FORMAT(date, '%Y-%m') , uid
ORDER BY mon, uid) AS fc
WHERE
fc.cnt > 1;
=============================
=============================
mon:$_DATE
uid:UID
id:$_LOGIN
fio:$_FIO
group_name:$_GROUP
cnt:$_COUNT
=============================
Отчет по списаниям разбитый по услугам
=============================
=============================
SET @sql = NULL;
SELECT GROUP_CONCAT(
DISTINCT CONCAT(
' SUM(case WHEN f.method = "',
id,
'" THEN f.sum ELSE 0 end) AS "',
replace(name, ' ', '')
), '"'
)
INTO @sql
FROM fees_types;
SET @sql = CONCAT('
SELECT u.uid,
u.id AS login,
pi.fio AS FIO,
', @sql,'
FROM users u
LEFT JOIN fees f ON (f.uid = u.uid)
LEFT JOIN fees_types ft ON (ft.id = f.method)
LEFT JOIN users_pi pi ON (u.uid = pi.uid)
GROUP BY u.uid
ORDER BY %SORT% %DESC%
LIMIT %PG%, %PAGE_ROWS%
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
=============================
SELECT COUNT(DISTINCT u.id) as total FROM users u
LEFT JOIN fees f ON (f.uid = u.uid)
LEFT JOIN fees_types ft ON (ft.id = f.method)
=============================
=============================
Список абонентов/договоров, без услуг (услуги можно дополнить)
=============================
=============================
SELECT u.id as "Логин", up.fio as "ФИО", uc.value as "Номер",CONCAT(st.name," д.", b.number, " кв.", up.address_flat) as "Адрес"
FROM users u
JOIN users_pi up ON u.uid = up.uid
LEFT JOIN users_contacts uc ON u.uid = uc.uid AND uc.type_id = 1
LEFT JOIN builds b ON up.location_id = b.id
LEFT JOIN streets st ON b.street_id = st.id
WHERE u.uid NOT IN (SELECT uid FROM internet_main)
AND u.uid NOT IN (SELECT uid FROM iptv_main)
AND u.uid NOT IN (SELECT uid FROM cams_main)
GROUP BY u.id
Последний раз редактировалось vasgor Вт авг 17, 2021 10:19 am, всего редактировалось 2 раза.
Пользователи с двумя и более услугами на одном адресе
=============================
=============================
select users_pi.address, users_pi.summ
from users t1
join
(
SELECT uid , CONCAT(st.name, ', ', bl.number, ', ' , up.address_flat) as address, COUNT(*) AS summ
FROM users_pi up, builds bl, streets st
WHERE location_id NOT IN (ID улиц для исключения из поиска, если необходимо) AND up.location_id = bl.id AND bl.street_id = st.id
GROUP BY up.location_id, up.address_flat
HAVING COUNT(*)>1
) users_pi on t1.uid= users_pi.uid
ORDER BY users_pi.address
Последний раз редактировалось vasgor Вт авг 17, 2021 10:19 am, всего редактировалось 2 раза.
Пользователи определенных тарифных планов, которые не пользуются услугами более 11 месяцев.
=============================
=============================
SELECT o.uid as id, bil.deposit as "Баланс", o.start as "Последнее использование"
FROM `internet_log` o
LEFT JOIN `internet_log` b
ON o.uid = b.uid AND o.start< b.start
JOIN bills bil
ON bil.uid = o.uid
WHERE b.start is NULL AND o.tp_id NOT IN (ID тарифных планов по которым ищем) AND UNIX_TIMESTAMP(o.start) <= UNIX_TIMESTAMP(NOW())-30240000
Последний раз редактировалось vasgor Вт авг 17, 2021 10:21 am, всего редактировалось 1 раз.
Отчет который выводит список абонентов и их услуги (ФИО, услуги, адрес, контакты). На данный момент штатным поиском этого не сделать.
Данные можно дополнить, при необходимости.
=============================
Запрос: Основной
=============================
SELECT pi.fio as "ФИО", CONCAT(st.name," д.",b.number," кв.",pi.address_flat) as "Адрес" , GROUP_CONCAT(tp.name ORDER BY tp.module ASC SEPARATOR " | ") as "Тариф",uc.value as "Телефоны",pi.comments AS "Примечание"
FROM users u
JOIN users_pi pi ON pi.uid = u.uid
LEFT JOIN internet_main im ON im.uid = u.uid
LEFT JOIN iptv_main imm ON imm.uid = u.uid
LEFT JOIN users_contacts uc ON u.uid = uc.uid AND uc.type_id = 1
JOIN builds b ON b.id = pi.location_id
JOIN streets st ON st.id = b.street_id
JOIN tarif_plans tp ON tp.tp_id = im.tp_id OR tp.tp_id = imm.tp_id
WHERE %ADDRESS%
GROUP BY pi.fio
=============================
Запрос: Всего
=============================
SELECT u.id as login, pi.uid
FROM users AS u
LEFT JOIN users_pi pi ON pi.uid = u.uid
WHERE %ADDRESS%
ORDER BY pi.address_flat+0 ASC
Последний раз редактировалось vasgor Вт авг 17, 2021 10:26 am, всего редактировалось 1 раз.
Динамика действий с абонентами
=============================
Запрос: Основной
=============================
SELECT DATE_FORMAT(datetime, '%Y-%m') AS month,
sum(if(action_type = 7, 1, 0)) AS registred,
sum(if(action_type = 12, 1, 0)) AS deleted,
sum(if(action_type = 9, 1, 0))-sum(if(action_type = 8, 1, 0)) AS disabled,
sum(if(action_type = 8, 1, 0)) AS enabled
FROM admin_actions aa
LEFT JOIN users u ON (u.uid=aa.uid)
WHERE aa.module='' AND u.gid='%GID%' AND
DATE_FORMAT(datetime, '%Y-%m-%d')>='%DATE_FROM%' AND DATE_FORMAT(datetime, '%Y-%m-%d')<='%DATE_TO%'
GROUP BY 1
ORDER BY 1
=============================
Поля (Поле:Название:CHART[LINE]:FILTER)
=============================
month:$_MONTH:X_TEXT
registred:$_REGISTRATION:LINE
deleted:$_DELETED:LINE
disabled:$_DISABLE:LINE
enabled:$_ACTIVATE:LINE
Количество абонентов ТВ на тарифных планах по скидкам
=============================
Запрос: Основной и Всего
=============================
Select tp.name as `Тариф`, tp.month_fee as `Стоимость`, us.reduction as `Скидка`, count(us.reduction) as `Количество`from users us
left join iptv_main im on im.uid = us.uid
left join tarif_plans tp on tp.tp_id = im.tp_id
group by im.tp_id, us.reduction
Количество абонентов Интернет на тарифных планах по скидкам
=============================
Запрос: Основной и Всего
=============================
select tp.name as `Тариф`, tp.month_fee as `Стоимость`, us.reduction as `Скидка`, count(us.reduction) as `Количество` from users us
left join internet_main im on im.uid = us.uid
left join tarif_plans tp on tp.tp_id = im.tp_id
group by im.tp_id, us.reduction
Количество абонентов ТВ на тарифных планах
=============================
Запрос: Основной и Всего
=============================
SELECT
MAX(tp.name) AS 'Тарифный план',
COUNT(im.tp_id) AS 'Количество абонентов'
FROM
iptv_main im, tarif_plans tp
where im.tp_id = tp.tp_id
GROUP BY (im.tp_id)
Количество абонентов без услуг (услуги дополняются по необходимости)
=============================
Запрос: Основной
=============================
SELECT COUNT(*) as not_servise
FROM users
WHERE uid NOT IN (SELECT uid FROM internet_main)
AND uid NOT IN (SELECT uid FROM iptv_main)
AND uid NOT IN (SELECT uid FROM cams_main);
Список номеров телефонов абонентов по адресу (некоторые условия написаны под нас - нужно исправить или удавлить)
=============================
Запрос: Основной и Всего
=============================
SELECT u.id as login, pi.uid,GROUP_CONCAT(DISTINCT uc.value SEPARATOR " | ") as "Телефон",tp.name
FROM users AS u
JOIN users_pi pi ON pi.uid = u.uid
LEFT JOIN iptv_main im ON u.uid = im.uid AND im.tp_id <> 20
LEFT JOIN internet_main imm ON u.uid = imm.uid AND imm.tp_id <> 64
LEFT JOIN cams_main immm ON u.uid = immm.uid AND immm.tp_id <> 91
JOIN tarif_plans tp ON immm.tp_id = tp.tp_id OR imm.tp_id = tp.tp_id OR im.tp_id = tp.tp_id
JOIN users_contacts uc ON u.uid = uc.uid AND uc.type_id <= 2
WHERE %ADDRESS% AND u.disable <> 1
GROUP BY u.uid