USP_DISCOUNT_CLONE

CREATE procedure dbo.USP_DISCOUNT_CLONE
(
    @ID uniqueidentifier,
    @CLONEID uniqueidentifier = null output
)
as begin


-- WARNING


-- This SP does absolutely nothing. It originally existed so that we could know the exact configuration of a discount

-- at the time it was applied to an order, which would allow us to recalculate the discounted value of the order when

-- partially refunding it (since many discounts depend on the specific items in the order).


-- However, the decision has been made that we are not recalculating anything for completed orders.


-- Rather than undoing 20+ files, since that decision may change, the lower-risk solution is simply to not clone discounts and

-- make everything work the same way it used to.


-- /WARNING




--    if not exists (select 1 from dbo.SALESORDERITEMITEMDISCOUNT where DISCOUNTID = @ID)

--    and not exists (select 1 from dbo.SALESORDERITEMORDERDISCOUNT where DISCOUNTID = @ID)

--    begin

        set @CLONEID = @ID;
        return 0;
--    end


    if @CLONEID is null set @CLONEID = newid();

    declare @CURRENTDATETIME datetime = getdate();
    declare @CHANGEAGENTID uniqueidentifier;
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @APPLIESTOCODE tinyint;
    declare @DISCOUNTTYPECODE tinyint;
    declare @NAME nvarchar(max);

    select
        @APPLIESTOCODE = APPLIESTOCODE,
        @DISCOUNTTYPECODE = DISCOUNTTYPECODE,
        @NAME = NAME
    from dbo.DISCOUNT where ID = @ID;

    -- Update original to get around NAME uniqueness constraint in insert below

    update dbo.DISCOUNT
    set
        NAME = NAME + ' - archived ' + convert(nvarchar(max), @CURRENTDATETIME, 121),
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATETIME
    where ID = @ID;

    -- Exact copy of original, except for ID and audit columns

    insert into dbo.DISCOUNT (ID, NAME, [DESCRIPTION], APPLIESTOCODE, APPLICATIONTYPECODE, DISCOUNTTYPECODE, CALCULATIONTYPECODE, [PERCENT], AMOUNT, NUMBERTOPURCHASE, NUMBERTODISCOUNTTYPECODE, NUMBERTODISCOUNT, LIMITDISCOUNTSPERORDER, NUMBEROFDISCOUNTSPERORDER, DISCOUNTTICKETSFORCODE, APPLIESTOMERCHANDISE, APPLIESTOTICKETS, MERCHANDISEAMOUNT, MERCHANDISEPERCENT, QUALIFYINGITEMTYPECODE, DISCOUNTITEMTYPECODE, DISCOUNTMERCHANDISEFORCODE, ISACTIVE, ORIGINALDISCOUNTID, SUPERSEDEDBYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select @CLONEID, @NAME, [DESCRIPTION], APPLIESTOCODE, APPLICATIONTYPECODE, DISCOUNTTYPECODE, CALCULATIONTYPECODE, [PERCENT], AMOUNT, NUMBERTOPURCHASE, NUMBERTODISCOUNTTYPECODE, NUMBERTODISCOUNT, LIMITDISCOUNTSPERORDER, NUMBEROFDISCOUNTSPERORDER, DISCOUNTTICKETSFORCODE, APPLIESTOMERCHANDISE, APPLIESTOTICKETS, MERCHANDISEAMOUNT, MERCHANDISEPERCENT, QUALIFYINGITEMTYPECODE, DISCOUNTITEMTYPECODE, DISCOUNTMERCHANDISEFORCODE, ISACTIVE, ORIGINALDISCOUNTID, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
    from dbo.DISCOUNT
    where ID = @ID;

    update dbo.DISCOUNT
    set
        ISACTIVE = 0,
        SUPERSEDEDBYID = @CLONEID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATETIME
    where ID = @ID;

    if @APPLIESTOCODE = 1
    begin
        -- Copy DISCOUNT sub-tables for item-level discounts


        insert into dbo.DISCOUNTPRICETYPE (ID, DISCOUNTID, PRICETYPECODEID, [PERCENT], AMOUNT, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select newid(), @CLONEID, PRICETYPECODEID, [PERCENT], AMOUNT, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
        from dbo.DISCOUNTPRICETYPE
        where DISCOUNTID = @ID;

        -- Many tables reference DISCOUNTGROUP or DISCOUNTGROUPDETAIL, so we have to store their ID's.

        declare @NEWDISCOUNTGROUPIDS table (OLDID uniqueidentifier, NEWID uniqueidentifier);
        declare @NEWDISCOUNTGROUPDETAILIDS table (OLDID uniqueidentifier, NEWID uniqueidentifier);

        begin -- DISCOUNTGROUP


            insert into @NEWDISCOUNTGROUPIDS
            select ID, newid()
            from dbo.DISCOUNTGROUP
            where DISCOUNTID = @ID;

            insert into dbo.DISCOUNTGROUP (ID, DISCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select NEWDISCOUNTGROUPIDS.NEWID, @CLONEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
            from dbo.DISCOUNTGROUP
            inner join @NEWDISCOUNTGROUPIDS NEWDISCOUNTGROUPIDS on NEWDISCOUNTGROUPIDS.OLDID = DISCOUNTGROUP.ID;

        end
        begin -- DISCOUNTGROUPDETAIL


            insert into @NEWDISCOUNTGROUPDETAILIDS
            select ID, newid()
            from dbo.DISCOUNTGROUPDETAIL
            where DISCOUNTGROUPID in (select OLDID from @NEWDISCOUNTGROUPIDS);

            insert into dbo.DISCOUNTGROUPDETAIL (ID, DISCOUNTGROUPID, DISCOUNTEDITEM, DISCOUNTGROUPDETAILAPPLICATIONCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select NEWDISCOUNTGROUPDETAILIDS.NEWID, NEWDISCOUNTGROUPIDS.NEWID, DISCOUNTEDITEM, DISCOUNTGROUPDETAILAPPLICATIONCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
            from dbo.DISCOUNTGROUPDETAIL
            inner join @NEWDISCOUNTGROUPDETAILIDS NEWDISCOUNTGROUPDETAILIDS on NEWDISCOUNTGROUPDETAILIDS.OLDID = DISCOUNTGROUPDETAIL.ID
            inner join @NEWDISCOUNTGROUPIDS NEWDISCOUNTGROUPIDS on NEWDISCOUNTGROUPIDS.OLDID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID;

        end
        begin -- DISCOUNTGROUPDETAILPROGRAM


            insert into dbo.DISCOUNTGROUPDETAILPROGRAM (ID, PROGRAMID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select NEWDISCOUNTGROUPDETAILIDS.NEWID, PROGRAMID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
            from dbo.DISCOUNTGROUPDETAILPROGRAM
            inner join @NEWDISCOUNTGROUPDETAILIDS NEWDISCOUNTGROUPDETAILIDS on NEWDISCOUNTGROUPDETAILIDS.OLDID = DISCOUNTGROUPDETAILPROGRAM.ID;

        end
        begin -- DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT


            insert into dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT (ID, MERCHANDISEDEPARTMENTID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select NEWDISCOUNTGROUPDETAILIDS.NEWID, MERCHANDISEDEPARTMENTID, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
            from dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT
            inner join @NEWDISCOUNTGROUPDETAILIDS NEWDISCOUNTGROUPDETAILIDS on NEWDISCOUNTGROUPDETAILIDS.OLDID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID;

        end
        begin -- DISCOUNTGROUPDETAILMERCHANDISEITEM


            insert into dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM (ID, MERCHANDISEITEMID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select NEWDISCOUNTGROUPDETAILIDS.NEWID, MERCHANDISEITEMID, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
            from dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM
            inner join @NEWDISCOUNTGROUPDETAILIDS NEWDISCOUNTGROUPDETAILIDS on NEWDISCOUNTGROUPDETAILIDS.OLDID = DISCOUNTGROUPDETAILMERCHANDISEITEM.ID;

        end
        begin -- GROUPSIZEDISCOUNT (by quantity)


            insert into dbo.GROUPSIZEDISCOUNT (ID, DISCOUNTID, GROUPSIZE, [PERCENT], AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select newid(), @CLONEID, GROUPSIZE, [PERCENT], AMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
            from dbo.GROUPSIZEDISCOUNT
            where DISCOUNTID = @ID;

        end
        begin -- DISCOUNTQUALIFYINGPRICETYPE


            insert into dbo.DISCOUNTQUALIFYINGPRICETYPE (ID, DISCOUNTID, PRICETYPECODEID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select newid(), @CLONEID, PRICETYPECODEID, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME
            from dbo.DISCOUNTQUALIFYINGPRICETYPE
            where DISCOUNTID = @ID;

        end
    end

    -- Update references on tables that don't need to be copied (these do not affect calculations, only availability to the customer).


    update dbo.PROMOTIONALCODE
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID

    update dbo.DISCOUNTADDRESS
    set ID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where ID = @ID;

    update dbo.DISCOUNTAVAILABILITY
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID;

    update dbo.DISCOUNTCONSTITUENCY
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID;

    update dbo.DISCOUNTGLMAPPING
    set ID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where ID = @ID;

    update dbo.DISCOUNTMEMBER
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID;

    update dbo.DISCOUNTTAX
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID;

    update dbo.PROGRAMDISCOUNT
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID;

    update dbo.SALESMETHODDISCOUNT
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID;

    update dbo.DAILYSALEITEMDISCOUNT
    set DISCOUNTID = @CLONEID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATETIME
    where DISCOUNTID = @ID;

end