Option Compare Database
Option Explicit

' ============================================================
' : mBeerMovement
'
' :
'          .
'
'  :
'    -> 
'
'  :
'   t
'   t
'   t
'   t
'
'  :
'         t.
'        .
' ============================================================


' -----------------------------
'   
' -----------------------------
Private Const VESSEL_TYPE_CCT As String = ""
Private Const VESSEL_TYPE_FORFAS As String = ""


' -----------------------------
'   
' -----------------------------
Private Const VESSEL_STATUS_FREE As String = ""
Private Const VESSEL_STATUS_BUSY As String = ""


' -----------------------------
'   
' -----------------------------
Private Const OPERATION_BREW_TO_CCT As String = "  "
Private Const OPERATION_ADJUSTMENT As String = ""


Private Function EscapeSqlValue(ByVal sourceText As String) As String

    '         SQL.

    EscapeSqlValue = Replace(sourceText, "'", "''")

End Function


Public Function GetVesselIdByTypeAndNumber( _
    ByVal vesselType As String, _
    ByVal vesselNumber As Long _
) As Long

    '     t    .
    '
    ' :
    '   GetVesselIdByTypeAndNumber("", 1) ->   1
    '
    '    ,  0.

    On Error GoTo ErrorHandler

    Dim whereText As String

    whereText = _
        "[ ] = '" & EscapeSqlValue(vesselType) & "' " & _
        "AND [ ] = " & vesselNumber & " " & _
        "AND [] = True"

    GetVesselIdByTypeAndNumber = Nz(DLookup("[]", "t", whereText), 0)

    Exit Function

ErrorHandler:

    GetVesselIdByTypeAndNumber = 0

End Function


Public Function GetVesselNameById(ByVal vesselId As Long) As String

    '      .
    '
    ' :
    '    1 -> 1

    On Error GoTo ErrorHandler

    GetVesselNameById = Nz(DLookup("[]", "t", "[] = " & vesselId), "")

    Exit Function

ErrorHandler:

    GetVesselNameById = ""

End Function


Public Function GetVesselTypeById(ByVal vesselId As Long) As String

    '      .
    '
    ' :
    '   
    '   

    On Error GoTo ErrorHandler

    GetVesselTypeById = Nz(DLookup("[ ]", "t", "[] = " & vesselId), "")

    Exit Function

ErrorHandler:

    GetVesselTypeById = ""

End Function


Public Function GetVesselMaxVolume(ByVal vesselId As Long) As Double

    '    .
    '
    '       4000 ,
    '     t,     .

    On Error GoTo ErrorHandler

    GetVesselMaxVolume = Nz(DLookup("[ ()]", "t", "[] = " & vesselId), 0)

    Exit Function

ErrorHandler:

    GetVesselMaxVolume = 0

End Function


Public Function GetVesselCurrentVolume(ByVal vesselId As Long) As Double

    '      t.
    '
    '   ,  0.

    On Error GoTo ErrorHandler

    GetVesselCurrentVolume = Nz(DLookup("[()]", "t", "[ ] = " & vesselId), 0)

    Exit Function

ErrorHandler:

    GetVesselCurrentVolume = 0

End Function


Public Function GetVesselFreeVolume(ByVal vesselId As Long) As Double

    '    .
    '
    ' :
    '    =   -  

    On Error GoTo ErrorHandler

    GetVesselFreeVolume = GetVesselMaxVolume(vesselId) - GetVesselCurrentVolume(vesselId)

    Exit Function

ErrorHandler:

    GetVesselFreeVolume = 0

End Function


Public Function GetBrewSentToVessel( _
    ByVal brewId As Long, _
    ByVal vesselId As Long _
) As Double

    ' ,      
    '   .
    '
    '    .

    On Error GoTo ErrorHandler

    Dim whereText As String

    whereText = _
        "[ ] = " & brewId & " " & _
        "AND [  ] = " & vesselId & " " & _
        "AND ([] = False OR [] Is Null)"

    GetBrewSentToVessel = Nz(DSum("[  ()]", "t", whereText), 0)

    Exit Function

ErrorHandler:

    GetBrewSentToVessel = 0

End Function


