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

Технические вопросы по UTM 5.0
Закрыто
Vlad83
Сообщения: 11
Зарегистрирован: Пт май 15, 2009 12:09

Сообщение Vlad83 »

Здравствуйте!
Пытаюсь составить запрос, который должен выбирать абонента у которого сумма ВСЕХ услуг меньше текущего баланса. Выборка работает, но к сожалению по каждой услуге отдельно.
Подскажите пожалуйста как сделать выборку с суммой именно всех услуг абонента ?

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

SELECT u.login as lgn,u.password as pwd,u.mobile_telephone as mbl,ABS&#40;ROUND&#40;a.balance&#41;-&#40;psd.cost&#41;&#41; as razn 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 sl.account_id=a.ID AND sl.is_deleted=0 AND psd.id=sl.service_id  AND psd.is_deleted=0 AND psd.cost!=0 AND u.mobile_telephone!=0 AND a.balance-psd.cost<0;

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

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

Vlad83 писал(а):Здравствуйте!
Пытаюсь составить запрос, который должен выбирать абонента у которого сумма ВСЕХ услуг меньше текущего баланса. Выборка работает, но к сожалению по каждой услуге отдельно.
Подскажите пожалуйста как сделать выборку с суммой именно всех услуг абонента ?
Я давал на 12 странице этой темы, смотрите внимательно, вообще делается через

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

HAVING
&#40;a.balance+a.credit&#41;-SUM&#40;psd.cost&#41;<0;

Jonson
Сообщения: 150
Зарегистрирован: Ср фев 02, 2005 21:48
Откуда: МО, Ногинский р-он

Сообщение Jonson »

Добрый день, ранее делал запрос на ip, порт и коммутатор - запрос выдает 0.0.0.0 ip, как вытащить выданные dhcp ip?

SELECT inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS ip, port_id AS port, switches.name AS switch FROM \
users, service_links, iptraffic_service_links, ip_groups, switches WHERE \
users.id=service_links.user_id AND \
iptraffic_service_links.id=service_links.id AND \
iptraffic_service_links.ip_group_id=ip_groups.ip_group_id AND \
switches.id =ip_groups.switch_id AND \
iptraffic_service_links.is_deleted=0 AND \
service_links.is_deleted=0 AND \
switches.is_deleted=0 AND \
users.id ='10';

Fiendflug
Сообщения: 6
Зарегистрирован: Сб окт 08, 2011 18:13

Сообщение Fiendflug »

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

Аватара пользователя
MaxDM
Сообщения: 313
Зарегистрирован: Пн апр 03, 2006 10:26
Контактная информация:

Сообщение MaxDM »

Fiendflug писал(а):Друзья, подскажите пожалуйста, как посредством sql запроса достать список всех телефонных соединений конкретного лицевого счета со стоимостью за выбранный расчетный период?
Как-то так:

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

SELECT to_char&#40;to_timestamp&#40;tsl.session_start_date&#41;,'DD/MM/YYYY HH24&#58;MI&#58;SS'&#41; AS date, tsl.called_station_id, round&#40;SUM&#40;tsd.duration/60&#41;,2&#41; AS min, SUM&#40;tsd.sum_cost&#41; AS rub
FROM tel_sessions_log tsl, tel_sessions_detail tsd
WHERE tsl.account_id=111111 AND tsd.account_id=111111 AND tsl.id=tsd.dhs_sess_id AND tsl.session_start_date>=unix_timestamp&#40;'2016-07-01 00&#58;00&#58;00'&#41; AND tsl.session_start_date<=unix_timestamp&#40;'2016-07-31 23&#58;59&#58;59'&#41;
AND tsd.sum_cost>0
GROUP BY to_char&#40;to_timestamp&#40;tsl.session_start_date&#41;,'DD/MM/YYYY HH24&#58;MI&#58;SS'&#41;, tsl.called_station_id
ORDER BY to_char&#40;to_timestamp&#40;tsl.session_start_date&#41;,'DD/MM/YYYY HH24&#58;MI&#58;SS'&#41;

Fiendflug
Сообщения: 6
Зарегистрирован: Сб окт 08, 2011 18:13

Сообщение Fiendflug »

MaxDM писал(а):
Fiendflug писал(а):Друзья, подскажите пожалуйста, как посредством sql запроса достать список всех телефонных соединений конкретного лицевого счета со стоимостью за выбранный расчетный период?
Как-то так:

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

