php - 付费成员(member)处理

Question

我有两张表，一张是“交易”表，一张是“成员(member)”表。

用户可以同时激活所有可用的成员(member)资格(每个成员(member)资格提供不同的好处)。

我正在尝试处理具有相同用户 ID 和相同成员(member)身份的多个交易记录。

这是我的表格

Transactions table

ID | USERID | MID |      CREATED      | AMOUNT
-------------------------------------------------
 1  |   1   |  2  | 2014-10-01 00:00:00 |   1
 2  |   1   |  2  | 2014-10-16 00:00:00 |   1
 3  |   2   |  1  | 2014-10-30 00:00:00 |   1

Membership tables

ID |    TITLE    |  DURATION
-------------------------
 1 |   Premium   |     365
 2 |  Supporter  |     30
 3 | Beta Access |     30

在交易表中，我有 2 条用户 ID 1 的记录，一条从 2014-10-01 开始，另一条在2014-10-16.

以下脚本可以很好地选择个人事件成员日志

SELECT t.USERID AS UID, t.CREATED AS CREATED, FROM_UNIXTIME(UNIX_TIMESTAMP(t.CREATED) + t.AMOUNT * m.DURATION) AS ENDS
FROM Transactions AS t
LEFT JOIN Memberships AS m on m.ID = t.MID
LIMIT 5

输出是这样的，

UID | MID |       CREATED      | ENDS
-----------------------------------------------------
 1  | 2  | 2014-10-01 00:00:00 | 2014-10-31 00:00:00
 1  | 2  | 2014-10-16 00:00:00 | 2014-11-15 00:00:00
 2  | 1  | 2014-10-30 00:00:00 | 2015-10-30 00:00:00

现在有两条记录具有相同的成员(member) ID (MID) 和用户 ID (UID)，并且第一条记录在第二条之前过期。

基本上，我想做的是，只要在当前过期之前添加另一个成员(member)(相同的用户 ID 和相同的 memebrship id)，“合并”或合并“成员(member)”的总“未使用”天数.

(这是一个显示给定数据和所需输出的示例:)

ID | USERID | MID |      CREATED        | Amount
-------------------------------------------------
 1  |   1   |  2  | 2014-10-01 00:00:00 |   1 #30 days days remains
 2  |   1   |  2  | 2014-10-17 00:00:00 |   1 #14 days of the previous transaction is not fully consumed,43 days remains - (days amount +  previous unused days)
 3  |   1   |  2  | 2014-11-01 00:00:00 |   1 #28 days of the previous transaction (44 days ones) is not fully consumed,59 days remains - (days amount +  previous unused days)
 4  |   2   |  3  | 2014-10-01 00:00:00 |   1 #30 days days remains
 5  |   2   |  3  | 2014-11-08 00:00:00 |   1 #30 days days remains

输出应该是这样的

UID | MID |       CREATED       | ENDS
-----------------------------------------------------
 1  | 2  | 2015-10-01 00:00:00 | 2014-12-29 00:00:00
 2  | 1  | 2014-10-01 00:00:00 | 2014-10-30 00:00:00
 2  | 1  | 2014-11-08 00:00:00 | 2014-12-08 00:00:00

如果不清楚，我深表歉意，因为英语不是我的母语，也没有文字来解释我想要完成的事情。

编辑:如果无法通过 mysql 寻找 php 解决方案。

Answer 1

我想说主要有三种不同的方法，我会尝试为它们举例。然而，所有方法也都有优点/缺点......

MySQL查询

似乎这样的查询需要某种递归......所以一个潜在的方法可以类似于this一，利用 MySQL 的表达式求值，使用 User-Defined Variables在选择语句中。

返回每个交易记录的持续时间和(扩展)结束的查询:

SELECT id, userid, mid, created,
  -- initialize if new userid or membership id
  IF (@lastuser!=userid or @lastmb!=mid, (@prevend:=created)+(@lastuser:=userid)+(@lastmb:=mid), 0) AS tmp,
  -- calculate unused days
  @unused:=IF(@prevend>created, datediff(@prevend, created), 0) AS tmp2, 
  -- calculate the end of current membership (will be used for next record)
  @prevend:=DATE_ADD(created, INTERVAL (amount * duration)+@unused DAY) AS ends,
  -- calculate the days remaining
  @unused+duration AS 'days remain'
FROM (
  SELECT tt.id, tt.userid, tt.mid, tt.created, tt.amount, duration
  FROM transactions tt
  LEFT JOIN memberships as m on m.ID = tt.MID
  ORDER BY tt.userid, tt.created) t
JOIN (SELECT @lastuser:=0)tmp;

