Project

General

Profile

p2r.rb

Alexei Siventsev, 2019-05-23 12:42

 
# Copyright (c) 2019 NPO Karat
# Author: Aleksei Siventsev
#=====================================================================
# Console Script for MS Project to Redmine synchronization
#=====================================================================
VER = '0.3 22/05/19'
HDR = "Console Script for MS Project to Redmine synchronization v#{VER} (c) A. Siventsev 2019"

require 'yaml'
require 'win32ole'
require 'net/http'
require 'json'
require 'date'
require './p2r_lib.rb'

puts '', HDR, ('=' * HDR.scan(/./mu).size), ''

#---------------------------------------------------------------------
# process command line arguments
#---------------------------------------------------------------------
# answer help request and exit
chk !(ARGV & %w(h H -h -H /h /H ? -? /? help -help --help)).empty?, HELP
# check execution request
DRY_RUN = (ARGV & %w(e E -e -E /e /E exec -exec --exec execute -execute --execute)).empty?
puts "DRY RUN (add -e key for actual execution)\n\n" if DRY_RUN

#---------------------------------------------------------------------
# connect to .msp
#---------------------------------------------------------------------
msg = 'Please open your MS Project file and leave it active with no dialogs open'
begin
pserver = WIN32OLE.connect 'MSProject.Application'
rescue
chk true, msg
end
$msp = pserver.ActiveProject
chk !$msp,msg
$msp_name = $msp.Name.clone.encode 'UTF-8'

#---------------------------------------------------------------------
# find and process settings task
#---------------------------------------------------------------------
settings_task = nil
(1..$msp.Tasks.Count).each do |i|
raw = $msp.Tasks(i)
if raw && raw.Name == 'Redmine Synchronization'
settings_task = raw
break
end
end
chk !settings_task, 'ERROR: task with name \'Redmine Sysncronization\' was not found in the project.'

begin
$settings = YAML.load settings_task.Notes.to_s.gsub("\r", "\n")
rescue
chk true, 'ERROR: could not extract settiings from Notes in \'Redmine Sysncronization\' task (YAML format expected)'
end

rmp_id = $settings.delete 'redmine_project_id'
missed_pars = %w(redmine_host redmine_api_key redmine_project_uuid resource_default_redmine_role_id) - $settings.keys

chk !missed_pars.empty?, "ERROR: following settings not found in 'Redmine Sysncronization' task: #{missed_pars.sort.join ', '}"

#---------------------------------------------------------------------
# check Redmine project availability
#---------------------------------------------------------------------
# 401 ERROR: not authorized bad key
# 404 not found
# if rpr_id then ERROR: suppose project has been published already
# else project is to be published
# 403 forbidden
# 200 ок
# if rpr_id then
# if prp_id == project id then OK to proceed
# else ERROR: different ids in project and redmine
# else ERROR: suppose project is to be published but found it is already published
#

$uuid = $settings['redmine_project_uuid']
project_path="/projects/#{$uuid}.json"
re = rm_request(project_path)

case re.code
when '401'
chk true, 'ERROR: not authorized by Redmine (maybe bad api key?)'
when '404'
if rmp_id # else proceed
chk true, "ERROR: suppose project '#{$uuid}' has been published already (because redmine_project_id is provided) but have not found it"
end
when '403'
chk true, "ERROR: access to project '#{$uuid}' in Redmine is forbidden, ask Redmine admin"
when '200'
begin
rmp = JSON.parse(re.body)
rescue
chk true, "ERROR: wrong reply format to '/projects/#{$uuid}.json' (JSON expected)"
end
rmp = rmp['project']
chk !rmp, "ERROR: wrong reply format to '/projects/#{$uuid}.json' ('project' key not found)"
if rmp_id
unless rmp_id == rmp['id'] # else proceed
chk true, "ERROR: Redmine project id does not comply with redmine_project_id provided in settings"
end
else
chk true, "ERROR: suppose have to create new project '#{$uuid}' (because redmine_project_id is not provided) but found the project with that uuid has been published already"
end
else
chk true, "ERROR: #{re.code} #{re.message}"
end

