Обмен отчётами

Модуль формирования произвольных отчётов и обмен отчётами
phantom
Сообщения: 2
Зарегистрирован: Пн июн 05, 2017 4:15 pm

Re: Обмен отчётами

Сообщение phantom »

Отчет по использованным системой статическим ip-адресам присвоенным абонентам (DV)

Код: Выделить всё

Используемые системой статические 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:
=============================

~AsmodeuS~
Site Admin
Сообщения: 5746
Зарегистрирован: Пт янв 28, 2005 3:11 pm
Контактная информация:

Re: Обмен отчётами

Сообщение ~AsmodeuS~ »

Последнее подключение

Код: Выделить всё

Последние подключения
=============================

=============================
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
=============================

=============================

=============================

n.borisenkov
Сообщения: 2
Зарегистрирован: Пт ноя 17, 2017 8:52 pm

Re: Обмен отчётами

Сообщение n.borisenkov »

Код: Выделить всё

Клиенты со статусом "Отключено" и привязанным 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: Кол-во пользователей
=============================

n.borisenkov
Сообщения: 2
Зарегистрирован: Пт ноя 17, 2017 8:52 pm

Re: Обмен отчётами

Сообщение n.borisenkov »

Код: Выделить всё

Клиенты, у которых было больше 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
=============================

~AsmodeuS~
Site Admin
Сообщения: 5746
Зарегистрирован: Пт янв 28, 2005 3:11 pm
Контактная информация:

Re: Обмен отчётами

Сообщение ~AsmodeuS~ »

Код: Выделить всё

Отчет по списаниям разбитый по услугам
=============================

=============================
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)
=============================

=============================

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Список абонентов/договоров, без услуг (услуги можно дополнить)
=============================

=============================
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 раза.

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Пользователи с двумя и более услугами на одном адресе
=============================

=============================
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 раза.

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Пользователи определенных тарифных планов, которые не пользуются услугами более 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 раз.

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Отчет который выводит список абонентов и их услуги (ФИО, услуги, адрес, контакты). На данный момент штатным поиском этого не сделать. 
Данные можно дополнить, при необходимости.
=============================

Запрос: Основной
=============================
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 раз.

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Динамика действий с абонентами
=============================

Запрос: Основной
=============================
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

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Количество абонентов ТВ на тарифных планах по скидкам
=============================

Запрос: Основной и Всего
=============================
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

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Количество абонентов Интернет на тарифных планах по скидкам
=============================

Запрос: Основной и Всего
=============================
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

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Количество абонентов ТВ на тарифных планах
=============================

Запрос: Основной и Всего
=============================
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)

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Количество абонентов без услуг (услуги дополняются по необходимости)
=============================

Запрос: Основной
=============================
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);

vasgor
Сообщения: 11
Зарегистрирован: Пн авг 16, 2021 12:51 pm
Откуда: Беларусь

Re: Обмен отчётами

Сообщение vasgor »

Код: Выделить всё

Список номеров телефонов абонентов по адресу (некоторые условия написаны под нас - нужно исправить или удавлить)
=============================

Запрос: Основной и Всего
=============================
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

Ответить