MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table points(
	id int
	,user_id int
	,point int
	,memo VARCHAR(50)
	,isGift int
	,status int
	,`valid` timestamp NULL DEFAULT NULL COMMENT '生效日'
	,`expiry` timestamp NULL DEFAULT NULL COMMENT '到期日'
	,`expired_at` timestamp NULL DEFAULT NULL COMMENT '過期時間，已經過期了的註記(重新計算會更新)'
	,`ExtendValid` timestamp NULL DEFAULT NULL COMMENT '展延期間點數，最早的生效日(重新計算會更新)'
	,`ExtendExpiry` timestamp NULL DEFAULT NULL COMMENT '展延後的到期日(重新計算會更新)'
	,deleted_at datetime
	,created_time datetime
);
insert into points
values(1,1,75,'舊點數轉移',0,1,'2018/10/10','2020/3/1',null,null,null,null,'2018/10/10 13:50')
  ,(2,1,2,'舊點數轉移(贈送)',1,1,'2018/10/10','2020/3/1',null,null,null,null,'2018/10/10 13:50')
  ,(3,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2018/4/11 13:50')
  ,(4,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2018/5/12 13:50')
  ,(5,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2018/6/13 13:50')
  ,(6,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2018/7/14 13:50')
  ,(7,1,50,'購買點數',0,1,'2019/8/2','2020/8/1',null,null,null,null,'2019/8/1 13:50')
  ,(8,1,2,'交易贈送',1,1,'2019/8/2','2020/8/1',null,null,null,null,'2019/8/1 13:50')
  ,(9,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2019/9/11 13:50')
  ,(10,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2019/10/12 13:50')
  ,(11,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2019/11/13 13:50')
  ,(12,1,-11,'消費扣點',0,1,null,null,null,null,null,null,'2019/12/14 13:50')
  ,(13,1,25,'購買點數',0,0,null,null,null,null,null,null,'2022/8/1 13:50')
  ,(14,1,25,'購買點數',0,0,null,null,null,null,null,null,'2022/8/1 13:50')
  ,(15,1,25,'購買點數',0,1,'2022/10/10','2023/10/10',null,null,null,null,'2022/10/10 13:50')
  ,(16,1,-22,'消費扣點',0,1,null,null,null,null,null,null,'2022/10/14 13:50')
  ;

Records: 16  Duplicates: 0  Warnings: 0


select *
from points;


id
user_id
point
memo
isGift
status
valid
expiry
expired_at
ExtendValid
ExtendExpiry
deleted_at
created_time


1
1
75
舊點數轉移
0
1
2018-10-10 00:00:00
2020-03-01 00:00:00
null
null
null
null
2018-10-10 13:50:00

2
1
2
舊點數轉移(贈送)
1
1
2018-10-10 00:00:00
2020-03-01 00:00:00
null
null
null
null
2018-10-10 13:50:00

3
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-04-11 13:50:00

4
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-05-12 13:50:00

5
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-06-13 13:50:00

6
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-07-14 13:50:00

7
1
50
購買點數
0
1
2019-08-02 00:00:00
2020-08-01 00:00:00
null
null
null
null
2019-08-01 13:50:00

8
1
2
交易贈送
1
1
2019-08-02 00:00:00
2020-08-01 00:00:00
null
null
null
null
2019-08-01 13:50:00

9
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-09-11 13:50:00

10
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-10-12 13:50:00

11
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-11-13 13:50:00

12
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-12-14 13:50:00

13
1
25
購買點數
0
0
null
null
null
null
null
null
2022-08-01 13:50:00

14
1
25
購買點數
0
0
null
null
null
null
null
null
2022-08-01 13:50:00

15
1
25
購買點數
0
1
2022-10-10 00:00:00
2023-10-10 00:00:00
null
null
null
null
2022-10-10 13:50:00

16
1
-22
消費扣點
0
1
null
null
null
null
null
null
2022-10-14 13:50:00


create table users(
	id int
	,name VARCHAR(50)
    ,ValidPoint int
	,deleted_at datetime
	,created_time datetime
);
insert into users
values(1,'test',0,null,'2018/10/10 13:50');
select *
from users;


id
name
ValidPoint
deleted_at
created_time


1
test
0
null
2018-10-10 13:50:00


CREATE FUNCTION `CalculatePoints`(UserID INT) RETURNS int(10)
    DETERMINISTIC
BEGIN
    DECLARE I, TOTAL, OutPoint INT DEFAULT 0;
    SELECT count(*) INTO TOTAL FROM points WHERE user_id = UserID AND `deleted_at` IS NULL AND `status` = '1' AND `isGift` = 0 AND `point` > '0';
		
		/*重置所有設定*/
		UPDATE points SET expired_at = null, ExtendValid = valid, ExtendExpiry = expiry WHERE user_id = UserID AND `deleted_at` IS NULL AND `status` = '1' AND `point` > '0'; 

		SET I = 0;
		loop1: WHILE I < TOTAL DO
			
-- 		重新計算ExtendValid、ExtendExpiry
			UPDATE points AS a
			JOIN (
				SELECT ExtendValid, ExtendExpiry
				FROM points 
				WHERE user_id = UserID AND `deleted_at` IS NULL AND `status` = '1' AND `isGift` = 0 AND `point` > '0' 
				ORDER BY id desc limit I, 1
			) AS tt ON ((tt.ExtendValid <= a.ExtendExpiry and a.ExtendExpiry <= tt.ExtendExpiry) or (tt.ExtendValid <= a.ExtendValid and a.ExtendValid <= tt.ExtendExpiry)) 
			SET a.ExtendValid = (SELECT ifnull(min(b.ExtendValid),a.ExtendValid) FROM points b WHERE b.ExtendValid is not null and ((a.ExtendValid <= b.ExtendExpiry and b.ExtendExpiry <= a.ExtendExpiry) or (a.ExtendValid <= b.ExtendValid and b.ExtendValid <= a.ExtendExpiry)) and a.user_id = b.user_id AND b.`deleted_at` IS NULL AND b.`status` = '1' AND b.`isGift` = 0 AND b.`point` > '0'), 
			a.ExtendExpiry = (SELECT ifnull(max(b.ExtendExpiry),a.ExtendExpiry) FROM points b WHERE b.ExtendExpiry is not null and ((a.ExtendValid <= b.ExtendExpiry and b.ExtendExpiry <= a.ExtendExpiry) or (a.ExtendValid <= b.ExtendValid and b.ExtendValid <= a.ExtendExpiry)) and a.user_id = b.user_id AND b.`deleted_at` IS NULL AND b.`status` = '1' AND b.`isGift` = 0 AND b.`point` > '0')
			WHERE user_id = UserID AND a.`deleted_at` IS NULL AND a.`status` = '1' AND a.`isGift` = 0 AND a.`point` > '0';
			
			SET I = I + 1;
		END WHILE loop1;
		
		UPDATE points
		SET expired_at = ExtendExpiry
		WHERE ExtendExpiry < now() and user_id = UserID AND `deleted_at` IS NULL AND `status` = '1' AND `point` > '0';
			
		SELECT GetPoint(UserID) INTO OutPoint;
		
    RETURN OutPoint;
END
CREATE FUNCTION `GetPoint`(UserID INT) RETURNS int(10)
    DETERMINISTIC
BEGIN
    DECLARE tmp_ID, OutPoint INT DEFAULT 0;
		SELECT min(id) INTO tmp_ID FROM points WHERE `user_id` = UserID AND `expired_at` IS NULL AND `deleted_at` IS NULL AND `status` = '1' AND `isGift` = 0 AND `point` > '0';

		SELECT ifnull(sum(a.point),0) INTO OutPoint FROM points a
		WHERE a.user_id = UserID and id >= tmp_ID and a.status = 1 and a.deleted_at is null and (a.valid < now() or a.valid is null);

    RETURN OutPoint;
END
select CalculatePoints(1);


CalculatePoints(1)


3


CREATE PROCEDURE `CheckAllUserPoint`()
BEGIN
		DECLARE I, TOTAL, UserID INT DEFAULT 0;
    SELECT count(*) INTO TOTAL FROM users WHERE deleted_at is null;

		SET I = 0;
		loop1: WHILE I < TOTAL DO
			SELECT id INTO UserID FROM users
			WHERE deleted_at is null
			ORDER BY id asc limit I, 1;
		
			UPDATE users SET ValidPoint = CalculatePoints(UserID) WHERE id = UserID;
			
			SET I = I + 1;
		END WHILE loop1;
END
call CheckAllUserPoint()
select *
from points;


id
user_id
point
memo
isGift
status
valid
expiry
expired_at
ExtendValid
ExtendExpiry
deleted_at
created_time


1
1
75
舊點數轉移
0
1
2018-10-10 00:00:00
2020-03-01 00:00:00
2020-08-01 00:00:00
2018-10-10 00:00:00
2020-08-01 00:00:00
null
2018-10-10 13:50:00

2
1
2
舊點數轉移(贈送)
1
1
2018-10-10 00:00:00
2020-03-01 00:00:00
2020-03-01 00:00:00
2018-10-10 00:00:00
2020-03-01 00:00:00
null
2018-10-10 13:50:00

3
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-04-11 13:50:00

4
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-05-12 13:50:00

5
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-06-13 13:50:00

6
1
-11
消費扣點
0
1
null
null
null
null
null
null
2018-07-14 13:50:00

7
1
50
購買點數
0
1
2019-08-02 00:00:00
2020-08-01 00:00:00
2020-08-01 00:00:00
2018-10-10 00:00:00
2020-08-01 00:00:00
null
2019-08-01 13:50:00

8
1
2
交易贈送
1
1
2019-08-02 00:00:00
2020-08-01 00:00:00
2020-08-01 00:00:00
2019-08-02 00:00:00
2020-08-01 00:00:00
null
2019-08-01 13:50:00

9
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-09-11 13:50:00

10
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-10-12 13:50:00

11
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-11-13 13:50:00

12
1
-11
消費扣點
0
1
null
null
null
null
null
null
2019-12-14 13:50:00

13
1
25
購買點數
0
0
null
null
null
null
null
null
2022-08-01 13:50:00

14
1
25
購買點數
0
0
null
null
null
null
null
null
2022-08-01 13:50:00

15
1
25
購買點數
0
1
2022-10-10 00:00:00
2023-10-10 00:00:00
null
2022-10-10 00:00:00
2023-10-10 00:00:00
null
2022-10-10 13:50:00

16
1
-22
消費扣點
0
1
null
null
null
null
null
null
2022-10-14 13:50:00


select *
from users;


id
name
ValidPoint
deleted_at
created_time


1
test
3
null
2018-10-10 13:50:00

id	user_id	point	memo	isGift	status	valid	expiry	expired_at	ExtendValid	ExtendExpiry	deleted_at	created_time
1	1	75	舊點數轉移	0	1	2018-10-10 00:00:00	2020-03-01 00:00:00	null	null	null	null	2018-10-10 13:50:00
2	1	2	舊點數轉移(贈送)	1	1	2018-10-10 00:00:00	2020-03-01 00:00:00	null	null	null	null	2018-10-10 13:50:00
3	1	-11	消費扣點	0	1	null	null	null	null	null	null	2018-04-11 13:50:00
4	1	-11	消費扣點	0	1	null	null	null	null	null	null	2018-05-12 13:50:00
5	1	-11	消費扣點	0	1	null	null	null	null	null	null	2018-06-13 13:50:00
6	1	-11	消費扣點	0	1	null	null	null	null	null	null	2018-07-14 13:50:00
7	1	50	購買點數	0	1	2019-08-02 00:00:00	2020-08-01 00:00:00	null	null	null	null	2019-08-01 13:50:00
8	1	2	交易贈送	1	1	2019-08-02 00:00:00	2020-08-01 00:00:00	null	null	null	null	2019-08-01 13:50:00
9	1	-11	消費扣點	0	1	null	null	null	null	null	null	2019-09-11 13:50:00
10	1	-11	消費扣點	0	1	null	null	null	null	null	null	2019-10-12 13:50:00
11	1	-11	消費扣點	0	1	null	null	null	null	null	null	2019-11-13 13:50:00
12	1	-11	消費扣點	0	1	null	null	null	null	null	null	2019-12-14 13:50:00
13	1	25	購買點數	0	0	null	null	null	null	null	null	2022-08-01 13:50:00
14	1	25	購買點數	0	0	null	null	null	null	null	null	2022-08-01 13:50:00
15	1	25	購買點數	0	1	2022-10-10 00:00:00	2023-10-10 00:00:00	null	null	null	null	2022-10-10 13:50:00
16	1	-22	消費扣點	0	1	null	null	null	null	null	null	2022-10-14 13:50:00