/**
*
*    This procedure is called to populate the detail grid of the Revenue Import screen.
*
*    @param @inOrderID The order to get the detail information from.
*    @param @inImported The table to use for the detail data. 0 = Not Imported  1 = Imported
*    @param @inRevenueImportType The name of the revenue type to use. Can be any [Name] value
*            from the RevenueImportType table.
*/

CREATE PROCEDURE [dbo].[RevenueRatingImportInvoiceDetails]
    @inOrdersID int,
    @inImported int,
    @inRevenueImportType varchar(50),
    @inRevenueRatingImportHeaderID int
as
set nocount on

declare @theTempID int
declare @theContinueUpdatingDescriptions bit = 1
declare @theDistributionCode varchar(10)
declare @theVanlineGroupExternalIdentifier varchar(10);
declare @theErrorCode int;
declare @theICPriKey int;
declare @theItemCode int;
declare @theDescription varchar(128);
declare @theRGPriKey int;
declare @theInvoice varchar( 10 );
declare @theAgentPercentage decimal( 12, 4 );


declare @theDetailsTable table
(
    TempID int identity( 1, 1 ) not null,
    AgentFID int,
    RevenueRatingImportDetailID int,
    [Description] varchar(128),
    DistributionCode varchar(10),
    Revenue decimal(12,4),
    IsMerged bit,
    AgentID varchar(10),
    DescriptionUpdated bit,
    ImportDateTime datetime
)

-- When @theIsRating is 1 then we apply reductions to the invoice amount.
-- When @theIsRating is 0 then we use the distribution amount as is.
--declare @theIsRating bit = ( select IsRating from RevenueImportType where [Name] = @inRevenueImportType )

