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; }