Несколько полезных SQL запросов

[Godzilla]

не знаю Вашу версию УТМ, но посмотрел у себя, так как положено.

Дальше подсказать не могу, посмотрел как сам считаю, у меня завязано с своей веб мордой и от туда берется цена.

Разрешите Вас ещё немножко помучать со своим вопросом)
UTM5 версия 3.0 у меня, а в таблице periodic_services_data следующие поля:
id, cost, discount_method, start_date, expire_date, radius_session_limit, is_deleted. У Вас аналогично?

[Cramac]

да, поля те же.
П.С. у меня 5.2.005 а в 5.3 там новая система тарификации, вроде как

[Godzilla]

да, поля те же.
П.С. у меня 5.2.005 а в 5.3 там новая система тарификации, вроде как

Спасибо большое, буду искать

[kara]

Ребят, а как можно спрогнозировать, кто завтра\послезавтра заблокируется по балансу(через снятие абонплаты)?

[Rico-X]

Ребят, а как можно спрогнозировать, кто завтра\послезавтра заблокируется по балансу(через снятие абонплаты)?

Теоретически, делаем выборку по расчетным периодам с окончанием текущая дата +3 дня, какие пользователи в выборку попадают и у кого сума на счету + сумма в кредите меньше стоимости его тарифа, запрос сходу не набросаю, надо ковырять базу, но видится как-то так. Делал для другого биллинга такой запрос, но там в базе можно без литра водки разобраться, а в сети с UTM5 в качестве биллинга пока такой задачи не стоит.

UPD. Я конечно не настоящий сварщик (наш ДБА в запое, пришлось вспомнить мускуль), но набросал запрос, который выдает юзеров, которым осталось менее 3х дней до расчетного периода при этом сумма баланса и кредита на счету меньше суммарной стоимости по всем услугам в сервисных связках, юзер не заблокирован и не удален как и сервисные связки. Сделал округляшку сумм в выводе и нормализатор телефонов, кривой, но кому надо подправит под себя

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

SELECT
u.id as User_ID,u.login,
REPLACE(TRIM(u.full_name), '\t', ''),
SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(u.mobile_telephone,' ',''),'+',''),')',''),'(',''),'-',''), 1, 11) as MPhone,
a.id as Account_ID,
ROUND(a.balance,2),
a.credit,
atl.tariff_id,
SUM(psd.cost),
ROUND(a.balance+a.credit-SUM(psd.cost),2) as DIFF,
DATEDIFF(FROM_UNIXTIME(dp.end_date),NOW()) as DaysLeft
FROM
accounts a, users u, account_tariff_link atl, discount_periods dp, service_links sl, periodic_services_data psd
WHERE
u.basic_account=a.ID
AND atl.account_id=a.ID
AND atl.is_deleted=0
AND dp.id=atl.discount_period_id
AND a.id=atl.account_id
AND a.is_deleted=0
AND a.is_blocked=0
AND a.block_id=0
AND sl.account_id=a.ID
AND sl.is_deleted=0
AND psd.id=sl.service_id
AND psd.is_deleted=0
AND (DATEDIFF(FROM_UNIXTIME(dp.end_date),NOW()) between 0 and 3)
AND (dp.end_date - UNIX_TIMESTAMP()>0) GROUP BY u.id,u.login,u.full_name,a.id,a.balance,a.credit,atl.tariff_id
HAVING
&#40;a.balance+a.credit&#41;-SUM&#40;psd.cost&#41;<0;

[Rico-X]

дубль верхнего.

[gagarin]

Помогите плз с запросом на выгрузку пользователей определенного тарифа. Нужно добавить состояние блокировки и полное имя.

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

select u.login, t.name, atl.account_id, atl.tariff_id , atl.is_deleted from account_tariff_link AS atl, tariffs AS t, users AS u where atl.account_id=u.basic_account AND atl.tariff_id=t.id AND atl.is_deleted=0 and t.name LIKE '%500%';

[Magnum72]

Помогите плз с запросом на выгрузку пользователей определенного тарифа. Нужно добавить состояние блокировки и полное имя.

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

