php - 从钱包系统计算可用余额，不包括过期信用

Question

在 credits 表中我有

(id, user_id, process, amount, date_add, date_exp, date_redeemed, remark)

SELECT * FROM credits WHERE user_id = 2;

+----+---------+---------+--------+------------+------------+---------------+----------+
| id | user_id | process | amount |  date_add  |  date_exp  | date_redeemed |  remark  |
+----+---------+---------+--------+------------+------------+---------------+----------+
| 22 |       2 | Add     | 200.00 | 2018-01-01 | 2019-01-01 |               | Credit1  |
| 23 |       2 | Add     | 200.00 | 2018-03-31 | 2019-03-31 |               | Credit2  |
| 24 |       2 | Deduct  | 200.00 |            |            | 2018-04-28    | Redeemed |
| 25 |       2 | Add     | 200.00 | 2018-07-11 | 2018-10-11 |               | Campaign |
| 26 |       2 | Deduct  | 50.00  |            |            | 2018-08-30    | Redeemed |
| 27 |       2 | Add     | 200.00 | 2018-10-01 | 2019-09-30 |               | Credit3  |
| 28 |       2 | Deduct  | 198.55 |            |            | 2018-10-20    | Redeemed |
+----+---------+---------+--------+------------+------------+---------------+----------+

我写的下面查询只会计算余额，但我不知道信用是否已过期以及是否在过期前使用。

SELECT 
    u.id,
    email,
    CONCAT(first_name, ' ', last_name) AS name,
    type,
    (CASE
        WHEN (SUM(amount) IS NULL) THEN 0.00
        ELSE CASE
            WHEN
                (SUM(CASE
                    WHEN process = 'Add' THEN amount
                END) - SUM(CASE
                    WHEN process = 'Deduct' THEN amount
                END)) IS NULL
            THEN
                SUM(CASE
                    WHEN process = 'Add' THEN amount
                END)
            ELSE SUM(CASE
                WHEN process = 'Add' THEN amount
            END) - SUM(CASE
                WHEN process = 'Deduct' THEN amount
            END)
        END
    END) AS balance
FROM
    users u
        LEFT JOIN
    credits c ON u.id = c.user_id
GROUP BY u.id;

还是我做错了？也许我应该在后端而不是 SQL 中完成计算？

编辑 1:

我想计算每个用户电子钱包的余额，但是信用会过期，

如果它已过期且未兑换，则从余额中排除

ELSE IF 在过期前使用 AND 兑换金额 < 过期金额 THEN (余额 - (到期金额 - 赎回金额))

ELSE IF 在过期前使用 AND redeem amount > expire amount THEN 可用余额将被扣除，因为过期金额不足以扣除赎回金额

编辑 2:

上面的查询将输出 351.45，我的预期输出是 201.45。 2018-08-30赎回金额低于过期金额不计算赎回

编辑 3:

用户表:

+----+------------+-----------+----------+----------------+----------+
| id | first_name | last_name |   type   |     email      | password |
+----+------------+-----------+----------+----------------+----------+
|  2 | Test       | Oyster    | Employee | test@gmail.com | NULL     |
+----+------------+-----------+----------+----------------+----------+

我的输出:

+----+----------------+-------------+----------+---------+
| id |     email      |    name     |   type   | balance |
+----+----------------+-------------+----------+---------+
|  2 | test@gmail.com | Test Oyster | Employee |  351.45 |
+----+----------------+-------------+----------+---------+

预期输出:

总计(200+200+200)600

赎回金额448.55(200+50+198.55)

余额为151.45

+----+----------------+-------------+----------+---------+
| id |     email      |    name     |   type   | balance |
+----+----------------+-------------+----------+---------+
|  2 | test@gmail.com | Test Oyster | Employee |  151.45 |
+----+----------------+-------------+----------+---------+

Answer 1

您当前的表格存在基本的结构问题。所以我会建议对表结构和随后的应用程序代码进行一些更改。钱包系统的表结构可以非常详细；但我会在这里建议尽可能少的更改。 我并不是说这是理想的方式；但它应该可以工作。最初，我将列出当前方法存在的一些问题。

问题:

• 如果有多个尚未过期的积分可用怎么办？
• 在这些可用积分中，有些可能实际上已经被使用，但尚未过期。我们如何忽略它们以获得可用余额？
• 此外，有些可能已被部分利用。我们如何计算部分利用率？
• 可能存在这样一种情况，即赎回金额跨越多个未过期的信用额度。有些可能会被部分利用；而有些可能会得到充分利用。

