Option Explicit
Dim dicT As Object '辞書 キー:支店名+課名+項目名(A~N) 値:粗利の値の合計値
Const Sheet1 As String = "1部ピボット"
Const Sheet2 As String = "2部ピボット"
Const Sheet3 As String = "3部ピボット"
Dim Uri_book As String '粗利データブック名
Dim trg_mm As Long '指定月
Dim Jis_folder As String '実績フォルダ
Dim Kei_folder As String '計画フォルダ
Dim trg_col As Long '算出カラム番号
Dim warnP As String '警告データ
Dim item_tbl1 As Variant '1部の項目名テーブル
Dim item_tbl2 As Variant '2部の項目名テーブル
Dim item_tbl3 As Variant '3部の項目名テーブル
Public Sub 拠点別粗利集計()
Dim ws As Worksheet
Dim mm, ext As Variant
Dim t1, t2 As Variant
Dim Uri_path As String
Dim key As Variant
Set dicT = CreateObject("Scripting.Dictionary")
Set ws = Worksheets("粗利集計")
'項目名テーブル作成
item_tbl1 = Array("I", "J", "K")
item_tbl2 = Array("L", "M", "N")
item_tbl3 = Array("A", "B", "C", "D", "E", "F", "G", "H")
mm = ws.Cells(2, "A").Value '集計月
If mm = "" Or IsNumeric(mm) = False Then
MsgBox ("集計月が不正")
Exit Sub
End If
trg_mm = mm
If trg_mm < 1 Or trg_mm > 12 Then
MsgBox ("集計月(1-12)が範囲外")
Exit Sub
End If
'集計月に対応するカラム位置を取得
trg_col = GetColNumber(trg_mm)
Jis_folder = ws.Cells(2, "B").Value '実績フォルダ名
Uri_book = ws.Cells(2, "C").Value '粗利データファイル
'拡張子のチェック
ext = Right(LCase(Uri_book), 5)
If ext <> ".xlsx" And ext <> ".xlsm" Then
MsgBox ("粗利データファイル名が不正")
Exit Sub
End If
Kei_folder = ws.Cells(2, "D").Value '計画フォルダ名
If Dir(Jis_folder, vbDirectory) = "" Then
MsgBox ("実績フォルダが存在しません<" & Jis_folder & ">")
Exit Sub
End If
If Dir(Kei_folder, vbDirectory) = "" Then
MsgBox ("計画フォルダが存在しません<" & Kei_folder & ">")
Exit Sub
End If
'粗利データブック名設定
Uri_path = Jis_folder & "\" & Uri_book
If Dir(Uri_path, vbNormal) = "" Then
MsgBox (Uri_path & "が存在しません")
Exit Sub
End If
If MsgBox(trg_mm & "月の粗利を" & Uri_book & "から集計します", vbOKCancel) <> vbOK Then Exit Sub
t1 = Timer
Application.ScreenUpdating = False
'再計算を手動に設定
Application.Calculation = xlCalculationManual
'売上明細ブック名オープン
Workbooks.Open Uri_path
Workbooks(Uri_book).Activate
'ピボットデータ読み込み
Call readPivot(Sheet1)
Call readPivot(Sheet2)
Call readPivot(Sheet3)
'再計算を自動に戻す
Application.Calculation = xlCalculationAutomatic
Workbooks(Uri_book).Saved = True
Workbooks(Uri_book).Close
'計画フォルダ内の全ブックを更新する
Call UpdateAllBooks(Kei_folder)
Application.ScreenUpdating = True
t2 = Timer
MsgBox ("処理完了 所要時間(秒)=" & t2 - t1)
warnP = ""
'未処理のピボットがあるなら表示する
If dicT.count > 0 Then
For Each key In dicT
warnP = warnP & key & vbLf
Next
End If
If warnP <> "" Then
MsgBox ("ピボットの以下の部門(支店)|所属(課)|項目名(A~N)のデータが未処理です" & vbLf & warnP)
End If
End Sub
'計画フォルダ内の全てのブックを更新する
Private Sub UpdateAllBooks(ByVal Kei_folder As String)
Dim bookname As String
bookname = Dir(Kei_folder & "\*.xlsx", vbNormal)
If bookname = "" Then
MsgBox (Kei_folder & "内に拠点別ブックが存在しません。")
End
End If
'全てのブックを更新する
Do While bookname <> ""
Call Update1Book(Kei_folder, bookname)
bookname = Dir()
Loop
End Sub
'1つのブックを更新する
Private Sub Update1Book(ByVal Kei_folder As String, ByVal bookname As String)
Dim ws As Worksheet
Dim i As Long
Application.Calculation = xlCalculationManual
Workbooks.Open Kei_folder & "\" & bookname
Set ws = Worksheets("拠点計")
Call Update1Sheet(bookname, ws)
Application.Calculation = xlCalculationAutomatic
'ブックを保存し、閉じる
Workbooks(bookname).Save
Workbooks(bookname).Close
End Sub
'1つのシートを更新する
Private Sub Update1Sheet(ByVal bookname As String, ByVal ws As Worksheet)
'拠点シートに値を設定する
'3部
Call set_value(bookname, ws, "A", 9)
Call set_value(bookname, ws, "B", 10)
Call set_value(bookname, ws, "C", 11)
Call set_value(bookname, ws, "D", 12)
Call set_value(bookname, ws, "E", 13)
Call set_value(bookname, ws, "F", 14)
Call set_value(bookname, ws, "G", 15)
Call set_value(bookname, ws, "H", 16)
'1部
Call set_value(bookname, ws, "I", 18)
Call set_value(bookname, ws, "J", 19)
Call set_value(bookname, ws, "K", 20)
'2部
Call set_value(bookname, ws, "L", 22)
Call set_value(bookname, ws, "M", 23)
Call set_value(bookname, ws, "N", 24)
End Sub
'個人シートに値の設定を行う
Private Sub set_value(ByVal bookname As String, ByVal ws As Worksheet, ByVal item As String, ByVal row As Long)
Dim col As Long
Dim key As String
'カラム位置の設定
col = trg_col
key = ws.Cells(2, "C").Value & "|" & ws.Cells(3, "C").Value & "|" & item
If dicT.exists(key) = True Then
ws.Cells(row, col).Value = dicT(key)
'登録したキーの削除
dicT.Remove (key)
Else
'該当データなし
ws.Cells(row, col).Value = ""
End If
End Sub
'ピボットテーブル読み込み
Private Sub readPivot(ByVal sheet_name As String)
Dim index As Long
Dim sh As Worksheet
Dim maxrow As Long
Dim row As Long
Dim dmonth As String
Dim key As String
Dim item As String
Dim arari As Variant
index = 0
dmonth = trg_mm & "月"
Set sh = Worksheets(sheet_name)
maxrow = sh.Cells(Rows.count, "A").End(xlUp).row 'Sheet1 A列最大行
'3行から最終行まで繰り返す
For row = 3 To maxrow
'集計月に一致するなら、テーブルに格納する
If sh.Cells(row, "A").Text = dmonth Then
Select Case sheet_name
Case Sheet1
Call get_arari1(sheet_name, sh, row, item, arari)
Case Sheet2
Call get_arari2(sheet_name, sh, row, item, arari)
Case Sheet3
Call get_arari3(sheet_name, sh, row, item, arari)
End Select
'支店名+課名+項目名
key = sh.Cells(row, "D").Value & "|" & sh.Cells(row, "E").Value & "|" & item
If dicT.exists(key) = False Then
'最初のデータ
dicT(key) = arari
Else
'以降のデータ
dicT(key) = dicT(key) + arari
End If
End If
Next
End Sub
'1部の粗利取得
Private Sub get_arari1(ByVal sheet_name As String, ByVal sh As Worksheet, ByVal row As Long, ByRef item As String, ByRef arari As Variant)
Dim ix As Long
Call checkvalue1(sheet_name, sh, row, "K")
arari = sh.Cells(row, "K").Value
Call checkvalue2(sheet_name, sh, row, "F", 1, 2, False)
Call checkvalue2(sheet_name, sh, row, "G", 1, 1, True)
If sh.Cells(row, "G").Value = 1 Then
ix = 2
Else
ix = sh.Cells(row, "F").Value - 1
End If
item = item_tbl1(ix)
End Sub
'2部の粗利取得
Private Sub get_arari2(ByVal sheet_name As String, ByVal sh As Worksheet, ByVal row As Long, ByRef item As String, ByRef arari As Variant)
Dim ix As Long
Call checkvalue1(sheet_name, sh, row, "H")
arari = sh.Cells(row, "H").Value
Call checkvalue2(sheet_name, sh, row, "B", 4, 6, False)
ix = sh.Cells(row, "B").Value - 4
item = item_tbl2(ix)
End Sub
'3部の粗利取得
Private Sub get_arari3(ByVal sheet_name As String, ByVal sh As Worksheet, ByVal row As Long, ByRef item As String, ByRef arari As Variant)
Dim ix As Long
Call checkvalue1(sheet_name, sh, row, "J")
arari = sh.Cells(row, "J").Value
Call checkvalue2(sheet_name, sh, row, "F", 1, 8, False)
ix = sh.Cells(row, "F").Value - 1
item = item_tbl3(ix)
End Sub
'データのニューメリックチェックを行う
Private Sub checkvalue1(ByVal sheet_name As String, ByVal sh As Worksheet, ByVal row As Long, ByVal col As String)
If IsNumeric(sh.Cells(row, col).Value) = False Then
sh.Activate
sh.Cells(row, col).Select
Application.Calculation = xlCalculationAutomatic
MsgBox (sheet_name & "のデータ不正、以下の情報をメモしてください" & vbLf & row & "行" & col & "列")
MsgBox (sh.Cells(row, col).Text)
End
End If
End Sub
'データのニューメリックチェックを行い、範囲チェックを行う
Private Sub checkvalue2(ByVal sheet_name As String, ByVal sh As Worksheet, ByVal row As Long, ByVal col As String, ByVal lowval As Long, ByVal highval As Long, ByVal allow_sp As Boolean)
If allow_sp = True And sh.Cells(row, col).Value = "" Then
Exit Sub
End If
Call checkvalue1(sheet_name, sh, row, col)
If sh.Cells(row, col).Value > highval Or sh.Cells(row, col).Value < lowval Then
sh.Activate
sh.Cells(row, col).Select
Application.Calculation = xlCalculationAutomatic
MsgBox (sheet_name & "のデータ不正、以下の情報をメモしてください" & vbLf & row & "行" & col & "列")
MsgBox (sh.Cells(row, col).Text)
End
End If
End Sub
'指定月から指定月対応のカラム位置(実績の計)を計算する
'カラム位置は1からの連番
'10月= 9... 9月=80
Private Function GetColNumber(ByVal mm As Long)
Dim ix As Long
Dim arr As Variant
'1月、2月、3月、4月、4月、5月、6月、7月、8月、9月、10月、11月、12月の先頭からの相対位置
arr = Array(4, 5, 6, 9, 10, 11, 13, 14, 15, 0, 1, 2)
ix = arr(mm - 1)
GetColNumber = 3 + 6 * ix + 6
End Function
T3B0aW9uIEV4cGxpY2l0CiAgICBEaW0gZGljVCBBcyBPYmplY3QgICAgICAgICAgICAn6L6e5pu444CA44Kt44O877ya5pSv5bqX5ZCN77yL6Kqy5ZCN77yL6aCF55uu5ZCN77yIQe+9nk7vvIkg5YCk77ya57KX5Yip44Gu5YCk44Gu5ZCI6KiI5YCkCiAgICBDb25zdCBTaGVldDEgQXMgU3RyaW5nID0gIjHpg6jjg5Tjg5zjg4Pjg4giCiAgICBDb25zdCBTaGVldDIgQXMgU3RyaW5nID0gIjLpg6jjg5Tjg5zjg4Pjg4giCiAgICBDb25zdCBTaGVldDMgQXMgU3RyaW5nID0gIjPpg6jjg5Tjg5zjg4Pjg4giCiAgICBEaW0gVXJpX2Jvb2sgQXMgU3RyaW5nICAgICAgICAgICAgICAn57KX5Yip44OH44O844K/44OW44OD44Kv5ZCNCiAgICBEaW0gdHJnX21tIEFzIExvbmcgICAgICAgICAgICAgICAgICAn5oyH5a6a5pyICiAgICBEaW0gSmlzX2ZvbGRlciBBcyBTdHJpbmcgICAgICAgICAgICAn5a6f57i+44OV44Kp44Or44OACiAgICBEaW0gS2VpX2ZvbGRlciBBcyBTdHJpbmcgICAgICAgICAgICAn6KiI55S744OV44Kp44Or44OACiAgICBEaW0gdHJnX2NvbCBBcyBMb25nICAgICAgICAgICAgICAgICAn566X5Ye644Kr44Op44Og55Wq5Y+3CiAgICBEaW0gd2FyblAgQXMgU3RyaW5nICAgICAgICAgICAgICAgICAn6K2m5ZGK44OH44O844K/CiAgICBEaW0gaXRlbV90YmwxIEFzIFZhcmlhbnQgICAgICAgICAgICAnMemDqOOBrumgheebruWQjeODhuODvOODluODqwogICAgRGltIGl0ZW1fdGJsMiBBcyBWYXJpYW50ICAgICAgICAgICAgJzLpg6jjga7poIXnm67lkI3jg4bjg7zjg5bjg6sKICAgIERpbSBpdGVtX3RibDMgQXMgVmFyaWFudCAgICAgICAgICAgICcz6YOo44Gu6aCF55uu5ZCN44OG44O844OW44OrClB1YmxpYyBTdWIg5oug54K55Yil57KX5Yip6ZuG6KiIKCkKICAgIERpbSB3cyBBcyBXb3Jrc2hlZXQKICAgIERpbSBtbSwgZXh0IEFzIFZhcmlhbnQKICAgIERpbSB0MSwgdDIgQXMgVmFyaWFudAogICAgRGltIFVyaV9wYXRoIEFzIFN0cmluZwogICAgRGltIGtleSBBcyBWYXJpYW50CiAgICBTZXQgZGljVCA9IENyZWF0ZU9iamVjdCgiU2NyaXB0aW5nLkRpY3Rpb25hcnkiKQogICAgU2V0IHdzID0gV29ya3NoZWV0cygi57KX5Yip6ZuG6KiIIikKICAgICfpoIXnm67lkI3jg4bjg7zjg5bjg6vkvZzmiJAKICAgIGl0ZW1fdGJsMSA9IEFycmF5KCJJIiwgIkoiLCAiSyIpCiAgICBpdGVtX3RibDIgPSBBcnJheSgiTCIsICJNIiwgIk4iKQogICAgaXRlbV90YmwzID0gQXJyYXkoIkEiLCAiQiIsICJDIiwgIkQiLCAiRSIsICJGIiwgIkciLCAiSCIpCiAgICBtbSA9IHdzLkNlbGxzKDIsICJBIikuVmFsdWUgICAgICAgICAgICAgICAgICfpm4boqIjmnIgKICAgIElmIG1tID0gIiIgT3IgSXNOdW1lcmljKG1tKSA9IEZhbHNlIFRoZW4KICAgICAgICBNc2dCb3ggKCLpm4boqIjmnIjjgYzkuI3mraMiKQogICAgICAgIEV4aXQgU3ViCiAgICBFbmQgSWYKICAgIHRyZ19tbSA9IG1tCiAgICBJZiB0cmdfbW0gPCAxIE9yIHRyZ19tbSA+IDEyIFRoZW4KICAgICAgICBNc2dCb3ggKCLpm4boqIjmnIgoMS0xMinjgYznr4Tlm7LlpJYiKQogICAgICAgIEV4aXQgU3ViCiAgICBFbmQgSWYKICAgICfpm4boqIjmnIjjgavlr77lv5zjgZnjgovjgqvjg6njg6DkvY3nva7jgpLlj5blvpcKICAgIHRyZ19jb2wgPSBHZXRDb2xOdW1iZXIodHJnX21tKQogICAgSmlzX2ZvbGRlciA9IHdzLkNlbGxzKDIsICJCIikuVmFsdWUgICAgICAgICAgICAn5a6f57i+44OV44Kp44Or44OA5ZCNCiAgICBVcmlfYm9vayA9IHdzLkNlbGxzKDIsICJDIikuVmFsdWUgICAgICAgICAgICAgICfnspfliKnjg4fjg7zjgr/jg5XjgqHjgqTjg6sKICAgICfmi6HlvLXlrZDjga7jg4Hjgqfjg4Pjgq8KICAgIGV4dCA9IFJpZ2h0KExDYXNlKFVyaV9ib29rKSwgNSkKICAgIElmIGV4dCA8PiAiLnhsc3giIEFuZCBleHQgPD4gIi54bHNtIiBUaGVuCiAgICAgICAgTXNnQm94ICgi57KX5Yip44OH44O844K/44OV44Kh44Kk44Or5ZCN44GM5LiN5q2jIikKICAgICAgICBFeGl0IFN1YgogICAgRW5kIElmCiAgICBLZWlfZm9sZGVyID0gd3MuQ2VsbHMoMiwgIkQiKS5WYWx1ZSAgICAgICAgICAgICfoqIjnlLvjg5Xjgqnjg6vjg4DlkI0KICAgIAogICAgSWYgRGlyKEppc19mb2xkZXIsIHZiRGlyZWN0b3J5KSA9ICIiIFRoZW4KICAgICAgICBNc2dCb3ggKCLlrp/nuL7jg5Xjgqnjg6vjg4DjgYzlrZjlnKjjgZfjgb7jgZvjgpM8IiAmIEppc19mb2xkZXIgJiAiPiIpCiAgICAgICAgRXhpdCBTdWIKICAgIEVuZCBJZgogICAgSWYgRGlyKEtlaV9mb2xkZXIsIHZiRGlyZWN0b3J5KSA9ICIiIFRoZW4KICAgICAgICBNc2dCb3ggKCLoqIjnlLvjg5Xjgqnjg6vjg4DjgYzlrZjlnKjjgZfjgb7jgZvjgpM8IiAmIEtlaV9mb2xkZXIgJiAiPiIpCiAgICAgICAgRXhpdCBTdWIKICAgIEVuZCBJZgogICAgJ+eyl+WIqeODh+ODvOOCv+ODluODg+OCr+WQjeioreWumgogICAgVXJpX3BhdGggPSBKaXNfZm9sZGVyICYgIlwiICYgVXJpX2Jvb2sKICAgIElmIERpcihVcmlfcGF0aCwgdmJOb3JtYWwpID0gIiIgVGhlbgogICAgICAgIE1zZ0JveCAoVXJpX3BhdGggJiAi44GM5a2Y5Zyo44GX44G+44Gb44KTIikKICAgICAgICBFeGl0IFN1YgogICAgRW5kIElmCiAgCiAgICBJZiBNc2dCb3godHJnX21tICYgIuaciOOBrueyl+WIqeOCkiIgJiBVcmlfYm9vayAmICLjgYvjgonpm4boqIjjgZfjgb7jgZkiLCB2Yk9LQ2FuY2VsKSA8PiB2Yk9LIFRoZW4gRXhpdCBTdWIKICAgIHQxID0gVGltZXIKICAgIEFwcGxpY2F0aW9uLlNjcmVlblVwZGF0aW5nID0gRmFsc2UKICAgICflho3oqIjnrpfjgpLmiYvli5XjgavoqK3lrpoKICAgIEFwcGxpY2F0aW9uLkNhbGN1bGF0aW9uID0geGxDYWxjdWxhdGlvbk1hbnVhbAogICAgJ+WjsuS4iuaYjue0sOODluODg+OCr+WQjeOCquODvOODl+ODswogICAgV29ya2Jvb2tzLk9wZW4gVXJpX3BhdGgKICAgIFdvcmtib29rcyhVcmlfYm9vaykuQWN0aXZhdGUKICAgICfjg5Tjg5zjg4Pjg4jjg4fjg7zjgr/oqq3jgb/ovrzjgb8KICAgIENhbGwgcmVhZFBpdm90KFNoZWV0MSkKICAgIENhbGwgcmVhZFBpdm90KFNoZWV0MikKICAgIENhbGwgcmVhZFBpdm90KFNoZWV0MykKICAgICflho3oqIjnrpfjgpLoh6rli5XjgavmiLvjgZkKICAgIEFwcGxpY2F0aW9uLkNhbGN1bGF0aW9uID0geGxDYWxjdWxhdGlvbkF1dG9tYXRpYwogICAgV29ya2Jvb2tzKFVyaV9ib29rKS5TYXZlZCA9IFRydWUKICAgIFdvcmtib29rcyhVcmlfYm9vaykuQ2xvc2UKICAgICfoqIjnlLvjg5Xjgqnjg6vjg4DlhoXjga7lhajjg5bjg4Pjgq/jgpLmm7TmlrDjgZnjgosKICAgIENhbGwgVXBkYXRlQWxsQm9va3MoS2VpX2ZvbGRlcikKICAgIEFwcGxpY2F0aW9uLlNjcmVlblVwZGF0aW5nID0gVHJ1ZQogICAgdDIgPSBUaW1lcgogICAgTXNnQm94ICgi5Yem55CG5a6M5LqGIOaJgOimgeaZgumWkyjnp5IpPSIgJiB0MiAtIHQxKQogICAgd2FyblAgPSAiIgogICAgJ+acquWHpueQhuOBruODlOODnOODg+ODiOOBjOOBguOCi+OBquOCieihqOekuuOBmeOCiwogICAgSWYgZGljVC5jb3VudCA+IDAgVGhlbgogICAgICAgIEZvciBFYWNoIGtleSBJbiBkaWNUCiAgICAgICAgICAgIHdhcm5QID0gd2FyblAgJiBrZXkgJiB2YkxmCiAgICAgICAgTmV4dAogICAgRW5kIElmCiAgICBJZiB3YXJuUCA8PiAiIiBUaGVuCiAgICAgICAgTXNnQm94ICgi44OU44Oc44OD44OI44Gu5Lul5LiL44Gu6YOo6ZaA77yI5pSv5bqX77yJfOaJgOWxnu+8iOiqsu+8iXzpoIXnm67lkI0oQe+9nk4p44Gu44OH44O844K/44GM5pyq5Yem55CG44Gn44GZIiAmIHZiTGYgJiB3YXJuUCkKICAgIEVuZCBJZgpFbmQgU3ViCgon6KiI55S744OV44Kp44Or44OA5YaF44Gu5YWo44Gm44Gu44OW44OD44Kv44KS5pu05paw44GZ44KLClByaXZhdGUgU3ViIFVwZGF0ZUFsbEJvb2tzKEJ5VmFsIEtlaV9mb2xkZXIgQXMgU3RyaW5nKQogICAgRGltIGJvb2tuYW1lIEFzIFN0cmluZwogICAgYm9va25hbWUgPSBEaXIoS2VpX2ZvbGRlciAmICJcKi54bHN4IiwgdmJOb3JtYWwpCiAgICBJZiBib29rbmFtZSA9ICIiIFRoZW4KICAgICAgICBNc2dCb3ggKEtlaV9mb2xkZXIgJiAi5YaF44Gr5oug54K55Yil44OW44OD44Kv44GM5a2Y5Zyo44GX44G+44Gb44KT44CCIikKICAgICAgICBFbmQKICAgIEVuZCBJZgogICAgJ+WFqOOBpuOBruODluODg+OCr+OCkuabtOaWsOOBmeOCiwogICAgRG8gV2hpbGUgYm9va25hbWUgPD4gIiIKICAgICAgICBDYWxsIFVwZGF0ZTFCb29rKEtlaV9mb2xkZXIsIGJvb2tuYW1lKQogICAgICAgIGJvb2tuYW1lID0gRGlyKCkKICAgIExvb3AKRW5kIFN1YgoKJ++8keOBpOOBruODluODg+OCr+OCkuabtOaWsOOBmeOCiwpQcml2YXRlIFN1YiBVcGRhdGUxQm9vayhCeVZhbCBLZWlfZm9sZGVyIEFzIFN0cmluZywgQnlWYWwgYm9va25hbWUgQXMgU3RyaW5nKQogICAgRGltIHdzIEFzIFdvcmtzaGVldAogICAgRGltIGkgQXMgTG9uZwogICAgQXBwbGljYXRpb24uQ2FsY3VsYXRpb24gPSB4bENhbGN1bGF0aW9uTWFudWFsCiAgICBXb3JrYm9va3MuT3BlbiBLZWlfZm9sZGVyICYgIlwiICYgYm9va25hbWUKICAgIFNldCB3cyA9IFdvcmtzaGVldHMoIuaLoOeCueioiCIpCiAgICBDYWxsIFVwZGF0ZTFTaGVldChib29rbmFtZSwgd3MpCiAgICBBcHBsaWNhdGlvbi5DYWxjdWxhdGlvbiA9IHhsQ2FsY3VsYXRpb25BdXRvbWF0aWMKICAgICfjg5bjg4Pjgq/jgpLkv53lrZjjgZfjgIHplonjgZjjgosKICAgIFdvcmtib29rcyhib29rbmFtZSkuU2F2ZQogICAgV29ya2Jvb2tzKGJvb2tuYW1lKS5DbG9zZQpFbmQgU3ViCifvvJHjgaTjga7jgrfjg7zjg4jjgpLmm7TmlrDjgZnjgosKUHJpdmF0ZSBTdWIgVXBkYXRlMVNoZWV0KEJ5VmFsIGJvb2tuYW1lIEFzIFN0cmluZywgQnlWYWwgd3MgQXMgV29ya3NoZWV0KQogICAgJ+aLoOeCueOCt+ODvOODiOOBq+WApOOCkuioreWumuOBmeOCiwogICAgJzPpg6gKICAgIENhbGwgc2V0X3ZhbHVlKGJvb2tuYW1lLCB3cywgIkEiLCA5KQogICAgQ2FsbCBzZXRfdmFsdWUoYm9va25hbWUsIHdzLCAiQiIsIDEwKQogICAgQ2FsbCBzZXRfdmFsdWUoYm9va25hbWUsIHdzLCAiQyIsIDExKQogICAgQ2FsbCBzZXRfdmFsdWUoYm9va25hbWUsIHdzLCAiRCIsIDEyKQogICAgQ2FsbCBzZXRfdmFsdWUoYm9va25hbWUsIHdzLCAiRSIsIDEzKQogICAgQ2FsbCBzZXRfdmFsdWUoYm9va25hbWUsIHdzLCAiRiIsIDE0KQogICAgQ2FsbCBzZXRfdmFsdWUoYm9va25hbWUsIHdzLCAiRyIsIDE1KQogICAgQ2FsbCBzZXRfdmFsdWUoYm9va25hbWUsIHdzLCAiSCIsIDE2KQogICAgJzHpg6gKICAgIENhbGwgc2V0X3ZhbHVlKGJvb2tuYW1lLCB3cywgIkkiLCAxOCkKICAgIENhbGwgc2V0X3ZhbHVlKGJvb2tuYW1lLCB3cywgIkoiLCAxOSkKICAgIENhbGwgc2V0X3ZhbHVlKGJvb2tuYW1lLCB3cywgIksiLCAyMCkKICAgICcy6YOoCiAgICBDYWxsIHNldF92YWx1ZShib29rbmFtZSwgd3MsICJMIiwgMjIpCiAgICBDYWxsIHNldF92YWx1ZShib29rbmFtZSwgd3MsICJNIiwgMjMpCiAgICBDYWxsIHNldF92YWx1ZShib29rbmFtZSwgd3MsICJOIiwgMjQpCkVuZCBTdWIKJ+WAi+S6uuOCt+ODvOODiOOBq+WApOOBruioreWumuOCkuihjOOBhgpQcml2YXRlIFN1YiBzZXRfdmFsdWUoQnlWYWwgYm9va25hbWUgQXMgU3RyaW5nLCBCeVZhbCB3cyBBcyBXb3Jrc2hlZXQsIEJ5VmFsIGl0ZW0gQXMgU3RyaW5nLCBCeVZhbCByb3cgQXMgTG9uZykKICAgIERpbSBjb2wgQXMgTG9uZwogICAgRGltIGtleSBBcyBTdHJpbmcKICAgICfjgqvjg6njg6DkvY3nva7jga7oqK3lrpoKICAgIGNvbCA9IHRyZ19jb2wKICAgIGtleSA9IHdzLkNlbGxzKDIsICJDIikuVmFsdWUgJiAifCIgJiB3cy5DZWxscygzLCAiQyIpLlZhbHVlICYgInwiICYgaXRlbQogICAgSWYgZGljVC5leGlzdHMoa2V5KSA9IFRydWUgVGhlbgogICAgICAgIHdzLkNlbGxzKHJvdywgY29sKS5WYWx1ZSA9IGRpY1Qoa2V5KQogICAgICAgICfnmbvpjLLjgZfjgZ/jgq3jg7zjga7liYrpmaQKICAgICAgICBkaWNULlJlbW92ZSAoa2V5KQogICAgRWxzZQogICAgICAgICfoqbLlvZPjg4fjg7zjgr/jgarjgZcKICAgICAgICB3cy5DZWxscyhyb3csIGNvbCkuVmFsdWUgPSAiIgogICAgRW5kIElmCkVuZCBTdWIKJ+ODlOODnOODg+ODiOODhuODvOODluODq+iqreOBv+i+vOOBvwpQcml2YXRlIFN1YiByZWFkUGl2b3QoQnlWYWwgc2hlZXRfbmFtZSBBcyBTdHJpbmcpCiAgICBEaW0gaW5kZXggQXMgTG9uZwogICAgRGltIHNoIEFzIFdvcmtzaGVldAogICAgRGltIG1heHJvdyBBcyBMb25nCiAgICBEaW0gcm93IEFzIExvbmcKICAgIERpbSBkbW9udGggQXMgU3RyaW5nCiAgICBEaW0ga2V5IEFzIFN0cmluZwogICAgRGltIGl0ZW0gQXMgU3RyaW5nCiAgICBEaW0gYXJhcmkgQXMgVmFyaWFudAogICAgaW5kZXggPSAwCiAgICBkbW9udGggPSB0cmdfbW0gJiAi5pyIIgogICAgU2V0IHNoID0gV29ya3NoZWV0cyhzaGVldF9uYW1lKQogICAgbWF4cm93ID0gc2guQ2VsbHMoUm93cy5jb3VudCwgIkEiKS5FbmQoeGxVcCkucm93ICdTaGVldDEgQeWIl+acgOWkp+ihjAogICAgJzPooYzjgYvjgonmnIDntYLooYzjgb7jgafnubDjgorov5TjgZkKICAgIEZvciByb3cgPSAzIFRvIG1heHJvdwogICAgICAgICfpm4boqIjmnIjjgavkuIDoh7TjgZnjgovjgarjgonjgIHjg4bjg7zjg5bjg6vjgavmoLzntI3jgZnjgosKICAgICAgICBJZiBzaC5DZWxscyhyb3csICJBIikuVGV4dCA9IGRtb250aCBUaGVuCiAgICAgICAgICAgIFNlbGVjdCBDYXNlIHNoZWV0X25hbWUKICAgICAgICAgICAgQ2FzZSBTaGVldDEKICAgICAgICAgICAgICAgIENhbGwgZ2V0X2FyYXJpMShzaGVldF9uYW1lLCBzaCwgcm93LCBpdGVtLCBhcmFyaSkKICAgICAgICAgICAgQ2FzZSBTaGVldDIKICAgICAgICAgICAgICAgIENhbGwgZ2V0X2FyYXJpMihzaGVldF9uYW1lLCBzaCwgcm93LCBpdGVtLCBhcmFyaSkKICAgICAgICAgICAgQ2FzZSBTaGVldDMKICAgICAgICAgICAgICAgIENhbGwgZ2V0X2FyYXJpMyhzaGVldF9uYW1lLCBzaCwgcm93LCBpdGVtLCBhcmFyaSkKICAgICAgICAgICAgRW5kIFNlbGVjdAogICAgICAgICAgICAn5pSv5bqX5ZCN77yL6Kqy5ZCN77yL6aCF55uu5ZCNCiAgICAgICAgICAgIGtleSA9IHNoLkNlbGxzKHJvdywgIkQiKS5WYWx1ZSAmICJ8IiAmIHNoLkNlbGxzKHJvdywgIkUiKS5WYWx1ZSAmICJ8IiAmIGl0ZW0KICAgICAgICAgICAgSWYgZGljVC5leGlzdHMoa2V5KSA9IEZhbHNlIFRoZW4KICAgICAgICAgICAgICAgICfmnIDliJ3jga7jg4fjg7zjgr8KICAgICAgICAgICAgICAgIGRpY1Qoa2V5KSA9IGFyYXJpCiAgICAgICAgICAgIEVsc2UKICAgICAgICAgICAgICAgICfku6XpmY3jga7jg4fjg7zjgr8KICAgICAgICAgICAgICAgIGRpY1Qoa2V5KSA9IGRpY1Qoa2V5KSArIGFyYXJpCiAgICAgICAgICAgIEVuZCBJZgogICAgICAgIEVuZCBJZgogICAgTmV4dApFbmQgU3ViCicx6YOo44Gu57KX5Yip5Y+W5b6XClByaXZhdGUgU3ViIGdldF9hcmFyaTEoQnlWYWwgc2hlZXRfbmFtZSBBcyBTdHJpbmcsIEJ5VmFsIHNoIEFzIFdvcmtzaGVldCwgQnlWYWwgcm93IEFzIExvbmcsIEJ5UmVmIGl0ZW0gQXMgU3RyaW5nLCBCeVJlZiBhcmFyaSBBcyBWYXJpYW50KQogICAgRGltIGl4IEFzIExvbmcKICAgIENhbGwgY2hlY2t2YWx1ZTEoc2hlZXRfbmFtZSwgc2gsIHJvdywgIksiKQogICAgYXJhcmkgPSBzaC5DZWxscyhyb3csICJLIikuVmFsdWUKICAgIENhbGwgY2hlY2t2YWx1ZTIoc2hlZXRfbmFtZSwgc2gsIHJvdywgIkYiLCAxLCAyLCBGYWxzZSkKICAgIENhbGwgY2hlY2t2YWx1ZTIoc2hlZXRfbmFtZSwgc2gsIHJvdywgIkciLCAxLCAxLCBUcnVlKQogICAgSWYgc2guQ2VsbHMocm93LCAiRyIpLlZhbHVlID0gMSBUaGVuCiAgICAgICAgaXggPSAyCiAgICBFbHNlCiAgICAgICAgaXggPSBzaC5DZWxscyhyb3csICJGIikuVmFsdWUgLSAxCiAgICBFbmQgSWYKICAgIGl0ZW0gPSBpdGVtX3RibDEoaXgpCkVuZCBTdWIKJzLpg6jjga7nspfliKnlj5blvpcKUHJpdmF0ZSBTdWIgZ2V0X2FyYXJpMihCeVZhbCBzaGVldF9uYW1lIEFzIFN0cmluZywgQnlWYWwgc2ggQXMgV29ya3NoZWV0LCBCeVZhbCByb3cgQXMgTG9uZywgQnlSZWYgaXRlbSBBcyBTdHJpbmcsIEJ5UmVmIGFyYXJpIEFzIFZhcmlhbnQpCiAgICBEaW0gaXggQXMgTG9uZwogICAgQ2FsbCBjaGVja3ZhbHVlMShzaGVldF9uYW1lLCBzaCwgcm93LCAiSCIpCiAgICBhcmFyaSA9IHNoLkNlbGxzKHJvdywgIkgiKS5WYWx1ZQogICAgQ2FsbCBjaGVja3ZhbHVlMihzaGVldF9uYW1lLCBzaCwgcm93LCAiQiIsIDQsIDYsIEZhbHNlKQogICAgaXggPSBzaC5DZWxscyhyb3csICJCIikuVmFsdWUgLSA0CiAgICBpdGVtID0gaXRlbV90YmwyKGl4KQpFbmQgU3ViCicz6YOo44Gu57KX5Yip5Y+W5b6XClByaXZhdGUgU3ViIGdldF9hcmFyaTMoQnlWYWwgc2hlZXRfbmFtZSBBcyBTdHJpbmcsIEJ5VmFsIHNoIEFzIFdvcmtzaGVldCwgQnlWYWwgcm93IEFzIExvbmcsIEJ5UmVmIGl0ZW0gQXMgU3RyaW5nLCBCeVJlZiBhcmFyaSBBcyBWYXJpYW50KQogICAgRGltIGl4IEFzIExvbmcKICAgIENhbGwgY2hlY2t2YWx1ZTEoc2hlZXRfbmFtZSwgc2gsIHJvdywgIkoiKQogICAgYXJhcmkgPSBzaC5DZWxscyhyb3csICJKIikuVmFsdWUKICAgIENhbGwgY2hlY2t2YWx1ZTIoc2hlZXRfbmFtZSwgc2gsIHJvdywgIkYiLCAxLCA4LCBGYWxzZSkKICAgIGl4ID0gc2guQ2VsbHMocm93LCAiRiIpLlZhbHVlIC0gMQogICAgaXRlbSA9IGl0ZW1fdGJsMyhpeCkKRW5kIFN1Ygon44OH44O844K/44Gu44OL44Ol44O844Oh44Oq44OD44Kv44OB44Kn44OD44Kv44KS6KGM44GGClByaXZhdGUgU3ViIGNoZWNrdmFsdWUxKEJ5VmFsIHNoZWV0X25hbWUgQXMgU3RyaW5nLCBCeVZhbCBzaCBBcyBXb3Jrc2hlZXQsIEJ5VmFsIHJvdyBBcyBMb25nLCBCeVZhbCBjb2wgQXMgU3RyaW5nKQogICAgSWYgSXNOdW1lcmljKHNoLkNlbGxzKHJvdywgY29sKS5WYWx1ZSkgPSBGYWxzZSBUaGVuCiAgICAgICAgc2guQWN0aXZhdGUKICAgICAgICBzaC5DZWxscyhyb3csIGNvbCkuU2VsZWN0CiAgICAgICAgQXBwbGljYXRpb24uQ2FsY3VsYXRpb24gPSB4bENhbGN1bGF0aW9uQXV0b21hdGljCiAgICAgICAgTXNnQm94IChzaGVldF9uYW1lICYgIuOBruODh+ODvOOCv+S4jeato+OAgeS7peS4i+OBruaDheWgseOCkuODoeODouOBl+OBpuOBj+OBoOOBleOBhCIgJiB2YkxmICYgcm93ICYgIuihjCIgJiBjb2wgJiAi5YiXIikKICAgICAgICBNc2dCb3ggKHNoLkNlbGxzKHJvdywgY29sKS5UZXh0KQogICAgICAgIEVuZAogICAgRW5kIElmCkVuZCBTdWIKJ+ODh+ODvOOCv+OBruODi+ODpeODvOODoeODquODg+OCr+ODgeOCp+ODg+OCr+OCkuihjOOBhOOAgeevhOWbsuODgeOCp+ODg+OCr+OCkuihjOOBhgpQcml2YXRlIFN1YiBjaGVja3ZhbHVlMihCeVZhbCBzaGVldF9uYW1lIEFzIFN0cmluZywgQnlWYWwgc2ggQXMgV29ya3NoZWV0LCBCeVZhbCByb3cgQXMgTG9uZywgQnlWYWwgY29sIEFzIFN0cmluZywgQnlWYWwgbG93dmFsIEFzIExvbmcsIEJ5VmFsIGhpZ2h2YWwgQXMgTG9uZywgQnlWYWwgYWxsb3dfc3AgQXMgQm9vbGVhbikKICAgIElmIGFsbG93X3NwID0gVHJ1ZSBBbmQgc2guQ2VsbHMocm93LCBjb2wpLlZhbHVlID0gIiIgVGhlbgogICAgICAgIEV4aXQgU3ViCiAgICBFbmQgSWYKICAgIENhbGwgY2hlY2t2YWx1ZTEoc2hlZXRfbmFtZSwgc2gsIHJvdywgY29sKQogICAgSWYgc2guQ2VsbHMocm93LCBjb2wpLlZhbHVlID4gaGlnaHZhbCBPciBzaC5DZWxscyhyb3csIGNvbCkuVmFsdWUgPCBsb3d2YWwgVGhlbgogICAgICAgIHNoLkFjdGl2YXRlCiAgICAgICAgc2guQ2VsbHMocm93LCBjb2wpLlNlbGVjdAogICAgICAgIEFwcGxpY2F0aW9uLkNhbGN1bGF0aW9uID0geGxDYWxjdWxhdGlvbkF1dG9tYXRpYwogICAgICAgIE1zZ0JveCAoc2hlZXRfbmFtZSAmICLjga7jg4fjg7zjgr/kuI3mraPjgIHku6XkuIvjga7mg4XloLHjgpLjg6Hjg6LjgZfjgabjgY/jgaDjgZXjgYQiICYgdmJMZiAmIHJvdyAmICLooYwiICYgY29sICYgIuWIlyIpCiAgICAgICAgTXNnQm94IChzaC5DZWxscyhyb3csIGNvbCkuVGV4dCkKICAgICAgICBFbmQKICAgIEVuZCBJZgpFbmQgU3ViCgon5oyH5a6a5pyI44GL44KJ5oyH5a6a5pyI5a++5b+c44Gu44Kr44Op44Og5L2N572u77yI5a6f57i+44Gu6KiI77yJ44KS6KiI566X44GZ44KLCifjgqvjg6njg6DkvY3nva7jga8x44GL44KJ44Gu6YCj55WqCicxMOaciD0gOS4uLiA55pyIPTgwClByaXZhdGUgRnVuY3Rpb24gR2V0Q29sTnVtYmVyKEJ5VmFsIG1tIEFzIExvbmcpCiAgICBEaW0gaXggQXMgTG9uZwogICAgRGltIGFyciBBcyBWYXJpYW50CiAgICAnMeaciOOAgTLmnIjjgIEz5pyI44CBNOaciOOAge+8lOaciOOAge+8leaciOOAge+8luaciOOAgTfmnIjjgIE45pyI44CBOeaciOOAgTEw5pyI44CBMTHmnIjjgIExMuaciOOBruWFiOmgreOBi+OCieOBruebuOWvvuS9jee9rgogICAgYXJyID0gQXJyYXkoNCwgNSwgNiwgOSwgMTAsIDExLCAxMywgMTQsIDE1LCAwLCAxLCAyKQogICAgaXggPSBhcnIobW0gLSAxKQogICAgR2V0Q29sTnVtYmVyID0gMyArIDYgKiBpeCArIDYKRW5kIEZ1bmN0aW9uCgo=