/// <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">カテゴリ検索用配列１</param>
/// <param name="paraArrWord2">カテゴリ検索用配列２（評価部署用）</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;
}
}
