master

分支 (1)

管理

管理

master

Ruby
/
main.rb

# frozen_string_literal: true

# config.encoding = "utf-8"
require 'csv'
require 'active_record'
require 'pp'
require 'mysql2'
require 'uuidtools'

file_name = './org_emp.csv'

# Time.zone_default = Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone = :local

ActiveRecord::Base.configurations = YAML.load_file('./database.yml')
ActiveRecord::Base.establish_connection(ActiveRecord::Base.configurations['development'])

class EpFUnit < ActiveRecord::Base
  self.table_name = 'ep_f_unit'
end

class EpPersonal < ActiveRecord::Base
  self.table_name = 'ep_personal'
end

class SysUser < ActiveRecord::Base
  self.table_name = 'sys_user'
end

class DimensionTree < ActiveRecord::Base
  self.table_name = 'ep_f_dimension_tree'
end

class JobCode < ActiveRecord::Base
  self.table_name = 'ep_f_job_code'
end

class Depart < ActiveRecord::Base
  self.table_name = 'sys_depart'
end
# insert

# User.delete_all

# EpFUnit.create(id: new_uuid, unit_code: 'tanaka1', name: 19, unit_type_code: '1')
header = %w[STATUS,USERID,USERNAME,FIRSTNAME,NICKNAME,LASTNAME,TITLE,GENDER,EMAIL,MANAGER,HR,DEPARTMENT,JOBCODE,DIVISION,LOCATION,TIMEZONE,HIREDATE,EMPID,BIZ_PHONE,CELL_PHONE,FAX,ADDR1,ADDR2,CITY,STATE,ZIP,COUNTRY,REVIEW_FREQ,LAST_REVIEW_DATE,MATRIX_MANAGER,DEFAULT_LOCALE,PROXY,seatingChart,CUSTOM01,CUSTOM02,CUSTOM03,CUSTOM04,CUSTOM05,CUSTOM06,CUSTOM07,CUSTOM08,CUSTOM09,CUSTOM10,CUSTOM11,CUSTOM12,CUSTOM13,CUSTOM14,CUSTOM15,CUSTOM_MANAGER,SECOND_MANAGER,LOGIN_METHOD,JOBTITLE]
cols = %w[STATUS USERID 用户名 姓名 昵称 姓氏 职位 性别 电子邮件 上级主管 人力资源 部门 职称 部门 地点 时区 录用日期 工号 办公电话 手机]
# pp cols[1]
# pp cols['用户名']

# message = '区域拓展(50000024)'.gsub(/\s+/, '')

# regex.match(message)
# pp Regexp.last_match(1)
# pp Regexp.last_match(2)
def input_data
  #file_name = './ini/org_emp.csv'
  regex = /(\p{Han}*)\((\d*)\)/
  CSV.foreach(file_name, encoding: 'utf-8', headers: true) do |row|
    # puts row['DIVISION']
    division = row['DIVISION'].gsub(/\s+/, '')
    regex.match(division)
    name = Regexp.last_match(1)
    unit_code = Regexp.last_match(2)

    # if unit_code
    #   unless EpFUnit.find_by(unit_code: unit_code)
    #     new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
    #     EpFUnit.create(id: new_uuid, unit_type_code: '1',
    #                    unit_code: unit_code,
    #                    name: name)
    #   end
    # end

    work_no = row['USERID'].gsub(/\s+/, '')
    real_name = row['FIRSTNAME']
    if work_no
      if EpPersonal.find_by(work_no: work_no)
        EpPersonal.delete_by(work_no: work_no)
      end
      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      EpPersonal.create(id: new_uuid,
                        work_no: work_no,
                        real_name: real_name)

      SysUser.delete_by(work_no: work_no) if SysUser.find_by(work_no: work_no)
      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      SysUser.create(id: new_uuid,
                     work_no: work_no,
                     username: work_no,
                     realname: real_name,
                     status: 1,
                     del_flag: 0)

      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      # parent_id_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      if DimensionTree.find_by(node_code: work_no)
        DimensionTree.delete_by(node_code: work_no)
      end
      DimensionTree.create(id: new_uuid,
                           #  parent_id: parent_id_uuid,
                           dimension_code: '0001',
                           node_category: 3,
                           node_code: work_no)
    end
  end
end

def update_emp(row)
  regex = /(\p{Han}*|\w*)\((\w*)\)/
  work_no = row['USERID'].gsub(/\s+/, '')
  real_name = row['FIRSTNAME']
  if work_no && row['DEPARTMENT'] && row['JOBCODE']
    dep = row['DEPARTMENT'].gsub(/\s+/, '')
    job_code = row['JOBCODE'].gsub(/\s+/, '')

    regex.match(dep)
    d_name = Regexp.last_match(1)
    d_code = Regexp.last_match(2)

    regex.match(job_code)
    j_name = Regexp.last_match(1)
    j_code = Regexp.last_match(2)
    if d_code && j_code
      p_code = d_code + '_' + j_code

      # if EpPersonal.find_by(work_no: work_no)
      #   EpPersonal.delete_by(work_no: work_no)
      # end
      # new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      # EpPersonal.create(id: new_uuid,
      #                   work_no: work_no,
      #                   real_name: real_name)
      puts p_code
      SysUser.delete_by(work_no: work_no) if SysUser.find_by(work_no: work_no)
      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      SysUser.create(id: new_uuid,
                     work_no: work_no,
                     username: work_no,
                     realname: real_name,
                     post: p_code,
                     org_code: d_code,
                     del_flag: 0)
    end
  end
