public int InsertItem(ShiftItemInfo oParam)
{
string sql = @"INSERT INTO St_Shift_Item
(
ShiftSysNo, ProductSysNo, ShiftQty
)
VALUES (
@ShiftSysNo, @ProductSysNo, @ShiftQty
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramShiftSysNo = new SqlParameter("@ShiftSysNo", SqlDbType.Int,4);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4);
SqlParameter paramShiftQty = new SqlParameter("@ShiftQty", SqlDbType.Int,4);
paramSysNo.Direction = ParameterDirection.Output;
if ( oParam.ShiftSysNo != AppConst.IntNull)
paramShiftSysNo.Value = oParam.ShiftSysNo;
else
paramShiftSysNo.Value = System.DBNull.Value;
if ( oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if ( oParam.ShiftQty != AppConst.IntNull)
paramShiftQty.Value = oParam.ShiftQty;
else
paramShiftQty.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramShiftSysNo);
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramShiftQty);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
public void UpdateItemQty(ShiftInfo masterInfo, ShiftItemInfo 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.ShiftStatus.Origin)
throw new BizException("status is not origin now, update item qty failed");
//��ȡ������ֵ
ShiftItemInfo oldItemInfo = masterInfo.itemHash[itemInfo.ProductSysNo] as ShiftItemInfo;
int deltQty = itemInfo.ShiftQty - oldItemInfo.ShiftQty;
//���±����ϸ ( ������ӱ����ϸ������ԣ���Ҫ�����ﴦ��һ�£�
itemInfo.SysNo = oldItemInfo.SysNo;
itemInfo.ShiftSysNo = oldItemInfo.ShiftSysNo;
if (1 != new ShiftDac().UpdateItemQty(itemInfo.SysNo, deltQty))
throw new BizException("expected one-row update failed, update item qty failed");
//���¿��
InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNoA, itemInfo.ProductSysNo, deltQty);
//���� itemInfo �� masterInfo ע:���ݿ���³ɹ��Ժ�Ÿ�����
masterInfo.itemHash.Remove(itemInfo.ProductSysNo);
masterInfo.itemHash.Add(itemInfo.ProductSysNo, itemInfo);
scope.Complete();
}
}
public ShiftInfo Load(int shiftSysNo)
{
ShiftInfo 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_shift where sysno = " + shiftSysNo;
DataSet masterDs = SqlHelper.ExecuteDataSet(masterSql);
if (!Util.HasMoreRow(masterDs))
throw new BizException("there is no this shift sysno");
masterInfo = new ShiftInfo();
map(masterInfo, masterDs.Tables[0].Rows[0]);
string itemSql = "select * from st_shift_item where shiftsysno=" + shiftSysNo;
DataSet itemDs = SqlHelper.ExecuteDataSet(itemSql);
if (Util.HasMoreRow(itemDs))
{
foreach (DataRow dr in itemDs.Tables[0].Rows)
{
ShiftItemInfo item = new ShiftItemInfo();
map(item, dr);
masterInfo.itemHash.Add(item.ProductSysNo, item);
}
}
scope.Complete();
}
return masterInfo;
}
public void InsertItem(ShiftInfo masterInfo, ShiftItemInfo 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.ShiftStatus.Origin)
throw new BizException("status is not origin now, add item failed");
//��ȡ������ֵ
//����item
if (1 != new ShiftDac().InsertItem(itemInfo))
throw new BizException("expected one-row update failed, add item failed");
//���¿��
InventoryManager.GetInstance().SetAvailableQty(masterInfo.StockSysNoA, itemInfo.ProductSysNo, itemInfo.ShiftQty);
//���� 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_shift";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table shift 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.shiftid, stocka_con.newsysno as stocksysnoa, stockb_con.newsysno as stocksysnob,
create_con.newsysno as createusersysno,
audit_con.newsysno as auditusersysno,
out_con.newsysno as outusersysno,
in_con.newsysno as inusersysno,
createtime, audittime,outstocktime as outtime,instocktime as intime,
auditstatus, productstatus, note, '1' as status
from
ipp2003..st_shift as old,
ippconvert..sys_user as create_con,
ippconvert..sys_user as audit_con,
ippconvert..sys_user as out_con,
ippconvert..sys_user as in_con,
ippconvert..stock as stocka_con,
ippconvert..Stock as stockb_con
where
old.createusersysno *= create_con.oldsysno and
old.auditusersysno *= audit_con.oldsysno and
old.outstockusersysno *= out_con.oldsysno and
old.instockusersysno *= in_con.oldsysno and
old.stocksysnoa = stocka_con.oldsysno and
old.stocksysnob = stockb_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 aduit product
* abandon -1 n/a
* origin 0 n/a
* verified 1 n/a
*
* StillInA n/a 0
* OnTheRoad n/a 1
* AlreadyInB n/a 2
*/
int newStatus = (int)AppEnum.ShiftStatus.Origin;
int auditStatus = Util.TrimIntNull(dr["auditStatus"]);
int productStatus = Util.TrimIntNull(dr["productStatus"]);
if (auditStatus == -1)
newStatus = (int)AppEnum.ShiftStatus.Abandon;
if (auditStatus == 0)
newStatus = (int)AppEnum.ShiftStatus.Origin;
if (auditStatus == 0)
newStatus = (int)AppEnum.ShiftStatus.Verified;
if (productStatus == 1)
newStatus = (int)AppEnum.ShiftStatus.OutStock;
if (productStatus == 2)
newStatus = (int)AppEnum.ShiftStatus.InStock;
ShiftInfo oInfo = new ShiftInfo();
map(oInfo, dr);
oInfo.Status = newStatus;
if (new ShiftDac().InsertMaster(oInfo) != 1)
{
throw new BizException("master expected one row error");
}
//insert item
string sql_item = @"select '0' as sysno,
ShiftSysNo, con_product.newsysno as productsysno, shiftqty
from
ipp2003..St_Shift_Item si, ippconvert..productbasic as con_product
where si.productsysno = con_product.oldsysno and ShiftSysNo=" + oInfo.SysNo;
DataSet ds_item = SqlHelper.ExecuteDataSet(sql_item);
if (!Util.HasMoreRow(ds_item))
continue;
foreach (DataRow drItem in ds_item.Tables[0].Rows)
{
ShiftItemInfo oItem = new ShiftItemInfo();
map(oItem, drItem);
int resultitem = new ShiftDac().InsertItem(oItem);
if (resultitem < 1)
throw new BizException("insert item row < 1");
//�������
if (oInfo.Status == (int)AppEnum.ShiftStatus.Origin || oInfo.Status == (int)AppEnum.ShiftStatus.Verified)
{
InventoryManager.GetInstance().SetAvailableQty(oInfo.StockSysNoA, oItem.ProductSysNo, oItem.ShiftQty);
}
}
}
string sqlMaxSysNo = "select top 1 sysno from ipp2003..st_shift 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_Shift_Sequence");
} while (newSysNo < maxSysNo);
scope.Complete();
}
}
private void map(ShiftItemInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.ShiftSysNo = Util.TrimIntNull(tempdr["ShiftSysNo"]);
oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]);
oParam.ShiftQty = Util.TrimIntNull(tempdr["ShiftQty"]);
}
