class Ucert::Sage100Tracker
Class to handle Sage 100 ERP user entitlement reprot
Attributes
file_user_map[RW]
Class constant variables
sy_2_ad_user[R]
sy_user_details_report[RW]
Class constant variables
sy_user_entitlement[R]
sy_user_role_details_report[RW]
Class constant variables
verbose[RW]
Class constant variables
Public Class Methods
new(params ={})
click to toggle source
Instance default variables
# File lib/ucert/sage100_tracker.rb, line 19 def initialize (params ={}) @verbose=params.fetch(:verbose, false) # sage 100 user entitlement details report in xlsx format, generated by accessing Sage 100 under production environtment # Once logon, go to "Report -> User Report -> select "All", "Datat Only Export"; choose "Role and Preferences Details" then # press "Print" to open Export window. # Under "Export" menu, choose "Microsoft Excel Workbook Data-only" as Format, "Disk" as Destination, then press OK button to # generate and save the report; refer to the screenshot for details. # # Note 1/12/2015: # => I did not like the complexity of the XML generated automatically by the Crytal Report module. IMHO the Excel format # is a cleaner option. # => No need to parse the user role report as the information is already presetned in the user report above @sy_user_details_report = File.dirname(__FILE__)+"/../../data/sage100/SY_UserReport_RolePreferencesDetails.xlsx" # "Report -> User Report -> select "All", "Datat Only Export"; choose "Role and Task Permissions Details" then # press "Print" to open Export window. # Under "Export" menu, choose "Microsoft Excel Workbook Data-only" as Format, "Disk" as Destination, then press OK button to # generate and save the report; refer to the screenshot for details. @sy_role_details_report = File.dirname(__FILE__)+"/../../data/sage100/SY_UserReport_RoleTaskPermissionsDetails.xlsx" # sage100 to AD user map file @file_user_map = File.dirname(__FILE__)+"/../../data/sage100/sy_user_map.txt" # Load the user map file to an instance variable (for performance gain) @sy_2_ad_user=load_known_user_map_from_file(@file_user_map) # Load the user entitlement instance variable from the most complete 'User Accout Function' Sage report @sy_user_entitlement=parse_sy_user_role_preferences_detail_report (@sy_user_details_report) # Load the user role task permissions detail report parse_sy_uer_role_task_permissions_detail_report (@sy_role_details_report) # Procedure to perform Sage to AD user matching, insert AD DN into @sy_user_entitlement data structure insert_dn save! end
Public Instance Methods
print_user_entitlement()
click to toggle source
Print out the user entitlement table in plain text, to be imported into database
# File lib/ucert/sage100_tracker.rb, line 307 def print_user_entitlement begin puts "User Entitlement Report in Plain-text Format" if @verbose puts "User Logon|First Name|Last Name|Active|User Code|Customization Group|Expires|User Account Locked|E-mail|Preferences|Role|Scope|DN" if @verbose @sy_user_entitlement.values.map do |r_index| puts "#{r_index['User Logon']}|#{r_index['First Name']}|#{r_index['Last Name']}|#{r_index['Active']}|#{r_index['User Code']}|#{r_index['Customization Group']}|#{r_index['Expires']}|#{r_index['User Account Locked']}|#{r_index['E-mail']}|#{r_index['Preferences']}|#{r_index['Permissions']}|#{r_index['Role']}|#{r_index['Scope']}|#{r_index['DN']}" end rescue => ee puts "Exception on method #{__method__}: #{ee}" end end
Also aliased as: print_user
sage100_search_by_dn(dn)
click to toggle source
Search user entitlement r_index by AD DN
# File lib/ucert/sage100_tracker.rb, line 341 def sage100_search_by_dn (dn) begin puts "Perform search on the user entitlement r_index by AD DN: #{dn}" if @verbose @sy_user_entitlement.each do |key, val| return val if @sy_user_entitlement[key]['DN'].eql? dn end return nil rescue => ee puts "Exception on method #{__method__}: #{ee}" end end
Also aliased as: search_by_dn
user_name_2_index(name)
click to toggle source
sy_user_entitlement
table lookup, input is “User Name”, output is the corresponding user r_index number
# File lib/ucert/sage100_tracker.rb, line 289 def user_name_2_index (name) begin raise "Invalid user name: #{name}" if name.nil? or name.empty? puts "Perform Record number lookup for user name: #{name}" if @verbose @sy_user_entitlement.each do |key,val| next if val['User Logon'].nil? or val['User Logon'].empty? if val['User Logon'].upcase == name.upcase puts "User record number found: #{key}" if @verbose return key end end return nil rescue => ee puts "Exception on method #{__method__}: #{ee}" end end
Private Instance Methods
insert_dn()
click to toggle source
Perform search on the AD database, insert AD DN as a foreign key to the @sy_user_entitlement instance variable
# File lib/ucert/sage100_tracker.rb, line 249 def insert_dn begin tracker = Ucert::AdTracker.new(:verbose=>false) @sy_user_entitlement.each do |r_index, value| puts "\n\nPerform DN lookup for r_index: #{r_index}" if @verbose key1 = @sy_user_entitlement[r_index]['User Logon'] if @sy_user_entitlement[r_index]['User Logon'] key2 = @sy_user_entitlement[r_index]['First Name'] + @sy_user_entitlement[r_index]['Last Name'] if @sy_user_entitlement[r_index]['First Name'] and @sy_user_entitlement[r_index]['Last Name'] key3 = @sy_user_entitlement[r_index]['First Name'] if @sy_user_entitlement[r_index]['First Name'] my_key=key1.upcase puts "Perform 1st order search from the local cache: #{my_key}" if @verbose if @sy_2_ad_user.key?(my_key) dn=@sy_2_ad_user[my_key] # additional logic to update the existing DN r_index unless tracker.ad_person_records.key?(dn) dn = update_dn(tracker,dn) end puts "Found in the local cache file: #{dn}" if @verbose else if dn.nil? and !key1.nil? puts "Perform 2nd order search only if the 1st one fail, by using: #{key1}" if @verbose dn = tracker.ad_search_by_text(key1, "person") end if dn.nil? and !key2.nil? puts "Perform 3rd order search only if the last fail, by using: #{key2}" if @verbose dn = tracker.ad_search_by_text(key2, "person") end if dn.nil? and !key3.nil? puts "Perform 4th order search only if the last fail, by using: #{key3}" if @verbose dn = tracker.ad_search_by_text(key3, "person") end end @sy_user_entitlement[r_index]['DN'] = dn end tracker=nil rescue => ee puts "Exception on method #{__method__}: #{ee}" end end
parse_sy_uer_role_task_permissions_detail_report(file)
click to toggle source
Parsing the “User Report” task Permissions details in Excel xlsx format (exported from Sage 100)
# File lib/ucert/sage100_tracker.rb, line 189 def parse_sy_uer_role_task_permissions_detail_report (file) begin puts "Start parsing Excel workbook file: #{file}" if @verbose permissions = Hash.new workbook = RubyXL::Parser.parse(file) worksheet = workbook[0] report_type = String.new cur_user_start_row=0 # counter for the start row of the current role being recorded. cur_role = String.new cur_user_idx = 0 pre_user_idx = 0 recording=false worksheet.count.times do |row| puts "Processing row: #{row}" if @verbose # Step 1 - Protection check first if worksheet[row].nil? puts "Skip row #{row}" if @verbose next elsif worksheet[row][0].nil? puts "Skip row #{row}" if @verbose next elsif worksheet[row][0].value.to_s.empty? puts "Skip row #{row}" if @verbose next elsif worksheet[row][0].value.to_s.strip.empty? puts "Skip row #{row}" if @verbose next # continue Processing in the section below end # Step 2 - Detect the role definition section if worksheet[row][0].value.to_s.include?("User Logon:") pre_user_idx = cur_user_idx cur_user_start_row = row cur_user_idx = user_name_2_index (worksheet[row][1].value.to_s) @sy_user_entitlement[pre_user_idx]["Permissions"]=permissions unless pre_user_idx==0 # save previous recording result permissions=Hash.new end if row - cur_user_start_row == 2 recording=true next end # Step 3 - Record Permissions details if recording permit = Hash.new if worksheet[row][1].nil? permit[worksheet[row][0].value.to_s]=nil else permit[worksheet[row][0].value.to_s]=worksheet[row][1].value.to_s end permissions.merge!(permit) end end puts "Done parsing the workbook: #{file} " if @verbose workbook=nil @sy_user_entitlement[cur_user_idx]["Permissions"]=permissions # save the last recording rescue => ee puts "Exception on method #{__method__}: #{ee}" end end
parse_sy_user_role_preferences_detail_report(file)
click to toggle source
Parsing the “User Report” details in Excel xlsx format (exported from Sage 100)
# File lib/ucert/sage100_tracker.rb, line 51 def parse_sy_user_role_preferences_detail_report (file) begin puts "Start parsing Excel workbook file: #{file}" if @verbose sy_user_entitlement = Hash.new workbook = RubyXL::Parser.parse(file) worksheet = workbook[0] report_type = String.new r_index=0 # opeartor record index cur_user_start_row=0 # counter for the start row of the current user being recorded. worksheet.count.times do |row| puts "Processing row: #{row}" if @verbose # Step 1 - Protection check first if worksheet[row].nil? puts "Skip row #{row}" if @verbose next elsif worksheet[row][0].nil? puts "Skip row #{row}" if @verbose next elsif worksheet[row][0].value.to_s.empty? puts "Skip row #{row}" if @verbose next elsif worksheet[row][0].value.to_s.strip.empty? puts "Skip row #{row}" if @verbose next else # continue Processing in the section below end # Step 2 - Processing user general profile row if worksheet[row][0].value.to_s.include?("User Logon:") r_index += 1 cur_user_start_row = row sy_user_entitlement[r_index] = Hash.new unless sy_user_entitlement.key?(r_index) puts "Recording user #{r_index} profile: " if @verbose u_keys = Array.new u_vals = Array.new 0.upto(worksheet[row].size-1) do |col| #Processing cell [#{row}, #{col}]: #{worksheet[row][col].value.to_s} if col % 2 == 0 if worksheet[row][col].nil? u_keys.push(nil) else u_keys.push(worksheet[row][col].value.to_s.sub(/\:$/,"").strip) end else if worksheet[row][col].nil? u_vals.push(nil) else u_vals.push(worksheet[row][col].value.to_s) end end end puts "Assemblying user profile:" if @verbose record = u_keys.zip(u_vals).to_h puts "Found user profile - \n#{record}" if @verbose sy_user_entitlement[r_index]=record end # Step 3 - Processing the user "Job Title" row if worksheet[row][0].value.to_s.include?("Job Title:") sy_user_entitlement[r_index]["Job Title"] = worksheet[row][1].value.to_s end # Step 4 - Processing the user "Preferences Folder" row if worksheet[row][0].value.to_s.include?("Preferences Folder") puts "Recording user #{r_index} preferences: " if @verbose p_keys = Array.new p_vals = Array.new 1.upto(worksheet[row].size-1) do |col| puts "Processing column: #{col}" if @verbose if worksheet[row][col].nil? puts "Skip - empty cell" if @verbose next end #puts "Processing cell #{row}, #{col}: #{worksheet[row][col].value.to_s}" if col % 2 == 1 if worksheet[row][col].nil? p_keys.push(nil) else p_keys.push(worksheet[row][col].value.to_s.sub(/\:$/,"")) end else if worksheet[row][col].nil? p_vals.push(nil) else p_vals.push(worksheet[row][col].value.to_s) end end end puts "Assemblying user preferences:" if @verbose preferences = p_keys.zip(p_vals).to_h puts "Found user preferences - \n#{preferences}" if @verbose sy_user_entitlement[r_index]["Preferences"] = preferences end # Step 5 - Processing the extra user role information if row - cur_user_start_row == 2 role = Hash.new unless worksheet[row][0].nil? if worksheet[row][1].nil? role[worksheet[row][0].value.to_s] = nil else role[worksheet[row][0].value.to_s] = worksheet[row][1].value.to_s end end sy_user_entitlement[r_index]["Role"] = role end if row - cur_user_start_row == 3 scope = Hash.new if worksheet[row][0].nil? scope["Organization"]=nil else scope["Organization"]=worksheet[row][0].value.to_s end if worksheet[row][1].nil? scope["Start"]=nil else scope["Start"]=worksheet[row][1].value.to_s end if worksheet[row][2].nil? scope["Expires"]=nil else scope["Expires"]=worksheet[row][2].value.to_s end sy_user_entitlement[r_index]["Scope"]=scope end # Step 6 - Record ending self check if worksheet[row][0].value.to_s.include?("Run Date:") puts "Report Date: #{worksheet[row][1].value} " if @verbose break end puts "Done processing row: #{row}" if @verbose end puts "Done parsing the workbook: #{file} " if @verbose workbook=nil return sy_user_entitlement rescue => ee puts "Exception on method #{__method__}: #{ee}" end end
save_sy_user_map!(file=@file_user_map)
click to toggle source
Save the sage100 to AD user mapping relation into the cache file
# File lib/ucert/sage100_tracker.rb, line 321 def save_sy_user_map!(file=@file_user_map) puts "Saving the known Sage100 to AD user mapping relationship to file: #{file} ..." if @verbose begin timestamp=Time.now f=File.open(file, 'w') f.write "# local Sage100 to AD user map file created by the #{self.class} class #{__method__} method at: #{timestamp}" @sy_user_entitlement.values.map do |r_index| key = r_index['User Logon'].upcase value = r_index['DN'] f.write "\n#{key}|#{value}" end f.close puts "Sage100 to AD user map file is successfully saved to: #{file}" if @verbose rescue => ee puts "Exception on method #{__method__}: #{ee}" if @verbose end end
Also aliased as: save!