<% dim rs set rs = server.createobject("ADODB.Recordset") 'sqlString = "SELECT Users.ID, Users.FirstName, Count(Trip.ID) AS TripCount, Sum(CountSegments.SegmentCount) AS SegmentCount, Max(CountSegments.FromDate) AS SegmentFromDate, Max(CountSegments.LastEdited) AS SegmentLastEdited, Sum(CountImages.ImageCount) AS ImageCount, Max(CountImages.LastEdited) AS ImageLastEdited, Sum(CountExpenses.ExpenseCount) AS ExpenseCount, Sum(CountExpenses.ExpenseSum) AS ExpenseSum, Sum(CountPacking.PackingCount) AS PackingCount, Sum(CountCompanions.CompanionCount) AS CompanionCount " & _ ' "FROM Users INNER JOIN (CountCompanions RIGHT JOIN (CountPacking RIGHT JOIN (CountExpenses RIGHT JOIN ((Trip INNER JOIN CountSegments ON Trip.ID = CountSegments.ID) INNER JOIN CountImages ON Trip.ID = CountImages.ID) ON CountExpenses.ID = Trip.ID) ON CountPacking.ID = Trip.ID) ON CountCompanions.ID = Trip.ID) ON Users.ID = Trip.UserID " & _ ' "WHERE ((((Trip.Active)=True) OR ((Users.ProfileImage)=True) OR ((Len(Users.Profile))>0)) AND ((Users.AccountActive)=True)) " & _ ' "GROUP BY Users.ID, Users.FirstName, Users.FirstName " & _ ' "ORDER BY Max(CountSegments.FromDate) DESC , Users.FirstName;" sqlString = "SELECT Users.ID, Users.FirstName, Sum(IIf([Trip].[Active]=True,1,0)) AS TripCount, Sum(CountSegments.SegmentCount) AS SegmentCount, Max(CountSegments.FromDate) AS SegmentFromDate, Max(CountSegments.LastEdited) AS SegmentLastEdited, Sum(CountImages.ImageCount) AS ImageCount, Max(CountImages.LastEdited) AS ImageLastEdited, Sum(CountExpenses.ExpenseCount) AS ExpenseCount, Sum(CountExpenses.ExpenseSum) AS ExpenseSum, Sum(CountPacking.PackingCount) AS PackingCount, Sum(CountCompanions.CompanionCount) AS CompanionCount " & _ "FROM Users RIGHT JOIN (CountCompanions RIGHT JOIN (CountPacking RIGHT JOIN (CountExpenses RIGHT JOIN ((Trip LEFT JOIN CountSegments ON Trip.ID = CountSegments.ID) LEFT JOIN CountImages ON Trip.ID = CountImages.ID) ON CountExpenses.ID = Trip.ID) ON CountPacking.ID = Trip.ID) ON CountCompanions.ID = Trip.ID) ON Users.ID = Trip.UserID " & _ "WHERE (((Users.AccountActive)=True) AND ((Trip.Active)=True)) OR (((Users.AccountActive)=True) AND ((Users.ProfileImage)=True)) OR (((Users.AccountActive)=True) AND ((Len([Users].[Profile]))>0)) " & _ "GROUP BY Users.ID, Users.FirstName, Users.FirstName " & _ "ORDER BY Max(CountSegments.FromDate) DESC , Users.FirstName;" rs.open sqlString, Application("RTW_DSN"), 0 %>
Journeys
<% dim tripsLink, colour do while not rs.eof if colour = 1 then colour = 2 else colour = 1 tripsLink = "" response.write "" rs.movenext loop rs.close set rs = nothing %>
" & tripsLink & "" & rs("firstName") & "" response.write tripsLink & rs("TripCount") & " journey" if rs("TripCount") <> 1 then response.write "s" end if response.write ", " & rs("SegmentCount") & " entr" if rs("SegmentCount") <> 1 then response.write "ies" else response.write "y" end if response.write ", " & rs("ImageCount") & " image" if rs("ImageCount") <> 1 then response.write "s" end if if len(rs("SegmentLastEdited")) > 0 and len(rs("ImageLastEdited")) > 0 then response.write "
Last Edited: " & formatDateShort(rs("SegmentLastEdited"),1) & " - Images: " & formatDateShort(rs("ImageLastEdited"),1) end if response.write "
<%= drawSpacer(517,1) %> <%= drawSpacer(123,1) %>