-- RevenueRatingImport(ed)InvoiceDetails on exist for Atlas Carrier imports
if( @inRevenueImportType in ( 'Atlas Carrier' ) )
begin
    if( 0 = @inImported )
    begin
        insert into @theDetailsTable
        (
            AgentFID,
            RevenueRatingImportDetailID,
            [Description],
            DistributionCode,
            Revenue,
            IsMerged,
            AgentID,
            DescriptionUpdated
        )
        -- Get the information from the Import Invoice tables
        select
            AgentFID = RevenueRatingImportInvoiceDetail.AgentFID,
            RevenueRatingImportDetailID = RevenueRatingImportInvoiceDetail.RevenueRatingImportInvoiceDetailID,
            [Description] = RevenueRatingImportInvoiceDetail.[Description],
            DistributionCode = isnull( RevenueRatingImportInvoiceMerge.MergeToCode, RevenueRatingImportInvoiceDetail.DistributionCode ),
            Revenue = RevenueRatingImportInvoiceDetail.GrossAmount - round( ( RevenueRatingImportInvoiceDetail.GrossAmount * RevenueRatingImportInvoiceDetail.DiscountPct ) / 100.0, 2 ),
            IsMerged = case
                when RevenueRatingImportInvoiceMerge.RevenueRatingImportInvoiceMergeID is not null then 1
                else 0
            end,
            AgentID = Agent.AgentID,
            DescriptionUpdated = case
                when RevenueRatingImportInvoiceMerge.RevenueRatingImportInvoiceMergeID is not null then 0
                else 1
            end
        from RevenueRatingImportInvoiceDetail
        left outer join Agent on Agent.AgentPriKey = RevenueRatingImportInvoiceDetail.AgentFID
        left outer join RevenueRatingImportInvoiceMerge on RevenueRatingImportInvoiceMerge.DistributionCode = RevenueRatingImportInvoiceDetail.DistributionCode
        left outer join RevenueRatingImportInvoiceOmit on RevenueRatingImportInvoiceOmit.DistributionCode = RevenueRatingImportInvoiceDetail.DistributionCode
        where RevenueRatingImportInvoiceDetail.RevenueRatingImportHeaderFID = @inRevenueRatingImportHeaderID and
            RevenueRatingImportInvoiceOmit.RevenueRatingImportInvoiceOmitID is null
    end
    else if( 1 = @inImported )
    begin
        insert into @theDetailsTable
        (
            AgentFID,
            RevenueRatingImportDetailID,
            [Description],
            DistributionCode,
            Revenue,
            IsMerged,
            AgentID,
            DescriptionUpdated,
            ImportDateTime
        )
        -- Get the information from the Import Invoice tables
        select
            AgentFID = RevenueRatingImportedInvoiceDetail.AgentFID,
            RevenueRatingImportDetailID = RevenueRatingImportedInvoiceDetail.RevenueRatingImportedInvoiceDetailID,
            [Description] = RevenueRatingImportedInvoiceDetail.[Description],
            DistributionCode = isnull( RevenueRatingImportInvoiceMerge.MergeToCode, RevenueRatingImportedInvoiceDetail.DistributionCode ),
            Revenue = RevenueRatingImportedInvoiceDetail.GrossAmount - round( ( RevenueRatingImportedInvoiceDetail.GrossAmount * RevenueRatingImportedInvoiceDetail.DiscountPct ) / 100.0, 2 ),
            IsMerged = case
                when RevenueRatingImportInvoiceMerge.RevenueRatingImportInvoiceMergeID is not null then 1
                else 0
            end,
            AgentID = Agent.AgentID,
            DescriptionUpdated = case
                when RevenueRatingImportInvoiceMerge.RevenueRatingImportInvoiceMergeID is not null then 0
                else 1
            end,
            ImportDateTime = RevenueRatingImportedInvoiceDetail.ImportDateTime
        from RevenueRatingImportedInvoiceDetail
        left outer join Agent on Agent.AgentPriKey = RevenueRatingImportedInvoiceDetail.AgentFID
        left outer join RevenueRatingImportInvoiceMerge on RevenueRatingImportInvoiceMerge.DistributionCode = RevenueRatingImportedInvoiceDetail.DistributionCode
        left outer join RevenueRatingImportInvoiceOmit on RevenueRatingImportInvoiceOmit.DistributionCode = RevenueRatingImportedInvoiceDetail.DistributionCode
        where RevenueRatingImportedInvoiceDetail.RevenueRatingImportedHeaderFID = @inRevenueRatingImportHeaderID and
            RevenueRatingImportInvoiceOmit.RevenueRatingImportInvoiceOmitID is null
    end

    set @theVanlineGroupExternalIdentifier = dbo.GetVanlineGroupExternalCodeFromRatingType( @inRevenueRatingImportHeaderID, @inImported );

    while( @theContinueUpdatingDescriptions = 1 )
    begin
        set @theTempID = null

        -- Update the descriptions of the merged distribution codes.
        -- spRevenueImportGetItemCode was called for all items when
        -- XmlImportProcessRatingInvoiceDetails processed the original
        -- download.
        select top 1
            @theTempID = TempID,
            @theDistributionCode = DistributionCode
        from @theDetailsTable
        where DescriptionUpdated = 0
        order by TempID

        if( @theTempID is null )
        begin
            -- All done.
            set @theContinueUpdatingDescriptions = 0
        end
        else
        begin
            -- If an ItemCode description exists for this revenue item then use that instead of the provided description
            exec spRevenueImportGetItemCode
                @theDistributionCode,
                null, -- The CartonCode
                null, -- The AgentPercentage is not used so ignore this parameter.
                null, -- The ShipmentType is not used so ignore this parameter
                @theErrorCode output,
                @theICPriKey output,
                @theItemCode output,
                @theDescription output,
                @theRGPriKey output,
                @theInvoice output,
                @theAgentPercentage output,
                @theVanlineGroupExternalIdentifier; -- @inVanlineGroupExternalCode

            -- update the description
            update @theDetailsTable set
                [Description] = isnull( @theDescription, [Description] ),
                DescriptionUpdated = 1
            where TempID = @theTempID
        end
    end

    -- Update the description for any merged items.  So, why are we doing this
    -- if we did the above loop?  Because, there is no guarantee that the "Merged
    -- To" code is actually one of the details (it should be).
    update theSourceTable set
        [Description] = theDescTable.[Description]
    from @theDetailsTable as theSourceTable
    inner join @theDetailsTable as theDescTable on theDescTable.[DistributionCode] = theSourceTable.[DistributionCode] and
        theDescTable.IsMerged = 0
    where theSourceTable.IsMerged = 1
end

-- Return the data in temp table (if any).
select
    RevenueRatingImportDetailID = theDetailsTable.RevenueRatingImportDetailID,
    RevenueRatingImportHeaderFID = @inRevenueRatingImportHeaderID,
    [Description] = theDetailsTable.[Description],
    AgentID = theDetailsTable.AgentID,
    DistributionCode = theDetailsTable.DistributionCode,
    Revenue = theDetailsTable.Revenue,
    AgentPercentage = convert( decimal(12,4), 100.0 ),
    CartonCode = convert( varchar(50), null ),
    ImportDateTime = theDetailsTable.ImportDateTime,
    Import = 0,
    AgentFID = theDetailsTable.AgentFID,
    IsImported = @inImported
from @theDetailsTable as theDetailsTable
order by 3
GO
GRANT EXECUTE ON  [dbo].[RevenueRatingImportInvoiceDetails] TO [MssExec]
GO