/// <summary>
/// The fetch log.
/// </summary>
/// <param name="empNr">
/// The emp nr.
/// </param>
/// <param name="projectList">
/// The project list.
/// </param>
/// <param name="from">
/// The from.
/// </param>
/// <param name="to">
/// The to.
/// </param>
/// <returns>
/// The <see cref="DataTable"/>.
/// </returns>
/// <exception cref="Exception">
/// Unable to fetch log.
/// </exception>
public DataTable FetchLog(string empNr, IEnumerable <string> projectList, DateTime from, DateTime to)
{
BasicValidator.ValidateEntry(empNr, "DummyValue", from, to, sqlHelper.ErrorLogger);
if (!projectList.Any())
{
return(new DataTable());
}
var projectArray = projectList.Select(x => x.Trim()).ToArray();
var joinQuery = sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[0]);
for (int i = 1; i < projectArray.Length; i++)
{
BasicValidator.ValidateAsNonSpacedString(projectArray[i], sqlHelper.ErrorLogger);
var joinSuffix =
$" full outer join {sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[i])} on {projectArray[0]}.WorkDay = {projectArray[i]}.WorkDay ";
joinQuery += joinSuffix;
}
string coalease_value = projectList.Count() > 1
? $"coalesce( {string.Join(",", projectList.Select(x => $"{x}.WorkDay"))} )"
: $"{projectList.First()}.WorkDay";
var query =
$"select convert( varchar(10), {coalease_value}, 120) as WorkDay, {string.Join(",", projectList.Select(x => $"{x}.TimeLog as Project_{x}"))} from {joinQuery} order by {projectArray[0]}.WorkDay";
return(sqlHelper.RunCommand(query, new SqlParameter[0]));
}
/// <summary>Calls when a process requests authorization.</summary>
/// <param name="actionContext">The action context, which encapsulates information for using <see cref="T:System.Web.Http.Filters.AuthorizationFilterAttribute" />.</param>
public override void OnAuthorization(HttpActionContext actionContext)
{
if (actionContext.Request.Headers.Authorization == null)
{
actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
}
else
{
try
{
var auth = Encoding.UTF8
.GetString(Convert.FromBase64String(actionContext.Request.Headers.Authorization.Parameter)).Split(':');
auth.ToList().ForEach(x => BasicValidator.ValidateAsNonSpacedString(x, errorLogger));
var username = auth[0];
var password = auth[1];
var login = new LoginModule(new SqlHelperBase(errorLogger));
if (login.UserLogin(username, password))
{
Thread.CurrentPrincipal = new GenericPrincipal(new GenericIdentity(username), null);
}
else
{
actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
}
}
catch (Exception e)
{
actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
}
}
}
/// <summary>
/// The fetch log.
/// </summary>
/// <param name="empNr">
/// The emp nr.
/// </param>
/// <param name="projectList">
/// The project list.
/// </param>
/// <param name="from">
/// The from.
/// </param>
/// <param name="to">
/// The to.
/// </param>
/// <returns>
/// The <see cref="DataTable"/>.
/// </returns>
/// <exception cref="Exception">
/// Unable to fetch log.
/// </exception>
public DataTable FetchLog(string empNr, IEnumerable <string> projectList, DateTime from, DateTime to)
{
BasicValidator.ValidateEntry(empNr, "DummyValue", from, to, sqlHelper.ErrorLogger);
if (!projectList.Any())
{
return(new DataTable());
}
var projectArray = projectList.Select(x => x.Trim()).ToArray();
var joinQuery = sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[0]);
for (int i = 1; i < projectArray.Length; i++)
{
BasicValidator.ValidateAsNonSpacedString(projectArray[i], sqlHelper.ErrorLogger);
var joinSuffix =
$" full outer join {sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[i])} on {projectArray[0]}.WorkDay = {projectArray[i]}.WorkDay ";
joinQuery += joinSuffix;
}
string coalease_value = projectList.Count() > 1
? $"coalesce( {string.Join(",", projectList.Select(x => $"{x}.WorkDay"))} )"
: $"{projectList.First()}.WorkDay";
var query =
$"select convert( varchar(10), {coalease_value}, 120) as WorkDay, {string.Join(",", projectList.Select(x => $"{x}.TimeLog as {x}"))} from {joinQuery} order by {projectArray[0]}.WorkDay";
var dt = sqlHelper.RunCommand(query, new SqlParameter[0]);
var grid = new DataTable();
grid.Columns.Add("WorkDay");
grid.PrimaryKey = new[] { grid.Columns["WorkDay"] };
grid.Columns.AddRange(projectList.Select(x => new DataColumn($"{x}")).ToArray());
for (var i = from; i <= to; i = i.AddDays(1))
{
grid.Rows.Add(i.ToShortDateString());
}
grid.BeginLoadData();
foreach (DataRow dtrow in dt.Rows)
{
var itemArray = new string[dtrow.ItemArray.Length];
itemArray[0] = DateTime.Parse(dtrow.ItemArray[0].ToString()).ToShortDateString();
for (var i = 1; i < dtrow.ItemArray.Length; i++)
{
itemArray[i] = dtrow.ItemArray[i].ToString();
}
grid.LoadDataRow(itemArray.Select(x => (object)x).ToArray(), LoadOption.OverwriteChanges);
}
grid.EndLoadData();
return(grid);
}
public void ValidateAsNonSpacedStringTest(string input, bool valid)
{
//Arrange, Act and Assert
if (valid)
{
BasicValidator.ValidateAsNonSpacedString(input, new ServerLogger());
}
else
{
Assert.Throws <Exception>(() => BasicValidator.ValidateAsNonSpacedString(input, errorLogger));
}
}
/// <summary>
/// The update database.
/// </summary>
/// <param name="projectName">
/// The project Name.
/// </param>
/// <param name="timeLog">
/// The time Log.
/// </param>
/// <param name="day">
/// The day.
/// </param>
/// <param name="empNr">
/// The emp nr.
/// </param>
/// <exception cref="Exception">
/// Update Failed.
/// </exception>
public void UpdateDatabase(string projectName, string timeLog, string day, string empNr)
{
projectName = projectName.Trim();
timeLog = timeLog.Trim();
BasicValidator.ValidateAsNonSpacedString(projectName, ErrorLogger);
BasicValidator.ValidateAsNonSpacedString(empNr, ErrorLogger);
BasicValidator.ValidateTimeLog(timeLog, ErrorLogger);
BasicValidator.ValidateDate(day, ErrorLogger);
var tableName = $"Project_{projectName}_Log";
using (var conn = new SqlConnection())
{
var command = new SqlCommand($"select count(*) from {tableName} where WorkDay = @day and EmployeeNr = @empNr ", conn);
command.Parameters.AddRange(CreateSqlParameters(timeLog, day, empNr));
conn.ConnectionString = ConnectionString;
try
{
conn.Open();
if ((int)command.ExecuteScalar() > 0)
{
var updateCommand = new SqlCommand($"update {tableName} set TimeLog = @value where WorkDay = @day and EmployeeNr = @empNr ", conn);
updateCommand.Parameters.AddRange(CreateSqlParameters(timeLog, day, empNr));
var rowsUpdated = updateCommand.ExecuteNonQuery();
Console.WriteLine($"rows updated: {rowsUpdated}");
}
else
{
var insertCommand = new SqlCommand($"insert into {tableName} (EmployeeNr, WorkDay, TimeLog, Routed) values (@empNr, @day, @value, 0) ", conn);
insertCommand.Parameters.AddRange(CreateSqlParameters(timeLog, day, empNr));
var rowsInserted = insertCommand.ExecuteNonQuery();
Console.WriteLine($"rows updated: {rowsInserted}");
}
}
catch (Exception e)
{
ErrorLogger.Error(e.Message, trace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
/// <summary>
/// Add new Project.
/// </summary>
/// <param name="project">
/// The project.
/// </param>
/// <param name="empNr">
/// The emp Nr.
/// </param>
/// <returns>
/// The <see cref="bool"/>.
/// </returns>
public bool AddNewProject(string project, string empNr)
{
project = project.Trim();
BasicValidator.ValidateAsNonSpacedString(project, sqlHelper.ErrorLogger);
BasicValidator.ValidateEmployeeNr(project, sqlHelper.ErrorLogger);
var rowsCount = sqlHelper.RunNonQueryCommand(
$@"IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Project_{project}_Log')
BEGIN
/****** Object: Table [dbo].[Project_{project}_Log] Script Date: 11-04-2018 14:17:19 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE[dbo].[Project_{project}_Log](
[EmployeeNr][nchar](10) NOT NULL,
[WorkDay][smalldatetime] NOT NULL,
[TimeLog][smallint] NOT NULL,
[Routed][bit] NOT NULL
) ON[PRIMARY]
ALTER TABLE[dbo].[Project_{project}_Log] ADD CONSTRAINT[DF_Project_{
project
}_Log_TimeLog] DEFAULT((0)) FOR[TimeLog]
ALTER TABLE[dbo].[Project_{project}_Log] WITH CHECK ADD CONSTRAINT[FK_Project_{
project
}_Log_Login] FOREIGN KEY([EmployeeNr])
REFERENCES[dbo].[Login_Table]
([EmployeeNr])
ALTER TABLE[dbo].[Project_{project}_Log]
CHECK CONSTRAINT[FK_Project_{project}_Log_Login]
END", new SqlParameter[0]);
var rows = sqlHelper.RunNonQueryCommand(
@"IF NOT EXISTS(SELECT * FROM Projects WHERE EmployeeNr = @empNr)
INSERT INTO Projects(EmployeeNr, ProjectList) VALUES(@empNr, @projectName)
ELSE
Update Projects set ProjectList = ProjectList + ',' + @projectName where EmployeeNr = @empNr",
new[]
{
new SqlParameter("empNr", empNr),
new SqlParameter("projectName", project),
});
return(rowsCount != 0);
}
public HttpResponseMessage AddTimeLog(HttpRequestMessage request)
{
try
{
if (request.Content.Headers.ContentType.MediaType != "application/json")
{
return(new HttpResponseMessage(HttpStatusCode.UnsupportedMediaType));
}
var jsonData = request.Content.ReadAsStringAsync().Result;
var data = JArray.Parse(jsonData);
var logs = new List <Log>();
foreach (var jToken in data)
{
var log = new Log()
{
WorkDay = DateTime.Parse((string)jToken["WorkDay"]),
TimeLog = jToken.Children().Select(x => (JProperty)x)
.Where(y => y.Name != "WorkDay" && !string.IsNullOrWhiteSpace((string)y.Value))
.ToDictionary(project => (string)project.Name, hours => (int)hours.Value)
};
if (log.TimeLog.Sum(x => x.Value) > 24)
{
return new HttpResponseMessage(HttpStatusCode.BadRequest)
{
Content = new StringContent($"Total time exceeds 24 hours for workday: {log.WorkDay.ToShortDateString()}")
}
}
;
logs.Add(log);
}
var empNr = Thread.CurrentPrincipal.Identity.Name;
foreach (var log in logs)
{
foreach (var timeLog in log.TimeLog)
{
try
{
BasicValidator.ValidateAsNonSpacedString(timeLog.Key, errorLogger);
timeLogModule.UpdateTimeLog($"{timeLog.Key}", timeLog.Value.ToString(), log.WorkDay.ToShortDateString(), empNr);
}
catch (Exception e)
{
return(new HttpResponseMessage(HttpStatusCode.BadRequest)
{
Content = new StringContent($"Error adding log for project: {timeLog.Key}, for workday: {log.WorkDay}")
});
}
}
}
return(new HttpResponseMessage(HttpStatusCode.Accepted));
}
catch (Exception e)
{
return(new HttpResponseMessage(HttpStatusCode.BadRequest));
}
}
private void ValidateInput(string username, string password)
{
BasicValidator.ValidateEmployeeNr(username, sqlHelper.ErrorLogger);
BasicValidator.ValidateAsNonSpacedString(username, sqlHelper.ErrorLogger);
BasicValidator.ValidateAsNonSpacedString(password, sqlHelper.ErrorLogger);
}