/*
* Gets invoice details to populate MoversSuite bookmark values.
*/
create procedure [dbo].[GetInvoiceDetailsForLetter]
    @inInvoicedHeaderId int
as

declare @theWeightInvoiceLabel varchar(16)
declare @theMilesInvoiceLabel varchar(16)
select @theWeightInvoiceLabel = ' ' + InvoiceLabel + ', ' from RateMatrixRangeType where FieldName = 'Weight'
select @theMilesInvoiceLabel = ' ' + InvoiceLabel from RateMatrixRangeType where FieldName = 'Miles'

-- Put the Rate matrix values into a temporary table so that
-- we can join it into the main select.
declare @theRateMatrixBasisTable table
(
    RateMatrixBasisTableID int identity( 1, 1 ),
    GroupBillingMinorItemID int,
    DetailBillingMinorItemID int,
    Miles int,
    Weight int
)
insert into @theRateMatrixBasisTable
(
    GroupBillingMinorItemID,
    DetailBillingMinorItemID,
    Miles,
    Weight
)
select InvoicedGroup.BillingMinorFID as GroupBillingMinorItemID,
    null as DetailBillingMinorItemID,
    case
        when XRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
        when YRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
        else null
    end as Miles,
    case
        when XRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
        when YRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
        else null
    end as Weight
from InvoicedGroup
inner join InvoicedHeader on InvoicedHeader.IHPriKey = InvoicedGroup.IHPriKey  
inner join RateMatrixXYBasis on RateMatrixXYBasis.BillingMinorItemFID = InvoicedGroup.BillingMinorFID
inner join RateMatrix on RateMatrix.RateMatrixID = RateMatrixXYBasis.RateMatrixFID
inner join RateMatrixRangeType XRateMatrixRangeType on XRateMatrixRangeType.RateMatrixRangeTypeID = RateMatrix.RateMatrixRangeTypeXFID
inner join RateMatrixRangeType YRateMatrixRangeType on YRateMatrixRangeType.RateMatrixRangeTypeID = RateMatrix.RateMatrixRangeTypeYFID
inner join Orders on Orders.PriKey = InvoicedHeader.OrdPriKey
where InvoicedGroup.IHPriKey = @inInvoicedHeaderId and
    (
        XRateMatrixRangeType.FieldName in ( 'Weight', 'Miles' ) or
        YRateMatrixRangeType.FieldName in ( 'Weight', 'Miles' )
    ) and
    (
        Orders.Weight !=
        case
            when XRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
            when YRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
            else null
        end or
        Orders.Miles !=
        case
            when XRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
            when YRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
            else null
        end
    )
union    
select
    null as GroupBillingMinorItemID,
    InvoicedDetail.BillingMinorFID as DetailBillingMinorItemID,
    case
        when XRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
        when YRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
        else null
    end as Miles,
    case
        when XRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
        when YRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
        else null
    end as Weight
from InvoicedDetail
inner join InvoicedGroup on InvoicedGroup.IGPriKey = InvoicedDetail.IGPriKey  
inner join InvoicedHeader on InvoicedHeader.IHPriKey = InvoicedGroup.IHPriKey  
inner join RateMatrixXYBasis on RateMatrixXYBasis.BillingMinorItemFID = InvoicedDetail.BillingMinorFID
inner join RateMatrix on RateMatrix.RateMatrixID = RateMatrixXYBasis.RateMatrixFID
inner join RateMatrixRangeType XRateMatrixRangeType on XRateMatrixRangeType.RateMatrixRangeTypeID = RateMatrix.RateMatrixRangeTypeXFID
inner join RateMatrixRangeType YRateMatrixRangeType on YRateMatrixRangeType.RateMatrixRangeTypeID = RateMatrix.RateMatrixRangeTypeYFID
inner join Orders on Orders.PriKey = InvoicedHeader.OrdPriKey
where InvoicedGroup.IHPriKey = @inInvoicedHeaderId and
    (
        XRateMatrixRangeType.FieldName in ( 'Weight', 'Miles' ) or
        YRateMatrixRangeType.FieldName in ( 'Weight', 'Miles' )
    ) and
    (
        Orders.Weight !=
        case
            when XRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
            when YRateMatrixRangeType.FieldName = 'Weight' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
            else null
        end or
        Orders.Miles !=
        case
            when XRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.XBreakPoint, RateMatrixXYBasis.XRangeValue )
            when YRateMatrixRangeType.FieldName = 'Miles' then isnull( RateMatrixXYBasis.YBreakPoint, RateMatrixXYBasis.YRangeValue )
            else null
        end
    )


