private void Button_SelectTaskChart_Click(object sender, EventArgs e)
{
#region エラー制御
// 日付FROMが日付型に変換できない場合はエラー
if (TextBox_Day_From_SelectTaskChart.Text != "" && !DateTime.TryParse(TextBox_Day_From_SelectTaskChart.Text, out DateTime dt_from))
{
MessageBox.Show("日付を正しく入力してください。",
"エラー",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
TextBox_Day_From_SelectTaskChart.Clear();
return;
}
// 日付TOが日付型に変換できない場合はエラー
if (TextBox_Day_To_SelectTaskChart.Text != "" && !DateTime.TryParse(TextBox_Day_To_SelectTaskChart.Text, out DateTime dt_to))
{
MessageBox.Show("日付を正しく入力してください。",
"エラー",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
TextBox_Day_To_SelectTaskChart.Clear();
return;
}
// 間隔をチェックしていない場合はエラー
if (!CheckBox_Day_SelectTaskChart.Checked && !CheckBox_Week_SelectTaskChart.Checked && !CheckBox_Month_SelectTaskChart.Checked)
{
MessageBox.Show("間隔はどれかにチェックが必要です。",
"エラー",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
TextBox_Day_To_SelectTaskChart.Clear();
return;
}
#endregion
// チャートの初期化
Chart_TaskBudget.Series.Clear();
// チャート名
string chartTitlePlus = "過剰見積り率";
string chartTitleMinus = "過少見積り率";
// チャートデータインスタンス生成
ChartData chartData = new ChartData();
// チャート検索フラグインスタンス生成
Flg_Chart_Select flgChartSelect = new Flg_Chart_Select()
{
USER_NO = TextBox_UserNo.Text,
DAY_FROM = TextBox_Day_From_SelectTaskChart.Text == "" ? new DateTime(0) : DateTime.Parse(TextBox_Day_From_SelectTaskChart.Text),
DAY_TO = TextBox_Day_To_SelectTaskChart.Text == "" ? new DateTime(0) : DateTime.Parse(TextBox_Day_To_SelectTaskChart.Text),
TASK_KIND_CODE = CheckBox_Personal_SelectTaskChart.Checked == true ? "01" : TextBox_KindCode_SelectTaskChart.Text,
TASK_GROUP_CODE = TextBox_GroupCode_SelectTaskChart.Text,
CHART_INTERVAL = CheckBox_Day_SelectTaskChart.Checked == true ? "DAY" :
CheckBox_Week_SelectTaskChart.Checked == true ? "WEEK" : "MONTH"
};
// タスク予実推移データの取得
DataTable taskChartTable = chartData.TaskChartTable_Select(flgChartSelect);
// チャートの追加
Chart_TaskBudget.Series.Add(chartTitlePlus);
Chart_TaskBudget.Series.Add(chartTitleMinus);
// チャート種類の変更
Chart_TaskBudget.Series[chartTitlePlus].ChartType = SeriesChartType.Line;
Chart_TaskBudget.Series[chartTitlePlus].MarkerStyle = MarkerStyle.Square;
Chart_TaskBudget.Series[chartTitlePlus].MarkerSize = 10;
Chart_TaskBudget.Series[chartTitlePlus].BorderWidth = 3;
Chart_TaskBudget.Series[chartTitlePlus].Color = Color.SteelBlue;
Chart_TaskBudget.Series[chartTitleMinus].ChartType = SeriesChartType.Line;
Chart_TaskBudget.Series[chartTitleMinus].MarkerStyle = MarkerStyle.Square;
Chart_TaskBudget.Series[chartTitleMinus].MarkerSize = 10;
Chart_TaskBudget.Series[chartTitleMinus].BorderWidth = 3;
Chart_TaskBudget.Series[chartTitleMinus].Color = Color.SlateGray;
// X・Y軸のタイトル設定
Chart_TaskBudget.ChartAreas[0].AxisX.Title = "完了帯";
Chart_TaskBudget.ChartAreas[0].AxisY.Title = "上限超過率";
// Y軸上限値の設定
Chart_TaskBudget.ChartAreas[0].AxisY.Maximum = 100;
// チャートにデータ値の登録
for (int i = 0; i < taskChartTable.Rows.Count; i++)
{
Chart_TaskBudget.Series[chartTitlePlus].Points.AddXY(taskChartTable.Rows[i][0], taskChartTable.Rows[i][1]);
Chart_TaskBudget.Series[chartTitleMinus].Points.AddXY(taskChartTable.Rows[i][0], taskChartTable.Rows[i][2]);
}
}
public DataTable TaskChartTable_Select(Flg_Chart_Select flgChartSelect)
{
// タスク種別インスタンス生成
DataTable taskChartTable = new DataTable();
// 共通部品を生成
DataBaceAccess dbAccess = new DataBaceAccess();
// 共通部品からSQLserverとの接続オブジェクトを取得
SqlConnection connection = dbAccess.GetSqlSvrConnect();
SqlCommand command = connection.CreateCommand();
try
{
// データベースの接続開始
connection.Open();
// SQLの準備
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT ");
query.AppendLine(" REPLACE(FINISHED_YMD, '-', '/') AS FINISHED_YMD ");
query.AppendLine(" , FLOOR ( ");
query.AppendLine(" CONVERT( FLOAT, ");
query.AppendLine(" SUM ( ");
query.AppendLine(" CASE WHEN DIFFERENCE_PERCENT < (-1) * PERCENT_TIME ");
query.AppendLine(" THEN 1 ");
query.AppendLine(" ELSE 0 ");
query.AppendLine(" END ");
query.AppendLine(" ) ");
query.AppendLine(" ) ");
query.AppendLine(" / ");
query.AppendLine(" NULLIF(CONVERT( FLOAT, COUNT(1)), 0) ");
query.AppendLine(" * ");
query.AppendLine(" 100 ");
query.AppendLine(" ) AS MINUS_OVER_PERCENT ");
query.AppendLine(" , FLOOR ( ");
query.AppendLine(" CONVERT( FLOAT, ");
query.AppendLine(" SUM ( ");
query.AppendLine(" CASE WHEN DIFFERENCE_PERCENT > PERCENT_TIME ");
query.AppendLine(" THEN 1 ");
query.AppendLine(" ELSE 0 ");
query.AppendLine(" END ");
query.AppendLine(" ) ");
query.AppendLine(" ) ");
query.AppendLine(" / ");
query.AppendLine(" NULLIF(CONVERT( FLOAT, COUNT(1)), 0) ");
query.AppendLine(" * ");
query.AppendLine(" 100 ");
query.AppendLine(" ) AS PLUS_OVER_PERCENT ");
query.AppendLine("FROM ");
query.AppendLine(" ( ");
query.AppendLine(" SELECT ");
if (flgChartSelect.CHART_INTERVAL == "DAY")
{
query.AppendLine(" T_TL.TASK_NO ");
query.AppendLine(" , T_TL.FINISHED_YMD ");
}
else if (flgChartSelect.CHART_INTERVAL == "WEEK")
{
query.AppendLine(" LEFT(T_TL.FINISHED_YMD, 9) AS FINISHED_YMD ");
}
else if (flgChartSelect.CHART_INTERVAL == "MONTH")
{
query.AppendLine(" LEFT(T_TL.FINISHED_YMD, 7) AS FINISHED_YMD ");
}
query.AppendLine(" , FLOOR ( ");
query.AppendLine(" CONVERT( FLOAT, DATEDIFF( MINUTE, T_TL.PLAN_TIME, T_TL.RESULT_TIME ) ) ");
query.AppendLine(" / ");
query.AppendLine(" NULLIF( CONVERT( FLOAT, DATEDIFF( MINUTE, 0, T_TL.PLAN_TIME ) ), 0) ");
query.AppendLine(" * ");
query.AppendLine(" 100 ");
query.AppendLine(" ) AS DIFFERENCE_PERCENT ");
query.AppendLine(" , M_UI.PERCENT_TIME ");
query.AppendLine(" FROM TRN_TASK_LIST T_TL ");
query.AppendLine(" INNER JOIN MST_USER_INFO M_UI ");
query.AppendLine(" ON T_TL.USER_NO = M_UI.USER_NO ");
#region 検索条件
// ユーザーNo
query.AppendLine(" WHERE T_TL.USER_NO = @USER_NO ");
command.Parameters.Add(new SqlParameter("@USER_NO", flgChartSelect.USER_NO));
// タスクステータスコード
query.AppendLine(" AND T_TL.TASK_STATUS_CODE = '10' ");
// 検索日付条件
if (flgChartSelect.DAY_FROM != new DateTime(0))
{
query.AppendLine(" AND T_TL.TODO_YMD >= @DAY_FROM ");
command.Parameters.Add(new SqlParameter("@DAY_FROM", flgChartSelect.DAY_FROM));
}
if (flgChartSelect.DAY_TO != new DateTime(0))
{
query.AppendLine(" AND T_TL.TODO_YMD <= @DAY_TO ");
command.Parameters.Add(new SqlParameter("@DAY_TO", flgChartSelect.DAY_TO));
}
// タスク種別コード条件
if (flgChartSelect.TASK_KIND_CODE != Constants.TaskKind.ALL_00)
{
query.AppendLine(" AND T_TL.TASK_KIND_CODE = @TASK_KIND_CODE ");
command.Parameters.Add(new SqlParameter("@TASK_KIND_CODE", flgChartSelect.TASK_KIND_CODE));
}
// タスクグループコード条件
if (flgChartSelect.TASK_GROUP_CODE != Constants.TaskGroup.ALL_00)
{
query.AppendLine(" AND T_TL.TASK_GROUP_CODE = @TASK_GROUP_CODE ");
command.Parameters.Add(new SqlParameter("@TASK_GROUP_CODE", flgChartSelect.TASK_GROUP_CODE));
}
#endregion
if (flgChartSelect.CHART_INTERVAL == "DAY")
{
query.AppendLine(" GROUP BY FINISHED_YMD, TASK_NO, PLAN_TIME, RESULT_TIME, PERCENT_TIME ");
}
else if (flgChartSelect.CHART_INTERVAL == "WEEK")
{
query.AppendLine(" GROUP BY LEFT(T_TL.FINISHED_YMD, 9), TASK_NO, PLAN_TIME, RESULT_TIME, PERCENT_TIME ");
}
else if (flgChartSelect.CHART_INTERVAL == "MONTH")
{
query.AppendLine(" GROUP BY LEFT(T_TL.FINISHED_YMD, 7), TASK_NO, PLAN_TIME, RESULT_TIME, PERCENT_TIME ");
}
query.AppendLine(" ) TASKLIST_FOR_CHART ");
query.AppendLine("GROUP BY FINISHED_YMD ");
query.AppendLine("ORDER BY FINISHED_YMD ");
command.CommandText = query.ToString();
// SQLの実行
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(taskChartTable);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
return(taskChartTable);
}
