Opomini za zapadle račune po e-mailu
Opomini za zapadle račune po e-mailu
Obveščanje dolžnikov o zapadlih računih je dokaj mukotrpno delo. Zakaj si ga ne bi olajšali tako, da bi SQL strežnik avtomatsko vsak večer pripravil sporočilo o zapadlih postavkah in ga poslal po elektronski pošti?
Najprej pripravimo podatkovno bazo. Pošiljali bomo vsem partnerjiem, ki imajo v kontaktnih osebah vpisanega finančnega direktorja. Sporočilo pa bomo poslali le tistim, ki zapadle postavke imajo.
Lotimo se kar programiranja:
declare
@cSubjekt char(40), -- šifra partnerja
@cImePriimek varchar(255), -- ime in priimek finančnega direktorja
@cEmail varchar(255), -- naslov e-pošte
@cVezniDok varchar(20), -- vezni dokument (št. računa)
@dDatumVal datetime, -- datum zapadlosti (valute)
@dDatumDok datetime, -- datum dokumenta
@mDebet money, -- znesek v debet
@mKredit money, -- znesek v kredit
@mSaldo money, -- saldo računa
@mGrandTotal money, -- skupni saldo za partnerja
@cNaslovEmaila varchar(255), -- naslov, pod katerim bomo poslali sporočilo
@cSeznam varchar(8000) -- seznam zapadlih računov
Najprej zgoraj pripravimo vse spremenljivke, ki jih bomo potrebovali za programček, nato pa napišemo ogrodje programa:
-- pripravi recordset z vsemi finančnimi direktorji
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 -- pojdi čez vse fin. direktorje
-- !!! TU NOTRI BOMO DODALI KAJ PAMETNEGA !!! --
fetch next from crDolzniki into @cImePriimek, @cSubjekt, @cEmail
end -- pojdi čez vse fin. direktorje
close crDolzniki
deallocate crDolzniki
Vidimo, da ta delček najprej odpre recordset, ki vsebuje podatke o imenu in priimku, šifri kupca in naslovu elektronske pošte. Ker želimo pošiljati le tistim, ki naslov e-pošte imajo, lahko uporabimo običajen join namesto left/right joina (v primeru, da bi želeli vse finančne direktorje, tudi tiste, ki nimajo email naslova).
Poglejmo, kako dobimo vse zapadle račune za enega partnerja. Znova bomo naredili recordset, ki ga bomo brali račun po račun.
-- za vsakega naredi recordset z zapadlimi postavkami
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
Sedaj nam preostane le še, da podatke iz tega recordseta prenesemo v telo e-poštnega sporočila (spremenljivka @cSeznam).
set @cSeznam = ''
set @mGrandTotal = 0
open crOdprte
fetch next from crOdprte into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
while @@fetch_status = 0
begin -- napolni seznam z odprto postavko
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 -- napolni seznam z odprto postavko
close crOdprte
deallocate crOdprte
Preglejmo, če je v seznamu kaj računov in če so, pošljimo obvestilo.
if @cSeznam <> ''
begin
set @cNaslovEmaila = 'Seznam zapadlih računov za ' + @cSubjekt + ' na dan: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Vezni dokument Datum zap. Datum dok. Debet Kredit Saldo' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Skupaj:.....................................................................' + Convert(char(19),@mGrandTotal,1)
-- pošlji email sporočilo o zapadlosti
exec master.dbo.xp_sendmail
@recipients = @cEmail,
@message = @cSeznam,
@subject = @cNaslovEmaila
end
Sestavimo celoten programček in ga vpišimo med SQL jobe. Preostane le, da nastavimo urnik izvajanja in že smo končali.
 |
Pri definiranju jobov moramo upoštevati, da največja dolžina teksta, ki ga lahko spravimo v en korak SQL joba, ne sme presegati 7800 znakov. Da bi dosegli to omejitev smo spodnji primer "očistili" presledkov, vnešenih za pregledno strukturo in vseh komentarjev.
|
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 = 'Seznam zapadlih računov za ' + @cSubjekt + ' na dan: ' + Convert(varchar(12),GetDate(),104)
set @cSeznam = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSeznam
set @cSeznam = 'Vezni dokument Datum zap. Datum dok. Debet Kredit Saldo' + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cNaslovEmaila + Char(13) + Char(10) + @cSeznam
set @cSeznam = @cSeznam + Char(13) + Char(10) + 'Skupaj:.....................................................................' + 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