USP_CUSTOMFORMDASHBOARD_GETADMISSIONDETAILS

CREATE procedure dbo.USP_CUSTOMFORMDASHBOARD_GETADMISSIONDETAILS
(
  @GROUPBY int,
  @GRADEYEARMODECODE int, -- 0 = grade level apply, 1 = current grade level

  @ACADEMICYEARIDS xml,   -- XML list of integer values from the EA7ACADEMICYEAR table, representing school + year

  @GRADELEVEL nvarchar(100) = null, -- grade level name if GRADEYEARMODECODE = 0

  @STATUS int = -1 -- 1 = Started, 2 = Completed

)
as
begin
  set nocount on;

  -- MaryR 10.11.2012 Haven't figured out why yet, but we have sporadic cases where the form that has just been submitted is not updated in cache. This isn't

  --   a huge problem on the Data tab since it mainly means that one form is going to show up as Anonymous, but it is a real problem on the Dashboards where 

  --   it can put the totals off. So I'm putting all of these calls back in. Since the trigger is now handling the bulk of the work, these other calls should

  --   just be handling stragglers, and the sproc is optimized to only process records that need it, so performance shouldn't be an issue.

  -- MaryR 06.25.2012 Bug #216946  Hitting the UpdateCache USP too infrequently caused timeouts that prevented the Data tab from loading, so caching is now 

  --   handled as entries are added and updated (TR_CUSTOMFORMENTRY_INSERT_UPDATE) instead of here where we don't know how many records it might have to process.

  -- Ensure the cache is up-to-date

  exec dbo.USP_CUSTOMFORMDASHBOARD_UPDATEFORMENTRYCACHE

  declare @AcademicYears table
  (
    ID int
  )
  insert into @AcademicYears (ID)
  select
    T.c.value('.','int') as ID
  from @ACADEMICYEARIDS.nodes('/SET/ID') T(c)

  select
    FormEntry.EntryID as FormEntryID,
    CUSTOMFORMDASHBOARDFORMENTRY.STUDENTFIRSTNAME + ' ' + CUSTOMFORMDASHBOARDFORMENTRY.STUDENTLASTNAME as NAME,
    CLIENTUSERS.UserName
  from dbo.CustomFormEntry FormEntry
    inner join dbo.CustomFormEntryAttribute StatusAttribute on FormEntry.EntryID = statusAttribute.EntryID
    inner join dbo.CUSTOMFORMDASHBOARDFORMENTRY on FormEntry.EntryID = CUSTOMFORMDASHBOARDFORMENTRY.CUSTOMFORMENTRYID
    inner join @AcademicYears AY on CUSTOMFORMDASHBOARDFORMENTRY.ACADEMICYEARID = AY.ID
    left join dbo.CLIENTUSERS on FormEntry.ClientUserID = CLIENTUSERS.ID
  where FormEntry.EntryState = statusAttribute.AttributeType
    and StatusAttribute.AttributeType = @STATUS
    and
    (
      (
        @GROUPBY = 0
        and (
          (@GRADEYEARMODECODE = 0 and CUSTOMFORMDASHBOARDFORMENTRY.GRADELEVELAPPLY = @GRADELEVEL)
          or
          (@GRADEYEARMODECODE = 1 and CUSTOMFORMDASHBOARDFORMENTRY.CURRENTGRADELEVEL = @GRADELEVEL)
        )
      )
      or
      (
        @GROUPBY = 1
      )
    )
  order by CUSTOMFORMDASHBOARDFORMENTRY.STUDENTLASTNAME, CUSTOMFORMDASHBOARDFORMENTRY.STUDENTFIRSTNAME
end