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