/// <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); } } }