Attribute VB_Name = "modCalc"
Option Explicit
'=====================================================================
' modCalc : 세부산출내역 (세출예산요구서의 산출기초 행 단위)
'   열: No · 세부사업 · 사업항목 · 사업명 · 산출내역 · 세목코드 · 예산(천원) · 전년도(천원) · 메모(입력)
'       + 숨김: 원가통계목 · 레벨 · 팀/부서 · 담당자 · 단위과제카드 · 키
'   슬라이서(오른쪽 세로): 팀/부서 · 담당자 · 단위과제카드 · 세부사업 · 사업명 · 레벨 · 세목코드 · 원가통계목
'   찾기: E7(산출내역 키워드) / G7(메모) 입력 후 [찾기](I7) → 해당 문구가 있는 행만 표시(둘 다 입력 시 AND), [취소] → 해제
'   메모는 새로고침해도 보존 (키 = 세부사업|사업명|산출내역|세목코드|순서)
'=====================================================================
Public Const SH_CALC As String = "세부산출내역"
Public Const TBL_CALC As String = "tbl세부산출"
Private Const SC_CAL As String = "슬라이서_산출_"
Private Const C_HDR As Long = 9
Private Const nC As Long = 15
Private Const K_NO As Long = 1, K_SEB As Long = 2, K_NM As Long = 3, K_SNM As Long = 4, K_CALC As Long = 5
Private Const K_CODE As Long = 6, K_BUD As Long = 7, K_PRV As Long = 8, K_MEMO As Long = 9
Private Const K_STAT As Long = 10, K_LV As Long = 11, K_TEAM As Long = 12, K_OWN As Long = 13, K_CARD As Long = 14, K_KEY As Long = 15
Private Const MEMO_CELL As String = "G7"      '메모 찾기 입력칸
Private Const CALC_CELL As String = "E7"      '산출내역 키워드 찾기 입력칸

Public Sub 세부산출내역()
    SpeedOn "세부산출내역 생성 중..."
    On Error GoTo EH
    If Not ModelReady Then BuildModel
    BuildCalc
    BW().Worksheets(SH_CALC).Activate
    SpeedOff
    Exit Sub
EH:
    SpeedOff
    MsgBox "세부산출내역 오류 [" & gStep & "]: " & Err.Description & " (" & Err.Number & ")", vbCritical
End Sub

'찾기: 산출내역 키워드(E7) / 메모(G7) - 입력된 칸 기준으로 표 필터 (둘 다 입력 시 모두 만족하는 행)
Public Sub 메모찾기()
    Dim ws As Worksheet, lo As ListObject, tc As String, tM As String
    If Not SheetExists(SH_CALC) Then Exit Sub
    Set ws = BW().Worksheets(SH_CALC)
    If ws.ListObjects.Count = 0 Then Exit Sub
    Set lo = ws.ListObjects(TBL_CALC)
    tc = CleanText(ws.Range(CALC_CELL).Value2)
    tM = CleanText(ws.Range(MEMO_CELL).Value2)
    If tc = "" And tM = "" Then
        tc = InputBox("산출내역에서 찾을 키워드를 입력하세요. (메모 찾기는 G7 칸에 입력)", "산출내역 찾기")
        If tc = "" Then Exit Sub
        ws.Range(CALC_CELL).Value2 = tc
    End If
    On Error Resume Next
    lo.AutoFilter.ShowAllData
    On Error GoTo 0
    If tc <> "" Then lo.Range.AutoFilter Field:=K_CALC, Criteria1:="=*" & tc & "*"
    If tM <> "" Then lo.Range.AutoFilter Field:=K_MEMO, Criteria1:="=*" & tM & "*"
End Sub

Public Sub 메모찾기취소()
    Dim ws As Worksheet, lo As ListObject
    If Not SheetExists(SH_CALC) Then Exit Sub
    Set ws = BW().Worksheets(SH_CALC)
    If ws.ListObjects.Count = 0 Then Exit Sub
    Set lo = ws.ListObjects(TBL_CALC)
    On Error Resume Next
    lo.AutoFilter.ShowAllData
    On Error GoTo 0
    ws.Range(MEMO_CELL).Value2 = ""
    ws.Range(CALC_CELL).Value2 = ""
End Sub

