Help with adding new columns in my plugin
Added by Adam Pfeiffer over 8 years ago
I have created a new plugin and patched both Issue and IssueQuery as well as created a new model to store the new data. This is a follow up to this post:
http://www.redmine.org/boards/2/topics/49747?r=49925#message-49925.
I have gotten all of the code written to update my new model statustimes table and extended Issue to have functions to pull out all the needed data. I can get the data to display using alias_method_chain in the IssueQueryPatch and adding new QueryColumns with lines like this (see full code at the bottom of the post):
base_columns.push(QueryColumn.new(:status_time_1)) unless base_columns.detect{ |c| c.name == :status_time_1 }
base_columns.push(QueryColumn.new(:status_time_2)) unless base_columns.detect{ |c| c.name == :status_time_2 }
Here are my questions:
1. Why do I have to have a function named status_time_1 and a label in en.yml with the name status_time_1?
2. Is there a way that I can call the new function days_in_status(status_id) that I have added to issue instead of having to hard code funtion calls?
3. How do I make this sortable? When I try this:
base_columns.push(QueryColumn.new(:status_time_1, :sortable => "#{Statustime.table_name}.cum_t")) unless base_columns.detect{ |c| c.name == :status_time_1 }
the sql statement fails as it doesn't know to look in the Statustime table for an attribue with the name cum_t. I get this error when I try to sort by that column:
Started GET "/projects/adam/issues?c%5B%5D=tracker&c%5B%5D=status&c%5B%5D=priority&c%5B%5D=subject&c%5B%5D=assigned_to&c%5B%5D=updated_on&c%5B%5D=status_time_1&f%5B%5D=status_id&f%5B%5D=&group_by=&op%5Bstatus_id%5D=o&set_filter=1&sort=status_time_1%2Cid%3Adesc&utf8=%E2%9C%93" for 172.16.114.126 at 2016-04-20 23:55:08 +0000
Processing by IssuesController#index as HTML
Parameters: {"c"=>["tracker", "status", "priority", "subject", "assigned_to", "updated_on", "status_time_1"], "f"=>["status_id", ""], "group_by"=>"", "op"=>{"status_id"=>"o"}, "set_filter"=>"1", "sort"=>"status_time_1,id:desc", "utf8"=>"✓", "project_id"=>"adam"}
Current user: user (id=1)
Query::StatementInvalid: Mysql2::Error: Unknown column 'statustimes.cum_t' in 'order clause': SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issues`.`last_status_change` AS t0_r24, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`default_version_id` AS t2_r13, `users`.`id` AS t3_r0, `users`.`login` AS t3_r1, `users`.`hashed_password` AS t3_r2, `users`.`firstname` AS t3_r3, `users`.`lastname` AS t3_r4, `users`.`admin` AS t3_r5, `users`.`status` AS t3_r6, `users`.`last_login_on` AS t3_r7, `users`.`language` AS t3_r8, `users`.`auth_source_id` AS t3_r9, `users`.`created_on` AS t3_r10, `users`.`updated_on` AS t3_r11, `users`.`type` AS t3_r12, `users`.`identity_url` AS t3_r13, `users`.`mail_notification` AS t3_r14, `users`.`salt` AS t3_r15, `users`.`must_change_passwd` AS t3_r16, `users`.`passwd_changed_on` AS t3_r17, `trackers`.`id` AS t4_r0, `trackers`.`name` AS t4_r1, `trackers`.`is_in_chlog` AS t4_r2, `trackers`.`position` AS t4_r3, `trackers`.`is_in_roadmap` AS t4_r4, `trackers`.`fields_bits` AS t4_r5, `trackers`.`default_status_id` AS t4_r6, `enumerations`.`id` AS t5_r0, `enumerations`.`name` AS t5_r1, `enumerations`.`position` AS t5_r2, `enumerations`.`is_default` AS t5_r3, `enumerations`.`type` AS t5_r4, `enumerations`.`active` AS t5_r5, `enumerations`.`project_id` AS t5_r6, `enumerations`.`parent_id` AS t5_r7, `enumerations`.`position_name` AS t5_r8, `issue_categories`.`id` AS t6_r0, `issue_categories`.`project_id` AS t6_r1, `issue_categories`.`name` AS t6_r2, `issue_categories`.`assigned_to_id` AS t6_r3, `versions`.`id` AS t7_r0, `versions`.`project_id` AS t7_r1, `versions`.`name` AS t7_r2, `versions`.`description` AS t7_r3, `versions`.`effective_date` AS t7_r4, `versions`.`created_on` AS t7_r5, `versions`.`updated_on` AS t7_r6, `versions`.`wiki_page_title` AS t7_r7, `versions`.`status` AS t7_r8, `versions`.`sharing` AS t7_r9 FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN `issue_categories` ON `issue_categories`.`id` = `issues`.`category_id` LEFT OUTER JOIN `versions` ON `versions`.`id` = `issues`.`fixed_version_id` WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND projects.id = 1) ORDER BY statustimes.cum_t ASC, issues.id DESC LIMIT 25 OFFSET 0
I have a strong feeling I have architected part of this incorrectly. I am thinking my functions should be in my a controller for Statustimes, but would love advice. This is my first time using ruby and/or rails, so this is a huge learning process for me.
Thanks in advance for any help you offer!
Below is all of the code:
root@ubuntu:/opt/bitnami/apps/redmine/htdocs/plugins/adam_plugin3# more init.rb
require 'redmine'
Redmine::Plugin.register :adam_plugin3 do
name 'Adam Plugin3 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
require_dependency 'issue_patch'
require_dependency 'issue_query_patch'
require_dependency 'hooks/controller_issues_new_after_save'
require_dependency 'hooks/controller_issues_edit_after_save'
require_dependency 'issue'
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
has_many :Statustimes
end
end
module ClassMethods
end
module InstanceMethods
def days_in_status(status_to_check)
# Get current status id
current_status = self.status_id
# Calculate number of seconds in a day
day_in_secs = (60 * 60 * 24)
# Time spent in the current status.
time_in_current_status = 0
# Time spend in the status as found in the db
time_in_status = 0
# If the current_status is the status we are checking, then we need to add how long it
# has been in the current status to the value that is stored in db. The reason for this
# is that the db fields are updated after a change to the status.
if (current_status == status_to_check)
time_in_current_status = self.seconds_in_cur_status
end
st = self.Statustimes.find_by(issue_id: self.id, issue_statuses_id: status_to_check)
if !st.nil?
time_in_status = st.cum_t
end
time_total = time_in_status + time_in_current_status
puts "time from db: #{time_in_status}"
puts "time in current status: #{time_in_current_status}"
puts "total time: #{time_total}"
return (time_total.to_f / day_in_secs.to_f).round(2)
end
def seconds_in_cur_status
time = (Time.now - self.last_status_change).to_f.round(2)
return time
end
def days_in_cur_status
day_in_secs = (60 * 60 * 24)
return (seconds_in_cur_status/day_in_secs).to_f.round(2)
end
def itr_in_status(status_to_check)
# Current status for this issue
current_status = self.status_id
# Number of iterations this issue has been in the status being checked
itr = 0
st = self.Statustimes.find_by(issue_id: self.id, issue_statuses_id: status_to_check)
if !st.nil?
itr = st.itr
end
# The db increments the iterations after a change. If we are in the
# status that is being checked, we need to add 1 to the iterations
if (current_status == status_to_check)
itr = itr + 1
end
return itr
end
def status_time_1
return days_in_status(1)
end
def status_time_2
return days_in_status(2)
end
def status_time_3
return days_in_status(3)
end
def status_time_4
return days_in_status(4)
end
def status_time_5
return days_in_status(5)
end
def status_time_6
return days_in_status(6)
end
end
end
# Add module to Issue
Issue.send(:include, IssuePatch)
require 'issue_query'
module IssueQueryPatch
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
alias_method_chain :available_columns, :status_times
end
end
module InstanceMethods
def available_columns_with_status_times
base_columns = available_columns_without_status_times
#base_columns.push(QueryColumn.new(:status_time_1, :sortable => "Issue.find(#{Issue.table_name}.id).days_in_status(1)")) unless base_columns.detect{ |c| c.name == :status_time_1 }
base_columns.push(QueryColumn.new(:status_time_1)) unless base_columns.detect{ |c| c.name == :status_time_1 }
base_columns.push(QueryColumn.new(:status_time_2)) unless base_columns.detect{ |c| c.name == :status_time_2 }
base_columns.push(QueryColumn.new(:status_time_3)) unless base_columns.detect{ |c| c.name == :status_time_3 }
base_columns.push(QueryColumn.new(:status_time_4)) unless base_columns.detect{ |c| c.name == :status_time_4 }
base_columns.push(QueryColumn.new(:status_time_5)) unless base_columns.detect{ |c| c.name == :status_time_5 }
base_columns.push(QueryColumn.new(:status_time_6)) unless base_columns.detect{ |c| c.name == :status_time_6 }
return base_columns
end
end
end
## Add module to IssueQueryPatch
IssueQuery.send(:include, IssueQueryPatch)
root@ubuntu:/opt/bitnami/apps/redmine/htdocs/plugins/adam_plugin3/lib/hooks# more *
::::::::::::::
controller_issues_edit_after_save.rb
::::::::::::::
module RedmineAdamPlugin3
module Hooks
class ControllerIssuesEditAfterSave < Redmine::Hook::ViewListener
def controller_issues_edit_after_save(context={})
# Pull the issue from the context
i = context[:issue]
# Grab the journals in the issue
journals = i.journals
# Will hold the statustime data
#statustime = nil
if journals.nil?
# do something her
else
j = journals.last
j.visible_details.to_a.each do |jd|
# If we enter this block of code, then a status change did occur on this edit
# so we need to update all the appropriate fields
if (jd.prop_key == "status_id")
# Find the statustime data for this status we are exiting.
statustime = i.Statustimes.find_by(issue_id: i.id, issue_statuses_id: jd.old_value)
# If we dn't have one, we need to create a new entry and then save it
# If we do have one, we need to update both cum_t and itr
if (statustime.nil?)
diff_in_seconds = Time.now - i.last_status_change
statustime = i.Statustimes.build(issue_statuses_id: jd.old_value, cum_t: diff_in_seconds, itr: 1)
else
# Get how long we have been in this status
time_in_status = i.seconds_in_cur_status
# Add the time we have been in this status to the total time in this status
statustime.cum_t = statustime.cum_t + time_in_status
# Increment the iterations for this statustime
statustime.itr = statustime.itr + 1
end
# Update issue.last_status_change to be now
statustime.save
# Save statustime and issue changes to the DB
i.last_status_change = Time.now()
i.save
end
end
end
return ''
end
end
end
end
::::::::::::::
controller_issues_new_after_save.rb
::::::::::::::
module RedmineAdamPlugin3
module Hooks
class ControllerIssuesNewAfterSave < Redmine::Hook::ViewListener
def controller_issues_new_after_save(context={})
puts "running adams hook"
i = context[:issue]
i.last_status_change = Time.now()
i.save
return ''
end
end
end
end