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