一般做法:

我们通常遵循FIFO(先进先出)的方法，为客户提供最大利益。因此，较早的信用额度(更有可能在未使用的情况下过期)首先被利用。

为了遵循 FIFO，我们每次都必须在查询/应用程序代码中有效地使用循环技术，以计算基本的东西，例如“可用钱包余额”、“过期和未充分利用的信用”等。为此编写查询将很麻烦，而且在更大的规模下可能效率低下

解决方案:

我们可以在您当前的表格中再添加一列amount_redeemed。它基本上代表已经兑现特定信用额度的金额。

ALTER TABLE credits ADD COLUMN amount_redeemed DECIMAL (8,2);

因此，填充的表格如下所示:

+----+---------+---------+--------+-----------------+------------+---------------+---------------+----------+
| id | user_id | process | amount | amount_redeemed |  date_add  |  date_exp     | date_redeemed |  remark  |
+----+---------+---------+--------+-----------------+------------+---------------+---------------+----------+
| 22 |       2 | Add     | 200.00 |      200.00     | 2018-01-01 | 2019-01-01    |               | Credit1  |
| 23 |       2 | Add     | 200.00 |      200.00     | 2018-03-31 | 2019-03-31    |               | Credit2  |
| 24 |       2 | Deduct  | 200.00 |                 |            |               | 2018-04-28    | Redeemed |
| 25 |       2 | Add     | 200.00 |      0.00       | 2018-07-11 | 2018-10-11    |               | Campaign |
| 26 |       2 | Deduct  | 50.00  |                 |            |               | 2018-08-30    | Redeemed |
| 27 |       2 | Add     | 200.00 |      48.55      | 2018-10-01 | 2019-09-30    |               | Credit3  |
| 28 |       2 | Deduct  | 198.55 |                 |            |               | 2018-10-20    | Redeemed |
+----+---------+---------+--------+-----------------+------------+---------------+---------------+----------+

请注意，id = 25 的 Credit 的 amount_redeemed 是 0.00，使用 FIFO 方法。它在2018-10-20获得了一次赎回机会，但到那时它已经过期了(date_exp = 2018-10-11)

所以，现在一旦我们有了这个设置，您就可以在您的应用程序代码中执行以下操作:

1. 在表的现有行中填充 amount_redeemed 值:

这将是一次性事件。为此，制定单个查询将很困难(这就是我们首先在这里的原因)。因此，我建议您使用循环和 FIFO 方法在您的应用程序代码(例如:PHP)中执行一次。查看下面的第 3 点，了解如何在应用程序代码中执行此操作。

1. 获取当前可用余额:

查询现在变得微不足道了，因为我们只需要计算所有 Add 进程的 amount - amount_redeemed 的总和，这些进程还没有过期。

SELECT SUM(amount - amount_redeemed) AS total_available_credit
FROM credits 
WHERE process = 'Add' AND 
      date_exp > CURDATE() AND 
      user_id = 2

1. 兑换时更新amount_redeemed:

在此，您首先可以得到所有可用的Credits，这些Credits有可兑换的金额，并且还没有过期。

SELECT id, (amount - amount_redeemed) AS available_credit 
FROM credits 
WHERE process = 'Add' AND 
      date_exp > CURDATE() AND 
      user_id = 2 AND 
      amount - amount_redeemed > 0
ORDER BY id

现在，我们可以循环上面的查询结果，并相应地使用金额

 // PHP code example

 // amount to redeem
 $amount_to_redeem = 100;

 // Map storing amount_redeemed against id
 $amount_redeemed_map = array();

 foreach ($rows as $row) {

     // Calculate the amount that can be used against a specific credit
     // It will be the minimum of available credit and amount left to redeem
     $amount_redeemed  = min($row['available_credit'], $amount_to_redeem);

     // Populate the map
     $amount_redeemed_map[$row['id']] = $amount_redeemed;

     // Adjust the amount_to_redeem
     $amount_to_redeem -= $amount_redeemed;

     // If no more amount_to_redeem, we can finish the loop
     if ($amount_to_redeem == 0) {
         break;
     } elseif ($amount_to_redeem < 0) {

        // This should never happen, still if it happens, throw error
        throw new Exception ("Something wrong with logic!");
        exit();
     }

     // if we are here, that means some more amount left to redeem
 }

现在，您可以使用两个更新查询。第一个将针对所有信用 ID 更新 amount_redeemed 值。第二个将使用所有单个 amount_redeemed 值的总和来插入 Deduct 行。