Option Compare Database
Option Explicit

' ============================================================
' : mExcelReports
'
' :
'      ""   Excel-.
'
' :
'   -     -;
'   -       "";
'   -      Reports   ;
'   -      Templates   .
'
'  :
'   CurrentProject.Path\Templates
'
'  :
'   CurrentProject.Path\Reports
'
' :
'   Pivovar_Report_Filling_Detail_Template.xlsx
'   Pivovar_Report_Filling_By_Package_Template.xlsx
'   Pivovar_Report_Filling_By_Sort_Template.xlsx
'   Pivovar_Report_Filling_By_Batch_Template.xlsx
'   Pivovar_Report_Filtration_Losses_Template.xlsx
'   Pivovar_Report_Batch_State_Template.xlsx
'
'  :
'   f_
' ============================================================


Public Const REPORT_FILLING_DETAIL As String = "FILLING_DETAIL"
Public Const REPORT_FILLING_BY_PACKAGE As String = "FILLING_BY_PACKAGE"
Public Const REPORT_FILLING_BY_SORT As String = "FILLING_BY_SORT"
Public Const REPORT_FILLING_BY_BATCH As String = "FILLING_BY_BATCH"
Public Const REPORT_FILTRATION_LOSSES As String = "FILTRATION_LOSSES"
Public Const REPORT_BATCH_STATE As String = "BATCH_STATE"

Private Const TEMPLATE_FOLDER As String = "Templates"
Private Const REPORTS_FOLDER As String = "Reports"

Private Const REPORT_SHEET_NAME As String = ""

Private Const HEADER_ROW As Long = 5
Private Const DATA_START_ROW As Long = 6


' ============================================================
'  
' ============================================================

Public Sub ExportPivovarReportToExcel( _
    ByVal reportCode As String, _
    Optional ByVal dateFrom As Variant, _
    Optional ByVal dateTo As Variant, _
    Optional ByVal sortValue As Variant, _
    Optional ByVal fermentationNumber As Variant, _
    Optional ByVal packageType As Variant, _
    Optional ByVal sourceType As Variant _
)

    '        Excel.
    '
    '   .
    '    / Null,   .

    On Error GoTo ErrorHandler

    Dim reportTitle As String
    Dim templateFileName As String
    Dim outputPrefix As String
    Dim SqlText As String
    Dim outputPath As String

    reportTitle = GetReportTitle(reportCode)
    templateFileName = GetReportTemplateFileName(reportCode)
    outputPrefix = GetReportOutputPrefix(reportCode)

    SqlText = BuildReportSql( _
        reportCode, _
        dateFrom, _
        dateTo, _
        sortValue, _
        fermentationNumber, _
        packageType, _
        sourceType _
    )

    If Len(SqlText) = 0 Then
        Err.Raise vbObjectError + 7000, , _
            "   SQL  : " & reportCode
    End If

    outputPath = BuildReportOutputPath(outputPrefix)

    Call CopyTemplateToOutput(templateFileName, outputPath)

    Call FillExcelReport( _
        outputPath:=outputPath, _
        sheetName:=REPORT_SHEET_NAME, _
        reportTitle:=reportTitle, _
        SqlText:=SqlText, _
        dateFrom:=dateFrom, _
        dateTo:=dateTo, _
        sortValue:=sortValue, _
        fermentationNumber:=fermentationNumber, _
        packageType:=packageType, _
        sourceType:=sourceType _
    )

    Application.FollowHyperlink outputPath

    MsgBox "   Excel:" & vbCrLf & outputPath, _
           vbInformation, _
           " "

    Exit Sub

ErrorHandler:

    MsgBox "     Excel." & vbCrLf & vbCrLf & _
           ": " & reportCode & vbCrLf & _
           ": " & Err.Description, _
           vbCritical, _
           " "

End Sub


Public Sub OpenPivovarReportsFolder()

    '     Excel-.
    '
    ' :
    '   CurrentProject.Path\Reports
    '
    '      .

    On Error GoTo ErrorHandler

    Dim reportsFolderPath As String

    reportsFolderPath = GetReportsFolderPath()

    If Len(Dir(reportsFolderPath, vbDirectory)) = 0 Then
        MkDir reportsFolderPath
    End If

    Shell "explorer.exe " & Chr$(34) & reportsFolderPath & Chr$(34), vbNormalFocus

    Exit Sub

