UFN_SALESORDER_GETSALESDOCUMENTWORKSTATIONPRINTERS

create function dbo.UFN_SALESORDER_GETSALESDOCUMENTWORKSTATIONPRINTERS
(
    @WORKSTATIONID uniqueidentifier,
    @TEMPLATETYPE tinyint,
    @ORDERID uniqueidentifier,    
    @PROGRAMID uniqueidentifier,
    @IGNOREDELIVERYMETHOD bit = 0
)
returns @PRINTERS table
(
    REPORTCATALOGID uniqueidentifier,
    PRINTERNAME nvarchar(max)
)
as
begin
    declare @SALESMETHODTYPECODE integer

    select @SALESMETHODTYPECODE=SALESMETHODTYPECODE from dbo.SALESORDER where ID = @ORDERID;

    declare @AVAILABLEDOCUMENTS as TABLE (NUMBERID int identity, DOCUMENTID uniqueidentifier, PRINTERLISTID uniqueidentifier)

    if @TEMPLATETYPE = 0 and @PROGRAMID is not null
    begin
        if @SALESMETHODTYPECODE in (0,1,2)     --advance/daily/online sales

        begin
            insert into @AVAILABLEDOCUMENTS
            (
                DOCUMENTID,
                PRINTERLISTID
            )
            select
                DOCUMENT.ID,
                DOCUMENT.PRINTERLISTID                
            from dbo.DOCUMENT
            inner join dbo.PROGRAMDOCUMENT on
                DOCUMENT.ID = PROGRAMDOCUMENT.DOCUMENTID                        
            inner join dbo.DOCUMENTPRINTINGRULE on
                DOCUMENT.ID = DOCUMENTPRINTINGRULE.DOCUMENTID
            inner join dbo.DOCUMENTPRINTINGRULESALESMETHOD on
                DOCUMENTPRINTINGRULE.ID = DOCUMENTPRINTINGRULESALESMETHOD.DOCUMENTPRINTINGRULEID
            inner join dbo.SALESORDER on
                DOCUMENTPRINTINGRULE.DELIVERYMETHODID = SALESORDER.DELIVERYMETHODID or
                @IGNOREDELIVERYMETHOD = 1
            inner join dbo.SALESMETHOD on
                SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE and
                DOCUMENTPRINTINGRULESALESMETHOD.SALESMETHODID = SALESMETHOD.ID    
            where                     
                SALESORDER.ID = @ORDERID and
                PROGRAMDOCUMENT.PROGRAMID = @PROGRAMID and 
                DOCUMENT.TYPECODE = @TEMPLATETYPE and
                DOCUMENT.ISACTIVE = 1 and
                DOCUMENT.PRINTERLISTID is not null
            order by
                PROGRAMDOCUMENT.SEQUENCE                
        end
    end                        
    else
        if @TEMPLATETYPE = 5 or @TEMPLATETYPE = 6
        --refund templates

        begin
            insert into @AVAILABLEDOCUMENTS
            (
                DOCUMENTID,
                PRINTERLISTID
            )
            select
                DOCUMENT.ID,
                DOCUMENT.PRINTERLISTID
            from
                dbo.DOCUMENT
            where                                        
                DOCUMENT.TYPECODE = @TEMPLATETYPE and
                DOCUMENT.ISACTIVE = 1 and
                DOCUMENT.PRINTERLISTID is not null and
                (
                    (
                        @SALESMETHODTYPECODE = 0 or   --daily sales

                        @SALESMETHODTYPECODE = 1 or    --advance sales

                        @SALESMETHODTYPECODE = 2 or        --online sales

                        (
                            @SALESMETHODTYPECODE = 3 and   --group sales

                            DOCUMENT.PRINTFORGROUPSALES = 1
                        )
                        or @SALESMETHODTYPECODE is null
                    ) or
                    @IGNOREDELIVERYMETHOD = 1
                )
            order by
                DOCUMENT.SEQUENCE                                        
        end
        else
        begin
            if @SALESMETHODTYPECODE in (0,1,2)     --advance/daily/online sales

            begin
                insert into @AVAILABLEDOCUMENTS
                    (DOCUMENTID,
                    PRINTERLISTID)
                select
                    DOCUMENT.ID,
                    DOCUMENT.PRINTERLISTID                                        
                from
                    dbo.DOCUMENT inner join dbo.DOCUMENTPRINTINGRULE on
                        DOCUMENT.ID = DOCUMENTPRINTINGRULE.DOCUMENTID
                    inner join dbo.DOCUMENTPRINTINGRULESALESMETHOD on
                        DOCUMENTPRINTINGRULE.ID = DOCUMENTPRINTINGRULESALESMETHOD.DOCUMENTPRINTINGRULEID
                    inner join dbo.SALESORDER on
                        DOCUMENTPRINTINGRULE.DELIVERYMETHODID = SALESORDER.DELIVERYMETHODID or
                        @IGNOREDELIVERYMETHOD = 1
                    inner join dbo.SALESMETHOD on
                        SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE and
                        DOCUMENTPRINTINGRULESALESMETHOD.SALESMETHODID = SALESMETHOD.ID    
                where                     
                    SALESORDER.ID = @ORDERID and
                    DOCUMENT.TYPECODE = @TEMPLATETYPE and
                    DOCUMENT.ISACTIVE = 1 and
                    DOCUMENT.PRINTERLISTID is not null
                order by
                    DOCUMENT.SEQUENCE
            end
            else
                if @SALESMETHODTYPECODE = 3
                begin
                    insert into @AVAILABLEDOCUMENTS
                        (DOCUMENTID,
                        PRINTERLISTID)
                    select
                        DOCUMENT.ID,
                        DOCUMENT.PRINTERLISTID                                        
                    from
                        dbo.DOCUMENT
                    where
                        DOCUMENT.TYPECODE = @TEMPLATETYPE and
                        (DOCUMENT.PRINTFORGROUPSALES = 1 or
                        @IGNOREDELIVERYMETHOD = 1) and                                                    
                        DOCUMENT.ISACTIVE = 1 and
                        DOCUMENT.PRINTERLISTID is not null
                    order by
                        DOCUMENT.SEQUENCE                                                    
                end                    
        end            


    insert into @PRINTERS
    select
        DOCUMENT.REPORTCATALOGID,
        PRINTERLISTITEM.PRINTERNAME    
    from
        @AVAILABLEDOCUMENTS AVAILABLEDOCUMENTS inner join dbo.DOCUMENT on
            AVAILABLEDOCUMENTS.DOCUMENTID = DOCUMENT.ID
        inner join dbo.PRINTERLIST on
            AVAILABLEDOCUMENTS.PRINTERLISTID = PRINTERLIST.ID
        inner join dbo.PRINTERLISTITEM on
            PRINTERLIST.ID = PRINTERLISTITEM.PRINTERLISTID
        inner join dbo.WORKSTATIONPRINTER on
            PRINTERLISTITEM.PRINTERNAME = WORKSTATIONPRINTER.PRINTERNAME
    where
        WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID
    order by
        AVAILABLEDOCUMENTS.NUMBERID,
        WORKSTATIONPRINTER.SEQUENCE

    return;
end