USP_REPORT_SALESREPORT
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @FROMDATE | datetime | IN | |
| @TODATE | datetime | IN | |
| @SALESMETHODID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.USP_REPORT_SALESREPORT
(
@FROMDATE datetime = null,
@TODATE datetime = null,
@SALESMETHODID uniqueidentifier = null
)
as
set nocount on;
declare @SALESMETHODTYPECODE tinyint = null;
select @SALESMETHODTYPECODE = [TYPECODE]
from dbo.[SALESMETHOD]
where [ID] = @SALESMETHODID;
set @FROMDATE = cast(@FROMDATE as date);
set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);
-- pulling group sales discount amount out to the front so it can be stored in a variable
-- to recalculate the gross amount, Bug 246981
declare @GROUPSALESDISCOUNTAMOUNT money = 0;
if (@SALESMETHODTYPECODE is null or @SALESMETHODTYPECODE = 3)
begin
select @GROUPSALESDISCOUNTAMOUNT = ( -- Bug #229902 MDC
(
select isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID in
(
select SALESORDERITEM.ID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDERITEM.SALESORDERID
inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = SALESORDERPAYMENT.PAYMENTID
inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
where
FT.CALCULATEDDATE between @FROMDATE and @TODATE
and SALESORDER.STATUSCODE not in (1, 5) -- Complete, Cancelled
)
)
+
(
select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMITEMDISCOUNT
where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID in
(
select SALESORDERITEM.ID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDERITEM.SALESORDERID
inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = SALESORDERPAYMENT.PAYMENTID
inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
where
FT.CALCULATEDDATE between @FROMDATE and @TODATE
and SALESORDER.STATUSCODE not in (1, 5) -- Complete, Cancelled
)
)
)
option (recompile);
end;
-- Pulling the items under consideration into temporary storage allows us to query per typecode, with inner joins, rather than left-joining across dozens of tables at once, which SQL Server can't optimize as well.
-- We don't use this for the refunds, however, because it's possible that the order was completed within the given time range and the refund was not, or vice versa.
declare @SALESORDERITEM table (
ID uniqueidentifier primary key,
TYPECODE tinyint,
QUANTITY decimal(20,4),
TOTAL money
);
insert into @SALESORDERITEM
select
SALESORDERITEM.ID,
SALESORDERITEM.TYPECODE,
SALESORDERITEM.QUANTITY,
SALESORDERITEM.TOTAL
from dbo.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE) as FILTEREDSALESORDERS
inner join dbo.SALESORDER on SALESORDER.ID = FILTEREDSALESORDERS.ID
inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
where
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
and [SALESORDER].[STATUSCODE] = 1;
--Note, since users want to see the report in order of Admission, Events, and Memberships... SALESORDERITEM.TYPECODEs are overridden for sorting
--Admission = 0, Events = 5, Membership = 10, Membership Add-ons = 11, Donations = 15, Resources = 20, Volunteers = 25, Fees = 30, Facility rentals = 35, Merchandise = 40 (Numbers separated to provide room for future item types)
-- Also note that this report does not include taxes.
-- Tickets
select
0 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMTICKET].[PROGRAMNAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
sum([ITEMDISCOUNTS].[AMOUNT]) + isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0) as [DISCOUNT],
0 as [REFUND],
[PROGRAMCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
COMBINATION.NAME as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[ID] = [SALESORDERITEMTICKET].[ID]
left join dbo.[PROGRAMCATEGORYCODE] on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
left join dbo.[COMBINATION] on [COMBINATION].[ID] = [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID]
outer apply (
select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMITEMDISCOUNT
where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
) as ITEMDISCOUNTS
outer apply (
select sum(AMOUNT) as AMOUNT
from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
where SALESORDERITEMID = SALESORDERITEM.ID
) as SALESORDERITEMORDERDISCOUNTDETAIL
where
[SALESORDERITEM].[TYPECODE] = 0
group by [SALESORDERITEMTICKET].[PROGRAMNAME], [PROGRAMCATEGORYCODE].[DESCRIPTION], COMBINATION.NAME
union all
-- Memberships
select
10 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
sum(SALESORDERITEMMEMBERSHIPITEMPROMOTIONS.AMOUNT) as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
outer apply (
select isnull(sum(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
where SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
) as SALESORDERITEMMEMBERSHIPITEMPROMOTIONS
where
[SALESORDERITEM].[TYPECODE] = 1
group by
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME]
union all
-- Membership add-ons
select
11 as [SALESORDERITEMTYPECODE],
ADDON.NAME as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.ID = SALESORDERITEM.ID
inner join dbo.ADDON on ADDON.ID = SALESORDERITEMMEMBERSHIPADDON.ADDONID
where
[SALESORDERITEM].[TYPECODE] = 16
group by
ADDON.NAME
union all
-- Donations
select
15 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMDONATION].[DESIGNATIONNAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMDONATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
where
[SALESORDERITEM].[TYPECODE] = 2
group by
[SALESORDERITEMDONATION].[DESIGNATIONNAME]
union all
-- Itinerary supply/equipment resources
select
20 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMITINERARYRESOURCE].[RESOURCENAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
[RESOURCECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMITINERARYRESOURCE] on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYRESOURCE].[SALESORDERITEMID]
inner join dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.RESOURCECATEGORYCODE on RESOURCE.RESOURCECATEGORYCODEID = RESOURCECATEGORYCODE.ID
where
[SALESORDERITEM].[TYPECODE] = 8
group by
[SALESORDERITEMITINERARYRESOURCE].[RESOURCENAME],
[RESOURCECATEGORYCODE].[DESCRIPTION]
union all
-- Itinerary item supply/equipment resources
select
20 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMITINERARYITEMRESOURCE].[RESOURCENAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
[ITEMRESOURCECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMITINERARYITEMRESOURCE] on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMRESOURCE].[SALESORDERITEMID]
inner join dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID left join dbo.RESOURCECATEGORYCODE ITEMRESOURCECATEGORYCODE on RESOURCE.RESOURCECATEGORYCODEID = ITEMRESOURCECATEGORYCODE.ID
inner join dbo.RESOURCECATEGORYCODE on RESOURCE.RESOURCECATEGORYCODEID = RESOURCECATEGORYCODE.ID
where
[SALESORDERITEM].[TYPECODE] = 9
group by
[SALESORDERITEMITINERARYITEMRESOURCE].[RESOURCENAME],
[ITEMRESOURCECATEGORYCODE].[DESCRIPTION]
union all
-- Itinerary staffing resources
select
25 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMITINERARYSTAFFRESOURCE].[VOLUNTEERTYPENAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMITINERARYSTAFFRESOURCE] on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYSTAFFRESOURCE].[SALESORDERITEMID]
where
[SALESORDERITEM].[TYPECODE] = 10
group by
[SALESORDERITEMITINERARYSTAFFRESOURCE].[VOLUNTEERTYPENAME]
union all
-- Itinerary item staffing resources
select
25 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[VOLUNTEERTYPENAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMITINERARYITEMSTAFFRESOURCE] on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[SALESORDERITEMID]
where
[SALESORDERITEM].[TYPECODE] = 11
group by
[SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[VOLUNTEERTYPENAME]
union all
-- Fees
select
30 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMFEE].[FEENAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMFEE] on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
where
[SALESORDERITEM].[TYPECODE] = 3
group by
[SALESORDERITEMFEE].[FEENAME]
union all
-- Facility rental
select
35 as [SALESORDERITEMTYPECODE],
[SALESORDERITEMFACILITY].[EVENTLOCATIONNAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMFACILITY] on [SALESORDERITEM].[ID] = [SALESORDERITEMFACILITY].ID
where
[SALESORDERITEM].[TYPECODE] = 7
group by
[SALESORDERITEMFACILITY].[EVENTLOCATIONNAME]
union all
-- Merchandise
select
40 as [SALESORDERITEMTYPECODE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
sum([ITEMDISCOUNTS].[AMOUNT]) + isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0) as [DISCOUNT],
0 as [REFUND],
null as [CATEGORY],
null as COMBINATIONNAME
from @SALESORDERITEM as SALESORDERITEM
inner join dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
inner join dbo.[MERCHANDISEDEPARTMENT] on [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
outer apply (
select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMITEMDISCOUNT
where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
) as ITEMDISCOUNTS
outer apply (
select sum(AMOUNT) as AMOUNT
from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
where SALESORDERITEMID = SALESORDERITEM.ID
) as SALESORDERITEMORDERDISCOUNTDETAIL
where
[SALESORDERITEM].[TYPECODE] = 14
group by
[MERCHANDISEDEPARTMENT].[NAME]
union all
-- Group Sales prepayments (for reservations that are not yet checked in)
select
45 as SALESORDERITEMTYPECODE,
null as PROGRAMNAME,
count(*) as QUANTITY,
coalesce(sum(SALESORDERPAYMENT.AMOUNT),0) + @GROUPSALESDISCOUNTAMOUNT as GROSS,
@GROUPSALESDISCOUNTAMOUNT as DISCOUNT,
0 as REFUND,
null as CATEGORY,
null as COMBINATIONNAME
from dbo.FINANCIALTRANSACTION as FT
inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = FT.ID
inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
where
(@SALESMETHODTYPECODE is null or @SALESMETHODTYPECODE = 3) -- Determines whether Group Sales should be included
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and SALESORDER.SALESMETHODTYPECODE = 3 -- Group Sales
and SALESORDER.STATUSCODE <> 1 -- Not yet completed
having
count(*) > 0 -- If there have been no Group Sales prepayments in the given time range, don't show an empty row.
union all
-- Order refunds (other than membership/add-on and event registration)
select
case
when [SALESORDERITEM].[TYPECODE] = 0 then 0
when [SALESORDERITEM].[TYPECODE] = 1 then 10
when [SALESORDERITEM].[TYPECODE] = 2 then 15
when [SALESORDERITEM].[TYPECODE] = 3 then 30
when [SALESORDERITEM].[TYPECODE] = 14 then 40
when [SALESORDERITEM].[TYPECODE] = 16 then 11
end as [SALESORDERITEMTYPECODE],
case [SALESORDERITEM].[TYPECODE]
when 0 then [SALESORDERITEMTICKET].[PROGRAMNAME]
when 1 then [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME]
when 2 then [SALESORDERITEMDONATION].[DESIGNATIONNAME]
when 3 then [SALESORDERITEMFEE].[FEENAME]
when 14 then [MERCHANDISEDEPARTMENT].[NAME]
when 16 then [ADDON].[NAME]
end as [PROGRAMNAME],
-sum(coalesce(LI.QUANTITY,0)) as [QUANTITY],
0 as [GROSS],
0 as [DISCOUNT],
sum(coalesce(LI.QUANTITY * LI.UNITVALUE - EXT.DISCOUNTS,0) - (
case
-- If it's an old style (non-itemized) refund, then we know the discount was fully reversed and should include its amount.
when coalesce(TICKET.ID, SALESORDERITEMMERCHANDISEUNIT.ID) is null then
isnull([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT], 0)
-- Otherwise, if the BASEAMOUNT is more than the amount paid for the ticket or merchandise, then order level discounts were reversed.
when LI.BASEAMOUNT > coalesce(TICKET.AMOUNTPAID, SALESORDERITEMMERCHANDISEUNIT.AMOUNTPAID) then
coalesce(TICKET.ORDERLEVELDISCOUNTSAPPLIED, SALESORDERITEMMERCHANDISEUNIT.ORDERLEVELDISCOUNTSAPPLIED)
-- Finally, if BASEAMOUNT is less than or equal to the amount paid, then order-level discounts were not reversed; do not subtract them.
else 0
end
)
) as [REFUND],
case [SALESORDERITEM].[TYPECODE]
when 0 then [PROGRAMCATEGORYCODE].[DESCRIPTION]
else null
end as [CATEGORY],
case when SALESORDERITEM.TYPECODE = 0 then COMBINATION.NAME else null end as [COMBINATIONNAME]
from dbo.FINANCIALTRANSACTION as FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
inner join dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
--Tickets
left join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE] on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
left join TICKET on TICKET.ID = EXT.SALESORDERITEMIZEDITEMID
left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
left join dbo.COMBINATION on COMBINATION.ID = SALESORDERITEMTICKETCOMBINATION.COMBINATIONID
--Memberships
left join dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
--Membership add-ons
left join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
left join dbo.ADDON on ADDON.ID = SALESORDERITEMMEMBERSHIPADDON.ADDONID
--Fees
left join dbo.[SALESORDERITEMFEE] on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
--Merchandise
left join dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join dbo.[MERCHANDISEDEPARTMENT] on [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
left join SALESORDERITEMMERCHANDISEUNIT on SALESORDERITEMMERCHANDISEUNIT.ID = EXT.SALESORDERITEMIZEDITEMID
--Order discounts
left join (
select
SALESORDERITEMID,
sum(AMOUNT) as AMOUNT
from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
group by
SALESORDERITEMID
) SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
--Donations
left join dbo.[SALESORDERITEMDONATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
where
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
[SALESORDERITEM].[TYPECODE] in (0,1,2,3,14,16) and
FT.CALCULATEDDATE between @FROMDATE and @TODATE
and FT.TYPECODE = 23 -- Refund
group by
[SALESORDERITEM].[TYPECODE],
SALESORDERITEMTICKET.PROGRAMNAME,
SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMNAME,
SALESORDERITEMDONATION.DESIGNATIONNAME,
SALESORDERITEMFEE.FEENAME,
MERCHANDISEDEPARTMENT.NAME,
ADDON.NAME,
PROGRAMCATEGORYCODE.DESCRIPTION,
COMBINATION.NAME
union all
--Outside-order Refunds (Memberships)
select
10 as [SALESORDERITEMTYPECODE],
[CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME],
-count(1) as [QUANTITY],
0 as [GROSS],
0 as [DISCOUNT],
sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as [REFUND],
null as CATEGORY,
null as COMBINATIONNAME
from dbo.FINANCIALTRANSACTION as FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.[CREDITITEMMEMBERSHIP] on [CREDITITEMMEMBERSHIP].[ID] = LI.ID
where
@SALESMETHODTYPECODE is null
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and FT.TYPECODE = 23 -- Refund
and EXT.SALESORDERITEMID is null
group by
[CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME]
union all
--Outside-order refunds (membership add-ons)
select
11 as SALESORDERITEMTYPECODE,
ADDON.NAME,
-count(1) as [QUANTITY],
0 as [GROSS],
0 as [DISCOUNT],
sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as [REFUND],
null as CATEGORY,
null as COMBINATIONNAME
from dbo.FINANCIALTRANSACTION as FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = LI.SOURCELINEITEMID
inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
where
@SALESMETHODTYPECODE is null and
FT.CALCULATEDDATE between @FROMDATE and @TODATE and
FT.TYPECODE = 23 and -- Refund
EXT.SALESORDERITEMID is null
group by
ADDON.NAME
union all
-- Event Registrations and event registration refunds
select
5 as [SALESORDERITEMTYPECODE],
[PROGRAMNAME],
sum(
case
-- Need to track whether there were payments and/or refunds
-- for the special case of having no payments for waived fees
-- and refunding those event registrations
when PAYMENTCOUNT = 0 and REFUNDCOUNT > 0 then
-1
when [GROSS] > [REFUND] or ([GROSS] = 0 and [REFUND] = 0 and REFUNDCOUNT = 0) then
1
else
0
end
) [QUANTITY],
sum([GROSS]) [GROSS],
0 as [DISCOUNT],
sum([REFUND]) as [REFUND],
null as [CATEGORY],
null as [COMBINATIONNAME]
from --Sum the gross and refund for each registrant so we can compare it for the quantity calculation
(select
[PROGRAMNAME],
sum([GROSS]) as [GROSS],
sum(ISPAYMENT) as PAYMENTCOUNT,
sum([REFUND]) as [REFUND],
sum(ISREFUND) as REFUNDCOUNT,
[REGISTRANTID]
from --Get all the payment and refund pieces so that we can aggregate them for each registrant
(select --Event registration payments
[EVENT].[NAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
1 as ISPAYMENT,
0 as [REFUND],
0 as ISREFUND,
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as REGISTRANTID
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM] on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
inner join dbo.[REGISTRANT] on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
inner join dbo.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE) as FILTEREDSALESORDERS on FILTEREDSALESORDERS.ID = SALESORDERITEM.SALESORDERID
inner join dbo.[SALESORDER] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
where
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
group by
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID],
[EVENT].[NAME]
union all
select
[EVENT].[NAME] as [PROGRAMNAME],
0 as [GROSS],
0 as ISPAYMENT,
coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [REFUND],
1 as ISREFUND,
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as REGISTRANTID
from dbo.FINANCIALTRANSACTION as FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
inner join dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
inner join dbo.[REGISTRANT] on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
inner join dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
where
FT.CALCULATEDDATE between @FROMDATE and @TODATE and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
group by
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID],
[EVENT].[NAME]
--Event Registration refunds/exchanges purchased outside the context of an order
union all
select
[EVENT].[NAME] as [PROGRAMNAME],
0 as [GROSS],
0 as ISPAYMENT,
coalesce(sum(REFUNDEDLI.BASEAMOUNT),0) as [REFUND],
1 as ISREFUND,
[EVENTREGISTRANTPAYMENT].[REGISTRANTID] as REGISTRANTID
from dbo.FINANCIALTRANSACTION as FT
inner join dbo.[CREDITPAYMENT] on [CREDITPAYMENT].[CREDITID] = FT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on REFUNDEDLI.ID = [CREDITPAYMENT].[REVENUESPLITID]
inner join dbo.[EVENTREGISTRANTPAYMENT] on [EVENTREGISTRANTPAYMENT].[PAYMENTID] = REFUNDEDLI.ID
inner join dbo.[REGISTRANT] on [REGISTRANT].[ID] = [EVENTREGISTRANTPAYMENT].[REGISTRANTID]
inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
where
FT.CALCULATEDDATE between @FROMDATE and @TODATE and
@SALESMETHODTYPECODE is null
group by EVENTREGISTRANTPAYMENT.REGISTRANTID, [EVENT].NAME
) as PAYMENTPIECES
group by PROGRAMNAME, REGISTRANTID
) as REGISTRANTS
group by PROGRAMNAME, REGISTRANTID
order by
SALESORDERITEMTYPECODE,
PROGRAMNAME,
COMBINATIONNAME -- Currently the design has combos in alphabetical order within the program. If that ever changes, be careful modifying this line, because the rdl also depends on this ordering to show/hide the toggle next to the program name (entries with null combinationname must appear before other entries).
option (recompile);
return 0;