前言
知识付费已不再陌生, 这里对知识付费浅谈我的理解:
知识付费:主要指知识的接收者为所阅览知识付出资金的现象。
知识付费平台:让知识获得者间接向知识传播者和创造者给予报酬
知识服务平台有非常多,大致可分为以下几类:
(1)社群类:微信、知识星球
(2)社区类: 知乎、 简书、 豆瓣
(3)问答类: 知乎、在行
(4)读书类:得到、樊登读书、有书
(5)音频类:千聊、荔枝微课、喜马拉雅
(6)视频/直播类:抖音、 快手、微信视频号
(7)在线教育:腾讯课堂、网易云课堂
(8)第三方知识服务平台:小鹅通、有赞、鲸打卡
总之,为任何非实物商品支付的行为都可称之为知识付费
本系统仅对付费阅读+付费下载做设计和展开;记录我从0开始的全过程实录(欢迎朋友们拍砖和指正……)
需求&目标
付费阅读和付费下载本文中统称为【知识】
- 付费后阅读内容
- 付费后下载文件
- 可部分阅读内容
- 可预览文件内容
- 可独立设置知识的付费模式和金额
- 支持分享免费、收费设置
- 知识可分为平台和个人用户提供(暂时仅支持平台知识分享)
- 知识可设置分类,标签(用户知识关联推荐)
- 提供知识列表
- 提供分类或标签查询
- 知识预览详情页面,底部提供相关知识推荐
- 提供我的下载列表页
- 提供我的收藏功能
- 付费下载后重复下载免费
- 如果知识需要分享助力, 可显示助力进度和人数
- 需预留个人用户知识分享提成功能
需求与目标先整理到这里, 明天直接开干数据库设计
== 2022-11-14晚 ==
数据库设计
数据库— Mysql,表1:附件表、表2:知识产品表、表3:用户订单表、表4:用户行为表等
1. 附件表
CREATE TABLE `t_files` (
`filecode` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件编码',
`fileName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件名',
`fileType` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件类型:专业分,调档线,省控线',
`localshow` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '显示位置:对应wx文件名,如pages/home/filedownLoad,多个用;隔开',
`suffix` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '后缀名',
`fullpath` varchar(400) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件全路径',
`imgpath` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
PRIMARY KEY (`filecode`)
)
AUTO_INCREMENT=1
;
2.知识商品表
CREATE TABLE `t_kh_product` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '知识产品ID',
`productCode` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品编号',
`sellerID` int(11) NULL DEFAULT NULL COMMENT '商家ID-用户ID',
`title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标题',
`subTitle` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '子标题',
`publicStr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '公开内容',
`payStr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '付费内容',
`price` float NOT NULL COMMENT '价格',
`is_marketable` int(11) NULL DEFAULT NULL COMMENT '是否上架 1上架,0下架 -1:删除',
`fileID` int(11) NOT NULL DEFAULT 0 COMMENT '文件下载ID',
`views` int(11) NOT NULL DEFAULT 0 COMMENT '浏览次数',
`dlcount` int(11) NULL DEFAULT 0 COMMENT '下载统计',
`prefer` int(11) NULL DEFAULT NULL COMMENT '点赞次数',
`nogood` int(11) NULL DEFAULT NULL COMMENT '拍砖次数',
`ctime` datetime NULL DEFAULT NULL COMMENT '创建时间',
`utime` datetime NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
AUTO_INCREMENT=1
;
3.知识商品订单表
CREATE TABLE `t_kh_order` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单号',
`orderNum` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单号',
`userID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '购买者ID',
`productName` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '订单名字',
`productID` int(11) NULL DEFAULT NULL COMMENT '商品ID',
`price` float NULL DEFAULT NULL COMMENT '订单价格',
`cratetime` datetime NULL DEFAULT NULL COMMENT '订单时间',
`status` int(11) NULL DEFAULT NULL COMMENT '订单状态:1,支持成功; -1订单退款,0订单支付中',
`sellerID` int(11) NULL DEFAULT NULL COMMENT '商家ID',
`payTyle` int(11) NULL DEFAULT -1 COMMENT '1微信,2支付宝,3分享后获得,4商家活动',
`shareNum` int(11) NULL DEFAULT 0 COMMENT '分享次数, 当分享次数大于商品指定次数时获得',
`ctime` datetime NULL DEFAULT NULL COMMENT '创建时间',
`utime` datetime NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
AUTO_INCREMENT=1
COMMENT='用户订单表'
4.用户行为表
CREATE TABLE `t_kn_user_action` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户收藏&浏览表',
`productID` int(11) NOT NULL COMMENT '知识产品ID',
`userID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
`actionType` int(11) NOT NULL COMMENT '行为类型:1 浏览,2 收藏,3购买,4 点赞,5 拍砖. ',
`ctime` datetime NULL DEFAULT NULL,
`utime` datetime NULL DEFAULT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
AUTO_INCREMENT=1
COMMENT='用户行为表,包含阅读,收藏,购买记录, 点赞等';
明天开始进入开发,开发环境准备可见
== 2022-11-15晚 ==
后台代码
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Data;
namespace KH
{
/// <summary>
/// 知识付费
/// </summary>
public class KnowhowController : BaseController
{
// <summary>
/// 获取商品列表
/// </summary>
/// <param name="categoryKey">分类名称</param>
/// <param name="pageIndex">页码</param>
/// <param name="khKey">知识点名称</param>
/// <param name="provinceID">省份</param>
/// <param name="tagKey">标签</param>
/// <returns></returns>
[HttpGet("getLists")]
public JsonResult GetLists(int provinceID, int pageIndex, string categoryKey, string tagKey, string khKey)
{
string sql = @"SELECT ID,productCode,title,subTitle,provinceID,categoryStr,categoryID,tagStrs,publicStr,price,
is_marketable,fileSrc,views,dlcount,prefer,nogood,ctime,utime
from t_kh_product where 1=1";
if (provinceID != 0)
{
sql += " and (provinceID = @provinceID or provinceID = 0)";
}
if (!string.IsNullOrEmpty(categoryKey))
{
sql += " and categoryStr like CONCAT('%',@categoryKey,'%')";
}
if (!string.IsNullOrEmpty(tagKey))
{
sql += " and tagStrs like CONCAT('%',@tagKey,'%')";
}
if (!string.IsNullOrEmpty(khKey))
{
sql += " and title like CONCAT('%', @khKey,'%')";
}
sql += " order by utime desc limit " + pageIndex * 10 + ",10";
DataTable dt = Common.GetDataTable(sql, new()
{
{ "provinceID", provinceID },
{ "categoryKey", categoryKey },
{ "tagKey", "%" + tagKey + "%" },
{ "khKey", "%" + khKey + "%" }
});
return Json(dt.ToJson());
}
/// <summary>
/// 用户知识产品行为表, 同时更新产品表相应数值
/// </summary>
/// <param name="productID">知识产品ID</param>
/// <param name="userID">用户ID</param>
/// <param name="actionType">行为类别 行为类型:1 浏览,2 收藏,3购买,4 点赞,5 拍砖,6下载 </param>
/// <param name="isAdd">是否添加</param>
/// <returns></returns>
[HttpGet("userAction")]
public JsonResult UserAction(string productID, string userID, int actionType, bool isAdd)
{
string sql = @"SELECT 1";
string usql = "update t_kh_product set ID = ID";
//取消操作,如取消收藏,点赞取消, 拍砖取消
if (isAdd == false)
{
sql = @"DELETE from t_kh_user_action WHERE productID = @productID AND userID = @userid AND actionType = @actionType";
switch (actionType)
{
case 2://
usql += ",favorites = favorites-1";
break;
case 4://点赞
usql += ",prefer = prefer-1";
break;
case 5://拍砖
usql += ",nogood = nogood-1";
break;
default:
break;
}
}
else
{
sql = @"insert into t_kh_user_action(productID,userID,actionType,ctime,utime) values(@productID,@userID,@actionType,now(),now())";
switch (actionType)
{
case 1://浏览
usql += ",views = views+1";
break;
case 2://收藏
usql += ",favorites = favorites+1";
break;
case 3://下载
usql += ",sellNUm = sellNUm+1";
break;
case 4://点赞
usql += ",prefer = prefer+1";
break;
case 5://拍砖
usql += ",nogood = nogood+1";
break;
case 6://下载
usql += ",dlcount = dlcount+1";
break;
default:
break;
}
}
usql = " where ID = @productID";
int num = Common.ExecuteNonQuery(sql + ";" + usql, new()
{
{ "productID", productID },
{ "userID", userID },
{ "actionType", actionType }
});
return Json(num);
}
/// <summary>
/// 知识商品详细,如果未支付则不显示付费内容
/// </summary>
/// <param name="productID">商品ID</param>
/// <param name="userid">用户ID</param>
/// <returns></returns>
[HttpGet("getProductInfo")]
public JsonResult GetProductInfo(string productID,string userid)
{
string sql = @"
SELECT a.ID,productCode,title,subTitle,provinceID,categoryStr,categoryID,
tagStrs,publicStr,payStr,a.price,
is_marketable,fileSrc,views,dlcount,prefer,nogood,a.ctime,a.utime,b.status
from t_kh_product a LEFT JOIN t_kh_order b
on a.ID = b.productID and userID = @userid
where a.ID = @ID;";
Dictionary<string, object> rdic = new();
DataTable dt = Common.GetDataTable(sql, new()
{
{ "ID", productID },
{ "userid", userid }
});
if (dt.Rows.Count > 0)
{
rdic = dt.Rows[0].ToJson();
if (rdic["STATUS"].ToString() != "1")
{
rdic.Remove("PAYSTR");//如果未支付则不显示付费内容
}
}
return Json(rdic);
}
/// <summary>
/// 生成订单
/// </summary>
/// <param name="productID"></param>
/// <returns></returns>
[HttpGet("booking")]
public JsonResult PayBooking(string productID)
{
string sql = @"selelct title,price from t_kh_product where ID = @productID";
DataTable dt = Common.GetDataTable(sql, new()
{
{ "productID", productID }
});
if (dt.Rows.Count < 1)
{
return null;
}
Dictionary<string, object> pairs = new();
string orderid = DateTime.Now.Ticks.ToString();
pairs.Add("money", dt.Rows[0]["price"]);
pairs.Add("productName", dt.Rows[0]["title"]);
pairs.Add("orderNum", orderid);
pairs.Add("userID", UserID);
pairs.Add("productID", productID);
//订单列表并给支付成功后回调更新
Common.ExecuteNonQuery(@"insert into t_kh_order(orderNum,userID,productName,productID,price,createtime,`status`,payTyle,ctime,utime)
values(@orderNum,@userID,@productName,@productID,@price,now(),0,0,now(),now())",pairs);
return Json(pairs);
}
}
}
明天开始进入前端页面的设计和开发中
== 2022-11-16晚 ==
前端小程序
1、知识列表
可按用户登录省份默认分别展示资源
<!--学子宏图微信小程序数据下载-->
<view class="page">
<view class="FilterView">
<!-- 省份筛选, 默认为用户当前省份-->
<view class="weui-cell weui-cell_access special-link filter_province">
<picker class="weui-cell__bd" data-type="1" bindchange="bindPickerChange" range="{{provincesDic}}" range-key="name" value="{{pickerIndex}}">
省份: {{provincesDic[pickerIndex].name}}
</picker>
<view class="weui-cell__ft weui-cell__ft_in-access" style="margin-left: -30px;"> </view>
</view>
<!-- 文件付费类型, 免费, 会员免费(已支付AI志愿会员),付费-->
<view class="weui-cell weui-cell_access special-link filter_fileType">
<picker class="weui-cell__bd" data-type="2" bindchange="bindPickerChange" range="{{fileTypes}}" value="{{typeIndex}}">
类型: {{fileTypes[typeIndex]}}
</picker>
<view class="weui-cell__ft weui-cell__ft_in-access" style="margin-left: -30px;"> </view>
</view>
<!--排序方式, 最热, 推荐, 最新, 下载量-->
<view class="weui-cell weui-cell_access special-link filter_sort">
<picker class="weui-cell__bd" data-type="3" bindchange="bindPickerChange" range="{{sorts}}" value="{{sortIndex}}">
排序: {{sorts[sortIndex]}}
</picker>
<view class="weui-cell__ft weui-cell__ft_in-access" style="margin-left: -30px;"> </view>
</view>
</view>
<view class="fileList">
<view wx:for="{{KHItems}}" class="fileItems" wx:key="item">
<view data-id="{{item.ID}}" class="weui-cell weui-cell_access special-link" bindtap="onShowInfo" data-item="{{item}}">
<view class="preImg">
<image src="{{item.PREIMGSRC}}"></image>
<image src="https://www.luqub.cn/678img/home/pdf.png" style="width: 32px;height:32px;margin-left: -32px;border: 0px;"></image>
</view>
<view class="weui-cell__bd">
<view class="khtitle">{{item.TITLE}}</view>
<view class="khbottomTags">
<font class="Tags">{{item.SUFFIX}}</font>
<font class="Tags">{{(item.ISFREE==1?'免费':'¥'+item.PRICE/100+'元')}}</font>
<font class="Tags">{{item.CTIME}}</font>
</view>
<view class="khbottomTags">
<font class="Tags">查看:{{item.VIEWS}}</font>
<font class="Tags">点赞:{{item.PREFER}} </font>
<font class="Tags">下载:{{item.DLCOUNT}}</font>
</view>
</view>
<view class="weui-cell__ft weui-cell__ft_in-access"> </view>
</view>
</view>
</view>
</view>
2、内容详情
<!--学子宏图微信小程序数据页详细-->
<view class="page">
<view class="topBarNavigation">
<view>学子数据</view>
<view> > {{khData.CATEGORYSTR}}</view>
<view> > {{khData.PROVINCENAME}}</view>
</view>
</view>
<view class="content">
<view class="fileTitle">{{khData.TITLE}}</view>
<view class="subTitle">{{khData.SUBTITLE}}</view>
<rich-text class="richTextPublicStr" nodes="{{khData.PUBLICSTR}}">
</rich-text>
<view class="preImg">
<image bindtap="openimgview" data-img="{{khData.PREIMGSRC}}" src="{{khData.PREIMGSRC}}"></image>
</view>
</view>
效果截图
详细
后续再实现点赞,支付等功能即可