Attribute VB_Name = "modBoard"
Option Explicit
'=====================================================================
' modBoard : 메인 시트 (버튼 + KPI + 사업별 집행 표 + 오른쪽 슬라이서)
'   - 표(ListObject) → 슬라이서(팀/담당자/세부사업/단위과제카드). 슬라이서용 열은 표 끝에 숨김
'   - 요구서 금액 ≠ 장표 금액 이면 예산현액을 빨간 글자 + 메모(요구서 금액/차이)
'=====================================================================
Public Const MAIN_HDR As Long = 9
Public Const TBL_MAIN As String = "tbl메인"
Private Const SC_MAIN As String = "슬라이서_메인_"
Private Const BTN_TOP As Single = 4
Private Const BTN_H As Single = 22

'메인 열
Private Const M_NO As Long = 1
Private Const M_SEB As Long = 2
Private Const M_NM As Long = 3
Private Const M_HY As Long = 4
Private Const M_EX As Long = 5
Private Const M_REM As Long = 6
Private Const M_RATE As Long = 7
Private Const M_BAR As Long = 8
Private Const M_ST As Long = 9
Private Const M_TEAM As Long = 10    '숨김 (슬라이서용)
Private Const M_OWN As Long = 11     '숨김
Private Const M_CARD As Long = 12    '숨김
Private Const M_MATCH As Long = 13   '숨김
Private Const M_LAST As Long = 13

'메인 시트 생성 + 버튼 배치
Public Sub SetupMainSheet()
    Dim ws As Worksheet, x As Single, g As Single
    Set ws = GetSheet(SH_MAIN)
    ws.Tab.color = ClrBlue()
    If ws.Index <> 1 Then ws.Move Before:=BW().Worksheets(1)
    ws.rows(1).RowHeight = 4
    ws.rows("2:3").RowHeight = 14
    ws.rows(4).RowHeight = 6
    x = 4: g = 4
    AddButton ws, "btn1", "① 세출예산요구서", "요구서선택", x, BTN_TOP, 96, BTN_H, ClrBlue(): x = x + 96 + g
    AddButton ws, "btn2", "② 장표(102-B)", "장표선택", x, BTN_TOP, 84, BTN_H, ClrBlue(): x = x + 84 + g
    AddButton ws, "btn3", "③ 새로고침(리셋)", "새로고침버튼", x, BTN_TOP, 96, BTN_H, ClrGreen(): x = x + 96 + g + 8
    On Error Resume Next
    ws.Shapes("btn7").Delete            '구버전 [슬라이서 리셋] 제거 (새로고침에 통합)
    On Error GoTo 0
    AddButton ws, "btn8", "인쇄", "인쇄", x, BTN_TOP, 46, BTN_H, ClrNavy(): x = x + 46 + g + 8
    AddButton ws, "btn9", "예산보드", "예산보드", x, BTN_TOP, 60, BTN_H, ClrIndigo(): x = x + 60 + g
    On Error Resume Next
    ws.Shapes("btnT").Delete
    On Error GoTo 0
    AddButton ws, "btn4", "세부현황", "세목별현황", x, BTN_TOP, 60, BTN_H, ClrIndigo(): x = x + 60 + g
    AddButton ws, "btnC", "세부산출내역", "세부산출내역", x, BTN_TOP, 80, BTN_H, ClrOrange(): x = x + 80 + g + 8
    AddButton ws, "btn5", "부서·업무담당자", "부서담당자", x, BTN_TOP, 92, BTN_H, ClrGray(): x = x + 92 + g
    AddButton ws, "btn6", "단위사업카드", "단위사업카드", x, BTN_TOP, 82, BTN_H, ClrGray(): x = x + 82 + g
    On Error Resume Next
    ws.Shapes("btnD").Delete           '구버전 [부서 선택] 버튼 제거 (② 장표 선택 시 부서 목록에서 선택)
    On Error GoTo 0
End Sub

Public Sub 메인슬라이서리셋()
    DeleteOrClearSlicers SC_MAIN, False
End Sub

