从钱包系统中计算可用余额(不包括过期贷项)

Question

在credits表中

(id，user_id，进程，金额，date_add，date_exp，date_redeemed，备注)

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:

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

如果已过期且未赎回，则从余额中排除

否则，如果在过期之前使用，并且赎回金额<到期金额，则(余额-(到期金额-赎回金额))

否则，如果在过期前使用，且赎回金额>到期金额，则可用余额将被扣除，因为到期金额不足以扣除赎回金额。

编辑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

您当前的表格存在基本的结构问题。因此，我建议对表结构和随后的应用程序代码进行一些修改。Wallet系统的表结构可以非常详细；但我建议这里尽量减少可能的更改。我并不是说这是一种理想的方式，但它应该能奏效。首先，我将用当前的方法列出一些问题。

问题：

• 如果有多个可用的学分尚未过期怎么办？
• 在这些可用贷项中，有些可能已经实际使用，但尚未过期。我们如何才能忽略它们以获得可用的余额呢？
• 此外，有些可能已被部分利用。我们如何说明部分使用情况？
• 可能会出现这样一种情况，即赎回金额跨越多个未过期信用额。有些可能得到部分利用，而有些则可能得到充分利用。

普通实践：

我们通常遵循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 |
+----+---------+---------+--------+-----------------+------------+---------------+---------------+----------+

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

因此，一旦我们完成了这个设置，您就可以在应用程序代码中执行以下操作：

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

这将是一次活动。因此，制定一个单一的查询将很困难(这就是为什么我们一开始就在这里)。因此，我建议您使用循环和FIFO方法，在应用程序代码(例如: PHP)中执行一次。请看下面的第3点，了解如何在应用程序代码中这样做。

1. 获得可用余额：

对此的查询现在变得非常简单，因为我们只需要计算所有尚未过期的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时更新

在此，您可以首先获得所有可用的学分，其中有可赎回的金额，但尚未过期。

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
 }

现在，您可以使用两个Update查询。首先，将根据所有的Credit更新amount_redeemed值。第二种方法是使用所有单个Insert值的和来表示减行。

Answer 2

SELECT `id`, `email`, `NAME`, `type`,
    (
        ( SELECT SUM(amount) FROM credit_table AS ct1 WHERE u.id = ct1.id AND process = 'ADD' AND date_exp > CURDATE()) - 
        ( SELECT SUM(amount) FROM credit_table AS ct2 WHERE u.id = ct2.id AND process = 'Deduct' )
    ) AS balance
FROM
    `user_table` AS u
WHERE
    id = 2;

希望它能按你的意愿运作