public int InsertItem(ROItemInfo oParam)
{
string sql = @"INSERT INTO RO_Item
(
ROSysNo, ProductSysNo, ReturnPriceType, ReturnType,
ReturnSysNo, Quantity, Price, Cost,
Weight, Point, RefundCash, RefundPoint
)
VALUES (
@ROSysNo, @ProductSysNo,@ReturnPriceType, @ReturnType,
@ReturnSysNo, @Quantity, @Price, @Cost,
@Weight, @Point, @RefundCash, @RefundPoint
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramROSysNo = new SqlParameter("@ROSysNo", SqlDbType.Int, 4);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4);
SqlParameter paramReturnPriceType = new SqlParameter("@ReturnPriceType", SqlDbType.Int, 4);
SqlParameter paramReturnType = new SqlParameter("@ReturnType", SqlDbType.Int, 4);
SqlParameter paramReturnSysNo = new SqlParameter("@ReturnSysNo", SqlDbType.Int, 4);
SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 4);
SqlParameter paramPrice = new SqlParameter("@Price", SqlDbType.Decimal, 9);
SqlParameter paramCost = new SqlParameter("@Cost", SqlDbType.Decimal, 9);
SqlParameter paramWeight = new SqlParameter("@Weight", SqlDbType.Int, 4);
SqlParameter paramPoint = new SqlParameter("@Point", SqlDbType.Int, 4);
SqlParameter paramRefundCash = new SqlParameter("@RefundCash", SqlDbType.Decimal, 9);
SqlParameter paramRefundPoint = new SqlParameter("@RefundPoint", SqlDbType.Int, 4);
if (oParam.ROSysNo != AppConst.IntNull)
paramROSysNo.Value = oParam.ROSysNo;
else
paramROSysNo.Value = System.DBNull.Value;
if (oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if (oParam.ReturnPriceType != AppConst.IntNull)
paramReturnPriceType.Value = oParam.ReturnPriceType;
else
paramReturnPriceType.Value = System.DBNull.Value;
if (oParam.ReturnType != AppConst.IntNull)
paramReturnType.Value = oParam.ReturnType;
else
paramReturnType.Value = System.DBNull.Value;
if (oParam.ReturnSysNo != AppConst.IntNull)
paramReturnSysNo.Value = oParam.ReturnSysNo;
else
paramReturnSysNo.Value = System.DBNull.Value;
if (oParam.Quantity != AppConst.IntNull)
paramQuantity.Value = oParam.Quantity;
else
paramQuantity.Value = System.DBNull.Value;
if (oParam.Price != AppConst.DecimalNull)
paramPrice.Value = oParam.Price;
else
paramPrice.Value = System.DBNull.Value;
if (oParam.Cost != AppConst.DecimalNull)
paramCost.Value = oParam.Cost;
else
paramCost.Value = System.DBNull.Value;
if (oParam.Weight != AppConst.IntNull)
paramWeight.Value = oParam.Weight;
else
paramWeight.Value = System.DBNull.Value;
if (oParam.Point != AppConst.IntNull)
paramPoint.Value = oParam.Point;
else
paramPoint.Value = System.DBNull.Value;
if (oParam.RefundCash != AppConst.DecimalNull)
paramRefundCash.Value = oParam.RefundCash;
else
paramRefundCash.Value = System.DBNull.Value;
if (oParam.RefundPoint != AppConst.IntNull)
paramRefundPoint.Value = oParam.RefundPoint;
else
paramRefundPoint.Value = System.DBNull.Value;
cmd.Parameters.Add(paramROSysNo);
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramReturnPriceType);
cmd.Parameters.Add(paramReturnType);
cmd.Parameters.Add(paramReturnSysNo);
cmd.Parameters.Add(paramQuantity);
cmd.Parameters.Add(paramPrice);
cmd.Parameters.Add(paramCost);
cmd.Parameters.Add(paramWeight);
cmd.Parameters.Add(paramPoint);
cmd.Parameters.Add(paramRefundCash);
cmd.Parameters.Add(paramRefundPoint);
return SqlHelper.ExecuteNonQuery(cmd);
}
private void map(ROItemInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.ROSysNo = Util.TrimIntNull(tempdr["ROSysNo"]);
oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]);
oParam.ReturnPriceType = Util.TrimIntNull(tempdr["ReturnPriceType"]);
oParam.ReturnType = Util.TrimIntNull(tempdr["ReturnType"]);
oParam.ReturnSysNo = Util.TrimIntNull(tempdr["ReturnSysNo"]);
oParam.Quantity = Util.TrimIntNull(tempdr["Quantity"]);
oParam.Price = Util.TrimDecimalNull(tempdr["Price"]);
oParam.Cost = Util.TrimDecimalNull(tempdr["Cost"]);
oParam.Weight = Util.TrimIntNull(tempdr["Weight"]);
oParam.Point = Util.TrimIntNull(tempdr["Point"]);
oParam.RefundCash = Util.TrimDecimalNull(tempdr["RefundCash"]);
oParam.RefundPoint = Util.TrimIntNull(tempdr["RefundPoint"]);
}
public void UpdateROItem(ROItemInfo roItem)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
new RODac().UpdateItem(roItem);
scope.Complete();
}
}
public ROInfo LoadROfromRMA(int rmaSysNo)
{
string sqlMaster = @"select * from ro_master where status <> " + (int)AppEnum.RMAStatus.Abandon + " and rmasysno =" + rmaSysNo;
DataSet dsMaster = SqlHelper.ExecuteDataSet(sqlMaster);
ROInfo oRO = new ROInfo();
if (Util.HasMoreRow(dsMaster))
{
map(oRO, dsMaster.Tables[0].Rows[0]);
string sqlItem = @"select * from ro_item where rosysno =" + rmaSysNo;
DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem);
if (Util.HasMoreRow(dsItem))
{
foreach (DataRow dr in dsItem.Tables[0].Rows)
{
ROItemInfo oROItem = new ROItemInfo();
map(oROItem, dr);
oRO.ItemHash.Add(oROItem.SysNo, oROItem);
}
}
}
else
oRO = null;
return oRO;
}
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 ROInfo BuildROFromRMA(RMAInfo rmaInfo)
{
ROInfo roInfo = new ROInfo();
SOInfo soInfo = SaleManager.GetInstance().LoadSO(rmaInfo.SOSysNo);
roInfo.RMASysNo = rmaInfo.SysNo;
roInfo.CreateTime = DateTime.Now;
roInfo.CreateUserSysNo = rmaInfo.RMAUserSysNo;
roInfo.StockSysNo = soInfo.StockSysNo;
roInfo.Status = (int)AppEnum.ROStatus.Origin;
roInfo.ReceiveName = soInfo.ReceiveName;
roInfo.ReceivePhone = soInfo.ReceivePhone;
roInfo.ReceiveAddress = soInfo.ReceiveAddress;
roInfo.PointAmt = 0;
roInfo.CashAmt = 0m;
roInfo.RedeemAmt = 0m;
Hashtable leftHash = RMAManager.GetInstance().GetLeftRMAQty(rmaInfo.SOSysNo);
decimal originCashAmt = 0m;
int originPointAmt = 0;
foreach (RMAItemInfo rmaItem in rmaInfo.ItemHash.Values)
{
if (rmaItem.RMAType == (int)AppEnum.RMAType.Return)
{
ROItemInfo roItem = new ROItemInfo();
roItem.ProductSysNo = rmaItem.ProductSysNo;
roItem.Quantity = rmaItem.RMAQty;
if (roItem.Quantity > (int)leftHash[(int)roItem.ProductSysNo])
throw new BizException("Too many item(" + roItem.ProductSysNo + ") to return");
foreach (SOItemInfo soItem in soInfo.ItemHash.Values)
{
if (soItem.ProductSysNo == rmaItem.ProductSysNo)
{
roItem.Price = soItem.Price;
roItem.Point = soItem.Point;
roItem.Cost = soItem.Cost;
roItem.Weight = soItem.Weight;
roItem.RefundPoint = soItem.Point;
roItem.RefundCash = soItem.Price;
roItem.ReturnPriceType = (int)AppEnum.ReturnPriceType.InputPrice;
roItem.ReturnType = (int)AppEnum.ReturnType.SecondHand;
originCashAmt += roItem.Price * roItem.Quantity;
originPointAmt += roItem.Point * roItem.Quantity;
break;
}
}
roInfo.ItemHash.Add(roItem.ProductSysNo, roItem);
}
}
roInfo.OriginPointAmt = originPointAmt;
roInfo.OriginCashAmt = originCashAmt;
return roInfo;
}
