Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Refresh my page then alert a rep after a database update

Status
Not open for further replies.
JoeZ430

JoeZ430

Technical User
Jan 4, 2005
51
US
This is for a help desk that we implemented and
We want to be able to keep our problems page open in the explorer for the reps so when a new support ticket comes in the rep will get a notice all of this is located in our file viewall.asp and we want it to refresh maybe every 5-10 minutes then if it refreshes and finds a new problem maybe have it blow up full screen or play a sound or maybe just have a simple alert box pop up to alert the rep of the new problem.Does anybody have any solutions for this? Is there any way to have it refresh and then check the database and if there is a new update in the database have it play a sound or any way to alert the rep so he will be alerted when a new problem comes in thank you. here is the code from the asp page any information on this would be appriciated thanks. here would be the refresh script
that i would put in this code.
META HTTP-EQUIV="Refresh" CONTENT="5;URL="viewall.asp">


<%@ LANGUAGE="VBScript" %>
<%
'Option Explicit
'Buffer the response, so Response.Expires can be used
Response.Buffer = TRUE
%>


<?xml version="1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<html xmlns="
<!--
Liberum Help Desk, Copyright (C) 2000-2001 Doug Luxem
Liberum Help Desk comes with ABSOLUTELY NO WARRANTY
Please view the license.html file for the full GNU General Public License.

Filename: viewall.asp
Date: $Date: 2002/06/15 23:49:20 $
Version: $Revision: 1.50.2.2.2.1 $
Purpose: This page lists the open problem for the rep or another selected rep.
-->

<!-- #include file = "../public.asp" -->
<%
Dim cnnDB, sid
Set cnnDB = CreateCon
sid = GetSid
%>

<head>
<title><%=lang(cnnDB, "HelpDesk")%> - <%=lang(cnnDB, "OpenProblems")%></title>
<link rel="stylesheet" type="text/css" href="../default.css">
</head>
<body>

<%
' Check if user has permissions for this page
Call CheckRep(cnnDB, sid)

' Determine if a rep_id has been entered by form or in the
' URL. If not, just query on the rep's own id.
Dim rep_id
if Len(Request.QueryString("rep_id")) > 0 Then
rep_id = Cint(Request.QueryString("rep_id"))
Elseif Len(Request.Form("rep_id")) > 0 Then
rep_id = Cint(Request.Form("rep_id"))
Else
rep_id = sid
End If

' Get the rep's name that is being queryed on
Dim ruid, repRes
Set repRes = SQLQuery(cnnDB, "SELECT uid FROM tblUsers WHERE sid=" & rep_id )
ruid = repRes("uid")

' Query the database for the problems.
Dim listStr, cntStr, listRes
listStr = "SELECT TOP 100 p.id, p.title, p.start_date, p.uid, p.uemail, r.uid AS ruid, f.location1 as floc, pri.pname, s.sname " & _
"FROM (((problems AS p " & _
"INNER JOIN tblUsers AS r ON p.rep = r.sid) " & _
"LEFT JOIN tblUsers AS f ON p.uid = f.uid) " & _
"INNER JOIN priority AS pri ON p.priority = pri.priority_id) " & _
"INNER JOIN status AS s ON p.status = s.status_id " & _
"WHERE"

cntStr = "SELECT count(*) AS total FROM problems WHERE"
Dim disp_total

' If a problem ID is entered, search only for that
If Len(Request.QueryString("id"))>0 Then
listStr = listStr & " p.id=" & Request.QueryString("id")
disp_total = FALSE
Else
listStr = listStr & " p.status<>" & Cfg(cnnDB, "CloseStatus")
cntStr = cntStr & " status<>" & Cfg(cnnDB, "CloseStatus")
disp_total = TRUE
End If

