/**
*    
*    This procedure returns open AR transactions and includes the invoice information and their associated invoice information from Great Plains
*    that have a non-zero balance.
*    
*    @param @inBranch The branch or a comma separated list of branches.
*    @param @inCompanyWideBranchFlag When true, specifies that the selected branches should be considered as one branch for grouping purposes.
*        When false, Branches are grouped normally.
*    @param @inAuthority    The authority or a comma separated list of authorities.
*    @param @inCompanyWideAuthorityFlag When true, specifies that the selected branches should be considered as one branch for grouping purposes.
*        When false, Branches are grouped normally.
*    @param @inCustomerNumber The customer number or a comma separated list of customer numbers.
*    @param @inGroupBy Customer, or Order.  The group must be known so that the appropriate summing of the invoice amount can be done.
*
*    SPECIAL PROGRAMMER'S NOTE:
*    ==========================
*    Do not call this SP directly.  Instead, call ReportStandardARCollections_Synonym and then correct SP
*    for the current accounting system will be called.  This version is for GP Dynamics accounting systems.
*    The ReportStandardARCollections_XLedger SP handles this data when the accounting system is XLedger.
*/

create procedure [dbo].[ReportStandardARCollections_Legacy]
    @inBranch nvarchar(max),
    @inCompanyWideBranchFlag bit,
    @inAuthority nvarchar(max),
    @inCompanyWideAuthorityFlag bit,
    @inCustomerNumber nvarchar(max),
    @inGroupBy varchar(30)
as

declare @theInvoiceStatusID int
declare @theCollectionsNoteTypeID int
declare @theSQLQuery nvarchar(max)

-- Obtain some primary key values for some specific foreign table references for later usage.
set @theInvoiceStatusID = ( select InvoiceStatusID from InvoiceStatus where Status = 'Invoice' )
set @theCollectionsNoteTypeID = ( select NoteTypeID from NoteType where NoteType = 'Collections' )

-- This temp table and its index are solely here to speed up this sp.  Often this report is run with many customers
-- so having an index to join on will avoid a table scan.  We saw this query speed up by a factor of 2000% to 7000%.
create table #theCustomerData
(
    CustomerNumber char(15)
)
-- And having an index on this data will help as well.
create unique clustered index #IX_theCustomerData on #theCustomerData
(
    CustomerNumber
) with fillfactor = 100

insert into #theCustomerData
(
    CustomerNumber
)
select Item from dbo.ParseMultiValuedParameter( @inCustomerNumber, ',' )

-- Temp table to handle the branch parameter.  Some of our users have lots of branches.  So doing this will speed things
-- up since we only need to convert the branch data one time.  There was also two places were we access this table.
create table #theBranchData
(
    BranchCode char(11)
)
create unique clustered index #IX_theBranchData on #theBranchData
(
    BranchCode
) with fillfactor = 100
insert into #theBranchData ( BranchCode )
select isnull( Item, '' ) from dbo.ParseMultiValuedParameter( @inBranch, ',' )

-- Create a temporary table to hold the transaction data
create table #theData
(
    DocumentNumber varchar( 21 ),
    DocumentDescription varchar(31),
    Authority varchar( 31 ),
    OrderID int,
    OrderNumber varchar( 24 ),
    Salesperson varchar( 43 ),
    Shipper varchar( 50 ),
    BranchID varchar( 11 ),
    CustomerNumber varchar( 15 ),
    CustomerName varchar( 65 ),
    CustomerClass varchar( 15 ),
    CustomerContact varchar( 61 ),
    CustomerAddress1 varchar( 128 ),
    CustomerAddress2 varchar( 128 ),
    CustomerAddress3 varchar( 128 ),
    CustomerCity varchar( 35 ),
    CustomerState varchar( 29 ),
    CustomerPostalCode varchar( 11 ),
    CustomerPhone1 varchar( 21 ),
    CustomerPhone2 varchar( 21 ),
    CustomerPhone3 varchar( 21 ),
    CurrentTransactionAmount money,
    InvoiceNumber varchar( 64 ),
    InvoiceDate datetime,
    InvoiceTotal money,
    DocumentDate datetime,
    DueDate datetime,
    PostDate datetime,
    JournalDate datetime,
    DocumentType int,
    OrderInvoiceTotal money,
    CustomerInvoiceTotal money,
    CollectionsSubject varchar( 256 ),
    CollectionsNote text,
    MultiBranchInvoiceFlag bit default(0)
)