end

def update_job_code(p_job_code)
  regex = /(\p{Han}*|\w*)\((\w*)\)/
  if p_job_code
    division = p_job_code.gsub(/\s+/, '')
    regex.match(division)
    name = Regexp.last_match(1)
    external_code = Regexp.last_match(2)

    if external_code

      job_code = JobCode.find_by(external_code: external_code)
      if job_code

        pp external_code
        puts name
        puts job_code.name
        # job_code.update_by(external_code: external_code,
        #                    name: name,
        #                    deleted: 0)
        JobCode.where(external_code: external_code)
               .update_all(external_code: external_code,
                           name: name,
                           deleted: 0)
      else
        new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
        JobCode.create(id: new_uuid,
                       external_code: external_code,
                       name: name,
                       deleted: 0)
      end
    end
  end
end

# update unit
def update_unit
  #file_name = './ini/org_emp.csv'
  regex = /(\p{Han}*|\w*)\((\d*)\)/
  CSV.foreach(file_name, encoding: 'utf-8', headers: true) do |row|
    division = row['DIVISION'].gsub(/\s+/, '')
    # puts division
    regex.match(division)
    name = Regexp.last_match(1)
    division_code = Regexp.last_match(2)

    if division_code
      # puts unit_code
      unless EpFUnit.find_by(unit_code: division_code)
        new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
        EpFUnit.create(id: new_uuid,
                       unit_type_code: '1',
                       unit_code: division_code,
                       name: name)
      end

      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      if DimensionTree.find_by(node_code: division_code)
        DimensionTree.delete_by(node_code: division_code)
      end
      DimensionTree.create(id: new_uuid,
                           parent_id: 0,
                           dimension_code: '0002',
                           node_category: 1,
                           node_code: division_code)
    end
    #  DEPARTMENT
    department = row['DEPARTMENT'].gsub(/\s+/, '')

    regex.match(department)
    dep_name = Regexp.last_match(1)
    dep_code = Regexp.last_match(2)

    if dep_code
      puts dep_code
      puts dep_name
      unless EpFUnit.find_by(unit_code: dep_code)

        new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
        EpFUnit.create(id: new_uuid,
                       unit_type_code: '1',
                       unit_code: dep_code,
                       deleted: 0,
                       name: dep_name)
      end

      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      if DimensionTree.find_by(node_code: dep_code)
        DimensionTree.delete_by(node_code: dep_code)
      end
      DimensionTree.create(id: new_uuid,
                           parent_id: 0,
                           dimension_code: '0002',
                           node_category: 1,
                           node_code: dep_code)
    end
  end
end

def update_position(row)
  regex = /(\p{Han}*|\w*)\((\w*)\)/

  if row['DEPARTMENT'] && row['JOBCODE']
    dep = row['DEPARTMENT'].gsub(/\s+/, '')
    job_code = row['JOBCODE'].gsub(/\s+/, '')

    regex.match(dep)
    d_name = Regexp.last_match(1)
    d_code = Regexp.last_match(2)

    regex.match(job_code)
    j_name = Regexp.last_match(1)
    j_code = Regexp.last_match(2)

    if d_code && j_code
      p_code = d_code + '_' + j_code
      p_name = d_name + '_' + j_name
      position = Position.find_by(code: p_code)
      if position
        # job_code.update_by(external_code: external_code,
        #                    name: name,
        #                    deleted: 0)
        Position.where(code: p_code)
                .update_all(code: p_code,
                            name: p_name,
                            job_code: j_code,
                            deleted: 0)
      else
        new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
        Position.create(id: new_uuid,
                        code: p_code,
                        name: p_name,
                        job_code: j_code,
                        deleted: 0)
      end
    end

    if dep
      position = Position.find_by(code: p_code)
      if position
        # job_code.update_by(external_code: external_code,
        #                    name: name,
        #                    deleted: 0)
        Position.where(code: p_code)
                .update_all(code: p_code,
                            name: p_name,
                            job_code: j_code,
                            deleted: 0)
      else
        new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
        Position.create(id: new_uuid,
                        code: p_code,
                        name: p_name,
                        job_code: j_code,
                        deleted: 0)
      end
    end
  end
end