' Determine Sort Order
Dim intSort, intOrder, intIDOrder, intTitleOrder, intUIDOrder, intDateOrder, intPriOrder, intStatusOrder
intSort = Cint(Request.QueryString("sort"))
If Len(Request.QueryString("order")) > 0 Then
intOrder = Cint(Request.QueryString("order"))
Else
intOrder = 0
End If
Select Case intSort
Case 1 ' id
listStr = listStr & " ORDER BY p.id"
If intOrder = 0 Then
listStr = listStr & " DESC"
intIDOrder = 1
Else
listStr = listStr & " ASC"
intIDOrder = 0
End If
Case 2 ' title
listStr = listStr & " ORDER BY p.title"
If intOrder = 0 Then
listStr = listStr & " ASC"
intTitleOrder = 1
Else
listStr = listStr & " DESC"
intTitleOrder = 0
End If
Case 3 ' uid
listStr = listStr & " ORDER BY p.uid"
If intOrder = 0 Then
listStr = listStr & " ASC"
intUIDOrder = 1
Else
listStr = listStr & " DESC"
intUIDOrder = 0
End If
Case 4 ' start_date
listStr = listStr & " ORDER BY p.start_date"
If intOrder = 0 Then
listStr = listStr & " DESC"
intDateOrder = 1
Else
listStr = listStr & " ASC"
intDateOrder = 0
End If
Case 5 ' priority
listStr = listStr & " ORDER BY p.priority"
If intOrder = 0 Then
listStr = listStr & " DESC"
intPriOrder = 1
Else
listStr = listStr & " ASC"
intPriOrder = 0
End If
Case 6 ' status
listStr = listStr & " ORDER BY p.status"
If intOrder = 0 Then
listStr = listStr & " DESC"
intStatusOrder = 1
Else
listStr = listStr & " ASC"
intStatusOrder = 0
End If
Case 7 ' rep
listStr = listStr & " ORDER BY ruid"
If intOrder = 0 Then
listStr = listStr & " DESC"
intROrder = 1
Else
listStr = listStr & " ASC"
intROrder = 0
End If
Case 8 ' location
listStr = listStr & " ORDER BY floc"
If intOrder = 0 Then
listStr = listStr & " DESC"
intLOrder = 1
Else
listStr = listStr & " ASC"
intLOrder = 0
End If
Case Else ' id again
listStr = listStr & " ORDER BY p.id"
If intOrder = 0 Then
listStr = listStr & " DESC"
intRIDOrder = 1
Else
listStr = listStr & " ASC"
intRIDOrder = 0
End If
End Select

Set listRes = SQLQuery(cnnDB, listStr)

' Get a total number of problems returned
Dim cntRes, start
If disp_total Then
Set cntRes = SQLQuery(cnnDB, cntStr)
End If

' If not empty results, set up the page. Only display
' 10 results per page.
If Not listRes.EOF Then
Dim Counter, numToDisplay, startNum
Counter = 1
If Len(Request.QueryString("num")) > 0 Then
numToDisplay = CInt(Request.QueryString("num"))
Else
numToDisplay = 30
End if
If Len(Request.QueryString("start")) > 0 Then
start = CInt(Request.QueryString("start"))
Else
start = 1
End if

Dim strColumns, intUseInoutBoard
intUseInoutBoard = Cfg(cnnDB, "UseInoutBoard")
If intUseInoutBoard = 1 Then
strColumns = 8
Else
strColumns = 8
End If

%>
<div align="center">
<table class="Wide" rules=rows>
<tr class="Head1">
<td colspan="<%=strColumns%>">
All problems NOT closed for all Reps
<%
If disp_total Then
Response.Write("&nbsp;(" & lang(cnnDB, "Total") & ":" & cntRes("total") & ")")
cntRes.Close
End If
%>
</td>
</tr>
<tr align="center" Class="Head2">
<td nowrap><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=1&order=<% = intIDOrder %>" class="HeadLink"><%=lang(cnnDB, "ID")%></a></td>
<td><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=2&order=<% = intTitleOrder %>" class="HeadLink"><%=lang(cnnDB, "Title")%></a></td>
<td nowrap><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=3&order=<% = intUIDOrder %>" class="HeadLink">User</a></td>
<td nowrap><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=8&order=<% = intLOrder %>" class="HeadLink">&nbsp;User Location&nbsp;</a></td>
<td nowrap><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=7&order=<% = intROrder %>" class="HeadLink">Rep</a></td>
<% If intUseInoutBoard = 1 Then %>
<td><div class="info">i</div></td>
<% End If %>
<td nowrap><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=4&order=<% = intDateOrder %>" class="HeadLink">&nbsp;Submitted&nbsp;</a></td>
<td><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=5&order=<% = intPriOrder %>" class="HeadLink">Last Updated</a></td>
<td><a href="viewall.asp?rep_id=<% = rep_id %>&start=<% = start %>&num=<% = numToDisplay %>&sort=6&order=<% = intStatusOrder %>" class="HeadLink"><%=lang(cnnDB, "Status")%></a></td>
</tr>
<%
Do While Not (listRes.EOF) AND (Counter <= (numToDisplay + start - 1))
If Counter >= start Then
%>
<% probid= listRes("id")

DateModStr = "SELECT addDate as LastModifiedDate,uid FROM tblNotes where id=" & probid & " ORDER BY addDate DESC"

