<% on error resume next %> <% '****************************************************** ' Code Description: This ASP page interacts with the ' WIND server for both authorization and ' validation. The validation is handled ' by the third-party http component called ' AspTear 1.50 and is available for $35.00 ' here -- http://www.alphasierrapapa.com/ComponentCenter/AspTear/ ' ' In general, the code below is set up to receive the UNI from ' the WIND validation server, and then to perform various ' database look-ups (and entries) based upon the UNI. ' ' In this example we are attempting to verify that the UNI ' belongs to a Teachers College full-time faculty member, or ' that the UNI belongs to someone else who has been granted ' special permission to use the document delivery service. ' ' Code Creator: Michael Rennick (mr366@columbia.edu) ' Date Created: 10/18/2002 ' ' permission is granted by Michael Rennick to use any portion of this code -- no restrictions apply ' ' refer any questions to the e-mail above ' ' ** NOTE: some of the code, like database connections, have been removed '*********************************************************** sub ProcessYesResponse() 'extract the UNI UNI = trim(mid(sResponse, 5)) UNI = trim(left(UNI, (len(UNI) - 1))) 'check to see if they are valid in the central TC database call funct_Connection1(conn) sql = "select top 1 Client.PIDM, Client.Prefix, Client.FirstName, Client.MiddleName, Client.LastName, Email.EmailAddress from Email join Client on (Email.PIDM = Client.PIDM) join Client_Department on (Client.PIDM = Client_Department.PIDM) where Client.UNI = '" & UNI & "' and Client_Department.RoleID = 'RFAC' and Email.Preferred = 1" Set RS = Server.CreateObject ("Adodb.Recordset") RS.Open sql, conn, adOpenStatic, adLockReadOnly if RS.RecordCount > 0 then 'we have a link between the passed UNI from WIND and a person in the central TC Directory DB PIDM = RS("PIDM") FormalTitle = RS("Prefix") FirstName = RS("FirstName") MiddleName = RS("MiddleName") LastName = RS("LastName") Email = RS("EmailAddress") RS.close set RS = nothing conn.close set conn = nothing 'check to see if we have created an account for this person in docdel database call funct_Connection(conn) sql = "select top 1 Client.* from Client where Client.PIDM = '" & PIDM & "'" Set RS = Server.CreateObject ("Adodb.Recordset") RS.Open sql, conn, adOpenStatic, adLockReadOnly if RS.RecordCount > 0 then 'we have an account for this person -- update their stats; give them a cookie and send them on thier way 'update client stats set cmd = Server.CreateObject("ADODB.command") cmd.ActiveConnection = conn cmd.CommandText = "sp_UpdateClientStats" cmd.CommandType = AdCmdStoredProc 'Here we manually create the parameters and append them to the parameters collection cmd.parameters.Append cmd.CreateParameter("ClientID", adInteger, adParamInput) 'Here we add values to the newly created input parameters cmd.Parameters("ClientID").Value = RS("ClientID") 'Execute the stored procedure cmd.execute set cmd = nothing 'set the cookie with response .cookies("DocDel")("SignedIn") = true .cookies("DocDel")("ClientID") = RS("ClientID") .cookies("DocDel")("FormalTitle") = RS("FormalTitle") .cookies("DocDel")("FirstName") = RS("FirstName") .cookies("DocDel")("MiddleName") = RS("MiddleName") .cookies("DocDel")("LastName") = RS("LastName") .cookies("DocDel")("Email") = RS("Email") 'this is encrypted .cookies("DocDel")("staff") = RS("Staff") end with RS.close set RS = nothing conn.close set conn = nothing response.redirect("default.asp") else 'no account yet, so create one RS.close set RS = nothing sql = "" sql = sql & "insert into Client (Client.PIDM, Client.FormalTitle, Client.FirstName, Client.MiddleName, Client.LastName, Client.Email, Client.DateEntered, Client.DateUpdated, Client.TotalLogins, Client.LastVisit) values (" & PIDM & ", '" & FormalTitle & "', '" & FirstName & "', '" & MiddleName & "', '" & LastName & "', '" & Email & "', '" & now() & "', '" & now() & "', 1, '" & now() & "'); select @@Identity as 'ClientID';" Set RS = Server.CreateObject ("Adodb.Recordset") RS.CursorLocation = adUseServer RS.CursorType = adOpenForwardOnly RS.LockType = adLockReadOnly RS.Open sql, conn 'move to the second RS -- called in the sql statement above Set RS = RS.NextRecordset ClientID = RS("ClientID") RS.close set RS = nothing conn.close set conn = nothing 'set the cookie with response .cookies("DocDel")("SignedIn") = true .cookies("DocDel")("ClientID") = ClientID .cookies("DocDel")("FormalTitle") = FormalTitle .cookies("DocDel")("FirstName") = FirstName .cookies("DocDel")("MiddleName") = MiddleName .cookies("DocDel")("LastName") = LastName .cookies("DocDel")("Email") = Email 'this is encrypted? .cookies("DocDel")("staff") = false end with response.redirect("default.asp") end if else 'we can't find the person in TCDirectory with the UNI that Wind sent and/or with the proper crededentials to use this site RS.close set RS = nothing conn.close set conn = nothing 'check to see if we added them directly to the docdel database -- if so let them in call CheckSpecial(UNI) end if end sub sub CheckSpecial(pUNI) call funct_Connection(conn) sql = "select top 1 Client.* from Client where Client.UNI = '" & pUNI & "'" Set RS = Server.CreateObject ("Adodb.Recordset") RS.Open sql, conn, adOpenStatic, adLockReadOnly if RS.RecordCount > 0 then 'we have an account for this person -- update stats, give them a cookie and send them on thier way 'update client stats set cmd = Server.CreateObject("ADODB.command") cmd.ActiveConnection = conn cmd.CommandText = "sp_UpdateClientStats" cmd.CommandType = AdCmdStoredProc 'Here we manually create the parameters and append them to the parameters collection cmd.parameters.Append cmd.CreateParameter("ClientID", adInteger, adParamInput) 'Here we add values to the newly created input parameters cmd.Parameters("ClientID").Value = RS("ClientID") 'Execute the stored procedure cmd.execute set cmd = nothing 'set the cookie with response .cookies("DocDel")("SignedIn") = true .cookies("DocDel")("ClientID") = RS("ClientID") .cookies("DocDel")("FormalTitle") = RS("FormalTitle") .cookies("DocDel")("FirstName") = RS("FirstName") .cookies("DocDel")("MiddleName") = RS("MiddleName") .cookies("DocDel")("LastName") = RS("LastName") .cookies("DocDel")("Email") = RS("Email") 'this is encrypted .cookies("DocDel")("staff") = RS("Staff") end with RS.close set RS = nothing conn.close set conn = nothing response.redirect("default.asp") else 'no account call Error(2) end if end sub sub Error(ErrorNumber) dim str str = str & "" str = str & "" str = str & "Milbank Document Delivery" str = str & "" str = str & "" str = str & "" str = str & "
" str = str & "" str = str & "" str = str & "" str = str & "" str = str & "" str = str & "" str = str & "" str = str & "
" str = str & "Help | Milbank Library Main Page
 
 
" str = str & "" str = str & "" str = str & "
" select case ErrorNumber case 0 'no ticketid yet str = str & "Login Required

To use the Milbank Document Delivery service, you must be a full-time faculty member at Teachers College, Columbia University. To verify your identity please click here to sign-in." case 1 'ticket not valid str = str & "Invalid Ticket

The ""ticket"" which you obtained at a previous login is not currently valid. Please click here to login again and obtain a new ticket." case 2 'incorrect credentials str = str & "Access Problem

It seems that although you have a valid Columbia UNI, you are not currently listed among the full-time faculty at Teachers College. Please contact Michael Rennick at mr366@Columbia.edu if you believe this to be an error. Or, you may try logging in with a different account by clicking here." end select str = str & "
" str = str & "" str = str & "" str = str & "" str = str & "" str = str & "" str = str & "
 
Copyright " & year(now) & " Milbank Memorial Library, Teachers College, Columbia University. All rights reserved.
" str = str & "
" str = str & "" str = str & "" response.write(str) end sub sub EvaluateTicket() ticketid = request.querystring("ticketid") ValidateUrl = "https://www1.columbia.edu/sec-cgi-bin/wind/validate" QueryString = "ticketid=" & Server.URLEncode(ticketid) Set oHttp = Server.CreateObject("SOFTWING.AspTear") oHttp.TrustUnknownCA = True 'need to get around the Columbia-issued certificate (setting this property is only possible with the $35 version -- if Columbia used Verisign certs then we could use the free version of AspTear) oHttp.IgnoreInvalidCN = True sResponse = oHttp.Retrieve(ValidateURL, 2, QueryString, "", "") set oHttp = nothing 'process response here ----------------- if instr(1, sResponse, "yes", 1) then 'the ticket is valid so proceed with authorization call ProcessYesResponse() else 'bad ticket, so let them try again? call Error(1) end if end sub 'Run the program if request.querystring("ticketid").count = 0 then 'then they have not signed in yet call Error(0) if not err.number = 0 then 'call error handler -- error handler code not shown on this page end if else 'they have a ticket that needs checking call EvaluateTicket() if not err.number = 0 then 'call error handler -- error handler code not shown on this page end if end if %>