private string BuildPayrollQuery(EntryFilter filter, Dictionary <string, object> filterParams)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(@"SELECT U.AccountingName, T.Id AS TimeEntryId, T.UserDetailId, T.EntryDate, W.BaseCode, W.Description, T.JobNumber, W.sub, Sum(H.Duration) AS Hours
FROM userdetail U
INNER JOIN timeentry T
ON U.Id = T.UserDetailId
INNER JOIN workcode W
ON T.WorkCodeId = W.Id
INNER JOIN timeentryhours H ON T.Id = H.TimeEntryId
WHERE 1 = 1 ");
if (filter.FilterStartDate != null)
{
sb.AppendLine("AND T.EntryDate >= ?p_startDate ");
filterParams.Add("?p_startDate", filter.FilterStartDate.Value.Date);
}
if (filter.FilterEndDate != null)
{
sb.AppendLine("AND T.EntryDate <= ?p_endDate ");
filterParams.Add("?p_endDate", filter.FilterEndDate.Value.Date);
}
if (filter.CurrentUserDetailId > 0)
{
sb.AppendLine("AND U.Id = ?p_userDetailId ");
filterParams.Add("?p_userDetailId", filter.CurrentUserDetailId);
}
else if (filter.UserDetailId != null)
{
sb.Append($"AND UserDetailId = {filter.UserDetailId} ");
}
if (filter.ExportedToNavision != null)
{
if ((bool)filter.ExportedToNavision)
{
sb.AppendLine("AND ExportedToNavision = 1 ");
}
else
{
sb.AppendLine("AND ExportedToNavision = 0 ");
}
}
if (filter.ExportedToPayroll != null)
{
if ((bool)filter.ExportedToPayroll)
{
sb.AppendLine("AND ExportedToPayroll = 1 ");
}
else
{
sb.AppendLine("AND ExportedToPayroll = 0 ");
}
}
sb.AppendLine(@"GROUP BY U.AccountingName,TimeEntryId, T.UserDetailId, T.EntryDate, W.BaseCode, W.Description, W.sub;");
return(sb.ToString());
}
public void query_via_messageclass_and_return_entry()
{
repo.Add(Helper.makeEntry(Messageclass.Warning));
repo.Add(Helper.makeEntry());
Entry[] actual = repo.Query(EntryFilter.MessageclassFilter(Messageclass.Error));
Assert.AreEqual(1, actual.Length);
}
public void query_via_messageclass_without_entries_and_return_no_entry()
{
repo.Add(Helper.makeEntry(Messageclass.Warning));
repo.Add(Helper.makeEntry());
Entry[] actual = repo.Query(EntryFilter.MessageclassFilter(Messageclass.Information));
Assert.AreEqual(0, actual.Length);
}
public IHttpActionResult GetTotalHoursForDate([FromBody] EntryFilter filter)
{
if (User.Identity != null)
{
return(Ok(new TimeEntryAccess().GetTotalHours(filter, User)));
}
else
{
return(Unauthorized());
}
}
public decimal GetTotalHours(EntryFilter filter, IPrincipal user)
{
decimal total = 0;
var entries = GetTimeEntries(filter, user);
foreach (var entry in entries)
{
total += entry.userHours;
}
return(total);
}
public void query_via_application_and_return_entry()
{
repo.Add(Helper.makeEntry(Application: "App1"));
repo.Add(Helper.makeEntry(Application: "App2"));
Assert.AreEqual(2, repo.Count());
Entry[] actual = repo.Query(EntryFilter.ApplicationFilter("App1"));
Assert.AreEqual(1, actual.Length);
Assert.AreEqual("App1", actual[0].Application);
}
public void query_via_message_and_return_two_entries()
{
repo.Add(Helper.makeEntry(Messageclass.Warning, "Test"));
repo.Add(Helper.makeEntry(Messageclass.Warning, "Text"));
repo.Add(Helper.makeEntry(Messageclass.Warning, "Warning"));
Entry[] actual = repo.Query(EntryFilter.MessageFilter("^Te[sx]{1,1}t$"));
Assert.AreEqual(2, actual.Length);
}
public ActionResult Password(PasswordPostModel model)
{
if (model.Password != PasswordValue)
{
return(Redirect(HttpContext.Request.Url.OriginalString));
}
EntryFilter.Authenticate(Response);
var returnUrl = Request.QueryString[PasswordReturnUrlName] ?? HomeActions.Index();
return(Redirect(returnUrl));
}
public List <PayrollExport> GetEntriesForPayrollExport(EntryFilter filter, IPrincipal user)
{
var filterParams = new Dictionary <string, object>();
try
{
return(Retrieve(PayrollExport.PayrollExportFactory, BuildPayrollQuery(filter, filterParams), filterParams, false).ToList());
}
catch (Exception ex) {
new EventsAccess().LogException(ex);
throw;
}
}
public void query_via_logdate_and_message_should_return_one_entry()
{
repo.Add(Helper.makeEntry(Message: "Test", Logdate: "2017-07-01Z00:00:00"));
repo.Add(Helper.makeEntry(Message: "Text", Logdate: "2017-08-20Z00:00:00"));
repo.Add(Helper.makeEntry(Message: "Message", Logdate: "2017-07-20Z00:00:00"));
Entry[] actual = repo.Query(EntryFilter.MessageFilter("^Te[sx]{1,1}t$"),
EntryFilter.LogdateFilter(new DateTime(2017, 7, 1), new DateTime(2017, 7, 31)));
Assert.AreEqual(3, repo.Count());
Assert.AreEqual(1, actual.Length);
Assert.AreEqual("Test", actual[0].Message);
}
public MemoryStream GetExportFileForPayroll(EntryFilter filter, IPrincipal user, ITimeEntryAccess timeEntryAccess)
{
var sb = new StringBuilder();
sb.AppendLine("Name,Date Worked,Work Code,Hours (Total), Sub");
var entries = timeEntryAccess.GetEntriesForPayrollExport(filter, user);
foreach (var entry in entries) {
sb.AppendLine($"{entry.AccountingName},{entry.EntryDate.ToShortDateString()},{entry.BaseCode} - {entry.CodeDescription},{entry.Hours}, {entry.Sub}");
timeEntryAccess.SetEntryExportedToPayroll(entry.TimeEntryId);
}
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.Write(sb.ToString());
writer.Flush();
stream.Position = 0;
return stream;
}
public HttpResponseMessage ExportEntriesForNavision([FromBody] EntryFilter filter)
{
using (MemoryStream stream = new FileExport().GetExportFile(filter, User, new TimeEntryAccess()))
{
var result = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(stream.GetBuffer())
};
result.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = "Test.csv"
};
result.Content.Headers.ContentType =
new MediaTypeHeaderValue("application/octet-stream");
return(result);
}
}
/// <summary>
/// Changes the filter that controls what type of messages are displayed in the console.
/// </summary>
/// <param name="filter">Flags that control which type of messages should be displayed.</param>
private void SetFilter(EntryFilter filter)
{
if (this.filter == filter)
{
return;
}
this.filter = filter;
listView.Clear();
filteredEntries.Clear();
foreach (var entry in entries)
{
if (DoesFilterMatch(entry.type))
{
listView.AddEntry(entry);
filteredEntries.Add(entry);
}
}
sSelectedElementIdx = -1;
}
public Predicate <Entry>[] Filter()
{
List <Predicate <Entry> > Result = new List <Predicate <Entry> >();
if (!string.IsNullOrEmpty(Application))
{
Result.Add(EntryFilter.ApplicationFilter(Application));
}
if (!string.IsNullOrEmpty(Message))
{
Result.Add(EntryFilter.MessageFilter(Message));
}
if (Messageclass != null)
{
Result.Add(EntryFilter.MessageclassFilter(Messageclass));
}
DateTime Enddate = LogdateTo == DateTime.MinValue ? DateTime.MaxValue : LogdateTo;
Result.Add(EntryFilter.LogdateFilter(LogdateFrom, Enddate));
return(Result.ToArray());
}
// Save current state (only persistent over a single session)
void SaveState()
{
lastMappingMode = mode;
lastEntryFilter = (EntryFilter)cbFilter.SelectedValue;
lastEntryFilterNot = chkEntryFilterNot.Checked;
}
public MemoryStream GetExportFile(EntryFilter filter, IPrincipal user, ITimeEntryAccess timeEntryAccess)
{
var sb = new StringBuilder();
sb.AppendLine("CODE,DATE,HOURS,JOB_NO");
var entries = timeEntryAccess.GetTimeEntries(filter, user);
foreach (var entry in entries)
{
foreach (var hours in entry.hours)
{
var hoursSuffix = string.Empty;
switch (hours.HoursType)
{
case HourTypes.Regular:
hoursSuffix = "HRS";
break;
case HourTypes.Overtime:
hoursSuffix = "OT";
break;
case HourTypes.DoubleTime:
hoursSuffix = "DT";
break;
}
sb.AppendLine($"{entry.workCode.baseCode}-{hoursSuffix},{entry.entryDate.ToShortDateString()},{hours.Duration}, J00{entry.jobnumber}");
}
timeEntryAccess.SetEntryExportedToNavision(entry);
}
//var usersWithEntries = entries.GroupBy(e => e.userDetailId).Select(u => u.ToList()).ToList();
//foreach (var userWithEntries in usersWithEntries)
//{
// var entriesPerDay = userWithEntries.GroupBy(u => u.entryDate).Select(u => u.ToList()).ToList();
// foreach (var entryPerDay in entriesPerDay)
// {
// bool splitCalculated = false;
// decimal hoursTotal = 0;
// foreach (var entry in entryPerDay)
// {
// var hours = entry.hours.Sum(h => h.Duration);
// hoursTotal += hours;
// if (hoursTotal > 40 && !splitCalculated)
// {
// var overtimeHours = hoursTotal - 40;
// var regularHours = hours - overtimeHours;
// if (regularHours > 0)
// {
// sb.AppendLine($"{entry.workCode.BaseCode}-HRS,{entry.entryDate.ToShortDateString()},{regularHours}, J00{entry.jobnumber}");
// }
// sb.AppendLine($"{entry.workCode.BaseCode}-OT,{entry.entryDate.ToShortDateString()},{overtimeHours}, J00{entry.jobnumber}");
// splitCalculated = true;
// }
// else if (hoursTotal > 40 && splitCalculated)
// {
// sb.AppendLine($"{entry.workCode.BaseCode}-OT,{entry.entryDate.ToShortDateString()},{hours}, J00{entry.jobnumber}");
// }
// else
// {
// sb.AppendLine($"{entry.workCode.BaseCode}-HRS,{entry.entryDate.ToShortDateString()},{hours}, J00{entry.jobnumber}");
// }
// timeEntryAccess.SetEntryExportedToNavision(entry);
// }
// }
//}
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.Write(sb.ToString());
writer.Flush();
stream.Position = 0;
return stream;
}
public List <TimeEntryView> GetTimeEntries(EntryFilter filter, IPrincipal user)
{
//TODO: Parameterize sql
var sb = new StringBuilder();
sb.Append("SELECT * FROM peakertimemanagement.timeentry ");
if (!filter.ShowAllUsers)
{
if (filter.UserDetailId != null)
{
if (UserAccess.IsUserAdmin(user))
{
sb.Append($"WHERE UserDetailId = {filter.UserDetailId} ");
}
}
else
{
sb.Append($"WHERE UserDetailId = {filter.CurrentUserDetailId} ");
}
}
else
{
if (UserAccess.IsUserAdmin(user))
{
sb.Append($"WHERE UserDetailId <> -1 ");
}
else
{
sb.Append($"WHERE UserDetailId = {filter.CurrentUserDetailId} ");
}
}
if (filter.EntryId != null)
{
sb.Append($"AND Id = {filter.EntryId} ");
}
if (filter.FilterStartDate != null)
{
var startDate = filter.FilterStartDate.Value.Date;
sb.Append($"AND EntryDate >= '{startDate.ToString("yyyy-MM-dd")}' ");
}
if (filter.FilterEndDate != null)
{
var endDate = filter.FilterEndDate.Value.Date;
sb.Append($"AND EntryDate <= '{endDate.ToString("yyyy-MM-dd")}' ");
}
if (filter.RequireJobCode != null && (bool)filter.RequireJobCode)
{
sb.Append($"AND Jobnumber <> '' ");
}
if (filter.ExportedToNavision != null)
{
if ((bool)filter.ExportedToNavision)
{
sb.Append("AND ExportedToNavision = 1 ");
}
else
{
sb.Append("AND ExportedToNavision = 0 ");
}
}
if (filter.ExportedToPayroll != null)
{
if ((bool)filter.ExportedToPayroll)
{
sb.Append("AND ExportedToPayroll = 1 ");
}
else
{
sb.Append("AND ExportedToPayroll = 0 ");
}
}
try
{
var entries = Retrieve(TimeEntry.WorkCodeFactory, sb.ToString(), null, false).OrderByDescending(t => t.EntryDate).ToList();
return(FillTimeEntryView(entries));
}
catch (Exception ex)
{
new EventsAccess().LogException(ex);
return(null);
}
}
private void WriteTagsForm_FormClosing(object sender, FormClosingEventArgs e)
{
lastEntryFilter = (EntryFilter)cbFilter.SelectedValue;
lastEntryFilterNot = chkEntryFilterNot.Checked;
}