/// <summary> /// /// </summary> /// <param name="session"></param> /// <param name="PERM_TYPE"></param> /// <param name="privilegerId"></param> /// <param name="operationId"></param> /// <returns></returns> public static Permission Retrieve(ISession session, PermissionType type, int privilegerId, int operationId) { EntityQuery qry = session.CreateEntityQuery <Permission>(); qry.And(Exp.Eq("Type", type)); qry.And(Exp.Eq("OperationId", operationId)); if (type == PermissionType.OnUser) { qry.And(Exp.Eq("UserId", privilegerId)); } else if (type == PermissionType.OnUserGroup) { qry.And(Exp.Eq("GroupId", privilegerId)); } else { return(null); } IList <Permission> list = qry.List <Permission>(); if (list != null && list.Count > 0) { return(list[0]); } return(null); }
/// <summary> /// Get query conditions from page , and query data from db, then bind the query result to Repeater /// </summary> private void QueryAndBindData(int pageIndex, int pageSize, bool fetchRecordCount) { string opObjectType = ddlOpObjectType.SelectedValue; string opType = ddlOpType.SelectedValue; DateTime opTime; string operatorName = txtOperatorName.Text.Trim(); string operatorType = ddlOperatorType.SelectedValue; IList <OpLog> dataOpLog = null; int recordCount = 0; using (Session session = new Session()) { EntityQuery query = session.CreateEntityQuery <OpLog>(); if (!String.IsNullOrEmpty(opObjectType)) { query.And(Exp.Like("OpObjectType", "%" + opObjectType + "%")); } if (!String.IsNullOrEmpty(opType)) { query.And(Exp.Like("OpType", "%" + opType + "%")); } if (DateTime.TryParse(txtOpTime.Text.Trim(), out opTime)) { query.And(Exp.Ge("OpTime", opTime)); } if (!String.IsNullOrEmpty(operatorName)) { query.And(Exp.Like("OperatorName", "%" + operatorName + "%")); } if (!String.IsNullOrEmpty(operatorType)) { query.And(Exp.Eq("OperatorType", operatorType)); } query.SetPage(pageIndex, pageSize); dataOpLog = query.List <OpLog>(); if (fetchRecordCount) { recordCount = query.Count(); } } this.rptOpLog.DataSource = dataOpLog; this.rptOpLog.DataBind(); if (fetchRecordCount) { this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = recordCount; } WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex); WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex); //After a query, save current query this.hidReturnUrl.Value = GetReturnUrl(); }
/// <summary> /// Get query conditions from page , and query data from db, then bind the query result to Repeater /// </summary> private void QueryAndBindData(int pageIndex, int pageSize, bool fetchRecordCount) { string title = txtTitle.Text.Trim(); IList <string> layout = WebUtil.GetSelectedValues(cklLayout); string type = rdlType.SelectedValue; IList <string> status = WebUtil.GetSelectedValues(cklStatus); DateTime createTime; IList <DashPage> dataDashPage = null; int recordCount = 0; using (Session session = new Session()) { EntityQuery query = session.CreateEntityQuery <DashPage>(); if (!String.IsNullOrEmpty(title)) { query.And(Exp.Like("Title", "%" + title + "%")); } if (layout != null && layout.Count > 0) { query.And(Exp.In("Layout", layout)); } if (!String.IsNullOrEmpty(type)) { query.And(Exp.Eq("Type", type)); } if (status != null && status.Count > 0) { query.And(Exp.In("Status", status)); } if (DateTime.TryParse(txtCreateTime.Text.Trim(), out createTime)) { query.And(Exp.Ge("CreateTime", createTime)); } query.SetPage(pageIndex, pageSize); dataDashPage = query.List <DashPage>(); if (fetchRecordCount) { recordCount = query.Count(); } } this.rptDashPage.DataSource = dataDashPage; this.rptDashPage.DataBind(); if (fetchRecordCount) { this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = recordCount; } WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex); WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex); //After a query, save current query this.hidReturnUrl.Value = GetReturnUrl(); }
/// <summary> /// Get query conditions from page , and query data from db, then bind the query result to Repeater /// </summary> private void QueryAndBindData(int pageIndex, int pageSize, bool fetchRecordCount) { string itemCode = txtItemCode.Text.Trim(); string groupCode = txtGroupCode.Text.Trim(); string name = txtName.Text.Trim(); string itemType = rdlItemType.SelectedValue; IList <DictionaryItem> dataDictionaryItem = null; int recordCount = 0; // using (_session = new Session()) { EntityQuery query = _session.CreateEntityQuery <DictionaryItem>(); if (!String.IsNullOrEmpty(itemCode)) { query.And(Exp.Like("ItemCode", "%" + itemCode + "%")); } if (!String.IsNullOrEmpty(groupCode)) { query.And(Exp.Eq("GroupCode", groupCode)); } else { query.And(Exp.Eq("GroupCode", DictionaryItem.Root.ItemCode)); } if (!String.IsNullOrEmpty(name)) { query.And(Exp.Like("Name", "%" + name + "%")); } if (!String.IsNullOrEmpty(itemType)) { query.And(Exp.Eq("ItemType", itemType)); } query.SetPage(pageIndex, pageSize); dataDictionaryItem = query.List <DictionaryItem>(); if (fetchRecordCount) { recordCount = query.Count(); } } this.rptDictionaryItem.DataSource = dataDictionaryItem; this.rptDictionaryItem.DataBind(); if (fetchRecordCount) { this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = recordCount; } WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex); WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex); //After a query, save current query this.hidReturnUrl.Value = GetReturnUrl(); }
/// <summary> /// Get query conditions from page , and query data from db, then bind the query result to Repeater /// </summary> private void QueryAndBindData(int pageIndex, int pageSize, bool fetchRecordCount) { string name = txtName.Text.Trim(); string description = txtDescription.Text.Trim(); IList <string> groupType = WebUtil.GetSelectedValues(cklGroupType); string modifyTime = txtModifyTime.Text.Trim(); IList <UserGroup> dataUserGroup = null; int recordCount = 0; using (Session session = new Session()) { EntityQuery query = session.CreateEntityQuery <UserGroup>(); if (!String.IsNullOrEmpty(name)) { query.And(Exp.Like("Name", "%" + name + "%")); } if (!String.IsNullOrEmpty(description)) { query.And(Exp.Like("Description", "%" + description + "%")); } if (groupType != null && groupType.Count > 0) { query.And(Exp.In("GroupType", groupType)); } if (!String.IsNullOrEmpty(modifyTime)) { query.And(Exp.Eq("ModifyTime", modifyTime)); } query.SetPage(pageIndex, pageSize); dataUserGroup = query.List <UserGroup>(); if (fetchRecordCount) { recordCount = query.Count(); } } this.rptUserGroup.DataSource = dataUserGroup; this.rptUserGroup.DataBind(); if (fetchRecordCount) { this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = recordCount; } WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex); WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex); //After a query, save current query this.hidReturnUrl.Value = GetReturnUrl(); }
/// <summary> /// Get query conditions from page , and query data from db, then bind the query result to Repeater /// </summary> private void QueryAndBindData(int pageIndex, int pageSize, bool fetchRecordCount) { string msgTypeId = ddlMsgTypeId.SelectedValue; string title = txtTitle.Text.Trim(); IList <string> accessibility = WebUtil.GetSelectedValues(chkAccessibility); IList <string> status = WebUtil.GetSelectedValues(ddlStatus); IList <Message> dataMessage = null; int recordCount = 0; using (Session session = new Session()) { EntityQuery query = session.CreateEntityQuery <Message>(); if (!String.IsNullOrEmpty(msgTypeId)) { query.And(Exp.Eq("MsgTypeId", msgTypeId)); } if (!String.IsNullOrEmpty(title)) { query.And(Exp.Like("Title", "%" + title + "%")); } if (accessibility != null && accessibility.Count > 0) { query.And(Exp.In("Accessibility", accessibility)); } if (status != null && status.Count > 0) { query.And(Exp.In("Status", status)); } query.SetPage(pageIndex, pageSize); dataMessage = query.List <Message>(); if (fetchRecordCount) { recordCount = query.Count(); } } this.rptMessage.DataSource = dataMessage; this.rptMessage.DataBind(); if (fetchRecordCount) { this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = recordCount; } WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex); WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex); //After a query, save current query this.hidReturnUrl.Value = GetReturnUrl(); }
/// <summary> /// Get query conditions from page , and query data from db, then bind the query result to Repeater /// </summary> private void QueryAndBindData(int pageIndex, int pageSize, bool fetchRecordCount) { string tmplCode = txtTmplCode.Text.Trim(); string name = txtName.Text.Trim(); string msgType = ddlMsgType.SelectedValue; IList <string> accessibility = WebUtil.GetSelectedValues(cklAccessibility); IList <MsgTemplate> dataMsgTemplate = null; int recordCount = 0; using (Session session = new Session()) { EntityQuery query = session.CreateEntityQuery <MsgTemplate>(); if (!String.IsNullOrEmpty(tmplCode)) { query.And(Exp.Like("TmplCode", "%" + tmplCode + "%")); } if (!String.IsNullOrEmpty(name)) { query.And(Exp.Like("Name", "%" + name + "%")); } if (!String.IsNullOrEmpty(msgType)) { query.And(Exp.Eq("MsgTypeId", msgType)); } if (accessibility != null && accessibility.Count > 0) { query.And(Exp.In("Accessibility", accessibility)); } query.SetPage(pageIndex, pageSize); dataMsgTemplate = query.List <MsgTemplate>(); if (fetchRecordCount) { recordCount = query.Count(); } } this.rptMsgTemplate.DataSource = dataMsgTemplate; this.rptMsgTemplate.DataBind(); if (fetchRecordCount) { this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = recordCount; } WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex); WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex); //After a query, save current query this.hidReturnUrl.Value = GetReturnUrl(); }
public static bool HasChildren(ISession session, int operationId) { EntityQuery qry = session.CreateEntityQuery <Operation>(); qry.And(Exp.Eq("ParentId", operationId)); return(qry.Count() > 0); }
public static bool HasChildren(ISession session, int groupId) { EntityQuery qry = session.CreateEntityQuery <UserGroup>(); qry.And(Exp.Eq("ParentId", groupId)); return(qry.Count() > 0); }
public static StockDetail Retrieve(ISession session, int skuId, string locationCode, string areaCode, string sectionCode) { if (skuId <= 0) { return(null); } if (string.IsNullOrEmpty(locationCode) || locationCode.Trim().Length <= 0) { return(null); } if (string.IsNullOrEmpty(areaCode) || areaCode.Trim().Length <= 0) { return(null); } EntityQuery query = session.CreateEntityQuery <StockDetail>() .Where(Exp.Eq("SKUID", skuId) & Exp.Eq("LocationCode", locationCode) & Exp.Eq("AreaCode", areaCode)) .OrderBy("StockQty", Order.Desc); if (!string.IsNullOrEmpty(sectionCode) && sectionCode.Trim().Length > 0) { query.And(Exp.Eq("SectionCode", sectionCode.Trim())); } IList <StockDetail> details = query.List <StockDetail>(); return(details == null || details.Count <= 0 ? null : details[0]); }
private void GetChidren(ISession session) { EntityQuery qry = session.CreateEntityQuery <UserGroup>(); qry.And(Exp.Eq("ParentId", this.GroupId)); this._children = qry.List <UserGroup>(); }
private void GetChidren(ISession session) { EntityQuery qry = session.CreateEntityQuery <Operation>(); qry.And(Exp.Eq("ParentId", this.OperationId)); qry.OrderBy(new OrderBy("SeqNo", Order.Asc)); this._children = qry.List <Operation>(); }
/// <summary> /// Get query conditions from page , and query data from db, then bind the query result to Repeater /// </summary> private void QueryAndBindData(int pageIndex, int pageSize, bool fetchRecordCount) { string category = ddlCategory.SelectedValue; string title = txtTitle.Text.Trim(); IList <Dashlet> dataDashlet = null; int recordCount = 0; using (Session session = new Session()) { EntityQuery query = session.CreateEntityQuery <Dashlet>(); if (!String.IsNullOrEmpty(category)) { query.And(Exp.Like("Category", "%" + category + "%")); } if (!String.IsNullOrEmpty(title)) { query.And(Exp.Like("Title", "%" + title + "%")); } query.SetPage(pageIndex, pageSize); dataDashlet = query.List <Dashlet>(); if (fetchRecordCount) { recordCount = query.Count(); } } this.rptDashlet.DataSource = dataDashlet; this.rptDashlet.DataBind(); if (fetchRecordCount) { this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = recordCount; } WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex); WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex); //After a query, save current query this.hidReturnUrl.Value = GetReturnUrl(); }
public bool Create(ISession session) { EntityQuery qry = session.CreateEntityQuery <User>(); qry.And(Exp.Eq("UserName", this.UserName)); if (qry.Count() > 0) { throw new ApplicationException("已经存在帐户名称为{0}的用户,请选择其他的名称!"); } return(EntityManager.Create(session, this)); }
/// <summary> /// 是否存在指定条件的用户的DashPage /// </summary> /// <param name="session"></param> /// <param name="propertyNames"></param> /// <param name="propertyValues"></param> /// <returns></returns> public static bool Exists(ISession session, string[] propertyNames, object[] propertyValues) { if (propertyNames != null && propertyValues != null && propertyNames.Length > 0 && propertyValues.Length == propertyNames.Length) { EntityQuery qry = session.CreateEntityQuery <UserDashpage>(); for (int i = 0; i < propertyNames.Length; i++) { qry.And(Exp.Eq(propertyNames[i], propertyValues[i])); } return(qry.Count() > 0); } return(false); }
public static Operation Retrieve(ISession session, string operationName) { EntityQuery qry = session.CreateEntityQuery <Operation>(); qry.And(Exp.Eq("OPT_NAME", operationName)); IList <Operation> list = qry.List <Operation>(); if (list != null && list.Count > 0) { return(list[0]); } return(null); }
/// <summary> /// 是否存在指定条件的实体 /// </summary> /// <param name="session"></param> /// <param name="conditonPropertyNames"></param> /// <param name="conditionPropertyValues"></param> /// <returns></returns> public static bool Exists(ISession session, string[] conditonPropertyNames, object[] conditionPropertyValues) { EntityQuery query = session.CreateEntityQuery <MsgSubscriber>(); if (conditionPropertyValues != null && conditonPropertyNames != null && conditionPropertyValues.Length == conditonPropertyNames.Length) { for (int i = 0; i < conditionPropertyValues.Length; i++) { query.And(Exp.Eq(conditonPropertyNames[i], conditionPropertyValues[i])); } } return(query.Count() > 0); }
public static void BindEntity2ListControl <entityT>(ISession session, ListControl listCtrl, string dataTextProperty, string dataValueProperty, params Magic.Framework.ORM.Query.AbstractExpression[] filterExps) { EntityQuery qry = session.CreateEntityQuery <entityT>(); if (filterExps != null && filterExps.Length > 0) { foreach (AbstractExpression exp in filterExps) { qry.And(exp); } } IList <entityT> list = qry.List <entityT>(); BindEntity2ListControl <entityT>(listCtrl, dataTextProperty, dataValueProperty, list); }
/// <summary> /// <para>开始库存交易</para> /// <returns>执行成功返回true,否则将抛出异常</returns> /// </summary> /// <remarks> /// 1. 采购退货没有使用这个方式 /// </remarks> public static bool CommitWHTrans(ISession session, IWHTransHead transHead) { if (transHead == null) { throw new Exception("the order committed for wh transaction is null"); } if (string.IsNullOrEmpty(transHead.OrderTypeCode) || transHead.OrderTypeCode.Trim().Length <= 0) { throw new Exception(string.Format("the order {0}'s OrderTypeCode is null or empty", transHead.OrderNumber)); } OrderTypeDef orderTypeDef = OrderTypeDef.Retrieve(session, transHead.OrderTypeCode); if (orderTypeDef == null) { throw new Exception(string.Format("OrderTypeDef {0} not exists", transHead.OrderTypeCode)); } if (orderTypeDef.TransStepCount <= 0) { throw new Exception("transaction steps (TransStepCount) invalidate"); } int commitDate = Cast.Int(DateTime.Now.ToString("yyyyMMdd")), commitTime = Cast.Int(DateTime.Now.ToString("HHmm")); int lineNumber = 1; //交易主档 log.DebugFormat("to build transaction head for {0}...", transHead.OrderNumber); WHTransHead head = new WHTransHead(); #region 创建主档 head.TransOrderType = transHead.OrderTypeCode; head.TransOrderNumber = transHead.OrderNumber; head.RefOrderType = transHead.RefOrderType; head.RefOrderNumber = transHead.RefOrderNumber; head.OriginalOrderType = transHead.OriginalOrderType; head.OriginalOrderNumber = transHead.OrginalOrderNumber; head.CommitUser = transHead.CreateUser; head.CommitDate = commitDate; head.CommitTime = commitTime; head.TransDesc = transHead.Note; #endregion //交易明细 IList <WHTransLine> lines = new List <WHTransLine>(); IList <IWHTransLine> transLines = transHead.GetLines(session); if (transLines == null || transLines.Count <= 0) { transHead.AfterTransaction(session); log.Warn(orderTypeDef.TypeText + transHead.OrderNumber + " has no lines, the transaction finished"); return(true); } #region 创建明细 log.DebugFormat("to build transaction lines for {0}...", transHead.OrderNumber); foreach (IWHTransLine transLine in transLines) { for (int i = 0; i < orderTypeDef.TransStepCount; i++) { log.DebugFormat("line:{0}, trans step:{1}", transLine.LineNumber, i + 1); OrderTransDef orderTransDef = OrderTransDef.Retrieve(session, orderTypeDef.OrderTypeCode, i + 1); if (orderTransDef == null) { throw new Exception(string.Format(" transaction step {0} for order type {1} not exists", i + 1, transHead.OrderTypeCode)); } IList <WHTransLine> tempTransLine = new List <WHTransLine>(); if (transHead.PreLockStock) { //销售发货、换货发货,生成发货单的时候已经为每个发货明细分配好明细库位、货架、出库数量 // (可能一个发货明细从多个库位、货架出货,具体的分配明细在CRMSNLineINV中) //并且冻结明细库存。因此发货出库时除了出库操作外,需要恢复冻结的明细库存量 IList <IWHLockItem> lockItems = transLine.GetLockItem(session); if (lockItems == null || lockItems.Count <= 0) { log.DebugFormat("{0}{1} use pre-locked detail stock items", orderTypeDef.TypeText, transHead.OrderNumber); throw new Exception("no pre-locked detail stock items found"); } foreach (IWHLockItem li in lockItems) { log.DebugFormat("pre-locked item: trans type={0}, id={1}, ownerid={2}, area={3}, section={4}, qty={5}", transLine.TransTypeCode, li.StockDetailID, li.OwnerID, li.AreaCode, li.SectionCode, li.Quantity); TransItemWrapper wrapper = new TransItemWrapper( transLine.SKUID, transLine.UnitID, transLine.CurrencyCode, transLine.LineNumber, transLine.OriginalOrderLine, transLine.TaxValue, transLine.TransTypeCode, li.Quantity, transLine.UnQualifiedQty, transLine.Price, li.LocationCode, li.AreaCode, li.SectionCode, li.StockDetailID); tempTransLine.Add(BuildTransLine(session, wrapper, transHead, commitDate, commitTime, ref lineNumber, orderTransDef)); } } else if (transLine is IWHTransTransferLine) { //移库操作,从A移库到B,对应会配置2个交易明细 TransTypeDef tranTypeDef = TransTypeDef.Retrieve(session, orderTransDef.TransTypeCode); if (tranTypeDef.TransProperty == TransProperty.Out) //移库操作,出库部分 { IWHTransTransferLine transferLine = transLine as IWHTransTransferLine; log.DebugFormat("transfer order line - Out: trans type={0}, sku={1}, price={2}, qty={3}, area={4}, section={5}, stoid={6}", transLine.TransTypeCode, transLine.SKUID, transLine.Price, transLine.QualifiedQty, transLine.AreaCode, transLine.SectionCode, transferLine.FromStockID); TransItemWrapper wrapper = new TransItemWrapper( transLine.SKUID, transLine.UnitID, transLine.CurrencyCode, transLine.LineNumber, transLine.OriginalOrderLine, transLine.TaxValue, transLine.TransTypeCode, transLine.QualifiedQty, transLine.UnQualifiedQty, transLine.Price, transLine.LocationCode, transLine.AreaCode, transLine.SectionCode, transferLine.FromStockID); tempTransLine.Add(BuildTransLine(session, wrapper, transHead, commitDate, commitTime, ref lineNumber, orderTransDef)); } else if (tranTypeDef.TransProperty == TransProperty.In) //移库操作,入库部分 { IWHTransTransferLine transferLine = transLine as IWHTransTransferLine; log.DebugFormat("transfer order line - In: trans type={0}, sku={1}, price={2}, qty={3}, area={4}, section={5}", transLine.TransTypeCode, transLine.SKUID, transLine.Price, transLine.QualifiedQty, transferLine.ToArea, transferLine.ToSection); TransItemWrapper wrapper = new TransItemWrapper( transLine.SKUID, transLine.UnitID, transLine.CurrencyCode, transLine.LineNumber, transLine.OriginalOrderLine, transLine.TaxValue, transLine.TransTypeCode, transLine.QualifiedQty, transLine.UnQualifiedQty, transLine.Price, transferLine.ToLocation, transferLine.ToArea, transferLine.ToSection, 0); tempTransLine.Add(BuildTransLine(session, wrapper, transHead, commitDate, commitTime, ref lineNumber, orderTransDef)); } else { throw new Exception("Transfer Order Type does not support TransProperty " + tranTypeDef.TransProperty.ToString()); } } else //其它出入库操作 { int stoid = 0; if (transLine is IWHExactItem) { stoid = (transLine as IWHExactItem).StockDetailID; } log.DebugFormat("trans order line: trans type={0}, sku={1}, price={2}, qty={3}, area={4}, section={5}", transLine.TransTypeCode, transLine.SKUID, transLine.Price, transLine.QualifiedQty, transLine.AreaCode, transLine.SectionCode); TransItemWrapper wrapper = new TransItemWrapper( transLine.SKUID, transLine.UnitID, transLine.CurrencyCode, transLine.LineNumber, transLine.OriginalOrderLine, transLine.TaxValue, transLine.TransTypeCode, transLine.QualifiedQty, transLine.UnQualifiedQty, transLine.Price, transLine.LocationCode, transLine.AreaCode, transLine.SectionCode, stoid); tempTransLine.Add(BuildTransLine(session, wrapper, transHead, commitDate, commitTime, ref lineNumber, orderTransDef)); } foreach (WHTransLine line in tempTransLine) { lines.Add(line); } } } #endregion #region 更新库存表, 计算移动平均成本 //update ... set STOCK_QTY=STOCK_QTY+:qty,避免并发影响 //事务一个一个串行排队,应该不会造成库存量不准 log.DebugFormat("to update stock qty for {0}...", transHead.OrderNumber); DbSession dbSession = session.DbSession as DbSession; IDbCommand commandUpdate1 = dbSession.CreateSqlStringCommand("update INV_STOCK_SUM set USE_QTY=USE_QTY+:qty,LAST_UPDATE=sysdate where SKU_ID=:skuId"); IDbCommand commandInsert1 = dbSession.CreateSqlStringCommand("insert into INV_STOCK_SUM(SKU_ID,USE_QTY,FROZEN_QTY,LAST_UPDATE) values(:skuId,:qty,0,sysdate)"); IDbCommand commondUpdate2 = dbSession.CreateSqlStringCommand("update INV_STOCK_DETAIL set STOCK_QTY=STOCK_QTY+:qty,FROZEN_QTY=FROZEN_QTY+:fzqty where STOCK_DTL_ID=:dtlId"); IDbCommand cmdCheck = dbSession.CreateSqlStringCommand("select STOCK_QTY from INV_STOCK_DETAIL where STOCK_DTL_ID=:dtlId"); foreach (WHTransLine l in lines) { //计算移动平均成本的逻辑 TransTypeDef transDef = TransTypeDef.Retrieve(session, l.TransTypeCode); if (transDef.IsCostTrans) //成本交易才计算移动平均价 { //采购成本计算说明: //小规模纳税人,采购税率(进项税)不可以抵扣,即没有退税政策,不管税率多少都必须算作采购成本,所以单位采购成本即为采购价格 //一般纳税人有退税政策,不过涉及的因素比较多,比如对方是什么性质的企业,所开的发票是否可以退税等。企业规模增长,成为一般纳税人后, //采购收货由财务手工确定是否可以抵扣,抵扣的税率为多少 //一般采购成本需要考虑的其它费用包括:运杂费、运输途中的损耗、国外贸易的进口关税。采购差旅费一般记入管理费用 //系统实现: //流程: 采购下达采购订单=>仓库收货检验=>财务确定是否可抵扣、抵扣税率=>系统执行入库、计算商品移动平均成本 //1. 系统默认是不可以抵扣的,采购收货的抵扣税率设置为0 //2. 如果可以抵扣,财务选择收货抵扣税率,这种情况下采购成本计算公式为: (采购数量*采购单价)/(1+抵扣税率) //3. 如果一张采购单分多次送货、收货,财务先与供应商沟通所开发票是否可以退税抵扣,供应商在采购订单全部送货完毕后开发票, // 所开发票必须与先前确定的发票类型、金额保持一致,否则财务将发票退回供应商重开 // 只有最后一次收货之后供应商才会开发票,所以开票校验之类的动作必须能对应多次收货 //4. 对于国内贸易运杂费等其它因素可以不用考虑到采购成本中(因为一般金额比较小,财务可以手工记入相应费用分录中);但对于国际贸易则必须考虑运费、进口关税之类 if (l.TaxValue < 0) { l.TaxValue = 0; } decimal costAmt = l.TransQty * l.TransPrice / (1 + l.TaxValue); DataSet ds = session.CreateObjectQuery(@" select a.AvgMoveCost as AvgMoveCost,b.StockQty as StockQty from ItemSpec a left join StockSummary b on a.SKUID=b.SKUID where a.SKUID=?sku") .Attach(typeof(ItemSpec)).Attach(typeof(StockSummary)) .SetValue("?sku", l.SKUID, "a.SKUID") .DataSet(); decimal prevCost = Cast.Decimal(ds.Tables[0].Rows[0]["AvgMoveCost"], 0M), currentStock = Cast.Decimal(ds.Tables[0].Rows[0]["StockQty"], 0M); decimal currentCost = (prevCost * currentStock + costAmt) / (l.TransQty + currentStock); log.DebugFormat("new avg mov cost: {0}. old cost:{1}, old stock:{2}, trans amt:{3}, trans qty:{4}", currentCost, prevCost, currentStock, costAmt, l.TransQty); ItemSpec spec = new ItemSpec(); spec.SKUID = l.SKUID; spec.AvgMoveCost = currentCost; spec.ItemCost = currentCost; spec.Update(session, "AvgMoveCost", "ItemCost"); } //质检仓的出入库事件,不更新汇总库存表的库存量 if (!l.IsScrap && !l.IsQC && !l.IsNonFormal) { if (session.CreateEntityQuery <StockSummary>().Where(Exp.Eq("SKUID", l.SKUID)).Count() > 0) { log.DebugFormat("to update summary stock, sku:{0}, qty:{1}", l.SKUID, l.TransQty); commandUpdate1.Parameters.Clear(); dbSession.AddParameter(commandUpdate1, ":qty", EntityManager.GetEntityMapping(typeof(StockSummary)).GetPropertyMapping("StockQty").DbTypeInfo, l.TransQty); dbSession.AddParameter(commandUpdate1, ":skuId", EntityManager.GetEntityMapping(typeof(StockSummary)).GetPropertyMapping("SKUID").DbTypeInfo, l.SKUID); dbSession.ExecuteNonQuery(commandUpdate1); } else { log.DebugFormat("to create summary stock, sku:{0}, qty:{1}", l.SKUID, l.TransQty); commandInsert1.Parameters.Clear(); dbSession.AddParameter(commandInsert1, ":skuId", EntityManager.GetEntityMapping(typeof(StockSummary)).GetPropertyMapping("SKUID").DbTypeInfo, l.SKUID); dbSession.AddParameter(commandInsert1, ":qty", EntityManager.GetEntityMapping(typeof(StockSummary)).GetPropertyMapping("StockQty").DbTypeInfo, l.TransQty); dbSession.ExecuteNonQuery(commandInsert1); } } bool update = false; decimal qty = l.TransQty, fzqty = 0M; if (transHead.PreLockStock) { update = true; fzqty = l.TransQty; StockDetail stockDetail = StockDetail.Retrieve(session, l.StockDetailID); if (stockDetail != null) { l.BeforeTransQty = stockDetail.StockQty; } else { log.ErrorFormat("the locked sto dtl {0} for order {1}, line {2} not exists", l.StockDetailID, transHead.RefOrderNumber, l.RefOrderLine); throw new Exception("被冻结的库存明细" + l.StockDetailID.ToString() + "不存在"); } } else if (l.StockDetailID > 0) { StockDetail stockDetail = StockDetail.Retrieve(session, l.StockDetailID); if (stockDetail != null) { update = true; l.BeforeTransQty = stockDetail.StockQty; } else { log.ErrorFormat("sto dtl {0} for order {1}, line {2} not exists", l.StockDetailID, transHead.RefOrderNumber, l.RefOrderLine); throw new Exception("库存明细" + l.StockDetailID.ToString() + "不存在"); } } else { EntityQuery query = session.CreateEntityQuery <StockDetail>() .Where(Exp.Eq("SKUID", l.SKUID) & Exp.Eq("LocationCode", l.LocationCode) & Exp.Eq("AreaCode", l.AreaCode)) .And(Exp.Eq("SectionCode", string.IsNullOrEmpty(l.SectionCode) || l.SectionCode.Trim().Length <= 0 ? " " : l.SectionCode)) .SetLastOffset(1); if (l.HasLotControl && !string.IsNullOrEmpty(l.LotNumber) && l.LotNumber.Trim().Length > 0) { query.And(Exp.Eq("LotNumber", l.LotNumber)); } IList <StockDetail> stockDetails = query.List <StockDetail>(); if (stockDetails != null && stockDetails.Count > 0) { update = true; l.StockDetailID = stockDetails[0].StockDetailID; l.BeforeTransQty = stockDetails[0].StockQty; } } if (update) { log.DebugFormat("to update detail stock, sku:{0}, old qty:{1}, trans qty:{2}", l.SKUID, l.BeforeTransQty, l.TransQty); commondUpdate2.Parameters.Clear(); dbSession.AddParameter(commondUpdate2, ":qty", EntityManager.GetEntityMapping(typeof(StockDetail)).GetPropertyMapping("StockQty").DbTypeInfo, qty); dbSession.AddParameter(commondUpdate2, ":fzqty", EntityManager.GetEntityMapping(typeof(StockDetail)).GetPropertyMapping("FrozenQty").DbTypeInfo, fzqty); dbSession.AddParameter(commondUpdate2, ":dtlId", EntityManager.GetEntityMapping(typeof(StockDetail)).GetPropertyMapping("StockDetailID").DbTypeInfo, l.StockDetailID); dbSession.ExecuteNonQuery(commondUpdate2); } else { log.DebugFormat("to create detail stock, sku{0}, trans qty:{1}", l.SKUID, l.TransQty); l.BeforeTransQty = 0M; StockDetail sto = new StockDetail(); sto.SKUID = l.SKUID; sto.LocationCode = l.LocationCode; sto.AreaCode = l.AreaCode; sto.SectionCode = string.IsNullOrEmpty(l.SectionCode) || l.SectionCode.Trim().Length <= 0 ? " " : l.SectionCode; sto.LotNumber = string.IsNullOrEmpty(l.LotNumber) || l.LotNumber.Trim().Length <= 0 ? " " : l.LotNumber; sto.StockQty = l.TransQty; sto.Create(session); l.StockDetailID = sto.StockDetailID; } cmdCheck.Parameters.Clear(); dbSession.AddParameter(cmdCheck, ":dtlId", DbTypeInfo.Int32(), l.StockDetailID); decimal newStoQty = Cast.Decimal(dbSession.ExecuteScalar(cmdCheck)); if (newStoQty < 0M) { throw new Exception(string.Format("库存明细{0}将变成负数!不允许负库存!", l.StockDetailID)); } } #endregion #region 插入交易记录 //insert记录,不会存在并发冲突 head.Create(session); log.DebugFormat("wh trans head created: {0}", head.TransNumber); foreach (WHTransLine l in lines) { l.TransNumber = head.TransNumber; l.Create(session); } log.DebugFormat("{0} wh trans lines created", lines.Count); transHead.AfterTransaction(session); #endregion return(true); }