Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/O Code)
 Sending mail upon birthday		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

balexandre
Junior Member

Denmark
418 Posts
Posted - 19 May 2010 :  17:52:12  Show Profile  Visit balexandre's Homepage  Send balexandre an ICQ Message  Reply with Quote
Did anyone made this to complement the Birthdays Module?
Bruno Alexandre
(Strøby, DANMARK)

"a Portuguese in Danmark"

modifichicci
Average Member

Italy
787 Posts
Posted - 20 May 2010 :  12:22:10  Show Profile  Visit modifichicci's Homepage  Reply with Quote
yes i did
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum
Go to Top of Page

balexandre
Junior Member

Denmark
418 Posts
Posted - 21 May 2010 :  13:37:40  Show Profile  Visit balexandre's Homepage  Send balexandre an ICQ Message  Reply with Quote
care to share?
Bruno Alexandre
(Strøby, DANMARK)

"a Portuguese in Danmark"
Go to Top of Page

modifichicci
Average Member

Italy
787 Posts
Posted - 21 May 2010 :  17:05:24  Show Profile  Visit modifichicci's Homepage  Reply with Quote
you need to add a field called M_SENTEMAIL to the table MEMBERS_BDATES
then add this function to cal_functions.asp

sub EmailBirthdays                                   '##
'#######################################################

strSql = " SELECT * FROM " & strMemberTablePrefix & "MEMBERS_BDATES ORDER BY DOB "
set rs = my_Conn.Execute(strSql)

if rs.EOF then
	'no birthdays at all :D
else
	
'bTodayFound = False
	rs.movefirst
	do while not(rs.EOF)
	idmembro = rs("MEMBER_ID")
	sentmail = rs("M_SENTEMAIL")

	 If sentmail = 0 then
	      	if DateToStr(rs("DOB")&"235900") > DateToStr(todaysdate) and DateToStr(rs("DOB")&"000000") < DateToStr(todaysdate) then
		       '	bTodayFound = true
			strSQL1 = " SELECT M_NAME, M_EMAIL, M_RECEIVE_EMAIL  FROM " & strMemberTablePrefix & "MEMBERS "
			strSQL1 = strSQL1 & " WHERE MEMBER_ID = " & idmembro
			set rsmail= my_Conn.Execute(strSQL1)
			 	mricevimail = rsmail("M_RECEIVE_EMAIL") 
			strFromName = strForumTitle
					strRecipientsName = rsMail("M_NAME")
					strRecipients = rsMail("M_EMAIL")
					strSender = strSender
					strSubject = "Buon Compleanno!!"
					strMessage = "Ciao " & strRecipientsName &  vbNewline & vbNewline
					strMessage = strMessage & "Tanti Auguri di buon Compleanno da tutti noi!!" &  vbNewline & vbNewline
					strMessage = strMessage & "Da:  " & strForumURL & vbNewline & vbNewline

					htmlflag = 0
					if mricevimail = "1" then
				%><!--#INCLUDE FILE="inc_mail.asp" --><%
				end if
				strMessage =""
			   	rsmail.close
				set rsmail = nothing
			
	              	strSql = " UPDATE " & strMemberTablePrefix & "MEMBERS_BDATES SET M_SENTEMAIL = 1 WHERE MEMBER_ID = " & idmembro
		my_Conn.Execute (strSql)
	      	end if
	      	end if
		rs.Movenext
	loop
       '	rs.movefirst
	
		
end if
rs.close
set rs = nothing

End sub '### EmailBirthdays ###


in cal_deafult add
EmailBirthdays
after
WriteBirthdays

I add a variable to enable the email birthday function, but you can delete the if then part


PrepBdayTables

WriteBirthdays
if strBirthMail = "1" and strEmail = "1" then
EmailBirthdays  
end if
Response.Write	"				</TD></TR></TABLE>" & vbNewLine

Response.Write 	"</TD></TR>" & vbNewLine
Response.Write 	"</TABLE>" & vbNewLine
'### Birthday Mod - Must Use Birthdate in Member Profile - Huntress Beta v2.0 ###


this is a quick answer, i hope to have written all the changes, i did it a long time ago and i haven't the documentation
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum
Edited by - modifichicci on 21 May 2010 17:06:43
Go to Top of Page

balexandre
Junior Member

