public int DeleteSOItem(SOItemInfo itemInfo) { string sql = @"Delete from SO_Item where productsysno = @ProductSysNo and sosysno=@SOSysNo"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4); SqlParameter paramSOSysNo = new SqlParameter("@SOSysNo", SqlDbType.Int, 4); if (itemInfo.ProductSysNo != AppConst.IntNull) paramProductSysNo.Value = itemInfo.ProductSysNo; else paramProductSysNo.Value = System.DBNull.Value; if (itemInfo.SOSysNo != AppConst.IntNull) paramSOSysNo.Value = itemInfo.SOSysNo; else paramSOSysNo.Value = System.DBNull.Value; cmd.Parameters.Add(paramProductSysNo); cmd.Parameters.Add(paramSOSysNo); return SqlHelper.ExecuteNonQuery(cmd); }
private void map(SOItemInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.SOSysNo = Util.TrimIntNull(tempdr["SOSysNo"]); oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]); oParam.Quantity = Util.TrimIntNull(tempdr["Quantity"]); oParam.Weight = Util.TrimIntNull(tempdr["Weight"]); oParam.OrderPrice = Util.TrimDecimalNull(tempdr["OrderPrice"]); oParam.Price = Util.TrimDecimalNull(tempdr["Price"]); oParam.Cost = Util.TrimDecimalNull(tempdr["Cost"]); oParam.Point = Util.TrimIntNull(tempdr["Point"]); oParam.PointType = Util.TrimIntNull(tempdr["PointType"]); oParam.DiscountAmt = Util.TrimDecimalNull(tempdr["DiscountAmt"]); oParam.Warranty = Util.TrimNull(tempdr["Warranty"]); oParam.ProductType = Util.TrimIntNull(tempdr["ProductType"]); oParam.GiftSysNo = Util.TrimIntNull(tempdr["GiftSysNo"]); oParam.BaseProductType = Util.TrimIntNull(tempdr["BaseProductType"]); oParam.ExpectQty = Util.TrimIntNull(tempdr["ExpectQty"]); }
private void InsertSOItem(SOItemInfo oParam,SOInfo soInfo) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { new SODac().InsertItem(oParam); InventoryManager.GetInstance().SetOrderQty(soInfo.StockSysNo,oParam.ProductSysNo,oParam.Quantity); scope.Complete(); } }
private bool IfExistSOItem(SOItemInfo itemInfo) { string sql = "select * from so_item where sosysno ="+itemInfo.SOSysNo+" and productsysno="+itemInfo.ProductSysNo; DataSet ds = SqlHelper.ExecuteDataSet(sql); if(Util.HasMoreRow(ds)) return true; else return false; }
public void UpdateSOItemPriceAndQty(SOInfo soInfo,int productSysNo,int qty,decimal price) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { SOItemInfo item = new SOItemInfo(); if(soInfo.ItemHash.ContainsKey(productSysNo)) item = (SOItemInfo)soInfo.ItemHash[productSysNo]; else throw new BizException("No such product in this order"); //�����Ʒ���� int masterQty = 0; int giftQty = 0; if(item.ProductType==(int)AppEnum.SOItemType.ForSale&&item.GiftSysNo!=AppConst.IntNull&&item.Quantity>qty)//�ڼ�������Ʒ������ʱ����Ҫ���� { foreach(SOItemInfo itemInfo in soInfo.ItemHash.Values) { if(itemInfo.ProductType==(int)AppEnum.SOItemType.ForSale&&itemInfo.ProductSysNo!=item.ProductSysNo&&itemInfo.GiftSysNo==item.GiftSysNo) masterQty += itemInfo.Quantity; else if(itemInfo.ProductType==(int)AppEnum.SOItemType.Gift&&itemInfo.ProductSysNo==item.GiftSysNo) giftQty = itemInfo.Quantity; } if((masterQty+qty)<giftQty) throw new BizException("There is too many gifts,please remove some gifts first"); } else if(item.ProductType==(int)AppEnum.SOItemType.Gift&&item.Quantity<qty)//��������Ʒ������ʱ����Ҫ���� { foreach(SOItemInfo itemInfo in soInfo.ItemHash.Values) { if(itemInfo.ProductType==(int)AppEnum.SOItemType.ForSale&&itemInfo.GiftSysNo==item.ProductSysNo) masterQty += itemInfo.Quantity; } if(masterQty<(giftQty+qty)) throw new BizException("Too many gifts added"); } ((SOItemInfo)soInfo.ItemHash[productSysNo]).Quantity = qty; ((SOItemInfo)soInfo.ItemHash[productSysNo]).ExpectQty = qty; ((SOItemInfo)soInfo.ItemHash[productSysNo]).Price = price; //hawkins 2010-4-20 if (soInfo.CouponCode != "") { soInfo.CouponAmt = 0; Icson.Objects.Promotion.CouponInfo oCoupon = Icson.BLL.Promotion.CouponManager.GetInstance().LoadCouponByPwd(soInfo.CouponCode); if (oCoupon != null) { string errstr = Icson.BLL.Promotion.CouponManager.GetInstance().CheckCouponSOByUpdate(oCoupon, soInfo); if (errstr == "") soInfo.CouponAmt = oCoupon.CouponAmt; } } SaleManager.GetInstance().CalcSO(soInfo); if(soInfo.SysNo!=AppConst.IntNull) { SaleManager.GetInstance().UpdateSO(soInfo); } scope.Complete(); } }
public void UpdateSOItem(SOItemInfo oParam) { new SODac().UpdateSOItem(oParam); }
public Hashtable LoadSOProducts(Hashtable sysNoHash,int soItemType) { Hashtable productHash = new Hashtable(5); if(sysNoHash.Count!=0) { string strSysNo = "("; foreach(int sysNo in sysNoHash.Keys) { strSysNo += "'"+sysNo+"',"; } strSysNo = strSysNo.TrimEnd(','); strSysNo += ")"; string sql = @"select null as sysno,null as sosysno,p.sysno as productsysno,null as quantity,null as expectqty ,p.weight,pp.currentprice as price,pp.unitcost as cost,pp.currentprice as orderprice, pp.pointtype,null as discountamt,p.warranty,pp.point,null as producttype,null as giftsysno,p.producttype as baseproducttype from product p left join product_price pp on pp.productsysno = p.sysno where p.sysno in " + strSysNo; if(soItemType==(int)AppEnum.SOItemType.ForSale) sql += " and (pp.clearancesale = "+(int)AppEnum.YNStatus.Yes+" or pp.currentprice>=pp.unitcost) and p.status="+(int)AppEnum.ProductStatus.Show; else sql += " and p.status="+(int)AppEnum.ProductStatus.Valid; DataSet ds = SqlHelper.ExecuteDataSet(sql); if(Util.HasMoreRow(ds)) { foreach(DataRow dr in ds.Tables[0].Rows) { SOItemInfo itemInfo = new SOItemInfo(); this.map(itemInfo,dr); productHash.Add(itemInfo.ProductSysNo,itemInfo); } } } return productHash; }
public SOItemInfo LoadSOProduct(int productSysNo) { string sql = @"select null as sysno,null as sosysno,p.sysno as productsysno,null as quantity,null as expectqty ,p.weight,pp.currentprice as price,pp.unitcost as cost,pp.currentprice as orderprice, pp.pointtype,null as discountamt,p.warranty,pp.point,null as producttype,null as giftsysno,p.producttype as baseproducttype from product p left join product_price pp on pp.productsysno = p.sysno where p.sysno = "+productSysNo; DataSet ds = SqlHelper.ExecuteDataSet(sql); SOItemInfo itemInfo = new SOItemInfo(); if(Util.HasMoreRow(ds)) { this.map(itemInfo,ds.Tables[0].Rows[0]); } else itemInfo = null; return itemInfo; }
public SOItemInfo LoadSOItem(int soItemSysNo) { string sql = "select * from so_item where sysno ="+soItemSysNo; DataSet ds = SqlHelper.ExecuteDataSet(sql); SOItemInfo oSaleOrderItem = new SOItemInfo(); if(Util.HasMoreRow(ds)) map(oSaleOrderItem,ds.Tables[0].Rows[0]); else oSaleOrderItem = null; return oSaleOrderItem; }
public SOInfo LoadSO(int soSysNo) { string sql = "select * from so_master where sysno ="+soSysNo; DataSet ds = SqlHelper.ExecuteDataSet(sql); SOInfo oSaleOrder = new SOInfo(); if(Util.HasMoreRow(ds)) { bool HasService = false; map(oSaleOrder,ds.Tables[0].Rows[0]); //load soitems string sqlItem = "select * from so_item where sosysno ="+oSaleOrder.SysNo; DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem); if(Util.HasMoreRow(dsItem)) { foreach(DataRow dr in dsItem.Tables[0].Rows) { SOItemInfo oSOItem = new SOItemInfo(); map(oSOItem,dr); oSaleOrder.ItemHash.Add(oSOItem.ProductSysNo,oSOItem); if (oSOItem.BaseProductType == (int)AppEnum.ProductType.Service) { HasService = true; } } } //load sosalerules string sqlSaleRule = "select * from so_salerule where sosysno ="+oSaleOrder.SysNo; DataSet dsSaleRule = SqlHelper.ExecuteDataSet(sqlSaleRule); if(Util.HasMoreRow(dsSaleRule)) { foreach(DataRow dr in dsSaleRule.Tables[0].Rows) { SOSaleRuleInfo oSOSaleRule = new SOSaleRuleInfo(); map(oSOSaleRule,dr); oSaleOrder.SaleRuleHash.Add(oSOSaleRule.SysNo,oSOSaleRule); } } //load vat if(oSaleOrder.IsVAT==(int)AppEnum.YNStatus.Yes) { string sqlVAT = "select * from so_valueadded_invoice where sosysno="+oSaleOrder.SysNo; DataSet dsVAT = SqlHelper.ExecuteDataSet(sqlVAT); if(Util.HasMoreRow(dsVAT)) { map(oSaleOrder.VatInfo,dsVAT.Tables[0].Rows[0]); } } //load adways //SOAdwaysInfo adwaysInfo = LoadSOAdwaysBySOSysNo(oSaleOrder.SysNo); //if (adwaysInfo != null) //{ // oSaleOrder.AdwaysInfo = adwaysInfo; //} //load service product if (HasService) { oSaleOrder.ServiceInfo = LoadSOService(oSaleOrder.SysNo); } } else oSaleOrder = null; return oSaleOrder; }
public void ImportSO() { if(!AppConfig.IsImportable) throw new BizException("IsImportable is false"); string sqlMasterChk = "select top 1 * from SO_Master"; DataSet dsMasterChk = SqlHelper.ExecuteDataSet(sqlMasterChk); if(Util.HasMoreRow(dsMasterChk)) throw new BizException("The SO_Master is not empty"); string sqlItemChk = "select top 1 * from so_item"; DataSet dsItemChk = SqlHelper.ExecuteDataSet(sqlItemChk); if(Util.HasMoreRow(dsItemChk)) throw new BizException("The SO_Item is not empty"); string sqlCheckVAT = @"select top 1 * from so_valueAdded_invoice"; DataSet dsCheckVAT = SqlHelper.ExecuteDataSet(sqlCheckVAT); if(Util.HasMoreRow(dsCheckVAT)) throw new BizException("The VAT is not empty"); string sqlCheckSaleRule = @"select top 1 * from so_salerule"; DataSet dsCheckSaleRule = SqlHelper.ExecuteDataSet(sqlCheckSaleRule); if(Util.HasMoreRow(dsCheckSaleRule)) throw new BizException("The SO_SaleRule is not empty"); string sqlDataCleanUp = @"--����so���� --ɾ���������soitem delete from ipp2003..so_item where sysno in (select si.sysno from ipp2003..so_item si left join ipp2003..so_master sm on si.sosysno = sm.sysno where sm.sysno is null) --ɾ��ͬһ����ͬһ��Ʒ���ظ���ϸ delete from ipp2003..so_item where sysno not in (select min(sysno) as sysno from ipp2003..so_item group by sosysno,productsysno having count(*)>1) and sosysno in (select sosysno from ipp2003..so_item group by sosysno,productsysno having count(*)>1) and productsysno in (select productsysno from ipp2003..so_item group by sosysno,productsysno having count(*)>1) --���¶����ֿ���0-->1 update ipp2003..so_master set warehousesysno = 1 where warehousesysno = 0 --���¶����ջ������ţ����Ϊ�������Ϊ�ͻ�ע����Ϣ�е��ջ�������� update ipp2003..so_master set receiveareasysno = wi.shipareasysno from ipp2003..webuser_item wi where wi.webusersysno = ipp2003..so_master.sysno and (ipp2003..so_master.receiveareasysno is null or ipp2003..so_master.receiveareasysno = 1) and wi.shipareasysno is not null "; new ProductSaleTrendDac().Exec(sqlDataCleanUp); string sqlCheckSource = @"select * from ipp2003..so_item si left join ipp2003..so_master sm on si.sosysno = sm.sysno where sm.sysno is null; select sosysno,productsysno,count(*) as number from ipp2003..so_item group by sosysno,productsysno having count(*)>1; select soid,count(*) as number from ipp2003..so_master group by soid having count(*)>1; select * from ipp2003..so_master sm left join ipp2003..stock s on s.sysno = sm.warehousesysno where s.sysno is null;"; DataSet dsCheckSource = SqlHelper.ExecuteDataSet(sqlCheckSource); foreach(DataTable dt in dsCheckSource.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)) { //import master string sqlDefaultUser = @"select sysno from customer where CustomerID = 'UnknownUser'"; DataSet dsDefaultUser = SqlHelper.ExecuteDataSet(sqlDefaultUser); string sqlDefaultPayType = @"select sysno from paytype where paytypeid = '0005'"; DataSet dsDefaultPayType = SqlHelper.ExecuteDataSet(sqlDefaultPayType); string sqlMaster = @"select sm.sysno,soid,status,outtime,sodate as orderdate,deliverytime as DeliveryDate,ispremium,PremiumAmt,(case when sm.customsysno=-999 then " +dsDefaultUser.Tables[0].Rows[0][0]+@" else sm.customsysno end) as customersysno, shipprice, payprice, soamt, st.newsysno as shiptypesysno, isnull(pt.newsysno,"+dsDefaultPayType.Tables[0].Rows[0][0]+@") as paytypesysno,isprintinvoice as isvat,suwh.newsysno as outusersysno,receiveaddress, receivecontact, receivename,receivephone, receivezip, suft.newsysno as freightusersysno, audittime, suat.newsysno as auditusersysno, leaderaudittime as manageraudittime,sula.newsysno as Managerauditusersysno,updatetime,suud.newsysno as updateusersysno,stock.newsysno as stocksysno, memo,note as invoicenote,suam.newsysno as allocatedmansysno,iswholesale,pointpay,cashamt as cashpay,isnull(susa.newsysno,0) as SalesManSysNo,PointAmt, isPrintPackageCover,deliverymemo,discountAmt,null as note, area.newsysno as receiveareasysno, null as receivecellphone from ipp2003..so_master sm left join ippconvert..shiptype st on st.oldsysno = sm.shiptypesysno left join ippconvert..paytype pt on pt.oldsysno = sm.paytypesysno left join ippconvert..sys_user suwh on suwh.oldsysno = sm.warehouseusersysno left join ippconvert..sys_user suft on suft.oldsysno = sm.freightusersysno left join ippconvert..sys_user suat on suat.oldsysno = sm.auditusersysno left join ippconvert..sys_user sula on sula.oldsysno = sm.leaderauditsysno left join ippconvert..sys_user suud on suud.oldsysno = sm.updateusersysno left join ippconvert..stock stock on stock.oldsysno = sm.warehousesysno left join ippconvert..sys_user suam on suam.oldsysno = sm.allocatedmansysno left join ippconvert..sys_user susa on susa.oldsysno = sm.salesmansysno left join ippconvert..area area on area.oldsysno = sm.receiveareasysno"; DataSet dsMaster = SqlHelper.ExecuteDataSet(sqlMaster); if(Util.HasMoreRow(dsMaster)) { foreach(DataRow dr in dsMaster.Tables[0].Rows) { SOInfo oSOMaster = new SOInfo(); this.map(oSOMaster,dr); new SODac().InsertMaster(oSOMaster); } } //import item string sqlItem = @"select 1 as sysno,sosysno,pb.newsysno as productsysno,orderprice,price,(case when cost=-999 then 0 else isnull(cost,0) end) as cost,quantity,weight, (case when returnpoint = -999 then 0 else isnull(returnpoint,0) end) as point, (case when ltrim(rtrim(replace(warranty,CHAR(13)+char(10),'')))='' then '��' else isnull(warranty,'��') end) as warranty ,pointstatus as pointtype,discountamt, getdate() as createtime,0 as producttype,null as giftsysno from ipp2003..so_item si left join ippConvert..productbasic pb on pb.oldsysno = si.productsysno"; DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem); if(Util.HasMoreRow(dsItem)) { foreach(DataRow drItem in dsItem.Tables[0].Rows) { SOItemInfo oSOItem = new SOItemInfo(); this.map(oSOItem,drItem); new SODac().InsertItem(oSOItem); } } //import VAT string sqlVATCleanUp = @"--������Ϣ��ȫ����Ʊ��¼ update ipp2003..sale_value_added_invoice set taxsignnumber = '��Ϣ��ȫ' where ltrim(rtrim(replace(taxsignnumber,CHAR(13)+char(10),''))) = '' update ipp2003..sale_value_added_invoice set companyaddress = '��Ϣ��ȫ' where ltrim(rtrim(replace(companyaddress,CHAR(13)+char(10),''))) = '' update ipp2003..sale_value_added_invoice set companyphone = '��Ϣ��ȫ' where ltrim(rtrim(replace(companyphone,CHAR(13)+char(10),''))) = '' update ipp2003..sale_value_added_invoice set bankAndAccount = '��Ϣ��ȫ' where ltrim(rtrim(replace(bankAndAccount,CHAR(13)+char(10),''))) = '' update ipp2003..sale_value_added_invoice set companyname = '��Ϣ��ȫ' where ltrim(rtrim(replace(companyname,CHAR(13)+char(10),''))) = ''"; new ProductSaleTrendDac().Exec(sqlVATCleanUp); string sqlVAT = @"select 1 as sysno,sosysno,customsysno as customersysno,CompanyName,TaxSignNumber as TaxNum,CompanyAddress,CompanyPhone,BankAndAccount as bankaccount,Memo,getdate() as createtime from ipp2003..sale_value_added_invoice sva"; DataSet dsVAT = SqlHelper.ExecuteDataSet(sqlVAT); if(Util.HasMoreRow(dsVAT)) { foreach(DataRow drVAT in dsVAT.Tables[0].Rows) { SOVATInfo oVAT = new SOVATInfo(); map(oVAT,drVAT); InsertSOVAT(oVAT); } } //import SaleRule string sqlSaleRule = @"select 1 as sysno,sosysno,discountdescription as salerulename,discountamt as discount,discounttimes as times,memo as note, srm.newsysno as salerulesysno,getdate() as createtime from ipp2003..so_discount sd inner join ippconvert..salerulemaster srm on srm.oldsysno = sd.salerulesysno"; DataSet dsSaleRule = SqlHelper.ExecuteDataSet(sqlSaleRule); if(Util.HasMoreRow(dsSaleRule)) { foreach(DataRow drSaleRule in dsSaleRule.Tables[0].Rows) { SOSaleRuleInfo oSaleRule = new SOSaleRuleInfo(); map(oSaleRule,drSaleRule); InsertSOSaleRule(oSaleRule); } } //insert sequence string sqlMaxSysNo = @"select max(sysno) as sysno from so_master"; DataSet dsMaxSysNo = SqlHelper.ExecuteDataSet(sqlMaxSysNo); int n = 0; while(n<Util.TrimIntNull(dsMaxSysNo.Tables[0].Rows[0][0])) { n = SequenceDac.GetInstance().Create("SO_Sequence"); } scope.Complete(); } }
public void AddSOItem(SOItemInfo itemInfo,SOInfo soInfo) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { //��֤��Ʒ���� if(itemInfo.ProductType==(int)AppEnum.SOItemType.Gift) { int masterQty = 0; int giftQty = 0; foreach(SOItemInfo item in soInfo.ItemHash.Values) { if(item.ProductType==(int)AppEnum.SOItemType.ForSale&&item.GiftSysNo==itemInfo.ProductSysNo) masterQty += item.Quantity; else if(item.ProductType==(int)AppEnum.SOItemType.Gift&&item.ProductSysNo==itemInfo.ProductSysNo) giftQty = item.Quantity; } if(masterQty<(giftQty+itemInfo.Quantity)) throw new BizException("There is too many gifts,can't add any more'"); } if(soInfo.SysNo!=AppConst.IntNull) { if( getCurrentSOStatus(soInfo.SysNo)!=(int)AppEnum.SOStatus.Origin ) throw new BizException("The SOStatus is not origin now,can't be edited"); if(!soInfo.ItemHash.ContainsKey(itemInfo.ProductSysNo))//�����в����ڸ���Ʒ��������Item { if(this.IfExistSOItem(itemInfo)) throw new BizException("The same product already exists in this Sale Order."); this.InsertSOItem(itemInfo,soInfo); } } //����Info if(soInfo.ItemHash.ContainsKey(itemInfo.ProductSysNo)) { SOItemInfo tempItem = (SOItemInfo)soInfo.ItemHash[itemInfo.ProductSysNo]; if(tempItem.ProductType!=itemInfo.ProductType) throw new BizException("This product already exists in the order with a different type"); itemInfo.SysNo = tempItem.SysNo; itemInfo.Quantity += tempItem.Quantity; soInfo.ItemHash.Remove(itemInfo.ProductSysNo); } soInfo.ItemHash.Add(itemInfo.ProductSysNo,itemInfo); this.CalcSO(soInfo); if(soInfo.SysNo!=AppConst.IntNull) this.UpdateSO(soInfo); scope.Complete(); } }
public void AddSOGiftItem(SOItemInfo giftInfo,int parentSysNo,SOInfo soInfo) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { SOItemInfo parentItem = (SOItemInfo)soInfo.ItemHash[parentSysNo]; //ɾ��ԭ����Ʒ if(parentItem.GiftSysNo!=AppConst.IntNull&&soInfo.ItemHash.ContainsKey(parentItem.GiftSysNo)) { SOItemInfo originGift = (SOItemInfo)soInfo.ItemHash[parentItem.GiftSysNo]; if(originGift.Quantity<=parentItem.Quantity) this.DeleteSOItem(soInfo,originGift.ProductSysNo); else { this.UpdateSOItemPriceAndQty(soInfo,originGift.ProductSysNo,(originGift.Quantity-parentItem.Quantity),originGift.Price); } } //��������Ʒ��GiftSysNo parentItem.GiftSysNo = giftInfo.ProductSysNo; giftInfo.ProductType = (int)AppEnum.SOItemType.Gift; giftInfo.Price = 0;//��Ʒ�۸�д��Ϊ0 giftInfo.DiscountAmt = 0m; //������Ʒ this.AddSOItem(giftInfo,soInfo); if(soInfo.SysNo!=AppConst.IntNull) { if(getCurrentSOStatus(soInfo.SysNo)!=(int)AppEnum.SOStatus.Origin) throw new BizException("The SOStatus is not origin now,can't be edited"); Hashtable paramHash = new Hashtable(); paramHash.Add("SysNo",parentItem.SysNo); paramHash.Add("GiftSysNo",parentItem.GiftSysNo); this.UpdateSOItem(paramHash); } scope.Complete(); } }
public int UpdateSOItem(SOItemInfo oParam) { string sql = @"UPDATE SO_Item SET Quantity=@Quantity, Weight=@Weight, OrderPrice=@OrderPrice,Price=@Price, Cost=@Cost, Point=@Point, PointType=@PointType, DiscountAmt=@DiscountAmt, Warranty=@Warranty, ProductType=@ProductType, GiftSysNo=@GiftSysNo,BaseProductType=@BaseProductType,ExpectQty=@ExpectQty WHERE SysNo=@SysNo"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4); SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 4); SqlParameter paramWeight = new SqlParameter("@Weight", SqlDbType.Int, 4); SqlParameter paramOrderPrice = new SqlParameter("@OrderPrice", SqlDbType.Decimal, 9); SqlParameter paramPrice = new SqlParameter("@Price", SqlDbType.Decimal, 9); SqlParameter paramCost = new SqlParameter("@Cost", SqlDbType.Decimal, 9); SqlParameter paramPoint = new SqlParameter("@Point", SqlDbType.Int, 4); SqlParameter paramPointType = new SqlParameter("@PointType", SqlDbType.Int, 4); SqlParameter paramDiscountAmt = new SqlParameter("@DiscountAmt", SqlDbType.Decimal, 9); SqlParameter paramWarranty = new SqlParameter("@Warranty", SqlDbType.NVarChar, 500); SqlParameter paramProductType = new SqlParameter("@ProductType", SqlDbType.Int, 4); SqlParameter paramGiftSysNo = new SqlParameter("@GiftSysNo", SqlDbType.Int, 4); SqlParameter paramBaseProductType = new SqlParameter("@BaseProductType", SqlDbType.Int, 4); SqlParameter paramExpectQty = new SqlParameter("@ExpectQty", SqlDbType.Int, 4); if (oParam.SysNo != AppConst.IntNull) paramSysNo.Value = oParam.SysNo; else paramSysNo.Value = System.DBNull.Value; if (oParam.Quantity != AppConst.IntNull) paramQuantity.Value = oParam.Quantity; else paramQuantity.Value = System.DBNull.Value; if (oParam.Weight != AppConst.IntNull) paramWeight.Value = oParam.Weight; else paramWeight.Value = System.DBNull.Value; if (oParam.OrderPrice != AppConst.DecimalNull) paramOrderPrice.Value = oParam.OrderPrice; else paramOrderPrice.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.Point != AppConst.IntNull) paramPoint.Value = oParam.Point; else paramPoint.Value = System.DBNull.Value; if (oParam.PointType != AppConst.IntNull) paramPointType.Value = oParam.PointType; else paramPointType.Value = System.DBNull.Value; if (oParam.DiscountAmt != AppConst.DecimalNull) paramDiscountAmt.Value = oParam.DiscountAmt; else paramDiscountAmt.Value = System.DBNull.Value; if (oParam.Warranty != AppConst.StringNull) paramWarranty.Value = oParam.Warranty; else paramWarranty.Value = System.DBNull.Value; if (oParam.ProductType != AppConst.IntNull) paramProductType.Value = oParam.ProductType; else paramProductType.Value = System.DBNull.Value; if (oParam.GiftSysNo != AppConst.IntNull) paramGiftSysNo.Value = oParam.GiftSysNo; else paramGiftSysNo.Value = System.DBNull.Value; if (oParam.BaseProductType != AppConst.IntNull) paramBaseProductType.Value = oParam.BaseProductType; else paramBaseProductType.Value = System.DBNull.Value; if (oParam.ExpectQty != AppConst.IntNull) paramExpectQty.Value = oParam.ExpectQty; else paramExpectQty.Value = System.DBNull.Value; cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramQuantity); cmd.Parameters.Add(paramWeight); cmd.Parameters.Add(paramOrderPrice); cmd.Parameters.Add(paramPrice); cmd.Parameters.Add(paramCost); cmd.Parameters.Add(paramPoint); cmd.Parameters.Add(paramPointType); cmd.Parameters.Add(paramDiscountAmt); cmd.Parameters.Add(paramWarranty); cmd.Parameters.Add(paramProductType); cmd.Parameters.Add(paramGiftSysNo); cmd.Parameters.Add(paramBaseProductType); cmd.Parameters.Add(paramExpectQty); return SqlHelper.ExecuteNonQuery(cmd); }
public int InsertItem(SOItemInfo oParam) { string sql = @"INSERT INTO SO_Item ( SOSysNo, ProductSysNo, Quantity, Weight, OrderPrice, Price, Cost, Point, PointType, DiscountAmt, Warranty, ProductType, GiftSysNo, BaseProductType,ExpectQty ) VALUES ( @SOSysNo, @ProductSysNo, @Quantity, @Weight, @OrderPrice, @Price, @Cost, @Point, @PointType, @DiscountAmt, @Warranty, @ProductType, @GiftSysNo, @BaseProductType,@ExpectQty );set @SysNo = SCOPE_IDENTITY();"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4); SqlParameter paramSOSysNo = new SqlParameter("@SOSysNo", SqlDbType.Int, 4); SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4); SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 4); SqlParameter paramWeight = new SqlParameter("@Weight", SqlDbType.Int, 4); SqlParameter paramOrderPrice = new SqlParameter("@OrderPrice", SqlDbType.Decimal, 9); SqlParameter paramPrice = new SqlParameter("@Price", SqlDbType.Decimal, 9); SqlParameter paramCost = new SqlParameter("@Cost", SqlDbType.Decimal, 9); SqlParameter paramPoint = new SqlParameter("@Point", SqlDbType.Int, 4); SqlParameter paramPointType = new SqlParameter("@PointType", SqlDbType.Int, 4); SqlParameter paramDiscountAmt = new SqlParameter("@DiscountAmt", SqlDbType.Decimal, 9); SqlParameter paramWarranty = new SqlParameter("@Warranty", SqlDbType.NVarChar, 500); SqlParameter paramProductType = new SqlParameter("@ProductType", SqlDbType.Int, 4); SqlParameter paramGiftSysNo = new SqlParameter("@GiftSysNo", SqlDbType.Int, 4); SqlParameter paramBaseProductType = new SqlParameter("@BaseProductType", SqlDbType.Int, 4); SqlParameter paramExpectQty = new SqlParameter("@ExpectQty", SqlDbType.Int, 4); paramSysNo.Direction = ParameterDirection.Output; if (oParam.SOSysNo != AppConst.IntNull) paramSOSysNo.Value = oParam.SOSysNo; else paramSOSysNo.Value = System.DBNull.Value; if (oParam.ProductSysNo != AppConst.IntNull) paramProductSysNo.Value = oParam.ProductSysNo; else paramProductSysNo.Value = System.DBNull.Value; if (oParam.Quantity != AppConst.IntNull) paramQuantity.Value = oParam.Quantity; else paramQuantity.Value = System.DBNull.Value; if (oParam.Weight != AppConst.IntNull) paramWeight.Value = oParam.Weight; else paramWeight.Value = System.DBNull.Value; if (oParam.OrderPrice != AppConst.DecimalNull) paramOrderPrice.Value = oParam.OrderPrice; else paramOrderPrice.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.Point != AppConst.IntNull) paramPoint.Value = oParam.Point; else paramPoint.Value = System.DBNull.Value; if (oParam.PointType != AppConst.IntNull) paramPointType.Value = oParam.PointType; else paramPointType.Value = System.DBNull.Value; if (oParam.DiscountAmt != AppConst.DecimalNull) paramDiscountAmt.Value = oParam.DiscountAmt; else paramDiscountAmt.Value = System.DBNull.Value; if (oParam.Warranty != AppConst.StringNull) paramWarranty.Value = oParam.Warranty; else paramWarranty.Value = System.DBNull.Value; if (oParam.ProductType != AppConst.IntNull) paramProductType.Value = oParam.ProductType; else paramProductType.Value = System.DBNull.Value; if (oParam.GiftSysNo != AppConst.IntNull) paramGiftSysNo.Value = oParam.GiftSysNo; else paramGiftSysNo.Value = System.DBNull.Value; if (oParam.BaseProductType != AppConst.IntNull) paramBaseProductType.Value = oParam.BaseProductType; else paramBaseProductType.Value = System.DBNull.Value; if (oParam.ExpectQty != AppConst.IntNull) paramExpectQty.Value = oParam.ExpectQty; else paramExpectQty.Value = System.DBNull.Value; cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramSOSysNo); cmd.Parameters.Add(paramProductSysNo); cmd.Parameters.Add(paramQuantity); cmd.Parameters.Add(paramWeight); cmd.Parameters.Add(paramOrderPrice); cmd.Parameters.Add(paramPrice); cmd.Parameters.Add(paramCost); cmd.Parameters.Add(paramPoint); cmd.Parameters.Add(paramPointType); cmd.Parameters.Add(paramDiscountAmt); cmd.Parameters.Add(paramWarranty); cmd.Parameters.Add(paramProductType); cmd.Parameters.Add(paramGiftSysNo); cmd.Parameters.Add(paramBaseProductType); cmd.Parameters.Add(paramExpectQty); return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo); }