select u.login, t.name, atl.account_id, atl.tariff_id , atl.is_deleted from account_tariff_link AS atl, tariffs AS t, users AS u where atl.account_id=u.basic_account AND atl.tariff_id=t.id AND atl.is_deleted=0 and t.name LIKE '%500%';

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

SELECT 
  atl.account_id,
  u.login,
  u.full_name,
  acc.balance,
  b.block_type,
  atl.tariff_id,
  t.name 
FROM
  account_tariff_link AS atl,
  tariffs AS t,
  users AS u,
  accounts AS acc 
  LEFT JOIN blocks_info AS b 
    ON (acc.block_id = b.id) 
WHERE 1 
  AND atl.account_id = u.basic_account 
  AND acc.id = u.basic_account 
  AND atl.tariff_id = t.id 
  AND atl.is_deleted = 0 
  AND t.name LIKE '%500%';

[gagarin]

Еще и с балансом, спасибо огромное

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

SELECT 
  atl.account_id,
  u.login,
  u.full_name,
  acc.balance,
  b.block_type,
  atl.tariff_id,
  t.name 
FROM
  account_tariff_link AS atl,
  tariffs AS t,
  users AS u,
  accounts AS acc 
  LEFT JOIN blocks_info AS b 
    ON (acc.block_id = b.id) 
WHERE 1 
  AND atl.account_id = u.basic_account 
  AND acc.id = u.basic_account 
  AND atl.tariff_id = t.id 
  AND atl.is_deleted = 0 
  AND t.name LIKE '%500%';

[kara]

набросал запрос, который выдает юзеров, которым осталось менее 3х дней до расчетного периода при этом сумма баланса и кредита на счету меньше суммарной стоимости по всем услугам в сервисных связках, юзер не заблокирован и не удален как и сервисные связки. Сделал округляшку сумм в выводе и нормализатор телефонов, кривой, но кому надо подправит под себя

Спасибо за идею!

Чуток перебыдлокодил под v5.2.1-009 с несколькими аккаунтами на каждого юзера и под ежедневное списание абонплаты для уведомления тех абонентов, у кого завтра заблокируется аккаунт.

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

SELECT 
u.id as User_ID,u.login, 
REPLACE(TRIM(u.full_name), '\t', '') AS imya, 
SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(u.mobile_telephone,' ',''),'+',''),')',''),'(',''),'-',''), 1, 11) as MPhone, 
a.id as Account_ID, 
ROUND(a.balance,2) AS balans, 
a.credit, 
atl.tariff_id, 
(SELECT EXTRACT(DAY FROM LAST_DAY(CURRENT_DATE))) as dayinmonth
FROM 
accounts a, users u, users_accounts ua, account_tariff_link atl, discount_periods dp, service_links sl, periodic_services_data psd 
WHERE 
u.id=ua.uid
AND ua.account_id=a.ID 
AND ua.is_deleted=0
AND atl.account_id=a.ID 
AND atl.is_deleted=0 
AND dp.id=atl.discount_period_id 
AND a.id=atl.account_id 
AND a.is_deleted=0 
AND a.is_blocked=0 
AND sl.account_id=a.ID 
AND sl.is_deleted=0 
AND psd.id=sl.service_id 
AND psd.discount_method=3
AND psd.is_deleted=0 
  GROUP BY u.id,u.login,u.full_name,a.id,a.balance,a.credit,atl.tariff_id 
HAVING 
&#40;a.balance+a.credit&#41;-&#40;SUM&#40;psd.cost&#41;/dayinmonth&#41;<0;

[kara]

Коллеги, есть у кого идеи каким запросом можно выбрать всех юзеров, которые являются членами одновременно нескольких групп?

Ну т.е. нужны юзеры у которые находятся к примеру сразу и в группе 100 и в группе 110.

Голову что-то уже сломал, не могу понять как сделать такую простейшую выборку, админка как-то у себя в потрохах сама фильтрует при таких условиях выборки.

[Rico-X]

Коллеги, есть у кого идеи каким запросом можно выбрать всех юзеров, которые являются членами одновременно нескольких групп?

Ну т.е. нужны юзеры у которые находятся к примеру сразу и в группе 100 и в группе 110.

