/**
* Don't use this stored proc directly but instead use spCSMBuildStatement_Synonym.
* spCSMBuildStatement_Synonym will either point to this stored proc or to
* spCSMBuildStatement_XLedger.
*
* This procedure is called to insert the commission statement information from the accounting system into MoversSuite.
* Generates all commission statements for the vendors in the vendor class.
* This "Legacy" stored proc only works with GP, and returns an error code of 1 if the accounting system is not GP.
*    
*    @param @VendorClass        The vendor class that the information is built for.
*    @param @DueDate            The date that the commission is due.
*    @param @CreatedBy Primary key of a SysUser record. Identifies the person creating this commission statement.
*    @param @CSHPriKey Outputs the primary key of the CommStmtHeader record that this stored proc created for the commission statement.
*/

create procedure [dbo].[spCSMBuildStatement_Legacy]
    @VendorClass    varchar(15),
    @DueDate         datetime,
    @CreatedBy         int,
    @CSHPriKey        int output
as
set nocount on

declare @theLastStmtDate        datetime
declare @TRUE bit
set @TRUE = 1

-- Get the accounting system type (XL/GP/QB/NO)
declare @theAccountingSystemType varchar(2) = dbo.GetAccountingSystemType()

if( @theAccountingSystemType = 'GP' )
begin
    -- Create a temporary table.
    create table #GPTransactions
    (
        DocumentNumber varchar(21) not null default '',
        DocumentDate datetime not null default '1/1/1900',
        DocumentAmount money not null default 0,
        OrderNumber varchar(23) not null default '',
        ShipperName varchar(50) not null default '',
        InvoiceAmount money not null default 0,
        RevenueAmount money not null default 0,
        CommissionType varchar(8) not null default '',
        CommissionBasis varchar(15) not null default '',
        CommissionAmount money not null default 0,
        CommissionPerc float not null default 0,
        CommissionFactor money not null default 0,
        OrderWeight int not null default 0,
        AmountAfterSplits money not null default 0,
        ItemDescription varchar(128) not null default 'None',
        AllocatedItemDescription varchar(128) null,
        VoucherNumber varchar(21) not null default '',
        DocType int not null,
        AcctTransactionID int null,
        VendorId char(15) null,
        VendorName char(65) null,
        NetAmount money not null default 0,
        Miles int,
        AccountingVendorFid int null
    )
        
    -- Get the open transactions (from PM20000) that are not in CommStmtDetail.
    insert into #GPTransactions
    (
        VendorID,
        VendorName,
        VoucherNumber,
        DocType,
        DocumentNumber,
        DocumentDate,
        DocumentAmount,
        OrderNumber,
        ItemDescription,
        AcctTransactionID,
        AccountingVendorFid
    )
    select
        VendorID = rtrim( Vendor.VendorID ),
        VendorName = rtrim( Vendor.[Name] ),
        VoucherNumber = PM20000.VCHRNMBR,
        DocType = PM20000.DOCTYPE,
        DocumentNumber = PM20000.DOCNUMBR,
        DocumentDate = PM20000.DOCDATE,
        DocumentAmount =
            case
                when PM20000.DOCTYPE between 4 and 6 then -PM20000.DOCAMNT
                else PM20000.DOCAMNT
            end,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '' ),
        ItemDescription = PM20000.TRXDSCRN,
        AcctTransactionID = MSS10000.MSS_ATPriKey,
        AccountingVendorFid = Vendor.AccountingVendorId
    from PM20000_Synonym as PM20000
    inner join Vendor_Synonym as Vendor on Vendor.VendorID = PM20000.VENDORID
    left join MSS10000_Synonym as MSS10000 on
        PM20000.DOCTYPE = MSS10000.DOCTYPE and
        PM20000.VCHRNMBR = MSS10000.DOCNUMBR
    left join CommStmtDetail on
        CommStmtDetail.VoucherNumber = PM20000.VCHRNMBR and
        CommStmtDetail.DocType = PM20000.DOCTYPE
    where
        Vendor.VendorClass = @VendorClass and
        PM20000.VOIDED = 0 and
        PM20000.HOLD = 0 and
        PM20000.DOCDATE <= @DueDate and
        CommStmtDetail.VoucherNumber is null

        
    -- Get the history transactions (from PM30200) that are not in CommStmtDetail.
    insert into #GPTransactions
    (
        VendorID,
        VendorName,
        VoucherNumber,
        DocType,
        DocumentNumber,
        DocumentDate,
        DocumentAmount,
        OrderNumber,
        ItemDescription,
        AcctTransactionID,
        AccountingVendorFid
    )
    select
        VendorID = rtrim( Vendor.VendorID ),
        VendorName = rtrim( Vendor.[Name] ),
        VoucherNumber = PM30200.VCHRNMBR,
        DocType = PM30200.DOCTYPE,
        DocumentNumber = PM30200.DOCNUMBR,
        DocumentDate = PM30200.DOCDATE,
        DocumentAmount =
            case
                when PM30200.DOCTYPE between 4 and 6 then -PM30200.DOCAMNT
                else PM30200.DOCAMNT
            end,
        OrderNumber = isnull( MSS10000.MSS_OrderNo, '' ),
        ItemDescription = isnull( PM30200.TRXDSCRN, 'None' ),
        AcctTransactionID = MSS10000.MSS_ATPriKey,
        AccountingVendorFid = Vendor.AccountingVendorId
    from PM30200_Synonym as PM30200
    inner join Vendor_Synonym as Vendor on Vendor.VendorID = PM30200.VENDORID
    left join MSS10000_Synonym as MSS10000 on
        MSS10000.DOCTYPE = PM30200.DOCTYPE and
        MSS10000.DOCNUMBR = PM30200.VCHRNMBR
    left join CommStmtDetail on
        CommStmtDetail.VoucherNumber = PM30200.VCHRNMBR and
        CommStmtDetail.DocType = PM30200.DOCTYPE
    where
        Vendor.VendorClass = @VendorClass and
        PM30200.VOIDED = 0 and
        PM30200.HOLD = 0 and
        PM30200.DOCDATE <= @DueDate and
        CommStmtDetail.VoucherNumber is null
        
    -- Get the balance forward information.
    set @theLastStmtDate = ( select top 1 ToDate from CommStmtHeader where VendorClassId = @VendorClass and Final = @TRUE order by MSID Desc )

    -- Ensure the late statement date is defaulted if it is null.
    set @theLastStmtDate = isnull( @theLastStmtDate, '1/1/1900' )

    if( @theLastStmtDate is not null )
    begin
        insert into #GPTransactions
        (
            VendorID,
            VendorName,
            VoucherNumber,
            DocType,
            DocumentNumber,
            DocumentDate,
            DocumentAmount,
            OrderNumber,
            ItemDescription,
            AcctTransactionID,
            AccountingVendorFid
        )
        select
            VendorID = rtrim( Vendor.VendorID ),
            VendorName = rtrim( Vendor.[Name] ),
            VoucherNumber = '',
            DocType = '',
            DocumentNumber = '',
            DocumentDate = @theLastStmtDate,
            DocumentAmount = CommStmtVendor.Balance,
            OrderNumber = '',
            ItemDescription = 'Balance Forward',
            AcctTransactionID = null,
            AccountingVendorFid = Vendor.AccountingVendorId
        from Vendor_Synonym as Vendor
        inner join CommStmtVendor_Synonym as CommStmtVendor on Vendor.VendorJoinField = CommStmtVendor.VendorJoinField
        where
            Vendor.VendorClass = @VendorClass and
            CommStmtVendor.MSID =
            (
                select max( CommStmtVendor_max.MSID )
                from CommStmtVendor as CommStmtVendor_max
                where CommStmtVendor_max.VendorID = CommStmtVendor.VendorID
            ) and
            isnull( CommStmtVendor.Balance, 0 ) != 0
    end

    -- Create an index on the temp table for update statement.
    create index IX_AcctTransactions on #GPTransactions( AcctTransactionID )
    
    -- Update the data with the Mover's Suite information.
    update #GPTransactions
    set
        ShipperName = isnull( AcctTransactions.ShipperName, '' ),
        InvoiceAmount =    
            case
                when BillingMinorItem.Percentage is not null then isnull( BillingMajorItem.Amount, 0 )
                else isnull( BillingMinorItem.InvoiceAmount, 0 )
            end,
        RevenueAmount =
            case
                when BillingMinorItem.Amount is null then isnull( BillingMinorDist.Amount, 0 )
                else isnull( BillingMinorItem.Amount, 0 )
            end,
        NetAmount =
            case
                when BillingMinorItem.Percentage is not null then isnull( BillingMinorItem.InvoiceAmount, 0 )
                else isnull( BillingMinorItem.InvoiceAmount - BillingMinorItem.ReductionAmount, 0 )
            end,
        CommissionType =
            case
                when CommissionedDetail.CDPriKey is null
                then isnull( SplitCommissions.CommissionType, 0 )
                else isnull( CommissionedDetail.CommissionType, '' )
            end,
        CommissionBasis = isnull( CommissionBasis.Basis, '' ),
        CommissionAmount =
            case
                when CommissionedDetail.CDPriKey is null
                then isnull( SplitCommissions.Amount, 0 )
                else isnull( CommissionedDetail.Amount, 0 )
            end,
        CommissionPerc =
            case
                when CommissionedDetail.CDPriKey is null
                then isnull( SplitCommissions.CommissionPerc, 0 )
                else isnull( CommissionedDetail.CommissionPerc, 0 )
            end,
        CommissionFactor = isnull( CommissionedDetail.CommissionFactor, 0 ),
        OrderWeight = isnull( Orders.Weight, 0 ),
        AmountAfterSplits =
            case
                when CommissionedDetail.CDPriKey is null
                then isnull( SplitCommissions.Amount, 0 )
                else isnull( CommissionedDetail.CommissionAfterSplit, 0 )
            end,
        ItemDescription =
            case
                when AcctTransactions.ItemDescription is null
                then GP.ItemDescription
                else AcctTransactions.ItemDescription
            end,
        AllocatedItemDescription =
            case
                when BillingMinorItem.Percentage is not null then BillingMajorItem.[Description]
                else null
            end,
        Miles =
            case
                when CommissionedDetail.CDPriKey is null
                then CommissionedDetail_Split.Miles
                else CommissionedDetail.Miles
            end
    from #GPTransactions GP
    inner join AcctTransactions on AcctTransactions.ATPriKey = GP.AcctTransactionID
    left join Orders on Orders.PriKey = AcctTransactions.OrdPriKey
    left join SplitCommissions on
        AcctTransactions.SourceRecord = isnull( SplitCommissions.SCPriKey, 0 ) and
        AcctTransactions.Source = 'SplitCommissions'
    left join CommissionedDetail CommissionedDetail_Split on CommissionedDetail_Split.CDPriKey = SplitCommissions.CDPriKey
    left join CommissionedDetail on
        AcctTransactions.SourceRecord = isnull( CommissionedDetail.CDPriKey, 0 ) and
        AcctTransactions.Source = 'CommissionedDetail'
    left join BillingMinorItem on
        CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey or
        CommissionedDetail_Split.BMinPriKey = BillingMinorItem.BMinPriKey
    left outer join BillingMajorItem on BillingMajorItem.BMajPriKey = BillingMinorItem.BMajPriKey
    left outer join RevGroups on RevGroups.RGPriKey = BillingMajorItem.RevGroupPriKey
    left join BillingMinorDist on
        CommissionedDetail.BMinDistPrikey = BillingMinorDist.BMinDistPrikey or
        CommissionedDetail_Split.BMinDistPrikey = BillingMinorDist.BMinDistPrikey
    left join CommissionBasis on CommissionBasis.CBPriKey = CommissionedDetail.CBPriKey
        
    -- Update amounts for splits
    update #GPTransactions
    set
        InvoiceAmount =    convert( decimal(10,2), isnull( BillingMajorItem.Amount, 0 ) * ( isnull( RevenueRatingImportedSplitHaul.Percentage, 100 ) / 100 ) ),
        NetAmount =  convert( decimal(10,2), isnull( BillingMinorItem.InvoiceAmount, 0 ) * ( isnull( RevenueRatingImportedSplitHaul.Percentage, 100 ) / 100 ) )
    from #GPTransactions GP
    join AcctTransactions on AcctTransactions.ATPriKey = GP.AcctTransactionID
    left join CommissionedDetail on
        AcctTransactions.SourceRecord = isnull( CommissionedDetail.CDPriKey, 0 ) and
        AcctTransactions.Source = 'CommissionedDetail'
    left join BillingMinorItem on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
    left outer join BillingMajorItem on BillingMajorItem.BMajPriKey = BillingMinorItem.BMajPriKey
    left join CommissionBasis on CommissionBasis.CBPriKey = CommissionedDetail.CBPriKey
    inner join RevenueRatingImportedSplitHaul on BillingMinorItem.RevenueRatingImportedSplitHaulFID = RevenueRatingImportedSplitHaul.RevenueRatingImportedSplitHaulID
    where
        CommissionBasis.Basis in ( 'Invoice Amount', 'Net Amount' ) and
        CommissionedDetail.CommissionType = 'P'

    -- Get the history transactions that are voided and in CommStmtDetail.
    insert into #GPTransactions
    (
        DocumentNumber,
        DocumentDate,
        DocumentAmount,
        OrderNumber,
        ShipperName,
        InvoiceAmount,
        RevenueAmount,
        CommissionType,
        CommissionBasis,
        CommissionAmount,
        CommissionPerc,
        CommissionFactor,
        OrderWeight,
        AmountAfterSplits,
        ItemDescription,
        AllocatedItemDescription,
        VoucherNumber,
        DocType,
        AcctTransactionID,
        VendorId,
        VendorName,
        NetAmount,
        Miles,
        AccountingVendorFid
    )
    select
        DocumentNumber = max( CommStmtDetail.DocumentNumber ),
        DocumentDate = max( CommStmtDetail.DocumentDate ),
        DocumentAmount = -max( CommStmtDetail.DocumentAmount ),
        OrderNumber = max( CommStmtDetail.OrderNumber ),
        ShipperName = max( CommStmtDetail.ShipperName ),
        InvoiceAmount = max( CommStmtDetail.InvoiceAmount ),
        RevenueAmount = max( CommStmtDetail.RevenueAmount ),
        CommissionType = max( CommStmtDetail.CommissionType ),
        CommissionBasis = max( CommStmtDetail.CommissionBasis ),
        CommissionAmount = -max( CommStmtDetail.CommissionAmount ),
        CommissionPerc = max( CommStmtDetail.CommissionPerc ),
        CommissionFactor = max( CommStmtDetail.CommissionFactor ),
        OrderWeight = max( CommStmtDetail.OrderWeight ),
        AmountAfterSplits = -max( CommStmtDetail.AmountAfterSplits ),
        ItemDescription = cast( 'VOIDED: ' + max( CommStmtDetail.ItemDescription ) as varchar(128) ),
        AllocatedItemDescription = case when max( CommStmtDetail.AllocatedItemDescription ) is null then null else cast( 'VOIDED: ' + max( CommStmtDetail.AllocatedItemDescription ) as varchar(128) ) end,
        VoucherNumber = max( CommStmtDetail.VoucherNumber ),
        DocType = max( CommStmtDetail.DocType ),
        AcctTransactionID = null,
        VendorID = rtrim( max( CommStmtVendor.VendorID ) ),
        VendorName = rtrim( max( Vendor.[Name] ) ),
        NetAmount = max( CommStmtDetail.NetAmount ),
        Miles = max( CommStmtDetail.Miles ),
        AccountingVendorFid = max( Vendor.AccountingVendorId )
    from PM30200_Synonym as PM30200
    inner join Vendor_Synonym as Vendor on Vendor.VendorID = PM30200.VENDORID
    inner join CommStmtDetail on
        CommStmtDetail.VoucherNumber = PM30200.VCHRNMBR and
        CommStmtDetail.DocType = PM30200.DOCTYPE
    inner join CommStmtVendor on CommStmtDetail.CommStmtVendor = CommStmtVendor.MSID
    where
        Vendor.VendorClass = @VendorClass and
        PM30200.VOIDED = 1
    group by
        CommStmtDetail.VoucherNumber,
        CommStmtDetail.DocType
    having count(*) = 1

    -- If there are records in #GPTransactions, insert them into Mover's Suite.
    if exists ( select 1 from #GPTransactions where VendorID != '' )
    begin
            
        -- Get the vendor class name and insert a record into CommStmtHeader.
        declare @theVendorClassName varchar(31)
        declare @theAccountingVendorClassId int
        select
            @theVendorClassName = VendorClass.[Description],
            @theAccountingVendorClassId = VendorClass.AccountingVendorClassId
        from VendorClass_Synonym as VendorClass
        where VendorClass.Code = @VendorClass
            
        insert into CommStmtHeader ( VendorClassID, VendorClass, DateCreated, ToDate, Final, CreatedBy, AccountingVendorClassFid )
        select distinct
            VendorClassID     = @VendorClass,
            VendorClass     = @theVendorClassName,
            DateCreated        = convert( datetime, convert( varchar, getdate(), 101 ), 101 ),
            ToDate             = convert( datetime, convert( varchar, @DueDate, 101 ), 101 ),
            Final             = 0,
            CreatedBy        = @CreatedBy,
            AccountingVendorClassFid = @theAccountingVendorClassId
            
        set @CSHPriKey = scope_identity()
            
        -- Insert the vendor information.
        -- All records that are due before(or on) the DueDate, and after(or on) the FromDate are included.
        -- Also, any records that were created after the FromDate, and are Due previous to the FromDate.
        insert into CommStmtVendor ( CommStmtHeader, VendorID, VendorName, Balance, AccountingVendorFid )
        select distinct
            CommStmtHeader     = @CSHPriKey,
            VendorID         = VendorID,
            VendorName         = VendorName,
            Balance         = 0,
            AccountingVendorFid = AccountingVendorFid
        from #GPTransactions
            
        insert into CommStmtDetail
        (
            VoucherNumber,
            DocType,
            DocumentNumber,
            DocumentDate,
            DocumentAmount,
            OrderNumber,
            ShipperName,
            InvoiceAmount,
            RevenueAmount,
            CommissionType,
            CommissionBasis,
            CommissionAmount,
            CommissionPerc,
            CommissionFactor,
            OrderWeight,
            AmountAfterSplits,
            ItemDescription,
            AllocatedItemDescription,
            CommStmtVendor,
            NetAmount,
            Miles
        )
        select
            VoucherNumber     = GPTransaction.VoucherNumber,
            DocType           = GPTransaction.DocType,
            DocumentNumber    = GPTransaction.DocumentNumber,
            DocumentDate      = GPTransaction.DocumentDate,
            DocumentAmount    = GPTransaction.DocumentAmount,
            OrderNumber       = GPTransaction.orderNumber,
            ShipperName       = GPTransaction.ShipperName,
            InvoiceAmount     = GPTransaction.InvoiceAmount,
            RevenueAmount     = GPTransaction.RevenueAmount,
            CommissionType    = GPTransaction.CommissionType,
            CommissionBasis   = GPTransaction.CommissionBasis,
            CommissionAmount  = GPTransaction.CommissionAmount,
            CommissionPerc    = GPTransaction.CommissionPerc,
            CommissionFactor      = GPTransaction.CommissionFactor,
            OrderWeight       = GPTransaction.orderWeight,
            AmountAfterSplits = GPTransaction.AmountAfterSplits,
            ItemDescription   = GPTransaction.ItemDescription,
            AllocatedItemDescriptoin = GPTransaction.AllocatedItemDescription,
            CommStmtVendor      = CSV.MSID,
            NetAmount          = GPTransaction.NetAmount,
            Miles              = GPTransaction.Miles
        from #GPTransactions GPTransaction
        join CommStmtVendor CSV on ( CSV.VendorID = GPTransaction.VendorID )
        where CSV.CommStmtHeader = @CSHPriKey
            
        update CommStmtVendor
        set Balance =
            isnull( ( select sum( csd.DocumentAmount )
                        from CommStmtDetail csd
                        where csd.CommStmtVendor = CommStmtVendor.MSID ), 0 )
        where CommStmtHeader = @CSHPriKey
    end
    -- cleanup our temp table
    drop table #GPTransactions
end

-- Otherwise return error
else
begin
    return 1
end
GO
GRANT EXECUTE ON  [dbo].[spCSMBuildStatement_Legacy] TO [MssExec]
GO