SELECT to_char&#40;to_timestamp&#40;tsl.session_start_date&#41;,'DD/MM/YYYY HH24&#58;MI&#58;SS'&#41; AS date, tsl.called_station_id, round&#40;SUM&#40;tsd.duration/60&#41;,2&#41; AS min, SUM&#40;tsd.sum_cost&#41; AS rub
FROM tel_sessions_log tsl, tel_sessions_detail tsd
WHERE tsl.account_id=111111 AND tsd.account_id=111111 AND tsl.id=tsd.dhs_sess_id AND tsl.session_start_date>=unix_timestamp&#40;'2016-07-01 00&#58;00&#58;00'&#41; AND tsl.session_start_date<=unix_timestamp&#40;'2016-07-31 23&#58;59&#58;59'&#41;
AND tsd.sum_cost>0
GROUP BY to_char&#40;to_timestamp&#40;tsl.session_start_date&#41;,'DD/MM/YYYY HH24&#58;MI&#58;SS'&#41;, tsl.called_station_id
ORDER BY to_char&#40;to_timestamp&#40;tsl.session_start_date&#41;,'DD/MM/YYYY HH24&#58;MI&#58;SS'&#41;
Спасибо большое.

Morbid
Сообщения: 104
Зарегистрирован: Пт окт 02, 2009 15:00
Откуда: МО

Сообщение Morbid »

Коллеги, подскажите пожалуйста, как узнать, у абонента остаток предоплаченого трафика (желательно именно селектом из базы)

Ну или как вычислить его, порылся так по базе, на вскидку не нашел.

Аватара пользователя
MaxDM
Сообщения: 313
Зарегистрирован: Пн апр 03, 2006 10:26
Контактная информация:

Сообщение MaxDM »

Morbid писал(а):Коллеги, подскажите пожалуйста, как узнать, у абонента остаток предоплаченого трафика (желательно именно селектом из базы)

Ну или как вычислить его, порылся так по базе, на вскидку не нашел.
Селектом не делал.

Через урфу:

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

my $prepaid = $ourfa->rpcf_get_prepaid_units&#40; slink_id=>111111 &#41;;

Аватара пользователя
Magnum72
Сообщения: 1947
Зарегистрирован: Чт сен 22, 2005 06:54
Контактная информация:

Сообщение Magnum72 »

Может кому пригодятся функции для мускула:

1) mask (Форматирует данные, удобно для выборки из базы телефонов)

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

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `mask`&#40;`unformatted_value` BIGINT, `format_string` CHAR&#40;32&#41;&#41; RETURNS char&#40;1&#41; CHARSET utf8
    DETERMINISTIC
BEGIN

# Example&#58;
# SELECT mask&#40;123456789,'###-##-####'&#41;;
# SELECT mask&#40;123456789,'&#40;###&#41; ###-####'&#41;;
# SELECT mask&#40;123456789,'###-#!##@&#40;###&#41;'&#41;;

# Declare variables
DECLARE input_len TINYINT;
DECLARE output_len TINYINT;
DECLARE temp_char CHAR;

# Initialize variables
SET input_len = LENGTH&#40;unformatted_value&#41;;
SET output_len = LENGTH&#40;format_string&#41;;

# Construct formated string
WHILE &#40; output_len > 0 &#41; DO

SET temp_char = SUBSTR&#40;format_string, output_len, 1&#41;;
IF &#40; temp_char = '#' &#41; THEN
IF &#40; input_len > 0 &#41; THEN
SET format_string = INSERT&#40;format_string, output_len, 1, SUBSTR&#40;unformatted_value, input_len, 1&#41;&#41;;
SET input_len = input_len - 1;
ELSE
SET format_string = INSERT&#40;format_string, output_len, 1, '0'&#41;;
END IF;
END IF;

SET output_len = output_len - 1;
END WHILE;

RETURN format_string;
END$$
DELIMITER ;
2) IPv6 для нетапа

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

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `net_ntoa_ipv6`&#40;ip BIGINT, ip_ext BIGINT&#41; RETURNS char&#40;48&#41; CHARSET utf8 COLLATE utf8_unicode_ci
    DETERMINISTIC
