Dim strConn,Conn,RecObj,strTeamId,strTableHeader,intCount
Dim dicTeams,dicTACount,dicTeamNames,strRows,strTableFoot
Dim objMail,strSubject
Set dicTeams=CreateObject("Scripting.Dictionary")
Set dicTACount=CreateObject("Scripting.Dictionary")
Set dicTeamNames=CreateObject("Scripting.Dictionary")
Set dicTeamIDs=CreateObject("Scripting.Dictionary")

strConn = "Provider=SQLOLEDB;Data Source=WIN-59JDF64IOMC;Initial Catalog=cloudspo_sportsmgr;User Id=cloudspo_sports;password=letmein@cliff64"
strSQL = "EXECUTE dbo.USP_ROSTER_REMOVAL_NOTICE"
Set Conn = CreateObject("adodb.Connection")
With Conn
.ConnectionString  = strConn 
.Open 
End With
Set RecObj = CreateObject("adodb.recordset")
With RecObj
.Open strSQL,Conn
If .EOF = False Then
strTableHeader = "<table cellpadding=""3"" style=""border:solid 1px #ccc;background-color:#ccc;""><thead><tr><td>First</td><td>Last</td><td align=""center"">Team</td><td align=""center"">Org</td><td align=""center"">Deleted On</td><td>TA ID</td></tr></thead><tbody>"
Do While .EOF = False 
strTeamId = CStr(.Fields("TeamID"))

strSQL = "EXECUTE dbo.USP_REMOVE_ROSTER_APP " & .Fields("PlayerID") & "," & strTeamId
Conn.Execute(strSQL)


strRows = "<tr style=""background-color:#fff""><td>" & .Fields("First") & "</td><td>" & .Fields("Last") & "</td><td>" & .Fields("Season") & " - " & .Fields("League") & " - " &.Fields("TeamName") & "</td><td>" & .Fields("OMOrg") & " - " & .Fields("SMGR") &  "</td><td>"&.Fields("DateDeleted")&"</td><td align=""center"">"&.Fields("TeamassignmentId")&"</td></tr>" & VBCrLf
If dicTeams.Exists(strTeamId) = False Then
dicTeams.Add strTeamId,strRows
strSubject = CStr(.Fields("Season") & " - " & .Fields("SMGRAbbr") & " - " & .Fields("League") & " - " & .Fields("TeamName")) 
dicTeamNames.Add strTeamId,strSubject
dicTACount.Add strTeamId,"1"
dicTeamIDs.Add strTeamId,strTeamId
Else
strRows = dicTeams.Item(strTeamId) & strRows
dicTeams.Item(strTeamId) = strRows
intCount = Int(dicTACount.Item(strTeamId))
dicTACount.Item(strTeamId)=CStr(intCount+1)
End If 
.MoveNext
Loop
strTableFoot = "</tbody></table>"
End If 
.Close()
End With
Set RecObj = Nothing : 

arrTeams=dicTeams.Items
arrTACount = dicTACount.Items
arrTeamsName = dicTeamNames.Items
arrTeamIDs = dicTeamIDs.Items


For I=LBound(arrTeamsName) To UBound(arrTeamsName)
If Int(arrTACount(I)) = 1 Then 
strSubject = arrTACount(I) & " Player has been removed from the " & arrTeamsName(I) & " team"
Else
strSubject = arrTACount(I) & " Players has been removed from the " & arrTeamsName(I) & " team"
End If 
strBody = strTableHeader & arrTeams(I) & strTableFoot
arr = Receivers(arrTeamIDs(I))
Set objMail=CreateObject("CDO.Message")
With objMail
.Subject=strSubject
.From="SportsManager<donotreply@sportsmanager.us>"
.To=arr(0)
'.To="bhartiya.ravi@hotmail.com;sharty@sportsmanager.us"
.BCC = "Copy<copy@sportsmanager.us>"
.Cc=arr(1)
.HTMLBody = strBody 
.Send
End With
Set objMail=Nothing
Next

Function Receivers(TeamID)
Dim strEmailSQL,RecEmailObj,arrEmails(1)
Set RecEmailObj = CreateObject("adodb.recordset")
strEmailSQL= "EXECUTE dbo.USP_ROSTER_REMOVAL_NOTICE 1,"&TeamID
With RecEmailObj
.Open strEmailSQL,Conn
If .EOF = False Then
Do While .EOF = False 
If .Fields(1) = 0 Then
	strTo = .Fields(0) & ";" & strTo
Else
	strCc = .Fields(0) & ";" & strCc
End If 
.MoveNext
Loop
If strCc <> "" Then
strCc = Left(strCc,Len(strCc)-1)
Else
strCc = "copy<copy@sportsmanager.us>"
End If

If strTo <> "" Then
strTo = Left(strTo,Len(strTo)-1)
Else
strTo = "copy<copy@sportsmanager.us>"
End If

Else
strTo = "copy<copy@sportsmanager.us>"
strCc = "copy<copy@sportsmanager.us>"
End If 
.Close()
End With
Set RecEmailObj = Nothing
arrEmails(0) = strTo : arrEmails(1) = strCc
Receivers = arrEmails
End Function

Conn.Close() : Set Conn = Nothing