'메인 보드 작성
Public Sub BuildMain()
    Dim ws As Worksheet, keys As Collection, i As Long, r As Long, it As clsProject
    Dim dept As String, arr As Variant, n As Long, lo As ListObject, rng As Range, nNoOwn As Long
    Dim sx As Single, sy As Single, lo0 As ListObject

    Set ws = GetSheet(SH_MAIN)
    SetupMainSheet
    gStep = "메인 초기화"
    DeleteOrClearSlicers SC_MAIN, False
    ws.Range(ws.rows(5), ws.rows(ws.rows.Count)).ClearComments
    ClearRowsKeepTables ws, 5                    '표 머리글·슬라이서 유지
    ws.Columns.hidden = False
    ws.cells.Interior.color = RGB(255, 255, 255)

    '제목 (5행)
    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("D5").Value2 = "기준일 " & CfgGet("기준일") & "   |   갱신 " & NowText() & "   |   (단위: 원)"
    ws.Range("D5").Font.Size = 9: ws.Range("D5").Font.color = ClrGray(): ws.Range("D5").VerticalAlignment = xlBottom

    If Not ModelReady Then Exit Sub
    Set keys = SortedKeys()
    n = keys.Count
    If n = 0 Then Exit Sub

    '표 데이터
    gStep = "메인 표"
    ws.cells(MAIN_HDR, 1).Resize(1, M_LAST).Value2 = Array("No", "세부사업", "사업항목", "예산현액", "집행액", "집행잔액", "집행률", "진행", "상태", _
        "팀/부서", "담당자", "단위과제카드", "매칭")
    ReDim arr(1 To n, 1 To M_LAST)
    For i = 1 To n
        Set it = gItems(keys(i))
        arr(i, M_NO) = i
        arr(i, M_SEB) = it.세부사업
        arr(i, M_NM) = it.사업항목
        arr(i, M_HY) = it.예산현액
        arr(i, M_EX) = it.집행액
        arr(i, M_REM) = it.잔액
        arr(i, M_RATE) = it.집행률
        arr(i, M_BAR) = it.집행률
        arr(i, M_ST) = it.상태
        arr(i, M_TEAM) = IIf(it.팀 = "", "(팀 미지정)", it.팀)
        arr(i, M_OWN) = IIf(it.담당자 = "", "(담당자 미지정)", it.담당자)
        arr(i, M_CARD) = IIf(it.과제카드 = "", "-", it.과제카드)
        arr(i, M_MATCH) = it.매칭
        If it.담당자 = "" Then nNoOwn = nNoOwn + 1
    Next i
    r = MAIN_HDR + 1
    ws.cells(r, 1).Resize(n, M_LAST).Value2 = arr

    '엑셀 표
    Set rng = ws.Range(ws.cells(MAIN_HDR, 1), ws.cells(MAIN_HDR + n, M_LAST))
    Set lo = MakeTable(ws, rng, TBL_MAIN)
    StyleHeader lo.HeaderRowRange
    StyleBody lo.DataBodyRange
    With ws.Range(ws.cells(r, M_HY), ws.cells(r + n - 1, M_REM))
        .NumberFormat = "#,##0;[Red]-#,##0;""-""": .HorizontalAlignment = xlRight: .ShrinkToFit = True
    End With
    '집행률·진행 한 셀: % 숫자 + 그라데이션 막대 (80%↑ 파랑 / 60~80% 초록 / 60%↓ 빨강), 진행 열은 숨김
    With ws.Range(ws.cells(r, M_RATE), ws.cells(r + n - 1, M_RATE))
        .NumberFormat = "0.0%": .HorizontalAlignment = xlRight: .Font.Bold = True: .IndentLevel = 1
    End With
    For i = 1 To n
        BarByRate ws.cells(r + i - 1, M_RATE), NzD(arr(i, M_RATE))
    Next i
    ws.cells(MAIN_HDR, M_RATE).Value2 = "집행률·진행"
    ws.Range(ws.cells(r, M_ST), ws.cells(r + n - 1, M_ST)).HorizontalAlignment = xlCenter
    ws.Range(ws.cells(r, M_NO), ws.cells(r + n - 1, M_NO)).HorizontalAlignment = xlCenter
    ColorStatus ws.Range(ws.cells(r, M_ST), ws.cells(r + n - 1, M_ST))

    '합계 행
    lo.ShowTotals = True
    lo.ListColumns(M_NM).total.Value2 = "합계"
    lo.ListColumns(M_NO).TotalsCalculation = xlTotalsCalculationNone
    lo.ListColumns(M_HY).TotalsCalculation = xlTotalsCalculationSum
    lo.ListColumns(M_EX).TotalsCalculation = xlTotalsCalculationSum
    lo.ListColumns(M_REM).TotalsCalculation = xlTotalsCalculationSum
    lo.ListColumns(M_RATE).total.Formula = "=IFERROR(SUBTOTAL(109,[집행액])/SUBTOTAL(109,[예산현액]),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, M_HY), lo.TotalsRowRange.cells(1, M_REM)).NumberFormat = "#,##0;[Red]-#,##0"
    lo.TotalsRowRange.cells(1, M_RATE).NumberFormat = "0.0%"

    'KPI (7~8행)
    gStep = "메인 KPI"
    '열너비 / 숨김 (KPI 배치 전에 확정)
    ws.Columns(M_NO).ColumnWidth = 5: ws.Columns(M_SEB).ColumnWidth = 18: ws.Columns(M_NM).ColumnWidth = 36
    ws.Range(ws.Columns(M_HY), ws.Columns(M_REM)).ColumnWidth = 18
    ws.Columns(M_RATE).ColumnWidth = 24: ws.Columns(M_ST).ColumnWidth = 11
    ws.Columns(M_BAR).hidden = True
    ws.Range(ws.Columns(M_TEAM), ws.Columns(M_MATCH)).hidden = True
    ws.Columns(M_LAST + 1).ColumnWidth = 2

    KpiRow ws, 7, Array("예산현액", "집행액", "집행잔액", "집행률", "사업 수"), _
        Array("=SUBTOTAL(109," & TBL_MAIN & "[예산현액])", "=SUBTOTAL(109," & TBL_MAIN & "[집행액])", "=SUBTOTAL(109," & TBL_MAIN & "[집행잔액])", _
              "=IFERROR(SUBTOTAL(109," & TBL_MAIN & "[집행액])/SUBTOTAL(109," & TBL_MAIN & "[예산현액]),0)", "=SUBTOTAL(103," & TBL_MAIN & "[사업항목])"), _
        Array("#,##0", "#,##0", "#,##0", "0.0%", "0"), Array(ClrNavy(), ClrBlue(), ClrOrange(), ClrGreen(), ClrIndigo())
    ws.rows(6).RowHeight = 6

    '슬라이서: 표 오른쪽 세로 고정
    gStep = "메인 슬라이서"
    sx = ws.cells(MAIN_HDR, M_LAST + 2).Left
    sy = 4
    '상태 슬라이서: 1열(항목 세로 나열) - 오른쪽 세로 배치 맨 위 (버튼·KPI와 겹치지 않음)
    AddSlicerTo lo, ws, "상태", SC_MAIN & "상태", sx, sy, 220, 128, 1: sy = sy + 132
    AddSlicerTo lo, ws, "팀/부서", SC_MAIN & "팀", sx, sy, 220, 128, 1: sy = sy + 132
    AddSlicerTo lo, ws, "담당자", SC_MAIN & "담당자", sx, sy, 220, 150, 2: sy = sy + 154
    AddSlicerTo lo, ws, "단위과제카드", SC_MAIN & "단위과제카드", sx, sy, 220, 118, 1: sy = sy + 122
    AddSlicerTo lo, ws, "세부사업", SC_MAIN & "세부사업", sx, sy, 220, 130, 1

    ws.Activate
    ActiveWindow.FreezePanes = False
    ws.Range("A1").Select
    ws.cells(MAIN_HDR + 1, M_HY).Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.DisplayGridlines = False
    ws.Range("A1").Select