#---------------------------------------------------------------------
# check default tracker and role
#---------------------------------------------------------------------

if ($dflt_tracker_id = $settings['task_default_redmine_tracker_id'])
chk !$dflt_tracker_id.is_a?(Integer), "ERROR: parameter task_default_redmine_tracker_id must be integer"
trackers = rm_get '/trackers.json', 'trackers', 'ERROR: could not get Redmine tracker list'
found = false
trackers.each do |t|
if t['id'] = $dflt_tracker_id
found=true
break
end
end
chk !found, "ERROR: tracker not found for parameter task_default_redmine_tracker_id = #{$dflt_tracker_id}"
end

$dflt_role_id = $settings['resource_default_redmine_role_id']
chk !$dflt_tracker_id.is_a?(Integer), "ERROR: parameter task_default_redmine_tracker_id must be integer"
rm_get "/roles/#{$dflt_role_id}.json", 'role', "ERROR: could not get default team role resource_default_redmine_role_id=#{$dflt_role_id}"

#---------------------------------------------------------------------
# util for loading project team
#---------------------------------------------------------------------
$team = {}
def load_team
offset = 0
loop do
re = rm_request "/projects/#{$uuid}/memberships.json?offset=#{offset}"
chk (re.code != '200'), 'ERROR: could not get team list of Redmine project'
re = JSON.parse(re.body) rescue nil
chk re.nil?, 'ERROR: could not parse reply of team list request'
break if re['memberships'].empty? or re['limit'].nil?
re['memberships'].each do |m|
$team[m['user']['id']] = m
end
offset += re['limit']
end
end

#---------------------------------------------------------------------
# some utils for msp custom fields editing
#---------------------------------------------------------------------

def set_mst_url(mst, rmt_id)
url="http://#{$settings['redmine_host']}:#{$settings['redmine_port']}/issues/#{rmt_id}"
mst.HyperlinkAddress = url
return url
end

#---------------------------------------------------------------------
# task (issue) processing util
#---------------------------------------------------------------------

$rmts={} # issues processed
$rmus=[] # memberships processed

def process_issue rmp_id, mst, force_new_task = false, is_group = false

mst_name = mst.Name.clone.encode 'UTF-8'
rmt_id = mst.Hyperlink
return nil unless rmt_id =~ /^\s*\d+\s*$/ # task not marked for sync
rmt_id = rmt_id.to_i

if (rmt = $rmts[rmt_id])
return rmt # already processed
end

# process task parent:
mst_papa = mst.OutlineParent
rmt_papa = nil
unless mst_papa.UniqueID == 0 # suppose project summary task has UniqueID = 0
rmt_papa_id = mst_papa.Hyperlink
if rmt_papa_id =~ /^\s*\d+\s*$/
rmt_papa_id = rmt_papa_id.to_i
rmt_papa = $rmts[rmt_papa_id]
unless rmt_papa
rmt_papa = process_issue rmp_id, mst_papa, force_new_task, true
end
else
rmt_papa_id = 0
mst_papa.Hyperlink = '0'
rmt_papa = process_issue rmp_id, mst_papa, false, true
end

end

# check task resource appointment
# we expect not more than one synchronizable appointment
rmu_id_ok = nil
msr_ok = nil
(1..mst.Resources.Count).each do |j|
next unless msr = mst.Resources(j)
rmu_id = msr.Hyperlink
next unless rmu_id =~ /^\s*\d+\s*$/ # resource not marked for sync
chk rmu_id_ok, "ERROR: more than one sync resource for MSP task #{mst.ID} '#{mst_name}'"
rmu_id = rmu_id.to_i

