On Error Resume Next

Dim strConn,strSQL,RecObj,strSubject,strBody,strCc,strBCc,strFrom
Dim strPrimaryEmail,strSecondaryEmail,objMail,intCount
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"


strFrom = "notices@sportsmanager.us"
strBCc  = "copy@sportsmanager.us"
intCount = 0 


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_SAVE_EMAIL_LOGS 1,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



strSQL = "EXECUTE dbo.USP_RESCHEDULE_GAMES"

Set RecObj = CreateObject("adodb.recordset")
With RecObj
.Open strSQL,Conn
Do While .EOF = False
intCount = intCount + 1
strSubject= "Home Coach Game Rescheduling for cancelled Game # "&.Fields("GameID")&" on "&.Fields("Date")&"/"&.Fields("GameTime")&"/"&.Fields("Area")
strGame = "Rescheduling Cancelled Game: <strong>#" & .Fields("GameID") & " " & .Fields("Season") &"-" & .Fields("League") & "</strong> "&.Fields("HomeOrgName")&"-<strong>"& .Fields("HomeTeam") & "</strong> vs "&.Fields("AwayOrgName")&"-<strong>" & .Fields("AwayTeam") & " on "&.Fields("Date")& " " &  .Fields("GameTime") & " "& .Fields("Duration") &" Min @ " &  .Fields("AreaOrgName") & " "&.Fields("Area")&"</strong>"
strBody = strGame & "<br/><a href=""https://www.sportsmanager.us/Rescheduling.asp?HomeCoach=T&O="&.Fields("HomeOrg")&"&Game="&.Fields("GameID")&"&OMOrg="&.Fields("OMOrgID")&""" target=""_blank"" style=""color:blue;font-size:1.5em"">Click here to start the Rescheduling process for this cancelled game</a>"
arrEmails = Split(.Fields("Email"), ":")
strPrimaryEmail = arrEmails(0)
strSecondaryEmail = arrEmails(1)
If strPrimaryEmail = "" Then 
strPrimaryEmail = "copy@sportsmanager.us" 
End If

If strSecondaryEmail = "" Then
strSecondaryEmail = "copy@sportsmanager.us" 
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.Configuration = objConfig



Set objMail=CreateObject("CDO.Message")
With objMail
.Subject=strSubject
.From=strFrom
.To=strPrimaryEmail
.Cc = strSecondaryEmail
.BCC = strBCc
.HTMLBody = strBody 
.Send
End With
Set objMail=Nothing

If strGames  = "" Then
strGames = .Fields("GameID")
Else
strGames = strGames & "," & .Fields("GameID")  
End If

strMobile = arrEmails(2)
If strMobile <> "" Then
sText = .Fields("OMNameAbbr") & " Rescheduling needs your help.We just sent you a rescheduling email for game #"&intGame&" at "&strTo&". Please check your email and respond within 24 hours. It is very important that you respond quickly due to field shortages and expiration dates. Thank you"
Call SendMessage(strMobile,sText)
End If 

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


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

Conn.Close() : Set Conn = Nothing



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