/// <summary>
/// 時間の重複をチェック
/// </summary>
/// <param name="item"></param>
private bool IsErrorTimeDup(Interface.ImportData item)
{
var table = new DataTable();
using (var connection = new SqlConnection(GetConnectionString()))
using (var command = connection.CreateCommand())
{
try
{
// データベースの接続開始
connection.Open();
// SQLの設定
var sb = new StringBuilder();
sb.Append("SELECT ");
sb.Append(" WorkKousuuJisseki.* ");
sb.Append("FROM ");
sb.Append(" WorkKousuuJisseki ");
sb.Append("WHERE ");
sb.Append(" WorkKousuuJisseki.StaffID = @StaffID ");
sb.Append(" AND WorkKousuuJisseki.WorkDate = @WorkDate ");
sb.Append(" AND ( ");
sb.Append(" ( ");
sb.Append(" (WorkKousuuJisseki.StartTime < @StartTime) AND (@StartTime < WorkKousuuJisseki.EndTime) ");
sb.Append(" ) ");
sb.Append(" OR ( ");
sb.Append(" (WorkKousuuJisseki.StartTime < @EndTime) AND (@EndTime < WorkKousuuJisseki.EndTime) ");
sb.Append(" ) ");
sb.Append(" OR ( ");
sb.Append(" (WorkKousuuJisseki.StartTime >= @StartTime) AND (WorkKousuuJisseki.EndTime <= @EndTime) ");
sb.Append(" ) ");
sb.Append(" ) ");
command.CommandText = sb.ToString();
command.Parameters.Add(new SqlParameter("@StaffID", item.StaffID));
command.Parameters.Add(new SqlParameter("@WorkDate", item.WorkDate));
command.Parameters.Add(new SqlParameter("@StartTime", item.StartTime));
command.Parameters.Add(new SqlParameter("@EndTime", item.EndTime));
// SQLの実行
var adapter = new SqlDataAdapter(command);
adapter.Fill(table);
if (table.Rows.Count > 1)
{
return(true);
}
return(false);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
}
/// <summary>
/// 作業工数を新規登録
/// </summary>
/// <param name="command"></param>
/// <param name="item"></param>
private void InsertNewData(SqlCommand command, Interface.ImportData item)
{
// 実行するSQL
var sb = new StringBuilder();
sb.Append("INSERT ");
sb.Append("INTO dbo.WorkKousuuJisseki( ");
sb.Append(" StaffID ");
sb.Append(" , ProjectID ");
sb.Append(" , WorkID ");
sb.Append(" , WorkDate ");
sb.Append(" , StartTime ");
sb.Append(" , EndTime ");
sb.Append(" , WorkNaiyou ");
sb.Append(" , TourokuDate ");
sb.Append(" , KoushinDate ");
sb.Append(") ");
sb.Append("VALUES ( ");
sb.Append(" @StaffID ");
sb.Append(" , @ProjectID ");
sb.Append(" , @WorkID ");
sb.Append(" , @WorkDate ");
sb.Append(" , @StartTime ");
sb.Append(" , @EndTime ");
sb.Append(" , @WorkNaiyou ");
sb.Append(" , CURRENT_TIMESTAMP ");
sb.Append(" , NULL ");
sb.Append(") ");
command.CommandText = sb.ToString();
command.Parameters.Add(new SqlParameter("@StaffID", item.StaffID));
command.Parameters.Add(new SqlParameter("@ProjectID", item.ProjectID));
command.Parameters.Add(new SqlParameter("@WorkID", item.WorkID));
command.Parameters.Add(new SqlParameter("@WorkDate", item.WorkDate));
command.Parameters.Add(new SqlParameter("@StartTime", item.StartTime));
command.Parameters.Add(new SqlParameter("@EndTime", item.EndTime));
command.Parameters.Add(new SqlParameter("@WorkNaiyou", item.WorkNaiyou));
// SQLの実行
command.ExecuteNonQuery();
}