/// <summary> /// 物业相关费用缴费时锁定积分 /// </summary> private string LockedPointsOnPayPropertyFees(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(new ApiResult(false, "UserId不能为空").toJson()); } if (!row.Table.Columns.Contains("UsePoints") || string.IsNullOrEmpty(row["UsePoints"].ToString())) { return(new ApiResult(false, "UsePoints不能为空").toJson()); } string UserId = row["UserId"].ToString(); int UsePoints = AppGlobal.StrToInt(row["UsePoints"].ToString()); using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { if (conn.State == ConnectionState.Closed) { conn.Open(); } var trans = conn.BeginTransaction(); // 要使用的积分是否大于用户积分余额 int balance = conn.Query <int>("SELECT PointBalance FROM Tb_App_UserPoint WHERE UserID=@UserID", new { UserID = UserId }, trans).FirstOrDefault(); if (balance < UsePoints) { return(new ApiResult(false, "积分余额不足").toJson()); } string useHistoryID = Guid.NewGuid().ToString(); conn.Execute($@"UPDATE Tb_App_UserPoint SET PointBalance=(PointBalance-@UsePoints) WHERE UserID=@UserID; INSERT INTO Tb_App_Point_UseHistory(IID, UserID, UseWay, UsePoints, PointBalance) VALUES(@UseHistoryID, @UserID, @UseWay, @UsePoints, @PointBalance); INSERT INTO Tb_App_Point_Locked(UserID, UseHistoryID, LockedPoints) VALUES (@UserID, @UseHistoryID, @LockedPoints);", new { UsePoints = UsePoints, UserID = UserId, UseHistoryID = useHistoryID, UseWay = AppPointUseWayConverter.GetKey(AppPointUseWay.PropertyFeeDeduction), PointBalance = balance - UsePoints, LockedPoints = UsePoints }, trans); return(new ApiResult(true, useHistoryID).toJson()); } }
/// <summary> /// 生成银行及物业订单 /// </summary> /// <param name="Row"></param> /// <returns></returns> public string GenerateOrder(DataRow Row) { bool IsBankOk = false; bool IsPropertyOk = false; string PropertyOrderId = ""; string prepay_str = ""; string CommunityId = Row["CommunityId"].ToString(); string FeesIds = Row["FeesIds"].ToString(); string txnTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string CustID = Row["CustID"].ToString(); string UserId = null; if (Row.Table.Columns.Contains("UserID") && !string.IsNullOrEmpty(Row["UserID"].ToString())) { UserId = Row["UserID"].ToString(); } int UsePoints = 0; if (Row.Table.Columns.Contains("UsePoints") && !string.IsNullOrEmpty(Row["UsePoints"].ToString())) { UsePoints = AppGlobal.StrToInt(Row["UsePoints"].ToString()); } //增加FeesIds重复验证 if (FeesIds == "") { return(JSONHelper.FromString(false, "未选择任何费用")); } string[] feesArray = FeesIds.Split(',').Distinct().ToArray(); FeesIds = string.Join(",", feesArray.ToArray()); bool IsConfig = GenerateConfig(CommunityId); if (IsConfig == false) { return(JSONHelper.FromString(false, "该小区不支持支付宝支付")); } PubConstant.hmWyglConnectionString = GetConnection(CommunityId); using (var appConn = new SqlConnection(PubConstant.UnifiedContionString)) { if (appConn.State == ConnectionState.Closed) { appConn.Open(); } var appTrans = appConn.BeginTransaction(); // 检查数据 string useHistoryID = null; decimal Amount = 0.0m; // 缴费总额 decimal propertyAmount = 0.0m; // 物业费缴费总额 decimal parkingAmount = 0.0m; // 车位费缴费总额 decimal deductionAmount = 0.0m; // 积分可抵扣金额 decimal propertyMaxDiscountsAmount = 0.0m; // 物管费最大可抵用金额 decimal parkingMaxDiscountsAmount = 0.0m; // 车位费最大可抵用金额 int pointBalance = 0; // 积分余额 var deductionObject = new List <string>(); // 积分可抵扣对象 #region 获取积分抵扣规则 // 要使用的积分是否大于用户积分余额 pointBalance = appConn.Query <int>("SELECT PointBalance FROM Tb_App_UserPoint WHERE UserID=@UserID", new { UserID = UserId }, appTrans).FirstOrDefault(); if (pointBalance < UsePoints) { return(new ApiResult(false, "积分余额不足").toJson()); } // 企业编号 short corpId = appConn.Query <short>("SELECT CorpID FROM Tb_Community WHERE Id=@CommunityId", new { CommunityId = CommunityId }, appTrans).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 }, appTrans).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 #region 获取缴费列表,读取欠费总额 using (IDbConnection erpConn = new SqlConnection(PubConstant.hmWyglConnectionString)) { try { IEnumerable <dynamic> arrearsList = erpConn.Query($@"SELECT * FROM view_HSPR_Fees_Filter WHERE FeesID IN({FeesIds})"); // 允许抵用物业费 if (controlInfo.AllowDeductionPropertyFees) { foreach (dynamic item in arrearsList) { if (item.SysCostSign != null && item.SysCostSign.ToString() == "B0001") { propertyAmount += item.DebtsAmount; } } } // 允许抵用车位费 if (controlInfo.AllowDeductionParkingFees) { foreach (dynamic item in arrearsList) { if (item.SysCostSign != null && item.SysCostSign.ToString() == "B0002") { parkingAmount += item.DebtsAmount; } } } } catch (Exception) { appTrans.Rollback(); return(new ApiResult(false, "验证欠费列表失败").toJson()); } } #endregion if (UsePoints > 0 && !string.IsNullOrEmpty(UserId)) { #region 判断积分是否足够 try { if (deductionObject.Count == 0) { return(new ApiResult(false, "暂不支持积分抵用功能").toJson()); } // 积分抵用规则 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 }, appTrans); if (ruleInfo.Count() == 0) { return(new ApiResult(false, "积分抵用规则未设置或已失效").toJson()); } // 确定物管费可抵用金额 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; } } } // 要使用积分抵用的金额 deductionAmount = UsePoints / (decimal)controlInfo.PointExchangeRatio; // 积分数量不正常 if (deductionAmount > (parkingMaxDiscountsAmount + propertyMaxDiscountsAmount)) { return(new ApiResult(false, "使用积分超过可抵用最大金额").toJson()); } 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) { return(new ApiResult(false, "积分抵用出错").toJson()); } } if (UsePoints > 0 && deductionAmount > 0) { useHistoryID = Guid.NewGuid().ToString(); } } catch (Exception ex) { appTrans.Rollback(); return(new ApiResult(false, "积分验证失败" + ex.Message + ex.StackTrace).toJson()); } #endregion } // 积分数量正常,生成订单 try { // 生成ERP账单 string PropertyResult = GeneratePropertyOrder(CommunityId, FeesIds, txnTime, CustID, ref IsPropertyOk, ref Amount, ref PropertyOrderId); // ERP订单生成成功 if (IsPropertyOk == true) { // 应缴总金额 Amount = Amount - deductionAmount; // 支付宝签名订单信息 string BankResult = GenerateBankOrder(CommunityId, UserId, feesArray.First(), PropertyOrderId, txnTime, Amount, ref IsBankOk, ref prepay_str); if (IsBankOk == true) { // 计算预赠送积分 new AppPoint().CalcPresentedPointForPropertyFees(CommunityId, propertyAmount - propertyMaxDiscountsAmount, parkingAmount - parkingMaxDiscountsAmount, out int p1, out int p2); prepay_str = prepay_str.Insert(prepay_str.Length - 1, ",\"presented_points\":" + (p1 + p2) + ""); if (!string.IsNullOrEmpty(useHistoryID)) { prepay_str = prepay_str.Insert(prepay_str.Length - 1, ",\"deduction_amount\":" + deductionAmount + ""); prepay_str = prepay_str.Insert(prepay_str.Length - 1, ",\"point_use_history_id\":\"" + useHistoryID + "\""); appConn.Execute($@"UPDATE Tb_App_UserPoint SET PointBalance=(PointBalance-@UsePoints) WHERE UserID=@UserID; INSERT INTO Tb_App_Point_UseHistory(IID, UserID, UseWay, UsePoints, PointBalance, DeductionAmount, Remark) VALUES(@UseHistoryID, @UserID, @UseWay, @UsePoints, @PointBalance, @DeductionAmount, @Remark); INSERT INTO Tb_App_Point_Locked(UserID, UseHistoryID, LockedPoints) VALUES (@UserID, @UseHistoryID, @LockedPoints);", new { UsePoints = UsePoints, UserID = UserId, UseHistoryID = useHistoryID, UseWay = AppPointUseWayConverter.GetKey(AppPointUseWay.PropertyFeeDeduction), PointBalance = pointBalance - UsePoints, DeductionAmount = deductionAmount, Remark = AppPointUseWayConverter.GetValue(AppPointUseWay.PropertyFeeDeduction), LockedPoints = UsePoints }, appTrans); string usableObject = string.Join(",", deductionObject.Select(obj => AppPointUsableObjectConverter.GetValue(obj.Replace("'", "")))); // 存储积分使用记录与订单关联关系 appConn.Execute(@"INSERT INTO Tb_App_Point_UseHistoryOrder(UseHistoryID, OrderID, Payment, UsableObject) VALUES(@UseHistoryID, @OrderID, '支付宝', @UsableObject)", new { UseHistoryID = useHistoryID, OrderID = PropertyOrderId, UsableObject = usableObject }, appTrans); } using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString)) { // 更新订单 erpConn.Execute(@"UPDATE Tb_OL_AlipayOrder SET prepay_str=@prepay_str WHERE out_trade_no = @out_trade_no ", new { prepay_str = prepay_str.ToString(), out_trade_no = PropertyOrderId }); } appTrans?.Commit(); return(JSONHelper.FromJsonString(true, prepay_str)); } else { appTrans?.Rollback(); return(JSONHelper.FromString(false, BankResult)); } } else { appTrans?.Rollback(); return(JSONHelper.FromString(false, PropertyResult)); } } catch (Exception ex) { appTrans?.Rollback(); return(JSONHelper.FromString(false, ex.Message + ex.StackTrace)); } } }