USP_DATALIST_ORDERSALESDOCUMENTSTOPRINT

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

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

    declare @DOCUMENTS table (
        DOCUMENTID uniqueidentifier,
        DOCUMENTTYPE nvarchar(100),
        DOCUMENTREPORTCATALOGID uniqueidentifier,
        PRINTERNAME nvarchar(255),
        PRINTERSEQUENCE int,
        DOCUMENTTYPECODE tinyint,
        DOCUMENTSEQUENCE int,
        DOCUMENTPRINTFORZEROBALANCEORDER bit,
        SVGTEMPLATE nvarchar(max)
    )

    insert into @DOCUMENTS
    select
        DOCUMENT.ID,
        DOCUMENT.TYPE,
        DOCUMENT.REPORTCATALOGID,
        WORKSTATIONPRINTER.PRINTERNAME,
        WORKSTATIONPRINTER.SEQUENCE,
        DOCUMENT.TYPECODE,
        DOCUMENT.SEQUENCE,
        DOCUMENT.PRINTFORZEROBALANCEORDER,
        DOCUMENTSVGTEMPLATE.TEMPLATE as SVGTEMPLATE
    from 
        dbo.SALESORDER
    inner join
        dbo.SALESMETHOD on SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE
    inner join
        dbo.DOCUMENTPRINTINGRULESALESMETHOD on DOCUMENTPRINTINGRULESALESMETHOD.SALESMETHODID = SALESMETHOD.ID
    inner join
        dbo.DOCUMENTPRINTINGRULE on DOCUMENTPRINTINGRULESALESMETHOD.DOCUMENTPRINTINGRULEID = DOCUMENTPRINTINGRULE.ID and SALESORDER.DELIVERYMETHODID = DOCUMENTPRINTINGRULE.DELIVERYMETHODID
    inner join
        dbo.DELIVERYMETHOD on SALESORDER.DELIVERYMETHODID = DELIVERYMETHOD.ID and DELIVERYMETHOD.PRINTCODE = 1    
    inner join
        dbo.DOCUMENT on DOCUMENTPRINTINGRULE.DOCUMENTID = DOCUMENT.ID
    inner join
        dbo.PRINTERLISTITEM on DOCUMENT.PRINTERLISTID = PRINTERLISTITEM.PRINTERLISTID
    inner join
        dbo.WORKSTATIONPRINTER on PRINTERLISTITEM.PRINTERNAME = WORKSTATIONPRINTER.PRINTERNAME
    left outer join
        dbo.DOCUMENTSVGTEMPLATE on DOCUMENT.ID = DOCUMENTSVGTEMPLATE.ID
    where
        SALESORDER.ID = @SALESORDERID
        and WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID
        and DOCUMENT.ISACTIVE = 1;

    select
        DOCUMENTS.DOCUMENTID as ID,
        DOCUMENTS.DOCUMENTTYPE as TYPE,
        0 TEMPLATEPRINTORDER,    
        DOCUMENTS.DOCUMENTREPORTCATALOGID as REPORTCATALOGID,
        null PROGRAMID,
        DOCUMENTS.PRINTERNAME,
        DOCUMENTS.PRINTERSEQUENCE as SEQUENCE,
        DOCUMENTS.DOCUMENTTYPECODE TEMPLATETYPECODE,
        CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
        1 as ISMERCHANTCOPY,
        @SALESORDERID SALESORDERID,
        null DOCUMENTPRINTINGHISTORYID,
        null CREDITPAYMENTID,
        null CREDITID,
        DOCUMENTS.DOCUMENTSEQUENCE,
        0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
        DOCUMENTS.SVGTEMPLATE
    from 
        @DOCUMENTS as DOCUMENTS
    inner join
        dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = @SALESORDERID
    inner join
        dbo.REVENUEPAYMENTMETHOD on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
    inner join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    where
        DOCUMENTS.DOCUMENTTYPECODE = 2 -- Credit Card Receipt


    union
    select
        DOCUMENTS.DOCUMENTID as ID,
        DOCUMENTS.DOCUMENTTYPE as TYPE,
        DOCUMENTS.DOCUMENTTYPECODE TEMPLATEPRINTORDER,    
        DOCUMENTS.DOCUMENTREPORTCATALOGID as REPORTCATALOGID,
        null PROGRAMID,
        DOCUMENTS.PRINTERNAME,
        DOCUMENTS.PRINTERSEQUENCE as SEQUENCE,
        DOCUMENTS.DOCUMENTTYPECODE TEMPLATETYPECODE,
        null CREDITCARDPAYMENTMETHODDETAILID,
        0 ISMERCHANTCOPY,
        @SALESORDERID SALESORDERID,
        null DOCUMENTPRINTINGHISTORYID,
        null CREDITPAYMENTID,
        null CREDITID,
        DOCUMENTS.DOCUMENTSEQUENCE,
        0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
        DOCUMENTS.SVGTEMPLATE
    from 
        @DOCUMENTS as DOCUMENTS
    outer apply
        dbo.UFN_SALESORDER_TOTALS(@SALESORDERID) as TOTALS
    where
        DOCUMENTS.DOCUMENTTYPECODE not in (0, 2) and  -- Ticket, Credit Card Receipt

        (DOCUMENTS.DOCUMENTPRINTFORZEROBALANCEORDER = 1 or TOTALS.TOTAL <> 0)

    union
    select
        DOCUMENTS.DOCUMENTID as ID,
        PROGRAM.NAME as TYPE, 
        2 TEMPLATEPRINTORDER, 
        DOCUMENTS.DOCUMENTREPORTCATALOGID as REPORTCATALOGID,
        PROGRAMDOCUMENT.PROGRAMID,
        DOCUMENTS.PRINTERNAME,
        DOCUMENTS.DOCUMENTSEQUENCE as SEQUENCE,
        10 TEMPLATETYPECODE,
        null CREDITCARDPAYMENTMETHODDETAILID,
        0 ISMERCHANTCOPY,
        @SALESORDERID SALESORDERID,
        null DOCUMENTPRINTINGHISTORYID,
        null CREDITPAYMENTID,
        null CREDITID,
        PROGRAMDOCUMENT.SEQUENCE DOCUMENTSEQUENCE,
        0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
        DOCUMENTS.SVGTEMPLATE
    from 
        @DOCUMENTS as DOCUMENTS
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = @SALESORDERID
    inner join
        dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    inner join
        dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
    inner join
        dbo.PROGRAMDOCUMENT on PROGRAMDOCUMENT.PROGRAMID = PROGRAM.ID and PROGRAMDOCUMENT.DOCUMENTID = DOCUMENTS.DOCUMENTID
    where
        DOCUMENTS.DOCUMENTTYPECODE = 0  -- Ticket

    order by
        TEMPLATEPRINTORDER, DOCUMENTSEQUENCE, CREDITCARDPAYMENTMETHODDETAILID, ISMERCHANTCOPY desc, PROGRAMID, REPORTCATALOGID, DOCUMENTS.PRINTERSEQUENCE;

    return 0;