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