SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPHomeScreenAlertCounts')
	DROP PROCEDURE LD0SPHomeScreenAlertCounts
GO

CREATE PROCEDURE LD0SPHomeScreenAlertCounts  
 @CaseWorkersXml varchar(MAX),  
 @RestrictedFromDate varchar(8),  
 @AlertTypes varchar(MAX),
 @KantaTermSystemId varchar(MAX)    
AS  
BEGIN  
 SET NOCOUNT ON  
  
 DECLARE @CountTotalUnhandledApplications int = 0;  
 DECLARE @CountOrganizationUnhandledApplications int = 0;  
 DECLARE @CountUrgentUnhandledApplications int = 0;  
 DECLARE @CountLateUnhandledApplications int = 0;  
  
 DECLARE @CountTotalUnhandledAnnouncements int = 0;  
 DECLARE @CountOrganizationUnhandledAnnouncements int = 0;  
 DECLARE @CountUrgentUnhandledAnnouncements int = 0;  
 DECLARE @CountLateUnhandledAnnouncements int = 0;  
  
 DECLARE @CountTotalOtherActualizations int = 0;  
 DECLARE @CountOrganizationOtherActualizations int = 0;  
 DECLARE @CountUrgentOtherActualizations int = 0;  
 DECLARE @CountLateOtherActualizations int = 0;  
   
 DECLARE @CountUnhandledTasks int = 0;  
 DECLARE @CountUnhandledDecisions int = 0;  
 DECLARE @CountUnhandledEfficaDocuments int = 0;  
 DECLARE @CountUnhandledEhrDocuments int = 0;  
   
 DECLARE @FetchApplications int  
 DECLARE @FetchAnnouncements int  
 DECLARE @FetchActualizations int  
 DECLARE @FetchTasks int  
 DECLARE @FetchDecisions int  
 DECLARE @FetchEfficaDocuments int  
 DECLARE @FetchEhrDocuments int  
   
  
 IF @AlertTypes LIKE '%Applications%'  
 BEGIN  
  SET @FetchApplications = 1;  
 END  
   
 IF @AlertTypes LIKE '%Announcements%'  
 BEGIN  
  SET @FetchAnnouncements = 1;  
 END  
   
 IF @AlertTypes LIKE '%Actualizations%'  
 BEGIN  
  SET @FetchActualizations = 1;  
 END  
   
 IF @AlertTypes LIKE '%Tasks%'  
 BEGIN  
  SET @FetchTasks = 1;  
 END  
   
 IF @AlertTypes LIKE '%Decisions%'  
 BEGIN  
  SET @FetchDecisions = 1;  
 END  
   
 IF @AlertTypes LIKE '%EfficaDocuments%'  
 BEGIN  
  SET @FetchEfficaDocuments = 1;  
 END  
   
 IF @AlertTypes LIKE '%EhrDocuments%'  
 BEGIN  
  SET @FetchEhrDocuments = 1;  
 END  
  
 create table #Count (  
  CountValue INT NULL  
 );  
  
 create table #HSAC_Aktualserings (  
  AKTUALNR DECIMAL(9,0) NULL,  
  DATUM VARCHAR(8) NULL,  
  URGENCY DECIMAL(1,0) NULL,  
  PROCDATUM VARCHAR(8) NULL,  
  USEWORKINGDAYS DECIMAL(1,0) NULL,  
  ADATUM VARCHAR(8) NULL,  
  NEXTMONTH DECIMAL(1,0) NULL,  
  AVBFROMDAT VARCHAR(8) NULL,  
  AVBTOMDAT VARCHAR(8) NULL,  
  DEFAULTHANDLINGTIME DECIMAL(9,0) NULL,  
  ACTUALIZATIONSOURCE INT  
 );  
  
 DECLARE @LastAKTUALNR DECIMAL(9,0);  
 DECLARE @CurrentAKTUALNR DECIMAL(9,0);  
 DECLARE @BeginDate DateTime;  
 DECLARE @DATUM varchar(10);  
 DECLARE @Today DateTime = getdate();  
 DECLARE @DecisionDate DateTime  
 DECLARE @UseOnlyWorkingDays int;  
    DECLARE @ConcernsNextMonth int;  
 DECLARE @DefaultHandlingTime int;  
 DECLARE @InterruptionStartDate varchar(10);  
 DECLARE @InterruptionEndDate varchar(10);  
 DECLARE @FirstDate DateTime;  
 DECLARE @SevenDaysPriorEndOfMonth DateTime  
 DECLARE @ActualProcessingTime int  
  
 DECLARE @AKTUALTYP INT;  
 DECLARE @XMLlist XML; SELECT @XMLList = @CaseWorkersXml;  
 DECLARE @CaseWorkersTable AS TABLE ([UserName][varchar](100) NOT NULL);  
 INSERT INTO @CaseWorkersTable SELECT x.y.value('.','nvarchar(100)') AS UserNames FROM @XMLList.nodes('/list/user') AS x(y);  
 DECLARE @TeamIdTable AS TABLE ([TeamId][varchar](100) NOT NULL);  
 INSERT INTO @TeamIdTable SELECT x.y.value('.','nvarchar(100)') AS UserNames FROM @XMLList.nodes('/teamId') AS x(y);  
 DECLARE @TeamId INT = 0;  
 SELECT TOP 1 @TeamId = TeamId FROM @TeamIdTable;  
   
 -- Fill @OrganizationsTable with distinct organization unit ids for given caseworkers.  
 DECLARE @OrganizationsTable TABLE ([ORGENHNR][varchar](10) NOT NULL);  
 IF @TeamId = 0  
 BEGIN  
  insert into @OrganizationsTable  
  select distinct M.ORGENHNR  
  FROM LD0VAACASEWORKERORGMEMBER M  
  join @CaseWorkersTable t on M.HAND = t.UserName  
  WHERE M.FOMDAT <= CONVERT(VARCHAR,GETDATE(),112)   
    AND M.TOMDAT >= CONVERT(VARCHAR,GETDATE(),112);  
 END;  
  
 --Unhandled Applications  
 IF @FetchApplications = 1  
 BEGIN  
  INSERT INTO #HSAC_Aktualserings  
  exec OnPremFI_LD0.LD0SPListApplicationsByOrganization @CaseWorkersXml, @RestrictedFromDate, @TeamId  
  SELECT @CountOrganizationUnhandledApplications = COUNT(*) FROM #HSAC_Aktualserings;  
  
  INSERT INTO #HSAC_Aktualserings  
  exec OnPremFI_LD0.LD0SPListApplicationsByCaseworker @CaseWorkersXml, @RestrictedFromDate  
  SELECT @CountTotalUnhandledApplications = COUNT(*) FROM #HSAC_Aktualserings;  
  SELECT @CountUrgentUnhandledApplications = COUNT(*) FROM #HSAC_Aktualserings WHERE URGENCY = 3; --EfficaDefinitions.UrgentAktualsering  
  
  SET @CountLateUnhandledApplications = 0;  
  SET @LastAKTUALNR = -1;  
  SET @CurrentAKTUALNR = 0;  
  WHILE @LastAKTUALNR <> @CurrentAKTUALNR  
  BEGIN    
   SET @LastAKTUALNR = @CurrentAKTUALNR;  
   SELECT TOP 1 @CurrentAKTUALNR = AKTUALNR,  
      @BeginDate = CONVERT(datetime, LEFT(ADATUM,4) + SUBSTRING(ADATUM,5,2) + RIGHT(ADATUM,2),112),  
      @DATUM = DATUM,  
      @UseOnlyWorkingDays = USEWORKINGDAYS, @ConcernsNextMonth = NEXTMONTH, @DefaultHandlingTime = DEFAULTHANDLINGTIME, @InterruptionStartDate = AVBFROMDAT, @InterruptionEndDate = AVBTOMDAT  
   FROM #HSAC_Aktualserings  
   WHERE AKTUALNR > @LastAKTUALNR   
   ORDER BY AKTUALNR  
  
   IF @CurrentAKTUALNR <> @LastAKTUALNR  
   BEGIN  
    --Calculations start from the next counted day after application has been received  
    SET @FirstDate = DATEADD(day, 1, @BeginDate);  
  
    --adjust start date if the application concerns next month  
    IF @ConcernsNextMonth > 0  
    BEGIN  
     --First count backwards seven days from the end of the month   
     exec OnPremFI_LD0.LD0SPGetSevenDaysPriorEndOfMonth @BeginDate = @FirstDate, @UseOnlyWorkingDays = @UseOnlyWorkingDays, @ResultDate = @SevenDaysPriorEndOfMonth OUT;  
  
     --counted start date is later than original application date...  
     IF @SevenDaysPriorEndOfMonth >= @FirstDate  
     BEGIN  
      SET @FirstDate = @SevenDaysPriorEndOfMonth  
     END  
    END  
  
    IF @DATUM IS NOT NULL AND @DATUM != '' AND ISDATE(@DATUM) = 1  
     SET @DecisionDate = CONVERT(datetime, LEFT(@DATUM,4) + SUBSTRING(@DATUM,5,2) + RIGHT(@DATUM,2),112);  
    ELSE  
     SET @DecisionDate = @Today;  
  
  
    EXEC OnPremFI_LD0.LD0SPCountDaysForProcessingTime @BeginDate = @FirstDate, @EndDate = @DecisionDate,  
               @UseOnlyWorkingDays = @UseOnlyWorkingDays,  
               @InterruptionStartDate = @InterruptionStartDate, @InterruptionEndDate = @InterruptionEndDate,  
               @Count = @ActualProcessingTime OUT;  
  
    IF (@DefaultHandlingTime != 0 AND @DefaultHandlingTime < @ActualProcessingTime)  
    BEGIN  
     SET @CountLateUnhandledApplications = @CountLateUnhandledApplications + 1;  
    END  
   END  
  END  
  
  DELETE #HSAC_Aktualserings;  
 END  
  
  
 if @FetchTasks = 1  
 BEGIN  
  --Unhandled tasks  
  SELECT @CountUnhandledTasks = COUNT(*) FROM LD0V0UNFINISHEDTASKS task  
  INNER JOIN @CaseWorkersTable t on (task.CASEWORKERID = t.UserName)  
  --ProcessOwnerType.Actualization  
  LEFT OUTER JOIN LD0V0CAKTUALSERING A ON (task.OWNERID = 0 AND task.OWNERTYPE = 1 AND A.AKTUALNR = task.OWNERID AND A.PROCDATUM = '')  
  --ProcessOwnerType.Investigation  
  LEFT OUTER JOIN LD0V0CCASEWORKERINVESTIGATIONS I ON (task.OWNERID = 0 AND task.OWNERTYPE = 2 AND I.AEVERKNR = task.OWNERID)  
  --ProcessOwnerType.Service  
  LEFT OUTER JOIN LD0V0CCASEWORKERSERVICES S ON (task.OWNERID = 0 AND task.OWNERTYPE = 3 AND S.INSNR = task.OWNERID)  
  WHERE (task.ENDDATE IS NULL OR CONVERT(VARCHAR, GETDATE(), 112) < task.ENDDATE)  
  AND task.ISCOMPLETED = 0  
  AND task.ISPROCESSACTIVE = 1  
  AND  
  (  
   task.OWNERTYPE IS NULL  
   OR  
   task.OWNERTYPE NOT IN (1, 2, 3)  
   OR  
   (  
    --ProcessOwnerType.Actualization  
    (task.OWNERTYPE = 1 AND (A.AKTUALNR IS NULL OR (A.AKTUALNR IS NOT NULL AND A.PROCDATUM = '')))  
    OR  
    --ProcessOwnerType.Investigation  
    (task.OWNERTYPE = 2 AND (I.AEVERKNR IS NULL OR (I.AEVERKNR IS NOT NULL AND (I.STOPDAT = '' OR I.STOPDAT = '99999999'))))  
    OR  
    --ProcessOwnerType.Service     
	 (task.OWNERTYPE = 3 AND (S.INSNR IS NULL OR (S.INSNR IS NOT NULL AND (S.TOMDAT = '' OR S.TOMDAT = '99999999'))))  
   )  
  );  
 END  
  
 IF @FetchDecisions = 1  
 BEGIN  
  --Unhandled Decisions   
  INSERT INTO #Count  
  EXEC OnPremFI_LD0.LD0SPGetUnfinishedDecisionsCount @CaseWorkersXml, @RestrictedFromDate  
  SELECT TOP 1 @CountUnhandledDecisions = CountValue FROM #Count  
  DELETE #Count  
 END  
   
 IF @FetchEfficaDocuments = 1  
 BEGIN  
  --Unhandled Effica documents  
  INSERT INTO #Count  
  EXEC OnPremFI_LD0.LD0SPGetUnfinishedDocumentsCount @CaseWorkersXml, @RestrictedFromDate  
  SELECT TOP 1 @CountUnhandledEfficaDocuments = CountValue FROM #Count  
  DELETE #Count  
 END  
   
 IF @FetchEhrDocuments = 1  
 BEGIN  
  --Unhandled EHR documents  
  SELECT @CountUnhandledEhrDocuments = COUNT(*) FROM LD0V1EHRWELFAREDOCUMENTDETAILS EHR  
  INNER JOIN @CaseWorkersTable t on (EHR.CASEWORKERID = t.UserName)  
  WHERE EHR.KANTATERMSYSTEMID = @KantaTermSystemId AND EHR.CompositionState NOT IN (2, 3) --EHRCompositionState.Ready = 2, EHRCompositionState.Approved = 3  
 END  
   
 --Unhandled Announcements - by organization  
 IF @FetchAnnouncements = 1  
 BEGIn  
  set @AKTUALTYP = 0;  
   
  -- Get all aktualiserings for OrganizationUnitId (A.ORGENHNR).  
  IF @TeamId = 0  
  BEGIN  
   INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
   SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
   ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
   FROM LD0V0H A  
   INNER JOIN LD0P0A P ON A.PNR = P.PNR  
   INNER JOIN LD0V0Q01 T ON T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL  
   INNER JOIN @OrganizationsTable O ON A.ORGENHNR = O.ORGENHNR  
   WHERE A.AKTUALTYP = T.XNUM1 AND A.AKTUALTYP = @AKTUALTYP   
   AND A.ADATUM >= @RestrictedFromDate  
   AND A.HAND = '' AND A.HAND2 = ''  
   AND A.PROCDATUM = ''  
   AND A.BESLUTNR = 0;  
  END;  
   
  -- Get all aktualiserings for CoOrganizationUnitId (A.ORGENHNR2).  
  IF @TeamId = 0  
  BEGIN  
   INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
   SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
   ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
   FROM LD0V0H A  
   INNER JOIN LD0P0A P ON A.PNR = P.PNR  
   INNER JOIN LD0V0Q01 T ON T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL  
   INNER JOIN @OrganizationsTable O ON A.ORGENHNR2 = O.ORGENHNR  
   WHERE A.AKTUALTYP =  T.XNUM1 and A.AKTUALTYP = @AKTUALTYP   
   AND A.ADATUM >= @RestrictedFromDate  
   AND A.HAND = '' AND A.HAND2 = ''  
   AND A.PROCDATUM = ''  
   AND A.BESLUTNR = 0  
   AND A.AKTUALNR not in (select AKTUALNR from #HSAC_Aktualserings);  
  END;  
   
  SELECT @CountOrganizationUnhandledAnnouncements = COUNT(*) FROM #HSAC_Aktualserings;  
   
  --Unhandled Announcements - by caseworker  
   
  INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
  SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
  ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
  FROM LD0V0H A  
  INNER JOIN LD0P0A P ON A.PNR = P.PNR  
  INNER JOIN LD0V0Q01 T ON T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL  
  INNER JOIN @CaseWorkersTable C on A.HAND = C.UserName  
  WHERE A.AKTUALTYP = T.XNUM1 AND A.AKTUALTYP = @AKTUALTYP  
   AND A.ADATUM >= @RestrictedFromDate  
   AND A.PROCDATUM = ''  
   AND A.BESLUTNR = 0;  
   
  INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
  SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
  ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
  FROM LD0V0H A  
  INNER JOIN LD0P0A P ON A.PNR = P.PNR  
  INNER JOIN LD0V0Q01 T ON T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL  
  INNER JOIN @CaseWorkersTable C on A.HAND2 = C.UserName  
  WHERE A.AKTUALTYP = T.XNUM1 AND A.AKTUALTYP = @AKTUALTYP  
   AND A.ADATUM >= @RestrictedFromDate  
   AND A.PROCDATUM = ''  
   AND A.BESLUTNR = 0  
   AND AKTUALNR not in (select AKTUALNR from #HSAC_Aktualserings);  
  SELECT @CountTotalUnhandledAnnouncements = COUNT(*) FROM #HSAC_Aktualserings;  
  SELECT @CountUrgentUnhandledAnnouncements = COUNT(*) FROM #HSAC_Aktualserings WHERE URGENCY = 3; --EfficaDefinitions.UrgentAktualsering  
   
  SET @CountLateUnhandledAnnouncements = 0;  
  SET @LastAKTUALNR = -1;  
  SET @CurrentAKTUALNR = 0;  
  WHILE @LastAKTUALNR <> @CurrentAKTUALNR  
  BEGIN    
   SET @LastAKTUALNR = @CurrentAKTUALNR;  
   SELECT TOP 1 @CurrentAKTUALNR = AKTUALNR, @BeginDate = CONVERT(datetime, LEFT(ADATUM,4) + SUBSTRING(ADATUM,5,2) + RIGHT(ADATUM,2),112),  
      @UseOnlyWorkingDays = USEWORKINGDAYS, @ConcernsNextMonth = NEXTMONTH, @DefaultHandlingTime = DEFAULTHANDLINGTIME, @InterruptionStartDate = AVBFROMDAT, @InterruptionEndDate = AVBTOMDAT  
   FROM #HSAC_Aktualserings  
   WHERE AKTUALNR > @LastAKTUALNR   
   ORDER BY AKTUALNR  
   
   IF @CurrentAKTUALNR <> @LastAKTUALNR  
   BEGIN  
    --Calculations start from the next counted day after application has been received  
    SET @FirstDate = DATEADD(day, 1, @BeginDate);  
   
    --adjust start date if the application concerns next month  
    IF @ConcernsNextMonth > 0  
    BEGIN  
     --First count backwards seven days from the end of the month  
     exec OnPremFI_LD0.LD0SPGetSevenDaysPriorEndOfMonth @BeginDate = @FirstDate, @UseOnlyWorkingDays = @UseOnlyWorkingDays, @ResultDate = @SevenDaysPriorEndOfMonth OUT;  
   
     --counted start date is later than original application date...  
     IF @SevenDaysPriorEndOfMonth >= @FirstDate  
     BEGIN  
      SET @FirstDate = @SevenDaysPriorEndOfMonth  
     END  
    END  
   
    EXEC OnPremFI_LD0.LD0SPCountDaysForProcessingTime @BeginDate = @FirstDate, @EndDate = @DecisionDate,  
                 @UseOnlyWorkingDays = @UseOnlyWorkingDays,  
                 @InterruptionStartDate = @InterruptionStartDate, @InterruptionEndDate = @InterruptionEndDate,  
                 @Count = @ActualProcessingTime OUT;  
    IF (@DefaultHandlingTime != 0 AND @DefaultHandlingTime < @ActualProcessingTime)  
    BEGIN  
     SET @CountLateUnhandledAnnouncements = @CountLateUnhandledAnnouncements + 1;  
    END  
   END  
  END  
  
  DELETE #HSAC_Aktualserings;  
 END  
  
 --Unhandled Other Aktualizations  
 IF @FetchActualizations = 1  
 BEGIN  
  --Unhandled Other Aktualizations - by organization  
  set @AKTUALTYP = 3;  
  
  -- Get all aktualiserings for OrganizationUnitId (A.ORGENHNR).  
  IF @TeamId = 0  
  BEGIN  
   INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
   SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
   ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
   FROM LD0V0H A  
   INNER JOIN LD0P0A P ON A.PNR = P.PNR  
   LEFT JOIN LD0V0Q01 T ON T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL  
   INNER JOIN @OrganizationsTable O ON A.ORGENHNR = O.ORGENHNR  
   WHERE A.AKTUALTYP = @AKTUALTYP  
    AND A.ADATUM >= @RestrictedFromDate  
    AND A.HAND = '' AND A.HAND2 = ''  
    AND A.PROCDATUM = ''  
    AND A.BESLUTNR = 0;  
  END;  
  
  -- Get all aktualiserings for CoOrganizationUnitId (A.ORGENHNR2).  
  IF @TeamId = 0  
  BEGIN  
   INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
   SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
   ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
   FROM LD0V0H A  
   INNER JOIN LD0P0A P ON A.PNR = P.PNR  
   LEFT JOIN LD0V0Q01 T ON T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL  
   INNER JOIN @OrganizationsTable O ON A.ORGENHNR2 = O.ORGENHNR  
   WHERE A.AKTUALTYP = @AKTUALTYP  
    AND A.ADATUM >= @RestrictedFromDate  
    AND A.HAND = '' AND A.HAND2 = ''  
    AND A.PROCDATUM = ''  
    AND A.BESLUTNR = 0  
    AND A.AKTUALNR not in (select AKTUALNR from #HSAC_Aktualserings);  
  END;  
  
  SELECT @CountOrganizationOtherActualizations = COUNT(*) FROM #HSAC_Aktualserings;  
  
  INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
  SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
  ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
  FROM [OnPremFI_LD0].LD0V0H A  
  INNER JOIN [OnPremFI_LD0].LD0P0A P ON A.PNR = P.PNR  
  INNER JOIN @CaseWorkersTable C on A.HAND = C.UserName  
  LEFT JOIN LD0V0Q01 T ON (T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL)  
  WHERE A.BESLUTNR = 0  
    AND A.AKTUALTYP >= @AKTUALTYP  
    AND A.ADATUM >= @RestrictedFromDate  
    AND A.PROCDATUM = '';  
  
  INSERT INTO #HSAC_Aktualserings(AKTUALNR, DATUM, URGENCY, ADATUM, NEXTMONTH, AVBFROMDAT, AVBTOMDAT, USEWORKINGDAYS, DEFAULTHANDLINGTIME)  
  SELECT A.AKTUALNR, DATUM = NULL, URGENCY, ADATUM,NEXTMONTH,AVBFROMDAT,AVBTOMDAT, IIF(A.AKTUALTYP = 1, ISNULL(T.XNUM10,0),  
  ISNULL(T.XNUM8,0)) AS USEWORKINGDAYS, IIF(A.AKTUALTYP = 1,T.XNUM6,T.XNUM5) AS DEFAULTHANDLINGTIME  
  FROM [OnPremFI_LD0].LD0V0H A  
  INNER JOIN [OnPremFI_LD0].LD0P0A P ON A.PNR = P.PNR  
  INNER JOIN @CaseWorkersTable C on A.HAND2 = C.UserName  
  LEFT JOIN LD0V0Q01 T ON (T.KODTYP = 'AKTUAL' AND T.KOD = A.AKTUAL)  
  WHERE A.BESLUTNR = 0  
    AND A.AKTUALTYP >= @AKTUALTYP  
    AND A.ADATUM >= @RestrictedFromDate  
    AND A.AKTUALNR not in (select AKTUALNR from #HSAC_Aktualserings)  
    AND A.PROCDATUM = '';  
  
  SELECT @CountTotalOtherActualizations = COUNT(*) FROM #HSAC_Aktualserings;  
  SELECT @CountUrgentOtherActualizations = COUNT(*) FROM #HSAC_Aktualserings WHERE URGENCY = 3; --EfficaDefinitions.UrgentAktualsering  
  
  SET @CountLateOtherActualizations = 0;  
  SET @LastAKTUALNR = -1;  
  SET @CurrentAKTUALNR = 0;  
  WHILE @LastAKTUALNR <> @CurrentAKTUALNR  
  BEGIN    
   SET @LastAKTUALNR = @CurrentAKTUALNR;  
   SELECT TOP 1 @CurrentAKTUALNR = AKTUALNR, @BeginDate = CONVERT(datetime, LEFT(ADATUM,4) + SUBSTRING(ADATUM,5,2) + RIGHT(ADATUM,2),112),  
      @UseOnlyWorkingDays = USEWORKINGDAYS, @ConcernsNextMonth = NEXTMONTH, @DefaultHandlingTime = DEFAULTHANDLINGTIME, @InterruptionStartDate = AVBFROMDAT, @InterruptionEndDate = AVBTOMDAT  
   FROM #HSAC_Aktualserings  
   WHERE AKTUALNR > @LastAKTUALNR   
   ORDER BY AKTUALNR  
  
   IF @CurrentAKTUALNR <> @LastAKTUALNR  
   BEGIN  
    --Calculations start from the next counted day after application has been received  
    SET @FirstDate = DATEADD(day, 1, @BeginDate);  
  
    --adjust start date if the application concerns next month  
    IF @ConcernsNextMonth > 0  
    BEGIN  
     --First count backwards seven days from the end of the month  
     exec OnPremFI_LD0.LD0SPGetSevenDaysPriorEndOfMonth @BeginDate = @FirstDate, @UseOnlyWorkingDays = @UseOnlyWorkingDays, @ResultDate = @SevenDaysPriorEndOfMonth OUT;  
  
     --counted start date is later than original application date...  
     IF @SevenDaysPriorEndOfMonth >= @FirstDate  
     BEGIN  
      SET @FirstDate = @SevenDaysPriorEndOfMonth  
     END  
    END  
  
    EXEC OnPremFI_LD0.LD0SPCountDaysForProcessingTime @BeginDate = @FirstDate, @EndDate = @DecisionDate,  
               @UseOnlyWorkingDays = @UseOnlyWorkingDays,  
               @InterruptionStartDate = @InterruptionStartDate, @InterruptionEndDate = @InterruptionEndDate,  
               @Count = @ActualProcessingTime OUT;  
    IF (@DefaultHandlingTime != 0 AND @DefaultHandlingTime < @ActualProcessingTime)  
    BEGIN  
     SET @CountLateOtherActualizations = @CountLateOtherActualizations + 1;  
    END  
   END  
  END  
 END  
   
 DELETE #HSAC_Aktualserings;  
 DROP TABLE #HSAC_Aktualserings  
 DROP TABLE #Count  
   
 SELECT @CountTotalUnhandledApplications as TotalUnhandledApplications,  
     @CountOrganizationUnhandledApplications as OrganizationUnhandledApplications,  
        @CountUrgentUnhandledApplications as UrgentUnhandledApplications,  
     @CountLateUnhandledApplications as LateUnhandledApplications,  
  
     @CountTotalUnhandledAnnouncements as TotalUnhandledAnnouncements,  
     @CountOrganizationUnhandledAnnouncements as OrganizationUnhandledAnnouncements,  
     @CountUrgentUnhandledAnnouncements as UrgentUnhandledAnnouncements,  
     @CountLateUnhandledAnnouncements as LateUnhandledAnnouncements,  
  
     @CountTotalOtherActualizations as TotalOtherActualizations,  
     @CountOrganizationOtherActualizations as OrganizationOtherActualizations,  
     @CountUrgentOtherActualizations as UrgentOtherActualizations,  
     @CountLateOtherActualizations as LateOtherActualizations,  
  
     @CountUnhandledTasks as UnhandledTasks,  
     @CountUnhandledDecisions as UnhandledDecisions,  
     @CountUnhandledEfficaDocuments as UnhandledEfficaDocuments,  
     @CountUnhandledEhrDocuments as UnhandledEhrDocuments  
  
 SET NOCOUNT OFF  
END  
  
  
GO

GRANT EXECUTE ON LD0SPHomeScreenAlertCounts TO LD0ROANV 

GO