private DQueryDom GetQueryDom()
{
var query = _mQueryContainer.Build();
var goods = JoinAlias.Create("goods");
var pptAlias = JoinAlias.Create("gppt");
var propertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog));
query.From.AddJoin(JoinType.Left, new DQDmoSource(propertyCatalog), DQCondition.EQ(pptAlias, "GoodsPropertyCatalog_ID", propertyCatalog, "ID"));
query.Columns.Add(DQSelectColumn.Field("ID", "序号"));
query.Columns.Add(DQSelectColumn.Field("Code", goods, "存货编码"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(goods, "Name"), "存货名称"));
query.Columns.Add(DQSelectColumn.Field("Spec", goods));
query.Columns.Add(DQSelectColumn.Field("MainUnit", goods));
query.Columns.Add(DQSelectColumn.Field("SecondUnit", goods));
query.Columns.Add(DQSelectColumn.Field("Name", pptAlias, "存货属性"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(propertyCatalog, "Name"), "属性分类"));
query.Columns.Add(DQSelectColumn.Field("Store_Name"));
query.Columns.Add(DQSelectColumn.Field("AccountingUnit_Name"));
query.Where.Conditions.Add(DQCondition.EQ("AccountingUnit_Domain_ID", DomainContext.Current.ID));
if (!_goodsPropertyCatalogBox.IsEmpty)
{
TreeUtil.AddTreeCondition <GoodsPropertyCatalog>(query, long.Parse(_goodsPropertyCatalogBox.Value), propertyCatalog);
}
return(query);
}
private DQueryDom GetQueryDom()
{
var query = _mQueryContainer.Build();
var root = query.From.RootSource.Alias;
var pptAlias = JoinAlias.Create("gppt");
var propertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog));
var alreadySet = new JoinAlias(typeof(Goods_Accounting_Store));
query.From.AddJoin(JoinType.Left, new DQDmoSource(propertyCatalog), DQCondition.EQ(pptAlias, "GoodsPropertyCatalog_ID", propertyCatalog, "ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(alreadySet), DQCondition.EQ(root, "ID", alreadySet, "Goods_ID"));
query.Columns.Add(DQSelectColumn.Field("ID", "存货ID"));
query.Columns.Add(DQSelectColumn.Field("Code"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Columns.Add(DQSelectColumn.Field("Spec"));
query.Columns.Add(DQSelectColumn.Field("MainUnit"));
query.Columns.Add(DQSelectColumn.Field("SecondUnit"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(pptAlias, "Name"), "存货属性"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(propertyCatalog, "Name"), "属性分类"));
query.Columns.Add(DQSelectColumn.Field("AccountingUnit_Name", alreadySet, "已配会计单位"));
if (!_goodsPropertyCatalogBox.IsEmpty)
{
TreeUtil.AddTreeCondition <GoodsPropertyCatalog>(query, long.Parse(_goodsPropertyCatalogBox.Value), propertyCatalog);
}
if (_checkBox.Checked)
{
query.Where.Conditions.Add(DQCondition.IsNull(DQExpression.Field(alreadySet, "Goods_ID")));
}
return(query);
}
protected override DQueryDom GetQueryDom()
{
var dom = base.GetQueryDom();
var prop = new JoinAlias(typeof(GoodsProperty));
dom.From.AddJoin(JoinType.Inner, new DQDmoSource(prop), DQCondition.EQ(prop, "ID", dom.From.RootSource.Alias, "GoodsProperty_ID"));
var catalog = dom.EJoin <GoodsPropertyCatalog>("GoodsPropertyCatalog_ID", JoinType.Left, prop);
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Field(prop, "Name"), "存货属性"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Field(catalog, "Name"), "存货属性分类"));
TreeUtil.AddTreeCondition <GoodsPropertyCatalog>(dom, mQueryContainer, "存货属性分类", catalog);
DomainUtil.AddDomainPermissionLimit(dom, typeof(GoodsProperty), prop);
return(dom);
}
protected override DQueryDom GetQueryDom()
{
var dom = base.GetQueryDom();
var goodsProperty = dom.EJoin <GoodsProperty>();
dom.Columns.Add(DQSelectColumn.Field("ID"));
dom.Columns.Add(DQSelectColumn.Field("Name"));
dom.Columns.Add(DQSelectColumn.Field("Spec"));
dom.Columns.Add(DQSelectColumn.Field("GoodsProperty_Name"));
dom.Columns.Add(DQSelectColumn.Field("GoodsPropertyCatalog_Name", goodsProperty));
dom.Columns.Add(DQSelectColumn.Field("SecondUnit"));
dom.Columns.Add(DQSelectColumn.Field("MainUnit"));
dom.Columns.Add(DQSelectColumn.Field("MainUnitRatio"));
dom.Columns.Add(DQSelectColumn.Field("SecondUnitRatio"));
dom.Columns.Add(DQSelectColumn.Field("UnitConvertDirection"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Snippet <decimal?>("null"), "主数量"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Snippet <decimal?>("null"), "辅数量"));
TreeUtil.AddTreeCondition <GoodsPropertyCatalog>(dom, mQueryContainer, "存货属性分类", null, goodsProperty);
dom.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
return(dom);
}
protected override DQueryDom GetQueryDom()
{
mBrowseGrid.EnableRowsGroup = _showTypeList.Items.FindByText("合并单元格").Selected;
var query = base.GetQueryDom();
OrganizationUtil.AddOrganizationLimit <Department>(query, "Department_ID");
OrganizationUtil.AddOrganizationLimit <Store>(query, "Store_ID");
var bill = query.From.RootSource.Alias;
var detail = JoinAlias.Create("detail");
var goodsAlias = new JoinAlias(typeof(Goods));
var goodsProperty = new JoinAlias(typeof(GoodsProperty));
var propertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog));
query.From.AddJoin(JoinType.Left, new DQDmoSource(goodsAlias), DQCondition.EQ(detail, "Goods_ID", goodsAlias, "ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(goodsProperty), DQCondition.EQ(goodsAlias, "GoodsProperty_ID", goodsProperty, "ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(propertyCatalog), DQCondition.EQ(goodsProperty, "GoodsPropertyCatalog_ID", propertyCatalog, "ID"));
foreach (ListItem field in checkbox.Items)
{
if (field.Selected)
{
SetQueryResult(query, field);
if (sumFileds.Contains(field.Value))
{
query.Columns.Add(DQSelectColumn.Sum(detail, field.Value));
SumColumnIndexs.Add(query.Columns.Count - 1);
}
else if (goodsFields.Contains(field.Value))
{
query.Columns.Add(DQSelectColumn.Field(field.Value, goodsAlias));
query.GroupBy.Expressions.Add(DQExpression.Field(goodsAlias, field.Value));
}
else if (mainFields.Contains(field.Value))
{
query.Columns.Add(DQSelectColumn.Field(field.Value));
query.GroupBy.Expressions.Add(DQExpression.Field(field.Value));
}
else if (field.Text == "每日")
{
var snippetDay = DQExpression.Snippet <DateTime?>("(Convert(nvarchar(10),[bill].[InStoreDate], 23))");
query.Columns.Add(DQSelectColumn.Create(snippetDay, "每日"));
query.GroupBy.Expressions.Add(snippetDay);
}
else if (field.Text == "每月")
{
var snippetMonth = DQExpression.Snippet <string>("Left(Convert(nvarchar(10),[bill].[InStoreDate], 23),7)");
query.Columns.Add(DQSelectColumn.Create(snippetMonth, "每月"));
query.GroupBy.Expressions.Add(snippetMonth);
}
else if (field.Text == "每年")
{
var snippetMonth = DQExpression.Snippet <string>("Left(Convert(nvarchar(10),[bill].[InStoreDate], 23),4)");
query.Columns.Add(DQSelectColumn.Create(snippetMonth, "每月"));
query.GroupBy.Expressions.Add(snippetMonth);
}
else if (field.Text == "标签")
{
var tarName = new JoinAlias(typeof(Dmo_TagNames));
query.From.AddJoin(JoinType.Left, new DQDmoSource(tarName), DQCondition.And(DQCondition.EQ(tarName, "DmoID", query.From.RootSource.Alias, "ID"), DQCondition.EQ(tarName, "DmoTypeID", mDmoTypeID)));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(tarName, "Names"), "标签"));
query.GroupBy.Expressions.Add(DQExpression.Field(tarName, "Names"));
}
else if (field.Text == "存货属性")
{
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(goodsProperty, "Name"), field.Text));
query.GroupBy.Expressions.Add(DQExpression.Field(goodsProperty, "Name"));
}
else if (field.Text == "属性分类")
{
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(propertyCatalog, "Name"), field.Text));
query.GroupBy.Expressions.Add(DQExpression.Field(propertyCatalog, "Name"));
var v = 0;
if (!depth.IsEmpty && int.TryParse(depth.Text, out v))
{
if (v < 0)
{
v = 0;
}
if (v > 8)
{
v = 8;
}
for (var i = 1; i <= v; i++)
{
var p = new JoinAlias("_p" + i, typeof(GoodsPropertyCatalog));
query.From.AddJoin(JoinType.Left, new DQDmoSource(p), DQCondition.EQ(p, "ID", propertyCatalog, string.Format("TreeDeep{0}ID", i)));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(p, "Name"), i + "级分类"));
query.GroupBy.Expressions.Add(DQExpression.Field(p, "Name"));
}
}
}
else if (field.Text == "货位")
{
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, "CargoSpace_Name"), field.Text));
query.GroupBy.Expressions.Add(DQExpression.Field(detail, "CargoSpace_Name"));
}
else if (field.Text == "产品线")
{
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(goodsAlias, field.Value), field.Text));
query.GroupBy.Expressions.Add(DQExpression.Field(goodsAlias, field.Value));
}
else
{
//用户自定义模块的字段 比如永达
if (field.Value.StartsWith(CustomMainFieldBegin))
{
continue;
}
var s = field.Value;
if (field.Value == "DRemark")
{
s = "Remark";
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, s), "备注"));
}
else
{
query.Columns.Add(DQSelectColumn.Field(s, detail));
}
query.GroupBy.Expressions.Add(DQExpression.Field(detail, s));
}
}
}
if (!string.IsNullOrEmpty(goodsOrigin.Text))
{
query.Where.Conditions.Add(DQCondition.Like(goodsAlias, "Origin", goodsOrigin.Text));
}
if (!goodsName.IsEmpty)
{
var nameList = goodsName.Text.Split(' ');
var conditonList = new List <IDQExpression>();
foreach (var n in nameList)
{
conditonList.Add(DQCondition.Like(goodsAlias, "Name", n));
}
query.Where.Conditions.Add(DQCondition.Or(conditonList));
}
query.Where.Conditions.Add(DQCondition.And(DQCondition.EQ("Domain_ID", DomainContext.Current.ID)));
var brand = mQueryContainer.GetControl <DFChoiceBox>("Goods_Brand");
if (!brand.IsEmpty)
{
query.Where.Conditions.Add(DQCondition.EQ(goodsAlias, "Brand", brand.Value));
}
var gProperty = mQueryContainer.GetControl <DFChoiceBox>("GoodsProperty_ID");
if (!gProperty.IsEmpty)
{
query.Where.Conditions.Add(DQCondition.EQ(goodsProperty, "ID", gProperty.Value));
}
if (!_productLine.IsEmpty)
{
query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field(goodsAlias, "ProductLine_ID"), _productLine.GetValues().Select(x => DQExpression.Value(x)).ToArray()));
}
if (!_producttypeLine.IsEmpty)
{
query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field(goodsAlias, "ProductLineCategory_ID"), _producttypeLine.GetValues().Select(x => DQExpression.Value(x)).ToArray()));
}
TreeUtil.AddTreeCondition <GoodsPropertyCatalog>(query, mQueryContainer, "PropertyCatalog_ID", propertyCatalog);
TagWebUtil.AddTagQueryCondition(mDmoTypeID, query, bill, mQueryContainer);
if (query.Columns.Count == 0)
{
throw new Exception("至少选择一条显示列");
}
return(query);
}