USP_DATAFORMTEMPLATE_ADD_CUSTOMFORMPAYMENTSETTING

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CUSTOMFORMPAYMENTSETTING
(
    @ID uniqueidentifier = null output,
    @FORMGUID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @ALLOWDIRECTDEBIT            bit,
    @ALLOWPLEDGE                bit,
    @REQUIREFIRSTNAME            bit,
    @REQUIRETITLE                bit,
    @REQUIREPHONE                bit,
    @MERCHANTACCOUNTID            int,
    @ALLOWWAIVE                    bit,
    @WAIVECODE                    nvarchar(1000) = '',
    @ALLOWPAYMENTCOLLECTION        bit,
    @DISPLAYRECAPTCHA            bit,
    @PAYMENTPARTPAGEID            int = null,
    @ACKNOWLEDGEMENTBLOCK        nvarchar(max) = null,
    @FEES                        xml = null
)
as

set nocount on;

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

    UPDATE dbo.CustomFormPaymentSetting
    SET
        FormGUID = @FormGUID,
        AllowDirectDebit = @AllowDirectDebit,
        AllowPledge = @AllowPledge,
        RequireFirstName = @RequireFirstName,
        RequireTitle = @RequireTitle,
        RequirePhone = @RequirePhone,
        MerchantAccountID = @MerchantAccountID,
        AllowWaive = @AllowWaive,
        WaiveCode = @WaiveCode,
        AllowPaymentCollection = @AllowPaymentCollection,
        DisplayReCAPTCHA = @DisplayReCAPTCHA,
        PaymentPartPageID = @PaymentPartPageID,
        AcknowledgementBlock = @AcknowledgementBlock
    WHERE FormGUID = @FormGUID

    IF @@ROWCOUNT = 0 
    BEGIN
        INSERT INTO dbo.CustomFormPaymentSetting
        (
            FormGUID,
            AllowDirectDebit,
            AllowPledge,
            RequireFirstName,
            RequireTitle,
            RequirePhone,
            MerchantAccountID,
            AllowWaive,
            WaiveCode,
            AllowPaymentCollection,
            DisplayReCAPTCHA,
            PaymentPartPageID,
            AcknowledgementBlock
        )
        values
        (
            @FormGUID,
            @AllowDirectDebit,
            @AllowPledge,
            @RequireFirstName,
            @RequireTitle,
            @RequirePhone,
            @MerchantAccountID,
            @AllowWaive,
            @WaiveCode,
            @AllowPaymentCollection,
            @DisplayReCAPTCHA,
            @PaymentPartPageID,
            @AcknowledgementBlock
        )
    END

    --Pull the fees out into a table variable
    declare @FEESTABLE table(FEEID uniqueidentifier, NAME nvarchar(60), AMOUNT money, CRITERIACODE tinyint, RULES xml)
    insert into @FEESTABLE(FEEID, NAME, AMOUNT, CRITERIACODE, RULES)
    select 
    F.I.value('FEEID[1]', 'uniqueidentifier'),
    F.I.value('NAME[1]', 'nvarchar(60)'),
    F.I.value('AMOUNT[1]', 'money'),
    F.I.value('CRITERIACODE[1]', 'tinyint'),
    F.I.query('./RULES')
    from @FEES.nodes('/FEES/ITEM') F(I)

    --Pull the rules out into a table variable
    declare @RULEACTIONSTABLE table(RULEACTIONID uniqueidentifier, RULEACTIONFIELDID uniqueidentifier, RULEACTIONCOMPARISONCODE tinyint, RULEACTIONVALUE1 nvarchar(255), RULEACTIONVALUE2 nvarchar(255), CUSTOMFORMFEEID uniqueidentifier)
    insert into @RULEACTIONSTABLE(RULEACTIONID, RULEACTIONFIELDID, RULEACTIONCOMPARISONCODE, RULEACTIONVALUE1, RULEACTIONVALUE2, CUSTOMFORMFEEID)
    select
    RA.I.value('RULEACTIONID[1]', 'uniqueidentifier'),
    RA.I.value('RULEACTIONFIELDID[1]', 'uniqueidentifier'),
    RA.I.value('RULEACTIONCOMPARISONCODE[1]', 'tinyint'),
    RA.I.value('RULEACTIONVALUE1[1]', 'nvarchar(255)'),
    RA.I.value('RULEACTIONVALUE2[1]', 'nvarchar(255)'),
    FEEID
    from @FEESTABLE
    cross apply RULES.nodes('/RULES/ITEM') RA(I)

    --Cache current context information 
    declare @CONTEXTCACHE varbinary(128); 
    set @CONTEXTCACHE = CONTEXT_INFO(); 
    set CONTEXT_INFO @CHANGEAGENTID; 

    --merge fees
    merge dbo.CUSTOMFORMFEE as target
        using (select FEEID, NAME, AMOUNT, CRITERIACODE from @FEESTABLE) 
            as source (ID, NAME, AMOUNT, CRITERIACODE)
        on (target.ID = source.ID)
        when not matched by source and target.FORMID = @FORMGUID then DELETE
        when matched and (target.NAME <> source.NAME or target.AMOUNT <> source.AMOUNT or target.CRITERIACODE <> source.CRITERIACODE) then
            update set
                NAME = source.NAME,
                AMOUNT = source.AMOUNT,
                CRITERIACODE = source.CRITERIACODE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        when not matched then
            insert (ID, FORMID, NAME, AMOUNT, CRITERIACODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (source.ID, @FORMGUID, source.NAME, source.AMOUNT, source.CRITERIACODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    --merge rules
    merge dbo.CUSTOMFORMRULEACTION as target
        using (select RULEACTIONID, RULEACTIONFIELDID, RULEACTIONCOMPARISONCODE, RULEACTIONVALUE1, RULEACTIONVALUE2, CUSTOMFORMFEEID from @RULEACTIONSTABLE) 
            as source (ID, RULEACTIONFIELDID, RULEACTIONCOMPARISONCODE, RULEACTIONVALUE1, RULEACTIONVALUE2, CUSTOMFORMFEEID)
        on (target.ID = source.ID)
        when not matched by source and target.CUSTOMFORMFEEID is not null and target.FORMID = @FORMGUID then DELETE
        when matched and (target.RULEACTIONFIELDID <> source.RULEACTIONFIELDID or target.RULEACTIONCOMPARISONCODE <> source.RULEACTIONCOMPARISONCODE or target.RULEACTIONVALUE1 <> source.RULEACTIONVALUE1 or target.RULEACTIONVALUE2 <> source.RULEACTIONVALUE2) then
            update set
                RULEACTIONFIELDID = source.RULEACTIONFIELDID,
                RULEACTIONCOMPARISONCODE = source.RULEACTIONCOMPARISONCODE,
                RULEACTIONVALUE1 = source.RULEACTIONVALUE1,
                RULEACTIONVALUE2 = source.RULEACTIONVALUE2,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        when not matched then
            insert (ID, FORMID, RULEACTIONFIELDID, RULEACTIONCOMPARISONCODE, RULEACTIONVALUE1, RULEACTIONVALUE2, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, CUSTOMFORMFEEID)
            values (source.ID, @FORMGUID, source.RULEACTIONFIELDID, source.RULEACTIONCOMPARISONCODE, source.RULEACTIONVALUE1, source.RULEACTIONVALUE2, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, source.CUSTOMFORMFEEID);


    if not @CONTEXTCACHE is null 
        set CONTEXT_INFO @CONTEXTCACHE;    

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0