'---------------------------------------------------------------- 시트 생성
Public Sub BuildCalc()
    Dim ws As Worksheet, src As Worksheet, hr As Long, hm As Object, arr As Variant, lr As Long, lc As Long
    Dim cLv As Long, cE As Long, cI As Long, cM As Long, cP As Long, cQ As Long, cCode As Long, cStat As Long
    Dim r As Long, x As Long, out() As Variant, lv As String, e As String, m As String, nm As String, p As Long
    Dim k As String, it As clsProject, memo As Object, lo As ListObject, rng As Range, dept As String, fc As FormatCondition
    Dim sx As Single, sy As Single, cnt As Object, rk As String, seq As Long

    If Not SheetExists(SH_REQ) Then
        MsgBox "세부산출내역은 ① 세출예산요구서가 있어야 만들 수 있습니다.", vbExclamation
        Exit Sub
    End If
    gStep = "산출내역 읽기"
    Set src = BW().Worksheets(SH_REQ)
    hr = FindHeaderRow(src, "예산요구액", 20)
    If hr = 0 Then Err.Raise vbObjectError + 1, , "요구서 헤더행(예산요구액)을 찾지 못했습니다."
    Set hm = HeaderMap(src, hr)
    cLv = HCol(hm, "레벨"): cE = HCol(hm, "세세부사업"): cI = HCol(hm, "세부사업")
    cM = CalcColCur(src, hr, hm): cP = HCol(hm, "예산요구액"): cQ = HCol(hm, "전년도예산액", False)
    cCode = HCol(hm, "세목코드", False): cStat = HCol(hm, "원가통계목", False)
    lr = Application.WorksheetFunction.Max(lastRow(src, cE), lastRow(src, cM), lastRow(src, cP))
    If lr <= hr Then Exit Sub
    lc = src.cells(hr, src.Columns.Count).End(xlToLeft).Column
    arr = src.Range(src.cells(hr + 1, 1), src.cells(lr, lc)).Value2

    '기존 메모 보존
    Set memo = CreateObject("Scripting.Dictionary")
    If SheetExists(SH_CALC) Then
        Set ws = BW().Worksheets(SH_CALC)
        If ws.ListObjects.Count > 0 Then
            Set lo = ws.ListObjects(TBL_CALC)
            If lo.ListRows.Count > 0 Then
                For r = 1 To lo.ListRows.Count
                    rk = CleanText(lo.DataBodyRange.cells(r, K_KEY).Value2)
                    If rk <> "" And CleanText(lo.DataBodyRange.cells(r, K_MEMO).Value2) <> "" Then memo(rk) = lo.DataBodyRange.cells(r, K_MEMO).Value2
                Next r
            End If
        End If
    End If

    '행 구성
    ReDim out(1 To UBound(arr, 1), 1 To nC)
    Set cnt = CreateObject("Scripting.Dictionary")
    x = 0: Set it = Nothing
    For r = 1 To UBound(arr, 1)
        lv = CleanText(arr(r, cLv)): e = CleanText(arr(r, cE))
        m = RTrim(Replace(Replace(CStr(IIf(IsError(arr(r, cM)), "", arr(r, cM))), vbLf, ""), vbCr, ""))
        If e = "" Or m = "" Then GoTo NextRow
        If CleanText(m) = "소계" Or lv = "합계" Then GoTo NextRow
        If lv = "" And IsLevel1(e) Then
            p = InStr(e, "."): nm = Trim(Mid(e, p + 1))
            k = MakeKey(CleanText(arr(r, cI)), nm)
            If gItems.Exists(k) Then Set it = gItems(k) Else Set it = Nothing
            lv = "1"
        Else
            If lv <> "" Then lv = CStr(CLng(val(lv)))
        End If
        x = x + 1
        out(x, K_NO) = x
        out(x, K_SEB) = CleanText(arr(r, cI))
        out(x, K_SNM) = StripNo(e)                  '09-27: UBIS 순서 번호("1.") 삭제
        If it Is Nothing Then out(x, K_NM) = nm Else out(x, K_NM) = it.사업항목
        If lv = "1" Then out(x, K_CALC) = StripNo(CleanText(m)) Else out(x, K_CALC) = m
        If cCode > 0 Then out(x, K_CODE) = CleanText(arr(r, cCode))
        out(x, K_BUD) = NzD(arr(r, cP))
        If cQ > 0 Then out(x, K_PRV) = NzD(arr(r, cQ))
        If cStat > 0 Then out(x, K_STAT) = CleanText(arr(r, cStat))
        out(x, K_LV) = lv
        If it Is Nothing Then
            out(x, K_TEAM) = "(팀 미지정)": out(x, K_OWN) = "(담당자 미지정)": out(x, K_CARD) = "-"
        Else
            out(x, K_TEAM) = IIf(it.팀 = "", "(팀 미지정)", it.팀)
            out(x, K_OWN) = IIf(it.담당자 = "", "(담당자 미지정)", it.담당자)
            out(x, K_CARD) = IIf(it.과제카드 = "", "-", it.과제카드)
        End If
        rk = out(x, K_SEB) & "|" & e & "|" & CleanText(m) & "|" & out(x, K_CODE)
        If cnt.Exists(rk) Then cnt(rk) = cnt(rk) + 1 Else cnt.Add rk, 1
        rk = rk & "|" & cnt(rk)
        out(x, K_KEY) = rk
        If memo.Exists(rk) Then out(x, K_MEMO) = memo(rk)
NextRow:
    Next r
    If x = 0 Then Exit Sub

    '시트
    gStep = "산출내역 시트"
    DeleteOrClearSlicers SC_CAL, False
    Set ws = GetSheet(SH_CALC, True)
    ws.Tab.color = ClrOrange()
    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 = "갱신 " & NowText() & "   |   (단위: 천원)   |   세출예산요구서 산출기초 기준, 파란 행 = 사업명(1레벨)   |   메모는 새로고침해도 보존"
    ws.Range("A6").Font.Size = 9: ws.Range("A6").Font.color = ClrGray()
    ws.rows(6).RowHeight = 14
    '찾기: D7 안내 / E7 산출내역 키워드 / F7 안내 / G7 메모 / I7 [찾기][취소]
    ws.Range("D7").Value2 = "산출내역 찾기 ▶": ws.Range("F7").Value2 = "메모 찾기 ▶"
    With ws.Range("D7,F7")
        .Font.Bold = True: .Font.name = FONT_NM: .Font.Size = 9: .Font.color = ClrNavy(): .HorizontalAlignment = xlRight: .VerticalAlignment = xlCenter
    End With
    With ws.Range(CALC_CELL & "," & MEMO_CELL)
        .Interior.color = RGB(255, 255, 224): .Borders.LineStyle = xlContinuous: .Borders.color = ClrGray()
        .Font.name = FONT_NM: .VerticalAlignment = xlCenter
    End With
    ws.rows(7).RowHeight = 24
    '[찾기][취소] 버튼은 열 너비 설정 후 H7·I7 셀 위치에 고정 (아래 열 너비 블록 다음)
    ws.rows(8).RowHeight = 6

    '표
    gStep = "산출내역 표"
    ws.cells(C_HDR, 1).Resize(1, nC).Value2 = Array("No", "세부사업", "사업항목", "사업명", "산출내역", "세목코드", "예산", "전년도", "메모", _
        "원가통계목", "레벨", "팀/부서", "담당자", "단위과제카드", "키")
    ws.cells(C_HDR + 1, 1).Resize(x, nC).Value2 = out
    Set rng = ws.Range(ws.cells(C_HDR, 1), ws.cells(C_HDR + x, nC))
    Set lo = MakeTable(ws, rng, TBL_CALC)
    StyleHeader lo.HeaderRowRange
    StyleBody lo.DataBodyRange
    With ws.Range(ws.cells(C_HDR + 1, K_BUD), ws.cells(C_HDR + x, K_PRV))
        .NumberFormat = "#,##0;[Red]-#,##0;""-""": .HorizontalAlignment = xlRight: .ShrinkToFit = True
    End With
    ws.Range(ws.cells(C_HDR + 1, K_NO), ws.cells(C_HDR + x, K_NO)).HorizontalAlignment = xlCenter
    ws.Range(ws.cells(C_HDR + 1, K_CODE), ws.cells(C_HDR + x, K_CODE)).HorizontalAlignment = xlCenter
    ws.Range(ws.cells(C_HDR + 1, K_CALC), ws.cells(C_HDR + x, K_CALC)).WrapText = False
    ws.Range(ws.cells(C_HDR + 1, K_MEMO), ws.cells(C_HDR + x, K_MEMO)).Interior.color = RGB(255, 255, 224)
    '1레벨(사업명) 행 강조, 2레벨 굵게
    Set fc = lo.DataBodyRange.FormatConditions.Add(Type:=xlExpression, formula1:="=$K" & (C_HDR + 1) & "=""1""")
    fc.Interior.color = RGB(229, 240, 255): fc.Font.Bold = True
    Set fc = lo.DataBodyRange.FormatConditions.Add(Type:=xlExpression, formula1:="=$K" & (C_HDR + 1) & "=""2""")
    fc.Font.Bold = True
    '합계 (세목코드가 있는 말단 행만, 슬라이서 반영)
    lo.ShowTotals = True
    lo.ListColumns(K_CALC).total.Value2 = "합계 (세목 행 기준)"
    lo.ListColumns(K_BUD).total.Formula = LeafSum("예산")
    lo.ListColumns(K_PRV).total.Formula = LeafSum("전년도")
    With lo.TotalsRowRange
        .Font.name = FONT_NM: .Font.Bold = True: .Font.color = ClrNavy(): .Interior.color = ClrLight()
        .Borders(xlEdgeTop).LineStyle = xlContinuous: .Borders(xlEdgeTop).weight = xlMedium: .Borders(xlEdgeTop).color = ClrGray()
    End With
    ws.Range(lo.TotalsRowRange.cells(1, K_BUD), lo.TotalsRowRange.cells(1, K_PRV)).NumberFormat = "#,##0"

    '열 너비 / 숨김
    ws.Columns(K_NO).ColumnWidth = 5: ws.Columns(K_SEB).ColumnWidth = 16: ws.Columns(K_NM).ColumnWidth = 22
    ws.Columns(K_SNM).ColumnWidth = 26: ws.Columns(K_CALC).ColumnWidth = 48: ws.Columns(K_CODE).ColumnWidth = 9
    ws.Columns(K_BUD).ColumnWidth = 12: ws.Columns(K_PRV).ColumnWidth = 12: ws.Columns(K_MEMO).ColumnWidth = 30
    ws.Range(ws.Columns(K_STAT), ws.Columns(K_KEY)).hidden = True
    ws.Columns(nC + 1).ColumnWidth = 2
    '찾기/취소 버튼: H7 · I7 셀에 고정 (셀 크기에 맞춤)
    With ws.cells(7, K_PRV)
        AddButton ws, "btnFind", "찾기", "메모찾기", .Left + 2, .top + 2, .Width - 4, .Height - 4, ClrBlue()
    End With
    With ws.cells(7, K_MEMO)
        AddButton ws, "btnFindOff", "취소", "메모찾기취소", .Left + 2, .top + 2, 60, .Height - 4, ClrGray()
    End With

    '슬라이서: 표 오른쪽 세로 배치
    gStep = "산출내역 슬라이서"
    sx = ws.cells(C_HDR, nC + 2).Left: sy = 4
    AddSlicerTo lo, ws, "팀/부서", SC_CAL & "팀", sx, sy, 220, 100, 2: sy = sy + 104
    AddSlicerTo lo, ws, "담당자", SC_CAL & "담당자", sx, sy, 220, 120, 2: sy = sy + 124
    AddSlicerTo lo, ws, "단위과제카드", SC_CAL & "단위과제카드", sx, sy, 220, 110, 1: sy = sy + 114
    AddSlicerTo lo, ws, "세부사업", SC_CAL & "세부사업", sx, sy, 220, 110, 1: sy = sy + 114
    AddSlicerTo lo, ws, "사업명", SC_CAL & "사업명", sx, sy, 220, 150, 1: sy = sy + 154
    AddSlicerTo lo, ws, "레벨", SC_CAL & "레벨", sx, sy, 220, 60, 4: sy = sy + 64
    AddSlicerTo lo, ws, "세목코드", SC_CAL & "세목코드", sx, sy, 220, 110, 3: sy = sy + 114
    AddSlicerTo lo, ws, "원가통계목", SC_CAL & "원가통계목", sx, sy, 220, 110, 2

    ws.Activate
    ActiveWindow.FreezePanes = False
    ws.Range("A1").Select
    ws.cells(C_HDR + 1, K_CALC).Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.DisplayGridlines = False
    ws.Range("A1").Select
End Sub

'세목코드가 있는 말단 행만, 보이는 행 합계
Private Function LeafSum(ByVal col As String) As String
    LeafSum = "=SUMPRODUCT((" & TBL_CALC & "[세목코드]<>"""")*SUBTOTAL(109,OFFSET(" & TBL_CALC & "[" & col & "],ROW(" & TBL_CALC & "[" & col & "])-MIN(ROW(" & TBL_CALC & "[" & col & "])),0,1)))"
End Function

'"1. 사업명" → "사업명" (UBIS 입력 순서 번호일 뿐 고유번호가 아니고 업무포털에도 표시되지 않음)
Private Function StripNo(ByVal e As String) As String
    If IsLevel1(e) Then StripNo = Trim(Mid(e, InStr(e, ".") + 1)) Else StripNo = e
End Function

Private Function IsLevel1(ByVal e As String) As Boolean
    Dim p As Long
    p = InStr(e, ".")
    If p < 2 Or p > 4 Then Exit Function
    IsLevel1 = IsNumeric(Left(e, p - 1))
End Function