Attribute VB_Name = "SaiPrakashOperationsAutomation"
Option Explicit

' Portfolio demonstration built for this application pack.
' It is not presented as a prior production VBA deployment.

Private Const DASHBOARD_SHEET As String = "Dashboard"
Private Const INPUT_SHEET As String = "Timesheet Input"
Private Const IMPORT_SHEET As String = "Imported Attendance"
Private Const FIRST_DATA_ROW As Long = 6

Public Sub RefreshAutomationDashboard()
    On Error GoTo CleanFail

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.StatusBar = "Validating timesheet rows and refreshing dashboard..."

    ValidateTimesheetRows
    ThisWorkbook.RefreshAll
    Application.CalculateFull
    Worksheets(DASHBOARD_SHEET).Activate

CleanExit:
    Application.StatusBar = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

CleanFail:
    MsgBox "The dashboard could not be refreshed: " & Err.Description, vbExclamation, "Automation Demo"
    Resume CleanExit
End Sub

Public Sub ValidateTimesheetRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim issueCount As Long

    Set ws = Worksheets(INPUT_SHEET)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For rowNumber = FIRST_DATA_ROW To lastRow
        If Len(Trim$(CStr(ws.Cells(rowNumber, "A").Value))) = 0 Then
            ws.Cells(rowNumber, "Q").Value = "Blocked: work date missing"
            issueCount = issueCount + 1
        ElseIf Len(Trim$(CStr(ws.Cells(rowNumber, "B").Value))) = 0 Then
            ws.Cells(rowNumber, "Q").Value = "Blocked: employee ID missing"
            issueCount = issueCount + 1
        ElseIf ws.Cells(rowNumber, "H").Value <> "Yes" Then
            ws.Cells(rowNumber, "Q").Value = "Blocked: source missing"
            issueCount = issueCount + 1
        ElseIf ws.Cells(rowNumber, "I").Value <> "Approved" Then
            ws.Cells(rowNumber, "Q").Value = "Blocked: pending approval"
            issueCount = issueCount + 1
        Else
            ws.Cells(rowNumber, "Q").Value = "Ready"
        End If
    Next rowNumber

    MsgBox CStr(lastRow - FIRST_DATA_ROW + 1) & " rows checked. " & _
           CStr(issueCount) & " row(s) need attention.", _
           vbInformation, "Timesheet Validation"
End Sub

Public Sub ImportAttendanceWorkbook()
    Dim selectedFile As Variant
    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    selectedFile = Application.GetOpenFilename( _
        FileFilter:="Excel or CSV Files (*.xlsx;*.xls;*.csv),*.xlsx;*.xls;*.csv", _
        Title:="Select an attendance file to import")

    If selectedFile = False Then Exit Sub

    On Error GoTo ImportFail
    Application.ScreenUpdating = False
    Set sourceBook = Workbooks.Open(Filename:=CStr(selectedFile), ReadOnly:=True)
    Set sourceSheet = sourceBook.Worksheets(1)

    On Error Resume Next
    Set destinationSheet = ThisWorkbook.Worksheets(IMPORT_SHEET)
    On Error GoTo ImportFail

    If destinationSheet Is Nothing Then
        Set destinationSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        destinationSheet.Name = IMPORT_SHEET
    End If

    destinationSheet.Cells.Clear
    destinationSheet.Range("A1").Resize( _
        sourceSheet.UsedRange.Rows.Count, _
        sourceSheet.UsedRange.Columns.Count).Value = sourceSheet.UsedRange.Value
    destinationSheet.Columns.AutoFit

    sourceBook.Close SaveChanges:=False
    Application.ScreenUpdating = True
    MsgBox "Attendance data imported to the '" & IMPORT_SHEET & "' sheet.", vbInformation, "Import Complete"
    Exit Sub

ImportFail:
    On Error Resume Next
    If Not sourceBook Is Nothing Then sourceBook.Close SaveChanges:=False
    Application.ScreenUpdating = True
    MsgBox "The attendance file could not be imported: " & Err.Description, vbExclamation, "Import Failed"
End Sub

Public Sub ExportDashboardToPDF()
    Dim outputPath As String

    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook before exporting the dashboard.", vbExclamation, "Export Dashboard"
        Exit Sub
    End If

    outputPath = ThisWorkbook.Path & Application.PathSeparator & _
                 "Sai_Prakash_Operations_Dashboard.pdf"

    Worksheets(DASHBOARD_SHEET).ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=outputPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub