private string GetPaymentHistoryDetail(DataRow row)
{
#region 获取参数并简单校验
string CommID = string.Empty;
if (!row.Table.Columns.Contains("CommID"))
{
return(new ApiResult(false, "小区编码不能为空").toJson());
}
CommID = row["CommID"].ToString();
Tb_Community community = GetCommunity(CommID);
if (null == community)
{
return(new ApiResult(false, "未查找到小区信息").toJson());
}
CommID = community.CommID;
string ReceID = string.Empty;
if (row.Table.Columns.Contains("OrderSN"))
{
ReceID = row["OrderSN"].ToString();
}
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(Convert.ToInt32(CommID), Global_Fun.BURST_TYPE_CHARGE);
#endregion
using (IDbConnection conn = new SqlConnection(chargeConnectionString))
{
dynamic Receipts = conn.QueryFirstOrDefault("SELECT BillType,BillsDate,BillsAmount,ChargeMode FROM view_HSPR_CustomerBillSign_Filter WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID });
if (null == Receipts)
{
return(new ApiResult(false, "订单不存在").toJson());
}
int BillType = Convert.ToInt32(Receipts.BillType);
string BillsDate = Convert.ToString(Receipts.BillsDate);
decimal BillsAmount = Convert.ToDecimal(Receipts.BillsAmount);
string ChargeMode = Convert.ToString(Receipts.ChargeMode);
Dictionary <string, object> result = new Dictionary <string, object>();
List <Dictionary <string, string> > FeesInfoList = new List <Dictionary <string, string> >();
if (BillType == 1)
{
List <dynamic> CostList = conn.Query("SELECT CostID,CostName,FeesID,FeesStateDate,FeesEndDate FROM view_HSPR_Fees_SearchFilter WHERE CommID = @CommID AND ISNULL(FeesID, 0) != 0 AND FeesID IN (SELECT FeesID FROM Tb_HSPR_FeesDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID UNION SELECT FeesID FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID)", new { CommID, ReceID }).ToList();
if (null == CostList || CostList.Count <= 0)
{
return(new ApiResult(false, "订单数据错误").toJson());
}
foreach (var CostGroup in CostList.GroupBy(item => Convert.ToString(item.CostName)))
{
if (null == CostGroup || CostGroup.Count() == 0)
{
continue;
}
#region 分组后,取费用最早的开始时间,和最晚的结束时间
DateTime FeesStateDate, FeesEndDate;
FeesStateDate = DateTime.MaxValue;
FeesEndDate = DateTime.MinValue;
string CostID = "";
string CostName = "";
List <string> FeesIDList = new List <string>();
foreach (var item in CostGroup)
{
FeesIDList.Add(Convert.ToString(item.FeesID));
if (string.IsNullOrEmpty(CostID))
{
CostID = Convert.ToString(item.CostID);
}
if (string.IsNullOrEmpty(CostName))
{
CostName = Convert.ToString(item.CostName);
}
if (DateTime.TryParse(Convert.ToString(item.FeesStateDate), out DateTime startTime))
{
if (FeesStateDate.CompareTo(startTime) > 0)
{
FeesStateDate = startTime;
}
}
else
{
FeesStateDate = DateTime.Now;
}
if (DateTime.TryParse(Convert.ToString(item.FeesEndDate), out DateTime endTime))
{
if (FeesEndDate.CompareTo(endTime) < 0)
{
FeesEndDate = endTime;
}
}
else
{
FeesEndDate = DateTime.Now;
}
}
#endregion
decimal Amount = conn.QueryFirstOrDefault <decimal>(@"SELECT ISNULL(SUM(ISNULL(Amount,0.00)),0.00) AS Amount FROM (
SELECT ISNULL(SUM(ISNULL(ISNULL(ChargeAmount,0.00) + ISNULL(LateFeeAmount,0.00),0.00)),0.00) AS Amount FROM Tb_HSPR_FeesDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID AND FeesID IN @FeesID
UNION
SELECT ISNULL(SUM(ISNULL(DueAmount,0.00)),0.00) AS Amount FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID AND FeesID IN @FeesID
) AS a", new { ReceID, FeesID = FeesIDList.ToArray() });
FeesInfoList.Add(new Dictionary <string, string>
{
{ "CostID", CostID },
{ "CostName", CostName },
{ "Amount", Amount.ToString() },
{ "CostArea", FeesStateDate.ToString("yyyy-MM") + "至" + FeesEndDate.ToString("yyyy-MM") }
});
}
}
if (BillType == 2)
{
dynamic info = conn.QueryFirstOrDefault("SELECT CostID,CostNames,PrecMemo FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID }).ToList();
if (null != info)
{
string CostID = Convert.ToString(info.CostID);
string CostName = Convert.ToString(info.CostNames);
string PrecMemo = Convert.ToString(info.PrecMemo);
string PrecAmount = Convert.ToString(info.PrecAmount);
FeesInfoList.Add(new Dictionary <string, string>
{
{ "CostID", CostID },
{ "CostName", CostName },
{ "Amount", PrecAmount },
{ "CostArea", PrecMemo }
});
}
}
string ElectronicInvoice = string.Empty;
if (conn.QueryFirstOrDefault <long>("SELECT isnull(object_id(N'Tb_HSPR_ElectronicInvoice',N'U'),0)") != 0)
{
ElectronicInvoice = conn.QueryFirstOrDefault <string>("SELECT ImgUrl FROM Tb_HSPR_ElectronicInvoice WHERE CommID = @CommID AND ReceID = @ReceID", new { CommID, ReceID });
}
result.Add("Amount", BillsAmount);
result.Add("Status", "交易成功");
result.Add("PayType", ChargeMode);
result.Add("CreateTime", BillsDate);
result.Add("OrderSN", ReceID);
result.Add("FeesDetail", FeesInfoList);
result.Add("ElectronicInvoice", ElectronicInvoice);
return(new ApiResult(true, result).toJson());
}
}
/// <summary>
/// 获取历史账单列表（显示票据号）
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private string GetPaymentHistory(DataRow row)
{
#region 获取参数并简单校验
string CommID = string.Empty;
if (!row.Table.Columns.Contains("CommID"))
{
return(new ApiResult(false, "小区编码不能为空").toJson());
}
CommID = row["CommID"].ToString();
Tb_Community community = GetCommunity(CommID);
if (null == community)
{
return(new ApiResult(false, "未查找到小区信息").toJson());
}
CommID = community.CommID;
if (!row.Table.Columns.Contains("CustID") || !long.TryParse(row["CustID"].ToString(), out long CustID))
{
CustID = 0;
}
if (!row.Table.Columns.Contains("RoomID") || !long.TryParse(row["RoomID"].ToString(), out long RoomID))
{
RoomID = 0;
}
if (!row.Table.Columns.Contains("Page") || !int.TryParse(row["Page"].ToString(), out int Page))
{
Page = 1;
}
if (Page <= 0)
{
Page = 1;
}
if (!row.Table.Columns.Contains("Size") || !int.TryParse(row["Size"].ToString(), out int Size))
{
Size = 10;
}
if (Size <= 0)
{
Size = 10;
}
int Start = (Page - 1) * Size;
int End = Page * Size;
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(Convert.ToInt32(CommID), Global_Fun.BURST_TYPE_CHARGE);
#endregion
using (IDbConnection conn = new SqlConnection(chargeConnectionString))
{
List <Dictionary <string, object> > resultList = new List <Dictionary <string, object> >();
DateTime BeginDate = new DateTime(1970, 1, 1);
#region 查询可查询账单开始时间设置
try
{
dynamic setting = conn.QueryFirstOrDefault("SELECT ISNULL(IsShow, 1) AS IsShow,BeginDate FROM Tb_HSPR_FeeBundleSettings WHERE ISNULL(IsDelete,0) = 0 AND CommID = @CommID", new { CommID });
if (null != setting)
{
if (Convert.ToInt32(setting.IsShow) == 0)
{
return(new ApiResult(true, new { Data = resultList, Count = 0, Page = 0 }).toJson());
}
if (!DateTime.TryParse(Convert.ToString(setting.BeginDate), out BeginDate))
{
BeginDate = new DateTime(1970, 1, 1);
}
}
}
catch (Exception)
{
}
#endregion
conn.Execute("Proc_HSPR_CustomerBillSign_Cre", new { CommID, CustID, RoomID }, null, null, CommandType.StoredProcedure);
int Count = conn.QueryFirstOrDefault <int>("SELECT COUNT(ReceID) FROM (SELECT * FROM view_HSPR_CustomerBillSign_Filter) AS a WHERE ISNULL(IsDelete,0) = 0 AND BillType IN (1,2) AND CommID = @CommID AND CustID = @CustID AND RoomID = @RoomID AND BillsDate > @BeginDate", new { CommID, CustID, RoomID, BeginDate });
int PageRes = Count % Size > 0 ? (Count / Size) + 1 : Count / Size;
int CountRes = Count;
List <dynamic> list = conn.Query("SELECT ReceID,BillsDate,BillsSign,BillsAmount,ChargeMode,BillType FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY BillsDate DESC) AS RowId FROM view_HSPR_CustomerBillSign_Filter WHERE ISNULL(IsDelete,0) = 0 AND BillType IN (1,2) AND CommID = @CommID AND CustID = @CustID AND RoomID = @RoomID AND BillsDate > @BeginDate) AS a WHERE RowId BETWEEN @Start AND @End", new { CommID, CustID, RoomID, Start, End, BeginDate }).ToList();
if (null != list && list.Count > 0)
{
foreach (dynamic item in list)
{
try
{
int BillType = Convert.ToInt32(item.BillType);
string ReceID = Convert.ToString(item.ReceID);
string BillsSign = Convert.ToString(item.BillsSign);
string BillsDate = Convert.ToString(item.BillsDate);
decimal BillsAmount = Convert.ToDecimal(item.BillsAmount);
string ChargeMode = Convert.ToString(item.ChargeMode);
if (BillType == 1)
{
#region 实收查实收表，不能查预存
// 查询费项名称和区间
List <string> FeesIDList = conn.Query <string>("SELECT FeesID FROM Tb_HSPR_FeesDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID UNION SELECT FeesID FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID }).ToList();
List <dynamic> CostList = conn.Query("SELECT CostName,FeesStateDate,FeesEndDate FROM view_HSPR_Fees_SearchFilter WHERE CommID = @CommID AND ISNULL(FeesID, 0) != 0 AND FeesID IN @FeesIDList", new { CommID, FeesIDList }).ToList();
if (null == CostList || CostList.Count <= 0)
{
continue;
}
DateTime FeesStateDate, FeesEndDate;
FeesStateDate = DateTime.MaxValue;
FeesEndDate = DateTime.MinValue;
HashSet <string> CostNameList = new HashSet <string>();
foreach (dynamic costinfo in CostList)
{
CostNameList.Add(Convert.ToString(costinfo.CostName));
if (DateTime.TryParse(Convert.ToString(costinfo.FeesStateDate), out DateTime startTime))
{
if (FeesStateDate.CompareTo(startTime) > 0)
{
FeesStateDate = startTime;
}
}
else
{
FeesStateDate = DateTime.Now;
}
if (DateTime.TryParse(Convert.ToString(costinfo.FeesEndDate), out DateTime endTime))
{
if (FeesEndDate.CompareTo(endTime) < 0)
{
FeesEndDate = endTime;
}
}
else
{
FeesEndDate = DateTime.Now;
}
}
resultList.Add(new Dictionary <string, object>
{
{ "OrderSN", ReceID },
{ "PayChannel", ChargeMode },
{ "PayTime", BillsDate },
{ "Amount", BillsAmount },
{ "BillsSign", BillsSign },
{ "CostName", string.Join(",", CostNameList.ToArray()) },
{ "CostArea", FeesStateDate.ToString("yyyy.MM.dd") + "-" + FeesEndDate.ToString("yyyy.MM.dd") }
});
#endregion
}
if (BillType == 2)
{
dynamic info = conn.QueryFirstOrDefault("SELECT CostNames,PrecMemo FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID }).ToList();
if (null == info)
{
continue;
}
string CostName = Convert.ToString(info.CostNames);
string PrecMemo = Convert.ToString(info.PrecMemo);
// 预存查预存的费用信息
resultList.Add(new Dictionary <string, object>
{
{ "OrderSN", ReceID },
{ "PayChannel", ChargeMode },
{ "PayTime", BillsDate },
{ "Amount", BillsAmount },
{ "BillsSign", BillsSign },
{ "CostName", CostName },
{ "CostArea", PrecMemo }
});
}
}
catch (Exception)
{
continue;
}
}
}
return(new ApiResult(true, new { Data = resultList, Count = CountRes, Page = PageRes }).toJson());
}
}