/******************************************************************************* * Copyright(c) 2019 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：PPModuleLogic.cs * 2.功能描述：生产计划 年计划 业务逻辑 * 编辑履历： * 作者 日期 版本 修改内容 * 徐伟 2019/08/30 1.00 新建 *******************************************************************************/ using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Text; using Oracle.DataAccess.Client; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Service.CMNModuleService; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; namespace Dongke.IBOSS.PRD.Service.PPModuleService { public class PPModuleLogic { #region 计划通用 //获取 序列的值 public static int Seq(string seqName) { return Convert.ToInt32(OracleDB.ExecuteScalar($"select {seqName} from dual")); } //生产计划类型 列表 public static ServiceResultEntity PlanTypeSearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("TP_PP_PLAN_TYPE", "search", cre, "displayno asc"); } //生产计划产品 列表 public static ServiceResultEntity PlanGoodsSearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("TP_MST_GOODS", "search", cre, "goodsid asc"); } #endregion #region 年计划 //年计划 版本 public static int PlanYearVer(int planTypeId) { return Convert.ToInt32(OracleDB.ExecuteScalar($"select nvl(max(PLANVER)+1,1) from TP_PP_PLAN_YEAR where PLANTYPEID = {planTypeId}")); } //年计划 保存副本 public static int PlanYearCopy(int planId) { string sqlStr = @""; IDBTransaction conn = OracleDB.GetConn(); //读取新ID序列 int newPlanId = Convert.ToInt32(conn.GetSqlResultToObj($"select SEQ_PP_PLAN_YEAR_PLANID.NEXTVAL from dual")); //复制年计划=========================== //取计划类型ID int planTypeId = Convert.ToInt32(conn.GetSqlResultToObj($"select PLANTYPEID from TP_PP_PLAN_YEAR where PLANID = {planId}")); //取最新版本+1 int planVer = Convert.ToInt32(conn.GetSqlResultToObj($"select nvl(max(PLANVER)+1,1) from TP_PP_PLAN_YEAR where PLANTYPEID = {planTypeId}")); sqlStr = $@"insert into TP_PP_PLAN_YEAR (PLANID, YEARNO, PLANNAME, PLANTYPEID, PLANVER, STARTMONTH, ENDMONTH, STARTDATE, ENDDATE, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM) select {newPlanId}, YEARNO, PLANNAME||'-副本', PLANTYPEID, {planVer}, STARTMONTH, ENDMONTH, STARTDATE, ENDDATE, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM from TP_PP_PLAN_YEAR where PLANID = {planId}"; OracleDB.ExecuteNonQuery(sqlStr, (IDataParameter[])null, conn, false); //====================================== //复制年计划产量计划==================== sqlStr = $@"insert into TP_PP_PLAN_YEAR_GOODS (PLANID, YEARNO, STARTDATE, ENDDATE, GOODSID, PLANQUANTITY, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM) select {newPlanId}, YEARNO, STARTDATE, ENDDATE, GOODSID, PLANQUANTITY, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM from TP_PP_PLAN_YEAR_GOODS where planid = {planId} "; OracleDB.ExecuteNonQuery(sqlStr, (IDataParameter[])null, conn, true); //====================================== return newPlanId; } //年计划 列表 只取最新版本 public static ServiceResultEntity PlanYearSearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_YEAR", "search", cre, "planid asc"); } //年计划 列表 所有版本 public static ServiceResultEntity PlanYearVerSearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_YEAR_VER", "search", cre, "planid asc"); } //年计划 插入 public static ServiceResultEntity PlanYearInsert(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_YEAR", "insert", cre); } //年计划 更新 public static ServiceResultEntity PlanYearUpdate(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_YEAR", "update", cre); } //年计划 删除 public static ServiceResultEntity PlanYearDelete(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_YEAR", "delete", cre); } //年计划产量 列表 public static ServiceResultEntity PlanYearGoodsSearch(ClientRequestEntity cre = null) { ServiceResultEntity sre = new ServiceResultEntity(); return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_YEAR_GOODS", "search", cre, "plangoodsid asc"); } //年计划 产量插入 public static ServiceResultEntity PlanYearGoodsInsert(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_YEAR_GOODS", "insert", cre); } //年计划 产量更新 public static ServiceResultEntity PlanYearGoodsUpdate(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_YEAR_GOODS", "update", cre); } //年计划 产量删除 public static ServiceResultEntity PlanYearGoodsDelete(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_YEAR_GOODS", "delete", cre); } #endregion #region 月计划 //月计划 版本 public static int PlanMonthVer(int planTypeId) { return Convert.ToInt32(OracleDB.ExecuteScalar($"select nvl(max(PLANVER)+1,1) from TP_PP_PLAN_MONTH where PLANTYPEID = {planTypeId}")); } //月计划 保存副本 public static int PlanMonthCopy(int planId) { string sqlStr = @""; IDBTransaction conn = OracleDB.GetConn(); //读取新ID序列 int newPlanId = Convert.ToInt32(conn.GetSqlResultToObj($"select SEQ_PP_PLAN_MONTH_PLANID.NEXTVAL from dual")); //复制月计划=========================== //取计划类型ID int planTypeId = Convert.ToInt32(conn.GetSqlResultToObj($"select PLANTYPEID from TP_PP_PLAN_MONTH where PLANID = {planId}")); //取最新版本+1 int planVer = Convert.ToInt32(conn.GetSqlResultToObj($"select nvl(max(PLANVER)+1,1) from TP_PP_PLAN_MONTH where PLANTYPEID = {planTypeId}")); sqlStr = $@"insert into TP_PP_PLAN_MONTH (PLANID, YEARNO, MONTHNO, PLANNAME, PLANTYPEID, PLANVER, STARTDATE, ENDDATE, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM) select {newPlanId}, YEARNO, MONTHNO, PLANNAME||'-副本', PLANTYPEID, {planVer}, STARTDATE, ENDDATE, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM from TP_PP_PLAN_MONTH where PLANID = {planId}"; OracleDB.ExecuteNonQuery(sqlStr, (IDataParameter[])null, conn, false); //====================================== //复制月计划产量计划==================== sqlStr = $@"insert into TP_PP_PLAN_MONTH_GOODS (PLANID, YEARNO, MONTHNO, STARTDATE, ENDDATE, GOODSID, PLANQUANTITY, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM) select {newPlanId}, YEARNO, MONTHNO, STARTDATE, ENDDATE, GOODSID, PLANQUANTITY, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM from TP_PP_PLAN_MONTH_GOODS where planid = {planId} "; OracleDB.ExecuteNonQuery(sqlStr, (IDataParameter[])null, conn, true); //====================================== return newPlanId; } //月计划 列表 只取最新版本 public static ServiceResultEntity PlanMonthSearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_MONTH", "search", cre, "planid asc"); } //月计划 列表 所有版本 public static ServiceResultEntity PlanMonthVerSearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_MONTH_VER", "search", cre, "planid asc"); } //月计划 插入 public static ServiceResultEntity PlanMonthInsert(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_MONTH", "insert", cre); } //月计划 更新 public static ServiceResultEntity PlanMonthUpdate(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_MONTH", "update", cre); } //月计划 删除 public static ServiceResultEntity PlanMonthDelete(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_MONTH", "delete", cre); } //月计划产量 列表 public static ServiceResultEntity PlanMonthGoodsSearch(ClientRequestEntity cre = null) { ServiceResultEntity sre = new ServiceResultEntity(); return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_MONTH_GOODS", "search", cre, "plangoodsid asc"); } //月计划 产量插入 public static ServiceResultEntity PlanMonthGoodsInsert(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_MONTH_GOODS", "insert", cre); } //月计划 产量更新 public static ServiceResultEntity PlanMonthGoodsUpdate(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_MONTH_GOODS", "update", cre); } //月计划 产量删除 public static ServiceResultEntity PlanMonthGoodsDelete(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_MONTH_GOODS", "delete", cre); } #endregion #region 日计划 //日计划 版本 public static int PlanDayVer(int planTypeId) { return Convert.ToInt32(OracleDB.ExecuteScalar($"select nvl(max(PLANVER)+1,1) from TP_PP_PLAN_DAY where PLANTYPEID = {planTypeId}")); } //日计划 保存副本 public static int PlanDayCopy(int planId) { string sqlStr = @""; IDBTransaction conn = OracleDB.GetConn(); //读取新ID序列 int newPlanId = Convert.ToInt32(conn.GetSqlResultToObj($"select SEQ_PP_PLAN_DAY_PLANID.NEXTVAL from dual")); //复制日计划=========================== //取计划类型ID int planTypeId = Convert.ToInt32(conn.GetSqlResultToObj($"select PLANTYPEID from TP_PP_PLAN_DAY where PLANID = {planId}")); //取最新版本+1 int planVer = Convert.ToInt32(conn.GetSqlResultToObj($"select nvl(max(PLANVER)+1,1) from TP_PP_PLAN_DAY where PLANTYPEID = {planTypeId}")); sqlStr = $@"insert into TP_PP_PLAN_DAY (PLANID, YEARNO, MONTHNO, DAYNO, PLANNAME, PLANTYPEID, PLANVER, STARTDATE, ENDDATE, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM) select {newPlanId}, YEARNO, MONTHNO, DAYNO, PLANNAME||'-副本', PLANTYPEID, {planVer}, STARTDATE, ENDDATE, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM from TP_PP_PLAN_DAY where PLANID = {planId}"; OracleDB.ExecuteNonQuery(sqlStr, (IDataParameter[])null, conn, false); //====================================== //复制日计划产量计划==================== sqlStr = $@"insert into TP_PP_PLAN_DAY_GOODS (PLANID, YEARNO, MONTHNO, DAYNO, STARTDATE, ENDDATE, GOODSID, PLANQUANTITY, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM) select {newPlanId}, YEARNO, MONTHNO, DAYNO, STARTDATE, ENDDATE, GOODSID, PLANQUANTITY, REMARKS, ACCOUNTID, CREATETIME, CREATEUSERID, UPDATETIME, UPDATEUSERID, OPTIMESTAMP, SESSIONPROGRAM from TP_PP_PLAN_DAY_GOODS where planid = {planId} "; OracleDB.ExecuteNonQuery(sqlStr, (IDataParameter[])null, conn, true); //====================================== return newPlanId; } //日计划 列表 只取最新版本 public static ServiceResultEntity PlanDaySearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_DAY", "search", cre, "planid asc"); } //日计划 列表 所有版本 public static ServiceResultEntity PlanDayVerSearch(ClientRequestEntity cre = null) { return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_DAY_VER", "search", cre, "planid asc"); } //日计划 插入 public static ServiceResultEntity PlanDayInsert(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_DAY", "insert", cre); } //日计划 更新 public static ServiceResultEntity PlanDayUpdate(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_DAY", "update", cre); } //日计划 删除 public static ServiceResultEntity PlanDayDelete(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_DAY", "delete", cre); } //日计划产量 列表 public static ServiceResultEntity PlanDayGoodsSearch(ClientRequestEntity cre = null) { ServiceResultEntity sre = new ServiceResultEntity(); return OracleDB.ExecuteDataSetSRE("VP_PP_PLAN_DAY_GOODS", "search", cre, "plangoodsid asc"); } //日计划 产量插入 public static ServiceResultEntity PlanDayGoodsInsert(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_DAY_GOODS", "insert", cre); } //日计划 产量更新 public static ServiceResultEntity PlanDayGoodsUpdate(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_DAY_GOODS", "update", cre); } //日计划 产量删除 public static ServiceResultEntity PlanDayGoodsDelete(ClientRequestEntity cre = null) { return OracleDB.ExecuteNonQuerySRE("TP_PP_PLAN_DAY_GOODS", "delete", cre); } #endregion } }