private void UpdateDoneNumber(ProduceFinish dmo)
{
var ids = dmo.Details.Select(x => x.ProductNotice_Detail_ID).ToList();
var detail = new JoinAlias(typeof(ProduceFinish_Detail));
var bill = new JoinAlias(typeof(ProduceFinish));
var dom = new DQueryDom(detail);
dom.From.AddJoin(JoinType.Inner, new DQDmoSource(bill), DQCondition.EQ(bill, "ID", detail, "ProduceFinish_ID"));
dom.EAddCheckedCondition(bill);
dom.Where.Conditions.EFieldInList(DQExpression.Field(detail, "ProductNotice_Detail_ID"), ids);
dom.Columns.Add(DQSelectColumn.Sum(detail, "Number"));
dom.Columns.Add(DQSelectColumn.Field("ProductNotice_Detail_ID", detail));
dom.GroupBy.Expressions.Add(DQExpression.Field(detail, "ProductNotice_Detail_ID"));
var tupleList = dom.EExecuteList <Money <decimal>?, long>(Session);
foreach (var id in ids)
{
var number = tupleList.Where(x => x.Item2 == id).Sum(x => ((x.Item1 ?? 0).Value));
var update = new DQUpdateDom(typeof(ProductNotice_Detail));
update.Columns.Add(new DQUpdateColumn("DoneNumber", number));
update.Where.Conditions.Add(DQCondition.EQ("ID", id));
Session.ExecuteNonQuery(update);
}
}
static List <T> GetBaseInfoList <T>() where T : BaseInfo, new()
{
var query = new DQueryDom(new JoinAlias(typeof(T)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Columns.Add(DQSelectColumn.Field("Spell"));
query.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
var list = new List <T>();
using (var context = new TransactionContext())
{
using (var reader = context.Session.ExecuteReader(query))
{
while (reader.Read())
{
var entity = new T();
entity.ID = (long)reader[0];
entity.Name = (string)reader[1];
entity.Spell = (string)reader[2];
list.Add(entity);
}
}
}
return(list);
}
private static IEnumerable <WordPair> SelectProductLinkTemplate(ChoiceBoxArgument argu)
{
var main = new JoinAlias(typeof(ProductLinkTemplate));
var query = new DQueryDom(main);
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
if (!string.IsNullOrEmpty(argu.CodeArgument))
{
var accDep = argu.CodeArgument.Split(new[] { '|' }).ToArray();
query.Where.Conditions.Add(DQCondition.EQ("AccountingUnit_ID", accDep[0]));
var id = GetDepartment(Convert.ToInt64(accDep[1]));
var field = string.Format("Department_TreeDeep{0}ID", id);
var deptCondition = B3ButcheryUtil.部门或上级部门条件(Convert.ToInt64(accDep[1]));
query.Where.Conditions.Add(DQCondition.Or(DQCondition.EQ(main, field, accDep[1]), deptCondition));
}
if (!string.IsNullOrEmpty(argu.InputArgument))
{
IList <IDQExpression> conditions = new List <IDQExpression>();
conditions.Add(DQCondition.Like("Name", argu.InputArgument));
conditions.Add(DQCondition.Like("Spell", argu.InputArgument));
query.Where.Conditions.Add(DQCondition.Or(conditions));
}
return(query.EExecuteList <long, string>().Select(x => new WordPair(x.Item2, x.Item1.ToString(CultureInfo.InvariantCulture))));
}
private DQueryDom GetDQueryDom()
{
DQueryDom dom = mQueryContainer.Build();
var main = dom.From.RootSource.Alias;
var detail = new JoinAlias(typeof(ProductInStore_Temp_Detail));
dom.From.AddJoin(JoinType.Inner, new DQDmoSource(detail), DQCondition.EQ(main, "ID", detail, "ProductInStoreTemp_ID"));
dom.Columns.Add(DQSelectColumn.Field("ID", main));
dom.Columns.Add(DQSelectColumn.Field("Name", main));
dom.Columns.Add(DQSelectColumn.Field("AccountingUnit_ID", main));
dom.Columns.Add(DQSelectColumn.Field("AccountingUnit_Name", main));
dom.Columns.Add(DQSelectColumn.Field("Department_ID", main));
dom.Columns.Add(DQSelectColumn.Field("Department_Name", main));
dom.Columns.Add(DQSelectColumn.Field("Store_ID", main));
dom.Columns.Add(DQSelectColumn.Field("Store_Name", main));
dom.Columns.Add(DQSelectColumn.Field("Employee_ID", main));
dom.Columns.Add(DQSelectColumn.Field("Employee_Name", main));
dom.Columns.Add(DQSelectColumn.Field("InStoreType_ID", main));
dom.Columns.Add(DQSelectColumn.Field("InStoreType_Name", main));
dom.Columns.Add(DQSelectColumn.Field("CheckEmployee_ID", main));
dom.Columns.Add(DQSelectColumn.Field("CheckEmployee_Name", main));
dom.Columns.Add(DQSelectColumn.Field("InStoreDate", main));
dom.Columns.Add(DQSelectColumn.Field("CheckDate", main));
dom.Distinct = true;
dom.Where.Conditions.Add(DQCondition.EQ("Stopped", false));
//dom.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
//dom.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
//dom.Columns.Add(DQSelectColumn.Field("Goods_Code", detail));
//dom.Columns.Add(DQSelectColumn.Field("Goods_Spec", detail));
//dom.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, "ID"), "DetailID"));
dom.Where.Conditions.Add(DQCondition.EQ(main, "Domain_ID", DomainContext.Current.ID));
return(dom);
}
public static List <WpfUser> GetWpfUser()
{
var query = new DQueryDom(new JoinAlias(typeof(WpfUser)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Columns.Add(DQSelectColumn.Field("Stopped"));
query.Columns.Add(DQSelectColumn.Field("Password"));
query.Columns.Add(DQSelectColumn.Field("RoleSchema"));
var list = new List <WpfUser>();
using (var context = new TransactionContext())
{
using (var reader = context.Session.ExecuteReader(query))
{
while (reader.Read())
{
var entity = new WpfUser();
entity.ID = (long)reader[0];
entity.Name = (string)reader[1];
entity.Stopped = (bool)reader[2];
entity.Password = (byte[])reader[3];
entity.RoleSchema = (string)reader[4];
list.Add(entity);
}
}
}
return(list);
}
public static List <Store> SyncStores()
{
var list = new List <Store>();
var query = new DQueryDom(new JoinAlias(typeof(Store)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
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));
using (var session = Dmo.NewSession())
{
using (var reader = session.ExecuteReader(query))
{
while (reader.Read())
{
list.Add(new Store()
{
ID = (long)reader[0], Name = (string)reader[1], Code = (string)reader[2]
});
}
}
}
return(list);
}
public static List <ProductInStoreSimpleDto> GetSimpleList(int pageIndex, int pageSize)
{
var list = new List <ProductInStoreSimpleDto>();
var query = new DQueryDom(new JoinAlias(typeof(ProductInStore)));
query.Where.Conditions.Add(DQCondition.EQ("BillState", 单据状态.未审核));
query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", DomainContext.Current.ID));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create("ID", true));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("InStoreDate"));
query.Columns.Add(DQSelectColumn.Field("Store_Name"));
query.Range = new SelectRange(pageSize * pageIndex, pageSize);
using (var session = Dmo.NewSession())
{
using (var reader = session.ExecuteReader(query))
{
while (reader.Read())
{
var dto = new ProductInStoreSimpleDto();
dto.ID = (long)reader[0];
dto.InStoreDate = (DateTime)reader[1];
dto.Store_Name = (string)reader[2];
list.Add(dto);
}
}
}
return(list);
}
public DQueryDom CreatePartialQuery(AvailableStorageContext context)
{
var detail = new JoinAlias(typeof(ProductInStore_Detail));
var bill = new JoinAlias(typeof(ProductInStore));
var query = new DQueryDom(detail);
query.From.AddJoin(JoinType.Inner, new DQDmoSource(bill), DQCondition.EQ(bill, "ID", detail, "ProductInStore_ID"));
query.Where.Conditions.Add(DQCondition.EQ(bill, "BillState", 单据状态.未审核));
if (context.Goods_ID.HasValue)
{
query.Where.Conditions.Add(DQCondition.EQ("Goods_ID", context.Goods_ID));
}
if (context.Store_ID.HasValue)
{
query.Where.Conditions.Add(DQCondition.EQ(bill, "Store_ID", context.Store_ID));
}
if (context.AccountingUnit_ID.HasValue)
{
query.Where.Conditions.Add(DQCondition.EQ(bill, "AccountingUnit_ID", context.AccountingUnit_ID));
}
if (context.Date.HasValue)
{
query.Where.Conditions.Add(DQCondition.LessThan(bill, "Time", context.Date.Value.Date.AddDays(1)));
}
query.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
query.Columns.Add(DQSelectColumn.Create(DQExpression.ConstValue("成品入库单"), "Source"));
query.Columns.Add(DQSelectColumn.Field("Store_ID", bill));
query.Columns.Add(DQSelectColumn.Field("Goods_ID"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Multiply(DQExpression.Field("Number"), DQExpression.ConstValue(-1)), "Number"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Multiply(DQExpression.Field("SecondNumber"), DQExpression.ConstValue(-1)), "SecondNumber"));
query.Columns.Add(DQSelectColumn.Field("GoodsBatch_ID"));
return(query);
}
public static void InsertProductInStore(ProductInStore dmo)
{
var query = new DQueryDom(new JoinAlias(typeof(ProductInStore)));
query.Columns.Add(DQSelectColumn.Field("DeviceId"));
query.Where.Conditions.Add(DQCondition.EQ("InStoreDate", dmo.InStoreDate));
var deviceIds = query.EExecuteList <string>();
if (deviceIds.Contains(dmo.DeviceId))
{
return;
}
using (var context = new TransactionContext())
{
foreach (var d in dmo.Details)
{
d.Price = 0;
d.Money = 0;
d.ProductionDate = GetProductPlanDate(d.ProductPlan_ID);
}
var bl = BIFactory.Create <IProductInStoreBL>(context);
//dmo.BillState = 单据状态.已审核;
dmo.IsHandsetSend = true;
bl.Insert(dmo);
if (new B3ButcheryConfig().DoCheckCreatedInStore.Value)
{
bl.Check(dmo);
}
context.Commit();
}
}
public static long?DeleteByFrozenInStore(long?frozenInStoreId)
{
if (!BLContext.User.IsInRole("B3Butchery.成品入库.删除"))
{
return(-3);
}
long?id = null;
using (var context = new TransactionContext())
{
var frozenInStoreBl = BIFactory.Create <IFrozenInStoreBL>(context.Session);
var frozenInStoreDmo = frozenInStoreBl.Load(Convert.ToInt64(frozenInStoreId));
var query = new DQueryDom(new JoinAlias(typeof(ProductInStore)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Where.Conditions.Add(DQCondition.EQ("BillState", 单据状态.未审核));
query.Where.Conditions.Add(DQCondition.EQ("Client", frozenInStoreDmo.Client));
var result = query.EExecuteList <long?>();
foreach (var l in result)
{
var bl = BIFactory.Create <IProductInStoreBL>(context);
var obj = bl.Load(Convert.ToInt64(l));
id = (int)obj.ID;
bl.Delete(obj);
}
context.Commit();
}
return(id);
}
protected override DQueryDom GetQueryDom()
{
var dom = base.GetQueryDom();
var detail = JoinAlias.Create("detail");
var alias = dom.From.RootSource.Alias;
dom.Columns.Add(DQSelectColumn.Field("ID", alias));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, "ID"), "DetailID"));
dom.Columns.Add(DQSelectColumn.Field("AccountingUnit_Name"));
dom.Columns.Add(DQSelectColumn.Field("Customer_Name"));
dom.Columns.Add(DQSelectColumn.Field("Detail_Customer_Name", detail));
dom.Columns.Add(DQSelectColumn.Field("ProductionUnit_ID"));
dom.Columns.Add(DQSelectColumn.Field("ProductionUnit_Name"));
dom.Columns.Add(DQSelectColumn.Field("Date", "单据日期"));
dom.Columns.Add(DQSelectColumn.Field("ProduceDate", detail));
dom.Columns.Add(DQSelectColumn.Field("DeliveryDate", detail));
dom.Columns.Add(DQSelectColumn.Field("ProduceRequest", detail, "工艺描述"));
dom.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
dom.Columns.Add(DQSelectColumn.Field("Goods_MainUnit", detail));
dom.Columns.Add(DQSelectColumn.Field("Goods_SecondUnit", detail));
dom.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
dom.Columns.Add(DQSelectColumn.Field("Number", detail));
dom.Columns.Add(DQSelectColumn.Field("SecondNumber", detail));
dom.Columns.Add(DQSelectColumn.Field("DoneNumber", detail));
dom.Columns.Add(DQSelectColumn.Field("BrandItem_Name", detail));
dom.Columns.Add(DQSelectColumn.Field("BrandItem_ID", detail));
dom.EAddCheckedCondition(alias);
dom.Where.Conditions.Add(DQCondition.EQ(alias, "Domain_ID", DomainContext.Current.ID));
if (_hideFinishedBill.Checked)
{
dom.Where.Conditions.Add(DQCondition.GreaterThan(DQExpression.Field(detail, "Number"), DQExpression.IfNull(DQExpression.Field(detail, "DoneNumber"), DQExpression.Value(0))));
}
return(dom);
}
public static List <Store> GetStore()
{
var query = new DQueryDom(new JoinAlias(typeof(Store)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Columns.Add(DQSelectColumn.Field("Code"));
query.Columns.Add(DQSelectColumn.Field("Stopped"));
query.Columns.Add(DQSelectColumn.Field("Domain_ID"));
query.Columns.Add(DQSelectColumn.Field("Spell"));
var list = new List <Store>();
using (var context = new TransactionContext())
{
using (var reader = context.Session.ExecuteReader(query))
{
while (reader.Read())
{
var entity = new Store();
entity.ID = (long)reader[0];
entity.Name = (string)reader[1];
entity.Code = (string)reader[2];
entity.Stopped = (bool)reader[3];
entity.Domain_ID = (long)reader[4];
entity.Spell = (string)reader[5];
list.Add(entity);
}
}
}
return(list);
}
public void CheckDetailGoods(ProductNotice dmo)
{
if (dmo.Customer_ID == null)
{
return;
}
var ids =
dmo.Details.Where(x => x.DmoID.HasValue && (x.DmoTypeID == null || x.DmoTypeID == SaleForecastTypeID)).Select
(x => x.DmoID);
var query = SaleForecastQuery(dmo.Customer_ID, dmo.Date, ids);
query.Columns.Clear();
query.Columns.Add(DQSelectColumn.Field("SaleGoods_ID"));
var result = query.EExecuteList <long>();
foreach (var detail in dmo.Details)
{
if (detail.DmoID == null)
{
continue;
}
if (detail.DmoTypeID.HasValue && detail.DmoTypeID != SaleForecastTypeID)
{
continue;
}
if (!result.Contains(detail.Goods_ID))
{
throw new Exception(detail.Goods_Name + "和销售预报中不相符");
}
}
}
static DQueryDom SaleForecastQuery(long?customer, DateTime?dates, IEnumerable <long?> ids = null)
{
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(detail);
dom.From.AddJoin(JoinType.Inner, new DQDmoSource(bill), 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(GetNumber(detail, "UnitNum"), "Number"));
dom.Columns.Add(DQSelectColumn.Field("Unit", detail));
dom.Columns.Add(DQSelectColumn.Field("Price", detail));
dom.Columns.Add(DQSelectColumn.Field("Money", detail));
dom.Columns.Add(DQSelectColumn.Field("SecondNumber", detail));
dom.Columns.Add(DQSelectColumn.Field("Goods_SecondUnit", detail));
dom.Columns.Add(DQSelectColumn.Field("Remark", detail));
dom.Columns.Add(DQSelectColumn.Field("Goods_Code", detail));
dom.Columns.Add(DQSelectColumn.Field("Goods_Name", detail));
dom.Columns.Add(DQSelectColumn.Field("Goods_Spec", detail));
dom.Where.Conditions.Add(DQCondition.EQ(bill, "Customer_ID", customer));
dom.Where.Conditions.Add(DQCondition.EQ(bill, "BillState", 单据状态.已审核));
dom.Where.Conditions.Add(DQCondition.EQ(bill, "Domain_ID", DomainContext.Current.ID));
if (ids != null)
{
dom.Where.Conditions.EFieldInList(DQExpression.Field(bill, "ID"), ids.ToArray());
}
var date = dates ?? BLContext.Today;
dom.Where.Conditions.Add(DQCondition.GreaterThanOrEqual(bill, "Date", date.Date.AddDays(-7)));//只显示7天内的预报
return(dom);
}
public static DepartmentDto GetDepartmentBaseInfoDto()
{
var dto = new DepartmentDto();
using (var session = Dmo.NewSession())
{
var employeeId = GetCurrentBindingEmployeeID(session);
var query = new DQueryDom(new JoinAlias(typeof(Employee)));
query.Where.Conditions.Add(DQCondition.EQ("ID", employeeId));
query.Columns.Add(DQSelectColumn.Field("Department_ID"));
query.Columns.Add(DQSelectColumn.Field("Department_Name"));
query.Columns.Add(DQSelectColumn.Field("Department_Depth"));
using (var reader = session.ExecuteReader(query))
{
if (reader.Read())
{
dto.ID = (long?)reader[0] ?? 0;
if (dto.ID > 0)
{
dto.Name = (string)reader[1];
dto.Department_Depth = (int?)reader[2] ?? 0;
}
}
}
}
return(dto);
}
private DQueryDom GetQueryDom()
{
var query = _mQueryContainer.Build();
var root = query.From.RootSource.Alias;
var pptAlias = JoinAlias.Create("gppt");
var propertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog));
var alreadySet = new JoinAlias(typeof(Goods_Accounting_Store));
query.From.AddJoin(JoinType.Left, new DQDmoSource(propertyCatalog), DQCondition.EQ(pptAlias, "GoodsPropertyCatalog_ID", propertyCatalog, "ID"));
query.From.AddJoin(JoinType.Left, new DQDmoSource(alreadySet), DQCondition.EQ(root, "ID", alreadySet, "Goods_ID"));
query.Columns.Add(DQSelectColumn.Field("ID", "存货ID"));
query.Columns.Add(DQSelectColumn.Field("Code"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Columns.Add(DQSelectColumn.Field("Spec"));
query.Columns.Add(DQSelectColumn.Field("MainUnit"));
query.Columns.Add(DQSelectColumn.Field("SecondUnit"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(pptAlias, "Name"), "存货属性"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(propertyCatalog, "Name"), "属性分类"));
query.Columns.Add(DQSelectColumn.Field("AccountingUnit_Name", alreadySet, "已配会计单位"));
if (!_goodsPropertyCatalogBox.IsEmpty)
{
TreeUtil.AddTreeCondition <GoodsPropertyCatalog>(query, long.Parse(_goodsPropertyCatalogBox.Value), propertyCatalog);
}
if (_checkBox.Checked)
{
query.Where.Conditions.Add(DQCondition.IsNull(DQExpression.Field(alreadySet, "Goods_ID")));
}
return(query);
}
public static List <ProductPlan> GetProductPlan()
{
var list = new List <ProductPlan>();
var bill = new JoinAlias(typeof(ProductPlan));
var query = new DQueryDom(bill);
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("PlanNumber"));
query.Where.Conditions.Add(DQCondition.Or(DQCondition.And(DQCondition.GreaterThanOrEqual("EndDate", DateTime.Today), DQCondition.LessThanOrEqual("Date", DateTime.Today)), DQCondition.And(DQCondition.GreaterThanOrEqual("EndDate", DateTime.Today.AddDays(-1)), DQCondition.LessThanOrEqual("Date", DateTime.Today.AddDays(-1))), DQCondition.And(DQCondition.GreaterThanOrEqual("EndDate", DateTime.Today.AddDays(-2)), DQCondition.LessThanOrEqual("Date", DateTime.Today.AddDays(-2))), DQCondition.And(DQCondition.GreaterThanOrEqual("EndDate", DateTime.Today.AddDays(-3)), DQCondition.LessThanOrEqual("Date", DateTime.Today.AddDays(-3)))));
query.Where.Conditions.Add(DQCondition.EQ("BillState", 单据状态.已审核));
using (var context = new TransactionContext())
{
using (var reader = context.Session.ExecuteReader(query))
{
while (reader.Read())
{
var productPlan = new ProductPlan
{
ID = (long)reader[0],
PlanNumber = (string)reader[1]
};
list.Add(productPlan);
}
}
}
return(list);
}
public static List <ProductPlanInfo> GetProductPlanInfo(long[] id)
{
if (id.Length == 0)
{
return(new List <ProductPlanInfo>());
}
var query = new DQueryDom(new JoinAlias(typeof(ProductPlan)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("PlanNumber"));
query.Columns.Add(DQSelectColumn.Field("Date"));
query.Columns.Add(DQSelectColumn.Field("RowVersion"));
query.Where.Conditions.Add(DQCondition.InList(DQExpression.Field("ID"), id.Select(x => DQExpression.Value(x)).ToArray()));
query.OrderBy.Expressions.Add(DQOrderByExpression.Create("ID"));
var result = query.EExecuteList <long, string, DateTime, int>();
var rst = new List <ProductPlanInfo>();
foreach (var item in result)
{
rst.Add(new ProductPlanInfo {
ID = item.Item1, PlanNumber = item.Item2, PlanDate = item.Item3, RowVersion = item.Item4
});
}
return(rst);
}
public static string GetPlanNoBaseInfo()
{
var query = new DQueryDom(new JoinAlias(typeof(ProductPlan)));
query.Columns.Add(DQSelectColumn.Field("PlanNumber"));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Where.Conditions.Add(DQCondition.LessThanOrEqual("Date", DateTime.Today));
query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual("EndDate", DateTime.Today));
query.Where.Conditions.Add(DQCondition.GreaterThanOrEqual("BillState", 20));
using (var context = new TransactionContext())
{
var list = new List <PlanNoBaseInfo>();
;
using (var reader = context.Session.ExecuteReader(query))
{
while (reader.Read())
{
var plan = new PlanNoBaseInfo();
plan.ID = (long)reader[1];
plan.Name = (string)reader[0];
list.Add(plan);
}
return(JsonConvert.SerializeObject(list));
}
}
}
private DQueryDom GetQueryDom()
{
var query = _mQueryContainer.Build();
var goods = JoinAlias.Create("goods");
var pptAlias = JoinAlias.Create("gppt");
var propertyCatalog = new JoinAlias(typeof(GoodsPropertyCatalog));
query.From.AddJoin(JoinType.Left, new DQDmoSource(propertyCatalog), DQCondition.EQ(pptAlias, "GoodsPropertyCatalog_ID", propertyCatalog, "ID"));
query.Columns.Add(DQSelectColumn.Field("ID", "序号"));
query.Columns.Add(DQSelectColumn.Field("Code", goods, "存货编码"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(goods, "Name"), "存货名称"));
query.Columns.Add(DQSelectColumn.Field("Spec", goods));
query.Columns.Add(DQSelectColumn.Field("MainUnit", goods));
query.Columns.Add(DQSelectColumn.Field("SecondUnit", goods));
query.Columns.Add(DQSelectColumn.Field("Name", pptAlias, "存货属性"));
query.Columns.Add(DQSelectColumn.Create(DQExpression.Field(propertyCatalog, "Name"), "属性分类"));
query.Columns.Add(DQSelectColumn.Field("Store_Name"));
query.Columns.Add(DQSelectColumn.Field("AccountingUnit_Name"));
query.Where.Conditions.Add(DQCondition.EQ("AccountingUnit_Domain_ID", DomainContext.Current.ID));
if (!_goodsPropertyCatalogBox.IsEmpty)
{
TreeUtil.AddTreeCondition <GoodsPropertyCatalog>(query, long.Parse(_goodsPropertyCatalogBox.Value), propertyCatalog);
}
return(query);
}
public ISqlDom CreateView()
{
var main = new JoinAlias(typeof(DailyProductReport));
var inputdetail = new JoinAlias(typeof(DailyProductReport_InputDetail));
var dom = new DQueryDom(main);
dom.From.AddJoin(JoinType.Inner, new DQDmoSource(inputdetail), DQCondition.EQ(main, "ID", inputdetail, "DailyProductReport_ID"));
dom.Columns.Add(DQSelectColumn.Field("Domain_ID"));
dom.Columns.Add(DQSelectColumn.Field("AccountingUnit_ID"));
dom.Columns.Add(DQSelectColumn.Field("Department_ID"));
dom.Columns.Add(DQSelectColumn.Field("Date"));
dom.Columns.Add(DQSelectColumn.Field("PlanNumber_ID"));
dom.Columns.Add(DQSelectColumn.Field("Goods_ID", inputdetail));
dom.Columns.Add(DQSelectColumn.Field("Number", inputdetail));
dom.Columns.Add(DQSelectColumn.Field("Weight", inputdetail));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Value <decimal?>(null), "OutputPrecent"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Value <decimal>(0), "Price"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Value <bool>(false), "DetailType"));
dom.Where.Conditions.Add(DQCondition.EQ("BillState", 单据状态.已审核));
SelectDom selectDom = (SelectDom)dom.Build(DbProviderType.SqlClient, DbmsType.SqlServer2005);
UnionDom unionDom = new UnionDom();
UnionItem inputDetailUnion = new UnionItem(selectDom, UnionType.Default);
UnionItem outputDetailUnion = new UnionItem(GetOutputDetailDom(), UnionType.All);
unionDom.Items.Add(inputDetailUnion);
unionDom.Items.Add(outputDetailUnion);
return(unionDom);
}
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 GoodsBatch Get(long id, string[] fields)
{
if (fields.Length == 0)
{
throw new Exception("未指定fields");
}
var query = new DQueryDom(new JoinAlias(typeof(GoodsBatch)));
foreach (var field in fields)
{
query.Columns.Add(DQSelectColumn.Field(field));
}
query.Where.Conditions.Add(DQCondition.EQ("ID", id));
var dmoType = typeof(GoodsBatch);
var i = 0;
using (var context = new TransactionContext())
{
using (var reader = context.Session.ExecuteReader(query))
{
if (reader.Read())
{
var student = new GoodsBatch {
ID = id
};
foreach (var field in fields)
{
dmoType.GetProperty(field).SetValue(student, reader[i++], null);
}
return(student);
}
return(null);
}
}
}
private static DQueryDom GetAllSettingedGoodsSubQuery()
{
var query = new DQueryDom(new JoinAlias(typeof(HandsetSelectedGoods)));
query.Columns.Add(DQSelectColumn.Field("Goods_ID"));
return(query);
}
private static DQueryDom GetDom(DateTime?date)
{
var main = new JoinAlias("__frezenMain", typeof(FrozenOutStore));
var detail = new JoinAlias(typeof(FrozenOutStore_Detail));
var dom = new DQueryDom(main);
dom.From.AddJoin(JoinType.Left, new DQDmoSource(detail), DQCondition.EQ(detail, "FrozenOutStore_ID", main, "ID"));
var exp = DQExpression.Snippet(" (CONVERT(varchar(10), [__frezenMain].[Date], 23))");
dom.Columns.Add(DQSelectColumn.Create(exp, "Date"));
dom.Columns.Add(DQSelectColumn.Field("Goods_ID", detail));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, "Goods2_ID"), "成品ID"));
dom.Columns.Add(DQSelectColumn.Create(DQExpression.Field(detail, "Goods2_Name"), "成品Name"));
dom.Columns.Add(DQSelectColumn.Field("Number", detail));
dom.Columns.Add(DQSelectColumn.Field("SecondNumber2", detail));
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.Where.Conditions.Add(DQCondition.InEQ(main, "BillState", 1));
return(dom);
}
private static int?GetDepartment(long?id)
{
var query = new DQueryDom(new JoinAlias(typeof(Department)));
query.Columns.Add(DQSelectColumn.Field("Depth"));
query.Where.Conditions.Add(DQCondition.EQ("ID", id));
return(query.EExecuteScalar <int?>());
}
private static DQueryDom GetSubQuery(long dtoDepartmentId)
{
var query = new DQueryDom(new JoinAlias(typeof(PackingBagType)));
query.Where.Conditions.Add(DQCondition.EQ("Department_ID", dtoDepartmentId));
query.Columns.Add(DQSelectColumn.Field("ID"));
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);
}
private long?GetBanChengPinByGoodsID(long goodID)
{
var main = new JoinAlias(typeof(ChengPinToBanChengPinConfig));
var query = new DQueryDom(main);
query.Columns.Add(DQSelectColumn.Field("Goods2_ID", main));
query.Where.Conditions.Add(DQCondition.EQ(main, "Goods_ID", goodID));
return(query.EExecuteScalar <long?>(Session));
}
public static DFDataTable SelectInStoreType(long domainID)
{
var query = new DQueryDom(new JoinAlias(typeof(InStoreType)));
query.Columns.Add(DQSelectColumn.Field("ID"));
query.Columns.Add(DQSelectColumn.Field("Name"));
query.Where.Conditions.Add(DQCondition.EQ("Domain_ID", domainID));
return(new DFDataAdapter(new LoadArguments(query)).Fill());
}