Public Function GetBrewTotalSentToCCT(ByVal brewId As Long) As Double

    ' ,      
    '    .
    '
    '      .

    On Error GoTo ErrorHandler

    Dim whereText As String

    whereText = _
        "[ ] = " & brewId & " " & _
        "AND ([] = False OR [] Is Null)"

    GetBrewTotalSentToCCT = Nz(DSum("[  ()]", "t", whereText), 0)

    Exit Function

ErrorHandler:

    GetBrewTotalSentToCCT = 0

End Function


Private Function EnsureVesselStateRecord(ByVal vesselId As Long) As Boolean

    '      .
    '
    '   ,   :
    '    = 0
    '    = 
    '    = 
    '     = 
    '
    '    :
    '      t,
    '       .

    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim stateCount As Long

    Set db = CurrentDb

    stateCount = DCount("*", "t", "[ ] = " & vesselId)

    If stateCount = 0 Then

        db.Execute _
            "INSERT INTO t " & _
            "([ ], [()], [], [], [ ]) " & _
            "VALUES " & _
            "(" & vesselId & ", 0, False, '" & VESSEL_STATUS_FREE & "', False);", _
            dbFailOnError

    End If

    EnsureVesselStateRecord = True

ExitPoint:

    Set db = Nothing
    Exit Function

ErrorHandler:

    EnsureVesselStateRecord = False
    Resume ExitPoint

End Function


Public Function GetNextFermentationNumber() As Long

    '    .
    '
    '    :
    '   t.[ ]
    '   t.[ ]
    '
    '  ,      
    '       .

    On Error GoTo ErrorHandler

    Dim maxStateNumber As Long
    Dim maxJournalNumber As Long

    maxStateNumber = Nz(DMax("[ ]", "t", "[ ] Is Not Null"), 0)
    maxJournalNumber = Nz(DMax("[ ]", "t", "[ ] Is Not Null"), 0)

    If maxStateNumber > maxJournalNumber Then
        GetNextFermentationNumber = maxStateNumber + 1
    Else
        GetNextFermentationNumber = maxJournalNumber + 1
    End If

    Exit Function

ErrorHandler:

    GetNextFermentationNumber = 0

End Function


Private Function AppendBrewNumberToText( _
    ByVal currentText As String, _
    ByVal brewNumber As Long _
) As String

    '      [ ],     .
    '
    ' :
    '   currentText = "1; 2"
    '   brewNumber = 3
    '    = "1; 2; 3"
    '
    '    ,    .

    On Error GoTo ErrorHandler

    Dim normalizedText As String
    Dim searchText As String

    normalizedText = "; " & Trim(Nz(currentText, "")) & ";"
    normalizedText = Replace(normalizedText, ";;", ";")

    searchText = "; " & CStr(brewNumber) & ";"

    If InStr(1, normalizedText, searchText, vbTextCompare) > 0 Then

        AppendBrewNumberToText = Trim(Nz(currentText, ""))

    Else

        If Trim(Nz(currentText, "")) = "" Then
            AppendBrewNumberToText = CStr(brewNumber)
        Else
            AppendBrewNumberToText = Trim(currentText) & "; " & CStr(brewNumber)
        End If

    End If

    Exit Function

ErrorHandler:

    AppendBrewNumberToText = Trim(Nz(currentText, ""))

End Function


