protected void UpdateSupplierInfo(int LogItemID) { JD_LimitPriceApply_LogDal logdal = new JD_LimitPriceApply_LogDal(); JD_LimitPriceApply_Log logmodel = logdal.Detail(LogItemID); if (logmodel != null) { JD_CuPriceDetail model = Detail(logmodel.ParentID); //供应商编号不相等 禁用原来的铜价信息 if (logmodel.SupplierCode != model.SupplierCode) { #region 新增铜价阶梯价记录 JD_CuPriceDetail newmodel = model; newmodel.SupplierCode = logmodel.SupplierCode; newmodel.SupplierName = logmodel.SupplierName; newmodel.EffectiveDate = DateTime.Now; newmodel.IsDeleted = 0; Add(newmodel); #endregion //禁用原来的铜价信息 model.IsDeleted = 1; Update(model); } } }
public void UpdateParentID() { try { string sql = " select distinct TaskID from [dbo].[JD_LimitPriceApply_Log] where ApplyType='3' and ParentID=0 order by TaskID asc "; JD_LimitPriceApply_LogDal limtdal = new JD_LimitPriceApply_LogDal(); DataView dv = DBUtil.Query(sql, connectionString).Tables[0].DefaultView; foreach (DataRowView dr in dv) { string DataSql = string.Format(@"select * from[dbo].[JD_LimitPriceApply_Log] where ApplyType = '3' and ParentID = 0 and TaskID = '{0}'", dr["TaskID"].ToString()); DataView Newdv = DBUtil.Query(DataSql, connectionString).Tables[0].DefaultView; int Count = 0; foreach (DataRowView drchild in Newdv) { //偶数行 更新Parent if (Count % 2 == 1) { int ItemID = Convert.ToInt32(drchild["ItemID"].ToString()); JD_LimitPriceApply_Log limlogNew = limtdal.Detail(ItemID); JD_LimitPriceApply_Log limlog = limtdal.Detail(ItemID - 1); if (limlog != null) { JD_CuPriceDetail detailMol = Detail(limlog.SupplierCode, limlog.ItemCode, limlog.BeginCuPrice, limlog.EndCuPrice, limlog.FromCount, limlog.EndCount); limlogNew.ParentID = detailMol.ItemID; limtdal.Update(limlogNew); } } Count++; } } } catch (Exception ex) { common.WriteLogs("铜价阶梯价服务错误:" + ex.Message); } finally { } }
/// <summary> /// 对象JD_LimitPriceApply_Log明细 /// 编写人:ywk /// 编写日期:2018/7/20 星期五 /// </summary> public JD_LimitPriceApply_Log Detail(int ItemID) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("SELECT * FROM JD_LimitPriceApply_Log WHERE ItemID = @m_ItemID", con); con.Open(); cmd.Parameters.Add(new SqlParameter("@m_ItemID", SqlDbType.Int, 0)).Value = ItemID; JD_LimitPriceApply_Log myDetail = new JD_LimitPriceApply_Log(); SqlDataReader myReader = cmd.ExecuteReader(); if (myReader.Read()) { if (!Convert.IsDBNull(myReader["ItemID"])) { myDetail.ItemID = Convert.ToInt32(myReader["ItemID"]); } if (!Convert.IsDBNull(myReader["CreateTime"])) { myDetail.CreateTime = Convert.ToDateTime(myReader["CreateTime"]); } if (!Convert.IsDBNull(myReader["UpdateTime"])) { myDetail.UpdateTime = Convert.ToDateTime(myReader["UpdateTime"]); } if (!Convert.IsDBNull(myReader["IsUpdate"])) { myDetail.IsUpdate = Convert.ToString(myReader["IsUpdate"]); } if (!Convert.IsDBNull(myReader["Operater"])) { myDetail.Operater = Convert.ToString(myReader["Operater"]); } if (!Convert.IsDBNull(myReader["SupplierName"])) { myDetail.SupplierName = Convert.ToString(myReader["SupplierName"]); } if (!Convert.IsDBNull(myReader["SupplierCode"])) { myDetail.SupplierCode = Convert.ToString(myReader["SupplierCode"]); } if (!Convert.IsDBNull(myReader["BeginCuPrice"])) { myDetail.BeginCuPrice = Convert.ToDecimal(myReader["BeginCuPrice"]); } if (!Convert.IsDBNull(myReader["EndCuPrice"])) { myDetail.EndCuPrice = Convert.ToDecimal(myReader["EndCuPrice"]); } if (!Convert.IsDBNull(myReader["ItemName"])) { myDetail.ItemName = Convert.ToString(myReader["ItemName"]); } if (!Convert.IsDBNull(myReader["ItemCode"])) { myDetail.ItemCode = Convert.ToString(myReader["ItemCode"]); } if (!Convert.IsDBNull(myReader["FromCount"])) { myDetail.FromCount = Convert.ToInt32(myReader["FromCount"]); } if (!Convert.IsDBNull(myReader["EndCount"])) { myDetail.EndCount = Convert.ToInt32(myReader["EndCount"]); } if (!Convert.IsDBNull(myReader["Price"])) { myDetail.Price = Convert.ToDecimal(myReader["Price"]); } if (!Convert.IsDBNull(myReader["FModel"])) { myDetail.FModel = Convert.ToString(myReader["FModel"]); } if (!Convert.IsDBNull(myReader["FUnit"])) { myDetail.FUnit = Convert.ToString(myReader["FUnit"]); } if (!Convert.IsDBNull(myReader["CoinType"])) { myDetail.CoinType = Convert.ToString(myReader["CoinType"]); } if (!Convert.IsDBNull(myReader["MOQ"])) { myDetail.MOQ = Convert.ToString(myReader["MOQ"]); } if (!Convert.IsDBNull(myReader["PPQ"])) { myDetail.PPQ = Convert.ToString(myReader["PPQ"]); } if (!Convert.IsDBNull(myReader["LimitTimes"])) { myDetail.LimitTimes = Convert.ToString(myReader["LimitTimes"]); } if (!Convert.IsDBNull(myReader["ApplyType"])) { myDetail.ApplyType = Convert.ToString(myReader["ApplyType"]); } if (!Convert.IsDBNull(myReader["TaskID"])) { myDetail.TaskID = Convert.ToInt32(myReader["TaskID"]); } if (!Convert.IsDBNull(myReader["PackageInfo"])) { myDetail.PackageInfo = Convert.ToString(myReader["PackageInfo"]); } if (!Convert.IsDBNull(myReader["CostPrice"])) { myDetail.CostPrice = Convert.ToDecimal(myReader["CostPrice"]); } if (!Convert.IsDBNull(myReader["CostCoinType"])) { myDetail.CostCoinType = Convert.ToString(myReader["CostCoinType"]); } if (!Convert.IsDBNull(myReader["ParentID"])) { myDetail.ParentID = Convert.ToInt32(myReader["ParentID"]); } if (!Convert.IsDBNull(myReader["EffectiveDate"])) { myDetail.EffectiveDate = Convert.ToDateTime(myReader["EffectiveDate"]); } if (!Convert.IsDBNull(myReader["PriceRemarks"])) { myDetail.PriceRemarks = Convert.ToString(myReader["PriceRemarks"]); } } myReader.Close(); cmd.Dispose(); con.Close(); con.Dispose(); return(myDetail); }
/// <summary> /// 更新JD_LimitPriceApply_Log对象 /// 编写人:ywk /// 编写日期:2018/7/20 星期五 /// </summary> public void Update(JD_LimitPriceApply_Log model) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("UPDATE JD_LimitPriceApply_Log SET CreateTime = @m_CreateTime,UpdateTime = @m_UpdateTime,IsUpdate = @m_IsUpdate,Operater = @m_Operater,SupplierName = @m_SupplierName,SupplierCode = @m_SupplierCode,BeginCuPrice = @m_BeginCuPrice,EndCuPrice = @m_EndCuPrice,ItemName = @m_ItemName,ItemCode = @m_ItemCode,FromCount = @m_FromCount,EndCount = @m_EndCount,Price = @m_Price,FModel = @m_FModel,FUnit = @m_FUnit,CoinType = @m_CoinType,MOQ = @m_MOQ,PPQ = @m_PPQ,LimitTimes = @m_LimitTimes,ApplyType = @m_ApplyType,TaskID = @m_TaskID,PackageInfo = @m_PackageInfo,CostPrice = @m_CostPrice,CostCoinType = @m_CostCoinType,ParentID = @m_ParentID,EffectiveDate = @m_EffectiveDate,PriceRemarks=@m_PriceRemarks WHERE ItemID = @m_ItemID", con); con.Open(); if (model.CreateTime == null) { cmd.Parameters.Add(new SqlParameter("@m_CreateTime", SqlDbType.DateTime, 0)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_CreateTime", SqlDbType.DateTime, 0)).Value = model.CreateTime; } if (model.UpdateTime == null) { cmd.Parameters.Add(new SqlParameter("@m_UpdateTime", SqlDbType.DateTime, 0)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_UpdateTime", SqlDbType.DateTime, 0)).Value = model.UpdateTime; } if (model.IsUpdate == null) { cmd.Parameters.Add(new SqlParameter("@m_IsUpdate", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_IsUpdate", SqlDbType.NVarChar, 50)).Value = model.IsUpdate; } if (model.Operater == null) { cmd.Parameters.Add(new SqlParameter("@m_Operater", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_Operater", SqlDbType.NVarChar, 50)).Value = model.Operater; } if (model.SupplierName == null) { cmd.Parameters.Add(new SqlParameter("@m_SupplierName", SqlDbType.NVarChar, 100)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_SupplierName", SqlDbType.NVarChar, 100)).Value = model.SupplierName; } if (model.SupplierCode == null) { cmd.Parameters.Add(new SqlParameter("@m_SupplierCode", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_SupplierCode", SqlDbType.NVarChar, 50)).Value = model.SupplierCode; } if (model.BeginCuPrice == null) { cmd.Parameters.Add(new SqlParameter("@m_BeginCuPrice", SqlDbType.Decimal, 18)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_BeginCuPrice", SqlDbType.Decimal, 18)).Value = model.BeginCuPrice; } if (model.EndCuPrice == null) { cmd.Parameters.Add(new SqlParameter("@m_EndCuPrice", SqlDbType.Decimal, 18)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_EndCuPrice", SqlDbType.Decimal, 18)).Value = model.EndCuPrice; } if (model.ItemName == null) { cmd.Parameters.Add(new SqlParameter("@m_ItemName", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_ItemName", SqlDbType.NVarChar, 50)).Value = model.ItemName; } if (model.ItemCode == null) { cmd.Parameters.Add(new SqlParameter("@m_ItemCode", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_ItemCode", SqlDbType.NVarChar, 50)).Value = model.ItemCode; } if (model.FromCount == null) { cmd.Parameters.Add(new SqlParameter("@m_FromCount", SqlDbType.Int, 0)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_FromCount", SqlDbType.Int, 0)).Value = model.FromCount; } if (model.EndCount == null) { cmd.Parameters.Add(new SqlParameter("@m_EndCount", SqlDbType.Int, 0)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_EndCount", SqlDbType.Int, 0)).Value = model.EndCount; } if (model.Price == null) { cmd.Parameters.Add(new SqlParameter("@m_Price", SqlDbType.Decimal, 18)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_Price", SqlDbType.Decimal, 18)).Value = model.Price; } if (model.FModel == null) { cmd.Parameters.Add(new SqlParameter("@m_FModel", SqlDbType.NVarChar, 500)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_FModel", SqlDbType.NVarChar, 500)).Value = model.FModel; } if (model.FUnit == null) { cmd.Parameters.Add(new SqlParameter("@m_FUnit", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_FUnit", SqlDbType.NVarChar, 50)).Value = model.FUnit; } if (model.CoinType == null) { cmd.Parameters.Add(new SqlParameter("@m_CoinType", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_CoinType", SqlDbType.NVarChar, 50)).Value = model.CoinType; } if (model.MOQ == null) { cmd.Parameters.Add(new SqlParameter("@m_MOQ", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_MOQ", SqlDbType.NVarChar, 50)).Value = model.MOQ; } if (model.PPQ == null) { cmd.Parameters.Add(new SqlParameter("@m_PPQ", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_PPQ", SqlDbType.NVarChar, 50)).Value = model.PPQ; } if (model.LimitTimes == null) { cmd.Parameters.Add(new SqlParameter("@m_LimitTimes", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_LimitTimes", SqlDbType.NVarChar, 50)).Value = model.LimitTimes; } if (model.ApplyType == null) { cmd.Parameters.Add(new SqlParameter("@m_ApplyType", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_ApplyType", SqlDbType.NVarChar, 50)).Value = model.ApplyType; } if (model.TaskID == null) { cmd.Parameters.Add(new SqlParameter("@m_TaskID", SqlDbType.Int, 0)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_TaskID", SqlDbType.Int, 0)).Value = model.TaskID; } if (model.PackageInfo == null) { cmd.Parameters.Add(new SqlParameter("@m_PackageInfo", SqlDbType.NVarChar, 500)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_PackageInfo", SqlDbType.NVarChar, 500)).Value = model.PackageInfo; } if (model.CostPrice == null) { cmd.Parameters.Add(new SqlParameter("@m_CostPrice", SqlDbType.Decimal, 18)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_CostPrice", SqlDbType.Decimal, 18)).Value = model.CostPrice; } if (model.CostCoinType == null) { cmd.Parameters.Add(new SqlParameter("@m_CostCoinType", SqlDbType.NVarChar, 50)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_CostCoinType", SqlDbType.NVarChar, 50)).Value = model.CostCoinType; } if (model.ParentID == null) { cmd.Parameters.Add(new SqlParameter("@m_ParentID", SqlDbType.Int, 0)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_ParentID", SqlDbType.Int, 0)).Value = model.ParentID; } if (model.EffectiveDate == null) { cmd.Parameters.Add(new SqlParameter("@m_EffectiveDate", SqlDbType.DateTime, 0)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_EffectiveDate", SqlDbType.DateTime, 0)).Value = model.EffectiveDate; } if (model.PriceRemarks == null) { cmd.Parameters.Add(new SqlParameter("@m_PriceRemarks", SqlDbType.NVarChar, 100)).Value = DBNull.Value; } else { cmd.Parameters.Add(new SqlParameter("@m_PriceRemarks", SqlDbType.NVarChar, 100)).Value = model.PriceRemarks; } cmd.Parameters.Add(new SqlParameter("@m_ItemID", SqlDbType.Int, 0)).Value = model.ItemID; try { cmd.ExecuteNonQuery(); } catch (Exception e) { throw new Exception(e.ToString()); } cmd.Dispose(); con.Close(); con.Dispose(); }
/// <summary> /// 根据限价日志的ID 批量处理 /// </summary> /// <param name="ItemID"></param> /// JD_LimitPriceApply 数据 更新至JD_CuPriceDetail中 public void HandleCuPrice(int ItemID, string FileName, int TaskID) { JD_LimitPriceApply_LogDal logdal = new JD_LimitPriceApply_LogDal(); JD_LimitPriceApply_Log Logmodel = logdal.Detail(ItemID); JD_CuPriceDetailDal detaildal = new JD_CuPriceDetailDal(); JD_CuPriceDetail detailmodel = new JD_CuPriceDetail(); string ErrorMsg = string.Empty; int CuPriceID = 0; try { if (Logmodel != null) { #region 本地集成 switch (Logmodel.ApplyType) { case "1": //新增 #region 新增 //判断是否存在 若存在 更新单价 detailmodel = Detail(Logmodel.SupplierCode, Logmodel.ItemCode, Logmodel.BeginCuPrice, Logmodel.EndCuPrice, Logmodel.FromCount, Logmodel.EndCount); if (detailmodel != null && !string.IsNullOrEmpty(detailmodel.SupplierCode)) { detailmodel.Price = Logmodel.Price; detailmodel.PriceRemarks = Logmodel.PriceRemarks; detailmodel.EffectiveDate = DateTime.Now; Update(detailmodel); } else { //不存在 更新 CuPriceID = detaildal.Add(new JD_CuPriceDetail { ItemName = Logmodel.ItemName, ItemCode = Logmodel.ItemCode, FUnit = Logmodel.FUnit, BeginCuPrice = Logmodel.BeginCuPrice, EndCuPrice = Logmodel.EndCuPrice, FromCount = Logmodel.FromCount, EndCount = Logmodel.EndCount, SupplierName = Logmodel.SupplierName, SupplierCode = Logmodel.SupplierCode, CoinType = Logmodel.CoinType, Price = Logmodel.Price, MOQ = Logmodel.MOQ, PPQ = Logmodel.PPQ, LimitTimes = Logmodel.LimitTimes, FModel = Logmodel.FModel, CreateTime = DateTime.Now, Operater = Logmodel.Operater, PackageInfo = Logmodel.PackageInfo, //CostPrice = Logmodel.CostPrice, //CostCoinType = Logmodel.CostCoinType, CostPrice = Logmodel.Price, CostCoinType = Logmodel.CoinType, PriceRemarks = Logmodel.PriceRemarks, EffectiveDate = DateTime.Now }); Logmodel.ParentID = CuPriceID; } #endregion break; case "2": //删除 #region 除 detailmodel = Detail(Logmodel.SupplierCode, Logmodel.ItemCode, Logmodel.BeginCuPrice, Logmodel.EndCuPrice, Logmodel.FromCount, Logmodel.EndCount); if (detailmodel != null && !string.IsNullOrEmpty(detailmodel.SupplierCode)) { detailmodel.IsDeleted = 1; detailmodel.EffectiveDate = DateTime.Now; Update(detailmodel); } #endregion break; case "3": //涨价 case "4": //降价 //2018-11-19 阶梯价更新时 数量也被修改 所以用ItemID确定唯一性 //detailmodel = Detail(Logmodel.SupplierCode, Logmodel.ItemCode, Logmodel.BeginCuPrice, Logmodel.EndCuPrice, Logmodel.FromCount, Logmodel.EndCount); detailmodel = Detail(Logmodel.ParentID); if (detailmodel != null && !string.IsNullOrEmpty(detailmodel.SupplierCode)) { detailmodel.Price = Logmodel.Price; //2018-10-29 单价和限价不做区分 //detailmodel.CostPrice = Logmodel.CostPrice; detailmodel.CostPrice = Logmodel.Price; //2018-10-18 价格变更中还要包括铜价和数量的变更 detailmodel.BeginCuPrice = Logmodel.BeginCuPrice; detailmodel.EndCuPrice = Logmodel.EndCuPrice; detailmodel.FromCount = Logmodel.FromCount; detailmodel.EndCount = Logmodel.EndCount; detailmodel.EffectiveDate = DateTime.Now; detailmodel.CoinType = Logmodel.CoinType; //detailmodel.MOQ = Logmodel.MOQ; //detailmodel.PPQ = Logmodel.PPQ; //detailmodel.LimitTimes = Logmodel.LimitTimes; Update(detailmodel); } //2018-10-17 如果供应商变更了,需要IsDeleted原来的数据,并新增数据 UpdateSupplierInfo(Logmodel.ItemID); break; case "5": //其他变更 除了价格的变更 //2018-10-17 已不存在其他变更 #region 除了价格的变更 料号和供应商不能变 detailmodel = Detail(Logmodel.SupplierCode, Logmodel.ItemCode, Logmodel.BeginCuPrice, Logmodel.EndCuPrice, Logmodel.FromCount, Logmodel.EndCount); if (detailmodel != null && !string.IsNullOrEmpty(detailmodel.SupplierCode)) { detailmodel.FUnit = Logmodel.FUnit; detailmodel.BeginCuPrice = Logmodel.BeginCuPrice; detailmodel.EndCuPrice = Logmodel.EndCuPrice; detailmodel.FromCount = Logmodel.FromCount; detailmodel.EndCount = Logmodel.EndCount; detailmodel.CoinType = Logmodel.CoinType; detailmodel.MOQ = Logmodel.MOQ; detailmodel.PPQ = Logmodel.PPQ; detailmodel.LimitTimes = Logmodel.LimitTimes; detailmodel.FModel = Logmodel.FModel; detailmodel.Operater = Logmodel.Operater; detailmodel.PackageInfo = Logmodel.PackageInfo; detailmodel.CostCoinType = Logmodel.CostCoinType; Update(detailmodel); } #endregion break; } #endregion #region K3物料集成 UpdateItemInfo(Logmodel.MOQ, Logmodel.PPQ, Logmodel.PackageInfo, Logmodel.ItemCode, ItemID.ToString()); #endregion } } catch (Exception ex) { ErrorMsg = ex.Message; common.WriteLogs(FileName, ItemID.ToString(), "Error:" + ex.Message); } finally { //更新状态 Logmodel.IsUpdate = "1"; Logmodel.UpdateTime = DateTime.Now; logdal.Update(Logmodel); if (!string.IsNullOrEmpty(ErrorMsg)) { common.AddLogQueue(TaskID, "JD_LimitPriceApply_Log", ItemID, "SQL", ErrorMsg, false); } else { common.AddLogQueue(TaskID, "JD_LimitPriceApply_Log", ItemID, "SQL", "操作成功", true); } } }