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