-- Declare a table variable for the Authority parameter
set @theSQLQuery = N'declare @theAuthority table( Authority char(31) ) ' +
'insert into @theAuthority ( Authority ) ' +
'select isnull( Item, '''' ) from dbo.ParseMultiValuedParameter( @inAuthority, '','' ) '

-- Build the insert portion of the script
set @theSQLQuery = @theSQLQuery +
N'insert into #theData ' +
'( ' +
    'DocumentNumber, ' +
    'DocumentDescription, ' +
    'Authority, ' +
    'OrderID, ' +
    'OrderNumber, ' +
    'Salesperson, ' +
    'Shipper, ' +
    'BranchID, ' +
    'CustomerNumber, ' +
    'CustomerName, ' +
    'CustomerClass, ' +
    'CustomerContact, ' +
    'CustomerAddress1, ' +
    'CustomerAddress2, ' +
    'CustomerAddress3, ' +
    'CustomerCity, ' +
    'CustomerState, ' +
    'CustomerPostalCode, ' +
    'CustomerPhone1, ' +
    'CustomerPhone2, ' +
    'CustomerPhone3, ' +
    'CurrentTransactionAmount, ' +
    'InvoiceNumber, ' +
    'InvoiceDate, ' +
    'InvoiceTotal, ' +
    'DocumentDate, ' +
    'DueDate, ' +
    'PostDate, ' +
    'JournalDate, ' +
    'DocumentType ' +
') ' +
'select distinct ' +
    'DocumentNumber = rtrim( RM20101.DOCNUMBR ), ' +
    'DocumentDescription = rtrim( RM20101.TRXDSCRN ), ' +
    'Authority = rtrim( MSS10000.MSS_Authority ), ' +
    'OrderID = MSS10000.MSS_OrdPriKey, ' +
    'OrderNumber = case Orders.Archived ' +
        'when 1 then rtrim( MSS10000.MSS_OrderNo) + '' [A]'' ' +
        'else rtrim( MSS10000.MSS_OrderNo ) ' +
    'end, ' +
    'Salesperson = rtrim( MSS10000.MSS_SalesPerson ), ' +
    'Shipper = rtrim( MSS10000.MSS_ShipperName ), ' +
    'BranchID = rtrim( MSS10000.MSS_BranchCode ), ' +
    'CustomerNumber = rtrim( RM00101.CUSTNMBR ), ' +
    'CustomerName = rtrim( RM00101.CUSTNAME ), ' +
    'CustomerClass = rtrim( RM00101.CUSTCLAS ), ' +
    'CustomerContact = rtrim( RM00101.CNTCPRSN ), ' +
    'CustomerAddress1 = rtrim( RM00101.ADDRESS1 ), ' +
    'CustomerAddress2 = rtrim( RM00101.ADDRESS2 ), ' +
    'CustomerAddress3 = rtrim( RM00101.ADDRESS3 ), ' +
    'CustomerCity = rtrim( RM00101.CITY ), ' +
    'CustomerState = rtrim( RM00101.STATE ), ' +
    'CustomerPostalCode = rtrim( RM00101.ZIP ), ' +
    'CustomerPhone1 = dbo.FormatGreatPlainsPhoneNumber( RM00101.PHONE1 ), ' +
    'CustomerPhone2 = dbo.FormatGreatPlainsPhoneNumber( RM00101.PHONE2 ), ' +
    'CustomerPhone3 = dbo.FormatGreatPlainsPhoneNumber( RM00101.PHONE3 ), ' +
    'CurrentTransactionAmount = RM20101.CURTRXAM, ' +
    'InvoiceNumber = rtrim( MSS10000.MSS_InvoiceNumber ), ' +
    'InvoiceDate = MSS10000.MSS_InvoiceDate, ' +
    'InvoiceTotal = InvoicedHeader.InvoiceTotal, ' +
    'DocumentDate = RM20101.DOCDATE, ' +
    'DueDate = RM20101.DUEDATE, ' +
    'PostDate = RM20101.POSTDATE, ' +
    'JournalDate = RM20101.GLPOSTDT, ' +
    'DocumentType = RM20101.RMDTYPAL '

-- Begin building the from and join clauses
set @theSQLQuery = @theSQLQuery +
N'from RM20101_Synonym as RM20101 ' +
'inner join RM00101_Synonym as RM00101 on RM00101.CUSTNMBR = RM20101.CUSTNMBR '

/*
*    If a specific branch is not specified, build the join clause to get all open transactions
*    regardless of whether it is association to an order.  Otherwise, build the join clause to get
*    open transactions that have an associated order.  Note that ParseMultiValuedParameter converts
*    '<none>' to NULL and we converted NULL to '' when we inserted into #theBranchData so the or
*    condition is basically checking to see if '<none>' is in the @inBranch parameter.
*/
if( 1 = @inCompanyWideBranchFlag or exists ( select BranchCode from #theBranchData where BranchCode = '' ) )
begin
    set @theSQLQuery = @theSQLQuery + N'left outer'
end
else
begin
    set @theSQLQuery = @theSQLQuery + N'inner'
end

-- Add in the remainder of the joins
set @theSQLQuery = @theSQLQuery +
    N' join MSS10000_Synonym as MSS10000 on MSS10000.SERIES = 3 and ' +
    'MSS10000.DOCTYPE = RM20101.RMDTYPAL and ' +
    'MSS10000.DOCNUMBR = RM20101.DOCNUMBR ' +
'left outer join InvoicedHeader on ' +
    'rtrim( MSS10000.MSS_InvoiceNumber ) = InvoicedHeader.InvoiceNumber and ' +
    'InvoicedHeader.InvoiceStatusFID =  @inInvoiceStatusID '

-- Add in the Customer Number join
set @theSQLQuery = @theSQLQuery +
    N'inner join #theCustomerData as theCustomerData on theCustomerData.CustomerNumber = RM20101.CUSTNMBR '

-- Only pull transactions for the specified branches (this can include the case of a null branch on a transaction).
set @theSQLQuery = @theSQLQuery +
    N'inner join #theBranchData as theBranchData on MSS10000.MSS_BranchCode = theBranchData.BranchCode '

-- Only pull transactions for the specified authorities (this can include the case of a null authority on a transaction).
set @theSQLQuery = @theSQLQuery +
    N'inner join @theAuthority as theAuthorityData on MSS10000.MSS_Authority = theAuthorityData.Authority '

-- Join in on orders so we can check on it's archival state and choose what it returns as the order number.
set @theSQLQuery = @theSQLQuery +
    N'left outer join Orders on MSS10000.MSS_OrdPriKey = Orders.PriKey '

-- And now the where clause
set @theSQLQuery = @theSQLQuery +
    N'where RM20101.CURTRXAM != 0 '

-- Set the ordering for the result set
set @theSQLQuery = @theSQLQuery +
    N'order by CustomerNumber, OrderNumber, DocumentNumber'

exec sp_executesql @theSQLQuery,
    N'@inInvoiceStatusID int, @inAuthority varchar(max)', @theInvoiceStatusID, @inAuthority

-- We do not need these temp tables any more.
drop table #theCustomerData
drop table #theBranchData

-- Temporary table used to hold the unique combination of Customer, Order, and Invoice and hold the Invoice Total
create table #theInvoiceTotals
(
    Customer varchar(15),
    OrderID int,
    Invoice varchar(64),
    Amount money
)

-- Get the Invoice totals
insert into #theInvoiceTotals
(
    Customer,
    OrderID,
    Invoice,
    Amount
)
select distinct
    CustomerNumber,
    OrderID,
    InvoiceNumber,
    InvoiceTotal
from #theData
where InvoiceTotal is not null

-- Update the various Invoice totals based on the value of the GroupBy parameter
if( @inGroupBy = 'Customer' )
begin
    -- Update the Invoice Total for each Customer
    update #theData
    set CustomerInvoiceTotal =
    isnull( (
        select sum( Amount )
        from #theInvoiceTotals
        where #theData.CustomerNumber = #theInvoiceTotals.Customer
        group by Customer
    ), 0 )
    
    -- Update the Invoice Total for each Order of each Customer
    update #theData
    set OrderInvoiceTotal =
    isnull( (
        select sum( Amount )
        from #theInvoiceTotals
        where
            #theInvoiceTotals.Customer = #theData.CustomerNumber and
            #theInvoiceTotals.OrderID = #theData.OrderID
        group by
            #theInvoiceTotals.Customer,
            #theInvoiceTotals.OrderID
    ), 0 )
end
else
begin
    -- Update the Invoice Total for each Customer of each Order
    update #theData
    set CustomerInvoiceTotal =
    isnull( (
        select sum( Amount )
        from #theInvoiceTotals
        where
            #theInvoiceTotals.OrderID = #theData.OrderID and
             #theInvoiceTotals.Customer = #theData.CustomerNumber
        group by
            OrderID,
            Customer
    ), 0 )
    
    -- Update the Invoice Total for each Order
    update #theData
    set OrderInvoiceTotal =
    isnull( (
        select sum( Amount )
        from #theInvoiceTotals
        where #theInvoiceTotals.OrderID = #theData.OrderID
        group by OrderID
    ), 0 )
    from #theData
end

-- Clean up the temp table
drop table #theInvoiceTotals

-- Insert the most recent Collections note's subject and memo
update #theData
set CollectionsSubject =
    (
        select top 1 Subject
        from OrderNoteHeader
        where
            OrderFID = theData.OrderID and
            NoteTypeFID = @theCollectionsNoteTypeID
        order by OrderNoteHeaderID desc
    ),
    CollectionsNote =
    (
        select top 1 OrderNoteDetail.NoteBody
        from OrderNoteHeader
        inner join OrderNoteDetail on OrderNoteDetail.OrderNoteHeaderFID = OrderNoteHeader.OrderNoteHeaderID
        where
            OrderNoteHeader.OrderFID = theData.OrderID and
            OrderNoteHeader.NoteTypeFID = @theCollectionsNoteTypeID
        order by
            OrderNoteHeaderID desc,
            OrderNoteDetailID desc
    )
from #theData as theData
    
-- Set a flag that indicates the transactions that make up an invoice span multiple branches
update #theData
set MultiBranchInvoiceFlag =
    isnull( (
        select 1
        from #theData as theInnerData
        where theInnerData.InvoiceNumber = theOuterData.InvoiceNumber
        group by theInnerData.InvoiceNumber
        having count( distinct theInnerData.BranchID ) > 1
    ), 0 )
from #theData as theOuterData
where rtrim( theOuterData.InvoiceNumber ) != ''

-- Return the data from the temp table
select * from #theData

-- Clean up our temp table
drop table #theData
GO
GRANT EXECUTE ON  [dbo].[ReportStandardARCollections_Legacy] TO [MssExec]
GO