Public Function RegisterBrewToVessel( _
    ByVal brewId As Long, _
    ByVal brewNumber As Long, _
    ByVal brewDate As Date, _
    ByVal recipeName As String, _
    ByVal vesselId As Long, _
    ByVal operationVolumeL As Double, _
    Optional ByVal operationType As String = "", _
    Optional ByVal commentText As String = "" _
) As Boolean

    '   :
    '    -> 
    '
    '  :
    '      t1 / t2,
    '        t.
    '
    ' :
    '
    '      +100 :
    '       -  ;
    '       -   ;
    '       -    ;
    '       -   .
    '
    '    +10 :
    '       -   ;
    '       -     ;
    '       -    ;
    '       -    .
    '
    '    -10 :
    '       -   ;
    '       -     ;
    '       -    ;
    '       -    .
    '
    '     .

    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Dim rsMovement As DAO.Recordset
    Dim rsState As DAO.Recordset

    '  ,  Rollback     BeginTrans.
    '   Access    :
    ' "        ".
    Dim transactionStarted As Boolean

    Dim stepName As String
    Dim errorDescription As String

    Dim vesselType As String
    Dim vesselName As String
    Dim maxVolumeL As Double
    Dim currentVolumeL As Double
    Dim newVolumeL As Double

    Dim isBusy As Boolean
    Dim currentSort As String
    Dim currentBrewNumbers As String

    Dim fermentationNumber As Long
    Dim isNewCycle As Boolean
    Dim isPositiveBrewIntake As Boolean

    RegisterBrewToVessel = False
    transactionStarted = False

    Set ws = DBEngine.Workspaces(0)
    Set db = CurrentDb

    '     ,   .
    operationType = Trim(operationType)

    If operationType = "" Then

        If operationVolumeL < 0 Then
            operationType = OPERATION_ADJUSTMENT
        Else
            operationType = OPERATION_BREW_TO_CCT
        End If

    End If

    '     .
    If operationVolumeL < 0 Then
        operationType = OPERATION_ADJUSTMENT
    End If

    '    ->      .
    If StrComp(operationType, OPERATION_BREW_TO_CCT, vbTextCompare) <> 0 _
       And StrComp(operationType, OPERATION_ADJUSTMENT, vbTextCompare) <> 0 Then

        Err.Raise vbObjectError + 2020, , _
            "  : " & operationType

    End If

    '    .
    stepName = "  "

    If brewId <= 0 Then
        Err.Raise vbObjectError + 2000, , "   ."
    End If

    If brewNumber <= 0 Then
        Err.Raise vbObjectError + 2001, , "   ."
    End If

    If Trim(recipeName) = "" Then
        Err.Raise vbObjectError + 2002, , "  /."
    End If

    If vesselId <= 0 Then
        Err.Raise vbObjectError + 2003, , "   ."
    End If

    If operationVolumeL = 0 Then
        Err.Raise vbObjectError + 2004, , "      0."
    End If

    If StrComp(operationType, OPERATION_ADJUSTMENT, vbTextCompare) = 0 _
       And Trim(commentText) = "" Then

        Err.Raise vbObjectError + 2005, , _
            "    ."

    End If

    '  .
    stepName = " "

    vesselType = GetVesselTypeById(vesselId)
    vesselName = GetVesselNameById(vesselId)

    If vesselType = "" Then
        Err.Raise vbObjectError + 2006, , "     ."
    End If

    If vesselType <> VESSEL_TYPE_CCT Then
        Err.Raise vbObjectError + 2007, , "       ."
    End If

    maxVolumeL = GetVesselMaxVolume(vesselId)

    If maxVolumeL <= 0 Then
        Err.Raise vbObjectError + 2008, , "     ."
    End If

    '    .
    stepName = "   "

    If EnsureVesselStateRecord(vesselId) = False Then
        Err.Raise vbObjectError + 2009, , "      ."
    End If

    '       .
    stepName = "   "

    Set rsState = db.OpenRecordset( _
        "SELECT * FROM [t] " & _
        "WHERE [ ] = " & vesselId & ";", _
        dbOpenDynaset _
    )

    If rsState.EOF Then
        Err.Raise vbObjectError + 2010, , "    ."
    End If

    currentVolumeL = Nz(rsState.Fields("()").value, 0)
    isBusy = Nz(rsState.Fields("").value, False)
    currentSort = Nz(rsState.Fields("").value, "")
    currentBrewNumbers = Nz(rsState.Fields(" ").value, "")
    fermentationNumber = Nz(rsState.Fields(" ").value, 0)

    '   0,       .
    If currentVolumeL <= 0 Then
        isBusy = False
    End If

    isNewCycle = (isBusy = False)

    '    .
    isPositiveBrewIntake = _
        (operationVolumeL > 0 And StrComp(operationType, OPERATION_BREW_TO_CCT, vbTextCompare) = 0)

    '    .
    If isNewCycle = True Then

        If operationVolumeL <= 0 Then
            Err.Raise vbObjectError + 2011, , _
                vesselName & " .         ."
        End If

        If StrComp(operationType, OPERATION_BREW_TO_CCT, vbTextCompare) <> 0 Then
            Err.Raise vbObjectError + 2021, , _
                vesselName & " ." & vbCrLf & _
                "     ." & vbCrLf & _
                "      ."
        End If

        fermentationNumber = GetNextFermentationNumber()

        If fermentationNumber <= 0 Then
            Err.Raise vbObjectError + 2012, , "     ."
        End If

    Else

        '   .
        '      .
        If StrComp(Trim(currentSort), Trim(recipeName), vbTextCompare) <> 0 Then

            Err.Raise vbObjectError + 2013, , _
                vesselName & "    """ & currentSort & """." & vbCrLf & _
                "   """ & recipeName & """."

        End If

        If fermentationNumber <= 0 Then
            Err.Raise vbObjectError + 2014, , _
                "      ."
        End If

    End If

    '   .
    stepName = "  "

    newVolumeL = currentVolumeL + operationVolumeL

    If newVolumeL < 0 Then
        Err.Raise vbObjectError + 2015, , _
            " :    " & vesselName & "  ."
    End If

    If newVolumeL > maxVolumeL Then
        Err.Raise vbObjectError + 2016, , _
            " :     " & vesselName & "." & vbCrLf & _
            " : " & currentVolumeL & " " & vbCrLf & _
            " : " & operationVolumeL & " " & vbCrLf & _
            " : " & newVolumeL & " " & vbCrLf & _
            " : " & maxVolumeL & " "
    End If

    '  .
    stepName = " "

    ws.BeginTrans
    transactionStarted = True

    '     t.
    stepName = "    t"

    Set rsMovement = db.OpenRecordset("t", dbOpenDynaset)

    rsMovement.AddNew

    rsMovement.Fields(" ").value = Now()
    rsMovement.Fields(" ").value = brewId
    rsMovement.Fields(" ").value = brewNumber
    rsMovement.Fields("  ").value = vesselId
    rsMovement.Fields(" ").value = fermentationNumber
    rsMovement.Fields("").value = recipeName
    rsMovement.Fields(" ").value = operationType
    rsMovement.Fields("  ()").value = operationVolumeL
    rsMovement.Fields("").value = commentText
    rsMovement.Fields("").value = False

    rsMovement.Update

    '   .
    stepName = "  "

    rsState.Edit

    If newVolumeL = 0 Then

        '     0,
        '  .
        rsState.Fields("").value = Null
        rsState.Fields("").value = Null
        rsState.Fields("()").value = 0
        rsState.Fields("").value = False
        rsState.Fields("").value = VESSEL_STATUS_FREE
        rsState.Fields(" ").value = Null
        rsState.Fields(" ").value = Null
        rsState.Fields("  ").value = Null
        rsState.Fields(" ").value = False
        rsState.Fields("  ").value = Null

    Else

        '   .
        rsState.Fields("()").value = newVolumeL
        rsState.Fields("").value = True
        rsState.Fields("").value = VESSEL_STATUS_BUSY
        rsState.Fields("").value = recipeName
        rsState.Fields(" ").value = fermentationNumber

        If isNewCycle = True Then

            '       ,
            '      .
            rsState.Fields("").value = brewDate
            rsState.Fields(" ").value = CStr(brewNumber)
            rsState.Fields("  ").value = brewDate

            '       .
            rsState.Fields(" ").value = False
            rsState.Fields("  ").value = Null

        Else

            '     ,
            '         .
            '
            '    ,   
            '     .
            If isPositiveBrewIntake = True Then

                rsState.Fields(" ").value = _
                    AppendBrewNumberToText(currentBrewNumbers, brewNumber)

                '        .
                rsState.Fields("  ").value = brewDate

                '    
                '    .
                rsState.Fields(" ").value = False
                rsState.Fields("  ").value = Null

            End If

        End If

    End If

    rsState.Update

    '  .
    stepName = " "

    If transactionStarted = True Then
        ws.CommitTrans
        transactionStarted = False
    End If

    RegisterBrewToVessel = True

ExitPoint:

    On Error Resume Next

    If Not rsMovement Is Nothing Then
        rsMovement.Close
    End If

    If Not rsState Is Nothing Then
        rsState.Close
    End If

    Set rsMovement = Nothing
    Set rsState = Nothing
    Set db = Nothing
    Set ws = Nothing

    Exit Function

ErrorHandler:

    errorDescription = Err.Description

    On Error Resume Next

    If transactionStarted = True Then
        ws.Rollback
        transactionStarted = False
    End If

    RegisterBrewToVessel = False

    MsgBox "    ." & vbCrLf & vbCrLf & _
           ": " & stepName & vbCrLf & _
           ": " & errorDescription, _
           vbExclamation, _
           " "

    Resume ExitPoint

End Function