public virtual QueryResult QueryGroupBuying(GroupBuyingQueryFilter msg) { int totalCount; var ds = ObjectFactory <IGroupBuyingQueryDA> .Instance.Query(msg, out totalCount); //为DataSet中的两个table建立关系 DataRelation relGroupBuyingPrice = new DataRelation("GroupBuyingPrice", ds.Tables[0].Columns["SysNo"], ds.Tables[1].Columns["ProductGroupBuyingSysNo"]); ds.Relations.Add(relGroupBuyingPrice); //在第一个table中增加一列容纳阶梯价格 const string priceInfoColName = "PriceInfo"; string priceInfoFormat = "{0}({1}人)" + Environment.NewLine; var dtMaster = ds.Tables[0]; dtMaster.Columns.Add(priceInfoColName, typeof(string)); //将阶梯价格拼接到自定义列 foreach (DataRow drMaster in dtMaster.Rows) { string priceInfoContent = ""; foreach (DataRow drPrice in drMaster.GetChildRows(relGroupBuyingPrice)) { priceInfoContent += string.Format(priceInfoFormat , Convert.ToDecimal(drPrice["GroupBuyingPrice"]).ToString("F2") , drPrice["SellCount"]); } drMaster[priceInfoColName] = priceInfoContent; } return(new QueryResult() { Data = ds.Tables[0], TotalCount = totalCount }); }
public DataTable Query(int productSysNo) { GroupBuyingQueryFilter filter = new GroupBuyingQueryFilter() { ProductSysNo = productSysNo }; int count = 0; return(Query(filter, out count).Tables[0]); }
//分页查询 public QueryResult <GroupBuyingQueryResult> Query(GroupBuyingQueryFilter filter) { if (filter.BeginDateTo.HasValue) { filter.BeginDateTo = filter.BeginDateTo.Value.AddDays(1); } if (filter.EndDateTo.HasValue) { filter.EndDateTo = filter.EndDateTo.Value.AddDays(1); } return(m_GroupBuyingDA.Query(filter)); }
public DataSet Query(GroupBuyingQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.PagingInfo.SortBy; pagingEntity.MaximumRows = filter.PagingInfo.PageSize; pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize; var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductGroupBuyingList"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "M.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo); if (filter.GroupBuyingCategorySysNo.HasValue && filter.GroupBuyingCategorySysNo.Value > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GroupBuyingCategorySysNo", DbType.Int32, "@GroupBuyingCategorySysNo", QueryConditionOperatorType.Equal, filter.GroupBuyingCategorySysNo.Value); } if (filter.GroupBuyingAreaSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GroupBuyingAreaSysNo", DbType.Int32, "@GroupBuyingAreaSysNo", QueryConditionOperatorType.Equal, filter.GroupBuyingAreaSysNo); } if (filter.GroupBuyingVendorSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.GroupBuyingVendorSysNo); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, filter.C2SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.C3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, filter.C3SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.SysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo); #region 时间过滤 if (filter.InDateTo != null) { filter.InDateTo = (filter.InDateTo == filter.InDateFrom ? filter.InDateTo.Value.AddDays(1) : filter.InDateTo); } if (filter.BeginDateTo != null) { filter.BeginDateTo = (filter.BeginDateTo == filter.BeginDateFrom ? filter.BeginDateTo.Value.AddDays(1) : filter.BeginDateTo); } if (filter.EndDateTo != null) { filter.EndDateTo = (filter.EndDateTo == filter.EndDateFrom ? filter.EndDateTo.Value.AddDays(1) : filter.EndDateTo); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.InDate", DbType.DateTime, "@InDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.InDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.InDate", DbType.DateTime, "@InDateTo", QueryConditionOperatorType.LessThan, filter.InDateTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.BeginDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.BeginDate", DbType.DateTime, "@BeginDateTo", QueryConditionOperatorType.LessThan, filter.BeginDateTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.EndDate", DbType.DateTime, "@EndDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.EndDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.EndDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, filter.EndDateTo); #endregion sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "M.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "c.CategoryType", DbType.Int32, "@CategoryType", QueryConditionOperatorType.Equal, filter.CategoryType); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "M.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); //TODO:添加渠道过滤条件 cmd.CommandText = sqlBuilder.BuildQuerySql(); var ds = cmd.ExecuteDataSet(); foreach (DataRow row in ds.Tables[0].Rows) { if (row["VendorSysNo"] != DBNull.Value && Convert.ToInt32(row["VendorSysNO"].ToString()) == 0) { row["VendorName"] = "泰隆优选"; } } //cmd.ExecuteDataTable(); cmd.ConvertEnumColumn(ds.Tables[0], new EnumColumnList { { "Status", typeof(GroupBuyingStatus) }, { "GroupBuyingCategoryType", typeof(GroupBuyingCategoryType) } }); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(ds); } }
public QueryResult <GroupBuyingQueryResult> Query(GroupBuyingQueryFilter filter) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.SortFields; pagingEntity.MaximumRows = filter.PageSize; pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize; var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductGroupBuyingList"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "M.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.SysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.BeginDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.BeginDate", DbType.DateTime, "@BeginDateTo", QueryConditionOperatorType.LessThan, filter.BeginDateTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.EndDate", DbType.DateTime, "@EndDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.EndDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.EndDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, filter.EndDateTo); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "M.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[GroupBuyingTitle]", DbType.String, "@GroupBuyingTitle", QueryConditionOperatorType.Like, filter.GroupBuyingTitle); cmd.CommandText = sqlBuilder.BuildQuerySql(); var ds = cmd.ExecuteDataSet(); var dataList = DataMapper.GetEntityList <GroupBuyingQueryResult, List <GroupBuyingQueryResult> >(ds.Tables[0].Rows, true, true); var priceInfoList = DataMapper.GetEntityList <GroupBuyingPriceInfo, List <GroupBuyingPriceInfo> >(ds.Tables[1].Rows, true, true); foreach (var data in dataList) { var found = priceInfoList.FirstOrDefault(item => item.ProductGroupBuyingSysNo == data.SysNo); if (found != null) { data.GroupBuyPrice = found.GroupBuyingPrice; } } int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); QueryResult <GroupBuyingQueryResult> result = new QueryResult <GroupBuyingQueryResult>(); result.ResultList = dataList; result.PageInfo = new PageInfo() { TotalCount = totalCount, PageIndex = filter.PageIndex, PageSize = filter.PageSize }; return(result); } }