/// <summary> /// WMS设置毛重 /// </summary> /// <param name="skulist"></param> /// <param name="errorskulist"></param> /// <returns></returns> public bool SetGrossWeight(List <SimpleSKUUpdateInfo> skulist, out List <ErrorSimpleSKUUpdateInfo> errorskulist) { errorskulist = new List <ErrorSimpleSKUUpdateInfo>(); bool result = false; using (DbConnection connection = this.database.CreateConnection()) { //打开链接 connection.Open(); //创建事务 DbTransaction Tran = connection.BeginTransaction(); try { foreach (var item in skulist) { //查询sku是否存在 DbCommand sqlStringCommand1 = this.database.GetSqlStringCommand("select COUNT(SKuId) from Ecshop_SKUs A inner join Ecshop_Products B on A.ProductId=B.ProductId where A.SKuId=@SKuId"); this.database.AddInParameter(sqlStringCommand1, "SKuId", DbType.String, item.SkuId); int count = 0; int.TryParse(this.database.ExecuteScalar(sqlStringCommand1, Tran).ToString(), out count); if (count > 0) { DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand("update Ecshop_SKUs set GrossWeight=@GrossWeight where SKuId=@SKuId"); this.database.AddInParameter(sqlStringCommand2, "GrossWeight", DbType.Decimal, item.GrossWeight); this.database.AddInParameter(sqlStringCommand2, "SKuId", DbType.String, item.SkuId); this.database.ExecuteNonQuery(sqlStringCommand2, Tran); } else { ErrorSimpleSKUUpdateInfo errorSimpleSKUUpdateInfo = new ErrorSimpleSKUUpdateInfo(); errorSimpleSKUUpdateInfo.GrossWeight = item.GrossWeight; errorSimpleSKUUpdateInfo.SkuId = item.SkuId; errorskulist.Add(errorSimpleSKUUpdateInfo); } } //提交事务 Tran.Commit(); result = true; } catch (Exception Ex) { //出错回滚 Tran.Rollback(); ErrorLog.Write(Ex.ToString()); } finally { //关闭连接 connection.Close(); } return(result); } }
/// <summary> /// 库存同步 /// </summary> /// <param name="skulist"></param> /// <returns></returns> public bool AdjustSkusStock(List <SimpleSKUUpdateInfo> skulist, out List <ErrorSimpleSKUUpdateInfo> errorskulist) { errorskulist = new List <ErrorSimpleSKUUpdateInfo>(); bool result = false; using (DbConnection connection = this.database.CreateConnection()) { //打开链接 connection.Open(); //创建事务 DbTransaction Tran = connection.BeginTransaction(); try { foreach (var item in skulist) { SKUItem skuItem = new SKUItem(); skuItem = this.GetSkuItem(item.SkuId, Tran); if (skuItem == null) { ErrorSimpleSKUUpdateInfo errorSimpleSKUUpdateInfo = new ErrorSimpleSKUUpdateInfo(); errorSimpleSKUUpdateInfo.Amount = item.Amount; errorSimpleSKUUpdateInfo.SkuId = item.SkuId; errorSimpleSKUUpdateInfo.errorcode = "0001"; errorSimpleSKUUpdateInfo.errordescr = "SKU不存在"; errorskulist.Add(errorSimpleSKUUpdateInfo); } else { DbCommand sqlStringCommand1 = this.database.GetSqlStringCommand("select top 1 B.ConversionRelation from Ecshop_SKUs A inner join Ecshop_Products B on A.ProductId=B.ProductId where A.SKuId=@SKuId"); this.database.AddInParameter(sqlStringCommand1, "SKuId", DbType.String, item.SkuId); int conversionRelation = 0; object objconversionRelation = this.database.ExecuteScalar(sqlStringCommand1, Tran); if (objconversionRelation != null) { int.TryParse(objconversionRelation.ToString(), out conversionRelation); } if (conversionRelation == 0) { conversionRelation = 1; } //if (skuItem.Stock > item.Amount && skuItem.FactStock > item.Amount) int convertCount = item.Amount / conversionRelation; //WMS库存除以转换系数 #region 注释代码 //计算未付款的商品数量 //DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand(" select count(ShipmentQuantity) from dbo.Ecshop_OrderItems A inner join dbo.Ecshop_Orders B on A.OrderId=B.OrderId where B.OrderStatus=1 and A.SkuId=@SKuId"); //this.database.AddInParameter(sqlStringCommand2, "SKuId", DbType.String, item.SkuId); //int waitpaycount = 0; //object objwaitpaidcount = this.database.ExecuteScalar(sqlStringCommand2, Tran); //if (objwaitpaidcount != null) //{ // int.TryParse(objwaitpaidcount.ToString(), out waitpaycount); //} //计算已付款未推送至wms的商品数量 //DbCommand sqlStringCommand3 = this.database.GetSqlStringCommand(" select count(ShipmentQuantity) from dbo.Ecshop_OrderItems A inner join dbo.Ecshop_Orders B on A.OrderId=B.OrderId where B.OrderStatus=2 and B.IsSendWMS=0 and A.SkuId=@SKuId"); //this.database.AddInParameter(sqlStringCommand3, "SKuId", DbType.String, item.SkuId); //int paidcount = 0; //object objpaidcount = this.database.ExecuteScalar(sqlStringCommand3, Tran); //if (objpaidcount != null) //{ // int.TryParse(objpaidcount.ToString(), out paidcount); //} //if (skuItem.Stock + waitpaycount + paidcount > convertCount) //{ //DbCommand sqlStringCommand4 = this.database.GetSqlStringCommand("update Ecshop_SKUs set Stock=@Amount where SKuId=@SKuId"); //this.database.AddInParameter(sqlStringCommand4, "Amount", DbType.Int32, (convertCount > paidcount + waitpaycount) ? (convertCount - paidcount - waitpaycount) : 0); //this.database.AddInParameter(sqlStringCommand4, "SKuId", DbType.String, item.SkuId); //this.database.ExecuteNonQuery(sqlStringCommand4, Tran); //if(skuItem.FactStock+paidcount> convertCount) //{ //DbCommand sqlStringCommand5 = this.database.GetSqlStringCommand("update Ecshop_SKUs set factStock=@Amount where SKuId=@SKuId"); //this.database.AddInParameter(sqlStringCommand5, "Amount", DbType.Int32, (convertCount > paidcount) ? (convertCount - paidcount) : 0); //this.database.AddInParameter(sqlStringCommand5, "SKuId", DbType.String, item.SkuId); //this.database.ExecuteNonQuery(sqlStringCommand5, Tran); //} //} //else //{ // ErrorSimpleSKUUpdateInfo errorSimpleSKUUpdateInfo = new ErrorSimpleSKUUpdateInfo(); // errorSimpleSKUUpdateInfo.Amount = item.Amount; // errorSimpleSKUUpdateInfo.SkuId = item.SkuId; // errorSimpleSKUUpdateInfo.errorcode = "0002"; // errorSimpleSKUUpdateInfo.errordescr = "推送sku库存比站点库存大,未同步库存"; // errorskulist.Add(errorSimpleSKUUpdateInfo); //} #endregion DbCommand sqlStringCommand4 = this.database.GetSqlStringCommand("update Ecshop_SKUs set WMSStock=@Amount where SKuId=@SKuId"); this.database.AddInParameter(sqlStringCommand4, "Amount", DbType.Int32, convertCount); this.database.AddInParameter(sqlStringCommand4, "SKuId", DbType.String, item.SkuId); this.database.ExecuteNonQuery(sqlStringCommand4, Tran); } } //提交事务 Tran.Commit(); result = true; } catch (Exception Ex) { //出错回滚 Tran.Rollback(); ErrorLog.Write(Ex.ToString()); } finally { //关闭连接 connection.Close(); } return(result); } }
/// <summary> /// 入库推送 /// </summary> /// <param name="skulist"></param> /// <returns></returns> public bool AddSkusStock(List <SimpleSKUUpdateInfo> skulist, out List <ErrorSimpleSKUUpdateInfo> errorskulist) { errorskulist = new List <ErrorSimpleSKUUpdateInfo>(); bool result = false; using (DbConnection connection = this.database.CreateConnection()) { //打开链接 connection.Open(); //创建事务 DbTransaction Tran = connection.BeginTransaction(); try { foreach (var item in skulist) { //查询sku是否存在 DbCommand sqlStringCommand1 = this.database.GetSqlStringCommand("select COUNT(SKuId) from Ecshop_SKUs A inner join Ecshop_Products B on A.ProductId=B.ProductId where A.SKuId=@SKuId"); this.database.AddInParameter(sqlStringCommand1, "SKuId", DbType.String, item.SkuId); int count = 0; int.TryParse(this.database.ExecuteScalar(sqlStringCommand1, Tran).ToString(), out count); if (count > 0) { DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand("select top 1 B.ConversionRelation from Ecshop_SKUs A inner join Ecshop_Products B on A.ProductId=B.ProductId where A.SKuId=@SKuId"); this.database.AddInParameter(sqlStringCommand2, "SKuId", DbType.String, item.SkuId); int conversionRelation = 0; object objconversionRelation = this.database.ExecuteScalar(sqlStringCommand2, Tran); if (objconversionRelation != null) { int.TryParse(objconversionRelation.ToString(), out conversionRelation); } if (conversionRelation == 0) { conversionRelation = 1; } DbCommand sqlStringCommand3 = this.database.GetSqlStringCommand("update Ecshop_SKUs set Stock=Stock+@Amount,FactStock=FactStock+@Amount,WMSStock=WMSStock+@Amount where SKuId=@SKuId"); this.database.AddInParameter(sqlStringCommand3, "Amount", DbType.Int32, (item.Amount) / conversionRelation); this.database.AddInParameter(sqlStringCommand3, "SKuId", DbType.String, item.SkuId); this.database.ExecuteNonQuery(sqlStringCommand3, Tran); } else { ErrorSimpleSKUUpdateInfo errorSimpleSKUUpdateInfo = new ErrorSimpleSKUUpdateInfo(); errorSimpleSKUUpdateInfo.Amount = item.Amount; errorSimpleSKUUpdateInfo.SkuId = item.SkuId; errorskulist.Add(errorSimpleSKUUpdateInfo); } } //提交事务 Tran.Commit(); result = true; } catch (Exception Ex) { //出错回滚 Tran.Rollback(); ErrorLog.Write(Ex.ToString()); } finally { //关闭连接 connection.Close(); } return(result); } }