UFN_DISCOUNTRESULTS_COMPUTEHASH

CREATE function dbo.UFN_DISCOUNTRESULTS_COMPUTEHASH
(
    @SALESORDERID uniqueidentifier,
    @ITEMDISCOUNTS UDT_APPLYDISCOUNTS_SALESORDERAVAILABLEDISCOUNT readonly,
    @DISCOUNTORDERITEMSXML xml  -- In my tests, parsing the already-created XML was faster than re-selecting from the tables (for single iterations)

)
returns varbinary(20)
with execute as caller
as begin

    -- Notes:

    -- -- datetime -> nvarchar conversion format 120 is 'yyyy-mm-dd hh:mm:ss'

    -- -- IMPORTANT: Including 'DATECHANGED' in the hash ensures that we won't return obsolete results if any prices or criteria change that could affect the calculations.


    declare @SCENARIOSTRINGTOHASH nvarchar(max) = '';
    select @SCENARIOSTRINGTOHASH = @SCENARIOSTRINGTOHASH
        + convert(nchar(36), DISCOUNT.ID)
        + convert(nchar(19), DISCOUNT.DATECHANGED, 120)
        + case when LIMITOVERRIDE.ID is not null then '#' + cast(LIMITOVERRIDE.NUMBEROFDISCOUNTSPERORDER as nvarchar(10)) + '#' else '' end
    from @ITEMDISCOUNTS ITEMDISCOUNTS
    inner join dbo.DISCOUNT on DISCOUNT.ID = ITEMDISCOUNTS.DISCOUNTID
    left join dbo.SALESORDERDISCOUNTLIMITOVERRIDE LIMITOVERRIDE on (LIMITOVERRIDE.SALESORDERID = @SALESORDERID and LIMITOVERRIDE.DISCOUNTID = DISCOUNT.ID)
    order by DISCOUNT.ID;


    select @SCENARIOSTRINGTOHASH = @SCENARIOSTRINGTOHASH
        + convert(nchar(36), TICKETITEMS.PROGRAMID)
        + coalesce(convert(nchar(36), TICKETITEMS.EVENTID), '')
        + convert(nchar(36), TICKETITEMS.PRICETYPECODEID)
        + TICKETITEMS.QUANTITYSTR
        + TICKETITEMS.UNITPRICESTR
        + convert(nchar(19), coalesce(PROGRAMEVENTPRICE.DATECHANGED, PROGRAMPRICE.DATECHANGED), 120)
    from (
        select
            T.c.value('(@PROGRAMID)[1]','uniqueidentifier') PROGRAMID,
            T.c.value('(@EVENTID)[1]','uniqueidentifier') EVENTID,
            T.c.value('(@PRICETYPECODEID)[1]','uniqueidentifier') PRICETYPECODEID,
            T.c.value('(@QUANTITY)[1]','nvarchar(20)') QUANTITYSTR,
            T.c.value('(@UNITPRICE)[1]','nvarchar(20)') UNITPRICESTR
        from
        @DISCOUNTORDERITEMSXML.nodes('/DISCOUNTORDERITEMSXML/ITEM') T(c)
    ) TICKETITEMS
    left join dbo.PROGRAMPRICE on PROGRAMPRICE.PROGRAMID = TICKETITEMS.PROGRAMID and PROGRAMPRICE.PRICETYPECODEID = TICKETITEMS.PRICETYPECODEID
    left join dbo.PROGRAMEVENTPRICE on PROGRAMEVENTPRICE.EVENTID = TICKETITEMS.EVENTID and PROGRAMEVENTPRICE.PRICETYPECODEID = TICKETITEMS.PRICETYPECODEID
    where TICKETITEMS.PROGRAMID is not null
    order by TICKETITEMS.PROGRAMID, TICKETITEMS.EVENTID, TICKETITEMS.PRICETYPECODEID;


    select @SCENARIOSTRINGTOHASH = @SCENARIOSTRINGTOHASH
        + convert(nchar(36), MERCHITEMS.MERCHANDISEPRODUCTINSTANCEID)
        + MERCHITEMS.QUANTITYSTR
        + MERCHITEMS.UNITPRICESTR
        + convert(nchar(19), MERCHANDISEPRODUCTINSTANCE.DATECHANGED, 120)
    from (
        select
            T.c.value('(@MERCHANDISEPRODUCTINSTANCEID)[1]','uniqueidentifier') MERCHANDISEPRODUCTINSTANCEID,
            T.c.value('(@QUANTITY)[1]','nvarchar(20)') QUANTITYSTR,
            T.c.value('(@UNITPRICE)[1]','nvarchar(20)') UNITPRICESTR
        from
        @DISCOUNTORDERITEMSXML.nodes('/DISCOUNTORDERITEMSXML/ITEM') T(c)
    ) MERCHITEMS
    inner join dbo.MERCHANDISEPRODUCTINSTANCE on MERCHANDISEPRODUCTINSTANCE.ID = MERCHITEMS.MERCHANDISEPRODUCTINSTANCEID
    where MERCHITEMS.MERCHANDISEPRODUCTINSTANCEID is not null
    order by MERCHITEMS.MERCHANDISEPRODUCTINSTANCEID;

    if (len(@SCENARIOSTRINGTOHASH) <= 4000)    --Allowed input values are limited to 8000 bytes(i.e. 4000 characters) for HASHBYTES method

        begin
            return hashbytes('SHA1', @SCENARIOSTRINGTOHASH);
        end

    return null;
end