E-mails til eksterne med registrerede timer fra Bouw7

Forneden-stående forespørgsel til Invantive Query Tool eller eventuelt en Invantive Cloud-applikation kan du bruge til at sende eksterne medarbejdere som freelancere en mail hver uge med hvilke timer de har registreret og som er berettiget til fakturering.

--
-- Send e-mail til eksterne medarbejdere for at sende deres faktura
-- baseret på de registrerede timer.
--

local undefine DATUM_START_WEEK_JJJJMMDD

select '${DATUM_START_WEEK_JJJJMMDD}'

create or replace table maildata@inmemorystorage
as
select emp.emailAddress
       label 'E-mailadresse'
,      pjt.name
       label 'Projektnavn'
,      hlg.type_name
       label 'Timetype'
,      hlg.hourlyRate
       label 'Timesats'
,      sum(hlg.hours) Hours
       label '#Timer'
,      sum(hlg.AmountEUR) AmountEur
       label 'Beløb (EUR)'
--
-- Hent kun timer fra igangværende projekter.
--
from   ( select hlg.employee_id
         ,      hlg.project_id
         ,      hlg.type_name
         ,      hlg.logDate
         ,      to_number(hlg.hourlyRate) hourlyRate
         ,      to_number(hlg.hours) hours
         ,      to_number(hlg.hours) * to_number(hlg.hourlyRate) AmountEur
         from   employeehourlogs(q => 'project.statusId = ONDERHANDENWERKID AND isExternal = true') hlg
       )
join   employees emp
on     emp.id = hlg.employee_id
join   projects pjt
on     pjt.id = hlg.project_id
--
-- Udelukke interne projekter.
--
and    pjt.category_name not in ('Intern', 'Lønberegning')
where  ( hlg.logDate >= to_date('${DATUM_START_WEEK_JJJJMMDD}', 'YYYYMMDD') )
and    ( hlg.logDate <  to_date('${DATUM_START_WEEK_JJJJMMDD}', 'YYYYMMDD') + 7 )
group
by     emp.emailAddress
,      pjt.name
,      hlg.type_name
,      hlg.hourlyRate
order
by     emp.emailAddress
,      pjt.name
,      hlg.type_name
,      hlg.hourlyRate

select *
from   maildata@inmemorystorage

local export results as "${system:userdesktopdirectory}\eksterne timer ${DATUM_START_WEEK_JJJJMMDD}.xlsx" format xlsx include headers

begin
  set smtp-host-address@mail 'smtp.office365.com';
  set smtp-user-name@mail 'john.doe@acme.eu';
  set smtp-password@mail 'mysecretpassword';
end;

insert into smtp@mail
( fromEmail
, toEmail
, isBodyHtml
, subject
, body
)
select 'info@acme.eu'
,      emailAddress
,      true
,      'Timer denne uge'
,      '<html>'
       || '<body>'
       || '<p>'
       || 'Kære ' || emailAddress || ', vedlagt timerne du må fakturere.'
       || '<ul>'
       || htmllist
       || '</ul>'
       || '</p>'
       || '</body>'
       html
from   ( select emailAddress
         ,      listagg
                ( '<li>' 
                  || htmlencode(name) 
                  || ' : ' 
                  || htmlencode(hourlyRate) 
                  || ' ' 
                  || htmlencode(hours) 
                  || ', i alt EUR ' 
                  || htmlencode(amounteur) 
                  || '</li>'
                , ''
                ) htmllist
         from   maildata@inmemorystorage
         group
         by     emailAddress
       )