if $rmus.include? rmu_id
# resource already processed
rmu_id_ok = rmu_id
msr_ok = msr
else
member = $team[rmu_id]
unless member
# Redmine user is not team member - create new membership
# check user availability
re = rm_request "/users/#{rmu_id}.json"
chk (re.code != '200'), "ERROR: Redmine user #{rmu_id} not found for resource in MSP task #{mst.ID} '#{mst_name}'"
re = JSON.parse(re.body) rescue nil
chk re.nil?, "ERROR: could not parse reply: Redmine user #{rmu_id} not found for resource in MSP task #{mst.ID} '#{mst_name}'"
# create membership
data = {user_id: rmu_id, role_ids: [$dflt_role_id]}
member = rm_create "/projects/#{$uuid}/memberships.json", 'membership', data,
"ERROR: could not create Redmine project membership for user #{rmu_id}"
puts "New membership created for user: #{rmu_id}"
$team[rmu_id] = member
end
rmu_id_ok = rmu_id
msr_ok = msr
end
end
if rmu_id_ok
rmu_name = $team[rmu_id_ok]['user']['name']
msr_name = msr_ok.Name.clone.encode 'UTF-8'
unless rmu_name == msr_name
puts "WARNING: RM user ID=#{rmu_id_ok} name '#{rmu_name}' does not correspond to MSP resource name '#{msr_name}' (task ##{rmt_id} for #{mst.ID} '#{mst_name}')"
end
end

if rmt_id == 0 || force_new_task

# create new task
unless DRY_RUN
rmt = {
project_id: rmp_id, subject: mst_name, description: "-----\nAutocreated by P2R from MSP task #{mst.ID} in MSP project #{$msp_name}\n-----\n",
start_date: mst.Start.strftime('%Y-%m-%d'), due_date: mst.Finish.strftime('%Y-%m-%d'),
assigned_to_id: rmu_id_ok, tracker_id: $dflt_tracker_id,
parent_issue_id: (rmt_papa ? rmt_papa['id'] : '')
}
rmt['estimated_hours'] = mst.Work/60 unless is_group
rmt = rm_create '/issues.json', 'issue', rmt,
"ERROR: could not create Redmine task from #{mst.ID} '#{mst_name}' for some reasons"
# write new task number to MSP
mst.Hyperlink = rmt['id']
set_mst_url mst, rmt['id']
puts "Created task Redmine ##{rmt['id']} from MSP #{mst.ID} '#{mst_name}'"

$rmts[rmt['id']] = rmt
return rmt

else
# keep task to be created
puts "Will create task #{mst.ID} '#{mst_name}'"

return nil

end

else

# update existing task
# check task availability
rmt = rm_get "/issues/#{rmt_id}.json", 'issue', "ERROR: could not find Redmine task ##{rmt_id} for #{mst.ID} '#{mst_name}'"

# check for changes
# to RM: subject - Name, parent_id - OutlineParent.Hyperlink, assigned_to_id - rmu_id_ok
# to MSP: start_date - Start, due_date - Finish, estimated_hours - Work, sum of reports - ActualWork

# collect changes to RM
changes={}
changes['assigned_to_id'] = (rmu_id_ok || '') if rmu_id_ok != (rmt['assigned_to'] ? rmt['assigned_to']['id'] : nil)
changes['subject'] = mst_name if rmt['subject'] != mst_name
rmt_papa_id_old = (rmt['parent'] ? rmt['parent']['id'] : '')
rmt_papa_id_new = (rmt_papa ? rmt_papa['id'] : '')
changes['parent_issue_id'] = rmt_papa_id_new if rmt_papa_id_new != rmt_papa_id_old

# collect changes to MSP
unless is_group
changes2={}
d = mst.Start.strftime('%Y-%m-%d')
changes2['start_date'] = rmt['start_date'] if rmt['start_date'] != d
d = mst.Finish.strftime('%Y-%m-%d')
changes2['due_date'] = rmt['due_date'] if rmt['due_date'] != d
# calculate estimate and spent hours
spent = (rmt['spent_hours'] || 0.0) * 60
changes2['spent_hours'] = spent if spent != mst.ActualWork
est = (rmt['estimated_hours'] || 0.0) * 60
if rmt['done_ratio'] > 0 && spent > 0
# we will consider priority of done ratio over estimated hours
est = spent * 100 / rmt['done_ratio']
elsif rmt['done_ratio'] == 0 && spent > 0
# done ratio is wrong
if est >= spent
# some discrepancy - we will fix done ratio in RM
changes['done_ratio'] = spent * 100 / est
else
# some error in estimate? we will ignore estimate and warn
est = nil
puts "Warning: estimated hours less than spent hours, estimate will be ignored"
end
end
changes2['estimated_hours'] = est if est && est != mst.Work
end

