Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringSending mail upon birthday

Author : balexandre
Subject : Sending mail upon birthday
Posted : 19 May 2010, 17:52
Message :

Did anyone made this to complement the Birthdays Module?

Reply Author: modifichicci
Replied on: 20 May 2010, 12:22
Message:

yes i did

Reply Author: balexandre
Replied on: 21 May 2010, 13:37
Message:

care to share?

Reply Author: modifichicci
Replied on: 21 May 2010, 17:05
Message:

you need to add a field called M_SENTEMAIL to the table MEMBERS_BDATES
then add this function to cal_functions.asp

Code:


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

Code:


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

Reply Author: balexandre
Replied on: 07 August 2010, 10:26
Message:

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

Code:

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!

Reply Author: modifichicci
Replied on: 09 August 2010, 15:06
Message:

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!|

Reply Author: Carefree
Replied on: 21 August 2010, 20:04
Message:

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.

Code:


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".

Code:


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

Reply Author: Sean Sounds
Replied on: 23 August 2010, 15:34
Message:

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

Reply Author: Carefree
Replied on: 24 August 2010, 20:09
Message:

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

Reply Author: Sean Sounds
Replied on: 25 August 2010, 15:43
Message:

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

Reply Author: Carefree
Replied on: 26 August 2010, 22:57
Message:

Got it working, that's all that matters.

Reply Author: Carefree
Replied on: 28 August 2010, 23:22
Message:

To cover all supported DB types, change the function thus:

Code:


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

Sending mail upon birthday

Topic

Replies ...