/*
* below to BE removed!
* private async Task<ExcelReport<JobSummaryReportCriteria>> GetJobSummaryReportCriteria(bool isCurrentUserAdmin)
* {
* var vm = new JobSummaryReportCriteria();
* vm.PeriodSettings = GetDefaultPeriodSettings();
* vm.AvailableJobs = (await jobService.GetAsync()).OrderBy(x => x.FullJobCodeWithName).ToList();
* var rpt = new ExcelReport<JobSummaryReportCriteria>(JobSummaryReportCriteria.PROJECT_STATUS_REPORT_NAME, vm, isCurrentUserAdmin);
* return rpt;
* }
*/
private async Task <ExcelReport <QuickJobTimeReportCriteria> > GetJobDetailreportAsync()
{
var vm = new QuickJobTimeReportCriteria();
vm.PeriodSettings = GetDefaultPeriodSettings();
vm.AvailableJobs = (await jobService.GetAsync()).OrderBy(x => x.FullJobCodeWithName).ToList();
var rpt = new ExcelReport <QuickJobTimeReportCriteria>(QuickJobTimeReportCriteria.QUICK_JOB_TIME_REPORT_NAME, vm, true);
return(rpt);
}
public async Task <ReportDTO <QuickJobTimeReportDTO> > RunAsync(QuickJobTimeReportCriteria criteria)
{
DateTime start = criteria.PeriodSettings.Start;
DateTime end = criteria.PeriodSettings.End;
int jobId = int.Parse(criteria.SelectedJobId);
int?limitToEmployeeId = await _sessionAdapter.EmployeeIdAsync();
if (criteria.ShowAllEmployeesForJob)
{
limitToEmployeeId = null;
}
using (var conn = new SqlConnection(configuration.GetConnectionString("SiteConnection")))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = @"
Select " +
$" min(Convert(varchar(10),Isnull(te.Date,@WeekStart), 101)) as {nameof(QuickJobTimeReportDTO.PeriodStart)}, "
+ $" max(Convert(varchar(10),isnull( te.Date,@WeekEnd),101)) as {nameof(QuickJobTimeReportDTO.PeriodEnd)}, "
+ $" COALESCE(e.Last,'') + ', ' + COALESCE(e.First,'') as {nameof(QuickJobEmployees.EmployeeName)}, "
+ $" j.JobCode as {nameof(QuickJobTimeReportDTO.JobCode)}, "
+ $" j.JobName as {nameof(QuickJobTimeReportDTO.JobName)},"
+ $" c.ClientName as {nameof(QuickJobTimeReportDTO.ClientName)}, "
+ $" s.SiteName as {nameof(QuickJobTimeReportDTO.SiteName)}, "
+ $" jt.LegacyCode + ' - ' + jt.[Name] as {nameof(QuickJobEmployees.TaskName)}, "
+ $" tc.Name as {nameof(QuickJobEmployees.TaskCategory)}, "
+ $" isnull(sum(te.hours),0) as {nameof(QuickJobEmployees.Regular)}, "
+ $" isnull(sum(te.overtimehours),0) as {nameof(QuickJobEmployees.Overtime)}, "
+ $" isnull(sum(te.hours),0) + isnull(sum(te.overtimehours),0) as {nameof(QuickJobEmployees.Combined)}"
+
@"
from
dbo.Jobs j
inner join dbo.Clients c
on c.ClientId = j.ClientId
left outer join [dbo].TimeEntries te
on j.JobId = te.JobId
left outer join [dbo].Employees e
on e.EmployeeId = te.EmployeeId
inner join dbo.[Sites] s
on j.SiteId = s.SiteID
left outer join dbo.JobTasks jt
on jt.JobTaskId = te.TaskId
inner join dbo.TaskCategories tc
on tc.TaskCategoryId = jt.TaskCategoryId
where
(@JobId is null Or te.JobId = @JobId) and
te.Date >= @WeekStart and te.Date <= @WeekEnd and
ISNULL(e.[UserName],'') != '*****@*****.**' "
+ (limitToEmployeeId.HasValue ? $" and te.EmployeeId = { limitToEmployeeId.Value}" : string.Empty)
+ @" group by tc.Name,s.SiteName,
j.JobCode,
COALESCE(e.Last,'') + ', ' + COALESCE(e.First,'') ,
j.JobName, c.ClientName ,
jt.LegacyCode + ' - ' + jt.[Name],
j.JobId
Select
'Time And Expense Expenditures' as [name]
,SUM(ISNULL(e.Amount,0)) as amount
from dbo.Jobs j
inner join dbo.TimeAndExpenceExpenditures e
on e.JobId = j.JobId
where WeekId BETWEEN @WeekIdStartInclusive AND @WeekIdEndInclusive
AND (@LimitToEmployeeId is null OR @LimitToEmployeeId = e.EmployeeId)
AND (@JobId is null Or j.JobId = @JobId)
HAVING SUM(ISNULL(e.Amount,0)) > 0
UNION ALL
select
'Company Vehicle Expense' as [name]
,SUM(ISNULL((ve.TotalNumberOfDaysUsed * 125)
+ CASE WHEN ve.TotalMiles > 250
THEN (ve.TotalMiles * .50) - (250 * .50)
ELSE 0
END,0)) as amount
from dbo.Jobs j
inner join dbo.CompanyVehicleExpenditures ve
on ve.JobId = j.JobId
where WeekId BETWEEN @WeekIdStartInclusive AND @WeekIdEndInclusive
AND (@LimitToEmployeeId is null OR @LimitToEmployeeId = ve.EmployeeId)
AND (@JobId is null Or j.JobId = @JobId)
HAVING SUM(ISNULL((ve.TotalNumberOfDaysUsed * 125)
+ CASE WHEN ve.TotalMiles > 250
THEN (ve.TotalMiles * .50) - (250 * .50)
ELSE 0
END,0)) > 0
UNION ALL
select
'Contractor/PO Expense' as [name]
,SUM(ISNULL(ce.TotalPOContractAmount,0)) as amount
from dbo.Jobs j
inner join dbo.ContractorExpenditures ce
on j.JobId = ce.JobId
where WeekId BETWEEN @WeekIdStartInclusive AND @WeekIdEndInclusive
AND (@LimitToEmployeeId is null OR @LimitToEmployeeId = ce.EmployeeId)
AND (@JobId is null Or j.JobId = @JobId)
HAVING SUM(ISNULL(ce.TotalPOContractAmount,0)) > 0
UNION ALL
select
'Arc Flash Labels Expense' as [name]
,SUM(ISNULL(e.TotalLabelsCost,0) + ISNULL(e.TotalPostageCost,0)) as amount
FROM dbo.Jobs j
inner join dbo.ArcFlashlabelExpenditures e
on j.jobId = e.jobId
where WeekId BETWEEN @WeekIdStartInclusive AND @WeekIdEndInclusive
AND (@LimitToEmployeeId is null OR @LimitToEmployeeId = e.EmployeeId)
AND (@JobId is null Or j.JobId = @JobId)
HAVING SUM(ISNULL(e.TotalLabelsCost,0) + ISNULL(e.TotalPostageCost,0)) > 0
UNION ALL
select
'Miscellaneous Expense' as [name]
,SUM(ISNULL(e.Amount,0)) as amount
from dbo.Jobs j
inner join dbo.MiscExpenditures e
on j.JobId = e.JobId
where WeekId BETWEEN @WeekIdStartInclusive AND @WeekIdEndInclusive
AND (@LimitToEmployeeId is null OR @LimitToEmployeeId = e.EmployeeId)
AND (@JobId is null Or j.JobId = @JobId)
HAVING SUM(ISNULL(e.Amount,0)) > 0 ";
cmd.Parameters.Add(new SqlParameter("JobId", jobId));
cmd.Parameters.Add(new SqlParameter("LimitToEmployeeId", limitToEmployeeId.HasValue ? limitToEmployeeId : DBNull.Value));
cmd.Parameters.Add(new SqlParameter("WeekIdStartInclusive", WeekDTO.CreateWithWeekContaining(start).WeekId.Value));
cmd.Parameters.Add(new SqlParameter("WeekIdEndInclusive", WeekDTO.CreateWithWeekContaining(end).WeekId.Value));
cmd.Parameters.Add(new SqlParameter("WeekStart", start));
cmd.Parameters.Add(new SqlParameter("WeekEnd", end));
var rdr = cmd.ExecuteReader();
var map = GetColumnMap(rdr.GetColumnSchema());
var rpt = new QuickJobTimeReportDTO()
{
PeriodEnd = end,
PeriodStart = start,
Expenses = new Dictionary <string, decimal>()
};
var employeeRows = new List <QuickJobEmployees>();
var job = (await _jobsRepository.GetAsync()).SingleOrDefault(x => x.JobId == jobId);
rpt.JobCode = job.JobCode;
rpt.JobName = job.JobName;
rpt.SiteName = (await _sitesRepository.GetAll()).SingleOrDefault(x => x.SiteID == job.SiteId)?.SiteName;
rpt.ClientName = (await _clientsRepository.GetClient(job.ClientId))?.ClientName;
while (await rdr.ReadAsync())
{
if (rdr.HasRows)
{
employeeRows.Add(new QuickJobEmployees()
{
Combined = rdr.GetDecimal(map[nameof(QuickJobEmployees.Combined)]),
Regular = rdr.GetDecimal(map[nameof(QuickJobEmployees.Regular)]),
Overtime = rdr.GetDecimal(map[nameof(QuickJobEmployees.Overtime)]),
EmployeeName = rdr.GetSqlString(map[nameof(QuickJobEmployees.EmployeeName)]).Value,
TaskCategory = rdr.GetSqlString(map[nameof(QuickJobEmployees.TaskCategory)]).Value,
TaskName = rdr.GetSqlString(map[nameof(QuickJobEmployees.TaskName)]).Value,
});
}
}
rpt.Employees = employeeRows;
await rdr.NextResultAsync();
while (await rdr.ReadAsync())
{
rpt.Expenses.Add(rdr.GetString(0), rdr.GetDecimal(1));
}
return(new ReportDTO <QuickJobTimeReportDTO>()
{
Data = rpt,
ReportName = QuickJobTimeReport.QuickJobTimeReportCriteria.QUICK_JOB_TIME_REPORT_NAME,
RunSettings =
new Dictionary <string, string>()
{
{ "Generated", $"{DateTimeWithZone.EasternStandardTime.ToShortDateString()} at {DateTimeWithZone.EasternStandardTime.ToShortTimeString()}" },
{ "Company", $"Orion Engineering Co., Inc." },
{ "Showing Time From", criteria.ShowAllEmployeesForJob ? "All Employees" : "Self" },
}
});
}
}
public async Task <ReportDTO <QuickJobTimeReportDTO> > CreateQuickJobTimeReportAsync(QuickJobTimeReportCriteria settings)
{
return(await quickJobTimeReportQuery.RunAsync(settings));
}