Denmark
418 Posts
Posted - 07 August 2010 :  10:26:54  Show Profile  Visit balexandre's Homepage  Send balexandre an ICQ Message  Reply with Quote
I have my code running but I had to take a diff approach, using only MEMBERS table so I dont need to query twice for each email, I can share my code if needed.

In your code, the

DateToStr(rs("DOB")&"235900") > DateToStr(todaysdate)


is always False as DOB contains YEAR, in my case it would be 19770128 that, when todaysdate is 20110128 will never work!
Other thing is to reset the SentEmail flag back to 0, on a day that is bigger that the DOB month and day.

maybe you should review your code...

But thank you for the heads up!
Bruno Alexandre
(Strøby, DANMARK)

"a Portuguese in Danmark"
Go to Top of Page

modifichicci
Average Member

Italy
787 Posts
Posted - 09 August 2010 :  15:06:03  Show Profile  Visit modifichicci's Homepage  Reply with Quote
DOB in MEMBERS_BDATES is memorized as the last birthday, not as the real birthday. So If my date is 19770128, in DOB I have 20100128, so when date is bigger (20110128) mail is sent and field is changed
DOB memorize date only for members posting in last 30 days and is deleted 30 days after..
That is the birthday mod, it is not mine, i have used his function to let send mail with not a lot of changes... It is working perfectly in every forum of our distribution, but thanks if you would share your code, so i can see something usefull.. Thanks!|
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum
Go to Top of Page

Carefree
Advanced Member

Philippines
4206 Posts
Posted - 21 August 2010 :  20:04:12  Show Profile  Reply with Quote
I did this for someone a year or so ago. I used a different approach, though. I created a 4 char text field (M_SENT_DOBMAIL) in the members table to hold the year. That's the only dbase mod necessary. This method doesn't rely upon a later erasure of the contents of the DOBMAIL field.

You can add this function to either "cal_functions.asp" or "inc_func_common.asp", but I would recommend leaving it with the calendar.


Function BirthMail
	strSql = "SELECT M_NAME, MEMBER_ID, M_EMAIL, M_RECEIVE_EMAIL, M_DOB, M_SENT_DOBMAIL FROM " & strMemberTablePrefix & "MEMBERS WHERE MID(M_DOB,5)='" & MID(DATETOSTR(STRFORUMTIMEADJUST),5,4) & "' ORDER BY M_DOB"
	set rsBirthMail = my_Conn.Execute(strSql)
	if not rsBirthMail.EOF then
		rsBirthMail.MoveFirst
		Do while not rsBirthMail.EOF
			strSent=rsBirthMail("M_SENT_DOBMAIL")
			strYY=left(DatetoStr(strForumTimeAdjust),4)
			If isNull(strSent) or (strSent <> strYY) then
				strFromName = strForumTitle
				strRecipientsName = rsBirthMail("M_NAME")
				strRecipients = rsBirthMail("M_EMAIL")
				strSender = strSender
				strSubject = "Happy Birthday!"
				strMessage = "Dear " & rsBirthMail("M_NAME")
				strMessage = strMessage & "<br><br>Happy birthday from everyone at <a href=" & strForumURL & ">" & strForumTitle & "</a>." & vbNewline & vbNewline
				htmlflag = 0
				if rsBirthMail("M_RECEIVE_EMAIL") = 1 then
					%><!--#INCLUDE FILE="inc_mail_html.asp" --><%
				end if
				strMessage =""
				strSql = " UPDATE " & strMemberTablePrefix & "MEMBERS SET M_SENT_DOBMAIL = '" & strYY & "' WHERE MEMBER_ID = " & rsBirthMail("MEMBER_ID")
				my_Conn.Execute(strSql)
			end if
			rsBirthMail.MoveNext
		Loop
		rsBirthMail.Close
	end if
	set rsBirthMail = Nothing
End Function


With that done, simply add one line to the top of "cal_default.asp".


Search for the following lines (appx 11-17):

%>

<!--#INCLUDE FILE="cal_functions.asp"-->

<LINK rel="stylesheet" href="cal_style.asp" type="text/css">

<%


After those lines, insert the following:

BirthMail
Edited by - Carefree on 21 August 2010 20:08:30
Go to Top of Page

Sean Sounds
Starting Member

