private void map(LendReturnInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.LendSysNo = Util.TrimIntNull(tempdr["LendSysNo"]);
oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]);
oParam.ReturnQty = Util.TrimIntNull(tempdr["ReturnQty"]);
oParam.ReturnTime = Util.TrimDateNull(tempdr["ReturnTime"]);
}
public LendInfo Load(int lendSysNo)
{
LendInfo 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_lend where sysno = " + lendSysNo;
DataSet masterDs = SqlHelper.ExecuteDataSet(masterSql);
if ( !Util.HasMoreRow(masterDs))
throw new BizException("there is no this lend sysno");
masterInfo = new LendInfo();
map(masterInfo, masterDs.Tables[0].Rows[0]);
string itemSql = "select * from st_lend_item where lendsysno=" + lendSysNo;
DataSet itemDs = SqlHelper.ExecuteDataSet(itemSql);
if ( Util.HasMoreRow(itemDs))
{
foreach(DataRow dr in itemDs.Tables[0].Rows)
{
LendItemInfo item = new LendItemInfo();
map(item, dr);
masterInfo.itemHash.Add(item.ProductSysNo, item);
}
}
//��Ϊ������¼��pk����product��������sysno ��key
string returnSql = "select * from st_lend_return where lendsysno=" +lendSysNo;
DataSet returnDs = SqlHelper.ExecuteDataSet(returnSql);
if ( Util.HasMoreRow(returnDs))
{
foreach(DataRow dr in returnDs.Tables[0].Rows)
{
LendReturnInfo item = new LendReturnInfo();
map(item, dr);
masterInfo.returnHash.Add(item.SysNo, item);
}
}
scope.Complete();
}
return masterInfo;
}
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_lend";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table lend 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.lendid, stock_con.newsysno as stocksysno,
lender_con.newsysno as lendusersysno,
create_con.newsysno as createusersysno,
audit_con.newsysno as auditusersysno,
out_con.newsysno as outusersysno,
createtime, audittime,outstocktime as outtime,
productstatus, auditstatus, memo as note, '1' as status
from
ipp2003..st_lend as old,
ippconvert..sys_user as lender_con,
ippconvert..sys_user as create_con,
ippconvert..sys_user as audit_con,
ippconvert..sys_user as out_con,
ippconvert..stock as stock_con
where
old.employeesysno = lender_con.oldsysno and
old.createusersysno = create_con.oldsysno and
old.auditusersysno = audit_con.oldsysno and
old.outstockusersysno = out_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 aduit product
* abandon -1 n/a
* origin 0 n/a
* verified 1 n/a
* outstock n/a 1
* returnpartly n/a 2
* returnall n/a 3
*/
int newStatus = (int)AppEnum.LendStatus.Origin;
int auditStatus = Util.TrimIntNull(dr["auditStatus"]);
int productStatus = Util.TrimIntNull(dr["productStatus"]);
if ( auditStatus == -1 )
newStatus = (int)AppEnum.LendStatus.Abandon;
if ( auditStatus == 0 )
newStatus = (int)AppEnum.LendStatus.Origin;
if ( auditStatus == 1)
newStatus = (int)AppEnum.LendStatus.Verified;
if ( productStatus == 1)
newStatus = (int)AppEnum.LendStatus.OutStock;
if ( productStatus == 2)
newStatus = (int)AppEnum.LendStatus.ReturnPartly;
if ( productStatus == 3)
newStatus = (int)AppEnum.LendStatus.ReturnAll;
LendInfo oInfo = new LendInfo();
map(oInfo, dr);
oInfo.Status = newStatus;
if ( new LendDac().ImportMaster(oInfo)!= 1)
{
throw new BizException("master expected one row error");
}
//insert item
string sql_item = @"select '0' as sysno,
LendSysNo, con_product.newsysno as productsysno, lendqty, returnqty, restoretime as ExpectReturnTime
from
ipp2003..St_Lend_Item sli, ippconvert..productbasic as con_product
where sli.productsysno = con_product.oldsysno and LendSysNo=" + oInfo.SysNo;
DataSet ds_item = SqlHelper.ExecuteDataSet(sql_item);
if ( !Util.HasMoreRow(ds_item) )
continue;
foreach(DataRow drItem in ds_item.Tables[0].Rows)
{
LendItemInfo oItem = new LendItemInfo();
map(oItem, drItem);
int resultitem = new LendDac().InsertItem(oItem);
if ( resultitem < 1 )
throw new BizException("insert item row < 1");
if ( Util.TrimIntNull(drItem["returnqty"])!= 0)
{
LendReturnInfo oReturn = new LendReturnInfo();
oReturn.LendSysNo = oItem.LendSysNo;
oReturn.ProductSysNo = oItem.ProductSysNo;
oReturn.ReturnTime = oInfo.OutTime.AddDays(30);
oReturn.ReturnQty = Util.TrimIntNull(drItem["returnqty"]);
int resultReturn = new LendDac().InsertReturn(oReturn);
if ( resultReturn != 1)
throw new BizException("return item one-row expected error");
}
if ( oItem.LendQty - Util.TrimIntNull(drItem["returnqty"]) != 0 )
InventoryManager.GetInstance().SetImportLendQty(oInfo.StockSysNo, oItem.ProductSysNo, oItem.LendQty-Util.TrimIntNull(drItem["returnqty"]));
//�������
if ( oInfo.Status == (int)AppEnum.LendStatus.Origin || oInfo.Status == (int)AppEnum.LendStatus.Verified)
{
InventoryManager.GetInstance().SetAvailableQty(oInfo.StockSysNo, oItem.ProductSysNo, oItem.LendQty);
}
}
}
string sqlMaxSysNo = "select top 1 sysno from ipp2003..st_lend 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_Lend_Sequence");
} while( newSysNo < maxSysNo);
scope.Complete();
}
}
public void InsertReturn(LendInfo masterInfo, LendReturnInfo returnInfo)
{
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.LendStatus.OutStock
&& getCurrentStatus(masterInfo.SysNo) != (int)AppEnum.LendStatus.ReturnPartly)
throw new BizException("status is not outstock or returnpartly now, return failed");
//��ȡ������ֵ
LendItemInfo oldItemInfo = masterInfo.itemHash[returnInfo.ProductSysNo] as LendItemInfo;
int alreadyReturnQty = 0;
foreach(LendReturnInfo item in masterInfo.returnHash.Values)
{
if ( item.ProductSysNo == returnInfo.ProductSysNo )
{
alreadyReturnQty += item.ReturnQty;
}
}
if ( oldItemInfo.LendQty - alreadyReturnQty < returnInfo.ReturnQty )
throw new BizException("you don't need to return so much");
//����item
if ( 1 != new LendDac().InsertReturn(returnInfo))
throw new BizException("expected one-row update failed, update item qty failed");
//���¿��
InventoryManager.GetInstance().SetInStockQty(masterInfo.StockSysNo, returnInfo.ProductSysNo, returnInfo.ReturnQty);
//���� returnInfo �� masterInfo
masterInfo.returnHash.Add(returnInfo.SysNo, returnInfo);
//���õ��š�״̬
Hashtable ht = new Hashtable(2);
ht.Add("SysNo", masterInfo.SysNo);
ht.Add("Status", CalcReturnStatus(masterInfo));
if ( 1!=new LendDac().UpdateMaster(ht))
throw new BizException("expected one-row update failed, cancel outstock failed ");
scope.Complete();
}
}
public int InsertReturn(LendReturnInfo oParam)
{
string sql = @"INSERT INTO St_Lend_Return
(
LendSysNo, ProductSysNo, ReturnQty,
ReturnTime
)
VALUES (
@LendSysNo, @ProductSysNo, @ReturnQty,
@ReturnTime
); set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramLendSysNo = new SqlParameter("@LendSysNo", SqlDbType.Int,4);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4);
SqlParameter paramReturnQty = new SqlParameter("@ReturnQty", SqlDbType.Int,4);
SqlParameter paramReturnTime = new SqlParameter("@ReturnTime", SqlDbType.DateTime);
paramSysNo.Direction = ParameterDirection.Output;
paramLendSysNo.Value = oParam.LendSysNo;
paramProductSysNo.Value = oParam.ProductSysNo;
paramReturnQty.Value = oParam.ReturnQty;
paramReturnTime.Value = oParam.ReturnTime;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramLendSysNo);
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramReturnQty);
cmd.Parameters.Add(paramReturnTime);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
