Бонусная система со сгоранием бонусов, как нужно делать ?

ksnk

прохожий
Таким образом, получается, что в одну таблицу не уложить. Должен быть лог операций (источник sku для приходных операций) и приходно-расходная ведомость для каждого прихода из примера @WMix.
Каждый расход (покупка или списание) будет вызывать добавление, возможно, нескольких записей об изменении каждого отдельного прихода.
 

ksnk

прохожий
Тем не менее, пока не считаю, что в одну таблицу уложиться нельзя :)
Для того, чтобы посмотреть более конкретно - вот песочница в db-fiddle https://www.db-fiddle.com/f/b2bQKJnFvhgyAWLb1Pi9ud/2
База там, очевидно, без ключей и типы странно подобраны, внимания на это обращать не нужно. Макет - он такой какой получился после импорта таблички в фидл.
Однако, план "сгорания" всех несгоревших бонусов там расчитывается корректно, imho.
Если фидл не открывается - вот схема
SQL:
CREATE TABLE bonus_log
    (`id` int, `user` varchar(4), `reason` varchar(10), `account` int,
     `created` DATETIME, `expired` DATETIME, `link` int, `description` varchar(40))
;
INSERT INTO bonus_log
(`id`, `user`, `reason`, `account`, `created`, `expired`, `link`, `description`)
VALUES
    (1, 'user', 'income', 20, '2022/02/01', NULL, NULL, 'present 20'),
    (50, 'user', 'income', 20, '2022/02/20', '2022/03/20', NULL, 'period'),
    (75, 'user', 'buying', -28, '2022/02/28', NULL, NULL, 'buying 18'),
    (100, 'user', 'income', 10, '2022/03/01', '2022/04/01', NULL, 'period'),
    (101, 'user', 'income', 10, '2022/03/01', '2022/04/01', NULL, 'period'),
    (102, 'user', 'income', 10, '2022/03/01', '2022/04/01', NULL, 'period'),
    (103, 'user', 'income', 10, '2022/03/01', '2022/04/01', NULL, 'period'),
    (104, 'user', 'burning', 0, '2022/03/20', NULL, 50, 'burn 20'),
    (105, 'user', 'buying', -28, '2022/03/21', NULL, NULL, 'buying 22'),
    (106, 'user', 'burning', -10, '2022/04/01', NULL, 100, 'burn2'),
    (107, 'user', 'burning', -2, '2022/04/01', NULL, 101, 'burn0'),
    (108, 'user', 'burning', -0, '2022/04/01', NULL, 102, 'burn0'),
    (109, 'user', 'burning', -0, '2022/04/01', NULL, 103, 'burn0'),
    (110, 'user', 'income', 10, '2022/04/02', '2022/05/02', NULL, 'period')
;
и набор тестовых запросов
SQL:
-- select @date := '2022/05/02'; -- 3 жег
-- select @date := '2022/04/02'; -- второй жег+1день
select @date := '2022/04/01' ; -- второй жег
-- select @date := '2022/03/21' ; -- первый жег+1день
-- select @date := '2022/03/20' ; -- первый жег
-- select @date := '2022/02/19' ; -- начало времен
-- select @date := '2022/01/19' ; -- до начала времен

-- считаем минимальную дату несгоревшего прихода (2 почти одинаковых запроса подряд)
select  @date2:=min(x.created),@summ:=sum(x.account) from bonus_log  x
left join bonus_log y on y.reason='burning' and  y.link=x.id
and y.created<@date
where x.reason='income' and x.expired<[email protected]
  and y.id is null
  order by x.created, x.id;
-- сумма трат, которая должна сгореть, по дате
select @toburn:= @summ+COALESCE(sum(x.account),0) from bonus_log  x
where x.reason="buying" and x.created<@date and x.created>[email protected] ;

-- все сгорающие в этот день приходы на дату
select x.user, 'burning' as reason ,IF(@toburn<x.account,[email protected],-x.account ) as account, x.expired as created,NULL as expired, x.id as link, IF(@toburn<x.account, @toburn:=0, @toburn:[email protected] ) as tmp from bonus_log  x
left join bonus_log y on y.reason='burning' and  y.link=x.id
and y.created<@date
where x.reason='income' and x.expired<[email protected]
  and y.id is null
  order by x.created, x.id;

--
select CONCAT(' всего в кошельке по дате:', @date) `+`, COALESCE(sum(`account`),0) as total from bonus_log where created<@date;
Результат, для даты расчета сжигания 4 одновременных выплат - 2022-04-01