20 Posts
Posted - 23 August 2010 :  15:34:49  Show Profile  Reply with Quote
Hey Carefree. I'm getting an error on the second line of the code which says:

Microsoft OLE DB Provider for SQL Server error '80040e14'

'MID' is not a recognized function name.

cal_functions.asp, line 550

Any suggestions? Thanks
Go to Top of Page

Carefree
Advanced Member

Philippines
4206 Posts
Posted - 24 August 2010 :  20:09:57  Show Profile  Reply with Quote
It shouldn't be trying to read it as a function, it's reading a portion of a string value.
Go to Top of Page

Sean Sounds
Starting Member

20 Posts
Posted - 25 August 2010 :  15:43:11  Show Profile  Reply with Quote
quote:
Originally posted by Carefree

It shouldn't be trying to read it as a function, it's reading a portion of a string value.


I figured it out.

Changed the second line from:

strSql = "SELECT M_NAME, MEMBER_ID, M_EMAIL, M_RECEIVE_EMAIL, M_DOB, M_SENT_DOBMAIL FROM " & strMemberTablePrefix & "MEMBERS WHERE MID(M_DOB,5)='" & MID(DATETOSTR(STRFORUMTIMEADJUST),5,4) & "' ORDER BY M_DOB"

to

strSql = "SELECT M_NAME, MEMBER_ID, M_EMAIL, M_RECEIVE_EMAIL, M_DOB, M_SENT_DOBMAIL FROM " & strMemberTablePrefix & "MEMBERS WHERE substring(M_DOB, 5, 4) = '" & MID(DATETOSTR(STRFORUMTIMEADJUST),5,4) & "' ORDER BY M_DOB"

Thanks
Go to Top of Page

Carefree
Advanced Member

Philippines
4206 Posts
Posted - 26 August 2010 :  22:57:56  Show Profile  Reply with Quote
Got it working, that's all that matters.
Go to Top of Page

Carefree
Advanced Member

Philippines
4206 Posts
Posted - 28 August 2010 :  23:22:10  Show Profile  Reply with Quote
To cover all supported DB types, change the function thus:

Function BirthMail
	strSql = "SELECT M_NAME, MEMBER_ID, M_EMAIL, M_RECEIVE_EMAIL, M_DOB, M_SENT_DOBMAIL FROM " & strMemberTablePrefix & "MEMBERS WHERE "
	if strDBType="sqlserver" or strDBType="mysql" then
		strSql = strSql & "SUBSTRING(M_DOB,5,4) ='" & MID(DATETOSTR(STRFORUMTIMEADJUST),5,4) & "' ORDER BY M_DOB"
	else
		strSql = strSql & "MID(M_DOB,5) ='" & MID(DATETOSTR(STRFORUMTIMEADJUST),5,4) & "' ORDER BY M_DOB"
	end if
	set rsBirthMail = my_Conn.Execute(strSql)
	if not rsBirthMail.EOF then
		rsBirthMail.MoveFirst
		Do while not rsBirthMail.EOF
			strSent=rsBirthMail("M_SENT_DOBMAIL")
			strYY=left(DatetoStr(strForumTimeAdjust),4)
			If isNull(strSent) or (strSent <> strYY) then
				strFromName = strForumTitle
				strRecipientsName = rsBirthMail("M_NAME")
				strRecipients = rsBirthMail("M_EMAIL")
				strSender = strSender
				strSubject = "Happy Birthday!"
				strMessage = "Dear " & rsBirthMail("M_NAME")
				strMessage = strMessage & "<br><br>Happy birthday from everyone at <a href=" & strForumURL & ">" & strForumTitle & "</a>." & vbNewline & vbNewline
				htmlflag = 0
				if rsBirthMail("M_RECEIVE_EMAIL") = 1 then
					%><!--#INCLUDE FILE="inc_mail_html.asp" --><%
				end if
				strMessage =""
				strSql = " UPDATE " & strMemberTablePrefix & "MEMBERS SET M_SENT_DOBMAIL = '" & strYY & "' WHERE MEMBER_ID = " & rsBirthMail("MEMBER_ID")
				my_Conn.Execute(strSql)
			end if
			rsBirthMail.MoveNext
		Loop
		rsBirthMail.Close
	end if
	set rsBirthMail = Nothing
End Function
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000 Version 3.4.07