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);
}
private AddOrderRecordCommand GenerateOrder(OrderInput input, GoodsInfoDto goods, double discount)
{
var orderNo = OrderHelper.GenerateOrderNo(OrderType.Order, input.SellType);
var orderOriginCost = input.Count * input.UnitPrice;
var orderCost = orderOriginCost * discount;
return(new AddOrderRecordCommand(Guid.NewGuid().ToString(), orderNo, goods.Id, goods.AuthRankId, _lotterySession.SystemTypeId, OrderSourceType.V1,
input.Count, input.UnitPrice, orderOriginCost, orderCost, input.SellType, _lotterySession.UserId));
}
static List <GoodsInfoDto> GetListByDquery(DQueryDom query)
{
var list = new List <GoodsInfoDto>();
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];
// dto.GoodsPropertyCatalog_Sort = (int?)reader[14];
dto.Goods_Spell = (string)reader[14];
dto.Goods_Spec = (string)reader[15];
list.Add(dto);
}
}
}
return(list);
}
public static GoodsInfoDto Get(long?id)
{
if (id == null)
{
return(null);
}
var goodsInfo = new GoodsInfoDto();
var goods = WebBLUtil.GetSingleDmo <Goods>("ID", id, "SecondUnit", "UnitConvertDirection", "MainUnit", "MainUnitRatio", "SecondUnitRatio");
goodsInfo.Goods_SecondUnit = goods.SecondUnit;
goodsInfo.Goods_MainUnit = goods.MainUnit;
goodsInfo.Goods_UnitConvertDirection = goods.UnitConvertDirection;
goodsInfo.Goods_MainUnitRatio = goods.MainUnitRatio;
goodsInfo.Goods_SecondUnitRatio = goods.SecondUnitRatio;
return(goodsInfo);
}
public static List <GoodsInfoDto> GetProductInStoreTemplate(string input)
{
var list = new List <GoodsInfoDto>();
var productInStore = new JoinAlias(typeof(ProductInStoreTemplate));
var productInStoreGoods = new JoinAlias(typeof(ProductInStoreTemplate_GoodsDetail));
var goods = new JoinAlias(typeof(Goods));
var query = new DQueryDom(productInStoreGoods);
query.From.AddJoin(JoinType.Left, new DQDmoSource(productInStore), DQCondition.EQ(productInStore, "ID", productInStoreGoods, "ProductInStoreTemplate_ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(goods), DQCondition.EQ(productInStoreGoods, "Goods_ID", goods, "ID"));
query.Range = SelectRange.Top(200);
query.Where.Conditions.Add(DQCondition.EQ(productInStore, "Stopped", false));
if (!string.IsNullOrWhiteSpace(input))
{
query.Where.Conditions.Add(DQCondition.Or(DQCondition.Like(goods, "Name", input), DQCondition.Like(goods, "Code", input), DQCondition.Like(goods, "Spell", input)));
}
query.OrderBy.Expressions.Add(DQOrderByExpression.Create(goods, "Name"));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create(goods, "Code"));
query.Columns.Add(DQSelectColumn.Field("ID", goods));
query.Columns.Add(DQSelectColumn.Field("Name", goods));
query.Columns.Add(DQSelectColumn.Field("MainUnit", goods));
query.Columns.Add(DQSelectColumn.Field("SecondUnit", goods));
query.Columns.Add(DQSelectColumn.Field("UnitConvertDirection", goods));
query.Columns.Add(DQSelectColumn.Field("MainUnitRatio", goods));
query.Columns.Add(DQSelectColumn.Field("SecondUnitRatio", goods));
query.Columns.Add(DQSelectColumn.Field("Code", goods));
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("SecondUnitII", goods));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII_MainUnitRatio", goods));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII_SecondUnitRatio", goods));
query.Columns.Add(DQSelectColumn.Field("Spec", goods));
query.Distinct = true;
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.GoodsProperty_ID = (long?)reader[8];
dto.GoodsProperty_Name = (string)reader[9];
dto.GoodsPropertyCatalog_Name = (string)reader[10];
dto.SecondUnitII = (string)reader[11];
dto.SecondUnitII_MainUnitRatio = Convert.ToDecimal(reader[12]);
dto.SecondUnitII_SecondUnitRatio = Convert.ToDecimal(reader[13]);
dto.Goods_Spec = (string)(reader[14]);
list.Add(dto);
}
}
}
return(list);
}
public static List <GoodsInfoDto> GetAllSettingedGoods(int pageIndex, int pageSize)
{
var list = new List <GoodsInfoDto>();
var joinGoods = new JoinAlias(typeof(Goods));
// var goodsProperty = new JoinAlias(typeof(GoodsProperty));
// var goodsPropertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog));
var query = new DQueryDom(joinGoods);
query.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create("Name"));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create("Code"));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Columns.Add(DQSelectColumn.Field("MainUnit"));
query.Columns.Add(DQSelectColumn.Field("SecondUnit"));
query.Columns.Add(DQSelectColumn.Field("UnitConvertDirection"));
query.Columns.Add(DQSelectColumn.Field("MainUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("Code"));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_ID"));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_Name"));
query.Columns.Add(DQSelectColumn.Field("GoodsPropertyCatalog_Name"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII_MainUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII_SecondUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("Spec"));
query.Where.Conditions.Add(DQCondition.InSubQuery(DQExpression.Field("ID"), GetAllSettingedGoodsSubQuery()));
query.Range = new SelectRange(pageIndex * pageSize, pageSize);
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.GoodsProperty_ID = (long?)reader[8];
dto.GoodsProperty_Name = (string)reader[9];
dto.GoodsPropertyCatalog_Name = (string)reader[10];
dto.SecondUnitII = (string)reader[11];
dto.SecondUnitII_MainUnitRatio = Convert.ToDecimal(reader[12]);
dto.SecondUnitII_SecondUnitRatio = Convert.ToDecimal(reader[13]);
dto.Goods_Spec = (string)(reader[14]);
list.Add(dto);
}
}
}
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);
}
public static List <GoodsInfoDto> GetAllGoods(string input)
{
var list = new List <GoodsInfoDto>();
var joinGoods = new JoinAlias(typeof(Goods));
// var goodsProperty = new JoinAlias(typeof(GoodsProperty));
// var goodsPropertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog));
var query = new DQueryDom(joinGoods);
//query.Range = SelectRange.Top(100);
query.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
if (!string.IsNullOrWhiteSpace(input))
{
query.Where.Conditions.Add(DQCondition.Or(DQCondition.Like("Name", input), DQCondition.Like("Code", input), DQCondition.Like("Spell", input)));
}
query.OrderBy.Expressions.Add(DQOrderByExpression.Create("Name"));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create("Code"));
//query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", DomainContext.Current.ID));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Columns.Add(DQSelectColumn.Field("MainUnit"));
query.Columns.Add(DQSelectColumn.Field("SecondUnit"));
query.Columns.Add(DQSelectColumn.Field("UnitConvertDirection"));
query.Columns.Add(DQSelectColumn.Field("MainUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("Code"));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_ID"));
query.Columns.Add(DQSelectColumn.Field("GoodsProperty_Name"));
query.Columns.Add(DQSelectColumn.Field("GoodsPropertyCatalog_Name"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII_MainUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("SecondUnitII_SecondUnitRatio"));
query.Columns.Add(DQSelectColumn.Field("Spec"));
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.GoodsProperty_ID = (long?)reader[8];
dto.GoodsProperty_Name = (string)reader[9];
dto.GoodsPropertyCatalog_Name = (string)reader[10];
dto.SecondUnitII = (string)reader[11];
dto.SecondUnitII_MainUnitRatio = Convert.ToDecimal(reader[12]);
dto.SecondUnitII_SecondUnitRatio = Convert.ToDecimal(reader[13]);
dto.Goods_Spec = (string)(reader[14]);
list.Add(dto);
}
}
}
return(list);
}
