public void UpdateMaster(AdjustInfo oParam) { //������Ը���note TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { //�����dz�ʼ״̬ if ( getCurrentStatus(oParam.SysNo) != (int)AppEnum.AdjustStatus.Origin ) throw new BizException("status is not origin now, update failed"); //���� ����, ��ע Hashtable ht = new Hashtable(3); ht.Add("SysNo", oParam.SysNo); ht.Add("Note", oParam.Note); if ( 1!=new AdjustDac().UpdateMaster(ht)) throw new BizException("expected one-row update failed, update failed "); scope.Complete(); } }
private void map(AdjustInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.AdjustID = Util.TrimNull(tempdr["AdjustID"]); oParam.StockSysNo = Util.TrimIntNull(tempdr["StockSysNo"]); oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]); oParam.CreateUserSysNo = Util.TrimIntNull(tempdr["CreateUserSysNo"]); oParam.AuditTime = Util.TrimDateNull(tempdr["AuditTime"]); oParam.AuditUserSysNo = Util.TrimIntNull(tempdr["AuditUserSysNo"]); oParam.OutTime = Util.TrimDateNull(tempdr["OutTime"]); oParam.OutUserSysNo = Util.TrimIntNull(tempdr["OutUserSysNo"]); oParam.Status = Util.TrimIntNull(tempdr["Status"]); oParam.Note = Util.TrimNull(tempdr["Note"]); }
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 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)) { //�����dz�ʼ 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 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)) { //�����dz�ʼ 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 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 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)) { //�����dz�ʼ 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 Create(AdjustInfo oParam) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { oParam.SysNo = SequenceDac.GetInstance().Create("St_Adjust_Sequence"); oParam.AdjustID = getAdjustID(oParam.SysNo); //���������¼ int rowsAffected = new AdjustDac().InsertMaster(oParam); if(rowsAffected != 1) throw new BizException("insert adjust master error"); foreach( AdjustItemInfo item in oParam.itemHash.Values) { item.AdjustSysNo = oParam.SysNo; rowsAffected = new AdjustDac().InsertItem(item); if ( rowsAffected != 1) throw new BizException("insert adjust item error"); if ( item.AdjustQty < 0 ) {//��Ҫռ�ÿ�� InventoryManager.GetInstance().SetAvailableQty(oParam.StockSysNo, item.ProductSysNo, -1*item.AdjustQty); } } scope.Complete(); } }
public int ImportMaster(AdjustInfo oParam) { string sql = @"INSERT INTO St_Adjust ( SysNo, AdjustID, StockSysNo, CreateTime, CreateUserSysNo, AuditTime, AuditUserSysNo, OutTime, OutUserSysNo, Status, Note ) VALUES ( @SysNo, @AdjustID, @StockSysNo, @CreateTime, @CreateUserSysNo, @AuditTime, @AuditUserSysNo, @OutTime, @OutUserSysNo, @Status, @Note )"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4); SqlParameter paramAdjustID = new SqlParameter("@AdjustID", SqlDbType.NVarChar,20); SqlParameter paramStockSysNo = new SqlParameter("@StockSysNo", SqlDbType.Int,4); SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime); SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int,4); SqlParameter paramAuditTime = new SqlParameter("@AuditTime", SqlDbType.DateTime); SqlParameter paramAuditUserSysNo = new SqlParameter("@AuditUserSysNo", SqlDbType.Int,4); SqlParameter paramOutTime = new SqlParameter("@OutTime", SqlDbType.DateTime); SqlParameter paramOutUserSysNo = new SqlParameter("@OutUserSysNo", SqlDbType.Int,4); SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4); SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,200); if ( oParam.SysNo != AppConst.IntNull) paramSysNo.Value = oParam.SysNo; else paramSysNo.Value = System.DBNull.Value; if ( oParam.AdjustID != AppConst.StringNull) paramAdjustID.Value = oParam.AdjustID; else paramAdjustID.Value = System.DBNull.Value; if ( oParam.StockSysNo != AppConst.IntNull) paramStockSysNo.Value = oParam.StockSysNo; else paramStockSysNo.Value = System.DBNull.Value; if ( oParam.CreateTime != AppConst.DateTimeNull) paramCreateTime.Value = oParam.CreateTime; else paramCreateTime.Value = System.DBNull.Value; if ( oParam.CreateUserSysNo != AppConst.IntNull) paramCreateUserSysNo.Value = oParam.CreateUserSysNo; else paramCreateUserSysNo.Value = System.DBNull.Value; if ( oParam.AuditTime != AppConst.DateTimeNull) paramAuditTime.Value = oParam.AuditTime; else paramAuditTime.Value = System.DBNull.Value; if ( oParam.AuditUserSysNo != AppConst.IntNull) paramAuditUserSysNo.Value = oParam.AuditUserSysNo; else paramAuditUserSysNo.Value = System.DBNull.Value; if ( oParam.OutTime != AppConst.DateTimeNull) paramOutTime.Value = oParam.OutTime; else paramOutTime.Value = System.DBNull.Value; if ( oParam.OutUserSysNo != AppConst.IntNull) paramOutUserSysNo.Value = oParam.OutUserSysNo; else paramOutUserSysNo.Value = System.DBNull.Value; if ( oParam.Status != AppConst.IntNull) paramStatus.Value = oParam.Status; else paramStatus.Value = System.DBNull.Value; if ( oParam.Note != AppConst.StringNull) paramNote.Value = oParam.Note; else paramNote.Value = System.DBNull.Value; cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramAdjustID); cmd.Parameters.Add(paramStockSysNo); cmd.Parameters.Add(paramCreateTime); cmd.Parameters.Add(paramCreateUserSysNo); cmd.Parameters.Add(paramAuditTime); cmd.Parameters.Add(paramAuditUserSysNo); cmd.Parameters.Add(paramOutTime); cmd.Parameters.Add(paramOutUserSysNo); cmd.Parameters.Add(paramStatus); cmd.Parameters.Add(paramNote); return SqlHelper.ExecuteNonQuery(cmd); }