<% ' *********************************************************** ' ' 09/03/07 781 list only UPDATES (tracking) since the requested date. ' ' *********************************************************** Session("PriorPage") = Session("CurrentPage") Session("CurrentPage") = "openinv.asp" Dim DD if DBMS = 2 then DD = "'" 'MS SQL Server date delimiter else DD = "#" 'MS Access date delimiter end if ' ***** Initialize Query ***** Dim ConnStr Dim Conn Dim rs Dim rsTrack Dim SQL Dim SQL2 Dim PriorStatus Dim PriorAssign Dim StatusID Dim HeadName Dim Debtor Dim Vehicle Dim UpdateDate Dim Comment Dim UserType Dim UserClass Dim UserName Dim EmpID Dim strAgentName Dim UserID Dim FromDate ' *************************************** ' Initialize Variables ' *************************************** UserID = Session("UserID") strErrorMsg = "" FromDate = Request.querystring("FromDate") if len(FromDate) > 50 then FromDate = "" end if ' FromDate = dateadd("h",-1,"1/1/06 01:00 AM") ' FromDate = dateadd("h",-1,Now()) ' *************************************** ' Connect to Database ' *************************************** connectdb If Err.number <> 0 Then strErrorMsg = strErrorMsg & "305. Error Unable to connect to database.
" Err.Clear End If ' *************************************** ' Read Employee Logout Date ' *************************************** if strErrorMsg = "" then if FromDate = "" then SQL = "SELECT LogoutDate " SQL = SQL & "FROM Employee " SQL = SQL & "WHERE EmpID = " & UserID & " " Set rs = Conn.Execute(SQL) if err.number <> 0 then strErrorMsg = strErrorMsg & "302. Error Unable to connect to Employee database.
" Err.Clear else While Not rs.EOF if not isnull(rs.Fields(0)) then FromDate = formatdatetime(rs.Fields(0),2) end if rs.movenext Wend rs.close end if set rs = nothing end if end if if FromDate = "" then FromDate = date end if if not isdate(fromdate) then FromDate = "" strErrorMsg = strErrorMsg & "301. Invalid Date Format.
" end if ' *************************************** ' Select Case Information ' *************************************** if strErrorMsg = "" then 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 & "R1.Addr1, " SQL = SQL & "R1.City, " SQL = SQL & "R1.State, " SQL = SQL & "R1.Zip, " 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 " 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 " if session("UserType") = 2 then SQL = SQL & "AND C.CaseID IN (" SQL = SQL & "SELECT Track.ID FROM Track " SQL = SQL & "WHERE Track.entrydate >= " & DD & FromDate & DD & " " SQL = SQL & "AND Track.EmpID <> " & cstr(UserID) & " " SQL = SQL & "AND Track.Private <> 'Y') " else SQL = SQL & "AND C.CaseID IN (" SQL = SQL & "SELECT Track.ID FROM Track " SQL = SQL & "WHERE Track.entrydate >= " & DD & FromDate & DD & ") " end if if session("UserType") = 3 then SQL = SQL & " UNION " SQL = 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 & "R1.Addr1, " SQL = SQL & "R1.City, " SQL = SQL & "R1.State, " SQL = SQL & "R1.Zip, " 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 T " SQL = SQL & "Where R1.Type = '3' " SQL = SQL & "AND R1.Active = 'Y' " SQL = SQL & "AND T.CaseID = R1.ID " SQL = SQL & "AND T.EmpID = " & session("userid") & " " SQL = SQL & "AND T.Active = 'Y' " 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 C.CaseID IN (" SQL = SQL & "SELECT Track.ID FROM Track " SQL = SQL & "WHERE Track.entrydate > " & DD & FromDate & DD & ") " else ' Add the "processing" status SQL = SQL & " UNION " SQL = SQL & "SELECT C.CaseID, " SQL = SQL & "9999, " SQL = SQL & "C.AssignType, " SQL = SQL & "C.Agent, " SQL = SQL & "R1.Lname, " SQL = SQL & "R1.Fname, " SQL = SQL & "R1.Addr1, " SQL = SQL & "R1.City, " SQL = SQL & "R1.State, " SQL = SQL & "R1.Zip, " SQL = SQL & "C.Year, " SQL = SQL & "C.Make, " SQL = SQL & "C.Model, " SQL = SQL & "C.ClientAcct, " SQL = SQL & "C.VIN, " SQL = SQL & "'Processing New Assignment' as StatusDesc, " SQL = SQL & "Cd2.CodeDesc as AssignDesc, " SQL = SQL & "' ' as AgentLname, " SQL = SQL & "' ' as AgentFname, " SQL = SQL & "R3.Lname as ClientLname " SQL = SQL & "FROM tCase C, Rdex R1, Rdex R3, Codes Cd1, Codes Cd2 " 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 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 = 0 " SQL = SQL & "AND R1.MasterID = R3.ID " end if ' SQL = SQL & "Order by Cd1.CodeDesc, Cd2.CodeDesc, R1.Lname, R1.Fname, C.CaseID " SQL = SQL & "Order by 16 DESC, 17, 5, 6, 1 " ' ***** Start Page ***** %> Open Inventory

