DeployLineUp

' [Embasa] Deploys the line-up of a work schedule
' @author Roger Pestana (e367982) — DG/GPEP
' @date 08/dec/2019

' Returns a array length
Function ArrayLength(ByVal arr As Variant) As Integer
        ArrayLength = UBound(arr) - LBound(arr) + 1
End Function

' Returns the number of days passed between a year range
Function DaysGoneByYear(ByVal year1 As Integer, ByVal year2 As Integer)
        DaysGoneByYear = DateDiff("d", DateValue("01/01/" & year1), DateValue("01/01/" & year2))
        If DaysGoneByYear < 0 Then DaysGoneByYear = -DaysGoneByYear
End Function

' Verifies if the date is a holiday
Function IsHoliday(ByVal current_date As String, ByVal referenceYear As Integer) As Boolean
        Dim found As Integer
        found = False

        ' Fixed holidays
        For i = 0 To ArrayLength(holidays) - 1
                If DateValue(holidays(i) & "/" & referenceYear) = current_date Then
                        holidays_found_list(holiday_found_index) = Left(holidays(i), 2) & " - " & holidays_names(i)
                        found = True
                        Exit For
                End If
        Next i

        ' Unfixed holidays
        If referenceYear >= BASE_YEAR Then
                If DateValue(holidays_unfixed_list(0)) = current_date Then 'Carnival
                        holidays_found_list(holiday_found_index) = Left(holidays_unfixed_list(0), 2) & " - " & holidays_names_unfixed(0)
                        found = True
                ElseIf DateValue(holidays_unfixed_list(1)) = current_date Then 'Good day
                        holidays_found_list(holiday_found_index) = Left(holidays_unfixed_list(1), 2) & " - " & holidays_names_unfixed(1)
                        found = True
                ElseIf DateValue(holidays_unfixed_list(2)) = current_date Then 'Corpus Christi
                        holidays_found_list(holiday_found_index) = Left(holidays_unfixed_list(2), 2) & " - " & holidays_names_unfixed(2)
                        found = True
                End If
        End If

        IsHoliday = found
End Function

' Returns the letter which the column which is represented
' Max range is 52 (AZ)
Function GetColumnLetter(ByVal col_index As Integer) As String
        Dim letter As String
        indexer_base = IIf(col_index > 26, 26, 0)

        If indexer_base = 0 Then
                letter = Chr(64 + col_index)
        Else
                letter = "A" & Chr(64 + (col_index - indexer_base))
        End If

        GetColumnLetter = letter
End Function

' Prints a cells with a default format
Sub Printc(ByVal cell_ref As Range, ByVal cell_value As Variant, ByVal cell_color As Long, _
        Optional ByVal font_size As Integer, _
        Optional ByVal custom_column_width As Double, _
        Optional ByVal custom_row_height As Double, _
        Optional ByVal value_color As Long, _
        Optional ByVal horizontal_alignment As Integer, _
        Optional ByVal border As Integer, _
        Optional ByVal font_name As String, _
        Optional ByVal border_color As Long _
)
        With cell_ref
                .Value = cell_value
                .ColumnWidth = IIf(custom_column_width, custom_column_width, column_width)
                .RowHeight = IIf(custom_row_height, custom_row_height, 15)
                .HorizontalAlignment = IIf(horizontal_alignment, horizontal_alignment, xlCenter)
                .VerticalAlignment = xlCenter
                .Interior.Color = cell_color
                .Borders.Color = IIf(border_color, border_color, 0)
                .Borders.LineStyle = IIf(border, border, xlContinuous)
                .Font.Bold = True
                .Font.Size = IIf(font_size, font_size, 11)
                .Font.Color = IIf(value_color, value_color, 0)
                .Font.Name = IIf(font_name <> "", font_name, "Calibri")
        End With
End Sub

' Merges cell by index range
Sub MergeCellsByIndex(ByVal col_a As Integer, ByVal row_a As Integer, ByVal col_b As Integer, ByVal row_b As Integer, _
        Optional ByVal horizontal_alignment As Integer _
)
        With Range(GetColumnLetter(col_a) & CStr(row_a), GetColumnLetter(col_b) & CStr(row_b))
                .Merge
                .VerticalAlignment = xlCenter
                .HorizontalAlignment = IIf(horizontal_alignment, horizontal_alignment, xlCenter)
        End With
