public async System.Threading.Tasks.Task <ActionResult> List()
{
var isAdmin = User.IsInRole(UserRoleName.Admin);
var jobs = await _jobsRepository.GetAsync();
if (!isAdmin)
{
jobs = jobs.Where(x => x.JobStatusId == JobStatus.Enabled).ToList();
}
var myJobs = await _jobsRepository.GetAsync(await sessionAdapter.EmployeeIdAsync());
var availableClients = await clientRepository.GetAllClients();
var availableSites = await siteRepository.GetAll();
var allEmployees = await _employeeRepository.GetAllEmployees();
var models = jobs.OrderBy(x => x.JobCode).Select(job =>
{
var details = new JobModelDetail
{
Client = _mapper.Map <ClientModel>(availableClients.SingleOrDefault(z => z.ClientId == job.ClientId)),
Site = _mapper.Map <SiteModel>(availableSites.SingleOrDefault(z => z.SiteID == job.SiteId)),
ProjectManager = _mapper.Map <ProjectManagerModel>(allEmployees.SingleOrDefault(z => z.EmployeeId == job.ProjectManagerEmployeeId))
};
return(job, details);
}).ToList();
var vm = new JobListViewModel()
{
AllJobsWithAssociationStatus = models.OrderBy(x => x.job.JobCode)
.ToDictionary(x => x, x => myJobs.Any(z => z.JobId == x.job.JobId)),
};
return(View("ListJobs", vm));
}
public async Task <ActionResult <IEnumerable <SiteModel> > > Get()
{
return(Ok(_mapper.Map <IEnumerable <SiteModel> >(await _sitesRepository.GetAll())));
}
public async Task <ActionResult <IEnumerable <CoreJobDto> > > SaveBulkJobs([FromBody] IEnumerable <JobUploadModel> jobs)
{
var res = new JobUploadResults()
{
NewClients = new Dictionary <string, int>(),
NewSites = new Dictionary <string, int>(),
};
try
{
var allSites = await _sitesRepository.GetAll();
var sitesBySiteName = await BulkSaveSites(allSites, jobs, newSite => res.NewSites.TryAdd(newSite.SiteName, newSite.SiteID));
var allClients = await _clientsRepository.GetAllClients();
var clientsByClientName = await BulkSaveClients(allClients, jobs, newClient => res.NewClients.TryAdd(newClient.ClientName, newClient.ClientId));
var allJobs = await _jobsRepository.GetAsync();
var newJobs = new List <CoreJobDto>();
var updatedJobs = new List <CoreJobDto>();
var skippedEntry = new List <CoreJobDto>();
foreach (var rec in jobs)
{
var isBrandNewJob = false;
var temp = rec;
var matchedJob = await SaveForMatchingField(allJobs,
matchCriteria : (existingJob, newJob) => {
var wtf = existingJob.JobCode.Replace("-", "").Equals(newJob.JobCode.Replace("-", ""), StringComparison.InvariantCultureIgnoreCase);
return(wtf);
},
factory : async(newJob) =>
{
var created = await _jobsRepository.Create(new BLL.Jobs.CreateJobDto()
{
ClientId = clientsByClientName[newJob.ClientName.Trim()],
JobCode = newJob.JobCode,
JobName = newJob.JobName,
JobStatusId = JobStatus.Enabled,
SiteId = sitesBySiteName[newJob.SiteName.Trim()],
ProjectManagerEmployeeId = newJob.ProjectManager ?? 1
});
isBrandNewJob = true;
return(created);
},
field : temp);
var nameChanged = matchedJob.JobName != rec.JobName;
var clientChanged = matchedJob.ClientId != clientsByClientName[rec.ClientName.Trim()];
var siteChanged = matchedJob.SiteId != sitesBySiteName[rec.SiteName.Trim()];
if (isBrandNewJob)
{
newJobs.Add(matchedJob);
}
else if (nameChanged || clientChanged || siteChanged)
{
matchedJob.JobName = rec.JobName;
matchedJob.ClientId = clientsByClientName[rec.ClientName.Trim()];
matchedJob.SiteId = sitesBySiteName[rec.SiteName.Trim()];
await _jobsRepository.Update(matchedJob);
updatedJobs.Add(matchedJob);
}
else
{
skippedEntry.Add(matchedJob);
}
}
res.UpdatedJobs = updatedJobs;
res.CreatedJobs = newJobs;
res.SkippedEntriesBecuaseNoChange = skippedEntry;
}
catch (Exception e)
{
return(new ObjectResult(new
{
Error = new ProblemDetails()
{
Title = e.Message,
Detail = e.ToString(),
Instance = this.HttpContext.Request.GetDisplayUrl(),
Status = StatusCodes.Status500InternalServerError,
Type = "Unhandled Error"
},
Processed = res
}));
}
return(Ok(res));
}
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" },
}
});
}
}
