■Salesforce ロール階層をId順でツリー構造にしてみる。 Salesforce上の設定画面はId順ではないので表示順までは一致しない点に注意。 SOQLでUserRoleオブジェクトから取得する項目は3つ。 SELECT Id, Name, ParentRoleId FROM UserRole ■Linux用にcsvから「\r」を削除 ※SJISならUTF-8に変換もしておく。 $ cat de_usertole.csv | tr -d '\r' > de_usertole_linux.csv ■まずは区切り文字を「,」から「^」に変更する。 $ awk -F, '{print NF | "sort -V | uniq -c"}' de_usertole_linux.csv 20 3 10 4 $ awk -F, '{if(NF==4){print $0}}' de_usertole_linux.csv "00E5h000001JXD2EAO","Customer Support, International","00E5h000001JXCtEAO" "00E5h000001JXCxEAO","Customer Support, North America","00E5h000001JXCtEAO" "00E5h000001JXCuEAO","Director, Channel Sales","00E5h000001JXCqEAO" "00E5h000001JXCrEAO","Director, Direct Sales","00E5h000001JXCqEAO" "00E5h000001JXCtEAO","SVP, Customer Service & Support","00E5h000001JXCoEAO" "00E5h000001JXD4EAO","SVP, Human Resources","00E5h000001JXCoEAO" "00E5h000001JXCpEAO","SVP, Sales & Marketing","00E5h000001JXCoEAO" "00E5h000001JXD3EAO","VP, International Sales","00E5h000001JXCpEAO" "00E5h000001JXCsEAO","VP, Marketing","00E5h000001JXCpEAO" "00E5h000001JXCqEAO","VP, North American Sales","00E5h000001JXCpEAO" $ sed -i -e 's%","%"^"%g' de_usertole_linux.csv ■階層図に変換。矢印があまりきれいじゃないけど、何階層あるかわかりやすいのは大きな収穫。 $ awk -F^ '!/PARENTROLEID/{gsub("\042\042","Top",$NF);gsub("\042","",$NF);gsub("\042","",$1);print "["$NF"] -- ["$1"]" | "sort -uV"}' \ de_usertole_linux.csv | graph-easy --dot | dot -Tpng -o parentId.png
■Idじゃわかりにくいので、上記のPNG出力の直前をparentファイルに書き出して、 最後に「&」に意味を持たせないように変換したsedコマンドをawkで作ってparentを書き換えるよう実行(sh)したものをPNG化。 $ awk -F^ '!/PARENTROLEID/{gsub("\042\042","Top",$NF);gsub("\042","",$NF);gsub("\042","",$1);print "["$NF"] -- ["$1"]" | "sort -uV"}' \ de_usertole_linux.csv | graph-easy --dot > parent $ cat de_usertole_linux.csv | tr -d '\042' | \ awk -F^ 'BEGIN{printf "sed "}!/NAME/{print " -e \047s%"$1"%"$2"\\\\n"$1"%g\047 \\"}END{print " parent"}' | \ sed -e 's/&/\\&/g' | sh | graph-easy --dot | dot -Tpng -o parentName.png
■最大の階層数が分かったところで、 これ以上親がいないというのを5回抽出できれば階層をディレクトリ構成として再現できそう。 $ echo 00E5h000001JXCvEAO/ | wc -c 20 ■これ以上親がいないIdを抽出。これの親はrootとなる組織名 $ awk -F^ '($0 !~ /PARENTROLEID/ && $NF !~ /00E/){gsub("\042","",$0);print $1}' de_usertole_linux.csv > parent1 $ cat parent1 00E5h000001JXCoEAO ■1階層目を親Idに持つ2階層目のIdを抽出 $ awk -F^ -v keyword=$(cat parent1) '($0 !~ /PARENTROLEID/ && $NF ~ keyword){gsub("\042","",$0);print keyword"/"$1}' de_usertole_linux.csv > parent2 $ cat parent2 00E5h000001JXCoEAO/00E5h000001JXCvEAO 00E5h000001JXCoEAO/00E5h000001AQYmEAO 00E5h000001JXCoEAO/00E5h000001JXD5EAO 00E5h000001JXCoEAO/00E5h000000GyPvEAK 00E5h000001JXCoEAO/00E5h000000GyQ0EAK 00E5h000001JXCoEAO/00E5h000001JXCtEAO 00E5h000001JXCoEAO/00E5h000001JXD4EAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO 00E5h000001JXCoEAO/00E5h000001B5syEAC ■これ実は「substr(keyword,1)」と同義。 $ awk '{print "awk -F^ -v keyword=\042"$1"\042 \047($0 !~ /PARENTROLEID/ && $NF ~ substr(keyword,1)){gsub(\042\\042\042,\042\042,$0);print keyword\042/\042$1}\047 de_usertole_linux.csv"}' parent1 |sh 00E5h000001JXCoEAO/00E5h000001JXCvEAO 00E5h000001JXCoEAO/00E5h000001AQYmEAO 00E5h000001JXCoEAO/00E5h000001JXD5EAO 00E5h000001JXCoEAO/00E5h000000GyPvEAK 00E5h000001JXCoEAO/00E5h000000GyQ0EAK 00E5h000001JXCoEAO/00E5h000001JXCtEAO 00E5h000001JXCoEAO/00E5h000001JXD4EAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO 00E5h000001JXCoEAO/00E5h000001B5syEAC ■2から4階層目を親Idに持つ3～5階層目のIdを抽出 $ awk '{print "awk -F^ -v keyword=\042"$1"\042 \047($0 !~ /PARENTROLEID/ && $NF ~ substr(keyword,20)){gsub(\042\\042\042,\042\042,$0);print keyword\042/\042$1}\047 de_usertole_linux.csv"}' parent2 |sh > parent3 $ awk '{print "awk -F, -v keyword=\042"$1"\042 \047($0 !~ /PARENTROLEID/ && $NF ~ substr(keyword,40)){gsub(\042\\042\042,\042\042,$0);print keyword\042/\042$1}\047 de_usertole_linux.csv"}' parent3 |sh > parent4 $ awk '{print "awk -F, -v keyword=\042"$1"\042 \047($0 !~ /PARENTROLEID/ && $NF ~ substr(keyword,60)){gsub(\042\\042\042,\042\042,$0);print keyword\042/\042$1}\047 de_usertole_linux.csv"}' parent4 |sh > parent5 $ cat parent[3-5] 00E5h000001JXCoEAO/00E5h000001AQYmEAO/00E5h000001AQYrEAO 00E5h000001JXCoEAO/00E5h000001JXCtEAO/00E5h000001JXD2EAO 00E5h000001JXCoEAO/00E5h000001JXCtEAO/00E5h000001JXCxEAO 00E5h000001JXCoEAO/00E5h000001JXCtEAO/00E5h000001JXD0EAO 00E5h000001JXCoEAO/00E5h000001JXD4EAO/00E5h000001B5tNEAS 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXD3EAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCsEAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCqEAO 00E5h000001JXCoEAO/00E5h000001B5syEAC/00E5h000001B5t3EAC 00E5h000001JXCoEAO/00E5h000001B5syEAC/00E5h000001B5t8EAC 00E5h000001JXCoEAO/00E5h000001AQYmEAO/00E5h000001AQYrEAO/00E5h000001AQYwEAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCsEAO/00E5h000001JXCzEAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCqEAO/00E5h000001JXCuEAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCqEAO/00E5h000001JXCrEAO 00E5h000001JXCoEAO/00E5h000001B5syEAC/00E5h000001B5t8EAC/00E5h000001B5tDEAS 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCqEAO/00E5h000001JXCuEAO/00E5h000001JXCyEAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCqEAO/00E5h000001JXCrEAO/00E5h000001JXD1EAO 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCqEAO/00E5h000001JXCrEAO/00E5h000001B5tIEAS 00E5h000001JXCoEAO/00E5h000001JXCpEAO/00E5h000001JXCqEAO/00E5h000001JXCrEAO/00E5h000001JXCwEAO ■Idを、{Id}_{名前}の組にするsed文を生成し実行 実際に存在するディレクトリではないのでtreeコマンドの代わりのsedコマンドのあと、 整形して、{Id}_箇所をgrepで色付けして名前と区別しやすいようにした。 ※Idが固定長なので、まあ以下の色付けなしのままでも。。。 $ awk -F^ 'BEGIN{print "cat parent[1-5] | sed \\"}($0 !~ /PARENTROLEID/){gsub("\042","",$0);print " -e \047s%"$1"%"$1"_"$2"%g\047 \\"}' \ de_usertole_linux.csv | sed -e 's/&/\\&/g' -e '$ s/\\$//g' | sh | \ sort | sed '1d;s/^\.//;s/\/\([^/]*\)$/|--\1/;s/\/[^/|]*/| /g' | \ sed -e '2,$ s/00E5h000001JXCoEAO_CEO/ /g' -e 's/|--00E/+--00E/g' | grep --color "00E[A-Za-z0-9]*_" 00E5h000001JXCoEAO_CEO+--00E5h000000GyPvEAK_Customer Manager +--00E5h000000GyQ0EAK_Partner Manager +--00E5h000001AQYmEAO_Chief Sales Officer | +--00E5h000001AQYrEAO_Sales Strategy Manager | | +--00E5h000001AQYwEAO_Training Coordinator +--00E5h000001B5syEAC_VP of Services | +--00E5h000001B5t3EAC_Accounts Receivable | +--00E5h000001B5t8EAC_Customer Support Director | | +--00E5h000001B5tDEAS_Customer Support Rep +--00E5h000001JXCpEAO_SVP, Sales & Marketing | +--00E5h000001JXCqEAO_VP, North American Sales | | +--00E5h000001JXCrEAO_Director, Direct Sales | | | +--00E5h000001B5tIEAS_Sales Engineer | | | +--00E5h000001JXCwEAO_Western Sales Team | | | +--00E5h000001JXD1EAO_Eastern Sales Team | | +--00E5h000001JXCuEAO_Director, Channel Sales | | | +--00E5h000001JXCyEAO_Channel Sales Team | +--00E5h000001JXCsEAO_VP, Marketing | | +--00E5h000001JXCzEAO_Marketing Team | +--00E5h000001JXD3EAO_VP, International Sales +--00E5h000001JXCtEAO_SVP, Customer Service & Support | +--00E5h000001JXCxEAO_Customer Support, North America | +--00E5h000001JXD0EAO_Installation & Repair Services | +--00E5h000001JXD2EAO_Customer Support, International +--00E5h000001JXCvEAO_CFO +--00E5h000001JXD4EAO_SVP, Human Resources | +--00E5h000001B5tNEAS_Recruiter +--00E5h000001JXD5EAO_COO ■「+」か「|」が階を表すので、それを数えれば階層数も付与できる。 awkで順序性が破壊されないように行数のNR変数を使ってソートする。 $ awk -F^ 'BEGIN{print "cat parent[1-5] | sed \\"}($0 !~ /PARENTROLEID/){gsub("\042","",$0);print " -e \047s%"$1"%"$1"_"$2"%g\047 \\"}' \ de_usertole_linux.csv | sed -e 's/&/\\&/g' -e '$ s/\\$//g' | sh | \ sort | sed '1d;s/^\.//;s/\/\([^/]*\)$/|--\1/;s/\/[^/|]*/| /g' | \ sed -e '2,$ s/00E5h000001JXCoEAO_CEO/ /g' -e 's/|--00E/+--00E/g'| \ awk '{for(a=1;a<=NF;a++){if($a ~ /[|+]/){b[NR"_"$0]++}}}END{for(n in b){print n,b[n]+1 | "sort -V"}}' | \ sed -e 's/^[0-9]*_//g' | \ grep --color "00E[A-Za-z0-9]*_" 00E5h000001JXCoEAO_CEO+--00E5h000000GyPvEAK_Customer Manager 2 +--00E5h000000GyQ0EAK_Partner Manager 2 +--00E5h000001AQYmEAO_Chief Sales Officer 2 | +--00E5h000001AQYrEAO_Sales Strategy Manager 3 | | +--00E5h000001AQYwEAO_Training Coordinator 4 +--00E5h000001B5syEAC_VP of Services 2 | +--00E5h000001B5t3EAC_Accounts Receivable 3 | +--00E5h000001B5t8EAC_Customer Support Director 3 | | +--00E5h000001B5tDEAS_Customer Support Rep 4 +--00E5h000001JXCpEAO_SVP, Sales & Marketing 2 | +--00E5h000001JXCqEAO_VP, North American Sales 3 | | +--00E5h000001JXCrEAO_Director, Direct Sales 4 | | | +--00E5h000001B5tIEAS_Sales Engineer 5 | | | +--00E5h000001JXCwEAO_Western Sales Team 5 | | | +--00E5h000001JXD1EAO_Eastern Sales Team 5 | | +--00E5h000001JXCuEAO_Director, Channel Sales 4 | | | +--00E5h000001JXCyEAO_Channel Sales Team 5 | +--00E5h000001JXCsEAO_VP, Marketing 3 | | +--00E5h000001JXCzEAO_Marketing Team 4 | +--00E5h000001JXD3EAO_VP, International Sales 3 +--00E5h000001JXCtEAO_SVP, Customer Service & Support 2 | +--00E5h000001JXCxEAO_Customer Support, North America 3 | +--00E5h000001JXD0EAO_Installation & Repair Services 3 | +--00E5h000001JXD2EAO_Customer Support, International 3 +--00E5h000001JXCvEAO_CFO 2 +--00E5h000001JXD4EAO_SVP, Human Resources 2 | +--00E5h000001B5tNEAS_Recruiter 3 +--00E5h000001JXD5EAO_COO 2 ■当たり前だけどIdがいらなくなったら消せばいい。 $ awk -F^ 'BEGIN{print "cat parent[1-5] | sed \\"}($0 !~ /PARENTROLEID/){gsub("\042","",$0);print " -e \047s%"$1"%"$1"_"$2"%g\047 \\"}' \ de_usertole_linux.csv | sed -e 's/&/\\&/g' -e '$ s/\\$//g' | sh | \ sort | sed '1d;s/^\.//;s/\/\([^/]*\)$/|--\1/;s/\/[^/|]*/| /g' | \ sed -e '2,$ s/00E5h000001JXCoEAO_CEO/ /g' -e 's/|--00E/+--00E/g'| \ awk '{for(a=1;a<=NF;a++){if($a ~ /[|+]/){b[NR"_"$0]++}}}END{for(n in b){print n,b[n]+1 | "sort -V"}}' | \ sed -e 's/^[0-9]*_//g' -e 's/00E[A-Za-z0-9]*_//g' CEO+--Customer Manager 2 +--Partner Manager 2 +--Chief Sales Officer 2 | +--Sales Strategy Manager 3 | | +--Training Coordinator 4 +--VP of Services 2 | +--Accounts Receivable 3 | +--Customer Support Director 3 | | +--Customer Support Rep 4 +--SVP, Sales & Marketing 2 | +--VP, North American Sales 3 | | +--Director, Direct Sales 4 | | | +--Sales Engineer 5 | | | +--Western Sales Team 5 | | | +--Eastern Sales Team 5 | | +--Director, Channel Sales 4 | | | +--Channel Sales Team 5 | +--VP, Marketing 3 | | +--Marketing Team 4 | +--VP, International Sales 3 +--SVP, Customer Service & Support 2 | +--Customer Support, North America 3 | +--Installation & Repair Services 3 | +--Customer Support, International 3 +--CFO 2 +--SVP, Human Resources 2 | +--Recruiter 3 +--COO 2 ■元のファイルを壊さずに「\r」の削除、区切り文字の変換「,」→「^」もSJIS→UTF-8も埋め込んじゃおう。 rootとなる組織名階層は0として、日本語をまぜて階層を最大7、2階層のCEO2、1階層のCOE3を追加で作成。 「COE2 [1]」が出ないのが気になるけど、そもそも共有ロールなので1階層目を増やすアイデア自体が良くない。 当然、階層ではなくフラットにしようとして1階層目だけで表現するのもアイデア自体が良くない。 $ nkf -g de_usertole2.csv Shift_JIS ■5行目の先頭「CEO」の文字を消すsed文があるが、 何行目から何行目まで出現するか、そもそも「CEO」かという問題があるので外出しにした。 Id順 $ ./myUserRole de_usertole2.csv 7 | sed -e '5,$ s/^CEO\([+|]\)/ \1/' CEO2+--サンプル [2] CEO3 [1] CEO [1] CEO+--Customer Manager [2] +--Partner Manager [2] +--Chief Sales Officer [2] | +--Sales Strategy Manager [3] | | +--Training Coordinator [4] +--VP of Services [2] | +--Accounts Receivable [3] | +--Customer Support Director [3] | | +--Customer Support Rep [4] +--SVP, Sales & Marketing [2] | +--VP, North American Sales [3] | | +--Director, Direct Sales [4] | | | +--Sales Engineer [5] | | | | +--6階層目 [6] | | | | | +--7階層目 [7] | | | +--Western Sales Team [5] | | | +--Eastern Sales Team [5] | | +--Director, Channel Sales [4] | | | +--Channel Sales Team [5] | +--VP, Marketing [3] | | +--Marketing Team [4] | +--VP, International Sales [3] +--SVP, Customer Service & Support [2] | +--Customer Support, North America [3] | +--Installation & Repair Services [3] | +--Customer Support, International [3] +--CFO [2] +--SVP, Human Resources [2] | +--Recruiter [3] +--COO [2] ■「myUserRole」コマンドの中身 $ cat myUserRole #!/bin/bash CSV=$1 CSV_LINUX=$(echo $1 | sed -e 's/.csv/_linux.csv/') DIR=$2 if [ $# -ne 2 ];then echo "$0 [csvname] [dir number]" exit 1 fi if [ ! -f ${CSV} ];then echo "Not Found:${CSV}" exit 2 fi if [ ${DIR} -eq 0 ] || [ ${DIR} -ge 100 ];then echo "[dir number] must be 1 to 99" exit 3 fi # Change Char SJIS to UTF-8, and Delete '\r', and sep ',' to '^' nkf -Lu -d ${CSV} | tr -d '\r' | sed -e 's%","%"^"%g' > ${CSV_LINUX} # 2 time escape # $ echo | awk '{print "echo | awk \047{print \042echo | awk \\047{print \\0421\\042}\\047\042}\047"}' | sh | sh #1 # 1 time escape # echo | awk '{print "echo | awk \047{print \0421\042}\047"}' |sh # 1 # no escape # $ echo | awk '{print 1}' # 1 # first parent awk -F^ '($0 !~ /PARENTROLEID/ && $NF !~ /00E/){gsub("\042","",$0);print $1}' ${CSV_LINUX} > parent1 echo | awk -v EOF=${DIR} -v csv=${CSV_LINUX} '{for(a=2;a<=EOF;a++){if(a==2){b=1}else{b=(a-2)*20}{print "awk \047{print \042awk -F^ -v keyword=\\042\042$1\042\\042 \\047($0 !~ /PARENTROLEID/ && $NF ~ substr(keyword,"b")){gsub(\\042\\\\042\\042,\\042\\042,$0);print keyword\\042/\\042$1}\\047 "csv"\042}\047 parent"a-1" | sh > parent"a}}}' | sh awk -F^ -v n=${DIR} 'BEGIN{print "cat parent[1-"n"] | sed \\"}($0 !~ /PARENTROLEID/){gsub("\042","",$0);print " -e \047s%"$1"%"$1"_"$2"%g\047 \\"}' ${CSV_LINUX} | \ sed -e 's/&/\\&/g' -e '$ s/\\$//g' | sh | \ #awk '{print NR,"Org/"$0 | "sort"}' | sed -e 's/^[0-9]* //g' | \ sort | sed -e '1d;s/^\.//;s/\/\([^/]*\)$/|--\1/;s/\/[^/|]*/| /g' | \ sed -e 's/|--00E/+--00E/g' | \ awk '{for(a=1;a<=NF;a++){if($a ~ /[|+]/){b[NR"_"$0]++}else{if($0 !~ /[|+]/){b[NR"_"$0]=0}}}}END{for(n in b){print n,"["b[n]+1"]" | "sort -V"}}' | \ sed -e 's/^[0-9]*_//g' -e 's/00E[A-Za-z0-9]*_//g' ■最終行の「-e 's/00E[A-Za-z0-9]*_//g'」をコメントアウトすると $ awk '(/[A-Za-z0-9]/){a=$0}END{print a}' myUserRole sed -e 's/^[0-9]*_//g' #-e 's/00E[A-Za-z0-9]*_//g' $ ./myUserRole de_usertole2.csv 7 | sed -e '5,$ s/^00E.*CEO\([+|]\)/ \1/' 00E5h000000m1IHEAY_CEO2+--00E5h000000m1IMEAY_サンプル [2] 00E5h000000m1IHEAZ_CEO3 [1] 00E5h000001JXCoEAO_CEO [1] 00E5h000001JXCoEAO_CEO+--00E5h000000GyPvEAK_Customer Manager [2] +--00E5h000000GyQ0EAK_Partner Manager [2] +--00E5h000001AQYmEAO_Chief Sales Officer [2] | +--00E5h000001AQYrEAO_Sales Strategy Manager [3] | | +--00E5h000001AQYwEAO_Training Coordinator [4] +--00E5h000001B5syEAC_VP of Services [2] | +--00E5h000001B5t3EAC_Accounts Receivable [3] | +--00E5h000001B5t8EAC_Customer Support Director [3] | | +--00E5h000001B5tDEAS_Customer Support Rep [4] +--00E5h000001JXCpEAO_SVP, Sales & Marketing [2] | +--00E5h000001JXCqEAO_VP, North American Sales [3] | | +--00E5h000001JXCrEAO_Director, Direct Sales [4] | | | +--00E5h000001B5tIEAS_Sales Engineer [5] | | | | +--00E5h000000m1I7EAI_6階層目 [6] | | | | | +--00E5h000000m1ICEAY_7階層目 [7] | | | +--00E5h000001JXCwEAO_Western Sales Team [5] | | | +--00E5h000001JXD1EAO_Eastern Sales Team [5] | | +--00E5h000001JXCuEAO_Director, Channel Sales [4] | | | +--00E5h000001JXCyEAO_Channel Sales Team [5] | +--00E5h000001JXCsEAO_VP, Marketing [3] | | +--00E5h000001JXCzEAO_Marketing Team [4] | +--00E5h000001JXD3EAO_VP, International Sales [3] +--00E5h000001JXCtEAO_SVP, Customer Service & Support [2] | +--00E5h000001JXCxEAO_Customer Support, North America [3] | +--00E5h000001JXD0EAO_Installation & Repair Services [3] | +--00E5h000001JXD2EAO_Customer Support, International [3] +--00E5h000001JXCvEAO_CFO [2] +--00E5h000001JXD4EAO_SVP, Human Resources [2] | +--00E5h000001B5tNEAS_Recruiter [3] +--00E5h000001JXD5EAO_COO [2]
