Option Explicit
    Const TRG_SHEET As String = "全体"          '集計先シート名
    Const SRC_SHEET As String = "実績"          '集計元シート名
    Const ROW_COUNT_OF_1BLOCK As Long = 28      '1ブロックの行数
    Const ROW_START_NO As Long = 6              'データの開始行
    Const ZENNEN As Long = 0                    '前年
    Const TOUNEN As Long = 1                    '当年
    Dim dicKCD As Object 'キー：分類区分コード　値：相対行番号（0オリジン）
    Dim dicRow As Object 'キー：開始行番号 　値：true
    Dim dicT As Object 'キー：支店名＋課名 値：開始行番号
    Dim srowAllSum As Long  '全体合計の開始行
    Dim UriArray As Variant '売上集計対象　分類コード（82、86、85、87、88）
Public Sub 新全体集計()
    Dim maxrow As Long
    Dim i As Long
    Dim j As Long
    Dim wrow As Long
    Dim wcol As Long
    Dim srow As Long    '開始行
    Dim scol As Long    '開始列
    Dim siten As String    '支店名
    Dim ka As String    '課名
    Dim key As Variant    'キー
    Dim t1 As Variant
    Dim t2 As Variant
    Dim ksh As Worksheet    '管理シート
    Dim tsh As Worksheet    '全体シート
    Dim zen_folder As String    '前年フォルダー
    Dim tou_folder As String    '当年フォルダー
    Dim zen_file As String      '前年実績ファイル
    Dim tou_file As String      '当年実績ファイル
    Dim block_count As Long     '全体シートのブロック数
    Set dicKCD = CreateObject("Scripting.Dictionary")
    Set dicT = CreateObject("Scripting.Dictionary")
    Set dicRow = CreateObject("Scripting.Dictionary")
    Set tsh = Worksheets(TRG_SHEET)
    Set ksh = Worksheets("管理")
    UriArray = Array("82", "85", "86", "87", "88")  '売上を集計する分類コード
    '管理シートのチェック
    zen_folder = ksh.Range("B2").Value
    tou_folder = ksh.Range("B3").Value
    zen_file = ksh.Range("C2").Value
    tou_file = ksh.Range("C3").Value
    Call check_folder("前年フォルダ", zen_folder)
    Call check_folder("当年フォルダ", tou_folder)
    Call check_file("前年ファイル", zen_folder & "\" & zen_file)
    Call check_file("当年ファイル", tou_folder & "\" & tou_file)
    '全体シートのチェック
    maxrow = tsh.Cells(Rows.Count, "C").End(xlUp).row             'C列の最終行を求める
    If (maxrow - ROW_START_NO + 1) Mod ROW_COUNT_OF_1BLOCK <> 0 Then
        MsgBox ("全体シートの行数不正")
        Exit Sub
    End If
 
    'ブロック数算出
    block_count = (maxrow - ROW_START_NO + 1) \ ROW_COUNT_OF_1BLOCK
 
    '分類区分コードのリストを作成する
    For wrow = ROW_START_NO To (ROW_COUNT_OF_1BLOCK + ROW_START_NO - 1)
        key = CStr(tsh.Cells(wrow, "D").Value)
        If key <> "" Then
            If dicKCD.exists(key) = False Then
                dicKCD(key) = wrow - ROW_START_NO   '相対行を記憶
            Else
                MsgBox ("全体シートの分類区分コード重複")
                Exit Sub
            End If
        End If
    Next
    'ブロック開始行番号記憶
    For i = 1 To block_count    '1～最後のブロックまで処理
        srow = GetRowNumber(i)
        dicRow(srow) = True
    Next
 
    '全体シートの支店名・課名を記憶
    For wrow = ROW_START_NO To maxrow
        If tsh.Cells(wrow, "A").Value <> "" Then    '支店名を取得
            siten = tsh.Cells(wrow, "A").Value
        End If
        If tsh.Cells(wrow, "B").Value <> "" Then    '課名を取得
            ka = tsh.Cells(wrow, "B").Value
        End If
        If siten = "全体合計" Then    '全体合計の記憶
            key = "|||合計"
            If dicT.exists(key) = False Then dicT(key) = wrow
        Else
            If Right(ka, 2) = "合計" Then    '支店合計の記憶
                key = siten & "||合計"
                If dicT.exists(key) = False Then dicT(key) = wrow
            End If
        End If
        key = siten & "|" & ka
        If dicT.exists(key) = False Then
            If dicRow.exists(wrow) = False Then
                MsgBox ("開始行不正 行番号=" & wrow & " 支店=" & siten & " 課=" & ka)
                Exit Sub
            End If
            dicT(key) = wrow    '行番号を記憶
        End If
    Next
    key = "|||合計"
    If dicT.exists(key) = False Then
        MsgBox ("全体合計が全体シートになし")
        Exit Sub
    End If
    srowAllSum = dicT(key)
    If MsgBox("集計を開始します", vbOKCancel) <> vbOK Then
        Exit Sub
    End If
 
    t1 = Timer
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    '全体シートクリア
    For i = 1 To block_count    '1～最後のブロックまで処理
        srow = GetRowNumber(i)
        For j = 1 To 12    '1月～12月まで処理
            scol = GetColNumber(j)
            Call blockClear(tsh, srow, scol)
        Next
    Next
    '前年データを集計
    Call Sum1Book(zen_folder, zen_file, ZENNEN)
    '当年データを集計
    Call Sum1Book(tou_folder, tou_file, TOUNEN)
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    t2 = Timer
    MsgBox ("処理完了 所要時間(秒)=" & t2 - t1)
End Sub
Private Sub check_folder(ByVal comment As String, ByVal path As String)
    If Dir(path, vbDirectory) = "" Then
        MsgBox (comment & "が不正[" & path & "]は存在しません")
        End
    End If
End Sub
Private Sub check_file(ByVal comment As String, ByVal path As String)
    If Dir(path, vbNormal) = "" Then
        MsgBox (comment & "が不正[" & path & "]は存在しません")
        End
    End If
End Sub
'１つのブックを集計する
Private Sub Sum1Book(ByVal src_folder As String, ByVal bookname As String, ByVal nendo As Long)
    Workbooks.Open src_folder & "\" & bookname
    Call Sum1Sheet(nendo)
    'ブックを保存しないで閉じる（更新していないので保存不要）
    Workbooks(bookname).Saved = True
    Workbooks(bookname).Close
End Sub
'１つのシートを集計する
Private Sub Sum1Sheet(ByVal nendo As Long)
    Dim ws As Worksheet
    Dim maxrow As Long
    Dim wrow As Long
    Dim wcol As String
    Dim mm As Long          '月
    Dim siten As String     '支店
    Dim ka As String        '課
    Dim key As Variant      '支店・課のキー
    Dim key2 As Variant     '支店合計のキー
    Dim bcd As String       '分類区分コード
    Dim val1 As Variant     'H列/I列の値
    Dim val2 As Variant     'J列の値
    Dim rowA As Long '全体シートの行
    Dim rowY As Long '全体シートの支店合計の行
    Dim rowZ As Long '全体シートの全体合計の行
    Dim scol As Long '全体シートの対応月の開始列
 
    Set ws = Worksheets(SRC_SHEET)
    maxrow = ws.Cells(Rows.Count, "A").End(xlUp).row             'A列の最終行を求める
    For wrow = 3 To maxrow
        mm = get_month(ws.Cells(wrow, "A").Text)
        If mm = -1 Then
            Call abort_proc(nendo, wrow, "A", "売上日付不正(" & ws.Cells(wrow, "A").Text & ")")
        End If
        siten = ws.Cells(wrow, "B").Value '支店名取得
        ka = ws.Cells(wrow, "C").Value '課名取得
        key = siten & "|" & ka
        If dicT.exists(key) = False Then
            Call abort_proc(nendo, wrow, "B", "支店・課が全体シートに未登録(" & key & ")")
        End If
        key2 = siten & "||合計"
        If dicT.exists(key2) = False Then
            Call abort_proc(nendo, wrow, "B", "支店合計が全体シートに未登録(" & siten & ")")
        End If
        bcd = ws.Cells(wrow, "F").Value '分類区分コード取得
        If dicKCD.exists(bcd) = False Then
            Call abort_proc(nendo, wrow, "F", "分類区分コードが全体シートに未登録(" & bcd & ")")
        End If
        If is_uriage(bcd) = True Then
            wcol = "I"
        Else
            wcol = "H"
        End If
        val1 = get_val(nendo, ws, wrow, wcol)
        val2 = get_val(nendo, ws, wrow, "J")
        rowA = dicT(key) + dicKCD(bcd)
        rowY = dicT(key2) + dicKCD(bcd)
        rowZ = srowAllSum + dicKCD(bcd)
        scol = GetColNumber(mm)
        '数量／価格の集計
        Call add_val(nendo, rowA, scol + 3, val1)
        Call add_val(nendo, rowY, scol + 3, val1)
        Call add_val(nendo, rowZ, scol + 3, val1)
        '粗利の集計
        Call add_val(nendo, rowA, scol + 8, val2)
        Call add_val(nendo, rowY, scol + 8, val2)
        Call add_val(nendo, rowZ, scol + 8, val2)
    Next
End Sub
'全体シートへ加算する
Private Sub add_val(ByVal nendo As Long, ByVal wrow As Long, ByVal wcol As Long, ByVal val As Variant)
    If val = "" Then Exit Sub
    If nendo = TOUNEN Then wcol = wcol + 1
    ThisWorkbook.Worksheets(TRG_SHEET).Cells(wrow, wcol).Value = ThisWorkbook.Worksheets(TRG_SHEET).Cells(wrow, wcol).Value + val
End Sub
'分類区分コードが売上の集計か否かを返す
'true:売上の集計
'false:数量の集計
Private Function is_uriage(ByVal bcd As String) As Boolean
    Dim i As Long
    is_uriage = True
    For i = 0 To UBound(UriArray)
        If UriArray(i) = bcd Then Exit Function
    Next
    is_uriage = False
End Function
'文字で表示された月（1月...12月）の月を取得する
Private Function get_month(ByVal mstr As String) As Long
    Dim mm As String
    get_month = -1
    If Right(mstr, 1) <> "月" Then Exit Function
    mm = Left(mstr, Len(mstr) - 1)
    If IsNumeric(mm) = False Then Exit Function
    get_month = CLng(mm)
    If get_month > 0 And get_month < 13 Then Exit Function
    get_month = -1
End Function
'指定行、指定列のデータの取得及びチェック
Private Function get_val(ByVal nendo As String, ByVal ws As Worksheet, ByVal wrow As Long, ByVal wcol As String) As Variant
    get_val = ws.Cells(wrow, wcol).Value
    If IsNumeric(get_val) = False Then
        Call abort_proc(nendo, wrow, wcol, " 列=" & wcol & " 不正データ(" & ws.Cells(wrow, wcol).Text & ")")
    End If
End Function
'マクロ停止処理
Private Sub abort_proc(ByVal nendo As Long, ByVal wrow As Long, ByVal wcol As Variant, ByVal msg As String)
    Dim name As String
    If nendo = ZENNEN Then
        name = "前年元データ"
    Else
        name = "当年元データ"
    End If
    MsgBox (name & " 行番号=" & wrow & " " & msg)
    Worksheets(SRC_SHEET).Activate
    Cells(wrow, wcol).Select
    Application.Calculation = xlCalculationAutomatic
    End
End Sub
'ブロッククリア（1課・1ケ月分）
Private Sub blockClear(ByVal ws As Worksheet, ByVal srow As Long, ByVal scol As Long)
    Dim wrow As Long
    Dim key As Variant
    For Each key In dicKCD
        wrow = dicKCD(key) + srow
        ws.Cells(wrow, scol + 3).Value = ""
        ws.Cells(wrow, scol + 4).Value = ""
        ws.Cells(wrow, scol + 8).Value = ""
        ws.Cells(wrow, scol + 9).Value = ""
    Next
End Sub
'指定月から指定月対応のカラム位置（計画数量）を計算する
'カラム位置は1からの連番
'10月=6 ... 9月=186
Private Function GetColNumber(ByVal mm As Long)
    Dim x, ix As Long
    If mm < 10 Then mm = mm + 12
    x = mm - 10
    ix = x + x \ 3 + x \ 6
    GetColNumber = 6 + 12 * ix
End Function
 
'指定ブロック番号の行番号を取得する
'1=6,2=34,21=566
Private Function GetRowNumber(ByVal blkNo As Long)
    GetRowNumber = ROW_START_NO + (blkNo - 1) * ROW_COUNT_OF_1BLOCK
End Function
 
'列番号を英文字に変換
Function ConvertToLetter(ByVal iCol As Integer) As String
    Dim iAlpha As Integer
    Dim iRemainder As Integer
    iAlpha = Int((iCol - 1) / 26)
    iRemainder = iCol - (iAlpha * 26)
    If iAlpha > 0 Then
        ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
        ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
End Function
 
 

T3B0aW9uIEV4cGxpY2l0CiAgICBDb25zdCBUUkdfU0hFRVQgQXMgU3RyaW5nID0gIuWFqOS9kyIgICAgICAgICAgJ+mbhuioiOWFiOOCt+ODvOODiOWQjQogICAgQ29uc3QgU1JDX1NIRUVUIEFzIFN0cmluZyA9ICLlrp/nuL4iICAgICAgICAgICfpm4boqIjlhYPjgrfjg7zjg4jlkI0KICAgIENvbnN0IFJPV19DT1VOVF9PRl8xQkxPQ0sgQXMgTG9uZyA9IDI4ICAgICAgJzHjg5bjg63jg4Pjgq/jga7ooYzmlbAKICAgIENvbnN0IFJPV19TVEFSVF9OTyBBcyBMb25nID0gNiAgICAgICAgICAgICAgJ+ODh+ODvOOCv+OBrumWi+Wni+ihjAogICAgQ29uc3QgWkVOTkVOIEFzIExvbmcgPSAwICAgICAgICAgICAgICAgICAgICAn5YmN5bm0CiAgICBDb25zdCBUT1VORU4gQXMgTG9uZyA9IDEgICAgICAgICAgICAgICAgICAgICflvZPlubQKICAgIERpbSBkaWNLQ0QgQXMgT2JqZWN0ICfjgq3jg7zvvJrliIbpoZ7ljLrliIbjgrPjg7zjg4njgIDlgKTvvJrnm7jlr77ooYznlarlj7fvvIgw44Kq44Oq44K444Oz77yJCiAgICBEaW0gZGljUm93IEFzIE9iamVjdCAn44Kt44O877ya6ZaL5aeL6KGM55Wq5Y+3IOOAgOWApO+8mnRydWUKICAgIERpbSBkaWNUIEFzIE9iamVjdCAn44Kt44O877ya5pSv5bqX5ZCN77yL6Kqy5ZCNIOWApO+8mumWi+Wni+ihjOeVquWPtwogICAgRGltIHNyb3dBbGxTdW0gQXMgTG9uZyAgJ+WFqOS9k+WQiOioiOOBrumWi+Wni+ihjAogICAgRGltIFVyaUFycmF5IEFzIFZhcmlhbnQgJ+WjsuS4iumbhuioiOWvvuixoeOAgOWIhumhnuOCs+ODvOODie+8iDgy44CBODbjgIE4NeOAgTg344CBODjvvIkKUHVibGljIFN1YiDmlrDlhajkvZPpm4boqIgoKQogICAgRGltIG1heHJvdyBBcyBMb25nCiAgICBEaW0gaSBBcyBMb25nCiAgICBEaW0gaiBBcyBMb25nCiAgICBEaW0gd3JvdyBBcyBMb25nCiAgICBEaW0gd2NvbCBBcyBMb25nCiAgICBEaW0gc3JvdyBBcyBMb25nICAgICfplovlp4vooYwKICAgIERpbSBzY29sIEFzIExvbmcgICAgJ+mWi+Wni+WIlwogICAgRGltIHNpdGVuIEFzIFN0cmluZyAgICAn5pSv5bqX5ZCNCiAgICBEaW0ga2EgQXMgU3RyaW5nICAgICfoqrLlkI0KICAgIERpbSBrZXkgQXMgVmFyaWFudCAgICAn44Kt44O8CiAgICBEaW0gdDEgQXMgVmFyaWFudAogICAgRGltIHQyIEFzIFZhcmlhbnQKICAgIERpbSBrc2ggQXMgV29ya3NoZWV0ICAgICfnrqHnkIbjgrfjg7zjg4gKICAgIERpbSB0c2ggQXMgV29ya3NoZWV0ICAgICflhajkvZPjgrfjg7zjg4gKICAgIERpbSB6ZW5fZm9sZGVyIEFzIFN0cmluZyAgICAn5YmN5bm044OV44Kp44Or44OA44O8CiAgICBEaW0gdG91X2ZvbGRlciBBcyBTdHJpbmcgICAgJ+W9k+W5tOODleOCqeODq+ODgOODvAogICAgRGltIHplbl9maWxlIEFzIFN0cmluZyAgICAgICfliY3lubTlrp/nuL7jg5XjgqHjgqTjg6sKICAgIERpbSB0b3VfZmlsZSBBcyBTdHJpbmcgICAgICAn5b2T5bm05a6f57i+44OV44Kh44Kk44OrCiAgICBEaW0gYmxvY2tfY291bnQgQXMgTG9uZyAgICAgJ+WFqOS9k+OCt+ODvOODiOOBruODluODreODg+OCr+aVsAogICAgU2V0IGRpY0tDRCA9IENyZWF0ZU9iamVjdCgiU2NyaXB0aW5nLkRpY3Rpb25hcnkiKQogICAgU2V0IGRpY1QgPSBDcmVhdGVPYmplY3QoIlNjcmlwdGluZy5EaWN0aW9uYXJ5IikKICAgIFNldCBkaWNSb3cgPSBDcmVhdGVPYmplY3QoIlNjcmlwdGluZy5EaWN0aW9uYXJ5IikKICAgIFNldCB0c2ggPSBXb3Jrc2hlZXRzKFRSR19TSEVFVCkKICAgIFNldCBrc2ggPSBXb3Jrc2hlZXRzKCLnrqHnkIYiKQogICAgVXJpQXJyYXkgPSBBcnJheSgiODIiLCAiODUiLCAiODYiLCAiODciLCAiODgiKSAgJ+WjsuS4iuOCkumbhuioiOOBmeOCi+WIhumhnuOCs+ODvOODiQogICAgJ+euoeeQhuOCt+ODvOODiOOBruODgeOCp+ODg+OCrwogICAgemVuX2ZvbGRlciA9IGtzaC5SYW5nZSgiQjIiKS5WYWx1ZQogICAgdG91X2ZvbGRlciA9IGtzaC5SYW5nZSgiQjMiKS5WYWx1ZQogICAgemVuX2ZpbGUgPSBrc2guUmFuZ2UoIkMyIikuVmFsdWUKICAgIHRvdV9maWxlID0ga3NoLlJhbmdlKCJDMyIpLlZhbHVlCiAgICBDYWxsIGNoZWNrX2ZvbGRlcigi5YmN5bm044OV44Kp44Or44OAIiwgemVuX2ZvbGRlcikKICAgIENhbGwgY2hlY2tfZm9sZGVyKCLlvZPlubTjg5Xjgqnjg6vjg4AiLCB0b3VfZm9sZGVyKQogICAgQ2FsbCBjaGVja19maWxlKCLliY3lubTjg5XjgqHjgqTjg6siLCB6ZW5fZm9sZGVyICYgIlwiICYgemVuX2ZpbGUpCiAgICBDYWxsIGNoZWNrX2ZpbGUoIuW9k+W5tOODleOCoeOCpOODqyIsIHRvdV9mb2xkZXIgJiAiXCIgJiB0b3VfZmlsZSkKICAgICflhajkvZPjgrfjg7zjg4jjga7jg4Hjgqfjg4Pjgq8KICAgIG1heHJvdyA9IHRzaC5DZWxscyhSb3dzLkNvdW50LCAiQyIpLkVuZCh4bFVwKS5yb3cgICAgICAgICAgICAgJ0PliJfjga7mnIDntYLooYzjgpLmsYLjgoHjgosKICAgIElmIChtYXhyb3cgLSBST1dfU1RBUlRfTk8gKyAxKSBNb2QgUk9XX0NPVU5UX09GXzFCTE9DSyA8PiAwIFRoZW4KICAgICAgICBNc2dCb3ggKCLlhajkvZPjgrfjg7zjg4jjga7ooYzmlbDkuI3mraMiKQogICAgICAgIEV4aXQgU3ViCiAgICBFbmQgSWYKICAgIAogICAgJ+ODluODreODg+OCr+aVsOeul+WHugogICAgYmxvY2tfY291bnQgPSAobWF4cm93IC0gUk9XX1NUQVJUX05PICsgMSkgXCBST1dfQ09VTlRfT0ZfMUJMT0NLCiAgICAKICAgICfliIbpoZ7ljLrliIbjgrPjg7zjg4njga7jg6rjgrnjg4jjgpLkvZzmiJDjgZnjgosKICAgIEZvciB3cm93ID0gUk9XX1NUQVJUX05PIFRvIChST1dfQ09VTlRfT0ZfMUJMT0NLICsgUk9XX1NUQVJUX05PIC0gMSkKICAgICAgICBrZXkgPSBDU3RyKHRzaC5DZWxscyh3cm93LCAiRCIpLlZhbHVlKQogICAgICAgIElmIGtleSA8PiAiIiBUaGVuCiAgICAgICAgICAgIElmIGRpY0tDRC5leGlzdHMoa2V5KSA9IEZhbHNlIFRoZW4KICAgICAgICAgICAgICAgIGRpY0tDRChrZXkpID0gd3JvdyAtIFJPV19TVEFSVF9OTyAgICfnm7jlr77ooYzjgpLoqJjmhrYKICAgICAgICAgICAgRWxzZQogICAgICAgICAgICAgICAgTXNnQm94ICgi5YWo5L2T44K344O844OI44Gu5YiG6aGe5Yy65YiG44Kz44O844OJ6YeN6KSHIikKICAgICAgICAgICAgICAgIEV4aXQgU3ViCiAgICAgICAgICAgIEVuZCBJZgogICAgICAgIEVuZCBJZgogICAgTmV4dAogICAgJ+ODluODreODg+OCr+mWi+Wni+ihjOeVquWPt+iomOaGtgogICAgRm9yIGkgPSAxIFRvIGJsb2NrX2NvdW50ICAgICcx772e5pyA5b6M44Gu44OW44Ot44OD44Kv44G+44Gn5Yem55CGCiAgICAgICAgc3JvdyA9IEdldFJvd051bWJlcihpKQogICAgICAgIGRpY1Jvdyhzcm93KSA9IFRydWUKICAgIE5leHQKICAgIAogICAgJ+WFqOS9k+OCt+ODvOODiOOBruaUr+W6l+WQjeODu+iqsuWQjeOCkuiomOaGtgogICAgRm9yIHdyb3cgPSBST1dfU1RBUlRfTk8gVG8gbWF4cm93CiAgICAgICAgSWYgdHNoLkNlbGxzKHdyb3csICJBIikuVmFsdWUgPD4gIiIgVGhlbiAgICAn5pSv5bqX5ZCN44KS5Y+W5b6XCiAgICAgICAgICAgIHNpdGVuID0gdHNoLkNlbGxzKHdyb3csICJBIikuVmFsdWUKICAgICAgICBFbmQgSWYKICAgICAgICBJZiB0c2guQ2VsbHMod3JvdywgIkIiKS5WYWx1ZSA8PiAiIiBUaGVuICAgICfoqrLlkI3jgpLlj5blvpcKICAgICAgICAgICAga2EgPSB0c2guQ2VsbHMod3JvdywgIkIiKS5WYWx1ZQogICAgICAgIEVuZCBJZgogICAgICAgIElmIHNpdGVuID0gIuWFqOS9k+WQiOioiCIgVGhlbiAgICAn5YWo5L2T5ZCI6KiI44Gu6KiY5oa2CiAgICAgICAgICAgIGtleSA9ICJ8fHzlkIjoqIgiCiAgICAgICAgICAgIElmIGRpY1QuZXhpc3RzKGtleSkgPSBGYWxzZSBUaGVuIGRpY1Qoa2V5KSA9IHdyb3cKICAgICAgICBFbHNlCiAgICAgICAgICAgIElmIFJpZ2h0KGthLCAyKSA9ICLlkIjoqIgiIFRoZW4gICAgJ+aUr+W6l+WQiOioiOOBruiomOaGtgogICAgICAgICAgICAgICAga2V5ID0gc2l0ZW4gJiAifHzlkIjoqIgiCiAgICAgICAgICAgICAgICBJZiBkaWNULmV4aXN0cyhrZXkpID0gRmFsc2UgVGhlbiBkaWNUKGtleSkgPSB3cm93CiAgICAgICAgICAgIEVuZCBJZgogICAgICAgIEVuZCBJZgogICAgICAgIGtleSA9IHNpdGVuICYgInwiICYga2EKICAgICAgICBJZiBkaWNULmV4aXN0cyhrZXkpID0gRmFsc2UgVGhlbgogICAgICAgICAgICBJZiBkaWNSb3cuZXhpc3RzKHdyb3cpID0gRmFsc2UgVGhlbgogICAgICAgICAgICAgICAgTXNnQm94ICgi6ZaL5aeL6KGM5LiN5q2jIOihjOeVquWPtz0iICYgd3JvdyAmICIg5pSv5bqXPSIgJiBzaXRlbiAmICIg6KqyPSIgJiBrYSkKICAgICAgICAgICAgICAgIEV4aXQgU3ViCiAgICAgICAgICAgIEVuZCBJZgogICAgICAgICAgICBkaWNUKGtleSkgPSB3cm93ICAgICfooYznlarlj7fjgpLoqJjmhrYKICAgICAgICBFbmQgSWYKICAgIE5leHQKICAgIGtleSA9ICJ8fHzlkIjoqIgiCiAgICBJZiBkaWNULmV4aXN0cyhrZXkpID0gRmFsc2UgVGhlbgogICAgICAgIE1zZ0JveCAoIuWFqOS9k+WQiOioiOOBjOWFqOS9k+OCt+ODvOODiOOBq+OBquOBlyIpCiAgICAgICAgRXhpdCBTdWIKICAgIEVuZCBJZgogICAgc3Jvd0FsbFN1bSA9IGRpY1Qoa2V5KQogICAgSWYgTXNnQm94KCLpm4boqIjjgpLplovlp4vjgZfjgb7jgZkiLCB2Yk9LQ2FuY2VsKSA8PiB2Yk9LIFRoZW4KICAgICAgICBFeGl0IFN1YgogICAgRW5kIElmCiAgICAKICAgIHQxID0gVGltZXIKICAgIEFwcGxpY2F0aW9uLlNjcmVlblVwZGF0aW5nID0gRmFsc2UKICAgIEFwcGxpY2F0aW9uLkNhbGN1bGF0aW9uID0geGxDYWxjdWxhdGlvbk1hbnVhbAogICAgJ+WFqOS9k+OCt+ODvOODiOOCr+ODquOCogogICAgRm9yIGkgPSAxIFRvIGJsb2NrX2NvdW50ICAgICcx772e5pyA5b6M44Gu44OW44Ot44OD44Kv44G+44Gn5Yem55CGCiAgICAgICAgc3JvdyA9IEdldFJvd051bWJlcihpKQogICAgICAgIEZvciBqID0gMSBUbyAxMiAgICAnMeaciO+9njEy5pyI44G+44Gn5Yem55CGCiAgICAgICAgICAgIHNjb2wgPSBHZXRDb2xOdW1iZXIoaikKICAgICAgICAgICAgQ2FsbCBibG9ja0NsZWFyKHRzaCwgc3Jvdywgc2NvbCkKICAgICAgICBOZXh0CiAgICBOZXh0CiAgICAn5YmN5bm044OH44O844K/44KS6ZuG6KiICiAgICBDYWxsIFN1bTFCb29rKHplbl9mb2xkZXIsIHplbl9maWxlLCBaRU5ORU4pCiAgICAn5b2T5bm044OH44O844K/44KS6ZuG6KiICiAgICBDYWxsIFN1bTFCb29rKHRvdV9mb2xkZXIsIHRvdV9maWxlLCBUT1VORU4pCiAgICBBcHBsaWNhdGlvbi5TY3JlZW5VcGRhdGluZyA9IFRydWUKICAgIEFwcGxpY2F0aW9uLkNhbGN1bGF0aW9uID0geGxDYWxjdWxhdGlvbkF1dG9tYXRpYwogICAgdDIgPSBUaW1lcgogICAgTXNnQm94ICgi5Yem55CG5a6M5LqGIOaJgOimgeaZgumWkyjnp5IpPSIgJiB0MiAtIHQxKQpFbmQgU3ViClByaXZhdGUgU3ViIGNoZWNrX2ZvbGRlcihCeVZhbCBjb21tZW50IEFzIFN0cmluZywgQnlWYWwgcGF0aCBBcyBTdHJpbmcpCiAgICBJZiBEaXIocGF0aCwgdmJEaXJlY3RvcnkpID0gIiIgVGhlbgogICAgICAgIE1zZ0JveCAoY29tbWVudCAmICLjgYzkuI3mraNbIiAmIHBhdGggJiAiXeOBr+WtmOWcqOOBl+OBvuOBm+OCkyIpCiAgICAgICAgRW5kCiAgICBFbmQgSWYKRW5kIFN1YgpQcml2YXRlIFN1YiBjaGVja19maWxlKEJ5VmFsIGNvbW1lbnQgQXMgU3RyaW5nLCBCeVZhbCBwYXRoIEFzIFN0cmluZykKICAgIElmIERpcihwYXRoLCB2Yk5vcm1hbCkgPSAiIiBUaGVuCiAgICAgICAgTXNnQm94IChjb21tZW50ICYgIuOBjOS4jeato1siICYgcGF0aCAmICJd44Gv5a2Y5Zyo44GX44G+44Gb44KTIikKICAgICAgICBFbmQKICAgIEVuZCBJZgpFbmQgU3ViCifvvJHjgaTjga7jg5bjg4Pjgq/jgpLpm4boqIjjgZnjgosKUHJpdmF0ZSBTdWIgU3VtMUJvb2soQnlWYWwgc3JjX2ZvbGRlciBBcyBTdHJpbmcsIEJ5VmFsIGJvb2tuYW1lIEFzIFN0cmluZywgQnlWYWwgbmVuZG8gQXMgTG9uZykKICAgIFdvcmtib29rcy5PcGVuIHNyY19mb2xkZXIgJiAiXCIgJiBib29rbmFtZQogICAgQ2FsbCBTdW0xU2hlZXQobmVuZG8pCiAgICAn44OW44OD44Kv44KS5L+d5a2Y44GX44Gq44GE44Gn6ZaJ44GY44KL77yI5pu05paw44GX44Gm44GE44Gq44GE44Gu44Gn5L+d5a2Y5LiN6KaB77yJCiAgICBXb3JrYm9va3MoYm9va25hbWUpLlNhdmVkID0gVHJ1ZQogICAgV29ya2Jvb2tzKGJvb2tuYW1lKS5DbG9zZQpFbmQgU3ViCifvvJHjgaTjga7jgrfjg7zjg4jjgpLpm4boqIjjgZnjgosKUHJpdmF0ZSBTdWIgU3VtMVNoZWV0KEJ5VmFsIG5lbmRvIEFzIExvbmcpCiAgICBEaW0gd3MgQXMgV29ya3NoZWV0CiAgICBEaW0gbWF4cm93IEFzIExvbmcKICAgIERpbSB3cm93IEFzIExvbmcKICAgIERpbSB3Y29sIEFzIFN0cmluZwogICAgRGltIG1tIEFzIExvbmcgICAgICAgICAgJ+aciAogICAgRGltIHNpdGVuIEFzIFN0cmluZyAgICAgJ+aUr+W6lwogICAgRGltIGthIEFzIFN0cmluZyAgICAgICAgJ+iqsgogICAgRGltIGtleSBBcyBWYXJpYW50ICAgICAgJ+aUr+W6l+ODu+iqsuOBruOCreODvAogICAgRGltIGtleTIgQXMgVmFyaWFudCAgICAgJ+aUr+W6l+WQiOioiOOBruOCreODvAogICAgRGltIGJjZCBBcyBTdHJpbmcgICAgICAgJ+WIhumhnuWMuuWIhuOCs+ODvOODiQogICAgRGltIHZhbDEgQXMgVmFyaWFudCAgICAgJ0jliJcvSeWIl+OBruWApAogICAgRGltIHZhbDIgQXMgVmFyaWFudCAgICAgJ0rliJfjga7lgKQKICAgIERpbSByb3dBIEFzIExvbmcgJ+WFqOS9k+OCt+ODvOODiOOBruihjAogICAgRGltIHJvd1kgQXMgTG9uZyAn5YWo5L2T44K344O844OI44Gu5pSv5bqX5ZCI6KiI44Gu6KGMCiAgICBEaW0gcm93WiBBcyBMb25nICflhajkvZPjgrfjg7zjg4jjga7lhajkvZPlkIjoqIjjga7ooYwKICAgIERpbSBzY29sIEFzIExvbmcgJ+WFqOS9k+OCt+ODvOODiOOBruWvvuW/nOaciOOBrumWi+Wni+WIlwogICAgICAKICAgIFNldCB3cyA9IFdvcmtzaGVldHMoU1JDX1NIRUVUKQogICAgbWF4cm93ID0gd3MuQ2VsbHMoUm93cy5Db3VudCwgIkEiKS5FbmQoeGxVcCkucm93ICAgICAgICAgICAgICdB5YiX44Gu5pyA57WC6KGM44KS5rGC44KB44KLCiAgICBGb3Igd3JvdyA9IDMgVG8gbWF4cm93CiAgICAgICAgbW0gPSBnZXRfbW9udGgod3MuQ2VsbHMod3JvdywgIkEiKS5UZXh0KQogICAgICAgIElmIG1tID0gLTEgVGhlbgogICAgICAgICAgICBDYWxsIGFib3J0X3Byb2MobmVuZG8sIHdyb3csICJBIiwgIuWjsuS4iuaXpeS7mOS4jeatoygiICYgd3MuQ2VsbHMod3JvdywgIkEiKS5UZXh0ICYgIikiKQogICAgICAgIEVuZCBJZgogICAgICAgIHNpdGVuID0gd3MuQ2VsbHMod3JvdywgIkIiKS5WYWx1ZSAn5pSv5bqX5ZCN5Y+W5b6XCiAgICAgICAga2EgPSB3cy5DZWxscyh3cm93LCAiQyIpLlZhbHVlICfoqrLlkI3lj5blvpcKICAgICAgICBrZXkgPSBzaXRlbiAmICJ8IiAmIGthCiAgICAgICAgSWYgZGljVC5leGlzdHMoa2V5KSA9IEZhbHNlIFRoZW4KICAgICAgICAgICAgQ2FsbCBhYm9ydF9wcm9jKG5lbmRvLCB3cm93LCAiQiIsICLmlK/lupfjg7voqrLjgYzlhajkvZPjgrfjg7zjg4jjgavmnKrnmbvpjLIoIiAmIGtleSAmICIpIikKICAgICAgICBFbmQgSWYKICAgICAgICBrZXkyID0gc2l0ZW4gJiAifHzlkIjoqIgiCiAgICAgICAgSWYgZGljVC5leGlzdHMoa2V5MikgPSBGYWxzZSBUaGVuCiAgICAgICAgICAgIENhbGwgYWJvcnRfcHJvYyhuZW5kbywgd3JvdywgIkIiLCAi5pSv5bqX5ZCI6KiI44GM5YWo5L2T44K344O844OI44Gr5pyq55m76YyyKCIgJiBzaXRlbiAmICIpIikKICAgICAgICBFbmQgSWYKICAgICAgICBiY2QgPSB3cy5DZWxscyh3cm93LCAiRiIpLlZhbHVlICfliIbpoZ7ljLrliIbjgrPjg7zjg4nlj5blvpcKICAgICAgICBJZiBkaWNLQ0QuZXhpc3RzKGJjZCkgPSBGYWxzZSBUaGVuCiAgICAgICAgICAgIENhbGwgYWJvcnRfcHJvYyhuZW5kbywgd3JvdywgIkYiLCAi5YiG6aGe5Yy65YiG44Kz44O844OJ44GM5YWo5L2T44K344O844OI44Gr5pyq55m76YyyKCIgJiBiY2QgJiAiKSIpCiAgICAgICAgRW5kIElmCiAgICAgICAgSWYgaXNfdXJpYWdlKGJjZCkgPSBUcnVlIFRoZW4KICAgICAgICAgICAgd2NvbCA9ICJJIgogICAgICAgIEVsc2UKICAgICAgICAgICAgd2NvbCA9ICJIIgogICAgICAgIEVuZCBJZgogICAgICAgIHZhbDEgPSBnZXRfdmFsKG5lbmRvLCB3cywgd3Jvdywgd2NvbCkKICAgICAgICB2YWwyID0gZ2V0X3ZhbChuZW5kbywgd3MsIHdyb3csICJKIikKICAgICAgICByb3dBID0gZGljVChrZXkpICsgZGljS0NEKGJjZCkKICAgICAgICByb3dZID0gZGljVChrZXkyKSArIGRpY0tDRChiY2QpCiAgICAgICAgcm93WiA9IHNyb3dBbGxTdW0gKyBkaWNLQ0QoYmNkKQogICAgICAgIHNjb2wgPSBHZXRDb2xOdW1iZXIobW0pCiAgICAgICAgJ+aVsOmHj++8j+S+oeagvOOBrumbhuioiAogICAgICAgIENhbGwgYWRkX3ZhbChuZW5kbywgcm93QSwgc2NvbCArIDMsIHZhbDEpCiAgICAgICAgQ2FsbCBhZGRfdmFsKG5lbmRvLCByb3dZLCBzY29sICsgMywgdmFsMSkKICAgICAgICBDYWxsIGFkZF92YWwobmVuZG8sIHJvd1osIHNjb2wgKyAzLCB2YWwxKQogICAgICAgICfnspfliKnjga7pm4boqIgKICAgICAgICBDYWxsIGFkZF92YWwobmVuZG8sIHJvd0EsIHNjb2wgKyA4LCB2YWwyKQogICAgICAgIENhbGwgYWRkX3ZhbChuZW5kbywgcm93WSwgc2NvbCArIDgsIHZhbDIpCiAgICAgICAgQ2FsbCBhZGRfdmFsKG5lbmRvLCByb3daLCBzY29sICsgOCwgdmFsMikKICAgIE5leHQKRW5kIFN1Ygon5YWo5L2T44K344O844OI44G45Yqg566X44GZ44KLClByaXZhdGUgU3ViIGFkZF92YWwoQnlWYWwgbmVuZG8gQXMgTG9uZywgQnlWYWwgd3JvdyBBcyBMb25nLCBCeVZhbCB3Y29sIEFzIExvbmcsIEJ5VmFsIHZhbCBBcyBWYXJpYW50KQogICAgSWYgdmFsID0gIiIgVGhlbiBFeGl0IFN1YgogICAgSWYgbmVuZG8gPSBUT1VORU4gVGhlbiB3Y29sID0gd2NvbCArIDEKICAgIFRoaXNXb3JrYm9vay5Xb3Jrc2hlZXRzKFRSR19TSEVFVCkuQ2VsbHMod3Jvdywgd2NvbCkuVmFsdWUgPSBUaGlzV29ya2Jvb2suV29ya3NoZWV0cyhUUkdfU0hFRVQpLkNlbGxzKHdyb3csIHdjb2wpLlZhbHVlICsgdmFsCkVuZCBTdWIKJ+WIhumhnuWMuuWIhuOCs+ODvOODieOBjOWjsuS4iuOBrumbhuioiOOBi+WQpuOBi+OCkui/lOOBmQondHJ1ZTrlo7LkuIrjga7pm4boqIgKJ2ZhbHNlOuaVsOmHj+OBrumbhuioiApQcml2YXRlIEZ1bmN0aW9uIGlzX3VyaWFnZShCeVZhbCBiY2QgQXMgU3RyaW5nKSBBcyBCb29sZWFuCiAgICBEaW0gaSBBcyBMb25nCiAgICBpc191cmlhZ2UgPSBUcnVlCiAgICBGb3IgaSA9IDAgVG8gVUJvdW5kKFVyaUFycmF5KQogICAgICAgIElmIFVyaUFycmF5KGkpID0gYmNkIFRoZW4gRXhpdCBGdW5jdGlvbgogICAgTmV4dAogICAgaXNfdXJpYWdlID0gRmFsc2UKRW5kIEZ1bmN0aW9uCifmloflrZfjgafooajnpLrjgZXjgozjgZ/mnIjvvIgx5pyILi4uMTLmnIjvvInjga7mnIjjgpLlj5blvpfjgZnjgosKUHJpdmF0ZSBGdW5jdGlvbiBnZXRfbW9udGgoQnlWYWwgbXN0ciBBcyBTdHJpbmcpIEFzIExvbmcKICAgIERpbSBtbSBBcyBTdHJpbmcKICAgIGdldF9tb250aCA9IC0xCiAgICBJZiBSaWdodChtc3RyLCAxKSA8PiAi5pyIIiBUaGVuIEV4aXQgRnVuY3Rpb24KICAgIG1tID0gTGVmdChtc3RyLCBMZW4obXN0cikgLSAxKQogICAgSWYgSXNOdW1lcmljKG1tKSA9IEZhbHNlIFRoZW4gRXhpdCBGdW5jdGlvbgogICAgZ2V0X21vbnRoID0gQ0xuZyhtbSkKICAgIElmIGdldF9tb250aCA+IDAgQW5kIGdldF9tb250aCA8IDEzIFRoZW4gRXhpdCBGdW5jdGlvbgogICAgZ2V0X21vbnRoID0gLTEKRW5kIEZ1bmN0aW9uCifmjIflrprooYzjgIHmjIflrprliJfjga7jg4fjg7zjgr/jga7lj5blvpflj4rjgbPjg4Hjgqfjg4Pjgq8KUHJpdmF0ZSBGdW5jdGlvbiBnZXRfdmFsKEJ5VmFsIG5lbmRvIEFzIFN0cmluZywgQnlWYWwgd3MgQXMgV29ya3NoZWV0LCBCeVZhbCB3cm93IEFzIExvbmcsIEJ5VmFsIHdjb2wgQXMgU3RyaW5nKSBBcyBWYXJpYW50CiAgICBnZXRfdmFsID0gd3MuQ2VsbHMod3Jvdywgd2NvbCkuVmFsdWUKICAgIElmIElzTnVtZXJpYyhnZXRfdmFsKSA9IEZhbHNlIFRoZW4KICAgICAgICBDYWxsIGFib3J0X3Byb2MobmVuZG8sIHdyb3csIHdjb2wsICIg5YiXPSIgJiB3Y29sICYgIiDkuI3mraPjg4fjg7zjgr8oIiAmIHdzLkNlbGxzKHdyb3csIHdjb2wpLlRleHQgJiAiKSIpCiAgICBFbmQgSWYKRW5kIEZ1bmN0aW9uCifjg57jgq/jg63lgZzmraLlh6bnkIYKUHJpdmF0ZSBTdWIgYWJvcnRfcHJvYyhCeVZhbCBuZW5kbyBBcyBMb25nLCBCeVZhbCB3cm93IEFzIExvbmcsIEJ5VmFsIHdjb2wgQXMgVmFyaWFudCwgQnlWYWwgbXNnIEFzIFN0cmluZykKICAgIERpbSBuYW1lIEFzIFN0cmluZwogICAgSWYgbmVuZG8gPSBaRU5ORU4gVGhlbgogICAgICAgIG5hbWUgPSAi5YmN5bm05YWD44OH44O844K/IgogICAgRWxzZQogICAgICAgIG5hbWUgPSAi5b2T5bm05YWD44OH44O844K/IgogICAgRW5kIElmCiAgICBNc2dCb3ggKG5hbWUgJiAiIOihjOeVquWPtz0iICYgd3JvdyAmICIgIiAmIG1zZykKICAgIFdvcmtzaGVldHMoU1JDX1NIRUVUKS5BY3RpdmF0ZQogICAgQ2VsbHMod3Jvdywgd2NvbCkuU2VsZWN0CiAgICBBcHBsaWNhdGlvbi5DYWxjdWxhdGlvbiA9IHhsQ2FsY3VsYXRpb25BdXRvbWF0aWMKICAgIEVuZApFbmQgU3ViCifjg5bjg63jg4Pjgq/jgq/jg6rjgqLvvIgx6Kqy44O7MeOCseaciOWIhu+8iQpQcml2YXRlIFN1YiBibG9ja0NsZWFyKEJ5VmFsIHdzIEFzIFdvcmtzaGVldCwgQnlWYWwgc3JvdyBBcyBMb25nLCBCeVZhbCBzY29sIEFzIExvbmcpCiAgICBEaW0gd3JvdyBBcyBMb25nCiAgICBEaW0ga2V5IEFzIFZhcmlhbnQKICAgIEZvciBFYWNoIGtleSBJbiBkaWNLQ0QKICAgICAgICB3cm93ID0gZGljS0NEKGtleSkgKyBzcm93CiAgICAgICAgd3MuQ2VsbHMod3Jvdywgc2NvbCArIDMpLlZhbHVlID0gIiIKICAgICAgICB3cy5DZWxscyh3cm93LCBzY29sICsgNCkuVmFsdWUgPSAiIgogICAgICAgIHdzLkNlbGxzKHdyb3csIHNjb2wgKyA4KS5WYWx1ZSA9ICIiCiAgICAgICAgd3MuQ2VsbHMod3Jvdywgc2NvbCArIDkpLlZhbHVlID0gIiIKICAgIE5leHQKRW5kIFN1Ygon5oyH5a6a5pyI44GL44KJ5oyH5a6a5pyI5a++5b+c44Gu44Kr44Op44Og5L2N572u77yI6KiI55S75pWw6YeP77yJ44KS6KiI566X44GZ44KLCifjgqvjg6njg6DkvY3nva7jga8x44GL44KJ44Gu6YCj55WqCicxMOaciD02IC4uLiA55pyIPTE4NgpQcml2YXRlIEZ1bmN0aW9uIEdldENvbE51bWJlcihCeVZhbCBtbSBBcyBMb25nKQogICAgRGltIHgsIGl4IEFzIExvbmcKICAgIElmIG1tIDwgMTAgVGhlbiBtbSA9IG1tICsgMTIKICAgIHggPSBtbSAtIDEwCiAgICBpeCA9IHggKyB4IFwgMyArIHggXCA2CiAgICBHZXRDb2xOdW1iZXIgPSA2ICsgMTIgKiBpeApFbmQgRnVuY3Rpb24KCifmjIflrprjg5bjg63jg4Pjgq/nlarlj7fjga7ooYznlarlj7fjgpLlj5blvpfjgZnjgosKJzE9NiwyPTM0LDIxPTU2NgpQcml2YXRlIEZ1bmN0aW9uIEdldFJvd051bWJlcihCeVZhbCBibGtObyBBcyBMb25nKQogICAgR2V0Um93TnVtYmVyID0gUk9XX1NUQVJUX05PICsgKGJsa05vIC0gMSkgKiBST1dfQ09VTlRfT0ZfMUJMT0NLCkVuZCBGdW5jdGlvbgoKJ+WIl+eVquWPt+OCkuiLseaWh+Wtl+OBq+WkieaPmwpGdW5jdGlvbiBDb252ZXJ0VG9MZXR0ZXIoQnlWYWwgaUNvbCBBcyBJbnRlZ2VyKSBBcyBTdHJpbmcKICAgIERpbSBpQWxwaGEgQXMgSW50ZWdlcgogICAgRGltIGlSZW1haW5kZXIgQXMgSW50ZWdlcgogICAgaUFscGhhID0gSW50KChpQ29sIC0gMSkgLyAyNikKICAgIGlSZW1haW5kZXIgPSBpQ29sIC0gKGlBbHBoYSAqIDI2KQogICAgSWYgaUFscGhhID4gMCBUaGVuCiAgICAgICAgQ29udmVydFRvTGV0dGVyID0gQ2hyKGlBbHBoYSArIDY0KQogICAgRW5kIElmCiAgICBJZiBpUmVtYWluZGVyID4gMCBUaGVuCiAgICAgICAgQ29udmVydFRvTGV0dGVyID0gQ29udmVydFRvTGV0dGVyICYgQ2hyKGlSZW1haW5kZXIgKyA2NCkKICAgIEVuZCBJZgpFbmQgRnVuY3Rpb24KCg==