protected override DQueryDom GetQueryDom()
{
var query = base.GetQueryDom();
OrganizationUtil.AddOrganizationLimit <Department>(query, "Department_ID");
foreach (ListItem field in checkbox.Items)
{
if (field.Selected)
{
if (field.Text == "金额")
{
query.Columns.Add(DQSelectColumn.Create(DQExpression.Multiply(DQExpression.Field("Number"), DQExpression.Field("Price")), field.Text));
}
else
{
query.Columns.Add(DQSelectColumn.Field(field.Value));
}
}
}
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.EQ("Domain_ID", DomainContext.Current.ID));
if (query.Columns.Count == 0)
{
throw new Exception("至少选择一条显示列");
}
return(query);
}
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 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);
}
private List <Tuple <long, string> > GetShiftInfo()
{
var query = new DQueryDom(new JoinAlias(typeof(ProductShift)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", DomainContext.Current.ID));
OrganizationUtil.AddOrganizationLimit(query, typeof(ProductShift));
return(query.EExecuteList <long, string>());
}
public static List <FrozenInStoreObj> GetFrozenInStore(bool?positiveNumber)
{
var bill = new JoinAlias(typeof(FrozenInStore));
var detail = new JoinAlias(typeof(FrozenInStore_Detail));
var query = new DQueryDom(bill);
query.From.AddJoin(JoinType.Left, new DQDmoSource(detail), DQCondition.EQ(bill, "ID", detail, "FrozenInStore_ID"));
query.Columns.Add(DQSelectColumn.Field("ID", bill));
query.Columns.Add(DQSelectColumn.Field("Date", bill));
query.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
query.Columns.Add(DQSelectColumn.Field("Number", detail));
query.Columns.Add(DQSelectColumn.Field("Client", bill));
query.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
query.Where.Conditions.Add(DQCondition.Or(DQCondition.EQ(bill, "BillState", 单据状态.已审核), DQCondition.EQ(bill, "BillState", 单据状态.未审核)));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create(bill, "ID", true));
OrganizationUtil.AddOrganizationLimit(query, typeof(FrozenInStore));
var list = new List <FrozenInStoreObj>();
using (var session = Dmo.NewSession())
{
using (var reader = session.ExecuteReader(query))
{
while (reader.Read())
{
if (positiveNumber == true)
{
if ((Money <decimal>?)reader[4] < 0)
{
continue;
}
}
else
{
if ((Money <decimal>?)reader[4] >= 0)
{
continue;
}
}
list.Add(new FrozenInStoreObj
{
ID = (long)reader[0],
Date = (DateTime?)reader[1],
Goods_ID = (long?)reader[2],
Goods_Name = (string)reader[3],
Number = (Money <decimal>?)reader[4],
Client = (string)reader[5]
});
}
}
}
return(list);
}
static DQueryDom GetPlanDquery(out JoinAlias billAlias, bool isAndSetedGoods = false)
{
var bill = new JoinAlias(typeof(ProductPlan));
billAlias = bill;
var detail = new JoinAlias(typeof(ProductPlan_OutputDetail));
var goods = new JoinAlias(typeof(Goods));
var query = new DQueryDom(bill);
query.From.AddJoin(JoinType.Inner, new DQDmoSource(detail), DQCondition.EQ(bill, "ID", detail, "ProductPlan_ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(goods), DQCondition.EQ(goods, "ID", detail, "Goods_ID"));
query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual(bill, "Date", DateTime.Today));
query.Where.Conditions.Add(DQCondition.LessThan(bill, "Date", DateTime.Today.AddDays(1)));
query.Where.Conditions.Add(DQCondition.EQ(bill, "BillState", 单据状态.已审核));
OrganizationUtil.AddOrganizationLimit(query, typeof(ProductPlan));
query.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_MainUnit", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnit", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_UnitConvertDirection", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_MainUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_Code", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitII", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitII_MainUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitII_SecondUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_ID", goods));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_Name", goods));
query.Columns.Add(DQSelectColumn.Field("GoodsPropertyCatalog_Name", goods));
// query.Columns.Add(DQSelectColumn.Field("GoodsPropertyCatalog_Sort", goods));
query.Columns.Add(DQSelectColumn.Field("Spell", goods));
query.Columns.Add(DQSelectColumn.Field("Spec", goods));
// query.OrderBy.Expressions.Add(DQOrderByExpression.Create(goods, "GoodsPropertyCatalog_Sort"));
query.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
if (isAndSetedGoods)
{
query.Where.Conditions.Add(DQCondition.InSubQuery(DQExpression.Field(goods, "ID"), GetAllSettingedGoodsSubQuery()));
}
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);
}
private static List <BaseInfoDto> GetBaseInfoDQueryDom(Type type, bool hasCode, BaseInfoQueryDto queryDto)
{
var list = new List <BaseInfoDto>();
var query = new DQueryDom(new JoinAlias(type));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
if (hasCode)
{
query.Columns.Add(DQSelectColumn.Field("Code"));
}
query.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", DomainContext.Current.ID));
if (queryDto.PageSize > 0)
{
query.Range = new SelectRange(queryDto.PageIndex * queryDto.PageSize, queryDto.PageSize);
}
if (!string.IsNullOrWhiteSpace(queryDto.Input))
{
if (hasCode)
{
query.Where.Conditions.Add(DQCondition.Or(DQCondition.Like("Name", queryDto.Input), DQCondition.Like("Code", queryDto.Input)));
}
else
{
query.Where.Conditions.Add(DQCondition.Like("Name", queryDto.Input));
}
}
if (type == typeof(Department))
{
OrganizationUtil.AddOrganizationLimit(query, typeof(Department));
}
using (var session = Dmo.NewSession())
{
using (var reader = session.ExecuteReader(query))
{
while (reader.Read())
{
list.Add(new BaseInfoDto()
{
ID = (long)reader[0], Name = (string)reader[1]
});
}
}
}
return(list);
}
private List <Tuple <long, string> > GetBaseInfo <T>(Action <DQueryDom> action = null)
{
var query = new DQueryDom(new JoinAlias(typeof(T)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", DomainContext.Current.ID));
OrganizationUtil.AddOrganizationLimit(query, typeof(T));
if (action != null)
{
action(query);
}
return(query.EExecuteList <long, string>());
}
public static List <RpcEasyProductInStore> GetProductInStoreList()
{
var query = new DQueryDom(new JoinAlias(typeof(ProductInStore)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("InStoreDate"));
query.Where.Conditions.Add(DQCondition.EQ("BillState", 单据状态.未审核));
OrganizationUtil.AddOrganizationLimit(query, typeof(ProductInStore));
OrganizationUtil.AddOrganizationLimit <Store>(query, "Store_ID");
query.Where.Conditions.Add(DQCondition.IsNotNull(DQExpression.Field("InStoreDate")));
try
{
return(query.EExecuteList <long, DateTime>().Select(x => new RpcEasyProductInStore(x.Item1, x.Item2)).ToList());
}
catch (Exception)
{
return(new List <RpcEasyProductInStore>());
}
}
public static ListData Query(ListData data)
{
var queryobj = (ProductInStoreQueryObj)data.QueryObject;
var query = new DQueryDom(new JoinAlias(typeof(ProductInStore)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("BillState"));
query.Columns.Add(DQSelectColumn.Field("AccountingUnit_Name"));
query.Columns.Add(DQSelectColumn.Field("Department_Name"));
query.Columns.Add(DQSelectColumn.Field("Employee_Name"));
query.Columns.Add(DQSelectColumn.Field("Store_Name"));
query.Columns.Add(DQSelectColumn.Field("InStoreType_Name"));
query.Columns.Add(DQSelectColumn.Field("InStoreDate"));
query.Columns.Add(DQSelectColumn.Field("CheckEmployee_Name"));
query.Columns.Add(DQSelectColumn.Field("CheckDate"));
query.Columns.Add(DQSelectColumn.Field("ProductInStoreTemplate_Name"));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create("ID", true));
HippoUtil.AddEQ(query, "ID", queryobj.ID);
HippoUtil.AddEQ(query, "BillState", queryobj.BillState);
HippoUtil.AddEQ(query, "AccountingUnit_ID", queryobj.AccountingUnit_ID);
HippoUtil.AddEQ(query, "Department_ID", queryobj.Department_ID);
HippoUtil.AddEQ(query, "Employee_ID", queryobj.Employee_ID);
HippoUtil.AddEQ(query, "Store_ID", queryobj.Store_ID);
HippoUtil.AddEQ(query, "InStoreType_ID", queryobj.InStoreType_ID);
HippoUtil.AddEQ(query, "ProductPlan_ID", queryobj.ProductPlan_ID);
if (queryobj.MinInStoreDate.HasValue)
{
query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual("InStoreDate", queryobj.MinInStoreDate.Value));
}
if (queryobj.MaxInStoreDate.HasValue)
{
query.Where.Conditions.Add(DQCondition.LessThanOrEqual("InStoreDate", queryobj.MaxInStoreDate.Value));
}
query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", DomainContext.Current.ID));
query.Range = new SelectRange(data.Start, data.Count);
OrganizationUtil.AddOrganizationLimit(query, typeof(ProductInStore));
var pagedData = new DFDataAdapter(new LoadArguments(query)).PagedFill();
data.Start = 0;
data.Count = (int)pagedData.TotalCount;
data.Data = pagedData.Data;
return(data);
}
public static List <ProduceOutputObj> GetProduceOutput()
{
var bill = new JoinAlias(typeof(ProduceOutput));
var detail = new JoinAlias(typeof(ProduceOutput_Detail));
var query = new DQueryDom(bill);
query.From.AddJoin(JoinType.Left, new DQDmoSource(detail), DQCondition.EQ(bill, "ID", detail, "ProduceOutput_ID"));
query.Columns.Add(DQSelectColumn.Field("ID", bill));
query.Columns.Add(DQSelectColumn.Field("Time", bill));
query.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
query.Columns.Add(DQSelectColumn.Field("Number", detail));
query.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
query.Where.Conditions.Add(DQCondition.Or(DQCondition.EQ(bill, "BillState", 单据状态.已审核), DQCondition.EQ(bill, "BillState", 单据状态.未审核)));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create(bill, "ID", true));
OrganizationUtil.AddOrganizationLimit(query, typeof(ProduceOutput));
var list = new List <ProduceOutputObj>();
using (var session = Dmo.NewSession())
{
using (var reader = session.ExecuteReader(query))
{
while (reader.Read())
{
list.Add(new ProduceOutputObj
{
ID = (long)reader[0],
Time = (DateTime?)reader[1],
Goods_ID = (long?)reader[2],
Goods_Name = (string)reader[3],
Number = (Money <decimal>?)reader[4]
});
}
}
}
return(list);
}
public static List <GoodsInfoDto> GetByDepartPlan(long?departId, long?productionPlanId = null, string productionPlanNumber = "")
{
if (departId == null || departId == 0)
{
throw new Exception("员工档案上没有配置部门");
}
var list = new List <GoodsInfoDto>();
var bill = new JoinAlias(typeof(ProductPlan));
var detail = new JoinAlias(typeof(ProductPlan_OutputDetail));
var goods = new JoinAlias(typeof(Goods));
var query = new DQueryDom(bill);
query.From.AddJoin(JoinType.Inner, new DQDmoSource(detail), DQCondition.EQ(bill, "ID", detail, "ProductPlan_ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(goods), DQCondition.EQ(goods, "ID", detail, "Goods_ID"));
//query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual(bill, "Date", DateTime.Today));
//query.Where.Conditions.Add(DQCondition.LessThan(bill, "Date", DateTime.Today.AddDays(1)));
query.Where.Conditions.Add(DQCondition.EQ(bill, "BillState", 单据状态.已审核));
//if(productionPlanId!=null)
// query.Where.Conditions.Add(DQCondition.EQ(bill, "ID", productionPlanId));
if (productionPlanNumber != "")
{
query.Where.Conditions.Add(DQCondition.EQ(bill, "PlanNumber", productionPlanNumber));
}
// query.Where.Conditions.Add(B3ButcheryUtil.部门或上级部门条件(departId??0, bill));
OrganizationUtil.AddOrganizationLimit(query, typeof(ProductPlan));
query.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_MainUnit", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnit", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_UnitConvertDirection", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_MainUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_Code", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitII", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitII_MainUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("Goods_SecondUnitII_SecondUnitRatio", detail));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_ID", goods));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_Name", goods));
query.Columns.Add(DQSelectColumn.Field("GoodsPropertyCatalog_Name", goods));
query.Distinct = true;
query.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
using (var session = Dmo.NewSession())
{
using (var reader = session.ExecuteReader(query))
{
while (reader.Read())
{
var dto = new GoodsInfoDto();
dto.Goods_ID = (long)reader[0];
dto.Goods_Name = (string)reader[1];
dto.Goods_MainUnit = (string)reader[2];
dto.Goods_SecondUnit = (string)reader[3];
dto.Goods_UnitConvertDirection = (NamedValue <主辅转换方向>?)reader[4];
dto.Goods_MainUnitRatio = (Money <decimal>?)reader[5];
dto.Goods_SecondUnitRatio = (Money <decimal>?)reader[6];
dto.Goods_Code = (string)reader[7];
if (dto.Goods_MainUnitRatio == null)
{
dto.Goods_MainUnitRatio = 1;
}
if (dto.Goods_SecondUnitRatio == null)
{
dto.Goods_SecondUnitRatio = 1;
}
dto.SecondUnitII = (string)reader[8];
dto.SecondUnitII_MainUnitRatio = Convert.ToDecimal(reader[9]);
dto.SecondUnitII_SecondUnitRatio = Convert.ToDecimal(reader[10]);
dto.GoodsProperty_ID = (long?)reader[11];
dto.GoodsProperty_Name = (string)reader[12];
dto.GoodsPropertyCatalog_Name = (string)reader[13];
list.Add(dto);
}
}
}
return(list);
}
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);
}