def update_depart(row)
  regex = /(\p{Han}*|\w*)\((\w*)\)/
  if row['DEPARTMENT'] && row['JOBCODE']
    dep = row['DEPARTMENT'].gsub(/\s+/, '')
    job_code = row['JOBCODE'].gsub(/\s+/, '')

    regex.match(dep)
    d_name = Regexp.last_match(1)
    d_code = Regexp.last_match(2)

    regex.match(job_code)
    j_name = Regexp.last_match(1)
    j_code = Regexp.last_match(2)
    new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
    parent_id_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
    if d_code && d_name
      Depart.delete_by(org_code: d_code) if Depart.find_by(org_code: d_code)
      Depart.create(id: new_uuid,
                    depart_name: d_name,
                    parent_id: '33fff21ee50545789dc1212b334f0bb9',
                    org_category: 1,
                    org_type: 2,
                    del_flag: 0,
                    org_code: d_code)
    end
  end
end

def input_f_data
  #file_name = './ini/org_emp.csv'
  CSV.foreach(file_name, encoding: 'utf-8', headers: true) do |row|
    # update_job_code(row['JOBCODE'])
    # update_position(row)
    # update_emp(row)
    update_depart(row)
  end
end

def update_parent
  #file_name = './ini/org_emp.csv'
  CSV.foreach(file_name, encoding: 'utf-8', headers: true) do |row|
    manager_id = row['MANAGER']
    work_no = row['USERID'].gsub(/\s+/, '')
    user = DimensionTree.find_by(node_code: work_no)
    if manager_id == 'NO_MANAGER'
      user&.update(parent_id: 0)
    else
      manager = DimensionTree.find_by(node_code: manager_id)
      user&.update(parent_id: manager.id)
    end
  end
end

def input_tree_data
  #file_name = './ini/org_emp.csv'
  regex = /(\p{Han}*)\((\d*)\)/
  CSV.foreach(file_name, encoding: 'utf-8', headers: true) do |row|
    # puts row['DIVISION']
    division = row['DIVISION'].gsub(/\s+/, '')
    regex.match(division)
    name = Regexp.last_match(1)
    unit_code = Regexp.last_match(2)

    work_no = row['USERID'].gsub(/\s+/, '')
    real_name = row['FIRSTNAME']
    if work_no
      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      # parent_id_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      if DimensionTree.find_by(node_code: work_no)
        DimensionTree.delete_by(node_code: work_no)
      end
      DimensionTree.create(id: new_uuid,
                           #  parent_id: parent_id_uuid,
                           dimension_code: '0001',
                           node_category: 3,
                           node_code: work_no)
    end
  end
  CSV.foreach(file_name, encoding: 'utf-8', headers: true) do |row|
    # puts row['DIVISION']
    division = row['DIVISION'].gsub(/\s+/, '')
    regex.match(division)
    name = Regexp.last_match(1)
    unit_code = Regexp.last_match(2)

    work_no = row['USERID'].gsub(/\s+/, '')
    real_name = row['FIRSTNAME']
    if work_no
      new_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      # parent_id_uuid = UUIDTools::UUID.timestamp_create.to_s.gsub('-', '')
      if DimensionTree.find_by(node_code: work_no)
        DimensionTree.delete_by(node_code: work_no)
      end
      DimensionTree.create(id: new_uuid,
                           #  parent_id: parent_id_uuid,
                           dimension_code: '0001',
                           node_category: 3,
                           node_code: work_no)
    end
  end
end

def find_parent(p_parent_id, brd)
  node = DimensionTree.find_by(id: p_parent_id)

  parent_id = node.parent_id
  new_brd = node.node_code + ',' + brd
  if parent_id != '0'
    find_parent(parent_id, new_brd)
  else
    new_brd
  end
end

def build_breadcrumb
  DimensionTree.find_each do |node|
    parent_id = node.parent_id
    puts parent_id
    if parent_id != '0'
      brd = find_parent(parent_id, node.node_code)
      pp brd
      node.breadcrumb = brd
    else
      node.breadcrumb = node.node_code
    end
    node.save
  end
end

def update_unit_parent
  #file_name = './ini/org_emp.csv'
  regex = /(\p{Han}*|\w*)\((\w*)\)/
  CSV.foreach(file_name, encoding: 'utf-8', headers: true) do |row|
    division = row['DIVISION'].gsub(/\s+/, '')
    # puts division
    regex.match(division)
    name = Regexp.last_match(1)
    division_code = Regexp.last_match(2)

    #  DEPARTMENT
    department = row['DEPARTMENT'].gsub(/\s+/, '')

    regex.match(department)
    dep_name = Regexp.last_match(1)
    dep_code = Regexp.last_match(2)

    if division_code && dep_code
      unit = DimensionTree.find_by(node_code: dep_code)
      parent = DimensionTree.find_by(node_code: dep_code)
      unit&.update(parent_id: parent.id)
    end
  end
end

def build_tree
  input_tree_data
  # update_parent
  #build_breadcrumb
end

#update_unit
#update_unit_parent
build_tree