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