Obaveštavanje dužnika o računima dospelim za naplatu je veoma mukotrpan
posao. Zašto
ga ne bi olakšali tako, da SQL server automatski svako veče pripremi
obaveštenje o dospelim stavkama i poslao ga preko elektronske pošte?
Prvo pripremimo bazu podataka. Slaćemo svim partnerima, koji imaju u kontaktnim osobama upisanog finansiskog
direktora. Obaveštenje ćemo poslati onima, koji imaju dospele stavke.
Latimo se programiranja:
declare
@cSubjekt char(40), -- šifra partnera
@cImePrezime varchar(255), -- ime i prezime finansiskog direktora
@cEmail varchar(255), -- adresa e-pošte
@cVezniDok varchar(20), -- vezani dokument (br. računa)
@dDatumVal datetime, -- datum dospeća (valute)
@dDatumDok datetime, -- datum dokumenta
@mDebet money, -- iznos u debet
@mKredit money, -- iznos u kredit
@mSaldo money, -- saldo računa
@mGrandTotal money, -- grupni saldo za partnera
@cNaslovEmaila varchar(255), -- adresa, pod kojom ćemo poslati obaveštenje
@cSpisak varchar(8000) -- spisak dospelih računa
Prvo gore pripremimo sve promenljive, koje će nam trebati za
programčić, a posle napišemo alate programa:
-- pripremi recordset sa svim finansiskim direktorijumima
declare crDolzniki cursor local fast_forward for
select RTrim(C.IME)+' '+RTrim(C.PREZIME) as IMEPREZIME,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.ULOGA like 'Finansiski direktor'
open crDolzniki
fetch next from crDolzniki into @cImePrezime, @cSubjekt, @cEmail
while @@fetch_status = 0
begin -- pojdi čez sve fin. direktore
-- !!! TU ĆEMO DODATI KAJ PAMETNEGA !!! --
fetch next from crDolzniki into @cImePrezime, @cSubjekt, @cEmail
end -- pojdi čez sve fin. direktore
close crDolzniki
deallocate crDolzniki
Vidimo, da taj delić prvo otvori recordset, koji sadrži podatke o imenu i
prezimenu, šifri kupca i adresi elektronske pošte. Pošto želimo slati onima,
koji adresu e-pošte imaju, možemo da upotrebimo običan join umesto left/right joina
(u slučaju, da bi želeli sve finansijske direktore, i one, koji nemaju email
adrese).
Pogledajmo, kako dobijamo sve dospele račune za jednoga partnera. Ponovo ćemo
napraviti recordset, koji ćemo čitati račun po račun.
-- za svakog napravi recordset sa dospelim stavkama
declare crOtvorene 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 = '2012'
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
Sada nam preostane samo još, da podatke iz tog recordseta prenesemo u telo(body)
e-mail obaveštenja (promenljia @cSpisak).
set @cSpisak = ''
set @mGrandTotal = 0
open crOtvorene
fetch next from crOtvorene into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
while @@fetch_status = 0
begin -- napuni spisak z otvoreno postavko
set @cSpisak = @cSpisak + @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 crOtvorene into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
end -- napuni spisak z otvoreno postavko
close crOtvorene
deallocate crOtvorene
Pregledajmo, da li je u spisku nešto računa i ako ima, pošaljimo obaveštenje.
if @cSpisak <> ''
begin
set @cNaslovEmaila = 'Spisak zapadlih računa za ' + @cSubjekt + ' na dan: ' + Convert(varchar(12),GetDate(),104)
set @cSpisak = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSpisak
set @cSpisak = 'Vezani dokument Datum zap. Datum dok. Debet Kredit Saldo' + Char(13) + Char(10) + @cSpisak
set @cSpisak = @cNaslovEmaila + Char(13) + Char(10) + @cSpisak
set @cSpisak = @cSpisak + Char(13) + Char(10) + 'Zajedno:.....................................................................' + Convert(char(19),@mGrandTotal,1)
-- pošlji email obaveštenje o rokove dospeća
exec master.dbo.xp_sendmail
@recipients = @cEmail,
@message = @cSpisak,
@subject = @cNaslovEmaila
end
Sastavimo celokupan programčić i upišimo ga među SQL jobe. Preostane nam,
samo da podesimo časovnik izvođenja i već smo završili.
 |
Pri definisanju jobova moramo uzimati u obzir, da najveća dužina
teksta, koji možemo da unesemo u jedan korak SQL joba, ne sme prelaziti 7800
znakova. Da bi dostigli to ograničenje donji primer smo "očistili"
od razmaka, unetih za preglednu strukturu i svih komentara.
|
declare
@cSubjekt char(40),
@cImePrezime varchar(255),
@cEmail varchar(255),
@cVezniDok varchar(20),
@dDatumVal datetime,
@dDatumDok datetime,
@mDebet money,
@mKredit money,
@mSaldo money,
@mGrandTotal money,
@cNaslovEmaila varchar(255),
@cSpisak varchar(8000)
declare crDolzniki cursor local fast_forward for
select RTrim(C.IME)+' '+RTrim(C.PREZIME) as IMEPREZIME,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 @cImePrezime, @cSubjekt, @cEmail
while @@fetch_status = 0
begin
declare crOtvorene 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 = '2012'
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 @cSpisak = ''
set @mGrandTotal = 0
open crOtvorene
fetch next from crOtvorene into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
while @@fetch_status = 0
begin
set @cSpisak = @cSpisak + @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 crOtvorene into @cVezniDok,@dDatumVal,@dDatumDok,@mDebet,@mKredit,@mSaldo
end
close crOtvorene
deallocate crOtvorene
if @cSpisak <> ''
begin
set @cNaslovEmaila = 'Spisak zapadlih računa za ' + @cSubjekt + ' na dan: ' + Convert(varchar(12),GetDate(),104)
set @cSpisak = '-----------------------------------------------------------------------------------------------' + Char(13) + Char(10) + @cSpisak
set @cSpisak = 'Vezani dokument Datum zap. Datum dok. Debet Kredit Saldo' + Char(13) + Char(10) + @cSpisak
set @cSpisak = @cNaslovEmaila + Char(13) + Char(10) + @cSpisak
set @cSpisak = @cSpisak + Char(13) + Char(10) + 'Zajedno:.....................................................................' + Convert(char(19),@mGrandTotal,1)
exec master.dbo.xp_sendmail
@recipients = @cEmail,
@message = @cSpisak,
@subject = @cNaslovEmaila
end
fetch next from crDolzniki into @cImePrezime, @cSubjekt, @cEmail
end
close crDolzniki
deallocate crDolzniki