ErrorHandler:

    MsgBox "     ." & vbCrLf & _
           ": " & reportsFolderPath & vbCrLf & vbCrLf & _
           ": " & Err.Description, _
           vbExclamation, _
           " "

End Sub


' ============================================================
'  SQL 
' ============================================================

Private Function BuildReportSql( _
    ByVal reportCode As String, _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant, _
    ByVal packageType As Variant, _
    ByVal sourceType As Variant _
) As String

    '  SQL   .
    '
    ' :
    '         
    '     q_,     
    '   ,     /.

    Select Case reportCode

        Case REPORT_FILLING_DETAIL

            BuildReportSql = BuildFillingDetailSql( _
                dateFrom, _
                dateTo, _
                sortValue, _
                fermentationNumber, _
                packageType, _
                sourceType _
            )

        Case REPORT_FILLING_BY_PACKAGE

            BuildReportSql = BuildFillingByPackageSql( _
                dateFrom, _
                dateTo, _
                sortValue, _
                fermentationNumber, _
                packageType, _
                sourceType _
            )

        Case REPORT_FILLING_BY_SORT

            BuildReportSql = BuildFillingBySortSql( _
                dateFrom, _
                dateTo, _
                sortValue, _
                fermentationNumber, _
                packageType, _
                sourceType _
            )

        Case REPORT_FILLING_BY_BATCH

            BuildReportSql = BuildFillingByBatchSql( _
                dateFrom, _
                dateTo, _
                sortValue, _
                fermentationNumber, _
                packageType, _
                sourceType _
            )

        Case REPORT_FILTRATION_LOSSES

            BuildReportSql = BuildFiltrationLossesSql( _
                dateFrom, _
                dateTo, _
                sortValue, _
                fermentationNumber _
            )

        Case REPORT_BATCH_STATE

            BuildReportSql = BuildBatchStateSql( _
                sortValue, _
                fermentationNumber _
            )

        Case Else

            BuildReportSql = ""

    End Select

End Function


Private Function BuildFillingDetailSql( _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant, _
    ByVal packageType As Variant, _
    ByVal sourceType As Variant _
) As String

    Dim whereText As String

    whereText = BuildFillingWhere( _
        dateFrom, _
        dateTo, _
        sortValue, _
        fermentationNumber, _
        packageType, _
        sourceType _
    )

    BuildFillingDetailSql = _
        "SELECT " & _
        "[ ], " & _
        "[ ], " & _
        "[ ], " & _
        "[], " & _
        "[ ], " & _
        "[ ], " & _
        "[ ], " & _
        "[ ], " & _
        "[ ], " & _
        "[  ()], " & _
        "[ ()], " & _
        "[ ], " & _
        "[] " & _
        "FROM [q_] " & _
        whereText & _
        " ORDER BY [ ], [ ];"

End Function


Private Function BuildFillingByPackageSql( _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant, _
    ByVal packageType As Variant, _
    ByVal sourceType As Variant _
) As String

    Dim whereText As String

    whereText = BuildFillingWhere( _
        dateFrom, _
        dateTo, _
        sortValue, _
        fermentationNumber, _
        packageType, _
        sourceType _
    )

    BuildFillingByPackageSql = _
        "SELECT " & _
        "[], " & _
        "[], " & _
        "[], " & _
        "[], " & _
        "[ ], " & _
        "[ ], " & _
        "[  ()], " & _
        "Sum(Nz([ ], 0)) AS [  ], " & _
        "Sum(Nz([ ()], 0)) AS [ ()], " & _
        "Count([ ]) AS [ ] " & _
        "FROM [q_] " & _
        whereText & _
        " GROUP BY " & _
        "[], [], [], [], [ ], [ ], [  ()] " & _
        " ORDER BY [] DESC, [] DESC, [], [ ], [ ];"

End Function


