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