Re: [Emerald] rebatch behind the scenes .... ?

Will LaSala ( will@greennet.net )
Fri, 11 Jun 1999 12:17:15 -0400

Ok I have a little better Stored Proc for emailing those customer with
expired and about to expire CC
This script will email the customer 3 months before thier account is going
to expire
to inform them to fix this before it does.
There maybe bugs in this and make sure you change the email message and
blindcc address.
I have yet to full test it here since I have no idea how to get SQLMail
working on the SQL Server without an Exchange server but I'll figure
something out (any help out there?)

Thanks to Dan Tang for his part in this script since this is just a build
off of his.
P.S. I forgot to mention that you must have only four or three digits in
your CCExpireDate fields!!!! It is very important that you don't have any
"-"'s or "/"'s in it!
The field should be in the format of mmyy or myy!
Will ,,,=^. .^=,,,
Webmaster
GreenNet® ==========================================
"Link Locally ~ Surf Globally©" http://www.greennet.net
Come Play On The Best Quake Server in the Northeast
Serving Eastern Massachusetts and Southern New Hampshire
TEL: 978-363-8898 FAX: 978-363-1225 email:will@greennet.net
===================================================
--------CUT Here----------
declare CCExpireDate INSENSITIVE cursor for
Select m.CreditCardExpire, m.CustomerID
from subaccounts s, masteraccounts m
where s.CustomerID=m.CustomerID
and m.PayMethod = "Credit Card"
and DatePart(mm,maExpireDate) = DatePart(mm,GETDATE()) + 3
and DatePart(yy,maExpireDate) = DatePart(yy,GETDATE())
and datalength(s.email)>0
declare @CCDate varchar(25)
declare @CustID int

open CCExpireDate
fetch next from CCExpireDate into @CCDate,@CustID
while @@FETCH_STATUS=0
begin
if DATALENGTH(@CCDate) = 3
declare curMailer INSENSITIVE cursor for
select s.email as MailBox, m.FirstName , m.LastName, m.maExpireDate,
SUBSTRING(CreditCardExpire, 1, 1) as CCExpireMonth, Right(CreditCardExpire,
2) as CCExpireYear
from subaccounts s, masteraccounts m
where m.CustomerID = @CustID
and s.CustomerID = @CustID
and m.PayMethod = "Credit Card"
and DatePart(mm,maExpireDate) = DatePart(mm,GETDATE()) + 3
and DatePart(yy,maExpireDate) = DatePart(yy,GETDATE())
and datalength(s.email)>0
Else
declare curMailer INSENSITIVE cursor for
select s.email as MailBox, m.FirstName , m.LastName, m.maExpireDate,
SUBSTRING(CreditCardExpire, 1, 2) as CCExpireMonth, Right(CreditCardExpire,
2) as CCExpireYear
from subaccounts s, masteraccounts m
where m.CustomerID = @CustID
and s.CustomerID = @CustID
and m.PayMethod = "Credit Card"
and DatePart(mm,maExpireDate) = DatePart(mm,GETDATE()) + 3
and DatePart(yy,maExpireDate) = DatePart(yy,GETDATE())
and datalength(s.email)>0

declare @email varchar(50)
declare @FirstName varchar(50)
declare @LastName varchar(50)
declare @MsgBody varchar(255)
declare @CCMonth varchar(4)
declare @CCYear varchar(4)
declare @AccountExpireDate datetime

open curMailer
fetch next from curMailer into
@email,@FirstName,@LastName,@AccountExpireDate,@CCMonth,@CCYear
IF (Convert(int, DatePart(mm, @AccountExpireDate)) >= Convert(int,@CCMonth))
and (Convert(int, DatePart(yy, @AccountExpireDate)) = Convert(int,"19" +
@CCYear))
Select @MsgBody='Dear ' + @FirstName + ' ' + @LastName + ',' + char(13)
select @MsgBody='Your Credit Card is about to Expire.' + char(13)
select @MsgBody='Our Records show that the Credit Card you have given
us will expire on ' + @CCMonth + "/" + @CCYear + "." + char(13)
select @MsgBody='Please call us before this time to update our
records.' + char(13)
select @MsgBody='If we do not here from you before this time we will
start issuing you an invoice, and an addtional invoicing fee of $2.00.' +
char(13)
select @MsgBody='To avoid this additional charge please call us today
at 978-363-8898' + char(13) + char(13)
select @MsgBody='Regards,'+ char(13) + char(13)
select @MsgBody='GreenNet Billing Department'
exec master..xp_sendmail @recipients=@email,
@blind_copy_recipients="billing@greennet.net", @subject='Our Credit Card
Information', @Message=@MsgBody
close curMailer
deallocate curMailer
fetch next from CCExpireDate into @CCDate,@CustID
end
close CCExpireDate
deallocate CCExpireDate