CODEWITHSUNDEEP
phpmysql
louis
louis

Reputation: 57

Handling loyalty credits with different expiry date and calculation

I'm creating a membership system with loyalty point for a restaurant.

Every member spend at the restaurant the merchant will issue loyalty point for member as cashback, and they were able to use the loyalty point in their account and act as money for transaction. And every points has its expiration date which is 3 months.

Here's the problem im facing:

The logic I write could not calculate the remaining points and add on to the rest if they were only use partial point.

This is my table 

CREATE TABLE `transaction_record_tpg` (
  `trans_id` int(15) NOT NULL,
  `collected_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expired_date` datetime NOT NULL,
  `user_id` int(15) NOT NULL,
  `m_debit` decimal(6,2) NOT NULL,
  `m_credit` decimal(6,2) NOT NULL,
  `collect_outlet` varchar(255) NOT NULL,
)

I would like to create a table that can automatically calculate user's debit/credit transaction record with different expiration date.

Below are the test case that I created:

enter image description here

From the table, you can see that the member gained 15 pts at sept 1, and used 11 pts on sept 10 and oct 10 respectively.

So the user actually still has 4 pts left and the expiry date and collect date should remain the same which is (sept 1-dec 4).

Then the user gained 25 pts on oct 31 and dec 5 respectively, and on jan 10 used 10 pts, but by now the 4 pts from above CANNOT add into the calculate as the pts is already expired.

So we should only use 20pts that we gain from oct 31 to minus 10 pts, which left us 10pts plus 5 points from dec 5th.

So by now (25th feb 2020), the 10pts left from oct 31 is expired, which only left 5 pts in total.

So the computer should show me 5 points.

However the computer calculate as -16 pts which is obviously not the answer.

Here's the logic i use to pull out the result from database:

<?php 

$member_session = $_SESSION['u_id'];
$get_member = 
"SELECT(SELECT SUM(m_debit) AS metallic_point FROM transaction_record_tpg WHERE user_id = $member_session AND  expired_date >= CURRENT_DATE())
    -
    (SELECT SUM(m_credit) AS metallic_point FROM transaction_record_tpg WHERE user_id = $member_session)
    ";
?>

Can anyone tell me what went wrong and what should i do to fix the bug? Or is there a better way to record and do this kind of function?

Upvotes: 3

Views: 1374

Answers (1)

Eriks Klotins
Eriks Klotins

Reputation: 4180

To elaborate my suggestion in the comments, I would implement the following algorithm for spending points:

  1. Find the oldest still valid rows (say A and B) with a sum equal or larger than the required amount (X). How to select rows that sum to a certain value
  2. If such rows do not exist, return an error.
  3. Mark rows A and B as "spent"
  4. Calculate the remaining points (C = A + B - X)
  5. If C == 0 go to the end
  6. Find max expiration date (D) between A and B
  7. Create a new row with expiration date D and amount C.
  8. The end

Make sure to wrap all database calls into one transaction, or you could create a room for weird bugs and exploits.

Upvotes: 1

Related Questions