Код:
| user | created             | link | reason  | account                            | expired | tmp                              |
| ---- | ------------------- | ---- | ------- | ---------------------------------- | ------- | -------------------------------- |
| user | 2022-04-01 00:00:00 | 100  | burning | -10.000000000000000000000000000000 |         | 2.000000000000000000000000000000 |
| user | 2022-04-01 00:00:00 | 101  | burning | -2.000000000000000000000000000000  |         | 0.000000000000000000000000000000 |
| user | 2022-04-01 00:00:00 | 102  | burning | 0.000000000000000000000000000000   |         | 0.000000000000000000000000000000 |
| user | 2022-04-01 00:00:00 | 103  | burning | 0.000000000000000000000000000000   |         | 0.000000000000000000000000000000 |


| +                                    | total |
| ------------------------------------ | ----- |
|  всего в кошельке по дате:2022/04/01 | 24    |
P.S. Понятною что на php не нужно писать 2 одинаковых запроса подряд, и/или использовать встроенные переменные, но так фидл все это сам может посчитать и показать.
 

флоппик

promotor fidei
Команда форума
Партнер клуба
Можно, но непонятно, зачем, что, таблицы админ под роспись выдает, или что-то? Соревнование кто сильнее себе усложнит работу?
 

Valick

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

ksnk

прохожий
Можно, но непонятно, зачем, что, таблицы админ под роспись выдает, или что-то? Соревнование кто сильнее себе усложнит работу?
Ощущение, что делаются ненужные телодвижения.
Зачем ходить только одной дорогой, вдруг есть тропинка короче и быстрее?
Спортивный интерес, в конце концов. :)
Впрочем, в моем случае вижу ошибку в логике вычислений, если сильно перемешать оплаты-списания и сгорания по логу (3+ вложения)...
 

WMix

герр M:)ller
Партнер клуба
(`id`, `user`, `reason`, `account`, `created`, `expired`, `link`, `description`)
а где баланс? линк это у тебя связка на приходы?
делить надо на "бонус (1, 'за регистрацию', 10, '100d' , 'any-users', 'registerEvent')" причем она у тебя наверняка есть и на приход/расход это лог.
а так ты в эксель играешь
 

Valick

Новичок
Вот теперь знаю чем заняться после баловства с корзиной заказов :) буду баловться со сгоранием бонусов.
Сегодня утром скинули в чат.
 
Последнее редактирование:

ksnk

прохожий
а где баланс? линк это у тебя связка на приходы?
Баланс, как таковой, не хранится. Баланс - это сумма всей истории. Линк - да, завязка на приход, но только для операций сгорания. Они же сигнализируют, что этот приход "можно больше не сжигать".
 

ksnk

прохожий
Ну, задача @WMix - это задача складского учета, когда есть пара коробок шурупов, по 100 штук и пара коробок гвоздей. И их нужно работягам раздавать каждый день и новые коробки прикупать... А тут все баллы единообразны, хотя некоторые тухнут со временем... В общем - немного другая задача.
 

Valick

Новичок
ksnk, а вот и не совсем так. С баллами нельзя оперировать общей суммой для вычислений, с конкретным начислением необходимо работать отдельно. Лично у меня сложилась некая концепция того как надо это делать. Скажу лишь, что рядом с полем начисленных баллов, необходимо держать поле с информацией о том сколько из этих конкретных баллов уже списано.
 

Valick

Новичок
Да еще к этой записи придётся прикрутить таблицу с основанием списания (и связь там нужна многие ко многим), так как например единожды начисленные 1000 бонусов можно списать за 1 раз, а можно за (1...n) раз.
Так что уже минимум три таблицы 😉
 
Последнее редактирование:

ksnk

прохожий
таблицу с основанием списания
Ну это же тот самый лог и есть. Можно навернуть description как поле json и пихать туда любой объем сопроводительной информации. Хотя, по сути, достаточно только хранить ссылку на заказ в корзине товаров.
И вопрос ЗАЧЕМ настолько плотно хранить конкретные источники и списания конкретных денег? Деньги складывать в кошелек, а списывать можно по понятным алгоритмам, без хранения частичных сумм в каждом конкретном приходе.
 

ksnk

прохожий
например единожды начисленные 1000 бонусов можно списать за 1 раз, а можно за (1...n) раз.
Я про вот это. Зачем нужно знать, что на эту конкретную оплату потрачены вот конкретно такие и такие начисления, если их можно вычислить и учесть к моменту сгорания ? Смотреть их пользователю нафиг не нужно. Для отладки алгоритма разве что...
Опять же, в корзине товаров, в форме оплаты будет написано "оплачено бонусами". Без всяких ссылок. Ну или с ссылкой на строку лога с информацией о списании. Таким образом концы находятся с лишней связи не требуется

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

