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"]);
}
