E-mails aux externes avec les heures enregistrées de Bouw7

La requête ci-dessous pour Invantive Query Tool ou éventuellement une application Invantive Cloud peut être utilisée pour envoyer chaque semaine un e-mail aux collaborateurs externes, tels que les indépendants (freelances), indiquant les heures qu’ils ont enregistrées et qui sont éligibles à la facturation.

--
-- Envoyer un e-mail aux collaborateurs externes pour qu'ils envoient leur facture
-- sur la base des heures enregistrées.
--

local undefine DATUM_START_WEEK_JJJJMMDD

select '${DATUM_START_WEEK_JJJJMMDD}'

create or replace table maildata@inmemorystorage
as
select emp.emailAddress
       label 'Adresse e-mail'
,      pjt.name
       label 'Nom du projet'
,      hlg.type_name
       label 'Type d'heures'
,      hlg.hourlyRate
       label 'Tarif horaire'
,      sum(hlg.hours) Hours
       label '#Heures'
,      sum(hlg.AmountEUR) AmountEur
       label 'Montant (EUR)'
--
-- Récupérer uniquement les heures des projets en cours.
--
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
--
-- Exclure les projets internes.
--
and    pjt.category_name not in ('Intern', 'Loonberekening')
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}\heures externes ${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
,      'Heures de cette semaine'
,      '<html>'
       || '<body>'
       || '<p>'
       || 'Cher/Chère ' || emailAddress || ', veuillez trouver ci-joint les heures que vous pouvez facturer.'
       || '<ul>'
       || htmllist
       || '</ul>'
       || '</p>'
       || '</body>'
       html
from   ( select emailAddress
         ,      listagg
                ( '<li>' 
                  || htmlencode(name) 
                  || ' : ' 
                  || htmlencode(hourlyRate) 
                  || ' ' 
                  || htmlencode(hours) 
                  || ', soit un total de ' 
                  || htmlencode(amounteur) 
                  || ' EUR</li>'
                , ''
                ) htmllist
         from   maildata@inmemorystorage
         group
         by     emailAddress
       )