USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONLEVELPAGEDATA

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONLEVELPAGEDATA (
  @ID uniqueidentifier
  ,@CURRENTAPPUSERID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@NAME nvarchar(100) = null output
  ,@DESIGNATIONLEVELNAME nvarchar(100) = null output
  ,@HASRECIPIENTS bit = null output
  ,@HASDONORINFORMATION bit = null output
  ,@HASFINANCIALINFORMATION bit = null output
  ,@DESIGNATIONID uniqueidentifier = null output
  ,@HASKPIRIGHTS bit = null output
  ,@STEWARDSHIPPACKAGEPROCESSID uniqueidentifier = null output
  ,@GIFTFEE_ENABLED bit = null output
  ,@DESIGNATIONLEVELGIFTFEE_ENABLED bit = null output
  ,@GIFTFEES_AREDEFAULT bit = null output
  ,@HASGLMAPPING bit = null output
  ,@ATTRIBUTEDEFINED bit = null output
  ,@ISACCEPTINGFUNDS bit = null output
  ,@CHANGECURRENCY_ENABLED bit = null output
  )
as
begin
  set nocount on;

  select @DATALOADED = 1
    ,@NAME = DESIGNATIONLEVEL.name
    ,@DESIGNATIONLEVELNAME = FPT.DESCRIPTION
    ,@HASRECIPIENTS = FPT.HASRECIPIENTS
    ,@HASDONORINFORMATION = FPT.HASDONORINFORMATION
    ,@HASFINANCIALINFORMATION = FPT.HASFINANCIALINFORMATION
    ,@DESIGNATIONID = (
      select ID
      from dbo.DESIGNATION
      where DESIGNATIONLEVEL1ID = @ID
        and DESIGNATIONLEVEL2ID is null
      )
    ,@HASKPIRIGHTS = dbo.UFN_SECURITY_APPUSER_GRANTED_KPI(@CURRENTAPPUSERID, '32CC2B6B-73CF-43EC-A966-B86362F3F1AB')
    --GUID is ID of FP Goal KPI Type
    ,@STEWARDSHIPPACKAGEPROCESSID = DESIGNATIONLEVEL.STEWARDSHIPPACKAGEPROCESSID
    ,@GIFTFEE_ENABLED = dbo.UFN_GIFTFEE_ENABLED()
    ,@DESIGNATIONLEVELGIFTFEE_ENABLED = dbo.UFN_DESIGNATIONLEVELGIFTFEE_ENABLED(@ID)
    ,@GIFTFEES_AREDEFAULT = 1
    ,@ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('Fundraising Purpose', @CURRENTAPPUSERID)
    ,@ISACCEPTINGFUNDS = DESIGNATIONLEVEL.ISACCEPTINGFUNDS
  from dbo.DESIGNATIONLEVEL
  inner join dbo.DESIGNATIONLEVELTYPE FPT on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = FPT.ID
  where DESIGNATIONLEVEL.ID = @ID

  if @DATALOADED = 1
  begin
    if @HASRECIPIENTS = 0
      set @HASRECIPIENTS = case 
          when exists (
              select top 1 1
              from dbo.DESIGNATIONLEVELRECIPIENT DLR
              where DLR.DESIGNATIONLEVELID = @ID
              )
            then 1
          else 0
          end

    if @HASDONORINFORMATION = 0
      set @HASDONORINFORMATION = case 
          when exists (
              select top 1 1
              from dbo.FINANCIALTRANSACTION
              inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
              inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
              inner join dbo.DESIGNATION D on D.ID = REVENUESPLIT_EXT.DESIGNATIONID
              where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
                and (
                  D.DESIGNATIONLEVEL1ID = @ID
                  or D.DESIGNATIONLEVEL2ID = @ID
                  or D.DESIGNATIONLEVEL3ID = @ID
                  or D.DESIGNATIONLEVEL4ID = @ID
                  or D.DESIGNATIONLEVEL5ID = @ID
                  )
                and (
                  FINANCIALTRANSACTION.TYPECODE in (
                    1 
                    ,3
                    ,4
                    ,6
                    ,7
                    ,8
                    )
                  or (
                    FINANCIALTRANSACTION.TYPECODE = 0
                    and REVENUESPLIT_EXT.APPLICATIONCODE in (
                      0
                      ,1
                      ,3
                      ,4
                      )
                    )
                  or (
                    FINANCIALTRANSACTION.TYPECODE = 5
                    and REVENUESPLIT_EXT.APPLICATIONCODE = 0
                    )
                  )
              )
            then 1
          else 0
          end

    if @HASFINANCIALINFORMATION = 0
      set @HASFINANCIALINFORMATION = case 
          when exists (
              select top 1 1
              from dbo.DESIGNATIONLEVELFINANCIALINFO DLF
              where DLF.DESIGNATIONLEVELID = @ID
              )
            then 1
          else 0
          end
  end

  if exists (
      select top 1 1
      from DESIGNATIONLEVELGIFTFEEOPTION
      where ID = @ID
      )
    set @GIFTFEES_AREDEFAULT = 0;

  if exists (
      select top 1 1
      from DESIGNATIONLEVELGIFTFEEOVERRIDE
      where ID = @ID
        and OVERRIDEGIFTFEESTRUCTURE = 1
      )
    set @GIFTFEES_AREDEFAULT = 0;

  --if exists(select ID from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTTABLESAVAILABLEFORSEGMENTID = '4C4A084F-597C-4CDE-BFB6-F1730397A01A')
  --set @HASGLMAPPING = 1
  if (
      select count(ID)
      from dbo.PDACCOUNTSTRUCTURE
      where PDACCOUNTTABLESAVAILABLEFORSEGMENTID = '4C4A084F-597C-4CDE-BFB6-F1730397A01A'
      ) = 1
    set @HASGLMAPPING = 1

  declare @DESIGNATIONLEVELKPIEXISTS bit = 0;

 WITH XMLNAMESPACES ('bb_appfx_dataforms' as DFI) 
  select @DESIGNATIONLEVELKPIEXISTS = 1
  from dbo.KPIINSTANCE
  where (
      KPIINSTANCE.KPICATALOGID = '32CC2B6B-73CF-43EC-A966-B86362F3F1AB'
      or KPIINSTANCE.KPICATALOGID = '9CD205B2-8D1B-4A50-A8D4-FAC4E5A5FF66'
      )
    and KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="DESIGNATIONLEVELID"]/DFI:Value)[1]', 'varchar(36)') = cast(@ID as varchar(36))

  declare @DESIGNATIONKPIEXISTS bit = 0;

 WITH XMLNAMESPACES ('bb_appfx_dataforms' as DFI) 
  select @DESIGNATIONKPIEXISTS = 1
  from dbo.KPIINSTANCE
  where (
      KPIINSTANCE.KPICATALOGID = '574D999C-7870-410E-9E12-6CAF955C2234'
      or KPIINSTANCE.KPICATALOGID = 'A7463D6E-87B2-4F0F-BF44-C5D29BA77D5E'
      )
    and KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="DESIGNATIONID"]/DFI:Value)[1]', 'varchar(36)') in (
      select cast(DESIGNATION.ID as varchar(36))
      from dbo.DESIGNATION
      where (
          DESIGNATION.DESIGNATIONLEVEL1ID = @ID
          and DESIGNATION.DESIGNATIONLEVEL2ID is null
          )
        or (
          DESIGNATION.DESIGNATIONLEVEL2ID = @ID
          and DESIGNATION.DESIGNATIONLEVEL3ID is null
          )
        or (
          DESIGNATION.DESIGNATIONLEVEL3ID = @ID
          and DESIGNATION.DESIGNATIONLEVEL4ID is null
          )
        or (
          DESIGNATION.DESIGNATIONLEVEL4ID = @ID
          and DESIGNATION.DESIGNATIONLEVEL5ID is null
          )
        or DESIGNATION.DESIGNATIONLEVEL5ID = @ID
      )

  if (
      select COUNT(ID)
      from dbo.DESIGNATION
      where (
          DESIGNATION.DESIGNATIONLEVEL1ID = @ID
          or DESIGNATION.DESIGNATIONLEVEL2ID = @ID
          or DESIGNATION.DESIGNATIONLEVEL3ID = @ID
          or DESIGNATION.DESIGNATIONLEVEL4ID = @ID
          or DESIGNATION.DESIGNATIONLEVEL5ID = @ID
          )
      ) < 2
    and not exists (
      select top 1 1
      from dbo.BATCHDESIGNATIONLEVELFINANCIALINFO
      where DESIGNATIONLEVELID = @ID
      )
    and @DESIGNATIONLEVELKPIEXISTS = 0
    and @DESIGNATIONKPIEXISTS = 0
    set @CHANGECURRENCY_ENABLED = 1

  return 0
end