[444] in Tooltime
"Find Open" script
daemon@ATHENA.MIT.EDU (Debi Fuchs)
Tue Oct 28 11:29:56 1997
Date: Tue, 28 Oct 1997 11:30:27 -0500
To: pragone@MIT.EDU
From: Debi Fuchs <debi@MIT.EDU>
Cc: tooltime@MIT.EDU
Hi Pete,
I just wanted to clarify what's up with the "Find Open" script in Scopus,
which you had begun to look at before you left. This morning, we narrowed
down the functionality we're hoping to see in the "Find Open" script before
the release of Scopus.
First of all, we'd like "Find Open" to find all logs which are neither
cancelled or closed, or are Frozen and should be thawed, and sort them by
status weight, modified date, and log number. (Currently, the script finds
ALL Frozen logs, whether or not they should still be frozen, and they are
sorted by status weight and log number only.) Furthermore, we'd optimally
like to have "Find Open" be some kind of pop-up which allows one to select
a category to narrow down the query further.
That is,
Find Open-->CWIS
DCS
Helpdesk
Housecall
Mainframe
Projects
Netinstall
HelpNet
UNIX/VMS
I'm hoping you and David Ho can look into how we might do both of these
things in TCL. I have been looking at the SQL code which is getting
executed. From what I can tell, the following SQL code is run when the
"Find Open" script is run:
SELECT l.mit_log_id "Log", l.mit_status "Status",
l.mit_category "Category", l.mit_pri_lastname "Last Name",
l.mit_pri_firstname "First Name" ,
substr(to_char(l.mit_created_ts,'YYYY/MM/DD HH24:MI:SS'),1,20)
"Created On", substr(l.mit_summary,1,80) "Summary"
FROM mit_logs l, mit_lk_status s
WHERE l.mit_status = s.mit_status and s.mit_status_flag=1
and l.mit_status != 'CLOSED' and l.mit_status != 'CANCEL'
ORDER BY s.mit_weight desc, l.mit_log_id asc
The functionality we'd like it to have, instead, would be to extend the
WHERE and ORDER BY clauses as follows:
SELECT l.mit_log_id "Log", l.mit_status "Status",
l.mit_category "Category", l.mit_pri_lastname "Last Name",
l.mit_pri_firstname "First Name" ,
substr(to_char(l.mit_created_ts,'YYYY/MM/DD HH24:MI:SS'),1,20)
"Created On", substr(l.mit_summary,1,80) "Summary"
FROM mit_logs l, mit_lk_status s
WHERE l.mit_status = s.mit_status and s.mit_status_flag=1
and l.mit_status != 'CLOSED' and l.mit_status != 'CANCEL'
and (l.mit_status != 'FROZEN' OR (l.mit_status = 'FROZEN'
and l.mit_frozen_ts <= (SELECT sysdate from dual)))
ORDER BY s.mit_weight desc, l.mit_modified_ts, l.mit_log_id asc
As for the "by category" functionality, this could be done as follows. For
example, to modify the script to do a find based on "Helpdesk", the SQL
code could be modified as follows:
SELECT l.mit_log_id "Log", l.mit_status "Status",
l.mit_pri_lastname "Last Name",
l.mit_pri_firstname "First Name" ,
substr(to_char(l.mit_created_ts,'YYYY/MM/DD HH24:MI:SS'),1,20)
"Created On", substr(l.mit_summary,1,80) "Summary"
FROM mit_logs l, mit_lk_status s
WHERE l.mit_status = s.mit_status and s.mit_status_flag=1
and l.mit_category = 'Helpdesk'
and l.mit_status != 'CLOSED' and l.mit_status != 'CANCEL'
and (l.mit_status != 'FROZEN' OR (l.mit_status = 'FROZEN'
and l.mit_frozen_ts <= (SELECT sysdate from dual)))
ORDER BY s.mit_weight desc, l.mit_modified_ts desc, l.mit_log_id asc
Hope this helps,
-Debi
-------------
Debi Fuchs, Consultant
MIT Information Systems
Computing Help Desk Group
77 Massachusetts Avenue, 11-319
Cambridge, MA 02139
(617) 253-7812
debi@mit.edu