Private Function BuildFillingBySortSql( _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant, _
    ByVal packageType As Variant, _
    ByVal sourceType As Variant _
) As String

    Dim whereText As String

    whereText = BuildFillingWhere( _
        dateFrom, _
        dateTo, _
        sortValue, _
        fermentationNumber, _
        packageType, _
        sourceType _
    )

    BuildFillingBySortSql = _
        "SELECT " & _
        "[], " & _
        "[], " & _
        "[], " & _
        "[], " & _
        "Sum(Nz([ ()], 0)) AS [ ()], " & _
        "Sum(Nz([ ], 0)) AS [  ], " & _
        "Count([ ]) AS [ ], " & _
        "Min([ ]) AS [ ], " & _
        "Max([ ]) AS [ ] " & _
        "FROM [q_] " & _
        whereText & _
        " GROUP BY [], [], [], [] " & _
        " ORDER BY [] DESC, [] DESC, [];"

End Function


Private Function BuildFillingByBatchSql( _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant, _
    ByVal packageType As Variant, _
    ByVal sourceType As Variant _
) As String

    Dim whereText As String

    whereText = BuildFillingWhere( _
        dateFrom, _
        dateTo, _
        sortValue, _
        fermentationNumber, _
        packageType, _
        sourceType _
    )

    BuildFillingByBatchSql = _
        "SELECT " & _
        "[ ], " & _
        "[], " & _
        "[ ], " & _
        "[ ], " & _
        "[ ], " & _
        "[  ()], " & _
        "Sum(Nz([ ], 0)) AS [  ], " & _
        "Sum(Nz([ ()], 0)) AS [ ()], " & _
        "Count([ ]) AS [ ], " & _
        "Min([ ]) AS [ ], " & _
        "Max([ ]) AS [ ] " & _
        "FROM [q_] " & _
        whereText & _
        " GROUP BY " & _
        "[ ], [], [ ], [ ], [ ], [  ()] " & _
        " ORDER BY [ ] DESC, [], [ ], [ ];"

End Function


Private Function BuildFiltrationLossesSql( _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant _
) As String

    Dim whereText As String

    whereText = BuildFiltrationWhere( _
        dateFrom, _
        dateTo, _
        sortValue, _
        fermentationNumber _
    )

    BuildFiltrationLossesSql = _
        "SELECT " & _
        "[], " & _
        "[], " & _
        "[], " & _
        "[], " & _
        "[ ], " & _
        "[ ], " & _
        "Sum(Nz([  ()], 0)) AS [  ()], " & _
        "Sum(Nz([  ()], 0)) AS [  ()], " & _
        "Sum(Nz([()], 0)) AS [ ()], " & _
        "IIf(Sum(Nz([  ()], 0)) = 0, Null, " & _
        "Round(Sum(Nz([()], 0)) / Sum(Nz([  ()], 0)) * 100, 2)) AS [(%)], " & _
        "Count([ ]) AS [ ], " & _
        "Min([ ]) AS [ ], " & _
        "Max([ ]) AS [ ] " & _
        "FROM [q_] " & _
        whereText & _
        " GROUP BY " & _
        "[], [], [], [], [ ], [ ] " & _
        " ORDER BY [] DESC, [] DESC, [], [ ];"

End Function


Private Function BuildBatchStateSql( _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant _
) As String

    Dim whereText As String

    whereText = ""

    If HasValue(sortValue) Then
        Call AddWherePart(whereText, "[] = " & SqlTextLiteral(sortValue))
    End If

    If HasValue(fermentationNumber) Then
        If IsNumeric(fermentationNumber) Then
            Call AddWherePart(whereText, "[ ] = " & CLng(fermentationNumber))
        End If
    End If

    If Len(whereText) > 0 Then
        whereText = " WHERE " & whereText
    End If

    BuildBatchStateSql = _
        "SELECT " & _
        "[ ], " & _
        "[], " & _
        "[ ], " & _
        "[   ()], " & _
        "[ ()], " & _
        "[ ()], " & _
        "[ ()], " & _
        "[   ()], " & _
        "[   ()], " & _
        "[()], " & _
        "[ ], " & _
        "[ ], " & _
        "[  (%)], " & _
        "[  (%)] " & _
        "FROM [q_] " & _
        whereText & _
        " ORDER BY [ ] DESC, [];"

End Function


' ============================================================
' WHERE-
' ============================================================

