private void map(ShiftInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.ShiftID = Util.TrimNull(tempdr["ShiftID"]); oParam.StockSysNoA = Util.TrimIntNull(tempdr["StockSysNoA"]); oParam.StockSysNoB = Util.TrimIntNull(tempdr["StockSysNoB"]); 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.InTime = Util.TrimDateNull(tempdr["InTime"]); oParam.InUserSysNo = Util.TrimIntNull(tempdr["InUserSysNo"]); oParam.CheckQtyUserSysNo = Util.TrimIntNull(tempdr["CheckQtyUserSysNo"]); oParam.CheckQtyTime = Util.TrimDateNull(tempdr["CheckQtyTime"]); oParam.Status = Util.TrimIntNull(tempdr["Status"]); oParam.Note = Util.TrimNull(tempdr["Note"]); oParam.DLSysNo = Util.TrimIntNull(tempdr["DLSysNo"]); oParam.SetDeliveryManTime = Util.TrimDateNull(tempdr["SetDeliveryManTime"]); oParam.FreightUserSysNo = Util.TrimIntNull(tempdr["FreightUserSysNo"]); oParam.IsLarge = Util.TrimIntNull(tempdr["IsLarge"]); }
public int InsertMaster(ShiftInfo oParam) { string sql = @"INSERT INTO St_Shift ( SysNo, ShiftID, StockSysNoA, StockSysNoB, CreateTime, CreateUserSysNo, AuditTime, AuditUserSysNo, OutTime, OutUserSysNo, InTime, InUserSysNo, Status, Note ) VALUES ( @SysNo, @ShiftID, @StockSysNoA, @StockSysNoB, @CreateTime, @CreateUserSysNo, @AuditTime, @AuditUserSysNo, @OutTime, @OutUserSysNo, @InTime, @InUserSysNo, @Status, @Note )"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4); SqlParameter paramShiftID = new SqlParameter("@ShiftID", SqlDbType.NVarChar,20); SqlParameter paramStockSysNoA = new SqlParameter("@StockSysNoA", SqlDbType.Int,4); SqlParameter paramStockSysNoB = new SqlParameter("@StockSysNoB", 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 paramInTime = new SqlParameter("@InTime", SqlDbType.DateTime); SqlParameter paramInUserSysNo = new SqlParameter("@InUserSysNo", 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.ShiftID != AppConst.StringNull) paramShiftID.Value = oParam.ShiftID; else paramShiftID.Value = System.DBNull.Value; if ( oParam.StockSysNoA != AppConst.IntNull) paramStockSysNoA.Value = oParam.StockSysNoA; else paramStockSysNoA.Value = System.DBNull.Value; if ( oParam.StockSysNoB != AppConst.IntNull) paramStockSysNoB.Value = oParam.StockSysNoB; else paramStockSysNoB.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.InTime != AppConst.DateTimeNull) paramInTime.Value = oParam.InTime; else paramInTime.Value = System.DBNull.Value; if ( oParam.InUserSysNo != AppConst.IntNull) paramInUserSysNo.Value = oParam.InUserSysNo; else paramInUserSysNo.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(paramShiftID); cmd.Parameters.Add(paramStockSysNoA); cmd.Parameters.Add(paramStockSysNoB); 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(paramInTime); cmd.Parameters.Add(paramInUserSysNo); cmd.Parameters.Add(paramStatus); cmd.Parameters.Add(paramNote); return SqlHelper.ExecuteNonQuery(cmd); }
public void UpdateItemQty(ShiftInfo masterInfo, ShiftItemInfo 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.ShiftStatus.Origin) throw new BizException("status is not origin now, update item qty failed"); //��ȡ������ֵ ShiftItemInfo oldItemInfo = masterInfo.itemHash[itemInfo.ProductSysNo] as ShiftItemInfo; int deltQty = itemInfo.ShiftQty - oldItemInfo.ShiftQty; //���±����ϸ ( ������ӱ����ϸ������ԣ���Ҫ�����ﴦ��һ�£� itemInfo.SysNo = oldItemInfo.SysNo; itemInfo.ShiftSysNo = oldItemInfo.ShiftSysNo; if (1 != new ShiftDac().UpdateItemQty(itemInfo.SysNo, deltQty)) throw new BizException("expected one-row update failed, update item qty failed"); //���¿�� InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNoA, itemInfo.ProductSysNo, deltQty); //���� itemInfo �� masterInfo ע:���ݿ���³ɹ��Ժ�Ÿ����� masterInfo.itemHash.Remove(itemInfo.ProductSysNo); masterInfo.itemHash.Add(itemInfo.ProductSysNo, itemInfo); scope.Complete(); } }
public void UpdateMaster(ShiftInfo 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.ShiftStatus.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 ShiftDac().UpdateMaster(ht)) throw new BizException("expected one-row update failed, update failed "); scope.Complete(); } }
public ShiftInfo Load(int shiftSysNo) { ShiftInfo 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_shift where sysno = " + shiftSysNo; DataSet masterDs = SqlHelper.ExecuteDataSet(masterSql); if (!Util.HasMoreRow(masterDs)) throw new BizException("there is no this shift sysno"); masterInfo = new ShiftInfo(); map(masterInfo, masterDs.Tables[0].Rows[0]); string itemSql = "select * from st_shift_item where shiftsysno=" + shiftSysNo; DataSet itemDs = SqlHelper.ExecuteDataSet(itemSql); if (Util.HasMoreRow(itemDs)) { foreach (DataRow dr in itemDs.Tables[0].Rows) { ShiftItemInfo item = new ShiftItemInfo(); map(item, dr); masterInfo.itemHash.Add(item.ProductSysNo, item); } } scope.Complete(); } return masterInfo; }
public void InsertItem(ShiftInfo masterInfo, ShiftItemInfo 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.ShiftStatus.Origin) throw new BizException("status is not origin now, add item failed"); //��ȡ������ֵ //����item if (1 != new ShiftDac().InsertItem(itemInfo)) throw new BizException("expected one-row update failed, add item failed"); //���¿�� InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNoA, itemInfo.ProductSysNo, itemInfo.ShiftQty); //���� 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_shift"; DataSet ds = SqlHelper.ExecuteDataSet(sql); if (Util.HasMoreRow(ds)) throw new BizException("the table shift 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.shiftid, stocka_con.newsysno as stocksysnoa, stockb_con.newsysno as stocksysnob, create_con.newsysno as createusersysno, audit_con.newsysno as auditusersysno, out_con.newsysno as outusersysno, in_con.newsysno as inusersysno, createtime, audittime,outstocktime as outtime,instocktime as intime, auditstatus, productstatus, note, '1' as status from ipp2003..st_shift as old, ippconvert..sys_user as create_con, ippconvert..sys_user as audit_con, ippconvert..sys_user as out_con, ippconvert..sys_user as in_con, ippconvert..stock as stocka_con, ippconvert..Stock as stockb_con where old.createusersysno *= create_con.oldsysno and old.auditusersysno *= audit_con.oldsysno and old.outstockusersysno *= out_con.oldsysno and old.instockusersysno *= in_con.oldsysno and old.stocksysnoa = stocka_con.oldsysno and old.stocksysnob = stockb_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 aduit product * abandon -1 n/a * origin 0 n/a * verified 1 n/a * * StillInA n/a 0 * OnTheRoad n/a 1 * AlreadyInB n/a 2 */ int newStatus = (int)AppEnum.ShiftStatus.Origin; int auditStatus = Util.TrimIntNull(dr["auditStatus"]); int productStatus = Util.TrimIntNull(dr["productStatus"]); if (auditStatus == -1) newStatus = (int)AppEnum.ShiftStatus.Abandon; if (auditStatus == 0) newStatus = (int)AppEnum.ShiftStatus.Origin; if (auditStatus == 0) newStatus = (int)AppEnum.ShiftStatus.Verified; if (productStatus == 1) newStatus = (int)AppEnum.ShiftStatus.OutStock; if (productStatus == 2) newStatus = (int)AppEnum.ShiftStatus.InStock; ShiftInfo oInfo = new ShiftInfo(); map(oInfo, dr); oInfo.Status = newStatus; if (new ShiftDac().InsertMaster(oInfo) != 1) { throw new BizException("master expected one row error"); } //insert item string sql_item = @"select '0' as sysno, ShiftSysNo, con_product.newsysno as productsysno, shiftqty from ipp2003..St_Shift_Item si, ippconvert..productbasic as con_product where si.productsysno = con_product.oldsysno and ShiftSysNo=" + oInfo.SysNo; DataSet ds_item = SqlHelper.ExecuteDataSet(sql_item); if (!Util.HasMoreRow(ds_item)) continue; foreach (DataRow drItem in ds_item.Tables[0].Rows) { ShiftItemInfo oItem = new ShiftItemInfo(); map(oItem, drItem); int resultitem = new ShiftDac().InsertItem(oItem); if (resultitem < 1) throw new BizException("insert item row < 1"); //������� if (oInfo.Status == (int)AppEnum.ShiftStatus.Origin || oInfo.Status == (int)AppEnum.ShiftStatus.Verified) { InventoryManager.GetInstance().SetAvailableQty(oInfo.StockSysNoA, oItem.ProductSysNo, oItem.ShiftQty); } } } string sqlMaxSysNo = "select top 1 sysno from ipp2003..st_shift 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_Shift_Sequence"); } while (newSysNo < maxSysNo); scope.Complete(); } }
public void DeleteItem(ShiftInfo 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.ShiftStatus.Origin) throw new BizException("status is not origin now, delete item failed"); //��ȡ������ֵ ShiftItemInfo oldItemInfo = masterInfo.itemHash[itemProductSysNo] as ShiftItemInfo; int deltQty = -1 * oldItemInfo.ShiftQty; //����item if (1 != new ShiftDac().DeleteItem(oldItemInfo.SysNo)) throw new BizException("expected one-row update failed, delete item qty failed"); //���¿�� InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNoA, itemProductSysNo, deltQty); //���� masterInfo masterInfo.itemHash.Remove(itemProductSysNo); //����RMA�ƿ���Ϣ��Ϣ Hashtable ht = new Hashtable(); ht.Add("ShiftSysNo", masterInfo.SysNo); ht.Add("RevertProductSysNo", itemProductSysNo); DataSet ds = GetRMARegisterByShiftSysNo(ht); if (Util.HasMoreRow(ds)) { RMARegisterManager.GetInstance().DeleteRegisterShift(ds); } scope.Complete(); } }
public void Create(ShiftInfo 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_Shift_Sequence"); oParam.ShiftID = getShiftID(oParam.SysNo); //���������¼ int rowsAffected = new ShiftDac().InsertMaster(oParam); if (rowsAffected != 1) throw new BizException("insert shift master error"); foreach (ShiftItemInfo item in oParam.itemHash.Values) { item.ShiftSysNo = oParam.SysNo; rowsAffected = new ShiftDac().InsertItem(item); if (rowsAffected != 1) throw new BizException("insert shift item error"); InventoryManager.GetInstance().SetAvailableQty(oParam.StockSysNoA, item.ProductSysNo, item.ShiftQty); } scope.Complete(); } }