pax_global_header 0000666 0000000 0000000 00000000064 14122431402 0014503 g ustar 00root root 0000000 0000000 52 comment=d6e46880a9de01718fb73daff3d64c0df2f61754 intranet-sla-management-master/ 0000775 0000000 0000000 00000000000 14122431402 0017053 5 ustar 00root root 0000000 0000000 intranet-sla-management-master/README.md 0000664 0000000 0000000 00000032241 14122431402 0020334 0 ustar 00root root 0000000 0000000 # ]po[ SLA Management This package is part of ]project-open[, an open-source enterprise project management system. For more information about ]project-open[ please see: * [Documentation Wiki](https://www.project-open.com/en/) * [V5.0 Download](https://sourceforge.net/projects/project-open/files/project-open/V5.0/) * [Installation Instructions](https://www.project-open.com/en/list-installers) About ]po[ SLA Management:
This "SLA Management" package allows to define and track parameters of service level agreement contracts. It defines the notion of "Service Level Agreement (SLA)", "SLA Parameter", "SLA Indicator", "SLA Service Hours" and "ticket resolution time". # Online Reference Documentation ## Procedure Files
tcl/intranet-sla-management-procs.tcl |
callback::im_ticket_after_create::impl::im_sla_management | Callback to be executed after the creation of any ticket. | |
callback::im_ticket_after_update::impl::im_sla_management | Callback to be executed after the update of any ticket. | |
im_sla_check_time_in_service_hours | Returns 1 if the time (example: "09:55") falls within service hours (example: {09:00 20:00}) | |
im_sla_day_of_week_list | Returns a list with weekday names from 0=Su to 6=Sa | |
im_sla_management_epoch_in_service_hours | Returns 1 if the epoch falls within service hours ToDo:: Implement | |
im_sla_parameter_component | Returns a HTML component to show a list of SLA parameters with the option to add more parameters | |
im_sla_parameter_list_component | Returns a HTML component with a mix of SLA parameters and indicators. | |
im_sla_parameter_permissions | Fill the "by-reference" variables read, write and admin with the permissions of $user_id on $ticket_id | |
im_sla_parameter_status_active | ||
im_sla_parameter_status_deleted | ||
im_sla_parameter_type_default | ||
im_sla_service_hours_component | Returns a HTML component with a component to display and modify working hours for the 7 days of the week. | |
im_sla_ticket_close_resolved_tickets_sweeper | Set ticket statatus to "closed" after the ticket is in status "resolved" for a certain time. | |
im_sla_ticket_solution_time_sweeper | Calculates "resolution time" for all open tickets. | |
im_sla_ticket_solution_time_sweeper_helper | Calculates "resolution time" for all open tickets. | |
im_sla_ticket_traffic_light_sweeper_helper | Calculates the green/yellow/red status of tickets depending on solution time and SLA parameters. | |
im_ticket_priority_lookup | Takes ticket_type and ticket_status to lookup the ticket priority in the "map". | |
im_ticket_priority_map_component | Returns a HTML component with a component containing a list of ticket_type x ticket_severity => ticket_priority tuples. |
sql/postgresql/intranet-sla-management-create.sql | ||
sql/postgresql/intranet-sla-management-drop.sql |
www/ | |
new.adp | |
new.tcl | Show, create and edit a single SLA parameter |
related-objects-associate-2.adp | |
related-objects-associate-2.tcl | Associate the ticket_ids in "tid" with one of the specified objects. |
related-objects-associate.adp | |
related-objects-associate.tcl | Allow the user to create new OpenACS relationships. |
report-resolution-time-per-support-group.tcl | Resolution Time per Support Group This report shows ticket information (resolution time) for the people serving tickets. |
reports/ | |
sla-reaction-time.tcl | Show Reaction time per ticket |
sla-resolution-time.tcl | Show Resolution time per ticket |
service-hours-component.adp | |
service-hours-component.tcl | |
service-hours-save.tcl | Associate the ticket_ids in "tid" with one of the specified objects. |
sla-parameter-action.tcl | Takes commands from the /intranet-sla-management/index page or the sla-parameter-indicator-component and perform the selected action on the selected items |
sla-parameter-indicator-component.adp | |
sla-parameter-indicator-component.tcl | |
sla-parameter-list-component.adp | |
sla-parameter-list-component.tcl | |
ticket-priority-add.tcl | Add a new tuple to the priority map at the SLA |
ticket-priority-component.adp | |
ticket-priority-component.tcl | |
ticket-priority-del.tcl | Add a new tuple to the priority map at the SLA |
$err_msg\n\n$errorInfo" } # Catch errors calculating the green/yellow/red status of tickets if {[catch { append result [im_sla_ticket_traffic_light_sweeper_helper -debug_p $debug_p -ticket_id $ticket_id -limit $traffic_light_limit] } err_msg]} { global errorInfo ns_log Error "im_sla_ticket_solution_time_sweeper: traffic light status: Found error: $err_msg, $errorInfo" append result "
$err_msg\n\n$errorInfo" } # De-block the execution of this procedure for a 2nd thread nsv_incr intranet_sla_management sweeper_p -1 if {$debug_p} { ad_return_complaint 1 $result } return $result } ad_proc -public im_sla_ticket_traffic_light_sweeper_helper { {-debug_p 0} {-ticket_id ""} {-limit ""} {-reset_closed_tickets_p 0} } { Calculates the green/yellow/red status of tickets depending on solution time and SLA parameters. } { ns_log Notice "im_sla_ticket_traffic_light_sweeper_helper: starting" # --------------------------------------- # Parameters and Conditions # set green_expr "1" set yellow_expr "\$ticket_resolution_time > \[expr \$max_resolution_hours + 4]" set red_expr "\$ticket_resolution_time > \$max_resolution_hours" set green_expr [parameter::get_from_package_key -package_key "intranet-sla-management" -parameter "TrafficLightStatusTCLGreen" -default $green_expr] set yellow_expr [parameter::get_from_package_key -package_key "intranet-sla-management" -parameter "TrafficLightStatusTCLYellow" -default $yellow_expr] set red_expr [parameter::get_from_package_key -package_key "intranet-sla-management" -parameter "TrafficLightStatusTCLRed" -default $red_expr] # --------------------------------------- # Metadata # set sla_fields [util_memoize [list db_list sla_dynfields "select aa.attribute_name from acs_attributes aa, im_dynfield_attributes da where aa.attribute_id = da.acs_attribute_id and aa.object_type = 'im_sla_parameter' and da.also_hard_coded_p = 'f' order by aa.sort_order"]] set ticket_fields [util_memoize [list db_list sla_dynfields "select aa.attribute_name from acs_attributes aa, im_dynfield_attributes da where aa.attribute_id = da.acs_attribute_id and aa.object_type = 'im_ticket' order by aa.sort_order"]] # Parameter fields are "input fields" set parameter_fields [set_intersection $sla_fields $ticket_fields] # Value fields are "output fields". # Their value is determines based on a best match of the parameter fields set value_fields [set_difference $sla_fields $parameter_fields] # --------------------------------------- # Get the list of open tickets to process # set open_tickets_sql " select sla.project_id as sla_id, p.on_track_status_id, t.*, p.* from im_tickets t, im_projects p, im_projects sla where t.ticket_id = p.project_id and p.parent_id = sla.project_id and sla.project_type_id = [im_project_type_sla] and t.ticket_status_id in ([join [im_sub_categories [im_ticket_status_open]] ","]) and sla.project_status_id in ([join [im_sub_categories [im_project_status_open]] ","]) " if {"" != $ticket_id} { # Manually specified the ticket (for debugging?) set open_tickets_sql " select p.parent_id as sla_id, p.on_track_status_id, t.*, p.* from im_tickets t, im_projects p where t.ticket_id = p.project_id and t.ticket_id = :ticket_id " } else { if {$reset_closed_tickets_p} { # Set the status of all closed tickets to "" # taking into account performance. db_dml reset_closed_tickets " update im_projects set on_track_status_id = null where on_track_status_id is not null and project_id in ( select t.ticket_id from im_tickets t, im_projects p where t.ticket_id = p.project_id and p.on_track_status_id is not null and t.ticket_id not in ( select ticket_id from ($open_tickets_sql) t ) ) " } } if {"" != $limit && 0 != $limit} { append open_tickets_sql "\t\t\tLIMIT $limit\n" } # --------------------------------------- # Load the list of sla_parameters into a hash per sla_id # set sla_parameter_sql " select sla.project_id as sla_id, par.*, CASE WHEN ticket_prio_id is NULL THEN 0 ELSE 10 END as prio_score, CASE WHEN ticket_type_id is NULL THEN 0 ELSE 20 END as type_score from im_projects sla, im_sla_parameters par where par.param_sla_id = sla.project_id and sla.project_id in ( select distinct sla_id from (\n$open_tickets_sql\n) t ) " db_foreach sla_parameters $sla_parameter_sql { # set key "$sla_id-$ticket_prio_id-$ticket_type_id" set key "$sla_id" foreach field $parameter_fields { set value [expr $$field] if {"" != $value} { append key "-$value" } } # Store the value field contents in the corresponding hashes foreach field $value_fields { set cmd [list set ${field}_hash($key) [expr "\$$field"]] eval $cmd } } # --------------------------------------- # Loop through all open tickets and check for the SLA parameters of their SLA # set max_resolution_hours "" set permutations [im_report_take_all_ordered_permutations $parameter_fields] db_foreach open_tickets $open_tickets_sql { ns_log Notice "im_sla_ticket_traffic_light_sweeper_helper: #$ticket_id: ticket_type_id=$ticket_type_id, ticket_prio_id=$ticket_prio_id, ticket_resolution_time=$ticket_resolution_time" # Check if we can find the set found_p 0 foreach perm $permutations { ns_log Notice "sweeper_helper: #$ticket_id: permutation: $perm" set key "$sla_id" foreach field $perm { set value [expr $$field] if {"" != $value} { append key "-$value" } } # Check if the entry exists for the permutation of the parameter fields set field [lindex $value_fields 0] set exists_p [info exists ${field}_hash($key)] # Extract the value field values, if they exist if {$exists_p} { foreach field $value_fields { set cmd "set $field \$${field}_hash(\$key)" eval $cmd } # This breaks out of the foreach perm $permutations loop set found_p 1 break } } set debug "" foreach field $value_fields { append debug "$field=[expr "\$$field"] ," } ns_log Notice "im_sla_ticket_traffic_light_sweeper_helper: #$ticket_id: $debug" # Advance to the next open ticket if we didn't find any parameters if {!$found_p} { continue } # Now determine the color for the ticket set color "" if {[catch { if {"" != $green_expr && [expr {$green_expr}]} { set color [im_project_on_track_status_green] } } err_msg]} { ns_log Error "im_sla_ticket_traffic_light_sweeper_helper: #$ticket_id: Error evaluating green_expr=$green_expr: $err_msg" } if {[catch { if {"" != $yellow_expr && [expr {$yellow_expr}]} { set color [im_project_on_track_status_yellow] } } err_msg]} { ns_log Error "im_sla_ticket_traffic_light_sweeper_helper: #$ticket_id: Error evaluating yellow_expr=$yellow_expr: $err_msg" } if {[catch { if {"" != $red_expr && [expr {$red_expr}]} { set color [im_project_on_track_status_red] } } err_msg]} { ns_log Error "im_sla_ticket_traffic_light_sweeper_helper: #$ticket_id: Error evaluating red_expr=$red_expr: $err_msg" } # Update the ticket only if the status has changed if {$color != $on_track_status_id} { db_dml update_ticket_on_track_status " update im_projects set on_track_status_id = :color where project_id = :ticket_id " } } ns_log Notice "im_sla_ticket_traffic_light_sweeper_helper: finished" return } ad_proc -public im_sla_ticket_solution_time_sweeper_helper { {-debug_p 0} {-ticket_id ""} {-limit 100} } { Calculates "resolution time" for all open tickets. The procedure takes about a second per ticket, so the limit is set to 100 by default. } { ns_log Notice "im_sla_ticket_solution_time_sweeper_helper: starting" # Deal with timezone offsets for epoch calculation... set tz_offset_seconds [util_memoize [list db_string tz_offset "select extract(timezone from now())"]] # User to act as set current_user_id [db_string cuid "select min(user_id) from users where user_id > 0"] set limit_to_ticket_id $ticket_id # Calculate the list of "open" ticket states (when to advance the restime counter) # Exclude the status "customer_review" (no work to be done by the helpdesk) set ticket_open_states [db_list ostate " select * from im_sub_categories([im_ticket_status_open]) where im_sub_categories not in ( [im_ticket_status_customer_review] ) "] # SISLA code: ToDo: remove for production # Add "rejected" to "open" states lappend ticket_open_states [im_ticket_status_rejected] set debug_html "" set time_html "" # Returns a list with weekday names from 0=Su, 1=Mo to 6=Sa: # {Sunday Monday Tuesday Wednesday Thursday Friday Saturday} set dow_list [im_sla_day_of_week_list] # Count the number of tickets processed set total_tickets_processed 0 # Calculate a list of groups for storing resolution times per group set group_list [db_list group_list "select group_id from groups where group_id > 0 order by group_id"] # ---------------------------------------------------------------- # Get the group's labour time start and end dates during the week # ToDo: Setup a link between SLAs and group's labour time. # SISLA: "Developers" work from 9:00 until 17:00. # Time from 17:00 to service end doesn't count. set developer_group_id [db_string developer_gid "select group_id from groups where group_name = 'Linux Admins'" -default 0] set employee_labour_hours {0 {} 1 {{09:00 17:00}} 2 {{09:00 17:00}} 3 {{09:00 17:00}} 4 {{09:00 17:00}} 5 {{09:00 17:00}} 6 {}} array set employee_labour_hours_dow_hash $employee_labour_hours set employee_labour_hours_list [list] foreach dow [lsort [array names employee_labour_hours_dow_hash]] { lappend employee_labour_hours_list $employee_labour_hours_dow_hash($dow) } set labour_hours_hash($developer_group_id) $employee_labour_hours_list set labour_hours_hash(463) $employee_labour_hours_list # ---------------------------------------------------------------- # Get the list of SLAs to work with. # Include all open tickets or tickets with dirty resolution_time. # set slas_with_open_tickets [db_list sla_list " select p.project_id from im_projects p where p.project_type_id = [im_project_type_sla] and exists ( select * from im_tickets t, im_projects tp where t.ticket_id = tp.project_id and tp.parent_id = p.project_id and ( ticket_status_id in ([join [im_sub_categories [im_ticket_status_open]] ","]) OR ticket_resolution_time_dirty is NULL ) ) "] # Debugging: Only calculate a single ticket if {"" != $limit_to_ticket_id} { set slas_with_open_tickets [db_list sla_list " select p.parent_id from im_projects p where p.project_id = :limit_to_ticket_id "] } # ---------------------------------------------------------------- # Loop through all SLAs ns_log Notice "im_sla_ticket_solution_time_sweeper: Looping through all SLAs with open tickets" foreach sla_id $slas_with_open_tickets { if {$debug_p} { ns_log Notice "im_sla_ticket_solution_time: sla_id=$sla_id" append debug_html "
Epoch | Date | Event | Duration Seconds |
Count Duration? |
Last Queue |
Queue | Resolution Seconds |
Resolution Minutes |
Resolution Hours |
Resolution time per Group |
[expr {round(100.0 * $e) / 100.0}] | [im_date_epoch_to_ansi $e] [im_date_epoch_to_time $e] | [expr {round(100.0 * $duration_epoch) / 100.0}] | $count_duration_p | $last_queue_name | $queue_name | [expr {round(100.0 * $resolution_seconds) / 100.0}] | [expr {round(100.0 * $resolution_seconds / 60.0) / 100.0 }] | [expr {round(100.0 * $resolution_seconds / 3600.0) / 100.0}] | $restime_html |
<%= [im_box_header [lang::message::lookup "" intranet-sla-management.SAL_Parameter_Details "SLA Parameter Details"]] %>
|
<%= [im_component_bay right] %> |
$err_msg" ad_script_abort } ad_returnredirect $return_url } # --------------------------------------------------------------- # Create the Form # --------------------------------------------------------------- set form_id "form" ad_form \ -name $form_id \ -mode $form_mode \ -export "param_sla_id return_url" \ -form { param_id:key {param_name:text(text) {label "[lang::message::lookup {} intranet-sla-management.SLA_Parameter_Name Name]"}} {param_type_id:text(im_category_tree) {label "[lang::message::lookup {} intranet-sla-management.SLA_Parameter_Type Type]"} {custom {category_type "Intranet SLA Parameter Type" translate_p 1 package_key intranet-sla-management include_empty_p 0}} } {param_note:text(textarea),optional {label "[lang::message::lookup {} intranet-sla-management.SLA_Parameter_Note Note]"} {html {cols 40} {rows 8}} } } # --------------------------------------------- # Add DynFields to the form # --------------------------------------------- set dynfield_param_type_id "" if {[info exists param_type_id]} { set dynfield_param_type_id $param_type_id} set dynfield_param_id "" if {[info exists param_id]} { set dynfield_param_id $param_id } # Add DynFields to the form im_dynfield::append_attributes_to_form \ -form_display_mode $form_mode \ -object_subtype_id $dynfield_param_type_id \ -object_type "im_sla_parameter" \ -form_id $form_id \ -object_id $dynfield_param_id # ------------------------------------------------------------------ # Param Action # ------------------------------------------------------------------ set pid [value_if_exists param_id] set param_action_html " " if {!$edit_param_status_p} { set param_action_html "" } # --------------------------------------------------------------- # Define Form Actions # --------------------------------------------------------------- ad_form -extend -name $form_id \ -select_query { select * from im_sla_parameters where param_id = :param_id } -new_data { set param_note [string trim $param_note] set duplicate_param_sql " select count(*) from im_sla_parameters where param_sla_id = :param_sla_id and param_name = :param_name " if {[db_string dup $duplicate_param_sql]} { ad_return_complaint 1 "[lang::message::lookup "" intranet-sla-management.Duplicate_parameter "Duplicate parameter"]:
<%= [lang::message::lookup "" intranet-sla-management.Associate_Params_Msg "This page allows you to associated your params with other objects."] %>