Attribute VB_Name = "modImport"
Option Explicit
'=====================================================================
' modImport : ¼¼Ã⿹»ê¿ä±¸¼­ / ÀåÇ¥(102-A, 102-B) ÆÄÀÏ °¡Á®¿À±â
'=====================================================================

'¨ç ¼¼Ã⿹»ê¿ä±¸¼­ ¼±ÅÃ
Public Sub ¿ä±¸¼­¼±ÅÃ()
    Dim fp As Variant, wbS As Workbook, wsS As Worksheet, wsD As Worksheet
    Dim dept As String
    fp = Application.GetOpenFilename("Excel ÆÄÀÏ (*.xls*), *.xls*", , "¼¼Ã⿹»ê¿ä±¸¼­ ÆÄÀÏÀ» ¼±ÅÃÇϼ¼¿ä", , False)
    If VarType(fp) = vbBoolean Then Exit Sub

    On Error GoTo EH
    SpeedOn "¼¼Ã⿹»ê¿ä±¸¼­ °¡Á®¿À´Â Áß..."
    Set wbS = Workbooks.Open(fileName:=fp, ReadOnly:=True, UpdateLinks:=0)
    Set wsS = wbS.Worksheets(1)
    Set wsD = GetSheet(SH_REQ, True)
    CopyValues wsS, wsD, "", ""
    dept = ExtractDept(wsD)
    wbS.Close SaveChanges:=False
    Set wbS = Nothing

    If dept <> "" Then CfgSet "ºÎ¼­¸í", dept
    CfgSet "¿ä±¸¼­ÆÄÀÏ", CStr(fp)
    CfgSet "¿ä±¸¼­°¡Á®¿Â½Ã°¢", NowText()
    wsD.Visible = xlSheetHidden
    SpeedOff
    AutoRefresh
    MsgBox "¼¼Ã⿹»ê¿ä±¸¼­¸¦ °¡Á®¿Ô½À´Ï´Ù." & vbCrLf & "ºÎ¼­: " & dept & vbCrLf & _
           IIf(SheetExists(SH_B), "º¸µå°¡ °»½ÅµÇ¾ú½À´Ï´Ù.", "ÀÌÁ¦ ¨è ÀåÇ¥(102-B) ¼±ÅÃÀ¸·Î ÁýÇà ÀåÇ¥¸¦ ºÒ·¯¿À¼¼¿ä."), vbInformation
    Exit Sub
EH:
    SpeedOff
    If Not wbS Is Nothing Then wbS.Close SaveChanges:=False
    MsgBox "¿ä±¸¼­ °¡Á®¿À±â ¿À·ù: " & Err.Description, vbCritical
End Sub

