Opomene za neisplaćene račune e-mailom

Ovo poglavlje sadrži uputstva za kreiranje zadatka koji identifikuje dospela potraživanja i šalje e-mail podsetnike finansijskim menadžerima dužnika.

Deklarisanje globalnih promenljivih

Prvo je potrebno deklarisati globalne promenljive:

declare
  @cSubjekt char(40),          -- ID subjekta
  @cImePriimek varchar(255),   -- puno ime finansijskog menadžera
  @cEmail varchar(255),        -- e-mail adresa
  @cVezniDok varchar(20),      -- povezani dokument (broj fakture)
  @dDatumVal datetime,         -- datum dospeća
  @dDatumDok datetime,         -- datum dokumenta
  @mDebet money,               -- iznos zaduženja
  @mKredit money,              -- iznos odobrenja
  @mSaldo money,               -- saldo
  @mGrandTotal money,          -- ukupni saldo subjekta
  @cNaslovEmaila varchar(255), -- adresa e-maila
  @cSeznam varchar(8000)       -- lista dospelih faktura

Preuzimanje finansijskih menadžera

Sledeći kod otvara skup zapisa koji sadrži imena, ID subjekta i e-mail adrese finansijskih menadžera.

Zbog toga što se e-mailovi šalju samo kontaktima sa važećom e-mail adresom, standardni JOIN je dovoljan i nema potrebe za korišćenjem LEFT JOIN ili RIGHT JOIN.

-- Priprema skupa zapisa sa svim finansijskim menadžerima
declare crDolzniki cursor local fast_forward for
  select RTrim(C.IME)+' '+RTrim(C.PRIIMEK) as IMEPRIIMEK,C.SUBJEKT, A.TEL
    from CONTACTS C
    join CONTADDRESS A on C.SUBJEKT = A.SUBJEKT and C.POZ = A.POZ and TIP = 'E'
    where C.VLOGA like 'Financial manager'

open crDolzniki
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
while @@fetch_status = 0
  begin  -- prolazak kroz sve finansijske menadžere
    -- !!! UBACITI NEŠTO KORISNO OVDE !!! --
    fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
  end

close crDolzniki
deallocate crDolzniki

Preuzimanje dospelih faktura

Sledeći upit preuzima sve dospele fakture za određeni subjekt i čuva ih u skupu zapisa.

-- Kreiranje skupa zapisa sa dospelim stavkama za svaki subjekt
declare crOdprte cursor local fast_forward for
  select
      P.VEZNIDOK,
      (select top 1 X.DATUMVAL
         from TEMEPOZ X
         where X.SUBJEKT = P.SUBJEKT
           and X.KONTO = P.KONTO
           and X.VEZNIDOK = P.VEZNIDOK
           and X.DEBET <> 0
         order by X.DATUMVAL) as DATUMVAL,
      (select top 1 X.DATUMDOK
         from TEMEPOZ X
         where X.SUBJEKT = P.SUBJEKT
           and X.KONTO = P.KONTO
           and X.VEZNIDOK = P.VEZNIDOK
           and X.DEBET <> 0
         order by X.DATUMVAL) as DATUMDOK,
      Sum(P.DEBET) as DEBET,
      Sum(P.KREDIT) as KREDIT,
      Sum(P.DEBET - P.KREDIT) as Saldo
    from TEME G
      join TEMEPOZ P on P.KLJUC = G.KLJUC
        and P.KONTO = '1200'
        and (P.DATUMVAL <= GetDate())
    where P.SUBJEKT = @cSubjekt
    group by P.VEZNIDOK, P.SUBJEKT, P.KONTO
    having (Sum(P.DEBET - P.KREDIT) <> 0)
    order by P.VEZNIDOK

Izgradnja e-mail poruke

Koristite sledeći kod za prenos podataka iz skupa zapisa u telo e-mail poruke (@cSeznam).

set @cSeznam = ''
set @mGrandTotal = 0

open crOdprte
fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo

while @@fetch_status = 0
  begin
    set @cSeznam = @cSeznam + @cVezniDok + Convert(char(12),@dDatumVal,104) + Convert(char(12),@dDatumDok,104)
                   + Convert(char(17),@mDebet,1) + Convert(char(17),@mKredit,1) + Convert(char(17),@mSaldo,1)
                   + Char(13) + Char(10)

    set @mGrandTotal = @mGrandTotal + @mSaldo

    fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
  end

close crOdprte
deallocate crOdprte

Slanje e-maila

Proverite da li lista sadrži dospele stavke i pošaljite e-mail poruku.

