方式一:C#
// Model/IntegralRecord.cs
public class IntegralRecord
{
public long Id { get; set; }
public short Type { get; set; }
public string OriginalId { get; set; }
public int Value { get; set; }
public DateTime ExpireTime { get; set; }
public string Reason { get; set; }
public string UserId { get; set; }
public bool Deleted { get; set; }
public DateTime CreateTime { get; set; }
public DateTime UpdateTime { get; set; }
}
// Model/UsableIntegral.cs
public class UsableIntegral
{
public long Id { get; set; }
public long RecordId { get; set; }
public int Value { get; set; }
public DateTime ExpireTime { get; set; }
public string UserId { get; set; }
public bool Deleted { get; set; }
public DateTime CreateTime { get; set; }
public DateTime UpdateTime { get; set; }
}
// Model/ReduceIntegralDetail.cs
public class ReduceIntegralDetail
{
public long Id { get; set; }
public long ReduceId { get; set; }
public long AddId { get; set; }
public int UsedValue { get; set; }
public DateTime ExpireTime { get; set; }
public string UserId { get; set; }
public bool Deleted { get; set; }
public DateTime CreateTime { get; set; }
public DateTime UpdateTime { get; set; }
}
// Service/IntegralService.cs
public class IntegralService
{
private readonly DbContext _context;
public IntegralService(DbContext context)
{
_context = context;
}
// 获取用户当前可用总积分
public async Task<int> GetUserTotalIntegral(string userId)
{
return await _context.UsableIntegrals
.Where(u => u.UserId == userId && !u.Deleted)
.SumAsync(u => u.Value);
}
// 获取用户积分明细
public async Task<List<IntegralDetailDto>> GetUserIntegralDetails(string userId)
{
var records = await _context.IntegralRecords
.Where(r => r.UserId == userId)
.OrderByDescending(r => r.CreateTime)
.Select(r => new IntegralDetailDto
{
Id = r.Id,
Type = r.Type,
Value = r.Value,
ExpireTime = r.ExpireTime,
Reason = r.Reason,
CreateTime = r.CreateTime
})
.ToListAsync();
return records;
}
// 处理积分过期
public async Task HandleExpiredIntegral()
{
var now = DateTime.Now;
var expiredIntegrals = await _context.UsableIntegrals
.Where(u => !u.Deleted && u.ExpireTime <= now)
.ToListAsync();
foreach (var integral in expiredIntegrals)
{
// 标记为已删除
integral.Deleted = true;
integral.UpdateTime = now;
// 添加过期记录
var record = new IntegralRecord
{
Type = 3, // 过期类型
Value = -integral.Value,
ExpireTime = now,
Reason = "积分过期",
UserId = integral.UserId,
CreateTime = now,
UpdateTime = now
};
_context.IntegralRecords.Add(record);
// 添加扣减详情
var detail = new ReduceIntegralDetail
{
ReduceId = record.Id,
AddId = integral.RecordId,
UsedValue = integral.Value,
ExpireTime = integral.ExpireTime,
UserId = integral.UserId,
CreateTime = now,
UpdateTime = now
};
_context.ReduceIntegralDetails.Add(detail);
}
await _context.SaveChangesAsync();
}
// 使用积分
public async Task<bool> UseIntegral(string userId, int value, string reason)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
var availableIntegrals = await _context.UsableIntegrals
.Where(u => u.UserId == userId && !u.Deleted)
.OrderBy(u => u.ExpireTime)
.ThenBy(u => u.Value)
.ToListAsync();
var totalAvailable = availableIntegrals.Sum(u => u.Value);
if (totalAvailable < value)
{
return false;
}
var now = DateTime.Now;
var remainingValue = value;
var reduceRecord = new IntegralRecord
{
Type = 2, // 使用积分类型
Value = -value,
ExpireTime = now,
Reason = reason,
UserId = userId,
CreateTime = now,
UpdateTime = now
};
_context.IntegralRecords.Add(reduceRecord);
await _context.SaveChangesAsync();
foreach (var integral in availableIntegrals)
{
if (remainingValue <= 0) break;
var useValue = Math.Min(remainingValue, integral.Value);
remainingValue -= useValue;
if (useValue == integral.Value)
{
integral.Deleted = true;
}
else
{
integral.Value -= useValue;
}
integral.UpdateTime = now;
var detail = new ReduceIntegralDetail
{
ReduceId = reduceRecord.Id,
AddId = integral.RecordId,
UsedValue = useValue,
ExpireTime = integral.ExpireTime,
UserId = userId,
CreateTime = now,
UpdateTime = now
};
_context.ReduceIntegralDetails.Add(detail);
}
await _context.SaveChangesAsync();
await transaction.CommitAsync();
return true;
}
catch
{
await transaction.RollbackAsync();
throw;
}
}
}
// Controller/IntegralController.cs
[ApiController]
[Route("api/[controller]")]
public class IntegralController : ControllerBase
{
private readonly IntegralService _integralService;
public IntegralController(IntegralService integralService)
{
_integralService = integralService;
}
[HttpGet("total/{userId}")]
public async Task<ActionResult<int>> GetTotalIntegral(string userId)
{
var total = await _integralService.GetUserTotalIntegral(userId);
return Ok(total);
}
[HttpGet("details/{userId}")]
public async Task<ActionResult<List<IntegralDetailDto>>> GetIntegralDetails(string userId)
{
var details = await _integralService.GetUserIntegralDetails(userId);
return Ok(details);
}
[HttpPost("use")]
public async Task<ActionResult> UseIntegral([FromBody] UseIntegralRequest request)
{
var success = await _integralService.UseIntegral(request.UserId, request.Value, request.Reason);
if (!success)
{
return BadRequest("积分不足");
}
return Ok();
}
}
方式二:SQL Server 存储过程方式
-- 创建获取用户总积分的存储过程
CREATE PROCEDURE sp_GetUserTotalIntegral
@UserId VARCHAR(40)
AS
BEGIN
SELECT SUM(Value) as TotalIntegral
FROM UsableIntegral
WHERE UserId = @UserId
AND Deleted = 0;
END;
-- 创建获取用户积分明细的存储过程
CREATE PROCEDURE sp_GetUserIntegralDetails
@UserId VARCHAR(40)
AS
BEGIN
SELECT
Id,
Type,
Value,
ExpireTime,
Reason,
CreateTime
FROM IntegralRecord
WHERE UserId = @UserId
ORDER BY CreateTime DESC;
END;
-- 创建处理过期积分的存储过程
CREATE PROCEDURE sp_HandleExpiredIntegral
AS
BEGIN
BEGIN TRANSACTION;
DECLARE @Now DATETIME = GETDATE();
-- 找出过期的积分记录
DECLARE @ExpiredIntegrals TABLE (
Id BIGINT,
RecordId BIGINT,
Value INT,
ExpireTime DATETIME,
UserId VARCHAR(40)
);
INSERT INTO @ExpiredIntegrals
SELECT Id, RecordId, Value, ExpireTime, UserId
FROM UsableIntegral
WHERE Deleted = 0
AND ExpireTime <= @Now;
-- 标记过期积分为已删除
UPDATE UsableIntegral
SET Deleted = 1,
UpdateTime = @Now
FROM UsableIntegral u
INNER JOIN @ExpiredIntegrals e ON u.Id = e.Id;
-- 添加过期记录
INSERT INTO IntegralRecord (
Type,
Value,
ExpireTime,
Reason,
UserId,
CreateTime,
UpdateTime
)
SELECT
3, -- 过期类型
-Value,
@Now,
'积分过期',
UserId,
@Now,
@Now
FROM @ExpiredIntegrals;
-- 获取新插入的记录ID
DECLARE @LastInsertId TABLE (Id BIGINT);
INSERT INTO @LastInsertId
SELECT SCOPE_IDENTITY();
-- 添加扣减详情
INSERT INTO ReduceIntegralDetail (
ReduceId,
AddId,
UsedValue,
ExpireTime,
UserId,
CreateTime,
UpdateTime
)
SELECT
(SELECT Id FROM @LastInsertId),
RecordId,
Value,
ExpireTime,
UserId,
@Now,
@Now
FROM @ExpiredIntegrals;
COMMIT TRANSACTION;
END;
前端
// types.ts
interface IntegralDetail {
id: number;
type: number;
value: number;
expireTime: string;
reason: string;
createTime: string;
}
interface UseIntegralRequest {
userId: string;
value: number;
reason: string;
}
// api.ts
const api = {
async getUserTotalIntegral(userId: string): Promise<number> {
const response = await fetch(`/api/integral/total/${userId}`);
return response.json();
},
async getUserIntegralDetails(userId: string): Promise<IntegralDetail[]> {
const response = await fetch(`/api/integral/details/${userId}`);
return response.json();
},
async useIntegral(request: UseIntegralRequest): Promise<void> {
const response = await fetch('/api/integral/use', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify(request),
});
if (!response.ok) {
throw new Error('Failed to use integral');
}
},
};
// IntegralPage.tsx
import React, { useEffect, useState } from 'react';
export const IntegralPage: React.FC = () => {
const [totalIntegral, setTotalIntegral] = useState(0);
const [details, setDetails] = useState<IntegralDetail[]>([]);
const [loading, setLoading] = useState(true);
const userId = '1'; // 假设用户ID为1
useEffect(() => {
const loadData = async () => {
try {
const [total, detailsList] = await Promise.all([
api.getUserTotalIntegral(userId),
api.getUserIntegralDetails(userId)
]);
setTotalIntegral(total);
setDetails(detailsList);
} catch (error) {
console.error('Failed to load integral data:', error);
} finally {
setLoading(false);
}
};
loadData();
}, []);
const handleUseIntegral = async (value: number) => {
try {
await api.useIntegral({
userId,
value,
reason: '积分兑换'
});
// 刷新数据
const [total, detailsList] = await Promise.all([
api.getUserTotalIntegral(userId),
api.getUserIntegralDetails(userId)
]);
setTotalIntegral(total);
setDetails(detailsList);
} catch (error) {
console.error('Failed to use integral:', error);
}
};
if (loading) {
return <div>Loading...</div>;
}
return (
<div className="p-4">
<h1 className="text-2xl font-bold mb-4">我的积分</h1>
<div className="bg-white rounded-lg shadow p-6 mb-6">
<div className="text-lg font-semibold">当前可用积分</div>
<div className="text-3xl font-bold text-blue-600">{totalIntegral}</div>
</div>
<div className="bg-white rounded-lg shadow">
<div className="p-4 border-b">
<h2 className="text-lg font-semibold">积分明细</h2>
</div>
<div className="overflow-x-auto">
<table className="w-full">
<thead>
<tr className="bg-gray-50">
<th className="px-6 py-3 text-left">类型</th>
<th className="px-6 py-3 text-left">积分值</th>
<th className="px-6 py-3 text-left">过期时间</th>
<th className="px-6 py-3 text-left">原因</th>
<th className="px-6 py-3 text-left">时间</th>
</tr>
</thead>
<tbody>
{details.map(detail => (
<tr key={detail.id} className="border-t">
<td className="px-6 py-4">
{detail.type === 1 ? '获取' :
detail.type === 2 ? '使用' : '过期'}
</td>
// IntegralPage.tsx continued...
<td className={`px-6 py-4 ${detail.value > 0 ? 'text-green-600' : 'text-red-600'}`}>
{detail.value > 0 ? '+' : ''}{detail.value}
</td>
<td className="px-6 py-4">{new Date(detail.expireTime).toLocaleDateString()}</td>
<td className="px-6 py-4">{detail.reason}</td>
<td className="px-6 py-4">{new Date(detail.createTime).toLocaleString()}</td>
</tr>
))}
{details.length === 0 && (
<tr>
<td colSpan={5} className="px-6 py-4 text-center text-gray-500">
暂无积分记录
</td>
</tr>
)}
</tbody>
</table>
</div>
</div>
{/* 积分使用模块 */}
<div className="mt-6 bg-white rounded-lg shadow p-6">
<h2 className="text-lg font-semibold mb-4">使用积分</h2>
<div className="flex items-center space-x-4">
<input
type="number"
className="border rounded px-4 py-2 w-32"
placeholder="输入积分"
min="1"
max={totalIntegral}
onChange={(e) => setUseIntegralValue(Number(e.target.value))}
value={useIntegralValue}
/>
<button
className="bg-blue-600 text-white px-4 py-2 rounded hover:bg-blue-700 disabled:bg-gray-400"
disabled={!useIntegralValue || useIntegralValue > totalIntegral}
onClick={() => handleUseIntegral(useIntegralValue)}
>
确认使用
</button>
</div>
</div>
</div>
);
};
// IntegralStatistics.tsx - 积分统计组件
import React, { useEffect, useState } from 'react';
import { LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, Legend } from 'recharts';
interface IntegralStatistics {
date: string;
earned: number;
used: number;
expired: number;
}
export const IntegralStatistics: React.FC = () => {
const [statistics, setStatistics] = useState<IntegralStatistics[]>([]);
const [loading, setLoading] = useState(true);
useEffect(() => {
const loadStatistics = async () => {
try {
const response = await fetch('/api/integral/statistics');
const data = await response.json();
setStatistics(data);
} catch (error) {
console.error('Failed to load statistics:', error);
} finally {
setLoading(false);
}
};
loadStatistics();
}, []);
if (loading) {
return <div>Loading statistics...</div>;
}
return (
<div className="bg-white rounded-lg shadow p-6">
<h2 className="text-lg font-semibold mb-4">积分统计</h2>
<div className="w-full h-80">
<LineChart
width={800}
height={300}
data={statistics}
margin={{ top: 5, right: 30, left: 20, bottom: 5 }}
>
<CartesianGrid strokeDasharray="3 3" />
<XAxis dataKey="date" />
<YAxis />
<Tooltip />
<Legend />
<Line type="monotone" dataKey="earned" stroke="#4CAF50" name="获得积分" />
<Line type="monotone" dataKey="used" stroke="#2196F3" name="使用积分" />
<Line type="monotone" dataKey="expired" stroke="#FF5722" name="过期积分" />
</LineChart>
</div>
</div>
);
};
// notification.ts - 积分通知服务
export const IntegralNotification = {
showSuccess(message: string) {
// 实现成功提示
console.log('Success:', message);
},
showError(message: string) {
// 实现错误提示
console.error('Error:', message);
},
showWarning(message: string) {
// 实现警告提示
console.warn('Warning:', message);
}
};
// constants.ts - 常量定义
export const INTEGRAL_TYPES = {
EARN: 1,
USE: 2,
EXPIRE: 3
} as const;
export const INTEGRAL_TYPE_NAMES = {
[INTEGRAL_TYPES.EARN]: '获取积分',
[INTEGRAL_TYPES.USE]: '使用积分',
[INTEGRAL_TYPES.EXPIRE]: '积分过期'
} as const;
// hooks/useIntegral.ts - 积分相关自定义Hook
import { useCallback, useState } from 'react';
export const useIntegral = (userId: string) => {
const [loading, setLoading] = useState(false);
const [error, setError] = useState<string | null>(null);
const refreshIntegral = useCallback(async () => {
setLoading(true);
try {
const [total, details] = await Promise.all([
api.getUserTotalIntegral(userId),
api.getUserIntegralDetails(userId)
]);
return { total, details };
} catch (err) {
setError(err instanceof Error ? err.message : '获取积分信息失败');
return null;
} finally {
setLoading(false);
}
}, [userId]);
const useIntegral = useCallback(async (value: number, reason: string) => {
setLoading(true);
try {
await api.useIntegral({ userId, value, reason });
return true;
} catch (err) {
setError(err instanceof Error ? err.message : '使用积分失败');
return false;
} finally {
setLoading(false);
}
}, [userId]);
return {
loading,
error,
refreshIntegral,
useIntegral,
clearError: () => setError(null)
};
};
现在我们已经完成了完整的前后端实现,包括:
- 后端:
- 实体模型设计
- 业务逻辑服务层
- API控制器
- SQL存储过程
- 前端:
- TypeScript类型定义
- API调用封装
- 积分页面组件
- 积分统计图表
- 通知服务
- 常量定义
- 自定义Hook
主要功能包括:
- 查看当前可用积分总额
- 查看积分明细记录
- 使用积分功能
- 积分统计图表展示
- 过期积分自动处理
使用方法:
- 后端部署:
- 创建数据库表
- 部署API服务
- 配置定时任务执行过期积分处理存储过程
- 前端部署:
- 安装依赖: React, Recharts等
- 配置API地址
- 构建并部署前端应用
- 使用:
# 安装依赖
npm install
# 开发模式运行
npm run dev
# 构建生产版本
npm run build
您可以根据具体需求进行以下扩展:
- 添加更多积分获取途径
- 优化统计图表展示
- 添加积分规则配置
- 实现积分兑换商城
- 添加积分变动通知
- 完善权限控制
需要任何修改或有其他需求都可以告诉我。
扫一扫
7927
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
