public QueryResult QueryCategoryKPIList(CategoryKPIQueryFilter request) { if (request == null) { throw new BizException(ResouceManager.GetMessageString("IM.Category", "CategoryCondtionInvalid")); } int totalCount; var data = ObjectFactory <ICategoryKPIQueryDA> .Instance.QueryCategoryKPIList(request, out totalCount); var source = new QueryResult { Data = data, TotalCount = totalCount }; return(source); }
/// <summary> /// 查询类别KPI指标 /// </summary> /// <returns></returns> public virtual DataTable QueryCategoryKPIList(CategoryKPIQueryFilter queryCriteria, out int totalCount) { var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PagingInfo.SortBy, StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize, MaximumRows = queryCriteria.PagingInfo.PageSize }; CustomDataCommand dataCommand; if (queryCriteria.CategoryType == CategoryType.CategoryType3) { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCategoryKPIList"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "A.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, "8601"); if (queryCriteria.Status != -999) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status); } if (queryCriteria.C3SysNo != null && queryCriteria.C3SysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, queryCriteria.C3SysNo); } if (queryCriteria.C2SysNo != null && queryCriteria.C2SysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, queryCriteria.C2SysNo); } if (queryCriteria.C1SysNo != null && queryCriteria.C1SysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, queryCriteria.C1SysNo); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); DataTable dt = dataCommand.ExecuteDataTable(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } } else { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCategoryKPIList2"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "A.SysNo DESC")) { if (queryCriteria.Status != -999) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status); } if (queryCriteria.C2SysNo != null && queryCriteria.C2SysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, queryCriteria.C2SysNo); } if (queryCriteria.C1SysNo != null && queryCriteria.C1SysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, queryCriteria.C1SysNo); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); DataTable dt = dataCommand.ExecuteDataTable(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } } //StringBuilder joinTableBuilder = new StringBuilder(); // if (queryCriteria.PMUserSysNo > 0 && queryCriteria.C3SysNo>0) // { // joinTableBuilder.AppendFormat(@" INNER JOIN ( // SELECT DISTINCT(C3.SysNo) FROM IPP3.dbo.Category3 C3 WITH(NOLOCK) // LEFT JOIN IPP3.dbo.Product P WITH(NOLOCK) ON P.C3SysNo=C3.SysNo AND C3.CompanyCode=P.CompanyCode // WHERE P.PMUserSysNo={0} AND C3.CompanyCode=@CompanyCode // )P ON A.SysNo=P.SysNo", queryCriteria.PMUserSysNo); // } // dataCommand.CommandText = dataCommand.CommandText.Replace("#StrJoinTable#", joinTableBuilder.ToString()); }