Menu
▾
▴
MailManager/sql/v2_1 getTicketWindow.zsql,NONE,1.1 getHistoricalTickets.zsql,1.1,1.2 listHistory.zsql,1.1,1.2
|
From: Kevin C. <ke...@us...> - 2006-03-02 11:38:59
|
Update of /cvsroot/mailmanager/MailManager/sql/v2_1 In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv25935/sql/v2_1 Modified Files: getHistoricalTickets.zsql listHistory.zsql Added Files: getTicketWindow.zsql Log Message: Changes for the QueueReports The QueueReport class can now calculate the avg/max duration of tickets in the queue at any given point. Index: getHistoricalTickets.zsql =================================================================== RCS file: /cvsroot/mailmanager/MailManager/sql/v2_1/getHistoricalTickets.zsql,v retrieving revision 1.1 retrieving revision 1.2 diff -u -d -r1.1 -r1.2 --- getHistoricalTickets.zsql 6 Feb 2006 18:22:33 -0000 1.1 +++ getHistoricalTickets.zsql 2 Mar 2006 11:38:53 -0000 1.2 @@ -17,6 +17,7 @@ SELECT FROM <dtml-var schema>mm_ticket WHERE <dtml-sqltest sqv_message_id column="message_id" type="int"> +; SELECT id AS ticket_id, assigned, CURRENT_TIMESTAMP AS itemdate FROM lwtestkev10.mm_ticket @@ -34,3 +35,25 @@ <dtml-comment> Now obtain </dtml-comment> + + +SELECT + FROM <dtml-var schema>mm_ticket + WHERE <dtml-sqltest sqv_message_id column="message_id" type="int"> + +SELECT id AS ticket_id, assigned, CURRENT_TIMESTAMP AS itemdate + FROM lwtestkev10.mm_ticket + WHERE CURRENT_TIMESTAMP < '2006-02-05' + + UNION ( + SELECT + ticket_id, assigned, change_date AS itemdate + FROM lwtestkev10.mm_history + WHERE change_date < '2006-02-07' + ) +ORDER BY ticket_id +; + + + + --- NEW FILE: getTicketWindow.zsql --- <dtml-comment> title:Get a list of tickets in a certain state for a range of dates connection_id: mailmanager_db max_rows:0 arguments:sqv_from_date sqv_to_date sqv_assigned sqv_state This method is specifically used for the queue reports, in order to find tickets in the queued state at a given point of time. For each ticket there is also a calculated field (duration) which covers how long the ticket was in the given state/assignment in the given window. Only tickets which are at some point in the given state/assignment in the window are returned. </dtml-comment> <dtml-comment> This method works backwards from what would be intuitive. We first find the state of tickets at the end date, by combining the mm_ticket and mm_history table. We find either the first history item after the end date, or alternative the current information from mm_ticket, for each ticket in the database. This allows us to find the Current Ticket Status Ticket History End of Window | | s | h------|-----s h--|---------|-----s s : current status from mm_ticket -----|---------|--h s h : history item from mm_history | | h-h s - : point at which a ticket is in -----|---------|-----s the given assignment/state And history items in the window are them used to calculate the duration, or alternatively the duration is the entire period. The SQL goes along the following lines Select the closest history item from past the end window date, so we know the current state of the the ticket. We then join this with the ticket table to get the state of tickets which are not affected by history at this point. We need to use DISTINCT in this query to restrict the result to the last ticket item. This appears to be reproducable in oracle in mssql using a rework of the query. Search the web for more info when porting. This is turning into a rather messy operation. We need to do this windowing for each attribute type we are interested in. For now, this method just deals with assignments! </dtml-comment> SELECT curr_assigned AS assigned, curr_state AS state, curr_date AS date, id FROM ( SELECT <dtml-var schema>mm_ticket.id, COALESCE(history.assigned, <dtml-var schema>mm_ticket.assigned) AS curr_assigned, COALESCE(history.state, <dtml-var schema>mm_ticket.state) AS curr_state, COALESCE(history.last_date, <dtml-var sql_now>) AS curr_date FROM <dtml-var schema>mm_ticket LEFT JOIN ( SELECT DISTINCT ON (<dtml-var schema>mm_history.ticket_id) <dtml-var schema>mm_history.ticket_id AS id, <dtml-var schema>mm_history.assigned, <dtml-var schema>mm_history.state, <dtml-var schema>mm_history.change_date AS last_date FROM <dtml-var schema>mm_history WHERE <dtml-var schema>mm_history.change_date > <dtml-sqlvar sqv_from_date type="nb"> AND assigned IS NOT NULL ORDER BY <dtml-var schema>mm_history.ticket_id, <dtml-var schema>mm_history.change_date ) AS history USING(id) ) AS currinfo <dtml-sqlgroup where> <dtml-sqltest sqv_assigned column="curr_assigned" type="nb" optional> <dtml-and> <dtml-sqltest sqv_state column="curr_state" type="nb" optional> </dtml-sqlgroup> ; Index: listHistory.zsql =================================================================== RCS file: /cvsroot/mailmanager/MailManager/sql/v2_1/listHistory.zsql,v retrieving revision 1.1 retrieving revision 1.2 diff -u -d -r1.1 -r1.2 --- listHistory.zsql 16 Jan 2006 16:56:13 -0000 1.1 +++ listHistory.zsql 2 Mar 2006 11:38:53 -0000 1.2 @@ -2,8 +2,40 @@ title:List users connection_id: mailmanager_db max_rows:0 -arguments:sqv_ticket_id +arguments:sqv_ticket_id sqv_from_date sqv_to_date sqv_reverse_history sqv_limit sqv_active_field + +Note that sqv_limit will mean some modifications are needed for porting to MSSQL +and Oracle. Check the web for more info. + +sqv_active_field is used to denote a field to test for non null. This is used +in the Queue reporting code. + </dtml-comment> + + SELECT * FROM <dtml-var schema>mm_history -WHERE <dtml-sqltest sqv_ticket_id column="ticket_id" type="int"> + + <dtml-sqlgroup where> + <dtml-sqltest sqv_ticket_id column="ticket_id" type="int" optional> + <dtml-and> + <dtml-if sqv_from_date> + <dtml-sqltest sqv_from_date type="nb" column="change_date" op="gt" optional> + </dtml-if> + <dtml-and> + <dtml-if sqv_to_date> + <dtml-sqltest sqv_to_date type="nb" column="change_date" op="lt" optional> + </dtml-if> + <dtml-and> + <dtml-if sqv_active_field> + <dtml-var sqv_active_field> IS NOT NULL + </dtml-if> + </dtml-sqlgroup> + ORDER BY change_date + <dtml-if sqv_reverse_history> + DESC + </dtml-if> + +<dtml-if sqv_limit> + LIMIT <dtml-var sqv_limit> +</dtml-if> |