public int InsertMaster(TransferInfo oParam)
{
string sql = @"INSERT INTO St_Transfer
(
SysNo, TransferID, StockSysNo, CreateTime,
CreateUserSysNo, AuditTime, AuditUserSysNo, OutTime,
OutUserSysNo, Status, Note
)
VALUES (
@SysNo, @TransferID, @StockSysNo, @CreateTime,
@CreateUserSysNo, @AuditTime, @AuditUserSysNo, @OutTime,
@OutUserSysNo, @Status, @Note
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramTransferID = new SqlParameter("@TransferID", SqlDbType.NVarChar,20);
SqlParameter paramStockSysNo = new SqlParameter("@StockSysNo", SqlDbType.Int,4);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int,4);
SqlParameter paramAuditTime = new SqlParameter("@AuditTime", SqlDbType.DateTime);
SqlParameter paramAuditUserSysNo = new SqlParameter("@AuditUserSysNo", SqlDbType.Int,4);
SqlParameter paramOutTime = new SqlParameter("@OutTime", SqlDbType.DateTime);
SqlParameter paramOutUserSysNo = new SqlParameter("@OutUserSysNo", SqlDbType.Int,4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,200);
if ( oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if ( oParam.TransferID != AppConst.StringNull)
paramTransferID.Value = oParam.TransferID;
else
paramTransferID.Value = System.DBNull.Value;
if ( oParam.StockSysNo != AppConst.IntNull)
paramStockSysNo.Value = oParam.StockSysNo;
else
paramStockSysNo.Value = System.DBNull.Value;
if ( oParam.CreateTime != AppConst.DateTimeNull)
paramCreateTime.Value = oParam.CreateTime;
else
paramCreateTime.Value = System.DBNull.Value;
if ( oParam.CreateUserSysNo != AppConst.IntNull)
paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
else
paramCreateUserSysNo.Value = System.DBNull.Value;
if ( oParam.AuditTime != AppConst.DateTimeNull)
paramAuditTime.Value = oParam.AuditTime;
else
paramAuditTime.Value = System.DBNull.Value;
if ( oParam.AuditUserSysNo != AppConst.IntNull)
paramAuditUserSysNo.Value = oParam.AuditUserSysNo;
else
paramAuditUserSysNo.Value = System.DBNull.Value;
if ( oParam.OutTime != AppConst.DateTimeNull)
paramOutTime.Value = oParam.OutTime;
else
paramOutTime.Value = System.DBNull.Value;
if ( oParam.OutUserSysNo != AppConst.IntNull)
paramOutUserSysNo.Value = oParam.OutUserSysNo;
else
paramOutUserSysNo.Value = System.DBNull.Value;
if ( oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
if ( oParam.Note != AppConst.StringNull)
paramNote.Value = oParam.Note;
else
paramNote.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramTransferID);
cmd.Parameters.Add(paramStockSysNo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramCreateUserSysNo);
cmd.Parameters.Add(paramAuditTime);
cmd.Parameters.Add(paramAuditUserSysNo);
cmd.Parameters.Add(paramOutTime);
cmd.Parameters.Add(paramOutUserSysNo);
cmd.Parameters.Add(paramStatus);
cmd.Parameters.Add(paramNote);
return SqlHelper.ExecuteNonQuery(cmd);
}
private bool isBalance(TransferInfo oMaster)
{
if ( oMaster.itemHash.Count == 0)
return true;
decimal sourceTotal, targetTotal;
sourceTotal = targetTotal = 0;
foreach(TransferItemInfo item in oMaster.itemHash.Values)
{
if ( item.TransferType == (int)AppEnum.TransferItemType.Source )
{
sourceTotal += item.TransferQty * item.TransferCost;
}
else
{
targetTotal += item.TransferQty * item.TransferCost;
}
}
if ( Math.Abs(sourceTotal-targetTotal) < 0.09M )
return true;
else
return false;
}
private void map(TransferInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.TransferID = Util.TrimNull(tempdr["TransferID"]);
oParam.StockSysNo = Util.TrimIntNull(tempdr["StockSysNo"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
oParam.CreateUserSysNo = Util.TrimIntNull(tempdr["CreateUserSysNo"]);
oParam.AuditTime = Util.TrimDateNull(tempdr["AuditTime"]);
oParam.AuditUserSysNo = Util.TrimIntNull(tempdr["AuditUserSysNo"]);
oParam.OutTime = Util.TrimDateNull(tempdr["OutTime"]);
oParam.OutUserSysNo = Util.TrimIntNull(tempdr["OutUserSysNo"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
oParam.Note = Util.TrimNull(tempdr["Note"]);
}
/// <summary>
/// ���������ͳɱ�
/// �����souce�� �ɱ��ӵ�ǰ���ݿ�����ȡ
/// </summary>
/// <param name="masterInfo"></param>
/// <param name="itemInfo"></param>
public void UpdateItem(TransferInfo masterInfo, TransferItemInfo 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.TransferStatus.Origin )
throw new BizException("status is not origin now, update item qty failed");
//��ȡ������ֵ
TransferItemInfo oldItemInfo = masterInfo.itemHash[itemInfo.ProductSysNo] as TransferItemInfo;
int deltQty = itemInfo.TransferQty - oldItemInfo.TransferQty;
//���±����ϸ ( ������ӱ����ϸ������ԣ���Ҫ�����ﴦ��һ�£�
itemInfo.SysNo = oldItemInfo.SysNo;
itemInfo.TransferSysNo = oldItemInfo.TransferSysNo;
itemInfo.TransferType = oldItemInfo.TransferType;
//����source�ijɱ����¿��
if ( itemInfo.TransferType == (int)AppEnum.TransferItemType.Source)
{
ProductPriceInfo priceInfo = ProductManager.GetInstance().LoadPrice(itemInfo.ProductSysNo);
itemInfo.TransferCost = priceInfo.UnitCost;
InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemInfo.ProductSysNo, deltQty);
}
//����item�����ݿ�
if ( 1 != new TransferDac().UpdateItem(itemInfo.SysNo, deltQty, itemInfo.TransferCost))
throw new BizException("expected one-row update failed, update item qty failed");
//���� itemInfo �� masterInfo ע:���ݿ���³ɹ��Ժ�Ÿ�����
masterInfo.itemHash.Remove(itemInfo.ProductSysNo);
masterInfo.itemHash.Add(itemInfo.ProductSysNo, itemInfo);
scope.Complete();
}
}
public void UpdateMaster(TransferInfo oParam)
{
//������Ը���note
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(oParam.SysNo) != (int)AppEnum.TransferStatus.Origin )
throw new BizException("status is not origin now, update failed");
//���� ����, ��ע
Hashtable ht = new Hashtable(3);
ht.Add("SysNo", oParam.SysNo);
ht.Add("Note", oParam.Note);
if ( 1!=new TransferDac().UpdateMaster(ht))
throw new BizException("expected one-row update failed, update failed ");
scope.Complete();
}
}
public TransferInfo Load(int transferSysNo)
{
TransferInfo 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_transfer where sysno = " + transferSysNo;
DataSet masterDs = SqlHelper.ExecuteDataSet(masterSql);
if ( !Util.HasMoreRow(masterDs))
throw new BizException("there is no this transfer sysno");
masterInfo = new TransferInfo();
map(masterInfo, masterDs.Tables[0].Rows[0]);
string itemSql = "select * from st_transfer_item where transfersysno=" + transferSysNo;
DataSet itemDs = SqlHelper.ExecuteDataSet(itemSql);
if ( Util.HasMoreRow(itemDs))
{
foreach(DataRow dr in itemDs.Tables[0].Rows)
{
TransferItemInfo item = new TransferItemInfo();
map(item, dr);
masterInfo.itemHash.Add(item.ProductSysNo, item);
}
}
scope.Complete();
}
return masterInfo;
}
public void InsertItem(TransferInfo masterInfo, TransferItemInfo 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.TransferStatus.Origin )
throw new BizException("status is not origin now, add item failed");
//���¿��
if ( itemInfo.TransferType == (int)AppEnum.TransferItemType.Source )
{
ProductPriceInfo priceInfo = ProductManager.GetInstance().LoadPrice(itemInfo.ProductSysNo);
itemInfo.TransferCost = priceInfo.UnitCost;
InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemInfo.ProductSysNo, itemInfo.TransferQty);
}
//����item�����ݿ�
if ( 1 != new TransferDac().InsertItem(itemInfo))
throw new BizException("expected one-row update failed, add item failed");
//����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_transfer";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table transfer 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.transferid, 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_transfer 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.TransferStatus.Origin;
int auditStatus = Util.TrimIntNull(dr["auditStatus"]);
if ( auditStatus == -1 )
newStatus = (int)AppEnum.TransferStatus.Abandon;
else if ( auditStatus == 0 )
newStatus = (int)AppEnum.TransferStatus.Origin;
else if ( auditStatus == 1)
newStatus = (int)AppEnum.TransferStatus.OutStock;
else
throw new BizException("old transfer status out of range");
TransferInfo oInfo = new TransferInfo();
map(oInfo, dr);
oInfo.Status = newStatus;
if ( new TransferDac().InsertMaster(oInfo)!= 1)
{
throw new BizException("master expected one row error");
}
//insert item
string sql_item = @"select '0' as sysno,
TransferSysNo, con_product.newsysno as productsysno, transferqty, transfercost, transferstatus as transfertype
from
ipp2003..St_Transfer_Item sti, ippconvert..productbasic as con_product
where sti.productsysno = con_product.oldsysno and TransferSysNo=" + oInfo.SysNo;
DataSet ds_item = SqlHelper.ExecuteDataSet(sql_item);
if ( !Util.HasMoreRow(ds_item) )
continue;
foreach(DataRow drItem in ds_item.Tables[0].Rows)
{
TransferItemInfo oItem = new TransferItemInfo();
map(oItem, drItem);
if ( oItem.TransferCost == 0 )
oItem.TransferCost = AppConst.IntNull;
int resultitem = new TransferDac().InsertItem(oItem);
if ( resultitem < 1 )
throw new BizException("insert item row < 1");
if ( oInfo.Status == (int)AppEnum.TransferStatus.Origin && oItem.TransferType == (int)AppEnum.TransferItemType.Source )
{
InventoryManager.GetInstance().SetAvailableQty(oInfo.StockSysNo, oItem.ProductSysNo, oItem.TransferQty);
}
}
}
string sqlMaxSysNo = "select top 1 sysno from ipp2003..st_transfer 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_Transfer_Sequence");
} while( newSysNo < maxSysNo);
scope.Complete();
}
}
public void DeleteItem(TransferInfo masterInfo, int itemProductSysNo)
{
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.TransferStatus.Origin )
throw new BizException("status is not origin now, delete item failed");
//��ȡ������ֵ
TransferItemInfo oldItemInfo = masterInfo.itemHash[itemProductSysNo] as TransferItemInfo;
int deltQty = -1*oldItemInfo.TransferQty;
//����item
if ( 1 != new TransferDac().DeleteItem(oldItemInfo.SysNo))
throw new BizException("expected one-row update failed, delete item qty failed");
//���¿��
if ( oldItemInfo.TransferType == (int)AppEnum.TransferItemType.Source)
{
InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNo, itemProductSysNo, deltQty);
}
//���� masterInfo
masterInfo.itemHash.Remove(itemProductSysNo);
scope.Complete();
}
}
public void Create(TransferInfo oParam)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
oParam.SysNo = SequenceDac.GetInstance().Create("St_Transfer_Sequence");
oParam.TransferID = getTransferID(oParam.SysNo);
//���������¼
int rowsAffected = new TransferDac().InsertMaster(oParam);
if(rowsAffected != 1)
throw new BizException("insert transfer master error");
foreach( TransferItemInfo item in oParam.itemHash.Values)
{
item.TransferSysNo = oParam.SysNo;
//����source�ijɱ�
if ( item.TransferType == (int)AppEnum.TransferItemType.Source)
{
ProductPriceInfo priceInfo = ProductManager.GetInstance().LoadPrice(item.ProductSysNo);
item.TransferCost = priceInfo.UnitCost;
}
rowsAffected = new TransferDac().InsertItem(item);
if ( rowsAffected != 1)
throw new BizException("insert transfer item error");
if ( item.TransferType == (int)AppEnum.TransferItemType.Source )
{//��Ҫռ�ÿ��
InventoryManager.GetInstance().SetAvailableQty(oParam.StockSysNo, item.ProductSysNo, item.TransferQty);
}
}
scope.Complete();
}
}