Set DateModList = SQLQuery(cnnDB, DateModStr)
If Not DateModList.EOF Then
if DisplayDate(DateModList("LastModifiedDate"), lhdDateOnly) = DisplayDate(date, lhdDateOnly) then
LastModified = "Today<br>" & right(DateModList("LastModifiedDate"),11) & "<br>by " & DateModList("uid")
elseif DisplayDate(DateModList("LastModifiedDate"), lhdDateOnly) = DisplayDate(date-1, lhdDateOnly) then
LastModified = "Yesterday<br>" & right(DateModList("LastModifiedDate"),11) & "<br>by " & DateModList("uid")
elseif Date - Cdate(DateModList("LastModifiedDate")) > 5 then
LastModified = DisplayDate(DateModList("LastModifiedDate"), lhdDateOnly) & "<br><b>(" & cint(Date - cdate(DateModList("LastModifiedDate"))) & " days ago)</b><br>by " & DateModList("uid")
else
LastModified = DisplayDate(DateModList("LastModifiedDate"), lhdDateOnly) & "<br>" & right(DateModList("LastModifiedDate"),11) & "<br>by " & DateModList("uid")
end if

else
LastModified = "<b>Not since<br> opening</b>" 'listRes("start_date")
end if



%>
<tr align="center" valign="center" class="Body1">
<td nowrap><% = listRes("id") %></td>
<td><A HREF="details.asp?id=<% = listRes("id") %>"><% = listRes("title") %></A></td>
<td nowrap><A HREF="mailto:<% = listRes("uemail") %>?Subject=HELPDESK: Problem <% = listRes("id") %>"><% = listRes("uid") %></A></td>
<td><A HREF="details.asp?id=<% = listRes("id") %>">
<% if listRes("floc") <> "" then %>
<% = listRes("floc") %></A></td>
<%else%>
Not Entered</A></td>
<% end if %>
<% if ruid = listRes("ruid") then %>
<td bgcolor=88cc99 nowrap><b><font color=white>&nbsp;For Me!&nbsp;<br><A HREF="details.asp?id=<% = listRes("id") %>"><font size=-2>(open)</b></font></b></font></td>
<% elseif listRes("ruid") = "itsupport" then %>
<td bgcolor=00cc00><b><font color=white>&nbsp;Unallocated&nbsp;<br><A HREF="details.asp?id=<% = listRes("id") %>"><font size=-2>(open)</b></font></A></td>
<% else %>
<td><A HREF="details.asp?id=<% = listRes("id") %>"><% = listRes("ruid") %><br><A HREF="details.asp?id=<% = listRes("id") %>"><font size=-2>(open)</b></font></A></td>
<% end if %>
<%
If intUseInoutBoard = 1 Then
dim rstUser
set rstUser = sqlQuery(cnnDB, "SELECT sid FROM tblUsers WHERE uid='" & listRes("uid") & "'")
If not rstUser.EOF then
Response.Write "<td><a href=""../inout/details.asp?id=" & rstUser("sid") & """><div class=""info"">i</div></a></td>"
Else
Response.Write "<td>&nbsp;</td>"
End If
rstUser.Close
End If
%> <% if DisplayDate(listRes("start_date"), lhdDateOnly) = DisplayDate(date, lhdDateOnly) then %>
<td nowrap><b>Today<br><%=right(listRes("start_date"),11)%></b></td>
<% elseif DisplayDate(listRes("start_date"), lhdDateOnly) = DisplayDate(date-1, lhdDateOnly) then %>
<td nowrap>Yesterday<br><%=right(listRes("start_date"),11)%></td>
<% else %>
<% if WeekDay(CDate(listRes("start_date"))) = 1 then
friendly = "Sunday"
elseif WeekDay(CDate(listRes("start_date"))) = 2 then
friendly = "Monday"
elseif WeekDay(CDate(listRes("start_date"))) = 3 then
friendly = "Tuesday"
elseif WeekDay(CDate(listRes("start_date"))) = 4 then
friendly = "Wednesday"
elseif WeekDay(CDate(listRes("start_date"))) = 5 then
friendly = "Thursday"
elseif WeekDay(CDate(listRes("start_date"))) = 6 then
friendly = "Friday"
elseif WeekDay(CDate(listRes("start_date"))) = 7 then
friendly = "Saturday"
end if %>
<td ><%=friendly%><br><% = DisplayDate(listRes("start_date"), lhdDateOnly) %></td>
<% end if %>
<td nowrap><%=lastmodified%></td>

<% if listRes("sname") = "Pending" then %>
<td nowrap bgcolor="ffff99"><b><% = listRes("sname") %></b></td>
<% elseif listRes("sname") <> "OPEN" then %>
<td nowrap ><% = listRes("sname") %></td>
<% else %>
<!--Open status-->
<td nowrap bgcolor=ff3333 style="color:white"> <IMG SRC="exclam.gif"><b><% = listRes("sname") %></b></td>
<% end if%>
</tr>
<%
End If
Counter = Counter + 1
listRes.MoveNext
Loop
DateModList.Close
Response.Write("</table></center>")

' Calculate prev/next page links
Dim startP, StartN
startP = start - numToDisplay
If startP < 1 Then
startP = 1
End if
startN = start + numToDisplay
%>
<div align="center">
<% If start > 1 Then %>
<A HREF="viewall.asp?rep_id=<% = rep_id %>&start=<% = startP %>&num=<% = numToDisplay %>&sort=<% = intSort %>&order=<% = intOrder %>">Previous</A>&nbsp;
<% End If
If Not (listRes.EOF) Then
%>
<A HREF="viewall.asp?rep_id=<% = rep_id %>&start=<% = startN %>&num=<% = numToDisplay %>&sort=<% = intSort %>&order=<% = intOrder %>">Next</A>
<% End If %>
</div>
<%

' If no results returned:
Else
%>
<div align="center">
<table border="0" cellspacing="3" cellpadding="5" width="600">
<tr class="Head1">
<td colspan="6" valign="center">
<font size="+2"><b><%=lang(cnnDB, "OpenProblemsfor")%>&nbsp;<% = ruid %></b></font>
</td>
</tr>
<tr align="center" Class="Head2">
<td nowrap><%=lang(cnnDB, "ID")%></td>
<td><%=lang(cnnDB, "Title")%></td>
<td nowrap><%=lang(cnnDB, "UserName")%></td>
<% If intUseInoutBoard = 1 Then %>
<td><div class="info">i</div></td>
<% End If%>
<td nowrap><%=lang(cnnDB, "DateSubmitted")%></td>
<td><%=lang(cnnDB, "Priority")%></td>
<td><%=lang(cnnDB, "Status")%></td>
</tr>
<tr align="center" class="Body1">
<td colspan="6">
<%=lang(cnnDB, "Noresultsfound")%>.
</td>
</tr>
</table>
</div>
<% End If

' Close results

listRes.Close
repRes.Close

Call DisplayFooter(cnnDB, sid)
cnnDB.Close
%>

</body>
</html>





Joe Z
 
Sort by date Sort by votes
I am not sure how to produce sounds when a new ticket get shows up...but i guess we can easily highlight this new ticket request showing that its submitted recently...

-L
 
Upvote 0 Downvote
thank you for the quick response is there any way to make that window full screen or make an alert box pop up when a new ticket comes in thank you for all your help.
 
Upvote 0 Downvote
You could put a counter in your client side script so that the page itself refreshes every X minutes... sorta like when you see a page that says "You will be redirected in X seconds, click here if not.." Just have the page reload itself and in some script at the top before the HTML ... this is where you would look to see if anything has changed and display different HTML depending on what you find, you could make another browser window popup even... assuming they don't have browser popups blocked.

You could also put an input trigger on your database. Many databases support input triggers that are basically little stored procedures associated with a table so that whenever a row is inserted the procedure will run. You could use the procedure to send a windows pop-up or an email or whatever. By "windows pop-up" I mean the kind that you do with NET SEND.

You could write a little service that polls the database every X number of minutes and then sends emails or popups or whatever. I wrote one of these that polls the timeclock database to see if anyone has been on the clock for more than 16 hours. It emails and sends popups to the managers.
 
Upvote 0 Downvote
thank you for the information! I am going to look into this and see if I can implement the input trigger.
 
Upvote 0 Downvote
<SCRIPT language=Javascript>
function refreshPeriodic() {
location.reload();
}
timerID = setTimeout("refreshPeriodic()",60000);
</SCRIPT>

Put this at the start of your ASP page and it forces a reload of the page every 60 seconds or 60000 miliseconds

I assume your ticket numbers increment.
Store the last know ticket number in a cookie
Refresh the page using your method or the above
On refresh check the cookie value against what is read from the database
If the database has a newer ticket number flag that in your ASP output in a hidden value and check it using a javasctipt which is executed when the page is loaded in the browser using the onLoad event in the <body> tag
Your javascript can display an alert to avoid any POP UP blockers etc
 
Upvote 0 Downvote
Thank you so much i have wrote some code here and it is now working but i ran into one problem i was trying to reseatch a way to alert the rep via script for instance if he has the browser window minimized have the windows maximize when a new support ticket comes in or even i saw for outlook for the web which was very cool is when a new mail message comes they have a little popup window that comes up in the system tray and says new mail message and they made that all web based does anybody have any solutions for this thanks!
 
Upvote 0 Downvote
For one site I maintain, there's a monitor that has the ASP page in an <iframe> that updates every 15 minutes to indicate how many new orders have come in. There's also a button that refreshes the page in the <iframe>, if desired. Clicking on a button on the frame page opens a new window that sends the viewer to either the list of new orders (if there are any) or the main page (if no new orders) to select which orders to view. If there are any new orders (the number of orders is stored on the parent page) from the last refresh, the page is set to bring itself to the forefront with self.focus().

Lee
 
Upvote 0 Downvote
thank you so much! it works but i changed self to window.focus(); very much appriciated .
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
753
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
569
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
489
leifoet
leifoet
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
581
leifoet
leifoet
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
607
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top