Mementouri prin e-mail pentru creanțe restante
Acest subiect conține instrucțiuni pentru crearea unui job care identifică creanțele restante și trimite mementouri prin e-mail managerilor financiari ai debitorilor.
Declararea variabilelor globale
Mai întâi, declarați variabilele globale:
declare
@cSubjekt char(40), -- ID subiect
@cImePriimek varchar(255), -- numele complet al managerului financiar
@cEmail varchar(255), -- adresă de e-mail
@cVezniDok varchar(20), -- document legat (număr factură)
@dDatumVal datetime, -- data scadenței
@dDatumDok datetime, -- data documentului
@mDebet money, -- sumă debit
@mKredit money, -- sumă credit
@mSaldo money, -- sold
@mGrandTotal money, -- sold total al subiectului
@cNaslovEmaila varchar(255), -- subiectul e-mailului
@cSeznam varchar(8000) -- listă de facturi restante
Preluarea managerilor financiari
Următorul cod deschide un set de înregistrări care conține numele, ID-urile subiectelor și adresele de e-mail ale managerilor financiari.
Deoarece e-mailurile sunt trimise doar către contactele cu o adresă de e-mail validă, un JOIN standard este suficient și nu este necesar să se folosească un LEFT JOIN sau RIGHT JOIN.
-- Pregătește setul de înregistrări cu toți managerii financiari
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 -- parcurge toți managerii financiari
-- !!! INSEREAZĂ CEVA UTIL AICI !!! --
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
end
close crDolzniki
deallocate crDolzniki
Preluarea facturilor restante
Următoarea interogare preia toate facturile restante pentru un anumit subiect și le stochează într-un set de înregistrări.
-- Creează un set de înregistrări cu elementele restante pentru fiecare subiect
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
Construirea mesajului de e-mail
Folosiți următorul cod pentru a transfera datele din setul de înregistrări în corpul mesajului de e-mail (@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
Trimiterea e-mailului
Verificați dacă lista conține elemente restante și trimiteți mesajul de e-mail.
if @cSeznam <> ''
begin
set @cNaslovEmaila = 'Listă de elemente restante pentru ' + @cSubjekt + ' la data: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Document legat Data scadenței Data doc. Debit Credit Sold' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Total:.....................................................................' + Convert(char(19),@mGrandTotal,1)
-- Trimite e-mail
exec master.dbo.xp_sendmail
@recipients = @cEmail,
@message = @cSeznam,
@subject = @cNaslovEmaila
end
Programarea jobului
Adăugați interogarea completă într-un Job SQL Server, definiți un program de execuție, iar procesul este gata să ruleze automat.
Notă: Un pas al unui Job SQL Server poate conține maximum 7.800 de caractere.
Interogarea de mai jos este furnizată fără comentarii și spații inutile pentru a respecta această limitare.
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 = 'Listă de elemente restante pentru ' + @cSubjekt + ' la data: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Document legat Data scadenței Data doc. Debit Credit Sold' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Total:.....................................................................' + 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