/// <summary> /// 更新物料需求计划和物料需求计划明细信息 /// </summary> /// <param name="model"></param> /// <param name="UpdateID"></param> /// <returns></returns> public static bool UpdateMRPInfo(MRPModel model, Hashtable ht, string UpdateID) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; try { bool succ = false; string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; LogInfoModel logModel = InitLogInfo(model.MRPNo, 0); logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; succ = MRPDBHelper.UpdateMRPInfo(model, ht,loginUserID, UpdateID); if (!succ) logModel.Remark = ConstUtil.LOG_PROCESS_FAILED; else logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS; LogDBHelper.InsertLog(logModel); return succ; } catch (Exception ex) { WriteSystemLog(userInfo, 0, ex); return false; } }
/// <summary> /// 取消确认 /// </summary> /// <param name="model"></param> /// <param name="isConfirm"></param> /// <returns></returns> public static bool CancelConfirmMRP(MRPModel model, int BillTypeFlag, int BillTypeCode) { string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; return MRPDBHelper.CancelConfirmOperate(model, BillTypeFlag, BillTypeCode, loginUserID); }
/// <summary> /// 确认或结单 /// </summary> /// <param name="model"></param> /// <param name="isConfirm"></param> /// <returns></returns> public static bool ConfirmOrCompleteMRP(MRPModel model, int OperateType) { string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; //string loginUserID = "admin";//[待修改] return MRPDBHelper.ConfirmOrCompleteMRP(model, loginUserID, OperateType); }
/// <summary> /// 主生产计划唯一性验证 /// </summary> /// <param name="ParentCode">上级编码</param> /// <returns>大于0:已经有物料需求计划引用该计划了,否则无物料需求计划引用该计划</returns> public static int PlanCount(MRPModel model) { try { return MRPDBHelper.PlanCount(model); } catch (Exception ex) { throw ex; } }
/// <summary> /// MRP明细信息 /// </summary> /// <returns>DataTable</returns> public static DataTable GetMRPDetailInfo(MRPModel model) { try { return MRPDBHelper.GetMRPDetailInfo(model); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 查询主生产计划单信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static DataTable GetMRPListBycondition(MRPModel model, int FlowStatus, int BillTypeFlag, int BillTypeCode, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { return MRPDBHelper.GetMRPListBycondition(model, FlowStatus, BillTypeFlag, BillTypeCode, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 主生产计划唯一性验证 /// </summary> /// <param name="ParentCode">上级编码</param> /// <returns>大于0:已经有物料需求计划引用该计划了,否则无物料需求计划引用该计划</returns> public static int PlanCount(MRPModel model) { string sql = string.Empty; if (model.ID > 0) { sql = "select Count(ID) from officedba.MRP where CompanyCD=@CompanyCD and PlanID=@PlanID and ID<>@ID"; SqlParameter[] parms = new SqlParameter[3]; parms[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); parms[1] = SqlHelper.GetParameter("@PlanID", model.PlanID); parms[2] = SqlHelper.GetParameter("@ID", model.ID); object obj = SqlHelper.ExecuteScalar(sql, parms); return Convert.ToInt32(obj); } else { sql = "select Count(ID) from officedba.MRP where CompanyCD=@CompanyCD and PlanID=@PlanID"; SqlParameter[] parms = new SqlParameter[2]; parms[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); parms[1] = SqlHelper.GetParameter("@PlanID", model.PlanID); object obj = SqlHelper.ExecuteScalar(sql, parms); return Convert.ToInt32(obj); } }
/// <summary> /// 确认或结单 /// </summary> /// <param name="model"></param> /// <param name="loginUserID"></param> /// <param name="isConfirm"></param> /// <returns></returns> public static bool ConfirmOrCompleteMRP(MRPModel model, string loginUserID, int OperateType) { //1:确认 2:结单 3:取消结单 if (OperateType == 1) { StringBuilder sql = new StringBuilder(); sql.AppendLine(" UPDATE officedba.MRP SET"); sql.AppendLine(" Confirmor = @Confirmor,"); sql.AppendLine(" ConfirmDate = @ConfirmDate,"); sql.AppendLine(" ModifiedDate = getdate(),"); sql.AppendLine(" BillStatus = 2,"); sql.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sql.AppendLine(" Where ID=@ID"); SqlParameter[] param = new SqlParameter[3]; param[0] = SqlHelper.GetParameter("@ID", model.ID); param[1] = SqlHelper.GetParameter("@Confirmor", model.Confirmor); param[2] = SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate); SqlHelper.ExecuteTransSql(sql.ToString(), param); return SqlHelper.Result.OprateCount > 0 ? true : false; } else if (OperateType == 2) { StringBuilder sql = new StringBuilder(); sql.AppendLine(" UPDATE officedba.MRP SET"); sql.AppendLine(" Closer = @Closer,"); sql.AppendLine(" CloseDate = @CloseDate,"); sql.AppendLine(" BillStatus = 4,"); sql.AppendLine(" ModifiedDate = getdate(),"); sql.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sql.AppendLine(" Where ID=@ID"); SqlParameter[] param = new SqlParameter[3]; param[0] = SqlHelper.GetParameter("@ID", model.ID); param[1] = SqlHelper.GetParameter("@Closer", model.Closer); param[2] = SqlHelper.GetParameter("@CloseDate", model.CloseDate); SqlHelper.ExecuteTransSql(sql.ToString(), param); return SqlHelper.Result.OprateCount > 0 ? true : false; } else { StringBuilder sql = new StringBuilder(); sql.AppendLine(" update officedba.MRP set Closer=null,CloseDate=null,ModifiedDate=getdate(),BillStatus=2,ModifiedUserID = '" + loginUserID + "'"); sql.AppendLine(" Where ID=@ID"); SqlParameter[] param = new SqlParameter[1]; param[0] = SqlHelper.GetParameter("@ID", model.ID); SqlHelper.ExecuteTransSql(sql.ToString(), param); return SqlHelper.Result.OprateCount > 0 ? true : false; } }
/// <summary> /// MRP明细信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetMRPDetailInfo(MRPModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder detSql = new StringBuilder(); detSql.AppendLine("select * from ("); detSql.AppendLine(" select a.CompanyCD,a.ID as DetailID,a.MRPNo,a.SortNo,"); detSql.AppendLine(" a.ProductID,b.ProdNo,b.ProductName,b.Specification,a.UnitID,c.CodeName as UnitName,a.UsedUnitID,e.CodeName as UsedUnitName,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedUnitCount) as UsedUnitCount,"); detSql.AppendLine(" f.TypeName as ColorName,g.TypeName as MaterialName,"); detSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.GrossCount) as GrossCount,Convert(numeric(14,"+userInfo.SelPoint+"),a.PlanCount) as PlanCount,isnull( CONVERT(CHAR(10), a.PlanDate, 23),'') as PlanDate,a.MaterialSource,"); detSql.AppendLine(" a.FromBillID,a.FromBillNo,a.FromLineNo,a.Remark,Convert(numeric(14,"+userInfo.SelPoint+"),a.ProcessedCount) as ProcessedCount,"); detSql.AppendLine(" case when a.MaterialSource=0 then '采购' when a.MaterialSource=1 then '生产' when a.MaterialSource=2 then '库存' end as strMaterialSource,"); detSql.AppendLine(" a.ModifiedDate,a.ModifiedUserID"); detSql.AppendLine(" from officedba.MRPDetail a"); detSql.AppendLine(" left join officedba.ProductInfo b on a.ProductID=b.ID"); detSql.AppendLine(" left join officedba.CodeUnitType c on a.UnitID=c.ID"); detSql.AppendLine(" left join officedba.CodeUnitType e on a.UsedUnitID=e.ID"); detSql.AppendLine(" left join officedba.CodePublicType f on b.ColorID=f.ID"); detSql.AppendLine(" left join officedba.CodePublicType g on b.Material=g.ID"); detSql.AppendLine(" where a.CompanyCD=@CompanyCD and MRPNo=("); detSql.AppendLine(" select top 1 MRPNo from officedba.MRP where ID=@ID"); detSql.AppendLine(" )"); detSql.AppendLine(") as info"); detSql.AppendLine("where CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); //指定命令的SQL文 comm.CommandText = detSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 通过检索条件查询物料需求计划单信息 /// </summary> /// <param name="model"></param> /// <param name="FlowStatus"></param> /// <returns></returns> public static DataTable GetMRPListBycondition(MRPModel model, int FlowStatus, int BillTypeFlag, int BillTypeCode, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select * from ("); searchSql.AppendLine(" SELECT a.CompanyCD,a.ID,a.MRPNo,a.Subject,a.PlanID,isnull(e.PlanNo,'')as PlanNo,a.ModifiedDate,"); searchSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,"); searchSql.AppendLine(" a.Principal,isnull(c.EmployeeName,'')as PrincipalReal,a.DeptID,isnull(d.DeptName,'')as DeptName ,"); searchSql.AppendLine(" isnull( a.Confirmor,'')as Confirmor ,isnull(f.EmployeeName,'')as ConfirmorReal,"); searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,"); searchSql.AppendLine(" a.BillStatus,isnull( b.FlowStatus,'0')as FlowStatus,"); searchSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '执行' when a.BillStatus=3 then '变更' when a.BillStatus=4 then '手工结单' when a.BillStatus=5 then '自动结单' end as strBillStatus,"); searchSql.AppendLine(" case when b.FlowStatus=1 then '待审批' when b.FlowStatus=2 then '审批中' when b.FlowStatus=3 then '审批通过' when b.FlowStatus=4 then '审批不通过' when b.FlowStatus=5 then '撤消审批' end as strFlowStatus "); searchSql.AppendLine(" FROM officedba.MRP a "); searchSql.AppendLine(" LEFT JOIN officedba.FlowInstance b ON a.ID=b.BillID and b.BillTypeFlag=@BillTypeFlag and b.BillTypeCode=@BillTypeCode"); searchSql.AppendLine(" AND b.ID =( select max(ID) from officedba.FlowInstance H where H.CompanyCD = A.CompanyCD AND H.BillID = A.ID and H.BillTypeFlag =@BillTypeFlag and H.BillTypeCode = @BillTypeCode)"); searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo c ON a.Principal=c.ID"); searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo f ON a.Confirmor =f.ID"); searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo d ON a.DeptID=d.ID"); searchSql.AppendLine(" LEFT Join officedba.MasterProductSchedule e ON a.PlanID=e.ID"); searchSql.AppendLine(") as info"); searchSql.AppendLine("where CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //BillTypeFlag comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeFlag", BillTypeFlag.ToString())); //BillTypeCode comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeCode", BillTypeCode.ToString())); //单据编号 if (!string.IsNullOrEmpty(model.MRPNo)) { searchSql.AppendLine("and MRPNo like @MRPNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@MRPNo", "%" + model.MRPNo + "%")); } //单据主题 if (!string.IsNullOrEmpty(model.Subject)) { searchSql.AppendLine(" and Subject like @Subject"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", "%" + model.Subject + "%")); } //负责人 if (model.PlanID > 0) { searchSql.AppendLine(" and PlanID=@PlanID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanID", model.PlanID.ToString())); } //负责人 if (model.Principal > 0) { searchSql.AppendLine(" and Principal=@Principal "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Principal", model.Principal.ToString())); } //部门 if (model.DeptID > 0) { searchSql.AppendLine(" and DeptID=@DeptID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID.ToString())); } //单据状态 if (!string.IsNullOrEmpty(model.BillStatus)) { if (int.Parse(model.BillStatus) > 0) { searchSql.AppendLine("and BillStatus=@BillStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus)); } } //审批状态 if (FlowStatus > -1) { searchSql.AppendLine(" and FlowStatus=@FlowStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", FlowStatus.ToString())); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// MRP详细信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetMRPInfo(MRPModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder infoSql = new StringBuilder(); infoSql.AppendLine("select * from ("); infoSql.AppendLine(" select a.CompanyCD,a.ID,a.MRPNo,a.Subject,"); infoSql.AppendLine(" a.PlanID,e.PlanNo,a.Remark,a.Principal,g.EmployeeName as PricipalReal,"); infoSql.AppendLine(" a.DeptID,f.DeptName,Convert(numeric(22," + userInfo.SelPoint + "),a.CountTotal) as CountTotal,"); infoSql.AppendLine(" a.Creator,b.EmployeeName as CreatorReal,isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,"); infoSql.AppendLine(" a.Confirmor,c.EmployeeName as ConfirmorReal,isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,"); infoSql.AppendLine(" a.Closer,d.EmployeeName as CloserReal,isnull( CONVERT(CHAR(10), a.CloseDate, 23),'') as CloseDate,"); infoSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '执行' when a.BillStatus=4 then '手工结单' when a.BillStatus=5 then '自动结单' end as strBillStatusText,"); infoSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,"); infoSql.AppendLine(" a.BillStatus,isnull( CONVERT(CHAR(10), a.ModifiedDate, 23),'') as ModifiedDate,a.ModifiedUserID from officedba.MRP as a"); infoSql.AppendLine(" left join officedba.EmployeeInfo as b on a.Creator=b.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo as c on a.Confirmor=c.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo as d on a.Closer=d.ID"); infoSql.AppendLine(" left join officedba.EmployeeInfo as g on a.Principal=g.ID"); infoSql.AppendLine(" left join officedba.DeptInfo as f on a.DeptID=f.ID"); infoSql.AppendLine(" left join officedba.MasterProductSchedule e on a.PlanID=e.ID"); infoSql.AppendLine(") as m"); infoSql.AppendLine("where ID=@ID"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); //指定命令的SQL文 comm.CommandText = infoSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 物料需求计划插入 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertMRP(MRPModel model, Hashtable htExtAttr, string loginUserID, out string ID) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; ArrayList listADD = new ArrayList(); bool result = false; //#region 传参 try { #region 物料需求计划添加SQL语句 StringBuilder sqlMrp = new StringBuilder(); sqlMrp.AppendLine("insert into officedba.MRP"); sqlMrp.AppendLine("(CompanyCD,MRPNo,Subject,PlanID,Remark,Principal,DeptID,CountTotal,Creator,CreateDate,BillStatus,ModifiedDate,ModifiedUserID)"); sqlMrp.AppendLine("VALUES "); sqlMrp.AppendLine(" (@CompanyCD"); sqlMrp.AppendLine(" ,@MRPNo"); sqlMrp.AppendLine(" ,@Subject"); sqlMrp.AppendLine(" ,@PlanID"); sqlMrp.AppendLine(" ,@Remark"); sqlMrp.AppendLine(" ,@Principal"); sqlMrp.AppendLine(" ,@DeptID"); sqlMrp.AppendLine(" ,@CountTotal"); sqlMrp.AppendLine(" ,@Creator"); sqlMrp.AppendLine(" ,@CreateDate"); sqlMrp.AppendLine(" ,@BillStatus"); sqlMrp.AppendLine(" ,getdate()"); sqlMrp.AppendLine(" ,'" + loginUserID + "') "); sqlMrp.AppendLine("set @ID=@@IDENTITY"); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlMrp.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@MRPNo", model.MRPNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject)); comm.Parameters.Add(SqlHelper.GetParameter("@PlanID", model.PlanID)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@Principal", model.Principal)); comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal)); comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator)); comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate)); comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus)); comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int)); listADD.Add(comm); #endregion #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); if (htExtAttr.Count > 0) listADD.Add(cmd); #endregion #region 物料需求计划明细信息添加SQL语句 if (!String.IsNullOrEmpty(model.detPlanCount) && !String.IsNullOrEmpty(model.detPlanDate) && !String.IsNullOrEmpty(model.detMaterialSource)) { string[] dtSortNo = model.detSortNo.Split(','); string[] dtProductID = model.detProductID.Split(','); string[] dtUnitID = model.detUnitID.Split(','); string[] dtGrossCount = model.detGrossCount.Split(','); string[] dtPlanCount = model.detPlanCount.Split(','); string[] dtPlanDate = model.detPlanDate.Split(','); string[] dtMaterialSource = model.detMaterialSource.Split(','); string[] dtRemark = model.detRemark.Split(','); string[] dtFromBillID = model.detFromBillID.Split(','); string[] dtUsedUnitID = model.detUsedUnitID.Split(','); string[] dtUsedUnitCount = model.detUsedUnitCount.Split(','); string[] dtExRate = model.detExRate.Split(','); //页面上这些字段都是必填,数组的长度必须是相同的 if (dtPlanCount.Length >= 1) { for (int i = 0; i < dtPlanCount.Length; i++) { System.Text.StringBuilder cmdsql = new System.Text.StringBuilder(); cmdsql.AppendLine("insert into officedba.MRPDetail"); cmdsql.AppendLine("(CompanyCD,"); cmdsql.AppendLine("MRPNo,"); cmdsql.AppendLine("SortNo,"); cmdsql.AppendLine("ProductID,"); cmdsql.AppendLine("UnitID,"); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { cmdsql.AppendLine("GrossCount,"); } cmdsql.AppendLine("PlanCount,"); if (userInfo.IsMoreUnit) { cmdsql.AppendLine("UsedUnitID,"); cmdsql.AppendLine("UsedUnitCount,"); cmdsql.AppendLine("ExRate,"); } cmdsql.AppendLine("PlanDate,"); cmdsql.AppendLine("MaterialSource,"); cmdsql.AppendLine("Remark,"); cmdsql.AppendLine("FromBillID,"); cmdsql.AppendLine("ModifiedDate,"); cmdsql.AppendLine("ModifiedUserID)"); cmdsql.AppendLine(" Values(@CompanyCD"); cmdsql.AppendLine(" ,@MRPNo"); cmdsql.AppendLine(" ,@SortNo"); cmdsql.AppendLine(" ,@ProductID"); cmdsql.AppendLine(" ,@UnitID"); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { cmdsql.AppendLine(" ,@GrossCount"); } cmdsql.AppendLine(" ,@PlanCount"); if (userInfo.IsMoreUnit) { cmdsql.AppendLine(" ,@UsedUnitID"); cmdsql.AppendLine(" ,@UsedUnitCount"); cmdsql.AppendLine(" ,@ExRate"); } cmdsql.AppendLine(" ,@PlanDate"); cmdsql.AppendLine(" ,@MaterialSource"); cmdsql.AppendLine(" ,@Remark"); cmdsql.AppendLine(" ,@FromBillID"); cmdsql.AppendLine(" ,getdate()"); cmdsql.AppendLine(" ,'" + loginUserID + "')"); SqlCommand comms = new SqlCommand(); comms.CommandText = cmdsql.ToString(); comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comms.Parameters.Add(SqlHelper.GetParameter("@MRPNo", model.MRPNo)); comms.Parameters.Add(SqlHelper.GetParameter("@SortNo", dtSortNo[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", dtProductID[i].ToString().Trim())); comms.Parameters.Add(SqlHelper.GetParameter("@UnitID", dtUnitID[i].ToString().Trim())); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { comms.Parameters.Add(SqlHelper.GetParameter("@GrossCount", dtGrossCount[i].ToString().Trim())); } comms.Parameters.Add(SqlHelper.GetParameter("@PlanCount", dtPlanCount[i].ToString().Trim())); if (userInfo.IsMoreUnit) { comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", dtUsedUnitID[i].ToString().Trim())); comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", dtUsedUnitCount[i].ToString().Trim())); comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", dtExRate[i].ToString().Trim())); } comms.Parameters.Add(SqlHelper.GetParameter("@PlanDate", dtPlanDate[i].ToString().Trim())); comms.Parameters.Add(SqlHelper.GetParameter("@MaterialSource", dtMaterialSource[i].ToString().Trim())); comms.Parameters.Add(SqlHelper.GetParameter("@Remark", dtRemark[i].ToString().Trim())); comms.Parameters.Add(SqlHelper.GetParameter("@FromBillID", dtFromBillID[i].ToString().Trim())); listADD.Add(comms); } } } #endregion if (SqlHelper.ExecuteTransWithArrayList(listADD)) { ID = comm.Parameters["@ID"].Value.ToString(); result = true; } else { ID = "0"; } return result; } catch (Exception ex) { throw ex; } }
/// <summary> /// 修改物料需求计划和物料需求计划单明细信息 /// </summary> /// <param name="model"></param> /// <param name="loginUserID"></param> /// <param name="UpdateID"></param> /// <returns></returns> public static bool UpdateMRPInfo(MRPModel model, Hashtable htExtAttr,string loginUserID, string UpdateID) { //获取登陆用户ID ArrayList listADD = new ArrayList(); UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; if (model.ID <= 0) { return false; } #region MRP修改SQL语句 StringBuilder sqlBom = new StringBuilder(); sqlBom.AppendLine("update officedba.MRP set"); sqlBom.AppendLine(" Subject=@Subject,"); sqlBom.AppendLine(" PlanID=@PlanID,"); sqlBom.AppendLine(" Remark=@Remark,"); sqlBom.AppendLine(" Principal=@Principal,"); sqlBom.AppendLine(" DeptID=@DeptID,"); sqlBom.AppendLine(" CountTotal=@CountTotal,"); sqlBom.AppendLine(" ModifiedDate=getdate(),"); sqlBom.AppendLine(" ModifiedUserID='" + loginUserID + "'"); sqlBom.AppendLine("where CompanyCD=@CompanyCD"); sqlBom.AppendLine("and ID=@ID"); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlBom.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject)); comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@PlanID", model.PlanID)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@Principal", model.Principal)); comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal)); listADD.Add(comm); #endregion #region 拓展属性 SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); if (htExtAttr.Count > 0) listADD.Add(cmd); #endregion #region MRP明细信息更新语句 //1.先删除不在BOM子件中的 //2.更新子件中的ID //3.添加其它明细 #region 先删除不在BOM子件中的 if (!string.IsNullOrEmpty(UpdateID)) { StringBuilder sqlDel = new StringBuilder(); sqlDel.AppendLine("delete from officedba.MRPDetail where CompanyCD=@CompanyCD and MRPNo=@MRPNo and ID not in(" + UpdateID + ")"); SqlCommand commDel = new SqlCommand(); commDel.CommandText = sqlDel.ToString(); commDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commDel.Parameters.Add(SqlHelper.GetParameter("@MRPNo", model.MRPNo)); listADD.Add(commDel); } else { StringBuilder sqlDel = new StringBuilder(); sqlDel.AppendLine("delete from officedba.MRPDetail where CompanyCD=@CompanyCD and MRPNo=@MRPNo"); SqlCommand commDel = new SqlCommand(); commDel.CommandText = sqlDel.ToString(); commDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commDel.Parameters.Add(SqlHelper.GetParameter("@MRPNo", model.MRPNo)); listADD.Add(commDel); } #endregion #region 添加或更新操作 string[] updateID = UpdateID.Split(','); if (!string.IsNullOrEmpty(UpdateID) && updateID.Length > 0) { if (!String.IsNullOrEmpty(model.detPlanCount) && !String.IsNullOrEmpty(model.detPlanDate) && !String.IsNullOrEmpty(model.detMaterialSource)) { string[] dtSortNo = model.detSortNo.Split(','); string[] dtProductID = model.detProductID.Split(','); string[] dtUnitID = model.detUnitID.Split(','); string[] dtGrossCount = model.detGrossCount.Split(','); string[] dtPlanCount = model.detPlanCount.Split(','); string[] dtPlanDate = model.detPlanDate.Split(','); string[] dtMaterialSource = model.detMaterialSource.Split(','); string[] dtRemark = model.detRemark.Split(','); string[] dtUsedUnitID = model.detUsedUnitID.Split(','); string[] dtUsedUnitCount = model.detUsedUnitCount.Split(','); string[] dtExRate = model.detExRate.Split(','); string[] dtFromBillID = model.detFromBillID.Split(','); for (int i = 0; i < updateID.Length; i++) { int intUpdateID = int.Parse(updateID[i].ToString()); if (intUpdateID > 0) { #region 更新MRP明细中的ID StringBuilder sqlEdit = new StringBuilder(); sqlEdit.AppendLine("Update officedba.MRPDetail"); sqlEdit.AppendLine("Set SortNo=@SortNo,"); sqlEdit.AppendLine(" ProductID=@ProductID,"); sqlEdit.AppendLine(" UnitID=@UnitID,"); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { sqlEdit.AppendLine(" GrossCount=@GrossCount,"); } sqlEdit.AppendLine(" PlanCount=@PlanCount,"); if (userInfo.IsMoreUnit) { sqlEdit.AppendLine(" UsedUnitID=@UsedUnitID,"); sqlEdit.AppendLine(" UsedUnitCount=@UsedUnitCount,"); sqlEdit.AppendLine(" ExRate=@ExRate,"); } sqlEdit.AppendLine(" PlanDate=@PlanDate,"); sqlEdit.AppendLine(" MaterialSource=@MaterialSource,"); sqlEdit.AppendLine(" Remark=@Remark,"); sqlEdit.AppendLine(" ModifiedDate=getdate(),"); sqlEdit.AppendLine(" ModifiedUserID='" + loginUserID + "'"); sqlEdit.AppendLine("where CompanyCD=@CompanyCD"); sqlEdit.AppendLine("and ID=@ID"); SqlCommand commEdit = new SqlCommand(); commEdit.CommandText = sqlEdit.ToString(); commEdit.Parameters.Add(SqlHelper.GetParameter("@SortNo", dtSortNo[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@ProductID", dtProductID[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@UnitID", dtUnitID[i].ToString())); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { commEdit.Parameters.Add(SqlHelper.GetParameter("@GrossCount", dtGrossCount[i].ToString())); } commEdit.Parameters.Add(SqlHelper.GetParameter("@PlanCount", dtPlanCount[i].ToString())); if (userInfo.IsMoreUnit) { commEdit.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", dtUsedUnitID[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", dtUsedUnitCount[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@ExRate", dtExRate[i].ToString())); } commEdit.Parameters.Add(SqlHelper.GetParameter("@PlanDate", dtPlanDate[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@MaterialSource", dtMaterialSource[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@Remark", dtRemark[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commEdit.Parameters.Add(SqlHelper.GetParameter("@ID", updateID[i].ToString())); listADD.Add(commEdit); #endregion } else { #region 添加MRP明细中的ID StringBuilder sqlIn = new StringBuilder(); sqlIn.AppendLine("insert into officedba.MRPDetail("); sqlIn.AppendLine(" CompanyCD,"); sqlIn.AppendLine(" MRPNo,"); sqlIn.AppendLine(" SortNo,"); sqlIn.AppendLine(" ProductID,"); sqlIn.AppendLine(" UnitID,"); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { sqlIn.AppendLine(" GrossCount,"); } sqlIn.AppendLine(" PlanCount,"); if (userInfo.IsMoreUnit) { sqlIn.AppendLine(" UsedUnitID,"); sqlIn.AppendLine(" UsedUnitCount,"); sqlIn.AppendLine(" ExRate,"); } sqlIn.AppendLine(" PlanDate,"); sqlIn.AppendLine(" MaterialSource,"); sqlIn.AppendLine(" Remark,"); sqlIn.AppendLine(" FromBillID,"); sqlIn.AppendLine(" ModifiedDate,"); sqlIn.AppendLine(" ModifiedUserID)"); sqlIn.AppendLine("Values( @CompanyCD,"); sqlIn.AppendLine(" @MRPNo,"); sqlIn.AppendLine(" @SortNo,"); sqlIn.AppendLine(" @ProductID,"); sqlIn.AppendLine(" @UnitID,"); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { sqlIn.AppendLine(" @GrossCount,"); } sqlIn.AppendLine(" @PlanCount,"); if (userInfo.IsMoreUnit) { sqlIn.AppendLine(" @UsedUnitID,"); sqlIn.AppendLine(" @UsedUnitCount,"); sqlIn.AppendLine(" @ExRate,"); } sqlIn.AppendLine(" @PlanDate,"); sqlIn.AppendLine(" @MaterialSource,"); sqlIn.AppendLine(" @Remark,"); sqlIn.AppendLine(" @FromBillID,"); sqlIn.AppendLine(" getdate(),"); sqlIn.AppendLine(" '"+loginUserID+"')"); SqlCommand commIn = new SqlCommand(); commIn.CommandText = sqlIn.ToString(); commIn.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commIn.Parameters.Add(SqlHelper.GetParameter("@MRPNo", model.MRPNo)); commIn.Parameters.Add(SqlHelper.GetParameter("@SortNo", dtSortNo[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@ProductID", dtProductID[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@UnitID", dtUnitID[i].ToString())); if (!string.IsNullOrEmpty(dtGrossCount[i].ToString().Trim())) { commIn.Parameters.Add(SqlHelper.GetParameter("@GrossCount", dtGrossCount[i].ToString())); } commIn.Parameters.Add(SqlHelper.GetParameter("@PlanCount", dtPlanCount[i].ToString())); if (userInfo.IsMoreUnit) { commIn.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", dtUsedUnitID[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", dtUsedUnitCount[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@ExRate", dtExRate[i].ToString())); } commIn.Parameters.Add(SqlHelper.GetParameter("@PlanDate", dtPlanDate[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@MaterialSource", dtMaterialSource[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@Remark", dtRemark[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@FromBillID", dtFromBillID[i].ToString())); listADD.Add(commIn); #endregion } } } } #endregion #endregion return SqlHelper.ExecuteTransWithArrayList(listADD); }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(MRPModel model, Hashtable htExtAttr, SqlCommand cmd) { try { string strSql = string.Empty; strSql = "UPDATE officedba.MRP set "; foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例 { strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ","; cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim()); } int iLength = strSql.Length - 1; strSql = strSql.Substring(0, iLength); strSql += " where CompanyCD = @CompanyCD AND MRPNo = @MRPNo"; cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD); cmd.Parameters.AddWithValue("@MRPNo", model.MRPNo); cmd.CommandText = strSql; } catch (Exception) { } }
/// <summary> /// 取消确认(未生成采购计划的才可以取消确认) /// </summary> /// <param name="CompanyCD"></param> /// <param name="BillTypeFlag"></param> /// <param name="BillTypeCode"></param> /// <param name="BillID"></param> /// <param name="loginUserID"></param> /// <returns></returns> public static bool CancelConfirmOperate(MRPModel model, int BillTypeFlag, int BillTypeCode, string loginUserID) { ArrayList listADD = new ArrayList(); //#region 传参 try { #region 撤消审批流程 #region 撤消审批处理逻辑描述 //可参见撤消审批的存储过程[FlowApproval_Update],个别的判断去掉 //--1.往流程任务历史记录表(officedba.FlowTaskHistory)插1条处理记录, //--记录的步骤序号为0(表示返回到流程提交人环节),审批状态为撤销审批 //Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate) //Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate()) //--2.更新流程任务处理表(officedba.FlowTaskList)中的流程步骤序号为0(表示返回到流程提交人环节) //Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID //Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID //--3更新流程实例表(officedba.FlowInstance)中的流程状态为“撤销审批” //Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID //Where CompanyCD=@CompanyCD //and FlowNo=@tempFlowNo //and BillTypeFlag=@BillTypeFlag //and BillTypeCode=@BillTypeCode //and BillID=@BillID #endregion DataTable dtFlowInstance = Common.FlowDBHelper.GetFlowInstanceInfo(model.CompanyCD, BillTypeFlag, BillTypeCode, model.ID); if (dtFlowInstance.Rows.Count > 0) { //提交审批了的单据 string FlowInstanceID = dtFlowInstance.Rows[0]["FlowInstanceID"].ToString(); string FlowStatus = dtFlowInstance.Rows[0]["FlowStatus"].ToString(); string FlowNo = dtFlowInstance.Rows[0]["FlowNo"].ToString(); #region 往流程任务历史记录表 StringBuilder sqlHis = new StringBuilder(); sqlHis.AppendLine("Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)"); sqlHis.AppendLine("Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())"); SqlCommand commHis = new SqlCommand(); commHis.CommandText = sqlHis.ToString(); commHis.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID)); commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo)); commHis.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag)); commHis.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID)); commHis.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID)); listADD.Add(commHis); #endregion #region 更新流程任务处理表 StringBuilder sqlTask = new StringBuilder(); sqlTask.AppendLine("Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID"); sqlTask.AppendLine("Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID"); SqlCommand commTask = new SqlCommand(); commTask.CommandText = sqlTask.ToString(); commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commTask.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID)); commTask.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID)); listADD.Add(commTask); #endregion #region 更新流程实例表 StringBuilder sqlIns = new StringBuilder(); sqlIns.AppendLine("Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID "); sqlIns.AppendLine("Where CompanyCD=@CompanyCD "); sqlIns.AppendLine("and FlowNo=@tempFlowNo "); sqlIns.AppendLine("and BillTypeFlag=@BillTypeFlag "); sqlIns.AppendLine("and BillTypeCode=@BillTypeCode "); sqlIns.AppendLine("and BillID=@BillID"); SqlCommand commIns = new SqlCommand(); commIns.CommandText = sqlIns.ToString(); commIns.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commIns.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo)); commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag)); commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeCode", BillTypeCode)); commIns.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID)); commIns.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID)); listADD.Add(commIns); #endregion } #endregion #region 处理自己的业务逻辑 #region 更新主表信息 StringBuilder sqlUn = new StringBuilder(); sqlUn.AppendLine(" UPDATE officedba.MRP SET"); sqlUn.AppendLine(" Confirmor = null,"); sqlUn.AppendLine(" ConfirmDate = null,"); sqlUn.AppendLine(" ModifiedDate = getdate(),"); sqlUn.AppendLine(" BillStatus = 1,"); sqlUn.AppendLine(" ModifiedUserID = '" + loginUserID + "'"); sqlUn.AppendLine(" Where ID=@ID"); SqlCommand commUn = new SqlCommand(); commUn.CommandText = sqlUn.ToString(); commUn.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); listADD.Add(commUn); #endregion #region 删除采购需求计划表里的数据 StringBuilder sqlDel = new StringBuilder(); sqlDel.AppendLine("delete from officedba.PurchaseRequire where CompanyCd=@CompanyCD and MRPCD=@ID"); SqlCommand commDel= new SqlCommand(); commDel.CommandText = sqlDel.ToString(); commDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commDel.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); listADD.Add(commDel); #endregion #region 更新明细表里的已生成采购需求数量 StringBuilder sqlUnDet = new StringBuilder(); sqlUnDet.AppendLine("Update officedba.MRPDetail Set ProcessedCount=null where CompanyCD=@CompanyCD and MRPNo=(select top 1 MRPNo from officedba.MRP where ID=@ID)"); SqlCommand commUnDet = new SqlCommand(); commUnDet.CommandText = sqlUnDet.ToString(); commUnDet.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commUnDet.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); listADD.Add(commUnDet); #endregion #endregion return SqlHelper.ExecuteTransWithArrayList(listADD); } catch (Exception ex) { throw ex; } }