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

Технические вопросы по UTM 5.0
Закрыто
Godzilla
Сообщения: 7
Зарегистрирован: Ср дек 16, 2015 13:26

Сообщение Godzilla »

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

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

Cramac
Сообщения: 454
Зарегистрирован: Сб июл 01, 2006 17:59

Сообщение Cramac »

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

Godzilla
Сообщения: 7
Зарегистрирован: Ср дек 16, 2015 13:26

Сообщение Godzilla »

Cramac писал(а):да, поля те же.
П.С. у меня 5.2.005 а в 5.3 там новая система тарификации, вроде как
Спасибо большое, буду искать

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

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

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

kara писал(а):Ребят, а как можно спрогнозировать, кто завтра\послезавтра заблокируется по балансу(через снятие абонплаты)? :?
Теоретически, делаем выборку по расчетным периодам с окончанием текущая дата +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 Вт янв 05, 2016 14:53, всего редактировалось 2 раза.

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

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

gagarin
Сообщения: 16
Зарегистрирован: Пт мар 06, 2015 15:10

Сообщение 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
Сообщения: 1947
Зарегистрирован: Чт сен 22, 2005 06:54
Контактная информация:

Сообщение Magnum72 »

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%';

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

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 &#40;acc.block_id = b.id&#41; 
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
Сообщения: 16
Зарегистрирован: Пт мар 06, 2015 15:10

Сообщение gagarin »

Еще и с балансом, спасибо огромное :)
Magnum72 писал(а):

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

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 &#40;acc.block_id = b.id&#41; 
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
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

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

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

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

SELECT 
u.id as User_ID,u.login, 
REPLACE&#40;TRIM&#40;u.full_name&#41;, '\t', ''&#41; AS imya, 
SUBSTRING&#40;REPLACE&#40;REPLACE&#40;REPLACE&#40;REPLACE&#40;REPLACE&#40;u.mobile_telephone,' ',''&#41;,'+',''&#41;,'&#41;',''&#41;,'&#40;',''&#41;,'-',''&#41;, 1, 11&#41; as MPhone, 
a.id as Account_ID, 
ROUND&#40;a.balance,2&#41; AS balans, 
a.credit, 
atl.tariff_id, 
&#40;SELECT EXTRACT&#40;DAY FROM LAST_DAY&#40;CURRENT_DATE&#41;&#41;&#41; 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 Сб фев 20, 2016 07:18, всего редактировалось 1 раз.

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

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

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

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

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

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

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

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

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

select * from users_groups_link GROUP BY user_id HAVING COUNT&#40;user_id&#41;>1;
Думаю сами подставите нужные значение, вот так например я считаю сколько в какой группе у меня юзеров.

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

select group_id,COUNT&#40;group_id&#41; from users_groups_link GROUP BY group_id;

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

Да, нужно
Rico-X писал(а): присутствие в 2х конкретных группах
Просто если такой запрос сделать:

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

SELECT u.full_name, ugl.group_id
  FROM users u, users_groups_link ugl
  WHERE u.id=ugl.user_id
  AND &#40;ugl.group_id=110 AND ugl.group_id=130&#41;
то в ответе пусто.

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

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

SELECT u.full_name, ugl.group_id
  FROM users u
  LEFT JOIN users_groups_link ugl ON ugl.user_id=u.id
  WHERE &#40;ugl.group_id=110 AND ugl.group_id=130&#41;

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

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
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

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

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

SELECT u.full_name, ugl.group_id
  FROM users u, users_groups_link ugl
  WHERE u.id=ugl.user_id
  AND &#40;ugl.group_id=110 AND ugl.group_id=130&#41;
то в ответе пусто.
По-разному пробовал
Ну так логично, вы же в выборке хотите чтоб в одной и той-же строке была группа и 110 и 130 так не бывает, меняйте на:

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

SELECT u.full_name, ugl.group_id   FROM users u, users_groups_link ugl   WHERE u.id=ugl.user_id AND &#40;ugl.group_id=110 OR ugl.group_id=130&#41; GROUP BY u.id HAVING COUNT&#40;u.id&#41;>1;
И будет как вы хотите, суть запроса находим всех юзеров что вообще есть в группах 110 и130, затем проверяем по u.id сколько раз встречается конкретный юзер, если больше 1 (ну или =2), то соответственно он есть в обеих группах.

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

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

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

SELECT u.full_name
  FROM UTM5.users u
  JOIN UTM5.users_groups_link ugl ON ugl.user_id=u.id
  WHERE &#40;ugl.group_id=130 OR ugl.group_id=110&#41; 
  GROUP BY u.id 
  HAVING COUNT&#40;u.id&#41;>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 &#40;ugl.group_id=130 OR ugl.group_id=110&#41; 
  GROUP BY u.id 
  HAVING COUNT&#40;u.id&#41;>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 &#40;ugl.group_id=130 OR ugl.group_id=110&#41; 
  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
Тяжко как-то этот запрос рождается...

Закрыто