USP_CUSTOMFORMDASHBOARD_GETADMISSIONS

CREATE procedure dbo.USP_CUSTOMFORMDASHBOARD_GETADMISSIONS
(
  @GROUPBY int,
  @GRADEYEARMODECODE int
)
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

  select
    T.ACADEMICYEARID,
    T.GRADELEVELVALUE,
    T.STATUS,
    COUNT(T.EntryID) as COUNT
  from (
    select
      FormEntry.EntryID,
      CUSTOMFORMDASHBOARDFORMENTRY.ACADEMICYEARID,
      case when @GROUPBY = 0
        then
          case when @GRADEYEARMODECODE = 0
              --grade level apply

            then CUSTOMFORMDASHBOARDFORMENTRY.GRADELEVELAPPLY
              --current grade level

            else CUSTOMFORMDASHBOARDFORMENTRY.CURRENTGRADELEVEL
          end      
        else null
      end as GRADELEVELVALUE,
      StatusAttribute.AttributeType as [STATUS]
    from dbo.CustomFormEntry FormEntry
      inner join dbo.CustomFormEntryAttribute StatusAttribute on FormEntry.EntryID = statusAttribute.EntryID
      inner join dbo.CUSTOMFORMDASHBOARDFORMENTRY on FormEntry.EntryID = CUSTOMFORMDASHBOARDFORMENTRY.CUSTOMFORMENTRYID
    where FormEntry.EntryState = statusAttribute.AttributeType
      and CUSTOMFORMDASHBOARDFORMENTRY.ACADEMICYEARID is not null
      and CUSTOMFORMDASHBOARDFORMENTRY.ACADEMICYEARID != ''
  ) T
  group by T.ACADEMICYEARID, T.GRADELEVELVALUE, T.STATUS
end