Attribute VB_Name = "PricingDemo"
Option Explicit

' INDEPENDENT, CLEAN-ROOM VBA DEMONSTRATION — created for a 2026 portfolio.
' NOT code exported from the original employer-owned Access application.
'
' Models documented pricing behaviour:
'   1. Try category-specific price first if all five rates and all required
'      category costs are available; if not, fall back to project-wide price.
'   2. Apply discount AFTER gross-margin calculation.
'   3. Treat Material/Labour/Admin Null costs as zero (analogous to Nz), while
'      Other/Panel must be explicitly entered, including 0 when no cost.
'   4. If neither route is available, return Null.
'
' Material differences vs original:
'   - This is a stateless calculation, NOT an Access form event.
'   - Negative costs, invalid rates, and invalid discounts raise errors here.
'   - Currency rounding is performed ONCE at the end (half-up to cents);
'     original Access expression precision/storage rounding is unverified.
'   - The original ProjectCost uses a TotalSubform aggregate; this example
'     uses five synthetic category costs to build an illustrative total.
'   - The original double-click Refresh bug is NOT reproduced/fixed here.
'
' Array order: Material, Labour, Administration, Other Costs, Panel.
' Rates use decimal fractions (20 percent = 0.2); 0 is valid, Empty/Null missing.

Private Const CATEGORY_COUNT As Long = 5
Private Const IDX_OTHER As Long = 3
Private Const IDX_PANEL As Long = 4
Private Const ERR_INVALID_INPUT As Long = vbObjectError + 7101
Private Const ERR_INVALID_MARGIN As Long = vbObjectError + 7102
Private Const ERR_INVALID_DISCOUNT As Long = vbObjectError + 7103

Public Function CalculateSellingPrice(ByVal categoryCosts As Variant, _
                                      ByVal projectMargin As Variant, _
                                      ByVal categoryMargins As Variant, _
                                      ByVal discountInput As Variant) As Variant
    Dim i As Long
    Dim totalCost As Double
    Dim categorySelling As Double
    Dim rate As Double
    Dim discountRate As Double
    Dim categoryReady As Boolean
    Dim otherAndPanelPresent As Boolean
    Dim numericCosts(0 To 4) As Double

    If Not IsArray(categoryCosts) Or Not IsArray(categoryMargins) Then
        Err.Raise ERR_INVALID_INPUT, "PricingDemo", "Costs and category margins must be arrays."
    End If
    If ItemCount(categoryCosts) <> CATEGORY_COUNT Or _
       ItemCount(categoryMargins) <> CATEGORY_COUNT Then
        Err.Raise ERR_INVALID_INPUT, "PricingDemo", "Provide exactly five costs and five category margins."
    End If

    discountRate = ParseDiscount(discountInput)

    ' Separate "not entered" from an explicit zero for Other and Panel.
    otherAndPanelPresent = True
    For i = 0 To CATEGORY_COUNT - 1
        If IsBlankInput(categoryCosts(LBound(categoryCosts) + i)) Then
            numericCosts(i) = 0#
            If i = IDX_OTHER Or i = IDX_PANEL Then
                otherAndPanelPresent = False
            End If
        Else
            If Not IsNumeric(categoryCosts(LBound(categoryCosts) + i)) Then
                Err.Raise ERR_INVALID_INPUT, "PricingDemo", "Cost must be numeric."
            End If
            numericCosts(i) = CDbl(categoryCosts(LBound(categoryCosts) + i))
            If numericCosts(i) < 0# Then
                Err.Raise ERR_INVALID_INPUT, "PricingDemo", "Cost cannot be negative."
            End If
        End If
        totalCost = totalCost + numericCosts(i)
    Next i

    categoryReady = otherAndPanelPresent
    For i = 0 To CATEGORY_COUNT - 1
        If IsBlankInput(categoryMargins(LBound(categoryMargins) + i)) Then
            categoryReady = False
        End If
    Next i

    ' No mode selector: when the category calculation is available it wins,
    ' even if a project-wide margin was ALSO entered. Mirrors original priority.
    If categoryReady Then
        For i = 0 To CATEGORY_COUNT - 1
            rate = ParseMargin(categoryMargins(LBound(categoryMargins) + i))
            categorySelling = categorySelling + numericCosts(i) / (1# - rate)
        Next i
        CalculateSellingPrice = RoundHalfUpCents(categorySelling * (1# - discountRate))
        Exit Function
    End If

    ' When category pricing cannot produce a value, try the project-wide rate.
    If Not IsBlankInput(projectMargin) Then
        rate = ParseMargin(projectMargin)
        CalculateSellingPrice = RoundHalfUpCents(totalCost / (1# - rate) * (1# - discountRate))
    Else
        CalculateSellingPrice = Null
    End If
End Function

Private Function ItemCount(ByVal values As Variant) As Long
    ItemCount = UBound(values) - LBound(values) + 1
End Function

Private Function IsBlankInput(ByVal value As Variant) As Boolean
    If IsNull(value) Then
        IsBlankInput = True
    ElseIf IsEmpty(value) Then
        IsBlankInput = True
    ElseIf VarType(value) = vbString Then
        IsBlankInput = (Len(Trim$(CStr(value))) = 0)
    Else
        IsBlankInput = False
    End If
End Function

Private Function ParseMargin(ByVal value As Variant) As Double
    Dim rate As Double
    If Not IsNumeric(value) Then
        Err.Raise ERR_INVALID_MARGIN, "PricingDemo", "Margin must be numeric."
    End If
    rate = CDbl(value)
    If rate < 0# Or rate >= 1# Then
        Err.Raise ERR_INVALID_MARGIN, "PricingDemo", "Margin must be at least 0 and below 1."
    End If
    ParseMargin = rate
End Function

Private Function ParseDiscount(ByVal value As Variant) As Double
    Dim rate As Double
    If IsBlankInput(value) Then
        ParseDiscount = 0#
        Exit Function
    End If
    If Not IsNumeric(value) Then
        Err.Raise ERR_INVALID_DISCOUNT, "PricingDemo", "Discount must be numeric."
    End If
    rate = CDbl(value)
    If rate < 0# Or rate > 1# Then
        Err.Raise ERR_INVALID_DISCOUNT, "PricingDemo", "Discount must be between 0 and 1."
    End If
    ParseDiscount = rate
End Function

Private Function RoundHalfUpCents(ByVal value As Double) As Currency
    ' Nonnegative results by design; production storage precision unverified.
    RoundHalfUpCents = CCur(Fix(value * 100# + 0.5) / 100#)
End Function