Calculate how long issue is in current status

Added by Adam Pfeiffer over 3 years ago

Hello,
We just rolled out redmine to our organization and we love it! There is one critical feature we need that I hope is already implemented and I am just not seeing it in the documentation.

We need to know how long each issues that is open has been in its current state. I have created a database query to pull this information, but I would really like to be able to access this information via the web interface.

The ideal solution would be to have another 2 columns under options when performing a filter called "Time in Status", "Last status change".

"Time in Status" would calculate the days it has been in the current status
"Last Status Change" would be a date and time stamp of the last time the status was changed.

Does anyone know of a plugin or method which I can get this type of data today? I would greatly appreciate the help. I searched all of the available plugins and found none that met my specifications. There are a few plugins that will allow me to search on status changes and when they took place, but the implementation isn't what we need.

If none exist, I will try my hand at creating a plugin. I am not certain I have the ability to add new columns to the filter options via a plugin, but I will research that if all other routes come to a dead end.

Thanks much

Replies (13)

RE: Calculate how long issue is in current status - Added by Kush Suryavanshi over 3 years ago

Hi Adam,
May be computed custom field might be able to achieve some of this functionality? Its worth a try.

As for adding a field to filter, we were able to do that for project description here - How to add additional information to issues list?

We didn't make a plugin though. So not sure if this is possible with plugin but manipulating the codebase maybe able to give you the ability to achieve what you want.

RE: Calculate how long issue is in current status - Added by Adam Pfeiffer over 3 years ago

Thanks so much for the information. I am going to try to add a custom column as they do in the link you provided. I will provide the solution if I get it working.

Thanks

RE: Calculate how long issue is in current status - Added by Kush Suryavanshi over 3 years ago

Welcome. Sounds good. Good luck. Also check this plugin to see if it can be useful at least partially https://www.redmine.org/plugins/computed_custom_field

RE: Calculate how long issue is in current status - Added by Adam Pfeiffer over 3 years ago

I have take a look at computered_custom_field and I don't think we have access to the data that I need. I have spend a good 6 hours looking through documentation on plugins and hooks and I am leaning toward a plugin.

Here is what I envision as the easiest solution:
1. Create a new table called issue_last_status_change with two fields:
- id => This will be the same as issues.id
- date => type of datetime and this would be updated when an issue is created and updated when the status is changed for the issue

I am having trouble determining how I can enhance the issue save to update the date in the new table when status changes. Any further helps would be appreciated.

Thanks much

RE: Calculate how long issue is in current status - Added by Kush Suryavanshi over 3 years ago

1) I am not a database expert but writing a trigger for changes in status column might achieve this.
2) Or you probably need to extend functionality of issues_controller.Rob - Specifically, you may need to look at save_issue_with_child_records

RE: Calculate how long issue is in current status - Added by Kush Suryavanshi over 3 years ago

By any chance, did you get this implemented? If so, please share the code as it will be useful for me too. Thanks.

RE: Calculate how long issue is in current status - Added by Adam Pfeiffer over 3 years ago

SOLVED!!!!

Documenting the steps I took to be able to extend the Issue controller. It didn't work for me until I started from scratch to write up this post. Then, of course, murphy kicked in and it actually worked for me.

1. Created a new plugin using this link: http://www.redmine.org/projects/redmine/wiki/Plugin_Tutorial
- export RAILS_ENV="production"
- bundle exec ruby bin/rails generate redmine_plugin adam_plugin
create plugins/adam_plugin/app
create plugins/adam_plugin/app/controllers
create plugins/adam_plugin/app/helpers
create plugins/adam_plugin/app/models
create plugins/adam_plugin/app/views
create plugins/adam_plugin/db/migrate
create plugins/adam_plugin/lib/tasks
create plugins/adam_plugin/assets/images
create plugins/adam_plugin/assets/javascripts
create plugins/adam_plugin/assets/stylesheets
create plugins/adam_plugin/config/locales
create plugins/adam_plugin/test
create plugins/adam_plugin/test/fixtures
create plugins/adam_plugin/test/unit
create plugins/adam_plugin/test/functional
create plugins/adam_plugin/test/integration
create plugins/adam_plugin/README.rdoc
create plugins/adam_plugin/init.rb
create plugins/adam_plugin/config/routes.rb
create plugins/adam_plugin/config/locales/en.yml
create plugins/adam_plugin/test/test_helper.rb
2. Created issue_patch.rb using these directions: http://www.redmine.org/projects/redmine/wiki/Plugin_Internals
- cd plugins/adam_plugin/lib/
- vim issue_patch.rb and put the following in the file

