Attribute VB_Name = "modDetail"
Option Explicit
'=====================================================================
' modDetail : ¼¼ºÎÇöȲ_¼¼¸ñº° (»ç¾÷Ç׸ñ 1·¹º§ ÃÑ¾× ¡æ ¼¼Ãâ¼¼¸ñº°) + ¿À¸¥ÂÊ ½½¶óÀ̼­
'=====================================================================
Public Const SH_GROUP As String = "¼¼ºÎÇöȲ"
Public Const TBL_GROUP As String = "tbl¼¼¸ñº°"
Private Const SC_GRP As String = "½½¶óÀ̼­_¼¼¸ñ_"
Private Const G_HDR As Long = 9
Private Const nC As Long = 15

Public Sub ¼¼¸ñº°ÇöȲ()
    SpeedOn "¼¼ºÎÇöȲ(¼¼¸ñº°) »ý¼º Áß..."
    On Error GoTo EH
    If Not ModelReady Then BuildModel
    BuildGrouped
    If SheetExists(SH_GROUP) Then BW().Worksheets(SH_GROUP).Activate
    SpeedOff
    Exit Sub
EH:
    SpeedOff
    MsgBox "¼¼ºÎÇöȲ(¼¼¸ñº°) ¿À·ù [" & gStep & "]: " & Err.Description, vbCritical
End Sub

Public Sub ¼¼¸ñ½½¶óÀ̼­¸®¼Â()
    DeleteOrClearSlicers SC_GRP, False
End Sub

