USP_DATALIST_GROUPSALESDOCUMENTSTOREPRINT

CREATE procedure dbo.USP_DATALIST_GROUPSALESDOCUMENTSTOREPRINT(
    @REPRINTJOBID 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 NAME,
    0 TEMPLATEPRINTORDER,    
    DOCUMENT.REPORTCATALOGID,
    null PROGRAMID,
    WORKSTATIONPRINTER.PRINTERNAME,
    WORKSTATIONPRINTER.SEQUENCE PRINTERSEQUENCE,
    DOCUMENT.TYPECODE TEMPLATETYPECODE,
    CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
    1 ISMERCHANTCOPY,
    DOCUMENTPRINTINGHISTORY.SALESORDERID SALESORDERID,
    DOCUMENTPRINTINGHISTORY.ID DOCUMENTPRINTINGHISTORYID,
    null CREDITPAYMENTID,
    null CREDITID,
    DOCUMENT.SEQUENCE DOCUMENTSEQUENCE,
    0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
    DOCUMENTSVGTEMPLATE.TEMPLATE as SVGTEMPLATE
from 
    dbo.DOCUMENT
    inner join dbo.DOCUMENTPRINTINGHISTORY on DOCUMENT.TYPECODE = DOCUMENTPRINTINGHISTORY.TYPECODE
    inner join dbo.SALESORDER on SALESORDER.ID = DOCUMENTPRINTINGHISTORY.SALESORDERID
    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
    inner join (select ID, SALESORDERID, PAYMENTID, PAYMENTDATEWITHTIMEOFFSET, AMOUNT from dbo.SALESORDERPAYMENT
                            union all 
                            select ID, RESERVATIONID as SALESORDERID, PAYMENTID, PAYMENTDATEWITHTIMEOFFSET, AMOUNT from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
                          ) as SALESORDERPAYMENT
        on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
    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 and
            DOCUMENTPRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID = CREDITCARDPAYMENTMETHODDETAIL.ID    
    left outer join dbo.DOCUMENTSVGTEMPLATE 
        on DOCUMENT.ID = DOCUMENTSVGTEMPLATE.ID
    where
        DOCUMENT.TYPECODE = 2 and
        DOCUMENTPRINTINGHISTORY.REPRINTJOBID = @REPRINTJOBID and
        WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID and
        DOCUMENT.PRINTFORGROUPSALES = 1 and
        DOCUMENT.ISACTIVE = 1
union
select
    DOCUMENT.ID,
    DOCUMENT.TYPE NAME,
    DOCUMENT.TYPECODE TEMPLATEPRINTORDER,    
    DOCUMENT.REPORTCATALOGID,
    null PROGRAMID,
    WORKSTATIONPRINTER.PRINTERNAME,
    WORKSTATIONPRINTER.SEQUENCE PRINTERSEQUENCE,
    DOCUMENT.TYPECODE TEMPLATETYPECODE,
    null CREDITCARDPAYMENTMETHODDETAILID,
    0 ISMERCHANTCOPY,
    DOCUMENTPRINTINGHISTORY.SALESORDERID SALESORDERID,
    DOCUMENTPRINTINGHISTORY.ID DOCUMENTPRINTINGHISTORYID,
    null CREDITPAYMENTID,
    null CREDITID,
    DOCUMENT.SEQUENCE DOCUMENTSEQUENCE,
    0 as SHOULDPRINTMERCHANTANDCUSTOMERCOPIES,
    DOCUMENTSVGTEMPLATE.TEMPLATE as SVGTEMPLATE
from 
    dbo.DOCUMENTPRINTINGHISTORY    
    inner join dbo.DOCUMENT on DOCUMENTPRINTINGHISTORY.TYPECODE = DOCUMENT.TYPECODE
    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 in (1,3,4) and
        DOCUMENTPRINTINGHISTORY.REPRINTJOBID = @REPRINTJOBID and
        WORKSTATIONPRINTER.WORKSTATIONID = @WORKSTATIONID and
        DOCUMENT.PRINTFORGROUPSALES = 1 and
        DOCUMENT.ISACTIVE = 1

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