End Sub

'인쇄 (활성 시트의 표/보드 영역만)
Public Sub 인쇄()
    Dim ws As Worksheet, lo As ListObject, lastR As Long, lastC As Long
    Set ws = ActiveSheet
    On Error GoTo EH
    If ws.name = SH_CHART Then
        PrintChartBoard
        Exit Sub
    End If
    If ws.ListObjects.Count = 0 Then
        MsgBox "인쇄할 표가 없습니다.", vbExclamation
        Exit Sub
    End If
    Set lo = ws.ListObjects(1)
    lastR = lo.Range.row + lo.Range.rows.Count - 1
    lastC = lo.Range.Columns.Count
    PrintSheetArea ws, 5, lastR, lastC, "$" & lo.HeaderRowRange.row & ":$" & lo.HeaderRowRange.row
    Exit Sub
EH:
    MsgBox "인쇄 설정 오류: " & Err.Description, vbCritical
End Sub

Public Sub ColorStatus(rng As Range)
    Dim c As Range
    For Each c In rng.cells
        Select Case Left(CStr(c.Value2), 1)
            Case "■": c.Font.color = ClrGreen(): c.Font.Bold = True
            Case "●": c.Font.color = ClrBlue()
            Case "▲": c.Font.color = ClrRed(): c.Font.Bold = True
            Case "△": c.Font.color = ClrRed()
            Case Else: c.Font.color = ClrGray()
        End Select
    Next c
End Sub

Public Sub ColorMatch(rng As Range)
    Dim c As Range
    For Each c In rng.cells
        Select Case CStr(c.Value2)
            Case "일치", "지정", "요구서": c.Font.color = ClrGray()
            Case Else: c.Interior.color = RGB(255, 228, 226): c.Font.color = ClrRed()
        End Select
    Next c
End Sub