On Error Resume Next
Dim strConn,Conn,RecObj,strSQL,strSubject,strStatus,strBody,objMail,strDate
Dim intCount,blnBCC,strTeamA,strTeamB,dicGames,strGameID : intCount = 0
Dim strRefInfo,strGameInfo
Const Appending=8
strConn = "Provider=SQLOLEDB;Data Source=SQL-64;Initial Catalog=SheddPark;User Id=sports;password=letmein@cliff64;"

Set Conn = CreateObject("adodb.Connection")
With Conn
.ConnectionString  = strConn 
.Open 
End With

strSQL = "EXECUTE dbo.USP_REMINDER_COPY 7"
Set RecObj = CreateObject("adodb.recordset")
With RecObj
.Open strSQL,Conn
If .EOF = False Then
	blnBCC = .Fields(0)
Else
	blnBCC = "F"
End If
.Close()
End With
Set RecObj = Nothing


Set dicGames=CreateObject("Scripting.Dictionary")


strSQL = "EXECUTE dbo.USP_GAME_REMINDERS '" & Now() & "'"

Set RecObj = CreateObject("adodb.recordset")
With RecObj
.Open strSQL,Conn
Do While .EOF = False
intCount= intCount + 1
strGameID = CStr(.Fields("GameID"))
If 1=2 And dicGames.Exists(strGameID) = False Then 
strRefInfo = ""
strSQL = "EXECUTE dbo.USP_GAME_REMAINDER_REF_INFO " & strGameID 
Set RecGame = CreateObject("ADODB.Recordset")
RecGame.Open strSQL,Conn

If RecGame.EOF = False Then
Do While RecGame.EOF = False
strRefInfo = strRefInfo & RecGame.Fields("Position") & "-" & RecGame.Fields("Name") & " " & RecGame.Fields("Email") 
If RecGame.Fields("Email2") <> "" Then
	strRefInfo = strRefInfo  & "  " & RecGame.Fields("Email2")
End If

If RecGame.Fields("phHome") <> "" Then
	strRefInfo = strRefInfo & " (H)" & RecGame.Fields("phHome")
End If

If RecGame.Fields("phCell") <> "" Then
	strRefInfo = strRefInfo & " (C)" & RecGame.Fields("phCell")
End If

If RecGame.Fields("phBusiness") <> "" Then
	strRefInfo = strRefInfo & " (W)" & RecGame.Fields("phBusiness")
End If
strRefInfo = strRefInfo & "<br/>"
RecGame.MoveNext
Loop
dicGames.Add strGameID,strRefInfo
End If
End If

'strRefInfo = dicGames.Item(strGameID)


strDate = .Fields("GameDate") & "-" & WeekDayName(Weekday(.Fields("GameDate")) ,True)
strTeamA = .Fields("HomeTeam")
strTeamB = .Fields("AwayTeam")
If .Fields("EmailType") = 0 Or .Fields("EmailType") = 3 Then '***COACH OR REFEREE
	strTeamA = strTeamA & " " & .Fields("HomeCoachName")
	strTeamB = strTeamB & " " & .Fields("AwayCoachName")
ElseIf .Fields("EmailType") = 1 Then '**HOME TEAM PLAYER
	strTeamA = strTeamA & " " & .Fields("HomeCoachName")
ElseIf .Fields("EmailType") = 2 Then '**Away TEAM PLAYER
	strTeamB = strTeamB & " " & .Fields("AwayCoachName")
End If 

