
<!--#include file="x_email.asp"-->
<%
'******************************
'****x_mass_email.asp*********
'******Cully Radvillas************
'**********CS460**************
'****last updated 4/27/06********
'*******************************
'******declare variables**********
dim c1,r1,scstr1
dim sql
Set c1 = Server.CreateObject("ADODB.connection") 
Set r1 = Server.CreateObject("ADODB.Recordset")
scstr1="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("../database/dbNVS.mdb")
sql = "SELECT * FROM ADMIN_SETTINGS;"
c1.Open scstr1
r1.Open sql, c1
'*****check database is mass email ON?************
if (r1("MASS_EMAIL").VALUE=true)then
	'cleanup
	r1.Close
	Set r1 = Nothing
	c1.Close
	Set c1 = Nothing
'*************************************************************************************************************	
		'declare your variables
		Dim connection1, recordset1
		Dim sConnString1
		Dim connection2, recordset2
		DIM strSQL1,strSQL2, strSQLSN, strSQLEN, strSQLSV, strSQLEV
		dim fs, f
		dim msgend
		dim snom, enom, svot, evot, admin
		dim Nomslst, Nomelst, Votslst, Votelst 
		DIM connectionSN, connectionEN, connectionSV, connectionEV
		DIM RecordsetSN, RecordsetEN, RecordsetSV, RecordsetEV		
		
		set fs=Server.CreateObject("Scripting.FileSystemObject")
		set f=fs.OpenTextFile("d:\vote\nvs\test.txt",2,true)	
		'*****generic message to append to the end of the message
		msgend =  vbcrlf& vbcrlf& "Thank you for using the NVS Voting system "
		msgend = msgend + vbcrlf& "please go to bennetpc.net/vote to view committee details"
		fromWho = "bennett@bennettpc.net"
				
		'**********create an ADO connection and recordset object
		Set connection1 = Server.CreateObject("ADODB.connection") 
		Set recordset1 = Server.CreateObject("ADODB.Recordset")
		set connection2 = server.CreateObject("ADODB.connection")
		Set recordset2 = Server.CreateObject("ADODB.Recordset")
		
		'*********define the connection string, specify database 
		'*************driver and the location of database 
		sConnString1="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("../database/dbNVS.mdb") 
		
		'**********Open the connection to the database
		Connection1.Open sConnString1
		Connection2.Open sConnString1
		
		'*******declare SQL statement that will query the database
		'get dates from comm1 table
		sSQL1="SELECT COMID, COMNAME, STARTNOM, ENDNOM, STARTVOTE, ENDVOTE FROM COMM1;"
		'get email for faculty table
		sSQL2="SELECT EMAIL FROM FACULTY;"
		
		'*******Open the recordset object, executing the SQL
		Recordset1.Open sSQL1, Connection1
		Recordset2.Open sSQL2, Connection2
		'**********************************************************************************
		'*******traverse committees********************************************************
		Do While (NOT recordset1.EOF)
			'*********if nomination starts today***********
			if (Recordset1("STARTNOM").Value = DATE) and (snom<1) then
				snom = snom +1
				'f.writeLine("**********start NOM********* ") 
				set connectionSN = server.CreateObject("ADODB.connection")
				Set recordsetSN = Server.CreateObject("ADODB.Recordset")
				ConnectionSN.Open sConnString1
				
				strSQLSN = "SELECT [COMNAME] FROM [COMM1]"
				strSQLSN = strSQLSN + " WHERE [STARTNOM] = #" & cstr(Date) & "#"
				strSQLSN = strSQLSN + " ORDER BY [STARTNOM]"
				RecordsetSN.Open strSQLSN, ConnectionSN
				Nomslst =  "*****The following committees are up for nomination "
				Nomslst = Nomslst + " starting today****** "  
				'*************make list of committees up for nom********
				DO WHILE NOT RecordsetSN.EOF
					Nomslst = Nomslst  + vbcrlf& recordsetSN("COMNAME").value   
					RecordsetSN.MoveNext
				loop 
				'*******************************************************
				recordsetSN.Close
				Set recordsetSN = Nothing
				connectionSN.Close
				Set connectionSN = Nothing	
				'response.write Nomslst'****display committees starting nom for testing
				'subject= "A new nomination period has began for NVS"
				'message = Nomslst + msgend
				
			end if			
			'*********if nomination ends today***********
			if (Recordset1("ENDNOM").Value = DATE)and (enom<1) then
				enom = enom +1
				f.writeLine("************end NOM**************")
				
				set connectionEN = server.CreateObject("ADODB.connection")
				Set recordsetEN = Server.CreateObject("ADODB.Recordset")
				ConnectionEN.Open sConnString1
				strSQLEN = "SELECT [COMNAME] FROM [COMM1]"
				strSQLEN = strSQLEN + " WHERE [ENDNOM] = #" & cstr(Date) & "#"
				strSQLEN = strSQLEN + " ORDER BY [ENDNOM]"
				RecordsetEN.Open strSQLEN, ConnectionEN
				Nomelst =  "******Today, is the last day to nominate "
				Nomelst = Nomelst + "faculty for the following committees****** "
				'*************make list of committees ending nom********
				DO WHILE NOT RecordsetEN.EOF
					Nomelst = Nomelst + vbcrlf& recordsetEN("COMNAME").value  
					RecordsetEN.MoveNext
				loop 
				'*******************************************************
				recordsetEN.Close
				Set recordsetEN = Nothing
				connectionEN.Close
				Set connectionEN = Nothing	
				'response.write Nomelst
				'subject= "A NVS nomonation period is ending"
				'message = Nomelst + msgend
				
			end if		
			'*********if voting starts today***********
			if (Recordset1("STARTVOTE").Value = DATE)and (svot<1) then
				svot = svot +1
				f.writeLine("************start VOTE**************")
				
				set connectionSV = server.CreateObject("ADODB.connection")
				Set recordsetSV = Server.CreateObject("ADODB.Recordset")
				ConnectionSV.Open sConnString1
				strSQLSV = "SELECT [COMNAME] FROM [COMM1]"
				strSQLSV = strSQLSV + " WHERE [STARTVOTE] = #" & cstr(Date) & "#"
				strSQLSV = strSQLSV + " ORDER BY [STARTVOTE]"
				RecordsetSV.Open strSQLSV, ConnectionSV
				Votslst =  "******The following committees are up for voting"
				Votslst = Votslst + " starting today****** "
				'*************make list of committees up for voting********
				DO WHILE NOT RecordsetSV.EOF
					Votslst = Votslst + vbcrlf& recordsetSV("COMNAME").value 
					RecordsetSV.MoveNext
				loop 
				'**********************************************************
				recordsetSV.Close
				Set recordsetSV = Nothing
				connectionSV.Close
				Set connectionSV = Nothing	
				'response.write Votslst
				'subject= "A new voting period has began for NVS"
				'message = Votslst + msgend
				
			end if	
			'*********if voting ends today***********
			if (Recordset1("ENDVOTE").Value = DATE) and (evot<1) then
				evot = evot +1
				f.writeLine("************end VOTE**************")
				
				set connectionEV = server.CreateObject("ADODB.connection")
				Set recordsetEV = Server.CreateObject("ADODB.Recordset")
				ConnectionEV.Open sConnString1
				strSQLEV = "SELECT [COMNAME] FROM [COMM1]"
				strSQLEV = strSQLEV + " WHERE [ENDVOTE] = #" & cstr(Date) & "#"
				strSQLEV = strSQLEV + " ORDER BY [ENDVOTE]"
				RecordsetEV.Open strSQLEV, ConnectionEV
				Votelst = "******Today, is the last day to vote for"
				Votelst = Votelst + "faculty for the following committees****** "
				'*************make list of committees ending voting********
				DO WHILE NOT RecordsetEV.EOF
					Votelst = Votelst + vbcrlf& recordsetEV("COMNAME").value 
					RecordsetEV.MoveNext
				loop 
				'**********************************************************
				recordsetEV.Close
				Set recordsetEV = Nothing
				connectionEV.Close
				Set connectionEV = Nothing	
				
			end if			
			recordset1.MoveNext
		Loop
		subject = "NVS committee update"
		message = "Today's date is " & cstr(Date) +vbcrlf& Nomslst + vbcrlf& vbcrlf& Nomelst + vbcrlf& vbcrlf& Votslst + vbcrlf& vbcrlf& Votelst + vbcrlf& msgend
		'*******comment in final implimentation*******
		'*******send email for testing*********
			towho="vote@bennettpc.net"
			fromwho="admin@bennettpc.net"					
			If toWho <> "" THEN 
				sendMail fromWho, toWho, Subject, message 
			END IF
		'*****end of testing email************
		'*************************************
		'*****send email to all faculty*******
		'*****For Final Implimentation********
		'do while not recordset2.EOF
			'f.WriteLine(recordset2("EMAIL").value)'<<<---writing to file for testing
			'towho=recordset2("EMAIL").value'<<<--- UNCOMMENT send email to all faculty
			'fromwho="admin@bennettpc.net"'<<<--- UNCOMMENT sender email address	
			'If toWho <> "" THEN 
			'	sendMail fromWho, toWho, Subject, message 
			'END IF
			'recordset2.MoveNext
		'loop
		'*****end of all faculty email********
		recordset1.Close
		recordset2.Close
		Set recordset1 = Nothing
		set recordset2 = nothing
		connection1.Close
		Set connection1 = Nothing
		f.close
		set f = nothing
		set fs = nothing
		'Cleanup 
		Set ObjCDO = Nothing 
		Set iConf = Nothing 
		Set Flds = Nothing
else
'*****when mass email is off********
'*****cleanup & message**********
r1.Close
Set r1 = Nothing
c1.Close
Set c1 = Nothing
Response.write "<SCRIPT>javascript: alert('MASS EMAIL SYSTEM IS OFF');</SCRIPT>"
'response.redirect("./admin/email.asp")	
end if
		%>