'¨è ÀåÇ¥(102-A, 102-B) ¼±Åà (º¹¼ö ¼±Åà °¡´É)
Public Sub ÀåÇ¥¼±ÅÃ()
    Dim fps As Variant, i As Long, wbS As Workbook, wsS As Worksheet, wsD As Worksheet
    Dim kind As String, dept As String, hr As Long, hm As Object, Done As String, cnt As Long
    fps = Application.GetOpenFilename("Excel ÆÄÀÏ (*.xls*), *.xls*", , "ÀåÇ¥ ÆÄÀÏÀ» ¼±ÅÃÇϼ¼¿ä (102-B Çʼö, 102-A ¼±Åà / Ctrl·Î ÇÔ²² ¼±Åà °¡´É)", , True)
    If VarType(fps) = vbBoolean Then Exit Sub

    On Error GoTo EH
    SpeedOn "ÀåÇ¥ °¡Á®¿À´Â Áß..."
    dept = CfgGet("ºÎ¼­¸í")
    For i = LBound(fps) To UBound(fps)
        Set wbS = Workbooks.Open(fileName:=fps(i), ReadOnly:=True, UpdateLinks:=0)
        Set wsS = wbS.Worksheets(1)
        hr = FindHeaderRow(wsS, "ȸ°è¿¬µµ", 10)
        kind = ""
        If hr > 0 Then
            Set hm = HeaderMap(wsS, hr)
            If hm.Exists("»ç¾÷Ç׸ñ¸í1") Then
                kind = "B"
            ElseIf hm.Exists("»ç¾÷Ç׸ñ1·¹º§") Then
                kind = "A"
            End If
        End If
        If kind = "" Then
            If InStr(1, fps(i), "102-B", vbTextCompare) > 0 Then kind = "B"
            If InStr(1, fps(i), "102-A", vbTextCompare) > 0 Then kind = "A"
        End If
        If kind = "" Then
            wbS.Close SaveChanges:=False
            MsgBox "ÀåÇ¥ Çü½ÄÀ» ÀνÄÇÏÁö ¸øÇØ °Ç³Ê¶Ý´Ï´Ù:" & vbCrLf & fps(i), vbExclamation
        Else
            If dept = "" And hr > 0 Then dept = PickDept(wsS, hr, hm)
            Set wsD = GetSheet(IIf(kind = "A", SH_A, SH_B), True)
            cnt = CopyValues(wsS, wsD, "¿¹»ê¼Ò°üºÎ¼­¸í", dept)
            If cnt = 0 Then
                'ºÎ¼­ ÇÊÅÍ °á°ú°¡ ¾øÀ¸¸é Àüü º¹»ç
                cnt = CopyValues(wsS, wsD, "", "")
            End If
            CfgSet "±âÁØÀÏ", ExtractBaseDate(wsS)
            CfgSet "ÀåÇ¥" & kind & "ÆÄÀÏ", CStr(fps(i))
            CfgSet "ÀåÇ¥" & kind & "Çà¼ö", CStr(cnt)
            wsD.Visible = xlSheetHidden
            Done = Done & vbCrLf & " - 102-" & kind & " : " & cnt & "Çà"
            wbS.Close SaveChanges:=False
        End If
        Set wbS = Nothing
    Next i
    If dept <> "" Then CfgSet "ºÎ¼­¸í", dept
    If dept = "" Then MsgBox "ºÎ¼­¸¦ ÁöÁ¤ÇÏÁö ¾Ê¾Æ ÀåÇ¥ Àüü ÇàÀ» °¡Á®¿Ô½À´Ï´Ù. [¨ç ¼¼Ã⿹»ê¿ä±¸¼­ ¼±ÅÃ] ¶Ç´Â [ºÎ¼­ ¼±ÅÃ] ÈÄ ´Ù½Ã ¨è ¸¦ ½ÇÇàÇϼ¼¿ä.", vbExclamation
    CfgSet "ÀåÇ¥°¡Á®¿Â½Ã°¢", NowText()
    SpeedOff
    AutoRefresh
    MsgBox "ÀåÇ¥¸¦ °¡Á®¿Ô½À´Ï´Ù. (ºÎ¼­: " & dept & ", ±âÁØÀÏ: " & CfgGet("±âÁØÀÏ") & ")" & Done, vbInformation
    Exit Sub
EH:
    SpeedOff
    If Not wbS Is Nothing Then wbS.Close SaveChanges:=False
    MsgBox "ÀåÇ¥ °¡Á®¿À±â ¿À·ù: " & Err.Description, vbCritical
End Sub

'¹öÆ°: ºÎ¼­¸í Á÷Á¢ ÁöÁ¤ (¿ä±¸¼­ ¾øÀÌ »ç¿ëÇÒ ¶§)
Public Sub ºÎ¼­¼±ÅÃ()
    Dim ans As String
    ans = Trim(InputBox("ÀåÇ¥¿¡¼­ ÃßÃâÇÒ ¿ì¸® ºÎ¼­¸íÀ» ÀÔ·ÂÇϼ¼¿ä (¿¹: ¡Û¡Û±³À°°ú)." & vbCrLf & _
                        "ÀåÇ¥ '¿¹»ê¼Ò°üºÎ¼­¸í' ¿­¿¡ ÀÌ ±ÛÀÚ°¡ Æ÷ÇÔµÈ Çุ °¡Á®¿É´Ï´Ù.", "ºÎ¼­ ¼±ÅÃ", CfgGet("ºÎ¼­¸í")))
    If ans = "" Then Exit Sub
    CfgSet "ºÎ¼­¸í", ans
    MsgBox "ºÎ¼­¸íÀÌ '" & ans & "' ·Î ÀúÀåµÇ¾ú½À´Ï´Ù. ÀÌÁ¦ ¨è ÀåÇ¥(102-B) ¼±ÅÃÀ» ½ÇÇàÇϼ¼¿ä.", vbInformation
