/// <summary>
/// 是否使用了积分
/// </summary>
private void UsePoint(string orderId)
{
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
try
{
var orderInfo = conn.Query(@"SELECT a.UseHistoryID,b.UserID FROM Tb_App_Point_UseHistoryOrder a
LEFT JOIN Tb_App_Point_UseHistory b ON a.UseHistoryID=b.IID
WHERE a.OrderID=@OrderID AND a.Payment='支付宝'",
new
{
OrderID = orderId,
UsableObject = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.Goods)
}).FirstOrDefault();
if (orderInfo != null)
{
// 积分生效
conn.Execute(@"UPDATE Tb_App_Point_UseHistory SET IsEffect=1 WHERE IID=@IID", new { IID = orderInfo.UseHistoryID.ToString() });
Business.Alipay.Log("支付宝购物:使用了积分，记录id=" + orderInfo.UseHistoryID.ToString());
}
}
catch (Exception)
{
}
}
}
/// <summary>
/// 计算商品赠送积分
/// </summary>
public int CalcPresentedPointForGoods(int corpId, decimal paidAmount)
{
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
try
{
string key = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.Goods);
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
ORDER BY ConditionAmount,DiscountsAmount",
new
{
CorpID = corpId,
PresentedObject = key
});
if (rules.Count() > 0)
{
Tb_App_Point_PropertyPresentedRule rule = null;
foreach (var item in rules)
{
if (paidAmount >= item.ConditionAmount)
{
rule = item;
}
}
if (rule != null)
{
return(rule.PresentedPoints);
}
}
return(0);
}
catch (Exception ex)
{
return(0);
}
}
}
/// <summary>
/// 是否使用了积分
/// </summary>
private void UsePoint(string orderId, string userId, out string useHistoryId)
{
useHistoryId = null;
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var trans = conn.BeginTransaction();
try
{
var orderInfo = conn.Query(@"SELECT a.UseHistoryID,b.UserID FROM Tb_App_Point_UseHistoryOrder a
LEFT JOIN Tb_App_Point_UseHistory b ON a.UseHistoryID=b.IID
WHERE a.OrderID=@OrderID AND a.Payment='支付宝'",
new
{
OrderID = orderId,
UsableObject = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.PropertyFee)
}, trans).FirstOrDefault();
useHistoryId = orderInfo?.UseHistoryID.ToString();
if (!string.IsNullOrEmpty(userId) && !string.IsNullOrEmpty(useHistoryId))
{
// 积分生效
conn.Execute(@"UPDATE Tb_App_Point_UseHistory SET IsEffect=1 WHERE IID=@IID", new { IID = useHistoryId }, trans);
Business.Alipay.Log("支付宝下账:使用了积分，记录id=" + useHistoryId);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Business.Alipay.Log("支付宝下账:判断积分异常，" + ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
/// <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));
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
corpId = HttpContext.Current.Request.QueryString["CorpID"];
communityId = HttpContext.Current.Request.QueryString["CommunityID"];
string innerHTML = "";
if (corpId == "1509")
{
innerHTML = @"<h3>【积分价值】</h3>
邦客生活APP内积分价值设定为：100积分=1元。
<h3>【积分获取途径】</h3>
签到积分，每天签到可获得1积分，累计签到奖励积分,当签到累计到一定天数时，系统自动赠送签到以外的积分。
在“邦客生活”APP线上商城购物，完成线上付款后获得相应积分。
<h3>【积分使用途径】</h3>
用户在“邦客生活”APP线上商城选择参加积分兑换的商品购物付款时可选择使用积分抵扣商品价格。用户可自行选择设置积分抵扣数值。
<h3>【积分相关说明】</h3>
1.个人用户所获积分只能在个人账号下的APP内使用，积分不可转赠、转让、兑现;若购物付款使用积分抵扣价格后退货的，则积分系统将自动将该笔积分扣除，不予退还；<br/>
2.积分有效期为1年，每年12月31日将自动清零；<br/>
3.积分的获取、使用方式以及详细数据，可在APP内个人积分账户查看；<br/>
4.使用积分抵扣商品金额时，用户可自行选择是否使用积分抵扣，并可自行选择使用多少积分进行抵扣；<br/>
5.“邦客生活”APP积分规则由成都衡信英伦物业管理有限公司制定并依据相关法律法规及规章制度进行解释及修改，具体规则以“邦客生活”APP公布为准；<br/>
6.“邦客生活”APP所有积分活动的最终解释权归成都衡信英伦物业管理有限公司所有。<br/>";
this.form1.InnerHtml = innerHTML;
return;
}
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
// 1、物业欠费缴费抵用规则
var deductionRules = conn.Query <Tb_App_Point_PropertyDeductionRule>(@"SELECT * FROM Tb_App_Point_PropertyDeductionRule
WHERE CorpID=@CorpID AND CommunityID=@CommunityID AND IsDelete=0 AND getdate()<EndTime
AND (DeductionObject=@DeductionObject1 OR DeductionObject=@DeductionObject2)
ORDER BY ConditionAmount,DiscountsAmount",
new
{
CorpID = corpId,
CommunityID = communityId,
DeductionObject1 = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.PropertyFee),
DeductionObject2 = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.ParkingFee)
}).ToList();
if (deductionRules.Count() > 0)
{
innerHTML += "<div><span>欠费缴费积分抵用规则</span><br />";
for (int i = 0; i < deductionRules.Count(); i++)
{
var item = deductionRules[i];
innerHTML += $@"<p>{i + 1}、缴费满{item.ConditionAmount}元可用积分抵扣{item.DiscountsAmount}元，活动截止至{item.EndTime}。</p>";
}
innerHTML += "<div>";
}
// 2、物业欠费缴费赠送规则
var presentedRules = conn.Query <Tb_App_Point_PropertyPresentedRule>(@"SELECT * FROM Tb_App_Point_PropertyPresentedRule
WHERE CorpID=@CorpID AND CommunityID=@CommunityID AND IsDelete=0 AND getdate()<EndTime
AND (PresentedObject=@PresentedObject1 OR PresentedObject=@PresentedObject2)
ORDER BY ConditionAmount,PresentedPoints",
new
{
CorpID = corpId,
CommunityID = communityId,
PresentedObject1 = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.PropertyFee),
PresentedObject2 = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.ParkingFee)
}).ToList();
// 2、预存缴费规则
if (corpId == "2015") // 力帆
{
innerHTML += @"<div>
<span> 预存缴费赠送规则 </span>
<br />
<p>
1、预存满6个月赠送18000
</p>
<p>
2、预存满12个月赠送45000
</p>
<p>
3、预存满18个月赠送63000
</p>
<p>
4、预存满24个月赠送90000
</p>
<p>
活动截止时间至2019年3月31号
</p>
</div> ";
}
else
{
if (presentedRules.Count() > 0)
{
innerHTML += "<div><span>欠费缴费积分抵扣规则</span><br />";
for (int i = 0; i < presentedRules.Count(); i++)
{
var item = presentedRules[i];
innerHTML += $@"<p>{i + 1}、缴费满{item.ConditionAmount}元赠送{item.PresentedPoints}积分，活动截止至{item.EndTime}。</p>";
}
innerHTML += "<div>";
}
else
{
innerHTML += "<div><span>暂未配置欠费缴费积分抵扣规则</span><br/>";
}
}
this.form1.InnerHtml = innerHTML;
}
}
/// <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>
public void CalcPresentedPointForPropertyFees(string communityId, decimal paidPropertyFeeAmount, decimal paidParkingFeeAmount,
out int propertyRulePresentedPoint, out int parkingRulePresentedPoint)
{
propertyRulePresentedPoint = 0;
parkingRulePresentedPoint = 0;
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
try
{
string propertyFeeKey = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.PropertyFee);
string parkingFeeKey = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.ParkingFee);
var rules = conn.Query <Tb_App_Point_PropertyPresentedRule>(@"SELECT * FROM Tb_App_Point_PropertyPresentedRule
WHERE CommunityID=@CommunityID AND (PresentedObject=@PresentedObject1 OR PresentedObject=@PresentedObject2)
AND IsDelete=0 ORDER BY ConditionAmount",
new
{
CommunityID = communityId,
PresentedObject1 = propertyFeeKey,
PresentedObject2 = parkingFeeKey
});
if (rules.Count() > 0)
{
Tb_App_Point_PropertyPresentedRule propertyRule = null;
Tb_App_Point_PropertyPresentedRule parkingRule = null;
foreach (var item in rules)
{
// 物业类
if (item.PresentedObject == propertyFeeKey)
{
if (paidPropertyFeeAmount >= item.ConditionAmount)
{
propertyRule = item;
}
}
// 车位类
if (item.PresentedObject == parkingFeeKey)
{
if (paidParkingFeeAmount >= item.ConditionAmount)
{
parkingRule = item;
}
}
}
if (propertyRule != null)
{
propertyRulePresentedPoint = propertyRule.PresentedPoints;
}
if (parkingRule != null)
{
parkingRulePresentedPoint = parkingRule.PresentedPoints;
}
}
}
catch (Exception ex)
{
}
}
}
/// <summary>
/// 获取购物积分抵用规则
/// </summary>
private string GetPointDeductionRuleAboutGoods(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(new ApiResult(false, "UserId不能为空").toJson());
}
if (!row.Table.Columns.Contains("CorpID") || string.IsNullOrEmpty(row["CorpID"].ToString()))
{
return(new ApiResult(false, "CorpID不能为空").toJson());
}
string UserId = row["UserId"].ToString();
string CorpID = row["CorpID"].ToString();
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
List <string> deductionObject = new List <string>();
var controlInfo = conn.Query <Tb_Control_AppPoint>(@"SELECT * FROM Tb_Control_AppPoint WHERE CorpID=@CorpID AND CommunityID IS NULL AND IsEnable=1",
new { CorpID = CorpID }).FirstOrDefault();
if (controlInfo == null || controlInfo.IsEnable == false)
{
controlInfo = Tb_Control_AppPoint.DefaultControl;
controlInfo.CommunityID = Guid.Empty.ToString();
}
// 允许抵用商品
if (!controlInfo.AllowDeductionGoodsFees)
{
return(new ApiResult(true, new object()).toJson());
}
// 用户积分余额
int balance = conn.Query <int>("SELECT PointBalance FROM Tb_App_UserPoint WHERE UserID=@UserID",
new { UserID = UserId }).FirstOrDefault();
var ruleInfo = conn.Query($@"SELECT IID,ConditionAmount,DiscountsAmount,DeductionObject,StartTime,EndTime
FROM Tb_App_Point_PropertyDeductionRule a LEFT JOIN Tb_Dictionary_Point_UsableObject b
ON a.DeductionObject=b.[Key]
WHERE CorpID=@CorpID AND CommunityID IS NULL AND DeductionObject=@DeductionObject
AND getdate() BETWEEN StartTime AND EndTime AND a.IsDelete=0 ORDER BY ConditionAmount",
new { CorpID = CorpID, DeductionObject = AppPointUsableObjectConverter.GetKey(AppPointUsableObject.Goods) });
var list = new List <dynamic>();
foreach (var item in ruleInfo)
{
var _o = list.Find(obj => obj.DeductionObject == item.DeductionObject);
if (_o == null)
{
_o = new
{
DeductionObject = item.DeductionObject,
Rules = new List <dynamic>()
{
new {
ConditionAmount = item.ConditionAmount,
DiscountsAmount = item.DiscountsAmount,
StartTime = item.StartTime,
EndTime = item.EndTime,
}
}
};
list.Add(_o);
}
else
{
_o.Rules.Add(new
{
ConditionAmount = item.ConditionAmount,
DiscountsAmount = item.DiscountsAmount,
StartTime = item.StartTime,
EndTime = item.EndTime,
});
}
}
return(new ApiResult(true, new
{
UserPointBalance = balance,
PointExchangeRatio = controlInfo.PointExchangeRatio,
DeductionRules = list
}).toJson());
}
}
/// <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);
}
}
}
