public void Import() { if ( !AppConfig.IsImportable) throw new BizException("Is Importable is false"); TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { //���inventory���Ϊ�գ��Ͳ��ܵ������� string sql = "select top 1 * from inventory"; DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( Util.HasMoreRow(ds) ) throw new BizException("the table inventory is not empty"); //��ipp3���� available + allocated + order = account. //�ɹ���;����po�����ʱ����ƣ���ʼ��Ϊ�㡣 //������AccountQty-AvailableQty-WebQty as AllocatedQty, ȫ���ɵ��������� string sql2 = @"select conProduct.newSysNo as productSysNo, AccountQty, AvailableQty+WebQty-OrderQty as AvailableQty, 0 as AllocatedQty, OrderQty, 0 as PurchaseQty, VirtualQty from ipp2003..product_inventory as inv, ippconvert..productbasic as conproduct where inv.productsysno = conProduct.oldsysno"; DataSet ds2 = SqlHelper.ExecuteDataSet(sql2); if ( !Util.HasMoreRow(ds2) ) return; Hashtable ht = new Hashtable(1000); foreach(DataRow dr in ds2.Tables[0].Rows) { InventoryInfo item = new InventoryInfo(); map(item, dr); ht.Add(item.ProductSysNo, item); new InventoryDac().Insert(item); } //���inventory_stock���Ϊ�գ��Ͳ��ܵ������� string sql3 = "select top 1 * from inventory_stock"; DataSet ds3 = SqlHelper.ExecuteDataSet(sql3); if ( Util.HasMoreRow(ds3) ) throw new BizException("the table inventory_stock is not empty"); string sql5 = "select sysno from stock where stockname='�Ϻ�����' "; DataSet ds5 = SqlHelper.ExecuteDataSet(sql5); if ( !Util.HasMoreRow(ds5)) throw new BizException("stock sysno load error"); int stockSysNo = Util.TrimIntNull(ds5.Tables[0].Rows[0]["sysno"]); string sql4 = @"select conStock.newSysno as StockSysno, conProduct.newSysNo as productSysNo, AccountQty, AvailableQty+WebQty as AvailableQty, 0 as AllocatedQty, ShiftInQty, ShiftOutQty,isnull(SafeQty,0) as SafeQty,Position1,Position2, 0 as OrderQty, 0 as PurchaseQty from ipp2003..stock_inventory as inv, ippconvert..productbasic as conproduct, ippconvert..stock as conStock where inv.productsysno = conProduct.oldsysno and conStock.oldsysno = inv.stocksysno"; DataSet ds4 = SqlHelper.ExecuteDataSet(sql4); if ( !Util.HasMoreRow(ds4) ) return; foreach(DataRow dr in ds4.Tables[0].Rows) { InventoryStockInfo item = new InventoryStockInfo(); map(item, dr); if ( item.StockSysNo == stockSysNo ) { InventoryInfo item2 = ht[item.ProductSysNo] as InventoryInfo; if ( item2 == null ) throw new Exception("this product has record in stock, but no records in inventory, impossible!"); item.AvailableQty -= item2.OrderQty; item.OrderQty = item2.OrderQty; } new InventoryDac().Insert(item); } scope.Complete(); } }
/// <summary> /// Ŀǰ�������ڻ�ȡ��λ /// </summary> /// <param name="productSysNoHt"></param> /// <param name="stockSysNo"></param> /// <returns></returns> public Hashtable GetInventoryStockBoundle(Hashtable productSysNoHt, int stockSysNo) { if ( productSysNoHt == null || productSysNoHt.Count == 0) return null; string sql = "select * from inventory_stock where stocksysno = " + stockSysNo + " and productsysno in ("; int index = 0; foreach(int item in productSysNoHt.Keys) { if ( index != 0 ) sql +=","; sql += item.ToString(); index ++; } sql +=")"; DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( !Util.HasMoreRow(ds)) return null; Hashtable ht = new Hashtable(5); foreach(DataRow dr in ds.Tables[0].Rows) { InventoryStockInfo item = new InventoryStockInfo(); map(item, dr); ht.Add(item.ProductSysNo, item); } return ht; }
private InventoryStockInfo LoadInventoryStock(string sql) { DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( !Util.HasMoreRow(ds)) return null; if ( ds.Tables[0].Rows.Count != 1 ) throw new BizException("expected one-row affected failed"); InventoryStockInfo oInfo = new InventoryStockInfo(); map(oInfo, ds.Tables[0].Rows[0]); return oInfo; }
private void map(InventoryStockInfo oParam, DataRow tempdr) { //oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.StockSysNo = Util.TrimIntNull(tempdr["StockSysNo"]); oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]); oParam.AccountQty = Util.TrimIntNull(tempdr["AccountQty"]); oParam.AvailableQty = Util.TrimIntNull(tempdr["AvailableQty"]); oParam.AllocatedQty = Util.TrimIntNull(tempdr["AllocatedQty"]); oParam.OrderQty = Util.TrimIntNull(tempdr["OrderQty"]); oParam.PurchaseQty = Util.TrimIntNull(tempdr["PurchaseQty"]); oParam.ShiftInQty = Util.TrimIntNull(tempdr["ShiftInQty"]); oParam.ShiftOutQty = Util.TrimIntNull(tempdr["ShiftOutQty"]); oParam.SafeQty = Util.TrimIntNull(tempdr["SafeQty"]); oParam.Position1 = Util.TrimNull(tempdr["Position1"]); oParam.Position2 = Util.TrimNull(tempdr["Position2"]); oParam.PosLastUpdateTime = Util.TrimDateNull(tempdr["PosLastUpdateTime"]); }
public int Insert(InventoryStockInfo oParam) { string sql = @"INSERT INTO Inventory_Stock ( StockSysNo, ProductSysNo, AccountQty, AvailableQty, AllocatedQty, OrderQty, PurchaseQty, ShiftInQty, ShiftOutQty, SafeQty, Position1, Position2,PosLastUpdateTime ) VALUES ( @StockSysNo, @ProductSysNo, @AccountQty, @AvailableQty, @AllocatedQty, @OrderQty, @PurchaseQty, @ShiftInQty, @ShiftOutQty, @SafeQty, @Position1, @Position2,@PosLastUpdateTime )"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramStockSysNo = new SqlParameter("@StockSysNo", SqlDbType.Int, 4); SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4); SqlParameter paramAccountQty = new SqlParameter("@AccountQty", SqlDbType.Int, 4); SqlParameter paramAvailableQty = new SqlParameter("@AvailableQty", SqlDbType.Int, 4); SqlParameter paramAllocatedQty = new SqlParameter("@AllocatedQty", SqlDbType.Int, 4); SqlParameter paramOrderQty = new SqlParameter("@OrderQty", SqlDbType.Int, 4); SqlParameter paramPurchaseQty = new SqlParameter("@PurchaseQty", SqlDbType.Int, 4); SqlParameter paramShiftInQty = new SqlParameter("@ShiftInQty", SqlDbType.Int, 4); SqlParameter paramShiftOutQty = new SqlParameter("@ShiftOutQty", SqlDbType.Int, 4); SqlParameter paramSafeQty = new SqlParameter("@SafeQty", SqlDbType.Int, 4); SqlParameter paramPosition1 = new SqlParameter("@Position1", SqlDbType.NVarChar, 20); SqlParameter paramPosition2 = new SqlParameter("@Position2", SqlDbType.NVarChar, 20); SqlParameter paramPosLastUpdateTime = new SqlParameter("@PosLastUpdateTime", SqlDbType.DateTime); paramStockSysNo.Value = oParam.StockSysNo; paramProductSysNo.Value = oParam.ProductSysNo; if (oParam.AccountQty != AppConst.IntNull) paramAccountQty.Value = oParam.AccountQty; else paramAccountQty.Value = System.DBNull.Value; if (oParam.AvailableQty != AppConst.IntNull) paramAvailableQty.Value = oParam.AvailableQty; else paramAvailableQty.Value = System.DBNull.Value; if (oParam.AllocatedQty != AppConst.IntNull) paramAllocatedQty.Value = oParam.AllocatedQty; else paramAllocatedQty.Value = System.DBNull.Value; if (oParam.OrderQty != AppConst.IntNull) paramOrderQty.Value = oParam.OrderQty; else paramOrderQty.Value = System.DBNull.Value; if (oParam.PurchaseQty != AppConst.IntNull) paramPurchaseQty.Value = oParam.PurchaseQty; else paramPurchaseQty.Value = System.DBNull.Value; if (oParam.ShiftInQty != AppConst.IntNull) paramShiftInQty.Value = oParam.ShiftInQty; else paramShiftInQty.Value = System.DBNull.Value; if (oParam.ShiftOutQty != AppConst.IntNull) paramShiftOutQty.Value = oParam.ShiftOutQty; else paramShiftOutQty.Value = System.DBNull.Value; if (oParam.SafeQty != AppConst.IntNull) paramSafeQty.Value = oParam.SafeQty; else paramSafeQty.Value = System.DBNull.Value; if (oParam.Position1 != AppConst.StringNull) paramPosition1.Value = oParam.Position1; else paramPosition1.Value = System.DBNull.Value; if (oParam.Position2 != AppConst.StringNull) paramPosition2.Value = oParam.Position2; else paramPosition2.Value = System.DBNull.Value; if (oParam.PosLastUpdateTime != AppConst.DateTimeNull) paramPosLastUpdateTime.Value = oParam.PosLastUpdateTime; else paramPosLastUpdateTime.Value = System.DBNull.Value; cmd.Parameters.Add(paramStockSysNo); cmd.Parameters.Add(paramProductSysNo); cmd.Parameters.Add(paramAccountQty); cmd.Parameters.Add(paramAvailableQty); cmd.Parameters.Add(paramAllocatedQty); cmd.Parameters.Add(paramOrderQty); cmd.Parameters.Add(paramPurchaseQty); cmd.Parameters.Add(paramShiftInQty); cmd.Parameters.Add(paramShiftOutQty); cmd.Parameters.Add(paramSafeQty); cmd.Parameters.Add(paramPosition1); cmd.Parameters.Add(paramPosition2); cmd.Parameters.Add(paramPosLastUpdateTime); return SqlHelper.ExecuteNonQuery(cmd); }