End Sub

' Border thick by index range
Sub BorderThickness(ByVal row_a As Integer, ByVal col_a As Integer, ByVal row_b As Integer, ByVal col_b As Integer)
        With Range(GetColumnLetter(row_a) & CStr(col_a), GetColumnLetter(row_b) & CStr(col_b))
                .Borders(xlEdgeTop).Weight = xlMedium
                .Borders(xlEdgeLeft).Weight = xlMedium
                .Borders(xlEdgeRight).Weight = xlMedium
                .Borders(xlEdgeBottom).Weight = xlMedium
        End With
End Sub

'Main function
Sub deployLineup(ByVal combo_lineup As Variant, ByVal txt_lineupName, ByVal txt_classesInitFormat, ByVal txt_classesNumber As String, _
        ByVal txt_columnSize As Double, ByVal txt_referenceYear As Integer, ByVal txt_baseYear _
)
Dim holiday_found As Boolean
Dim classes_number, current_year, current_row, current_col, holidays_unfixed_baseIndex As Integer
Dim current_date As String
Dim lineup, current_line, classes_init_format, months_cols As Variant

' Clear everything
Range("A:AZ").Clear
ActiveSheet.Pictures.Delete

' Remove gridlines
ActiveWindow.DisplayGridlines = False

' Setup variables
baseYear_classesInitFormat = IIf(txt_baseYear <> "", txt_baseYear, BASE_YEAR)
classes_number = txt_classesNumber
current_year = txt_referenceYear
lineup = Split(txt_lineup, ",")
classes_init_format = Split(txt_classesInitFormat, ",")
column_width = txt_columnSize
current_line = Array(0, 0, 0, 0, 0)
months_cols = Array(2, 0, 0, 0, 0, 0)

' Get the line-up of the combobox
lineup = Split(combo_lineup.Value, ",")

' holidays list
' Unfixed holidays just settled until 2019 to 2030, with 2019 as the base year
holidays = Array("01/01", "21/04", "01/05", "24/06", "02/07", "07/09", "12/10", "02/11", "15/11", "25/12")
holiday_carnival = Array("05/03/2019", "25/02/2020", "16/02/2021", "01/03/2022", "21/02/2023", "13/02/2024", "04/03/2025", "17/02/2026", "09/fev/2027", "29/02/2028", "13/02/2029", "05/03/2030")
holiday_goodfriday = Array("19/04/2019", "10/04/2020", "02/04/2021", "15/04/2022", "07/04/2023", "29/04/2024", "18/04/2025", "03/04/2026", "26/03/2027", "14/04/2028", "30/03/2029", "19/04/2030")
holiday_corpuschristi = Array("20/06/2019", "11/06/2020", "03/06/2021", "16/06/2022", "08/06/2023", "30/05/2024", "19/06/2025", "04/06/2026", "27/05/2027", "15/06/2028", "31/05/2029", "20/06/2030")
holidays_unfixed_baseIndex = txt_referenceYear Mod BASE_YEAR

' Holidays names
holidays_names = Array( _
        "Confraternização Universal", "Tiradentes", _
        "Dia do Trabalho", "São João", _
        "Independência da Bahia", "Independência do Brasil", _
        "Nossa Senhora Aparecida", "Finados", _
        "Proclamação da República", "Natal" _
)
holidays_names_unfixed = Array("Carnaval", "Sexta-feira Santa", "Corpus Christi")

' Holidays found
holidays_found_list = Array("", "", "")

' Set up the unfixed holidays
If holidays_unfixed_baseIndex <= (MAX_HOLIDAYS_YEAR - BASE_YEAR) Then
        holidays_unfixed_list = Array(holiday_carnival(holidays_unfixed_baseIndex), holiday_goodfriday(holidays_unfixed_baseIndex), holiday_corpuschristi(holidays_unfixed_baseIndex))
End If

' Fills the fixed months columns
For i = 1 To ArrayLength(months_cols) - 1
        months_cols(i) = months_cols(i - 1) + classes_number + 3
Next i

' Sets the first column width
Cells(1, 1).ColumnWidth = 1