strSubject = "Reminder: "&.Fields("Name")&" has a "& .Fields("TypeName") & " (#"&.Fields("GameID")&") on " & strDate & " " &.Fields("GameTime") & " at " & .Fields("AreaAbbr") & "."
strBody = "<style type=""text/css"">body{font-family:Arial, Verdana, Helvetica, sans-serif;}" & VBCrlf & "td.tdbold{font-weight:bold;}</style>"
strBody = strBody & "<table cellpadding=""3"" style=""font-size:1.0em;"">"
strBody = strBody & "<tr><td>For:</td><td class=""tdbold"">" & .Fields("Name") & "</td></tr>" 
strBody = strBody & "<tr><td>ID #:</td><td class=""tdbold"">" & .Fields("GameID") & "</td></tr>" 
strBody = strBody & "<tr><td>Date:</td><td class=""tdbold"">" & strDate & "</td></tr>" 
strBody = strBody & "<tr><td>Time:</td><td class=""tdbold"">" & .Fields("GameTime") & "</td></tr>" 
strBody = strBody & "<tr><td>Duration:</td><td class=""tdbold"">" & .Fields("Duration") & "</td></tr>" 
strBody = strBody & "<tr><td>Season:</td><td class=""tdbold"">" & .Fields("SeasonLeague") & "</td></tr>" 
strBody = strBody & "<tr><td nowrap=""nowrap"">Home Team:</td><td class=""tdbold"">" & strTeamA & "</td></tr>" 
strBody = strBody & "<tr><td nowrap=""nowrap"">Away Team:</td><td class=""tdbold"">" & strTeamB & "</td></tr>" 
strBody = strBody & "<tr><td>Playing at:</td><td class=""tdbold"">" & .Fields("Area") & "</td></tr>" 
strBody = strBody & "<tr><td nowrap=""nowrap"">Last Changed:</td><td class=""tdbold"">" & .Fields("DateChanged") & "</td></tr>" 
strBody = strBody & "<tr><td>Type:</td><td class=""tdbold"">" & .Fields("TypeName") & "</td></tr>" 
strBody = strBody & "<tr><td>Comments:</td><td class=""tdbold"">" & .Fields("Comments") & "</td></tr>" 
strBody = strBody & "<tr><td>Directions:</td><td class=""tdbold"">" & .Fields("AreaDirection") & "</td></tr>"
strBody = strBody & "<tr><td valign=""top"">Ref Info:</td><td class=""tdbold"" valign=""top"">" & strRefInfo & "</td></tr></table>"
strBody = strBody & "<hr/><p>This email was sent to all involved players, coaches and referees by an automated process by SportsManager Solutions, Inc. Please contact your organization’s administrator for schedule questions."
strBody = strBody & "For technical problems with this email contact <a href=""mailto:support@sportsmanager.us"">support@sportsmanager.us</a></p>" 
Set objMail=CreateObject("CDO.Message")
objMail.Subject=strSubject
objMail.From = "notices@sportsmanager.us"
If .Fields("Email") <> "" And IsNull(.Fields("Email")) = False Then
	objMail.To = .Fields("Email")
	'objMail.To = "bhartiya.ravi@hotmail.com"
Else
	objMail.To = "copy@sportsmanager.us"
End If



If .Fields("EmailType") =  1 And .Fields("HomeCoachEmail") <> "" Then
objMail.ReplyTo = .Fields("HomeCoachEmail")
ElseIf .Fields("EmailType") =  2 And .Fields("AwayCoachEmail") <> "" Then
objMail.ReplyTo = .Fields("AwayCoachEmail")
ElseIf .Fields("EmailType") = 3  Then
	If .Fields("HomeCoachEmail") <> "" And .Fields("AwayCoachEmail") <> "" Then
		objMail.ReplyTo = .Fields("AwayCoachEmail") & ";" & .Fields("AwayCoachEmail")
	ElseIf .Fields("HomeCoachEmail") <> "" Then
		objMail.ReplyTo = .Fields("HomeCoachEmail")
	ElseIf .Fields("AwayCoachEmail") <> "" Then
		objMail.ReplyTo = .Fields("AwayCoachEmail")
	End If 
Else
	objMail.ReplyTo = "notices@sportsmanager.us"
End If 


'objMail.To = "ravi_mca2k4@rediffmail.com"
If blnBCC = "T" Then
	objMail.BCC = "copy<copy@sportsmanager.us>"
End If 
objMail.HTMLBody = strBody
objMail.Send
Set objMail=Nothing


.MoveNext
Loop
End With
RecObj.Close() : Set RecObj = Nothing : Conn.Close() : Set Conn = Nothing

Set fs=CreateObject("Scripting.FileSystemObject")
Set f=fs.OpenTextFile("C:\ScheduleTask\reminderLog.txt", Appending) 
f.Write VBCRLF&intCount&" Emails Sent Sucessfully At "&Now()&VBCRLF&"------------------"
f.close()
Set fs=Nothing