private void map(AdjustItemInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.AdjustSysNo = Util.TrimIntNull(tempdr["AdjustSysNo"]);
oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]);
oParam.AdjustQty = Util.TrimIntNull(tempdr["AdjustQty"]);
oParam.AdjustCost = Util.TrimDecimalNull(tempdr["AdjustCost"]);
}
public void UpdateItemQty(AdjustInfo masterInfo, AdjustItemInfo 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.AdjustStatus.Origin )
throw new BizException("status is not origin now, update item qty failed");
//��ȡ������ֵ
AdjustItemInfo oldItemInfo = masterInfo.itemHash[itemInfo.ProductSysNo] as AdjustItemInfo;
/*
* old new
* >0 >0 ����Ҫ����
* <0 <0 new - old = delt
* <0 >0 0 - old = delt
* >0 <0 new - 0 = delt
*/
int newAllocatedQty =0;
int oldQty = oldItemInfo.AdjustQty;
int newQty = itemInfo.AdjustQty;
int deltQty = newQty - oldQty;
if ( oldQty <0 && newQty <0 )
newAllocatedQty = newQty - oldQty;
else if ( oldQty < 0 && newQty > 0 )
newAllocatedQty = 0 - oldQty;
else if ( oldQty >0 && newQty < 0 )
newAllocatedQty = newQty - 0;
//���±����ϸ ( ������ӱ����ϸ������ԣ���Ҫ�����ﴦ��һ�£�
itemInfo.SysNo = oldItemInfo.SysNo;
itemInfo.AdjustSysNo = oldItemInfo.AdjustSysNo;
//��ȡ�ɱ�����Ҫ�Ĵ�ӡ������ã���Ϊ�����ʱ���ǻ���µ�
ProductPriceInfo priceInfo = ProductManager.GetInstance().LoadPrice(itemInfo.ProductSysNo);
itemInfo.AdjustCost = priceInfo.UnitCost;
if ( 1 != new AdjustDac().UpdateItemQtyCost(itemInfo.SysNo, deltQty, itemInfo.AdjustCost))
throw new BizException("expected one-row update failed, update item qty failed");
//����ռ�úͿ��ÿ��
//////////////////////////////////////////////////////////////////////////
if ( newAllocatedQty != 0)
InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemInfo.ProductSysNo, -1*newAllocatedQty);
//���� itemInfo �� masterInfo ע:���ݿ���³ɹ��Ժ�Ÿ�����
masterInfo.itemHash.Remove(itemInfo.ProductSysNo);
masterInfo.itemHash.Add(itemInfo.ProductSysNo, itemInfo);
scope.Complete();
}
}
public AdjustInfo Load(int adjustSysNo)
{
AdjustInfo 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_adjust where sysno = " + adjustSysNo;
DataSet masterDs = SqlHelper.ExecuteDataSet(masterSql);
if ( !Util.HasMoreRow(masterDs))
throw new BizException("there is no this adjust sysno");
masterInfo = new AdjustInfo();
map(masterInfo, masterDs.Tables[0].Rows[0]);
string itemSql = "select * from st_adjust_item where adjustsysno=" + adjustSysNo;
DataSet itemDs = SqlHelper.ExecuteDataSet(itemSql);
if ( Util.HasMoreRow(itemDs))
{
foreach(DataRow dr in itemDs.Tables[0].Rows)
{
AdjustItemInfo item = new AdjustItemInfo();
map(item, dr);
masterInfo.itemHash.Add(item.ProductSysNo, item);
}
}
scope.Complete();
}
return masterInfo;
}
public void InsertItem(AdjustInfo masterInfo, AdjustItemInfo 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.AdjustStatus.Origin )
throw new BizException("status is not origin now, add item failed");
//��ȡ�ɱ�����Ҫ�Ĵ�ӡ������ã���Ϊ�����ʱ���ǻ���µ�
ProductPriceInfo priceInfo = ProductManager.GetInstance().LoadPrice(itemInfo.ProductSysNo);
itemInfo.AdjustCost = priceInfo.UnitCost;
//����item
if ( 1 != new AdjustDac().InsertItem(itemInfo))
throw new BizException("expected one-row update failed, add item failed");
//���¿��
if ( itemInfo.AdjustQty < 0 )
{
InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemInfo.ProductSysNo, -1*itemInfo.AdjustQty);
}
//���� 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_adjust";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table adjust 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.adjustid, stock_con.newsysno as stocksysno,
create_con.newsysno as createusersysno,
audit_con.newsysno as auditusersysno,
audit_con.newsysno as outusersysno,
createtime, audittime,audittime as outtime,
auditstatus, note, '1' as status
from
ipp2003..st_adjust as old,
ippconvert..sys_user as create_con,
ippconvert..sys_user as audit_con,
ippconvert..stock as stock_con
where
old.createusersysno *= create_con.oldsysno and
old.auditusersysno *= audit_con.oldsysno and
old.stocksysno = stock_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 audit
* abandon -1
* origin 0
* verified 1
*/
int newStatus = (int)AppEnum.AdjustStatus.Origin;
int auditStatus = Util.TrimIntNull(dr["auditStatus"]);
if ( auditStatus == -1 )
newStatus = (int)AppEnum.AdjustStatus.Abandon;
else if ( auditStatus == 0 )
newStatus = (int)AppEnum.AdjustStatus.Origin;
else if ( auditStatus == 1)
newStatus = (int)AppEnum.AdjustStatus.OutStock;
else
throw new BizException("old adjust status out of range");
AdjustInfo oInfo = new AdjustInfo();
map(oInfo, dr);
oInfo.Status = newStatus;
if ( new AdjustDac().ImportMaster(oInfo)!= 1)
{
throw new BizException("master expected one row error");
}
//insert item
string sql_item = @"select '0' as sysno,
AdjustSysNo, con_product.newsysno as productsysno, adjustqty, adjustcost
from
ipp2003..St_Adjust_Item sti, ippconvert..productbasic as con_product
where sti.productsysno = con_product.oldsysno and AdjustSysNo=" + oInfo.SysNo;
DataSet ds_item = SqlHelper.ExecuteDataSet(sql_item);
if ( !Util.HasMoreRow(ds_item) )
continue;
foreach(DataRow drItem in ds_item.Tables[0].Rows)
{
AdjustItemInfo oItem = new AdjustItemInfo();
map(oItem, drItem);
if ( oItem.AdjustCost == 0 )
oItem.AdjustCost = AppConst.IntNull;
int resultitem = new AdjustDac().InsertItem(oItem);
if ( resultitem < 1 )
throw new BizException("insert item row < 1");
//�������
if ( oInfo.Status == (int)AppEnum.AdjustStatus.Origin && oItem.AdjustQty < 0 )
{
InventoryManager.GetInstance().SetAvailableQty(oInfo.StockSysNo, oItem.ProductSysNo, -1*oItem.AdjustQty);
}
}
}
string sqlMaxSysNo = "select top 1 sysno from ipp2003..st_adjust 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_Adjust_Sequence");
} while( newSysNo < maxSysNo);
scope.Complete();
}
}
public int InsertItem(AdjustItemInfo oParam)
{
string sql = @"INSERT INTO St_Adjust_Item
(
AdjustSysNo, ProductSysNo, AdjustQty,
AdjustCost
)
VALUES (
@AdjustSysNo, @ProductSysNo, @AdjustQty,
@AdjustCost
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramAdjustSysNo = new SqlParameter("@AdjustSysNo", SqlDbType.Int,4);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4);
SqlParameter paramAdjustQty = new SqlParameter("@AdjustQty", SqlDbType.Int,4);
SqlParameter paramAdjustCost = new SqlParameter("@AdjustCost", SqlDbType.Decimal,9);
paramSysNo.Direction = ParameterDirection.Output;
if ( oParam.AdjustSysNo != AppConst.IntNull)
paramAdjustSysNo.Value = oParam.AdjustSysNo;
else
paramAdjustSysNo.Value = System.DBNull.Value;
if ( oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if ( oParam.AdjustQty != AppConst.IntNull)
paramAdjustQty.Value = oParam.AdjustQty;
else
paramAdjustQty.Value = System.DBNull.Value;
if ( oParam.AdjustCost != AppConst.DecimalNull)
paramAdjustCost.Value = oParam.AdjustCost;
else
paramAdjustCost.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramAdjustSysNo);
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramAdjustQty);
cmd.Parameters.Add(paramAdjustCost);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}