' Set the init format of classes
For i = 0 To classes_number - 1
        current_line(i) = (classes_init_format(i) + DaysGoneByYear(baseYear_classesInitFormat, txt_referenceYear)) Mod ArrayLength(lineup)
        If current_line(i) = 0 Then current_line(i) = ArrayLength(lineup)
        current_line(i) = current_line(i) - 1
Next i

' Header
Printc _
        cell_ref:=Cells(HEADER_ROW, 2), _
        cell_value:=txt_lineupName & IIf(txt_lineupName <> "", " — " & current_year, ""), _
        cell_color:=HEADER_BGCOLOR, _
        font_size:=20, _
        custom_row_height:=25, _
        value_color:=HEADER_VALUECOLOR, _
        border:=xlNone, _
        font_name:="Bahnschrift SemiLight"
MergeCellsByIndex 2, HEADER_ROW, (6 * classes_number) + 18, HEADER_ROW

' Main loop
For current_month = 1 To 12
        ' Set the indexes
        ROW_BASE_INDEX = IIf(current_month < 7, FIRST_SEMESTER_ROW, SECOND_SEMESTER_ROW)
        COL_BASE_INDEX = IIf(current_month < 7, 1, 7)
        current_col = months_cols(current_month - COL_BASE_INDEX)

        ' Reset holidays list
        holidays_found_list = Array("", "", "")
        holiday_found_index = 0

        ' Print month name
        Printc _
                cell_ref:=Cells(ROW_BASE_INDEX, current_col), _
                cell_value:=UCase(MonthName(current_month, True)), _
                cell_color:=MONTHNAME_BGCOLOR, _
                font_size:=14, _
                value_color:=MONTHNAME_VALUECOLOR, _
                border:=xlNone
        MergeCellsByIndex current_col, ROW_BASE_INDEX, current_col + 1, ROW_BASE_INDEX + 1

        ' Print "Turmas" besides the month name
        Printc _
                cell_ref:=Cells(ROW_BASE_INDEX, current_col + 2), _
                cell_value:="Turmas", _
                cell_color:=CLASSES_HEADER_BGCOLOR, _
                font_size:=14, _
                value_color:=CLASSES_HEADER_VALUECOLOR, _
                border:=xlNone
        MergeCellsByIndex current_col + 2, ROW_BASE_INDEX, current_col + classes_number + 1, ROW_BASE_INDEX

        ' Prints the classes letters
        For i = 0 To classes_number - 1
                Printc _
                        cell_ref:=Cells(ROW_BASE_INDEX + 1, current_col + i + 2), _
                        cell_value:=Chr(65 + i), _
                        cell_color:=CLASSES_LETTERS_BGCOLOR, _
                        font_size:=13, _
                        value_color:=CLASSES_LETTERS_VALUECOLOR, _
                        border:=xlNone
        Next i

        ' Thick the month header
        BorderThickness current_col, ROW_BASE_INDEX, current_col + 1 + classes_number, ROW_BASE_INDEX + 1

        ' Deploys each table line
        For current_day = 1 To 31
                current_date = Format(current_day & "/" & current_month & "/" & current_year, "dd/mm/yyyy")
                current_row = current_day + ROW_BASE_INDEX + 1
                holiday_found = False

                ' If isn't a date, set a gray background
                If Not IsDate(current_date) Then
                        For i = 0 To (31 - current_day)
                                For j = 0 To (classes_number + 1)
                                        Printc _
                                                cell_ref:=Cells(current_row + i, current_col + j), _
                                                cell_value:="", _
                                                cell_color:=EMPTY_BGCOLOR, _
                                                font_size:=11, _
                                                custom_column_width:=IIf(j < 2, DEFAULT_COLUMN_WIDTH, column_width)
                                Next j
                        Next i
                        Exit For
                End If

                ' Restart line position
                For i = 0 To ArrayLength(current_line) - 1
                        If current_line(i) >= ArrayLength(lineup) Then current_line(i) = 0
                Next i

                ' Verify if is a holiday
                If IsHoliday(DateValue(current_date), current_year) Then
                        holiday_found = True
                        holiday_found_index = holiday_found_index + 1
                End If

                ' Fills the weekdays
                Printc _
                        cell_ref:=Cells(current_row, current_col), _
                        cell_value:=WeekdayName(Weekday(current_date), True), _
                        cell_color:=WEEKDAY_BGCOLOR, _
                        custom_column_width:=DEFAULT_COLUMN_WIDTH, _
                        value_color:=WEEKDAY_VALUECOLOR, _
                        border:=xlNone

                ' Fills the days
                Printc _
                        cell_ref:=Cells(current_row, current_col + 1), _
                        cell_value:=current_day, _
                        cell_color:=IIf(holiday_found, RED, DAY_BGCOLOR), _
                        custom_column_width:=DEFAULT_COLUMN_WIDTH, _
                        value_color:=DAY_VALUECOLOR, _
                        border:=xlNone

                ' Fills the lineup
                For i = 0 To classes_number - 1
                        Printc _
                                cell_ref:=Cells(current_row, current_col + i + 2), _
                                cell_value:=IIf(lineup(current_line(i)) = 0, "", lineup(current_line(i))), _
                                cell_color:=IIf(lineup(current_line(i)) = 0, LINEUP_DAYOFF_BGCOLOR, LINEUP_BGCOLOR), _
                                custom_column_width:=column_width, _
                                value_color:=LINEUP_VALUECOLOR, _
                                font_name:="Arial", _
                                border_color:=BASIC_BORDERCOLOR
                        current_line(i) = current_line(i) + 1
                Next i
        Next current_day

        ' Thick the last deployed month borders
        BorderThickness current_col, ROW_BASE_INDEX, current_col + classes_number + 1, ROW_BASE_INDEX + 32

        ' Set the width of column after the last deployed month
        If current_month Mod 6 <> 0 Then
                Cells(1, current_col + classes_number + 2).ColumnWidth = 1
        Else
                ' Prints "Gerência de Administração de Pessoal — GPEP" bellow the semesters and merge the entire scope of the semester
                Printc _
                        cell_ref:=Cells(ROW_BASE_INDEX + 37, 2), _
                        cell_value:="Gerência de Administração de Pessoal — GPEP", _
                        cell_color:=xlNone, _
                        font_size:=14, _
                        custom_column_width:=DEFAULT_COLUMN_WIDTH, _
                        border:=xlNone
                MergeCellsByIndex 2, ROW_BASE_INDEX + 37, current_col + classes_number + 1, ROW_BASE_INDEX + 37

                ' Prints "$1º semestre" above the semesters
                Printc _
                        cell_ref:=Cells(ROW_BASE_INDEX - 2, 2), _
                        cell_value:=(current_month / 6) & "º semestre", _
                        cell_color:=xlNone, _
                        font_name:="Arial", _
                        font_size:=15, _
                        custom_column_width:=DEFAULT_COLUMN_WIDTH, _
                        border:=xlNone
                MergeCellsByIndex 2, ROW_BASE_INDEX - 2, (6 * classes_number) + 18, ROW_BASE_INDEX - 2
        End If

        ' Sets the first row height above the semester
        Cells(ROW_BASE_INDEX + 33, current_col).RowHeight = 7

        ' Prints the holidays names above above each month
        If holiday_found_index > 0 Then
                For i = 0 To ArrayLength(holidays_found_list) - 1
                        If holidays_found_list(i) <> "" Then
                                Printc _
                                        cell_ref:=Cells(ROW_BASE_INDEX + 34 + i, current_col), _
                                        cell_value:=holidays_found_list(i), _
                                        cell_color:=HOLIDAY_BGCOLOR, _
                                        font_size:=13, _
                                        custom_column_width:=DEFAULT_COLUMN_WIDTH, _
                                        value_color:=HOLIDAY_VALUECOLOR, _
                                        horizontal_alignment:=xlLeft, _
                                        border:=xlNone
                                MergeCellsByIndex current_col, ROW_BASE_INDEX + 34 + i, current_col + classes_number + 1, ROW_BASE_INDEX + 34 + i, xlLeft
                        End If
                Next i
        End If
Next current_month

' Deploys the Embasa's logo
ActiveSheet.Shapes.AddPicture _
        Filename:=LOGO_FILEPATH, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=(Range("B1:" & GetColumnLetter((6 * classes_number) + 18) & "1").Width / 2) - (LOGO_WIDTH / 2), _
        Top:=(Range("A1:A" & HEADER_ROW - 1).Height / 2) - (LOGO_HEIGHT / 2), _
        Width:=LOGO_WIDTH, Height:=LOGO_HEIGHT
End Sub