public void CreateSR(SRInfo srInfo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
//加入退货单主项
srInfo.SysNo = SequenceDac.GetInstance().Create("SR_Sequence");
this.InsertSRMaster(srInfo);
//加入退货单商品明细
int itemCount = 0;
foreach (SRItemInfo item in srInfo.ItemHash.Values)
{
if (item.Quantity > 0) //排除数量为零的情况
{
item.SRSysNo = srInfo.SysNo;
this.InsertSRItem(item, srInfo);
itemCount++;
}
}
if (itemCount == 0) //无退货商品
{
throw new BizException("您选择的退货商品数量为0！");
}
scope.Complete();
}
}
public void AddRO(ROInfo roInfo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
roInfo.SysNo = SequenceDac.GetInstance().Create("RO_Sequence");
roInfo.ROID = this.BuildROID(roInfo.SysNo);
new RODac().InsertMaster(roInfo);
if (roInfo.ItemHash.Count > 0)
{
foreach (ROItemInfo roItem in roInfo.ItemHash.Values)
{
roItem.ROSysNo = roInfo.SysNo;
new RODac().InsertItem(roItem);
}
}
else
{
throw new BizException("Can't create an empty RO'");
}
scope.Complete();
}
}
public void Create(AdjustInfo 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_Adjust_Sequence");
oParam.AdjustID = getAdjustID(oParam.SysNo);
//建立主表记录
int rowsAffected = new AdjustDac().InsertMaster(oParam);
if (rowsAffected != 1)
{
throw new BizException("insert adjust master error");
}
foreach (AdjustItemInfo item in oParam.itemHash.Values)
{
item.AdjustSysNo = oParam.SysNo;
rowsAffected = new AdjustDac().InsertItem(item);
if (rowsAffected != 1)
{
throw new BizException("insert adjust item error");
}
if (item.AdjustQty < 0)
{ //需要占用库存
InventoryManager.GetInstance().SetAvailableQty(oParam.StockSysNo, item.ProductSysNo, -1 * item.AdjustQty);
}
}
scope.Complete();
}
}
public int Insert(ManufacturerInfo oParam)
{
//string sql = " select top 1 sysno from Manufacturer where ManufacturerID = " + Util.ToSqlString(oParam.ManufacturerID);
//DataSet ds = SqlHelper.ExecuteDataSet(sql);
//if ( Util.HasMoreRow(ds) )
// throw new BizException("the same Manufacurer ID exists already");
oParam.SysNo = SequenceDac.GetInstance().Create("Manufacturer_Sequence");
oParam.ManufacturerID = oParam.SysNo.ToString();
return(new ManufacturerDac().Insert(oParam));
}
private void InsertDLMaster(DLInfo 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("DL_Sequence");
new DLDac().Insert(oParam);
scope.Complete();
}
}
public int Insert(StockInfo oParam)
{
string sql = " select top 1 sysno from Stock where StockID = " + Util.ToSqlString(oParam.StockID);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
throw new BizException("the same Stock ID exists already");
}
oParam.SysNo = SequenceDac.GetInstance().Create("Stock_Sequence");
int result = new StockDac().Insert(oParam);
SyncManager.GetInstance().SetDbLastVersion((int)AppEnum.Sync.Stock);
stockHash.Add(oParam.SysNo, oParam);
return(result);
}
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的成本
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();
}
}
public void AddAccessory(RMASendAccessoryInfo rmaInfo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
rmaInfo.SysNo = SequenceDac.GetInstance().Create("RMA_SendAccessory_Sequence");
rmaInfo.SendAccessoryID = this.BuildSendAccessoryID(rmaInfo.SysNo);
rmaInfo.Status = (int)AppEnum.RMASendAccessoryStatus.WaitingAudit;
this.InsertSentAccessory(rmaInfo);
foreach (RMASendAccessoryItemInfo rmaItem in rmaInfo.ItemHash.Values)
{
rmaItem.SendAccessorySysNo = rmaInfo.SysNo;
RMASendAccessoryManager.GetInstance().InsertSentAccessoryItem(rmaItem);
}
scope.Complete();
}
}
public void Create(RMAOutBoundInfo 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("RMA_OutBound_Sequence");
oParam.OutBoundID = getOutBoundID(oParam.SysNo);
new RMAOutBoundDac().InsertMaster(oParam);
int OutBoundInvoiceQty = 0;
foreach (int registerSysNo in oParam.itemHash.Keys)
{
object item = oParam.itemHash[registerSysNo];
this.InsertItem(oParam.SysNo, registerSysNo);
Hashtable htRegister = new Hashtable();
htRegister.Add("SysNo", registerSysNo);
htRegister.Add("OutBoundWithInvoice", Util.TrimIntNull(item.ToString()));
RMARegisterManager.GetInstance().UpdateRegister(htRegister);
if (Util.TrimIntNull(item.ToString()) == (int)AppEnum.OutBoundWithInvoice.SendWithInvoice)
{
OutBoundInvoiceQty = OutBoundInvoiceQty + 1;
}
else
{
OutBoundInvoiceQty = OutBoundInvoiceQty + 0;
}
}
Hashtable htOutBound = new Hashtable();
htOutBound.Add("SysNo", oParam.SysNo);
htOutBound.Add("OutBoundInvoiceQty", OutBoundInvoiceQty);
RMAOutBoundManager.GetInstance().UpdateMaster(htOutBound);
scope.Complete();
}
}
/// <summary>
/// 新增rma单
/// </summary>
/// <param name="rmaInfo"></param>
public void AddRMA(RMAInfo rmaInfo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
if (!this.IfExistOpenedRMA(rmaInfo.SOSysNo))
{
Hashtable leftHash = this.GetLeftRMAQty(rmaInfo.SOSysNo);
foreach (RMAItemInfo item in rmaInfo.ItemHash.Values)
{
if (item.RMAQty > (int)leftHash[item.ProductSysNo])
{
throw new BizException("您填写的保修商品数量超过销售单的实际购买数量，请确认需保修的商品均为此单购买");
}
}
rmaInfo.SysNo = SequenceDac.GetInstance().Create("RMA_Sequence");
rmaInfo.RMAID = this.BuildRMAID(rmaInfo.SysNo);
rmaInfo.SubmitInfo = this.GetRMASnapShot(rmaInfo);
rmaInfo.Status = (int)AppEnum.RMAStatus.Origin;
this.InsertMaster(rmaInfo);
foreach (RMAItemInfo rmaItem in rmaInfo.ItemHash.Values)
{
rmaItem.RMASysNo = rmaInfo.SysNo;
this.InsertItem(rmaItem);
}
}
else
{
throw new BizException("本销售单已经存在一张保修单在处理中，其间您不能再提交新的保修申请，请联系ORS商城客服");
}
scope.Complete();
}
}
public int Insert(CustomerInfo oParam)
{
string sql = @"select case when CustomerID = " + Util.ToSqlString(oParam.CustomerID) + @" then 1 else 0 end as idcheck,
case when email = " + Util.ToSqlString(oParam.Email) + @" then 1 else 0 end as emailcheck
from customer
where CustomerID = " + Util.ToSqlString(oParam.CustomerID)
+ " or Email = "+ Util.ToSqlString(oParam.Email);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
string errorStr = "";
if (Util.TrimIntNull(ds.Tables[0].Rows[0]["idcheck"]) == 1)
{
errorStr = "这个用户名已经被注册";
}
if (Util.TrimIntNull(ds.Tables[0].Rows[0]["emailcheck"]) == 1)
{
if (errorStr != "")
{
errorStr += ",";
}
errorStr += "这个Email已经被注册";
}
if (errorStr == "")
{
//可能有两rows的重复数据，理论上是不应该有的
errorStr = "用户名或Email已经被注册";
}
throw new BizException(errorStr);
}
oParam.SysNo = SequenceDac.GetInstance().Create("Customer_Sequence");
return(new CustomerDac().Insert(oParam));
}
public void ImportCustomer()
{
if (!AppConfig.IsImportable)
{
throw new BizException("IsImportable is false");
}
string sql = "select top 1 * from Customer";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
throw new BizException("The Customer is not empty");
}
string sqlDataCleanUp = @"--删除无明细的主项,保留游客
declare @ret_value as int set @ret_value = 0
begin tran
delete from ipp2003..webuser_master where sysno in (select wm.sysno from ipp2003..webuser_master wm left join ipp2003..webuser_item wi on wi.webusersysno = wm.sysno
where wi.sysno is null) and sysno <> -100
if @@error<>0
set @ret_value = -1
--删除非法用户名
delete from ipp2003..webuser_item where webusersysno in (select sysno from ipp2003..webuser_master where usercode like '%''%')
delete from ipp2003..webuser_master where usercode like '%''%'
--删除同一主项的重复明细
delete from ipp2003..webuser_item where webusersysno in (select webusersysno from ipp2003..webuser_item group by webusersysno having count(*)>1)
and sysno not in (select min(sysno) as sysno from ipp2003..webuser_item group by webusersysno having count(*)>1)
if @@error<>0
set @ret_value = -2
--更新同名主项的usercode=usercode+sysno，保留第一条不变
update ipp2003..webuser_master set usercode = (ltrim(rtrim(usercode)) +'('+ cast(sysno as nvarchar)+')') where sysno in (select wm.sysno
from ipp2003..webuser_master wm inner join (select ltrim(rtrim(usercode)) as usercode ,count(*) as num from ipp2003..webuser_master group by ltrim(rtrim(usercode))
having count(*)>1) a on ltrim(rtrim(wm.usercode)) = a.usercode) and sysno not in (select min(sysno) as sysno from ipp2003..webuser_master
group by ltrim(rtrim(usercode)) having count(*)>1)
if @@error<>0
set @ret_value = -3
if @ret_value <> 0
rollback tran
else
commit tran" ;
new ProductSaleTrendDac().Exec(sqlDataCleanUp);
string sqlCheckData = @"select * from ipp2003..webuser_Master wm left join ipp2003..webuser_item wi on wi.webusersysno = wm.sysno where wi.sysno is null and wm.sysno<> -100;
select ltrim(rtrim(wm.usercode)) as usercode,count(*) as number from ipp2003..webuser_master wm group by ltrim(rtrim(wm.usercode)) having count(*)>1;
select wi.webusersysno,count(*) as number from ipp2003..webuser_item wi group by wi.webusersysno having count(*)>1;" ;
DataSet dsCheckData = SqlHelper.ExecuteDataSet(sqlCheckData);
foreach (DataTable dt in dsCheckData.Tables)
{
if (Util.HasMoreRow(dt))
{
throw new BizException("The DataSource is uncorrect");
}
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sqlSearch = @"select wm.sysno,wm.usercode as CustomerID,(case when wm.userpassword = '' then '8888' else isnull(wm.userpassword,'8888') end) as pwd,
wm.status ,name as CustomerName,gender,email,phone,cellphone,fax,dwelladdress,zip as dwellzip,shipname as receivename,
shipcontact as receivecontact,shipaddress as receiveaddress,shipzip as receivezip,
shipphone as receivephone,shipfax as receivefax,totalscore,validscore,wm.isactive as emailstatus,
cardnumber as cardno ,registertime ,null as receivecellphone,wi.memo as note,
dwellarea.newsysno as dwellareasysno, shiparea.newsysno as receiveareasysno
from ipp2003..webuser_master wm
inner join ipp2003..webuser_item wi on wi.webusersysno = wm.sysno
inner join ippconvert..Area dwellarea on dwellarea.oldsysno = wi.dwellareasysno
inner join ippconvert..Area shiparea on shiparea.oldsysno = wi.shipareasysno" ;
DataSet dsSearch = SqlHelper.ExecuteDataSet(sqlSearch);
if (Util.HasMoreRow(dsSearch))
{
foreach (DataRow dr in dsSearch.Tables[0].Rows)
{
CustomerInfo oCustomer = new CustomerInfo();
this.map(oCustomer, dr);
new Icson.DBAccess.Basic.CustomerDac().Insert(oCustomer);
}
}
//insert sequence
string sqlMaxSysNo = @"select max(sysno) as sysno from customer";
DataSet dsMaxSysNo = SqlHelper.ExecuteDataSet(sqlMaxSysNo);
int n = 0;
while (n < Util.TrimIntNull(dsMaxSysNo.Tables[0].Rows[0][0]))
{
n = SequenceDac.GetInstance().Create("Customer_Sequence");
}
//insert Guest
string sqlInsertGuest = @"INSERT INTO Customer
(
SysNo,CustomerID, Pwd,CustomerName, Status, DwellAreaSysNo,
ReceiveAreaSysNo,TotalScore, ValidScore,EmailStatus
)
VALUES (
-100,'游客','','游客',0, 0,
0, 0,0,0
)" ;
new ProductSaleTrendDac().Exec(sqlInsertGuest);
scope.Complete();
}
}
public void Import()
{
string sql = @"select top 1 * from rma_master;
select top 1 * from rma_item;" ;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
foreach (DataTable dt in ds.Tables)
{
if (Util.HasMoreRow(dt))
{
throw new BizException("The target 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))
{
//Insert RMA_Master
string sqlMaster = @"select rm.sysno, rmaid, sosysno, customsysno as customersysno, approvetime as audittime, suau.newsysno as auditusersysno, status, rmatime as createtime,
note as rmanote,note as ccnote
from ipp2003..rma_master rm
left join ippconvert..sys_user suau on suau.oldsysno = rm.approversysno" ;
DataSet dsMaster = SqlHelper.ExecuteDataSet(sqlMaster);
if (Util.HasMoreRow(dsMaster))
{
foreach (DataRow dr in dsMaster.Tables[0].Rows)
{
RMAInfo oRMA = new RMAInfo();
oRMA.SysNo = Util.TrimIntNull(dr["SysNo"]);
oRMA.RMAID = Util.TrimNull(dr["RMAID"]);
oRMA.SOSysNo = Util.TrimIntNull(dr["SOSysNo"]);
oRMA.CustomerSysNo = Util.TrimIntNull(dr["CustomerSysNo"]);
oRMA.AuditTime = Util.TrimDateNull(dr["AuditTime"]);
oRMA.AuditUserSysNo = Util.TrimIntNull(dr["AuditUserSysNo"]);
switch ((int)dr["Status"])
{
case -1:
oRMA.Status = (int)AppEnum.RMAStatus.Abandon;
break;
case 0:
oRMA.Status = (int)AppEnum.RMAStatus.Origin;
break;
case 1:
oRMA.Status = (int)AppEnum.RMAStatus.Closed;
break;
}
oRMA.CreateTime = Util.TrimDateNull(dr["CreateTime"]);
oRMA.RMANote = Util.TrimNull(dr["RMANote"]);
oRMA.CCNote = Util.TrimNull(dr["CCNote"]);
oRMA.UserStatus = 1;
new RMADac().InsertMaster(oRMA);
}
}
//Insert RMA_Item
string sqlItem = @"select 1 as sysno,rmasysno,case realrmatype when 3 then " + (int)AppEnum.RMAType.Overrule + @" when 1 then " + (int)AppEnum.RMAType.Return
+ @" when 2 then " + (int)AppEnum.RMAType.Maintain + @" end as rmatype, realrmaquantity as rmaqty,
realrmareason as rmadesc,pb.newsysno as productsysno
from ipp2003..rma_item ri
inner join ippconvert..productbasic pb on pb.oldsysno = ri.productsysno" ;
DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem);
if (Util.HasMoreRow(dsItem))
{
foreach (DataRow dr in dsItem.Tables[0].Rows)
{
RMAItemInfo oRMAItem = new RMAItemInfo();
this.map(oRMAItem, dr);
new RMADac().InsertItem(oRMAItem);
}
}
//Insert SnapShot
string sqlSnapShot = @"select rmasysno,rmaid,case rmatype when 0 then " + (int)AppEnum.RMAType.Unsure + @" when 1 then " + (int)AppEnum.RMAType.Return
+ @" when 2 then " + (int)AppEnum.RMAType.Maintain + @" end as rmatype, rmaquantity as rmaqty,
rmareason as rmadesc,pb.newsysno as productsysno,pl.productname,p.productid
from ipp2003..rma_item ri
inner join ippconvert..productbasic pb on pb.oldsysno = ri.productsysno
inner join ipp2003..product_language pl on pl.productsysno = ri.productsysno and pl.languageid = 'cn'
inner join ipp2003..product p on p.sysno = ri.productsysno
inner join ipp2003..rma_master rm on rm.sysno = ri.rmasysno
order by rmasysno" ;
DataSet dsSnapShot = SqlHelper.ExecuteDataSet(sqlSnapShot);
if (Util.HasMoreRow(dsSnapShot))
{
StringBuilder sb = new StringBuilder();
int tempSysNo = 0;
foreach (DataRow dr in dsSnapShot.Tables[0].Rows)
{
if (tempSysNo != (int)dr["RMASysNo"])
{
if (tempSysNo != 0)
{
sb.Append("</table>");
Hashtable updateht = new Hashtable();
updateht.Add("SysNo", tempSysNo);
updateht.Add("SubmitInfo", sb.ToString());
updateht.Add("ReceiveInfo", sb.ToString());
new RMADac().UpdateMaster(updateht);
sb.Remove(0, sb.Length);
}
sb.Append("<table width='100%' border='1' cellpadding='0' cellspacing='0'>");
sb.Append(" <tr>");
sb.Append(" <td>" + dr["RMAID"].ToString() + "</td>");
sb.Append(" <td>商品名称</td>");
sb.Append(" <td>返修数量</td>");
sb.Append(" <td>返修类型</td>");
sb.Append(" <td>返修原因</td>");
sb.Append(" </tr>");
}
sb.Append("<tr>");
sb.Append(" <td>" + dr["ProductID"].ToString() + "</td>");
sb.Append(" <td>" + dr["ProductName"].ToString() + "</td>");
sb.Append(" <td>" + dr["RMAQty"].ToString() + "</td>");
sb.Append(" <td>" + AppEnum.GetRMAType(Util.TrimIntNull(dr["RMAType"])) + "</td>");
sb.Append(" <td>" + dr["RMADesc"].ToString() + "</td>");
sb.Append("</tr>");
tempSysNo = (int)dr["RMASysNo"];
}
}
//Insert Sequence
string sqlMaxSysNo = @"select max(sysno) as sysno from rma_master";
DataSet dsMaxSysNo = SqlHelper.ExecuteDataSet(sqlMaxSysNo);
int n = 0;
while (n < Util.TrimIntNull(dsMaxSysNo.Tables[0].Rows[0][0]))
{
n = SequenceDac.GetInstance().Create("RMA_Sequence");
}
scope.Complete();
}
}
public void Import()
{
string sql = @"select top 1 * from ro_master;
select top 1 * from ro_item;" ;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
foreach (DataTable dt in ds.Tables)
{
if (Util.HasMoreRow(dt))
{
throw new BizException("The target 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))
{
//Insert Master
string sqlDataCleanUp = @"--更新空的退货单创建人为审核人
update ipp2003..ut_master set utusersysno = approversysno where utusersysno =-999" ;
new ProductSaleTrendDac().Exec(sqlDataCleanUp);
string sqlMaster = @"select um.sysno,utid as roid,rmasysno,stock.newsysno as stocksysno,redeemamt,cashamt as origincashamt,pointamt as originpointamt,
(cashamt-redeemamt/10) as cashamt,(pointamt+redeemamt) as pointamt,suct.newsysno as createusersysno,uttime as createtime,
suap.newsysno as auditusersysno ,approvetime as audittime,receiveaddress,receivephone,memo as note,um.status,um.customname as receivename
from ipp2003..ut_master um
left join ippconvert..stock stock on stock.oldsysno = um.warehousesysno
left join ippconvert..sys_user suct on suct.oldsysno = um.utusersysno
left join ippconvert..sys_user suap on suap.oldsysno = um.approversysno" ;
DataSet dsMaster = SqlHelper.ExecuteDataSet(sqlMaster);
if (Util.HasMoreRow(dsMaster))
{
foreach (DataRow dr in dsMaster.Tables[0].Rows)
{
ROInfo oRO = new ROInfo();
oRO.SysNo = Util.TrimIntNull(dr["SysNo"]);
oRO.ROID = Util.TrimNull(dr["ROID"]);
oRO.RMASysNo = Util.TrimIntNull(dr["RMASysNo"]);
oRO.StockSysNo = Util.TrimIntNull(dr["StockSysNo"]);
switch (Util.TrimIntNull(dr["Status"]))
{
case -1: //作废
oRO.Status = (int)AppEnum.ROStatus.Abandon;
break;
case 0: //未审核，可修改
oRO.Status = (int)AppEnum.ROStatus.Origin;
break;
case 1: //待审核
oRO.Status = (int)AppEnum.ROStatus.Origin;
break;
case 2: //已审核
oRO.Status = (int)AppEnum.ROStatus.Audited;
break;
case 3: //已退货
oRO.Status = (int)AppEnum.ROStatus.Returned;
break;
}
oRO.OriginCashAmt = Util.TrimDecimalNull(dr["OriginCashAmt"]);
oRO.OriginPointAmt = Util.TrimIntNull(dr["OriginPointAmt"]);
oRO.RedeemAmt = Util.TrimDecimalNull(dr["RedeemAmt"]);
oRO.CashAmt = Util.TrimDecimalNull(dr["CashAmt"]);
oRO.PointAmt = Util.TrimIntNull(dr["PointAmt"]);
oRO.CreateUserSysNo = Util.TrimIntNull(dr["CreateUserSysNo"]);
oRO.CreateTime = Util.TrimDateNull(dr["CreateTime"]);
oRO.AuditUserSysNo = Util.TrimIntNull(dr["AuditUserSysNo"]);
oRO.AuditTime = Util.TrimDateNull(dr["AuditTime"]);
oRO.ReceiveName = Util.TrimNull(dr["ReceiveName"]);
oRO.ReceiveAddress = Util.TrimNull(dr["ReceiveAddress"]);
oRO.ReceivePhone = Util.TrimNull(dr["ReceivePhone"]);
oRO.Note = Util.TrimNull(dr["Note"]);
new RODac().InsertMaster(oRO);
}
}
//Insert Item
string sqlItem = @"select 1 as sysno ,utsysno as rosysno,pb.newsysno as productsysno,2 as returntype,pb.newsysno as returnsysno,quantity,price,cost,weight,returnpoint as point,
isnull(refundcash,0) as refundcash,isnull(refundpoint,0) as refundpoint
from ipp2003..ut_item ui
left join ippconvert..productbasic pb on pb.oldsysno = ui.productsysno" ;
DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem);
if (Util.HasMoreRow(dsItem))
{
foreach (DataRow dr in dsItem.Tables[0].Rows)
{
ROItemInfo oROItem = new ROItemInfo();
map(oROItem, dr);
new RODac().InsertItem(oROItem);
}
}
//Insert Sequence
string sqlMaxSysNo = @"select max(sysno) as sysno from rma_master";
DataSet dsMaxSysNo = SqlHelper.ExecuteDataSet(sqlMaxSysNo);
int n = 0;
while (n < Util.TrimIntNull(dsMaxSysNo.Tables[0].Rows[0][0]))
{
n = SequenceDac.GetInstance().Create("RO_Sequence");
}
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 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();
}
}