Выгрузка клиентов с балансом счетов из УКМ : УКМ-4

m1n1mal · █ 13.06.2018 11:55

Здравствуйте.
Стандартной выгрузки информации по клиентам и их счетам в УКМ нет. На форуме тоже не нашел, возможно проглядел.
В силу своих знаний, сформировал запрос который выводит информацию по клиенту и его состоянию бонусных счетов.
Карты и свойства клиентов пока не выгружаю. На текущий момент, нет понимания как решить задачу со структурой, потому как если карт или свойств более одного, строки по клиентам увеличиваются на их количество, что приводит к большому излишку данных. Т.е. нет понимания, как каждую карту или свойство выводить в отдельной колонке, не создавая лишних строк.
В текущей версии скрипта, лишние строки будут присутствовать если у клиента более одного активного счета.
Скрипт выбирает только тех клиентов, у которых есть активные счета.
Что получается сейчас:
+-------+---------+--------+
|Клиент|Счет......|Баланс.|
+-------+---------+--------+
|Вася...|Счет №1|1000....|
+-------+---------+--------+
|Вася...|Счет №2|20000..|
+-------+---------+--------+
|Коля...|Счет №1|10000..|
+-------+---------+--------+

Что хотелось бы получить:
+-------+---------+---------+
|Клиент|Счет №1|Счет №2|
+-------+---------+---------+
|Вася...|1000......|20000...|
+-------+---------+---------+
|Коля...|10000....|0..........|
+-------+---------+---------+

Сам запрос:

SQL код:

 SELECT `trm_in_classifclients`.`name` 'КЛАССИФИКАТОР КЛИЕНТОВ'
, `trm_in_clients`.`sur_name` AS 'ФАМИЛИЯ'
, `trm_in_clients`.`name` AS 'ИМЯ'
, `trm_in_clients`.`patronymic` AS 'ОТЧЕСТВО'
, `trm_in_clients`.`birthday` AS 'ДАТА РОЖДЕНИЯ'
, `trm_in_clients`.`id` AS 'ID КЛИЕНТА'
, `trm_in_account_type`.`name` AS 'СЧЕТ'
, `local_auth_account_journal`.`balance` AS 'БАЛАНС'
, CASE WHEN `trm_in_clients`.`active`=1 THEN 'ДА' WHEN `trm_in_clients`.`active`=0 THEN 'НЕТ' END AS 'АКТИВЕН'
, CASE WHEN `trm_in_clients`.`deleted`=1 THEN 'ДА' WHEN `trm_in_clients`.`deleted`=0 THEN 'НЕТ' END AS 'УДАЛЕН'
FROM `local_auth_account_journal`, `local_auth_account`, `trm_in_clients`, `trm_in_account_type`, trm_in_classifclients
WHERE `local_auth_account_journal`.`id`=(SELECT MAX(`local_auth_account_journal`.`id`) FROM `local_auth_account_journal` WHERE `local_auth_account_journal`.`account_id`=`local_auth_account`.`id`)
AND `trm_in_clients`.`id`=`local_auth_account`.`params`
AND `local_auth_account`.`account_type_id`=`trm_in_account_type`.`id`
AND `trm_in_clients`.`classifclient`=`trm_in_classifclients`.`id`
ORDER BY `trm_in_classifclients`.`name`, `trm_in_clients`.`sur_name`;

Возможно более опытные коллеги "допилят" скрипт, или поделятся идеями как это можно реализовать. Варианты предложенные в интернете пробовал, но что-то не выходит.

P.S. Текущий вариант сегодняшнюю мою задачу решает. Возможно и другим этот скрипт будет полезен.
P.P.S. "Батник" с формированием файла доступен по ссылке https://storage.olegon.ru/supermag/У...cliens_v0.1.7z

Mtirt · █ 13.06.2018 12:05

Цитата:

m1n1mal ➤ Здравствуйте.
Стандартной выгрузки информации по клиентам и их счетам в УКМ нет.

Есть.

m1n1mal · █ 13.06.2018 12:22

Цитата:

Mtirt ➤ Есть.

Суть была не в том, чтобы выгрузить все проводки по клиенту, а в том, чтобы выгрузить самого клиента и его состояние бонусного счета (итоговый баланс, не транзакции).
А конвертер по ссылке, выгружает ID клиента и все его транзакции.

Starter · █ 13.06.2018 12:41

Сдаётся мне, задачу можно сформировать так: хочу из плоской таблицы получить кросс таблицу.

Исходя из этого, уже и нужно подбирать решения, тут всё зависит от того, для чего и насколько часто эта информация требуется. Может быть, достаточно будет средств экселя (если пользователь искушенный и данные требуются раз-два в месяц).
Если данные используются для дальнейшего анализа (в том же 1С), то, может быть, проще будет в 1С сделать такое преобразование ()
Если для веба, то где то встречал код на PHP для такой цели.
Либо искать утилиту для преобразования плоских таблиц в кросс таблицу.

vdm · █ 13.06.2018 13:04

Цитата:

m1n1mal ➤ состояние бонусного счета (итоговый баланс, не транзакции)

Если количество видов счетов ограничено (известны их id) - баланс по каждому счету из local_auth_account_journal можно взять встроенной функцией.
Только на большом количестве счетов оно небыстро будет.

Код:

SELECT ...
       acc_get_current_balance(ac1.id) balance1,
       acc_get_current_balance(ac2.id) balance2,
       ...
  FROM trm_in_clients c
   LEFT JOIN local_auth_account ac1
     ON c.global_id=0 AND c.id=ac1.params AND ac1.account_type_id=@account_type1
   LEFT JOIN local_auth_account ac2
     ON c.global_id=0 AND c.id=ac2.params AND ac2.account_type_id=@account_type2
...

m1n1mal · █ 13.06.2018 14:14

vdm,
Баланс по счетам клиента я вывожу, с этим проблем не возникло.
У меня проблема именно в том, как выше написал Starter, у меня не получается сформировать кросс-таблицу на финише.
Вот собственно проблема с лишними строками:

(0,05Мб)

vdm · █ 13.06.2018 14:49

А я про то, что баланс каждого счета можно получить "горизонтально", если количество видов счетов фиксировано/ограничено.
Хотя и при изменяющемся списке счетов можно динамически формировать список полей в запросе.

Starter · █ 13.06.2018 22:57

Вот еще нагугленный пример: