<% ' *********************************************************** ' ' 10/28/06 612 Add search by last 6 of vin ' ' *********************************************************** ' Session("PriorPage") = Session("CurrentPage") ' Session("CurrentPage") = "Alphainv.asp" ' ***** Initialize Query ***** Dim ConnStr Dim Conn Dim rs Dim rsTrack Dim SQL Dim SQL2 Dim StatusID Dim HeadName Dim Debtor Dim Vehicle Dim UpdateDate Dim strAgentName ' New Values Dim CaseID Dim ClientAcct Dim Lname ' *************************************** ' Set new values ' *************************************** EntryDate = Request.QueryString("EntryDate") EntryDate = trim(EntryDate) EntryDate = Replace(EntryDate, "'", "''") KeyWord = Request.QueryString("KeyWord") KeyWord = trim(KeyWord) KeyWord = Replace(KeyWord, "'", "''") ' *************************************** ' Connect to Database ' *************************************** connectdb SQL = "SELECT C.CaseID, " SQL = SQL & "C.Status, " SQL = SQL & "C.AssignType, " SQL = SQL & "C.Agent, " SQL = SQL & "R1.Lname, " SQL = SQL & "R1.Fname, " SQL = SQL & "C.Year, " SQL = SQL & "C.Make, " SQL = SQL & "C.Model, " SQL = SQL & "C.ClientAcct, " SQL = SQL & "C.VIN, " SQL = SQL & "Cd1.CodeDesc as StatusDesc, " SQL = SQL & "Cd2.CodeDesc as AssignDesc, " SQL = SQL & "R2.Lname as AgentLname, " SQL = SQL & "R2.Fname as AgentFname, " SQL = SQL & "R3.Lname as ClientLname " SQL = SQL & "FROM tCase C, Rdex R1, Rdex R2, Rdex R3, Codes Cd1, Codes Cd2, Track " SQL = SQL & "Where R1.Type = '3' " SQL = SQL & "AND R1.Active = 'Y' " if session("UserType") = 2 then SQL = SQL & "AND R1.MasterID = " & session("userid") & " " end if if session("UserType") = 3 then SQL = SQL & "AND C.Agent = " & session("userid") & " " end if SQL = SQL & "AND C.CaseID = R1.ID " SQL = SQL & "AND C.Status = Cd1.CodeID " SQL = SQL & "AND C.AssignType = Cd2.CodeID " SQL = SQL & "AND C.Agent = R2.ID " SQL = SQL & "AND R1.MasterID = R3.ID " SQL = SQL & "AND Track.ID = R1.ID " ' add search values if IsDate(EntryDate) then SQL = SQL & "AND Track.EntryDate >= '" & EntryDate & "' " end if if Keyword > "" then SQL = SQL & "AND Track.Comment LIKE '%" & Keyword & "%' " end if ' ' Union with team access for Agent logins ' if session("UserType") = 3 then SQL = SQL & "UNION SELECT C.CaseID, " SQL = SQL & "C.Status, " SQL = SQL & "C.AssignType, " SQL = SQL & "C.Agent, " SQL = SQL & "R1.Lname, " SQL = SQL & "R1.Fname, " SQL = SQL & "C.Year, " SQL = SQL & "C.Make, " SQL = SQL & "C.Model, " SQL = SQL & "C.ClientAcct, " SQL = SQL & "C.VIN, " SQL = SQL & "Cd1.CodeDesc as StatusDesc, " SQL = SQL & "Cd2.CodeDesc as AssignDesc, " SQL = SQL & "R2.Lname as AgentLname, " SQL = SQL & "R2.Fname as AgentFname, " SQL = SQL & "R3.Lname as ClientLname " SQL = SQL & "FROM tCase C, Rdex R1, Rdex R2, Rdex R3, Codes Cd1, Codes Cd2, Team, Track " SQL = SQL & "Where R1.Type = '3' " SQL = SQL & "AND R1.Active = 'Y' " SQL = SQL & "AND Team.Active = 'Y' " SQL = SQL & "AND R1.ID = Team.CaseID " SQL = SQL & "AND Team.EmpID = " & session("userid") & " " SQL = SQL & "AND C.CaseID = R1.ID " SQL = SQL & "AND C.Status = Cd1.CodeID " SQL = SQL & "AND C.AssignType = Cd2.CodeID " SQL = SQL & "AND C.Agent = R2.ID " SQL = SQL & "AND R1.MasterID = R3.ID " SQL = SQL & "AND Track.ID = R1.ID " ' add search values if IsDate(EntryDate) then SQL = SQL & "AND Track.EntryDate >= '" & EntryDate & "' " end if if Keyword > "" then SQL = SQL & "AND Track.Comment LIKE '%" & Keyword & "%' " end if end if ' ' Add Sort order ' SQL = SQL & "Order by R1.Lname, R1.Fname, C.CaseID " ' ***** Start Page ***** %> Search Results

Search Results

<% if ClientAcct > "" then %> <% end if %> <% if session("UserType") = 2 then %><% else %><% end if %> <% ' ***** Run Query ***** Set rs = Conn.Execute(SQL) While Not rs.EOF StatusID = rs.fields("Status") AssignID = rs.fields("AssignType") ' ***** Format new Section ***** %> <% ' end if line 115 ? ' ***** Format Detail Line ***** Debtor = rs.fields("Lname") if rs.fields("Fname") > "" then Debtor = Debtor & ", " & rs.fields("Fname") end if strAgentName = rs.fields("AgentLname") if rs.fields("AgentFname") > "" then strAgentName = strAgentName & ", " & rs.fields("AgentFname") end if strAgentName = Left(strAgentName,15) Vehicle = rs.fields("Year") Vehicle = Vehicle & " " & rs.fields("Make") Vehicle = Vehicle & " " & rs.fields("Model") %> <% if ClientAcct > "" then %> <% end if %> <% if session("UserType") = 2 then %><% else %><% end if ' read date of last tracking note SQL2 = "SELECT EntryDate " SQL2 = SQL2 & "FROM Track " SQL2 = SQL2 & "WHERE ID = " & cstr(rs.fields("CaseID")) & " " SQL2 = SQL2 & "ORDER BY EntryDate Desc " Set rsTrack = Conn.Execute(SQL2) UpdateDate = " " if NOT rsTrack.EOF then if NOT IsNull(rsTrack.fields("EntryDate")) then UpdateDate = rsTrack.fields("EntryDate") end if end if rsTrack.close set rsTrack = Nothing %> <% priorStatus = StatusID priorAssign = AssignID rs.MoveNext Wend rs.close set rs = nothing conn.close set conn = nothing %>
CaseAccountName Vehicle StatusAgentLeinholderLast Update Action
"><%=cstr(rs.fields("CaseID"))%><%=rs.fields("ClientAcct")%> <%=Debtor%> <%=Vehicle%>  <%=rs.fields("StatusDesc")%> <%=strAgentName%><%=left(rs.fields("ClientLName"),15)%><%=UpdateDate%> ">Update <% %> ">Repo Data<% %>