require_dependency 'issue'

# Patches Redmine's Issues dynamically.  Adds a relationship 
# Issue +belongs_to+ to Deliverable
module IssuePatch
  def self.included(base) # :nodoc:
    base.extend(ClassMethods)

    base.send(:include, InstanceMethods)

    # Same as typing in the class 
    base.class_eval do
      unloadable # Send unloadable so it will not be unloaded in development
      belongs_to :deliverable

    end

  end

  module ClassMethods

  end

  module InstanceMethods
    # Wraps the association to get the Deliverable subject.  Needed for the 
    # Query and filtering
    def deliverable_subject
      unless self.deliverable.nil?
        return self.deliverable.subject
      end
    end
  end    
end

# Add module to Issue
Issue.send(:include, IssuePatch)

- cd ..; vim init.rb and then put the following in the init.rb file:

require 'redmine'

require_dependency 'issue_patch'

Redmine::Plugin.register :adam_plugin do
  name 'Adam Plugin plugin'
  author 'Author name'
  description 'This is a plugin for Redmine'
  version '0.0.1'
  url 'http://example.com/path/to/plugin'
  author_url 'http://example.com/about'
end

3. installed the plugin
- cd /opt/bitnami/
- ./use_redmine
- cd /opt/bitnami/apps/redmine/htdocs/
- root@ubuntu:/opt/bitnami/apps/redmine/htdocs# bundle install --without development test postgresql sqlite --no-deployment
Don't run Bundler as root. Bundler can ask for sudo if it is needed, and installing your bundle as root will break this application for all non-root users on this
machine.
Resolving dependencies...
Using rake 10.5.0
Using i18n 0.7.0
Using json 1.8.3
Using minitest 5.8.4
Using thread_safe 0.3.5
Using tzinfo 1.2.2
Using activesupport 4.2.5.2
Using builder 3.2.2
Using erubis 2.7.0
Using mini_portile2 2.0.0
Using nokogiri 1.6.7.2
Using rails-deprecated_sanitizer 1.0.3
Using rails-dom-testing 1.0.7
Using loofah 2.0.3
Using rails-html-sanitizer 1.0.3
Using actionview 4.2.5.2
Using rack 1.6.4
Using rack-test 0.6.3
Using actionpack 4.2.5.2
Using globalid 0.3.6
Using activejob 4.2.5.2
Using mime-types 2.99.1
Using mail 2.6.3
Using actionmailer 4.2.5.2
Using actionpack-action_caching 1.1.1
Using actionpack-xml_parser 1.0.2
Using activemodel 4.2.5.2
Using arel 6.0.3
Using activerecord 4.2.5.2
Using addressable 2.4.0
Using bundler 1.10.4
Using coderay 1.1.1
Using concurrent-ruby 1.0.0
Using css_parser 1.3.7
Using htmlentities 4.3.1
Using thor 0.19.1
Using railties 4.2.5.2
Using jquery-rails 3.1.4
Using mysql2 0.4.2
Using net-ldap 0.12.1
Using protected_attributes 1.1.3
Using ruby-openid 2.3.0
Using rack-openid 1.4.2
Using sprockets 3.5.2
Using sprockets-rails 3.0.0
Using rails 4.2.5.2
Using rbpdf-font 1.19.0
Using rbpdf 1.19.0
Using redcarpet 3.3.4
Using request_store 1.0.5
Using rmagick 2.15.4
Using roadie 3.1.0
Using roadie-rails 1.1.0
Bundle complete! 30 Gemfile dependencies, 53 gems now installed.
Gems in the groups development, test, postgresql, sqlite and SQLite were not installed.
Use `bundle show [gemname]` to see where a bundled gem is installed.
- root@ubuntu:/opt/bitnami/apps/redmine/htdocs# bundle install --without development test postgresql sqlite --deployment
Don't run Bundler as root. Bundler can ask for sudo if it is needed, and installing your bundle as root will break this application for all non-root users on this
machine.
Using rake 10.5.0
Using i18n 0.7.0
Using json 1.8.3
Using minitest 5.8.4
Using thread_safe 0.3.5
Using tzinfo 1.2.2
Using activesupport 4.2.5.2
Using builder 3.2.2
Using erubis 2.7.0
Using mini_portile2 2.0.0
Using nokogiri 1.6.7.2
Using rails-deprecated_sanitizer 1.0.3
Using rails-dom-testing 1.0.7
Using loofah 2.0.3
Using rails-html-sanitizer 1.0.3
Using actionview 4.2.5.2
Using rack 1.6.4
Using rack-test 0.6.3
Using actionpack 4.2.5.2
Using globalid 0.3.6
Using activejob 4.2.5.2
Using mime-types 2.99.1
Using mail 2.6.3
Using actionmailer 4.2.5.2
Using actionpack-action_caching 1.1.1
Using actionpack-xml_parser 1.0.2
Using activemodel 4.2.5.2
Using arel 6.0.3
Using activerecord 4.2.5.2
Using addressable 2.4.0
Using coderay 1.1.1
Using concurrent-ruby 1.0.0
Using css_parser 1.3.7
Using htmlentities 4.3.1
Using thor 0.19.1
Using railties 4.2.5.2
Using jquery-rails 3.1.4
Using mysql2 0.4.2
Using net-ldap 0.12.1
Using protected_attributes 1.1.3
Using ruby-openid 2.3.0
Using rack-openid 1.4.2
Using bundler 1.10.4
Using sprockets 3.5.2
Using sprockets-rails 3.0.0
Using rails 4.2.5.2
Using rbpdf-font 1.19.0
Using rbpdf 1.19.0
Using redcarpet 3.3.4
Using request_store 1.0.5
Using rmagick 2.15.4
Using roadie 3.1.0
Using roadie-rails 1.1.0
Bundle complete! 30 Gemfile dependencies, 53 gems now installed.
Gems in the groups development, test, postgresql, sqlite and SQLite were not installed.
Bundled gems are installed into ./vendor/bundle.
- root@ubuntu:/opt/bitnami/apps/redmine/htdocs# ruby bin/rake redmine:plugins RAILS_ENV=production
Migrating adam_plugin (Adam Plugin plugin)...
Migrating computed_custom_field (Computed custom field)...
- /opt/bitnami/ctlscript.sh restart (waited for the restart)

