/// <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()); }