USP_DATALIST_GROUPSALESORDERDOCUMENTSTOPRINT

CREATE procedure dbo.USP_DATALIST_GROUPSALESORDERDOCUMENTSTOPRINT(
    @REVENUEID uniqueidentifier,
    @WORKSTATIONMACHINENAME nvarchar(255))
as
    set nocount on;

    declare @WORKSTATIONID uniqueidentifier
    select top(1)
        @WORKSTATIONID = ID 
    from dbo.WORKSTATION 
    where (@WORKSTATIONMACHINENAME = MACHINENAME) 
        or 
        (@WORKSTATIONMACHINENAME = 
            (
                case when charindex('.', MACHINENAME) > 1
                then left(MACHINENAME, charindex('.', MACHINENAME) -1)
                end
            )
        )

    select
        DOCUMENT.ID,
        DOCUMENT.TYPE,
        0 TEMPLATEPRINTORDER,    
        DOCUMENT.REPORTCATALOGID,
        null PROGRAMID,
        WORKSTATIONPRINTER.PRINTERNAME,
        WORKSTATIONPRINTER.SEQUENCE,
        DOCUMENT.TYPECODE TEMPLATETYPECODE,
        CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
        1 ISMERCHANTCOPY,
        SALESORDERPAYMENT.SALESORDERID SALESORDERID,
        null DOCUMENTPRINTINGHISTORYID,
        null CREDITPAYMENTID,
        null CREDITID,
        DOCUMENT.SEQUENCE DOCUMENTSEQUENCE,
        0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
        DOCUMENTSVGTEMPLATE.TEMPLATE as SVGTEMPLATE
    from    
    (
        select ID, SALESORDERID, PAYMENTID, PAYMENTDATEWITHTIMEOFFSET, AMOUNT
        from dbo.SALESORDERPAYMENT
        union all 
        select ID, RESERVATIONID as SALESORDERID, PAYMENTID, PAYMENTDATEWITHTIMEOFFSET, AMOUNT
        from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
    ) as SALESORDERPAYMENT 
    inner join dbo.REVENUE on
        SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
    inner join dbo.REVENUEPAYMENTMETHOD on 
        REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
    inner join dbo.CREDITCARDPAYMENTMETHODDETAIL on
            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and
            CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    cross join dbo.DOCUMENT
    inner join dbo.PRINTERLIST on 
        DOCUMENT.PRINTERLISTID = PRINTERLIST.ID
    inner join dbo.PRINTERLISTITEM on 
        PRINTERLIST.ID = PRINTERLISTITEM.PRINTERLISTID
    inner join dbo.WORKSTATIONPRINTER on 
        PRINTERLISTITEM.PRINTERNAME = WORKSTATIONPRINTER.PRINTERNAME
    left outer join dbo.DOCUMENTSVGTEMPLATE 
        on DOCUMENT.ID = DOCUMENTSVGTEMPLATE.ID
    where
        DOCUMENT.TYPECODE = 2 and
        SALESORDERPAYMENT.PAYMENTID = @REVENUEID and
        WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID and
        DOCUMENT.ISACTIVE = 1 and
        DOCUMENT.PRINTFORGROUPSALES = 1

    union

    select
        DOCUMENT.ID,
        DOCUMENT.TYPE,
        0 TEMPLATEPRINTORDER,    
        DOCUMENT.REPORTCATALOGID,
        null PROGRAMID,
        WORKSTATIONPRINTER.PRINTERNAME,
        WORKSTATIONPRINTER.SEQUENCE,
        DOCUMENT.TYPECODE TEMPLATETYPECODE,
        CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
        0 ISMERCHANTCOPY,
        SALESORDERPAYMENT.SALESORDERID SALESORDERID,
        null DOCUMENTPRINTINGHISTORYID,
        null CREDITPAYMENTID,
        null CREDITID,
        DOCUMENT.SEQUENCE DOCUMENTSEQUENCE,
        0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
        DOCUMENTSVGTEMPLATE.TEMPLATE as SVGTEMPLATE
    from    
    (select ID, SALESORDERID, PAYMENTID, PAYMENTDATEWITHTIMEOFFSET, AMOUNT, 0 as ISSECURITYDEPOSITPAYMENT
                    from dbo.SALESORDERPAYMENT
                union all 
                select ID, RESERVATIONID as SALESORDERID, PAYMENTID, PAYMENTDATEWITHTIMEOFFSET, AMOUNT, 1 as ISSECURITYDEPOSITPAYMENT
                    from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
    ) as SALESORDERPAYMENT
    inner join dbo.REVENUE on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
    inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL on
            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and
            CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID,
    dbo.DOCUMENT
    inner join dbo.PRINTERLIST on DOCUMENT.PRINTERLISTID = PRINTERLIST.ID
    inner join dbo.PRINTERLISTITEM on PRINTERLIST.ID = PRINTERLISTITEM.PRINTERLISTID
    inner join dbo.WORKSTATIONPRINTER on PRINTERLISTITEM.PRINTERNAME = WORKSTATIONPRINTER.PRINTERNAME
    left outer join dbo.DOCUMENTSVGTEMPLATE 
        on DOCUMENT.ID = DOCUMENTSVGTEMPLATE.ID
    where
        (DOCUMENT.TYPECODE = 1 and SALESORDERPAYMENT.ISSECURITYDEPOSITPAYMENT = 0) and
        SALESORDERPAYMENT.PAYMENTID = @REVENUEID and
        WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID and
        DOCUMENT.ISACTIVE = 1 and
        DOCUMENT.PRINTFORGROUPSALES = 1
    order by
        TEMPLATEPRINTORDER, DOCUMENTSEQUENCE, CREDITCARDPAYMENTMETHODDETAILID, ISMERCHANTCOPY desc, REPORTCATALOGID, WORKSTATIONPRINTER.SEQUENCE