protected override void AddQueryOptions(VLayoutPanel vPanel)
{
mDisplayHelper.AddOptionItem("单号", "bill", "ID", false);
mDisplayHelper.AddOptionItem("日期", "bill", "Date", false);
mDisplayHelper.AddOptionItem("生产单位", "bill", "ProductionUnit_Name", false);
mDisplayHelper.AddOptionItem("会计单位", "bill", "AccountingUnit_Name", false);
mDisplayHelper.AddOptionItem("部门", "bill", "Department_Name", false);
mDisplayHelper.AddOptionItem("经办人", "bill", "Employee_Name", false);
mDisplayHelper.AddOptionItem("客户", "bill", "Customer_Name", false);
mDisplayHelper.AddOptionItem("存货属性", "detail", "GoodsProperty_Name", false);
mDisplayHelper.AddOptionItem("品牌项", "detail", "BrandItem_Name", false);
mDisplayHelper.AddOptionItem("产品线", "goods", "ProductLine_Name", false);
mDisplayHelper.AddOptionItem("存货编号", "detail", "Goods_Code", false);
mDisplayHelper.AddOptionItem("存货名称", "detail", "Goods_Name", false);
mDisplayHelper.AddOptionItem("规格", "detail", "Goods_Spec", false);
mDisplayHelper.AddOptionItem("主单位", "detail", "Goods_MainUnit", false);
mDisplayHelper.AddOptionItem("主数量", "detail", "Number", true, true);
mDisplayHelper.AddOptionItem("辅单位", "detail", "Goods_SecondUnit", false);
mDisplayHelper.AddOptionItem("辅数量", "detail", "SecondNumber", true, true);
mDisplayHelper.AddOptionItem("计划数量", "noticDetail", "Number", true, true);
mDisplayHelper.AddOptionItem("未完成数量", () => {
return(DQExpression.Subtract(DQExpression.Field(JoinAlias.Create("noticDetail"), "Number"), DQExpression.Field(JoinAlias.Create("noticDetail"), "DoneNumber")));
}, true, true);
AddQueryOption("选项", mDisplayHelper.GetAllDisplayNames(), mDisplayHelper.GetDefaultSelelectedDisplayNames());
base.AddQueryOptions(vPanel);
}
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);
}
static DQueryDom SaleForecastQuery(long customer)
{
if (!PluginManager.Current.Installed("B3Sale"))
{
return(null);
}
var detail = new JoinAlias(Type.GetType("BWP.B3Sale.BO.SaleForecast_Detail, B3Sale"));
var bill = new JoinAlias(Type.GetType("BWP.B3Sale.BO.SaleForecast, B3Sale"));
var dom = new DQueryDom(bill);
dom.From.AddJoin(JoinType.Inner, new DQDmoSource(detail), DQCondition.EQ(detail, "SaleForecast_ID", bill, "ID"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Field(bill, "ID"), "Forecase_ID"));
dom.Columns.Add(DQSelectColumn.Field("SaleGoods_ID", detail));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Subtract(GetNumber(detail, "UnitNum"), GetNumber(detail, "AlreadyToOrderUnitNum")).ECastType <Money <decimal>?>(), "Number"));
dom.Columns.Add(DQSelectColumn.Field("Unit", detail));
dom.Columns.Add(DQSelectColumn.Field("Price", detail));
dom.Columns.Add(DQSelectColumn.Field("Money", detail));
dom.Where.Conditions.Add(DQCondition.EQ(bill, "Customer_ID", customer));
dom.Where.Conditions.Add(DQCondition.EQ(bill, "BillState", 单据状态.已审核));
dom.Where.Conditions.Add(DQCondition.GreaterThan(GetNumber(detail, "UnitNum"), GetNumber(detail, "AlreadyToOrderUnitNum")));
dom.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
return(dom);
}
protected DQueryDom GetQueryDom()
{
var items = GetAllItems();
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(main, "Number");
var 速冻出库袋数Exp = DQExpression.Field(frozenNum, "AllSecondNumber2");
var 产出袋数Exp = DQExpression.Field(main, "SecondNumber2");
foreach (ListItem item in items)
{
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));
detailSumIndex.Add(query.Columns.Count - 1);
break;
case "速冻库差异数量":
query.Columns.Add(DQSelectColumn.Create(DQExpression.Subtract(产出重量Exp, 速冻出库重量Exp), item.Text));
//query.GroupBy.Expressions.Add(DQExpression.Subtract(产出重量Exp, 速冻出库重量Exp));
break;
case "速冻库差异辅数量II":
query.Columns.Add(DQSelectColumn.Create(DQExpression.Subtract(产出袋数Exp, 速冻出库袋数Exp), item.Text));
//query.GroupBy.Expressions.Add(DQExpression.Subtract(产出袋数Exp, 速冻出库袋数Exp));
break;
case "对比值":
var 对比值Exp = DQExpression.Divide(速冻出库重量Exp, DQExpression.NullIfZero(产出重量Exp));
query.Columns.Add(DQSelectColumn.Create(对比值Exp, item.Text));
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);
}
