USP_DATAFORMTEMPLATE_VIEW_SALESORDERCOMPLETEANDPRINTINFORMATION
The load procedure used by the view dataform template "Sales Order Complete and Print Information View Data Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(max) | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @ORDERSTATUS | tinyint | INOUT | Order status |
| @PRINTCODE | tinyint | INOUT | Print code |
| @WORKSTATIONID | uniqueidentifier | INOUT | WORKSTATIONID |
| @OPENDRAWER | bit | INOUT | Open Drawer |
| @CASHDRAWERPRINTER | nvarchar(255) | INOUT | Receipt printer |
| @CASHDRAWEROPENCOMMAND | nvarchar(50) | INOUT | Printer command |
| @PRINTERS | xml | INOUT | Printers |
| @CREDITCARDPAYMENTMETHODDETAILS | xml | INOUT | Credit card payment method details |
| @PROGRAMS | xml | INOUT | Programs |
| @PROGRAMWORKSTATIONPRINTERS | xml | INOUT | Program workstation printers |
| @WORKSTATIONPRINTERS | xml | INOUT | Workstation printers |
| @ISZERODOLLARORDER | bit | INOUT | ISZERODOLLARORDER |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESORDERCOMPLETEANDPRINTINFORMATION
(
@ID nvarchar(max),
@DATALOADED bit = 0 output,
@ORDERSTATUS tinyint = null output,
@PRINTCODE tinyint = null output,
@WORKSTATIONID uniqueidentifier = null output,
@OPENDRAWER bit = null output,
@CASHDRAWERPRINTER nvarchar(255) = null output,
@CASHDRAWEROPENCOMMAND nvarchar(50) = null output,
@PRINTERS xml = null output,
@CREDITCARDPAYMENTMETHODDETAILS xml = null output,
@PROGRAMS xml = null output,
@PROGRAMWORKSTATIONPRINTERS xml = null output,
@WORKSTATIONPRINTERS xml = null output,
@ISZERODOLLARORDER bit = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @SALESORDERID uniqueidentifier
declare @MACHINENAME nvarchar(255)
-- Extract ID and machine name
declare @SEPARATOR int
set @SEPARATOR = charindex ('|', @ID, 0)
set @SALESORDERID = convert(uniqueidentifier, left(@ID, @SEPARATOR - 1))
set @MACHINENAME = right(@ID, len(@ID) - @SEPARATOR)
select
@DATALOADED = 1,
@ORDERSTATUS = SALESORDER.STATUSCODE,
@PRINTCODE = coalesce(DELIVERYMETHOD.PRINTCODE,0),
@ISZERODOLLARORDER = case when AMOUNT = 0 then 1 else 0 end
from dbo.SALESORDER
left join dbo.DELIVERYMETHOD on
SALESORDER.DELIVERYMETHODID = DELIVERYMETHOD.ID
where SALESORDER.ID = @SALESORDERID
if @DATALOADED = 1
begin
if @ORDERSTATUS = 1
begin
-- Even though MACHINENAME is unique, we should try to maintain what search does so nothing changes
set @MACHINENAME = coalesce(@MACHINENAME,'') + '%' ;
select top(1)
@WORKSTATIONID = ID
from dbo.WORKSTATION where (MACHINENAME like @MACHINENAME) order by NAME asc
if @WORKSTATIONID is not null
begin
set @OPENDRAWER = 0
if exists
(
select
PAYMENTMETHOD.PAYMENTMETHODID
from
(
select
case
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then
'CF693144-0204-4FFD-90EF-335E509F9C69'
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then
'49BCEDF6-213C-4734-896B-BB183B487D22'
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then
'3D8F2B59-3647-48F0-831A-ABC527C3B7AB'
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then
OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID
end as PAYMENTMETHODID
from dbo.SALESORDER
inner join dbo.SALESORDERPAYMENT on
SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
inner join dbo.REVENUE
on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
inner join dbo.REVENUEPAYMENTMETHOD on
REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
left outer join dbo.OTHERPAYMENTMETHODDETAIL on
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 and
OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
where
SALESORDER.ID = @SALESORDERID
) PAYMENTMETHOD
inner join
(
select
case
when WORKSTATIONCASHDRAWERPAYMENTMETHOD.PAYMENTTYPECODE = 0 then
'CF693144-0204-4FFD-90EF-335E509F9C69'
when WORKSTATIONCASHDRAWERPAYMENTMETHOD.PAYMENTTYPECODE = 1 then
'49BCEDF6-213C-4734-896B-BB183B487D22'
when WORKSTATIONCASHDRAWERPAYMENTMETHOD.PAYMENTTYPECODE = 2 then
'3D8F2B59-3647-48F0-831A-ABC527C3B7AB'
when WORKSTATIONCASHDRAWERPAYMENTMETHOD.PAYMENTTYPECODE = 10 then
WORKSTATIONCASHDRAWERPAYMENTMETHOD.OTHERPAYMENTMETHODCODEID
end as PAYMENTMETHODID
from dbo.WORKSTATIONCASHDRAWERPAYMENTMETHOD
where
WORKSTATIONCASHDRAWERPAYMENTMETHOD.WORKSTATIONID = @WORKSTATIONID
) WORKSTATIONPAYMENTMETHOD on
PAYMENTMETHOD.PAYMENTMETHODID = WORKSTATIONPAYMENTMETHOD.PAYMENTMETHODID
)
set @OPENDRAWER = 1
else
set @OPENDRAWER = 0
if @OPENDRAWER = 1
begin
select
@CASHDRAWERPRINTER = coalesce(PRINTERNAME,''),
@CASHDRAWEROPENCOMMAND = CASHDRAWEROPENCOMMAND
from dbo.WORKSTATION
left outer join dbo.WORKSTATIONPRINTER on
WORKSTATION.CASHDRAWERPRINTERID = WORKSTATIONPRINTER.ID
where WORKSTATION.ID = @WORKSTATIONID
end
if @PRINTCODE = 1
begin
set @PRINTERS =
(
select WORKSTATIONPRINTER.[PRINTERNAME]
from dbo.WORKSTATIONPRINTER
where WORKSTATIONID = @WORKSTATIONID
order by SEQUENCE
for xml raw('ITEM'),type,elements,root('PRINTERS'),binary base64
)
set @CREDITCARDPAYMENTMETHODDETAILS =
(
select
T.CREDITCARDPAYMENTMETHODDETAILID as CREDITCARDPAYMENTMETHODDETAILID
from
(
select
CREDITCARDPAYMENTMETHODDETAIL.ID as CREDITCARDPAYMENTMETHODDETAILID
from dbo.SALESORDER
inner join dbo.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
where SALESORDER.ID = @SALESORDERID
union all
select
CREDITCARDPAYMENTMETHODDETAIL.ID as CREDITCARDPAYMENTMETHODDETAILID
from dbo.SALESORDER
inner join dbo.RESERVATIONSECURITYDEPOSITPAYMENT on
SALESORDER.ID = RESERVATIONSECURITYDEPOSITPAYMENT.RESERVATIONID
inner join dbo.REVENUE on
RESERVATIONSECURITYDEPOSITPAYMENT.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
where SALESORDER.ID = @SALESORDERID
) as T
for xml raw('ITEM'),type,elements,root('CREDITCARDPAYMENTMETHODDETAILS'),binary base64
)
declare @PROGRAMSTABLE table
(
ID uniqueidentifier,
PROGRAMNAME nvarchar(100)
)
insert into @PROGRAMSTABLE
select distinct
PROGRAM.ID as PROGRAMID,
PROGRAM.NAME as PROGRAMNAME
from dbo.SALESORDERITEMTICKET SOIT
inner join dbo.SALESORDERITEM SOI on SOI.ID = SOIT.ID
left join dbo.EVENT on SOIT.EVENTID = EVENT.ID
inner join dbo.PROGRAM on
EVENT.PROGRAMID = PROGRAM.ID or
SOIT.PROGRAMID = PROGRAM.ID
where SOI.SALESORDERID = @SALESORDERID
set @PROGRAMS =
(
select
ID as PROGRAMID,
PROGRAMNAME as PROGRAMNAME
from @PROGRAMSTABLE
for xml raw('ITEM'),type,elements,root('PROGRAMS'),binary base64
)
set @PROGRAMWORKSTATIONPRINTERS =
(
select
PT.ID as PROGRAMID,
WP.REPORTCATALOGID as REPORTCATALOGID,
WP.PRINTERNAME as PRINTERNAME
from @PROGRAMSTABLE as PT
cross apply dbo.UFN_SALESORDER_GETSALESDOCUMENTWORKSTATIONPRINTERS
(@WORKSTATIONID, 0, @SALESORDERID, PT.ID, 0) as WP
for xml raw('ITEM'),type,elements,root('PROGRAMWORKSTATIONPRINTERS'),binary base64
)
set @WORKSTATIONPRINTERS =
(
select
T.TYPE as TYPE,
T.REPORTCATALOGID as REPORTCATALOGID,
T.PRINTERNAME as PRINTERNAME,
T.PRINTFORZEROBALANCEORDER
from
(
select
1 as TYPE, -- ItemizedReceipt
WP.REPORTCATALOGID as REPORTCATALOGID,
WP.PRINTERNAME as PRINTERNAME,
WP.PRINTFORZEROBALANCEORDER as PRINTFORZEROBALANCEORDER
from dbo.UFN_SALESORDER_GETSALESDOCUMENTWORKSTATIONPRINTERS_2
(@WORKSTATIONID, 1, @SALESORDERID, null, 0) as WP
union all
select
2 as TYPE, -- CreditCardReceipt
WP.REPORTCATALOGID as REPORTCATALOGID,
WP.PRINTERNAME as PRINTERNAME,
1 as PRINTFORZEROBALANCEORDER
from dbo.UFN_SALESORDER_GETSALESDOCUMENTWORKSTATIONPRINTERS
(@WORKSTATIONID, 2, @SALESORDERID, null, 0) as WP
union all
select
3 as TYPE, -- MailingHeader
WP.REPORTCATALOGID as REPORTCATALOGID,
WP.PRINTERNAME as PRINTERNAME,
1 as PRINTFORZEROBALANCEORDER
from dbo.UFN_SALESORDER_GETSALESDOCUMENTWORKSTATIONPRINTERS
(@WORKSTATIONID, 3, @SALESORDERID, null, 0) as WP
union all
select
4 as TYPE, -- WillCallHeader
WP.REPORTCATALOGID as REPORTCATALOGID,
WP.PRINTERNAME as PRINTERNAME,
1 as PRINTFORZEROBALANCEORDER
from dbo.UFN_SALESORDER_GETSALESDOCUMENTWORKSTATIONPRINTERS
(@WORKSTATIONID, 4, @SALESORDERID, null, 0) as WP
) as T
for xml raw('ITEM'),type,elements,root('WORKSTATIONPRINTERS'),binary base64
)
end -- PRINTCODE = 1
end -- WORKSTATION IS NOT NULL
end -- ORDERSTATUS = 1
end -- DATALOADED = 1
return 0;