Private Function BuildFillingWhere( _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant, _
    ByVal packageType As Variant, _
    ByVal sourceType As Variant _
) As String

    Dim whereText As String

    whereText = ""

    If HasValue(dateFrom) Then
        If IsDate(dateFrom) Then
            Call AddWherePart(whereText, "[ ] >= " & SqlDateLiteral(dateFrom))
        End If
    End If

    If HasValue(dateTo) Then
        If IsDate(dateTo) Then
            Call AddWherePart(whereText, "[ ] < " & SqlDateLiteral(DateAdd("d", 1, CDate(dateTo))))
        End If
    End If

    If HasValue(sortValue) Then
        Call AddWherePart(whereText, "[] = " & SqlTextLiteral(sortValue))
    End If

    If HasValue(fermentationNumber) Then
        If IsNumeric(fermentationNumber) Then
            Call AddWherePart(whereText, "[ ] = " & CLng(fermentationNumber))
        End If
    End If

    If HasValue(packageType) Then
        Call AddWherePart(whereText, "[ ] = " & SqlTextLiteral(packageType))
    End If

    If HasValue(sourceType) Then
        Call AddWherePart(whereText, "[ ] = " & SqlTextLiteral(sourceType))
    End If

    If Len(whereText) > 0 Then
        BuildFillingWhere = " WHERE " & whereText
    Else
        BuildFillingWhere = ""
    End If

End Function


Private Function BuildFiltrationWhere( _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant _
) As String

    Dim whereText As String

    whereText = ""

    If HasValue(dateFrom) Then
        If IsDate(dateFrom) Then
            Call AddWherePart(whereText, "[ ] >= " & SqlDateLiteral(dateFrom))
        End If
    End If

    If HasValue(dateTo) Then
        If IsDate(dateTo) Then
            Call AddWherePart(whereText, "[ ] < " & SqlDateLiteral(DateAdd("d", 1, CDate(dateTo))))
        End If
    End If

    If HasValue(sortValue) Then
        Call AddWherePart(whereText, "[] = " & SqlTextLiteral(sortValue))
    End If

    If HasValue(fermentationNumber) Then
        If IsNumeric(fermentationNumber) Then
            Call AddWherePart(whereText, "[ ] = " & CLng(fermentationNumber))
        End If
    End If

    If Len(whereText) > 0 Then
        BuildFiltrationWhere = " WHERE " & whereText
    Else
        BuildFiltrationWhere = ""
    End If

End Function


' ============================================================
'  Excel
' ============================================================

Private Sub FillExcelReport( _
    ByVal outputPath As String, _
    ByVal sheetName As String, _
    ByVal reportTitle As String, _
    ByVal SqlText As String, _
    ByVal dateFrom As Variant, _
    ByVal dateTo As Variant, _
    ByVal sortValue As Variant, _
    ByVal fermentationNumber As Variant, _
    ByVal packageType As Variant, _
    ByVal sourceType As Variant _
)

    '   Excel   Recordset.
    '
    '     :
    '   
    '
    '      :
    '   B3 -  
    '   D3 -  
    '   F3 - 
    '   H3 -  
    '   J3 -  
    '   L3 - 

    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object

    Dim rowIndex As Long
    Dim colIndex As Long
    Dim fieldIndex As Long
    Dim lastUsedRow As Long
    Dim lastUsedColumn As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset(SqlText, dbOpenSnapshot)

    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(outputPath)
    Set xlWs = xlWb.Worksheets(sheetName)

    ' ------------------------------------------------------------
    '   .
    ' ------------------------------------------------------------
    xlWs.Cells(1, 1).value = reportTitle

    xlWs.Cells(2, 1).value = ":"
    xlWs.Cells(2, 2).value = Format(Now(), "dd.mm.yyyy hh:nn:ss")

    xlWs.Cells(3, 1).value = " :"
    xlWs.Cells(3, 2).value = FormatFilterDate(dateFrom)

    xlWs.Cells(3, 3).value = " :"
    xlWs.Cells(3, 4).value = FormatFilterDate(dateTo)

    xlWs.Cells(3, 5).value = ":"
    xlWs.Cells(3, 6).value = FormatFilterValue(sortValue)

    xlWs.Cells(3, 7).value = " :"
    xlWs.Cells(3, 8).value = FormatFilterValue(fermentationNumber)

    xlWs.Cells(3, 9).value = " :"
    xlWs.Cells(3, 10).value = FormatFilterValue(packageType)

    xlWs.Cells(3, 11).value = ":"
    xlWs.Cells(3, 12).value = FormatFilterValue(sourceType)

    ' ------------------------------------------------------------
    '    .
    ' ------------------------------------------------------------
    lastUsedRow = xlWs.UsedRange.Rows.Count
    lastUsedColumn = xlWs.UsedRange.Columns.Count

    If lastUsedRow < DATA_START_ROW Then
        lastUsedRow = DATA_START_ROW
    End If

    If lastUsedColumn < 1 Then
        lastUsedColumn = 1
    End If

    If xlWs.AutoFilterMode Then
        xlWs.AutoFilterMode = False
    End If

    xlWs.Range( _
        xlWs.Cells(HEADER_ROW, 1), _
        xlWs.Cells(lastUsedRow + 1000, lastUsedColumn + 50) _
    ).ClearContents

    ' ------------------------------------------------------------
    '  .
    ' ------------------------------------------------------------
    For fieldIndex = 0 To rs.Fields.Count - 1

        colIndex = fieldIndex + 1
        xlWs.Cells(HEADER_ROW, colIndex).value = rs.Fields(fieldIndex).Name
        xlWs.Cells(HEADER_ROW, colIndex).Font.Bold = True

    Next fieldIndex

    ' ------------------------------------------------------------
    ' .
    ' ------------------------------------------------------------
    rowIndex = DATA_START_ROW

    Do While Not rs.EOF

        For fieldIndex = 0 To rs.Fields.Count - 1

            colIndex = fieldIndex + 1
            xlWs.Cells(rowIndex, colIndex).value = Nz(rs.Fields(fieldIndex).value, "")

        Next fieldIndex

        rowIndex = rowIndex + 1
        rs.MoveNext

    Loop

    ' ------------------------------------------------------------
    ' .
    ' ------------------------------------------------------------
    If rs.Fields.Count > 0 Then

        xlWs.Range( _
            xlWs.Cells(HEADER_ROW, 1), _
            xlWs.Cells(HEADER_ROW, rs.Fields.Count) _
        ).AutoFilter

        xlWs.Columns.AutoFit

    End If

    xlWb.Save
    xlWb.Close SaveChanges:=True
    xlApp.Quit

