/// <summary>
/// 获取检索条件
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private string GetConditionSql(InterestFitFilter filter)
{
string sql = "";
sql += " WHERE b.BusinessStatus <> 1";
// 信托方
if (!string.IsNullOrEmpty(filter.LendingSideKey))
{
sql += " AND b.LendingSideKey = '" + filter.LendingSideKey + "'";
}
// 批次号
if (!string.IsNullOrEmpty(filter.BatchNo))
{
sql += " AND b.GuaranteeNum = '" + filter.BatchNo + "'";
}
// 合同号
if (!string.IsNullOrEmpty(filter.ContractNO))
{
sql += " AND b.ContractNo = '" + filter.ContractNO + "'";
}
//转担保日期
DateTime startime;
DateTime endtime;
if (!string.IsNullOrEmpty(filter.GuaranteeFromTime))
{
if (DateTime.TryParse(filter.GuaranteeFromTime, out startime))
{
sql += " AND b.ToGuaranteeTime >= " + startime;
}
}
if (!string.IsNullOrEmpty(filter.GuaranteeToTime))
{
if (DateTime.TryParse(filter.GuaranteeToTime, out endtime))
{
sql += " AND b.ToGuaranteeTime <= " + endtime;
}
}
// 状 态
if ("已支付" == filter.PaymentStatus)
{
sql += " AND b.PaymentDate IS NOT NULL";
}
else if ("未支付" == filter.PaymentStatus)
{
sql += " AND b.PaymentDate IS NULL";
}
if (!string.IsNullOrEmpty(filter.BranchKey))
{
sql += @" AND b.BusinessID IN (
SELECT DISTINCT BusinessID
FROM dbo.Bill WITH (NOLOCK)
WHERE CompanyKey IN ('" + filter.BranchKey + "'))";
}
// 贷款方
if (!string.IsNullOrEmpty(filter.LoanServiceKey))
{
sql += @" AND b.LendingSideID IN (
SELECT ba.BankAccountID FROM dbo.BankAccount ba
WHERE ba.CompanyKey = '" + filter.LoanServiceKey + "')";
}
return(sql);
}
/// <summary>
/// 检索数据
/// </summary>
/// <param name="filter"></param>
/// <param name="responseEntity"></param>
public void SearchData(InterestFitFilter filter, ResponseEntity responseEntity)
{
if (filter == null)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.RequestCommandError);
return;
}
List <string> companys = Singleton <RedisEnumOperatorBLL> .Instance.GetUserOwnCompanyKeys(responseEntity.UserId);
filter.BranchKey = string.Join("','", companys.ToArray());
// 获取件数
int totalCount = Singleton <InterestFitDAL <Business> > .Instance.GetCount(filter);
if (totalCount <= 0)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult);
m_Logger.Info("未查询到数据。");
return;
}
if (filter.PageNo < 1)
{
filter.PageNo = 1;
}
int fromIndex = (filter.PageNo - 1) * filter.PageSize;
int toIndex = filter.PageNo * filter.PageSize;
if (toIndex > totalCount)
{
toIndex = totalCount;
}
filter.FromIndex = fromIndex;
filter.ToIndex = toIndex;
var lstBusinessDatas = Singleton <InterestFitDAL <Business> > .Instance.SearchData(filter);
if (lstBusinessDatas == null || lstBusinessDatas.Count == 0)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult);
m_Logger.Info("未查询到数据。");
}
else
{
var lstBusinessIds = lstBusinessDatas.Select(x => x.BusinessID);
filter.BusinessIDs = string.Join(",", lstBusinessIds.ToArray());
// 银行账户
var bankAccounts = Singleton <RedisEnumOperatorBLL>
.Instance.GetEnumRedisDataEntitys <BankAccountRedisEntity>();
var lstBills = Singleton <BillDAL <Bill> > .Instance.SearchData(filter);
var lstInterestFiltViewData = new List <InterestFiltViewData>();
foreach (var business in lstBusinessDatas)
{
business.Bills = lstBills.FindAll(x => x.BusinessID == business.BusinessID).ToList();
decimal sum = GetSum(business) + GetInter(business);
var bankser = bankAccounts.FirstOrDefault(x => x.BankAccountID == business.LendingSideID);
lstInterestFiltViewData.Add(new InterestFiltViewData
{
ContractNO = business.ContractNo,
CustomerName = business.CustomerName,
idenNO = business.IdentityNo,
GuaranteeSideDate = business.ToGuaranteeTime.ToDateString(),
SurplusInterest = sum.ToAmtString(),
Loaner = Singleton <RedisEnumOperatorBLL> .Instance.GetRedisData(
RedisEnumOperatorBLL.HashId_LendingGroup_4, bankser.CompanyKey).Name,
PayDate = business.PaymentDate.ToString()
});
}
var responseResult = new ResponseListResult <InterestFiltViewData>();
responseResult.TotalCount = totalCount;
responseResult.LstResult = lstInterestFiltViewData;
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.Success);
responseEntity.Results = responseResult;
}
}