USP_REPORT_TAXES

create procedure dbo.USP_REPORT_TAXES
(
    @SALESMETHODID uniqueidentifier = null,
    @TAXID uniqueidentifier = null,
    @TAXSELECTIONID uniqueidentifier = null,
    @FROMDATE date = null,
    @TODATE date = null
)
as 
    set nocount on;

    declare @SALESMETHODTYPECODE tinyint = (select TYPECODE from dbo.SALESMETHOD where ID = @SALESMETHODID);

    --Optimizing in case using selection
    declare @TAXIDS table (
        ID uniqueidentifier
    )

    if @TAXID is not null
        insert @TAXIDS
        values (@TAXID)
    else if @TAXSELECTIONID is not null
        insert @TAXIDS
        select [ID] 
        from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@TAXSELECTIONID)
    else
        insert @TAXIDS
        select [ID] 
        from dbo.TAX;

    with SALES_CTE as (
        select 
            SALESORDERITEMTAX.TAXID,
            sum(SOI.PRICE) TOTAL
        from
            dbo.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE) as FILTEREDSALESORDERS
        inner join
            dbo.SALESORDER SO on SO.ID = FILTEREDSALESORDERS.ID
        inner join
            dbo.SALESORDERITEM SOI on SO.ID = SOI.SALESORDERID
        inner join (
            select distinct 
                TAXITEMID, 
                TAXID 
            from dbo.SALESORDERITEMTAX
            inner join @TAXIDS [TAXIDS] on
            SALESORDERITEMTAX.TAXID = [TAXIDS].ID
        ) SALESORDERITEMTAX on SOI.ID = SALESORDERITEMTAX.TAXITEMID
        where
            (@SALESMETHODTYPECODE is null or SO.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
            and SO.STATUSCODE in (1,3,4)
        group by
            SALESORDERITEMTAX.TAXID
    ),
    REFUNDS_CTE as (
        select
            SALESORDERITEMTAX.TAXID,
            sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as TOTAL
        from
            dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join
            dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
        inner join (
            select distinct 
                TAXITEMID, 
                TAXID 
            from dbo.SALESORDERITEMTAX
            inner join @TAXIDS [TAXIDS] on
            SALESORDERITEMTAX.TAXID = [TAXIDS].ID
        ) SALESORDERITEMTAX on EXT.SALESORDERITEMID = SALESORDERITEMTAX.TAXITEMID
        inner join
            dbo.SALESORDERITEM on SALESORDERITEMTAX.TAXITEMID = SALESORDERITEM.ID
        inner join
            dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        inner join
            dbo.FINANCIALTRANSACTION on LI.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        where
            (@SALESMETHODTYPECODE is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE) and
            (@FROMDATE is null or FINANCIALTRANSACTION.CALCULATEDDATE >= @FROMDATE) and
            (@TODATE is null or FINANCIALTRANSACTION.CALCULATEDDATE <= @TODATE)
        group by
            SALESORDERITEMTAX.TAXID
    )
    select 
        TAX.NAME as TAXNAME,
        isnull(SALES.TOTAL,0) - isnull(REFUNDS.TOTAL,0) TAXTOTAL,
        TAX.TOTALTAX as TAXAMOUNT
    from dbo.TAX
    left join SALES_CTE SALES
        on TAX.ID = SALES.TAXID
    left join REFUNDS_CTE REFUNDS
        on TAX.ID = REFUNDS.TAXID
    where isnull(SALES.TOTAL, REFUNDS.TOTAL) is not null
    order by TAX.NAME
    option (recompile);

    return 0;