这个查询的输出是:

id  userid mid  created             tmp     tmp2 ends                   days remain
1   1      2    2014-10-01 00:00:00 2017    0    2014-10-31 00:00:00    30
2   1      2    2014-10-17 00:00:00 0       14   2014-11-30 00:00:00    44
3   1      2    2014-11-01 00:00:00 0       29   2014-12-30 00:00:00    59
4   2      3    2014-10-01 00:00:00 2019    0    2014-10-31 00:00:00    30
5   2      3    2014-11-08 00:00:00 0       0    2014-12-08 00:00:00    30

还有一个任务，就是输出合并的区间:

SELECT userid, mid, begins, max(ends) as ends FROM (
    SELECT id, userid, mid, created,
      -- initialize if new userid or membership id
      IF (@lastuser!=userid or @lastmb!=mid, (@prevend:=created)+(@lastuser:=userid)+(@lastmb:=mid), 0) AS tmp,
      -- firstcreated stores the very first creation time of overlapping memberships 
      if (@prevend>created, @firstcreated, (@firstcreated:=created)) as begins,
      -- calculate unused days
      @unused:=IF(@prevend>created, datediff(@prevend, created), 0) AS tmp2, 
      -- calculate the end of current membership (will be used for next record)
      @prevend:=DATE_ADD(created, INTERVAL (amount * duration)+@unused DAY) AS ends,
      -- calculate the days remaining
      @unused+duration AS 'days remain'
    FROM (
      SELECT tt.id, tt.userid, tt.mid, tt.created, tt.amount, duration
      FROM transactions tt
      LEFT JOIN memberships as m on m.ID = tt.MID
      ORDER BY tt.userid, tt.created) t
    JOIN (SELECT @lastuser:=0)tmp
) mship 
GROUP BY userid, mid, begins;

但是请注意，这确实不是一个可取的解决方案，因为无法保证表达式的求值顺序。所以查询可能会产生好的结果，但是对于不同的数据集，或者不同的 MySQL 版本，它可能很容易产生不好的结果。在提议的查询中，有一个带有 order by 子句的子查询，所以记录顺序在这里应该不会成为问题，但是如果你想把这个查询放在你希望维护更长时间的代码中，你可能会在迁移时感到惊讶例如，到新版本的 MySQL。

至少，它似乎也适用于 MySQL 5.5 和 MySQL 5.6。

再次警告，因为正如 MySQL 文档所说:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1; For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

在客户端(应用程序)端计算所有内容(例如在 PHP 中)

思路是一样的。获取按 userid、mid 和创建日期排序的交易。遍历记录，并为每个新交易延长成员资格的持续时间与“未使用”日期(如果有的话)，这可以从以前的交易中计算出来。当我们看到成员(member)出现中断时，我们会保存实际的时间段。

执行此操作的示例 PHP 代码:

