/// <summary>
/// 赠送积分
/// </summary>
public void PresentedPoint(string UserId, int Months)
{
int presentedPoint = new AppPoint().CalcPresentedPointForPrec_lf(Months);
if (presentedPoint == 0)
{
return;
}
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var trans = conn.BeginTransaction();
try
{
int pointBalance = 0;
var resultSet = conn.Query("SELECT * FROM Tb_App_UserPoint WHERE UserID=@UserID", new { UserID = UserId }, trans);
// 用户积分记录
if (resultSet.Count() == 0)
{
// 用户积分
conn.Execute(@"INSERT INTO Tb_App_UserPoint(UserID, PointBalance) VALUES(@UserID, @PointBalance)",
new { UserID = UserId, PointBalance = presentedPoint }, trans);
}
else
{
pointBalance = resultSet.FirstOrDefault().PointBalance + presentedPoint;
// 5、更新积分余额
conn.Execute("UPDATE Tb_App_UserPoint SET PointBalance=(PointBalance+@PresentedPoint) WHERE UserID=@UserID",
new
{
PresentedPoint = presentedPoint,
UserID = UserId
}, trans);
}
// 赠送历史
conn.Execute(@"INSERT INTO Tb_App_Point_PresentedHistory(UserID, PresentedWay, PresentedPoints, PointBalance, Remark)
VALUES(@UserID, @PresentedWay, @PresentedPoints, @PointBalance, @PresentedName)",
new
{
UserID = UserId,
PresentedWay = AppPointPresentedWayConverter.GetKey(AppPointPresentedWay.PropertyPrestorePayment),
PresentedPoints = presentedPoint,
PointBalance = pointBalance,
PresentedName = AppPointPresentedWayConverter.GetValue(AppPointPresentedWay.PropertyPrestorePayment)
}, trans);
trans.Commit();
Business.Alipay.Log("支付宝预存下账:赠送积分=" + presentedPoint);
}
catch (Exception ex)
{
trans.Rollback();
Business.Alipay.Log("支付宝预存下账:赠送积分异常，" + ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
/// <summary>
/// 每日签到
/// </summary>
private string DailyCheckIn(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()) || row["UserId"].ToString() == "(null)")
{
return(new ApiResult(false, "UserId不能为空").toJson());
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(new ApiResult(false, "CommunityId不能为空").toJson());
}
string UserId = row["UserId"].ToString();
string CommunityId = row["CommunityId"].ToString();
Tb_Community tb_Community = GetCommunity(CommunityId);
if (null == tb_Community)
{
return(new ApiResult(false, "小区不存在").toJson());
}
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var trans = conn.BeginTransaction();
// 查询是否已经签到
if (conn.Query(@"SELECT * FROM Tb_App_DailyCheckIn WHERE UserID=@UserID AND CheckInTime
BETWEEN convert(varchar(10), getDate(), 120) + ' 00:00:00'
AND convert(varchar(10), getDate(), 120) + ' 23:59:59'", new { UserID = UserId }, trans).Count() > 0)
{
return(new ApiResult(false, "今日已签到").toJson());
}
// 查询该公司是否允许签到奖励
if (!conn.Query <bool>(@"SELECT AllowDailyCheckInAward FROM Tb_Control_AppPoint
WHERE CorpID=@CorpID AND (CommunityID=@CommunityID OR CommunityID IS NULL) AND IsEnable=1",
new { CommunityID = tb_Community.Id, CorpID = tb_Community.CorpID }, trans).FirstOrDefault())
{
trans.Commit();
return(new ApiResult(false, "功能暂未开通").toJson());
}
// 查询该公司是否设置了签到奖励规则
var controlInfo = conn.Query <Tb_Control_AppPoint_DailyCheckIn>(@"SELECT * FROM Tb_Control_AppPoint_DailyCheckIn
WHERE CorpID=@CorpID AND (CommunityID=@CommunityID OR CommunityID IS NULL) AND IsEnable=1",
new { CommunityID = tb_Community.Id, CorpID = tb_Community.CorpID }, trans).FirstOrDefault();
if (controlInfo == null || controlInfo.IsEnable == false)
{
controlInfo = Tb_Control_AppPoint_DailyCheckIn.DefaultControl;
}
try
{
// 用户积分记录
if (conn.Query("SELECT * FROM Tb_App_UserPoint WHERE UserID=@UserID", new { UserID = UserId }, trans).Count() == 0)
{
// 用户积分
conn.Execute(@"INSERT INTO Tb_App_UserPoint(UserID, PointBalance) VALUES(@UserID, @PointBalance)",
new { UserID = UserId, PointBalance = controlInfo.BaseRewardPoints }, trans);
// 签到记录
conn.Execute("INSERT INTO Tb_App_DailyCheckIn(UserID, RewardPoints) VALUES(@UserID, @RewardPoints)",
new { UserID = UserId, RewardPoints = controlInfo.BaseRewardPoints }, trans);
// 赠送历史
conn.Execute(@"INSERT INTO Tb_App_Point_PresentedHistory(UserID, PresentedWay, PresentedPoints, PointBalance, Remark)
VALUES(@UserID, @PresentedWay, @PresentedPoints, @PointBalance, '每日签到')",
new
{
UserID = UserId,
PresentedWay = AppPointPresentedWayConverter.GetKey(AppPointPresentedWay.DailyCheckIn),
PresentedPoints = controlInfo.BaseRewardPoints,
PointBalance = controlInfo.BaseRewardPoints
}, trans);
trans.Commit();
return(new ApiResult(true, new { PointBalance = controlInfo.BaseRewardPoints, RewardPoint = controlInfo.BaseRewardPoints }).toJson());
}
else
{
// 1、获取用户持续签到天数
string sql = $@"DECLARE @now DATETIME=getdate();
SELECT count(*) FROM (
SELECT datediff(DAY, CheckInTime, @now) a, /* 签到时间对比今天的差值 */
row_number() OVER (ORDER BY CheckInTime DESC) b /* 排序字段 */
FROM Tb_App_DailyCheckIn
WHERE UserID=@UserID AND datediff(DAY, CheckInTime, @now)>0 /* 条件排除今天的签到记录 */
) t WHERE a=b;";
int continuousDays = conn.Query <int>(sql, new { UserID = UserId }, trans).FirstOrDefault() + 1;
// 赠送方式
AppPointPresentedWay way = (continuousDays == 1 ? AppPointPresentedWay.DailyCheckIn : AppPointPresentedWay.ContinuousCheckInReward);
// 2、计算奖励的积分数量
int rewardPoints = CalcRewardPoints(continuousDays, controlInfo, out int additionalRewardPoints);
int pointBalance = conn.Query <int>("SELECT PointBalance FROM Tb_App_UserPoint WHERE UserID=@UserID",
new { UserID = UserId }, trans).FirstOrDefault();
// 3、签到记录
conn.Execute(@"INSERT INTO Tb_App_DailyCheckIn(UserID, RewardPoints, AdditionalRewardPoints, IsAdditionalReward)
VALUES(@UserID, @RewardPoints, @AdditionalRewardPoints, @IsAdditionalReward)",
new
{
UserID = UserId,
RewardPoints = controlInfo.BaseRewardPoints,
AdditionalRewardPoints = additionalRewardPoints,
IsAdditionalReward = (additionalRewardPoints > 0 ? 1 : 0)
}, trans);
// 4、插入积分赠送历史
conn.Execute(@"INSERT INTO Tb_App_Point_PresentedHistory(UserID, PresentedWay, PresentedPoints, PointBalance, Remark)
VALUES(@UserID, @PresentedWay, @PresentedPoints, @PointBalance, '每日签到')",
new
{
UserID = UserId,
PresentedWay = AppPointPresentedWayConverter.GetKey(way),
PresentedPoints = (rewardPoints + additionalRewardPoints),
PointBalance = (pointBalance + rewardPoints + additionalRewardPoints)
}, trans);
// 5、更新积分余额
conn.Execute("UPDATE Tb_App_UserPoint SET PointBalance=(PointBalance+@RewardPoint+@AdditionalRewardPoints) WHERE UserID=@UserID",
new
{
RewardPoint = rewardPoints,
AdditionalRewardPoints = additionalRewardPoints,
UserID = UserId
}, trans);
trans.Commit();
return(new ApiResult(true, new { PointBalance = pointBalance, RewardPoint = rewardPoints }).toJson());
}
}
catch (Exception)
{
trans.Rollback();
return(new ApiResult(false, "签到异常").toJson());
}
}
}
/// <summary>
/// 计算赠送积分
/// </summary>
private void PresentedPoint(string bussId, string orderId, decimal paidAmount)
{
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var trans = conn.BeginTransaction();
try
{
int corpId = 0;
string userId = null;
using (var bzconn = new SqlConnection(PubConstant.BusinessContionString))
{
var sql = @"SELECT BussNature FROM Tb_System_BusinessCorp WHERE BussId=@BussId;
SELECT CorpID FROM Unified..Tb_Community WHERE Id=(SELECT CommunityId FROM Tb_System_BusinessConfig
WHERE BussId=@BussId);
SELECT CorpID FROM Tb_System_BusinessCorp_Config WHERE BussId=@BussId";
var reader = bzconn.QueryMultiple(sql, new { BussId = bussId });
var bussNature = reader.Read <string>().FirstOrDefault();
if (bussNature == "平台商城")
{
Business.Alipay.Log("支付宝购物:未赠送积分，平台商城不赠送积分");
return;
}
var nature_1 = reader.Read <int>().FirstOrDefault();
var nature_2 = reader.Read <int>().FirstOrDefault();
if (nature_1 != 0)
{
corpId = nature_1;
}
if (nature_2 != 0)
{
corpId = nature_2;
}
userId = bzconn.Query <string>(@"SELECT UserId FROM Tb_Charge_Receipt WHERE OrderId=@OrderId", new { OrderId = orderId }).FirstOrDefault();
if (string.IsNullOrEmpty(userId))
{
Business.Alipay.Log("支付宝购物:未赠送积分，未找到用户信息");
return;
}
}
var rules = conn.Query <Tb_App_Point_PropertyPresentedRule>(@"SELECT * FROM Tb_App_Point_PropertyPresentedRule
WHERE CorpID=@CorpID AND CommunityID IS NULL AND PresentedObject=@PresentedObject AND IsDelete=0
AND getdate() BETWEEN StartTime AND EndTime ORDER BY ConditionAmount",
new
{
CorpID = corpId,
PresentedObject = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.Goods)
}, trans);
if (rules.Count() > 0)
{
Tb_App_Point_PropertyPresentedRule current = null;
foreach (var item in rules)
{
if (paidAmount >= item.ConditionAmount)
{
current = item;
}
}
if (current != null)
{
var userPoint = conn.Query("SELECT * FROM Tb_App_UserPoint WHERE UserID=@UserID", new { UserID = userId }, trans).FirstOrDefault();
int balance = 0;
if (userPoint == null)
{
balance = 0;
conn.Execute(@"INSERT INTO Tb_App_UserPoint(UserID, PointBalance) VALUES(@UserID, @PointBalance)",
new { UserID = userId, PointBalance = current.PresentedPoints }, trans);
}
else
{
balance = userPoint.PointBalance;
conn.Execute("UPDATE Tb_App_UserPoint SET PointBalance=(PointBalance+@PresentedPoints) WHERE UserID=@UserID",
new
{
PresentedPoints = current.PresentedPoints,
UserID = userId
}, trans);
}
// 赠送历史
conn.Execute(@"INSERT INTO Tb_App_Point_PresentedHistory(UserID,PresentedWay,PresentedPoints,PointBalance,Remark)
VALUES(@UserID, @PresentedWay, @PresentedPoints, @PointBalance, @Remark)",
new
{
UserID = userId,
PresentedWay = AppPointPresentedWayConverter.GetKey(AppPointPresentedWay.StoreTrade),
PresentedPoints = current.PresentedPoints,
PointBalance = balance + current.PresentedPoints,
Remark = AppPointPresentedWayConverter.GetValue(AppPointPresentedWay.StoreTrade),
}, trans);
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Business.Alipay.Log("支付宝购物:计算赠送积分异常，" + ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
/// <summary>
/// 计算赠送积分
/// </summary>
private void PresentedPoint(string communityId, string orderId, string userId, string useHistoryId, decimal paidAmount)
{
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
if (appConn.State == ConnectionState.Closed)
{
appConn.Open();
}
var trans = appConn.BeginTransaction();
short corpId = 0;
try
{
decimal propertyMaxDiscountsAmount = 0.0m; // 物管费最大可抵用金额
decimal parkingMaxDiscountsAmount = 0.0m; // 车位费最大可抵用金额
var deductionObject = new List <string>(); // 积分可抵扣对象
using (var erpConn = new SqlConnection(PubInfo.GetConnectionStr(PubInfo.GetCommunity(communityId))))
{
var feesIds = erpConn.Query <long>(@"SELECT FeesId FROM Tb_OL_AlipayDetail WHERE PayOrderId=
(SELECT Id FROM Tb_OL_AlipayOrder WHERE out_trade_no=@out_trade_no)",
new { out_trade_no = orderId });
if (feesIds.Count() == 0)
{
return;
}
#region 获取积分抵扣规则
// 企业编号
corpId = appConn.Query <short>("SELECT CorpID FROM Tb_Community WHERE Id=@CommunityId",
new { CommunityId = communityId }, trans).FirstOrDefault();
// 积分权限控制
var controlInfo = appConn.Query <Tb_Control_AppPoint>(@"SELECT * FROM Tb_Control_AppPoint WHERE CorpID=@CorpID AND IsEnable=1
AND (CommunityID=@CommunityId OR CommunityID IS NULL) ORDER BY CommunityID DESC",
new { CorpID = corpId, CommunityId = communityId }, trans).FirstOrDefault();
if (controlInfo == null || controlInfo.IsEnable == false)
{
controlInfo = Tb_Control_AppPoint.DefaultControl;
controlInfo.CommunityID = Guid.Empty.ToString();
}
// 允许抵用物业费
if (controlInfo.AllowDeductionPropertyFees)
{
deductionObject.Add($@"'{AppPointUsableObjectConverter.GetKey(AppPointUsableObject.PropertyFee)}'");
}
// 允许抵用车位费
if (controlInfo.AllowDeductionParkingFees)
{
deductionObject.Add($@"'{AppPointUsableObjectConverter.GetKey(AppPointUsableObject.ParkingFee)}'");
}
#endregion
// 不计算违约金
string sql = $@"SELECT isnull(sum(isnull(DebtsAmount,0)),0)
FROM view_HSPR_Fees_Filter WHERE FeesID IN({string.Join(",", feesIds)}) AND SysCostSign = 'B0001';
SELECT isnull(sum(isnull(DebtsAmount,0)),0)
FROM view_HSPR_Fees_Filter WHERE FeesID IN({string.Join(",", feesIds)}) AND SysCostSign = 'B0002';";
var reader = erpConn.QueryMultiple(sql);
// 物业费、车位费实际欠费总额
decimal propertyAmount = reader.Read <decimal>().FirstOrDefault();
decimal parkingAmount = reader.Read <decimal>().FirstOrDefault();
// 积分抵用了部分金额
if (!string.IsNullOrEmpty(useHistoryId))
{
// 积分抵用的金额数量
var deductionAmount = appConn.Query <decimal>(@"SELECT isnull(DeductionAmount,0) FROM Tb_App_Point_UseHistory WHERE IID=@IID;",
new { IID = useHistoryId }, trans).FirstOrDefault();
// 计算相关抵用的金额
if (deductionAmount != 0)
{
// 积分抵用规则
var ruleInfo = appConn.Query($@"SELECT IID,ConditionAmount,DiscountsAmount,DeductionObject,b.Remark AS SysCostSign,StartTime,EndTime
FROM Tb_App_Point_PropertyDeductionRule a LEFT JOIN Tb_Dictionary_Point_UsableObject b
ON a.DeductionObject=b.[Key]
WHERE CommunityID=@CommunityId AND DeductionObject IN({string.Join(", ", deductionObject) })
AND getdate() BETWEEN StartTime AND EndTime AND a.IsDelete=0 ORDER BY ConditionAmount,DiscountsAmount",
new { CommunityId = communityId }, trans);
if (ruleInfo.Count() == 0)
{
Business.Alipay.Log("支付宝下账:计算赠送积分失败，相关积分抵用规则已被禁用");
return;
}
// 确定物管费可抵用金额
if (propertyAmount > 0)
{
string key = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.PropertyFee);
foreach (var item in ruleInfo)
{
if (item.DeductionObject == key && propertyAmount >= item.ConditionAmount)
{
propertyMaxDiscountsAmount = item.DiscountsAmount;
}
}
}
// 确定车位费可抵用金额
if (parkingAmount > 0)
{
string key = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.ParkingFee);
foreach (var item in ruleInfo)
{
if (item.DeductionObject == key && parkingAmount >= item.ConditionAmount)
{
parkingMaxDiscountsAmount = item.DiscountsAmount;
}
}
}
// 积分数量不正常
if (deductionAmount > (parkingMaxDiscountsAmount + propertyMaxDiscountsAmount))
{
Business.Alipay.Log("支付宝下账:计算赠送积分失败，积分实际抵用金额超出可抵用金额");
return;
}
else
{
decimal tmp = deductionAmount;
// 部分抵扣物业费
if (propertyMaxDiscountsAmount != 0)
{
if (tmp <= propertyMaxDiscountsAmount)
{
propertyMaxDiscountsAmount = tmp;
tmp = 0;
}
else
{
tmp -= propertyMaxDiscountsAmount;
}
}
// 部分抵扣车位费
if (parkingMaxDiscountsAmount != 0 && tmp > 0)
{
if (tmp <= parkingMaxDiscountsAmount)
{
parkingMaxDiscountsAmount = tmp;
tmp = 0;
}
else
{
tmp -= parkingMaxDiscountsAmount;
}
}
if (tmp != 0)
{
Business.Alipay.Log("支付宝下账:计算赠送积分失败，积分实际抵用金额与可抵用金额不相等，可能是更改了积分抵用规则");
return;
}
}
}
}
// 计算要赠送的积分数量
new AppPoint().CalcPresentedPointForPropertyFees(communityId, propertyAmount - propertyMaxDiscountsAmount, parkingAmount - parkingMaxDiscountsAmount, out int p1, out int p2);
if (p1 == 0 && p2 == 0)
{
return;
}
int presentedPoints = p1 + p2;
var userPoint = appConn.Query("SELECT * FROM Tb_App_UserPoint WHERE UserID=@UserID", new { UserID = userId }, trans).FirstOrDefault();
int balance = 0;
if (userPoint == null)
{
balance = 0;
appConn.Execute(@"INSERT INTO Tb_App_UserPoint(UserID, PointBalance) VALUES(@UserID, @PointBalance)",
new { UserID = userId, PointBalance = presentedPoints }, trans);
}
else
{
balance = userPoint.PointBalance;
appConn.Execute("UPDATE Tb_App_UserPoint SET PointBalance=(PointBalance+@PresentedPoints) WHERE UserID=@UserID",
new
{
PresentedPoints = presentedPoints,
UserID = userId
}, trans);
}
// 赠送历史
appConn.Execute(@"INSERT INTO Tb_App_Point_PresentedHistory(UserID, PresentedWay, PresentedPoints, PointBalance, Remark)
VALUES(@UserID, @PresentedWay, @PresentedPoints, @PointBalance, @Remark)",
new
{
UserID = userId,
PresentedWay = AppPointPresentedWayConverter.GetKey(AppPointPresentedWay.PropertyArrearsPayment),
PresentedPoints = presentedPoints,
PointBalance = balance + presentedPoints,
Remark = AppPointPresentedWayConverter.GetValue(AppPointPresentedWay.PropertyArrearsPayment),
}, trans);
Business.Alipay.Log("支付宝下账:赠送积分=" + presentedPoints);
// 力帆，缴清，额外赠送
if (corpId == 2015)
{
// 查询CustID、RoomID
var custInfo = erpConn.Query(@"SELECT CustID,RoomID FROM Tb_HSPR_Fees WHERE FeesID=
(SELECT TOP 1 FeesId FROM Tb_OL_AlipayDetail WHERE PayOrderId IN
(SELECT Id FROM Tb_OL_AlipayOrder WHERE out_trade_no=@out_trade_no))",
new { out_trade_no = orderId }).FirstOrDefault();
if (custInfo != null && custInfo.CustID != 0 && custInfo.RoomID != 0)
{
// 无欠费
if (erpConn.Query(@"SELECT * FROM Tb_HSPR_Fees WHERE CustID=@CustID AND RoomID=@RoomID AND isnull(IsCharge,0)=0",
new { CustID = custInfo.CustID, RoomID = custInfo.RoomID }).Count() == 0)
{
int extraPoints = new AppPoint().CalcPresentedPointForPayAll();
if (extraPoints > 0)
{
presentedPoints += extraPoints;
// 赠送积分
appConn.Execute(@"UPDATE Tb_App_UserPoint SET PointBalance=(PointBalance+@PresentedPoints) WHERE UserID=@UserID;
INSERT INTO Tb_App_Point_PresentedHistory(UserID, PresentedWay, PresentedPoints, PointBalance, Remark)
VALUES(@UserID, @PresentedWay, @PresentedPoints, @PointBalance, @Remark);",
new
{
UserID = userId,
PresentedWay = AppPointPresentedWayConverter.GetKey(AppPointPresentedWay.PropertyArrearsPayment),
PresentedPoints = extraPoints,
PointBalance = balance + presentedPoints,
Remark = "物业欠费缴清赠送",
}, trans);
}
}
}
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Business.Alipay.Log("支付宝下账:计算赠送积分异常，" + ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
