Подсчёт баланса пользователя в срезке даты?

Question

[Tim Tim]

Подскажите пожалуйста, как подсчитать правильно баланс пользователя.

Таблица всех начислений / списаний хранится в следующем виде:



Status:
1 - начислено
2 - списано
3 - отменено

Paid (bool) - заказ оплачен

К примеру, начислили бонус за регистрацию +100 бонусов с сроком годности 5 дней,
далее он списал 10% от суммы заказа -10 бонусов и ему с этого заказа вернётся кэшбек в 5% / 5 бонусов.
Бонусный счёт его будет равен 95 бонусов.

Всё хорошо, но, если у приветственных бонусов (100) выйдет срок действия, то подсчёт будет в минус, а должно остаться 5 бонусов и 90 не учитываться.
Подсчёт:

$paid_1 = Db::getInstance()->getValue('
            SELECT SUM(points)
            FROM '._DB_PREFIX_.'bonus_account 
            WHERE id_customer = '.(int)$id_customer.'
            AND paid = 1
            AND status = 1
            AND date_add <= "'.date('Y-m-d H:i:s').'"
            AND date_bonus_to >= "'.date('Y-m-d H:i:s').'"');
 $paid_0 = Db::getInstance()->getValue('
            SELECT SUM(points)
            FROM '._DB_PREFIX_.'bonus_account 
            WHERE id_customer = '.(int)$id_customer.'
            AND paid = 1
            AND status = 2
            AND date_add <= "'.date('Y-m-d H:i:s').'"
            AND date_bonus_to >= "'.date('Y-m-d H:i:s').'"');
        $end_result = ((float)$paid_1 - (float)$paid_0);
        if ($end_result < 0) {
            return 0;
        } else {
            return $end_result;
        }

Тестовые данные Создание базы данных

CREATE TABLE `ps_bonus_account` (
  `id_bonusaccount` int(11) NOT NULL,
  `id_customer` int(10) UNSIGNED NOT NULL,
  `id_order` int(10) UNSIGNED NOT NULL,
  `id_cart_rule` int(10) UNSIGNED NOT NULL,
  `points` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `comment` text NOT NULL,
  `status` int(10) UNSIGNED NOT NULL,
  `paid` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `type` varchar(20) NOT NULL,
  `date_add` datetime DEFAULT NULL,
  `date_bonus_to` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ps_bonus_account` (`id_bonusaccount`, `id_customer`, `id_order`, `id_cart_rule`, `points`, `comment`, `status`, `paid`, `type`, `date_add`, `date_bonus_to`) VALUES
(218, 518, 278, 50, '5.000000', 'Бонусы за заказ', 1, 1, 'order', '2021-11-09 21:45:16', '2022-12-09 21:45:16'),
(219, 518, 278, 50, '10.000000', 'Оплата заказа', 2, 1, 'order', '2021-11-09 21:45:17', '2022-12-09 21:45:16'),
(220, 518, 0, 0, '100.000000', 'За регистрацию', 1, 1, 'no_order', '2021-11-09 21:57:40', '2021-11-08 21:57:40');

Как правильно сделать подсчёт с учётом сгоревших бонусов?
Спасибо!

Answer 1

[Slava Rozhnev]

SELECT
	GREATEST(0, -- return 0 in case negative balance
		SUM(
			IF(`status` = 1 AND `date_bonus_to` > NOW() , `points`, 0) -- bonus added and not expired
			- IF(`status` = 2, `points`, 0) -- bonus used
		)
	) `balance`
FROM `ps_bonus_account`
WHERE  
	`id_customer` = 518 AND
	`paid` = 1 
GROUP BY `id_customer`;

SQL fiddle

Comments

[Tim Tim]

Спасибо за ответ, но баланс получился 0 а должен быть 5 так как только 90 сгорает

[Slava Rozhnev]

В качестве головоломки :)

SELECT 
	`ps_bonus_account`.`id_customer`,
	SUM(IF(`status` = 1, `points`, - `points`) -
	IF(
	  `status` = 1 AND `date_bonus_to` < NOW(),
	  GREATEST(0, `ps_bonus_account`.`points` - (
			SELECT SUM(`points`)
			FROM `ps_bonus_account` `used`
			WHERE  
			  `used`.`id_customer` = `ps_bonus_account`.`id_customer` AND `status` = 2 AND
			  `paid` = 1 AND `used`.`date_add` < `ps_bonus_account`.`date_bonus_to`
	  )), 
	  0)) `balance`
FROM `ps_bonus_account`
WHERE  
	`id_customer` = 518 AND
	`paid` = 1 
GROUP BY `ps_bonus_account`.`id_customer`	
;

fiddle

[Tim Tim]

Slava Rozhnev, Вы гений, спасибо огромное!

[Tim Tim]

К сожалению не работает, пожалуйста посмотрите, стоило просто поменять местами
https://sqlize.online/?phpses=null&sqlses=bdec80e4...

[Slava Rozhnev]

А мне кажется всё верно.
- первый 'Бонус за регистрацию' сгорел '2021-11-09 00:00:00' - раньше чем был использован
- остается 1.355200 - 2.150000 = -0.794800

[Tim Tim]

Спасибо, изменил, все равно не правильно считает, почему 9 баллов
https://sqlize.online/?phpses=null&sqlses=af127eb4...

[Slava Rozhnev]

Tim2345, Потому что 10 + 1.355200 - 2.150000 = 9.205200
https://sqlize.online/?phpses=null&sqlses=bcef8b2f...

[Tim Tim]

Slava Rozhnev, А если 10 бонусов сгорит, то должно остаться 1.35, в этом и был смысл, что бонусы сгорают и не должны учитываться.
https://sqlize.online/?phpses=null&sqlses=35ef8fa9...

Answer 2

[Дмитрий Энтелис]

Вообще это выглядит как плохая и неочевидная архитектура.

Начисление/списание - это факт который должен происходить в транзакции в конкретный момент времени.
Если у Вас есть "протухающие" бонусы - соответственно нужно по крону с пользователей их вычитать, создавая новые записи в момент протухания.

Если уж совсем никак - надо добавить в текущую логику 3й запрос: количество протухших бонусов

select sum(points) from ... where date_end < now() and points > 0

Текущие бонусы = начисленные - потраченные - протухшие.

Comments

[Tim Tim]

Спасибо, но это уводит в минус тоже

[Дмитрий Энтелис]

Tim2345, я вижу вы обновили вопрос, но по прежнему не понимаю вашу структуру данных.

Что такое поля status и paid ?
Как выглядит в таблице запись о трате бонусов на заказ?

[Tim Tim]

Status:
1 - начислено
2 - списано
3 - отменено

Paid (bool) - заказ оплачен