spCustomFormElement_GetFormByGuidOrID

CREATE PROCEDURE [dbo].[spCustomFormElement_GetFormByGuidOrID]
( 
  @FormId int, 
  @FormGuid uniqueidentifier
)
AS
BEGIN
  SET @FormId = nullif(@FormId, dbo.fnGetIntMinValue())
  SET @FormGuid = nullif(@FormGuid, dbo.fnGetGuidMinValue())

  IF @FormId IS NULL 
      SET @FormID = (select ID from [dbo].[CustomFormElement] where Guid = @FormGuid)

  IF @FormGuid IS NULL
      SET @FormGuid = (select Guid from [dbo].[CustomFormElement] where ID = @FormID)

  -- Form Properties
  SELECT
      A.[ID],
      [TypeID],
      [SubTypeID],
      [Title],
      [Label],
      [Description],
      [Instruction],
      A.[Guid],
      B.[SiteFoldersID] as 'FolderID',
      C.[DataSource] as 'DataSource',
      [OwnerID],
      [DateCreated],
      [IsComplete]
  FROM
      [dbo].[CustomFormElement] A
  INNER JOIN 
      [dbo].[CustomFormDataSource] C
      ON C.FormGuid = A.Guid
  LEFT JOIN 
      [dbo].[CustomFormFolder] B 
      ON B.CustomFormElementID = A.ID
  WHERE
      ((A.ID = @FormId) or (A.Guid = @FormGuid)) AND (A.TypeID = 1)


  -- Form Payment Settings
  select 
      FormGUID
      , AllowDirectDebit
      , AllowPledge
      , AllowWaive
      , RequireFirstName
      , RequireTitle
      , RequirePhone
      , MerchantAccountID
      , WaiveCode
      , AllowPaymentCollection
      , DisplayReCAPTCHA
      , PaymentPartPageID
      , AcknowledgementBlock
  from dbo.CustomFormPaymentSetting where FormGUID = @FormGUID


  -- Form Other Settings
  select 
      [FormGUID]                    
      , [CustomConfirmationPage]
      , [LoginProperties]
      , [OAProperties]
      , [ConfirmationEmailBody]
      , [ConfirmationEmailFromAddress]
      , [ConfirmationEmailFromName]
      , [ConfirmationEmailName]
      , [ConfirmationEmailSubject]
      , [ConfirmationEmailTemplateID]
      , [LanguageData]
      , [Disabled]
      , [ConfirmationEmailReplyAddress]
      , [ConfirmationEmailForwardDSN]
      , [ConfirmationEmailForwardDSNAddress]
      , [ClientSitesID]
      , [NotificationProperties]
      , [SendNotification]
      , [NotificationEmailTemplateID]                  
  from dbo.CustomFormSettings where FormGUID = @FormGUID


  -- Form Mapping Schema
  select
      [FormGuid],
      [MappingSchemaGuid],
      [MappingSchema]
  from
      dbo.CustomFormMappingSchema
  where
      [FormGuid] = @FormGuid

  --Form Rules
  select
      CUSTOMFORMRULEACTION.ID,
      NUMID,
      FORMID,
      RULEACTIONFIELDID,
      RULEACTIONCOMPARISONCODE,
      RULEACTIONVALUE1,
      RULEACTIONVALUE2,
      RULETARGETACTIONCODE,
      CUSTOMFORMRULETARGET.ID as RULETARGETID,
      RULETARGETFIELDID
  from
      dbo.CUSTOMFORMRULEACTION
  left join
      dbo.CUSTOMFORMRULETARGET on CUSTOMFORMRULEACTION.ID = CUSTOMFORMRULETARGET.RULEACTIONID
  where
      FORMID = @FormGuid and CUSTOMFORMFEEID is null

  --Form Fees
  select
      CUSTOMFORMFEE.ID,
      CUSTOMFORMFEE.FORMID,
      CUSTOMFORMFEE.NAME,
      CUSTOMFORMFEE.AMOUNT,
      CUSTOMFORMFEE.CRITERIACODE,
      CUSTOMFORMRULEACTION.ID as RULEACTIONID,
      CUSTOMFORMRULEACTION.NUMID,
      CUSTOMFORMRULEACTION.RULEACTIONFIELDID,
      CUSTOMFORMRULEACTION.RULEACTIONCOMPARISONCODE,
      CUSTOMFORMRULEACTION.RULEACTIONVALUE1,
      CUSTOMFORMRULEACTION.RULEACTIONVALUE2
  from
      dbo.CUSTOMFORMFEE
  left join
      dbo.CUSTOMFORMRULEACTION on CUSTOMFORMFEE.ID = CUSTOMFORMRULEACTION.CUSTOMFORMFEEID
  where
      CUSTOMFORMFEE.FORMID = @FormGuid
END