BEGIN
  # Example&#58; SELECT net_ntoa_ipv6&#40;ip,ip_ext&#41; FROM UTM5.ip_groups;
  SELECT 
    LOWER&#40;
      CONCAT&#40;
        IF &#40;
          ip_ext != 0,
          CONCAT_WS&#40;
            '&#58;',
            SUBSTR&#40;LPAD&#40;HEX&#40;ip_ext&#41;, 16, '0'&#41;, 1, 4&#41;,
            SUBSTR&#40;LPAD&#40;HEX&#40;ip_ext&#41;, 16, '0'&#41;, 5, 4&#41;,
            SUBSTR&#40;LPAD&#40;HEX&#40;ip_ext&#41;, 16, '0'&#41;, 9, 4&#41;,
            RIGHT&#40;LPAD&#40;HEX&#40;ip_ext&#41;, 16, '0'&#41;, 4&#41;
          &#41;,
          ""
        &#41;,
        IF &#40;ip_ext != 0, '&#58;', ""&#41;,
        IF &#40;
          ip_ext != 0,
          CONCAT_WS&#40;
            '&#58;',
            SUBSTR&#40;LPAD&#40;HEX&#40;ip&#41;, 16, '0'&#41;, 1, 4&#41;,
            SUBSTR&#40;LPAD&#40;HEX&#40;ip&#41;, 16, '0'&#41;, 5, 4&#41;,
            SUBSTR&#40;LPAD&#40;HEX&#40;ip&#41;, 16, '0'&#41;, 9, 4&#41;,
            RIGHT&#40;LPAD&#40;HEX&#40;ip&#41;, 16, '0'&#41;, 4&#41;
          &#41;,
          INET_NTOA&#40;ip & 0xffffffff&#41;
        &#41;
      &#41;
    &#41; AS result INTO @result ;
  RETURN @result ;
END$$
DELIMITER ;

Fiendflug
Сообщения: 6
Зарегистрирован: Сб окт 08, 2011 18:13

Как удалить ошибчно залитый через send_cdr телефонный трафик

Сообщение Fiendflug »

Парни, здравствуйте. Подскажите как sql - запросом удалить весь залитый телефонный трафик за период времени, месяц к примеру.

Jonson
Сообщения: 150
Зарегистрирован: Ср фев 02, 2005 21:48
Откуда: МО, Ногинский р-он

Сообщение Jonson »

Подскажите, можно ли в 5.3-004 сделать выборку абонентов, у которых в предыдущем месяце была подключена услуга с id=1?

Если да, то как?

Аватара пользователя
Magnum72
Сообщения: 1947
Зарегистрирован: Чт сен 22, 2005 06:54
Контактная информация:

Сообщение Magnum72 »

Jonson писал(а):Подскажите, можно ли в 5.3-004 сделать выборку абонентов, у которых в предыдущем месяце была подключена услуга с id=1?

Если да, то как?
SQL выборку или через админку?

Jonson
Сообщения: 150
Зарегистрирован: Ср фев 02, 2005 21:48
Откуда: МО, Ногинский р-он

Сообщение Jonson »

Magnum72 писал(а):
Jonson писал(а):Подскажите, можно ли в 5.3-004 сделать выборку абонентов, у которых в предыдущем месяце была подключена услуга с id=1?

Если да, то как?
SQL выборку или через админку?

SQL

Аватара пользователя
Magnum72
Сообщения: 1947
Зарегистрирован: Чт сен 22, 2005 06:54
Контактная информация:

Сообщение Magnum72 »

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

SELECT
  us.id,
  us.full_name
FROM
  UTM5.users AS us,
  UTM5.service_links AS sl,
  UTM5.periodic_service_links AS psl
WHERE 1
  AND us.basic_account = sl.account_id
  AND sl.id = psl.id
  AND sl.service_id = 1
  AND psl.start_date BETWEEN UNIX_TIMESTAMP&#40;'2017-04-01 00&#58;00&#58;00'&#41; AND UNIX_TIMESTAMP&#40;'2017-04-30 23&#58;59&#58;59'&#41;;

Jonson
Сообщения: 150
Зарегистрирован: Ср фев 02, 2005 21:48
Откуда: МО, Ногинский р-он

Сообщение Jonson »

Magnum72, спасибо за ответ.

Но я не правильно сформулировал вопрос, меня интересует у кого в предыдущем месяце ИСПОЛЬЗОВАЛАСЬ услуга с id=1, т.е. она могла быть подключена пользователю и в прошлом месяце и два и три месяца назад - для этого можно заменить время на psl.start_date < UNIX_TIMESTAMP('2017-03-31 23:59:59');

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

Закрыто