Dim strConn,Conn,RecObj,strSQL,strSubject,strStatus,strBody,objMail,strDate
Dim intCount,blnBCC,strTeamA,strTeamB : intCount = 0
Dim strText
Const Appending=8

'***EMAIL CONSTANT
Const cdoContentDisposition = "urn:schemas:mailheader:content-disposition"
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSendUsingPort   = 2
Const cdoSMTPServer      = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPServerPort  = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cdoSMTPConnectionTimeout  = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cdoSMTPAuthenticate =	"http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cdoBasic = 1
Const cdoSendUserName = "http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cdoSendPassword = "http://schemas.microsoft.com/cdo/configuration/sendpassword"		
Const EMAIL_FORM = "donotreply@sportsmgr.us"


strConn = "Provider=SQLOLEDB;Data Source=WIN-59JDF64IOMC;Initial Catalog=cloudspo_sportsmgr;User Id=cloudspo_sports;password=letmein@cliff64"

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

strSQL = "EXECUTE dbo.USP_REMINDER_COPY 8"
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

strSQL = "EXECUTE dbo.USP_SAVE_EMAIL_LOGS 2,0,0"

Set RecObj = CreateObject("adodb.recordset")
With RecObj
.Open strSQL,Conn
If .EOF = False Then
	intLog = .Fields(0)
End If 
.Close()
End With
Set RecObj = Nothing

'strText = "A text message notice was also sent to 508-423-2444"
'Call SendMessage("15084232444",strText)


strSQL = "EXECUTE dbo.USP_GAME_CHANGE_NOTICE"
Set dicGames=CreateObject("Scripting.Dictionary")
Set dicGameID=CreateObject("Scripting.Dictionary")

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




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 = "Schedule Change: "&.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 & "<p>Please review this change that has taken place within the last hour. Below is the current status.Below that is the change history for this game.</p>"
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("GameTypeCancel") & "</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>Ref Info:</td><td class=""tdbold"">" & strRefInfo & "</td></tr></table>" 

strBody = strBody & GameChangeHistory(.Fields("GameID"))

strBody = strBody & "<hr/><p>This email was sent to all involved 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>" 

If .Fields("PhCell") <> "" Then
strText = "<br/>A text message notice was also sent to " & .Fields("PhCell")
strEmailBody = strBody & strText 
Else
strText = ""
strEmailBody = strBody & "<br/>No text message was sent"
End If 

Set objConfig = CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
With Fields
	.Item(cdoSendUsingMethod)       = cdoSendUsingPort
	.Item(cdoSMTPServer)            = "mail.sportsmgr.us"
	.Item(cdoSMTPServerPort)        = 25
	.Item(cdoSMTPAuthenticate)      = cdoBasic
	.Item(cdoSendUserName)          = "donotreply@sportsmgr.us"
	.Item(cdoSendPassword)         = "Shadow@1958"
	.Item(cdoSMTPConnectionTimeout) = 10
	.Update
End With
Set objMail=CreateObject("CDO.Message")
Set objMail.Configuration = objConfig


objMail.Subject=strSubject
objMail.From = "notices@sportsmanager.us"
If .Fields("Email") <> "" Then
	objMail.To = .Fields("Email")
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 



If blnBCC = "T" Then
	'objMail.BCC = "bhartiya.ravi@gmail.com"
End If 
'objMail.BCC = "bhartiya.ravi@gmail.com"
objMail.HTMLBody = strEmailBody
objMail.Send
Set objMail=Nothing
Set objConfig = Nothing


If strText <> "" Then
Call SendMessage(.Fields("PhCell"),strSubject)
End If 


If dicGameID.Exists(strGameID) = False Then
dicGameID.Add strGameID,strGameID
If strGames  = "" Then
strGames = .Fields("GameID")
Else
strGames = strGames & "," & .Fields("GameID")  
End If
End If

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

strSQL = "EXECUTE dbo.USP_SAVE_EMAIL_LOGS 2," & intCount & "," & intLog & ",'" & strGames & "'"
Conn.Execute(strSQL)

Conn.Close() : Set Conn = Nothing

Function GameChangeHistory(ByVal intGame)
Dim arrGameInfo(1),RecObjHistory
strSQL = "EXECUTE dbo.USP_OM_GAME_HISROTY_EMAIL " & intGame
Set RecObjHistory = CreateObject("adodb.recordset")
RecObjHistory.Open strSQL,Conn



With RecObjHistory
If .EOF = False Then
strGameinfo = "<h4 style=""text-align:center"">Game Change history for "& .Fields("League") &", " & .Fields("HomeDivName") & ", #"& .Fields("GameID") & " created on " & .Fields("DateCreated")  & "</h4>"
Do While .EOF = False
arrGameInfo(.Fields("ListOrder")) = arrGameInfo(.Fields("ListOrder")) & "<tr><td>"& .Fields("Date") & "</td><td>"& .Fields("GameTime") & "</td><td>"& .Fields("HomeTeam") & "</td><td>"& .Fields("AwayTeam") & "</td><td>"& .Fields("AreaOrgName") & " - " & .Fields("Area") &"</td><td>"& .Fields("DateEdited") & "</td><td>"& .Fields("GameTypeName") & "</td></tr>"

.MoveNext
Loop
End If 
.Close()
End With


If arrGameInfo(0) <> "" Then
arrGameInfo(0) = strGameinfo & "<h3 class=""h3heading"">Current Game</h3><table class=""tbllist"" style=""font-size:1.0em;"" cellpadding=""5""><thead><tr><th>Date</th><th>Time</th><th>Home Team</th><th>Away Team</th><th>Date Edited</th><th>Game Type</th></tr></thead><tbody><tbody>" & arrGameInfo(0) & "</tbody></table>"
Else
arrGameInfo(0) = "<h3 class=""h3heading"">No Game Info</h3>"
End If 

If arrGameInfo(1) <> "" Then
arrGameInfo(1) = strGameinfo & "<h3 class=""h3heading"">Game Change History</h3><table class=""tbllist"" style=""font-size:1.0em;""><thead><tr><th>Date</th><th>Time</th><th>Home Team</th><th>Away Team</th><th>Playing Area</th><th>Date Edited</th><th>Game Type</th></tr></thead><tbody>" & arrGameInfo(1) & "</tbody></table>"
Else
arrGameInfo(1) = "<h3 class=""h3heading"">No Game Change History</h3>"
End If 

'Response.Write(arrGameInfo(0))
GameChangeHistory  = arrGameInfo(1)

End Function 


Sub SendMessage(ByVal sMobileNo,ByVal sText)
If sMobileNo = "" Or sText = ""  Then
	Exit Sub
End If


sUrl = "https://api.clickatell.com/http/sendmsg"
sAPI_ID = "3175721"
sPassword = "61rivercliff"
sUsername = "SportsManager"
sFrom = "19288572666"
sPostData = "api_id=" & sAPI_ID
sPostData = sPostData & "&user=" & sUsername
sPostData = sPostData & "&password=" & sPassword
sPostData = sPostData & "&to=" & sMobileNo
sPostData = sPostData & "&text=" & sText
sPostData = sPostData & "&mo=1&from=" & sFrom


Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
oXMLHTTP.Open "POST", sUrl, false
oXMLHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
oXMLHTTP.Send sPostData
sResult = oXMLHTTP.responseText
Set oXMLHTTP = Nothing
End Sub