USP_DATAFORMTEMPLATE_ADD_CUSTOMFORMRULEACTION_BULK

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CUSTOMFORMRULEACTION_BULK
(
  @ID uniqueidentifier = null output, 
  @CHANGEAGENTID uniqueidentifier = null,
  @FORMID uniqueidentifier,
    @RULES xml = ''
)
as

set nocount on;

if @ID is null
    set @ID = newid()

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

--Pull the rule actions out into a table variable
declare @RULEACTIONSTABLE table(RULEACTIONID uniqueidentifier, RULEACTIONFIELDID uniqueidentifier, RULEACTIONCOMPARISONCODE tinyint, RULEACTIONVALUE1 nvarchar(255),
                                RULEACTIONVALUE2 nvarchar(255), RULETARGETS xml)
insert into @RULEACTIONSTABLE
select 
R.I.value('RULEACTIONID[1]', 'uniqueidentifier'),
R.I.value('RULEACTIONFIELDID[1]', 'uniqueidentifier'),
R.I.value('RULEACTIONCOMPARISONCODE[1]', 'tinyint'),
R.I.value('RULEACTIONVALUE1[1]', 'nvarchar(255)'),
R.I.value('RULEACTIONVALUE2[1]', 'nvarchar(255)'),
R.I.query('./RULETARGETS')
from @RULES.nodes('/RULES/ITEM') R(I)

--Update changes to existing rule actions
update dbo.CUSTOMFORMRULEACTION
set
  RULEACTIONFIELDID = RAT.RULEACTIONFIELDID,
  RULEACTIONCOMPARISONCODE = RAT.RULEACTIONCOMPARISONCODE,
  RULEACTIONVALUE1 = RAT.RULEACTIONVALUE1,
  RULEACTIONVALUE2 = RAT.RULEACTIONVALUE2,
  CHANGEDBYID = @CHANGEAGENTID,
  DATECHANGED = @CURRENTDATE
from dbo.CUSTOMFORMRULEACTION RA
inner join @RULEACTIONSTABLE RAT on RA.ID = RAT.RULEACTIONID

--Add new rule actions
insert dbo.CUSTOMFORMRULEACTION (ID, FORMID, RULEACTIONFIELDID, RULEACTIONCOMPARISONCODE, RULEACTIONVALUE1, RULEACTIONVALUE2, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select RAT.RULEACTIONID, @FORMID, RAT.RULEACTIONFIELDID, RAT.RULEACTIONCOMPARISONCODE, RAT.RULEACTIONVALUE1, RAT.RULEACTIONVALUE2, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @RULEACTIONSTABLE RAT
left outer join dbo.CUSTOMFORMRULEACTION RA on RAT.RULEACTIONID = RA.ID
where RA.ID is null

--Pull the rule targets out into a table variable
declare @RULETARGETSTABLE table(RULETARGETID uniqueidentifier, RULEACTIONID uniqueidentifier, RULETARGETFIELDID uniqueidentifier, RULETARGETACTIONCODE tinyint)
insert into @RULETARGETSTABLE
select
RT.I.value('RULETARGETID[1]', 'uniqueidentifier'),
RULEACTIONID,
RT.I.value('RULETARGETFIELDID[1]', 'uniqueidentifier'),
RT.I.value('RULETARGETACTIONCODE[1]', 'tinyint')
from @RULEACTIONSTABLE
cross apply RULETARGETS.nodes('/RULETARGETS/ITEM') RT(I)

--Update changes to existing rule targets
update dbo.CUSTOMFORMRULETARGET
set
  RULEACTIONID = RTT.RULEACTIONID,
  RULETARGETFIELDID = RTT.RULETARGETFIELDID,
  RULETARGETACTIONCODE = RTT.RULETARGETACTIONCODE,
  CHANGEDBYID = @CHANGEAGENTID,
  DATECHANGED = @CURRENTDATE
from dbo.CUSTOMFORMRULETARGET RT
inner join @RULETARGETSTABLE RTT on RT.ID = RTT.RULETARGETID

--Add new rule targets
insert dbo.CUSTOMFORMRULETARGET (ID, RULEACTIONID, RULETARGETFIELDID, RULETARGETACTIONCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select RTT.RULETARGETID, RTT.RULEACTIONID, RTT.RULETARGETFIELDID, RTT.RULETARGETACTIONCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @RULETARGETSTABLE RTT
left outer join dbo.CUSTOMFORMRULETARGET RT on RTT.RULETARGETID = RT.ID
where RT.ID is null

----Delete old rules for this form
delete from dbo.CUSTOMFORMRULEACTION
where FORMID = @FORMID and ID not in (select RULEACTIONID from @RULEACTIONSTABLE) and CUSTOMFORMFEEID is null

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0