/// <summary>
/// 检索数据
/// </summary>
/// <param name="baseFilter"></param>
/// <param name="responseEntity"></param>
public void SearchData(BaseFilter baseFilter, ResponseEntity responseEntity)
{
BusinessExportFilter filter = baseFilter as BusinessExportFilter;
if (filter == null)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.RequestCommandError);
return;
}
var reportDetailList
= Singleton <BusinesssDataExportDAL <BusinessExportViewData> >
.Instance.SearchData(baseFilter);
if (reportDetailList == null || reportDetailList.Count == 0)
{
ServiceUtility.SetResponseStatus(responseEntity, EnumResponseState.NoResult);
m_Logger.Info("未查询到数据！。");
}
else
{
var lstViewBusiness = CreateViewBusinessExtPivot(reportDetailList);
// 设置输出文件
SetExportFile(filter, lstViewBusiness, responseEntity);
}
}
/// <summary>
/// 根据画面检索条件，获取订单筛选件数
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public int GetViewDataBusinessCount(BusinessExportFilter filter)
{
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT COUNT(*) AS TOTALCOUNT");
sb.Append(" FROM [fin].[BusinessBasic] bb");
sb.Append(" JOIN [fin].[BusinessExtend] be");
sb.Append(" ON bb.BusinessID = be.BusinessID");
sb.Append(" JOIN [fin].[BusinessCurrentStaus] bcs");
sb.Append(" ON bb.BusinessID = bcs.BusinessID");
sb.Append(" JOIN [fin].[BusinessLawsuit] bl");
sb.Append(" ON bb.BusinessID = bl.BusinessID");
sb.Append(" JOIN [fin].[BusinessGuarantee] bg");
sb.Append(" ON bb.BusinessID = bg.BusinessID");
sb.Append(" WHERE 1 = 1 ");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(condition);
}
return((int)QueryScalar(sb.ToString()
, null, "PostLoanDB", System.Data.CommandType.Text));
}
/// <summary>
/// 程序执行主入口
/// </summary>
/// <param name="requestEntity"></param>
/// <param name="responseEntity"></param>
protected override void DoExecute(RequestEntity requestEntity, ResponseEntity responseEntity)
{
// 定义接收客户端参数的变量
BusinessExportFilter filter
= ServiceUtility.ConvertToFilterFromDict <BusinessExportFilter>(requestEntity.Parameters);
// 检索数据
Singleton <BusinesssDataExportBLL> .Instance.SearchData(filter, responseEntity);
}
/// <summary>
/// 根据过滤条件，返回检索数据的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetSearchSql(BaseFilter baseFilter)
{
BusinessExportFilter filter = baseFilter as BusinessExportFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT b.BusinessID");
sb.Append(" ,b.ContractNo");
sb.Append(" ,v.CustomerName AS CustomerName");
sb.Append(" ,v.IdenNo AS IdentityCard");
sb.Append(" ,b.OverAmount AS OverdueAmt");
sb.Append(" ,b.CurrentOverAmount AS CurrentDueAmt");
sb.Append(" ,b.BusinessStatus");
sb.Append(" ,b.CLoanStatus");
sb.Append(" ,b.ProductKind");
sb.Append(" ,b.LendingSideKey");
sb.Append(" ,b.ServiceSideKey");
sb.Append(" ,b.GuaranteeSideKey");
sb.Append(" ,b.LoanKind");
sb.Append(" ,b.OverMonth");
sb.Append(" ,b.SalesTeam");
sb.Append(" ,b.SalesManID");
sb.Append(" ,b.LoanTime");
sb.Append(" ,b.ClearLoanTime");
//sb.Append(" ,DATEADD(month, b.LoanPeriod, b.LoanTime) AS ZClearLoanTime");
sb.Append(" ,b.ToLitigationTime");
sb.Append(" ,b.ToGuaranteeTime");
sb.Append(" ,b.SavingCard");
sb.Append(" ,b.BankKey");
sb.Append(" ,b.LoanPeriod");
sb.Append(" ,b.ResidualCapital");
sb.Append(" ,b.BranchKey");
sb.Append(" ,b.LoanCapital");
sb.Append(" ,b.ServiceRate");
sb.Append(" ,b.ProceduresRate");
sb.Append(" ,b.ManagementRate");
sb.Append(" FROM dbo.Business b WITH (NOLOCK)");
sb.Append(" JOIN customer.vw_customer_CustomerBasic v");
sb.Append(" ON v.Bid = b.BusinessID");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(" WHERE 1 = 1 ");
sb.Append(condition);
}
sb.Append(" ORDER BY b.BusinessID");
return(sb.ToString());
}
/// <summary>
/// 根据过滤条件，返回检索件数的Sql文
/// </summary>
/// <param name="baseFilter"></param>
/// <returns></returns>
protected override string GetCountSql(BaseFilter baseFilter)
{
BusinessExportFilter filter = baseFilter as BusinessExportFilter;
if (filter == null)
{
return("");
}
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT COUNT(*) AS TOTALCOUNT");
sb.Append(" FROM dbo.Business b WITH (NOLOCK)");
sb.Append(" WHERE 1 = 1 ");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(condition);
}
return(sb.ToString());
}
/// <summary>
/// 生成检索条件
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private string CombineCondition(BusinessExportFilter filter)
{
StringBuilder sb = new StringBuilder();
if (filter.BusinessID > 0)
{
sb.Append(" AND b.BusinessID = " + filter.BusinessID);
}
if (!string.IsNullOrEmpty(filter.ProductType))
{
sb.Append(" AND b.ProductKind = '" + filter.ProductType + "'");
}
if (!string.IsNullOrEmpty(filter.ContractNo))
{
sb.Append(" AND b.ContractNo = '" + filter.ContractNo + "'");
}
if (filter.BusinessStatus > 0)
{
sb.Append(" AND b.BusinessStatus = " + filter.BusinessStatus);
}
if (filter.CLoanStatus > 0)
{
sb.Append(" AND b.CLoanStatus = " + filter.CLoanStatus);
}
if (!string.IsNullOrEmpty(filter.LoanKind))
{
sb.Append(" AND b.LoanKind = '" + filter.LoanKind + "'");
}
if (filter.OverMonth > 0)
{
sb.Append(" AND b.OverMonth = " + filter.OverMonth);
}
if (!string.IsNullOrEmpty(filter.LendingSideKey))
{
sb.Append(" AND b.LendingSideKey = '" + filter.LendingSideKey + "'");
}
if (!string.IsNullOrEmpty(filter.ServiceSideKey))
{
sb.Append(" AND b.ServiceSideKey = '" + filter.ServiceSideKey + "'");
}
if (!string.IsNullOrEmpty(filter.GuaranteeSideKey))
{
sb.Append(" AND b.GuaranteeSideKey = '" + filter.GuaranteeSideKey + "'");
}
if (!string.IsNullOrEmpty(filter.BranchKey))
{
sb.Append(" AND b.BranchKey = '" + filter.BranchKey + "'");
}
if (!string.IsNullOrEmpty(filter.SalesTeam))
{
sb.Append(" AND b.SalesTeam = '" + filter.SalesTeam + "'");
}
if (filter.SalesManId > 0)
{
sb.Append(" AND b.SalesManID = " + filter.SalesManId);
}
if (filter.LoanDateBegin.HasValue)
{
sb.Append(" AND b.LoanTime >= " + filter.LoanDateBegin);
}
if (filter.LoanDateEnd.HasValue)
{
sb.Append(" AND b.LoanTime <= " + filter.LoanDateEnd);
}
if (filter.CLoanDateBegin.HasValue)
{
sb.Append(" AND b.ClearLoanTime >= " + filter.CLoanDateBegin);
}
if (filter.CLoanDateEnd.HasValue)
{
sb.Append(" AND b.ClearLoanTime <= " + filter.CLoanDateEnd);
}
if (filter.LawsuitDateBegin.HasValue)
{
sb.Append(" AND b.ToLitigationTime >= " + filter.LawsuitDateBegin);
}
if (filter.LawsuitDateEnd.HasValue)
{
sb.Append(" AND b.ToLitigationTime <= " + filter.LawsuitDateEnd);
}
if (filter.GuarteeDateBegin.HasValue)
{
sb.Append(" AND b.ToGuaranteeTime >= " + filter.GuarteeDateBegin);
}
if (filter.GuarteeDateEnd.HasValue)
{
sb.Append(" AND b.ToGuaranteeTime <= " + filter.GuarteeDateEnd);
}
if (filter.ZLoanDateBegin.HasValue)
{
sb.Append(" AND DATEADD(month, b.LoanPeriod, b.LoanTime) >= " + filter.ZLoanDateBegin);
}
if (filter.ZLoanDateEnd.HasValue)
{
sb.Append(" AND DATEADD(month, b.LoanPeriod, b.LoanTime) <= " + filter.ZLoanDateEnd);
}
return(sb.ToString());
}
/// <summary>
/// 根据画面检索条件，获取订单筛选结果
/// </summary>
/// <param name="filter"></param>
/// <param name="fromIndex"></param>
/// <param name="toIndex"></param>
/// <returns></returns>
public List <BusinessExportViewData> GetViewDataBusiness(BusinessExportFilter filter,
int fromIndex, int toIndex)
{
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT * ");
sb.Append(" FROM ( ");
sb.Append(" SELECT bb.BusinessID");
sb.Append(" ,bb.ContractNo");
sb.Append(" ,bb.CustomerName");
sb.Append(" ,bb.IdentityCard");
sb.Append(" ,bb.OverAmount AS OverdueAmt");
sb.Append(" ,bb.CurrentOverAmount AS CurrentDueAmt");
sb.Append(" ,bcs.BusinessStatus");
sb.Append(" ,bb.IsFreeze");
sb.Append(" ,bcs.CLoanStatus");
sb.Append(" ,be.BusinessLogicID AS ProductType");
sb.Append(" ,bb.LendingSide AS LendingSideKey");
sb.Append(" ,bb.ServiceSide AS ServiceSideKey");
sb.Append(" ,bb.GuaranteeSide AS GuaranteeSideKey");
sb.Append(" ,bb.LoanKind");
sb.Append(" ,bcs.OverMonth");
sb.Append(" ,'' AS SalesTeam");
sb.Append(" ,0 AS SalesManID");
sb.Append(" ,bb.LoanTime");
sb.Append(" ,bcs.ClearLoanTime");
sb.Append(" ,DATEADD(month, bb.LoanPeriod, bb.LoanTime) AS ZClearLoanTime");
sb.Append(" ,bl.ToLitigationTime");
sb.Append(" ,bg.ToGuaranteeTime");
sb.Append(" ,cc.SavingCard");
sb.Append(" ,cc.BankID");
sb.Append(" ,bb.LoanPeriod");
sb.Append(" ,bcs.ResidualCapital");
sb.Append(" ,bb.BranchID");
sb.Append(" ,bb.LoanCapital");
sb.Append(" ,birs.InterestRateValue AS ServiceRate");
sb.Append(" ,birp.InterestRateValue AS ProceduresRate");
sb.Append(" FROM [fin].[BusinessBasic] bb");
sb.Append(" JOIN [fin].[BusinessExtend] be");
sb.Append(" ON bb.BusinessID = be.BusinessID");
sb.Append(" JOIN [fin].[BusinessCurrentStaus] bcs");
sb.Append(" ON bb.BusinessID = bcs.BusinessID");
sb.Append(" JOIN [fin].[BusinessLawsuit] bl");
sb.Append(" ON bb.BusinessID = bl.BusinessID");
sb.Append(" JOIN [fin].[BusinessGuarantee] bg");
sb.Append(" ON bb.BusinessID = bg.BusinessID");
sb.Append(" JOIN [fin].[RelationBusinessCustomerCard] rbc");
sb.Append(" ON bb.BusinessID = rbc.BusinessID");
sb.Append(" JOIN [fin].[CustomerCard] cc");
sb.Append(" ON rbc.CustomerCardID = cc.CardID");
sb.Append(" JOIN [fin].[BusinessInterestRateFee] birs");
sb.Append(" ON bb.BusinessID = birs.BusinessID");
sb.Append(" JOIN [fin].[BusinessInterestRateFee] birp");
sb.Append(" ON bb.BusinessID = birp.BusinessID");
sb.Append(" WHERE 1 = 1 ");
sb.Append(" AND birs.InterestSubject = 3");
sb.Append(" AND birp.InterestSubject = 10");
string condition = CombineCondition(filter);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(condition);
}
sb.Append(" ) a");
sb.AppendFormat(" WHERE a.RowId > {0} AND a.RowId <= {1}", fromIndex, toIndex);
return(Query <BusinessExportViewData>(sb.ToString()
, null, "PostLoanDB", System.Data.CommandType.Text));
}
/// <summary>
/// 生成检索条件
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private string CombineCondition(BusinessExportFilter filter)
{
StringBuilder sb = new StringBuilder();
if (filter.BusinessID > 0)
{
sb.Append(" AND bb.Bid = " + filter.BusinessID);
}
if (filter.ProductType > 0)
{
sb.Append(" AND be.BusinessLogicID = " + filter.ProductType);
}
if (!string.IsNullOrEmpty(filter.ContractNo))
{
sb.Append(" AND bb.ContractNo = '" + filter.ContractNo + "'");
}
if (filter.BusinessStatus > 0)
{
sb.Append(" AND bcs.BusinessStatus = " + filter.BusinessStatus);
}
if (filter.CLoanStatus > 0)
{
sb.Append(" AND bcs.CLoanStatus = " + filter.CLoanStatus);
}
if (filter.LoanKind > 0)
{
sb.Append(" AND bb.LoanKind = " + filter.LoanKind);
}
if (filter.OverMonth > 0)
{
sb.Append(" AND bb.OverMonth = " + filter.OverMonth);
}
if (filter.LendingSide > 0)
{
sb.Append(" AND bb.LendingSide = " + filter.LendingSide);
}
if (filter.ServiceSide > 0)
{
sb.Append(" AND bb.ServiceSide = " + filter.ServiceSide);
}
if (filter.GuaranteeSide > 0)
{
sb.Append(" AND bb.GuaranteeSide = " + filter.GuaranteeSide);
}
if (filter.BranchId > 0)
{
sb.Append(" AND bb.BranchID = " + filter.BranchId);
}
if (filter.LoanDateBegin.HasValue)
{
sb.Append(" AND bb.LoanTime >= '" + filter.LoanDateBegin.ToString() + "'");
}
if (filter.LoanDateEnd.HasValue)
{
sb.Append(" AND bb.LoanTime <= '" + filter.LoanDateEnd.ToString() + "'");
}
if (filter.CLoanDateBegin.HasValue)
{
sb.Append(" AND bcs.ClearLoanTime >= '" + filter.CLoanDateBegin.ToString() + "'");
}
if (filter.CLoanDateEnd.HasValue)
{
sb.Append(" AND bcs.ClearLoanTime <= '" + filter.CLoanDateEnd.ToString() + "'");
}
if (filter.LawsuitDateBegin.HasValue)
{
sb.Append(" AND bl.ToLitigationTime >= '" + filter.LawsuitDateBegin.ToString() + "'");
}
if (filter.LawsuitDateEnd.HasValue)
{
sb.Append(" AND bl.ToLitigationTime <= '" + filter.LawsuitDateEnd.ToString() + "'");
}
if (filter.GuarteeDateBegin.HasValue)
{
sb.Append(" AND bg.ToGuaranteeTime >= '" + filter.GuarteeDateBegin.ToString() + "'");
}
if (filter.GuarteeDateEnd.HasValue)
{
sb.Append(" AND bg.ToGuaranteeTime <= '" + filter.GuarteeDateEnd.ToString() + "'");
}
if (filter.ZLoanDateBegin.HasValue)
{
sb.Append(" AND DATEADD(month, bb.LoanPeriod, bb.LoanTime) >= '" + filter.ZLoanDateBegin.ToString() + "'");
}
if (filter.ZLoanDateEnd.HasValue)
{
sb.Append(" AND DATEADD(month, bb.LoanPeriod, bb.LoanTime) <= '" + filter.ZLoanDateEnd.ToString() + "'");
}
return(sb.ToString());
}
