"For some reason," writes Graeme Hefner, "someone decided that it'd be best that we write our own bug/issue tracking system instead of using one of the plethora of fully-functional systems available on the market."
"For some reason, someone — perhaps the same someone — decided that the best way to develop this system was a SQL stored procedure called by a Classic ASP page that simply dumped everything to a single page list. Every ticket, ever. This is the core of its entire logic, which impressively uses of SQL, HTML, and JavaScript all in one query."
ALTER PROCEDURE [sp_Tickets_Select_Main] @StatusID int, @UserFilter varchar(255) = NULL, @DateFilter varchar(255) = NULL, @PriorityFilter varchar(255) = NULL, @CategoryFilter varchar(255) = NULL, @TechFilter varchar(255) = NULL, @DescriptionFilter varchar(255) = NULL AS SELECT '<input type="button" class="p' + cast(Tickets.PriorityID as varchar) + '" onmouseover="hov(this,''hov p' + cast(Tickets.PriorityID as varchar) + ''')" onmouseout="hov(this,''p' + cast(Tickets.PriorityID as varchar) + ''')" onclick="st('+ cast(Tickets.TicketID as varchar) + ')" value="#' + cast(Tickets.TicketID as varchar) + '">' , Category.CategoryName, UserInfo.UserFullName, (left(Tickets.Description,512) + '...'), Tickets.IssueDate FROM Tickets INNER JOIN Status ON Tickets.StatusID = Status.StatusID INNER JOIN Tech ON Tickets.TechID = Tech.TechID INNER JOIN Category ON Tickets.CategoryID = Category.CategoryID INNER JOIN Priority ON Tickets.PriorityID = Priority.PriorityID INNER JOIN UserInfo ON Tickets.UserName = UserInfo.UserName WHERE Tickets.StatusID = @StatusID AND (UserInfo.UserFullName LIKE '%' + ISNULL(@UserFilter, UserInfo.UserFullName) + '%' OR Tickets.UserName LIKE '%' + ISNULL(@UserFilter, Tickets.UserName)+ '%' OR UserInfo.UserExt LIKE '%' + ISNULL(@UserFilter, UserInfo.UserExt)+ '%' ) AND convert(varchar, Tickets.IssueDate,101) LIKE '%' + ISNULL(@DateFilter, convert(varchar, Tickets.IssueDate,101)) + '%' AND (Tickets.PriorityID LIKE '%' + ISNULL(@PriorityFilter, Tickets.PriorityID) + '%' OR Priority.PriorityName LIKE '%' + ISNULL(@PriorityFilter, Priority.PriorityName) + '%') AND (Category.CategoryName LIKE '%' + ISNULL(@CategoryFilter, Category.CategoryName) + '%' OR Category.CategoryName LIKE '%' + ISNULL(@CategoryFilter, Category.CategoryName) + '%') AND (Tickets.TechID LIKE '%' + ISNULL(@TechFilter, Tickets.TechID) + '%' OR Tech.TechUserName LIKE '%' + ISNULL(@TechFilter,Tech.TechUserName) + '%') AND Tickets.Description LIKE '%' + ISNULL(@DescriptionFilter, Tickets.Description) + '%' ORDER BY Tickets.PriorityID DESC, Tickets.TicketID DESC