/// <summary> /// 更新销售计划 /// </summary> /// <returns></returns> public static bool Update(Hashtable ht,SellPlanModel sellPlanModel, SellPlanDetailModel sellPlanDetailModel, string strDetailAction, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; if (IsUpdate(sellPlanModel.PlanNo)) { TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { UpdateOrder(sellPlanModel, tran); #region 拓展属性 GetExtAttrCmd(sellPlanModel, ht, tran); #endregion //明细操作的类型 switch (strDetailAction) { case "1"://无操作 break; case "2"://添加新明细 InsertOrderDetail(sellPlanDetailModel, tran); break; case "3"://更新明细 UpdateOrderDetail(sellPlanDetailModel, tran); break; case "4"://删除明细 DelOrderDetail(sellPlanDetailModel, tran); break; default: break; } tran.Commit(); strMsg = "保存成功!"; isSucc = true; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "非制单状态的未提交审批、审批未通过或撤销审批计划不可修改!"; } return isSucc; }
/// <summary> /// 保存销售计划 /// </summary> /// <returns></returns> public static bool Save(Hashtable ht,SellPlanModel sellPlanModel, SellPlanDetailModel sellPlanDetailModel, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; //判断计划编号是否存在 if (NoIsExist(sellPlanModel.PlanNo)) { TransactionManager tran = new TransactionManager(); tran.BeginTransaction(); try { InsertOrder(sellPlanModel, tran); #region 拓展属性 GetExtAttrCmd(sellPlanModel, ht, tran); #endregion if (sellPlanDetailModel.PlanNo != null) { InsertOrderDetail(sellPlanDetailModel, tran); } tran.Commit(); isSucc = true; strMsg = "保存成功!"; } catch (Exception ex) { tran.Rollback(); strMsg = "保存失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "该编号已被使用,请输入未被使用的编号!"; } return isSucc; }
/// <summary> ///总结计划 /// </summary> /// <returns></returns> public static bool SummarizeOrder(SellPlanDetailModel model, out string strMsg) { bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; strMsg = ""; try { isSucc = SellPlanDBHelper.SummarizeOrder(model, out strMsg); //设置操作成功标识 remark = "总结成功"; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, "2031001"); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } SellLogCommon.InsertLog(model.PlanNo, "2031001", "officedba.SellPlanDetail", remark, ConstUtil.LOG_PROCESS_UPDATE); return isSucc; }
/// <summary> /// 更新销售发货单 /// </summary> /// <returns></returns> public static bool Update(Hashtable ht,SellPlanModel sellPlanModel, SellPlanDetailModel sellPlanDetail, string strDetailAction, out string strMsg) { bool isSucc = false;//是否添加成功 //定义变量 string remark = string.Empty; strMsg = ""; try { isSucc = SellPlanDBHelper.Update(ht,sellPlanModel, sellPlanDetail, strDetailAction, out strMsg); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_SUCCESS; } catch (Exception ex) { //输出日志 SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, "2031001"); //设置操作成功标识 remark = ConstUtil.LOG_PROCESS_FAILED; } SellLogCommon.InsertLog(sellPlanModel.PlanNo, "2031001", "officedba.SellPlan", remark, ConstUtil.LOG_PROCESS_UPDATE); return isSucc; }
/// <summary> /// 为明细表插入数据 /// </summary> /// <param name="sellSendDetailModellList"></param> /// <param name="tran"></param> private static void InsertOrderDetail(SellPlanDetailModel model, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into officedba.SellPlanDetail("); strSql.Append("CompanyCD,PlanNo,DetailType,ParentID,DetailID,DetailTotal,MinDetailotal,IsSummarize)"); strSql.Append(" values ("); strSql.Append("@CompanyCD,@PlanNo,@DetailType,@ParentID,@DetailID,@DetailTotal,@MinDetailotal,'0')"); #region 参数 SqlParameter[] parameters = { new SqlParameter("@CompanyCD", SqlDbType.VarChar,8), new SqlParameter("@PlanNo", SqlDbType.VarChar,50), new SqlParameter("@DetailType", SqlDbType.Char,1), new SqlParameter("@ParentID", SqlDbType.Int,4), new SqlParameter("@DetailID", SqlDbType.Int,4), new SqlParameter("@DetailTotal", SqlDbType.Decimal,13), new SqlParameter("@MinDetailotal", SqlDbType.Decimal,13)}; parameters[0].Value = model.CompanyCD; parameters[1].Value = model.PlanNo; parameters[2].Value = model.DetailType; parameters[3].Value = model.ParentID; parameters[4].Value = model.DetailID; parameters[5].Value = model.DetailTotal; parameters[6].Value = model.MinDetailotal; foreach (SqlParameter para in parameters) { if (para.Value == null) { para.Value = DBNull.Value; } } #endregion SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters); }
/// <summary> /// 删除子表数据 /// </summary> /// <param name="sellBackModel"></param> /// <param name="tran"></param> private static void DelOrderDetail(SellPlanDetailModel model, TransactionManager tran) { string strSql = string.Empty; strSql += " DECLARE @Id Int "; strSql += " SET @Id = " + model.ID.ToString() + "; WITH RootNodeCTE(Id, ParentId) AS "; strSql += " (SELECT Id, ParentId "; strSql += " FROM officedba.SellPlanDetail "; strSql += " WHERE ParentId IN (@Id) "; strSql += " UNION ALL "; strSql += " SELECT officedba.SellPlanDetail.Id, officedba.SellPlanDetail.ParentId "; strSql += " FROM RootNodeCTE INNER JOIN "; strSql += " officedba.SellPlanDetail ON RootNodeCTE.Id = officedba.SellPlanDetail.ParentId "; strSql += " ) "; strSql += " SELECT * "; strSql += " FROM RootNodeCTE "; DataTable dt = SqlHelper.ExecuteSql(strSql);//当前删除节点的所有子节点 if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, "DELETE FROM officedba.SellPlanDetail WHERE ID=" + dt.Rows[i]["ID"].ToString(), null); } } strSql = "DELETE FROM officedba.SellPlanDetail WHERE ID= " + model.ID.ToString(); SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), null); }
/// <summary> /// 总结计划 /// </summary> /// <param name="OrderNO">计划编号</param> /// <param name="strMsg">操作返回的信息</param> /// <returns></returns> public static bool SummarizeOrder(SellPlanDetailModel model, out string strMsg) { bool isSucc = false;//是否添加成功 strMsg = ""; //判断计划是否为执行状态,非执行状态不能总结 if (isHandle(model.PlanNo, "2")) { StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.SellPlanDetail set "); strSql.Append("SummarizeDate=@SummarizeDate,"); strSql.Append("SummarizeNote=@SummarizeNote,"); strSql.Append("Summarizer=@Summarizer,"); strSql.Append("AimRealResult=@AimRealResult,"); strSql.Append("AddOrCut=@AddOrCut,"); strSql.Append("Difference=@Difference,"); strSql.Append("CompletePercent=@CompletePercent,"); strSql.Append("IsSummarize=@IsSummarize"); strSql.Append(" where ID=@ID "); #region 参数 SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int,4), new SqlParameter("@SummarizeDate", SqlDbType.DateTime), new SqlParameter("@SummarizeNote", SqlDbType.VarChar,1024), new SqlParameter("@Summarizer", SqlDbType.Int,4), new SqlParameter("@AimRealResult", SqlDbType.VarChar,1024), new SqlParameter("@AddOrCut", SqlDbType.Char,1), new SqlParameter("@Difference", SqlDbType.VarChar,100), new SqlParameter("@CompletePercent", SqlDbType.Decimal,5), new SqlParameter("@IsSummarize", SqlDbType.Char,1)}; parameters[0].Value = model.ID; parameters[1].Value = model.SummarizeDate; parameters[2].Value = model.SummarizeNote; parameters[3].Value = model.Summarizer; parameters[4].Value = model.AimRealResult; parameters[5].Value = model.AddOrCut; parameters[6].Value = model.Difference; parameters[7].Value = model.CompletePercent; parameters[8].Value = model.IsSummarize; foreach (SqlParameter para in parameters) { if (para.Value == null) { para.Value = DBNull.Value; } } #endregion try { SqlHelper.ExecuteTransSql(strSql.ToString(), parameters); strMsg = "总结成功!"; isSucc = true; } catch (Exception ex) { strMsg = "总结失败,请联系系统管理员!"; throw ex; } } else { isSucc = false; strMsg = "只有执行状态的计划才可以进行总结!"; } return isSucc; }
/// <summary> /// 跟新子表数据 /// </summary> /// <param name="sellBackModel"></param> /// <param name="tran"></param> private static void UpdateOrderDetail(SellPlanDetailModel model, TransactionManager tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("update officedba.SellPlanDetail set "); strSql.Append("DetailType=@DetailType,"); strSql.Append("DetailID=@DetailID,"); strSql.Append("DetailTotal=@DetailTotal,"); strSql.Append("MinDetailotal=@MinDetailotal"); strSql.Append(" where ID=@ID "); #region 参数 SqlParameter[] parameters = { new SqlParameter("@DetailType", SqlDbType.Char,1), new SqlParameter("@DetailID", SqlDbType.Int,4), new SqlParameter("@DetailTotal", SqlDbType.Decimal,13), new SqlParameter("@MinDetailotal", SqlDbType.Decimal,13), new SqlParameter("@ID", SqlDbType.Int,4)}; parameters[0].Value = model.DetailType; parameters[1].Value = model.DetailID; parameters[2].Value = model.DetailTotal; parameters[3].Value = model.MinDetailotal; parameters[4].Value = model.ID; foreach (SqlParameter para in parameters) { if (para.Value == null) { para.Value = DBNull.Value; } } #endregion SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters); }