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