4. go into rails console
- cd /opt/bitnami/apps/redmine/htdocs
- bin/rails console production
- irb(main):001:0> i = Issue.find(1)
- irb(main):003:0> puts i.methods.grep(/subject/)
deliverable_subject
subject
subject=
subject_before_type_cast
subject_came_from_user?
subject?
subject_changed?
subject_change
subject_will_change!
subject_was
reset_subject!
restore_subject!
=> nil

WHOA, IT WORKED THIS TIME....!!!!

Do the happy dance, I can start to write code now :-)

RE: Calculate how long issue is in current status - Added by Adam Pfeiffer over 3 years ago

Kush Suryavanshi wrote:

By any chance, did you get this implemented? If so, please share the code as it will be useful for me too. Thanks.

I am still working on the implementation. I am not certain I can share my code to the community, I need to discuss with my manager since this is being done on work time.

Thanks

RE: Calculate how long issue is in current status - Added by Kush Suryavanshi over 3 years ago

Hello Adam,
No worries :). Glad its working for you and thanks for your reply.I will try and go the plugin route

RE: Calculate how long issue is in current status - Added by Adam Pfeiffer over 3 years ago

Adam Pfeiffer wrote:

Hello,
We just rolled out redmine to our organization and we love it! There is one critical feature we need that I hope is already implemented and I am just not seeing it in the documentation.

