USP_DATALIST_CREDITSALESDOCUMENTSTOPRINT

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

    declare @WORKSTATIONID uniqueidentifier = dbo.UFN_WORKSTATION_GETIDBYMACHINENAME(@WORKSTATIONMACHINENAME);

    declare @SALESORDERID uniqueidentifier
    declare @SALESMETHODTYPECODE tinyint

    select
        @SALESORDERID = isnull(SALESORDER.ID, EXT.SALESORDERID),
        @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    where
        FT.ID = @CREDITID;

    if @SALESMETHODTYPECODE is null begin
        select @SALESMETHODTYPECODE = SALESMETHODTYPECODE from dbo.SALESORDER where ID = @SALESORDERID;
    end

    select
        DOCUMENT.ID,
        DOCUMENT.TYPE,
        0 TEMPLATEPRINTORDER,    
        DOCUMENT.REPORTCATALOGID,
        null PROGRAMID,
        WORKSTATIONPRINTER.PRINTERNAME,
        WORKSTATIONPRINTER.SEQUENCE,
        DOCUMENT.TYPECODE TEMPLATETYPECODE,
        CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
        1 as ISMERCHANTCOPY,
        @SALESORDERID SALESORDERID,
        null DOCUMENTPRINTINGHISTORYID,
        CREDITPAYMENT.ID CREDITPAYMENTID,
        @CREDITID CREDITID,
        DOCUMENT.SEQUENCE DOCUMENTSEQUENCE,
        0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
        DOCUMENTSVGTEMPLATE.TEMPLATE as SVGTEMPLATE
    from
        dbo.CREDITPAYMENT
    inner join
        dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = CREDITPAYMENT.REVENUEID
    inner join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    outer apply
        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
        CREDITPAYMENT.PAYMENTMETHODCODE = 2 and                    -- Credit card refund method

        CREDITPAYMENT.CREDITID = @CREDITID and
        DOCUMENT.TYPECODE = 6 and                
        WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID and
        DOCUMENT.ISACTIVE = 1

    union
    select
        DOCUMENT.ID,
        DOCUMENT.TYPE,
        1 TEMPLATEPRINTORDER,    
        DOCUMENT.REPORTCATALOGID,
        null PROGRAMID,
        WORKSTATIONPRINTER.PRINTERNAME,
        WORKSTATIONPRINTER.SEQUENCE,
        DOCUMENT.TYPECODE TEMPLATETYPECODE,
        null CREDITCARDPAYMENTMETHODDETAILID,
        ISMERCHANTCOPY.ISMERCHANTCOPY,
        @SALESORDERID SALESORDERID,
        null DOCUMENTPRINTINGHISTORYID,
        CREDITPAYMENT.ID CREDITPAYMENTID,
        @CREDITID CREDITID,
        DOCUMENT.SEQUENCE DOCUMENTSEQUENCE,
        0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
        DOCUMENTSVGTEMPLATE.TEMPLATE as SVGTEMPLATE
    from 
        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 join
        dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = @CREDITID and @SALESMETHODTYPECODE = 3
    left outer join
        dbo.DOCUMENTSVGTEMPLATE on DOCUMENT.ID = DOCUMENTSVGTEMPLATE.ID
    outer apply (
        select 0 as ISMERCHANTCOPY
        union all
        select 1 as ISMERCHANTCOPY
    ) as ISMERCHANTCOPY
    where
        DOCUMENT.TYPECODE = 5 and                
        WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID and
        DOCUMENT.ISACTIVE = 1

    order by
        TEMPLATEPRINTORDER, DOCUMENTSEQUENCE, CREDITCARDPAYMENTMETHODDETAILID, CREDITPAYMENTID, ISMERCHANTCOPY desc, REPORTCATALOGID, WORKSTATIONPRINTER.SEQUENCE;

    return 0;