Public Sub BuildGrouped()
    Dim src As Worksheet, ws As Worksheet, hr As Long, hm As Object, arr As Variant, lr As Long, lc As Long
    Dim cSeb As Long, cN1 As Long, cCode As Long, cSem As Long, cHy As Long, cJi As Long, cGyo As Long, cGeup As Long, cWon As Long
    Dim useB As Boolean, r As Long, k As String, sk As String, agg As Object, sub1 As Object
    Dim v As Variant, it As clsProject, keys As Collection, i As Long, row As Long, n As Long, T As Variant
    Dim lo As ListObject, rng As Range, sx As Single, sy As Single, dept As String, out() As Variant, cnt As Long, x As Long

    If Not ModelReady Then Exit Sub
    gStep = "¼¼¸ñº° Áý°è"
    Set agg = ItemSubtotals(cnt)
    If agg Is Nothing Then Exit Sub

    'Ãâ·Â ¹è¿­ (ÃѾ×Çà + ¼¼¸ñÇà)
    gStep = "¼¼¸ñº° Ç¥"
    ReDim out(1 To cnt + gItems.Count + 1, 1 To nC)
    Set keys = SortedKeys()
    For i = 1 To keys.Count
        Set it = gItems(keys(i))
        If it.ÀåǥŰ <> "" Then
            If agg.Exists(it.ÀåǥŰ) Then
                Set sub1 = agg(it.ÀåǥŰ)
                x = x + 1
                out(x, 1) = x: out(x, 2) = "»ç¾÷ÃѾ×"
                out(x, 3) = it.¼¼ºÎ»ç¾÷: out(x, 4) = it.»ç¾÷Ç׸ñ
                out(x, 5) = "(" & sub1.Count & "°³ ¼¼¸ñ)"
                out(x, 6) = it.¿¹»êÇö¾×: out(x, 7) = it.¿øÀÎÇàÀ§: out(x, 8) = it.ÁýÇà¾×: out(x, 9) = it.Àܾ×
                out(x, 10) = it.ÁýÇà·ü: out(x, 11) = it.ÁýÇà·ü: out(x, 12) = it.»óÅÂ
                out(x, 13) = IIf(it.ÆÀ = "", "(ÆÀ ¹ÌÁöÁ¤)", it.ÆÀ): out(x, 14) = IIf(it.´ã´çÀÚ = "", "(´ã´çÀÚ ¹ÌÁöÁ¤)", it.´ã´çÀÚ)
                out(x, 15) = IIf(it.°úÁ¦Ä«µå = "", "-", it.°úÁ¦Ä«µå)
                For Each v In sub1.keys
                    T = sub1(v)
                    x = x + 1
                    out(x, 1) = x: out(x, 2) = "¼¼¸ñ"
                    out(x, 3) = it.¼¼ºÎ»ç¾÷: out(x, 4) = it.»ç¾÷Ç׸ñ
                    out(x, 5) = "   " & T(0)
                    out(x, 6) = T(1): out(x, 7) = T(3): out(x, 8) = T(2): out(x, 9) = T(1) - T(2)
                    out(x, 10) = rate(T(2), T(1)): out(x, 11) = rate(T(2), T(1)): out(x, 12) = StatusText(T(1), rate(T(2), T(1)), "")
                    out(x, 13) = out(x - 1, 13): out(x, 14) = out(x - 1, 14): out(x, 15) = out(x - 1, 15)
                Next v
            End If
        End If
    Next i
    n = x

    '½ÃÆ®
    gStep = "¼¼¸ñº° ½ÃÆ®"
    DeleteOrClearSlicers SC_GRP, False          '½½¶óÀ̼­ À¯Áö(ÇÊÅ͸¸ ÇØÁ¦)
    Set ws = GetSheet(SH_GROUP, True)
    ws.Tab.color = ClrIndigo()
    ws.cells.Interior.color = RGB(255, 255, 255)
    ws.Columns.hidden = False
    ws.rows(1).RowHeight = 4: ws.rows("2:3").RowHeight = 14: ws.rows(4).RowHeight = 6
    AddHomeButton ws, 4, 4
    AddButton ws, "btnPrint", "Àμâ", "Àμâ", 68, 4, 50, 20, ClrNavy()
    AddButton ws, "btnRefresh", "»õ·Î°íħ(¸®¼Â)", "»õ·Î°íħ¹öÆ°", 122, 4, 90, 20, ClrGreen()

    dept = CfgGet("ºÎ¼­¸í")
    ws.Range("A5").Value2 = IIf(dept <> "", dept & " ", "") & "¼¼ºÎÇöȲ"
    ws.Range("A5").Font.Size = 20: ws.Range("A5").Font.Bold = True: ws.Range("A5").Font.name = FONT_NM: ws.Range("A5").Font.color = ClrNavy()
    ws.rows(5).RowHeight = 34
    ws.Range("A6").Value2 = "±âÁØÀÏ " & CfgGet("±âÁØÀÏ") & "   |   °»½Å " & NowText() & "   |   (´ÜÀ§: ¿ø)   |   ÆĶõ Çà = »ç¾÷Ç׸ñ ÃѾ×"
    ws.Range("A6").Font.Size = 9: ws.Range("A6").Font.color = ClrGray()
    ws.rows(6).RowHeight = 14

    If n = 0 Then Exit Sub
    ws.cells(G_HDR, 1).Resize(1, nC).Value2 = Array("No", "±¸ºÐ", "¼¼ºÎ»ç¾÷", "»ç¾÷Ç׸ñ", "¼¼Ãâ¼¼¸ñ", _
        "¿¹»êÇö¾×", "¿øÀÎÇàÀ§¾×", "ÁýÇà¾×", "ÁýÇàÀܾ×", "ÁýÇà·ü", "ÁøÇà", "»óÅÂ", "ÆÀ/ºÎ¼­", "´ã´çÀÚ", "´ÜÀ§°úÁ¦Ä«µå")
    ws.cells(G_HDR + 1, 1).Resize(n, nC).Value2 = out

    Set rng = ws.Range(ws.cells(G_HDR, 1), ws.cells(G_HDR + n, nC))
    Set lo = MakeTable(ws, rng, TBL_GROUP)
    StyleHeader lo.HeaderRowRange
    StyleBody lo.DataBodyRange
    With ws.Range(ws.cells(G_HDR + 1, 6), ws.cells(G_HDR + n, 9))
        .NumberFormat = "#,##0;[Red]-#,##0;""-""": .HorizontalAlignment = xlRight: .ShrinkToFit = True
    End With
    'ÁýÇà·ü¡¤ÁøÇà ÇÑ ¼¿ (% + ±×¶óµ¥ÀÌ¼Ç ¸·´ë), ÁøÇà ¿­ ¼û±è
    With ws.Range(ws.cells(G_HDR + 1, 10), ws.cells(G_HDR + n, 10))
        .NumberFormat = "0.0%": .HorizontalAlignment = xlRight: .Font.Bold = True: .IndentLevel = 1
    End With
    Dim ii As Long
    For ii = 1 To n
        BarByRate ws.cells(G_HDR + ii, 10), NzD(out(ii, 10))
    Next ii
    ws.cells(G_HDR, 10).Value2 = "ÁýÇà·ü¡¤ÁøÇà"
    ColorStatus ws.Range(ws.cells(G_HDR + 1, 12), ws.cells(G_HDR + n, 12))
    ws.Range(ws.cells(G_HDR + 1, 12), ws.cells(G_HDR + n, 12)).HorizontalAlignment = xlCenter
    ws.Range(ws.cells(G_HDR + 1, 1), ws.cells(G_HDR + n, 2)).HorizontalAlignment = xlCenter
    'ÃÑ¾× Çà °­Á¶ (Á¶°ÇºÎ ¼­½Ä)
    Dim fc As FormatCondition
    Set fc = lo.DataBodyRange.FormatConditions.Add(Type:=xlExpression, formula1:="=$B" & (G_HDR + 1) & "=""»ç¾÷ÃѾ×""")
    fc.Interior.color = RGB(229, 240, 255)
    fc.Font.Bold = True

    '¿ä¾à Çà: ÃÑ¾× Çุ ÇÕ»ê (ÇÊÅÍ ¹Ý¿µ)
    lo.ShowTotals = True
    lo.ListColumns(4).total.Value2 = "ÇÕ°è (»ç¾÷ÃÑ¾× Çà ±âÁØ)"
    lo.ListColumns(1).TotalsCalculation = xlTotalsCalculationNone
    lo.ListColumns(6).total.Formula = VisibleSumFormula(TBL_GROUP, "¿¹»êÇö¾×")
    lo.ListColumns(7).total.Formula = VisibleSumFormula(TBL_GROUP, "¿øÀÎÇàÀ§¾×")
    lo.ListColumns(8).total.Formula = VisibleSumFormula(TBL_GROUP, "ÁýÇà¾×")
    lo.ListColumns(9).total.Formula = VisibleSumFormula(TBL_GROUP, "ÁýÇàÀܾ×")
    lo.ListColumns(10).total.Formula = "=IFERROR(" & Mid(VisibleSumFormula(TBL_GROUP, "ÁýÇà¾×"), 2) & "/" & Mid(VisibleSumFormula(TBL_GROUP, "¿¹»êÇö¾×"), 2) & ",0)"
    With lo.TotalsRowRange
        .Font.name = FONT_NM: .Font.Bold = True: .Font.color = ClrNavy(): .Interior.color = ClrLight(): .ShrinkToFit = True
        .Borders(xlEdgeTop).LineStyle = xlContinuous: .Borders(xlEdgeTop).weight = xlMedium: .Borders(xlEdgeTop).color = ClrGray()
    End With
    ws.Range(lo.TotalsRowRange.cells(1, 6), lo.TotalsRowRange.cells(1, 9)).NumberFormat = "#,##0"
    lo.TotalsRowRange.cells(1, 10).NumberFormat = "0.0%"

    'KPI
    ws.Columns(1).ColumnWidth = 5: ws.Columns(2).ColumnWidth = 8: ws.Columns(3).ColumnWidth = 16: ws.Columns(4).ColumnWidth = 32
    ws.Columns(5).ColumnWidth = 30
    ws.Range(ws.Columns(6), ws.Columns(9)).ColumnWidth = 18: ws.Columns(10).ColumnWidth = 24: ws.Columns(11).hidden = True: ws.Columns(12).ColumnWidth = 11
    ws.Range(ws.Columns(13), ws.Columns(15)).hidden = True
    ws.Columns(nC + 1).ColumnWidth = 2

    KpiRow ws, 7, Array("¿¹»êÇö¾×", "ÁýÇà¾×", "ÁýÇàÀܾ×", "ÁýÇà·ü", "»ç¾÷ ¼ö"), _
        Array(VisibleSumFormula(TBL_GROUP, "¿¹»êÇö¾×"), VisibleSumFormula(TBL_GROUP, "ÁýÇà¾×"), VisibleSumFormula(TBL_GROUP, "ÁýÇàÀܾ×"), _
              "=IFERROR(" & Mid(VisibleSumFormula(TBL_GROUP, "ÁýÇà¾×"), 2) & "/" & Mid(VisibleSumFormula(TBL_GROUP, "¿¹»êÇö¾×"), 2) & ",0)", _
              "=SUMPRODUCT((" & TBL_GROUP & "[±¸ºÐ]=""»ç¾÷ÃѾ×"")*SUBTOTAL(103,OFFSET(" & TBL_GROUP & "[±¸ºÐ],ROW(" & TBL_GROUP & "[±¸ºÐ])-MIN(ROW(" & TBL_GROUP & "[±¸ºÐ])),0,1)))"), _
        Array("#,##0", "#,##0", "#,##0", "0.0%", "0"), Array(ClrNavy(), ClrBlue(), ClrOrange(), ClrGreen(), ClrIndigo())

    '½½¶óÀ̼­: Á¦¸ñ ¿À¸¥ÂÊ »ó´Ü(1~6Çà) °¡·Î ¹èÄ¡ - Ç¥ ¿· °ø°£ Àý¾à
    gStep = "¼¼¸ñº° ½½¶óÀ̼­"
    'Á¦¸ñ(A5, 20pt) ¿À¸¥ÂʺÎÅÍ ½ÃÀÛ, Æø Ãà¼Ò ¡æ ¼¼ºÎ»ç¾÷ ½½¶óÀ̼­±îÁö È­¸é ¾È¿¡
    sx = ws.cells(5, 4).Left: sy = 3
    If sx < 380 Then sx = 380
    '³ôÀÌ 76 ÅëÀÏ ¡æ 7~8Çà KPI Ä«µå¿Í °ãÄ¡Áö ¾ÊÀ½, ÆÀ/ºÎ¼­ Æø È®´ë
    AddSlicerTo lo, ws, "ÆÀ/ºÎ¼­", SC_GRP & "ÆÀ", sx, sy, 210, 76, 3: sx = sx + 214
    AddSlicerTo lo, ws, "´ã´çÀÚ", SC_GRP & "´ã´çÀÚ", sx, sy, 200, 76, 3: sx = sx + 204
    AddSlicerTo lo, ws, "´ÜÀ§°úÁ¦Ä«µå", SC_GRP & "´ÜÀ§°úÁ¦Ä«µå", sx, sy, 230, 76, 2: sx = sx + 234
    AddSlicerTo lo, ws, "¼¼ºÎ»ç¾÷", SC_GRP & "¼¼ºÎ»ç¾÷", sx, sy, 230, 76, 2

    ws.Activate
    ActiveWindow.FreezePanes = False
    ws.Range("A1").Select
    ws.cells(G_HDR + 1, 6).Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.DisplayGridlines = False
    ws.Range("A1").Select
