simplify select n.id, nt.Name as [Type], n.CreatedBy as FromId, n.CreatedOn as [SentAt],…
- User
simplify select n.id, nt.Name as [Type], n.CreatedBy as FromId, n.CreatedOn as [SentAt], n.ContextId as [ViewId], o.name as OrgName, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0 as BreakMinutes, Comment as Message, null as OrgLocation, null as CurrentLocation,n.ReadAt, n.SentTo, tz.Name as Orgtimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN jobs j on n.ContextId = j.Id INNER JOIN orgs o on j.orgid = o.id INNER JOIN timezones tz on tz.Id=o.TimezoneId INNER JOIN JobComments m on j.id = m.jobid and n.CreatedBy = m.UserId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.Name = 'JOB_COMMENT' UNION -- job related select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as [SentAt], n.ContextId as [ViewId], o.name as OrgName, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0, j.WithdrawReason as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimeZone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN jobs j on n.ContextId = j.Id INNER JOIN orgs o on j.orgid = o.id INNER JOIN timezones tz on tz.Id=o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name like 'JOB_%' and nt.name not in ('JOB_COMMENT', 'JOB_START_REMIND') and nt.name not like 'JOB_REQUEST_%' and nt.name not like 'JOB_END_DATE_UPDATED_%' and nt.name not like 'JOB_SITE_%' UNION -- clock in/out related select n.id, nt.Name as Type, case when nt.name = 'CC_CDD_HASNT_CLOCKED_IN' then jc.CandidateId when nt.name = 'CC_JOB_STARTED_YOU_HAVENT_CLOCKED_IN' then j.CreatedBy else n.createdby end as [FromId],n.CreatedOn as [SentAt], case when nt.name = 'CC_CDD_HASNT_CLOCKED_IN' then j.Id else n.ContextId end as [ViewId], o.name as OrgName, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0, '' as Message, Cast(a.Location as varbinary(MAX)) as OrgLocation, Cast(ccl.CurrentLocation as varbinary(MAX)) as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimeZone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN JobTimesheets jt on n.ContextId = jt.Id INNER JOIN JobCandidates jc on jc.Id = jt.JobCandidateId INNER JOIN Jobs j on j.Id = jc.JobId INNER JOIN orgs o on j.orgid = o.id INNER JOIN timezones tz on tz.Id = o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id LEFT JOIN CandidateCurrentLocations ccl on ccl.TimesheetId = n.ContextId where nt.name like 'CC_%' UNION -- reminder before job start select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as [SentAt], n.ContextId as [ViewId], o.name as OrgName, a.Suburb, j.JobTitle as Title, jd.StartTime, jd.EndTime, 0, CAST(j.Id as varchar(10)) as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimeZone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN JobDays jd on n.ContextId = jd.Id INNER JOIN Jobs j on j.Id = jd.JobId INNER JOIN orgs o on j.orgid = o.id INNER JOIN timezones tz on tz.Id=o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.Name = 'JOB_START_REMIND' UNION -- timesheet select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, n.ContextId as [ViewId], OrgName, Suburb, JobTitle, StartTime, EndTime, BreakMinutes, '' as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, t.OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN ( select t.id as jobtimesheetid, c.jobid, o.name as OrgName, a.Suburb, JobTitle, t.StartTime, t.EndTime, t.BreakMinutes, tz.Name as OrgTimezone from JobTimesheets t INNER JOIN JobCandidates c on t.JobCandidateId = c.Id INNER JOIN jobs j on c.jobid = j.id INNER JOIN orgs o on j.orgid = o.id INNER JOIN timezones tz on tz.Id=o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id ) t on n.ContextId = t.jobtimesheetid where nt.name like 'TIMESHEET_%' UNION -- fav added select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, 0 as [ViewId], o.name as OrgName, a.Suburb, 'Added as a favourite!' as Title, null, null, 0, '' as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN orgs o on n.contextId = o.id INNER JOIN timezones tz on tz.Id=o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name = 'FAVOURITE_ADDED' UNION -- expense claim select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, n.ContextId as [ViewId], o.name as OrgName, a.Suburb, 'Expense Claim Notification' as Title, ts.StartTime, ts.EndTime, 0, CONVERT(varchar, CONVERT(DECIMAL(10,2), ec.Quantity * ec.UnitPrice)) as Message, null as OrgLocation, --Message is used as amount here null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN ExpenseClaims ec on n.ContextId = ec.Id INNER JOIN JobTimesheets ts on ec.TimesheetId = ts.Id INNER JOIN JobCandidates jc on ts.JobCandidateId = jc.Id INNER JOIN Jobs j on jc.JobId = j.Id INNER JOIN Orgs o on j.OrgId = o.Id INNER JOIN timezones tz on tz.Id=o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name like 'EXPENSE_CLAIM_%' UNION -- expiring verifications select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, v.Id as [ViewId], s.name as OrgName, a.Suburb, CONCAT('Your ',vt.Name,' is expiring!') as Title, null, null, 0, CONCAT('Your ', vt.Name,' in ', s.Name,' is expiring in ',DATEDIFF(day,GETUTCDATE (), v.DocExpiryDate), case when DATEDIFF(day,GETUTCDATE (), v.DocExpiryDate) > 1 then ' days' else ' day' end, '!') as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN verificationLogs v on v.Id = n.contextId INNER JOIN verificationTypes vt on vt.Id = v.VerificationTypeId INNER JOIN jbusers sndr on n.CreatedBy = sndr.AspNetUserId INNER JOIN orgs o on sndr.orgid = o.id INNER JOIN syndicates s on s.Id=v.syndicateid INNER JOIN JBUsers vc on v.CreatedBy = vc.AspNetUserId INNER JOIN orgs vco on vc.OrgId = vco.Id INNER JOIN timezones tz on tz.Id = vco.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name = 'VERIFICATION_EXPIRING' UNION -- job requests select n.Id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, n.ContextId as [ViewId], o.name as OrgName, a.Suburb, tp.Name as Title, (select min(StartTime) from JobTimesheetRequests where JobRequestId = jr.Id), (select max(EndTime) from JobTimesheetRequests where JobRequestId = jr.Id), 0, case when nt.Name = 'JOB_REQUEST_SUBMITTED' then CONCAT('Please confirm this job request from ', sndr.FirstName, ' ', sndr.LastName, '.') when nt.Name = 'JOB_REQUEST_APPROVED' then CONCAT('Your job record at ', o.Name, ' is approved.') when nt.Name = 'JOB_REQUEST_DECLINED' then CONCAT('Your job record at ', o.Name, ' is declined.') end as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN JobRequests jr on jr.Id = n.contextId INNER JOIN JobTimesheetRequests jtr on jtr.JobRequestId = jr.Id INNER JOIN TalentPools tp on tp.Id = jr.TalentPoolId INNER JOIN jbusers sndr on sndr.AspNetUserId = n.CreatedBy INNER JOIN orgs o on o.id = jr.OrgId INNER JOIN timezones tz on tz.Id = o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name like 'JOB_REQUEST_%' UNION -- job extended to org contact select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId], o.name as OrgName, a.Suburb, j.JobTitle as Title, null starttime, j.EndDate as endtime /* newEndDateTime /, 0, STRING_AGG(jc.FirstName, ', ') as Message / candidatefirstnames /, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN jobs j on n.ContextId = j.Id inner join (select cdd.FirstName, jc.JobId from JobCandidates jc inner join JBUsers cdd on jc.CandidateId = cdd.AspNetUserId where jc.InviteStatusId = 1) jc on j.id = jc.JobId INNER JOIN orgs o on j.OrgId = o.Id INNER JOIN timezones tz on tz.Id = o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name = 'JOB_END_DATE_UPDATED_TO_ORG_CONTACT' group by n.id, nt.name, n.CreatedBy, n.CreatedOn, j.id, o.name, a.Suburb, j.JobTitle, n.ReadAt, n.SentTo, tz.Name, j.EndDate UNION -- job extended to cdd select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId], o.name as OrgName, a.Suburb, j.JobTitle as Title, null starttime, j.EndDate as endtime / newEndDateTime */, 0, '' as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN jobs j on n.ContextId = j.Id INNER JOIN orgs o on j.OrgId = o.Id INNER JOIN timezones tz on tz.Id = o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id
where nt.name = 'JOB_END_DATE_UPDATED_TO_CDD' UNION -- site changed to orgcontact select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId], case when isnull(s.name, '') = '' then o.name else s.name end as OrgName /* sitename */, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0, STRING_AGG(jc.FirstName, ', ') as Message /* candidatefirstnames */, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN jobs j on n.ContextId = j.Id left join sites s on j.SiteId = s.id inner join (select cdd.FirstName, jc.JobId from JobCandidates jc inner join JBUsers cdd on jc.CandidateId = cdd.AspNetUserId where jc.InviteStatusId = 1) jc on j.id = jc.JobId INNER JOIN orgs o on j.OrgId = o.Id INNER JOIN timezones tz on tz.Id = o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name = 'JOB_SITE_UPDATED_TO_ORG_CONTACT' group by n.id, nt.name, n.CreatedBy, n.CreatedOn, j.id, o.name, a.Suburb, j.StartDate, j.EndDate, j.JobTitle, s.name, n.ReadAt, n.SentTo, tz.Name, j.EndDate UNION -- site changed to cdd select n.id, nt.name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId], case when isnull(s.name, '') = '' then o.name else s.name end as OrgName /* sitename */, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0, '' as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id INNER JOIN jobs j on n.ContextId = j.Id inner join sites s on j.SiteId = s.id INNER JOIN orgs o on j.OrgId = o.Id INNER JOIN timezones tz on tz.Id = o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id where nt.name = 'JOB_SITE_UPDATED_TO_CDD' - Assistant
SELECT n.id, nt.Name AS [Type], n.CreatedBy AS [FromId], n.CreatedOn AS [SentAt], n.ContextId AS [ViewId], o.name AS OrgName, a.Suburb, j.JobTitle AS Title, j.StartDate, j.EndDate, 0 AS BreakMinutes, Comment AS Message, NULL AS OrgLocation, NULL AS CurrentLocation, n.ReadAt, n.SentTo, tz.Name AS Orgtimezone FROM notifications n INNER JOIN NotificationTypes nt ON n.NotificationTypeId = nt.id INNER JOIN jobs j ON n.ContextId = j.Id INNER JOIN orgs o ON j.orgid = o.id INNER JOIN timezones tz ON tz.Id=o.TimezoneId INNER JOIN JobComments m ON j.id = m.jobid AND n.CreatedBy = m.UserId INNER JOIN Addresses a ON o.AddressId = a.Id WHERE nt.Name = 'JOB_COMMENT' UNION SELECT n.id, nt.Name AS [Type], n.CreatedBy AS [FromId], n.CreatedOn AS [SentAt], n.ContextId AS [ViewId], o.name AS OrgName, a.Suburb, j.JobTitle AS Title, j.StartDate, j.EndDate, 0, j.WithdrawReason AS Message, NULL AS OrgLocation, NULL AS CurrentLocation, n.ReadAt, n.SentTo, tz.Name AS OrgTimeZone FROM notifications n INNER JOIN NotificationTypes nt ON n.NotificationTypeId = nt.id INNER JOIN jobs j ON n.ContextId = j.Id INNER JOIN orgs o ON j.orgid = o.id INNER JOIN timezones tz ON tz.Id=o.TimezoneId INNER JOIN Addresses a ON o.AddressId = a.Id WHERE nt.name LIKE 'JOB_%' AND nt.name NOT IN ('JOB_COMMENT', 'JOB_START_REMIND') AND nt.name NOT LIKE 'JOB_REQUEST_%' AND nt.name NOT LIKE 'JOB_END_DATE_UPDATED_%' AND nt.name NOT LIKE 'JOB_SITE_%' UNION SELECT n.id, nt.Name AS Type, CASE WHEN nt.name = 'CC_CDD_HASNT_CLOCKED_IN' THEN jc.CandidateId WHEN nt.name = 'CC_JOB_STARTED_YOU_HAVENT_CLOCKED_IN' THEN j.CreatedBy ELSE n.createdby END AS [FromId], n.CreatedOn AS [SentAt], CASE WHEN nt.name = 'CC_CDD_HASNT_CLOCKED_IN' THEN j.Id ELSE n.ContextId END AS [ViewId], o.name AS OrgName, a.Suburb, j.JobTitle AS Title, j.StartDate, j.EndDate, 0, '' AS Message, Cast(a.Location AS varbinary(MAX)) AS OrgLocation, Cast(ccl.CurrentLocation AS varbinary(MAX)) AS CurrentLocation, n.ReadAt, n.SentTo, tz.Name AS OrgTimeZone FROM notifications n INNER JOIN NotificationTypes nt ON n.NotificationTypeId = nt.id INNER JOIN JobTimesheets jt ON n.ContextId = jt.Id INNER JOIN JobCandidates jc ON jc.Id = jt.JobCandidateId INNER JOIN Jobs j ON j.Id = jc.JobId INNER JOIN orgs o ON j.orgid =