public int Insert(InventoryInfo oParam) { string sql = @"INSERT INTO Inventory ( ProductSysNo, AccountQty, AvailableQty, AllocatedQty, OrderQty, PurchaseQty, VirtualQty ) VALUES ( @ProductSysNo, @AccountQty, @AvailableQty, @AllocatedQty, @OrderQty, @PurchaseQty, @VirtualQty )"; SqlCommand cmd = new SqlCommand(sql); 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 paramVirtualQty = new SqlParameter("@VirtualQty", SqlDbType.Int,4); 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.VirtualQty != AppConst.IntNull) paramVirtualQty.Value = oParam.VirtualQty; else paramVirtualQty.Value = System.DBNull.Value; 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(paramVirtualQty); return SqlHelper.ExecuteNonQuery(cmd); }
public InventoryInfo LoadInventory(int productSysNo) { string sql = "select * from Inventory where productsysno = " + productSysNo; 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"); InventoryInfo oInfo = new InventoryInfo(); map(oInfo, ds.Tables[0].Rows[0]); return oInfo; }
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(); } }
private void map(InventoryInfo oParam, DataRow tempdr) { //oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); 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.VirtualQty = Util.TrimIntNull(tempdr["VirtualQty"]); }