End Sub

'°ª º¹»ç (ÇÊÅÍ ¿­/ÅؽºÆ® ÁöÁ¤ ½Ã ÇØ´ç ÅؽºÆ®°¡ Æ÷ÇÔµÈ Çà + Çì´õ À§ Çุ º¹»ç). ¹Ýȯ: µ¥ÀÌÅÍ Çà¼ö
Public Function CopyValues(wsS As Worksheet, wsD As Worksheet, ByVal filterHeader As String, ByVal filterText As String) As Long
    Dim arr As Variant, ur As Range, nR As Long, nC As Long, r As Long, c As Long
    Dim hr As Long, fc As Long, out As Variant, k As Long, keep As Boolean
    Set ur = wsS.usedRange
    arr = ur.Value2
    If Not IsArray(arr) Then CopyValues = 0: Exit Function
    nR = UBound(arr, 1): nC = UBound(arr, 2)
    '¿øº» ÁÂÇ¥ À¯Áö (UsedRange°¡ A1¿¡¼­ ½ÃÀÛÇÏÁö ¾Ê´Â °æ¿ì ´ëºñ)
    Dim r0 As Long, c0 As Long
    r0 = ur.row: c0 = ur.Column

    fc = 0: hr = 0
    If filterHeader <> "" And filterText <> "" Then
        hr = FindHeaderRow(wsS, "ȸ°è¿¬µµ", 10)
        If hr > 0 Then
            For c = 1 To nC
                If CleanText(arr(hr - r0 + 1, c)) = filterHeader Then fc = c: Exit For
            Next c
        End If
    End If

    If fc = 0 Then
        wsD.cells(r0, c0).Resize(nR, nC).Value2 = arr
        CopyValues = nR - IIf(hr > 0, hr, 0)
    Else
        ReDim out(1 To nR, 1 To nC)
        k = 0
        For r = 1 To nR
            keep = (r <= hr - r0 + 1)
            If Not keep Then keep = (InStr(1, CleanText(arr(r, fc)), filterText, vbTextCompare) > 0)
            If keep Then
                k = k + 1
                For c = 1 To nC: out(k, c) = arr(r, c): Next c
            End If
        Next r
        If k - (hr - r0 + 1) <= 0 Then CopyValues = 0: Exit Function
        wsD.cells(r0, c0).Resize(k, nC).Value2 = out
        CopyValues = k - (hr - r0 + 1)
    End If
End Function

'¿ä±¸¼­¿¡¼­ ºÎ¼­¸í ÃßÃâ ("¡à ±â°ü(ºÎ¼­)¸í : ¡Û¡Û±³À°°ú")
Private Function ExtractDept(ws As Worksheet) As String
    Dim r As Long, c As Long, T As String, p As Long
    For r = 1 To 10
        For c = 1 To 15
            T = CleanText(ws.cells(r, c).Value2)
            If InStr(T, "ºÎ¼­)¸í") > 0 Or InStr(T, "ºÎ¼­¸í") > 0 Then
                p = InStr(T, ":")
                If p > 0 Then ExtractDept = Trim(Mid(T, p + 1)): Exit Function
            End If
        Next c
    Next r
    ExtractDept = ""
End Function