Голову что-то уже сломал, не могу понять как сделать такую простейшую выборку, админка как-то у себя в потрохах сама фильтрует при таких условиях выборки.

Не совсем понял что вы хотите, если присутствие в 2х конкретных группах, то через where и AND, если у кого вообще есть больше 1 группы, то типа

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

select * from users_groups_link GROUP BY user_id HAVING COUNT(user_id)>1;

Думаю сами подставите нужные значение, вот так например я считаю сколько в какой группе у меня юзеров.

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

select group_id,COUNT(group_id) from users_groups_link GROUP BY group_id;

[kara]

Да, нужно

присутствие в 2х конкретных группах

Просто если такой запрос сделать:

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

SELECT u.full_name, ugl.group_id
  FROM users u, users_groups_link ugl
  WHERE u.id=ugl.user_id
  AND (ugl.group_id=110 AND ugl.group_id=130)

то в ответе пусто.

По-разному пробовал:

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

SELECT u.full_name, ugl.group_id
  FROM users u
  LEFT JOIN users_groups_link ugl ON ugl.user_id=u.id
  WHERE (ugl.group_id=110 AND ugl.group_id=130)

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

SELECT u.full_name, ugl.group_id
  FROM users u
  LEFT JOIN users_groups_link ugl ON ugl.user_id=u.id
  LEFT JOIN groups g ON g.id=ugl.group_id
  WHERE g.id=110
  AND g.id=130;

Один фиг пусто, что-то не так делаю...

[Rico-X]

Просто если такой запрос сделать:

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

SELECT u.full_name, ugl.group_id
  FROM users u, users_groups_link ugl
  WHERE u.id=ugl.user_id
  AND (ugl.group_id=110 AND ugl.group_id=130)

то в ответе пусто.
По-разному пробовал

Ну так логично, вы же в выборке хотите чтоб в одной и той-же строке была группа и 110 и 130 так не бывает, меняйте на:

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

SELECT u.full_name, ugl.group_id   FROM users u, users_groups_link ugl   WHERE u.id=ugl.user_id AND (ugl.group_id=110 OR ugl.group_id=130) GROUP BY u.id HAVING COUNT(u.id)>1;

И будет как вы хотите, суть запроса находим всех юзеров что вообще есть в группах 110 и130, затем проверяем по u.id сколько раз встречается конкретный юзер, если больше 1 (ну или =2), то соответственно он есть в обеих группах.

[kara]

Переделал на join:

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

SELECT u.full_name
  FROM UTM5.users u
  JOIN UTM5.users_groups_link ugl ON ugl.user_id=u.id
  WHERE (ugl.group_id=130 OR ugl.group_id=110) 
  GROUP BY u.id 
  HAVING COUNT(u.id)>1;

Вроде пашет, но.

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

SELECT u.full_name
  FROM UTM5.users u
  JOIN UTM5.users_groups_link ugl ON ugl.user_id=u.id
  JOIN UTM5.users_accounts ua ON ua.uid=u.id
  WHERE (ugl.group_id=130 OR ugl.group_id=110) 
  GROUP BY u.id 
  HAVING COUNT(u.id)>1;

Как только иду дальше и беру инфу о лицевых счетах, то ахтунг, результат выборки сбивается почему-то

Конечная цель примерно такая:

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

SELECT u.*, i.*
  FROM UTM5.users u
  JOIN UTM5.users_groups_link ugl ON ugl.user_id=u.id
  JOIN UTM5.users_accounts ua ON ua.uid=u.id
  JOIN UTM5.accounts a ON ua.account_id=a.id
  JOIN UTM5.invoices i ON i.account_id=a.id
  JOIN UTM5.invoice_entry ie ON ie.invoice_id=i.id
  WHERE (ugl.group_id=130 OR ugl.group_id=110) 
  AND u.is_deleted=0
  AND ua.is_deleted=0
  AND a.is_deleted=0
  AND i.invoice_date >= 1451588400 
  AND i.invoice_date <= 1454266800 
  AND ie.sum_cost > '0' 
  GROUP BY u.id 
  HAVING COUNT&#40;u.id&#41;>1

Тяжко как-то этот запрос рождается...