-- Get all groups and details for the invoiced header.
select
    InvoicedGroup.IGPriKey as GroupId,
    InvoicedGroup.ItemCode as GroupItemCode,
    InvoicedGroup.[Description] +
        case
            when RateMatrixGroup.GroupBillingMinorItemID is not null and InvoicedDetail.IDPriKey is null
                then char(13) + char(10) + ltrim( str( RateMatrixGroup.[Weight] ) ) + @theWeightInvoiceLabel + ltrim( str( RateMatrixGroup.Miles ) + @theMilesInvoiceLabel )
            when RateMatrixDetail.DetailBillingMinorItemID is not null and InvoicedDetail.IDPriKey is null
                then char(13) + char(10) + ltrim( str( RateMatrixDetail.[Weight] ) ) + @theWeightInvoiceLabel + ltrim( str( RateMatrixDetail.Miles ) + @theMilesInvoiceLabel )
            else ''
        end as GroupDescription,
    InvoicedGroup.Quantity as GroupQuantity,
    InvoicedGroup.Quantity2 as GroupQuantity2,
    InvoicedGroupRateType.QuantityOneInvoiceLabel as GroupQuantity2Description,
    InvoicedGroupRateType.QuantityTwoInvoiceLabel as GroupQuantityDescription,
    InvoicedGroup.Rate as GroupRate,
    InvoicedGroup.GrossAmount as GroupGrossAmount,
    InvoicedGroup.Discount as GroupDiscount,
    InvoicedGroup.Amount as GroupAmount,
    InvoicedGroup.SortOrder as GroupSortOrder,
    -- Currently, all details of a group are either hidden or displayed.  It is only necessary to look
    -- at the first detail to determine if the group record or the detail group header should be displayed.
    GroupDetailsHidden =
        case
            when isnull( InvoicedDetail.[Hidden], 0 ) = 1 then 1
            else 0
        end,
    InvoicedDetail.IDPriKey as DetailId,
    InvoicedDetail.ItemCode as DetailItemCode,
    InvoicedDetail.[Description] +
        case
            when RateMatrixGroup.GroupBillingMinorItemID is not null
                then char(13) + char(10) + ltrim( str( RateMatrixGroup.Weight ) ) + @theWeightInvoiceLabel + ltrim( str( RateMatrixGroup.Miles ) ) + @theMilesInvoiceLabel
            when RateMatrixDetail.DetailBillingMinorItemID is not null
                then char(13) + char(10) + ltrim( str( RateMatrixDetail.Weight ) ) + @theWeightInvoiceLabel + ltrim( str( RateMatrixDetail.Miles ) ) + @theMilesInvoiceLabel
            else ''
        end as DetailDescription,
    InvoicedDetail.Quantity as DetailQuantity,
    InvoicedDetail.Quantity2 as DetailQuantity2,
    InvoicedDetailRateType.QuantityOneInvoiceLabel as DetailQuantity2Description,
    InvoicedDetailRateType.QuantityTwoInvoiceLabel as DetailQuantityDescription,
    InvoicedDetail.Rate as DetailRate,
    InvoicedDetail.GrossAmount as DetailGrossAmount,
    InvoicedDetail.Discount as DetailDiscount,
    InvoicedDetail.Amount as DetailAmount,
    InvoicedDetail.SortOrder as DetailSortOrder
from InvoicedHeader
left outer join InvoicedGroup  on ( InvoicedHeader.IHPriKey = InvoicedGroup.IHPriKey )
left outer join InvoicedDetail on ( InvoicedGroup.IGPriKey = InvoicedDetail.IGPriKey )
left outer join RateTypes InvoicedGroupRateType on InvoicedGroupRateType.RTypePriKey = InvoicedGroup.RateTypeFID
left outer join RateTypes InvoicedDetailRateType on InvoicedDetailRateType.RTypePriKey = InvoicedDetail.RateTypeFID
left outer join @theRateMatrixBasisTable RateMatrixGroup on RateMatrixGroup.GroupBillingMinorItemID = InvoicedGroup.BillingMinorFID
left outer join @theRateMatrixBasisTable RateMatrixDetail on RateMatrixDetail.DetailBillingMinorItemID = InvoicedDetail.BillingMinorFID
where InvoicedHeader.IHPriKey = @inInvoicedHeaderId
order by
    InvoicedGroup.SortOrder,
    InvoicedDetail.SortOrder
GO
GRANT EXECUTE ON  [dbo].[GetInvoiceDetailsForLetter] TO [MssExec]
GO