ExitPoint:

    On Error Resume Next

    If Not rs Is Nothing Then
        rs.Close
    End If

    Set rs = Nothing
    Set db = Nothing

    Set xlWs = Nothing
    Set xlWb = Nothing

    If Not xlApp Is Nothing Then
        xlApp.Quit
    End If

    Set xlApp = Nothing

    Exit Sub

ErrorHandler:

    Dim errorDescription As String
    errorDescription = Err.Description

    On Error Resume Next

    If Not xlWb Is Nothing Then
        xlWb.Close SaveChanges:=False
    End If

    If Not xlApp Is Nothing Then
        xlApp.Quit
    End If

    Err.Raise vbObjectError + 7100, , _
        "  Excel-: " & errorDescription

End Sub


' ============================================================
' ,   
' ============================================================

Private Sub CopyTemplateToOutput( _
    ByVal templateFileName As String, _
    ByVal outputPath As String _
)

    '      .

    On Error GoTo ErrorHandler

    Dim templatePath As String

    templatePath = GetTemplatePath(templateFileName)

    If Len(Dir(templatePath)) = 0 Then
        Err.Raise vbObjectError + 7200, , _
            "   :" & vbCrLf & templatePath
    End If

    If Len(Dir(GetReportsFolderPath(), vbDirectory)) = 0 Then
        MkDir GetReportsFolderPath()
    End If

    FileCopy templatePath, outputPath

    Exit Sub

ErrorHandler:

    Err.Raise Err.Number, Err.Source, Err.Description

End Sub


Private Function GetTemplatePath(ByVal templateFileName As String) As String

    GetTemplatePath = CurrentProject.Path & "\" & TEMPLATE_FOLDER & "\" & templateFileName

End Function


Private Function GetReportsFolderPath() As String

    GetReportsFolderPath = CurrentProject.Path & "\" & REPORTS_FOLDER

End Function


Private Function BuildReportOutputPath(ByVal outputPrefix As String) As String

    Dim fileName As String

    If Len(Dir(GetReportsFolderPath(), vbDirectory)) = 0 Then
        MkDir GetReportsFolderPath()
    End If

    fileName = outputPrefix & "_" & Format(Now(), "yyyymmdd_hhnnss") & ".xlsx"

    BuildReportOutputPath = GetReportsFolderPath() & "\" & fileName

