ConfigurationModule.bas

Attribute VB_Name = "ConfigurationModule"
' Manufacturing Workflow Scheduler - Configuration Module
' Created: April 2025
'
' This module contains functions for managing system configuration
' and customization options, allowing the client to easily modify
' scheduling rules without changing core code.

Option Explicit

' Configuration constants
Private Const CONFIG_SHEET_NAME As String = "Configuration"
Private Const DEFAULT_CONFIG_FILE As String = "scheduler_config.ini"

' Load configuration settings from sheet or create default if not exists
Public Sub LoadConfiguration()
    ' Create configuration sheet if it doesn't exist
    If Not SheetExists(CONFIG_SHEET_NAME) Then
        CreateConfigurationSheet
    End If
    
    ' Load values into system
    ApplyConfigurationSettings
    
    ' Update dashboard status
    UIHelperModule.UpdateDashboardStatus "Configuration loaded successfully", UIHelperModule.COLOR_SUCCESS
End Sub

' Apply configuration settings to the system
Private Sub ApplyConfigurationSettings()
    Dim wsConfig As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Get reference to configuration sheet
    Set wsConfig = ThisWorkbook.Sheets(CONFIG_SHEET_NAME)
    lastRow = wsConfig.Cells(wsConfig.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through configuration settings
    For i = 2 To lastRow
        Dim settingKey As String
        Dim settingValue As Variant
        
        settingKey = wsConfig.Cells(i, 1).Value
        settingValue = wsConfig.Cells(i, 2).Value
        
        ' Apply setting
        If Trim(settingKey) <> "" Then
            ApplySetting settingKey, settingValue
        End If
    Next i
End Sub

' Apply a specific setting
Private Sub ApplySetting(settingKey As String, settingValue As Variant)
    Dim wsParams As Worksheet
    
    ' Get reference to parameters sheet
    On Error Resume Next
    Set wsParams = ThisWorkbook.Sheets("Parameters")
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Apply setting based on key
    Select Case settingKey
        Case "DefaultStartDate"
            If IsDate(settingValue) Then
                wsParams.Range("B2").Value = CDate(settingValue)
            Else
                wsParams.Range("B2").Value = Date
            End If
        
        Case "WorkingHoursPerDay"
            If IsNumeric(settingValue) Then
                wsParams.Range("B3").Value = CDbl(settingValue)
            Else
                wsParams.Range("B3").Value = 8
            End If
        
        Case "UseCalendarDays"
            If UCase(Trim(settingValue)) = "YES" Or UCase(Trim(settingValue)) = "TRUE" Then
                wsParams.Range("B4").Value = "Yes"
            Else
                wsParams.Range("B4").Value = "No"
            End If
        
        Case "MaxResourceUtilization"
            If IsNumeric(settingValue) Then
                wsParams.Range("B5").Value = CDbl(settingValue)
            Else
                wsParams.Range("B5").Value = 90
            End If
        
        Case "SchedulePriority"
            Select Case UCase(Trim(settingValue))
                Case "DUE DATE", "DUEDATE"
                    wsParams.Range("B6").Value = "Due Date"
                Case "PRIORITY"
                    wsParams.Range("B6").Value = "Priority"
                Case "RESOURCE EFFICIENCY", "RESOURCEEFFICIENCY"
                    wsParams.Range("B6").Value = "Resource Efficiency"
                Case Else
                    wsParams.Range("B6").Value = "Due Date"
            End Select
        
        ' Add more settings as needed
    End Select
End Sub

' Create default configuration sheet
Private Sub CreateConfigurationSheet()
    Dim wsConfig As Worksheet
    
    ' Create the sheet
    ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = CONFIG_SHEET_NAME
    Set wsConfig = ThisWorkbook.Sheets(CONFIG_SHEET_NAME)
    
    ' Add header
    With wsConfig.Range("A1:C1")
        .Value = Array("Setting", "Value", "Description")
        .Font.Bold = True
        .Interior.Color = RGB(200, 200, 200)
    End With
    
    ' Add default settings
    AddConfigRow wsConfig, 2, "DefaultStartDate", Date, "Default start date for scheduling if not specified in work orders"
    AddConfigRow wsConfig, 3, "WorkingHoursPerDay", 8, "Number of working hours in a standard day"
    AddConfigRow wsConfig, 4, "UseCalendarDays", "No", "If Yes, includes weekends in scheduling calculations"
    AddConfigRow wsConfig, 5, "MaxResourceUtilization", 90, "Maximum resource utilization percentage (1-100)"
    AddConfigRow wsConfig, 6, "SchedulePriority", "Due Date", "Method used to prioritize scheduling (Due Date, Priority, Resource Efficiency)"
    AddConfigRow wsConfig, 7, "CriticalPathHighlight", "Yes", "Highlight critical path in schedule and reports"
    AddConfigRow wsConfig, 8, "AutoLevelResources", "Yes", "Automatically level resources during schedule generation"
    AddConfigRow wsConfig, 9, "ScheduleBufferPercent", 10, "Buffer percentage added to task durations (0-100)"
    AddConfigRow wsConfig, 10, "DefaultSetupTime", 0.5, "Default setup time in hours if not specified"
    AddConfigRow wsConfig, 11, "ConflictResolutionMethod", "Priority", "Method to resolve scheduling conflicts (Priority, FIFO, LIFO)"
    AddConfigRow wsConfig, 12, "WorkOrderLabelFormat", "WO-{0}", "Format for work order labels, {0} replaced with work order number"
    AddConfigRow wsConfig, 13, "DateFormat", "yyyy-mm-dd", "Format for displaying dates in reports"
    AddConfigRow wsConfig, 14, "EnableLogging", "Yes", "Enable detailed logging for debugging"
    AddConfigRow wsConfig, 15, "LogFilePath", "scheduler_log.txt", "Path to log file relative to workbook"
    
    ' Format cells
    wsConfig.Range("B2").NumberFormat = "yyyy-mm-dd"
    wsConfig.Columns.AutoFit
    
    ' Add validation to certain cells
    ' UseCalendarDays
    With wsConfig.Range("B4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No"
    End With
    
    ' SchedulePriority
    With wsConfig.Range("B6").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Due Date,Priority,Resource Efficiency"
    End With
    
    ' CriticalPathHighlight
    With wsConfig.Range("B7").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No"
    End With
    
    ' AutoLevelResources
    With wsConfig.Range("B8").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No"
    End With
    
    ' ConflictResolutionMethod
    With wsConfig.Range("B11").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Priority,FIFO,LIFO"
    End With
    
    ' EnableLogging
    With wsConfig.Range("B14").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No"
    End With
    
    ' Add instructions
    wsConfig.Range("A17").Value = "Instructions:"
    wsConfig.Range("A17").Font.Bold = True
    wsConfig.Range("A18").Value = "1. Modify the configuration values above to customize the scheduler behavior"
    wsConfig.Range("A19").Value = "2. Click 'Apply Configuration' on the Dashboard to apply changes"
    wsConfig.Range("A20").Value = "3. Configuration changes will affect future schedule generation"
    
    ' Add notes
    wsConfig.Range("A22").Value = "Notes:"
    wsConfig.Range("A22").Font.Bold = True
    wsConfig.Range("A23").Value = "- Changes to these settings do not affect previously generated schedules"
    wsConfig.Range("A24").Value = "- To save this configuration for future use, use the Export/Import Configuration buttons"
    
    ' Add button to apply configuration
    AddButton wsConfig, "A26", "Apply Configuration", "ConfigurationModule.LoadConfiguration"
    AddButton wsConfig, "C26", "Export Configuration", "ConfigurationModule.ExportConfiguration"
    AddButton wsConfig, "E26", "Import Configuration", "ConfigurationModule.ImportConfiguration"
    
    ' Add navigation link back to dashboard
    wsConfig.Range("G17").Value = "Return to Dashboard"
    wsConfig.Hyperlinks.Add Anchor:=wsConfig.Range("G17"), Address:="", SubAddress:="'Dashboard'!A1"
    wsConfig.Range("G17").Font.ColorIndex = 5 ' Blue
    wsConfig.Range("G17").Font.Underline = True
End Sub

' Add a configuration row
Private Sub AddConfigRow(ws As Worksheet, row As Long, setting As String, value As Variant, description As String)
    ws.Cells(row, 1).Value = setting
    ws.Cells(row, 2).Value = value
    ws.Cells(row, 3).Value = description
End Sub

' Add a button to a sheet
Private Sub AddButton(ws As Worksheet, cell As String, caption As String, action As String)
    Dim btn As Button
    
    ' Create button
    Set btn = ws.Buttons.Add(ws.Range(cell).Left, ws.Range(cell).Top, 120, 20)
    
    ' Configure button
    With btn
        .Caption = caption
        .OnAction = action
        .Name = "btn" & Replace(Replace(caption, " ", ""), "-", "")
    End With
End Sub

' Export configuration to file
Public Sub ExportConfiguration()
    Dim wsConfig As Worksheet
    Dim lastRow As Long
    Dim configPath As String
    Dim fileNum As Integer
    Dim i As Long
    
    ' Check if configuration sheet exists
    If Not SheetExists(CONFIG_SHEET_NAME) Then
        UIHelperModule.ShowError "Configuration sheet not found."
        Exit Sub
    End If
    
    ' Get reference to configuration sheet
    Set wsConfig = ThisWorkbook.Sheets(CONFIG_SHEET_NAME)
    lastRow = wsConfig.Cells(wsConfig.Rows.Count, 1).End(xlUp).Row
    
    ' Get file path for export
    With Application.FileDialog(msoFileDialogSaveAs)
        .Title = "Save Configuration"
        .InitialFileName = DEFAULT_CONFIG_FILE
        .Filters.Add "Configuration Files", "*.ini"
        
        If .Show = -1 Then
            configPath = .SelectedItems(1)
        Else
            UIHelperModule.ShowWarning "Export cancelled."
            Exit Sub
        End If
    End With
    
    ' Write configuration to file
    fileNum = FreeFile
    On Error GoTo ErrorHandler
    
    Open configPath For Output As #fileNum
    
    ' Write header
    Print #fileNum, "[Manufacturing Workflow Scheduler Configuration]"
    Print #fileNum, "ExportDate=" & Format(Now, "yyyy-mm-dd hh:mm:ss")
    Print #fileNum, "Version=" & MainModule.VERSION
    Print #fileNum, ""
    
    ' Write settings
    Print #fileNum, "[Settings]"
    For i = 2 To lastRow
        If Trim(wsConfig.Cells(i, 1).Value) <> "" Then
            Print #fileNum, wsConfig.Cells(i, 1).Value & "=" & wsConfig.Cells(i, 2).Value
        End If
    Next i
    
    Close #fileNum
    
    UIHelperModule.ShowSuccess "Configuration exported successfully to " & configPath
    Exit Sub
    
ErrorHandler:
    If fileNum > 0 Then Close #fileNum
    UIHelperModule.ShowError "Error exporting configuration: " & Err.Description
End Sub

' Import configuration from file
Public Sub ImportConfiguration()
    Dim configPath As String
    Dim fileNum As Integer
    Dim line As String
    Dim settingKey As String
    Dim settingValue As String
    Dim pos As Long
    Dim section As String
    Dim wsConfig As Worksheet
    
    ' Get file path for import
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Open Configuration File"
        .Filters.Add "Configuration Files", "*.ini"
        .AllowMultiSelect = False
        
        If .Show = -1 Then
            configPath = .SelectedItems(1)
        Else
            UIHelperModule.ShowWarning "Import cancelled."
            Exit Sub
        End If
    End With
    
    ' Create or get reference to configuration sheet
    If Not SheetExists(CONFIG_SHEET_NAME) Then
        CreateConfigurationSheet
    End If
    Set wsConfig = ThisWorkbook.Sheets(CONFIG_SHEET_NAME)
    
    ' Read configuration from file
    fileNum = FreeFile
    On Error GoTo ErrorHandler
    
    Open configPath For Input As #fileNum
    
    ' Reset section
    section = ""
    
    ' Read file line by line
    Do Until EOF(fileNum)
        Line Input #fileNum, line
        line = Trim(line)
        
        ' Skip empty lines and comments
        If line <> "" And Left(line, 1) <> ";" Then
            ' Check for section header
            If Left(line, 1) = "[" And Right(line, 1) = "]" Then
                section = Mid(line, 2, Len(line) - 2)
            ElseIf section = "Settings" Then
                ' Parse setting
                pos = InStr(line, "=")
                If pos > 0 Then
                    settingKey = Trim(Left(line, pos - 1))
                    settingValue = Trim(Mid(line, pos + 1))
                    
                    ' Update configuration sheet
                    UpdateConfigSetting wsConfig, settingKey, settingValue
                End If
            End If
        End If
    Loop
    
    Close #fileNum
    
    ' Apply imported configuration
    LoadConfiguration
    
    UIHelperModule.ShowSuccess "Configuration imported successfully from " & configPath
    Exit Sub
    
ErrorHandler:
    If fileNum > 0 Then Close #fileNum
    UIHelperModule.ShowError "Error importing configuration: " & Err.Description
End Sub

' Update a configuration setting
Private Sub UpdateConfigSetting(wsConfig As Worksheet, settingKey As String, settingValue As String)
    Dim lastRow As Long
    Dim i As Long
    Dim found As Boolean
    
    lastRow = wsConfig.Cells(wsConfig.Rows.Count, 1).End(xlUp).Row
    found = False
    
    ' Look for existing setting
    For i = 2 To lastRow
        If wsConfig.Cells(i, 1).Value = settingKey Then
            wsConfig.Cells(i, 2).Value = settingValue
            found = True
            Exit For
        End If
    Next i
    
    ' Add new setting if not found
    If Not found Then
        wsConfig.Cells(lastRow + 1, 1).Value = settingKey
        wsConfig.Cells(lastRow + 1, 2).Value = settingValue
        wsConfig.Cells(lastRow + 1, 3).Value = "Imported setting"
    End If
End Sub

' Reset configuration to defaults
Public Sub ResetConfiguration()
    Dim response As VbMsgBoxResult
    
    ' Confirm reset
    response = MsgBox("This will reset all configuration settings to their default values. Continue?", _
                     vbQuestion + vbYesNo, "Reset Configuration")
    
    If response = vbYes Then
        ' Delete configuration sheet if it exists
        If SheetExists(CONFIG_SHEET_NAME) Then
            Application.DisplayAlerts = False
            ThisWorkbook.Sheets(CONFIG_SHEET_NAME).Delete
            Application.DisplayAlerts = True
        End If
        
        ' Create new configuration sheet with defaults
        CreateConfigurationSheet
        
        ' Apply configuration
        LoadConfiguration
        
        UIHelperModule.ShowSuccess "Configuration reset to default values."
    End If
End Sub

' Check if a sheet exists
Private Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    
    SheetExists = Not ws Is Nothing
End Function