'ÀåÇ¥¿¡ µé¾îÀÖ´Â ¿¹»ê¼Ò°üºÎ¼­ ¸ñ·ÏÀ» º¸¿©ÁÖ°í ¿ì¸® ºÎ¼­¸¦ °í¸£°Ô ÇÔ (¿ä±¸¼­¸¦ ¾È ºÒ·¯¿Â °æ¿ì)
Public Function PickDept(ws As Worksheet, ByVal hr As Long, hm As Object) As String
    Dim c As Long, lr As Long, arr As Variant, d As Object, r As Long, T As String, k As Variant
    Dim names() As String, n As Long, i As Long, j As Long, tmp As String, msg As String, ans As String
    c = HCol(hm, "¿¹»ê¼Ò°üºÎ¼­¸í", False)
    If c = 0 Then Exit Function
    lr = lastRow(ws, c)
    If lr <= hr Then Exit Function
    arr = ws.Range(ws.cells(hr + 1, c), ws.cells(lr, c)).Value2
    Set d = CreateObject("Scripting.Dictionary")
    For r = 1 To UBound(arr, 1)
        T = CleanText(arr(r, 1))
        If T <> "" Then d(T) = 1
    Next r
    n = d.Count
    If n = 0 Then Exit Function
    ReDim names(1 To n): i = 0
    For Each k In d.keys: i = i + 1: names(i) = k: Next k
    For i = 2 To n
        tmp = names(i): j = i - 1
        Do While j >= 1
            If StrComp(names(j), tmp, vbTextCompare) <= 0 Then Exit Do
            names(j + 1) = names(j): j = j - 1
        Loop
        names(j + 1) = tmp
    Next i
    If n = 1 Then
        arr = Split(names(1), " "): PickDept = arr(UBound(arr)): Exit Function
    End If
    msg = "ÀåÇ¥¿¡ " & n & "°³ ºÎ¼­°¡ ÀÖ½À´Ï´Ù. ¿ì¸® ºÎ¼­ÀÇ ¹øÈ£¸¦ ÀÔ·ÂÇϼ¼¿ä (¶Ç´Â ºÎ¼­¸í ÀϺΠÁ÷Á¢ ÀÔ·Â)" & vbCrLf & vbCrLf
    For i = 1 To n
        msg = msg & i & ". " & names(i) & vbCrLf
        If Len(msg) > 900 And i < n Then msg = msg & "... (ÀÌÇÏ »ý·« - ºÎ¼­¸íÀ» Á÷Á¢ ÀÔ·ÂÇϼ¼¿ä)" & vbCrLf: Exit For
    Next i
    ans = Trim(InputBox(msg, "¿ì¸® ºÎ¼­ ¼±ÅÃ", ""))
    If ans = "" Then Exit Function
    If IsNumeric(ans) Then
        If CLng(ans) >= 1 And CLng(ans) <= n Then
            arr = Split(names(CLng(ans)), " "): PickDept = arr(UBound(arr)): Exit Function
        End If
    End If
    PickDept = ans
End Function

'ÀåÇ¥¿¡¼­ ºÎ¼­¸í(ªÀº À̸§) ÃßÃâ: "±³À°±¹ ¡Û¡Û±³À°°ú" -> "¡Û¡Û±³À°°ú"
Private Function DeptFromSheet(ws As Worksheet, ByVal hr As Long, hm As Object) As String
    Dim c As Long, T As String, arr As Variant
    c = HCol(hm, "¿¹»ê¼Ò°üºÎ¼­¸í", False)
    If c = 0 Then Exit Function
    T = CleanText(ws.cells(hr + 1, c).Value2)
    arr = Split(T, " ")
    DeptFromSheet = arr(UBound(arr))
End Function

'ÀåÇ¥ »ó´Ü "±âÁØÀÏ : 2026-09-24"
Private Function ExtractBaseDate(ws As Worksheet) As String
    Dim r As Long, c As Long, T As String, p As Long
    For r = 1 To 5
        For c = 1 To 5
            T = CleanText(ws.cells(r, c).text)      'Ç¥½Ã Çü½Ä ±×´ë·Î (³¯Â¥ ¼¿µµ ÅؽºÆ®·Î)
            If InStr(T, "±âÁØÀÏ") > 0 Then
                p = InStr(T, ":")
                If p > 0 Then
                    ExtractBaseDate = FmtDate(Trim(Mid(T, p + 1)))
                Else
                    '"±âÁØÀÏ" ¶óº§ ¿· ¼¿¿¡ ³¯Â¥°¡ ÀÖ´Â °æ¿ì
                    ExtractBaseDate = FmtDate(ws.cells(r, c + 1).Value2)
                End If
                Exit Function
            End If
        Next c
    Next r
    ExtractBaseDate = FmtDate(Date)
End Function