Notificaciones: ejemplos vistos en clase

Hola

Aquí os dejo las sentencias para las notificaciones que hemos creado en clase.

NOTICE SENTENCE:

Tareas a realizar ésta semana.

select 
'Tasks to complete this week' AS Title
,'<span> You have' + cast(count (1) as varchar) + '<small class="txt-muted">  tasks to finish</small><span>' AS [Message]
, CAST(GETDATE() AS smalldatetime) AS ReminderTime
, CAST(DATEADD(WEEK,1,GETDATE()) AS smalldatetime) AS ExpiryTime
, 'app' AS MethodName
, 0 AS AllUsers
, null AS UserId 
, ACTION.employee_id AS currentReference 
, 'object' AS TypeId
, 'ACTIONS' AS ObjectName
, 'ACTION.state_id between 1 and 2 and cast(ACTION.scheduledend as date) 
between cast(GETDATE() as date) 
and CAST(DATEADD(WEEK,1,GETDATE()) as date) AND (ACTION.employee_id=''{{currentReference}}'')' AS ObjectWhere
, 'list' AS PageTypeId
from ACTION
where cast(scheduledend as date) between 
cast(GETDATE() as date) and CAST(DATEADD(WEEK,1,GETDATE()) as date)
and ACTION.state_id between 1 and 2
group by ACTION.employee_id

Notice users

select 
DISTINCT -1 as UserId
, ACTION.employee_id as CurrentReference
from ACTION
where cast(scheduledend as date) between 
cast(GETDATE() as date) and CAST(DATEADD(WEEK,1,GETDATE()) as date)
and ACTION.state_id between 1 and 2
group by ACTION.employee_id

EMail sentence

select 
DISTINCT -1 as UserId
, ACTION.employee_id as CurrentReference
,'myemail@mydomain.com' as FromEmail
,'My name' as FromName
,'New pending actions' as Subject
,'You have new pending action' as Body
, cast(getdate() as smallDatetime) as SendDate
,'e.emailas [To]
,null as attachments
from ACTION
INNER JOIN EMPLOYEE e on e.employee_id=action.employee_id
where cast(scheduledend as date) between 
cast(GETDATE() as date) and CAST(DATEADD(WEEK,1,GETDATE()) as date)
and ACTION.state_id between 1 and 2
group by ACTION.employee_id

0 Votos