/// <summary>
/// 获取历史账单列表
/// </summary>
private string GetFeesHistoryList(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].AsString()))
{
return(new ApiResult(false, "用户信息错误").toJson());
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "请选择房屋所在项目").toJson());
}
if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "未指定欠费主体").toJson());
}
var userId = row["UserId"].AsString();
var communityId = row["CommunityId"].AsString();
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = 0L;
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].AsString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
}
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查找到小区信息"));
}
var commId = AppGlobal.StrToInt(community.CommID);
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_CHARGE);
var shieldCost = "";
// 查询需要屏蔽的费用
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT isnull(object_id(N'Tb_Control_AppCostItem',N'U'),0)";
if (appConn.Query <long>(sql).FirstOrDefault() != 0)
{
sql = @"SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND CommunityID=@Id AND AllowShow=0;
SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND isnull(CommunityID,'')='' AND AllowShow=0;";
var reader = appConn.QueryMultiple(sql, new { CorpID = community.CorpID, Id = community.Id });
var data1 = reader.Read <string>().FirstOrDefault();
var data2 = reader.Read <string>().FirstOrDefault();
var data = data1 ?? data2;
if (!string.IsNullOrEmpty(data))
{
using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
sql = $@"SELECT CostCode FROM Tb_HSPR_CorpCostItem
WHERE CorpCostID IN
(
SELECT Value FROM SplitString('{data}',',',1)
)";
var costCodes = erpConn.Query <string>(sql).Select(obj => $"'{obj}'");
shieldCost = $@"AND CostCode NOT IN ({ string.Join(",", costCodes.ToArray()) })";
}
}
}
}
using (var chargeConn = new SqlConnection(chargeConnectionString))
{
var list = new List <PMSAppFeesCostSimpleModel>();
var sql = $@"/* 欠费已缴清 */
SELECT FeesID,x.CostID,CostName,
isnull(PaidAmount,0.00) AS PaidAmount,
isnull(PrecAmount, 0.00) AS PrecAmount,
isnull(SysCostSign,'Unknown') AS SysCostSign,
isnull(DueAmount,0.00) AS DueAmount,
isnull(DebtsAmount,0.00) AS DebtsAmount,
CASE WHEN isnull(x.DebtsLateAmount, 0)>0
THEN dbo.funGetLateFeeDebts(x.CommID,x.FeesID,isnull(x.DebtsLateAmount,0))
ELSE 0.0 END AS LateFeeAmount,
convert(varchar(10),isnull(FeesDueDate,getdate()),120) AS FeesDueDate
FROM Tb_HSPR_Fees x
LEFT JOIN { community.DBName }.dbo.Tb_HSPR_CostItem y ON x.CostID=y.CostID
WHERE /*x.CustID=@CustID AND*/ x.RoomID=@RoomID
AND (isnull(x.IsCharge,0)=1 OR isnull(x.IsPrec,0)=1)
{ shieldCost }
ORDER BY SysCostSign ASC,FeesDueDate DESC;";
var data = chargeConn.Query(sql, new { CommID = community.CommID, RoomID = roomId /*, CustID = custId*/ });
for (int i = 0; i < data.Count(); i++)
{
var feesInfo = data.ToList()[i];
var tmp = list.Find(obj => obj.CostID == feesInfo.CostID);
if (tmp == null)
{
tmp = new PMSAppFeesCostSimpleModel()
{
CostID = feesInfo.CostID,
CostName = feesInfo.CostName,
SysCostSign = feesInfo.SysCostSign
};
tmp.Fees = new List <PMSAppFeesSimpleModel>();
list.Add(tmp);
if (tmp.SysCostSign == "Unknown")
{
tmp.SysCostSign = null;
}
}
var model = new PMSAppFeesSimpleModel()
{
FeesID = feesInfo.FeesID,
DueAmount = feesInfo.DueAmount,
DebtsAmount = feesInfo.DebtsAmount,
LateFeeAmount = feesInfo.LateFeeAmount,
FeesDueDate = feesInfo.FeesDueDate,
PaidAmount = feesInfo.PaidAmount,
PrecAmount = feesInfo.PrecAmount
};
tmp.TotalDueAmount += model.DueAmount;
tmp.TotalDebtsAmount += model.DebtsAmount;
tmp.TotalLateFeeAmount += model.LateFeeAmount;
tmp.TotalPaidAmount += model.PaidAmount;
tmp.TotalPrecAmount += model.PrecAmount;
if (feesInfo.SysCostSign == "B0001")
{
tmp.Expanded = 1;
}
tmp.Fees.Add(model);
}
return(new ApiResult(true, new
{
ArrearsFees = list
}).toJson());
}
}
/// <summary>
/// 获取欠费列表
/// </summary>
private string GetArrearsFees(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].AsString()))
{
return(new ApiResult(false, "用户信息错误").toJson());
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "请选择房屋所在项目").toJson());
}
if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "未指定欠费主体").toJson());
}
var userId = row["UserId"].AsString();
var communityId = row["CommunityId"].AsString();
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = 0L;
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].AsString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
}
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查找到小区信息"));
}
var commId = AppGlobal.StrToInt(community.CommID);
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_CHARGE);
List <string> BindCostList = new List <string>();
JObject PaymentCycleSetting = new JObject();
using (IDbConnection conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var sql = @"SELECT isnull(object_id(N'Tb_HSPR_PaymentBind',N'U'),0)";
if (conn.QueryFirstOrDefault <long>(sql) != 0)
{
BindCostList = conn.Query <string>("SELECT CostID FROM Tb_HSPR_PaymentBind WHERE CommID = @CommID AND ISNULL(IsDelete,0) = 0", new { CommID = commId }).ToList();
}
sql = @"SELECT isnull(object_id(N'Tb_HSPR_PaymentBindingDateModelSet',N'U'),0)";
if (conn.QueryFirstOrDefault <long>(sql) != 0)
{
// 需求9759，视图新增返回参数IsHis，用于判断是否需要缴纳历史欠费
var Setting = conn.QueryFirstOrDefault("SELECT CheckBoxID,MonthNum,IsHis FROM View_HSPR_PaymentBindingDateModelSet_Filter WHERE CommID = @CommID", new { CommID = commId });
// Type值：0=默认全选，1=任意选择，2=按月份数量一次性整月选择，3=一次性至少缴纳几个月费用
int Type = 0;
int MonthNum = 0;
if (null != Setting)
{
int CheckBoxID = 1;
if (Setting.CheckBoxID != null)
{
CheckBoxID = Convert.ToInt32(Setting.CheckBoxID);
}
if (Setting.MonthNum != null)
{
MonthNum = Convert.ToInt32(Setting.MonthNum);
}
switch (CheckBoxID)
{
case 1:
{
Type = 0;
MonthNum = 0;
}
break;
case 2:
{
Type = 1;
MonthNum = 0;
}
break;
case 4:
{
Type = 2;
MonthNum = 1;
}
break;
case 6:
{
Type = 2;
MonthNum = 3;
}
break;
case 8:
{
Type = 2;
MonthNum = 6;
}
break;
case 10:
{
Type = 2;
MonthNum = 12;
}
break;
case 12:
{
Type = 3;
}
break;
default:
{
Type = 0;
MonthNum = 0;
}
break;
}
}
PaymentCycleSetting.Add("Type", Type);
PaymentCycleSetting.Add("MonthNum", MonthNum);
PaymentCycleSetting.Add("IsHis", Setting != null ? Setting.IsHis : 1);
}
}
var shieldCost = "";
// 查询需要屏蔽的费用
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT isnull(object_id(N'Tb_Control_AppCostItem',N'U'),0)";
// 优先取ERP
if (string.IsNullOrEmpty(shieldCost) && appConn.Query <long>(sql).FirstOrDefault() != 0)
{
sql = @"SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND CommunityID=@Id AND AllowShow=0;
SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND isnull(CommunityID,'')='' AND AllowShow=0;";
var reader = appConn.QueryMultiple(sql, new { CorpID = community.CorpID, Id = community.Id });
var data1 = reader.Read <string>().FirstOrDefault();
var data2 = reader.Read <string>().FirstOrDefault();
var data = data1 ?? data2;
if (!string.IsNullOrEmpty(data))
{
using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
sql = $@"SELECT CostCode FROM Tb_HSPR_CorpCostItem
WHERE CorpCostID IN
(
SELECT Value FROM SplitString('{data}',',',1)
)";
var costCodes = erpConn.Query <string>(sql).Select(obj => $"'{obj}'");
shieldCost = $@"AND CostCode NOT IN ({ string.Join(",", costCodes.ToArray()) })";
}
}
}
}
using (var chargeConn = new SqlConnection(chargeConnectionString))
{
var list = new List <PMSAppFeesCostSimpleModel>();
var sql = $@"/* 欠费未缴清 */
SELECT FeesID,x.CostID,CostName,isnull(SysCostSign,'Unknown') AS SysCostSign,
isnull(DueAmount,0.00) AS DueAmount,
isnull(DebtsAmount,0.00) AS DebtsAmount,
CASE WHEN isnull(x.DebtsLateAmount, 0)>0
THEN dbo.funGetLateFeeDebts(x.CommID,x.FeesID,isnull(x.DebtsLateAmount, 0))
ELSE 0.0 END AS LateFeeAmount,
convert(varchar(10),isnull(FeesDueDate,getdate()),120) AS FeesDueDate
FROM Tb_HSPR_Fees x
LEFT JOIN { community.DBName }.dbo.Tb_HSPR_CostItem y ON x.CostID=y.CostID
WHERE x.CustID=@CustID AND x.RoomID=@RoomID
AND isnull(x.IsCharge,0)=0 AND isnull(x.IsBank,0)=0 AND isnull(x.IsPrec,0)=0 AND isnull(IsFreeze,0)=0
{ shieldCost }
ORDER BY SysCostSign ASC,FeesDueDate DESC;
/* 仅剩违约金 */
/*
SELECT FeesID,CostID,CostName,isnull(SysCostSign,'Unknown') AS SysCostSign,
isnull(DueAmount,0.00) AS DueAmount,
isnull(DebtsAmount,0.00) AS DebtsAmount,
isnull(LateFeeAmount,0.00) AS LateFeeAmount,
convert(varchar(10),isnull(FeesDueDate,getdate()),120) AS FeesDueDate
FROM view_HSPR_Fees_SearchFilter x
WHERE x.CustID=@CustID AND x.RoomID=@RoomID
AND isnull(x.IsCharge,0)=1 AND isnull(x.IsBank,0)=0 AND isnull(x.IsPrec,0)=0 AND isnull(IsFreeze,0)=0
AND isnull(x.LateFeeAmount,0.0)>0
{ shieldCost }
ORDER BY SysCostSign ASC,FeesDueDate DESC;
*/";
var reader = chargeConn.QueryMultiple(sql, new { CommID = community.CommID, RoomID = roomId, CustID = custId });
var data1 = reader.Read();
//var data2 = reader.Read();
var data = data1.ToList();
for (int i = 0; i < data.Count; i++)
{
var feesInfo = data[i];
var tmp = list.Find(obj => obj.CostID == feesInfo.CostID);
if (tmp == null)
{
tmp = new PMSAppFeesCostSimpleModel()
{
CostID = feesInfo.CostID,
CostName = feesInfo.CostName,
SysCostSign = feesInfo.SysCostSign
};
tmp.Fees = new List <PMSAppFeesSimpleModel>();
list.Add(tmp);
if (tmp.SysCostSign == "Unknown")
{
tmp.SysCostSign = null;
}
}
var model = new PMSAppFeesSimpleModel()
{
FeesID = feesInfo.FeesID,
DueAmount = feesInfo.DueAmount,
DebtsAmount = feesInfo.DebtsAmount,
LateFeeAmount = feesInfo.LateFeeAmount,
FeesDueDate = feesInfo.FeesDueDate
};
tmp.TotalDueAmount += model.DueAmount;
tmp.TotalDebtsAmount += model.DebtsAmount;
tmp.TotalLateFeeAmount += model.LateFeeAmount;
if (feesInfo.SysCostSign == "B0001")
{
tmp.Expanded = 1;
}
tmp.Fees.Add(model);
}
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
// 读取积分信息
sql = @"SELECT * FROM Tb_Control_AppPoint WHERE CommunityID=@CommunityId AND isnull(CommunityID,'')<>'' AND IsEnable=1
UNION ALL
SELECT * FROM Tb_Control_AppPoint WHERE CorpID=@CorpId AND isnull(CommunityID,'')='' AND IsEnable=1";
var controlInfo = appConn.Query <Tb_Control_AppPoint>(sql, new { CommunityId = communityId, CorpId = community.CorpID }).FirstOrDefault();
if (controlInfo == null || controlInfo.IsEnable == false)
{
controlInfo = Tb_Control_AppPoint.DefaultControl;
}
// 用户积分余额
sql = @"SELECT PointBalance FROM Tb_App_UserPoint WHERE UserID=@UserID";
var balance = appConn.Query <int>(sql, new { UserID = userId }).FirstOrDefault();
var costSign = new List <string>();
// 允许抵用物业费
if (controlInfo.AllowDeductionPropertyFees)
{
costSign.Add("B0001");
}
// 允许抵用车位费
if (controlInfo.AllowDeductionParkingFees)
{
costSign.Add("B0002");
}
return(new ApiResult(true, new
{
ArrearsFees = list,
BindCostList,
PaymentCycleSetting,
Points = new
{
UserPointBalance = balance,
AllowDeductionPropertyFees = controlInfo.AllowDeductionPropertyFees,
AllowDeductionParkingFees = controlInfo.AllowDeductionParkingFees,
AllowDeductionOtherPropertyFees = controlInfo.AllowDeductionOtherPropertyFees,
PointExchangeRatio = controlInfo.PointExchangeRatio,
SysCostSign = costSign
},
}).toJson());
}
}
}