We need to know how long each issues that is open has been in its current state. I have created a database query to pull this information, but I would really like to be able to access this information via the web interface.

The ideal solution would be to have another 2 columns under options when performing a filter called "Time in Status", "Last status change".

"Time in Status" would calculate the days it has been in the current status
"Last Status Change" would be a date and time stamp of the last time the status was changed.

Does anyone know of a plugin or method which I can get this type of data today? I would greatly appreciate the help. I searched all of the available plugins and found none that met my specifications. There are a few plugins that will allow me to search on status changes and when they took place, but the implementation isn't what we need.

If none exist, I will try my hand at creating a plugin. I am not certain I have the ability to add new columns to the filter options via a plugin, but I will research that if all other routes come to a dead end.

Thanks much

So, I have the backend working, but now I am having trouble adding to the views. In order to implement this feature, I have done the following:
1. Added new field to issues called last_status_change
2. Created a new table called statustimes:

+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| id                | int(11) | NO   | PRI | NULL    | auto_increment | # Uniq id
| issue_id          | int(11) | YES  | MUL | NULL    |                | # id from issue table
| issue_statuses_id | int(11) | YES  |     | NULL    |                | # id from issue_statuses table
| cum_t             | int(11) | YES  |     | NULL    |                | # Total time in seconds spent in this status for this issue
| itr               | int(11) | YES  |     | NULL    |                | # Number of times it has been in this status for this issue
+-------------------+---------+------+-----+---------+----------------+

3.5 Added new model:
class Statustime < ActiveRecord::Base
  belongs_to :issue
  belongs_to :issue_statuses
  unloadable
end

3. created a rake task to populate the statustimes table for each current issue (parse through the journals and journal details to get this info) and the last_status_change field in the issues table
4. Created hooks for for controller_issues_edit_after_save and controller_issues_new_after_save to update the statustimes table and the last_status_change field in the issues table
5. Created issue_patch.rb and added new functions to pull data from the statustimes table:
def days_in_status(status_to_check)
def seconds_in_cur_status
def days_in_cur_status
def itr_in_status(status_to_check)

and validated this all works

irb(main):002:0> i.days_in_status(1)
time from db: 1069
time in current status: 0
total time: 1069
=> 0.01
irb(main):003:0> i.seconds_in_cur_status
=> 183261.02
irb(main):004:0> i.days_in_cur_status
=> 2.12
irb(main):005:0> i.itr_in_status(1)
=> 2
irb(main):006:0>

Now, on the issues page (IssuesController#index) I need to add new columns to the 'Available Columns' listed under options. After a lot of head scratching. I believe that the best way (correct me if I am wrong!) is to use alias_method_chain to add my custom columns to the IssueQuery by chaining available_columns. Where I am stuck is on how to access the data from Issue.days_in_status inside of the IssueQuery in order to populate a new QueryColumn.

Any help, hints, or suggestions are hugely appreciated!

Thanks

RE: Calculate how long issue is in current status - Added by Adam Pfeiffer over 3 years ago

Going to spawn a new issue to reduce noise.

RE: Calculate how long issue is in current status - Added by Matt Thompson almost 2 years ago

Hi Adam,

I appreciate this is an old thread but it looks like you are trying to do the same as I am looking to do. Thought I'd ask here as I don't see a follow on.

Can you share the SQL you used, or the plugin you created to report this information?

Thanks in advance,
Matt

(1-13/13)