WMix

герр M:)ller
Партнер клуба
Баланс, как таковой, не хранится. Баланс - это сумма всей истории. Линк - да, завязка на приход, но только для операций сгорания. Они же сигнализируют, что этот приход "можно больше не сжигать".
понимаем что либо хранить всю таблицу либо писать хитровы... алгоритмы с подсчетом баланса для архивирования записей,
манипуляции записей сложнее избежать,
статистику сложно вести,...
и так сложность лога высока, а ты еще рекурсии изначально планируешь

и честно, лучше in/out soll/haben приход/уход чем, "сгорело -5 пунктов", это как бы не православно. правильней "сгорело 5 пунктов"
 
Последнее редактирование:

ksnk

прохожий
Очередной вариант проекта, уже более похож на рабочий.
Сначала - хотелки
Хочется в коде, в любом нужном месте иметь возможность нужному юзеру дописать-списать некоторое количество бонусов.
Хочется в личном кабинете пользователявилеть строку "у вас прямо сейчас 300 баллов".
Хочется в личном кабинете видеть строку - "завтра ровно в 9:00 ваши 200 баллов превратятся в тыкву".
Хочется иметь возможность видеть историю количества бонусов на месяц-полгода-ets (не обязательно)
В админке нужен лог начислений-оплат-сгораний бонусов по юзерам, чтобы оператор мог убедительно ответить на вопрос юзера - где мои деньги.
Раз в день автоматически, непотраченные бонусы должны сгорать по истечении срока годности.
Больше, в общем то ничего пока не нужно...

Проектируем...
  • Лог. Просто лог. Особого сакрального смысла в нем нет, но этот лог будет выводится оператору в админке. Он должен быть полным и убедительным. От лога, внутри системы, будет использоваться номер строки лога - log.id. По этому индексу оператор может извлечь какую то историю телодвижений системы.
  • Склад - примерно как и предлагает @WMix.
    • Таблица ящиков на складе sku{articul, user, key} - ящик для товара articul, у меня пока единственный - "баллы бонусной системы", но вдруг придумаются еще какие-нибудь фантики или настоящие деньги..., принадлежит пользователю, key - строковый ключ, в нашем случае будет иметь строковое значение даты срока годности, но это пока не точно.
    • Склад. {sku, in, out, bilance, created, note, type,key}. Содержимое ящиков. Сама таблица устроена как лог, все записи только добавляются. В самом последней по дате записи про этот ящик хранится его состояние. поля note, type и key служать для привязки внешних сущьностей к операциям на складе. note - текстовая запись, type=='log' значит key указывает на строку лога операций.
  • Таблица сроков годности товара в ящиках - {sku, expired} .

В момент, когда системе приходит в голову добавить кому-то бонусов (user, amount, expired)
- дополняется строка таблицы логов ( запоминаем log.id)
- Ищем нужный ящик на складе. Если нет - добавляем. {articul:"бонусы", user:user, key:expired} запоминаем номер ящика на складе - sku.
- вычиcляем количество товаров ящика и добавляем запись об изменении количества {sku:sku, in:amount, out:0, bilance: +=amount, created:now, note: 'добавлено, на основании ${log.id}', type:'log', key:log.id}
если указан срок годности - добавляем запись в таблицу сроков годности. {sku:sku, expired:expired}

Вывод состояния счета пользователя
- находим все ящики пользователя и суммируем количество

Вывод строки "через 2 дня все сгорит"
- выводим список непустых ящиков, с угрожаемым (в пределах 1месяца) временем сгорания. Находим самый близкий по времени, считаем количеств, считаем количество всех ящиков, выводим - "через 3 часа сгорит 100 баллов, а через 30 дней сгорит еще 100500 баллов. торопись..."

Сгорание бонусов
- ищем все непустые ящики, которые должны сгореть по исчерпании времени хранения - получаем {sku, total}
- добавляем "обнуление" - {sku:sku, in:0, out:total, bilance: 0, created:now, note: 'сгорело, время хранения исчерпано', type:'autoburn'}

Организация склада в виде лога позволяет нам увидеть состояние всех ящиков склада на любую дату со сроками годности.

Итого получается 4 таблицы. Можно бы и 5, если артикулы товаров внезапно размножатся, но в таком случае, таблица товаров в системе и так уже где-то есть...
 
Последнее редактирование:
Сверху