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()
print_user_entitlement() click to toggle source

Print out the user entitlement table in plain text, to be imported into database

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
save!(file=@file_user_map)
Alias for: save_sy_user_map!
search_by_dn(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!