if @cSeznam <> ''
  begin
    set @cNaslovEmaila = 'Lista dospelih stavki za ' + @cSubjekt + ' na dan: ' + Convert(varchar(12),GetDate(),104)

    set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
    set @cSeznam = 'Povezani dokument      Datum dospeća  Datum dokumenta        Zaduženje       Odobrenje        Saldo' + Char(13) + Char(10) + @cSeznam
    set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
    set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Ukupno:.....................................................................' + Convert(char(19),@mGrandTotal,1)

    -- Slanje e-maila
    exec master.dbo.xp_sendmail
      @recipients = @cEmail,
      @message = @cSeznam,
      @subject = @cNaslovEmaila
  end

Zakazivanje zadatka

Dodajte kompletan upit u SQL Server Job, definišite raspored izvršavanja i proces je spreman za automatsko pokretanje.

Napomena: Jedan korak SQL Server Job-a može sadržati maksimalno 7.800 karaktera.

Upit ispod je dat bez komentara i nepotrebnih razmaka da bi se poštovalo ovo ograničenje.

declare
  @cSubjekt char(40),
  @cImePriimek varchar(255),
  @cEmail varchar(255),
  @cVezniDok varchar(20),
  @dDatumVal datetime,
  @dDatumDok datetime,
  @mDebet money,
  @mKredit money,
  @mSaldo money,
  @mGrandTotal money,
  @cNaslovEmaila varchar(255),
  @cSeznam varchar(8000)

declare crDolzniki cursor local fast_forward for
  select RTrim(C.IME)+' '+RTrim(C.PRIIMEK) as IMEPRIIMEK,C.SUBJEKT, A.TEL
  from CONTACTS C
  join CONTADDRESS A on C.SUBJEKT = A.SUBJEKT and C.POZ = A.POZ and TIP = 'E'
  where C.VLOGA like 'Finančni direktor'

open crDolzniki
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail

while @@fetch_status = 0
begin
  declare crOdprte cursor local fast_forward for

  select
    P.VEZNIDOK,
    (select top 1 X.DATUMVAL
       from TEMEPOZ X
      where X.SUBJEKT = P.SUBJEKT
        and X.KONTO = P.KONTO
        and X.VEZNIDOK = P.VEZNIDOK
        and X.DEBET <> 0
      order by X.DATUMVAL) as DATUMVAL,
    (select top 1 X.DATUMDOK
       from TEMEPOZ X
      where X.SUBJEKT = P.SUBJEKT
        and X.KONTO = P.KONTO
        and X.VEZNIDOK = P.VEZNIDOK
        and X.DEBET <> 0
      order by X.DATUMVAL) as DATUMDOK,
    Sum(P.DEBET) as DEBET,
    Sum(P.KREDIT) as KREDIT,
    Sum(P.DEBET - P.KREDIT) as Saldo
  from TEME G
  join TEMEPOZ P on P.KLJUC = G.KLJUC
    and P.KONTO = '1200'
    and (P.DATUMVAL <= GetDate())
  where P.SUBJEKT = @cSubjekt
  group by P.VEZNIDOK, P.SUBJEKT, P.KONTO
  having (Sum(P.DEBET - P.KREDIT) <> 0)
  order by P.VEZNIDOK

  set @cSeznam = ''
  set @mGrandTotal = 0

  open crOdprte
  fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo

  while @@fetch_status = 0
  begin
    set @cSeznam = @cSeznam + @cVezniDok + Convert(char(12),@dDatumVal,104) + Convert(char(12),@dDatumDok,104)
      + Convert(char(17),@mDebet,1) + Convert(char(17),@mKredit,1) + Convert(char(17),@mSaldo,1) + Char(13) + Char(10)

    set @mGrandTotal = @mGrandTotal + @mSaldo

    fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
  end

  close crOdprte
  deallocate crOdprte

  if @cSeznam <> ''
  begin
    set @cNaslovEmaila = 'Lista dospelih stavki za ' + @cSubjekt + ' na dan: ' + Convert(varchar(12),GetDate(),104)
    set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
    set @cSeznam = 'Povezani dokument      Datum dospeća  Datum dokumenta        Zaduženje       Odobrenje        Saldo' + Char(13) + Char(10) + @cSeznam
    set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
    set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Ukupno:.....................................................................' + Convert(char(19),@mGrandTotal,1)

    exec master.dbo.xp_sendmail
      @recipients = @cEmail,
      @message = @cSeznam,
      @subject = @cNaslovEmaila
  end

  fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
end

close crDolzniki
deallocate crDolzniki