/// <summary> /// 回答情報更新 /// </summary> /// <param name="FMC_mc">FMC/mc 1:FMC、2:mc</param> /// <param name="KAIHATU_ID">開発符号</param> /// <param name="BY_PU">BYPU区分</param> /// <param name="EVENT_NO">イベントNO</param> /// <param name="FOLLOW_NO">フォロー管理No</param> /// <param name="KA_CODE">課・主査コード</param> /// <param name="SYSTEM_NO">システム管理番号</param> /// <param name="SINDO">進度</param> /// <param name="TAIOU_NAIYO">対応内容</param> /// <returns>更新結果</returns> public Boolean UpdateFollowData(String FMC_mc, String KAIHATU_ID, String BY_PU, String EVENT_NO, String FOLLOW_NO, String KA_CODE, String SYSTEM_NO, String SINDO, String TAIOU_NAIYO, String UserId) { Boolean result = false; // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); //トランザクションの開始 SqlTransaction sqlTran = connDb.BeginTransaction(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.Transaction = sqlTran; // 2016.04.20 Kanda 適用有無設計の更新を廃止 // cmd.CommandText = "UPDATE T_FOLLOW_DATA SET TEKIYO_SEKKEI = @TEKIYO, SINDO = @SINDO, TAIOU_NAIYO = @TAIOU_NAIYO ,"; cmd.CommandText = "UPDATE T_FOLLOW_DATA SET SINDO = @SINDO, TAIOU_NAIYO = @TAIOU_NAIYO ,"; cmd.CommandText += "UPDATE_USER = '******', UPDATE_YMD = getdate() "; cmd.CommandText += "WHERE FMC_mc = '" + FMC_mc + "' AND KAIHATU_ID = '" + KAIHATU_ID + "' AND "; cmd.CommandText += "BY_PU = '" + BY_PU + "' AND EVENT_NO = '" + EVENT_NO + "' AND "; cmd.CommandText += "FOLLOW_NO = '" + FOLLOW_NO + "' AND KA_CODE = '" + KA_CODE + "' "; cmd.CommandText += "AND SYSTEM_NO = " + SYSTEM_NO; // 2016.04.20 Kanda 適用有無設計の更新を廃止 // cmd.Parameters.AddWithValue("@TEKIYO", TEKIYO); cmd.Parameters.AddWithValue("@SINDO", SINDO); cmd.Parameters.AddWithValue("@TAIOU_NAIYO", TAIOU_NAIYO); // コマンドを実行 if (cmd.ExecuteNonQuery() == 1) { // コミット sqlTran.Commit(); } else { result = true; } return(result); } catch (Exception ex) { throw ex; } finally { sqlTran.Dispose(); sqlTran = null; connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// フォロー情報取得 /// </summary> /// <param name="FMC_mc">FMC/mc区分</param> /// <param name="kaihatu_id">開発符号</param> /// <param name="by_pu">BYPU区分</param> /// <param name="event_no">イベントNO</param> /// <param name="follow_no">フォロー管理No</param> /// <param name="ka_code">課・主査コード</param> /// <param name="system_no">システム管理番号</param> /// <returns>取得結果情報</returns> public DataTable SelectFollowDataOtherDept(String FMC_mc, String kaihatu_id, String by_pu, String event_no, String follow_no, String ka_code, String system_no) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT "; cmd.CommandText += "FLW.KA_CODE, FLW.SINDO, FLW.TAIOU_NAIYO "; cmd.CommandText += "FROM T_FOLLOW_DATA AS FLW "; cmd.CommandText += "LEFT OUTER JOIN M_BUSYO_SEKKEI AS SKK "; cmd.CommandText += "ON FLW.KA_CODE = SKK.KA_CODE "; cmd.CommandText += "LEFT OUTER JOIN M_BUSYO_HYOUKA AS HYK "; cmd.CommandText += "ON FLW.KA_CODE = HYK.KA_CODE "; cmd.CommandText += "WHERE FLW.FMC_mc = '" + FMC_mc + "' AND FLW.KAIHATU_ID = '" + kaihatu_id + "' AND FLW.BY_PU = '" + by_pu + "' AND FLW.EVENT_NO = '" + event_no + "' AND FLW.FOLLOW_NO = '" + follow_no + "' "; cmd.CommandText += "AND FLW.KA_CODE <> '" + ka_code + "' AND FLW.SYSTEM_NO = '" + system_no + "' "; cmd.CommandText += "ORDER BY CASE WHEN SKK.KA_CODE IS NULL THEN 1 ELSE 0 END "; cmd.CommandText += ",FLW.KA_CODE "; cmd.CommandText += ",CASE WHEN HYK.KA_CODE IS NULL THEN 1 ELSE 0 END "; cmd.CommandText += ",HYK.KA_CODE;"; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// フォロー情報取得 /// </summary> /// <param name="FMC_mc">FMC/mc区分</param> /// <param name="kaihatu_id">開発符号</param> /// <param name="by_pu">BYPU区分</param> /// <param name="event_no">イベントNO</param> /// <param name="Type">フォロー管理No</param> /// <param name="ka_code">課・主査コード</param> /// <param name="Type">システム管理番号</param> /// <returns>取得結果情報</returns> public DataTable SelectFollowData(String FMC_mc, String kaihatu_id, String by_pu, String event_no, String follow_no, String ka_code, String system_no) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT "; cmd.CommandText += "TRB.* , "; cmd.CommandText += "FLW.HEARING, FLW.SINDO, FLW.TAIOU_NAIYO, FORMAT(FLW.UPDATE_YMD, 'yyyyMMddHHmmss') AS SHARED_YMD "; cmd.CommandText += "FROM T_FOLLOW_DATA AS FLW "; cmd.CommandText += "INNER JOIN T_TROUBLE_DATA AS TRB "; cmd.CommandText += "ON FLW.SYSTEM_NO = TRB.SYSTEM_NO "; cmd.CommandText += "WHERE FLW.FMC_mc = '" + FMC_mc + "' AND FLW.KAIHATU_ID = '" + kaihatu_id + "' AND FLW.EVENT_NO = '" + event_no + "' AND FLW.FOLLOW_NO = '" + follow_no + "' "; cmd.CommandText += "AND FLW.KA_CODE = '" + ka_code + "' AND FLW.SYSTEM_NO = " + system_no; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// リプロ20マスタ存在チェック /// </summary> /// <param name="FollowNo">フォローNo</param> /// <returns>処理結果情報</returns> public Boolean SelectRipro20(String followNo) { DataTable wkTable = new DataTable(); Boolean result = false; // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT FOLLOW_NO FROM M_RIPRO20 WHERE FOLLOW_NO = @follow_no"; cmd.Parameters.AddWithValue("@follow_no", followNo); // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む wkTable.Load(reader); reader.Close(); if (wkTable.Rows.Count > 0) { result = true; // 存在 } return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; wkTable.Dispose(); } }
// 2017/07/14 Add Start #region 課コードから部コードを取得 /// <summary> /// 課コードから部コードを取得 /// </summary> /// <param name="Type">課・主査コード</param> /// <returns>部コード</returns> public DataTable selectBuCode(String ka_code) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT DISTINCT(BU_CODE) AS BU_CODE "; cmd.CommandText += "FROM M_BUSYO_SEKKEI "; cmd.CommandText += "WHERE KA_CODE = '" + ka_code + "' "; cmd.CommandText += "UNION "; cmd.CommandText += "SELECT DISTINCT(BU_CODE) AS BU_CODE "; cmd.CommandText += "FROM M_BUSYO_HYOUKA "; cmd.CommandText += "WHERE KA_CODE = '" + ka_code + "' "; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
// 2017/07/14 Add Start #region フォロー対象部署一覧取得 /// <summary> /// フォロー対象部署一覧取得 /// </summary> /// <param name="FMC_mc">FMC/mc区分</param> /// <param name="Type">開発符号</param> /// <param name="Type">BYPU区分</param> /// <param name="Type">イベントNO</param> /// <returns>取得結果情報</returns> public DataTable selectKaCodeFollowDataList(String FMC_mc, String kaihatu_id, String by_pu, String event_no) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT "; cmd.CommandText += "DISTINCT(KA_CODE) AS KA_CODE "; // 課コード cmd.CommandText += "FROM T_FOLLOW_DATA AS FLW "; cmd.CommandText += "WHERE FLW.FMC_mc = '" + FMC_mc + "' AND FLW.KAIHATU_ID = '" + kaihatu_id + "' AND FLW.EVENT_NO = '" + event_no + "' "; cmd.CommandText += " AND FLW.TEKIYO_SQB = '*' AND FLW.TEKIYO_SEKKEI = '*' "; // 20160322 INS フォロー展開コピー対応 cmd.CommandText += "ORDER BY FLW.KA_CODE ASC"; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
// 2017/07/14 Add Start #region 展開区分取得 /// <summary> /// 展開区分取得 /// </summary> /// <param name="FMC_mc">FMC/mc区分</param> /// <param name="kaihatu_id">開発符号</param> /// <param name="by_pu">BYPU区分</param> /// <param name="event_no">イベントNO</param> /// <returns>展開区分</returns> public DataTable selectTenkaiKbn(String FMC_mc, String kaihatu_id, String by_pu, String event_no) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT "; cmd.CommandText += "EVT.TENKAI_KBN AS TENKAI_KBN "; cmd.CommandText += "FROM T_EVENT_DATA EVT "; cmd.CommandText += "WHERE EVT.FMC_mc = '" + FMC_mc + "' AND EVT.KAIHATU_ID = '" + kaihatu_id + "' AND EVT.BY_PU = '" + by_pu + "' AND EVT.EVENT_NO = '" + event_no + "';"; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// おすすめTOP10情報取得 /// </summary> /// <param name="">無し</param> /// <returns>処理結果情報</returns> public DataTable SelectRecommendList() { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT RECOMMEND_WORD FROM M_RECOMMEND ORDER BY SEQ"; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// 各マスタ情報取得 /// </summary> /// <param name="Type">種類</param> /// <param name="BYPU">部署表示用BY/PU</param> /// <returns>処理結果情報</returns> public DataTable SelectMasterList(String Type, String BYPU) { DataTable result = new DataTable(); String strSql = ""; switch (Type) { case "31": // 部署・設計 strSql = "SELECT case when RTRIM(KA_CODE) = RTRIM(BU_CODE) then RTRIM(BU_CODE) else RTRIM(KA_CODE) end as ID,"; strSql += " case when RTRIM(KA_CODE) = RTRIM(BU_CODE) then RTRIM(BU_CODE) else RTRIM(BU_CODE) + '/' + RTRIM(KA_CODE) end as NAME"; strSql += " FROM M_BUSYO_SEKKEI WHERE BY_PU = '" + BYPU + "' ORDER BY SEQ"; break; case "32": // 部署・評価 strSql = "SELECT case when RTRIM(KA_CODE) = RTRIM(BU_CODE) then RTRIM(BU_CODE) else RTRIM(KA_CODE) end as ID,"; strSql += " case when RTRIM(KA_CODE) = RTRIM(BU_CODE) then RTRIM(BU_CODE) else RTRIM(BU_CODE) + '/' + RTRIM(KA_CODE) end as NAME"; strSql += " FROM M_BUSYO_HYOUKA WHERE BY_PU = '" + BYPU + "' ORDER BY SEQ"; break; case Const.Def.DefTYPE_PARTS: // 部品・部位 システム strSql = "SELECT "; strSql += "DISTINCT RTRIM(SYSTEM_NO) AS SYSTEM_NO, "; strSql += "RTRIM(SYSTEM_NO) + ',,,' + RTRIM(SYSTEM_NAME) AS ID, "; strSql += "RTRIM(SYSTEM_NAME) AS NAME "; strSql += "FROM M_PARTS "; strSql += "ORDER BY SYSTEM_NO;"; break; case Const.Def.DefTYPE_KAIHATU: // 開発符号 strSql = "SELECT KAIHATU_ID + ',' + KAIHATU_FUGO AS ID, SYAKEI + '/' + KAIHATU_FUGO AS NAME"; strSql += " FROM M_DEVELOPMENTSIGN "; strSql += " WHERE FMC_MC = '1' ORDER BY SEQ"; break; case Const.Def.DefTYPE_GENSYO: case Const.Def.DefTYPE_SGENSYO: // 現象(分類) // 現象(制御系) strSql = "SELECT GENSYO_NO + ',' + GENSYO_NAME AS ID, GENSYO_NAME AS NAME"; strSql += " FROM M_GENSYO ORDER BY SEQ"; break; case Const.Def.DefTYPE_GENIN: // 原因(分類) strSql = "SELECT CASE_NO + ',' + CASE_NAME AS ID, CASE_NAME AS NAME"; strSql += " FROM M_CASE ORDER BY SEQ"; break; case Const.Def.DefTYPE_SYAKATA: // 車型特殊 strSql = "SELECT KATA_NO + ',' + KATA_NAME AS ID, KATA_NAME AS NAME"; strSql += " FROM M_SYAKATA ORDER BY SEQ"; break; case Const.Def.DefTYPE_SYOUIN: // 要因(制御系) strSql = "SELECT FACTOR_NO + ',' + FACTOR_NAME AS ID, FACTOR_NAME AS NAME"; strSql += " FROM M_FACTOR ORDER BY SEQ"; break; case Const.Def.DefTYPE_EGTM: // EGTM形式 strSql = "SELECT EGTM_NO + ',' + EGTM_NAME AS ID, EGTM_NAME AS NAME"; strSql += " FROM M_EGTM ORDER BY SEQ"; break; case Const.Def.DefTYPE_TOP40: // TOP40 strSql = "SELECT FOLLOW_NO AS ID, FOLLOW_NO AS NAME"; strSql += " FROM M_TOP40 ORDER BY SEQ"; break; case Const.Def.DefTYPE_RIPRO20: // リプロ20 strSql = "SELECT FOLLOW_NO AS ID, FOLLOW_NO AS NAME"; strSql += " FROM M_RIPRO20 ORDER BY SEQ"; break; } // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = strSql; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// 部品・部位情報取得 /// </summary> /// <param name="BY">BY</param> /// <param name="PU">PU</param> /// <param name="dtSystem">システム選択リスト</param> /// <returns>処理結果情報</returns> public DataTable SelectPartsList(String BY, String PU, ArrayList dtSystem) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { String where = ""; // SQL作成 if (BY == "1" && PU == "1") { where = "BY_PU IN ('BY','PU')"; } else { if (BY == "1" && PU == "0") { where = "BY_PU = 'BY'"; } else { where = "BY_PU = 'PU'"; } } where += " AND RTRIM(BUHIN_NO) <> '' AND SYSTEM_NO IN ("; String[] strArrayData; for (int i = 0; i < dtSystem.Count; i++) { if (i != 0) { where += ","; } strArrayData = dtSystem[i].ToString().Trim().Split(','); where += "'" + strArrayData[0].ToString() + "'"; } SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT "; cmd.CommandText += "CASE "; cmd.CommandText += "WHEN RTRIM(KOBUHIN_NO) = '' THEN"; cmd.CommandText += " RTRIM(SYSTEM_NO) + ',' + RTRIM(BUHIN_NO) + ',,' + RTRIM(SYSTEM_NAME) + '/' + RTRIM(BUHIN_NAME)"; cmd.CommandText += "ELSE"; // 20170304 START k-ohmatsuzawa 部品検索結果の"|"を"/"に変更 //cmd.CommandText += " RTRIM(SYSTEM_NO) + ',' + RTRIM(BUHIN_NO) + ',' + RTRIM(KOBUHIN_NO) + ',' + RTRIM(SYSTEM_NAME) + '/' + RTRIM(BUHIN_NAME) + '|' + RTRIM(KOBUHIN_NAME)"; cmd.CommandText += " RTRIM(SYSTEM_NO) + ',' + RTRIM(BUHIN_NO) + ',' + RTRIM(KOBUHIN_NO) + ',' + RTRIM(SYSTEM_NAME) + '/' + RTRIM(BUHIN_NAME) + '/' + RTRIM(KOBUHIN_NAME)"; // 20170304 END k-ohmatsuzawa cmd.CommandText += "END AS ID,"; cmd.CommandText += "CASE "; cmd.CommandText += "WHEN RTRIM(KOBUHIN_NO) = '' THEN"; cmd.CommandText += " RTRIM(SYSTEM_NAME) + '/' + RTRIM(BUHIN_NAME) "; cmd.CommandText += "ELSE"; cmd.CommandText += " RTRIM(SYSTEM_NAME) + '/' + RTRIM(BUHIN_NAME) + '/' + RTRIM(KOBUHIN_NAME) "; cmd.CommandText += "END AS NAME "; cmd.CommandText += "FROM M_PARTS "; cmd.CommandText += "WHERE " + where; cmd.CommandText += ") ORDER BY ID;"; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// フォロー情報取得 /// </summary> /// <param name="FMC_mc">FMC/mc 1:FMC、2:mc</param> /// <param name="Type">種類 1:現在、2:過去</param> /// <returns>取得結果情報</returns> public DataTable SelectFollowList(String FMC_mc, String Type) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT RTRIM(a.FMC_mc) + ',' + RTRIM(a.KAIHATU_ID) + ','"; cmd.CommandText += " + RTRIM(a.BY_PU) + ',' + RTRIM(a.EVENT_NO) + ',' + "; cmd.CommandText += "RTRIM(a.BY_PU) + '-' + "; cmd.CommandText += "CASE WHEN RTRIM(a.FMC_mc) = '1' THEN 'FMC' "; cmd.CommandText += "ELSE 'mc' END + '-' + "; cmd.CommandText += "RTRIM(b.KAIHATU_FUGO) + '-' + RTRIM(c.EVENT_NAME) + '-'"; cmd.CommandText += " + CONVERT(VARCHAR, a.YMD_KAISI,111) + '~'"; cmd.CommandText += " + CONVERT(VARCHAR, a.YMD_END,111) + ',' + "; cmd.CommandText += "RTRIM(c.EVENT_NAME) AS EVENT_CODE, "; cmd.CommandText += "RTRIM(a.BY_PU) + '-' + RTRIM(b.KAIHATU_FUGO) + '-' + RTRIM(c.EVENT_NAME) + '-'"; cmd.CommandText += " + CONVERT(VARCHAR, a.YMD_KAISI,111) + '~'"; cmd.CommandText += " + CONVERT(VARCHAR, a.YMD_END,111) AS FOLLOW_NAME "; cmd.CommandText += ", b.SEQ AS FUGO_SEQ, c.SEQ AS EVENT_SEQ "; cmd.CommandText += "FROM T_EVENT_DATA AS a "; cmd.CommandText += "LEFT JOIN M_DEVELOPMENTSIGN AS b "; cmd.CommandText += "ON a.KAIHATU_ID = b.KAIHATU_ID "; cmd.CommandText += "LEFT JOIN M_EVENT AS c "; cmd.CommandText += "ON a.EVENT_NO = c.EVENT_NO "; cmd.CommandText += "WHERE a.FMC_mc = '" + FMC_mc + "' AND "; // 現在情報取得 if (Type == Const.Def.DefTYPE_Now) { cmd.CommandText += "CONVERT(VARCHAR, a.YMD_KAISI,111) <= CONVERT(VARCHAR,CURRENT_TIMESTAMP,111) AND "; cmd.CommandText += "CONVERT(VARCHAR, a.YMD_END,111) >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,111) "; } // 過去情報取得 else { cmd.CommandText += "CONVERT(VARCHAR, a.YMD_END,111) < CONVERT(VARCHAR,CURRENT_TIMESTAMP,111) "; } cmd.CommandText += "ORDER BY a.BY_PU, FUGO_SEQ, EVENT_SEQ, a.YMD_KAISI"; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// フォローダウンロード情報取得 /// </summary> /// <param name="FMC_mc">FMC/mc区分</param> /// <param name="Type">開発符号</param> /// <param name="Type">BYPU区分</param> /// <param name="Type">イベントNO</param> /// <param name="Type">課・主査コード</param> /// <returns>取得結果情報</returns> public DataTable SelectFollowDownList(String FMC_mc, String kaihatu_id, String by_pu, String event_no, String ka_code) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT CONVERT(varchar,ROW_NUMBER() OVER(ORDER BY K.BY_PU ASC, K.FOLLOW_NO ASC, K.FOLLOW_EDA DESC, K.KA_CODE ASC, SYSTEM_NO ASC)) AS ROWID,"; cmd.CommandText += " K.RANK, K.SAIHATU, K.RSC,"; // ランク、再発、RSC cmd.CommandText += "CASE WHEN RTRIM(SYUMU_SEIZO) = '○' AND RTRIM(SYUMU_GAISEI) != '○' THEN '製造' "; cmd.CommandText += " ELSE CASE WHEN RTRIM(SYUMU_SEIZO) != '○' AND RTRIM(SYUMU_GAISEI) = '○' THEN '外製' "; cmd.CommandText += " ELSE '設計' "; cmd.CommandText += " END "; cmd.CommandText += "END AS SYUMU, "; // 主務部署 cmd.CommandText += "CASE WHEN RTRIM(K.BUHIN_NAME1) <> '' THEN RTRIM(K.BUHIN_NAME1) "; cmd.CommandText += "ELSE"; cmd.CommandText += " RTRIM(K.BUHIN_NAME2) "; cmd.CommandText += "END AS BUHIN_NAME, "; // 部品名 cmd.CommandText += "K.BUNRUI_GENSYO_NAME, "; // マスタの現象 cmd.CommandText += "K.SEIGYO_FACTOR_NAME, "; // マスタの制御系要因 cmd.CommandText += "K.KOUMOKU_KANRI_NO, K.KOUMOKU, "; // 項目管理番号、項目 cmd.CommandText += "K.GENIN, K.FOLLOW_INFO, "; // 原因、進捗 cmd.CommandText += "K.TAISAKU, "; // 対策 cmd.CommandText += "K.KAIHATU_MIHAKKEN_RIYU, "; // 開発時の流出要因 cmd.CommandText += "K.SQB_KANTEN, "; // 確認の観点 cmd.CommandText += "K.SAIHATU_SEKKEI, "; // (再発防止策)設計面 cmd.CommandText += "K.SAIHATU_HYOUKA, "; // (再発防止策)評価面 cmd.CommandText += "K.SIRYOU_NO1, K.SIRYOU_NO2, K.SIRYOU_NO3, K.SIRYOU_NO4, K.SIRYOU_NO5, K.KANREN_KANRI_NO, "; // 資料№一覧、関連管理No cmd.CommandText += "K.KA_CODE, K.SORT_KA_CODE, "; // フォロー情報・課コード cmd.CommandText += "K.TEKIYO_SQB, K.TEKIYO_SEKKEI, K.HEARING, K.SINDO, K.TAIOU_NAIYO, "; // フォロー情報 cmd.CommandText += "K.FOLLOW_NO, CONVERT(VARCHAR, K.SYSTEM_NO) AS SYSTEM_NO "; cmd.CommandText += "FROM ("; cmd.CommandText += "SELECT TRB.RANK, TRB.SAIHATU, TRB.RSC,TRB.SYUMU_SEIZO, TRB.SYUMU_GAISEI, TRB.BUHIN_NAME1, TRB.BUHIN_NAME2, TRB.BUNRUI_GENSYO_NAME, TRB.SEIGYO_FACTOR_NAME, TRB.KOUMOKU_KANRI_NO, TRB.KOUMOKU, TRB.GENIN, TRB.FOLLOW_INFO, TRB.TAISAKU, TRB.KAIHATU_MIHAKKEN_RIYU, TRB.SQB_KANTEN, TRB.SAIHATU_SEKKEI, TRB.SAIHATU_HYOUKA, TRB.SIRYOU_NO1, TRB.SIRYOU_NO2, TRB.SIRYOU_NO3, TRB.SIRYOU_NO4, TRB.SIRYOU_NO5, TRB.KANREN_KANRI_NO, FLW.KA_CODE, FLW.KA_CODE AS SORT_KA_CODE, FLW.TEKIYO_SQB, FLW.TEKIYO_SEKKEI, FLW.HEARING, FLW.SINDO, FLW.TAIOU_NAIYO, FLW.FOLLOW_NO, FLW.FOLLOW_EDA, FLW.BY_PU, CONVERT(VARCHAR, FLW.SYSTEM_NO) AS SYSTEM_NO "; cmd.CommandText += "FROM T_FOLLOW_DATA AS FLW "; cmd.CommandText += "INNER JOIN T_TROUBLE_DATA AS TRB "; //cmd.CommandText += "ON FLW.SYSTEM_NO = TRB.SYSTEM_NO AND TRB.SAIHATU <> '類似' "; cmd.CommandText += "ON FLW.SYSTEM_NO = TRB.SYSTEM_NO "; cmd.CommandText += "WHERE FLW.FMC_mc = '" + FMC_mc + "' AND FLW.KAIHATU_ID = '" + kaihatu_id + "' AND FLW.EVENT_NO = '" + event_no + "' "; cmd.CommandText += " AND FLW.TEKIYO_SQB = '*' AND FLW.TEKIYO_SEKKEI = '*' "; // 20160322 INS フォロー展開コピー対応 if (ka_code != "ALL") // 個別課指定 { cmd.CommandText += "AND FLW.KA_CODE = '" + ka_code + "' "; } cmd.CommandText += "UNION "; cmd.CommandText += "SELECT TB2.RANK, TB2.SAIHATU, TB2.RSC,TB2.SYUMU_SEIZO, TB2.SYUMU_GAISEI, TB2.BUHIN_NAME1, TB2.BUHIN_NAME2, TB2.BUNRUI_GENSYO_NAME, TB2.SEIGYO_FACTOR_NAME, TB2.KOUMOKU_KANRI_NO, TB2.KOUMOKU, TB2.GENIN, TB2.FOLLOW_INFO, TB2.TAISAKU, TB2.KAIHATU_MIHAKKEN_RIYU, TB2.SQB_KANTEN, TB2.SAIHATU_SEKKEI, TB2.SAIHATU_HYOUKA, TB2.SIRYOU_NO1, TB2.SIRYOU_NO2, TB2.SIRYOU_NO3, TB2.SIRYOU_NO4, TB2.SIRYOU_NO5, TB2.KANREN_KANRI_NO, '' AS KA_CODE ,'ZZZZZ' AS SORT_KA_CODE, '' AS TEKIYO_SQB, '' AS TEKIYO_SEKKEI, '' AS HEARING, '' AS SINDO, '' AS TAIOU_NAIYO, TB2.FOLLOW_NO, TB2.FOLLOW_EDA, TB2.BY_PU, CONVERT(VARCHAR, TB2.SYSTEM_NO) AS SYSTEM_NO "; cmd.CommandText += "FROM T_TROUBLE_DATA AS TB2 "; cmd.CommandText += "INNER JOIN ("; cmd.CommandText += "SELECT FOLLOW_NO, FOLLOW_EDA FROM T_FOLLOW_DATA "; cmd.CommandText += "WHERE FMC_mc = '" + FMC_mc + "' AND KAIHATU_ID = '" + kaihatu_id + "' AND EVENT_NO = '" + event_no + "' "; cmd.CommandText += " AND TEKIYO_SQB = '*' AND TEKIYO_SEKKEI = '*' "; // 20160322 INS フォロー展開コピー対応 if (ka_code != "ALL") // 個別課指定 { cmd.CommandText += "AND KA_CODE = '" + ka_code + "' "; } cmd.CommandText += "GROUP BY FOLLOW_NO ,FOLLOW_EDA) AS FL2 "; cmd.CommandText += "ON FL2.FOLLOW_NO = TB2.FOLLOW_NO AND FL2.FOLLOW_EDA > TB2.FOLLOW_EDA"; cmd.CommandText += ") AS K "; //cmd.CommandText += "ORDER BY K.FOLLOW_NO, K.FOLLOW_EDA DESC, K.SORT_KA_CODE"; cmd.CommandText += "ORDER BY K.BY_PU ASC, K.FOLLOW_NO ASC, K.FOLLOW_EDA DESC, K.KA_CODE ASC, SYSTEM_NO ASC "; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// フォロー情報取得 /// </summary> /// <param name="FMC_mc">FMC/mc区分</param> /// <param name="Type">開発符号</param> /// <param name="Type">BYPU区分</param> /// <param name="Type">イベントNO</param> /// <param name="Type">課・主査コード</param> /// <returns>取得結果情報</returns> public DataTable SelectFollowDataList(String FMC_mc, String kaihatu_id, String by_pu, String event_no, String ka_code) { DataTable result = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "SELECT "; cmd.CommandText += "CONVERT(varchar,ROW_NUMBER() OVER(ORDER BY FLW.BY_PU ASC, FLW.FOLLOW_NO ASC, TRB.FOLLOW_EDA DESC)) "; cmd.CommandText += " + '<br>' +TRB.RANK + '<br>' + TRB.SAIHATU + '<br>' + TRB.RSC + '<br>' + "; cmd.CommandText += "CASE WHEN RTRIM(SYUMU_SEIZO) = '○' AND RTRIM(SYUMU_GAISEI) != '○' THEN '製造' "; cmd.CommandText += " ELSE CASE WHEN RTRIM(SYUMU_SEIZO) != '○' AND RTRIM(SYUMU_GAISEI) = '○' THEN '外製' "; cmd.CommandText += " ELSE '設計' "; cmd.CommandText += " END "; cmd.CommandText += "END AS RANK, "; cmd.CommandText += "CASE WHEN RTRIM(TRB.BUHIN_NAME1) <> '' THEN RTRIM(TRB.BUHIN_NAME1) "; cmd.CommandText += "ELSE"; cmd.CommandText += " RTRIM(TRB.BUHIN_NAME2) "; cmd.CommandText += "END AS BUHIN_NAME, "; // 部品名 cmd.CommandText += "TRB.BUNRUI_GENSYO_NAME, "; // マスタの現象 cmd.CommandText += "TRB.SEIGYO_FACTOR_NAME, "; // マスタの制御系要因 cmd.CommandText += "'(' + TRB.KOUMOKU_KANRI_NO + ')<br>' + TRB.KOUMOKU AS KOUMOKU, "; // 項目管理番号+項目名 cmd.CommandText += "TRB.GENIN, "; // 原因 cmd.CommandText += "TRB.KAIHATU_MIHAKKEN_RIYU, "; // 開発時の流出要因 cmd.CommandText += "TRB.SQB_KANTEN, "; // 確認の観点 cmd.CommandText += "TRB.SAIHATU_SEKKEI, "; // (再発防止策)設計面 cmd.CommandText += "TRB.SAIHATU_HYOUKA, "; // (再発防止策)評価面 cmd.CommandText += "FLW.TEKIYO_SQB, FLW.TEKIYO_SEKKEI, FLW.HEARING, FLW.SINDO, FLW.TAIOU_NAIYO, "; // フォロー情報 cmd.CommandText += "FLW.FMC_mc + ',' + FLW.KAIHATU_ID + ',' + FLW.BY_PU + ',' + FLW.EVENT_NO + ',' + FLW.FOLLOW_NO + ',' + FLW.KA_CODE + ',' + CONVERT(VARCHAR, FLW.SYSTEM_NO) AS FOLLOW_KEY "; cmd.CommandText += "FROM T_FOLLOW_DATA AS FLW "; cmd.CommandText += "INNER JOIN T_TROUBLE_DATA AS TRB "; //cmd.CommandText += "ON FLW.SYSTEM_NO = TRB.SYSTEM_NO AND TRB.SAIHATU <> '類似' "; cmd.CommandText += "ON FLW.SYSTEM_NO = TRB.SYSTEM_NO "; cmd.CommandText += "WHERE FLW.FMC_mc = '" + FMC_mc + "' AND FLW.KAIHATU_ID = '" + kaihatu_id + "' AND FLW.EVENT_NO = '" + event_no + "' AND FLW.KA_CODE = '" + ka_code + "' "; cmd.CommandText += " AND FLW.TEKIYO_SQB = '*' AND FLW.TEKIYO_SEKKEI = '*' "; // 20160322 INS フォロー展開コピー対応 cmd.CommandText += "ORDER BY FLW.BY_PU ASC, FLW.FOLLOW_NO ASC, TRB.FOLLOW_EDA DESC"; // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); return(result); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
//20170201 機能改善 START #region カテゴリ名称取得 /// <summary> /// カテゴリ名称取得 /// </summary> /// <param name="strType">カテゴリ種類</param> /// <param name="strCode">カテゴリコード</param> /// <returns>結果データテーブル</returns> /// <remarks></remarks> public DataTable SelectCategoryName(String strType, String strCode) { DataTable work_t = new DataTable(); // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; // 20170304 START k-ohmatsuzawa EXCEL出力のカテゴリ検索条件の表示修正 //switch (strType) //{ // case Const.Def.DefTYPE_PARTS: // return work_t; // case Const.Def.DefTYPE_KAIHATU: // return work_t; // case Const.Def.DefTYPE_GENSYO: // return work_t; // case Const.Def.DefTYPE_GENIN: // return work_t; // case Const.Def.DefTYPE_SYAKATA: // return work_t; // case Const.Def.DefTYPE_SGENSYO: // return work_t; // case Const.Def.DefTYPE_SYOUIN: // return work_t; // case Const.Def.DefTYPE_EGTM: // cmd.CommandText = "SELECT "; // cmd.CommandText += "EGTM_NAME "; // cmd.CommandText += "FROM M_EGTM "; // cmd.CommandText += "WHERE EGTM_NO = @code "; // cmd.Parameters.AddWithValue("@code", strCode); // break; // default: // return work_t; //} switch (strType) { case Const.Def.DefTYPE_PARTS: switch (strCode.Length) { case 2: cmd.CommandText = "SELECT "; cmd.CommandText += "SYSTEM_NAME "; cmd.CommandText += "FROM M_PARTS "; cmd.CommandText += "WHERE BY_PU IN ('BY','PU') "; cmd.CommandText += "AND SYSTEM_NO = @code "; cmd.Parameters.AddWithValue("@code", strCode); break; case 4: cmd.CommandText = "SELECT "; cmd.CommandText += "SYSTEM_NAME + '/' + BUHIN_NAME "; cmd.CommandText += "FROM M_PARTS "; cmd.CommandText += "WHERE BY_PU IN ('BY','PU') "; cmd.CommandText += "AND SYSTEM_NO = @code "; cmd.CommandText += "AND BUHIN_NO = @code2 "; cmd.Parameters.AddWithValue("@code", strCode.Substring(0, 2)); cmd.Parameters.AddWithValue("@code2", strCode.Substring(2, 2)); break; case 6: cmd.CommandText = "SELECT "; cmd.CommandText += "SYSTEM_NAME + '/' + BUHIN_NAME + '/' + KOBUHIN_NAME "; cmd.CommandText += "FROM M_PARTS "; cmd.CommandText += "WHERE BY_PU IN ('BY','PU') "; cmd.CommandText += "AND SYSTEM_NO = @code "; cmd.CommandText += "AND BUHIN_NO = @code2 "; cmd.CommandText += "AND KOBUHIN_NO = @code3 "; cmd.Parameters.AddWithValue("@code", strCode.Substring(0, 2)); cmd.Parameters.AddWithValue("@code2", strCode.Substring(2, 2)); cmd.Parameters.AddWithValue("@code3", strCode.Substring(4, 2)); break; } break; case Const.Def.DefTYPE_KAIHATU: cmd.CommandText = "SELECT "; cmd.CommandText += "SYAKEI + '/' + KAIHATU_FUGO "; cmd.CommandText += "FROM M_DEVELOPMENTSIGN "; cmd.CommandText += "WHERE KAIHATU_ID = @code "; cmd.Parameters.AddWithValue("@code", strCode); break; case Const.Def.DefTYPE_GENSYO: case Const.Def.DefTYPE_SGENSYO: cmd.CommandText = "SELECT "; cmd.CommandText += "GENSYO_NAME "; cmd.CommandText += "FROM M_GENSYO "; cmd.CommandText += "WHERE GENSYO_NO = @code "; cmd.Parameters.AddWithValue("@code", strCode); break; case Const.Def.DefTYPE_GENIN: cmd.CommandText = "SELECT "; cmd.CommandText += "CASE_NAME "; cmd.CommandText += "FROM M_CASE "; cmd.CommandText += "WHERE CASE_NO = @code "; cmd.Parameters.AddWithValue("@code", strCode); break; case Const.Def.DefTYPE_SYAKATA: cmd.CommandText = "SELECT "; cmd.CommandText += "KATA_NAME "; cmd.CommandText += "FROM M_SYAKATA "; cmd.CommandText += "WHERE KATA_NO = @code "; cmd.Parameters.AddWithValue("@code", strCode); break; case Const.Def.DefTYPE_SYOUIN: cmd.CommandText = "SELECT "; cmd.CommandText += "FACTOR_NAME "; cmd.CommandText += "FROM M_FACTOR "; cmd.CommandText += "WHERE FACTOR_NO = @code "; cmd.Parameters.AddWithValue("@code", strCode); break; case Const.Def.DefTYPE_EGTM: cmd.CommandText = "SELECT "; cmd.CommandText += "EGTM_NAME "; cmd.CommandText += "FROM M_EGTM "; cmd.CommandText += "WHERE EGTM_NO = @code "; cmd.Parameters.AddWithValue("@code", strCode); break; default: return(work_t); } // 20170304 END k-ohmatsuzawa // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む work_t.Load(reader); reader.Close(); return(work_t); } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }
/// <summary> /// 過去トラ情報取得 /// </summary> /// <param name="Mode">モード:1:画面、2:Excel</param> /// <param name="Type">種類 カテゴリ検索の場合はnull</param> /// <param name="Moji">検索文字 カテゴリ検索の場合はnull</param> /// <param name="paraArrWord1">カテゴリ検索用配列1</param> /// <param name="paraArrWord2">カテゴリ検索用配列2(評価部署用)</param> /// <param name="Table">カテゴリデータテーブル(カテゴリ検索用)</param> /// <param name="paraCondition">キーワード検索用 And・Or検索条件 1:And、2:Or</param> /// <param name="paraCategoryCondition">カテゴリ検索用 And・Or検索条件 1:And、2:Or</param> // 20170719 Add /// <returns>取得結果情報</returns> //20170201 機能改善 START //public DataTable SelectTroubleList(String Mode, String Type, String Moji, ArrayList paraArrWord1, ArrayList paraArrWord2) //public DataTable SelectTroubleList(String Mode, String Type, String Moji, ArrayList paraArrWord1, ArrayList paraArrWord2, DataTable Table, String paraCondition) //20170201 機能改善 END public DataTable SelectTroubleList(String Mode, String Type, String Moji, ArrayList paraArrWord1, ArrayList paraArrWord2, DataTable Table, String paraCondition, String paraCategoryCondition) // 20170719 Add { String strSql = ""; String strWork = ""; String strWhere = ""; // 20170724 Add String strKeyWordStartKbn = ""; // 過去トラ検索結果 カテゴリ検索 0:一番目ではない、1:一番目 // 20170725 Add //String strMoji = ""; String[] strArrayData = null; DataTable result = new DataTable(); DataTable resultCopy = new DataTable(); // 20170721 Add strSql = "SELECT " + "\r\n"; if (Mode == Const.Def.DefMODE_DISP) { // 画面情報 strSql += "CONVERT(varchar,ROW_NUMBER() OVER(ORDER BY YMD_HENSYU DESC, BY_PU ASC, FOLLOW_NO ASC, FOLLOW_EDA DESC)) AS ROWID, " + "\r\n"; strSql += "RANK + '<br>' + SAIHATU + '<br>' + RSC + '<br>' + " + "\r\n"; strSql += "CASE WHEN RTRIM(SYUMU_SEIZO) = '○' AND RTRIM(SYUMU_GAISEI) != '○' THEN '製造' " + "\r\n"; strSql += " ELSE CASE WHEN RTRIM(SYUMU_SEIZO) != '○' AND RTRIM(SYUMU_GAISEI) = '○' THEN '外製' " + "\r\n"; strSql += " ELSE '設計' " + "\r\n"; strSql += " END " + "\r\n"; strSql += "END AS SYUMU, RANK, " + "\r\n"; strSql += "SYSTEM_NO, FOLLOW_INFO, KOUMOKU_KANRI_NO, KOUMOKU, " + "\r\n"; strSql += "FUGO_NAME1 + '<br>' + FUGO_NAME2 + '<br>' + FUGO_NAME3 + '<br>' + FUGO_NAME4 AS FUGO_NAME, " + "\r\n"; strSql += "GENSYO_NAIYO, JYOUKYO, GENIN, TAISAKU, KAIHATU_MIHAKKEN_RIYU, SQB_KANTEN, SAIHATU_SEKKEI, SAIHATU_HYOUKA, " + "\r\n"; strSql += "BUSYO_SEKKEI1 + '<br>' + BUSYO_SEKKEI2 + '<br>' + BUSYO_HYOUKA1 + '<br>' + BUSYO_HYOUKA2 AS BUSYO_CODE, " + "\r\n"; strSql += "RTRIM(SIRYOU_NO1) + '<br>' + RTRIM(SIRYOU_NO2)" + "\r\n"; strSql += "AS SIRYOU_NO, LINK_FOLDER_PATH," + "\r\n"; strSql += "BY_PU, FUGO_NO1, TRA.INSERT_YMD, YMD_HENSYU, INPUT_ROW, SEQ " + "\r\n"; } else { // Excel情報 strSql += "CONVERT(varchar,ROW_NUMBER() OVER(ORDER BY YMD_HENSYU DESC, BY_PU ASC, FOLLOW_NO ASC, FOLLOW_EDA DESC)) AS ROWID, " + "\r\n"; strSql += "RANK, SAIHATU, RSC, " + "\r\n"; strSql += "CASE WHEN RTRIM(SYUMU_SEIZO) = '○' AND RTRIM(SYUMU_GAISEI) != '○' THEN '製造' " + "\r\n"; strSql += " ELSE CASE WHEN RTRIM(SYUMU_SEIZO) != '○' AND RTRIM(SYUMU_GAISEI) = '○' THEN '外製' " + "\r\n"; strSql += " ELSE '設計' " + "\r\n"; strSql += " END " + "\r\n"; strSql += "END AS SYUMU, " + "\r\n"; strSql += "SYSTEM_NO, FOLLOW_INFO, KOUMOKU_KANRI_NO, KOUMOKU, " + "\r\n"; strSql += "FUGO_NAME1, FUGO_NAME2, FUGO_NAME3, FUGO_NAME4, FUGO_NAME5, " + "\r\n"; strSql += "GENSYO_NAIYO, JYOUKYO, GENIN, TAISAKU, KAIHATU_MIHAKKEN_RIYU, SQB_KANTEN, " + "\r\n"; strSql += "SAIHATU_SEKKEI, SAIHATU_HYOUKA, " + "\r\n"; strSql += "BUSYO_SEKKEI1, BUSYO_SEKKEI2, BUSYO_SEKKEI3, BUSYO_SEKKEI4, BUSYO_SEKKEI5, " + "\r\n"; strSql += "BUSYO_SEKKEI6, BUSYO_SEKKEI7, BUSYO_SEKKEI8, BUSYO_SEKKEI9, BUSYO_SEKKEI10, " + "\r\n"; strSql += "BUSYO_HYOUKA1, BUSYO_HYOUKA2, BUSYO_HYOUKA3, BUSYO_HYOUKA4, BUSYO_HYOUKA5, " + "\r\n"; strSql += "BUSYO_HYOUKA6, BUSYO_HYOUKA7, BUSYO_HYOUKA8, BUSYO_HYOUKA9, BUSYO_HYOUKA10, " + "\r\n"; strSql += "SIRYOU_NO1, SIRYOU_NO2, SIRYOU_NO3, SIRYOU_NO4, SIRYOU_NO5, KANREN_KANRI_NO, " + "\r\n"; strSql += "BY_PU, FUGO_NO1, TRA.INSERT_YMD, YMD_HENSYU, INPUT_ROW, SEQ " + "\r\n"; } strSql += "FROM T_TROUBLE_DATA TRA " + "\r\n"; strSql += "LEFT JOIN M_DEVELOPMENTSIGN DEVSIGN " + "\r\n"; strSql += "ON TRA.FUGO_NAME1 = DEVSIGN.KAIHATU_FUGO " + "\r\n"; //20170201 機能改善 START //strSql += "WHERE RANK <> 'X' " + "\r\n"; strSql += "WHERE "; // 20170724 Add WHERE句の内容はstrWhereに格納 // タイプにより取得条件を組立てる bool andFlg = false; if (Type == Const.Def.DefTYPE_WORD || Type == Const.Def.DefTYPE_TOP10) { // 文字列検索 // TOP10検索 strWork = ""; strWork = "@moji"; // 半角スペースでスプリット strArrayData = Moji.Trim().Split(' '); for (int i = 0; i < strArrayData.Length; i++) { if (i > 3) { break; } if (i != 0) { if (paraCondition == Const.Def.DefTYPE_AND) { strWhere += ") AND ("; } else { strWhere += ") OR ("; } } else { strWhere += "AND (("; } strWork = "@moji" + i.ToString(); strWhere += "KOUMOKU_KANRI_NO LIKE " + strWork + " OR BY_PU LIKE " + strWork + " OR " + "\r\n"; strWhere += "SYSTEM_NAME1 LIKE " + strWork + " OR SYSTEM_NAME2 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUHIN_NAME1 LIKE " + strWork + " OR BUHIN_NAME2 LIKE " + strWork + " OR " + "\r\n"; strWhere += "KOBUHIN_NAME1 LIKE " + strWork + " OR KOBUHIN_NAME2 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUNRUI_GENSYO_NAME LIKE " + strWork + " OR BUNRUI_CASE_NAME LIKE " + strWork + " OR " + "\r\n"; strWhere += "SEIGYO_UNIT_NAME LIKE " + strWork + " OR SEIGYO_GENSYO_NAME LIKE " + strWork + " OR " + "\r\n"; strWhere += "SEIGYO_FACTOR_NAME LIKE " + strWork + " OR KATA_NAME LIKE " + strWork + " OR " + "\r\n"; strWhere += "EGTM_NAME LIKE " + strWork + " OR HAIKI_NAME LIKE " + strWork + " OR " + "\r\n"; strWhere += "KOUMOKU LIKE " + strWork + " OR GENSYO_NAIYO LIKE " + strWork + " OR " + "\r\n"; strWhere += "JYOUKYO LIKE " + strWork + " OR GENIN LIKE " + strWork + " OR " + "\r\n"; strWhere += "TAISAKU LIKE " + strWork + " OR KAIHATU_MIHAKKEN_RIYU LIKE " + strWork + " OR " + "\r\n"; strWhere += "SAIHATU_SEKKEI LIKE " + strWork + " OR SAIHATU_HYOUKA LIKE " + strWork + " OR " + "\r\n"; strWhere += "SQB_KANTEN LIKE " + strWork + " OR BUSYO_SEKKEI1 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_SEKKEI2 LIKE " + strWork + " OR BUSYO_SEKKEI3 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_SEKKEI4 LIKE " + strWork + " OR BUSYO_SEKKEI5 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_SEKKEI6 LIKE " + strWork + " OR BUSYO_SEKKEI7 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_SEKKEI8 LIKE " + strWork + " OR BUSYO_SEKKEI9 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_SEKKEI10 LIKE " + strWork + " OR BUSYO_HYOUKA1 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_HYOUKA2 LIKE " + strWork + " OR BUSYO_HYOUKA3 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_HYOUKA4 LIKE " + strWork + " OR BUSYO_HYOUKA5 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_HYOUKA6 LIKE " + strWork + " OR BUSYO_HYOUKA7 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_HYOUKA8 LIKE " + strWork + " OR BUSYO_HYOUKA9 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUSYO_HYOUKA10 LIKE " + strWork + " OR FUGO_NAME1 LIKE " + strWork + " OR " + "\r\n"; strWhere += "FUGO_NAME2 LIKE " + strWork + " OR FUGO_NAME3 LIKE " + strWork + " OR " + "\r\n"; strWhere += "FUGO_NAME4 LIKE " + strWork + " OR FUGO_NAME5 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BLKNO1 LIKE " + strWork + " OR BLKNO2 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BLKNO3 LIKE " + strWork + " OR BUHIN_BANGO1 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUHIN_BANGO2 LIKE " + strWork + " OR BUHIN_BANGO3 LIKE " + strWork + " OR " + "\r\n"; strWhere += "BUHIN_BANGO4 LIKE " + strWork + " OR BUHIN_BANGO5 LIKE " + strWork + " OR " + "\r\n"; strWhere += "KANREN_KANRI_NO LIKE " + strWork + " OR KEYWORD LIKE " + strWork + " OR " + "\r\n"; strWhere += "JYUYO_HOUKI LIKE " + strWork + " " + "\r\n"; } strWhere += ")) " + "\r\n"; strKeyWordStartKbn = Const.Def.DefTYPE_FIRST; } // 部署・設計 // 設計部署 DataRow[] drBusyo; SetSelectTable(Table, out drBusyo, Const.Def.DefTYPE_BUSYO); if (drBusyo.Length > 0) { strWork = ""; for (int i = 0; drBusyo.Length > i; i++) { if (i != 0) { strWork += ","; } strWork += "'" + drBusyo[i]["ItemValue1"].ToString().Trim() + "'"; } // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND ("; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND (("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR ("; } // 20170719 Add End strWhere += "BUSYO_SEKKEI1 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI2 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI3 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI4 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI5 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI6 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI7 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI8 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI9 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_SEKKEI10 IN (" + strWork + ") " + "\r\n"; andFlg = true; } // 評価部署 DataRow[] drHyouka; SetSelectTable(Table, out drHyouka, Const.Def.DefTYPE_HYOUKA); if (drHyouka.Length > 0) { strWork = ""; for (int i = 0; drHyouka.Length > i; i++) { if (i != 0) { strWork += ","; } strWork += "'" + drHyouka[i]["ItemValue1"].ToString().Trim() + "'"; } if (!andFlg) { // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND ("; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND (("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR ("; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR ("; } // 20170719 Add End andFlg = true; } else { strWhere += "OR "; } strWhere += "BUSYO_HYOUKA1 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA2 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA3 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA4 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA5 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA6 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA7 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA8 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA9 IN (" + strWork + ") OR " + "\r\n"; strWhere += "BUSYO_HYOUKA10 IN (" + strWork + ") " + "\r\n"; } if (andFlg) { strWhere += ") "; } // 部品・部位 DataRow[] drParts; SetSelectTable(Table, out drParts, Const.Def.DefTYPE_PARTS); String[] strPartsArrayData = null; for (int i = 0; drParts.Length > i; i++) { if (i == 0) { // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND ("; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND (("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR ("; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR ("; } // 20170719 Add End } else { strWhere += " OR "; } strPartsArrayData = drParts[i]["ItemValue1"].ToString().Trim().Split(','); strWhere += "(SYSTEM_NO1 = '" + strPartsArrayData[0].ToString() + "'" + "\r\n"; if (!(strPartsArrayData[1].ToString() == "")) { strWhere += " AND BUHIN_NO1 = '" + strPartsArrayData[1].ToString() + "'" + "\r\n"; } if (!(strPartsArrayData[2].ToString() == "")) { strWhere += " AND KOBUHIN_NO1 = '" + strPartsArrayData[2].ToString() + "'" + "\r\n"; } strWhere += ") OR (SYSTEM_NO2 = '" + strPartsArrayData[0].ToString() + "'" + "\r\n"; if (!(strPartsArrayData[1].ToString() == "")) { strWhere += " AND BUHIN_NO2 = '" + strPartsArrayData[1].ToString() + "'" + "\r\n"; } if (!(strPartsArrayData[2].ToString() == "")) { strWhere += " AND KOBUHIN_NO2 = '" + strPartsArrayData[2].ToString() + "'" + "\r\n"; } strWhere += ") " + "\r\n"; if (i == drParts.Length - 1) { strWhere += ") " + "\r\n"; } } // 開発符号 DataRow[] drKaihatu; SetSelectTable(Table, out drKaihatu, Const.Def.DefTYPE_KAIHATU); if (drKaihatu.Length > 0) { SetWork(drKaihatu, out strWork); // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND ("; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND (("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR ("; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR ("; } // 20170719 Add End strWhere += "FUGO_NO1 IN (" + strWork + ") OR " + "\r\n"; strWhere += "FUGO_NO2 IN (" + strWork + ") OR " + "\r\n"; strWhere += "FUGO_NO3 IN (" + strWork + ") OR " + "\r\n"; strWhere += "FUGO_NO4 IN (" + strWork + ") OR " + "\r\n"; strWhere += "FUGO_NO5 IN (" + strWork + ") " + "\r\n"; strWhere += ") " + "\r\n"; } // 現象(分類) DataRow[] drGensyo; SetSelectTable(Table, out drGensyo, Const.Def.DefTYPE_GENSYO); if (drGensyo.Length > 0) { SetWork(drGensyo, out strWork); // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND ("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR "; } // 20170719 Add End strWhere += "BUNRUI_GENSYO_NO IN (" + strWork + ") " + "\r\n"; } // 現象(制御系) DataRow[] drSGensyo; SetSelectTable(Table, out drSGensyo, Const.Def.DefTYPE_SGENSYO); if (drSGensyo.Length > 0) { SetWork(drSGensyo, out strWork); // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND ("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR "; } // 20170719 Add End strWhere += "SEIGYO_GENSYO_NO IN (" + strWork + ") " + "\r\n"; } // 原因(分類) DataRow[] drGenin; SetSelectTable(Table, out drGenin, Const.Def.DefTYPE_GENIN); if (drGenin.Length > 0) { SetWork(drGenin, out strWork); // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND ("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR "; } // 20170719 Add End strWhere += "BUNRUI_CASE_NO IN (" + strWork + ") " + "\r\n"; } // 車型特殊 DataRow[] drSyakata; SetSelectTable(Table, out drSyakata, Const.Def.DefTYPE_SYAKATA); if (drSyakata.Length > 0) { SetWork(drSyakata, out strWork); // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND ("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR "; } // 20170719 Add End strWhere += "KATA_NO IN (" + strWork + ") " + "\r\n"; } // 要因(制御系) DataRow[] drSYouin; SetSelectTable(Table, out drSYouin, Const.Def.DefTYPE_SYOUIN); if (drSYouin.Length > 0) { SetWork(drSYouin, out strWork); // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND ("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR "; } // 20170719 Add End strWhere += "SEIGYO_FACTOR_NO IN (" + strWork + ") " + "\r\n"; } // EGTM形式 DataRow[] drEgtm; SetSelectTable(Table, out drEgtm, Const.Def.DefTYPE_EGTM); if (drEgtm.Length > 0) { SetWork(drEgtm, out strWork); // 20170719 Add Start if (paraCategoryCondition == Const.Def.DefTYPE_AND) { strWhere += "AND "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_FIRST) // 過去トラ検索結果 カテゴリの一番目 { strWhere += "AND ("; strKeyWordStartKbn = Const.Def.DefTYPE_NEXT; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの一番目ではない { strWhere += "OR "; } else if (paraCategoryCondition == Const.Def.DefTYPE_OR && Type == null) // 過去トラ検索 { strWhere += "OR "; } // 20170719 Add End strWhere += "EGTM_NO IN (" + strWork + ") " + "\r\n"; } if (Type != null && strKeyWordStartKbn == Const.Def.DefTYPE_NEXT) // 過去トラ検索結果 カテゴリの最後尾 { strWhere += ") " + "\r\n"; } //20170201 機能改善 END strWhere += "ORDER BY YMD_HENSYU DESC, BY_PU ASC, FOLLOW_NO ASC, FOLLOW_EDA DESC " + "\r\n"; //20170724 Add Start strSqlとstrWhereを結合 //カテゴリ検索でAND条件の場合、strWhereの"AND"以降の文字列を取得 //カテゴリ検索でOR条件の場合、strWhereの"OR "以降の文字列を取得 //TOP10検索、履歴キーワード検索の場合、paraCategoryConditionがnullになる //キーワード検索の場合、paraCategoryConditionが"0"になる if (paraCategoryCondition == Const.Def.DefTYPE_AND) //カテゴリ検索AND条件 { strSql += strWhere.Substring(3); //"AND"以降の文字列取得 } else if (paraCategoryCondition == Const.Def.DefTYPE_OR) //カテゴリ検索OR条件 { strSql += strWhere.Substring(3); //"OR "以降の文字列取得 過去トラ検索結果のキーワード検索で"AND"のケース対応 } else if (paraCategoryCondition == null) //TOP10検索、履歴キーワード検索 { strSql += strWhere.Substring(3); // strWhereは"AND"で始まる } else if (paraCategoryCondition == "0") //キーワード検索 { strSql += strWhere.Substring(3); // strWhereは"AND"で始まる } //20170724 Add End // DBオープン DataAccess.Common.SqlCommon dbBase = new DataAccess.Common.SqlCommon(); SqlConnection connDb = dbBase.dbOpen(); try { // SQL作成 SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = strSql; cmd.Parameters.Clear(); //20170201 機能改善 START CommonLogic bcom = new CommonLogic(); DebugParameter dp = new DebugParameter(); dp.Rank = 9; dp.FileName = System.IO.Path.GetFileName(this.GetType().Assembly.Location); dp.ClassName = this.GetType().FullName; dp.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; dp.Title = "過去トラ検索結果表示用SQL"; dp.Content = strSql; bcom.DebugProcess(dp); //20170201 機能改善 END if (Type == Const.Def.DefTYPE_WORD || Type == Const.Def.DefTYPE_TOP10) { // 文字列検索 // TOP10検索 //cmd.Parameters.AddWithValue("@moji", "%" + strMoji + "%"); for (int i = 0; i < strArrayData.Length; i++) { cmd.Parameters.AddWithValue("@moji" + i.ToString(), "%" + strArrayData[i].ToString().Trim() + "%"); } } // コマンドを実行 SqlDataReader reader = cmd.ExecuteReader(); // SqlDataReader からデータを DataTable に読み込む result.Load(reader); reader.Close(); // 20170724 Add Start if (result.Rows.Count > 0) { // resultからRANK != 'X'のデータを除く var resultItems = result.AsEnumerable() .Where(x => x["RANK"].ToString() != "X").CopyToDataTable(); resultCopy = resultItems.Copy(); } else if (result.Rows.Count == 0) { resultCopy = result.Copy(); } return(resultCopy); // 20170724 Add End } catch (Exception ex) { throw ex; } finally { connDb.Close(); connDb.Dispose(); connDb = null; } }