End Sub

'ÀåÇ¥(102-B)¿¡¼­ »ç¾÷Ç׸ñÅ° -> (¼¼¸ñ -> Array(¼¼¸ñÇ¥½Ã, ¿¹»êÇö¾×, ÁýÇà¾×, ¿øÀÎÇàÀ§)) Áý°è (°ø¿ë)
Public Function ItemSubtotals(ByRef cnt As Long) As Object
    Dim src As Worksheet, hr As Long, hm As Object, arr As Variant, lr As Long, lc As Long, useB As Boolean
    Dim cSeb As Long, cN1 As Long, cCode As Long, cSem As Long, cHy As Long, cJi As Long, cGyo As Long, cGeup As Long, cWon As Long
    Dim r As Long, k As String, sk As String, agg As Object, sub1 As Object, T As Variant
    Set ItemSubtotals = Nothing: cnt = 0
    If SheetExists(SH_B) Then
        Set src = BW().Worksheets(SH_B): useB = True
    ElseIf SheetExists(SH_A) Then
        Set src = BW().Worksheets(SH_A): useB = False
    Else
        Exit Function
    End If
    hr = FindHeaderRow(src, "ȸ°è¿¬µµ", 10)
    If hr = 0 Then Exit Function
    Set hm = HeaderMap(src, hr)
    cSeb = HCol(hm, "¼¼ºÎ»ç¾÷"): cCode = HCol(hm, "¼¼Ãâ¼¼¸ñÄÚµå"): cSem = HCol(hm, "¼¼Ãâ¼¼¸ñ")
    cN1 = HCol(hm, IIf(useB, "»ç¾÷Ç׸ñ¸í1", "»ç¾÷Ç׸ñ1·¹º§"))
    cHy = HCol(hm, "¿¹»êÇö¾×"): cJi = HCol(hm, "ÁöÃâ±Ý¾×"): cWon = HCol(hm, "¿øÀÎÇàÀ§±Ý¾×")
    cGyo = HCol(hm, "ÀÏ»ó°æºñ±³ºÎ¾×", False): cGeup = HCol(hm, "ÀÏ»ó°æºñÁö±Þ¾×", False)
    lr = lastRow(src, 1): lc = src.cells(hr, src.Columns.Count).End(xlToLeft).Column
    If lr <= hr Then Exit Function
    arr = src.Range(src.cells(hr + 1, 1), src.cells(lr, lc)).Value2
    Set agg = CreateObject("Scripting.Dictionary")
    For r = 1 To UBound(arr, 1)
        If CleanText(arr(r, cCode)) <> "" Then
            k = MakeKey(CleanText(arr(r, cSeb)), CleanText(arr(r, cN1)))
            sk = CleanText(arr(r, cCode)) & " " & CleanText(arr(r, cSem))
            If Not agg.Exists(k) Then agg.Add k, CreateObject("Scripting.Dictionary")
            Set sub1 = agg(k)
            If sub1.Exists(sk) Then T = sub1(sk) Else T = Array(sk, 0#, 0#, 0#)
            T(1) = T(1) + NzD(arr(r, cHy))
            T(2) = T(2) + ExecAmount(arr, r, cJi, cGyo, cGeup)
            T(3) = T(3) + NzD(arr(r, cWon))
            sub1(sk) = T
            cnt = cnt + 1
        End If
    Next r
    Set ItemSubtotals = agg
End Function

'ÇÊÅÍ(½½¶óÀ̼­) ¹Ý¿µ + ±¸ºÐ="»ç¾÷ÃѾ×" Çุ ÇÕ»êÇÏ´Â ¼ö½Ä
Private Function VisibleSumFormula(ByVal tbl As String, ByVal col As String) As String
    VisibleSumFormula = "=SUMPRODUCT((" & tbl & "[±¸ºÐ]=""»ç¾÷ÃѾ×"")*SUBTOTAL(109,OFFSET(" & tbl & "[" & col & "],ROW(" & tbl & "[" & col & "])-MIN(ROW(" & tbl & "[" & col & "])),0,1)))"
End Function