protected override DQueryDom GetQueryDom() { var query = base.GetQueryDom(); OrganizationUtil.AddOrganizationLimit <Department>(query, "Department_ID"); var detail = JoinAlias.Create("detail"); var goodsAlias = new JoinAlias(typeof(Goods)); query.From.AddJoin(JoinType.Left, new DQDmoSource(goodsAlias), DQCondition.EQ(detail, "Goods_ID", goodsAlias, "ID")); foreach (ListItem field in checkbox.Items) { if (field.Selected) { 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 { query.Columns.Add(DQSelectColumn.Field(field.Value)); query.GroupBy.Expressions.Add(DQExpression.Field(field.Value)); } } } if (!string.IsNullOrEmpty(goodsName.Text)) { query.Where.Conditions.Add(DQCondition.Or(DQCondition.Like(goodsAlias, "Name", goodsName.Text), DQCondition.Like(goodsAlias, "Spell", goodsName.Text))); } if (!string.IsNullOrEmpty(goodsCode.Text)) { query.Where.Conditions.Add(DQCondition.Like(goodsAlias, "Code", goodsCode.Text)); } if (sd.Value.HasValue) { query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual("Date", sd.Value.Value)); } if (ed.Value.HasValue) { query.Where.Conditions.Add(DQCondition.LessThanOrEqual("Date", ed.Value.Value)); } query.Where.Conditions.Add(DQCondition.And(DQCondition.EQ("BillState", 单据状态.已审核), DQCondition.EQ("Domain_ID", DomainContext.Current.ID))); if (query.Columns.Count == 0) { throw new Exception("至少选择一条显示列"); } return(query); }
protected override DQueryDom GetQueryDom() { var alias = new JoinAlias(typeof(tempClass)); var query = new DQueryDom(alias); query.RegisterQueryTable(typeof(tempClass), new string[] { "Department_ID", "AccountingUnit_ID", "PlanNumber_ID", "AccountingUnit_Name", "PlanNumber_Name", "Name", "Code", "OutputNumber", "InStoreNumber" }, tempClass.GetQueryDom(mQueryContainer, sd, ed)); foreach (ListItem field in checkbox.Items) { if (field.Selected) { if (field.Value == "OutputNumber" || field.Value == "InStoreNumber") { query.Columns.Add(DQSelectColumn.Sum(field.Value)); SumColumnIndexs.Add(query.Columns.Count - 1); } else if (field.Value == "DiffNumber") { query.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Subtract(DQExpression.IfNull(DQExpression.Field("OutputNumber"), DQExpression.Value(0)), DQExpression.IfNull(DQExpression.Field("InStoreNumber"), DQExpression.Value(0)))), field.Text)); SumColumnIndexs.Add(query.Columns.Count - 1); } else { query.Columns.Add(DQSelectColumn.Field(field.Value)); query.GroupBy.Expressions.Add(DQExpression.Field(field.Value)); } } } var accids = mQueryContainer.GetControl <DFChoiceBox>("AccountingUnit_ID").Value.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); var pnumberids = mQueryContainer.GetControl <DFChoiceBox>("PlanNumber_ID").Value.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); if (accids.Length > 0) { query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field("AccountingUnit_ID"), accids.Select(x => DQExpression.Value(long.Parse(x))).ToArray())); } if (pnumberids.Length > 0) { query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field("PlanNumber_ID"), pnumberids.Select(x => DQExpression.Value(long.Parse(x))).ToArray())); } OrganizationUtil.AddOrganizationLimit <Department>(query, "Department_ID"); if (query.Columns.Count == 0) { throw new Exception("至少选择一条显示列"); } return(query); }
protected override DQueryDom GetQueryDom() { var main = JoinAlias.Create("bill"); var detail = JoinAlias.Create("detail"); var query = base.GetQueryDom(); OrganizationUtil.AddOrganizationLimit <Department>(query, "Department_ID"); foreach (ListItem field in _checkbox.Items) { if (field.Selected) { switch (field.Text) { case "日期": case "单据号": case "会计单位": case "部门": case "仓库": case "摘要": query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(main, field.Value), field.Text)); query.GroupBy.Expressions.Add(DQExpression.Field(main, field.Value)); break; case "计划号": case "存货名称": case "存货编码": case "规格": case "主单位": case "辅单位": case "辅单位II": case "备注": query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, field.Value), field.Text)); query.GroupBy.Expressions.Add(DQExpression.Field(detail, field.Value)); break; case "主数量": case "辅数量": case "辅数量II": query.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Field(detail, field.Value)), field.Text)); SumColumnIndexs.Add(query.Columns.Count - 1); break; } } } return(query); }
protected override DQueryDom GetQueryDom() { var main = new JoinAlias(typeof(ProductOutTemp)); var query = new DQueryDom(main); ProductOutTemp.Register(query, dateInput.Date); var frozen = new JoinAlias("tempFrozen", typeof(FrozenOutTemp)); var frozenNum = new JoinAlias("tempFrozenAllNumber", typeof(FrozeTemp)); FrozenOutTemp.Register(query, dateInput.Value); FrozenOutTemp.AddJoin(query, frozen); FrozeTemp.Register(query, dateInput.Value); FrozeTemp.AddJoin(query, frozenNum); var 速冻出库重量Exp = DQExpression.Field(frozenNum, "AllNumber"); var 速冻出库袋数Exp = DQExpression.Field(frozenNum, "AllSecondNumber2"); foreach (ListItem item in _checkbox.Items) { if (item.Selected) { var field = item.Value; if (field.StartsWith("_")) { field = field.Substring(4); } switch (item.Text) { case "会计单位": case "部门": case "经办人": case "产出单品名": case "速冻出库品名": case "产出数量": case "产出辅数量": query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(main, field), item.Text)); query.GroupBy.Expressions.Add(DQExpression.Field(main, field)); break; case "速冻出库数量": query.Columns.Add(DQSelectColumn.Create(速冻出库重量Exp, item.Text)); query.GroupBy.Expressions.Add(速冻出库重量Exp); break; case "速冻出库辅数量": query.Columns.Add(DQSelectColumn.Create(速冻出库袋数Exp, item.Text)); query.GroupBy.Expressions.Add(速冻出库袋数Exp); break; case "包装品名": query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(frozen, field), item.Text)); query.GroupBy.Expressions.Add(DQExpression.Field(frozen, field)); break; case "包装数量": case "包装辅数量": query.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Field(frozen, field)), item.Text)); SumColumnIndexs.Add(query.Columns.Count - 1); break; case "产出单差异(重量)": var 产出重量Exp = DQExpression.Field(main, "Number"); query.Columns.Add(DQSelectColumn.Create(DQExpression.Subtract(产出重量Exp, 速冻出库重量Exp), item.Text)); query.GroupBy.Expressions.Add(DQExpression.Subtract(产出重量Exp, 速冻出库重量Exp)); break; case "产出单差异(袋数)": var 产出袋数Exp = DQExpression.Field(main, "SecondNumber2"); query.Columns.Add(DQSelectColumn.Create(DQExpression.Subtract(产出袋数Exp, 速冻出库袋数Exp), item.Text)); query.GroupBy.Expressions.Add(DQExpression.Subtract(产出袋数Exp, 速冻出库袋数Exp)); break; } } } if (!string.IsNullOrEmpty(auuInput.Value)) { query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field(main, "AccountingUnit_Name"), auuInput.GetValues().Select(x => DQExpression.Value(x)).ToArray())); } if (!string.IsNullOrEmpty(departInput.Value)) { query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field(main, "Department_Name"), departInput.GetValues().Select(x => DQExpression.Value(x)).ToArray())); } if (!string.IsNullOrEmpty(goodInput.Value)) { query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field(main, "Goods_ID"), goodInput.GetValues().Select(x => DQExpression.Value(x)).ToArray())); } return(query); }
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); }
protected override DQueryDom GetQueryDom() { var query = base.GetQueryDom(); OrganizationUtil.AddOrganizationLimit <Department>(query, "Department_ID"); var detail = JoinAlias.Create("detail"); var goodsAlias = new JoinAlias(typeof(Goods)); var alias = query.From.RootSource.Alias; var goodsPropertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog)); var goodsProperty = new JoinAlias(typeof(GoodsProperty)); 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(goodsProperty, "ID", goodsAlias, "GoodsProperty_ID")); query.From.AddJoin(JoinType.Left, new DQDmoSource(goodsPropertyCatalog), DQCondition.EQ(goodsProperty, "GoodsPropertyCatalog_ID", goodsPropertyCatalog, "ID")); foreach (ListItem field in _checkbox.Items) { if (field.Selected) { 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)) { TakeValueCustomer(query, field, detail);//耘垦客户要求客户字段取值明细表中的客户字段 } else if (detailFields.Contains(field.Value)) { query.Columns.Add(DQSelectColumn.Field(field.Value, detail)); query.GroupBy.Expressions.Add(DQExpression.Field(detail, field.Value)); } else if (field.Value == "摘要") { query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(alias, "Remark"), "摘要")); query.GroupBy.Expressions.Add(DQExpression.Field(alias, "Remark")); } else if (field.Value == "GoodsPropertyCatalog_Name") { query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(goodsPropertyCatalog, "Name"), "属性分类")); query.GroupBy.Expressions.Add(DQExpression.Field(goodsPropertyCatalog, "Name")); } else if (field.Value == "GoodsProperty_Name") { query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(goodsProperty, "Name"), "存货属性")); query.GroupBy.Expressions.Add(DQExpression.Field(goodsProperty, "Name")); } else { query.Columns.Add(DQSelectColumn.Field(field.Value, detail)); query.GroupBy.Expressions.Add(DQExpression.Field(detail, field.Value)); } } } query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", DomainContext.Current.ID)); if (!摘要.IsEmpty) { query.Where.Conditions.Add(DQCondition.And(DQCondition.Like(alias, "Remark", 摘要.Text))); } if (!备注.IsEmpty) { query.Where.Conditions.Add(DQCondition.And(DQCondition.Like(detail, "Remark", 备注.Text))); } if (query.Columns.Count == 0) { throw new Exception("至少选择一条显示列"); } return(query); }