E-mails aan externen met geregistreerde uren Bouw7

Wiki
, ,
1

De onderstaande query voor Invantive Query Tool of eventueel een Invantive Cloud-applicatie kunt u gebruiken om externe medewerkers zoals ZZP-ers een mail te sturen elke week met welke uren ze geregistreerd hebben en in aanmerking komen voor facturatie.

--
-- Stuur e-mail naar externe medewerkers om hun factuur te sturen
-- op basis van de geregistreerde uren.
--

local undefine DATUM_START_WEEK_JJJJMMDD

select '${DATUM_START_WEEK_JJJJMMDD}'

create or replace table maildata@inmemorystorage
as
select emp.emailAddress
       label 'E-mailadres'
,      pjt.name
       label 'Projectnaam'
,      hlg.type_name
       label 'Urensoort'
,      hlg.hourlyRate
       label 'Uurtarief'
,      sum(hlg.hours) Hours
       label '#Uren'
,      sum(hlg.AmountEUR) AmountEur
       label 'Bedrag (EUR)'
--
-- Haal alleen uren op van onderhanden projecten.
--
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
--
-- Uitsluiten interne projecten.
--
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}\externe uren ${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
,      'Uren deze week'
,      '<html>'
       || '<body>'
       || '<p>'
       || 'Geachte ' || emailAddress || ', bijgaand de uren die u mag factureren.'
       || '<ul>'
       || htmllist
       || '</ul>'
       || '</p>'
       || '</body>'
       html
from   ( select emailAddress
         ,      listagg
                ( '<li>' 
                  || htmlencode(name) 
                  || ' : ' 
                  || htmlencode(hourlyRate) 
                  || ' ' 
                  || htmlencode(hours) 
                  || ', in totaal EUR ' 
                  || htmlencode(amounteur) 
                  || '</li>'
                , ''
                ) htmllist
         from   maildata@inmemorystorage
         group
         by     emailAddress
       )