# apply changes to RM
if changes.empty?
puts "No changes for Task Redmine ##{rmt_id} from MSP #{mst.ID} '#{mst_name}'"
else
# apply changes
changelist = changes.keys.join(', ')
changes['notes'] = "Autoupdated by P2P at #{Time.now.strftime '%Y-%m-%d %H:%M'} (#{changelist})"
if DRY_RUN
puts "Will update task Redmine ##{rmt_id} from MSP #{mst.ID} '#{mst_name}' (#{changelist})"
else
rm_update "/issues/#{rmt['id']}.json", {issue: changes},
"ERROR: could not update Redmine task ##{rmt['id']} from #{mst.ID} '#{mst_name}' for some reasons"
rmt = rm_get "/issues/#{rmt_id}.json", 'issue', "ERROR: could not find Redmine task ##{rmt_id} for #{mst.ID} '#{mst_name}'"
puts "Updated task Redmine ##{rmt_id} from MSP #{mst.ID} '#{mst_name}' (#{changelist})"
end
end

# apply changes to MSP
unless is_group
if changes2.empty?
puts "No changes for MSP task #{mst.ID} '#{mst_name}'"
else
# apply changes
changelist2 = changes2.keys.join(', ')
if DRY_RUN
puts "Will update MSP task #{mst.ID} '#{mst_name}' from Redmine ##{rmt_id} (#{changelist2})"
else
changes2.each do |k,v|
case k
when 'start_date'; mst.Start = Time.new *(v.split /\D+/ )
when 'due_date'; mst.Finish = Time.new *(v.split /\D+/ )
when 'spent_hours'; mst.ActualWork = v
when 'estimated_hours'; mst.Work = v
end
end
puts "Updated MSP task #{mst.ID} '#{mst_name}' from Redmine ##{rmt_id} (#{changelist2})"
end
end
end
set_mst_url mst, rmt['id']

$rmts[rmt['id']] = rmt
return rmt

end

end

#---------------------------------------------------------------------
# iterate over task list
#---------------------------------------------------------------------

def process_issues rmp_id, force_new_task = false

(1..$msp.Tasks.Count).each do |i|

# check msp task
next unless mst = $msp.Tasks(i)

is_group = (mst.OutlineChildren.Count > 0)
process_issue rmp_id, mst, force_new_task, is_group

end
end

#=====================================================================
# main work cycle
#=====================================================================

settings_task.Start = Time.now unless DRY_RUN

if rmp_id
#=====================================================================
# existing Redmine project update
#---------------------------------------------------------------------

puts 'Existing Redmine project update'

else
#=====================================================================
# new Redmine project creation
#---------------------------------------------------------------------
if DRY_RUN
# project creation requested - exit on dry run
chk true, "Will create new Redmine project #{$uuid} from MSP project #{$msp_name}"
end

#---------------------------------------------------------------------
# new Redmine project create
#---------------------------------------------------------------------
rmp = {name: $msp_name, identifier: $uuid, is_public: false}
rmp = rm_create '/projects.json', 'project', rmp,
'ERROR: could not create Redmine project for some reasons'

# add rm project id to msp settings
$settings['redmine_project_id'] = rmp['id']
settings_task.Notes = YAML.dump $settings
puts "Created new Redmine project #{$uuid} ##{rmp['id']} from MSP project #{$msp_name}"

#---------------------------------------------------------------------
# add tasks to Redmine project
#---------------------------------------------------------------------

end

load_team
process_issues (rmp_id || rmp['id']), rmp_id.nil?

settings_task.Finish = Time.now unless DRY_RUN

puts "\n\n"


(2-2/3)