private void map(AdjustItemInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.AdjustSysNo = Util.TrimIntNull(tempdr["AdjustSysNo"]); oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]); oParam.AdjustQty = Util.TrimIntNull(tempdr["AdjustQty"]); oParam.AdjustCost = Util.TrimDecimalNull(tempdr["AdjustCost"]); }
public void InsertItem(AdjustInfo masterInfo, AdjustItemInfo itemInfo) { if (masterInfo.itemHash.ContainsKey(itemInfo.ProductSysNo)) { throw new BizException("item duplicated!"); } TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { //必须是初始 if (getCurrentStatus(masterInfo.SysNo) != (int)AppEnum.AdjustStatus.Origin) { throw new BizException("status is not origin now, add item failed"); } //获取成本,主要的打印和浏览用,因为出库的时候还是会更新的 ProductPriceInfo priceInfo = ProductManager.GetInstance().LoadPrice(itemInfo.ProductSysNo); itemInfo.AdjustCost = priceInfo.UnitCost; //更新item if (1 != new AdjustDac().InsertItem(itemInfo)) { throw new BizException("expected one-row update failed, add item failed"); } //更新库存 if (itemInfo.AdjustQty < 0) { InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemInfo.ProductSysNo, -1 * itemInfo.AdjustQty); } //更新 itemInfo 到 masterInfo masterInfo.itemHash.Add(itemInfo.ProductSysNo, itemInfo); scope.Complete(); } }
public AdjustInfo Load(int adjustSysNo) { AdjustInfo masterInfo; TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { string masterSql = "select * from st_adjust where sysno = " + adjustSysNo; DataSet masterDs = SqlHelper.ExecuteDataSet(masterSql); if (!Util.HasMoreRow(masterDs)) { throw new BizException("there is no this adjust sysno"); } masterInfo = new AdjustInfo(); map(masterInfo, masterDs.Tables[0].Rows[0]); string itemSql = "select * from st_adjust_item where adjustsysno=" + adjustSysNo; DataSet itemDs = SqlHelper.ExecuteDataSet(itemSql); if (Util.HasMoreRow(itemDs)) { foreach (DataRow dr in itemDs.Tables[0].Rows) { AdjustItemInfo item = new AdjustItemInfo(); map(item, dr); masterInfo.itemHash.Add(item.ProductSysNo, item); } } scope.Complete(); } return(masterInfo); }
public void DeleteItem(AdjustInfo masterInfo, int itemProductSysNo) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { //必须是初始 if (getCurrentStatus(masterInfo.SysNo) != (int)AppEnum.AdjustStatus.Origin) { throw new BizException("status is not origin now, delete item failed"); } //获取数量差值 AdjustItemInfo oldItemInfo = masterInfo.itemHash[itemProductSysNo] as AdjustItemInfo; int deltQty = -1 * oldItemInfo.AdjustQty; //更新item if (1 != new AdjustDac().DeleteItem(oldItemInfo.SysNo)) { throw new BizException("expected one-row update failed, delete item qty failed"); } //更新库存 if (oldItemInfo.AdjustQty < 0) { InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemProductSysNo, -1 * deltQty); } //更新 masterInfo masterInfo.itemHash.Remove(itemProductSysNo); scope.Complete(); } }
public void Import() { /* 涉及的问题 * 1 还货记录的处理 * 2 库存的处理 * 3 状态的处理 * 4 还有单据id的对应,这个要特别注意 */ if (!AppConfig.IsImportable) { throw new BizException("Is Importable is false"); } string sql = " select top 1 sysno from st_adjust"; DataSet ds = SqlHelper.ExecuteDataSet(sql); if (Util.HasMoreRow(ds)) { throw new BizException("the table adjust is not empty"); } TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { string sql_old = @"select old.sysno, old.adjustid, stock_con.newsysno as stocksysno, create_con.newsysno as createusersysno, audit_con.newsysno as auditusersysno, audit_con.newsysno as outusersysno, createtime, audittime,audittime as outtime, auditstatus, note, '1' as status from ipp2003..st_adjust as old, ippconvert..sys_user as create_con, ippconvert..sys_user as audit_con, ippconvert..stock as stock_con where old.createusersysno *= create_con.oldsysno and old.auditusersysno *= audit_con.oldsysno and old.stocksysno = stock_con.oldsysno order by old.sysno" ; DataSet ds_old = SqlHelper.ExecuteDataSet(sql_old); if (!Util.HasMoreRow(ds_old)) { return; } foreach (DataRow dr in ds_old.Tables[0].Rows) { /* newStatus audit * abandon -1 * origin 0 * verified 1 */ int newStatus = (int)AppEnum.AdjustStatus.Origin; int auditStatus = Util.TrimIntNull(dr["auditStatus"]); if (auditStatus == -1) { newStatus = (int)AppEnum.AdjustStatus.Abandon; } else if (auditStatus == 0) { newStatus = (int)AppEnum.AdjustStatus.Origin; } else if (auditStatus == 1) { newStatus = (int)AppEnum.AdjustStatus.OutStock; } else { throw new BizException("old adjust status out of range"); } AdjustInfo oInfo = new AdjustInfo(); map(oInfo, dr); oInfo.Status = newStatus; if (new AdjustDac().ImportMaster(oInfo) != 1) { throw new BizException("master expected one row error"); } //insert item string sql_item = @"select '0' as sysno, AdjustSysNo, con_product.newsysno as productsysno, adjustqty, adjustcost from ipp2003..St_Adjust_Item sti, ippconvert..productbasic as con_product where sti.productsysno = con_product.oldsysno and AdjustSysNo=" + oInfo.SysNo; DataSet ds_item = SqlHelper.ExecuteDataSet(sql_item); if (!Util.HasMoreRow(ds_item)) { continue; } foreach (DataRow drItem in ds_item.Tables[0].Rows) { AdjustItemInfo oItem = new AdjustItemInfo(); map(oItem, drItem); if (oItem.AdjustCost == 0) { oItem.AdjustCost = AppConst.IntNull; } int resultitem = new AdjustDac().InsertItem(oItem); if (resultitem < 1) { throw new BizException("insert item row < 1"); } //调整库存 if (oInfo.Status == (int)AppEnum.AdjustStatus.Origin && oItem.AdjustQty < 0) { InventoryManager.GetInstance().SetAvailableQty(oInfo.StockSysNo, oItem.ProductSysNo, -1 * oItem.AdjustQty); } } } string sqlMaxSysNo = "select top 1 sysno from ipp2003..st_adjust order by sysno desc"; DataSet dsMax = SqlHelper.ExecuteDataSet(sqlMaxSysNo); if (!Util.HasMoreRow(dsMax)) { throw new BizException("got max sysno error"); } int maxSysNo = Util.TrimIntNull(dsMax.Tables[0].Rows[0]["sysno"]); // 将自动生成的sysno填到目前的最大单据号 int newSysNo; do { newSysNo = SequenceDac.GetInstance().Create("St_Adjust_Sequence"); } while(newSysNo < maxSysNo); scope.Complete(); } }
public void UpdateItemQty(AdjustInfo masterInfo, AdjustItemInfo itemInfo) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { //必须是初始 if (getCurrentStatus(masterInfo.SysNo) != (int)AppEnum.AdjustStatus.Origin) { throw new BizException("status is not origin now, update item qty failed"); } //获取数量差值 AdjustItemInfo oldItemInfo = masterInfo.itemHash[itemInfo.ProductSysNo] as AdjustItemInfo; /* * old new * >0 >0 不需要处理 * <0 <0 new - old = delt * <0 >0 0 - old = delt * >0 <0 new - 0 = delt */ int newAllocatedQty = 0; int oldQty = oldItemInfo.AdjustQty; int newQty = itemInfo.AdjustQty; int deltQty = newQty - oldQty; if (oldQty < 0 && newQty < 0) { newAllocatedQty = newQty - oldQty; } else if (oldQty < 0 && newQty > 0) { newAllocatedQty = 0 - oldQty; } else if (oldQty > 0 && newQty < 0) { newAllocatedQty = newQty - 0; } //更新表单明细 ( 如果增加表单明细项的属性,需要在这里处理一下) itemInfo.SysNo = oldItemInfo.SysNo; itemInfo.AdjustSysNo = oldItemInfo.AdjustSysNo; //获取成本,主要的打印和浏览用,因为出库的时候还是会更新的 ProductPriceInfo priceInfo = ProductManager.GetInstance().LoadPrice(itemInfo.ProductSysNo); itemInfo.AdjustCost = priceInfo.UnitCost; if (1 != new AdjustDac().UpdateItemQtyCost(itemInfo.SysNo, deltQty, itemInfo.AdjustCost)) { throw new BizException("expected one-row update failed, update item qty failed"); } //更新占用和可用库存 ////////////////////////////////////////////////////////////////////////// if (newAllocatedQty != 0) { InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemInfo.ProductSysNo, -1 * newAllocatedQty); } //更新 itemInfo 到 masterInfo 注:数据库更新成功以后才更新类 masterInfo.itemHash.Remove(itemInfo.ProductSysNo); masterInfo.itemHash.Add(itemInfo.ProductSysNo, itemInfo); scope.Complete(); } }
public int InsertItem(AdjustItemInfo oParam) { string sql = @"INSERT INTO St_Adjust_Item ( AdjustSysNo, ProductSysNo, AdjustQty, AdjustCost ) VALUES ( @AdjustSysNo, @ProductSysNo, @AdjustQty, @AdjustCost );set @SysNo = SCOPE_IDENTITY();"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4); SqlParameter paramAdjustSysNo = new SqlParameter("@AdjustSysNo", SqlDbType.Int, 4); SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4); SqlParameter paramAdjustQty = new SqlParameter("@AdjustQty", SqlDbType.Int, 4); SqlParameter paramAdjustCost = new SqlParameter("@AdjustCost", SqlDbType.Decimal, 9); paramSysNo.Direction = ParameterDirection.Output; if (oParam.AdjustSysNo != AppConst.IntNull) { paramAdjustSysNo.Value = oParam.AdjustSysNo; } else { paramAdjustSysNo.Value = System.DBNull.Value; } if (oParam.ProductSysNo != AppConst.IntNull) { paramProductSysNo.Value = oParam.ProductSysNo; } else { paramProductSysNo.Value = System.DBNull.Value; } if (oParam.AdjustQty != AppConst.IntNull) { paramAdjustQty.Value = oParam.AdjustQty; } else { paramAdjustQty.Value = System.DBNull.Value; } if (oParam.AdjustCost != AppConst.DecimalNull) { paramAdjustCost.Value = oParam.AdjustCost; } else { paramAdjustCost.Value = System.DBNull.Value; } cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramAdjustSysNo); cmd.Parameters.Add(paramProductSysNo); cmd.Parameters.Add(paramAdjustQty); cmd.Parameters.Add(paramAdjustCost); return(SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo)); }