<% '****************************** '****x_nom_email.asp*********** '******Cully Radvillas************ '**********CS460************* '****last updated 5/10/06******** '******************************* 'queries database to see if faculty have been nominated for committees 'checks database to see if faculty have been notifed previously 'if they have not been notified previously then send faculty member email '******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 nom email ON?************ if (r1("NOM_EMAIL").VALUE=true)then '***cleanup r1.Close Set r1 = Nothing c1.Close Set c1 = Nothing '************************************************* '***declare your variables Dim connection1, recordset1 Dim strSQL1, sConnString1 Dim connection2, recordset2 dim strSQL2 dim strSQL3, connection3, recordset3 dim strSQL4, connection4, recordset4 dim msg, sbj, msgend, head '*** hard coded msg header & footer head = " You have been nominated for the following committee, " msgend = vbcrlf& vbcrlf& "Thank you for using the NVS Voting system " msgend = msgend + vbcrlf& "please go to http://vote to view committee details" msgend = msgend + vbcrlf& "*****Please do not reply to this email address*****" msgend = msgend + vbcrlf& "*****Contact NVS administrator with concerns******" '***hard coded subject line sbj = " You have been nominated for a NVS committee" '***declare SQL statement that will query the database '***query returns comittees currently up for nomination***** strSQL1 = "SELECT [COMID], [COMNAME], [ENDNOM] FROM [COMM1]" strSQL1 = strSQL1 + " WHERE [STARTNOM] <= #" & cstr(Date) & "#" strSQL1 = strSQL1 + " AND [ENDNOM] >= #" & cstr(Date) & "#" strSQL1 = strSQL1 + " ORDER BY [ENDNOM]" '***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") set connection3 = server.CreateObject("ADODB.connection") set recordset3 = Server.CreateObject("ADODB.Recordset") set connection4 = server.CreateObject("ADODB.connection") set recordset4 = 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 '***Open the recordset object, executing the SQL recordset1.Open strSQL1, Connection1 '**********traverse committees******************************** Do While (NOT recordset1.EOF) '***sql to find emailed members of this committee strSQL4 = "SELECT Nominated.USER_ID, Nominated.EMAILED, L_Name, F_Name, EMAIL FROM Nominated, Faculty" strSQL4 = strSQL4 + " WHERE Faculty.USER_ID = Nominated.USER_ID AND " strSQL4 = strSQL4 + " Nominated.COMID = " & recordset1("COMID").value StrSQL4 = strSQL4 + " AND Nominated.EMAILED = true ;" '***Open the connection to the database and record set Connection4.Open sConnString1 Recordset4.Open strSQL4, Connection4 'response.write "Committee Name: " & recordset1("COMNAME").value & "
" 'print out committee for testing msg= head + vbcrlf& recordset1("COMNAME").value '*****************SQL to get individual nominations per faculty ********************* '*******it is going through the committees first with strSQL1 and then seeing ******* '**********if individual faculty are nominated the specified committees**************** strSQL2 = "SELECT DISTINCT Nominated.USER_ID, Nominated.EMAILED, L_Name, F_Name, EMAIL FROM Nominated, Faculty" strSQL2 = strSQL2 + " WHERE Faculty.USER_ID = Nominated.USER_ID AND " strSQL2 = strSQL2 + " Nominated.COMID = " & recordset1("COMID").value StrSQL2 = strSQL2 + " AND Nominated.EMAILED = false ;" Connection2.Open sConnString1 Recordset2.Open strSQL2, Connection2 '*************************************************************************************** '*******traverse nomanated with unchecked email********** do while (not recordset2.EOF) '******traverse nomanated with checked email*********** do while (not recordset4.EOF) 'Response.Write recordset4("USER_ID").Value 'for testing 'Response.Write "--------------" 'for testing 'Response.Write recordset2("USER_ID").Value 'for testing '********if the users match for unchecked and checked set checked******** if (recordset4("USER_ID").Value = recordset2("USER_ID").Value) then '********SQL execution to set EMAILED field to true***************** strSQL3 = "UPDATE Nominated SET Nominated.EMAILED = TRUE" strSQL3 = strSQL3 + " WHERE Nominated.USER_ID = " strSQL3 = strSQL3 & recordset2("USER_ID").Value & " AND Nominated.COMID = " & recordset1("COMID").Value & ";" '**open DB connection and execute SQL connection3.Open sConnString1 connection3.Execute(strSQL3) connection3.Close end if recordset4.MoveNext loop recordset2.MoveNext loop '*********close and open db and recordset to include modified values******** recordset2.Close Connection2.Close Connection2.Open sConnString1 Recordset2.Open strSQL2, Connection2 '**********send Email using x_email.asp************** Do While ( NOT recordset2.EOF) towho=recordset2("EMAIL").value fromwho="votingsystem@snc.edu" '***set sender addr subject= sbj '***set subject message = msg + msgend '***make message If toWho <> "" THEN Response.Write towho '***print out email addresses for testing 'sendMail fromWho, toWho, Subject, message '***call function to send mail with x_mail.asp END IF '*****set emailed field to true so will not send another email****** strSQL3 = "UPDATE Nominated SET Nominated.EMAILED = TRUE" strSQL3 = strSQL3 + " WHERE Nominated.USER_ID = " & recordset2("USER_ID").Value strSQL3 = strSQL3 + " AND Nominated.COMID = " & recordset1("COMID").Value & ";" connection3.Open sConnString1 connection3.Execute(strSQL3) connection3.Close recordset2.Movenext Loop recordset2.Close Connection2.Close recordset4.close connection4.Close recordset1.MoveNext Loop '*****clean up recordset1.Close Set recordset1 = Nothing set recordset2 = nothing Set connection1 = Nothing '***Cleanup for x_email Set ObjCDO = Nothing Set iConf = Nothing Set Flds = Nothing Response.Redirect "close.html"'***closes window when running on cron else '*****when nom email is off******** '*****cleanup r1.Close Set r1 = Nothing c1.Close Set c1 = Nothing Response.Redirect "close.html"'***closes window when running on cron end if %>