public static List <GoodsInfoDto> GetTodayGoodsByStore(long accountUnitId, long departId, long storeId, long productPlanId)
{
var list = new List <GoodsInfoDto>();
var bill = new JoinAlias(typeof(ProduceOutput));
var detail = new JoinAlias(typeof(ProduceOutput_Detail));
var query = new DQueryDom(bill);
query.From.AddJoin(JoinType.Inner, new DQDmoSource(detail), DQCondition.EQ(bill, "ID", detail, "ProduceOutput_ID"));
query.Where.Conditions.Add(DQCondition.EQ("AccountingUnit_ID", accountUnitId));
//query.Where.Conditions.Add(DQCondition.EQ("Department_ID", departId));
//DQCondition.EQ(string.Format("Department_TreeDeep{0}ID", context.Department_Depth), context.Department_ID)
query.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
query.Where.Conditions.Add(DQCondition.EQ("BillState", 单据状态.已审核));
query.Where.Conditions.Add(DQCondition.And(DQCondition.GreaterThanOrEqual(bill, "Time", DateTime.Today.AddDays(-3)), DQCondition.LessThanOrEqual(bill, "Time", DateTime.Today.AddDays(1))));
//query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual(bill, "Time", DateTime.Today));
//query.Where.Conditions.Add(DQCondition.LessThan(bill, "Time", DateTime.Today.AddDays(1)));
query.Where.Conditions.Add(DQCondition.EQ("FrozenStore_ID", storeId));
query.Where.Conditions.Add(DQCondition.EQ("PlanNumber_ID", productPlanId));
query.Where.Conditions.Add(DQCondition.NotInSubQuery(DQExpression.Field(detail, "Goods_ID"), GetTodayGoodsByStoreSubQuery(accountUnitId, departId, storeId, productPlanId)));
query.Where.Conditions.EFieldInList(DQExpression.Field(bill, "PlanNumber_ID"), GetProductPlan().Select(x => x.ID).ToArray());
OrganizationUtil.AddOrganizationLimit(query, typeof(ProduceOutput));
query.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
//query.Columns.Add(DQSelectColumn.Field("Goods_InnerPackingPer", detail));
query.Columns.Add(DQSelectColumn.Sum(detail, "Number"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Divide(DQExpression.Sum(DQExpression.Field(detail, "Number")), DQExpression.Field(detail, "Goods_InnerPackingPer")), "InnerPackingPer"));
query.Columns.Add(DQSelectColumn.Field("Goods_InnerPackingPer", detail));
//query.Columns.Add(DQSelectColumn.Create(DQExpression.Divide(DQExpression.Field(detail,"Number"),DQExpression.Field(detail, "Goods_InnerPackingPer")),"包装数"));
query.GroupBy.Expressions.Add(DQExpression.Field(detail, "Goods_ID"));
query.GroupBy.Expressions.Add(DQExpression.Field(detail, "Goods_Name"));
query.GroupBy.Expressions.Add(DQExpression.Field(detail, "Goods_InnerPackingPer"));
using (var session = Dmo.NewSession())
{
using (var reader = session.ExecuteReader(query))
{
while (reader.Read())
{
var goods = new GoodsInfoDto
{
Goods_ID = (long)reader[0],
Goods_Name = (string)reader[1],
Number = (decimal?)(Money <decimal>?)reader[2],
InnerPackingPer = (decimal?)reader[3],
Goods_InnerPackingPer = (decimal?)reader[4]
};
list.Add(goods);
}
}
}
return(list);
}
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);
}
private DQueryDom GetQueryDom()
{
var detail = new JoinAlias("__detail", typeof(ProduceOutput_Detail));
var main = new JoinAlias("__main", typeof(ProduceOutput));
var goods = new JoinAlias("__goods", typeof(ButcheryGoods));
var query = new DQueryDom(detail);
query.From.AddJoin(JoinType.Left, new DQDmoSource(main), DQCondition.EQ(main, "ID", detail, "ProduceOutput_ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(goods), DQCondition.EQ(goods, "ID", detail, "Goods_ID"));
query.Columns.Add(DQSelectColumn.Field("Goods_Name"));
query.Columns.Add(DQSelectColumn.Field("PlanNumber_Name", detail));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, "Remark"), "计数规格"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Field(detail, "SecondNumber")), "盘数"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Field(detail, "SecondNumber2")), "袋数"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Field(detail, "Number")), "重量"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(goods, "PackageModel"), "包装模式"));
query.GroupBy.Expressions.Add(DQExpression.Field(detail, "Goods_Name"));
query.GroupBy.Expressions.Add(DQExpression.Field(detail, "PlanNumber_Name"));
query.GroupBy.Expressions.Add(DQExpression.Field(detail, "Remark"));
query.GroupBy.Expressions.Add(DQExpression.Field(goods, "PackageModel"));
if (Shift_ID != null)
{
query.Where.Conditions.Add(DQCondition.EQ(detail, "Goods_ProductShift_ID", Shift_ID));
}
if (PackMode != null)
{
query.Where.Conditions.Add(DQCondition.EQ(goods, "PackageModel", PackMode));
}
if (BeginDate != null)
{
query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual(main, "Time", BeginDate));
}
if (EndDate != null)
{
query.Where.Conditions.Add(DQCondition.LessThanOrEqual(main, "Time", EndDate));
}
query.OrderBy.Expressions.Add(DQOrderByExpression.Create(DQExpression.Field(goods, "PackageModel"), false));
return(query);
}
private static DQueryDom GetAllNumDom(DateTime?date)
{
var main = new JoinAlias("__frezenMainte", typeof(FrozenOutStore));
var detail = new JoinAlias("__frezenMainte_De", typeof(FrozenOutStore_Detail));
var dom = new DQueryDom(main);
dom.From.AddJoin(JoinType.Left, new DQDmoSource(detail), DQCondition.EQ(detail, "FrozenOutStore_ID", main, "ID"));
dom.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Field(detail, "Number")), "AllNumber"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Sum(DQExpression.Field(detail, "SecondNumber2")), "AllSecondNumber2"));
if (date != null)
{
var c2 = DQCondition.LessThanOrEqual(main, "Date", date.Value.AddDays(2).AddSeconds(-1));
var c1 = DQCondition.GreaterThanOrEqual(main, "Date", date.Value.AddDays(1));
dom.Where.Conditions.Add(DQCondition.And(c1, c2));
}
dom.GroupBy.Expressions.Add(DQExpression.Field(detail, "Goods_ID"));
dom.Where.Conditions.Add(DQCondition.InEQ(main, "BillState", 1));
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);
}