<?php
$conn = mysqli_connect("localhost", "user", "password", "db");
if (mysqli_connect_errno($conn)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Query to select membership information
$res = mysqli_query($conn, "select t.id, userid, mid, created, (m.duration * t.amount) as duration
    from transactions t
    left join memberships m
    on t.mid=m.id
    order by userid, mid, created");

echo "Temporary calculation:<br/>";
echo "<table border=\"1\">";
echo "<th>Id</th><th>UserId</th><th>MID</th><th>Created</th><th>Unused</th><th>End</th><th>Duration</th>";

$last_userid=0;
while ($row = $res->fetch_assoc()) {
    // Beginning of a new userid or membership id
    if ($row['userid']!=$last_userid or $row['mid']!=$last_mid) {
        // If we are not at the first record, we save the current period
        if ($last_userid!=0) {
            $mships[$last_userid][$last_mid][$first_created->format('Y-m-d H:i:s')]=$last_end->format('Y-m-d H:i:s');
        }

        // Initialize temporaries
        $last_userid=$row['userid'];
        $last_mid=$row['mid'];
        $first_created=new DateTime($row['created']);
        $last_end=clone $first_created;
    }

    // Calculate duration
    $created=new DateTime($row['created']);
    $unused=date_diff($created, $last_end);
    $ends=clone $created;
    $ends->add(new DateInterval("P".$row['duration']."D"));

    // $unused->invert is 1 if diff is negative 
    if ($unused->invert==0 && $unused->days>=0) {
        // This transaction extends/immediately follows the previous period
        $ends->add(new DateInterval('P'.$unused->days.'D'));
    } else {
        // We split the period -> save it!
        $mships[$row['userid']][$row['mid']][$first_created->format('Y-m-d H:i:s')]=$last_end->format('Y-m-d H:i:s');
        $first_created=new DateTime($row['created']);
    }

    $duration=date_diff($ends, $created);   

    echo "<tr>";
    echo "<td>",$row['id'],"</td>";
    echo "<td>",$row['userid'],"</td>";
    echo "<td>",$row['mid'],"</td>";
    echo "<td>",$row['created'],"</td>";
    echo "<td>",($unused->invert==0 ? $unused->format('%a') : 0),"</td>";
    echo "<td>",$ends->format('Y-m-d H:i:s'),"</td>";
    echo "<td>",$duration->format('%a'),"</td>";
    echo "</tr>";

    $last_end=$ends;
}
// Last period should be saved
if ($last_userid!=0) {
    $mships[$last_userid][$last_mid][$first_created->format('Y-m-d H:i:s')]=$last_end->format('Y-m-d H:i:s');
}

echo "</table><br/>";

echo "Final array:<br/>";
echo "<table border=\"1\">";
echo "<th>UserId</th><th>MID</th><th>Created</th><th>End</th>";

foreach ($mships as $uid => &$mids) {
    foreach ($mids as $mid => &$periods) {
        foreach ($periods as $begin => $end) {
            echo "<tr>";
            echo "<td>",$uid,"</td>";
            echo "<td>",$mid,"</td>";
            echo "<td>",$begin,"</td>";
            echo "<td>",$end,"</td>";
            echo "</tr>";               
        }
    }
}

$conn->close();

?>

(老实说，自从我上次用 php 编写任何东西已经有几年了:)所以请随意重新格式化或使用一些更好的解决方案。)

输出应该是这样的:

Temporary calculation:
Id  UserId  MID Created             Unused  End                 Duration
1   1       2   2014-10-01 00:00:00 0       2014-10-31 00:00:00 30
2   1       2   2014-10-17 00:00:00 14      2014-11-30 00:00:00 44
3   1       2   2014-11-01 00:00:00 29      2014-12-30 00:00:00 59
4   2       3   2014-10-01 00:00:00 0       2014-10-31 00:00:00 30
5   2       3   2014-11-08 00:00:00 0       2014-12-08 00:00:00 30

Final results:
UserId  MID Created             End
1       2   2014-10-01 00:00:00 2014-12-30 00:00:00
2       3   2014-10-01 00:00:00 2014-10-31 00:00:00
2       3   2014-11-08 00:00:00 2014-12-08 00:00:00

MySQL 存储过程

也可以用存储过程计算结果集。例如。与前面的 PHP 代码具有相同算法的示例过程。

DROP PROCEDURE IF EXISTS get_memberships;

delimiter //

CREATE PROCEDURE get_memberships()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE uid, mid, duration INT;
  DECLARE created, unused, first_created, ends TIMESTAMP;
  -- make sure that there is no user with 0 id
  DECLARE last_uid, last_mid INT DEFAULT 0;
  DECLARE last_end TIMESTAMP;
  DECLARE cur CURSOR FOR SELECT t.userid, t.mid, t.created, (m.duration * t.amount) as duration
    FROM transactions t
    LEFT JOIN memberships m
    ON t.mid=m.id
    ORDER BY userid, mid, created;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  REPEAT
    FETCH cur INTO uid, mid, created, duration;
    IF (!done) THEN
        IF (uid!=last_uid OR last_mid!=mid) THEN
            IF (last_uid!=0) THEN
                INSERT INTO results (userid, mid, created, ends) VALUES (last_uid, last_mid, first_created, last_end);
            END IF;

            SET last_uid = uid;
            SET last_mid = mid;
            SET last_end = created;
            SET first_created = created;
        END IF;

        SET ends = DATE_ADD(created, INTERVAL duration DAY);

        IF (last_end>=created) THEN
            SET ends = DATE_ADD(ends, INTERVAL datediff(last_end, created) DAY);
        ELSE
            INSERT INTO results (userid, mid, created, ends) VALUES (uid, mid, first_created, last_end);
            SET first_created = created;
        END IF;

        SET last_end = ends;
    END IF;
  UNTIL done
  END REPEAT;

  IF (last_uid!=0) THEN
    INSERT INTO results (userid, mid, created, ends) VALUES (uid, last_mid, first_created, last_end);
  END IF;

  CLOSE cur;
END
//

DROP TABLE IF EXISTS results //
CREATE TEMPORARY TABLE results AS SELECT userid, mid, created, created as ends FROM transactions WHERE 0 //
call get_memberships //
SELECT * FROM results //
DROP TABLE results;

但是，这种技术的一个缺点是使用了临时表。