End Function


' ============================================================
'       
' ============================================================

Private Function GetReportTitle(ByVal reportCode As String) As String

    Select Case reportCode

        Case REPORT_FILLING_DETAIL
            GetReportTitle = " - "

        Case REPORT_FILLING_BY_PACKAGE
            GetReportTitle = "  "

        Case REPORT_FILLING_BY_SORT
            GetReportTitle = "  "

        Case REPORT_FILLING_BY_BATCH
            GetReportTitle = "  "

        Case REPORT_FILTRATION_LOSSES
            GetReportTitle = " "

        Case REPORT_BATCH_STATE
            GetReportTitle = " "

        Case Else
            GetReportTitle = ""

    End Select

End Function


Private Function GetReportTemplateFileName(ByVal reportCode As String) As String

    Select Case reportCode

        Case REPORT_FILLING_DETAIL
            GetReportTemplateFileName = "Pivovar_Report_Filling_Detail_Template.xlsx"

        Case REPORT_FILLING_BY_PACKAGE
            GetReportTemplateFileName = "Pivovar_Report_Filling_By_Package_Template.xlsx"

        Case REPORT_FILLING_BY_SORT
            GetReportTemplateFileName = "Pivovar_Report_Filling_By_Sort_Template.xlsx"

        Case REPORT_FILLING_BY_BATCH
            GetReportTemplateFileName = "Pivovar_Report_Filling_By_Batch_Template.xlsx"

        Case REPORT_FILTRATION_LOSSES
            GetReportTemplateFileName = "Pivovar_Report_Filtration_Losses_Template.xlsx"

        Case REPORT_BATCH_STATE
            GetReportTemplateFileName = "Pivovar_Report_Batch_State_Template.xlsx"

        Case Else
            GetReportTemplateFileName = ""

    End Select

End Function


Private Function GetReportOutputPrefix(ByVal reportCode As String) As String

    Select Case reportCode

        Case REPORT_FILLING_DETAIL
            GetReportOutputPrefix = "_"

        Case REPORT_FILLING_BY_PACKAGE
            GetReportOutputPrefix = "_"

        Case REPORT_FILLING_BY_SORT
            GetReportOutputPrefix = "_"

        Case REPORT_FILLING_BY_BATCH
            GetReportOutputPrefix = "_"

        Case REPORT_FILTRATION_LOSSES
            GetReportOutputPrefix = "_"

        Case REPORT_BATCH_STATE
            GetReportOutputPrefix = "_"

        Case Else
            GetReportOutputPrefix = ""

    End Select

End Function


' ============================================================
'  
' ============================================================

Private Sub AddWherePart( _
    ByRef whereText As String, _
    ByVal wherePart As String _
)

    If Len(whereText) > 0 Then
        whereText = whereText & " AND "
    End If

    whereText = whereText & wherePart

End Sub


Private Function HasValue(ByVal sourceValue As Variant) As Boolean

    On Error GoTo ErrorHandler

    If IsNull(sourceValue) Then
        HasValue = False
    ElseIf Len(Trim$(CStr(sourceValue))) = 0 Then
        HasValue = False
    Else
        HasValue = True
    End If

    Exit Function

ErrorHandler:

    HasValue = False

End Function


Private Function SqlTextLiteral(ByVal valueText As Variant) As String

    SqlTextLiteral = "'" & Replace(CStr(valueText), "'", "''") & "'"

End Function


Private Function SqlDateLiteral(ByVal dateValue As Variant) As String

    SqlDateLiteral = "#" & Format$(CDate(dateValue), "yyyy\/mm\/dd") & "#"

End Function


Private Function FormatFilterValue(ByVal sourceValue As Variant) As String

    If HasValue(sourceValue) Then
        FormatFilterValue = CStr(sourceValue)
    Else
        FormatFilterValue = ""
    End If

End Function


Private Function FormatFilterDate(ByVal sourceValue As Variant) As String

    If HasValue(sourceValue) Then

        If IsDate(sourceValue) Then
            FormatFilterDate = Format(CDate(sourceValue), "dd.mm.yyyy")
        Else
            FormatFilterDate = CStr(sourceValue)
        End If

    Else

        FormatFilterDate = ""

    End If

End Function