USP_PROGRAMDISCOUNT_ADD

CREATE procedure dbo.USP_PROGRAMDISCOUNT_ADD
(
    @ID uniqueidentifier = null output,
    @PROGRAMID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @DISCOUNTID uniqueidentifier = null
)
as

set nocount on;

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

declare @DISCOUNTGROUPID uniqueidentifier;
declare @DISCOUNTGROUPDETAILID uniqueidentifier;
declare @DISCOUNTTYPECODE tinyint;
declare @QUALIFYINGITEMTYPECODE tinyint;
declare @DISCOUNTITEMTYPECODE tinyint;

set @DISCOUNTGROUPID = @ID;
set @DISCOUNTGROUPDETAILID = newid();

select top 1
    @DISCOUNTTYPECODE = DISCOUNTTYPECODE,
    @QUALIFYINGITEMTYPECODE = QUALIFYINGITEMTYPECODE,
    @DISCOUNTITEMTYPECODE = DISCOUNTITEMTYPECODE
from dbo.DISCOUNT 
where ID = @DISCOUNTID

begin try

    insert into dbo.DISCOUNTGROUP (
        ID,
        DISCOUNTID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    ) values (
        @DISCOUNTGROUPID,
        @DISCOUNTID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    )

    if @DISCOUNTITEMTYPECODE = 0
    begin

        insert into dbo.DISCOUNTGROUPDETAIL (
            ID,
            DISCOUNTGROUPID,
            DISCOUNTEDITEM,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        ) values (
            @DISCOUNTGROUPDETAILID,
            @DISCOUNTGROUPID,
            1,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

        insert into dbo.DISCOUNTGROUPDETAILPROGRAM (
            ID,
            PROGRAMID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        ) values (
            @DISCOUNTGROUPDETAILID,
            @PROGRAMID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )
    end

    if (@DISCOUNTTYPECODE = 1) and (@QUALIFYINGITEMTYPECODE = 0) --is a BOGO

    begin
        declare @UNDISCOUNTEDGROUPDETAILID uniqueidentifier;
        set @UNDISCOUNTEDGROUPDETAILID = newid();

        insert into dbo.DISCOUNTGROUPDETAIL (
            ID,
            DISCOUNTGROUPID,
            DISCOUNTEDITEM,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        ) values (
            @UNDISCOUNTEDGROUPDETAILID,
            @DISCOUNTGROUPID,
            0,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

        insert into dbo.DISCOUNTGROUPDETAILPROGRAM (
            ID,
            PROGRAMID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        ) values (
            @UNDISCOUNTEDGROUPDETAILID,
            @PROGRAMID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

    end

    -- Update the DISCOUNT record to invalidate the cache for sales.

    update dbo.DISCOUNT
    set CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = getdate()
    where ID = @DISCOUNTID;

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0