What's New

<% ' ***** Run Query ***** Set rs = Conn.Execute(SQL) If Err.number <> 0 Then strErrorMsg = strErrorMsg & "Database Read failure.
" & Err.Description Err.Clear showerror(strErrorMsg) return End If priorStatus = -1 priorAssign = -1 While Not rs.EOF ' ***** check for most recient tracking ***** ' read date of last tracking note SQL2 = "SELECT EntryDate, " SQL2 = SQL2 & "EmpID, " SQL2 = SQL2 & "Comment, " SQL2 = SQL2 & "Private, " SQL2 = SQL2 & "R1.Type, " SQL2 = SQL2 & "R1.Lname, " SQL2 = SQL2 & "R1.Fname " SQL2 = SQL2 & "FROM Track, Rdex R1 " SQL2 = SQL2 & "WHERE Track.ID = " & cstr(rs.fields("CaseID")) & " " SQL2 = SQL2 & "AND EmpID = R1.ID " if session("UserType") = 2 then SQL2 = SQL2 & "AND Private <> 'Y' " end if SQL2 = SQL2 & "ORDER BY EntryDate Desc " Set rsTrack = Conn.Execute(SQL2) UpdateDate = " " UserName = "" EmpID = 0 if NOT rsTrack.EOF then if NOT IsNull(rsTrack.fields("EntryDate")) then UpdateDate = rsTrack.fields("EntryDate") end if if NOT IsNull(rsTrack.fields("EmpID")) then EmpID = rsTrack.fields("EmpID") end if Comment = rsTrack.fields("Comment") if len(Comment) > 120 then Comment = Left(Comment, 120) & "..." end if UserType = rsTrack.fields("Type") if UserType <= 2 then UserClass = "UpdateC" else UserClass = "UpdateA" UserName = rsTrack.fields("Fname") & " " & rsTrack.fields("Lname") end if if not isnull(rsTrack.fields("Private")) then if rsTrack.fields("Private") = "Y" then UserClass = "UpdateB" end if end if else UserClass = "UpdateB" Comment = "" UpdateDate = "" end if rsTrack.close set rsTrack = Nothing if EmpID <> session("userid") then StatusID = rs.fields("Status") AssignID = rs.fields("AssignType") if (StatusID <> PriorStatus) _ or (AssignID <> PriorAssign) then if priorStatus > 0 then ' ***** Close prior section ***** %>

<% ' ***** Case Variables ***** end if HeadName = rs.fields("StatusDesc") & " - " & rs.fields("AssignDesc") ' ***** Format new Section ***** %>

<%=HeadName%>

<% ' %> <% end if ' ***** Format Detail Line ***** Debtor = rs.fields("Lname") if rs.fields("Fname") > "" then Debtor = Debtor & ", " & rs.fields("Fname") end if strAgentName = trim(rs.fields("AgentLname")) if rs.fields("AgentFname") > "" then strAgentName = strAgentName & ", " & trim(rs.fields("AgentFname")) end if strAgentName = Left(strAgentName,15) Vehicle = rs.fields("Year") Vehicle = Vehicle & " " & rs.fields("Make") Vehicle = Vehicle & " " & rs.fields("Model") %> <% 'comment %> <% 'if session("UserType") = 2 then ' >< 'else %><% 'end if %> <% priorStatus = StatusID priorAssign = AssignID end if rs.MoveNext Wend rs.close set rs = nothing conn.close set conn = nothing %>

Case	Account	Name	Vehicle / VIN	Leinholder	Last Update	Action
"><%=cstr(rs.fields("CaseID"))%>	< %=rs.fields("ClientAcct")% >	<%=Debtor%> <% if INVSTYLE = "DETAIL" then if rs.fields("addr1") > "" then %> <%=rs.fields("addr1")%> <% end if %> <%=rs.fields("city")%>, <%=rs.fields("state")%> <%=rs.fields("zip")%> <% end if %>	<%=Vehicle%> <%=rs.fields("Vin")%>	<=strAgentName>	<%=left(rs.fields("ClientLName"),15)%>	<%=UpdateDate%> - <%=UserName%> <%=Comment%>	">Update <% ' if StatusID = 2 then %> ">Repo Data<% ' end if %>

<% end if ' *************************************** ' Send Response ' *************************************** if strErrorMsg > "" then ShowError(strErrorMsg) end if %>